[UPDATE] Estas 15 fórmulas Excel son un update del post original 3 fórmulas excel para tu felicidad.

Quise darle esta actualización, porque desde que comencé trabajando en performance, he aprendido muchísimo tanto de excel como de otras herramientas similares como Hojas de Cálculo de Google.

Y como mi cultura siempre ha sido la de compartir lo aprendido, me parece justo que tu también tengas estas fórmulas guardadas en tu drive, para esto cree una plantilla que puedes descargar gratuitamente.

El análisis de datos puede llegar a ser un trabajo hasta sencillo, si aprendes a aplicar las fórmulas necesarias que te ayudarán a reducir, en semanas, todo este trabajo. twittea esto

Cualquiera sea el desafío, te paso a explicar cuáles son y cómo las puedes aplicar.

1. Cómo eliminar los espacios en blanco en Excel =espacios

Vamos a comenzar con algunas fórmulas útiles para mantener limpias nuestra información y reportes.

Me ha pasado muchas veces que me envían tablas de las que tengo que sacar información, con espacios adicionales al inicio de la celda, y limpiarlos 1 a 1, es un trabajo que no quiero hacer.

Lo bueno es que Excel, y las hojas de cálculos de Google, cuentan con una función muy simple y útil, =espacios.

En el caso de abajo tengo un listado de países que me enviaron con espacios de más en algunas de sus celdas, para poder limpiarlas, simplemente en una columna escribo =espacios([numero_columna]) presiono Enter y listo! mi celda acaba de quedar sin ese tedioso espacio en blanco al inicio.

2. Cómo saber qué fecha es hoy con Excel (o hojas de cálculo de Google) =hoy ()

Esta función es útil si queremos crear una carta gantt por ejemplo que comience desde hoy y se repita semana a semana.

Para este caso, simplemente utilizamos la función =hoy() sin valores dentro del paréntesis, de esta forma te va a devolver el valor del día de hoy.

Ahora si a esta función le sumamos días, podrás conocer la fecha exacta en X días más, por ejemplo =hoy()+7, te devuélveme la fecha de hoy más 7 días más, lo mismo si pruebas con 14 (que serían 2 semanas más).

Si quieres jugar más aún y conocer el futuro, puedes ver incluso el valor de 4 años y 20 días, simplemente de la siguiente forma

=HOY()+((4*356)+20).

Esta función se hace útil, los días que planificamos los experimentos de la semana, y le colocamos una fecha de revisión a los resultados (30 a 60 días normalmente).

En la imagen inferior puedes ver cómo se visualiza esta fórmula.

3. Cómo separar texto en una celda por caracteres de IZQUIERDA, EXTRAE y DERECHA en Excel =izquierda, =extrae y =derecha

Esta función es muy útil cuando trabajamos con largas bases de datos, a las cuales siempre el programador le designa valores a cada elementos que después en nuestro reporte no se verán muy bien (no es un problema, simplemente es la forma como se hace).

Función =IZQUIERDA

El objetivo de esta función es separar una cantidad de caracteres de izquierda a derecha, los cuales deben ser contabilizados.

La función es

=IZQUIERDA(texto, [número_de_caracteres]).

Lo que hace es buscar el texto del donde quieres tomar los caracteres desde la izquierda.

Por ejemplo, en el ejemplo de abajo, lo que estamos haciendo es tomar los primeros 6 caracteres, para separarlo de su ID.

Función =EXTRAE

Su objetivo es tomar una parte del total de un texto, con el fin de extraer el pedazo exacto de información que necesitas.

Está compuesto de la siguiente forma:

=extraer(texto;posicion_inicial;numero_de_caracteres)

Donde texto, hace referencia al valor de donde quieres tomar la información, posicion_inicial corresponde al número de carácter específico desde donde quieres comenzar a extraer información y numero_de_caracteres corresponde al total de contenido a extraer.

En el ejemplo de abajo vemos que de la palabra completa, lo que queremos obtener son los 2 primeros números después del texto inicial.

Funcion =DERECHA

El objetivo de esta función es tomar los caracteres del lado derecho de una palabra y en base a la cantidad que nosotros le indiquemos.

La función es

=DERECHA(texto;número_de_caracteres)

Donde texto es la celda de donde vamos a tomar la información, y número de caracteres corresponde al total de caracteres que queremos tomar desde la derecha hacia la izquierda.

En el ejemplo de abajo lo que hicimos fue tomar los últimos 4 caracteres, para separarlo del resto del texto total.

4. Cómo crear valores Aleatorios en Excel =aleatorio y =aleatorio.entre

Función =ALEATORIO

Esta función tiene como objetivo, obtener datos aleatorios de un rango u obtener datos aleatorios sin necesidad de darle un rango.

Para quienes hacen concursos en redes sociales, esta función es muy útil al momento de seleccionar un ganador en forma aleatoria.

Por ejemplo puedes plantear que la persona que saque el número más alto es quién gana el concurso simplemente colocando =aleatorio(), pero para que ese número sea entero y menor a 100, debes hacer un cambio en la fórmula, quedando de la siguiente forma =aleatorio()*100.

Fijate en la imagen como cambia la forma con una y otra opción

NOTA: Super importante es que sepan que cada vez que hagan Enter o cambien de celda, este valor aleatorio también cambia.

Función =ALEATORIO.ENTRE

Ahora para no dejarlo todo al azar, lo que puedes hacer es seleccionar un rango de números concretos, por ejemplo designándole un número a cada participante, y cambiando de función a:

=aleatorio.entre(inferior;superior)

Donde inferior es el rango menor, y superior el rango mayor.

De esta forma podemos seleccionar a un ganador de forma aleatoria, como se ve en la imagen inferior.

Ahora si queremos ser más concretos, y dar el nombre de la persona ganadora, lo que podemos hacer es anteponer la función INDICE, de tal forma de que cuando se genere la elección aleatoria sea a partir de un listado de texto.

Esta función se escribiría de la siguiente forma

=indice(rango;(aleatorio.entre(inferior;superior)))

Donde rango son todos los valores de una columna de donde se quiere obtener el nombre del afortunado, el valor inferior y superior lo tomamos de la cantidad de participantes.

En el ejemplo inferior podremos ver cómo se implementa esta opción.

5. Cómo unir celdas en Excel = concatenar

La función de concatenar te permite unir diferentes celdas en una sola. Para poder utilizarla haremos un ejercicio práctico.

Unión de palabras para poder crear una URL de búsqueda en Google, que me ayude en mi investigación de palabras claves:

Primero veamos cómo podemos llevar a cabo esto

Creamos un listado de palabras, que sean relevantes, para poder presentar una propuesta de SEO competitiva.

Inicio mi búsqueda de potenciales palabras claves, para eso pueden utilizar:
Planificador de palabras claves de Google Adwords
Con esta herramienta podrás crear nuevas ideas de palabras o grupos de palabras claves para tu estrategia.

Google Trends
Esta herramienta te ayudará a conocer el nivel de búsqueda que tienen las palabras claves que quieres utilizar. Puedes incluso hacer comparaciones entre diferentes conceptos para medir el nivel de búsqueda.

SEMRUSH
Esta herramienta es muy potente para conocer el nivel de palabras claves de tu competencia, sin embargo está hasta ahora, limitada a encontrar resultados en algunos países.

Una vez que consigo las mejores palabras para obtener los resultados que necesito, comienza el proceso de optimización: o sea el mínimo de palabras efectivas que debería utilizar para aparecer en los resultados (hay miles de otras variantes en el ranking, pero esto es lo básico). Para eso tenemos que conocer la estructura de un resultado:

Estructura básica de un resultado

Titular: es el primer texto que describe la página web. No debe superar los 58 caracteres (incluido espacios) aproximadamente, para que se pueda visualizar completo.

URL: esta está compuesta por el nombre del sitio y el nombre de las páginas que contiene este. No debe superar los 68 caracteres aproximadamente, incluyendo los espacios.

Descripción: es el resumen inicial que le muestra al usuario, con qué contenido se va a encontrar una vez que haga clic en este link. No debe superar los 162 caracteres, aproximadamente para que se vea por completo. De igual forma Google selecciona y muestra un resumen del contenido.

Ahora paso a desarrollar las URLs más efectivas, para posicionar mi sitio bajo estos conceptos.

La fórmula de concatenación (suena como física cuántica, pero es más sencillo de lo que parece), es =CONCATENAR(texto1;texto2;…), así obtienes el resultado esperado.

Veamos el ejemplo a continuación:
Vamos a utilizar las palabras claves:
implementar + estrategia + digital + crecimiento

Si se dan cuenta, la estructura de un resultado en Google se compone más o menos así: www.google.cl/#q=texto1+texto2+… donde texto1 y texto 2 serían las palabras claves que vamos a utilizar para nuestra estrategia:

Para eso la fórmula que vamos a utilizar debería estar compuesta de la siguiente forma:

=CONCATENAR(A2&B2;"+";C2;"+";D2;"+";E2;"+";F2)

El símbolo “&” o “;” son utilizados para unir dos celdas o cerrar la función y pasar al siguiente bloque, sin dejar espacio entre ellas. el siguiente bloque si quieres colocar un elemento como el signo “+” por ejemplo, lo expresas dentro de los punto y coma como en el ejemplo.

Así obtienes las URLs de diferentes búsquedas en Google, el siguiente paso es reconocer cuales son los primeros resultados y comenzar a utilizarlos a tu favor.

6. Contabilizar la cantidad de caracteres en una celda en Excel, función =largo

Esta función es muy útil para todos los que trabajamos en plataformas de publicidad donde nos limitan la cantidad de caracteres, o bien para la optimización de los titulares y descripciones de SEO.

La función es simple.

=largo(texto)

Donde texto bien puede ser una celda seleccionada dentro de una tabla completa.

En el ejemplo de abajo vemos la cantidad de caracteres que contienen los avisos que hemos creado como ejemplo. La idea es que puedas comprender la cantidad de contenido que puedes agregar a tus avisos y así ir probando pequeños conceptos que hagan más eficientes tus avisos.

7. Fórmula para etiquetar tus columnas en base a una condición: =si

La función SI nos permite filtrar información, que puedes etiquetar en una columna aledaña, de esta forma si quieres conocer cuales son los mejores resultados de una tabla, utilizando esta función podrás segmentar tu información fácilmente.

Su estructura sería así:

=si(condición, verdadero, falso)

Donde la condición es un valor que puede designar en base a > (mayor), = (igual), o < (menor), entre otras opciones.

Si esta condición se cumple, pasamos a la siguiente “coma”, designando un valor como “Verdadero”.

Finalmente, si no se cumple la condición, podemos colocar el valor “Falso”.

Revisemos un ejemplo.

Si quisiéramos categorizar nuestros clientes, en base al ingreso total mensual que realizan, podemos hacer una separación entre “Cliente Premium”, aquellos que invierten más de 1.500 y “Cliente Regular”, quienes invierten menos de 1.500.

Nuestra fórmula sería en el ejemplo =si(D5>1500,”Cliente Premium”, “Cliente Regular”)

Si se fijan en el ejemplo, contamos con etiquetas al lado de cada columna, que nos ayudarán a conocer la categoría de cliente de una forma ultra simple.

8. Fórmula SI, pero con condiciones, para filtrar aún más tu información: =si(y

Estas fórmulas combinadas, nos ayudan a entender la base de la función SI, pero además nos permite generar un filtro aún más profundo, por lo que la información que vayamos etiquetando, puede ser cada vez más concreta a lo que quieres aprender de esta.

Tomemos el mismo ejemplo anterior, pero esta vez vamos a agregar una función que se llama =mes, que nos servirá, al igual que la función SI, para generar una condición.

Lo que queremos conocer en este caso, son la cantidad de clientes importantes que hayan comprado en los últimos 5 meses un valor superior a 1500.

Entonces la función sería así

=SI(Y(MES(A3)=2;D3>1500);"Cliente Premium Actual";"Cliente Regular").

Donde =si es la condición 1 MES, y la condición 2 D3, que sumado a la validación Verdadera “Cliente Premium Actual” y la Falsa “Cliente Regular”, generan una celda final que nos filtrar concretamente la cantidad de clientes que actualmente hayan comprado.

Si se fijan en esta nueva función, el gran cambio que vemos es la cantidad de clientes Premium, esto porque le dimos una condición adicional Y, que nos ayudó a generar una filtro más concreto a la información que necesitamos.

9. Personaliza los errores de una tabla en Excel con =si.error

Esta función permite que puedas personalizar los mensajes de error #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!, caso contrario te mostrará resultados como falso o verdadero.

El objetivo es personalizar el mensaje cuando bajo las condiciones planteadas aparezca un mensaje de error.

La función se escribe de la siguiente forma:

=si.error(valor;valor_si_error)

Donde valor es la regla sobre al cual quiero obtener el resultado, y valor_si_error sería el texto que debería aparecer en caso que exista error en la fórmula.

En el ejemplo de abajo queremos verificar cuales son los canales de marketing donde se generó un gasto y no logramos registrar usuarios.

Para esto en la columna E vamos a conocer el costo por adquisición de clientes, y si no tuvimos cliente y hubo gastos, nos debería arrojar el mensaje “Sin Registro”.

Nota: Si no utilizamos la fórmula =si.error, nos arrojaría como texto #¡DIV/0!.

10. Cómo contar elementos en Excel con =contar, =contara, y =contar.blanco

La función CONTAR debe ser una de las más utilizadas, para valorizar diferentes tipos de análisis.

En este caso vamos a mostrar 3 fórmulas que cumplen funciones similares, pero que en sí te ayudarán a resolver temas muy diferentes unos de otros.

Función =CONTAR

Esta función tiene como principal utilidad contar la cantidad de celdas dentro de un rango, que contengan solo números.

=contar

Función =CONTARA

Básicamente sirve para contar todas las celdas, incluídos textos y números, dejando de lado solo aquellas celdas en blanco.

=contara

Función =CONTAR.BLANCO

Esta función al igual que las anteriores tiene como principal objetivo, contar la cantidad de espacios en blancos que se generen dentro de un rango.

Para esto la función es =contar.blanco(rango1;rango2;…rangoX), donde el espacio rango1, rango2 puede ser reemplazado por un rango concreto: A1:A15, por ejemplo.

En la imagen se puede ver la fórmula y la cantidad total de espacios blancos contabilizados.

11. Fórmula para contar con una condición: =contar.si

Te acuerdas de la fórmula SI, que nos exigía que para mostrar un resultados, se tiene que cumplir cierta condición.

Para esto caso, es bastante similar el concepto, la diferencia básica es que al igual que la función CONTAR, esta función de CONTAR.SI, cumple el mismo cometido, pero con una condición.

En el ejemplo de abajo podrás ver que se utiliza esta fórmula para contabilizar la cantidad de links que utilizan la palabra “Growth”, para esto lo que hemos hecho es primero hacer un Crawl de nuestro sitio growthhacking.cl, con el fin de levantar todas las URLs de contenido existentes.

Para esto utilizamos la herramienta Screaming Frog (por si alguien quiere realizar este ejercicio con su propio sitio web).

Una vez que contemos con todas las URLs, el siguiente paso es aplicar la fórmula.

La fórmula quedaría así:

=contar.si(rango;criterio)

Donde el criterio en este caso, al ser una URL, lo que estoy buscando son los artículos donde utilicé la palabra Growth.

Para esto tendremos que trabajar con WildCards, que son funciones dinámicas de búsqueda, similares a las expresiones regulares.

En este caso la búsqueda de growth la tenía que realizar entre *-growth-* (cuando el nombre growth quedara entre palabras), */growth-* (cuando la URL empezara con growth) y *-growth/* (cuando la URL finalizara con growth).

Para estas 3 opciones se crearon 3 funciones diferentes

=contar.si(A3:A127;”*-growth-*)

=contar.si(A3:A127;”*/growth-*) y
=contar.si(A3:A127;”*-growth/*)

Dando como resultado 19 URLs con estos valores.

Abajo pueden ver como se ve el resultado en el ejemplo.

12. Fórmula para contar la cantidad de veces que se repite un texto dentro de una posición específica: =contar.si.conjunto

Uno de los trabajos que me ha tocado llevar a cabo, ha sido la investigación o validación de un producto. Este trabajo lo he realizado para empresas que quieren comenzar a emprender y están en la búsqueda de un servicio que les ayude a llevar a cabo el desarrollo de sus proyectos, partiendo siempre por una validación si el producto gusta y cuantos lo comprarían.

Esta validación se puede hacer a través de encuestas, que algunas veces tienen muchas variables. Al inicio se analizan las respuestas, unas independientes de las otras, pero para poder comprender o encontrar actividades más concretas debes generar una cantidad de filtros que te ayuden a medir realmente quienes, con ciertas características, estarían dispuestos a comprar tu producto a “ojos cerrados”.

Para ejemplificar, vamos a buscar la cantidad de mujeres, entre 20-27 años, que vivan en departamento y estén dispuestas a comprar una pera “Buda”.

Para este ejercicio vamos a colocar en la columna A los rangos de edad, en la columna B el sexo, columna C donde viven y columna D si están dispuestos a comprar o no.

Colocamos la fórmula en la columna E en la celda 2:

=contar.si.conjunto(A2:A5;”>=20”;A2:A5;”<=27”;B2:B5;”femenino”;C2:C5;”departamento”;D2:D5;”si”)

Vamos a desmenuzar la fórmula:

=contar.si.conjunto(Intervalo_criterio_1;criterio_1…: El valor intervalo_criterio_1 corresponde a todas las celdas donde voy a hacer mi primera búsqueda, o sea desde la A2 hasta la A5 y para esto se expresa de las siguiente forma: A2:A5, el “desde al hasta” en excel se expresa con “:”. Ahora agregamos un “;” para cerrar el intervalo y es minuto de colocar el criterio_1 que corresponde al valor o palabra exacta que estamos buscando dentro de la celda, y para eso la cerramos entre comillas “>=20”, que quiere decir mayor o igual a 20.

Lo mismo sucede para el intervalo_2, criterio_2 …. intervalo_n, criterio_n, entre otras.

El resultado debería ser 2, como se muestra en el ejemplo.

Si se quiere contar cuantas veces se escribe una palabra dentro de un titular se le agrega al criterio un asterisco “*” antes y después de la palabra: “*pera*”, y se van a contar todas las celdas donde se escribió esta palabra.

13. Fórmula para sumar el total numérico de un elemento dentro de diferentes columnas: =sumar.si

Esta fórmula permite sumar valores que cumplan un criterio concreto.

Su objetivo es que puedas aislar el total de un valor en base la condición específica que le asignes.

Para esto la fórmula se compone de la siguiente forma:

=sumar.si(rango;criterio;rango_de_suma)

Donde rango corresponde a los elementos de donde extraer la información, criterio sería la condición sobre la cual necesitas información y rango de suma, que es un campo opcional, serían todos los valores de donde quiero hacer la suma total.

La función más básica sencillo de esta fórmula sería así:

=sumar.si (A1:A5;“>5000”)

Donde A1:A5 sería la columna de donde necesito tomar la información y quiero que solo sumes aquellos que son >5000.

Pero podemos hacer más aún, como en el ejemplo de abajo, lo que haremos es sumar el total de ingresos generados por canales de marketing.

Para este ejemplo la fórmula es:

=sumar.si(A3:A22;E4;C3:C22)

Quiero que de A3:A22 (donde están todos los canales de marketing), busques solo el canal de marketing E4 (que es el canal Google Orgánico), y sumes el total de ingresos que puedes obtener en C3:C22 desde E4 (abajo la imagen de referencia)

Nota: Si se fijan agregamos el signo $ delante de la A y del número de celda, esto se hace para “fijar” este valor, y cuando traslademos la fórmula hacia otra celda, está siempre llamará a la celda que hemos “fijado”.

Para aquellos que trabajamos mucho en la calificación de canales de adquisición, esta fórmula es simple y muy principalmente cuando quieres tomar información concreta de un solo canal de marketing.

14. Fórmula para sumar el total numérico de un elemento dentro de diferentes columnas: =sumar.si.conjunto

Otra fórmula que estoy utilizando es la de sumar en base a diferentes criterios un valor total. La utilizo para conocer datos filtrados de Google Analytics, que finalmente me ayuden a comprender ciertas características, como por ejemplo a qué hora es donde se producen la mayor cantidad de conversiones y a través de qué medios de adquisición.

Para esto esta fórmula me permite conocer estos rangos que después transformo en hermosos gráficos que me permiten visualizar las curvas de crecimiento o depresión y así investigar en profundidad esa curva y porque se produjo.

Para efectos prácticos, vamos a hacer la suma de todas aquellas mujeres tienen entre 20 y 27 años, que viven en departamentos, quieren comprar las peras, pero vamos a agregar una columna E donde estará la cantidad de peras que cada una de estas personas quiere.

En la celda G2 colocaremos la fórmula para conocer el valor final:

=sumar.si.conjunto(E2:E5;A2:A5;”>=20”;A2:A5;”<=27”;B2:B5;”femenino”;C2:C5;”departamento”;D2:D5;”si”)

En este caso la fórmula ha cambiado, porque hemos colocado en la primera parte el intervalo de la columna que vamos a sumar. Donde el resultado es 41 peras de buda se compraría, si hacemos la suma manual, el valor se repite.

15. Fórmula para buscar un elemento dentro de una tabla (tu propio buscador en excel): =buscarv

Esta función, como bien dice su título, permite buscar un valor dentro de un rango de datos, considerando siempre la vertical (por esto la v al final de la fórmula).

Su objetivo es que puedas disminuir los tiempos de búsqueda de un valor dentro de una tabla de información, y entender si ese valor existe o no.

En caso que el valor no exista, te arrojará el valor #N/A.

La función se escribe de la siguiente forma:

=buscarv(valor_buscado;matriz_buscar_en;indicador_columnas;[ordenado])

Donde valor_buscado corresponde, como bien dice al valor que queremos encontrar en la tabla, que, la matriz_buscar_en corresponde al rango de valores de donde queremos obtener este valor, el indicador_columnas es el valor que nos va mostrara como resultado, y en este caso es el número de la columna de donde queremos obtener la información del valor_buscado, y donde [ordenado] utilizamos el valor FALSO, para que busca ese resultado concreto, en caso contrario va a buscar un valor aproximado, y no es lo que queremos.

En el ejemplo de abajo queremos conocer el ingreso total obtenido del canal Facebook y el mes, para esto el indicador:columnas es 3 y 2 respectivamente para cada caso.

Conclusiones

Excel, como también las hojas de cálculo de Google, son 2 herramientas similares y muy poderosas que te ayudarán a reducir en un montón de tiempo el trabajo que debes desarrollar para tu análisi y planificación de negocios, como de marketing.

El manejo de las fórmulas independientes unas de otras, te aportan el conocimiento necesario para que tu mismo puedas crear nuevas fórmulas al ir combinando estas funciones.

Un profesional del marketing que tenga un manejo óptimo en estas fórmulas cuenta con una ventaja competitiva frente al resto, ya que estas habilidades son muy complejas de desarrollar sin un conocimiento o práctica constante.

No solo te ayudará a reducir tiempos, sino que además te ayudará a descrubrir datos y elementos que nos tenías considerado, que bien podrían ser ejecutados en tu estrategia de Growth Marketing.

Por ahora, si tienes dudas déjalas en los comentarios.