КАК: Excel Trick для суммирования количества ячеек с переменным диапазоном — 2024
Table of Contents:
В Excel вы можете суммировать количество ячеек, используя переменный диапазон с функцией INDIRECT. Функция INDIRECT автоматически обновляет диапазон ячеек, на которые вы ссылались, без ручного редактирования самой формулы. Вы можете использовать функцию INDIRECT с любым количеством функций Excel, но наиболее распространенным (и полезным) является использование функции SUM.
Заметка: Информация в статье относится к версиям Excel 2019, 2016, 2013, 2010 и Excel для Mac.
Как суммировать количество ячеек с использованием диапазона переменных
Вы можете встроить функцию INDIRECT в качестве аргумента внутри функции SUM, чтобы создать диапазон переменных ссылок на ячейки для добавляемой функции SUM. Функция INDIRECT делает это, косвенно ссылаясь на диапазон ячеек через промежуточную ссылку на ячейку.
Формат обеих функций, используемых вместе, выглядит следующим образом:
Это блокирует диапазон, начинающийся с D1, и позволяет D4 изменять, если вы вставляете или удаляете любые ячейки в столбце D.
Вы также можете использовать другие ячейки в электронной таблице для изменения ссылок на ячейки. Например, если вы используете E1 для ссылки на первую ячейку диапазона и E2 для ссылки на последнюю ячейку диапазона, формула выглядит следующим образом:
Изменяя числа, расположенные в ячейках E1 и E2, вы можете изменить диапазон в формуле без необходимости вручную редактировать формулу.
Попробуйте функции SUM и INDIRECT
Создайте таблицу, чтобы самостоятельно проверить функции SUM и INDIRECT. Начните с создания пустой электронной таблицы и ввода следующих данных в столбцы D и E:
клетка Данные D1 — 5 D2 — 10 D3 — 15 D4 — 20 D5 — 25 D6 — 30 E1 — 1 E2 — 4
- Выбрать ячейку F1, Здесь будет показан результат этого примера.
- Выбрать Формулы.
- выберите Math & Trig чтобы открыть раскрывающийся список функций.
- ВыбратьSUM в списке, чтобы открыть диалоговое окно «Аргументы функций СУММ».
Гнездо НЕПРАВИЛЬНАЯ функция в функцию СУММ
Затем введите функцию INDIRECT в функцию SUM, используя это диалоговое окно.
-
в Номер 1 введите следующую функцию INDIRECT:
Когда вы понимаете, как отформатировать SUM и вложенные функции INDIRECT, вы сможете ввести всю функцию, показанную на шаге 4, непосредственно в ячейку результата (в этом примере ячейка F1).
Постройте аргумент для НЕПРЕРЫВНОЙ функции
В приведенном выше примере ссылки на ячейки E1 и E2 находятся вне кавычек. Это, в сочетании с & символ конкатенации, встраивает все, что содержится внутри ячеек E1 и E2, в формулу.
- & используется для объединения текстовых данных (в данном случае буквы D) со ссылкой на ячейку (E1 и E2), которая содержит числовое значение.
- ‘ ‘ должен окружать текстовые данные внутри ссылок на ячейки (в этом примере — буква D).
- : является разделителем между первой ячейкой и последней ячейкой в диапазоне SUM.
В приведенном выше примере начальная точка диапазона определяется символами: «D», & Е1.
Конечная точка диапазона определяется символами: «: D» и E2
Третий амперсанд в середине используется для объединения двух сегментов в один аргумент:
Диапазон изменения функции SUM
Функция INDIRECT, вложенная внутри функции SUM, позволяет легко изменять начальный и конечный диапазон диапазона, который определяется функцией SUM, без необходимости редактировать эту функцию.
Используя метод конкатенации на шаге 3, используйте цифры, хранящиеся в ячейках E1 и E2 в электронной таблице, чтобы изменить диапазон ячеек для функции SUM.
Проверьте это в своей электронной таблице, изменив ячейки E1 и E2 и посмотрите, как изменяются результаты функции SUM.
- Выбрать ячейку E1.
- Введите число 3.
- Нажмите Войти.
- Выбрать ячейку E2.
- Введите число 6.
- Нажмите Войти.
- Ответ в ячейке F1 меняется на 90. Это сумма чисел, содержащихся в ячейках D3-D6.
Чтобы увидеть функцию INDIRECT в действии, вставьте новую ячейку в ячейку D3. Это сдвигает все остальные ячейки вниз. Новая сумма — это сумма ячеек D3-D7. Функция INDIRECT динамически изменяет весь диапазон, чтобы включить весь список, который вы первоначально ввели, плюс добавленная новая ячейка.
#REF! Значение ошибки
#REF! значение ошибки появляется в ячейке F1, если функция INDIRECT:
- Содержит недопустимую ссылку на ячейку.
- Содержит внешнюю ссылку на другой лист, который не существует.
- Относится к диапазону ячеек вне пределов рабочего листа.
- Относится к пустой ячейке.
Как использовать функцию CONCATENATE Excel для объединения ячеек
Узнайте, как использовать функцию и оператор Excel CONCATENATE для быстрого объединения нескольких ячеек данных в один с помощью этого пошагового руководства.
Используйте функцию DGET Excel для суммирования больших таблиц
Функция DGET является одной из функций базы данных Excel; он используется для подсчета количества записей в базе данных, соответствующих конкретным условиям.
Функция Excel ISNUMBER для поиска ячеек с номерами
Узнайте, как использовать функцию ISNUMBER в Excel, чтобы проверить, содержат ли выбранные ячейки числовые данные с помощью этого пошагового руководства.
Как посчитать количество ячеек содержащих определенное значение в Exel статья
- Выбрать ячейку F1, Здесь будет показан результат этого примера.
- Выбрать Формулы.
- выберите Math & Trig чтобы открыть раскрывающийся список функций.
- ВыбратьSUM в списке, чтобы открыть диалоговое окно «Аргументы функций СУММ».
В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по – Минимум:
[expert_bq id=»1570″]Критерий таблица с условиями отбора, состоящая минимально из двух ячеек названия столбца, по которому идет проверка Товар и критерия Бумага, Карандаши, Ручки. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Для подсчета количества ячеек, содержащих ошибки, вы можете использовать функцию ЕОШ, завернутую в функцию СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, в которых вы хотели бы рассчитывать ошибки.
Ошибки
- Логические значения истина и ложь не учитываются, как текст
- Числа не подсчитываются «*», если они не будут введены в виде текста
- Пустая клетка, которая начинается с апострофа ( ‘) будут учитываться.
Вы можете легко настроить эту формулу, чтобы использовать содержимое другой ячейки для критериев. Например, если A1 содержит текст, который соответствует тому, что вы хотите, используйте следующую формулу:
