Как заменить формулу Excel на массив другой формулой, чтобы избежать длительного времени вычислений?

Question

ПРИМЕР

используя формулы массива в диапазоне (E11:E16) В соответствии с приведенным выше примером:

Для подсчета уникальных клиентов в диапазоне (E2:E7), которые соответствуют только следующим критериям:

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

2- Приобретено менее 5 единиц после суммы. если есть постоянные клиенты

3- Их коды областей соответствуют соответствующему коду в диапазоне (D11:D16)

Я использую следующую формулу массива, чтобы быть в E11:

=SUM(IF(FREQUENCY(IF($G$2:$G$7=D11,IF($I$2:$I$7="Product1",IF($J$2:$J$7<5,IF($E$2:$E$7<>"",MATCH($E$2:$E$7,$E$2:$E$7,0))))),ROW($E$2:$E$7)-ROW(G2)+1),1))

Эта формула работает отлично, в то же время при использовании ее в очень большой базе данных, содержащей тонны строк и столбцов, Excel занимает около 3 минут, чтобы вычислить только одну ячейку, которая ужасно продолжать в том же духе.

есть ли способ преобразовать эту формулу массива в обычную ... любая помощь будет оценена по максимуму ... заранее спасибо

Answer 1

Я бы использовал Power Query для этого. Power Query - это бесплатная надстройка от Microsoft для Excel 2010 и 2013, которая встроена в Excel 2016 на ленте данных в разделе Get & Transform.

Я не уверен в вашем определении "тонн", но Power Query довольно удобно масштабируется до 10 миллионов входных строк. Если ваш источник данных на самом деле является базой данных (например, SQL Server, Oracle и т.д.), Power Query переносит большую часть работы на базу данных.

Для вашей задачи я бы начал с создания запроса с помощью кнопки « Из таблицы» для чтения из 1-й таблицы, затем выберите « Закрыть» и «Загрузить / закрыть и загрузить в», затем выберите « Только создать соединение». Если ваши исходные данные на самом деле берутся из реальной базы данных или другого файла, более эффективно будет вместо этого указывать Power Query непосредственно на этот источник.

Затем я бы начал фактический запрос вывода из списка кодов регионов в диапазоне D11:D16, используя кнопку « Из таблицы» .

Я бы добавил шаг слияния, чтобы присоединиться к первому запросу по коду города, а затем развернуть все его столбцы.

Затем я бы добавил условный столбец с именем Product 1, указав: если (столбец) Product равен (значение) Product 1, то (столбец) Product

Затем я хотел бы добавить группу за шагом, группируя по кодам и области клиента, и агрегирование суммы продаж и Max продукта 1.

Затем я бы отфильтровал столбец Product 1 только по строкам со значением "Product 1", а затем отфильтровал столбец Sales до значения меньше 5.

Наконец -то я бы другая группа стадии группировки на междугородном коде и агрегирование графа по умолчанию.

По умолчанию Power Query доставит это в новую таблицу на новой книге - вероятно, лучше оставить ее там, чтобы избежать столкновений с другими объектами.

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