Hoja de referencia de Excel para analistas de datos

Total de la línea (escríbela en K2 y arrástrala hacia abajo)

=F2*G2*(1-H2/100)

Las referencias relativas se mueven al arrastrar: la fila 3 lee F3, G3, H3.

Fija la celda de entrada con $

=G2*(1+$R$1)

Sin $, al arrastrar hacia abajo R1 pasa a R2, R3 …, y todas las filas después de la primera leen la celda equivocada. F4 alterna $R$1 → R$1 → $R1 → R1.

Fija solo la columna o solo la fila

=$G2*R$1

$G2 mantiene la columna G al arrastrar a la derecha; R$1 mantiene la fila 1 al arrastrar hacia abajo.

Porcentaje sobre el total de la columna

=G2/SUM($G$2:$G$41)

Total, promedio y mediana

=SUM(F2:F41)
=AVERAGE(G2:G41)
=MEDIAN(G2:G41)

Mínimo, máximo y tercer mayor

=MIN(G2:G41)
=MAX(G2:G41)
=LARGE(G2:G41,3)

Contar números, celdas con datos y celdas vacías

=COUNT(G2:G41)
=COUNTA(I2:I41)
=COUNTBLANK(I2:I41)

COUNT cuenta solo números; COUNTA cuenta todo lo que no está vacío.

Redondear el resultado

=ROUND(AVERAGE(G2:G41),2)
=ROUNDUP(F2/4,0)

ROUND cambia el valor; un formato de número solo cambia cómo se ve.

Multiplicar dos columnas y sumar

=SUMPRODUCT(F2:F41,G2:G41)

Dos resultados posibles

=IF(G2>=$R$2,"high","low")

Varios tramos, gana la primera coincidencia

=IFS(G2<50,"under 50",G2<200,"50-199",TRUE,"200+")

El TRUE final es el comodín. Sin él, un valor que no cumple ninguna prueba devuelve #N/A.

Todas las condiciones verdaderas

=IF(AND(C2="web",G2>200),"review","")

Cualquier condición verdadera

=IF(OR(D2="cancelled",D2="refunded"),"lost","kept")

Contar las filas que coinciden

=COUNTIF(C2:C41,"web")
=COUNTIFS(C2:C41,"web",D2:D41,"completed")

Sumar o promediar donde coinciden las filas

=SUMIFS(F2:F41,C2:C41,"app",D2:D41,"completed")
=AVERAGEIFS(G2:G41,C2:C41,"web")

SUMIFS recibe primero la columna que se suma y después pares de rango y criterio.

Comparaciones y "distinto de" en los criterios

=COUNTIFS(G2:G41,">=75",D2:D41,"<>cancelled")

Criterio tomado de una celda

=COUNTIFS(G2:G41,">="&R2)

Une el operador y la celda con &. ">=R2" buscaría el texto R2.

Valor máximo donde coinciden las filas

=MAXIFS(G2:G41,C2:C41,"marketplace")

Una tabla canal × estado con una sola fórmula (en U5)

=COUNTIFS($C$2:$C$41,$T5,$D$2:$D$41,U$4)

Los canales van hacia abajo en T5:T7 y los estados a lo largo en U4:V4. Escríbela en U5 y arrástrala a la derecha y hacia abajo: $C/$D se quedan quietas, $T5 mantiene la columna y U$4 mantiene la fila.

XLOOKUP (Excel 365, Google Sheets)

=XLOOKUP(E2,$L$2:$L$121,$M$2:$M$121,"not found")

Coincidencia exacta por defecto; el 4.º argumento reemplaza el #N/A.

INDEX / MATCH (todas las versiones)

=INDEX($N$2:$N$121,MATCH(E2,$L$2:$L$121,0))

El 0 en MATCH significa coincidencia exacta. Si lo omites, obtienes la clave menor más cercana.

VLOOKUP, coincidencia exacta

=VLOOKUP(E2,$L$2:$O$121,3,FALSE)

Sin FALSE, VLOOKUP hace una coincidencia aproximada y devuelve filas equivocadas con datos sin ordenar. Además se rompe cuando se inserta una columna.

Detectar una clave que falta

=IFNA(INDEX($M$2:$M$121,MATCH(E2,$L$2:$L$121,0)),"missing")

Quitar espacios sueltos y corregir mayúsculas

=TRIM(I2)
=PROPER(TRIM(I2))
=UPPER(C2)

Texto antes y después de un carácter

=LEFT(J2,FIND("@",J2)-1)
=MID(J2,FIND("@",J2)+1,100)

Unir texto

=C2&" / "&D2
=TEXTJOIN(", ",TRUE,C2,D2,I2)

TEXTJOIN con TRUE omite las celdas vacías, así que una ciudad faltante no deja un hueco ", ,".

Reemplazar parte de un texto y medir la longitud

=SUBSTITUTE(J2,"@"," at ")
=LEN(TRIM(I2))

Texto que parece un número

=VALUE("42.50")
=ISNUMBER(G2)

Los números guardados como texto quedan alineados a la izquierda y SUM los ignora. VALUE los convierte.

Partes de una fecha

=YEAR(B2)
=MONTH(B2)
=WEEKDAY(B2,2)

WEEKDAY con 2 numera el lunes como 1 y el domingo como 7.

Una clave de mes para agrupar

=TEXT(B2,"yyyy-mm")

TEXT devuelve texto, que se agrupa y ordena bien pero ya no sirve para cálculos con fechas.

Fin de mes y mes siguiente

=EOMONTH(B2,0)
=EDATE(B2,1)

Días entre dos fechas

=DATE(2025,12,31)-B2
=DATEDIF(B2,DATE(2025,12,31),"m")

Las fechas son números (días), así que la resta da días. DATEDIF da meses o años completos.

Contar filas dentro de un rango de fechas

=COUNTIFS(B2:B41,">="&DATE(2025,1,1),B2:B41,"<"&DATE(2025,2,1))

Rellenar una celda vacía con una etiqueta

=IF(ISBLANK(I2),"unknown",TRIM(I2))

Reemplazar un error por un valor

=IFERROR(G2/F2,0)

IFERROR oculta todos los errores, también los de escritura. Usa IFNA cuando solo esperas "no encontrado".

Marcar duplicados

=COUNTIF($J$2:$J$41,J2)>1

TRUE en cada copia, incluida la primera.

Conservar solo la primera copia

=COUNTIF($J$2:J2,J2)=1

El rango crece al arrastrar hacia abajo, así que solo la primera vez que aparece un valor da TRUE.

Marcar valores imposibles

=IF(F2<=0,"check quantity","")