Preparar y trabajar con datos requiere algo de trabajo, pero con un poco de práctica serás un viejo profesional en muy poco tiempo. Realmente es mucho más fácil de lo que crees.
Si desea agilizar su preparación de datos sin esfuerzo, descargue el Libro de trabajo completo de auditoría técnica de SEO.
Sugerencias para limpiar y preparar datos
Uno de los primeros desafíos será la limpieza de datos.
En otras palabras, deberá eliminar las filas o columnas vacías que podrían estar generando sus datos, etiquetar todas las columnas correctamente y estandarizar sus datos (por ejemplo, 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 reconocerlos como iguales).
La eliminación de datos es una molestia, pero existen algunos trucos que pueden hacerlo más rápido.
Cómo encontrar y eliminar líneas vacías
Google Sheets y Excel funcionan esencialmente de la misma manera para eliminar espacios en blanco. Es solo que los controles se sienten un poco diferentes.
En Excel:
- Seleccione todos los datos.
- Hacer clic Inicio > Buscar y seleccionar > Ir a especialseleccione «Bloques» y haga clic en «Aceptar».
- Haz clic con el botón derecho en una celda seleccionada y selecciona «Eliminar» en el menú desplegable.
- Elija «Toda la fila» y haga clic en «Aceptar».
En Hojas de cálculo de Google:
- Seleccionar fechas.
- Hacer clic Datos > Filtrar y desmarque la casilla junto a «Vacíos».
- Después de eso, seleccione «Crear un filtro» en la pestaña Datos.
- Haga clic en el icono de filtro en la fila 1. Luego, Eliminar > Espacios libres.
- Ctrl (o Cmd) + A y elimina las líneas.
- apaga el filtro (pestaña Datos > Desactivar filtro).
Cómo encontrar y eliminar columnas vacías
Los pasos para eliminar columnas en blanco son esencialmente los mismos que para eliminar filas en blanco. Sin embargo, omita este paso y descubrirá que el análisis de datos de repente genera muchos errores.
En Excel:
- Seleccionar fechas.
- Hacer clic Inicio > Buscar y seleccionar > Sé especial > Tragamonedas gratisy 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 «Todas las columnas» y haga clic en «Aceptar».
En Hojas de cálculo de Google:
- Seleccionar fechas.
- Hacer clic Datos > Filtrar.
- Haga clic en el botón de filtro en la fila 1 y desmarque la casilla junto a «Balks».
- 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 especialmente útil para combinar nombres y apellidos, fechas u otros datos presentados en varias columnas. Y al concatenarlos, 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 el nombre, nombre y número de teléfono.
Combinamos los datos de las dos hojas para que sea más fácil trabajar con los datos.
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 «nombre».
2. En C2 en ambas hojas, ingrese la fórmula:
=CONCATENATE(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 los 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:
=VLOOKUP(Sheet1!C2,Sheet2!$C:$E,2,FALSE)
Esta fórmula buscará todo en C2 en la Hoja 2 (el nombre completo de John Doe) y devolverá el número de teléfono correspondiente en 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 de concatenación.
Otros comandos de limpieza de datos rápidos y útiles
¿Quiere eliminar el espacio no deseado dentro de una celda? Usar Cortar. (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 realizará cuando trabaje 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. Así que 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. Archivo > Importar > Seleccionar conjunto de datos para importar > Insertar. Después de eso, cambie el menú desplegable a «Insertar nueva hoja». Agregará los nuevos datos a continuación.
Utilice la función «CONSULTA».
Supongamos que tiene una tasa de rebote y una hoja de tráfico, y desea crear una hoja de tasa de rebote por debajo del 50 % para identificar las páginas con mejor rendimiento.
=QUERY(Sheet1!A2:C5, “SELECT A,B,C WHERE 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 tasa de rebote del 50 %.
En esta fórmula, Hoja1!A2:C5 son los intervalos a través de los cuales miran; SELECCIONA A,B,C le dice qué celdas disparar si la tasa de rechazo es inferior a 0,5; Y C<0,5" es la función matemática que le dice que copie la información de la otra hoja solo 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 coincidente de otro conjunto de datos. Aquí hay un ejemplo de uso:
Dos hojas: una con tasas de rebote (pestaña 2) y otra con números de tráfico (pestaña 1).
=VLOOKUP(Sheet1!A2, Sheet2!A:B, 2, FALSE)
Esta fórmula buscará el valor en Sheet1!A2 en la primera columna de Sheet 2 y devolverá el valor coincidente en 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 ventas» en un conjunto de datos y una «página de destino» en el otro, no funcionará.
Funciones «ÍNDICE» y «COINCIDIR».
=INDEX(Sheet2!B:B, MATCH(Sheet1!A2, Sheet2!A:A, 0))
Esta fórmula buscará el valor en Sheet1!A2 en la primera columna de Sheet 2 y devolverá el valor correspondiente en 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 qué rango copiar. (Se actualizará automáticamente a medida que se realicen cambios en el otro 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, “items title”)
Importar a través de XML:
=IMPORTXML(“https://example.com/”, “xpath_query”)
Importar a través de un archivo de datos estructurados en línea (como un CSV):
=IMPORTDATA(“https://example.com/file.csv”)
Importar desde una tabla en una página web:
=IMPORTHTML(“https://example.com/slug”,”table”,1)
(Donde «tabla» es la consulta y «1» es la posición inicial del índice).
Soluciones sin formula
Los complementos de Google Sheets 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 de alguna manera inteligente y poderoso cuando funcionan).
Cómo combinar conjuntos de datos en una tabla usando Excel
Importación sencilla
Elegir Archivo > Importar > Seleccionar el conjunto de datos Entonces dile si es REMOTO o un ancho fijo archivo. > Dile cómo está delimitado el archivo. (tarjetas, dos puntos, etc.). > Seleccione el formato de los datos (texto, datos, etc.). > Terminar.
Finalmente, dígales si desea agregarlo a una hoja existente (y dónde) o si necesitan crear una nueva hoja.
Usar Power Query desde Excel
Si te gustaron las consultas en Hojas de cálculo de Google, te encantará Power Query en Excel. Además 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 verlos) que lo ayudarán a aprender a usarlo. La presentación de informes de datos mensuales es una práctica excelente.
¿Comenzando con un nuevo archivo de Excel? Importe sus archivos de datos directamente en él.
Datos > Obtener y transformar datos > Obtener datos. 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, tienes que combinar los archivos de datos.
Datos > Obtener y transformar datos > Combinar consultas > Agregar consulta.
Seleccione los que desee. También puede agregar una columna personalizada que identifique de qué archivo provienen los datos.
V BUSCAR en Excel
La función «BUSCAR» funciona igual que BUSCARV en Hojas de cálculo de Google. Aquí hay un ejemplo:
=VLOOKUP(A2, Sheet1!A:B, 2, FALSE)
Esta fórmula buscará el valor en A2 en la primera columna de la Hoja 2 y devolverá el valor correspondiente en la segunda columna de la Hoja 2.
Usa «ÍNDICE» y «COINCIDIR»
INDEX y MATCH también hacen magia en Excel.
=INDEX(Sheet2!B5:C8,MATCH(Sheet1!A2,Sheet2!A5:A8,0)MATCH(D1,Sheet2!A4:C4,0))
Esta fórmula funciona buscando el rango en la hoja 2 (en azul) comprobando la primera columna (A5:A8) para cualquier valor que esté en A2 para una coincidencia exacta (0). Todo esto es texto en rojo.
El texto verde es información de línea. Verifique la primera fila (A4: C4) para cualquier valor que encuentre en D1, buscando una coincidencia exacta (0).
¿Desea que el valor se copie solo si el valor es una cierta cantidad? 1 = menor que y -1 = mayor que.
Si eso no suena tan emocionante, espere hasta que vea lo que puede hacer a continuación.
Trabajar con datos
Encontrar valores usando dos variables personalizadas
A veces, solo desea poder buscar un valor específico.
En este ejemplo, tenemos números de tráfico mensual en columnas y páginas en filas. Ahora podemos usar INDEX y MATCH para buscar cuánto tráfico recibió la página de contacto (o cualquier página) en enero (o cualquier mes) simplemente escribiendo G2 y G3.
La fórmula que ingresó en G4:
=INDEX(B2:C5,MATCH(G3,A2:A5,0),MATCH(G2,B1:C1,0))
INDEX necesita saber qué rango debe buscar. A continuación, quiere saber qué columna y fila necesita encontrar. Las funciones COINCIDIR actúan como números de fila y columna, buscando coincidencias exactas en función de lo que ingresó en G2 y G3.
¡Esto 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 los datos!
Si te gusta, pruébalo con SUMIF y AVERAGEIFs, que suman (o median) datos en función de varios criterios.
Esto es perfecto para resumir rápidamente los datos de Google Analytics en función de criterios específicos como la fuente de tráfico, el entorno 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 busque resumir grandes cantidades de datos y transformarlos en un formato más manejable.
Úselo para acelerar sus informes:
- 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.
- Comparación datos de diferentes categorías, como tasas de rebote para páginas de destino para diferentes productos o regiones.
- Filtrar datos para ver solo los datos de un producto o región específicos.
- calcular valores como promedios, conteos y porcentajes. Por ejemplo, el tráfico diario promedio de cada producto durante los últimos 30 días.
- Identificar valores atípicos, como páginas o productos que venden mucho más o mucho menos que otros productos.
En Hojas de cálculo de Google:
Resalte los datos que desea analizar.
Para preparar la comida: Datos > Tabla dinámica para abrir una hoja nueva con una tabla dinámica vacía.
Ahora, configure la 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 la 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 Hojas de cálculo, 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í.
Insertar > Tabla dinámica > Insertar el rango en el que se encuentran los datos que desea analizar. Entonces, si desea la tabla dinámica en una hoja nueva o existente.
(Si accidentalmente seleccionó un rango vacío, le dará un error. Entonces, si se queja, generalmente es por eso).
Ahora, configure la tabla dinámica. Agrega lo que quieras en las columnas del campo «Valores» y en las filas del campo «Filas».
Y si necesita cambiarlo (por ejemplo, usa la media en lugar de la suma), cámbielo usando el botón de información del valor correspondiente.
Use REGEXTRACT para extraer información de las URL
La fórmula Regextract extrae datos específicos de cadenas de texto usando expresiones regulares. Entonces, por ejemplo, puede usar Regextract para extraer la fuente y el fondo 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 varios ejemplos para asegurarse de que obtenga los datos correctos.
Minimizar tablas
para reducir a icono de una tabla, haga clic en el icono de flecha pequeña en la esquina superior izquierda de la tabla para colapsarla y ocultar los datos, dejando solo el encabezado de la tabla visible.
Para volver a expandir la tabla, haga clic en el icono de flecha.
También puedes usar «Datos» > «Filas de grupo» o «Columnas de grupo» función para agrupar filas o columnas relacionadas y crear secciones contraíbles.
Congelar filas y columnas
Al congelar filas y columnas, ciertas filas o columnas se mantienen visibles 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. Hacer clic Ver > Inmovilizar > A la fila o columna actual (o hasta e incluyendo la fila/columna actual).
En Excel: Seleccione la fila o columna que desea congelar. Entonces presione Ver > Congelar paneles Congelar la fila superior o «Congelar primera columna».
Si realmente está tratando con una gran cantidad de datos, pruebe los filtros. Esto le permite eliminar todos los datos irrelevantes y mostrar solo los datos que cumplen con ciertos criterios.
Más consejos para grandes conjuntos de datos
Usar rangos llamados para asignar un nombre específico a un rango de celdas en la hoja, lo que facilita la búsqueda y el análisis de grandes conjuntos de datos.
Evite usar demasiadas fórmulas. Disminuyen el rendimiento de las hojas e incluso pueden complicarle la vida, especialmente cuando comienza a encadenar varias fórmulas.
Usar formato condicional:
En Hojas de cálculo de Google:
Seleccione el rango de celdas al que desea aplicar el formato condicional. Haga clic en Formato > 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 Inicio > Estilos > Formato condicional.
En el menú desplegable, seleccione el tipo de formato que desea aplicar, incluidas «Reglas de resaltado de celdas», «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 algunos trucos bajo la manga, descubrirá rápidamente que estas se encuentran entre las herramientas más poderosas e importantes en su caja de herramientas de SEO.
¿Estás listo para convertirte en un profesional de los datos? Descargue el registro completo de auditoría técnica de SEO y domine el arte de preparar y trabajar con datos.
Imagen destacada: Paulo Bobita/Search Engine Journal
Imágenes de publicación: captura de pantalla tomada por el autor de Excel/Google Sheets, mayo de 2023
Fuente: searchenginejournal
Hashtags: #Técnicas #Avanzadas #Auditoría #SEO #Excel
Comentarios recientes