Блог

Главная - Вопросы по программированию - Поиск поиск повторяющихся значений и возврат альтернативного значения столбца

Поиск поиск повторяющихся значений и возврат альтернативного значения столбца

#excel #excel-formula #pivot-table

#excel #excel-формула #сводная таблица

Вопрос:

Я использую O365.

Настройка

Я пытаюсь преобразовать данные в формате A: D в формат F: K, и я в тупике, прося о помощи.

Моя первая формула в H2:

 =INDEX(C:C,MATCH(1,(A:A=F2)*(C:C="*Dental*"),0))

Это возвращает #N / A. Что я не так понимаю? Возвращает столбец C, соответствующий строке, где код EE равен F2, а план содержит «Dental».

План всегда будет включать либо «Dental», либо «United».

Затем я подумал бы использовать значения в F2 и H2 для поиска значения покрытия для этого плана EE в столбцах I и K.

Любая помощь приветствуется. Спасибо!

Редактировать:

 =INDEX(C:C,MATCH(1,(A:A=F2)*(IFERROR(SEARCH("*Dental*",C:C),0)),0))

Эта формула решает мою проблему.

Комментарии:

1. Сколько групп EE Codes может быть?

2. Всего существует около 500 не дублирующихся кодов EE (столбец F), а исходный список (столбец A) содержит около 1000 дубликатов.

3. Наверное, я не понял: может ли один EE-код иметь более одного дубликата (т.е. Три или четыре)

4. (C:C="*Dental*") не выполняет сопоставление подстановочных знаков. Он ищет точную строку. Вы можете сделать (ISNUMBER(SEARCH("Dental",C:C)))

5. Это можно сделать довольно легко с помощью Power Query, особенно если вы можете предоставить подробную информацию обо всех различных заголовках столбцов.

Ответ №1:

Я немного расширил ваш пример

введите описание изображения здесь

Если у вас есть O365, вы должны быть в состоянии выполнить следующее.

H2 =XLOOKUP($F$2:$F$5amp;"Dental*",$A$2:$A$7amp;$C$2:$C$7,C2:C7,"<not found>",2)

I2 =XLOOKUP($F$2:$F$5amp;"Dental*",$A$2:$A$7amp;$C$2:$C$7,D2:D7,"<not found>",2)

J2 =XLOOKUP($F$2:$F$5amp;"United*",$A$2:$A$7amp;$C$2:$C$7,C2:C7,"<not found>",2)

L2 =XLOOKUP($F$2:$F$5amp;"United*",$A$2:$A$7amp;$C$2:$C$7,D2:D7,"<not found>",2)

Это также позволит вам узнать, отсутствуют ли какие-либо выборы.

Вы также можете перечислить все уникальные коды EE, используя F2 =UNIQUE(A2:A7) .

XLOOKUP

Ответ №2:

В Power Query можно:

  • Группировать по EE Code и EE
  • Затем объедините столбцы типа покрытия / плана в группе, чтобы можно было разделить на отдельные столбцы.

M код

 let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EE Code", type text}, {"EE", type text}, {"Plan", type text}, {"Coverage", type text}}),
 
//Group by EE code and EE
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EE Code", "EE"}, {{"Grouped", each _, type table [EE Code=nullable text, EE=nullable text, Plan=nullable text, Coverage=nullable text]}}),

//Exapnd the Grouped columns to create a text string which can be split into the relevant columns
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        List.Combine(
            List.Zip({Table.Column([Grouped],"Plan"),Table.Column([Grouped],"Coverage")}))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

//This line will need modification if your order of coverage varies; or if the types of coverage are different
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), 
        {"Dental Plan", "Dental Coverage", "Health Plan", "Health Coverage"}),

//Remove unneeded column
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Grouped"})
in
    #"Removed Columns"

Результаты из ваших данных:

введите описание изображения здесь

Mcode потребуется изменить, если существуют другие типы покрытий или если типы покрытия расположены не в нужном порядке. Смотрите Комментарии в коде