Varat atrast un izcelt dublētos ierakstus starp divām kolonnām, izmantojot Google izklājlapu funkciju Nosacītā formatēšana.
Strādājot Google izklājlapās ar lielām datu kopām, jūs, iespējams, saskaraties ar problēmu, kas saistīta ar daudzām dublētām vērtībām. Daži ierakstu dublikāti tiek ievietoti apzināti, bet citi ir kļūdaini. Tas jo īpaši attiecas uz gadījumiem, kad sadarbojaties ar vienu lapu ar komandu.
Runājot par datu analīzi pakalpojumā Google izklājlapas, iespēja filtrēt dublikātus var būt ļoti svarīga un ērta. Lai gan pakalpojumā Google izklājlapas nav nekāda vietējā atbalsta dublikātu atrašanai izklājlapās, tā piedāvā vairākus veidus, kā salīdzināt, identificēt un noņemt dublikātus šūnās.
Dažreiz jūs vēlaties salīdzināt katru kolonnas vērtību ar citu kolonnu un noskaidrot, vai tajā nav dublikātu, un otrādi. Google izklājlapās varat viegli atrast dublikātus starp divām kolonnām, izmantojot nosacījumformatēšanas funkciju. Šajā rakstā mēs parādīsim, kā salīdzināt divas Google izklājlapu kolonnas un atrast starp tām dublikātus.
Atrodiet dublētus ierakstus starp divām kolonnām, izmantojot nosacījumu formatējumu
Nosacītā formatēšana ir Google izklājlapu funkcija, kas ļauj lietotājam lietot noteiktus formatējumus, piemēram, fonta krāsu, ikonas un datu joslas šūnai vai šūnu diapazonam, pamatojoties uz noteiktiem nosacījumiem.
Varat izmantot šo nosacījumu formatējumu, lai izceltu dublikātus starp divām kolonnām, aizpildot šūnas ar krāsu vai mainot teksta krāsu. Jums ir jāsalīdzina katra kolonnas vērtība ar citu kolonnu un jānoskaidro, vai kāda vērtība atkārtojas. Lai tas darbotos, katrai kolonnai atsevišķi jāpiemēro nosacījumformatējums. Lai to izdarītu, veiciet tālāk norādītās darbības.
Atveriet izklājlapu, kurā vēlaties pārbaudīt, vai pakalpojumā Google izklājlapas nav dublikātu. Vispirms atlasiet pirmo kolonnu (A), lai pārbaudītu to ar kolonnu B. Varat iezīmēt visu kolonnu, noklikšķinot uz kolonnas burta virs tās.
Pēc tam izvēlņu joslā noklikšķiniet uz izvēlnes “Formatēt” un atlasiet “Nosacījuma formatējums”.
Google lapu labajā pusē tiek atvērta izvēlne Nosacītā formatēšana. Varat apstiprināt, ka šūnu diapazons ir tas, ko atlasījāt sadaļā “Lietot diapazonam”. Ja vēlaties mainīt diapazonu, noklikšķiniet uz "diapazona ikonas" un izvēlieties citu diapazonu.
Pēc tam noklikšķiniet uz nolaižamās izvēlnes sadaļā Formatēšanas noteikumi un atlasiet opciju Pielāgota formula.
Tagad lodziņā Vērtība vai formula jāievada pielāgota formula.
Ja atlasījāt visu kolonnu (B:B), lodziņā “Vērtība vai formula” sadaļā Formātu noteikumi ievadiet šādu COUNTIF formulu:
=countif($B:$B,$A2)>0vai,
Ja kolonnā atlasījāt šūnu diapazonu (piemēram, simts šūnu, A2:A30), izmantojiet šo formulu:
=COUNTIF($B$2:$B$30, $A2)>0Ievadot formulu, noteikti aizstājiet visus burta “B” gadījumus formulā ar iezīmētās kolonnas burtu. Mēs pievienojam zīmi “$” pirms šūnu atsaucēm, lai padarītu tās par absolūtu diapazonu, tāpēc tas nemaina, mēs izmantojam formulu.
Sadaļā Formatēšanas stils varat izvēlēties formatējuma stilu dublēto vienumu izcelšanai. Pēc noklusējuma tas izmantos zaļo aizpildījuma krāsu.
Varat izvēlēties vienu no iepriekš iestatītajiem formatēšanas stiliem, sadaļā Formatēšanas stils noklikšķinot uz Noklusējums un pēc tam atlasot vienu no sākotnējiem iestatījumiem.
Vai arī varat izmantot jebkuru no septiņiem formatēšanas rīkiem (treknraksts, slīpraksts, pasvītrojums, pārsvītrojums, teksta krāsa, aizpildījuma krāsa), kas atrodas sadaļā Formatēšanas stils, lai izceltu dublikātus.
Šeit mēs izvēlamies aizpildījuma krāsu dublētajām šūnām, noklikšķinot uz ikonas “Aizpildījuma krāsa” un atlasot “dzelteno” krāsu.
Kad esat atlasījis formatējumu, noklikšķiniet uz "Gatavs", lai iezīmētu šūnas.
Funkcija COUNTIF uzskaita, cik reižu katra šūnas vērtība “A slejā” parādās “B kolonnā”. Tātad, ja vienums B kolonnā parādās pat vienu reizi, formula atgriež TRUE. Pēc tam šis vienums tiks izcelts A slejā, pamatojoties uz jūsu izvēlēto formatējumu.
Tas neizceļ dublikātus, bet gan izceļ vienumus, kuriem B slejā ir dublikāti. Tas nozīmē, ka katram dzelteni iezīmētajam vienumam ir dublikāti B slejā.
Tagad mums ir jāpiemēro nosacījumformatējums kolonnai B, izmantojot to pašu formulu. Lai to izdarītu, atlasiet otro kolonnu (B2:B30), dodieties uz izvēlni "Formatēt" un atlasiet "Nosacījuma formatēšana".
Varat arī noklikšķināt uz pogas Pievienot citu noteikumu rūtī Nosacījuma formāta noteikumi.
Pēc tam lodziņā “Lietot diapazonam” apstipriniet diapazonu (B2:B30).
Pēc tam iestatiet opciju Formatēt šūnas, ja... uz "Pielāgota formula ir" un ievadiet tālāk norādīto formulu formulas lodziņā:
=COUNTIF($A$2:$A$30, $B2)>0Šeit mēs izmantojam kolonnas A diapazonu ($A$2:$A$30) pirmajā argumentā un "$B2" otrajā argumentā. Šī formula pārbaudīs šūnas vērtību kolonnā B, salīdzinot ar katru A kolonnas šūnu. Ja tiek atrasta atbilstība (dublikāts), nosacījumformatēšana paaugstinās šo vienumu kolonnā B.
Pēc tam norādiet formatējumu opcijās "Formatēšanas stils" un noklikšķiniet uz "Gatavs". Šeit mēs izvēlamies B kolonnas oranžo krāsu.
Tas iezīmēs B kolonnas vienumus, kuriem A slejā ir dublikāti. Tagad esat atradis un iezīmējis dublikātus starp divām kolonnām.
Jūs droši vien pamanījāt, lai gan A slejā ir “Arcelia” dublikāts, tas nav izcelts. Tas ir tāpēc, ka dublikāta vērtība ir tikai vienā kolonnā (A), nevis starp kolonnām. Tāpēc tas nav izcelts.
Iezīmējiet dublikātus starp divām kolonnām tajā pašā rindā
Varat arī izcelt rindas, kurām ir vienādas vērtības (dublikāti) starp divām kolonnām, izmantojot nosacījumu formatējumu. Nosacītā formatēšanas kārtula var pārbaudīt katru rindu un izcelt rindas, kurām ir atbilstoši dati abās kolonnās. Lūk, kā to izdarīt:
Vispirms atlasiet abas kolonnas, kuras vēlaties salīdzināt, pēc tam atveriet izvēlni Formatēt un atlasiet Nosacījuma formatējums.
Nosacītā formāta noteikumu rūtī apstipriniet diapazonu lodziņā “Lietot diapazonam” un nolaižamajā izvēlnē “Formula šūnas, ja...” izvēlieties “Pielāgota formula ir”.
Pēc tam lodziņā “Vērtība vai formula” ievadiet tālāk norādīto formulu.
=$A2=$B2Šī formula salīdzinās abas kolonnas pa rindiņām un iezīmēs rindas, kurām ir identiskas vērtības (dublikāti). Kā redzat, šeit ievadītā formula ir paredzēta tikai atlasītā diapazona pirmajai rindai, taču nosacītā formatēšanas līdzeklis formula tiks automātiski lietota visām atlasītā diapazona rindām.
Pēc tam norādiet formatējumu no "Formatēšanas stils" opcijām un noklikšķiniet uz "Gatavs".
Kā redzat, tiks izceltas tikai tās rindas, kurām ir atbilstoši dati (dublikāti) starp divām kolonnām, un visi pārējie dublikāti tiks ignorēti.
Iezīmējiet dublētās šūnas vairākās kolonnās
Strādājot ar lielākām izklājlapām ar daudzām kolonnām, iespējams, vēlēsities izcelt visus dublikātus, kas parādās vairākās kolonnās, nevis tikai vienā vai divās kolonnās. Joprojām varat izmantot nosacījumu formatējumu, lai izceltu dublikātu vairākās kolonnās.
Vispirms atlasiet visu kolonnu un rindu diapazonu, kurās vēlaties meklēt dublikātus, nevis tikai vienu vai divas kolonnas. Varat atlasīt visas kolonnas, turot nospiestu taustiņu Ctrl un pēc tam noklikšķinot uz burta katras kolonnas augšdaļā. Varat arī noklikšķināt uz pirmās un pēdējās šūnas savā diapazonā, vienlaikus turot nospiestu taustiņu Shift, lai vienlaikus atlasītu vairākas kolonnas.
Piemērā mēs atlasām A2:C30.
Pēc tam izvēlnē noklikšķiniet uz opcijas "Formatēt" un atlasiet "Nosacījuma formatējums".
Nosacījuma formāta kārtulās iestatiet Formāta kārtulas uz Pielāgota formula un pēc tam lodziņā Vērtība vai formula ievadiet tālāk norādīto formulu.
=countif($A$2:$C$30,A2)>Mēs pievienojam zīmi “$” pirms šūnu atsaucēm, lai padarītu tās par absolūtām kolonnām, tāpēc tas nemainās, mēs izmantojam formulu. Varat arī ievadīt formulu bez “$” zīmēm, tā darbojas jebkurā gadījumā.
Pēc tam izvēlieties formatējumu, kurā vēlaties izcelt dublētās šūnas, izmantojot opcijas Formatēšanas stils. Šeit mēs izvēlamies "dzelteno" aizpildījuma krāsu. Pēc tam noklikšķiniet uz "Gatavs".
Tas iezīmēs dublikātus visās jūsu atlasītajās kolonnās, kā parādīts tālāk.
Pēc nosacījumformatēšanas lietošanas varat rediģēt vai dzēst nosacījumformatēšanas kārtulu jebkurā laikā.
Ja vēlaties rediģēt pašreizējo nosacījumformatēšanas kārtulu, atlasiet jebkuru šūnu ar nosacījumu formatējumu, izvēlnē dodieties uz Formatēt un atlasiet Nosacījuma formatējums.
Labajā pusē tiks atvērta rūts Nosacījuma formāta noteikumi ar formāta noteikumu sarakstu, kas tiek lietots pašreizējai atlasei. Novietojot peles kursoru virs kārtulas, tiks parādīta dzēšanas poga. Noklikšķiniet uz dzēšanas pogas, lai noņemtu kārtulu. Vai arī, ja vēlaties rediģēt kārtulu, kas pašlaik tiek rādīta, noklikšķiniet uz pašas kārtulas.
Ja pašreizējai kārtulai vēlaties pievienot citu nosacījumu formatējumu, noklikšķiniet uz pogas Pievienot citu noteikumu.
Saskaitiet dublikātus starp divām kolonnām
Dažreiz vēlaties saskaitīt, cik reižu vērtība vienā kolonnā atkārtojas citā kolonnā. To var viegli izdarīt, izmantojot to pašu COUNTIF funkciju.
Lai uzzinātu, cik reižu vērtība kolonnā A pastāv B kolonnā, ievadiet tālāk norādīto formulu citas kolonnas šūnā.
=COUNTIF($B$2:$B$30,$A2)Ievadiet šo formulu šūnā C2. Šī formula saskaita, cik reižu vērtība šūnā A2 pastāv kolonnā (B2:B30), un atgriež skaitu šūnā C2.
Ievadot formulu un nospiežot taustiņu Enter, parādīsies automātiskās aizpildīšanas funkcija. Noklikšķiniet uz “Atzīmēt atzīmi”, lai automātiski aizpildītu šo formulu pārējās šūnās (C3:C30).
Ja automātiskās aizpildīšanas funkcija neparādās, noklikšķiniet uz zilā kvadrāta šūnas C2 apakšējā labajā stūrī un velciet to uz leju, lai kopētu formulu šūnā C2 uz šūnām C3:C30.
Kolonnā “Salīdzinājums 1” (C) tagad tiks parādīts, cik reižu katra atbilstošā vērtība kolonnā A parādās B kolonnā. Piemēram, A2 vai “Franklyn” vērtība B kolonnā nav atrasta, tāpēc Funkcija COUNTIF atgriež “0”. Un vērtība “Loreta” (A5) tiek atrasta divreiz B ailē, tātad atgriež “2”.
Tagad mums ir jāatkārto tās pašas darbības, lai atrastu kolonnas B dublikātu skaitu. Lai to izdarītu, D kolonnas šūnā D2 ievadiet šādu formulu (2. salīdzinājums):
=COUNTIF($A$2:$A$30, $B2)Šajā formulā aizstājiet diapazonu no “$B$2:$B$30” līdz “$A$2:$A$30” un “$B2” uz “$A2”. Funkcija saskaita, cik reižu vērtība šūnā B2 pastāv kolonnā A (A2:A30), un atgriež skaitu šūnā D2.
Pēc tam automātiski aizpildiet formulu līdz pārējām šūnām (D3:D30) kolonnā D. Tagad 'Salīdzinājums 2' parādīs, cik reižu katra atbilstošā vērtība kolonnā B parādās kolonnā A. Piemēram, , B2 jeb “Stark” vērtība kolonnā A ir atrodama divreiz, tāpēc funkcija COUNTIF atgriež “2”.
Piezīme: Ja vēlaties uzskaitīt dublikātus visās kolonnās vai vairākās kolonnās, jums vienkārši ir jāmaina diapazons funkcijas COUNTIF pirmajā argumentā uz vairākām kolonnām, nevis tikai vienu kolonnu. Piemēram, mainiet diapazonu no A2:A30 uz A2:B30, kas skaitīs visus dublikātus divās kolonnās, nevis tikai vienā.
Tieši tā.