☰

Excel - für jeden Wert einer Werteliste mehrere Werte zurückgeben

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
Excel - für jeden Wert einer Werteliste mehrere Werte zurückgeben

Ich versuche nun schon seit einigen Stunden, das Problem zu lösen, aber ohne Ergebnis ...

Mein Setup:

  • Ich habe Excel-Tabellen mitRezepte für Kosmetikprodukte.

  • Jedes Rezept enthält Dutzende von Zutaten, jede mit ihremHandelsname.

  • Ich habe eine lange Liste mit den Übersetzungen der einzelnen Handelsnamen in'wissenschaftlicher Name.

Ich muss jeden Handelsnamen in sein wissenschaftliches Gegenstück übersetzen. Dieser Teil lässt sich mit einer Vlookup-Funktion sehr einfach erledigen.

Aber einigeNamen austauschenSindRezeptean sich, enthalten mehrere Zutaten in der Tabelle derwissenschaftliche Namen. Es handelt sich also nicht um eine 1:1-Beziehung, sondern manchmal (nicht immer) um eine 1:viele-Beziehung.

Zum Beispiel:

Kosmetik A Blatt

 IngredientA_trade_name

 IngredientB_trade_name

 IngredientC_trade_name

Handel mit der Stammdatei der Liste wissenschaftlicher Namen:

 IngredientA_trade_name     Science1
 IngredientB_trade_name     Science2
 IngredientB_trade_name     Science3
 IngredientB_trade_name     Science4    
 IngredientC_trade_name     Science5
 IngredientC_trade_name     Science6

 ....etc, for lots and lots of ingredients.

Ich habe zwar Möglichkeiten gefunden, mehrere Werte zurückzugeben, aber keine davon funktioniert automatisch:

Ich muss dieZutatName in einer Zelle, dann platzieren Sie die Formel in der Zelle daneben und ziehen Sie sie ein paar Stellen nach unten, damit sie für alle möglichen Übereinstimmungen automatisch ausgefüllt wird. Einige andere Lösungen platzieren die zurückgegebenen Werte in einer einzigen Zelle (Science2,Science3,Science4), was etwas besser ist, aber für den Rest meines Arbeitsablaufs nicht funktioniert ...

Gibt es eine Möglichkeit, die Liste der Handelsnamen durchzugehen und nach Bedarf Zeilen mit allen wissenschaftlichen Namen einzufügen?

Antwort1

Dieses Makro mit VBA erledigt die Aufgabe:

Public Sub ingredients()
    Dim wkb As Workbook
    Dim wks, wks1 As Worksheet
    Set wkb = ThisWorkbook
    Set wks = ActiveSheet
    wks.Application.ScreenUpdating = False
    mastername = "Master"
    totalsheets = wkb.Worksheets.Count
    For i = 1 To totalsheets
        Set wks1 = wkb.Worksheets(i)
        wks1name = wks1.Name
        If wks1name = mastername Then
            i = totalsheets
        Else
            Set wks1 = Nothing
        End If
    Next i
    reviewing = True
    activerow = 1
    While reviewing
        tradename = wks.Cells(activerow, 1)
        If tradename = "" Then
            reviewing = False
        End If
        reviewingmaster = True
        activerowmaster = 1
        found = 0
        While reviewingmaster
            sciname = wks1.Cells(activerowmaster, 1)
            If sciname = "" Then
                reviewingmaster = False
            End If
            If sciname = tradename Then
                found = found + 1
                If found > 1 Then
                    activerow = activerow + 1
                    wks.Rows(activerow).Insert
                End If
                wks.Cells(activerow, 2) = wks1.Cells(activerowmaster, 2)
                wks.Cells(activerow, 3) = wks1.Cells(activerowmaster, 3)
            End If
            activerowmaster = activerowmaster + 1
        Wend
        activerow = activerow + 1
    Wend
    wks.Application.ScreenUpdating = True
    theend = MsgBox("Finished on " & wks.Name, vbInformation)
End Sub

Öffnen Sie dazu Macros /VBA, fügen Sie ein Modul ein unterDiesesArbeitsbuchund fügen Sie den Code auf der rechten Seite ein.

Beachten Sie Folgendes:

  • Der Code wird auf dem aktiven Arbeitsblatt ausgeführt.
  • Jeder neue wissenschaftliche Name außer dem ersten Handelsnamen wird in einer neuen Zeile hinzugefügt.
  • Der Code funktioniert, solange die Spalte A des Arbeitsblatts keine leeren Zellen enthält.
  • Da angenommen wird, dass der Name des Master-Listen-ArbeitsblattsMeisterSie müssen die Zeile mastername="Master"in den entsprechenden Namen ändern.
  • Das Eingeben von 500 Handelsnamen und 5000 wissenschaftlichen Namen dauerte 23 Sekunden.

Antwort2

Für Excel 2010 oder 2013 würde ich hierfür das Power Query-Add-In verwenden. Ab Excel 2016 ist Power Query in das Daten-Menüband im Abschnitt „Abrufen und Transformieren“ integriert.

Power Query kann mit einer Excel-Tabelle beginnen. Es verfügt über einen Merge-Befehl, der Abfragen zusammenführen kann, mit der Option, nur die Übereinstimmungen beizubehalten. Ein Abfrageergebnis kann in eine Excel-Tabelle geschrieben werden.

Das Entwerfen Ihrer Anforderungen in Power Query würde einige Minuten dauern und erfordert keinen Code.

...

Ich wurde durch hilfreiches Feedback dazu inspiriert, diese Antwort zu erweitern. Tatsächlich habe ich eine funktionierende Lösung entwickelt, die Sie von meinem OneDrive herunterladen und ausprobieren können:

http://1drv.ms/1AzPAZp

Es ist die Datei:Power Query-Demo - mehrere Werte für jeden Wert einer Werteliste zurückgeben

Der Aufbau hat bei mir weniger als 2 Minuten gedauert (ohne das Kopieren der Eingabedaten und das Schreiben des Readme-Blatts) und erforderte keinen Code/keine Funktionen.

Die Schlüsseltechnik ist Zusammenführen und Erweitern, wie hier beschrieben:

https://support.office.com/en-us/article/Merge-queries-Power-Query-fd157620-5470-4c0f-b132-7ca2616d17f9

verwandte Informationen