Pobierz przykładowy skoroszyt
W tym samouczku zademonstrujemy, jak łączyć wartości komórek na podstawie kryteriów przy użyciu funkcji TEXTJOIN w programie Excel i Arkuszach Google.
Funkcja TEXTJOIN
Użytkownicy programu Excel 2022+ mają dostęp do funkcji TEXTJOIN, która łączy ze sobą wiele ciągów tekstu, oddzielając każdy ciąg określonym separatorem.
Notatka: Użytkownicy Arkuszy Google mogą korzystać z funkcji TEXTJOIN, ale mają nieco inny sposób wprowadzania formuł tablicowych. Zobacz późniejszą sekcję na ten temat.
Ten przykład użyje funkcji TEXTJOIN i IF w formule tablicowej do utworzenia ciągu tekstowego oddzielonego przecinkami Nazwy graczy które odnoszą się do Zespół wartość czerwony
| 1 | =TEKSTJOIN(", ",PRAWDA,JEŻELI(C3:C8="Czerwony",B3:B8,")) |
Użytkownicy programu Excel 2022 będą musieli wprowadzić tę formułę jako funkcję tablicową, naciskając klawisze CTRL + SHIFT + ENTER. Użytkownicy nowszych wersji programu Excel nie muszą wykonywać tego kroku.
Aby wyjaśnić, co robi ta formuła, podzielmy ją na kroki:
Oto nasza ostateczna formuła:
| 1 | =TEKSTJOIN(", ",PRAWDA,JEŻELI(C3:C8="Czerwony",B3:B8,")) |
Najpierw do formuły dodawane są wartości zakresu komórek:
| 1 | =TEXTJOIN(", ",TRUE,IF({"Czerwony"; "Niebieski"; "Niebieski"; "Czerwony"; "Niebieski"; "Czerwony"}="Czerwony",{"A"; "B"; „C”; „D”; „E”; „F”},”)) |
Dalej lista Zespół nazwy są porównywane do wartości czerwony:
| 1 | =TEXTJOIN(", ",PRAWDA,JEŻELI({PRAWDA; FAŁSZ; FAŁSZ; PRAWDA; FAŁSZ; PRAWDA},{"A"; "B"; "C"; "D"; "E"; "F"} ,"")) |
Funkcja IF zastępuje wartości TRUE przez Nazwa graczai FALSE wartości z „”
| 1 | =TEXTJOIN(", ",PRAWDA,{"A"; ""; ""; "D"; ""; "F"}) |
Funkcja TEXTJOIN łączy następnie wszystkie wartości tablicy w jeden ciąg tekstowy. Poinstruowaliśmy funkcję, aby ignorowała puste wartości i dodawała tekst „,” między każdą wartością. Daje to ostateczny wynik:
| 1 | ="A, D, F" |
TextJoin If - przed programem Excel 2022
Ponieważ funkcja TEXTJOIN nie jest dostępna przed wersją Excel 2022, musimy rozwiązać ten problem w inny sposób. Funkcja CONCATENATE jest dostępna, ale nie przyjmuje zakresów komórek jako danych wejściowych ani nie zezwala na operacje tablicowe, dlatego zamiast tego musimy użyć kolumny pomocniczej z funkcją IF.
Następny przykład pokazuje, jak użyć kolumny pomocniczej, aby utworzyć ciąg tekstowy oddzielony przecinkami Nazwy graczy które odnoszą się do Zespół wartość Czerwony:
| 1 | =JEŻELI(C3="Czerwony",B3&", ","")&D4 |
Pierwszym krokiem w tym przykładzie jest użycie funkcji IF w celu zreplikowania warunku Drużyna = Czerwony. Jeśli warunek jest spełniony, Nazwa gracza zapisywana jest wartość, po której następuje wybrany ogranicznik „, „:
| 1 | =JEŻELI(C3="Czerwony",B3&", ","") |
Następnie możemy utworzyć kolumnę, która tworzy listę tych wartości w jednej komórce, odwołując się również do komórki poniżej:
| 1 | =D3&E4 |
Ta formuła używa znaku & do połączenia dwóch wartości. Należy zauważyć, że funkcja CONCATENATE może zostać użyta do uzyskania dokładnie tego samego wyniku, ale metoda & jest często preferowana, ponieważ jest krótsza i wyraźniej wyjaśnia, jakie działanie wykonuje formuła.
Te dwie kolumny pomocnicze można następnie połączyć w jedną formułę:
| 1 | =JEŻELI(C3="Czerwony",B3&", ","")&D4 |
Komórka podsumowania może wtedy odwoływać się do pierwszej wartości w Lista graczy kolumna pomocnicza:
| 1 | =D3 |
Jeśli końcowa wartość „”, nie jest wymagana, do formuły należy dodać dodatkową zagnieżdżoną funkcję IF:
| 1 | =JEŻELI(C3="Czerwony",JEŻELI(D4="",B3,B3&", "),"")&D4 |
Dodatkową częścią wzoru jest poniższa funkcja IF:
| 1 | JEŻELI(D4="",B3,B3&", ") |
Ta część formuły sprawdza wartość komórki pod nią. Jeśli jest pusta, to ta komórka musi być ostatnią Nazwa gracza na liście, a nazwa jest napisana bez ogranicznika. Jeśli komórka poniżej zawiera wartość, to ta komórka nie jest ostatnią Nazwa gracza na liście, a więc dodawany jest ogranicznik „, ”.
TextJoin Jeśli w Arkuszach Google
Te formuły działają tak samo w Arkuszach Google, jak w programie Excel, z tą różnicą, że do poprawnej oceny wyników w Arkuszach Google wymagana jest funkcja ARRAYFORMULA. Można to dodać automatycznie, naciskając klawisze CTRL + SHIFT + ENTER podczas edycji formuły:
| 1 | =TABLICAFORMULA(TEKSTJOIN(", ",PRAWDA,(JEŻELI(C3:C8="Czerwony",B3:B8,"")))) |
