Cara Memahami Rumus dan Fungsi Microsoft Excel
Cara menggunakan rumus dan fungsi microsoft excel ini hal mendasar yang perlu dipelajari. Artikel ini mengupas secara detil tentang hal tersebut.
Senin, 06 April 2020
Apakah anda sedang belajar tentang Rumus dan Fungsi yang ada di Microsoft Excel? Bahasan di sini akan membantu proses belajar anda tersebut.
Artikel ini disertai dengan gambar dan contoh perintah, tentunya hal ini akan membuat kita lebih cepat memahami pembahasan dan permasalahan yang ada.
Tutorial ini diajarkan oleh mister Dave Bruns. Silahkan dicermati dan disimak dengan teliti. Selamat membaca.
Artikel ini memperkenalkan konsep dasar yang perlu kita ketahui agar mahir dengan rumus yang ada di Microsoft Excel nantinya.
Sebagai contoh:
Catatan: semua rumus di Excel harus dimulai dengan tanda sama dengan (=) atau (+) namun dalam bahasan sini kita gunakan = sebagai penanda formula.
Secara umum, ini dianggap sebagai bentuk yang tidak baik, karena menyembunyikan informasi dan membuatnya lebih sulit untuk mengolah informasi di spreadsheet nantinya.
Lebih baik, gunakan referensi sel agar nilai dapat diubah kapan saja kita mau.
Pada layar di bawah ini, C1 berisi rumus berikut:
Perhatikan, karena kita menggunakan referensi sel untuk A1, A2, dan A3, nilai-nilai ini dapat diubah kapan saja dan C1 akan tetap menunjukkan hasil yang akurat.
Rumus mengembalikan hasil yang benar di D2 dan D3, tetapi mengembalikan #DIV/0! kesalahan pada D4, karena B4 kosong:
Ada beberapa cara penanganan kesalahan. Dalam hal ini, kita bisa memberikan nilai yang hilang di B4, atau "menangkap" kesalahan dengan fungsi IFERROR dan menampilkan pesan yang informatif (atau tidak sama sekali).
Ini berarti, kita tidak perlu memasukkan formula dasar yang sama lagi dan lagi.
Pada gambar di bawah ini, rumus di E1 telah disalin ke clipboard dengan kontrol C (Ctrl+C):
Gambar di bawah ini menunjukkan rumus yang disisipkan ke sel E2 dengan Kontrol + V (Ctrl + V)
Perhatikan referensi sel telah berubah:
Formula yang sama disisipkan ke E3. Alamat sel diperbarui lagi:
Referensi relatif sangat berguna, tetapi ada kalanya kita tidak ingin referensi cell berubah.
Referensi sel yang tidak akan berubah saat disalin disebut referensi absolut.
Untuk membuat referensi absolut, gunakan simbol dolar ($):
Misalnya, pada tampilan di bawah ini, kita ingin mengalikan setiap nilai dalam kolom D dengan 10, yang dimasukkan dalam A1.
Dengan menggunakan referensi absolut untuk A1, kita "mengunci" referensi itu sehingga tidak akan berubah ketika rumus disalin ke E2 dan E3:
Berikut adalah rumus terakhir dalam E1, E2, dan E3:
Perhatikan referensi untuk pembaruan D1 ketika rumus disalin, tetapi referensi ke A1 tidak pernah berubah.
Sekarang kita dapat dengan mudah mengubah nilai dalam A1, dan ketiga rumus dihitung ulang.
Di bawah nilai dalam A1 telah berubah dari 10 menjadi 12:
Contoh sederhana ini juga menunjukkan mengapa tidak baik memasukkan nilai "kode statis" ke dalam rumus.
Dengan menyimpan nilai dalam A1 di satu tempat, dan merujuk ke A1 dengan referensi absolut, nilai dapat diubah kapan saja dan semua rumus terkait akan berubah secara instan.
Kiat: kita dapat beralih atau mengubah rumus relatif dan absolut dengan menekan tombol F4.
Daripada mengetik referensi sel secara manual, kita bisa mengarahkan mouse dan mengkliknya, seperti yang terlihat di bawah ini.
Referensi catatan diberi kode warna:
Semua rumus di Excel harus dimulai dengan tanda sama dengan (=). Tidak ada tanda sama dengan, tidak ada rumus:
Apa pun opsi yang kita gunakan, tekan Enter untuk mengonfirmasi perubahan setelah selesai.
Jika kita ingin membatalkan, dan membiarkan formula tidak berubah, klik tombol Escape (Esc).
Mereka terkait erat, tetapi tidak persis sama. Secara teknis, rumus adalah ekspresi apa pun yang dimulai dengan tanda sama dengan (=).
Fungsi, di sisi lain, adalah formula dengan nama dan tujuan khusus.
Dalam kebanyakan kasus, fungsi memiliki nama yang mencerminkan tujuan penggunaannya.
Misalnya, kita mungkin sudah tahu fungsi SUM, yang mengembalikan jumlah referensi yang diberikan:
Fungsi AVERAGE, seperti yang kita ketahui, mengembalikan nilai rata-rata dari referensi yang diberikan:
Dan fungsi MIN dan MAX masing-masing mengembalikan nilai minimum dan maksimum
Argumen fungsi muncul setelah nama fungsi, di dalam tanda kurung, dipisahkan dengan koma.
Semua fungsi membutuhkan tanda kurung buka dan tutup yang berpasangan ().
Polanya terlihat seperti ini:
Misalnya, fungsi COUNTIF menghitung sel yang memenuhi kriteria, dan mengambil dua argumen, rentang (range), dan kriteria:
Pada layar di bawah ini, kisarannya adalah A1: A5 dan kriterianya adalah "red".
Rumus dalam C1 yaitu:
Tidak semua argumen diperlukan. Argumen yang ditunjukkan tanda kurung siku adalah opsional.
Misalnya, fungsi YEARFRAC mengembalikan jumlah pecahan tahun antara tanggal mulai dan tanggal akhir dan mengambil 3 argumen:
Tanggal mulai dan tanggal akhir adalah argumen yang diperlukan, sedangkan basis adalah argumen opsional (boleh dipakai, boleh tidak)
Lihat di bawah untuk contoh bagaimana menggunakan YEARFRAC untuk menghitung usia saat ini berdasarkan tanggal lahir.
1. Masukkan tanda sama dengan (=) dan mulai mengetik perintahnya. Excel akan memperlihatkan daftar fungsi yang cocok berdasarkan pada apa yang kita ketik:
Ketika kita melihat fungsi yang diinginkan dalam daftar, gunakan tombol panah untuk memilih (atau terus mengetik).
2. Tekan tombol Tab untuk menerima fungsi. Excel akan melengkapi fungsinya:
3. Isi argumen yang dibutuhkan:
4. Tekan Enter untuk mengonfirmasi rumus:
Misalnya, di bawah ini kita memiliki tanggal lahir di B1 dan kita ingin menghitung usia saat ini di B2:
Fungsi YEARFRAC akan menghitung tahun dengan tanggal mulai dan tanggal akhir:
Kita dapat menggunakan B1 untuk tanggal mulai, lalu menggunakan fungsi TODAY untuk memberikan tanggal akhir:
Ketika kita tekan Enter untuk konfirmasi, kita mendapatkan umur terkini berdasarkan tanggal hari ini:
Perhatikan bahwa kita menggunakan fungsi TODAY untuk mengumpan tanggal akhir ke fungsi YEARFRAC.
Dengan kata lain, fungsi TODAY dapat bersarang di dalam fungsi YEARFRAC untuk memberikan argumen tanggal akhir.
Kita dapat mengambil rumus satu langkah lebih jauh dan menggunakan fungsi INT untuk memenggal nilai desimal:
Di sini, rumus awal YEARFRAC mengembalikan 20,4 ke fungsi INT, dan fungsi INT mengembalikan hasil akhir 20.
Catatan: Tanggal saat ini dalam gambar di atas adalah 22 Februari 2019.
Fungsi bersarang IF adalah contoh klasik dari fungsi bersarang.
Fungsi TODAY adalah fungsi Excel yang klasik (berdiri sendiri) tanpa memerlukan argumen apapun.
Operator logis yang tersedia di Excel ditunjukkan pada tabel di bawah ini:
Pertama, ekspresi dalam tanda kurung dievaluasi. Excel berikutnya akan menyelesaikan semua pemangkatan.
Setelah pangkat, Excel akan melakukan perkalian dan pembagian, lalu penjumlahan dan pengurangan.
Jika rumusnya melibatkan penggabungan, ini akan terjadi setelah operasi matematika standar.
Akhirnya, Excel akan mengevaluasi operator logis, jika ada.
Langkah Pembacaan:
Tips: kita bisa menggunakan fitur Evaluate untuk melihat Excel memecahkan rumus langkah demi langkah.
Cara termudah untuk melakukan ini di Excel adalah menyalin rumus, lalu tempel, menggunakan Paste Special> Values.
Ini akan menimpa rumus dengan nilai yang dikembalikannya.
Kita dapat menggunakan pintasan keyboard (Ctrl+V) untuk menempelkan nilai, atau menggunakan menu Paste pada tab yang ada di ribbon toolbar.
Baca juga: Fungsi Tombol Ctrl Microsoft Word yang sangat berguna untuk mempercepat pekerjaan.
Demikian artikel mengenai Cara Memahami Rumus dan Fungsi di Microsoft Excel ini. Semoga bermanfaat dan berguna bagi para pembacanya. Salam hangat.
Artikel ini disertai dengan gambar dan contoh perintah, tentunya hal ini akan membuat kita lebih cepat memahami pembahasan dan permasalahan yang ada.
Tutorial ini diajarkan oleh mister Dave Bruns. Silahkan dicermati dan disimak dengan teliti. Selamat membaca.
Cara Memahami Rumus dan Fungsi Microsoft Excel
Rumus atau Formula dan fungsinya adalah kaki tangan dari Excel. Mereka menggerakkan hampir semua hal menarik dan berguna yang kita lakukan pada spreadsheet.Artikel ini memperkenalkan konsep dasar yang perlu kita ketahui agar mahir dengan rumus yang ada di Microsoft Excel nantinya.
Apa itu Formula?
Formula atau Rumus di Excel adalah ekspresi yang mengembalikan hasil atau nilai tertentu.Sebagai contoh:
= 1 + 2 //menghasilkan nilai 3
Catatan: semua rumus di Excel harus dimulai dengan tanda sama dengan (=) atau (+) namun dalam bahasan sini kita gunakan = sebagai penanda formula.
Referensi Sel/Cell
Dalam contoh di atas, nilai yang dihasilkan bersifat Statis atau Kaku. Itu berarti, hasil tidak akan berubah, kecuali jika kita mengedit rumus lagi dan mengubah nilai secara manual.Secara umum, ini dianggap sebagai bentuk yang tidak baik, karena menyembunyikan informasi dan membuatnya lebih sulit untuk mengolah informasi di spreadsheet nantinya.
Lebih baik, gunakan referensi sel agar nilai dapat diubah kapan saja kita mau.
Pada layar di bawah ini, C1 berisi rumus berikut:
= A1 + A2 + A3 // mengembalikan 9
Perhatikan, karena kita menggunakan referensi sel untuk A1, A2, dan A3, nilai-nilai ini dapat diubah kapan saja dan C1 akan tetap menunjukkan hasil yang akurat.
Semua Rumus Mengembalikan Hasil
Semua rumus di Excel mengembalikan hasil, bahkan ketika hasilnya adalah kesalahan. Di bawah rumus digunakan untuk menghitung perubahan persen.Rumus mengembalikan hasil yang benar di D2 dan D3, tetapi mengembalikan #DIV/0! kesalahan pada D4, karena B4 kosong:
Ada beberapa cara penanganan kesalahan. Dalam hal ini, kita bisa memberikan nilai yang hilang di B4, atau "menangkap" kesalahan dengan fungsi IFERROR dan menampilkan pesan yang informatif (atau tidak sama sekali).
Copy dan Paste Rumus
Keluwesan referensi cell adalah bahwa mereka berubah secara otomatis ketika formula disalin ke lokasi baru.Ini berarti, kita tidak perlu memasukkan formula dasar yang sama lagi dan lagi.
Pada gambar di bawah ini, rumus di E1 telah disalin ke clipboard dengan kontrol C (Ctrl+C):
Gambar di bawah ini menunjukkan rumus yang disisipkan ke sel E2 dengan Kontrol + V (Ctrl + V)
Perhatikan referensi sel telah berubah:
Formula yang sama disisipkan ke E3. Alamat sel diperbarui lagi:
Referensi Relatif dan Absolut
Referensi sel di atas disebut referensi relatif. Ini berarti referensi yang selalu berubah ke sel tempat penyimpanannya.Referensi relatif sangat berguna, tetapi ada kalanya kita tidak ingin referensi cell berubah.
Referensi sel yang tidak akan berubah saat disalin disebut referensi absolut.
Untuk membuat referensi absolut, gunakan simbol dolar ($):
= A1 // referensi relatif
= $ A $ 1 // referensi absolut
= $ A $ 1 // referensi absolut
Misalnya, pada tampilan di bawah ini, kita ingin mengalikan setiap nilai dalam kolom D dengan 10, yang dimasukkan dalam A1.
Dengan menggunakan referensi absolut untuk A1, kita "mengunci" referensi itu sehingga tidak akan berubah ketika rumus disalin ke E2 dan E3:
Berikut adalah rumus terakhir dalam E1, E2, dan E3:
= D1 * $ A $ 1 // rumus pada E1
= D2 * $ A $ 1 // rumus di E2
= D3 * $ A $ 1 // rumus dalam E3
= D2 * $ A $ 1 // rumus di E2
= D3 * $ A $ 1 // rumus dalam E3
Perhatikan referensi untuk pembaruan D1 ketika rumus disalin, tetapi referensi ke A1 tidak pernah berubah.
Sekarang kita dapat dengan mudah mengubah nilai dalam A1, dan ketiga rumus dihitung ulang.
Di bawah nilai dalam A1 telah berubah dari 10 menjadi 12:
Contoh sederhana ini juga menunjukkan mengapa tidak baik memasukkan nilai "kode statis" ke dalam rumus.
Dengan menyimpan nilai dalam A1 di satu tempat, dan merujuk ke A1 dengan referensi absolut, nilai dapat diubah kapan saja dan semua rumus terkait akan berubah secara instan.
Kiat: kita dapat beralih atau mengubah rumus relatif dan absolut dengan menekan tombol F4.
Cara Memasukkan Formula
Untuk memasukkan formula:- Pilih cell
- Masukkan tanda sama dengan (=)
- Ketikkan rumus, dan tekan enter.
Daripada mengetik referensi sel secara manual, kita bisa mengarahkan mouse dan mengkliknya, seperti yang terlihat di bawah ini.
Referensi catatan diberi kode warna:
Semua rumus di Excel harus dimulai dengan tanda sama dengan (=). Tidak ada tanda sama dengan, tidak ada rumus:
Cara Mengubah Rumus
Untuk mengedit formula, kita memiliki 3 opsi:- Pilih sel, edit di bilah rumus
- Klik dua kali sel, edit langsung
- Pilih sel, tekan F2, edit langsung
Apa pun opsi yang kita gunakan, tekan Enter untuk mengonfirmasi perubahan setelah selesai.
Jika kita ingin membatalkan, dan membiarkan formula tidak berubah, klik tombol Escape (Esc).
Apa itu Fungsi?
Bekerja di Microsoft Excel, kita akan mendengar kata "formula" dan "fungsi" yang sering digunakan, kadang-kadang secara bergantian.Mereka terkait erat, tetapi tidak persis sama. Secara teknis, rumus adalah ekspresi apa pun yang dimulai dengan tanda sama dengan (=).
Fungsi, di sisi lain, adalah formula dengan nama dan tujuan khusus.
Dalam kebanyakan kasus, fungsi memiliki nama yang mencerminkan tujuan penggunaannya.
Misalnya, kita mungkin sudah tahu fungsi SUM, yang mengembalikan jumlah referensi yang diberikan:
= SUM (1,2,3) // mengembalikan 6
= SUM (A1: A3) // mengembalikan A1 + A2 + A3
= SUM (A1: A3) // mengembalikan A1 + A2 + A3
Fungsi AVERAGE, seperti yang kita ketahui, mengembalikan nilai rata-rata dari referensi yang diberikan:
= AVERAGE (1,2,3) // mengembalikan 2
Dan fungsi MIN dan MAX masing-masing mengembalikan nilai minimum dan maksimum
= MIN (1,2,3) // menghasilkan 1
= MAX (1,2,3) // nilai kembaliannya 3
= MAX (1,2,3) // nilai kembaliannya 3
Argumen Fungsi
Sebagian besar fungsi memerlukan input untuk mengembalikan hasil. Input ini disebut "argumen".Argumen fungsi muncul setelah nama fungsi, di dalam tanda kurung, dipisahkan dengan koma.
Semua fungsi membutuhkan tanda kurung buka dan tutup yang berpasangan ().
Polanya terlihat seperti ini:
= NAMAFUNGSI (argumen1, argumen2, argumen3)
Misalnya, fungsi COUNTIF menghitung sel yang memenuhi kriteria, dan mengambil dua argumen, rentang (range), dan kriteria:
= COUNTIF (range, kriteria) // dua argumen
Pada layar di bawah ini, kisarannya adalah A1: A5 dan kriterianya adalah "red".
Rumus dalam C1 yaitu:
= COUNTIF (A1: A5, "red") // menghasilkan 2
Tidak semua argumen diperlukan. Argumen yang ditunjukkan tanda kurung siku adalah opsional.
Misalnya, fungsi YEARFRAC mengembalikan jumlah pecahan tahun antara tanggal mulai dan tanggal akhir dan mengambil 3 argumen:
= YEARFRAC (Tanggal_Awal, Tanggal_Akhir, [basis])
Tanggal mulai dan tanggal akhir adalah argumen yang diperlukan, sedangkan basis adalah argumen opsional (boleh dipakai, boleh tidak)
Lihat di bawah untuk contoh bagaimana menggunakan YEARFRAC untuk menghitung usia saat ini berdasarkan tanggal lahir.
Cara Memasukkan Fungsi
Jika kita tahu nama fungsinya, mulailah mengetik. Berikut langkah-langkahnya:1. Masukkan tanda sama dengan (=) dan mulai mengetik perintahnya. Excel akan memperlihatkan daftar fungsi yang cocok berdasarkan pada apa yang kita ketik:
Ketika kita melihat fungsi yang diinginkan dalam daftar, gunakan tombol panah untuk memilih (atau terus mengetik).
2. Tekan tombol Tab untuk menerima fungsi. Excel akan melengkapi fungsinya:
3. Isi argumen yang dibutuhkan:
4. Tekan Enter untuk mengonfirmasi rumus:
Menggabungkan Fungsi (Bersarang)
Banyak rumus Excel menggunakan lebih dari satu fungsi, dan fungsi dapat "bersarang" pada satu sama lainnya.Misalnya, di bawah ini kita memiliki tanggal lahir di B1 dan kita ingin menghitung usia saat ini di B2:
Fungsi YEARFRAC akan menghitung tahun dengan tanggal mulai dan tanggal akhir:
Kita dapat menggunakan B1 untuk tanggal mulai, lalu menggunakan fungsi TODAY untuk memberikan tanggal akhir:
Ketika kita tekan Enter untuk konfirmasi, kita mendapatkan umur terkini berdasarkan tanggal hari ini:
=YEARFRAC(B1,TODAY())
Perhatikan bahwa kita menggunakan fungsi TODAY untuk mengumpan tanggal akhir ke fungsi YEARFRAC.
Dengan kata lain, fungsi TODAY dapat bersarang di dalam fungsi YEARFRAC untuk memberikan argumen tanggal akhir.
Kita dapat mengambil rumus satu langkah lebih jauh dan menggunakan fungsi INT untuk memenggal nilai desimal:
= INT (YEARFRAC (B1, TODAY ()))
Di sini, rumus awal YEARFRAC mengembalikan 20,4 ke fungsi INT, dan fungsi INT mengembalikan hasil akhir 20.
Catatan: Tanggal saat ini dalam gambar di atas adalah 22 Februari 2019.
Fungsi bersarang IF adalah contoh klasik dari fungsi bersarang.
Fungsi TODAY adalah fungsi Excel yang klasik (berdiri sendiri) tanpa memerlukan argumen apapun.
Operator Matematika
Tabel di bawah ini menunjukkan operator matematika standar yang tersedia di Excel:| Simbol | Operasi | Contoh |
|---|---|---|
| + | Tambah | = 2 + 3 = 5 |
| - | Pengurangan | = 9-2 = 7 |
| * | Perkalian | = 6 * 7 = 42 |
| / | Pembagian | = 9/3 = 3 |
| ^ | Pangkat | = 4 ^ 2 = 16 |
| () | Tanda kurung | = (2 + 4) / 3 = 2 |
Operator Logis
Operator logis menyediakan dukungan untuk perbandingan seperti "lebih besar dari", "kurang dari", dll.Operator logis yang tersedia di Excel ditunjukkan pada tabel di bawah ini:
| Operator | Arti | Contoh |
|---|---|---|
| = | Sama dengan | =A1=10 |
| <> | Tidak sama dengan | =A1<>10 |
| > | Lebih besar dari | =A1>100 |
| < | Kurang dari | =A1<100 |
| >= | Lebih besar dari atau sama dengan | =A1>=75 |
| <= | Kurang dari atau sama dengan | =A1<=0 |
Urutan Operasi Perhitungan Matematika
Saat memecahkan rumus, Excel mengikuti urutan yang disebut "urutan operasi".Pertama, ekspresi dalam tanda kurung dievaluasi. Excel berikutnya akan menyelesaikan semua pemangkatan.
Setelah pangkat, Excel akan melakukan perkalian dan pembagian, lalu penjumlahan dan pengurangan.
Jika rumusnya melibatkan penggabungan, ini akan terjadi setelah operasi matematika standar.
Akhirnya, Excel akan mengevaluasi operator logis, jika ada.
Langkah Pembacaan:
- Tanda kurung
- Pemangkatan
- Perkalian dan Pembagian, Penambahan dan pengurangan
- Rangkaian Operator logis
Tips: kita bisa menggunakan fitur Evaluate untuk melihat Excel memecahkan rumus langkah demi langkah.
Cara Mengubah Formula Menjadi Nilai
Terkadang kita ingin menyingkirkan rumus, dan hanya meninggalkan nilai di tempatnya.Cara termudah untuk melakukan ini di Excel adalah menyalin rumus, lalu tempel, menggunakan Paste Special> Values.
Ini akan menimpa rumus dengan nilai yang dikembalikannya.
Kita dapat menggunakan pintasan keyboard (Ctrl+V) untuk menempelkan nilai, atau menggunakan menu Paste pada tab yang ada di ribbon toolbar.
Baca juga: Fungsi Tombol Ctrl Microsoft Word yang sangat berguna untuk mempercepat pekerjaan.
Demikian artikel mengenai Cara Memahami Rumus dan Fungsi di Microsoft Excel ini. Semoga bermanfaat dan berguna bagi para pembacanya. Salam hangat.