Microsoft Excel

Herbers Excel/VBA-Archiv

Tarifzuordnung nach Datum und Tagen | Herbers Excel-Forum


Betrifft: Tarifzuordnung nach Datum und Tagen von: Hans Werner
Geschrieben am: 29.01.2010 20:51:10

Hallo,

Ich habe in der Beispieldatei https://www.herber.de/bbs/user/67634.xls ein Problem mit der Zuordnung eines Tarifs, wenn die Tage bzw. das Datum für den entsprechenden Tarif vorgegeben sind.
In der oberen Tabelle sind die entsprechenden Tarifdaten vorgegeben.
In den Zellen J7 bis J11 sind die entsprechenden Tage angegeben für die der entsprechende Tarif gültig ist.
In der unteren Tabelle soll entsprechend in den Zellen C19 bis C30 der Tarif angegeben werden, der für den entsprechenden Monat gültig ist.
Wenn aus der oberen Tabelle also der Tarif 1 für 128 Tage gültig ist, dann müssen in den Zellen C19-C30
der entsprechende Tarif stehen also T1,T2 oder T3.
Also müßte von Januar bis Mai der Tarif 1 gültig sein, dann käme ein Tarifwechsel. Wenn ein Tarifwechsel stattfindet, dann soll in der Spalte "Rest" die entsprechenden restlichen Tage für den Folgetarif ausgegeben werden.
Im nächsten Monat wäre dann der Termin 2 gültig usw.
Leider komme ich dabei mit meinen bescheidenen Kenntnissen nicht weiter.

Vielleich hat jemand eine Lösung

mfg

Hans Werner

  

Betrifft: AW: Tarifzuordnung nach Datum und Tagen von: ChristianM
Geschrieben am: 30.01.2010 10:55:48

Hallo Hans Werner,
erste Regel - keine verbundenen Zellen

wie auch immer - hier ein Ansatz (geht bestimmt noch eleganter...)
In Hilfsspalte O:
- in O7 die Formel:
=WENN(J7="";"";SUMME(J$7:J7))

- Formel runterziehen bis O12

In Spalte C:
- in C19 die Formel:
=INDEX($B$7:$B$12;VERGLEICH(KGRÖSSTE($O$7:$O$12;ZÄHLENWENN($O$7:$O$12;">="&SUMME(D$19:D19))); $O$7:$O$12;0))

- Formel runterziehen bis C30

In Spalte F:
- in F19 die Formel:
=WENN(C20<>C19;KGRÖSSTE($O$7:$O$12;ZÄHLENWENN($O$7:$O$12;">="&SUMME(D$19:D19))) -SUMME(D$19:D19);"")

- Formel runterziehen bis F30

et voila
Gruß
Christian


  

Betrifft: AW: Tarifzuordnung nach Datum und Tagen von: Hans Werner
Geschrieben am: 30.01.2010 18:07:38

Hallo Christian,

vielen Dank für Deine Hilfe.
Dein Lösungsansatz bringt auf jeden Fall die gewünschten Ergebnisse.
Ich würde aber auch gern verstehen wollen, was genau in den folgenden Formeln passiert:

- in C19 die Formel:
<b>=INDEX($B$7:$B$12;VERGLEICH(KGRÖSSTE($O$7:$O$12;ZÄHLENWENN($O$7:$O$12;">="&SUMME(D$19:D19))); $O$7:$O$12;0))</b>~f~

- in F19 die Formel:
~f~<b>=WENN(C20<>C19;KGRÖSSTE($O$7:$O$12;ZÄHLENWENN($O$7:$O$12;">="&SUMME(D$19:D19))) -SUMME(D$19:D19);"")</b>

Vielleicht kannst Du mir ja mal mit ein paar einfachen Worten auf die Sprünge helfen ?


mfg
Hans Werner


  

Betrifft: AW: Tarifzuordnung nach Datum und Tagen von: Hans Werner
Geschrieben am: 31.01.2010 01:13:50

Hallo,

ich habe nochmal ein wenig mit der Tabelle herumexperimentiert, und doch noch einen Fehler bemerkt.
Ich habe die Beispieldatei nochmal hochgeladen.

https://www.herber.de/bbs/user/67655.xls

Mit den eingegebenen Daten sollte in Spalte C19 der Tarif 1 ausgewählt sein, der Tarif 2 ist zwar ab 15.01 gültig, aber es soll dann immer der vorherige Tarif angegeben werden. In Zelle E19 soll dann angegeben werden, wielang der Folgetarif (Tarif 2) gültig ist, also noch 16 Tage.
In Zelle C20 soll dann T2 stehen usw.
Wahrscheinlich ist es nur eine Kleinigkeit, aber ich mußte bisjetzt leider passen!


mfg
Hans Werner


  

Betrifft: AW: Tarifzuordnung nach Datum und Tagen von: ChristianM
Geschrieben am: 31.01.2010 12:52:25

Hallo,
zu den verwendeten Formeln (IMHO sollten dir diese bei "Excel-gut" bestens bekannt sein):
siehe Excel-Hilfe zu Index und Vergleich.

Bei Vergleich wird hier als Suchkriterium der Wert ermittelt, der größer/gleich der Summe aller Tage bis zum aktuellen Monat ist. Wie gesagt ginge das auch einfacher - das größte Handicap sind deine verbundenden Zellen - ebenso habe ich hier auf Matrixfomeln verzichtet.

Du hast Recht, die Formel berücksicht nicht, dass die Tarifdauer kleiner als ein Monat sein kann. Dies betrifft also nicht nur den ersten Monat, sondern auch jeden weiteren, in dem mehrmals pro Monat der Tarif gewechselt wird. Das lässt sich IMHO auch nicht so ohnes Weiteres erschlagen - denn entweder sucht du den Wert der kleiner als der Größte ist oder jenen, der größer als der Kleinste ist - beides zusammen wird schwierig. Ich empfehle eine Überarbeitung der Tabellenstruktur und/oder eine Lösung per VBA Script.

Grüße
Christian