Одна ячейка в Excel обошлась JPMorgan в $6,5 миллиарда 🤦‍♂️

Не опечатка. В 2012-м сотрудник копировал данные между файлами, и при вставке формула взяла сумму вместо среднего. Ошибку не заметили, модель неделями занижала риск, и банк потерял $6,5 млрд каскадом.

Одна ячейка. Без проверки на входе. Шесть с половиной миллиардов 😡

В Excel есть для этого охранник, Data Validation. Сидит на входе в ячейку, не пускает мусор. Вкладка Data → Data Validation (или Alt+D+L).

Custom-формулы, самое мощное 😎

Тут ты сам пишешь правила. Антидубликаты:

 =COUNTIF($A$2:$A$100,A2)<=1

Только рабочие дни:

 =WEEKDAY(C2,2)<6

Обязательное заполнение:

=LEN(A2)>0

Если бы вначале я знал, как бояре любят изворачиваться в отчётах, изучил бы Data Validation самым первым.

И тут грабли: Ctrl+V обходит валидацию. Копипастой можно запихнуть что угодно мимо правил. Лечение: добавь защиту листа (Sheet Protection). Два замка надёжнее одного.

Базовые ограничения ✅

1️⃣ Выпадающие списки (List). Столбец «Статус задачи», один пишет «Готово», другой «готово», третий «сделал Петров». Ставишь List, задаёшь варианты через запятую или именованный диапазон. Творчество закончилось.

2️⃣ Числа и даты (Whole number, Decimal, Date). В столбце «Скидка» кто-то вбивает 500%. Или в дату ставит 2077 год. Задаёшь диапазон: скидка 0–50, дата от 01.01.2020 до сегодня. Дрянь не пролезет.

Ещё грабли, на которые наступают 🤔

1️⃣ Error Alert в режиме Warning. Пользователь видит предупреждение, жмёт «ОК» и спокойно пишет что попало. Ставь режим Stop. Тогда без вариантов.

2️⃣ Input Message. При настройке правила есть вкладка Input Message: вписываешь подсказку «Вводи только статус из списка, руками не дописывай». Большинство эту вкладку даже не открывают. А зря, сразу меньше «творчества».

3️⃣ Circle Invalid Data. Data → Circle Invalid Data. Обводит красным ячейки, где накосячили с правилами. Кайф для ревизии старых файлов, которые заполняли до тебя.

Открой любую рабочую таблицу. Поставь хотя бы одно правило на ключевой столбец. Через неделю нажми «Обвести неверные данные», увидишь, сколько мусора скопилось.
Спойлер: обычно дофига 🤷‍♀️

#excel

@data_dzen 🙂

На Уолл-стрит джунам отрезают провод от мыши 🤷‍♀️

На Reddit эту байку пересказывают годами, и в ней куда больше здравого смысла, чем кажется.
Бесконечно тянемся к мыши при работе. Тратим на это тонну рабочего времени - за год набегают сотни часов чистой рутины.
А если админ в запарке стянул мышь со стола. Что тогда, работу останавливать? 😁

В 2024-2025 Microsoft добавила в Excel 365 новые шорткаты и фичи. Кто предпочитает мышку, про них вряд ли знает.
Вот 10 комбинаций, которые закрывают большую часть ежедневных операций. От простых к свежим.

1️⃣ Alt+= - мгновенная автосумма

Выделяешь диапазон, жмёшь Alt+=, СУММ() вставляется сама. Работает и для строки, и для столбца, и для нескольких столбцов разом. На каждой сумме экономишь 5-10 секунд, за день набегает приличная экономия.

2️⃣ Ctrl+T - умная таблица за секунду

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

3️⃣ Ctrl+Shift+L - фильтры вкл/выкл

Нажал = включил автофильтр. Нажал ещё раз = выключил. Никаких заходов в меню «Данные».

4️⃣ F4 - двойной агент

Внутри формулы: переключает ссылку между $A$1, A1, $A1, A$1. Вне формулы: повторяет последнее действие. Залил ячейку цветом? F4 повторит заливку на следующей. Два режима, одна клавиша.

5️⃣ Ctrl+E - Flash Fill

Задаёшь 1-2 примера в соседнем столбце, жмёшь Ctrl+E. Excel сам распознаёт паттерн и заполняет весь столбец. Вытащить имя из ФИО, домен из email, поменять регистр - всё без единой формулы. Появился ещё в 2013-м, но до сих пор вызывает «вау» даже у опытных. Да и чего уж тут. Я сам на момент когда работал с Power Query не знал про все шорткаты.

6️⃣ Ctrl+; и Ctrl+Shift+; - дата и время

Ctrl+; вставляет сегодняшнюю дату. Ctrl+Shift+; - текущее время. Статически, без пересчёта. Вместо тысячи =СЕГОДНЯ().

7️⃣ Alt+H+O+I - автоподбор ширины столбцов

А это мой фаворит. Очень уж нравится когда ширина таблиц отшлифована до идеала. Вместо двойного клика по границе каждого столбца мышкой: выделяешь всё (Ctrl+A), жмёшь Alt → H → O → I. Все 20, 30, 50 столбцов подгоняются под содержимое разом.

8️⃣ Ctrl+` (обратный апостроф) - показать все формулы

Переключает весь лист между результатами и формулами. Для аудита - самое то. Уж точно лучше чем проверять формулы по одной через F2.

9️⃣ Ctrl+Shift+V - вставка только значений

Microsoft добавила в Excel 365 в августе 2024. Один из самых запрашиваемых шорткатов за всю историю Excel. Коли часто копируешь в таблицы данные извне без нее никуда.
​
1️⃣0️⃣ Alt+W+E+F - Focus Cell

Подсвечивает всю строку и столбец активной ячейки, аки линейка на экране. В широких таблицах на 30+ столбцов глаза больше не блуждают.
​
Всего десять штук, запомнишь на раз-два. А разницу в скорости почувствуешь ещё быстрее 💯

Если у вас не срабатывает один из этих шорткатов - скорее всего, у вас русская версия Excel.
Дело в том, что комбинации вроде Alt + W + E + F - это не глобальные хоткеи, а последовательные нажатия клавиш-подсказок (keytips) на ленте, и в локализованных версиях буквы отличаются. Чтобы узнать актуальные подсказки именно для вашей версии, просто зажмите клавишу Alt - над каждым элементом ленты появятся нужные буквы. Дальше нажимайте их по цепочке. Например, Focus Cell в русском Excel вызывается через Alt → О → Я1 → Ф.

Завтра закину постец про то как поставить свеженький Office без сомнительных активаторов 😎

Попробуй потыкать в работе. А сколько ты знал комбинаций до поста?

🔥 - знал почти все
👍 - половину знал, половину нет
😎 - Ctrl+Shift+V и Focus Cell - это что вообще??

#excel

@data_dzen 🙂

Из 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 😎

Ну вот и подходит наш путь по 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 🙂