Фильтрация данных в Excel и Power BI с помощью формул
Фильтрация данных — важная задача для аналитиков и специалистов по данным. В этой статье мы подробно разберём, как можно использовать формулы Excel для фильтрации данных, а также предложим альтернативное решение с использованием языка M в Power Query, которое удобно интегрируется с Power BI.
Основы фильтрации данных в Excel
Сначала давайте рассмотрим, как отфильтровать данные в Excel, используя формулу. У нас есть таблица, содержащая следующие столбцы: "Key", "Nr", "Provisional" и "VALUE". В столбце "E" написана формула, позволяющая фильтровать данные на основе определённых условий.
Формула для фильтрации данных
Вот используемая формула:
=IF(COUNTIF([Key];[@Key])=1;"Y";
IF(COUNTIF([Key];[@Key])>2;"";
IF(COUNTIFS([Key];[@Key];[Nr];[@Nr];[Provisional];[@Provisional])=2;"";
IF(AND(COUNTIFS([Key];[@Key];[Nr];[@Nr])=1;[@Nr]=1);"Y";
IF(AND(COUNTIFS([Key];[@Key];[Nr];[@Nr])=2;[@Provisional]="N");"Y";
"")))))Эта формула возвращает "Y" для строк, которые соответствуют следующим критериям:
- "Key" уникален.
- "Key" не уникален, однако "Nr" уникален и равен 1.
- "Key" и "Nr" не уникальны, но "Provisional" равно "N".
Пример данных для анализа
Вот образец данных, с которыми мы будем работать:
| Key | Nr | Provisional | VALUE |
|---|---|---|---|
| RED1000 | 1 | Y | 14 |
| RED1000 | 2 | Y | 8 |
| BLUE1001 | 2 | Y | 18 |
| BLUE1001 | 2 | N | 29 |
| YELLOW1002 | 1 | N | 30 |
| YELLOW1002 | 1 | N | 42 |
| GOLD1003 | 2 | Y | 14 |
| PINK1004 | 1 | Y | 15 |
| PINK1004 | 2 | N | 21 |
| PINK1004 | 1 | Y | 14 |
| PINK1004 | 2 | N | 7 |
| GREY1005 | 1 | Y | 21 |
| GREY1005 | 1 | N | 16 |
Переход на язык M для Power BI
Если вы планируете фильтровать данные в Power BI, вам потребуется использовать язык M в Power Query. Вот пример кода, который выполняет аналогичную функцию фильтрации:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangeDataType = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Nr", Int64.Type}, {"Provisional", type text}, {"VALUE", Int64.Type}}),
//Indicate any Key values that are unique with a Y, other values left as null.
KeyIsUnique = Table.AddColumn(ChangeDataType, "Unique Key", each if List.Count(List.FindText(ChangeDataType[Key],[Key]))=1 then "Y" else null),
//Combine Key & Nr columns.
AddTempCol = Table.AddColumn(KeyIsUnique, "Temp1", each [Key] & "|" & Number.ToText([Nr])),
//Check if the "Unique Key" column is null then count values in TempColumn. If it's unique and Nr = 1 then add a Y else leave it null.
NrIsUnique = Table.AddColumn(AddTempCol, "NrIsUnique", each if [Unique Key]=null then if List.Count(List.FindText(AddTempCol[Temp1],[Temp1]))=1 and [Nr]=1 then "Y" else null else null),
//Combine Key & Provisional columns.
AddTempCol2 = Table.AddColumn(NrIsUnique, "Temp2", each [Key] & "|" & [Provisional]),
//Count combination of Key & Provisional columns. If it's unique and Provisional is N then add a Y.
ProvIsUnique = Table.AddColumn(AddTempCol2, "ProvIsUnique", each if [Unique Key]=null and [NrIsUnique]=null then if List.Count(List.FindText(AddTempCol2[Temp2],[Temp2]))=1 and [Provisional]="N" then "Y" else null else null),
//Combine the three result columns and remove the other columns.
AddValidRows = Table.AddColumn(ProvIsUnique, "VALID ROWS", each Text.Combine({[Unique Key], [NrIsUnique], [ProvIsUnique]})),
RemoveOtherColumns = Table.SelectColumns(AddValidRows,{"Key", "Nr", "Provisional", "VALUE", "VALID ROWS"})
in
RemoveOtherColumns Заключение
Используя приведенные формулы и код, вы сможете эффективно фильтровать данные в Excel и Power BI. Грамотная фильтрация данных позволяет сфокусироваться на необходимой информации и получать более точные аналитические выводы. Не забудьте настроить данные по своему усмотрению и поэкспериментировать с различными условиями фильтрации!
