Определяне на последователни дни с Excel

Всеки месец много ЧР специалисти и счетоводители трябва да попълват разни справки с последователни дни отсъствие от работа на служителите. Тази статия по действителен случай може да им бъде от голяма полза.

Казусът с определяне на последователни дни

На долната картинка имаме една справка, която излиза от някакъв софтуер, за това на кои дати различни служители са отсъствали от работа и по каква причина – отпуск, болничен или обучение (vacation, sickness, training).

последователни дни

Целта е да обобщим тази информация по този начин:

последователни дни

Принципно, най-доброто решение е да си поискаме директно готовата справка от системата. Но в конкретния случай това не е опция.

За да го направим в Ексел, важни са следните особености:

  • Трябва да изчислим брой последователни работни дни, а не календарни. Например служителят с колоритното име Ганьо Балкански не е бил на работа в периода 14-18 Октомври. Това са 5 календарни дни, но само 3 работни.
  • Напълно е възможно един служител да отсъства по няколко пъти в един месец за различни периоди.
  • Същият Ганьо Балкански е бил в отпуск и на 31 Октомври.
  • Освен това, често служителите могат да отсъстват по различни причини в рамките на един и същ месец. Вижте служителя с изключително рядко срещаното име Иван Иванов как е „ваканцувал“ на 3-ти е 4-ти, но от 17-ти до 19-ти е бил болен.
  • Понякога в един общ период последователни дни, причината за отсъствието е различна. Калоян Иванов, например, след като е бил в отпуска от 5-ти до 14-ти, в следващите два работни дни (17-18) е бил на обучение. Да се аклиматизира човекът.

Големият проблем

Трудността при коректното определяне на всички последователни дни за всеки служител е това, че той може да е ползвал няколко пъти в рамките на месеца (тримесечието или годината) един и същи вид отсъствие. Така намирането на първата и последна дата и преброяването на дните между тях се обърква. Дали със SUMIF, дали с пивот таблица – винаги излиза за Ганьо Балкански например, че не е бил на работа 4 дни от 14 до 31 Октомври.

Решението на този проблем, всъщност е изненадващо лесно. За да можем да определим началото, края и броя последователни дни за всеки период на отсъствие, трябва да добавим една колона с уникален идентификатор на това отсъствие. А най-простият уникален идентификатор се постига с един брояч (индекс), който започва от 1 и нараства с 1 за всеки нов ред. Ето как изглеждат нещата:

последователни дни

Добавени са две колони (оцветени в червено, за да се знае, че не са оригиналните данни, а допълнение към тях). В първата колона просто броим от 1 до колкото редове има. Във втората колона с CONCATENATE правим уникален код (label), който е съчетание от името, индекса и типа отсъствие.

Всъщност е малко по-сложно.

Няма да отегчавам читателя с обяснение на формулата. В края на статията има линк, от който може да свалите файла и да я разгледате. Но накратко – тя проверява две неща. Първо дали служителят и типа отсъствие на даден ред са различни от горния. Второ – ако не са различни – дали работните дни между долния и горния ред са повече от 2. Ако поне едно от тези две условия е изпълнено, създава код като „слепва“ името, индекса и вида отсъствие. В противен случай – взима кода от горния ред.

А за определяне на броя на работните дни между две дати се използва функцията NETWORKDAYS.

 Създаване на справката, чрез пивот таблица

След като сме свършили тежката работа с помощта на индекса вече има няколко подхода, с които да създадем справката, която ни е необходима. Аз ще покажа как става бързо и лесно с пивот таблица. Винаги предпочитам такива обобщаващи справки да ги правя с пивот таблица, защото тя дава гъвкавост и при смяна на данните се обновява само с натискането на бутона Refresh.

След като създадем пивот таблица (ако не знаете как става – вижте тук), поставяме в редовете Label, Employee name и Event. А във Values – три пъти Date. Няма грешка – три пъти трябва да ги поставим и ако сме рабитили правилно – ще видим това:

последователни дни

Сега кликаме с десен бутон на мишката някъде в колоната с първата дата и избираме Summarize Values By и след това – Min.

Какво постигаме с това? Казваме на Ексел в тази колона да показва най-малката дата за съответния Label. А това всъщност е датата, от която започва то, нъл тъй?

Резултатът обаче, няма да ви очарова

Но спокойно, няма грешка. Помнете, че в Ексел датите всъщност са поредни числа като 1 е 1 Януари 1900 година. А 14 Октомври 2022 е точно 44848 дни след това.

За да оправим формата: отново кликаме с десен бутон някъде в колоната и избираме Number Format

А след това някой подходящ формат за дата

Така вече изглежда по-добре

Повтаряме същата процедура и за втората колона, само че вече изираме MAX. Ако сме работили правилно, трябва да виждаме това:

И това по същество е точно справката за последователни дни отсъствия на всеки служител, която ни е необходима. Можем да я ползваме така, а може просто с copy/paste да поставим данните, където е необходимо.

Заключителни думи

Най-забавната част в този казус беше, че колегата счетоводител, който помоли за помощ твърдеше, че той е нерешим според него и още трима други Ексел специалисти във фирмата. Но както виждате в Excel невъзможно е нищо. Просто, когато един казус се закучи, дайте една стъпка назад и се запитайте – каква е причината. В случая „пробивът“ в решението дойде с осъзнаването, че не разполагаме с уникален идентификатор на всеки отделен период на отсъствие. Добавянето на един прост индекс осигури тази уникалност.

И ако трябва да сме докрай прецизни, справката има един бъг. Вижте по-внимателно какво се случва при Пенка Иванова:

По-късната дата е отгоре. Има решение на този проблем и то е показано във файла, който може да изтеглите от тук.

Публикувано в Формули. Постоянна връзка.