Einzelnes Suchkriterium, mehrere Ergebnisse

Question

Ich muss die Ergebnisse aus einer Spalte sortierter Daten mit nur einem einzigen Suchkriterium ausgeben. Manchmal gibt es mehrere Vorkommen für dasselbe Kriterium. LOOKUP findet nur das erste Vorkommen. Ich muss in eine Zelle eingeben, dass die Zelle übereinstimmen soll, und zwar J8:J581, und die entsprechenden anzuzeigenden Daten sind N8:N581.

J   K L M N
bob       RED
bob       BLUE
Bob       Green
Sue       yellow
Sue       white
fred      grey
pete      brown

.

input=bob
output=    bob  RED
                BLUE
                Green

Answer 1

Wenn Sie einfach nur Zeilen wie in Ihrem obigen Beispiel zurückgeben möchten, verwenden Sie eine Excel-Tabelle.

1. Wählen Sie Ihre Daten aus
2. Erstellen Sie eine Tabelle Insert>Tables>Table(aktivieren Sie das Kontrollkästchen „Meine Tabelle hat Überschriften“)
3. Sobald die Tabelle erstellt wurde, wählen Sie einfach Ihre Filterschaltfläche (Abwärtspfeil in der Tabellenkopfzeile) und wählen Sie Ihren Filterwert (wählen Sie beispielsweise in Ihrem Beispiel Bob). Dadurch werden nur die „Bob“-Zeilen zurückgegeben.

Hier ist ein Vorher-Bild:

Und danach:

BEARBEITEN: Basierend auf Ihren zusätzlichen Informationen würde ich erwägen, eine Pivot-Tabelle basierend auf Ihrer Datentabelle hinzuzufügen. Auf diese Weise können Sie eine „umrissene“ Ansicht Ihrer Informationen erstellen, um besser zu erkennen, welche Campingplätze nur eine ID oder mehrere IDs haben. Das würde ungefähr so ​​aussehen:

Außerdem, um @pnuts zu beachten, ist Excel auf 10.000 Elemente in einer Dropdown-Liste (d. h. Filterliste) beschränkt, es gibt jedoch keine dokumentierte Beschränkung für Tabellenmitglieder. Ich hatte Tabellen mit 10.000 Zeilen.

EDIT2:Wenn Sie einfach nur wiederholte Werte finden möchten, ist eine Pivot-Tabelle genau das Richtige.

1. Nehmen Sie Ihre ursprüngliche Datentabelle und fügen Sie eine „Zähler“-Zeile hinzu. =IF([@Name]=D1,F1+1,1)Dadurch wird die Anzahl der Zeilen mit demselben Namen zurückgemeldet.
2. Insert>Tables>Pivot TableErstellen Sie aus Ihren Daten eine Pivot-Tabelle .
3. Formatieren Sie Ihre Pivot-Tabelle wie folgt:
   • Zeilenbeschriftungen = Name, Code, Counter, und Zwischensummen und Gesamtsummen für alle Werte deaktivieren.
   • Filterzähler = 1 löschen, dadurch werden nur Namen mit mehreren Werten angezeigt.
   • Filtername = jeder bestimmte Campingplatzname, an dem Sie interessiert sind.

Answer 2

Angenommen, Ihre Eingabezelle ist A1 und Ihre Ausgabezellen sind A3 für den Namen und B3:B.. für den Ausgabebereich (wobei das Ende des Bereichs B bis zur maximalen Anzahl der Ergebnisse reicht, die Sie erwarten).

Geben Sie für A3 die Formel ein =A1.

Für B3 setzen Sie die Formel

  =IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")

Dies ist eine Matrixformel und muss mit der Tastenkombination Control- Shift- eingegeben werden Enter. Sie können sie dann an das Ende Ihres Ausgabebereichs kopieren.

Beachten Sie, dass die Formel für eine Eingabeliste mit maximal 99 Zeilen fest codiert ist. Sie können diese Länge auf die gewünschte Länge ändern. Es wäre zwar möglich, auf die gesamte Spalte (J:J und N:N) zu verweisen, dies würde jedoch zu Leistungseinbußen führen, die Sie wahrscheinlich vermeiden möchten.

So funktioniert die Formel

Die Formel vergleicht von innen nach außen zunächst den Namen, für den die Suche durchgeführt werden soll (Zelle A3), mit der vollständigen Namensliste (bis zu 99 Namen im Bereich J1:J99). Dieser Vergleich wird in Zeile 6 der unten gezeigten Funktionsaufschlüsselung angezeigt.

Das Produkt dieses Vergleichs ist ein Array mit True-Werten für eine Übereinstimmung und False-Werten für eine Nichtübereinstimmung, z. B. {False, False, False, False, True, True, False, … usw.}.

Anschließend wird ein Vergleich zwischen diesem Array und einem Array durchgeführt, das man sich als die „Zeilennummern“ der Namensliste vorstellen kann: {1, 2, 3, 4, 5, 6, ... 99}. Dieser Vergleich wird durch die IF-Anweisung in den Zeilen 6-8 des Formeldiagramms durchgeführt.

Der Vergleich erfolgt Element für Element. Wenn ein Element des Namensvergleichsarrays True ist, gibt das IF die entsprechende Zeilennummer zurück; wenn das Element False ist, gibt das IF FALSE zurück. Bei Verwendung der beiden obigen Beispielarrays wäre das Ergebnis der IF-Anweisung {False, False, False, False, 4, 5, False, ...}.

Weiter wird die SMALL-Funktion (beginnend in Zeile 8 der Funktionsübersicht) verwendet, um das k-te kleinste Element dieses neuen Arrays aus dem IF zu erhalten. Das „k“ wird in diesem Fall durch den Ausdruck ROWS($N$1:$N1) geliefert, der einfach von 1 bis 99 hochzählt, wenn die gesamte Formel von Zeile 1 bis Zeile 99 nach unten kopiert wird (ROWS($N$1:$N1) = 1, ROWS($N$1:$N2) = 2 usw.).

SMALL sucht also zuerst das kleinste Element des Arrays, das durch IF erzeugt wird.ignorierenddie Elemente, die „False“ sind. Mit anderen Worten, es wird die erste Zeilennummer zurückgegeben, in der der verglichene Name mit einem Namen in der Nachschlageliste der Namen übereinstimmt. In unserem Beispiel ist das die Nummer 4, wie in der 6. Spalte der folgenden Tabelle gezeigt.

Der Abschlussschritt verwendet INDEX auf den Nachschlagewerten, um das Element abzurufen, das der berechneten Zeilennummer entspricht. In diesem Fall ist das vierte Element im Farblistenbeispiel für die Frage „gelb“. (Der IFERROR stellt sicher, dass ein Leerzeichen angezeigt wird, wenn die Formel keine Übereinstimmung findet.)

Dies war das Ergebnis der ersten Kopie der vollständigen Formel. Wenn sie in die nächste Zelle kopiert wird, ändert sich in der Berechnung nur der Wert von „k“ für die SMALL-Funktion, der auf 2 vorrückt. Und die zweitkleinste gefundene Zeilennummer ist 5, was den Wert „weiß“ ergibt.