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

EL ADMINISTRADOR DE PROYECTOS REUNE todos los nombres asignados en el libro abierto. Desde él puede crear nuevos nombres, así como editar o eliminar los que han sido creados previamente.

IMPORTANTE

El Administrador de nombres le permite crear un nuevo nombre desde este cuadro, editar el nombre seleccionado, borrarlo o cambiar el rango de celdas al que se refiere. Además, si pulsa en el botón que está a la derecha del campo Se refiere a, el cuadro se minimiza para permitirle seleccionar un nuevo rango de celdas.

1 En este ejercicio trabajaremos con otras funciones del grupo Nombres definidos de la Cinta de opciones. Pulse el botón Administrador de nombres de este grupo.

2 En el Administrador de nombres que se ha abierto en pantalla puede ver los tres nombres creados en el ejercicio anterior. Seleccione el nombre IVA_totales.

3 Ahora podría eliminarlo con sólo pulsar el botón Borrar. En lugar de ello pulse el botón Editar. El Administrador le permite trabajar con los nombres creados, así como crear nuevos nombres.

4 Se abre el cuadro Editar nombre, que ahora le permitiría cambiar el nombre o el rango de celdas al que se refiere, pero no su ámbito. Ahora no lo modificaremos, así que cancele el cuadro y cierre el Administrador.

5 Pulse en la celda A10, introduzca el texto IVA y pulse la tecla Tabulador para aplicar y pasar a la celda contigua.

6 Introduzca la fórmula =IV.

7 La lista Autocompletar fórmula que ha aparecido le permitiría pegar cualquiera de los dos nombres que comienzan por las letras IV. Seleccione el nombre IVA y aplique la fórmula.Para aplicar el comando Crear desde selección, es necesario seleccionar la celda o el rango al que se le aplicará el nombre y la o las celdas que aportarán nombres.

8 Se inserta el valor de la celda IVA. Introduzca en la celda A11 el texto Total IVA y pulse Tabulador.

9 Pulse en el botón Autosuma para insertar la fórmula SUMA y despliegue el menú Utilizar en la fórmula del grupo Nombres definidos de la ficha Fórmulas. En la lista Autocompletar fórmula aparece junto al nombre una etiqueta que muestra el comentario asignado al nombre.

10 Sólo puede escoger entre los dos nombres a los que asignó como ámbito el libro. El tercero, cuyo ámbito era la hoja de cálculo Lista Precios y cantidades, no está disponible. Seleccione la opción Pegar nombres del menú Utilizar en la fórmula.

11 En el cuadro Pegar nombre, seleccione el nombre IVA_totales y pulse el botón Aceptar.

12 El rango se aplica a la fórmula y hubiera obtenido el mismo resultado si hubiera escogido el nombre desde en el menú Utilizar en la fórmula. Aplique la fórmula.

13 Introduzca en la celda B2 el texto Sin descuento, pulse en la celda B1, pulse Mayúsculas y sin soltarla, pulse en la celda O2.

14 Pulse en el botón Crear desde la selección del grupo Nombres definidos de la Cinta de opciones.

15 En el cuadro de diálogo del mismo nombre, seleccione las opciones Fila superior y Columna izquierda y pulse el botón Aceptar.

PUEDE USAR UN NOMBRE DE CELDA o rango de celdas fuera de su ámbito, pero para ello debe anteponer a su nombre, el nombre de la hoja en la que se encuentra seguido por un signo de admiración de cierre. También aplicar a una fórmula los nombres que hayan sido introducidos posteriormente a su creación usando el comando Aplicar nombres de la Cinta.

IMPORTANTE

Al crear un nombre o un grupo de nombres desde una selección usando el comando correspondiente, se omiten en el rango creado, la fila y la columna que contienen las datos propuestos para ser usados como nombres.

1 Abra nuevamente el Administrador de nombres para comprobar los nombres que creó en el ejercicio anterior usando el comando Crear desde la selección.

2 Expanda el cuadro (desde su esquina inferior derecha) y la columna Nombre (por el borde derecho de su cabecera).

3 Analice el primer nombre, que debe ser Acuarelas_Bijo_12_col. En la columna Se refiere a nos indican que ese nombre ha sido asignado a la celda K2 de la hoja Descuentos. En la columna Valor, que el valor de la celda es 2,50 €. Del mismo modo, se ha creado un nombre con cada uno de los artículos de la fila 1, asignado a las celdas respectivas en la fila 2. Además, se ha creado un rango de la celda D2 a O2 llamado Sin_descuento. Cierre el cuadro.

4 Cambie el nombre de la hoja Lista Precios y cantidades por Ventas1. Modificamos el nombre de la hoja para hacer más cómoda su utilización en fórmulas.

5 Puede cambiar el nombre de la hoja sin temor, que éste se actualizará automáticamente en las referencias a sus contenidos de los nombres creados previamente. Introduzca en la celda A12 de la hoja Descuentos el texto Total sin IVA y pulse la tecla Tabulador.

6 Pulse el botón Autosuma de la Biblioteca de funciones de la ficha Fórmulas.

7 Puede usar un nombre fuera de su ámbito si indica a qué hoja corresponde. Para ello debe anteponer al nombre de la celda o rango, el nombre de la hoja de cálculo a cuyo ámbito está limitado, seguido de un signo de admiración de cierre (!). Introduzca la siguiente fórmula entre los paréntesis de la función SUMA: Ventas1!Total_€_sin_IVA y aplíquela.

8 Se suman los datos del rango y la hoja indicados. Regrese a la hoja Lista Precios y cantidades, seleccione las celdas F2 a F14, despliegue el menú del comando Asignar nombre del grupo Nombres definidos de la cinta y escoja la opción Aplicar nombres.

9 Mantenga seleccionados todos los nombres del panel Aplicar nombres del cuadro de diálogo abierto y pulse el botón Aceptar.

10 Seleccione cualquier celda F8 y compruebe en la Barra de fórmulas que la referencia a la celda ha sido sustituido por el nombre del rango (Total_€_sin_IVA). Para cada fórmula de la columna F este nombre tiene el valor que corresponde de la columna E2 ha sido sustituida por el nombre del rango, que en este caso actúa como una referencia relativa.

En una fórmula con varios operadores, los cálculos no se ejecutan siguiendo el orden de la fórmula, sino que se calculan primero ponencias, luego multiplicaciones y divisiones, y finalmente sumas y restas, cada uno de izquierda a derecha. Pero si coloca algún contenido entre paréntesis, se calcula éste primero. Si una fórmula contiene varios operadores con el mismo orden de precedencia y sin paréntesis, se calculan de izquierda a derecha.

IMPORTANTE

Un paréntesis debe necesariamente tener una pareja. De lo contrario, al intentar aplicar la fórmula la aplicación arrojará un mensaje de error y una propuesta de corrección. Deberá corregir el error para poder aplicar la fórmula.

1 Continuamos trabajando en el libro Precios y cantidades. Seleccione cualquier celda vacía de la hoja Ventas1, introduzca la fórmula =5+2*3 y pulse la tecla Retorno.

2 El resultado es 11. En celda siguiente, introduzca la fórmula =(5+2)*3 y pulse de nuevo la tecla Retorno. Un par de paréntesis puede hacer que el resultado de una fórmula cambie drásticamente.

3 El resultado es 21. La diferencia se debe a que, en la primera fórmula, la aplicación ha multiplicado 2 por 3 siguiendo el orden establecido, y al resultado le ha sumado 5. En la segunda fórmula, en cambio, ha realizado primero la operación situada entre paréntesis y luego ha multiplicado el resultado por 3. Vamos ahora a calcular el total del día 9 de marzo. Introduzca la fórmula =SUMA(G2:G24) y pulse Retorno.

4 El total es de 4.961,29. Vamos ahora a llegar al mismo cálculo de forma manual. Primero, sumemos los tres totales sin IVA del día. Escriba la fórmula =E2+E3+E4.

5 Luego añadimos un signo + y de inmediato introducimos la fórmula para calcular el IVA correspondiente. Escriba las cantidades a las que aplicaremos el IVA: +E2+E3+E4.

6 Lo multiplicamos por el IVA. Añada el texto *IVA.

7 Para acabar de calcular el IVA que corresponde a la cantidad sumada, dividimos entre 100. Escriba /100 y pulse Aplicar.

8 El resultado, ya se lo habrá imaginado, es incorrecto, porque aunque las sumas son las primeras operaciones de la fórmula, Excel ha ejecutado primero, como corresponde, la multiplicación y la división. Haga un doble clic en la celda G20 para modificar esta larga fórmula que acaba de crear.

9 Los paréntesis, además de modificar el orden, sirven para hacer más claras las fórmulas complejas. Encierre entre paréntesis las tres primeras variables de la fórmula (E2+E3+E4).

10 Repita el proceso para encerrar también entre paréntesis la siguiente suma, idéntica a la anterior y pulse Retorno.

11 El resultado es el que buscamos. Haga un doble clic sobre la fórmula para editarla de nuevo.

12 Crearemos dos paréntesis más que encierren la multiplicación. Siempre se ejecutan primero los paréntesis de mayor profundidad. La segunda parte de la fórmula debe quedar así: ((E2+E3+E4)*IVA), con paréntesis dobles y pulse Retorno.

13 La fórmula se hace clara pero el resultado en este caso es el mismo: da igual que realice la suma, luego multiplique el resultado por el IVA y divida finalmente entre 100, a que multiplique la suma por 0,18. Edite nuevamente la fórmula para encerrar entre paréntesis todo el cálculo del IVA aplicado.

14 Para ayudarle a identificar las parejas de paréntesis, cambian sus colores temporalmente. Aplique y compruebe que el resultado permanece intacto una vez más.

15 Pulse Ctrl.+Z para eliminar el último par de paréntesis y encierre ahora entre paréntesis todo el contenido desde el comienzo de la fórmula hasta la palabra IVA. Aplique, compruebe cómo ahora sí cambia el resultado y pulse Ctrl.+Z.

IMPORTANTE

Trabajar con diversos pares de paréntesis que anidan fórmulas en varios niveles de profundidad puede resultar confuso a pesar de que la aplicación utiliza distintos colores para ayudarle a diferenciarlos. Sepa que si coloca el cursor sobre un paréntesis, éste y su pareja se verán en negrita por un momento.

LLAMAMOS CELDAS PRECEDENTES A AQUELLAS a las que hace referencia una fórmula y Celdas dependientes a aquellas que son utilizadas en las fórmulas de otras celdas. Con los comandos del grupo Auditoría de fórmulas de la ficha Fórmulas de la Cinta de opciones, resulta muy fácil ubicar a unas y a otras de manera gráfica, no importa lo compleja que pueda ser la hoja de cálculo con la que está trabajando.

1 En este ejercicio comenzaremos trabajando con la hoja Ventas1 del archivo Precios y cantidades, para conocer los comandos del grupo Auditoría de fórmulas que nos permiten mostrar en la hoja la vinculación entre celdas con fórmulas. Seleccione la celda F2.

2 Pulse sobre el comando Rastrear precedentes del grupo Auditoría de fórmulas.

3 Automáticamente aparecen dos flechas azules que parten de la celda seleccionada y van hasta las dos celdas utilizadas en la fórmula que esta contiene.

4 Compruebe en la Barra de fórmulas que efectivamente la celda G18 depende del Total sin IVA y de la celda IVA. También es posible mostrar gráficamente las fórmulas a las que nutre una celda con datos o, lo que es lo mismo, mostrar las celdas dependientes. Haga clic en la celda G3.En la Barra de fórmulas puede comprobar que las celdas señaladas como precedentes son las utilizadas en la fórmula de la celda analizada.

5 Pulse ahora el comando Rastrear dependientes del grupo Auditoría de fórmulas. El comando Rastrear dependientes muestra en qué fórmulas ha sido usada la referencia a la celda seleccionada.

6 En este caso la flecha azul señala las celdas E18 y F26, donde la celda seleccionada es uno de los valores implicados en la fórmula. Podríamos comprobarlo pulsando directamente sobre cada una de estas celdas y leyendo la fórmula en la Barra de fórmulas pero en esta ocasión, utilizaremos otro de los comandos de auditoría de fórmulas. Haga clic sobre el comando Mostrar fórmulas, situado a la derecha del comando Rastrear precedentes.

7 Se muestra en cada celda la fórmula que contiene en lugar de su resultado. Para desactivar el comando Mostrar fórmulas, pulse la combinación de teclas Alt+o.

8 Seleccione la celda B11 de la hoja Descuentos y pulse el botón Rastrear precedentes.

9 El icono al que se dirige la flecha que ha aparecido nos indica que la celda que precede a la seleccionada está en otra hoja. Despliegue el comando Quitar flechas del grupo Auditoría de fórmulas.

10 Seleccione la opción Quitar un nivel de precedentes.

11 La flecha desaparece. Regrese a la hoja Ventas 1 y pulse esta vez directamente en el botón Quitar flechas para borrar todas las flechas de la hoja, que son las únicas que quedan.

12 Para acabar este ejercicio, guarde los cambios pulsando el comando Guardar de la Barra de herramientas de acceso rápido.

IMPORTANTE

El resto de comandos incluidos en el grupo Auditoría de fórmulas nos permiten localizar errores comunes en fórmulas y depurarlas evaluando cada una de sus partes.

EXCEL TAMBIÉN PERMITE CREAR REFERENCIAS EXTERNAS, es decir, vínculos a datos de otro libro. De este modo los vínculos es que se actualizan pues el archivo de origen y el archivo de destino se mantienen conectados.

1 En este ejercicio utilizaremos, además del libro en el que venimos trabajando, el llamado Resumen.xlsx, así que descárguelo de nuestra web y ábralo.

2 Con el libro Precios y cantidades en segundo plano, muestre la ficha Vista y pulse en el botón Vista en paralelo, que está a la derecha del botón Dividir del grupo Ventana.

3 Ahora puede ver ambos archivos simultáneamente y será más fácil realizar el ejercicio, aunque podría hacerlo perfectamente saltando de un archivo al otro. Seleccione la celda E16 de la hoja Ventas1 en el libro Precios y cantidades y pulse Ctrl.+C para copiarla.

4 En la Hoja 1 del archivo Resumen, ubíquese en la celda B3.

5 Active la ficha Inicio, despliegue el comando Pegar del grupo Portapapeles y, de la lista de opciones de pegado que aparece, seleccione la opción Pegar vínculo, el segundo icono de la sección Otras opciones de pegado. La opción Pegar vínculo crea automáticamente una referencia al origen del contenido del Portapapeles.

6 Se pega el valor de la celda y observe la Barra de fórmulas: aparece entre corchetes el nombre del libro, seguido del nombre de la hoja. Ambos datos están entre comillas simples y, inmediatamente después de éstas, un signo de admiración de cierre y la referencia de la celda pegada. Ésta es la sintáxis que deberá usar para teclear una referencia externa.

7 Vamos a comprobar el vínculo creado. Haga doble clic sobre la celda B2 de la hoja activa en el libro Precios y cantidades, añada el texto -5000 y pulse Retorno. También puede introducir una referencia externa desde su teclado.

8 Regrese al libro Resúmenes y compruebe que en la celda B3, donde está la referencia externa, el contenido ha cambiado al modificar la celda precedente.

9 Pulse ahora en la celda B4 e introduzca un signo igual.

10 Pulse la celda D17 de la hoja Ventas1 y observe cómo de inmediato se crea la referencia correspondiente, manteniendo la misma sintaxis que al usar la función Pegar vínculo.

 

11 Pulse ahora en la celda B5 de la hoja activa en el libro Resúmenes y pulse en el botón Autosuma de la Biblioteca de funciones de la ficha Funciones.

12 En el libro Ventas, seleccione con un arrastre de ratón las celdas E16 a E17.

13 Observe la sintaxis usada en este caso. Comienza, evidentemente, con el signo =, la función SUMA y el paréntesis de apertura. La referencia al libro y a la hoja es idéntica a los casos anteriores, y la diferencia es que después del signo de admiración de cierre, no se añade una referencia a celda sino la referencia del rango, en este caso una referencia absoluta.

14 Borre el texto -5000 la celda D16 de la hoja Ventas1.

15 Guarde los cambios y pulse de nuevo en el botón Vista en paralelo para desactivarla.

IMPORTANTE

Las comillas simples de las referencias creadas en este ejercicio no se deben a que se trata de una referencia externa sino a que el nombre del libro de origen (Precios y cantidades) contiene caracteres no alfabéticos, en este caso dos espacios en blanco. También tendría que usar las comillas simples para crear una referencia a una Hoja 1 en una Hoja 2, por ejemplo y por el mismo motivo.

SI UNA FÓRMULA UTILIZA LA CELDA que la contiene como uno de sus parámetros, ya sea de forma directa o indirecta, se trata de una referencia circular. Normalmente, una referencia circular produce un error en Excel; sin embargo, es posible establecer que las referencias circulares sean permitidas.

IMPORTANTE

Si inserta, antes de permitir las iteraciones, una referencia circular indirecta como la de nuestro segundo ejemplo (donde el valor de la celda E3 está calculado a partir del valor de la propia celda E2) unas flechas azules indicarán cuáles son las celdas en conflicto.

1 Seleccione la celda I9, introduzca la fórmula =B3+I9 y confirme la entrada pulsando sobre el botón Introducir.

2 Un mensaje de advertencia le indica que está utilizando una referencia circular, es decir, una fórmula que toma su contenido como parte del cálculo. Pulse el botón Aceptar.

3 Aparece el término Referencias circulares seguido del nombre de la celda que la contiene en la Barra de estado. El resultado de la celda es 0 porque Excel realmente no es capaz de utilizar referencias circulares, si no activa la opción apropiada. Haga clic en la pestaña Archivo, pulse sobre el comando Opciones y seleccione la categoría Fórmulas.

4 Marque la casilla Habilitar cálculo iterativo del apartado Opciones de cálculo, cambie las Iteracciones máximas a 1 y pulse el botón Aceptar.

5 Al permitir las iteraciones, el resultado de I9 cambia a 5, pues a su valor, que era 0, se le ha sumado 5. Seleccione la celda B3, cambie su valor por 10 y pulse Retorno.

6 En I9, el valor 15 es el resultado de añadir el nuevo contenido de la celda B3 al que ya contenía I9. Ahora calcularemos el precio sin descuento de un producto a partir del precio con 10% de descuento. Introduzca en la celda E2 la fórmula =E5*100/90 y pulse el botón Introducir.La fórmula introducida en E2 contiene una referencia circular indirecta, que es E5, porque E5 ha sido calculada a partir del valor de E2.

7 Aunque E5 está calculada a partir de E2, la aplicación es capaz de hacer el cálculo usando el resultado actual de E5 y el resultado es correcto: 1,75. Pero ¿qué ha sucedido en I9?

8 Si trabaja con referencias, circulares puede obtener resultados imprevistos al modificar el contenido de cualquier celda. Aunque no ha modificado la celda I9, ésta se ha vuelto a sumar al resultado anterior, pues al modificar una celda se recalculan todas las fórmulas de la hoja de cálculo.

9 En el grupo Calculo de la ficha Fórmulas de la Cinta de Opciones, puse el botón Opciones para el cálculo y seleccione la opción Manual. Al activar el cálculo manual, la aplicación sólo recalcula las fórmulas del libro cuando el usuario pulsa la tecla F9 o pulsa los botones Calcular ahora o Calcular hoja ahora, ubicados a la derecha del botón Opciones para el cálculo de la Cinta de opciones.

10 Ahora Excel no realizará ningún cálculo hasta que usted le indique que debe hacerlo. Para ello deberá pulsar la tecla F9. Hágalo y compruebe cómo el contenido de I9 cambia a 35.

11 Seleccione la celda G3 y pulse F2 y F9 para reemplazar su fórmula por su valor absoluto, que es 1,75.

12 Cambie el valor de B3 por 5 nuevamente.

13 Abra el cuadro Opciones de Excel, reactive el cálculo Automático, deshabilite el cálculo iterativo y acepte el cuadro.

14 Después de aceptar la advertencia sobre referencias circulares, borre el contenido de la celda I9.

IMPORTANTE

El grupo Cálculo de la ficha Fórmulas de la Cinta de opciones contiene, además del botón Opciones para el cálculo, que le permite establecer si este debe ser Manual, Automático o Automático excepto en las tablas de datos, dos botones que le permiten calcular toda la hoja o calcular todo el libro cuando la opción seleccionada es Manual.