Fungsi PILIH Excel. Formula dan Contoh

Tutorial menerangkan sintaks dan kegunaan asas bagi Fungsi PILIH Excel dan menyediakan beberapa contoh bukan remeh yang menunjukkan cara menggunakan formula CHOOSE. Ini adalah salah satu ciri Excel yang mungkin kelihatan tidak berguna dengan sendirinya, tetapi digabungkan dengan ciri lain ia memberikan beberapa faedah yang luar biasa. Pada peringkat paling asas, fungsi CHOOSE digunakan untuk mendapatkan nilai daripada senarai dengan menentukan kedudukan nilai tersebut. Kemudian dalam tutorial ini, anda akan menemui beberapa kegunaan lanjutan yang pastinya patut diterokai.

Fungsi PILIH Excel: Sintaks Asas dan Kegunaan

Fungsi CHOOSE dalam Excel direka untuk mengembalikan nilai daripada senarai berdasarkan kedudukan tertentu. Fungsi boleh didapati di Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 dan Excel 2007.

Sintaks fungsi CHOOSE adalah seperti berikut:

PILIH (Nombor_Indeks, nilai1, [nilai2],…)

Dónde:

Nombor_indeks (wajib): kedudukan nilai yang akan dikembalikan. Ia boleh menjadi sebarang nombor antara 1 dan 254, rujukan sel atau formula lain.

Nilai1, nilai2,…: senarai sehingga 254 nilai untuk dipilih. Nilai1 diperlukan, nilai lain adalah pilihan. Ini boleh menjadi nombor, nilai teks, rujukan sel, formula atau nama yang ditentukan. Berikut ialah contoh formula CHOOSE dalam bentuk paling mudah:

=PILIH(3, «Mike», «Sally», «Amy», «Neal»)

Formula mengembalikan «amy"kerana Nombor_indeks ialah 3 dan “Amy” ialah nilai ketiga dalam senarai:

Mungkin anda mungkin berminat: Cara Menggunakan Fungsi Kiri dan Kanan dalam Excel

Fungsi PILIH Excel: 3 Perkara Yang Perlu Diingati!

CHOOSE ialah fungsi yang sangat mudah dan anda tidak akan menemui sebarang kesukaran untuk melaksanakannya dalam lembaran kerja anda. Jika keputusan yang dikembalikan oleh formula CHOSEN anda tidak dijangka atau bukan hasil yang anda cari, ini mungkin disebabkan oleh sebab berikut:

1. Bilangan nilai untuk dipilih adalah terhad kepada 254.
2. Ralat dikembalikan.
3. Jika hujah Nombor_indeks ialah pecahan, ia dipotong kepada nombor bulat terendah.

Cara menggunakan fungsi CHOOSE dalam Excel – contoh formula

Contoh berikut menunjukkan cara CHOOSE boleh memanjangkan keupayaan fungsi Excel yang lain dan menyediakan penyelesaian alternatif kepada beberapa tugas biasa, malah tugasan yang dianggap tidak boleh dilaksanakan oleh ramai orang.

PILIH dalam Excel dan bukannya IF bersarang

Salah satu tugas yang paling biasa dalam Excel ialah mengembalikan nilai yang berbeza berdasarkan keadaan tertentu. Dalam kebanyakan kes, ini boleh dilakukan menggunakan pernyataan IF bersarang klasik. Tetapi fungsi CHOOSE Excel boleh menjadi alternatif yang cepat dan mudah difahami.

Contoh 1: Mengembalikan nilai yang berbeza bergantung pada keadaan

Katakan anda mempunyai lajur gred pelajar dan anda ingin melabelkan gred berdasarkan syarat berikut: Keputusan dan markah.

• miskin: 0 - 50
• Memuaskan: 51 - 100
• Baiklah: 101 - 150
• Cemerlang: lebih 151

Satu cara untuk melakukan ini ialah dengan menyarangkan beberapa formula IF di dalam satu sama lain:

=IF(B2>=151, "Cemerlang", IF(B2>=101, "Baik", JIKA(B2>=51, "Memuaskan", "Lemah")))

Cara lain ialah memilih label yang sepadan dengan keadaan:

=PILIH((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), «Lemah», «Memuaskan», «Baik», «Cemerlang»)

Cara formula ini berfungsi:

Dalam hujah Nombor_indeks, menilai setiap keadaan dan pulangan SEBENAR Jika syarat dipenuhi, SALAH sebaliknya. Sebagai contoh, nilai dalam sel B2 memenuhi tiga syarat pertama, jadi kami mendapat hasil perantaraan ini:

=PILIH(BENAR + BENAR + BENAR + SALAH, «Lemah», «Memuaskan», «Baik», «Cemerlang»)

Oleh kerana dalam kebanyakan formula cemerlang, BENAR sama dengan 1 dan SALAH sama dengan 0, formula kami mengalami transformasi ini:

=PILIH(1 + 1 + 1 + 0, "Lemah", "Memuaskan", "Baik", "Cemerlang")

Setelah operasi penambahan dilakukan, kami mempunyai:

=PILIH(3, "Lemah", "Memuaskan", "Baik", "Cemerlang")

Akibatnya, nilai ke-3 dalam senarai dikembalikan, iaitu "baik".

Petua:

• Untuk menjadikan formula lebih fleksibel, anda boleh menggunakan rujukan sel dan bukannya label berkod keras, contohnya:

=PILIH((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), $E$1, $E$2, $E$3, $E$4)

• Jika tiada syarat anda adalah BENAR, hujahnya Nombor_indeks akan ditetapkan kepada 0, memaksa formula anda mengembalikan #VALUE! kesilapan. Untuk mengelakkan ini, hanya bungkus CHOOSE dalam fungsi IFERROR seperti ini:

=IFERROR(PILIH((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), «Lemah», «Memuaskan», «Baik», «Cemerlang»), « »)

Contoh 2: Lakukan pengiraan yang berbeza bergantung pada keadaan

Anda boleh menggunakan fungsi CHOOSE Excel untuk melakukan pengiraan pada satu siri pengiraan/formula yang mungkin tanpa meletakkan berbilang penyata IF dalam satu sama lain. Sebagai contoh, mari kita mengira komisen setiap penjual berdasarkan jualan mereka:

• 5% – $0 hingga $50
• 7% – $51 hingga $100
• 10% – lebih daripada $101

Dengan jumlah jualan dalam B2, formula mengambil bentuk berikut:

=PILIH((B2>0) + (B2>=51) + (B2>=101), B2*5%, B2*7%, B2*10%)

Daripada mengekod keras peratusan ke dalam formula, anda boleh menanyakan sel yang sepadan dalam jadual rujukan anda, jika ada. Ingatlah untuk membetulkan rujukan menggunakan tanda $.

=PILIH((B2>0) + (B2>=51) + (B2>=101), B2*$E$2, B2*$E$3, B2*$E$4)

 Formula Pilih untuk menjana data rawak

Seperti yang anda ketahui, Microsoft Excel mempunyai fungsi khas untuk menjana integer rawak antara nombor bawah dan atas yang anda tentukan: fungsi RANDBETWEEN. Gunakan hujah index_num CHOOSE dan formula anda akan menjana hampir semua data rawak yang anda inginkan. Sebagai contoh, formula ini boleh menghasilkan senarai keputusan imbasan rawak:

=PILIH(RANDBETWEEN(1,4), “Lemah”, “Memuaskan”, “Baik”, “Cemerlang”)

Logik formula adalah jelas: RANDBETWEEN menjana nombor rawak dari 1 hingga 4 dan CHOOSE mengembalikan nilai yang sepadan daripada senarai empat nilai yang dipratakrifkan.

Nota: RANDBETWEEN ialah a fungsi meruap dan dikira semula dengan setiap perubahan yang anda buat pada lembaran kerja. Akibatnya, senarai nilai rawak anda juga akan berubah. Untuk mengelakkan perkara ini berlaku, anda boleh menggantikan formula dengan nilai anda menggunakan fungsi tersebut Gam khas.

Mungkin anda ingin tahu: Cara Memautkan Senarai Jatuh Turun dalam Excel

Formula Pilih untuk membuat Vlookup kiri

Jika anda pernah melakukan carian menegak dalam Excel, anda tahu bahawa fungsi VLOOKUP hanya boleh mencari lajur paling kiri. Dalam situasi di mana anda perlu mengembalikan nilai di sebelah kiri lajur carian, anda boleh menggunakan gabungan INDEKS / PADANAN atau menipu LIHAT bersarang fungsi CHOOSE di dalamnya. Begini caranya:

Katakan anda mempunyai senarai markah dalam lajur A, nama pelajar dalam lajur B, dan anda ingin mendapatkan semula markah pelajar tertentu. Oleh kerana lajur pulangan berada di sebelah kiri lajur carian, a Vlookup ralat pulangan biasa #N/A:

Untuk membetulkannya, dapatkan fungsi CHOOSE untuk menukar kedudukan lajur, memberitahu Excel bahawa lajur 1 ialah B dan lajur 2 ialah A:

=CHOOSE({1,2}, B2:B5, A2:A5)

Kerana kami menyediakan tatasusunan {1,2} dalam hujah nombor_indeks, fungsi CHOOSE menerima julat dalam argumen nilai (biasanya, ia tidak). Sekarang, masukkan formula di atas dalam hujah table_array de LIHAT:

=VLOOKUP(E1,CHOOSE({1,2}, B2:B5, A2:A5),2,FALSE)

Dengan ini, carian ke kiri dilakukan tanpa masalah!

Formula Pilih untuk kembali pada hari perniagaan berikutnya

Jika anda tidak pasti sama ada anda perlu pergi bekerja esok atau anda boleh tinggal di rumah dan menikmati hujung minggu anda, fungsi CHOOSE Excel boleh mengetahui bila hari kerja berikutnya. Dengan mengandaikan hari kerja anda ialah Isnin hingga Jumaat, formulanya adalah seperti berikut:

=HARI INI()+PILIH(HARI MINGGU(HARIINI()),1,1,1,1,1,3,2)

Sukar pada pandangan pertama, apabila melihat lebih dekat logik formula mudah diikuti:

HARI MINGGU(HARI INI()) mengembalikan nombor siri yang sepadan dengan tarikh hari ini, antara 1 (Ahad) hingga 7 (Sabtu). Nombor ini pergi ke hujah Nombor_indeks formula PILIH kami.

Nilai1 – nilai7 (1,1,1,1,1,3,2) menentukan bilangan hari untuk ditambahkan pada tarikh semasa. Jika hari ini hari Ahad – Khamis (indeks_nombor 1 – 5), tambah 1 untuk kembali pada hari berikutnya. Jika hari ini hari Jumaat (indeks_nombor 6), tambah 3 untuk kembali Isnin depan. Jika hari ini hari Sabtu (indeks_nombor 7), tambah 2 untuk kembali pada Isnin depan sekali lagi. Ya, semudah itu.

Pilih formula untuk mengembalikan nama hari/bulan tersuai dari tarikh

Dalam situasi di mana anda ingin mendapatkan nama hari dalam format standard, seperti nama penuh (Isnin, Selasa, dll.) atau nama pendek, anda boleh menggunakan fungsi TEKS seperti yang dijelaskan dalam contoh ini: Dapatkan hari dalam seminggu dari tarikh dalam Excel.

Jika anda ingin mengembalikan nama hari dalam minggu atau bulan dalam format tersuai, gunakan fungsi CHOOSE Excel seperti berikut. Untuk mendapatkan hari dalam seminggu:

=PILIH(HARI MINGGU(A2),»Su»,»Mo»,»Tu»,»Kami»,»Th»,»Fr»,»Sa»)

Untuk mendapatkan sebulan:

=PILIH(BULAN(A2), «Jan»,»Feb»,»Mac»,»Apr»,»Mei»,»Jun»,»Jul»,»Ogos»,»Sep»,»Okt»,»Nov »,»Dis»)

Di mana A2 ialah sel yang mengandungi tarikh asal.

Lihatlah: Bar Data Dalam Excel. Apakah Itu Dan Cara Menambahkannya

Pensamientos finales

Kami berharap tutorial ini telah memberi anda beberapa idea tentang cara anda boleh menggunakan fungsi CHOOSE Excel untuk menambah baik model data anda. Terima kasih kerana membaca dan kami berharap dapat berjumpa anda di sini minggu hadapan! Jika anda menyukai tutorial ini, anda boleh memberitahu kami pendapat anda di bahagian komen. Ingat bahawa fungsi ini boleh digunakan untuk banyak perkara, anda hanya perlu menyesuaikan data formula.