Big Data

Consejos y trucos de Excel | Consejos de Excel para el análisis de datos

Visión general

Microsoft Excel es una de las herramientas más utilizadas para el análisis de datos.
Conozca las funciones esenciales de Excel que se utilizan para analizar datos para análisis comerciales.
El análisis de datos con Excel sirve como precursor de la ciencia de datos con R o Python

* Este artículo se publicó originalmente en 2015 y se actualizó en abril de 2020.

Introducción

Siempre he admirado el inmenso poder de Sobresalir. Este software no solo es capaz de realizar cálculos de datos básicos, sino que también puede realizar análisis de datos usándolo. Se usa ampliamente para muchos propósitos, incluidos modelos financieros y planificación empresarial. Puede convertirse en un buen trampolín para las personas que son nuevas en el mundo de la analítica empresarial.

Incluso antes de aprender R o Python, es recomendable tener conocimientos de Excel. No hace ningún daño agregar Excel a su conjunto de habilidades. Excel, con su amplia gama de funciones, visualizaciones y matrices, le permite generar rápidamente conocimientos a partir de datos que serían difíciles de ver de otro modo. Y este es un aspecto crucial de cualquier proyecto de análisis empresarial.

De hecho, hemos diseñado un todo el programa integral de Business Analytics para usted, ¡con Excel como componente clave! Asegúrese de comprobarlo y darse el regalo de una carrera en analítica empresarial.

Me siento afortunado de que mi viaje haya comenzado con Excel. A lo largo de los años, he aprendido muchos trucos para trabajar con los datos más rápido que nunca. Excel tiene numerosas funciones. A veces se vuelve confuso elegir el mejor.

En este artículo, le proporcionaré algunos consejos y trucos para trabajar en Excel y ahorrarle tiempo. Este artículo es más adecuado para personas interesadas en actualizar sus habilidades de análisis de datos.

Funciones de uso común

1. Vlookup (): Ayuda a buscar un valor en una tabla y devuelve un valor correspondiente. Veamos la tabla a continuación (Política y Cliente). En la tabla de políticas, queremos mapear el nombre de la ciudad de las tablas de clientes en función de la clave común «ID de cliente». Aquí, la función vlookup () ayudaría a realizar esta tarea.

Syntax: =VLOOKUP(Key to lookup, Source_table, column of source table, are you ok with relative match?)

Para el problema anterior, podemos escribir la fórmula en la celda «F4» como = BUSCARV (B4, $ H $ 4: $ L $ 15, 5, 0) y esto devolverá el nombre de la ciudad para todos los ID de cliente 1 y publicará esa copia esta fórmula para todos los ID de cliente.

Propina: No olvide bloquear el rango de la segunda tabla con un signo «$», un error común al copiar esta fórmula. Esto se conoce como referencia relativa.

2. CONCATENAR (): Es muy útil combinar texto de dos o más celdas en una sola. Por ejemplo, queremos crear una URL basada en la entrada del nombre de host y la ruta de solicitud.

Syntax: =Concatenate(Text1, Text2,.....Textn)

El problema anterior se puede resolver usando la fórmula, = concatenar (B3, C3) y copiarlo.

Propina: Prefiero usar el símbolo «&», porque es más corto que escribir una fórmula de «concatenar» completa, y hace exactamente lo mismo. La fórmula se puede escribir como «= B3 & C3».

3. LEN () – Esta función le informa sobre la longitud de una celda, es decir, el número de caracteres, incluidos los espacios y los caracteres especiales.

Syntax: =Len(Text)

Ejemplo: = Len (B3) = 23

4. INFERIOR (), SUPERIOR () y ADECUADO () –Estas tres funciones ayudan a cambiar el texto a mayúsculas, minúsculas y oraciones respectivamente (Primera letra de cada palabra en mayúscula).

Syntax: =Upper(Text)/ Lower(Text) / Proper(Text)

En un proyecto de análisis de datos, estos son útiles para convertir clases de un caso diferente en un solo caso, de lo contrario, se consideran clases diferentes de la característica dada. Mire la instantánea a continuación, la columna A tiene cinco clases (etiquetas) mientras que la columna B tiene solo dos porque hemos convertido el contenido a minúsculas.

5. RECORTAR (): Esta es una función útil que se utiliza para limpiar texto que tiene espacios en blanco al principio y al final. A menudo, cuando obtiene un volcado de datos de una base de datos, el texto con el que está tratando se rellena con espacios en blanco. Y si no se ocupa de ellos, también se tratan como entradas únicas en una lista, lo que ciertamente no es útil.

Syntax: =Trim(Text)

6. SI (): Lo encuentro una de las funciones más útiles en Excel. Le permite usar fórmulas condicionales que calculan de una manera cuando una determinada cosa es verdadera y de otra manera cuando es falsa. Por ejemplo, desea marcar cada venta como «Alta» y «Baja». Si las ventas son mayores o iguales a $ 5000, entonces «Alto» o «Bajo».

Syntax: =IF(condition, True Statement, False Statement)

Generando inferencia a partir de datos

1. Tabla dinámica: Siempre que trabaja con datos de la empresa, busca respuestas a preguntas como «¿Cuántos ingresos aportan las sucursales de la región norte?» o «¿Cuál fue el número medio de clientes del producto A?» y muchos otros.

La tabla dinámica de Excel le ayuda a responder estas preguntas sin esfuerzo. Una tabla dinámica es una tabla de resumen que le permite contar, promediar, sumar y realizar otros cálculos de acuerdo con la función de referencia que haya seleccionado, es decir, convierte una tabla de datos en una tabla de inferencia que nos ayuda a tomar decisiones. Mire la instantánea a continuación:
Arriba, puede ver que la tabla de la izquierda tiene detalles de ventas para cada cliente con la región y el mapeo de productos. En la tabla de la derecha, hemos resumido la información a nivel de región que ahora nos ayuda a generar una inferencia de que la región Sur tiene las ventas más altas.

Métodos para crear una tabla dinámica:
Paso 1: Haga clic en algún lugar de la lista de datos. Elegir el Insertar pestaña y haga clic en Tabla dinámica. Excel seleccionará automáticamente el área que contiene los datos, incluidos los encabezados. Si no selecciona el área correctamente, arrastre sobre el área para seleccionarla manualmente. Lo mejor es colocar la tabla dinámica en una hoja nueva, así que haga clic en Nueva hoja de trabajo para la ubicación y luego haga clic en OKPaso 2: Ahora, puede ver el panel Lista de campos de la tabla dinámica, que contiene los campos de su lista; todo lo que necesita hacer es colocarlos en las cajas al pie del panel. Una vez que haya hecho eso, el diagrama de la izquierda se convierte en su tabla dinámica.
Arriba, puede ver que hemos organizado «Región» en fila, «Identificación de producto«En columna y suma de»Prima”Se toma como valor. Ahora está listo con la tabla dinámica que muestra la suma de la prima por región y producto. También puede utilizar recuento, promedio, mínimo, máximo y otras métricas de resumen.

2. Creación de gráficos: Crear un cuadro / gráfico en Excel no requiere nada más que seleccionar el rango de datos que desea trazar y presionar F11. Esto creará un gráfico de Excel con el estilo de gráfico predeterminado, pero puede cambiarlo seleccionando un estilo de gráfico diferente. Si prefiere que el gráfico esté en la misma hoja de trabajo que los datos, en lugar de presionar F11, presione ALT + F1.

Por supuesto, en cualquier caso, una vez que haya creado el gráfico, puede personalizarlo según sus necesidades particulares para comunicar el mensaje deseado.

Limpieza de datos

1. Elimine los valores duplicados: Excel tiene una función incorporada para eliminar valores duplicados de una tabla. Elimina los valores duplicados de la tabla dada en función de las columnas seleccionadas, es decir, si ha seleccionado dos columnas, busca un valor duplicado que tenga la misma combinación de datos de ambas columnas.

Arriba, puede ver que A001 y A002 tienen un valor duplicado, pero si seleccionamos ambas columnas «IDENTIFICACIÓN» y «Nombre”, Entonces solo tenemos un valor duplicado (A002, 2).
Siga estos pasos para eliminar los valores duplicados: Seleccione datos -> Ir a la cinta de Datos -> Eliminar duplicados

2. Texto a columnas: Supongamos que tiene datos almacenados en la columna como se muestra en la siguiente instantánea.
Arriba, puede ver que los valores están separados por punto y coma «;». Ahora, para dividir estos valores en una columna diferente, recomendaré usar el «Texto a columnas”Característica en Excel. Siga los pasos a continuación para convertirlo a diferentes columnas:

Seleccione el rango A1: A6
Vaya a la cinta «Datos» -> «Texto a columnas»
Arriba, tenemos dos opciones “Delimitado” y “Ancho fijo”. He seleccionado delimitado porque los valores están separados por un delimitador (;). Si estuviéramos interesados en dividir los datos en función del ancho, como los primeros cuatro caracteres en la primera columna, del 5 al 10º carácter en la segunda columna, entonces elegiríamos Ancho fijo.
Haga clic en Siguiente -> Marque la casilla de verificación para «Punto y coma», luego en Siguiente y finalice.

Atajos de teclado esenciales

Los atajos de teclado son la mejor manera de navegar por las celdas o ingresar fórmulas más rápidamente. A continuación, enumeramos nuestros favoritos.

Ctrl +[Down|Up Arrow]: Se mueve a la celda superior o inferior de la columna actual y la combinación de Ctrl con Izquierda | Flecha derecha , se mueve a la celda más a la izquierda o derecha en la fila actual
Ctrl + Mayús + Flecha arriba / abajo: Selecciona todas las celdas por encima o por debajo de la celda actual
Ctrl + Inicio: Navega a la celda A1
Ctrl + Fin: Navega a la última celda que contiene datos
Alt + F1: Crea un gráfico basado en el conjunto de datos seleccionado.
Ctrl + Mayús + L: Activa el filtro automático a la tabla de datos
Alt + Flecha abajo: Para abrir el menú desplegable de filtro automático
Alt + D + S: Para ordenar el conjunto de datos
Ctrl + O: Abrir un nuevo libro
Ctrl + N: Crear un libro de trabajo nuevo
F4: Seleccione el rango y presione la tecla F4, cambiará la referencia a absoluta, mixta y relativa.

Nota: esta no es una lista exhaustiva. No dude en compartir sus atajos de teclado favoritos en Excel en la sección de comentarios a continuación. Literalmente, hago el 80% de las tareas de Excel usando atajos.

Notas finales

Excel es posiblemente una de las mejores herramientas jamás creadas y se ha mantenido como el estándar de oro para casi todas las empresas en todo el mundo. Pero ya sea que sea un novato o un usuario avanzado, siempre queda algo por aprender. ¿O crees que lo has visto todo y lo has hecho todo? Háganos saber lo que nos hemos perdido en los comentarios.

¿Quiere empezar en el campo de los datos? ¡Hemos seleccionado el programa de varios cursos perfecto para ti! Revisar la Programa certificado de análisis empresarial y lanza tu carrera!