Как использовать поле для выбора, чтобы изменить ссылку на лист в формуле?
У меня есть формула, которая выглядит примерно так
=B2 - IF((B2 - Single!$D$1) < Single!$A$3, (B2 - Single!$A$2 - Single!$D$1) * Single!$B$2 + Single!$C$2, ...
Я использую что-то из таблицы поиска на листе с надписью "Sing le" для анализа. Я хотел бы иметь поле, выбираемое пользователем (в идеале что-то вроде выпадающего меню, скажем, в ячейке S1), чтобы я мог переключаться между различными листами.
Например, если бы мои параметры листа были "Лист1", "Лист2" и "Лист3", у меня было бы выпадающее меню в ячейке S1 с этими параметрами, и формула обновилась бы, чтобы отразить выбор. Если выбрать "Лист1", это будет
B2 - IF((B2 - Sheet1!$D$1) < Sheet1!$A$3, (B2 - Sheet1!$A$2 - Sheet1!$D$1) * Sheet1!$B$2 + Sheet1!$C$2, ...
если был выбран "Лист2", это было бы
B2 - IF((B2 - Sheet2!$D$1) < Sheet2!$A$3, (B2 - Sheet2!$A$2 - Sheet2!$D$1) * Sheet2!$B$2 + Sheet2!$C$2, ...
и так далее.
1 ответ
Вы должны использовать INDIRECT функция для создания формул и раскрывающийся список, чтобы пользователь мог выбрать лист.
Оба довольно просты в использовании:
(1) Раскрывающийся список:
- Создайте диапазон со значениями, которые вы хотите, чтобы пользователь мог выбрать; в вашем случае это список с рабочими листами. Давайте предположим, что они достаточно значимы для пользователя, поэтому вы можете использовать точное написание в списке (но для этого примера давайте выберем "Лист1" и т. Д.).
- Выберите ячейку (предположим, A1), где вы хотите иметь раскрывающийся список, перейдите в "Данные"> "Проверка данных", "Список" и выберите список значений (или диапазон), который вы создали.
(2) Используйте сейчас INDIRECT функция в вашей формуле (для упрощения я просто возьму небольшую часть формулы, но вы, конечно, должны использовать ее везде, где формула использует Sheet1! в формуле):
вместо B2 - Sheet1!$D$1, ты используешь B2 - INDIRECT(CONCATENATE(A1;"!$D$1")),
(CONCATENATE Функция берет строку "Sheet1" из ячейки A1 и объединяет ее с жестко закодированным текстом, чтобы получить действительную ссылку на ячейку, которая затем оценивается с помощью INDIRECT функция; вместо CONCATENATE Вы также можете использовать сокращенное обозначение амперсанда & оператор; также обратите внимание, что функция переименована CONCAT по состоянию на Excel 2016, но CONCATENATE все еще будет работать в обозримом будущем, чтобы сохранить обратную совместимость).