Semak sama ada nilai wujud dalam Lembaran Excel dan Google

Dalam artikel ini, kami akan meneroka formula yang paling biasa digunakan dalam lembaran Excel dan Google untuk memeriksa sama ada nilai wujud dalam lajur, baris, atau dalam pelbagai sel.

Sama ada anda seorang pengguna yang berpengalaman atau hanya mula mengetahui tentang program spreadsheet kegemaran anda, mengetahui bagaimana untuk mengenal pasti dengan cepat jika nilai tertentu hadir atau tidak hadir dalam julat tertentu dapat menjimatkan banyak masa dan usaha dalam analisis data anda. Ini boleh menjadi sangat berguna apabila bekerja dengan dataset yang besar atau semasa menjalankan penyelidikan. Di Microsoft Excel dan Google Sheets, terdapat beberapa formula untuk melaksanakan tugas ini. Dengan panduan langkah demi langkah kami, anda boleh menggunakannya dengan mudah ke spreadsheet anda, tanpa mengira struktur dan saiz dataset anda.

• Excel: Periksa sama ada nilai wujud dalam julat
  • Sekiranya nilai wujud dalam lajur
  • Sekiranya nilai berlaku berturut -turut
  • Sekiranya nilai muncul dalam julat
• Lebih banyak formula untuk mencari nilai carian dalam julat
• Helaian Google: Sekiranya nilai wujud dalam jarak
• Periksa sama ada nilai ada dalam senarai - Perlawanan Separa
• Sorot nilai yang terdapat dalam julat

Sekiranya nilai wujud dalam julat excel

Untuk memeriksa sama ada nilai tertentu wujud dalam pelbagai sel, dan jika ia kemudiannya kembali beberapa teks, dan jika ia tidak mengembalikan teks lain, anda boleh menggunakan gabungan fungsi IF dan Countif.

Jika (countif ( julat , nilai )> 0, "ya", "tidak")

Dalam formula ini, Countif mengira kejadian nilai yang diberikan dalam julat. Jika kiraan lebih besar daripada sifar, pernyataan IF mengembalikan "ya". Jika nilai tidak dijumpai dalam julat, fungsi countif membawa sifar, dan jika output "tidak".

Formula generik ini berfungsi dengan baik dalam semua senario yang mungkin.

Periksa sama ada nilai wujud dalam lajur

Untuk menguji sama ada nilai dalam C3 terdapat dalam lajur A (lebih tepat dalam julat A3: A20), anda boleh menggunakan formula ini:

=IF(COUNTIF($A$3:$A$20, C3)>0, "Yes", "No")

Sila perhatikan bahawa kami mengunci rujukan julat ($ A $ 3: $ A $ 20) dengan menggunakan tanda $ untuk formula untuk menyalin dengan betul dalam sel D4: D7:

Periksa sama ada nilai wujud berturut -turut

Untuk mengetahui sama ada nilai dalam B5 muncul dalam baris B2: I2, formula mengambil borang ini:

=IF(COUNTIF($B$2:$I$2, B5)>0, "Yes", "No")

Periksa sama ada nilai wujud dalam julat

Untuk memeriksa sama ada nilai tertentu berlaku dalam julat 2D, membekalkan rujukan julat yang sepadan dengan hujah pertama COUNTIF. Contohnya:

=IF(COUNTIF($A$3:$B$11, D3)>0, "Yes", "No")

Lebih banyak formula untuk mencari nilai carian dalam julat

IF Formula Countif adalah yang paling mudah tetapi bukan satu -satunya cara untuk mencari jika nilai tertentu muncul dalam julat. Berikut adalah beberapa penyelesaian alternatif.

Tidak diketahui dan perlawanan

Satu lagi cara untuk memeriksa sama ada nilai berlaku dalam senarai menggunakan perlawanan bersama -sama dengan fungsi ISNumber. Contohnya:

=IF(ISNUMBER(MATCH(C3, $A$3:$A$20, 0)), "Yes", "No")

Fungsi perlawanan mengembalikan kedudukan nilai carian (C3) dalam julat (A3: A20) sebagai nombor; Ralat #N/A jika tiada perlawanan dijumpai. Fungsi ISNumber menukarkan nombor kepada benar dan ralat palsu. Membungkus pembinaan ke jika memberikan "ya" atau "tidak" sebagai hasil akhir.

ISNA dan VLOOKUP

Penyelesaian yang lebih kompleks tetapi masih berfungsi menggunakan fungsi IF, ISNA dan VLOOKUP dalam satu formula:

=IF(ISNA(VLOOKUP(C3, $A$3:$A$20, 1, FALSE)), "No", "Yes")

Di sini, fungsi VLOOKUP mencari nilai carian adalah array yang ditentukan dan mengembalikan perlawanan tepat (palsu) dari lajur yang sama (1). Jika tiada perlawanan dijumpai, ralat #N/A dikembalikan. . Akhirnya, fungsi IF mengembalikan "tidak" untuk benar (tiada perlawanan) dan "ya" untuk palsu (perlawanan).

Untuk maklumat lanjut, sila lihat cara menggunakan ISNA dengan VLOOKUP di Excel.

ISNA dan perlawanan

Penyelesaian ini sangat mirip dengan yang sebelumnya kecuali anda menggunakan perlawanan dan bukannya VLOOKUP untuk memeriksa julat untuk perlawanan:

=IF(ISNA(MATCH(C3, $A$3:$A$20, 0)), "No", "Yes")

Petua. Jika matlamat anda adalah untuk mencari nilai carian dalam satu lajur dan mengembalikan nilai yang sepadan dari lajur lain, kemudian gunakan fungsi VLOOKUP atau XLOOKUP dalam bentuk asasnya.

Sekiranya nilai wujud dalam Lembaran Google

Dalam Helaian Google, anda boleh menyemak sama ada nilai wujud dalam julat menggunakan formula yang sama yang kami gunakan dalam Excel.

Sebagai contoh, untuk mencari sama ada nilai dalam D3 berlaku dalam julat A3: B11, formula dalam E4 adalah:

=IF(COUNTIF($A$3:$B$11, D3)>0, "Yes", "No")

Anda boleh menemui beberapa lagi formula dalam spreadsheet sampel kami, yang boleh didapati pada akhir tutorial ini.

Periksa sama ada nilai wujud dalam julat - perlawanan separa

Untuk melihat sama ada mana -mana sel dalam julat mengandungi substring yang diberikan, letakkan watak Wildcard (asterisk) di kedua -dua belah nilai carian supaya countif mencarinya di mana -mana sahaja di dalam sel:

Jika (countif ( julat , " * nilai *")> 0, "ya", "tidak")

Sebagai contoh, formula di bawah akan memeriksa sama ada sel dalam julat A3: B11 mengandungi perkataan "epal":

=IF(COUNTIF(A3:B11, "*apple*")>0, "Yes", "No")

Jika anda merujuk kepada sel tertentu dan bukannya nilai yang dikodkan, maka concatenate asterisk sebelum dan selepas rujukan sel seperti ini:

=IF(COUNTIF($A$3:$B$11,"*"&D3&"*")>0, "Yes", "No")

Sorot nilai yang wujud dalam julat

Cara yang lebih visual untuk mengenal pasti nilai -nilai yang wujud dalam julat tertentu boleh menonjolkannya dengan pemformatan bersyarat Excel. Langkah -langkahnya adalah:

1. Pilih sel dengan nilai yang anda ingin cari dalam julat (C3: C7 dalam kes kami).
2. Pada tab Home , dalam kumpulan Styles , klik pemformatan bersyarat> peraturan baru .
3. Dalam tetingkap Peraturan Pemformatan baru , pilih Gunakan formula untuk menentukan sel -sel yang hendak dipenuhi .
4. Masukkan salah satu formula ini dalam nilai format di mana formula ini adalah kotak yang benar :

   =MATCH(C3, $A$3:$A$20, 0)

   =COUNTIF($A$3:$A$20, C3)>0

   Di mana C3 adalah sel teratas dengan nilai sasaran dan $ A $ 3: $ A $ 20 adalah julat untuk diperiksa.

5. Klik butang Format ... dan konfigurasikan pemformatan yang dikehendaki.
6. Klik OK dua kali untuk menutup kedua -dua tingkap dialog.

Selesai! Semua nilai yang wujud dalam julat (padanan tepat) diserlahkan:

Untuk menyerlahkan nilai sepadan sebahagiannya , ubah formula seperti yang ditunjukkan di bawah - menyusun asterisk pada kedua -dua belah rujukan nilai carian:

=MATCH("*"&C3&"*", $A$3:$A$20, 0)

=COUNTIF($A$3:$A$20, "*"&C3&"*")>0

Petua. Formula yang sama boleh digunakan dengan pemformatan bersyarat Google Sheets untuk menyerlahkan perlawanan tepat atau separa dalam julat dalam hamparan Google.

Sekarang anda dilengkapi dengan pengetahuan dan kemahiran untuk memeriksa sama ada nilai wujud dalam lajur, baris atau julat dengan keyakinan dan kemudahan. Terima kasih kerana membaca!

Amalkan buku kerja

Periksa sama ada nilai wujud dalam julat dalam excel (.xlsx file) Cari jika nilai wujud dalam julat dalam helaian Google (lembaran dalam talian)

Atas ialah kandungan terperinci Semak sama ada nilai wujud dalam Lembaran Excel dan Google. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!