☰

Расчет суммы по целочисленному столбцу, когда другой столбец содержит критерий года для расчета

tag-icon • tag-icon microsoft-excel-2010 worksheet-function
Расчет суммы по целочисленному столбцу, когда другой столбец содержит критерий года для расчета

У меня есть электронная таблица, столбцы которой содержат следующие значения:

Column(A)       B            C       D     E  

NameA        01/02/2010      5       4     5  
NameB        05/06/2011      4       3     2  
NameC        12/11/2010      3       5     3  
NameD        08/14/2012      4       4     4  

-------------------------------------------------  

SUMMARY            2010      8       9     8  
                   2011      4       3     2  
                   2012      4       4     4

и так далее...

Я хочу вычислить итоговую сумму столбца C, где год в столбце B = 2010 (или 2011, или 2012 и т. д.), и создать сводную таблицу (возможно, на другом листе), сопровождаемую диаграммой.

Я рассмотрел функции СУММЕСЛИ, СУММЕСЛИМН и СУММПРОИЗВ.

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

решение1

АСводная таблицаможете делать то, что хотите.

Вот руководства по группировке по дате: http://www.databison.com/index.php/pivot-table-quick-tip-group-by-year-quarter-month-and-date-field/ http://www.youtube.com/watch?v=XR8x58Le0HA

решение2

Определенно, сводная таблица — это то, что нужно. Просуммируйте столбец C, перетащив его в область «Значения», и просуммируйте по строкам для столбца B, перетащив его в область «Строки».

Затем, чтобы получить то, что вам нужно, нужно сгруппировать строки — поскольку это даты, Excel предоставит вам полезные параметры, такие как по году, месяцу и т. д. Щелкните правой кнопкой мыши заголовок строки (например, дату), выберите «Группировать» > «Выбрать год» и снимите флажки со всех остальных, чтобы в итоге у вас осталась только сводка по году.

Дело сделано.

Добавьте диаграмму, используя PT в качестве источника данных, и вы получите простой результат.

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

PS: рассмотрите возможность преобразования исходных данных в таблицу, если вы хотите, чтобы PT продолжал работать, когда вы позже добавите больше данных.

решение3

Допустим, что годы указаны в формате B97:B99. Затем установите C97значение

=SUMIFS(C$1:C$4, $B$1:$B$4, ">="&DATE($B97,1,1), $B$1:$B$4, "<"&DATE($B97+1,1,1))

… корректировка  1и  4для отображения местоположения ваших данных. Это говорит о том, что нужно сложить (суммировать) значения в столбце  Cдля строк, где значение в столбце  B≥ 1 января года, указанного числом в ячейке,  B97но < 1 января следующего года. Затем перетащите/заполните вниз до строки  99и до нужного количества столбцов. Конечно, это может быть на отдельном листе; просто используйте обозначение Sheet1!.

Редактировать

Если вам нужно среднее значение этих данных, вы можете сделать следующее:

=SUMIF(аргументы как указано выше) / COUNTIF(Аргументы со 2-го по 5-й)

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

=IFERROR(Формула СУММЕСЛИ/СЧЁТЕСЛИ, из вышеприведённой, значение для отображения в случае ошибки)

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