Anda boleh mencari dan menyerlahkan entri pendua antara dua lajur menggunakan ciri Pemformatan Bersyarat dalam Helaian Google.
Semasa bekerja dalam Helaian Google dengan set data yang besar, anda mungkin menghadapi masalah di mana anda perlu berurusan dengan banyak nilai pendua. Walaupun beberapa entri pendua diletakkan dengan sengaja manakala yang lain adalah kesilapan. Ini benar terutamanya apabila anda bekerjasama pada helaian yang sama dengan pasukan.
Apabila ia datang untuk menganalisis data pada Helaian Google, dapat menapis pendua boleh menjadi penting dan mudah. Walaupun Helaian Google tidak mempunyai sebarang sokongan asli untuk mencari pendua dalam helaian, ia menawarkan beberapa cara untuk membandingkan, mengenal pasti dan mengalih keluar data pendua dalam sel.
Kadangkala, anda ingin membandingkan setiap nilai dalam lajur dengan lajur lain dan mencari jika terdapat sebarang pendua di dalamnya dan begitu juga sebaliknya. Dalam Helaian Google, anda boleh mencari pendua dengan mudah antara dua lajur dengan bantuan ciri pemformatan bersyarat. Dalam artikel ini, kami akan menunjukkan kepada anda cara membandingkan dua lajur dalam Helaian Google dan mencari pendua antara lajur tersebut.
Cari Entri Pendua Antara Dua Lajur menggunakan Pemformatan Bersyarat
Pemformatan bersyarat ialah ciri dalam Helaian Google yang membolehkan pengguna menggunakan pemformatan tertentu seperti warna fon, ikon dan bar data pada sel atau julat sel berdasarkan syarat tertentu.
Anda boleh menggunakan pemformatan bersyarat ini untuk menyerlahkan entri pendua antara dua lajur, sama ada dengan mengisi sel dengan warna atau menukar warna teks. Anda perlu membandingkan setiap nilai dalam lajur dengan lajur lain dan mencari sama ada sebarang nilai diulang. Untuk ini berfungsi, anda perlu menggunakan pemformatan bersyarat pada setiap lajur secara berasingan. Ikuti langkah-langkah ini untuk melakukannya:
Buka hamparan yang ingin anda semak untuk pendua dalam Helaian Google. Mula-mula, pilih lajur pertama (A) untuk menyemak dengan lajur B. Anda boleh menyerlahkan keseluruhan lajur dengan mengklik pada huruf lajur di atasnya.
Kemudian, klik menu 'Format' dari bar menu dan pilih 'Pemformatan bersyarat'.
Menu Pemformatan Bersyarat dibuka di sebelah kanan helaian google. Anda boleh mengesahkan julat sel adalah apa yang anda pilih di bawah pilihan 'Gunakan pada julat'. Jika anda ingin menukar julat, klik 'ikon julat' dan pilih julat yang berbeza.
Kemudian, klik menu lungsur di bawah 'Peraturan format' dan pilih pilihan 'Formula tersuai ialah'.
Sekarang, anda perlu memasukkan formula tersuai dalam kotak 'Nilai atau formula'.
Jika anda memilih keseluruhan lajur (B:B), masukkan formula COUNTIF berikut ke dalam kotak 'Nilai atau formula' di bawah Peraturan format:
=countif($B:$B,$A2)>0Atau,
Jika anda memilih julat sel dalam lajur (katakan seratus sel, A2:A30), gunakan formula ini:
=COUNTIF($B$2:$B$30, $A2)>0Apabila anda memasukkan formula, pastikan anda menggantikan semua contoh huruf 'B' dalam formula dengan huruf lajur yang telah anda serlahkan. Kami menambah tanda '$' sebelum rujukan sel untuk menjadikannya julat mutlak, jadi ia tidak mengubah kami menggunakan formula.
Dalam bahagian Gaya pemformatan, anda boleh memilih gaya pemformatan untuk menyerlahkan item pendua. Secara lalai, ia akan menggunakan warna isian hijau.
Anda boleh memilih salah satu gaya pemformatan pratetap dengan mengklik pada 'Lalai' di bawah pilihan 'Gaya pemformatan', kemudian memilih salah satu pratetap.
Atau, anda boleh menggunakan mana-mana daripada tujuh alatan pemformatan (Tebal, Italic, Garis bawah, Garisan Telus, Warna Teks, Warna Isi) di bawah bahagian 'Gaya pemformatan' untuk menyerlahkan pendua.
Di sini, kami memilih warna isian untuk sel pendua dengan mengklik ikon 'Warna isi' dan memilih warna 'kuning'.
Sebaik sahaja anda memilih pemformatan, klik 'Selesai' untuk menyerlahkan sel.
Fungsi COUNTIF mengira berapa kali setiap nilai sel dalam 'Lajur A' muncul dalam 'Lajur B'. Jadi jika item muncul sekali pun dalam lajur B, formula mengembalikan BENAR. Kemudian item itu akan diserlahkan dalam 'Lajur A' berdasarkan pemformatan yang anda pilih.
Ini tidak menyerlahkan pendua, sebaliknya ia menyerlahkan item yang mempunyai pendua dalam Lajur B. Ini bermakna setiap item yang diserlahkan kuning mempunyai pendua dalam Lajur B.
Sekarang, kita perlu menggunakan pemformatan bersyarat pada Lajur B menggunakan formula yang sama. Untuk berbuat demikian, pilih lajur kedua (B2:B30), pergi ke menu 'Format' dan pilih 'Pemformatan bersyarat'.
Sebagai alternatif, klik butang 'Tambah peraturan lain' di bawah anak tetingkap 'Peraturan format bersyarat'.
Seterusnya, sahkan julat (B2:B30) dalam kotak 'Gunakan pada julat'.
Kemudian, tetapkan pilihan 'Format sel jika..' kepada 'Formula tersuai ialah' dan masukkan formula di bawah dalam kotak formula:
=COUNTIF($A$2:$A$30, $B2)>0Di sini, kami menggunakan julat lajur A ($A$2:$A$30) dalam hujah pertama dan '$B2' dalam hujah kedua. Formula ini akan menyemak nilai sel dalam 'lajur B' terhadap setiap sel dalam lajur A. Jika padanan (pendua) ditemui, maka pemformatan bersyarat akan menaikkan item itu dalam 'lajur B'
Kemudian, nyatakan pemformatan dalam pilihan 'Gaya pemformatan' dan klik 'Selesai'. Di sini, kami memilih warna oren untuk lajur B.
Ini akan menyerlahkan item lajur B yang mempunyai pendua dalam lajur A. Kini, anda telah menemui dan menyerlahkan item pendua antara dua lajur.
Anda mungkin perasan, walaupun terdapat pendua untuk 'Arcelia' dalam lajur A, ia tidak diserlahkan. Ini kerana nilai pendua hanya dalam satu lajur (A) bukan antara lajur. Oleh itu, ia tidak ditonjolkan.
Serlahkan Pendua Antara Dua lajur dalam Baris yang Sama
Anda juga boleh menyerlahkan baris yang mempunyai nilai yang sama (pendua) antara dua lajur menggunakan pemformatan bersyarat. Peraturan pemformatan bersyarat boleh menyemak setiap baris dan menyerlahkan baris yang mempunyai data yang sepadan dalam kedua-dua lajur. Begini cara anda melakukan ini:
Mula-mula, pilih kedua-dua lajur yang ingin anda bandingkan, kemudian pergi ke menu 'Format' dan pilih 'Pemformatan bersyarat'.
Dalam anak tetingkap Peraturan format bersyarat, sahkan julat dalam kotak 'Gunakan pada julat' dan pilih 'Formula tersuai ialah' daripada lungsur turun 'Sel formula jika..'.
Kemudian, masukkan formula di bawah dalam kotak 'Nilai atau formula':
=$A2=$B2Formula ini akan membandingkan dua lajur baris demi baris dan menyerlahkan baris yang mempunyai nilai yang sama (pendua). Seperti yang anda lihat, formula yang dimasukkan di sini hanya untuk baris pertama julat yang dipilih, tetapi formula akan digunakan secara automatik pada semua baris dalam julat yang dipilih oleh ciri pemformatan bersyarat.
Kemudian, nyatakan pemformatan daripada pilihan 'Gaya pemformatan' dan klik 'Selesai'.
Seperti yang anda lihat, hanya baris yang mempunyai data yang sepadan (pendua) antara dua lajur akan diserlahkan dan semua pendua lain akan diabaikan.
Serlahkan Sel Pendua dalam Berbilang Lajur
Apabila bekerja dengan hamparan yang lebih besar dengan banyak lajur, anda mungkin mahu menyerlahkan semua pendua yang muncul merentas berbilang lajur dan bukannya hanya satu atau dua lajur. Anda masih boleh menggunakan pemformatan bersyarat untuk menyerlahkan pendua dalam berbilang lajur.
Mula-mula, pilih julat semua lajur dan baris yang anda ingin cari untuk pendua dan bukannya satu atau dua lajur sahaja. Anda boleh memilih keseluruhan lajur dengan menahan kekunci Ctrl, kemudian mengklik pada huruf di bahagian atas setiap lajur. Sebagai alternatif, anda juga boleh mengklik pada sel pertama dan terakhir dalam julat anda sambil juga menahan kekunci Shift untuk memilih berbilang lajur sekaligus.
Dalam contoh, kami memilih A2:C30.
Kemudian, klik pilihan 'Format' dalam menu dan pilih 'Pemformatan bersyarat'.
Dalam Peraturan format bersyarat, tetapkan peraturan Format kepada 'Formula tersuai ialah', dan kemudian masukkan formula berikut dalam kotak 'Nilai atau Formula':
=countif($A$2:$C$30,A2)>Kami menambah tanda '$' sebelum rujukan sel untuk menjadikannya lajur mutlak, jadi ia tidak mengubah kami menggunakan formula. Anda juga boleh memasukkan formula tanpa tanda '$', ia berfungsi sama ada cara.
Kemudian, pilih pemformatan yang anda ingin serlahkan sel pendua menggunakan pilihan 'Gaya pemformatan'. Di sini, kami memilih warna isian 'Kuning'. Selepas itu, klik ‘Selesai’.
Ini akan menyerlahkan pendua merentas semua lajur yang anda pilih, seperti yang ditunjukkan di bawah.
Selepas menggunakan pemformatan bersyarat, anda boleh mengedit atau memadam peraturan pemformatan bersyarat pada bila-bila masa yang anda mahu.
Jika anda ingin mengedit peraturan pemformatan bersyarat semasa, pilih mana-mana sel dengan pemformatan bersyarat, pergi ke 'Format' pada menu dan pilih 'Pemformatan bersyarat'.
Ini akan membuka anak tetingkap 'Peraturan format bersyarat' di sebelah kanan dengan senarai peraturan format yang digunakan pada pemilihan semasa. Apabila anda mengarahkan tetikus anda pada peraturan, ia akan menunjukkan kepada anda butang padam, klik pada butang padam untuk mengalih keluar peraturan. Atau, jika anda ingin mengedit peraturan yang sedang dipaparkan, klik pada peraturan itu sendiri.
Jika anda ingin menambah pemformatan bersyarat lain ke atas peraturan semasa, klik butang 'Tambah peraturan lain'.
Kira Pendua Antara Dua Lajur
Kadangkala, anda ingin mengira bilangan kali nilai dalam satu lajur berulang dalam lajur lain. Ia boleh dilakukan dengan mudah menggunakan fungsi COUNTIF yang sama.
Untuk mencari bilangan kali nilai dalam lajur A wujud dalam lajur B, masukkan formula berikut dalam sel dalam lajur lain:
=COUNTIF($B$2:$B$30,$A2)Masukkan formula ini dalam sel C2. Formula ini mengira bilangan kali nilai dalam sel A2 wujud dalam lajur (B2:B30) dan mengembalikan kiraan dalam sel C2.
Apabila anda menaip formula dan tekan Enter, ciri Auto-Isi akan muncul, klik 'Tick mark' untuk mengisi secara automatik formula ini ke seluruh sel (C3:C30).
Jika ciri autoisi tidak muncul, klik segi empat sama biru di sudut kanan bawah sel C2 dan seretnya ke bawah untuk menyalin formula dalam sel C2 ke sel C3:C30.
Lajur 'Perbandingan 1' (C) kini akan menunjukkan kepada anda bilangan kali setiap nilai yang sepadan dalam lajur A muncul dalam lajur B. Contohnya, nilai A2 atau "Franklyn" tidak ditemui dalam lajur B, jadi, Fungsi COUNTIF mengembalikan "0". Dan nilai "Loreta" (A5) ditemui dua kali dalam lajur B, oleh itu, ia mengembalikan "2".
Sekarang, kita perlu mengulangi langkah yang sama untuk mencari kiraan pendua lajur B. Untuk melakukannya, masukkan formula berikut dalam sel D2 dalam lajur D (Perbandingan 2):
=COUNTIF($A$2:$A$30,$B2)Dalam formula ini, gantikan julat daripada '$B$2:$B$30' kepada '$A$2:$A$30' dan '$B2' kepada '$A2'. Fungsi mengira bilangan kali nilai dalam sel B2 wujud dalam lajur A (A2:A30) dan mengembalikan kiraan dalam sel D2.
Kemudian, isikan formula secara automatik ke seluruh sel (D3:D30) dalam lajur D. Sekarang, 'Perbandingan 2' akan menunjukkan kepada anda bilangan kali setiap nilai yang sepadan dalam lajur B muncul dalam lajur A. Contohnya , nilai B2, atau "Stark" ditemui dua kali dalam lajur A, jadi, fungsi COUNTIF mengembalikan "2".
Nota: Jika anda ingin mengira pendua merentas semua lajur atau berbilang lajur, anda hanya perlu menukar julat dalam hujah pertama fungsi COUNTIF kepada berbilang lajur dan bukannya satu lajur sahaja. Sebagai contoh, tukar julat daripada A2:A30 kepada A2:B30, yang akan mengira semua pendua dalam dua lajur dan bukannya hanya satu.
itu sahaja.