Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


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)
to 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

Excel 2007
to top
#3
Hallo Attila,

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

Hardbopper
to 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

Excel 2007
to 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ß


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
to top
#6
Hallo, Edgar,

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

Gruß

Hardbopper
to 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)
to 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 XL2013
[-] Folgende(r) 1 Benutzer sagt Danke an Jockel für diesen Beitrag:
Hardbopper
to 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. 
to 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 XL2013
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  VBA Solver mit zusätzlicher Variable und Zielwert eränzen tschibolli 2 77 22.11.2016, 08:11
Letzter Beitrag: tschibolli
  Variable in Makro VaterBaum 12 165 12.11.2016, 20:34
Letzter Beitrag: AlterDresdner
  Inhalt Multiselect-ListBox in Variable auslesen Rabe 3 83 28.10.2016, 12:04
Letzter Beitrag: Rabe
  Variable Nachtzeiten und Zuschläge Chimera 2 111 28.10.2016, 00:28
Letzter Beitrag: Chimera
  Zellenformeln verkürzen durch Namen definieren (variable) Woofer 2 101 27.10.2016, 15:46
Letzter Beitrag: BoskoBiati
  Wort und Prozentzahl mit einer Formel verknüpfen Rondon 5 138 12.10.2016, 13:59
Letzter Beitrag: Rondon
Question Variable Höhe /Breite eines Excel VBA-UserForms GuteFrage 2 157 25.09.2016, 11:04
Letzter Beitrag: schauan
  Formelsuche: "Textwert" und variable Zahl frylich 22 740 02.09.2016, 13:45
Letzter Beitrag: Peter
  Vereinfachung einer Excel Formel Luffffy 11 549 18.08.2016, 15:02
Letzter Beitrag: steve1da
  Frage zu einer Formel WENN(UND Pulsaro 5 168 10.08.2016, 12:01
Letzter Beitrag: Pulsaro

Gehe zu:


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