Давайте честно: Excel любят все. Он есть на каждом компьютере, в нём можно быстро посчитать проценты, построить график и сделать сводную. Для 80% задач Excel действительно закрывает вопрос.

Но есть 20%, где он превращается из помощника в каменный мешок. И дело не в том, что Excel какой-то недостаточно профессиональный. Просто он создавался для интерактивной работы ручками, а не для автоматизации и больших данных.

Вот примеры, где Excel "не тянет": ·       Вы анализируете транзакции интернет-магазина - 2 млн. строк

Помним про ограничения на листе Excel чуть более 1 млн. строк

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

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

Что же делать?

Нужно понять, что Excel может быть только частью системы инструментов. Одним из, но не единственным. Именно здесь на сцену выходят лучшие друзья Excel - SQL и Python. Они забирают на себя то, с чем Excel не справляется, а отдают результат обратно - в удобном, привычном формате.

Для этого нужно научиться строить "мосты" между ними

Что такое "мост" простыми словами

Мост - это способ передать данные из одного инструмента в другой без копирования файлов руками и без потери качества

Пример плохого "моста" (без моста): Выгрузил отчёт из SQL в CSV — открыл в Excel — скопировал 100 000 строк вручную — Python не видит этот файл — снова сохраняешь вручную. Пример хорошего моста: Вы написали в Python запрос к SQL — данные приехали в датафрейм — вы их обработали — отправили результат в Excel-файл одной командой. Всё автоматически.

Какие бывают "мосты

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

Мост SQL - Python - Excel

Задача: Посчитать средний чек по каждому региону за последний месяц. Данные - в PostgreSQL.

Без мостов: Выгружаете CSV — открываете в Excel — 2 млн строк — Excel виснет — дальше не работать.

Используем мост: В редакторе Python (я использую Jupyter) пишем небольшой код, который: ·     Подключается к базе данных SQL ·     При помощи SQL-скрипта тянет данные из базы и записывает их в датафрейм ·     Делает необходимые преобразования созданного датафрейма, например, очищает пустые строки, агрегирует, выводит среднее значение ·     Сохраняет обработанные данные в файл Excel

Мост Excel (Power Query) - SQL

Когда Excel тянет данные из SQL сам через Power Query.

Задача: Менеджеры хотят сами обновлять отчёт по продажам, не трогая Python.

Мост SQL — Excel через Power Query: 1.     В Excel - вкладка Данные - Получить данные - Из базы данных - Из SQL Server (или через ODBC коннектор) 2.     Пишите SQL запрос и добавляете его в окно Power Query 3.     Нажимаете Загрузить 4.     Создаете нужную визуализацию из этих данных: сводную таблицу, дашборд и т.д.

Теперь менеджеры жмут Обновить всё - Excel сам бежит в SQL.

Это тоже мост, но без Python.

Можно еще добавить автоматизации и заставить планировщик Windows обновлять ваш файл по расписанию.

Мост Excel - Python - SQL (- Excel)

Задача: У вас "грязный" Excel-файл от поставщика. Нужно: очистить и преобразовать его в Python, записать в SQL, добавив данные в таблицу в базе.

В редакторе Python пишем скрипт, который: ·       Читает входящий "грязный" файл, записывает в датафрейм ·       Делает преобразования и очистку данных в датафреме ·       Подключается к базе данных SQL и записывает "чистые" данные в таблицу SQL

Почему в заголовке указано (-Excel) в скобках

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