У меня есть файл 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
Предполагая, что таблица в том виде, в каком вы ее задаете, находится в 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 заключает в формулу фигурные скобки {} (хотя не пытайтесь вставить их вручную).