martes, 31 de enero de 2012

Tablas para encadenar listas de longitud variable

En la entrada anterior hemos visto cómo encadenar listas cuya longitud no cambia nunca. Los "nombres" creados son de tamaño fijo.

En el caso de listas que cambian a lo largo del tiempo, los "nombres" tendrán que ser dinámicos; es decir, su tamaño deberá cambiar y adaptarse a la nueva longitud de la lista. Esto suele hacerse, normalmente, usando la función DESREF. Pero, en ocasiones, es mejor utilizar tablas, como se comprobará en este ejercicio.

Disponemos del listado de corredores de seis equipos ciclistas. Estas listas cambian de nombres cada temporada así como de número de corredores. Éste es su aspecto actual:

En las celdas C4 y C5 de la hoja "Ciclista" pondremos dos listas. En la primera, elegiremos un equipo y, en la segunda, escogeremos un corredor de ese equipo. Naturalmente, la segunda lista (la de la celda C5) será la correspondiente al equipo elegido en C4.

Es muy fácil crear la primera lista. Hacemos clic en la celda C4 de la hoja "Ciclista", accedemos a Datos + Validación de datos y ponemos estos valores:

A continuación, crearemos 6 "nombres" para las 6 listas de los equipos ciclistas. Serán nombres dinámicos ya que prevemos que las listas cambien en cualquier momento.

Accediendo a Fórmulas + Asignar nombre, creamos los siguientes:

Los "nombres" no admiten espacios; por esa razón, debemos eliminarlos o sustituirlos por guiones bajos.

Ahora, en C5, pondremos la lista de los ciclistas del equipo seleccionado. Hacemos clic en C5, accedemos a Datos + Validación de datos y dejamos estos datos:

Excel nos devuelve el siguiente mensaje de error:

El argumento de la función INDIRECTO debe ser una referencia a una celda o rango que no sea una fórmula. De ahí el mensaje de error.

Si en vez de utilizar DESREF creamos "tablas" ("listas" en la antigua terminología de Excel), la cosa funcionará perfectamente, ya que INDIRECTO sí admite este tipo de argumento.

Comencemos borrando todos los "nombres" que hemos creado. Para hacerlo, accedemos a Fórmulas + Administrador de nombres y los borramos, uno a uno, con el botón Eliminar.

En la hoja "Listados", seleccionamos B2:B18 y vamos a Insertar + Tabla. Excel muestra el consiguiente cuadro de diálogo en el que debemos marcar la opción "La tabla tiene encabezados".

En la ficha Herramientas de tabla + Diseño, debemos cambiar el nombre (Tabla1), que, por defecto, Excel ha asignado a la tabla, por el de Andalucía.

Repetimos estos pasos con el resto de las columnas de la hoja "Listados" pero teniendo la precaución de no dejar espacios en blanco en los nombres de las tablas. Si accedemos a Fórmulas + Administración de nombres, observaremos que el icono asignado a las "tablas" es distinto del que Excel asigna a los "nombres" normales.

En la celda C5 de la hoja "Ciclista", ponemos la lista de ciclistas accediendo a Datos + Validación de datos:

Hemos eliminado los espacios en blanco de C4 para que coincidan con los nombres que hemos dado a las "tablas". La nueva lista funciona correctamente; ya no hay errores.

Podemos añadir nuevos ciclistas al final de cualquier lista y, automáticamente, aparecerán en el listado de la celda C5. Por el contrario, si queremos eliminar un ciclista no debemos borrarlo con la tecla Supr. El modo correcto para eliminar elementos de una tabla consiste en seleccionarlos y, en el menú contextual, elegir Eliminar + Filas de la tabla.

Nota: El método descrito no se puede utilizar en Excel 2003. El ejemplo para esta versión se ha resuelto por el método empleado en el ejercicio Selecciones encadenadas.

 
 
 

domingo, 29 de enero de 2012

Selecciones encadenadas

En una celda (C4) ponemos la lista de las Comunidades Autónomas de España. Eligiendo una, se debe crear automáticamente la lista de las provincias de esa comunidad (en C5). Luego, escogiendo una provincia, se mostrarán todos sus pueblos (en C6).

El libro tiene 5 hojas. La cuatro imágenes anteriores están tomadas de la hoja "Selección". La hoja "Comunidades" contiene la lista de todas las Comunidades Autónomas.

La hoja "Provincias" contiene las provincias que hay en cada Comunidad Autónoma.

La hoja "Poblaciones" es la más extensa y contiene todos los pueblos de España agrupados por provincias.

Finamente, la hoja "Auxiliar" la usaremos como hoja de apoyo para obtener valores auxiliares.

Necesitamos crear una serie de "nombres". En la hoja "Comunidades", seleccionamos A2:A20 y, en el Cuadro de nombres, escribimos: COMUNIDADES_AUTÓNOMAS

Al rango A2:A20 le hemos asignado el nombre del encabezado de la columna. Tenemos que hacer lo mismo con las columnas de la hoja "Provincias" y, como son muchos los nombres que hay que escribir, se pueden producir errores fácilmente. Para evitarlos, los crearemos de la siguiente manera:

En la hoja "Provincias" seleccionamos A1:A9, accedemos a Fórmulas + Crear desde selección y marcamos Fila superior.

De este modo, no tenemos que escribir la palabra ANDALUCÍA y, lo que es más importante, evitamos errores de escritura. Repitiendo estos pasos con el resto de las columnas de la hoja "Provincias" tendremos la siguiente lista de "nombres":

Hacemos clic en la celda C4 de la hoja "Selección", accedemos a Datos + Validación de datos y ponemos:

Abrimos la lista de la celda C4 y elegimos una comunidad: CASTILLA_Y_LEÓN.

Ahora, en C5 tenemos que poner la lista de provincias de la comunidad escogida. Para ello, hacemos clic en C5, accedemos a Datos + Validación de datos y ponemos:

Abrimos la lista y elegimos una provincia: PALENCIA.

El siguiente paso consiste en poner la lista de los pueblos de PALENCIA en C6. Podríamos utilizar el método anterior, pero esto nos obligaría a crear otros 52 "nombres" con los datos de la hoja "Poblaciones". Vamos a hacerlo de otra forma.

Calculamos el número de pueblos, villas y ciudades que hay en la provincia de ÁLAVA; dicho de otra forma, contamos las filas de la columna A de la hoja "Poblaciones".

En la celda A2 de la hoja "Auxiliar":
=CONTARA(Poblaciones!A:A)-1     [Resultado: 348]

Extendemos la fórmula hasta la columna AZ.

En B4 calculamos el número de la columna en la que se encuentra PALENCIA (la provincia elegida) en la hoja "Poblaciones".

En la celda B4 de la hoja "Auxiliar":
=COINCIDIR(Selección!C5;Poblaciones!A1:AZ1;0)    [Resultado: 38]

Podríamos haber utilizado la función CONTAR.SI de esta manera:

En la celda C4 de la hoja "Auxiliar":
=CONTAR.SI(Poblaciones!$1:$1; "<="&Selección!$C$5)    [Resultado: 38]

Necesitamos saber cuántos pueblos hay en PALENCIA. Puesto que ya hemos obtenido la lista del total de pueblos de cada provincia, será fácil extraer el dato de PALENCIA usando BUSCARH.

En la celda B5 de la hoja "Auxiliar":
=BUSCARH(Selección!$C$5;Auxiliar!$A$1:$AZ$2;2)    [Resultado: 428]

Ahora, crearemos nuestro último "nombre", al que llamaremos Población, que contendrá la lista de los pueblos de PALENCIA que estamos buscando.

Accedemos a Fórmulas + Asignar nombre y creamos el "nombre" siguiente:

Terminamos el ejercicio haciendo clic en la celda C6 de la hoja "Selección", accediendo a Datos + Validación de datos y poniendo los valores:


 
 
 

miércoles, 18 de enero de 2012

Búsqueda en una tabla

El resultado de la búsqueda de un dato en una tabla puede ser un número (las veces que el dato está repetido) o, simplemente, VERDADERO o FALSO (está o no está; independientemente del número de veces que pueda estar repetido). Consideremos una tabla de actividades deportivas que se desarrollan en distintas fechas:

Hay deportistas que participan en varias modalidades en diferentes fechas. Algunos nombres están escritos en minúscula. Poniendo un nombre en D10, vamos a realizar cuatro tipos de búsquedas.
  1. Comprobar el número de veces que aparece el nombre en la tabla. No se tendrán en cuenta las mayúsculas ni las minúsculas.
  2. Comprobar el número de veces que aparece el nombre en la tabla, teniendo en cuenta las mayúsculas y las minúsculas.
  3. Comprobar si el nombre existe. No se tendrán en cuenta las mayúsculas ni las minúsculas.
  4. Comprobar si el nombre existe, teniendo en cuenta las mayúsculas y las minúsculas.
En D10 ponemos un nombre cualquiera: carlos

En D11:
=SUMAPRODUCTO(--((C3:F8)=D10))    [Resultado: 1]

En E11 pondremos otra solución, utilizando SUMA en vez de SUMAPRODUCTO.

En E11:
=SUMA(--((C3:F8)=D10))    [Terminar con Ctrl + Mayús. + Intro. Resultado: 1]

En D12:
=SUMAPRODUCTO(--(IGUAL(C3:F8;D10)))    [Resultado: 0]

Ahora, con SUMA:

En E12:
=SUMA(--(IGUAL(C3:F8;D10)))    [Terminar con Ctrl + Mayús. + Intro. Resultado: 0] 

-------------------------------------------------------------------------------------------------------------------------
La función IGUAL distingue entre mayúsculas y minúsculas. Así:

="A"="a"     [Devuelve: VERDADERO]

=IGUAL("A";"a")      [Devuelve: FALSO]
-------------------------------------------------------------------------------------------------------------------------

En D13:
=O(C3:F8=D10)    [Terminar con Ctrl + Mayús. + Intro. Resultado: VERDADERO]

En D14:
=O(IGUAL(C3:F8;D10))     [Terminar con Ctrl + Mayús. + Intro. Resultado: FALSO]



 
 
 

lunes, 16 de enero de 2012

Líneas de tendencia y función PRONOSTICO

Los resultados comparativos de la evolución del precio de un producto en el extranjero y en nuestro país a lo largo una serie de meses se han colocado en una hoja de cálculo (B2:D11).

Se constata que el precio se va encareciendo a un ritmo superior en el extranjero, por lo que, si la evolución continúa al mismo ritmo, llegará un momento en que ambos precios se igualarán y, a partir de entonces, será más económica la compra en el mercado nacional.

Como es lógico, interesa calcular cuándo se produce el encuentro. Para ello, trazaremos el gráfico de la evolución de precios, obtendremos las líneas de regresión (líneas de tendencia en Excel) y calcularemos dónde se cruzan.

Seleccionamos B2:D11 y accedemos a Insertar + Dispersión + Dispersión con líneas rectas y marcadores.

Hacemos doble clic en la leyenda y, en Opciones de leyenda, ponemos una marca en Inferior.

Para hallar la línea de tendencia, hacemos clic con el botón derecho en la línea de la serie Nacional (marrón), elegimos Agregar línea de tendencia y ponemos:
  • Opciones de línea de tendencia → Tipo de tendencia o regresión: Lineal / Nombre de la línea de tendencia → Personalizado: Lin_Nac / Extrapolar → Adelante: 20 / Presentar ecuación en el gráfico: (activado).
Repetimos la operación con la línea de la serie Extranjero (azul), poniendo los mismos valores excepto el nombre:
  • Opciones de línea de tendencia → Tipo de tendencia o regresión: Lineal / Nombre de la línea de tendencia → Personalizado: Lin_Ext / Extrapolar → Adelante: 20 / Presentar ecuación en el gráfico: (activado).
Podemos cambiar el tamaño del gráfico, los colores de las líneas, marcadores, fondos..., y dejarlo a nuestro gusto. También conviene arrastrar las ecuaciones a lugares que permitan asociarlas fácilmente con sus correspondientes líneas de regresión.


Se aprecia que entre los meses 19 y 20 se cruzan las dos líneas. A partir de ese momento será más rentable comprar en el mercado nacional. Aunque esto es una previsión, y no tiene mucho sentido conocer el momento exacto del encuentro, podemos calcular matemáticamente cuándo se producirá usando la herramienta de análisis de hipótesis Buscar objetivo. Lo haremos en F2:I3.

Ponemos 1 en F3 (vale cualquier cifra). En el mes 1, los precios en el Extranjero y en el mercado Nacional son 1,1 y 35,5 respetivamente pero las ecuaciones de las líneas de regresión nos darán valores ligeramente diferentes (no pasan exactamente por los puntos del gráfico).

En G3:
=3,7033*F3-4,0944    [Resultado: -0,3911]

En H3:
=1,7433*F3+34,017    [Resultado: 35,7603]

En I3:
=G3-H3    [Resultado: -36,1514]

Con Buscar objetivo calcularemos el valor de hay que poner en G3 para que la diferencia de precios obtenida en I3 sea 0.

Accedemos a Datos + Análisis Y si + Buscar objetivo. En Definir la celda, ponemos I3; en Con valor, escribimos 0; en Para cambiar la celda, seleccionamos F3. Pulsando Aceptar Excel nos indica que ha encontrado la solución.


Cuando transcurran 19,4445918 meses se prevé que se produzca la coincidencia de precios.


El ejercicio puede resolverse sin crear el gráfico. La función PRONOSTICO devuelve los mismos valores que los obtenidos usando las ecuaciones de las líneas de regresión.

----------------------------------------------------------------------------------------------------------------------------------------------------------------
PRONOSTICO(x;conocido_y;conocido_x)

Calcula o pronostica un valor futuro a través de los valores existentes. La predicción del valor es un valor y teniendo en cuenta un valor x. Los valores conocidos son valores x y valores y existentes, y el nuevo valor se pronostica utilizando regresión lineal. Esta función se puede utilizar para realizar previsiones de ventas, establecer requisitos de inventario o tendencias de los consumidores.
  • x: Obligatorio. El punto de datos cuyo valor se desea predecir.
  • conocido_y: Obligatorio. La matriz o rango de datos dependientes.
  • conocido_x: Obligatorio. La matriz o rango de datos independientes.
----------------------------------------------------------------------------------------------------------------------------------------------------------------

Vamos a repetir el ejercicio en otra hoja usando la función PRONOSTICO.

En F3 escribimos 1.

En G3:
=PRONOSTICO(F3;C3:C11;B3:B11)    [Resultado: -0,39111111]

En H3:
=PRONOSTICO(F3;D3:D11;B3:B11)    [Resultado: 35,76]

En I3:
=G3-H3     [Resultado: -36,1511111]

Los valores no coinciden exactamente con los obtenidos por el método de las líneas de tendencia debido al número limitado de decimales empleados. PRONOSTICO utiliza todos los decimales posibles, de modo que su resultado es más preciso.

Ahora, accedemos a Datos + Análisis Y si + Buscar objetivo y volvemos a poner los mismos datos:

El resultado es:


 

jueves, 12 de enero de 2012

La función SIFECHA

La función SIFECHA se emplea para calcular el tiempo transcurrido entre dos fechas. Excel 2000 fue la última versión que incorporó información sobre esta función. Sin embargo, la función está presente en todas las versiones posteriores, aunque Microsoft haya decidido no documentarla.

Ponemos en una celda cualquiera:
=SIFECHA("2003/2/12";"2003/2/24";"d")    [Resultado: 12]

Ahora, con la celda seleccionada, hacemos clic en la fx de la barra de fórmulas. Excel mostrará el cuadro de diálogo siguiente:

Haciendo clic en Ayuda sobre esta función debería mostrase información referente a SIFECHA, pero, en su lugar, aparece una ventana con información de carácter general. No hay ayuda sobre la función.

---------------------------------------------------------------------------------------------------------------------------------------------------------------
SIFECHA(fecha_inicial;fecha_final;unidad)

Calcula el número de días, meses o años entre dos fechas. Esta función se proporciona por compatibilidad con Lotus 1-2-3
  • fecha_inicial: fecha que representa la primera fecha o fecha inicial del período. Las fechas se pueden escribir como cadenas de texto entre comillas (por ejemplo, "2001/1/30"), como números de serie (por ejemplo, 36921, que representa el 30 de enero de 2001 si se utiliza el sistema de fechas de 1900) o bien, como resultado de otras fórmulas o funciones (por ejemplo, FECHANUMERO("2001/1/30")).
  • fecha_final: fecha que representa la última fecha o fecha final del período.
  • unidad: el tipo de información que desea obtener:
Comentarios:
  • Las fechas están almacenadas como números de serie secuenciales para poder utilizarse en los cálculos. De forma predeterminada, el 31 de diciembre de 1899 es el número de serie 1 y el 1 de enero de 2008 es el número de serie 39448, porque está 39.448 días después del 1 de enero de 1900.
  • La función SIFECHA es útil en fórmulas en las que se necesita calcular un período transcurrido
---------------------------------------------------------------------------------------------------------------------------------------------------------------

En E3:
=SIFECHA(B3;C3;"y")

En F3:
=SIFECHA(B3;C3;"m")

En G3:
=SIFECHA(B3;C3;"d")

En H3:
=SIFECHA(B3;C3;"md")

En I3:
=SIFECHA(B3;C3;"ym")

En J3:
=SIFECHA(B3;C3;"yd")

Extendemos todas las fórmulas hasta la fila 8.

Descargar archivo (SE10-SIFECHA.xls)

Descargar archivo (SE10-SIFECHA.xlsx)


miércoles, 11 de enero de 2012

La función FRECUENCIA

Se ha medido la velocidad de lectura, en palabras por minuto (ppm), de 470 personas y los resultados se han colocado en una hoja de cálculo (B2:C472). Se desea obtener la frecuencia de la velocidad de lectura por tramos de 100 en 100 (columnas G y H).

Daremos una solución empleando la función FRECUENCIA y otra con CONTAR.SI.

-------------------------------------------------------------------------------------------------------------------------------------------------------------
FRECUENCIA(datos; grupos)

Esta función calcula la frecuencia con que se repiten los valores de un rango y devuelve un matriz vertical de números. La ayuda de Excel nos indica que se utilice FRECUENCIA para contar el número de los resultados que se encuentran dentro de un rango.
  • datos es la matriz donde tenemos que contar las frecuencias (en nuestro caso C3:C472).
  • grupos son los intervalos empleados en la búsqueda (F3:F13).
Como FRECUENCIA devuelve una matriz, debe terminarse con Ctrl + Mayús + Intro.
-------------------------------------------------------------------------------------------------------------------------------------------------------------

Seleccionamos G3:G13 y escribimos:
=FRECUENCIA(C3:C472;F3:F13)    [Terminar con Ctrl + Mayús + Intro]

No se emplea la columna E sino la F. La razón es que cada tramo empieza en una cifra mayor que el número de la fila anterior y acaba en el número de esa fila.

También podemos utilizar esta fórmula:

En H3:
=CONTAR.SI($C$3:$C$472;"<="&F3)-SUMA($H$2:H2)

Extendemos la fórmula hasta la fila 13. Las columnas G y H coinciden. Sumando cualquiera de ellas comprobaremos que el total es 470.

Descargar archivo (SE9-Velocidad de lectura.xls)

Descargar archivo (SE9-Velocidad de lectura.xlsx)

lunes, 9 de enero de 2012

Gráfico de termómetro

Vamos a presentar el resultado de una encuesta con un gráfico que simula un termómetro clásico graduado desde el 0 hasta el 100.

El primer paso consiste en diseñar el termómetro. Lo haremos, íntegramente, con las herramientas que nos proporciona Excel.

Accedemos a Insertar + Formas + Formas básicas + Cilindro y dibujamos un cilindro, que constituirá el tubo del termómetro. Hacemos clic con el botón derecho en el cilindro, elegimos Formato de forma y ponemos los siguientes valores:
  • Relleno → Relleno: Sin relleno
  • Color de línea → Color de línea: Línea sólida / Color: Rojo
  • Estilo de línea → Ancho: 1,5 pto
  • Tamaño → Tamaño y giro  → Alto: 6,19 cm / Ancho: 0,64 cm
Accedemos a Insertar + Formas + Formas básicas + Elipse y dibujamos encima del tubo una pequeña elipse que nos servirá para tapar el tubo por la parte superior. Las propiedades de esta elipse deben ser:
  • Relleno → Relleno → Relleno degradado / Tipo: Lineal / Dirección: Lineal arriba / Puntos de degradado: 2 (Izquierdo: Rojo / Derecho: Negro)

  • Color de línea → Color de línea: Sin línea
  • Tamaño → Tamaño y giro → Alto: 0,2 cm / Ancho: 0,68 cm
Accedemos a Insertar + Formas + Formas básicas + Elipse y dibujamos otra elipse debajo del tubo, que será el depósito del termómetro. Propiedades:
  • Relleno → Relleno: Relleno degradado / Tipo: Lineal / Dirección: Lineal derecha / Puntos de degradado: 2 (Izquierdo: Rojo / Derecho: Negro)
  • Color de línea → Color de línea: Sin línea
  • Tamaño→ Tamaño y giro → Alto: 1,83 cm / Ancho: 1,24 cm
Hasta este momento, las tres piezas habrán quedado separadas (Fig. 1). Para colocarlas en la posición correcta conviene ampliar el zoom hasta 300 ó 400. El resultado puede que no sea del todo preciso (Fig. 2). Para alinear los tres objetos, los seleccionamos manteniendo pulsada la tecla Ctrl (Fig. 3) y accedemos a Herramienta de dibujo + Formato + Alinear + Alinear verticalmente (Fig. 4).

Ahora, vamos a hacer una copia del tubo para crear una sombra. Hacemos clic en el tubo para seleccionarlo y, manteniendo pulsadas las techas Mayúscula + Ctrl, arrastramos el tubo hacia la derecha. Obtendremos una copia exactamente igual a la original desplazada horizontalmente a su derecha.

Hacemos clic con el botón derecho en la copia, elegimos Formato de forma y ponemos los siguientes valores:
  • Relleno → Relleno: Relleno degradado / Tipo: Lineal / Dirección: Lineal derecha / Puntos de degradado: 2 (Izquierdo: Rojo / Derecho: Blanco) / Transparencia: 45 %
  • Color de línea → Color de línea: Sin línea
Para terminar el diseño sólo nos falta agrupar los tres componentes del termómetro para que se comporten como un único objeto. Manteniendo pulsada la tecla Ctrl, seleccionamos el depósito, el tubo y la tapa, accedemos al menú contextual (botón derecho) y elegimos Agrupar + Agrupar.

Con los datos del rango B4:C5, vamos a hacer un gráfico de columnas apiladas. Seleccionamos B4:C5 y accedemos a Insertar + Columnas + Columnas apiladas. El gráfico está hecho pero va a ser necesario modificar muchas cosas.

En primer lugar, hay que intercambiar la leyenda y el rótulo del eje horizontal. Con el gráfico seleccionado, accedemos a Herramientas de gráficos + Diseño + Cambiar entre filas y columnas. Además, debemos eliminar el título del gráfico y las líneas de división principales (seleccionar y Supr).

Ahora, vamos a añadir dos nuevas series al gráfico. Hacemos clic con el botón derecho en el gráfico y, en el menú emergente, seleccionamos Seleccionar datos.

Pulsamos el botón Agregar. En Nombre de la serie, ponemos: Base (o el nombre que nosotros queramos). En Valores de la serie, ponemos: -10. Terminamos pulsando Aceptar.

Habremos vuelto a la ventana Seleccionar origen de datos. Volvemos a pulsar el botón Agregar para añadir otra serie. En Nombre de la serie, ponemos: Marca (u otro nombre que nos parezca más oportuno). En Valores de la serie, ponemos: =Hoja1!$C$5. Terminamos pulsando Aceptar.

Cerramos el cuadro de diálogo Seleccionar origen de datos. El gráfico no habrá quedado así:

Las columnas granate y azul simularán el líquido del interior del termómetro (las pondremos de color rojo con una sombra negra). La columna verde la sustituiremos por un marcador. Esto se hace cambiando el tipo de gráfico (sólo el de esta columna). Más adelante ocultaremos el marcador pero mostraremos el valor de la serie. Vamos paso a paso.

Hacemos clic con el botón derecho en la serie Base (columna granate), seleccionamos Dar formato a serie de datos y ponemos las propiedades siguientes:
  • Opciones de serie → Opciones de serie → Ancho de intervalos: 0 %
  • Relleno → Relleno → Relleno degradado / Tipo: Lineal / Dirección: Lineal derecha/ Puntos de degradado: 2 (Izquierdo: Rojo / Derecho: Negro)
  • Color de borde → Color de borde: Sin línea
Repetimos estos mismos pasos con la serie Valoración (columna azul).

Hacemos clic con el botón derecho en la serie Marca (columna verde), seleccionamos Cambiar tipo de gráfico de series y elegimos XY (Dispersión) + Dispersión sólo con marcadores.

La columna verde ha sido sustituida por un marcador triangular de color verde. Hacemos clic con el botón derecho en el marcador y elegimos Agregar etiquetas de datos. A su derecha habrá aparecido el número 68 (el dato de la celda C5). Hacemos clic con el botón derecho en el número 68 y elegimos Color de fuente: Rojo

 Doble clic en el marcador triangular:
  • Opciones de serie → Opciones de serie → Trazar serie en: Eje secundario
  • Opciones de marcador → Opciones de marcador → Tipo de marcador: Ninguno
La escala vertical izquierda (de -20 a 80) sirve para las columnas de las series Base y Valoración. La escala vertical derecha (de 0 a 80) es para la serie Marca. Debemos hacer coincidir ambas escalas y, luego, debemos ocultarlas. Ambas irán de -10 a 100 ya que la encuesta tendrá una valoración entre 0 y 100 (serie Valoración) y, debajo, irá la columna de la serie Base que va de -10 a 0.

Doble clic en la escala vertical izquierda (o bien, botón derecho y Dar formato a eje)

  • Opciones del eje → Mínima → Fija: -10 / Máxima → Fija: 100 / Marca de graduación principal: Ninguno / Marca de graduación secundaria: Ninguno / Etiquetas del eje: Ninguno

Ponemos las mismas propiedades en el eje vertical secundario y eliminamos la leyenda haciendo clic y pulsando Supr.

Vamos a sacar a la derecha de las columnas la etiqueta de la serie Marca (el número 68). Para hacerlo, necesitamos mostrar el eje horizontal secundario: Herramientas de gráficos + Presentación + Ejes + Ejes horizontales secundarios + Mostrar eje predeterminado.

Por defecto, el marcador de la serie Marca se coloca en la posición 1 del eje horizontal secundario. Como queremos ponerlo a la derecha, hay que cambiar la escala y dejarla entre 0 y 1.

Doble clic en el eje horizontal secundario:
  • Opciones del eje → Mínima → Fija: 0 / Máxima → Fija: 1 / Marca de graduación principal: Ninguno / Marca de graduación secundaria: Ninguno / Etiquetas del eje: Ninguno
También debemos quitar el eje horizontal primario (el inferior). Para ello, accedemos a Herramientas de gráficos + Presentación + Ejes + Eje horizontal primario + Ninguno.

El número 68 no tiene sitio y nos ha quedado montado sobre la columna roja. Hacemos clic en el Área de trazado y arrastramos un poco hacia la izquierda el tirador central del borde derecho.

Lo que nos ha quedado es una columna que empieza en -10 y llega a una altura de 68. Naturalmente, necesitamos estrechar la columna para que quepa dentro del tubo del termómetro y eliminar los bordes del Área de trazado y del Área del gráfico. Lo haremos arrastrando hacia la izquierda el borde derecho desde los 4 puntitos que hay en ese borde. Después de unos cuantos ajustes (también hay que reducir la altura del gráfico), la hoja nos habrá quedado así:

Hay que montar las tres piezas. Arrastramos el tubo transparente sobre el gráfico y, luego, colocamos el termómetro encima. Si es necesario, traemos el termómetro al frente (botón derecho y Traer al frente + Traer al frente)

Ahora, ponemos las graduaciones del termómetro. En C5 ponemos un cero. Trazamos una línea horizontal pequeñita justamente en el cero. Repetimos el proceso poniendo 50 y 100. Las otras líneas (más largas) las ponemos de 10 en 10. Los números 0, 50 y 100 los ponemos insertando cuadros de texto a los que hay que quitar el relleno y el borde.

Para evitar que en C5 se puedan escribir números mayores que 100 o menores que 0, usamos Validación de datos. Para ello, estando en C5, accedemos a Datos + Validación de datos y dejamos los valores siguientes:

En la parte inferior del termómetro ponemos otro cuadro de texto vinculado a la celda B5. Para ello, con el cuadro de texto seleccionado, escribimos en la barra de fórmulas: =$B$5