Wie bilde ich per Excel Formel einen Zahlenbereich auf einen anderen ab?
Hallo Leute,
meine kleinen Excelkenntnisse reichen mal wieder nicht aus.
Ich möchte eine Formel in eine Zelle bringen, die mir verschiedene Möglichkeiten einräumen soll. Je nach der Zahl von 1 bis 12, welche in Zelle A1 steht, soll automatisch festgelegt sein, welchen Inhalt die Zelle A2 bekommt.
Ein schönes Beispiel sind die 12 Monate, die unterschiedliche Tageszahlen haben. Wenn ich also in Zelle A1 die Zahl 1 stehen habe, so bedeutet es "Januar", welcher 31 Tage hat. Somit soll in Zelle A2 die Zahl 31 stehen. Wenn dort die Zahl 2 steht, so soll in A2 die Zahl 59 (31+28) stehen usw.
Ihr könnt mir sicherlich helfen... hat beim letzten Mal auch super geklappt.
Danke Euch
4 Antworten
Allgemein kannst du so etwas mit der Funktion WAHL oder SVERWEIS umsetzen. Bei den Monaten kannst du das natürlich über ein Format lösen.
=WAHL(A1;"Jan";"Feb";"Mrz";"Apr";"Mai";"Jun";"Jul";"Aug";"Sep";"Okt";"Nov";"Dez")
=SVERWEIS(A1;{1."Jan";2."Feb";3."Mrz";4."Apr";5."Mai";6."Jun";7."Jul";8."Aug";9."Sep";10."Okt";11."Nov";12."Dez"};2)
Wenn ich also in Zelle A1 die Zahl 1 stehen habe, so bedeutet es den Januar, welcher 31 Tage hat. Somit soll in Zelle A2 die Zahl 31 stehen. Wenn die Zahl 2 steht, so soll in A2 die Zahl 59 stehen usw.
Du willst also kumulieren? Für dieses Jahr würde das zum Beispiel gar nicht passen. Kumulieren würde so funktionieren:
=WAHL(A1;31;59;90;120;151;181;212;243;273;304;334;365)
oder
=SVERWEIS(A1;{1.31;2.59;3.90;4.120;5.151;6.181;7.212;8.243;9.273;10.304;11.334;12.365};2)
Mit dem SVERWEIS kann man auch Bereiche abbilden. Eventuell musst du für WAHL und SVERWEIS eine Normalisierung vorschalten.
Noch eine Möglichkeit:
=SUMMENPRODUKT((ZEILE($1:$12)<=A1)*{31;28;31;30;31;30;31;31;30;31;30;31})
ich fürchte zwar, dass das Beispiel nur ein sehr spezielles Nebengleis ist, aber wenn in A1 die Monatszahl steht, dann (für 2016):
=MONATSENDE("1.1.16";A1)-"31.12.15"
Das liefert die kumulierten Tage, auch in Schaltjahren richtig.
Ansonsten den Bereich 1..12 definieren (sei A1..A12) und daneben in B das einzutragende Argument auflisten. Formel:
=index(B1:B12;Vergleich(Wunschzahl;A1:A12;0);1)
Wichtig: Wenn der Bereich in B mit Zeile 1 beginnt, ist die Nr. der Vergleichszelle gleich der Zeile des gefundenen Infex-Werts. Ist so am einfachsten, da ansonsten Korrekturglied nötig!
da ist mir noch aufgefallen, dass in der Monatsendeformel die 1 in A1 den Februar liefert, da A1 ein Addend für den genannten Monat (=0) ist. Für eine Monatsliste von 1..12 muss die Formel also so heissen:
=MONATSENDE("1.1.16";A1-1)-"31.12.15"
anstatt des 31.12.15 könntest Du auch -"1.1.16"+1 nehmen (-1.1.16 zieht ja einen Tag zuviel ab, der wieder draufgeschlagen werden muß)
die Index-Formel stimmt aber so. Wobei A1:A12 durchaus auch woanders stehen könnte, zB in X11:X22, die Vergleichsfunktion liefert nämlich nur die Listennummer der übereinstimmenden Zelle im VergleichsBereich, Index holt aus dem Bereich B1:B12 dann den Wert mit der gleichen Listennummer. Aber natürlich ist es übersichtlicher, wenn die zugehörigen Werte nebeneinander stehen.
Im Gegensatz zum SVerweis kann in Index die Ergebnisspalte aber auch links von der Vergleichsspalte stehen und Du siehst auch sofort, dass der Wert aus B1:B12 geholt wird. (es gibt noch ein paar Vorteile mehr)
Das würde hiermit gehen, wobei du entweder das tatsächliche Jahr einträgt oder ein Nicht-Schaltjahr fest vorgibts (mit dem Jahr 2016 ergeben sich ja 60 Tage bis Ende Februar statt 59...)
=TAGE(DATUM(2016;A1+1;1);DATUM(2016;1;1))
Eure Tipps waren gut.... jedoch ist mir noch ein kleines Problem aufgefallen, wenn ich beispielsweise diese Formel nehme:
=WAHL(A1;31;59;90;120;151;181;212;243;273;304;334;365)
wenn in Zelle A1 die Null steht, dann habe ich ERROR.... da gibt es sicherlich ne kleine Ergänzung, wie ich das hinbekommen kann. Wer kann helfen?
das könntest du so lösen
=WENNFEHLER(WAHL(A1;31;59;90;120;151;181;212;243;273;304;334;365);"")
oder
=Wahl(A1+1;0;31;28;...
also einfach ein zusätzliches Glied für die Liste und A1-korrigiert darauf verweisen.
aber wennfehler ist eine gute Lösung.
Also soll wenn in A1 12 steht, dann A2 365?
ich kenne nur "meine" Lösung, schreibe ich dir wenn keine bessere Antwort kommt.
Na, nun tu mal nicht so geheimnisvoll, hier braucht sich keiner zu genieren, wenn sein Vorschlag noch nicht optimal ist!
DH!
Auf das in der Frage vorkommende Szenario angepasst wäre das dann z.B.:
=WAHL(A1;31;59;89;120;...usw.)