Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā salikt šūnu vērtības, pamatojoties uz kritērijiem, izmantojot funkciju TEXTJOIN programmā Excel un Google izklājlapas.
Funkcija TEXTJOIN
Programmas Excel 2022+ lietotājiem ir piekļuve funkcijai TEXTJOIN, kas apvieno vairākas teksta virknes, atdalot katru virkni ar noteiktu atdalītāju.
Piezīme: Google izklājlapu lietotāji var izmantot funkciju TEXTJOIN, taču tiem ir nedaudz atšķirīgs veids, kā ievadīt masīva formulas. Skatiet šīs sadaļas turpmāko sadaļu.
Šajā piemērā tiks izmantotas funkcijas TEXTJOIN un IF masīva formulā, lai izveidotu ar komatu atdalītu teksta virkni Spēlētāju vārdi kas attiecas uz a Komanda vērtība sarkans
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "sarkans", B3: B8, "")) |
Programmas Excel 2022 lietotājiem šī formula būs jāievada kā masīva funkcija, nospiežot CTRL + SHIFT + ENTER. Jaunāko Excel versiju lietotājiem šī darbība nav jāveic.
Lai izskaidrotu, ko šī formula dara, iedalīsim to soļos:
Šī ir mūsu galīgā formula:
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "sarkans", B3: B8, "")) |
Pirmkārt, šūnu diapazona vērtības tiek pievienotas formulai:
| 1 | = TEXTJOIN (",", TRUE, IF ({"Red"; "Blue"; "Blue"; "Red"; "Blue"; "Red"} = "Red", "" A ";" B "; "C"; "D"; "E"; "F"}, "")) |
Tālāk saraksts Komanda vārdus salīdzina ar vērtību sarkans:
| 1 | = TEXTJOIN (",", TRUE, IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}, {"A"; "B"; "C"; "D"; "E"; "F"} , "")) |
Funkcija IF aizstāj TRUE vērtības ar Spēlētāja vārds, un FALSE vērtības ar “”
| 1 | = TEXTJOIN (",", TRUE, {"A"; ""; ""; "D"; ""; "F"}) |
Funkcija TEXTJOIN apvieno visas masīva vērtības vienā teksta virknē. Mēs esam norādījuši šai funkcijai ignorēt tukšās vērtības un starp katru vērtību pievienot tekstu “,”. Tas rada gala rezultātu:
| 1 | = "A, D, F" |
TextJoin If - pirms Excel 2022
Tā kā funkcija TEXTJOIN nav pieejama pirms Excel 2022 versijas, mums šī problēma ir jāatrisina citādi. Funkcija CONCATENATE ir pieejama, taču šūnu diapazoni netiek ņemti par ievadi vai masīva darbības, tāpēc mums ir jāizmanto palīgkolonna ar IF funkciju.
Šis nākamais piemērs parāda, kā izmantot palīgu kolonnu, lai izveidotu ar komatu atdalītu teksta virkni Spēlētāju vārdi kas attiecas uz a Komanda vērtība Sarkans:
| 1 | = IF (C3 = "sarkans", B3 un ",", "") & D4 |
Pirmais solis šajā piemērā ir izmantot IF funkciju, lai atkārtotu nosacījumu Komanda = Sarkans. Ja nosacījums ir izpildīts, Spēlētāja vārds Vērtību raksta un tam seko izvēlētais norobežotājs “,”:
| 1 | = IF (C3 = "sarkans", B3 un ",", "") |
Tālāk mēs varam izveidot kolonnu, kas vienā šūnā izveido šo vērtību sarakstu, atsaucoties arī uz šūnu zem tās:
| 1 | = D3 un E4 |
Šī formula izmanto & rakstzīmi, lai apvienotu divas vērtības. Ņemiet vērā, ka funkciju CONCATENATE var izmantot, lai radītu tieši tādu pašu rezultātu, taču bieži vien priekšroka tiek dota metodei &, jo tā ir īsāka un padara skaidrāku, kādu darbību formula veic.
Šīs divas palīgu kolonnas var apvienot vienā formulā:
| 1 | = IF (C3 = "sarkans", B3 un ",", "") & D4 |
Kopsavilkuma šūna pēc tam var atsaukties uz pirmo vērtību Spēlētāju saraksts palīga kolonna:
| 1 | = D3 |
Ja galīgā vērtība “,” nav nepieciešama, formulai jāpievieno papildu ligzdota IF funkcija:
| 1 | = IF (C3 = "sarkans", IF (D4 = "", B3, B3 & ","), "") & D4 |
Formulas papildu daļa ir tālāk norādītā IF funkcija:
| 1 | IF (D4 = "", B3, B3 un ",") |
Šī formulas daļa pārbauda zem tās esošās šūnas vērtību. Ja tā ir tukša, šai šūnai jābūt pēdējai Spēlētāja vārds sarakstā un nosaukums ir rakstīts bez norobežotāja. Ja zemāk esošā šūna satur vērtību, šī šūna nav pēdējā Spēlētāja vārds sarakstā un tādējādi tiek pievienots atdalītājs “,”.
TextJoin Ja Google izklājlapās
Šīs formulas darbojas Google izklājlapās tāpat kā programmā Excel, izņemot to, ka funkcija ARRAYFORMULA ir jāizmanto Google izklājlapās, lai tā pareizi novērtētu rezultātus. To var automātiski pievienot, rediģējot formulu, nospiežot taustiņus CTRL + SHIFT + ENTER:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Red", B3: B8, "")))) |
