🔝To translate this blog post to your language, select it in the top left Google box.
¿A quien no le gustan los juegos retro, los juegos clásicos de toda la vida?
¡A mí sí!
Este artículo está escrito para versiones de Excel retro, de Excel 2010 en adelante.
¡Porque no todo el mundo tiene la última versión de Excel instalada en su escritorio!
Aquí aprenderás cómo convertir unos gráficos, creados con las versiones más recientes de Excel, para que sean compatibles con versiones de Excel clásicas, antiguas o retro.
Se presupone la compatibilidad de versiones Excel antiguas cuando se ejecutan en versiones Excel más recientes pero, un Excel creado con versiones recientes de Excel no tiene por qué funcionar en versiones antiguas de Excel, debido a que hay funciones que no son retro compatibles...
En los últimos 15 años de la historia de Microsoft Excel para Windows se han liberado 6 versiones de Excel de escritorio, desde Excel 2010 hasta Excel 2024, además de Excel para Microsoft 365 que es un servicio de suscripción, y de Excel para la Web que es una versión gratuita que corre en navegadores Web.
En todas esa versiones de Excel funcionan los gráficos de barras, los gráficos de columnas y los gráficos de dispersión por lo que, en un principio, se pueden visualizar los gráficos de aranceles con colores difusos, que publiqué en el artículo anterior de este blog:
Léelo para comprender cómo se hicieron estos gráficos, y quienes tuvieron las brillantes ideas originales para hacer estos gráficos difusos, tan impactantes visualmente, con una única serie definida para el gráfico de barras o de columnas.
En los últimos 15 años me ha tocado lidiar con problemas de incompatibilidad entre versiones de Excel, pues he tenido que compatibilizar versiones antiguas, por ejemplo Excel 2003, con versiones modernas como Excel 2016, para que una plantilla determinada corriera en cualquiera de esas versiones de Excel.
En este caso el ejercicio de compatibilidad consiste en que:
Unos gráficos creados en versiones modernas de Excel, sean visualizados en versiones antiguas de Excel.
¡Y lo he conseguido!
Como evidencias observa esta imagen animada de un par de pantallazos visualizados en Excel 2010 de los gráficos de aranceles con colores difusos:
Una vez que conseguí crear gráficos compatibles en versiones antiguas de Excel, me quedaba un problema por resolver: Cómo mostrar las etiquetas con los nombres de los países.
En una de las imágenes de arriba. creada con Excel 2010, no se ven las etiquetas. en la otra sí se ven.
Si sigues leyendo te enterarás cómo he resuelto el problema de mostrar las etiquetas mediante dos procedimientos totalmente distintos...
Para estos gráficos, el problema de incompatibilidad entre versiones de Excel es debido al origen de datos de los gráficos, obtenido mediante fórmulas construidas con las funciones de las versiones más recientes de Excel, que generan error en versiones de Excel antiguas, en las que esas funciones no existen.
En las versiones más recientes de Excel, la fórmula de la celda AUX!A2 son los valores del eje horizontal del gráfico de columnas y los valores del eje vertical del gráfico de barras, con el nombre definido: filasEje
Esta fórmula se basa en las siguientes funciones, que son compatibles con las versiones de Excel descritas a continuación:
LET: Se aplica a Excel para Microsoft 365; Excel para la Web; Excel 2024; Excel 2021.
SECUENCIA: Se aplica a Excel para Microsoft 365; Excel para la Web; Excel 2024; Excel 2021.
EXCLUIR: Se aplica a Excel para Microsoft 365; Excel para la Web.
REDUCE: Se aplica a Excel para Microsoft 365; Excel para la Web.
APILARV: Se aplica a Excel para Microsoft 365; Excel para la Web.
LAMBDA: Se aplica a Excel para Microsoft 365; Excel para la Web; Excel 2024.
Por lo que una fórmula basada en todas estas funciones únicamente es compatible con dos versiones: Excel para Microsoft 365 y Excel para la Web, que son las que son compatibles con todas las funciones más recientes de Excel.
En cualquier otra versión anterior de Excel dicha fórmula generará errores de valor: #¡VALOR!, o de que la función no existe: #¿NOMBRE?, pues son retro incompatibles en versiones antiguas.
Incluso los gráficos difusos son incompatibles con la última versión de escritorio, que es Excel 2024, lo que nos obliga a pagar por una suscripción mensual o anual a Microsoft 365, para poder crearlos y visualizarlos. Aunque también podemos verlos en Excel para la Web, como hice en el artículo anterior, insertándolos en la nube de Microsoft OneDrive.
Como me gustó muchísimo el gráfico propuesto por Roberto Mensa en este artículo en inglés:
Desde que lo publicó me propuse el reto de hacerlo compatible con versiones retro antiguas de Excel, a partir de Excel 2010, lo que me ha obligado a modificar las fórmulas que generan el origen de datos de los gráficos, eliminando cualquier función incompatible con las versiones no tan recientes de Excel.
Una vez analizada la fórmula que genera el origen de datos del gráfico, sustituí las funciones LET, LAMBDA, etc., que no existen en Excel 2010, modificando la fórmula en AUX!A2 así:
Esta es una fórmula matricial, en la que no hay que escribir los corchetes {}, que aparecen automáticamente pulsando a la vez la combinación de teclas: Control + Mayús + Intro. En inglés se conocen como fórmulas matriciales CSE, al introducirlas con las teclas: Ctrl + Shift + Enter.
Para saber más sobre las diferencias entre las nuevas fórmulas de matriz dinámica vs las fórmulas de matriz heredadas, lee este artículo:
- Fórmulas de matriz dinámica frente a fórmulas de matriz CSE heredadas - Soporte técnico de Microsoft
En la hoja 'AUX' están escritas las fórmulas retro matriciales necesarias para crear el origen de datos de los gráficos en versiones retro de Excel.
En este pantallazo del Administrador de nombre están todos los nombres definidos para hacer que los gráficos sean retro compatibles:
Con la fórmula matricial comentada anteriormente se genera el nombre definido usado como rango de rótulos del eje en los gráficos.
filasEje: Devuelve un rango de valores mayor que cero en el rango AUX!$A:$A, comenzando por la celda A2.
=AUX!$A$2:INDICE(AUX!$A:$A;CONTAR.SI(AUX!$A:$A;">0")+1)
Dicha fórmula matricial de la columna A, usa 3 nombres definidos:
secFilas: Devuelve una secuencia de números enteros, del 1 al número de filas de la tabla de aranceles.
=FILA(AUX!$A$1:INDICE(AUX!$A:$A;nFilasSubtotales+SI(nFilasSubtotales=nFilasTotales;1;0)))
filasRepeticiones: Devuelve una secuencia de números enteros, del 1 a la suma de la secuencia de repeticiones.
=FILA(AUX!$A$1:INDICE(AUX!$A:$A;SUMA(secRepeticiones)))
secRepeticiones: Devuelve un rango de valores mayor que cero en el rango AUX!$K$2:$K$31
=AUX!$K$2:INDICE(AUX!$K$2:$K$31;CONTAR.SI(AUX!$K$2:$K$31;">0"))
En el rango AUX!$K$2:$K$31 hay una fórmula matricial con el número de repeticiones según una escala de los valores del filtro de aranceles:
{=COINCIDIR(filasFiltro2;secEscala;1)}
filasFiltro2: Nombre definido para obtener los valores filtrados de la tabla de aranceles en el rango AUX!$G$2:$G$31
=AUX!$G$2:INDICE(AUX!$G$2:$G$31;nFilasSubtotales+SI(SI.ERROR(COINCIDIR(-1%;AUX!$G$2:$G$31;0);0)>0;0;1))
filasFiltro1: Se escribe como fórmula matricial en el rango AUX!$G$2:$G$31 con los aranceles visibles filtrados de la lista de aranceles de la tabla.
=SI.ERROR(INDICE(listaAranceles; K.ESIMO.MENOR(SI(listaVisible=""; FILA(listaAranceles)-6); FILA(listaAranceles)-6));-1%)
secEscala: La escala de colores de corte según el valor de los aranceles y el valor de difusión de los colores.
=(FILA(AUX!$A$1:INDICE(AUX!$A:$A;nColores))-1)/nColores*nMax
siendo nMax: =MAX(filasFiltro2)
nColores: El valor de difusión de los colores de 5 a 25.
='Aranceles difusos'!$H$3
En el rango AUX!$B$2:$B$501 hay una fórmula matricial, con la que se obtienen los valores de aranceles de cada fila del eje. Este método es mucho más simple y efectivo que en la versión original con fórmulas llamando a funciones recientes, usando únicamente la conocida función INDICE:
{=INDICE(filasFiltro2;filasEje)}
filasValores: Son los valores de la serie, tanto en el gráfico de barras como en el de columnas, con los aranceles repetidos según la escala de colores difusos:
=AUX!$B$2:INDICE(AUX!$B:$B;CONTAR.SI(AUX!$A:$A;">0")+1)
Para las etiquetas con los nombres de los países, que se visualizan en una serie del tipo gráfico de dispersión, se usan dos rangos con fórmulas matriciales:
Fórmula matricial en el rango AUX!$D$2:$D$31
{=FILA($A$1:INDICE($A:$A;nFilasSubtotales))}
EtiquetaX: Valores de la serie en el gráfico de dispersión.
=AUX!$D$2:INDICE(AUX!$D$2:$D$31;nFilasSubtotales)
Fórmula matricial en el rango AUX!$D$2:$D$31
{=EtiquetaX*0}
EtiquetaY: Valores de la serie en el gráfico de dispersión.
=AUX!$E$2:INDICE(AUX!$E$2:$E$31;nFilasSubtotales)
¡¡¡ RETO CONSEGUIDO !!!
¡Todas las funciones usadas en la fórmula de arriba son compatibles en Excel 2010 y versiones posteriores!
¡Y se obtienen los mismos resultados que con las fórmulas escritas en la versión más reciente de Excel, creadas por Roberto Mensa!
¡Se pueden comprobar los resultados en el rango de columnas AUX!M:P, siempre que se abra el archivo en Excel para Microsoft 365!
Como no era nada sencilla la tarea de convertir las fórmulas de matriz dinámica en fórmulas matriciales heredadas, le pedí ayuda a la IA de Microsoft Copilot, pues pienso que no hay que ser retrógrado y quedarse obsoleto, sino usar los asistentes personales expertos en Excel, como es la IA que, si se le pregunta, responderá retroalimentando y mejorando nuestras capacidades de formulación y programación. Eso sí, analizando la respuesta de la IA con sentido crítico.
Estas son las consultas que le hice a Copilot en modo pensamiento profundo (Think Deeper):
Las fórmulas generadas por Copilot usan funciones volátiles, como INDIRECTO, que prefiero no usar porque se recalculan siempre. En todas las fórmulas he usado funciones no volátiles...
Por ejemplo, para el nombre definido: filasRepeticiones, la fórmula volátil sería:
=FILA(INDIRECTO("1:" & SUMA(secRepeticiones)))
Por lo que la he convertido a una fórmula no volátil con la función INDICE:
=FILA(AUX!$A$1:INDICE(AUX!$A:$A;SUMA(secRepeticiones)))
RECOMENDACIÓN: Es fundamental mantener una actitud crítica frente a las respuestas de la IA, lo que implica tener un conocimiento profundo de las funciones de Excel, así como probar y validar las fórmulas que sugiera la IA antes de darlas por buenas.
ATENCIÓN: Las etiquetas con los nombres de los países, generadas con los gráficos de dispersión no son visibles en Excel 2010.
En versiones posteriores de Excel se permite seleccionar un rango de datos cualquiera.
A partir de versiones de Excel 2013, se obtienen las etiquetas según el Valor de las celdas seleccionadas con el botón: Seleccionar rango...
Por ejemplo, se seleccionan los nombres de los países en el rango de la tabla de aranceles:
='Aranceles difusos'!$B$7:$B$25
Pero en Excel 2010 genera error cuando trata de visualizar estas etiquetas:
Siempre muestra como etiqueta el error: [CELLRANGE], por lo que hay que buscar soluciones alternativas a este problema, aunque solamente ocurra este problema en Excel 2010.
A continuación propongo dos workarounds o soluciones alternativas.
Esta solución para Excel 2010 pasa por:
- Ocultar las etiquetas con errores en los gráficos difusos de barras y columnas.
- Crear gráficos nítidos de barras y de columnas, ocultando todo excepto las etiquetas.
- Agrupar los gráficos difusos y nítidos superpuestos para ver las etiquetas nítidas y las barras y columnas difusas.
Este tipo de soluciones son fáciles de implementar si no se puede crear el gráfico combinado de nuestros sueños, por lo que no es la solución definitiva para los gráficos de aranceles difusos en Excel 2010.
Esta es la mejor solución para Excel 2010, pues se puede hacer con un gráfico combinado:
- Eliminar la serie del tipo gráfico de dispersión que mostraba las etiquetas.
- Crear una nueva serie con un gráfico nítido de barras o de columnas agrupadas.
- Combinar los dos gráficos, colocando en el eje secundario la nueva serie.
- En el gráfico nítido del eje secundario ocultar todo excepto las etiquetas.
- Esta solución no precisa datos para las etiquetas, por lo que hay menos nombres definidos implicados.
Este el resultado en Excel 2010 con cualquiera de las dos retro soluciones alternativas propuestas:
Descarga los gráficos de aranceles con colores difusos, compatibles desde Excel 2010, desde estos enlaces:
- Gráficos compatibles a partir de Excel 2013: Aranceles con colores difusos - PW2.xlsx
- Gráficos compatibles a partir de Excel 2010: Aranceles con colores difusos - PW3.xlsx
- Gráficos compatibles con todas las versiones de Excel: Aranceles con colores difusos - PW4.xlsx
Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.
Las hojas están protegidas sin contraseña para que sea fácil analizar las fórmulas y los gráficos.
ATENCIÓN: Se puede modificar este libro de Excel respetando esta licencia:
Creative Commons — Atribución-NoComercial-CompartirIgual-No portada — CC BY-NC-SA 4.0
En este nuevo vídeo explico cómo hice los gráficos retro con colores difusos.
Sígueme si te ha parecido interesante el contenido de este artículo, y me animarás a seguir publicando más retos diseñados en Excel.
