Теми статей

Обрати теми

Податок на прибуток ПДВ РРО ЄСВ ФОП Звільнення Штрафи ПДФО Відпустка Єдиний податок Лікарняні Всі теми

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. Відкриваємо документ, як наведено на рис. 5, переходимо в меню «Разработчик».

Важливо! Якщо вкладка «Разработчик» на вашій версії 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. Повертаємося до документа, як на рис. 5. Виділяємо комірки «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()