Cualquier traducción errónea que encuentres solamente se debe al autor de
este blog. Si me la comentas será corregida en una próxima revisión.
Si te gusta el ajedrez, y tienes un buen nivel en alguno de estos idiomas,
puedes colaborar en la revisión de las traducciones, editando el siguiente
archivo de OneDrive:
Hay que revisar las traducciones de estas dos hojas 'Lessons' y
'Languages'.
Si quieres que añada algún idioma más, por ejemplo, ruso, catalán, euskera,
gallego o ¿por qué no?
el español colombiano, coméntamelo y procuraré que esté disponible en la siguiente revisión del
archivo que puedes descargar un poco más abajo.
Descarga y aprende ajedrez en 6 idiomas
Descarga la versión 3.2 del archivo desde estos enlaces:
Abre la plantilla y presiona el botón: Habilitar edición
En versiones recientes de Excel aparece el aviso: RIESGO DE SEGURIDAD
Microsoft ha bloqueado la ejecución de macros porque el origen de este
archivo no es de confianza. Con un botón:
Obtener más información
Para desbloquear el archivo se debe abrir el Explorador de archivos y
hacer clic con el botón derecho del ratón sobre el archivo, seleccionando
Propiedades.
Marcar la casilla: Desbloquear y hacer clic en el botón:
Aplicar
Abrir el archivo de nuevo, apareciendo una
ADVERTENCIA DE SEGURIDAD: Las macros se han deshabilitado. Presiona el botón:
Habilitar contenido
Esta plantilla contiene macros y está protegida sin contraseña, por lo que
se pueden estudiar y analizar las fórmulas y el código VBA.
Cómo agregar las voces de profesores nativos
Para conseguir que Excel pronuncie voces en varios idiomas, hay que seguir
las instrucciones que he publicado en este artículo:
Porque es necesario agregar los paquetes de voz, desde la Configuración de
Windows, de los 6 idiomas elegidos para aprender ajedrez: español, inglés,
francés, italiano, alemán y portugués.
Desde el Panel de Control se puede cambiar la configuración de texto a voz,
y seleccionar la voz de Excel por defecto, pero es un procedimiento manual.
Cómo elegir al profesor
El profesor se elige con la lista desplegable de la celda C5, lo que permite
aprender ajedrez en varios idiomas y con la voz del profesor elegido.
Para conseguir que Excel pronuncie automáticamente la voz del profesor
elegido:
Se obtiene el idioma de Office la primera vez que se abre el archivo
descargado.
Se genera la lista de voces disponibles en Excel y se guarda en la hoja
'Voices'.
Se genera el desplegable de la celda C5 con las voces e idiomas, por
ejemplo, en inglés se generan dos voces: la voz masculina de David y la
voz femenina de Zira. En los demás idiomas sólo se genera una voz.
Se traducen las lecciones al idioma del profesor elegido.
Haciendo clic en la celda O5 se silencia la voz o se escuchan las
lecciones con el oído.
Cómo aprendo a jugar al ajedrez con Excel
En los siguientes artículos explico las principales características del
archivo descargado:
🔝Select the language of this blog post in the Google box at the top
left.
A veces oigo voces
Cómo cambiar la voz de Excel es algo que sabía hacer manualmente, pero ahora
me he propuesto hacerlo automáticamente para poder incluir cambios de voz en
aplicaciones Excel, como la nueva versión que estoy desarrollando para
Aprender ajedrez en tu idioma, o incluso en más lenguas o idiomas.
Excel puede llegar a hablar muchas lenguas, pero hay que enseñarle las
voces, ¡como si fuera un crío!
¿Sabes cuántas voces es capaz de hablar Excel en tu equipo?
¿Sabes cómo usar más voces en Excel?
Si no lo sabes, sigue leyendo...
Si sabes cambiar manualmente la voz a Excel, sigue leyendo...
Si ya sabes cómo cambiar automáticamente la voz a Excel, podrías decirnos
si usas el método que explico a continuación, para lo que tendrás que
seguir leyendo...
Si no sigues las instrucciones del archivo que puedes descargar un poco
más abajo, Excel solamente sabrá hablar el idioma por defecto, definido
durante la instalación.
Es hora de que Excel sea multilingüe. 😜
En el archivo que puedes descargar más abajo hay 3 hojas:
Instrucciones:
Cómo instalar voces en Windows
Cómo cambiar las voces en Excel
Cómo cambiar automáticamente las voces en Excel
Cómo hacer que Excel hable muchas lenguas
Voces: Con la lista de voces disponibles en Excel.
Habla: Para hacer que Excel hable.
Esta imagen es de la hoja 'Voces'. Como puedes ver, en mi equipo
tengo instaladas 4 voces disponibles en Excel: dos en inglés, una en alemán
y la española por defecto.
¿Cuántas voces le oyes decir a Excel?
¿Quieres hacer hablar a Excel con distinta voz?
Si quieres practicar el cambio automático de voz de Excel, puedes hacerlo
descargando el archivo siguiente, y siguiendo las instrucciones que explico
a continuación.
Abre la plantilla y presiona el botón: Habilitar edición
En versiones recientes de Excel aparece el aviso: RIESGO DE SEGURIDAD
Microsoft ha bloqueado la ejecución de macros porque el origen de este
archivo no es de confianza. Con un botón:
Obtener más información
Para desbloquear el archivo se debe abrir el Explorador de archivos y
hacer clic con el botón derecho del ratón sobre el archivo, seleccionando
Propiedades.
Marcar la casilla: Desbloquear y hacer clic en el botón:
Aplicar
Abrir el archivo de nuevo, apareciendo una
ADVERTENCIA DE SEGURIDAD: Las macros se han deshabilitado. Presiona el botón:
Habilitar contenido
Esta plantilla contiene macros y está protegida sin contraseña, por lo que
se pueden estudiar y analizar las fórmulas y el código VBA.
Estas son las instrucciones a seguir para que hable el archivo descargado:
1) Cómo instalar voces en Windows
Para poder cambiar la voz a Excel, debemos instalar en Windows nuevas voces
en otros idiomas.
Hay que abrir la Configuración de Windows (con la rueda dentada)
Buscar: texto a voz, seleccionando:
Cambiar la configuración de texto a voz
En la sección de Voces, se puede elegir una voz como Helena, en
lengua española.
Haciendo clic en el botón: Vista previa de voz, se escuchará una
muestra de esa voz.
Ir a la sección de Administrar voces, donde por defecto se ven los
paquetes de voz instalados. Por ejemplo: Español (España), donde se puede
elegir una de estas voces: Helena, Pablo, Laura.
Para agregar un nuevo idioma hay que presionar el botón con el símbolo
+ en Agregar voces.
Por ejemplo, la lengua inglesa es diferente para varios países.
En la ventana con los Paquetes de voz se selecciona uno de los
idiomas, marcando su casilla, y se presiona el botón: Agregar
AVISO: Para usar las voces nuevas, hay que cerrar y volver a abrir la
Configuración, así como cualquier aplicación que use las voces.
Una vez instalado un paquete de voz por ejemplo, el Inglés de Estados
Unidos, se podrán elegir varias voces nuevas: David, Zira, Mark.
Pero falta un detalle fundamental, Excel no habla en la voz seleccionada con
la voz elegida en la Configuración de Windows, sino con un número de
voces más limitado, como veremos a continuación.
2) Cómo cambiar las voces en Excel
Para cambiar las voces en las que puede hablar Excel debemos abrir el
Panel de Control y buscar: texto a voz
En Reconocimiento de voz, hacer clic en:
Cambiar la configuración de texto a voz
Debemos abrir la pestaña de Texto a voz, con las voces que son
reconocidas para convertir texto a voz, seleccionar una de las voces y
presionar el botón: Aplicar
En español solamente reconoce la voz de Helena, pero no las de Pablo y
Laura.
En el inglés de Estados Unidos reconoce las voces de Zira y David, pero no
la voz de Mark.
Para hacer hablar a Excel con otra voz, se selecciona una de las voces de la
lista desplegable en la pestaña Texto a voz y se presiona el botón:
Aplicar
A partir de ese momento, Excel hablará con la voz elegida...
3) Cómo cambiar automáticamente las voces en Excel
En la hoja 'Voces' se refresca la lista de voces disponibles en Excel
presionando el icono:
que ejecuta la macro: ListarVoces
4) Cómo hacer que Excel hable muchas lenguas
En la hoja 'Habla' escucharás como habla Excel en varias lenguas.
Selecciona un tipo de voz con el desplegable de la celda C3.
Selecciona la velocidad de la voz en la celda C4.
Selecciona el volumen de la voz en la celda C5.
Al seleccionar cualquier celda de la hoja 'Habla' se escuchará lo que esté
escrito, con la voz elegida y a la velocidad y el volumen seleccionados.
Recuerda que la voz que elijas será la que hable Excel en cualquier otro
libro de trabajo, por lo que deberás seleccionar la voz que tenía Excel por
defecto para volver a escucharlo como antes de cambiarle la voz.
Cómo cambiar la voz de Excel con macros
Las macros están en el módulo: MóduloHabla
Entrar en el Editor VBA con la combinación de teclas: Alt + F11
Para que funcionen las voces, en el menú:
Herramientas > Referencias, debe estar marcada la referencia a:
Microsoft Speech Object Library
Si quieres oír otras voces que las que tiene Excel por defecto, o quieres
que hable otros idiomas, lo primero es saber qué número es el de la voz
deseada.
La macro que lista las voces es: ListarVoces
Esta macro escribe la lista de voces en una hoja activa.
Por ejemplo, si la voz número 2 es la de Zira en inglés, se usarán estos
argumentos de la macro: Hablar, para escuchar esta voz.
Hablar "Excel is talking to me.", 2, 1, 100
El primer argumento es la frase a decir.
El segundo argumento es el número de voz, un 2.
El tercer argumento es la velocidad de la voz, un 1.
El cuarto argumento es el volumen de la voz, un 100 es el máximo.
MEJORA:
La instrucción voz.Pause permite interrumpir la frase que Excel está
hablando y continuar con otra frase, si se selecciona una nueva celda antes
de que acabe de decir la frase anterior.
Videotutorial para cambiar la voz
Si no quieres leer las instrucciones escritas, mira el vídeo y aprenderás a
cambiar la voz de Excel:
El próximo artículo para Aprender ajedrez en 6 idiomas será la versión con las lecciones en 6 o más idiomas, usando lo
aprendido en este artículo para cambiar la voz de las lecciones que
pronunciará Excel en tiempo real.
🔝Select the language of this blog post in the Google box at the top
left.
I've designed an Excel workbook that aims to help teachers and educators to
teach how to play chess, with the help of Excel. And students, and those who
don't know it yet, can learn chess with Excel, practicing the moves
of the pieces, as well as playing chess games.
This way, my grandson can learn to play, and
chess teachers will not get bored teaching the basic rules of chess
to children and beginners. Each lesson contains theoretical topics and can
be practiced on a chessboard, made with a single Excel chart, in which the
pieces move according to the rules of the game, programmed with Excel
formulas, without the help of VBA macros.
What I have dared to do is to program the chess rules with Excel formulas, insert the chessboard and pieces in an Excel chart, and create several
lessons to teach beginners how to play chess, so
I intend to make it easier to teach the game for chess teachers, who
surely know how to use Excel, as it is much easier to understand than this
complex game.
EXTRAS:
You can play a chess game and record it, so that you can create your own chess game database.
This application allows you to
learn chess in both English and Spanish, selecting the language with
the drop-down menu in cell C5:
WARNING:Lessons will be heard in the default voice in Excel. When you
change languages it will be necessary to change the text-to-speech settings
in the Control Panel, and select a different voice in the language you want
to hear. Voices cannot be changed from within Excel. And you can only choose
one voice at a time!
By following these lessons you will learn how to play chess and you will
also learn how to use this application. You will also be able to review
games from chess history such as "The Opera Game".
This version is much more advanced than the first Spanish version, which was
designed entirely in Spanish, and which can be found here:
I have to thank the selfless help of my friend
Carlos Arroyo, a great scholar of English philology, who has revised the English
translation. Of course, any errors found in the translations are mine alone.
Download Learn Chess with Excel
Download Learn Chess with Excel v2.1, in both English and
Spanish versions, from this link:
🔝Select the language of this blog post in the Google box at the top
left.
ACTUALIZACIÓN del 29 de octubre:Descarga la versión 1.8 "definitiva" un poco más
abajo. Probada en Excel 2010 y en Excel para Microsoft 365.
He preparado un libro de Excel que pretende servir,
¡por ambición que no sea!, para que maestros y educadores
podáis enseñar a jugar al ajedrez, con la ayuda de Excel. Y los
alumnos, y cualquiera que aún no sepa, podáis aprender ajedrez con Excel, practicando los movimientos de las piezas y jugando partidas de ajedrez.
Así podrá aprender a jugar mi nieto
¡sin hacer trampas!, y los maestros de ajedrez no se aburrirán enseñando las reglas básicas
del ajedrez a los niños y principiantes. Cada lección contiene temas
teóricos y cada lección se puede practicar sobre un tablero de ajedrez,
hecho con un único gráfico de Excel, en el que mover las piezas cumpliendo
las reglas del juego, programadas con fórmulas de Excel, sin necesidad de
programar los movimientos de las piezas con macros VBA.
Este verano comentaba mi afición por el ajedrez con un amigo, que es
profesor de ajedrez en varias escuelas e institutos, y me dijo que hay pocas
aplicaciones para que los niños y jóvenes aprendan las reglas del juego del
ajedrez. Como profesor se aburre explicando las reglas básicas del
movimiento de las piezas a jóvenes que prefieren jugar a videojuegos, y los
programas de ajedrez para niños son demasiado elementales para los que se
inician a partir de los 8 o 9 años.
Que lo poco que hay está dedicado a los peques, con historietas en dibujos
animados como El pequeño Fritz, el popular programa de aprendizaje y entrenamiento de ajedrez para niños,
pero también para adultos que deseen aprender el juego de manera lúdica, con
una aventura de dibujos animados y con juegos interactivos para aprender los
movimientos de las piezas.
No pretendo competir con un cursillo hecho por algunos de los mejores
programadores de ajedrez del mundo, que incluso ganaron varios campeonatos
mundiales de ajedrez por computadora:
Lo que me he atrevido a hacer es programar las reglas del ajedrez con
fórmulas y funciones de Excel, insertar el tablero y las piezas en un
gráfico de Excel, y crear varias lecciones para enseñar a jugar al ajedrez a
los principiantes, por lo que pretendo facilitar la enseñanza del juego a
los profesores de ajedrez, que seguro que saben usar Excel, ya que es mucho
más fácil de comprender que este ajetreado juego.
Este es el aspecto de la aplicación "definitiva", con el final de la partida
Carlsen vs Niemann del 4 de septiembre de 2022, ¿se habrán hecho trampas?:
Gracias a los 400.000 visitantes de este blog
Mientras he estado construyendo este "ajetreado proyecto", mi blog ha
alcanzado a más de 400.000 visitantes, que han visto (que no creo que hayan
leído...) más de un millón de páginas, lo que creo que no está mal para ser
un blog sobre retos en Excel... Gracias a quienes me siguen y que me animan
a seguir compartiendo mis propios retos, para sacarle todo el poder a esta
excelente herramienta multiusos, tan usada y a la vez tan incomprendida.
Descarga Aprende Ajedrez con Excel
Descarga Aprende Ajedrez con Excel versión 1.8 desde
este enlace:
Abre la plantilla y presiona el botón: Habilitar edición
En versiones recientes de Excel aparece el aviso: RIESGO DE SEGURIDAD
Microsoft ha bloqueado la ejecución de macros porque el origen de este
archivo no es de confianza. Con un botón:
Obtener más información
Para desbloquear el archivo se debe abrir el Explorador de archivos y
hacer clic con el botón derecho del ratón sobre el archivo, seleccionando
Propiedades.
Marcar la casilla: Desbloquear y hacer clic en el botón:
Aplicar
Abrir el archivo de nuevo, apareciendo una
ADVERTENCIA DE SEGURIDAD: Las macros se han deshabilitado. Presiona el botón:
Habilitar contenido
Esta plantilla contiene macros y no está protegida, por lo que se pueden
estudiar y analizar las fórmulas y el código VBA.
Lecciones para aprender ajedrez
Ya puedes aprender a jugar al ajedrez con estas 27 lecciones:
Funcionalidades para aprender ajedrez
Estas son las funcionalidades desarrolladas en esta aplicación hecha en
Excel para aprender a jugar al ajedrez:
Mis vídeos sobre Ajedrez en Excel
Mira todos mis vídeos para Aprender Ajedrez con Excel, en los que
explico cómo usar Excel para jugar al ajedrez, en esta lista de reproducción
de YouTube:
Si eres experto en Excel puedes estudiar las fórmulas y macros para aprender
cómo he hecho estas lecciones en Excel.
Maestro de Excel, ¿te animas a mejorar esta aplicación para aprender
ajedrez?
Maestro de Ajedrez, ¿me ayudas a mejorar las lecciones de ajedrez?
Estos son los artículos que he escrito hasta ahora sobre ajedrez en Excel,
que te invito a leer en este enlace:
🔝To change the language of this blog post, choose it from the Google box
at the top left.
Para saber qué provincias españolas alcanzaron más temperatura durante las
olas de calor de este verano no vale con el gráfico del artículo anterior,
que puedes descargar desde aquí:
Hace falta añadir al gráfico los valores máximos de cada provincia durante
un período seleccionado de fechas:
Para ello he añadido un nuevo cuadro combinado (ComboBox) con el que
seleccionar las provincias con los TOP de valores máximos climáticos:
NO TOP: Se obtiene el mismo gráfico del anterior artículo,
seleccionando las autonomías o provincias en la segmentación de datos.
TOP 1: Se obtiene la provincia con los valores climáticos máximos
en los días seleccionados.
TOP 2: Se obtienen las 2 provincias con valores máximos.
TOP 3: Se obtienen las 3 provincias con valores máximos.
TOP 4: Se obtienen las 4 provincias con valores máximos.
TOP 5: Se obtienen las 5 provincias con valores máximos.
TOP 10: Se obtienen las 10 provincias con valores máximos.
Al seleccionar cualquiera de los gráficos, TOP 1 a TOP 10, aparecerán las
provincias con los valores máximos de hasta 14 días seleccionados en la
escala de tiempo, sin que interactúen las segmentaciones de datos de
autonomías y provincias.
En este artículo solamente explicaré los cambios que he hecho al gráfico del
artículo anterior para conseguir obtener las provincias con los valores TOP.
Vídeo de los gráficos climáticos
En este vídeo presento estos gráficos con datos del clima en España.
Tablas dinámica con valores Top del clima
En la hoja 'TOP' he incluido 6 tablas dinámicas con las que se
obtienen 6 listas de provincias ordenadas por cada uno de los valores
máximos climáticos durante el período de días seleccionados, excepto para
las temperaturas mínimas para las que se obtienen sus valores mínimos.
Pasos a seguir al insertar cada una de las tablas dinámicas:
1) Como filas de la tabla dinámica se escogen:
50 Provincias + 2 CA
Autonomías
Fecha
2) Como sumatorio de cada una de las tablas dinámicas se escoge uno de los
tipos climáticos:
Máximas (°C)
Mínimas máx. (°C)
Mínimas (°C)
Racha (km/h)
Velocidad máxima (km/h)
Precipitación (mm)
Por ejemplo, para obtener las temperaturas máximas los campos de la primera
tabla dinámica son los de la imagen de la derecha.
3) En la cinta de opciones se selecciona:
Diseño > Diseño de informe > Mostrar en formato tabular
4) En la configuración de los campos de 50 Provincias + 2 CA y de
Autonomías, se marca: Repetir etiquetas de elementos
5) Seleccionar cualquier valor de 50 Provincias + 2 CA
6) En la cinta de opciones seleccionar:
Inicio > Ordenar y filtrar > Orden personalizado...
7) Seleccionar
Descendente (Z a A) por: Máxima (ºC) o cualquier otro tipo de dato
climático, excepto para las temperaturas mínimas que hay que seleccionar
Ascendente (A a Z) por: Mínima (ºC)
Estas son las 3 primeras tablas dinámicas obtenidas:
Tabla auxiliar con las provincias
En la hoja 'Provincias' he modificado las fórmulas para
referenciarlas a las filas de las 6 tablas dinámicas de la hoja
'TOP'.
Para ello he creado un nuevo nombre definido: miNúmTOP, con la
fórmula:
=COINCIDIR(miTOP;Gráfico!$S$3:$S$9;0)
Por ejemplo, para las temperaturas máximas en la celda N4 la fórmula es:
En la hoja 'Gráfico' he añadido un cuadro combinado (ComboBox) para elegir
el tipo de TOP.
En el rango oculto S3:S9 están los posibles valores: NO TOP; TOP 1 a TOP 5 y
TOP 10, como se explicó al principio de este artículo.
En la celda S2, con el nombre definido miTOP, está el valor elegido.
Las columnas Q y siguientes se ocultan.
Por ejemplo, este es el gráfico del TOP5 de temperaturas mínimas entre los
días 12 y 25 de julio:
Zamora tuvo la temperatura mínima durante esos 14 días y se muestran sus
etiquetas por ser la primera provincia de la lista. No se han añadido
etiquetas a las otras 9 series del gráfico porque serían imposibles de leer
al superponerse unas con otras.
Descarga del gráfico con valores TOP climáticos
Descarga el
gráfico TOP climático de España desde estos enlaces:
Abre la plantilla con
una versión de Excel de escritorio a partir de Excel 2013 y
presiona el botón:
Habilitar edición
Aparece una
ADVERTENCIA DE SEGURIDAD:
Se han deshabilitado las conexiones de datos externos. Presiona el botón: Habilitar contenido
IMPORTANTE: Este archivo contiene datos climáticos de 6 meses, desde el 15 de junio
al 16 de diciembre de 2022. Para actualizar los datos climáticos del gráfico
a partir de esa fecha hay que descargar el archivo con los datos
actualizados desde este artículo:
Para actualizar los datos hay que tener los 2 archivos abiertos en la misma
carpeta y, en la cinta de opciones del archivo con el gráfico, seleccionar:
Datos > Actualizar todo, o la combinación de teclas:
Control + Alt + F5
Cuéntame si necesitas ayuda para adaptar este gráfico y para analizar
los datos climáticos extremos registrados en España durante este verano,
que han provocado varias
olas de calor récord en Europa.
🔝To change the language of this blog post, choose it from the Google box
at the top left.
Este mes de julio lo estoy dedicando a generar datos, mapas y gráficos de
las olas de calor de junio y julio en España, aunque primordialmente quiero
que sirva para enseñar técnicas y trucos en Excel.
1) Crear un libro nuevo en blanco para el gráfico.
2) Crear la hoja de 'Autonomías'.
3) Crear la tabla de Autonomías con sus provincias en 52 filas, 50
filas para las provincias de cada autonomía y 2 filas para las ciudades
autónomas de Ceuta y Melilla.
Pasos para crear una tabla dinámica con los datos del clima:
1) Abrir el archivo con el origen de los datos climáticos: Consulta Tiempo AEMET - PW1.xlsx
2) Crear la hoja 'Tiempo' en el libro nuevo.
3) Insertar una tabla dinámica desde la cinta de opciones:
Insertar > Tabla dinámica > De una tabla o rango
4) Tabla o rango: 'Consulta Tiempo AEMET - PW1.xlsx'!HistorialTiempo
5) Hoja de cálculo existente. Ubicación: Tiempo!$A$1
6)
Importante: Para poder analizar varias tablas con
Power Pivot en Excel, marque: Agregar estos datos al Modelo de datos
7) Seleccionar en la cinta de opciones: Analizar tabla dinámica > Cálculos > Relaciones
8) En la ventana Administrar relaciones, presione el botón:
Nuevo
9) En la ventana Crear relación, seleccionar:
Tabla de modelo de datos: HistorialTiempo. Columna:
Provincia
Tabla de hoja de cálculo: TablaAutonomías. Columna:
50 Provincias + 2 CA
10) Presionar el botón: Aceptar, con lo que se crea la relación entre
las dos tablas en el modelo de datos en Power Pivot.
11) Presionar el botón: Cerrar
12) En Campos de la tabla dinámica, en la sección Todas,
seleccionar los dos campos de la TablaAutonomías en este orden:
50 Provincias + 2 CA
Autonomías
13) En la cinta de opciones seleccionar:
Diseño > Diseño del informe > Mostrar en formato tabular
14) En la cinta de opciones desmarcar botones en:
Analizar tabla dinámica > Mostrar > Botones
15) Añadir el campo Fecha de la tabla HistorialTiempo en el
área de Filas.
16) Seleccionar la celda C2 y seleccionar en la cinta de opciones:
Analizar tabla dinámica > Grupo > Desagrupar
17) Seleccionar la celda A2 y seleccionar en la cinta de opciones: Analizar tabla dinámica > Campo activo > Configuración de campo
18) En la ventana de Configuración de campo, seleccionar la pestaña
Diseño e impresión y marcar:
Repetir etiquetas de elementos y presionar el botón:
Aceptar
19) Repetir los pasos anteriores con la celda B2 de Autonomías, con
lo que resulta lo siguiente:
20) Seleccionar los campos con valores climáticos de la tabla
HistorialTiempo, que irán al área de sumario de valores:
Máxima (ºC)
Mínima máx. (ºC)
Mínima (ºC)
Racha (km/h)
Velocidad máxima (km/h)
Precipitación (mm)
21) Seleccionar cada vez una celda en la fila 2 del rango de columnas D:I,
seleccionando en la cinta de opciones: Analizar tabla dinámica > Campo activo > Configuración de campo
22) Resumir campo de valor por Máx. Para las temperaturas
mínimas resumir por Mín.
Con lo que hemos diseñado una tabla dinámica con los valores climáticos
necesarios para crear el gráfico del clima.
Crear filtros de fechas, provincias y autonomías
En la tabla dinámica insertada en la hoja 'Tiempo' hay que crear 3
filtros:
Filtro con una escala de tiempos para filtrar las fechas.
Filtro con una segmentación de datos para filtrar las provincias.
Filtro con una segmentación de datos para filtrar las autonomías.
1) Teniendo seleccionada una celda de la tabla dinámica, en la cinta de
opciones seleccionar: Analizar tabla dinámica > Insertar escala de tiempo
2) En la tabla HistorialTiempo, seleccionar: Fecha,
y presionar el botón: Aceptar, con lo que se inserta la escala
de tiempo por defecto para la Fecha:
3) En la cinta de opciones seleccionar: Escala de tiempo
4) Cambiar el nivel de tiempo, de MESES a DÍAS.
5) En la sección Mostrar desmarcar las 4 casillas: Encabezado;
Barra de desplazamiento; Etiqueta de selección y Nivel de tiempo.
6) Ajustar para que se vean 14 días y seleccionarlos.
7) Cambiar el estilo de la escala de tiempo a gusto del usuario.
8) Teniendo seleccionada una celda de la tabla dinámica, en la cinta de
opciones seleccionar: Analizar tabla dinámica > Insertar Segmentación de datos
9) En la tabla de autonomías, seleccionar los dos campos, de Provincias y de
Autonomías, y presionar el botón: Aceptar
10) Ajustar las segmentaciones de datos.
11) Cambiar el estilo de las segmentaciones al gusto del usuario.
12) Mover los 3 filtros a una nueva hoja denominada 'Gráfico'.
Crear 4 tablas auxiliares
En la hoja 'Tiempo' hay que crear 4 tablas auxiliares:
Una tabla dinámica con la lista de provincias filtradas.
Una tabla para limitar el tamaño de la tabla dinámica de provincias.
Una tabla dinámica con la lista de fechas filtradas.
Una tabla para limitar el tamaño de la tabla dinámica de fechas.
1. Crear tabla dinámica con la lista de provincias filtradas:
1) Seleccionar la celda Tiempo!K1
2) En la cinta de opciones, seleccionar:
Insertar > Tablas > Tabla dinámica > Desde modelo de datos
3) Seleccionar en TablaAutonomías: 50 Provincias + 2 CA
4) Cambiar nombre de la tabla dinámica: TablaDinámicaProvincias
5) En la cinta de opciones seleccionar: Diseño > Diseño del informe > Mostrar en formato tabular
6) En la cinta de opciones seleccionar:
Analizar tabla dinámica > Filtrar > Conexiones de filtro
8) Marcar el filtro de Provincias y el de Autonomías, y
presionar el botón: Aceptar
9) En la cinta de opciones seleccionar: Diseño > Totales generales >
Desactivado para filas y columnas
10) Seleccionar una única autonomía en la segmentación de
Autonomías de la hoja 'Gráfico'.
2. Crear tabla para limitar el tamaño de la tabla dinámica de
provincias:
1) Crear una tabla en el rango K12:K13 con el nombre
TablaFinProvincias.
2) La celda K13 con la fórmula: =CONTARA($K$2:$K$11)
3. Crear una tabla dinámica con la lista de fechas filtradas:
1) Copiar la tabla dinámica de K1 en L1
2) Agregar al área de Filas el campo Fecha.
3) Quitar el campo 50 Provincias + 2 CA.
4) Cambiar el nombre de la tabla dinámica: TablaDinámicaFechas
5) En la cinta de opciones seleccionar: Analizar tabla dinámica > Filtrar > Conexiones de filtro
6) Marcar el filtro de Fecha y presionar el
botón: Aceptar
4. Crear tabla para limitar el tamaño de la tabla dinámica de fechas:
1) Crear una tabla en el rango L16:L17 con el nombre
TablaFinFechas.
2) La celda L17 con la fórmula: =CONTARA($L$2:$L$15)
El resultado son 4 tablas en la hoja 'Tiempo' con las que se consigue
limitar en las segmentaciones de datos el número máximo de provincias
seleccionadas a 10 y el número máximo de días a 14.
Si se intenta seleccionar más de 10 provincias con las segmentaciones de
Autonomía o Provincia, o más de 14 días en la escala de tiempo, las tablas
auxiliares lo impiden mostrando este aviso:
Datos climáticos por provincia
La tabla dinámica con los datos climáticos hay que transformarla antes de
crear el gráfico climático, para lo que hay que seguir estos pasos:
1) Crear una nueva hoja denominada: Provincias
2) En la celda D1 escribir la fórmula: =TRANSPONER(Tiempo!$K$2:$K$11)
& ""
3) En el rango A3:S3 escribir este encabezado y convertirlo en una tabla:
4) Cambiar el nombre de la tabla: TablaProvincias
5) En Diseño de tabla, marcar: Fila de totales
6) Cambiar el tamaño de la tabla: $A$3:$S$144, con lo que admite un
máximo de 10 provincias x 14 días.
7) Seleccionar el rango: Provincias!N3:S3, creando el nombre definido:
miTipoDatos
8) En la celda R3 de la hoja 'Gráfico' escribir la
fórmula: =TRANSPONER(miTipoDatos)
9) En la celda R2 de la hoja 'Gráfico' crear una validación de datos
con lista y origen: =$R$3:$R$8
10) En la celda Gráfico!R2 crear el nombre definido: miTiempo
11) Crear un nuevo nombre definido: miNúmTiempo con la fórmula:
=COINCIDIR(miTiempo;Gráfico!$R$3:$R$8;0)
12) Escribir fórmulas en las celdas:
B1: =miTiempo
A4: =SI(Tiempo!A2>0;Tiempo!A2 & "";"")
B4: =SI(Tiempo!B2>0;Tiempo!B2 & "";"")
C4: =SI(Tiempo!C2>0;Tiempo!C2;NOD()) y cambiar el formato de
celda: dd mmm
D4: =SI(Provincias!$A4=D$1;SI.ERROR(INDICE($N4:$S4;1;miNúmTiempo);NOD());NOD())
y arrastrar la fórmula en el rango D4:M143
N4: =SI(Tiempo!$A2="";"";Tiempo!D2) y arrastrar la fórmula en el
rango N4:S143
C144: =MAX.SI.CONJUNTO([Fecha];[Fecha];">0") y convertirlo en
fecha con el formato de celda: dd mmm
D144: =SI(miNúmTiempo=3;MIN.SI.CONJUNTO([Datos1];[Datos1];">0");MAX.SI.CONJUNTO([Datos1];[Datos1];">0"))
y arrastrar la fórmula al rango D144:M144
N144: =SUBTOTALES(104;[Máximas (°C)]) y arrastrar la fórmula al rango
N144:S144
P144: =SUBTOTALES(105;[Mínimas (°C)])
D2: =SI(D1="";"";D1 & " (" &
TablaProvincias[[#Totales];[Datos1]] & ")") y arrastrar la fórmula al
rango D2:M2
La hoja 'Provincias' tendrá una apariencia como ésta:
Cómo crear el gráfico climático
Para saber cómo insertar un cuadro combinado (ComboBox), con la lista
desplegable de los tipos de datos climáticos, lee la sección: Tratamiento
del mapa climático, en este artículo:
En la hoja 'Gráfico' se tendrán colocados los siguientes elementos
interactivos:
Un cuadro combinado para seleccionar el tipo de dato climático, que es una
réplica exacta del desplegable en R2 que se ocultará...
Una escala de tiempos para seleccionar hasta 14 días.
Una segmentación de datos para seleccionar autonomías.
Una segmentación de datos para seleccionar hasta 10 provincias.
Pasos para crear el gráfico climático:
1) En la hoja 'Provincias' seleccionar el rango combinado de los
nombres de las series (C2:M2) y de los valores de las series (C4:M143). Para
ello seguir estos pasos:
Seleccionar la hoja 'Provincias'.
Seleccionar la celda C2.
Mantener pulsada la tecla de Mayúsculas.
Presionar una vez la tecla Fin y una vez la tecla de cursor hacia la
derecha.
Soltar la tecla de Mayúsculas.
Manteniendo pulsada la tecla Control, seleccionar con el ratón la celda
C4.
Soltar la tecla Control.
Mantener pulsada la tecla de Mayúsculas.
Presionar una vez la tecla Fin y una vez la tecla de cursor hacia abajo.
Presionar una vez la tecla de cursor hacia arriba para no seleccionar la
fila de totales.
Soltar la tecla de Mayúsculas.
Mantener pulsada la tecla de Mayúsculas.
Presionar 10 veces la tecla de cursor hacia la derecha.
2) En la cinta de opciones seleccionar:
Insertar > Gráfico recomendado
3) En la cinta de opciones seleccionar:
Diseño de gráfico > Seleccionar datos, y comprobar que el
Rango de datos del gráfico es:
=Provincias!$C$2:$M$2;Provincias!$C$4:$M$143
4) En la cinta de opciones seleccionar:
Diseño de gráfico > Cambiar tipo de gráfico, y seleccionar:
Línea con marcadores
5) Situar el formato de la leyenda hacia la derecha.
6) En la cinta de opciones seleccionar: Diseño de gráfico > Cambiar colores, seleccionar: Paleta monocromática 2
7) Cortar el gráfico con la combinación de teclas: Control + X
8) Pegar el gráfico en la hoja 'Gráfico' con la combinación de
teclas: Control + V
9) Quitar el borde exterior del gráfico.
10) Poner en negrita el título del gráfico, la leyenda y los ejes
vertical y horizontal.
11) En la celda E2 escribir la
fórmula: =SI(DERECHA(miTiempo;4)="(°C)";"Temperaturas ";"") &
miTiempo
12) Seleccionar como título del gráfico la celda E2, que debe estar oculta
detrás del gráfico.
13) En opciones de eje poner el límite mínimo a 30. Para aprender a hacerlo
ver el vídeo de mas arriba.
14) Seleccionar la serie que aparece primera en la leyenda, en este caso:
Alicante.
15) En la cinta de opciones seleccionar: Diseño de gráfico > Agregar elemento de gráfico > Etiqueta de datos
> Encima
16) Poner en negrita las etiquetas de datos de Alicante.
17) En opciones de eje del límite mínimo presionar el botón:
Restablecer. Para aprender a hacerlo ver el vídeo de mas arriba.
Este es el gráfico climático obtenido siguiendo los pasos anteriores:
NOTA: Entre paréntesis está la temperatura máxima alcanzada por cada provincia
en los días seleccionados.
Si has leído hasta aquí habrás aprendido a hacerlo por ti mismo. Si no
quieres aprender a hacerlo puedes descargar y probar el gráfico a
continuación.
Descarga del gráfico climático
Descarga el
gráfico climático de España desde estos enlaces:
Abre la plantilla con
una versión de Excel de escritorio a partir de Excel 2013 y
presiona el botón:
Habilitar edición
Aparece una
ADVERTENCIA DE SEGURIDAD:
Se han deshabilitado las conexiones de datos externos. Presiona el botón: Habilitar contenido
IMPORTANTE: Este archivo contiene datos climáticos desde el 15 de junio hasta el 22
de julio. Para actualizar los datos climáticos del gráfico a partir de esa
fecha hay que descargar el archivo con los datos actualizados desde este
artículo:
Para actualizar los datos hay que tener los 2 archivos abiertos en la misma
carpeta y, en la cinta de opciones del archivo con el gráfico, seleccionar:
Datos > Actualizar todo, o la combinación de teclas:
Control + Alt + F5
ATENCIÓN: En el siguiente artículo ampliaré la funcionalidad de este gráfico,
añadiendo el TOP5 y el TOP10 de provincias con más valor climático, para
tener el registro de temperaturas que han sufrido los
españoles durante las olas de calor de junio y julio.
Este blog pasa a ser un archivo histórico
-
Durante muchos años he utilizado este blog para compartir artículos,
reflexiones, recursos y experiencias relacionadas con la tecnología, la
administrac...
py_RC Design with 3D Cache
-
Following my recent post on passing 3D arrays from Python to Excel I have
now added a py_UmomC function to the py_RC Design spreadsheet. The new
spreadshee...
Números de pastel
-
Estos números constituyen una extensión natural de los traducidos como
“Catering perezoso” (o también como “Cortador perezoso”), que ya se han
estudiado ...
Build a Daily Events Calendar in Excel
-
Use my Excel events calendar to track meetings and events inside a
workbook. Add your upcoming events to a list, set a start date, and then
see those items...
Cómo hacer gráficos en Excel
-
Excel es una de las herramientas más potentes y versátiles para el análisis
y la presentación de datos. Los gráficos en Excel no solo ayudan a
visualizar...
Análisis DAFO (FODA, DOFA) las decisiones con Excel
-
Para conocer la situación de una empresa, proyecto o persona, recurrimos al
análisis DAFO (FODA, DOFA) en la toma de decisiones con Excel. El los años
sese...
How To Predict Bearing Life With Excel
-
When you work in mechanical engineering, understanding the reliability and
performance of bearings under various conditions is crucial. Bearings are
the co...
TikTok’s search evolution
-
2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are
more likely to rely on TikTok than Google as a search engine. More than
half of...
Unblocking and Enabling Macros
-
When Windows detects that a file has come from a computer other than the
one you're using, it marks the file as coming from the web, and blocks the
file....
London Excel Meetup Workbooks
-
The workbooks used in my presentation on “Analytical and Interactive
Dashboards in Excel” at the London Excel Meetup, September 3, 2020
International Keyboard Shortcut Day 2019
-
The first Wednesday of every November is International Keyboard Shortcut
Day. This Wednesday, people from all over the world will become far less
efficient...
Welcome, Prashanth!
-
Last March, I shared that we were starting to look for a new CEO for Stack
Overflow. We were looking for that rare combination of someone who… Read
more "W...
Salvador Sostres, analfabeto profesional
-
Los nuevos tiempos traen nuevas profesiones. Internet, además, ha
revolucionado el mundo del periodismo y la palabra escrita. Adaptarse o
morir, ese es el ...
Planificación de compras
-
Realizar una lista con los productos que necesitamos y que formarán parte
de nuestra cesta de la compra nos ayuda a *encontrar la combinación de
bienes p...
Mis metas son seguir superando nuevos retos en Excel y compartirlos en mi blog, para sacarle todo el poder a esta excelente herramienta multiusos, tan usada y a la vez tan incomprendida, para así poder mejorar nuestros conocimientos de Excel.
