Защо да купувате скъп инструмент за управление на посещаемостта за вашето стартиране, ако можете да проследите посещаемостта на екипа в Excel? Да! Можете лесно да създадете тракер за посещаемост в Excel. В тази статия ще научим как да го направим.
Стъпка 1: Създайте 12 листа за всеки месец в работна книга
Ако планирате да проследявате посещаемостта за една година, ще трябва да създадете всеки месец лист в Excel.
Стъпка 2: Добавете колони за всяка дата в листа на всеки месец.
Сега създайте таблица, която съдържа имената на вашите съотборници, колона за суми и 30 (или брой дни в месеца) колони с дата и ден от седмицата като заглавия на колони.
За да получите името на делничния ден, можете да потърсите календара или да използвате формулата, за да го копирате в останалите клетки.
| = ТЕКСТ (дата, "ddd") |
Можете да прочетете за това тук.
Форматирайте уикендите и празниците на тъмно и ги напълнете с фиксирани стойности като Уикенд/Празник, както е показано на изображението по -долу.
Направете същото за всеки лист.
Стъпка 3. Поправете възможните входове, като използвате валидиране на данни за всяка отворена клетка.
Сега всеки може да вмъкне присъствието си в листа, но може да въведе произволен текст. Някои могат да напишат P за настояще, или Настоящо, или за и т.н. Еднородността на данните е задължителна във всяка система за управление на присъствието.
За да позволим на потребителите да пишат само P или A съответно за настоящи и отсъстващи, можем да използваме валидиране на данни.
Изберете която и да е клетка, отидете на данни в лентата и кликнете върху валидирането на данните. Изберете списък от опциите и напишете A, P в текстовото поле.
Натиснете OK.
Копирайте това валидиране за целия отворен диапазон от данни (отворен диапазон означава клетка, в която потребителят може да вмъква стойности).
Стъпка 3: Заключете всички клетки, с изключение на местата, където трябва да се въведе присъствие.
Изберете дата колона с дата. Например, изберете 1-януари. Щракнете с десния бутон върху избрания диапазон и отидете на форматирането на клетката. Отидете на защита. Премахнете отметката от заключеното квадратче. Натиснете OK. Сега копирайте този диапазон във всички отворени периоди.
Това ще позволи влизане в тези клетки само когато защитим работните листове с помощта на менюто за защита на работни листове. По този начин вашите формули, форматиране ще бъдат непокътнати и потребителите могат само да променят посещаемостта си.
Стъпка 4: Изчислете настоящите дни на съотборниците
И така, как изчислявате настоящите дни? Е, всеки има свои собствени формули за изчисляване на посещаемостта. Тук ще обсъдя моето. Можете да правите промени според изискванията на листа за присъствие.
Отчитам общия брой на настоящите дни като общо дни в месеца, минус броя на отсъстващите дни. Това ще запази празниците и уикендите под контрол. Те автоматично ще се отчитат като работни дни.
Така че формулата на Excel за преброяване на настоящите дни ще бъде като:
| = COUNT (дати) -COUNTIF (посещаемост_, "A") |
Това по подразбиране ще задържи всички присъстващи през целия месец, докато не ги маркирате отсъстващи на листа.
В примера формулата е:
| = COUNT ($ C $ 2: $ AG $ 2) -COUNTIF (C3: AG3, "A") |
Написах тази формула в клетка В3 и след това я копирах. Можете да видите, че 27 дни се показват като подарък. Въпреки че не съм попълнил всички клетки за присъствие. Можете да го запазите по този начин, ако искате те да присъстват по подразбиране. Или ако искате те по подразбиране да отсъстват, проверете всички клетки като отсъстващи. Това ще запази само настоящите дни в настоящото изчисление.
Стъпка 5: Защитете листа
Сега, когато направихме всичко на този лист. Нека го защитим, така че никой да не може да промени формулата или форматирането на листа.
Отидете на раздела за преглед на лентата. Намерете менюто Protect Sheet. Щракнете върху него. Ще се отвори диалогов прозорец, който ще поиска разрешенията, които искате да дадете на потребителите. Проверете всички разрешения, които искате да разрешите. Искам само потребителят да може да запълни присъствието с нищо друго. Така че ще го запазя такъв, какъвто е.
Трябва да използвате парола, която лесно да запомните. В противен случай всеки може да го отключи и да промени работната тетрадка за присъствие.
Сега, ако се опитате да промените клетките, които не присъстват, Excel няма да ви позволи да го направите. Можете обаче да промените клетките за присъствие, тъй като сме ги защитили.
Стъпка 6: Направете горната процедура за всички месечни листове
Направете същото за всеки месечен лист. Най -добрият начин е да копирате същия лист и да направите 12 листа от него. Премахнете защитата от тях и направете необходимите промени и след това ги защитете отново.
Подгответе основния лист за присъствие
Въпреки че имаме всички листове, готови да се използват за попълване на присъстващите, нямаме едно място, където да ги наблюдаваме всички.
Администрацията би искала да види цялото присъствие на едно място, вместо на различни листове. Трябва да създадем главен лист за присъствие.
Стъпка 7: Подгответе главна таблица за наблюдение на посещаемостта на едно място в Excel
За целта подгответе таблица, която съдържа името на съотборниците като заглавия на редове и името на месеца като заглавия на колони. Вижте изображението по -долу.
Стъпка 7: Потърсете присъствието на екипа от всеки месечен лист
За да потърсим посещаемостта от листа, можем да имаме проста формула VLOOKUP, но след това ще трябва да го направим 12 пъти за всеки лист. Но знаете, че можем да имаме една формула за търсене от няколко листа.
Използвайте тази формула в клетка С3 и копирайте в останалите листове.
| = VLOOKUP ($ A3, INDIRECT (C $ 2 & "! $ A $ 3: $ B $ 12"), 2,0) |
Тъй като знаем, че всички листове имат обща посещаемост в диапазон B3: B12, използваме функцията INDIRECT за извличане на стойности от няколко листа. Когато копирате тази формула вдясно, тя търси стойности в листата за февруари.
Внимание: уверете се, че имената на листовете и заглавията на колоните в главната страница са еднакви, в противен случай тази формула няма да работи.
Стъпка 8: Използвайте сумата, за да получите всички настоящи дни от годината на съотборник.
Това е по избор. Ако искате, можете да изчислите общите настоящи дни на вашите служители през цялата година, като просто използвате формулата за сумата.
И това е всичко. Имаме готова нашата система за управление на присъствието в Excel. Можете да промените това според вашите изисквания. Използвайте го за изчисляване на заплатата, стимулиране или нещо друго. Този инструмент няма да ви подведе.
Можете да направите промени, за да изчислите празниците и уикендите отделно във всеки лист. След това ги извадете от общите настоящи дни, за да изчислите общите работни дни. Можете също да включите L за отпуск в падащото меню, за да маркирате отпуск на служителите.
Така че да, момчета, ето как можете да създадете система за управление на посещаемостта на Excel за вашето стартиране. Той е евтин и много гъвкав. Надявам се този урок да ви помогне да създадете своя собствена работна книга за Excel. Ако имате въпроси, уведомете ме в секцията за коментари по -долу.
Търсене от променливи таблици, използвайки INDIRECT: За да потърсите от променлива на таблица в Excel, можем да използваме функцията INDIRECT. Функцията INDIRECT ще вземе текстовия диапазон и ще го преобразува в действителния диапазон на посещаемост.
Използвайте INDEX и MATCH за търсене на стойност: Формулата INDEX-MATCH се използва за динамично и точно търсене на стойност в дадена таблица. Това е алтернатива на функцията VLOOKUP и тя преодолява недостатъците на функцията VLOOKUP.
Използвайте VLOOKUP от две или повече таблици за търсене | За търсене от множество таблици можем да използваме подход IFERROR. За да търсите от няколко таблици, грешката се приема като превключвател за следващата таблица. Друг метод може да бъде подход If.
Как да извършите търсене с чувствителност към регистъра в Excel | функцията VLOOKUP на excel не е чувствителна към регистъра и ще върне първата съвпадаща стойност от списъка. INDEX-MATCH не прави изключение, но може да бъде променен, за да направи чувствителен регистъра му. Да видим как…
Търсене на често срещан текст с критерии в Excel | Търсенето най-често се появява в текст в диапазон, който използваме функцията INDEX-MATCH с MODE. Ето метода.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.