Excel: Verweis auf Tabellenspalte wenn Wert größer ist als X?
Hey ihr lieben. Ich hab mal wieder eine Frage zum Thema Excel.
Ich möchte für die Arbeit und einen Kunden eine Excelliste als Wartungstabelle erstellen. Ziel ist es die aktuellen Maschinenarbeitsstunden in eine Zelle einzutragen und dafür alle Aufgaben zu erhalten, bei denen ein X drin steht.
Beispiel:
Ich habe eine Tabelle von 1000, 2000, ... 30000h (sagen wir im Bereich G500-Aj500)
In Zelle B5 sollen die aktuellen Maschinenstunden eingetragen werden.
Auswertung: Wenn B5 größer ist, als 1000/2000/3000/... dann gebe mir die Werte aus, die unter diesem Wert stehen.
Also wenn ich bei 5683h bin möchte ich die Ergebnisse von 5000h angezeigt bekommen.
Mit Sverweis und Wverweis hab ich zwar schon ezwas rumprobiert, bin aber nur uu #nv gekommen..
Eventuell weiss jemand von euch Rat.
Beste Grüße
Flo
2 Antworten
Nachtrag auf Grund Excel älter 2021:
=WENNFEHLER(INDEX($D$500:$D$818;KGRÖSSTE((INDEX($D$500:$AJ$818;;VERGLEICH($G$8;$D$500:$AJ$500;1))="X")*(ZEILE(INDEX($D$500:$AJ$818;;VERGLEICH($G$8;$D$500:$AJ$500;1)))-499);ZÄHLENWENN(INDEX($D$500:$AJ$818;;VERGLEICH($G$8;$D$500:$AJ$500;1));"X")+1-ZEILE(A1)));"")
Falls du eine Erklärung wünschst, bitte Fragen.
Diese Formel muss allerdings heruntergezogen werden, daher einfach auf 100 Zellen runterziehen, es werden nur die Zellen befüllt, die in der jeweiligen Spalte auch mit X versehen sind, alle anderen bleiben einfach leer.
- - - -
Alte Antwort, nur mit Excel 2021 und neuer möglich.
Ich bin mir nicht sicher, ob ich dich richtig verstehe, daher hab ich mal schnell eine Tabelle aufgebaut, wie ich mir das von deiner Beschreibung her vorstelle.
=FILTER(A8:A13;XVERWEIS(B5;B8:E8;B8:E13;"";-1;1)="X";"")
Die Formel dazu kombiniert den Filter mit dem Xverweis, den Xverweis brauche ich, da ich nur den letzten Wert haben will, der gerade überschritten wurde, dafür ist der vorletzte Parameter -1 zuständig, willst du umgedreht, den nächsten der bald ansteht, also vorbereiten für zukünftige Wartungsarbeiten, dann wäre 1 der richtige Wert.
Der XVerweis übergibt nun als ein Array in meinem Falle D9 bis D13, der Filter Filtert diesen Bereich in alle Zellen wo ein X vorkommt und gibt die gleiche Zeile in Spalte A aus.
kannst du mal deine Formel hier rüber schicken
Was kam denn bei Filter für eine Fehlermeldung? #Name!
Alternativ auch gerne deine Arbeitsmappe, reicht gerne auch ein Ausschnitt und anonymisiert, ich möchte keine vertraulichen Kundendaten öffnen.
Welche Excel Version nutzt du denn?
Denn Filter steht erst ab 2021 zur Verfügung, der XVerweis aber ebenso, demnach sollte es damit gehen, wenn der XVerweis geht.
"Sie haben zu viele Argumente für diese Funktion eingegeben"
kleine Anmerkung noch, dass deutet daraufhin, dass du vielleicht ausversehen ein ";" statt einem ":" verwendet hast, denn dann glaubt Excel du willst einen weiteren Parameter eingeben, die meisten Formeln haben aber nur eine begrenzte Anzahl Parameter und bringen dann entsprechend oben genannte Fehlermeldung, aber auch eine falsch gesetzte Klammer kann natürlich eine Ursache sein, da dann die Parameter auch nicht mehr stimmen.
Hier im Übrigen erstmal das Dokument mit dem ich gern arbeiten möchte.
Die Daten für die Auswertung sind zwischen A500 und AJ818 (alle einzelnen Aufgaben, welchen Bereich das umfasst, bei welchen Stunden was gemacht werden soll, etc.
Ich hab eben festgestellt, dass unsere Firma Excel 2019 hat. Dementsprechend stehen Filter garnicht zur verfügung(wird als ungültig deklariert) und Sortieren/Sortierennach gibt er mir beide als #Name wieder weil er sie nicht kennt.
Ist dir dennoch eine Lösung bekannt, wie man das Ganze dennoch bewerkstelligen kann?
Beste Grüße
Flo
danke erstmal, ich habe noch nicht reingeschaut, der erste Tipp wäre benutzt am besten das xlsx Format, die Vorteile die für mich an Eurer Stelle entscheidend dafür wären, wäre:
- XLSX-Dateien können größere Datensätze speichern als XLS-Dateien, was sie für anspruchsvolle Anwendungen besser geeignet macht.
- XLSX-Dateien behalten in der Regel alle Formatierungen und Funktionen bei, wenn sie in neueren Versionen von Excel geöffnet werden, während XLS-Dateien Formatierungen und sehr selten Funktionen verlieren können.
Ja das typische, die Firmen setzen oft auf noch ältere Versionen, die mehr oder minder schon abgekündigt sind, das Geld wird ja mit was anderem Verdient, daher liegt da kein Fokus drauf.
Ich habe oben mal die Formel angehängt, die passen sollte.
Nabend 😁
Erstmal vielen Dank.
Ja, leider war die Idee hier zu fragen zwischen Tür und Angel entstanden, weswegen ich kein konkretes Beispiel vorbereiten konnte. Du hast aber genau das getroffen, was ich bezwecken möchte 👍
Ich probier das Ganze Morgen dann aus. So wie es aber in deinem Beispiel angezeigt wird, scheint es super zu funktionieren. Ich bin gespannt :)
Versuche eine Kombination von VERGLEICH und INDEX
Daran dachte ich auch erst, mein Gefühl sagt mir aber, sofern ich ihn richtig verstehe, damit wird man sich schwer tun, da diese Kombi eigentlich immer nur exakt einen Wert ausgibt. Und so wie ich ihn verstehe, müssen ggf. mehrere Werte aus einer Spalte ausgeben werden
Wenn ich dich richtig verstanden habe, funktioniert das mit VERGLEICH, du musst nur das letzte Argument richtig setzen (1) und die List muss aufsteigend sortiert sein.
Also: Wenn du dort die Werte 5000 und 6000 in der Tabelle stehen hast und du mit 5555 in den Vergleich gehst, erhältst du den Zeiger auf die 5000.
Habs mal gerade geprüft, hast vollkommen recht, geht genauso, Index(Vergleich;...) gibt genauso ein Array aus, wie auch der XVergleich, dennoch komme ich nach jetziger Denkweise nicht um die Funktion Filtern rum, siehe auch Kommentar vom Autor auf meine eigene Antwort.
Ja als alternative zum Filter gibt es auch das ellenlange Zählenwenn;kkleinste;Index;Vergleich Konstrukt, aber das hier hinzuschreiben und zu erwarten das das jemand wirklich versteht ist für einen typischen Excel Anwender eher nicht gegeben.
Maximal fällt mir noch was mit Nachzeile oder Zuspalte ein, aber die Funktionen sind ja noch Moderner als der Filter und daher genauso wie mein XVergleich erst ab 2021 und neuer verfügbar. Mir ging es ja eher um eine alternative für Leute mit älteren Excel Versionen, wofür dein Index-Vergleich ja auch prädestiniert ist.
So, da bin ich wieder. teils positive, teils negative Nachrichten.
Nach einem kurzen Update kannte mein Excel dann auch die sortieren funktion (mit filter wollte er nicht). Ich hab die Formel auf meine Werte angepasst, kam aber leider zu keinem wirklichen Ergebnis. sattdessen lediglich "Sie haben zu viele Argumente für diese Funktion eingegeben"
Anscheinend bezieht sich das auf den Verweis