Как сделать раскрывающуюся таблицу в Эксель: статусы
Как сделать раскрывающуюся таблицу в Эксель: настраиваю список состояний реквизита. Показываю источник значений, проверку ввода и отличие от фильтра строк.
Евгений
Дизайнер-партнёр LibraChat
8 мин чтения
В листе подготовки реквизита я хочу выбирать «Осмотр», «В работе» или «Готово» рядом с каждым предметом. Тогда человеку не придётся каждый раз печатать состояние заново. Когда меня спрашивают, как сделать раскрывающуюся таблицу в Эксель, я сначала уточняю место действия: раскрыться должна ячейка, меню в заголовке или целый блок строк? Для нашего реквизита нужен список внутри ячейки.
Коротко: подготовь отдельный столбец допустимых состояний, выдели ячейки ввода и задай им проверку данных типа «Список». В примере ниже значения лежат в G2:G4, а выбирать их будем в B2:B6. Фильтр и группировка решают другие задачи.
Как сделать раскрывающуюся таблицу в Эксель: список или фильтр?
Я проверил, какое действие требуется от человека перед репетицией. Он берёт предмет, осматривает его и отмечает состояние. Строка при этом должна остаться на месте, а выбранное слово появиться рядом с названием.
В Excel есть несколько похожих элементов со стрелками. Их легко спутать, особенно если сначала искать картинку нужной таблицы, а потом пытаться повторить её вид.
Что ты хочешь сделать · Подходящий инструмент · Что меняется
Выбрать «В работе» для фонаря — Раскрывающийся список в ячейке — Значение одной ячейки
Увидеть только готовый реквизит — Фильтр по столбцу состояния — Набор видимых строк
Спрятать подробности отдельной сцены — Группировка строк — Видимость целого блока
Фильтр обычно раскрывается из заголовка. Он оставляет на экране записи, соответствующие условию. Если выбрать там «Готово», это не присвоит готовность всем предметам. Изменится отображение списка.
У группировки другая форма: рядом со строками появляются элементы структуры, в том числе плюс и минус для раскрытия и сворачивания подробностей. Так удобно сокращать длинный лист по сценам. Но из трёх состояний предмета такая кнопка ничего не выбирает.
Для этой задачи я оставляю первый вариант. Остальные пригодятся позже, когда захочется смотреть только незавершённую работу или скрывать детали сцен.
Какие данные разместить на листе?
Для разбора я собрал вымышленный список студии. Реквизита немного, всего 5 штук: окно, фонарь, табличка, рама и сундук. Поле состояния пока пустое. Заполнять его будем после осмотра.
Размести таблицу в A1:C6. Заголовки займут первую строку, предметы — строки со второй по шестую.
A: Реквизит · B: Состояние · C: Что сделать
Окно — Укрепить петлю
Фонарь — Заменить ручку
Табличка — Обновить надпись
Рама — Проверить крепление
Сундук — Осмотреть углы
Правее, в G1, напиши «Допустимые состояния». В G2 внеси «Осмотр», в G3 — «В работе», в G4 — «Готово». По одному значению в ячейку.
Я отделяю этот справочник от рабочих строк. В B человек выбирает состояние конкретного предмета; в G находятся варианты выбора. Если смешать эти места, легко случайно начать редактировать сам перечень состояний вместо отметки о фонаре.
Заголовок G1 не входит в список. Он нужен читателю листа. Предлагать его как четвёртый вариант состояния было бы странно.
Я также заранее сделал бы колонку B достаточно широкой для «В работе». Смысл списка теряется, когда видна только часть выбранного слова. Для первого варианта мне хватает текста, без обязательной раскраски каждого состояния.
Где включить выбор для B2:B6?
Настройку для пяти предметов я описываю в настольном Excel для Windows. У других версий интерфейс может отличаться. Нужный механизм называется проверкой данных.
Настройку можно выполнить сразу для всех пяти рабочих ячеек:
Выдели B2:B6, оставив заголовок B1 за пределами выделения.
В разделе «Данные» выбери команду «Проверка данных», а в открывшемся окне перейди к параметрам и установи тип «Список».
Свяжи правило с тремя состояниями справа: поле «Источник» должно содержать `=$G$2:$G$4`.
Убедись, что включён показ раскрывающегося списка в ячейке. В русской справке этот флажок называется «Список допустимых значений».
Подтверди настройку кнопкой ОК.
Вместо набора адреса вручную можно выбрать в поле источника диапазон G2:G4 на листе. Принцип тот же: три исходные ячейки дают три варианта выбора.
Теперь ожидаемое действие пользователя выглядит так: выделить B3 рядом с фонарём, открыть список и выбрать «В работе». Выбранное значение должно оказаться в B3. Остальные предметы остаются со своими состояниями.
Я попробовал представить эту операцию глазами человека, который впервые открыл лист. Ему должно быть понятно, куда нажать и что означает выбор. Поэтому я сохраняю обычный заголовок «Состояние» и не прячу подсказку о трёх вариантах.
Список применяется к выделенным ячейкам. Наше правило настроено для B2:B6. Соседний столбец с задачами ремонта от этого не становится списком.
Как проверить ввод и сообщение об ошибке?
После настройки я начал бы с двух проверок: выбрать предусмотренное слово и вручную ввести постороннее. Это проверяет разные вещи. Наличие стрелки ещё не показывает, как Excel обработает неправильную запись.
Для отказа от недопустимого ручного ввода открой для B2:B6 вкладку «Сообщение об ошибке» в окне проверки данных. Включи показ сообщения и выбери вид «Остановка». Текст можно написать свой: «Выбери Осмотр, В работе или Готово».
Виды предупреждений различаются. «Остановка» требует исправить недопустимый ввод, а более мягкие сообщения могут разрешать продолжение. Поэтому я не ограничиваюсь словами «включи предупреждение»: для нашего листа это слишком неопределённо.
И всё же такое правило не делает книгу защищённой от любых изменений. Microsoft отдельно оговаривает: при копировании и вставке проверочные сообщения могут не отображаться. Я не обещал бы, что после списка неправильные состояния станут невозможны.
Есть и прежние записи. Если до настройки в B уже было написано «Сделано», создание правила не заменит это слово на «Готово». Старые значения нужно просмотреть отдельно и согласовать. Само появление стрелки историю ввода не исправляет.
Пустую ячейку я тоже оцениваю по смыслу. В нашем листе она означает, что предмет ещё не отмечен. Если студия хочет обязательное состояние для каждой вещи, это отдельная договорённость о заполнении. Выбор «Готово» нельзя подставлять за человека ради аккуратного вида.
Что делать, если понадобилось четвёртое состояние?
Допустим, решили добавить «Ждёт деталей». Впиши его в G5. Это пополнит справочник на листе, однако наш источник пока ограничен G2:G4.
Для обычного диапазона адреса нужно обновить. Вернись к настройке B2:B6 через «Проверку данных» и расширь адрес в поле «Источник» до `=$G$2:$G$5`. Подтверди изменение и посмотри, появился ли новый вариант в нескольких рабочих ячейках.
Здесь я намеренно использую фиксированный небольшой диапазон. Если источник организован как таблица Excel, для расширения есть другие возможности. Но в этом примере не стоит ждать их от простой ссылки на три ячейки.
Важно не путать расширение перечня с изменением прежних отметок. Новый вариант появится в меню. Фонарь, у которого выбрано «В работе», не станет автоматически ждать детали.
При удалении состояния подумай, что делать с предметами, которым оно уже назначено. Для подготовки репетиции это содержательное решение: нужно понять, на каком этапе находятся вещи, а не только сократить меню.
Как сделать раскрывающуюся таблицу в Эксель для новых строк?
Если после сундука в A7 появится новый предмет, проверь ячейку B7. Мы настраивали правило до строки 6. Предполагать, что оно уже действует дальше, не стоит.
Для нашей простой раскладки можно выделить нужный расширенный участок, например B2:B7, и задать ему те же параметры списка. После этого открой выбор у нового предмета. Источник остаётся справочником в G, а рабочий диапазон в B увеличивается.
Я различаю два расширения. Добавили новый предмет — меняется область ввода. Добавили новый статус — меняется источник значений. Когда их путают, четвёртое состояние ищут в новой строке справочника, а новый предмет случайно включают в меню.
Перед передачей листа другому человеку я проверил бы следующее:
У первого и последнего предмета предлагаются одинаковые состояния.
Заголовок справочника не попал в меню.
Выбранное слово видно целиком.
Новый предмет получил правило ввода.
Старые нестандартные отметки разобраны отдельно.
Удобство здесь складывается из небольших деталей. Я хочу, чтобы человек думал о креплении рамы, а не пытался понять устройство таблицы.
Чем в этой настройке поможет LibraChat?
В чате LibraChat можно описать свой лист и попросить адаптировать последовательность под другие адреса. Например:
В Excel для Windows у меня реквизит в A, состояние в B, пять предметов в строках 2–6. Допустимые состояния записаны в G2:G4. Объясни настройку списка именно внутри B2:B6. Отдельно укажи, что поменять после добавления состояния в G5 и нового предмета в строку 7.
Это заготовка запроса. Настройку своей книги выполняешь ты; текст инструкции не означает, что проверка данных уже включена. Особенно если собеседник видел только описание колонок.
А теперь попробуй пройти один предмет от начала до конца: найти фонарь, открыть выбор в его строке и поставить состояние по факту осмотра. Стрелка помогает записать решение. Убедиться, что ручка держится, всё равно нужно человеку.
