Kodėl verta pirkti brangų lankomumo valdymo įrankį savo startuoliui, jei galite stebėti komandos lankomumą „Excel“? Taip! „Excel“ galite lengvai sukurti lankomumo stebėjimo priemonę. Šiame straipsnyje mes sužinosime, kaip tai padaryti.
1 žingsnis: sukurkite 12 lapų kiekvieną mėnesį darbo knygoje
Jei planuojate sekti lankomumą metus, turėsite sukurti kiekvieno mėnesio lapą „Excel“.
2 veiksmas: pridėkite stulpelių kiekvienai datai kiekvieno mėnesio lape.
Dabar sukurkite lentelę, kurioje būtų jūsų komandos draugų vardai, stulpelis sumoms ir 30 stulpelių (arba dienų skaičius per mėnesį) su data ir savaitės diena kaip stulpelių antraštės.
Norėdami sužinoti savaitės dienos pavadinimą, galite ieškoti kalendoriaus arba naudoti formulę, kad nukopijuotumėte jį į likusias ląsteles.
| = TEKSTAS (data, "ddd") |
Apie tai galite paskaityti čia.
Savaitgalius ir šventes suformatuokite tamsiai ir užpildykite fiksuotomis vertėmis, pvz., Savaitgalis/atostogos, kaip parodyta paveikslėlyje žemiau.
Atlikite tą patį su kiekvienu lapu.
Žingsnis 3. Ištaisykite galimas įvestis naudodami kiekvieno atviro langelio duomenų patvirtinimą.
Dabar kiekvienas gali įterpti savo lankomumą į lapą, bet gali įvesti atsitiktinį tekstą. Kai kurie gali parašyti P dabartiniam, arba Dabartinis, arba per ir tt Duomenų vienodumas yra privalomas bet kurioje lankomumo valdymo sistemoje.
Norėdami leisti vartotojams rašyti P arba A tik esantiems ir nebuvusiems, galime naudoti duomenų patvirtinimą.
Pasirinkite bet kurį langelį, eikite į duomenis juostelėje ir spustelėkite duomenų patvirtinimą. Pasirinkite sąrašą iš parinkčių ir teksto laukelyje parašykite A, P.
Spustelėkite Gerai.
Nukopijuokite šį patvirtinimą visam atviram duomenų diapazonui (atviras diapazonas reiškia langelį, kuriame vartotojas gali įterpti reikšmes).
3 veiksmas: užrakinkite visus langelius, išskyrus tuos atvejus, kai reikia įvesti dalyvavimą.
Pasirinkite datos ir datos stulpelį. Pavyzdžiui, pasirinkite sausio 1 d. Dabar spustelėkite pasirinktą diapazoną ir eikite į langelio formatavimą. Eikite į apsaugą. Atžymėkite užrakto žymės langelį. Spustelėkite Gerai. Dabar nukopijuokite šį diapazoną į visas atidarytas dienų sekas.
Tai leis patekti į šias ląsteles tik tada, kai saugosime darbalapius naudodami darbalapių apsaugos meniu. Taigi jūsų formulės, duomenys bus nepažeisti ir vartotojai galės tik keisti savo lankomumą.
4 žingsnis: apskaičiuokite dabartines komandos draugų dienas
Taigi kaip apskaičiuoti dabartines dienas? Na, kiekvienas turi savo lankomumo skaičiavimo formules. Čia aptarsiu savo. Galite atlikti pakeitimus pagal savo lankomumo lapo reikalavimus.
Visą dabartinių dienų skaičių skaičiuoju kaip visas mėnesio dienas, atėmus nebuvimo dienų skaičių. Taip bus kontroliuojamos atostogos ir savaitgaliai. Jie automatiškai bus skaičiuojami kaip darbo dienos.
Taigi „Excel“ formulė, skirta skaičiuoti dabartines dienas, bus tokia:
| = COUNT (datos) -COUNTIF (lankomumo diapazonas, „A“) |
Pagal numatytuosius nustatymus visi asmenys bus visą mėnesį, kol lape nepažymėsite jų nebuvimo.
Pavyzdyje formulė yra tokia:
| = COUNT ($ C $ 2: $ AG $ 2) -COUNTIF (C3: AG3, "A") |
Aš parašiau šią formulę B3 langelyje ir nukopijavau. Matote, kad 27 dienos rodomos kaip dovana. Nors aš neužpildžiau visų buvimo langelių. Galite tai palikti, jei norite, kad jie būtų pagal numatytuosius nustatymus. Arba, jei norite, kad pagal numatytuosius nustatymus jų nebūtų, patikrinkite, ar nėra visų langelių. Atliekant šį skaičiavimą, tik dabartinės dienos bus skaičiuojamos.
5 žingsnis: apsaugokite lapą
Dabar, kai padarėme viską šiame lape. Saugokime, kad niekas negalėtų pakeisti lapo formulės ar formato.
Eikite į peržiūros skirtuką juostelėje. Raskite meniu „Apsaugoti lapą“. Spustelėkite jį. Bus atidarytas dialogo langas, kuriame bus prašoma leidimų, kuriuos norite suteikti vartotojams. Patikrinkite visus norimus leisti leidimus. Aš tik noriu, kad vartotojas galėtų užpildyti dalyvavimą niekuo kitu. Taigi aš pasiliksiu taip, kaip yra.
Turėtumėte naudoti slaptažodį, kurį lengvai atsiminsite. Priešingu atveju kiekvienas gali ją atrakinti ir pakeisti lankomumo darbo knygą.
Dabar, jei bandysite pakeisti neatvykimo langelius, „Excel“ neleis jums to padaryti. Tačiau galite pakeisti lankomumo langelius, nes mes jų neapsaugojome.
6 veiksmas: atlikite aukščiau aprašytą procedūrą visiems mėnesio lapams
Atlikite tą patį kiekvieno mėnesio lape. Geriausias būdas yra nukopijuoti tą patį lapą ir padaryti iš jo 12 lapų. Apsaugokite juos ir atlikite reikiamus pakeitimus, tada vėl juos apsaugokite.
Paruoškite pagrindinį lankomumo lapą
Nors visi lapai yra paruošti naudoti lankomumui užpildyti, neturime vienos vietos juos visus stebėti.
Administracija norėtų, kad visi dalyviai būtų vienoje vietoje, o ne skirtinguose lapuose. Turime sukurti pagrindinį lankomumo lapą.
7 žingsnis: Paruoškite pagrindinę lentelę, kad galėtumėte stebėti lankomumą vienoje „Excel“ vietoje
Tam paruoškite lentelę, kurioje eilučių antraštėmis nurodomas komandos draugų vardas ir mėnesio pavadinimas. Žiūrėkite paveikslėlį žemiau.
7 žingsnis: ieškokite komandos lankomumo iš kiekvieno mėnesio lapo
Norėdami sužinoti lankomumą iš lapo, galime turėti paprastą VLOOKUP formulę, bet tada turėsime tai padaryti 12 kartų kiekvienam lapui. Bet jūs žinote, kad galime turėti vieną formulę, kurią galime ieškoti iš kelių lapų.
Naudokite šią formulę langelyje C3 ir nukopijuokite į kitus lapus.
| = VLOOKUP ($ A3, NETIESIOGINĖ ($ 2 ir "! $ A $ 3: $ B $ 12"), 2,0) |
Kadangi žinome, kad visi lapai yra iš viso lankomi diapazone B3: B12, mes naudojame funkciją NETIESIOGINĖ, norėdami gauti reikšmes iš kelių lapų. Kai nukopijuojate šią formulę į dešinę, ji vasario mėnesio lapuose randa reikšmes.
Įspėjimas: įsitikinkite, kad lapo pavadinimai ir stulpelio antraštės pagrindiniame puslapyje yra vienodi, kitaip ši formulė neveiks.
8 veiksmas: naudokite „Sum“ funkciją, kad gautumėte visas dabartines komandos draugo metų dienas.
Tai neprivaloma. Jei norite, galite apskaičiuoti bendrą šių dienų darbuotojų skaičių per metus tiesiog naudodami sumos formulę.
Ir viskas. Turime parengtą „Excel“ lankomumo valdymo sistemą. Tai galite pakeisti pagal savo reikalavimus. Naudokite jį apskaičiuodami atlyginimą, paskatų apskaičiavimą ar bet ką kitą. Šis įrankis jūsų nenuvils.
Galite atlikti pakeitimus, kad kiekviename lape atskirai apskaičiuotumėte atostogas ir savaitgalius. Tada atimkite juos iš visų dabartinių dienų, kad apskaičiuotumėte bendrą darbo dienų skaičių. Taip pat išskleidžiamajame meniu galite įtraukti L už atostogas, kad pažymėtumėte darbuotojų atostogas.
Taigi taip, vaikinai, taip galite sukurti „Excel“ lankomumo valdymo sistemą savo verslui. Jis yra pigus ir labai lankstus. Tikiuosi, kad ši pamoka padės jums sukurti savo „Excel“ lankomumo darbo knygą. Jei turite klausimų, praneškite man toliau pateiktame komentarų skyriuje.
Ieškoti iš kintamųjų lentelių naudojant INDIRECT: Norėdami ieškoti iš „Excel“ lentelės kintamojo, galime naudoti funkciją INDIRECT. Funkcija NETIESIOGINĖ paims teksto diapazoną ir pavers jį faktiniu lankomumo diapazonu.
Norėdami ieškoti vertės, naudokite INDEX ir MATCH: INDEX-MATCH formulė naudojama dinamiškai ir tiksliai ieškoti vertės tam tikroje lentelėje. Tai yra alternatyva funkcijai VLOOKUP ir ji pašalina VLOOKUP funkcijos trūkumus.
Naudokite VLOOKUP iš dviejų ar daugiau peržiūros lentelių | Norėdami ieškoti iš kelių lentelių, galime taikyti IFERROR metodą. Jei norite ieškoti iš kelių lentelių, klaida laikoma kitos lentelės jungikliu. Kitas metodas gali būti If metodas.
Kaip atlikti didžiųjų ir mažųjų raidžių paiešką „Excel“ | „Excel“ funkcija VLOOKUP neskiria didžiųjų ir mažųjų raidžių ir grąžins pirmąją atitiktį iš sąrašo. „INDEX-MATCH“ nėra išimtis, tačiau ji gali būti pakeista, kad būtų didžiosios ir mažosios raidės. Pažiūrėkime, kaip…
Dažnai rodomo teksto paieška naudojant „Excel“ kriterijus | Dažniausiai paieška rodoma tekste diapazone, kuriame naudojame funkciją INDEX-MATCH with MODE. Štai metodas.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis „Excel VLOOKUP“ funkcija| Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
Kaip naudotis „Excel“ Funkcija COUNTIF| Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.