Som waardes als.interval

[1vogeltje1]

Beste Forumgebruikers,

Ik zit momenteel in een Excel bestand te knoeien met een formule (som.als.interval.). Deze functie werkt als volgt:

In kolom A zijn allemaal datums weergegeven. Deze datums kunnen meerdere keren voorkomen en staan op volgorde. Achter iedere datum staat in kolom B een bepaalde waarde. Deze waarde kan een getal, cijfer of een combinatie van beide zijn. De bedoeling is om per datum te kijken hoe vaak verschillende waardes voorkomen. Dit lukt mij met de formule =SOM(ALS(INTERVAL(VERGELIJKEN(B2:B9;B2:B9;0);VERGELIJKEN(B2:B9;B2:B9;0))>0;1)).

Omdat de datums kunnen veranderen, wil ik het gekozen bereik (B2:B9) koppelen aan de datum en dat is me tot nu toe niet gelukt. Het klinkt allemaal vast heel vaag, dus heb ik een voorbeeldje toegevoegd.

Bekijk bijlage VoorbeeldExcel - Als.index.xlsx

Ik hoop dat jullie mij hierbij kunnen helpen en mocht het een en ander onduidelijk zijn, dan hoor ik het graag.
Alvast bedankt!

 

[zapatr]

Zie bijlage.
Een dergelijke vraag is hier al vaak aan de orde geweest.

 

[1vogeltje1]

Ten eerste wil ik je bedanken voor het meedenken zapatr!

Ten tweede spijt het me mocht de vraag vaker gesteld zijn. Ik probeer eerst zelf de oplossing te vinden, alleen hier kwam ik niet uit.

Over je oplossing, ik heb een fout geplaatst in het excel bestand. Er staat in de toelichting het aantal dezelfde waardes, maar dit moet het aantal verschillende waardes zijn. Zo komt uit jouw oplossing het antwoord 15, terwijl het gewenste antwoord 10 moet zijn.

Het aantal waardes is behoorlijk groot en kan vaak veranderen. De waardes die nu in Rij E9 t/m M9 staan zouden dan volgens mij gefilterd moeten worden uit de waardes die in kolom B staan (evenals de datums in kolom A die wel op volgorde staan, maar niet achter elkaar. De datum kan 'springen' van 20-09 naar 28-09).

 

[zapatr]

Dat is een heel andere vraag dan in je eerste bericht. Een andere vraag vereist een andere oplossing. En als je zelf aan een oplossing werkt, pas die dan toe op hetzelfde bereik. Bij elke datum in jouw bestand staat een ander bereik.
In de bijlage een oplossing voor je laatste vraag. Ik heb die wel getest op vele, maar niet op alle mogelijke combinaties. daarvoor ontbreekt me nu de tijd.

 

[1vogeltje1]

Top, bedankt zapatr!

Het enige waar ik nu nog mee struikel is het vinden van de datums die in jouw voorbeeld een vast gegeven zijn. Als de eerste datum bekend is, dan wil ik dat de volgende datum gezocht wordt in het bereik A2:A17. Is dit mogelijk (zonder VBA) wanneer er 'gaten' tussen de datums zitten en sommige datums vaker voorkomen, de datums zijn wel oplopend?

 

[zapatr]

Typ in D10:

=MIN($A$2:$A$100)

Typ in D11 (afsluiten via Ctrl-Shift-Enter):

=ALS(ZOEKEN(9E+307;$A$2:$A$100)>D10;ZOEKEN(D10+1;$A$2:$A$100);"")

D11 met de vulgreep naar beneden kopiëren.
Als je met Excel 2010 zonder Servicepack 1 werkt, moet je ZOEKEN vervangen door OPZOEKEN.

 

[1vogeltje1]

Had dit weekend geen tijd en heb net je oplossing geprobeerd. Perfecte oplossing, bedankt voor je hulp zapatr!

Probleem opgelost :thumb:

 

[1vogeltje1]

Ik heb nog 1 klein probleempje waardoor de oplossing niet helemaal werkt. Wanneer ik de formule in de cellen D10 en D11 invul dan werken de formules perfect.
Echter, als ik de formules kopieer naar mijn bestand dan stuit ik op een probleem. Omdat er 'gaten' tussen de datums zitten, berekent de formule niet de volgende datum.
Als ik in het voorbeeldbestand de datums in A14 en A15 weghaal, dan worden alleen de datums tot en met 17-09 weergegeven.

 

[zapatr]

Eerder schreef je dat de datums oplopend gesorteerd staan, maar als er - zoals jij schrijft - 'gaten' tussen de datums zitten, dan is dat niet meer het geval (er staan dan immers nullen tussen). Logisch dus dat dan een eerder gegeven formule niet meer werkt.
Zo klein als jij denkt is dat probleem niet hoor (anders had je het zelf wel opgelost neem ik aan).
Maar je kunt het oplossen door onderstaande in D11 te typen en die formule naar beneden te kopiëren.
Voor andere bereiken de formule overeenkomstig aanpassen.
Vanzelfsprekend moet, als in kolom B een waarde is ingevuld, daar altijd een datum in kolom A bij staan.

=ALS(MIN(ALS(A$2:A$100>D10;A$2:A$100))>D10;MIN(ALS(A$2:A$100>D10;A$2:A$100));"")

Invoeren via Ctrl-Shift-Enter.

 

[1vogeltje1]

Nu ik mijn vraag zo teruglees denk ik dat mijn uitleg nogal onduidelijk was. Zal in het vervolg mijn vraag duidelijker proberen te stellen, zodat ik jullie kostbare tijd kan besparen.

Je formule werkt uitstekend, ontzettend bedankt!

 

[1vogeltje1]

Zapatr, in het begin werkte je formule perfect. Alleen na het invullen van een hoop gegevens, merkte ik dat het aantal verschillende waardes niet meer klopte. Ik heb de data van mijn bestand in het voorbeeldbestand gezet. In Cel E12 hoort als uitkomst 9 te komen, terwijl er 6 staat. Ik dacht dat ik het bereik dynamisch kon maken, maar dat was me niet gelukt. Als ik het bereik van de formule in cel E10 aanpas aan A2:A100, B2:B100 etc., dan komt de gewenste waarde van 8. Pas ik het bereik aan het totaal aantal waardes (A2:A350), dan is de uitkomst 7. Na een hoop gepuzzel, frustratie en nog steeds geen oplossing, probeer ik het via dit forum.

Bekijk bijlage Voorbeeld.som_als(2).xlsx

Alvast bedankt!

 

[zapatr]

Zoals ik al eerder schreef, had ik de formule niet voor alle waarden getest; ze blijkt slechts te werken t/m rij 48, een vreemd iets dat ik nog eens nader moet bestuderen. Het was even puzzelen, maar onderstaande zal, denk ik, over het gehele bereik goed werken.Typ in E10 (en sluit af met Ctrl-Shift-Enter):

=SOM(ALS(INTERVAL(ALS(A$2:A$350=D10;ALS(B$2:B$350<>"";VERGELIJKEN(B$2:B$350;B$2:B$350;0)));RIJ(B$2:B$350)-MIN(RIJ(B$2:B$350))+1)>0;1))

en kopieer naar beneden.

 

[1vogeltje1]

Ontzettend bedankt Zapatr! Dit is de formule die voor alle gegevens werkt! :thumb:
Mocht je ontdekken waarom de vorige formule tot rij 48 werkt en niet voor het gehele bereik , dan ben ik erg nieuwsgierig naar het antwoord.