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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Откройте окно Найти и заменить. Сделать это можно сочетанием клавиш Ctrl + F.
Поле Найти оставьте пустым и нажмите на кнопку Параметры.
Должна отобразиться кнопка Формат. Нажмите левой кнопкой мыши по ней.
В диалоговом окне Найти формат на вкладке Выравнивание выберите опцию Объединение ячеек. Далее нажмите Ok.
В диалоговом окне Найти и заменить нажмите Найти далее или Найти все в зависимости от вашей дальнейшей задачи.

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

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

Альтернатива объединению ячеек

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

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

Все способы можно скачать нажав кнопку вначале статьи.

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

Так и надо было сразу сказать, что нужен макрос.

Что должно получиться в результате?
Как именно нумеровать ячейки?

Sub НумерацияОбъединённыхЯчеек() On Error Resume Next: i = 1 Dim sh As Worksheet, cell As Range, ra As Range: Application.ScreenUpdating = False For Each sh In ActiveWorkbook.Worksheets Set ra = sh.UsedRange For Each cell In ra.Cells If cell.MergeCells And Len(cell.MergeArea.Cells(1)) = 0 Then _ cell.MergeArea.Cells(1) = i: i = i + 1 Next cell Next sh Application.ScreenUpdating = True End Sub

PS: А здесь есть макрос для окрашивания всех объединённых ячеек.

__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов

EducatedFool

Посмотреть профиль

Найти ещё сообщения от EducatedFool

Пользователь
Регистрация: 22.09.2009
Сообщений: 31

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

Программист VBA
СуперМодератор
Регистрация: 13.07.2008
Сообщений: 6,856

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

Читать:

Что такое hangouts на андроиде и для чего

Вот теперь задание точно некорректное.

На каждом листе Excel 2003 имеется 16777216 ячеек (в Excel 2007 — намного больше)
Вы уверены, что макрос должен пронумеровать ВСЕ ячейки?

Если Вам лень подробно описать задачу — то и макроса Вы не дождётесь.

EducatedFool

Посмотреть профиль

Найти ещё сообщения от EducatedFool

Пользователь
Регистрация: 22.09.2009
Сообщений: 31

Есть таблица 1000 рядков на 10 столбцов, первый идет номер по порядку, некоторые ячейки обьденены между собой, надо пронумеровать ячейки в первом столбце, по порядку. Пример прилагаю

Вложения

План закупівель БАЗОВЫЙ №2.rar (60.1 Кб, 55 просмотров)

Программист VBA
СуперМодератор
Регистрация: 13.07.2008
Сообщений: 6,856

Вот с этого и надо было начинать.

PS: Скрытые строки тоже нумеруются.

EducatedFool

Посмотреть профиль

Найти ещё сообщения от EducatedFool

Способы автоматической нумерации строк и ячеек в Excel

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

растягивание;
с помощью функции;
используя прогрессию;
используя специальную формулу.

Растягивание

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

Запустить утилиту Microsoft Excel.
Выделить первую ячейку и кликнуть по клавише «1». Таким образом пользователь задаст начало нумерации.
Теперь снова выделить область ячейки нажимая на нее левой клавишей мыши и удерживать.
Зажать кнопку на клавиатуре под названием «Ctrl». Курсор сменится с белой стрелки на черный крестик.
Теперь удерживая «Ctrl» тянуть мышку вниз на столько делений, сколько нужно пронумеровать.
Цифры появятся автоматически, когда юзер отпустит кнопку мыши.
После того, как необходимое количество строк пронумеровано, вначале нужно отпустить кнопку мыши и только потом «Ctrl». В противном случае пользователь увидит на экране строки пронумерованные только одними единицами.
Например, это будет выглядеть вот так.

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

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

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

Если же человеку нужен отступ через несколько клеток, то это можно сделать так:

Ввести в первую клетку число, отступить нужное количество ячеек и прописать следующее число ниже.
Зажав кнопку мыши на первой клетке, протянуть до следующего числа. Зажать курсор ЛКМ на зеленой точке выделенной ячейки и протянуть вниз. Получится так, как на картинке.

Excel для чайников — изменение столбцов, строк и ячеек

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

Таким способом можно продолжить нумерацию с любой цифры, например написав 22 и 23, последовательность начнется с цифры 22.

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

Функция

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

Запустить Excel.
В первой ячейке прописать цифру с которой будет начинаться нумерация.
Кликнуть по вкладке «Формулы». Найти пункт «Вставить функцию».
Протянуть курсором мыши за крестик вниз на определенное количество строк.

Есть еще один метод создания списка в Excel — использовать прогрессию.

Прогрессия

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

Вставить нумерацию ячеек методом прогрессии можно следующим образом:

Запустить программу Эксель.
Указать число с которого начнется отсчет.
Кликнуть по значку со стрелкой вниз, который находится на вкладке «Главная». Если на него навести курсор, то отобразится окно в виде списка, в котором нужно будет запустить пункт «Прогрессия».
В новом окне можно выбрать параметры прогрессии. Будет ли она арифметической или геометрической. Можно выбрать в каком направлении будет идти нумерация: по строкам или столбцам. Определить шаг последовательности проставления чисел и поставить предел, по достижении которого утилита остановит нумерацию. Можно выбрать нумерацию по годам, месяцам и рабочим дням.
Изменяя эти значения можно добиться практически любого результата.
На скрине были выбраны: расположение по строкам с шагом два и конечным результатом «10» в виде арифметической прогрессии.

Способы копирования текста и таблиц из документов Ворд в Эксель

Формула

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

Вписать цифру с которой начнется нумерация.
Кликнуть по тому полю, в которое будет записано последнее число.
Теперь пользователь должен записать формулу в эту ячейку — вписываем знак равенства «=» и нажимаем на первую ячейку. После этих действий устанавливается шаг, например «+5». Кликаем клавишу «Enter».
Выделить весь столбец, протянув курсором мышки вниз до того пункта, где должен закончиться список.
Теперь в ячейках пользователь заменяет формулы. Для этого выделяем столбец и кликаем правой кнопкой мыши по нему. Выбрать «Специальная вставка».
Откроется окно, в котором пользователь выставляет необходимые значения.
Теперь можно нажать «Ок».