Разъединение ячеек Vba Excel

Обновлено: 21.11.2024

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

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

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

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

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

Это руководство охватывает:

Сочетание клавиш для разделения ячеек

Самый быстрый способ (по крайней мере, для меня) разъединить ячейки на листе — использовать сочетание клавиш.

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

Ниже показано сочетание клавиш для разделения ячеек в Excel:

Нажимайте каждую из этих клавиш последовательно (одну за другой).

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

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

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

Отменить объединение ячеек с помощью Option на ленте

Еще один столь же быстрый способ разъединить ячейки в Excel — использовать параметр "Объединить и центрировать" на ленте.

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

Ниже приведены шаги, чтобы быстро разъединить эти ячейки в Excel:

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

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

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

Добавление параметра «Разъединить» в QAT (разъединение одним щелчком мыши)

Еще один способ быстро разделить данные о продажах на листе — добавить кнопку "Объединить и центрировать" на панель быстрого доступа (QAT).

Таким образом, вы можете выполнить работу одним щелчком мыши (поскольку панель быстрого доступа всегда видна).

Ниже приведены шаги по добавлению кнопки «Объединить и центрировать» на панель быстрого доступа:

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

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

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

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

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

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

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

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

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

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

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

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

Что делать, если вы хотите получить это значение во всех этих трех ячейках.

Вы можете легко сделать это с помощью небольшого обходного пути.

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

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

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

В выделенных пустых ячейках выделена 1 ячейка — это активная ячейка.
Нажмите клавишу со знаком равенства (=). это введет знак равенства в активной ячейке
Нажмите клавишу со стрелкой вверх. это подберет ссылку на ячейку прямо над активной ячейкой
Удерживая нажатой клавишу управления, нажмите клавишу ввода. Это позволит ввести одну и ту же формулу во все эти выбранные пустые ячейки (таким образом, чтобы каждая пустая ячейка ссылалась на ячейку над ней и выбирала значение оттуда)

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

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

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

Во-первых, вам нужно открыть редактор Visual Basic. Есть два способа сделать это. Либо нажав ALT + F11, либо выбрав вкладку «Разработчик» | Группа кодов | Visual Basic. Оба метода имеют одинаковый результат. Тогда у вас есть выбор. Вы можете создать модуль для хранения кода либо в личной книге макросов, либо в текущей книге. Какая разница?. Если вы сохраните свой код в своей книге личных макросов, он будет доступен для использования в любой из моих книг Excel. Если вы сохраните его в текущей книге, то использование будет ограничено этой книгой.

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

Подробнее о личной книге макросов (PMW)

Если вы хотите узнать больше о Excel PMW, ознакомьтесь с моими сообщениями в блоге ниже.

Запуск макроса

Нам нужно запустить макрос, вставив новый модуль. Сделайте это, выбрав книгу Personal.xlsb, а затем «Вставить модуль». Введите Sub, затем имя вашего макроса. В этом примере я назвал его Sort_Worksheets. Обратите внимание, что Excel автоматически введет конечный текст End Sub, чтобы завершить подпрограмму. Нам нужно ввести остальную часть кода между этими двумя строками.

Разъединение всех объединенных ячеек.

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

Завершение макроса.

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

Протестируйте свой код. Разъединить ячейки.

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

Часто возникает соблазн объединить несколько связанных ячеек листа в одну, соединив их вместе.

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

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

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

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

Оглавление

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

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

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

Предположим, у вас есть следующий рабочий лист:

Обратите внимание, что в этом образце таблицы много объединенных ячеек, например, ячейки A6:A7, B3:B4 и B8:B9.

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

Выделите все ячейки, нажав CTRL+A на клавиатуре или нажав кнопку Выбрать все ячейки (в верхнем левом углу рабочего листа), как показано ниже:

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

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

Как найти все объединенные ячейки с помощью функции «Найти и заменить»

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

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

Выделите все ячейки, нажав CTRL+A на клавиатуре или нажав кнопку Выбрать все ячейки.
На вкладке Главная в группе Редактирование нажмите раскрывающееся меню Найти и выбрать.

Выберите Заменить в раскрывающемся списке.

Откроется диалоговое окно Найти и заменить.
Выберите Параметры. Отобразятся дополнительные параметры Найти и заменить.

Нажмите кнопку Формат рядом с полем «Найти». Откроется диалоговое окно Найти формат.

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

Нажмите "ОК".
Теперь вы должны вернуться к диалоговому окну Найти и заменить. В этом поле нажмите «Найти все».

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

Вы можете выбрать все эти объединенные ячейки, нажав CTRL+A, или выбрать только те ячейки, которые хотите разъединить.

Нажмите кнопку Закрыть, чтобы закрыть диалоговое окно.

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

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

Отменить объединение всех ячеек в Excel с помощью VBA

Еще один способ разъединить все ячейки в Excel — использовать скрипт VBA. Вот код, который вы можете использовать:

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

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

Убедитесь, что лист, с которым вы хотите работать, является активным.
В меню Разработчик выберите Visual Basic.
Когда откроется окно VBA, нажмите Вставить->Модуль. Теперь вы можете вставить свой код. Скопируйте и вставьте приведенный выше код в окно модуля.

Запустите код, нажав F5 на клавиатуре или нажав кнопку Выполнить () на панели инструментов.

Вот результат, который вы должны получить:

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

Обычно при разделении ячеек содержимое исходной (объединенной) ячейки отображается в верхней или крайней левой ячейке не объединенной группы, как показано ниже:

Перед разъединением

После разделения

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

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

Выберите "Перейти к специальному…" в раскрывающемся списке.

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

При этом будут выбраны все пустые ячейки (созданные в результате действия "Разъединить").

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

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

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

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

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

Первый метод предполагает использование функции Excel "Объединение и центрирование", а второй – простой скрипт VBA.

Мы также показали вам, как лучше управлять ячейками, которые вы разделяете.

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

В этом посте рассказывается, как разъединить ячейки и заполнить повторяющимися значениями в Excel 2013/2016. Как разъединить несколько ячеек и скопировать содержимое каждой ранее объединенной ячейки в Excel. Как разъединить ячейки и заполнить повторяющиеся значения с помощью макроса VBA в Excel.

Разъединить ячейки и заполнить повторяющимися значениями

Предполагается, что у вас есть список данных в диапазоне A1:C4, и некоторые ячейки в вашей таблице объединены. И вы хотите разъединить ячейки в этом диапазоне и заполнить каждую ячейку исходным значением. Как этого добиться. Сначала вам нужно разъединить ячейки в выбранном диапазоне, затем выбрать все пустые ячейки с помощью функции «Перейти к специальной», затем вам нужно использовать формулу для заполнения значения в каждой пустой ячейке. Просто выполните следующие действия:

Разъединение ячеек и заполнение повторяющимися значениями с помощью макроса VBA

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

Видео: разделение ячеек и заполнение повторяющимися значениями

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

В этом посте рассказывается, как подсчитать количество ячеек, содержащих нечетные или четные числа в диапазоне ячеек, с помощью формулы в Excel 2013/2016. Как подсчитать ячейки, содержащие нечетные числа, с помощью .

В этом посте рассказывается, как подсчитать количество ячеек, содержащих отрицательные числа в диапазоне ячеек, с помощью формулы в Excel 2013/2016. Вы можете подсчитать количество отрицательных чисел в своих данных с помощью простых функций.

В этом посте рассказывается, как подсчитать количество ячеек, которые не пусты или не пусты, в ячейках заданного диапазона, используя формулу в Excel 2013/2016. Как подсчитать количество ячеек, которые не являются пустыми в определенном диапазоне .

В этом посте рассказывается, как подсчитать количество ячеек меньше определенного числового значения в ячейках заданного диапазона с помощью формулы в Excel 2013/2016. Как подсчитать ячейки, которые меньше определенного .

В этом посте рассказывается, как подсчитать количество ячеек, превышающее определенное числовое значение в ячейках заданного диапазона, с помощью формулы в Excel 2013/2016. Как подсчитать ячейки, которые больше определенного .

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

В этом посте рассказывается, как автоматически фильтровать заданный диапазон ячеек на основе определенного значения ячейки в Excel 2013/2016. Как использовать функцию автофильтра VBA для фильтрации данных на основе значения ячейки в .

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

Иногда нам нужно удалить всю строку, содержащую пустую ячейку из таблицы. Хотя мы можем удалить их, щелкнув правой кнопкой мыши индекс строки и просто «Удалить» их вручную, если они не являются смежными или таблица .

Читайте также: