производительность Excel: поиск против Getpivotdata

Question

Я создаю электронную таблицу Excel 2007, которая содержит большую таблицу с исходными данными (около 500 000 строк и 10 столбцов). Мне нужно извлечь данные из этой большой таблицы для моего анализа. Для извлечения и агрегации данных я обычно использую функции sumif, vlookup/hlookup и index+match.

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

Ожидаете ли вы повышения производительности, если я вместо этого буду использовать getpivotdata для извлечения и агрегирования данных? Я ожидаю, что в базовом объекте Pivot агрегированные значения рассчитываются заранее и, следовательно, производительность будет лучше.

Если производительность будет лучше, есть ли причины не следовать этому подходу? Чтобы было ясно, нет необходимости обновлять сводную таблицу, поскольку она содержит исходные данные (которые расположены в начале цепочки вычислений).

Answer 1

Я провел несколько тестов производительности на двухъядерном настольном ПК с оперативной памятью 2,33 ГГц и 2 ГБ в Excel 2007.

Поиск был выполнен на столе с 241 000 записей. Результаты (самый быстрый первый и самый медленный последний):

1. С помощью функции сопоставления индекса в отсортированном списке число поисков в секунду составило: 180 000 !! (на основе 1 440 000 поисков за 8 секунд). Дополнительную информацию о том, как реализовать сортированные поиски в Excel, можно найти здесь и прокрутить вниз до раздела INDEX-MATCH в одной формуле, Сортированные данные.

2. С функцией getpivotdata число поисков в секунду было: 6000 (на основе 250 000 поисков за 40 секунд)

3. С помощью функции getpivotdata, использующей очень гибкий синтаксис строки с одним аргументом (см. Здесь), количество просмотров в секунду составило: 2000 (на основе 250 000 поисков за 145 секунд)

4. С функцией сопоставления индексов в несортированном списке число поисков в секунду составило: 500 (на основе 20 000 поисков за 35 секунд)

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

Итак, чтобы ответить на вопрос. Поиск по getpivotdata примерно в 10 раз быстрее, чем при обычном поиске по индексу, но лучшее улучшение производительности достигается за счет сортировки исходных данных. Сортировка исходных данных может сделать ваш поиск в 400 раз быстрее.

Answer 2

Поиск в VBA (с использованием словаря) - самый быстрый способ. Смотрите это: https://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup

Answer 3

У меня та же проблема на ежедневной основе. Большое количество строк в нескольких таблицах данных в Excel.

В настоящее время единственное решение, которое позволяет использовать очень большие таблицы, - это экспортировать их на сервер базы данных и выполнять / писать запросы SQL для выполнения Sumif, Vlookups и агрегации.

Вы можете использовать Excel для создания запросов SQL

В течение многих лет я экспортировал листы / таблицы в « MySQL » и « MS SQL Server Express », а затем подключался к ним с помощью Excel и писал SQL-запросы.

Сервер выполняет обработку быстрее, чем Excel, и если база данных находится на другом сервере, производительность возрастает, поскольку он не использует ресурсы вашего ПК для выполнения вычислений.

Есть и другие преимущества этого решения.

Как автоматизация ETL и разделение строки подключения, а не "БОЛЬШАЯ" электронная таблица.

Answer 4

Использование GetPivotData дает вам доступ только к тому, что видно в отчете сводной таблицы. Если вы являетесь единственным пользователем этой таблицы, это может быть подходящим для вас подходом.

Если вы можете спроектировать Pivot так, чтобы он выполнял большую часть ваших агрегатов, то использование GetPivotData будет быстрее.

Я не тестировал производительность GetPivotData, но я ожидаю, что она будет медленнее, чем поиск / совпадение двоичного поиска в отсортированных данных.