Функции SUMIF и COUNTIF удобны для простого SUM и операций COUNT на только некоторые из значений в диапазоне, но есть более общий вариант , который позволит мне подключить любую другую функцию я хочу? Например, я могу захотеть получить стандартное отклонение значений, соответствующих некоторым критериям, но нет STDDEVIF . Если нет, то как лучше всего это сделать, очевидно, без копирования данных? Я использую Excel 2007.
1
4 ответа
1
Вы должны посмотреть на функции базы данных. Они намного более могущественны, чем Сумиф, потому что критерии не ограничены. Это может быть так сложно или просто, как вам нужно. Они также имеют функции стандартного отклонения в категории базы данных. Они не позволяют вам вставить ЛЮБУЮ функцию, которую вы хотите, но она позаботится о вашей проблеме с std dev.
1
Преобразуйте диапазон данных в таблицу и добавьте итоговую строку (щелкните правой кнопкой мыши -> Таблицы -> Строка итогов). По умолчанию отображается SUM но вы можете легко изменить его на ряд других функций, включая STDDEV . Затем примените числовой фильтр к столбцу таблицы.
Изменить: (Ответить на комментарий)
- Убедитесь, что все столбцы данных имеют имя в первой строке.
- Выберите диапазон, включая первую строку, содержащую имена.
- Нажмите Ctrl + L (Windows) и убедитесь, что вы выбрали "Моя таблица имеет заголовки". Нажмите ОК.
- Теперь вы должны увидеть, что Excel применил новое форматирование к вашим данным, и у вас есть раскрывающиеся фильтры для каждого заголовка столбца - теперь у вас есть таблица Excel.
- Вы можете добавить итоговую строку, щелкнув правой кнопкой мыши в любом месте столбца, для которого хотите получить итоговую сумму. Теперь выберите Таблицы -> Итоги во всплывающем меню. По умолчанию вы получите общее (
SUM) данных в столбце. - Выберите ячейку, содержащую сумму, и справа от нее появится выпадающая стрелка. Вы можете нажать эту кнопку, чтобы изменить функцию с
SUMна желаемую функцию, напримерStdDev. - Значение рассчитывается исходя из любых значений, видимых в столбце. Вы можете применить фильтры к одному или нескольким столбцам, щелкнув раскрывающиеся списки в строках заголовка и используя фильтры чисел или меток. Рассчитанное значение изменится соответственно.
0
Другой подход, который вы хотите рассмотреть, - это использование столбца "Помощник" для пометки отфильтрованных данных:
Вы можете добавить столбец данных и использовать этот столбец, чтобы определить, является ли строка скрытой (отфильтрованной) или нет. Со значениями в столбце A, номинально заполненными данными, вы можете использовать, скажем, Col Z (новый столбец фильтра) с формулой "= промежуточный итог (3, A2)" (начиная со строки 2), и Excel автоматически заполняется до конца с помощью Cntrl+ ручка. После настройки вы можете при желании скрыть новый столбец данных фильтра.
После того, как вы установили новую настройку Filter col Z, вы можете использовать ее либо с COUNTIFS, либо с обычными условиями IF. С COUNTIFS вы можете использовать его аналогично COUNTIFS(Data_Rng, Data_Val, Filter_Rng, 1), который отфильтровывает подсчет Data_Rng для значений, равных Data_Val, только в тех строках, которые не скрыты, что определяется Filter_Rng, равным 1. Убедитесь, что ваши диапазоны совпадают по длине, иначе Excel выдаст ошибку.
Если COUNTIFS не дает вам то, что вам нужно, вы также можете установить производные условные значения на основе столбца Filter Z и использовать автоматическое заполнение Excel для заполнения этих значений IF(Z2 = 1, вычисленное новое производное значение, ошибка),
0
Не совсем ответ, но кое-что нужно учитывать: в Excel нет более сложных функций xxxxIF(), потому что сообщество вряд ли будет их использовать. Ваши примеры, SUMIF() и STDEVIF() будут использоваться очень разными сообществами. Очень легко увидеть бухгалтера, который хочет извлечь все транзакции для определенного клиента из глобального реестра (SUMIF). Статистики, с другой стороны, должны очень тщательно обосновывать удаление любого значения из набора данных. Так что автоматические фильтры маловероятны; данные будут скорректированы вручную перед анализом (STDEVIF). Следовательно, это потребует некоторой степени ручного манипулирования или очень сложных клеточных формул.
Есть ли причина, по которой вы не хотите добавлять временную таблицу для чистого пространства? Я бы с радостью вставил рабочий лист и либо вручную выбрал данные для копирования, либо написал формулы для извлечения данных из исходного листа. Как только вы позаботились о "IF", проанализируйте. Вы можете удалить или скрыть рабочий лист, когда закончите.