Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как получить максимальное / минимальное количество видимых ячеек только в Excel?

Информатика в экономике и управлении

Свободное программное обеспечение для бизнеса и частных лиц.

Страницы блога

четверг, 10 ноября 2016 г.

Функция AGGREGATE в LibreOffice Calc

Основная задача функции AGGREGATE вернуть значение из диапазона, который может иметь скрытые строки, ошибки, результаты других функции SUBTOTAL и AGGREGATE. Я говорю «значение», потому что AGGREGATE может возвращать не только сумму видимых ячеек, но и находить минимальное/максимальное значение, подсчитывать количество ячеек и многое другое.

Прежде всего, следует отметить, что эта функция предназначена для работы с AutoFilter, и если вы используете ее для других целей, то можете получить неожиданные результаты. Например, если вы используете его для вертикального диапазона, но без Автофильтра, он не будет пересчитывать значение при маскировании линий. При применении к горизонтальному диапазону он не определяет скрытые столбцы, но может игнорировать ошибки ячеек или результаты СУММЕСЛИ и СЛОЖНЫЕ.

Синтаксис

AGGREGATE(Функция; Условие; Ссылка1 [; Ссылка2 [; …]])
или
AGGREGATE(Функция; Условие; Массив [; k])

Функция – это обязательный аргумент. Индекс или ссылка на ячейку содержащую значение от 1 до 19, задающее применяемую функцию для возвращаемого значения в соответствии со следующей таблицей.

Условие – это обязательный аргумент. Индекс условия или ссылка на ячейку со значением от 0 до 7, определяющий какие значения будут игнорироваться при подсчете.

Ссылка1 – это обязательный аргумент. Первый числовой аргумент (если диапазон задается списком значений внутри функции) или ссылка на ячейку, которая содержит этот числовой аргумент.

Ссылка2, 3, . – это не обязательные аргументы, которые представляют собой числовой значения или ссылки на ячейку (до 253 аргументов), для которых вам необходимо рассчитать функцию AGGREGATE.
Массив – это обязательный аргумент. Массив может быть указан границами диапазона, именем именованного диапазона или заголовком столбца.

Читайте так же:
Как подсчитать количество экземпляров, если длина строки больше X в Excel?

Замечу, что для использования названий столбцов, должна быть включена функция «Автоматический поиск заголовков столбцов и строк» (Сервис → Параметры → Calc → Вычисления).

k – Обязательный аргумент для следующих функций: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC. Это числовой аргумент, который должен соответствовать второму аргументу этих функций.

Следующие ошибки могут появляться при использовании функции:
Если аргумент k является необходимым, но не указан, то функция возвращает ошибку Err:511.

Если аргументы Function и/или Condition указаны неверно (индекс не совпадает с индексом массива), функция возвращает Err:502.

Примеры

=AGGREGATE(4;2;A2:A9)
Возвращает максимальное значение в диапазоне A2:A9 =34, в то время как =MAX(A2:A9) возвращает ошибку.

=AGGREGATE(9;5;A5:C5)
Возвращает сумму в строке A5:C5 =29, даже если некоторые из столбцов скрыты.

=AGGREGATE(9;5;B2:B9)
Возвращает сумму в столбце B =115. Если какая-либо строка скрыта, то функция опустить её значение, например, если 7-ая строка скрыта, функция вернёт 95.

В приведенном ниже примере показано, как применить функцию 3D диапазона.

=AGGREGATE(13;3;Sheet1.B2:B9:Sheet3.B2:B9)
Функция возвращает значения второго столбца сквозного (3D) диапазона листов 1:3 (который содержит те же данные) =8.

Вы можете использовать ссылку на ячейку или диапазон для любого аргумента функции. В следующем примере показано, как это работает. Это также показывает, что вы можете использовать имена столбцов для указания массива.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel для промежуточных расчетов

Функция subtotals.TOTAL в Excel используется для вычисления промежуточных итогов в таблицах (включая базы данных) и возвращает нужное числовое значение (в зависимости от номера нужной операции, указанного в качестве первого аргумента функции, например, 1 — среднее арифметическое диапазона значений, 9 — целое значение и т.д.) Эта функция чаще всего используется для модификации списков промежуточных итогов, создаваемых с помощью специальной встроенной команды Excel.

Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Функция TOTALS.TOTALS полезна, когда таблица содержит большое количество данных, которые можно отфильтровать по одному или нескольким критериям. В этом случае фильтры будут отображать только ту часть таблицы, данные которой соответствуют заданным критериям. Однако операции с использованием обычных функций, таких как SUMM, SMALLNUM и т.д., будут выполняться над всей исходной таблицей (т.е. с учетом скрытых строк). Функция TOTALS.TOTALS работает только с отфильтрованными данными.

Читайте так же:
Как подсчитать конкретное значение на нескольких листах?

Суммирование только видимых ячеек в фильтре Excel

Пример 1. Таблица продаж электрогитар, показывающая продажи трех брендов за три дня в магазине музыкальных инструментов, представлена в следующей таблице. Вам необходимо рассчитать промежуточные результаты для продаж гитары Ibanez.

Отображение таблицы исходных данных:

Пример 1.

Используя фильтр, выберите данные, относящиеся к гитарам Ibanez. Для этого выделите всю таблицу или просто подведите курсор к любой ячейке таблицы и используйте инструмент «ДАННЫЕ»-«Фильтр». В результате исходная таблица выглядит следующим образом:

Фильтр.

Нажмите на выпадающий список в колонке B («Марка товара») и отметьте только название «Ibanez»:

Марка товара.

Вот как будет выглядеть таблица после нажатия кнопки «ОК»:

Ibanez.

Теперь, как вы видите, некоторые строки скрыты. В обычной функции SUMM вычисляются все строки исходной таблицы:

СУММ.

Вместо этого ячейка C24 будет использована со следующей функцией:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

  • 9 — числовое значение, соответствующее использованию функции SUMM для промежуточных итогов;
  • C4:C20 — диапазон ячеек, содержащих данные о стоимости гитары (все остальные гитары, кроме Ibanez, не включаются в расчет).

Проведите аналогичный расчет для количества проданных гитар и общей выручки («Сумма»). Результат следующий:

расчет для количества проданных гитар.

Для сравнения, вот результаты, полученные с помощью обычной функции SUMM

Для сравнения.

Даже если некоторые строки скрыты фильтром, расчет функции SUMM учитывает все строки.

Выборочное суммирование ячеек таблицы в Excel

9 — числовое значение, соответствующее использованию функции SUM для промежуточных итогов; Ω C4:C20 — диапазон ячеек, содержащих данные о стоимости гитары (все остальные гитары, кроме марки Ibanez, в расчет не включены).

Пример 2.

В открывшемся диалоговом окне появится критерий «При каждом изменении в:». Выберите имя столбца «Дата». Далее выбираем операцию, которую мы будем выполнять над данными. Суммируем значения, выбрав «Сумма». С помощью критерия «Add totals by:» можно выбрать, какие столбцы будут суммироваться. Вы также можете установить флажки «Заменить текущие итоги» и «Итоги под данными»:

Читайте так же:
Как повернуть текст нижнего колонтитула / заголовка в альбомном режиме в Excel?

Итоги под данными.

После нажатия OK исходная таблица будет выглядеть следующим образом:

ОК.

В полученной таблице есть инструменты, позволяющие скрывать/отображать части данных и при необходимости отображать только итоговые данные. При выделении любых промежуточных итогов можно увидеть, что для их расчета использовались итоговые значения. Функция СУММЕСЛИ.

Каждая из этих функций может быть изменена по усмотрению пользователя. Например, это автоматически определяет среднюю стоимость гитар, проданных за 13.08.2018 :

средняя стоимость.

Примеры формул для расчетов промежуточных итогов в таблице Excel

Просмотр исходной таблицы данных:

Пример 3.

Давайте отфильтруем данные, используя критерий «джинсы» и дату, заданную в условии:

джинсы.

Функция TOTALS.TOTALS не имеет встроенных функций для вычисления моды и стандартного отклонения. Для расчета режима используйте следующую формулу (матричная формула CTRL+SHIFT+ENTER):

В этом случае функция TOTALS. Функция Totals возвращает ссылку на диапазон ячеек, которые исключаются из отображения при использовании фильтров. Функция IF возвращает массив, содержащий числовые значения для отображаемых строк и пустые значения «» для строк, которые не отображаются. MODA игнорирует их при вычислении. Результатом выполнения формулы является:

МОДА.

Чтобы рассчитать среднее отклонение, используйте аналогичную формулу:

СРОТКЛ.

Обычно функция TOTAL.TOTAL используется для простых вычислений. Для создания отчетов с промежуточными итогами обычно достаточно 11 функций, доступных в синтаксисе этого инструмента.

Как получить максимальное / минимальное количество видимых ячеек только в Excel?

x

x

x

Как подсчитать игнорирование скрытых ячеек / строк / столбцов в Excel?

Обычно мы можем применить функцию TOTAL, чтобы легко подсчитать количество ячеек в отфильтрованном списке. Но иногда нам может понадобиться подсчитать количество ячеек, игнорируя ячейки/строки/столбцы, которые скрыты вручную. В этой статье мы покажем вам несколько способов легкого подсчета путем исключения скрытых ячеек/строк/столбцов в Excel.

  • Счетчик игнорирует скрытые ячейки или строки с помощью функций Excel
  • Счетчик игнорирует скрытые строки и столбцы с помощью Kutools for Excel.
Читайте так же:
Как переименовать несколько листов в Excel?
Счетчик игнорирует скрытые ячейки и строки с функциями Excel

Мы представим функции TOTAL TOTAL и AGREGATE для подсчета скрытых вручную ячеек или строк в Excel.

Например, я вручную скрыл несколько строк в таблице, как показано на скриншоте ниже. И я подробно расскажу, как подсчитать количество ячеек, исключая все скрытые ячейки и строки.

Затем выберите ячейку, в которую вы хотите поместить результат подсчета, введите формулу = ПРОИЗВЕСТИ ИТОГ (102; C2: C22) (C2: C22 — диапазон данных, без учета скрытых вручную ячеек и строк) в эту ячейку и нажмите кнопку Go.

И теперь вы получите количество ячеек в указанном диапазоне, игнорируя все вручную скрытые ячейки или строки.

Ноты:
(1) Для подсчета количества всех видов ячеек, за исключением скрытых вручную ячеек и строк, примените эту формулу = ПРОМЕЖУТОЧНЫЙ ИТОГ (103; A2: A22) .
(2) В Excel 2010 или более поздней версии вы также можете применить формулу = АГРЕГАТ (2,3; C2: C22) для подсчета количества числовых ячеек с игнорированием скрытых вручную ячеек и строк. См. Снимок экрана ниже; и примените формулу = АГРЕГАТ (3,3; A2: A22) для подсчета количества всех видов ячеек с игнорированием вручную скрытых ячеек и строк.

(3) Все вышеприведенные формулы не игнорируют ячейки скрытого столбца.

Счетчик игнорирует скрытые строки и столбцы с помощью Kutools for Excel

Например, я вручную скрыл 3 столбца и 4 строки в таблице, как показано на скриншоте ниже, и я покажу вам детали для подсчета количества всех видов ячеек с игнорированием всех скрытых строк и столбцов с помощью СОВМЕСТНЫЙ функция Kutools для Excel.

Kutools for Excel — включает в себя более 300 удобных инструментов для Excel. Вы можете попробовать продукт в течение 30 дней бесплатно без предоставления кредитной карты. Получить сейчас

Читайте так же:
Как перевернуть / перевернуть столбец порядка данных по вертикали в Excel?

Выберите пустую ячейку, куда вы хотите поместить результат подсчета, и введите формулу = COUNTER (B2: H11) (B2: H11 — это диапазон, в котором вы хотите подсчитать ячейки, игнорируя скрытые ячейки/строки/столбцы), затем нажмите кнопку Go (Вперед).

И теперь вы получите общее количество всех видов ячеек в указанном диапазоне, за исключением всех скрытых строк и столбцов. Смотрите скриншот:

Примечание: Вы также можете применить это СООБЩЕНИЕ к работе следующим образом: Выберите пустую ячейку и нажмите Cthulus > Функции > Статистика и математика > КОМБИНИРОВАННЫЕ.

2. В открывшемся диалоговом окне Аргументы функции укажите диапазон, в котором будут подсчитываться ячейки, игнорируя любые вручную скрытые строки и столбцы в Справке, а затем нажмите OK. См. скриншот выше:

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Бесплатная 30-дневная пробная версия с полной функциональностью. Не требуется кредитная карта! Получить сейчас

Демонстрация: счетчик игнорирует скрытые ячейки / строки / столбцы в Excel
Сумма / количество / среднее значение видимых ячеек только в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек / строк / столбцов

Обычно функция SUM / Count / Average будет подсчитывать все ячейки в указанном диапазоне, если ячейки материи скрыты / отфильтрованы или нет. В то время как функция Subtotal может только суммировать / подсчитывать / усреднять, игнорируя скрытые строки. Однако Kutools для Excel СУЩЕСТВЕННЫЙ/СОВМЕСТНЫЙ/СРЕДНЯЯВИДИМАЯ функции легко вычисляют указанный диапазон, игнорируя любые скрытые ячейки, строки или столбцы. Полнофункциональная бесплатная 30-дневная пробная версия!
только количество видимых ячеек

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector