Как преобразовать таблицу в сводку, как показано

Question

У меня есть таблица, как показано ниже. Я хочу добавить строки для категории и суммировать все (кроме нуля) одинаковые виды (скажем, лимон), одинаковую длину вида для каждой категории , Теперь я хочу объединить таблицу, как показано на рисунке - добавление строк в каждой категории должно иметь разную длину и быть автоматическим, например, в A может быть не все 120 видов.

NB. Я попробовал разворот, но не получилось. У меня более 100 видов и 1000 категорий

Answer 1

Сводная таблица может помочь в этом случае, хотя. Исходные данные должны быть размещены в строках, как записи в базе данных. Вот пример настройки сводной таблицы.

1. Скопируйте исходный диапазон.

2. Вставить и транспонировать.

3. Построить сводную таблицу на транспонированных данных.

4. Перетащите имена категорий "A", "B" и "C" сверху в область "Значения".

5. Измените тип расчета с "Количество" на "Сумма" для каждого из названий категорий.

ОБНОВЛЕНИЕ для пунктов 4 и 5

Если категорий много, вы можете автоматизировать пункты 4 и 5 - запустите приведенный ниже макрос, чтобы переместить все поля (кроме первого) в область "Значения" с типом вычисления "Сумма":

Sub AddDataFields()
    With ActiveSheet.PivotTables(1)
        Set objPivotFields = .PivotFields
        For i = 2 To objPivotFields.Count
            Set objPivotField = objPivotFields(i)
            Debug.Print objPivotField.Caption
            .AddDataField objPivotField, "Sum of " & objPivotField.Caption, xlSum
        Next
    End With
End Sub

6. Перетащите появившееся поле "Значения Σ" из "Метки столбцов" в область "Метки строк", перетащите "Категория" сверху в область "Метки строк".

7. Изменить макет отчета в табличную форму.

8. В результате теперь у вас есть суммы видов и длины в одном столбце.

9. Вы можете настроить фильтр меток, чтобы получать только суммы видов.

Answer 2

Вот рабочий пример, который может вам помочь:

Это, вероятно, вызывает функцию SUMIF(диапазон, критерии, [sum_range]), которая работает примерно так:

foreach element in range {
    if element == criteria {
        sum = sum + corresponding element in sum_range
    }
}
return sum

В этом случае вы бы добавили что-то вроде (см. Мой скриншот):

C9 = SUMIF(1:1;B9;2:2)

который просматривает всю первую строку и каждый раз находит метку, совпадающую с тем, что вы поместили в ячейку B11, он принимает соответствующее значение в строке 2.

Для суммы длины вида вы можете просто использовать функцию CONCATENATE в Excel, выполнив:

D9 = SUMIF(1:1;CONCATENATE(B9;"_length");2:2)

Надеюсь это поможет.

О, еще одна вещь, чтобы сохранить ссылки постоянными при перетаскивании, чтобы вы могли заполнить несколько ячеек, вставьте $ в ссылку:

C9 = SUMIF($1:$1;B9;$2:$2)

Таким образом, только B9 изменяется при перетаскивании на ссылку B10, B11 и т.д.