Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip sujungti ląstelių vertes pagal kriterijus naudojant „TEXTJOIN“ funkciją „Excel“ ir „Google“ skaičiuoklėse.
Funkcija TEXTJOIN
„Excel 2022+“ vartotojai turi prieigą prie funkcijos „TEXTJOIN“, kuri sujungia kelias teksto eilutes ir atskiria kiekvieną eilutę nurodytu skiriamuoju ženklu.
Pastaba: „Google“ skaičiuoklių vartotojai gali naudoti funkciją „TEXTJOIN“, tačiau turi šiek tiek kitokį būdą įvesti masyvo formules. Žr. Vėlesnį šios temos skyrių.
Šiame pavyzdyje masyvo formulėje bus naudojamos funkcijos TEXTJOIN ir IF, kad būtų sukurta kableliais atskirta teksto eilutė Žaidėjų vardai kurie susiję su a Komanda vertė Raudona
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Raudona", B3: B8, "")) |
„Excel 2022“ vartotojai turės įvesti šią formulę kaip masyvo funkciją paspausdami CTRL + SHIFT + ENTER. Vėlesnių „Excel“ versijų naudotojams šio veiksmo atlikti nereikia.
Norėdami paaiškinti, ką daro ši formulė, suskirstykite ją į veiksmus:
Tai yra mūsų galutinė formulė:
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Raudona", B3: B8, "")) |
Pirma, ląstelių formulės vertės pridedamos prie formulės:
| 1 | = TEXTJOIN (",", TRUE, IF ({"Raudona"; "Mėlyna"; "Mėlyna"; "Raudona"; "Mėlyna"; "Raudona"} = "Raudona", {"A"; "B"; „C“; „D“; „E“; „F“}, „“)) |
Toliau sąrašas Komanda pavadinimai yra lyginami su verte Raudona:
| 1 | = TEXTJOIN (",", TRUE, IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}, {"A"; "B"; "C"; "D"; "E"; "F"} , "")) |
Funkcija IF pakeičia TRUE reikšmes į Žaidėjo vardasir FALSE reikšmės su „“
| 1 | = TEXTJOIN (",", TRUE, {"A"; ""; ""; "D"; ""; "F"}) |
Funkcija TEXTJOIN sujungia visas masyvo reikšmes į vieną teksto eilutę. Mes nurodėme funkcijai ignoruoti tuščias reikšmes ir pridėti tekstą „,“ tarp kiekvienos vertės. Taip gaunamas galutinis rezultatas:
| 1 | = "A, D, F" |
„TextJoin If“ - „Excel 2022“
Funkcija TEXTJOIN nepasiekiama iki „Excel 2022“ versijos, todėl turime išspręsti šią problemą kitaip. Funkcija CONCATENATE yra prieinama, tačiau ji neima ląstelių diapazonų kaip įvesties ar leidžia masyvo operacijoms, todėl mes turime naudoti pagalbinę stulpelį su IF funkcija.
Šiame kitame pavyzdyje parodyta, kaip naudojant pagalbinį stulpelį sukurti kableliais atskirtą teksto eilutę Žaidėjų vardai kurie susiję su a Komanda vertė Raudona:
| 1 | = IF (C3 = "raudona", B3 ir ",", "") & D4 |
Pirmasis šio pavyzdžio žingsnis yra naudoti IF funkciją, kad pakartotumėte būklę Komanda = Raudona. Jei sąlyga įvykdyta, Žaidėjo vardas reikšmė parašoma, o po jos eina pasirinktas ribotuvas „,“:
| 1 | = IF (C3 = "raudona", B3 ir ",", "") |
Toliau galime sukurti stulpelį, kuris sudarytų šių verčių sąrašą į vieną langelį, taip pat nurodydamas langelį po juo:
| 1 | = D3 ir E4 |
Ši formulė naudoja simbolį &, kad sujungtų dvi reikšmes. Atminkite, kad funkcija CONCATENATE gali būti naudojama tiksliai tokiam rezultatui sukurti, tačiau dažnai pirmenybė teikiama & metodui, nes jis yra trumpesnis ir aiškiau parodo, kokį veiksmą atlieka formulė.
Tada šiuos du pagalbinius stulpelius galima sujungti į vieną formulę:
| 1 | = IF (C3 = "raudona", B3 ir ",", "") & D4 |
Tada suvestinės langelis gali nurodyti pirmąją reikšmę Žaidėjų sąrašas pagalbinė skiltis:
| 1 | = D3 |
Jei galutinė reikšmė „,“ nereikalinga, į formulę reikia pridėti papildomą įdėtą IF funkciją:
| 1 | = IF (C3 = "raudona", IF (D4 = "", B3, B3 & ","), "") ir D4 |
Papildoma formulės dalis yra toliau pateikta IF funkcija:
| 1 | IF (D4 = "", B3, B3 ir ",") |
Ši formulės dalis tikrina žemiau esančios ląstelės vertę. Jei jis tuščias, ši ląstelė turi būti paskutinė Žaidėjo vardas sąraše ir pavadinimas parašytas be skiriamųjų ženklų. Jei žemiau esančiame langelyje yra reikšmė, tai šis langelis nėra paskutinis Žaidėjo vardas sąraše ir taip pridedamas ribotuvas „,“.
„TextJoin“, jei yra „Google“ skaičiuoklėse
Šios formulės veikia taip pat „Google“ skaičiuoklėse kaip ir „Excel“, išskyrus tai, kad norint teisingai įvertinti rezultatus, „Google“ skaičiuoklėse turi būti naudojama funkcija ARRAYFORMULA. Tai galima automatiškai pridėti paspaudus klavišus CTRL + SHIFT + ENTER, redaguojant formulę:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Raudona", B3: B8, "")))) |
