Ich bin Botaniker und versuche, eine Excel-Tabelle zu erstellen, die eine sehr lange Liste von Arten nach Standort und grundlegendem Lebensraum filtert.
Ich habe eine Haupttabelle mit 200 Zeilen für jede Art erstellt und in den Spalten stehen zunächst die Standorte (z. B. Spalte b für England, Spalte b für Schottland usw.) und dann nach dem Standort die Spalten mit den grundlegenden Lebensräumen (d. h. Spalte f für Wald, Spalte g für Grasland, Spalte h für Wasser). Alle Zellen enthalten für jede Art ein JA oder NEIN, das zeigt, an welchem geografischen Standort und in welchem Lebensraum die Art vorkommt.
Ich möchte eine Tabelle erstellen, die mich fragt, wo sich mein Standort befindet und welche Lebensräume er beherbergt. Auf dieser Grundlage kann ich dann eingrenzen, welche Arten dort wahrscheinlich vorkommen. Wenn ich beispielsweise einen Standort in London hätte, der Wald, Grasland und eine Hecke beherbergt, könnte ich die Filter verwenden, um die Artenliste von über 200 auf hoffentlich eine Handvoll Arten zu reduzieren!
Über Ideen zur Erstellung freue ich mich. Ich habe ein Bild meines aktuellen Arbeitsblatts angehängt.
Antwort1
Ziemliech direkt
Sie haben Ihre Informationen im Wesentlichen in zwei nebeneinander liegenden Tabellen mit ausgerichteten Zeilen angeordnet. Tabelle 1 ist Ihr Standort und Tabelle 2 Ihr Lebensraum. Im Grunde möchten Sie eine Liste oder Zeilennummern generieren, bei denen in der Spalte „Übereinstimmender Standort“ ein „Ja“ und in der Spalte „Übereinstimmender Lebensraum“ ein „Ja“ steht. Ziehen Sie basierend auf dieser Liste von Zeilennummern die entsprechenden Arten heraus.
Lassen Sie uns dazu einige Annahmen treffen:
- Das Blatt, in dem sich alle Ihre Daten befinden, heißt SData
- Die Daten sind aus A1:R200
- Zeile 1 ist eine Kopfzeile
- Die Spalten C:I sind Standortspalten
- Die Spalten J:R sind die Standorte der Habitate
- Der gesuchte Standort ist B1
- Der gesuchte Lebensraum ist D1
- Ihre Liste wird in A2:B200 angezeigt
1) Standortspalte bestimmen
=INDEX(SData!C2:I200,0,MATCH($B$1,SData!$C$1:$I$1,0)
2) Lebensraumspalte bestimmen
=INDEX(SData!J2:R200,0,MATCH($D$1,SData!$J$1:$R$1,0)
3) Bestimmen Sie, welche Zeilen Ja enthalten
Sowohl Habitat als auch Location müssen „Ja“ enthalten, um eine akzeptable Zeile zu sein. Wenn Zelle = „Ja“ ist, ist das „Wahr“. Bei mathematischen Operationen behandelt Excel „Wahr“ als 1 und „Falsch“ als 0. Wenn also beide Zellen in einer Zeile „Ja“ sind, erhalten Sie 1*1=1. Wenn beide „Falsch“ sind, erhalten Sie 0*0=0. Und wenn eine Zelle „Ja“ und die andere „Nein“ ist, ist das Ergebnis 1*0=1.
Um dies zu tun, wird die Aggregatfunktion verwendet. Sie führt arrayähnliche Operationen für bestimmte Funktionen aus, wie 14 und 15, die verwendet werden. Aggregate können auch angewiesen werden, Fehler zu ignorieren. Wir werden AGGREGATE also so einrichten, dass die Zeilennummer durch die Bedingungsprüfung auf „Ja“ geteilt wird. Als Ergebnis erhalten Sie entweder die Zeilennummer oder einen Divisionsfehler durch 0, woraufhin Aggregate diese Ergebnisse ignoriert.
=INDEX(SData!A:A,AGGREGATE(14,6,ROW(SData!$A$2:$A$200)/((INDEX(SData!$C$2:$I$200,0,MATCH($B$1,SData!$C$1:$I$1,0))="yes")*(INDEX(SData!$J$2:$R$200,0,MATCH($B$1,SData!$J$1:$R$1,0))="yes")),ROW(A1)))
Platzieren Sie die obige Formel in A2 und kopieren Sie sie nach unten und quer zu B200. Das Problem, das ich bisher vorhersehe, ist, wenn Ihnen die Zeilen ausgehen, die Ihren Daten entsprechen. In dieser Situation oder wenn Sie nur Nullergebnisse hätten, würde ein Fehler auftreten. Um Fehlerergebnisse in Ihren Zellen zu vermeiden, können Sie die gesamte obige Formel in eine IFERROR-Formel einschließen, sodass "" angezeigt wird, wenn ein Fehler das Ergebnis ist.
=IFERROR(INDEX(SData!A:A,AGGREGATE(14,6,ROW(SData!$A$2:$A$200)/((INDEX(SData!$C$2:$I$200,0,MATCH($B$1,SData!$C$1:$I$1,0))="yes")*(INDEX(SData!$J$2:$R$200,0,MATCH($D$1,SData!$J$1:$R$1,0))="yes")),ROW(A1))),"")
Beispiel:
Datentabelle:
Ergebnisblatt 1:
Ergebnisblatt 2:
