Подытог 109 при скрытии столбцов не работает в Excel

Question

У меня есть формулы в столбце B, которые подытоживают каждый второй столбец, начиная с столбца E. Таким образом, столбец E, G, I, K, M, O, Q, S и, в конечном итоге, больше. Формула в строке 4:

=SUBTOTAL(109,E4,G4,I4,K4,M4,O4,Q4,S4)

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

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

Answer 1

За SUBTOTAL Пожалуйста, прочитайте это

Функция SUBTOTAL предназначена для столбцов данных или вертикальных диапазонов. Он не предназначен для строк данных или горизонтальных диапазонов. Например, когда вы подводите промежуточный итог по горизонтальному диапазону, используя номер_функции 101 или более, например SUBTOTAL(109,B2:G2), скрытие столбца не влияет на промежуточный итог. Но сокрытие строки в промежуточном итоге вертикального диапазона влияет на промежуточный итог.

Единственный не-VBA способ сделать это (который я знаю) использует CELL как это:

=SUM(E4*(CELL("width",E4)>0),G4*(CELL("width",G4)>0),I4*(CELL("width",I4)>0),K4*(CELL("width",K4)>0),M4*(CELL("width",M4)>0),O4*(CELL("width",O4)>0),Q4*(CELL("width",P4)>0),S4*(CELL("width",S4)>0))

И недостатком является то, что вам нужно делать какие-либо реальные действия для пересчета (в то время как промежуточный итог делает пересчет, скрывая строку, CELL не). Просто введите ячейку и нажмите Enter (или просто Del при выборе пустой ячейки) или нажмите "Рассчитать сейчас" на вкладке "Формулы".

Он использует поведение, при котором скрытые строки возвращают ширину 0 (то же самое для строк и высоты), но здесь проверяется весь столбец (скрытие ячейки за строкой не приводит к изменению ширины).

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

Answer 2

Я предлагаю создать вспомогательный ряд, такой как E12 - S12

=CELL("Width",E4)

поместите этот код в каждый столбец и скройте всю строку

Тогда в сумме, скажем, T4 написать этот код

=SUMIF($E$12:$S$12,">0",E4:S4)

Единственная проблема с этой формулой состоит в том, что конкретное событие "Скрыть", "Показать" не регистрируется как событие, а ячейки суммы не пересчитываются. Это можно исправить с помощью VBA и UIeditor.

Вам понадобится UIeditor, чтобы добавить пользовательские ленты для вашего листа, чтобы поймать это конкретное событие, например скрыть / показать столбцы, для этого вам нужен редактор, который можно загрузить здесь: http://www.rondebruin.nl/win/s2/win001.htm

Затем добавьте этот код для вашего листа:

<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
<commands >
    <command 
        idMso="ColumnsHide"
        onAction="Column_Hide_Macro"/>
    <command 
        idMso="ColumnsUnhide"
        onAction="Column_UnHide_Macro"/>
</commands >

Так что теперь, когда кто-то нажимает скрыть или показать столбцы макросы "Column_Hide_Macro" или "Column_UnHide_Macro" активируются

Затем добавьте в свою таблицу

Sub column_hide_Macro(control As IRibbonControl, ByRef cancelDefault)
    cancelDefault = False
    alertTime = Now + TimeSerial(0, 0, 0.1)
    Application.OnTime alertTime, "reCalc"
End Sub
Sub column_unhide_Macro(control As IRibbonControl, ByRef cancelDefault)
    cancelDefault = False
    alertTime = Now + TimeSerial(0, 0, 0.1)
    Application.OnTime alertTime, "reCalc"
End Sub
Sub reCalc()
    Application.CalculateFullRebuild
End Sub

Добавьте этот код в модуль, а не код листа или книги!

Теперь вы должны пересчитывать листы каждый раз, когда скрываете или отображаете столбец.

Приветствия...

Answer 3

Например, в строке 12 укажите "H" в каждом столбце, который вы не хотите суммировать. Формула в ячейке B4 тогда

=SUMIFS(E4:S4,E12:S12,"<>H")

Answer 4

SUBTOTAL Фильтрует скрытые строки, а не скрытые столбцы или ячейки.

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

Самое простое решение - реструктурировать свой лист и использовать скрытые строки.