☰

Ich möchte die Funktion „Summewenn“ mit einer Dropdown-Liste verwenden, um Daten von einem Blatt in ein anderes zu übertragen

tag-icon • tag-icon microsoft-excel dropdownlist
Ich möchte die Funktion „Summewenn“ mit einer Dropdown-Liste verwenden, um Daten von einem Blatt in ein anderes zu übertragen

Ich habe Daten in Bild eins, die ich mithilfe einer Dropdown-Liste in Bild zwei sichtbar machen möchte, wobei sich das Ergebnis ändert, je nachdem, welcher Shop aus der Dropdown-Liste ausgewählt wird. Ich habe die Sumif-Funktion ausprobiert, aber es werden nur Daten für Shop 1 angezeigt, nicht für Shop 2. Bitte überprüfen Sie die Bilder zur Referenz und zur Korrektur der Formel.

Bildbeschreibung hier eingeben

Bild 1 mit Daten

Bildbeschreibung hier eingeben

Bild 2 mit Formel

Bildbeschreibung hier eingeben

Bild drei Shop2-Daten sind nicht sichtbar

Antwort1

So kann man das tun:

Bildbeschreibung hier eingeben

• In der Zelle verwendete FormelI4

=VLOOKUP($H4,INDIRECT($I$1),MATCH(I$3,HEADERS,0),0)
  • Definieren Sie einige Bereiche, damit die Formeln entsprechend der Dropdown-Liste funktionieren.
  • Bereich =$B$4:$F$7definiert alsSHOP1
  • Bereich =$B$11:$F$14definiert alsSHOP2
  • Da die Header für jeden gleich bleiben, SHOPSdefinieren Sie für einen der SHOPSals HEADERSfür=$B$3:$F$3
  • Als nächstes verwenden Sie VLOOKUP()die Formel wie oben gezeigt: Füllen Sie nach unten und nach rechts aus!

Wenn Sie jedoch beabsichtigen, zu verwenden, SUMIFS()empfehlen wir Ihnen, zu verwenden SUMPRODUCT()oder SUM()basierend auf IhrerExcel Version

Bildbeschreibung hier eingeben

• In der Zelle verwendete FormelI4

=SUMPRODUCT(INDIRECT($I$1)*(Criteria_Range=$H4)*(I$3=HEADERS))
  • Nehmen Sie einige Änderungen an den definierten Namen vor. Wie folgt:

  1. Bereich =$C$4:$F$7definiert alsSHOP1

  2. Bereich =$C$11:$F$14definiert alsSHOP2

  3. Bereich =$C$3:$F$3definiert alsHEADERS

  4. Bereich =$B$4:$B$7definiert alsCriteria_Range

  • Verwenden Sie nun die obige Formel und füllen Sie sie nach unten und rechts aus!

Vielleicht SUMIFS()bleiben die definierten benannten Bereiche gleich wie bei der ersten Methode mitVLOOKUP()

Bildbeschreibung hier eingeben

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

• Wenn Sie Zugriff darauf haben MS365, verwenden Sie Folgendes. Dabei handelt es sich um eine einzige Formel, die das gesamte Array überträgt:

=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))))

Vorbehalt:Die Verwendung von INDIRECT()Funktionen ist nicht empfehlenswert, da sie Volatilenaturgemäß die Arbeitsfunktionalität verlangsamt Excelund die Zellen bei jeder Änderung in einer geöffneten Arbeitsmappe neu berechnet. Sie sollten einige Änderungen an Ihrer Datenstruktur vornehmen, um Ihre Arbeit zu erleichtern, und die Verwendung flüchtiger Funktionen vermeiden.

Dies kann auch mit erreicht werden , das ab Windows und MAC POWER QUERYverfügbar ist .Excel 2010+

Bildbeschreibung hier eingeben

  • Konvertieren Sie zunächst die Quellbereiche in eine Tabelle und benennen Sie diese entsprechend. Für dieses Beispiel habe ich for SHOP1as SHOP_1, for SHOP2as SHOP_2und for Channelas benannt.Channeltbl
  • Öffnen Sie als Nächstes eine leere Abfrage über Datadie Tabulatortaste --> Get & Transform Data--> Get Data--> From Other Sources-->Blank Query
  • Das obige lässt das Power QueryFenster öffnet sich, jetzt von HomeTab --> Advanced Editor--> Und fügen Sie das Folgende ein, M-Codeindem Sie alles entfernen, was Sie sehen, und drücken SieDone

• Für die Ausgabe:

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"

• Für Kanal:

let
    Source = Excel.CurrentWorkbook(){[Name="Channeltbl"]}[Content],
    CHANNEL = Source{0}[CHANNEL]
in
    CHANNEL
  • Denken Sie daran, dass Sie das Obige in zwei verschiedene leere Abfragen einfügen müssen. Außerdem CHANNEList die Abfrage eine Verbindung, während die andere in das Blatt eingegeben werden muss.
  • Zum Schluss importieren Sie es wieder nach Excel--> Klicken Sie auf Close & Loadoder Close & Load To-->. Der erste Klick erstellt ein New Sheetmit der erforderlichen Ausgabe, während der letzte Klick ein Fenster öffnet, in dem Sie gefragt werden, wo das Ergebnis abgelegt werden soll.

verwandte Informationen