Descărcați exemplul de registru de lucru
Acest tutorial va demonstra cum să concatenați valorile celulei pe baza criteriilor folosind funcția TEXTJOIN în Excel și Foi de calcul Google.
Funcția TEXTJOIN
Utilizatorii Excel 2022+ au acces la funcția TEXTJOIN, care îmbină mai multe șiruri de text împreună, separând fiecare șir cu un delimitator specificat.
Notă: Utilizatorii Foi de calcul Google pot utiliza funcția TEXTJOIN, dar au un mod ușor diferit de a introduce formulele matrice. Consultați secțiunea ulterioară despre acest subiect.
Acest exemplu va folosi funcțiile TEXTJOIN și IF într-o formulă matrice pentru a crea un șir de text separat de virgule Numele jucătorilor care se referă la a Echipă valoarea roșu
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Red", B3: B8, "")) |
Utilizatorii Excel 2022 vor trebui să introducă această formulă ca funcție matrice apăsând CTRL + SHIFT + ENTER. Utilizatorii de versiuni ulterioare de Excel nu trebuie să urmeze acest pas.
Pentru a explica ce face această formulă, să o împărțim în pași:
Aceasta este formula noastră finală:
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Red", B3: B8, "")) |
În primul rând, valorile intervalului de celule sunt adăugate la formulă:
| 1 | = TEXTJOIN (",", TRUE, IF ({"Red"; "Blue"; "Blue"; "Red"; "Blue"; "Red"} = "Red", {"A"; "B"; "C"; "D"; "E"; "F"}, "")) |
Urmează lista de Echipă numele este comparat cu valoarea roșu:
| 1 | = TEXTJOIN (",", TRUE, IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}, {"A"; "B"; "C"; "D"; "E"; "F"} , "")) |
Funcția IF înlocuiește valorile TRUE cu Numele jucatorului, și valori FALSE cu „”
| 1 | = TEXTJOIN (",", TRUE, {"A"; ""; ""; "D"; ""; "F"}) |
Funcția TEXTJOIN combină apoi toate valorile matricei într-un șir de text. Am instruit funcția să ignore valorile goale și să adauge textul „,” între fiecare valoare. Aceasta produce rezultatul final:
| 1 | = "A, D, F" |
TextInscrieți-vă dacă - în pre-Excel 2022
Deoarece funcția TEXTJOIN nu este disponibilă înainte de versiunea Excel 2022, trebuie să rezolvăm această problemă într-un mod diferit. Funcția CONCATENATE este disponibilă, dar nu ia intervale de celule ca intrări sau permite operații de matrice și, prin urmare, suntem obligați să folosim o coloană de ajutor cu o funcție IF.
Următorul exemplu arată cum să utilizați o coloană de ajutor pentru a crea un șir de text separat de virgule Numele jucătorilor care se referă la a Echipă valoarea Roșu:
| 1 | = IF (C3 = "Roșu", B3 și ",", "") și D4 |
Primul pas din acest exemplu este de a utiliza o funcție IF pentru a reproduce condiția Echipa = Roșu. Dacă condiția este îndeplinită, Numele jucatorului valoarea este scrisă și urmată de delimitatorul ales „,“:
| 1 | = IF (C3 = "Roșu", B3 și ",", "") |
Apoi, putem crea o coloană care creează o listă a acestor valori într-o singură celulă, făcând referire și la celula de sub aceasta:
| 1 | = D3 și E4 |
Această formulă folosește caracterul & pentru a uni două valori împreună. Rețineți că funcția CONCATENATE ar putea fi utilizată pentru a crea exact același rezultat, dar metoda & este adesea preferată, deoarece este mai scurtă și clarifică ce acțiune efectuează formula.
Aceste două coloane de ajutor pot fi apoi combinate într-o singură formulă:
| 1 | = IF (C3 = "Roșu", B3 și ",", "") și D4 |
O celulă rezumativă poate face referire la prima valoare din Lista de jucători coloana de ajutor:
| 1 | = D3 |
Dacă valoarea finală „,” nu este necesară, o formulă IF imbricată suplimentară ar trebui adăugată la formulă:
| 1 | = IF (C3 = "Roșu", IF (D4 = "", B3, B3 și ","), "") și D4 |
Partea suplimentară a formulei este funcția IF de mai jos:
| 1 | IF (D4 = "", B3, B3 & ",") |
Această parte a formulei verifică valoarea celulei de sub ea. Dacă este goală, atunci această celulă trebuie să fie ultima Numele jucatorului în listă și numele este scris fără delimitator. Dacă celula de mai jos conține o valoare, atunci această celulă nu este ultima Numele jucatorului în listă și astfel se adaugă delimitatorul „,”.
TextInscrieți-vă dacă este în Foi de calcul Google
Aceste formule funcționează la fel în Foi de calcul Google ca în Excel, cu excepția faptului că funcția ARRAYFORMULA trebuie să fie utilizată în Foi de calcul Google pentru a evalua corect rezultatele. Acest lucru poate fi adăugat automat apăsând tastele CTRL + SHIFT + ENTER în timp ce editați formula:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Red", B3: B8, "")))) |
