In der beigefügten Datei ist in Zelle X23 die Monatsspanne ausgewiesen, die den bereits erfassten Werten des Jahres 2015 in Spalte M entspricht. Die dabei eingesetzte Formel ist insofern statisch, als sie sich ausdrücklich auf Spalte M bezieht.
Wie kann man sie so verändern, dass sie - etwa durch Bezug auf Zelle Y19 ( = das
jeweils lfd. Jahr ) auch in anderen Jahren noch zu zutreffenden Ergebnissen führt ?
Für alle Lösungsvorschläge vorab herzlichen Dank.
Hardbopper
[
attachment=1873]
Hallo,
ich würde die Datentabelle so aufbauen:
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| | A | B |
| 1 | Datum | Verbrauch |
| 2 | 01.01.2005 | 365,13 |
| 3 | 01.02.2005 | 651,32 |
| 4 | 01.03.2005 | 394,74 |
| 5 | 01.04.2005 | 261,51 |
| 6 | 01.05.2005 | 182,57 |
| 7 | 01.06.2005 | 113,49 |
| 8 | 01.07.2005 | 69,08 |
| 9 | 01.08.2005 | 93,75 |
| 10 | 01.09.2005 | 182,57 |
| 11 | 01.10.2005 | 143,09 |
| 12 | 01.11.2005 | 458,88 |
| 13 | 01.12.2005 | 379,93 |
| 14 | 01.01.2006 | 675,99 |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Dann kannst Du alle möglichen Auswertungen ganz bequem mit einer Pivottabelle machen.
Beispiel auf der Basis obigen Tabellenausschnitts:
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| | E | F | G | H | I | J |
| 2 | Summe von Verbrauch | Jahre | | | | |
| 3 | Datum | 2005 | 2006 | 2014 | 2015 | Gesamtergebnis |
| 4 | Jan | 365,13 | 675,99 | 385,86 | 405,59 | 1832,57 |
| 5 | Feb | 651,32 | 473,68 | 319,74 | 410,53 | 1855,26 |
| 6 | Mrz | 394,74 | 523,03 | 258,55 | 332,57 | 1508,88 |
| 7 | Apr | 261,51 | 236,84 | 157,89 | 184,14 | 840,39 |
| 8 | Mai | 182,57 | 162,83 | 153,95 | | 499,34 |
| 9 | Jun | 113,49 | 108,55 | 88,82 | | 310,86 |
| 10 | Jul | 69,08 | 44,41 | 109,54 | | 223,03 |
| 11 | Aug | 93,75 | 103,62 | 82,89 | | 280,26 |
| 12 | Sep | 182,57 | 138,16 | 91,78 | | 412,50 |
| 13 | Okt | 143,09 | 143,09 | 161,84 | | 448,03 |
| 14 | Nov | 458,88 | 291,12 | 272,37 | | 1022,37 |
| 15 | Dez | 379,93 | 463,82 | 384,87 | | 1228,62 |
| 16 | Gesamtergebnis | 3296,05 | 3365,13 | 2468,09 | 1332,82 | 10462,10 |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Hallo Attila,
ich habe schon gemerkt, Du hast es mit Pivottabellen, aber was löst das an meinem Problem ?
Hardbopper
Hallo,
das sollte eine allgemeine Empfehlung sein, wie Du Deine Daten am besten listest um dann wesentlich einfacher und effektiver diese auszuwerten.
Wenn Du es so machst, wie ich vorschlug, dann kannst Du ohne Formeln und ohne große Anstrengung die verschiedensten Auswertungen realisieren.
Du kannst nach Jahren, Monaten, Quartalen auswerten, Du kannst Jahre, Monate etc. miteinander vergleichen.
Du kannst alles bequem grafisch darstellen, wie gesagt ohne Formeln meist nur mit der Maus.
Hallo,
wenn Du das so möchtest. Einige Deiner Formel sind überdimensioniert, die habe ich mal etwas überarbeitet.
Edit: Datei überarbeitet!!!!
Hallo, Edgar,
herzlichen Dank für alles, das clever Excel - Forum hilft einem doch immer wieder aus der Patsche.
Gruß
Hardbopper
Hallo Edgar,
ich muss zu diesem Thema doch noch einmal Deine Hilfe in Anspruch nehmen:
Zunächst ein paar Kleinigkeiten: Der Multiplikator in der Formel
="= Jan. - "&TEXT(ANZAHL(INDEX($C$19:$R$30;;Y19-2004))*29;"MMM") beruht wohl auf einer Überlegung, wie ich sie in der Tabelle ab Zeile 45 dargestellt habe. Ich habe daher den Multiplikator entsprechend erhöht, ohne dass das Ergebnis beeinträchtigt worden wäre. Außerdem würde mir zur Berechnung der Spaltenzahl
Y19-(2005-1) besser gefallen, weil die Jahreszahl 2004 in der Tabelle nicht erscheint. Aber das sind nur Randbemerkungen.
In der Hauptsache halte ich die verwendete Formel für fehleranfällig, weil sie mit der Berechnung über die Anzahl der Eintragungen in der maßgeblichen Spalte dann einen falschen Monat auswirft, wenn einer der bereits abgelaufenen Monate (hier: M19:M22) - aus welchen Gründen auch immer - ohne Eintrag geblieben ist (auch kein Null-Wert). Wenn ich z.B. den Wert für den Monat Februar 2015 entferne, erscheint als Rechenergebnis "MÄRZ", obwohl der Aprilwert schon eingetragen ist.
Kannst Du die Formel so verändern, dass dies ausgeschlossen wird ?
Gruß Hardbopper
[
attachment=1916]
Hallo, kannst du mal verraten was das hier soll..?
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| | B | C | D | E |
| 45 | Januar | 31 | | |
| 46 | Februar | 28 | 59 | 29,50 |
| 47 | März | 31 | 90 | 30,00 |
| 48 | April | 30 | 120 | 30,00 |
| 49 | Mai | 31 | 151 | 30,20 |
| 50 | Juni | 30 | 181 | 30,17 |
| 51 | Juli | 31 | 212 | 30,29 |
| 52 | August | 31 | 243 | 30,38 |
| 53 | September | 30 | 273 | 30,33 |
| 54 | Oktober | 31 | 304 | 30,40 |
| 55 | November | 30 | 334 | 30,36 |
| 56 | Dezember | 31 | 365 | 30,42 |
| Zelle | Formel |
| D46 | =SUMME(C$45:C46) |
| E46 | =D46/ANZAHL(C$45:C46) |
| D47 | =SUMME(C$45:C47) |
| E47 | =D47/ANZAHL(C$45:C47) |
| D48 | =SUMME(C$45:C48) |
| E48 | =D48/ANZAHL(C$45:C48) |
| D49 | =SUMME(C$45:C49) |
| E49 | =D49/ANZAHL(C$45:C49) |
| D50 | =SUMME(C$45:C50) |
| E50 | =D50/ANZAHL(C$45:C50) |
| D51 | =SUMME(C$45:C51) |
| E51 | =D51/ANZAHL(C$45:C51) |
| D52 | =SUMME(C$45:C52) |
| E52 | =D52/ANZAHL(C$45:C52) |
| D53 | =SUMME(C$45:C53) |
| E53 | =D53/ANZAHL(C$45:C53) |
| D54 | =SUMME(C$45:C54) |
| E54 | =D54/ANZAHL(C$45:C54) |
| D55 | =SUMME(C$45:C55) |
| E55 | =D55/ANZAHL(C$45:C55) |
| D56 | =SUMME(C$45:C56) |
| E56 | =D56/ANZAHL(C$45:C56) |
... und in der angesprochenen Formel kannst du das so abändern... (weil 2005 wird ja nicht in Stein gemeißelt sein..??)
="= Jan. - "&TEXT(VERGLEICH(0;INDEX($C$19:$R$30;;Y19-2004);-1)*28;"MMM.")
Hallo Edgar, hallo Jockel,
ich habe zwar einen Weg gefunden, auch bei unterbrochenen Einträgen in die Spalte des lfd. Jahres den letzten Wert, dessen Zeilennummer und damit die zugehörige Monatsbezeichnung zu ermitteln, im Nachhinein aber festgestellt, dass damit auch die Berechnungen für das Vorjahr und das "beste" Jahr umgestellt werden müssten. Das war mir dann aber doch zu viel Aufwand, sodass es bei der ursprünglichen Version verbleibt. Ich habe lediglich einen deutlichen Hinweis angebracht, auf Lücken in der Dateneingabe zu verzichten ( notfalls Nullwert ).
Fazit: Die Perfektion hat auch irgendwo ihre Grenzen.
Trotzdem nochmals vielen Dank für Eure Beteiligung.
Hardbopper
P.S. Die kleine Tabelle ermittelt den möglichen rechnerischen Abstand zwischen aufeinanderfolgenden Monaten, um die Grenzwerte für den Multiplikator zur Anzeige des richtigen Monats zu erfassen.
(11.04.2015, 00:30)Hardbopper schrieb: [ -> ]Hallo ...
P.S. Die kleine Tabelle ermittelt den möglichen rechnerischen Abstand zwischen aufeinanderfolgenden Monaten, um die Grenzwerte für den Multiplikator zur Anzeige des richtigen Monats zu erfassen.
Hallo, ich verstehe es immer noch nicht. Denke auch, dass sie nicht not tut. Aber das musst du wissen... deine Anfrage scheint ja hier abgeschlossen...