Sverweis/index mit einigen einschränkungen

[Ben10623]

hi,

ich habe hier mehrere tabellenblätter. eines dieser blätter enthält eine liste mit daten aus der bei bedarf per sverweis oder index gelesen werden soll.

die tabelle enthält 13 zeilen und 9 spalten.

die erste spalte führt eine nummerierung die auch wichtig ist, ich brauche die ausgabe dieser nummern. da sverweis nicht aus der linken spalte lesen kann, habe ich diese in die 9. spalte eingefügt.

die spalten 3-6 habe ich rechts daneben noch einmal extra angelegt(Bild spalten 3-6), das vereinfachte für mich das auslesen der einzelnen daten. diese werte werden beim ändern der tabelle automatisch mit geändert.

zur berechnung werden die daten auf dem blatt Eingabe eingegeben.

generell gilt bei mir, farbig hinterlegte felder sind felder die entweder automatisch ausgefüllt werden oder bezeichnungen tragen.

die berechnungen erfolgen auf dem letzten blatt in diesen sortiment… der formelliste.

aufgabe dieser mappe ist es, bei eingeben eines einkommens aus der liste gleich den richtigen betrag nach abzug aller abzugsfähigen positionen auszuspucken und was an zahlungen fällig wird.

also, wer interesse hat, darf sich da gerne dieser runde anschließen…

im moment hänge ich an der position B5 in der formelliste.

=SVERWEIS(Dateneingabe!I8;‚Düsseldorfer Tabelle‘!C9:K21;9)

erfüllt seinen zweck gut, aber nicht perfekt. um perfekt funktionieren zu können, musste ich die B6 einsetzen.

=WENN(UND(Dateneingabe!I14=„JA“;B5>1);B5-1;B5)

diese soll die zahl um „1“ reduzieren wenn in der eingabemaske ein „JA“ erscheint, allerdings immer mindestens „1“ sein.

wie bekomme ich das kombiniert so das ich nur noch eine formel habe?

[littlepinguin]

hi,

erstmal kürzen, damit B5 nicht sooft vor kommt.
aus =WENN(UND(Dateneingabe!I14="JA";B5>1);B5-1;B5)
kann `=max(1;B5-WENN(Dateneingabe!I14=„JA“;1;0))’ werden

dann hast du auch nur noch ein B5 und könntest an deren Stelle deine Formel direkt einsetzen.
=max(1;SVERWEIS(Dateneingabe!I8;'Düsseldorfer Tabelle'!C9:K21;9)-WENN(Dateneingabe!I14="JA";1;0)) sollte gehen.

grüße
lipi

[Ifm001]

@Ben10623

da sverweis nicht aus der linken spalte lesen kann, habe ich diese in die 9. spalte eingefügt.

Nur als Randbemerkung:

Wenn Du Excel 365 vorraussetzen kannst, schaue dir mal XVERWEIS() an. Das ist der „Nachfolger“ von SVERWEIS() und WVERWEIS(). Damit ist diese verkorkste Spaltenzählerei nicht mehr nötig und damit verbunden kann der Rückgabewert auch links vom Suchwert stehen. Es lassen sich damit auch viele Behelfe mit INDEX() u. ä. viel eleganter sowie performanter ablösen.

[Ben10623]

@littlepinguin

funktioniert bedingt, danke.

im gelben feld funktioniert es super, kopiere ich die formel (nicht das feld) an die richtige stelle gibt es einen fehler und gibt mir ne „0“ aus.

einzigst der „Unterhaltsanspruch“ greift auf dieses feld zu mit =INDEX(‚Düsseldorfer Tabelle‘!O9:AM21;Formelliste!B5;Formelliste!E21)

[Ben10623]

@Ifm001

Office 2003… ich gehe nicht ganz mit der zeit

[littlepinguin]

hi,

Welche Zellen greifen denn außerdem noch auf B5 zu?
Ich würde hier zuerst an dateneingabe!I14 denken.

Das liegt aber nicht an der Formel, sondern an dem Dokument.
Das ist aber zu unübersichtlich, um etwas sagen zu können.

Ich würde der angezeigten Hilfe folgen. Ich kenne Excel nicht, daher kann ich in dem Punkt nicht weiter helfen.

grüße
lipi

[Ben10623]

@littlepinguin

das feld dateieingabe14 ist nur das JA oder NEIN =WENN(I12-I13<0;„JA“;„NEIN“)

ich weiß auch nicht wie ich das übersichtlicher gestalten soll. am ende will ich auf einer seite nur die eingaben und das ergebnis haben. das erfordert aber leider unendlich viele formeln und berechnungsfelder die auf der gleichen seite mit den ergebnissen nur für unübersichtlichkeit sorgen.

[Ben10623]

@littlepinguin

der unterhaltsanspruch gleift doch nur auf das ergebnis von feld B5 (deinem formelvorschlag) zu, wie kann das dann zu konflikten führen?

der unterhaltsanspruch ist quasi nur das ergebnis der vorliegenden formeln, und sollte sich mit ändern der zahl in B5 auch automatisch anpassen.

[littlepinguin]

hi,

und i13 bezieht sich auf die Tabelle oder?

Wenn sich i13 daraus berechnet, wie B5 ist um sich den Wert zu holen, dann führt das eben zum Zirkelbezug (=dem rechnen mit sich selbst)
Das muss nicht direkt sein, das kann so viele Umwege beinhalten, wie du magst.
Wenn sich I14 ändert, weil sich B5 ändert, dann kann sich B5 nicht auf I14 beziehen.

Wie gesagt, ich kenne Excel nicht und weiß daher nicht, wie man das ab besten findet oder auflöst oder welche Optionen man überhaupt hat.

Aber wenn Excel dir sagt, dass da ein Zirkelbezug ist, dann gehe ich mal davon aus: da ist einer. Egal ob du ihn siehst, oder nicht.

grüße
lipi

Beispiel: A1=1-B1 B1=wenn(a1=1;1;0)
verstehste?

[littlepinguin]

@littlepinguin

Beispiel: A1=1-B1 B1=wenn(a1=1;1;0)
verstehste?

Edith sagt (nur eben viel zu spät), dass es vielleicht wichtig ist zu erwähnen, dass es egal ist, ob sich praktisch dadurch etwas ändert. Der alleinige Umstand, dass es sich aufeinander bezieht reicht aus.
OO würde sogar meckern, wenn B1=wenn(a1=1;1;1) wäre, obwohl das keine Rolle spielen würde, denn B1 wäre faktisch immer 1.
Das ist als Beispiel vermutlich noch besser geeignet.

grüße
lipi

[Ben10623]

ja klar, wenn das programm es sagt, gehe ich davon aus das da wirklich irgendwo der wurm drin ist.

mein ziel ist es unter berücksichtigung der eingegebenen daten die richtigen positionen aus der tabelle zu ziehen…

vielleicht hat da jemand eine idee?

[littlepinguin]

hi,

was steht in I13? wenn die sich auf B5 bezieht, dann ändere das, damit der Wert direkt aus der Tabelle kommt.

Oder wäre es korrekt, wenn nach dem herabsetzen der Stufe durch den Mangelfall auch wieder ein anderer SB gilt?

Da fehlt einfach das Wissen, wie die Berechnung und die Zusammenhänge überhaupt sind.
Das ist anhand des Beispiels nicht zu erkennen.

grüße
lipi

[Ben10623]

@littlepinguin

in der I13 wird die „Formelliste B9“ wiedergespiegelt. Die Formelliste B9 beinhaltet die Formel =SVERWEIS(Dateneingabe!I8;‚Düsseldorfer Tabelle‘!C9:K21;8)

ja, deine denkweise ist korrekt. Bei herabsetzen der Sufe gilt auch ein anderer Selbstbehalt. Immer dieser, der in der Tabelle rechts außen steht. bei Stufe 1 wäre es dann 1370 Euro.

wenn diese stufe dann erreicht ist, muss wieder geprüft werden ob dann immernoch ein mangelfall vorliegt oder nicht, das soll dann das JA oder NEIN in der Dateieingabe erledigen.

so ganz grob mal zur Unterhaltsberechnung.

Rangezogen wird das Durchschnitts-Nettoeinkommen der letzten 12 Monate. von diesem Betrag dürfen dann einige kostenpunkte abgezogen werden. Was dann übrig bleibt, wird laut Tabelle zur berechnung der unterhaltssätze rangezogen. 100 - 1xx% , je nachdem wie hoch das einkommen ist.

wenn jetzt nach abzugs des laut tabelle fälligen unterhalts weniger übrigbleibt als in der tabelle der selbstbehalt ist, rutscht man in die nächst niedrigere stufe. das geht so lange bis man bei 100% angekommen ist.

ab da wird dann der fällige unterhalt zu gleichen teilen prozentual gemindert bis die 1370 euro an selbstbehalt erreicht sind… man redet da von einem mangelfall.

aber danke das du dir für mich den kopf zerbrichst um mir zu helfen…

[littlepinguin]

hi,

Dann ist der Zirkelbezug ja auch bei den Unterhaltszahlungen gegeben.

Such mal jemand, der sich damit auskennt. Man kann solche Schleifen eine begrenzte Anzahl rechnen lassen und dann stoppt es eben.

Wie gut sich das praktisch macht und wo Fehlerquellen lauern, weiß ich aber nicht.
Ich weiß auch nicht, ob das in deiner Version schon verfügbar ist.

Aus dem Bauch heraus, würde ich Hilfszellen für jede Stufe machen und für jede Stufe rechnen, ob der Selbstbehalt unterschritten wird.
Das beinhaltet dann natürlich auch alle Kinder, die für jede Stufe neu bewertet werden. Aber dafür hast du ja deine Tabelle.
Ich würde noch 2 Kinder hinzufügen.
Am Ende ergibt sich so die höchste Stufe, bei dem der SB nicht unterschritten wird, oder eben 0, dann wird gekürzt.

Ich würde es bewusst aufblähen, damit Änderungen später noch möglich sind und man nicht vor einem Formelsalat sitzt, den man nicht mehr versteht.
Die Berechnung muss ja nicht sichtbar sein, wenn’s um die Optik geht.

grüße
lipi

[Ben10623]

Hi, ich habe jetzt einmal die Tabelle zerlegt.

Auf der seite „Formelliste“ wie üblich meine Berechnungsversuche.

wie bekomme ich das hin, das mir der tabellenwert nach > oder < angezeigt wird?

im aktuellen beispiel liegt das Einkommen bei 2523,70 Euro.

damit wäre das theoretisch stufe 3 mit einem selbstbehalt vor mangelfallprüfung von 1750 euro…

[Ben10623]

@littlepinguin

ich habe jetzt ein neues konstruckt geschaffen. für jede stufe habe ich quasi eine eigene tabelle angelegt. aber wie bekomme ich es hin das mir nicht wieder ein zirkelbezug unterläuft? am ende muss ich ja immerauf bereits errechnete daten zugreifen lassen um neu zu berechnen.

ich bin jetzt gerade wider am nachdenken wie ich diese mangelfalllösung einbringe mit den neuen 13 tabellen… am ende versuche ich ja immer wieder die bereits errechneten daten neu berechnen zu lassen… oder sehe ich das falsch?

[littlepinguin]

hi,

mein Gedanke war, ohne bisher genauer darüber nachgedacht zu haben, es könnten also Fehler enthalten sein.

Du erstellt eine Liste von 1 bis 13. Daneben ziehst du dir für jede der Stufen das Gehalt die SB und die Unterhaltsprozente oder ersatzweise den Unterhalt aller Kinder einzeln.

[-> hier finde ich es aber einfacher, den Unterhalt bei 100% für alle Kinder einmal zusammen zu rechnen und erst dann und als Summe Prozentual zu erhöhen. Sonst musst du jedes Kind für sich behandeln.
Zudem wäre es einfacher die Anzahl der Kinder zu erhöhen, wenn man nur mit der Summe der Unterhaltszahlungen weiter rechnet.
Kommt man auf Stufe 1 unter den SB braucht man die Summe ohnehin. Ich sehe keinen Grund, das nicht direkt zusammen zu fassen. Außer die Beträge passen nicht zu den Prozenten oder müssen unbedingt auf ganze € gerundet werden. Testweise wäre z.B. Alter 6-11 bei Stufe 2 nicht 528 sondern 527,1€
Da fehlt mir das Fachwissen, warum das so ist. ]

Danach schaust du das Einkommen an und ob der SB unterschritten wird. Das ist eine einfache Wenn() abfrage, welche als Ergebnis bei ausreichendem Einkommen die Stufe ausgeben sollte. Wird der SB unterschritten, würde ich 0 ausgeben.

Wenn das für alle 13 Stufen gemacht ist
[ -> machen musst du es nur einmal, wenn du Bereiche und Zellen Absolut angibst, dann kannst du die Formeln nach unten kopieren. ]
hast du eine Spalte mit einigen Nullen und irgendwo auch einigen Stufen. Wie schon mal beschrieben, ist dann die Maximale Stufe deine gesuchte.

Und diese, und nur, diese wird dann weiter verwendet. So ergibt sich nirgendwo ein Bezug auf etwas zuvor berechnetes.

diese Hilfsberechnung würde ich unter der Düsseldorfer-Tabelle anlegen. Die muss niemand sonst sehen.

Vorher würde ich an deiner Stelle aber nochmal kurz nachschlagen, was Sverweis() macht. Deine neuen Tabellenausschnitte legen den Verdacht nahe, dass du das nicht verstanden hast und mit einem funktionierenden Stück Formel arbeitest und die Umgebung so anpasst, dass es damit auch woanders geht.

Viel Text und garantiert auch nicht komplett verständlich, das ist mir schon klar.
Missverständnisse sind eigentlich vorprogrammiert.

Du hast aber sicher Verständnis dafür, dass es ohne die Tabelle vor sich zu haben das fehlerfrei nahezu unmöglich ist.
Das beginnt, wie oben gezeigt, schon damit, dass ich nicht weiß, warum der Unterhalt aufgerundet wird.

Anmerkung: Das Alter ist auch so eine Sache, da würde man die Tabelle doch eher ändern und nicht 0-5, 11-6 schreiben, sondern 5, 11, 17, 100 verwenden, also „Bis zu diesem Alter“ (analog zu „bis zu diesem Einkommen“)
Dann wäre die suche mittels WVerweis() machbar und die rechte Tabelle unnötig.
Das muss nicht für den Menschen lesbar sein, es muss maschinenoptimiert sein.
Wenn es für dein Verständnis der Formeln besser ist, das Alter+2 in Spalten nach rechts zu rücken, dann kannst du das durchaus so lassen.
Dann ist es besser, es belegt unnötig Platz, aber es ist dafür verständlicher.

Die Tabelle jedoch doppelt und dreifach anzulegen, sprengt den Rahmen und führt dazu, dass es unübersichtlich wird.

@Christa

Kannst du die Datei irgendwie bei filehorst.de oder so hochladen

ist vielleicht gar keine schlechte Idee.

grüße
lipi

[Ben10623]

@littlepinguin

warum das bei den prozenten nicht hin haut, erklärt sich mir auch nicht. im ersten versuch hatte ich versucht mit diesen werten zu rechnen… und bin direkt aufs gesicht gefallen… vermutlich haben die nur noch eine nummerische bedeutung.

iteration fehlerhaft…

mal funktionierts, mal nicht. die gelben felder greifen alle auf das blaue zu, nur damit die zuordnung schneller gefunden wird.

theoretisch sollte es bei aktuellen daten wie folgt aussehen:

B9 - 100 %
D5 - 1370
F20 - 437
F21 - 502
M27 - 1370
M28 - NEIN

also teilweise klappt es, und auch wieder nicht. je öfter man aktualisiert, ändert sich auch mal die konstellation.

einmal geschlossen und wieder geöffnet… keine dateneingabe oder sonstige änderung getätigt.

Datei liegt unter https://filehorst.de/d/eGFwbgut

Vielen Dank

[Christa]

Vorab: ich habe weder mit index noch mit vergleich gearbeitet. Aber die Microsoft-Hilfe sagt:

Mit der Funktion VERGLEICH wird in einem Bereich von Zellen nach einem angegebenen Element gesucht und anschließend die relative Position dieses Elements im Bereich zurückgegeben. Ein Beispiel: Wenn der Bereich A1:A3 die Werte „5“, „25“ und „38“ enthält, gibt die Formel =VERGLEICH(25;A1:A3;0) den Wert „2“ zurück, weil „25“ der zweite Wert im Bereich ist.

Du hast in D13 die Formel
=INDEX(D6:D12;VERGLEICH(1;D6:D12;-1))
stehen. Der zweite Teil bedeutet, dass im Bereich D6:D12 geschaut wird, ob die 1 vorhanden ist. Dabei hast du -1 als Vergleichstyp angegeben, das bedeutet:

ÜBEREINSTIMMUNG findet den kleinsten Wert, der größer oder gleich dem Wert lookup_value. Die Werte im Argument lookup_array in absteigender Reihenfolge angegeben werden, z. B. WAHR, FALSCH, Z-A, … 2, 1, 0, -1, -2, … und so weiter.

Ist das überhaupt das, was du möchtest?

Irgendwie beißt sich schon die Katze in den Schwanz, denn dein Wert in M27 wird aus D5 bezogen, D5 benutzt aber D13, welches den Bereich D6:D12 benutzt, in dem sich wiederum Formeln befinden, die sich auf M28 beziehen, welches zur Anzeige den Eintrag in M27 benutzt.

@Ben10623

theoretisch sollte es bei aktuellen daten wie folgt aussehen:

B9 - 100 %

Schon beim Öffnen der Datei steht bei mir da 110 %.

Irgendwie müsste man die ganzen Abhängigkeiten anders auflösen.

[littlepinguin]

hi,

warum wird in M26, dem Restbetrag des Einkommens, nach Abzug der Unterhaltszahlungen, nochmal der Fahrtweg abgezogen?

und warum ist das Bereinigte Einkommen nicht abzüglich der Sonstigen Aufwendungen?

auf deiner Seite Formelliste sehe ich nicht ganz durch, was das soll.
Ich hab das einfach mal ignoriert, ich glaube das sind Überreste der Versuche es hin zu bekommen.

In der Düsseldorfer Tabelle steht die Stufe, die gilt.
Die 3 rechten Spalten sind nur zur Kontrolle, welche Einkommensstufe gewählt wird, und bis wohin die SB reicht.
Trifft beides zu, wird die höchste der möglichen Stufen verwendet.

Ich musste die Berechnung des Altern ändern. Ich habe openoffice und kein Excel. Deine Formel gibt es da nicht.
Möglich, dass es dir jetzt genau so geht, dann kopiere deine da wieder rein.

v0.1: https://filehorst.de/d/eqwCsfql

grüße
lipi