Cuatro formas de calcular la media ponderada

En el rango B2:C15 hemos registrado un número de personas agrupadas por edades. Queremos saber cuál es la edad media de este colectivo.

Primer método: Con celdas auxiliares

Usaremos la columna G para obtener resultados intermedios.

En G3:

=B3*C3    [Resultado: 1.127]

Extendemos la fórmula hasta la fila 15.

En E3:

=SUMA(G3:G15)/SUMA(B3:B15)    [Resultado 38,26]

Segundo método: Con una fórmula matricial

Podemos prescindir de la columna auxiliar almacenando los valores en una matriz. Luego, sumamos todos los valores de la matriz y dividimos el resultado por el número total de personas de la muestra. Todo en un solo paso.

En E3:

=SUMA(B3:B15*C3:C15)/SUMA(B3:B15)    [Terminamos con Ctrl + Mayúscula + Intro]

Tercer método: Con la función SUMAPRODUCTO para evitar la fórmula matricial

Ya hemos visto en otros ejercicios cómo la función SUMAPRODUCTO permite transformar una fórmula matricial en una normal. Bastará sustituir la primera SUMA por SUMAPRODUCTO.

En E3:

=SUMAPRODUCTO(B3:B15*C3:C15)/SUMA(B3:B15)    [Terminamos con Intro]

Cuarto método: Con tablas y SUMAPRODUCTO

Éste es el método más adecuado si prevemos que la lista de valores va a crecer (o disminuir). Partimos de la lista sin formato:

Accedemos a Insertar + Tabla y ponemos los datos siguientes:

Si abrimos la lista Cuadro de nombres comprobaremos que Excel ha creado un nombre llamado Tabla1.

La fórmula que vamos a usar es la misma que la del tercer método, pero adaptada al uso de las tablas. No escribiremos toda la fórmula; la construiremos así:

1. Escribimos: =SUMAPRODUCTO(
2. Con el ratón, seleccionamos B3:B15. Excel escribirá: =SUMAPRODUCTO(Tabla1[Nº personas]
3. Añadimos (;) punto y coma: =SUMAPRODUCTO(Tabla1[Nº personas];
4. De nuevo, con el ratón, seleccionamos C3:C15. El resultado será: =SUMAPRODUCTO(Tabla1[Nº personas];Tabla1[Edad]
5. Cerramos paréntesis y añadimos la barra de la división: =SUMAPRODUCTO(Tabla1[Nº personas];Tabla1[Edad])/
6. Escribimos: SUMA(
7. Seleccionamos B3:B15 y cerramos el paréntesis. Acabamos con Intro.
8. Resultado: =SUMAPRODUCTO(Tabla1[Nº personas];Tabla1[Edad])/SUMA(Tabla1[Nº personas])

Este método evita la necesidad de modificar la fórmula si añadimos o quitamos nuevas filas a la lista de datos. Para añadir nuevos datos al final, nos ponemos en la última fila y escribimos los valores. Si necesitamos insertar una fila entre la 7 y la 8, nos ponemos en la fila 8 (siempre en la inferior) y, con el botón derecho, abrimos el menú contextual. Eligiendo Insertar + Filas de la tabla arriba se insertará una fila en blanco donde podremos escribir los nuevos valores. Para eliminar una fila, por ejemplo, la 12, nos ponemos en una celda de la fila 12, abrimos el menú contextual y elegimos Eliminar + Filas de la tabla.

Descargar archivo (S129-Media.xlsx)

Descargar archivo (S129-Media.xls)

Transferir las configuraciones de la "Barra de acceso rápido" y de la "Cinta de opciones" a otro ordenador

Tanto la Barra de herramientas de acceso rápido como la Cinta de opciones se pueden configurar a nuestro gusto. Esta configuración puede transferirse a otro ordenador fácilmente copiando el fichero Excel.officeUI. Este asunto ya fue tratado en el artículo Personalizar la barra de herramientas de acceso rápido. Se busca el fichero (en mi ordenador está en C:\Users\Javi\AppData\Local\Microsoft\OFFICE) y se copia en la carpeta correspondiente del otro ordenador).

Puede que en el ordenador de destino se haya hecho una instalación personalizada de Office y no sepamos la carpeta exacta en la que debemos copiar el fichero. No es una tragedia; Excel dispone de un procedimiento semiautomático para hacer la operación.

Primer paso: Dejar que Excel extraiga el fichero de configuración

• Accedemos a Archivo + Opciones + Personalizar cinta de opciones.
• Abrimos la lista Importar o exportar.
• Elegimos Exportar todas las personalizaciones

• Seleccionamos la carpeta de destino y descargamos el fichero de configuración. Por defecto, Excel le pone de nombre: Personalizaciones de Excel.exportedUI.
• Copiamos el fichero en un pendrive, CD, disco duro externo... para llevarlo al otro ordenador.

Segundo paso: Dejar que Excel copie el fichero de configuración en el otro ordenador

• En el ordenador de destino, accedemos a Archivo + Opciones + Personalizar cinta de opciones.
• Abrimos la lista Importar o exportar.
• Elegimos Importar archivo de personalización.
• Buscamos el fichero en nuestro pendrive y aceptamos. Excel se encarga de dejar el fichero de configuración en el lugar correcto.

Comentarios con fotografías

Ya utilizamos los Comentarios en el artículo Ecuaciones, comentarios y cuadros de texto para poner información en una celda. Pero, además de texto, pueden mostrarse imágenes o fotografías que sirvan de ilustración y complemento a los datos de la celda. Así, en una lista de monumentos, podemos asociar a cada celda una fotografía que se mostrará al pasar el cursor por encima.

¿Cómo se hace?

Primero hay que conseguir las fotografías. Aquí están las utilizadas en el ejercicio.

Ahora, para mostrar el menú contextual, hacemos clic con el botón derecho en la celda que contiene el nombre de un monumento. Elegiremos la celda B3 (Monasterio de El Escorial).

Seleccionamos Insertar comentario. A la derecha de la celda se muestra un rectángulo con el nombre del usuario en su interior.

Borramos el nombre del usuario y, con el botón derecho, hacemos clic en el marco para mostrar el menú contextual. 

Eligiendo Formato de comentario se mostrará el correspondiente cuadro de diálogo. En la pestaña Colores y líneas, abrimos la lista Color y hacemos clic en Efectos de relleno.

En la pestaña Imagen de la nueva ventana hacemos clic en Seleccionar imagen.

Elegimos la fotografía del Monasterio de El Escorial y vamos cerrando todas las ventanas. La imagen insertada queda demasiado pequeña. Arrastramos los tiradores hasta ampliarla al tamaño deseado y repetimos los mismos pasos con el resto de las fotos.

Excel pone marcos sencillos a los comentarios, aunque podemos cambiarlos por otros extraídos de la lista de formas (autoformas en Excel 2003). Por ejemplo, al Museo Guggenheim de Bilbao le vamos a poner este marco:

Antes de empezar, es necesario habilitar el acceso a la herramienta Cambiar forma que, por defecto, no está visible en ningún menú.

Vamos a Archivo + Opciones + Barra de herramientas de acceso rápido. En la lista Comandos disponibles en, seleccionamos Todos los comandos. En el panel de la izquierda hacemos clic en Cambiar forma, pulsamos el botón Agregar (Cambiar forma pasará al panel de la derecha) y terminamos con Aceptar.

La Barra de herramientas de acceso rápido tendrá un nuevo icono.

Ya estamos en disposición de poder cambiar el marco del comentario.

Con el botón derecho pulsamos en la celda B9 y, en el menú emergente, elegimos Modificar comentario. Hacemos clic en el marco de la foto, abrimos la lista de formas del icono Cambiar forma y elegimos Doble onda.

Aún podemos hacer otro cambio. La nueva forma tiene a la izquierda y debajo unos tiradores amarillos con los que podemos cambiar el ondulado del marco y su inclinación.

Haciendo los cambios precisos, éste podría ser el... desafortunado resultado:

Al Museo del Prado le vendría mejor un marco que semejara el de un cuadro de la pinacoteca.

¿Cómo quedaría la Torre del Oro dentro de una nube?

Descargar archivo (S119-FotoComentarios.xlsx)

Descargar archivo (S119-FotoComentarios.xls)

Usar "Pegado especial" para hacer operaciones

En la entrada anterior hemos utilizado Pegado especial para copiar valores, fórmulas y formatos. Pero también se puede usar para realizar sumas, restas, multiplicaciones y divisiones.

Supongamos que tenemos la tabla de salarios de 5 empleados a los que debemos incrementar el sueldo un 5%. Para hacerlo, tendremos que multiplicar todos los salarios por 1,05.

Necesitamos una celda cualquiera para poner el incremento; por ejemplo, la celda E1.

 

En E1 escribimos: 1,05

 

Ahora, es necesario copiar ese valor en el portapapeles. Para ello, nos ponemos en E1 y pulsamos Ctrl + C.

 

Seleccionamos C3:C7 y, con el botón derecho, mostramos el menú contextual. Elegimos Pegado especial, marcamos el botón Multiplicar y pulsamos Aceptar.

 

Terminamos poniendo el formato de moneda y los bordes. Eso es todo.

 

El dato de E1 ya no nos hace falta. Lo eliminamos.

 

Naturalmente, en vez de Multiplicar podemos elegir Sumar, Restar o Dividir.

 

 

Intercambiar filas por columnas

Hay varios métodos para intercambiar las filas de una tabla por las columnas. Los ejemplos los haremos con la siguiente tabla:

En la columna E se han multiplicado los datos de las columnas C y D. La fórmula de E3 es: =C3*D3. El objetivo es colocar las frutas como encabezados de las columnas.

Método 1: Conservar el formato, los valores y las fórmulas.

• Seleccionamos el rango B2:E8 y pulsamos Ctrl + C para copiarlo en el portapapeles.
• Hacemos clic con el botón derecho en la celda G2 para mostrar el menú contextual y elegimos Pegado especial.
• En la ventana Pegado especial marcamos Formatos y Transponer.

La hoja habrá quedado así:

Ahora, debemos copiar los datos y las fórmulas.

• Seleccionamos de nuevo el rango B2:E8 y pulsamos Ctrl + C.
• Volvemos a hacer clic con el botón derecho en G2 y elegimos Pegado especial.
• Marcamos Fórmulas y Transponer.

Podemos comprobar que en H5:M5 se han copiado las fórmulas de la columna E debidamente modificadas para calcular el producto del precio y la cantidad de cada fruta.

Si no quisiéramos conservar las fórmulas y deseáramos mostrar únicamente los valores, en la última operación tendríamos que haber marcado Valores y Transponer.

Si utilizamos este método, las dos tablas serán totalmente independientes; es decir, la modificación de una tabla no afectará a la otra.

Método 2: Mantener la vinculación entre las dos tablas.

Cuando queramos que la modificación de la primera tabla tenga efecto en la segunda, esta última tendremos que crearla mediante fórmulas. La opción más sencilla es mediante la función TRANSPONER.

El primer paso es igual que en el caso anterior.

• Seleccionamos B2:E8 y pulsamos Ctrl + C.
• Hacemos clic con el botón derecho en la celda G2 y elegimos Pegado especial.
• En la ventana Pegado especial marcamos Formatos y Transponer. 

TRANSPONER es una función matricial. Se usa así:

• Seleccionamos G2:M5
• Escribimos: =TRANSPONER(B2:E8)    [Se termina con Ctrl + Mayúscula + Intro]

Poniéndonos en cualquier celda del rango G2:M5, si nos fijamos en la barra de fórmulas, veremos que pone: {=TRANSPONER(B2:E8)} 

Descargar archivo (SE133-Transponer.xlsx)

Descargar archivo (SE133-Transponer.xls)

Cálculo condicionado por un valor de referencia

Las celdas B3 y C3 contienen valores numéricos. En la celda D3 hay un número de referencia. En F3 debemos calcular el producto de B3 y C3 siempre que el número de B3 sea mayor que el valor de referencia de la celda D3. En caso contrario F3 conservará el mismo valor. En G3 indicaremos cuál ha sido el último producto que se ha realizado.

A primera vista, parece un problema sencillo. Veamos si es tan simple. Comenzaremos poniendo algunos valores en las celdas.

En B3: 45

En C3: 100

En D3: 25

Estos serán los datos iniciales con los que vamos a probar nuestra fórmula.

En F3:

=SI(B3>D3;B3*C3;F3)    [Resultado: 4.500,00]

Parece que todo va bien. La fórmula nos dice que si B3 es mayor que D3 se multipliquen los valores de las celdas B3 y C3. Como se da esta circunstancia, Excel calcula el producto y devuelve el resultado correcto.

Pero, si cambiamos el valor de B3 por 4, se produce un error de referencia circular:

En este momento no se da la circunstancia anterior y la fórmula nos dice que en F3 se debe poner el valor de F3. Esto produce el error ya que en esa celda no hay un valor sino una fórmula. El cálculo es imposible.

¿Significa esto que la fórmula es incorrecta? No. Lo que ocurre es que deberíamos haber habilitado el cálculo iterativo y no lo hemos hecho.

Lo haremos ahora.

Elegimos Archivo + Opciones + Fórmulas. Ponemos una marca en Habilitar cálculo iterativo, un 1 en Iteraciones máximas y un 0 en Cambio máximo.

En realidad, podríamos poner cualquier valor en los dos últimos apartados, pero no es lógico realizar muchas iteraciones si con una basta. Por otro lado, el Cambio máximo no interviene en este caso.

En G3 pondremos otra fórmula para que se sepa cuál ha sido la última multiplicación realizada. También contendrá una referencia circular, que funcionará perfectamente porque el cálculo iterativo está habilitado.

En G3:

=SI(B3>D3;B3&" x "&C3;G3)

Cambiando los valores de las celdas B3, C3 y D3 comprobaremos que siempre se cumplen las condiciones impuestas en el enunciado del ejercicio.

Descargar archivo (S60-ProCond.xlsx)

Descargar archivo (S60-ProCond.xls)

Filtrar valores

Hay varias maneras de filtrar valores en Excel. Para estudiarlas, partiremos de una lista de dos columnas: la primera será un listado de futbolistas y la segunda el país de nacimiento.

Método 1: Aplicación de un filtro a la lista

Hacemos clic en cualquier celda de la tabla y seleccionamos Datos + Filtro. En la parte derecha de los encabezados de las columna aparecen sendas flechas. Abrimos el menú asociado al encabezado País y elegimos uno; por ejemplo, Brasil. 

Método 2: Tablas

Hacemos clic en cualquier celda de la lista y seleccionamos Insertar + Tabla. Excel muestra el siguiente cuadro de diálogo:

Nos aseguramos que los datos coincidan con los de la figura y pulsamos Aceptar.

Parece que tenemos otra lista con filtros, pero una tabla es mucho más. Por ejemplo, una tabla lleva asociado un nombre de Excel. Para comprobarlo, accedemos a Fórmulas + Administrador de nombres.

El nombre asignado por defecto es Tabla1. Para cambiarlo, seleccionamos Tabla1, pulsamos Editar y en cuadro de diálogo cambiamos el nombre por Futbolistas (o el que queramos).

Para filtrar los jugadores de Brasil, actuamos igual que con los filtros de lista. En este ejercicio no vamos a analizar las ventajas de las tablas con respecto a las listas.

Método 3: Tablas dinámicas

He escrito más de 80 artículos en este blog y nunca me había referido a las tablas dinámicas hasta ahora. Tiempo habrá para ello y, por el momento, sólo voy a hacer una somera mención.

Para crear una tabla dinámica debemos hacer clic en cualquier celda de la lista y seleccionar Insertar + Tabla dinámica. Dejando los datos de la siguiente figura, crearemos la tabla dinámica a partir de la celda B2 de la Hoja3.

La Hoja3 queda así:

Arrastramos los campos País y Futbolista al área Etiquetas de fila.

Podemos cambiar el aspecto; por ejemplo, seleccionando Herramientas de tabla dinámica + Diseño + Diseño de informe + Mostrar en forma de esquema, obtenemos:

Ahora, basta aplicar el filtro que queramos a la columna País y obtendremos los nombres de los futbolistas del país elegido.

En este caso, el Total general no tiene sentido y puede eliminarse accediendo a Herramientas de tabla dinámica + Diseño + Totales generales + Activado sólo para filas.

Método 4: Filtrado mediante fórmulas

Los métodos anteriores requieren que el usuario aplique el filtro manualmente. Si no queremos que el usuario manipule nada tendremos que usar fórmulas.

El primer paso consiste en crear una tabla con la lista de datos, llamada Futbolistas, como se ha explicado en el Método 2.

A continuación, crearemos una tabla dinámica a partir de la celda F2 de la Hoja2. Esta tabla se hace como se ha explicado en el Método 3 pero, en este caso, sólo arrastraremos el campo País al área Etiquetas de fila. Podemos quitar el Total general pero no es necesario.

Esta tabla la usaremos para crear la lista desplegable que vamos a poner en la celda E2 de la Hoja1.

Estando en la celda E2 de la Hoja1, accedemos a Datos + Validación de datos y ponemos los datos de la siguiente figura:

En E2 se ha creado la lista desplegable de todos los países. Elegimos Brasil (u otro cualquiera) y nos vamos a la Hoja2. En esta hoja haremos los cálculos necesarios para obtener los jugadores de Brasil.

En B1:

=Hoja1!E2     [Resultado: Brasil]

En B3:

=SI(Futbolistas[@País]=$B$1;Futbolistas[@Futbolista];"")

En realidad no es necesario escribir la fórmula completa. Es mejor hacerlo así:

• Escribimos =SI(
• Hacemos clic en la celda C3 de la Hoja1   {Resultado: =SI(Futbolistas[@País]}
• Escribimos =$B$1;
• Hacemos clic en la celda B3 de la Hoja1   {Resultado: =SI(Futbolistas[@País]=$B$1;Futbolistas[@Futbolista]}
• Escribimos ;"")

De este modo, resulta más difícil equivocarse.

Arrastramos el controlador de relleno para copiar la fórmula hasta la fila 57.

En la Hoja2, seleccionamos B2:B57 y creamos una tabla de nombre País.

En C3:

=SI(País[@País]="";"";CONTAR.SI(País[País];"<="&País[@País])-CONTAR.BLANCO(País[País]))

Como en el caso anterior, es conveniente introducir esta fórmula así:

• Escribimos =SI(
• Hacemos clic en B3
• Escribimos ="";"";CONTAR.SI(
• Seleccionamos B3:B57
• Escribimos ;"<="&
• Hacemos clic en B3
• Escribimos )-CONTAR.BLANCO(
• Seleccionamos B3:B57
• Escribimos ))

Arrastramos el controlador de relleno hasta la fila 57.

Con el rango C3:C57 creamos otra tabla de nombre Orden.

En D3:

=SI.ERROR(INDICE(Futbolistas[Futbolista];COINCIDIR(FILA(A1);Orden[Orden];0));"")

De nuevo, introduciremos esta fórmula así:

• Escribimos =SI.ERROR(INDICE(
• Seleccionamos el rango B3:B57 de la Hoja1
• Escribimos ;COINCIDIR(FILA(A1);
• Seleccionamos el rango C3:C57 de la Hoja2
• Escribimos ;0));"")

Arrastramos el controlador de relleno hasta la fila 57.

Repetimos esta fórmula en la columna D de la Hoja1 y el ejercicio estará terminado.

Descargar archivo (SE56-Futbolistas.xlsx)