Waarom een dure aanwezigheidsbeheertool kopen voor uw startup als u de aanwezigheid van het team in Excel kunt volgen? Ja! U kunt eenvoudig een Aanwezigheidstracker maken in Excel. In dit artikel zullen we leren hoe u dit kunt doen.
Stap 1: Maak 12 bladen voor elke maand in een werkmap
Als u van plan bent de aanwezigheid een jaar lang bij te houden, moet u elke maand een blad maken in Excel.
Stap 2: Voeg kolommen toe voor elke datum in het blad van elke maand.
Maak nu een tabel met de namen van je teamgenoten, een kolom voor totalen en 30 (of aantal dagen per maand) kolommen met datum en weekdag als kolomkoppen.
Om de naam van de weekdag te krijgen, kunt u de kalender opzoeken of u kunt de formule gebruiken om deze naar de rest van de cellen te kopiëren.
| =TEKST(datum,"ddd") |
Je kunt er hier over lezen.
Maak de weekenden en feestdagen donker en vul ze met vaste waarden zoals Weekend/Vakantie zoals weergegeven in de onderstaande afbeelding.
Doe hetzelfde voor elk blad.
Stap 3. Corrigeer de mogelijke invoer met behulp van gegevensvalidatie voor elke open cel.
Nu kan iedereen zijn aanwezigheid in het blad invoegen, maar ze kunnen willekeurige tekst invoeren. Sommigen schrijven P voor heden, of Present, of per, enz. Uniformiteit van gegevens is verplicht in elk aanwezigheidsbeheersysteem.
Om gebruikers in staat te stellen alleen P of A te schrijven voor respectievelijk aanwezig en afwezig, kunnen we gegevensvalidatie gebruiken.
Selecteer een cel, ga naar gegevens in lint en klik op gegevensvalidatie. Selecteer een lijst uit de opties en schrijf A,P in het tekstvak.
Druk op OK.
Kopieer deze validatie voor het hele open gegevensbereik (open bereik betekent cel waar de gebruiker waarden kan invoegen).
Stap 3: Vergrendel alle cellen behalve waar de aanwezigheid moet worden ingevoerd.
Selecteer datum een datumkolom. Selecteer bijvoorbeeld 1 jan. Klik nu met de rechtermuisknop op het geselecteerde bereik en ga naar de celopmaak. Ga naar bescherming. Schakel het vergrendelde selectievakje uit. Druk op OK. Kopieer dit bereik nu naar alle open datumbereiken.
Hierdoor is toegang tot deze cellen alleen mogelijk als we de werkbladen beveiligen met het werkbladbeveiligingsmenu. Zo blijven uw formules, opmaak intact en kunnen gebruikers alleen hun aanwezigheid wijzigen.
Stap 4: Bereken de huidige dagen van teamgenoten
Dus hoe bereken je de huidige dagen? Nou, iedereen heeft zijn eigen formules om de aanwezigheid te berekenen. Ik zal de mijne hier bespreken. U kunt wijzigingen aanbrengen volgens uw aanwezigheidsformulier.
Ik tel het totale aantal huidige dagen als het totale aantal dagen in een maand, minus het aantal afwezige dagen. Dit houdt vakanties en weekenden in toom. Ze worden automatisch als werkdagen geteld.
Dus de Excel-formule voor het tellen van de huidige dagen zal zijn als:
| =AANTAL(datums)-AANTAL.ALS(aanwezigheidsbereik, "A") |
Hierdoor blijft iedereen standaard de hele maand aanwezig totdat u ze als afwezig op het blad hebt gemarkeerd.
In het voorbeeld is de formule:
| =COUNT($C$2:$AG$2)-COUNTIF(C3:AG3,"A") |
Ik heb deze formule in cel B3 geschreven en vervolgens gekopieerd. U kunt zien dat 27 dagen als cadeau worden weergegeven. Ook al heb ik niet alle aanwezigheidscellen gevuld. U kunt het op deze manier houden als u wilt dat ze standaard aanwezig zijn. Of als u wilt dat ze standaard afwezig zijn, vinkt u alle cellen aan als afwezig. Hierdoor tellen alleen de huidige dagen mee in de huidige berekening.
Stap 5: Bescherm het blad
Nu we alles op dit blad hebben gedaan. Laten we het beschermen zodat niemand de formule of de opmaak op het blad kan wijzigen.
Ga naar het review-tabblad in het lint. Zoek het menu Blad beschermen. Klik erop. Er wordt een dialoogvenster geopend waarin wordt gevraagd naar de machtigingen die u aan de gebruikers wilt geven. Vink alle machtigingen aan die u wilt toestaan. Ik wil alleen dat de gebruiker de aanwezigheid kan vullen met niets anders. Dus ik ga het houden zoals het is.
U moet een wachtwoord gebruiken dat u gemakkelijk kunt onthouden. Anders kan iedereen het ontgrendelen en de aanwezigheidswerkmap wijzigen.
Als u nu niet-aanwezigheidscellen probeert te wijzigen, staat Excel dit niet toe. U kunt de aanwezigheidscellen echter wijzigen omdat we ze onbeschermd hebben.
Stap 6: Voer de bovenstaande procedure uit voor alle maandbladen
Doe hetzelfde voor elk maandblad. De beste manier is om hetzelfde vel te kopiëren en er 12 vellen van te maken. Hef de beveiliging op en breng de nodige wijzigingen aan en beveilig ze vervolgens opnieuw.
Bereid het hoofdaanwezigheidsblad voor
Hoewel we alle bladen klaar hebben om te worden gebruikt voor het invullen van presentielijsten, hebben we niet één plek om ze allemaal te controleren.
De administratie wil graag alle aanwezigheden op één plek zien in plaats van op verschillende bladen. We moeten een master presentielijst maken.
Stap 7: Hoofdtabel voorbereiden om de aanwezigheid op één plaats in Excel te controleren
Maak daarvoor een tabel met de naam van teamgenoten als rijkoppen en de naam van de maand als kolomkoppen. Zie de afbeelding hieronder.
Stap 7: Aanwezigheid van team opzoeken van elk maandblad
Om de aanwezigheid van het blad op te zoeken, kunnen we een eenvoudige formule voor VERT.ZOEKEN gebruiken, maar dan moeten we dit 12 keer doen voor elk blad. Maar u weet dat we één formule kunnen hebben om van meerdere bladen op te zoeken.
Gebruik deze formule in cel C3 en kopieer in de rest van de bladen.
| =VERT.ZOEKEN($A3,INDIRECT(C$2&"!$A$3:$B$12"),2,0) |
Omdat we weten dat alle bladen een totale aanwezigheid hebben in het bereik B3:B12, gebruiken we de INDIRECT-functie om waarden van meerdere bladen op te halen. Wanneer u deze formule naar rechts kopieert, zoekt deze de waarden op in februari-bladen.
Let op: zorg ervoor dat de bladnamen en de kolomkoppen in de master hetzelfde zijn, anders werkt deze formule niet.
Stap 8: Gebruik de Som-functie om alle huidige dagen van het jaar van een teamgenoot te krijgen.
Dit is optioneel. Met de somformule kunt u desgewenst het totaal aantal tegenwoordige dagen van uw medewerkers over het hele jaar berekenen.
En dat is het. We hebben ons Excel Attendance Management Systeem gereed. U kunt dit naar uw wens aanpassen. Gebruik het voor salarisberekening, beloningsberekening of iets anders. Deze tool zal je niet teleurstellen.
U kunt in elk blad wijzigingen aanbrengen om vakanties en weekenden afzonderlijk te berekenen. Trek ze vervolgens af van het totaal aantal tegenwoordige dagen om het totale aantal werkdagen te berekenen. U kunt ook L voor verlof opnemen in de vervolgkeuzelijst om verlof van werknemers te markeren.
Dus ja jongens, dit is hoe je een Excel-aanwezigheidsbeheersysteem voor je startup kunt maken. Het is goedkoop en zeer flexibel. Ik hoop dat deze tutorial je helpt bij het maken van je eigen Excel-aanwezigheidswerkmap. Als je vragen hebt, laat het me dan weten in de comments hieronder.
Opzoeken van variabelentabellen met INDIRECT: Om op te zoeken vanuit een tabelvariabele in Excel, kunnen we de INDIRECT-functie gebruiken. De INDIRECT-functie neemt het tekstbereik en zet het om in het werkelijke aanwezigheidsbereik.
Gebruik INDEX en MATCH om waarde op te zoeken: De formule INDEX-MATCH wordt gebruikt om dynamisch en nauwkeurig een waarde in een bepaalde tabel op te zoeken. Dit is een alternatief voor de functie VERT.ZOEKEN en lost de tekortkomingen van de functie VERT.ZOEKEN op.
VERT.ZOEKEN gebruiken vanuit twee of meer opzoektabellen | Om uit meerdere tabellen op te zoeken, kunnen we een IFERROR-benadering gebruiken. Om uit meerdere tabellen op te zoeken, wordt de fout gebruikt als een schakelaar voor de volgende tabel. Een andere methode kan een If-benadering zijn.
Hoe hoofdlettergevoelig opzoeken in Excel te doen | de functie VERT.ZOEKEN van Excel is niet hoofdlettergevoelig en retourneert de eerste overeenkomende waarde uit de lijst. INDEX-MATCH is geen uitzondering, maar kan worden aangepast om hoofdlettergevoelig te worden. Laten we eens kijken hoe…
Veelvoorkomende tekst opzoeken met criteria in Excel | De zoekopdracht verschijnt het vaakst in tekst in een bereik dat we de INDEX-MATCH met MODE-functie gebruiken. Hier is de methode.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de Excel VERT.ZOEKEN-functie te gebruiken| Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de Excel te gebruiken? AANTAL.ALS-functie| Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.