Excel 2010: как объединить ячейки
Задача объединения ячеек на практике возникает довольно часто. Оформление заголовков (шапок) таблиц, подготовка реестров, списков рассылки — примеры могут быть самые разные. Но проблема остается одна и та же: взять данные из нескольких ячеек, объединить их и поместить в одну ячейку рабочего листа. Хочу обратить ваше внимание. В данном случае речь идет не о параметре форматирования, который называется « Объединение ячеек ». Наша задача заключается в объединении данных, причем данные эти могут быть разного типа. И это (в некотором смысле) усложняет задачу. Хотя на самом деле, ничего сложного здесь нет. Все, что нужно вспомнить, — это работа со встроенными функциями преобразования данных и специальные операции MS Excel. Этим мы сейчас и займемся. Но для начала определимся с таблицей для нашего примера.
Я остановил свой выбор на базе данных, фрагмент которой показан на рис. 1. Это реестр сотрудников, который я получил из программы « 1С », преобразовал в формат MS Excel и которым собираюсь воспользоваться в качестве списка рассылки. Изначально в исходной базе были такие поля. В колонках « A:C » под заголовками « Фамилия », « Имя » и « Отчество » записаны соответствующие данные для конкретного сотрудника. Далее в колонках « E:H » расположены серия, номер паспорта, кем и когда он выдан. Эти сведения тоже хранятся в отдельных столбцах рабочего листа. Начиная с колонки « J » идут другие данные о сотруднике (сумма договора, адрес, ИНН, дата рождения и т. п.). Эта информация для нас уже не принципиальна. Итак, список рассылки у меня готов. И теперь я решил проанализировать документ, в который нужно внедрить поля слияния из имеющегося реестра. Оказалось, что будет удобно сформировать две дополнительные колонки, куда записать фамилию, имя и отчество сотрудника (одной строкой) и сведения о паспортных данных (тоже в виде одной строки). Иными словами, мы должны в отдельной колонке объединить данные из столбцов « A:C », а потом выполнить такое же объединение данных для колонок « E:H ». Посмотрим, какие инструменты для решения этой задачи нам предложит Excel 2010.
Объединение ячеек через функцию «СЦЕПИТЬ()»
Самый простой способ объединить данные из нескольких ячеек — воспользоваться функцией « СЦЕПИТЬ() ». Эта функция находится в категории « Тестовые » и может содержать до 255 параметров. Каждый параметр представляет собой текстовую строку или ссылку на ячейку, где записан текст. Функция объединит данные из всех своих параметров и вернет в ячейку результат в виде одной текстовой строки. Применим функцию « СЦЕПИТЬ() » для объединения сведений о фамилии, имени и отчестве в нашей таблице. Делаем так.
1. Открываем базу данных, как на рис. 1. Добавляем колонку для будущего результата. В нашем примере — это колонка « D », назовем ее « ФИО ».
2. Становимся на ячейку « D2 », щелкаем на значке вызова мастера функций (иконка « fx » в строке формул). Откроется окно Мастера функций, как на рис. 2.
3. В списке « Категория: » выбираем вариант « Текстовые ».
4. В списке « Выберите функцию: » находим строку « СЦЕПИТЬ() » и нажимаем « ОК ». Откроется окно с параметрами функции, как на рис. 3.
5. Оставаясь в поле для параметра « Текст1 », щелкаем левой кнопкой мышки по ячейке « A2 ».
6. Переходим в окно параметра « Текст2 », вводим символ « » (пробел) — он нужен для того, чтобы отделить фамилию от имени сотрудника. В окне с параметрами функции появится дополнительной окошко с названием « Текст3 ».
7. Переходим в окно для параметра « Текст3 », щелкаем левой кнопкой на ячейке « B2 ». В окне с параметрами функции появится дополнительной окошко с названием « Текст4 ».
8. Переходим в окошко « Текст4 », вводим символ « » (пробел) — отделяем имя сотрудника от его отчества.
9. Переходим в окошко « Текст5 », щелкаем левой кнопкой на ячейке « С2 ». В результате окно с параметрами должно выглядеть, как показано на рис. 3.
10. В окне « Аргументы функции » нажимаем « ОК ».
В результате наших действий в ячейке « D2 » появится формула « =СЦЕПИТЬ(A2;" ";B2;" ";C2) », а текст в ячейке « D2 » будет выглядеть так: « Григорьева Нина Михайловна ». Остается скопировать формулу на всю высоту таблицы, и реестр в первом приближении готов.
Прежде чем сделать выводы относительно способов объединения ячеек, предлагаю посмотреть на другие методы решения этой задачи.
Объединение данных операцией «&»
Альтернативным вариантом объединения данных в ячейках является операция « & » (на большинстве клавиатур знак « & » находится на цифре « 7 »). Правила использования операции « & » точно такое же, как и при выполнении арифметических действий. То есть при написании формулы символ « & » нужно ставить в каждой «точке соединения» текстовых строк.
Важно! Если в формуле с операцией « & » используется текст, его нужно обязательно заключить в двойные кавычки.
Поясню сказанное на примере. Предположим, я хочу написать формулу, с помощью которой объединить три строки: « Бухгалтер », « & », « Компьютер ». В Excel эта формула будет выглядеть так: « ="Бухгалтер"&" & "& "Компьютер" ». Обратите внимание, что в ней первый и третий симоволы « & » — это знак операции, а второй символ « & » (выделен полужирным начертанием) — текстовая строка (операнд). Посмотрим, как применить операцию « & » для нашего примера. Делаем так.
1. Открываем базу данных, как на рис. 1.
2. Становимся на ячейку « D2 », нажимаем « = ».
3. Щелкаем на ячейке « A2 » (в строке формул должно получиться « =A2 »).
4. Печатаем символ « & » (в строке формул будет выражение « =A2& »).
5. С клавиатуры вводим текст « " " » (двойная кавычка, пробел, еще одна двойная кавычка).
7. Щелкаем на ячейке « B2 ».
9. Щелкаем на « С2 » и нажимаем « Enter ». В результате должна получиться формула: « =A2&" "&B2&" "&C2 ». Копируем ее на всю высоту таблицы.
В принципе, мы получили тот же результат, и в этом смысле функция « СЦЕПИТЬ() » и операция « & » идентичны. Единственное отличие состоит в том, что при использовании « СЦЕПИТЬ() » мы не заботились о расстановке кавычек вокруг текстовых строк. Эту работу автоматически сделал Мастер функций. Хотя, на мой взгляд, формула с операцией « & » выглядит понятнее. Особенно если учесть, что в операциях объединения могут использоваться и другие функции. Например, чтобы получить в отдельной колонке фамилию сотрудника и его инициалы, придется дополнительно задействовать функцию « ЛЕВСИМВ() » — она позволяет извлечь из текста несколько начальных символов. Тогда формула с использованием операции « & » будет выглядеть так: « =A2&" "&ЛЕВСИМВ(B2;1)&"."& ЛЕВСИМВ(C2;1)&"." », а результатом ее работы будет текст « Григорьева Н.М. ». В случае функции « СЦЕПИТЬ() » выражение получится таким: « =СЦЕПИТЬ(A2;" ";ЛЕВСИМВ(B2);".";ЛЕВСИМВ(C2);".") ».
Как объединить данные разного типа
При объединении данных (функцией « СЦЕПИТЬ() » или с операцией « & ») бывают ситуации, когда исходные данные представлены в разных форматах: числа, даты, логические выражения и т. п. В этом случае нужно помнить, что при таком объединении Excel преобразует все данные в текстовый формат. В определенных ситуациях такое преобразование будет некорректным, поэтому его лучше сделать самому при помощи встроенной функции « ТЕКСТ() ».
В качестве примера я предлагаю сформировать строку из серии, номера паспорта сотрудника и даты его выдачи, воспользовавшись операцией « & ». Делаем так.
1. Открываем базу данных, как на рис. 1.
2. Становимся на любую свободную ячейку внутри базы (например, на « K2 »).
4. Нажимаем « Enter ». В ячейке « K2 » появится текст: « паспорт сер. ММ, N 676757, выдан Киевским РО ХГУ УМВД Украины в Харьк. обл., 36511 ».
В целом все правильно, за исключением загадочного текста « 36511 » в конце итоговой строки. Такой результат — следствие преобразования даты « 17/12/1999 » в текстовый формат.
Чтобы устранить проблему, нужно в формуле заменить ссылку на ячейку « H2 » функцией « ТЕКСТ() », в которой четко определить шаблон преобразования данных. И тогда формула будет выглядеть так: « ="паспорт сер. "&E2&", N "&F2&", выдан "&G2& ", "& ТЕКСТ(H2;"ДД/ММ/ГГГГ") », а в результате мы получим строку « паспорт сер. ММ, N 676757, выдан Киевским РО ХГУ УМВД Украины в Харьк. обл., 17/12/1999 ».
Функцию « ТЕКСТ() » применяют в большинстве случаев, когда к строке нужно добавить числовое значение. Элементарный пример. Предположим, что в ячейке « A1 » записан текст « Процентная ставка ». Само значение этой ставки равно « 0,2 » и записано оно в ячейку « A2 ». Причем « A2 » отформатирована с двумя знаками после запятой. То есть на рабочем листе в « A2 » мы видим результат « 0,20 », и это именно то, что нам нужно. Если ввести формулу « =A1&": "&A2 », мы получим текст « Процентная ставка: 0,2 », что не совсем верно. Правильной будет формула « =A1& ": "& ТЕКСТ(A2;"0,00 ") », которая вернет значение « Процентная ставка: 0,20 ».
И последний момент по работе с функциями объединения текста. Иногда нужно сделать так, чтобы в определенном месте результирующего текста происходил переход на новую строку. Такая ситуация характерна, например, для оформления шапок таблицы с переносом по словам. Чтобы добиться такого эффекта в формуле объединения можно воспользоваться функцией « СИМВОЛ() ». Эта функция позволяет вставить в текст любой знак из таблицы символов системы Windows. Чтобы ввести такой символ, в параметре функции нужно указать его цифровой код. Например, код « 0151 » соответствует знаку «тире», код « 013 » означает «перевод каретки» и т. д. Для принудительного разрыва строки нам понадобится специальный символ с кодом « 010 ». И тогда формула для формирования паспортных данных может выглядеть так: « ="паспорт сер. "&E2&", N "&F2&", выдан "&СИМВОЛ(10)&G2& ", "&СИМВОЛ(10)&ТЕКСТ(H2;"ДД/ММ/ГГГГ") ». В таком варианте в первой строке будет напечатан текст « паспорт сер. ММ, N 676757, выдан », под ним — текст « Киевским РО ХГУ УМВД Украины в Харьк. обл., », и только в последней строке — дата « 17/12/1999 ».
Важно! Перенос текста при использовании функции « СИМВОЛ(10) » будет работать только в том случае, если для ячейки указан параметр форматирования « Переносить по словам ».
Чтобы установить этот параметр, сделайте так.
1. Щелкните левой кнопкой мышки на ячейке с формулой, чтобы сделать ее активной.
2. Перейдите в меню « Главная ».
3. В группе « Выравнивание » щелкните на иконке « Перенос текста » (рис. 4).
Объединение ячеек без потери текста
Такая проблема периодически возникает при форматировании документов. Особенно, если в них есть шапки со сложной, многоуровневой структурой. В общих чертах задача выглядит так. Есть несколько ячеек, в каждой из которых записан текст. Нужно выделить эти ячейки и объединить их в одну. При этом в результирующую ячейку должен попасть весь текст из исходных ячеек (до их объединения). В качестве примера я предлагаю воспользоваться таблицей, изображенной на рис. 5. Это фрагмент бланка «Налоговая накладная», а точнее — надпись в правом верхнем углу этого документа. В ней фигурируют три строки: « ЗАТВЕРДЖЕНО », « Наказ Міністерства фінансів України » и « 01.11.2011 N 1379 ». Сейчас эти строки расположены в отдельных ячейках рабочего листа (в « A1 », « A2 » и « A3 » соответственно). Мне нужно создать одну объединенную ячейку « A1:A3 » и перенести в нее весь текст « ЗАТВЕРДЖЕНО Наказ Міністерства фінансів України 01.11.2011 N 1379 », а затем оформить эту часть с переносом слов и поставить на нужное место на бланке документа.
На первый взгляд, в программе Excel 2010 есть инструмент для решения такой задачи — кнопка « Объединить и поместить в центре » (она расположена на ленте « Выравнивание », рис. 4). Попробуем воспользоваться этой возможностью. Делаем так.
1. Открываем файл с таблицей, как на рис. 5.
2. Выделяем блок ячеек « A1:A3 ».
3. Вызываем меню « Главная ».
4. В группе « Выравнивание » щелкаем на иконке « Объединить и поместить в центре ». На экране появится окно с предупреждением, что часть данных при объединении будет потеряна (рис. 6).
5. В этом окне нажимаем « ОК », результат преобразований показан на рис. 7.
Excel объединил ячейки. Но большую часть текста он при этом потерял. Сохранилось только содержимое верхней левой ячейки блока « A1:A3 ». Нас это, конечно же, не устраивает. Проблему нужно как-то решать, и стандартными средствами Excel здесь не обойтись — придется написать небольшой макрос на языке VBA (Visual Basic for Application). Ничего сложного в этом нет. Тем более что с VBA мы уже работали, причем неоднократно. Да и текст макроса, я бы сказал, получится миниатюрный. Делаем так.
1. Открываем документ, как на рис. 6, переходим в меню « Разработчик ».
Важно! Если вкладка « Разработчик » в вашей версии Excel недоступна, вызовите меню « Файл », затем « Параметры ». Перейдите в раздел « Настройка ленты ». В правой части окна найдите список « Основные вкладки » и включите галочку возле строки « Разработчик ».
2. Щелкаем на иконке « Visual Basic » (рис. 8). Откроется окно, изображенное на рис. 9.
3. Вызываем меню « Insert → Module ». В открывшееся окно вводим такой текст:
Sub MrgToOne ()
Const sDLM As String = " "
Dim rCell As Range
Dim sMrgStr As String
If TypeName(Selection) <> "Range" Then Exit Sub
For Each rCell In .Cells
sMrgStr = sMrgStr & sDLM & rCell.Text
.Item(1).Value = Mid(sMrgStr, 1 + Len(sDLM))
4. Сохраняем файл и закрываем редактор « Visual Basic ».
5. Возвращается к документу, как на рис. 6. Выделяем ячейки « A1:A3 ».
6. В меню « Разработчик » щелкаем на иконке « Макросы » (рис. 8). Откроется окно, как на рис. 10.
7. В этом окне выбираем элемент « MrgToOne » (в нашем файле это единственный макрос) и нажимаем « Выполнить ».
8. Форматируем объединенную ячейку с переносом текста по словам, результат показан на рис. 11.
В данном случае Excel объединил фрагмент рабочего листа и сохранил в нем содержимое всех ячеек исходного блока « A1:A3 ».
Для быстрого обращения к макросу « MrgToOne » можно закрепить его вызов за графическим элементов, или же создать специальную комбинацию горячих клавиш. Я советую использовать второй способ. Для этого делаем так.
1. Вызываем меню « Разработчик », щелкаем на иконке « Макросы » (рис. 8). Откроется окно, как на рис. 10.
2. Выделяем макрос « MrgToOne ».
3. Нажимаем кнопку « Параметры… ». Откроется окно « Параметры макроса », как на рис. 12.
4. В поле « Сочетание клавиш: » вводим любой символ. Главное, чтобы он не пересекался с устоявшимися комбинациями горячих клавиш MS Excel. В примере на рис. 12 это символ « m ».
5. В окне « Параметры макроса » нажимаем « ОК ». Теперь для вызова программы « MrgToOne » нужно выделить блок и нажать « Ctrl+m ».
Кстати, если слегка изменить текст макроса, он будет собирать данные в первой ячейке блока без последующего объединения ячеек: