Маркировка элементов на основе критериев

У меня есть таблица в Excel 2013, которая содержит категории в виде строк и элементы в виде столбцов. Знак X на пересечении категории и элемента означает, что элемент принадлежит к категории.

На другом листе у меня есть список всех пунктов. Я хотел бы выделить все элементы (или добавить X в соседнем столбце), которые относятся к определенной категории. 
Categories | Item1 | Item2 | Item 3
-----------------------------------
Category 1 |       |   X   |
Category 2 |       |   X   |   X
Category 3 |   X   |   X   |   X
Category 4 |       |       |
Category 5 |   X   |       |   X

Выделено: Категория 5 

Items | In category
-------------------
Item1 |      X
Item2 |      
Item3 |      X     <- ?

Какова формула для ячейки, на которую указывает ??

3 2
excel excel-formula worksheet-function unpivot

3 ответа:

Вы можете использовать функцию INDEX для получения информации из определенного набора координат.

Например, в этом случае мы получим значение для ячейки в 5-й строке и 1-м столбце выделенной области:

Индекс

Если мы хотим динамически найти эти значения, мы можем использовать MATCH, чтобы найти номер столбца или строки, соответствующий нашему значению следующим образом: 

=INDEX($B$2:$D$6,5,MATCH($A10,$B$1:$D$1,0))

Матч

В этом случае номер столбца динамически вытягивается на основе элемента ты в деле. Номер строки жестко закодирован как строка 5, но вы можете использовать ту же технику, чтобы вытащить номер строки

Конечный результат должен выглядеть так:

Результат

2

Если вы" сгладите " вашу таблицу (например, как здесь ), Вы можете удалить пустые строки для Value и создать сводную таблицу с опциями для фильтрации, упорядочивания и т. д. (Флаг, однако, будет числом, а не X - без копирования, вставки и замены).

1

Почему бы не начать с формулы транспозиции: http://spreadsheets.about.com/od/excelfunctions/qt/transpose.htm это дает вам все категории в виде столбцов, но затем довольно легко отфильтровать ненужные.

0