Al trabajar con datos en Excel, algunas tareas pueden resultar innecesariamente tediosas. Quizás necesite dividir una columna de nombres completos en columnas separadas para nombres y apellidos, o combinar texto de varias celdas con comas específicas. Estos no son desafíos analíticos complejos, sino tareas básicas de procesamiento de datos que surgen con frecuencia.
La buena noticia es que Excel tiene funciones integradas diseñadas específicamente para estas situaciones. Sin embargo, a menudo se pasan por alto porque no forman parte de... El conjunto de herramientas estándar de Excel que la mayoría de la gente aprende, incluyéndome a mí. Las funciones que explicaré aquí no se centran en cálculos avanzados, pero si trabajas con datos de forma repetitiva, estas funciones pueden ahorrarte tiempo.
Enlaces rápidos
5. DIVIDIR TEXTO
Separa textos pegados entre sí
Si alguna vez has recibido una hoja de cálculo donde alguien ha metido su nombre y apellido, e incluso sus iniciales, en una sola celda, sabes lo complicado que es separar esos datos. TextSplit soluciona este problema: toma el texto de una sola celda y lo divide en varias columnas según el separador que especifiques.
Trabajemos con una hoja de cálculo de ventas de ejemplo. Verá los nombres de los representantes de ventas como "Sarah Chen", "Mike Johnson" y "Lisa Park", todos en una misma columna. En lugar de escribir manualmente cada nombre en columnas separadas, TextSplit puede hacerlo automáticamente.
La fórmula es la siguiente:
=DIVIDIRTEXTO(texto, delimitador_de_columna, [delimitador_de_fila], [ignorar_vacío], [modo_de_coincidencia], [rellenar_con])
Esto es lo que hace cada profesor:
- texto: La celda que contiene el texto que desea dividir.
- delimitador de columna: El carácter que separa los datos (como un espacio, una coma o un punto y coma).
- delimitador de fila (opcional): Se utiliza cuando se divide en filas y columnas.
- ignore_empty (opcional): VERDADERO ignora los valores vacíos, FALSO los conserva (el valor predeterminado es FALSO).
- match_mode (opcional): Controla la distinción entre mayúsculas y minúsculas (0 para distinguir entre mayúsculas y minúsculas, 1 para no distinguir entre mayúsculas y minúsculas).
- pad_with (opcional): ¿Con qué rellenar las celdas vacías cuando los resultados tienen longitudes desiguales?
Para los nombres de los representantes de ventas, por ejemplo, usaría la siguiente fórmula para dividir los nombres en columnas separadas:
=DIVIDIRTEXTO(A2, " ")
La función crea automáticamente el número necesario de columnas según sus datos. Si bien este enfoque básico funciona en la mayoría de los casos, existen parámetros adicionales que le brindan un control preciso sobre... Función TEXTSPLIT en Excel.
4. TEXTJOIN
Fusionar varias celdas en una sola
TEXTJOIN hace lo contrario de TEXTSPLIT. Toma texto de varias celdas y lo combina en una sola usando el separador que elijas. Esto es útil cuando necesitas crear valores secuenciales, como direcciones completas, descripciones de productos o listas de correo electrónico.
La fórmula se ve así:
=TEXTOUNIR(delimitador, ignorar_vacío, texto1, [texto2], ...)
Esto es lo que controla cada parámetro:
- delimitador: El carácter o texto que separa los valores incrustados (coma, espacio, guion, etc.).
- ignorar_vacío: VERDADERO para ignorar celdas en blanco, FALSO para incluirlas en el resultado.
- texto1, texto2, etc.: Las celdas o rangos que desea fusionar (puede especificar celdas individuales o rangos completos).
Al observar la hoja de cálculo de ventas, si tengo columnas separadas para el nombre y la región, pero los necesito en una columna que los combine, usaría TEXTJOIN. ignore_empty VERDADERO significa que cualquier celda en blanco se omite automáticamente.
=TEXTOUNIR(" - ", VERDADERO, B2, D2)Al elegir entre diferentes métodos de integración de texto, es necesario comprender Diferencias entre las funciones CONCAT y TEXTJOIN Puede ayudarle a elegir la herramienta adecuada para sus necesidades específicas de integración de datos.
3. ELECCIONES
Especifique columnas específicas de sus datos.
CHOOSECOLS permite extraer columnas específicas de un rango sin copiar y pegar ni crear referencias. Si tiene un conjunto de datos grande, pero solo necesita las columnas 2, 5 y 8 para su análisis, esta función tomará lo que necesite y descartará el resto.
Según los datos de ventas, podría querer extraer solo el vendedor y sus nombres, ignorando las fechas de pedido, las categorías de producto y otros detalles. En lugar de seleccionar y copiar columnas manualmente, la función CHOOSECOLS crea una referencia dinámica que se actualiza automáticamente cuando cambian los datos de origen.
La función sigue la siguiente fórmula:
=ELEGIRCOLS(matriz, col_num1, [col_num2], ...)
Así es como funciona cada parámetro:
- formación: El rango o la tabla que contiene los datos de origen (puede ser un rango de celdas como A1:F100 o una referencia de tabla).
- columna_num1: El número de la primera columna que desea extraer (1 para la primera columna, 2 para la segunda columna, etc.).
- col_num2, etc.: Números de columna adicionales que desee incluir (opcional: puede especificar tantos como desee).
Por ejemplo, si quisiera extraer los nombres de los representantes de ventas de la columna 2 y su estado de la columna 9, usaría:
=ELEGIRCOLES(A1:I23, 2, 9)
La función devuelve ambas columnas como una matriz de flujo continuo, redimensionada automáticamente para ajustarse a los datos. Por eso, CHOOSECOLS es una de las... Funciones de Excel que pueden ahorrarte mucho tiempoElimina la necesidad de utilizar múltiples fórmulas VLOOKUP o de copiar columnas manualmente cuando se trabaja con grandes conjuntos de datos.
Excel también tiene una función ELEGIRFRONTERAS, que funciona de manera similar pero selecciona filas específicas en lugar de columnas, utilizando la misma estructura de fórmula con números de fila.
2. TOMAR y SOLTAR
Extraer partes de tus datos
TAKE y DROP funcionan en conjunto para capturar partes específicas de su rango de datos. TAKE extrae un número específico de filas o columnas del principio o del final de su conjunto de datos, mientras que DROP elimina filas o columnas del principio o del final, dejándole con lo restante.
Estas funciones actúan como herramientas precisas para el muestreo de datos. Ya sea que solo necesite las primeras diez filas de datos para un análisis rápido o que desee eliminar las filas de encabezado que obstruyen sus cálculos, estas funciones realizan la tarea a la perfección.
TAKE utiliza esta fórmula:
=TOMAR(matriz, filas, [columnas])
DROP sigue un patrón similar:
=DROP(matriz, filas, [columnas])
Así es como funcionan los parámetros para ambas funciones:
- formación: El rango de datos de origen que desea extraer o modificar.
- filas: Número de filas que se tomarán/eliminarán (los números positivos funcionan desde arriba, los negativos desde abajo).
- columnas (opcional):
El número de columnas que desea tomar o eliminar (positivas desde la izquierda, negativas desde la derecha).
Para obtener las primeras cinco filas de datos de ventas, utilice la siguiente fórmula:
=TOMAR(A1:C100, 5)
Para eliminar las primeras 20 filas y trabajar con datos limpios, intente:
=SOLTAR(A1:C23, 20)
Se pueden combinar operaciones de filas y columnas. Por ejemplo, la siguiente fórmula muestra las primeras diez filas y las primeras tres columnas:
=TOMAR(A1:F23, 10, 3)
Estas funciones resultan muy útiles, especialmente cuando se necesitan subconjuntos dinámicos de datos que se adapten automáticamente. Aprender Cómo utilizar las funciones TAKE y DROP en Excel Le abre posibilidades para crear informes flexibles que se adapten a tamaños cambiantes de conjuntos de datos.
1. AGREGAR
Cálculos potentes que manejan datos desordenados
AGREGAR combina la funcionalidad de 19 funciones estadísticas diferentes en una fórmula flexible. Su principal característica es su capacidad para ignorar errores, filas ocultas o datos filtrados, algo que funciones estándar como SUMA o PROMEDIO no pueden hacer con fiabilidad.
Si sus datos contienen errores #N/A, o si filtra para mostrar solo ciertas regiones, AGGREGATE puede calcular sumas, promedios u otras estadísticas sin que estos problemas alteren sus resultados. Resulta útil al trabajar con conjuntos de datos dinámicos donde la visibilidad y la calidad de los datos cambian con frecuencia.
La estructura de la oración incluye varios componentes:
=AGREGAR(función_num, opciones, matriz, [k])
Cada criterio controla diferentes aspectos del cálculo:
- número_de_función: Un número del 1 al 19 que especifica la función a utilizar (1=PROMEDIO, 4=MÁX, 9=SUMA, 12=MEDIANA, etc.).
- opciones: Controla qué ignorar durante el cálculo (0=ninguno, 1=filas ocultas, 2=valores de error, 3=filas ocultas y errores, 5=solo valores de error, 6=filas ocultas y valores de error).
- formación: El rango de celdas a calcular.
- k (opcional):
- Sólo se utiliza con determinadas funciones como GRANDE, PEQUEÑO o PERCENTIL.
Para resumir los montos de ventas mostrados ignorando cualquier error, puedo usar:
=AGREGAR(9, 6, D2:D23)
El número 9 especifica la SUMA y el número 6 le indica a la función que ignore las filas ocultas y los valores de error.
Esta poderosa capacidad para realizar cálculos es exactamente la razón por la que se incluye AGGREGATE. Lista de funciones de Excel que todo trabajador de oficina debería conocer—Maneja el caos de datos del mundo real que las funciones más simples no pueden gestionar de manera efectiva.
Herramientas integradas que vale la pena usar
Las funciones más importantes de Excel no suelen ser las que se aprenden primero. Sin embargo, abordan los problemas sutiles que surgen en el trabajo real con hojas de cálculo, como el manejo de datos de texto desordenados, la extracción de partes específicas de grandes conjuntos de datos y la realización de cálculos con datos incompletos. Ninguna de las funciones que hemos analizado requiere conocimientos avanzados de Excel. Sin embargo, TEXTSPLIT, CHOOSECOLS, TAKE y DROP solo están disponibles en Microsoft 365 y Excel para la web.
La próxima vez que tengas que limpiar datos repetidamente o copiar columnas manualmente, recuerda que estas funciones existen. Ya están integradas en Excel para gestionar las tareas tediosas, así que puedes concentrarte en lo que realmente te dicen los datos.
