В тази статия ще научим как да свързваме или обединяваме в Excel.
Какво е Concatenate в Excel?
Като цяло има два начина за комбиниране на данни в електронни таблици на Excel:
Сливане на клетки
Свържете стойностите на клетката
КОНЦЕНТАЦИЯ в Excel
Как да използвате функцията CONCATENATE Excel
Функцията CONCATENATE в Excel е проектирана да свързва различни парчета текст заедно или да комбинира стойности от няколко клетки в една клетка.
Синтаксисът на Excel CONCATENATE е следният:
| СВЪРЗВАНЕ (текст1, [текст2], …) |
Където текст е текстов низ, препратка към клетка или стойност, управлявана от формула.
Примери:
Обединяване на стойностите на няколко клетки
Най -простата формула CONCATENATE за комбиниране на стойностите на клетки A1 и B1 е следната:
| = КОНКАТЕНТ (A1, B1) |
Моля, обърнете внимание, че стойностите ще бъдат свързани заедно без разделител, както в ред 2 на екрана по -долу.
За да разделите стойностите с интервал, въведете "" във втория аргумент, както в ред 3 на екрана по -долу.
| = CONCATENATE (A1, "", B1) |
За да разделите конкатенираните стойности с други разделители като запетая, интервал или наклонена черта, моля, вижте формулите на Excel CONCATENATE със специални знаци.
Свързване на текстов низ и стойност на клетката
Няма причина функцията Excel CONCATENATE да се ограничава само до присъединяване към стойностите на клетките. Можете също да го използвате, за да обедините различни текстови низове, за да направите резултата по -смислен. Например:
| = CONCATENATE (A1, "", B1, "завършено") |
Горната формула информира потребителя, че определен проект е завършен, както в ред 2 на екрана по -долу. Моля, обърнете внимание, че добавяме интервал преди думата "завършено", за да разделим конкатенираните текстови низове.
Естествено, можете да добавите и текстов низ в началото или в средата на вашата формула Concatenate:
| = CONCATENATE ("Вижте", A1, "", B1) |
Между комбинираните стойности се добавя интервал (""), така че резултатът да се показва като "Project 1", а не като "Project1".
Свързване на текстов низ и изчислена по формула стойност. За да направите резултата, върнат по някаква формула, по -разбираем за вашите потребители, можете да го комбинирате с текстов низ, който обяснява каква всъщност е стойността.
Например, можете да използвате следната формула, за да върнете текущата дата:
| = CONCATENATE ("Днес е", TEXT (TODAY (), "dd-mmm-yy")) |
Използване на CONCATENATE в Excel - неща, които трябва да запомните
За да сте сигурни, че вашите формули CONCATENATE винаги дават правилните резултати, запомнете следните прости правила:
Функцията Excel CONCATENATE изисква поне един "текстов" аргумент, за да работи.
В една формула CONCATENATE можете да обедините до 255 низа, общо 8 192 знака.
Резултатът от функцията CONCATENATE винаги е текстов низ, дори когато всички изходни стойности са числа.
Excel CONCATENATE не разпознава масиви. Всяка референтна клетка трябва да бъде посочена отделно. Например, трябва да напишете = CONCATENATE (A1, A2, A3) вместо = CONCATENATE (A1: A3).
Ако поне един от аргументите на функцията CONCATENATE е невалиден, формулата връща #VALUE! грешка.
оператор "&" за свързване на низове в Excel
В Microsoft Excel, & operator е друг начин за свързване на клетки. Този метод е много полезен в много сценарии, тъй като въвеждането на знака амперсанд (&) е много по -бързо от въвеждането на думата "конкатенация" 🙂
Подобно на функцията CONCATENATE, можете да използвате „&“ в Excel, за да комбинирате различни текстови низове, стойности на клетки и резултати, върнати от други функции.
Примери за формули на Excel "&"
За да видим оператора на конкатенация в действие, нека пренапишем формулите CONCATENATE, обсъдени по -горе:
Свържете стойностите в A1 и B1:
= A1 & B1
Свържете стойностите в A1 и B1, разделени с интервал:
= A1 & "" & B1
Свържете стойностите в A1, B1 и текстов низ:
= A1 & B1 & "завършено"
Свържете низ и резултата от функцията TEXT / TODAY:
= "Днес е" & TEXT (TODAY (), "dd-mmm-yy")
Както е показано на екрана по -долу, функцията CONCATENATE и операторът "&" връщат идентични резултати:
Оператор Excel "&" срещу функция CONCATENATE
Много потребители се чудят кой е по -ефективният начин за свързване на низове в Excel - функция CONCATENATE или оператор "&".
Единствената съществена разлика между операторите CONCATENATE и "&" е ограничението от 255 низове на функцията Excel CONCATENATE и няма такива ограничения при използването на амперсанда. Освен това, няма разлика между тези два метода на конкатенация, нито разлика в скоростта между формулите CONCATENATE и "&".
И тъй като 255 е наистина голям брой и в реални задачи едва ли някой ще има нужда да комбинира толкова много струни, разликата се свежда до комфорта и лекотата на използване. Някои потребители намират формулите CONCATENATE за по -лесни за четене, аз лично предпочитам да използвам метода "&". Така че, просто се придържайте към техниката на свързване, с която се чувствате по -удобно.
Свържете клетките с интервал, запетая и други знаци
В работните ви листове често може да се наложи да обединявате стойности по начин, който включва запетаи, интервали, различни препинателни знаци или други знаци, като тире или наклонена черта. За да направите това, просто включете желания знак във вашата формула за конкатенация. Не забравяйте да поставите този знак в кавички, както е показано в следващите примери.
Свързване на две клетки с интервал:
= CONCATENATE (A1, "", B1) или = A1 & "" & B1
Свързване на две клетки със запетая:
= CONCATENATE (A1, ",", B1) или = A1 & "," & B1
Свързване на две клетки с тире:
= CONCATENATE (A1, "-", B1) или = A1 & "-" & B1
Следващата екранна снимка показва как могат да изглеждат резултатите:
Свържете текстови низове с прекъсвания на редове
Най -често бихте разделяли съединените текстови низове с препинателни знаци и интервали, както е показано в предишния пример. В някои случаи обаче може да се наложи разделяне на стойностите с прекъсване на ред или връщане на каретка. Често срещан пример е обединяването на пощенски адреси от данни в отделни колони.
Проблем е, че не можете просто да въведете прекъсване на ред във формулата като обикновен знак и следователно е необходима специална функция CHAR за предоставяне на съответния ASCII код към формулата за свързване:
В Windows използвайте CHAR (10), където 10 е ASCII кодът за емисия на линии.
В системата Mac използвайте CHAR (13), където 13 е ASCII кодът за връщане на каретка.
В този пример имаме адресни части в колони от A до F и ги поставяме заедно в колона G, като използваме оператора за свързване "&". Обединените стойности са разделени със запетая (","), интервал ("") и прекъсване на ред CHAR (10):
| = A2 & "" & B2 & CHAR (10) & C2 & CHAR (10) & D2 & "," & E2 & "" & F2 |
Забележка. Когато използвате прекъсвания на редове за разделяне на свързаните стойности, трябва да имате "Прехвърляне на текст"опцията е активирана, за да може резултатът да се показва правилно. За да направите това, натиснете Ctrl + 1, за да отворите диалоговия прозорец Форматиране на клетки, превключете към раздела Подравняване и поставете отметка в текстовото поле Wrap.
По същия начин можете да разделите низове с други символи като:
Двойни кавички (") - CHAR (34)
Наклонена черта напред (/) - CHAR (47)
Звездичка (*) - CHAR (42)
Пълният списък на ASCII кодове е на разположение
Въпреки това, по -лесен начин да включите отпечатващи се символи във формулата за конкатенация е просто да ги въведете в двойни кавички, както направихме в предишния пример.
Така или иначе, четирите формули по -долу дават идентични резултати:
Използвайте някоя от следните формули:
| = A1 & CHAR (47) & B1
= A1 & "/" & B1 = КОНКАТЕНТ (A1, CHAR (47), B1) = КОНКАТЕНТ (A1, "/", B1) |
Как да обединявате колони в Excel
За да обедините две или повече колони в Excel, просто въведете обичайна формула за конкатенация в първата клетка и след това я копирайте надолу в други клетки, като плъзнете дръжката за запълване (малкия квадрат, който се появява в долния десен ъгъл на избрана клетка).
Например, за да обедините две колони (колони A и B), разделящи стойностите с интервал, въвеждате следната формула в клетка C2 и след това я копирате в други клетки. Когато плъзгате дръжката за запълване, за да копирате формулата, показалецът на мишката се променя в кръст, както е показано на екрана по -долу:
Забележка: Бърз начин да копирате формулата надолу в други клетки в колоната е да изберете клетката с формулата и да щракнете двукратно върху дръжката за запълване.
Моля, обърнете внимание, че Microsoft Excel определя колко далеч да копирате клетки, след като запълването обработва двойно щракване въз основа на клетките, посочени във вашата формула. Ако случайно в таблицата ви има празни клетки, да речем, че клетки A6 и B6 са празни в този пример, формулата ще се копира само до ред 5. В този случай ще трябва да плъзнете ръкохватката за запълване надолу ръчно, за да обедините всички колони.
Алтернативен начин за обединяване на колони в Excel е да използвате съответната опция на добавката Merge Cells.
Как да обединим диапазон от клетки в Excel
Комбинирането на стойности от множество клетки може да отнеме известно усилие, тъй като функцията Excel CONCATENATE не приема масиви и изисква една препратка към клетка във всеки аргумент.
За да обедините няколко клетки, да речем A1 до A4, имате нужда от някоя от следните формули:
| = КОНКАТЕНТ (A1, A2, A3, A4)
или = A1 & A2 & A3 & A4 |
Когато се присъедините към сравнително малък диапазон, не е голяма работа да въведете всички препратки в лентата с формули. Голям диапазон би бил досаден за добавяне, като се въвежда ръчно всяка референция на клетка. По -долу ще намерите 3 метода за бързо свързване на обхвата в Excel.
Метод 1. Натиснете CTRL, за да изберете няколко клетки, които да бъдат свързани.
За да изберете бързо няколко клетки, можете да натиснете клавиша CTRL и да кликнете върху всяка клетка, която искате да включите във формулата CONCATENATE. Ето подробните стъпки:
- Изберете клетка, в която искате да въведете формулата.
- Тип = CONCATENATE (в тази клетка или в лентата с формули.
- Натиснете и задръжте Ctrl и щракнете върху всяка клетка, която искате да обедините.
- Освободете бутона Ctrl, въведете затварящата скоба в лентата с формули и натиснете Enter.
Метод 2. Използвайте функцията TRANSPOSE, за да получите диапазона
Когато трябва да обедините огромен диапазон, състоящ се от десетки или стотици клетки, предишният метод не е достатъчно бърз, защото изисква щракване върху всяка клетка. В този случай по -добрият начин е да използвате функцията TRANSPOSE за връщане на масив и след това да го замените с отделни препратки към клетки с един замах.
В клетката, където искате да изведете конкатенирания диапазон, въведете формулата TRANSPOSE, например:
= ТРАНСПОЗИРАНЕ (A1: A10)
В лентата с формули натиснете F9, за да замените формулата с изчислени стойности. В резултат на това ще имате масив от числа, които да бъдат свързани.
Въведете = CONCATENATE (преди първата стойност, след това въведете затварящата скоба след последната стойност и натиснете Enter.
Забележка: Който и метод да използвате, свързаната стойност в C1 е текстов низ (забележете подравняването му вляво в клетката), въпреки че всяка от първоначалните стойности е число. Това е така, защото функцията CONCATENATE винаги връща текстов низ, независимо от типа източник на данни.
Метод 3. Използвайте добавката Merge Cells
Бърз и без формули начин да обедините всеки диапазон в Excel е да използвате добавката Merge Cells за Excel с изключена опцията „Обединяване на всички области в селекцията“, както е показано в Комбиниране на стойностите на няколко клетки в една клетка.
Свържете числа и дати в различни формати
Когато комбинирате текстов низ с число или дата, може да искате да форматирате резултата по различен начин в зависимост от набора от данни. За да направите това, вградете функцията TEXT във вашата формула за конкатенация на Excel.
Функцията TEXT (стойност, формат_текст) има два аргумента:
В първия аргумент (стойност), предоставяте номер или дата, която да се преобразува в текст, или препратка към клетката, съдържаща числова стойност.
Във втория аргумент (format_text), въвеждате желания формат, като използвате кодовете, които функцията ТЕКСТ може да разбере.
Вече обсъждахме една такава формула в началото на този урок, която обединява текст и дата.
Ще ви напомня, че когато комбинирате a текстов низ и дата, трябва да използвате функцията ТЕКСТ, за да покажете датата в желания формат. Например:
| = CONCATENATE ("Днес е", TEXT (TODAY (), "mm/dd/yy"))
или = "Днес е" & TEXT (TODAY (), "mm/dd/yy") |
Още няколко примера за формули, които свързват a текстова стойност и число следвайте по -долу:
| = A2 & "" & TEXT (B2, "$#,#0.00") - показва числото с 2 десетични знака и знака $.
= A2 & "" & TEXT (B2, "0.#") - не показва допълнителни нули и знака $. = A2 & "" & TEXT (B2, "#?/???") - покажете номера като дроб. |
Как да разделя клетки (обратно на CONCATENATE в Excel)
Ако търсите обратното на CONCATENATE в Excel, т.е.искате да разделите една клетка на няколко клетки, имате няколко опции:
Функция Текст в колони
Опция Flash Fill в Excel 2013 и 2016
Формули (функции MID, RIGHT, LEFT)
Можете да намерите подробните стъпки, илюстрирани с примери за формули и екранни снимки в урока Как да разделяте клетки в Excel.
Добавка за обединяване на клетки-начин без формула за обединяване на клетки в Excel
С добавката Merge Cells, включена в Ultimate Suite for Excel, можете ефективно да направите и двете:
Обединете няколко клетки в една, без да губите данни.
Свържете стойностите на няколко клетки в една клетка и ги разделете с всеки разделител по ваш избор.
Инструментът за сливане на клетки работи с всички версии на Excel от 2003 до 2016 г. и може да комбинира всички типове данни, включително текстови низове, числа, дати и специални символи. Двете ключови предимства са простотата и бързината - всяко свързване се извършва с няколко кликвания. И сега, нека ви го покажа в действие.
Комбинирайте стойностите на няколко клетки в една клетка
За да комбинирате съдържанието на няколко клетки, избирате диапазона за конкатенация и конфигурирате следните настройки:
Клетки в едно под „Какво да се слеят“;
Изберете желания разделител под „Отделете стойностите с“, това е запетая и интервал в този пример;
Изберете къде искате да поставите резултата и най -важното
Премахнете отметката от опцията „Обединяване на всички области в селекцията“. Тази опция определя дали клетките са обединени или стойностите на клетките са свързани.
Комбинирайте колони ред по ред
За да обедините две или повече колони, конфигурирате настройките на Merge Cells по подобен начин, но изберете Колони под „Какво да обединим“:
Присъединете се към редове колона по колона
За да комбинирате данни във всеки отделен ред, колона по колона, вие избирате да обедините Редове, изберете желания разделител (прекъсване на ред в този пример), конфигурирайте други настройки по желания от вас начин и натиснете бутона Обединяване. Резултатът може да изглежда подобен на този:
Забележка :
В Excel 2016, Excel 2019, Excel Online и Excel Mobile, CONCATENATE се заменя с функцията CONCAT, която има абсолютно същия синтаксис. Въпреки че функцията CONCATENATE се запазва за обратна съвместимост, се препоръчва вместо това да се използва CONCAT, тъй като Microsoft не обещава, че CONCATENATE ще бъде наличен в бъдещите версии на Excel.
Надявам се, че тази статия за това как да обединявате или обединявате в Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Excel REPLACE vs SUBSTITUTE функция: Функциите REPLACE и SUBSTITUTE са най -неразбраните функции. За да намерим и заменим даден текст, използваме функцията SUBSTITUTE. Когато REPLACE се използва за замяна на редица знаци в низ …
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнявайте задачите си в Excel. Тези преки пътища ще ви помогнат да увеличите ефективността на работата си в Excel.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.