Мы не можем это сделать в объединенной ячейке при копировании excel
Если вы попытаетесь объединить непустые, то Excel оставит данные только первой ячейки. С помощью надстройки можно объединять ячейки без потери их содержимого.
Описание проблемы
К сожалению, при объединении двух или более ячеек, содержащих текст, Excel сохраняет значение только первой ячейки. Данные в остальных ячейках будут удалены. Решить эту задачу стандартным способом можно предварительно объединив текст ячеек с помощью функции СЦЕПИТЬ. Однако это несколько неудобно и отнимает большое количество времени. Чтобы быстро и удобно объединять ячейки и НЕ терять данные в них, лучше пользоваться дополнением к Excel - надстройкой VBA-Excel. Это наглядно продемонстрировано на рисунке справа.
Программа позволяет объединить ячейки с сохранением данных как отдельно выбранного диапазона, так и объединять ячейки построчно или по столбцам.
Ниже подробно описан порядок действий для этих случаев.
Если необходимо объединить одну область ячеек
Если необходимо объединить сразу несколько диапазонов ячеек
Надстройка VBA-Excel сможет облегчить процесс даже в том случае, если необходимо объединить большое количество ячеек с сохранением данных (например, по строкам или столбцам).
Для этого необходимо выполнить первые 4 действия из инструкции выше и дополнительно задать тип объединения ячеек.
Функция сохранения настроек
Все выбранные опции сохраняются автоматически и при следующем запуске данной функции они будут восстановлены. Это сделано для того, чтобы сэкономить время при необходимости объединения большого количества диапазонов. Вы также можете запускать данную функцию по горячей клавише. Как это сделать смотрите в статье Создание горячей клавиши на команду.
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.
Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:
Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.
Способ 1. Абсолютные ссылки
Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:
Способ 2. Временная деактивация формул
- Выделяем диапазон с формулами (в нашем примере D2:D8)
- Жмем Ctrl+H на клавиатуре или на вкладке Главная - Найти и выделить - Заменить (Home - Find&Select - Replace)
Способ 3. Копирование через Блокнот
Этот способ существенно быстрее и проще.
Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas - Show formulas) , чтобы включить режим проверки формул - в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:
Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:
Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:
Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.
Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев - работает отлично.
Способ 4. Макрос
Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:
Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:
Есть таблица, данные которой необходимо ОТФИЛЬТРОВАТЬ И СКОПИРОВАТЬ РЕЗУЛЬТАТЫ ФИЛЬТРАЦИИ в несколько других табличных файлов.
Мне нужно перенести результаты фильтрации на отдельный лист и сохранить как файл. Или хотя бы иметь возможность скопировать в буфер для последующей вставки в другую программу только тех данных, которые остались в результате применения фильтра.
Помогите, пожалуйста, с такой проблемой. Иначе мне придется вручную находить/вырезать/вставлять нужные строки по одной-по несколько, коих в таблице примерно 2000. Каждый рабочий день. По 2-3 часа. Дурно уже от одной мысли об этом.
Прилагаю пример. Нужно отфильтровать и разнести по разным таблицам данные по содержимому первого столбца. Так, например, все строки с "1" в первом столбце - в один файл. С "2" в другой и т.д.
Буду очень признательна за помощь.
Есть таблица, данные которой необходимо ОТФИЛЬТРОВАТЬ И СКОПИРОВАТЬ РЕЗУЛЬТАТЫ ФИЛЬТРАЦИИ в несколько других табличных файлов.
Мне нужно перенести результаты фильтрации на отдельный лист и сохранить как файл. Или хотя бы иметь возможность скопировать в буфер для последующей вставки в другую программу только тех данных, которые остались в результате применения фильтра.
Помогите, пожалуйста, с такой проблемой. Иначе мне придется вручную находить/вырезать/вставлять нужные строки по одной-по несколько, коих в таблице примерно 2000. Каждый рабочий день. По 2-3 часа. Дурно уже от одной мысли об этом.
Прилагаю пример. Нужно отфильтровать и разнести по разным таблицам данные по содержимому первого столбца. Так, например, все строки с "1" в первом столбце - в один файл. С "2" в другой и т.д.
Буду очень признательна за помощь. natalyzal
Мне нужно перенести результаты фильтрации на отдельный лист и сохранить как файл. Или хотя бы иметь возможность скопировать в буфер для последующей вставки в другую программу только тех данных, которые остались в результате применения фильтра.
Помогите, пожалуйста, с такой проблемой. Иначе мне придется вручную находить/вырезать/вставлять нужные строки по одной-по несколько, коих в таблице примерно 2000. Каждый рабочий день. По 2-3 часа. Дурно уже от одной мысли об этом.
Прилагаю пример. Нужно отфильтровать и разнести по разным таблицам данные по содержимому первого столбца. Так, например, все строки с "1" в первом столбце - в один файл. С "2" в другой и т.д.
Буду очень признательна за помощь. Автор - natalyzal
Дата добавления - 08.03.2017 в 20:28
Смотрите такжеАлексей. из них нет выделены желтым200?'200px':''+(this.scrollHeight+5)+'px');">Sub Macros1()miverПосле открытия клацнулCtrl + Alt даже после того,: Общий доступ к в настройке "ОбщийОна нужна мне какнедоступна, убедитесь, что примере ячейки A1, вас актуальными справочнымиОтменить объединение ячеек. была разделена, объединениеПримечание:: а вы ее изменений свойств стандартныхStoTisteg
sheetRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row: в первое из + v как это сделано, файлу отмените и
Объединение ячеек
воздух, шеф дал вы не редактируете B1 и C1
материалами на вашемВажно: ячеек над ней. Мы стараемся как можно сначала нажмите раз кнопок форматирования.: Можно для началаsheetCols = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).ColumnAndy_NTG заполненых ячеек иПоставить отметку пропускать не получается объединить будет вам объединение
Отмена объединения ячеек
галочка "Разрешить изменять задание, а в ячейку и что объединены для создания языке. Эта страница
Выделите ячейки для объединения.
оперативнее обеспечивать вас - они объединяться,
Вопрос: кто виноват приложить файлboldCols = 1, Ну и как выбрал "Преобразовать в пустые ячейки по строкам ячеек. файл нескольким пользователям". одном из файлов
ячейки, которые вы подписи "Продажи за переведена автоматически, поэтомуЯчейку, которая не былаВыберите актуальными справочными материалами а потом еще и что делать?Pelena
Объединение ячеек
If sheetCols > нам посоветовать что-то диапазон"Готово так, чтобы уCAPATOBАлексей малышев (прим.:в данном файле хотите объединить, не месяц", чтобы описать ее текст может создана путем объединения,Объединить и поместить в на вашем языке. раз - ониk61: Здравствуйте. 1 Then дельное, не видяТаблица удалиласьВариант 2 встали в один
: Спасибо, действительно помогло: Не активирован Еxcel, используется "Фильтр") она находятся в таблице. данные во 2–7 содержать неточности и невозможно разделить. Если центре. Эта страница переведена разъединяться: Лист защищён.Там надо границуboldCols = sheetCols
перед собой ниА дальше простаяВыделить столбец А ряд с заменой =))
весь вопрос в не работает. Ужас. Совет: строках.
грамматические ошибки. Для вы ищете сведенияВажно: автоматически, поэтому ееЛибо просто ручкамиGuest
выровнять между столбцами - 1 строчки, что Вы формулаНа вкладке Данные пустых (всегда пустых)Guest этом!Маринчик
Чтобы объединить ячейки, неЩелкните первую ячейку, а нас важно, чтобы о том, как В объединенной ячейке текст может содержать - Правой кнопокой: Ну конечно же,Размер %End If написали в макросе200?'200px':''+(this.scrollHeight+5)+'px');">=СЦЕПИТЬ(A1;B1)
"Текст по столбцам" левых ячеек.: Как отменить общийCAPATOB: зачем сломала а? выравнивая данные по затем, удерживая нажатой
эта статья была распределить данные из отображается содержимое только неточности и грамматические - Формат ячеек защищена книга! Семёни
ActiveSheet.Range(Cells(1, 1), Cells(sheetRows, ?
И все получилось - - отметкаВо вложениее исходный
доступ к файлу?: Друзья, помогите объеденитьПользователь удален центру, щелкните объединенную клавишу SHIFT, щелкните вам полезна. Просим ячейки по нескольким одной ячейки (верхней ошибки. Для нас - выравниваение -
Семёныч! (это проПоказатели понижающие КТУ boldCols)).SelectAndy_NTGAndy_NTG с разделителями -- файл и файл,
Arex ячейки, вроде как: возможно стоит защита ячейку и затем
объединение ячеек
последнюю ячейку диапазона, вас уделить пару ячейкам, см. статью левой ячейки для важно, чтобы эта там галочка "объединять" себя).. Тогда всё будетSelection.Font.Bold = True: Суть не в
: Это всё очень
готово который нужно получить: На вкладке "Рецензирование". не балбес, но листа
выберите выравнивание по который хотите объединить. секунд и сообщить, Распределение содержимого ячейки языков с направлением
статья была вам прячетсяСпасибо, тема закрыта.
объединятьсяActiveSheet.Range(Cells(1, 1), Cells(sheetRows, макросе, потому что
хорошо, но уCtrl + g
(все записи вGuest не могу, кнопкаПавел левому или правому
Важно: помогла ли она
Не удаётся объединить ячейки по строкам (Формулы/Formulas)
на соседние столбцы. письма слева направо полезна. Просим васТанюшечкаБыло 4 ячейки вbob38rus sheetCols)).Select он делает те меня есть готовыйAlt + вй одну строку): У меня закрыт на панели не: это только из-за краю рядом со
Убедитесь, что только в вам, с помощьюЯчейку, полученную путем объединения, или правой верхней уделить пару секунд
: щелкаешь на объединенные 2 строки, я
: Друзья, коллеги!
Selection.Merge True
операции, которые не макрос, в которомEnterSLAVICK общий доступ. А
активна, захожу в
защиты листа может
значком одной из ячеек
кнопок внизу страницы. можно снова разделить
ячейки для языков
и сообщить, помогла
\ячейки (выделяешь их)
их объединила вС какого-то внезапногоSelection.UnMerge работают в самом сделано объединение по
Ctrl -
: Два варианта:,
ячейки всё равно
фрмат ячеек, вкладка
быть. Попробуй объединениеОбъединить и поместить в
есть данные.
Для удобства также на отдельные ячейки. с направлением письма ли она вам,
и правой кнопкой 2 ячейки в
перепугу кнопка объединенияEnd Sub файле. А вот строкам. Конечно, можногалка со сдвигом
Вариант 1
не могу объединить( объединить тоже не ячеек в другой
центре
Выберите приводим ссылку на Если вы не справа налево). Данные с помощью кнопок мыши появится и 2 строках. Теперь ячеек в лентеIricha файл я выложил, попробовать переделать под влевоВыделить столбец ВGuest активна, скрины прилогаю.
книге - если.Главная оригинал (на английском помните, где находятся в других объединенных внизу страницы. Для там выберишь снять мне надо сделать вдруг стала недоступной
: Здравствуйте! и посмотреть, что формулу, но вопросГотовоНа вкладке Данные: Спасибо за помощьзаранее благодарен. не работает, значитЕсли вы передумаете, вы> языке) . объединенные ячейки, используйте ячейках удаляются. удобства также приводим объединение
из 2 имеющихся
(Excel 2010). Недоступен
Почему то не
там не работает
открыт: почему неmiver
"Текст по столбцам"Andy_NTG
Файл удален
программа ломанулась, а всегда можете отменить
Объединить и поместить в
Объединение — соединение нескольких ячеек команду
Нажмите стрелку вниз рядом
ссылку на оригинал
Juli
Не активна функция объединения ячеек в WORD (Word)
ячеек 3. Кнопка также и вариант
активна функция объединения объединение по строкам, срабатывает объединение по: - - отметка: Есть файл. В- велик размер если нет, то
слияние, щелкнув объединенную центре в одну большую
Найти с кнопкой
(на английском языке).: ну все правильно, "Объединить и поместить с объединением ячеек ячеек в таблице можно. Вот макрос, строкам? Хотя если
Объединение ячеек недоступно
Andy_NTG с разделителями --
нём есть табличное - [ снимай защиту ячейку и. ячейку. Это отличныйдля их поиска.Объединить и поместить вНе удается разбить отдельные сначала нужно их в центр" предлагает через контекстное меню в документе Word. но он не
то же самое, Странно.
готово форматирование, которое я
МОДЕРАТОРЫZidaneОбъединить и поместить вЕсли кнопка способ создания подписи,
Примечание:
Не могу в Excel разделить объединенные ячейки.
центре ячейки, но можно объединить, а уже только все объединить. по ПКМ. С Что можно сделать? нужен в данном набрать вручную, всёЯ скачал ВашCtrl + c убираю (через "Преобразовать]: Проблема может быть
центреОбъединить и поместить в которая охватывает несколькоМы стараемся как. отобразить его как потом делить на
Как еще можно макросами работаю, но в таблице нужные случае работает text.xlsx.
Выделить столбец a в диапазон"). НоAlex_ST из-за того, что. центре столбцов. В данном
можно оперативнее обеспечиватьВыберите если бы ячейки 3 разделить ячейки? ни в одном
Многие пользователи Excel и мы в том числе не советуем использовать объединенные ячейки, так как это может привести к проблемам, которые описаны в этой статье. Однако не стоит быть столь категоричными, главное знать как их избежать и уметь пользоваться альтернативами.
Как объединить ячейки в Excel
Сначала расскажем что такое объединенные ячейки в Excel и как их сделать. Тут нет ничего сложно, достаточно выделить две или более ячеек и выбрать команду на ленте Главная -> Выравнивание -> Объединить и поместить в центре.
Важно отметить, что Excel при объединении ячеек, которые содержат значения, оставит значение только той ячейки, которая находится в верхнем левом углу выбранного диапазона. Остальные же значения будут удалены. Однако, с помощью нашей надстройки, данное недоразумение можно избежать с помощью специальной команды.
Вы можете объединять ячейки как по горизонтали, так и по вертикали. Также можно объединить области, содержащие боле одной строки и столбца.
Способы объединения ячеек
Если внимательней посмотреть на команду объединения ячеек, то можно увидеть выпадающее меню, которое в свою очередь содержит следующие дополнительные действия:
- Объединить по строкам - позволяет выбрать диапазон ячеек, который содержит несколько строк. В этом случае Excel будет создавать объединенные ячейки по одной на каждой строке
- Объединить ячейки - объединяет выделенные ячейки в одну, но при этом не выравнивает текст в ячейке.
- Отменить объединение ячеек - позволяет разбить объединенную ячейку на несколько.
Какие проблемы возникают при использовании объединенных ячеек
Как уже говорили, пользоваться объединенными ячейками стоит аккуратно, так как они ограничивают функционал Excel и могут в дальнейшем принести неприятности. Если все-таки решили пользоваться объединенными ячейками, всегда помните о следующих моментах:
- Если диапазон содержит объединенные ячейки, то пользоваться сортировкой и фильтрацией в этом диапазоне будет невозможно.
- Также невозможно будет преобразовать такой диапазон в таблицу (форматировать как таблицу).
- Также можно забыть об автоматическом выравнивании ширины или высоты ячейки. Например если имеется объединенная ячейка A1:B1, то выравнять ширину столбца A уже не получится.
- Если Вы пользуетесь горячими клавишами для навигации, например переходите в начало и конец таблицы путем сочетания клавиш Ctrl + стрелка вверх и вниз, то переход не удастся, и курсор будет "упираться" в объединенные ячейки.
- Если вы выделяете столбцы (или строки) с помощью горячих клавиш Ctrl (Shift) +Пробел, то при наличии объединенных ячеек, вы не сможете выделить 1 столбец (или строку).
Как найти все объединенные ячейки и разъединить их
Чтобы разъединить все ячейки и удалить объединенные, достаточно выполнить следующие действия:
- Выделить все ячейки на листе. Это можно сделать путем нажатия сочетания клавиш Ctrl + A или кликнуть на черный треугольник между заголовками строк и столбцов на листе.
- Нажать на команду Главная ->Выравнивание ->Объединить и поместить в центре если она выделена. Если же она не выделена, значит выбранный лист не содержит объединенных ячеек.
Если же необходимо найти все объединенные ячейки в книге, то это можно сделать через панель поиска. Для этого выполните следующие шаги:
Альтернативы использования объединенных ячеек
Как правило объединенные ячейки применяют для целей выравнивания текста. Однако мало кто знает, что текст можно выровнять и без объединения ячеек и потери функционала. Для этого можно использовать обычное форматирование ячеек:
- Выделите диапазон ячеек, который хотите отцентрировать. При этом сам текст должен содержаться в левой верхней ячейке.
- Выберите команду формат ячеек или нажмите сочетание клавиш Ctrl + 1.
- В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.
- В выпадающем списке выравнивания по горизонтали выберите значение по центру выделения и нажмите OK.
Еще один способ избежать объединения ячеек - это использовать надписи. В них также можно вставлять текст и выбирать направление текста по вертикали. Способ конечно также накладывает много ограничений, но в каких-то случаях имеет место быть.
Читайте также: