Перейти к основному содержанию

Справочник по динамическому листу или книге Excel

Автор: Силувия Последнее изменение: 2019 июля 06 г.

Предположим, у вас есть данные в одном и том же формате на нескольких листах или книгах, и вам необходимо динамически переносить данные из этих листов или книг на другой лист. Функция КОСВЕННО может помочь вам быстро сделать это.

Связывание ячеек на другом листе динамически
Ссылки на ячейки в другой книге динамически


Связывание ячеек на другом листе динамически

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

Общая формула

=INDIRECT("'"&sheet_name&"'!Cell to return data from")

1. Как показано на скриншоте ниже, во-первых, вам нужно создать сводный рабочий лист, введя имена листов отдельно в разные ячейки, затем выберите пустую ячейку, скопируйте в нее формулу ниже и нажмите Enter .

=INDIRECT("'"&B3&"'!C3")

Заметки: В коде:

  • B3 это ячейка, содержащая имя листа, из которого вы будете извлекать данные;
  • C3 это адрес ячейки на конкретном листе, данные которого вы извлечете;
  • Чтобы предотвратить возврат значения ошибки, если либо B5 (ячейка с именем листа), либо C3 (ячейка, в которую вы будете извлекать данные) пуста, пожалуйста, заключите формулу INDIRECT с функцией IF, отображаемой ниже:
    = ЕСЛИ (ИЛИ (B3 = "", C3 = ""), "", КОСВЕННО ($ B $ 3 & "! C3"))
  • Если в именах ваших листов нет пробелов, вы можете напрямую использовать эту формулу
    = КОСВЕННО (B3 & "! C3")

2. Затем перетащите его Ручка заполнения вниз, чтобы применить формулу к другим ячейкам. Теперь вы вернули все продажи за первый квартал из конкретных таблиц.

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


Ссылки на ячейки в другой книге динамически

В этом разделе говорится о динамических ссылках на ячейки в другой книге в Excel.

Общая формула

=INDIRECT("'[" & Book name & "]" & Sheet name & "'!" & Cell address)

Как показано на скриншоте ниже, данные, которые вы хотите вернуть, находятся в столбце E рабочего листа. "Тотальная распродажа" в отдельной книге под названием «Файл продаж». Пожалуйста, сделайте следующее шаг за шагом, чтобы сделать это.

1. Во-первых, давайте заполним информацию о книге (включая имя книги, имя рабочего листа и ссылочные ячейки), из которых вы будете извлекать данные на основе этой информации в текущую книгу.

2. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter .

=INDIRECT("'["&$B$3&"]"&$C$3&"'!"&D3)

Заметки:

  • B3 содержит имя книги, из которой вы хотите извлечь данные;
  • C3 имя листа;
  • D3 это ячейка, из которой вы будете извлекать данные;
  • Команда #REF! значение ошибки будет возвращено, если указанная книга закрыта;
  • Чтобы избежать #REF! значение ошибки, пожалуйста, заключите формулу КОСВЕННО с функцией ЕСЛИОШИБКА следующим образом:
    = ЕСЛИОШИБКА (КОСВЕННАЯ ("'[" & $ B $ 3 & "]" & $ C $ 3 & "'!" & D3), "")

3. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам.

Наконечник: Если вы не хотите, чтобы возвращаемое значение превращалось в ошибку после закрытия указанной книги, вы можете напрямую указать имя книги, имя рабочего листа и адрес ячейки в формуле следующим образом:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,"")


Связанная функция

КОСВЕННАЯ функция
Функция Microsoft Excel INDIRECT преобразует текстовую строку в действительную ссылку.


Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...

Описание


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
please get back with me. it's been a while since i used cel references. what i want to do is from a column of entires, i wish to make a new column and grab every 100th row of the prior column. let's say i have cells filled in F3, F103, F203...i want the contents to appear in G3,G4,G5. it doesn't work to say =(F3+100*counter) with having a counter in a column next to where i am having the formula.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations