“ittaqunnar walau bisyiqqo tamrotin: Jagalah diri kalian dari api neraka, meski hanya dengan bersedekah sepotong kurma”(Hadits Shahih, Riwayat Bukhari dan Muslim. Lihat Shahiihul jaami’ no. 114)

Tuesday, November 2, 2021

Rumus Microsoft Office



Assalamualaikum Warrahmatullahi Wabarakatuhu,
Hai sobat blogger, kali ini cacatan saya ingin berbagi mengenai rumus atau formula yang biasa digunakan saat kita mengerjakan tugas menggunakan Microsoft Office. Rumus yang saya tulis disini, rumus yang biasa kita gunakan agar bisa membantu pekerjaan kita.


1. Membuat pangkat pada Word dan Powerpoint
Contoh: Lima pangkat n (5n)
Cara:
  • Tulis 5n
  • Blok huruf n
  • Tekan tombol Shift dan Ctrl bersamaan lalu tekan tombol =
Apabila kita ingin menulis pada blog, caranya tulis dulu di Word atau PPT lalu copy paste ke blog.


2. Menghitung banyak huruf/text atau angka pada Excel

Contoh 1 Menghitung banyak angka dan huruf/text
Cara:
  • Siapkan data yang mau dihitung contohnya menghitung berapa banyak angka 3 dan angka yang lebih besar dari 2, dari baris data diatas.
  • Letakkan hasil perhitungan pada cell dibawahnya:
  • Tulis rumus: =Countif(range,"3") dan =Countif(range,">2")
  • Untuk menghitung jumlah huruf atau text rumusnya: =Countif(range,"Salah") dan =Countif(range,"Benar")
  • Pada range kita blok semua baris data yang akan kita cari.

Contoh 2 Menghitung banyak karakter tanggal aktifitas "*" tanpa menghitung karakter "N/A"


Cara:
  • Hitung banyak karakter tanggal dengan perintah =COUNTIF(range,"*") tanda bintang diapit kutip 2 atau kutip 1 untuk menghitung karakter tanggal
  • Hitung banyak karakter "N/A" dengan perintah =COUNTIF(range,"N/A") tanda N/A harus diapit kutip 2 atau kutip 1
  • Lakukan pengurangan hasil kedua penjumlahan tersebut.

Contoh 3 Menghitung banyak angka atau huruf yang ada

Cara:
Menghitung banyaknya peserta yang sudah bayar tagihan
Gunakan perintah COUNTA(range), huruf "A" disini maksudnya AVAILABLE jadi huruf atau anggak yang ada pada cell tersebut.


Contoh 4 Menghitung banyak angka atau huruf yang kosong

Cara:
Menghitung banyaknya peserta yang belum bayar tagihan
Gunakan perintah COUNTBLANK(range), huruf "BLANK" disini maksudnya huruf atau anggak yang kosong pada cell tersebut.



3. Menghitung Jumlah angka dalam excel

Contoh 1 Menghitung jumlah angka dari satu kriteria yang kita inginkan


Cara:
Menghitung jumlah harga barang di tiap kota yang diinginkan
Gunakan perintah SUMIF(range lokasi yang diinginkan,"kriteria lokasi yang diinginkan", range angka yang akan di jumlahkan)


Contoh 2 Menghitung jumlah angka dari beberapa kriteria yang kita inginkan

Cara:
Meghitung jumlah harga barang dari jenis handphpne Motorola dan kota yang diinginkan 
Gunakan perintah SUMIFS(range angka yang akan di jumlahkan, range kriteria pertama, "kriteria pertama", range kriteria kedua, "kriteria kedua")



4. Membuat block agar saat copas rumus tidak berubah

Contoh: =$D$3
Cara: 
  • Ketik D3 lalu blok
  • Tekan tombol F4


5. Mencari data dari suatu range referensi serta mencocokannya

LOOKUP = Mencari data yang tersusun pada suatu kolom
Rumusnya =LOOKUP(Sel yang akan dicocokan, sel referensi, sel yang diambil nilainya)

Contoh: Mencari warna sesuai data frekwensi yang kita inginkan
Cara:
  • Buat kolom data input disebelahnya
  • Buat kolom Jawaban disebelah data input
  • Masukkan rumus pada sel Jawab: =LOOKUP(sel Data Input, blok data pada kolom Frekwensi, blok data pada kolom Warna)
  • Coba masukkan Data input 8.44 maka akan keluar hasil pada sel Jawab Putih.

VLOOKUP = Mencari data yang tersusun vertikal keatas atau bawah
Rumusnya =VLOOKUP (Nilai Yang DiCari; Tabel Referensi; Nomor Kolom yang akan ditampilkan pada hasil;  [Range Lookup]). 
Contoh: Mencari nama depan seseorang dengan memasukkan nomor ID


Cara:
  • Buat kolom Data input dibawanya
  • Buat kolom Hasil disebelah Data input
  • Masukan rumus pada sel Hasil: VLOOKUP (sel Data Input, blok semua tabel referensi, 2 artinya kolom ke2 dimulai dari kolom referensi yang di blok, FALSE artinya pencarian tulisan yang sama persis)

HLOOKUP = Mencari data yang tersusun horizontal kesamping kiri atau kanan
Rumusnya =HLOOKUP (Nilai Yang DiCari; Tabel Referensi; Nomor Baris; [Range Lookup])
Contoh: Mencari nilai sparepart




Cara:
  • Buat kolom Data input dibawanya
  • Buat kolom Hasil disebelah Data input
  • Masukan rumus pada sel Hasil: =HLOOKUP (sel Data Input, blok semua tabel referensi, 3 artinya baris ke3 dimulai dari baris teratas kolom referensi yang di blok, FALSE artinya pencarian tulisan yang sama persis)

CHOOSE = Memilih salah satu nilai dari sekumpulan argument
Rumusnya =CHOOSE(nilai yang dicari, data ref1, data ref 2, data ref 3 ...)
Contoh: 

Cara:
  • Buat kolom Data input dibawanya
  • Buat kolom Hasil disebelah Data input
  • Masukan rumus pada sel Hasil: =CHOOSE(sel Data Input, sel data ref1, sel data ref2, sel data ref3)


6. Mengganti huruf besar (Capital) menjadi huruf yang sesuai ejaan

Contoh:

Cara:
  • Sisipkan kolom sementara di samping kolom yang berisi teks yang ingin Anda konversi sebagai sel Hasil.
  • Masukan rumus pada sel Hasil : =PROPER(sel Data)



7. Mengganti huruf besar (Capital) menjadi huruf kecil semua

Contoh:

Cara:
  • Sisipkan kolom sementara di samping kolom yang berisi teks yang ingin Anda konversi sebagai sel Hasil.
  • Masukan rumus pada sel Hasil : =LOWER(sel Data)


8. Mengganti huruf kecil menjadi huruf besar (Capital) semua

Contoh:
Cara:
  • Sisipkan kolom sementara di samping kolom yang berisi teks yang ingin Anda konversi sebagai sel Hasil.
  • Masukan rumus pada sel Hasil : =UPPER(sel Data)


9. Rumus AND, OR dan NOT

Fungsi AND digunakan untuk menyatakan  apabila ada 2 data dimana salah satu data tersebut tidak sesuai target maka kedua data tersebut dinyatakan salah.

Fungsi OR digunakan untuk menyatakan apabila ada 2 data dimana salah satu data tersebut tidak sesuai target maka kedua data tersebut dinyatakan benar.

Berikut tabel logika fungsi AND dan OR:

Contoh:


Fungsi NOT dugunakan untuk membalik logika sebuah data dari TRUE menjadi FALSE atau sebaliknya.

Berikut tabel logika fungsi NOT:
Contoh:



10. Rumus IF

Fungsi IF digunakan untuk membandingkan secara logis antara sesuatu nilai data dengan perkiraan nilai data yang dikehendaki dan kitapun bisa menuliskan hasil perbandingan tersebut jika benar dan jika salah.

Formula logika IF: 
=IF(logica_test, [value_if_true], [value_if false])

Cara memahaminya:
=IF(Jika data A dibandingkan dengan data B, "maka jika benar ditulis", "jika salah ditulis")
Pembandingan data A dan B bisa menggunakan simbol >, <, dan =
Untuk penulisan nilai benar dan salah harus diberi dua tanda kutif (") sebelum dan sesudahnya.

Contoh 1:

Contoh 2:

Contoh 3: (IF-AND, IF-OR)


Contoh 4: Variasi IF
Untuk Resiko (R) standar nilai Factor Number (FN) lebih kecil atau sama dengan 4
Untuk Opportunity (O) standar nilai Factor Number (FN) lebih besar atau sama dengan 4

Jika Severity (S) untuk Resiko (R) di atas 4 maka tulis target objektive Zero Accident
Jika Health (H) untuk Resiko di atas 4 maka tulis target objektive Zero ilness
Jika Environment (E) untuk Resiko di atas 4 maka tulis target objektive Zero Polution
Jika Quality (Q) untuk Resiko di atas 4 maka tulis target objektive Zero Claim

Jika Severity (S) untuk Opportunity (O) di bawah 4 maka tulis target objektive Zero Accident
Jika Health (H) untuk Opportunity (O) di bawah 4 maka tulis target objektive Zero ilness
Jika Environment (E) untuk Opportunity (O) di bawah 4 maka tulis target objektive Zero Polution
Jika Quality (Q) untuk Opportunity (O) di bawah 4 maka tulis target objektive Zero Claim




11. Menghitung jumlah beberapa variabel atau elemen tertentu dalam rentang sel yang diinginkan sesuai dengan beberapa elemen pada kolom lain

Contoh 1: Menghitung jumlah huruf "I", "O", dan "X" di dalam Belt BB


Gunakan fungsi SUMPRODUCT untuk mengalikan seluruh elemen dalam rentang yang telah dihasilkan oleh kondisi di atas dan menghitung jumlah totalnya.

Rumus =SUMPRODUCT((A2:A7="BB")*(B2:D7="x"))

Dengan penjelasan:

A2:A7="BB": Ini adalah kondisi yang memeriksa sel-sel dari A2 sampai A7 apakah nilai-nilainya adalah "BB". Jika benar, hasilnya adalah 1, dan jika salah, hasilnya adalah 0.

B2:D7="x": Ini adalah kondisi yang memeriksa sel-sel dari B2 sampai D7 apakah nilai-nilainya adalah "x". Jika benar, hasilnya adalah 1, dan jika salah, hasilnya adalah 0.

(A2:A7="BB")*(B2:D7="x"): Ini adalah hasil kali dari dua kondisi di atas. Jika kedua kondisi tersebut benar, hasilnya adalah 1, dan jika salah satu atau kedua kondisi salah, hasilnya adalah 0.


Contoh 2: Menghitung jumlah huruf "I", "O", dan "X" di dalam Belt BB dan Bagian SPG

Gunakan fungsi SUMPRODUCT untuk mengalikan seluruh elemen dalam rentang yang telah dihasilkan oleh kondisi di atas dan menghitung jumlah totalnya.

Rumus =SUMPRODUCT((I2:I7="BB")*(I2:I7="SPG")*(K2:M7="I"))




12. Membulatkan bilangan desimal

Contoh 1 Membulatkan bilangan desimal di sampingnya


Cara:
Membulatkan nilai masing-masing peserta
Gunakan perintah ROUND(angka yang diinginkan, jumlah digit pembulatan)


13. Menghapus spasi yang tidak diinginkan

Contoh:
Cara:
Gunakan perintah TRIM(cel tempat kalimat yang spasinya akan diperbaiki)


14. Menghitung jumlah karakter (huruf/text/angka/spasi)

Contoh:
Cara:
Gunakan perintah LEN(cel yang akan kita hitung karakternya)


15. Cara copy paste pada cell yang di hide

Contoh: 

Gambar 1 Nama operator berdasarkan level


Gambar 2 Memfilter untuk level operator dan executive



Gambar 3. Melakukan copy paste tulisan "SPG" pada cell yang terlihat (tanpa mempaste pada cell yang di hide)


Cara:
  • Copy dahulu cell yang ada tulisan "SPG" dengan cara takkan kursor pada cell D2 lalu tekan tombol Ctrl C
  • Blok cell yang akan di paste dari cell D3-D7
  • Tekan tombol Ctrl G akan tampil menu Go To Special, klik menu Special..., pilih Visual cell only, lalu OK
  • Tekan tombol Ctrl V, maka tulisan SPG akan terpaste pada cell yang terlihat saja dan tanpa terpaste pada cell yang di hide.
Hasilnya seperti gambar dibawah ini setelah dilepas filternya





Semoga rumus Excel tersebut bisa membantu sobat blogger semua.

Wassalam,
DK

Diposkan oleh di
Label: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)