Note: The other languages of the website are Google-translated. Back to English

Динамічний аркуш Excel або довідкова книга

Припустимо, у вас є дані з однаковим форматом на декількох робочих аркушах або книгах і вам потрібно динамічно отримувати дані з цих аркушів або книг на інший аркуш. Функція INDIRECT може допомогти вам швидко зробити це.

Довідкові комірки на іншому аркуші динамічно
Посилання на комірки в іншій книзі динамічно


Довідкові комірки на іншому аркуші динамічно

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

Загальна формула

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

1. Як показано на скріншоті нижче, по-перше, вам потрібно створити зведений аркуш, ввівши імена аркушів окремо в різні комірки, потім виділіть порожню комірку, скопіюйте в неї формулу нижче та натисніть вводити ключ

=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)

Як показано на скріншоті нижче, дані, які потрібно повернути, знаходять у стовпці Е робочого аркуша "Загальний обсяг продажів" в окремій робочій книжці ім “Файл продажів”. Будь ласка, виконайте наступні кроки за кроком, щоб це зробити.

1. По-перше, давайте заповнимо інформацію про робочу книгу (включаючи назву книги, назву робочого аркуша та осередки посилань), до якої ви отримаєте дані на основі цієї інформації до поточної книги.

2. Виділіть порожню комірку, скопіюйте в неї формулу нижче та натисніть вводити ключ

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

примітки:

  • B3 містить назву книги, з якої потрібно витягти дані;
  • C3 - назва аркуша;
  • D3 це комірка, з якої ви будете витягувати дані;
  • повне г, повне г,, показали, від, номер, XNUMX #REF! значення помилки повернеться, якщо книга, на яку посилається, закрита;
  • Щоб уникнути #REF! значення помилки, будь ласка, додайте формулу INDIRECT до функції IFERROR наступним чином:
    = IFERROR (INDIRECT ("'[" & $ B $ 3 & "]" & $ C $ 3 & "'!" & D3), "")

3. Потім перетягніть маркер заповнення вниз, щоб застосувати формулу до інших комірок.

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


Пов’язана функція

Функція INDIRECT
Функція Microsoft Excel INDIRECT перетворює текстовий рядок у дійсне посилання.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (1)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
будь ласка, поверніться зі мною. Минуло багато часу з тих пір, як я використовував посилання на cel. те, що я хочу зробити, це зі стовпця цілих, я хочу створити новий стовпець і захопити кожен 100-й рядок попереднього стовпця. скажімо, у мене є клітинки, заповнені F3, F103, F203...я хочу, щоб вміст відображався в G3, G4, G5. не працює, щоб сказати =(F3+100*лічильник), маючи лічильник у стовпці поруч із тим, де я маю формулу.
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL