Excel Formel SVerweis(), wenn(), etc...?
Hallo,
Ich habe ein Problem. Und zwar bekomme ich immer wieder Tabellen die verschieden aussehen. Ich habe ein einfaches Beispiel aufgebaut:
Jede Nummer !kann! "ja"s oder "nein"s enthalten. Es kann auch 3 Ja's und 3 Nein's haben.
Ich möchte jetzt pro Nummer die Werte die daneben stehen in einer Zeile haben, also so:
Kann mir da bitte jemand weiterhelfen? Bitte beachten, dass das nur ein Beispiel ist und die Daten variieren.
Vielen Dank!
Lg Benni
Ausgangstabelle kann auch so aussehen:
sprich die neins sind nr 30, ja nr 20 und die 4-stelligen nummern 10
4 Antworten
Hallo,
deine Liste besteht gewissermaßen aus mehreren kleinen Tabellen (eine pro Nummer), man muss nur die relative Position dieser Tabellen ermitteln und kann diese dann für einen Verweis verwenden.
Zunächst wird eine Liste der Nummern ohne die ja/nein erstellt:
- {=WENNFEHLER(INDEX($A$1:$A$25;KKLEINSTE(WENN($A$1:$A$25="ja";ZEILEN($A$1:$A$25)+1;WENN($A$1:$A$25="nein";ZEILEN($A$1:$A$25)+1;ZEILE($A$1:$A$25)));ZEILE(A1)));"")}
- Matrixformel, die geschweiften Klammern {} nicht mit eingeben sondern die Eingabe über Strg+Umschalt+Enter abschließen.
Davon ausgehend wird in einer Hilfspalte die relative Position ermittelt, falls es keine Duplikate gibt wäre das über Vergleich() einfach. Wenn Duplikate vorkommen können, kannst du dies verwenden:
- {=KKLEINSTE(WENN($A$1:$A$25=G2;ZEILE($A$1:$A$25);ZEILEN($A$1:$A$25)+0);ZÄHLENWENN($G$1:G2;G2))}
Die so ermittelte Position und die der darunterliegenden Nummer wird dann für den Verweis genutzt. Das Zählenwenn() ist für die Anzahl der ja bzw nein (nebenbei auch für mehr als 3 ja/nein verwendbar)
- {=WENNFEHLER(INDEX(INDIREKT("B1:B"&$H3-1;1);KKLEINSTE(WENN(INDIREKT("A"&$H2&":A"&$H3;1)=I$1;ZEILE(INDIREKT("A"&$H2&":A"&$H3;1));MAX(ZEILE(INDIREKT("A"&$H2&":A"&$H3;1)))+1);ZÄHLENWENN($I$1:I$1;I$1)));"")}
Um die Formeln an deine Tabelle anzupassen empfiehlt sich die Suchen-Ersetzen-Funktion, bei mir sind:
- Spalte A:B die Ausgangstabelle
- Spalte G: die bereinigten Nummern
- Spalte H: Hilfspalte Position
- I1:O1: Überschrift ja/nein
Bitte :D
ok das sie einzigartig sind hatte ich mir fast schon gedacht, aber eine Absicherung schadet ja nicht^^
Die Nullen sollten keine weiteren Auswirkungen haben, aber man kann sie verhindern indem man den Bereich in der Formel auf den tatsächlichen Bereich der Tabelle beschränkt. In meinem Beispiel geht die eigentliche Tabelle ja bis A23, der Bezug in der Formel aber bis A25 geht, durch diesen Überschuss entstehen sie. Dynamisch in der Formel lässt sich die letzte Zeile mit
- =MAX(WENNFEHLER(VERGLEICH(MIN(A:A)-1;A:A;-1);0);WENNFEHLER(VERGLEICH("";A:A;-1);0))
ermittelt, aber das würde ziemlich unübersichtlich werden. Sollte aber möglich sein, die Formel im Makro selbst zusammenzusetzen, falls du das möchtest.
Wenn die Ausgangstabelle in einer anderen Tabelle ist funktioniert die Indirekt() Formel dann so?
INDIREKT("Tabelle1!m1:Tabelle1!m"&$D3-1;1)
die Ausgangstabelle heißt in diesem Fall "Tabelle1 und offensichtlich Spalte M
Fast, das zweite Tabelle1 würde zu einem Bezugsfehler führen
INDIREKT("Tabelle1!M1:M2"&$D3-1;1)
Für sowas ist Excel nicht gebaut!
Das zu lösen gibt ne mega unsaubere, fehleranfällige Fummellösung. Wahrscheinlich musst du sogar Programmieren dafür.
Du solltest hier DRINGEND auf ein ordentliches Datenmodell umstellen, das so tickt, wie Excel gedacht ist!
=> 1 Zeile, 1 Datensatz!
Ich habe die Tabelle ergänzt -> ist das so auch nicht möglich?
wäre die Programmierung aufwendig? Bzw. wie würde die aussehen?
Danke für deine Hilfe
So bekomme ich leider die Ausgangstabelle nicht :/
Kann man sich aber durch editieren sehr leicht machen...
Und ansonsten: Bring dem Datenlieferanten bei, eine ordentliche Datenlieferung zu machen :)
Hm, also prinzipiell kann man zumindest erst einmal alle Zahlen einzeln ermitteln und die entsprechenden Matrix-Zellen dazu heraussuchen. Anschließend leere Zeilen markieren und löschen, dann kommt man eventuell weiter, weil man die Summen- oder Suchbereiche für ja und nein festlegen kann.
Wie, das weiß ich aber noch nicht :D
=WENN(WENNFEHLER(UND(WENN(B1<>"ja";B1;"");WENN(B1<>"nein";B1;""));"")=WAHR;B1;"")
=WENN(H1<>"";ADRESSE(ZEILE(H1);2;4;1);"")
=WENN(H2<>"";ADRESSE(ZEILE(H2)-1;2;4;1);"")
Entschuldige, dass ist nicht nachvollziehbar. Wo kommt denn ja2 und 3 her - genauso bei nein. In der Ausgangstabelle gibt es kein 2 oder 3 - nur ja und nein.
Sind die 4stelligen Nummern immer gleich? Und wieso ist bei 4211 die 8 bei nein2?
Nein, die 4-stelligen Nummern sind nur ein Beispiel, theoretisch könnten auch Buchstaben enthalten sein bzw. längere Nummern.
Ja2 und ja2... kommt daher, weil die Nummern teilweise mehr ja's haben
Achso, und wie oft kann ja und nein vorkommen? Maximal 3 mal? Weil wenn dies bis zu n-mal möglich ist, gibt es keine „einfache“ Lösung mit diesen Datensätzen.
Und die Ja's sollen nicht zufällig pro Nummer addiert werden?
Bisjetzt war das maximum sogar mit 2 Ja's, aber ich denke es könnten auch 3 sein. Aber mit 2 wäre es für mich schon ein Fortschritt.
Wenn es direkt addiert wird und das einfacher is würde es auch klappen, ich dachte mir mit einer Zwischenspalte ist es einfacher
danke für eure Hilfe
Ich bin gerade am teste, aufjedenfall vielen Dank!!
Es sollten keine doppelten Nummern vorkommen, da es individuelle Rechnungsnummern sind - die sind hoffentlich alle einzigartig ^^
Ich würde diese Formeln in ein Makro packen und dieses Makro immer wieder ausführen -> sprich ich muss die Formel ganz runterziehen bis keine Daten mehr sind, mir werden aber in der ersten Spalte nach '4211' noch 4 weitere 0 angezeigt, kann man das noch ändern oder spielt das im weiteren Verlauf keine Rolle?
Ich versuche jetzt noch weiter, aber bis jetzt hast Du meinen Respekt verdient!