Перейти до основного матеріалу

Як посилатись на одну клітинку з декількох аркушів у Excel?

Припустимо, у вас є книга, яка містить кілька робочих аркушів, і тепер ви хочете витягти дані в одній комірці з декількох аркушів в один головний аркуш. Наприклад, витягніть комірку B8 з Sheet1, Sheet2, Sheet3, Sheet4… на головний аркуш, як показано на наступному знімку екрана. Як ви могли швидко та легко вирішити це завдання в Excel?

Посилання на ту саму комірку з декількох аркушів на один головний аркуш із формулою

Посилання на ту саму комірку з декількох аркушів на один головний аркуш із кодом VBA

Посилання на ту саму комірку з декількох аркушів на один головний аркуш із дивовижною особливістю


Посилання на ту саму комірку з декількох аркушів на один головний аркуш із формулою

Якщо імена ваших робочих аркушів є іменами аркушів за замовчуванням, наприклад, Sheet1, Sheet2, Sheet3 ..., ви можете скористатися формулою для швидкого вирішення цієї роботи в Excel.

1. По-перше, вам слід створити список допоміжних номерів, введіть 1, 2, 3, 4 ... порядкові номери, які вказують робочі аркуші, на які потрібно посилатися, див. Знімок екрана:

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

=INDIRECT("'Sheet" & E2 & "'!$B$8")

примітки: У наведеній вище формулі, E2 - допоміжний номер, який ви ввели на кроці 1, та B8 - посилання на клітинку, яке потрібно витягти. Ця формула добре працює, лише якщо назви аркушів - Аркуш1, Аркуш2, Аркуш3 ...


Посилання на одну клітинку з декількох аркушів на один головний аркуш

Kutools для Excel підтримує потужну функцію-Динамічно звертайтесь до робочих аркушів що може допомогти вам посилатися на одне і те ж значення комірки на кількох робочих аркушах у головний аркуш. Будь ласка, дивіться демонстрацію нижче    Клацніть, щоб завантажити Kutools для Excel!


Посилання на ту саму комірку з декількох аркушів на один головний аркуш із кодом VBA

Якщо є десятки аркушів, імена аркушів яких різний вміст замість Аркуш1, аркуш2, у цьому випадку наступний код VBA може допомогти вам заповнити однакові посилання на клітинки з кількох аркушів одночасно на одному аркуші.

1. На головному аркуші клацніть клітинку B8, яка є коміркою, яку потрібно витягти з інших аркушів.

2. Утримуйте клавішу ALT + F11 і відкриває Вікно Microsoft Visual Basic для програм.

3. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.

Код VBA: посилання на одну комірку з кількох аркушів

Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub

4. Потім натисніть F5 ключ для запуску цього коду, і всі значення комірки B8 з інших аркушів були витягнуті на головний аркуш. Дивіться знімок екрана:

Примітка: Цей код VBA застосовується для заповнення клітинок, де ви клацаєте. Наприклад, якщо клацнути клітинку А1 на вказаному аркуші, усі значення комірки А1 з інших аркушів будуть заповнені на цьому аркуші.


Посилання на ту саму комірку з декількох аркушів на один головний аркуш із дивовижною особливістю

Якщо ви не знайомі з кодом VBA, тут я можу представити вам корисний інструмент - Kutools для Excel, З його Динамічно звертайтесь до робочих аркушів Утиліта, ви можете легко заповнити посилання на клітинки з декількох аркушів у Excel.

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

після установки Kutools для Excel, будь ласка, зробіть так:

1. Клацніть клітинку B8 на головному аркуші, який потрібно витягти з інших аркушів.

2. Потім натисніть Кутулс > більше > Динамічно звертайтесь до робочих аркушів, див. скріншот:

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

примітки: Якщо ви не зафіксуєте цей маленький замок, посилання на клітинку, на яку ви натиснули, буде змінено з вибраною коміркою.

4. Потім натисніть Діапазон заповнення , а значення комірки комірки B8 з інших робочих аркушів були перераховані вертикально на робочому аркуші, а потім закрийте це діалогове вікно. Дивіться знімок екрана:

Натисніть, щоб завантажити Kutools для Excel та безкоштовну пробну версію зараз!


Більше статей:

  • Скопіюйте рядки з декількох аркушів на основі критеріїв у новий аркуш
  • Припустимо, у вас є робоча книга з трьома робочими аркушами, які мають те саме форматування, що показано нижче. Тепер ви хочете скопіювати всі рядки з цих аркушів, стовпець C яких містить текст «Завершено», на новий аркуш. Як ви могли швидко та легко вирішити цю проблему, не копіюючи та не вставляючи їх по черзі вручну?
  • Створіть список унікальних цінностей із декількох робочих аркушів
  • Чи існує швидкий спосіб створити для нас список унікальних значень з усіх аркушів книги? Наприклад, у мене є чотири аркуші, в яких перелік деяких імен містить дублікати у стовпці A, і тепер я хочу витягти всі унікальні імена з цих аркушів у новий список, як я можу закінчити цю роботу в Excel?
  • Countif Конкретне значення на кількох робочих аркушах
  • Припустимо, у мене є декілька робочих аркушів, які містять наступні дані, і тепер я хочу отримати кількість зустрічей певного значення «Excel» з цих аркушів. Як я можу порахувати конкретні значення на декількох аркушах?
  • Вставте одне і те ж зображення на кілька аркушів
  • Зазвичай ми можемо швидко вставити малюнок на аркуш за допомогою функції Вставити в Excel, але чи намагалися коли-небудь вставити одне і те ж зображення на всі аркуші вашої книги? У цій статті я запропоную вам корисний метод вирішення цієї роботи.

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

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

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

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (13)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have create a stock sheet and opening stock list is there and i create 500 sheets for different items so how to put opening stock in all sheets at a time
This comment was minimized by the moderator on the site
Hello,


I have a list of students in column A and the schools they attend in G. I want a worksheet to populate all students who attend X school from the available worksheets. How would I do this? Thanks in advance!
This comment was minimized by the moderator on the site
I have a spreadsheet with all the months. I want some of the cells to carry over from the first month to the proceeding other (i.e., cell B12 in January can be linked to February - December cell B12). How would I do this?
This comment was minimized by the moderator on the site
Hi, thanks it works with a workbook at my job. Thanks Best regards Luzardo
This comment was minimized by the moderator on the site
Dears, Kindly note that I used Reference Same Cell From Multiple Worksheets With VBA Code and it works with me properly, however I need your help in how to me this code retrieve the values in the master sheet horizontal instead of vertical. Best Regards, :-)
This comment was minimized by the moderator on the site
Dears Kindly note that I used Reference Same Cell From Multiple Worksheets With VBA Code and it works properly, but I need to modify something in the code and I need your help to make the values return in the master sheet horizontal instead of vertical. Best Regards, Mohamed AbdELhady
This comment was minimized by the moderator on the site
Is there a MAcbook version of this tool?
This comment was minimized by the moderator on the site
Hi I have just tried your vba code and it makes sense how to use it however on my master spreadsheet for this to work it is slightly more complicated firstly i'd want when i run the code for the results to populate horizontally secondly the cell id use as a reference like B6 is used doesnt correpsond on the other spreadsheets - so to explain the value id want to find appears in the same cell on each sheet but this doesnt correspond onto the master spreadsheet hope this makes sense please help!!
This comment was minimized by the moderator on the site
hi Thank you for you coding its really great and so much helpful for us. In this above VBA code how can I exclude the hidden worksheet? As I have different customer whom I billed them monthly using a format. So I have created few worksheet and hide them and from where I extract the different data from this hidden sheet. So please let me know the coding so that it doesn't pick the cell value from hidden worksheet. Your work is highly appreciated and thak you for your wonderful support! Regards Abhishek
This comment was minimized by the moderator on the site
How would I reference a specific cell as opposed to the cell that I have selected in another workbook?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations