10 fungsi excel paling penting
1. Fungsi JIKA
Fungsi IF mengembalikan nilai jika kondisi yang Anda tentukan TRUE, jika tidak, nilai lainnya. Sederhananya, fungsi IF dapat menguji suatu kondisi terlebih dahulu dan mengembalikan nilai berdasarkan hasil dari kondisi tersebut.
Sintaksis
JIKA(uji_logis, nilai_jika_benar, nilai_jika_false)
Argumen
- logika_test: Kondisi yang ingin Anda evaluasi.
- value_if_true: Nilai yang ingin Anda peroleh jika kondisi ini BENAR.
- value_if_false: Nilai yang ingin diperoleh jika kondisi ini FALSE.
Komentar
- Jumlah maksimum kondisi bersarang yang dapat Anda lakukan adalah 64.
- Anda dapat menggunakan operator perbandingan untuk mengevaluasi suatu kondisi.
Contoh
Pada contoh di bawah, kami menggunakan operator perbandingan untuk mengevaluasi kondisi yang berbeda.
- Kami menggunakan teks tertentu untuk mendapatkan hasil apakah kondisinya terpenuhi atau tidak.
- Anda juga dapat menggunakan TRUE dan FALSE untuk mendapatkan hasilnya.
- Jika Anda melewatkan penentuan nilai untuk mendapatkan hasil jika kondisinya BENAR, maka hasilnya akan nol.
- Dan jika Anda melewatkan penentuan nilai untuk mendapatkan hasil jika kondisinya FALSE maka akan menghasilkan nol.
Pada contoh di bawah, kita menggunakan fungsi IF untuk membuat rumus bertingkat.
Kita menentukan suatu kondisi dan jika kondisi tersebut salah, kita menggunakan IF lain untuk mengevaluasi kondisi lain dan melakukan tugas, dan jika kondisi tersebut FALSE, kita menggunakan IF lain.
Dengan cara ini, kita menggunakan IF sebanyak lima kali untuk membuat rumus bertumpuk. Anda dapat menggunakan hal yang sama sebanyak 64 kali untuk rumus bersarang.
2.Fungsi IFERROR
Fungsi IFERROR mengembalikan nilai tertentu jika terjadi kesalahan. Dengan kata sederhana, ia dapat menguji nilai dan jika nilai itu adalah kesalahan, ia akan mengembalikan nilai yang Anda tentukan.
Sintaksis
IFERROR(nilai, nilai_jika_kesalahan)
Argumen
- value: Nilai yang ingin Anda uji kesalahannya.
- value_if_error: Nilai yang ingin Anda dapatkan kembali ketika terjadi kesalahan.
Komentar
- Fungsi IFERROR menyangkut terjadinya kesalahan, bukan jenis kesalahannya.
- Jika Anda mengabaikan nilai atau value_if_error maka hasilnya akan menjadi 0.
- Itu dapat menguji #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? dan #NULL!.
- Jika Anda mengevaluasi sebuah array, itu akan mengembalikan array hasil untuk setiap elemen yang ditentukan.
Contoh
Pada contoh di bawah, kami menggunakan fungsi IFERROR untuk menggantikan #DIV/0! dengan teks penuh makna.
IFERROR hanya kompatibel dengan versi 2007 dan sebelumnya. Untuk mengatasi masalah ini, Anda dapat menggunakan ISERROR.
3. Fungsi TRUNC
Fungsi TRUNC mengembalikan bilangan bulat setelah memotong angka aslinya. Secara sederhana, ini menghilangkan tempat desimal dari suatu angka dengan presisi tertentu dan kemudian mengembalikan bagian bilangan bulat dari hasilnya.
Sintaksis
TRUNC(angka, [angka_digit])
Argumen
- number: Nomor yang ingin Anda potong.
- [num_digits]: Angka untuk menentukan presisi pemotongan angka.
Komentar
- Jika Anda mengabaikan penentuan kelipatan maka akan terjadi kesalahan.
- Itu dibulatkan dari nol.
- Jika Anda memiliki dua kelipatan dalam jarak yang sama, ini akan menghasilkan kelipatan lebih tinggi dari angka yang Anda bulatkan.
Contoh
Pada contoh di bawah, kami menggunakan TRUNC untuk memotong data guna menghapus waktu dari tanggal.
4. Fungsi SUMIF
Fungsi SUMIF mengembalikan jumlah angka yang memenuhi kondisi yang Anda tentukan . Secara sederhana, ini hanya mempertimbangkan dan menghitung jumlah nilai yang memenuhi kondisi.
Sintaksis
SUMIF(rentang, kriteria, [jumlah_rentang])
Argumen
- rentang: Rentang sel yang kriterianya ingin Anda periksa.
- kriteria: kriteria yang dapat berupa angka, teks, ekspresi, referensi sel, atau fungsi.
- [sum_range]: Rentang sel yang berisi nilai yang ingin Anda jumlahkan.
Komentar
- Jika sum_range dihilangkan, sel-sel dalam rentang tersebut akan dijumlahkan.
- Pastikan untuk menggunakan tanda kutip ganda untuk menentukan kriteria teks atau yang menyertakan simbol matematika, yang harus diapit tanda kutip ganda.
- Besar kecilnya rentang kriteria dan rentang jumlah harus sama besarnya.
Contoh
Pada contoh di bawah ini, kita telah menetapkan A1:A9 sebagai rentang kriteria dan B1:B9 sebagai rentang jumlah dan setelah itu kita telah menentukan kriteria di A12 yang bernilai C.
Anda juga dapat memasukkan kriteria secara langsung ke dalam fungsi. Pada contoh di bawah, kami menggunakan wildcard tanda bintang untuk menentukan kriteria yang memiliki alfabet “S”.
Dan, jika Anda mengabaikan rentang jumlah, ini akan memberi Anda jumlah rentang kriteria. Namun hal ini hanya mungkin terjadi jika rentang kriteria memiliki nilai numerik.
5. Fungsi INDEKS
Fungsi INDEX mengembalikan nilai dari daftar nilai berdasarkan nomor indeksnya. Secara sederhana, INDEX mengembalikan nilai dari daftar nilai dan Anda perlu menentukan posisi nilai tersebut.
Sintaksis
INDEX memiliki dua sintaksis yang berbeda. Pada bagian pertama , Anda dapat menggunakan bentuk array dari indeks untuk sekadar mendapatkan nilai dari daftar menggunakan posisinya.
INDEKS(array, angka_baris, [angka_kolom])
Yang kedua , Anda dapat menggunakan formulir sponsorship yang jarang digunakan dalam kehidupan nyata, namun Anda dapat menggunakannya jika Anda memiliki lebih dari satu rentang untuk dipromosikan.
INDEKS(referensi, angka_baris, [angka_kolom], [angka_area])
Argumen
- array: Rentang sel atau konstanta array.
- referensi: rentang sel atau beberapa rentang.
- row_number: Nomor baris yang ingin Anda peroleh nilainya.
- [col_number]: Nomor kolom yang ingin Anda peroleh nilainya.
- [nomor_area]: Jika Anda merujuk ke beberapa rentang sel (menggunakan sintaks referensi), tentukan nomor untuk merujuk ke satu rentang di antara semuanya.
Komentar
- Ketika argumen jumlah_baris dan jumlah_kolom ditentukan, argumen tersebut akan mengembalikan nilai dalam sel di perpotongan keduanya.
- Jika Anda menentukan nomor_baris atau nomor_kolom sebagai 0 (nol), ini akan mengembalikan larik nilai untuk seluruh kolom atau baris.
- Ketika nomor_baris dan nomor_kolom berada di luar jangkauan, #REF akan dikembalikan! kesalahan.
- Jika angka_area lebih besar dari rentang angka yang Anda tentukan, #REF! akan dikembalikan.
Contoh 1 – Menggunakan ARRAY untuk mendapatkan nilai dari daftar
Pada contoh di bawah, kami menggunakan fungsi INDEX untuk mendapatkan kuantitas pada bulan Juni. Dalam daftar, Jun berada di posisi ke-6 (baris ke-6) makanya saya tentukan 6 di nomor_baris. INDEX mengembalikan nilai 1904 pada hasilnya.
Dan jika Anda mengacu pada rentang dengan lebih dari satu kolom, Anda harus menentukan nomor kolomnya.
Contoh 2 – Menggunakan REFERENCE untuk mendapatkan nilai dari beberapa daftar
Pada contoh di bawah, alih-alih memilih seluruh rentang sekaligus, saya memilihnya dalam tiga rentang berbeda. Dalam argumen terakhir kita menentukan 2 di area_number yang akan menentukan rentang yang akan digunakan dari tiga rentang berbeda ini.
Sekarang di baris kedua kita mengacu pada baris ke-5 dan kolom ke-1. INDEX mengembalikan nilai 172 yang berada pada baris ke-5 dari baris ke-2.
6.Fungsi VLOOKUP
Fungsi VLOOKUP mencari nilai di kolom pertama tabel dan mengembalikan nilai di baris yang sama dari nilai terkait menggunakan nomor indeks. Sederhananya, ia melakukan pencarian vertikal.
Sintaksis
VLOOKUP(nilai_pencarian,array_tabel,col_index_num,range_lookup)
Argumen
- lookup_value: Nilai yang ingin Anda cari di kolom. Anda dapat mereferensikan sel yang berisi nilai pencarian atau memasukkan nilai tersebut langsung ke dalam fungsi.
- table_array: Rentang sel, rentang bernama yang ingin Anda cari nilainya.
- col_index_num: Angka mewakili nomor kolom yang ingin Anda ambil nilainya.
- range_lookup: Gunakan false atau 0 untuk pencocokan tepat dan true atau 1 untuk pencocokan tepat. Standarnya adalah Benar.
Komentar
- Jika VLOOKUP tidak menemukan nilai yang Anda cari, VLOOKUP akan mengembalikan #N/A.
- VLOOKUP hanya dapat memberikan Anda nilai yang berada di sebelah kanan nilai pencarian. Jika ingin melihat dari sisi kanan, Anda bisa menggunakan INDEX dan MATCH untuk itu.
- Jika Anda menggunakan pencocokan tepat, maka hanya akan cocok dengan nilai yang berada di urutan pertama pada kolom tersebut.
- Anda juga dapat menggunakan wildcard dengan VLOOKUP .
- Anda dapat menggunakan TRUE atau 1 jika Anda menginginkan pencocokan yang tepat dan FALSE atau 0 untuk pencocokan tepat.
- Jika Anda menggunakan pencocokan yang tepat (Benar): Ini akan mengembalikan nilai terkecil berikutnya dalam daftar jika tidak ada pencocokan tepat.
- Jika nilai yang Anda cari kurang dari nilai terkecil dalam daftar, VLOOKUP akan mengembalikan #N/A.
- Jika ada nilai pasti yang Anda cari, itu akan memberi Anda nilai pasti tersebut.
- Pastikan Anda telah mengurutkan daftar dalam urutan menaik.
Contoh
1. Menggunakan VLOOKUP untuk Kategori
Pada contoh di bawah ini, kita memiliki daftar siswa dengan nilai yang mereka terima, dan di kolom keterangan kita ingin nilai berdasarkan nilainya.
Pada daftar merek di atas, kami ingin menambahkan keterangan sesuai dengan rentang kategori di bawah.
Dalam hal ini kami memiliki dua opsi untuk digunakan.
Cara PERTAMA adalah membuat rumus bertumpuk dengan IF yang membutuhkan sedikit waktu, dan pilihan KEDUA adalah membuat rumus dengan VLOOKUP dengan kecocokan yang sesuai. Dan rumusnya menjadi:
=VLOOKUP(B2,$E$2:$G$5,3,BENAR)
Bagaimana itu bekerja
Saya menggunakan kolom “MIN MARKS” untuk mencocokkan nilai pencarian dan mendapatkan kembali nilai dari kolom “Keterangan”.
Saya sudah menyebutkan bahwa ketika Anda menggunakan TRUE dan tidak ada nilai pencarian yang sama persis, itu akan mengembalikan nilai terkecil berikutnya dari nilai pencarian. Misalnya, ketika kita mencari nilai 77 pada tabel kategori, 65 adalah nilai terkecil setelah 77.
Inilah sebabnya kami mendapat nilai “Baik” dalam komentarnya.
2. Penanganan error pada fungsi VLOOKUP
Salah satu masalah paling umum yang muncul saat menggunakan VLOOKUP adalah Anda akan mendapatkan #N/A setiap kali tidak ditemukan kecocokan. Namun solusi untuk masalah ini sederhana dan mudah. Izinkan saya menunjukkan kepada Anda dengan contoh sederhana.
Pada contoh di bawah ini kita memiliki daftar nama beserta umurnya dan di sel E6 kita menggunakan fungsi VLOOKUP untuk mencari nama dalam daftar. Setiap kali saya mengetik nama yang tidak ada dalam daftar saya mendapatkan #N/A.
Namun yang saya inginkan di sini adalah menampilkan pesan yang bermakna, bukan kesalahannya. Rumusnya adalah: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),,”Tidak Ditemukan”)
Cara kerjanya : IFNA dapat menguji nilai #N/A dan jika ada kesalahan Anda dapat menentukan nilai sebagai pengganti kesalahan tersebut.
7. Fungsi IFNA
Fungsi IFNA mengembalikan nilai tertentu jika terjadi kesalahan #N/A. Berbeda dengan IFERROR, IFERROR hanya mengevaluasi kesalahan #N/A dan mengembalikan nilai yang Anda tentukan.
Sintaksis
IFNA(nilai, nilai_if_na)
Argumen
- value: Nilai yang ingin Anda uji untuk kesalahan #N/A.
- value_if_na: Nilai yang ingin Anda kembalikan jika terjadi kesalahan.
Komentar
- Jika Anda tidak menentukan argumen apa pun, IFNA akan memperlakukannya sebagai string kosong (“”).
- Jika suatu nilai adalah array, maka hasilnya akan dikembalikan sebagai array.
- Ini akan mengabaikan semua kesalahan lainnya #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? dan #NULL!.
Contoh
Dalam fungsi VLOOKUP, #N/A terjadi ketika nilai pencarian tidak berada dalam rentang pencarian dan untuk ini kami telah menentukan pesan yang bermakna menggunakan IFNA.
Catatan: IFNA diperkenalkan di Excel 2013, sehingga tidak tersedia di versi sebelumnya.
8. Fungsi RAND
Fungsi RAND mengembalikan angka acak antara 0 dan 1. Sederhananya, Anda bisa menghasilkan angka acak antara 0 dan 1 (nilainya diperbarui setiap kali Anda membuat perubahan pada lembar kerja).
Sintaksis
RAND()
Argumen
- Tidak ada argumen untuk ditentukan dalam fungsi RAND
Komentar
- Jika Anda memasukkan nol ke dalam kelipatan, hasilnya akan nol.
- Jika Anda mengabaikan penentuan kelipatan maka akan terjadi kesalahan.
- Itu dibulatkan dari nol.
- Jika Anda memiliki dua kelipatan dalam jarak yang sama, ini akan menghasilkan kelipatan lebih tinggi dari angka yang Anda bulatkan.
Contoh
Selain memiliki angka antara 0 dan 1, Anda juga dapat menggunakan RAND untuk angka acak antara dua angka tertentu. Pada contoh di bawah, saya menggunakannya untuk membuat rumus yang menghasilkan angka acak antara 50 dan 100.
Saat Anda memasukkan rumus ini ke dalam sel, rumus ini akan mengembalikan angka antara 100 dan 50 dengan mengalikan nilai yang dikembalikan oleh RAND dengan persamaan yang kita gunakan. Untuk memahami rumus ini, kita perlu membaginya menjadi tiga bagian:
- Pertama, ketika mendeteksi angka yang lebih rendah dari angka yang lebih tinggi, Anda mendapatkan perbedaan antara keduanya.
- Kemudian selisihnya dikalikan dengan angka acak yang dikembalikan setelah pengurangan.
- Dan ketiga, tambahkan angka ini dengan sisa angka terendah di bagian ketiga persamaan.
9. Fungsi SUM
Fungsi SUM mengembalikan jumlah nilai yang diberikan . Secara sederhana, dengan fungsi SUM Anda dapat menghitung jumlah daftar nilai (Anda dapat langsung memasukkan nilai ke dalam fungsi atau merujuk ke rentang sel.
Sintaksis
SUM(angka1,[angka2],…)
Argumen
- number1 : nomor, rentang sel yang berisi angka, atau sel tunggal yang berisi nomor.
- [number2] : nomor, rentang sel berisi angka, atau satu sel berisi angka.
Komentar
- Itu mengabaikan nilai teks.
Contoh
Pada contoh di bawah, Anda dapat memasukkan angka langsung ke dalam fungsi menggunakan koma di antara angka tersebut.
Anda juga bisa merujuk ke suatu rentang untuk menghitung jumlah angka dan jika ada teks, nilai logika, atau sel kosong, teks tersebut akan mengabaikannya.
Jika ada nilai kesalahan di sel yang Anda maksud, hasilnya akan #N/A.
Jika Anda memiliki nilai numerik yang diformat sebagai teks, nilai tersebut akan diabaikan. Disarankan untuk mengonversinya menjadi angka sebelum menggunakan SUM.
10. Fungsi ATAU
Fungsi OR mengembalikan nilai Boolean (TRUE atau FALSE) setelah menguji kondisi yang Anda tentukan. Secara sederhana, Anda dapat menguji beberapa kondisi dengan fungsi AND dan fungsi ini mengembalikan TRUE jika salah satu dari kondisi ini (atau semua) adalah TRUE dan mengembalikan FALSE hanya jika semua kondisi ini FALSE.
Sintaksis
ATAU(logika1, [logika2],…)
Argumen
- logika1: Kondisi yang ingin Anda periksa.
- [logis2]: Kondisi tambahan yang ingin Anda periksa.
Komentar
- Nilai akan diabaikan jika sel atau tabel referensi berisi sel atau teks kosong.
- Hasil dari kondisi tersebut harus berupa nilai logika (TRUE atau FALSE).
- Ini akan mengembalikan kesalahan jika tidak ada nilai logika yang dikembalikan.
Contoh
Pada contoh di bawah ini, kita telah membuat kondisi menggunakan fungsi IF bahwa jika seorang siswa mendapat nilai 60 poin di atas dalam salah satu dari dua mata pelajaran, rumusnya akan menghasilkan TRUE.
Sekarang pada contoh di bawah ini kita telah menggunakan angka untuk mendapatkan nilai logika dalam suatu rumus. Anda juga dapat melakukan ketentuan di atas dalam urutan terbalik.
Anda dapat menggunakan TRUE dan FALSE sebagai pengganti angka. Fungsi OR memperlakukan nilai logika ini sebagai angka.
Tutorial lainnya
Fungsi Statistik / Fungsi Tanggal / String – Fungsi Teks / Fungsi Keuangan