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. За допомогою клавіатури вводимо текст «" "» (лапки, пробіл, ще одна лапки).

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 ()