Вопрос или проблема
У меня есть ячейка, которая использует функцию CORREL для возвращения корреляции двух строк данных в моей таблице. Проблема в том, что я хотел бы, чтобы это обновлялось в зависимости от фильтра, который я применил.
Я использую функцию SUBTOTAL для этого для других функций, таких как SUM и AVERAGE. Как мне заставить мою функцию корреляции делать то же самое и учитывать примененный фильтр?
Это не просто с одной только функцией SUBTOTAL. Единственный способ – добавить столбец произведений между двумя столбцами (столбцами, а не строками) данных, а затем вычислять корреляцию как функцию функций SUBTOTAL.
Ответ или решение
Да, функция корреляции в Excel не учитывает фильтры по умолчанию. Это создаёт проблему, если вам нужно вычислить коэффициент корреляции между данными, представляемыми в отфильтрованном виде. Стандартная функция CORREL рассчитывает корреляцию по всему диапазону данных, игнорируя текущие фильтры, что может вводить в заблуждение, особенно в ситуациях, когда необходимо учитывать только видимые данные.
Понимание проблемы
В Excel имеется функция SUBTOTAL, которая может корректно обрабатывать видимые (не применённые фильтры) ячейки. Однако просто замена CORREL на SUBTOTAL не даст нужного результата, поскольку функция корреляции в Excel не ориентирована на видимые ячейки и не может автоматически игнорировать скрытые значения.
Альтернативное решение
Для того чтобы решить эту задачу, можно воспользоваться следующим методом:
-
Создание вспомогательного столбца:
- Добавьте новый столбец между вашими данными, который будет хранить произведение соответствующих элементов из двух ряда данных. Например, если ваши данные находятся в столбцах A и B, то в столбце C вы можете ввести формулу:
=ЕСЛИ(СУММПРОИЗВ(1*([@Название_столбца_фильтра]="условие_по_фильтру")); A2*B2; "") - Этот столбец будет оставаться пустым для скрытых строк и содержать произведение для видимых.
- Добавьте новый столбец между вашими данными, который будет хранить произведение соответствующих элементов из двух ряда данных. Например, если ваши данные находятся в столбцах A и B, то в столбце C вы можете ввести формулу:
-
Использование функции SUBTOTAL:
- После создания вспомогательного столбца используйте функцию SUBTOTAL для вычисления суммы и количества видимых элементов, что позволит вам рассчитать корреляцию только для видимых данных.
- Для расчета средней суммы используйте формулы на подобии:
=СУММ(C:C) - Для количества видимых значений применяйте:
=СЧЁТ(ЕСЛИ(ПОИСКПОЗ(0; A:A; 0>0)))
-
Формула корреляции:
- Теперь на основе этих вспомогательных данных и с учётом фильтра, вы можете использовать:
=КОВАР(C:C) / (КОРЕНЬ(ДИСП(C:C)) * КОРЕНЬ(ДИСП(B:B)) - Это позволит вам вычислить коэффициент корреляции, игнорируя скрытые строки и учитывая только видимые данные.
- Теперь на основе этих вспомогательных данных и с учётом фильтра, вы можете использовать:
Заключение
Хотя Excel не позволяет напрямую использовать функцию корреляции с учётом фильтров, с помощью добавления вспомогательных столбцов и применения комбинации других функций, таких как SUBTOTAL, вы сможете обойти это ограничение. Этот подход обеспечитCorrectly calculated correlation while respecting the applied filters.