Cargando ejercicio…
Cargando ejercicio…
Los ejercicios están pensados para usar con teclado. Lee la tarea aquí y luego abre esta página en una computadora para resolverla.
Condiciones dentro de SUMAPRODUCTO. Una comparación como (B2:B47=I1) da VERDADERO o FALSO en cada fila. Multiplicar dos de ellas da 1 solo donde ambas son verdaderas, y 0 en el resto, así que (rango=x)*(rango=y) funciona como Y. Ponlo delante de los valores y solo se suman las filas que coinciden.
=SUMAPRODUCTO((A2:A60="Paid")*(B2:B60="EU");C2:C60) facturas pagadas de la UE
Marcus Hill quiere una celda de verificación rápida: los ingresos de la región en I1 y la categoría en I2 a partir de las líneas de pedido de la primera semana de septiembre. Las líneas no tienen columna de ingresos, y él no quiere que se agregue una.
En I3, calcula los ingresos (cantidad en D × precio unitario en E × (1 − descuento en F)) solo de las líneas de las filas 2 a 47 cuya región en B coincide con I1 y cuya categoría en C coincide con I2, en una sola fórmula. Redondea a 2 decimales.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Category | Qty | Unit price | Discount | Region | South | |
| 2 | 50733 | East | Paper & Print | 6 | 9.90 | 0% | Category | Coffee & Kitchen | |
| 3 | 50734 | West | Desk Supplies | 5 | 15.50 | 0% | Revenue | ||
| 4 | 50734 | West | Coffee & Kitchen | 4 | 24.00 | 0% | |||
| 5 | 50735 | North | Coffee & Kitchen | 13 | 36.00 | 0% | |||
| 6 | 50736 | South | Paper & Print | 2 | 84.00 | 0% | |||
| 7 | 50736 | South | Cleaning | 1 | 25.00 | 5% | |||
| 8 | 50736 | South | Desk Supplies | 5 | 13.50 | 5% | |||
| 9 | 50737 | North | Coffee & Kitchen | 12 | 24.00 | 0% | |||
| 10 | 50737 | North | Cleaning | 12 | 23.50 | 0% | |||
| 11 | 50738 | South | Coffee & Kitchen | 30 | 21.50 | 5% | |||
| 12 | 50739 | South | Coffee & Kitchen | 4 | 36.00 | 5% | |||
| 13 | 50739 | South | Coffee & Kitchen | 3 | 21.50 | 0% | |||
| 14 | 50739 | South | Desk Supplies | 2 | 9.50 | 0% | |||
| 15 | 50739 | South | Cleaning | 4 | 15.00 | 0% | |||
| 16 | 50740 | West | Paper & Print | 13 | 6.20 | 5% | |||
| 17 | 50740 | West | Cleaning | 14 | 15.00 | 0% | |||
| 18 | 50740 | West | Coffee & Kitchen | 7 | 36.00 | 10% | |||
| 19 | 50741 | South | Coffee & Kitchen | 13 | 16.50 | 5% | |||
| 20 | 50742 | North | Coffee & Kitchen | 14 | 11.00 | 0% | |||
| 21 | 50742 | North | Cleaning | 3 | 15.00 | 0% | |||
| 22 | 50743 | North | Paper & Print | 13 | 84.00 | 0% | |||
| 23 | 50743 | North | Desk Supplies | 9 | 22.00 | 5% | |||
| 24 | 50743 | North | Paper & Print | 8 | 5.50 | 10% | |||
| 25 | 50744 | North | Coffee & Kitchen | 6 | 11.00 | 0% | |||
| 26 | 50745 | North | Paper & Print | 3 | 12.50 | 10% | |||
| 27 | 50746 | South | Desk Supplies | 18 | 19.00 | 0% | |||
| 28 | 50746 | South | Paper & Print | 11 | 9.90 | 0% | |||
| 29 | 50746 | South | Cleaning | 21 | 17.50 | 0% | |||
| 30 | 50746 | South | Desk Supplies | 15 | 9.50 | 10% | |||
| 31 | 50747 | South | Paper & Print | 6 | 6.20 | 5% | |||
| 32 | 50747 | South | Desk Supplies | 5 | 22.00 | 0% | |||
| 33 | 50748 | East | Cleaning | 5 | 14.00 | 5% | |||
| 34 | 50748 | East | Coffee & Kitchen | 12 | 21.50 | 5% | |||
| 35 | 50748 | East | Cleaning | 12 | 23.50 | 0% | |||
| 36 | 50748 | East | Paper & Print | 5 | 9.90 | 0% | |||
| 37 | 50749 | East | Coffee & Kitchen | 8 | 11.00 | 0% | |||
| 38 | 50749 | East | Coffee & Kitchen | 11 | 16.50 | 0% | |||
| 39 | 50749 | East | Cleaning | 5 | 23.50 | 0% | |||
| 40 | 50749 | East | Paper & Print | 8 | 59.00 | 10% | |||
| 41 | 50750 | North | Desk Supplies | 5 | 15.50 | 0% | |||
| 42 | 50750 | North | Coffee & Kitchen | 3 | 16.50 | 0% | |||
| 43 | 50751 | North | Cleaning | 6 | 25.00 | 0% | |||
| 44 | 50751 | North | Desk Supplies | 7 | 19.00 | 5% | |||
| 45 | 50751 | North | Coffee & Kitchen | 7 | 16.50 | 5% | |||
| 46 | 50751 | North | Desk Supplies | 11 | 22.00 | 0% | |||
| 47 | 50752 | South | Desk Supplies | 5 | 22.00 | 0% | |||
| 48 | |||||||||
| 49 | |||||||||
| 50 |
=SI(B2>=1,5;SUMA(C2:C9);0)Idioma
Plan completo
Excel ✓ · SQL y Power BI también incluidos
Cancela cuando quieras · Reembolso total en 14 días · Pago seguro con Stripe