сократи формулу Google Sheets чтобы было лаконичнее но не меняй функционал…
- User
сократи формулу Google Sheets чтобы было лаконичнее но не меняй функционал
=ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("🎂🎈Сегодня день рождения у🎉🥳: " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & 'Дни рождения'!G3:G & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ(); "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("🎈🎈Завтра день рождения у🎈🎈: " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & 'Дни рождения'!G3:G & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+1; "dd.mm")) ); "") &
ЕСЛИ(ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 2 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+2; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 3 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+3; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 4 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+4; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 5 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+5; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 6 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+6; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 7 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1; ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+7; "dd.mm")) ); "") <> ""; "На этой неделе день рождения у: " & СИМВОЛ(10) & ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 2 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+2; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 3 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+3; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 4 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+4; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 5 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+5; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 6 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1 & СИМВОЛ(10); ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+6; "dd.mm")) ); "") &
ЕСЛИОШИБКА(TEXTJOIN(СИМВОЛ(10); ИСТИНА; FILTER("(через 7 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1; ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()); ПСТР('Дни рождения'!E3:E; 3; 2); ПСТР('Дни рождения'!E3:E; 5; 2)); "dd.mm") = ТЕКСТ(СЕГОДНЯ()+7; "dd.mm")) ); ""))
- Assistant
Чтобы сделать вашу формулу более лаконичной, можно использовать функцию
LET, которая позволяет определить повторяющиеся выражения один раз и использовать их несколько раз внутри формулы. Это значительно сокращает длину формулы и улучшает её читаемость.Вот оптимизированная версия вашей формулы с сохранением функционала:
=LET( даты, TEXT(DATE(YEAR(СЕГОДНЯ()), ПСТР('Дни рождения'!E3:E, 3, 2), ПСТР('Дни рождения'!E3:E, 5, 2)), "dd.mm"), сообщения, TEXTJOIN(СИМВОЛ(10), ИСТИНА, { IFERROR( FILTER("🎂🎈Сегодня день рождения у🎉🥳: " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & 'Дни рождения'!G3:G, даты = TEXT(СЕГОДНЯ(), "dd.mm")), "" ); IFERROR( FILTER("🎈🎈Завтра день рождения у🎈🎈: " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & 'Дни рождения'!G3:G, даты = TEXT(СЕГОДНЯ()+1, "dd.mm")), "" ); IFERROR( FILTER("(через 2 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1, даты = TEXT(СЕГОДНЯ()+2, "dd.mm")), "" ); IFERROR( FILTER("(через 3 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1, даты = TEXT(СЕГОДНЯ()+3, "dd.mm")), "" ); IFERROR( FILTER("(через 4 дня): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1, даты = TEXT(СЕГОДНЯ()+4, "dd.mm")), "" ); IFERROR( FILTER("(через 5 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1, даты = TEXT(СЕГОДНЯ()+5, "dd.mm")), "" ); IFERROR( FILTER("(через 6 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1, даты = TEXT(СЕГОДНЯ()+6, "dd.mm")), "" ); IFERROR( FILTER("(через 7 дней): " & СИМВОЛ(10) & 'Дни рождения'!C3:C & СИМВОЛ(10) & "Исполнится: " & 'Дни рождения'!G3:G+1, даты = TEXT(СЕГОДНЯ()+7, "dd.mm")), "" ) }), ЕСЛИ(сообщения <> "", "На этой неделе день рождения у: " & СИМВОЛ(10) & сообщения, "") )Объяснение изменений:
-
Функция
LET:- Определяет промежуточные переменные, такие как
датыдля преобразования дат из столбца E. - Определяет переменную
сообщения, которая объединяет все отфильтрованные сообщения для разных дней.
- Определяет промежуточные переменные, такие как
-
Использование массива
{ ... ; ... ; ... }:- Внутри
TEXTJOINиспользуется массив, где каждый элемент соответствует одному дню (сегодня, завтра, через 2 дня и т.д.). - Для каждого дня применяется
FILTERс соответствующим условием. Если данные не найдены, используетсяIFERRORдля возврата пустой строки.
- Внутри
-
Упрощение структуры:
- Удалены повторяющиеся вызовы
TEXTJOINиIFERRORвнеLET, что делает формулу короче и чище. - Все фильтры объединены в одну переменную
сообщения, которая затем используется для формирования окончательного текста.
- Удалены повторяющиеся вызовы
Преимущества:
- Читаемость: Формула стала более структурированной и легче понимается благодаря использованию
LET. - Краткость: Удалены избыточные повторения, что сократило общую длину формулы.
- Поддерживаемость: Внесение изменений или добавление новых дней становится проще, так как вся логика организована в одном месте.
Дополнительные рекомендации:
- Использование именованных диапазонов: Если столбцы используются часто, можно создать именованные диапазоны для них, чтобы сделать формулу ещё более понятной.
- Проверка корректности дат: Убедитесь, что даты в столбце E имеют правильный формат для корректного извлечения месяца и дня.
Надеюсь, эта оптимизация поможет вам эффективно управлять формулами в Google Sheets!
-