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

Обновлено: 03.07.2024

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

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

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

Лучше разделить ячейки в первом столбце, но тогда вы можете задаться вопросом, как правильно отсортировать записи на листе; как сохранить пары строк вместе во время сортировки. Вы можете сделать это, поместив название вашего проекта в первую строку, а имя проекта, добавив «zz» во вторую строку. Например, если первая строка содержит «Wilburn Chemical» (название проекта), то вторая строка может содержать «Wilburn Chemicalzz». Отформатируйте ячейку второй строки так, чтобы имя не отображалось (например, белый текст на белом фоне), и тогда вы сможете успешно сортировать по своему усмотрению.

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

Макрос вставляет временный столбец, считывает элементы из первого столбца списка, добавляет номер строки, копирует его во временный столбец, разъединяет ячейки, сортирует список, удаляет временный столбец и повторно объединяет столбец A. (Одна только сортировка таблицы с объединенными ячейками требует много работы!)

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

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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

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

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

Утилита Unmerge Cell в Kutools for Excel может помочь вам быстро разъединить все объединенные ячейки в выбранном диапазоне без потери данных в Excel. Смотрите снимок экрана: Загрузите полнофункциональную 60-дневную бесплатную пробную версию Kutools for Excel прямо сейчас!

Kutools for Excel: с более чем 200 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 60 дней. Загрузите бесплатную пробную версию прямо сейчас!

Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

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

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

<р>1. Выберите список с объединенными ячейками, которые необходимо отсортировать, затем нажмите «Главная» > «Объединить и центрировать», чтобы разъединить выбранные объединенные ячейки. Смотрите скриншот:

<р>2. Теперь вы можете видеть пустые ячейки, существующие в выбранном диапазоне. Оставьте выбранным диапазон, затем нажмите «Найти и выбрать» > «Перейти к специальному» на вкладке «Главная».

<р>3. В диалоговом окне «Перейти к специальному» выберите параметр «Пробелы» и нажмите кнопку «ОК».

<р>4. Затем выбираются все пустые ячейки. Введите знак = в строке формул, а затем укажите ссылку на ячейку, расположенную над первой пустой ячейкой, а затем одновременно нажмите клавиши Ctrl + Enter.

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

<р>5. Теперь вы можете приступить к сортировке списка. И после сортировки вы можете снова объединить ту же ячейку, если вам нужно.

Сортировка данных с объединенными ячейками с помощью Kutools for Excel

Вы можете легко разъединить ячейки в выбранном диапазоне без потери каких-либо данных с помощью утилиты Unmerge Cell & Fill Value Kutools for Excel. Пожалуйста, сделайте следующее.

Перед применением Kutools for Excel сначала загрузите и установите его.

<р>1. Выберите список, который нужно отсортировать с помощью объединенных ячеек, затем нажмите Kutools > Объединить и разделить > Разъединить ячейку и заполнить значение. Смотрите скриншот:

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

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

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

Пожалуйста, выберите список с объединенными ячейками, которые нужно выбрать, затем нажмите Kutools > Выбрать > Выбрать объединенные ячейки. Затем появится диалоговое окно, чтобы сообщить вам, сколько объединенных ячеек существует в выбранном диапазоне, нажмите кнопку «ОК», после чего все объединенные ячейки будут выбраны немедленно. Смотрите скриншот:

Вкладка Office — просмотр, редактирование и управление книгами в Excel с вкладками:

Вкладка Office переносит интерфейс с вкладками, который используется в веб-браузерах, таких как Google Chrome, новые версии Internet Explorer и Firefox, в Microsoft Excel. Это будет инструмент, экономящий время и незаменимый в вашей работе. См. демо ниже:

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

Первый шаг: разделение всех объединенных ячеек

1. Выберите все ячейки на листе.

Быстрый способ сделать это — щелкнуть треугольник на пересечении заголовков строк и столбцов:

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

При нажатии этой кнопки все выделенные ячейки на листе будут объединены.

ПРИМЕЧАНИЕ. Если кнопка "Объединить и центрировать" не выделена, объединенных ячеек нет.

Второй шаг: заполнение пробелов на листе

3. Выберите диапазон с пробелами.

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

5. В диалоговом окне "Перейти к специальному" выберите параметр "Пробелы" и нажмите "ОК":

Это действие выделяет пустые ячейки в исходном выделении.

6. В строке формул введите знак равенства (=), затем адрес первой ячейки с записью в столбце (в данном примере = B9) и нажмите Ctrl+Enter:

7. Снова выберите исходный диапазон и нажмите Ctrl+C, чтобы скопировать выделение.

8. На вкладке "Главная" в группе "Буфер обмена" выберите раскрывающийся список "Вставить" и нажмите "Вставить значения", чтобы преобразовать формулы в значения:

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

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

Полезные функции Excel для работы с большими объемами данных

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

Вставить результаты в ячейку без формул

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

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

Загрузить практическую рабочую тетрадь

Загрузите это учебное пособие, чтобы тренироваться, пока читаете эту статью.

2 быстрых и простых способа сортировки объединенных ячеек в Excel

Здесь у нас есть список чисел, которые нужно отсортировать.

Попробуем отсортировать их от меньшего к большему. Для этого

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

Давайте узнаем, как решить задачу и отсортировать выбранные числа.

1. Разъединить сначала, чтобы отсортировать объединенные ячейки в Excel

1.1 Один столбец

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

Шаги: часть 1

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

В строке формул напишите =B5, ссылку на ячейку непосредственно над выделенной пустой ячейкой B6, и нажмите Ctrl + Ввод.

Шаги: часть 2

Наконец мы успешно отсортировали числа в желаемом порядке.

1.2 Несколько столбцов

Мы собираемся отсортировать набор данных с несколькими столбцами на основе объединенного столбца, т. е. столбца "Регион".

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

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

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

Теперь наш набор данных отсортирован на основе объединенного столбца.

Сортировка набора данных на основе столбца, отличного от объединенного столбца

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

Теперь нам нужно сделать следующее.

Теперь выберите столбец прибыли.
На вкладке "Главная" выберите "Сортировка и фильтр" и выберите "Сортировать от меньшего к большему".

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

Похожие чтения:

2. Использование кода VBA для сортировки объединенных ячеек в Excel

2.1 Один столбец

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

Шаги:

В окне Visual Basic выберите параметр «Модуль» на вкладке «Вставка», чтобы открыть новый модуль.

Поместите следующий код в редактор Visual Basic и нажмите F5, чтобы запустить код.

В этом коде мы использовали A1:A9 в качестве диапазона и A1 в качестве ключа для сортировки диапазона.

На выходе мы видим отсортированные неслитые ячейки в столбце A.

2.2 Несколько столбцов

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

Скопируйте и вставьте следующий код в редактор Visual Basic, чтобы отсортировать набор данных на основе столбца «Регион» в алфавитном порядке.

В этом коде мы использовали A4:E11 в качестве диапазона и A4 в качестве ключа для сортировки диапазона.

На следующем снимке экрана показан отсортированный набор данных.

Что нужно помнить

Хотя использование кода VBA отлично подходит для достижения нашей цели. Но как только код запускается, мы теряем историю. Это означает, что мы больше не можем отменить изменение.
Если мы выберем второй вариант в поле Предупреждение о сортировке, будут отсортированы только выбранные ячейки столбца, а не связанные столбцы. Вот как мы потеряем связь между ячейками набора данных. Эту опцию можно использовать в случае сортировки только одного столбца, не связанного с другими.

Заключение

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

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