logo_kurkom

Fungsi Spreadsheet Dasar

Details
Written by: Wawan Gunawan
Category: Perkantoran Dasar
Hits: 192

Spreadsheet adalah aplikasi perkantoran kategori pengolah angka yang dapat digunakan untuk mengolah data sehingga diperoleh informasi. Fungsi spreadsheet yang dipelajari meliputi SUM, SUMIF, SUMIFS, IFERROR, COUNT, COUNTA, COUNTIF, COUNTIFS, VLOOKUP, HLOOKUP, LEFT, MID, RIGHT, ROUND, INT, SUMPRODUCT, INDEX, MATCH, AVERAGE, STDEV, MEDIAN, MODE, MIN, MAX.

Untuk dapat berlatih materi fungsi spreadsheet dasar, download terlebih dahulu file latihan yang terdapat di bagian bawah halaman ini.

01. SUM, SUMIF

SUM adalah fungsi untuk menjumlahkan bilangan atau angka, sedangkan SUMIF adalah fungsi untuk menjumlahkan bilangan berdasarkan kriteria atau kategori tertentu. Untuk latihan fungsi ini, buka file dasar01.ods sehingga muncul tampilan seperti pada gambar di bawah ini.

Untuk mengisi sel kosong L3, L20, L24, L25, L26, L27, ikuti langkah-langkah berikut.

  1. Pada sel L3, ketik rumus =SUM(E3:K3), tekan <Enter>, salin rumus itu ke bawah sampai L19.
  2. Pada sel L20, ketik rumus =SUM(L3:L19), tekan <Enter>.
  3. Pada sel L24, ketik rumus =SUMIF(C3:C19;"K";L3:L19), tekan <Enter>.
  4. Pada sel L25, ketik rumus =SUMIF(C3:C19;"M";L3:L19), tekan <Enter>.
  5. Pada sel L26, ketik rumus =SUMIF(C3:C19;"T";L3:L19), tekan <Enter>.
  6. Pada sel L27, ketik rumus =SUM(L24:L26), tekan <Enter>.
02. SUMIFS, IFERROR

SUMIFS adalah fungsi yang digunakan untuk menjumlahkan bilangan berdasarkan dua kriteria atau lebih, sedangkan IFERROR adalah fungsi yang digunakan untuk menangani kesalahan jika syarat penggunaan rumus tidak terpenuhi. Untuk latihan fungsi ini, buka file dasar02.ods sehingga muncul tampilan seperti pada gambar di bawah ini.

  1. Pada sel D3, ketik rumus =SUMIFS(sks; npm; B3; semester; "2014.1"), tekan <Enter>, salin rumus itu ke bawah.
  2. Pada sel E3, ketik rumus =SUMIFS(nm; npm; B3; semester; "2014.1"), tekan <Enter>, salin rumus itu ke bawah.
  3. Pada sel F3, ketik rumus =IFERROR(E3/D3;""), tekan <Enter>, salin rumus itu ke bawah.
  4. Pada sel G3, ketik rumus =SUMIFS(sks; npm; B3; semester; "2014.2"), tekan <Enter>, salin rumus itu ke bawah.
  5. Pada sel H3, ketik rumus =SUMIFS(nm; npm; B3; semester; "2014.2"), tekan <Enter>, salin rumus itu ke bawah.
  6. Pada sel I3, ketik rumus =IFERROR(H3/G3;""), tekan <Enter>, salin rumus itu ke bawah.
03. COUNT, COUNTA, COUNTIF

COUNT adalah fungsi untuk menghitung jumlah sel yang berisi bilangan atau angka, COUNTA adalah fungsi untuk menghitung jumlah sel yang berisi bilangan atau angka, huruf atau teks, atau karakter lainnya, dan COUNTIF adalah fungsi untuk menghitung jumlah sel yang berisi kriteria tertentu. Untuk latihan fungsi ini, buka file dasar03.ods sehingga muncul tampilan seperti pada gambar di bawah ini.

  1. Pada sel W1, ketik rumus =COUNT(A4:A29), tekan <Enter>.
  2. Pada sel S4, ketik rumus =COUNTIF(C4:R4;"."), tekan <Enter>, salin rumus itu ke bawah.
  3. Pada sel T4, ketik rumus =COUNTIF(C4:R4;"i"), tekan <Enter>, salin rumus itu ke bawah.
  4. Pada sel U4, ketik rumus =COUNTIF(C4:R4;"s"), tekan <Enter>, salin rumus itu ke bawah.
  5. Pada sel V4, ketik rumus =COUNTIF(C4:R4;"a"), tekan <Enter>, salin rumus itu ke bawah.
  6. Pada sel W4, ketik rumus =S4/COUNTA(C4:R4), tekan <Enter>.
  7. Klik sel W4, klik Format, klik Cells, klik Percent, pilih Decimal Places: 0, klik OK, salin rumus itu ke bawah.
04. COUNTIFS

COUNTIFS adalah fungsi untuk menghitung jumlah sel yang berisi 2 kriteria tertentu atau lebih. Untuk latihan fungsi ini, buka file dasar04.ods sehingga muncul tampilan seperti pada gambar di bawah ini.

  1. Pada sel L2, ketik rumus =COUNTIFS(jeniskelamin;"Laki-laki";status;"Lajang";tempatlahir;"Indramayu";umur;">20"), tekan <Enter>.
  2. Pada sel L3, ketik rumus =COUNTIFS(jeniskelamin;"Laki-laki";status;"Lajang";tempatlahir;"Indramayu";umur;"<=20"), tekan <Enter>.
  3. Pada sel L4, ketik rumus =COUNTIFS(jeniskelamin;"Laki-laki";status;"Lajang";tempatlahir;"<>Indramayu";umur;">20"), tekan <Enter>.
  4. Pada sel L5, ketik rumus =COUNTIFS(jeniskelamin;"Laki-laki";status;"Lajang";tempatlahir;"<>Indramayu";umur;"<=20"), tekan <Enter>.
  5. Pada sel L6, ketik rumus =COUNTIFS(jeniskelamin;"Laki-laki";status;"Menikah";tempatlahir;"Indramayu";umur;">20"), tekan <Enter>.
  6. Pada sel L7, ketik rumus =COUNTIFS(jeniskelamin;"Laki-laki";status;"Menikah";tempatlahir;"Indramayu";umur;"<=20"), tekan <Enter>.
  7. Pada sel L8, ketik rumus =COUNTIFS(jeniskelamin;"Laki-laki";status;"Menikah";tempatlahir;"<>Indramayu";umur;">20"), tekan <Enter>.
  8. Pada sel L9, ketik rumus =COUNTIFS(jeniskelamin;"Laki-laki";status;"Menikah";tempatlahir;"<>Indramayu";umur;"<=20"), tekan <Enter>.
  9. Pada sel L10, ketik rumus =COUNTIFS(jeniskelamin;"Perempuan";status;"Lajang";tempatlahir;"Indramayu";umur;">20"), tekan <Enter>.
  10. Pada sel L11, ketik rumus =COUNTIFS(jeniskelamin;"Perempuan";status;"Lajang";tempatlahir;"Indramayu";umur;"<=20"), tekan <Enter>.
  11. Pada sel L12, ketik rumus =COUNTIFS(jeniskelamin;"Perempuan";status;"Lajang";tempatlahir;"<>Indramayu";umur;">20"), tekan <Enter>.
  12. Pada sel L13, ketik rumus =COUNTIFS(jeniskelamin;"Perempuan";status;"Lajang";tempatlahir;"<>Indramayu";umur;"<=20"), tekan <Enter>.
  13. Pada sel L14, ketik rumus =COUNTIFS(jeniskelamin;"Perempuan";status;"Menikah";tempatlahir;"Indramayu";Umur;">20"), tekan <Enter>.
  14. Pada sel L15, ketik rumus =COUNTIFS(jeniskelamin;"Perempuan";status;"Menikah";tempatlahir;"Indramayu";umur;"<=20"), tekan <Enter>.
  15. Pada sel L16, ketik rumus =COUNTIFS(jeniskelamin;"Perempuan";status;"Menikah";tempatlahir;"<>Indramayu";umur;">20"), tekan <Enter>.
  16. Pada sel L17, ketik rumus =COUNTIFS(jeniskelamin;"Perempuan";status;"Menikah";tempatlahir;"<>Indramayu";umur;"<=20"), tekan <Enter>.
  17. Pada sel L18, ketik rumus =SUM(L2:L17), tekan <Enter>.
05. VLOOKUP

VLOOKUP adalah fungsi yang digunakan untuk mencari atau menampilkan informasi dari sebuah basis data berdasarkan kode yang disusun secara vertikal. Untuk latihan fungsi ini, buka file dasar05.ods sehingga muncul tampilan seperti pada gambar di bawah ini.

  1. Pada sel C2, ketik rumus =VLOOKUP(B2;basisdata;2), tekan <Enter>, salin rumus itu ke bawah.
  2. Pada sel D2, ketik rumus =VLOOKUP(B2;basisdata;3), tekan <Enter>, salin rumus itu ke bawah.
  3. Pada sel E2, ketik rumus =VLOOKUP(B2;basisdata;4), tekan <Enter>, salin rumus itu ke bawah.
  4. Pada sel G2, ketik rumus =F2*E2, tekan <Enter>, salin rumus itu ke bawah.
06. HLOOKUP

HLOOKUP adalah fungsi yang digunakan untuk mencari atau menampilkan informasi dari sebuah basis data berdasarkan kode yang disusun secara horizontal. Untuk latihan fungsi ini, buka file dasar06.ods sehingga muncul tampilan seperti pada gambar di bawah ini.

  1. Pada sel C2, ketik rumus =HLOOKUP(B2;basisdata;2), tekan <Enter>, salin rumus itu ke bawah.
  2. Pada sel D2, ketik rumus =HLOOKUP(B2;basisdata;3), tekan <Enter>, salin rumus itu ke bawah.
  3. Pada sel E2, ketik rumus =HLOOKUP(B2;basisdata;4), tekan <Enter>, salin rumus itu ke bawah.
  4. Pada sel G2, ketik rumus =F2*E2, tekan <Enter>, salin rumus itu ke bawah.
07. LEFT, MID, RIGHT

LEFT berfungsi untuk menampilkan sejumlah karakter tertentu dari kiri, RIGHT berfungsi untuk menampilkan sejumlah karakter tertentu dari kanan, dan MID berfungsi untuk menampilkan sejumlah karakter tertentu dari kiri, dimulai dari posisi tertentu. Untuk latihan fungsi ini, buka file dasar07.ods sehingga muncul tampilan seperti pada gambar di bawah ini.

  1. Pada sel D2, ketik rumus =VLOOKUP(LEFT(B2;5);prodi;2), tekan <Enter>, salin rumus itu ke bawah.
  2. Pada sel E2, ketik rumus =VLOOKUP(MID(B2;6;2);semester;2), tekan <Enter>, salin rumus itu ke bawah.
  3. Pada sel F2, ketik rumus =VLOOKUP(MID(B2;8;2);tm;2), tekan <Enter>, salin rumus itu ke bawah.
  4. Pada sel G2, ketik rumus =RIGHT(B2;3), tekan <Enter>, salin rumus itu ke bawah.
  5. Pada sel H2, ketik rumus ="Mahasiswa program studi "&D2&" kategori "&E2&" yang masuk pada tahun "&F2&" dengan nomor urut "&G2, tekan <Enter>, salin rumus itu ke bawah.
08. ROUND, INT, SUMPRODUCT

ROUND adalah fungsi yang digunakan untuk menampilkan bilangan hasil pembulatan sesuai dengan kriteria tertentu, INT adalah fungsi yang digunakan untuk menampilkan bilangan bulat terbesar yang nilainya lebih kecil atau sama dengan bilangan tersebut, dan SUMPRODUCT adalah fungsi yang digunakan untuk menampilkan jumlah hasil kali dua baris data atau dua kolom data. Untuk latihan fungsi ini, buka file dasar08.ods sehingga muncul tampilan seperti pada gambar di bawah ini.

  1. Pada sel D3, ketik rumus =ROUND(C3;-3), tekan <Enter>, salin rumus itu ke bawah, sehingga diperoleh tampilan seperti gambar di bawah ini.

    Gaji pegawai dibulatkan ke ribuan terdekat. Apabila 3 digit terakhir dari gaji itu bernilai 500 atau lebih, maka dilakukan pembulatan ke atas, selain itu dilakukan pembulatan ke bawah.
  2. Pada sel E3, ketik rumus =INT(D3/E$2), tekan <Enter>, salin rumus itu ke bawah, sehingga diperoleh tampilan seperti gambar di bawah ini.

    Gaji pegawai yang telah dibulatkan (isi sel D3) kemudian dibagi dengan 100000 (isi sel E2) untuk mendapatkan banyaknya lembar uang pecahan 100000. Penulisan E$2 diperlukan agar isi selnya tetap befungsi sebagai penyebut dalam proses pembagian pada saat rumus itu disalin ke bawah. Selanjutnya digunakan fungsi INT untuk menjamin bahwa proses pembagian tersebut menghasilkan bilangan bulat.
  3. Pada sel F3, ketik rumus =INT(($D3-SUMPRODUCT($E3:E3; $E$2:E$2))/F$2), tekan <Enter>, salin rumus itu ke bawah sampai F13 dan ke kanan sampai K13, sehingga diperoleh tampilan seperti gambar di bawah ini.

    Perhatikan gaji yang diterima oleh Amelia sebesar Rp 2.505.000. Berdasarkan hasil yang diperoleh pada langkah kedua, Amelia akan menerima 25 lembar uang pecahan Rp 100.000, setara dengan Rp 2.500.000, sehingga masih ada kekurangan sebesar Rp 5.000. Kekurangan ini dinyatakan dengan rumus =$D3-SUMPRODUCT($E3:E3;$E$2:E$2). Karena kekurangan ini harus dikonversi dengan lembar uang pecahan yang nilainya harus bulat, rumusnya harus diubah menjadi =INT(($D3-SUMPRODUCT($E3:E3; $E$2:E$2))/F$2), dalam hal ini F$2 adalah nominal lembar uang pecahan.
10. STUDI KASUS

Di bawah ini adalah contoh penerapan spreadsheet dasar berupa studi kasus penjualan mingguan media cetak, yang mencakup item-item penamaan sel, sel semi absolut, operator & (ampersand), fungsi spreadsheet VLOOKUP, SUM, SUMIF, SUMIFS, COUNTIF, COUNTIFS, COUNTA, LEFT, RIGHT.

Untuk memulai latihan ini, download terlebih dahulu file latihan Data Penjualan, kemudian ikuti langkah-langkah berikut ini.

  1. Buka file penjualan.ods hasil download dengan aplikasi LibreOffice Calc sehingga muncul tampilan seperti gambar di bawah ini.
  2. Klik sheet daftar media, pilih sel A2:D67, ketik tabel pada kotak Name Box, tekan <Enter>, sehingga diperoleh tampilan seperti terlihat pada gambar di bawah ini.

    Keterangan: Daftar media cetak dimuat dalam sel A2:D67, terdiri dari 66 baris dan 4 kolom, serta telah diberi nama tabel.
  3. Klik sheet soal untuk menampilkan kembali soal atau data penjualan.
  4. Buat penamaan sel sebagai berikut:
    • Pilih sel E4:E20, ketik kategori pada kotak Name Box, kemudian tekan <Enter>.
    • Pilih sel N4:N20, ketik volume pada kotak Name Box, kemudian tekan <Enter>.
    • Pilih sel O4:O20, ketik nilai pada kotak Name Box, kemudian tekan <Enter>.
  5. Pada sel D4, ketik rumus =VLOOKUP($C4;tabel;2), tekan <Enter>, kemudian salin rumus itu ke kanan, ke dalam sel E4:F4.
  6. Edit rumus di E4, ganti angka 2 dengan angka 3, kemudian tekan <Enter>.
  7. Edit rumus di F4, ganti angka 2 dengan angka 4, kemudian tekan <Enter>.
  8. Klik sel D4, salin rumus yang terdapat di dalam sel D4 ke arah bawah, ke dalam sel D5:D20.
  9. Klik sel E4, salin rumus yang terdapat di dalam sel E4 ke arah bawah, ke dalam sel E5:E20.
  10. Klik sel F4, salin rumus yang terdapat di dalam sel F4 ke arah bawah, ke dalam sel F5:F20.
  11. Pada sel N4, ketik rumus =SUM(G4:M4), tekan <Enter>, kemudian salin rumus itu ke bawah, ke dalam sel N5:N20.
  12. Pada sel O4, ketik rumus =F4*N4, tekan <Enter>, kemudian salin rumus itu ke bawah, ke dalam sel O5:O20.
  13. Pada sel N24, ketik rumus =SUMIF(kategori;$J24;N$4:N$20), tekan <Enter>, kemudian salin rumus itu ke dalam sel N24:O26.
  14. Pada sel S5, ketik rumus =SUMIFS(nilai;kategori;$R5;volume;"<="&RIGHT(S$4;2))-SUMIFS(nilai;kategori;$R5;volume;"<"&LEFT(S$4;2)), tekan <Enter>, kemudian salin rumus itu ke dalam sel S5:U7 seperti terlihat pada gambar di bawah ini.
  15. Pada sel S12, ketik rumus =COUNTIFS(nilai;$R12;volume;"<="&RIGHT(S$11;2))-COUNTIFS(nilai;$R12;volume;"<"&LEFT(S$11;2)), tekan <Enter>, kemudian salin rumus itu ke arah bawah, ke dalam sel S12:U13 seperti terlihat pada gambar di bawah ini.
  16. Pada sel S18, ketik rumus =COUNTIF(kategori;R18), tekan <Enter>, kemudian salin rumus itu ke arah bawah, ke dalam sel S19:S20.
  17. Pada sel T18, ketik rumus =S18/COUNTA(kategori), tekan <Enter>, kemudian salin rumus itu ke dalam sel T19:T20
Download
  1. SUM, SUMIF
  2. SUMIFS, IFERROR
  3. COUNT, COUNTA, COUNTIF
  4. COUNTIFS
  5. VLOOKUP
  6. HLOOKUP
  7. LEFT, MID, RIGHT
  8. ROUND, INT, SUMPRODUCT
  9. Data Penjualan
  10. LibreOffice for Windows
Ujian Praktek
  1. Submateri: Fungsi Spreadsheet Dasar
  2. Soal ujian: Data Penjualan (langkah 01-13, langkah 04b dan 04c tidak perlu dilakukan)
  3. Durasi ujian: 180 detik (3 menit)
  4. Sifat ujian: Catatan tertutup
  5. Kategori ujian: Kemampuan mengingat-memahami
  6. Penilaian:
    • waktu penyelesaian 120 detik atau kurang, nilai 100
    • waktu penyelesaian 121-135 detik, nilai 95
    • waktu penyelesaian 136-150 detik, nilai 90
    • waktu penyelesaian 151-165 detik, nilai 85
    • waktu penyelesaian 166-180 detik, nilai 80
    • waktu penyelesaian lebih dari 180 detik, tidak lulus, mengulang
  7. Jadwal ujian: Setiap hari kerja, Senin-Sabtu
    • Pukul 10.00-11.30
    • Pukul 13.00-14.30
    • Pukul 15.30-17.00
  8. Tempat ujian: Sekretariat Pusat Komputer Universitas Wiralodra
Hasil Ujian
No. Nama Mahasiswa NPM Program Studi Tahun
Masuk		 Waktu
(detik)		 Nilai Tanggal Lulus
1 SYEKHIKULL AMRI 612010120050 Manajemen 2020 143 90 24 Februari 2024
2 DETTY SARISKA KUSUMA 132010120038 Kesehatan Masyarakat 2020 163 85 21 Maret 2024
3 MEDIANA AZIZAH 132010120046 Kesehatan Masyarakat 2020 180 80 25 Maret 2024

Perkantoran Dasar

Ujian Tektung

Ujian Grafik

000664
Today: 3
This Week: 9
This Month: 36