Из 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
@data_dzen🙂
Excel, которым пользуются в 90% компаний и за глаза ругают, на деле использует лишь каплю возможностей. Данные заливают руками, типы правят вручную, а вопрос «оно само обновится?» вызывает тяжёлый вздох. Потому что две вкладки в меню так и остались нетронутыми.
Расписание у большинства такое: скачал файл, почистил руками, добавил формулы, отправил боярину. Назавтра всё сначала. Послезавтра всё сначала. Скука смертная.
Сорок минут ежедневно на работу, которая должна занимать три клика.
Теперь добавь 2 миллиона строк. Excel упирается в потолок на миллионе строк и ложится. Ок, делишь файл на части. Теряешь связи между таблицами, сводная начинает врать.
А можно включить 2 надстройки и использовать Excel на максимум.
Я познакомился с этими надстройками в 2017-м. Подрабатывал на фрилансе. Сайты на Wordpress да всякая рутина. Прилетает задача. Нужно из 60 толстых excel файлов вытянуть только определённые группы товаров и всё объединить в одном файле. Дали два дня. Попробовал 2 файла руками собрать. Ужас, скучно и нудно. Начал копать сеть, вдруг есть волшебная пилюля. И нашёл Power Query. Чуть повозившись с интерфейсом, я подключил все файлы из папки и собрал целевой файл за 10 минут. Я аж привстал. Ладно, пойдем расскажу, что за звери эти надстройки
Он уже вшит. Вкладка «Данные», раздел «Получить и преобразовать». Подключаешь источник один раз, настраиваешь трансформацию, дальше жмёшь «Обновить». Процесс, который занимал час, теперь занимает десять секунд.
Но есть проблемка. Power Query «угадывает» типы данных, глядя лишь на первые 200–1000 строк, зависит от источника. Для CSV хватает 200, для других форматов чуть больше. Но суть одна: если данные неоднородные, числа тихо становятся текстом без единого предупреждения.
Потому чтоб спать спокойно, типы данных всегда проставляй вручную. Не доверяй автоопределению. Да, подольше. Зато потом не надо перелопачивать старые настройки и искать проблему.
И ещё. Не делай один огромный запрос. Всегда в работе используй итеративный подход.
Цепочка работает лучше: отдельный шаг на очистку, отдельный на преобразование. Загрузку — тоже отдельно. Когда что-то сломается (а это случится), найдёшь проблему за минуту, а не за час.
И да, PQ позволяет подключать базы данных напрямую
1 048 576 строк — лимит обычного Excel. Power Pivot держит десятки миллионов через VertiPaq-сжатие (тот же движок, что внутри Power BI). Сам xlsx-файл при этом становится тяжелее, внутрь упаковывается база данных. Зато в памяти данные занимают в разы меньше: VertiPaq сжимает примерно в 10 раз (ну, порядок такой — точная цифра зависит от данных), хранит колоночно, а не построчно. Большие данные летают.
Главное, что там есть — меры. Не лепи всё в вычисляемые столбцы: они считаются всегда и раздувают модель, а мера считается только тогда, когда ты её видишь в сводной, под конкретный контекст фильтра. Разница в разы.
Сердце Power Pivot — функция CALCULATE. Она меняет контекст вычисления. «Продажи без возвратов», «план только по Москве», «что если цена выросла на 10%». Это всё CALCULATE.
Для любителей яблочек (Apple), коим и я являюсь
Power Pivot недоступен на macOS. Да грустно, но как есть. Если в команде есть коллеги на Mac, они просто не увидят этого чуда.
Если у тебя отчёты крутятся вокруг Excel — попробуй эти две надстройки
#excel
@data_dzen
Please open Telegram to view this post
VIEW IN TELEGRAM
Please open Telegram to view this post
VIEW IN TELEGRAM
Пятница пришла, давайте похвалим себя за труды 😎
Обсуждали год → собрали за неделю. Проект по медицине региона, который месяцами ходил по кабинетам, наконец пошёл в разработку💪
За одну неделю с командой упаковали всё, что растягивали на согласования почти 12 месяцев, и отдали в работу. Ирония в том что разработка займёт меньше времени, чем её обсуждение😂
Каждый кусочек проекта это попытка упростить жизнь заказчику. Меньше ручного труда, головной боли. Ну и вишенкой на торте: экономия бюджета в перспективе.
22 месяца на закупку, 2 на работу
Gartner считает, что средний цикл государственных IT-закупок — около 22 месяцев. В коммерции 6–7. Разница в три с лишним раза. Свежесть этого исследования неизвестна, но цифры +/- совпадают с реалиями.
ТЗ в госсекторе часто пишут не ради продукта, а ради ГОСТов. Документ на 80–100 страниц. 10% про функционал. Остальное: стандарты, методички, регламенты. А ещё обязательные паузы, заседания комиссий, окна для жалоб, перезапуски процедур. Вот и набегают те самые 22 месяца.
Когда контракт наконец подписан, на реализацию остаётся 2–3 месяца. Отчётность может занимать до 30% объёма работ, потому что документы проходят через 3–4 уровня контроля.
Хотели как лучше, а получилось ...🤦♂️
Система задумана как защита от недобросовестных исполнителей. На деле выкручивает руки тем, кто хочет сделать нормальный продукт.
А вот проблемные компании за счёт сильных юристов и умения работать с формулировками ТЗ нередко отбиваются от контролирующих органов.
Видел я такую конторку. У них в штате даже не было разработчиков, и по итогу при ревью кода выяснилось что каждый раз код писали разные люди. Видимо фрилансеры😁
Вот такая вот «защита». Коли в компании штат юристов больше, чем специалистов - это первый звоночек.
ГОСТы на месте, а качество.. нуу как повезёт🤷♀️
Государство тратит годы, чтобы подробно описать, как потратить деньги. А потом даёт команде 2–3 месяца, чтобы всё реализовать. Документация ничего не говорит о качестве продукта, зато каждый ГОСТ на месте.
Но мы пробиваемся и делаем то, что нужно людям. И эта неделя, маленькая победа, за которую себя точно можно похвалить🔥
А теперь ваш черёд. Какие подвиги совершили на неделе?👇
@data_dzen🙂
Обсуждали год → собрали за неделю. Проект по медицине региона, который месяцами ходил по кабинетам, наконец пошёл в разработку
За одну неделю с командой упаковали всё, что растягивали на согласования почти 12 месяцев, и отдали в работу. Ирония в том что разработка займёт меньше времени, чем её обсуждение
Каждый кусочек проекта это попытка упростить жизнь заказчику. Меньше ручного труда, головной боли. Ну и вишенкой на торте: экономия бюджета в перспективе.
22 месяца на закупку, 2 на работу
Gartner считает, что средний цикл государственных IT-закупок — около 22 месяцев. В коммерции 6–7. Разница в три с лишним раза. Свежесть этого исследования неизвестна, но цифры +/- совпадают с реалиями.
ТЗ в госсекторе часто пишут не ради продукта, а ради ГОСТов. Документ на 80–100 страниц. 10% про функционал. Остальное: стандарты, методички, регламенты. А ещё обязательные паузы, заседания комиссий, окна для жалоб, перезапуски процедур. Вот и набегают те самые 22 месяца.
Когда контракт наконец подписан, на реализацию остаётся 2–3 месяца. Отчётность может занимать до 30% объёма работ, потому что документы проходят через 3–4 уровня контроля.
Хотели как лучше, а получилось ...
Система задумана как защита от недобросовестных исполнителей. На деле выкручивает руки тем, кто хочет сделать нормальный продукт.
А вот проблемные компании за счёт сильных юристов и умения работать с формулировками ТЗ нередко отбиваются от контролирующих органов.
Видел я такую конторку. У них в штате даже не было разработчиков, и по итогу при ревью кода выяснилось что каждый раз код писали разные люди. Видимо фрилансеры
Вот такая вот «защита». Коли в компании штат юристов больше, чем специалистов - это первый звоночек.
ГОСТы на месте, а качество.. нуу как повезёт
Государство тратит годы, чтобы подробно описать, как потратить деньги. А потом даёт команде 2–3 месяца, чтобы всё реализовать. Документация ничего не говорит о качестве продукта, зато каждый ГОСТ на месте.
Но мы пробиваемся и делаем то, что нужно людям. И эта неделя, маленькая победа, за которую себя точно можно похвалить
А теперь ваш черёд. Какие подвиги совершили на неделе?
@data_dzen
Please open Telegram to view this post
VIEW IN TELEGRAM
Убираем рутину из расписания через автоматизацию Excel 😎
Ну вот и подходит наш путь по Excel к завершению. На этой неделе закроем его большой статьёй и шпаргалкой по тренировке.
А пока обсудим золотую жилу для ленивого🤔
Его анонсировали в 1993-м. А он до сих пор кормит аналитиков в половине корпораций РФ. Речь про макросы.
Макрос - сценарий на VBA, который автоматизирует рутину: чистку данных, сборку отчётов, рассылку, генерацию документов. Всё локально. Без интернета.
Что умеют делать с макросами:
1️⃣ Чистят выгрузку из 1С за 8 секунд вместо 40 минут руками: пустые строки, пробелы, кривые даты, коды → нормальные названия
2️⃣ Собирают сводный отчёт из десятков региональных файлов: макрос сам обходит папки, забирает данные, клеит, считает итоги
3️⃣ Рассылают PDF через Outlook по списку адресатов - открыл, нажал, ушёл за кофе
4️⃣ Генерят сотни счетов/актов по шаблону: цикл по строкам = файл на каждого контрагента
5️⃣ Строят мини-дашборд для отдела продаж: сводная по менеджерам, топы и аутсайдеры, PDF в папку
А зачем вам это?🤔
Оптимизируем: полдня ручной работы, человеческие ошибки, «ой, забыл Х поле».
Получаем: 2–3 минуты работы машины, предсказуемый результат каждый раз.
Почему в РФ без VBA вообще никуда
В госах, банках, ВПК, промышленности - станции физически отрезаны от интернета. Office Scripts, Power Automate, Copilot - не работают без облака от слова совсем.
VBA - единственный встроенный способ автоматизации в таких контурах. IT-отдел не нужен. Согласования доступов, лицензии на RPA? Забудьте.
Факт: Наша дорогая ФНС с 120 000 рабочих мест годами жила на макросах как основном способе убить рутину. Переход на RPA начали только в 2025-м. Так как им потребовалась работа в браузере и связка с дополнительными программами.
Где засада🤦♂️
1️⃣ Жёсткие диапазоны: записал макрос под A1:F100, через месяц данных до строки 500 - код не обрабатывает новое, а ты уверен что всё ок
2️⃣ Привязка к ActiveSheet: запустил из другой книги - упал или посчитал не то
3️⃣ Нет обработки ошибок: без Option Explicit и On Error код ломается в проде на ровном месте
4️⃣ Обновление Excel/Windows: апдейт ломает старые макросы - от зависаний до ошибок на простых операциях
Лечим динамическими диапазонами, явными ссылками на листы, обработкой ошибок и документацией версий (Да да, той самой которую никто и никогда не хочет вести).
Это решает диалоги вроде:
— "У нас все поломалось, срочно почини"
— "Ну чего вам надо, у меня все работает. Это у вас кривые настройки"
Аналитик с VBA в закрытом контуре = человек у которого больше половины рабочего дня свободного время на кофе да шутки-прибаутки с коллегами. Ну или вдруг на создание гениального отчета… Главное сильно не кичиться такой автоматизацией, а то боярин нагрузит лишней работкой😂
Есть ли у вас в практике рутинные действия, что повторяются бесконечно и их можно было закрыть макросами?
Накиньте примеров в комменты👇
И конечно попробуйте потестить. Вдруг поможет и вам💯
#Excel
@data_dzen🙂
Ну вот и подходит наш путь по Excel к завершению. На этой неделе закроем его большой статьёй и шпаргалкой по тренировке.
А пока обсудим золотую жилу для ленивого
Его анонсировали в 1993-м. А он до сих пор кормит аналитиков в половине корпораций РФ. Речь про макросы.
Макрос - сценарий на VBA, который автоматизирует рутину: чистку данных, сборку отчётов, рассылку, генерацию документов. Всё локально. Без интернета.
Что умеют делать с макросами:
А зачем вам это?
Оптимизируем: полдня ручной работы, человеческие ошибки, «ой, забыл Х поле».
Получаем: 2–3 минуты работы машины, предсказуемый результат каждый раз.
Почему в РФ без VBA вообще никуда
В госах, банках, ВПК, промышленности - станции физически отрезаны от интернета. Office Scripts, Power Automate, Copilot - не работают без облака от слова совсем.
VBA - единственный встроенный способ автоматизации в таких контурах. IT-отдел не нужен. Согласования доступов, лицензии на RPA? Забудьте.
Факт: Наша дорогая ФНС с 120 000 рабочих мест годами жила на макросах как основном способе убить рутину. Переход на RPA начали только в 2025-м. Так как им потребовалась работа в браузере и связка с дополнительными программами.
Где засада
Лечим динамическими диапазонами, явными ссылками на листы, обработкой ошибок и документацией версий (Да да, той самой которую никто и никогда не хочет вести).
Это решает диалоги вроде:
— "У нас все поломалось, срочно почини"
— "Ну чего вам надо, у меня все работает. Это у вас кривые настройки"
Аналитик с VBA в закрытом контуре = человек у которого больше половины рабочего дня свободного время на кофе да шутки-прибаутки с коллегами. Ну или вдруг на создание гениального отчета… Главное сильно не кичиться такой автоматизацией, а то боярин нагрузит лишней работкой
Есть ли у вас в практике рутинные действия, что повторяются бесконечно и их можно было закрыть макросами?
Накиньте примеров в комменты
И конечно попробуйте потестить. Вдруг поможет и вам
#Excel
@data_dzen
Please open Telegram to view this post
VIEW IN TELEGRAM
Please open Telegram to view this post
VIEW IN TELEGRAM