☰

Suma de búsquedas virtuales para cada valor seguido

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
Suma de búsquedas virtuales para cada valor seguido

Debería haber una manera de hacer esto y, sin embargo, estoy luchando...

==Los datos==

Hoja1

        A           B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod           | $9.99 |
| Trout         | $8.99 |
| Haddock       | $6.99 |
| Kippers       | $3.99 |
+---------------+-------+

Hoja2

       A            B         C       D        E
+------------+-------------+-----+--------+---------+
|            | Total Spend | Cod | Trout  | Haddock |
+------------+-------------+-----+--------+---------+
| Customer 1 | ?           |   1 |        |       3 |
| Customer 2 | ?           |     |      2 |         |
| Customer 3 | ?           |   2 |      2 |       2 |
+------------+-------------+-----+--------+---------+

==El desafío==

Para cada cliente, necesito calcularGasto totalmultiplicando 'compras' en la Hoja2 por el 'producto/precio' en la Hoja1. Por ejemplo, podría calcularGasto totalpara el Cliente 1 del tipo: =(C2*vlookup(c1,'Sheet1!A:B,2,0))+(D2*vlookup(D1,'Sheet1!A:B,2,0))+(E2*vlookup(E1,'Sheet1!A:B,2,0))etc.

PEROSe agregan nuevos productos a la Hoja 1 todo el tiempo.

Tan pronto como se agregue "kippers" como Columna F, tendría que retroceder manualmente y actualizar toda la fórmula. (Tenga en cuenta que cada vez que se agrega un nuevo producto a la Hoja1, aparece automáticamente como una nueva columna en la Hoja2, debido a la fórmula que se utiliza). Dejando a un lado la pereza personal, la preocupación es que no puedo detectar cuándo se necesitan actualizaciones y trabajar con totales incorrectos.

¿Hay alguna forma de calcular?Gasto total¿Sin tener que actualizar manualmente la fórmula cada vez que se agrega un nuevo elemento/columna?

Muchas gracias de antemano a cualquiera que pueda ayudarme con esto.

PD: Debo agregar que estoy trabajando en Google Sheets.

Respuesta1

Solo usa SUMPRODUCT()para hacer tus cálculos:

ingrese la descripción de la imagen aquí

Fórmula en B8:

=SUMPRODUCT((A$2:A$5=C$7:E$7)*(B$2:B$5*C8:E8))

Arrastre hacia abajo, pero tenga en cuenta que no se actualizará automáticamente cuando se agreguen productos.

Para aclarar, el orden de los productos es irrelevante:

ingrese la descripción de la imagen aquí

Los mismos resultados.

Respuesta2

Con pocas modificaciones en la orientación de los datos, este problema se puede resolver:

:Advertencia:

El método mostrado también funciona con Google Sheet y Excel.

ingrese la descripción de la imagen aquí

  • Fórmula en la hoja de celda 2 H19 y complete:

     =IFERROR(SUMPRODUCT(VLOOKUP(Sheet1!G13,$G$19:$K$22,{3,4,5},FALSE))*Sheet1!H13,"")

NÓTESE BIEN

Si SUMPRODUCTO tiene un solo argumento, se comporta de manera muy similar a SUM. Pero cuando SUMPRODUCT tiene múltiples argumentos, devuelve la suma del producto de sus argumentos. Aquí se trabaja con un solo argumento.

{3,4,5} son las columnas I, J y K en el rango de datos $G$19:$K$22 y son ajustables.

Ajuste las referencias de celda en la fórmula según sea necesario.

Respuesta3

Selecciona un área grande para la tabla VLookup para acomodar todos los elementos. digamos tomar 5000 filas. Ahora, cada vez que se agreguen nuevos elementos, vuelva a ordenar las dos columnas A y B en A, que contienen elementos. En el momento en que CLASIFICA, se comportará correctamente, así que no hay problema.

Gracias.

información relacionada