☰

Как найти ближайшее число к среднему

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Как найти ближайшее число к среднему

У меня есть такой пример, где я хочу найти ближайшее значение к среднему

город и вес — это два отдельных столбца

city  weight
A      23
A      22
A      45
A      97
B      34
B      22
B      23
C      76
C      23
C      23

Я сделал поворот и вычислил средний вес для A-, который составил 46,75.

Мне нужно найти ближайшее число для A, которое в данном случае будет 45.

Думаю, мне нужно использовать индекс и сопоставление, но как это сделать, если у меня 17 000 строк с повторяющимися названиями городов и разными значениями веса?

Буду признателен за любую помощь.

Итак, ответ, который я ищу, это

Row Labels  Average of WEIGHT     nearest number
A                 46.75            45
B                 38.75            34
C                  23              23

Большинство похожих ответов не используют этот набор, пожалуйста, помогите мне составить эту формулу, которую я попробовал:

INDEX(rawdata,MATCH(MIN(ABS(weight-$B2)),ABS(weight-$B2),0),2)

Но он смотрит на весь массив веса из AC. Я хочу, чтобы он смотрел только на значения для A, когда он сравнивает среднее значение A,

И затем вес B при сравнении среднего значения B,

И ТАК ДАЛЕЕ....

Пожалуйста, дайте мне знать, что не так с моей формулой?

заранее спасибо

решение1

РЕДАКТИРОВАТЬ:

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

Похоже, вы нашлиОтвет XOR LXна аналогичный вопрос, и вы довольно близки к правильному ответу.

XOR LX использовал действительно изящную маленькую формулу, которая обходит ограничения MATCH()при поиске по неупорядоченным данным. Я объясню, как это работает, ниже.

В таблице данных, представленной ниже, я рассчитал средние значения с помощью:

=AVERAGEIF(A$2:A$11,A14,B$2:B$11)(Я получаю ответы, отличные от тех, что вы показали выше).

и наиболее близкий Weightк среднему с:

=INDEX((A$2:A$11=A14)*(B$2:B$11),MATCH(TRUE,(A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14))),0))

Обратите внимание, что это формула массива, поэтому ее необходимо вводить с помощью CTRLShiftEnter, а не просто Enter.

введите описание изображения здесь __________________________________________________________________________________

Как это работает:

ABS(B$2:B$11-B14)— это массив разностей между средним значением и всеми числами в Weightсписке. И (A$2:A$11=A14)— это массив True/Falseзначений, Trueгде бы ни Cityравнялось A14. Умножение этих двух чисел вместе дает массив этих разностей в позициях, соответствующих City = A14, с 0везде остальными.

Далее мы хотим найти минимум этих разностей, но нам придется создать немного другой массив, поскольку MIN()вернет , 0если таковые 0'sимеются в массиве.

IF(A$2:A$11=A14,ABS(B$2:B$11-B14))проверяет, где City = A14, и возвращает разницу между Weightэтими позициями и средним значением для Falseвсех остальных позиций.

Взяв минимум из этого массива, MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))получаем наименьшую разницу.только для тех позиций, где City = A14.

Теперь равенство (A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))дает массив True/Falseзначений с Trueпозицией наименьшей разницы для текущего City. MATCH()Находит позицию True, (которая является позицией ближайшего числа) и передает ее в , INDEX()чтобы вернуть фактическое значение.

Надеюсь, это поможет, и удачи.

Связанный контент