SVERWEIS über Werte aus Tabelle statt neuer Matrix?

[aero]

Liebe Community,

der SVERWEIS treibt mich noch immer um, aber immer wieder mit anderen Problemen.

Ich habe eine sehr große Tabelle mit Mitarbeiternamen und entsprechender Zuständigkeit. In der beigefügten Datei ist es vereinfacht dargestellt über Tabelle 1. Die Zuständigkeiten sind in Spalte B zusammengefasst (wobei teilweise auch noch Text mit in den Zellen steht). Bisher hatte ich es so gelöst, dass ich manuell eine eigene Tabelle mit den Zuständigkeiten aufgebaut habe (siehe Spalte D und E) und über die dann mittels SVERWEIS die Sache gelöst habe (Ergebnis in Spalte G und H).

Gern möchte ich mir das Erstellen der Extra-Tabelle bei großen Zuständigkeiten (1-5.000) sparen und gern direkt den SVERWEIS über die 1. Tabelle (Spalte A und B) generieren. Ist das möglich oder komme ich um die Tabelle 2 (Spalte D und E) nie herum?

Danke für eure Hinweise!

 

[Fortatus]

Möchtest du eine Excel-Datei mit Makros vermeiden? Müssen die Zuständigkeiten als Ranges angegeben werden (z.B. "1-5,16,36") oder können alle explizit gelistet werden? (z.B. "1,2,3,4,5,16,36")?

Wenn Makros erlaubt sind: Gar kein Problem.
Wenn keine Makros erlaubt sind, aber alle Zuständigkeiten einzeln aufgeführt sind: Sollte möglich sein
Weder Makros erlaubt, noch alle Zuständigkeiten einzeln gelistet werden können: Dann wird es schwierig

Können alle Zuständigkeiten in allen Kombinationen auftauchen? Oder gibt es zusätzlich "Zuständigkeitsgruppen", um häufige Zuständigkeitskombinationen zusammenzufassen? Das würde die Auswertung beschleunigen und die Datenverarbeitung besser durchschaubar machen.

 

[aero]

Da ich mich mit Makros nicht auskenne und die Tabelle nicht ewig betreuen werde, möchte ich das, wenn möglich, vermeiden.

Die Zellen mit den Zuständigkeitsaufzählungen können nicht so aufgebaut werden, dass dort hunderte Zahlen stehen - es muss also bei "3-485, 500-600, 605" etc bleiben...

 

[Fortatus]

Wieso müssen die Zuständigkeitsaufzählungen so zusammengefasst bleiben? Ein wenig mehr Infos zum System, das um das Excel herum besteht, wären wohl nicht schlecht. Nur um ein X-Y-Problem zu vermeiden.

Außerdem: Auch die bisherige Version gibt, so wie ich es sehe, immer nur einen (den ersten) Mitarbeiter aus, der eine Zuständigkeit hat. Können Zuständigkeiten doppelt vorkommen? Ist es erforderlich, dass alle Mitarbeiter ausgegeben werden? Dann wird es umso schwerer.

 

[aero]

Es ist eine sehr große Excel-Tabelle vorhanden. Das 1. Blatt listet die verschiedenen Teams und den Zuständigkeitsbereich an (z.B. Team 1: 1-499, Team 2: 500-999 etc.). Klickt man auf ein Team springt man je Team in ein neues Blatt mit der Auflistung der Mitarbeiter und der konkreten Nr., die dieser bearbeitet (das ist mein Beispiel). Es gibt noch viele weitere Blätter, so dass die Excel-Datei bereits sehr groß im Umfang ist. Ich möchte daher nichts weiter aufblähen. Diese Zellen mit den Zuständigkeitsangaben sind also einfach als fest zu betrachten.

Mein Ziel ist es, dass ich über die Eingfabe der Nr. sofort sehe, wer für diese zuständig ist, aber auch - und das ist mir eben wichtig - dass über die Aktualisierung der Zelle (statt 100-150 ändert man es z.B. zu 100-150) die Zuständigkeit direkt mit SVERWEIS zugeordnet wird. Aktuell habe ich eine extra-Tabelle (siehe Spalte D und E) angelegt, aber die ist bereits hunderte von Spalten tief und Änderungen der Zuordnung muss ich aktuell immer per Hand eintragen. ich hoffe, du verstehst, was ich meine....

 

[Fortatus]

Ok, siehst du, das macht es wesentlich eindeutiger.

Es gibt also bereits eine Zuständigkeitshierachie ("Zuständigkeitsgruppen"), die wir verwenden können. Ist das eine Datei mit mehreren Tabellen? Oder mehrere Dateien?

Die Frage bleibt bestehen: Wenn ein Mitarbeiter in einem Team eine bestimmte Zuständigkeit hat, (also bspw. Mitarbeiter A hat Zuständigkeit 475 in Team C ("Zuständigkeit 400-500"), wie wird diese Sub-Zuständigkeit gespeichert? Kann, wenn Mitarbeiter A die Zuständigkeiten 1,2,3,4,5 in Team A (Zuständigkeiten 1-73) ausführt, diese einzeln gespeichert werden? Falls nicht, wieso?

Beispiel: Ein Mitarbeiter hat eben nicht nur eine Zuständigkeit, sondern auch ein Team. Wenn er/sie in Team A ist, heißt das noch nicht, dass er auch eine Zuständigkeit hat (außer es gibt "Standardzuständigkeiten"). Wenn er in Team A (1-73) die Zuständigkeit 5 hat, und dann in Team C verschoben wird, verfällt dann automatisch auch die Zuständigkeit 5? Oder kann diese bestehen bleiben?

Kann ein Mitarbeiter in mehreren (unabhängigen) Teams sein? Oder hat ein Mitarbeiter immer nur ein Team? Gibt es eine Team-Hierachie? (Abteilungsleiter X ist zuständig für Teams A, B und C, aber nicht für Team D?)


Ich würde aktuell dazu tendieren, die Teams/Hierachie und die dafür automatisch notwendigen Daten ausnutzen.


/edit: Bisher hast du Teams (Gruppen von Zuständigkeiten), denen Mitarbeiter zugeordnet sind. Diesen Mitarbeitern ordnest du dann wieder Zuständigkeiten zu. Du wechselst also die Ebene zweimal (Von Zuständigkeitsgruppe->Zuständigkeitsinhaber->feinere Zuständigkeit)
Wieso kannst du nicht von vorneherein Teamzuständigkeit->feinere Zuständigkeit->Inhaber der Zuständigkeit zur Datenmodellierung nutzen. Das würde es vereinfachen.

 

[aero]

@Fortatus
Einige Mitarbeiter haben Sonderfunktionen, d. h. sie betreuen neben einigen Nr. noch ganz andere Dingen oder eben nur ganz andere Dinge - da steht dann in der Zelle: "10-20 und Zuständigkeit Bereich XYZ".
Ein Mitarbeiter kann immer nur in einem Team aufgeführt werden. Regelmäßig kann es vorkommen, dass die Zuständigkeiten unter den einzelnen Mitgliedern des Teams wechseln oder neuen Mitglieder in das Team kommen und diese dann die einzelnen Nr. des Teams neu unter den nun vorhandenen Mitgliedern des Teams aufteilen - die aktuelle Aufteilung werden dann immer, wie in meinem Beispiel in Spalte B, aktualisiert.

Zu deinem Edit: Ja, gern, aber wie? Ich stehe auf dem Schlauch

 

[Fortatus]

Ich stehe auf dem Schlauch

Ich leider auch. Mir ist immer noch nicht klar, wer diese Excel-Liste wie benutzt.

Aber als Zusammenfassung dessen, was ich aktuell zu verstehen glaube & dazu denke:
So wie die Daten aktuell gespeichert werden (z.B. "1,3,5,10-25,27,50-99") denke ich, dass ein schnelles Suchen über Formeln nicht möglich ist.
Sofern man über Formeln die Ranges ("10-25" als "10,11,12,13,14,usw") auswerten kann, dann sind werden die Formeln sehr komplex und am Ende schwerer zu warten und wesentlich fehleranfälliger, als dafür Makros zu programmieren.
Wenn die Modellierung der Daten verändert werden könnte (z.B. indem nicht den Mitarbeitern die Zuständigkeiten zugeordnet werden, sondern genau andersrum den Zuständigkeiten jeweils ein Mitarbeiter zugeordnet wäre), wäre das ganze viel einfacher.

Aktuell würde ich also eher dazu tendieren, in deiner Beispieldatei die Liste in den Spalten A:B wegzuwerfen und das gesamte System rund um die Spalten D:E aufzubauen. Wenn die Teamzugehörigkeit keinen Einfluss auf die Zuständigkeit hat (d.h. nur weil Team A für 1-25 zuständig ist, ist ein Mitglied von Team A nicht automatisch auch für 1-25 zuständig), hat das Mitführen dieser Daten wenig Sinn.

 

[ohmsl]

Hallo,
sehe ich so wie Fortatus. Wie wäre es so vereinfacht:
2 Spalten von bis, zusätzliche Spalte für Text. Wobei die Bis-Spalte nur der Optik dient. Kann auch weggelassen werden.

siehe Anlage.

 

[aero]

@Fortatus und @ohmsl

Danke für eure TIpps, ich denke, ich bleibe dann doch bei Spalte D und E. Die Idee von ohmsi hatte ich auch schon, aber da die Zuständigkeiten wechseln können, gibt das ein Kuddel-Muddel - dann lieber für jede Nr. einfach eine Zelle (Spalte E) und diese wird dann ggf. mit den neue Zuständigkeiten aktualisiert per Hand.

Ein anderes Problem, was ich habe: Ich habe ja bereits beschrieben, dass pro Team ein eigenes Blatt mit dessen Mitarbeitern und den Zuständigkeiten (Nr.) vorliegt. Ich möchte ja die Sache nun über den SVERWEIS machen und dafür die ganzen Team-Blätter zu einer Gesamtliste zusammenführen, um darüber dann den SVERWEIS zu fahren. Sprich: Die Teamzuständigkeiten werden immer im zuständigen Team-Blatt aktualisiert (das soll so bleiben) und diese Aktualisierungen sollen dann direkt in diese Gesamtliste, die ich in einem neuen Blatt (quasi im Hintergrund) erstelle, übertragen werden.

Nun überlege ich, wie ich die ganzen Tabellen der Teams in ein neues Gesamtlistenblatt spiegeln kann. Ich hatte bisher einfach geschrieben (z. B. für Zelle A5 im Gesamtlistenblatt):

=Tabelle_Team1!A1

und dann entsprechend das für jede Zelle kopiert. Ist das der beste Weg? ich hatte etwas mit der INDEX-Funktion gefunden (Link1, Link2), bekomme da aber nicht das gewünschte Ergebnis. Gern würde ich einfach sagen: spiegle hier (z. B. Blatt2, A1) von Blatt1 alle Zellen von A5:d10 ein und wenn in Blatt1 Daten aktualisiert werden, soll das in Blatt 2 automatisch auch aktualisiert werden.

 

[ohmsl]

Die Teamzuständigkeiten werden immer im zuständigen Team-Blatt aktualisiert (das soll so bleiben) .

Ist eher suboptimal. Besser eine Haupttabelle und daraus die Teamzuständigkeiten erstellen (evtl. auch per Pivot oder Power Query).
Haupttabelle um die jeweilige Teamzugehörigkeit des Mitarbeiters ergänzen.

Je nachdem wie die Teamtabellen aussehen, könntest du auch eine Zusammenführung per Power Query prüfen.

 

[Alex2005]

Hast du mal eine Beispieldateo? Ich hab da eine Idee, bräuchte aber ne Vorlage, genauso wie du es im orig. machen willst