Как найти объединенные ячейки в экселе: Поиск объединенных ячеек — Служба поддержки Майкрософт

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

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

Формат ячеек

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

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

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

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

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

Бывает, что в файле уже есть объединенные ячейки и они мешают нормальной работе. Например, в отчете из 1С или при работе с чужим файлом Excel. Тогда их нужно как-то быстро найти и отменить объединение. Как это быстро сделать? Выполните следующие шаги.

1. Вызовите команду поиска Главная (вкладка) → Редактирование (группа) → Найти и выделить → Найти. Или нажмите комбинацию горячих клавиш Ctrl + F.
2. Убедитесь, что в поле Найти пусто.
3. Нажмите кнопку Параметры.
4. Перейдите в Формат, в окне формата выберите вкладку Выравнивание и поставьте флажок объединение ячеек.
5. ОК.
6. В окне Найти и заменить нажмите Найти все.
7. Появятся адреса всех объединенных ячеек. Их можно выбрать по отдельности, или все сразу нажав Ctrl + A.

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

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

1. Выделите все ячейки на листе. Можно щелкнуть на треугольнике, расположенном на пересечении заголовков строк и столбцов, либо горячей комбинацией Ctrl + A (один или два раза).
2. Повторно примените команду Главная (вкладка) → Выравнивание (группа) → Объединить и поместить в центре.

Все объединенные ячейки снова станут нормальными. Если что-то пойдет не так, отмените последнее действие.

Горячие клавиши объединения ячеек в Excel

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

Затем буквы появятся под каждой командой или выпадающим списком команд. Таким образом можно определить последовательность клавиш вызова нужной команды. Для объединения и центрирования ячеек в моей версии Excel необходимо последовательно нажать Alt – Я – Щ – Б. Это был первый способ.

Второй способ. Любым способом объединить ячейки и повторно вызвать команду клавишей F4 (повтор последнего действия).

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

Вы попадаете в настройки панели быстрого доступа. Найдите команду Объединить и поместить в центре и добавьте ее в правое окно.

Кнопка появится на панели быстрого доступа.

После нажатия Alt под этой кнопкой появится цифра, зависящая от порядкового номера в ряду.

Теперь есть еще одна горячая клавиша объединить ячейки в Excel – Alt + 5.

Выравнивание по центру (без объединения ячеек)

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

1. Поместите надпись в левую ячейку строки, где должно произойти выравнивание.
2. Выделите вправо нужное количество ячеек.
3. Вызываете команду Правая кнопка мыши → Формат ячеек → Выравнивание → по горизонтали → по центру выделения.

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

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

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

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

Объединение ячеек с помощью & (амперсанд) и функции Excel СЦЕПИТЬ (CONCATENATE)

Объединение содержимого ячеек – очень распространенная задача. Выбор решения зависит от типа данных и их количества.
Если нужно сцепить несколько ячеек, то подойдет оператор & (амперсанд).

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

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

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

Правильное соединение текста и числа.

Соединение текста и даты.

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

Функция Excel СЦЕП (CONCAT)

В Excel 2016 на смену функции СЦЕПИТЬ пришла функция СЦЕП. Она работает так же, только в качестве аргумента можно задать целый диапазон.

Все хорошо, но без пробелов. Если требуется соединить с разделителем, СЦЕП не сильно поможет. Поможет другая новая функция для объединения ячеек.

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

Функция ОБЪЕДИНИТЬ также появилась в Excel 2016 и решила сразу все проблемы склеивания ячеек: указание целого диапазона, вставка разделителя и даже пропуск пустых ячеек в диапазоне, чтобы не дублировать разделитель.

Синтаксис функции.

ОБЪЕДИНИТЬ(разделитель;пропускатьпустые;текст1;…)

разделитель – разделитель, который вставляется между ячейками

пропускать_пустые – если 0, то пустые ячейки включаются, если 1 – игнорируются. Обычно задают 1, чтобы не дублировать разделитель.

текст1;… – ссылка на диапазон или отдельные ячейки для сцепления.

Функция Excel ОБЪЕДИНИТЬ – лучшее решение для склеивания ячеек.

Заполнить – Выровнять

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

Выполните следующие действия.

1. Выделите столбец с данными.
2. Расширьте выделение вправо настолько, сколько предположительно должен занять объединенный текст или больше.
3. Вызовите команду Главная (вкладка) -> Редактирование (группа) -> Заполнить -> Выровнять.

Текст будет помещен в одну ячейку через пробел.

Никаких формул.

Предупреждение.

• Если в ячейках есть числа, даты или формулы, фокус не получится. Работает только с текстом.
• Общая длина текста не должна превышать 255 символов. Лишнее будет перенесено на вторую строку автоматически (см. ролик ниже).

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

↓ Скачать файл с примером ↓

Поделиться в социальных сетях:

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

В программе Excel присутствует кнопка для разъединения объединенных ячеек таблицы на закладке: «ГЛАВНАЯ»-«Выравнивание»-«Отменить объединение ячеек». Но что, если эту операцию нужно выполнять многократно, да еще и после нее заполнять данными ново созданные ячейки. Реализовать данную задачу вручную – это весьма затратное занятие по времени и силам. Здесь рационально воспользоваться макросом.

Макрос для разъединения объединенных ячеек в Excel

Допустим у нас уже имеется вполне читабельная таблица списка заказов, в которой имеются объединенные ячейки в столбце «Год». Пример, такой таблицы изображен ниже на рисунке:

Но нам необходимо преобразовать данную таблицу в стандартный формат, например, для создания отчета на основе сводной таблицы. Для этого откроем редактор Visual Basic (ALT+F11):

И вставим новый стандартный модуль используя инструмент в редакторе: «Insert»-«Module». А после чего запишем в модуль VBA код макроса для разъединения объединенных ячеек:

Sub RazdelitVstavit()
Dim adres As String
adres = ActiveCell.MergeArea.Address
If adres <> ActiveCell.Address Then
  ActiveCell. UnMerge
  ActiveCell.Copy
  ActiveSheet.Paste ActiveSheet.Range(adres)
  Application.CutCopyMode = False
End If
End Sub


Если мы хотим отменить объединение ячеек в столбце «Год» и заполнить созданные ячейки соответствующими значениями (годами), тогда перейдите на одну большую объединенную ячейку B2 и запустите макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«RazdelitVstavit»-«Выполнить».

В данном VBA коде макроса используется только одна переменная. Она хранит в себе адрес диапазона ячеек B2:B15 которые охватывает одна активная объединенная ячейка.

adres = ActiveCell.MergeArea.Address

Адрес активной ячейки отображается в поле «Имя» (напротив строки формул Excel). Но там не отображается полный адрес объединенной ячейки.

Для пользователя в поле «Имя» будет адрес отображаться одинаково, но в макросе их можно различить с помощью методов объекта ActiveCell.MergeArea.Addres. В зависимости какой тип активных ячеек будет возвращен тип адреса – одна ячейка или диапазон. Если активная ячейка не является объединенной, тогда в переменной будет храниться только адрес одной активной ячейки, а не целого диапазона. Далее макрос проверяет является ли текущая активная ячейка – объединенной, с помощью сравнения двух способов получения адреса для одной и той же активной ячейки. Тот способ, который передал адрес в переменную из метода объекта ActiveCell.MergeArea.Addres и обычный – ActiveCell.Addres. Если адрес в переменной и адрес получен обычным способом не совпадает, значит она является объединенной и код выполняется дальше.

С помощью метода объекта ActiveCell.UnMerge выполняется разъединение объединенной активной ячейки. Далее копируется ее содержимое и заполняется диапазон на листе, адрес которого получен из переменной, его же ранее содержала в себе объединенная активная ячейка. После копирования значения для объекта CutCopyMode устанавливается свойство False, чтобы прекратить процесс копирования. В результате таблица листа заказов будет иметь такой же вид как показано ниже на рисунке:

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

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



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

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

Dim i As Long

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

For i = 1 To Selection.Count

В конце кода не забудем добавить конец цикла:

Next

Вместо ссылки на активную ячейку Active. Cell теперь будем использовать ссылку на очередную по счету ячейку в выделенном диапазоне: Selection.(i). Полная версия усовершенствованного макроса выглядит следующим образом:

Sub RazdelitVstavit()
Dim adres As String
Dim i As Long
For i = 1 To Selection.Count
  adres = Selection(i).MergeArea.Address
  If adres <> Selection(i).Address Then
    Selection(i).UnMerge
    Selection(i).Copy
    ActiveSheet.Paste ActiveSheet.Range(adres)
    Application.CutCopyMode = False
  End If
Next
End Sub


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

Читайте также: Как объединить ячейки в Excel с помощью кода макроса VBA.

Так же стоит отметить, что выделенный диапазон может содержать необъединенные ячейки, которые будут просто игнорироваться макросом. Если бы мы не усовершенствовали наш макрос, то при выделении нескольких объединенных ячеек – разделилась бы только первая.

Как разъединить ячейки в Excel

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

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

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

Как разъединить ячейки в Excel

Разъединить ячейки в Excel очень просто. Вот что вы делаете:

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

Или щелкните стрелку раскрывающегося списка рядом с кнопкой Merge & Center и выберите Unmerge Cells .

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

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

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

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

1. Выберите весь рабочий лист. Для этого либо щелкните маленький треугольник в верхнем левом углу рабочего листа, либо нажмите сочетание клавиш Ctrl + A.
2. Выделив все ячейки листа, взгляните на кнопку Merge & Center :
   • Если он выделен, щелкните его, чтобы разъединить все объединенные ячейки на листе.
   • Если он не выделен, на листе нет объединенных ячеек.

Как разъединить ячейки и скопировать исходное значение в каждую неслитую ячейку

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

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

1. Выберите таблицу (или только столбцы с объединенными ячейками) и нажмите кнопку Merge & Center на вкладке Home . Это приведет к разделению всех объединенных ячеек, но только верхние левые необъединенные ячейки будут заполнены данными.
2. Еще раз выберите всю таблицу, перейдите на вкладку Главная > Редактирование группы , нажмите Найти и выбрать , а затем нажмите Перейти к специальному…
3. В Перейти к Спецвыпуску , отметьте опцию Blanks и нажмите OK :
4. Выделив все пустые ячейки, введите знак равенства (=) и нажмите клавишу со стрелкой вверх. Это создаст простую формулу, которая заполнит первую пустую ячейку значением из приведенной выше ячейки:

.

5. Поскольку вы хотите заполнить все необъединенные ячейки, которые в настоящее время пусты, нажмите Ctrl + Enter, чтобы ввести формулу во все выделенные ячейки.

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

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

Как разделить содержимое объединенной ячейки на несколько ячеек

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

• Текст в столбцы — позволяет разбивать текстовые строки по заданному разделителю, такому как запятая, точка с запятой или пробел, а также разделять подстроки фиксированной длины.
• Flash Fill — быстрый способ разделить относительно простые текстовые строки одного шаблона.
• Формулы для разделения текстовых строк и чисел — лучше всего использовать, когда вам нужно индивидуальное решение для определенного набора данных.
• Инструмент «Разделить текст» — инструмент, который можно попробовать, когда все вышеперечисленные методы не помогли. Он может разбивать ячейки по любому указанному символу или нескольким другим символам, по строке и маске (указанный вами шаблон).

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

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

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

Итак, как найти объединенные ячейки на листе? Просто помните, что слияние ячеек связано с выравниванием, а выравнивание является частью форматирования, а Excel Find может выполнять поиск по формату 🙂 Вот как:

1. Нажмите Ctrl + F, чтобы открыть диалоговое окно Find . Или перейдите на вкладку Главная > группу Редактирование и нажмите Найти и выбрать > Найти .
2. В диалоговом окне «Найти и заменить » нажмите кнопку Параметры , а затем нажмите Формат…
3. Перейдите на вкладку Выравнивание , установите флажок Объединить ячейки под Элемент управления текстом и нажмите OK .
4. А теперь нажмите либо:
   • Найдите «Далее» , чтобы перейти к следующей объединенной ячейке.
   • Найти все , чтобы получить список всех объединенных ячеек.

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

Совет. Если вам просто интересно, есть ли какие-либо объединенные ячейки в определенном диапазоне, выберите этот диапазон и бросьте взгляд на кнопку Merge & Center . Если кнопка выделена, это означает, что в выбранном диапазоне есть хотя бы одна объединенная ячейка.

Вот как вы разъединяете ячейки в Excel. Я благодарю вас за чтение и надеюсь снова увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

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