Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Разъединение ячеек объединенных таблиц в Excel выполняется из меню HOME — Alignment — Unmerge Cells. Что если вам придется повторить этот процесс несколько раз, а после этого заполнить вновь образованные ячейки данными? Это очень трудоемкая и отнимающая много времени задача. Здесь рационально использовать макрос.

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

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

таблица списка заказов.

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

Visual Basic.

Затем вставьте стандартный модуль с помощью инструмента «Вставка»-«Модуль». Затем напишите макрокод для модуля VBA, чтобы разделить объединенные ячейки:

модуль VBA код макроса.

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

отменить объединение ячеек.

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

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

Адрес активной ячейки.

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

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

Читайте так же:
Как предотвратить изменение всей книги?

Пример.

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

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

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

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

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

For i = 1 To Selection.Count

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

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

Пример2.

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

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

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

Сначала мы объясним, что такое объединенные ячейки в Excel и как их делать. Здесь нет ничего сложного, просто выделите две или более ячеек, выберите команду Главная -> Выравнивание -> Объединить и поместите их в центр.

Команда объединения ячеек

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

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

Типы объединения ячеек

Способы объединения ячеек

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

Команды объединения ячеек

  • Объединить по строкам — позволяет выбрать диапазон ячеек, содержащий несколько строк. В этом случае Excel создаст объединенные ячейки, по одной в каждом ряду
  • Объединить ячейки — объединяет выделенные ячейки в одну ячейку, но не выравнивает текст в ячейке.
  • Объединить ячейки — разделить объединенную ячейку на несколько ячеек.
Читайте так же:
Как посчитать последовательные дубликаты в Excel?

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

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

  • Если диапазон содержит объединенные ячейки, то пользоваться сортировкой и фильтрацией в этом диапазоне будет невозможно.
  • Также невозможно будет преобразовать такой диапазон в таблицу (форматировать как таблицу).
  • Также можно забыть об автоматическом выравнивании ширины или высоты ячейки. Например если имеется объединенная ячейка A1:B1, то выравнять ширину столбца A уже не получится.
  • Если Вы пользуетесь горячими клавишами для навигации, например переходите в начало и конец таблицы путем сочетания клавиш Ctrl + стрелка вверх и вниз, то переход не удастся, и курсор будет "упираться" в объединенные ячейки.
  • Если вы выделяете столбцы (или строки) с помощью горячих клавиш Ctrl (Shift) +Пробел, то при наличии объединенных ячеек, вы не сможете выделить 1 столбец (или строку).

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

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

  1. Выберите все ячейки на листе. Это можно сделать, нажав Ctrl + A или щелкнув на черном треугольнике между заголовками строк и столбцов листа.
  2. Нажмите Главная -> Выравнивание -> Объединить и центрировать, если он выбран. Если он не выбран, это означает, что выбранный лист не содержит объединенных ячеек.

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

  1. Откройте окно Найти и заменить. Сделать это можно сочетанием клавиш Ctrl + F.
  2. Поле Найти оставьте пустым и нажмите на кнопку Параметры.
  3. Должна отобразиться кнопка Формат. Нажмите левой кнопкой мыши по ней.
    Поиск объединенных ячеек
  4. В диалоговом окне Найти формат на вкладке Выравнивание выберите опцию Объединение ячеек. Далее нажмите Ok.
    Диалоговое окноНайти формат
  5. В диалоговом окне Найти и заменить нажмите Найти далее или Найти все в зависимости от вашей дальнейшей задачи.
Читайте так же:
Как посчитать количество получасовых интервалов в Excel?

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

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

  1. Выделите диапазон ячеек, который хотите отцентрировать. При этом сам текст должен содержаться в левой верхней ячейке.
  2. Выберите команду формат ячеек или нажмите сочетание клавиш Ctrl + 1.
  3. В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.
  4. В выпадающем списке выравнивания по горизонтали выберите значение по центру выделения и нажмите OK.
    Альтернатива объединению ячеек

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

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

Объединение ячеек — несложная процедура. При объединении две и более ячейки сливаются в одну более крупную. Чтобы объединить ячейки, просто выделите их и выполните команду Главная → Выравнивание → Объединить и поместить в центре. Excel комбинирует выбранные ячейки и отображает содержимое в той, которая находится в левом верхнем углу, а содержимое выравнивается по центру объединенной ячейки.

Внешний вид рабочего листа обычно оптимизируется путем объединения ячеек. Например, на рисунке 1 показан рабочий лист с четырьмя наборами объединенных ячеек: B2:H2, I2:O2, A4:A8, A9:A13. Текст в объединенных ячейках в столбце A написан вертикально.

Рис. 1. На листе четыре множества объединенных ячеек

Рисунок 1: На листе имеется четыре набора слитых ячеек.

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

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

Другие операции, связанные с объединением ячеек

Для кнопки Объединить и разместить в центре появится выпадающее меню. Стрелка выведет на экран три дополнительные команды.

  • Объединить по строкам — позволяет выбрать диапазон, а потом создать несколько объединенных ячеек (по одной на каждую выделенную строку);
  • Объединить ячейки — действует почти как Объединить и поместить в центре, с той лишь разницей, что содержимое из верхней левой ячейки не выравнивается по центру, а сохраняет исходное выравнивание по горизонтали;
  • Отменить объединение ячеек — разбивка выделенной объединенной ячейки.
Читайте так же:
Как преобразовать дату в серийный номер в Excel?

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

На рисунке ниже показан лист с 171 ячейкой (19 строк по 9 столбцов). Вы можете переместить текст в объединенной ячейке, выбрав команду Переместить текст.

Рис. 2. Здесь 171 ячейка объединены в одну

Рисунок 2: В этом случае 171 ячейка объединена в одну

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

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

Помните об этом, и это вам поможет:

  1. Вы не можете использовать объединенные ячейки в таблице, созданной с помощью Вставка → Таблицы → Таблица. Это вполне объяснимо, поскольку информация в таблице должна быть последовательной во всех строках и столбцах. Если вы объедините ячейки, эта согласованность будет нарушена.
  2. Обычно для корректировки данных в строке или столбце можно дважды щелкнуть заголовок столбца или строки, но это невозможно, если в строке или столбце есть объединенные ячейки. Вместо этого вы должны вручную настроить ширину столбцов или высоту строк.
  3. Слившиеся клетки также могут повлиять на сортировку и фильтрацию. Это еще одна причина, по которой объединение ячеек в таблицах не допускается. Для диапазона данных, подлежащих сортировке или фильтрации, объединенные ячейки использовать не следует.
  4. Наконец, объединенные ячейки могут вызвать проблемы с макросами VBA. Например, при объединении ячеек в диапазоне A1:D1 команда VBA, подобная следующей, приведет к выделению четырех столбцов (что вряд ли соответствует замыслу программиста): Columns(«B:B»).Select .

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

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

  1. Нажмите Ctrl+F, чтобы открыть диалоговое окно Найти и заменить.
  2. Убедитесь, что в поле Найти ничего нет.
  3. Нажмите кнопку Настройки, чтобы открыть окно.
  4. Нажмите кнопку Формат, чтобы открыть диалоговое окно Найти формат с нужным форматированием.
  5. В диалоговом окне «Найти формат» выберите вкладку «Выравнивание» и установите флажок «Соединить ячейки».
  6. Нажмите OK, чтобы закрыть диалоговое окно Найти формат.
  7. В окне Найти и заменить нажмите Найти все.
Читайте так же:
Как преобразовать все заглавные буквы в только первую в выбранном диапазоне в Excel?

Excel отобразит на листе все объединенные ячейки, см. рис. 3. Щелкните на адресе в списке — объединенная ячейка станет активной.

Если команда Объединить и центрировать не выделена на шаге 3, это означает, что в рабочем листе нет объединенных ячеек. Если выполнить эту команду со всеми выделенными ячейками, все 17 179 869 184 ячейки рабочего листа будут объединены в одну.

Рис. 3. Поиск всех объединенных ячеек на рабочем листе

Рисунок 3 — Нахождение всех объединенных ячеек в рабочем листе

Какие существуют альтернативы для объединения ячеек

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

  1. Введите текст, который нужно выровнять по центру ячейки.
  2. Выберите ячейку, содержащую текст, и дополнительные ячейки рядом с ней.
  3. Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.
  4. В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.
  5. В разделе Выравнивание выберите выпадающий список Горизонтальное и укажите параметр Центр выделения.
  6. Нажмите OK, чтобы закрыть диалоговое окно Формат ячеек.

Текст выравнивается по центру в выбранном диапазоне.

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

Рис. 4. Применение надписи в качестве альтернативы изменению ячеек

Рисунок 4. Использование надписи в качестве альтернативы изменению ячеек

Чтобы добавить надпись, выполните команду Вставка → Текст → Надпись, отрисуйте надпись на листе, а затем введите текст. Пользуйтесь инструментами форматирования текста вкладки Главная, чтобы корректировать текст, а также инструментами контекстной вкладки Средства рисования → Формат, чтобы вносить изменения, например можно скрыть контур надписи.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector