Темы статей

Выбрать темы

Налог на прибыль НДС РРО ЕСВ ФЛП Увольнение Штрафы НДФЛ Отпуск Единый налог Больничные Все темы

Excel 2010: как объединить ячейки

Редакция БК

Бухгалтер&Компьютер Март, 2012/№ 5

Статья

Приєднуйтесь до Telegram спільноти та дізнавайтесь найважливіші новини першими!

Поводом для написания этой статьи стало следующее письмо читателя: «Приветствую всех сотрудников «Б & К»! У меня вопрос по работе с 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», щелкаем на значке вызова мастера функций (иконка «f_x» в строке формул). Откроется окно Мастера функций, как на рис. 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. С клавиатуры вводим текст «" "» (двойная кавычка, пробел, еще одна двойная кавычка).

6. Снова вводим символ «&».

7. Щелкаем на ячейке «B2».

8. Вводим «&» и разделитель «" "» (пробел).

9. Щелкаем на «С2» и нажимаем «Enter». В результате должна получиться формула: «=A2&" "&B2&" "&C2». Копируем ее на всю высоту таблицы.

В принципе, мы получили тот же результат, и в этом смысле функция «СЦЕПИТЬ()» и операция «&» идентичны. Единственное отличие состоит в том, что при использовании «СЦЕПИТЬ()» мы не заботились о расстановке кавычек вокруг текстовых строк. Эту работу автоматически сделал Мастер функций. Хотя, на мой взгляд, формула с операцией «&» выглядит понятнее. Особенно если учесть, что в операциях объединения могут использоваться и другие функции. Например, чтобы получить в отдельной колонке фамилию сотрудника и его инициалы, придется дополнительно задействовать функцию «ЛЕВСИМВ()» — она позволяет извлечь из текста несколько начальных символов. Тогда формула с использованием операции «&» будет выглядеть так: «=A2&" "&ЛЕВСИМВ(B2;1)&"."& ЛЕВСИМВ(C2;1)&"."», а результатом ее работы будет текст «Григорьева Н.М.». В случае функции «СЦЕПИТЬ()» выражение получится таким: «=СЦЕПИТЬ(A2;" ";ЛЕВСИМВ(B2);".";ЛЕВСИМВ(C2);".")».

Как объединить данные разного типа

При объединении данных (функцией «СЦЕПИТЬ()» или с операцией «&») бывают ситуации, когда исходные данные представлены в разных форматах: числа, даты, логические выражения и т. п. В этом случае нужно помнить, что при таком объединении Excel преобразует все данные в текстовый формат. В определенных ситуациях такое преобразование будет некорректным, поэтому его лучше сделать самому при помощи встроенной функции «ТЕКСТ()».

В качестве примера я предлагаю сформировать строку из серии, номера паспорта сотрудника и даты его выдачи, воспользовавшись операцией «&». Делаем так.

1. Открываем базу данных, как на рис. 1.

2. Становимся на любую свободную ячейку внутри базы (например, на «K2»).

3. Вводим формулу «="паспорт сер. "&E2&", N "&F2&", выдан "&G2& ", "&H2».

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

With Selection

For Each rCell In .Cells

sMrgStr = sMrgStr & sDLM & rCell.Text

Next rCell

Application.DisplayAlerts = False

.Merge Across:=False

Application.DisplayAlerts = True

.Item(1).Value = Mid(sMrgStr, 1 + Len(sDLM))

End With

End Sub

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».

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

Sub MrgToOne()