Excel y Google Sheets para análisis de datos

La hoja de cálculo es la herramienta de análisis más usada del mundo y la primera que te pedirán en casi cualquier oferta de analista. Con una docena de funciones bien elegidas puedes responder la mayoría de las preguntas de negocio sobre una tabla de miles de filas. En esta lección usarás Excel o Google Sheets para analizar las 197 ventas de Nexo: indicadores generales, sumas con condiciones, búsquedas en otra tabla y filtros dinámicos. Todas las fórmulas están en español y separadas con punto y coma.

1. Convierte el rango en tabla
2. Calcula indicadores básicos
3. Suma y cuenta con condiciones
4. Busca datos en otra tabla
5. Filtra y ordena con fórmulas

Paso 1: convierte el rango en tabla

Haz clic en cualquier celda de los datos y pulsa Ctrl + T (en Google Sheets: Formato › Convertir en tabla). Marca «La tabla tiene encabezados». En la pestaña Diseño de tabla, cámbiale el nombre a tVentas. Ganas tres cosas:

• Filtros automáticos en cada encabezado para explorar los datos.
• Rangos que crecen solos: si agregas pedidos, las fórmulas los incluyen sin que cambies nada.
• Referencias estructuradas, más legibles que las letras de columna: =SUMA(tVentas[importe]) en lugar de =SUMA(I2:I198).

En los ejemplos usaremos rangos con letras para que sirvan igual en Google Sheets. Las columnas de Ventas_limpias son: A id_pedido, B fecha, C region, D producto, E categoria, F canal, G unidades, H precio_unitario e I importe.

Paso 2: indicadores básicos

Empieza siempre con una «foto» general de los datos. Estas cinco fórmulas te dicen el tamaño del negocio y te ayudan a detectar valores imposibles (un importe negativo o un máximo absurdo):

CONTARA cuenta celdas no vacías de cualquier tipo; CONTAR solo cuenta números. Para contar pedidos usa CONTARA sobre la columna del identificador.

Paso 3: sumar, contar y promediar con condiciones

Las preguntas de negocio casi siempre tienen un «de»: ventas de Lima, pedidos del canal Web, ticket de la tienda física. Para eso están las funciones con .SI.CONJUNTO. Su estructura es: primero el rango a sumar (o promediar) y luego pares de rango y criterio.

Ventas de Lima:
=SUMAR.SI.CONJUNTO(I2:I198; C2:C198; "Lima")                       → 7.516,60

Ventas de Lima por la web (dos condiciones):
=SUMAR.SI.CONJUNTO(I2:I198; C2:C198; "Lima"; F2:F198; "Web")       → 3.658,00

Pedidos del canal Web:
=CONTAR.SI.CONJUNTO(F2:F198; "Web")                                → 94

Ticket promedio de la Tienda física:
=PROMEDIO.SI.CONJUNTO(I2:I198; F2:F198; "Tienda física")           → 206,58

Ventas de diciembre (criterios con fechas):
=SUMAR.SI.CONJUNTO(I2:I198; B2:B198; ">="&FECHA(2025;12;1);
                   B2:B198; "<="&FECHA(2025;12;31))                → 5.920,40

Fíjate en el último ejemplo: los criterios con operadores (>=, <=) van entre comillas y se unen a la fecha con &. Usar FECHA(año;mes;día) evita problemas de formato regional.

Una tabla resumen que se copia hacia abajo

En lugar de escribir el nombre de cada región en la fórmula, crea una lista de regiones en la columna L y apunta el criterio a esa celda. Fija los rangos con $ para que no se desplacen al copiar:

M2: =SUMAR.SI.CONJUNTO($I$2:$I$198; $C$2:$C$198; L2)
N2: =CONTAR.SI.CONJUNTO($C$2:$C$198; L2)
O2: =M2/N2          ← ticket promedio de la región

Copia M2:O2 hacia abajo y tendrás ventas, pedidos y ticket de las 9 regiones. Si las referencias absolutas te generan dudas, repasa la lección de referencias relativas y absolutas, y para más ejemplos de estas funciones, la de SUMAR.SI.CONJUNTO y CONTAR.SI.

Paso 4: trae el país con BUSCARX

La tabla de ventas tiene la región, pero no el país. El país está en la hoja Regiones (columna A región, columna B país). Agrega una columna J llamada pais en Ventas_limpias y escribe en J2:

=BUSCARX(C2; Regiones!$A$2:$A$10; Regiones!$B$2:$B$10; "No encontrado")

BUSCARX busca el valor de C2 en la lista de regiones y devuelve el país de la misma fila. El cuarto argumento define qué mostrar si no lo encuentra. Si tu Excel no tiene BUSCARX (versiones anteriores a Microsoft 365 o 2021), usa la clásica:

=BUSCARV(C2; Regiones!$A$2:$B$10; 2; FALSO)

Con la columna país ya puedes resumir por país con SUMAR.SI.CONJUNTO, por ejemplo =SUMAR.SI.CONJUNTO(I2:I198;J2:J198;"México"). El resultado:

Ventas 2025 por país en USD. El largo de cada barra es su porcentaje del total de 38.472,20 USD (40,5%, 38,3% y 21,2%).

Si en la hoja Ventas (la que no está limpia) aplicas la misma fórmula, verás varios «No encontrado»: son las regiones mal escritas como «CDMX» o «Bogota». BUSCARX es, además, una gran herramienta para detectar errores. Más detalle en la lección de BUSCARV y BUSCARX.

Paso 5: filtra y ordena con fórmulas

En Microsoft 365, Excel 2021 o posterior y Google Sheets existen funciones de matriz dinámica: escribes la fórmula en una celda y el resultado «se derrama» en las celdas de abajo.

En ORDENAR, el 9 indica la novena columna del rango (importe) y el −1, orden descendente. Estas funciones reemplazan muchos filtros manuales y, a diferencia de ellos, se actualizan solas. Las verás a fondo en la lección de FILTRAR, ORDENAR y UNICOS.

Columnas auxiliares de fecha

Para analizar por mes o trimestre, agrega dos columnas calculadas:

Mes:        =MES(B2)                          → 1 para enero
Trimestre:  ="T"&REDONDEAR.MAS(MES(B2)/3; 0)   → "T1" de enero a marzo

Con ellas, =SUMAR.SI.CONJUNTO(I2:I198;L2:L198;"T4") (si el trimestre está en la columna L) devuelve 15.687,00 USD: el 40,8% de las ventas del año.

Excel o Google Sheets

La función más poderosa de Google Sheets para analistas es QUERY, que permite escribir una consulta parecida a SQL dentro de una celda. Esta fórmula resume las ventas web por región, de mayor a menor:

=QUERY(Ventas_limpias!A1:I198;
  "select C, sum(I) where F = 'Web' group by C order by sum(I) desc"; 1)

Es una excelente puerta de entrada a SQL, que aprenderás en las lecciones 8 y 9.

Errores comunes (y qué hacer en su lugar)

1. =SUMAR.SI.CONJUNTO(I2:I198; E2:E198; "Audio"; J2:J198; "México"; F2:F198; "Marketplace")
   → 1.481,90 USD
   =CONTAR.SI.CONJUNTO(E2:E198; "Audio"; J2:J198; "México"; F2:F198; "Marketplace")
   → 8 pedidos

2. =PROMEDIO.SI.CONJUNTO(I2:I198; F2:F198; "Web";
        B2:B198; ">="&FECHA(2025;12;1); B2:B198; "<="&FECHA(2025;12;31))
   → 162,35 USD (15 pedidos que suman 2.435,30)

3. =SUMAR.SI.CONJUNTO(I2:I198; E2:E198; "Cómputo"; J2:J198; "Perú")
   → 8.132,50 USD, que es 8.132,50 / 14.735,70 = 55,2% de las ventas de Perú

Resumen

• Convierte los datos en tabla: filtros, rangos que crecen solos y referencias legibles.
• Empieza con indicadores generales: SUMA, CONTARA, PROMEDIO, MAX y MIN.
• SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO responden las preguntas con «de».
• BUSCARX trae datos de otra tabla y, de paso, delata valores mal escritos.
• UNICOS, FILTRAR, ORDENAR y QUERY convierten la hoja en una pequeña herramienta de consulta.

En la próxima lección: Limpieza de datos: duplicados, errores y valores faltantes. Limpiarás tú mismo la hoja Ventas, con sus 200 filas y sus errores, hasta obtener los 197 pedidos correctos.