Hvorfor kjøpe et dyrt oppmøtehåndteringsverktøy for oppstarten hvis du kan spore teamets oppmøte i Excel? Ja! Du kan enkelt lage en tilstedeværelsessporer i Excel. I denne artikkelen vil vi lære hvordan du gjør det.
Trinn 1: Lag 12 ark for hver måned i en arbeidsbok
Hvis du planlegger å spore oppmøte i et år, må du lage hver måneds ark i Excel.
Trinn 2: Legg til kolonner for hver dato i hver måneds ark.
Lag nå en tabell som inneholder navnene på lagkameratene dine, en kolonne for totaler og 30 (eller antall dager i måneden) kolonner med dato og ukedag som kolonneoverskrifter.
For å få navnet på ukedag kan du slå opp kalenderen, eller du kan bruke formelen til å kopiere den i resten av cellene.
| = TEKST (dato, "ddd") |
Du kan lese om det her.
Formater helgene og høytidene mørke og fyll dem med faste verdier som helg/ferie som vist på bildet nedenfor.
Gjør det samme for hvert ark.
Trinn 3. Fix de mulige inngangene ved hjelp av datavalidering for hver åpen celle.
Nå kan alle sette inn sitt oppmøte i arket, men de kan skrive tilfeldig tekst. Noen kan skrive P for present, eller Present, eller per, etc. Datauniformitet er obligatorisk i ethvert oppmøtehåndteringssystem.
For å la brukerne bare skrive P eller A for henholdsvis nåværende og fraværende, kan vi bruke datavalidering.
Velg hvilken som helst celle, gå til data i båndet og klikk på datavalidering. Velg liste fra alternativer og skriv A, P i tekstboksen.
Trykk OK.
Kopier denne valideringen for hele det åpne dataområdet (åpent område betyr celle der brukeren kan sette inn verdier).
Trinn 3: Lås alle cellene bortsett fra når oppmøte må angis.
Velg dato og dato -kolonne. Velg for eksempel 1-jan. Klikk nå på det valgte området og gå til celleformateringen. Gå til beskyttelse. Fjern merket for den låste avmerkingsboksen. Trykk OK. Kopier nå dette området til alle åpne datointervaller.
Dette tillater bare oppføring i disse cellene når vi beskytter regnearkene ved å bruke regnearkbeskyttelsesmenyen. Dermed vil formlene, beregningene være intakte, og brukerne kan bare endre sitt oppmøte.
Trinn 4: Beregn nåværende dager for lagkamerater
Så hvordan beregner du dagens dager? Alle har sine egne formler for å beregne oppmøte. Jeg vil diskutere min her. Du kan gjøre endringer i henhold til kravet om oppmøte.
Jeg teller det totale antallet nåværende dager som totale dager i en måned, minus antall dager som er fraværende. Dette vil holde ferier og helger i sjakk. De blir automatisk regnet som virkedager.
Så excelformelen for å telle nåværende dager vil være som:
| = COUNT (datoer) -COUNTIF (attendence_range, "A") |
Dette vil som standard beholde alle til stede i hele måneden til du har merket dem fraværende på arket.
I eksemplet er formelen:
| = COUNT ($ C $ 2: $ AG $ 2) -COUNTIF (C3: AG3, "A") |
Jeg har skrevet denne formelen i celle B3 og deretter kopiert den ned. Du kan se at 27 dager vises som en gave. Selv om jeg ikke har fylt alle tilstedeværelsescellene. Du kan beholde den på denne måten hvis du vil at de som standard skal være tilstede. Eller hvis du vil at de som standard skal være fraværende, sjekk alle cellene som fraværende. Dette vil bare beholde gjeldende dager i den nåværende beregningen.
Trinn 5: Beskytt arket
Nå som vi har gjort alt på dette arket. La oss beskytte det slik at ingen kan endre formelen eller formateringen på arket.
Gå til anmeldelse -fanen på båndet. Finn menyen Beskytt ark. Klikk på den. Den åpner en dialogboks som vil be om tillatelsene du vil gi brukerne. Sjekk alle tillatelsene du vil tillate. Jeg vil bare at brukeren skal kunne fylle oppmøte med ingenting annet. Så jeg skal beholde det som det er.
Du bør bruke et passord som du enkelt kan huske. Ellers kan hvem som helst låse den opp og endre arbeidsbok for oppmøte.
Hvis du prøver å endre ikke -oppmøte -celler, vil Excel ikke tillate deg å gjøre det. Du kan imidlertid endre tilstedeværelsescellene ettersom vi har ubeskyttet dem.
Trinn 6: Gjør prosedyren ovenfor for alle månedsarkene
Gjør det samme for hvert månedsark. Den beste måten er å kopiere det samme arket og lage 12 ark av det. Fjern beskyttelsen av dem og gjør de nødvendige endringene, og beskytt dem deretter igjen.
Utarbeid hovedoppmøtearket
Selv om vi har alle arkene klare til bruk for oppmøte, har vi ikke ett sted å overvåke dem alle.
Administrasjonen vil gjerne se alt oppmøtet på ett sted i stedet for på forskjellige ark. Vi må lage et hovedoppmøteark.
Trinn 7: Forbered hovedtabellen for å overvåke oppmøte på ett sted i Excel
For det, lag en tabell som inneholder navnet på lagkamerater som radoverskrifter og månedens navn som kolonneoverskrifter. Se bildet nedenfor.
Trinn 7: Slå opp tilstedeværelse av team fra hvert månedsark
For å slå opp deltakelse fra arket kan vi ha en enkel VLOOKUP -formel, men da må vi gjøre det 12 ganger for hvert ark. Men du vet at vi kan ha én formel for å slå opp fra flere ark.
Bruk denne formelen i celle C3 og kopier inn resten av arkene.
| = VLOOKUP ($ A3, INDIRECT (C $ 2 & "! $ A $ 3: $ B $ 12"), 2,0) |
Siden vi vet at alle arkene har totalt oppmøte i område B3: B12, bruker vi INDIRECT -funksjonen til å hente verdier fra flere ark. Når du kopierer denne formelen til høyre, ser den opp verdier i februar -ark.
Forsiktig: Kontroller at arknavn og kolonneoverskrifter i master er de samme, ellers fungerer ikke denne formelen.
Trinn 8: Bruk Sum -funksjonen til å få alle nåværende dager i året til en lagkamerat.
Dette er valgfritt. Hvis du vil, kan du beregne de totale nåværende dagene til dine ansatte gjennom året ved ganske enkelt å bruke sumformelen.
Og det er det. Vi har vårt Excel Attendance Management System klart. Du kan endre dette etter dine krav. Bruk den til lønnsberegning, insentivberegning eller noe annet. Dette verktøyet vil ikke svikte deg.
Du kan gjøre endringer for å beregne høytider og helger separat i hvert ark. Trekk dem deretter fra de totale nåværende dagene for å beregne totale arbeidsdager. Du kan også inkludere L for permisjon i rullegardinlisten for å markere permisjon for ansatte.
Så ja gutta, dette er hvordan du kan lage et excel -oppmøtehåndteringssystem for oppstarten din. Det er billig og svært fleksibelt. Jeg håper denne opplæringen hjelper deg med å lage din egen arbeidsoppgave for Excel -oppmøte. Gi meg beskjed i kommentarfeltet nedenfor hvis du har spørsmål.
Slå opp fra variabeltabeller ved bruk av INDIRECT: For å slå opp fra en tabellvariabel i Excel, kan vi bruke INDIRECT -funksjonen. INDIRECT -funksjonen tar tekstområdet og konverterer det til det faktiske oppmøteområdet.
Bruk INDEX og MATCH til å slå opp verdi: INDEX-MATCH-formelen brukes til å slå dynamisk og presist opp en verdi i en gitt tabell. Dette er et alternativ til VLOOKUP -funksjonen, og den overvinner manglene i VLOOKUP -funksjonen.
Bruk VLOOKUP fra to eller flere oppslagstabeller | For å slå opp fra flere tabeller kan vi ta en IFERROR -tilnærming. Å slå opp fra flere tabeller tar feilen som en bryter for neste tabell. En annen metode kan være en If -tilnærming.
Hvordan gjøre saksfølsom oppslag i Excel | excels VLOOKUP -funksjon er ikke mellom store og små bokstaver, og den returnerer den første matchede verdien fra listen. INDEX-MATCH er intet unntak, men det kan modifiseres for å gjøre bokstavssensitiv. La oss se hvordan …
Oppslag som ofte vises tekst med kriterier i Excel | Søket vises oftest i tekst i et område vi bruker INDEX-MATCH with MODE-funksjonen. Her er metoden.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du Excel VLOOKUP -funksjon| Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du Excel COUNTIF -funksjon| Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.