Поиск в EXCEL ДАТЫ ближайшей к заданной
Пусть имеется диапазон с датами. Найдем дату из этого диапазона, которая является ближайшей к заданной. Решение этой задачи аналогично решению, изложенного в статье Поиск ЧИСЛА ближайшего к заданному .
Пусть в диапазоне 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 .
Как видно из таблицы, применение функции ВПР() со значением аргумента интервальный_просмотр равным ИСТИНА, имеет недостатки. Во-первых, найденное значение м.б. далеко не ближайшим (задав в качестве критерия дату 06.02.2009 получим не ближайшую дату 07.02.2009, а наибольшее значение, которое меньше, чем искомое значение, т.е. 05.01.2009). Во-вторых, если искомая дата меньше, чем минимальное значение из диапазона, то будет возвращена ошибка #Н/Д. В-третьих, требуется сортировка списка, что не всегда удобно.
Хорошим решением является формула массива =ИНДЕКС(A4:A12; ПОИСКПОЗ(МИН(ABS(A4:A12-C4));ABS(A4:A12-C4);0))
свободная от всех указанных недостатков, но имеющая свои: формула относительно сложная и является формулой массива .
Совет : т.к. дата в MS EXCEL хранится в числовом виде (см. статью Как Excel хранит дату и время ), то формулы для поиска ближайшего числа также будут работать для дат (см. раздел Ближайшее ЧИСЛО ).
Ближайшая дата(из диапазона) к текущей
Сравнивание диапазона дат с текущей
Всем привет Пишу не большой код по сравниванию дат с текущей, есть три диапазона, если выбран один.
Дата оперативно проводимого документа меньше текущей. Документ не може
Добрый день! Абсурд какой-то. Документ с датой в прошлом отказывается проводиться! Говорит.
Задана дата. Определить, прошло ли 25 дней от этой самой даты до текущей
Вводим с клавиатуры дату: день, номер месяца и год. И нужно определить, прошло ли 25 дней с.
Как по умолчанию заполнять поле "дата" текущей датой
Здравствуйте! В Бд sql есть поле с типом "data" В detailview подключенном к sqldatasource , при.
Поиск нужных данных в диапазоне
Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.
Если же вы знакомы с ВПР, то — вдогон — стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:
Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.
Задача решается при помощи двух функций:
=ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16 . Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.
Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).
Ссылки по теме
Не за что!
А скажите, пожалуйста, можно ли чтобы эта чудесная формула искала бы значения на разных листах? Я поробовала сделать вот так:
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0), но ничего не получается.
Спасибо.
Без файла сказать трудно. Но у вас с ходу в формуле выделенное красным — это что?
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900; $C$700:$F$900; ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0)
У функции ИНДЕКС три аргумента, а у вас — четыре. Что-то лишнее
При поиске ближайшего наименьшего (последний аргумент функции ПОИСКПОЗ равен 1) таблица, где ищем, должна быть обязательно отсортирована по возрастанию.
При поиске ближайшего наибольшего — по убыванию.
Спасибо Большое Вам, Николай! Не только за этот пример, а в общем — за весь Сайт.
Николай, спасибо за урок!
А не лучше ли сделать то же самое с помощью функции ПРОСМОТР?
=ПРОСМОТР(C16;D2:D13;B2:B13)
Помоему проще и для понимания и для реализации.
Добрый день всем!
Это мой первый комментарий.
В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель!
Ну, а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере.
Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе.
Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.)
Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа?
Заранее благодарю за помощь!
Не всегда помогает. Сегодня весь день убил на реализацию этого метода. Все в толк не возьму — или криво офис на комп встал. или одно из двух. то #ссылку возвращает то #н/д .
=ИНДЕКС(Диллеры!$A$4:$B$103;C3;2)
на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало — непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле.
Если вставлять ПОИСКПОЗ вообще никак не отрабатывает.
Функция по потенциалу понравилась, но как отрабатывает конкретно у меня — нет.
ВПР отрабатывает на отлично, но только на одном листе. С другого тоже не хочет хоть разбейся.
Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам.
Благодарю Николая за невероятную комбинацию функций ВПР; СМЕЩ; ПОИСКПОЗ; СЧЁТЕСЛИ,
которая подарила мне уйму свободного времени. Очень грамотно.8)
Николай, большое спасибо за Ваши уроки!
Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗ
Ещё раз благодарю Вас!
С уважением, Вячеслав!
Ну да, хорошее решение, чтобы не считать номер колонки вручную
Здравствуйте, а если шапка таблицы многослойная решение есть?
Многослойная шапка — например в строке 2 условия в столбце 2 условия а не по одному.
т.е.
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);
т.е. значение готовое выберется не по 2-м условиям а по четырем
Подскажите пожалуйста как это реализовать в одной формуле.
Спасибо!
Вы так быстро ответили , что я таблицу неуспел нарисовать ))
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
| Усл 2 | q | q | q | h | h | h | |||
| Усл 4 | x | y | z | x | y | z | |||
| список выбора условия 1 | k | Усл 1 | Усл 3 | ||||||
| список выбора условия 2 | q | j | b | а | б | в | г | д | е |
| список выбора условия 3 | j | s | е | ж | з | и | к | л | |
| список выбора условия 4 | j | f | м | н | о | п | р | с | |
| решение | т | k | b | т | у | ф | х | ц | ч |
| k | s | ш | щ | ъ | ы | ь | э | ||
| k | f | ю | я | — | — | — | — |
а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);
т.е. значение готовое выберется не по 2-м условиям а по четырем
| Усл 2 | q | q | q | h | h | h | |||
| Усл 4 | x | y | z | x | y | z | |||
| список выбора условия 1 | k | Усл 1 | Усл 3 | ||||||
| список выбора условия 2 | h | j | b | а | б | в | г | д | е |
| список выбора условия 3 | s | j | s | е | ж | з | и | к | л |
| список выбора условия 4 | j | f | м | н | о | п | р | с | |
| решение | #ССЫЛ! | k | b | т | у | ф | х | ц | ч |
| должно быть ы | k | s | ш | щ | ъ | ы | ь | э | |
| k | f | ю | я | — | — | — | — |
Антон, по такой картинке качественно ответить нереально. Я бы склеил условия из шапки попарно с помощью функции СЦЕПИТЬ и получил бы в итоге одно условие, по которому бы и делал обычный поиск. Лучше сделайте тему на форуме и приложите нормальный файл с примером, тогда ответ будет точнее
Сам себе и отвечу . =ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
(Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. )
Искомое значение в ячейке C16 (авс)
0 заменил на 1 и почистил казалось-бы пустые ячейки (раньше формулой было записано «»;) в строке.
И тут появляется «НО» — если в строке D1:D13 пустые ячейки появляются пару раз (например:
D1 D2 D3 D4 D5 D6 D7 D8 . D13
(авс) (авс) (авс) ( ) ( ) (авс) (авс) ( ) . (авс), то формула ПОИСКПОЗ выдаст значение D7, хотя должно быть D13.
Встречал на каком-то форуме ПОИСК (Ctrl+F) — значение (авс) — ВВОД (Shift+Enter). А как это записать формулой?
| если в строке D1:D13 |
D1:D13 — это столбец, а не строка
Если вы имели ввиду вопрос «как сделать так, чтобы формула находила не первое встретившееся, а последнее значение», то тут проще всего макросом, наверное — писать на VBA функцию аналогичную ВПР.
Николай, во-первых, хочу сказать Вам огромное спасибо за ваш труд и за ту бесценную информацию, которую Вы двигаете в массы.
Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению. ):
яч. E16 =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0))
Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 — это тоже делает отчет удобнее. Теперь можно «играться» с разными значениями, просто выбирая их из выпадающего списка.
Афтар молодец! Куплю электронную книгу дабы поддержать энтузиазм
Добрый день, Николай. Функция ПОИСКПОЗ просматривает массив сверху вниз и, соответственно, возвращает первый порядковый номер аргумента:
| Вася | Миша | 2 |
| Миша | ||
| Маша | ||
| Жора | ||
| Миша | ||
| Валя |
Пример (скрин) прилагается. Бьюсь несколько дней, но никак не получается, чтобы найти функцию, указывающую последний порядковый номер соответствующего аргумента в массиве. В нашем примере это «5». Подскажите, пожалуйста, функцию для решения этой задачи. Заранее спасибо!
Как реализовать функции ИНДЕКС и ПОИСКПОЗ в VBA ?
При использовании этой формулы в работе с датами, выдает результат 0.1.1900 ( при пустой исходной ячейки) и #Н/Д(в случаях пустых всех заданных диапазонов)
Какую формулу можно дописать, чтобы при отсутствии исходных данных выдавал пусто, вместо самой первой даты в экселе?
P/S На функцию еслиошибка реагирует только #Н/Д , а пустую ячейку все равно выдает как 0.1.1900
СПАСИБО:<>
Добрый день!
Помогите пожалуйста по стоить формулу.
— есть таблица с данными: список товаров и столбцы магазинов с оборотами по ним
-среднее выводиться с отдельную ячейку С69
задача выводить рядом со средним какой товар = среднему значению и рядом какой магазин
B3:B61= это товары , С69 искомое значение , C3:C61= столбец магазинов где ищет.(НО ИХ 20)
Проблема в том, что выводиться только по одной колонке в формуле, а необходимо искать по всем 20.
Добрый день! Простите, если глупость спрашиваю, но как из столбца цифр выбрать (просуммировать) только те, которые одновременно больше например, 10, но меньше 20.
Т.е. как-то так: СУММЕСЛИ(А2:А30; И(«>=10»; «<20») )
Понимаю, что написанное красным неправильно. подскажите как одновременно учесть оба условия. СУММЕСЛИМН тоже лишь перечисляет критерии, выбирая сначала все числа больше 10, а потом ещё все меньше 20, а нужно только числа от 10 до 20.
Большое спасибо! Очень помогли!
Большое вам человеческое СПАСИБО!
Давно пользуюсь индексом и поиском позиции, но просто скопировав из чужого примера, меняя ссылки на ячейки, т.к. вообще не понимала как эти функции работают, а встроенная справка в Excel не дает понятной информации.
С помощью вашего примера разобралась с этой функцией: это так легко, просто и безумно полезно. 
Доброго времени суток!
А как быть, если нужно найти максимальное значение?
| Дата | А | В | Г |
| 01.02 | 1 | 1 | 1 |
| 02.02 | 3 | 3 | 1 |
| 03.02 | 1 | 1 | 2 |
| 04.02 | 1 | 1 | 1 |
<=ПОИСКПОЗ("1"&"1"&"1";&[А]&[В]&[Г];0)>— ПОИСКПОЗ находит первое значение
Николай доброго времени суток.
Прошу подсказать как найти все уникальные значения и объединить их как текст в одной ячейке по типу изделия.
| уникальный тип изделия | результат |
| С1 | 785; 786; 787; 788; 789; 790 |
| С2 | 791; 792; 793; 794; 795; 796; 797; 798; 799; 800 |
| С3 | 801; 802; 803; 804; 805; 806; 807; 808; 809; 810; 811; 812 |
| С4 | 813; 814; 815; 816; 817; 818; 819; 820; 821; 822; 823; 824; 825; 826 |
| С5 | 827; 828; 829; 830; 831; 832; 833; 834; 835; 836; 837; 838; 839; 840 |
| С6 | 841; 842; 843; 844; 845; 846; 847; 848; 849; 850 |
| С7 | 851; 852; 853; 854; 855; 856; 857; 858; 859; 860; 861; 862 |
| С8 | 863; 864; 865; 866; 867; 868 |
исходная таблица
| 785 | С1 |
| 786 | С1 |
| 787 | С1 |
| 788 | С1 |
| 789 | С1 |
| 790 | С1 |
| 791 | С2 |
| 792 | С2 |
| 793 | С2 |
| 794 | С2 |
| 795 | С2 |
| 796 | С2 |
| 797 | С2 |
| 798 | С2 |
| 799 | С2 |
| 800 | С2 |
| 801 | С3 |
| 802 | С3 |
| 803 | С3 |
| 804 | С3 |
| 805 | С3 |
| 806 | С3 |
| 807 | С3 |
и т.д.
Добрый день. вопрос
не получается найти значение через эти формулы.
может тогда подскажете решение?
Есть строка (не столбец) значений: 2030, 2000, 2050, 2100, 2000.
среднее значение их 2036
мне нужно рядом с ними сделать выборку значения максимально приближенного к среднему значению — и этим значением является 2030
ИНДЕКС+ПОИСКПОЗ — находит почему-то только 2000 (при значении»+1″)
при замене на «-1» — выдает Н/Д
0 — даже не ставлю, т.к. точного значения в строке нет
Excel как найти дату в диапазоне приближенную
Предположим, у вас есть список дат, смешанных с сегодняшней датой. Чтобы найти дату закрытия до сегодняшнего дня в этом списке, вам может потребоваться использовать формулы. Возможны две ситуации: одна определяет дату, ближайшую к сегодняшнему дню в прошлом, а другая — ближайшую к сегодняшнему дню в будущем. В этой статье мы покажем вам, как справиться с этими двумя проблемами.
Поиск ближайшей к сегодняшней дате даты в Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Как показано на скриншоте ниже, сегодняшняя дата — 1 января 29 года, пожалуйста, сделайте следующее, чтобы найти прошлое, ближайшее к сегодняшней дате.
1. Выберите пустую ячейку, например ячейку B2, чтобы найти результат.
2. Скопируйте и вставьте формулу. = МАКС (($ A $ 2: $ A $ 18 в Панель формул, а затем нажмите Ctrl + Shift + Enter ключи одновременно.
Как показано на приведенном выше снимке экрана, вы можете видеть, что последняя дата, ближайшая к сегодняшней дате 1, заполнена в ячейке B26.
Поиск ближайшей даты к сегодняшнему дню в Excel
В этом разделе мы покажем вам, как найти ближайшую к сегодняшнему дню дату в будущем в Excel.
1. Выберите пустую ячейку B2, скопируйте и вставьте формулу. = МИН (ЕСЛИ (A2: A18> СЕГОДНЯ (); A2: A18)) в Панель формул, а затем нажмите Ctrl + Shift + Enter клавиши одновременно. Смотрите скриншот:
Тогда вы получите ближайшую к сегодняшнему дню дату в будущем в ячейке B2.
Заметки:
1. В формулах измените диапазон по своему усмотрению;
2. Если формат даты не отображается, измените формат ячейки на «Дата» после расчета.
Статьи по теме:
Лучшие инструменты для офисной работы
Превратите часы в минуты с Kutools for Excel!
Готовы ускорить свои задачи в Excel? Используйте силу Kutools for Excel — ваш лучший инструмент для экономии времени. Оптимизируйте сложные задачи и скользите по своим данным, как профессионал. Испытайте Excel с молниеносной скоростью!
Зачем тебе Kutools for Excel
���� Более 300 мощных функций: Kutools содержит более 300 расширенных функций, упрощающих вашу работу в более чем 1500 сценариях.
�� Превосходная обработка данных: объединяйте ячейки, удаляйте дубликаты и выполняйте расширенные преобразования данных — и все это без особых усилий!
���� Эффективные пакетные операции: Зачем прилагать дополнительные усилия, если можно работать с умом? С легкостью импортируйте, экспортируйте, комбинируйте и корректируйте данные.
�� Настраиваемые диаграммы и отчеты: доступ к множеству дополнительных диаграмм и создание информативных отчетов, рассказывающих историю.
���� Мощная панель навигации: Получите преимущество благодаря надежному диспетчеру столбцов, диспетчеру рабочих листов и пользовательскому избранному.
. Семь типов раскрывающихся списков: Упростите ввод данных с помощью раскрывающихся списков различных функций и типов.
�� Удобный: простой инструмент для начинающих и мощный инструмент для экспертов.