Microsoft Excel

Ведомости КМД как данные: что автоматизировать на Python и pandas

TL;DR: Ведомости КМД удобно обрабатывать в Python и pandas: привести выгрузку к плоской таблице, проверить данные, посчитать суммы и распределения, собрать отчёт или дашборд. Главное — не пропускать проверки.

Ведомость — не просто таблица веса. В ней детали, сборки, марки, комплекты документации. Пока объём небольшой, хватает Excel. Когда ведомостей много и структура выгрузок различается, повторяющиеся проверки превращаются в отдельный процесс.

КМД — это деталировочные чертежи металлоконструкций. Ведомости в их составе — таблицы с элементами, деталями, марками, количеством и весом. Именно такие таблицы удобно обрабатывать в Python.

Почему выгрузка не готова к анализу

• многоуровневые заголовки и объединённые ячейки;

• несколько листов с разной структурой;

• разные названия одних и тех же полей;

• числа и текст в одном столбце;

• пустые значения, дубликаты, служебные итоги;

• разные единицы измерения.

Даже суммарный вес по типам элементов начинается с выяснения, что в таблице и как читать поля. Процесс лучше делить: Excel/CSV → подготовка → проверка → расчёты → отчёт.

Подготовка в pandas

pandas читает Excel/CSV, преобразует столбцы, фильтрует, группирует и считает. Если таблица уже плоская:

import pandas as pd
df = pd.read_excel("ведомость.xlsx")
df["Вес"] = pd.to_numeric(df["Вес"], errors="coerce")
summary = (df.dropna(subset=["Тип элемента", "Вес"])
             .groupby("Тип элемента", as_index=False)["Вес"]
             .sum()
             .sort_values("Вес", ascending=False))
print(summary)

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

Проверки важнее расчёта

Число получить легко. Важнее понять, можно ли ему доверять. Проверяйте:

• обязательные поля;

• числовые столбцы;

• попадание заголовков и итогов в расчёт;

• неожиданные дубликаты;

• сходимость с контрольными суммами;

• единицы измерения.

В pandas это делается коротко:

print(df[["Тип элемента", "Вес"]].isna().sum())
print(df.duplicated(subset=["Марка", "Тип элемента"]).sum())

Набор проверок зависит от методики предприятия. Универсального правила нет, но проверки можно сделать явными и повторяемыми.

Что анализировать

• суммарный вес и количество позиций;

• распределение веса по типам элементов;

• сравнение сборок и комплектов;

• повторяющиеся детали;

• необычно тяжёлые/лёгкие позиции;

• таблицы и графики для отчёта.

Для визуализации — Plotly, для интерактивного дашборда — Streamlit. Но график лишь помогает заметить необычное значение, а не объясняет его причину.

Повторяемый сценарий

Удобно работать в Jupyter Notebook: загрузка, преобразования, проверки, расчёты. Затем повторяющиеся операции вынести в функции и шаблоны. Сохраняйте прозрачность: какие данные загружены, что преобразовано, какие строки исключены.

Что разрабатываем

Готовим курс «Мастер ведомостей» о Python и pandas для ведомостей КМД: неидеальные Excel/CSV, проверка данных, показатели, анализ сборок, визуализация, простые дашборды. Программа уточняется; будут практика, Jupyter Notebook, код и шаблоны.

Оставить заявку на уведомление о старте можно на странице курса.

Вопросы к сообществу

1. Какие операции с ведомостями вы чаще всего делаете вручную?

2. Какие проверки хотели бы сделать повторяемыми?

3. В каком виде удобнее получать результат: таблица, Excel-отчёт, графики, дашборд?

Расскажите в комментариях — это поможет выбрать сценарии для курса.

Очередное кривое обновление для Excel.

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

Виной оказалось очередное кривое обновление Security Update for Microsoft Excel KB5002914

После его удаления все приходит в норму.

ЗЫ я решил отключить у себя обновления офиса.

Сегодня обнаружил проблему с очередным обновлением от Microsoft.

Обновление KB5002903 для Excel 2016 вызывает аварийное завершение при открытии файлов xls с китайскими символами внутри.

Простое удаление этого обновления сняло проблему.

Режимы фильтрации

Удобнейшая фича таблиц в Google Sheets — возможность фильтровать данные по различным условиям. Но есть одна проблема. Если документом пользуется несколько людей, злоупотребление этой функцией приводит к хаосу. «Кто опять изменил таблицу?»

Решение: использовать режимы фильтрации.

Создать режим фильтрации можно двумя способами:

• Выбрать в главном меню «Данные / Создать режим фильтрации»

• Нажать на калькулятор рядом с названием таблицы и выбрать «Создать режим фильтрации»

Плюсы такого подхода:

1. Режим фильтрации не меняет исходную таблицу.

2. Его можно сохранить под удобным именем.

3. На сохранённый режим можно дать ссылку.

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

В Excel есть похожая функция, находится в меню «Вид / Представление листа».

Функции сортировки

Функция SORT (СОРТ) позволяет упорядочить исходную таблицу и вставить результат в другое место. По умолчанию таблица сортируется по первому столбцу в порядке возрастания:

• Sheets: =SORT(A:C)

• Excel: =СОРТ(A:C)

Для сортировки по другому столбцу можно передать его номер и направление сортировки: по возрастанию или по убыванию. В Google Sheets это TRUE и FALSE, в Excel — 1 и -1. Следующая формула сортирует таблицу по второму столбцу в порядке убывания:

• Sheets: =SORT(A:C;2;FALSE)

• Excel: =СОРТ(A:C;2;-1)

Недостаток такого подхода: при добавлении/удалении столбцов формула может сломаться, придётся вручную обновлять номер столбца. Поэтому гораздо удобнее передавать не номер, а сам столбец для сортировки. В Google Sheets для этого используется та же функция SORT, в Excel — отдельная функция СОРТПО:

• Sheets: =SORT(A:C;B:B;FALSE)

• Excel: =СОРТПО(A:C;B:B;-1)

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

• Sheets: =SORT(A:C;B:B;FALSE;C:C;TRUE)

• Excel: =СОРТПО(A:C;B:B;-1;C:C;1)

Наконец, лайфхак, про который не рассказывают в документации. Если нужно упорядочить данные по разнице столбцов B и C (пример: доходы минус расходы или цена минус себестоимость), то можно использовать формулу массива. В Google Sheets понадобится ARRAYFORMULA или MAP, в Excel всё работает и без них:

• Sheets: =SORT(A:C;ARRAYFORMULA(B:B-C:C);TRUE)

• Excel: =СОРТПО(A:C;B:B-C:C;1)

Телефонный номер

Если попытаться ввести в ячейку электронной таблицы номер телефона, внезапно пропадёт плюс:

+79876543210 → 79876543210

А при попытке вбить форматированный номер телефона и вовсе выскочит ошибка:

+7 987 654-32-10 → #ERROR

Дело в том, что значения с плюсом в начале распознаются как формулы. Как же тогда ввести телефонный номер в ячейку? Достаточно добавить в начале апостроф. Тогда электронная таблица не будет пытаться распознать формулу и просто воспримет значение как текст:

'+79876543210 → +79876543210

'+7 987 654-32-10 → +7 987 654-32-10

Этот способ пригодится в любых случаях, когда значение начинается с плюса, знака равно или похоже на дату (особенно частая проблема).

Группировка

Грех номер один при работе с электронными таблицами — ручная группировка данных.

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

На следующий день приходит задача собрать список сотрудников, но уже по городам. Чёрт, нужно всё переделывать! Создаётся новый лист, где также собирается несколько таблиц. И красивые заголовки, куда без них.

Как избежать ручной работы? Использовать группировку по столбцам в Google Sheets:

1. Собрать один длинный список сотрудников.

2. Добавить и заполнить столбцы Отдел и Город.

3. Преобразовать список в таблицу.

4. Нажать на стрелку рядом с названием столбца «Отдел» и выбрать «Столбец "Основание группировки"».

5. Сохранить получившийся фильтр под названием «Сотрудники по отделам».

6. Проделать аналогичную операцию для столбца «Город».

Итог: получилась одна таблица с данными и два её представления: «Сотрудники по отделам» и «Сотрудники по городам», между которыми можно переключаться в два клика.

К сожалению, в Excel такой функции нет.

Таблицы

Самая недооценённая функция электронных таблиц — таблицы. Что за ерунда, подумает читатель. Дело в том, что есть два английских слова: spreadsheet и table. При переводе на русский язык возникает путаница.

Таблица — это набор данных в виде столбцов (как в SQL). Изначально таблицы были реализованы в Excel, а в 2024 появилась поддержка и в Google Sheets.

Пусть есть список сотрудников из трёх столбцов: ID, ФИО и Оклад. Преобразуем его в таблицу. Для этого достаточно в любом месте диапазона с данными нажать сочетание клавиш:

• Excel: Ctrl + T (⌘ + T)

• Google Sheets: Ctrl + Alt + T (⌘ + ⌥ + T)

Поначалу кажется, что данные просто красиво отформатировали. Но это лишь внешнее изменение. Главное отличие: названия таблицы и её столбцов можно использовать в формулах в виде табличных ссылок.

Переименуем таблицу в Сотрудники (в Excel это делается не совсем очевидно). Теперь посчитаем сумму окладов двумя способами: с помощью обычных и табличных ссылок.

=SUM(C2:C7)
=SUM(Сотрудники[Оклад])

Или найдём ФИО сотрудника по ID:

=XLOOKUP(4357379;A2:A7;B2:B7)
=XLOOKUP(4357379;Сотрудники[ID];Сотрудники[ФИО])

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

Поиск по столбцу

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

Функция VLOOKUP (ВПР) появилась в 1985 году в самой первой версии Excel и занимала третье место по популярности среди пользователей (после SUM и AVERAGE). За это время она морально устарела, поэтому в 2020 году разработчики Excel добавили новую функцию XLOOKUP. В 2022 году она появилась и в Google Sheets.

Чем же XLOOKUP лучше, чем VLOOKUP?

Напомню, VLOOKUP принимает на вход четыре параметра:

1. искомое значение;

2. ссылку на таблицу (поиск идёт по первому столбцу);

3. номер столбца с результатами;

4. тип поиска: точный или приблизительный.

1️⃣ VLOOKUP закладывается на структуру исходной таблицы. Если завтра порядок столбцов поменяется, формула может сломаться. Придётся руками обновлять номер столбца с результатами. XLOOKUP принимает на вход два диапазона и спокойно переживает перемещение любого из них:

=VLOOKUP("needle";A:Z;2;0)
=XLOOKUP("needle";A:A;B:B)

2️⃣ Для VLOOKUP столбец с результатами должен располагаться справа от столбца для поиска. Передать третьим аргументом отрицательное число нельзя. XLOOKUP лишён этого ограничения и позволяет доставать результаты слева от столбца для поиска:

=XLOOKUP("needle";B:B;A:A)

3️⃣ При неудачном поиске VLOOKUP возвращает #N/A. Если вместо ошибки хочется выводить что-то другое (например, пустое значение), приходится дополнительно вызывать функцию IFNA. В XLOOKUP можно четвёртым аргументом передать значение, которое будет выводиться при неудачном поиске:

=IFNA(VLOOKUP("needle";A:Z;2;0);"not found")
=XLOOKUP("needle";A:A;B:B;"not found")

4️⃣ По умолчанию VLOOKUP ищет приблизительное совпадение. Для поиска точного соответствия надо передать FALSE или ноль четвёртым параметром. Часто про это забывают и долго разбираются, почему функция работает не так, как ожидалось. XLOOKUP по умолчанию ищет точное соответствие, помогая избежать ошибок.

5️⃣ Приблизительный поиск VLOOKUP умеет искать только ближайшее меньшее значение. При этом исходная таблица должна быть отсортирована. XLOOKUP в режиме приблизительного поиска позволяет искать как меньшее, так и большее значение. Таблицу сортировать необязательно.

6️⃣ Если подходящих значений в таблице больше одного, VLOOKUP ищет только первое совпадение. XLOOKUP умеет запускать поиск с любого конца и может находить как первое, так и последнее совпадение.

Единственный минус XLOOKUP: функция недоступна в Excel 2019 и более ранних версиях. Да и по-русски называется ПРОСМОТРХ, где Х — это «икс», а не «ха». К вопросу, почему я избегаю русскоязычные названия функций.

Ссылка на массив переменной длины

Пусть в столбце A лежит массив переменной длины (например, результат работы FILTER). В столбце B мы хотим написать формулу массива, например, удвоить все значения столбца A.

Можно применить формулу ко всему столбцу A:

• Excel: =2*A2:A1000

• Sheets: =ARRAYFORMULA(2*A2:A)

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

В Excel достаточно использовать решётку:

=2*A2#

В Google Sheets такого оператора нет, приходится выкручиваться:

=ARRAYFORMULA(2*OFFSET(A2;0;0;COUNTA(A2:A)))

• Функция COUNTA считает количество непустых значений в столбце.

• Функция OFFSET возвращает диапазон нужного размера, начиная с указанной ячейки.

Из первого пункта следует важное ограничение: формула работает только при отсутствии пустых значений в данных, иначе функция COUNTA неправильно посчитает высоту диапазона.

Проверка на уникальность

Пусть есть список однотипных объектов: товаров, заказов или сотрудников. У каждого элемента есть идентификатор. Как предотвратить ситуацию, когда при заполнении таблицы кто-нибудь добавит элемент дважды? Другими словами, как гарантировать уникальность идентификаторов?

В sql для этого используется PRIMARY KEY или UNIQUE, в электронных таблицах встроенных инструментов нет. Как вариант, можно реализовать подсветку дубликатов с помощью условного форматирования и функции COUNTIF:

Формат → Условное форматирование
Применить к диапазону: A2:A
Правила форматирования → Ваша формула =AND(LEN(A2);COUNTIF(A$2:A;"="&A2)>1)
Цвет фона: красный

Как работает формула:

• LEN(A2) проверяет, что ячейка заполнена;

• COUNTIF(A$2:A;"="&A2) считает количество ячеек, совпадающих с текущей. Если оно больше одного, срабатывает условное форматирование.

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

Пустое значение

В большинстве случаев результатом вычисления формулы в электронной таблице является какое-то значение. Но иногда необходимо просто оставить ячейку пустой. В Google Sheets для этого достаточно передать в функцию пустой аргумент:

=IF(A1;A1*100;) — если другая ячейка заполнена, то произвести вычисление, в противном случае оставить ячейку пустой.

=XLOOKUP("needle";A:A;B:B;) — если needle найден в столбце A, вывести соответствующие значение из столбца B, в противном случае оставить ячейку пустой.

Точка с запятой перед закрывающей скобкой обязательна, без неё первая формула вернёт FALSE, вторая — #N/A.

Занятно, что в Excel это не работает. Там в принципе нельзя написать формулу, которая вернёт пустое значение. Приходится возвращать пустой текст (""):

=ЕСЛИ(A1;A1*100;"")

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

Навигация по электронной таблице

Как быстро перейти в конец текущего столбца с данными?

Достаточно нажать Ctrl + ↓ (⌘ + ↓).

Ctrl + ↑ (⌘ + ↑) перемещает в начало текущего столбца.

Ctrl + → (⌘ + →) переносит в конец текущей строки, а Ctrl + ← (⌘ + ←) — в начало.

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

Забавно, что эти сочетания клавиш не описаны в официальной документации.

Подсветка формул

В сложных электронных таблицах легко запутаться, где данные, а где формулы, т. к. выглядят они одинаково. Можно временно включить (и так же выключить) отображение формул вместо значений с помощью сочетания клавиш Ctrl + ~

Есть и более изящный подход: выделять ячейки с формулами цветом с помощью условного форматирования и функции ISFORMULA:

Формат → Условное форматирование
Применить к диапазону: A:Z
Правила форматирования → Ваша формула =ISFORMULA(A1)
Цвет текста: темно-серый (2)

Для правильной работы адрес в формуле =ISFORMULA(A1) должен соответствовать левой верхней ячейке указанного диапазона (в примере A:Z).

Как результат, все формулы на листе будут выводиться серым шрифтом.

Замена формул значениями

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

Правка → Специальная вставка → Только значения

Или, что гораздо быстрее, воспользоваться последовательными сочетаниями клавиш:

• Ctrl + C / ⌘ + C (копировать ячейку)

• Shift + Ctrl + V / Shift + ⌘ + V (вставить как значение)

Работает как с одиночными ячейками, так и с целыми диапазонами.