В 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)
- Нажмите OK, чтобы завершить функцию и закрыть диалоговое окно.
- Число 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:
- Содержит недопустимую ссылку на ячейку.
- Содержит внешнюю ссылку на другой лист, который не существует.
- Относится к диапазону ячеек за пределами рабочего листа.
- Относится к пустой ячейке.