@excel_analysis

Объединение файлов из папки с Power Query

2022-04-04T05:12:58.434Z

Задача объединения данных решается посредством опции "Получить данные"->"Из файла"->"Из папки" вкладки "Данные":

После выбора папки с файлами вылетает окно:

Нажимаем "Объединить и преобразовать данные". В появившемся окне выбираем предлагаемый лист:

После сгенерируется ряд запросов, где итоговый будем с именем папки, где хранятся файлы:

Как можно заметить, в первой колонке находятся имена файлов с данными. Дальнейшая обработка аналогична объединению информации с нескольких листов, о которой я рассказывал ранее.

Следует отметить, что во избежание проблем с объединением, следите за соответствием имен листов и колонок.

Загрузка таблиц со всех листов файла в Power Query

2022-03-17T01:07:39.074Z

Когда в файле имеется произвольное количество листов с однотипными таблицами, их можно просто объединить средствами Power Query. Для этого импортируем файл:

Выбираем символ папки с названием файла, в котором содержится перечень листов, и нажимаем "Преобразовать данные":

После этого появляется запрос с набором служебных колонок, описывающих листы, и данными, свернутыми в столбце Data:

Удаляем три последние колонки и раскрываем Data:

Выбираем колонки и получаем результат:

Далее для приведения итогов к нормальному виду следует осуществить следующие шаги:

1. Сделать первую строку заголовком

2. Переименовать первый столбец (двойной щелчок по названию):

3. Отфильтровать строки с заголовками из других листов. Для этого воспользуйтесь опцией фильтр для колонки и удалите значения с ее наименованием:

Теперь таблица примет нужный вид:

Определение оптимального ассортимента продукции в Excel

2022-03-15T05:21:32.651Z

Рассмотрим один из видов оптимизационных задач, которые можно решать с помощью инструмента "Поиск решения" в Excel. Пусть нам надо максимизировать прибыль от продажи товаров (1, 2, 3) с заданной ценой (G6:G8) при ограничениях:

1. на время работы техников (Тех А, Тех Б, Тех С, Тех D), которые производят детали:

В таблице A5-E8 на пересечении строк и столбцов стоят нули для тех деталей, которые мастер не производит, а для остальных - время на их производство. В строке 11 ограничение на общее время работы каждого мастера.

2. Максимальный объем спроса на каждый вид товара

Находится в столбце H.

Теперь об искомых значениях и сопутствующих решению формулах.

Так, на первом рисунке в строке 9 располагается фактическое общее время работы каждого мастера (это сумма произведений количества произведенной каждой детали на время для этого, например, СУММПРОИЗВ(B6:B8;B14:B16)), которое должно удовлетворять ограничению.

На пересечении столбцов B, C, D, E и строк 14-16 программа заполнит искомые объемы для произведенных деталей каждым мастером (возможны, дробные количества), чтобы максимизировать прибыль. В F14-F16 указана формула Суммы, подсчитывающая общее количество произведенных деталей 1,2 и 3, чтобы не нарушать ограничение.

И наконец, прибыль считается как =СУММПРОИЗВ(G6:G8;F14:F16) - ее нам надо максимизировать.

Теперь запускаем "Поиск решения" на вкладке "Данные", указываем целевую функцию прибыли, ставим флажок максимизации, заполняем ограничения (на нулевые значения для продукции не производимой конкретным мастером, спрос на продукцию и время работы мастеров), выбираем "Поиск решения линейных задач симплекс-методом" и жмем "Найти решение":

В результате получим:

Обратите внимание на значение прибыли и объемов произведенной продукции каждым мастером.

Конкатенация таблиц с Power Query

2022-03-13T06:29:57.349Z

Рассмотрим способ склеивания двух таблиц со схожим содержанием в Power Query. Для примера будем использовать следующие синтетические таблицы со сведениями о продаже фруктов и овощей:

Для их склеивания воспользуйтесь опцией "Добавить запросы" раздела "Объединить" вкладки "Главная":

После щелчка "Добавить запросы в новый" вылетает окно, в котором указываются таблицы:

После заполнения полей нажимаем кнопку "ОК" и получаем результат:

Следует отметить, что для склеивания можно было выбрать опцию "Добавить запросы" (а не "Добавить запросы в новый"), которая отличается тем, что данные добавляются не в новый запрос, а в текущий выбранный.

Преобразуем значения столбца к заданному виду с Power Query

2022-03-08T12:55:54.664Z

Рассмотрим ключевые функции для преобразования ячеек столбца к заданному виду с помощью Power Query. В демонстрационных целях будем использовать таблицу вида:

Извлечь значение по разделителю

Соответствующая возможность есть на вкладке "Преобразование" -> "Извлечь":

Выбираем столбец "Доход", опцию "Текст перед разделителем" и вводим в поле "Разделитель" - " руб.":

после в заданном столбце получаем очищенные значения:

Удаление пробелов слева и справа от текста

Доступно через опцию "Усечь" меню "Формат" вкладки "Преобразование":

Так как на прошлом шаге мы в качестве разделителя указали "руб.", итоговые числа получились вместе с пробелом в конце, что видно, если вывести длину столбца, доступную через все то же меню "Извлечь":

Теперь усечем колонку "Доход" и опять выведем длину:

Разделить столбец по подстроке

Удалим три крайних столбца, выделим колонку "Месяц" и в меню "Разделить столбец" вкладки "Преобразование" выберем "По разделителю":

Укажем в качестве разделителя "-" (в нашем случае распознается автоматически) и получим:

Добавить префикс и суффикс

Доступно через все то же меню "Формат" вкладки "Преобразование":

Вернем суффикс "руб." к колонке "Доход":

и получим:

Вставка производных столбцов в Power Query

2022-03-06T09:20:01.778Z

Рассмотрим наиболее популярные виды производных столбцов, которые можно легко добавлять в таблицу с Power Query. В демонстрационных целях будем работать со следующей таблицей:

Стандартные операции

Выделите числовой столбец и на вкладке "Добавление столбца" найдите пункт "Стандартный"

Выберите "Умножить", а затем столбец "Продано":

Получите новый столбец, который я переименовал в "Доход" (дважды кликнув по названию):

Также обратите внимание на пункты "Научный", "Тригонометрические", "Округление", позволяющие преобразовывать значения столбца.

Условный столбец

В том же разделе "Добавление столбца" есть пункт "Условный столбец", который позволяет задать значения нового исходя из величин в старом:

Вот результат с новым столбцом "Категория":

Дубликат столбца

Дубликаты бывают полезны для хранения обработанных значений, когда важно сопоставлять их с оригиналом, чтобы убедиться в правильности преобразований. Для создания выберите столбец и щелкните на "Создать дубликат столбца" на вкладке "Добавление столбца":

Создание индексного столбца

Все в том же разделе "Добавление столбца" имеется опция - "Столбец индекса", которая позволяет задать начальное значений и шаг:

Если заполнить их числами 5 и -1,получим следующий столбец:

Интеллектуальные свойства функции "Столбец из примеров" в Power Query

2022-03-04T05:15:09.980Z

Рассмотрим несколько выдающихся свойств функции "Столбец из примеров", которые помогут облегчить выполнение ряда операций и сэкономить время.

Для демонстрации воспользуемся следующей таблицей:

Извлечение значений

Давайте попытаемся получить из колонки "Адрес" цифровые последовательности после запятой и пробела. Для этого выделяем его и в разделе "Добавление столбца" выбираем "Столбец из примеров" -> "Из выделения":

После на панели обзора справа появляется столбец (я его переименовал в "индекс"), в котором можно задать несколько "правильных" значений для подбора подходящего правила. В нашем случае после заполнения первого поля последовательностью цифр из адреса уже предлагаются варианты для остальных полей и формула на языке "M":

Если вас что-то не удовлетворяет, попробуйте заполнить еще несколько важных значений, чтобы помочь системе определить правило. После этого нажимайте CTRL + ENTER для автоматического заполнения всех полей.

Замена значений

Замена значений осуществляется по схожему принципу. Например, в колонке "Пол" отобразим "М" в 1 и "Ж" в 0. В этом случае порядок действий аналогичный, только система потребует в качестве примера ввести значения в двух полях - одно для "М" и одно для "Ж":

Изучение

Третье свойство вытекает из первых двух. В частности, после формирования каждого "Столбца из примеров" можно изучать получившееся правило на языке "М" и узнаваться что-то новое.

Объединение Excel таблиц с помощью Power Query

2022-02-27T13:35:29.682Z

Рассмотрим, как в Power Query объединить таблицы по некоторому столбцу. Прежде всего их необходимо загрузить в Power Query как запросы (подробнее здесь).

Последующие действия рассмотрим на конкретном примере. Пусть имеются два запроса с информацией о жителях (citizens) и городах (cities):

Мы хотим добавить в таблицу citizens информацию о наименовании города по коду. Для этого выделяем запрос citizens и на вкладке "Объединить" панели "Главная" выбираем опцию "Объединить запросы":

Выбираем таблицу, с которой хотим объединить (cities), указываем колонки для соединения и тип соединения (левое, полное...). После этого жмем по кнопке "ОК" и получаем таблицу citizens со свернутыми полями cities в последней колонке:

Нажимаем в правом верхнем углу столбца на иконку для раскрытия данных (выделена на рисунке выше) и выбираем, какие поля добавить:

Если выбрать только поле "Город" и убрать чек с предложения добавить префикс к имени, то получим такой результат:

Экспорт, импорт Excel данных в Power Query

2022-02-27T13:23:36.029Z

С 2016 г. надстройка для обработки данных Power Query встроена в Excel. Однако вызов нового функционала не так очевиден для неопытного пользователя.

Если данные расположены в текущей книге, то для из загрузки в Power Query можно воспользоваться опцией "Из таблицы/диапазона" группы "Получить и преобразовать данные" вкладки "Данные":

Excel автоматически определит заполненные области диапазона и загрузит их как запрос в Power Query:

Если вы загружали умную таблицу, то запросу бы было присвоено ее имя, в нашем же случае он называется "Таблица2" (можете переименовать, если хотите).

Альтернативным вариантом загрузки данных является импорт через опцию "Получить данные" вкладки "Данные". Только после выбора источника следует нажать "Преобразовать данные", а не "Загрузить":

Допустим, вы провели преобразования и хотите выйти из редактора с сохранением проделанной работы. Для этого можно выбрать самый левый пункт "Закрыть и загрузить" на вкладке "Файл". При этом возможны две опции с сохранением и помещением результатов на новый лист или более гибкий второй -"Закрыть и загрузить в", который позволяет выборочно создать только подключение, не загружая данные (смотри на второй картинке внизу):

Когда снова захотите загрузить Power Query, зайдите в раздел "Запросы и подключения" вкладки "Данные" и выберите опцию "Запросы и подключения":

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

Получение уникальных значений в Excel

2022-02-13T11:01:00.633Z

Рассмотрим наиболее эффективные приемы удаления дубликатов в Excel. Так, распространенным способом является использование одноименной опции из вкладки "Работа с данными" и выбор столбцов, в которых не должно быть повторяющихся значений. Предварительно следует выделить весь диапазон, чтобы изменения затронули строку целиком:

Если выбрать столбцы A,B,C, то удалится последняя строка:

От дубликатов можно избавиться, используя опцию "Расширенный фильтр". Для этого в разделе "Сортировка и фильтр" на вкладке "Данные" выберите "Дополнительно", а затем в меню "Обработка":

переместите указатель на "скопировать результат в другое место";
в "Исходном диапазоне" укажите набор колонок для удаления дубликатов;
в "Поместить результат в диапазон" - ссылку на ячейку с результатом;
выделите чек "Только уникальные записи".

Аналогичным образом можно удалить дубликаты с опциями работы с умной таблицей.

Добиться получения уникальных значений можно с помощью формулы СЧЁТЕСЛИМН, в которой следует задать диапазоны проверки соответствий и ячейку поиска для каждой колонки:

Для выделения повторяющихся значений в столбце можно использовать условное форматирование с выбором в качестве "Правила выделения ячеек" "Повторяющихся значений":

Удобную визуализацию может предоставить Сводная таблица:

Теперь поместите столбец и в строки, и в значения с выбором агрегации (Количество по полю):