Сравните дату со списком праздников

Question

В Excel я хочу сравнить дату, скажем, столбец A1, со списком дат в матрице A2:F9 и сообщить, является ли это "праздником". У меня есть все выходные дни в матрице, где годы указаны в левой строке, а выходные дни указаны в верхней колонке. Первая группа ниже - все даты. У меня есть это собирается до 2050 года. Вторая группа - это матрица, где я показываю только некоторые праздники. Но я хочу сравнить, скажем, первое свидание «27.04.2008» в первой группе и в разделе "Отпуск", пометить ли его во второй группе ниже, поставив "Да" или "Отпуск"

Date        day    month    year   Holiday 
4/27/2018   Fri    Apr      2018     ??
4/28/2018   Sat    Apr      2018     
4/29/2018   Sun    Apr      2018    
4/30/2018   Mon    Apr      2018    
5/1/2018    Tue    May      2018    


        New Years Day   Martin Luther King Jr.  Presidents Day
2018    1/1/2018        1/20/2018               2/19/2018
2019    1/1/2019        1/18/2019               2/18/2019
2020    1/1/2020        1/17/2020               2/17/2020
2021    1/1/2021        1/16/2021               2/15/2021
2022    1/1/2022        1/15/2022               2/21/2022

Answer 1

... Аннотируйте ли это во второй группе ниже, поставив "Да" или "Праздник"

Кажется, вы просто хотите знать, выходной день или нет.

Следующая формула должна работать в ваших данных образца:

{=IF(OR(A2=$B$10:$D$14),"Yes", "No")}

Это формула массива. Не кладите вторую скобку вручную, вместо этого нажмите CTRL+SHIFT+ENTER, и скобка появится автоматически.

Пожалуйста, см. Формулу в строке формул на следующем рисунке.

Answer 2

Вы можете использовать Aggregate, чтобы вернуть правильный номер столбца в INDEX:

=IFERROR(INDEX($J$1:$L$1,AGGREGATE(15,6,(COLUMN($J$2:$L$6)-MIN(COLUMN($J$2:$L$6))+1)/($J$2:$L$6=A2),1)),"")

---

Если вы хотите только возврат TRUE/FALSE, то это будет сделано в виде массива:

=OR(A2=$J$2:$L$6)

Будучи формулой Array, она должна быть подтверждена нажатием Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования.

Он вернет TRUE, если любая из дат во втором списке совпадает с той, на которую ссылаются.

Answer 3

Вот решение, которое использует старые функции, которые должны работать в Excel 2007 (на самом деле я использую LO Calc, и он работает там).

Нажмите на картинку для увеличения

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

Формула в E2 (скопировано вниз по столбцу):

=IFERROR(INDEX($H$1:$J$1,1,MATCH(A2,INDIRECT("H"&MATCH(D2,$G$2:$G$6,1)+1&":J"&MATCH(D2,$G$2:$G$6,1)+1),0)),"")

Я объясню это изнутри.

MATCH(D2,$G$2:$G$6,1)

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

INDIRECT("H"&MATCH(D2,$G$2:$G$6,1)+1&":J"&MATCH(D2,$G$2:$G$6,1)+1)

Функция INDIRECT создает ссылку на диапазон, в котором вы ищите дату. Для 2018 года это дает диапазон H2:J2 (скорректируйте формулу для вашего фактического диапазона). Для удобства чтения я буду ссылаться на формулу выше как просто INDIRECT () в пояснении ниже.

MATCH(A2,INDIRECT(),0)

Затем Match находит столбец в том диапазоне, где находится дата в A2. Выдает ошибку, если дата не найдена (т. Е. Не выходной).

Если вы хотите просто общий результат, вы можете остановиться на этом и просто обернуть его в тесте IF, чтобы проверить наличие ошибки (ошибка = нет выходных, результат без ошибок = выходной), и отобразить формулировку на ваш выбор для результат.

INDEX($H$1:$J$1,1,MATCH(A2,INDIRECT(),0))

Это ищет указанный столбец в верхней строке, содержащий названия праздников. Я буду называть это просто INDEX() для удобства чтения в пояснении ниже.

IFERROR(INDEX(),"")

Это оборачивает результат в функции IFERROR, чтобы убрать сообщение об ошибке, если это не выходной.