Galite rasti ir paryškinti pasikartojančius įrašus tarp dviejų stulpelių naudodami „Google“ skaičiuoklių sąlyginio formatavimo funkciją.
Dirbdami „Google“ skaičiuoklėse su dideliais duomenų rinkiniais tikriausiai susidursite su problema, kai turėsite susidurti su daugybe pasikartojančių verčių. Kai kurie pasikartojantys įrašai įdedami tyčia, o kiti yra klaidos. Tai ypač aktualu, kai bendradarbiaujate tame pačiame lape su komanda.
Kalbant apie duomenų analizę „Google“ skaičiuoklėse, galimybė filtruoti dublikatus gali būti labai svarbi ir patogu. Nors „Google“ skaičiuoklės nepalaiko savojo palaikymo, kad būtų galima rasti pasikartojančius lapuose, ji siūlo kelis būdus, kaip palyginti, identifikuoti ir pašalinti pasikartojančius duomenis langeliuose.
Kartais norite palyginti kiekvieną stulpelio reikšmę su kitu stulpeliu ir sužinoti, ar jame nėra dublikatų, ir atvirkščiai. „Google“ skaičiuoklėse galite lengvai rasti dublikatus tarp dviejų stulpelių, naudodami sąlyginio formatavimo funkciją. Šiame straipsnyje parodysime, kaip palyginti du stulpelius „Google“ skaičiuoklėse ir rasti jų dublikatus.
Raskite pasikartojančius įrašus tarp dviejų stulpelių naudodami sąlyginį formatavimą
Sąlyginis formatavimas yra „Google“ skaičiuoklių funkcija, leidžianti vartotojui pritaikyti konkretų formatavimą, pvz., šrifto spalvą, piktogramas ir duomenų juostas langeliui arba langelių diapazonui, atsižvelgiant į tam tikras sąlygas.
Galite naudoti šį sąlyginį formatavimą norėdami paryškinti pasikartojančius įrašus tarp dviejų stulpelių, užpildydami langelius spalva arba pakeisdami teksto spalvą. Turite palyginti kiekvieną stulpelio reikšmę su kitu stulpeliu ir sužinoti, ar kuri nors reikšmė pasikartoja. Kad tai veiktų, kiekvienam stulpeliui turite taikyti sąlyginį formatavimą atskirai. Norėdami tai padaryti, atlikite šiuos veiksmus:
Atidarykite skaičiuoklę, kurioje norite patikrinti, ar „Google“ skaičiuoklėse nėra dublikatų. Pirmiausia pasirinkite pirmąjį stulpelį (A), kurį patikrinsite su B stulpeliu. Galite paryškinti visą stulpelį spustelėdami virš jo esančią stulpelio raidę.
Tada meniu juostoje spustelėkite meniu „Formatas“ ir pasirinkite „Sąlyginis formatavimas“.
Sąlyginio formatavimo meniu atidaromas dešinėje „Google“ lapų pusėje. Galite patvirtinti, kad langelių diapazonas yra tas, kurį pasirinkote parinktyje „Taikyti diapazonui“. Jei norite pakeisti diapazoną, spustelėkite „diapazono piktogramą“ ir pasirinkite kitą diapazoną.
Tada spustelėkite išskleidžiamąjį meniu „Formatavimo taisyklės“ ir pasirinkite parinktį „Tinkinta formulė“.
Dabar lauke „Vertė arba formulė“ turite įvesti pasirinktinę formulę.
Jei pasirinkote visą stulpelį (B:B), įveskite šią COUNTIF formulę į laukelį „Vertė arba formulė“, esantį Formato taisyklės:
=countif($B:$B,$A2)>0arba
Jei stulpelyje pasirinkote langelių diapazoną (tarkime, šimtas langelių, A2:A30), naudokite šią formulę:
=COUNTIF($B$2:$B$30, $A2)>0Įvesdami formulę būtinai pakeiskite visus formulės raidės „B“ atvejus pažymėto stulpelio raide. Pridedame ženklą „$“ prieš langelių nuorodas, kad jos būtų absoliutus diapazonas, todėl tai nepasikeis, kai taikome formulę.
Skiltyje Formatavimo stilius galite pasirinkti formatavimo stilių, kad paryškintumėte pasikartojančius elementus. Pagal numatytuosius nustatymus bus naudojama žalia užpildymo spalva.
Galite pasirinkti vieną iš iš anksto nustatytų formatavimo stilių spustelėdami „Numatytasis“, esantį skiltyje „Formatavimo stilius“, tada pasirinkę vieną iš išankstinių nustatymų.
Arba galite naudoti bet kurį iš septynių formatavimo įrankių (pusjuodis, kursyvas, pabrauktas, perbrauktas, teksto spalva, užpildymo spalva) skiltyje „Formatavimo stilius“, kad paryškintumėte dublikatus.
Čia pasirenkame pasikartojančių langelių užpildymo spalvą spustelėdami piktogramą „Užpildymo spalva“ ir pasirinkę „geltoną“ spalvą.
Pasirinkę formatavimą, spustelėkite „Atlikta“, kad paryškintumėte langelius.
Funkcija COUNTIF skaičiuoja, kiek kartų kiekviena langelio reikšmė „A stulpelyje“ rodoma „B stulpelyje“. Taigi, jei elementas B stulpelyje pasirodo net vieną kartą, formulė grąžina TRUE. Tada tas elementas bus paryškintas „A stulpelyje“, atsižvelgiant į jūsų pasirinktą formatavimą.
Tai neparyškina dublikatų, o paryškina elementus, kurių dublikatai yra B stulpelyje. Tai reiškia, kad kiekvienas geltonai paryškintas elementas turi dublikatus B stulpelyje.
Dabar turime taikyti sąlyginį formatavimą B stulpeliui naudodami tą pačią formulę. Norėdami tai padaryti, pasirinkite antrąjį stulpelį (B2:B30), eikite į meniu „Formatas“ ir pasirinkite „Sąlyginis formatavimas“.
Arba spustelėkite mygtuką „Pridėti kitą taisyklę“, esantį srityje „Sąlyginio formato taisyklės“.
Tada patvirtinkite diapazoną (B2:B30) laukelyje „Taikyti diapazonui“.
Tada parinktį „Formatuoti langelius, jei...“ nustatykite į „Custom formula is“ ir formulės laukelyje įveskite toliau pateiktą formulę:
=COUNTIF($A$2:$A$30, $B2)>0Pirmajame argumente naudojame A stulpelio diapazoną ($A$2:$A$30), o antrajame argumente „$B2“. Ši formulė patikrins langelio reikšmę „B stulpelyje“ pagal kiekvieną A stulpelio langelį. Jei randama atitiktis (dublikatas), sąlyginis formatavimas padidins tą elementą „B stulpelyje“.
Tada parinktyse „Formatavimo stilius“ nurodykite formatavimą ir spustelėkite „Atlikta“. Čia B stulpeliui pasirenkame oranžinę spalvą.
Taip bus paryškinti B stulpelio elementai, kurių A stulpelyje yra dublikatų. Dabar radote ir paryškinote pasikartojančius elementus tarp dviejų stulpelių.
Tikriausiai pastebėjote, nors A stulpelyje yra „Arcelia“ dublikatas, jis nėra paryškintas. Taip yra todėl, kad dublikato reikšmė yra tik viename stulpelyje (A), o ne tarp stulpelių. Todėl jis nėra paryškintas.
Pažymėkite dublikatus tarp dviejų stulpelių toje pačioje eilutėje
Taip pat galite paryškinti eilutes, kuriose yra tos pačios reikšmės (dublikatai) tarp dviejų stulpelių, naudodami sąlyginį formatavimą. Sąlyginio formatavimo taisyklė gali patikrinti kiekvieną eilutę ir paryškinti eilutes, kurių abiejuose stulpeliuose yra atitinkamų duomenų. Štai kaip tai darote:
Pirmiausia pasirinkite abu stulpelius, kuriuos norite palyginti, tada eikite į meniu „Formatas“ ir pasirinkite „Sąlyginis formatavimas“.
Sąlyginio formato taisyklių srityje patvirtinkite diapazoną lauke „Taikyti diapazonui“ ir išskleidžiamajame meniu „Formulės langeliai, jei...“ pasirinkite „Tinkinta formulė yra“.
Tada laukelyje „Vertė arba formulė“ įveskite toliau pateiktą formulę:
=$A2=$B2Ši formulė palygins du stulpelius po eilutės ir paryškins eilutes, kurių reikšmės yra identiškos (dublikatai). Kaip matote, čia įvesta formulė skirta tik pirmai pasirinkto diapazono eilutei, tačiau sąlyginio formatavimo funkcija formulė bus automatiškai pritaikyta visoms pasirinkto diapazono eilutėms.
Tada parinktyse „Formatavimo stilius“ nurodykite formatavimą ir spustelėkite „Atlikta“.
Kaip matote, bus paryškintos tik tos eilutės, kuriose yra atitinkamų duomenų (dublikatų) tarp dviejų stulpelių, o visų kitų dublikatų bus nepaisoma.
Pažymėkite pasikartojančius langelius keliuose stulpeliuose
Kai dirbate su didesnėmis skaičiuoklėmis, kuriose yra daug stulpelių, galbūt norėsite paryškinti visus pasikartojančius stulpelius, o ne tik vieną ar du stulpelius. Vis tiek galite naudoti sąlyginį formatavimą, kad paryškintumėte dublikatą keliuose stulpeliuose.
Pirmiausia pasirinkite visų stulpelių ir eilučių, kuriose norite ieškoti dublikatų, diapazoną, o ne tik vieną ar du stulpelius. Galite pasirinkti ištisus stulpelius laikydami nuspaudę klavišą Ctrl, tada spustelėdami raidę kiekvieno stulpelio viršuje. Arba taip pat galite spustelėti pirmą ir paskutinę savo diapazono langelius, laikydami nuspaudę klavišą Shift, kad vienu metu pasirinktumėte kelis stulpelius.
Pavyzdyje pasirenkame A2:C30.
Tada meniu spustelėkite parinktį „Formatas“ ir pasirinkite „Sąlyginis formatavimas“.
Sąlyginio formato taisyklėse Formato taisykles nustatykite į „Custom formula is“, tada lauke „Vertė arba formulė“ įveskite šią formulę:
=countif($A$2:$C$30,A2)>Prieš langelių nuorodas pridedame ženklą „$“, kad jie taptų absoliučiais stulpeliais, todėl tai nepasikeis, taikome formulę. Taip pat galite įvesti formulę be „$“ ženklų, ji veikia bet kuriuo atveju.
Tada pasirinkite formatavimą, kuriame norite paryškinti pasikartojančius langelius, naudodami parinktis „Formatavimo stilius“. Čia pasirenkame „geltoną“ užpildymo spalvą. Po to spustelėkite „Atlikta“.
Tai paryškins dublikatus visuose pasirinktuose stulpeliuose, kaip parodyta toliau.
Pritaikę sąlyginį formatavimą, bet kada galite redaguoti arba ištrinti sąlyginio formatavimo taisyklę.
Jei norite redaguoti dabartinę sąlyginio formatavimo taisyklę, pasirinkite bet kurį langelį su sąlyginiu formatavimu, meniu eikite į „Formatas“ ir pasirinkite „Sąlyginis formatavimas“.
Dešinėje bus atidaryta „Sąlyginio formato taisyklių“ sritis su dabartiniam pasirinkimui taikomų formato taisyklių sąrašu. Užvedus pelės žymeklį virš taisyklės, bus rodomas trynimo mygtukas, spustelėkite trynimo mygtuką, kad pašalintumėte taisyklę. Arba, jei norite redaguoti šiuo metu rodomą taisyklę, spustelėkite pačią taisyklę.
Jei norite pridėti kitą sąlyginį formatavimą, o ne esamą taisyklę, spustelėkite mygtuką „Pridėti kitą taisyklę“.
Suskaičiuokite dublikatus tarp dviejų stulpelių
Kartais norite suskaičiuoti, kiek kartų viename stulpelyje esanti reikšmė pasikartoja kitame stulpelyje. Tai galima lengvai padaryti naudojant tą pačią COUNTIF funkciją.
Norėdami sužinoti, kiek kartų A stulpelyje esanti reikšmė yra B stulpelyje, kito stulpelio langelyje įveskite šią formulę:
=COUNTIF($B$2:$B$30,$A2)Įveskite šią formulę į langelį C2. Ši formulė skaičiuoja, kiek kartų A2 langelio reikšmė yra stulpelyje (B2:B30), ir grąžina skaičių langelyje C2.
Kai įvedate formulę ir paspausite Enter, atsiras automatinio užpildymo funkcija. Spustelėkite „Varnelė“, kad automatiškai užpildytumėte šią formulę iki likusių langelių (C3:C30).
Jei automatinio pildymo funkcija nerodoma, spustelėkite mėlyną kvadratą apatiniame dešiniajame langelio C2 kampe ir vilkite jį žemyn, kad nukopijuotumėte C2 langelio formulę į langelius C3:C30.
1 palyginimo stulpelyje (C) dabar bus rodomas skaičius, kiek kartų kiekviena atitinkama A stulpelio reikšmė rodoma B stulpelyje. Pavyzdžiui, A2 arba Franklyn reikšmė B stulpelyje nerasta, todėl Funkcija COUNTIF grąžina „0“. O reikšmė „Loreta“ (A5) randama du kartus B stulpelyje, taigi, ji grąžina „2“.
Dabar turime pakartoti tuos pačius veiksmus, kad surastume pasikartojančius B stulpelio skaičius. Norėdami tai padaryti, D stulpelio D2 langelyje įveskite šią formulę (2 palyginimas):
=COUNTIF($A$2:$A$30, $B2)Šioje formulėje pakeiskite diapazoną nuo „$B$2:$B$30“ iki „$A$2:$A$30“ ir „$B2“ į „$A2“. Funkcija skaičiuoja, kiek kartų B2 langelio reikšmė yra stulpelyje A (A2:A30), ir grąžina skaičių langelyje D2.
Tada automatiškai užpildykite formulę į likusius langelius (D3:D30) D stulpelyje. Dabar „2 palyginimas“ parodys, kiek kartų kiekviena atitinkama B stulpelio reikšmė rodoma A stulpelyje. Pavyzdžiui, , B2 arba „Stark“ reikšmė A stulpelyje randama du kartus, taigi funkcija COUNTIF grąžina „2“.
Pastaba: Jei norite skaičiuoti visų stulpelių arba kelių stulpelių dublikatus, tiesiog turite pakeisti diapazoną pirmame funkcijos COUNTIF argumente į kelis stulpelius, o ne tik vieną stulpelį. Pavyzdžiui, pakeiskite diapazoną iš A2:A30 į A2:B30, kuris skaičiuos visus dublikatus dviejuose stulpeliuose, o ne viename.
Viskas.