Excel - вернуть наиболее часто встречающееся значение на основе нескольких условий

У меня есть три основных столбца Name, Size и Diameter. Я хочу отфильтровать имя и вернуть наиболее часто встречающееся значение в Diameter для определенного значения в Size. Например, у меня есть таблица, как показано ниже:

| Name | Size     | Diameter |
------------------------------
|  A   |    30    | 2232.23  |
|  A   |    30    | 2232.23  |
|  A   |    30    | 5382.98  |
|  A   |    29    | 1123.44  |
|  A   |    29    | 9323.42  |
|  A   |    29    | 1123.44  |
|  B   |    31    | 1232.11  |
|  B   |    31    | 1232.11  |
|  B   |    10    | 1111.00  |
------------------------------

Значение, которое я должен получить от Diameter для A с Size из 30, равно 2232.23, а для B я должен получить Diameter значение 1232.11 для Size 31

Это всего лишь его образец. Фактические данные составляют более 9000 строк. Спасибо.


excel excel-formula ms-office
person azriebakri    schedule 10.10.2017    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Учитывая, что ваши данные находятся в столбцах A, B и C, вы можете поместить эту формулу массива в ячейку D1.

=ИНДЕКС(C$1:C$10,MODE(IF(A$1:A$10=A1,MATCH(B$1:B$10,B$1:B$10,{0,0}))))

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

Не забудьте нажать Ctrl+Shift+Enter.

person Imran Malek    schedule 10.10.2017
comment
Спасибо. Работает как шарм :) - person azriebakri; 17.10.2017

arrow_upward
0
arrow_downward

Попробуйте вставить в ячейку D2 и перетащить в последнюю строку:

=СЧЁТЕСЛИМН(A:A,A2,B:B,B2,C:C,C2)

Он возвращает количество вхождений в каждой строке.

person Sasha Sachi    schedule 10.10.2017

arrow_upward
0
arrow_downward

Используйте эту формулу. Формула сначала создает массив значений, которые соответствуют двум заданным условиям. затем формула ЕСЛИ удаляет 0 значений из массива. Наконец, формула MODE оценивает оставшиеся значения и возвращает значение с наибольшим количеством вхождений.

=SUMPRODUCT(IFERROR(MODE(IF(--($A$3:$A$11000=G2)*($B$3:$B$11000=H2)*$C$3:$C$11000<>0,--($A$3:$A$11000=G2)*($B$3:$B$11000=H2)*$C$3:$C$11000,"")),MAX(--($A$3:$A$11000=G2)*($B$3:$B$11000=H2)*$C$3:$C$11000)))

Введите его с помощью CTRL+Shift+Enter, так как это формула массива.

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

если вы хотите отобразить большинство вхождений в ColumnD, используйте эту формулу в ячейке D3 и перетащите ее вниз.

=SUMPRODUCT(IFERROR(MODE(IF(--($A$3:$A$11000=A3)*($B$3:$B$11000=B3)*$C$3:$C$11000<>0,--($A$3:$A$11000=A3)*($B$3:$B$11000=B3)*$C$3:$C$11000,"")),MAX(--($A$3:$A$11000=A3)*($B$3:$B$11000=B3)*$C$3:$C$11000)))

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

person Kresimir L.    schedule 10.10.2017

arrow_upward
0
arrow_downward

Вот формула массива (нажмите Ctrl + Shift + Enter), которую вы можете попробовать:

=INDEX($C$2:$C$20,MATCH(MODE(IF(($A$2:$A$20=E2)*($B$2:$B$20=F2)*($C$2:$C$20),($A$2:$A$20=E2)*($B$2:$B$20=F2)*($C$2:$C$20),"")),$C$2:$C$20,0),1)

По сути, он использует функцию MODE для поиска наиболее часто встречающегося случая, а затем использует INDEX/MATCH для возврата значения.

person ian0411    schedule 10.10.2017