Тео использует таблицу Excel, чтобы отслеживать бронирования в своей компании. Данные состоят всего из трех столбцов. Первый — это имя человека, второй — номер первой недели (1-52) бронирования, а третий — номер последней недели бронирования.

Люди могут быть зарезервированы на несколько недель (т. Е. Начальная неделя — 15, а конечная неделя — 19). Тео нужен способ ввести номер недели, а затем формулу определить, какое имя (столбец A) связано с этим номером недели. Данные не сортируются в каком-либо определенном порядке, и компания не позволит Тео использовать макрос для получения результата (это должна быть формула).

Ситуация Тео кажется достаточно простой, но она полна подводных камней при поиске решения. Если посмотреть на возможные данные (как показано на следующем рисунке), можно быстро понять, почему это так. (См. Рис. 1.)

image

Рисунок 1. Возможные данные для проблемы Тео.

Обратите внимание, что данные (как сказал Тео) не в каком-то определенном порядке.

Также обратите внимание, что есть недели, когда нет резервирований (например, неделя 5 или 6), недели, когда есть несколько человек (например, неделя 11 или 16), и недели, когда кто-то зарезервирован, но неделя номер не отображается в столбцах B или C (например, 12 или 17 неделя).

Прежде чем приступить к рассмотрению возможных решений, предположим, что неделя, о которой вы хотите знать, — это ячейка E1. Вы должны назвать этот диапазон как Query. Кроме того, назовите диапазон, содержащий имена людей (в этом примере ячейки A2: A10), как ResNames, начальные недели (B2: B10) как StartWeeks, а конечные недели (C2: C10) как EndWeeks. Наконец, определите имя для всей таблицы (A2: C10), например MyData. Такое именование, хотя и не является строго необходимым, значительно облегчит понимание формул.

Одно из возможных решений — добавить то, что обычно называют «вспомогательной колонкой». Добавьте в ячейку D2 следующее:

=IF(AND(Query>=B2,Query<=C2),"RESERVED","")

Скопируйте формулу вниз для количества ячеек, равного количеству имен в таблице. (Например, скопируйте его через ячейку D10.) Когда вы помещаете номер недели в ячейку E1, тогда слово «ЗАБРОНИРОВАНО» появляется справа от любого бронирования, которое включает этот номер недели. Также легко увидеть, зарезервировано ли несколько человек на эту неделю или нет людей, зарезервированных на эту неделю. Вы даже можете применить Автофильтр и выбрать отображение только тех записей со словом «ЗАБРОНИРОВАНО» в столбце D.

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

Просто выберите имена в столбце A и добавьте правило условного форматирования, использующее следующую формулу:

=AND(Query>=B2,Query<=C2)

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

Другой подход — использовать формулу массива. Выделите на несколько ячеек больше, чем ожидаемое количество перекрывающихся резервирований, а затем введите в эти ячейки следующее, нажав Ctrl + Shift + Enter:

=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)(EndWeeks>=Query)(ROW(ResNames)),ROW()-1)-1),"")

Выбирая количество ячеек, вы хотите, чтобы эта формула массива занимала, посмотрите, например, количество людей, которые могут быть зарезервированы на 11-ю неделю. В примере, показанном в этом совете, это 2 человека. Выберите большее количество ячеек, а затем поместите в эти ячейки формулу массива.

Если вы ожидаете, что у вас может быть 20 человек, потенциально забронированных на одну и ту же неделю, вам нужно выбрать большее количество ячеек, например 20 или 30. Просто выберите ячейки, введите формулу в панель формул, а затем нажмите Ctrl + Shift + Enter.

Наконец, вам действительно стоит подумать о том, чтобы пересмотреть расположение ваших данных.

Вы можете создать рабочий лист с номерами недель в столбце A (от 1 до 52 или 53), а затем с названиями мест в столбце B. Если человек был зарезервирован на две недели, его имя будет отображаться в столбце B дважды, по одному разу рядом с каждым из две недели, которые они зарезервировали.

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

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (11078) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Determining_a_Name_for_a_Week_Number [Определение имени для номера недели].