• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Excel matrixformule ALS, MAX en SOM totaal berekening

Status
Niet open voor verdere reacties.
N

Nappie84

Gebruiker
Lid geworden
24 mrt 2012
Berichten
38
Goedenavond,

Ik heb een auditlog met het aantal inlog pogingen van gebruikers.
Gebruikers zijn gekoppeld aan een organisatie met een code en een naam. De naam van die organisatie kan wel eens veranderen terwijl de code hetzelfde blijft.
Daardoor kan het aantal inlog pogingen niet hetzelfde zijn. Soms kan het ook zijn dat een gebruiker bij het inloggen nog geen organisatie gekoppeld heeft/had.

In de bijlage heb ik een voorbeeld bestand met een formule welke het totaal aantal inlog pogingen gaat berekenen.

In kolom H heb ik de formule voor de totaal berekening.
Echter krijg ik in sommige situaties niet het gewenste resultaat. Vooral in kolom H9, H12, H13 en H14 krijg ik niet de juiste waarden na voren.
Daarom heb ik kolom I de waarde handmatig aangegeven welke ik verwacht.

Wie kan mij verder helpen?

Bekijk bijlage 274775Bekijk bijlage Audit.xlsx
 
Laatst bewerkt:
Naar welk resultaat ben je op zoek? M.a.w. kun je "juiste waarde" definiëren?
 
Voorbeeld in A2/A3
Als gebruiker test@test1 ingelogd is met dezelfde code maar de naam van de organisatie is anders dan is deze gebruiker niet 68 x ingelogd maar 34 keer.

Voorbeeld A8/A9
Als gebruik test@test5 eerst ingelogd met organisatie GM045 en daarna met V2000 dan is deze gebruiker dus 135+ 30 = 165 keer ingelogd en niet zoals mijn berekening 135 laat zien.
Hij moet dus de SOM van die waarde =165 tonen in beide regels.

Voorbeeld A12/A13/A14
Gebruikers test@test8.nl is 2 ingelogd geweest
1x zonder organisatie en 2x met dezelfde organisatie, dan is deze dus 52 keer ingelogd geweest.
45+5= 52 i.p.v. 99 keer.
 
Als je alleen voorbeelden geeft, dan blijft het nog steeds zoeken naar de logica er achter.

Maar goed: ander vraagje: is organisatie kolom C en zo ja wat is dan kolom D, of is organisatie de combinatie van C en D, of nog anders?
 
Excuses, ik heb even kolomkoppen toegevoegd.

Wat ik eigenlijk wil weten, is hoe vaak (totaal) een gebruiker ingelogd is geweest.

Als men met dezelfde code is ingelogd dan zou het de MAX van die waarde moeten zijn voor die gebruiker.
Als met met verschillende codes ingelogd is geweest dan is het de SOM van die die waarde van de gebruiker.

Als men namelijk de omschrijving van een organisatie aanpast maar de code blijft hetzelfde dan lijkt het erop dat de gebruiker (in dit geval test@test1.nl) 68 x is ingelogd terwijl het 34 moet zijn.
De audit log maar daardoor een nieuwe regel voor die gebruiker omdat de omschrijving afwijkt.

Overigens is kolom B de naam van een gebruiker. Als men hier ook de naam van de gebruiker aanpast, dan wordt ook een nieuwe audit log regel aangemaakt. Dat is het geval bij test@test8.nl maar kan dus ook voorkomen.
 

Bijlagen

Mijn interpretatie is dat je de som van alle inlogpogingen (kolom G) per inlognaam (kolom A) wilt hebben, maar de uitkomst moet gecorrigeerd worden voor dubbeltellingen, d.w.z. zelfde inlognaam en zelfde organisatiecode (kolom C).

Mijn voorstel is om een hulpkolom te hanteren voor de dubbele.

In J2 en gekopieerd naar beneden. Deze formule kijkt alleen vanaf de huidige regel, zodat elke dubbele maar 1 keer wordt meegeteld:
Code: 
=ALS(AANTALLEN.ALS($A2:$A$4998;$A2;$C2:$C$4998;$C2)>1;$G2;0)

Dan de uitkomst in K2 en gekopieerd naar beneden:
Code: 
=SOM.ALS($A$2:$A$4998;$A2;$G$2:$G$4998)-SOM.ALS($A$2:$A$4998;$A2;$J$2:$J$4998)

Ik heb overigens het originele voorbeeldbestand gebruikt.
 

Bijlagen

Ik heb nog een kleine aanvulling:

In kolom F heb ik op basis van kolom E de MAX waarde van laatste inlog datum van een gebruiker kunnen berekenen.
Nu zou ik nog graag willen weten met welke in organisatie (kolom D) men het laatst is ingelogd. Deze zou dan in kolom G getoond moeten worden.

Als een gebruiker dus met dezelfde organisatiecode is ingelogd, maar de naam van de org. is veranderd, en de MAX inlog datum is daardoor hetzelfde, dan mag de eerste waarde worden getoond.
In het rood heb ik aangeven welke waarde ik normaal zou verwachten.

Bekijk bijlage Audit MB_2.xlsx

Ik hoop dat iemand mij kan helpen.

Alvast dank!
 
Dat kan met een INDEX-VERGELIJKEN constructie.
Matrixformule in G2 en gekopieerd naar beneden:
Code: 
=INDEX($D$2:$D$15;VERGELIJKEN($A2&$F2;$A$2:$A$15&$E$2:$E$15;0))&""
Die &"" (aan het einde) is om te voorkomen dat je nullen krijgt i.p.v. lege cellen.
 

Bijlagen

Is het nog mogelijk om in de formule wanneer de MAX inlogdatum een niet gekoppelde organisatie bevat en dus leeg is (gebruiker heeft dus meerdere regels in de auditlog), dat dan de organisatie van de vorige
login wordt gebruikt?

Bekijk bijlage Audit MB_4.xlsx
 
Het eenvoudigste is om de matrixformule voor max inlogdatum aan te passen. In F2 en gekopieerd naar beneden:
Code: 
=ALS.FOUT(1/(1/MAX(ALS(A$2:A$5000=A2;ALS(C$2:C$5000<>"";E$2:E$5000))));MAX(ALS(A$2:A$5000=A2;E$2:E$5000)))

Eerst wordt gezocht naar de laatste inlogdatum van de betreffende gebruiker met ingevulde organisatie. Als die er niet is (in het voorbeeld test2 op regel 4), dan wordt gezocht naar de laatste inlogdatum van de betreffende gebruiker.

In deze formule wordt een truc toegepast: 1/(1/getal) levert altijd het getal op, tenzij getal 0 is, dan krijg je een delen-door-nul fout waarna ALS.FOUT ervoor zorgt dat dan het als-fout deel van de formule wordt uitgevoerd (dat is het deel vanaf de tweede MAX).
 

Bijlagen

Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan