Функция Excel, которая вычисляет среднее значение, сначала исключая все нули, а затем исключая минимальное и максимальное значение

Question

У меня есть около 300 значений в таблице Excel (многие из которых равны нулю), и я хочу вычислить среднее значение, исключив нули, самое высокое значение и самое низкое значение после нуля. Какая будет подходящая формула?

Заранее спасибо.

редактировать: что я пробовал до сих пор

Предполагая, что мои значения указаны в A1:A300, я сделал следующее:

=AVERAGEIFS(A1:A100, A1:A100, "<>0", A1:A100, "<>SMALL(A1:A100, COUNTIF(A1: A100,0)+1)", A1:A100, "<>LARGE(A1: A100, COUNTIF(A1: A100))")

Он успешно сбрасывает нули, но не максимум или минимум.

Answer 1

Я предполагаю, что результат должен быть напечатан в одну ячейку без использования каких-либо других ячеек для печати промежуточных результатов. Это, вероятно, работает только с формулами массива, кодом VBA или запросами к базе данных (ответ vembutech).

Массив Формул

См. Рекомендации и примеры формул массива и Создать формулу массива для деталей.

Одно решение

В моем примере значения для усреднения находятся в ячейках сетки от А1 до А11.

Эта формула решает проблему:

=AVERAGE(IF((A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0),"",A1:A11))

после его вставки вам нужно набрать CTRL+SHIFT+ENTER (не просто ENTER, см. Создание формулы массива).

Идея этого решения

Функция AVERAGE (AVERAGEA) вычисляет среднее значение всех ячеек, содержащих число. Ячейки, содержащие строку или логическое / логическое, игнорируются. Что мы будем делать: установить эти ячейки в «» (пустая строка), которые равны 0, равны минимальному значению ячейки (0 исключено) или равны максимальному значению ячейки. В основном мы используем эту формулу:

=AVERAGE(IF(_TODO_,"",A1:A11))

Теперь нам нужно заменить TODO на какой-то контент (три описанные ситуации). Эти три ситуации проверены:

A1:A11=MAX(A1:A11)
A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11))
A1:A11=0

Второй тест немного сложен: мы хотим исключить «0» (ноль) из расчета минимального значения. Функция MIN игнорирует все нечисловые значения (например, пустую строку ""). IF($A$1:$A$11=0,"",$A$1:$A$11) устанавливает все ячейки в "", которые равны нулю. Все остальные ячейки сохраняют свои исходные значения.

Теперь мы объединяем три теста. Это делается путем помещения скобок вокруг трех тестов и объединения их знаком + , который представляет логическое OR .

(A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0)

Этот комбинированный тест теперь заполняется в нашей формуле вверху, заменяя _TODO_:

=AVERAGE(IF((A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0),"",A1:A11))

Другое решение

На основе подхода пользователя 3396592 можно также написать:

=AVERAGEIFS(A1:A11,A1:A11,"<>0",A1:A11,"<>"&MAX(A1:A11), A1:A11,"<>"&MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))

Answer 2

Надеюсь, нижеприведенная формула удовлетворит ваше требование,

= QUERY(A:A, "выберите avg(A), где A> 0")

Скажем, к примеру, если у вас 100 номеров с нулевыми строками из 300, это пропустит 100 строк и подсчитает среднее для оставшихся 200 строк.