зміст
Час від часу може знадобитися об'єднати значення, що містяться в різних клітинках, в одну. Зазвичай для цього використовується символ &. Але його функціональність дещо обмежена, оскільки він не може об’єднувати кілька рядків.
Цю просту функцію замінено на більш функціональну її версію – СЦЕПІТ. Насправді в сучасних версіях Microsoft Office цієї функції вже немає, її повністю замінює функція КРОК. Наразі його можна використовувати, його включено для зворотної сумісності, але через деякий час його може не бути. Тому рекомендується використовувати цю функцію в Excel 2016, Online та новіших версіях КРОК.
Функція CONCATENATE – детальний опис
функція СЦЕПІТ відноситься до тексту. Це означає, що він використовується для виконання операцій над текстовими значеннями. При цьому ви можете вказувати аргументи в різних форматах: текстових, числових або у вигляді посилань на комірки.
Загалом, правила використання цієї функції такі:
- Для розділення аргументів використовується крапка з комою. Якщо користувач вирішить використовувати інші символи, то результат буде відображатися в лапках.
- Якщо значення в текстовому форматі використовується як аргумент функції та вводиться безпосередньо у формулу, воно має бути взяте в лапки. Якщо є посилання на таке значення, то лапки не потрібні. Те саме стосується числових значень. Якщо вам потрібно додати цифру до рядка, то лапки не потрібні. Якщо ви порушите ці правила, буде виведена така помилка – #NAME?
- Якщо потрібно додати пробіл між з’єднаними елементами, його потрібно додати окремим текстовим рядком, тобто взяти в лапки. Подобається це: " " .
Тепер давайте детальніше розглянемо синтаксис цієї функції. Він дуже простий.
синтаксис
Отже, фактично є лише один аргумент – це текстовий рядок, який потрібно вставити. Кожен аргумент, як ми вже знаємо, розділяється крапкою з комою. Ви можете вказати до 255 аргументів. Вони самі по-своєму дублюються. Перший аргумент є обов’язковим. І як ми вже знаємо, ви можете вказати аргументи в трьох форматах: текст, число та посилання.
Застосування функції CONCATENATE
Кількість сфер застосування функції СЦЕПІТ величезний. Насправді його можна застосовувати практично скрізь. Розглянемо деякі з них докладніше:
- Бухгалтерський облік. Наприклад, перед бухгалтером стоїть завдання виділити серію і номер документа, а потім вставити ці дані одним рядком в одну комірку. Або потрібно додати до серії та номер документа, ким він виданий. Або перелічіть кілька квитанцій в одній комірці одночасно. Насправді варіантів ціла купа, перераховувати можна нескінченно.
- Офісні звіти. Особливо, якщо потрібно надати зведені дані. Або поєднати ім'я та прізвище.
- Гейміфікація. Це дуже популярний тренд, який активно використовується в освіті, вихованні дітей, а також в програмах лояльності різних компаній. Тому у сфері освіти та бізнесу ця функція також може бути корисною.
Ця функція входить в стандартний набір, який повинен знати кожен користувач Excel.
Зворотна функція CONCATENATE в Excel
Насправді не існує такої функції, яка була б абсолютно протилежною функції «CONCATENATE». Для виконання поділу клітин використовуються інші функції, наприклад ЛЕВСІМВ и ПРАВОта PSTR. Перший витягує певну кількість символів зліва від рядка. Другий праворуч. АЛЕ PSTR може робити це з довільного місця і закінчувати в довільному місці.
Можливо, вам доведеться виконати і більш складні завдання, але для них є окремі формули.
Поширені проблеми з функцією CONCATENATE
На перший погляд функція СЦЕПІТ досить просто. Але на практиці виявляється, що можлива ціла купа проблем. Давайте розглянемо їх ближче.
- Лапки відображаються в рядку результату. Щоб уникнути цієї проблеми, вам потрібно використовувати крапку з комою як роздільник. Але, як було сказано вище, це правило не стосується чисел.
- Слова дуже близькі. Ця проблема виникає через те, що людина не знає всіх нюансів використання функції СЦЕПІТ. Щоб відображати слова окремо, необхідно додати до них символ пробілу. Або ви можете вставити його безпосередньо після текстового аргументу (як усередині клітинки, так і якщо ви вводите текст окремо у формулу). Наприклад, так: =CONCATENATE(«Привіт», «шановний»). Ми бачимо, що тут додано пробіл у кінці слова «Привіт».
- #ІМ'Я? Це означає, що для текстового аргументу не було вказано жодних лапок.
Рекомендації щодо використання функції
Щоб робота з цією функцією була більш ефективною, необхідно врахувати кілька важливих рекомендацій:
- Використовуйте & якомога більше. Якщо вам потрібно з'єднати лише два рядки тексту, то для цього не потрібно використовувати окрему функцію. Так табличний процесор працюватиме швидше, особливо на слабких комп’ютерах з невеликим об’ємом оперативної пам’яті. Прикладом є наступна формула: =A1 & B1. Вона схожа на формулу =РОЗДІЛ(A1;B1). Особливо перший варіант легший при введенні формули вручну.
- Якщо необхідно поєднати валюту або дату з текстовим рядком, а також інформацію в будь-якому іншому форматі, відмінному від перерахованих вище, то спочатку потрібно обробити це за допомогою функції TEXT. Він призначений для перетворення чисел, дат, символів у текст.
Як бачите, розібратися в цих нюансах зовсім не складно. І вони випливають з інформації вище.
Загальне використання функції CONCATENATE
Отже, загальна формула: CONCATENATE([текст2];[текст2];…). Вставте свій текст у відповідні місця. Важливо відзначити, що вимога до отриманого тексту така: він повинен бути менше довжини поля, в яке вводиться значення. В якості атрибутів можна використовувати не тільки заздалегідь визначені значення, але й інформацію в комірках, а також результати обчислень за іншими формулами.
У цьому плані немає обов'язкової рекомендації використовувати дані для введення в текстовому форматі. Але кінцевий результат буде відображено у форматі «Текст».
Є кілька способів введення функції: один ручний і кілька напівавтоматичних. Якщо ви новачок, то рекомендуємо використовувати метод діалогового вікна введення аргументів. Більш досвідчені користувачі програми також можуть вводити формули вручну. Спочатку це здасться незручним, але насправді нічого ефективнішого введення з клавіатури поки не винайдено.
До речі, рекомендація щодо використання Excel в цілому: завжди вивчайте гарячі клавіші. Вони допоможуть вам заощадити багато часу.
Але поки ви новачок, вам доведеться використовувати спеціально створене вікно.
То як це назвати? Якщо подивитися на рядок введення формули, то зліва від неї є ось така маленька кнопка «fx». Якщо натиснути її, з’явиться наступне меню. Нам потрібно вибрати потрібну функцію зі списку.
Після того, як ми виберемо потрібну функцію, відкриється вікно для введення аргументів. Через нього можна задати діапазон або вручну ввести текст, посилання на комірку.
Якщо ви вводите дані вручну, то введення здійснюється, починаючи зі знака «рівно». Тобто так:
=КОНКАТЕНАТИ(D2;”,”;E2)
Після всіх виконаних нами операцій ми побачимо в отриманій комірці текст «21.09», який складається з декількох частин: числа 21, яке знаходиться в комірці з індексом D2 і рядка 09, що знаходиться в комірці E2. . Щоб їх розділяла крапка, ми використали її як другий аргумент.
Прив'язка імені
Для наочності розглянемо приклад, який описує зв'язування імен.
Припустимо, у нас є така таблиця. Містить інформацію про ім'я, прізвище, місто, штат клієнтів. Наше завдання - поєднати ім'я та прізвище і отримати повне ім'я.
Виходячи з цієї таблиці, ми розуміємо, що посилання на імена потрібно давати в колонці Б, а прізвища – А. Сама формула буде написана в першій комірці під заголовком «ПІБ».
Перш ніж вводити формулу, пам’ятайте, що функція не пов’язуватиме більше інформації, ніж вказав користувач. Тому, якщо потрібно додати роздільники, знаки питання, крапки, тире, пробіли, їх потрібно вводити як окремі аргументи.
У нашому прикладі нам потрібно розділити ім’я та прізвище пробілом. Тому нам потрібно ввести три аргументи: адресу комірки, в якій міститься ім'я, пробіл (не забудьте взяти його в лапки) і адресу комірки, в якій міститься прізвище.
Після того, як ми визначили аргументи, ми записуємо їх у формулу у відповідній послідовності.
Дуже важливо звернути увагу на синтаксис формули. Ми завжди починаємо його зі знака рівності, після чого розкриваємо дужки, перераховуємо аргументи, розділяючи їх крапкою з комою, а потім закриваємо дужки.
Іноді між аргументами можна поставити звичайну кому. Якщо використовується англійська версія Excel, то ставиться кома. Якщо версія на -мові, то крапка з комою. Після того як ми натиснемо Enter, з’явиться об’єднана версія.
Тепер усе, що залишилося, це просто використати маркер автозаповнення, щоб вставити цю формулу в усі інші клітинки цього стовпця. В результаті ми маємо ПІБ кожного клієнта. Місію виконано.
Точно так само можна з'єднати державу і місто.
Зв'язування цифр і тексту
Як ми вже знаємо, за допомогою функції СЦЕПІТ ми можемо об’єднати числові значення з текстовими. Припустимо, у нас є таблиця з даними про наявність товару в магазині. На даний момент у нас 25 яблук, але цей ряд розбитий на дві клітини.
Нам потрібен наступний кінцевий результат.
У цьому випадку нам також потрібні три аргументи, а синтаксис незмінний. Але давайте спробуємо виконати завдання трохи підвищеної складності. Припустимо, нам потрібно написати складний рядок «У нас 25 яблук». Тому нам потрібно додати ще один рядок «Ми маємо» до трьох існуючих аргументів. Кінцевий результат виглядає так.
=CONCATENATE(“У нас є “;F17;” “;F16)
При бажанні користувач може додати майже стільки аргументів, скільки забажає (у межах зазначеного вище ліміту).
Підключення VLOOKUP і CONCATENATE
Якщо ви використовуєте функції VPR и СЦЕПІТ разом може вийти дуже цікава і, що важливо, функціональна комбінація. Використання функції VPR виконуємо вертикальний пошук по таблиці за певним критерієм. Тоді ми можемо додати знайдену інформацію до вже існуючого рядка.
Отже, припустимо, у нас є така таблиця. У ньому описано, які товари зараз знаходяться на першому та другому складах.
Нам потрібно знайти ціну певного товару на певному складі. Для цього використовується функція VPR. Але перед його використанням необхідно спочатку трохи підготувати стіл. VPR виводить дані ліворуч, тому потрібно вставити додатковий стовпець ліворуч таблиці з вихідними даними.
Після цього ми об’єднуємо дані.
Це можна зробити за допомогою цієї формули:
=B2&»/»&C2
Або такий.
=КОНКАТЕНАТИ(B2;”/”;C2)
Таким чином, ми об’єднали два стовпці разом, використовуючи скісну риску як роздільник між двома значеннями. Далі цю формулу ми перенесли на весь стовпець А. Отримаємо ось таку таблицю.
Далі беремо наступну таблицю і заповнюємо її інформацією про продукт, який вибрав відвідувач. З першої таблиці нам потрібно отримати інформацію про вартість товару та номер складу. Це робиться за допомогою функції VPR.
Далі виберіть клітинку К2, і запишіть у нього наступну формулу.
{=ВПР(G2&»/»&H2;A2:E6;5;0)}
Або це можна записати через функцію СЦЕПІТ.
{=ВПР(СЦЕПИТЬ(G2;»/»;H2);A2:E6;5;ЛОЖЬ)}
Синтаксис в даному випадку аналогічний тому, як було здійснено поєднання інформації про номер і склад.
Вам потрібно включити функцію VPR за допомогою комбінації клавіш «Ctrl» + «Shift» + «Enter».
Як бачите, все просто.