RumusHitung.com – Halo sobat bertemu lagi dengan rumushitung. Kali ini rumushitung akan membahas lagi materi rumus Excel. Masih dengan jenis rumus Excel, yaitu rumus fungsi Excel. Rumus fungsi yang sudah di bahas sebelumnya, antara lain fungsi logika, date and time, lookup and reference, text, dynamic array, engineering, financial, dan Information. Selanjutnya, kita akan membahas rumus fungsi Excel yang ke 9, yaitu rumus fungsi Math. Langsung saja kita mulai pembahasannya.
Contents
- 1 Fungsi Excel Math
- 2 1. ABS
- 3 2. AGGREGATE
- 4 3. ARABIC
- 5 4. BASE
- 6 5. CEILING
- 7 6. CEILING.MATH
- 8 7. CEILING.PRECISE
- 9 8. DECIMAL
- 10 9. EVEN
- 11 10. EXP
- 12 11. FACT
- 13 12. FACTDOUBLE
- 14 13. FLOOR
- 15 14. FLOOR.MATH
- 16 15. FLOOR.PRECISE
- 17 16. GCD
- 18 17. INT
- 19 18. LCM
- 20 19. LN
- 21 20. LOG
- 22 21. LOG10
- 23 22. MDETERM
- 24 23. MINVERSE
- 25 24. MOD
- 26 25. MROUND
- 27 26. MUNIT
- 28 27. ODD
- 29 28. PI
- 30 29. POWER
- 31 30. PRODUCT
- 32 31. QUOTIENT
- 33 32. RAND
- 34 33. RANDBETWEEN
- 35 34. ROMAN
- 36 35. ROUND
- 37 36. ROUNDDOWN
- 38 37. ROUNDUP
- 39 38. SIGN
- 40 39. SQRT
- 41 40. SUBTOTAL
- 42 41. SUM
- 43 42. SUMIF
- 44 43. SUMIFS
- 45 44. SUMPRODUCT
- 46 45. TRUNC
Fungsi Excel Math
Ada 45 rumus fungsi math yang akan kita bahas, antara lain ABS, AGGREGATE, ARABIC, BASE, CEILING, CEILING.MATH, CEILING.PRECISE, DECIMAL, EVEN, EXP, FACT, FACTDOUBLE, FLOOR, FLOOR.MATH, FLOOR.PRECISE, GCD, INT, LCM, LN, LOG, LOG10, MDETERM, MINVERSE, MOD, MROUND, MUNIT, ODD, PI, POWER, PRODUCT, QUOTIENT, RAND, RANDBETWEEN, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, SIGN, SQRT, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, dan TRUNC.
1. ABS
Fungsi Excel ABS menghasilkan nilai angka. Bilangan negatif diubah menjadi bilangan positif, dan bilangan positif menjadi bilangan positif (tetap).
Penulisan rumus : =ABS(number)
Argumen :
- number – angka untuk mendapatkan nilai absolutnya.
Contoh :
Misalnya, =ABS(-5) akan menghasilkan nilai 5 dan =ABS(5) akan menghasilkan nilai 5 (tetap), karena fungsi ABS menghasilkan jarak angka dari nol.
2. AGGREGATE
Fungsi Excel AGGREGATE menghasilkan penghitungan agregat seperti AVERAGE, COUNT, MAXX, dll. Secara opsional membiarkan baris dan kesalahan tersembunyi. Total ada 19 operasi tersedia, ditentukan oleh argumen nomor fungsi.
Penulisan rumus : =AGGREGATE(function_num;options;ref1;ref2)
Argumen :
- function_num – nomor fungsi operasi yang dilakukan (1-19).
- options – nilai untuk diabaikan (0-7).
- ref1 – argumen pertama.
- ref2 – argumen kedua (k).
Contoh :
Untuk menghasilkan nilai MAX dalam rentang B3:B12, dengan mengabaikan kesalahan dan baris tersembunyi, isi 4 untuk argumen function_num (fungsi MAX) dan 6 untuk argumen options :
=AGGREGATE(4;6;B3:B12) / / nilai tertinggi
Untuk menghasilkan nilai MIN dengan argumen options yang sama, ubah argumen function_num menjadi 5 (fungsi MIN) :
=AGGREGATE(5;6;B3:B12) / / nilai pengurangan
Bisa juga di argumen ref1 pada rentang B3:B12 diganti nama “nilai”. Misalnya, menentukan MAX dengan function_num adalah 4 dan options adalah 6, maka penulisan rumus seperti :
=AGGREGATE(4;6;nilai)
Seperti contoh yang ditampilkan paling atas, umus di D6:D8 menunjukkan cara menghasilkan nilai terbesar ke-n, dengan argumen function_num adalah 14 (fungsi LARGE) :
=AGGREGATE(14;6;B3:B12;1) / / terbesar pertama
=AGGREGATE(14;6;B3:B12;2) / / terbesar kedua
=AGGREGATE(14;6;B3:B12;3) / / terbesar ketiga
Argumen function_num tersebut adalah 14, yang menjalankan fungsi LARGE. Karena fungsi LARGE membutuhkan argumen ref2, yaitu k.
Nomor fungsi
Tabel di bawah ini mencantumkan nomor fungsi untuk argumen function_num yang tersedia pada fungsi AGGREGATE bersama dengan argumen fungsi lain yang terkait. Pada kolom ketiga, Ref2 menunjukkan argumen kedua yang diharapkan ketika dibutuhkan.
| Nomor | Fungsi | Ref2 |
|---|---|---|
| 1 | AVERAGE | |
| 2 | COUNT | |
| 3 | COUNTA | |
| 4 | MAX | |
| 5 | MIN | |
| 6 | PRODUCT | |
| 7 | STDEV.S | |
| 8 | STDEV.P | |
| 9 | SUM | |
| 10 | VAR.S | |
| 11 | VAR.P | |
| 12 | MEDIAN | |
| 13 | MODE.SNGL | |
| 14 | LARGE | k |
| 15 | SMALL | k |
| 16 | PERCENTILE.INC | k |
| 17 | QUARTILE.INC | quart |
| 18 | PERCENTILE.EXC | k |
| 19 | QUARTILE.EXC | quart |
Option
Fungsi AGGREGATE mempunyai banyak opsi (argumen options) untuk mengabaikan kesalahan, baris tersembunyi, dan fungsi lainnya. Nilai yang memungkinkan adalah 0-7, seperti yang ditunjukkan pada tabel di bawah :
| Nomor Opsi (Option) | Keterangan |
|---|---|
| 0 | abaikan fungsi SUBTOTAL dan AGGREGATE |
| 1 | abaikan baris tersembunyi, pada fungsi SUBTOTAL dan AGGREGATE |
| 2 | abaikan nilai kesalahan pada fungsi SUBTOTAL dan AGGREGATE |
| 3 | abaikan baris tersembunyi, nilai kesalahan paada SUBTOTAL dan AGGREGATE |
| 4 | abaikan apapun |
| 5 | abaikan baris tersembunyi |
| 6 | abaikan nilai kesalahan |
| 7 | abaikan baris tersembunyi dan nilai kesalahan |
3. ARABIC
Fungsi Excel ARABIC mengonversi angka romawi, sebagai teks, menjadi angka dalam angka Arab. Misalnya, =ARABIC(“VI”) akan menghasilkan 6.
Penulisan rumus : =ARABIC(roman_text)
Argumen :
- roman_text – angka romawi dalam teks yang ingin diubah.
Contoh :
Misalnya, untuk argumen roman_text adalah “XXIV”, maka menghasilkan 24. Dengan penulisan rumus seperti :
=ARABIC(“XXIV”) / / menghasilkan 24
Angka romawi
Tabel di bawah ini mencantumkan angka Romawi yang tersedia dengan nilai angka Arabnya yang setara.
| Simbol | Nilai |
|---|---|
| I | 1 |
| V | 5 |
| X | 10 |
| L | 50 |
| C | 100 |
| D | 500 |
| M | 1000 |
4. BASE
Fungsi Excel BASE mengonversi angka menjadi representasi teks dari radix (basis) yang diberikan.
Penulisan rumus : =BASE(number;radix;[min_length])
Argumen :
- number – angka yang ingin diubah ke representasi lain.
- radix – basis representasi yang akan dikonversi.
- min_length – [opsional] menjamin panjang minimum dari string yang dihasilkan dengan mengisi string dengan nol.
Dalam lembar kerja yang diperlihatkan, angka input diubah menjadi tiga representasi berbeda : basis 2 (biner), basis 10 (desimal), dan basis 16 (heksadesimal). Nilai radix menentukan representasi dasar dari teks yang dikembalikan. misalnya, untuk mengubah angka 3 menjadi representasi binernya (basis 2), rumusnya adalah :
=BASE(3;2) / / menghasilkan “11” sebagai teks, 3 dalam biner (basis 2)
=BASE(10;16) / / menghasilkan “A” sebagai teks, 10 dalam heksadesimal (basis 16)
Fungsi ini juga menwarkan argumen opsional min_length yang akan mengisi string yang dihasilkan dengan nol jika panjangnya kurang dari nilai yang diberikan.
=BASE(3;2;4) / / menghasilkan “0011” sebagai teks
=BASE(10;16;4) / / menghasilkan “000A” sebagai teks
5. CEILING
Fungsi Excel CEILING membulatkan angka tertentu hingga beberapa terdekat yang ditentukan. CEILING berfungsi seperti fungsi MROUND, tetapu CEILING selalu membulat.
Penulisan rumus : =CEILING(number;significance)
Argmen :
- number – angka yang harus dibulatkan.
- significance – kelipatan untuk dipakai saat pembulatan.
Contoh :
Untuk membulatkan angka di A2 hingga kelipatan 4 yang terdekat, bisa menggunakan rumus seperti :
=CEILING(A2;4)
PLAFON bisa menjadi berguna untuk menetapkan harga setelah konversi mata uang atau diskon diterapkan. Misalnya, rumus di bawah ini akan membulatkan nilai daalam A2 hingga seluruh dolar berikutnya, lalu mengurangi 1 sen, untuk menghasilkan harga seperti $2,99, $5,99, $23,99, dll.
=CEILING(A2;1)-0,01
CEILING memahami format waktu dan bisa digunakan untuk mengumpulkan waktu hingga kelipatan tertentu. Misalnya, untuk membulatkan waktu di A2 hingga 15 menit terdekat, bisa menggunakan fungsi CEILING seperti :
=CEILING(A2;”0:15″) / / membulatkan ke 15 terdekat
6. CEILING.MATH
Fungsi Excel CEILING.MATH membulatkan angka hingga kelipatan tertentu. Tidak seperti fungsi CEILING, CEILING MATH defaultnya adalah kelipatan 1, dan memberikan kontrol aksplisit atas arah pembulatan untuk bilangan negatif.
Penulisan rumus : =CEILING.MATH(number;[significance];[mode])
Argumen :
- number – angka yang harus dibulatkan.
- significance – [opsional] beberapa untuk digunakan saat pembulatan. Defaultnya adalah 1.
- mode – [opsional] untuk membulatkan angka negatif menuju atau menjauh nol. Defaultnya aadalah 0.
Secara default, CEILING.MATH membulatkan ke bilangan bulat terdekat, menggunakan signifikansi 1. Bilangan positif dengan bagian desimal dibulatkan ke atas ke bilangan bulat terdekat (misalnya, 6,3 dibulatkan menjadi 7) dan bilangan negatif dengan bagian desimal dibulatkan ke nol (misalnya -6,3 dibulatkan menjadi -6).
=CEILING.MATH(6,3) / / menghasilkan 7
=CEILING.MATH(-6,3) / / menghasilkan -6
Membulatkan angka negatif
Kendali untuk membulatkan bilangan negatif menuju nol atau menjauh dari nol disediakan melalui argumen mode (opsional). Mode default ke nol. Jika mode nol atau dihilangkan, maka CEILING.MATH membulatkan angka negatif menjauhi nol. Mode tidak berpengaruh untuk bilangan positif.
=CEILING.MATH(-5,1) / / menghasilkan -5
=CEILING.MATH(-5,1;1) / / menghasilkan -5
=CEILING.MATH(-5,1;1;1) / / menghasilkan -6
=CEILING.MATH(-5,1;1;-1) / / menghasilkan -6
=CEILING.MATH(-5,1;1;TRUE) / / menghasilkan -6
CEILING.MATH vs CEILING
Fungsi CEILING.MATH berbeda dari fungsi CEILING dalam beberapa hal :
- CEILING.MATH menyediakan kelipatan default 1 untuk bilangan positif dn -1 untuk bilangan negatif.
- CEILING.MATH memberikan kendali eksplisit untuk pembulatan angka negatif (menuju nol, menjauhi nol).
- CEILING.MATH tampaknya menggunakan nilai absolut dari argumen signifikansi. Mengubah tanda signifikansi tidak berpengaruh pada hasil.
7. CEILING.PRECISE
Fungsi Excel CEILING.PRECISE membulatkan angka hingga kelipatan tertentu.
Penulisan rumus : =CEILING.PRECISE(number;[significance])
Argumen :
- number – angka yang harus dibulatkan.
- significance – [opsioal] beberapa untuk digunakan ketika pembulatan. Defaultnya adalah 1.
Secara default, CEILING.PRECISE membulatkan ke bilangan bulat terdekat, menggunakan signifikansi 1. Angka positif dengan bagian desimal dibulatkan dari nol ke kelipatan signifikansi berikutnya (misalny 5,3 dibulatkan menjadi 6), sedangkan angka negatif dengan bagian desimal dibulatkan ke arah nol (misalnya -5,3 dibulatkan 5).
=CEILING.PRECISE(5,3) / / menghasilkan 6
=CEILING.PRECISE(-5,3) / / menghasilkan -5
Membulatkan angka negatif
Fungsi CEILING.PRECISE selalu membulatkan angka negatif menuju nol, dan mengabaikan tanda signifikansi.
=CEILING.PRECISE(-7,1) / / menghasilkan -7
=CEILING.PRECISE(-7,1;1) / / menghasilkan -7
=CEILING.PRECISE(-7,1;-1) / / menghasilkan -7
CEILING.PRECISE vs CEILING
Tidak seperti fungsi CEILING, CEILING.PRECISE secara default adalah kelipatan 1, dan selalu membulatkan angka negatif menuju nol.
Fungsi CEILING.PRECISE berbeda dari fungsi CEILING dalam beberapa hal :
- CEILING.PRECISE default ke kelipatan 1 (yaitu dibulatkan ke bilangan bulat terdekat).
- CEILING.PRECISE selalu membulatkan angka negatif menuju nol.
- CEILING.PRECISE mengabaikan argumen tanda signifikansi (memakai nilai absolut).
8. DECIMAL
Fungsi Excel DECIMAL mengonversi angka alfa-numerik menjadi padanan desimalnya.
Penulisan rumus : =DECIMAL(number;radix)
Argumen :
- number – string karakter alfa-numerik.
- radix – bilangan dalam rentang [2, 36] yang mewakili basis dari bilangan yang akan dikonversi.
String masukan hanya boleh berisi karakter alfanumerik valid yang sesuai dengan basis yang disediakan (radix).
| Base (basis) | Karakter alfa-numerik |
|---|---|
| binary | 0 – 1 |
| octal | 0 -7 |
| decimal | 0- 9 |
| hexadecimal | 0-9 dan A-F |
9. EVEN
Fungsi Excel EVEN menghasilkan bilangan bulat genap berikutnya setelah membulatkan ke atas. Fungsi EVEN selalu membulatkan angka ke atas (menjauhi nol) sehingga angka positif menjadi lebih besar dan angka negatif menjadi lebih kecil (lebih negatif).
Penulisan rumus : =EVEN(number)
Argumen :
- number – angka yang akan dibulatkan menjadi bilangan bulat genap.
10. EXP
Fungsi Excel EXP menghasilkan konstanta e yang dipangkatkan dengan angka. Konstanta e adalah konstanta numerik yang berkaitan dengan pertumbuhan dan peluruhan eksponensial yang nilainya kira-kira 2,71828. Fungsi EXP adalah kebalikan dari fungsi LN (logaritma natural).
Penulisan rumus : =EXP(number)
Argumen :
- number – kekuatan e dinaikkan.
Fungsi EXP mencari nilai konstanta e yang dipangkatkan ke bilangan tertentu, sehingga fungsi EXP bisa dianggap sebagai e^(bilangn), di mana e ≈ 2,718. Fungsi eksponensial bisa dipakai untuk mendapatkan nilai e dengan melewatkan angka 1 sebagai argumen.
=EXP(0) / / menghasilkan 1
=EXP(1) / / menghasilkan 2,71828182846 (nilai e)
=EXP(2) / / menghasilkan 7,38905609893
Fungsi eksponensial memodelkan pertumbauhan eksponensial dan memiliki sifat unik di mana keluaran fungsi pada titik tertentu sebanding dengan laju perubahan fungsi pada titik tersebut. Kebalikan dari fungsi eksponensial adalah logaritma natural yang mewakili kebalikan dan pertumbuhan eksponensial, peluruhan eksponensial.
11. FACT
Fungsi Excel FACT menghasilkan faktorial dari bilangan tertentu. Misalnya, =FACT(3) menghasilkan 6, setara dengan 3 x 2 x 1.
Penulisan rumus : =FACT(number)
Argumen :
- number – angka yang akan diperoleh faktorialya.
12. FACTDOUBLE
Fungsi Excel FACTDOUBLE menghasilkan faktorial ganda dari sebuah angka. Faktorial ganda dilambangkan dengan dua tanda seru (!!).
Penulisan rumus : =FACTDOUBLE(number)
Argumen :
- number – angka lebih besar dari atau sama dengan -1.
Faktorisasi ganda dihitung secara berbeda untuk bilangan genap dan ganjil. Untuk bilangan genap, n, itu adalah hasil kali dari semua bilangan bulat genap kurang dari atau sama dengan n dan lebih besar dari atau atau sama dengan 2. Untuk bilangan ganjil, faktorial ganda adalah hasil kali dari semua bilangan bulat ganjil kurang dari atau sama dengan n dan lebih dari atau sama dengan 1. Faktorial ganda untuk nol dan -1 didefinisikan sebagai 1. untuk bilangan kurang dari -1, faktorial ganda tidak ditentukan.
Misalnya, faktorial ganda dengan argumen number adalah 10 :
=FACTDOUBLE(10) / / menghasilkan 3840
Contoh rumus diatas dapat menghasilkan 3840 dari 10 x 8 x 6 x 4 x 2.
Misalnya, faktorial ganda dengan argumen number adalah 8 :
=FACTDOUBLE(8) / / menghasilkan 384
Contoh rumus di atas, dapat menghasilkan 384 dari 8 x 6 x 4 x 2.
13. FLOOR
Fungsi Excel FLOOR membulatkan angka yang diberikan ke beberapa terdekat yang ditentukan. FLOOR berfungsi seperti fungsi MROUND, namun FLOOR selalu membulatkan ke bawah.
Penulisan rumus : =FLOOR(number;significance)
Argumen :
- number – angka yang harus dibulatkan.
- significance – kelipatan untuk dipakai ketika pembulatan.
Fungsi Excel FLOOR membulatkan angka ke bawah menjadi kelipatan tertentu. Kelipatan yang dipakai untuk pembulatan disediakan sebagai argumen signifikansi. Jika angkanya sudah kelipatan tepat, tidak ada pembulatan yang terjadi.
FLOOR berfungsi seperti fungsi MROUND, namun tidak seperti MROUND, yang membulatkan ke kelipata terdekat, FLOOR selalu dibulatkan ke bawah.
Contoh :
Untuk membulatkan angka A2 ke kelipatan 6 yang terdekat :
=FLOOR(A2;6) / / membulatkan ke bawah ke 5 terdekat
FLOOR bisa dipakai untuk menetapkan harga setelah konversi mata uang, diskon, dll. Misalnya, rumus di bawah ini akan membulatkan angka di sel A2 ke bawah ke dolar berikutnya, kemudian dikurangi 1 sen untuk menghasilkan harga seperti $2,99, $3,99, $6,99, dll.
=FLOOR(A2;1)-0,01
FLOOR memahami format waktu, dan bisa digunakan untuk membulatkan waktu ke kelipatan tertentu. Misalnya, untuk membulatkan waktu di sel A2 ke satuan 15 menit sebelumnya. Penulisan rumus seperti :
=FLOOR(A2;”0:15″) / / waktu putaran ke 15 menit terdekat
14. FLOOR.MATH
Fungsi Excel FLOOR.MATH membulatkan angka ke bawah menjadi kelipatan tertentu. Tidak seperti fungsi FLOOR, FLOOR.MATH secara default memiliki kelipatan 1 dan memberikan dukungan eksplisit untuk pembulatan angka negatif.
Penulisan rumus : =FLOOR.MATH(number;[significance];[mode])
Argumen :
- number – angka yang harus dibulatkan.
- siginficance – [opsional] beberapa untuk digunakan ketika pembulatan. Default = 1.
- mode – [opsional] untuk membulatkan angka negatif menuju atau menjauhi nol (defaultnya 0).
Secara default, FLOOR.MATH membulatkan ke bilangan bulat terdekat dengan menggunakan siginfikansi 1. Bilangan positif dengan nilai desimal dibulatkan ke bawah ke bilangan bulat terdekat (misalnya, 3,7 dibulatkan ke bawah menjadi 3) sedangkan bilangan negatif dengan nilai desimal dibulatkan ke bawah (menjauhi nol), misal -2,5 dibulatkan menjadi -3.
=FLOOR.MATH(7,4) / / menghasilkan 7
=FLOOR.MATH(-9,6) / / menghasilkan -10
Membulatkan angka negatif
Kontrol untuk membulatkan angka negatif menuju nol atau menjauhi nol, disediakan dengan argumen mode. Argumen mode (opsional) defaultnya ke nol. Jika argumen tersebut diisi nol atau dihilangkan, maka FLOOR.MATH membulatkan angka negatif menjauhi nol. Jika argumen mode diisi selain nol, maka FLOOR.MATH membulatkan angka negatif menuju nol. Untuk bilangan positif, argumen mode tidak berpengaruh.
=FLOOR.MATH(-8,1) / / menghasilkan -9
=FLOOR.MATH(-8,1;1) / / menghasilkan -9
=FLOOR.MATH(-8,1;1;1) / / menghasilkan -8
=FLOOR.MATH(-8,1;1;-1) / / menghasilkan -8
=FLOOR.MATH(-8,1;1;TRUE) / / menghasilkan -8
15. FLOOR.PRECISE
Fungsi Excel FLOOR.PRECISE membulatkan angka ke bawah menjadi kelipatan tertentu. FLOOR.PRECISE secara default adalah kelipatan 1, dan selalu membulatkan angka negatif menjauhi nol.
Penulisan rumus :=FLOOR.PRECISE(number;[significance])
Argumen :
- number – angka yang harus dibulatkan.
- significance – [opsional] beberapa opsional.
Secara default, fungsi FLOOR.PRECISE membulatkan ke bilangan bulat terdekat menggunakan signifikansi 1. Bilangan positif dengan nilai desimaal dibulatkan ke bawah menuju nol (misalnya, 6,9 dibulatkan ke bawah menjadi 6), sedangkan bilangan negatif dengan nilai desimal dibulatkan menjauhi nol (misalnya, -7,7 dibulatkan menjadi -8).
=FLOOR.PRECISE(6,9) / / menghasilkan 6
=FLOOR.PRECISE(-7,7) / / menghasilkan -8
Membulatkan angka negatif
=FLOOR.PRECISE(-3,1) / / menghasilkan -4
=FLOOR.PRECISE(-3,1;1) / / menghasilkan -4
=FLOOR.PRECISE(-3,1;-1) / / menghasilkan -4
16. GCD
Fungsi Excel GCD menghasilkan pembagi persekutuan terbesar dari dua atau lebih bilangan bulat. Pembagi persekutuan terbesar adalah bilangan buat terbesar yang masuk dalam semua bilangan yang disediakan tanpa sisa. Misalnya, =GCD(60;36) menghasilkan 12.
Penulisan rumus : =GCD(number1;[number2];…)
Argumen :
- number1 – bilangan pertama.
- number2 – [opsional] bilangan kedua.
Gunakan fungsi PBT untuk mendapatkan pembagi persekutuan terbesar dari dua bilangan bulat atau lebih. Pembagi persekutuan terbesar adalah bilangan bulat positif terbesar yang membagi angka tanpa sisa. Misalnya, penulisan rumus seperti :
=GCD(60;36)
Dari rumus di atas, menghasilkan 12 karena 12 merupakan faktor terbesar yang menghasilkan kedua angka secara merata.
17. INT
Fungsi Excel INT menghasilkan bagian bilangan bulat dari angka desimal dengan membulatkan ke bawah ke bilangan bulat. Perhatikan bahwa fungsi INT membulatkan ke bawah, sehingga angka negatif menjadi lebih negatif. Misalnya, ketika INT (10,9) menghasilkan 10, (-10,8) menghasilkan -11.
Penulisan rumus : =INT(number)
Argumen :
- number – angka yang diinginkan berupa bilangan bulat.
Contoh :
Ketika bilangan positif, fungsi INT selalu membulatkan ke bawah ke bilangan bulat terendah berikutnya :
=INT(5,25) / / menghasilkan 5
=INT(3,99) / / menghasilkan 5
Ketika membulatkan angka atau bilangan negatif, fungsi INT akan membulatkan ke bawah menjauhi nol :
=INT(11,8) / / menghasilkan 11
=INT(-11,8) / / menghasilkan -12
18. LCM
Fungsi Excel LCM menghasilkan persekutuan terkecil dari bilangan bulat. Kelipatan persekutuan terkecil adalah bilangan bulat positif terkecil yang merupakan kelipatan dari semua bilangan yang diberikan. Misalnya, =LCM(25;40) menghasilkan 200.
Penulisan rumus : =LCM(number1;[number2];…)
Argumen :
- number1 – bilangan pertama.
- number2 – [opsional] bilangan kedua.
Contoh :
Misalnya, =LCM(3;4) menghasilkan 12 karena 12 adalah kelipatan terkecil dari 3 dan 4. Sedangkan, =LCM(3;4;5) menghasilkan 60 karena 60 adalah kelipatan terkecil dari ketiga angka tersebut.
19. LN
Fungsi Excel LN menghasilkan logaritma natural dari bilangan tertentu.
Penulisan rumus : =LN(number)
Argumen :
- number – angka untuk diambil logaritma natural.
Fungsi logaritma natural adalah kebalikan dari fungsi eksponensial yang digunakan untuk memodelkan peluruhan eksponensial. Fungsinya setara dengan log base e suatu bilangan, di mana e adalah bilangan Euler.
Contoh :
=LN(1) / / menghasilkan 0
=LN(e) / / menghasilkan 1
=LN(e^2) / / menghasilkan 2
Bentuk ekuivalen dari fungsi logaritma natural diberikan oleh :
=LN(number)=LOG(number;e) / / dimana e ≈ 2,7128 atau EXP(1)
Grafik
Di bawah ini merupakan grafik logaritma log natural :
Fungsi logaritma natural dan fungsi eksponensial adalah kebalikan dari satu sama lain, seperti yang pada grafik di bawah :
Hubungan terbalik ini bisa direpresentasikan dengan rumus di bawah, dimana input ke fungsi LN adalah output dari fungsi EXP :
=LN(EXP(1)) / / menghasilkan 1
=LN(EXP(2)) / / menghasilkan 2
=LN(EXP(n)) / / menghasilkan n
20. LOG
Fungsi Excel LOG menghasilkan logaritma dari angka tertentu, menggunakan basis yang disediakan. Argumen dasar default ke 10 jika tidak diberikan.
Penulisan rumus : =LOG(number;[base])
Argumen :
- number – angka logaritma yang diinginkan.
- base – [opsional] basis logaritma. Defaultnya 10.
Fungsi LOG memungkinkan untuk mendapatkan logaritma angka dengan basis tertentu.
21. LOG10
Fungsi Excel LOG10 menghasilkan logaritma basis 10 angka. Misalnya, =LOG10(100) akan menghasilkan 2, dan =LOG10(10000) akan menghasilkan 4.
Penulisan rumus : =LOG10(number)
Argumen :
- number – angka logaritma yang diinginkan.
Contoh :
Fungsi LOG memungkinkan mendapatkan logaritma basis 10 dari bilangan positif.
=LOG10(10) / / menghasilkan 1
=LOG10(0,01) / / menghasilkan -2
=LOG10(1000) / / menghasilkan 3
22. MDETERM
Fungsi Excel MDETERM menghasilkan determinan matriks dari larik tertentu. Array atau larik input harus berisi hanya angka dan berupa matriks persegi dengan baris dan kolom yang sama. Hasilnya adalah bilangan yang mewaliki determinan matriks.
Penulisan rumus : =MDETERM(array)
Argumen :
- array – larik bilangan persegi.
Contoh :
Dalam matematika, determinan menggunakan nilai-nilai dalam matriks mengikuti definisi tertentu. Hasil MDETERM berupa angka yang mewakili determinan matriks. Argumen larik bisa diberikan sebagai rentang atau konstanta larik.
Dalam contoh yang di tampilkan, rumus dimasukkan di sel M4 :
=MDETERM(I4:K6)
23. MINVERSE
Fungsi Excel MINVERSE menghasilkan matriks invers dari larik yang diberikan. Array atau larik input harus berisi hanya angka dan berupa matriks persegi dengan baris dan kolom yang sama. Hasilnya adalah matriks terbalik dengan dimensi yang sama seperti larik yang disediakan.
Penulisan rumus : =MINVERSE(array)
Argumen :
- array – larik bilangan persegi.
Produk dari suatu matriks dan kebalikannya adalah matriks identitas. Matriks persegi n x n dengan satu diagonal utama dan nol di setiap posisi lainnya.
Supaya MINVERSE dapat menghitung matriks invers, array atau larik input harus berisi angka dan barupa matriks perssegi dengan baris dan kolom yang sama. Saat adanya invers, fungsi MINVERSE menghasilkan matriks invers dengan dimensi yang sama seperti array yang disediakan.
Dalam contoh yang ditampilkan di atas, rumus yang dimasukkan di seluruh rentang M4 : O6 adalah :
{=MINVERSE(I4:K6)}
Untuk menampilkan larik yang dihasilkan secara langsung di lembar kerja, MINVERSE harus dimasukkan sebagai rumus larik multisel dengan Ctrl + Shift + Enter.
24. MOD
Fungsi Excel MOD menghasilkan sisa dua angka setelah pembagian. Misalnya, =MOD(10;3) menghasilkan 1. Hasil dari MOD mempunyai tanda yang sama dengan pembagi.
Penulisan rumus : =MOD(number;divisor)
Argumen :
- number – jumlah yang akan dibagi.
- divisor – angka untuk pembagi.
Gunakan fungsi MOD untuk mendapatkan pengingat setelah pembagian. Misalnya, =MOD(3;2) menghasilkan 1, jika dijabarkan seperti pecahan campuran
Contoh :
=MOD(15;3) / / menghasilkan 0
=MOD(11;3) / / menghasilkan 2
=MOD(13;6) / / menghasilkan 1
=MOD(30;4) / / menghasilkan 2
Rumus alternatif
Dengan angka yang sangat besar, fungsi MOD akan menghasilkan kesalahan #NUM. Supaya bisa mendapat hasilnya, bisa mencoba versi alternatif menurut fungsi INT :
=number-(INT(number/divisor)*divisor)
25. MROUND
Fungsi Excel MROUND menghasilkan angka yang dibulatkan ke kelipatan tertentu. MROUND akan membuatkan angka ke atas atau ke bawah, bergantung pada kelipatan terdekat.
Penulisan rumus : =MROUND(number;significance)
Argumen :
- number – angka yang harus dibulatkan.
- significance – kelipatan yang digunakan ketika pembulatan.
Fungsi MROUND membulatkan angka ke kelipatan terdekat yang diberikan. Kelipatan yang dipakai untuk pembulatan disediakan sebagai argumen signifikansi. Jika angkanya sudah kelipatan tepat, tidak aada pembulatan yang terjadi dan nomor asli dikembalikan.
Contoh :
Untuk membulatkan A2 ke kelipatan 5 yang terdekat, gunakan fungsi MROUND dengan rumus seperti :
=MROUND(A2;5) / / dibulatkan ke terdekat 5
MROUND juga bisa digunakan untuk membulatkan harga hingga diakhiri dengan ..,99. Rumus di bawah ini akan membulatkan nilai dalam A2 ke 1 dolar terdekat, mengurangi 1 sen, dan menghasilkan harga akhir seperti $1,99, $3,99, $50,99, dll.
=MROUND(A2;1)-0,01 / / dibulatkan ke terdekat ..,99
MROUND bisa digunakan untuk putaran waktu. Untuk membulattkan waktu dalam A2 menjadi 15 menit terdekat, gunakan rumus :
=MROUND(A2;”0:15″) / / dibulatkan ke terdekat 15 menit
26. MUNIT
Fungsi Excel MUNIT menghasilkan matriks unit untuk dimensi yang diberikan, n, dengan ukuran n x n. Matriks yang dihasilkan ini berisi satu di diagonal utama dan nol di setiap posisi lainnya.
Penulisan rumus : =MUNIT(dimension)
Argumen :
- dimension – bilangan bulat (integer) untuk ukuran tertentu.
Contoh :
=MUNIT(5)
Fungsi MUNIT menghasilkan array atau larik 5 x 5 seperti :
| 1 | 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 0 | 0 |
| 0 | 0 | 1 | 0 | 0 |
| 0 | 0 | 0 | 1 | 0 |
| 0 | 0 | 0 | 0 | 1 |
27. ODD
Fungsi Excel ODD menghasilkan bilangan bulat ganjil berikutnya setelah membulatkan angka tertentu ke atas. Fungsi ODD selalu membulatkan angka ke atas (menjauhi nol) sehingga angka positif menjadi lebih besar dan angka negatif menjadi lebih kecil (yaitu lebih negatif).
Penulisan rumus : =ODD(number)
Argumen :
- number – angka yang akan dibulatkan menjadi bilangan bulat ganjil.
28. PI
Fungsi Excel PI menghasilkan nilai konstanta geometris π (pi). Nilai tersebut mewakili setengah rotasi dalam sistem sudut radian. Konstanta muncul dalam banyak rumus yang berkaitan dengan lingkaran seperti luas lingkaran.
Penulisan rumus : =PI()
Contoh :
Fungsi PI mengembalikan nilai π (pi) akurat hingga 15 digit. Nilai π mewakili setengah putaran dalam radian dan muncul dalam banyak rumus yang berkaitan dengan lingkaran.
=PI() / / menghasilkan 3,14159265358979
Untuk mengonversi sudut yang diukur dalam radian yaitu π, harus ada fungsi DEGREES yang bisa digunakan untuk mendapatkan sudut yang sesuai dalam derajat :
=DEGREES(PI()) / / menghasilkan 180o
=DEGREES(2*PI()) / / menghasilkan 360o
Konstanta π muncul dalam banyak rumus matematika yang berkaitan dengan lingkaran. Misalnya, rumus yang ditunjukkan di bawah ini yang menghasilkan keliling lingkaran dengan jari-jari 3 :
=2*PI()*3 / / keliling lingkaran r = 3
29. POWER
Fungsi Excel POWER menghasilkan angka ke pangkat yang diberikan. Fungsi POWER bekerja seperti eksponen dalam persamaan matematika standar.
Penulisan rumus : =POWER(number;power)
Argumen :
- number – angka untuk dipangkatkan.
- power – eksponen untuk menaikkan daya.
Di Excel, eksponensial ditangani dengan operator tanda sisipan (^), maka :
=POWER(2;3) / / menghasilkan 8
Hasil didapat dari 2^3 (2 pangkat 3) sehingga menghasilkan 8.
30. PRODUCT
Fungsi Excel PRODUCT menghasilkan produk angka yang disediakan sebagai argumen. Fungsi PRODUCT berguna saat mengalikan banyak sel secara bersamaan. Misalnya, rumus =PRODUCT(A2:A5) sama dengan =PRODUCT(A2;A3;A4:A5) atau =A2*A3*A4*A5.
Penulisan rumus : =PRODUCT(number1;[number2];….)
Argumen :
- number1 – angka atau rentang pertama yang akan dikalikan.
- number2 – [opsional] angka atau rentang kedua untuk dikalikan.
31. QUOTIENT
Fungsi Excel QUONTIENT menghasilkan pembagian integer (bilangan bulat) tanpa sisa.
Penulisan rumus : =QUOTIENT(numerator;denominator)
Argumen :
- numerator – angka, kuantitas yang akan dibagi.
- denominator – angka, pembagi.
Untuk pembagian dengan sisa, gunakan operator pembagian”/”.
32. RAND
Fungsi Excel RAND menghasilkan angka acak antara 0 dan 1. Misalnya, =RAND() akan menghasilkan angka seperti 0,422245717. RAND menghitung ulang ketika lembar kerja dibuka atau diubah.
Penulisan rumus : =RAND()
33. RANDBETWEEN
Fungsi Excel RANDBETWEEN menghasilkan bilangan bulat acak antara angka yang diberikan. RANDBETWEEN menghitung ulang saat lembar kerja dibuka atau diubah.
Penulisan rumus : =RANDBETWEEN(bottom;top)
Argumen :
- bottom – bilangan bulat mewakili nilai rentang yang lebih rendah.
- top – bilangan bulat yang mewakili nilai rentang yag lebih tinggi.
34. ROMAN
Fungsi Excel ROMAN mengoversi angka menjadi angka romawi sebagai teks. Misalnya, =ROMAN(5) menghasilkan V.
Penulisan rumus : =ROMAN(number;[form])
Argumen :
- number – angka (dalam bentuk arab) yang ingin diubah menjadi angka romawi.
- form – [opsional] jenis angka romawi yang diinginkan.
Contoh :
=ROMAN(5) / / menghasilkan “V”
Angka romawi
Tabel dibawah menunjukkan angka romawi yang tersedia dengan nilai setara dengan arabnya.
| Simbol | Nilai |
|---|---|
| I | 1 |
| V | 5 |
| X | 10 |
| L | 50 |
| C | 100 |
| D | 500 |
| M | 1000 |
35. ROUND
Fungsi Excel ROUND menghasilkan angka yang dibulatkan menjadi sejumlah digit. Fungsi ROUND bisa membulatkan ke kanan atau kiri koma desimal.
Penulisan rumus : =ROUND(number;num_digits)
Argumen :
- number – angka yang akan dibulatkan.
- num_digits – jumlah digit yang nomornya harus dibulatkan.
Jumlah tempat untuk dibulatkan dikendalikan oleh jumlah digit yang diberikan untuk ROUND, seperti pada tabel berikut :
| Digit | Peranan |
|---|---|
| >0 | bulat terdekat ..,1 ; ..,01 ; ..,001 ; dll |
| <0 | bulat terdekat 10 ; 100 ; 1000 ; dll |
| =0 | bulat terdekat 1 |
Contoh :
Untuk membulatkan nilai di sebelah kanan koma demisal, gunakan angka positif untuk digit :
=ROUND(A1;1) / / menghasilkan 1 tempat desimal
=ROUND(A1;2) / / menghasilkan 2 tempat desimal
Untuk membulatkan nilai ke bawah di sebelah kiri koma desimal, gunakan nol atau angka negatif untuk digit :
=ROUND(A1;0) / / dibulatkan ke bilangan bulat terdekat
=ROUND(A1;-1) / / dibulatkan ke 10 terdekat
=ROUND(A1;-2) / / dibulatkan ke 100 terdekat
Operasi dan fungsi lain bisa ditempatkan di dalam fungsi ROUND. Misalnya, untuk membulatkan hasil A2 dibagi B2, bisa gunakan fungsi ROUND seperti :
=ROUND(A2/B2;0) / / hasil dibulatkan ke bilangan terdekat
36. ROUNDDOWN
Fungsi Excel ROUNDDOWN menghasilkan angka yang dibulatkan ke bawah di sejumlah tempat tertentu. Tidak seperti pembulatan standar, dimana hanya angka kurang dari 5 yang dibulatkan ke bawah, ROUNDDOWN akan membulatkan semua angka ke bawah.
Penulisan rumus : =ROUNDDOWN(number;num_digits)
Argumen :
- number – angka yang akan dibulatkan.
- num_digits – jumlah digit yang bilangannya harus dibulatkan ke bawah.
Jumlah tempat untuk dibulatkan dikendalikan oleh argumen num_digits. Bilangan positif dibulatkan di sebelah kanan koma desimal, bilangan negatif dibulatkan ke kiri, dan nol dibulatkan ke 1 terdekat. Bisa dilihat pada tabel dibawah :
| Digit | Peranan |
|---|---|
| >0 | bulat terdekat ..,1 ; ..,01 ; ..,001 ; dll |
| <0 | bulat terdekat 10 ; 100 ; 1000 ; dll |
| =0 | bulat terdekat 1 |
Untuk membulatkan nilai ke bawah di sebelah kanan koma desimal, gunakan angka positif untuk digit :
=ROUNDDOWN(A1;1) / / dibulatkan ke bawah 1 tempat desimal
=ROUNDDOWN(A1;2) / / dibulatkan ke bawah 2 tempat desimal
Untuk membulatkan nilai ke bawah di sebelah kiri koma desimal, gunakan nol atau angka negatif untuk digit :
=ROUNDDOWN(A1;0) / / dibulatkan ke bawah ke bilangan bulat terdekat
=ROUNDDOWN(A1;-1) / / dibulatkan ke bawah 10 terdekat
=ROUNDDOWN(A1;-2) / / dibulatkan ke bawah 100 terdekat
Operasi dan fungsi lain bisa ditemptkan di dalam fungsi ROUNDDOWN. Misalnya, untuk membulatkan ke bawah hasil A2 dibagi B2, bisa gunakan fungsi ROUNDDOWN seperti :
=ROUNDDOWN(A2/B2;0) / / hasil dibulatkan ke bawah ke bilangan terdekat
37. ROUNDUP
Fungsi Excel ROUNDUP menghasilkan angka yang dibulatkan ke sejumlah tempat desimal tertentu. Tidak seperti pembulatan standar, dimana angka kurang dari 5 dibulatkan ke bawah, ROUNDUP membulatkan semua angka ke atas.
Penulisan rumus : =ROUNDUP(number;num_digits)
Argumen :
- number – angka yang akan dibulatkan.
- num_digits – jiumlah digit yang angkanya harus dibulatkan.
Jumlah tempat untuk dibulatkan dikendalikan oleh argumen num_digits. Bilangan positif dibulatkan di sebelah kanan koma desimal, bilangan negatif dibulatkan ke kiri, dan nol dibulatkan ke 1 terdekat. Bisa dilihat pada tabel dibawah :
| Digit | Peranan |
|---|---|
| >0 | bulat terdekat ..,1 ; ..,01 ; ..,001 ; dll |
| <0 | bulat terdekat 10 ; 100 ; 1000 ; dll |
| =0 | bulat terdekat 1 |
Untuk membulatkan nilai di sebelah kanan koma desimal, gunakan angka positif untuk digit :
=ROUNDUP(A1;1) / / bulatkan menjadi 1 tempat desimal
=ROUNDUP(A1;2) / / bulatkan hingga 2 tempat desimal
Untuk membulatkan nilai di sebelah kiri koma desimal, gunakan nol atau angka negatif untuk digit :
=ROUNDUP(A1;0) / / bulatkan ke bilangan bulat terdekat
=ROUNDUP(A1;-1) / / bulatkan ke 10 terdekat
=ROUNDUP(A1;-2) / / bulatkan ke 100 terdekat
Operasi dan fungsi lain dapat ditempatkan di dalam ROUNDOWN. Misalnya, untuk membulatkan hasil A2 dibagi B2, Anda dapat menggunakan rumus seperti ini :
=ROUNDUP(A2/B2;0) / / membulatkan hasil ke bilangan bulat terdekat
38. SIGN
Fungsi Excel SIGN menghasilkan tanda angka sebagai +1, -1 atau 0. Jika angka positif, SIGN menghasilkan 1. Jika angka negatif, tanda menghasilkan -1. Jika angka nol, SIGN menghasilkan 0.
Penulisan rumus : =SIGN(number)
Argumen :
- number – nomor yang ditanda tangani.
39. SQRT
Fungsi Excel SQRT menghasilkan akar kuadrat dari bilangan positif. SQRT menghasilkan kesalahan jika angka negatif.
Penulisan rumus : =SQRT(number)
Argumen :
- number – bilangan yang akan memperoleh akar kuadrat.
Fungsi SQRT menghasilkan kesalahan #NUM saat diminta untuk mengambil akar kuadrat dari bilangan negatif. Jika ingin mendapatkan akar kuadrat dari nilai negatif (seolah-olah nilai positif), bisa membungkus angka tersebut dengan tambahan fungsi ABS seperti :
=SQRT(ABS(A2))
40. SUBTOTAL
Fungsi Excel SUBTOTAL menghasilkan agregat untuk nilai yang diberikan. SUBTOTAL bisa menghasilkan SUM, AVERAGE, COUNT, MAX, dan lainnya. Fungsi SUBTOTAL bisa menyertakan atau mengecualikan nilai dalam baris tersembunyi.
Penulisan rumus : =SUBTOTAL(function_num;ref1;[ref2];…)Argumen :
- function_num – angka yang menentukan fungsi mana yang akan dipakai dalam menghitung subtotal dalam daftar (Lebih lengkap bisa lihat pada tabel dibawah).
- ref1 – rentang bernama atau referensi ke subtotal.
- ref2 – [opsional] rentang bernama atau referensi ke subtotal.
Contoh :
Berikut adalah beberapa contoh SUBTOTAL yang dikonfigurasi ke fungsi SUM, COUNT, dan AVERAGE nilai dalam suatu rentang. Perhatikan baik-baik, perbedaan adalah nilai yang digunakan untuk argumen function_num :
=SUBTOTAL(109;range) / / SUM
=SUBTOTAL(103;range) / / COUNT
=SUBTOTAL(101;range) / / AVERAGE
Perhitungan yang tersedia
Peranan SUBTOTAL dikendalikan oleh argumen function_num, yang disediakan sebagai nilai numerik. Ada 11 fungsi yang tersedia, masing-masing dengan dua opsi, seperti yang terlihat pada tabel di bawah. Perhatikan bahwa nilainya “berpasangan” (misalnya 1-101, 2-102, 3-103, dan seterusnya). Ini terkait dengan cara SUBTOTAL menangani baris yang disembunyikan secara manual. Ketika function_num antara 1-11, SUBTOTAL menyertakan sel yang telah disembunyikan secara manual. Jika function_num antara 101-111, SUBTOTAL mengecualikan nilai dalam baris yang telah disembunyikan secara manual.
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
41. SUM
Fungsi Excel SUM menghasilkan jumlah nilai yang diberikan. Nilai-nilai ini dapat berupa angka, referensi sel, rentang, larik, dan konstanta, dalam kombinasi apa pun. SUM dapat menangani hingga 255 argumen individual.
Penulisan rumus : =SUM(number1;[number2];[number3];…)
Argumen :
- number1 – nilai pertama untuk dijumlahkan.
- number2 – [opsional] nilai kedua untuk dijumlahkan.
- number3 – [opsional] nilai ketiga untuk dijumlahkan.
Contoh :
Pada contoh yang ditunjukkan di atas, penulisan rumus di sel D10 :
=SUM(D4:D8) / / menghasilkan Rp37.700,00
Referensi tidak perlu bersebelahan. Misalnya, untuk menjumlahkan harga buah nanas, pisang, dan anggur, maka gunakan rumus seperti :
=SUM(D4;D7;D8) / / menghasilkan Rp26.500,00
42. SUMIF
Fungsi Excel SUMIF menghasilkan jumlah sel yang memenuhi satu keadaan. Kriteria bisa diterapkan pada tanggal, angka, dan teks. Fungsi SUMIF mendukung operator logika (>, <, <>, =) dan karaker pengganti (*, ?) untuk pencocokan parsial.
Penulisan rumus : =SUMIF(range;criteria;[sum_range])
Argumen :
- range – rentang sel yang diterapkan kriteria.
- criteria – kriteria yang dipakai untuk menentukan sel mana yang akan ditambahkan.
- sum_range – [opsional] sel yang akan ditambahkan. Jika sum_range dihilangkan, sel dalam rentang akan ditambahkan bersamaan.
SUMIF berada dalam grup delapan fungsi di Excel yang memisahkan kriteria logis menjadi dua bagian (rentang + kriteria). Akibatnya, sintaks atau rumus yang dipakai untuk kriteria berbeda, dan SUMIF membutuhkan rentang sel untuk argumen range, tidak bisa menggunakan larik.
SUMIF hanya mendukung satu keadaan. Jika ingin menerapkan beberapa kriteria, gunakan fungsi SUMIFS. Jika ingin memanipulasi nilai yang muncul dalam argumen range (yaitu mengekstrak tahun dari tanggal untuk dipakai dalam kriteria), bisa dilihat pada fungsi SUMPRODUCT dan/atau FILTER.
Penggunaan dasar
Pada contoh lembar kerja yang ditunjukkan di atas, ada 3 rumus SUMIF. Pada rumus pertama di sel G3, SUMIF dikonfigurasi untuk menjumlahkan nilai yang lebih besar dari 1.500.000. Pada rumus kedua di sel G4, SUMIF menghasilkan jumlah nilai rep “Sarah”. Pada contoh ketiga di sel G5, SUMIF dikonfigurasi untuk menjumlahkan nilai dengan kota bagian “JKT” (Jakarta).
=SUMIF(D3:D7;”>1.500.000″) / / nilai >1.500.000
=SUMIF(B3:B7;”Sarah”;D3:D7) / / Rep = Sarah
=SUMIF(C3:C7;”jkt”;D3:D7) / / Kota = JKT
Perhatikan untuk tanda =, >, < <> pada argumen criteria, untuk tanda “=” tidak perlu ditulis. Untuk >, <, <> perlu ditulis. Perhatikan juga bahwa SUMIF tidak peka terhadap huruf besar kecil.
Kriteria di sel lain
Nilai dari sel lain dapat dimasukkan dalam kriteria menggunakan penggabungan. Pada contoh di bawah, SUMIF menghasilkan jumlah semua penjualan di atas nilai di G2. Perhatikan tanda lebih dari (>), yaitu teks, harus diapit tanda kutip. Penulisan rumus di sel G3 :
=SUMIF(D3:D7;”>”&G2)
Tidak sebanding dengan
Untuk mengekspresikan kriteria “tidak sama dengan”, gunakan tanda “<>” yang diapit tanda kutip ganda (“..”) :
=SUMIF(B3:B7;”<>merah”;C3:C7)
=SUMIF(B3:B7;”<>biru”;C3:C7)
=SUMIF(B3:B7;”<>”&E5;C3:C7)
Sel kosong
SUMIF dapat menghitung jumlah berdasarkan sel yang kosong atau tidak kosong. Pada contoh di bawah, SUMIF digunakan untuk menjumlahkan jumlah di kolom C bergantung pada apakah kolom D berisi “x” atau kosong :
=SUMIF(D3:D7;” “;C3:C7) / / kosong
=SUMIF(D3:D7;”<>”;C3:C7) / / tidak kosong
Tanggal
Cara terbaik untuk menggunakan SUMIF untuk tanggal adalah dengan merujuk ke tanggal yang valid di sel lain, atau menggunakan fungsi DATE. Contoh di bawah menunjukkan kedua metode tersebut :
=SUMIF(B3:B7;”<“&DATE(2019;3;1);C3:C7)
=SUMIF(B3:B7;”>”&DATE(2019;4;1);C3:C7)
=SUMIF(B3:B7;”>”&E7;C3:C7)
Karakter pengganti
Fungsi SUMIF mendukung karakter pengganti, seperti yang terlihat pada contoh di bawah :
=SUMIF(B3:B7;”*arta”;C3:C7) / / diakhiri dengan “arta”
=SUMIF(B3:B7;”ja*”;C3:C7) / / dimulai dengan “ja”
=SUMIF(B3:B7;”?????”;C3:C7) / / berisi 5 karakter
43. SUMIFS
Fungsi Excel SUMIFS, fungsi untuk menjumlahkan sel yang memenuhi beberapa kriteria. SUMIFS bisa digunakan menjumlahkan nilai ketika sel yang berdekatan memenuhi kriteria berdasarkan tanggal, angka dan teks. SUMIFS mendukung operator logika (>, <, <>, =) dan karakter pengganti (*, ?) untuk pencocokan parsial.
Penulisan rumus : =SUMIFS(sum_range;range1;criteria1;[range2];[criteria2];….)
Argumen :
- sum_range – rentang yang akan dijumlahkan.
- range1 – rentang pertama yang dievaluasi.
- criteria1 – kriteria untuk dipakai di range1.
- range2 – [opsional] rentang kedua untuk dievaluasi.
- criteria2 – [opsional] kriteria untuk dipakai di range2.
Contoh :
Pada contoh yang ditampilkan di atas, untuk hasil di sel I3, SUMIFS dikonfigurasi untuk menjumlahkan nilai di kolom F saat warna di kolom C adalah “merah”. Pada contoh kedua, untuk hasil di sel I5, SUMIFS diatur untuk menjumlahkan nilai di kolom F hanya kalau warnanya “merah” dan kota bagiannya adalah Jakarta (JKT)
=SUMIFS(F4:F10;C4:C10;”merah”) / / jumlah jika merah
=SUMIFS(F4:F10;C4:C10;”merah”;D4:D10;”JKT”) / / jumlah jika merah dan JKT
44. SUMPRODUCT
Fungsi Excel SUMPRODUCT mengalikan rentang atau larik bersama-sama dan menghasilkan jumlah produk. SUMPRODUCT adalah fungsi yang sangat serbaguna yang bisa dipakai untuk menghitung dan menjumlahkan seperti COUNTIFS atau SUMIFS, namun dengan lebih banyak fleksibilitas. Fungsi lain bisa dengan mudah digunakan di dalam fungsi SUMPRODUCT untuk memperluas fungsionalitas.
Penulisan rumus : =SUMPRODUCT(array1;[array2];….)
Argumen :
- array1 – larik atau rentang pertama yang akan dikalikan, lalu ditambahkan.
- array2 – [opsional] larik atau rentang kedua yang akan dikalikan, lalu ditambahkan.
SUMPRODUCT untuk jumlah dan hitungan bersyarat
Ada beberapa data pesanan di A2:A6 dengan negara di kolom A, penjualan di kolom B :
| A | B | |
|---|---|---|
| 1 | Negara | Penjualan |
| 2 | UT | 75 |
| 3 | CO | 100 |
| 4 | TX | 125 |
| 5 | CO | 125 |
| 6 | TX | 150 |
Menggunakan SUMPRODUCT, bisa menghitung total penjualan untuk Texas (TX) dengan rumus seperti :
=SUMPRODUCT(–(A2:A6=”TX”))
=SUMPRODUCT(–(A2:A6=”TX”);B2:B6)
Catatan : Jangan bingung dengan negatif ganda. Ini adalah trik umum yang dipakai dalam rumus Excel tingkat lanjut untuk memaksa nilai TRUE dan FALSE menjadi 1 dan 0.
Untuk contoh pada penjumlahan di atas, berikut adalah representasi virtual dari dua larik ketika pertama kali diproses oleh SUMPRODUCT :
| array1 | array2 |
|---|---|
| FALSE | 75 |
| FALSE | 100 |
| TRUE | 125 |
| FALSE | 125 |
| TRUE | 150 |
Setiap larik (array) memiliki 5 item. Larik pertama berisi nilai TRUE/FALSE yang dihasilkan dari A2:A6 = “TX”, dan larik kedua adalah konten B2:B6. Setiap item di larik pertama akan dikalikan dengan item yang sesuai di larik kedua. Namun, dalam kondisi ini, hasil SUMPRODUCT akan menjadi nol karena nilai TRUE dan FALSE akan diberlakukan sebagai nol. Oleh karena itu, dibutuhkanlah item dalam array1 untuk menjadi numerik, dan perlu “dipaksa” menjadi satu dan nol. Maka disinilah terdapat negatif ganda.
Dengan menggunakan negatif ganda, TRUE/FALSE dapat dipaksa ke dalam nilai numerik satu dan nol, seperti yang ditunjukkan pada representasi virtual di bawah ini. Pada kolom produk, mewakili hasil perkalian dua larik. Hasil yang dijumlahkan adalah 275 yang merupakan nilai dari yang dihasilkan SUMPRODUCT.
Menggunakan penulisan rumus (sintaks) kurung kurawal untuk array, contohnya seperti :
=SUMPRODUCT({0;0;1;0;1};{75;100;125;125;150})
Dan rumus seperti ini setelah perkalian :
=SUMPRODUCT({0;0;125;0;150})
SUMPRODUCT dengan fungsi lain
SUMPRODUCT bisa menggunakan fungsi lain secara langsung. SUMPRODUCT digunakan dengan fungsi LEN untuk menghitung total karakter dalam rentang.
Karena SUMPRODUCT dibuat untuk bekerja dengan array, SUMPRODUCT bisa melakukan penghitungan pada array secara langsung.
Misalnya, ada 10 nilai teks yang berbeda di A2:A11, kemudian ingin menghitung total karakter untuk semua 10 nilai. Bisa menambahkan kolom pembantu di kolom B yang menggunakan rumus LEN (A2) untuk menghitung karakter di setiap sel. Penulisan rumus :
=SUMPRODUCT(LEN(A2:A11)
45. TRUNC
Fungsi Excel TRUNC menghasikan angka yang terpotong berdasarkan jumlah digit (opsional). MIsalnya, =TRUNC(4,9) akan menghasilkan 4, dan =TRUNC(-3,5) akan menghasilkan -3. Fungsi TRUC tidak membulatkan, hanya memotong eberapa koma sesuai pada contoh yang ditampilkan.
Penulisan rumus : =TRUNC(number;[num_digits])
Argumen :
- number – angka yang akan dipotong.
- num_digits – [opsional] ketepatan pemotongan (standarnya adalah 0).
Contoh :
Misalnya, =TRUNC(4,9) akan menghasilkan 4, dan =TRUNC(-3,5) akan menghasilkan -3. TRUNC tidak melakukan pembulatan apapun, ini hanya menghasilkan bagian bilangan bulat dari nomor tersebut.
=TRUNC(4;9) / / menghasilkan 4
=TRUNC(-3,5) / / menghasilkan -3
TRUNC juga bisa digunakan untuk menghasilkan sejumlah tempat desimal tanpa pembulatan, menggunakan argumen num_digits. Misalnya, =TRUNC(PI();2) akan menghasilkan 3,14 dan =TRUNC(PI();3) akan menghasilkan 3,141.
=TRUNC(PI();2) / / menghasilkan 3,14
=TRUNC(PI();3) / / menghasilkan 3,141
Demikian penjelasan mengenai rumus fungsi Excel Math, semoga bermanfaat.