SVERWEIS, XLVERWEIS oder INDEX?

[murks]

Hallo liebe Gemeinde, was auch immer ich probiere, ich bekomme es nicht hin.

Excel-Version: LTSC Professional Plus 2024

Grundsätzliche Voraussetzungen (gilt für alle Tabellenblätter)

Ich habe 2 Tabellenblätter in denen es Zuordnungen von "KU" (Kunden) zu "MA" (Mitarbeiter) gibt. 

Es ist nicht möglich, dass ein Name in der Spalte KU auch als Name in der Spalte MA auftaucht.

Blatt 1 (Tabellenname: KU)

In Zeile 1 habe ich Überschriften (Spalte A: KU, Spalte B: MA, Spalte C: Anmerkung).

Ab Zeile 2 (bis unendlich) stehen dann die Namen der KU und der MA sowie eine Anmerkung dazu.

Jeder KU-Name taucht in diesem Blatt nur einmal auf. 

MA-Namen können jedoch auch mehrfach auftauchen.

Blatt 2 (Tabellenname: Ergebnis)

Auch hier gibt es die Bezeichnungen wie oben als Überschriften in Zeile 1, jedoch in anderen Spalten:

Spalte F: KU, Spalte G: MA, (Spalte H: Datum/Einsätze), Spalte I: Anmerkung (hier soll ab Feld I2 bis Formel rein).

KU-Namen können hier mehrfach vorkommen.

MA-Namen können hier ebenfalls mehrfach vorkommen.

Nicht alle MA- und KU- Namen aus Blatt 2 stehen auch zwingend in Blatt 1.

Aufgabenstellung

In Blatt 2 soll nun in Spalte I der Wert des Feldes aus Spalte C (Blatt 1, also die Anmerkung) zurückgegeben werden, wenn dort die Kombination aus Spalte F + G (Blatt 2) mit den Namen aus Spalte A + B (Blatt 1) matcht.

Das soll ab Feld I2 so sein und die Formel soll "runterziehbar" sein.

Vielen Dank!

[steve1da]

Hola,
ungetestet:

Code:

=XVERWEIS(F2&G2;KU!$A$2:$A$10&KU!$B$2:$B$10;KU!$C$2:$C$10)

Gruß,
steve1da

[murks]

Hola,
ungetestet:

Code:

=XVERWEIS(F2&G2;KU!$A$2:$A$10&KU!$B$2:$B$10;KU!$C$2:$C$10)

Gruß,
steve1da

das sieht schon mal sehr gut aus, vielen dank!
wie verhindere ich die #NV-einträge, wenn keine kombination gefunden wird? schön wäre, wenn dort dann z.B. "iV" stehen würde. danke!!

[steve1da]

Einfach mal die Hilfe zum Xverweis anschauen.

[Andreas Killer]

Code:

=XVERWEIS(F2&G2;KU!$A$2:$A$10&KU!$B$2:$B$10;KU!$C$2:$C$10)

Im Prinzip ja, aber ich würde das so nicht machen, aus diversen Gründen:

a) Eindeutigkeit F2&G2
Wenn man das macht, dann muss man bedenken das "ab" & "c" das gleiche Ergebnis bringt wie "a" & "bc" => "abc". Daher sollte man immer ein Trennzeichen wie "#" dazwischen machen.

b) Array  KU!$A$2:$A$10&KU!$B$2:$B$10
Wenn man das macht, dann muss man bedenken das die Formel zuerst beide Bereiche genau wie unter a) miteinander verbindet. Füllt man diese Formeln nach unten bedeutet das das in jeder Zeile die beiden Bereiche jedesmal verbunden werden.

Bei kleinen Datenmengen ist dieser Overhead vernachlässigbar, da jedoch gefordert wird Ab Zeile 2 (bis unendlich) müsste man die Bereiche bis Zeile 1048576 ausdehnen und dann wird es nicht mehr vernachlässigbar. Die Performance geht deutlich in die Knie => Datei wird langsam.

Will man das vermeiden, dann ist es ratsam eine Hilfsspalte in beiden Blättern hinzuzufügen in denen das Suchkriterium ganz simple ala =F2&"#"&G2 erzeugt wird. Danach kann man dann auch ganz einfach mit eine beliebigen Formel suchen, ohne irgendwelche Einbußen.

Andreas.

[murks]

Vielen Dank Euch! 

Wo das jetzt steht, die nächste Herausforderung, der ich irgendwie nicht gewachsen bin. :(

Ich habe es jetzt mit der Hilfsspalte gemacht und die neuen Voraussetzungen sind jetzt so:

Blatt 1 (Tabellenname: KU)

In Spalte A ist jetzt die Hilfsspalte (=B2&"#"&C2).
In Spalte B der KU-Name.
In Spalte C der MA-Name.
In Spalte D die Anmerkung.

In dieser Tabelle sind maximal 150 Zeilen gefüllt, meistens deutlich weniger.

Blatt 2 (Tabellenname: Ergebnis)

In Spalte E ist der KU-Name.
In Spalte F ist der MA-Name.
(In Spalte G sind die Einsatztage.)
In Spalte H ist die Formel und entsprechend die Anmerkungen aus Blatt 1/Spalte D (das habe ich jetzt mit einem einfach SVERWEIS gelöst und noch einen WENNFEHLER mit eingebaut, um das #NV zu verhindern).
In Spalte J ist die Hilfsspalte (=E2&"#"&F2).

Die Anzahl der Reihen unterhalb dieser Spalten E-J ist immer mal wieder unterschiedlich. Bestimmt wird sie durch die tatsächlichen Kombinationen aus KU und MA in einem bestimmten Zeitraum (1 Monat, aber für die Aufgabenstellung nicht relevant). Das wird durch Eindeutigkeits- und Textverketten-Formeln geregelt.

Das heißt, dass die letzte Zeile mit Einträgen hier variabel ist.

Aufgabenstellung

Ich möchte nun unterhalb dieser Tabelle in Blatt 2 noch die Kombinationen aus KU+MA aus Blatt 1 auflisten, die in Blatt 2 so nicht vorkommt.

Sprich: Es gibt eindeutige Standardzuweisungen aus KU+MA, die aus der Tabelle Blatt 1 hervorgehen. Im Regelfall taucht diese Kombination dann auch in Blatt 2 auf. 

Es kann aber auch sein, dass ein MA mal für den Zeitraum (also 1 Monat) nicht bei seinem "Stamm-KU" war und daher diese Kombination nicht in Blatt 2 vorkommt. Dennoch soll diese Kombination in der Tabelle in Blatt 2 auftauchen.

Meine Idee dazu ist: Da es in Blatt 2 nie mehr als 250 Kombinationen geben wird, würde ich mit etwas Puffer gerne ab Zeile 300 die fehlenden Kombinationen auflisten. Im Anschluss würde ich das ganze dann mittels Makro neu sortieren und die leeren Zeilen dazwischen enfternen (das krieg ich selber hin).

Ich benötige also eine Formel für die Spalte E (KU) und die Spalte F (MA) in Blatt 2, so dass hier alle in Blatt 2 fehlenden Kombinationen aus Blatt 1 ergänzt werden. Möglichweise ist dazu ja dann auch die Hilfsspalte (Blatt 2, Spalte J; Blatt 1, Spalte A) ganz hilfreich.

Vielen Dank!

[rate mit]

Hallo,

sieh' dir das mal an...


2 Spalten: Einträge ermitteln, die in beiden Spalten enthalten sind

2 Spalten: Einträge ermitteln, die nur in einer Spalte enthalten sind

[Andreas Killer]

Aufgabenstellung

Ich möchte nun unterhalb dieser Tabelle in Blatt 2 noch die Kombinationen aus KU+MA aus Blatt 1 auflisten, die in Blatt 2 so nicht vorkommt.

Sprich: Es gibt eindeutige Standardzuweisungen aus KU+MA, die aus der Tabelle Blatt 1 hervorgehen. Im Regelfall taucht diese Kombination dann auch in Blatt 2 auf. 

Das zu separieren ist mit einigem Aufwand verbunden... Du musst ja schauen was schon an Kombinationen da ist und was noch möglich wäre... würde ich nicht machen.

Einfacher wäre es immer alle möglichen Kombinationen in einer Tabelle in Blatt 2 zu haben, dann sich die Daten aus Blatt 1 zu ziehen. Wenn es die Kombination in Blatt 1 nicht gibt kommt es zu einem Fehler, diese filtere ich weg und habe das gewünschte Ergebnis.

Wäre das ein gangbarer Weg?

Andreas.

[murks]

Danke, ich versuche das, wenn ich nächste Woche wieder an dem Arbeitsplatz bin, wo das benötigt wird.