Analítica web
Reflexiones desde el mercado español de Analítica Web

Excel aplicado a la analítica web

Se lee en 5 minutos

Para comenzar este año, vamos a recuperar un tema tan tradicional como esencial en la vida del analista digital: el Excel. 

Esta herramienta, que facilita la vida a más de uno, ha sido noticia recientemente debido a que algunos directivos consideran que los empleados deben estar menos tiempo sacando datos y centrarse más en su análisis. La importancia de tener automatizado la mayor parte de informes es una forma de ahorrar tiempo y para ello podéis encontrar información en este post sobre algunas herramientas para automatizarlos. En esta ocasión, queremos enseñaros algunas fórmulas aplicables tanto en Google Spreedsheet como en Excel y casos prácticos donde podréis aplicarlas en vuestro día a día.

TODAY (HOY)

Esta fórmula es imprescindible cuando se automatiza un informe que realiza una consulta diaria, ya que muestra el valor del día de hoy. Se puede conseguir el día de ayer o de días anteriores restándoselo al día de hoy, es decir: =TODAY()-1

Fórmula Excel: Hoy

Esto es aplicable también para las fórmulas week o month, la única diferencia respecto a la formula de today es que hay que indicar la fecha de la que se quiere conocer a qué semana o mes pertenece.

UNIQUE

Es una fórmula que solamente está disponible en Google Spreedsheet. Nos sirve para ver los valores únicos de un listado.

¿Cuántas URLs puede tener un site? Si realizas una consulta diaria con esta herramienta, puedes tener miles de URLs, y si se quiere crear una tabla que recoja las URLs y el día, esta fórmula os ayudará a ello. Se selecciona el rango que se quiere comprobar y de esta forma obtendremos las URLs necesarias:

=UNIQUE(A:A)

SPLIT

En Google Analytics se recogen URLs que contienen parámetros, y eso produce que muchas veces no se pueda saber, por ejemplo, el número de páginas vistas totales de una página vista. Por ello, con esta fórmula se podrá limpiar la URL, siempre y cuando tengan un carácter que coincida para poder sacarlo.

Tras esto tendríamos un conjunto de URLs. Entonces, ¿cómo podríamos saber cuántas páginas vistas tiene en total esa página? De forma muy sencilla:

=SPLIT(A2;“/”;VERDADERO)

En algunos casos, es recomendable en Google Spreedsehet duplicar la pestaña debido a que, al lanzar la consulta, la fórmula desaparece al sobreescribirse. Por eso, cuando se trata de un documento que se actualiza de forma automática, se duplicaría la pestaña, se enlazarían las celdas y se añadiría una columna donde se insertaría la fórmula quedando algo así:

Fórmula Excel: Split

La columna A y B estarían enlazadas a la página que muestra los resultados de la consulta. La columna C muestra los valores que hay previamente antes del parámetro que separa los valores que serán útiles para la extracción de la URL. En este caso, el parámetro es =, por lo que se muestran únicamente los valores /?s en este ejemplo. Finalmente, en la columna D se ven los valores que hay a continuación de dicho parámetro. De esta forma, al crear una tabla con los valores de la columna D, estarán “limpios” del resto de parámetros que no queremos mostrar.

EXTRAE

Esto ayudaría a sacar de una URL la parte que Google Analytics registra en la dimensión de página. Por ejemplo, seleccionamos la URL de la que queremos extraer la parte que necesitamos. Tras ello, indicamos la posición del carácter a partir del cual queremos extraer valores. Y por último, recogeríamos el número de caracteres que recogeríamos (conviene poner un número elevado para que no se queden parámetros que contenga la URL fuera de la extracción).

Fórmula Excel: Extrae

SUMIF (SUMAR.SI)

La respuesta a la pregunta anterior la tenéis en esta fórmula. Indicamos primero el rango que contiene todas las posibilidades de los valores, después la condición que tiene que coincidir y, por último, seleccionamos el rango con los datos que se tienen que sumar.

SUMIFS (SUMAR.SI.CONJUNTO)

Para sumar los valores se tienen que cumplir dos o más condiciones. Hay que seleccionar primero el rango de valores para sumar, después el rango con los posibles valores, a continuación seleccionar la condición, y, finalmente, se sigue el mismo proceso tantas veces como condiciones se tenga.

A continuación, os dejamos un ejemplo de esta fórmula:

=SUMAR.SI.CONJUNTO(A:A;B:B;Hoja2!A1;C:C;Hoja2!A1)

“A:A” quiere indicar que el rango es toda la columna A. Aparece Hoja2! porque la condición está en otra hoja. Normalmente, cuando se realiza una consulta, están en hoja los valores y en otra hoja las condiciones cuando se quiere montar los documentos.

BUSCARV

Es una de las funciones más importantes y más útiles. Sirve para ver un valor que se encuentre en una gran cantidad de datos.

Funciona de la siguiente manera: primero, se indica el valor que se desea buscar y, a continuación, el rango donde se tiene que buscar el valor. Dentro de ese rango hay que indicar el número de la columna donde se encuentra el valor que se necesita y, por último, hay que indicar si tiene que ser exactamente ese valor o uno aproximado, indicando Falso/0 o Verdadero/1 respectivamente (uno de los dos valores o Verdadero o 1 nunca los dos).

=BUSCARV(A1, Hoja1!A:C;2, FALSO)

IF.ERROR (SI.ERROR)

Cuando se realizan cálculos entre fórmulas, pueden aparecer errores del tipo que aparecen en la celda #DIV/0!.

Para que no quede el valor así, se aplica está fórmula de tal manera que aparecerá el valor que deseemos (tiene que ser un valor numérico).

Fórmula Excel: Si.error

RIGHT (DERECHA) o LEFT (IZQUIERDA)

Con estas fórmulas se recogen los valores. Empezando por la izquierda o por la derecha, se selecciona la celda y el número de caracteres que se quieren recoger.

CONCATENATE (CONCATENAR)

Con esta fórmula lo que se consigue es concatenar en una misma celda dos textos distintos que están en celdas separadas.

FIJAR CELDAS

Por último, y no menos importante, a la hora de fijar celdas para construir tablas y arrastrar de tal forma para que no se desmonten, hay que añadir el símbolo $ antes de la letra para fijar columna, o antes del número para fijar la fila o para fijar la celda delante tanto de la letra como del número:

Fijar columna: =$A1: si arrastramos hacia abajo, solamente cambiará el número. Si arrastramos de forma lateral, no cambiara el valor

Fijar fila: =A$1: si arrastramos hacia abajo no cambiará el número. Si arrastramos de forma lateral, cambiará el valor numérico.

Fijar celda: =$A$1: si arrastramos hacia abajo no cambiará el número. Si arrastramos de forma lateral, tampoco cambiará el valor numérico.

Podéis encontrar más información en el soporte de office.

Hay muchas fórmulas más de Excel que pueden ser aplicables a la analítica, pero estas son las más utilizadas en el día a día.

¿Echáis en falta alguna fórmula más?, ¿necesitáis ayuda con alguna? Podéis dejar vuestro comentario y os la resolveremos.

Escribe tu comentario

siete − dos =

Navegar