Excel: Wie ermittele ich nächstgrößten Wert?
Ausgangssituation: Ich habe eine Liste mit zwei Spalten: A= Wohneinheiten in bestimmter Lokation, B= Etagenbezeichnungen im Textformat (e.g. EG, OG, TG, 2.OG, DG).
Wenn in der Liste nun DG (=Dachgeschoss) steht und ich diesen in einen numerischen Wert umgewandelt haben möchte, ist dies natürlich abhängig davon, wie viele Stockwerke insgesamt für das Objekt vorhanden sind. D.h. höchstes OG innerhalb des Objekts +1 = DG.
Wie wäre dies mit Hilfe einer Formel umsetzbar?
Meine Listen enthalten teils über 100 Objekte, für jedes Objekt müsste das DG einzeln ermittelt werden.Im Bild: A=Objekte, B=Bezeichnung der Etagen die ich umwandeln muss, C=meine Formel, E=Formel von Suboptimierer
5 Antworten
Ich würde mit einer Hilfsspalte C arbeiten, in der du die Geschosse in Zahlen umwandelst.
Das geht z. B. so:
=SVERWEIS(B1;{"TG".0;"EG".1;"OG".2;"2. OG".3};2;0)
Dann würde ich mit MAXWENNS den höchsten Wert innerhalb der Wohneinheit ermitteln und inkrementieren.
=MAXWENNS($C$1:$C$6;$A$1:$A$6;A1)+1
Tipp meinerseits mindestens auf WENNS setzen da sieht kein Mensch mehr durch mach einiger Zeit. Oder zumindest via Let immer wieder wiederholende Bestandteile eindeutig deklarieren.
Ich habe einen Gegenvorschlag:
=MAX((WENNFEHLER(SUCHEN({"TG";"EG";"OG";"2. OG"};B1);-1)>=0)*{0;1;2;3})
=SVERWEIS(B1;{"TG".0;"EG".1;"OG".2;"2. OG".3};2;0)
genial stimmt dies gibt es ja auch noch, kann man innerhalb dieser Auflistung eigentlich auf Formeln verwenden, anstatt feste Werte?
z.B. so
Danke für die Antwort, mir fehlt hier leider die Lösung der Objekte; ich habe viele verschiedene Objekte in einer Liste und möchte die Formel nicht hundertfach neu anwenden müssen, sondern so gestalten, dass gleiche Objektbezeichnungen auch als gleiches Objekt erkannt werden und darauf die Suche nach dem höchsten Wert erfolgt.
Für TG, EG, KG ME und was es alles so gibt würde ich mit deiner Hilfstabelle definitiv mitgehen, aber warum nicht bei den OGs einfach die Zahl vorne extrahieren mittels links, teil etc., was auch immer.
Letztendlich muss jeder seine eigene Organisation wählen.
vollkommen richtig, wollte auch nur eine Anregung geben ich weiß ja nicht was er so für Gebäude hat, aber ich wäre ein wenig zu Faul für 20 Stockwerke, noch zumal wenn wie bei deiner Variante 2.OG = 4 bekommt.
Glaube ab er auch das es die Universallösung eh nicht gibt, denn ich kenne so viele verschiedene Gebäude die irgendwelche Zwischengeschosse, Halbetagen etc. haben, wo jedes System durcheinander kommt.
zumal wenn wie bei deiner Variante 2.OG = 4 bekommt.
Das ist kein MUSS. Ich bin in solchen Angelegenheiten recht frei und halte es für so flexibel wie ich es will. Die Zahlen die können so variabel wie auch immer genutzt/gewählt werden.
Das ist für mich kein Dogma. .. nur eine Anregung.
Möglicherweise kommen da noch Hausnummer und was auch immer dazu. Ist nicht erkennbar.
Aus der Frage ist möglicherweise der gesamte Komplex nicht abzuleiten.
Wenn ich das richtig sehe, ist das eine zweistufige Aufgabe
- ermitteln wie viele Stockwerke ein gegebenes Gebäude hat. Das könnte eine Tabelle liefern, in der die Stockwerke stehen und etwa mit Verweis() rausgeholt werden. Ergebnis ist eine Zahl
- das DG wäre dann die oben ermittelte Zahl plus 1.
Sprich die eigentliche Arbeit steckt in der erste Übung.
Mal eine ganz andere Meinung: Wenn das DG in den verschiedenen Häusern eine andere Nummer bekommt, geht ja die Eigenschaft DG verloren. Da ist aber manches anders als in den übrigen Geschossen. Sage doch zu DG einfach 99. Dann weißt Du beides: größer als höchstes Normalgeschoss und eben Dachgeschoss. Was willst Du denn überhaupt mit den Nummern auswerten?
Geht es nur um die Berechnung des Dachgeschosses, würde ich es so machen, war zumindest nach einigem überlegen die kürzeste Formel auf die ich kam.
=MAX(WENNFEHLER(LINKS(B2:B6;SUCHEN("OG";B2:B6)-2);0)+1)
Geht es jedoch auch die anderen Geschosse, hätte ich auf die Schnelle folgende Lösung im Angebot
Welche Excel Version kommt denn zum Einsatz?
Bei der unteren Variante ab Zeile C14 habe ich Stockwerke größer 10 berücksichtigt. Da ich mal denke, dass du nicht den Burj Khalifa betreuen musst, denke ich dass Stockwerke > 100 keine Rolle spielen.
Verbesserte Verison:
Die Formel in C14 wurde leicht verbessert, anstatt die Länge auf 4 oder 5 Zeichen abzufragen, subtrahiere ich von der Länge einfach 3 Zeichen ab, was den Zeichen ".OG" entspricht, es bleibt also immer das Stockwerk übrig, sofern du dich an die Schreibweise hältst, machst du zwischen den . und OG noch ein Leerzeichen, musst du eben 4 abziehen.
Falls Excel 365 zum Einsatz kommt gibt es statt dem LINKS+LÄNGE, eine einfachere Variante mit TEXTVOR, ich habe die hier mal angehangen.
Falls noch andere Bezeichnungen als TG wie UG oder irgendwelche Zwischengeschosse wie MEZ etc. dazu kommen müssten die entsprechend mit eingefügt werden.
Würde mich über Rückmeldung freuen.
In deinem Beispiel wird deutlich: Der höchste Wert des Objekt 3 beeinflusst das Ergebnis für Objekt 4. Das darf es für mich nicht. Der höchste Wert darf immer nur innerhalb eines Objektes ermittelt werden. Da das oftmal >100 Objekte in einer Liste sind, ist die manuelle Eingabe einer Formel für jede einzelne Gruppierung von Objekten keine zufriedenstellende Lösung. Kann man da irgendwas mit der Duplikatfunktion machen? Diese erkennt ja gleiche Werte. x-fach gleicher Wert soll dann eine isolierte Gruppierung ergeben und innerhalb dieser wird dann die höchste Etage gesucht und wenn vorhanden für den Begriff "DG" um eins erhöht.
Das mit Objekt 4,5 etc. ist gar nicht gewollt, da habe ich nur vergessen die Strg Taste gedrückt zu halten beim runter ziehen.
Bei mir haben die Objekte nur rein visuellen Charakter, sind aber keinerlei Kriterium. Wenn du das wünschst, dann müsstest du dies mit einem Filter(vorschalten), so dass nur alle zu rate gezogen werden, die den gleichen Namen haben. Nur benötigt der Filter ein Kriterium, also den genauen Objektnamen alternativ ginge das auch über Eindeutig und diese dann Zeilenweise an die entsprechenden Filter übergeben lassen.
Kannst ja mal ein Beispiel Teilen, damit ich/wir uns ein bisschen Besser vorstellen können, wie du das in etwa haben willst, sonst ist es ziemliches Stochern im Heuhaufen.
Nutzen muss ich gerade die Office365 Version, bis zum 10. Stockwerk sollte erstmal reichen + die Optionen von KG, UG, und OG statt 1.OG. Außerdem kann es durchaus mal 1.OG links oder DG rechts heißen, sodass nicht mit einem exakten Wert gerechnet werden kann.
Das sollte nicht das Problem, dann müsste man anstelle von "DG" ebenfalls mit suchen/finden arbeiten. Wie gesagt KG UG etc. müsstest du halt fest definieren, ich weiß halt nicht ob das möglich ist, ich kenne deine Daten ja nicht.
ich könnte mir zumindest vorstellen, dass KG in Objekt X -1 ist und in Objekt Y -2, hinzu kommt nicht jedes UG ist ein KG, aber sie können durchaus in verschiedenen Objekten in der gleichen Etage sein.
Verstehe ich hier richtig, dass ich also jedes Objekt einzeln behandeln muss?
Denn es kann durchaus sein, dass ich über 100 Objekte in einer Liste abhandle. Deshalb wäre der Anspruch, dass gleiche Bezeichnungen direkt als zusammengehöriges Objekt erkannt und behandelt werden.
naja nicht zwingend, aber es ging bei diesen 3 Beispielen nur darum verschiedene Szenarien zu zeigen, dass es funktioniert, du kannst auch alles in der Eierlegenden Wollmilchsau Formel abdecken, die Frage ist, ob du Sie dann noch verstehst, daher hier erstmal Schritt für Schritt. Noch zumal ich nicht weiß, ob alle Objektbezeichnungen wie in obigem Beispiel immer direkt untereinander stehen, oder ggf. in der Liste durcheinander vorkommen können?
Diese Formel habe ich gerade versucht und schnell festgestellt, hier werden nur die exakten Werte behandelt. Also schön knackig und kurz, aber wenn ich 1. OG oder 3. OG Links dort stehen habe, kriege ich eine Fehlermeldung.
Meine bisherige Formel sieht so aus:
=WENN(ISTZAHL(SUCHEN("EG";C2));0;WENN(ISTZAHL(SUCHEN("1.OG";C2));1;WENN(ISTZAHL(SUCHEN("2.OG";C2));2;WENN(ISTZAHL(SUCHEN("3.OG";C2));3;WENN(ISTZAHL(SUCHEN("4.OG";C2));4;WENN(ISTZAHL(SUCHEN("5.OG";C2));5;WENN(ISTZAHL(SUCHEN("6.OG";C2));6;WENN(ISTZAHL(SUCHEN("7.OG";C2));7;WENN(ISTZAHL(SUCHEN("8.OG";C2));8;WENN(ISTZAHL(SUCHEN("9.OG";C2));9;WENN(ISTZAHL(SUCHEN("10.OG";C2));10;WENN(ISTZAHL(SUCHEN("1. OG";C2));1;WENN(ISTZAHL(SUCHEN("2. OG";C2));2;WENN(ISTZAHL(SUCHEN("3. OG";C2));3;WENN(ISTZAHL(SUCHEN("4. OG";C2));4;WENN(ISTZAHL(SUCHEN("5. OG";C2));5;WENN(ISTZAHL(SUCHEN("6. OG";C2));6;WENN(ISTZAHL(SUCHEN("7. OG";C2));7;WENN(ISTZAHL(SUCHEN("8. OG";C2));8;WENN(ISTZAHL(SUCHEN("9. OG";C2));9;WENN(ISTZAHL(SUCHEN("10. OG";C2));10;0)))))))))))))))))))))