Estás leyendo la publicación: Técnicas Avanzadas de Auditoría SEO: Excel
Preparar y trabajar con datos requiere un poco de trabajo, pero con un poco de práctica serás un profesional en poco tiempo. Realmente es mucho más fácil de lo que piensas.
Si desea acelerar la preparación de datos sin esfuerzo, descargue el Libro de trabajo completo de auditoría técnica de SEO.
Consejos para limpiar y preparar datos
Uno de los primeros desafíos será limpiar los datos.
En otras palabras, deberá eliminar las filas o columnas vacías que pueden estropear sus datos, etiquetar todas las columnas correctamente y estandarizar los datos (p. ej., podría tener “AZ” en un conjunto de datos, pero “Arizona” en otro conjunto de datos. Deben ser iguales para que Excel o Hojas de cálculo de Google puedan reconocer que son iguales).
La limpieza de datos es una molestia, pero hay algunos trucos que pueden hacerlo más rápido.
Cómo encontrar y eliminar filas vacías
Google Sheets y Excel esencialmente funcionan de la misma manera para eliminar espacios en blanco. Es solo que los comandos se ven un poco diferentes.
En Excel:
- Seleccione todos los datos.
- Haga clic en , seleccione “Vacíos” y haga clic en “Aceptar”.
- Haga clic derecho en una celda seleccionada y seleccione “Eliminar” en el menú desplegable.
- Elija “Toda la fila” y haga clic en “Aceptar”.
En Hojas de cálculo de Google:
- Seleccione los datos.
- Haz clic y desmarca la casilla junto a “Vacíos”.
- Luego, seleccione “Crear un filtro” en la pestaña Datos.
- Haga clic en el ícono de filtro en la fila 1. Luego, .
- Ctrl (o Cmd) + A y eliminar filas.
- Apague el filtro.
Cómo encontrar y eliminar columnas vacías
Los pasos para eliminar columnas vacías son esencialmente los mismos que para eliminar filas vacías. Sin embargo, omita este paso y descubrirá que el análisis de datos de repente implica muchos errores.
En Excel:
- Seleccionar datos.
- Haga clic en y haga clic en “Aceptar”.
- Utilice Ctrl (o Cmd) + barra espaciadora para seleccionar toda la columna y haga clic en Eliminar en el menú contextual.
- Seleccione “Toda la columna” y haga clic en “Aceptar”.
En Hojas de cálculo de Google:
- Seleccione los datos.
- Haga clic en .
- Haga clic en el botón de filtro en la fila 1 y desmarque la casilla junto a “Vacíos”.
- Seleccione las columnas vacías, haga clic con el botón derecho y seleccione “Eliminar columna” en el menú.
Cómo usar Concatenar para combinar dos celdas
Puede combinar texto en una sola celda usando la función CONCATENAR.
Es particularmente útil para combinar nombres y apellidos, fechas u otros datos que se le presentan en varias columnas. Y al encadenarlos, puede organizar y combinar datos en una sola hoja.
En Excel y Google Sheets:
Supongamos que tenemos dos conjuntos de datos en hojas separadas:
La hoja 1 tiene nombre, apellido y correo electrónico.
La hoja 2 tiene nombre, apellido y número de teléfono.
Combinemos los datos de las dos hojas para que sea más fácil trabajar con ellos.
Primero, combinemos el nombre y el apellido en caso de que tengamos más de un John en nuestro conjunto de datos.
1. Inserte una nueva columna en las Hojas 1 y 2 a la derecha de la columna “apellido”.
2. En C2 en ambas hojas, ingrese la fórmula:
=CONCATENAR(A2,” “,B2)
(Esto dice, para la cadena en A2, deje un espacio (” “) y luego agregue el contenido de B2).
3. Seleccione C2, haga clic en la esquina inferior derecha de C2 y arrástrelo hasta la parte inferior de sus datos para copiar la fórmula.
4. Ahora, inserte una nueva columna a la derecha del nombre combinado (C) en la Hoja 1.
5. En la primera fila de la nueva columna, ingrese la siguiente fórmula:
=BUSCARV(Hoja1!C2,Hoja2!$C:$E,2,FALSO)
Esta fórmula buscará lo que esté en C2 en la Hoja 2 (el nombre completo de John Doe) y devolverá el número de teléfono correspondiente a la Hoja 1.
6. Copie la fórmula en la primera fila de la nueva columna en la Hoja 1 y péguela en las filas restantes de la columna como lo hizo con la fórmula concatenada.
Otros comandos de limpieza de datos rápidos y útiles
¿Quiere eliminar el espacio no deseado dentro de una celda? Usar recorte. (Funciona tanto en Excel como en Google Sheets).
Cómo combinar conjuntos de datos en una sola tabla usando hojas de cálculo de Google
Otra tarea común que te encontrarás haciendo cuando trabajes con datos es ponerlos todos en la misma hoja.
Pero una palabra de advertencia: combinar varios archivos de datos puede ser una pesadilla si no prepara sus datos. Por lo tanto, asegúrese de que todos tengan la misma estructura y formato antes de comenzar.
Comencemos con algo fácil y avancemos.
Importar un conjunto de datos completo a otro
Importar datos en una hoja existente. . Luego, cambie el menú desplegable a “Insertar nueva hoja”. Agregará los nuevos datos a continuación.
Utilice la función “CONSULTA”
Supongamos que tiene una hoja con tráfico y tasas de rebote, y desea crear una hoja con tasas de rebote inferiores al 50 % para identificar las páginas con mejor rendimiento.
=CONSULTA(Hoja1!A2:C5, “SELECCIONE A,B,C DONDE C < 0.5”)
Esta fórmula creará un nuevo conjunto de datos que incluye toda la información de cualquier página que cumpla con nuestro requisito de una tasa de rebote del 50 %.
En esta fórmula, Hoja1!A2:C5 son los rangos a través de los cuales mira; SELECCIONA A,B,C le dice qué celdas extraer si la tasa de rebote es inferior a 0,5; y C < 0,5” es la función matemática que le dice que solo copie la información de la otra hoja si el valor en la columna C es menor a 0.5.
Utilice la función “BUSCARV”
La función BUSCARV le permite buscar un valor en un conjunto de datos y devolver un valor correspondiente de otro conjunto de datos. Aquí hay un ejemplo de cómo usarlo:
Dos hojas: una con tasas de rebote (Hoja 2) y otra con números de tráfico (Hoja 1).
=BUSCARV(Hoja1!A2, Hoja2!A:B, 2, FALSO)
Esta fórmula buscará el valor en Sheet1!A2 en la primera columna de Sheet 2 y devolverá el valor correspondiente de la segunda columna de Sheet 2 cuando coincidan.
Los nombres de las páginas deben ser iguales en ambas hojas. Si tiene una “página de venta” en un conjunto de datos y una “página de destino” en el otro, no funcionará.
Las funciones “ÍNDICE” y “COINCIDIR”
=ÍNDICE(Hoja2!B:B, COINCIDIR(Hoja1!A2, Hoja2!A:A, 0))
Esta fórmula buscará el valor en Sheet1!A2 en la primera columna de Sheet 2 y devolverá el valor correspondiente de la segunda columna de Sheet 2.
Importar funciones en la barra de fórmulas
IMPORTRANGE puede hacer el trabajo desde la barra de fórmulas.
=IMPORTRANGE(“spreadsheet_url”, “range_string”)
Copie la URL del otro conjunto de datos. (Todo antes del signo #).
Luego, dígale en qué rango copiar. (Se actualizará automáticamente a medida que se realicen cambios en la otra hoja).
Te dará error, así que solo tienes que darle acceso. ¿Otros tipos de importación con los que puedes jugar?
Importar a través de fuente RSS:
=IMPORTFEED(https://example.com/rss, “título de los artículos”)
Importar a través de XML:
=IMPORTXML(“https://ejemplo.com/”, “xpath_query”)
Importar a través de un archivo de datos estructurados en línea (como un CSV):
=IMPORTARDATOS(“https://ejemplo.com/archivo.csv”)
Importar desde una tabla en una página web:
=IMPORTARHTML(“https://ejemplo.com/slug”,”tabla”,1)
(Donde “tabla” es la consulta y “1” es la ubicación de inicio del índice).
Soluciones sin fórmula
Los complementos de Hojas de cálculo de Google también pueden ser muy útiles si desea evitar las fórmulas. Sin embargo, te animo a que pruebes las fórmulas. Son flexibles. (Y te hacen sentir un poco inteligente y poderoso cuando funcionan).
Cómo combinar conjuntos de datos en una sola tabla usando Excel
Importación sencilla
Elija el conjunto de datos. Luego, dile si es un archivo. Dígale cómo se delinea el archivo.
Finalmente, dígale si desea agregarlo a una hoja existente (y dónde), o si debe crear una nueva hoja.
Utilice Power Query de Excel
Si le encantó la obtención de consultas en Hojas de cálculo de Google, le encantará Power Query en Excel. Fuera de Python, R y aprender un lenguaje de codificación completo, es la característica más útil y poderosa que puede usar para trabajar con grandes conjuntos de datos.
También encontrará excelente documentación y muchos videos (con datos que puede descargar para seguir) para ayudarlo a aprender a usarlo. Los informes de datos mensuales son una práctica excelente.
¿Comenzando con un nuevo archivo de Excel? Importe sus archivos de datos directamente en él.
Seleccione el tipo de archivo para importar o agregar a través de una URL y siga las indicaciones para importar el archivo a Excel.
Continúe importando todos los archivos de datos a Excel.
Ahora, necesita combinar los archivos de datos.
Seleccione los que desee. También puede agregar una columna personalizada que identifique de qué archivo provienen los datos.
BUSCARV en Excel
La función “BUSCARV” funciona igual que BUSCARV en Hojas de cálculo de Google. Aquí hay un ejemplo:
=BUSCARV(A2, Hoja1!A:B, 2, FALSO)
Esta fórmula buscará el valor en A2 en la primera columna de la Hoja 2 y devolverá el valor correspondiente de la segunda columna de la Hoja 2.
Use “ÍNDICE” y “COINCIDIR”
INDEX y MATCH también hacen magia en Excel.
=ÍNDICE(Hoja2!B5:C8,COINCIDIR(Hoja1!A2,Hoja2!A5:A8,0)COINCIDIR(D1,Hoja2!A4:C4,0))
Esta fórmula funciona al buscar el rango en la Hoja 2 (en azul) al verificar la primera columna (A5: A8) para cualquier valor que esté en A2 para una coincidencia exacta (0). Este es todo el texto en rojo.
El texto verde es la información de la fila. Comprueba la primera fila (A4:C4) para cualquier valor que encuentre en D1, buscando una coincidencia exacta (0).
¿Solo quiere que se copie el valor si el valor es una cierta cantidad? 1 = menor que y -1 = mayor que.
Si eso no parece tan emocionante, solo espera hasta que veas lo que puedes hacer con esto a continuación.
Trabajar con datos
Búsqueda de valores utilizando dos variables personalizadas
A veces, solo desea poder buscar un valor específico.
En este ejemplo, tenemos números de tráfico mensual en las columnas y páginas en las filas. Ahora, podemos usar INDEX y MATCH para buscar cuánto tráfico recibió la página de contacto (o cualquier página) en el mes de enero (o cualquier mes) simplemente escribiendo G2 y G3.
La fórmula que pones en G4:
=ÍNDICE(B2:C5,COINCIDIR(G3,A2:A5,0),COINCIDIR(G2,B1:C1,0))
ÍNDICE necesita saber en qué rango debe buscar. Luego, quiere saber qué columna y fila necesita encontrar. Las funciones COINCIDIR actúan como los números de fila y columna al buscar coincidencias exactas de acuerdo con lo que escribió en G2 y G3.
¡Eso significa que puede mover esa pequeña sección de escritura a una hoja limpia y configurar un tablero para que cualquier miembro de su equipo pueda buscar datos!
Si te encantó esto, pruébalo con SUMIF y AVERAGEIF, que suman (o promedian) los datos en función de varios criterios.
Esto es perfecto para resumir rápidamente sus datos de Google Analytics en función de criterios específicos, como la fuente de tráfico, el medio y el intervalo de fechas.
Crear tablas dinámicas
Las tablas dinámicas son probablemente una de las herramientas más importantes que tendrá cuando intente resumir grandes cantidades de datos y transformarlos en un formato más manejable.
Úselo para acelerar sus informes al:
- Resumir datos de tráfico por producto, categoría o período de tiempo.
- Analizar tráfico a lo largo del tiempo para identificar tendencias estacionales.
- Comparar datos en diferentes categorías, como tasas de rebote para las páginas de destino de diferentes productos o regiones.
- Filtrar data para mostrar solo los datos de un producto o región específicos.
- Calcular métricas como promedios, conteos y porcentajes. Por ejemplo, el tráfico promedio por día para cada producto durante los últimos 30 días.
- Identificar valores atípicos, como páginas o productos que se venden mucho más o mucho menos que otros productos.
En Hojas de cálculo de Google:
Resalte los datos que desea analizar.
Para hacer la tabla: para abrir una nueva hoja con una tabla dinámica en blanco.
Ahora, configure su tabla dinámica.
En los campos “Filas” y “Columnas”, seleccione los campos que desea utilizar como filas y columnas. En el campo “Valores”, seleccione el campo que desea analizar.
¿Necesitas algo un poco diferente? Personalice su tabla dinámica cambiando la función de resumen (suma, promedio, recuento, por ejemplo), ordene y filtre los datos y agregue subtotales y totales generales.
Consejo profesional: ¿Tiene un gran conjunto de datos? Seleccione toda la tabla haciendo clic en la esquina superior izquierda de la tabla.
En Excel:
Al igual que en las hojas, comience resaltando los datos que desea analizar. Para un conjunto de datos grande, hacer clic en la esquina superior izquierda de la tabla también funciona aquí.
el rango donde se encuentran sus datos que desea analizar. Luego, si desea la tabla dinámica en una hoja nueva o en la existente.
(Si accidentalmente seleccionó un rango vacío, le dará un error. Entonces, si se queja, generalmente es por eso).
Ahora, configure su tabla dinámica. Agregue lo que quiera en las columnas en el campo “Valores” y las filas en el campo “Filas”.
Y si necesita cambiarlo (por ejemplo, está usando promedio en lugar de suma), cámbielo usando el botón de información en el valor apropiado.
Use REGEXTRACT para extraer información de las URL
La fórmula Regexextract extrae datos específicos de cadenas de texto usando expresiones regulares. Entonces, por ejemplo, puede usar Regexextract para extraer la fuente y el medio del tráfico de su sitio web de los informes de Google Analytics.
Para extraer el dominio de una URL en la celda A2, puede usar la siguiente fórmula:
=REGEXEXTRACT(A2,”^(?:https?://)?(?:[^@\n]+@)?(?:www.)?([^:/\n]+)”)
Las expresiones regulares pueden ser complejas, por lo que es importante probar su fórmula con diferentes ejemplos para asegurarse de que está extrayendo los datos correctos.
Minimizar tablas
Para minimizar una tabla, haga clic en el ícono de la flecha pequeña en la esquina superior izquierda de la tabla para contraerla y ocultar los datos, dejando solo el encabezado de la tabla visible.
Para volver a expandir la tabla, simplemente haga clic en el icono de la flecha.
También puede usar la función para agrupar filas o columnas relacionadas y crear secciones contraíbles.
Congelación de filas y columnas
Al congelar filas y columnas, se mantienen visibles filas o columnas específicas en la pantalla mientras se desplaza por el resto de la hoja de cálculo.
En Hojas de cálculo de Google: Seleccione la fila o columna que desea congelar. Haga clic (o hasta e incluyendo la fila/columna actual).
En Excel: Seleccione la fila o columna que desea congelar. Luego, haga clic en
Si realmente tiene problemas con una gran cantidad de datos, pruebe los filtros. Esto le permite eliminar cualquier dato irrelevante y mostrar solo los datos que cumplen con ciertos criterios.
Consejos adicionales para grandes conjuntos de datos
Usar rangos con nombre para asignar un nombre específico a un rango de celdas en su hoja. Esto puede facilitar la consulta y el análisis de grandes conjuntos de datos.
Evite usar demasiadas fórmulas. Estos ralentizan el rendimiento de su hoja y también pueden hacerle la vida muy difícil, particularmente una vez que comienza a encadenar varias fórmulas juntas.
Hacer uso de formato condicional:
En Hojas de cálculo de Google:
Seleccione el rango de celdas al que desea aplicar el formato condicional.
En el panel “Reglas de formato condicional” en el lado derecho de la pantalla, seleccione el tipo de formato que desea aplicar. Hay varias opciones para elegir, que incluyen “Escala de colores”, “Conjunto de iconos” y “Fórmula personalizada”.
En Excel:
Seleccione el rango de celdas al que desea aplicar el formato condicional y haga clic en
En el menú desplegable, seleccione el tipo de formato que desea aplicar, incluidas “Reglas de celdas resaltadas”, “Reglas superior/inferior” y “Barras de datos”.
Conclusión
Excel y Google Sheets pueden parecer demasiado simplistas para hacer mucho, o pueden parecer demasiado complicados de aprender, pero ninguna de esas suposiciones es cierta.
Con algunas fórmulas y trucos bajo la manga, descubrirá rápidamente que se encuentran entre las herramientas más poderosas e importantes en su caja de herramientas de SEO.
¿Listo para convertirte en un profesional de los datos? Descargue el Libro de trabajo completo de auditoría técnica de SEO y domine el arte de preparar y trabajar con datos.
