Создайте новый список по горизонтали с уникальным значением в Excel

#arrays #excel #list

Вопрос:

Предположим, у меня есть данные в Sheet1 Excel, подобные этому

 C           D
ID-YEAR   LEVEL
1-2005      1
1-2006      2
1-2006      2
1-2006      1
1-2007      3

Как я могу создать таблицу в Excel, в которой по горизонтали отображается уникальное значение уровня?
например,

 G           H            I
ID-YEAR   1stLevel    2ndLevel  
1-2005        1          0       
1-2006        2          1   
1-2007        3          0

Я пробовал использовать эту формулу

 =INDEX(Sheet1!$D$2:$D$6,SMALL(IF($G2=Sheet1!$C$2:$C$6,ROW(Sheet1!$C$2:$C$6)-1,""),1),COLUMNS($H$2:H2))

Но он показывает только 1-й уровень, а не все уровни

1. Вы можете сделать это с помощью Power Query или VBA. Возможно, с помощью формул. Поскольку это не бесплатная услуга по написанию кода, пожалуйста, покажите, что вы пробовали и где у вас возникли проблемы.

2. Конечно, я пробовал использовать формулу. Я отредактировал свой вопрос, если вы знаете, как решить проблему, пожалуйста, дайте мне знать. Спасибо

Ответ №1:

Вы можете сделать это с помощью Power Query. Вы должны обновить запрос при изменении данных.

Использование запроса Мощности

Выберите какую-нибудь ячейку в таблице данных
Data => Getamp;Transform => from Table/Range
Когда откроется редактор PQ: Home => Advanced Editor
Обратите внимание на название таблицы в строке 2
Вставьте приведенный ниже код M вместо того, что вы видите
Измените имя таблицы в строке 2 на то, которое было сгенерировано изначально.
Прочитайте комментарии и изучите Applied Steps , чтобы понять алгоритм и понять любые ограничения или изменения, которые вам может потребоваться внести.

Код M

 let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

/*Make Type text so as to avoid 
    automatic date translation in column 1
    be able to combine the levels in column 2*/

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID-YEAR", type text}, {"LEVEL", type text}}),

//Group by ID-YEAR, then extract the unique list of LEVEL for each year
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID-YEAR"}, {
        {"Level", each Text.Combine(List.Distinct([LEVEL]),";")}
        }),

//Split the column that lists the Levels into multiple columns

/*Change the **2** at the end of the line to the maximum number of columns/levels
        If this will be dynamic, it can be computed*/

    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Level", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), 2)
in
    #"Split Column by Delimiter"

Примечание: Если вы хотите иметь 0 вместо blanks того, где нет соответствующего уровня, в коде PQ, в конце, из пользовательского интерфейса, вы можете

Выберите все столбцы
Replace Values и заменить null на 0