Quelle: https://www.office-hilfe.com/support/threads/auszaehlen-nach-mehreren-kriterien-mit-oder.59395/
Stand: 05.11.2024 02:30
Hier der Eröffnungsbeitrag aus dem Forum:
Liebe Excel-Community, danke vorab für Hilfe bei diesem Fall:
In meiner Ausgangstabelle (siehe Screenshot A) wird für eine Reihe von Projektanträgen (Spalte A) jeweils aufgelistet, welchen Diszplinen der Projektantrag zugeordnet ist (Spalten B bis F) und von welchen Partneruniversitäten er eingebracht wurde (Spalten G bis I).
Nun suche ich eine Formel, die mir ausrechnet, an wievielen Anträgen mit der Disziplinen-Zuordnung „X” eine bestimmte Universität „Y” insgesamt beteiligt war (also über alle Projektanträge zusammengerechnet). Beispiel: die Universität Frankfurt ist an insgesamt 3 Projektanträgen beteiligt, die (ausschließlich oder unter anderem) die Disziplinenzuordnung „Politikwissenschaft” haben. Sie ist analog dazu an 3 Projektanträgen mit der Zuordnung „Geschichte” beteiligt, an 4 mit „VWL”, an 2 mit „Soziologie” und an 2 mit „BWL”. Gegenbeispiel: die Uni Berlin ist nur an 1 Projektantrag mit der Disziplin „BWL” beteiligt.
Ich möchte das in einer Ergebnistabelle darstellen, und zwar für jede Universität und alle Disziplinen ausgerechnet (siehe Screenshot B). Beispiel: in Zelle B2 der Ergebnistabelle soll eine Formel stehen, die in der Ausgangstabelle alle Zeilen zählt, die SOWOHL „Universität Frankurt” enthalten (egal, ob in der Spalte G, H oder I) ALS AUCH „Politikwissenschaft” (egal ob in Spalte B, C, D, E oder F). In Zelle C2 soll eine Formel stehen, die das Ganze für die Uni Frankfurt und die Disziplin „Geschichte” auszählt, usw. usf.
Wie die Ausgangstabelle zeigt, kann es sowohl vorkommen, dass einem Projektantrag weniger als 5 Disziplinen zugerechnet sind (es gibt also leere Felder) als auch, dass eine Universität bei einer bestimmten Disziplin gar keine Beteiligung (das Ergebnis also „0” lauten muss…)
DANKE VORAB und liebe Grüße!
P.S.: Arbeite mit MS Office 2021]
Hinweis: In Beitrag #3 hat der Fragesteller diese *.xlsx zum Download angeboten. 🤩
Apropos „Kreuztabelle“: Auch die Quelldaten liegen als Kreuztabelle vor; allein das ist ja schon ein Grund, dass eine direkte Auswertung per PivotTable nicht denkbar ist. Meine Lösung ist einmalig mit etwas mehr Aufwand verbunden, dafür aber recht transparent und vor allen Dingen ausschließlich per Mausklicks, ohne eine einzige händisch eingegebene Formel bzw. Funktion zu verwenden.
Im Endeffekt soll für 11 Projektanträge berechnet werden, welche der 8 erfassten Universitäten wie viele der 5 aufgeführten Disziplinen angeboten werden (beteiligt sind). Da für mich aus der (etwas fragmentarischen) Musterlösung keine (logische) Rangfolge erkennbar ist, habe ich die Reihenfolge PQ überlassen. Mit einer weiteren Abfrage kann via Start | Kombinieren eine Wunsch-Reihenfolge deklariert werden.
Ich arbeite mit 3 Abfragen (Queries), wobei die Rohdaten (RawData) und die Disziplinen als Nur Verbindung gespeichert sind, nur die Abfrage mit den Partner-Unis (Ergebnis) wird direkt an die Zielposition geschrieben. – Hier nun meine 3 M‑Codeblöcke:
RawData
let
Quelle = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Projektantrag", type text}, {"Disziplin 1", type text}, {"Disziplin 2", type text}, {"Disziplin 3", type text}, {"Disziplin 4", type text}, {"Disziplin 5", type text}, {"Partner 1", type text}, {"Partner 2", type text}, {"Partner 3", type text}})
in
#"Geänderter Typ"
Disziplinen
let
Quelle = RawData,
#"Entfernte Spalten" = Table.RemoveColumns(Quelle,{"Partner 1", "Partner 2", "Partner 3"}),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Entfernte Spalten", {"Projektantrag"}, "Attribut", "Wert"),
// Die Spalte 'Attribut' wird hier nicht mehr gebraucht
#"Entfernte Spalten1" = Table.RemoveColumns(#"Entpivotierte andere Spalten",{"Attribut"})
in
#"Entfernte Spalten1"
Auswertung
let
Quelle = RawData,
#"Entfernte Spalten" = Table.RemoveColumns(Quelle,{"Disziplin 1", "Disziplin 2", "Disziplin 3", "Disziplin 4", "Disziplin 5"}),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Entfernte Spalten", {"Projektantrag"}, "Attribut", "Wert"),
#"Entfernte Spalten1" = Table.RemoveColumns(#"Entpivotierte andere Spalten",{"Attribut"}),
#"Zusammengeführte Abfragen" = Table.NestedJoin(#"Entfernte Spalten1", {"Projektantrag"}, Disziplinen, {"Projektantrag"}, "Disziplinen", JoinKind.LeftOuter),
#"Erweiterte Disziplinen" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Disziplinen", {"Wert"}, {"Wert.1"}),
#"Gruppierte Zeilen" = Table.Group(#"Erweiterte Disziplinen", {"Wert", "Wert.1"}, {{"Anzahl", each Table.RowCount(_), Int64.Type}}),
#"Pivotierte Spalte" = Table.Pivot(#"Gruppierte Zeilen", List.Distinct(#"Gruppierte Zeilen"[Wert.1]), "Wert.1", "Anzahl", List.Sum),
#"Umbenannte Spalten" = Table.RenameColumns(#"Pivotierte Spalte",{{"Wert", "Hochschule"}})
in
#"Umbenannte Spalten"
Bleibt noch der Hinweis, dass ich die beiden Queries Disziplinen und Auswertung per Verweis erstellt/verknüpft habe (darum ist die erste Zeile (nach dem let) des jweiligen M-Codes auch Quelle = RawData).
Und hier, beim Stichwort last but not least finden Sie noch einen Hinweis, wo und wie Sie Antworten auf eventuelle Fragen oder auch die ausgearbeitete Datei bekommen können. Grundlegendes zum Thema M-Code lesen Sie hier im Blog nach.