Определение имени для номера недели (Microsoft Excel)

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

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

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

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

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

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

Прежде чем приступить к рассмотрению возможных решений, предположим, что неделя, о которой вы хотите знать, — это ячейка E1. Вы должны назвать этот диапазон как Query. Такое именование, хотя и не является строго необходимым, немного облегчит понимание формул.

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

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

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

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

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

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

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

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

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

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

Этот совет (11077) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

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