[Excel 2002] Zoeken naar meerdere cellen

[Dirtbiter]

Ik heb een interessante uitdaging in Excel.

Ik heb een lijst met daarin verschillende gegevens. Het gaat om 3 kolommen, alle drie met daarin een getal. Ik wil de controleren of de precieze combinatie van die 3 kolommen in 1 rij voorkomt.

Voorbeeld

Rij	I	O	K
1	0		100
2		0	100
3	0		101

In het hierbovenstaande voorbeeld zijn dus geen van de rijen gelijk. De formule moet dus verschil kunnen maken tussen een lege cel en een 0.

Ik hoef niet persé te weten hoe vaak dat dezelfde waarde terugkomt, maar of het zo is. Een controle op dubbele invoer dus.

Ik neem aan dat ik gebruik moet maken van VERT.ZOEKEN, maar deze accepteerd maar 1 kolom als zoekgebied.

Ook heb ik geprobeerd om de cellenreeks om te zetten naar tekst, m.b.v. TEKST(), om zo het verschil tussen een lege cel en een 0 te maken, maar dat werkt niet.

Ook is er de mogelijkheid in Excel om met een matrixformule te werken (CTRL-SHIFT-ENTER) en dan komen er {} om je formule heen. Zo kun je met een selectie van meerdere cellen tegelijk werken.

Ik kom er alleen nog niet lekker uit, hoe ik deze gegevens moet combineren.

[Dirtbiter]

Ik heb het voor elkaar. Het is wel een draak van een formule geworden, en volgens mij moet het veel makkelijker kunnen, dus leef je nog steeds uit, maar zo werkt het ook:

code:

{=SOM(ALS(
TEKST.SAMENVOEGEN(
ALS(ISLEEG(K36);"x";TEKST(K36;"0"));
ALS(ISLEEG(L36);"x";TEKST(L36;"0"));
ALS(ISLEEG(M36);"x";TEKST(M36;"0")))
=TEKST.SAMENVOEGEN(
ALS(ISLEEG(K11:K51);"x";TEKST(K11:K51;"0"));
ALS(ISLEEG(L11:L51);"x";TEKST(L11:L51;"0"));
ALS(ISLEEG(M11:M51);"x";TEKST(M11:M51;"0")));1;0))}

Ik zal de functie uitleggen aan de hand van de line-numbers.

1+9: De {} haken geven aan dat het een matrix formule is, daardoor kan ik met K11:K51 (7)werken
1: Met SOM bereken ik het aantal keer dat het volgende in de matrix voorkomt
1: Met ALS tel ik alleen de keren dat het voorkomt.

2,3,4,5: Ik voeg de 3 kolommen samen, maar daarbij maak ik wel tekst van de kolom, omdat optellen niet werkt. 100+1 is namelijk hetzelfde als 101+0, en het gaat om de unieke combinatie.
Omdat een lege cel gezien wordt als 0, moet ik dat afvangen en daarvoor in de plaats een 'x' wegzetten, om een lege cel aan te geven.

6,7,8,9: Hier gebeurt hetzelfde als bij 2,3,4,5, maar dan met een matrix. Dit werkt door de formule op te slaan met CTRL-SHIFT-ENTER.

Door eventueel gebruik te maken van een ALS, kun je checken of er dubbelgangers zijn.

Update:
Helaas is het zo dat als ik deze formule kopiëer, dat dan de matrix ook mee verspringt. Het is natuurlijk de bedoeling dat het eerste deel van de formule verschuift naar de huidige rij, maar het 2e deel moet gelijk blijven. Iemand suggesties?

[ Voor 8% gewijzigd door Dirtbiter op 22-02-2007 17:13 ]

[Dirtbiter]

Het is me uiteindelijk gelukt, dus voor het nageslacht zal ik het hier even posten:

Het gaat erom dat je bij de zoekvelden (die dus niet moeten verspringen) tussen de letter en het getal een dollarteken ($) zet. Dan blijft dat hetzelfde. Het uiteindelijke ding:

code:

{=SOM(ALS(
TEKST.SAMENVOEGEN(
ALS(ISLEEG(K36);"x";TEKST(K36;"0"));
ALS(ISLEEG(L36);"x";TEKST(L36;"0"));
ALS(ISLEEG(M36);"x";TEKST(M36;"0")))
=TEKST.SAMENVOEGEN(
ALS(ISLEEG(K$11:K$51);"x";TEKST(K$11:K$51;"0"));
ALS(ISLEEG(L$11:L$51);"x";TEKST(L$11:L$51;"0"));
ALS(ISLEEG(M$11:M$51);"x";TEKST(M$11:M$51;"0")));1;0))}