Формула Excel для получения первого и последнего непустого значения в строке и возврата заголовка столбца

Question

Допустим, у меня есть таблица Excel, объединяющая голоса в каком-то опросе за определенный период времени, как в примере ниже.

Как найти минимальное и максимальное значение, за которое проголосовали люди в данный день (заголовок столбца)?

         A        B   C   D   E   F   G    H     I   
   --------------------------------------------------
1  | Answers:   | 0 | 1 | 2 | 3 | 4 | 5 | Min | Max |
   --------------------------------------------------
2  | 2014-12-01 |   | 2 | 4 | 1 |   |   |  1  |  3  |
3  | 2015-01-01 |   |   | 1 | 4 | 2 |   |  2  |  4  |
                          ^       ^        ^-----^- Min: 2, Max: 4
                          |       |        
                          |       |_ 2 people voted "4"
                          |
                          |_ 1 person voted "2" on 2015-01-01

Answer 1

Чтобы найти минимальное значение, за которое люди проголосовали в опросе (заголовок столбца первой непустой ячейки), я использовал следующую формулу (в ячейке H2):

=INDEX(B$1:G$1, MATCH(TRUE, INDEX(B2:G2<>"", 0), 0))

Чтобы найти максимальное значение (в ячейке I2):

=LOOKUP(2, 1/(B2:G2<>""),B$1:G$1)

Под капотом

Функция поиска имеет следующую подпись: LOOKUP( value, lookup_range, [result_range] )

Что вторая формула делает следующее:

1. Разделите 1 на массив логических (истина / ложь) значений (B2:G2<>"") ; это часть 1/(B2:G2<>"") .
2. Выше полагается на тот факт, что Excel преобразует логическое значение TRUE в 1 и FALSE в 0 , поэтому результатом этого шага является список:
   • 1 , где CELL_VALUE<>"" вернул TRUE , а 1/TRUE => 1)
   • и #DIV/0! ошибки, когда CELL_VALUE<>"" возвращал FALSE и 1/FALSE => 1/0 => #DIV/0!
3. Теперь, поскольку я использовал 2 (вы можете использовать число, начинающееся с 1) в качестве значения поиска, формула соответствует последнему числовому значению в диапазоне (как вы помните, диапазон - это что-то вроде [DIV/0!, 1, 1, 1, DIV/0!, DIV/0!] , Поэтому поиск будет соответствовать четвертому пункту). Кстати: обратите внимание, что если формула ничего не соответствует, вы все равно получите ошибку # N / A.
4. На этом этапе мы знаем, какой столбец нам интересен (четвертый в диапазоне). Таким образом, последний бит, который отсутствует, - это сказать Excel, что диапазон результатов - это первая строка таблицы: B$1:G$1 , и именно здесь Excel будет искать указанный четвертый элемент.

Подробнее о формулах поиска в Excel вы можете прочитать здесь: http://blogs.office.com/2012/04/26/using-multiple-criteria-in-excel-lookup-formulas/

Надеюсь это поможет!

январь