Quiero usar la función sumifs con una lista desplegable para completar datos de una hoja a otra

Question

Tengo datos en la imagen uno que quiero que sean visibles en la imagen dos con el uso de una lista desplegable, donde el resultado cambia según la tienda seleccionada en la lista desplegable. Probé los sumifs pero solo muestra datos para la tienda 1, no para la tienda 2. Verifique las imágenes para referencia y corrección de fórmula.

Imagen 1 con datos

Imagen 2 con fórmula

imagen tres datos de shop2 no son visibles

Answer 1

Aquí hay una forma de hacerlo:

---

• Fórmula utilizada en la celdaI4

=VLOOKUP($H4,INDIRECT($I$1),MATCH(I$3,HEADERS,0),0)

---

• Defina algunos rangos para que las fórmulas funcionen en consecuencia con respecto al menú desplegable.
• Rango =$B$4:$F$7definido comoSHOP1
• Rango =$B$11:$F$14definido comoSHOP2
• Dado que los encabezados siguen siendo los mismos para cada uno SHOPS, defina para uno de los SHOPSmismos que HEADERSpara=$B$3:$F$3
• A continuación, use VLOOKUP()la fórmula como se muestra arriba, complete y complete correctamente.

---

Sin embargo, si tiene la intención de utilizar, SUMIFS()le sugerimos que utilice SUMPRODUCT()o SUM()según suExcel Version

---

• Fórmula utilizada en la celdaI4

=SUMPRODUCT(INDIRECT($I$1)*(Criteria_Range=$H4)*(I$3=HEADERS))

---

• Realice algunos cambios en los nombres definidos. Según lo siguiente:

1. Rango =$C$4:$F$7definido comoSHOP1

2. Rango =$C$11:$F$14definido comoSHOP2

3. Rango =$C$3:$F$3definido comoHEADERS

4. Rango =$B$4:$B$7definido comoCriteria_Range

• ¡Ahora, use la fórmula anterior y complete y complete correctamente!

---

Quizás el uso de SUMIFS()los rangos con nombre definidos siga siendo el mismo que el primer método que usaVLOOKUP()

---

=SUMIFS(INDEX(INDIRECT($I$1),,MATCH(I$3,HEADERS,0)),INDEX(INDIRECT($I$1),,1),$H4)

---

• Si tiene acceso, MS365utilice lo siguiente, es una única fórmula que derramará toda la matriz:

=LET(
     _Data, VSTACK(EXPAND(SHOP1,,6,"SHOP1"),EXPAND(SHOP2,,6,"SHOP2")),
     _Filtered, FILTER(_Data, TAKE(_Data,,-1)=I1),
     MAKEARRAY(ROWS(H22:H24), COLUMNS(I21:L21), LAMBDA(r,c,
     VLOOKUP(INDEX(H22:H24,r),_Filtered,XMATCH(INDEX(I21:L21,c),HEADERS),0))))

---

Advertencia:El uso de INDIRECT()la función no es sugerente, ya que es Volatilenatural, ralentiza la funcionalidad de trabajo Excely seguirá recalculando las celdas cada vez que haya un cambio en cualquier libro abierto. Debe realizar algunos cambios en su estructura de datos para facilitar su trabajo evitando el uso de funciones volátiles.

---

Esto también se puede lograr utilizando POWER QUERYel que está disponible a Excel 2010+partir de Windows y MAC.

---

• Primero convierta los rangos de origen en una tabla y asígnele el nombre correspondiente; para este ejemplo, he nombrado for SHOP1as SHOP_1, for SHOP2as SHOP_2y for Channelas.Channeltbl

---

• A continuación, abra una consulta en blanco desde DataTab --> Get & Transform Data--> Get Data--> From Other Sources-->Blank Query

---

• Lo anterior permite que Power Queryse abra la ventana, ahora desde HomeTab --> Advanced Editor--> Y pega lo siguiente M-Codeeliminando lo que veas, y presionaDone

---

• Para la salida:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "SHOP")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Expense Type", "January", "February", "March", "April"}, {"Expense Type", "January", "February", "March", "April"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Content", each ([Name] = CHANNEL)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each [Expense Type] <> "Total Budget")
in
    #"Filtered Rows2"

---

• Para canal:

let
    Source = Excel.CurrentWorkbook(){[Name="Channeltbl"]}[Content],
    CHANNEL = Source{0}[CHANNEL]
in
    CHANNEL

---

• Recuerde, debe pegar lo anterior en dos consultas en blanco diferentes. Además, la CHANNELconsulta será una conexión mientras que la otra deberá ingresarse en la hoja.

---

• Por último, para volver a importarlo a Excel--> Haga clic en Close & Loado Close & Load To--> El primero que hizo clic creará un archivo New Sheetcon el resultado requerido, mientras que el segundo mostrará una ventana que le preguntará dónde colocar el resultado.

---