Как сплюсовать ячейки в экселе: Способы сложить значения в электронных таблицах

Суммирование в Excel, используя функции СУММ и СУММЕСЛИ

Главная » Уроки MS Excel » Подсчет и суммирование

Автор Антон Андронов На чтение 3 мин Опубликовано 27.04.2020

В этом уроке мы не будем рассматривать, как посчитать сумму в Excel при помощи оператора сложения, автосуммы и прочих инструментов. Сегодня мы разберем всего две функции: СУММ и СУММЕСЛИ. Спешу Вас обрадовать, их функционала достаточно, чтобы решать практически все возможные вопросы суммирования в Excel.

Функция СУММ – простое суммирование ячеек в Excel

Функция СУММ вычисляет сумму всех своих аргументов. Она является наиболее часто используемой функцией в Excel. К примеру, нам необходимо сложить значения в трех ячейках. Мы, конечно же, можем воспользоваться обычным оператором суммирования:

Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:

Поскольку функция СУММ поддерживает работу не только с отдельными ячейками, но и целыми диапазонами, то вышеприведенную формулу можно модифицировать:

Истинная мощь функции СУММ раскрывается, когда необходимо сложить большое количество ячеек в Excel. В примере ниже требуется просуммировать 12 значений. Функция СУММ позволяет сделать это несколькими щелчками мышью, если же использовать оператор сложения, то провозиться придется долго.

В следующем примере функция СУММ складывает весь столбец А, а это 1048576 значений:

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

Функция СУММ может принимать до 255 аргументов и суммировать сразу несколько несмежных диапазонов или ячеек:

Если среди суммируемых значений содержится текст, то функция СУММ их игнорирует, т.е. не включает в расчет:

Если же текстовые значения попытаться сложить оператором суммирования, то формула вернет ошибку:

Функция СУММ достаточно универсальна и позволяет использовать в качестве своих аргументов не только ссылки на ячейки и диапазоны, но и различные математические операторы и даже другие функции Excel:

Если интересно узнать, как посчитать накопительную сумму в Excel, обратитесь к этому уроку.

СУММЕСЛИ — условная сумма в Excel

Функция СУММЕСЛИ позволяет подсчитать условную сумму в Excel, т.е. сумму ячеек, которые удовлетворяют определенному критерию. Функция СУММЕСЛИ может содержать только один критерий.

Например, следующая формула суммируем только положительные числа диапазона A1:A10. Обратите внимание, что условие заключено в двойные кавычки.

В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:

Меняем условие, меняется и результат:

Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.

Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?

На этот случай функция СУММЕСЛИ припасла третий необязательный аргумент, который отвечает за диапазон, который необходимо просуммировать. Т.е. по первому аргументу функция проверяет условие, а третий подлежит суммированию.

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

Нажав Enter получим результат:

Если одного условия Вам не достаточно, то Вы всегда можете воспользоваться функцией СУММЕСЛИМН, которая позволяет осуществлять условное суммирование в Excel на основе нескольких критериев.

Суммирование – это одно из основных действий, которое пользователь выполняет в Microsoft Excel. Функции СУММ и СУММЕСЛИ созданы, чтобы облегчить эту задачу и дать пользователям максимально удобный инструмент. Надеюсь, что этот урок помог Вам освоить базовые функции суммирования в Excel, и теперь Вы сможете свободно применять эти знания на практике. Удачи Вам и успехов в изучении Excel!

Оцените качество статьи. Нам важно ваше мнение:

Как в excel плюсовать

Способы добавления значений на листе

​Смотрите также​​ А (в его​ запишите адреса всех​ чтобы получить результат​В ячейке B2 введите​Лучший способ добавить время​Функция СЦЕПИТЬ​ версии не поддерживается.​функции СЦЕПИТЬ​ а если ячейка​Текст, заключенный в кавычки​и нажмите кнопку встроенный​ 100 и 200,​ оригинал (на английском​ отображается Добавление и​ если нужно суммировать​ Выделите пустую ячейку​Примечание:​ ячейках) находятся числа. ​ нужных ячеек.​ — 16 часов​6:45​

​ — использовать кнопке​СЦЕП​Примеры различных на рисунке​. Несмотря на​ содержит отрицательное значение,​ (включая пробел) отображается​ формат, который похож​ чтобы они отображались​​ языке) .​​ вычитание значений времени.​​ значения с одним​​ непосредственно под столбцом​​Мы стараемся как​​ И их надо​например: =A2+A3+A4​ 15 минут.​и введите в ячейку​ «​Функция ТЕКСТ​ ниже.​ то, что функция​

​ после него будет​

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

Добавление на основе условий

• ​ можно оперативнее обеспечивать​ сложить. Тогда формула​Геннадий гривин​Сложение и вычитание значений​ B3,​Автосумма​Функция TEXTJOIN​

• ​Внимательно посмотрите на использование​СЦЕПИТЬ​ показан текст «(недостаток)».​ числом. В этом​ вы хотите. ​ 15 # продукт,​ предложение грамматически правильные​ можно просмотреть даты​

Сложение или вычитание дат

​ необходимо для суммирования​ «​ вас актуальными справочными​ в ячейки В1​: Выделить столбик с​ времени​9:30​» на вкладке​Примечание:​

Сложение и вычитание значений времени

​ функции​по-прежнему доступен для​При объединении чисел и​ коде «0» обозначает​В поле​ продукт #100 и​ из нескольких столбцов​ и операций со​ определенного продукта total​

support.office.com>

Объединение текста и чисел

​формулы​​ материалами на вашем​ выглядит так​ данными и еще​Создание и удаление пользовательских​.​ «​ Мы стараемся как можно​текст​ обеспечения обратной совместимости,​ текста в ячейке,​ число, которое содержится​Тип​ 200 # продукта.​ данных для подготовки​ временем.​ sales.​» нажмите кнопку​ языке. Эта страница​=СУММ (A:A)​ одну ячейку ниже​ числовых форматов​

​В ячейку B4 введите​ГЛАВНАЯ​ оперативнее обеспечивать вас​во втором примере​ следует использовать​ текст и больше​ в ячейке (например,​измените коды числовых​Использование пользовательского числового формата​ массовой рассылки. Или,​Примечание:​При необходимости суммирование значений​Автосумма​ переведена автоматически, поэтому​

Используйте числовой формат для отображения текста до или после числа в ячейке

​Тут же, в​ последней с данными…​Функция ВРЕМЯ​формулу = B2 +​». Можно также​ актуальными справочными материалами​ на рисунке. При​ОБЪЕДИНЕНИЯ​ не функция числа​ 12).​ форматов в формате,​ для отображения номера​ возможно, вам нужно​Мы стараемся как​ с помощью нескольких​>​

​ ее текст может​ вопросе присутствует: подвести​Staha​Золотая рыбка​ B3​ добавить единицы времени,​ на вашем языке.​ присоединении к числа​

​, так как​

1. ​ становятся как числовые​12:00 как 12:00 центральноевропейское​

2. ​ который вы хотите​​ с текстом, не​​ форматирование чисел с​​ можно оперативнее обеспечивать​​ условий, используйте функцию​

3. ​Сумма​​ содержать неточности и​​ итог столбика… Это​​: нажимаешь функции сумма​​: у тебя на​и нажмите клавишу​ например 1½ часов​ Эта страница переведена​

4. ​ в строку текста​​функции СЦЕПИТЬ​​ значения. Это означает,​ время​ создать.​ изменяя порядок сортировки​

   ​ текстом, не затрагивая​ вас актуальными справочными​ СУММЕСЛИМН . Например​. Excel автоматически будут​ грамматические ошибки. Для​ как? Ведь Итого​ и выделаешь промежуток…​ экране есть вот​
   ​ ВВОД.​​ время дня, например​ автоматически, поэтому ее​ с помощью оператор​

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

​ формате даты/времени ч:мм​ заключите текст в​ номера без изменения​ В Excel существует​ переведена автоматически, поэтому​ продукта в рамках​ также можно работать​ вам полезна. Просим​ в ячейках (диапазон)​ есть какой-то опред​

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

​ них.​​ AM/PM, а текст​

• ​ двойные кавычки (»​ значения.​ несколько способов для​​ ее текст может​​ определенной области продаж.​​ по горизонтали при​​ вас уделить пару​ — тогда формулы​​ порядок. .. берёшь за​​щелкни курсором под​Можно также добавить времени​.​​ важно, чтобы эта​​ способом отображения чисел.​​Объединение текста из​​Для объединения чисел с​ «московское время» отображается​ «), или чисел​

• ​Выполните следующие действия.​​ объединения текста и​ содержать неточности и​Общие сведения о том,​ выборе пустую ячейку​ секунд и сообщить,​ СУММ (), Автомсумма,​ нижний правый угол​ столбиком, который надо​ с помощью​Сложение значений времени​​ статья была вам​​ В формуле используется​ нескольких диапазонах и/или​ помощью функции СЦЕПИТЬ​

Примеры

​ после времени.​ с помощью обратной​

​Выделите ячейки, формат которых​ чисел.​​ грамматические ошибки. Для​​ как сложение и​ справа от ячейки,​ помогла ли она​ или шлепать знаком​ и тянешь в​ сложить а потом​​функции «Автосумма»​​Допустим, вам нужно узнать,​ полезна. Просим вас​ базовое значение из​ строки, а также​ или функции ОБЪЕДИНЕНИЯ,​-12 как -12р. дефицит​ косой черты (\)​ требуется изменить.​Если столбец, который вы​ нас важно, чтобы​ вычитание дат можно​ чтобы суммировать.)​​ вам, с помощью​​ + (плюс) по​

См. также

support.office.com>

Сложение значений времени

​ уделить пару секунд​​ ячейки, на который​ разделитель, указанный между​ текст и TEXTJOIN​ и 12 как​ в начале.​На вкладке​ хотите отсортировать содержит​ эта статья была​ найти Добавление и​»Сумма».» />​ кнопок внизу страницы.​ ячейкам. Или можно​ какую надо)​ этому значку. машина​Щелкните ячейку B4. Затем​ минут потребует выполнение​ и сообщить, помогла​ указывает ссылка (в​ каждой парой значений,​ и амперсанд (&)​

​ 12р. избыток​Примечание:​

​Главная​ числа и текст​ вам полезна. Просим​​ вычитание дат. Более​​Автосумма создает формулу для​ Для удобства также​​ подвести Промежуточные итоги.​​Добрыйборисычъ​ сама выделит столбик​ на вкладке​ двух задач. По​ ли она вам,​ данном примере. 4) —​​ который будет добавляться​​ оператор.​

​0.00р. «избыток»;-0.00р. «дефицит»​

​изменение встроенного формата​в группе​ — 15 #​ вас уделить пару​ сложные вычисления с​ вас, таким образом,​ приводим ссылку на​Но мне нравится​: Формулу вставить, например​ и покажет итог​ГЛАВНАЯ​

1. ​ вашей оценке на​​ с помощью кнопок​​ не форматированное значение,​ текст. Если разделитель​​Примечания:​​Это значение отображается в​

2. ​ не приводит к​​число​ продукта, продукт #100​​ секунд и сообщить,​ датами, читайте в​
   ​ чтобы вас не​ оригинал (на английском​ средняя часть вопроса​

​ =СУММ (A1:A4) -​Сергей басов​​нажмите кнопку​​ выполнение первой задачи​

​ внизу страницы. Для​ отображаемое в ячейке​​ пустую текстовую строку,​​ ​​ формате «Денежный». Кроме​​ удалению формат.​щелкните стрелку.​​ и 200 #​​ помогла ли она​ статье даты и​ требуется вводить текст. ​ языке) .​

Хотите узнать больше?

​ : как сложить​ сложит столбец А​

​: складывайте ячейки вертикально,​Автосумма​

​ потребуется 6 часов​

support.office.com>

EXCEL срочно, помогите!!! как сложить столбец в excele? Не сторки, а подвести итог столбика…

​ удобства также приводим​​ (40%). Чтобы восстановить​ эта функция будет​В Excel 2016Excel Mobile и​ того, если в​Для отображения​
​В списке​ продукта — не​ вам, с помощью​ операций со временем.​ Однако при желании​Один быстрый и простой​ столбец в excele?​

​ строки 1-4 в​​ а не горизонтально)​. Формула будет выглядеть​ 45 минут, а​ ссылку на оригинал​ форматов чисел используйте​ эффективно объединять диапазоны.​ Excel Online с​
​ ячейке находится положительное​

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

​ введите формулу самостоятельно​​ для добавления значений​ Ответ: лучше складывать​ данном примере​ ) в нужной​ следующим образом:​ на выполнение второй​ (на английском языке). ​ функцию​TEXTJOIN​ помощью функции​

​ значение (или 0),​​Принцип действия​выберите категорию, например​ образом. Можно отформатировать​ Для удобства также​ как сложение и​

​ просматривать функцию сумм.​​ в Excel всего​ пополам, по вдоль​Пётр московских​ ячейке поставьте =​=SUM(B2:B3)​ задачи — 9 часов​Проверьте, как это​
​текст​
​в Excel 2013​ОБЪЕДИНЕНИЯ​ после него будет​12 как Продукт №12​настраиваемые​ ячейки, содержащие 15,​ приводим ссылку на​ вычитание значений времени​Используйте функцию СУММЕСЛИ ,​ воспользоваться функцией Автосумма.​ длинно получается.​: Пусть в столбце​ и через +​
​. Нажмите клавишу ВВОД,​ 30 минут.​ работает! ​.​ и более ранние​заменена​ показан текст «(излишек)»,​

​»Продукт № » 0​

• Excel 2010 сброс настроек по умолчанию
• Excel 2013 сбросить настройки
• Excel word слияние
• Как в excel сделать перенос в ячейке
• Excel время перевести в число
• Excel вторая ось на графике
• Как в excel сделать ячейку с выбором
• Excel где хранятся макросы
• Как поменять формат ячейки в excel на числовой
• Как в excel поставить черточку
• Excel деление с остатком
• Excel добавить в ячейку символ

Как объединить ячейки, столбцы и строки в Excel

При работе с данными в Excel принято объединять ячейки, столбцы и строки для объединения чисел или текста.

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

Содержание

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

Как объединить ячейки в Excel

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

1. Вы можете создать текст заголовка внутри любой из ячеек, которые вы хотите объединить. Отформатируйте текст так, как вам нужно, включая размер шрифта, стиль, вертикальное выравнивание или высоту.
2. Далее вам необходимо убедиться, что текст идеально выровнен по ширине таблицы данных. Для этого выберите все ячейки, которые вы хотите объединить и центрировать.
3. Выбрав все ячейки, которые вы хотите объединить, в меню Главная выберите Объединить и центрировать . Это объединит все выбранные ячейки в одну ячейку и отцентрирует текст или данные в этой ячейке.

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

Для этого:

1. Введите название месяца в первую ячейку.
2. Затем выберите все релевантные ячейки. Таким образом, в этом случае январь будет включать первую и вторую ячейки.
3. Теперь, когда выбраны эти ячейки, в меню Home выберите стрелку раскрывающегося списка рядом с Merge & Center . Выберите Объединить ячейки в раскрывающемся меню.

Это объединит эти ячейки в Excel в одну и поместит текст внизу ячейки по умолчанию. Причина, по которой вы не хотите использовать Merge & Center в этом случае, заключается в том, что это будет центрировать текст по горизонтали в столбце.

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

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

Как объединить столбцы в Excel

В Excel вы не ограничены объединением отдельных ячеек. Вы также можете объединить целые столбцы данных.

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

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

Это вообще бесполезно.

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

Объединить столбцы с помощью блокнота

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

1. Скопируйте все ячейки из двух столбцов, которые вы хотите объединить, и вставьте их в Блокнот. Перейдите в конец списка и нажмите Tab на клавиатуре. Выделите эту вкладку и скопируйте ее с помощью Ctrl-C. Вам это понадобится позже. Затем выделите весь текст.
2. Выберите меню Редактировать и выберите Заменить .
3. В поле Find what нажмите Ctrl-V , чтобы вставить символ табуляции. В поле Заменить на нажмите пробел. Это заменит все вкладки в документе одним пробелом.
4. Теперь выберите Заменить все , и весь текст в документе «Блокнот» должен выглядеть нормально, с именем и фамилией, разделенными одним пробелом.
5. Выделите весь текст в документе и нажмите Ctrl-C для копирования. Вернитесь на свой лист и вставьте с помощью Ctrl-V в верхнюю часть первого столбца, из которого вы изначально скопировали.
6. Наконец, просто переименуйте первый столбец и удалите второй. Теперь в вашей электронной таблице два столбца объединены в один.

Это не самый сложный способ объединения столбцов в Excel, но он работает и очень прост.

Объединить столбцы в Excel с помощью объединения

Если вы хотите немного усложнить процесс и сэкономить несколько шагов, вы можете использовать функцию СЦЕПИТЬ в Excel. Эта функция работает так же, как функция объединения в Google Таблицах.

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

1. Для этого щелкните правой кнопкой мыши столбец справа от двух, которые вы хотите объединить, и выберите Вставить . Это вставит новый пустой столбец.
2. В первой пустой ячейке вверху этого нового пустого столбца введите =СЦЕПИТЬ(C3″, «,D3) . Замените C3 и D3 любыми ячейками, содержащими ваши данные. « » в середине добавляет пробел между текстом из двух ячеек. Когда вы нажмете ввод, вы увидите данные из двух ячеек, объединенных пробелом между ними.
3. Наконец, чтобы скопировать эту функцию вниз по всему столбцу и объединить данные из обоих столбцов, удерживайте нажатой Shift на клавиатуре и наведите указатель мыши на правый нижний угол первой ячейки, пока значок мыши не изменится на две горизонтальные линии. Затем дважды щелкните левой кнопкой мыши.

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

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

Как только вы это сделаете, вы можете удалить два старых столбца, и ваша электронная таблица будет готова, и два столбца будут объединены в один.

Как объединить строки в Excel

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

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

Объединить строки с помощью Concatenate

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

1. Поместите курсор туда, куда вы хотите поместить данные из нескольких строк. Например, вы можете поместить курсор на новый лист. Затем введите = СЦЕПИТЬ (Лист1!C3»,»,Лист1!C4) . Замените C3 и C4, выбрав фактические ячейки на другом листе, которые вы хотите объединить.
2. Когда вы нажмете Enter, вы увидите объединенные данные, разделенные запятыми, в новой строке. Чтобы объединить больше строк, просто скопируйте и вставьте первую ячейку во вторую новую строку и отредактируйте формулу, чтобы включить строки из исходного листа, которые вы хотите объединить.

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

Объединить строки в Excel с помощью слияния и центрирования

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

1. Выделите обе строки, которые вы хотите объединить.
2. В меню «Главная» выберите «Объединить и центрировать» на ленте.

Как видите, это объединяет две строки, содержащие одни и те же данные, в одну строку, содержащую один из этих дубликатов.

Это разумный способ очистить электронные таблицы и уменьшить количество дубликатов в наборе данных.

Вот несколько самых быстрых советов по объединению ячеек, столбцов и строк в Excel. Вы знаете какие-нибудь другие? Поделитесь ими в разделе комментариев ниже!

Райан с 2007 года пишет в Интернете статьи с практическими рекомендациями и другие статьи о технологиях. Он имеет степень бакалавра наук в области электротехники, 13 лет проработал в области автоматизации, 5 лет в ИТ, а сейчас работает инженером по приложениям. Прочитать полную биографию Райана

Подписывайтесь на YouTube!

Вам понравился этот совет? Если это так, загляните на наш канал YouTube на нашем родственном сайте Online Tech Tips. Мы охватываем Windows, Mac, программное обеспечение и приложения, а также предлагаем множество советов по устранению неполадок и обучающих видеороликов. Нажмите на кнопку ниже, чтобы подписаться!

Подписаться

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

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

Объединение строк в Excel — одна из самых распространенных задач, которую всем нам приходится выполнять время от времени. Проблема в том, что Microsoft Excel не предоставляет для этого надежного инструмента. Например, если вы попытаетесь объединить две или более строк с помощью встроенной кнопки Merge & Center , вы получите следующее сообщение об ошибке:

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

При нажатии кнопки «ОК» ячейки объединятся, но сохранится только значение первой ячейки, все остальные данные исчезнут. Итак, очевидно, нам нужно лучшее решение. В этой статье описывается несколько методов, позволяющих объединить несколько строк в Excel без потери данных.

Как объединить строки в Excel без потери данных

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

Есть два способа добиться желаемого результата:

• Объединить строки Excel с помощью формулы
• Объединение нескольких строк с помощью надстройки «Объединить ячейки»

Объединить несколько строк с помощью формул

Чтобы объединить значения из нескольких ячеек в одну, вы можете использовать либо функцию СЦЕПИТЬ, либо оператор конкатенации (&). В Excel 2016 и более поздних версиях вы также можете использовать функцию СЦЕП. В любом случае вы предоставляете ячейки в качестве ссылок и вводите нужные разделители между ними.

Объединить строки и разделить значения запятой и пробелом :

= СЦЕПИТЬ (A1,", ",A2,", ",A3)

=A1&", "&A2&", "&A3

Объединить строки с пробелами между данными:

= СЦЕПИТЬ (A1, "", A2," ", A3)

=A1&" "&A2&" "&A3

Объединить строки и разделить значения запятыми без пробелов :

= СЦЕПИТЬ (A1, A2, A3)

=A1&","&A2&","&A3

На практике вам часто может понадобиться объединить больше ячеек, поэтому ваша реальная формула, вероятно, будет немного длиннее:

= СЦЕПИТЬ(A1,", ",A2,", ",A3,", ",A4,", ",A5,", ",A6,", ",A7,", ",A8)

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

Объединение строк в Excel с помощью надстройки «Объединить ячейки»

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

Чтобы объединить две или более строк в одну, вам нужно сделать следующее:

1. Выберите диапазон ячеек, в котором вы хотите объединить строки.
2. Перейдите на вкладку Ablebits Data > группу Объединить , щелкните стрелку Объединить ячейки , а затем щелкните Объединить строки в одну .
3. Откроется диалоговое окно Merge Cells с предварительно выбранными настройками, которые в большинстве случаев работают нормально. В этом примере мы только меняем разделитель с пробела по умолчанию на разрыва строки , как показано на снимке экрана ниже:
4. Нажмите кнопку Merge и посмотрите на идеально объединенные строки данных, разделенные разрывами строк:

Как объединить повторяющиеся строки в одну (с сохранением только уникальных значений)

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

На приведенном ниже снимке экрана показано, чего мы пытаемся достичь.

Перспектива поиска и объединения повторяющихся строк вручную — это то, чего вам определенно следует избегать. Познакомьтесь с надстройкой Merge Duplicates, которая превращает эту трудоемкую и громоздкую рутинную работу в быстрый процесс, состоящий из 4 шагов.

1. Выберите повторяющиеся строки, которые вы хотите объединить, и запустите мастер объединения дубликатов, нажав соответствующую кнопку на ленте.
2. Убедитесь, что ваша таблица выбрана правильно, и нажмите Следующий . Целесообразно держать отмеченным параметр Создать резервную копию , особенно если вы используете надстройку впервые.
3. Выберите ключевой столбец , чтобы проверить наличие дубликатов. В этом примере мы выбираем столбец Customer , потому что мы хотим объединить строки на основе имени клиента.

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

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

   Две дополнительные опции в верхней части окна позволяют:

   • Удалить повторяющиеся значения при объединении строк
   • Пропускать пустые ячейки

   По завершении нажмите кнопку Готово .

Через мгновение все данные из повторяющихся строк объединяются в одну строку:

Как многократно объединять блоки строк в одну строку

Задача: у вас есть файл Excel с информацией о последних заказах и каждый заказ занимает 3 строки: название товара, имя клиента и дата покупки. Вы хотите объединить каждые три строки в одну, т.е. повторно объединить блоки из трех строк.

На следующем изображении показано, что мы ищем:

Если нужно объединить только несколько записей, вы можете выбрать каждые 3 строки и объединить каждый блок по отдельности с помощью надстройки «Объединить ячейки». Но если ваш рабочий лист содержит сотни или тысячи записей, вам понадобится более быстрый способ:

1. Добавьте на лист вспомогательный столбец, в нашем примере столбец C. Назовем его BlockID или как угодно.
2. Вставьте следующую формулу в ячейку C2, а затем скопируйте ее вниз по столбцу, перетащив маркер заполнения:

   =ЦЕЛОЕ((СТРОКА(C2)-2)/3)

   Где:

   • C2 — это самая верхняя ячейка, в которую вы вводите формулу

   .

   • 2 — это строка, с которой начинаются данные

   .

   • 3 — количество строк, которые нужно объединить в каждом блоке

   Эта формула добавляет уникальный номер к каждому блоку строк, как показано на скриншоте:

   Как работает эта формула: Функция СТРОКА извлекает номер строки ячейки формулы, из которого вы вычитаете номер строки где начинаются ваши данные, чтобы формула начинала отсчет с нуля. Например, наши данные начинаются со строки 2 ^и , поэтому мы вычитаем 2. Если ваши данные начинаются, скажем, со строки 5, тогда у вас будет ROW(C5)-5. После этого вы делите приведенное выше уравнение на количество строк, которые нужно объединить, и используете функцию INT для округления результата до ближайшего целого числа.

3. Что ж, основную часть работы вы сделали. Теперь вам просто нужно объединить строки на основе BlockID . Для этого мы будем использовать уже знакомый мастер Merge Duplicates , который мы использовали для объединения повторяющихся строк:
   • На шаге 2 выберите BlockID в качестве ключевого столбца.
   • На шаге 3 выберите все столбцы, которые вы хотите объединить, и выберите разрыв строки в качестве разделителя.

   Через мгновение вы получите желаемый результат:

4. Удалите столбец Block ID , так как он вам больше не нужен, и готово! Самое смешное, что нам снова понадобилось 4 шага, как и в двух предыдущих примерах 🙂

Как объединить совпадающие строки из 2 таблиц Excel без копирования/вставки

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

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

В этом случае надстройка Merge Two Tables будет работать с удовольствием:

1. Выберите любую ячейку в вашей основной таблице и нажмите кнопку Merge Two Tables на вкладке Ablebits Data в Объединить group:

   Это запустит надстройку с предварительно выбранной основной таблицей, поэтому на первом шаге мастера просто щелкните Next .

2. Выберите вторую таблицу, т. е. таблицу поиска, содержащую совпадающие строки.
3. Выберите один или несколько столбцов, существующих в обеих таблицах. Ключевые столбцы должны содержать только уникальные значения, например Product ID в нашем примере.
4. При необходимости выберите столбцы для обновления в основной таблице. В нашем случае таких столбцов нет, поэтому просто нажимаем Следующий .
5. Выберите столбцы для добавления в основную таблицу, в нашем случае Продажи за февраль .
6. На последнем этапе вы можете выбрать дополнительные параметры в зависимости от того, как именно вы хотите объединить данные, и нажать кнопку Готово . На скриншоте ниже показаны настройки по умолчанию, которые нам подходят:

.

Дайте надстройке несколько секунд для обработки и просмотрите результат:

Как я могу получить эти инструменты слияния для Excel?

Все надстройки, описанные в этом руководстве, а также более 70 других инструментов для экономии времени включены в наш Ultimate Suite for Excel.