Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Найти ближайший день рождения
 
#1
27.09.2021 22:11:45
Помогите, в инете есть какие-то решения, но что-то все не то и не знаю, как решить задачу
На листе в столбцах
A - №
B - имя
C - день рождения

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

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

заранее спасибо, на всяк случай пример
 
 
 
#2
27.09.2021 22:30:12
snatg, уточните, что Вы подразумеваете под ближайшим ДР?

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#3
27.09.2021 22:32:16
JayBhagavan, имею ввиду ближайший день, когда кого-то нужно поздравлять - сегодня, завтра, послезавтра и т.д.
 
 
 
#4
27.09.2021 22:45:12
snatg, можно и без доп. столбцов.
В H2 и протянуть вниз насколько надо:
Аналогично в I2:
и в J2:
=ЕСЛИ(ЕЧИСЛО($H2);РАЗНДАТ($H2;СЕГОДНЯ();"Y")+1;"")
Проверяйте.
Изменено: JayBhagavan - 28.09.2021 00:51:30 (исправлены формулы)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#5
27.09.2021 22:56:25
Цитата
JayBhagavan написал:
В H2 и протянуть вниз насколько надо:
эта формула выводит 18.09.1980...
ну оно уже прошло, так и смысл же в том, чтобы выводилось без учета года рождения
при растягивании формулы там пустые ячейки

результат там должен быть такой:
сегодня:
27.09.2021 Петров5
а завтра:
28.09.2021Водкин6
Водкин2
Иванов2
Иванов4
 
 
 
#6
27.09.2021 23:37:11
=IFERROR(INDEX(B:B;SMALL(IF((DAY(H2)=DAY(C2:C73))*MONTH(H2)=MONTH(C2:C73);ROW(C2:C73));ROWS(I$2:I2)));"")
=IFERROR(DATEDIF(INDEX(C:C;SMALL(IF((DAY(H2)=DAY(C2:C73))*MONTH(H2)=MONTH(C2:C73);ROW(C2:C73));ROWS(I$2:I2)));H2;"Y");"")
Но беда с рожденными 29 февраля.
Изменено: БМВ - 27.09.2021 23:38:50
По вопросам из тем форума, личку не читаю.
 
 
 
#7
27.09.2021 23:49:12
72 Водкин6 06.01.2022
Похоже, у меня календарь неправильный... )
 
 
 
#8
28.09.2021 00:01:40
БМВ, индекснуть людей у которых день рожденья в определенный день - это достаточно легко, но спасибо
но мне нужна формула для того, чтобы найти этот ближайший день, т.е. не формула сегодня(), а именно формула, которая найдет ближайший день, в который есть дни рождения, ну т.е. например, если сегодня 29.09, то след. д.р. чтоб показывался - 4.10

Цитата
БМВ написал: Но беда с рожденными 29 февраля.
пока таких не встречала)))

vikttur, это план)))

В общем, я тут подумала, ведь задача вроде простая... нужно экселю сказать: найди в списке (столбец F) дату равную или большую чем сегодня, и все, только я не знаю, как ему это сказать)))
 
 
 
#9
28.09.2021 00:16:20
Может быть, так устроит?
 
 
 
#10
28.09.2021 00:24:24
Цитата
snatg написал: нужно экселю сказать: найди в списке (столбец F) дату равную или большую чем сегодня
Давайте подскажем Excel'ю:
=ДАТА(ГОД($E$1)+(МЕСЯЦ(C2)<МЕСЯЦ($E$1));МЕСЯЦ(C2);ДЕНЬ(C2))
Прошедшие даты текущего месяца игнорируем. Вернее, показываем, но... кому они надо? )
Формулой массива находим номер строки:
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ($D$2:$D$73-$E$1={0;1;2;3;4};СТРОКА($D$2:$D$73));СТРОКА(A1));)
Дальше просто
 
 
 
#11
28.09.2021 00:45:46
snatg, исправил формулы в сообщении №4.
+++
Добавил проверку, чтобы брать в расчёт только ДР, которые меньше СЕГОДНЯ().
В H2 и протянуть вниз насколько надо:
Аналогично в I2:

и в J2 (без изменений):
=ЕСЛИ(ЕЧИСЛО($H2);РАЗНДАТ($H2;СЕГОДНЯ();"Y")+1;"")
Изменено: JayBhagavan - 28.09.2021 01:02:06

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#12
28.09.2021 01:23:42
Светлый, спасибо, круто работает
JayBhagavan, там кстати не выводятся дни рождения, которые выпадают на сегодня, только последующие.
ну да ладно, я уже решила задачу, спасибо
vikttur, тоже спасибо

посмотрела, какие жуткие формулы  :D
и они натолкнули меня на мысли и я решила с этим файлом уйти в гугл таблицу))) (наверное в последнем экселе это тоже легко делается)
там все так просто получилось, буквально в одну формулу
Код
=SORTN(FILTER(A:C;A:A>=TODAY());10;0;1;TRUE)
в A:A я вставила даты дней рождений с заменой на текущий год (ну т.е. перенесла столбец F)

ну и добавила всякие там "дней до", "лет" и получилась красота, еще раз спасибо)
Изменено: vikttur - 28.09.2021 11:17:44
 
 
 
#13
28.09.2021 02:25:53
snatg, очередная попытка.
В H2 и протянуть вниз насколько надо:
=ЕСЛИОШИБКА(ИНДЕКС($C:$C;АГРЕГАТ(15;6;СТРОКА($C$2:$C$73)/(ДАТА(ГОД(СЕГОДНЯ())+(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ($C$2:$C$73);ДЕНЬ($C$2:$C$73))<СЕГОДНЯ());МЕСЯЦ($C$2:$C$73);ДЕНЬ($C$2:$C$73))/($C$2:$C$73<=СЕГОДНЯ())-СЕГОДНЯ()<8);СТРОКА()-СТРОКА($1:$1)));"")
Аналогично в I2:
=ЕСЛИОШИБКА(ИНДЕКС($B:$B;АГРЕГАТ(15;6;СТРОКА($C$2:$C$73)/(ДАТА(ГОД(СЕГОДНЯ())+(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ($C$2:$C$73);ДЕНЬ($C$2:$C$73))<СЕГОДНЯ());МЕСЯЦ($C$2:$C$73);ДЕНЬ($C$2:$C$73))/($C$2:$C$73<=СЕГОДНЯ())-СЕГОДНЯ()<8);СТРОКА()-СТРОКА($1:$1)));"")
и в J2:
=ЕСЛИ(ЕЧИСЛО($H2);РАЗНДАТ($H2;СЕГОДНЯ();"Y")+(ДАТА(ГОД(СЕГОДНЯ()+(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ($H2);ДЕНЬ($H2))<СЕГОДНЯ()));МЕСЯЦ($H2);ДЕНЬ($H2))>СЕГОДНЯ());"")

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#14
28.09.2021 07:30:35
=IFERROR(INDEX(B:B;MOD(SMALL(IF(DATE(YEAR(H$2);MONTH($C$2:$C$73);DAY($C$2:$C$73))>=H$2;DATE(YEAR(H$2);MONTH($C$2:$C$73);DAY($C$2:$C$73))+ROW($C$2:$C$73)%%);ROWS(I$2:I2));1)/1%%+0,1);"")
=IFERROR(DATEDIF(INDEX(C:C;MOD(SMALL(IF(DATE(YEAR(H$2);MONTH($C$2:$C$73);DAY($C$2:$C$73))>=H$2;DATE(YEAR(H$2);MONTH($C$2:$C$73);DAY($C$2:$C$73))+ROW($C$2:$C$73)%%);ROWS(I$2:I2));1)/1%%+0,1);H$2;"Y");"")
По вопросам из тем форума, личку не читаю.
 
 
 
#15
28.09.2021 07:34:18
У меня есть вот такой простенький файл, делюсь может нужен а может и нет.
Занимайся тем чем увлекаешься!
 
 
 
#16
28.09.2021 11:36:04
ну и я в коллекцию подкину, хоть автор и нашел уже решение
можно хоть прямо сортировать общий список по возрастанию по ближайшей дате ДР
хоть видеть вывод ближайших в отдельной таблице
и счастливчики родившиеся 29 февраля - отмечают ДР 1 марта
Изменено: andylu - 28.09.2021 11:37:18
 
 
 
#17
28.09.2021 17:19:19
Цитата
JayBhagavan написал: очередная попытка.
о, сейчас все работает)
спасибо)

Шохбозбек Абдуфаттоев, о, интересная идея менять не просто год на текущий, а на ближайший, когда нужно поздравлять, спасибо)

andylu, спасибо, тоже интересное решение!
 
 
 
#18
28.09.2021 17:32:17
Цитата
находить ближайший к сегодня день рождения и выводить всех людей у кого д.р.
На Лист1 кнопка "Ближайшие на 30 дней ДР"
 
 
 
#19
28.09.2021 17:41:09
Kuzmich, красиво получилось, спасибо!
 
 
 
Страницы: 1
Наверх