Erstes Tabellenblatt nummerierte Daten. Wie erfolgt Übertrag in zweites Tabellenblatt jedoch alphabetisch?
Hallo zusammen
Ich habe eine Exceltabelle, in welcher nummeriert, fortlaufende Einträge erfolgen:
Spalte A: nummerierte Zahlen (Bsp. Nr. 2)
Spalte B: Name (Bsp. Amsel)
Spalte C: Vorname (Bsp. Mia)
Spalte D: Jahrgang (Bsp. 1999)
Spalte E: Adresse (Bsp. Amselstrasse)
Spalte F: Ort (Bsp. Amselhausen)
In einem zweiten Tabellenblatt möchte ich nun nur die Namen beispielsweise beginnend mit "A" aufgeführt haben. Dabei soll die ganze Spalte ins neue Tabellenblatt übernommen werden.
Beispiel neues Tabellenblatt enthält nur noch:
2 / Amsel / Mia / 1999 / Amselstrasse / Amselhausen
5 / Ameise / Klaus / 1953 / Ameisenstrasse / Ameisigen
9 / Auerhan / Karl / 2012 / Auerhanweg / Auerhanien
Könnt ihr mir sagen, wie ich das hinkriege?
Vielen Dank für eure Hilfe! :)
1 Antwort
Hallo stephi,
ich habe mal eine denkbare Lösung ausgearbeitet
Dazu greife ich im wesentlich auf die Moderne Funktion Filter() zurück.
Zunächst einmal Schritt für Schritt
Als erstes würde ich empfehlen die Tabelle in eine "intelligente Tabelle" zu konvertieren. Dazu klickt man irgendwo in den Datensatz, klickt dann auf -> einfügen -> Tabelle, dann wird man gefragt ob die Tabelle Überschriften enthält, was ja stimmt. Nun kann man der Tabelle noch über Tabellenentwurf einen sinnigen Namen geben, meine heißt hier Pers (für Personalien, denke mal sowas soll die Tabelle darstellen)
ich habe den Zellen I1, K1, M1 jeweils ein Dropdown
-> Daten -> Datentools -> Liste
- I1 (=$Q$2#)gibt an nach welchem Buchstaben gefiltert werden soll, falls hier nichts ausgewählt ist, wird auch nicht gefiltert.
- K1 (ASC;DSC) [Achtung muss ggf. mit Komma eingegeben werden, ist in der neusten Version irgendwie man gibt "," ein, aber es erscheint ";" beim öffnen des Dialogs] gibt an ob aufsteigend oder absteigend
- M1 (=$A$3:$F$3) nach welche Spalte sortiert werden soll, dabei ist I1 von M1 abhängig, das heißt die Buchstaben die in I1 zur Verfügung stehen, sind die, welche in der Spalte M zur Verfügung stehen.
=SORTIEREN(EINDEUTIG(LINKS(INDIREKT("Pers["&M1&"]");1)))
die Funktion in Q2 stellt nur eine Hilfsspalte dar. Diese ermittelt auf der ausgewählten Spalte den ersten Buchstaben aller in der Spalte vorkommenden Wörter. EINDEUTIG sorgt dafür dass z.B. nicht mehrmals A vorkommt sondern Dopplungen auf 1 reduziert werden, SORTIEREN macht dies nun noch chronologisch. Diese Hilfsspalte dient dazu die Werte für die Zelle I1 zur Verfügung zu stellen.
=ÜBERNEHMEN(Pers[#Kopfzeilen];1)
Diese Funktion übernimmt einfach die Überschriften, es würde auch gehen =A3:F3, ist am Ende Geschmackssache.
Nun zur kompliziertesten der Formeln
=SORTIEREN(FILTER(Pers;(
WENN(ODER(M1=Pers[[#Kopfzeilen];[Name]];M1=Pers[[#Kopfzeilen];[Vorname]];M1=Pers[[#Kopfzeilen];[Adresse]];M1=Pers[[#Kopfzeilen];[Ort]]);
LINKS(INDIREKT("Pers["&M1&"]");1)=I1;Pers[Nr.]<>""));"");
VERGLEICH(M1;Pers[#Kopfzeilen];0);
WENN(K1="ASC";1;-1);FALSCH)
Ich hab die Formel mal ein wenig versucht aufzutrennen, damit es übersichtlicher ist.
Filter(Pers schnappt sich einfach die Tabelle als Quelle, die lange Wenn Formel prüft im Anschluss ob in M1 entweder Name, Vorname, Adresse oder Ort drin steht (da dies alles Spalten mit Text sind), ist dies der Fall greift
LINKS(INDIREKT("Pers["&M1&"]");1)=I1
hier wird mittels indirekt ein Bezug zusammengebaut, nämlich, das was in M1 steht sprich steht im M1 = Name lautet der Bezug Pers[Name], daraus wird nun der erste Buchstabe bezogen und abgeglichen oder der gleich dem definierten Buchstaben in I1 entspricht, ist dies der Fall werden alle Datensätze ausgegeben.
Ist hingegen keine Spalte mit Text ausgewählt, sondern ID oder Jahr greift folgendes Kriterium: Pers[Nr.]<>"" hier werden im Endeffekt alle Datensätze ausgegeben. Eine Filterung nach beginnenden Jahreszahlen, halte für wenig zielführend, wenn doch gewünscht einfach schreiben, dann passe ich das noch schnell an.
SORTIEREN:
Nun wird die bis hier hin gefilterte Tabelle an Sortieren übergeben, dies wird in 2 Schritten gemacht
- der Spaltenindex, dieser wird ermittelt aus VERGLEICH(M1;Pers[#Kopfzeilen];0); sprich es wird die relative Position innerhalb des Bereichs zurückgegeben, ist also z.B. Adresse ausgewählt, erhältst du als Ergebnis 5, da die Spalte Adresse die 5 Spalte ist.
- WENN(K1="ASC";1;-1) gleicht ab ob ASC in K1 steht (aufsteigend), wenn nicht wird absteigend sortiert.
Das war es auch "schon".
Ich hoffe dir damit geholfen zu haben, würde mich über eine Antwort freuen, falls du noch weitere Wünsche hast, gibt einfach bescheid, sowas sieht erstmal nach viel Arbeit aus, ist aber wenn man Übung darin hat in wenigen Minuten erledigt.
