Как в sql подтягивать параметр из excel

Создание запроса с параметрами в Microsoft Query

При запросе данных в Excel можно использовать входное значение ( параметр), чтобы указать что-то о запросе. Для этого нужно создать запрос с параметрами в Microsoft Query.

• Параметры используются в предложении WHERE запроса— они всегда работают в качестве фильтра для извлечения данных.
• Параметры могут запрашивать у пользователя входное значение при запуске или обновлении запроса, использовать константы в качестве входного значения или использовать содержимое указанной ячейки в качестве входного значения.
• Параметр является частью запроса, который он изменяет, и его нельзя повторно использовать в других запросах.

Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами (Power Query).

Последовательность действий

1. Щелкните Данные >Получить & Преобразование данных >Получить данные >из других источников > из Microsoft Query.
2. Следуйте шагам мастера запросов. На экране Мастер запросов — готово выберите Просмотр данных или изменение запроса в Microsoft Query и нажмите кнопку Готово. Откроется окно Microsoft Query и отобразит запрос.
3. Нажмите кнопку>SQL. В диалоговом SQL найдите предложение WHERE — строку, которая начинается со слова WHERE, обычно в конце SQL кода. Если предложение WHERE не существует, добавьте его, введя WHERE в новой строке в конце запроса.
4. После where введите имя поля, оператор сравнения (=, , LIKE и т. д.) и одно из следующих данных:
   • Для запроса generic parameter (?) введите вопросии (?). В подсказке, которая появляется при запуске запроса, не отображается полезная фраза.
   • Введите фразу, заключенную в квадратные скобки, для запроса параметра, который помогает вводить допустимые данные. Фраза отображается в запросе с параметрами при запуске запроса.
5. Завершив добавление условий с параметрами в предложение WHERE, нажмите кнопку ОК, чтобы запустить запрос. Excel запрос на в качестве значения для каждого параметра, Microsoft Query отобразит результаты.
6. Когда вы будете готовы загрузить данные, закройте окно Microsoft Query, чтобы вернуться к Excel. Откроется диалоговое окно Импорт данных.
7. Чтобы просмотреть параметры, нажмите кнопку Свойства. Затем в диалоговом окне Свойства подключения на вкладке Определение нажмите кнопку Параметры.
8. В диалоговом окне Параметры отображаются параметры, используемые в запросе. Выберите параметр в области Имя параметра, чтобы просмотреть или изменить параметр How value is obtained. Вы можете изменить запрос параметра, ввести определенное значение или указать ссылку на ячейку.
9. Нажмите кнопку ОК, чтобы сохранить изменения и закрыть диалоговое окно Параметры, а затем в диалоговом окне Импорт данных нажмите кнопку ОК, чтобы отобразить результаты запроса Excel.

Теперь в книге есть запрос с параметрами. При запуске запроса или обновлении подключения к данным Excel проверяет параметр, чтобы завершить предложение WHERE запроса. Если параметр запросит значение, Excel отобразит диалоговое окно Введите значение параметра для сбора входных данных. Вы можете ввести значение или щелкнуть ячейку со значением. Вы также можете указать, что указанное значение или ссылка всегда должны использоваться, а при использовании ссылки на ячейку можно указать, что Excel должно автоматически обновлять подключение к данным (то есть повторно выполнить запрос) при внесении изменений в указанную ячейку.

Как в sql подтягивать параметр из excel

Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И мы представляем первую статью.

Данная статья относится к надстройке Power Query в Excel 2010/2013, к группе Скачать и преобразовать вкладки Данные в Excel 2016, и к экрану Get Data в Power BI Desktop. Термин «Power Query» используется в том же контексте, что и в предыдущих статьях.

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

Ссылка на значение ячейки в Редакторе запросов

Предположим, что источник данных – таблица Excel, подобная такой:

Импортируем её в Power Query. Чтобы получить данные из ячейки второго столбца второй строки щелкаем по ней ПКМ и выбираем пункт Детализация углублением:

Готово, мы получили 5 в ответе:

Обратите внимание, что это значение 5 не то же самое, что и в ячейке таблицы. Запрос Power Query может вернуть любой тип данных. В данном случае будет возвращено значение целочисленного типа, а не значение типа таблица. Если вывести результаты этого запроса на лист Excel, то мы увидим отформатированную таблицу. Но если использовать результаты этого запроса в качестве входных данных для другого (например, как фильтр в SQL-запросе), то иметь данные целочисленного типа удобнее, чем таблицу из одной строки и столбца.

Ссылка на значение ячейки в Редакторе кода

Вот код для действий на скриншотах выше. Вероятно, вы догадались как он работает.

let Источник = Excel.CurrentWorkbook()[Content], "Измененный тип" = Table.TransformColumnTypes( Источник, , , > ), poleB = #"Измененный тип"[poleB] in poleB

Рассмотрим эти три шага: – Источник – получаем данные из таблицы Excel
– «Измененный тип» – устанавливаем тип данных для трех столбцов в целочисленный
– poleB – возвращает значение ячейки из второй строки столбца В (строки начинаются с 0).

Код позволяет ссылаться на отдельные ячейки таблицы, используя систему координат из имени столбца и номера строки, еще раз повторим, что нумерация строк начинается с нуля. Поэтому выражение:

«Измененный тип»[poleB]

вернёт значение ячейки из второй строки столбца poleB., т.е 5. Аналогично, выражение

«Измененный тип»[poleC]

вернёт значение 3, соответствующее первой строке столбца poleC.

Отметим, что ссылки на столбец и строку могут идти в любом порядке, и выражение #»Измененный тип»[poleB] вернёт то же самое, что и

«Измененный тип»[poleB]

Но в некоторых случаях, как вы скоро увидите, порядок строк и столбцов может быть важен.

Ссылка на отсутствующие строку или столбец

Что произойдёт если использовать ссылку на отсутствующий столбец и/или строку? Конечно, мы получим сообщение об ошибке. Вернёмся к нашему примеру и запишем:

Оба выражения вернут ошибку, т.к. в таблице нет 5-ой строки и столбца poleD.

Однако вместо ошибки можно получить значение NULL, используя оператор «?» после ссылки. Например, выражение

«Измененный тип»[poleD]?

вернёт null вместо сообщения об ошибке:

Но будьте осторожны! Выражение

«Измененный тип»?[poleB]

по-прежнему возвращает ошибку, но не потому что отсутствует пятая строка, а потому что ссылка на пятую строку вернёт null, а у него нет столбца poleB.

Решением может быть изменение порядка ссылок:

«Измененный тип»[poleB]?

или применение оператора «?» для обеих ссылок:

«Измененный тип»?[poleB]?

К сожалению, применение оператора «?» не позволит избежать ошибок, если использовать отрицательные значения в ссылках строк.

Эффект первичного ключа

Знаете ли вы, что таблицы Power Query могут содержать первичный ключ (т. е. один или несколько столбцов, значения которых уникально идентифицируют каждую строку), определяемый самой надстройкой? Нет? Неудивительно, это вовсе не очевидно из пользовательского интерфейса. Однако, существует несколько ситуаций, когда Power Query определяет первичный ключ для таблицы, в том числе:

1. Когда импортируются данные из таблицы реляционной базы данных, подобной SQL Server, и таблица уже имеет первичный ключ.
2. Когда используется кнопка Удалить повторения чтобы убрать повторяющиеся значения из столбца или столбцов, скрытно вызывается функция Table.Distinct()
3. Когда к таблице применяется функция Table.AddKey()

Рассмотрим следующую таблицу Excel, в основном такую же, что приводилась выше, но с новым столбцом, который однозначно идентифицирует каждую строку.

Если вы загрузите таблицу в Power Query, щелкните ПКМ по заголовку столбца poleKey и выберете пункт Удалить повторения, то установите этот столбец первичным ключом.

(Кстати, можно использовать функцию Table.Keys(), чтобы увидеть, какие ключи определены для таблицы Power Query).

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

let Источник = Excel.CurrentWorkbook()[Content], "Измененный тип" = Table.TransformColumnTypes( Источник, , , >), "Удаленные дубликаты" = Table.Distinct( "Измененный тип", ), "Строка 2" = "Удаленные дубликаты"<[poleKey="Строка 2"]>[poleB] in "Строка 2

Обратите внимание на последний шаг, это важно! Вместо ссылки по номеру строки идёт ссылка по первичному ключу.

Можно продолжать использовать нотацию на основе номера строки, но если таблица имеет столбец с первичными ключами, то можно использовать нотацию с первичным ключом.

Замечания напоследок о производительности

Возможность ссылок на отдельные значения невероятно полезна в определенных типах запросов и расчётов. Однако стоит помнить, что зачастую существует несколько способов решения задачи, и не все они одинаково хороши.
Одно очевидное применение техники описанной в статье – запись предыдущих вычислений там, где необходимы ссылки на значения предыдущей строки таблицы. Но по опыту известно, что запись расчетов, использующих ссылки на строку/столбец не даёт осуществлять Query Folding («квэри фолдинг» — термин, означающий передачу тяжелых операций по обработке запросов на сторону сервера при работе с совместимой базой данных, на текущий момент это MS SQl, прим. пер.), и ведет к снижению производительности.
Возможно, альтернативные подходы (некоторые описаны в статьях Implementing Common Calculations In Power Query и Join Conditions in Power Query, Part 2: Events-In-Progress, Performance and Query Folding) будут лучшим выходом.

Нет каких-то общих правил, которые можно посоветовать, вы должны сами попробовать разные способы.

Power Query. Параметры в SQL-запросе

Вы получаете данные из базы данных. Вы хотите использовать параметр в SQL-запросе, который брал бы свое значение с листа Excel.

Решение

1. Создайте именную ячейку с параметром
2. Создайте подключение к базе данных
3. С помощью функции Text.Format добавьте параметр в этот запрос

Примененные функции

• Value.NativeQuery
• PostgreSQL.Database
• Text.Format
• Excel.CurrentWorkbook
• Text.From
• Time.From
• DateTime.LocalNow

Код

let src = Value.NativeQuery(PostgreSQL.Database("localhost", "postgres"), Text.Format( "select cast(payment_date as date), sum(amount) as amount from payment where payment_date >= # group by cast(payment_date as date) order by cast(payment_date as date)", ), null, [EnableFolding=true]), col_type = Table.TransformColumnTypes(src,>) in col_type

Power Query разное

Номер урока	Урок	Описание
1	Power Query. Знакомство с Power Query	В этом уроке мы познакомимся в Power Query. Зачем нужен Power Query Как установить Power Query Как его Настроить Как изменить запрос
2	Power Query. Подключение XML	В этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel.
3	Power Query. Уникальные значения двух столбцов	В этом уроке мы получим уникальные значения из двух столбцов таблицы.
4	Power Query. Импорт таблиц PDF	Импорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет.
5	Power Query. Собрать разбитую строку	В этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала.
6	Power Query. Пивот со счетом	В этом уроке мы создадим пивот, в котором будут пронумерованы столбцы.
7	Power Query. Минимальное значение в диапазоне	В этом уроке мы найдем минимальное значение в диапазоне строк.
8	Power Query. Нарастающий итог 2	В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query.
9	Power Query. Нарастающий итог 3	В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query.
10	Power Query. Прирост населения Китая	В этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет.
11	Power Query. Повторяющиеся значения в строке	В этом уроке разберем как определить есть ли в строке повторения.
12	Power Query. Таблица навигации по функциям М	В этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query.
13	Power Query. Удалить запросы и модель данных из книги	Разберем как быстро удалить все запросы и модель данных из текущей книги.
14	Power Query. Открыть еще 1 Excel и еще 3 трюка	В этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query.
15	Power Query. Подключиться к ZIP архиву	Пользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве.
16	Power Query. Импорт Word	Импортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками.
17	Power Query. Фильтрация списком	В этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров.
18	Power Query. Пользовательская функция Switch	В этом уроке мы создадим пользовательскую функцию Switch.
19	Power Query. Информация о формате, Чтение zip	В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.
20	Power Query. Импорт данных из gz	В этом уроке мы разберем как импортировать файл в формате gz.
21	Power Query. Удалить лишние пробелы, Text.Split	В этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы.
22	Power Query. Параметры в SQL-запросе	Вы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel.
23	Power Query. Параметры в SQL-запросе 2	Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать.
24	Power Query. Добавить столбец в каждую таблицу табличного столбца	В этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца.
25	Power Query. Интервальный просмотр 1 (ВПР 1)	Объединить 2 таблицы с интервальным просмотром = 1.
26	Power Query. Относительный путь к файлу и папке	Если ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере.
27	Power Query. Нарастающий итог в каждой категории	Применим функцию нарастающего итога не ко всей таблице, а к определенному окну.
28	Power Query. ВПР без Merge или Join	Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц.

Power Query. Параметры в SQL-запросе was last modified: 2 июня, 2022 by Admin

Из оператора в Data-инженеры: выверка данных через шаблоны Excel

Всем привет! Меня зовут Ксения, в 2019 году я пришла в СИГМУ оператором по оцифровке ГИС-планшетов с местоположением кабельных линий. Учитывая специфику деятельности компании, работа с большими данными — ежедневная практика. И если не владеть языками программирования (как в моем случае), на выручку может прийти Excel.

Внутри команды мы используем специальный шаблон для выверки данных распределительных электросетевых компаний. Он оказался настолько простым и удобным, что постепенно вместо ручной обработки данных я полностью перешла на работу в Excel. А через год работы неожиданно поняла, что научилась программировать. Это произошло настолько органично! Я даже не успела подумать, что вообще-то гуманитарий и у меня не получится использовать айтишные инструменты.

Сейчас я могу самостоятельно подготовить многоуровневый отчет, написать SQL-запрос с регулярными выражениями или создать запрос на новую разработку, которая будет понятна нашим программистам.

В этом материале хочу поделиться своим опытом работы в шаблоне Excel, который помог мне стать экспертом по выверке данных.

Начну с главного

Вот ссылка для свободного скачивания и использования.

P.S. При открытии может появиться сообщение о блокировке файла, пугаться не нужно. Решается все очень просто:

1. Откройте проводник Windowsи перейдите к папке, в которой сохранили файл.
2. Щелкните файл правой кнопкой мыши и выберите «Свойства» в контекстном меню.
3. В нижней части вкладки «Общее» установите флажок «Разблокировать» и нажмите кнопку «Ок».

Шаблон будет полезен не только специалистам с начальным уровнем подготовки, но и всем, кто хочет формализовать работу с данными и повысить свою производительность.

Какие компоненты входят в шаблон:

• Автоматическое формирование заголовков для столбцов;
• «Удобности» для функции ВПР;
• Загрузка данных из внешних СУБД;
• Пользовательские функции;
• Заготовки для быстрого создания сводных таблиц.

Специфика распределительных сетей такова, что один и тот же объект в разных источниках может называться по-разному. Например, ввод 1 и «Жилой дом ул. Лесная 8». И в базе с номером ввода 1 нет ни слова про улицу Лесная. То есть связь таблиц нужно делать по косвенным признакам. Например, по значению замеров зимнего максимума и других аналогичных полей. Думаю, что и в вашей деятельности есть подобные нюансы.

Чего помогает добиться шаблон:

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

И немного контекста. В своей работе я консолидирую данные по загрузке трансформаторов и низковольтных кабельных линий, это более 300 тыс. строк в отчете. А также работаю с координатами распределительных устройств по милицейскому адресу, те задаю алгоритмы для выделения адреса из 110 тыс. неформализованных наименований распределительных устройств. Все это великолепие усложняется расшифровкой сокращений в названиях улиц и удалением лишних фраз: подъезд, этаж, жилой дом и т.д.. Наконец, я создаю условия для написания адаптированной под специфику сетевой компании программы разбора данных на основе регулярных выражений. Где, например, по одной букве Ф можно догадаться, что речь идет об улице Фрунзе, так как в окрестностях трансформаторной подстанции N есть только одна улица с буквой Ф.

В материале я сначала покажу особенности шаблона, который может стать основой для создания других рабочих книг, а затем расскажу о примерах своей работы в Excel.

Особенности шаблона

Внешне шаблон выглядит следующим образом.

В зависимости от типа данных, листы книги раскрашиваются в различные цвета.

Оглавление и общая информация о назначении файла Excel

Заготовки с наиболее популярными SQL-запросами для выгрузки данных из внешних СУБД

Основные данные загружаются на лист из СУБД с помощью SQL-запросов

Наименования полей таблицы и комментарии к полям для автоматического формирования заголовков на листе 1

Данные добавляются на лист без загрузки из внешних СУБД (вручную)

Для каждого нового справочника создается новый лист

Заготовки сводных таблиц для «синих» и «красных» листов

Лист 1+ содержит шаблон сводной таблицы для листа 1

Сводная таблица опирается на диапазон с именем «База1»

В шаблоне зарезервированы строки с 1 по 10, и каждая имеет свое назначение.

А3 – формула для формирования названия листа

А2 – формула для расчета количества активных строк на листе

Прочие ячейки используются произвольным образом

Номер столбца на листе. Используется для функции ВПР. На эти ячейки ссылаются формулы ВПР, которые находятся на других листах книги

Содержит формулу для нахождения номера столбца в функции ВПР. Пример формулы: «=’4′!$H$4-‘4’!$B$4+1»

Строки для автоматического формирования наименований столбцов официальных отчетов. Наименование поля и порядковый номер поля в отчете

Содержит детальные комментарии к содержанию столбца. Текст комментариев берется из комментариев к полям СУБД

Строка для сохранения формул в качестве эталона

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

Основные заголовки столбцов

По умолчанию равны названиям полей из СУБД

Автоматизируем заголовки отчетов

Преимущество шаблона заключается в автоматическом формировании заголовков официальных отчетов. На листе 1 строки 6-8 содержат значения, автоматически подгружаемые из листа 2.

Названия полей и комментарии к полям загружаются вот таким запросом:

SELECT a.Column_Name as Поле, a.Column_ID as ID, b.COMMENTS as Коммент FROM all_tab_columns a left join all_col_comments b on a.owner = b.owner and a.table_name = b.table_name and a.COLUMN_NAME = b.COLUMN_NAME WHERE a.table_name = 'НАЗВАНИЕ_ТАБЛИЦЫ' AND a.owner = 'НАЗВАНИЕ_СХЕМЫ' Order by a.column_ID 

Тут также есть свои особенности. Комментарии к полям делятся на 2 части:

• До двух восклицательных знаков идет «официальная» часть. Это краткое название поля, которое будет выводиться в заголовке отчета. Рекомендуемая длина до 30 знаков.
• После двух восклицательных знаков идет подробное описание поля, которое может быть длиной до 4000 знаков (минус длина официальной части).

Заголовки на листе 1 заполняются с помощью формулы:

Лирическое отступление. Настройки панели быстрого доступа

Шаблон — отличный инструмент для ускорения и оптимизации работы в Excel с электронными таблицами. Это «адаптированное пространство» для быстрой и эффективной работы человека с начальным уровнем подготовки. Но, как и с любым другим сложным инструментом, все начинается с настроек. Чтобы возможности программы было использовать приятнее, советую настроить панель быстрого доступа – вроде, базовые инструменты, но помогают сократить цепочку действий и сэкономить минуты (которые потом выливаются в часы).

Для настройки достаточно загрузить в настройку панели быстрого доступа уже готовый файл с настройками (1).

Или настроить самостоятельно. Советую использовать следующие элементы.

Удобная методика работы с функцией ВПР

Функция вертикального просмотра (ВПР) является одной из универсальных и часто используемых при работе с данными в Excel. На просторах сети про ВПР написано немало, поэтому нет смысла останавливаться на том, что это такое и как работает. Но у нас есть свои «фишки» для этой функции.

Вот две основные:

• Использование именованных диапазонов. Например, База1, если используем для сверки первый столбец именованного диапазона База1.
• Формирование Индекса для столбца. Это гарантия того, что функция будет правильно работать после добавления или удаления новых столбцов на листе 1.

Обычно при использовании ВПР нужно указать диапазон ячеек для вычислений. Для того, чтобы каждый раз не вспоминать, какие же ячейки нужны, и не наматывать километры курсором по документу мы создаем «Базу», в которой закрепляем нужную нам область ячеек с данными. Обязательное условие для того, чтобы все это работало — столбец, по которому выверяем данные, должен быть первым (крайним левым) в данной базе. На одном листе может быть несколько баз, главное — не запутаться с их наименованиями.

Чтобы быстро найти или добавить новый именованный диапазон, кнопка «Диспетчер имен» должна быть вынесена на панель быстрого доступа.

Как я уже упоминала, вторая отличительная особенность нашего шаблона — использование индекса для столбца. Часто при сверке используются данные с разных листов документа, и проставленные ссылки на столбцы, при добавлении новых данных могут съехать. Использование индекса для столбца и абсолютных ссылок фиксирует «привязку» формулы к нужному столбцу и гарантирует, что формула не «поплывет» при добавлении новых данных.

Данные для функции ВПР подтягиваются с разных листов через «ключ» — поле с одним и тем же параметром для обоих листов. Ключ может быть просто табличным значением, а может составным — из значений двух или более полей. Чтобы каждый раз не указывать диапазон ячеек, можно создать базу на листе, с которого будут подтягиваться значения.

Пример основной формулы:

Формула для получения номера столбца (ячейка D5 на листе 1):

В каждой ячейке строки 4 листа 4 стоит формула «=СТОЛБЕЦ()», которая всегда показывает актуальный номер столбца. То есть при добавлении нового столбца на лист 4 номер столбца для ВПР автоматически изменится.

В шаблоне есть заранее заданные наименования диапазонов: «База4», «Запрос2».

Загрузка данных из внешних СУБД

Работая с Excel, рано или поздно мы сталкиваемся с необходимость загрузки каких-либо данных из внешних источников. И если внешний источник — другой файл, то можно все решить стандартным копированием, либо сохранить данные, изменив формат файла.

Если же внешний источник — база данных — нужен запрос SQL. Для этого на вкладке «Данные» нужно выбрать «Свойства» (1). И в открывшемся окне «Свойства внешних подключений» открыть окно «Свойства подключения».

В строке подключения (3):

• DSN=нужная база
• UID=логин
• PWD= пароль
• DBQ=база

в окне Текст команды (4) запрос:

• SELECT *
• FROM SCHEME.TABLE (нужная схема.таблица)
• WHERE S1_NUM=’23456′

Если выборку данных из таблицы нужно ограничить — добавляем необходимые условия.

Меняя запрос, можно экспортировать в Excel любой набор данных из одной или нескольких таблиц и обрабатывать эти данные в привычном «офисном» формате.

Пользовательские функции, которые облегчают жизнь

В нашем шаблоне есть пользовательские функции, предусмотренные для упрощения обработки данных, в том числе, созданные специально для обработки данных электросетевых компаний. Остановиться хочу лишь на нескольких.

=f_SuperMid() — последовательно вырезает из текстовой строки блоки текста, находящиеся между двумя подстроками. Функция особенно выручает, когда в большом объеме данных нужно удалить один и тот же текст.

Если нужно осуществить поиск назад (справа налево) — указываем значение «TRUE», если вперед (слева направо) — «FALSE».

1 — формула, 2 — первоначальный текст, 3 — результатТаким образом, на скриншоте:

• f_SuperMid(B58; «находится ;-та»; «false») — вырезает из первоначального текста часть между «находится» и « –та» и получает — «на балансе аб».
• f_SuperMid(B58;»ТП;(на»;»false») — вырезает из первоначального текста часть между «ТП» и « (на» и получает — номер ТП и район.

=f_NumbersOnly — удаляет из строки текст, преобразует текстовую строку в строку из чисел, разделенных пробелом (или другим разделителем). Идеально подойдет, когда нужны сухие цифры, а буквы «только мешают».

=f_TextOnly — противоположная предыдущей функции. Работает, когда нужны только текстовые значения. Удаляет из строки цифры, преобразует текстовую строку в строку из букв, разделенных пробелом.

=f_СцепитьДиапазонВСтроке — преобразует данные нескольких ячеек в единый текст одной строкой. Может собрать в строку любое количество ячеек.

Быстро, но не всегда удобно, так как функция соединяет текст без пробелов. Если знаки препинания и пробелы нужны — используйте «сцепить» с амперсандами и т.д.

=F_DeleteSubstrings — вырезает из оригинального текста заданный блок. Эта функция позволяет за одно выполнение удалить до 5 блоков «мусорного» текста. При необходимости можно прямо в коде макроса поменять 5 на 50 и автоматически собрать «длинную» формулу для удаления всего «мусорного текста».

Таким образом, на скриншоте с формулой (1) f_DeleteSubstrings(B99;$D$10) в столбцах отображается результат после удаления первого блока «подъезд строение» (2). Блок записан в строке наименований, в формуле закреплена ссылка на ячейку с текстовым блоком. Также можно получить результат после удаления второго блока «на балансе абонента» (3).

=f_FindLastNumber — находит и вырезает последний блок символов в строке после пробела. Блок может быть любым: текстовым, числовым, смешанным.

Есть еще более «узкая» функция f_FindNumbersNearKeyWords. Она используется для нахождения данных по текстовому ключу. В моем случае применяется для нахождения номера района в диапазоне от 1 до 25 (используемые нами, при желании можно изменить диапазон в скрипте функции).

Она находит и вырезает номер слева от указанного ключа «РЭР». Удобна для обработки формализованных данных, в которых используются одни и те же названия территориальных единиц. То есть везде должен быть использован либо «район», либо «РЭР». Если используются разные названия территориальных единиц, можно «докрутить» с помощью встроенных функций, например, «ЕСЛИОШИБКА»:

Конечно, многие из этих функций можно заменить формулами и получить тот же результат. Но чаще всего эти пользовательские функции — более простое и «красивое» решение, особенно если речь идет о недавних пользователях Excel, еще не освоивших весь возможный функционал.

Главное — быстро. Создание сводных таблиц

Еще один из нежно любимых инструментов Excel — cводные таблицы. Они позволяют максимально быстро преобразовать любое количество строк данных в краткий отчет. Дополнительный плюс — возможность очень оперативно изменять способ анализа путем перетаскивания полей из одной области отчета в другую.

Использование сводных таблиц даже на базовом уровне экономит огромное количество времени, при этом качество и точность анализа вырастают в разы по сравнению с попыткой «свести» данные вручную.

В шаблоне предусмотрены заготовки сводных таблиц для каждого листа с данными. Пример формирования отчета из данных электронной таблицы:

И далее отчет, сформированный из них. Не забываем, что заказчики у СИГМЫ — крупнейшие электросетевые компании, поэтому пример отражает специфику отрасли.

В отчете было сверено 3543 строки с местоположением трансформаторных подстанций. Из них однозначно верное местоположение у 881 ТП, остальные — ошибки с разными статусами: местоположение либо уже уточняется, либо неизвестно и будет выясняться посредством анализа наименования, сопоставления с названиями населенных пунктов и так далее. Так же хорошо визуализируется балансовая принадлежность — ТП на балансе и абонентские ТП.

Чем еще удобны сводные таблицы — быстрой трансформацией результата в зависимости от запроса (на основании одних и тех же данных). Например, ниже выведено процентное соотношение того или иного статуса ТП от общего количества.

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

Поговорим о применении

Здесь я приведу примеры того, как шаблон решает мои рабочие задачи. Уверена, что эти примеры можно адаптировать и под вашу деятельность. Если нужно, могу поделиться этими шаблонами по запросу.

Шаблон Ведомости загрузки трансформаторов 6-20 кВ

Исторически данный отчет формировался из 60 различных источников информации и подготавливался не менее недели. С помощью базового шаблона Excel я смогла подготовить отчет и одновременно сделать формализованную постановку для наших программистов.

Сейчас отчет, который содержит около 14 млн. полей, полностью автоматизирован и создается за 1 час. Все вычисления делаются в СУБД. Данные загружаются в Excel через ODBC с помощью SQL запроса.

Шаблон для создания таблиц Oracle

При разработке отчета «Ведомость загрузки трансформаторов (ВЗТ)» я использовала шаблон для автоматического создания таблиц в СУБД.

• Создает строки SQL-запроса, с помощью которого можно создать требуемую таблицу.
• Позволяет правильно подобрать названия полей для новой таблицы, определить тип поля и быстро заполнить поле «comment». Выгружает названия поля, тип и комментарии из таблицы, набора таблиц или целой схемы.

Шаблон для Макросов, эмулирующих ручной ввод

С помощью этого шаблона я могу создать макросы, которые «нажимают» кнопки в программе вместо оператора-человека. На скриншотах ниже приведен макрос, который загружает в систему трансформаторные подстанции с определенными ID и размещает их в точки с координатами X и Y.

Вот так выглядит макрос для двух ТП:

sigma.Callback("PoiDisplayDeviceCardsMCB", "", "11642725");) sigma.TabChange("11642725, "Container_823", "Container_896"); sigma.ValueChange("11642725", "y", "8446,9609375"); sigma.ValueChange("11642725", "x", "23436,734375"); sigma.Callback("PoiDisplayDeviceCardsMCB", "", "11642726");) sigma.TabChange("11642726, "Container_823", "Container_896"); sigma.ValueChange("11642726", "y", "8446,9609375"); sigma.ValueChange("11642726", "x", "23436,6484375");

Итоги

Неожиданно для себя за 2 года я смогла пройти путь от «уверенного пользователя» пакета MS Office до специалиста, способного за час проанализировать 5 источников, в которых хранятся данные о топологии сети 0.4-20 кВ. Параллельно найти ошибки и противоречия, и собрать из этой информации наиболее достоверную и непротиворечивую схему нормального режима, на которой сможет работать наш модуль по автоматическому расчету технических условий на технологическое присоединение.

Несмотря на то, что сейчас я уже многие вопросы решаю в самой базе данных, мне сложно представить работу без этого шаблона. Он помогает решать массу рабочих задач и является отличным инструментом обучения методам анализа данных новых сотрудников как в СИГМЕ, так и у заказчиков.

В шаблоне используются не самые популярные возможности Excel, но пользу он принесет как специалистам с начальным уровнем подготовки, так и уверенным пользователям. Особенно он облегчит жизнь тем, кто загружает данные из внешних баз, так как шаблон позволяет обрабатывать информацию в привычном формате даже без навыков работы с программными продуктами для обработки БД.

И еще несколько плюсов из практики применения шаблона:

• Обучение работе с шаблоном нового сотрудника занимает 1-2 недели. И это без предварительного опыта работы с Excel.
• Через 6-12 месяцев после начала работы с шаблоном 50% сотрудников способны перейти на работу с типовыми SQL-запросами, которые они берут готовыми из Глоссария. То есть фактически они начинают выполнять работу программистов. Шаблон Excel помогает преодолеть психологический барьер и сделать шаг от «у меня не получится» до «это же так просто».

На этом сегодня — все! Буду рада, если воспользуетесь заготовками и сможете улучшить свою производительность при работе с данными. Если появились вопросы, обязательно их задавайте. Постараюсь быстро и подробно поделиться всем, чему смогла научиться. А еще буду рада, если подкинете лайфхаки, которые не были обозначены, но могут быть использованы в повседневной работе с Excel.

• Блог компании СИГМА
• Data Mining
• Разработка для Office 365
• Учебный процесс в IT
• Data Engineering