Excel vraag: kan ik samengevoegde cellen opnemen in formule?

[Bloedvat]

Mijn vraag
Kan iemand mij vertellen hoe ik een samengevoegde kolom kan toevoegen aan een formule? Is dit überhaupt mogelijk?
Hopelijk kan ik mijn vraag hieronder een beetje duidelijker maken

Relevante software en hardware die ik gebruik
Excel 2016

Wat ik al gevonden of geprobeerd heb
Ik heb even als voorbeeld een tabelletje gemaakt:

Hoe kan ik er nu voor zorgen dat in in kolom F de samengevoegde cellen meegenomen worden in de opsomming. Dus dat de opsomming in kolom F, tot aan rij 6 de waardes uit cel B2 gebruikt en vanaf rij 7 overstapt naar cel B7.


Hele idee hierachter is dat ik het handmatig ingevulde deel moet kunnen versturen als mooi opgemaakte tabel, terwijl de opsomming uit kolom F geïmporteerd moet kunnen worden naar andere software.


edit:
Wellicht moet ik mijn vraag anders stellen:

Als ik cellen A1 t/m A4 samenvoeg, hoe kan ik er dan voor zorgen dat elke cel in het samengevoegde deel, de waarde heeft van cel A1?
(bij een standaard "merge" actie zullen cellen A2 t/m A4 de waarde 0 hebben)

[ Voor 13% gewijzigd door Bloedvat op 11-07-2019 15:01 ]

[Hero of Time]

Het eerste wat we hier verwachten is dat je zelf wat probeert voordat je een vraag stelt. Dus, heb je het überhaupt al geprobeerd? Waar loop je dan tegenaan? Wat lukt niet?

[Bloedvat]

Hero of Time schreef op donderdag 11 juli 2019 @ 14:57:
Het eerste wat we hier verwachten is dat je zelf wat probeert voordat je een vraag stelt. Dus, heb je het überhaupt al geprobeerd? Waar loop je dan tegenaan? Wat lukt niet?

ehm.. ik dacht dat ik duidelijk laat zien wat ik geprobeerd heb/wat er niet lukt
Is de afbeelding niet zichtbaar die ik heb toegevoegd?

Ik heb gezocht op internet, maar ik zou niet weten wat ik nog kan proberen

[ Voor 8% gewijzigd door Bloedvat op 11-07-2019 15:02 ]

[Hero of Time]

De afbeelding is prima te zien, maar formules niet. Dus, wat heb je waar ingevuld en wat gebeurt er?

[Bloedvat]

De formule die ik heb ingevuld in cel F2 is:

=B2&" "&C2&" "&D2"

Daarna heb ik deze naar onder gekopieerd "getrokken"


Op het plaatje dat ik heb ingevoegd zie je dat de samengevoegde cellen B2 t/m B6 en B7 t/m B11 alleen worden meegenomen in cel F2 en F7

In cellen F3 t/m F6 zou ik ook de waarde uit cel B2 willen zien
In cellen F8 t/m F11 zou ik ook de waarde uit cel B7 willen zien


In andere woorden/ander voorbeeld:
Als ik cellen A1 t/m A4 samenvoeg, hoe kan ik er dan voor zorgen dat elke cel in het samengevoegde deel, de waarde heeft van cel A1?
(bij een standaard "merge" actie worden cellen A2 t/m A4 in een formule gezien als waarde 0)

[azteke]

Gebruik =B$2&" "&C2&" "&D2" ipv wat je nu hebt staan. Dus B$2 ipv B2. Dit zorgt ervoor dat bij kopieren / plakken de rij hetzelfde blijft als uit het origineel.

edit.
Je moet voor de tweede samengevoegde (zzzzzz) reeks wel weer de formule opnieuw neerzetten. Er zijn wel meer fancy manieren maar dat lijkt me niet handig via een forum

[ Voor 35% gewijzigd door azteke op 11-07-2019 15:20 ]

[Icephase]

Aha dit herken ik wel inderdaad. De oplossing van @azteke levert overal XXXXXXX op als begin, terwijl je dit na 6 of 7 rijen wilt laten ‘verspringen’.

Ik zou een hulpkolom maken met de formule =ALS(B2=“”;B1;B2). Dit gaat alles vullen met de waarde van de rij erboven TENZIJ er een waarde in B2 staat.

Vervolgens kun je die kolom in je oorspronkelijke formule verwerken ipv kolom B.

[azteke]

Icephase schreef op donderdag 11 juli 2019 @ 15:20:
Ik zou een hulpkolom maken met de formule =ALS(B2=“”;B1;B2). Dit gaat alles vullen met de waarde van de rij erboven TENZIJ er een waarde in B2 staat.

Inderdaad. Maar dit leek mij wat te moeilijk voor TS

[Bloedvat]

azteke schreef op donderdag 11 juli 2019 @ 15:23:
[...]

Inderdaad. Maar dit leek mij wat te moeilijk voor TS

He! I heard that! (wat een vertrouwen zeg )
En jouw oplossing werkt niet zoals Icephase terecht aangeeft!

Ik ben Icephase's oplossing aan het proberen en kom erop terug

[azteke]

Bloedvat schreef op donderdag 11 juli 2019 @ 15:26:
[...]

He! I heard that! (wat een vertrouwen zeg )
En jouw oplossing werkt niet zoals Icephase terecht aangeeft!

Ik ben Icephase's oplossing aan het proberen en kom erop terug

Wat werkt er niet dan? Alleen het doorkopieren naar ZZZZ werkt niet, dat klopt

Edit.
Oh, Icephase's oplossing werkt ook niet. Zodra je naar rij 3 gaat wordt rij 2 gebruikt. Die is dan ook leeg.

Om het op te lossen moet je of accepteren dat je per reeks (xxxxxxx, zzzzzz) de formule opnieuw moet neerzetten, of iets veel fanciers neerzetten

[ Voor 21% gewijzigd door azteke op 11-07-2019 15:45 ]

[Bloedvat]

azteke schreef op donderdag 11 juli 2019 @ 15:40:
[...]


[Afbeelding: excel]

Wat werkt er niet dan? Alleen het doorkopieren naar ZZZZ werkt niet, dat klopt

hehe inderdaad
Maar het is juist dat doorkopieren dat ik graag wil verwerken (zodat er niet meer naar omgekeken hoeft te worden)

Geef het niet graag toe, maar het lukt me alleen inderdaad nog niet om Icephase's oplossing fatsoenlijk te verwerken
Maar ik geef nog niet op

[ Voor 4% gewijzigd door Bloedvat op 11-07-2019 15:48 ]

[azteke]

Als je een tussenkolom maakt werkt het wel zoals je wilt.

De tussenkolom corrigeert het gedrag voor de lege XXXXXX. Dan werkt je oude formule voor de laatste kolom wel zoals jij dat wilt.

De tussenkolom kan je natuurlijk verbergen.

Formules staan op de onderste regel (in het Engels).

[ Voor 7% gewijzigd door azteke op 11-07-2019 15:53 ]

[Bloedvat]

azteke schreef op donderdag 11 juli 2019 @ 15:53:
Als je een tussenkolom maakt werkt het wel zoals je wilt.

De tussenkolom corrigeert het gedrag voor de lege XXXXXX. Dan werkt je oude formule voor de laatste kolom wel zoals jij dat wilt.

De tussenkolom kan je natuurlijk verbergen.

Formules staan op de onderste regel (in het Engels).

[Afbeelding: excel2]

Ik heb het kunnen reproduceren en het werkt! Sterker nog, het is me na wat puzzelwerk ook duidelijk waarom het werkt! Bedankt!

[azteke]

Top! graag gedaan

En erg goed van je dat je ook probeert te begrijpen hoe het werkt

[Lustucru]

Bloedvat schreef op donderdag 11 juli 2019 @ 16:10:
[...]

Ik heb het kunnen reproduceren en het werkt! Sterker nog, het is me na wat puzzelwerk ook duidelijk waarom het werkt! Bedankt!

Dan kun je de volgende oplossing zonder hulpkolom ook wel begrijpen (ik heb een hekel aan hulpkolommen, komen de leesbaarheid formules meestal niet ten goede). De oplossing maakt gebruik van een eigenaardigheid van zoeken(): als de gezochte waarde groter is dan elke waarde in het bereik dan geeft de functie gewoon de laatste, niet lege waarde terug. En dat is precies wat je hier zoekt.

code:

=ZOEKEN(TEKEN(255);$B$2:B2) & C2 & D2

[Bloedvat]

Lustucru schreef op donderdag 11 juli 2019 @ 19:44:
[...]


Dan kun je de volgende oplossing zonder hulpkolom ook wel begrijpen (ik heb een hekel aan hulpkolommen, komen de leesbaarheid formules meestal niet ten goede). De oplossing maakt gebruik van een eigenaardigheid van zoeken(): als de gezochte waarde groter is dan elke waarde in het bereik dan geeft de functie gewoon de laatste, niet lege waarde terug. En dat is precies wat je hier zoekt.

code:

=ZOEKEN(TEKEN(255);$B$2:B2) & C2 & D2

Oke nice
Ga ik morgen gelijk uitproberen

[dix-neuf]

Zonder hulpkolom, typ in F2 en kopieer naar beneden t/m F11:

code:

=ALS(RIJ()<7;B$2;B$7)&" "&C2&" "&D2

Bovenstaande is een oplossing die alleen geldt t/m rij 11.
Heb je onder rij 11 nog meer gegevens staan met samengevoegde cellen in kolom B, typ dan in F2 en kopieer naar beneden tot zover als nodig:

code:

=INDIRECT("B"&INTEGER((RIJ()-2)/5+2)*5-8)&" "&C2&" "&D2

[Paultje3181]

Als je Rij B altijd even lang is (dus bijv 5):

Visual Basic:

="B" & int(rij(B2)/5)+2

Int rondt af naar het laagste hele getal, vervolgens tel je 2 bij op om op B2 te beginnen. Pas de 5 aan als je samenvoeging groter of kleiner is.

[Lustucru]

Paultje3181 schreef op maandag 15 juli 2019 @ 07:41:
Als je Rij B altijd even lang is (dus bijv 5):

Visual Basic:

="B" & int(rij(B2)/5)+2

Int rondt af naar het laagste hele getal, vervolgens tel je 2 bij op om op B2 te beginnen. Pas de 5 aan als je samenvoeging groter of kleiner is.

Je post een foute variant van een oplossing die direct boven je staat.

Voor future reference zijn er dus drie oplossingen voor het probleem van de TS:

a) een hulpkolom die de gezochte waarden als het ware doorkopieert naar de volgende rijen;

b) als de hoogte van de samengevoegde cellen iedere keer hetzelfde is de juiste verwijzing berekenen met een variant op '= startrij + Hoogte * INTEGER((rij()-startrij)/hoogte)'

c) de universele oplossing door iedere keer in het bereik tot en met de rij te zoeken naar de laatste waarden dmv
=Zoeken(maximalewaarde;kolom)

en voor de volledigheid is er ook nog een matrixoplossing.