Aprender fórmulas y funciones con Excel 2010 con 100 ejercicios prácticos

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.

1 Para este ejercicio descargue de nuestra web el archivo Precios y cantidades.xls y ábralo en pantalla.

2 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.

3 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.

4 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.

5 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.

6 Si lo prefiere puede copiar el formato de las celdas D20 y E20 y pegarlo a las dos celdas que acaba de modificar.

7 Ubíquese en la celda F2, la primera de la columna % IVA, e introduzca la fórmula =E25.

8 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.

9 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.

10 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.

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.

1 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.

2 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.

3 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.

4 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.

5 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.

6 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.

7 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.

8 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.

9 Ubíquese ahora en la celda C4 y compruebe que ahora la fórmula si es correcta.

10 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.

11 Tenemos un nuevo error, porque el precio no se multiplica por el descuento, sino que se desplaza a la columna siguiente. Pulse Ctrl.+Z.

12 Tenemos que hacer que la referencia a los descuentos sea también mixta. Regrese a la celda C3 y pulse F2 para editarla.

13 Añada un signo $ antes de la letra B. La fórmula debe quedar así: =C$2-C$2*$B3/100.

14 Con un arrastre, pegue la fórmula en las dos filas siguientes.

15 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.

16 Compruebe en algunas celdas seleccionadas de forma aleatoria la correcta adaptación de la fórmula.

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.

1 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.

2 Copie la fórmula introducida en las celdas F3 a F14 para que se calcule el IVA de cada total.

3 Introduciremos la función Autosuma en la celda G2. Haga clic en dicha celda para seleccionarla.

4 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.

5 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.

6 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.

7 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.

8 Seleccione la celda E16 y pulse el botón fx de la Barra de fórmulas.

9 En el cuadro de diálogo Insertar función se presenta como primera opción la función SUMA. Pulse el botón Aceptar.

10 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.

11 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.

12 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.

13 Ubíquese en la celda E18 y repita los dos pasos anteriores, pero esta vez sume el rango de celdas G2:G14.

14 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.

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.

1 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.

2 Introduzca el texto Precio Máximo y pulse la tecla Tabulador para desplazarse a la celda B7.

3 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.

4 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.

5 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.

6 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.

7 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.

8 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.

9 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 No de referencias y pulse la tecla Tabulador.

10 De las opciones de Autosuma, seleccione la llamada Contar números.

11 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.

12 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.

13 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.

14 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.

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.

1 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.

2 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.

3 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.

4 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.

5 En el nivel 1 sólo se muestra el Total general. Pulse sobre el número 2 en el cuadro de controles.

6 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.

7 Pulse sobre el primero de los signos Menos (-) en la columna 2, que corresponde al total del 9 de marzo.

8 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.

9 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.

10 Desactive la opción Reemplazar subtotales actuales para que los subtotales de suma no desaparezcan, sino que se añadan los de promedio.

11 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.

 

12 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.

13 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 en lugar de 9.

PUEDE CREAR UN NOMBRE PARA DESIGNAR UNA CELDA o un rango de celdas y, esto es lo interesante, luego utilizar ese nombre pare designar la celda o el rango en las fórmulas referidas a ellos.

IMPORTANTE

El primer caracter del nombre de una celda o un rango debe ser una letra, un guión bajo o una barra invertida. Después del primer caracter, el nombre puede usar letras, números, puntos y guiones bajos. Un nombre no distingue entre mayúsculas y minúsculas, y no puede ser idéntico a una referencia de celda, ni tener espacios en blanco. Puede tener hasta 255 caracteres (¡que son muchos!).

1 Seleccione la celda E25 de la hoja Lista Precios y cantidades.

2 Active la ficha Fórmulas y haga clic sobre el botón Asignar nombre del grupo Nombres definidos. Los comentarios añadidos a un nombre pueden ayudar más adelante a identificar mejor el contenido de la celda o rango.

3 El cuadro Nombre asigna por defecto como nombre el texto de la celda más próxima, que es IVA y en este caso es perfecto. El siguiente campo, Ámbito, se refiere a la ubicación donde el nombre será recocido. Despliegue su menú.

4 Puede ser reconocido en todo el libro o sólo en una de las hojas que contiene. Seleccione la opción Libro.

5 Puede además añadir algún comentario. Escriba por ejemplo el texto General y pulse el botón Aceptar.

6 Haga ahora un doble clic en la celda F2 para hacerla editable.

7 Seleccione la referencia $E$25 y pulse sobre la celda E25.

8 En la fórmula no se inserta la referencia a la celda sino el nombre que le hemos asignado, que es IVA. Aplique la fórmula y copie esta celda en el resto de la columna, de F13 a F14.

9 Ubíquese en la celda F14 y compruebe que el nombre actúa como referencia absoluta sin necesidad de usar el signo $. Un nombre actúa como una referencia absoluta.

10 Haga clic en la celda E2, pulse la tecla Mayúsculas y, sin liberarla, seleccione la celda E14.

11 Abra de nuevo el cuadro Nombre nuevo.

12 En este caso el campo Nombre muestra el texto de la cabecera del rango de celdas seleccionado, con un guión bajo en lugar los espacios, y omite los paréntesis. Seleccione el ámbito Lista precios y cantidades y añada algún comentario.

13 Confirme que el campo Hace referencia a indique el rango correcto y pulse el botón Aceptar.

14 Haga un doble clic en la celda E16, seleccione el rango de la fórmula y luego, seleccione las celdas E2 a E14 para introducir de nuevo el rango en la fórmula.

15 El nombre asignado al rango sustituye la referencia en la fórmula. Aplique la fórmula.

16 Seleccione de nuevo las celdas E2 a E14.

17 Nótese que en el primer campo de la Barra de fórmulas se indica el nombre asignado. Seleccione ahora las celdas F2 a F14 y pulse en el campo Nombre de la Barra de fórmulas. También puede asignar un nombre a una celda o un rango introduciéndolo en el campo Nombre.

18 Éste se hace editable. Introduzca el texto IVA_totales y pulse la tecla Retorno.

19 También puede asignar un nombre a una celda o a un rango de celdas con este sencillo método, aunque en este caso no puede modificar el ámbito sino que se aplica automáticamente a todo el libro. Tampoco se puede añadir ningún comentario.

IMPORTANTE

También puede acceder al cuadro Nombre nuevo desde la opción Definir nombre del menú contextual de una celda o un rango de celdas seleccionado.