Embedded Files
FILTER form entries tussen bepaalde datums
FILTER form entries tussen bepaalde datums
=filter(import!$A$2:$K,TO_DATE(
ALS(TO_DATE(import!$A$2:$A) >= VANDAAG()-(VANDAAG()-DATUM(JAAR(VANDAAG())-1,1,1)),
ALS(TO_DATE(import!$A$2:$A) <= VANDAAG()-(VANDAAG()-DATUM(JAAR(VANDAAG()),1,1)),import!$A$2:$A,""),
"")
) )
Door de gensete ALS acties ga je eerst na of de datum >= vorige jaar 1/1 en pas daarna of de datum ouder is dan 1/1 van dit jaar (<=).
Als dit waar is, neem je de timestamp uit import kolom A, zoniet maak je de waarde leeg)
De filter gebruikt deze om enkel waardes die hiermee overeen komen te filteren uit de lijst van alle waardes en de rest van de kolommen overeenkomstig over te nemen (import!$A$2:$K)
In de import sheet, wat een kopie is van een formsheet, wordt gekeken of de Timestamp in kolom A valt in het vorige jaar.
Een datum is een waarde die bepaald wordt berekenend van een bepaald start moment en komt daarom overeen met een getal.
Het omvormen van kolom A naar een datum gebeurt met TO_DATE.
Maak een datum van 1 jan van het vorige jaar
VANDAAG()-DATUM(JAAR(VANDAAG())-1,1,1)
Neem vandaag en daarvan het JAAR, trek hier 1 van af en voeg de dag en maand toe alvorens er terug een datum van te maken.
Bereken nu het aantal dagen dat dit geleden was door het van de huidge datum af te trekken. Zo bekom je de datum van vorig jaar 1/1)Doe hetzelfde voor deze van dit jaar
Indien de waarde in de kolom hier tussen valt, vorm je deze om tot een datum die je kan gebruiken in de filter actie.
Samenvoegen van lijsten uit verschillende kolomen tot één kolom
Samenvoegen van lijsten uit verschillende kolomen tot één kolom
Om verschillende kolommen tot éé kolom samen te voegen, kan je gebruik maken van een array notatie dmv {}.
=sort(unique({F4:F;I4:I;L4:L;O4:O;R4:R}))
De voorgaande notatie brengt 4 kolomen samen en haalt er enkel de unieke waarde uit, die nadien gesorteerd wordt.
Dit kan handig zijn om registraties van leden/cursisten uit verschillende tabellen/form exports samen te brengen en om te vormen tot een selectielijst..
Wanneer je lijsten wil samen voegen, waarbij er open rijen zijn, dan is het handig om gebruik te maken van de FILTER.
=SORT(UNIQUE({ filter(Lijst1!A1:B;LEN(Lijst1!A1:A)>0); filter(Lijst2!A1:B;LEN(Lijst2!A1:A)>0);
filter(Lijst3!A1:B;LEN(Lijst3!A1:A)>0);
filter(Lijst4!A1:B;LEN(Lijst4!A1:A)>0) }))
Het principe bestaat eruit dat je enkel rijen met een waarde in de kolom A over neemt.
Het bepalen of er een waarde is, doe je door de lengte van de celinhoud te bepalen en te zien of deze groter is dan 0
VLOOKUP en status meldingen
VLOOKUP en status meldingen
Als je wil achterhalen of iemand als deelnemer, lid, regelmatig aanwezig was tijdens een meeting, zonder onderbreking, dan kan je gebruik maken van een status berekening.
Aan iedere aanwezigheid, hang je een gewicht volgens het binaire stelsel en door de som te maken kan je nadien patronen herkennen.
Voorbeeld:
in kolom 1 gevonden geef je de waarde 8, kolom2 de waarde 4, kolom3 de waarde 2 en kolom1 de waarde 1(1111^2)
=SOM(
ALS(LENGTE(IFNA(VERT.ZOEKEN(Y2,$U$2:$U,1,ONWAAR),""))>1,8,0),
ALS(LENGTE(IFNA(VERT.ZOEKEN(Y2,$V$2:$V,1,ONWAAR),""))>1,4,0),
ALS(LENGTE(IFNA(VERT.ZOEKEN(Y2,$W$2:$W,1,ONWAAR),""))>1,2,0),
ALS(LENGTE(IFNA(VERT.ZOEKEN(Y2,$X$2:$X,1,ONWAAR),""))>1,1,0)
)
In de bovenstaande formule zoeken we met VLOOKUP het exact overeen komen.
Je geeft enkel de zoekterm weer als hij gevonden wordt (niet N#A IFNA), zoniet geef je een lege string.
Door te kijken naar de lengte>1 weet je of de term gevonden is en kan je deze uitkomst gebruiken om een berekening te maken.
De 4 kolommen, die doorzocht worden met dezelfde zoekterm Y2, worden gebruikt om een waarde te berekenen waarbij de 1ste kolom als gewicht 8 krijgt, de tweede 4, de derde 2 en de laatste 1.
De som geeft een getal dat je een aantal condities kan laten detecteren:
Voorbeeld:
=15: 4 maal gevonden
=14: laatste 3 maal aanwezig
12,13: De vorige twee maal aanwezig
>=8<=11: Nu aanwezig na afwezigheden
>=4<=7: laatste maal niet aanwezig
<4: meermaas afwezig
Conditionele opmaak
Conditionele opmaak
Aan de hand van de waarde kan je nu een rij of cel een bepaalde kleur geven.
In dit voorbeeld wordt de waarde in kolom K gebruikt om te vergelijken met de waarde 15, te starten vanaf de eerste rij.
Geef je enkel de rij op (zonder letter K) dan wordt de ganse tabel bekeken als range.
Wil je een conditionele waarde tussen twee waardes, dan kan je gebruik maken van een AND functie:
=AND($K1>5;$K1<10)
Is dit zo dan worden de kolommen A-K een rode kleur gegeven voor deze rij.
Statistische gegevens
Statistische gegevens
COUNTIFS om resultaten te bekijken
COUNTIFS om resultaten te bekijken
Om statistisch wat gegevens te kunnen maken, kan je gebruik maken van het tellen van een aantal waardes binnen een kolom.
=AANTALLEN.ALS(K:K,">=12",K:K,"<=13")
Door de COUNTIFS te gebruiken kan je meerdere condities na elkaar plaatsen, wat het mogelijk maakt om de vorige status melding in detail uit te splitsen.
De formule geeft nu het aantal personen dat de vorige twee maal afwezig was.
COUNTUNIQUE om unieke waardes te tellen
COUNTUNIQUE om unieke waardes te tellen
Als je uit een lijst het aantal unieke waardes wil halen, zonder de lijst aan te passen (vb hoeveel cursisten hebben de quizz één of meerdere keren afgelegd), dan kan je gebruik maken van de formule:
=COUNTUNIQUE(A2:A)
XLOOKUP
XLOOKUP
XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)
Deze functie geeft heel wat meer mogelijkheden om aan de hand van een sleutel een bepaald record te zoeken.
=X.ZOEKEN(Vorige!U2,$B$1:$B,$B$1:$L,,0,-1)
Het probleem met VLOOKUP is dat je in een tabel niet de laatste maar de eerste waarde krijgt die gevonden wordt.
Met XLOOKUP kan je door het meegeven van de paramater (-1 laatste waarde) de laatste waarde bekomen.
Dit is handig wanneer leden/cursisten meerdere antwoorden hebben gegeven in een formulier en je slechts het laatste wil gebruiken.
Zie voor meer detail XLOOKUP
Page updated
Report abuse