In Excel de verwijzing naar een cel variabel maken.

Question

Hallo allemaal,

Ik heb een excel bestand gemaakt waarin ik per jaar mijn energieverbruik en de opbrengst van de zonnepanelen bijhoudt

Op het tabblad "data" staat de hoofdtabel waarop de meterstanden staan:

kolom A|: datum,

kolom B, meterstand verbruik laag tarief;

kolom C meterstand verbruik hoog tarief;

kolom D meterstand teruglevering laag tarief;

kolom E meterstand teruglevering hoog tarief

enz...

Voor elk jaar gebruik ik een nieuw tabblad waarop het daadwerkelijk verbruik en verschil t.o.v de vorige jaren wordt berekend en weergegeven in grafieken. De meterstanden worden van het tabblad "data" gehaald.

Nu komt het::elke keer als ik voor een jaar nieuw tabblad maak doe ik dit door een kopie te maken van een bestaand tabblad. Alleen moet dan alle verwijzingen handmatig veranderen. Is het mogelijk de verwijzingen variabel te maken door bijvoorbeeld gebruik te maken van het jaartal dat in cel P2 van de sheet staat.

ik heb hierover 2 vragen:

1: Op tabblad "meterstanden 2017" staat cel "Q17" de meterstand van december 2017 die wordt opgevraagd: door "=data!B134"

Op het tabblad "meterstanden 2018" wordt in cel Q5 nu ook de meterstand december 2017 opgevraagd door "=data!B134" alleen moet ik elke keer als ik een nu een nieuw tabblad maak deze verwijzing handmatig aanpassen. Is het mogelijk om de wijzing te doen door =data!E(dan een formule waarin gebruik wordt gemaakt van het jaartal in P2). Als gezegd staat P2 de waarde van het jaar: 2018.

2. in cel Q6 staat de meterstand van januari 2018 dit wordt nu opvraagt door "=data!B135" Dit volgt op het tabblad "data" op de meterstand van december
3. (maar verwijzing Q6 t/m Q17 moet ik nu elke keer handmatig aanpassen) Is het mogelijk door bv gebruik te maken van een index kolom met als inhoud de getallen 1 t/m 12 en het jaartal "=data!B134" per maand automatisch op te hogen? 

Hieronder een stukje van het blad 'meterstanden 2018'

De maanden staan in kolom P.

De meterstanden =data!... staan in kolom Q.

En het verbruik van de maand wordt in de kolom R uitgerekend door de het verschil van bv Q6-Q5 uit te rekenen.

--------kolom (P)--------- kolom (Q) ------------------------kolom (R)

rij-5--------------------------=data!B134      <<< (dit is december van het vorige jaar)

rij-6-----januari------------=data!B135------------------------=ALS(Q6="";NB();Q6-Q5)

rij-7-----februari-----------=data!B136------------------------=ALS(Q7="";NB();Q7-Q6)

rij-8-----maart--------------=data!B137------------------------=ALS(Q8="";NB();Q8-Q7)

rij-9-----april----------------=data!B138------------------------=ALS(Q9="";NB();Q9-Q8)

rij-10----mei----------------=data!B139------------------------=ALS(Q10="";NB();Q10-Q9)

rij-11----juni----------------=data!B140------------------------ =ALS(Q11="";NB();Q11-Q10)

rij-12----juli-----------------=data!B141------------------------=ALS(Q12="";NB();Q12-Q11)

rij-13----augustus---------=data!B142------------------------=ALS(Q13="";NB();Q13-Q12)

rij-14----september-------=data!B143------------------------=ALS(Q14="";NB();Q14-Q13)

rij-15----oktober------------=data!B144------------------------=ALS(Q15="";NB();Q15-Q14)

rij-16----november--------=data!B145------------------------=ALS(Q16="";NB();Q16-Q15)

rij-14----december--------=data!B146------------------------=ALS(Q17="";NB();Q17-Q16)

En ophet blad 'data' staan: 

in rij 134 de gegevens van december 2017;

in rij 135 de gegevens van jan 2018;

in rij 136 de gegevens van febr 2018

in rij 137 de gegevens van maart 2018

enz.....

Ik geprobeerd de vraag zo duidelijk mogelijk te beschrijven en hoop dat iemand me verder kan helpen

Willem

Answer 1

Hallo Willem,

Ik voel me helemaal niet beledigd. Het is beter als iemand een oplossing zoekt die hij begrijpt dat klakkeloos een formule over te nemen.

Stel in A1 heb je het jaartal (2018)

Dan kan je alles in 1 formule stoppen.

=VERT.ZOEKEN(DATUM(2018;RIJEN($5:5);0);data!$A:$K;5;ONWAAR)

Zoals ik al zei, dag 0 van de maand is de laatste dag van vorige maand. Hetzelfde als wat LAATSTE.DAG doet.

Elk argument van een functie mag op zichzelf een functie zijn. Je hoeft dus niet eerst de laatste dag te berekenen om VERT.ZOEKEN te gebruiken.

Als volgende formule zou je zelfs alles in 1 formule kunnen combineren.

=ALS(VERT.ZOEKEN(DATUM(2018;RIJEN($5:6);0);data!$A:$K;5;ONWAAR)=0;NB();VERT.ZOEKEN(DATUM(2018;RIJEN($5:6);0);data!$A:$K;5;ONWAAR)-D5)

Answer 2

Hallo Willem,

Kopieer gewoon delen van je tabs en plak het hier.

Answer 3

	=ALS(data!E134="";NB();data!E134)	←31-12-2017
januari	=ALS(data!E135="";NB();data!E135)	=ALS(R6="";NB();R6-R5)
februari	=ALS(data!E136="";NB();data!E136)	=ALS(R7="";NB();R7-R6)
maart	=ALS(data!E137="";NB();data!E137)	=ALS(R8="";NB();R8-R7)
april	=ALS(data!E138="";NB();data!E138)	=ALS(R9="";NB();R9-R8)
mei	=ALS(data!E139="";NB();data!E139)	=ALS(R10="";NB();R10-R9)
juni	=ALS(data!E140="";NB();data!E140)	=ALS(R11="";NB();R11-R10)
juli	=ALS(data!E141="";NB();data!E141)	=ALS(R12="";NB();R12-R11)
augustus	=ALS(data!E142="";NB();data!E142)	=ALS(R13="";NB();R13-R12)
september	=ALS(data!E143="";NB();data!E143)	=ALS(R14="";NB();R14-R13)
oktober	=ALS(data!E144="";NB();data!E144)	=ALS(R15="";NB();R15-R14)
november	=ALS(data!E145="";NB();data!E145)	=ALS(R16="";NB();R16-R15)
december	=ALS(data!E146="";NB();data!E146)	=ALS(R17="";NB();R17-R16)
		=AGGREGAAT(9;6;S6:S17)

30-9-2017	1432	1071	348	758	173,1	738	706
31-10-2017	1541	1152	372	801	99,5	770	718
30-11-2017	1637	1269	382	820	58	833	729
31-12-2017	1773	1365	388	824	23,39	947	743
31-1-2018	1925	1463	392	837	27,78	1043	754
28-2-2018	2090	1535	413	911	132,4	1175	767
31-3-2018	2228	1603	436	975	162	1250	778
30-4-2018	2384	1652	467	1076	212	1280	790
31-5-2018	2516	1692	523	1220	315,25	1306	802
30-6-2018	2636	1743	568	1336	227,59	1331	814
31-7-2018	2751	1793	635	1491	350,1	1353	827
31-8-2018
30-9-2018
31-10-2018
30-11-2018
31-12-2018
31-1-2019
28-2-2019
31-3-2019

Answer 4

Dag Willem,

Je moet dit anders aanpakken, ik zie dat je mooi de data noteert op de laatste dag van de maand...

2018
dec	31/12/2017	=DATUM($A$1;RIJEN($5:5);0)
Jan	31/01/2018
	28/02/2018
	31/03/2018
	30/04/2018

In het jaarblad in A1 het jaar. Met de formule bereken je dan einde maand. Gewoon formule in B5 naar beneden slepen.

Dag 0 van een maand is de laatste dag van vorige maand maw DATUM(2018;1;0) = 31/12/2017 want RIJEN($5:5) =1. In RIJ 6 wordt dit dan DATUM(2018;2;0) wat dan eind januari is.

Met VERT.ZOEKEN moet je dan gemakkelijk de meterstanden kunnen ophalen.

Blijft dan nog de index te relativeren, dat kan met KOLOMMEN net zoals in RIJEN.

Answer 5

goedemiddag JP Ronse,

bedankt voor je antwoord. ik ga er volgende week mee aan het werk. vandaag viert mijn dochter haar 12e verjaardag.

groetjes,

Willem