Производительность Excel: поиск против Getpivotdata
Я создаю электронную таблицу Excel 2007, которая содержит большую таблицу с исходными данными (около 500000 строк и 10 столбцов). Мне нужно извлечь данные из этой большой таблицы для моего анализа. Для извлечения и агрегирования данных я обычно использую функции sumif, vlookup/hlookup и index+match.
Недавно я узнал о существовании функции getpivotdata, которая позволяет извлекать данные из сводной таблицы. Чтобы использовать его, мне сначала нужно преобразовать мою большую исходную таблицу в сводную таблицу, а после этого я могу извлечь данные с помощью функции getpivotdata.
Будете ли вы ожидать улучшения производительности, если я воспользуюсь getpivotdata для извлечения и агрегирования данных? Я ожидаю, что в базовом объекте Pivot агрегированные значения рассчитываются заранее и, следовательно, производительность будет лучше.
Если производительность будет лучше, есть ли причины не следовать этому подходу? Чтобы было ясно, нет необходимости обновлять сводную таблицу, поскольку она содержит исходные данные (которые находятся в начале цепочки вычислений).
4 ответа
Я провел несколько тестов производительности на двухъядерном настольном ПК с оперативной памятью 2,33 ГГц и 2 ГБ в Excel 2007
Поиск был выполнен на столе с 241 000 записей. Результаты (самый быстрый первый и самый медленный последний):
С помощью функции сопоставления индекса в отсортированном списке число поисков в секунду составило: 180 000!! (на основе 1 440 000 поисков за 8 секунд). Дополнительную информацию о том, как реализовать отсортированные поиски в Excel, можно найти здесь и прокрутить вниз до раздела INDEX-MATCH в одной формуле, отсортированные данные.
С функцией getpivotdata число поисков в секунду было: 6000 (на основе 250 000 поисков за 40 секунд)
С помощью функции getpivotdata, использующей очень гибкий синтаксис строки с одним аргументом (см. Здесь), число поисков в секунду составило: 2000 (на основе 250 000 поисков за 145 секунд)
С функцией сопоставления индексов в несортированном списке число поисков в секунду составило: 500 (на основе 20 000 поисков за 35 секунд)
Результаты не меняются, когда функция поиска ссылается на таблицу данных вместо именованного диапазона.
Итак, чтобы ответить на вопрос. Поиск по getpivotdata примерно в 10 раз быстрее, чем при обычном поиске по индексу, но лучшее улучшение производительности достигается за счет сортировки исходных данных. Сортировка исходных данных может сделать ваш поиск в 400 раз быстрее.
Поиск в VBA (с использованием словаря) - самый быстрый способ. Смотрите это: https://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup
У меня такая же проблема ежедневно. Большое количество строк в нескольких таблицах данных в Excel.
В настоящее время единственное решение, позволяющее использовать очень большие таблицы, - это экспортировать их на сервер базы данных и выполнять / писать запросы SQL для выполнения Sumif,Vlookups и агрегирования.
Вы можете использовать Excel для создания запросов SQL
В течение многих лет я экспортировал листы / таблицы в "MySQL" и "MS SQL Server Express", а затем подключался к ним с помощью Excel и писал SQL-запросы.
Сервер выполняет обработку быстрее, чем Excel, и если база данных находится на другом сервере, производительность возрастает, поскольку он не использует ресурсы вашего ПК для выполнения вычислений.
Есть и другие преимущества этого решения.
Как автоматизация ETL и разделение строки подключения, а не "БОЛЬШАЯ" электронная таблица.
Использование GetPivotData дает вам доступ только к тому, что видно в отчете сводной таблицы. Если вы являетесь единственным пользователем этой таблицы, это может быть подходящим для вас подходом.
Если вы можете спроектировать Pivot так, чтобы он выполнял большую часть ваших агрегатов, то использование GetPivotData будет быстрее.
Я не тестировал производительность GetPivotData, но я ожидаю, что она будет медленнее, чем поиск / совпадение двоичного поиска в отсортированных данных.