Si te gusta el Espacio y el Tiempo como a mí, ya puedes hacer este
Excelente Test Espacial, tanto en la nube como si te lo
descargas en tu ordenador, con el que puedes examinar tus conocimientos
sobre récords históricos de los vuelos espaciales.
Sean datos reales o imaginarios,
¡poco importa para hacer este Test! ¿o eres negacionista de los hitos
espaciales?
Las características más importantes del Test Espacial son:
🚀 Cada Test consta de 10 preguntas sobre los récords conseguidos por
los Astronautas durante los vuelos espaciales.
🧑🚀 Cada pregunta del Test se contesta seleccionando un
Astronauta en el desplegable de respuestas.
🆔 Se puede elegir el idioma del Test: Español o English, en la celda
E13.
✔️ Indica que la respuesta es correcta y muestra la
fecha del hito espacial.
❌ Indica que la respuesta es errónea, y seguirá marcada como errónea aunque
se cambie la respuesta por la correcta, en cuyo caso mostrará la fecha del
hito espacial.
🔝 Al final se obtiene el resultado del Test, de 0 a 10 puntos, en la
celda C13.
🔁 Si se quiere hacer otro Test, se debe limpiar el rango de celdas C3:C12,
seleccionándolas y presionando la tecla: Supr. Si haces el Test
en la nube, lee el Aviso al final del artículo para poder repetir de
nuevo el Test.
🛰️ El Test Espacial funciona sin macros, sólo
con fórmulas matriciales y con cálculo iterativo en celdas con
referencias circulares, generando el desplegable con astronautas únicos y
ordenados, como se explica aquí:
En este vídeo explico cómo usar la plantilla anterior para generar
desplegables de astronautas:
Cómo hacer el Test Espacial
Este Test Espacial se puede hacer directamente en la
nube de Microsoft OneDrive, o descargarlo para hacerlo en tu
ordenador.
NOTA: Con el botón con una flecha hacia abajo se puede descargar el archivo.
AVISO: Para hacer otro Test en la nube, como no funciona la tecla Supr en
la nube, se deben seguir estos pasos:
Hacer doble clic en la celda C3.
Pulsar la tecla de retroceso varias veces hasta eliminar la respuesta.
Hacer clic en cualquier otra celda.
Seleccionar la celda C3, que estará vacía.
Arrastrar la celda C3 hasta la celda C12, para borrar todas las
respuestas.
Con lo que aparecerá la primera pregunta de un nuevo Test.
Está protegido sin contraseña y se puede descargar con uno de los botones de
abajo a la derecha. Habilita la edición al abrirlo y te encontrarás con 3
hojas ocultas, necesarias para diseñar este Test Espacial, que no voy
a explicar cómo está hecho a no ser que lo preguntes haciendo un comentario.
Sabiendo lo importante que es introducir correctamente los datos en una
celda, voy a explicar cómo desplegar una lista de valores únicos ordenados
usando fórmulas, en cualquier versión igual o superior a Excel 2010, lo que
nos ayudará a controlar los datos que los usuarios introduzcan en una celda
cualquiera...
La
validación de datos (enlace aquí)
permiten restringir los valores que los usuarios escriben en las celdas,
consiguiendo introducir valores exactos y coherentes.
Limitando la entrada de datos a elementos predefinidos en una
lista desplegable (enlace aquí)
se impide introducir datos inexactos, que luego dan lugar a un cúmulo de
errores al analizar los datos introducidos "malamente", a no ser que
previamente se haya hecho
limpieza de datos (enlace aquí), que es una de las tareas más pesadas para un buen analista de datos. Mas
adelante le dedicaré un capítulo aparte a la limpieza de datos.
Desplegable con valores únicos ordenados
En la siguiente imagen se han introducido unos cuantos valores en la columna
B (VAL), como ejemplo de lista desordenada, duplicada y con errores y
espacios en blanco.
¡Vamos! ¡Lo típico si se introducen datos sin ningún control!
Esos valores están desordenados, duplicados, en blanco o con errores del
tipo: #N/D (no disponible), #¡DIV/0! (división por cero), #¿NOMBRE?
(error en el nombre de la función).
De esos valores solamente se quieren los valores sin errores, que sean
distintos de blanco y que sean únicos y sin duplicar, y que además estén
ordenados para que sean fáciles de buscar al
aplicar la validación de datos a celdas
con una lista desplegable.
La columna D (ORD) contiene una lista ordenada sin errores y sin
valores duplicados, pero sigue habiendo valores en blanco.
La columna E (UNI) es una lista con valores únicos y ordenados,
aunque los espacios en blanco aparecen al final de la lista.
En la columna G se han eliminado los espacios en blanco al final de la
lista, con lo que es la lista que se usará en el desplegable de la celda G2.
Prueba y descarga este desplegable
He subido el ejemplo de validación de datos con una lista desplegable, con
valores únicos ordenados, a
la nube de Microsoft OneDrive
para que lo pruebes y lo descargues si te apetece, o por si quieres usarlo
en tus hojas de cálculo. Está protegido sin contraseña y se puede descargar
con uno de los botones de abajo a la derecha, y recordar que hay que
habilitar la edición al abrirlo.
En esta hoja se pueden editar las celdas del rango B3:B30, para así poder
cambiar los valores de entrada que se quieran probar.
IMPORTANTE: Si se aumenta el rango de la columna B habrá que modificar manualmente
los nombres definidos: VAL1INI, VAL3AUX y VAL5AUX.
COMPATIBILIDAD: Con versiones de Excel que no soportan
matrices desbordadas, habrá que expandir las fórmulas de matriz como se explica en este enlace:
La celda G2 contiene la validación de datos con la lista desplegable de
valores únicos ordenados, sin espacios en blanco y sin errores.
PROPUESTAS PARA VERSIONES RECIENTES DE EXCEL:
En la columna I he incluido la matriz desbordada propuesta por
Gabriel Raigosaaquí, con las funciones FILTRAR, UNICOS y ORDENAR, que únicamente funcionan en
versiones recientes de Excel.
En la columna K he incluido la matriz desbordada propuesta por
Gerson Pinedaaquí, con la función LET, que sólo funciona en Excel para Microsoft 365; Excel
para la Web y Excel 2021.
Cómo desplegar valores únicos ordenados
En esta página en inglés hay multitud de métodos con listas desplegables:
Ya sé que Microsoft ha incluido
fórmulas de matriz dinámica
en las últimas versiones de Excel, con funciones como
UNICOS
y
ORDENAR, que facilitan la creación de listas desplegables únicas y ordenadas, pero
no son compatibles con todas las versiones de Excel, por lo que no las he
usado en mi desplegable...
También sé que Microsoft está desarrollando el autocompletar para listas
desplegables en Excel para Windows, pero aún no han conseguido la exclusión
de duplicados y solamente está disponible si estás suscrito al canal con la
versión Beta de Excel, como se puede leer en esta página en inglés:
El algoritmo de Autocompletar para la lista desplegable hace coincidir la
cadena que escribe en la celda de la lista desplegable con las palabras de
los elementos de la lista desplegable y luego muestra solo los elementos
de la lista coincidentes. A medida que escribe más caracteres, la lista
desplegable se contrae y, a la inversa, cuando elimina caracteres, se
expande. Las palabras coincidentes pueden provenir de cualquier parte de
la cadena del elemento de la lista: al principio, en el medio o al final.
Además, la lista desplegable excluye elementos en blanco de la lista
desplegable Validación de datos.
Será de gran ayuda cuando se libere esa versión Beta pero, mientras tanto,
nos tendremos que conformar con otros métodos de despliegue en celdas de
Excel.
Para obtener una lista desplegable con valores únicos ordenados se pueden
usar cualquiera de esos métodos, pero me he decantado por usar únicamente
fórmulas y nombres definidos, lo que hace que mi método sea compatible con
cualquier versión igual o superior a Excel 2010, incluso en Excel para la
Web.
Mi método para desplegar valores únicos ordenados
Mi propio método se basa en usar nombres definidos mediante el
Administrador de nombres en Excel, en los que se definen rangos de celdas y fórmulas con matrices dinámicas.
Desde la cinta de opciones se selecciona:
Fórmulas > Administrador de nombres
En esta imagen están los nombres definidos necesarios para crear una lista
con valores únicos ordenados.
Son estos 7 nombres definidos en el ámbito de la hoja 'VAL':
El nombre VAL0TXT es el valor de la celda G2 con el desplegable.
El nombre VAL1INI es el rango de celdas con los datos de origen.
El nombre VAL2ORD contiene la fórmula que ordena los datos. He
corregido un error en esta fórmula detectado por Gabriel Raigosa en
este enlace:
El nombre VAL3AUX es el rango auxiliar de celdas ordenadas de la
columna D.
El nombre VAL4UNI contiene la fórmula que deja valores únicos y los
valores en blanco al final.
El nombre VAL5AUX es el rango auxiliar de celdas únicas ordenadas de
la columna E.
El nombre VAL6FIN contiene la fórmula que elimina los valores en
blanco del final y obtiene la lista de valores únicos ordenados.
AVISO: Se puede eliminar la fórmula matricial desbordada de la celda G3, pues
solamente se usa como comprobación. Lo que no se puede eliminar son las
listas de las columnas auxiliares D y E, pues si no existieran dejarían de
funcionar las fórmulas de los nombres definidos.
El resultado de todo este despliegue de nombres definidos y de fórmulas se
obtiene en la celda G2, que contiene la siguiente validación de datos, con
origen en el nombre definido: =VAL6FIN
Con este desplegable ya tenemos el control de los valores que se pueden
introducir en una celda cualquiera.
En el siguiente artículo he diseñado mi propio trivial de astronautas
basado en este tipo de desplegable:
Incorpora dos idiomas de búsqueda de las palabras claves y de las
descripciones de los Emojis en Español y
en English.
En este artículo explicaré
Cómo añadir más idiomas a este Buscador de Emojis, usando la
herramienta ETL (Extract - Transform - Load):
Power Query en Excel, con la que extraeremos el idioma que se desee de un archivo XML, con las
descripciones y las palabras claves de búsqueda de los Emojis, cargándolos
en una nueva hoja con los textos en esa lengua 👅
Descargar más idiomas en el Buscador de Emojis
Los archivos con los idiomas de los Emojis los publica la organización:
CLDR es un Repositorio de Datos Localizados Comunes, que
proporciona nombres cortos y palabras clave de búsqueda para los nuevos
Emojis, de modo que las implementaciones puedan basarse en ellos para
proporcionar, por ejemplo, escritura anticipada en teclados, que es lo que
he hecho en las dos versiones del Buscador de Emojis que he publicado.
Para añadir más idiomas a la segunda versión del Buscador de Emojis,
publicada en el artículo anterior, se deben seguir estos pasos:
El formato de esos 146 archivos es del tipo
XML (eXtensible Markup Language), especialmente dedicado a la comunicación de datos localizados,
mediante el formato:
Este Lenguaje de Marcado de Datos Localizados es un formato común para que
los sistemas intercambien datos localizados y para que puedan obtener los
mismos resultados en los servicios proporcionados por las bibliotecas de
internacionalización de Emojis.
Para conocer las condiciones de uso de estos archivos, consulte su página de
Copyright:
Como ejemplo al Buscador de Emojis le vamos a añadir el idioma
Portugués.
Estos son los 27 pasos a seguir (al final de este artículo puedes ver un
vídeo con estos mismos pasos):
1) En la misma carpeta con el Buscador de Emojis, copia el archivo:
pt.xml
2) Con una versión reciente de Excel abre el archivo:
Buscador Emojis Unicode Look Up PW2.xlsx
3) Habilita las conexiones de datos externos.
4) Selecciona en la cinta de opciones:
Datos > Obtener datos > Configuración del origen de datos...
5) En la Configuración de origen de datos debe estar marcado:
Orígenes de datos en el libro actual y presionar el botón:
Cambiar origen...
6) En modo XML Básico, presionar el botón: Examinar...
7) Importar datos del archivo descargado previamente con el idioma
portugués: pt.xml
8) Teniendo seleccionado Abrir archivo como: Tablas XML, presionar el
botón: Aceptar y a continuación el botón: Cerrar
9) Selecciona en la cinta de opciones: Datos > Consultas y conexiones
10) Aparecen 4 conexiones en Consultas y conexiones con:
Solo conexión.
Hacer clic con el botón derecho del ratón en la consulta
CP y seleccionar en el menú contextual: Cargar en...
11) Seleccionar: Tabla y Hoja de cálculo nueva y
presionar el botón: Aceptar
12) Se habrá creado una nueva hoja CP con 1.910 filas.
NOTA: Las siguientes instrucciones no están explicadas en el vídeo y hacen
falta para no borrar la conexión CP, para poder añadir futuros
idiomas...
Copiar la hoja CP en otra hoja haciendo clic con el botón derecho del
ratón sobre el nombre de la hoja y en el menú contextual seleccionar:
Mover o copiar...
Seleccionar: (mover al final), marcar: Crear una copia y
presionar el botón: Aceptar. Se habrá creado la hoja CP (2)
Eliminamos la hoja: CP
13) En la hoja CP (2) eliminamos la columna C: Attribute:draft
14) Eliminamos la consulta de la hoja CP (2), seleccionando la
celda A1 y seleccionando en la cinta de opciones:
Consulta > Eliminar
En esta ventana presionar el botón: Eliminar
15) Renombrar la hoja CP (2) con las dos letras que identifican al
idioma Portugués: PT
16) Copiar la fila 1 de cabecera de la hoja ES en la hoja
PT.
17) Seleccionar en la cinta de opciones: Vista > Inmovilizar > Inmovilizar fila superior
Y desmarcar: Líneas de cuadrícula
18) Seleccionar en la cinta de opciones:
Diseño de tabla > Estilos de tabla
Y seleccionar un estilo del tipo: Gris claro medio.
19) Seleccionar la hoja PT y cambiar el color de la pestaña a
negro.
20) Cambiar nombre de la tabla: TablaPT
21) Ordenar la tabla de la A a la Z por la columna B ✍, con la
descripción de los Emojis.
22) Ocultar las filas 1912 y siguientes y las columnas D y siguientes,
para que sólo se vea la tabla.
23) Proteger la hoja PT sin contraseña, marcando:
✔️ Seleccionar celdas bloqueadas
✔️ Seleccionar celdas desbloqueadas
✔️ Usar Autofiltro
24) Desproteger la hoja Emojis.
25) En una nueva fila de la TablaIdiomas, escribir: PT y
Portugues
26) Proteger la hoja Emojis sin contraseña, marcando:
✔️ Seleccionar celdas bloqueadas
✔️ Seleccionar celdas desbloqueadas
27) Seleccionar con el desplegable de la celda H12 el idioma:
Portugues
¡Ya podemos buscar Emojis en portugués!
Vídeo de cómo añadir una lengua al Buscador
Si no te han quedado claro los pasos a seguir, mira este vídeo para saber
cómo añadir una nueva lengua al Buscador de Emojis:
¿Te animas a añadir alguna lengua más al Buscador de Emojis?
Consultas en Power Query para extraer la lengua
Para examinar y/o editar las consultas necesarias en Power Query en Excel para añadir un nuevo idioma hay que seleccionar en la cinta de
opciones:
Datos > Obtener datos > Iniciar Editor de Power Query
Son estas 4 consultas:
1) annotation - con el origen del archivo en formato XML con el
idioma.
Instrucciones paso a paso en lenguaje M de la consulta annotation:
2) annotation_tts - con las descripciones de los Emojis.
Instrucciones paso a paso en lenguaje M de la consulta
annotation_tts:
3) annotation_text - con las palabras clave de búsqueda de los
Emojis.
Instrucciones paso a paso en lenguaje M de la consulta
annotation_text:
4) CP - con las descripciones y las palabras clave de búsqueda de
los Emojis.
Instrucciones paso a paso en lenguaje M de la consulta CP:
Son 4 consultas solo conexión aunque, como he explicado en este artículo, la
última es la que se debe cargar como tabla en una hoja con los textos de los
Emojis en el nuevo idioma.
El próximo artículo será sobre
Cómo desplegar valores únicos ordenados mediante validación de
datos, eliminando los errores y los espacios en blanco, para que en el
desplegable de la celda solamente aparezcan los valores válidos. Este es el
enlace al nuevo artículo:
32-Bit vs 64-Bit Excel: Which Is Better?
-
32-bit vs 64-bit Excel—which is better? It all comes down to the need for
memory, data size, and compatibility. Not sure which version of Microsoft
Excel 3...
Tablas Dinámicas en Excel y Google Sheets
-
Las tablas dinámicas son una de las herramientas más poderosas en una hoja
de cálculo, esenciales para cualquier usuario que […]
The post Tablas Dinámica...
Debra’s Excel News–November 2024
-
Fix pivot table errors, show images in cells, and more, in this month’s
Excel news. Visit my Excel website for more tips, tutorials and videos, and
check t...
Accountex 2024 Excel a Power BI
-
En Accountex 2024 el jueves 7 noviembre presenté: «BI transición de Excel a
Power BI» (Sesión coordinada por el Consejo General de Economistas)Esta
sesió...
NADA tras la DANA
-
🔝*To translate this blog post to your language, select it in the top left
Google box. *
NADA a favor de la DANA
Justo después de las inundaciones d...
Installing 3DFrame-py
-
As mentioned in the previous post, the installation process for the
3DFrame-py spreadsheet has changed with the new version. Also there have
been some sign...
Divisor propio mayor que la raíz cuadrada
-
Explorando por OEIS, encontré un tipo de números en https://oeis.org/A332269
y me ha apetecido desarrollar el tema mediante nuestras funciones en hoja ...
How To Predict Bearing Life With Excel
-
When you work in mechanical engineering, understanding the reliability and
performance of bearings under various conditions is crucial. Bearings are
the co...
TikTok’s search evolution
-
2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are
more likely to rely on TikTok than Google as a search engine. More than
half of...
Interés compuesto con Excel
-
Este es un mapa mental de las distintas funciones Excel para calcular el
valor del dinero en el tiempo aplicando la ley de capitalización compuesta.
En ...
Unblocking and Enabling Macros
-
When Windows detects that a file has come from a computer other than the
one you're using, it marks the file as coming from the web, and blocks the
file....
-
Aprendiendo Unreal Engine 5. ¿Por dónde empiezo?
Desde la presentación de la Comunidad Oficial de desarrolladores de Unreal
Engine de Málaga, he recibido m...
Navigating Outlook Favorites
-
I have these four favorites defined in Outlook: From the inbox, I could hit
Shift+F6 to get into the Favorites area but sometimes I would end up in no
man’...
Office Scripts: Trabajando con Tablas
-
[image: Office Scripts: Trabajando con Tablas]
Me he dado cuenta que últimamente solo escribo de lenguaje M (es mi pequeño
vicio)... pero hay que liberar l...
Progress on the Block Protocol
-
Since the 1990s, the web has been a publishing place for human-readable
documents. Documents published on the web are in HTML. HTML has a little
bit of… Re...
London Excel Meetup Workbooks
-
The workbooks used in my presentation on “Analytical and Interactive
Dashboards in Excel” at the London Excel Meetup, September 3, 2020
Mis metas son seguir superando nuevos retos en Excel y compartirlos en mi blog, para sacarle todo el poder a esta excelente herramienta multiusos, tan usada y a la vez tan incomprendida, para así poder mejorar nuestros conocimientos de Excel.
