Erhalten Sie Zugang zu diesem und mehr als 300000 Büchern ab EUR 5,99 monatlich.
- Herausgeber: Marcombo
- Kategorie: Wissenschaft und neue Technologien
- Serie: Aprender...con 100 ejercicios prácticos
- Sprache: Spanisch
- Veröffentlichungsjahr: 2012
Con este manual a color aprenderá a: Crear diferentes tipos de referencias y nombres de celdas y rangos. Representar sus datos en tablas de datos, tablas dinámicas, matrices, toda clase de gráficos, minigráficos y formatos condicionales. Trabajar con las avanzadas herramientas de datos de Excel 2010 que permiten, por ejemplo, ubicar y corregir errores de planteamiento, así como crear criterios de validación. Realizar complejos análisis de hipótesis con cada uno de los sistemas que ofrece la aplicación. Utilizar desde diferentes perspectivas una gran cantidad de funciones para muy diversos tipos de análisis, tanto numéricos, como lógicos y de texto. Entre muchas otras cosas, calculará cada uno de los valores implicados en un préstamo bancario o en una inversión, valorará si le conviene económicamente alquilar o comprar un equipo, conocerá complejos métodos de búsqueda de datos para tablas de gran tamaño, concatenará datos y usará las más importantes funciones estadísticas que ofrece el programa.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 312
Das E-Book (TTS) können Sie hören im Abo „Legimi Premium” in Legimi-Apps auf:
Ähnliche
Título de la obra:
Aprender Fórmulas y funciones para Excel 2010 con 100 ejercicios prácticos
Primera edición, 2012
Diseño de la cubierta: NDENU DISSENY GRÀFIC
ISBN: 978-84-267-2028-3
Quedan rigurosamente prohibidas, sin la autorización escrita de los titulares del copyright, bajo las sanciones establecidas en las leyes, la reproducción total o parcial de esta obra por cualquier medio o procedimiento, incluidos la reprografía y el tratamiento informático, así como la distribución de ejemplares mediante alquiler o préstamo públicos.
Presentación
APRENDER FÓRMULAS Y FUNCIONES PARA EXCEL 2010 CON 100 EJERCICIOS PRÁCTICOS
100 ejercicios prácticos resueltos que conforman un recorrido por las más sofisticadas herramientas y funciones del programa. Si bien es imposible recoger en las páginas de este libro todas las posibilidades de cálculo, procesamiento y análisis de datos que ofrece Excel 2010, una vez realizados los 100 ejercicios que componen este manual, el lector conocerá aquellas fundamentales para el usuario ya iniciado que desea dar un paso adelante y sacar máximo provecho a la aplicación.
LA FORMA DE APRENDER
Nuestra experiencia en el ámbito de la enseñanza nos ha llevado a diseñar este tipo de manual, en el que cada función se ejercita mediante la realización de un ejercicio práctico. Dicho ejercicio se halla explicado paso a paso y pulsación a pulsación, a fin de no dejar ninguna duda en su proceso de ejecución. Además, lo hemos ilustrado con imágenes descriptivas de los pasos más importantes y, sobre todo, los resultados que deberían obtenerse y con los recuadros IMPORTANTE, que ofrecen información complementaria sobre los temas tratados en los ejercicios.
Gracias a este sistema se garantiza que una vez realizados los 100 ejercicios que componen el manual, el usuario será capaz de desenvolverse cómodamente con las herramientas más avanzadas de análisis de datos de Excel 2010 y sacar el máximo partido de sus múltiples prestaciones.
LOS ARCHIVOS NECESARIOS
En el caso de que desee utilizar los archivos de ejemplo de este libro, cosa que recomendamos, puede descargarlos desde la zona de descargas de la página de Marcombo (www.marcombo.com) y desde la página específica de este libro.
A QUIÉN VA DIRIGIDO EL MANUAL
Este libro está destinado a usuarios ya iniciados en el uso básico de Excel, puesto que comienza directamente con la introducción de fórmulas. Si es usted un experto en el programa, le resultará muy útil para consultar particularidades sobre el uso de ciertas herramientas y funciones y para conocer útiles modelos de análisis.
Aunque cada ejercicio está tratado de forma independiente, le recomendamos seguir el orden del libro, puesto que en muchos ejercicios se trabaja de forma progresiva sobre las mismas hojas de cálculo. Además, hemos intentado agrupar aquellos ejercicios con temática común. De cualquier manera, los ejercicios son independientes y si necesita realizar una consulta puntual, podrá dirigirse al ejercicio en el que se trata el tema y llevarlo a cabo sobre su propio proyecto.
APRENDER FÓRMULAS Y FUNCIONES PARA EXCEL 2010 CON 100 EJERCICIOS PRÁCTICOS
Con este manual aprenderá a utilizar diferentes tipos de referencias y nombres de celdas y rangos. Podrá representar sus datos en tablas de datos, tablas dinámicas, matrices, toda clase de gráficos, minigráficos y formatos condicionales.
Trabajará con las avanzadas herramientas de datos de la aplicación que permiten, por ejemplo, ubicar y corregir errores de planteamiento así como crear criterios de validación. Realizará complejos análisis de hipótesis con cada uno de los sistemas que ofrece Excel 2010 y, finalmente, conocerá y usará desde diferentes perspectivas una inmensa cantidad de funciones para muy diversos tipos de análisis, tanto numéricos, como lógicos y de texto. Entre muchas otras cosas, calculará cada uno de los valores implicados en un préstamo bancario o en una inversión, valorará si le conviene económicamente alquilar o comprar un equipo, conocerá complejos métodos de búsqueda de datos para tablas de gran tamaño, concatenará datos y usará las más importantes funciones estadísticas que ofrece el programa.
Cómo funcionan los libros
“Aprender…”
Índice
001 Introducir fórmulas
002 Trabajar con referencias
003 Editar una fórmula
004 Usar autorrelleno y referencias absolutas
005 Usar referencias mixtas
006 Insertar funciones: Usar SUMA
007 Otras funciones de Autosuma
008 Calcular subtotales
009 Nombrar celdas y rangos
010 Administrar y usar nombres (I)
011 Administrar y usar nombres (II)
012 Trabajar con referencias
013 Celdas precedentes y celdas dependientes
014 Usar referencia externa a libro abierto
015 Referencias circulares y cálculo manual
016 Crear y editar un gráfico de columnas
017 Crear y editar gráficos circulares
018 Crear y editar un minigráfico
019 Dar a los datos formato de tabla
020 Trabajar con subtotales en una tabla
021 Aplicar formato condicional
022 Administrar reglas de formato condicional
023 Crear informes de tablas dinámicas
024 Modificar informes de tablas dinámicas (I)
025 Modificar informes de tablas dinámicas (II)
026 Crear gráficos dinámicos
027 Editar un gráfico dinámico
028 Crear fórmulas de matriz
029 Modificar fórmulas de matriz
030 Trabajar con constantes de matriz
031 Operaciones reales con constantes de matriz
032 Dividir texto en columnas
033 Quitar duplicados
034 Definir criterios de validación de datos
035 Crear una lista desplegable de datos
036 Consolidar datos de varias tablas
037 Analizar en tabla de datos de una variable
038 Analizar en tabla de datos de dos variables
039 Analizar margen neto en diversos escenarios
040 Crear un resumen de escenarios
041 Ajustar una variable para alcanzar objetivo
042 Ajustar variables a objetivo con Solver (I)
043 Ajustar variables a objetivo con Solver (II)
044 Interpretar un error en una fórmula
045 Introducción al uso de funciones
046 Manipular caracteres con funciones de texto
047 Extraer, sustituir y reemplazar texto
048 Cambiar minúsculas y mayúsculas
049 Convertir valores en texto y texto en valores
050 Buscar desde el valor de la primera columna
051 Buscar usando referencias relativas (I)
052 Buscar usando referencias relativas (II)
053 Mostrar dirección y usar referencia indirecta
054 Transponer un rango con una función
055 Calcular precio variable con función SI
056 Conocer las otras funciones lógicas
057 Redondear un valor (I)
058 Redondear un valor (II)
059 Crear sumas condicionales
060 Sumar el acumulado para cada fila
061 Calcular las cuotas de un préstamo
062 Calcular cuotas para alcanzar una inversión
063 Estimar parte amortizada e intereses
064 Calcular el valor actual de una inversión
065 Calcular el valor futuro de una inversión
066 En cuánto tiempo podría pagar
067 Estimar la tasa de interés ideal
068 Calcular el valor presente neto de inversión
069 Calcular la tasa interna de retorno
070 El valor presente neto no periódico
071 Calcular TIR para flujos no periódicos
072 Calcular tasa interna de retorno múltiple
073 Analizar la tasa interna de retorno múltiple
074 Calcular el valor futuro para tasas variables
075 Retorno sobre inversión de interés variable
076 Calcular interés efectivo e interés nominal
077 Calcular el pago a capital acumulado
078 Calcular el pago de intereses acumulado
079 ¿Comprar o alquilar?
080 Estimar depreciación lineal
081 Calcular depreciación acelerada
082 Depreciación acelerada y por meses
083 Depreciación por disminución variable (I)
084 Depreciación por disminución variable (II)
085 Calcular depreciación acumulada
086 Entender las fechas como lo hace Excel
087 Extraer datos de una fecha
088 Contar a partir del día de hoy y hasta hoy
089 Trabajar con días laborables
090 Entender las horas como lo hace Excel
091 Extraer horas, minutos y segundos
092 Calcular con horas
093 Sumar horas trabajadas
094 Realizar un cálculo de tiempo muy complejo
095 Calcular promedio, media y mediana
096 Otras formas de medir tendencias centrales
097 Establecer jerarquías
098 Percentiles, frecuencias, los de moda y los 10 más
099 Usar algunas funciones matemáticas
100 Obtener información sobre sus datos
001
Introducir fórmulas
LAS FÓRMULAS TRABAJAN CON OPERADORES que indican a la aplicación la operación que debe realizar con determinados valores. Al introducir estos operadores aritméticos en las celdas de una hoja de Excel, la convertimos en una calculadora capaz de realizar operaciones de gran complejidad.
IMPORTANTE
Las fórmulas pueden introducirse directamente en la celda o bien en la Barra de fórmulas y deben ir precedidas del signo Igual () que las identifica como tales. Una vez ha introducido una fórmula, también puede aplicarla usando las teclas de desplazamiento. De este modo, además de confirmar la entrada, puede ubicarse en la celda que más le interese.
La escritura de fórmulas en Excel 2010 es muy sencilla y, en general, bastante lógica. En este ejercicio introduciremos algunas fórmulas muy simples utilizando valores constantes, para conocer los operadores básicos con los que trabaja la aplicación. Para comenzar, abra la aplicación o, si ya la tiene abierta, cree un nuevo libro en blanco.
Seleccione la celda
A1
e introduzca el signo
Igual (=)
. Cuando el primer caracter introducido en una celda es el signo
Igual (=)
, la aplicación entiende que se introducirá una fórmula.
Llamamos operadores aritméticos a los símbolos que indican la operación matemática que se debe realizar. La suma de dos valores constantes es una de las fórmula más simples que puede introducir y usa simplemente el sigo
Más (+)
. Escriba la fórmula
=170+30
directamente desde su teclado y pulse el botón
Introducir,
que es el que muestra el signo de verificación en la
Barra de fórmulas.
Las fórmulas en Excel siempre comienzan con un signo Igual ().
El botón Introducir permite aplicar la fórmula sin necesidad de desplazarse a otra celda de la hoja.
Aunque al aplicar una fórmula ésta es sustituida en la celda por el resultado de la operación, en la Barra de fórmulas puede ver la fórmula siempre que la celda esté seleccionada
El programa muestra el resultado de la operación en la celda, aunque aún puede verse en la
Barra de fórmulas
el contenido real de la celda
A1,
es decir, la fórmula introducida. Pulse la tecla
Desplazamiento
hacia abajo para seleccionar la celda
A3.
Introduciremos una resta en la celda seleccionada y para ello usaremos como operador un guión corto. Introduzca desde su teclado la fórmula
=893-94
(siempre sin punto final) y pulse la tecla
Retorno
para aplicar la fórmula.
Al usar la tecla
Retorno,
se realiza el cálculo y se selecciona automáticamente la casilla inferior. El asterisco (
*
) es el signo utilizado para expresar una multiplicación. Introduzca desde su teclado la fórmula
=155*1015.
Pulse la tecla
Tabulador
para introducir la fórmula y al mismo tiempo desplazarse a la celda de la derecha, es decir,
B3.
Como ve, la celda en cuestión muestra el producto obtenido. Pulse la tecla
Retorno.
Automáticamente se ubica en la siguiente celda de la primera columna, ya que es en la que está trabajando, donde introduciremos una división usando el signo la barra inclinada (
/
). Introduzca la fórmula
=999/3
y pulse el botón
Introducir
de la
Barra de fórmulas.
Puede moverse de una celda a la otra con clics de ratón, o usando las teclas de desplazamiento, la tecla Tabulador o la tecla Retorno.
Nuevamente la aplicación calcula en resultado en la celda, mientras continúa mostrando la fórmula en la
Barra de fórmulas.
Pulse
Ctrl.+G
para abrir el cuadro de diálogo
Guardar como,
introduzca el nombre
Libro de prueba
y guárdelo en la ubicación que prefiera.
IMPORTANTE
Además de los operadores aritméticos que hemos usado en este ejercicio, puede usar el acento circunflejo (^) para indicar una ponencia. Así, la fórmula =3^3 dará como resultado 9.
002
Trabajar con referencias
UNA FÓRMULA PUEDE TRABAJAR CON CONSTANTES o también puede contener referencias que la vinculen a otras celdas. Para ello simplemente deberá usar, en lugar del valor de la variable, el nombre de la celda que la contiene, es decir, su referencia.
IMPORTANTE
Observe que al crear una referencia a otra celda que contiene una fórmula, la aplicación asume para la referencia el valor que da como resultado la fórmula de la celda referida.
003
Editar una fórmula
LAS FÓRMULAS SE EDITAN de la misma forma que se editaría un texto: reescribiéndolas directamente en la celda o haciéndolo desde la Barra de fórmulas. Probablemente la forma más rápida de hacerlo es con un doble clic sobre la celda que la contiene. De esta forma el punto de inserción aparece dentro de la celda para realizar los cambios oportunos.
IMPORTANTE
Cuando introducimos el nombre de una celda en una fórmula, creamos una referencia que indica que la fórmula se calculará en función del contenido de dicha celda (o del resultado de la operación que ésta contenga). Si el contenido de la celda a la que se hace referencia cambia, el resultado del cálculo de la segunda fórmula también cambiará automáticamente.
En este ejercicio veremos distintas formas de modificar una fórmula una vez ha sido introducida. Seleccione la celda
A1
y haga clic en la
Barra de fórmulas
de manera que el cursor se sitúe al final de la fórmula.
La fórmula insertada en la celda A1 está formada por constantes, es decir, números o valores introducidos directamente desde el teclado.
Elimine el valor usando la tecla
Retroceso
e introduzca en su lugar el valor
200.
Pulse la tecla
Retorno
para confirmar la modificación en la fórmula y compruebe cómo el valor de la celda
A1
se actualiza al momento.
Ahora pulse
Ctrl.+Z
mientras observa atentamente los valores de las celdas
B1
y
B4.
Al cambiar la fórmula en la celda A1, cambia su resultado, así como el de todas las celdas que hacen referencia a ésta.
Al modificar uno de los valores a los que estas celdas hacen referencia, se han actualizado los resultados de cada una de ellas de forma automática. Pulse
Ctrl.+Mayúsculas+Z
para rehacer y compruebe una vez más la actualización automática de las celdas que contienen referencias a
A1.
Active la celda
B1
y seleccione con un arrastre de ratón la referencia
A1
en la
Barra de fórmulas.
Haga un clic sobre la celda
A4.
La celda se ve nuevamente enmarcada en el borde parpadeante y su referencia en la fórmula es de color azul. Pulse la tecla
Retorno
para confirmar la modificación.
Haga ahora un doble clic en la celda
B3.
La fórmula que contienen vuelve a mostrarse en la propia celda, que además se hace editable. Seleccione la primera referencia
(A3)
y sustitúyala por la referencia
D1.
Pulse la el botón
Introducir.
Dado que la celda
D1
está vacía, su valor numérico equivale a
0
y, por lo tanto, el resultado de la fórmula de la celda
B3
es ahora igual a
0.
Si ahora quisiera eliminar esta fórmula y sustituirla por otra, bastaría con situarse en la celda que la contiene y escribir la nueva fórmula. En lugar de esto seleccione nuevamente la celda
A1
y pulse la tecla
F2.
También de este modo la fórmula que contiene se hace editable. Pulse
F9.
Tanto en la celda como en la
Barra de fórmula
puede ver que la fórmula ha sido sustituida por el valor que daba como resultado
(343).
Haga ahora un doble clic sobre la celda
B3
y, cuando se haga editable, seleccione el texto
D1.
Pulse la tecla
F9
y observe cómo en este caso sólo el segmento seleccionado de la fórmula es sustituido por el valor de la celda a la que hacía referencia.
IMPORTANTE
Evidentemente, si sustituye una fórmula por su resultado usando la tecla F9, los cambios posteriores en la celdas a las que la fórmula hacía referencia no afectarán al valor de la celda en cuestión.
004
Usar autorrelleno y referencias absolutas
Para crear tablas más complejas es importante saber usar distintos tipos de referencias. En este ejercicio trabajaremos con referencias relativas (del tipo A1) y con referencias absolutas (como $A$1). Trabajaremos además con la función de Autorrelleno de la aplicación.
IMPORTANTE
Al copiar y pegar una fórmula con referencias absolutas y relativas, la primera se modifica adaptándose a su nueva ubicación mientras que la segunda permanece fija.
Para este ejercicio descargue de nuestra web el archivo
Precios y cantidades.xls
y ábralo en pantalla.
Es una sencilla hoja de cálculo que contiene una lista de productos y sus precios. Supondremos que se trata de una lista de ventas y completaremos en primer lugar la columna
Total
(€). Seleccione la celda
E2
e introduzca la siguiente fórmula con el método que prefiera:
=C2*E2.
Al multiplicar la cantidad del primer artículo por su precio, obtenemos el total antes del IVA. El resto de las celdas de la columna
Total (
€
) sin IVA
deberá rellenarse con fórmulas idénticas a la que hemos creado, pero con los números de fila correspondientes. Pulse la combinación de teclas
Ctrl.+C
para copiar en el portapapeles el contenido de esta celda, seleccione la celda inmediatamente inferior a la actual y pulse la combinación de teclas
Ctrl.+V.
La columna se completa. Seleccione la celda
E5
y observe la fórmula que contiene. Al pegar en las filas de una columna una fórmula con referencias relativas, la aplicación interpreta (con acierto) que todas las referencias a filas deben aumentar un número.
Al copiar una fórmula con referencias relativas en otras celdas, éstas se adaptan a los valores relativos a la celda en la que han sido copiados.
A todos los productos les corresponde el tipo impositivo general. Vamos a crear un nuevo campo en el que podamos modificar el tipo impositivo fácilmente para todos en caso de que sea necesario. Ubíquese en la celda
D25
e introduzca el texto
IVA (%).
Luego pulse la tecla
Desplazamiento hacia la derecha
e introduzca el valor
18.
Si lo prefiere puede copiar el formato de las celdas
D20
y
E20
y pegarlo a las dos celdas que acaba de modificar.
Ubíquese en la celda
F2,
la primera de la columna
% IVA,
e introduzca la fórmula
=E25.
De este modo le indica a la aplicación que el valor de la celda debe ser igual al de
E25.
Pulse el botón
Introducir
y luego arrastre hacia abajo, hasta cubrir toda la columna, desde la esquina inferior derecha de la celda actual.
La aplicación asume que, a partir de
F2,
desea crear nuevas referencias relativas a las celdas que siguen a
E25,
pero en este caso no es correcto. Deberemos por tanto crear referencias absolutas usando el signo
$
. Sustituya la fórmula de la celda
F2
por
=$E$25
y pulse el botón
Introducir.
El signo de $ en esta referencia indica a la aplicación que la fórmula, al ser pegada en otras celdas, no debe cambiar a otras referencias relativas, sino que debe mantener la referencia a la columna E y a la fila 25.
El signo
$
indica que la referencia es absoluta, es decir, que no debe cambiar si es copiada otra celda. Usando el método de arrastre, pegue de nuevo la fórmula en las celdas siguientes de la misma columna.
Ahora el IVA para todos los artículos es 18 y cambiarlo es tan fácil como modificar el valor de la celda E25.
Si necesita cambiar el tipo de IVA para todos los artículos de la lista, sólo tendrá que modificar el contenido de la celda E25.
005
Usar referencias mixtas
EN OCASIONES ES NECESARIO CREAR REFERENCIAS mixtas. Éstas permiten que una fórmula sea absoluta de forma parcial. Es decir, que al copiarla se adapte la columna y no la fila o, al contrario, que se actualice la fila y no la columna.
Supongamos que quiere aplicar a los precios de los productos de nuestra hoja de cálculo, distintos descuentos según número de artículos vendidos. Abra la hoja
Descuentos,
del mismo archivo, pulsando en la pestaña correspondiente.
La tabla en pantalla muestra en la columna
B
los descuentos a aplicar a cada artículo según la cantidad. La fila
2
muestra los precio sin descuento, que se aplican para ventas de 1 a 11 unidades. Para comenzar ubíquese en la celda
C3.
En esta celda calcularemos el precio después del descuento del artículo
Lápiz Triangle-E
para compras de 12 a 99 unidades. Partimos del precio unitario. Introduzca para comenzar el texto
=C2.
Restaremos de inmediato el descuento, que será el resultado de multiplicar el precio unitario por el valor del descuento y dividirlo entre 100. Complete la fórmula escribiendo:
-C2*B3/100
y pulse el botón
Aplicar.
Calculamos el precio del primer producto con el primer descuento.
El precio del lápiz para ventas de 12 a 99 unidades es de
0,133
euros. Arrastre la esquina inferior de la celda hasta cubrir la celda
C4,
una fila más abajo, y seleccione la
C4
para ver la fórmula que se ha creado automáticamente.
No es correcta pues el cálculo se hace a partir de la celda
C3,
que tiene el precio del primer descuento. Pulse la tecla
Suprimir
para borrarla y seleccione de nuevo la celda
C2.
Al pegar la fórmula, esta se ajusta automáticamente, pero en este caso el resultado no es el que buscamos.
Para que al pegar la fórmula en el resto de la columna siempre se aplique el descuento a los precios unitarios, que se encuentran en la fila
2,
haremos una referencia mixta. Pulse
F2
para hacer la fórmula editable y añada un signo
$
antes del número
2
en las dos referencias a la celda
C2.
La fórmula debe quedar así:
=C$2-C$2*B3/100.
De éste modo, cuando pegue la fórmula en las siguientes columnas se usará el valor del precio unitario indicado en la fila
2
de la columna que corresponda. Aplique la fórmula y arrástrela para pegarla en la celda de debajo.
Ubíquese ahora en la celda
C4
y compruebe que ahora la fórmula si es correcta.
Haga una copia de esta celda en la celda
D4
con el método que prefiera y ubíquese sobre ésta última para analizar la fórmula.
Tenemos un nuevo error, porque el precio no se multiplica por el descuento, sino que se desplaza a la columna siguiente. Pulse
Ctrl.+Z.
Tenemos que hacer que la referencia a los descuentos sea también mixta. Regrese a la celda
C3
y pulse
F2
para editarla.
Añada un signo
$
antes de la letra
B.
La fórmula debe quedar así:
=C$2-C$2*$B3/100.
Con un arrastre, pegue la fórmula en las dos filas siguientes.
Compruebe que las fórmulas son correctas, vuelva a seleccionar el rango de celdas de
C3
a
C5
y arrastre la esquina inferior del grupo hasta cubrir las siguientes columnas de la tabla.
En este ejercicio utilizamos una referencia mixta para los distintos precios unitarios de cada producto y otra para los distintos descuentos. Sería imposible que la aplicación generara las fórmulas correctas para el resto de la tabla sin el uso de referencias mixtas.
Compruebe en algunas celdas seleccionadas de forma aleatoria la correcta adaptación de la fórmula.
006
Insertar funciones: Usar SUMA
LA PRINCIPAL FINALIDAD DEL COMANDO AUTOSUMA es agilizar la introducción de las funciones más habituales. Siempre aplica como primera opción la función Suma y establece, de acuerdo con su propia lógica, cuál es el rango de celdas sobre el que debe aplicarse.
Para este ejercicio, regrese a la hoja
Lista Precios y cantidades
de nuestro documento, cambie el contenido de la celda
F2
por la siguiente fórmula:
=E2*$E$25/100
y aplíquela.
Copie la fórmula introducida en las celdas
F3
a
F14
para que se calcule el IVA de cada total.
Introduciremos la función
Autosuma
en la celda
G2.
Haga clic en dicha celda para seleccionarla.
Pulse en la pestaña
Fórmulas
de la
Cinta de opciones
y haga clic sobre el botón
Autosuma,
en el grupo de herramientas
Biblioteca de funciones.
Existen varias funciones de Autosuma además de la Suma.
Al pulsar directamente sobre el botón se inserta de manera automática la función correspondiente a la suma de las celdas que están a la izquierda, en este caso, de
C2
a
F2.
El rango de celdas se indica usando la referencia de la primera celda separada por dos puntos de la referencia de la última celda. Sin embargo, en este caso sólo nos interesa sumar las celdas
E2
y
F2,
así que seleccione el rango en la función para poder modificarlo y seleccione con un arrastre las celdas
E2
y
F2.
Mientras introduce la función, se muestra seleccionado en un marco parpadeante el rango de celdas considerado.
Al seleccionar las celdas, se muestran encerradas en el marco parpadeante y sus referencias sustituyen al rango en la función
SUMA.
Pulse la tecla
Retorno
para confirmar la entrada.
El valor de la celda refleja la sumatoria de las celdas indicadas. Pegue la función creada en las restantes celdas de la columna
G.
Seleccione la celda
E16
y pulse el botón
fx
de la
Barra de fórmulas.
En el cuadro de diálogo
Insertar función
se presenta como primera opción la función
SUMA.
Pulse el botón
Aceptar.
Se abre el cuadro
Argumentos de función,
donde automáticamente se ha establecido como
Número 1
el rango
E2:E15.
Cámbielo por
E2:E14,
que es lo que corresponde, y pulse el botón
Aceptar.
La celda muestra al momento el valor correspondiente a la suma de todos los totales sin IVA. Copie la función en la celda
E17
y haga un doble clic sobre ella.
Al hacerse editable la función, se muestra seleccionado el rango de celdas, que no es el que queremos. Seleccione el rango en la función y con un arrastre, seleccione los valores de
% IVA.
Pulse la tecla
Retorno
para aplicar.
Ubíquese en la celda
E18
y repita los dos pasos anteriores, pero esta vez sume el rango de celdas
G2:G14.
Seleccione las celdas de
F16
a
G20
y elimínelas usando la opción
Borrar todo
de la herramienta
Borrar,
en el grupo
Modificar
de la ficha
Inicio
de la
Cinta de opciones.
IMPORTANTE
Puede seleccionar celdas no contiguas utilizando la tecla Control. Se selecciona una celda, se pulsa Control y sin soltar la tecla, se van seleccionando las celdas necesarias.
007
Otras funciones de Autosuma
ADEMÁS DE LA SUMA, EL COMANDO AUTOSUMA permite una fácil aplicación de funciones que calculan el promedio de los resultados de un conjunto de celdas, el número mayor y el menor del rango seleccionado y contar el número de celdas numéricas comprendidas en el rango.
Regrese a la hoja de cálculo
Descuentos
del libro
Precios y cantidades
con el que estamos trabajando, y ubíquese en la celda
A7.
Introduzca el texto
Precio Máximo
y pulse la tecla
Tabulador
para desplazarse a la celda
B7.
Ahora insertaremos la función
MÁX
en la celda
B7.
El resultado debe corresponder al valor más alto de todas las celdas que seleccione. Pulse en la pestaña
Fórmulas
de la
Cinta de opciones,
haga un clic sobre la punta de flecha del botón
Autosuma
y seleccione la función
Máx.
La función Max da como resultado el mayor valor del rango.
Excel selecciona de forma automática el rango de celdas
B3:B6.
Para cambiar el rango de celdas implicado en esta función, seleccione la celda
C2,
pulse la tecla
Mayúsculas
y, sin liberarla, pulse sobre la celda
O5.
Después, pulse
Retorno.
El precio más alto del rango de celdas seleccionado es de
18 euros.
Corresponde al precio de las
Gomas de borrar Londres.
¡Error! El precio correcto de este artículo es de
0,18 €
, pero ha habido un error al introducirlo. Corríjalo en
H2.
Se corrigen los precios con descuento y el resultado arrojado por la función
Precio Máximo
cambia a
5,67,
que corresponde al
Compás técnico articulado.
Ubíquese en la celda
A8,
introduzca el texto
Precio medio
y pulse la tecla
Tabulador.
Ahora insertaremos en la celda
B8
la función
Promedio,
que calcula el promedio de los valores del rango seleccionado para la función. Haga clic en el botón
Más
de la herramienta
Autosuma
y seleccione la función
Promedio.
En este caso Excel selecciona la celda inmediatamente superior. Con un arrastre de ratón, seleccione las celdas
C2
a
O2
y pulse la tecla
Retorno
para aceptar la función insertada.
Efectivamente, el promedio de los precios unitarios sin descuento de todos los artículos es de
1,55€.
Inserte en la celda
A9
el texto
N
o
de referencias
y pulse la tecla
Tabulador.
De las opciones de
Autosuma,
seleccione la llamada
Contar números.
Se inserta la función
CONTAR
y selecciona las dos celdas superiores. Seleccione con un arrastre las celdas de
C2
a
O2
y pulse
Retorno.
En el rango seleccionado hay
13
números, es decir, nuestra tabla contiene 13 referencias de productos. Despliegue de nuevo el menú de opciones de
Autosuma.
La función Contar números da como resultado la cantidad de celdas numéricas del rango seleccionado.
Nos quedan las opciones
Min
y
Más funciones.
Escoja esta última.
La opción Más funciones abre el cuadro de diálogo Insertar función.
Se abre el cuadro
Insertar función.
Pulse el botón
Cancelar.
IMPORTANTE
La función Contar números, tal como su nombre hace suponer, no tendrá en cuenta las celdas que contengan valores lógicos o de texto, así como aquellas que estén vacías.
008
Calcular subtotales
EXCEL PUEDE CALCULAR SUBTOTALES DE FORMA automática en celdas concretas de las tablas usando el comando Subtotal, incluido en el grupo de herramientas Esquema de la ficha Datos.
IMPORTANTE
El primer argumento de la función SUBTOTALES desgina la función aplicada. El número 1 identifica la función PROMEDIO, el 2 se usa para CONTAR, el 4 identifica la función MAX, el 5 la función MIN y el 9 establece SUMA, por dar algunos ejemplos. En total permite calcular subtotales con once funciones distintas. Si quiere consultar la lista completa, recurra a la ayuda de la aplicación.
Para visualizar claramente la utilidad del comando
Subtotales,
introduciremos un par de cambios en la hoja
Lista Precios y cantidades.
Cambie la fecha de la celda
A2
por
9/03/2012,
las de
A6
y
A7
por
15/03/2012
y la de
A9
por
25/03/2012.
Sitúese en la celda
A1
y, en la ficha
Datos,
pulse en el comando
Subtotal
del grupo
Esquema.
El botón Subtotal abre el cuadro Subtotales donde debe establecer en qué celdas se inserta la función SUBTOTALES.
Se abre el cuadro de diálogo
Subtotales.
En el menú
Para cada cambio en,
mantenga seleccionada la opción
Fecha,
que nos permitirá calcular los totales diarios. En el panel
Agregar subtotal a
seleccione además de la columna
Total,
la llamada
Total (€) sin IVA
y pulse el botón
Aceptar.
Automáticamente se añaden los totales de cada día para las columnas
Total (€) sin IVA
y
Total
y se genera un esquema de la hoja de cálculo. Pulse sobre el número
1,
situado en la cabecera de los controles de esquema.
En el nivel 1 sólo se muestra el
Total general.
Pulse sobre el número
2
en el cuadro de controles.
Ahora puede ver el total de ventas diario. Pulse en el nivel
3
y observe cómo la tabla vuelve a mostrar todos los niveles.
Pulse sobre el primero de los signos
Menos (-)
en la columna
2,
que corresponde al total del
9 de marzo.
Se ocultan las filas correspondientes a este día y el signo
Menos (-)
es sustituido por un signo
Más (+)
que indica que hay datos ocultos. Nuevamente seleccione la celda
A1
y pulse sobre el botón
Subtotal.
Despliegue la lista del cuadro
Usar función,
compruebe la cantidad de funciones que podía aplicar a cada subtotal y seleccione la opción
Promedio.
La función Subtotales usa otra función sobre un determinado rango de celdas.
Desactive la opción
Reemplazar subtotales actuales
para que los subtotales de suma no desaparezcan, sino que se añadan los de promedio.
Puede establecer también que la aplicación inserte un salto de página manual después de cada subtotal y, si no está seleccionada la opción
Resumen debajo de los datos,
que los subtotales se ubiquen arriba de los datos. Pulse el botón
Aceptar.
Se añaden los promedios de cada día y se añade un nuevo nivel al esquema. Haga clic en el nivel
3
para ver sólo los subtotales creados y colóquese en la celda
E5.
Al agregar otro tipo de subtotal, se añade un nuevo nivel al esquema de la hoja de cálculo.
Excel ha aplicado en cada corte esta misma fórmula,
SUBTOTALES.
El primer argumento indica el número de función utilizada (la numeración ha sido establecida arbitrariamente por la aplicación). Los otros dos argumentos, ubicados después de un punto y seguido, indican el rango de celdas al que ha sido aplicada la función. Muestre de nuevo el cuadro de diálogo
Subtotales
y pulse en el botón
Quitar todos.
Si lo desea, puede modificar los rangos o el tipo de función usado por el de la función SUBTOTALES.
IMPORTANTE
Los números de función de un dígito indican a la aplicación que debe incluir cualquier celda oculta con la función Ocultar filas (no mediante filtros). Si desea que sean ignoradas las celdas ocultas deberá añadir al argumento el número 10. Así, el argumento para la función PROMEDIO sería 101 en lugar de 1 y el de SUMA sería 109
