Sustituir las barras de un gráfico por una imagen

Los diagramas de barras (o columnas) pueden personalizarse sustituyendo las barras por imágenes adecuadas al caso. Por ejemplo, para representar la masa forestal de un conjunto de países mediante un diagrama de columnas, podemos usar la imagen de un árbol y mostrar el siguiente gráfico:

Lo primero será conseguir la imagen de un árbol (aquí hay uno).

Seleccionamos B2:C8 y accedemos a Insertar + Columna + Columna agrupada. Eliminamos la leyenda y sustituimos el título por: Superficie forestal en miles de hectáreas. Reducimos el tamaño de la fuente a 12 puntos.

Hacemos doble clic en una columna para acceder al cuadro de diálogo Formato de serie de datos. En la opción Relleno, marcamos el botón Relleno con imagen o textura, hacemos clic en Archivo y seleccionamos nuestro árbol. Eso es todo.

Nota: En Excel 2003 hacemos clic en una columna, accedemos a Insertar + Imagen + Desde archivo y seleccionamos el árbol.

Descargar archivo (SE24-Superficie forestal.xls)

Descargar archivo (SE24-Superficie forestal.xlsx)

Búsqueda compleja en una tabla

Una editorial publica cinco revistas de otros tantos deportes: boxeo, ciclismo, fútbol, natación y tenis. Los clientes se suscriben a una o más revistas y los datos se almacenan en una tabla (B2:H10):

El objetivo del ejercicio es elegir un cliente (C12) y obtener su número de registro (G12) y la lista de revistas a las que está suscrito (C14:G14).

Como en otras ocasiones, resolveremos el problema usando celdas auxiliares (J3:N9); luego, agruparemos todas las fórmulas en una fórmula compuesta. Las celdas auxiliares ya no serán necesarias y las podremos eliminar.

Combinamos las celdas C12 y D12 dejando espacio suficiente para los nombres (Inicio + Combinar y centrar). Justificamos el texto a la izquierda.

Hacemos clic en C12, accedemos a Datos + Validación de datos y asociamos la lista de nombres (B3:B10) a la celda combinada:

Elegimos un nombre cualquiera; por ejemplo, Juan Gómez

En G12 ponemos el número de afiliado del suscriptor seleccionado:

=BUSCARV(C12;B3:C10;2;FALSO)     [Resultado: 1389]

En K3 obtendremos el número de la fila en la que está la persona elegida:

=COINCIDIR(C12;B3:B10;0)+2     [Resultado: 6]

Ahora, debemos comprobar qué celdas del rango D6:H6 contienen una "X" (son las suscripciones del afiliado). El rango D6:H6 lo formaremos con DIRECCION; así, DIRECCION(K3;4) devolverá $D$6; DIRECCION(K3;8) devolverá $H$6. Combinando ambas fórmulas de esta manera: DIRECCION(K3;4)&":"&DIRECCION(K3;8), obtendremos $D$6:$H$6. Con INDIRECTO obtenemos los valores de las celdas, y con HALLAR comprobaremos qué celdas tienen "X"; las que tengan "X" devolverán un 1; las restantes, devolverán un error.

Seleccionamos J5:N5 y escribimos:

=HALLAR("X";INDIRECTO(DIRECCION(K3;4)&":"&DIRECCION(K3;8)))     [Terminamos con Ctrl + Mayús + Intro]

En la siguiente fila numeraremos (del 1 al 5) las celdas en las que hemos encontrado una "X".

Seleccionamos J6:N6 y escribimos:

=SI(ESNUMERO(J5:N5);{1\2\3\4\5};"")     [Terminamos con Ctrl + Mayús + Intro]

Con esta fórmula matricial comprobamos si hay un número en cada celda del rango J5:N5 y, en caso de haberlo, le asignamos el valor correspondiente de la matriz {1\2\3\4\5}.

En J7:N7 obtenemos el número menor, el segundo menor, el tercero... de los números de rango J6:N6.

Seleccionamos J7:N7 y escribimos:

=K.ESIMO.MENOR(J6:N6;{1\2\3\4\5})      [Terminamos con Ctrl + Mayús + Intro]

Comparando esta lista de números con los valores del encabezado (D2:H2) obtendremos las revistas a las que se ha suscrito el cliente.

Seleccionamos J8:N8 y escribimos:

=INDICE(D2:H2;1;J7:N7)      [Terminamos con Ctrl + Mayús + Intro]

Seleccionamos J9:N9 y escribimos la primera fórmula compuesta:

=INDICE(D2:H2;1;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR("X";INDIRECTO(DIRECCION(COINCIDIR(C12;B3:B10;0)+2;4)&":"&DIRECCION(COINCIDIR(C12;B3:B10;0)+2;8))));{1\2\3\4\5};"");{1\2\3\4\5}))      [Terminamos con Ctrl + Mayús + Intro]

La fórmula definitiva la pondremos en C14:G14. Utilizaremos la función SI.ERROR para eliminar los valores #¡NUM! que hay en J9:N9.

Seleccionamos C14:G14 y escribimos la segunda y definitiva fórmula compuesta:

=SI.ERROR(INDICE(D2:H2;1;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR("X";INDIRECTO(DIRECCION(COINCIDIR(C12;B3:B10;0)+2;4)&":"&DIRECCION(COINCIDIR(C12;B3:B10;0)+2;8))));{1\2\3\4\5};"");{1\2\3\4\5}));"")      [Terminamos con Ctrl + Mayús + Intro]

Esta fórmula es definitiva; no se necesitan las columnas J a N. Podemos eliminarlas tranquilamente.

Nota: Como en Excel 2003 no existe la función SI.ERROR, podemos utilizar el formato condicional para poner a las celdas que contienen #¡NUM! el mismo color que el del fondo; de este modo, no se verá el código de error.

Si no queremos usar fórmulas matriciales y no nos importa conservar las celdas auxiliares, podemos resolver el ejercicio de la manera que se muestra a continuación:

En C12, G12 y K3 mantenemos las mismas fórmulas y elegimos a Martín Redondo.

En J5:

=SI(INDIRECTO(DIRECCION($K$3;COLUMNA(D1)))="X";D2;"")

Extendemos la fórmula hacia la derecha.

Martín Redondo se ha suscrito a las revistas de ciclismo, natación y tenis.

En J6:

=(J5<>"")*COLUMNA(D2)

Extendemos la fórmula hacia la derecha.

Hemos obtenido las columnas en las que están los nombres de las revistas de Martín Redondo.

En J7:

=JERARQUIA(J6;J6:N6;0)

Extendemos la fórmula hacia la derecha.

Hemos ordenado jerárquicamente la fila anterior.

En J8:

=INDICE($J$5:$N$5;0;COINCIDIR(COLUMNA(A1);$J$7:$N$7;0))

Extendemos la fórmula hacia la derecha.

Ya hemos conseguido los nombres de las revistas, aunque en orden invertido con respecto al original. Sólo nos falta eliminar el error #N/A. Lo haremos en C14:G14.

En C14:

=SI.ERROR(J8;"")

Extendemos la fórmula hacia la derecha.

Descargar archivo (S21-Fórmula compleja.xls)

Descargar archivo (S21-Fórmula compleja.xlsx)

Gráfico de líneas con fechas en el eje X a distancias proporcionales

Algunos terremotos importantes acaecidos en 2010 y 2011 han sido registrados en una hoja de cálculo (B2:D12) y, con ellos, queremos hacer el gráfico de líneas que se muestra a continuación:

En el eje horizontal del gráfico irán las fechas en las que se produjeron los terremotos, separadas por distancias proporcionales a las diferencias que hay entre ellas.

Necesitaremos usar las columnas M, N, P y Q para almacenar valores auxiliares.

Seleccionamos C2:D12, accedemos al grupo Gráficos de la pestaña Insertar y elegimos Línea + Línea con marcadores.

Eliminamos el título del gráfico y la leyenda (seleccionar + Supr). Hacemos doble clic en una de las líneas de división principales y ponemos:

• Color de línea → Línea sólida / Color: Azul claro
• Estilo de línea → Tipo de guión: Punto cuadrado

Hacemos clic con el botón derecho en el eje vertical y, en el menú emergente, seleccionamos Calibri + 9 ppp + Azul oscuro. Ponemos los mismos valores en el eje horizontal.

Hacemos doble clic en el eje vertical:

• Opciones del eje → Mínima: Fija + 5 / Máxima: Fija + 9 / Marca de graduación principal: Ninguno
• Número → Categoría: Número / Posiciones decimales: 2 / Usar separadores de miles: (poner marca)
• Color de línea → Línea sólida / Color: Azul

Hacemos doble clic en el eje horizontal:

• Opciones del eje → Eje del texto: (poner marca)
• Color de línea → Línea sólida / Color: Azul

Hacemos doble clic en una línea del gráfico:

• Opciones de marcador → Integrado / Tipo: Círculo / Tamaño: 5
• Relleno de marcador → Relleno sólido / Color: Verde claro
• Color de línea de marcador → Línea sólida / Color: Verde oscuro
• Color de línea → Línea sólida / Color: Rojo
• Estilo de línea → Ancho: 1,75 pto

En la columna M, empezando en M15 (más adelante se entenderá la razón), escribimos todas la fechas comprendidas entre el 12/01/2010 (primer terremoto) y el 19/05/2011 (último terremoto).

En la columna N pondremos las magnitudes de los terremotos (empezando en N15).

En N15:

=BUSCARV(M15;$C$3:$D$12;2;FALSO)

Extendemos la fórmula hasta la fila 507, que corresponde a la fecha del terremoto de Kütahya (Turquía) ocurrido el 19/05/2011.

El siguiente paso consiste en sustituir la tabla de valores C2:D12 por M2:N507.

Con el gráfico seleccionado, accedemos a Herramientas de gráficos + Diseño + Seleccionar datos y cambiamos el valor del apartado Rango de datos del gráfico:

 

El gráfico habrá quedado así:

Al dejar en blanco las celdas del rango M3:N14, hemos conseguido que el marcador de la izquierda quede separado del eje vertical.

Ahora, hay que hacer una nueva modificación en el rango de datos del gráfico, pero antes es necesario crear dos nuevas columnas.

En P15:

=SI(ESNUMERO(N15);M15;"")

En Q15:

=N15

Seleccionamos P15:Q15 y extendemos ambas fórmulas hasta la fila 507. En la columna P sólo aparecen las fechas de los terremotos. Cuando en una fecha no hay terremoto, la celda está en blanco.

De nuevo, con el gráfico seleccionado, accedemos a Herramientas de gráficos + Diseño + Seleccionar datos y cambiamos el valor del apartado Rango de datos del gráfico:

Éste será el nuevo aspecto del gráfico:

Hacemos doble clic en el eje horizontal:

• Alineación → Dirección del texto: Girar todo el texto 270º
• Opciones del eje → Marca de graduación principal: Ninguno

Con el gráfico seleccionado, accedemos a Herramientas de gráficos + Presentación + Análisis + Barra de error + Más opciones de las barras de error.

• Barras de error verticales → Dirección: Menos / Cuantía de error: Porcentaje + 100%
• Color de línea → Línea sólida / Color: Azul claro
• Estilo de línea → Tipo de guión: Punto cuadrado

Hacemos doble clic en el Área del gráfico:

• Color del borde → Línea sólida / Color: Azul
• Estilo de borde → Esquinas redondeadas

Ponemos un rótulo en el eje vertical accediendo a Herramientas de gráficos + Presentación + Rótulos del eje + Título del eje vertical primario + Título girado; escribimos: Grados Richter y, con el botón secundario, ponemos letra Calibri + Normal + 10 ppp + Azul claro.

Descargar archivo (SE23-Terremotos.xls)

Descargar archivo (SE23-Terremotos.xlsx)

Obtener encabezados

Hemos dispuesto en una tabla las ventas de varios productos en diferentes zonas (B2:F11). Queremos saber cuáles fueron los tres productos más vendidos y en qué zonas se vendieron (B13:E17).

Para resolver el ejercicio por el método que vamos a explicar, es necesario que todos los números de la tabla sean distintos. Usaremos las celdas del rango H2:K4 para hacer cálculos auxiliares; terminado el ejercicio podremos borrarlos.

Ponemos el número más grande (que corresponde al producto más vendido) en la celda C15:

=K.ESIMO.MAYOR($C$3:$F$11;1)    [Resultado: 7.002]

Ese número está en E6; es decir, en la fila 6 y en la columna 5. Vamos a determinar estos valores y ponerlos en H4 e I4 respectivamente.

En H4:

=SUMAPRODUCTO(FILA($C$3:$F$11)*(($C$3:$F$11)=C15))    [Resultado: 6]

En I4:

=SUMAPRODUCTO(COLUMNA($C$3:$F$11)*(($C$3:$F$11)=C15))    [Resultado: 5]

Para ver cómo funcionan estas fórmulas, analizaremos la primera (la otra funciona igual).

FILA($C$3:$F$11) devuelve una matriz con los números de las filas que ocupan cada uno de los valores de la tabla. Para comprobar que esto es cierto, seleccionamos M3:P11 y escribimos: =FILA($C$3:$F$11); terminamos pulsando Ctrl + Mayús + Intro.

(($C$3:$F$11)=C15) comprueba si cada valor de la tabla es igual a 7.002 (el valor de C15), devolviendo una matriz de valores VERDADERO y FALSO. Como el 7.002 no está repetido, sólo habrá un valor VERDADERO y el resto serán valores FALSO. Lo comprobaremos seleccionando R3:U11 y escribiendo: =(($C$3:$F$11)=C15); terminaremos con Ctrl + Mayús + Intro.

(FILA($C$3:$F$11)*(($C$3:$F$11)=C15)) multiplica las dos matrices anteriores. Como VERDADERO, a efectos numéricos, es 1 y FALSO es 0, el producto será una matriz que tendrá un único valor distinto de cero. Para realizar la comprobación, seleccionamos W3:Z11 y escribimos: =(FILA($C$3:$F$11)*(($C$3:$F$11)=C15)); terminamos con Ctrl + Mayús + Intro.

Sólo nos falta sumar todos los valores para obtener la fila en la que se encuentra el número 7.002. Podemos hacerlo con la función SUMA (en cuyo caso tendremos que terminar con Ctrl + Mayús + Intro) o con SUMAPRODUCTO (en cuyo caso terminaremos con Intro).

Una vez conocida la fila y la columna en la que está el producto más vendido, podemos determinar fácilmente los encabezados con DIRECCION e INDIRECTO.

En J4:

=INDIRECTO(DIRECCION(H4;2))    [Resultado: Microondas]

En K4:

=INDIRECTO(DIRECCION(2;I4))    [Resultado: Este]

Finalmente, pondremos sendas fórmulas compuestas en D15 y E15 para poder eliminar, si lo deseamos, las celdas auxiliares.

En D15:

=INDIRECTO(DIRECCION(SUMAPRODUCTO(FILA($C$3:$F$11)*(($C$3:$F$11)=C15));2))

En E15:

=INDIRECTO(DIRECCION(2;SUMAPRODUCTO(COLUMNA($C$3:$F$11)*(($C$3:$F$11)=C15))))

Las fórmulas para el segundo y tercer producto más vendidos son evidentes:

En C16:

=K.ESIMO.MAYOR($C$3:$F$11;2)    [Resultado: 6.375]

En C17:

=K.ESIMO.MAYOR($C$3:$F$11;3)    [Resultado: 5.688]

Arrastramos las fórmulas del rango D15:E15 hasta la fila 17 y el ejercicio estará terminado.

Como es lógico, hay otras alternativas al método propuesto para resolver el ejercicio. Ésta es una de ellas:

En H4:

=MAX(($C$3:$F$11=C15)*FILA($C$3:$F$11))    [Terminar con Ctrl + Mayús + Intro]

En I4:

=MAX(($C$3:$F$11=C15)*COLUMNA($C$3:$F$11))    [Terminar con Ctrl + Mayús + Intro]

En J4:

=INDIRECTO(DIRECCION(H4;2))

En K4:

=INDIRECTO(DIRECCION(2;I4))

Creamos las fórmulas compuestas:

En D15:

=INDIRECTO(DIRECCION(MAX(($C$3:$F$11=C15)*FILA($C$3:$F$11));2))    [Terminar con Ctrl + Mayús + Intro]

En E15:

=INDIRECTO(DIRECCION(2;MAX(($C$3:$F$11=C15)*COLUMNA($C$3:$F$11))))    [Terminar con Ctrl + Mayús + Intro]

Descargar archivo (SE21-Más vendidos.xls)

Descargar archivo (SE21-Más vendidos.xlsx)

Análisis de hipótesis con "Tabla de datos"

Solicitamos un crédito hipotecario de 120.000 € a pagar mensualmente en 20 años (240 mensualidades) a un interés del 3,50% anual. ¿Cuál será la cuota mensual?

Excel dispone de la función PAGO para hallar esta cuota. La fórmula que hay que poner en C6 es la siguiente:

=-PAGO(C3/12;C4;C2)     [Resultado: 695,95]

Ahora, queremos saber a cuánto ascenderán los pagos para otros intereses. Podemos resolver este nuevo problema creando una tabla donde el interés tome distintos valores.

En G3:

=-PAGO(F3/12;$C$4;$C$2)     [Resultado: 607,06]   

Extendemos la fórmula hasta la fila 15.

Hemos resuelto el ejercicio del modo convencional. A continuación, lo haremos con una Tabla de datos.

Nos ponemos en G2. Es imprescindible situar el cursor en esa celda. Cuando los valores de la variable (en nuestro ejemplo, el interés del préstamo) se encuentran en una columna, la fórmula que hay que usar como base para crear la Tabla de datos debe estar situada, obligatoriamente, en la celda que se encuentra una fila por encima de la lista y una columna a la derecha.

Además, debemos elegir una celda vacía que Excel utilizará como celda de entrada para los cálculos (será la A1). Esta celda la usaremos en el primer argumento de la función PAGO.

En G2:

=-PAGO(A1/12;C4;C2)     [Resultado: 500,00]

Seleccionamos el rango F2:G15 y accedemos a Datos + Análisis Y si + Tabla de datos. Se mostrará el cuadro de diálogo Tabla de datos.

Puesto que la lista de datos está en una columna, dejaremos en blanco la primera entrada y pondremos A1 en la segunda. El resultado será:

Colocando el cursor en cualquier celda del rango G3:G15 se observa que, en la barra de fórmulas, Excel ha insertado la siguiente fórmula matricial: {=TABLA(;A1)}

El valor de la celda G2 no interesa pero no se puede borrar, aunque es posible ocultarlo (cambiando el color de la fuente) o enmascararlo. Vamos a enmascararlo:

Hacemos clic con el botón derecho en G2, elegimos Formato de celdas y ponemos el formato personalizado siguiente: "P. mensual";;;

En otra hoja, calcularemos, por el método tradicional, los pagos mensuales para diferentes períodos de tiempo (180 meses, 240, 300, 360 y 420).

En F4:

=-PAGO($C$3/12;F3;$C$2)    [Resultado: 857,86]

Extendemos la fórmula hasta la celda J4.

Utilizando Tabla de datos, se hace así:

Nos ponemos en E4. Cuando los valores de la variable (en nuestro ejemplo, el número de pagos) se encuentran en una fila, la fórmula que hay que usar como base para crear la Tabla de datos debe estar situada, obligatoriamente, en la celda que se encuentra una fila por debajo de la lista y una columna a la izquierda (celda E4).

Volveremos a usar A1 como celda de entrada para los cálculos. Esta celda la utilizaremos en el segundo argumento de la función PAGO.

En E4:

=-PAGO(C3/12;A1;C2)    [Resultado: #¡NUM!]

Seleccionamos el rango E3:J4 y accedemos a Datos + Análisis Y si + Tabla de datos. Puesto que la lista de datos está en una fila, dejaremos en blanco la segunda entrada y pondremos A1 en la primera.

En la barra de fórmulas, Excel ha puesto: {=TABLA(A1;)}

El último caso que vamos a estudiar es el cálculo del pago mensual para diferentes tasas de interés y distintos períodos de tiempo. Lo haremos, como en los casos anteriores, primero, con fórmulas y, luego, con una Tabla de datos.

En G4:

=-PAGO($F4/12;G$3;$C$2)    [Resultado: 772,21]

Extendemos la fórmula al rango G4:L16

Veamos la solución con una Tabla de datos.

Cuando hay dos variables, la fórmula auxiliar de la Tabla de datos debe estar situada en el vértice superior izquierdo de las dos listas. En nuestro caso, la fórmula estará en F3. Además, necesitaremos dos celdas en blanco como celdas de entrada para los cálculos: una para las variables de la columna (será la A1) y otra para las variables de la fila (será la A2).

En F3:

=-PAGO(A1/12;A2;C2)    [Resultado: #¡NUM!]

Seleccionamos F3:L16 y accedemos a Datos + Análisis Y si + Tabla de datos. En el cuadro de diálogo Tabla de datos no podrá haber ninguna entrada vacía:

En la barra de fórmulas, Excel ha puesto una fórmula matricial con dos entradas: {=TABLA(A2;A1)}

Descargar archivo (E54-Tabla de datos.xls)

Descargar archivo (E54-Tabla de datos.xlsx)

Hallar el mínimo de una función con Solver

En Excel hay cuatro herramientas de análisis de hipótesis: Tabla de datos, Escenarios, Buscar objetivo y Solver. En el ejercicio Líneas de tendencia y función PRONOSTICO ya usamos Buscar objetivo; en éste utilizaremos la herramienta Solver.

Enunciado del problema:

Vamos a representar gráficamente la función. Para ello, calcularemos un conjunto de puntos a intervalos de 0,25 empezando en -2,25 y terminando en 3.

En B3 escribimos: -2,25

En C3 ponemos la fórmula: =B3^4-(5/2)*B3^3+B3-4     [Resultado: 47,86]

Seleccionamos B3:C3 y arrastramos el controlador de relleno hasta la fila 24.

Ya tenemos los valores con los que haremos el gráfico.

Seleccionamos B2:C24 y, en el grupo Gráficos de la pestaña Insertar, elegimos Dispersión + Dispersión con líneas suavizadas. Quitamos el título, la leyenda y las líneas de división principales. Ponemos una escala en el eje horizontal de -3 a 4 (doble clic en el eje horizontal). Del mismo modo, ponemos una escala en el eje vertical de -10 a 25. Cambiamos el tamaño de la fuente, los colores, las dimensiones del gráfico... y dejamos el gráfico como se muestra a continuación:

En L2 escribimos el límite inferior del intervalo en que vamos a halla el mínimo: -1

En L3 ponemos el límite superior: 3

En L6 y L7 calcularemos las coordenadas de mínimo absoluto de la función. Inicialmente debemos poner un punto cualquiera del gráfico. Por ejemplo, podemos poner el punto de abscisa x=1

En L6 ponemos: 1

En L7 ponemos: =L6^4-(5/2)*L6^3+L6-4     [Resultado: -4,50]

Por defecto, Solver no está disponible. Para poder usarlo, accedemos a Archivo + Opciones y, en Complementos, hacemos clic en el botón Ir...; marcamos el complemento Solver y pulsamos Aceptar.

Solver queda asociado a la pestaña Datos.

Accedemos a Datos + Solver.

• En Establecer objetivo, ponemos $L$7. En esta celda queremos obtener la ordenada del mínimo absoluto de la función.
• Lógicamente, en Para, elegimos Mín.
• En Cambiando las celdas de variables, ponemos $L$6. Solver tendrá que ir cambiando la abscisa hasta obtener el valor mínimo de la función.
• En Sujeto a las restricciones pondremos dos condiciones: la primera será que la abscisa sea mayor o igual al valor de $L$2; la segunda, que la abscisa sea menor o igual al valor de $L$3. Para hacerlo hay que pulsar el botón Agregar y rellenar el cuadro de diálogo correspondiente como se muestra en las figuras siguientes:

Terminada la introducción de datos pulsamos en botón Resolver. Se mostrará la ventana Resultados de Solver:

Pulsamos Aceptar y el ejercicio estará resuelto. Si se quiere, se puede añadir al gráfico una nueva serie, con los valores L6 y L7, con sus correspondientes barras de error X e Y, para señalizar el mínimo de la función. El resultado final quedará así:

Hay un mínimo relativo entre -1 y 0. Para determinarlo con precisión, será necesario poner -1 en L2 y 0 en L3; luego, bastará ejecutar de nuevo Solver siguiendo los mismos pasos.

Descargar archivo (S42-Mínimo.xls)

Descargar archivo (S42-Mínimo.xlsx)

La función CELDA con el argumento "ANCHO"

Ya hemos visto en entradas pasadas que la función CELDA con el argumento "NOMBREARCHIVO" puede dar problemas si no se utiliza el segundo argumento. Recordemos la sintaxis:

CELDA("nombrearchivo";[referencia])

También puede dar problemas si se utiliza "ANCHO" como primer argumento. Estudiemos en primer lugar el problema; después daremos la solución.

En A1 : =CELDA("ancho")    [Resultado (en mi ordenador): 11]

Ahora, hacemos clic con el botón derecho en el encabezado de la columna E, en el menú emergente elegimos Ancho de columna, ponemos un ancho de 15 y pulsamos Aceptar. Por el momento, no ha pasado nada raro.

En E5 escribimos: Presupuesto

Como CELDA("ancho") es una función volátil, en cuanto pulsemos Intro o salgamos de E5 con una tecla de fecha o con el tabulador, A1 mostrará el ancho de la última celda modificada (por omisión, la última celda modificada se toma como referencia); es decir, en A1 se mostrará el número 15. Lógicamente, queremos que en A1 se muestre la anchura de la celda A1, no la de E5.

La solución es bien sencilla; basta utilizar el segundo argumento de la función, indicando la referencia a la propia celda en la que hemos puesto la fórmula o a otra de la misma columna.

En A1 escribimos: =CELDA("ancho";A1)    [Resultado: 11]

Ya podemos cambiar el ancho de cualquier otra columna sin peligro de que se altere el valor que nos devuelve la fórmula.

Hacemos clic con el botón derecho en el encabezado de la columna A y, en el menú emergente, seleccionamos Ancho de columna; ponemos un ancho de 23 y pulsa Aceptar.

A pesar de que la fórmula es la correcta, en A1 sigue mostrándose 11. Esto es debido a que, en realidad, no se ha modificado ningún valor de la hoja y Excel no considera que el cambio de anchura de una columna suponga una modificación que implique recalcular las fórmulas. Tendremos que forzar el recálculo. Podemos hacerlo pulsando F9 o cambiando el valor de cualquier celda de la hoja.

Contar las veces que se repite un carácter

Para contar el número de veces que aparece un carácter —por ejemplo, la letra "h"—  en una frase, utilizaremos un procedimiento que consta de cuatro pasos.

1. Contar el número total de caracteres de la frase.
2. Eliminar de la frase todas las apariciones de la letra buscada (en nuestro ejemplo, será la "h").
3. Volver a contar los caracteres de la nueva frase.
4. Restar ambos valores.

Comencemos poniendo una frase en A1: Hemos rehusado eliminar las malas hierbas de nuestra huerta

En A2 contamos cuántos caracteres tiene:
=LARGO(A1)      [Resultado: 59]

En A3 eliminaremos las "haches" sustituyéndolas por nada. Para ello, utilizaremos la función SUSTITUIR, que distingue mayúsculas y minúsculas; por tanto, será necesario pasar la frase entera a minúsculas:
=SUSTITUIR(MINUSC(A1);"h";"")      [Resultado: emos reusado eliminar las malas ierbas de nuestra uerta]

En A4 contamos cuántos caracteres tiene la nueva frase:
=LARGO(A3)    [Resultado: 44]

En A5 restamos los caracteres de ambas frases y, de ese modo, obtenemos el número total de "haches":
=A2-A4    [Resultado: 4]

Como hemos hecho en otras ocasiones, podemos prescindir de los pasos intermedios creando una fórmula compuesta, por ejemplo, en A6:
=LARGO(A1)-LARGO(SUSTITUIR(MINUSC(A1);"h";""))    [Resultado: 4]

Problemas en las fórmulas al cambiar el nombre de la hoja

En ocasiones, una fórmula deja de funcionar si se cambia el nombre de la hoja. Esto puede ocurrir cuando el nombre de la hoja está incluido en la fórmula como un literal. Pongamos un ejemplo.

Escribimos algo en la celda  D1 de la Hoja1: mesa
En D3 ponemos el número de la fila donde hemos escrito la palabra anterior: 1
En D4 ponemos el número de la columna: 4
En D5 creamos la dirección: =DIRECCION(D3;D4)       [Resultado: $D$1]
En D7 extraemos el valor de la celda señalada en D5: =INDIRECTO("Hoja1!"&D5)       [Resultado: mesa]

Nota: A nadie se le escapa que la fórmula más lógica, si necesitáramos utilizar INDIRECTO, hubiera sido: =INDIRECTO(Hoja1!D5). He introducido el literal "Hoja1!", seguido del signo de concatenación (&) y el nombre de la celda, únicamente para ilustrar el problema que puede surgir al cambiar el nombre de una hoja.

¿Qué ocurre si sustituimos el nombre de la "Hoja1" por otro? Que se produce un error en D7. La razón es que la expresión "Hoja1!" es un literal que no se modifica al cambiar el nombre de la hoja; por tanto, INDIRECTO sigue apuntando a la celda D5 de una hoja que ya no existe.

¿Cómo resolver el problema? Lo haremos con el siguiente ejemplo:

Tenemos un texto en la celda C2 que debemos poner en C14 utilizando INDIRECTO y el nombre de la hoja (Monumentos) tratado como un literal.

En C4 ponemos la fila de la celda C2: 2

En C5 ponemos la columna de la celda C2: 3

En C6 obtenemos la dirección de C2: =DIRECCION(C4;C5)

En C8 obtenemos la ruta del fichero y el nombre de la hoja: =CELDA("nombrearchivo";Monumentos!A1)

En C9 calculamos los caracteres totales del texto obtenido en C8: =LARGO(C8)

En C10 calculamos en qué posición se encuentra el carácter "]": =HALLAR("]";C8;1)

En C11 extraemos el nombre de la hoja: =DERECHA(C8;C9-C10)

En C12 creamos una macrofórmula para evitar el uso de las celdas C8 a C11: =DERECHA(CELDA("nombrearchivo";Monumentos!A1);LARGO(CELDA("nombrearchivo";Monumentos!A1))-HALLAR("]";CELDA("nombrearchivo";Monumentos!A1);1))

En C14 ponemos el mismo valor que en C2: =INDIRECTO("'"&C12&"'!"&C6)

Para comprobar si funciona correctamente, sustituimos el nombre de la hoja por "Monumentos históricos":

El resultado de C14 no ha cambiado.

El empleo de la función CELDA, usando los dos argumentos que tiene, puede ser de utilidad si queremos mostrar una lista con el nombre de todas las hojas del libro. Lo correcto sería que la lista se actualizara al cambiar el nombre de cualquier hoja. Lo haremos en la hoja "Museos".

Usaremos las celdas D3 a D6 para poner los nombres de las hojas. Debemos conseguir que se actualicen al cambiar el nombre de la hoja.

En D3:

=DERECHA(CELDA("nombrearchivo";'Monumentos históricos'!A1);LARGO(CELDA("nombrearchivo";'Monumentos históricos'!A1))-HALLAR("]";CELDA("nombrearchivo";'Monumentos históricos'!A1);1))

En D4:

=DERECHA(CELDA("nombrearchivo";Museos!A1); LARGO(CELDA("nombrearchivo";Museos!A1))-HALLAR("]";CELDA("nombrearchivo";Museos!A1);1))

En D5:

=DERECHA(CELDA("nombrearchivo";'Instalaciones deportivas'!A1);LARGO(CELDA("nombrearchivo";'Instalaciones deportivas'!A1))-HALLAR("]";CELDA("nombrearchivo";'Instalaciones deportivas'!A1);1))

En D6:

=DERECHA(CELDA("nombrearchivo";Auditorios!A1); LARGO(CELDA("nombrearchivo";Auditorios!A1))-HALLAR("]";CELDA("nombrearchivo";Auditorios!A1);1))

Hacemos clic en B3, accedemos a Datos + Validación de datos y ponemos los valores siguientes:

La lista está creada. Podemos cambiar el nombre de cualquier hoja y comprobar que la lista queda actualizada.

Descargar archivo (SE20-Monumentos.xls)

Descargar archivo (SE20-Monumentos.xlsx)