Excel 2010 як об’єднати осередки, Сайт для бухгалтерів бюджетних установ
Завдання об'єднання осередків практично виникає досить часто. Оформлення заголовків (шапок) таблиць, підготовка реєстрів, списків розсилки - приклади можуть бути різні. Але проблема залишається та сама: взяти дані з кількох осередків, об'єднати їх і помістити в одну комірку робочого листа. Хочу звернути вашу увагу. В даному випадку йдеться не про параметр форматування, який називається "Об'єднання осередків". Наше завдання полягає в об'єднанні даних, причому ці дані можуть бути різного типу. І це (у певному сенсі) ускладнює завдання. Хоча насправді нічого складного тут немає. Все, що потрібно згадати, - це робота з вбудованими функціями перетворення даних та спеціальні операції MS Excel. Цим ми зараз і займемося. Але спочатку визначимося з таблицею для нашого прикладу.
Об'єднання осередків через функцію «ЗЧЕПИТИ()»
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". Копіюємо її на всю висоту таблиці.
Як поєднати дані різного типу
При об'єднанні даних (функцією «ЗЧЕПИТИ() » або з операцією « & ») бувають ситуації, коли вихідні дані представлені в різних форматах: числа, дати, логічні вирази тощо. У цьому випадку слід пам'ятати, що при такому об'єднанні Excel перетворює всі дані на текстовий формат. У певних ситуаціях таке перетворення буде некоректним, тому його краще зробити самому за допомогою вбудованої функції "ТЕКСТ()".
Як приклад я пропоную сформувати рядок із серії, номера паспорта співробітника та дати його видачі, скориставшись операцією & ». Робимо так.
1. Відкриваємо базу даних, як у рис. 1.
2. Стаємо на будь-яку вільну комірку всередині бази (наприклад, на «K2»).
3. Вводимо формулу "="паспорт сер. "&E2&", N "&F2&", виданий "&G2&" ", "&H2».
4. Натискаємо «Enter». У осередку «K2» з'явиться текст: «паспорт сірий. ММ, N676757, виданий Київським РВ ХМУ УМВС України в Харк. обл.,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).
Об'єднання осередків без втрати тексту
На перший погляд, у програмі 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». У вікно, що відкрилося, вводимо такий текст:
Const sDLM As String = " "
Dim rCell As Range
Dim sMrgStr As String
If TypeName(Selection) <> "Range" Then Exit Sub