Курс. Часть 8

Сравнение и анализ файлов XLSX

Рассмотрим, как работает третий модуль плагина «Слайдер данные».
После старта плагина для выбора модуля 3 нажмите кнопку «Анализ XLSX файлов» в панели слева.

Кнопка раскроется в меню для выбора: «Сравнение таблиц» и «Анализ источников».
Рассмотрим ниже каждый компонент модуля.

Компонент «Сравнение Таблиц» представляет собой эффективное решение для выявления расхождений между парой файлов Excel. Он способен в автоматическом режиме обнаруживать модификации в содержимом, формулах, организации данных и оформлении ячеек. Данный инструмент обеспечивает подробную наглядную аналитику с использованием цветовых маркеров для обозначения правок, предоставляет статистические данные о различиях и поддерживает функцию выгрузки полученных результатов. Решение особенно востребовано при управлении разными версиями документации, проверке достоверности информации и мониторинге корректировок в объёмных массивах данных.
Рассмотрим ниже основные элементы интерфейса

Компонент представляет собой окно с несколькими основными областями:
Левая панель (настройки):

Поля выбора источников данных для сравнения, кнопки для выбора файла с диска

Блок опций, которые позволяют более точно настроить сравнение двух файлов

Типы изменений в легенде:

• Изменено значение - различия в содержимом ячеек
• Изменена формула - различия в формулах
• Вставлена строка - добавление новых строк
• Удалена строка - удаление существующих строк
• Формула → Значение - преобразование формулы в значение
• Значение → Формула - преобразование значения в формулу
• Изменён тип - изменение типа данных ячейки
• Изменён формат - изменение форматирования
• Вставлен столбец - добавление новых столбцов
• Удален столбец - удаление существующих столбцов

Правая панель (результаты):
Область статуса сравнения:

Область детального анализа:

• Таблица с подробным списком всех найденных изменений
• Колонки: Тип, Ячейка, compare_base, compare_target
• Фильтрация по типам изменений
• Цветовое кодирование строк по типам изменений

1.2 Пример работы с функционалом
Для примера работы сравнения двух XLSX файлов возьмем данные из папки к курсу «Сценарий 3 - Сравнение и анализ данных в XLSX».

Сценарий тестирования
·       Подготовлен файл «compare_1.xlsx» с 3 вкладками – «Строки», «Столбцы», «Ячейки»
·       Каждая вкладка содержит таблицу с данными (Данные для вкладок)
·       В копии исходного файла будут внесены изменения для сравнения

Вкладка 1: «Строки»

Вкладка 2: «Столбцы»

Вкладка 3: «Ячейки»

Для сравнения изменений скопируем файл с данными в новый файл – «compare_2.xlsx».
В котором произведем следующие изменения:

Последовательность изменений:
Для вкладки "Строки":

1. Удалим строку ID=3 (Клавиатура)
2. Добавим новую строку: ID=9 | Роутер | 3500 | 10 | В наличии
3. Переместим строку ID=5 (Наушники) на позицию ID=2 - вырежем и вставим. Строка 5 останется пустой

Для вкладки "Столбцы":

1. Удалим столбец "Скидка"
2. Добавим новый столбец после "Итого": "Примечание"
3. Переместим столбец "Поставщик" перед столбец "Цена" - вырежем и вставим

Для вкладки "Ячейки":

1. Изменим C2: 25000 → 22000
2. Изменим F2: Да → Нет
3. Изменим E6: Козлова → Сидоров
4. Очистим F3: Нет → (пусто)
5. Вырежем F7 «Да» и переместим в E7 (колонка менеджер)

·       Выберем раздел для сравнения изменения файлов («Анализ XLSX файлов» -> «Сравнение таблиц»)
·       Выберем в качестве «Источник 1» файл «compare_1.xlsx», «Источник 2» - «compare_2.xlsx» и все 3 вкладки в них: «Строки», «Столбцы», «Ячейки»
·       По очереди будем запускать сравнение для каждой из вкладок – нажать кнопку «Запуск»
·       Рассмотрим результаты ниже

Сравнение вкладок «Строки» - видим, что модуль отобразил изменения:
·       Удаление строки ID=3 (Клавиатура)
·       Удаление строки ID=5 (Наушники) с ее места и вставка на позицию 3
·       Вставка строки ID=9 (Роутер)

Сравнение вкладок «Столбцы» - видим, что модуль отобразил изменения:

• Удален столбец E – «Скидка»
• Вставлен столбец D (перемещен столбец «Поставщик»)
• Изменен столбец после «Итого» (по факту убрали «Поставщик» и вставили «Примечание»)

Сравнение вкладок «Ячейки» - видим, что модуль отобразил изменения:

• C2 изменена 25000 → 22000
• F2 изменена Да → Нет
• Изменим E6: Козлова → Сидоров
• Очистим F3: Нет → (пусто)
• Вырежем F7 «Да» и переместим в E7 (колонка менеджер)

Видим, что по итогам модуль сравнения отработал верно.

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

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

Затем, чтобы найти информацию, вводим необходимый текст. Например, введем «ноябрь», чтобы найти строку с этим текстом. После ввода слова и нажатия Enter
o   Курсор позиционируется на нужную ячейку в области данных
o   Также в области сравнения выделяется строка с найденным текстом
o   В панели поиска отображается количество найденных результатов. С помощью служебных кнопок можно перемещаться по найденным результатам или очистить область поиска

·       Отчет - создание подробного отчета о найденных различиях
Для получения отчета (формат CSV) необходимо нажать кнопку «Отчет».
После этого появится окно сохранения файла. Файл на диске представляет собой отчет, состоящий из нескольких разделов.

·       Управление данными
o   Кнопка «Очистить импорт» отвечает за удаление загруженных данных и сброс настроек
·       Настройки отображения
o   Чекбокс «Включить синхронный скроллинг» - синхронная прокрутка сравниваемых таблиц в верхней области. Служит для помощи пользователю видеть сравниваемые данные одновременно.

Компонент "Анализ источников" предназначен для глубокого анализа Excel файлов с целью выявления потенциальных проблем в формулах, ошибок, волатильных функций и других особенностей структуры данных. Инструмент обеспечивает комплексную диагностику файла с детальной статистикой по листам и категоризацией найденных элементов.

Кнопка загрузки - открывает диалог выбора файла

Кнопка удаления - очищает выбранный файл

Кнопки управления:
·       Запустить анализ - начинает процесс анализа загруженного файла

Блок «Параметры анализа» - Набор флажков для настройки типов анализа

• Количество формул - подсчет всех формул в файле
• Волатильные функции - поиск функций, которые пересчитываются при каждом обновлении
• Ошибки (#DIV/0!, #N/A ...) - выявление ячеек с ошибками
• Длинные формулы - поиск сложных формул
• Глубокий IF - анализ вложенных условных конструкций (3 и более уровней)
• Несогласованные формулы (ряд/столбец) - поиск нарушений в логике формул
• Числовые константы в формулах - выявление "жестко прошитых" значений
• Агрегаты по листам - анализ межлистовых ссылок
• Топ длинных формул - список самых сложных формул

Элементы управления параметрами:

• Галочка в квадрате - выбрать все параметры
• Пустой квадрат - снять выбор со всех параметров

Правая панель результатов
Область инструкций:

• "Выберите файл и запустите анализ" - подсказка для пользователя
• Статус готовности – например. «Готово»
• Полоса, дублирующая статус

• Листов - количество листов в файле
• Ячеек - общее количество ячеек с данными
• Формулы - количество обычных формул
• Волатильные - количество волатильных функций
• Ошибки - количество ячеек с ошибками
• Константы - количество числовых констант в формулах
• Длинные формулы - количество сложных формул

Таблица "Детали по листам": Показывает детальную разбивку по каждому листу:

•  Раздел "Длинные формулы" - Отображение самых сложных формул файла

рядом с полем выбора файла

• В открывшемся диалоге проводника выберите нужный Excel файл. Используем файл «analysis_sampleTest-1.xlsx».

Шаг 3. Запуск анализа

1. Нажмите кнопку «Запустить анализ».
2. Дождитесь завершения процесса обработки файла
3. Результаты отобразятся в правой панели

Шаг 4. Анализ результатов
Сводная статистика: Изучите цветные карточки в блоке "Сводка" для получения общего представления о файле:

• Синие карточки показывают структурную информацию
• Зеленые - объем данных
• Красные и розовые - проблемные области (ошибки, волатильные функции)
• Желтые и оранжевые - особенности формул

Детальный анализ по листам: Таблица "Детали по листам" позволяет:

• Сравнить показатели разных листов
• Выявить листы с наибольшим количеством проблем
• Понять распределение формул по структуре файла

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

• Ошибки в формулах (#DIV/0!, #N/A, #REF! и др.) - требуют немедленного исправления
• Несогласованные формулы - нарушения логики в рядах или столбцах
• Волатильные функции (NOW(), TODAY(), RAND() и др.) - замедляют пересчет файла
• Длинные формулы - сложные для понимания и редактирования
• Числовые константы в формулах - затрудняют обновление данных
• Глубокие IF конструкции - снижают читаемость кода
• Агрегаты по листам - создают зависимости между листами