Намирането на датата на предходното събитие и изчисляване на разлика в дни между две събития може да бъде много ценен анализ в редица ситуации. Например:
- На коя дата е последната поръчка на клиент Х (съответно колко дни са изминали от тогава)?
- На коя дата е била последната смяна на маслото на автомобил?
- На коя дата ябълковата градина е пръскана с препарат У и колко дни са изминали от тогава, за да се направи следващата процедура?
- Каква е разликата в дни между запитването от клиент и поръчката?
- Колко е времето в дни от момента, в който кандидат е подал CV, до момента, в който постъпва на работа?
- Кога за последно ПФК Левски София е вкарал 4 гола на ЦСКА?
Примерите могат да бъдат безброй. В тази статия ще покажа няколко начина как с Ексел да намерим датата на предходното събитие и след това да изчислим каква разлика в дни има между двете.
Казусът
Имаме списък със събития и на коя дата са се случили.
Искаме за всяко събитие да намерим датата на предходното случване (ако има такава). Ако няма – клетката да остане празна (в зависимост от целите може да бъде и нула или да излиза съобщение като „няма предходно събитие“). След като намерим датата – трябва да изчислим разликата с предходното случване.
След това в зависимост от нуждите и целите можем да използваме тази разлика в дни по различен начин. Например да направим списък с всички клиенти, които не са правили поръчка през последните 90 дни или година и да им изпратим мейл с купон за отстъпка.
А може да зададем някакъв максимален брой дни, след който трябва да настъпи друго събитие. Например – най-късно 3 дни след запитване, клиентът трябва да получи отговор. Или ХХ дни след първото пръскане на ябълковите дръвчета, трябва да се направи второ.
Възможен е и вариант, при който искаме да анализираме т.нар. Outliers. Например да се провери досието и целият процес по подбор на кандидати, за които подборът е приключил за странно кратко време (под 5 дни) или пък за рекордно дълъг срок (повече от 6 месеца).
Без значение от целите и типа данни в крайна сметка всичко се свежда до три прости стъпки:
- Сортираме данните по събитие и после по дата във възходящ ред
- За всяко събитие намираме предходната дата на случване
- Правим проста разлика между датата на събитието и предходната дата на случване
1. Намиране на предходна дата с формула
Тук вече данните ни са сортирани както е нужно. Събитие А е най-напред, след това Събитие В, Събитие С и т.н. Когато едно събитие се е случило няколко пъти – датите са подредени във възходящ ред.
Сортирането е важно, за да можем да приложим проста формула с IF:
Какво прави тази формула:
Проверява дали събитието в клетка В1 е същото като събитието в клетка В2. Ако са еднакви – връща датата от клетка В1, т.е. предходната датата на която се е случило събитието от клетка В2. Точно това, което търсим.
Но ако събитията са различни, тогава ни връща символа тире („-“). Може да бъде всякакъв друг символ, числото нула или някакво съобщение.
Ето тук формулата е копирана надолу в колоната
Както виждате, резултатът не е много красив, затова трябва да променим формата на клетките. Аз предпочитам да го правя така:
По този начин положителните числа ще са с формат на дата „ДД.ММ.ГГГГ“, а всички останали – отрицателни числа, нула и текст, просто няма да се виждат.
След като сме намерили датата на предходното случване на всяко събитие, вече е фасулска работа да изчислим разликата в дни между двете дати:
Един прост IF проверява дали стойността в колона С е тире. Ако е, то значи няма дата на предходно събитие и тогава връща нула (или каквото си поискаме). Но ако не е тире, тогава значи ще е дата и следователно трябва да изчисли разликата между датите в колона А (текущата) и колона С (предходната дата).
Ето го и резултатът, след като клетките са форматирани по подходящ начин:
2. Намиране на разликата в дни с предходна дата с пивот таблица
Пивот таблиците са изключително мощен и гъвкав инструмент за анализ и това ясно ще си проличи като ви представя второто решение. Всъщност, ще използвам техника, за която и преди съм писал, но в нов контекст.
Като за начало, правим си една пивот таблица от изходните данни и по редовете разполагаме събитията и датите. Голямото удобство тук е, че данните автоматично се сортират по възходящ ред.
След това във Values слагаме отново датата. Да, това е възможно.
По някаква причина Ексел е решил, че трябва да брои датите (Count of дата). Може би защото няма много логика да събираш дати. Но в случая на нас ни трябва точно това – да събираме. Затова с кликаме един път с левия бутон на мишката върху „Count of дата“ и избираме Value Field Settings
Появява се меню, в което (1) избираме Sum и (2) отиваме на Show values As
В следващото меню избираме:
- От падащия списък на Show Value as: Difference from (разлика от, т.е. искаме Ексел да изчисли разликата между текущата дата и друга дата, която ще зададем в следващите две стъпки)
- В полето Base field: дата (защото търсим разликата от предишната дата)
- В полето Base item: previous – т.е. казваме на Ексел да сметне разликата от предходната дата
И когато дадем ОК – ето го резултатът
За проверка, нека да сравним с първото решение – резултатите са едни и същи (има си хас!)
Предимството на пивот таблицата е, че изчисленията са до голяма степен автоматизирани. Особено ако изходните данни са оформени като Ексел таблица (CTRL+T) – тогава когато добавим нови данни, трябва само да рефрешнем пивот таблицата и резултатът е готов.
Но има един недостатък – не ни показва коя е предходната дата, а само разликата с нея.
3. Други начини за намиране на предходна дата
Тук само ще отбележа, че има и други формули, с които можем да намерим предходната дата на случване на събитие. Ето пример с XLOOKUP:
И VLOOKUP:
Възможно е и с Power Query, както демонстрира в това видео Мат Алингтън.
Но принципът си остава същият – намираме датата на предходното събитие като сортираме и сравняваме с горния ред. След това да се намери разликата в дни е фасулска работа.