hulp gevraagd bij tellen

[mariannevanh]

In de bijlage vinden jullie de eerste kolommen van een stamboomoverzichtje.
De kolommen A tot en met D zijn de verschillende generaties. Kolom E is leeg indien niet getrouwd, samenwonend, enz., heeft een "b" indien hij/zij aangetrouwd is, heeft een "a" indien hij/zij een kind is van ouders die daarboven in het overzicht voorkomen (zelfde beginnummers; mijn neven en nichten, broers en zussen en ikzelf).
De kolommen B, C en D bevatten soms "0" (en de tekstkleur is wit) om het sorteren makkelijker te maken.
In kolom A staan de kinderen van mijn grootouders; dat is redelijk eenvoudig af te lezen: als de kolommen B, C en D "0" zijn en kolom E een "a" bevat.
In kolom B staan de kleinkinderen, maar daar wordt het al een stuk moeilijker om het totaal aantal af te lezen. Het zijn er 61 (alle kleinkinderen zijn geboren tijdens het leven van oma). Maar hoe vat ik dit nu samen in een formule?
Voor de kolommen C en D wordt het nog veel moeilijker.

Wie kan mij helpen hoe ik de juiste tellingen kan uitvoeren?

Uiteraard heb ik uit privacy-overwegingen geen namen weergegeven.

Bekijk bijlage stamboom-excel.xlsx

Vriendelijke groet,
Guido

 

[WHER]

In bijlage een poging met hulpkolommen en matrixformules, voor zover ik je bestand correct interpreteer natuurlijk.
De formules voor kolom D mag je zelf afleiden uit die van kolom B en C.

 

[Gijsbert1]

Ik doe ook een poging: Bekijk bijlage 277267

 

[mariannevanh]

WHER, hartelijk dank!!!
Je oplossing is redelijk duidelijk. Toch krijg ik nog een foutmelding bij enkele cellen. Ik begrijp zelf nog weinig van de accolades {} om de formules...
Zo krijg ik in J10 een #N/B te zien. Ik heb daar de formule:

=ALS(D10=0;"";ALS(VERGELIJKEN($A10&$B10&C10&1;$A$1:$A10&$B$1:$B10&$C$1:$C10;0)=RIJ();INDEX(D10:D$387;VERGELIJKEN(0;D10:D$387;0)-1);""))

Kun je mij misschien uitleggen wat ik hier fout doe?

Vriendelijke groet,
Guido

 

[mariannevanh]

Ook Gijsbert1 hartelijk dank!
Helaas moet ik hier toch een fout constateren (alhoewel ik de fout niet kan herleiden in de formules). Kind 10 in kolom A is niet getrouwd en heeft zelf geen kinderen, dus het aantal personen dat je in I3 noemt, moet 9 zijn. Aangezien het aantal mensen wel op 386 uitkomt, moet er dus nog ergens een fout zitten. Uit persoonlijke natelling blijkt het aantal achterkleinkinderen (kolom C) inderdaad 177 te zijn. De rest heb ik nog niet gecontroleerd aan de hand van de stamboom.

Je opmerking "2x 1e generatie nr7 zonder "a"tje of "b"tje, vergeten?". Je bedoelt bij 7.5a waar je geen b ziet? 7.5 is gescheiden en heeft wel kinderen.
En het "c"tje is inderdaad een tweede echtgenoot van een (aangetrouwde) tante die weduwe was (en inmiddels weer weduwe is).

Vriendelijke groet,
Guido

 

[mariannevanh]

Overigens, dat de 0 in witte kleur had mijn voorkeur omdat ik soms de tabel weleens anders sorteerde (om er bijv. een verjaardagskalender van te maken) en dan weer eenvoudig terug wilde naar de oude volgorde - sorteren, op A, B, C, D, E.

Vriendelijke groet,
Guido

 

[WHER]

@guidovanh,

de formule in J10 zou er ongeveer zo moeten uit zien (in mijn engelstalige versie):

=IF(D10=0;"";IF(MATCH($A10&$B10&C10&1;$A$1:$A10&$B$1:$B10&$C$1:$C10[COLOR="#FF0000"]&$D$1:$D10[/COLOR];0)=ROW();INDEX(D10:D$387;MATCH(0;D10:D$387;0)-1);""))

edit: bovendien lijkt er in cel D155 een "0" te ontbreken?

 

[mariannevanh]

@guidovanh,

de formule in J10 zou er ongeveer zo moeten uit zien (in mijn engelstalige versie):

=IF(D10=0;"";IF(MATCH($A10&$B10&C10&1;$A$1:$A10&$B$1:$B10&$C$1:$C10[COLOR="#FF0000"]&$D$1:$D10[/COLOR];0)=ROW();INDEX(D10:D$387;MATCH(0;D10:D$387;0)-1);""))

Ook na kopiëren en vertalen van de code krijg ik helaas dezelfde foutmelding. Maar dat zal wel komen door het ontbreken van accolades.

edit: bovendien lijkt er in cel D155 een "0" te ontbreken?

Je hebt gelijk. En dat was niet de enige. Ik ging in D2 staan (en daarna ook in C2) en gaf Ctrl-pijl neer en stuitte op nog een meer lege cellen.

Ik heb de huidige versie met jouw uitwerking hier - Bekijk bijlage stamboom-excel-aangevuld.xlsx staan.

Vriendelijke groet,
Guido

 

[sylvester-ponte]

deze formule werkt ook:

=ALS(EN(B4=1;B3=0);INDEX(B4:B$387;VERGELIJKEN(0;B4:B$387;0)-1);"")

zie grijze cellen:

 

[WHER]

Maar dat zal wel komen door het ontbreken van accolades.

Je moet de matrixformules (bijvoorbeeld in J2) inbrengen met ctrl+shift+enter, niet gewoon enter
Excel zet dan zelf de accolades om de formule

 

[mariannevanh]

Je moet de matrixformules (bijvoorbeeld in J2) inbrengen met ctrl+shift+enter, niet gewoon enter
Excel zet dan zelf de accolades om de formule

Ok, ik begreep je opmerking in L2 en L3 eerst verkeerd. Het woord "inbrengen" was het magische woord.
De kolommen H, I en J zijn nu samen 298 personen. Samen met de 10 kinderen uit kolom A en de 9 echtgenoten/s kom ik dan op 317.
Welke personen mis ik dan nog? Het zijn er totaal 386...

Vriendelijke groet,
Guido

 

[sylvester-ponte]

Guido, stuur een een lijstje met fictieve namen want dan worden de formules anders dan met cijfers.
en gebruik je bij de namen ook nullen of blijft de cel dan leeg ?

hoe bedoel je 386 ?
in kolomA staan er 10
in kolomB staan er 61
in kolomC staan er 177
in kolomD staan er 40

totaal: 288

misschien kun je in de hulp kolommen met de hand aangeven wat er wel had moeten staan.

 

[mariannevanh]

Guido, stuur een een lijstje met fictieve namen want dan worden de formules anders dan met cijfers.

Zou kunnen, maar is erg bewerkelijk en ik ben niet fantasierijk in het verzinnen van zoveel namen. Ik denk dat we gemakkelijk kunnen verwijzen naar cellen, rijen en kolommen. Of naar bijv. 7.1a (ofwel de persoon met 7 in kolom A en 1 in kolom B en "a" in kolom E (haha, dat ben ikzelf) Maar ik zal wel iets bedenken.

en gebruik je bij de namen ook nullen of blijft de cel dan leeg ?

In de rij namen is elke cel gevuld (nee, niet in de cel tussenvoegsels, die zijn soms leeg (Jansen, Boersma))

hoe bedoel je 386 ?
in kolomA staan er 10
in kolomB staan er 61
in kolomC staan er 177
in kolomD staan er 40

totaal: 288

Elke regel is één naam, cel A2 t/m A387 is dus 386 personen.
In kolom E hebben de eigen familieleden een a en de aangetrouwden een b. En er zijn genoeg familieleden die nog niet, niet of niet meer getrouwd zijn.

misschien kun je in de hulp kolommen met de hand aangeven wat er wel had moeten staan.

De inzending van Gijsbert was qua telling redelijk juist, maar had toch een hiaat (zie mijn post #5).

Vriendelijke groet,
Guido

 

[Gijsbert1]

HA Guido,

Alvast excuus voor het gequote..

Opmerking 1

De inzending van Gijsbert was qua telling redelijk juist, maar had toch een hiaat (zie mijn post #5).

Kind 10 in kolom A is niet getrouwd en heeft zelf geen kinderen, dus het aantal personen dat je in I3 noemt, moet 9 zijn

En het "c"tje is inderdaad een tweede echtgenoot van een (aangetrouwde) tante die weduwe was

1 kind (kind 10) was niet getrouwd maar 1 kind (kind 5) is 2x getrouwd... Dit heft elkaar mijns inziens op, en komt mijn berekening terrecht op 10 aangetrouwde kinderen uit! mijn telling klopt dan toch?

Opmerking 2

de opmerking "2x 1e generatie nr7 zonder "a"tje of "b"tje, vergeten?". Je bedoelt bij 7.5a waar je geen b ziet? 7.5 is gescheiden en heeft wel kinderen.

Nee ik bedoel bij 7.0 in jouw originele bestand op rij 280 en 281.
In het door mij geposte bestand was ik zo vrij hier een a en b alvast in te vullen.

Opmerking 3 Aanvulling ingaand op de opmerking van sylvester-ponte , zie post #12:

Guido, stuur een een lijstje met fictieve namen want dan worden de formules anders dan met cijfers.
en gebruik je bij de namen ook nullen of blijft de cel dan leeg ?

Als je, in het door mij geposte bestand, in kolom F (F1), de formule aanpast van:

=AANTAL.ALS(A1:D1;">0")

naar

=AANTALARG(A1:D1)-AANTAL.ALS(A1:D1;0)

dan maakt het helemaal niet uit of er namen of cijfers staan, en ook niet of er "nullen" staan of helemaal geen tekst.
Dan kun je het bestand aanpassen zoals jij het wilt en blijven de formules werken.

Groet Gijs :thumb:

 

[mariannevanh]

Hoi Gijs,

Ja, het was wel veel gequote, maar het maakt het wel duidelijker...

1 kind (kind 10) was niet getrouwd maar 1 kind (kind 5) is 2x getrouwd... Dit heft elkaar mijns inziens op, en komt mijn berekening terrecht op 10 aangetrouwde kinderen uit! mijn telling klopt dan toch?

Nee, kind 5 is niet 2 keer getrouwd. De weduwe van kind 5 (die zelf al "aangetrouwd" is) is hertrouwd..
Voor het complete beeld over kolom E: a is bloedeigen familie, b is aangetrouwd met a, c (en dat komt dus slechts één keer voor) is getrouwd met b.
Ik zit nog even te bedenken hoe je dit in jouw samenvatting het mooist zou kunnen weergeven...

Opmerking 2

Nee ik bedoel bij 7.0 in jouw originele bestand op rij 280 en 281.
In het door mij geposte bestand was ik zo vrij hier een a en b alvast in te vullen.

Je hebt gelijk!

Opmerking 3
...KNIP...

Inderdaad!

Dankjewel voor je aanvullingen en correcties.

Vriendelijke groet,
Guido

 

[Gijsbert1]

Graag gedaan hoor!

Als je de C-tjes niet wilt mee laten tellen dan verander je de formule in I3 van:

=AANTALLEN.ALS($F$1:$F$386;1;$E$1:$E$386;"b")+[COLOR="#FF0000"]AANTALLEN.ALS($F$1:$F$386;1;$E$1:$E$386;[B][U]"c"[/U][/B][/COLOR])&" Aangetrouwde kinderen van grootouders"

naar:

=AANTALLEN.ALS($F$1:$F$386;1;$E$1:$E$386;"b")&" Aangetrouwde kinderen van grootouders"

Dan wordt de uitkomst wel 9... :thumb:

 

[HSV]

De 'somproduct' kan iets wat 'aantallen.als' niet kan.

=SOMPRODUCT(($F$1:$F$386=1)*($E$1:$E$386={"b"[COLOR=#ff0000];[/COLOR]"c"}))&" Aangetrouwde kinderen van grootouders"

Desnoods de rode scheidingsteken in de matrix aanpassen van ; in \

Edit: zoals onderstaande twee formules.

=AANTALLEN.ALS($F$1:$F$386;4;$E$1:$E$386;{"";"a"})&" Kinderen van kleinkinderen"

=AANTALLEN.ALS($F$1:$F$386;4;$E$1:$E$386;{"a";""})&" Kinderen van kleinkinderen"