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)

Obtener el nombre de una hoja

La función CELDA devuelve información acerca del formato, la ubicación o el contenido de una celda. Su sintaxis es:

CELDA("tipo_de_info";[referencia])

• "tipo_de_info" es una palabra elegida entre una lista de posibles argumentos: "DIRECCIÓN", "COLUMNA", "COLOR", "CONTENIDO"...
• referencia (opcional) es el nombre de una celda.

CELDA con el argumento "NOMBREARCHIVO" se usa para obtener la ruta completa donde se guarda el libro así como el nombre de la hoja.

Nota: En la ayuda de Excel (al menos en las versiones 2003, 2007 y 2010) hay un error; en vez de "NOMBREARCHIVO" aparece "ARCHIVO".

Si usamos CELDA con el argumento "NOMBREARCHIVO" pero no usamos el segundo argumento, se puede producir un resultado inesperado. Veámoslo.

Abrimos Excel, sustituimos el nombre de la "Hoja1" por el de "Valores", ponemos los rótulos de la imagen siguiente y finalizamos guardando el libro en el escritorio con el nombre SE19-Celda_nombrearchivo.

Ya tenemos la hoja preparada. Vamos a intentar poner en C8 el nombre de la hoja.

En C2:

=CELDA("nombrearchivo")

En mi ordenador el resultado ha sido: C:\Users\Javi\Desktop\[SE19-Celda_nombrearchivo.xlsx]Valores

El nombre de la hoja ha quedado al final de la expresión y a continuación del corchete "]". Necesitamos conocer la longitud de la frase y en qué posición se encuentra el corchete para extraer los caracteres que hay a su derecha.

En C3:

=LARGO(C2)      [Resultado: 60]

En C4:

=ENCONTRAR("]";C2;1)      [Resultado: 53]

En C5:

=EXTRAE(C2;C4+1;C3-C4)      [Resultado: Valores]

En vez de EXTRAE, podemos usar la función DERECHA. Lo haremos en C6.

En C6:

=DERECHA(C2;C3-C4)      [Resultado: Valores]

Para prescindir de todas las celdas anteriores, creamos una fórmula compuesta en C8.

En C8:

=EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo");1)+1;LARGO(CELDA("nombrearchivo"))-ENCONTRAR("]";CELDA("nombrearchivo")))   [Resultado: Valores]

Hasta aquí todo parece correcto, pero hay un problema. Si cambiamos el nombre de otra hoja, la celda C8 ya no mostrará la etiqueta "Valores" sino la de la última hoja cambiada. Para comprobarlo, sustituimos "Hoja2" por "Compras" y volvemos a la hoja "Valores". Éste será el resultado:

Si ahora cambiamos "Hoja3" por "Balance anual" obtendremos:

El problema surge al prescindir del segundo argumento de la función CELDA. Si ponemos como segundo argumento cualquier celda de la hoja "Valores" el valor de C8 no cambiará, salvo que modifiquemos la etiqueta de la hoja "Valores". Así pues, debemos modificar las fórmulas de C2 y C8.

En C2:

=CELDA("nombrearchivo";Valores!E9)        [Vale cualquier celda]

En C8:

=EXTRAE(CELDA("nombrearchivo";Valores!E9);ENCONTRAR("]"; CELDA("nombrearchivo";Valores!E9);1)+1;LARGO(CELDA("nombrearchivo";Valores!E9))-ENCONTRAR("]";CELDA("nombrearchivo";Valores!E9);1))

Ya podemos cambiar tranquilamente el nombre de cualquier hoja sin temor de que se modifique el valor devuelto por la fórmulas de la celda C8 (excepto en el caso de que modifiquemos la etiqueta de la hoja "Valores").

Descargar archivo (SE19-Celda_nombrearchivo.xls)

Descargar archivo (SE19-Celda_nombrearchivo.xlsx)

Usar el controlador de relleno con el botón auxiliar del ratón

Todo usuario de Excel sabe que para crear series numéricas hay que seleccionar las celdas que marcan la pauta de la serie y arrastrar el controlador de relleno. Lo que muchos ignoran es que al arrastrar con el botón secundario (el derecho para los diestros) se abre un interesante menú contextual que permite crear diferentes tipos de series.

Ponemos 2 y 6 en B3 y B4 respectivamente. Seleccionamos B3:B4 y, manteniendo pulsado el botón auxiliar, arrastramos el controlador de relleno hasta la fila 14. En el menú contextual, seleccionamos Tendencia lineal. Obtendremos una progresión aritmética de diferencia 4.

El resultado habría sido el mismo si hubiéramos arrastrado el controlador de relleno con el botón principal. Así pues, en este caso, no parece muy útil el menú contextual.

Repetimos el ejercicio pero, ahora, elegimos Tendencia geométrica. El resultado será una progresión geométrica de razón 3.

Con las fechas hay otras opciones. Ponemos 26/12/2000 en la celda B3, arrastramos el controlador de relleno con el botón secundario y, en el menú contextual, elegimos Rellenar días; Excel mostrará la serie de días consecutivos que siguen al 26/12/2000.

Si ponemos dos fechas, la pauta será la diferencia de días entre ambas. Por ejemplo, poniendo 08/04/1955 en B3 y 11/04/1955 en B4, obtendremos la serie siguiente:

En los dos últimos ejemplos el resultado es el mismo usando el botón principal del ratón.

Para estudiar el siguiente caso, ponemos formato ddd, dd/mmm/aaaa en B3:B14; escribimos 26/12/2000 en B3, arrastramos el controlador de relleno con el botón secundario y seleccionamos Rellenar días de la semana. Aparecerá la lista de días consecutivos, comenzando el 26/12/2000, pero se habrán excluido los sábados y los domingos.

Se comprende que si elegimos Rellenar meses, no se cambiarán ni los días ni los años (al menos hasta que sobrepasemos el mes de diciembre). Análogamente, con la opción Rellenar años, se mantendrán invariables los días y los meses.

La opción Series incluye, en un cuadro de diálogo, todas las posibilidades estudiadas; además, se puede indicar la pauta que queramos imponer. Por ejemplo, ponemos 26/12/2000 en B3, arrastramos el controlador de relleno y elegimos Series.

Si queremos crear una secuencia de fechas separadas entre sí por 100 días, que termine, como más tarde, el 25/08/2002, rellenaremos el cuadro de diálogo con estos datos: