Из Excel в космолет за две надстройки 😎
Excel, которым пользуются в 90% компаний и за глаза ругают, на деле использует лишь каплю возможностей. Данные заливают руками, типы правят вручную, а вопрос «оно само обновится?» вызывает тяжёлый вздох. Потому что две вкладки в меню так и остались нетронутыми.
Расписание у большинства такое: скачал файл, почистил руками, добавил формулы, отправил боярину. Назавтра всё сначала. Послезавтра всё сначала. Скука смертная. 😔
Сорок минут ежедневно на работу, которая должна занимать три клика.
Теперь добавь 2 миллиона строк. Excel упирается в потолок на миллионе строк и ложится. Ок, делишь файл на части. Теряешь связи между таблицами, сводная начинает врать.
А можно включить 2 надстройки и использовать Excel на максимум.
Я познакомился с этими надстройками в 2017-м. Подрабатывал на фрилансе. Сайты на Wordpress да всякая рутина. Прилетает задача. Нужно из 60 толстых excel файлов вытянуть только определённые группы товаров и всё объединить в одном файле. Дали два дня. Попробовал 2 файла руками собрать. Ужас, скучно и нудно. Начал копать сеть, вдруг есть волшебная пилюля. И нашёл Power Query. Чуть повозившись с интерфейсом, я подключил все файлы из папки и собрал целевой файл за 10 минут. Я аж привстал. Ладно, пойдем расскажу, что за звери эти надстройки 💪
1️⃣ Power Query: данные без рук
Он уже вшит. Вкладка «Данные», раздел «Получить и преобразовать». Подключаешь источник один раз, настраиваешь трансформацию, дальше жмёшь «Обновить». Процесс, который занимал час, теперь занимает десять секунд.
Но есть проблемка. Power Query «угадывает» типы данных, глядя лишь на первые 200–1000 строк, зависит от источника. Для CSV хватает 200, для других форматов чуть больше. Но суть одна: если данные неоднородные, числа тихо становятся текстом без единого предупреждения.
Потому чтоб спать спокойно, типы данных всегда проставляй вручную. Не доверяй автоопределению. Да, подольше. Зато потом не надо перелопачивать старые настройки и искать проблему.
И ещё. Не делай один огромный запрос. Всегда в работе используй итеративный подход.
Цепочка работает лучше: отдельный шаг на очистку, отдельный на преобразование. Загрузку — тоже отдельно. Когда что-то сломается (а это случится), найдёшь проблему за минуту, а не за час.
И да, PQ позволяет подключать базы данных напрямую ✅
2️⃣ Power Pivot: Excel без потолка
1 048 576 строк — лимит обычного Excel. Power Pivot держит десятки миллионов через VertiPaq-сжатие (тот же движок, что внутри Power BI). Сам xlsx-файл при этом становится тяжелее, внутрь упаковывается база данных. Зато в памяти данные занимают в разы меньше: VertiPaq сжимает примерно в 10 раз (ну, порядок такой — точная цифра зависит от данных), хранит колоночно, а не построчно. Большие данные летают.
Главное, что там есть — меры. Не лепи всё в вычисляемые столбцы: они считаются всегда и раздувают модель, а мера считается только тогда, когда ты её видишь в сводной, под конкретный контекст фильтра. Разница в разы.
Сердце Power Pivot — функция CALCULATE. Она меняет контекст вычисления. «Продажи без возвратов», «план только по Москве», «что если цена выросла на 10%». Это всё CALCULATE.
Для любителей яблочек (Apple), коим и я являюсь 😁
Power Pivot недоступен на macOS. Да грустно, но как есть. Если в команде есть коллеги на Mac, они просто не увидят этого чуда.
Если у тебя отчёты крутятся вокруг Excel — попробуй эти две надстройки 👇
#excel
