☰

Extrahieren von Daten mit einem gemeinsamen Punkt in Excel

tag-icon • tag-icon microsoft-excel
Extrahieren von Daten mit einem gemeinsamen Punkt in Excel

Stellen Sie sich einen Datenstapel vor, der aufzeichnet, wann und in welchem ​​Zustand es geregnet hat.

State/Date 01Jan 02Jan 03Jan 04Jan 05Jan 
Alabama     YES   YES   NO    YES   NO
Alaska      YES   YES   YES   NO    YES
Florida     NO    NO    NO    YES   NO
Nevada      NO    YES   NO    YES   NO

Wie schreibe ich eine Formel, die mir eine Liste der Daten liefert, an denen es in einem bestimmten Bundesstaat geregnet hat? Ich möchte beispielsweise wissen, wann es in Nevada geregnet hat. Könnte ich die Antwort 02Jan, 04Janin einer Zelle erhalten?

Antwort1

Die folgende Show-Methode löst das Problem:

Bildbeschreibung hier eingeben

Wie es funktioniert:

  • Um schnellere Ergebnisse zu erzielen, erstellen Sie ein Dropdown-Menü für Staaten in Zelle A28.
  • Klicken Sie auf die Registerkarte „Daten“ und dann auf „Datenüberprüfung“.
  • Wählen Sie als Listenquelle D28:D31 aus.

Bildbeschreibung hier eingeben

  • Erstellen Sie nun für „Regen“ oder „Kein Regen“ ein Dropdown-Menü in Zelle B28, verwenden Sie die Datenüberprüfung und fügen Sie für die Listenquelle „Ja“ und „Nein“ hinzu.

Bildbeschreibung hier eingeben

  • Nun die endgültige Array-Formel (CSE) in Zelle A35:

    {=IFERROR(INDEX($E$27:$I$27,1,SMALL(IF(INDEX($E$28:$I$31,MATCH($A$28,$D$28:$D$31,0),)=$B$28,COLUMN($A$26:$E$26)),ROW(1:1))),"")}

Achtung

  • Beenden Sie die Formel mitStrg+Umschalt+Eingabe& Abfüllen.

  • Passen Sie die Zellbezüge in der Formel nach Bedarf an.

Antwort2

Ehrlich gesagt wäre es wahrscheinlich am besten, einfach eine Pivot-Tabelle aus Ihren Daten zu erstellen. Wenn Sie jedoch einen Formelansatz wünschen:

Angenommen, Ihre Daten befinden sich in den Zellen A1:F5, einschließlich Überschriften. Wenn sich Ihr Nachschlagewert dann in Zelle G8 befindet, gilt Folgendes:

=TEXTJOIN(", ",TRUE,TEXT(IFERROR(IF(IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")/IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")>0,IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0"),""),""),"dd-mmm"))

gibt Ihnen eine Ausgabe, die wie folgt aussieht:

02. Januar, 04. Januar

wenn nach „Nevada“ gesucht wird.

Und so weiter.

Im Grunde verwendet es das erste, XLOOKUP()um die Zeile mit JA- und NEIN-Daten zu finden, die für den Status gilt. Die IF()Umhüllung ergibt 0 für NEIN und das Datum in der Kopfzeile für JA. Dies wird von einem umhüllt, IF()das das vorhergehende Ergebnis geteilt durch sich selbst verwendet (was FEHLER und 1en erzeugt), um zu prüfen, ob das Ergebnis >0 ist, was bei den 1en der Fall ist. Diese FEHLER werden weitergeführt, aber das zweite IF()ersetzt die 1en wieder durch die Daten. Dann IFERROR()umhüllt ein das, um das Ergebnis auf nur die Elemente zu reduzieren, die Daten waren, keine FEHLER. An dieser Stelle sind es ganzzahlige Excel-Datumswerte (sagen wir 43563) und es scheint, dass sie in der Form erwünscht sind, in der sie in der Kopfzeile erscheinen, also TEXT()formatiert es sie so. Die Tatsache, dass sie dabei ihre „Zahlenhaftigkeit“ verlieren, ist kein Problem, da sie dies in jedem Fall tun würden, wenn sie aufgelistet würden (also, es sei denn, es würde nur ein Datum zurückgegeben ... wenn man „technisch sein“ will ...). Schließlich TEXTJOIN()gibt es zur besseren Lesbarkeit ein „Komma-Leerzeichen“-Trennzeichen (und so haben sie ein Trennzeichen, das bei Bedarf in der weiteren Arbeit zum Extrahieren eines oder aller verwendet werden kann).

Wahrscheinlich gibt es andere Ansätze, die XLOOKUP()Version der seit langem bewährten „Double INDEX/MATCH“-Technik könnte einer davon sein, obwohl XLOOKUP()ich mehrere Antworten erhielt (5, wie erwartet), aber es waren drei vom 1. Januar und zwei vom 2. Januar, nicht beispielsweise die vom 2. Januar und 4. Januar, die ich wollte. Es ist hier allerdings 3 Uhr morgens, also habe ich da vielleicht etwas übersehen. Ich FILTER()habe zu schnell gehandelt, wodurch ich die Positionsinformationen verloren habe, um die beiden Daten für Nevada zu erhalten (sofort nur die beiden Ergebnisse, aber jetzt 1-2, nicht 2-4, also keine gute Möglichkeit, die Daten aus der Kopfzeile zu bekommen). Könnte andere Ideen geben. Aber auch hier: 3 Uhr morgens...

Wahrscheinlich spielt das aber keine Rolle, da es sich bei jedem um einen Haufen von Elementen wie oben handelt und eine Reduzierung auf etwas einfach Aussehendes daher wahrscheinlich nicht möglich ist.

verwandte Informationen