Tablero Kanban personalizable en Excel

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

Esta es la tercera versión que publico de un tablero Kanban. Es la versión más completa, configurable y personalizable, y está programada en 6 idiomas fácilmente escalables. Es una versión didáctica, con fines educativos, por lo que no contiene toda la funcionalidad de las versiones comerciales.

Puedes ver todas las versiones de los tableros Kanban que he publicado aquí:

• Kanban en Excel | #ExcelPedroWave

Novedades del Tablero Kanban

El nuevo Tablero Kanban personalizable incorpora estas novedades:

1) Seleccionar de 1 a 8 líneas por tarjeta.

2) Configurar el orden de las líneas en las tarjetas.

3) Personalizar los Estados y la Criticidad.

4) Ver la duración y el retraso de cada tarjeta.

5) Filtrar tarjetas por duración y retraso en días. 

6) Seleccionar uno de los 6 idiomas del tablero, pudiendo añadir fácilmente más idiomas: Español; Inglés; Francés; Italiano; Alemán; Portugués.

El objetivo fundamental sigue siendo poder compartir el Tablero Kanban con el equipo de trabajo en la nube de Microsoft OneDrive, por lo que únicamente contiene fórmulas de Excel.

ATENCIÓN: Regístrate gratuitamente en este enlace para conseguir 5 GB de almacenamiento en la nube OneDrive y versiones gratuitas de Word, PowerPoint y Excel para la Web, donde podrás probar y compartir este Tablero Kanban personalizable.

Estas nuevas mejoras me las han sugerido mis lectores, a los que agradezco que compartan conmigo sus ideas, para ayudarme a diseñar un Tablero Kanban que se adapte a sus necesidades.

En la siguiente imagen animada muestro el Tablero Kanban con 8, 4 y 2 líneas por tarjeta:

Descarga el tablero Kanban personalizable

Este tablero Kanban es compatible con versiones desde Excel 2010 hasta Excel para Microsoft 365 y Excel para la Web.

Descarga la versión 3 desde este enlace:

• Sites Google Drive: Tablero Kanban - PW3.xlsx

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

Las hojas y el libro están protegidos sin contraseña, por lo que puedes estudiar y analizar las fórmulas, y ver las hojas ocultas.

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

Primer uso del tablero Kanban personalizable

Estos son los pasos a seguir para usar por primera vez este tablero Kanban:

1) Borrar el rango Datos!A2:G101 y Datos!J2:J101 para borrar los datos de todas mis tareas. Para ello seleccionar esos rangos y presionar la tecla: Supr

2) Editar los nombres de los estados en el rango Configura!B9:B18

3) Elegir el estado final en la celda Configura!B20. Si se deja en blanco el último estado será el estado final.

4) Editar los nombres de las criticidades en el rango Configura!D9:D11

5) Editar los miembros del equipo a quienes se asignarán las tareas en el rango Configura!F9:F23

6) Escribir el nombre del archivo en la celda Configura!B23, solamente si cambia debido, por ejemplo, a crear una copia del archivo.

7) Modificar a tu gusto el orden de las líneas de cada tarjeta en el rango Configura!I10:I16

8) Crear la primera tarea en la hoja 'Datos'

9) Usar el tablero Kanban...

Tablero Kanban en la nube

La última versión del tablero Kanban la he compartido en la nube de Microsoft OneDrive, para que sea fácil de probar aunque no tengas Excel instalado en el equipo.

Cada vez que se accede a este Kanban se ven los estados de mis propias tareas.

Para ajustar el zoom en la nube:

• 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.

AVISO: No se guardan los cambios que hagas en mi nube. Para guardar tus cambios tienes que hacer una copia en tu nube de OneDrive.

Vídeo: Tablero Kanban personalizable

En el vídeo explico las mejoras añadidas a la versión 3 del tablero Kanban en Excel, para que sea personalizable.

Hojas del Tablero Kanban personalizable

El Tablero Kanban consta de 6 hojas, que es mejor verlas en una versión de escritorio de Excel.

Hay 3 hojas visibles: KANBAN; Datos; Configura, y 3 hojas ocultas: Tareas; Formulas; Idiomas. Sigue estos pasos para mostrar las hojas ocultas:

1) Seleccionar en la cinta de opciones la protección del libro con: Revisar > Proteger libro

2) Haz clic con el botón derecho en cualquier pestaña visible en la parte inferior del libro de Excel.

3) En el menú desplegable, selecciona: Mostrar... Aparecerá un cuadro de diálogo con las 3 hojas ocultas.

4) Presiona la tecla Control y haz clic en cada hoja, con lo que se mostrarán en color azul las 3 hojas ocultas: Tareas; Formulas; Idiomas.

5) Haz clic en el botón: Aceptar, y se verán toda las hojas.

Hoja 'Idiomas'

En la hoja 'Idiomas' están las traducciones a los 6 idiomas que incorpora por defecto este tablero Kanban.

La tabla con los 6 idiomas se extiende desde la columna B hasta la columna G, y es fácilmente ampliable, con una columna por cada idioma.

La lista con los nombres de los idiomas se obtiene con el nombre definido: misIdiomas, con esta fórmula:

=Idiomas!$B$1:INDICE(TablaIdiomas[#Encabezados];1;CONTARA(TablaIdiomas[#Encabezados]))

La celda Configura!C5, denominada: miIdioma, es una validación de datos con una lista desplegable con los nombres de los idiomas obtenidos con Origen: =misIdiomas

En la celda A1 se obtiene el número del idioma, con la fórmula:

=SI.ERROR(COINCIDIR(miIdioma;misIdiomas;0);1)

La celda A2 se arrastra hacia abajo, hasta el número de filas de la tabla de idiomas, con la fórmula:

=INDICE(TablaIdiomas[@];1;$A$1)&""

Con lo que todas las traducciones están en la columna A de la hoja 'Idiomas'.

Por ejemplo, para traducir "Tablero Kanban" a cualquier idioma, se elige ese idioma y la traducción está en la celda Idiomas!A2

Hoja 'Configura'

En la hoja 'Configura' están las configuraciones de este tablero Kanban.

ATENCIÓN: Se debe escribir el nombre del archivo en la celda B23, que por defecto es:

Tablero Kanban - PW3.xlsx

Así será detectado por Excel para la Web y abrirá los hipervínculos del Tablero Kanban. Hay que tenerlo en cuenta si se copia el archivo, pues automáticamente cambia su nombre.

Antes de introducir datos en la hoja 'Datos', se debe elegir el Idioma en el desplegable con los idiomas de la celda B5.

Los nombres de los Estados se escriben en el rango B9:B18 y el estado final se selecciona en la celda B20.

Los nombres de las 3 Criticidades se escriben en el rango D9:D11 por orden de urgencia.

Los Miembros del equipo se escriben en el rango F9:F23

Los datos de las tareas son las líneas de las tarjetas y, en el rango I10:I16, se define el Orden en tarjetas: del 2 al 8, sin duplicar los números de orden. El 1 con el nombre de la tarea es fijo y es la primera línea de la tarjeta que siempre se ve en cada una de las tarjetas.

Hoja 'Datos'

Los datos del tablero Kanban están en la hoja 'Datos'.

En esta hoja se introduce toda la información que aparecerá en el tablero Kanban:

1) Estados: Con un desplegable tal y como se han escrito en la hoja 'Config'.

2) Criticidad: Con un desplegable de las 3 criticidades: URGENTE; IMPORTANTE; NORMAL.

3) Tarea: Escribir los nombres de cada tarea.

4) Descripción: Escribir una breve descripción de cada tarea.

5) Inicio: Introducir la fecha de inicio de cada tarea.

6) Previsto: Introducir la fecha prevista de finalización de cada tarea.

7) Final: Introducir la fecha final de cada tarea.

8) Duración: Con una fórmula para calcular la duración en días de cada tarea.

9) Retraso: Con una fórmula para calcular el retraso en día de cada tarea.

10) Asignado: Con un desplegable se asigna a un miembro del equipo a cada tarea.

11) Kanban: Con un vínculo para ir a la tarjeta asociada a una tarea del tablero Kanban.

AVISO: No conviene cambiar de idioma después de introducir estos datos en cada tarea, pues algunos campos no se traducen.

Hoja 'Tareas'

La hoja 'Tareas' contiene las fórmulas que permiten filtrar las tareas, averiguar el número de estado y de criticidad, y poder saltar con un vínculo desde las tareas de la hoja 'Datos' a las tarjetas de la hoja 'Kanban'.

Esta hoja contiene los siguientes campos:

1) Tarea: Con el número automático de la tarea.

2) #Estados: Con el número del estado de cada tarea, para situarla en la columna correspondiente.

3) #Criticidad: Con el número de criticidad de cada tarea, para cambiar el color de su tarjeta con un formato condicional.

4) Filtro: Con la fórmula que marca un 1 si la tarea se muestra en el tablero y un 0 si la tarea es filtrada:

=SI(Y(Datos!$A2<>""; O(miCriticidad="";miCriticidad=$C2); O(miAsignado="";miAsignado=Datos!$J2); O(miFechaInicio="";Datos!$E2="";Datos!$E2>=miFechaInicio); O(miTexto="";SI.ERROR(HALLAR(miTexto;Datos!$C2);0)>0;SI.ERROR(HALLAR(miTexto;Datos!$D2);0)>0); O(miRetraso="";SI.ERROR(--IZQUIERDA(Datos!$I2;ENCONTRAR(" ";Datos!$I2)-1);0)>miRetraso); O(miDuración="";SI.ERROR(--IZQUIERDA(Datos!$H2;ENCONTRAR(" ";Datos!$H2)-1);0)>miDuración)); 1;0)

5) C - H - M - R - W - AB - AG - AL - AQ - AV: Son 10 columnas de la hoja 'Kanban' donde está el lado izquierdo de cada tarjeta.

6) Celda: Concatena los valores de las columnas anteriores, donde cada tarea está en una celda distinta.

7) Kanban: Rango de celdas del lado izquierdo de cada tarjeta. Quedará en blanco para las tarjetas que no aparezcan en el tablero.

Hoja 'Formulas'

En la hoja 'Formulas' están las fórmulas, valga la redundancia, que permiten "dibujar" las tarjetas en el tablero Kanban.

Con cada uno de los 10 estados o columnas del tablero se calculan las fórmulas para obtener los datos de cada tarjeta y su posición en el tablero Kanban.

Por ejemplo, en el estado "POR HACER", en la celda B2 está la fórmula matricial:

=SI((misEstados=B$1)*(misFiltros=1);FILA(misEstados)-1;"")

Con la que se obtiene los números de tarea en ese estado, una vez filtrados.

En la columna C se obtienen los números de tarjetas consecutivas en ese estado.

En la columna D se obtienen los números de tareas repetidos tantas veces como líneas se muestren en cada tarjeta.

En la columna E se obtienen los números de líneas de cada tarjeta.

En la columna F se obtiene la criticidad de cada tarea o tarjeta, un signo separador y el número de linea.

Estas fórmulas se repiten en las demás columnas para cada uno de los 10 estados posibles.

Hoja 'KANBAN'

El Tablero Kanban está en la hoja 'KANBAN'.

En el tablero se visualizan las tarjetas en cada estado y con un número de líneas elegido en la celda F2, de 1 a 8 líneas por tarjeta.

Los siguientes filtros son excluyentes:

1) Tareas asignadas a: En la celda J2 se selecciona con un desplegable a que miembro del equipo están asignadas las tareas, filtrando las tarjetas asignadas a ese miembro del equipo. Si con la tecla Supr se deja en blanco, no se aplica el filtro.

2) Con criticidad: Desplegable en la celda O2 para filtrar por una criticidad: URGENTE; IMPORTANTE; NORMAL, o todas si se deja en blanco con la tecla Supr.

3) Con inicio desde: Filtra las tarjetas a partir de una fecha de inicio.

4) Con el texto: Filtra cualquier dato dentro de las tareas, excepto si se deja en blanco con la tecla Supr.

5) Con un retraso >: Filtra las tareas con un retraso mayor que un número de días.

6) Con una duración >: Filtra las tareas con una duración mayor que un número de días.

NOTA: No hay que olvidar que si solamente se quiere filtrar por una condición, es necesario dejar en blanco el resto de condiciones...

Las fórmulas que hacen posible el tablero Kanban son las siguientes para el primer estado, y están repetidas para los demás estados:

La fórmula en la celda C6, arrastrada hacia abajo en el rango C6:C201, es:

=SI(O(Formulas!E3=0;Formulas!E3="";DERECHA(Formulas!F3)="0");""; HIPERVINCULO("["&Archivo_Nombre&"]Datos!"&IZQUIERDA(SUSTITUIR(DIRECCION(1;SI(Formulas!E3=1;1;DERECHA(Formulas!F3)+2));"$";""))&Formulas!D3+1; SI.ERROR(INDICE(Datos!$A$1:$K$1;1;DERECHA(Formulas!F3)+2)&SI(Formulas!E3=1;" "&Formulas!D3;"")&":";"")))

La fórmula en la celda D6, arrastrada hacia abajo en el rango D6:D201, es:

=SI(O(Formulas!E3=0;Formulas!E3="";DERECHA(Formulas!F3)="0");""; HIPERVINCULO("["&Archivo_Nombre&"]Datos!"&IZQUIERDA(SUSTITUIR(DIRECCION(1;DERECHA(Formulas!F3)+2);"$";""))&Formulas!D3+1; SI.ERROR(INDICE(Datos!$A$2:$K$101;Formulas!D3;DERECHA(Formulas!F3)+2);"")))

Estas fórmulas contienen los hipervínculos con los que ir a cualquier campo de la hoja 'Datos' para poder editarlo...

¡Es el truco principal de este Tablero Kanban!

Conclusiones

La gracia de este Tablero Kanban es poder compartirlo en la nube de Microsoft OneDrive, para lo que está hecho únicamente con fórmulas y no contiene macros VBA, pues no funcionan en Excel para la Web.

El truco principal es usar masivamente los hipervínculos, pues es imposible editar las tarjetas en el Tablero Kanban, por lo que se vincula cada línea de una tarjeta con la tarea asociada en la hoja 'Datos', en la que se edita cualquier campo de las tareas.

Excel para la Web ha cambiado drásticamente la forma de abrir un vínculo desde hace unas semanas, lo que ha generado muchas quejas entre los usuarios de Excel Online pues, si se selecciona una celda, ahora obliga a usar la tecla Control (Ctrl) y hacer clic en el enlace. Ver las quejas en estos enlaces:

• Desactivar Ctrl+clic Excel Online | El foro Excel de TodoExcel.com

• I cannot remove the 'ctrl and click option for hyperlink - I JUST want - Microsoft Community

En Excel de escritorio no hace falta presionar la tecla Control para abrir un vínculo, solamente con hacer clic en él, como ocurre en cualquier página Web.

En esta imagen animada muestro un workaround con el que no hace falta presionar la tecla Control para abrir un vínculo en Excel para la Web:

1) Pasar el ratón por encima del enlace.

2) En la ventana que se abre, hacer clic en el enlace.

Habrá que acostumbrarse a abrir los vínculos en Excel para la Web de esta manera, para no tener que usar la tecla Control, mientras Microsoft hace caso a los usuarios y actualiza Excel para la Web, para que los enlaces se abran con un solo clic, como siempre ha sido, o hasta que exista una opción para no usar la tecla Control, como ocurre en las opciones de las versiones de escritorio de Word y Outlook.

Escribe un comentario si tienes dudas o si se te ocurren ideas de mejora de este Tablero Kanban.

Nuevo tablero KANBAN mejorado

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

ATENCIÓN: Descarga la mejor versión de mi Tablero Kanban aquí:

• Tablero Kanban personalizable en Excel | #ExcelPedroWave

Hace un año publiqué un tablero KANBAN que está recibiendo muchas visitas últimamente, tanto en el canal de YouTube como en el blog:

• Tablero Kanban en Excel | #ExcelPedroWave

Si no conoces mi primera versión de un KANBAN, será mejor que leas el artículo del enlace anterior para familiarizarte con este tablero, pues no me gusta repetir las mismas explicaciones una y otra vez.

KANBAN mejorado

Esta imagen animada es del nuevo tablero KANBAN mejorado, en el que se aprecian los nuevos filtros de tareas:

• Tareas asignadas a un miembro del equipo, o a todos si se deja en blanco la celda J2.
• Tareas con una criticidad seleccionada, o todas si se deja en blanco la celda O2.
• Tareas con inicio de la tarea desde una fecha selecciona, o todas si se deja en blanco la celda T2.
• PLUS: Tareas con un texto hallado en el nombre o en la descripción de la tarea, o todas si se deja en blanco la celda Y2. Este filtro lo he añadido a última hora, por lo que no aparece en las imágenes ni en las explicaciones del videotutorial.

Tablero KANBAN en la nube

La última versión del tablero KANBAN la he compartido en la nube de Microsoft OneDrive, para que sea fácil de probar aunque no se tenga Excel instalado en el equipo.

Cada vez que se accede a este KANBAN se ven los estados actualizados de mis propias tareas.

Para ajustar el zoom en la nube:

• 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.

AVISO: No se guardan los cambios que hagas en mi nube, tienes que hacer una copia en tu nube de OneDrive.

Vídeo: Tablero KANBAN mejorado

En el vídeo explico las mejoras añadidas a esta segunda versión del tablero KANBAN en Excel.

Descarga el tablero KANBAN mejorado

Este tablero KANBAN es compatible con versiones desde Excel 2010 hasta Excel para Microsoft 365 y Excel para la Web.

Descarga la versión 2.0 desde este enlace:

• Sites Google Drive: Tablero Kanban - PW2.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, por lo que puedes estudiar y analizar las fórmulas.

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

Primer uso del tablero KANBAN

Estos son los pasos a seguir para usar por primera vez el tablero KANBAN:

1) Borrar el rango Datos!A2:H101 para borrar los datos de todas mis tareas. Para ello seleccionar ese rango y presionar la tecla: Supr

2) Editar los nombres de los estados en el rango Configura!B7:B17

3) Elegir el estado final en la celda Configura!B19. Si se deja en blanco el último estado será el estado final.

4) Editar los nombres de las criticidades en el rango Configura!D8:D10

5) Editar los miembros del equipo a quienes se asignarán las tareas en el rango Configura!F8:F22

6) Si cambias el nombre del archivo tendrás que escribirlo en la celda Configura!B22

7) Crear la primera tarea en la hoja 'Datos'

8) Usar el tablero KANBAN...

Mejoras del tablero KANBAN

Con este tablero KANBAN hago el seguimiento de mis tareas desde hace más de un año, y ahora le he dado un repaso en esta nueva versión con los comentarios de quienes llevan probando y usando la primera versión desde hace un año, y lo conocen mejor que yo.

La nueva versión del tablero KANBAN contiene varias mejoras:

1️⃣ Hasta 100 tarjetas, una por cada tarea.

2️⃣ Hasta 10 estados o pasos de las tarjetas con posibilidad de renombrarlos.

3️⃣ Hasta 28 tarjetas en cada estado.

4️⃣ Hasta 3 criticidades con posibilidad de renombrarlas.

5️⃣ Hasta 15 miembros del equipo para asignarles tareas.

6️⃣ Filtrar tareas por miembros asignados del equipo.

7️⃣ Filtrar tareas por criticidad.

8️⃣ Filtrar tareas por fecha de inicio.

9️⃣ Filtrar tareas por texto en el nombre o la descripción de la tarea.

También contiene algunas mejoras de aspecto y con un poco de conocimiento de Excel se pueden ampliar los límites indicados.

Ya es hora de que dejes de usar notas adhesivas y de pasarte a este tablero virtual KANBAN, hecho totalmente con fórmulas de Excel, por lo que se puede compartir con el equipo de trabajo en la nube de Microsoft OneDrive.

El detalle de las mejoras es, sin orden de importancia:

• Crear una nueva hoja 'Formulas' con las fórmulas con las que se obtiene la lista de tareas aparecen en el tablero. En la primera versión estas fórmulas estaban en filas y columnas ocultas del propio tablero. En la segunda versión es mucho mas fácil añadir mas filas para nuevas tareas y mas columnas para añadir mas estados. Por defecto caben hasta 10 estados y 28 tareas en cada estado.

• Modificar la hoja 'Datos' para mostrar los errores de validación al introducir: Estado; Criticidad y fechas.

• Incluir formatos condicionales para resaltar con colores la criticidad de cada tarea en la hoja 'Datos'.

• Crear condiciones de filtrado ocultas en la hoja 'Datos'. Las tareas que en la columna I estén en rojo no se mostrarán en el tablero KANBAN pues están filtradas. El hipervínculo de las tareas ocultas por las condiciones del filtro lleva a la celda KANBAN!B2 por defecto.

• Poder ampliar en la hoja 'Datos' las 100 filas, una por cada tarea.

• Configurar hasta 10 estados en la hoja 'Configura', pudiendo modificar sus nombres antes de crear alguna tarea.

• Elegir el estado final en la celda B19 para que avise si no se ha introducido una fecha final en el estado final.

• Cambiar los nombres de las 3 criticidades en la hoja 'Configura', antes de crear alguna tarea.

• Añadir miembros al equipo asignado a las tareas en la hoja 'Configura'.

• Poder cambiar el nombre del archivo en Excel para la Web, para que sigan funcionando los hipervínculos en la nube.

• Filtrar las tareas de un miembro del equipo en la hoja con el tablero KANBAN.

• Filtrar las tareas por criticidad en la hoja 'KANBAN'.

• Filtrar las tareas por la fecha de inicio en la hoja 'KANBAN'.

• Filtrar las tareas con un texto hallado en el nombre o la descripción de la tarea.

ATENCIÓN: Este último filtro de texto lo he añadido a última hora, por lo que no está explicado en el vídeo.

En la siguiente imagen están algunos filtros aplicados como ejemplo:

En esta imagen filtro por las tareas con el texto: kanban

Ahora te toca a ti decirme qué le falta a este tablero KANBAN o si te gusta como ha quedado...

Mi ciclo de diseño Excel

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

Mi ciclo de diseño en forma de imagen animada:

Si tienes interés en saber cómo diseño mis artículos en este blog #ExcelPedroWave, y mis vídeos en el canal de YouTube @PedroWave, esta es la ocasión de averiguarlo.

Mi ciclo de diseño pasa por las siguientes etapas y herramientas:

• 💡 Idea: Busco datos que me permitan escribir nuevos artículos sobre Excel.

• 📊 Excel: Diseño prototipos con datos originales o extraídos de la Web.

• ⛅ OneDrive: Guardo plantillas Excel subiéndolas a la nube de Microsoft y de Google.

• 🪄 PowerPoint: Creo diapositivas, capturando la pantalla y mi voz, y las guardo en vídeo.

• 🎬 Clipchamp: Creo videotutoriales, editando y montando el vídeo anterior.

• 🎞️ YouTube: Subo a mi canal los videotutoriales montados y edito los subtítulos.

• ✍️ Blogger: Creo entradas en el blog, con vídeos y con la descarga de las plantillas Excel.

• 🕸️ LinkedIn: Promociono mi blog y mi canal de YouTube en las redes sociales.

A continuación daré más detalles de cada uno de los pasos de mi ciclo de diseño.

💡 Idea

Busco datos que me permitan escribir nuevos artículos sobre Excel.

Cualquier medio es bueno para tener nuevas ideas de diseño.

Puede ser un comentario en mi blog o en mi canal de YouTube o en las redes sociales en las que promociono mis artículos, como LinkedIn la red profesional por excelencia, Facebook con excelentes grupos de Excel, Instagram con seguidores fieles, Twitter con píldoras de Excel.

Las ideas pueden provenir también de los foros de Excel, con consultas y comentarios de usuarios del foro.

Leyendo otros blogs, páginas webs y canales de Excel pueden surgir nuevas ideas de mejora para escribir artículos.

Las noticias de actualidad en la prensa escrita son otro medio para generar ideas de desarrollo en Excel.

Ya ves que los orígenes de datos son de lo más diverso, lo que me ha permitido escribir 262 artículos en 13 años. 

Muchas ideas se convierten en un diseño en Excel en uno o en pocos días, pero otras ideas necesitan varios meses de desarrollo, como varias de las plantillas dedicadas al ajedrez. 

En septiembre actualizaré este tablero Kanban con las ideas POR HACER en los próximos meses:

Tablero Kanban en Excel | #ExcelPedroWave 

Si quieres que tus ideas sean una realidad en Excel, ya estás tardando en contármelas en un comentario y, si me parecen interesantes, las añadiré a mis tareas POR HACER.

📊 Excel

Diseño prototipos con datos originales o extraídos de la Web.

Este es el momento más gratificante de mi actividad cerebral, en la que estimulo mi perfil calculador mientras diseño hojas de cálculo en Excel.

Puedo crear un libro de Excel con fórmulas matriciales, con matrices dinámicas, con tablas dinámicas, con segmentación de datos, con gráficos curiosos, con mapas dinámicos, con cálculos iterativos. Todo vale para crear excelentes hojas de cálculo.

Uso Excel de escritorio en versiones Excel 2010 y Excel para Microsoft 365, con o sin macros en lenguaje VBA, y Excel para la Web, con las últimas funciones añadidas a Excel.

Con Power Query creo herramientas ETL para extraer, transformar y cargar datos en Excel.

Con Power Pivot creo modelos de datos, consultas dinámicas y analizo grandes volúmenes de datos.

Puedes buscar mis artículos y descargar mis archivos en Excel desde aquí:

Buscador de mis artículos de Excel | #ExcelPedroWave

⛅ OneDrive

Guardo plantillas Excel subiéndolas a la nube de Microsoft OneDrive y de Google Drive.

Todos mis prototipos y plantillas Excel los subo a estas dos nubes:

Microsoft OneDrive - Público #ExcelPedroWave

Google Drive - Descargas Blog Pedro Wave

🪄 PowerPoint

Con Microsoft PowerPoint creo diapositivas, capturando la pantalla y mi voz, y las guardo en vídeo.

Como transición entre diapositivas elijo Ondulación, como cuando una piedra cae en el agua.

En la pestaña Grabar selecciono: Grabación de pantalla...

Con lo que se graba el puntero del ratón, el audio de mi propia voz y el sonido de Windows, el área de la pantalla completa con una resolución de 1920 x 1080 pixeles en Full HD.

Estas son las diapositivas de mi ciclo de diseño Excel:

Una vez editada las diapositivas, las exporto a un vídeo que guardo en mi equipo.

También puedo crear GIF animados con fondo transparente, como el que aparece al principio de este artículo.

🎬 Clipchamp

Creo videotutoriales, editando y montando el vídeo anterior.

Microsoft Clipchamp es un editor de vídeos, incluido en las suscripciones de Microsoft 365, que permite exportaciones ilimitadas sin marca de agua, con resoluciones de hasta 1080p en formato HD, acción y filtros Premium.

Editor de video Clipchamp | Microsoft 365

🎞️ YouTube

Subo a mi canal los videotutoriales montados y edito los subtítulos en español, que se pueden traducir automáticamente a cien idiomas. 

Este es mi canal de YouTube, con 117 vídeos y 924 seguidores:

Excel Pedro Wave - YouTube

✍️ Blogger

Creo entradas en el blog, en las que incluyo mis vídeos y enlaces a las descargas de mis archivos hechos con Excel con:

Blogger.com - Crea un blog atractivo y original fácilmente.

En mi blog publico mis propios retos en Excel, 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.

Enlace a mi blog con 262 entradas y 1.122.000 visitas:

#ExcelPedroWave

Suelo incluir enlaces a páginas de Microsoft y de Wikipedia, e incrusto hojas de cálculo Excel desde Microsoft OneDrive:

Compártalo: Insertar un libro de Excel en su página web o blog de OneDrive - Soporte técnico de Microsoft

🕸️ LinkedIn

En la red profesional LinkedIn me siguen 1.300 personas.

Promociono mi blog y mi canal de YouTube en varias redes sociales:

Redes Sociales | #ExcelPedroWave

Y estoy activo en estos foros de Excel:

Mis Foros Excel | #ExcelPedroWave

Para consultar dudas de mis artículos del blog de Excel puedes hacer un comentario en mi blog.

Si quieres recibir ayuda para resolver tus propios problemas, crea un tema en uno de los foros de Excel.

Videotutorial de mi ciclo de diseño Excel

Para que quede más claro mi ciclo de diseño Excel puedo preparar un videotutorial, siempre que algún lector o alguna lectora me lo pida...

Ya ves que uso aplicaciones de Microsoft en mis diseños: Excel, OneDrive, PowerPoint y Clipchamp son mis herramientas preferidas. ¿Qué opinas? 

Os dejo, que tengo que comenzar un nuevo ciclo de diseño...

Cómo modificar fórmulas LAMBDA

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

Este es el tercer artículo de la trilogía para convertir números a palabras en inglés y en español, con la función LAMBDA:

1. Conversor de números a palabras | #ExcelPedroWave
2. Cómo copiar fórmulas LAMBDA | #ExcelPedroWave
3. Cómo modificar fórmulas LAMBDA | #ExcelPedroWave

• Con el primer artículo probarás y descargarás el conversor de números a palabras en inglés y en español.
• Con el segundo artículo aprenderás a copiar fórmulas escritas con Excel en inglés a Excel en español.
• Con este tercer artículo sabrás cómo modificar y probar una fórmula compleja que contiene la función LAMBDA.

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

NOTA: Como no todo el mundo tiene instalada la versión de pago de Excel para Microsoft 365, voy a explicar cómo modificar una megafórmula programada con la función LAMBDA en la versión gratuita de Excel para la Web.

AVISO: En la imagen de arriba se muestra el complemento Advanced formula environment del Excel Labs, que no está incluido en la versión gratuita de Excel para la Web, por lo que no lo voy a usar para explicar cómo modificar la fórmula con la función LAMBDA.

Descarga el conversor de números a palabras en la nube

Lo primero que tienes que hacer es descargar el archivo haciendo clic en el botón de Descarga para probarlo en Excel para Microsoft 365 o para subirlo a tu nube de Excel para la Web.

Crea una cuenta gratuita de Microsoft OneDrive

Para crear una cuenta gratuita de Microsoft OneDrive entra en esta página:

Personal Cloud Storage – Microsoft OneDrive

Y haz clic en el botón: Try for free 

Se te pedirá un correo electrónico, o podrás crear un nuevo correo de Outlook a tu nombre, y conseguirás un almacenamiento gratuito en Microsoft OneDrive de 5 GB.

Carga el archivo en tu nube de OneDrive

El conversor de números a palabras que has descargado anteriormente lo debes cargar en tu nube de OneDrive haciendo clic en Cargar y en Archivos, como se ve en esta imagen:

Abre el archivo en tu nube de OneDrive

Haz clic en el archivo que has cargado para abrirlo en tu nube de OneDrive, donde dispones de las funciones de Excel más novedosas, como la función LAMBDA, sin tener que gastar ni un euro...

Pausar protección del archivo en tu nube de OneDrive

Abre la hoja 'LAMBDA' y, en la pestaña Revisar, haz clic en Pausar protección.

Edita en la barra de fórmulas la celda B2, que contiene la fórmula con la función LAMBDA que convierte números a palabras.

Amplia la barra de fórmulas para editar las líneas de la fórmula, con lo que conseguirás ver unas cuantas líneas, pero no todas las líneas de la fórmula ya que es muy grande.

En la última línea se le pasan los parámetros a la función LAMBDA:

))(Number;Indian_or_InterN)

En este caso los parámetros entre paréntesis son dos nombres definidos:

• Number: con el número entero que se va a convertir.
• Indian_or_InterN: valor opcional para palabras en 1: Inglés de la India; 2: Inglés internacional; 3: Español.

Cómo probar la fórmula LAMBDA

Para probar una fórmula muy larga, como la del archivo de ejemplo con la función LAMBDA, se debe emplear la técnica divide y vencerás que permite programar algoritmos complejos.

Con esa técnica he podido analizar la fórmula original, modificarla para incluir la conversión de números a palabras en español y corregirla para eliminar un error detectado en la conversión de números al inglés.

Como dentro de la fórmula original con la función LAMBDA se usa varias veces la función LET, ha sido fácil dividir la fórmula en varias fórmulas más pequeñas, mucho más fáciles de editar, probar y depurar.

En la hoja 'LAMBDA' he dividido en 18 celdas los nombres definidos en la fórmula de la celda B2:

• Option (celda G2): Vale 1 para inglés de la India; 2 para inglés internacional; 3 para español.

• I (celda H2): Cifras del número entero a convertir.

• L_1 (celda I2): Secuencia de los primeros 19 números.

• R_1 (celda J2): Los primeros 19 números convertidos a palabras en inglés o español.

• L_2 (celda K2): Secuencia del 2 al 9.

• R_2 (celda L2): Del 20 al 90 en inglés o español.

• L_3 (celda M2): La conversión de mil, millones, mil millones y billones al inglés.

• A (celda N2): Secuencia de I números.

• B (celda O2): El número a convertir en una matriz con las cifras desplegadas.

• C (C_1 celda P2): Peso de cada cifra a convertir.

• D (celda Q2): Cifras separadas en miles.

• E (celda R2): Cifras de los miles convertidas al inglés o español.

• F (F_1 celda S2): Secuencia inversa de las filas de E

• G (celda T2): cientos y cifras de miles en inglés o español.

• H (celda U2): cientos y miles en español.

• I (celda V2): mil millones en español.

• Final (celda W2): cadenas unidas para convertir el número a palabras.

• Number_To_Words (celda X2): sustituciones elementales de palabras numeradas en español y conversión del número a palabras, tanto en inglés como en español.

Observa que he tenido que renombrar las variables C y F con los nombre C_1 y F_1 respectivamente, pues son usadas internamente por Excel.

Todas las variables anteriores se definen con el símbolo almohadilla o numeral (#) por la derecha, para comportarse como una matriz dinámica desbordada.

Por ejemplo B se calcula dinámicamente con la fórmula:

=EXTRAE(Number; A#; 1)

siendo A# una matriz dinámica con la secuencia de cifras del número a convertir.

Como las fórmulas de estas 18 celdas son más cortas, son más fáciles de analizar, de programar y de probar por separado que la fórmula completa.

Cómo combinar las fórmulas divididas en una única celda

La fórmula LAMBDA está editada en la celda B2 y ha sido combinada a partir de las fórmulas divididas en el rango G2:X2, explicado anteriormente.

AVISO: Hay que tener en cuenta el límite de 8.192 caracteres en una fórmula Excel, pues una fórmula con la función LAMBDA, como la del ejemplo, puede ser más larga debido a la indentación que genera el complemento "Advanced formula environment" del Excel Labs.

Para combinar las fórmulas, en cada fórmula dividida se han quitado los caracteres almohadilla (#) de la derecha de las matrices dinámicas, pues las funciones LET y LAMBDA no los precisan.

También se han renombrado las variables C_1 y F_1 con los nombres originales C y F, respectivamente.

La fórmula de la celda B2 se ha copiado en el Administrador de nombres (en la versión de escritorio de Excel para Microsoft 365) como un nombre definido:

Number_To_Words_IES

En la que se ha quitado el paso de parámetros entre paréntesis al final de la función LAMBDA:

(Number;Indian_or_InterN)

Con lo que se consigue llamar a la función LAMBDA como a cualquier otra función de Excel:

=Words_Converter(Number_To_Words_IES(Number;Indian_or_InterN))

ATENCIÓN: En Excel para la Web que yo sepa no existe el Administrador de nombres, ¡de momento!, por lo que no estos últimos pasos solamente se pueden hacer en Excel para Microsoft 365.

Error corregido en la fórmula original

El principal error detectado y corregido en la fórmula original se debe a que no discrimina a qué corresponden los cientos de cada tres cifras.

Por ejemplo. con el número escrito en la celda A2:

100100

Convertido erróneamente en la celda D2 con la fórmula original al inglés ¡sin Thousand!:

One Hundred One Hundred

Cuando realmente se debe leer correctamente en la celda B2 como:

One Hundred Thousand One Hundred

Videotutorial de cómo modificar fórmulas LAMBDA

En este videotutorial explico cómo modificar fórmulas LAMBDA en Excel para la Web.

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

Mi intención no era optimizar la fórmula LAMBDA sino explicar cómo modificar una fórmula compleja y suficientemente larga, para tener que analizarla dividiéndola en fórmulas más pequeñas que se puedan probar.

Por lo que mis cambios en la fórmula no son los óptimos, aunque he intentado que no haya errores en la conversión de números a palabras.

¿Lo habré conseguido?

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!