Prečo si pri štarte kúpiť drahý nástroj na správu dochádzky, ak môžete sledovať návštevnosť tímu v Exceli? Áno! Sledovač dochádzky môžete v programe Excel vytvoriť jednoducho. V tomto článku sa naučíme, ako to urobiť.
Krok 1: Vytvorte 12 listov pre každý mesiac v zošite
Ak plánujete sledovať dochádzku rok, budete musieť v Exceli vytvoriť hárok každého mesiaca.
Krok 2: Pridajte stĺpce pre každý dátum do listu každého mesiaca.
Teraz vytvorte tabuľku, ktorá obsahuje mená vašich spoluhráčov, stĺpček pre súčty a 30 (alebo počet dní v mesiaci) stĺpcov s názvami stĺpcov s dátumom a dňom v týždni.
Ak chcete získať názov dňa v týždni, môžete si vyhľadať kalendár alebo ho môžete pomocou vzorca skopírovať do ostatných buniek.
| = TEXT (dátum, "ddd") |
Môžete si o tom prečítať tu.
Víkendy a sviatky naformátujte na tmavo a naplňte ich pevnými hodnotami, ako napríklad Víkend/Sviatok, ako je znázornené na obrázku nižšie.
To isté urobte pre každý list.
Krok 3. Opravte možné vstupy pomocou validácie údajov pre každú otvorenú bunku.
Teraz môže každý do hárka vložiť svoju účasť, ale môže zadať náhodný text. Niektorí môžu písať P za súčasnosť alebo Súčasnosť alebo za atď. Jednotnosť údajov je povinná v každom systéme riadenia dochádzky.
Aby sme používateľom umožnili písať iba P alebo A za prítomné a neprítomné, môžeme použiť validáciu údajov.
Vyberte ľubovoľnú bunku, prejdite na údaje na páse s nástrojmi a kliknite na overenie údajov. Vyberte zoznam z možností a do textového poľa napíšte A, P.
Kliknite na OK.
Skopírujte toto overenie pre celý otvorený rozsah údajov (otvorený rozsah znamená bunku, do ktorej môže používateľ vkladať hodnoty).
Krok 3: Uzamknite všetky bunky okrem prípadov, keď je potrebné zadať dochádzku.
Vyberte dátum a stĺpec dátumu. Vyberte napríklad 1. január. Teraz kliknite na vybratý rozsah a prejdite na formátovanie bunky. Choďte na ochranu. Zrušte začiarknutie políčka uzamknuté. Kliknite na OK. Teraz skopírujte tento rozsah do všetkých otvorených rozsahov dátumov.
To umožní vstup do týchto buniek iba vtedy, keď chránime pracovné hárky pomocou ponuky ochrany pracovných hárkov. Vaše vzorce, fomattings tak budú nedotknuté a používatelia môžu iba upravovať svoju účasť.
Krok 4: Vypočítajte súčasné dni spoluhráčov
Ako teda vypočítate súčasnosť? Každý má svoje vlastné vzorce na výpočet dochádzky. Tu budem diskutovať o svojom. Môžete vykonávať zmeny podľa svojich požiadaviek na prezenčný list.
Celkový počet súčasných dní počítam ako celkový počet dní v mesiaci mínus počet dní absencie. Vďaka tomu budete mať sviatky a víkendy pod kontrolou. Automaticky sa budú počítať ako pracovné dni.
Vzorec programu Excel na počítanie súčasných dní bude teda vyzerať takto:
| = COUNT (dátumy) -COUNTIF (rozsah dochádzky, "A") |
Štandardne budú všetci prítomní celý mesiac, kým ich na hárku neoznačíte ako neprítomných.
V tomto prípade vzorec je:
| = COUNT ($ C $ 2: $ AG $ 2) -COUNTIF (C3: AG3, „A“) |
Tento vzorec som napísal do bunky B3 a potom som ho skopíroval. Môžete vidieť, že 27 dní je zobrazených ako darček. Aj keď som nevyplnil všetky bunky prítomnosti. Môžete to tak ponechať, ak chcete, aby boli predvolene prítomní. Alebo ak chcete, aby predvolene chýbali, začiarknite všetky bunky ako neprítomné. V tomto výpočte sa tak budú počítať iba súčasné dni.
Krok 5: Chráňte list
Teraz, keď sme urobili všetko pre tento list. Chráňme to tak, aby nikto nemohol meniť vzorec alebo formátovanie na hárku.
Na páse s nástrojmi prejdite na kartu kontroly. Nájdite ponuku Chrániť list. Kliknite naň. Otvorí sa dialógové okno, ktoré požiada o povolenia, ktoré chcete udeliť používateľom. Začiarknite všetky povolenia, ktoré chcete povoliť. Chcem len, aby bol používateľ schopný vyplniť dochádzku ničím iným. Nechám to teda tak, ako to je.
Mali by ste použiť heslo, ktoré si ľahko zapamätáte. V opačnom prípade ho môže ktokoľvek odomknúť a zmeniť dochádzkový zošit.
Ak sa teraz pokúsite zmeniť bunky dochádzky, Excel vám to nedovolí. Bunky dochádzky však môžete zmeniť, pretože sme ich nechránili.
Krok 6: Vykonajte vyššie uvedený postup pre všetky mesačné listy
To isté urobte pre každý hárok mesiaca. Najlepším spôsobom je skopírovať ten istý list a vytvoriť z neho 12 listov. Zrušte ich ochranu, vykonajte potrebné zmeny a potom ich znova chráňte.
Pripravte si prezenčný list majstra
Napriek tomu, že máme všetky hárky pripravené na použitie pri dochádzke, nemáme jedno miesto, kde by sme ich všetky monitorovali.
Administratíva by chcela vidieť všetku účasť na jednom mieste, a nie na rôznych listoch. Musíme vytvoriť hlavný dochádzkový list.
Krok 7: Pripravte si tabuľku na monitorovanie dochádzky na jednom mieste v Exceli
Na to pripravte tabuľku, ktorá bude obsahovať názvy spoluhráčov ako nadpisy riadkov a názov mesiaca ako záhlavia stĺpcov. Pozrite sa na obrázok nižšie.
Krok 7: Vyhľadajte dochádzku tímu z každého mesačného listu
Na vyhľadanie dochádzky z hárka môžeme mať jednoduchý vzorec VLOOKUP, ale potom to budeme musieť urobiť 12 -krát pre každý hárok. Ale viete, že môžeme mať jeden vzorec, ktorý môžeme vyhľadať z viacerých hárkov.
Tento vzorec použite v bunke C3 a skopírujte do ostatných hárkov.
| = VLOOKUP (A3 $, NEPRIAMY (C $ 2 & "! $ A $ 3: $ B $ 12" "), 2,0) |
Pretože vieme, že všetky hárky majú celkovú návštevnosť v rozsahu B3: B12, na načítanie hodnôt z viacerých hárkov používame funkciu INDIRECT. Keď skopírujete tento vzorec doprava, vyhľadá hodnoty v hárkoch februára.
Pozor: uistite sa, že názvy hárkov a nadpisy stĺpcov v predlohe sú rovnaké, inak tento vzorec nebude fungovať.
Krok 8: Pomocou funkcie Sum získate všetky súčasné dni v roku spoluhráča.
Toto je voliteľné. Ak chcete, môžete vypočítať celkový počet súčasných dní svojich zamestnancov počas celého roka jednoducho pomocou súčtového vzorca.
A to je všetko. Náš systém správy dochádzky v Exceli máme pripravený. Môžete to zmeniť podľa svojich požiadaviek. Použite ho na výpočet platu, incentívny výpočet alebo čokoľvek iné. Tento nástroj vás nesklame.
V každom hárku môžete vykonať zmeny na výpočet sviatkov a víkendov oddelene. Potom ich odpočítajte od celkového počtu súčasných dní a vypočítajte celkový počet pracovných dní. Do rozbaľovacieho zoznamu môžete tiež zahrnúť L pre dovolenku, aby ste označili dovolenku zamestnancov.
Takže áno, chlapci, takto môžete vytvoriť vynikajúci systém správy dochádzky pre vaše spustenie. Je to lacné a vysoko flexibilné. Dúfam, že vám tento návod pomôže pri vytváraní vlastného excelského dochádzkového zošita. Ak máte akékoľvek otázky, dajte mi vedieť v sekcii komentárov nižšie.
Vyhľadávanie z variabilných tabuliek pomocou NEPRIAME: Na vyhľadanie z tabuľkovej premennej v Exceli môžeme použiť funkciu NEPRIAMY. Funkcia NEPRIAMY prevezme rozsah textu a prevedie ho na skutočný rozsah dochádzky.
Na vyhľadanie hodnoty použite INDEX a MATCH: Vzorec INDEX-MATCH sa používa na dynamické a presné vyhľadanie hodnoty v danej tabuľke. Je to alternatíva k funkcii VLOOKUP a prekonáva nedostatky funkcie VLOOKUP.
Použite VLOOKUP z dvoch alebo viacerých vyhľadávacích tabuliek | Na vyhľadávanie z viacerých tabuliek môžeme použiť prístup IFERROR. Vzhľad hore z viacerých tabuliek považuje chybu za prepínač pre nasledujúcu tabuľku. Ďalšou metódou môže byť prístup If.
Ako vyhľadávať veľké a malé písmena v programe Excel | funkcia VLOOKUP v Exceli nerozlišuje malé a veľké písmená a vráti prvú zodpovedajúcu hodnotu zo zoznamu. INDEX-MATCH nie je výnimkou, ale môže byť upravený tak, aby rozlišoval veľké a malé písmena. Pozrime sa, ako…
Vyhľadávanie často sa vyskytujúceho textu s kritériami v programe Excel | Vyhľadávanie sa najčastejšie vyskytuje v texte v rozsahu, v ktorom používame funkciu INDEX-MATCH s funkciou REŽIM. Tu je metóda.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať funkciu Excel VLOOKUP| Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať Excel Funkcia COUNTIF| Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.