В поредицата Съвети и трикове в Excel предоставяме отговори на въпросите на участниците, свързани с Excel, които не са често срещани в учебната програма на нашите курсове. Създадохме мозайка от отговори, които биха могли да ви помогнат да работите с Excel и вие ...
Това е петата част, в която се занимаваме главно с функциите на времето, как да изчислим в Excel възрастта или продължителността на заетостта и т.н.
Срещали ли сте вече необходимостта да изчислите възрастта на човек с точност от години, месеци или дори с точност от един ден? Или със задачата да разбере дали родителят трябва да донесе удостоверение за посещение в училище, защото детето му е навършило 18 години? Как да изчислим продължителността на заетостта във фирмата? Колко дни ще отнеме на кораба да пътува от пристанище в Азия до Европа?
В Excel можем да направим това лесно и трудно, много точно и приблизително ... бързо и бавно 🙂
Функции за дата в Excel
Сред своите близо петстотин функции Excel има няколко, които ви помагат да изчислите дати и часове.
На снимката можете да видите в горната част преглед с описание на основните - ДЕН, МЕСЕЦ, ГОДИНА, СЕДМИЦА и ISOWEEKNUM.
Първите три функции връщат стойността за деня, месеца или годината от датата. Резултатът от WEEKNUM е серийният номер на седмицата от датата в посочената клетка. ISOWEEKNUM също връща поредния номер на седмицата от датата в референтната клетка, но за разлика от WEEKNUM, резултатът е номерът на седмицата според международния стандарт. На практика обаче другите две функции се използват повече.
Между другото - знаете ли, че броят на седмиците варира в света?
Има държави, които считат първата седмица от годината за тази на 1 януари. В същото време има страни, които считат първата седмица от годината за понеделник, вторник, сряда или четвъртък. Или когато празнуват Нова година с тях някъде през януари ... И така, коя е първата седмица от годината?
Международната организация по стандартизация въведе ISO 8601 в края на 80-те години. Първата седмица от годината на ISO е първият четвъртък от григорианската година, седмицата на 4 януари.
За какво е добре?
Например, ... износителят уведомява външнотърговския партньор, че стоките ще бъдат готови за изпращане от вторник на 23-та седмица. Той поръчва камион, кораб, самолет да транспортира стоката до склада му. И нищо. Камионът чака, те не могат да намерят пратката в склада. Телефонни обаждания, псувни, нервност. След час търсене информацията се оказва вярна, но новият работник използва местното обозначение на седмицата. Не му е хрумнало, че една седмица в чужбина може да бъде отбелязана по различен начин (собственият опит на автора с износителя). Мога.
Как да изчислим възрастта в Excel
Нека разгледаме как да изчислим датата в Excel на конкретни примери.
Интересуваме се - най-вече за нуждите на различни административни задачи в работата - колко години има човек (работник, дете, ...) до сегашното „днес“. Интересуваме ли се как да изчислим възрастта в Excel? Брой отработени години?
Не е трудно, ако знаем правилните функции или знаем как Excel работи с дати.
Да предположим, че хората, които ще разберем на възрастта, сме подредили в таблицата, както е на снимката.
Метод 1
Excel ни позволява да открием простата разлика между две дати колко дни са изминали между двете дати. Следователно бихме могли да напишем текущата дата днес в съседната колона до колоната за дата на раждане (сега неправилно обозначена като Възраст). И ще пишем в следващата колона формула, по която изчисляваме разликата между две клетки с дати в реда до. Така го правят начинаещите, в самото начало на разчитането на Excel.
Резултатът е правилен, но ...
(Ако в колоната се появи дата, която не говори, вместо броя на дните, не се отчайвайте. Проблемът е само във формат на клетка. Задайте селекцията в горния прозорец на раздела Начало в панела Число на Общо.)
Полученият резултат има един недостатък - той ще бъде валиден само на датата 7.2.2020. Следователно едно малко по-напреднало „съвършенство“ ще разгледа формула с функция, която би улеснила изчислението.
За да не бъдем обвързани само с една дата „днес“, която въведохме в колона D в предишния метод на изчисление, ще използваме друга функция в изчислението във формулата. Функция за дата ДНЕС в Excel винаги използва текущата дата от календара на компютъра. Така че изобщо не трябва да записваме текущата дата в таблицата. Изчислението ще се извършва всеки път, когато отворим файла, до новата текуща "днешна" дата. Добре обаче?
Функцията изглежда малко странно при писане, защото е без аргументи - няма аргумент в скоби. Формулата с нея изглежда така в клетка:
Не пишем нищо във функцията, няма място. Само лява и дясна скоба.
ДНЕС ще ни покаже резултата в клетката под формата на текущата дата.
В нашия пример ще оценим функцията, тъй като след използването й решението изглежда така.
Вместо датата въведете формулата за разлика в първата клетка на колона D (D7):
и го копирайте в клетките отдолу, като щракнете двукратно върху квадратната дръжка в ъгъла на клетката.
(Както при предишната процедура, ако в колоната вместо броя дни се появи дата, която не говори, проблемът е само във формат на клетка. Задайте избрания формат в горното поле в раздела Начало вместо Дата на Общи. )
Ето как получихме броя дни от живота на работника. Ако искаме няколко години, все пак трябва да превърнем резултата в години. Как?
Тъй като всяка четвърта година е пропусклива, разделяме броя на придобития ден от живота на 365,25. Получаваме доста точна възраст на всички служители.
Тогава цялата формула в първата клетка изглежда така:
Щракнете двукратно върху дръжката, за да я копирате в цялата колона.
Разлика между две дати в години разбрахме по този начин чрез формула с функцията ДНЕС.
Той изчислява разликата между две клетки с дати.
Можем да покажем резултата само за цели години, като използваме функцията INT (цяло число), която осигурява показването само на целочислената част. Изглежда така:
Забележка:
В случай, че трябва да изчислим възрастта за определен ден, например към 31.12.2019 г., вместо ДНЕС ще напишем конкретна дата във формуляра във формуляра ДАТА (2019; 12; 31).
Метод 2
За изчислението ще използваме друга функция за дата на Excel - YEARFRAC. Функцията YEARFRAC има два задължителни аргумента (начална и крайна дата) и незадължителен аргумент според съответната финансова операция (не се използва често).
Ще направим изчислението в страничната колона, за да можем да сравним резултатите. Първата клетка ще изглежда така:
Щракнете двукратно върху квадратната дръжка, за да я копирате в останалата част на колоната.
Открихме разликата между двете дати, използвайки функцията YEARFRAC, чрез което изчисляваме разликата на две клетки с дати.
Когато сравняваме двете колони Възраст, виждаме разлика. Възрастта, изчислена чрез разделяне и разделяне на 365,25, не е напълно точна. Резултатът, получен от функцията YEARFRAC, е точен с правилно въведени входни стойности (в изключителни случаи на отчитане на банкови години 30/360 с начална дата 29.2. Може да върне неправилни резултати).
Ако се интересуваме само от броя години, и двете изчисления са достатъчни.
Ако трябва да изчислим разликата в датите за определен ден, например към 31.12.2019 г., дори когато използваме функцията YEARFRAC вместо TODAY (), пишем конкретна дата под формата на DATE (2019; 12; 31 ) вътре в него.
(Последният ред показва формулите, използвани в клетките в маркираната клетка в колоната над тях.)
Как да изчислим продължителността на трудовото правоотношение
Изчисляваме продължителността на трудовото правоотношение в Excel, подобно на предишния случай.
Погледнете таблицата.
Вместо датата на раждане въвеждаме или копираме от друга таблица датите на влизане на служителите в работа. Процедурата и логиката за създаване на формула е подобна.
Извършваме изчислението, като използваме функцията YEARFRAC в колона G.
Първата клетка ще изглежда така:
Щракнете двукратно върху квадратния маркер в ъгъла на клетката, за да копирате формулата в останалата част на колоната.
Ако имате нужда само от години или не харесвате броя на десетичните знаци, можете да го коригирате с помощта на инструментите със стрелки в раздела Начало в панела Числа (Формат на числата). Добавете и извадете броя на показаните десетични знаци. Или можем да използваме функцията INT, спомената по-горе директно във формулата, както следва:
В случай, че трябва да преброим броя на годините, отработени в определен ден, например на 31.12.2019 г., вместо ДНЕС (), ние пишем конкретна дата във формата DATE (2019; 12; 31) във функцията, така че формулата ще изглежда така:
Разбира се, можем да изчислим и продължителността на трудовото правоотношение по първия начин, описан по-горе - чрез изваждане на две дати и разделяне на разликата на 365,25. Резултатът ще бъде по-малко точен от косата от функцията YEARFRAC.
За ценителите в Excel
Изчисляването на възрастта или периода може да има много форми в зависимост от изискванията за показване на резултата.
Формулата за просто показване на броя години (преживявания, отработени часове, ...) към днешна дата може да бъде следната:
където клетка C3 ще бъде датата, от която броим годините, т.е. дата на раждане, дата на започване на работа, ...
Формулата за по-точно представяне на изчислението на години, месеци и дни може да изглежда така:
Няколко метода за изчисление, включително проблеми с употребата на словесни словашки думи година, години, години, ... дори с английската версия могат да се видят на снимката по-долу. Ако някой иска да тества тези формули или да ги използва на практика, можете да изтеглите файла с електронната таблица на Excel на вашия компютър, като кликнете върху бутона в края на статията.
Когато изчислявах в тази формула, използвах по-малко известната функция за дата на Excel DATEIF.
Можете да намерите по-подробното му описание от 2019 г. в помощта на Excel (клавиш F1).
Заключение
Както се вижда от последната таблица, има няколко решения при изчисляването на датата.
За практическа употреба препоръчвам - изберете този, който най-добре разбирате. За да можете лесно да го интерпретирате или коригирате по всяко време.
Същите принципи и формули могат да се използват на практика, например, за да се определи дали родителят трябва да предостави на работодателя удостоверение за посещение в училище (във формулата използваме датата на раждане и ДНЕС или други практически задачи. Excel ще улесни живота ни със своите функции 🙂
Искате ли да научите повече за работата с Excel и функциите за време? Елате на курс на Excel, научете и опитайте примери. След два дни, прекарани в курса на Excel I или Excel II, ще знаете повече за работата с тази електронна таблица, ще научите клавишни комбинации, начини за различаване на типовете електронни таблици и кога да ги използвате, научете как да създавате формули, да използвате функции,.
Ще знаете как да изчислите датата след Excel II, ние поемаме функциите за време по-подробно в Excel III 🙂