Zakaj bi za svoj zagon kupili drago orodje za upravljanje obiskanosti, če lahko v Excelu spremljate prisotnost ekipe? Ja! Sledilnik prisotnosti v Excelu lahko preprosto ustvarite. V tem članku se bomo naučili, kako to storiti.
1. korak: Ustvarite 12 listov za vsak mesec v delovnem zvezku
Če nameravate eno leto spremljati prisotnost, boste morali ustvariti list za vsak mesec v Excelu.
2. korak: Dodajte stolpce za vsak datum na listu vsakega meseca.
Zdaj ustvarite tabelo, ki vsebuje imena vaših soigralcev, stolpec za vsote in 30 (ali število dni v mesecu) stolpcev z datumom in tednom kot naslovom stolpcev.
Če želite dobiti ime tedenskega dne, lahko poiščete koledar ali ga s formulo kopirate v preostale celice.
| = TEXT (datum, "ddd") |
O tem lahko preberete tukaj.
Oblikujte vikende in praznike temno in jih napolnite s fiksnimi vrednostmi, kot je vikend/prazniki, kot je prikazano na spodnji sliki.
Enako naredite za vsak list.
Korak 3. Popravite možne vnose z validacijo podatkov za vsako odprto celico.
Zdaj lahko vsak vnese svojo prisotnost v list, lahko pa vnese naključno besedilo. Nekateri lahko napišejo P za sedanjost ali Present ali za itd. Enotnost podatkov je obvezna v vsakem sistemu upravljanja prisotnosti.
Uporabnikom lahko omogočimo samo pisanje P oziroma A za sedanjo in odsotno, lahko uporabimo preverjanje podatkov.
Izberite katero koli celico, pojdite na podatke na traku in kliknite na potrditev podatkov. Med možnostmi izberite seznam in v besedilno polje vnesite A, P.
Pritisnite OK.
Kopirajte to potrditev za celotno odprto območje podatkov (odprto območje pomeni celico, kamor lahko uporabnik vstavi vrednosti).
3. korak: Zaklenite vse celice, razen tam, kjer je treba vnesti prisotnost.
Izberite datum stolpec z datumom. Na primer, izberite 1. januar. Zdaj kliknite na izbrani obseg in pojdite na oblikovanje celic. Pojdite na zaščito. Počistite polje za zaklepanje. Pritisnite OK. Zdaj kopirajte to območje v vsa odprta časovna obdobja.
To bo omogočilo vstop v te celice le, če delovne liste zaščitimo z menijem za zaščito delovnih listov. Tako bodo vaše formule, fomattings nedotaknjeni in uporabniki lahko samo spremenijo svojo udeležbo.
4. korak: Izračunajte trenutne dni soigralcev
Kako torej računate sedanje dni? No, vsak ima svoje formule za izračun obiskanosti. Tu bom razpravljal o svojem. Lahko spremenite v skladu z zahtevami za prisotnost.
Štejem skupno število sedanjih dni kot skupno število dni v mesecu, minus število dni odsotnosti. Tako bodo prazniki in vikendi nadzorovani. Samodejno se bodo šteli kot delovni dnevi.
Formula excela za štetje sedanjih dni bo torej naslednja:
| = COUNT (datumi) -COUNTIF (število obiskov, "A") |
To bo privzeto obdržalo vse prisotne ves mesec, dokler jih na listu ne označite kot odsotne.
V primeru je formula naslednja:
| = COUNT ($ C $ 2: $ AG $ 2) -COUNTIF (C3: AG3, "A") |
To formulo sem zapisal v celico B3 in jo nato prepisal. Vidite lahko, da je 27 dni prikazanih kot darilo. Čeprav nisem zapolnil vseh celic prisotnosti. Tako lahko ohranite, če želite, da so privzeto prisotne. Če pa želite, da so privzeto odsotni, preverite vse celice kot odsotne. Tako se bodo v sedanjem izračunu šteli samo sedanji dnevi.
5. korak: Zaščitite list
Zdaj, ko smo naredili vse na tem listu. Zaščitimo ga, da nihče ne bo mogel spremeniti formule ali oblikovanja na listu.
Pojdite na zavihek za pregled na traku. Poiščite meni Zaščitni list. Kliknite nanjo. Odprlo se bo pogovorno okno, ki bo zahtevalo dovoljenja, ki jih želite dati uporabnikom. Preverite vsa dovoljenja, ki jih želite dovoliti. Želim le, da bi uporabnik lahko zapolnil udeležbo z ničemer drugim. Zato ga bom ohranil takšnega, kot je.
Uporabiti morate geslo, ki si ga lahko zapomnite. V nasprotnem primeru ga lahko vsak odklene in spremeni delovni zvezek.
Če poskusite spremeniti celice, ki niso prisotne, vam Excel tega ne dovoli. Lahko pa spremenite celice prisotnosti, saj smo jih zaščitili.
Korak 6: Naredite zgornji postopek za vse mesečne liste
Za vsak mesec naredite isto. Najboljši način je, da kopirate isti list in iz njega naredite 12 listov. Odstranite zaščito in naredite potrebne spremembe ter jih nato znova zaščitite.
Pripravite glavni seznam udeležencev
Čeprav imamo vse liste pripravljene za polnjenje prisotnosti, nimamo enega mesta, kjer bi jih lahko vse spremljali.
Uprava bi rada videla vso prisotnost na enem mestu namesto na različnih listih. Ustvariti moramo glavni list prisotnosti.
7. korak: Pripravite glavno tabelo za spremljanje prisotnosti na enem mestu v Excelu
V ta namen pripravite tabelo, ki vsebuje ime soigralcev kot naslove vrstic in ime meseca kot naslove stolpcev. Oglejte si spodnjo sliko.
7. korak: Poiščite udeležbo ekipe na vsakem mesečnem listu
Za iskanje obiskanosti na listu lahko imamo preprosto formulo VLOOKUP, potem pa bomo to morali narediti 12 -krat za vsak list. Ampak veste, da imamo lahko eno formulo za iskanje z več listov.
Uporabite to formulo v celici C3 in kopirajte v preostale liste.
| = VLOOKUP ($ A3, NEPOSREDNO (C $ 2 & "! $ A $ 3: $ B $ 12"), 2,0) |
Ker vemo, da imajo vsi listi skupni obisk v območju B3: B12, uporabljamo funkcijo INDIRECT za pridobivanje vrednosti z več listov. Ko kopirate to formulo na desno, poišče vrednosti v februarskih listih.
Pozor: poskrbite, da bodo imena listov in naslovi stolpcev v glavni enoti enaki, sicer ta formula ne bo delovala.
8. korak: Uporabite funkcijo Vsota, da dobite vse sedanje dni v letu soigralca.
To ni obvezno. Če želite, lahko izračunate skupne sedanje dni svojih zaposlenih skozi vse leto s preprosto formulo za vsoto.
In to je to. Imamo pripravljen sistem za upravljanje udeležencev v Excelu. To lahko spremenite glede na vaše zahteve. Uporabite ga za izračun plače, izračun spodbude ali karkoli drugega. To orodje vas ne bo razočaralo.
Spremenite lahko izračune praznikov in vikendov ločeno na vsakem listu. Nato jih odštejte od skupnih sedanjih dni za izračun skupnih delovnih dni. V spustni meni lahko vključite tudi L za dopust, da označite dopust zaposlenih.
Torej, fantje, tako lahko za svoj zagon ustvarite sistem upravljanja obiskanosti v Excelu. Je poceni in zelo prilagodljiv. Upam, da vam bo ta vadnica pomagala pri ustvarjanju lastnega delovnega zvezka Excel. Če imate kakršna koli vprašanja, mi to sporočite v spodnjem oddelku za komentarje.
Iskanje iz spremenljivih tabel z uporabo INDIRECT: Za iskanje iz spremenljivke tabele v Excelu lahko uporabimo funkcijo INDIRECT. Funkcija INDIRECT bo sprejela obseg besedila in ga pretvorila v dejansko območje obiskanosti.
Za iskanje vrednosti uporabite INDEX in MATCH: Formula INDEX-MATCH se uporablja za dinamično in natančno iskanje vrednosti v dani tabeli. To je alternativa funkciji VLOOKUP in premaga pomanjkljivosti funkcije VLOOKUP.
Uporabite VLOOKUP iz dveh ali več iskalnih tabel | Za iskanje iz več tabel lahko uporabimo pristop IFERROR. Če iščete iz več tabel, se napaka šteje za stikalo za naslednjo tabelo. Druga metoda je lahko pristop If.
Kako v Excelu poiskati velike in male črke | Excelova funkcija VLOOKUP ne razlikuje velikih in malih črk in bo vrnila prvo ujemajočo se vrednost s seznama. INDEX-MATCH ni izjema, vendar ga je mogoče spremeniti tako, da je njegova velika in mala črka občutljiva. Poglejmo, kako…
Iskanje pogosto pojavljajočega se besedila z merili v Excelu | Iskanje se najpogosteje pojavi v besedilu v obsegu, ki ga uporabljamo INDEX-MATCH with MODE. Tukaj je metoda.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati funkcijo Excel VLOOKUP| To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati Excel COUNTIF funkcija| Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.