Определяне на броя на дните в месеца в Microsoft Excel. Как да изчислим броя на дните в него с помощта на една математическа формула въз основа на номера на месеца? Как да броим месец
За да разрешите някои проблеми, когато създавате таблица, трябва да посочите броя на дните в месеца в отделна клетка или във формула, така че програмата да може да извърши необходимите изчисления. Excel има инструменти, предназначени за извършване на тази операция. нека помислим различни начиниизползване на тази функция.
Можете да изчислите броя на дните в месеца в Excel, като използвате оператори за специални категории "Дата и час". За да разберете коя опция е най-добре да използвате, първо трябва да установите целите на операцията. В зависимост от това резултатът от изчислението може да се покаже в отделен елементна лист и може да се използва и в друга формула.
Метод 1: Комбинация от оператори DAY и MONTH
Повечето по прост начинза решаване на този проблем е комбинация от оператори ДЕНИ EON-МЕСЕЦ.
функция ДЕНпринадлежи към групата оператори "Дата и час". Сочи конкретен номер от 1 преди 31 . В нашия случай задачата на този оператор ще бъде да посочи последния ден от месеца, като използва вградената функция като аргумент EON-МЕСЕЦ.
Синтаксис на оператора ДЕНследващия:
ДЕН(дата_в_числов_формат)
Тоест, единственият аргумент на тази функция е „Дата в цифров формат“. Ще се задава от оператора EON-МЕСЕЦ. Трябва да се каже, че датата в цифров формат се различава от обичайния формат. Например дата 04.05.2017 в цифрова форма ще изглежда така 42859 . Следователно Excel използва този формат само за вътрешни операции. Рядко се използва за показване в клетки
Оператор EON-МЕСЕЦима за цел да посочи поредния номер на последния ден от месеца, който е определен брой месеци напред или назад от определената дата. Синтаксисът на функцията е:
EON-MONTH(начална_дата, брой_месеци)
Оператор "Начална дата"съдържа датата, от която се брои, или връзка към клетката, в която се намира.
Оператор "Брой месеци"показва броя на месеците, до които да се брои от дадена дата.
Сега нека видим как работи нататък конкретен пример. За да направите това, нека вземем Excel лист, в една от клетките на който е въведена определена календарна дата. Необходимо е, като използвате горния набор от оператори, да определите колко дни има в месечния период, за който се отнася това число.
Нашата обща формула прие следната форма:
ДЕН(МЕСЕЦ(B3,0))
В тази формула единствената стойност на променливата е адресът на клетката ( B3). По този начин, ако не искате да извършите процедурата чрез Помощници за функции, можете да вмъкнете тази формула във всеки елемент от листа, като просто замените адреса на клетката, съдържаща числото, с този, който е подходящ за вашия конкретен случай. Резултатът ще бъде подобен.
Метод 2: Автоматично откриване на броя дни
Сега нека разгледаме друг проблем. Изисква се броят на дните да се показва не според дадена календарна дата, а според текущата. Освен това смяната на периодите ще се извършва автоматично без намеса на потребителя. Въпреки че изглежда странно, тази задача е по-лесна от предишната. За да го решите, дори отворете Съветник за функциине е необходимо, защото формулата, която изпълнява тази операция, не съдържа стойности на променливи или препратки към клетки. Можете просто да въведете следната формула без промени в клетката на листа, където искате да се покаже резултатът:
ДЕН(МЕСЕЦ(ДНЕС(),0))
Вградената функция TODAY, която използвахме в в такъв случай, показва днешната дата и няма аргументи. Така вашата клетка постоянно ще показва броя на дните в текущия месец.
Метод 3: Изчислете броя на дните, които да използвате в сложни формули
В примерите по-горе показахме как да изчислим броя на дните в месеца въз основа на определена календарна дата или автоматично въз основа на текущия месец, показвайки резултата в отделна клетка. Но намирането на тази стойност може да е необходимо и за изчисляване на други показатели. В този случай изчисляването на броя на дните ще се извърши в сложна формула и няма да се показва в отделна клетка. Нека да видим как да направим това с пример.
Трябва да сме сигурни, че клетката показва броя на оставащите дни до края текущ месец. Както и в предишния метод, тази опция не изисква отваряне Помощници за функции. Можете просто да въведете следния израз в клетката:
ДЕН(МЕСЕЦ(ДНЕС(),0))-ДЕН(ДНЕС())
След това броят на дните до края на месеца ще се покаже в посочената клетка. Всеки ден резултатът ще се актуализира автоматично и от началото на новия период обратното броене ще започне наново. Оказва се един вид таймер за обратно броене.
Както можете да видите, тази формула се състои от две части. Първият от тях е вече познат израз за изчисляване на броя на дните в месеца:
ДЕН(МЕСЕЦ(ДНЕС(),0))
Но във втората част днешното число се изважда от този показател:
ДЕН ЗА ДЕН())
По този начин, когато се извършва това изчисление, формулата за изчисляване на броя на дните е интегрална частпо-сложна формула.
Метод 4: Алтернативна формула
Но, за съжаление, версиите на програмата, по-ранни от Excel 2007, нямат оператора EON-МЕСЕЦ. Какво ще кажете за онези потребители, които използват по-стари версии на приложението? За тях тази възможност съществува чрез друга формула, която е по-масивна от описаната по-горе. Нека да видим как да изчислим броя на дните в месеца въз основа на дадена календарна дата, използвайки тази опция.
- Изберете клетката за показване на резултата и отидете до прозореца с аргументи на оператора ДЕНпо вече познат ни начин. Поставете курсора в единственото поле на този прозорец и щракнете върху обърнатия триъгълник отляво на лентата с формули. Отидете в секцията „Други функции...“.
- В прозореца Помощници за функциив група "Дата и час"подчертайте името „ДАТА НА“и натиснете бутона "ДОБРЕ".
- Отваря се прозорецът на оператора ДАТА. Тази функция преобразува дата от нормален форматв числова стойност, която след това операторът ще трябва да обработи ДЕН.
Прозорецът, който се отваря има три полета. В полето "ден"можете веднага да въведете номера "1". Това ще бъде същото действие за всяка ситуация. Но другите две полета ще трябва да бъдат обработени задълбочено.
Поставете курсора в полето "година". След това преминаваме към избор на оператори чрез познатия триъгълник.
- Всички в една и съща категория Помощници за функцииподчертайте името "ГОДИНА"и щракнете върху бутона "ДОБРЕ".
- Отваря се прозорецът с аргументи на оператора ГОДИНА. Той определя годината въз основа на посоченото число. В едно поле на прозорец „Дата в цифров формат“Посочваме връзка към клетката, съдържаща първоначалната дата, за която трябва да определим броя на дните. След това не бързайте да щракнете върху бутона "ДОБРЕ"и щракнете върху името „ДАТА НА“в лентата с формули.
- След това се връщаме отново в прозореца с аргументи ДАТА. Поставете курсора в полето "месец"и преминете към избор на функции.
- IN Съветник за функциищракнете върху името "МЕСЕЦ"и натиснете бутона "ДОБРЕ".
- Отваря се прозорецът с аргументи на функцията МЕСЕЦ. Задачите му са подобни на предишния оператор, само че показва стойността на номера на месеца. В единственото поле на този прозорец задаваме същата връзка към оригиналния номер. След това в лентата с формули щракнете върху името "ДЕН".
- Връщане към прозореца с аргументи ДЕН. Тук трябва да направим само едно малко докосване. В единственото поле на прозореца, което вече съдържа данни, добавете израза в края на формулата "-1"без кавички и също поставете „+1“ след оператора МЕСЕЦ. След това щракнете върху бутона "ДОБРЕ".
- Както можете да видите, избраната преди това клетка показва броя на дните в месеца, към който принадлежи посочената дата. Общата формула изглежда така:
ДЕН(ДАТА(ГОДИНА(D3),МЕСЕЦ(D3)+1,1)-1)
Тайната на тази формула е проста. Използваме го, за да определим датата на първия ден от следващия период и след това изваждаме един ден от него, получавайки броя на дните в посочения месец. Променливата в тази формула е препратката към клетката D3на две места. Ако го замените с адреса на клетката, в която се намира датата във вашия конкретен случай, тогава можете просто да въведете този израз във всеки елемент на листа без никаква помощ Помощници за функции.
Както можете да видите, има няколко опции за намиране на броя на дните в месеца в Excel. Коя да използвате зависи от крайната цел на потребителя, както и от това коя версия на програмата използва.
Забележка: тази публикация е превод на статията cmcenroe.me/2014/12/05/days-in-month-formula.html ( Част I), както и авторското допълнение към него ( Част II). Материалът не трябва да се приема на сериозно, а по-скоро като умствено упражнение, което изисква не повече от училищни познания по аритметика и няма практическо приложение. Приятно четене на всички!
Част I
Въведение
Наскоро, след поредната безсънна нощ, си мислех за методите за запомняне на броя на дните във всеки месец от годината. Има рима за броене за това, както и начин да разчитате на кокалчетата си, но нито едното, нито другото ме устройваха. Чудех се дали може да има някаква математическа формула за решаване на такъв проблем и - тъй като не намерих такава по време на бегло проучване - предизвиках себе си да я създам.Формализиране С други думи, необходимо е да се намери функцията f, така че стойността f(x)за всеки месец х, представено с число от 1 до 12, е равно на броя на дните в този месец. Таблица със стойности на аргументи и функции:
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
Ако сте се изкушили да опитате сами, преди да прочетете моето решение, сега е моментът. Ако предпочитате веднага да видите готовия отговор, погледнете под спойлера.
Отговор
По-долу са моите стъпки за намиране на решение.
Математически апарат
Първо, нека накратко опресним паметта си за два жизненоважни оператора при решаването на този проблем: целочислено деление и остатък от деление.Целочислено делениеТова е оператор, използван в много езици за програмиране за разделяне на две цели числа и премахване на дробната част от частното. Ще го представя като. Например:
Остатък от делениетоТова е оператор, който намира остатъка от делението. Много езици за програмиране използват символа %
, ще използвам конструкции като например:
Имайте предвид, че останалата част от разделението има равен приоритет с разделянето.
Основи
И така, нека използваме нашия математически апарат, за да получим основната формула. Нормалният месец има 30 или 31 дни, така че можем да редуваме между 1 и 0 и след това просто да добавим константа към това число:Получаваме таблица с правилните стойности, подчертани с удебелен шрифт:
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x) | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 |
Не е лошо начало! Вече има коректни стойности за януари и за месеците от март до юли включително. февруари - специален случай, и ще се занимаем с него малко по-късно. След юли, за останалите месеци редът на получаване на 0 и 1 трябва да се обърне.
За да направим това, можем да добавим 1 към дивидента:
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x) | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 30 | 31 |
Стойностите за август до декември вече са правилни, но както се очаква, стойностите за останалите месеци са неправилни. Нека да видим как можем да комбинираме тези формули.
Наслагване на маска
Това изисква функция на части, но - тъй като това ми се стори скучно - се сетих за друго решение, използвайки една част от функцията на един интервал, другата на друг.Вярвам, че най-лесният начин би бил да се намери израз, който е равен на 1 в една област на приложение и 0 в останалите. Методът, при който чрез умножаване на аргумент по израз, ние го изключваме от формулата извън неговия обхват, нарекох „налагане на маска“, защото това поведение е подобно на един вид битова маска.
За да използваме този метод, в последната част на нашата функция трябва да намерим израз, равен на 1, когато , и - тъй като стойностите на аргумента са винаги по-малки от 16 - целочисленото деление на 8 работи добре за това.
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ⌊ х ⁄ 8 ⌋ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 |
Сега, използвайки тази маска, използвайки израз вместо 1 в дивидента, можем да обърнем реда на получаване на 0 и 1 във формулата:
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x) | 31 | 30 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
Еврика! Всичко е точно, с изключение на февруари. Изненадваща изненада.
февруари
Всеки месец има 30 или 31 дни, с изключение на февруари с неговите 28 (високосните години са извън обхвата на тази задача). В момента, според нашата формула, той има 30 дни, така че би било хубаво да извадим израза, равен на 2 при .Най-доброто, което можах да измисля, е това, което прилага маската за всички месеци след февруари:
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2 мод х | 0 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
Променяйки базовата константа на 28 и добавяйки 2 към останалите месеци, получаваме формулата:
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x) | 29 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
За съжаление януари вече е с 2 дни по-кратък. Но, за щастие, е много лесно да се получи израз, който ще се прилага само за първия месец: той е обратен на числото, закръглено надолу. Умножавайки го по 2, получаваме крайната формула:
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
Послеслов
Ето я - формула за получаване на броя на дните във всеки месец от годината, използвайки проста аритметика. Следващият път, когато се чудите колко дни има през септември, просто използвайте тази едноредова JavaScript функция:Функция f(x) ( връщане 28 + (x + Math.floor(x/8)) % 2 + 2 % x + 2 * Math.floor(1/x); )
Част II
Въведение
В първата част беше получена кратка и дори леко елегантна формула, чиито основни предимства са простотата на математическия апарат, липсата на разклонения и условни изрази и сбитостта. Недостатъците - освен факта, че няма да го използвате в проекта си - включват липсата на проверка за високосна и невисокосна година.Затова си поставих задачата да създам функция f, така че стойността f(x, y)за всеки месец х, представени с число от 1 до 12 и годината г, по-голямо от 0, е равно на броя на дните в месеца хна година г.
За нетърпеливите има готов отговор под спойлера, но за останалите ви моля да ме последвате.
Отговор
Остатък от делението: модИ ⌊⌋
За визуална яснота, нека се съгласим, че в някои формули операторът за деление с остатък се заменя с долни скоби, където ми се стори необходимо:
Високосна година
През високосна година се въвежда допълнителен календарен ден: 29 февруари. Както знаете, високосна година е година, която се дели на 4 и не се дели на 100, нито на 400. Нека напишем израза, идентичен на това твърдение:За да преобразувате този израз в алгебричен, е необходимо да приложите инжекция на формата към резултата от израза:
Което ще ви позволи да получите 1 при деление без остатък и 0 при деление с остатък, за да го използвате във формулата за определяне броя на дните в месеца.
Като функция g"можете да използвате 1 минус остатъка от делението за:
| х | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| g"(x) | безкрайност | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Лесно се вижда, че като увеличим дивидента и делителя с 1, получаваме правилната формула за:
| х | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| g"(x) | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Така записваме израза като:
И нека напишем израза като:
Използвайки този подход, получаваме следната функция g(y), чиято стойност ще бъде 1, ако годината е високосна, или 0 в противен случай:
| г | 1990 | 1991 | 1992 | 1993 | 1994 | 1995 | 1996 | 1997 | 1998 | 1999 | 2000 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| g(y) | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
| г | 2000 | 2100 | 2200 | 2300 | 2400 | 2500 | 2600 | 2700 | 2800 | 2900 | 3000 |
| g(y) | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
Високосните години са показани с удебелен шрифт.
Позволете ми да ви напомня, че в рамките на приетото споразумение операторът за получаване на остатъка от делението може да бъде изобразен като мод, и ⌊⌋.
Наслагване на маска
Във формулата частта е изменение, което добавя 2 дни към януари. Ако премахнем фактора 2 и заменим 1 с 2 в числителя, тогава тази формула ще добави 2 дни към януари и 1 ден към февруари, което ни дава ключа за добавяне на ден във високосна година. За по-голяма яснота използваме междинна стойност във формулата g(y)и като гИзползваме 2000 (високосна) и 2001 (невисокосна) години:| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x, 2000) | 31 | 29 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
| f(x, 2001) | 30 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 30 |
Стойностите за всички месеци с изключение на януари на невисокосна година са правилни.
За да коригираме това досадно недоразумение, нека добавим 1 ден към януари, използвайки формулата, която вече ни е известна:
Или:
| х | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x, 2000) | 31 | 29 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
| f(x, 2001) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 30 |
Заключение
В резултат на това е получена много по-тромава, но по-универсална формула, която може да се използва и за определяне на броя на дните в месеца от дадена година:Функция f(x, y) ( return 28 + ((x + Math.floor(x / 8)) % 2) + 2 % x + Math.floor((1 + (1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1)) + (1 - (y % 400 + 2) % (y % 400 + 1))) / x) + Math.floor(1/x) - Math.floor(((1 - (y % 4 + 2) % (y % 4 + 1)) * ((y % 100 + 2) % (y % 100 + 1) )) + (1 - (y % 400 + 2) % (y % 400 + 1)))/x); )
Пример в C# ideone.com/fANutz.
1
. Не знам как да използвам такава мнемоника, затова погледнах знака в интернет.
2
. „Основи“ или „Правило с много изключения“, като повечето правила.
3
. Първоначално в римския календар февруари е бил последният месец на годината, така че е логично да е по-кратък от всички останали. Освен това има логика в добавянето или премахването на ден в края на годината, така че дължината му е променлива.
Актуализация 1:
Алтернативен превод на първата част в
Безплатният онлайн калкулатор Kontur.Accounting ще ви помогне и ще ви каже колко дни са изминали между две дадени дати. Освен това, ако имате нужда, можете да преброите колко календарни, почивни дни или работни дни (часове) съдържа определен период от година или няколко години.
Колко дни има между датите? Инструкции
Просто задавате конкретен начален и краен ден и получавате оферта за част от секундата. Онлайн калкулаторът изчислява всички данни независимо. Ако промените първоначалните дни от седмицата, резултатът автоматично се преизчислява, за да вземе предвид високосните години.
Важно: не можете да вземете месечни работни дни/часове от изчисленията за миналата година и да ги предоставите като изчисления - данните ще варират. Затова е по-добре да използвате калкулатор.
И така, процедурата е:
- В полетата „Начална дата“ и „Крайна дата“ изберете съответно началния и крайния ден на обратното броене, започвайки от 2013 г. и завършвайки в бъдеще през 2018 г.
- Задайте броя на работните часове на ден в следващото поле. По подразбиране това поле вече е зададено на 8 часа (40-часова работна седмица), но можете да промените това число.
- От дясната страна на екрана в банера ще видите резултата: работни дни, календарни дни и работно време между посочените дати. Резултатите трябва да бъдат копирани и запазени във вашия документ.
За какво можете да използвате калкулатор?
- Да изчислява неустойки и закъснения по договори
- Как да разберем ефективността на използването на даден ресурс и сроковете за използване
- Как да избегнете случайно планиране на задачи през уикенда
- Колко време остава до крайния срок
Пример:
Вие сте счетоводител. Мениджърът ви помоли в следващите няколко минути да предоставите данни за броя на работните часове, които всички служители на компанията трябва да работят през февруари. Можете лесно да определите броя на служителите - имате числата пред очите си. Но броят на часовете трябва да се преброи.... Колко дни има през февруари? Високосна ли е годината? Кои дни бяха почивните дни? Как да определим броя на почивните дни?
Решение: просто използвайте нашата джаджа. Ще получите цялата информация автоматично, нямате нужда от настолни календари и калкулатори.
Хареса ли ви този калкулатор? След това опитайте другите ни опции
Искате да водите счетоводство, да изпращате отчети и да правите изчисления по удобен и удобен начин? проста уеб услуга? Опитайте Контур.Счетоводство безплатно за 14 дни! Ние бързо ще Ви научим как да използвате услугата и ще отговорим на всички Ваши въпроси!
