Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

variable Spaltenbezeichnung in einer Formel
#1
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 




.xlsx   Kopie Heizölverbrauch.xlsx (Größe: 17,41 KB / Downloads: 9)
Antwortento top
#2
Hallo,

ich würde die Datentabelle so aufbauen:

Arbeitsblatt mit dem Namen 'Tabelle2'
 AB
1DatumVerbrauch
201.01.2005365,13
301.02.2005651,32
401.03.2005394,74
501.04.2005261,51
601.05.2005182,57
701.06.2005113,49
801.07.200569,08
901.08.200593,75
1001.09.2005182,57
1101.10.2005143,09
1201.11.2005458,88
1301.12.2005379,93
1401.01.2006675,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'
 EFGHIJ
2Summe von VerbrauchJahre    
3Datum2005200620142015Gesamtergebnis
4Jan365,13675,99385,86405,591832,57
5Feb651,32473,68319,74410,531855,26
6Mrz394,74523,03258,55332,571508,88
7Apr261,51236,84157,89184,14840,39
8Mai182,57162,83153,95 499,34
9Jun113,49108,5588,82 310,86
10Jul69,0844,41109,54 223,03
11Aug93,75103,6282,89 280,26
12Sep182,57138,1691,78 412,50
13Okt143,09143,09161,84 448,03
14Nov458,88291,12272,37 1022,37
15Dez379,93463,82384,87 1228,62
16Gesamtergebnis3296,053365,132468,091332,8210462,10
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß Atilla
Antwortento top
#3
Hallo Attila,

ich habe schon gemerkt, Du hast es mit Pivottabellen, aber was löst das an meinem Problem ?

Hardbopper
Antwortento top
#4
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.
Gruß Atilla
Antwortento top
#5
Hallo,

wenn Du das so möchtest. Einige Deiner Formel sind überdimensioniert, die habe ich mal etwas überarbeitet.

Edit:  Datei überarbeitet!!!!


Angehängte Dateien
.xlsx   Kopie Heizölverbrauch.xlsx (Größe: 15,72 KB / Downloads: 11)
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Benutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • Hardbopper
Antwortento top
#6
Hallo, Edgar,

herzlichen Dank für alles, das clever Excel - Forum hilft einem doch immer wieder aus der Patsche.

Gruß

Hardbopper
Antwortento top
#7
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 


.xlsx   Monat aus Anzahl der Eintragungen.xlsx (Größe: 20,16 KB / Downloads: 3)
Antwortento top
#8
Hallo, kannst du mal verraten was das hier soll..?

Arbeitsblatt mit dem Namen 'Tabelle1'
 BCDE
45Januar31  
46Februar285929,50
47März319030,00
48April3012030,00
49Mai3115130,20
50Juni3018130,17
51Juli3121230,29
52August3124330,38
53September3027330,33
54Oktober3130430,40
55November3033430,36
56Dezember3136530,42

ZelleFormel
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.")
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2016
[-] Folgende(r) 1 Benutzer sagt Danke an Jockel für diesen Beitrag:
  • Hardbopper
Antwortento top
#9
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. 
Antwortento top
#10
(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...
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2016
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste