Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Поиск даты в формате МЕСЯЦ()&ГОД() в диапазоне дат, Оптимизация поиска
 
Добрый день!

Прошу помочь с оптимизацией поиска даты в формате МЕСЯЦ()&ГОД() в диапазоне с датами.
0. Задача: если в массиве (B1:B34) есть хоть одна дата за февраль 2017, то если указать дату в ячейке A2: 02/%ЛЮБОЙ_ДЕНЬ%/2017, то формула выведет "Да", хоть и конкретно такой даты в массиве нет.
1. Очевидный способ - достроить вспомогательный столбец (C2:C35), который будет преобразовывать дату (A2) из диапазона (B1:B34) в нужный формат (например, МЕСЯЦ()&ГОД())
2. Формат такой выбрал, поскольку он самый простой, пробовал использовать: ДАТА(ГОД(),МЕСЯЦ(),1), но МЕСЯЦ()&ГОД() выглядит проще, т.е. привязки к формату нет.
3. Ну и сама формула поиска/подсчета:
Код
=ЕСЛИ(СЧЁТЕСЛИ(C2:C35,МЕСЯЦ(A2)&ГОД(A2))>0,"Да","Нет")
4. Пробовал сразу обращаться к массиву B1:B34, с помощью: МЕСЯЦ(B1:B34)&ГОД(B1:B34), но либо формула берет только первое значение из массива дат, либо возвращает ошибку.

Вопрос: есть возможность избежать создания вспомогательного столбца C?
Файл прикрепил.

P.S. Даты могут быть не упорядочены. Пустых ячеек не будет. Excel 2016.

Спасибо!
Изменено: andrewmr - 26 Мар 2018 10:29:17
 
формула массива
=ЕСЛИ(СЧЁТ(1/(ТЕКСТ(A2;"ММГГГ")=ТЕКСТ(B2:B35;"ММГГГ")))>0;"Да";"Нет")
с обычным вводом
=ЕСЛИ(СУММПРОИЗВ(Ч(ТЕКСТ(A2;"ММГГГ")=ТЕКСТ(B2:B35;"ММГГГ")))>0;"Да";"Нет")
или
=ТЕКСТ(СУММПРОИЗВ(Ч(ТЕКСТ(A2;"ММГГГ")=ТЕКСТ(B2:B35;"ММГГГ")));"\Да;;Нет")
Изменено: АlехМ - 26 Мар 2018 10:34:34
Алексей М.
 
АlехМ, ошибся то что назвал так столбец A.
В ячейке A2 будет дата не в формате МЕСЯЦ()&ГОД(), там может быть любая дата в привычном формате: 3/26/2018 (у меня англ. вариант дат). Т.е. преобразований даты в текст не требуется.

Попробовал Ваши обе формулы, к сожалению на дату с апрелем 2017, формула также ответила "Да", при этом в диапазоне апрельской даты нет. На этапе вычисления Вашей формулы заметил, что она обрабатывает не даты, а "ММГГГГ" и на любое значение выведет: Да. А третья формула вывела: "Да, Нет".  
 
Не так красиво как у АlехМ, но вот тоже формула:
Код
=ЕСЛИ(СУММПРОИЗВ((B2:B35>=ДАТА(ГОД(A2);МЕСЯЦ(A2);1))*1;(B2:B35<=ДАТА(ГОД(A2);МЕСЯЦ(A2);ДЕНЬ(КОНМЕСЯЦА(A2;0))))*1)>0;"Да";"Нет")
 
PooHkrd, Ваш вариант работает.
Всем спасибо большое! :)  
 
andrewmr, Если формула не работает, покажите это в файле
Алексей М.
 
АlехМ, Например, изменил значение ячейки A2 на 1/28/2017 (28 Января 2017), итого: формулы по прежнему выводят ответ: "Да". При этом в диапазоне B2:B35 нет дат за январь 2017.
 
andrewmr, У меня все 4 формулы от АlехМ из вашего примера - рабочие. Думаю, тут что-то связано с настройкой языковой локализации. Моя формула к форматам дат не обращается, поэтому в данной ситуации наверное оказалась более универсальной.
 
PooHkrd, Да, похоже на истину.
АlехМ, тогда прошу прощения, был не прав.
 
andrewmr, Вариант без ТЕКСТ()
=ЕСЛИ(СУММПРОИЗВ((ДАТА(ГОД(A2);МЕСЯЦ(A2);)<B2:B35)*(ДАТА(ГОД(A2);МЕСЯЦ(A2)+1;1)>B2:B35));"Да";"Нет")
Изменено: АlехМ - 26 Мар 2018 11:32:16
Алексей М.
 
указываем в А2 любое число месяца ищет все числа этого месяца
Код
=ЕСЛИ(СЧЁТЕСЛИМН(B2:B35;">="&КОНМЕСЯЦА(A2;-1)+1;B2:B35;"<="&КОНМЕСЯЦА(A2;0));"Да";"Нет")
Лень двигатель прогресса, доказано!!!
 
andrewmr, здравия желаю. Предлагаю так:

=ПРОСМОТР(;-ЧАСТОТА(ЧАСТОТА(B2:B35;A2);0);{"да";"нет"})
 
Akropochev, по условию задачи для даты 28.03.2017 формула должна выдавать результат "Да". Ваша формула пишет "нет". Потому что она проверяет наличие самого элемента в массиве, а нужно проверять наличие скажем так, компонентов элемента в компонентах массива.
 
Еще вариант на СУММПРОИЗВ (из первоначальной идеи автора топика)
=ЕСЛИ(--СУММПРОИЗВ((МЕСЯЦ(B2:B35)=МЕСЯЦ(A2))*(ГОД(B2:B35)=ГОД(A2)));"Да";"Нет")
 
на 29/02/2017 - выдает эпическую ошибку ))))
вот вариант формулы тоже самое что и у PooHkrd, только по другому ))
=ЕСЛИ(СЧЁТЕСЛИМН(   $B$2:$B$35;  ">="&ДАТА(ГОД($A$2);МЕСЯЦ($A$2);1);     $B$2:$B$35;     "<"&ДАТА(ГОД($A$2);МЕСЯЦ($A$2)+1;1))    >0;    "Да";   "Нет")
 
Цитата
Blood81 написал:
на 29/02/2017 - выдает эпическую ошибку ))))
Так такой даты не существует)
 
Если в столбце В даты без пропусков и сортированы по порядку. Как в примере.
=ЕСЛИ(ЕНД(ВПР(ДАТА(ГОД(A2);МЕСЯЦ(A2);1);B2:B35;;));"Нет";"Да")
Изменено: АlехМ - 26 Мар 2018 15:36:29
Алексей М.
Страницы: 1
Читают тему (гостей: 1)
Наверх