☰

Почему поиск MIN возвращает N/A?

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
Почему поиск MIN возвращает N/A?

Я учитель, и мой класс выполнил тест. У меня есть анализ их результатов по каждому вопросу. Я хочу найти наибольшее значение в столбце B (лучший вопрос) и вернуть для него номер вопроса (столбец A).

Это работает отлично.

Затем я пробую то же самое, но чтобы найти худший вопрос, я получаю ответ #N/A. Я не совсем понимаю, почему.

Данные следующие:

    A   B
1   Q   Score
2   1   1.13
3   2   1.13
4   3   -0.94
5   4   -1.29
6   5   -1.34
7   6   -0.67
8   7   0.11
9   8   0.91
10  9a  -0.88
11  9b  -1
12  10ab    0.93
13  10c -1.21
14  11  -1
15  12  0.88
16  13  -0.83
17  14  0.94
18  15  -0.88
19  16  -0.13
20  17  0.72
21  18  -0.85
22  19  0.2
23  20  -1.32
24  21  1.19
25  22  -0.06
26  23  1.15
27  24  -0.48
28  25  0.31
29  26  1.15
30  27  0.5
31  28  1.41

Запрос =LOOKUP(MAX(B2:B31),B2:B31,A2:A31)возвращает 28, как и должно быть, но запрос =LOOKUP(MIN(B2:B31),B2:B31,A2:A31)возвращает #N/A.

Любой совет?

решение1

Если ваши данные не отсортированы, как в примере выше, вы можете использовать:
=OFFSET(A1,MATCH(MIN(B2:B31),B2:B31,0),0)
=OFFSET(A1,MATCH(MAX(B2:B31),B2:B31,0),0)

Даже для максимума ваша формула даст результат N/A, если максимум не находится в конце столбца.

Тейлин права: если данные не отсортированы, правильный результат — чистая удача, а если использовать ВПР со значением False, то данные в столбце A (столбец результата) должны быть справа от столбца B (первый столбец Score, затем Q).

При использовании Match и Offset нет необходимости в сортировке, просто введите 0 в Match, чтобы получить точное совпадение.
введите описание изображения здесь

решение2

Поиск требует, чтобы данные были отсортированы по возрастанию. Тот факт, что вариант формулы Max возвращает правильный результат, является чистой удачей.

Поиск в отсортированном диапазоне работает следующим образом:

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

Более полное описание приблизительного поиска совпадений можно найти здесь.VLOOKUP – зачем мне ИСТИНА или ЛОЖЬ?.

Если данные не отсортированы, то любой правильный результат — чистая удача.

Редактировать:

Решение с Offset, предложенное в другом ответе, является изменчивым и может быть причиной медленной работы рабочих книг. Если порядок сортировки диапазона нельзя изменить, то Index/Match является неизменяемой альтернативой.

=Index(A2:A31,MATCH(MIN(B2:B31),B2:B31,0))
=Index(A2:A31,MATCH(MAX(B2:B31),B2:B31,0))

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