Cómo copiar fórmulas LAMBDA

🔝To translate this blog post to your language, select it in the top left Google box.

En este artículo explicaré cómo copiar fórmulas programadas con la función LAMBDA, escritas en Excel en inglés para que las entienda Excel en español.

La fórmula original está escrita en inglés y publicada en el repositorio de códigos de GitHub. En este enlace se encuentran ejemplos de la función LAMBDA de Excel: Search · excel +lambda · GitHub 

La copia de la fórmula original me permitió modificarla y así diseñar el conversor que publiqué en mi artículo anterior:

Conversor de números a palabras | #ExcelPedroWave

En la siguiente imagen está seleccionada la celda B2, con la fórmula que usa la función LAMBDA para convertir números a palabras en inglés y en español.

También está abierto el complemento: Advanced formula environment del Excel Labs (Entorno de formulas avanzado de los Laboratorios de Excel), con el que se pueden editar las fórmulas definidas con la función LAMBA, en un entorno más amigable que el Administrador de nombres de Excel.

Este es mi segundo artículo sobre una función que permite a Excel hacer cosas que antes solamente se podían hacer con las macros en lenguaje VBA:

Función LAMBDA - Soporte técnico de Microsoft

AVISO: Como es la función más avanzada de Excel, únicamente está disponible en Excel para Microsoft 365 y en Excel para la Web.

ATENCIÓN: A continuación explico los pasos que he seguido para copiar la fórmula original, con la función LAMBDA escrita en Excel en inglés, y que inicialmente convertía números a palabras en inglés, para modificar la fórmula original en Excel en español, añadiendo la conversión de números a palabras en español.

Hicieron falta 4 intentos para conseguir copiar la fórmula original a una celda.

Hicieron falta otros 4 intentos para conseguir copiar la fórmula original como un nombre definido, usando el complemento: Advanced formula environment del Excel Labs

1) Primer intento de copia de la fórmula original a una celda

La fórmula original para convertir números a palabras en inglés, tanto americano como de la India, usando la función LAMBDA fue publicada por @Bhavya250203 en este enlace:

Convert Very Large Numbers to Words for both Indian as well as International Numbers · GitHub

Esta fórmula está escrita en inglés, por lo que no se puede copiar directamente en una celda de Excel en español.

Un método para traducir fórmulas del inglés al español está documentado en este artículo:

Cómo traducir localmente fórmulas Excel | #ExcelPedroWave

Aunque no lo recomiendo para traducir fórmulas muy largas, como la megafórmula que nos ocupa, y porque habrá que convertir el archivo del tipo .xls a .xlsm para que traduzca fórmulas con las nuevas funciones de Excel para Microsoft 365.

2) Segundo intento de copia de la fórmula original a una celda

Instalando Excel en idioma inglés se puede copiar la fórmula original.

ATENCIÓN: Se deben tener unos mínimos conocimientos de la lengua inglesa para cambiar el idioma de Office al inglés y luego revertir los cambios al español. 

Para ello selecciona en la cinta de opciones: Archivo > Opciones > Idioma

En Idioma para mostrar de Office, si no aparece Inglés (English) presiona el botón: Agregar un idioma

Selecciona el idioma que deseas instalar: Inglés (Estados Unidos) (English) y presiona el botón: Instalar

Cuando esté instalado el idioma Inglés (English), selecciónalo y presiona el botón: Establecer como Preferido

Aparece un aviso indicando que hay que reiniciar Office.

Cierra Excel y todas las aplicaciones de Office y vuelve a abrir Excel, con lo que ya se mostrará en inglés el interface de Excel.

Al copiar la fórmula original escrita en inglés en una celda de Excel en inglés, aparece un mensaje de error avisando que "There's a problem with this formula." (Hay un problema con esta fórmula).

3) Tercer intento de copia de la fórmula original a una celda

El problema es debido al separador de listas. En la fórmula original ese separador es la coma (,) cuando en Windows en español el separador de listas es el punto y coma (;)

En esta página se explica cómo seleccionar el separador de listas correcto para que las fórmulas de Exce no generen errores.

Errores de fórmula cuando el separador de lista no se establece correctamente - Office | Microsoft Learn

Lo más fácil es cambiar la Configuración de Windows:

En Formato regional se selecciona Español (Estados Unidos), con lo que el separador de listas será la coma (,)

Ahora ya podemos pegar la fórmula original en una celda de Excel en inglés con la coma como separador de listas.

Para ello se copia la fórmula original y se edita para quitarle el nombre definido: Number_To_Words, y que comience por el signo igual (=).

Pero sigue provocando un error al final de la fórmula.

4) Cuarto intento de copia de la fórmula original a una celda

La fórmula original copiada incluye el nombre definido Number_To_Words, que ya hemos quitado, pero falta algo para poder probarla como una fórmula correcta dentro de una celda de Excel.

Estudiando la función LAMBDA en el siguiente enlace se aprende a resolver el problema:

Función LAMBDA - Soporte técnico de Microsoft

Se explica en el apartado de cómo Crear una función LAMBDA:

Paso 2: Crear la función LAMBDA en una celda

Una buena práctica es crear y probar la función LAMBDA en una celda para asegurarse de que funciona correctamente, incluyendo la definición y el paso de parámetros. Para evitar el error #CALC! , agregue una celda a la función LAMBDA para devolver inmediatamente el resultado:

=función LAMBDA ([parámetro1, parámetro2, ...],cálculo) (función llamar)

El siguiente ejemplo devuelve el valor 2.

=LAMBDA(number, number + 1)(1)

Por lo que, después del paréntesis de cierre de la función LAMBDA, hay que añadir entre paréntesis los valores de los parámetros que se van a pasar a la función LAMBDA.

Con lo que, si convertimos la última línea copiada: );

Por esta línea con los valores de los parámetros a pasar a la función LAMBDA, por ejemplo: )(101,2)

Habremos conseguido introducir la función LAMBDA en la celda, dando como resultado: One Hundred One, que es el número 101 convertido en inglés.

IMPORTANTE: Guardar y cerrar el archivo con la fórmula LAMBDA en una celda.

Hay que recordar que esa fórmula ha sido pegada en un Excel en versión inglesa, y con la coma (,) como separador de listas.

• Para cambiar el separador de listas: Abre la Configuración de Windows y en Formato regional cambia a: Español (España, internacional), con lo que el punto y coma (;) será el separador de listas.

• Para cambiar Excel a español: Selecciona en la cinta de opciones en inglés: File > Options > Language. En la sección: Office display languages, selecciona: Spanish (español) y presiona los botones: Set as Preferred y OK.

Cierra Excel y al abrirlo de nuevo aparecerá en español, con la fórmula LAMBDA y sus funciones escritas en español. ¡Eureka!

5) Primer intento de copia de la fórmula a un nombre definido

Con los 4 pasos anteriores hemos conseguido copiar la fórmula original en una celda, pero lo que nos interesa es copiarla como un nombre definido en el Administrador de nombres de Excel.

Microsoft ha incluido el complemento: Advanced formula environment del Excel Labs, con el que se pueden editar las funciones definidas con LAMBA mucho mejor que con el Administrador de nombres.

Lo primero que hay que hacer es instalar el complemento Excel Labs siguiendo estos pasos:

1. Abrir el archivo descargado del artículo anterior: Conversor de números a palabras | #ExcelPedroWave 
2. En la cinta de opciones debe estar la pestaña Programador, como se explica en esta página: Mostrar la pestaña Programador - Soporte técnico de Microsoft
3. Ir a: Programador > Complementos
4. Abajo a la izquierda seleccionar: Buscar más complementos de la Tienda Office.
5. Buscar la palabra: labs

6. 

7. Presionar el botón Agregar del complemento: Excel Labs, a Microsoft Garage Project
8. Presionar el botón Continuar
9. En la pestaña Inicio aparece el icono de Excel Labs (una probeta a la derecha) en inglés, sin traducción al español...
10. Y se abren dos complementos, siendo uno de ellas: Advanced formula environment

11. 

12. Presionar el botón Open de Advanced formula environment. 
13. Se produce un fallo en la carga del complemento.

Este fallo es muy críptico y no está muy documentado.

Investigando, deduje que el fallo se debía a que el libro de trabajo estaba protegido por lo que, para desprotegerlo, en la cinta de opciones seleccione: Revisar y presioné el botón: Proteger libro, que no está protegido con contraseña.

Con lo que, presionando el botón Retry, conseguí que no fallará el complemento y pude ver en el nuevo entorno avanzado de fórmulas la función LAMBDA escrita en la celda B2 de la hoja 'LAMBDA':

6) Segundo intento de copia de la fórmula a un nombre definido

Ahora que ya funciona el nuevo editor avanzado de fórmulas, vamos a intentar copiar la fórmula original que está en el repositorio GitHub, con solo saber la URL del GitHub Gist que vamos a descargar.

En el editor avanzado se selecciona la pestaña Names y se hace clic en el icono con la nube de descarga: Import from URL to module

Se pega este enlace en el que se encuentra la fórmula original:

https://gist.github.com/Bhavya2502/8413a0e6af783ad18e72419eca47ad09

y se escribe un nombre para el módulo.

Al presionar el botón: Import, se produce un error:

¿Por qué será?

7) Tercer intento de copia de la fórmula a un nombre definido

Como la fórmula original está escrita en inglés y he leído que el entorno avanzado de fórmulas solamente está preparado para Excel en inglés, hay que usar la versión inglesa de Excel, con lo que hay que seguir los pasos indicados en el punto 2) de este artículo, configurando Excel en inglés.

Al intentar importar la fórmula original se produce el mismo error que en el punto 6) anterior.

¿Por qué será?

8) Cuarto intento de copia de la fórmula a un nombre definido

Como la fórmula original está escrita con el separador de listas con comas (,) habrá que cambiarlo como expliqué en el punto 3) de este artículo.

Al importar de nuevo la fórmula original se consigue descargar y copiar al módulo.

¡Eureka!

Modules es una nueva característica de Excel que guarda los nombres definidos en el libro de trabajo.

Y en el Administrador de nombres ha importado el nombre definido: Number_To_Words, con la fórmula original LAMBDA con funciones en inglés.

IMPORTANTE: Guardar y cerrar el archivo con el nuevo nombre definido.

Hay que volver a cambiar el separador de listas a: Español (España, internacional) y el idioma de Excel a español.

Al abrir de nuevo el archivo Excel guardado anteriormente, se puede editar la fórmula con las funciones convertidas al español.

Con lo que ya hemos conseguido descargar de la nube de HitHub una fórmula LAMBDA escrita en inglés, y la hemos importado en un módulo y en el Administrador de nombres en Excel en español.

Algún día Microsoft publicará en español el complemento: Advanced formula environment del Excel Labs (Entorno de formulas avanzado de los Laboratorios de Excel), mientras tanto "ajo y agua".

Videotutorial de cómo copiar fórmulas LAMBDA

En este videotutorial explico cómo copiar fórmulas LAMBDA escritas en inglés en un Excel en español.

Selecciona los subtítulos en tu idioma para entender el vídeo.

En el próximo artículo explicaré cómo modificar la fórmula original para convertir números enteros muy grandes a palabras en español.

¡Y para corregir algunos errores de la fórmula original que convierte números al inglés!

Conversor de números a palabras

🔝To translate this blog post to your language, select it in the top left Google box.

Conversor de números a palabras

Este es mi primer artículo sobre una función que permite a Excel hacer cosas que antes solamente se podían hacer con las macros en lenguaje VBA:

Función LAMBDA - Soporte técnico de Microsoft

AVISO: Como es la función más avanzada de Excel, únicamente está disponible en Excel para Microsoft 365 y en Excel para la Web.

Como ejercicio de ejemplo, he modificado una fórmula que convertía números a palabras en inglés americano y de la India, para convertir también números a palabras en español, como se ve en esta imagen:

Con este conversor no pretendo sustituir a la gran cantidad de conversores de números a palabras en distintos idiomas que hay en Internet, solamente quiero hacerlo en Excel sin macros, con una fórmula que llama a la función LAMBDA.

Busca, compara y si encuentras algún conversor mejor ¡úsalo!

Cómo convertir números a palabras en inglés

La fórmula original para convertir números a palabras en inglés, tanto americano como de la India, usando la función LAMBDA fue publicada por @Bhavya250203 en estos enlaces:

Convert Number to Words LAMBDA (Very Large Numbers) - Microsoft Community Hub

Convert Very Large Numbers to Words for both Indian as well as International Numbers · GitHub

Bhavya Gupta creó esta función LAMBDA "Number_To_Words" para convertir números en palabras (por ejemplo, 2813 se puede escribir como "Two Thousand Eight Hundred Thirteen" en palabras en inglés)

El primer parámetro de la función es el número a convertir y el segundo es opcional (por defecto un 1 para el sistema de número en inglés de la India y un 2 para el sistema de número en inglés americano)

No se convierten los decimales por ahora, pero eso también se puede implementar.

En la imagen anterior el resultado de la fórmula original aparece en la columna D, con esta fórmula:

=Words_Converter(Number_To_Words([@[Integer Numbers]];MyLanguageNumber))

Siendo "Integer Numbers" el número a convertir en texto y MyLanguageNumber un 1 para inglés de la India y un 2 para inglés internacional.

Con la función LAMBDA "Words_Converter" se convierten las palabras:

1. lowercase: minúsculas.
2. UPPERCASE: MAYÚSCULAS
3. Title Case: Mayúscula la primera letra de cada palabra.
4. Sentence case: Mayúscula la primera letra de la primera palabra, el resto en minúsculas.

Cómo convertir números a palabras en español

Con una modificación de la fórmula anterior, llamando a la función "Number_To_Words_IES", con la que convertir números a IES, con el segundo argumento opcional con el número de lenguaje:

1. I - Indian : Inglés de la India, por defecto.
2. E - English: Inglés.
3. S - Spanish: Español.

En la columna C está la siguiente fórmula:

=Words_Converter(Number_To_Words_IES([@[Integer Numbers]];MyLanguageNumber))

Esta fórmula, creada con la función LAMBDA, la he modificado para que convierta los números a palabras en español usando demasiadas funciones SUSTITUIR, por lo que hace falta probarla y depurarla y optimizarla algún día...

Descarga el conversor de números a palabras

Descarga el conversor desde aquí:

• Sites Google Drive: Numbers to Words Converter - PW2.xlsx

Conversor de números a palabras en la nube

Convierte números a palabras modificando estas celdas:

• B2 - Elige el lenguaje: Indian; English; Spanish.
• B3 - Cambia el tipo de letras: lowercase; UPPERCASE; Title Case; Sentence case.
• B6:B21 - Escribe los números enteros a convertir de hasta 15 cifras, del 1 al 999.999.999.999.999

En la columna C están las palabras con la pronunciación escrita de cada número.

Haz clic en el botón de Descarga para probarlo en Excel para Microsoft 365.

Si no tienes instalada la versión más reciente de Excel, puedes probarlo en la nube de Excel para la Web, sin necesidad de tener instalado Excel y sin salir de esta página:

Para ajustar el zoom de este libro incrustado:

• En el móvil o celular usa dos dedos en la pantalla, como haces para ampliar o reducir una foto.
• En el PC sitúa el cursor dentro del buscador y presiona la tecla <Control> girando a la vez la ruleta del ratón.

Cómo convertir números a palabras

En este videotutorial explico cómo convertir números enteros a palabras en indio, inglés o español.

Selecciona los subtítulos en tu idioma para entender el vídeo.

Como no puedo probar los 1.000 billones de números, ayúdame escribiendo los números incorrectamente convertidos a palabras en un comentario.

En el próximo artículo explicaré cómo programar con la función LAMBDA.

Cómo convertir unidades de medida

En Excel existe una función muy útil, poco conocida y menos usada:

Función CONVERTIR - Soporte técnico de Microsoft

Con la función CONVERTIR se pueden convertir unas unidades en otras unidades de la misma medida, lo que es muy útil para físicos, químicos, matemáticos, ingenieros, estudiantes de ciencias o cualquier usuario de Excel con necesidad de convertir unidades de medida.

Por ejemplo para convertir 1 milla en kilómetros se usa la siguiente fórmula:

=CONVERTIR(1;"mi";"km")

Donde el primer argumento es el valor numérico a convertir, un 1 en el ejemplo, el segundo argumento es la abreviatura de millas "mi" y el tercer argumento es la abreviatura de kilómetros "km".

Como hay que conocer las abreviaturas de las unidades de medida para editar los argumentos, prefiero hacerlo con un libro de Excel que he preparado para convertir automáticamente unas unidades de medida en otras.

En este conversor de medidas hay 3 segmentaciones de datos:

1. Medidas: para seleccionar un tipo de medida: Área, Distancia, Energía, Fuerza, Hora, Información, Magnetismo, Peso y masa, Potencia, Presión, Temperatura, Velocidad y Volumen (o medida líquida)
2. Prefijo: para seleccionar el prefijo de la unidad de medida, por ejemplo: kilo que es el múltiplo de mil. El signo igual (=) indica que no hay prefijo, por lo que vale la unidad.
3. Unidades: para seleccionar el tipo de unidad dependiente del tipo de medida.

Se introduce un valor numérico en la celda K2 y se obtiene una tabla en el rango K4:L28 con la conversión de todas las unidades de la medida seleccionada.

Para obtener una determinada unidad de medida se introduce su prefijo en la celda M3, y su tipo de unidad en la celda N3, con lo que se obtiene la conversión en L2.

En el rango M4:M28 se obtienen las unidades convertidas con el prefijo de la celda M3.

Las abreviaturas de cada conversión se listan en el rango N4:N28, separadas por guiones si hay más de una abreviatura, para la conversión de las unidades de medida.

AVISO: No todas las abreviaturas del rango N4:N28 consiguen convertir las unidades de medida, por lo que las fórmulas dependientes prueban todos los casos. Por ejemplo la distancia en pulgadas se consigue con la abreviatura inglesa "in", siendo que en la página de Microsoft con la función CONVERTIR se abrevia en español como "pda", lo que no sirve para convertir pulgadas...

En algunas de las hojas con tablas de unidades he editado la columna D con las abreviaturas que sí que funcionan al convertir... Puede que me haya dejado alguna sin añadir.

Por favor dime si encuentras algún error en las conversiones, para corregirlo cuanto antes...

Convierte unidades de medida en la nube

Prueba a convertir medidas sin necesidad de Excel y sin salir de esta página.

Cuando cambies el tipo de medidas deberás hacer clic en el primero de los 4 botones de abajo a la derecha para: Actualizar todas las conexiones de datos

Para ajustar el zoom del libro incrustado:

• En el móvil o celular usa dos dedos en la pantalla, como haces para ampliar o reducir una foto.
• En el PC sitúa el cursor dentro del buscador y presiona la tecla <Control> girando a la vez la ruleta del ratón.

Descarga el conversor de medidas

Descarga la versión 1.0 desde este enlace:

• Sites Google Drive: Convertir medidas - PW1.xlsx

Este archivo es compatible con Excel 2016 y versiones superiores, ya que usa:

Fórmulas de matriz dinámicas y comportamiento de matriz desbordada - Soporte técnico de Microsoft

Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.

Presiona el botón: Habilitar contenido cuando aparezca la ADVERTENCIA DE SEGURIDAD.

Las hojas están protegidas sin contraseña, por lo que puedes estudiar y analizar las fórmulas. Incluso puedes modificar este libro de Excel siempre que respetes esta licencia:

 

Creative Commons — Atribución-NoComercial-CompartirIgual 3.0 No portada — CC BY-NC-SA 3.0

En el archivo descargado también puedes analizar las consultas en Power Query, con las que he descargado las tablas de las unidades de medida desde esta página:

Función CONVERTIR - Soporte técnico de Microsoft

Videotutorial de cómo convertir medidas

En este videotutorial explico cómo convertir unas unidades de medida en otras.

Selecciona los subtítulos en tu idioma para entender el vídeo.

¿Qué otras conversiones entre unidades de medida añadirías?

Dímelo y lo incluiré en una próxima versión.

How to convert units of measurement

In Excel there is a very useful function, little known and less used:

CONVERT function - Microsoft Support

With the CONVERT function you can convert some units into other units of the same measure, which is very useful for physicists, chemists, mathematicians, engineers, science students or any Excel user who needs to convert units of measurement.

For example, to convert 1 statute mile to kilometers, the following formula is used:

=CONVERT(1,"mi","km")

Where the first argument is the numeric value to convert, a 1 in the example, the second argument is the abbreviation for miles "mi" and the third argument is the abbreviation for kilometers "km".

Since you have to know the abbreviations for the units of measurement to edit the arguments, I prefer to do it with an Excel workbook that I have prepared to automatically convert some units of measurement into others.

In this measurement converter there are 3 data segmentations:

1. Measurements: to select a measurement type: Area, Distance, Energy, Force, Information, Magnetism, Power, Pressure, Speed, Temperature, Time, Volume (or liquid measure), Weight and mass.
2. Prefix: to select the prefix of the measurement unit, for example: kilo, which is the multiple of a thousand. The equal sign (=) indicates that there is no prefix, so the unit is worth.
3. Units: to select the type of unit depending on the type of measurement.

A numerical value is entered in cell K2 and a table in the range K4:L28 is obtained with the conversion of all the units of the selected measure.

To obtain a certain unit of measurement, its prefix is entered in cell M3, and its type of unit in cell N3, with which the conversion is obtained in L2.

In the range M4:M28, the converted units are obtained with the cell prefix M3.

The abbreviations for each conversion are listed in the range N4:N28, separated by hyphens if there is more than one abbreviation, for conversion of units of measure.

In some of the sheets with tables of units I have edited column D with the abbreviations that do work when converting... I may have left some without adding.

Please tell me if you find any errors in the conversions, so I can correct them as soon as possible...

Convert units of measurement in the cloud

Try converting measurements without the need for Excel and without leaving this page.

When you change the type of measurement you must click on the first of the 4 buttons at the bottom right to: Update all data connections

To adjust the zoom of the embedded book:

• On the mobile or cell phone, use two fingers on the screen, as you do to enlarge or reduce a photo.
• On the PC, place the cursor inside the browser and press the <Control> key while turning the mouse wheel.

Download the measurement converter

Download version 1.0 from this link:

• Sites Google Drive: Convert measurements - PW1.xlsx

This file is compatible with Excel 2016 and higher versions, since it uses:

Dynamic array formulas and spilled array behavior - Microsoft Support

Open the file and press the button: Enable editing when the PROTECTED VIEW notice appears.

Press the button: Enable content when the SECURITY WARNING appears.

The sheets are protected without a password, so you can study and analyze the formulas. You can even modify this workbook as long as you respect this license:

 

Creative Commons — Attribution-NonCommercial-ShareAlike 3.0 Unported (CC BY-NC-SA 3.0)

In the downloaded file you can also analyze the queries in Power Query, with which I have downloaded the measurement unit tables from this web page:

CONVERT function - Microsoft Support

Video-tutorial on how to convert measurements

In this video-tutorial I explain how to convert units of measurement in Spanish.

Select the captions in your language to understand the video-tutorial.

What other conversions between units of measurement would you add?

Tell me and I'll include it in a future version.

Un par de gráficos con iconos

🔝To translate this blog post to your language, select it in the top left Google box.

¿Sabes hacer gráficos con iconos en Excel?

En la imagen verás dos tipos de gráficos con iconos:

🚗 Gráfico 1 en el que los iconos cambian de tamaño.
🚌 Gráfico 2 en el que los iconos no cambian de tamaño.

¿Cuál gráfico prefieres, el Gráfico 1 o el Gráfico 2?

¿Ya sabes hacer el Gráfico 1? ¿Quieres aprender a hacer el Gráfico 2?

¡Ahora mismo te lo explico! Pero antes puedes probar los 2 tipos de gráficos con iconos en la nube.

Gráficos con iconos en la nube

He subido a la nube de OneDrive los 2 gráficos para que los pruebes sin necesidad de Excel.

El Gráfico 1 es un gráfico de barras agrupadas y el Gráfico 2 es un gráfico de barras apiladas.

Para ajustar el zoom de los gráficos:

• En el móvil o celular usa dos dedos en la pantalla, como haces para ampliar o reducir una foto.
• En el PC sitúa el cursor dentro del buscador y presiona la tecla <Control> girando la ruleta del ratón.

Descarga el par de gráficos con iconos

Descarga la versión 1.0 desde este enlace:

• Sites Google Drive: Vehículos en Zaragoza PW1.xlsx

Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.

Las hojas no están protegidas, por lo que puedes estudiar y analizar todo. Incluso puedes modificarlo respetando la licencia:

 

Creative Commons — Atribución-NoComercial-CompartirIgual 3.0 No portada — CC BY-NC-SA 3.0

Origen de los datos de los gráficos

Origen de los datos: Ayuntamiento de Zaragoza desde su Portal de Transparencia:

Parque de vehículos matriculados. Indicadores SIU. Ayuntamiento de Zaragoza

Los datos se representan en los gráficos con un fin educativo, para enseñar cómo hacer gráficos con iconos en Excel.

En la hoja 'Zaragoza' he incluido en las columnas A:D los datos extraídos de la Web del Ayuntamiento.

He limpiado los datos originales, sustituyendo "Ciclomotorres" por "Ciclomotores".

La columna E con el Tipo se consigue con relleno rápido, como se explica en el primer vídeo de más abajo.

He insertado una tabla dinámica en las columnas G:H, con el total de vehículos por tipo y con el filtro por año. Tengo que recalcar que el mes es único para cada año, por lo que no entra en consideración.

El título de los gráficos se obtiene con la fórmula de la celda J2:

="Vehículos en el año " & $H$1 & " en Zaragoza"

Siendo $H$1 el año filtrado en la tabla dinámica.

Para filtrar el año uso una segmentación de datos en la tabla dinámica, copiada en las dos hojas con los gráficos.

También he insertado 4 iconos con los tipos de vehículos que figurarán en los gráficos, con colores distintos para cada vehículo. 

Cómo crear el Gráfico 1 con iconos

En la hoja 'Vehículos 1' he creado el Gráfico 1, con iconos que cambian de tamaño dependiendo de la cantidad de vehículos.

La tabla de las columnas A:B contiene el tipo de Vehículos y su Cantidad.

La fórmula de la columna Cantidad importa los datos de la tabla dinámica:

=IMPORTARDATOSDINAMICOS("Vehículos";Zaragoza!$G$3;"Tipo";[@Vehículos])

El año se determina seleccionándolo en la segmentación de datos de las dos primeras filas en el rango E:H

Sigue estos pasos para crear el Gráfico 1:

1) Selecciona una celda cualquiera de la tabla.

2) Selecciona en la cinta de opciones: Insertar gráfico de barras agrupadas y presiona el botón: Aceptar

3) Haz clic con el botón derecho del ratón en las series para mostrar el menú contextual.

4) Selecciona: Agregar etiqueta de datos

5) Elimina el eje horizontal y las líneas de división verticales.

6) Selecciona el título del gráfico y en la barra de fórmulas escribe un signo igual y selecciona la celda: =Zaragoza!$J$2

Observa que las series están en orden inverso respecto a la tabla.

7) Selecciona las categorías del eje vertical.

8) Marca: Categorías en orden inverso

9) Selecciona las series y en Opciones de serie indicar:

• Superposición de series: 100%
• Ancho del rango: 0%

10) Copia cada icono de la hoja 'Zaragoza' en una de las series, obteniendo el Gráfico 1 con iconos de distinto tamaño.

El icono del autobús ¡no se ve en el gráfico!, pues su valor es muy pequeño comparado con los demás vehículos.

¿Cómo crees que he conseguido insertar el icono del autobús en el gráfico?

Para aprender el truco para insertar cada icono en el gráfico tendrás que ver el siguiente videotutorial.

Videotutorial para crear el Gráfico 1

En este videotutorial explico cómo crear el Gráfico 1, en el que los iconos cambian de tamaño.

Selecciona los subtítulos en tu idioma para entender el vídeo.

Cómo crear el Gráfico 2 con iconos

En la hoja 'Vehículos 2' he creado el Gráfico 2, con iconos que no cambian de tamaño aunque varíe la cantidad de vehículos.

La tabla de las columnas A:C contiene el tipo de Vehículos, el tamaño de los Iconos y su Cantidad.

La fórmula de la columna C con la Cantidad importa los datos de la tabla dinámica:

=IMPORTARDATOSDINAMICOS("Vehículos";Zaragoza!$G$3;"Tipo";[@Vehículos])

Para conseguir que los iconos tengan el mismo tamaño, escribe la fórmula de la columna B con el valor máximo de las cantidades dividido por 4 (prueba con otros valores):

=MAX([Cantidad])/4

El año se determina seleccionándolo en la segmentación de datos de las dos primeras filas en el rango E:H

Sigue estos pasos para crear el Gráfico 2:

1) Selecciona una celda cualquiera de la tabla.

2) Selecciona en la cinta de opciones: Insertar gráfico de barras apiladas y presiona el botón: Aceptar

3) Haz clic con el botón derecho del ratón en la serie Cantidad para mostrar el menú contextual.

4) Selecciona: Agregar etiqueta de datos

5) Elimina el eje horizontal, las líneas de división verticales y la leyenda.

6) Selecciona el título del gráfico y en la barra de fórmulas escribe un signo igual y selecciona la celda: =Zaragoza!$J$2

Observa que las series están en orden inverso respecto a la tabla.

7) Selecciona las categorías del eje vertical.

8) Marca: Categorías en orden inverso

9) Selecciona las series y en Opciones de serie indicar:

• Superposición de series: 100%
• Ancho del rango: 50%

10) Copia cada icono de la hoja 'Zaragoza' en una de las series de Iconos, obteniendo el Gráfico 2 con iconos del mismo tamaño.

11) Cambia los colores en las barras apiladas de cada una de las series Cantidad, con el mismo color que los iconos.

12) Elimina las categorías del eje vertical.

13) Selecciona las etiquetas de datos con el siguiente formato:

• Marca en Nombre de categoría
• Separador: (espacio)
• Posición de etiqueta: Base interior

14) Alinea las etiquetas sin marcar nada.

La barra del autobús ¡no se ve en el gráfico!, pues su valor es muy pequeño comparado con los demás vehículos.

¿Cómo crees que he conseguido cambiar el color de la barra horizontal de los autobuses en el gráfico?

Para aprender el truco para insertar cada color de las barras en el gráfico tendrás que ver el segundo videotutorial.

Videotutorial para crear el Gráfico 2

En el segundo videotutorial explico cómo crear el Gráfico 2, en el que los iconos mantienen su tamaño.

Selecciona los subtítulos en tu idioma para entender el vídeo.

¡Eso es todo, amigos!

My new Excel calculator

🔝To translate this blog post to your language, select it in the top left Google box.

NOTICE: This calculator is designed for educational purposes and personal use. It is not a professional calculator and is provided “AS IS”, without guarantees or responsibilities on the part of the author of this blog. This means that the user assumes all risks associated with the use of this calculator.

My new calculator

My new Excel calculator is based on one I made 12 years ago.

In these two articles I explain the most relevant characteristics of the old calculator, that will help to understand how the new calculator is made:

Calculating minds | #ExcelPedroWave

How to make Excel calculators | #ExcelPedroWave

This last article is in the TOP 5, since it has received more than 31,000 visits, being the fourth of the most visited articles on my blog.

This is the image of my new calculator made in Excel. If you want one, download it below.

Calculator features

The main features of this new calculator made in Excel are:

A) The pressing of the keys by 3 methods:

1) Using the virtual keyboard, being unchecked the Tooltips, a macro is called that detects the key being pressed. It is the elementary method, since all the keys call the same macro.

2) Using the virtual keyboard, being checked the Tooltips, creating the keyboard adds a different hyperlink for each key, using the method: Hyperlinks.Add. It is a more sophisticated method, since each key has a cell associated with it, that is selected with the hyperlink, and the selected cell change event launches the macro that detects the key pressed.

3) Using the physical keyboard, by pressing keys shown in parentheses when you hover over a key, bringing up the information in a Tooltip. For example, with the key 10^x it appears: Ten raised to x (SHIFT+X), so the physical key is the uppercase X. It is the most elaborate method, since a macro has been created for each key, which is called with the method: Application.OnKey

B) The dynamic creation of the calculator keys in 3 cases:

1) When changing the type of calculator, the actual keys are deleted and the new keys of the selected calculator are created. Initially there are 8 calculators in addition to the number 0 that serves as a presentation and help.

2) By checking or unchecking Tooltips, because it is necessary to create or eliminate the hyperlinks of each key.

3) When switching languages, if are checked the Tooltips, because the hyperlinks of each key must be created in the selected language.

C) Customizing calculator keys and recording a new one in 5 steps:

1) Select one of the existing calculators.

2) Disable the physical keyboard to be able to clear the keys with the key: Delete

3) Select and delete the keys that will not appear in the new calculator.

4) Change the size and position of customized keys.

5) Save the new calculator by typing a descriptive name.

D) The 8 features that make this new version of my calculator unique:

1) It is made in Excel with VBA macros, being compatible with desktop versions, from Excel 2010 to Excel for Microsoft 365.

2) Calculation results are viewed in a seven-segment display, being able to easily choose its color, by clicking on one of the colors in row 1.

3) You can see how each key is dynamically created when you change the calculator type, by checking: Floating.

4) The calculator is translated into 6 languages, being able to add more languages.

5) It is a touch calculator, if the screen is touch screen, being able to hide key groups by clicking on the below keys.

6) With 4 new keys to change the Zoom, making the calculator bigger or smaller.

7) With a new date and time key, with the year represented by: yyyy, in 3 different date formats:

• With the year ahead según ISO 8601: yyyy.mm.dd hh:mm:ss
• With the month ahead: mm.dd.yyyy hh:mm:ss
• With the day ahead: dd.mm.yyyy hh:mm:ss

8) It is a scientific calculator in Floating-point arithmetic. It is the main innovation of this new version, since the old calculator only operated in Fixed-point arithmetic, being able to operate the new calculator with very small or very large numbers, like the one that appears in this image:

For numbers in scientific notation the notation E is used, with the same limits of the VBA language for the double data type:

• -1.79769313486231E308 to -4.94065645841247E-324 for negative values
• 4.94065645841247E-324 to 1.79769313486232E308 for positive values

If during any calculation these limits are exceeded, an overflow appears represented as: -E-

If the exponent is positive, it is represented only with the letter E, for example: 4.E3 is equivalent to 4x10^3

If the exponent is negative, it is represented by the letter E and the minus sign, for example: -2.E-3, equals -2x10^-3

Calculator video-tutorial

In this video-tutorial I explain how to use this new calculator.

Select the captions in your language to understand the video-tutorial.

Download the calculator

Download version 5.0 from this link:

• Calculadora Táctil PW5.xlsm

The downloaded file's macros are blocked by default. To unlock the macros you must modify the Properties of the file following these instructions:

• Macros from the internet will be blocked by default in Office - Deploy Office | Microsoft Learn

Open the file and press the button: Enable editing when the PROTECTED VIEW notice appears.

Press the button: Enable content when the WARNING appears SECURITY Macros have been disabled

The sheets are protected without a password and the VBA project is not protected, so you can study and analyze all the code. You can even modify it respecting the license:

Creative Commons — Attribution-NonCommercial-ShareAlike 3.0 Unported (CC BY-NC-SA 3.0)

Feedback of the new calculator

I accept criticism and suggestions if you use and try this new calculator.

To improve this calculator I need to know its bugs, its deficiencies and their possible improvements and, if you give me feedback, I will thank you and I will commit to improve the calculator in future versions during the next few years, I don't expect so many to pass...

And so I finish the series of 4 articles on keyboards and their keys, which I have been publishing for more than a month, and which you can find at this link:

keyboard | #ExcelPedroWave