Сводная таблица в LibreOffice Calc — не отдельный «магический» инструмент, а надстройка над нормально подготовленными данными. Если даты лежат текстом, а суммы хранятся с пробелами, сводная покажет пустые ячейки и неверные итоги. Поэтому рабочий порядок такой: сначала привести типы, затем построить агрегаты формулами, и только потом собирать сводную для отчёта. Ниже — полный сценарий на примере выгрузки продаж из CSV.
Подготовка данных: типы, диапазоны и имена
При открытии CSV Calc показывает диалог импорта, и его настройки важнее, чем кажется. Разделитель столбцов, кодировка и формат чисел должны соответствовать файлу, иначе адреса и артикулы превратятся в даты, а ведущие нули исчезнут. Для колонки с кодами явно выберите тип «Текст», для сумм — «Число», для дат — «Дата». Флажок «Обнаружить специальные числа» помогает с процентами, но иногда портит артикулы.
Проверка занимает минуту. Функция ЕТЕКСТ покажет, что значение осталось строкой, ЕТЕЧИСЛО подтвердит число, а ЕПУСТО найдёт пропуски. Для преобразования используйте ЗНАЧЕН и ДАТАЗНАЧ, но не поверх исходных данных — лучше в соседней колонке, чтобы сохранить источник.
=ЕСЛИ(ЕТЕКСТ($E2);"текст";"число")
=ЕСЛИОШИБКА(ЗНАЧЕН(ПОДСТАВИТЬ($E2;" ";""));"")
=ЕСЛИ(ЕТЕКСТ($A2);ДАТАЗНАЧ($A2);$A2)Диапазон лучше не задавать «на глаз». Выделите данные и создайте именованный диапазон через «Лист → Именованные диапазоны и выражения → Определить». Имя вроде Продажи читается в формулах лучше, чем $A$2:$F$5000. Если строки добавляются регулярно, оставьте запас и не выделяйте целые столбцы: Calc будет пересчитывать миллион пустых ячеек. Для растущих таблиц удобен зарегистрированный диапазон базы данных: «Данные → Определить диапазон», тогда сводная и фильтры видят новые строки после расширения.
Мусор в текстовых полях ломает и поиск, и сводную. Двойные пробелы убирает СЖПРОБЕЛЫ, непечатаемые символы из выгрузок — ПЕЧСИМВ. Эти формулы применяйте к копии столбца, а исходник сохраните: иногда полезно увидеть, что именно пришло из системы.
=СЖПРОБЕЛЫ($B2)
=ПЕЧСИМВ($B2)
=ЕСЛИ(СЧЁТЕСЛИ($D$2:$D$5000;$D2)>1;"дубль";"")Условные агрегаты вместо ручных итогов
До сводной полезно получить контрольные цифры формулами — они не зависят от кэша и сразу показывают ошибку в данных. В русской локали аргументы разделяются точкой с запятой, в английской — запятой; при копировании файла между локалями это первая причина ошибки Err:509.
=СУММЕСЛИМН($E$2:$E$5000;$B$2:$B$5000;$I2;$A$2:$A$5000;">="&$J$1;$A$2:$A$5000;"<="&$K$1)
=СЧЁТЕСЛИМН($B$2:$B$5000;$I2;$D$2:$D$5000;$L2)
=СРЗНАЧЕСЛИМН($F$2:$F$5000;$B$2:$B$5000;$I2)
=ЕСЛИОШИБКА(ИНДЕКС(Цены;ПОИСКПОЗ($D2;Товары;0));"нет цены")Здесь I2 — регион, J1 и K1 — начало и конец периода. Последняя формула — связка ИНДЕКС и ПОИСКПОЗ: она ищет товар по названию и возвращает цену из параллельного столбца. В отличие от простого ВПР, столбец поиска может находиться справа или слева, а вставка нового столбца не ломает ссылку. ПРОМЕЖУТОЧНЫЕ.ИТОГИ с кодом 109 считает только видимые строки после фильтра — удобно для быстрой проверки выборки.
Критерии с датами собираются конкатенацией: ">="&ДАТА(2026;1;1). Если написать дату строкой, результат зависит от локали и может молча дать ноль. Для поиска по части текста используйте "*север*", для пустых ячеек — "", для непустых — "<>".
Сводная таблица: сборка и настройка
Выделите диапазон, вызовите «Данные → Сводная таблица → Вставить или изменить» и выберите источник. В макете перетащите «Регион» в строки, «Дату» в столбцы, «Количество» в данные, «Менеджера» в фильтр. Calc по умолчанию суммирует числа; двойной щелчок по полю данных переключает сумму, количество, среднее, минимум и максимум.
Даты в области столбцов сначала выглядят как отдельные дни. Щёлкните по полю правой кнопкой и выберите «Группировать»: месяцы, кварталы, годы. Если пункт недоступен, значит, колонка содержит текст — вернитесь к подготовке данных. Пустые строки и элементы без данных скрываются в «Дополнительных параметрах»; для отчёта иногда полезно включить их, чтобы видеть провалы продаж.
Сводная не обновляется сама при изменении исходных ячеек: после правок нажмите «Данные → Обновить диапазон» или кнопку обновления на панели. При повторном импорте CSV в тот же лист кэш остаётся старым — это самая частая причина «цифры не те». Если исходник лежит в другом файле, держите его открытым или используйте зарегистрированный диапазон.
Перед публикацией отчёта полезно свести три числа: итог сводной, сумму по исходному столбцу и результат СЧЁТЗ. Они должны совпадать до копейки. Если сводная считает количество, а не сумму, проверьте тип поля данных — иногда Calc автоматически выбирает «Количество» для текстового столбца.
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;$E$2:$E$5000)
=СУММ($E$2:$E$5000)
=СЧЁТЗ($D$2:$D$5000)Оформление и проверка результата
Отчёт читают люди, поэтому настройте формат чисел: разделитель разрядов, две цифры после запятой, процент для долей. Условное форматирование («Формат → Условное форматирование») подсветит отрицательные значения и превышение плана. Закрепите строку заголовков через «Вид → Закрепить строки и столбцы», иначе при прокрутке теряется контекст.
Сверка обязательна. Сравните общий итог сводной с СУММ по исходному столбцу и с количеством строк. Расхождение обычно означает дубли, текст вместо чисел или строки за пределами диапазона. Дубли ищутся формулой =СЧЁТЕСЛИ($D$2:$D$5000;$D2)>1 и фильтром по истине. Уникальные записи удобно извлекать через «Данные → Дополнительные фильтры» с копированием результата и включённой опцией «Без дубликатов».
Диагностика типичных проблем
- Суммы равны нулю — числа записаны текстом; проверьте
ЕТЕКСТ, уберите пробелы и неразрывные символы. - Даты не группируются — колонка текстовая или содержит время в неожиданном формате.
- Сводная пустая после фильтра — в кэше нет строк, обновите таблицу и проверьте диапазон источника.
- Файл тормозит — уменьшите диапазон, удалите лишнее форматирование и условные правила на весь лист.
- Кракозябры в CSV — при импорте выберите UTF-8; для старых выгрузок подойдёт Windows-1251.
- Формулы не пересчитались после открытия XLSX — «Сервис → Параметры → Calc → Формула» и режим пересчёта при загрузке.
Для пакетной проверки вне графической среды удобен headless-режим: он пересчитывает документ и сохраняет CSV, а ошибки видны в коде возврата. Так можно автоматизировать ежедневную выгрузку отчёта без ручного открытия файла. Кодировку исходника проверяйте командой file -i, иначе UTF-8 и Windows-1251 будут путаться при каждом импорте.
libreoffice --headless --convert-to csv --outdir ./out report.xlsx
file -i report.xlsx
python3 -c "import csv; print(sum(float(r['qty']) for r in csv.DictReader(open('report.csv'))))"Итог
Calc даёт полный цикл работы с данными: импорт с контролем типов, именованные диапазоны, условные агрегаты и сводная таблица. Секрет не в редких функциях, а в дисциплине: числа остаются числами, даты — датами, диапазоны имеют имена, а итог всегда сверяется с источником. Начните с одной выгрузки, доведите её до чистой таблицы, и сводные перестанут быть источником сюрпризов.