Агрегирование столбцов в Excel

Question

У меня есть данные, состоящие из даты и списка покупок, что-то вроде этого (запятые представляют отдельные столбцы):

**date,product1,product2,product3** 
08/11/13,oranges,apples,chips
08/11/13,grapes,oranges
08/12/13,grapes,pineapples
08/12/13,grapes,oranges

Я хочу знать, сколько предметов каждого продукта было куплено на каждую дату (и подготовить его).

Если бы у меня был один столбец продукта, это было бы довольно просто; Я мог бы просто использовать сводную таблицу. Однако в моем случае, для 11 августа мне нужен способ подсчета "апельсинов" из product1 вместе с "апельсинами" из product2...

Одним из способов будет дублирование строк и перевод данных в один столбец, но я бы хотел этого избежать.

Есть ли способ сделать это, желательно без использования VBA?

Answer 1

Если ваши данные находятся в диапазоне A2:D25, то вы можете использовать следующую формулу массива:

=SUM(($A$2:$A$25=DATEVALUE("8/11/13"))*
    (($B$2:$B$25="oranges")+($C$2:$C$25="oranges")+($D$2:$D$25="oranges")))

Вам нужно будет ввести его с помощью комбинации клавиш Control - Shift - Enter .

Если у вас есть несколько дат, для которых вы хотите рассчитать итоги для каждого из большого числа продуктов, вы можете сделать следующее.

Предположим, что данные вашего продукта находятся в диапазоне A2:D20000 на листе 1, а диапазон дат - в столбце A, как в вашем примере. Скажем, вы хотите поместить количество продуктов в Sheet2.

В Sheet2 вы должны ввести в ячейки B1, C1 и т.д. Интересующие вас даты. Ниже, в диапазоне A2:A2000, у вас есть список ваших продуктов. В ячейку B2 введите эту формулу массива:

=SUM((Sheet1!$A$2:$A$20000=B$1)*((Sheet1!$B$2:$B$20000=$A2)+
     (Sheet1!$C$2:$C$20000=$A2)+(Sheet1!$D$2:$D$20000=$A2)))

и скопируйте его в конец списка продуктов. Это дает вам счет на первое свидание.

Затем скопируйте весь столбец с данными о продуктах для каждого столбца с датой в строке 1. Это дает вам счет на все даты.

В качестве альтернативы, если у вас есть как большое количество продуктов, так и большое количество дат, только немного более сложная формула без массивов даст вам лучшую производительность (т. Е. Для расчета потребуется меньше времени).

В ячейку B2 вместо этого введите:

     =COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$B$2:$B$20000,$A2)+
      COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$C$2:$C$20000,$A2)+
      COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$D$2:$D$20000,$A2)

Затем вы скопируете формулу вниз списка продуктов, а затем скопируете весь столбец формул (в диапазоне B2:B2000) во все столбцы, для которых вы указали даты в строке 1.