Kā izveidot apmeklējumu izsekotāju programmā Excel

Satura rādītājs:

Anonim

Kāpēc pirkt dārgu apmeklējumu pārvaldības rīku savam startam, ja varat izsekot komandas apmeklējumam programmā Excel? Jā! Programmā Excel varat viegli izveidot apmeklējumu izsekotāju. Šajā rakstā mēs uzzināsim, kā to izdarīt.

1. darbība: izveidojiet 12 lapas darbgrāmatā katram mēnesim

Ja plānojat uzskaitīt apmeklējumu uz gadu, programmā Excel jāizveido katra mēneša lapa.

2. darbība. Pievienojiet slejas katram datumam katra mēneša lapā.

Tagad izveidojiet tabulu, kurā ir jūsu komandas biedru vārdi, kolonna kopsummām un 30 (vai dienu skaits mēnesī) kolonnas ar datumu un nedēļas dienu kā sleju virsrakstus.

Lai iegūtu nedēļas dienas nosaukumu, varat meklēt kalendāru vai izmantot formulu, lai to kopētu pārējās šūnās.

= TEKSTS (datums, "ddd")

Par to varat izlasīt šeit.

Formatējiet nedēļas nogales un brīvdienas tumšā krāsā un aizpildiet tās ar fiksētām vērtībām, piemēram, Nedēļas nogale/brīvdienas, kā parādīts attēlā zemāk.

Dariet to pašu katrai loksnei.

3. solis. Izlabojiet iespējamos ievades datus, izmantojot katras atvērtās šūnas datu validāciju.

Tagad ikviens var ievietot savu apmeklējumu lapā, bet var ievadīt izlases tekstu. Daži var rakstīt P tagadnei, vai Pašreizējais, vai par utt. Datu vienveidība ir obligāta jebkurā apmeklējumu vadības sistēmā.

Lai ļautu lietotājiem rakstīt tikai P vai A tikai tagadnes un prombūtnes laikā, mēs varam izmantot datu validāciju.

Atlasiet jebkuru šūnu, dodieties uz lentes datiem un noklikšķiniet uz datu validācijas. Izvēlieties sarakstu no iespējām un tekstlodziņā ierakstiet A, P.

Noklikšķiniet uz Labi.

Kopējiet šo apstiprinājumu visam atvērtajam datu diapazonam (atvērts diapazons nozīmē šūnu, kurā lietotājs var ievietot vērtības).

3. darbība: bloķējiet visas šūnas, izņemot vietas, kur jāievada apmeklējums.

Atlasiet datumu un datuma kolonnu. Piemēram, atlasiet 1. janvāris. Šobrīd noklikšķiniet uz atlasītā diapazona un dodieties uz šūnu formatējumu. Iet uz aizsardzību. Noņemiet atzīmi no bloķētās izvēles rūtiņas. Noklikšķiniet uz Labi. Tagad kopējiet šo diapazonu uz visiem atvērtajiem datumu diapazoniem.

Tas ļaus iekļūt šajās šūnās tikai tad, kad mēs aizsargājam darblapas, izmantojot darblapu aizsardzības izvēlni. Tādējādi jūsu formulas, informācija būs neskarta, un lietotāji varēs tikai mainīt savu apmeklējumu.

4. solis: aprēķiniet komandas biedru pašreizējās dienas

Tātad, kā aprēķināt pašreizējās dienas? Nu katram ir savas formulas apmeklētības aprēķināšanai. Es šeit apspriedīšu savu. Jūs varat veikt izmaiņas atbilstoši jūsu apmeklējuma lapas prasībām.

Kopējo pašreizējo dienu skaitu es skaita kā kopējās dienas mēnesī, atskaitot prombūtnes dienu skaitu. Tas kontrolēs brīvdienas un nedēļas nogales. Tie automātiski tiks ieskaitīti darba dienās.

Tātad Excel formula šodienas skaitīšanai būs šāda:

= COUNT (datumi) -COUNTIF (apmeklējumu diapazons, "A")

Tas pēc noklusējuma saglabās visus klātesošos visu mēnesi, līdz esat atzīmējis viņu prombūtnē uz lapas.

Piemērā formula ir šāda:

= COUNT ($ C $ 2: $ AG $ 2) -COUNTIF (C3: AG3, "A")

Es esmu uzrakstījis šo formulu šūnā B3 un pēc tam nokopējis. Jūs varat redzēt, ka 27 dienas tiek parādītas kā dāvana. Pat ja es neesmu aizpildījis visas klātbūtnes šūnas. Jūs varat to saglabāt šādā veidā, ja vēlaties, lai tie būtu pēc noklusējuma. Vai arī, ja vēlaties, lai pēc noklusējuma to nebūtu, pārbaudiet visas šūnas kā neesošas. Šajā aprēķinā tiks ņemtas vērā tikai pašreizējās dienas.

5. darbība: aizsargājiet lapu

Tagad, kad esam izdarījuši visu šajā lapā. Aizsargāsim to, lai neviens nevarētu mainīt lapas formulu vai formatējumu.

Atveriet pārskatīšanas cilni lentē. Atrodiet izvēlni Aizsargāt lapu. Noklikšķiniet uz tā. Tiks atvērts dialoglodziņš, kurā tiks prasītas atļaujas, kuras vēlaties piešķirt lietotājiem. Pārbaudiet visas atļaujas, kuras vēlaties atļaut. Es tikai vēlos, lai lietotājs varētu aizpildīt apmeklējumu ar neko citu. Tāpēc es to paturēšu tādu, kāds tas ir.

Jums vajadzētu izmantot paroli, kuru varat viegli atcerēties. Pretējā gadījumā ikviens var to atbloķēt un mainīt apmeklējumu darbgrāmatu.

Tagad, ja jūs mēģināt mainīt neapmeklēšanas šūnas, Excel neļaus jums to darīt. Tomēr jūs varat mainīt apmeklējuma šūnas, jo esam tās neaizsargājuši.

6. darbība. Veiciet iepriekš minēto procedūru visām mēneša lapām

Dariet to pašu katrai mēneša lapai. Labākais veids ir kopēt to pašu lapu un izgatavot no tās 12 lapas. Noņemiet to aizsardzību un veiciet nepieciešamās izmaiņas un pēc tam vēlreiz aizsargājiet tās.

Sagatavojiet apmeklējuma galveno lapu

Lai gan visas lapas ir gatavas izmantošanai apmeklējumu aizpildīšanai, mums nav vienas vietas, kur tās visas uzraudzīt.

Administrācija vēlētos redzēt visu apmeklējumu vienā vietā, nevis uz dažādām lapām. Mums ir jāizveido galvenā apmeklējuma lapa.

7. darbība. Sagatavojiet galveno tabulu, lai pārraudzītu apmeklējumu vienā Excel vietā

Lai to izdarītu, sagatavojiet tabulu, kurā rindas virsrakstos ir komandas biedru vārds un sleju virsrakstos - mēneša nosaukums. Skatiet attēlu zemāk.

7. darbība. Meklējiet komandas apmeklējumu no katra mēneša lapas

Lai aplūkotu apmeklējumu no lapas, mums var būt vienkārša VLOOKUP formula, bet tad mums tas būs jādara 12 reizes katrai lapai. Bet jūs zināt, ka mums var būt viena formula, ko meklēt no vairākām lapām.

Izmantojiet šo formulu šūnā C3 un kopējiet pārējās lapas.

= MEKLĒŠANA ($ A3, NETIEŠA (C $ 2 un "! $ A $ 3: $ B $ 12"), 2,0)

Tā kā mēs zinām, ka visas lapas kopējais apmeklējums ir diapazonā B3: B12, mēs izmantojam funkciju INDIRECT, lai iegūtu vērtības no vairākām lapām. Kopējot šo formulu pa labi, tā februāra lapās meklē vērtības.

Uzmanību: pārliecinieties, vai lapu nosaukumi un kolonnas virsraksti galvenajā kartē ir vienādi, pretējā gadījumā šī formula nedarbosies.

8. solis: izmantojiet funkciju Summa, lai iegūtu visas pašreizējās komandas biedra gada dienas.

Tas nav obligāti. Ja vēlaties, varat aprēķināt savu darbinieku kopējo pašreizējo dienu visa gada garumā, vienkārši izmantojot summas formulu.

Un tas arī viss. Mums ir gatava Excel apmeklējumu pārvaldības sistēma. Jūs to varat mainīt atbilstoši savām prasībām. Izmantojiet to algas aprēķināšanai, stimulu aprēķināšanai vai jebko citu. Šis rīks jums nepievils.

Jūs varat veikt izmaiņas, lai katrā lapā atsevišķi aprēķinātu brīvdienas un nedēļas nogales. Pēc tam atņemiet tos no pašreizējām dienām, lai aprēķinātu kopējās darba dienas. Nolaižamajā izvēlnē varat iekļaut arī L atvaļinājumam, lai atzīmētu darbinieku atvaļinājumu.

Tātad, jā, puiši, šādā veidā jūs varat izveidot Excel apmeklējumu vadības sistēmu savam startam. Tas ir lēts un ļoti elastīgs. Es ceru, ka šī apmācība palīdzēs jums izveidot savu Excel apmeklējuma darbgrāmatu. Ja jums ir kādi jautājumi, dariet man to zināmu zemāk esošajā komentāru sadaļā.

Uzmeklēšana no mainīgajām tabulām, izmantojot netiešu: Lai meklētu tabulas mainīgo programmā Excel, mēs varam izmantot funkciju NETIEK. Funkcija NETIEŠA ņem teksta diapazonu un pārvērš to faktiskajā apmeklējumu diapazonā.

Izmantojiet INDEX un MATCH, lai atrastu vērtību: Formulu INDEX-MATCH izmanto, lai dinamiski un precīzi meklētu vērtību noteiktā tabulā. Šī ir alternatīva funkcijai VLOOKUP, un tā novērš funkcijas VLOOKUP trūkumus.

Izmantojiet VLOOKUP no divām vai vairākām uzmeklēšanas tabulām | Lai meklētu no vairākām tabulām, mēs varam izmantot IFERROR pieeju. Lai meklētu informāciju no vairākām tabulām, kļūda tiek uzskatīta par nākamās tabulas slēdzi. Vēl viena metode var būt If pieeja.

Kā veikt reģistrjutīgu meklēšanu programmā Excel | Excel VLOOKUP funkcija nav reģistrjutīga, un tā atgriezīs pirmo atbilstošo vērtību no saraksta. INDEX-MATCH nav izņēmums, taču to var mainīt, lai padarītu reģistrjutīgu. Redzēsim, kā…

Meklējiet bieži parādīto tekstu ar kritērijiem programmā Excel | Meklēšana visbiežāk parādās tekstā diapazonā, kurā izmantojam funkciju INDEX-MATCH with MODE. Lūk, metode.

Populāri raksti:

50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.

Kā izmantot Excel VLOOKUP funkciju| Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.

Kā lietot Excel Funkcija COUNTIF| Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.

Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.