У Excel ви можете підсумувати кількість комірок за допомогою змінного діапазону з функцією INDIRECT. Функція INDIRECT автоматично оновлює діапазон комірок, на які ви посилаєтеся, без ручного редагування самої формули. Ви можете використовувати функцію INDIRECT з будь-якою кількістю функцій Excel, але найбільш поширеним (і корисним) є використання функції SUM.
Інформація в статті відноситься до версій Excel 2019, 2016, 2013, 2010 і Excel для Mac.
Як підсумувати кількість комірок за допомогою змінного діапазону
Ви можете вбудувати функцію INDIRECT в якості аргументу всередині функції SUM, щоб створити змінний діапазон посилань на комірки для додаваної функції SUM. Функція INDIRECT робить це шляхом непрямого посилання на діапазон комірок через проміжне посилання на комірку.
Формат обох функцій, які використовуються разом, виглядає наступним чином:
= СУМ (ДВСИЛ («» D1 «»: D4)
Це блокує діапазон, починаючи з D1, і дозволяє D4 змінюватися, якщо ви вставляєте або видаляєте якісь комірки в стовпчику D.
Ви також можете використовувати інші комірки в електронній таблиці для зміни посилань на комірки. Наприклад, якщо ви використовуєте E1 для посилання на першу комірку діапазону і E2 для посилання на останню комірку діапазону, формула виглядатиме так:
= СУМА (БЕЗПОСЕРЕДНЬО ("D" "& E1 &" ": D"" & E2))
Змінюючи числа, розташовані в комірках E1 і E2, ви можете змінити діапазон у формулі, не редагуючи формулу вручну.
Спробуйте суму і непрямі функції
Створіть електронну таблицю для самостійного тестування функцій SUM та INDIRECT. Почніть зі створення порожньої електронної таблиці та введення наступних даних у стовпчики D та E:
Дані комірки
D1 - 5
D2 - 10
D3 - 15
D4 - 20
D5 - 25
D6 - 30
E1 - 1
E2 - 4
Далі створіть формулу в комірці F1. Ось як:
- Виберіть комірку F1. Це де результат цього прикладу буде відображатися.
- Виберіть Формули.
- Виберіть Math & Trig, щоб відкрити список функцій, що розкриваються.
- Виберіть SUM у списку, щоб відкрити діалогове вікно SUM Function Arguments.
Вкладіть БЕЗПОСЕРЕДНЮ функцію у функцію SUM
Потім введіть функцію INDIRECT у функцію SUM за допомогою цього діалогового вікна.
- У полі Number1 введіть таку функцію INDIRECT:
НЕПРЯМІ ("D" "& E1 &" ": D «» і E2)
- Натисніть кнопку Гаразд, щоб завершити роботу і закрити діалогове вікно.
- Число 50 з'являється в комірці F1. Це сума комірок від D1 до D4.
- Коли ви вибираєте комірку F1, поле формули показує:
= СУМ (ДВССИЛ ("D" ", і Е1 &" ": D «», & Е2))
Якщо ви зрозумієте, як форматувати SUM і вкладені функції INDIRECT, ви зможете ввести всю функцію, показану на кроці 4, безпосередньо в комірку результату (в даному прикладі, комірку F1).
Створіть аргумент для непрямої функції
У наведеному вище прикладі посилання на комірки E1 і E2 знаходяться поза лапками. Це, у поєднанні з символом & concatenate, вбудовує все, що міститься всередині комірок E1 і E2, у саму формулу.
Ось як працюють елементи формули:
- & використовується для об'єднання текстових даних (в даному випадку літери D) з посиланням на комірку (E1 і E2), яка містить числове значення.
- "" "повинен оточувати текстові дані всередині посилань на комірки (в даному прикладі це буква D).
- : є розділювачем між першою і останньою комірками в діапазоні SUM.
У наведеному вище прикладі початкова точка діапазону визначається символами: «D» і E1.
Кінцева точка діапазону визначається символами: «: D» & E2
Двокрапка має бути включена в лапки.
Третій амперсанд в середині використовується для об'єднання двох сегментів в один аргумент:
""D"" & E1 & "": D"" & E2
Діапазон змінних функції SUM
Функція INDIRECT, вкладена у функцію SUM, дозволяє легко змінювати початок і кінець діапазону, який складає функція SUM, без необхідності редагувати саму функцію.
Використовуючи метод конкатенації на кроці 3, використовуйте числа, що зберігаються в комірках E1 і E2 в електронній таблиці, щоб змінити діапазон комірок для функції SUM.
Перевірте це у власній таблиці, змінивши комірки E1 і E2 і подивіться, як змінюються результати функції SUM.
- Виберіть комірку E1.
- Введіть число 3.
- Натисніть Enter.
- Виберіть комірку E2.
- Введіть число 6.
- Натисніть Enter.
- Відповідь у комірці F1 змінюється на 90. Це сума чисел, що містяться в комірках з D3 по D6.
Щоб побачити функцію INDIRECT у дії, вставте нову комірку в комірку D3. Це зрушує всі інші клітини вниз. Нова сума - це сума комірок від D3 до D7. Функція INDIRECT динамічно змінює весь діапазон, щоб включити весь список, який ви спочатку ввели, а також нову комірку, яку ви вставили.
#REF! Значення помилки
#REF! Значення помилки з'являється в комірці F1, якщо функція INDIRECT:
- Містить некоректне посилання на комірку.
- Містить зовнішнє посилання на інший аркуш, який не існує.
- Відноситься до діапазону комірок за межами робочого аркуша.
- Відноситься до порожньої комірки.