W tym artykule dowiemy się, jak łączyć lub scalać w programie Excel.
Co to jest konkatenacja w programie Excel?
Ogólnie istnieją dwa sposoby łączenia danych w arkuszach kalkulacyjnych Excel:
Scal komórki
Połącz wartości komórek
ŁĄCZENIE w Excelu
Jak korzystać z funkcji ZŁĄCZ.TEKSTY Excel
Funkcja CONCATENATE w programie Excel służy do łączenia ze sobą różnych fragmentów tekstu lub łączenia wartości z kilku komórek w jedną komórkę.
Składnia programu Excel CONCATENATE jest następująca:
| ZŁĄCZ.TEKST(tekst1, [tekst2];… ) |
Gdzie tekst to ciąg tekstowy, odwołanie do komórki lub wartość sterowana formułą.
Przykłady:
Łączenie wartości kilku komórek
Najprostsza formuła CONCATENATE łącząca wartości komórek A1 i B1 jest następująca:
| =ZŁĄCZ.TEKSTY(A1; B1) |
Należy pamiętać, że wartości zostaną połączone bez ogranicznika, jak w wierszu 2 na poniższym zrzucie ekranu.
Aby oddzielić wartości spacją, wpisz „ ” w drugim argumencie, tak jak w wierszu 3 na poniższym zrzucie ekranu.
| =ZŁĄCZ.TEKSTY(A1; " "; B1) |
Aby oddzielić połączone wartości za pomocą innych ograniczników, takich jak przecinek, spacja lub ukośnik, zobacz formuły ZŁĄCZANIE W programie Excel ze znakami specjalnymi.
Łączenie ciągu tekstowego i wartości komórki
Nie ma powodu, aby funkcja ZŁĄCZ.TEKSTY w programie Excel była ograniczona tylko do łączenia wartości komórek. Możesz również użyć go do łączenia różnych ciągów tekstowych, aby wynik był bardziej znaczący. Na przykład:
| =ZŁĄCZ.TEKSTY(A1; " "; B1; " zakończone") |
Powyższa formuła informuje użytkownika, że dany projekt został zakończony, jak w wierszu 2 na poniższym zrzucie ekranu. Zwróć uwagę, że dodajemy spację przed słowem „zakończono”, aby oddzielić połączone ciągi tekstowe.
Oczywiście możesz dodać ciąg tekstowy na początku lub w środku formuły Concatenate:
| =CONCATENATE("Patrz", A1, " ", B1) |
Spacja (" ") jest dodawana pomiędzy połączonymi wartościami, dzięki czemu wynik jest wyświetlany jako "Projekt 1", a nie "Projekt1".
Łączenie ciągu tekstowego i wartości obliczonej przez formułę. Aby wynik zwracany przez jakąś formułę był bardziej zrozumiały dla użytkowników, możesz połączyć go z ciągiem tekstowym wyjaśniającym, jaka w rzeczywistości jest wartość.
Na przykład możesz użyć następującej formuły, aby zwrócić bieżącą datę:
| =CONCATENATE("Dzisiaj jest", TEKST(DZIŚ(); "dd-mmm-rr")) |
Korzystanie z funkcji CONCATENATE w programie Excel - rzeczy do zapamiętania
Aby mieć pewność, że formuły CONCATENATE zawsze dostarczają prawidłowe wyniki, pamiętaj o następujących prostych zasadach:
Funkcja CONCATENATE programu Excel wymaga do działania co najmniej jednego argumentu „text”.
W jednej formule CONCATENATE można połączyć maksymalnie 255 ciągów, łącznie 8192 znaków.
Wynikiem funkcji CONCATENATE jest zawsze ciąg tekstowy, nawet jeśli wszystkie wartości źródłowe są liczbami.
Excel CONCATENATE nie rozpoznaje tablic. Każde odwołanie do komórki musi być wymienione osobno. Na przykład należy napisać = CONCATENATE(A1, A2, A3) zamiast = CONCATENATE(A1:A3).
Jeśli co najmniej jeden z argumentów funkcji CONCATENATE jest nieprawidłowy, formuła zwraca #ARG! błąd.
Operator „&” do łączenia ciągów znaków w programie Excel
W programie Microsoft Excel & operator to kolejny sposób łączenia komórek. Ta metoda jest bardzo przydatna w wielu sytuacjach, ponieważ wpisanie znaku ampersand (&) jest znacznie szybsze niż wpisanie słowa „konkatenacja” 🙂
Podobnie jak w przypadku funkcji CONCATENATE, możesz użyć "&" w Excelu, aby połączyć różne ciągi tekstowe, wartości komórek i wyniki zwracane przez inne funkcje.
Przykłady formuł „&” w programie Excel
Aby zobaczyć operator konkatenacji w akcji, przepiszmy formuły CONCATENATE omówione powyżej:
Połącz wartości w A1 i B1:
=A1&B1
Połącz wartości w A1 i B1 oddzielone spacją:
=A1&" "&B1
Połącz wartości w A1, B1 i ciąg tekstowy:
=A1 i B1 i „zakończono”
Połącz ciąg i wynik funkcji TEKST / DZIŚ:
="Dzisiaj jest " & TEKST(DZIŚ(), "dd-mmm-rr")
Jak pokazano na poniższym zrzucie ekranu, funkcja CONCATENATE i operator „&” zwracają identyczne wyniki:
Operator Excela „&” a funkcja ZŁĄCZ.TEKSTY
Wielu użytkowników zastanawia się, który jest bardziej wydajnym sposobem łączenia ciągów w programie Excel - funkcja CONCATENATE czy operator „&”.
Jedyną istotną różnicą między operatorami CONCATENATE i „&” jest limit 255 ciągów funkcji CONCATENATE programu Excel i brak takich ograniczeń w przypadku używania znaku ampersand. Poza tym nie ma różnicy między tymi dwiema metodami konkatenacji, ani nie ma różnicy prędkości między formułami CONCATENATE i „&”.
A ponieważ 255 to naprawdę duża liczba i w realnych zadaniach prawie nigdy nie będzie trzeba łączyć tylu strun, różnica sprowadza się do wygody i łatwości użytkowania. Niektórzy użytkownicy uważają formuły CONCATENATE za łatwiejsze do odczytania, ja osobiście wolę używać metody „&”. Więc po prostu trzymaj się techniki łączenia, z którą czujesz się bardziej komfortowo.
Połącz komórki ze spacją, przecinkiem i innymi znakami
W arkuszach roboczych często może być konieczne łączenie wartości w sposób zawierający przecinki, spacje, różne znaki interpunkcyjne lub inne znaki, takie jak łącznik lub ukośnik. Aby to zrobić, po prostu uwzględnij żądany znak w formule konkatenacji. Pamiętaj, aby umieścić ten znak w cudzysłowie, jak pokazano w poniższych przykładach.
Łączenie dwóch komórek spacją:
=CONCATENATE(A1, " ", B1) lub =A1 & " " & B1
Łączenie dwóch komórek przecinkiem:
=ZŁĄCZ.TEKSTY(A1, ”, ”, B1) lub =A1 & ”, ” & B1
Łączenie dwóch komórek z myślnikiem:
=CONCATENATE(A1, "-", B1) lub =A1 & "-" & B1
Poniższy zrzut ekranu pokazuje, jak mogą wyglądać wyniki:
Połącz ciągi tekstowe z podziałami wierszy
Najczęściej połączone ciągi tekstowe oddziela się znakami interpunkcyjnymi i spacjami, jak pokazano w poprzednim przykładzie. Jednak w niektórych przypadkach może być konieczne oddzielenie wartości za pomocą łamania wiersza lub powrotu karetki. Typowym przykładem jest scalanie adresów pocztowych z danych w osobnych kolumnach.
Problem polega na tym, że nie można po prostu wpisać podziału wiersza w formule, jak zwykły znak, i dlatego potrzebna jest specjalna funkcja CHAR, aby dostarczyć odpowiedni kod ASCII do formuły konkatenacji:
W systemie Windows użyj CHAR(10), gdzie 10 to kod ASCII dla wysuwu wiersza.
W systemie Mac użyj CHAR(13), gdzie 13 jest kodem ASCII dla zwrotu karetki.
W tym przykładzie mamy fragmenty adresu w kolumnach od A do F i łączymy je razem w kolumnie G za pomocą operatora konkatenacji „&”. Połączone wartości są oddzielone przecinkiem (", "), spacją (" ") i znakiem końca wiersza CHAR(10):
| =A2 & " " & B2 & ZNAK(10) & C2 & ZNAK(10) & D2 & ", " & E2 & " " & F2 |
Notatka. Używając łamania wierszy do oddzielania połączonych wartości, musisz mieć znak „Zawijaj tekstopcja włączona, aby wynik był wyświetlany poprawnie. Aby to zrobić, naciśnij Ctrl + 1, aby otworzyć okno dialogowe Formatowanie komórek, przejdź do karty Wyrównanie i zaznacz pole tekstowe Zawijaj.
W ten sam sposób możesz oddzielić połączone ciągi innymi znakami, takimi jak:
Podwójne cudzysłowy (") - CHAR(34)
Ukośnik (/) - CHAR(47)
Gwiazdka (*) - CHAR (42)
Pełna lista Kody ASCII jest dostępny
Chociaż łatwiejszym sposobem włączenia znaków drukowalnych do formuły konkatenacji jest po prostu wpisanie ich w podwójnych cudzysłowach, tak jak to zrobiliśmy w poprzednim przykładzie.
Tak czy inaczej, wszystkie cztery poniższe formuły dają identyczne wyniki:
Użyj jednej z poniższych formuł:
| =A1 & ZNAK(47) & B1
=A1 & "/" & B1 =ZŁĄCZ.TEKST(A1, ZNAK(47); B1) =ZŁĄCZ.TEKSTY(A1;"/"; B1) |
Jak łączyć kolumny w programie Excel
Aby połączyć dwie lub więcej kolumn w programie Excel, wystarczy wprowadzić zwykłą formułę łączenia w pierwszej komórce, a następnie skopiować ją do innych komórek, przeciągając uchwyt wypełniania (mały kwadrat, który pojawia się w prawym dolnym rogu wybrana komórka).
Na przykład, aby połączyć dwie kolumny (kolumny A i B), oddzielając wartości spacją, wprowadź następującą formułę w komórce C2, a następnie skopiuj ją do innych komórek. Podczas przeciągania uchwytu wypełniania w celu skopiowania formuły wskaźnik myszy zmienia się w krzyżyk, jak pokazano na poniższym zrzucie ekranu:
Uwaga : Szybkim sposobem skopiowania formuły do innych komórek w kolumnie jest zaznaczenie komórki z formułą i dwukrotne kliknięcie uchwytu wypełniania.
Należy pamiętać, że program Microsoft Excel określa, jak daleko skopiować komórki po dwukrotnym kliknięciu obsługi wypełniania na podstawie komórek, do których odwołuje się formuła. Jeśli w tabeli znajdują się puste komórki, powiedzmy, że komórki A6 i B6 są puste w tym przykładzie, formuła zostanie skopiowana tylko do wiersza 5. W takim przypadku musisz ręcznie przeciągnąć uchwyt wypełniania w dół, aby połączyć całe kolumny.
Alternatywnym sposobem łączenia kolumn w programie Excel jest użycie odpowiedniej opcji dodatku Scal komórki.
Jak połączyć zakres komórek w Excelu?
Łączenie wartości z wielu komórek może wymagać pewnego wysiłku, ponieważ funkcja ZŁĄCZ.TEKSTY programu Excel nie akceptuje tablic i wymaga pojedynczego odwołania do komórki w każdym argumencie.
Aby połączyć kilka komórek, powiedzmy od A1 do A4, potrzebujesz jednej z następujących formuł:
| =ZŁĄCZ.TEKSTY(A1, A2, A3, A4)
lub =A1 i A2 oraz A3 i A4 |
Gdy dołączasz do dość małego zakresu, nie ma problemu, aby wprowadzić wszystkie odwołania na pasku formuły. Dodawanie dużego zakresu byłoby żmudne, wpisując każde odwołanie do komórki ręcznie. Poniżej znajdziesz 3 metody szybkiego łączenia zakresów w Excelu.
Metoda 1. Naciśnij klawisz CTRL, aby wybrać wiele komórek do połączenia.
Aby szybko zaznaczyć kilka komórek, możesz nacisnąć klawisz CTRL i kliknąć każdą komórkę, którą chcesz uwzględnić w formule CONCATENATE. Oto szczegółowe kroki:
- Wybierz komórkę, w której chcesz wprowadzić formułę.
- Wpisz =CONCATENATE(w tej komórce lub na pasku formuły.
- Naciśnij i przytrzymaj klawisz Ctrl i kliknij każdą komórkę, którą chcesz połączyć.
- Zwolnij przycisk Ctrl, wpisz nawias zamykający na pasku formuły i naciśnij Enter.
Metoda 2. Użyj funkcji TRANSPONUJ, aby uzyskać zasięg
Kiedy musisz połączyć ogromny zakres składający się z dziesiątek lub setek komórek, poprzednia metoda nie jest wystarczająco szybka, ponieważ wymaga kliknięcia każdej komórki. W takim przypadku lepszym sposobem jest użycie funkcji TRANSPONUJ w celu zwrócenia tablicy, a następnie zastąpienie jej pojedynczymi odwołaniami do komórek za jednym zamachem.
W komórce, w której chcesz wyprowadzić połączony zakres, wprowadź formułę TRANSPONUJ, na przykład:
=TRANSPOZYCJA(A1:A10)
Na pasku formuły naciśnij klawisz F9, aby zastąpić formułę obliczonymi wartościami. W rezultacie będziesz mieć tablicę liczb do połączenia.
Wpisz =CONCATENATE(przed pierwszą wartością, a następnie wpisz nawias zamykający po ostatniej wartości i naciśnij klawisz Enter.
Uwaga : Bez względu na to, której metody użyjesz, połączona wartość w C1 jest ciągiem tekstowym (zwróć uwagę na jego wyrównanie do lewej w komórce), chociaż każda z oryginalnych wartości jest liczbą. Dzieje się tak, ponieważ funkcja CONCATENATE zawsze zwraca ciąg tekstowy, niezależnie od typu danych źródłowych.
Metoda 3. Użyj dodatku Merge Cells
Szybkim i pozbawionym formuł sposobem łączenia dowolnego zakresu w programie Excel jest użycie dodatku Scal komórki dla programu Excel z wyłączoną opcją „Scal wszystkie obszary w zaznaczeniu”, jak pokazano w sekcji Łączenie wartości kilku komórek w jedną komórkę.
Łącz liczby i daty w różnych formatach
Gdy łączysz ciąg tekstowy z liczbą lub datą, możesz chcieć sformatować wynik inaczej w zależności od zestawu danych. Aby to zrobić, osadź funkcję TEKST w swojej formule konkatenacji programu Excel.
Funkcja TEKST(wartość; format_tekst) ma dwa argumenty:
W pierwszym argumencie (wartość), podajesz liczbę lub datę do przekonwertowania na tekst, albo odwołanie do komórki zawierającej wartość liczbową.
W drugim argumencie (format_tekst), wprowadzasz żądany format za pomocą kodów zrozumiałych dla funkcji TEKST.
Omówiliśmy już jedną taką formułę na początku tego samouczka, która łączy tekst i datę.
Przypomnę, że przy łączeniu a ciąg tekstowy i data, musisz użyć funkcji TEKST, aby wyświetlić datę w żądanym formacie. Na przykład:
| =CONCATENATE("Dzisiaj jest ", TEKST(DZIŚ(); "mm/dd/rr"))
lub ="Dzisiaj to " & TEKST(DZIŚ(), "mm/dd/rr") |
Jeszcze kilka przykładów formuł, które łączą a wartość tekstowa i liczba śledź poniżej:
| =A2 & " " & TEKST(B2, "$#,#0.00") - wyświetla liczbę z 2 miejscami po przecinku i znakiem $.
=A2 & " " & TEKST(B2, "0.#") - nie wyświetla dodatkowych zer i znaku $. =A2 & " " & TEKST(B2, "# ?/???") - wyświetla liczbę jako ułamek. |
Jak podzielić komórki (przeciwieństwo ZŁĄCZENIA w Excelu)
Jeśli szukasz przeciwieństwa funkcji CONCATENATE w Excelu, czyli chcesz podzielić jedną komórkę na kilka komórek, dostępnych jest kilka opcji:
Funkcja tekstu do kolumn
Opcja Flash Fill w Excelu 2013 i 2016
Formuły (funkcje MID, RIGHT, LEFT)
Szczegółowe kroki zilustrowane przykładami formuł i zrzutami ekranu można znaleźć w samouczku Jak podzielić komórki w programie Excel.
Dodatek Merge Cells - sposób łączenia komórek w programie Excel bez formuł
Dzięki dodatkowi Merge Cells zawartemu w Ultimate Suite for Excel możesz wydajnie wykonywać obie te czynności:
Scal kilka komórek w jedną bez utraty danych.
Połącz wartości kilku komórek w jedną komórkę i oddziel je dowolnym wybranym ogranicznikiem.
Narzędzie Scal komórki działa ze wszystkimi wersjami programu Excel od 2003 do 2016 r. i może łączyć wszystkie typy danych, w tym ciągi tekstowe, liczby, daty i symbole specjalne. Jego dwie kluczowe zalety to prostota i szybkość – każda konkatenacja odbywa się za pomocą kilku kliknięć. A teraz pozwól, że pokażę ci to w akcji.
Połącz wartości kilku komórek w jedną komórkę
Aby połączyć zawartość kilku komórek, wybierz zakres do połączenia i skonfiguruj następujące ustawienia:
Komórki w jeden w sekcji „Co scalić”;
Wybierz separator, który chcesz w sekcji „Oddziel wartości za pomocą”, w tym przykładzie jest to przecinek i spacja;
Wybierz, gdzie chcesz umieścić wynik, a co najważniejsze
Odznacz opcję „Połącz wszystkie obszary w zaznaczeniu”. To ta opcja określa, czy komórki są scalane, czy wartości komórek są łączone.
Połącz kolumny wiersz po wierszu
Aby połączyć dwie lub więcej kolumn, konfigurujesz ustawienia scalania komórek w podobny sposób, ale wybierz Kolumny w sekcji „Co scalić”:
Połącz wiersze kolumna po kolumnie
Aby połączyć dane w każdym wierszu, kolumna po kolumnie, wybierasz scalanie Wydziwianie, wybierz żądany ogranicznik (w tym przykładzie podział wiersza), skonfiguruj inne ustawienia tak, jak chcesz i naciśnij przycisk Scal. Wynik może wyglądać podobnie do tego:
Notatka :
W Excel 2016, Excel 2019, Excel Online i Excel Mobile funkcja CONCATENATE jest zastępowana funkcją CONCAT, która ma dokładnie taką samą składnię. Mimo że funkcja ZŁĄCZ.TEKSTY jest zachowywana w celu zapewnienia zgodności z poprzednimi wersjami, zaleca się używanie funkcji ZŁĄCZ.TEKSTY, ponieważ firma Microsoft nie daje żadnych obietnic, że funkcja ZŁĄCZ.TEKSTY będzie dostępna w przyszłych wersjach programu Excel.
Mam nadzieję, że ten artykuł na temat łączenia lub łączenia w programie Excel jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Excel REPLACE vs funkcja SUBSTITUTE: Funkcje REPLACE i SUBSTITUTE są najbardziej niezrozumianymi funkcjami. Aby znaleźć i zamienić dany tekst, używamy funkcji SUBSTITUTE. Gdzie REPLACE służy do zastąpienia liczby znaków w ciągu…
Popularne artykuły :
50 skrótów Excela, które zwiększą Twoją produktywność : Szybsze wykonywanie zadań w programie Excel. Te skróty pomogą Ci zwiększyć wydajność pracy w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.