Las funciones de transformación de datos en Excel: una guía completa para optimizar su trabajo

Microsoft Excel es una herramienta muy popular para realizar tareas que no necesariamente son financieras o aritméticas, y una de las principales razones es la gran cantidad de opciones que ofrece para convertir, transformar, limpiar y enriquecer datos en bruto y darles la forma adecuada para su uso. La tecnología Power Query en Excel (ahora conocida como Obtener y Transformar) es tan buena en la transformación de datos que es la base de la aplicación Power BI desktop, lo que te permite preprocesar datos de forma repetible para análisis, visualización e incluso aprendizaje automático.

No es necesario conectar a fuentes de datos externas para encontrar útiles las diferentes herramientas de transformación de datos en Excel. Si necesitas ajustar las respuestas de formularios en línea y cuestionarios, limpiar una lista de direcciones, eliminar puntuación y etiquetas HTML de datos copiados de fuentes en línea o formatear tus estados de cuenta de tarjetas de crédito para poder copiar las transacciones en tu reporte de gastos, Excel es la herramienta perfecta. También puedes asegurarte de que las fechas y los montos de divisas se formateen correctamente o agregar datos adicionales como el tipo de cambio (la función XLOOKUP agregada en 2019 es ideal para eso).

La función Texto en Columnas (encuéntrala en Datos, Herramientas de datos) hace mucho más de lo que sugiere su nombre. ¿Abres una hoja de cálculo con fechas en formato estadounidense? Podrías crear un segundo volumen de forma laboriosa y crear una fórmula compleja para invertir el orden de día y mes y obtener fechas en formato europeo, o puedes usar el asistente de Texto en Columnas para hacerlo por ti. Selecciona las celdas con las fechas en formato estadounidense, elige Delimitado en la primera pantalla del asistente y luego desmarca todos los delimitadores sugeridos en la segunda pantalla. En la tercera pantalla, establece el formato de fecha en MES-DÍA-AÑO. La vista previa puede verse incorrecta, pero cuando hagas clic en "Finalizar", las fechas estarán en el formato correcto.

La función Texto en Columnas también puede dividir texto en varias celdas, lo cual es útil para descartar información adicional como el número de comercio en una transacción con tarjeta de crédito, o mover el código postal a un campo separado para poder ordenar las direcciones por ubicación. Si quieres hacer esto automáticamente en cientos de hojas de cálculo sin crear un canal de datos usando las herramientas Obtener y Transformar, usar una fórmula puede ser un enfoque mejor que un cuadro de diálogo con el que tienes que interactuar. Podrías combinar las funciones BUSCAR, ENCONTRAR, IZQUIERDA, DERECHA, MEDIO, LARGO, SUSTITUIR y SECUENCIA para dividir el texto utilizando delimitadores como comas o incluso los espacios entre palabras, pero terminarás escribiendo complicadas expresiones regulares.

Las últimas versiones beta de Office Insider para suscriptores de Microsoft 365 agregan nuevas funciones que funcionan como el asistente Texto en Columnas. TROZOTEXTO divide el texto en una celda en múltiples celdas (una por cada delimitador): el primer valor que se le da a la función es la celda que contiene los datos. Puedes dividir texto en varias columnas o en múltiples filas si deseas convertir un párrafo de texto en oraciones individuales, especificando los delimitadores de columna o fila, que son el segundo y tercer valor de la función, y debes poner comillas alrededor del carácter delimitador, como "," para una coma o "=" para el símbolo de igual. Si deseas que TROZOTEXTO maneje múltiples delimitadores de la misma manera, enuméralos como un conjunto de datos: TROZOTEXTO(A1,{" ",",",";"}) dividirá texto separado por comas, puntos y comas, y espacios.

O puedes convertir tu texto en un conjunto de datos (en Excel, es una tabla que no está formateada como tabla) indicándole a TROZOTEXTO los delimitadores para filas y columnas.

Enseñanza y Aprendizaje con Microsoft: Herramientas y Lecciones para Profesores de todos los Niveles

Si tienes los datos "Rojo=54,Amarillo=16,Morado=99" en la celda A1, TROZOTEXTO(A1,",") creará tres nuevas celdas con "Rojo=54" en la primera, "Amarillo=16" en la siguiente y "Morado=99" en la tercera. TROZOTEXTO utiliza conjuntos de datos dinámicos, por lo que el texto se muestra en tantas celdas como sea necesario (si el conjunto de datos que se desborda sobrescribiría una celda que ya tiene datos, se muestra un error para asegurarse de que no se pierdan datos sin que te des cuenta).

Si siempre necesitas tener el mismo número de columnas o filas, incluso cuando no haya la misma cantidad de datos, puedes indicarle a TROZOTEXTO que cree celdas vacías si hay dos delimitadores uno al lado del otro sin valor en medio. De forma predeterminada, TROZOTEXTO ignora el valor vacío, pero si se coloca VERDADERO como cuarto valor en la función, se agregarán celdas vacías.

Si deseas copiar solo parte del texto de una celda, utiliza TEXTOPREVIO y TEXTODESPUES: especificas el delimitador de la misma manera, pero también indicas qué elemento de la lista deseas (1 para el primer elemento, 2 para el segundo, -1 para el último y -2 para el penúltimo). La diferencia entre las dos funciones es si obtienes el texto antes o después del delimitador. Esto es particularmente útil porque el delimitador no tiene que ser los típicos signos de puntuación; puedes usar texto como marcador para indicar dónde comenzar a copiar o usar un espacio para dividir nombres en nombre y apellido.

Cuando tienes datos organizados en columnas y deseas tenerlos en filas (o viceversa), puedes copiar y pegar transponer para reorganizarlos. Ahora, con las nuevas funciones para cambiar la forma de los datos, puedes hacerlo con una fórmula: AUNAFILA convierte un conjunto de datos en una sola fila, AUNACOLUMNA lo convierte en una sola columna, y ENVOLVERFILAS y ENVOLVERCOLUMNAS convierten filas y columnas en conjuntos de datos.

También puedes unir dos conjuntos de datos utilizando APILARV y APILARH para apilarlos uno debajo del otro o uno al lado del otro, eliminando las celdas vacías entre ellos. Y si tu contenido ya está en varias celdas, hay un conjunto de funciones que te permiten seleccionar columnas y filas de un conjunto de datos, ya sea especificando las columnas y filas que deseas (ELEGIRFILAS y ELEGIRCOLUMNAS), o indicando cuáles filas y columnas deseas conservar (TOMAR) o ignorar (ELIMINAR), comenzando desde el principio o el final del conjunto de datos.

Guía de Examen de Certificación Lotus Notes: Desarrollo de Aplicaciones y Administración del Sistema

Excel ya permite seleccionar columnas en tablas con relativa facilidad, pero trabajar con conjuntos de datos no ha sido tan sencillo, y ahora que cualquier fórmula que devuelve varios resultados se muestra en un conjunto de datos dinámico de celdas, tener funciones para seleccionar columnas y filas dentro de esos conjuntos de datos es muy útil.

Listas automáticas

Si estás ingresando texto y deseas asegurarte de ser consistente en aspectos como nombres de productos, categorías de contabilidad, abreviaturas de estados en direcciones, o cualquier otra cosa donde estés seleccionando entre una lista de posibilidades, puedes crear una lista desplegable en Excel seleccionando Datos, Validación de datos y eligiendo "Lista" en la casilla "Permitir" de la pestaña "Configuración". Utiliza una tabla en tu hoja de cálculo para lo que se mostrará en la lista, para que puedas ampliarla fácilmente: puedes ponerla en una pestaña diferente y seleccionarla en la casilla "Origen" (oculta o bloquea la pestaña si no quieres que las personas puedan cambiar lo que hay en la lista).

Pero esto se vuelve engorroso cuando tienes cientos o miles de entradas en la lista por las que las personas tienen que desplazarse o escribir perfectamente. Puedes agregar un cuadro combinado de control de formulario que permita a las personas comenzar a escribir y que Excel complete la celda con las entradas de la lista que coincidan con lo que están escribiendo, pero eso ya no es una celda estándar (y tienes que crear un cuadro combinado en todas partes donde desees usar la lista en lugar de simplemente usar el Relleno Automático para agregar la lista desplegable a cada celda en la columna).

Esta es una solicitud tan común que existen extensiones de Excel para hacerlo y Microsoft finalmente está agregando la función Autocompletar para las listas desplegables. No tienes que hacer nada diferente: crea tu lista desplegable como de costumbre utilizando la función de Validación de datos, y una vez que hagas clic para abrir la lista, puedes comenzar a escribir para filtrarla. Si hay una sola coincidencia, se autocompletará; si hay varias, aún habrá menos opciones para elegir.

Como todas las funciones en las versiones beta de Office Insider, estas características pueden cambiar o incluso desaparecer antes de su lanzamiento completo. La función de autocompletado de la lista desplegable ya ha sido modificada y relanzada, cambiando en abril de la posibilidad de seleccionar la celda y comenzar a escribir a tener que abrir la lista desplegable antes de comenzar a escribir. Esto ha causado cierta frustración, al igual que el hecho de que, incluso si tienes la versión más reciente de Office Insider, es posible que no veas la función en tu versión debido a la forma en que Microsoft lanza las funciones de Office.

Cómo gestionar eficazmente grandes volúmenes de correo electrónico

Una nueva función se lanza para un subconjunto de usuarios; si la telemetría muestra que no causa bloqueos, problemas de rendimiento u otros problemas, entonces se lanzará progresivamente a más usuarios. Estas entregas se hacen a menudo a todos los usuarios en lugar de activar las funciones para todos en una organización o en un inquilino de Office 365, porque eso permite a Microsoft probar en la mayor variedad de configuraciones de hardware y software, topologías de red, ancho de banda, etc. (en Archivo, Cuenta, Novedades, deberías poder ver si la función está habilitada para ti, pero es una lista larga de funciones que no puedes buscar fácilmente, y el cuadro de diálogo para crear listas desplegables no luce diferente si tienes la nueva función).

Si eso resulta frustrante (o confuso para tus usuarios), asegúrate de que no estén utilizando versiones beta (que no son compatibles y solo están diseñadas para entornos de prueba). Las versiones en canal actual (versión preliminar) incluyen características que estarán en la próxima versión para que los usuarios las prueben anticipadamente y sigan recibiendo soporte, pero aún no se garantiza que todos los que usen una compilación de Office Insider obtendrán las nuevas funciones al mismo tiempo, por lo que debes advertir a las personas que esto tiende a ser impredecible.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Las funciones de transformación de datos en Excel: una guía completa para optimizar su trabajo , tenemos lo ultimo en tecnología 2023.