Office: "VERKETTEN(x;Y)" als Suchkriterium (Excel2007)

Hallo!

Ich habe folgende Problemstellung:

Ich möchte in einer Tabellenzelle (Zeile "Projekt", Spalte "Nachname") einen Wert ausgeben, wenn der Wert "ProjektNachname" in einer anderen Tabelle auftaucht.

Die Formel "=SVERWEIS(VERKETTEN($B5;D$4);Daten!A1:A3000;2;0)" funktioniert leider nicht, wohl weil ich den Verketten-Befehl nicht als Suchkriterium verwenden darf.

Bzw. das eigentliche Problem:
Ich habe eine "Ausgabetabelle", in den Zeilen die Projekte, in den Spalten die Namen.

In einer zweiten Tabelle finden sich in einer Spalte die Projektnamen (mehrfach) wieder, sollte aber in einer anderen Spalte, in der der Projektname steht, auch ein Nachname stehen, dann soll in der "Ausgabetabelle" in das entsprechende Feld beispielsweise ein "x" eingetragen werden. Wie kann ich das umsetzen?

Besten Dank im Voraus!

 

Hallo!

Du darfst sehr wohl das Suchkriterium durch VERKETTEN darstellen, das macht dem SVERWEIS nix, aber:
Deine Suchmatrix Daten!A1:A3000 ist EINspaltig.
Laut deiner Formel soll aber der Wert aus der ZWEITEN Spalte zurück gegeben werden. Das kann nicht klappen!

Vielleicht löst das schon dein Problem.
Falls nicht, wäre eine hier hochgeladene Beispieldatei sicher hilfreich, denn irgendwie habe ich Schwierigkeiten, mir deinen Aufbau vorzustellen.

 

Der Wert "1" funktioniert, habe es ausprobiert. Ich bekomme mit der Formel "=SVERWEIS(VERKETTEN($B7;R$4);Daten!$A$1:$B$3000;1;0)" eine Rückmeldung, allerdings für jedes Feld ohne Übereinstimmung einen Fehler. Wie kann ich die Fehlermeldung "abschalten"?

Beispieltabelle werde ich bei Gelegenheit vorbereiten, die Originale kann ich aus Datenschutzgründen leider nicht hochladen.

Grob gesagt lese ich mittels Batch-Datei die Ordnerstruktur aus einem Laufwerk aus, das entstehende Textfile ist als Datenquelle mit Excel verknüpft. In dieser Ordnerstruktur sind für jedes Projekt ein eigener Ordner, darin befinden sich wiederum für jeden am Projekt beteiligten Mitarbeiter Unterordner (und weitere, für meine Übersicht nicht benötigte Unterordner).

Ich möchte nun daraus eine Tabelle erstellen, wo man auf einen Blick sieht, welcher Mitarbeiter an welchem Projekt beteiligt ist.

Im Excel sieht das eingelesene Textfile so aus, das Jede Hirarchie eine Spalte besitzt (Als Trennzeichen einfach "\" benutzt).

 

Die Fehlerabschaltung geht so

=WENN(ISTFEHLER(Deine_Formel);"";Deine_Formel)

Unter Excel2007 auch kürzer

=WENNFEHLER(Deine_Formel;"")
Allerdings ist die Funktion WENNFEHLER nicht abwärtskompatibel zu Excel2003 oder früher.

 

PS:

Die Formel

=WENN(SVERWEIS(VERKETTEN($B16;AI$4);Daten!$A$1:$B$3000;1;0)=0;"nein";"ja")

bringt mir das gewünschte Ergebnis bei Übereinstimmung, allerdings steht immer noch "#NV", wenn kein Wert gefunden wurde. Was muss ich ändern?

Wenn das geht, habe ich die Grundfunktion, nach der ich suche.

(Aber über den Umweg, das ich für meine eingelesenen Daten eine zusätzliche Spalte mit der Verkettung der Spalte Projektordnername und Mitarbeitername benötige, da SVERWEIS nur nach einem Suchkriterium sucht)

 

Danke!

Ist zwar sicherlich alles andere als die eleganteste Lösung, aber für heute erstmal okay! )

 

Hallo!

=WENN(ISTFEHLER(SVERWEIS(VERKETTEN($B7;R$4);Daten!$A$1:$B$3000;1;0));"";SVERWEIS(VERKETTEN($B7;R$4);Daten!$A$1:$B$3000;1;0))

Den Ausdruck "VERKETTEN($B7;R$4)" kannst du auch schreiben als "$B7&R$4". Dann wird's etwas kürzer.

 

Hallo!

Ich habe mal ein vereinfachtes Beispiel erstellt.
Enthalten sind die bis jetzt besprochenen Punkte.

http://home.arcor.de/woerli/Projekte

Wie gesagt, die gewünschte Grundfunktion geht auf dem besprochenen Weg. Es gibt aber sicher elegantere Lösungen die gewünschten Informationen herauszufiltern.

Was ich noch machen muss ist ein Makro zur automatischen Aktualisierung der eingelesenen Textdatei bei Programmstart.

 

Hallo!

Wesentlich eleganter und angebrachter wäre der Einsatz einer Pivot-Tabelle.
Dafür musst du nur deine Spalten im Blatt "Daten" jeweils mit einer Überschrift versehen, dann die Projekte in den Pivot-Zeilenbereich ziehen und die Nutzer sowohl in den Spalten- wie auch in den Wertebereich ziehen und du hast dein Ergebnis.
Sinnvoll wäre hier vorher noch eine Namensdefinition im Blatt "Daten", um den Bereich dynamisch zu halten:
Menü Einfügen > Name > Definieren
Name: PivotListe
Bezieht sich auf: =BEREICH.VERSCHIEBEN($B$1;;;ANZAHL2($B:$B);7)

(Die "7" evtl. anpassen, falls es mehr Spalten werden können.)

Da mein VBA leider noch in den Kinderschuhen steckt, kann ich dir beim Dateneinlesen leider (noch) nicht helfen.

 

Danke für den Tipp!

So ganz verstanden habe ich das mit dem "Hineinziehen" (von wo?) noch nicht.

Aber, wenn ein neuer Projektordner hinzukommt, wird das dann auch automatisch eingefügt?

 

Im menü Daten findest du "Pivot".
Hier einfach dem Assi folgen.
Am Schluss wirst du sehen, was ich meine...
Automatisch aktualisiert sich eine PT von Haus aus zwar nicht, aber ein Klick auf den Button "Aktualisieren" hält den Aufwand in Grenzen. Zur Not gibt's ein kleines Makro, das das übernimmt.

 

Okay, funktioniert prinzipiell, aber wenn ich Überschriften einfüge, geht irgendwie die Verknüpfung zur Textdatei flöten. Ich kann neue Daten aus der Textdatei auch nur dann auswählen, wenn ich eine Zelle in der ersten Arbeitsblattzeile ausgewählt habe. Also kann ich keine Überschriften einfügen?!

 

Nach dem Import sollte ein Makro dieser Art Abhilfe schaffen (fügt eine neue Zeile ein und schreibt die entsprechenden Überschriften):

Code:

Sub Ueberschriften()
Rows("1:1").Insert Shift:=xlDown
Range("A1").Value = "Überschrift1"
Range("B1").Value = "Überschrift2"
Range("C1").Value = "Überschrift3"
Range("D1").Value = "Überschrift4"
Range("E1").Value = "Überschrift5"
End Sub

 

Das Makro erfüllt die Funktion "Zeile einfügen" und schreibt dann die Überschriften.
Das geht ja auch manuell und führt dazu, dass die Daten nicht mehr aktualisiert werden können.

Ich glaube ich muss doch auf die Pivot-Tabelle verzichten und es machen wie bisher.

 

Dann nutze aber doch lieber diese Formel (s. Anhang).
Dann kannst du auf VERKETTEN ganz verzichten.