Основное предназначение набора инструментов этой вкладки это анализ имеющейся книги с целью выявления узких мест в расчетах и избыточных данных внутри файла.
1. Profile Workbook
Это инструмент для анализа производительности на уровне всей рабочей книги Excel.
Выполнение профилирования иногда занимает много времени. Для его сокращения вынесен ряд настроек. После запуска будет сформирована отдельная книга Excel с результатами анализа.
Давайте разберемся когда Profile Workbook особенно полезен:
1. Общий обзор производительности: Profile Workbook дает комплексное представление о производительности всей книги, что идеально для начального этапа оптимизации или для регулярных проверок здоровья файла.
2. Выявление проблемных листов: Инструмент помогает определить, какие листы в книге потребляют больше всего ресурсов при расчетах. Это позволяет сфокусировать усилия по оптимизации на наиболее критичных частях файла.
3. Анализ волатильности: Profile Workbook показывает процент волатильности для каждого листа и всей книги, что помогает выявить области, требующие частого пересчета.
4. Оценка эффективности многопоточности: Инструмент предоставляет информацию об эффективности многопоточных вычислений, что критично для оптимизации на современных многоядерных процессорах.
5. Анализ структуры книги: Profile Workbook даёт информацию о количестве листов, формул, уникальных формул, что помогает оценить сложность и структуру книги.
6. Выявление проблем с памятью: Инструмент предоставляет данные о использовании памяти, что помогает выявить потенциальные проблемы с большими объемами данных или сложными вычислениями.
7. Оценка времени открытия файла: Profile Workbook показывает время, необходимое для открытия книги, что важно для оценки удобства использования файла.
8. Анализ связей между листами: Инструмент может помочь выявить сложные зависимости между листами, которые могут влиять на производительность.
9. Оценка влияния условного форматирования: Profile Workbook предоставляет информацию о количестве условных форматов, что может быть источником проблем производительности.
10. Анализ использования диапазона: Инструмент показывает процент неиспользуемых ячеек в диапазоне использования, что может указывать на неэффективное использование пространства листа.
11. Выявление проблем с внешними ссылками: Profile Workbook может помочь идентифицировать проблемы, связанные с внешними ссылками или связями.
12. Оценка влияния таблиц данных: Инструмент показывает количество таблиц данных, которые могут значительно влиять на производительность.
13. Анализ настроек вычислений: Profile Workbook предоставляет информацию о текущих настройках вычислений книги, что важно для понимания поведения файла при расчетах.
14. Планирование оптимизации: На основе полученных данных можно разработать комплексный план оптимизации всей книги, начиная с наиболее проблемных областей.
15. Сравнение версий файла: Profile Workbook можно использовать для сравнения производительности разных версий одной и той же книги, что помогает оценить эффект внесенных изменений.
В целом, Profile Workbook предоставляет всесторонний анализ производительности на уровне всей книги, что делает его незаменимым инструментом для начального этапа оптимизации и для общей оценки состояния сложных файлов Excel.
Пример использования.
Есть у меня одна книга Excel, которую сделал в 2015 году и теперь на ней проверяю различные инструменты анализа книг и формул. Давайте посмотрим, что выдаст анализ книги.
В верхней части отчета до 11 строки информация о настройках и окружении, которые могут помочь при анализе проблем. Например:
1) какая версия Excel используется (можно использовать для проверки информации о доступности функций и о возможных багах, которые решены в других версиях).
2) Выдан ли общий доступ к файлу, установлена ли защита различных элементов книги
3) Количество временных файлов в папке Temp системы Windows.
В моем случае в этой папке уже 161 файл, что может замедлять открытие книг. Для устранения достаточно почистить эту папку.
В средней части от 13 до 33 строки общая информация в разрезе листов (в строках) о количестве использованных ячеек, формул, в т.ч. уникальных, формул массива, условных форматирований, таблиц что-если. Для листов показано время пересчета, доля изменчивых функций на листе, процент отходов (ячейки в использованном диапазоне без данных), количество объектов (shapes), гиперссылок и разрывов страниц.
Например, в ячейке O22 значение 72% говорит о том, что на листе ПоказателиООУ значительная часть ячеек в диапазоне, который Excel считает использованным на этом листе, пуста, содержит только форматирование или проверку данных.
На указанном листе действительно большая часть ячеек пустая. В некоторых случаях значительное количество пустых ячеек в использованном диапазоне может замедлять расчеты. Решением будет удаление неиспользованных столбцов и строк, либо сброс последней использованной ячейки. Это легко сделать с помощью функции Сброс последней ячейки Excel в бесплатном дополнении ASAP Utilities.
В нижней части отчета общая информация о книге.
2. Profile Worksheet Formulas and Functions
Это мощный инструмент для детального анализа формул и функций на уровне отдельного листа Excel.
Перед запуском также открывается окно с настройками. Результатом выполнения является отчет на отдельном листе Excel в новой книге. При последовательном использовании нескольких инструментов анализа книги, отчеты помещаются в одной новой книге на отдельных листах.
Он особенно полезен в следующих случаях:
- Детальный анализ формул: Инструмент предоставляет подробную информацию о каждой уникальной формуле на листе, включая время вычисления, количество использований и флаги особенностей.
- Выявление проблемных формул: Помогает идентифицировать формулы, которые занимают наибольшее время при вычислениях, что позволяет сосредоточиться на оптимизации наиболее ресурсоемких вычислений.
- Анализ использования функций: Предоставляет информацию о всех функциях, используемых на листе, их количестве и времени выполнения, что помогает выявить неэффективное использование функций.
- Идентификация волатильных функций: Позволяет быстро определить все волатильные функции на листе, которые могут значительно влиять на производительность при пересчете.
- Выявление однопоточных функций: Помогает обнаружить функции, которые выполняются в одном потоке, потенциально снижая эффективность многопоточных вычислений.
- Анализ пользовательских функций (UDF): Предоставляет информацию об использовании пользовательских функций, которые часто могут быть источником проблем производительности.
- Оценка сложности формул: Позволяет оценить сложность формул на основе их структуры и времени выполнения, что может помочь в их упрощении.
- Выявление избыточных вычислений: Помогает идентифицировать повторяющиеся или избыточные вычисления, которые можно оптимизировать.
- Анализ использования массивов: Предоставляет информацию о формулах массива (CSE), которые могут быть ресурсоемкими.
- Оптимизация часто используемых формул: Позволяет сосредоточиться на оптимизации формул, которые используются наиболее часто.
- Выявление нестандартных формул: Помогает обнаружить необычные или потенциально проблемные формулы, которые могут требовать дополнительного внимания.
- Анализ зависимостей формул: Может помочь в понимании структуры зависимостей между формулами на листе.
- Оценка использования ресурсов: Предоставляет информацию о времени выполнения каждой формулы, что помогает в оценке использования вычислительных ресурсов.
- Планирование рефакторинга: На основе анализа можно планировать рефакторинг формул для повышения общей производительности листа.
- Обучение и стандартизация: Результаты анализа могут быть использованы для обучения команды лучшим практикам написания эффективных формул.
- Сравнение эффективности различных подходов: Позволяет сравнивать различные подходы к решению одной и той же задачи с точки зрения производительности.
В целом, Profile Worksheet Formulas and Functions предоставляет глубокий и детальный анализ формул и функций на уровне листа, что делает его незаменимым инструментом для точечной оптимизации и улучшения производительности сложных расчетов в Excel.
Пример использования.
Давайте посмотрим, что покажет отчет по формулам на отдельном листе.
В верхней части также выводится информация об окружении и настройках. В средней части информация об уникальных формулах, их количестве, доля времени пересчета на формулу от времени на лист. В ячейке B16 мы видим аббревиатуры B-V-S, что говорит о том, что формула является волатильной и при этом вычисляется в 1 поток.
Важно знать:
Если в формуле есть хотя бы 1 однопоточная функция, вся формула будет выполняться в 1 потоке, даже если остальные функции будут многопоточными. Также и зависимые от таких формул ячейки также будут вычисляться в 1 поток. В сложных моделях это может приводить к медленной работе из-за каскадного эффекта, когда большая часть вычислений осуществляется в 1 поток из-за однопоточной функции в ключевой формуле.
Примеры однопоточных функций: СМЕЩ(), ЯЧЕЙКА().
Что делать:
- выявлять однопоточные функции в ключевых формулах и по возможности заменять их на многопоточные аналоги;
- иногда можно реструктурировать вычисления, чтобы изолировать однопоточные операции и минимизировать их влияние на остальную часть модели.
3. Profile Worksheet Areas
Это инструмент для детального анализа отдельного листа Excel.
Будет полезен в следующих случаях:
- Анализ больших листов: Когда у вас есть большой лист с множеством формул, Profile Worksheet Areas позволяет разбить его на меньшие секции для анализа. Это помогает выявить конкретные области, которые могут быть узкими местами в производительности.
- Выявление проблемных областей: Инструмент помогает определить, какие конкретные области листа потребляют больше всего времени при расчетах. Это может быть полезнее, чем общий анализ листа или отдельных формул, так как позволяет сосредоточиться на оптимизации наиболее ресурсоемких секций.
- Оптимизация структуры листа: Понимая, какие области листа наиболее ресурсоемкие, вы можете принять решение о реорганизации структуры вашего листа для повышения эффективности.
- Сравнение различных частей листа: Вы можете сравнить производительность различных секций листа, что может быть полезно при поиске оптимальных подходов к организации данных и формул.
- Анализ влияния столбцов и строк: Profile Worksheet Areas позволяет анализировать производительность по столбцам и строкам, что может помочь в выявлении проблем, связанных с конкретными наборами данных или типами формул.
- Детальный анализ после общего профилирования: После использования Worksheet profiler для выявления проблемных листов, Profile Worksheet Areas можно использовать для более глубокого анализа этих листов.
- Фокусировка на конкретных областях: В отличие от Formula profiler, который анализирует отдельные формулы, Profile Worksheet Areas позволяет сосредоточиться на целых секциях листа, что может быть более эффективно для оптимизации взаимосвязанных вычислений.
- Анализ неформульных областей: Profile Worksheet Areas может помочь выявить проблемы производительности, связанные не только с формулами, но и с форматированием, условным форматированием и другими аспектами, которые могут влиять на производительность.
- Оптимизация динамических диапазонов: Если вы используете динамические диапазоны или таблицы, этот инструмент может помочь оценить их влияние на производительность разных частей листа.
- Планирование оптимизации: Результаты анализа могут помочь в планировании стратегии оптимизации, позволяя сосредоточиться на наиболее проблемных областях листа.
Пример использования.
Давайте посмотрим, что покажет отчет на моем чудо-юдо файле.
На что тут обратить внимание? Посмотрим строку 16. В области G65:BN73 всего 540 формул длиной до 1500 символов каждая. Что же ждет в этой зоне:
Мы нашли формулу кандидата на оптимизацию.
4.Map Worksheet Cross-references
Предназначен для анализа и визуализации связей между листами в рабочей книге Excel.
Он особенно полезен в следующих случаях:
1. Анализ структуры книги: Предоставляет четкую картину того, как листы в книге связаны между собой, что помогает понять общую структуру и поток данных.
2. Выявление зависимостей: Позволяет быстро идентифицировать, какие листы ссылаются на другие и наоборот, что критично для понимания влияния изменений в одном листе на другие.
3. Оптимизация порядка расчетов: Помогает оптимизировать последовательность расчетов между листами, что может значительно улучшить производительность, особенно в сложных моделях.
4. Обнаружение циклических ссылок: Инструмент может помочь выявить циклические зависимости между листами, которые могут быть источником проблем и ошибок.
5. Планирование изменений: При планировании изменений в структуре книги, этот инструмент помогает оценить потенциальное влияние на другие листы.
6. Документирование структуры: Создает визуальное представление структуры книги, что полезно для документации и обмена информацией с командой.
7. Оптимизация производительности: Помогает выявить листы с большим количеством внешних ссылок, которые могут быть узкими местами в производительности.
8. Анализ "прямых" и "обратных" ссылок: Показывает не только на какие листы ссылается данный лист, но и какие листы ссылаются на него.
9. Выявление изолированных листов: Помогает обнаружить листы, которые не имеют связей с другими, что может указывать на неиспользуемые или забытые данные.
10. Оптимизация формул: Зная структуру связей, можно оптимизировать формулы для уменьшения количества межлистовых ссылок.
11. Управление сложностью: В больших и сложных книгах этот инструмент помогает управлять и понимать общую структуру и сложность модели.
12. Планирование реструктуризации: При необходимости реорганизации книги, этот инструмент предоставляет ценную информацию для планирования изменений.
13. Анализ потока данных: Помогает понять, как данные перемещаются между листами, что важно для оптимизации и отладки.
14. Выявление избыточных ссылок: Может помочь обнаружить избыточные или ненужные ссылки между листами.
15. Оценка модульности: Позволяет оценить, насколько модульной является структура книги, что важно для поддерживаемости и масштабируемости.
16. Поддержка совместной работы: Помогает разным членам команды понять структуру книги и их зоны ответственности.
В целом, Map Worksheet Cross-references предоставляет ценную информацию о структуре и взаимосвязях в рабочей книге Excel, что делает его незаменимым инструментом для анализа, оптимизации и управления сложными Excel-моделями.
Пример использования.
Давайте теперь посмотрим, что покажет отчет по анализируемой книге.
Какие выводы можно сделать по этому отчету. Например, что формулы с листа расч.Спецодежда (ячейка A20) ссылаются на лист спр.Спецодежда 75600 раз. Явно формулы на этом листе следует рассмотреть детальнее:
Тут и формула массива, и множество вложенных если, и пользовательская VBA функция. В общем весь набор возможных проблем с производительностью.
Если сравнивать информативность полученного отчета с другими инструментами, мне больше нравится результат, выдаваемый PerfectXL Explore.
Это приложение работает отдельно от Excel и принимает на вход файл для анализа.
Что мы видим:
1) слева сверху блок с именами листов, при раскрытии которого можно увидеть информацию о количестве различных объектов на нем. При выборе объектов определенных типов можно увидеть их список и перейти в файл к этому объекту
2) слева снизу сводная информация о количестве объектов различных типов. При выборе объектов определенных типов можно увидеть их список и перейти в файл к этому объекту
3) Правый верхний блок отображает листы и стрелками связи.
Видно, например, что на листе спр.ТМЦ_экспл используется в качестве источника внешний файл. А лист Оглавление2 не имеет связей с другими листами.
4) Правый нижний блок позволяет управлять отображением в верхней части. Можно, например, отобразить какие листы используются для ввода, какие для вывода, а какие промежуточные. Также можно вывести количество связей.
Я тестировал более 15 различных инструментов для анализа книг и формул. Вывод у меня такой: идеального инструмента не существует. Для каждого пользователя выбор будет определен как вкусом, так и набором решаемых задач.
В целом FastExcel Profiler предлагает серьезный набор полезных функций. В моей специфике работы регулярно им пользуюсь и вижу ценность.
А как вы используете FastExcel Profiler? Поделитесь пожалуйста опытом работы с этим или другими приложениями/дополнениями для анализа книг и формул в комментариях.
В следующих статьях мы подробно рассмотрим другие компоненты FastExcel, предоставив конкретные примеры их применения в различных сценариях, охватывающих широкий спектр бизнес-задач. Для инструмента SpeedTools уделим особое внимание применению в сфере HR и C&B, демонстрируя универсальность и гибкость FastExcel. Следите за моими публикациями, чтобы узнать, как сделать вашу работу с Excel более эффективной и менее утомительной, независимо от специфики вашей деятельности.
