Selectie en subselectie in tabel

[gonzo31]

Hallo,

Ik heb in Excel een tabel met daarin kolom 'waarde' achter een kolom met 'dag', 'maand' en 'jaar'.
Nu wil ik van de eerste 6 regels per maand het aantal keren weten dat een bepaalde waarde voorkomt én van de rest van de regels zodat ik dit later kan vermenigvuldigen met andere bedragen.

De data kunnen bestaan uit verschillende data, maar ook kan dezelfde dag meermaals voorkomen in de tabel met een andere waarde of zelfs dezelfde waarde in de 'waarde' kolom.

Om de juiste maand te bepalen en het juiste jaar, moet ik deze even handmatig invullen in 2 cellen. Dat is geen probleem.

Hoe kan ik dit het beste aanpakken. Ik zat zelf al te puzzelen met

=AANTALLEN.ALS(tbl_invoer[waarde];1;tbl_invoer[maand];$N$3;tbl_invoer[jaar];$N$4)

Zie ook voorbeeldbestand.
Alleen dan heb ik nog niet de scheiding tussen de eerste 6 gebeurtenissen per maand en de rest...

Hopelijk is er iemand met een bredere Excel geest dan ik
Groet en alvast bedankt,
Arjan

Voorbeeldbestand maakt hopelijk een stuk meer duidelijk!Bekijk bijlage Voorbeeld.xlsx

 

[VenA]

Even zonder rekening te houden met het jaar. Komt dit een beetje in de buurt?

 

[gonzo31]

Hoi V&A,

Ja en nee is het antwoord. Het tellen van het aantal keer dat waarde 1 of 2 of 3 voorkomt in een opgegeven maand, gaat wel goed maar zo ver was ik zelf helaas ook zei het op een andere manier. Het gaat er om dat in bijv. de maand januari de eerste 6 waarden van die maand (tm rij 7) apart geteld worden. Dus t/m de 6de regel in die maand komt waarde 1 4x voor, waarde 2 komt 0x voor en waarde 3 komt 2x voor.

Voor februari geldt dat het tellen voor de eerste 6 regels begint op rij 14 en eindigt op rij 19 (al is deze leeg, maar normaal gesproken komen er meer gegevens achter aan)

Bedankt voor het meedenken. Hopelijk kun je er wat mee, ik blijf ook door puzzelen!
Arjan

 

[WHER]

test deze eens:

 

[gonzo31]

Wow WHER,

Dit werkt perfect! Super bedankt, zo was ik er echt niet uitgekomen, met deze oplossing!

Als iemand mij overigens kan uitleggen waarom de functie van WHER zijn oplossing in het vakje 'namen beheren' moet staan en niet gewoon in de cell ingevuld kan worden, dan leer ik er ook nog wat van.

Arjan

 

[gonzo31]

Bij nader inzien gaat het toch nog ergens fout.
Ik heb in een bepaalde maand precies 6 dagen staan maar de formule telt nu tot het einde van de tabel. Regel 119 tm 124 zou het doel moeten zijn, maar er word doorgezocht tm 153, terwijl dit een andere maand is... Voor de sectors na "eerste6" zoals in de file van WHER komen er nu negatieve getallen te staan. Dit is relatief snel te verhelpen met een als-functie, maar natuurlijk niet de bedoeling.

Het lijkt dus nog fout te gaan als er 6 of minder waarden per maand voorkomen... is hier een als-functie aan te raden om dat snel te filteren? Of moet de hele boel anders?
Gaarne jullie inzicht!

Arjan

Edit: in de originele file ligt de scheidingslijn niet op 6 maar op 35. Echter heb ik dit wel correct aangepast. Het gaat dus feitelijk als er minder regels zijn dan de eerste 35 (6 in het voorbeeld)

 

[VenA]

Ik heb een nieuwe naam toegevoegd die nu ook rekening houdt met maanden die minder dan 6 keer voorkomen.

 

[gonzo31]

Samen komen we er uit! Nu werkt het inderdaad naar behoren. Super bedankt!

Nu weet ik wel hoe de functie verschuiven werkt en zie ook wat er gebeurd dus tot zo ver ben ik er uit. Waar ik echter nog nieuwsgierig naar ben is waarom de functie 'Eerste6' of 'Eerstex' aparte gedefinieerd worden en dan weer later opgeroepen. Komt dat omdat de formule Aantal.als in de cel niet correct werkt? Of word de formule simpelweg te lang?

Groet,
Arjan

 

[VenA]

Er zijn meerdere wegen naar Rome. Vanaf XL-2007 kan je ook dmv ADRES() een bereik bepalen.

De formule zal dan zoiets worden:

=ALS(AANTAL.ALS($B$1:$B$18;$N$3)<6;AANTAL.ALS(INDIRECT(ADRES(VERGELIJKEN($N$3;$B$1:$B$18;0);4)&":"&ADRES(VERGELIJKEN($N$3;$B$1:$B$18;0)+AANTAL.ALS($B$1:$B$18;$N$3)-1;4));$H16);AANTAL.ALS(INDIRECT(ADRES(VERGELIJKEN($N$3;$B$1:$B$18;0);4)&":"&ADRES(VERGELIJKEN($N$3;$B$1:$B$18;0)+5;4));$H16))

Het lukt mij echter niet om in deze formule de kracht van tabelfuncties aan te roepen wat via gedefinieerde namen wel lukt. Dit zal wel aan mij liggen. Maar nu worden alleen de maanden vergeleken en niet naar het jaar gekeken.

 

[gonzo31]

Ok, bedankt.

Ik ben iig geholpen!

Arjan

 

[zapatr]

Het kan wel wat simpeler, zie bijlage.
Kies in N3 een maand en kijk naar het resultaat.

 

[gonzo31]

Dit lukt inderdaad ook, al lopen de tellers van de waarde '2' en '3' bij '>6' (I11:I12) niet door als ik meer regels toevoeg aan de tabel. Ofwel, het zoekbereik wordt niet uitgebreid met de nieuwe regels. Dit gebeurd wel bij waarde '1' (I10). Best raar eigenlijk.

Bedankt voor het laten zien van een andere methode!

Arjan

 

[zapatr]

Wat bedoel je met de opmerking dat de tellers bij 2 en 3 niet doorlopen, ik begrijp je niet.
Een oplossing waarbij ook rekenin g wordt gehouden met het jaar, is ook gemakkelijk te maken.
Nadat je antwoortd hebt gegeven op mijn vraag, zal ik je dat tonen.

 

[zapatr]

Mijn laatse bijlage was niet juist en die heb ik daarom verwijderd.
Ik zal kijken of ik nog wat kan aanpassen.

 

[VenA]

@zapatr
Doe eens niet zo chagrijnig De uitkomsten kloppen niet. En verder is het ook wel prettig dat je even vermeldt, dat je gebruik gemaakt hebt van matrix formules.

Dat je mijn formule hebt kunnen inkorten vind ik natuurlijk wel weer prettig. (Af en toe zit je (eigenlijk ik) in een verkeerd denkpatroon)

Nb. Goede vakantie gehad? (Aanname).

 

[gonzo31]

zapatr,

Als ik jou formule toepas en ik voeg rijen toe aan mijn invoertabel, dan loopt het bereik van de formule niet mee. Dus het bereik waar de formule naar verwijst in cel I11 en I12 word niet groter als ik regels toevoeg in mijn tabel. Bij cel I10 gaat het wel goed...

 

[zapatr]

VenA,
De uitkomsten in mijn bestand bij bericht #11 kloppen wél, dat kan iedereen nagaan die het bestand bekijkt. In dat bestand staat duidelijk onder de formules (die geen inkorting zijn van jouw formules maar ándere zijn) vermeld (nl. in rood), dat de formules moeten worden ingevoerd via Ctrl-Shift-Enter. Dat was ook het geval in het bestand dat ik later plaatste, maar dat amper een minuut op het forum heeft gestaan. Dat ik daarin een fout had gemaakt zag ik iets te laat in, maar ik heb dit wel onmiddellijk gemeld in een nieuw bericht.
Dat ik in de oplossing bij bericht #11 het bereik t/m rij 18 in acht heb genomen is logisch, omdat de gegevens in het voorbeeldbestand van de vragensteller ook tot die rij lopen. Dat dat bereik eenvoudig is uit te breiden door dat getal in de formules te wijzigen, mag bekend worden verondersteld. Van vragenstellers mag naar mijn mening worden verwacht dat ze de aangeboden oplossingen grondig bestuderen. En verder zijn het m.i. niet alleen vragenbeantwoorders die verplichtingen hebben, maar ook vragenstellers.
Voor zover het je interesseert VenA, ik heb geen vakantie gehad.
Omdat inmiddels noch jij, noch iemand anders met een oplossing is gekomen waarin ook rekening wordt gehouden met het jaar, hierbij een nieuwe uitwerking van het probleem. Daarin is zowel rekening gehouden met de maand, de eerste 6 gebeurtenissen daarin versus de overige gebeurtenissen, en met het jaar. Het bereik loopt t/m rij 100, eenvoudig uit te breiden door dat getal in de formules te wijzigen. Succes ermee gonzo31.

 

[gonzo31]

Zonder nu weer te gaan wijzen, waardeer ik een ieder zijn oplossing. Ik bekijk ze allemaal aandachtig en pak diegene er uit die naar mijn idee het beste past bij mijn bestand. Ook ben ik iedereen dankbaar voor het überhaupt kijken naar een oplossing want dit is natuurlijk geheel vrijwillig. Ik probeer ook positieve feedback te geven maar dit komt wellicht soms anders over. Dat kan ook komen omdat het stomme tekst is en de interpretatie van tekst per persoon anders kan zijn.

Dus V&A, WHER en zapatr, allen bedankt en het werkt super nu!

Arjan