Поиск ближайшей даты в эксель
Добрый вечер дорогие форумчане! Слезно прошу о помощи. Ибо собственных знаний не хватило для выполнения нижеописанной задачи.
Пробовала и
. Но это все не помогает сделать привязку к отметке "да" (ниже будет понятно к чему это).
Есть такая задача:
В первом столбце имеется список дат (который постепенно может дополняться (вниз)). Каждая из этих дат обозначает крайний период подачи справки в налоговую. Во втором столбце напротив даты ставится пометка "да" если справка подана и пусто, если справка еще не подана. Также имеется дата "сегодня", которая прописана формулой СЕГОДНЯ().
Нужно в отдельную ячейку выводить ближайшую (ближайшую к дате "сегодня") дату подачи справки из первого столбца, учитывая все предыдущие даты по которым уже есть пометка "да" (т.е. справка уже подана).
Добрый вечер дорогие форумчане! Слезно прошу о помощи. Ибо собственных знаний не хватило для выполнения нижеописанной задачи.
Пробовала и
. Но это все не помогает сделать привязку к отметке "да" (ниже будет понятно к чему это).
Есть такая задача:
В первом столбце имеется список дат (который постепенно может дополняться (вниз)). Каждая из этих дат обозначает крайний период подачи справки в налоговую. Во втором столбце напротив даты ставится пометка "да" если справка подана и пусто, если справка еще не подана. Также имеется дата "сегодня", которая прописана формулой СЕГОДНЯ().
Нужно в отдельную ячейку выводить ближайшую (ближайшую к дате "сегодня") дату подачи справки из первого столбца, учитывая все предыдущие даты по которым уже есть пометка "да" (т.е. справка уже подана). ShagiDasha
. Но это все не помогает сделать привязку к отметке "да" (ниже будет понятно к чему это).
Есть такая задача:
В первом столбце имеется список дат (который постепенно может дополняться (вниз)). Каждая из этих дат обозначает крайний период подачи справки в налоговую. Во втором столбце напротив даты ставится пометка "да" если справка подана и пусто, если справка еще не подана. Также имеется дата "сегодня", которая прописана формулой СЕГОДНЯ().
Нужно в отдельную ячейку выводить ближайшую (ближайшую к дате "сегодня") дату подачи справки из первого столбца, учитывая все предыдущие даты по которым уже есть пометка "да" (т.е. справка уже подана). Автор - ShagiDasha
Дата добавления - 28.11.2017 в 22:56
На случай, если даты не отсортированы
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
На случай, если даты не отсортированы
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
sboy,
Добрый день. Спасибо за ответ.
Но первый вариант у меня не работает. Когда ставишь да в ячейку В6 дата остается по-прежнему 19/12/2017, хотя она должна поменяться на 28/01/2018.
А второй вариант работает, но есть один момент, у вас в формуле выбраны диапазоны фиксированные и по столбцу B и по столбцу A. А даты то будут добавляться, и отметки тоже. Получается что если добавлять даты в нижние строки, то уже не работает(
Может быть мне нужно изначально в формуле выбирать более широкий диапазон по нумерации строк вниз?
sboy,
Добрый день. Спасибо за ответ.
Но первый вариант у меня не работает. Когда ставишь да в ячейку В6 дата остается по-прежнему 19/12/2017, хотя она должна поменяться на 28/01/2018.
А второй вариант работает, но есть один момент, у вас в формуле выбраны диапазоны фиксированные и по столбцу B и по столбцу A. А даты то будут добавляться, и отметки тоже. Получается что если добавлять даты в нижние строки, то уже не работает(
Может быть мне нужно изначально в формуле выбирать более широкий диапазон по нумерации строк вниз? ShagiDasha
_Boroda_, спасибо за ответ.
Но друзья, получается что формулы работают только в случае примера с тремя датами. А в условии задачи предполагается, что даты будут добавляться, и получается если добавить дату на 8 и 9ю строки (а будут добавляться и дальше), то формула перестает работать(
_Boroda_, спасибо за ответ.
Но друзья, получается что формулы работают только в случае примера с тремя датами. А в условии задачи предполагается, что даты будут добавляться, и получается если добавить дату на 8 и 9ю строки (а будут добавляться и дальше), то формула перестает работать( ShagiDasha
Пусть имеется диапазон с датами. Найдем дату из этого диапазона, которая является ближайшей к заданной. Решение этой задачи аналогично решению, изложенного в статье Поиск ЧИСЛА ближайшего к заданному .
Пусть в диапазоне A4:A12 имеется список дат. Будем в нем искать дату из ячейки С4 . Если диапазон не содержит искомого значения, то будет возвращено ближайшее значение.
Искомая дата необязательно должна совпадать с какой-нибудь датой или даже находиться в диапазоне поиска (см. Файл примера ):
Решение
Результат поиска
Примечание
если столбец не отсортирован по возрастанию, то результат непредсказуем
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое расположено выше (номер строки меньше))
столбец м.б. не отсортирован
столбец м.б. не отсортирован
= ИНДЕКС(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 хранит дату и время ), то формулы для поиска ближайшего числа также будут работать для дат (см. раздел Ближайшее ЧИСЛО ).
Если у вас есть список дат на листе, и вы хотите найти самую последнюю или самую старую дату на основе определенного значения, как показано на следующем снимке экрана:
У вас есть идеи, как решить эту проблему в Excel?
Поиск последней или самой ранней даты с формулами в Excel
Чтобы получить относительную самую старую или самую новую дату определенного значения, вы можете применить следующие формулы для ее решения.
Чтобы получить самую старую дату, введите эту формулу: =MIN(IF(A2:A16=E1,B2:B16)) в указанную ячейку, а затем нажмите Shift + Ctrl + Enter вместе, чтобы вернуть правильный ответ, см. снимок экрана:
Чтобы получить самую новую дату, введите эту формулу: =MAX(IF(A2:A16=E1,B2:B16)) в указанную вами ячейку и не забудьте нажать Shift + Ctrl + Enter вместе, чтобы вернуть результат, см. снимок экрана:
Ноты:
1. В приведенных выше формулах:
- A2: A16 столбец, в котором вы хотите искать;
- E1 это значение поиска;
- B2: B16 - столбец, в который будет возвращен результат.
2. Если вы получаете числовой результат после нажатия Shift + Ctrl + Enter ключи, вам просто нужно преобразовать число в формат даты. Выберите числовую ячейку и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек, то в Формат ячеек диалоговое окно, нажмите Время под Число и выберите один формат даты в правом поле списка Тип.
В некоторых случаях события могут быть назначены не на конкретную дату, а привязаны к определенному дню недели заданного месяца и года - например:
- первый понедельник января 2007 года - самый тяжелый понедельник года
- второе воскресенье апреля 2011 года - день ПВО
- первое воскресенье октября 2012 года - день Учителя
- и т.д.
Чтобы определить точную дату, на которую приходится такой день недели, нам потребуется небольшая, но хитрая формула:
в англоязычной версии это будет
При использовании этой формулы предполагается, что
- B1 - год (число)
- B2 - номер месяца (число)
- B3 - номер дня недели (Пн=1, Вт=2 и т.д.)
- B4 - порядковый номер дня недели, который вам нужен
За существенное упрощение и улучшение формулы большое спасибо уважаемому МСН с нашего Форума.
Ссылки по теме
Здравствуйте,Николай, использовала Ваш пример в 7-ой версии,ввела формулу , которую Вы предложили, однако в ячейке b5 у меня получилось 40675,почему?.
Татьяна, поменяйте формат этой ячейки на дату - вы видите код даты, т.к. формат общий или числовой, скорее всего.
Добрый день!
Бегу к Вам за помощью.
Вопрос:
наимен. | дата развоза | сумма |
молоко | ср,сб | |
сыр | вскр. |
Мне нужно,чтобы в колонке дата развоза подставлялась определенная дата, в чем суть, молоко развозят по ср и сб, а сыр по вскр.,т.е. когда открываю эксель,нужно чтобы автоматически подставлялась ближайшая дата развоза,например сегодня 29.08, а молоко привезут 31.08 и сыр 01.09,т.е. эти даты уже автоматом становятся,а когда эти даты пройдут,то подставляются следущие ближайшие.Как это сделать?
Добрый день, подскажите пожалуйста в ячейках дата с временем, требутется для вычисления без времени. Есть 23.08.2013 23:25:12, а нужно 23.08.2013
Использовать =ЦЕЛОЕ(А1), где А1 - ячейка с датой и временем. Посмотрите вот эту статью - сразу станет понятно, как Excel на самом деле работает с датами и временем.
Николай, добрый день! помогите пожалуйста решить следующую задачу. в работе на практике зачастую приходится вычислить дату приходящуюся на 30 рабочий день. Например дата отсчета 15.07.2014 необходимо вычислить дату приходящуюся на 30 рабочий день с учетом праздников и выходных суббота и воскресенье. Спасибо.
Добрый день!
каким образом можно обнулить дату, например
15.08.2014 15.55 на 15.08.2014 00.00
Заранее благодарю!
есть ли возможность в дате со временем (одна ячейка) обнулить только секунды?
необходимо для суммирования по критерию даты.
секунды мешают
=ЦЕЛОЕ(А1) не подойдет.
решил
=ВРЕМЗНАЧ(СЦЕПИТЬ(ЧАС(A1);":";МИНУТЫ(A1)))
Добрый день. Формула хороша. Вот только не понимаю вторую часть. ОСТАТ(B3-ДАТА(B1;B2;);7). Получается мы из 6 вычисляем 31, т.к. 5-й месяц 2013 - май, там 31 день, получается -25, и дальше -25 делим на 7. НО, получается в остатке 4, т.к. 3*7=21. 25-21=4. Как так получается, что в этой части формулы - 3?
Миха Миха, а причем здесь "31"?
Значение выражения ДАТА(B1;B2;) равно 30.04.2013 или 41394 если в числовом значении.
Но мне все же не понятен алгоритм вычисления Excel' ем этой части:
6 - 41394 = - 41388, а -41388 / 7 = -5912,5714
Откуда Excel берет 3?
Добрый день!
Нужна помощь специалистов.
Имеем дату начала события вида 21.08.2015 11:42:00.
Нужно вычислить дату и время, которое наступит ровно через 18 рабочих часов. За рабочие часы принимаем длительность рабочего дня включая перерыв на обед, т.е. 9 часов. Субботу и воскресенье не считаем.
Рабочий день начинается в 09:00, заканчивается в 18:00.
Буду очень признателен за помощь.
Добрый день! Поясните пожалуйста, почему у функции Дата только 2 аргумента. В справке Excel указано, что 3 аргумента обязательны.
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071
Возникла следующая проблема: есть диапазоны дат, есть также искомое значение (конкретная календарная дата). Надо определить конкретный диапазон дат, который содержит искомую календарную дату и получить значение ячейки напротив нужного диапазона.
Пример в приложении
Поиск значения в диапазоне
Добрый день! Помогите пожалуйста. В данном конкретном случае необходимо в лист1 столбец Е.
Поиск значения в диапазоне ячеек и вывод результата в ячейку
Доброго времени суток. Поиск не помог мне, решил создать тему. Исходные данные: есть 2.
Поиск совпадений в диапазоне и поставление значения соседней от совпавшей ячейки
Формула находится в ячейке Е1 и ищет совпадения значения ячейки А1 со значением одной из ячеек в.
Поиск дат в периоде дат (целиком или частично)
Добрый день! У Меня есть проблема, не могу подобрать формулу по поиску определенного периода даты.
Большое спасибо Все имена заняты и DV68. Похоже все работает.
Как як понял, используются формулы массивов. Не могли бы вы сказать как они работают конкретно в этом месте (I5>=B1:B5)*(I5<=C1:C5) ?
Почему при использовании =ПОИСКПОЗ(1;. в качестве искомого значения используется 1?
Почему при использовании =ПРОСМОТР(2;. в качестве искомого значения используется 2?
Два масссива, содержащие значения ИСТИНА или ЛОЖЬ, перемножаются. В результате получается массив, состоящий из нулей и, возможно, содержащий одну единицу.
Именно поэтому здесь используется единица.
Можно ведь, кстати, посмотреть поэтапную работу формулы, там все очень наглядно.
Для 2007: Формулы >> Зависимости формул >> Вычислить формулу.
Для 2003: Сервис >> Зависимости формул >> Вычислить формулу.
Все имена заняты, спасибо огромное. не знал, что можно применять множить логические значения. Также не знал, что можно создавать по сути новый массив не в ячейке сбоку а, как бы, в памяти и с ним проводить операции (в том числе поиска по созданному в памяти массиву).
Пробовал смотреть выполнение формулы, но после того как появились ИСТИНА и ЛОЖЬ я упустил цепочку происходящего и дальше уже ничего не понимал.
Еще раз спасибо, что просветили.
С уважение,
Виктор Королев
Если не помогает просмотр вычисления формулы, то можно выделить необходимый кусок в формуле, например (I5>=B1:B5)*(I5 <=C1:C5), и нажать F9. Сразу увидите массив, который получился при вычислении.
Кстати формула с ПРОСМОТРом не формула массива, но помедленнее будет, чем ИНДЕКС(. ПОИСКПОЗ(. ))
У меня почти такая же проблема что у Buchhalter:
есть диапазоны дат, есть также искомое значение (конкретная календарная дата). Надо определить конкретный диапазон дат, который содержит искомую календарную дату и получить значение ячейки напротив нужного диапазона.
напротив ячейки содержащую дату нужно ставить значение из массива. В моем случае это курс доллара на эту дату.
В приведенном примере у Buchhalter только два столбца с датами, а у меня их много.
Я пытался пользоваться формулой приведенной Все имена заняты, не получилось.
Во вложенном файле я указал два варианта:
Вариант 1 - в массиве, содержащую даты, указаны все даты периода
Вариант 2 - в массиве, содержащую даты, указаны только первая и последняя дата периода.
Читайте также: