Как использовать поле для выбора, чтобы изменить ссылку на лист в формуле?

Question

У меня есть формула, которая выглядит примерно так

=B2 - IF((B2 - Single!$D$1) < Single!$A$3, (B2 - Single!$A$2 - Single!$D$1) * Single!$B$2 + Single!$C$2, ...

Я использую что-то из таблицы поиска на листе с надписью "Single" для анализа. Я хотел бы иметь поле, выбираемое пользователем (в идеале что-то вроде выпадающего меню, скажем, в ячейке 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, ...

если был выбран "Sheet2", это было бы

B2 - IF((B2 - Sheet2!$D$1) < Sheet2!$A$3, (B2 - Sheet2!$A$2 - Sheet2!$D$1) * Sheet2!$B$2 + Sheet2!$C$2, ...

и так далее.

Answer 1

Вы должны использовать функцию INDIRECT для создания формул и раскрывающийся список, чтобы пользователь мог выбрать лист.

Оба довольно просты в использовании:

(1) Раскрывающийся список:

• Создайте диапазон со значениями, которые вы хотите, чтобы пользователь мог выбрать; в вашем случае это список с рабочими листами. Давайте предположим, что они достаточно значимы для пользователя, поэтому вы можете использовать точное написание в списке (но для этого примера давайте выберем "Лист1" и т.д.).
• Выберите ячейку (предположим, A1), где вы хотите иметь раскрывающийся список, перейдите в "Данные"> "Проверка данных", "Список" и выберите список значений (или диапазон), который вы создали.

(2) Теперь используйте функцию INDIRECT в своей формуле (для упрощения я просто возьму небольшую часть формулы, но вы, конечно, должны использовать ее везде, где формула использует Sheet1! в формуле):

вместо B2 - Sheet1!$D$1 , вы используете B2 - INDIRECT(CONCATENATE(A1;"!$D$1")) .
(функция CONCATENATE берет строку "Лист1" из ячейки A1, и сочетает в себе это с трудом закодирован текстом , чтобы дать действительную ссылку на ячейку, которая затем оценивается с INDIRECT функциями, вместо CONCATENATE вы можете также использовать сокращенную амперсанд & оператор; также обратите внимание, что функция переименована в CONCAT начиная с Excel 2016, но CONCATENATE все равно будет работать в обозримом будущем для обеспечения обратной совместимости).