Excel: определить столбец с определенным значением

Question

У меня есть файл Excel с таблицей следующего формата, там около 50 столбцов

A B C D E id
1 0 0 0 0  1
0 1 0 0 0  2
0 0 0 0 1  3

Гарантируется, что одна строка будет иметь ровно 1 значение, равное '1', остальные будут равны 0

Как я могу преобразовать вышеупомянутое в формат:

val  id
 A    1
 B    2
 E    3

Answer 1

Предполагая, что таблица в том виде, в каком вы ее задаете, находится в A1:F4 (с заголовками в строке 1), сначала введите эту формулу в H1:

= СУММ (А2: Е4)

Затем введите эту ** формулу массива **** в I1:

= IF(ЧСТРОК ($ 1:1)> $ H $ 1, "", ИНДЕКС ($ A $ 1:$ E $ 1, малый (ЕСЛИ ($ A $ 2:$ E $ 4, колонка ($ A $ 2:$ E $ 4)- MIN (колонка ($ A $ 2:$ E $ 4))+1), ЧСТРОК ($ 1:1))))

Скопируйте эту формулу вниз (хотя не ту, что в H1 - это единовременно), пока вы не начнете получать пробелы для результатов.

Начальное предложение IF разработано таким образом, что в строках, в которые формула копируется сверх ожидаемого количества возвратов (заданных формулой в H1), возвращается пробел. Это работает, поскольку ROWS ($ 1:1) (что равно 1), когда копируется последовательно, ROWS ($ 1:2) (= 2), ROWS ($ 1:3) (= 3) и т.д., И т.д., И т.д. ,

Конструкция IF в функции SMALL генерирует массив значений, состоящий из относительных номеров столбцов для всех случаев, когда где-то в этом столбце есть 1. Функция SMALL, имеющая в качестве параметра k конструкцию ROWS($ 1:1), как указано выше, возвращает в последовательных строках 1-е такое значение, затем 2-е такое значение и т.д., И т.д.

Эти индексы столбцов затем передаются в INDEX для получения соответствующей записи из заголовков.

Затем введите эту (не массив) формулу в J1:

= ЕСЛИ (I1 = "", "", ИНДЕКС ($ F $ 2:$ F $ 4, MATCH (1, ИНДЕКС ($ A $ 2:$ E $ 4,, MATCH (I1, $ A $ 1:$ E $ 1,0)), 0)))

Скопируйте при необходимости.

С уважением

** Формулы массива вводятся не так, как «стандартные» формулы. Вместо того, чтобы просто нажимать ENTER, вы сначала удерживаете CTRL и SHIFT, и только потом нажимаете ENTER. Если вы сделали это правильно, вы заметите, что Excel заключает в формулу фигурные скобки {} (хотя не пытайтесь вставить их вручную).