formule door copieren

[tobyke]

Hallo,

Ik heb misschien een hele simpele vraag, maar ik kom er niet uit.

Heb in tabblad 1 van mijn exceldocument leveranciersgegevens per weekdag staan, verschillende leveranciers die elke dag zouden kunnen leveren.
In tabblad 2 staan de weekgegevens opgeteld per leverancier. Er daggegevens worden opgeteld per leverancier.
De optelformule per week wil ik copieren, maar bij copieren hoogt Excel automatisch 1 op. van week 44 naar 45 copieren bijvoorbeeld in het tabblad leverancierwordt opgehoogd van c3 naar C4, terwijl van C3 naar C3+39 = C42 opgehoogd zou moeten worden.
Ik heb zitten rondkijken, kan helaas niet werken met macro's op onze servers (worden na opslaan ongedaan gemaakt oid). Het gaat om een veel uitgebreider productieblad, maar als voorbeeld heb ik onderstaande meegestuurd. Misschien is het iets heel simpels, maar ik kom er echt niet uit. Kan iemand mij uitleggen hoe ik mijn formule makkelijk doorcopieer met een vaste, zelf te bepalen ophoging? Bekijk bijlage Voorbeeld.xlsx

Alvast bedankt!

groetjes van Karin

 

[HSV]

Bv. in B25 en doorvoeren naar B26.

=INDEX('Per dag'!$C:$C;((RIJ()-24)*39) - 1;1)

 

[wildboy]

of bv
Sommen. als met een een hulpkolom in F, deze kan je vervolgens verbergen of tekstkleur wit maken
zie voorbeeld.

 

[tobyke]

Top zeg, ze werken beiden in het voorbeeld document, alleen nog niet in mijn grote document.
Zou u de formule eens toe willen lichten? Misschien verwijs ik niet helemaal goed.

Alvast bedankt!

 

[HSV]

De verwijzing zit in de rij waar je start.

 

[VenA]

Om de werking van formules te begrijpen kan je de Help van Excel gebruiken <F1>. Ook zit er mogelijkheid in Excel waarmee je een formule stap voor stap kan doorgronden. In het lint onder Formules en dan Formule evalueren.

 

[wildboy]

Hoi,

SOMMEN.ALS (werk volgens mij vanaf Excel 2007) is een oplossing als je meer al 1 criteria gebruikt zoals in SOM.ALS
de naam zegt het eigenlijk al, SOMMEN(tel op)ALS indien er voldaan wordt aan een bepaald criteria bij SOMMEN.ALS gaat dit zelfs tot 127 verschillende criteria.

SOMMEN.ALS(optelbereik;criteriabereik1;criterium1;criteriabereik2; criterium2…)

Als we dit toepassen op jou voorbeeld en dan kijken bij mijn eerste voorbeeld in leveranciers B25

=SOMMEN.ALS('Per dag'!C:C;'Per dag'!$B:$B;$B$1;'Per dag'!$F:$F;$A25)

1. optelbereik = wat moet er worden opgeteld ('Per dag'!C:C) de aantallen in kolom C
2. criteriabereik1 = in welke kolom staat het 1ste criteria 'Per dag'!$B:$B al je leveranciers in kolom B
3. criterium1 = wat is het 1ste criterium (in jou geval op het tabblad leverancier in $B$1 stond in je voorbeeld Lev 1 als je dit aanpast naar Lev2 of Lev 3 etz.. krijg je de resultaten van die leverancier
4. criteriabereik2 =in welke kolom staat het 2de criteria 'Per dag'!$F:$F dat is de hulpkolom met het weeknummer die ik heb toegevoegd
5. criterium2 = wat is het 2de criterium $A25 dit verwijst naar het weeknummer in A25 in dit geval week 44

 

[zapatr]

Met de formule van HSV worden de totalen van ALLE leveranciers in een week berekend, terwijl het het totaal van 1 leverancier wordt gevraagd (zie cel B1 in het blad "Leveranciers"). Dat blijkt als je in het blad "Per dag" voor meerdere leveranciers gegevens invult. De oplossing van wildboy is wel juist.

Als de vragenstelster een oplossing wil zonder hulpkolom, dan kan dat bv. zo:
Typ op het blad "Leverancier" in B25:

=SOMMEN.ALS(INDIRECT("'Per dag'!C"&(RIJ()-25)*39+1):INDIRECT("'Per dag'!C"&(RIJ()-24)*39);INDIRECT("'Per dag'!B"&(RIJ()-25)*39+1):INDIRECT("'Per dag'!B"&(RIJ()-24)*39);"=lev 1")

Typ in datzelfde blad in C25:

=SOMMEN.ALS(INDIRECT("'Per dag'!D"&(RIJ()-25)*39+1):INDIRECT("'Per dag'!D"&(RIJ()-24)*39);INDIRECT("'Per dag'!B"&(RIJ()-25)*39+1):INDIRECT("'Per dag'!B"&(RIJ()-24)*39);"=lev 1")

Typ in datzelfde blad in D25:

=ALS(B25>0;C25/B25;"")

Kopieer deze 3 formules naar beneden.

Wil je de formules voor alle weken van het jaar gaan gebruiken (in het blad "Per dag" start je dan met week 1 en in het Blad "Leverancier" staan dan in kolom A vanaf A4 de weeknummers, te beginnen met 1 in A4, 2 in A5, enz.), dan kun je in het blad "Leverancier" in B4 deze formule gebruiken:

=SOMMEN.ALS(INDIRECT("'Per dag'!C"&(RIJ()-4)*39+1):INDIRECT("'Per dag'!C"&(RIJ()-3)*39);INDIRECT("'Per dag'!B"&(RIJ()-4)*39+1):INDIRECT("'Per dag'!B"&(RIJ()-3)*39);"=lev 1")

in C4:

=SOMMEN.ALS(INDIRECT("'Per dag'!D"&(RIJ()-4)*39+1):INDIRECT("'Per dag'!D"&(RIJ()-3)*39);INDIRECT("'Per dag'!B"&(RIJ()-4)*39+1):INDIRECT("'Per dag'!B"&(RIJ()-3)*39);"=lev 1")

en in D4:

=ALS(B4>0;C4/B4;"")

Het is wel noodzakelijk dat in het bestand waarin je de formules gaat gebruiken, de tabellen in het blad "Per dag" er hetzelfde uitzien als in het bestand dat je hier plaatste, d.w.z. dat de tabel van de eerste week begint in rij 2 en dat er zich tussen de weektabellen telkens 2 rijen bevinden. En in het blad "Leverancier" moeten de formules beginnen in rij 4.