Вопрос или проблема
Я пытаюсь сделать так, чтобы создание новой страницы было проще для других, когда я покину свою должность. Поэтому я хочу убедиться, что им придется изменять как можно меньше для генерации новых листов. Моя работа запрещает использование макросов, так как они, как правило, приводят к блокировке и автоматическому удалению файлов Excel системой (странно, я знаю).
Моя формула во всех столбцах E выглядит следующим образом:
=IF(ISNA(VLOOKUP(B9,Table2029[[SN:[CTO Balance]], 7, FALSE)), 0, (VLOOKUP(B9,Table2029[[SN:[CTO Balance]], 7, FALSE))
Столбец B заполнен уникальными идентификационными номерами членов, что устраняет дубликаты, генерируемые фамилиями, вызывающими проблемы.
Названия моих листов – это год, для которого они предназначены (например, этот лист – “2030”, но он ссылается на таблицу на предыдущем листе с названием “2029”, и эта таблица называется “Table2029”).
Я сделал ячейку F1 такой же, как название листа. Ячейка G6 равна =F1-1. Ячейка H6 (для тестирования, но неудачно) равна =”Table”&G6, это действительно генерирует “Table2029”.
Я пытался использовать косвенные ссылки и/или ссылки на ячейки всеми возможными способами, но это нарушает формулу.
Заранее благодарю за любую помощь. Я покину свою должность следующим летом и просто пытаюсь упростить вещи для себя, чтобы получать звонки только по поводу формулы, а не о том, как создавать новые листы.
Эта формула должна работать:
=IFNA(VLOOKUP(B9,INDIRECT("Table"&G6&"[[SN]:[CTO Balance]]"),7,FALSE),0)
Или эта:
=IF(ISNA(VLOOKUP(B9,INDIRECT("Table"&G6&"[[SN]:[CTO Balance]]"),4,FALSE)),0,
VLOOKUP(B9,INDIRECT("Table"&G6&"[[SN]:[CTO Balance]]"),4,FALSE))
Но это ненужное усложнение.
Ответ или решение
Ваша задача по созданию удобно настраиваемых листов в Excel, не требующих использования макросов, является важной для обеспечения простоты работы новых сотрудников. Рассмотрим, как можно усовершенствовать вашу формулу VLOOKUP для использования значений из ячеек в качестве table_array, ссылаясь на таблицы на другом листе.
Шаг 1: Подготовка ячеек
Для корректного использования формулы INDIRECT, необходимо убедиться, что в ваших ячейках настроены правильные значения. У вас уже есть:
- В ячейке F1 — год текущего листа (например, "2030").
- В ячейке G6 — год предыдущего листа, вычисляемый как
=F1-1, который дает "2029". - В ячейке H6 — формула для создания имени таблицы:
="Table"&G6, что автоматически генерирует "Table2029".
Шаг 2: Использование функции INDIRECT
Основная проблема, с которой вы столкнулись, — это корректное использование функции INDIRECT для динамического ссылочного массива в функции VLOOKUP. Ниже представлена рабочая версия вашей формулы, которая использует INDIRECT:
=IFNA(VLOOKUP(B9,INDIRECT(H6&"[[SN]:[CTO Balance]]"),7,FALSE),0)Или, если требуется учесть использование функции ISNA:
=IF(ISNA(VLOOKUP(B9,INDIRECT(H6&"[[SN]:[CTO Balance]]"),7,FALSE)),0,VLOOKUP(B9,INDIRECT(H6&"[[SN]:[CTO Balance]]"),7,FALSE))Пояснение формулы
-
Функция
INDIRECT: Позволяет интерпретировать строку как адрес таблицы. Например,INDIRECT(H6&"[[SN]:[CTO Balance]]")создаст ссылку на таблицу "Table2029", если в H6 будет "Table2029". -
Функция
VLOOKUP: Основная функция для поиска значения в указанной таблице в зависимости от уникального идентификатора из колонки B. -
Функция
IFNAиIF(ISNA...): Эти функции проверяют, возвращает лиVLOOKUPзначение #N/A. Если да, то возвращают 0, что упрощает чтение и анализ данных.
Шаг 3: Заключение и рекомендации
Подводя итог, использование динамического составления ссылок через INDIRECT позволяет легко обновлять ваши формулы при добавлении новых листов. Это значительно упростит работу вашим коллегам, поскольку они смогут просто изменять значения в ячейке F1 и соответствующим образом обновлять свои формулы, не заботясь о создании макросов.
Мы рекомендуем тщательно протестировать вашу формулу после внесения изменений. Убедитесь, что все необходимые диапазоны таблиц правильно указаны и доступ к данным из других листов настроен корректно.
Работа с формулами в Excel требует внимания к деталям, но с правильным подходом ваша задача станет значительно проще. Надеемся, что данное объяснение поможет вам в вашей работе и упростит процесс для ваших коллег.