Файлы к уроку:
Ссылки:
Описание
В этом уроке мы продолжим изучать объект List. Мы изучим несколько самых распространенных операций с листами.
Мы изучим или повторим:
- Как создать список в Power Query
- Создать список из чисел с нужным инкрементом
- Счет значений в списке с List.Count
- Объединение списков оператором &
- Объединение списков с List.Combine
- Объединить списки с сохранением только уникальных значений с List.Union
- Ссылки на значения объекта List
- Получить первое и последнее значение списка с List.First и List.Last
- Выбор значений из списка по условию с List.Select
- Получение нескольких первых и последних значений с List.FirstN и List.LastN
- Получить значения, которые входят во все списки с List.Intersect
- Получить значения, которые встречаются только однократно с List.Difference
- Сортировка списка с List.Sort
- Получение максимального и минимального значения в списке с List.Max и List.Min
- Получение нескольких максимальных и минимальных значений в списке с List.MaxN и List.MinN
- Среднее значение в списке с List.Average
- Суммирование всех значений в списке с List.Sum
- Сравнение списков
- Как проверить является ли список пустым с List.IsEmpty
- Проверить входит ли в список определенное значение в List.Contains
Создание списка из чисел
Создадим список из чисел 1, 2, 4, а дальше с 5 по 10 без промежутков.
{1, 2, 4, 5..10}
Создание списка чисел функцией List.Numbers
Параметры функции:
- Начало списка
- Количество значений
- Шаг
List.Numbers(1, 10, 3)
Посчитать количество значений в списке
Посчитаем сколько значений находится в списке. Результат — это целое число.
List.Count({"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"})
Конкатенация/объединение списков
Из двух списков получим 1, который включает все значения из обоих.
{"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"} &
{"Ария", "Виконт"}
Конкатенация списков функцией List.Combine
Объединить 2 списка в один можно и функцией. Параметр функции — это список списков, которые нужно комбинировать.
List.Combine({{"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"},
{"Ария", "Виконт"}})
Конкатенация/объединение списков функцией List.Union
Данная функция тоже объединит списки, но в результирующем списке будут присутствовать не все значения. В него войдут все значения из первого списка и только несовпадающие из второго. В результате данного примера мы получим список {«Иван», «Иван», «Иван», «Сергей», «Петр»}
List.Union({{"Иван", "Иван", "Иван", "Сергей"},
{"Иван", "Иван", "Петр"}})
Как сослаться на элемент списка
Чтобы сослаться на элемент списка нужно сначала сослаться на сам список, а потом в фигурных скобках указать индекс элемента. Индексация в Power Query начинается с 0.
В результате данной формулы получится «Iron Maiden».
{"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"}{1}
Вернуть первый элемент списка
Функция List.First возвращает первое значение в списке. В данном примере в результате получится «Judas Priest».
List.First({"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"})
Вернуть последний элемент списка
Функция List.Last вернет последнее значение в списке. В данном примере это «Ozzy Osbourne».
List.Last({"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"})
Выбрать элементы из списка по условию
С помощью функции List.Select можно выбрать элементы из списка по условию. Второй параметр функции — это условие отбора. В данном примере мы выбираем числа, которые > 80.
List.Select({1..100}, each _ > 80)
Выбрать первые N элементов списка
С помощью функции List.FirstN получим список только первых N значений. В данном случае получим список {«Judas Priest», «Iron Maiden»}.
List.FirstN({"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"}, 2)
Выбрать последние N элементов списка
Функция List.LastN вернет список из последних N элементов списка. В данном примере в результате мы получим список {«Black Sabbath», «Ozzy Osbourne»}.
List.LastN({"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"}, 2)
Пересечение списков / Поиск совпадений в списках
Функция List.Intersect находит совпадения в списках. В качестве параметра она получает список списков. В данном примере в результате получится список из одного значения {3}, потому что только это значение присутствует в каждом списке.
List.Intersect({{1,3,5},
{3,6,8},
{7,3,18}})
Разница между списками
Функция List.Difference возвращает список значений первого списка, которых нет во втором списке. В данном примере в результате получится {1}.
List.Difference({1..5},
{2..7})
Сортировка списка
Функция List.Sort сортирует значения списка по возрастанию или по убыванию.
List.Sort({"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"},
Order.Ascending)
Минимальное и максимальное значение в списке
Функции List.Max и List.Min возвращают максимальное и минимальное значение в списке соответственно.
List.Max({1..100})
List.Min({1..100})
N наименьших значений
Функция List.MinN возвращает список N наименьших значений из исходного списка. В данном примере в результате получится список {1..5}.
List.MinN({1..100}, 5)
N наибольших значений
Функция List.MaxN возвращает список N наибольших значений из исходного списка. В данном примере в результате получится список {96..100}.
List.MaxN({1..100}, 5)
Среднее значение элементов списка
Функция возвращает среднее значение элементов списка.
List.Average({1..10})
Сумма элементов списка
Функция List.Sum вернет сумму всех элементов списка.
List.Sum({1..10})
Сравнение списков
Для сравнения списков используется оператор «=». В результате получается значение TRUE/FALSE. При сравнении важен порядок значений списков. В данном примере получится FALSE не смотря на то, что значения совпадают.
{1,2}={2,1}
Является ли список пустым
Функция List.IsEmpty проверяет является ли список пустым. В результате получается значение TRUE/FALSe.
Проверить, содержит ли список определенное значение
Функция List.Contains проверяет присутствует ли в списке определенное значение. В результате возвращается значение TRUE/FALSE. В данном примере получится FALSE.
List.Contains({"Judas Priest", "Iron Maiden", "Manowar", "Black Sabbath", "Ozzy Osbourne"},
"ABBA")
Примененные функции
- List.Numbers
- List.Count
- List.Combine
- List.Union
- List.First
- List.Last
- List.Select
- List.FirstN
- List.LastN
- List.Intersect
- List.Difference
- List.Sort
- List.Min
- List.Max
- List.MinN
- List.MaxN
- List.Average
- List.Sum
- List.IsEmpty
- List.Contains
Этот урок входит в Продвинутый курс Power Query
Номер урока | Урок | Описание |
---|---|---|
1 | Power Query Продвинутый №1. Введение в Язык М 1 | В этом уроке мы узнаем, почему стоит изучать формулы М в Power Query. Какие преимущества нам дает знание языка формул М. |
2 | Power Query Продвинутый №2. Введение в Язык М 2 | В этом втором вводном уроке по языку формул Power Query мы познакомимся с самыми основами языка форму М. |
3 | Power Query Продвинутый №3. Уровни владения Языком М | В этом уроке мы узнаем зачем изучать Power Query. Какой процент задач вы сможете решить только с пользовательским интерфейсом, а какой процент вы сможете решить с использованием функций М? Об этом узнаем в это видео. |
4 | Power Query Продвинутый №4. Объекты Power Query | В этом уроке мы познакомимся с объектами Power Query: Table, List, Record. |
5 | Power Query Продвинутый №5. Объект List 1 | В этом уроке мы начнем знакомиться с объектом List в Excel Power Query. Создадим простые List (списки) из чисел и букв. Познакомимся с функциями для создания листов/списков из чисел и дат. Научимся создавать список названий месяцев. Изучим функцию List.FindText. Создадим лист с условием при помощи List.Transform. Найдем среднее значение в листе/списке при помощи простой функции List.Average. |
6 | Power Query Продвинутый №6. Объект List 2 | В этом уроке мы продолжим знакомиться с объектом List. Мы узнаем как создавать листы, как ссылаться на элементы листа и изучим несколько полезных формул листа. |
7 | Power Query Продвинутый №7. Объект Record (Запись) | В этом уроке немного подробнее изучим объект Record в Power Query. Record — это набор пар ключ-значение. Каждый Record — это строка таблицы. В этом уроке мы изучим ссылки на Record и функции Record. |
8 | Power Query Продвинутый №8. Объект Table (Таблица) | В этом уроке мы познакомимся с объектом Table в Power Query. Что это такое и какие основные функции применяются к этому объекту. |
9 | Power Query Продвинутый №9. Ссылки 1 | В этом уроке из курса по Power Query мы повторим как ссылаться на строки таблицы. |
10 | Power Query Продвинутый №10. Ссылки 2 | В этом уроке из курса обучения Power Query будем практиковать ссылки. Помимо ссылок еще повторим и изучим несколько техник и команд: объединение листов по вертикали, Table.Skip, Table.PromoteHeaders. |
11 | Power Query Продвинутый №11. Ссылки 3 | В этом уроке курса по Power Query мы повторим ссылки на запись, ссылки на значение, ссылки на другую строку. Также вспомним как создавать таблицу параметров. |
12 | Power Query Продвинутый №12. Ссылки 4: Ссылка на другой шаг запроса и много практики | В этом уроке мы повторим/научимся ссылаться на ячейку, которая находится в другом шаге запроса. |
13 | Power Query Продвинутый №13. Пользовательские функции | В этом уроке мы научимся создавать пользовательские функции. Разберем пользовательские функции на примере столбца с нарастающим итогом. |
14 | Power Query Продвинутый №14. #datetime, #duration: работа с датой и временем | В этом уроке по Excel Power Query вы научитесь пользоваться функциями #duration и #datetime для создания даты/времени и длительности. Изучите функцию DateTimeZone.UtcNow() Узнаете как складывать данные в формате datetime и duration. Познакомитесь с функцией DateTime.LocalNow(). Научитесь преобразовывать дату/время в текст при помощи DateTime.ToText() |
15 | Power Query Продвинутый №15. Текстовые функции / Функции Text.* | В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра. |
Номер урока | Урок | Описание |
---|---|---|
16 | Power Query Продвинутый №16. Важные операции: Группировка, Пивот, Анпивот, Сортировка | В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра. |
17 | Power Query Продвинутый №17. Нюансы консолидации из разных книг/листов Excel и List PositionOf | В этом уроке мы узнаем о некоторых нюансах объединения таблиц по вертикали из разных файлов. |
18 | Power Query Продвинутый №18. Разделить столбец на строки, Text.Trim | В этом уроке мы разделим текстовый столбец на строки. Данные скучены в одной ячейке. Нужно разбить ее на несколько. |
19 | Power Query Продвинутый №19. Заголовки в двух строках, List.Zip | В этом уроке мы обработаем заголовки, которые находятся в двух строках. Нам понадобится функция List.Zip |
20 | Power Query Продвинутый №20. Вычисления в Power Query | В этом уроке мы научимся выполнять вычисления в Power Query и в Power Pivot. |
21 | Power Query Продвинутый №21. Скользящее среднее, List Range | В этом уроке мы научимся считать скользящее среднее в Power Query. Для примера рассчитаем скользящее среднее предыдущих 13 значений. Нам пригодятся функции List.Range и List.Average. |
22 | Power Query Продвинутый №22. Агрегирование текста, группировка | В этом уроке вы научитесь выполнять группировку с агрегированием текстовых значений в Power Query. |
23 | Power Query Продвинутый №23. Трансформация столбцов, Table.TransformColumns | В этом уроке мы изучим функцию Table.TransformColumns. |
24 | Power Query Продвинутый №24. Разгруппировка, Скрытые возможности Table.ReplaceValue | В этом уроке мы научимся делать разгруппировку данных в Power Query, а также изучим скрытые возможности функции Table.ReplaceValue. |
25 | Power Query Продвинутый №25. Функция List.Accumulate | В этом уроке мы с нуля разберем функцию List.Accumulate. Сначала я объясню цикл For, а потом мы закрепим знания на несложных, но очень интересных примерах. |
26 | Power Query Продвинутый №26. Множественная замена текста c List.Accumulate и VBA | В этом уроке мы научимся делать множественную текстовую замену с помощью Power Query. Для этого мы воспользуемся функцией List.Accumulate. Также разберем как решить эту задачу при помощи VBA. |
27 | Power Query Продвинутый №27. Генерируем список URL (Text.Format, List.Generate, List.Accumulate) | В этом уроке мы сгенерируем список веб-страниц, в которых будет меняться параметр id. Сделаем это всего лишь в одном шаге при помощи функций Text.Format, List.Generate и List.Accumulate. |
28 | Power Query Продвинутый №28. Множественная текстовая замена c List.Generate | В этом уроке мы научимся выполнять множественную текстовую замену в Power Query с помощью функции List.Generate. Для спонсоров канала я так же расскажу как это делать в VBA. |
29 | Power Query Продвинутый №29. Генераций таблиц с List.Generate, График платежей по кредиту | Продолжим изучать функцию List.Generate. Создадим при помощи этой функции целую таблицу, которая будет представлять из себя график платежей по кредиту. |
30 | Power Query Продвинутый №30. Нарастающий итог с List.Generate | В этом уроке мы разберем функцию List.Generate, а также при помощи этой функции выполним нарастающий итог, который в сотни раз быстрее предыдущих версий нарастающего итога. |