8 sposobów konkatenacji w programie Excel może poprawić Twoje dane

Biuro Microsoft Microsoft Bohater Przewyższać / by admin / March 19, 2020

Jak

PrzezRyan Dube

Ostatnia aktualizacja dnia13 listopada 2019 r

Gdy dane w arkuszu kalkulacyjnym Excel nie wyglądają dokładnie tak, jak chcesz, lub potrzebujesz lepszego sposobu prezentacji danych w arkuszu, konkatenacja może pomóc.

Funkcja konkatenacji w programie Excel jest jedną z najczęściej używanych funkcji. Powodem jest to, że jest tak wszechstronny. Gdy dane w arkuszu kalkulacyjnym nie wyglądają dokładnie tak, jak chcesz, lub potrzebujesz lepszego sposobu prezentacji danych w arkuszu, konkatenacja może pomóc.

Jak działa konkatenacja w programie Excel

Funkcja konkatenatu jest bardzo łatwa w użyciu. Składnia funkcji działa w następujący sposób:

CONCATENATE (tekst1, tekst2, tekst3,…)

Ta funkcja wydaje się bardzo prosta. Połączy wiele ciągów tekstowych w jeden ciąg. Każdy ciąg (lub komórkę zawierającą ciąg) umieszczasz w funkcji oddzielając je przecinkami.

przy użyciu konkatenatu w programie Excel

Choć może to być proste, jego zastosowania są obfite.

W tym artykule omówimy kilka przykładów najbardziej przydatnych sposobów korzystania z funkcji konkatenacji w różnych projektach.

1. Formatowanie nazw

Jednym z najczęstszych zastosowań funkcji konkatenacji jest to, że arkusz kalkulacyjny zawiera nazwy w dwóch kolumnach, jak w powyższym przykładzie. Jest to jeden z najłatwiejszych sposobów korzystania z tej funkcji, ponieważ polega ona na połączeniu dwóch kolumn ze spacją w środku.

Składnia tego jest następująca:

CONCATENATE (B2, ”„, A2)

Spowoduje to umieszczenie imienia i nazwiska we właściwej kolejności, ze spacją na środku. Aby wypełnić resztę kolumny dla innych nazw, po prostu przytrzymaj Zmiana klawisz na klawiaturze i kliknij dwukrotnie.

za pomocą konkatenatu dla nazw w programie Excel

Spowoduje to automatyczne wypełnienie kolumny resztą nazw.

2. Zaktualizuj status w prostym języku

Możesz użyć arkusza kalkulacyjnego do zebrania dużej ilości danych w celu wykonania złożonej serii obliczeń przy każdym otwarciu arkusza. Ale wszystko, na czym naprawdę Ci zależy, to obliczenie końcowe.

Zaoszczędź czas, używając Concatenate do stworzenia statusu z ostatecznym wynikiem na samej górze arkusza. Jeśli wynik obliczeń znajduje się w komórce G47, użyj poniższej formuły, aby utworzyć zaktualizowany status w komórce A1:

= CONCATENATE („Łączne dotychczasowe wydatki to: $”, G47)

Spowoduje to wyświetlenie wyciągu u góry arkusza kalkulacyjnego, jak pokazano poniżej.

Teraz możesz zaoszczędzić trochę czasu, po prostu otwierając arkusz i uzyskując efekt końcowy na pierwszy rzut oka.

3. Parsuj adres pocztowy

Jednym z najpopularniejszych zastosowań funkcji Concatenate jest wypełnianie różnych pól w wypełnionej bazie danych informacje od użytkowników, klientów lub kogokolwiek innego oraz łączenie ich pełnego adresu w jeden pole.

W ten sposób możesz pobrać cały adres jednym kliknięciem prawym przyciskiem myszy i skopiować.

W arkuszu kalkulacyjnym mogą znajdować się fragmenty adresu podzielone na pola rozłożone w całej tabeli.

Możesz zbudować sformatowany adres, łącząc fragmenty adresu w jedną komórkę oddzieloną znakiem powrotu karetki. W programie Excel jest to „Char (10)”.

Poskładasz adres za pomocą następującej funkcji:

= CONCATENATE (G2, CHAR (10), H2, CHAR (10), K2, ”,”, L2, ”„, M2)

Kod powrotu karetki następuje po nazwie i ulicy, a miasto, ulica i kod pocztowy muszą być sformatowane przecinkiem i spacją.

Rezultat może wyglądać jak pomieszany bałagan. Wynika to z faktu, że musisz sformatować komórki adresu za pomocą opcji Zawiń tekst.

Zrób to, zaznaczając wszystkie komórki adresu, klikając prawym przyciskiem myszy i wybierając Formatuj komórki. W oknie Formatuj komórki wybierz Wyrównanie i włącz Zawijaj tekst.

ustawianie zawijania tekstu w programie Excel

Teraz zobaczysz, że wszystkie adresy są sformatowane zgodnie z oczekiwaniami.

Tego podejścia można użyć do dowolnego pola, w którym chcesz utworzyć sformatowany tekst, który zbiera dane z całego arkusza kalkulacyjnego.

4. Parsuj razem adres URL

Innym dobrym zastosowaniem funkcji konkatenacji jest złożenie razem wszystkich elementów dla pełny ciąg adresu URL. Na przykład w arkuszu kalkulacyjnym produktu link URL do zakupionego produktu może składać się z domeny witryny, działu i identyfikatora produktu.

przeanalizuj adres URL za pomocą konkatenatu

Dla każdego wiersza możesz zbudować adres URL, używając zarówno statycznego, jak i dynamicznego adresowania komórek.

= CONCATENATE („ https://”,D1,”/”,D3,”/”,O3,”.php”)

Spowoduje to dołączenie statycznego tekstu „https:” na początku i „.php” na końcu ciągu adresu URL tego produktu. Wstawisz segmenty adresu URL, wybierając komórki dla domeny, działu i identyfikatora produktu między każdym z ukośników. Wszystkie są oddzielone przecinkami.

Rezultatem jest idealnie sformatowany link URL do tego produktu.

Resztę komórek możesz wypełnić, stosując to samo podejście, co powyżej. Przytrzymaj klawisz Shift i umieść mysz w prawym dolnym rogu pierwszej komórki, w której utworzono konkatenowany adres URL, aż zobaczysz podwójne równoległe linie. Kliknij dwukrotnie, aby wypełnić resztę komórek w tej kolumnie tą samą funkcją. Komórki automatycznie dostosują się do każdego wiersza.

5. Dynamiczne tabele tytułów

Jednym z ulubionych zastosowań funkcji Concatenate jest dynamiczne tytułowanie wykresów w arkuszu kalkulacyjnym. Jest to często potrzebne, aby zaktualizować np. Bieżącą datę arkusza kalkulacyjnego, który jest używany jako raport cykliczny.

Na przykład w arkuszu sprzedaży możesz utworzyć komórkę tytułu, w której znajduje się tytuł dla wykresu. Korzystając z funkcji konkatenacji, możesz zaktualizować ją dla bieżącej daty podczas aktualizacji arkusza kalkulacyjnego.

= CONCATENATE („Całkowita sprzedaż na dzień:”, ROK (TERAZ ()))

Ta komórka będzie aktualizować się z poprawną datą za każdym razem, gdy aktualizacja wykresu.

tworzenie konkatenowanej komórki dla tytułu wykresu

Następnie wystarczy ustawić tytuł wykresu, aby aktualizował się z zawartością komórki.

Aby to zrobić:

Wybierz tytuł wykresu
Wybierz pole funkcji i wpisz „= I1” (zamień I1 gdzie zbudowano konkatenację tytułu wykresu)
naciśnij Wchodzić a tytuł wykresu będzie pasował do dynamicznie aktualizowanej komórki

Teraz, za każdym razem, gdy arkusz kalkulacyjny zostanie zaktualizowany, wykres będzie miał prawidłową datę w tytule. Nie musisz się martwić, że pamiętasz o zaktualizowaniu go przy każdym uruchomieniu raportu.

6. Połącz liczby i tekst

Kolejną przydatną funkcją funkcji konkatenacji jest to, że pozwala ona łączyć zarówno komórki tekstowe, jak i komórki liczbowe w jeden ciąg. Możesz także wprowadzić daty, jeśli przekształcisz pole daty na tekst.

Na przykład, aby przekonwertować każde zdarzenie sprzedaży na zwykłe angielskie oświadczenie, możesz użyć funkcji konkatenacji, jak pokazano poniżej.

= CONCATENATE (C4, „sprzedane”, E4, ”jednostki„, D4, ”na„, TEKST (A4, ”mm / dd / rrrr”))

To zajmie dwa pola tekstowe (nazwa przedstawiciela handlowego i nazwa jednostki) i połączy je z polem numeru i daty (numer jednostki i data sprzedaży).

Umożliwia to między innymi budowanie całego pola tekstowego, którego można następnie użyć do automatycznego wysyłania treści wiadomości e-mail z arkusza Excel.

7. Automatycznie buduj HTML

Jeśli wykorzystasz tę umiejętność łączenia tekstu, wartości i innych danych z arkusza kalkulacyjnego w jedną komórkę treści, a następnie łącząc to za pomocą funkcji nowego wiersza Char (10) i kodów HTML, można zbudować kod HTML, który można wstawić do dowolnego Strona internetowa.

Wykorzystanie tego może polegać na kopiowaniu wyników obliczeń arkusza kalkulacyjnego na stronę internetową, na której udostępniasz wyniki obliczeń publicznie (lub wewnętrznej sieci firmowej).

Załóżmy na przykład, że prowadzisz witrynę klubu książki i publikujesz cotygodniowe aktualizacje dla członków z najnowszymi materiałami do czytania za ten miesiąc. Jeśli używasz arkusza kalkulacyjnego do rejestrowania każdej comiesięcznej aktualizacji, możesz użyć Concatenate do zbudowania zawartości strony dla tego wpisu.

arkusz kalkulacyjny zawartości strony internetowej

Utwórz kolejną komórkę dla zawartości strony internetowej, a następnie Połącz treść za pomocą wpisów komórek z arkusza:

= CONCATENATE („Witam ponownie wszystkich!

”, CHAR (10),„ W tym miesiącu będziemy czytać ”, A2” przez ”, B2”.

”, Char (10),” Ten tytuł został opublikowany w ”, C2”. ”,„ Nie mogę się doczekać, aby usłyszeć, co wszyscy myślicie! ”)

Jak widać, łączenie ciągów statycznych, informacji z komórek i kodów formatowania w połączeniu z HTML formatowanie kodów pozwala tworzyć dowolną treść, która dynamicznie pobiera informacje z twojego arkusz.

Przy odrobinie kreatywności możesz wygenerować dużo treści strony. I po prostu skopiuj i wklej go bezpośrednio do swoich postów na blogu.

8. Generuj kody

Kolejną przydatną sztuczką, do której można użyć konkatenacji, jest poskładanie sekcji informacji z każdego pola w specjalny kod. Możesz użyć tego kodu jako unikalnego identyfikatora.

Korzystając z przykładu arkusza kalkulacyjnego produktu, załóżmy, że chcesz utworzyć unikalny kod identyfikujący każdy zakup dokonany w witrynie. W tym przykładzie kod zakupu będzie się składał z: