Archief - Formule ms excel

Het archief is een bevroren moment uit een vorige versie van dit forum, met andere regels en andere bazen. Deze posts weerspiegelen op geen enkele manier onze huidige ideeën, waarden of wereldbeelden en zijn op sommige plaatsen gecensureerd wegens ontoelaatbaar. Veel zijn in een andere tijdsgeest gemaakt, al dan niet ironisch - zoals in het ironische subforum Off-Topic - en zouden op dit moment niet meer gepost (mogen) worden. Toch bieden we dit archief nog graag aan als informatiedatabank en naslagwerk. Lees er hier meer over of start een gesprek met anderen.
e

ellenwiu

Legacy Member
Hoi,

Ik heb al verschillende formules geprobeerd, maar ik raak er niet uit.
Er zijn 2 kolommen (A en B) en deze bevatten beide getallen van 0 tot en met 9
vb:
A B
2 3
4 5
2 7
8 3
9 4
2 3
...

Nu zoek ik een formule die telt hoeveel keer een getal voorkomt in kolom B bij een getal in kolom A.
In het voorbeeld:
Hoeveel keer komt 3 voor in kolom B bij getal 2 in kolom A?
A B
2 3
4 5
2 7
8 3
9 4
2 3
...
Antwoord is 2 keer, maar ik krijg het niet in een formule.
Misschien kan iemand mij verder helpen?
d

daVinci

Legacy Member
Een mogelijkheid is 2 kolommen bij creëren nl. 1 kolom om de waardes van kolom A en B samen in 1 kolom te zetten en 1 kolom om op basis hiervan een aantal te berekenen.

vb.

Code: 
A       B          C                         D
-----------------------------------------------------------------
2	3	=A1&"-"&B1	=AANTAL.ALS($C$1:$C$6;C1)
4	5	=A2&"-"&B2	=AANTAL.ALS($C$1:$C$6;C2)
2	7	=A3&"-"&B3	=AANTAL.ALS($C$1:$C$6;C3)
8	3	=A4&"-"&B4	=AANTAL.ALS($C$1:$C$6;C4)
9	4	=A5&"-"&B5	=AANTAL.ALS($C$1:$C$6;C5)
2	3	=A6&"-"&B6	=AANTAL.ALS($C$1:$C$6;C6)


In kolom C heb je nu een 1 waarde die een combinatie is van de waarde in A en B, met een "-"-teken tussen. Nu kan je in Kolom D het aantal malen zoeken dat de waarde van kolom C voorkomt in het totale bereik, mbv de AANTAL.ALS formule.

Als resultaat krijg je dan:

Code: 
A       B        C      D
------------------------------
2	3	2-3	2
4	5	4-5	1
2	7	2-7	1
8	3	8-3	1
9	4	9-4	1
2	3	2-3	2


Ik hoop dat dit je kan verderhelpen.

Wil je de tussenliggende kolom C niet gebruiken, dan vrees ik dat je een eigen functie moet maken in VBA. Die mogelijkheid heeft uiteraard een hogere moeilijkheidsgraad.
d

daVinci

Legacy Member
Ik heb mijn grijze hersencellen nog even aan het werk gezet. Onderstaande formule is weliswaar ingewikkelder maar behoeft geen extra kolom en zou ook moeten werken.

Code: 
A       B                      C
----------------------------------------------------------------------
2	3	=SOMPRODUCT(--($A$1:$A$6=A1);--($B$1:$B$6=B1))
4	5	=SOMPRODUCT(--($A$1:$A$6=A2);--($B$1:$B$6=B2))
2	7	=SOMPRODUCT(--($A$1:$A$6=A3);--($B$1:$B$6=B3))
8	3	=SOMPRODUCT(--($A$1:$A$6=A4);--($B$1:$B$6=B4))
1	6	=SOMPRODUCT(--($A$1:$A$6=A5);--($B$1:$B$6=B5))
2	3	=SOMPRODUCT(--($A$1:$A$6=A6);--($B$1:$B$6=B6))

je kan A1 en B1, A2 en B2; enz. ook vervangen door werkelijke waarden, zonder dat ze naar een cel verwijzen. Maar ik neem aan dat je wijzigingen aan de formule zelf aankan, anders vraag je het maar.
d

daVinci

Legacy Member
dreet'n zei:
Werkt dat ook niet met aantal.als???
Klik om te vergroten...

daar kan je dacht ik maar 1 criterium opgeven. Dus bv. alleen in bereik A1:A10 alles gelijk aan 10. Maar linken tussen 2 criteriums lukt niet dacht ik.
e

ellenwiu

Legacy Member
daVinci zei:
Een mogelijkheid is 2 kolommen bij creëren nl. 1 kolom om de waardes van kolom A en B samen in 1 kolom te zetten en 1 kolom om op basis hiervan een aantal te berekenen.

vb.

Code: 
A       B          C                         D
-----------------------------------------------------------------
2	3	=A1&"-"&B1	=AANTAL.ALS($C$1:$C$6;C1)
4	5	=A2&"-"&B2	=AANTAL.ALS($C$1:$C$6;C2)
2	7	=A3&"-"&B3	=AANTAL.ALS($C$1:$C$6;C3)
8	3	=A4&"-"&B4	=AANTAL.ALS($C$1:$C$6;C4)
9	4	=A5&"-"&B5	=AANTAL.ALS($C$1:$C$6;C5)
2	3	=A6&"-"&B6	=AANTAL.ALS($C$1:$C$6;C6)


In kolom C heb je nu een 1 waarde die een combinatie is van de waarde in A en B, met een "-"-teken tussen. Nu kan je in Kolom D het aantal malen zoeken dat de waarde van kolom C voorkomt in het totale bereik, mbv de AANTAL.ALS formule.

Als resultaat krijg je dan:

Code: 
A       B        C      D
------------------------------
2	3	2-3	2
4	5	4-5	1
2	7	2-7	1
8	3	8-3	1
9	4	9-4	1
2	3	2-3	2


Ik hoop dat dit je kan verderhelpen.

Wil je de tussenliggende kolom C niet gebruiken, dan vrees ik dat je een eigen functie moet maken in VBA. Die mogelijkheid heeft uiteraard een hogere moeilijkheidsgraad.
Klik om te vergroten...

Op deze manier lukt het inderdaad, heel erg bedankt!
Een eigen functie maken zit er niet in. :)
e

ellenwiu

Legacy Member
daVinci zei:
Ik heb mijn grijze hersencellen nog even aan het werk gezet. Onderstaande formule is weliswaar ingewikkelder maar behoeft geen extra kolom en zou ook moeten werken.

Code: 
A       B                      C
----------------------------------------------------------------------
2	3	=SOMPRODUCT(--($A$1:$A$6=A1);--($B$1:$B$6=B1))
4	5	=SOMPRODUCT(--($A$1:$A$6=A2);--($B$1:$B$6=B2))
2	7	=SOMPRODUCT(--($A$1:$A$6=A3);--($B$1:$B$6=B3))
8	3	=SOMPRODUCT(--($A$1:$A$6=A4);--($B$1:$B$6=B4))
1	6	=SOMPRODUCT(--($A$1:$A$6=A5);--($B$1:$B$6=B5))
2	3	=SOMPRODUCT(--($A$1:$A$6=A6);--($B$1:$B$6=B6))

je kan A1 en B1, A2 en B2; enz. ook vervangen door werkelijke waarden, zonder dat ze naar een cel verwijzen. Maar ik neem aan dat je wijzigingen aan de formule zelf aankan, anders vraag je het maar.
Klik om te vergroten...

Met deze functie lukt het in 1 keer, maar ik begrijp ze niet echt.
Zou je ze wat meer kunnen uitleggen?
"=SOMPRODUCT(--($A$1:$A$6=A1);--($B$1:$B$6=B1))"
Waarvoor dienen die "--"? Als ik ze weg laat, is het resultaat verkeerd.
Het archief is een bevroren moment uit een vorige versie van dit forum, met andere regels en andere bazen. Deze posts weerspiegelen op geen enkele manier onze huidige ideeën, waarden of wereldbeelden en zijn op sommige plaatsen gecensureerd wegens ontoelaatbaar. Veel zijn in een andere tijdsgeest gemaakt, al dan niet ironisch - zoals in het ironische subforum Off-Topic - en zouden op dit moment niet meer gepost (mogen) worden. Toch bieden we dit archief nog graag aan als informatiedatabank en naslagwerk. Lees er hier meer over of start een gesprek met anderen.
Terug
Bovenaan