Senin, 25 Mei 2020
Setelah kita mengetahui tentang elemen yang terdapat dalam formula spreadsheet, saatnya kita belajar tentang macam-macam formula dan juga cara penulisannya. Sebelumnya buat contoh data sederhana berisi angka sebagai berikut:
Berdasarkan contoh di atas kita akan menerapkan fungsi dasar spreadsheet. Berikut uraian lengkapnya.
SUM
Kita sering menemui rumus ini dalam spreadsheet. Rumus SUM dapat membantu untuk mendapatkan nilai total dari rentang sel yang dipilih. Penggunaannya dengan cara menyorot semua baris atau kolom yang ingin diketahui jumlahnya. Penulisannya sebagai berikut:
- =SUM(data ke-1, data ke-2, …. , data ke-n)
Contoh kasus:
Bagaimana cara mengetahui total semua stok dan barang yang yang terjual berdasarkan data penjualan ATK di atas?
Pertama kita menghitung total dari stok barang dengan rumus =SUM(B3:B12). Sehingga sel yang dihitung mulai dari B3 hingga B12 menghasilkan nilai 2550.
Selanjutnya kita hitung jumlah barang yang terjual. Masih menggunakan rumus fungsi yang sama yaitu =SUM(C3:C12)sehingga sel yang dihitung mulai dari C3 hingga C12 menghasilkan nilai 1150.
Berdasarkan perhitungan kita dapat mengetahui total stok barang adalah 2550 unit dan total barang yang terjual adalah 1150 unit.
SUMIF
Selanjutnya kita membahas fungsi SUMIF. Fungsi ini berbeda dari SUM yang biasa karena SUMIF ini berfungsi untuk menjumlahkan data dengan kriteria tertentu. Penulisan fungsi dari SUMIF sebagai berikut:
- =SUMIF(range,”kriteria”,sum_range)
Contoh kasus
Kita akan menggunakan contoh data di bawah ini:
Misalnya, kita ingin mengetahui berapa total buku tulis berdasarkan data di atas?
Jawab:
Berdasarkan contoh tabel data di atas, kita dapat menuliskan rumus SUMIF pada sel G3 sebagai berikut:
Pada rumus di atas, range diisi dengan sel C3 sampai C12 yang merupakan kumpulan nama barang yang terjual dari tanggal 01 Februari 2020 sampai 04 Februari 2020. Kemudian kriteria diisi dengan nama barang yang ingin dicari jumlahnya dan dalam contoh ini menggunakan “Buku tulis”. Kriteria dapat diisi dengan teks, angka, ataupun ekspresi. Sedangkan sum_range diisi dengan sel-sel yang ingin dijumlahkan datanya yang ditunjukkan pada sel D3 sampai D12. Sehingga menghasilkan total buku tulis yang terjual adalah 295 unit.
SUMIFS
Kemudian ada lagi fungsi yang bernama SUMIFS. Tahukah Anda perbedaan antara SUMIF dan SUMIFS? Secara penggunaan sama yaitu untuk menjumlahkan data dengan kriteria tertentu. Apabila SUMIF hanya bisa menggunakan satu kriteria saja, pada SUMIFS kita bisa menggunakan lebih dari satu kriteria. Penulisannya sebagai berikut:
- =SUMIFS(sum_range, kriteria_range1, “kriteria1”, kriteria_range2, “kriteria2”, dan seterusnya)
Contoh kasus:
Masih menggunakan contoh data di bawah ini:
Kali ini kita ingin mengetahui berapa total buku tulis yang terjual dari tanggal 01 Februari 2020 sampai 02 Februari 2020?
Jawab:
Berdasarkan contoh tabel data di atas, kita dapat menuliskan rumus SUMIF pada sel G3 sebagai berikut:
- =SUMIFS(D3:D12,C3:C12,"Buku tulis",B3:B12,">=01/02/2020",B3:B12,"<=02/02/2020")
Pada contoh penjelasan gambar dan rumus di atas, sum_range diisi dengan sel sel yang ingin dijumlahkan datanya. Kriteria_range1 berisi nama barang yang ditunjukkan oleh sel C3 sampai C12. Kriteria1 yang ingin kita cari adalah Buku tulis dan Kriteria2 yang kita cari adalah buku tulis yang terjual pada atau setelah tanggal 01 Februari 2020 dan yang terjual pada atau sebelum tanggal 02 Februari 2020. Sehingga buku tulis yang terjual sejumlah 175 unit.
AVERAGE
Masing ingatkah Anda dengan rata-rata aritmatika? Fungsi AVERAGE sama dengan rata-rata aritmatika yang menjumlahkan semua data kemudian dibagi dengan jumlah data yang ada. Penggunaannya hampir sama dengan rumus SUM yaitu sebagai berikut:
- =AVERAGE(data ke-1, data ke-2, … , data ke-n)
Contoh kasus:
Bagaimana jika kita ingin mengetahui berapa rata-rata dari semua barang yang terjual dalam tabel data penjualan ATK?
Kita dapat menghitung rata-rata jumlah semua barang yang terjual dengan rumus =AVERAGE(C3:C12)sehingga sel yang dihitung mulai dari C3 hingga C12 menghasilkan nilai 115.
Berdasarkan perhitungan kita dapat mengetahui rata-rata dari semua barang yang terjual adalah 115 unit.
COUNT
Count merupakan fungsi yang dapat menghitung jumlah sel terpilih dalam rentang tertentu yang berisi nilai numerik. Penggunaan fungsi COUNT sebagai berikut:
- =COUNT(data ke-1, data ke-2, … , data ke-n)
Contoh kasus:
Dalam contoh penggunaan COUNT, kita modifikasi sedikit tabel data penjualan ATK untuk memperjelas penggunaan fungsi ini. Contoh perubahannya sebagai berikut:
Pertanyaannya adalah bagaimana kita mengetahui jumlah produk yang berhasil terjual berdasarkan kolom terjual?
Kita mulai dengan menuliskankan rumus =COUNT(C3:C12) pada sel C13. Lebih jelasnya bisa dilihat pada gambar di bawah ini.
Berdasarkan perhitungan di atas kita dapat mengetahui barang yang berhasil terjual ada 8 jenis. Apakah Anda tahu kenapa hasilnya 8?
Pada fungsi COUNT hanya menghitung nilai numerik (hanya angka saja) sehingga data yang berupa non-numerik dan sel kosong akan dilewati.
COUNTA
Sama seperti COUNT, COUNTA dapat menghitung jumlah sel terpilih dalam rentang tertentu. Namun bedanya dalam COUNTA kita dapat menghitung semua sel yang terpilih, tak peduli apa pun jenis data yang ada di dalamnya (angka, teks, tanggal, kondisi benar atau salah, hingga kesalahan perhitungan). Tetapi terdapat satu yang dilewati dalam perhitungan COUNTA yaitu sel yang kosong. Penggunaan rumus COUNTA sebagai berikut:
- =COUNTA(data ke-1, data ke-2, … , data ke-n)
Contoh kasus:
Pada contoh kasus ini kita masih menggunakan tabel sebelumnya yang digunakan untuk fungsi COUNTA.
Pertanyaannya adalah bagaimana kita mengetahui berapa jenis barang yang terjual berdasarkan kolom terjual? (termasuk yang belum terjual dan sel tidak kosong).
Kita lakukan hal yang sama seperti fungsi sebelumnya, namun di sini menggunakan rumus =COUNTA(C3:C12) pada sel C13. Untuk lebih jelasnya lihatlah gambar di bawah ini.
Berdasarkan gambar di atas, COUNTA dari data terjual adalah 9 karena menghitung semua jenis data hanya melewati sel yang kosong saja.
COUNTIF dan COUNTIFS
Tahukah Anda apa fungsi dari COUNTIF? COUNTIF sering digunakan untuk menghitung banyaknya data pada kumpulan sel dengan kriteria tertentu. Sistematika penulisannya sebagai berikut:
- =COUNTIF(range,kriteria)
- Range : Data COUNTIF yang dihitung.
- Kriteria : Kondisi tertentu yang diinginkan untuk diketahui. Bisa berisi teks, operasi logika, ataupun angka.
Sedangkan COUNTIFS berfungsi untuk menghitung banyaknya data pada kumpulan sel berdasarkan kriteria (lebih dari satu). Sistematika penulisannya sebagai berikut:
- =COUNTIFS(kriteria_range1,kriteria1, kriteria_range2, kriteria2, dan seterusnya)
- Kriteria_range1 : range pertama yang dihitung jumlah datanya.
- Kriteria1 adalah kondisi tertentu yang diinginkan untuk diketahui.
- Kriteria_range2,kriteria2 adalah kriteria atau kondisi berikutnya yang ingin diketahui.
Contoh kasus:
Kita akan menggunakan contoh tabel berikut:
Berdasarkan tabel di atas, kita ingin mengetahui:
- Berapa orang yang berjenis kelamin laki-laki?
- Berapa orang yang berjenis kelamin laki-laki dan hobi bermain sepak bola?
Jawab:
Untuk mengetahui berapa orang yang berjenis kelamin laki-laki, kita dapat menggunakan fungsi COUNTIF seperti berikut:
Penulisan fungsi yang digunakan =COUNTIF(C3:C12,"L")
Sedangkan untuk mengetahui berapa orang yang berjenis kelamin laki-laki dan memiliki hobi sepak bola, kita dapat menggunakan fungsi COUNTIFS seperti berikut:
Penulisan fungsi yang digunakan =COUNTIFS(C3:C12,"L",D3:D12,"Sepak bola")
Min dan Max
Apakah Anda pernah menggunakan fungsi min max sebelumnya? Kedua fungsi ini berguna untuk menentukan nilai terendah (MIN) dan tertinggi (MAX) dari suatu rentang data yang terpilih. Contoh penggunaan rumus min max seperti berikut:
- =MIN(data ke-1, data ke-2, …, data ke-n)
- =MAX(data ke-1, data ke-2, …, data ke-n)
Contoh kasus:
Kita kembali menggunakan tabel data penjualan ATK di bawah ini untuk menerapkan contoh kasus penggunaan Min Max.
Nah berdasarkan tabel di atas, apa yang kita lakukan jika ingin mengetahui barang apa yang paling banyak dan paling sedikit terjual?
- Pertama, untuk mengetahui barang apa yang paling banyak terjual gunakan rumus =MAX(C3:C12).
Rumus MAX di atas memilih rentang data mulai dari C3 hingga C12. Sehingga dapat disimpulkan bahwa barang yang terjual paling banyak adalah buku tulis sejumlah 300 penjualan. - Selanjutnya untuk mengetahui barang apa yang paling rendah penjualannya, carilah nilai minimal. Rumus yang dapat diterapkan adalah =MIN(C3:C12).
Rumus MIN di atas memilih rentang data mulai dari C3 hingga C12. Kesimpulannya, barang yang terjual lebih sedikit dibanding yang lain adalah rautan pensil, tempat pensil, dan gunting dengan jumlah masing-masing sebanyak 50 penjualan.
Trim
Selanjutnya fungsi TRIM digunakan untuk menghilangkan ruang kosong yang tidak dibutuhkan pada sebuah teks. Fungsi ini hanya berjalan pada sel tunggal bukan rentang sel. Penggunaan rumusnya adalah sebagai berikut:
- =TRIM(teks)
Contoh kasus:
Untuk menerapkan fungsi TRIM, kita gunakan contoh kasus dengan data seperti berikut:
Terlihat pada contoh di atas bahwa data yang ada terlalu banyak spasi dan tidak rapi. Di sinilah kegunaan fungsi TRIM. Terapkan rumus =TRIM(A2) pada sel B2 sebagai berikut:
Hasilnya setelah menggunakan fungsi TRIM, data yang dihasilkan pun menjadi lebih rapi. Tampilan akhirnya sebagai berikut:
Replace
Selanjutnya terdapat fungsi Replace. Tahukah Anda apakah fungsi Replace dalam spreadsheet? Replace dapat digunakan untuk mengganti string baik berupa angka maupun teks biasa. Sistematika penulisan Replace sebagai berikut:
- =REPLACE(text, position, length, new_text)
Contoh kasus:
Pada contoh data di atas, terdapat 3 data yang ingin kita ganti, yaitu:
- Buka diganti menjadi Buku
- Saya membaca buku diganti menjadi Saya menulis buku
- Saya123 diganti menjadi Aku123
- Untuk contoh pertama kita menerapkan fungsi sebagai berikut pada sel B2
Berikut penjelasannya sesuai sistematika penulisannya:- Text : Dapat diisi dengan teks secara langsung atau alamat sel. Dalam contoh di atas menggunakan alamat sel A2.
- Position : Posisi atau urutan dari huruf yang ingin diganti. Dalam hal ini huruf “a” yang ingin kita ganti berada diurutan ke 4 dari kata “Buka”
- Length : Panjang karakter yang ingin kita ganti. Karena yang ingin kita ganti hanya huruf “a” saja berarti kita isi dengan 1.
- New_text : Teks baru yang ingin kita gunakan untuk mengganti teks lama, bisa berupa angka atau huruf. Dalam contoh di atas kita menggunakan huruf “u” sebagai teks baru supaya kata “Buka” bisa berganti menjadi “Buku”.
- Contoh selanjutnya kita ingin mengganti kalimat “Saya membaca buku” menjadi “Saya menulis buku”.
Berikut penjelasannya sesuai sistematika penulisannya:- Text : Dapat diisi dengan teks secara langsung atau alamat sel. Dalam contoh di atas menggunakan alamat sel A3.
- Position : Posisi atau urutan dari huruf yang ingin diganti. Dalam hal ini kata “membaca” yang ingin kita ganti berada diurutan ke 6 dengan spasi terhitung satu karakter.
- Length : Panjang karakter yang ingin kita ganti. Karena yang ingin kita ganti kata “membaca” berarti kita isi dengan 7.
- New_text : Teks baru yang ingin kita gunakan untuk mengganti teks lama, bisa berupa angka, huruf, dan kalimat. Dalam contoh di atas kita menggunakan kata “menulis” sebagai teks baru supaya kata “membaca” bisa berganti menjadi “menulis”.
- Kemudian kita ingin mengganti kalimat “Saya123” menjadi “Aku123”.
Berikut penjelasannya sesuai sistematika penulisannya:- Text : Dapat diisi dengan teks secara langsung atau alamat sel. Dalam contoh di atas menggunakan alamat sel A4.
- Position : Posisi atau urutan dari huruf yang ingin diganti. Dalam hal ini dari kata “Saya123” hanya diganti bagian “Saya” saja. Sehingga urutan karakternya diisi dengan 1.
- Length : Panjang karakter yang ingin kita ganti. Karena yang ingin kita ganti kata “Saya” berarti kita isi dengan 4.
- New_text : Teks baru yang ingin kita gunakan untuk mengganti teks lama, bisa berupa angka, huruf, dan kalimat. Dalam contoh di atas kita menggunakan kata “Aku”
Sehingga hasil akhirnya sebagai berikut:
Unique
Unique merupakan sebuah fungsi yang dapat kita gunakan untuk mencari data yang unik dari sebuah data yang mungkin saja ada yang sama. Sistematika penulisannya sebagai berikut:
- =UNIQUE(range)
Contoh kasus:
Bagaimanakah jika kita ingin mengambil data yang unik saja dari data di atas?
Jawab:
Kita dapat menggunakan fungsi UNIQUE seperti di bawah ini:
Secara otomatis setelah fungsi dijalankan hasilnya akan memunculkan daftar unik dari nama barang. Unik dalam artian tidak ada data yang sama seperti gambar di bawah ini:
IF
Fungsi IF digunakan untuk menentukan data Anda dengan hasil tertentu berdasarkan kondisi logika yang diberikan. Penggunaannya sebagai berikut:
- =IF(Sel yang ingin diuji, [nilai jika benar], [nilai jika salah])
Contoh:
- = IF (E4>=E3, “Ya”,”Salah”)
Berdasarkan contoh di atas, kita akan memeriksa apakah nilai pada sel E4 lebih besar sama dengan sel E3. Jika logikanya benar, biarkan nilai sel tempat fungsi tersebut menjadi bernilai “Ya”, jika tidak maka bernilai “Salah”.
IF Sederhana
Untuk menerapkan fungsi IF dalam contoh kasus, kita modifikasi contoh tabel Penjualan data seperti berikut:
Contoh kasus:
Kita ingin mengetahui status penjualan setiap barang: sudah bagus atau perlu ditingkatkan. Parameter untuk menentukan status tersebut adalah jika barang yang terjual lebih dari 75 unit maka masuk kategori penjualan bagus. Namun jika kurang dari 75 unit yang terjual maka statusnya perlu ditingkatkan. Bagaimana cara mengetahuinya?
Pada alamat sel D3 masukkan rumus =IF(C3>75,"Bagus","Perlu ditingkatkan"). Fungsi tersebut memeriksa sel C3 yang berisi nilai 300 - apakah lebih besar dari angka standar yang ditentukan yaitu 75. Jika ya, maka masuk ke kategori bagus, kalau lebih kecil masuk ke kondisi perlu ditingkatkan.
IF Bertingkat
Terdapat 5 nilai siswa dalam sebuah ulangan matematika. Bagaima caranya jika kita ingin mengetahui nilai huruf dari kelima siswa tersebut?
Jawab:
Kita dapat menggunakan IF bertingkat pada alamat sel E3 seperti berikut:
- =IF(D3>=90,"A",IF(D3>=80,"B",IF(D3>=70,"C",IF(D3>=60,"D","E"))))
Pada tabel di atas terdapat empat logika IF. IF pertama digunakan jika nilai lebih dari sama dengan 90 mendapat A. Disambung dengan IF kedua jika nilai lebih dari sama dengan 80 mendapat B. Selanjutnya jika nilai lebih besar sama dengan 70 mendapat nilai C. Terakhir jika nilai lebih dari sama dengan 60 maka mendapat nilai D. Jika tidak memenuhi kondisi di atas berarti otomatis mendapat nilai E karena di bawah 60. Jangan lupa tutup kurung sebanyak jumlah IF pada akhir fungsi. Sehingga seluruh hasilnya sebagai berikut:
