Las 7 fórmulas de hojas de cálculo de Google más útiles

Últimamente, la mejor parte de mi día ha sido descubrir las cosas nuevas y geniales que puedo hacer en Hojas de cálculo de Google, lo que, sí, definitivamente significa que necesito salir más, pero también significa que puedo compartir mis fórmulas favoritas con ustedes.

Cómo usar fórmulas para hojas de cálculo de Google

Haga doble clic en la celda en la que desea ingresar la fórmula. (Si desea la fórmula para toda la fila, probablemente sea la primera o la segunda fila de una columna).
Escriba el signo igual (=).
Introduzca su fórmula. Según los datos, Google Sheets puede sugerirle una fórmula y/o un rango.

V-LOOKUP Fórmula de hojas de Google

Las búsquedas en V son, con diferencia, la fórmula más útil de su conjunto de herramientas cuando trabaja con grandes cantidades de datos. La fórmula de búsqueda en V busca un punto de datos, como, por ejemplo, el título de una publicación de blog o una URL, en una hoja y devuelve información relevante para ese punto de datos, como vistas mensuales o tasa de conversión en otra hoja. .

Por ejemplo, si quiero ver cuánto tráfico obtuvo un conjunto específico de publicaciones de blog, exportaré una lista de Google Analytics, luego colocaré esa lista en otra pestaña y usaré la función BUSCAR-V para obtener vistas por URL en el primera pestaña.

La única advertencia: el punto de datos debe existir en ambas celdas, y debe estar en la primera columna de la segunda hoja.

Fórmula:

=BUSCARV(criterio_de_búsqueda, matriz, índice, orden_de_clasificación)

Veamos un ejemplo, que debería hacer esto un poco más fácil de entender.

En la primera hoja, tengo una lista de publicaciones de blog, incluidos sus títulos, URL y tráfico mensual. En la segunda hoja, tengo un informe de Google Analytics con el tiempo promedio de carga de la página por URL. Quiero ver si hay alguna correlación entre la velocidad de la página y el rendimiento.

Un ejemplo:

=BUSCARV(A2,’Tiempo de carga promedio de GA»!$1:$1000,2,FALSO)

The 7 Most Useful Google Sheets Formulas 3

IFERROR Fórmula de Hojas de cálculo de Google

Cada vez que usa una fórmula en la que más del 10% de los valores devueltos generan errores, su hoja de cálculo comienza a verse realmente desordenada (¡vea la captura de pantalla anterior!).

Para darte una idea, tal vez tengas dos columnas: una para páginas vistas y otra para clics de CTA. Desea ver las páginas con mayor conversión, por lo que crea una tercera columna para las vistas de página divididas por los clics de CTA (o = B2/C2).

Sin embargo, alrededor de un tercio de sus páginas no tienen llamadas a la acción, por lo que no han recibido ningún clic. ¡Esto aparecerá como #VALOR! en tu hoja, ya que no puedes dividir por cero.

¡Usar la fórmula IFERROR le permite reemplazar el VALOR! Estado con otro valor. Normalmente uso un espacio (» «) para que la hoja esté lo más limpia posible.

Aquí está la fórmula:

=SI.ERROR(fórmula_original, valor_si_error)

Entonces, para la situación anterior, mi fórmula sería:

=SI.ERROR((B2/C2, “ “)

Fórmula COUNTIF Google Sheets

La fórmula COUNTIF le dice cuántas celdas en un rango determinado cumplen con los criterios que ha especificado. Con esto bajo la manga, nunca más tendrá que contar celdas manualmente.

Fórmula:

=CONTAR.SI(rango, criterio)

Digamos que tengo curiosidad por saber cuántas publicaciones de blog recibieron más de 1000 visitas durante este período de tiempo. Ingresaría:

=CONTAR.SI(C2:C500,“>1000”)

O tal vez quiero ver cuántas publicaciones de blog escribió Caroline Forsey. Si el autor estuviera en la Columna D, mi fórmula sería:

=CONTAR.SI(D2:D500, “Caroline Forsey”)

LEN Función de hojas de Google

¿Ha notado que Google Analytics elimina el «o» de cada URL? Esto me planteó un problema importante cuando quería combinar datos de HubSpot y GA: la función V-Lookup no funcionaba porque las URL no eran idénticas («frente a» blog.hubspot.com/marketing).

Afortunadamente, no hay necesidad de cambiar manualmente cada URL. La función LEN le permite adaptar la longitud de cualquier cadena.

=IZQUIERDA(texto,LARGO(texto)-n)

=DERECHA(texto,LARGO(texto)-n)

Entonces, digamos que la URL completa está en la columna I. Para eliminar la cadena » y hacerla idéntica a la URL en la pestaña de Google Analytics, usaría:

=DERECHA(I2, LONGITUD(I2)-8)

Si quisiera eliminar los últimos caracteres de una celda, simplemente cambiaría DERECHO a IZQUIERDO.

The 7 Most Useful Google Sheets Formulas 1

Fórmula de matriz para hojas de cálculo de Google

Rara vez necesita aplicar una fórmula a una sola celda; por lo general, la usa en una fila o columna. Si copia y pega una fórmula en una nueva celda, Google Sheets la cambiará automáticamente o hará referencia a las celdas correctas; por ejemplo, si ingreso =A2+B2 en la celda C2, luego arrastro la fórmula a C3, la fórmula se convertirá en =A3+B3.

Pero hay algunos inconvenientes en esto. Primero, si está trabajando con una gran cantidad de datos, tener cientos o miles de fórmulas puede hacer que Google Sheets sea mucho más lento. En segundo lugar, si cambia la fórmula, tal vez ahora quiera ver =A2*B2 en su lugar, debe realizar ese cambio en todas las fórmulas. Nuevamente, eso lleva mucho tiempo y requiere mucha potencia de procesamiento. Y, por último, la fórmula no se aplica automáticamente a filas o columnas nuevas.

Una fórmula matricial resuelve estos problemas. Es una fórmula, con un cálculo, pero los resultados se ordenan en varias filas o columnas. Esto no solo es más eficiente, sino que cualquier cambio se aplicará automáticamente a todos sus datos.

ARRAYFORMULA(matriz_fórmula)

The 7 Most Useful Google Sheets Formulas 4

Supongamos que quiero ver cuánto tráfico no pagado obtuvimos en marzo y abril. Eso requiere restar el tráfico pagado del total (columna D de la columna C) y luego sumar los totales. Dos fórmulas separadas.

O podría usar una fórmula matricial:

=FÓRMULA DE ARRAY(SUMA(C2:C5-D2:D5)

La segunda parte, SUM(C2:C5-D2:D5), debería parecer algo familiar. Es una fórmula de suma tradicional, pero se aplica a un rango (celdas C2 a C5 y D2 a D5) en lugar de celdas individuales.

La primera parte, =ARRAYFORMULA, le dice a Google Sheets que estamos aplicando esta fórmula a un rango.

También podría usar una fórmula de matriz para observar el tráfico no pagado específicamente de actualizaciones (no contenido nuevo) en marzo y abril.

Así es como se vería:

=FÓRMULA DE ARRAY(SUMA(C2,C4-D2,D4))

IMPORTRANGE Fórmula de Hojas de cálculo de Google

Solía pasar mucho tiempo (y poder de procesamiento) copiando manualmente grandes cantidades de datos de una hoja de cálculo a otra. Luego aprendí sobre esta práctica fórmula, que importa datos de una hoja de cálculo separada de Hojas de cálculo de Google.

Supongamos que nuestro experto residente en optimización histórica Braden Becker me envió una hoja de cálculo del contenido que actualizó el mes pasado. Quiero agregar esos datos a una hoja de cálculo maestra de todo el contenido (tanto nuevo como históricamente optimizado) que publicamos. Yo usaría esta fórmula:

IMPORTRANGE(hoja_de_cálculo_url, rango_cadena)

Que se vería como:

IMPORTRANGE(» «Actualizar Rendimiento!A2:D100»)

Cómo dividir texto en hojas de cálculo de Google

Dividir texto puede ser increíblemente útil cuando se trata de diferentes versiones de las mismas URL.

Para darle una idea, supongamos que he creado una hoja de cálculo con cada URL que recibió al menos 300 visitas en enero y febrero. Quiero comparar los dos meses para ver qué publicaciones de blog obtuvieron más vistas con el tiempo, menos o casi lo mismo.

El problema es que, si realizo una V-LOOKUP entre las dos pestañas, Google Sheets no las reconocerá como las mismas URL:

(URL normal)

?utm_medium=paid_EN&utm_content=songs-for-maximum-motivation&utm_source=getpocket.com&utm_campaign=PocketPromotion (URL de seguimiento)

/crear-infografías-con-plantillas-de-powerpoint-gratis (URL normal)

/create-infographics-with-free-powerpoint-templates?utm_medium=paid_EN&utm_content=create-infographics-with-free-powerpoint-templates&utm_source=getpocket.com&utm_campaign=PocketPromotion (URL de seguimiento)

Sería increíble si pudiera eliminar todo lo que está después del signo de interrogación en las URL de seguimiento para que coincidieran con las originales.

Ahí es donde entra la fórmula de texto dividido.

=DIVIDIR(texto, delimitador, [split_by_each], [remove_empty_text])

Texto: El texto que desea dividir (puede ser una cadena de caracteres, como /create-infographics-with-free-powerpoint-templates?utm_medium=paid_EN&utm_content=create-infographics-with-free-powerpoint-templates&utm_source=getpocket.com&utm_campaign=PocketPromotion , o una celda, como A2)

Delimitador: Los caracteres por los que desea dividir el texto.

Dividido_por_cada uno: Hojas de cálculo de Google considera que cada carácter del delimitador está separado. Eso significa que si divide su texto por «utm», dividirá todo alrededor de los caracteres «u», «t» y «m». Incluya FALSO en su fórmula para desactivar esta configuración.

En el ejemplo anterior, esta es la fórmula que usaría para dividir la primera parte de la URL del código UTM:

=DIVIDIR(A2,“?”)

La primera parte ahora está en la Columna B, y el código UTM está en la Columna C. Simplemente puedo eliminar todo en la Columna C y ejecutar V-LOOKUP en las URL en la Columna B.

Alternativamente, puede usar la función «Dividir texto en columnas» de Google Sheet. Resalte el rango de datos que desea dividir, luego seleccione «Datos»> «Dividir texto en columnas».

The 7 Most Useful Google Sheets Formulas

Ahora elija el carácter que desea delimitar por: dos puntos, punto y coma, punto, espacio o carácter personalizado. También puede optar por Hojas de cálculo de Google para averiguar por qué carácter desea dividir (lo cual es lo suficientemente inteligente como para hacerlo si sus datos se ingresan de manera uniforme, por ejemplo, cada celda sigue el mismo formato) eligiendo la primera opción, «detectar automáticamente».