У меня есть данные в Excel, которые выглядят следующим образом:
Shop | Division | Launch Year
-------+-------------+-------------
Shop 1 | Division 1 | 2005
Shop 2 | Division 1 | 2006
Shop A | Division 2 | 2005
Shop B | Division 2 | 2007
...
И так далее. Всего около 100 магазинов в 4 подразделениях. Мне нужно получить таблицу, показывающую, сколько магазинов открыто в конце каждого года по подразделениям. С помощью сводной таблицы я могу легко показать, сколько новых магазинов было открыто в течение года, но не сколько всего.
Я знаю, как добавлять текущие итоги в сводную таблицу, но в моем случае это не работает, так как у меня нет чисел для добавления - мне нужно вместо этого добавлять текущие подсчеты. Как это сделать?
Я ищу такой конечный результат:
Year | Division 1 | Division 2 | ...
-----+------------+------------+----
2005 | 1 | 1 |
2006 | 2 | 1 |
2007 | 2 | 2 |
...
решение1
Текущий счет — это текущая сумма единиц.
Если данные это позволяют, просто добавьте дополнительный столбец, полный единиц, и используйте его в сводной таблице для получения промежуточного итога.
решение2
Если ваша таблица относительно статична (столбцы и строки не сильно меняются), то используйте эти заголовки столбцов и строк. В теле используйте формулы типа (в псевдокоде):
=countifs('Launch Year',"<="&'row header', 'Division', 'column header')
Если вы используете правильную привязку, вы можете построить формулу один раз, а затем скопировать и вставить ее по всему тексту.
решение3
Для этого вам не нужна сводная таблица.
Создайте таблицу, как показано в желаемом выводе. Заполните область данных следующей формулой массива, введенной с помощью Ctrl Shift Enterи затем скопированной по горизонтали и вертикали:
=SUMPRODUCT(--IF($C$2:$C$5<=$E2,1,0),IF($B$2:$B$5=F$1,1,0))
При необходимости обновите ссылки на ячейки, обращая внимание на привязку.
Функция суммированиядокументацияот МС.
Вы используете IFоператоры two, чтобы создать два массива одинакового размера совпадающих значений (повороты --to TRUEи 1to FALSE) 0, умножить их друг на друга, чтобы найти записи, соответствующие обоим критериям, а затем суммировать полученный список 1«s» и 0«s», чтобы получить общую сумму.
EDIT: Чтобы ответить на ваш комментарий/вопрос
Настройка, показанная здесь до сих пор, представляет собой небольшую таблицу данных и результатов для проверки концепции. Чтобы сделать ее более расширяемой, вы, вероятно, превратите входную таблицу в именованную таблицу. Для этого выберите любую ячейку в соответствующем диапазоне (например, B4в настройке, как показано) и нажмите Ctrl+ T, затем подтвердите в результирующем модальном окне. Это упростит запись ссылок на ячейки и автоматически обновит любые ссылки на ячейки в этих диапазонах данных по мере добавления данных.
Вы также можете сделать таблицу результатов Таблицей, используя тот же метод. Таким образом, когда вы добавляете новый год, все, что вам нужно сделать, это ввести год в следующей строке под этой таблицей, и все формулы будут автоматически вставлены:
Обратите внимание, что это автоматическое обновление выходной таблицы не будет работать для добавления нового подразделения. Таблица расширится, чтобы вместить его, но вам придется вручную скопировать формулу SUMPRODUCTв новое поле.
Удачи и приятного просмотра!