Office: Pivotdatenzuordnen #BEZUG!

Hi,
ich möchte gerne eine Raumübersicht erstellen, wo in jedem Raum entsprechend angezeigt wird, wer dort sitzt. Die Daten dafür kommen aus einem anderen Tabellenblatt der selben Datei. Zunächst habe ich das mit dem SVerweis versucht, hatte dort jedoch das Problem, dass bei Räumen, in denen mehrere Personen sitzen, immer nur eine der Personen angezeigt wurde.
Ich wollte das Problem mit PIVOTDATENZUORDNEN lösen und habe entsprechen zunächst eine PivotTable erstellt mit den Räumen und den darin sitzenden Personen. Wenn ich jetzt die Formel

=PIVOTDATENZUORDNEN("Mitarbeiter"; PivotTable; "Raum";"(Raumnummer)")

in die Zielzelle eingebe, bekomme ich leider #BEZUG! zurück. Woran kann das liegen?

Angehängt ist eine Beispieldatei mit einer vereinfachten Darstellung. Dort möchte ich beispielhaft im Tabellenblatt "Raumübersicht" in einer Zelle angezeigt bekommen, wer in Raum 115 sitzt.
Dabei ist wichtig, dass Aktualisierungen der Quelltabelle in die Raumübersicht übernommen werden.

Wo liegt mein Fehler?

Danke für eure Hilfe

 

Hallo,

wenn du im Besitz von Excel365 Insider im Betakanal wärst, wäre die Formel ganz einfach:
=GRUPPIERENNACH(Tabelle1[Raum];Tabelle1[Mitarbeiter];MATRIXZUTEXT;0;0)
Die abgefragten Daten würden mit dieser Formel wie folgt aussehen:

 

Hallo,

danke dir für deine Antwort! Leider habe ich vergessen dazu zu erwähnen, dass sich das ganze in Excel 2019 realisieren lassen muss. Also fällt die Lösung wohl leider raus

Trotzdem danke! Vielleicht gibt es ja noch eine andere Lösungsmöglichkeit

 

Hola,
ginge in 2019 zB so:
=WENNFEHLER(INDEX(Raumliste!$C$3:$C$6;AGGREGAT(15;6;ZEILE(Raumliste!$C$3:$C$6)-2/(Raumliste!$B$3:$B$6=115);ZEILE(A1)));"")
Gruß,
steve1da

 

Heyho,

danke auch dir für deine Antwort! Ich habe es mal ausprobiert, leider wird mir auch in diesem Fall leider nur ein Name angezeigt. Also auch, wenn in einem Raum mehrere Personen sitzen. Das Ergebnis ist quasi genau das selbe, wie wenn ich es mit dem SVerweis versuche.

Bin echt langsam am verzweifeln. Aber danke an euch alle die ihr versucht zu helfen!

 

Du musst die Formel schon noch nach unten ziehen ;-)

 

Deshalb hab ich mich hier auch OfficeBeginner genannt
Danke dir, jetzt klappt es tatsächlich!! Warst mir eine enorme Hilfe und du hast mir eine Woche der Verzweiflung erspart. Danke!!

 

Gerne :)

 

:-)

Ich hab noch eine Frage: Gibt es eine Möglichkeit, dass die Ergebnisse alle in einer Zelle angezeigt werden? Wäre für den Zweck der automatischen Aktualisierung denke ich sinnvoller, wenn man bedenkt, dass sich die Anzahl der Personen in einem Raum auch ändern kann :)

 

Okay Mist, ich hab mich leider wohl zu früh gefreut :-) und hab mich wohl auch ein wenig missverständlich ausgedrückt..

Habe nämlich in meiner endgültigen Datei mal ein wenig herumprobiert und bin nicht zu dem Ergebnis gekommen, das ich wollte. Es sieht so aus, als wäre deine wirklich gute Formel konkret für den Sachverhalt in der Beispieldatei. Was ich eigentlich suche ist eine allgemeingültige Formel, bei der ich immer jeweils nur die Raumnummer ändern muss. Die Formel soll quasi abfragen "Welche Daten gehören zu Raum XY". In jeden Raum in meinem Modell möchte ich diese Formel dann reinkopieren und entsprechend die Raumnummer ändern, sodass mir letztendlich alle Personen in diesem Raum in einer Zelle angezeigt werden. Ich hoffe das ist irgendwie möglich

 

Hallo,
du hast im Arbeitsblatt "Raumliste" eine strukturierte Tabelle namens "Tabelle1" mit den Spalten "Raum", "Mitarbeiter" und "Funktion", wobei diese Spaltenköpfe sich in Zeile 2 des Arbeitsblattes befinden.

Du kannst die Formel von steve1da an diese strukt.Tabelle anpassen wie folgt:
=WENNFEHLER(INDEX(Tabelle1[Mitarbeiter]; AGGREGAT(15;6;ZEILE(Tabelle1[Mitarbeiter])-2/(Tabelle1[Raum]=115); ZEILE(A1)));"")
Die 115 in dieser Formel ist genau die Raumnummer
Ferner ist zu beachten, dass auch noch eine 2 vorkommt. Diese 2 ist die Zeilennummer, in der der Tabellenkopf von "Tabelle1" im Arbeitsblatt enthalten ist. Falls du diese Tabelle in einer anderen Zeilennummer beginnen lässt, musst du auch diese Zahl anpassen.

Ich würde dir empfehlen, die Formel auf diese strukturierte Form umzustellen, weil sich dann die Formelbereiche automatisch an veränderte Tabellengrößen anpassen.

 

Leider führt alles bisher versuchte nicht zum gewünschten Ergebnis. Ich verstehe nach wie vor nicht, warum es mit PIVOTDATENZUORDNEN nicht funktioniert. Eigentlich wäre eine PivotTable perfekt für meinen Anwendungsfall, weil diese ja genau erkennt "Das sind die Werte "Mitarbeiter" zu diesem speziellen Wert "Raum"". Aber irgendwie komm ich nicht dahin

 

Hallo,

du hast für die PivotTable zwar Zeilen- und Spaltenkriterien definiert, aber - und das ist das Entscheidende - keinen Wertebereich samt Aggregierungsfunktion ("Anzahl von ...", "Summe von ...", etc.). Damit hast du in Wahrheit keine PivotTable-Daten produziert. Die Funktion "PIVOTDATENZUORDNEN(...)" benötigt aber den Datenbereich. Somit ist es nicht verwunderlich, dass eine Fehlermeldung erscheint.

Aber die Funktion von @steve1da bzw. meine modifizierte Funktion müssten doch funktionieren.
Eine solche zeilenmäßige Auflistung von Texten je Raum wie in meinem Posting #2 ist nur mit meiner neuesten Version möglich (oder aber mit PowerQuery).

 

Aah okay, danke für deine Erklärung bezüglich der Pivottabelle. Zeigt, dass ich das ganze System dahinter wohl noch nicht ganz verstanden habe.

Und ich habe es mit eurer Formel jetzt zum Glück auch hinbekommen. Ich hatte lediglich einen Fehler drin. Statt einer Festen Raumnummer (z.B.115) habe ich dort einen Verweis auf eine Zelle eingefügt, in dem die Raumnummer steht, nach der gesucht werden soll. Beim "runterziehen" der Funktion hat sich aber der Zellverweis immer um eine Zelle nach unten verschoben, sodass dann nicht mehr nach dem gewünschten Raum gesucht wurde. Wenn ich den Raum aber als festen Wert eingebe (also wie ihr es hier auch gemacht habt) dann klappt es.

Damit sollte mein Problem hoffentlich endgültig gelöst sein! Die automatische Aktualisierung funktioniert auch, das habe ich ausprobiert.

Danke euch! :-)

 

Hallo,

eine Zellangabe, in der zB. die Raumnummer steht, in der Formel ist durchaus erlaubt. Man muss nicht unbedingt eine Zahlenkonstante angeben. Du musst nur dafür sorgen, dass sich die Zellangabe beim Kopieren der Formel nicht verändert/verschiebt. Angenommen in Zelle B17 steht die abzufragende Raumnummer (115), dann musst du halt diese Zelladresse absolut setzen, indem du $-Zeichen einfügst: B$17 bewirkt, dass sich die Zeilennummer (=17) beim Kopieren in die Zeilen unterhalb nicht mit verschiebt, $B$17 setzt Spalte und Zeile absolut fürs Formelkopieren, $B17 setzt nur die Spalte absolut zB. beim Formelkopieren nach rechts.