☰

Excel: подсчет взвешенных клеток

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007
Excel: подсчет взвешенных клеток

У меня есть таблица Excel, которая является моим расписанием. Это значит, что у меня есть некоторые задачи, которые почти всегда представляют собой связанную ячейку, иногда я связываю 20 ячеек, иногда только две.Пример

Так как каждая ячейка - это один час, я хотел посчитать взвешенное количество ячеек. Это означает: если у меня есть ячейка, которая состоит только из одной ячейки, она считается за одну, если у меня есть 3 подключенных ячейки, она будет считаться за 3. Таким образом, в примере на картинке количество ячеек будет S1: 3, S2: 4, S3:2, S4:6

Я долго искал, но не нашел ни одной функции, которая бы решала эту проблему. Лучшее, что я нашел, это =countif(B4:H19, "Subject1"), но здесь ячейка, основанная на трех соединенных ячейках, считается за одну. Не могли бы вы помочь мне решить эту проблему?

Заранее благодарю за ответ!

решение1

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

введите описание изображения здесь

   Function MergedCellCount(ByRef Rng As Range, ByVal Criteria As Variant)

    Dim c       As Long
    Dim Cell    As Range
    Dim n       As Long
    Dim r       As Long
    
        Application.Volatile
        
        For c = 1 To Rng.Columns.Count
            For r = 1 To Rng.Rows.Count
                Set Cell = Rng.Cells(r, c)
                If Cell.MergeCells = True And (Rng.Columns(c).Column = Cell.MergeArea.Column) Then
                    If Cell = Criteria Then
                        n = n + Cell.MergeArea.Count
                        r = r + (Cell.MergeArea.Rows.Count - 1)
                    End If
                End If
            Next r
        Next c
        
        MergedCellCount = n
        
    End Function

    Function unMergedCellCount(ByRef Rng As Range, ByVal Criteria As Variant)

    Dim c       As Long
    Dim Cell    As Range
    Dim n       As Long
    Dim r       As Long
    
        Application.Volatile
        
        For c = 1 To Rng.Columns.Count
            For r = 1 To Rng.Rows.Count
                Set Cell = Rng.Cells(r, c)
                If Cell.MergeCells <> True And (Rng.Columns(c).Column = Cell.MergeArea.Column) Then
                    If Cell = Criteria Then
                        n = n + Cell.MergeArea.Count
                        r = r + (Cell.MergeArea.Rows.Count - 1)
                    End If
                End If
            Next r
        Next c
        
        unMergedCellCount = n
        
End Function

Как это работает:

  • Либо нажмитеАльт+Ф11или выберите TAB, затемрщелкните правой кнопкой мыши и в меню выберитеВновый код.

  • Скопия &пвоспользуйтесь этими макросами VBA.

  • НажиматьАльт+Qчтобы вернуться к листу.

  • Теперь сохраните книгу как файл с поддержкой макросов *.xlsm.

  • Введите эту формулу в ячейку K2и заполните ее.

    =MergedCellCount(H$2:H$17,J2)+unMergedCellCount(H$2:H$17,J2)

  • При необходимости отрегулируйте ссылки на ячейки.

Связанный контент