Evolcom.ru

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

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

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

У меня есть список материалов, который я хочу отфильтровать по нескольким столбцам, а затем подсчитать количество уникальных значений в столбце Part Number. Этот подсчет должен обновляться, когда я изменяю критерии фильтрации в других столбцах. Как я могу это сделать? Спасибо!

1 ответ 1

Формула предполагает, что данные находятся в диапазоне от A2 до A10. Вы должны настроить ссылки для ячейки A2 и диапазона A2:A10 в соответствии с вашими данными.

Это формула массива, которая должна быть введена с помощью Control — Shift — Enter.sta caracter.

Если вы посмотрите на выражение более внимательно, то увидите, что оно зависит от использования матриц в сочетании с четырьмя функциями Excel. Это объясняется ниже. Для иллюстрации обсуждение будет сосредоточено на следующем примере с 9 значениями в ячейках A2:A10 и только 5 видимыми после фильтрации.

Функция MATCH обычно используется для поиска значения в другом диапазоне значений и, если оно найдено, для возврата позиции строки, в которой найдено совпадающее значение, что проявляется следующим образом

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

Если интервал содержит повторяющиеся значения, то для каждого повторяющегося значения возвращается один и тот же элемент строки. Используя данные примера, функция MATCH выведет результат:

Немного поработав, этот результат можно использовать для подсчета того, сколько раз каждое значение встречается во входном массиве: обратите внимание, например, что позиция строки 3 для значения 67 встречается три раза в массиве результатов, что соответствует трем встречам этого значения во входном массиве. *

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

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

Эта функция, однако, возвращает массив ячеек таким образом, что SUBTOTAL создает результат массива с помощью OFFSET. Эта формулировка, приписываемая Лорану Лонгре, дает массив из 1 для видимых ячеек и 0 для скрытых.

Этот результат позволяет в последующих расчетах вычислять только те значения из диапазона, которые видны после фильтрации. **

Функция FREQUENCY принимает два аргумента, массив значений данных и массив диапазонов, и возвращает массив, указывающий, сколько значений диапазона находится в каждом из них. Например, если значения в диапазоне ячеек были (1.5, 2.5, 3.5), FREQUENCY вернет подсчет значений данных, которые меньше или равны 1.5, больше 1.5 и меньше или равны 2.5, и т.д.

В формуле массив данных выражается с помощью комбинации операторов IF и SUBTOTAL/OFFSET и MATCH. По сути, это поэлементное И для массивов, созданных этими выражениями.

Массив bin, образованный выражением ROW(A2:A10)-ROW(A2)+1 — это просто значения от 1 до 9, соответствующие возможным позициям строк в диапазоне A2:10.

Частоты соответствуют 2 раза 98, отображаемым в фильтрованном диапазоне, 2 раза 67 и 1 раз 56.it.it это

Поскольку целью является подсчет количества различных значений в отфильтрованном диапазоне, конечный включающий IF — IF(FREQUENCY(. ), 1) — преобразует ненулевые значения в массиве частот к 1:

Сложив эту таблицу, мы получаем окончательный ответ 3.ой Маск

) в выражении MATCH включена для экранирования таких символов, как ‘+’ и ‘>’, которые могли бы выдавать значение ошибки, если бы они были первыми символами в сопоставляемых значениях (потому что Excel скорее воспринимал бы их как операторы чем персонажи).

Читайте так же:
Как правильно суммировать числа с единицами в диапазоне в Excel?

** Первый аргумент функции SUBTOTAL, значение 3, указывает функции вернуть количество видимых ячеек. Наиболее часто используемое значение, 9, дает сумму видимых ячеек.

Сложение значений в зависимости от цвета ячеек в EXCEL

Цвет ячейки нельзя использовать для суммирования значений в EXCEL (по крайней мере, в EXCEL 2016 и более старых версиях). Вероятно, найдется немного пользователей, которым это необходимо.

Рассмотрим столбец A, содержащий диапазон ячеек. Чтобы создать группы значений, пользователь выделил ячейки цветом.

Значения в ячейках должны быть добавлены в соответствии с цветом фона. Основная задача: Как «объяснить» функции add, что добавлять нужно только значения, расположенные, например, в зеленых ячейках?

Это можно сделать несколькими способами, три из которых: использование автофильтра, макрофункции CURRENT.BEACH() и VBA.рание ст

С помощью Автофильтра (ручной метод)

  • Добавьте еще один столбец справа с заголовком Color Code.
  • Выделите заголовки и нажмите CTRL+SHIFT+L, чтобы открыть Автофильтр (подробнее здесь)
  • Откройте меню Автофильтр, выберите зеленый цвет

  • Будут отображаться только строки с зелеными ячейками
  • Введите цифру 1 перед каждым «зеленым» значением.

  • Проделайте то же самое со всеми цветами.

Введите формулу =SUMMEDI(B7:B17;E7;A7:A17), как показано в примере файла (лист фильтра).

Вычислите значения, используя функциюCALCULATION().

С помощью Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ()

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

Идея заключается в том, чтобы автоматически отображать в соседнем столбце цифровой код фона ячейки (в MS EXCEL все цвета имеют соответствующий цифровой код). Для этого нам нужна функция, которая может вернуть этот код. Ни одна обычная функция не может этого сделать. Воспользуемся макрофункцией FOLLOW.cell(), которая возвращает код цвета заливки ячейки (она может делать многое, но нам нужно только это свойство).

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

Примечание: Макрофункции — это набор функций в EXCEL версии 4, которые нельзя использовать непосредственно в листе EXCEL современных версий, а только как именованную формулу. Макросы — это промежуточный вариант между обычными функциями и функциями VBA. Для работы с этими функциями необходимо сохранить файл в макроформате *.xlsm.

  • Сделайте ячейку B7 активной (это важно, поскольку в формуле мы будем использовать относительную адресацию)
  • В диспетчере имен введите формулу =GET.cell(63;Macrofunction!A7)
  • Дайте ей имя Цвет.

  • Закройте менеджер имен
  • Введите формулу =Цвет в ячейку B7 и скопируйте ее.

В следующем разделе мы добавляем значения, как и в предыдущем разделе.

Функция макросов работает нерегулярно

  • Если вы измените цвет ячейки, макрофункция не обновит значения кода (для этого скопируйте формулу с B7 вниз или выделите ячейку, нажмите F2 и затем ENTER )
  • Функция возвращает только 56 цветов (так называемая палитра EXCEL), т.е. если цвета расположены близко друг к другу, например, зеленый и светло-зеленый, коды этих цветов могут совпадать. Для получения дополнительной информации об этом см. пример рабочего листа «Цвета». Поэтому значения ячеек с разными цветами будут суммироваться.

С помощью VBA

На рабочем листе VBA есть файл примера, в котором решение представлено с использованием VBA. Решений может быть много:

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