Вопрос или проблема
Есть ли более быстрый способ рассчитать ячейки, выделенные зеленым цветом? Из-за политики компании формат отчета должен остаться таким, как есть, и не может быть изменен. Это может стать довольно трудоемкой задачей, если, скажем, будет до компании Z.
Столбец B всегда либо План №1, либо План №2. Столбец D всегда Комиссия.
Без сводной таблицы. Я использовал функцию SUBTOTAL, просто хочу устранить ручной процесс.
Возможно, на это нет ответа, но я очень ценю любую помощь.
Хорошо, вот 3 формулы, которые выполнят то, что вам нужно, используя функцию SUBTOTAL с функцией не учитывать любые другие SUBTOTAL, которые она встречает, и возможность использовать INDEX с двоеточиями для создания динамических диапазонов ячеек.
Итак, самая простая — это Общая сумма в ячейке E23:
=SUBTOTAL(9,E$2:E22)
Это просто SUBTOTAL всей колонны выше нее.
Следующей по простоте является Сумма по продуктам в E3, E14 и E17: (Формула вводится как в E3; после вставки можно просто скопировать/вставить в другие. Применив фильтр к вашей книге, вы можете отфильтровать столбец B на “Не пусто” и “не ‘Subtotal:'” для выполнения этого массово)
=SUBTOTAL(9,E4:INDEX(E4:E109,MATCH(1,--(""&D4:D105=""),0)-1))
Это смотрит вниз по столбцу D, чтобы найти следующую пустую ячейку, затем SUBTOTAL все ячейки в столбце E между этой строкой и текущей строкой, исключая текущую.
(Примечание: это вызовет ошибку, если у вас более 100 клиентов для конкретного продукта. Вы можете исправить это, изменив ""&D4:D105="" на ""&D4:D$1048576="", но это увеличит время расчетов…)
Наконец, у вас есть Суммы по компаниям в ячейках E11 и E21: (Формула вводится как в E11. Как и в предыдущем примере, вы можете просто отфильтровать столбец B на “Subtotal”, чтобы упростить это)
=SUBTOTAL(9, E10:INDEX(A:A,MAX(ROW(A$1:A11)*--(LEN(A$1:A11)>0))))
Это смотрит вверх по столбцу A, чтобы найти предыдущую ненулевую строку, затем SUBTOTAL все от этой строки до строки перед текущей.
Если необходимо, вы, вероятно, могли бы собрать скрипт VBA, который добавляет эти формулы в соответствующие ячейки (обратите внимание, что работа с ссылками R1C1 вместо A1 упростит это, хотя в этом случае лучше остаться с ссылками A1 и рассчитывать каждую область по отдельности…), затем сохранить его в вашем Personal.XLSM и добавить кнопку на вашу ленту или панель быстрого доступа, которая будет применять его к текущему активному листу.
Откройте отчет, нажмите кнопку, готово.
Эта формула находится в E3 и требует копирования/вставки в другие строки плана
Найдите следующую непустую ячейку в столбце B и измените диапазон для суммирования.
B$2000 — это фиктивная ячейка за пределами последних данных в столбце B.
=LET(next,MATCH(1,--NOT(ISBLANK(B4:B$2000)),),
SUM(E4:OFFSET(E4,next-2,0)))
Ответ или решение
Для упрощения процесса подсчета ячеек, выделенных зеленым цветом, в таблице Excel, где столбец B всегда содержит значения "No.1 Plan" или "No.2 Plan", а столбец D – комиссию, можно использовать несколько эффективных формул, которые минимизируют ручной труд.
Подходы к автоматизации подсчетов:
-
Использование функции SUBTOTAL: Эта функция может быть использована для подсчета сумм в определенных диапазонах. Она игнорирует другие функции SUBTOTAL, что позволяет создавать динамические расчеты во время фильтрации данных.
-
Формула для общей суммы: Для вычисления общей суммы в ячейке E23 можно использовать простую формулу:
=SUBTOTAL(9,E$2:E22)Данная формула суммирует значения в указанном диапазоне, обеспечивая актуальность данных при изменении.
-
Продуктовые подытоги: Для подытогов по продуктам в ячейках E3, E14 и E17 можно использовать следующую формулу:
=SUBTOTAL(9,E4:INDEX(E4:E109,MATCH(1,--(""&D4:D105=""),0)-1))Эта формула находит следующую пустую ячейку в столбце D и подсчитывает сумму всех значений в столбце E от текущей строки до найденной пустой ячейки.
-
Ручные подытоги по компаниям: Для ячеек E11 и E21, где необходимо подсчитать суммы по компаниям, следует использовать:
=SUBTOTAL(9, E10:INDEX(A:A,MAX(ROW(A$1:A11)*--(LEN(A$1:A11)>0))))Эта формула проверяет, где закончились данные в столбце A, и выполняет подсчет значений от последней заполненной строки до строки, предшествующей текущей.
Использование VBA для автоматизации:
Если объем данных значителен и использование формул требует много времени, можно рассмотреть возможность разработки скрипта на VBA. Он сможет автоматически вставлять указанные формулы в правильные ячейки и обновлять их по мере необходимости.
Пример простого скрипта для вставки формул:
Sub AddFormulas()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("ИмяВашегоЛиста")
ws.Range("E3").Formula = "=SUBTOTAL(9,E4:INDEX(E4:E109,MATCH(1,--(""&D4:D105=""),0)-1))"
' Повторите для других необходимых ячеек
End SubЗаключение:
Эти методы и инструменты позволяют сокращать трудозатраты на расчеты и делать процесс более эффективным. Использование функций Excel, таких как SUBTOTAL и INDEX, в сочетании с VBA для автоматизации задач даст возможность сосредоточиться на анализе данных, а не на их обработке. Важно учитывать, что увеличение диапазона до D$1048576 может замедлить вычисления и стоит применять этот подход только при необходимости.