Поиск даты в диапазоне дат excel

Обновлено: 07.01.2025

Помогите с разрешением задачи.

Задача:
Есть таблица с данными:
Таблица 1
Наименование Значение период
Слива В 01-10.01.2015
Груша В 01-10.01.2015
Яблоко В 01-10.01.2015
Слива А 11-20.01.2015
Груша А 11-20.01.2015
Яблоко А 11-20.01.2015

В данной таблице одно и тоже наименование, но в разные периоды имеет разное значение

Необходимо что бы во второй таблице

Таблица 2
Наименование Дата Значение
Слива 05.01.2015
Яблоко 15.01.2015

В столбце "значение" в зависимости от даты вычислялось значение соответствующее периоду из первой таблицы.

Помогите с разрешением задачи.

В данной таблице одно и тоже наименование, но в разные периоды имеет разное значение

Необходимо что бы во второй таблице

Таблица 2
Наименование Дата Значение
Слива 05.01.2015
Яблоко 15.01.2015

В столбце "значение" в зависимости от даты вычислялось значение соответствующее периоду из первой таблицы.

Помогите с разрешением задачи.

В данной таблице одно и тоже наименование, но в разные периоды имеет разное значение

Необходимо что бы во второй таблице

Таблица 2
Наименование Дата Значение
Слива 05.01.2015
Яблоко 15.01.2015

В столбце "значение" в зависимости от даты вычислялось значение соответствующее периоду из первой таблицы.

Спасибо. Автор - Салим
Дата добавления - 21.01.2015 в 18:54

Салим, название темы ни о чём, придумайте что-нибудь более конкретное. Салим, название темы ни о чём, придумайте что-нибудь более конкретное. Pelena

Палена, Вы лучше бы решение подсказали, а название темы не главное.

Но в прочем решение найдено.

Спасибо всем кто просматривал)))

Палена, Вы лучше бы решение подсказали, а название темы не главное.

Но в прочем решение найдено.

Спасибо всем кто просматривал))) Салим

Палена, Вы лучше бы решение подсказали, а название темы не главное.

Но в прочем решение найдено.

Спасибо всем кто просматривал))) Автор - Салим
Дата добавления - 23.01.2015 в 14:18

Салим, здравствуйте!
Если обращаетесь к человеку, то особое внимание уделите правильности написания его Имени или НИКнейма.
Уважаемая Pelena является модератором форума, а замечания модераторов не обсуждаются.
Вы правила форума читали? Название темы это не просто так.
Елену полностью поддерживаю. Название темы не о чем! Салим, здравствуйте!
Если обращаетесь к человеку, то особое внимание уделите правильности написания его Имени или НИКнейма.
Уважаемая Pelena является модератором форума, а замечания модераторов не обсуждаются.
Вы правила форума читали? Название темы это не просто так.
Елену полностью поддерживаю. Название темы не о чем! VEKTORVSFREEMAN

"Опыт - это то, что получаешь, не получив того, что хотел"

Добрый день, VEKTORVSFREEMAN.

Дискуссия не о чем. Сюда люди обращаются за разрешением возникших проблем с решением задач, а название темы подразумевает как автор понимает, что ему нужно. А как известно понимание у каждого свое.

То что Вы поддерживаете пользователя/модератора Pelena Ваше личное дело.

Добрый день, VEKTORVSFREEMAN.

То что Вы поддерживаете пользователя/модератора Pelena Ваше личное дело. Салим

То что Вы поддерживаете пользователя/модератора Pelena Ваше личное дело. Автор - Салим
Дата добавления - 23.01.2015 в 14:31

Возникла следующая проблема: есть диапазоны дат, есть также искомое значение (конкретная календарная дата). Надо определить конкретный диапазон дат, который содержит искомую календарную дату и получить значение ячейки напротив нужного диапазона.

Пример в приложении

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Поиск значения в диапазоне
Добрый день! Помогите пожалуйста. В данном конкретном случае необходимо в лист1 столбец Е.

Поиск значения в диапазоне ячеек и вывод результата в ячейку
Доброго времени суток. Поиск не помог мне, решил создать тему. Исходные данные: есть 2.

Поиск совпадений в диапазоне и поставление значения соседней от совпавшей ячейки
Формула находится в ячейке Е1 и ищет совпадения значения ячейки А1 со значением одной из ячеек в.

Поиск дат в периоде дат (целиком или частично)
Добрый день! У Меня есть проблема, не могу подобрать формулу по поиску определенного периода даты.

Большое спасибо Все имена заняты и DV68. Похоже все работает.

Как як понял, используются формулы массивов. Не могли бы вы сказать как они работают конкретно в этом месте (I5>=B1:B5)*(I5<=C1:C5) ?

Почему при использовании =ПОИСКПОЗ(1;. в качестве искомого значения используется 1?
Почему при использовании =ПРОСМОТР(2;. в качестве искомого значения используется 2?

Не могли бы вы сказать как они работают конкретно в этом месте (I5>=B1:B5)*(I5<=C1:C5) ? Два масссива, содержащие значения ИСТИНА или ЛОЖЬ, перемножаются. В результате получается массив, состоящий из нулей и, возможно, содержащий одну единицу.
Почему при использовании =ПОИСКПОЗ(1;. в качестве искомого значения используется 1?

Именно поэтому здесь используется единица.

Можно ведь, кстати, посмотреть поэтапную работу формулы, там все очень наглядно.

Для 2007: Формулы >> Зависимости формул >> Вычислить формулу.
Для 2003: Сервис >> Зависимости формул >> Вычислить формулу.

Все имена заняты, спасибо огромное. не знал, что можно применять множить логические значения. Также не знал, что можно создавать по сути новый массив не в ячейке сбоку а, как бы, в памяти и с ним проводить операции (в том числе поиска по созданному в памяти массиву).

Можно ведь, кстати, посмотреть поэтапную работу формулы, там все очень наглядно.

Пробовал смотреть выполнение формулы, но после того как появились ИСТИНА и ЛОЖЬ я упустил цепочку происходящего и дальше уже ничего не понимал.

Еще раз спасибо, что просветили.

С уважение,
Виктор Королев

Если не помогает просмотр вычисления формулы, то можно выделить необходимый кусок в формуле, например (I5>=B1:B5)*(I5<=C1:C5), и нажать F9. Сразу увидите массив, который получился при вычислении.
Кстати формула с ПРОСМОТРом не формула массива, но помедленнее будет, чем ИНДЕКС(. ПОИСКПОЗ(. ))

У меня почти такая же проблема что у Buchhalter:
есть диапазоны дат, есть также искомое значение (конкретная календарная дата). Надо определить конкретный диапазон дат, который содержит искомую календарную дату и получить значение ячейки напротив нужного диапазона.
напротив ячейки содержащую дату нужно ставить значение из массива. В моем случае это курс доллара на эту дату.

В приведенном примере у Buchhalter только два столбца с датами, а у меня их много.
Я пытался пользоваться формулой приведенной Все имена заняты, не получилось.

Во вложенном файле я указал два варианта:
Вариант 1 - в массиве, содержащую даты, указаны все даты периода
Вариант 2 - в массиве, содержащую даты, указаны только первая и последняя дата периода.

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

При создании формул массива, операторы массива могут значительно увеличить время расчета. В этой короткой главе рассматриваются два примера, в которых обычная формула справляется с задачей значительно быстрее формулы массива.

Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.

Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)

Давайте подробнее изучим работу этих пяти формул.

Если вы можете позволить себе вспомогательный столбец, функция СЧЁТЕСЛИ будет самым простым решением.
Функция МЕСЯЦ возвращает число между 1 и 12, а функция ГОД – число (год).
Хотя Excel требует, чтобы аргумент функции МЕСЯЦ был представлен датой в числовом формате, этот аргумент может распознать и текст. Однако МЕСЯЦ(Окт) вернет ощибку, а вот если добавить к названию месяца любое число, например, 1, то Excel справится. Используйте, как в формуле выражение Окт1, заданное фрагментом F8&1, или 1Окт, заданное фрагментом 1&F8.
Формулы с вспомогательными столбцами как правило работают быстрее.

Если у вас Excel 2007 или более поздний, вы можете использовать функции СЧЁТЕСЛИМН и КОНМЕСЯЦА.
Вам даны год (в виде числа) и месяц (как текст). Это означает, что вы можете вычислить дату начала и конца месяца, а затем определить даты, попажающие между ними.
Месяц всегда начинается с первого числа, так что вы можете создать нижнюю границу диапазона конкатенацией: " >=1 " &F8&E8. Операции конкатенации возвращают текст, но это не страшно, т.к. функция СУММЕСЛИМН понимает даты в виде текста.
Вы используете функцию КОНМЕСЯЦА с аргументом число_месяцев равным нулю; это позволяет получить последнюю дату текущего месяца. Функция КОНМЕСЯЦА является динамической: она возвращает 28 или 29 для февраля и 30 или 31 для любого другого месяца.
Эта формула является самой быстрой, если вам нужно получить решение в одной ячейке.

Если у вас Excel версии младше 2007 г., вы можете использовать две функции СЧЁТЕСЛИ, одну – для верхнего диапазона, вторую – для нижнего. Фокус в том, чтобы сначала сосчитать все значения, которые равны или меньше верхней границы, а затем вычесть все значения, которые меньше нижней границы.
В Excel 2003 или более ранней, чтобы добавить функцию КОНМЕСЯЦА, вам нужно выбрать Инструменты → Надстройки → Анализ Данных.
Эта формула работает быстрее, чем формулы [4] и [5].

Функции МЕСЯЦ и ГОД возвращают числа, извлекая их из порядкового номера даты.
Далее сравниваются два фрагмента, каждый полкченный конкатенацией.

Функция ТЕКСТ используется для представления чисел в виде текста. Второй аргумент этой функции – формат – определяет, как будет представлено число. Вы может конвертировать весь столбец А в текст, состоящий из 7 символов: 3 буквы месяца и 4 цифры года.

Нахождение объема продаж за год. На рис. 12.4 показан пример несоответствие формата года в критерии Е6 (число) и формата дат в диапазоне А2:А6 (порядковый номер). Цель – найти сумму продаж за год. На рисунке представлены шесть вариантов формул, которые могут решить задачу. Обратите внимание, что в формулах [1] и [2] критерии начала и конца года жестко зашиты в коде, т.к. они не могут изменяться. Это 1/1 и 31/12). Формулы размещены на рисунке в порядка увеличения скорости работы.

Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)

Основное назначение этой функции в том, чтобы искать позицию заданного элемента в наборе значений. Чаще всего она применяется для поиска порядкового номера ячейки в диапазоне, где лежит нужное нам значение.

Синтаксис этой функции следующий:

=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )

Что_ищем - это значение, которое надо найти
Где_ищем - это одномерный диапазон или массив (строка или столбец), где производится поиск
Режим_поиска - как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий - поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

Поиск первой или последней текстовой ячейки

Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:

Числа и пустые ячейки в этом случае игнорируются.

Поиск ближайшего числа или даты

Если последний аргумент задать равным 1 или -1, то можно реализовать поиск ближайшего наименьшего или наибольшего числа. Таблица при этом обязательно должна быть отсортирована по возрастанию или убыванию соответственно. В общем и целом, это чем-то похоже на интервальный просмотр у функции ВПР (VLOOKUP) , но там возможен только поиск ближайшего наименьшего, а здесь - есть выбор.

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

Связка функций ПОИСКПОЗ и ИНДЕКС

Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией - ИНДЕКС (INDEX) , которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, "левый ВПР".

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:

Принципиальное ограничение функции ПОИСКПОЗ состоит в том, что она умеет искать только в одномерных массивах (т.е. строчке или столбце), но никто не запрещает использовать сразу два ПОИСКПОЗа вложенных в ИНДЕКС, чтобы реализовать двумерный поиск по строке и столбцу одновременно:

Читайте также: