Excel - VERWEIS mit mehreren Ergebnissen

[Hirila]

Hallo

Frage:
Wie kann ich aus einer Liste mit Werten alle unterschiedlichen Werte auslesen, die einem anderen Wert zugeordnet sind?

Ausgangslage:
Ich habe zwei Tabellen.
- Bauarten: Jede Zeile ist ein Projekt. In jedem Projekt gibt es eine Adressnummer und eine "Bauart-Nummer". Weil viele Projekte durchgeführt wurden, haben viele Adressen mehrere Baurten zugeordnet. Es gibt 4 Bauarten (1, 2, 3 999 und leere Zellen)
- Kundenadressen: Jede Adressnummer ist exakt einmal enthalten.

Ziel:
In der Tabelle Kundenadressen will ich neben der Adressnummer aufgeführt haben, zu welchen Bauarten dieser Kunde Projekte laufen hatte.
Beispiel ist Adressnummer 115910.

Versuche das Problem zu lösen:
- Verweis-Formel gibt nur dann einen Wert aus, wenn eine Adressnummer in der Bauart-Liste nur einmal vorkommt. Bei Mehrfach-Vorkommen gibt sie keinen Wert aus
- Im Internet habe ich Lösungswege mit Formeln gefunden, die "INDEX", KGRÖSSTE" und ähnlich Funktionen aufweisen. Diese verstehe ich leider nicht.


Hier ein stark (!) vereinfachtes Excel-File mit Daten.
Anhang anzeigen Bauarten_Testfile.xlsx

Kann mir jemand beim Erstellen der Formel helfen? (Und idealerweise auch gleich erklären was diese macht?)

Mein ewiger Dank wäre euch sicher!
(Ich nutze Excel 2010.)

 

[Schoddy84]

Vielleicht die "Datentabelle" mit einer Pivottabelle auswerten?

 

[Hirila]

Hi

Ja, da kommt leider das "stark" vereinfacht meines Testfiles ins Spiel...
Ich hab in meinem Original-File dermassen viele Daten die miteinander verknüpft und "ge-Pivot-et" sind... Das kann ich nicht ewig weiterziehen. Irgendwo möchte ich "hart" verknüpfte Werte stehen haben.

Und ich weiss auch nicht, wie man z.B. zwei verschiedene Pivots miteinander verknüpfen könnte. Ginge so was denn?

 

[LesGrossman]

kopiere die Werte aus der Pivot-Tabelle in ein neues Tabellenblatt (dort stehen dann nur noch Werte / Values ) und fahr darüber nochmal eine Pivot-Tabelle. Einfachster Weg!

 

[Hirila]

Hmm. Werde ich wohl so lösen.
Schade dass es da nichts gibt, dass einem die Copy-Paste-Handarbeit erspart.

Aber trotzdem Danke.

 

[E.o.B]

Hallo,

habe dir mal ein VBA erstellt (siehe unten).

mit ALT+F11 den VBA Editor öffnen, über Einfügen dort dann ein neues Modul hinzufügen und da den Code reinkopieren.
In der Excelmappe mit den Kunden dann dort eine Spalte mit der Funktion =getBauart(<FIRMENNUMMER>) füllen. das ganze dauert eine Kleinigkeit.
siehe Bild

Hoffe das ganze funktioniert auch so in excel 2010, habe hier leider nur 2003 zur Verfügung.

glhf
E.o.B

Sorry, wegen dem verschobenen Bild, habe halt nur zwei 17" Monitore auf unterschiedlicher Höhe.

Public Function getBauart(firma)
Dim z As Integer
Dim cB1 As Boolean
Dim cB2 As Boolean
Dim cB3 As Boolean
Dim cB999 As Boolean
Dim result As String

z = 2
cB1 = False
cB2 = False
cB3 = False
cB999 = False
result = ""

Do While (IsEmpty(Worksheets("Bauart").Cells(z, 1)) = False) And (z < 65000)
    
    If Worksheets("Bauart").Cells(z, 1) = firma Then
        If Worksheets("Bauart").Cells(z, 2) = 1 Then
            cB1 = True
        End If
        If Worksheets("Bauart").Cells(z, 2) = 2 Then
            cB2 = True
        End If
        If Worksheets("Bauart").Cells(z, 2) = 3 Then
            cB3 = True
        End If
        If Worksheets("Bauart").Cells(z, 2) = 999 Then
            cB999 = True
        End If
    End If
    z = z + 1
    
    If (cB1 = True) And (cB2 = True) And (cB3 = True) And (cB999 = True) Then
        Exit Do
    End If
Loop

If cB1 = True Then
    result = result + "1 "
End If
If cB2 = True Then
    result = result + "2 "
End If
If cB3 = True Then
    result = result + "3 "
End If
If cB999 = True Then
    result = result + "999"
End If

getBauart = result

End Function

 

[Wanderer101]

Wäre hier nicht so langsam eine DB wie Access sinniger? Man kommt ja schon an die Grenzen von Excel...

 

[nurmalsoamrande]

Mit dem SVerweis geht sowas nur, wenn du Ihn als Matrixfunktion benutzt, das setzt aber voraus, dass du die Größe deiner Ergebnismatrix kennst. Dann kann Excel tatsächlich mit einer einzelnen Funktion mehrere, unterschiedliche Ergebnisse liefern.

Ansonsten würde ich hier eher in Richtung PowerPivot statt Pivot verweisen. Kostet zwar etwas Einarbeitung, verwandelt dein Excel aber quasi in eine Datenbank. Also da ist es durchaus möglich, sehr Komplexe Auswertungen zu fahren.