Excel: сумма значений в нескольких столбцах в каждой строке, если критерии в другом столбце (также несколько) выполнены

Question

Пожалуйста, обратитесь к приложенному изображению, содержащему образец таблицы, которая похожа на фактическую книгу:

Я хочу написать формулы в столбцах BD так, чтобы для каждого клиента рейтинги добавлялись по типу. Например, для клиента Ava я хочу, чтобы формулы производили это: 22 в столбце B (тип A), поскольку в ее строке есть все типы A с рейтингами 7, 8 и 7 (7 плюс 8 плюс 7 равны 22). Если тип не найден в строке (в этом примере Тип B и C), результаты в столбцах C и D (Тип B и C соответственно) равны 0. Столбцы «Тип, Имя, Код и Рейтинги» будут расширяться вправо (для покрытия ежемесячно) и содержать другие столбцы между ними.

Я пробовал вложенные ifs, vlookups, sumifs, сопоставление индексов и т.д., Но я не нашел подходящего сочетания формул, чтобы они были полезны в долгосрочной перспективе после появления новых групп столбцов для «Типа, имени, кода и рейтинга». "добавляются постоянно. Созданные мной формулы нужно было обновлять каждый раз, когда добавляются новые группы этих столбцов.

Answer 1

Формула для клетки B2

=SUM((($E2:$XFA2)=B$1)*IFERROR(VALUE($H2:$XFD2),0))

Это действительно "матричная формула" (необходимо подтвердить, нажав CTRL+SHIFT+ENTER). После подтверждения растяните эту ячейку за столбцы B, C, D.

Эта формула находит столбец "рейтинг" всегда 3 столбца прямо из столбца "Тип".

Answer 2

Это делает то, что вы хотите, и масштабируется! это в VBa, хотя. Я знаю, что вы спрашивали о функции листа, но поскольку у вас нет (не было) подходящего ответа, я предлагаю это.

Помните, что отмена невозможна, поэтому сначала сделайте резервную копию вашего файла.

Option Explicit    
Sub WalkThePlank()

Dim startRow As Integer
startRow = 2                     'update this if needed Cap'ain, assumes content starts on row 2 as your "headings" be on row 1. Argggghhhh

Dim startCol As Integer
startCol = 69                   '69 is for Column E, the first column you want to look at. If this be wrong, then I'll feed myself to the sharks

Dim currentCol As Integer    
Dim typeToUse As String        
Do While Range("A" & startRow).Value <> ""
currentCol = startCol    
Do While Range(Chr(currentCol) & 1).Value <> ""
    Dim heading As String
    heading = Range(Chr(currentCol) & 1).Value        
    If (LCase(heading) = "type") Then
        typeToUse = Range(Chr(currentCol) & startRow).Value
    End If        
    If (LCase(heading) = "ranking") Then
        If LCase(typeToUse) = "a" Then
            Range("B" & startRow).Value = Range("B" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
        If LCase(typeToUse) = "b" Then
            Range("C" & startRow).Value = Range("C" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
        If LCase(typeToUse) = "c" Then
            Range("D" & startRow).Value = Range("D" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
    End If
    currentCol = currentCol + 1
Loop    
startRow = startRow + 1
Loop    
End Sub

Как добавить VBA в MS Office?

Единственным критерием является то, что слева направо столбец «Тип» должен находиться перед ранжированием.

На вашем скриншоте у вас есть "группы" из 4 столбцов

Тип Имя Код Рейтинг

С учетом вышесказанного, вы можете иметь группу из 2 или 20! Это все еще будет работать

Если вы добавите дополнительную группу столбцов к правому краю ваших данных, будь то группа из 2, 3, 4 (как у вас есть) или любого другого числа, она все равно будет работать

До

После запуска VBa

Затем удалите значения в столбцах TypeA, TypeB и TypeC и добавьте еще 2 столбца (я опустил столбец с именем Argh чтобы показать, как другая структура не имеет значения)

И запустить тот же VBa