Группировка строк Excel с суммированием
Обновлено: 20.11.2024
Если у вас есть список данных, которые вы хотите сгруппировать и обобщить, вы можете создать структуру до восьми уровней. Каждый внутренний уровень, представленный более высоким номером в контурных символах, отображает подробные данные для предыдущего внешнего уровня, представленного меньшим числом в контурных символах. Используйте схему, чтобы быстро отобразить сводные строки или столбцы или открыть подробные данные для каждой группы. Вы можете создать структуру строк (как показано в примере ниже), структуру столбцов или структуру строк и столбцов.
<р>1. Чтобы отобразить строки для уровня, щелкните соответствующие символы контура. <р>2. Уровень 1 содержит общий объем продаж для всех строк сведений. <р>3. Уровень 2 содержит общий объем продаж за каждый месяц в каждом регионе. <р>4. Уровень 3 содержит строки сведений — в данном случае строки с 17 по 20. <р>5. Чтобы развернуть или свернуть данные в схеме, нажмите на символы и или нажмите ALT+SHIFT+=, чтобы развернуть, и ALT+SHIFT+-, чтобы свернуть.Убедитесь, что каждый столбец данных, которые вы хотите выделить, имеет метку в первой строке (например, "Регион"), содержит аналогичные факты в каждом столбце и что в диапазоне, который вы хотите выделить, нет пустых строк или столбцов. .
При желании сгруппированные строки сведений могут иметь соответствующую итоговую строку — промежуточный итог. Чтобы создать их, выполните одно из следующих действий:
Вставка итоговых строк с помощью команды "Промежуточный итог"
Используйте команду «Промежуточный итог», которая вставляет функцию ПРОМЕЖУТОЧНЫЙ ИТОГ непосредственно под или над каждой группой строк сведений и автоматически создает структуру для вас. Дополнительные сведения об использовании функции «Промежуточный итог» см. в разделе Функция ПРОМЕЖУТОЧНЫЙ ИТОГ.
Вставьте свои собственные сводные строки
Вставляйте свои собственные сводные строки с формулами непосредственно под или над каждой группой строк с подробными сведениями. Например, под строками данных о продажах за март и апрель (или над ними) используйте функцию СУММ для подсчета продаж за эти месяцы. В таблице ниже в этом разделе показан пример этого.
По умолчанию Excel ищет сводные строки ниже подробных сведений, которые они суммируют, но их можно создать над строками подробных сведений. Если вы создали сводные строки ниже сведений, перейдите к следующему шагу (шаг 4). Если вы создали сводные строки над строками сведений, на вкладке "Данные" в группе "Структура" щелкните кнопку запуска диалогового окна.
Откроется диалоговое окно настроек.
Затем в диалоговом окне "Настройки" снимите флажок "Сводные строки ниже сведений" и нажмите "ОК".
Обрисуйте свои данные. Выполните одно из следующих действий:
Автоматическое выделение данных
Выберите ячейку в диапазоне ячеек, которые вы хотите выделить.
На вкладке "Данные" в группе "Структура" нажмите стрелку под группой и выберите "Автоматическая схема".
Выделите данные вручную
Важно! При ручной группировке уровней структуры лучше отображать все данные, чтобы избежать неправильного группирования строк.
Чтобы выделить внешнюю группу (уровень 1), выберите все строки, которые будут содержаться во внешней группе (т. е. строки сведений и, если вы их добавили, их итоговые строки).
<р>1. Первая строка содержит ярлыки и не выбрана. <р>2. Поскольку это внешняя группа, выберите все строки с промежуточными итогами и подробностями. <р>3. Не выбирайте общую сумму.На вкладке "Данные" в группе "Структура" нажмите "Группировать". Затем в диалоговом окне "Группа" нажмите "Строки" и нажмите "ОК".
Совет. Если вы выбираете целые строки, а не только ячейки, Excel автоматически группирует по строкам — диалоговое окно "Группировать" даже не открывается.
Символы контура отображаются рядом с группой на экране.
При необходимости наметьте внутреннюю вложенную группу — строки сведений для данного раздела ваших данных.
Примечание. Если вам не нужно создавать какие-либо внутренние группы, перейдите к шагу f ниже.
Для каждой внутренней вложенной группы выберите строки сведений, смежные со строкой, содержащей строку сводки.
<р>1. Вы можете создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже сгруппированы на уровне 2. <р>2. Этот раздел выбран и готов к группировке. <р>3. Не выбирайте итоговую строку для данных, которые вы группируете.На вкладке "Данные" в группе "Структура" нажмите "Группировать".
Затем в диалоговом окне "Группа" нажмите "Строки" и нажмите "ОК". Символы контура появляются рядом с группой на экране.
Совет. Если вы выбираете целые строки, а не только ячейки, Excel автоматически группирует по строкам — диалоговое окно "Группировать" даже не открывается.
Продолжайте выбирать и группировать внутренние строки, пока не создадите все нужные уровни в схеме.
Если вы хотите разгруппировать строки, выберите строки, а затем на вкладке "Данные" в группе "Структура" нажмите "Разгруппировать".
Вы также можете разгруппировать разделы схемы, не удаляя весь уровень. Удерживая нажатой клавишу SHIFT, щелкните или для группы, а затем на вкладке "Данные" в группе "Структура" нажмите "Разгруппировать".
Важно! Если разгруппировать структуру, когда подробные данные скрыты, строки сведений могут остаться скрытыми. Чтобы отобразить данные, перетащите видимые номера строк рядом со скрытыми строками. Затем на вкладке "Главная" в группе "Ячейки" нажмите "Формат", выберите "Скрыть и показать", а затем нажмите "Показать строки".
Убедитесь, что каждая строка данных, которые вы хотите выделить, имеет метку в первом столбце, содержит похожие факты в каждой строке, а диапазон не содержит пустых строк или столбцов.
Вставьте свои собственные итоговые столбцы с формулами сразу справа или слева от каждой группы столбцов с подробными сведениями. В таблице, приведенной на шаге 4 ниже, показан пример.
Примечание. Чтобы разбить данные по столбцам, необходимо иметь итоговые столбцы, содержащие формулы, которые ссылаются на ячейки в каждом из столбцов сведений для этой группы.
Если столбец сводки находится слева от столбцов сведений, на вкладке "Данные" в группе "Структура" щелкните кнопку запуска диалогового окна.
Откроется диалоговое окно настроек.
Затем в диалоговом окне "Настройки" снимите флажок "Сводные столбцы справа от подробностей" и нажмите "ОК".
Чтобы выделить данные, выполните одно из следующих действий:
Автоматическое выделение данных
Выберите ячейку в диапазоне.
На вкладке "Данные" в группе "Структура" нажмите стрелку под "Группой" и выберите "Автоматическая схема".
Выделите данные вручную
Важно! При ручной группировке уровней структуры лучше отображать все данные, чтобы избежать неправильной группировки столбцов.
Чтобы выделить внешнюю группу (уровень 1), выберите все подчиненные сводные столбцы, а также связанные с ними подробные данные.
<р>1. Столбец A содержит ярлыки.
<р>2. Выберите все столбцы сведений и промежуточных итогов. Обратите внимание: если вы не выберете целые столбцы, при нажатии кнопки «Группировать» (на вкладке «Данные» в группе «Структура») откроется диалоговое окно «Группа», в котором вам будет предложено выбрать «Строки» или «Столбцы». <р>3. Не выбирайте столбец общей суммы.На вкладке "Данные" в группе "Структура" нажмите "Группировать".
Символ контура появляется над группой.
Чтобы выделить внутреннюю вложенную группу столбцов сведений (уровня 2 или выше), выберите столбцы сведений, расположенные рядом со столбцом, содержащим итоговый столбец.
<р>1. Вы можете создать несколько групп на каждом внутреннем уровне. Здесь два раздела уже сгруппированы на уровне 2. <р>2. Эти столбцы выбраны и готовы к группировке. Обратите внимание: если вы не выберете целые столбцы, при нажатии кнопки «Группировать» (на вкладке «Данные» в группе «Структура») откроется диалоговое окно «Группа», в котором вам будет предложено выбрать «Строки» или «Столбцы». <р>3. Не выбирайте итоговый столбец для данных, которые вы группируете.На вкладке "Данные" в группе "Структура" нажмите "Группировать".
Символы контура отображаются рядом с группой на экране.
Продолжайте выбирать и группировать внутренние столбцы, пока не создадите все нужные уровни в структуре.
Если вы хотите разгруппировать столбцы, выберите столбцы, а затем на вкладке "Данные" в группе "Структура" нажмите "Разгруппировать".
Вы также можете разгруппировать разделы схемы, не удаляя весь уровень.Удерживая нажатой клавишу SHIFT, щелкните или для группы, а затем на вкладке "Данные" в группе "Структура" нажмите "Разгруппировать".
Если вы разгруппируете схему, когда подробные данные скрыты, столбцы подробностей могут остаться скрытыми. Чтобы отобразить данные, перетащите буквы видимого столбца рядом со скрытым столбцом. На вкладке "Главная" в группе "Ячейки" нажмите "Формат", выберите "Скрыть и показать", а затем нажмите "Показать столбцы".
Если вы не видите символы контура , и , выберите «Файл» > «Параметры» > «Дополнительно», а затем в разделе «Параметры отображения для этого листа» установите флажок «Показывать символы контура, если применяется контур», а затем нажмите ОК.
Выполните одно или несколько из следующих действий:
Показать или скрыть подробные данные для группы
Чтобы отобразить подробные данные в группе, нажмите кнопку группы или нажмите клавиши ALT+SHIFT+=.
Чтобы скрыть подробные данные для группы, нажмите кнопку группы или нажмите клавиши ALT+SHIFT+-.
Развернуть или свернуть весь план до определенного уровня
В контурных символах щелкните номер нужного уровня. Затем подробные данные на более низких уровнях скрываются.
Например, если схема имеет четыре уровня, вы можете скрыть четвертый уровень, а остальные уровни отобразить, нажав .
Показать или скрыть все выделенные подробные данные
Чтобы отобразить все подробные данные, щелкните самый нижний уровень контурных символов. Например, если есть три уровня, нажмите .
Чтобы скрыть все подробные данные, нажмите .
Для выделенных строк Microsoft Excel использует такие стили, как RowLevel_1 и RowLevel_2 . Для выделенных столбцов Excel использует такие стили, как ColLevel_1 и ColLevel_2. Эти стили используют жирный шрифт, курсив и другие текстовые форматы, чтобы различать строки или столбцы сводки в ваших данных. Изменяя способ определения каждого из этих стилей, вы можете применять различные форматы текста и ячеек для настройки внешнего вида схемы. Вы можете применить стиль к схеме либо при создании схемы, либо после ее создания.
Выполните одно или несколько из следующих действий:
Автоматически применять стиль к новым строкам или столбцам сводки
На вкладке "Данные" в группе "Структура" щелкните кнопку запуска диалогового окна.
Откроется диалоговое окно настроек.
Установите флажок "Автоматические стили".
Применить стиль к существующей строке или столбцу сводки
Выберите ячейки, к которым вы хотите применить стиль.
На вкладке "Данные" в группе "Структура" щелкните кнопку запуска диалогового окна.
Откроется диалоговое окно настроек.
Установите флажок "Автоматические стили" и нажмите "Применить стили".
Вы также можете использовать автоформаты для форматирования выделенных данных.
Если вы не видите символы контура , и , выберите «Файл» > «Параметры» > «Дополнительно», а затем в разделе «Параметры отображения для этого листа» установите флажок «Показывать символы контура, если применяется контур».
Используйте контурные символы , и , чтобы скрыть подробные данные, которые вы не хотите копировать.
Дополнительную информацию см. в разделе Отображение или скрытие выделенных данных.
Выберите диапазон итоговых строк.
На вкладке "Главная" в группе "Редактирование" нажмите "Найти и выбрать", а затем нажмите "Перейти".
Нажмите "Перейти к специальному".
Нажмите Только видимые ячейки.
Нажмите "ОК" и скопируйте данные.
Примечание. При скрытии или удалении контура никакие данные не удаляются.
Скрыть контур
Перейдите в меню "Файл" > "Параметры" > "Дополнительно", а затем в разделе "Параметры отображения для этого листа" снимите флажок "Показывать символы контура, если применяется контур".
Удалить контур
Нажмите на рабочий лист.
На вкладке "Данные" в группе "Структура" нажмите "Разгруппировать", а затем "Очистить структуру".
Важно! Если удалить структуру, когда подробные данные скрыты, строки или столбцы сведений могут остаться скрытыми.Чтобы отобразить данные, перетащите видимые номера строк или буквы столбцов рядом со скрытыми строками и столбцами. На вкладке "Главная" в группе "Ячейки" нажмите "Формат", выберите "Скрыть и показать", а затем нажмите "Показать строки" или "Показать столбцы".
Представьте, что вы хотите создать сводный отчет о ваших данных, в котором отображаются только итоги, сопровождаемые диаграммой этих итогов. В общем, вы можете сделать следующее:
Создайте сводный отчет
Обрисуйте свои данные.
Скройте сведения, щелкнув символы структуры , и , чтобы отобразить только итоги, как показано в следующем примере структуры строки:
Дополнительную информацию см. в разделе Отображение или скрытие выделенных данных.
Нарисуйте сводный отчет в виде диаграммы
Выберите сводные данные, которые вы хотите отобразить на диаграмме.
Например, чтобы отобразить только итоги Бьюкенена и Даволио, но не общие итоги, выберите ячейки с A1 по C19, как показано в приведенном выше примере.
Нажмите «Вставка» > «Диаграммы» > «Рекомендуемые диаграммы», затем перейдите на вкладку «Все диаграммы» и выберите тип диаграммы.
Например, если вы выбрали параметр "Сгруппированный столбец", ваша диаграмма будет выглядеть следующим образом:
Если вы показываете или скрываете детали в структурированном списке данных, диаграмма также обновляется, чтобы отображать или скрывать данные.
Вы можете группировать (или выделять) строки и столбцы в Excel для Интернета.
Примечание. Несмотря на то, что вы можете добавлять строки или столбцы сводки к своим данным (с помощью таких функций, как СУММ или ПРОМЕЖУТОЧНЫЕ ИТОГО), вы не можете применять стили или задавать положение для строк и столбцов сводки в Excel для Интернета.
Создайте структуру строк или столбцов
Контур строк в Excel Online
Первый столбец содержит ярлыки.
Строки сведений и итоговые строки сгруппированы в структуре.
Общая сумма не группируется в схеме.
Контур столбцов в Excel Online
Первая строка содержит ярлыки.
Столбцы сведений и итоговые столбцы сгруппированы в структуре.
Общая сумма не группируется в схеме.
Убедитесь, что каждый столбец (или строка) данных, которые вы хотите выделить, имеет метку в первой строке (или столбце), содержит аналогичные факты в каждом столбце (или строке) и что в диапазоне нет пробелов. строки или столбцы.
Выберите данные (включая любые итоговые строки или столбцы).
На вкладке "Данные" в группе "Структура" нажмите "Группировать" > "Группировать строки" или "Группировать столбцы".
При желании, если вы хотите выделить внутреннюю вложенную группу, выберите строки или столбцы в выделенном диапазоне данных и повторите шаг 3.
Продолжайте выбирать и группировать внутренние строки или столбцы, пока не создадите все нужные уровни в структуре.
Разгруппировать строки или столбцы
Чтобы разгруппировать, выберите строки или столбцы, а затем на вкладке "Данные" в группе "Структура" нажмите "Разгруппировать" и выберите "Разгруппировать строки" или "Разгруппировать столбцы".
Показать или скрыть выделенные данные
Выполните одно или несколько из следующих действий:
Показать или скрыть подробные данные для группы
Чтобы отобразить подробные данные в группе, щелкните значок группы или нажмите клавиши ALT+SHIFT+=.
Чтобы скрыть подробные данные для группы, щелкните значок группы или нажмите клавиши ALT+SHIFT+-.
Развернуть или свернуть весь план до определенного уровня
В контурных символах щелкните номер нужного уровня. Затем подробные данные на более низких уровнях скрываются.
Например, если схема имеет четыре уровня, вы можете скрыть четвертый уровень, а остальные уровни отобразить, нажав .
Показать или скрыть все выделенные подробные данные
Чтобы отобразить все подробные данные, щелкните самый нижний уровень контурных символов. Например, если есть три уровня, нажмите .
Чтобы скрыть все подробные данные, нажмите .
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
При работе с Excel мы можем суммировать значения, удовлетворяющие более чем одному критерию, с помощью функции СУММЕСЛИ. Это пошаговое руководство поможет пользователям Excel всех уровней суммировать значения по группам с помощью функций ЕСЛИ и СУММЕСЛИ.
Рисунок 1.Окончательный результат: сумма по группам
Окончательная формула: =ЕСЛИ(B3=B2,"",СУММЕСЛИ($B$3:$B$12,B3,$C$3:$C$12))
Синтаксис функции ЕСЛИ
Функция ЕСЛИ оценивает данный логический тест и возвращает ИСТИНА или ЛОЖЬ
= ЕСЛИ (логическая_проверка, [значение_если_истина], [значение_если_ложь])
- Аргументы «значение_если_истина» и «значение_если_ложь» являются необязательными. Если оставить это поле пустым, функция вернет значение TRUE, если логический тест выполнен, и FALSE, если нет.
Синтаксис функции СУММЕСЛИ
СУММЕСЛИ суммирует значения в указанном диапазоне на основе одного заданного критерия
=СУММЕСЛИ ( диапазон , критерии , [ диапазон_суммы ])
- Диапазон: диапазон данных, который будет оцениваться с использованием критериев.
- Критерии: критерии или условия, определяющие, какие ячейки будут добавлены.
- Sum_range: добавляемые ячейки; если оставить пустым, «sum_range» = «диапазон», что означает, что диапазон данных, которые будут добавлены, совпадает с диапазоном оцениваемых данных
Настройка данных
В нашей таблице есть три столбца: "Продукт" (столбец B), "Заказы" (столбец C) и "Сумма по группам" (столбец D). Мы хотим сгруппировать продукты и рассчитать сумму по группам. Результаты будут записаны в столбце D.
Рисунок 2. Пример данных для суммирования по группам
У нас есть три продукта, а именно: ноутбук, динамик и планшет. Во-первых, нам нужно отсортировать данные по продуктам, выполнив следующие действия:
Шаг 1 . Выберите диапазон для наших данных, B2:D12
Шаг 2 . Перейдите на вкладку "Данные" и выберите "Создать фильтр".
Рисунок 3. Создание фильтра для сортировки данных по продуктам
Шаг 3 . Нажмите на стрелку раскрывающегося списка "Продукт".
Шаг 4 . Выберите Сортировать от А до Я
Рисунок 4. Сортировка данных по продуктам
В итоговой таблице продукты будут расположены в алфавитном порядке.
Рисунок 5. Вывод: сортировка данных по продуктам с помощью фильтра Excel
Сумма заказов по продуктам
Теперь, когда мы отсортировали данные по продуктам, мы можем суммировать заказы по продуктам с помощью функций ЕСЛИ и СУММЕСЛИ. Наша цель — показать сумму в первой строке, в которой появляется продукт. Давайте выполним следующие шаги:
Шаг 1 . Выберите ячейку D3
Шаг 2 . Введите формулу: = ЕСЛИ ( B3=B2 , "" , СУММЕСЛИ ($B$3:$B$12,B3,$C$3:$C$12))
Шаг 3 . Нажмите Enter
Шаг 4. Скопируйте формулу из ячейки D3 в ячейки D4:D12, нажав значок «+» в правом нижнем углу ячейки D3 и перетащив ее вниз
Наша формула оценивает, равно ли значение в ячейке B3 значению предыдущей ячейки B2. Если логическая проверка возвращает ИСТИНА, функция ЕСЛИ вернет пустую строку «». В противном случае будут суммированы заказы в столбце C, соответствующие продукту в столбце B.
Поскольку B3 «Ноутбук» не равен B2 «Товар», наша формула возвращает сумму заказов для «Ноутбука». В результате общее количество заказов на ноутбуки в ячейке D3 составляет 4500, что является суммой 1000, 500, 1000 и 2000 в ячейках C3:C6.
Для ячеек D4:D6 наша формула возвращает пустую строку «», поскольку значение в ячейке B4:B6 равно ячейке перед ней, то есть ноутбуку.
Рисунок 6. Ввод формулы с использованием ЕСЛИ и СУММЕСЛИ для суммирования заказов по продуктам
Для последующих ячеек в столбце D наша формула возвращает сумму заказов для «Динамик» и «Планшет» в ячейках D7 и D10 соответственно. Ячейки между ними пусты или пусты, потому что они оцениваются как ИСТИНА по нашей формуле ЕСЛИ.
В таблице ниже показан окончательный результат по сумме заказов на продукт.
Рис. 7. Вывод: использование ЕСЛИ и СУММЕСЛИ для суммирования заказов по продуктам
В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть.Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.
Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.
Мне нужно разделить одну группу процентов на подгруппы и найти сумму подгруппы. по одной группе для каждого процента справа.
Во время работы с Excel мы получаем множество новых сценариев, о которых даже не подозревали, чтобы превосходить ежедневного пользователя, мы должны быть в состоянии столкнуться с этими сценариями. Мы не можем точно определить какую-то одну ситуацию, но наверняка такие уникальные ситуации возникнут. Одна из конкретных ситуаций, с которыми мы недавно столкнулись, заключалась в том, чтобы получить сумму нескольких элементов строки в одну ячейку, когда у нас есть все значения элемента строки в одном столбце. Итак, в этой статье мы покажем вам, как группировать значения суммы в Excel.
Как суммировать значения по группам в Excel?
Ниже показано, как использовать значения суммы по группам в Excel с примерами.
Когда вы получаете данные, вы получаете много позиций, и когда вы суммируете их, мы можем получить итоговую сумму в нескольких строках, так что это обычная задача, которую мы все выполнили, используя сводную таблицу. Использование сводной таблицы. Сводная таблица — это инструмент Excel, который позволяет извлекать данные в предпочтительном формате (панель мониторинга/отчеты) из больших наборов данных, содержащихся на листе. читать далее инструмент. Однако суммирование значений всех элементов одной группы в одной ячейке — это совсем другая задача без использования сводной таблицы.
Для приведенных выше данных нам нужно создать сумму значений по городам в одной ячейке, расположив все города по порядку.
На приведенном выше изображении у нас есть дополнительный столбец «Всего по городу», и все города отсортированы один за другим, а затем мы использовали функцию СУММ, чтобы получить общее количество по каждому городу. Но проблема с описанным выше методом заключается в том, что мы использовали функцию СУММ Excel. Функция СУММ в Excel. Функция СУММ в Excel складывает числовые значения в диапазоне ячеек. Будучи отнесенной к категории функции «Математика и тригонометрия», она вводится путем ввода «= СУММ», за которым следуют значения для суммирования. Значения, предоставляемые функции, могут быть числами, ссылками на ячейки или диапазонами. подробнее , и для каждого города есть разные позиции, и для каждого города нам нужно применять функцию СУММ индивидуально, поэтому при больших объемах данных это занимает много времени.
Возьмите приведенные выше данные и для этого экземпляра.
- Сортировать данные по названиям городов.
Теперь позвольте мне объяснить результат.
- В ячейке C2 у нас есть итоговая сумма по городу «Бангалор», потому что логический тестЛогический тестЛогический тест в Excel приводит к аналитическому результату, который может быть либо истинным, либо ложным. Оператор равенства «=» является наиболее часто используемой логической проверкой.Подробнее о функции ЕСЛИ говорится, что если ячейка A2 = A1, то она возвращает пустую ячейку или дает общую сумму города «Бангалор». Поскольку логика A2 = A1 неверна, она вернула сумму города «Бангалор».
- Теперь перейдем ко второму результату в ячейке D3. Мы получили пустой результат, потому что в этой ячейке логические тесты A3 = A2 равны TRUE, поэтому результат пустой.
- Но когда формула достигает D5, логический тест ячейки будет A5 = A4, поэтому в этом случае в ячейке A5 у нас есть "Хайдарабад", а в ячейке A4 у нас есть "Бангалор", так что оба различны, и на этот раз СУММЕСЛИ возвращает общее значение «Хайдарабад».
- Эту комбинацию ЕСЛИ и СУММЕСЛИ можно использовать для возврата суммы на основе группы значений.
Что нужно помнить
- Вам необходимо отсортировать данные по целевой группе. В этом примере нашей целью было получить итог по городу, поэтому мы отсортировали по городу.
- С помощью сводной таблицы мы получаем общую сводку.
Рекомендуемые статьи
Это руководство по сумме групп Excel. Здесь мы узнаем, как суммировать значения по группам Excel и использовать функции ЕСЛИ и СУММЕСЛИ вместе с примерами и загружаемым шаблоном Excel. Вы можете узнать больше об Excel из следующих статей –
поиск меню
Урок 20. Группы и промежуточные итоги
Введение
Рабочие листы с большим объемом содержания иногда могут показаться слишком сложными и даже трудными для чтения. К счастью, Excel может организовывать данные в группы, что позволяет легко отображать и скрывать различные разделы рабочего листа. Вы также можете суммировать различные группы с помощью команды «Промежуточный итог» и создать схему для своего листа
Чтобы сгруппировать строки или столбцы:
- Выберите строки или столбцы, которые хотите сгруппировать. В этом примере мы выберем столбцы A, B и C.
Выбор столбцов для группировки
Нажатие команды "Группировать"
Сгруппированные столбцы
Чтобы разгруппировать данные, выберите сгруппированные строки или столбцы, затем нажмите команду "Разгруппировать".
Нажатие команды "Разгруппировать"
Чтобы скрыть и показать группы:
- Чтобы скрыть группу, нажмите кнопку "Скрыть сведения".
Скрытие группы
Нажатие кнопки "Показать подробности", чтобы отобразить скрытую группу
Создание промежуточных итогов
Команда «Промежуточный итог» позволяет автоматически создавать группы и использовать общие функции, такие как СУММ, СЧЁТ и СРЗНАЧ, для суммирования данных. Например, команда «Промежуточный итог» может помочь рассчитать стоимость канцелярских товаров по типам из большого заказа на складские запасы. Он создаст иерархию групп, известную как схема, чтобы упростить организацию рабочего листа.
Перед использованием команды «Промежуточный итог» ваши данные должны быть правильно отсортированы, поэтому вы можете просмотреть наш урок «Сортировка данных», чтобы узнать больше.
Чтобы создать промежуточный итог:
В нашем примере мы будем использовать команду «Промежуточный итог» с формой заказа футболки, чтобы определить, сколько футболок было заказано в каждом размере (маленький, средний, большой и очень большой). Это создаст структуру нашего рабочего листа с группой для каждого размера футболки, а затем подсчитает общее количество футболок в каждой группе.
-
Сначала отсортируйте свой рабочий лист по данным, которые вы хотите подытожить. В этом примере мы создадим промежуточный итог для каждого размера футболки, поэтому наш рабочий лист был отсортирован по размеру футболки от меньшего к большему.
Таблица отсортирована по размеру футболки
Нажатие команды «Промежуточный итог»
Создание промежуточного итога
Выделенные данные и промежуточные итоги
Чтобы просмотреть группы по уровню:
Когда вы создаете промежуточные итоги, ваш рабочий лист делится на разные уровни. Вы можете переключаться между этими уровнями, чтобы быстро контролировать, сколько информации отображается на рабочем листе, нажимая кнопки уровня слева от рабочего листа. В нашем примере мы будем переключаться между всеми тремя уровнями в нашей структуре. Хотя этот пример содержит только три уровня, в Excel их может быть до восьми.
-
Щелкните самый нижний уровень, чтобы отобразить наименьшие детали. В нашем примере мы выберем уровень 1, который содержит только общий счет или общее количество заказанных футболок.
Просмотр данных на самом низком уровне
Просмотр данных на следующем уровне
Просмотр данных на самом высоком уровне
Вы также можете использовать кнопки "Показать" и "Скрыть сведения", чтобы отобразить или скрыть группы в структуре.
Отображение и скрытие новых групп в структуре
Чтобы удалить промежуточные итоги:
Иногда вы можете не захотеть сохранять промежуточные итоги на своем листе, особенно если вы хотите реорганизовать данные по-разному. Если вы больше не хотите использовать промежуточные итоги, удалите его из рабочего листа.
-
Выберите вкладку «Данные», затем нажмите команду «Промежуточный итог».
Нажатие команды "Промежуточный итог"
Удаление промежуточных итогов
Чтобы удалить все группы, не удаляя промежуточные итоги, нажмите стрелку раскрывающегося списка команды "Разгруппировать" и выберите "Очистить структуру".
Читайте также: