Как рассчитать знак зодиака в Google-таблице?

Question

[Bri0ni]

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

Answer 1

[Alexander Ivanov]

Попробуйте VLOOKUP

Например, у вас есть справочник сотрудников и знаков зодиака. В последнем нужно сделать индексы, чтобы поиск происходил в заданном диапазоне. Индекс - это целое число, которое собирается из единицы и начала действия знака в календаре. Например, Лев начинается 23 июля, значит его индекс будет записан как число, состоящее из цифр 1, 06, 23, т.е. 10623.

Чтобы узнать индекс для сотрудника, нужно вызвать формулу VALUE(TEXT(A1;"1MMDD"), где в A1 дата рождения сотрудника.

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

=ARRAYFORMULA(IF(B2:B;IFERROR(
  VLOOKUP(
    VALUE(TEXT(B2:B;"1MMDD"));
    SORT({'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L};1;1);
    2;
    1
  )
);""))

Проверяем, чтобы в B2:B было значение, иначе выводим пустую строку, далее в составном диапазоне

{'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L}

ищем индекс по колонке J:J, т.е. "Западная астрология (вариант II)".

Чтобы вывести еще и стихии с планетами, можно добавить список колонок для вывода

=ARRAYFORMULA(IF(B2:B;IFERROR(
  VLOOKUP(
    VALUE(TEXT(B2:B;"1MMDD"));
    SORT({'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L};1;1);
    {2\3\4\5\6};
    1
  )
);""))

• Пример в Таблице https://docs.google.com/spreadsheets/d/1nsFTjp96IR...
  

Comments

[lebedev_dv]

Добрый день! Есть проблема с козерогами, так как они разделяют календарный год. Проблему можно решить добавлением еще одной строки "Козерог" с индексом 10101

[Alexander Ivanov]

lebedev_dv, или добавить смещение на нужное количество дней. Добавил в пример ваше предложение по правке. Спасибо.

[vishdecleir]

Мне пришлось зарегиться на портале, чтобы оставить этот комментарий с благодарностью.
Я ничерта не поняла из статьи и мне пришлось просто скопировать формулу из вашего примера (уже в самой гугл таблице), просто скорректировать диапазоны и ВУАЛЯ! все работает)

ТАК ЧТО СПАСИБО ОГРОМЕННОЕ!

[Alexander Ivanov]

vishdecleir, отлично!

[Mehabr]

на одном форуме нашел чуть более простой способ:
=ПРОСМОТР(ДАТА(1900;МЕСЯЦ(A1);ДЕНЬ(A1));{1;21;51;80;111;142;173;204;235;266;297; 327;356};{"Козерог";"Водолей";"Рыбы";"Овен";"Телец";"Близнецы";"Рак";"Лев";"Дева ";"Весы";"Скорпион";"Стрелец";"Козерог"})

для упрощения загнал ее через пункт меню Данные - Именованные функции в свою функцию =ЗНАКЗОДИАКА () на случай если придется что-то подправить. Думаю еще добавить туда условие ISBLANK, а то по умолчанию формула выдает Козерог если ячейка пустая....

[Alexander Ivanov]

Mehabr, этот способ не "проще", эта та же самая формула. Ваш способ просто не обрабатывает ошибки и работает с массивом, который в нее вписан, причем вместо понятных дат вписаны номера дней в году. Уже не так красиво звучит "именованная функция", да?

[Mehabr]

Alexander Ivanov, простота в том что не надо создавать еще один лист-справочник в котором перечислены все знаки зодиака. Конечно при этом теряется богатство функционала который есть в вашем примере, иконки, планеты, лунные фазы и тп. Но зато все работает из одной ячейки. Мне, как дилетанту, показалось это проще. То что это одна и таже формула, кто же спорит? Так что ваш чрезмерный сарказм излишен.

[Alexander Ivanov]

Mehabr, хорошо, половину заберу.