Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1576to1580
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Afrage aus Matrix, die auch leere Zellen enthält

Afrage aus Matrix, die auch leere Zellen enthält
11.09.2017 15:30:21
Peter
Guten Tag
Meine Abfragen aus der Matrix (grün) ergeben immer Wert Null, da die Bereiche auch leere Felder (Zeilen 5 und 6) beinhalten.
Formel in H2: =VERWEIS(2;1/((E2>=_FER_A1)*(F2>=_FER_D1));_FER_T1~f~
Die Abfragen aus Matrix (gelb) funktionieren, da keine Leerzellen vorhanden sind. Kann ich die Formel für die grüne Matrix ergänzen, so dass die Leerzellen ignoriert werden und ich auch das gewünschte Ergebnis erhalte?
Formel in H10: ~f~=VERWEIS(2;1/((E10>=_FER_A2)*(F10>=_FER_D2));_FER_T2)
https://www.herber.de/bbs/user/116171.xlsx
Userbild
Danke für jede Hilfe.
Gruss, Peter

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Afrage aus Matrix, die auch leere Zellen enthält
11.09.2017 16:38:54
onur
Sag doch mal, was die Formel berechnen soll, ich blicke da nicht so richtig durch, da du arithmetische Operationen mit Boolean-Werten machst.
AW: Matrixabfrage, die auch leere Zellen enthält
11.09.2017 16:50:14
Peter
Hallo
Am Beispiel der grünen Matrix: Mitarbeiter ab dem vollendeten 16. Altersjahr erhalten 30 Tage Ferien, ab dem vollendeten 21. Altersjahr 25 Tage und ab dem vollendeten 51. Altersjahr wieder 30 Tage.
Diese Regelung ist ab 1.1.2015 gültig. Falls die Ferienansprüche neu festgelegt werden würde diese in der Tabelle abgefüllt unter Angabe in Spalte C, ab wann gültig.
Wenn ich nun in Zelle H2 abfrage, wie hoch der Ferienanspruch beim Alter 50.9 ist, muss das Ergebnis 25 sein, da das 51. Altersjahr noch nicht vollendet ist. In der Zelle H3 muss das Ergebnis 30 sein, da das die Person das Alter 51 erreicht hat.
Gruss, Peter
Anzeige
AW: Matrixabfrage, die auch leere Zellen enthält
11.09.2017 16:55:22
onur
Wenn sich die Regelung ändern würde, müsste die Tabelle sowieso neu gemacht werden, da du dann neue Spalten bräuchtest für die neuen Altersgrenzen und Urlaubsansprüche - also kann man eh auf "gültig ab" verzichten - oder?
AW: Matrixabfrage, die auch leere Zellen enthält
11.09.2017 17:06:17
Peter
Hallo
Nein. Es kann ja sein, dass die Ferienansprüche von im 2015 anders als im 2016 sind. Ich brauche jedoch die alten und neuen Ansätze, das Mehrjahresauswertungen erstellt werden.
Userbild
AW: Matrixabfrage, die auch leere Zellen enthält
11.09.2017 17:08:26
onur
Ja so kann man es machen. indem man sie einfach drunter schreibt.
AW: Matrixabfrage, die auch leere Zellen enthält
11.09.2017 17:23:26
Peter
Nur das Problem ist, dass die Formel kein Ergebnis zeigt, wenn unten noch Leerzeilen bleiben. Gibt es eine Möglichkeit, dass die Formel trotz Leerzeilen das gewünschte Ergebnis liefert?
Anzeige
AW: Matrixabfrage, die auch leere Zellen enthält
11.09.2017 17:28:37
onur
Ja so kann man es machen. indem man sie einfach drunter schreibt.
Nimm diese Formel - stell vorher auf Z1S1-Schreibweise um.
=VERWEIS(2;1/((ZS(-3)>=Z2S1:Z4S1)*(ZS(-2)>=Z2S3:Z4S3));Z2S2:Z4S2)

Du darfst nicht die leeren Felder mit in die Matrix aufnehmen, sondern nur die relevanten Daten.
AW: Matrixabfrage, die auch leere Zellen enthält
13.09.2017 08:06:42
Peter
Hallo Onur
Ich wollte ja ursprünglich die leeren Zeilen vermeiden - ohne leere Zeilen funktionierte meine Formel bereits. Deshalb wusste ich nicht, was ich dazu sagen soll.
Gruss, Peter
AW: Matrixabfrage, die auch leere Zellen enthält
13.09.2017 09:02:39
onur
Wahrscheinlich genauso wenig wie ich verstehe, warum jemand überhaupt leere Zellen mit in die "Stammdaten" aufnehmen muss - diese Daten ändern sich ja schliesslich nicht jeden Monat.
Anzeige
AW: mir stellte sich allerdings auch die Frage ...
13.09.2017 09:42:55
...
Hallo onur,
... warum Du die eingestellte Formel nur auf die Z1S1-Schreibweise umgestellt hast und Peters eigentliche Fragestellung einfach abgetan hast. Es ist durchaus nicht unüblich, leere Datenzellen in einer Auswertung entsprechend zu berücksichtigen.
Gruß Werner
.. , - ...
AW: diese mit zusätzl. Bedingung ausschließen ...
12.09.2017 17:14:55
...
Hallo Peter,
... in H2:

=VERWEIS(9;1/((E2>=_FER_A1)/(F2>=_FER_D1))/(_FER_A1>0);_FER_T1)
Und anstelle mit benannten Formeln zu arbeiten, gib Deinen Datenspalten deren Namen als Überschriften und formatiere dies dann als "intelligente Tabelle" dann kannst Du Dir die benannten Namen sparen.
Gruß Werner
.. , - ...
Anzeige
AW: diese mit zusätzl. Bedingung ausschließen ...
13.09.2017 07:58:25
Peter
Hallo Werner
Vielen Dank. Das liefert das gewünschte Resultat. Das mit den inteligenten Tabellen muss ich mal studierren.
Ich habe zur erweiterten Formel noch eine Verständnisfrage. Bisher habe ich ja in der Formel Multiplikationen vorgenommen - nun sind Divisionen daraus geworden. Wie muss ich das verstehen?
ALT: =VERWEIS(2;1/((E2>=_FER_A1)*(F2>=_FER_D1));_FER_T1)
NEU: =VERWEIS(9;1/((E2>=_FER_A1)/(F2>=_FER_D1))/(_FER_A1>0);_FER_T1)
Gruss, Peter
AW: diese mit zusätzl. Bedingung ausschließen ...
13.09.2017 08:02:18
onur
Wieso bekomme ich nicht mal eine Antwort auf meinen post?
AW: das kannst Du leicht selbst herausfinden ...
13.09.2017 09:32:56
...
Hallo Peter,
... ersetze doch meine Divisionen mal durch Multiplikationen. Was erhältst Du dann? Willst Du das?
Die spez. VERWEIS() -Formel wird deshalb von mir bewusst so konsequent immer mit Divisionen konstruiert, damit alle Bedingungen übereinstimmend erfüllt sein müssen. Anderenfalls verliert man schnell den Überblick und Excel liefert ein falsches Ergebnis. Grundlage dessen ist das eine Division durch FALSCH eine Division durch 0 und diese wiederum einen Fehlermeldung produziert die von VERWEIS() als eine der wenigen Excelfunktionen eine solche einfach ignoriert solange diese ein Teilergebnis 1/WAHR =1/1 vorfindet. In einigen früheren threads hab ich dazu allerdings noch ausführlicher geschrieben.
Gruß Werner
.. , - ...
Anzeige
AW: vielen Dank! owT
13.09.2017 10:08:46
Peter

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige