Sumifs (): ограничение sum_range и crit_range для оптимизации скорости вычислений при сортировке данных.

488
Acemanhattan

У меня есть отсортированный набор данных длиной около 50 000 строк с данными, которые выглядят следующим образом:

enter image description here

В нем у меня есть около 3 столбцов, похожих на общий столбец, показанный здесь, который выполняет довольно простой расчет sumifs (). Проблема состоит в том, что все три происходят одновременно, и все три смотрят на диапазоны суммы / критерия, которые имеют длину 50 000 ячеек, и в результате получается очень медленно вычисляемая рабочая книга.

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

Спасибо

0
Во сколько ячеек скопированы ваши текущие формулы? Что вы подразумеваете под "только когда-либо нуждающимся в диапазоне, расположенном выше и ниже заданной ячейки"? Зачем? Какая формула на самом деле рассчитывается? Почему вы привели пример со ссылкой на верхнюю строку только 14? И почему ссылки в этой формуле все относительные, а не абсолютные? Например, если эта формула скопирована в одну ячейку, что, как я предполагаю, должно быть, диапазоны, на которые делается ссылка, станут B3: B15 и т. Д. Я полагаю, что это известно и намеренно? XOR LX 8 лет назад 0
@XORLX Я пытаюсь подсчитать общее количество долларов, связанных с данным ID, рядом с первым наблюдением. Формула состоит из 3 столбцов и копируется в 50000 строк в каждом столбце. Фактические данные начинаются со строки 101, а относительная ссылка - это что-то вроде B51: B151. Причина в том, что, поскольку эти данные отсортированы по id # и поскольку ни один id # не может иметь более 20 или около того наблюдений, нам не нужно искать из B101: B50000, а только диапазон от id # (n-1, n + 1) (это то, что я имел в виду, имея в виду только непосредственный диапазон выше и ниже). Acemanhattan 8 лет назад 0
Но разве вы не ответили эффективно на свой собственный вопрос, то есть вы установили относительную ссылку так, чтобы она всегда имела форму B (n-50): B (n + 50)? XOR LX 8 лет назад 0
Знание того, что мне нужно сделать, не то же самое, что знать, как это сделать. Но я думаю, что вы знаете об этом. Acemanhattan 8 лет назад 0
Нет, правда. Что было не так, например, с вашей ссылкой на B51: B151? XOR LX 8 лет назад 0
О, я думал, что тебе было трудно. Да, это работает, но режим # наблюдений на идентификатор, вероятно, примерно 3, так что для большинства моих идентификаторов # это излишне. Если это так, что формула, как написано, не должна занимать слишком много времени для выполнения, тогда это не имеет большого значения (я не знаю, так ли это на самом деле), так как я действительно ищу это чтобы моя книга не затрачивала ни минуты на вычисления каждый раз, когда я вносил изменения в одну из динамических переменных. Acemanhattan 8 лет назад 0
И вы выполнили тесты, чтобы убедиться, что именно эти формулы являются проблемой, а не какие-либо другие в вашей книге? XOR LX 8 лет назад 0
Я только что провел быстрый тест. В новой пустой книге я ввел случайные данные в диапазон A1: C50000. Затем я ввел формулу = SUMIFS (B1: B101, A1: A101, A51, C1: C101, C51) в ячейку E51, т. Е. Одну, которая ссылается на диапазон из 101 ячейки, включающий пятьдесят строк до и после ячейки, содержащей формулу сам и единственная строка, содержащая саму формулу. Затем я скопировал эту формулу в строку 50000. Я также продублировал эти формулы 50000 в диапазоне F51: G50000, чтобы имитировать описанную вами установку. Отсюда всего 150000 формул. Полный расчет занял меньше секунды. XOR LX 8 лет назад 0
Я действительно новичок в Excel, поэтому я не уверен, каким будет хороший тест, однако я отметил, что удаление трех столбцов из моей книги имеет свойство принимать скорость вычисления до 1 секунды против 1 минуты, поэтому из этого Я предположил, что они, вероятно, были лучшим местом для начала оптимизации. Acemanhattan 8 лет назад 0
Хорошо. Если это так, мне нужно подумать о том, где настоящая проблема. Рабочая книга стала больше базой данных, чем должна быть (50 МБ), поэтому, возможно, пришло время убедить в том, что нам нужно выполнять большую часть тяжелой работы в SAS. Acemanhattan 8 лет назад 0
Давайте [продолжим это обсуждение в чате] (http://chat.stackexchange.com/rooms/30731/discussion-between-xor-lx-and-acemanhattan). XOR LX 8 лет назад 0

0 ответов на вопрос

Похожие вопросы