.png)
В настоящее время SUMIFS вычисляет как положено для 200 000+ строк. Есть одно исключение, которое я не могу понять, когда данные одинаковы для 10 строк, затем 11-я строка меняется, а затем строки с 12 по 21 содержат те же данные, что и первые 10 строк. SUMIFS выдает количество для всех 20 «подобных» строк вместо двух отдельных сумм для каждого набора из 10. Цель — разделить количества, не разбивая электронную таблицу.Пример листа Excel
решение1
Возможно, это не идеальное решение из-за размера файла, но любое решение в той или иной степени страдает от этого.
Используйте два вспомогательных столбца, один для ведения промежуточного итога сверху вниз, а другой для ведения промежуточного итога снизу вверх. Предполагая, что столбец I — это то, что вы определили для того, что составляет «группу», а K содержит рассматриваемые величины, я буду использовать X и Y в качестве вспомогательных столбцов. В X1 введите 0 (ноль), затем в X2, чтобы вести промежуточный итог для текущей группы:
=K2+X1*(I2=I1)
Это берет текущее значение и добавляет к нему нарастающий итог выше, но только если ID совпадает, в противном случае начать снова с текущего количества. Аналогично в Y2, но немного по-другому:
=(K3+Y3)*(I2=I3)
Это добавляет количество ниже к текущей сумме ниже, если идентификаторы совпадают, в противном случае начните снова с нуля. Скопируйте обе формулы вниз по столбцам X и Y. Y1 может иметь любую метку, которую вы хотите.
Теперь, магия этого в том, что количество для группы является суммой этих двух итогов, потому что счет идет в противоположных направлениях и имеет смещение строки, так что вы ничего не считаете дважды. Также их можно копировать вниз по мере добавления новых данных. Поместите простую формулу =SUM(X2:Y2) в любой столбец, в котором вы хотите хранить окончательный итог.
Недостатком этого подхода является то, что у вас здесь огромное дерево зависимостей, поэтому при изменении одного значения все пересчитывается, как комната, полная домино. Если это становится большой проблемой, вы можете скопировать и вставить значения во все, кроме верхней строки X и Y, внося множество изменений в данные, затем скопировать вниз и пойти прогуляться/выпить кофе/пива, пока он вычисляется. Затем снова вставить значения.
Я вообще-то не поклонник решений, требующих сортировки данных определенным образом, но, похоже, это единственный способ сделать ваши данные осмысленными, поскольку они очень иерархичны, поэтому я не слишком беспокоюсь о том, что в этом конкретном сценарии они будут отсортированы другим образом.