[Excel] Query op array zonder filers of macros

[Zerveza]

ben al een tijdje aan het stoeien met een vraagstuk in Excel waar ik niet uit kom.

Ik wil een query uitvoeren op een tabel in Excel. Stel ik heb onderstaande tabel. Criteria die ik meegeef zijn
product = fax
salesman = brown

Product	Salesman	Units Sold
Fax	Brown	1
Phone	Smith	10
Fax	Jones	20
Fax	Smith	30
Phone	Jones	40
PC	Smith	50
Fax	Brown	60
Phone	Davis	70
PC	Jones	80

wat ik graag terug wil krijgen in een array is:

Units Sold
1
60

Ik wil dit graag doen zonder filters en zonder macro's. Dus alleen met (array) functie(s). Mijn gevoel zegt me dat het kan, maar mijn vingers krijgen het niet ingetikt... of ik krijg de juiste formules niet gevonden. Iemand die zich er aan waagt?

[ Voor 28% gewijzigd door Zerveza op 16-01-2009 17:02 ]

[Verwijderd]

Ik vraag me eerlijk gezegd af of je dit wel met Excel wilt oplossen. Excel is naar mijn ervaring vooral geschikt als data-analyse- en -presentatie-applicatie; de mogelijkheden voor data-opslag en -querying zijn beperkt en bovendien is Excel er niet voor geoptimaliseerd.
Het kan overigens wel, maar ik zie - naast bovenstaande - nog drie hobbels:

1) de formule zal vrij complex zijn: data ophalen in Excel adhv twee of meer criteria vereist vaak al een combinatie van meerdere functies;
2) je zult in je formule rekening moeten houden met de relatieve positie van de cel die een waarde teruggeeft (dwz: dat resultaat is afhankelijk van het resultaat dat de cel erboven heeft teruggegeven);
3) je zult de formule waarschijnlijk als array moeten ingeven in meerdere cellen tegelijkertijd. Dat gaat problemen opleveren als je zoekbereik een veranderlijke afmeting heeft (=je zult de arrayformule dan weer moeten aanpassen).

In Excel is dit probleem met VBA relatief eenvoudig op te lossen: for each-loop adhv de opgegeven criteria; relevante matches in een array opslaan en dat array naar Excel overzetten.
En anders zou je eens aan Access moeten denken: een query voor je probleem is heel simpel opgezet...

[Verwijderd]

zoals je het probleem gesteld hebt zou het eigenlijk niet uitmaken of je 61 als enkelvoudig resultaat krijgt, of een dataset met als som 61.
als je werkelijk een dataset als antwoord nodig hebt ipv een enkele som, dan lijkt me een draaitabel de aangewezen methode. van zodra deze datasets wat groter worden valt het inderdaad te overwegen over te stappen op een meer geschikt hulpmiddel.
met onderstaande matrixformule die je inbrengt in de cel van een selectie van 1 kolom en evenveel rijen als het bronbereik, en dan te bevestigen met ctrl+shift+enter, bekom je het volgende resultaat :

Units Sold
1
0
0
0
0
0
60
0
0

code:

=($A$2:$A$10="Fax")*(B2:B10="Brown")*$C$2:$C$10

als je het echt ver wil drjiven, kan je uitsluitend met behulp van met formules index, vergelijken enz. de resultaatlijst wat verder beperken, maar dat wordt al gauw erg complex.

[Zerveza]

Zoals ik al zei, het zal niet gemakkelijk zijn.

Even nog voor de duidelijkheid, ik wil dit oplossen zonder filters, zonder pivots en zonder VBA in Excel slechts met (array) functies.


ik zit aan zoiets te denken:
= IFERROR(INDEX(units sold;eerste rij die aan criteria voldoet);"")

of zoiets..

[Verwijderd]

Succes!

[Zerveza]

Verwijderd schreef op maandag 19 januari 2009 @ 09:33:
Succes!

thnx,

Probleem zit hem in de "eerste rij die voldoet".. die kan ik er wel uit halen, maar de tweede rij... en de 3de, en de 4de...
wellicht iets met een rank...

woei, al tikkend een eureka momentje

[Verwijderd]

Volgens mij zijn er al twee alternatieven geboden op je vraag (waaronder een door mij)...en toch blijf je liever zelf zoeken?

[Zerveza]

Verwijderd schreef op maandag 19 januari 2009 @ 17:20:
Volgens mij zijn er al twee alternatieven geboden op je vraag (waaronder een door mij)...en toch blijf je liever zelf zoeken?

Thanks voor de feedback, zoals gezegd waren aan mijn oplos mogelijkheden wat beperkingen. (geen pivots, geen VBS, geen filters).

Heel verhaal om hier uit te tikken hoe ik het uiteindelijk gedaan heb.... mocht er behoefte aan zijn doe ik het uitgebreider..

1. variabel formaat van array heb ik opgelost door de array in een named range te stoppen, die named range bevat een offset met een counta.
2. ik heb gebruik gemaakt van het feit dat TRUE=1 en FALSE=0
-> =(A2="criteria 1")*(B2="criteria 2")*C2
Hierdoor heb ik een kolom met of de waarde die ik wil hebben, of een nul.
3. Vervolgens een kolom die begint met een 1, als op de volgende rij een nul staat weer een 1, als er een cijfer staat hoog ik de 1 op met een +1, daarnaast een kolom die alleen de eerste keer dat een cijfer voorkomt weergeeft en de rest blanks. dus eerste keer dat aan alle criteria voldaan wordt een 1, tweede keer een 2 etc..
4. Vervolgens de output gemaakt door een kolom te maken met 1,2,3,4, etc, daarnaast een index functie met een match
=IFERROR(INDEX($G$3:$G$11;MATCH(E16;$L$3:$L$11;0));"")

keurig netjes een query in excel.... ik sta te kijken dat ik geen array functies nodig had..