Maaari mong mahanap at i-highlight ang mga duplicate na entry sa pagitan ng dalawang column gamit ang feature na Conditional Formatting sa Google Sheets.
Habang nagtatrabaho sa Google Sheets na may malalaking set ng data, malamang na magkaroon ka ng problema kung saan kailangan mong harapin ang maraming duplicate na value. Habang ang ilang mga duplicate na entry ay sadyang inilalagay habang ang iba ay mga pagkakamali. Ito ay totoo lalo na kapag nakikipagtulungan ka sa parehong sheet sa isang koponan.
Pagdating sa pagsusuri ng data sa Google Sheets, maaaring maging mahalaga at maginhawa ang kakayahang mag-filter ng mga duplicate. Bagama't ang Google Sheets ay walang anumang katutubong suporta para sa paghahanap ng mga duplicate sa mga sheet, nag-aalok ito ng ilang paraan upang ihambing, tukuyin, at alisin ang duplicate na data sa mga cell.
Minsan, gusto mong ihambing ang bawat halaga sa isang column sa isa pang column at hanapin kung mayroong anumang mga duplicate dito at vise versa. Sa Google Sheets, madali kang makakahanap ng mga duplicate sa pagitan ng dalawang column sa tulong ng feature na conditional formatting. Sa artikulong ito, ipapakita namin sa iyo kung paano ihambing ang dalawang column sa Google Sheets at maghanap ng mga duplicate sa pagitan ng mga ito.
Maghanap ng Mga Duplicate na Entry sa Pagitan ng Dalawang Column gamit ang Conditional Formatting
Ang conditional formatting ay isang feature sa Google Sheets na nagbibigay-daan sa user na maglapat ng mga partikular na formatting gaya ng kulay ng font, icon, at data bar sa isang cell o hanay ng mga cell batay sa ilang partikular na kundisyon.
Maaari mong gamitin ang conditional formatting na ito upang i-highlight ang mga duplicate na entry sa pagitan ng dalawang column, alinman sa pamamagitan ng pagpuno sa mga cell ng kulay o pagbabago ng kulay ng text. Kailangan mong ihambing ang bawat halaga sa isang column laban sa isa pang column at hanapin kung ang anumang halaga ay mauulit. Para gumana ito, kailangan mong ilapat ang conditional formatting sa bawat column nang hiwalay. Sundin ang mga hakbang na ito upang gawin iyon:
Buksan ang spreadsheet na gusto mong tingnan kung may mga duplicate sa Google Sheets. Una, piliin ang unang column (A) upang suriin sa column B. Maaari mong i-highlight ang buong column sa pamamagitan ng pag-click sa column letter sa itaas nito.
Pagkatapos, i-click ang menu na ‘Format’ mula sa menu bar at piliin ang ‘Conditional formatting’.
Ang menu ng Conditional Formatting ay bubukas sa kanang bahagi ng google sheets. Maaari mong kumpirmahin na ang hanay ng cell ay kung ano ang iyong pinili sa ilalim ng opsyong 'Ilapat sa hanay'. Kung gusto mong baguhin ang range, i-click ang ‘range icon’ at pumili ng ibang range.
Pagkatapos, i-click ang drop-down sa ilalim ng 'Format rules' at piliin ang 'Custom formula is' na opsyon.
Ngayon, kailangan mong maglagay ng custom na formula sa kahon ng 'Halaga o formula'.
Kung pinili mo ang isang buong column (B:B), ilagay ang sumusunod na COUNTIF formula sa kahon ng ‘Halaga o formula’ sa ilalim ng Mga panuntunan sa format:
=countif($B:$B,$A2)>0o kaya,
Kung pumili ka ng hanay ng mga cell sa isang column (sabihin ang isang daang cell, A2:A30), gamitin ang formula na ito:
=COUNTIF($B$2:$B$30, $A2)>0Kapag ipinasok mo ang formula, tiyaking palitan ang lahat ng instance ng letrang 'B' sa formula ng titik ng column na iyong na-highlight. Idinaragdag namin ang sign na '$' bago ang mga reference ng cell upang gawin silang ganap na saklaw, kaya hindi ito nagbabago na inilalapat namin ang formula.
Sa seksyong Istilo ng pag-format, maaari mong piliin ang istilo ng pag-format para sa pag-highlight ng mga duplicate na item. Bilang default, gagamitin nito ang kulay berdeng fill.
Maaari kang pumili ng isa sa mga preset na istilo ng pag-format sa pamamagitan ng pag-click sa 'Default' sa ilalim ng mga opsyon sa 'Formatting style', pagkatapos ay pagpili ng isa sa mga preset.
O kaya, maaari mong gamitin ang alinman sa pitong tool sa pag-format (Bold, Italic, Underline, Strikethrough, Text color, Fill color) sa ilalim ng seksyong ‘Formatting style’ para i-highlight ang mga duplicate.
Dito, pumipili kami ng fill color para sa mga duplicate na cell sa pamamagitan ng pag-click sa icon na 'Fill color' at pagpili sa 'dilaw' na kulay.
Sa sandaling napili mo ang pag-format, i-click ang 'Tapos na' upang i-highlight ang mga cell.
Binibilang ng function na COUNTIF kung gaano karaming beses lumilitaw ang bawat value ng cell sa 'Column A' sa 'Column B'. Kaya kung ang isang item ay lilitaw kahit isang beses sa column B, ang formula ay nagbabalik ng TRUE. Pagkatapos ay mai-highlight ang item na iyon sa 'Column A' batay sa pag-format na iyong pinili.
Hindi nito hina-highlight ang mga duplicate, sa halip ay hina-highlight nito ang mga item na may mga duplicate sa Column B. Ibig sabihin, ang bawat yellow highlighted na item ay may mga duplicate sa Column B.
Ngayon, kailangan nating ilapat ang conditional formatting sa Column B gamit ang parehong formula. Upang gawin iyon, piliin ang pangalawang column (B2:B30), pumunta sa menu na ‘Format’, at piliin ang ‘Conditional formatting’.
Bilang kahalili, i-click ang button na ‘Magdagdag ng isa pang panuntunan’ sa ilalim ng pane ng ‘Mga tuntunin sa kondisyong format.
Susunod, kumpirmahin ang hanay (B2:B30) sa kahon na ‘Ilapat sa hanay’.
Pagkatapos, itakda ang opsyong 'Format cells if..' sa 'Custom formula is' at ilagay ang formula sa ibaba sa kahon ng formula:
=COUNTIF($A$2:$A$30, $B2)>0Dito, ginagamit namin ang hanay A na hanay ($A$2:$A$30) sa unang argumento at '$B2' sa pangalawang argumento. Susuriin ng formula na ito ang halaga ng cell sa 'column B' laban sa bawat cell sa column A. Kung may nakitang tugma (duplicate), pagkatapos ay matataas ng conditional formatting ang item na iyon sa 'column B'
Pagkatapos, tukuyin ang pag-format sa mga opsyon sa 'Estilo ng pag-format' at i-click ang 'Tapos na'. Dito, pinipili namin ang kulay kahel para sa column B.
Iha-highlight nito ang mga item sa column B na may mga duplicate sa column A. Ngayon, nakita mo at na-highlight mo ang mga duplicate na item sa pagitan ng dalawang column.
Marahil ay napansin mo, bagama't may duplicate para sa 'Arcelia' sa column A, hindi ito naka-highlight. Ito ay dahil ang duplicate na halaga ay nasa isang column (A) lamang hindi sa pagitan ng mga column. Samakatuwid, hindi ito naka-highlight.
I-highlight ang Mga Duplicate sa Pagitan ng Dalawang column sa Parehong Row
Maaari mo ring i-highlight ang mga row na may parehong mga halaga (mga duplicate) sa pagitan ng dalawang column gamit ang conditional formatting. Maaaring suriin ng tuntunin ng kondisyonal na pag-format ang bawat row at i-highlight ang mga row na may tugmang data sa parehong column. Narito kung paano mo ito gagawin:
Una, piliin ang parehong column na gusto mong ikumpara, pagkatapos ay pumunta sa menu na ‘Format’ at piliin ang ‘Conditional formatting’.
Sa pane ng Mga panuntunan sa Kondisyon na format, kumpirmahin ang hanay sa kahon na 'Ilapat sa hanay' at piliin ang 'Custom na formula ay' mula sa drop-down na 'Mga formula cell kung..'.
Pagkatapos, ilagay ang formula sa ibaba sa kahon ng 'Halaga o formula':
=$A2=$B2Ihahambing ng formula na ito ang dalawang column na row-by-row at i-highlight ang mga row na may magkaparehong value (mga duplicate). Tulad ng nakikita mo, ang formula na ipinasok dito ay para lamang sa unang hilera ng napiling hanay, ngunit ang formula ay awtomatikong ilalapat sa lahat ng mga hilera sa napiling hanay sa pamamagitan ng tampok na conditional formating.
Pagkatapos, tukuyin ang pag-format mula sa mga opsyon sa 'Estilo ng pag-format' at i-click ang 'Tapos na'.
Gaya ng nakikita mo, ang mga row lang na may tumutugmang data (mga duplicate) sa pagitan ng dalawang column ang iha-highlight at lahat ng iba pang duplicate ay hindi papansinin.
I-highlight ang Mga Duplicate na Cell sa Maramihang Column
Kapag nagtatrabaho sa mas malalaking spreadsheet na may maraming column, maaaring gusto mong i-highlight ang lahat ng duplicate na lumalabas sa maraming column sa halip na isa o dalawang column lang. Maaari mo pa ring gamitin ang conditional formatting upang i-highlight ang duplicate sa maraming column.
Una, piliin ang hanay ng lahat ng column at row na gusto mong hanapin ng mga duplicate sa halip na isa o dalawang column lang. Maaari kang pumili ng buong column sa pamamagitan ng pagpindot sa Ctrl key, pagkatapos ay pag-click sa titik sa tuktok ng bawat column. Bilang kahalili, maaari ka ring mag-click sa una at huling mga cell sa iyong hanay habang pinipigilan din ang Shift key upang pumili ng maraming column nang sabay-sabay.
Sa halimbawa, pinipili namin ang A2:C30.
Pagkatapos, i-click ang opsyong ‘Format’ sa menu at piliin ang ‘Conditional formatting’.
Sa Conditional format rules, itakda ang Format rules sa 'Custom formula is', at pagkatapos ay ilagay ang sumusunod na formula sa 'Value o Formula' na kahon:
=countif($A$2:$C$30,A2)>Idinaragdag namin ang sign na '$' bago ang mga cell reference upang gawin silang ganap na mga column, kaya hindi ito nagbabago na inilalapat namin ang formula. Maaari mo ring ipasok ang formula nang walang '$' na mga palatandaan, ito ay gumagana sa alinmang paraan.
Pagkatapos, piliin ang pag-format kung saan mo gustong i-highlight ang mga duplicate na cell gamit ang mga opsyon sa 'Estilo ng pag-format'. Dito, pinipili namin ang 'Yellow' na kulay ng fill. Pagkatapos nito, i-click ang 'Tapos na'.
Iha-highlight nito ang mga duplicate sa lahat ng column na iyong pinili, gaya ng ipinapakita sa ibaba.
Pagkatapos ilapat ang conditional formatting, maaari mong i-edit o tanggalin ang conditional formatting rule anumang oras na gusto mo.
Kung gusto mong i-edit ang kasalukuyang tuntunin sa conditional formatting, pumili ng anumang cell na may conditional formatting, pumunta sa 'Format' sa menu, at piliin ang 'Conditional formatting'.
Bubuksan nito ang pane ng 'Conditional format rules' sa kanan na may listahan ng mga panuntunan sa format na inilapat sa kasalukuyang pagpili. Kapag ini-hover mo ang iyong mouse sa ibabaw ng panuntunan, ipapakita nito sa iyo ang delete button, i-click ang delete button upang alisin ang panuntunan. O, kung gusto mong i-edit ang panuntunang kasalukuyang ipinapakita, i-click ang mismong panuntunan.
Kung gusto mong magdagdag ng isa pang kondisyonal na pag-format sa kasalukuyang panuntunan, i-click ang button na ‘Magdagdag ng isa pang panuntunan’.
Bilangin ang mga Duplicate sa pagitan ng Dalawang Column
Minsan, gusto mong bilangin kung ilang beses umuulit ang isang value sa isang column sa isa pang column. Madali itong magawa gamit ang parehong COUNTIF function.
Upang mahanap ang dami ng beses na mayroong value sa column A sa column B, ilagay ang sumusunod na formula sa isang cell sa isa pang column:
=COUNTIF($B$2:$B$30,$A2)Ilagay ang formula na ito sa cell C2. Binibilang ng formula na ito ang dami ng beses na umiiral ang value sa cell A2 sa column (B2:B30) at ibinabalik ang bilang sa cell C2.
Kapag nai-type mo ang formula at pinindot ang Enter, lalabas ang feature na Auto-Fill, i-click ang ‘Tik mark’ para awtomatikong punan ang formula na ito sa iba pang mga cell (C3:C30).
Kung hindi lalabas ang feature na auto-fill, i-click ang asul na parisukat sa kanang sulok sa ibaba ng cell C2 at i-drag ito pababa upang kopyahin ang formula sa cell C2 sa mga cell C3:C30.
Ipapakita na ngayon sa iyo ng column (C) ng 'Paghahambing 1' ang dami ng beses na lumilitaw ang bawat katumbas na halaga sa column A sa column B. Halimbawa, ang value ng A2, o "Franklyn" ay hindi matatagpuan sa column B, kaya, ang Ang COUNTIF function ay nagbabalik ng "0". At ang halagang "Loreta" (A5) ay matatagpuan nang dalawang beses sa column B, kaya, ito ay nagbabalik ng "2".
Ngayon, kailangan nating ulitin ang parehong mga hakbang upang mahanap ang mga duplicate na bilang ng column B. Upang gawin iyon, ilagay ang sumusunod na formula sa cell D2 sa column D (Paghahambing 2):
=COUNTIF($A$2:$A$30,$B2)Sa formula na ito, palitan ang hanay mula sa '$B$2:$B$30' hanggang '$A$2:$A$30' at '$B2' hanggang '$A2'. Binibilang ng function ang dami ng beses na umiiral ang value sa cell B2 sa column A (A2:A30) at ibinabalik ang bilang sa cell D2.
Pagkatapos, awtomatikong punan ang formula sa iba pang mga cell (D3:D30) sa column D. Ngayon, ipapakita sa iyo ng 'Paghahambing 2' ang dami ng beses na lumilitaw ang bawat katumbas na halaga sa column B sa column A. Halimbawa , ang halaga ng B2, o "Stark" ay makikita ng dalawang beses sa column A, kaya, ang COUNTIF function ay nagbabalik ng "2".
Tandaan: Kung gusto mong bilangin ang mga duplicate sa lahat ng column o maramihang column, kailangan mo lang baguhin ang range sa unang argument ng COUNTIF function sa maraming column sa halip na isang column lang. Halimbawa, baguhin ang hanay mula A2:A30 hanggang A2:B30, na bibilangin ang lahat ng duplicate sa dalawang column sa halip na isa lang.
Ayan yun.