У меня есть эти данные в Excel, и я хочу найти сумму каждой категории, и я хочу, чтобы сумма менялась автоматически при добавлении дополнительных значений. Любые идеи о том, как это сделать, если это возможно?
1
1 ответ
3
Какой бы метод вы ни использовали для суммирования, вам нужно использовать либо динамический именованный диапазон, либо (в Excel 2007 или более поздней версии) таблицу данных. Любой из них расширит ваш диапазон данных, когда вы добавите новые значения.
Таблица данных
Для таблицы данных вы просто выделите ячейку внутри своего диапазона данных, а затем перейдете на вкладку Главная | Отформатируйте как таблицу и выберите любой из доступных форматов.
Excel попытается автоматически определить диапазон данных - при условии, что ваши данные размещены, как в вашем примере, без пустых строк или столбцов и заголовков в верхней части, он сможет автоматически получить диапазон. Если нет, вы можете выбрать его здесь.
Нажмите OK, и ваша таблица данных будет создана.
Таблица данных дает вам ряд отличных функций:
Лучшим для наших целей является то, что он создает именованный диапазон, по умолчанию называемый Table1, который автоматически расширяется, если новые данные добавляются в строку непосредственно под ним или в столбец рядом с ним.
Вторым лучшим для наших целей является то, что вы можете использовать этот именованный диапазон в формулах. В вашем примере, предполагая, что заголовки "Газ" и "Продуктовый" находятся в столбце D, мы можем поместить следующую формулу в столбец E:
=SUMIF(Table1[Item],D4,Table1[Value])Эта формула будет смотреть на таблицу данных,Table1, и посмотрите на столбецItemдля всех значений, которые соответствуют значению вD4. Затем он будет суммировать соответствующие значения из столбцаValue.
Вы также можете использовать Table1 в качестве источника для сводной таблицы, но она не будет автоматически обновляться при добавлении новых данных в таблицу данных; вам нужно будет перейти к Pivot, щелкнуть по нему правой кнопкой мыши и выбрать "Обновить".
Динамический именованный диапазон
Чтобы было понятно, я рекомендую опцию Data Table. Это чище, проще, и там меньше места для вещей, которые идут ужасно неправильно! Однако, если вы предпочитаете, мы можем посмотреть на динамический именованный диапазон. Это было бы необходимо в более старых версиях Excel, и в некоторых ситуациях вы можете предпочесть это.
На самом деле мы собираемся создать два именованных диапазона, используя очень похожую формулу для каждого. В Excel 2007 и более поздних версиях перейдите на вкладку Формулы и выберите «Диспетчер имен»:
В окне диспетчера имен нажмите «Создать», чтобы перейти в поле «Новое имя». Нам понадобятся два основных элемента: имя и формула. В имени не должно быть пробелов; в нашем случае мы, вероятно, захотим использовать Item:
В поле «Относится к» мы собираемся поместить формулу. Обратите внимание, что при вводе этого текста клавиши со стрелками фактически начнут выделять ячейки в электронной таблице, если вы сначала не нажмете клавишу F2. Введите следующее: =OFFSET(Sheet1!$A$2,0,0,COUNTA(SHEET1!$A:$A)-1,1)
Функция OFFSET используется для возврата диапазона ячеек, начиная с ссылочной ячейки - в данном случае Sheet1!A2 - затем движение вниз на определенное количество строк и поперек на определенное количество столбцов. Мы используем 0,0 для этого. Затем он возвращает диапазон с определенной высотой и определенной шириной. Мы используем COUNTA(A:A) чтобы подсчитать, сколько ячеек в столбце A имеют значения любого вида, чтобы получить эту высоту, затем вычесть 1, чтобы учесть тот факт, что наши данные начинаются со строки 2, и вернуть диапазон, который 1 столбец в ширину.
Создайте второй именованный диапазон, такой же, как первый, но с именем "Значение" и формулой, ссылающейся на столбец B: =OFFSET(Sheet1!$B$2,0,0,COUNTA(SHEET1!$B:$B)-1,1)
Теперь мы можем использовать эти именованные диапазоны в качестве источников для формулы, аналогичной той, которую мы использовали в методе Data Table : =SUMIF(Item,D4,Value)
Вы также можете использовать динамические именованные диапазоны для подачи в сводную таблицу, но в этом случае вы захотите изменить ,1) на ,1) , чтобы вернуть весь набор данных.
Просто используйте весь столбец
Все эти методы довольно сложны, но они избегают попыток использовать слишком много ресурсов для достижения цели. Если вас это не беспокоит, вы всегда можете воспользоваться простой опцией: ссылаться на весь столбец в формулах.
Поместите формулу, подобную этой, в ваши итоговые ячейки: =SUMIF($A:$A,D4,$B:$B) Это включает все строки в столбцах A и B. Это будет работать, но с меньшим разбросом, чем другие варианты ,