Excel как определить високосный год
Как проверить, является ли год високосным в Excel?
Вы знаете, как определить, високосный год или нет? В Excel нет прямого метода проверки, но вы можете создать определяемую пользователем функцию или формулу для решения этой проблемы.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Проверьте, является ли год високосным с формулой
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Предположим, у вас есть список лет на листе, следующая длинная формула может помочь вам определить, является ли данный год високосным или нет. Пожалуйста, сделайте так:
1. Помимо ячеек года, введите в пустую ячейку следующую формулу:
= ЕСЛИ (ИЛИ (MOD (A2,400) = 0, AND (MOD (A2,4) = 0, MOD (A2,100) <> 0)), «Високосный год», «НЕ високосный год») (A2 содержит год, когда вы хотите применить эту формулу, вы можете изменить его по своему усмотрению) см. снимок экрана:
2. Затем перетащите маркер заполнения в диапазон, который вы хотите содержать в этой формуле, и все годы будут проверены, являются ли они високосными или нет.
Внимание: Если соответствующая ячейка пуста, эта формула также покажет високосный год.
Проверьте, является ли год високосным с помощью функции, определяемой пользователем
Вы также можете создать функцию, определяемую пользователем, чтобы проверить, является ли год високосным, вы можете заполнить его следующим образом:
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: проверьте, является ли год високосным
3. Затем сохраните и закройте этот код, вернитесь на свой рабочий лист и введите эту формулу: = isleapyear (A2), см. Снимок экрана:
4. Затем перетащите маркер заполнения в ячейки, которые вы хотите содержать эту формулу, и все годы отмечены флажком, НЕПРАВДА означает не високосный год, а ИСТИНА указывает високосный год. Смотрите скриншот:
Microsoft Excel неправильно предполагает, что 1900 год является високосным. В этой статье объясняется, почему 1900 год рассматривается как високосный год, и описывается поведение, которое может произойти при исправлении этой конкретной проблемы.
Дополнительная информация
При первом выпуске Lotus 1-2-3 программа предполагает, что 1900 год был високосным, хотя на самом деле это был не високосный год. Это упростит для программы обработку високосных лет и не навредит почти всем вычислениям дат в Lotus 1-2-3.
Когда microsoft Multiplan и Microsoft Excel были выпущены, они также предполагали, что 1900 был високосным годом. Это предположение позволило Microsoft Multiplan и Microsoft Excel использовать ту же последовательную систему дат, используемую Lotus 1-2-3, и обеспечить большую совместимость с Lotus 1-2-3. Если рассматривать 1900 как високосный год, пользователям также было проще перемещать листы из одной программы в другую.
Если бы это поведение было исправлено, возникнет множество проблем, в том числе:
- Почти все даты в текущих Microsoft Excel листах и других документах будут уменьшены на один день. Исправление этой смены займет много времени и усилий, особенно в формулах, использующих даты.
- Некоторые функции, такие как функция WEEKDAY, возвращают разные значения. Это может привести к неправильной работе формул на листах.
- Исправление этого поведения приведет к разрыву совместимости последовательной даты между Microsoft Excel и другими программами, использующими даты.
Если поведение остается некорrected, возникает только одна проблема:
- Функция WEEKDAY возвращает неверные значения для дат до 1 марта 1900 г. Так как большинство пользователей не используют даты до 1 марта 1900 г., эта проблема возникает редко.
ПРИМЕЧАНИЕ. Microsoft Excel правильно обрабатывает все остальные високосные годы, включая високосные годы, которые не являются високосными (например, 2100). Неправильно обрабатывается только 1900 год.
Ссылки
Дополнительные сведения о том, является ли данный год високосным, см. в разделе "Метод определения того, является ли год високосным годом".
Microsoft Excel поддерживает две разные системы дат. Это система дат 1900 и 1904. В этой статье описываются две системы дат и проблемы, которые могут возникнуть при использовании книг, использующих разные системы дат.
Дополнительная информация
Система дат 1900 г.
В системе дат 1900 года первый поддерживаемый день — 1 января 1900 г. При вводе даты дата преобразуется в серийный номер, представляющий количество затраченных дней, начиная с 1 на 1 января 1900 г. Например, если ввести 5 июля 1998 г., Excel преобразует дату в серийный номер 35981.
По умолчанию Microsoft Excel для Windows использует систему дат 1900. Система дат 1900 обеспечивает лучшую совместимость между Excel и другими программами электронной таблицы, такими как Lotus 1-2-3, которые предназначены для работы в MS-DOS или Microsoft Windows.
Система дат 1904 г.
В системе дат 1904 первый поддерживаемый день — 1 января 1904 г. При вводе даты дата преобразуется в серийный номер, представляющий количество затраченных дней с 1 января 1904 г., начиная с 0 для 1 января 1904 г. Например, если ввести 5 июля 1998 г., Excel преобразует дату в серийный номер 34519.
Из-за проектирования ранних компьютеров Macintosh даты до 1 января 1904 г. не поддерживались. Эта структура была предназначена для предотвращения проблем, связанных с тем, что 1900 не был високосным годом. В прошлом для Excel Macintosh по умолчанию используется система дат 1904 для книг, созданных на компьютере Macintosh. Однако Excel macintosh теперь по умолчанию используется система дат 1900 и поддерживает даты уже 1 января 1900 г.
Разница между системами дат
Поскольку в двух системах дат используются разные начальные дни, в каждой системе дат одинаковые даты представлены разными серийными номерами. Например, 5 июля 1998 г. могут иметь два разных серийных номера, как показано ниже.
Система дат | Серийный номер от 5 июля 1998 г. |
---|---|
система дат 1900 | 35981 |
Система дат 1904 | 34519 |
Разница между двумя системами дат — 1462 дня; то есть серийный номер даты в системе дат 1900 всегда на 1462 дня больше, чем серийный номер той же даты в системе дат 1904 года. 1462 дня — это четыре года и один день (включая один високосный день).
Настройка системы дат для книги
В Excel каждая книга может иметь собственные системные параметры дат, даже если открыто несколько книг.
Чтобы задать систему дат для книги в Microsoft Office Excel 2003 и более ранних версиях Excel, выполните следующие действия.
Чтобы задать систему дат для книги в Microsoft Office Excel 2007 г., выполните следующие действия:
Проблемы с связыванием и копированием дат между книгами
Если две книги используют разные системы дат, могут возникнуть проблемы при связывание или копировании дат между книгами. В частности, даты могут быть сдвинуты на четыре года и один день.
Чтобы просмотреть пример такого поведения, выполните следующие действия:
В Excel создайте две новые книги (Book1 и Book2).
Выполните действия, описанные в разделе "Настройка системы дат для книги", чтобы использовать систему дат 1900 в книге 1. Используйте систему дат 1904 в Книге 2.
В book1 введите дату 5 июля 1998 г.
Выберите ячейку, содержащую дату, и нажмите кнопку "Копировать " в меню "Правка ".
Примечание В Excel 2007 выберите ячейку, содержащую дату, и нажмите кнопку "Копировать" в группе буфера обмена на вкладке " Главная".
Переключитесь на Book2, выберите ячейку и щелкните " Вставить " в меню "Правка ".
Примечание В Excel 2007 переключитесь на Book2, выберите ячейку и нажмите кнопку "Вставить" в группе буфера обмена на вкладке " Главная".
Дата вставляется как 6 июля 2002 г. Обратите внимание, что дата на четыре года и на один день позже даты на шаге 3, так как в Book2 используется система дат 1904.
В book2 введите дату 5 июля 1998 г. Выберите ячейку, содержащую дату, и нажмите кнопку "Копировать " в меню "Правка ".
Примечание В Excel 2007 г. введите дату 5 июля 1998 г. в book2. Выделите ячейку, содержащую дату, и нажмите кнопку " Копировать" в группе буфера обмена на вкладке "Главная ".
Переключитесь на Book1, выберите ячейку и щелкните " Вставить " в меню "Правка ".
Примечание В Excel 2007 переключитесь на Book1, выберите ячейку и нажмите кнопку "Вставить" в группе буфера обмена на вкладке " Главная".
Дата вставляется как 4 июля 1994 г. Он был сдвинут на четыре года и один день, так как book1 использует систему дат 1900.
Исправление сдвига дат
В пустой ячейке введите значение 1462.
Выделите ячейку. В меню Правка выберите команду Копировать.
Примечание В Excel 2007 выберите ячейку и нажмите кнопку "Копировать" в группе буфера обмена на вкладке "Главная".
Выделите ячейки, содержащие сдвинутые даты. В меню "Правка " щелкните " Специальная вставка".
Примечание В Excel 2007 выделите ячейки, содержащие сдвинутые даты, щелкните "Вставить" в группе буфера обмена на вкладке "Главная", а затем нажмите кнопку "Специальная вставка ".
В диалоговом окне "Специальная вставка" установите флажок "Значения" в разделе "Вставка ", а затем установите один из следующих флажков в разделе "Операция".
Повторите эти действия, пока не будут исправлены все сдвинутые даты.
Если вы используете формулу для связывания с датой в другой книге и если дата, возвращенная формулой, неправильная, так как книги используют разные системы дат, измените формулу, чтобы она возвращала правильную дату. Например, используйте следующие формулы:
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
В этой статье описывается, как определить, является ли год в дате, используемой в документе Microsoft Excel, високосным.
Дополнительная информация
Система дат, используемая Excel, основана на григорианском календаре, впервые установленном в 1582 году папой римским Григорием XIII. Этот календарь был разработан для исправления ошибок, привнесенных менее точным юлианским календарем.
В григорианском календаре обычный год состоит из 365 дней. Поскольку фактическая продолжительность звездного года (период обращения Земли вокруг Солнца) составляет 365.2425 дней, «високосный год» в 366 дней используется один раз каждые четыре года для устранения ошибки, вызванной тремя обычными (но короткими) годами. Любой год, который делится на 4 без остатка, является високосным годом: например, 1988, 1992 и 1996 годы являются високосными годами.
Тем не менее, есть еще небольшая ошибка, которая должна быть учтена. Чтобы устранить эту ошибку, григорианский календарь предусматривает, что год, который делится без остатка на 100 (например, 1900) является високосным годом только в том случае, если он также без остатка делится на 400.
По этой причине следующие годы не являются високосными:
1700, 1800, 1900, 2100, 2200, 2300, 2500, 2600
Это потому, что они делятся без остатка на 100, но не на 400.
Следующие годы – високосные: 1600, 2000, 2400
Это потому, что они делятся без остатка на 100 и 400.
Поскольку версии Microsoft Excel, более ранние, чем Excel 97, обрабатывают только годы с 1900 по 2078 год, только для 1900 года действует правило исключения 100/400 високосных лет в Microsoft Excel. Однако для совместимости с другими программами Microsoft Excel рассматривает 1900 год как високосный.
Как определить, является ли год високосным
Чтобы определить, является ли год високосным, выполните следующие действия:
- Если год делится на 4 без остатка, перейдите на шаг 2. В противном случае перейдите к выполнению действия 5.
- Если год делится на 100 без остатка, перейдите на шаг 3. В противном случае перейдите к выполнению действия 4.
- Если год делится на 400 без остатка, перейдите на шаг 4. В противном случае перейдите к выполнению действия 5.
- Год високосный (366 дней).
- Год не високосный год (365 дней).
Формула, чтобы определить, является ли год високосным
Используйте следующую формулу, чтобы определить, является ли номер года, который вводится в ячейку (в данном примере, ячейка A1), високосным годом:
Чтобы иметь возможность использовать встроенные функции EXCEL необходимо, чтобы даты были в диапазоне от 01.01.1900 до 31.12.9999. Но, иногда, этого диапазона дат недостаточно. Рассмотрим типичную задачу, в которой может возникнуть необходимость использования дат до 01.01.1900.
Задача
Определим сколько лет, месяцев и дней прожил Джордж Ноэл Гордон Байрон (дата рождения 22.01.1788, дата смерти 19.04.1824).
Решение
Для решения воспользуемся функцией РАЗНДАТ() , но перед этим прибавим к датам рождения и смерти по 2000 лет, чтобы иметь возможность использовать эту функцию, т.к на вход РАЗНДАТ() можно подавать даты от 01.01.1900 до 31.12.9999. (см. файл примера ).
Почему мы прибавили 2000 лет, а не, например, 1000? Это сделано для того чтобы учесть високосный ли год . Дело в том, что согласно действующего Григорианского календаря, год кратный 4 – високосный, но если год кратен 100, то он не считается високосным. А вот если он еще и кратен 400, то он все-таки високосный. Поэтому для обеспечения точности до дней лучше прибавлять к датам до 01.01.1900 величины кратные 400, т.е. 2000, а не 1000. В нашем случае, конечно, можно было бы прибавить и 400 лет, чтобы попасть в диапазон от 01.01.1900 до 31.12.9999.
Если в ячейку ввести значение 22.01.1788, то EXCEL воспримет его как текст. Как же прибавить к дате 22.01.1788, представляющей собой текстовое значение, 2000 лет? Для этого в файле примера день, месяц и год рождения/ смерти введены в отдельные ячейки. Затем к году необходимо прибавить 2000 лет и, наконец, перевести получившееся значение в формат даты с помощью функции ДАТА() : =ДАТА(B7;B5;B4) .
Для подсчета полного времени жизни с точностью до дня можно использовать формулу: =РАЗНДАТ(B8;C8;"y")&" лет " &РАЗНДАТ(B8;C8;"ym")&" мес. " &РАЗНДАТ(B8;C8;"md")&" дн."
Итак, Байрон прожил 36 лет 2 месяца и 28 дней.
ВНИМАНИЕ! Как показано в статье с описанием недокументированной функции РАЗНДАТ() , эта функция с параметром "md" может вернуть ошибочное значение (ошибка м.б. несколько дней в зависимости от версии EXCEL).
О том как EXCEL хранит дату можно прочитать в одноименной статье Как EXCEL хранит дату и время .
Читайте также: