MS Excel Einzelne Zellen kopieren wenn bestimmter Text mit Formel?
Hallo Excel Profis
ich habe eine Tabelle mit Anrede (B2-B31) Vornamen (C2-C31 und Nachnamen (D2-D31) und eine Anwesenheitsspalte (E2-E31) in die ich nur ja oder nein Eintrage.
jetzt möchte ich das Excel Automatisch eine Liste Erstellt die mir nur die Andrede, Vorname und Namen der Abwesenden Personen Auflistet als eigene Tabelle neben der Gesamtliste also S2-S31die Anreden, T2-T31 die Vornamen und U2-U31 die Nachnamen.
wer kann mir da die passende Formel sagen? Habe vieles versucht aber komme nicht zum gewünschten Ergebnis
danke im Voraus
3 Antworten
Formel (und runterkopieren):
S2: {=WENNFEHLER(INDEX($B$1:$B$100;KKLEINSTE(WENN($E$1:$E$100="nein";ZEILE($E$1:$E$100));ZEILE(A1)));"")}
T2: {=WENNFEHLER(INDEX($C$1:$C$100;KKLEINSTE(WENN($E$1:$E$100="nein";ZEILE($E$1:$E$100));ZEILE(A1)));"")}
U2: {=WENNFEHLER(INDEX($D$1:$D$100;KKLEINSTE(WENN($E$1:$E$100="nein";ZEILE($E$1:$E$100));ZEILE(A1)));"")}
!!!Achtung!!!
Das ist eine Matrixformel.
Das heißt, die geschweiften Klammern { } NICHT mit eingeben,
sondern die Formel mit STRG&SHIFT&ENTER abschließen.
Das erzeugt die { } und macht sie zu einer Matrixformel.
Klappt es?
Schon, aber von Pivot habe ich mich schon vor Jahren "verabschiedet" und kenne mich damit inzwischen überhaupt nicht mehr aus.
Ich habe damals! die Erfahrung gemacht, dass Pivot irgendwann "an seine Grenzen stößt" und man dann, wenn man die Tabelle weiterentwickeln will, wieder ganz von Vorne anfangen muss.
DAS passiert aber wohl vor allem, wenn man Excel sehr intensiv nutzt und die Aufgabenstellung sich öfter verfeinert (weil der Chef oder der Kunde plötzlich neue Ideen hat 😐).
Aber Pivot hat sich inzwischen weiterentwickelt und ist sicher ein sehr hilfreiches Werkzeug.
DA GEBE ICH DIR RECHT !
Ich benutze in meinem Job täglich Pivot-Tabellen, teilweise über gigantische Datenmengen, die sich oft in ihrer Struktur verändern.
Pivot-Tabellen sind mit ein paar Mausklicks erstellt, sind total flexibel, können mit ein paar Mausklicks permanent angepasst werden und die Daten nach vielen verschiedenen Kriterien auswerten.
Auch wenn ich oft Hunderttausende Datensätze mit einer Pivot-Tabelle auswerte, bin ich noch nie an die Grenzen gestoßen.
Meiner Ansicht nach sind Pivot-Tabellen das mit Abstand Beste an Excel 👍🤓
Wie gesagt, ich kenne mich mit Pivot einfach nicht aus. Mein Fehler.
Mit "an die Grenzen stoßen" meinte ich übrigens nicht die Datenmenge, sondern spezielle Auswertungen etc. die ich damals! mit Pivot nicht mehr verwirklichen konnte. Damals war Pivot aber wohl auch noch viel primitiver als heute. Ich rede da von einer Zeit vor 15 bis 20 Jahren, seit der ich nicht mehr damit arbeite.
Hallo danke für die schnelle Hilfe, in der Zeile in der ich die Formel einfüge funktioniert diese, allerdings bleiben nach dem Kopieren die anderen Zeilen alle leer obwohl ich mehrere Personen mit nein markiert habe in Spalte E2-E31
Freut mich, dass ich helfen konnte 😊.
Beachte aber auf jeden Fall auch die Lösung von Ruubezahl2000.
Das geht ganz einfach und perfekt mit einer Pivot-Tabelle.
- Mit dem Cursor in deine Tabelle gehen
- Menü „Einfügen“ => Pivot-Tabelle (ganz links)
- „Vorhandenes Arbeitsblatt“ wählen und mit dem Cursor Zelle S2 anklicken
- Ok
Dann in der Pivot-Tabellen-Feld-Liste, die rechts aufgeht, deine Felder auswählen und bei der Anwesenheit-Spalte auf „nein“ filtern.
Eine viel einfachere Methode: Die Tabelle Blatt1 duplizieren zu Blatt2. Blatt2 sortieren nach Spalte E. Nun liegen alle Nichtanwesend hintereinander. Den Rest löschen.
Warum so kompliziert?
Eine Pivot-Tabelle ist mit 5 Maus-Klicks erstellt.