Как выбрать максимальную дату в эксель
Для работы с датами в Excel в разделе с функциями определена категория «Дата и время». Рассмотрим наиболее распространенные функции в этой категории.
Как Excel обрабатывает время
Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 – это 0,5.
Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.
Этот принцип лежит в основе расчетов временных данных. Чтобы найти количество дней между двумя датами, достаточно от более позднего временного периода отнять более ранний.
Пример функции ДАТА
Построение значение даты, составляя его из отдельных элементов-чисел.
Синтаксис: год; месяц, день.
Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для дня – от 1 до 31.
Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.
Пример использования функции:
Зададим большее количество дней для июня:
Примеры использования в качестве аргументов ссылок на ячейки:
Функция РАЗНДАТ в Excel
Возвращает разницу между двумя датами.
- начальная дата;
- конечная дата;
- код, обозначающий единицы подсчета (дни, месяцы, годы и др.).
Способы измерения интервалов между заданными датами:
- для отображения результата в днях – «d»;
- в месяцах – «m»;
- в годах – «y»;
- в месяцах без учета лет – «ym»;
- в днях без учета месяцев и лет – «md»;
- в днях без учета лет – «yd».
В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.
Примеры действия функции РАЗНДАТ:
В версии Excel 2007 данной функции нет в справочнике, но она работает. Хотя результаты лучше проверять, т.к. возможны огрехи.
Функция ГОД в Excel
Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.
Пример использования функции ГОД:
Функция МЕСЯЦ в Excel: пример
Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.
Примеры использования функции МЕСЯЦ:
Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel
Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент – дата дня, который нужно найти, в числовом формате.
Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:
По умолчанию функция считает воскресенье первым днем недели.
Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:
Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).
В качестве второго аргумента указана цифра 2. Поэтому формула считает, что неделя начинается с понедельника (второй день недели).
Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().
Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.
Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.
Пусть имеются данные
Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:
Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.
Теперь, меняя название группы, можно без всяких фильтров и сводных таблиц видеть максимальное значение внутри этой группы.
Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.
Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel
На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.
Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.
Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.
Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)
Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.
Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.
Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.
Пусть имеется диапазон с датами. Найдем дату из этого диапазона, которая является ближайшей к заданной. Решение этой задачи аналогично решению, изложенного в статье Поиск ЧИСЛА ближайшего к заданному .
Пусть в диапазоне A4:A12 имеется список дат. Будем в нем искать дату из ячейки С4 . Если диапазон не содержит искомого значения, то будет возвращено ближайшее значение.
Искомая дата необязательно должна совпадать с какой-нибудь датой или даже находиться в диапазоне поиска (см. Файл примера ):
Решение
Результат поиска
Примечание
если столбец не отсортирован по возрастанию, то результат непредсказуем
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое расположено выше (номер строки меньше))
столбец м.б. не отсортирован
= ИНДЕКС(A4:A12; ПОИСКПОЗ(МАКС(ЕСЛИ(A4:A12<=C4;A4:A12;"")); $A$4:$A$12;0);1)
столбец м.б. не отсортирован
= ИНДЕКС(A4:A12; ПОИСКПОЗ(МИН(ЕСЛИ(A4:A12>=C4;A4:A12;"")); $A$4:$A$12;0);1)
столбец м.б. не отсортирован
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое больше
столбец м.б. не отсортирован
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое меньше
столбец м.б. не отсортирован
Последние 5 решений реализованы с использованием формул массива . Для пошагового просмотра хода вычислений используйте клавишу F9 .
Хорошим решением является формула массива =ИНДЕКС(A4:A12; ПОИСКПОЗ(МИН(ABS(A4:A12-C4));ABS(A4:A12-C4);0))
свободная от всех указанных недостатков, но имеющая свои: формула относительно сложная и является формулой массива .
Совет : т.к. дата в MS EXCEL хранится в числовом виде (см. статью Как Excel хранит дату и время ), то формулы для поиска ближайшего числа также будут работать для дат (см. раздел Ближайшее ЧИСЛО ).
В моем примере значения и даты перечислены в разных столбцах. Кроме того, я пытаюсь получить результат, показывающий максимальное значение между фактическими датами (а не датами, которые указаны в ячейках).
Например, у меня есть даты в столбце AC и цены на акции в столбце AG. У меня есть столбец SU, отслеживающий конечную дату (которая остается неизменной на 10 лет раньше даты в текущем столбце AC). Столбец SV это где у меня есть формула. Формула для SV17249:
Я выбрал диапазон AC14739:AC17249, потому что он равен приблизительному количеству годовых торговых дней (x10) в течение 10-летнего периода (2510). Тем не менее, я пытаюсь найти максимальное значение между двумя датами, которые находятся на расстоянии ровно 10 лет (например, с 1 января 2000 года по 1 января 2010 года). Но есть некоторые годы, когда торговых дней больше или меньше, чем у других, и это отбрасывает фиксированный диапазон 2510, который я ввел в формулу.
Есть ли способ получить указанную выше формулу для получения максимальной цены акций в AG между фактической датой в ячейке AC17249 и датой ровно за 10 лет до AC17249?
1 ответ 1
Общий подход в вашей формуле, вероятно, так же хорош, как и любой. Это просто нужно настроить, чтобы делать то, что вы хотите.
Похоже, у вас есть тонна данных, поэтому это может ускорить процесс, если вы ограничите оценочный диапазон. Если в 10 лет примерно 2510 строк, округлите до числа, которое гарантированно включает 10 лет, например 2600 строк. Таким образом, оцененный диапазон для данной строки заканчивается в данной строке и включает 2600 предыдущих строк.
Я полагаю, что примерно за один день 10 календарных лет будут включать 3652 дня. Эта дата начала календаря не обязательно будет торговым днем, поэтому нам нужно найти первый торговый день после этой календарной даты (вы не хотите дважды считать даты окончания; ваш диапазон включает дату окончания, поэтому 10 лет перед этим фактически будет дата окончания предыдущего периода).
Я буду основывать свой пример на дате окончания в вашем вопросе, которая содержится в AC17249. Это позволило бы связать начало диапазона оценки с AC14649. Если вы жестко закодируете диапазоны как относительную адресацию, вы сможете скопировать формулу и правильно настроить ее для других строк. Трудно проверить формулы без вашей массивной таблицы, но я считаю, что это должно работать:
Это формула массива, поэтому необходимо подтвердить, нажав Ctrl - Shift - Enter, а не Enter .
Читайте также: