Альтернативы использованию нескольких формул СУММЕСЛИ, не требующих от пользователя применения фильтра.

Предположим, в вашей книге: Лист1 имеет таблицу с 20 столбцами и примерно 1000 строк. Каждая строка относится к одной из 50 категорий. Разработчик не может вносить видимые изменения в Sheet1.

На Листе 2 есть ячейка, в которую пользователь может ввести идентификатор категории, и отображается результат СУММЕСЛИ в каждом из столбцов Листа 1.

Насколько я понимаю, Excel будет перебирать все 1000 столбцов для каждого SUMIF, выполняя один и тот же поиск 20 раз. Есть ли альтернатива этому?


excel sumifs vba
person WoodenKitty    schedule 12.04.2013    source источник
comment
Я думаю, вы просите стать эффективным (более эффективная идея, чем SUMIF). Если да, то нет ничего лучше. Единственное, что вы могли бы рассмотреть, это то, как вы помещаете результат SUMIF в Sheet2. На данный момент у вас есть (я думаю) формула листа, например =SUMIF(...). Если по какой-либо причине вы не хотите, чтобы эта формула существовала, вы можете применять ее каждый раз, когда вам нужны новые результаты с кодом vba, используя WorksheetFunction.SumIf(...)   -  person Kazimierz Jawor    schedule 12.04.2013
comment
если это все в excel, на самом деле нет более эффективного способа сделать это. Если бы вы могли загрузить свои данные в базу данных, а затем запросить базу данных и сохранить результаты в набор записей, то я на 99% уверен, что это было бы более эффективно.   -  person    schedule 12.04.2013
comment
еще одна идея - сводная таблица...   -  person Kazimierz Jawor    schedule 12.04.2013
comment
Возможно, стоит отметить, что на самом деле вы можете подключиться к вашему рабочий лист с помощью ADO и запустите на нем SQL.   -  person wakjah    schedule 12.04.2013
comment
Вы можете запускать пользовательскую подпрограмму VBA всякий раз, когда обнаруживаете, что идентификатор выбранной категории изменился (событие Worksheet_Change). Загрузите диапазон данных в массив и продолжайте подсчитывать итоги для каждого столбца, проходя по массиву в цикле. Затем обновите общие значения на листе 2. Требуется только один цикл через массив.   -  person Fink    schedule 12.04.2013

Ответы (1)


arrow_upward
0
arrow_downward

Вот на чем я остановился. Это определенно не удовлетворило бы потребности каждого.

Ситуация:
 – я не хочу запускать SUMIF 20 раз для 10 000 строк
 – я могу с уверенностью предположить, что для каждой категории никогда не будет более 200 строк (т. е. группы строки, которые соответствуют моему критерию суммирования).
. Мне также полезно иметь таблицу, содержащую только строки, соответствующие этим критериям.

Решение.
 Я создаю таблицу с 200 строками формул, в которой будут отображаться строки, соответствующие моим критериям.

Крайний левый столбец будет иметь номер следующей совпадающей строки или будет пустым и не будет выполнять поиск, если их больше нет.

Первая строка левого столбца:

IFERROR(MATCH(CategoryID,'DataTable'!B:B,0),"")

Последующие строки левого столбца (A2 — ячейка выше):

IF(A2="","",MATCH(CategoryID,OFFSET('DataTable'!B:B,A2,0,RowsInTable-A2,1),0)+A2))

Все ячейки справа от этого являются основными формулами ИНДЕКС, которые используют номер строки из самого левого столбца.

Для подсчета количества найденных результатов используем:

=COUNTA(A2:A202)-COUNTBLANK(A2:A202)

Затем мы используем этот счетчик и таблицу для выполнения хороших быстрых сумм:

SUM(OFFSET(C:C,0,0,RowCount,1))
person WoodenKitty    schedule 15.04.2013