Excel: BUSCARX, SUMAR.SI.CONJUNTO y tablas dinámicas
Vas a ordenar una tabla de ventas, cruzar datos con BUSCARX resolviendo los #N/D, clasificar con SI y resumir con SUMAR.SI.CONJUNTO y una tabla dinámica con filtros. Terminás con un mini reporte que cierra contra los datos, en Excel o Google Sheets.
- Idioma
- Español
- Lecciones
- 6
- Minutos
- 165
Precio: · La suscripción incluye todo el catálogo, sin cargo extra.
Temario
Datos ordenados y cruces entre tablas
- Datos ordenados: la tabla que hace funcionar todo lo demás (25′)
- Cruzar dos tablas con BUSCARX y resolver los #N/D (30′)
- Clasificar con SI: condiciones que leen tus datos (25′)
Resúmenes y armado del reporte
- Resúmenes con condiciones: SUMAR.SI.CONJUNTO (25′)
- Tu primera tabla dinámica con filtros (30′)
- El reporte completo: cruzar, clasificar y resumir (30′)
Primera lección gratis
Datos ordenados: la tabla que hace funcionar todo lo demás
Por qué tu planilla de siempre rompe las fórmulas
Hoy cargás datos y hacés sumas simples, y eso te funciona. El problema aparece cuando querés cruzar tablas o resumir por vendedora: las fórmulas necesitan encontrar cada dato siempre en el mismo lugar, y una planilla desordenada se lo esconde.
Tres problemas hacen casi todo el daño:
- Celdas combinadas: al combinar, el valor queda guardado solo en la celda superior izquierda; las demás quedan vacías. Una fórmula que pasa por ahí ve un hueco.
- Filas de totales en el medio: para una fórmula de resumen, ese subtotal es una venta más y el total general suma dos veces.
- Dos registros en una fila: cada venta tiene que ocupar su propia fila, o no hay forma de filtrar ni contar bien.
¿Por qué la celda combinada deja vacías las demás? Porque combinar no reparte el valor: lo guarda arriba a la izquierda y el resto de las celdas pasa a existir solo en pantalla.
De planilla desordenada a datos listos
Mirá una planilla de ventas típica: un título «Ventas 1er trimestre» combinado en la fila 1 y, de ahí en abajo, las columnas Vendedora, Zona, Mes e Importe. Además tiene un subtotal al final de cada semana y algunas filas con dos ventas apretadas («1.200 y 850» en la misma celda de Importe).
Arreglarla es mecánico:
- Borrá el título combinado; ese texto puede ir en el nombre de la hoja.
- Pasá cada fila doble a su propia fila, repitiendo vendedora, zona y mes en cada una.
- Quitá los subtotales del medio; el total general lo resolvés después con la fila de totales propia de la tabla (pestaña Diseño de tabla ▸ Fila de totales) o en un área separada de la hoja, nunca en la fila pegada al final del rango.
Queda así: encabezados en la primera fila, una fila por venta, y ninguna celda combinada. Todo lo que viene en este curso arranca de esta forma.
Convertí el rango en tabla de Excel
Con la planilla limpia del ejemplo anterior:
- Parate en cualquier celda del rango de datos (con los encabezados incluidos alrededor).
- Presioná Ctrl+T (o Insertar ▸ Tabla).
- Confirmá que esté tildada la opción «La tabla tiene encabezados» y aceptá.
- Verificá que el rango tomado no arrastre filas de título ni filas de totales; si lo hace, corregilo a mano en el cuadro de diálogo.
Qué ganás: al escribir en la primera fila vacía, la tabla se agranda sola, y las fórmulas y la tabla dinámica de las próximas lecciones lo siguen tomando como dato. Por eso el total nunca va en la fila inmediatamente debajo del rango: usá la fila de totales propia de la tabla (pestaña Diseño de tabla ▸ Fila de totales), que queda siempre fuera de los registros y se actualiza sola, o ponelo en un área separada de la hoja. En Google Sheets también podés convertir el rango en tabla (Formato ▸ Convertir en tabla, o Ctrl+Alt+T), aunque funciona algo distinto que en Excel; si preferís no usarla, mantené el rango igual de limpio y, si necesitás un nombre fijo para las fórmulas, usá Datos ▸ Rangos con nombre.
Referencias que se corren al copiar
Cuando copiás =C2*D2 hacia abajo, Excel baja todo: C3*D3, C4*D4. Eso es una referencia relativa, y es lo que querés casi siempre. El problema aparece con los valores únicos: si la comisión del 5 % está en F1 y escribís =D2*F1, al copiar la fórmula esa parte pasa a F2, F3, F4 y multiplica contra celdas vacías.
Para fijar una celda, poné el signo $ antes de la columna y de la fila: $F$1. Mientras escribís la fórmula, la tecla F4 (en algunas notebooks, Fn+F4; en Excel para Mac, Cmd+T) te alterna entre todas las combinaciones.
**¿Qué pasa si copiás =D2*$F$1 diez filas hacia abajo?** D2 baja fila por fila y $F$1 queda siempre en la celda de la comisión: cada fila calcula contra el valor correcto.
Completá vos la fórmula
En tu tabla, el Importe está en la columna E y el porcentaje de comisión (un valor único) está en la celda H1. Querés armar una columna Comisión que calcule ese porcentaje de cada venta y copiarla hasta el final.
La fórmula de la primera fila arranca así:
=E2*____
Completá en qué tiene que terminar. Fijate qué parte tiene que moverse con cada fila y qué parte tiene que quedar quieta.
Errores que arruinan el reporte
- Dejar celdas combinadas y seguir: las búsquedas y las tablas dinámicas leen huecos. Descombiná antes de escribir cualquier fórmula.
- Subtotales dentro del rango de la tabla: cualquier resumen los cuenta como ventas y el total cierra alto. Los totales van fuera del rango.
- Copiar sin el $: la fórmula da bien en la primera fila y mal en las demás. Si un total no cierra, revisá primero las referencias que tenían que quedar fijas.
Lo que te llevás
- Una fila por registro, encabezados en la primera fila, sin celdas combinadas: sin eso, nada de lo que sigue funciona.
- Ctrl+T convierte el rango en tabla y se agranda sola al cargar filas.
- El $ fija una referencia; sin él, todo se corre al copiar.
Tu planilla, bajo la lupa
Abrí una planilla de ventas que uses (del trabajo o una propia) y mirala con estos ojos: anotá qué celdas combinadas, filas de totales o registros dobles tiene, y qué le haría cada uno a un resumen.
- registro
- Una fila completa de datos: en tu tabla de ventas, una venta con su vendedora, zona, mes e importe.
- celda combinada
- Varias celdas unidas en una sola; el valor queda guardado únicamente en la celda superior izquierda y el resto queda vacío para las fórmulas.
- tabla de Excel
- Rango convertido con Ctrl+T: reconoce sus encabezados, se agranda solo al escribir filas debajo y es lo que esperan las tablas dinámicas.
- referencia relativa
- Referencia que se ajusta al copiar la fórmula: C2 pasa a C3, C4 y así sigue.
- referencia absoluta
- Referencia fijada con el signo $ (por ejemplo $F$1): no se mueve al copiar la fórmula.