Как в эксель сложить строки с одинаковым названием

1. Выделите ячейку в диапазоне ячеек, которые нужно выделить.
2. Перейдите в раздел Структура >>Группа данных > структура, а затем выберите Автоматические структуры.

Структурирование данных вручную

Важно: При группировке уровней структуры вручную лучше отобразить все данные , чтобы избежать неправильной группировки строк.

1. Чтобы очертить внешнюю группу (уровень 1), выделите все строки, которые содержит внешняя группа (т. е. строки сведений и, если вы добавили их, их суммарные строки).

Совет: Если выбрать целые строки, а не только ячейки, Excel автоматически группирует по строкам— диалоговое окно Группа даже не открывается.

Примечание: Если вам не нужно создавать внутренние группы, перейдите к шагу f ниже.

Для каждой внутренней вложенной группы выберите строки с подробными данными, соответствующие итоговой строке.

Перейдите в раздел> группа > >данных.

В диалоговом окне Группа выберите Строки, а затем нажмите кнопку ОК. Рядом с группой на экране появятся знаки структуры.

Совет: Если выбрать целые строки, а не только ячейки, Excel автоматически группирует по строкам— диалоговое окно Группа даже не открывается.

для группы, а затем перейдите в раздел > структура >Разгруппировать > Разгруппировать.

Важно: Если вы разгруппируете структуру, пока подробные данные скрыты, строки сведений могут оставаться скрытыми. Для отображения данных перетащите указатель через номера видимых строк рядом со скрытыми строками. Затем перейдите в раздел Главная > Ячейки > Формат, наведите указатель мыши на пункт Скрыть & Отобразить, а затем выберите Отобразить строки.

Создание многоуровневой структуры из столбцов

1. Убедитесь в том, что в первом столбце каждой строки данных, для которых требуется создать структуру, имеется заголовок, что каждая строка содержит данные одного типа и что в диапазоне данных отсутствуют пустые строки или столбцы.
2. Вставьте собственные итоговые столбцы с формулами непосредственно справа или слева от каждой группы строк с подробными данными. В таблице, приведенной в шаге 4, показан пример.

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

Если столбец сводки находится слева от столбцов сведений, перейдите в раздел Данные >Структура, а затем выберите средство запуска диалогового окна.

Откроется диалоговое окно Параметры.

1. Выделите ячейку в диапазоне.
2. Перейдите в раздел > структура >группа >группа, а затем выберите Пункт Автоматические структуры.

Структурирование данных вручную

Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок при группировке столбцов.

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

Перейдите в раздел >>>группа данных.

Перейдите в раздел Группа >структуры > данных.

Вы также можете разгруппировать разделы структуры, не удаляя весь уровень. Удерживая нажатой клавишу SHIFT, выберите

группы, а затем перейдите в раздел > структура >Разгруппировать.

Если разгруппировать структуру, пока подробные данные скрыты, столбцы сведений могут оставаться скрытыми. Для отображения данных перетащите указатель через номера видимых столбцов рядом со скрытыми столбцами. Перейдите в раздел Главная > Ячейки > формат, наведите указатель мыши на пункт Скрыть & Отобразить, а затем выберите Отобразить столбцы.

Отображение и скрытие структурированных данных

1. Если символы структуры не отображаются

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

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

символов структуры. Например, если есть три уровня, выберите

Настройка многоуровневой структуры со стилями

Для выделенных строк Microsoft Excel использует такие стили, как RowLevel_1 и RowLevel_2 . Для контурных столбцов Excel использует такие стили, как ColLevel_1 и ColLevel_2. В этих стилях используются полужирные, курсивные и другие текстовые форматы, чтобы различать строки или столбцы сводки в данных. Изменив способ определения каждого из этих стилей, можно применить различные форматы текста и ячеек для настройки внешнего вида структуры. Стиль можно применить к структуре либо при создании структуры, либо после ее создания.

Выполните одно или несколько из указанных ниже действий.

Автоматическое применение стиля к новым строкам или столбцам сводки

Перейдите в раздел Данные >Структура, а затем выберите средство запуска диалогового окна.

Откроется диалоговое окно Параметры.

Назначение стиля существующей итоговой строке или столбцу

1. Выделите ячейки, к которым требуется применить стиль.
2. Перейдите в раздел Данные >Структура, а затем выберите средство запуска диалогового окна.

Откроется диалоговое окно Параметры.

Выберите проверка автоматические стили, а затем выберите Применить стили.

Для форматирования структурированных данных также можно применять автоформаты.

Основы Excel: 4 способа объединить ячейки и данные в них

Инструкции со скриншотами и нюансами. Учим использовать кнопку «Объединить» и функции СЦЕПИТЬ, СЦЕП, ОБЪЕДИНИТЬ. Рассказываем, какой метод выбрать.

Иллюстрация: Meery Mary для Skillbox Media

Ксеня Шестак

Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.

Объединение ячеек в Excel позволяет из нескольких ячеек таблицы сделать одну. Такая опция обычно требуется в двух случаях:

• если нужно отформатировать таблицу — например, расположить название таблицы по центру, убрать лишние пустые ячейки или оформить шапку;
• если нужно объединить информацию — из нескольких строк или столбцов сделать одну ячейку, при этом сохранив все данные.

Рассмотрим четыре способа объединения ячеек в Excel. Разберёмся, для каких случаев они подойдут и как их применять. В конце расскажем, какие из этих способов работают в «Google Таблицах».

• Кнопка «Объединить» — когда нужно сделать шапку в таблице.
• Функция СЦЕПИТЬ — когда нужно собрать данные из нескольких ячеек в одну.
• Функция СЦЕП — когда нужно собрать данные из большого диапазона.
• Функция ОБЪЕДИНИТЬ — когда нужно собрать данные из большого диапазона и автоматически разделить их пробелами.

Кнопка «Объединить»

Для чего подойдёт. Для форматирования таблицы, когда нужно улучшить её внешний вид. Например, сделать общую шапку для нескольких столбцов. Лучше, чтобы объединяемые ячейки были пустыми или информация была только в одной из них.

Особенности метода. После объединения сохраняется только значение первой ячейки, информация из других удаляется.

Как сделать. Допустим, нужно отформатировать таблицу с каталогом авто: оформить шапку для колонок с характеристиками и убрать лишнюю пустую ячейку.

Для начала сделаем шапку. Выделяем ячейки над колонками с характеристиками авто: год выпуска, объём двигателя, коробка передач, привод, руль.

На вкладке «Главная» нажимаем на кнопку «Объединить ячейки» или «Объединить и поместить в центре» — в зависимости от того, какой визуальный результат хотим получить.

В итоге вместо пяти ячеек получаем одну. Вводим её название и меняем оформление, если нужно.

В нашем случае получилась шапка части таблицы — «Характеристики автомобилей».

По аналогии с предыдущими шагами уберём лишнюю пустую ячейку над ячейкой «Марка, модель». Для этого нужно выделить их одновременно и также нажать на кнопку «Объединить и поместить в центре».

Получаем таблицу в таком виде:

Чтобы убрать объединение ячеек, выделим их и нажмём на кнопку «Отмена объединения ячеек». Можно выбирать всю таблицу целиком — тогда Excel сам найдёт объединённые ячейки и разъединит их. Можно выделить отдельный диапазон — в этом случае разъединится только он. Мы выделим и разъединим всю шапку таблицы.

Получим таблицу в таком виде. Можно внести необходимые изменения, если нужно, и объединить ячейки снова.

Дополнительно в этом методе есть кнопка «Объединить по строкам». С помощью неё можно объединить ячейки выбранного диапазона построчно. Например, есть диапазон из трёх столбцов и семи строк — нужно из всех этих столбцов сделать один, но при этом оставить количество строк неизменным. Выделим всю таблицу целиком и нажмём эту кнопку.

Получим таблицу в таком виде — три столбца объединились в один, при этом количество строк осталось прежним.

Следующие три способа используются для объединения ячеек с сохранением всех данных в них. Они работают по такому принципу: функции собирают значения из выбранных ячеек в отдельную пустую ячейку.

Например, нам нужно собрать все характеристики автомобилей в одну строку, чтобы в дальнейшем использовать их для квитанции. Разберёмся, как сделать это с помощью трёх функций.

Функция СЦЕПИТЬ

Для чего подойдёт. Для объединения небольшого количества ячеек с сохранением всех данных.

Особенности метода. Каждую ячейку нужно указывать в виде отдельного аргумента функции; большой диапазон сразу выделить нельзя.

Функция склеивает все данные из исходных ячеек в итоговую ячейку. Часто эти данные нужно разделить: вставить пробелы, знаки препинания, символы или слова. Это придётся прописывать вручную в строке ссылок.

Как сделать. Для начала выделим ячейку, в которую функция соберёт значения из объединяемых ячеек. В нашем случае создадим дополнительную колонку «Данные для квитанции» и выделим первую ячейку.

Дальше открываем окно для построения функции. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».

Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.

Справа появляется окно «Построитель формул». В нём через поисковик находим функцию СЦЕПИТЬ и нажимаем «Вставить функцию».

Появляется окно для ввода аргументов функции. «Текст 1», «Текст 2» — ячейки, значения которых мы объединяем. В нашем случае таких ячеек пять, поэтому с помощью кнопки + добавляем ещё три таких аргумента.

Заполняем аргументы функции. Поочерёдно нажимаем на окно каждого аргумента и из таблицы выбираем значение для него.

Порядок действий, чтобы указать значение, выглядит так:

• Ставим курсор в окно «Текст1» в построителе формул.
• Выбираем первое значение столбца «Год выпуска» в таблице. Это ячейка B4.

Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=СЦЕПИТЬ(B4).

То же самое делаем для остальных аргументов: «Текст2» → значение ячейки «Объем», «Текст3» → значение ячейки «Коробка передач», «Текст4» → значение ячейки «Привод», «Текст5» → значение ячейки “Руль”.

В итоговом виде функция получается такой: fx=СЦЕПИТЬ(B4; C4; D4; E4; F4). Нажимаем кнопку «Готово».

Функция объединила данные всех ячеек, но не добавила пробелы и знаки препинания между ними. В этом методе их можно добавить только вручную, с помощью изменений в строке ссылок.

Добавляем в получившуюся функцию после значения каждого аргумента запятую и пробел, в таком виде, вместе с кавычками: «, «;

Получаем функцию в таком виде: fx=СЦЕПИТЬ(«год выпуска «;B4;», объем «;C4;», коробка передач «;D4;», привод «;E4;», руль «;F4). Нажимаем Enter.

Таким же образом можно добавить не только пробелы и знаки препинания, но и целые слова и фразы.

Для нашего примера можно преобразовать функцию к такому виду: fx=СЦЕПИТЬ(«год выпуска «;B4;», объем «;C4;», коробка передач «;D4;», привод «;E4;», руль «;F4). Главное — не забывать указывать все необходимые пробелы, которые должны быть между объединёнными данными.

Дальше нужно протянуть это значение вниз до конца таблицы, чтобы функция объединила характеристики всех остальных автомобилей.

Функция СЦЕП

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

Особенности метода. Можно указать сразу весь диапазон ячеек. Но функция, как и СЦЕПЛЕНИЕ, склеит их: значения не будут ничем разделены.

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

Как сделать. По аналогии с функцией СЦЕПЛЕНИЕ выделим ячейку, в которую функция будет собирать значения. Откроем окно для построения функций и найдём функцию СЦЕП.

Появляется окно построителя формул. В нём аргумент «Текст 1» — диапазон ячеек, который нужно объединить. В нашем случае выделяем все ячейки с характеристиками автомобилей и жмём «Готово».

Выбранное значение диапазона переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=СЦЕП(B4:F4).

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

Вернёмся в окно построения функции и поменяем аргументы на такой вид: «Текст1» → значение ячейки «Год выпуска», «Текст2» → «, «, «Текст3» → значение ячейки «Объём», «Текст4» → «, «. И так далее до последней ячейки, которую нужно объединить: чередуем значения ячеек и аргумент-разделитель.

Затем нажимаем «Готово». Итоговое значение функции принимает вид: fx=СЦЕП(B4;», «;C4;», «;D4;», «;E4;», «;F4), и данные ячейки разделяются запятыми и пробелами.

Чтобы добавить не только запятые с пробелами, но и слова, нужно дописать их в промежуточных аргументах функции. Сделаем это на нашем примере. Вернёмся в построитель формул и заменим данные:
«Текст2» → «, объём «, «Текст4» → «, коробка передач «, «Текст6» → «, привод «, «Текст8» → «, руль «.

В итоге функция выдаёт значение, где все данные разделены между собой дополнительными словами. Формула при этом принимает вид: fx=СЦЕП (B4;”, объём “; C4;”, коробка передач “; D4;”, привод “; E4;”, руль “; F4).

Как и в предыдущем методе, можно было вносить изменения в формулу сразу в строке ссылок. Но через окно построения функций это делать удобнее — меньше вероятность ошибиться.

Протягиваем полученное значение вниз до конца таблицы — функция объединяет характеристики всех остальных автомобилей по такому же принципу.

Функция ОБЪЕДИНИТЬ

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

Особенности метода. Можно указать сразу весь диапазон ячеек и разделитель для них. При этом значение самого разделителя будет одним для всех объединённых ячеек. Если нужно изменить разделитель для части ячеек — делать это придётся вручную в строке ссылок.

Как сделать. Выделим ячейку, в которую функция будет собирать значения. Откроем окно для построения функций и найдём функцию ОБЪЕДИНИТЬ.

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

«Разделитель» — значение, которое появится между ячейками после объединения. Разделитель будет одинаковым для всех ячеек.

В нашем случае в качестве разделителя добавим запятую и пробел: «, «.

«Пропускать_пустые» — условное значение, которое определит, что функция будет делать с пустыми ячейками в диапазоне:

• Если нужно, чтобы пустые ячейки игнорировались и разделители не дублировались в них, вводим 1 (ИСТИНА).
• Если нужно, чтобы пустые ячейки обязательно учитывались, вводим 0 (ЛОЖЬ).

В нашем примере в таблице нет пустых ячеек, поэтому не принципиально, какое значение устанавливать в этом аргументе. Введём 1.

«Текст1» — значения ячеек, которые нужно объединить. Можно выбирать одним диапазоном или вводить каждое значение по отдельности.

В нашем примере выберем все ячейки одним диапазоном.

Итоговая функция в строке ссылок принимает вид: fx=ОБЪЕДИНИТЬ(«, «;1;B4:F4).

Нажимаем кнопку «Готово» и растягиваем полученную ячейку на все остальные строки.

В итоге функция объединила данные всех ячеек и сразу разделила их запятыми и пробелами. С одной стороны, это удобно: не пришлось вводить разделители несколько раз. С другой стороны, функция позволила ввести только одно значение разделителя. Если нужно дополнить значения дополнительным текстом, разным для всех ячеек, — придётся делать это вручную через построитель формул. Как это сделать, можно посмотреть в предыдущей инструкции для функции СЦЕП.

Какие методы объединения ячеек есть в «Google Таблицах»? В них есть кнопка «Объединить», которая работает по аналогии с Excel. Также есть функция СЦЕПИТЬ, но нет окна построителя формул — прописывать значения придётся вручную. Если вам нужен разделитель в виде запятой и пробела, то функция будет выглядеть так: fx=СЦЕПИТЬ(C4;», «;D4).

Другие материалы Skillbox Media для менеджеров

• Статья: модель VUCA и как она помогает процветать в хаосе
• Подборка одиннадцати типичных ошибок при создании презентации
• Статья: что такое матрица БКГ и как она помогает определить, какие проекты стоит развивать
• Опрос руководителей о методах тайм-менеджмента: как сделать так, чтобы сотрудники всё успевали
• Разбор инструмента: ищем первопричины проблем с помощью рыбьих костей Исикавы

Пробелы, слова, запятые или другие символы, которые разделяют данные в итоговой ячейке. Например: “Акварельные краски, цвет: оранжевый”. Здесь “акварельные краски” и “оранжевый” — это исходные данные из ячеек, а «, цвет: » — это разделитель.

Как объединить повторяющиеся строки и суммировать значения в Excel

Освой востребованный фреймворк для создания приложений на Java, чтобы выйти на новый профессиональный уровень.

119 700 ₽ 0 ₽
0 ₽/мес рассрочка
Содержание

Объедините повторяющиеся строки и суммируйте их значения в Excel для анализа данных и упрощения отчетности.

• Все статьи (700)
• EXCEL (420)
• Python (18)
• Unity (1)
• WORD (117)
• Бесплатные онлайн-курсы (4)
• Бизнес (2)
• Веб программирование (4)
• Интернет магазины (1)
• Интерьер (67)
• Маркетинг (12)
• Онлайн сервисы (1)
• Профессии (20)
• Развитие (10)
• Управление (6)
• Учимся рисовать (16)
• Финансы (1)

Показать все категории

В рамках моей обычной работы несколько лет назад одной из вещей, с которыми мне приходилось иметь дело, было объединение данных из разных рабочих тетрадей, которые используют другие люди.

И одной из самых распространенных задач было объединить данные таким образом, чтобы не было повторяющихся записей.

Например, ниже представлен набор данных, содержащий несколько записей для одного и того же региона.

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

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

Объединение и суммирование данных с помощью опции консолидации

Если все, что вам нужно сделать, это объединить данные и добавить все значения для повторяющихся записей, лучший способ — использовать функцию консолидации в Excel.

Другой метод — использовать сводную таблицу и суммировать данные (далее в этом руководстве).

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

Несмотря на то, что это уникальные записи, поскольку стоимость продажи различается, для целей отчетности вы можете удалить несколько экземпляров одной и той же страны и показать стоимость продажи как консолидированную сумму.

Ниже приведены шаги для этого:

• Скопируйте исходные заголовки данных и вставьте их в то место, где вы хотите консолидировать данные.
• Выберите ячейку под крайним левым заголовком
• Перейдите на вкладку «Данные».
• В группе «Инструменты для работы с данными» щелкните значок «Консолидировать».
• В диалоговом окне «Консолидировать» выберите «Сумма» в раскрывающемся списке «Функция» (если он еще не выбран по умолчанию).
• Щелкните значок переключателя диапазона в поле «Ссылка».
• Выберите диапазон A2: B9 (данные без заголовков)
• Установите флажок в левом столбце.
• Нажмите ОК

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

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

Я решил получить СУММУ значений из каждой записи. Вы также можете выбрать другие параметры, такие как «Счетчик» или «Среднее» или «Макс. / Мин.».

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

Объединение и суммирование данных с помощью сводных таблиц

Сводная таблица — это швейцарский армейский нож для нарезки и нарезки данных в Excel.

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

Недостатком этого метода перед предыдущим является то, что этот метод требует больше кликов и на несколько секунд больше, чем предыдущий.

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

Ниже приведены шаги по созданию сводной таблицы:

• Выберите ячейку в наборе данных
• Щелкните вкладку Вставка
• В группе Таблицы выберите опцию Сводная таблица».
• В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица или диапазон указаны правильно.
• Щелкните существующий лист
• Выберите место, куда вы хотите вставить итоговую сводную таблицу.
• Щелкните ОК.

Вышеупомянутые шаги вставят сводную таблицу в выбранную целевую ячейку.

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

Ниже приведены шаги для этого:

• Щелкните в любом месте области сводной таблицы, чтобы открыть панель сводной таблицы справа.
• Перетащите поле Country в область Row.
• Перетащите поле «Продажи» в область «Значения».

Предыдущие шаги суммируют данные и предоставляют общий объем продаж для всех стран.

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

Это также поможет вам уменьшить размер вашей книги Excel.

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

Надеюсь, вы нашли этот урок полезным!

Как объединить одинаковые строки в Excel и сложить их значения?

У меня такая таблица, задача объединять похожие диски в одну строку и сложить их значения (Кол-во). Это возможно только макросом сделать ? Нашел один вариант в сети, но не рабочий. Можно ли обычно формулой сделать ?

• Вопрос задан более года назад
• 1157 просмотров

7 комментариев

Средний 7 комментариев