Friday, 23 June 2017

Rumus & Fungsi Excel: Tanggal, Waktu, String, VLOOKUP di Excel

Rumus dan fungsi adalah blok bangunan bekerja dengan data numerik di Excel. Artikel ini mengenalkan Anda pada formula dan fungsi.
Pada artikel ini, kami akan membahas topik berikut.
  • Apa itu formula
  • Kesalahan yang harus dihindari saat bekerja dengan formula di Excel
  • Apa fungsinya? (Function Wizard)
  • Pentingnya fungsi
  • Fungsi umum
  • Fungsi numerik
  • Fungsi string
  • Fungsi waktu tanggal
  • Fungsi V Lookup

Tutorial Data

Untuk tutorial ini, kita akan bekerja dengan dataset berikut.
Anggaran persediaan rumah
S / N BARANG QTY HARGA SUBTOTAL Apakah itu terjangkau
1 Mangga 9 600

2 Jeruk 3 1200

3 Tomat 1 2500

4 Minyak goreng 5 6500

5 Air tonik 13 3900

Jadwal Proyek Gedung Rumah Tangga
S / N BARANG MULAI TANGGAL AKHIR TANGGAL DURATION (DAYS)
1 Lahan survei 04/02/2015 07/02/2015
2 Lay Foundation 10/02/2015 15/02/2015
3 Atap 27/02/2015 03/03/2015
4 Lukisan 09/03/2015 21/03/2015

Apa itu formula

Ini adalah bentuk yang paling sederhana, formula adalah ungkapan yang terdiri dari alamat sel dan operator aritmatika. Rumus juga bisa terdiri dari nilai diskrit yaitu = 6 * 3. Excel mengevaluasi formula ke sebuah nilai. Contoh dari formula terlihat sebagai berikut.
= A2 * D2 / 2
SINI,
  • "=" Memberitahu Excel bahwa ini adalah formula, dan harus dievaluasi.
  • "A2" * D2 " membuat referensi ke alamat sel A2 dan D2 kemudian mengalikan nilai yang ditemukan di alamat sel ini.
  • "/" Adalah divisi operator aritmatika
  • "2" adalah nilai diskrit

Rumus latihan praktis

Kami akan bekerja dengan data sampel untuk anggaran rumah untuk menghitung subtotal.
  • Buat buku kerja baru di Excel
  • Masukkan data yang ditunjukkan pada anggaran persediaan rumah di atas.
  • Lembar kerja Anda akan terlihat seperti berikut.
Introduction to formulas and functions in Excel
Kita sekarang akan menulis rumus yang menghitung subtotal
Atur fokus ke sel E4
Masukkan rumus berikut.
= C4 * D4
SINI,
  • "C4 * D4" menggunakan penggandaan operator aritmatika (*) untuk mengalikan nilai alamat sel C4 dan D4.
Tekan tombol enter
Anda akan mendapatkan hasil sebagai berikut
Introduction to formulas and functions in Excel
Gambar animasi berikut menunjukkan cara untuk memilih alamat sel secara otomatis dan menerapkan formula yang sama ke baris lainnya.
Introduction to formulas and functions in Excel

Kesalahan yang harus dihindari saat bekerja dengan formula di Excel

  1. Ingatlah aturan kurung dari Multiplication Multiplication Addition & Subtraction (BODMAS). Ini berarti ungkapan adalah kurung yang dievaluasi terlebih dahulu. Untuk operator aritmetika, pembagian dievaluasi terlebih dahulu diikuti dengan perkalian maka penambahan dan pengurangannya adalah yang terakhir untuk dievaluasi. Dengan menggunakan aturan ini, kita dapat menulis ulang rumus di atas sebagai = (A2 * D2) / 2. Ini akan memastikan bahwa A2 dan D2 dievaluasi terlebih dahulu kemudian dibagi dua.
  2. Rumus biasanya bekerja dengan data numerik; Anda bisa memanfaatkan validasi data untuk menentukan jenis data yang harus diterima oleh sel yaitu angka saja.
  3. Untuk memastikan bahwa Anda bekerja dengan alamat sel yang benar yang dirujuk dalam formula, Anda dapat menekan F2 pada keyboard. Ini akan menyoroti alamat sel yang digunakan dalam formula, dan Anda dapat memeriksa silang untuk memastikan alamat sel yang diinginkan.
  4. Bila Anda bekerja dengan banyak baris, Anda dapat menggunakan nomor seri untuk semua baris dan mencatat jumlah di bagian bawah lembaran. Anda harus membandingkan jumlah nomor seri dengan total catatan untuk memastikan bahwa formula Anda mencakup semua baris.

Apa fungsinya? (Function Wizard)

Fungsi adalah formula yang telah ditentukan. Fungsi memungkinkan Anda menggunakan nama deskriptif untuk menerapkan formula secara otomatis untuk Anda. Contoh fungsi meliputi;
  • SUM untuk penjumlahan dari sejumlah angka
  • RATA - RATA untuk perhitungan rata-rata diberi range angka
  • COUNT untuk menghitung jumlah item dalam kisaran tertentu

Pentingnya fungsi

Fungsi meningkatkan produktivitas pengguna saat bekerja dengan excel . Misalkan Anda ingin mendapatkan jumlah keseluruhan untuk anggaran perlengkapan rumah di atas. Agar lebih mudah, Anda bisa menggunakan formula untuk mendapatkan jumlah keseluruhan. Dengan menggunakan formula, Anda harus merujuk sel E4 ke E8 satu per satu. Anda harus menggunakan rumus berikut.
= E4 + E5 + E6 + E7 + E8
Dengan sebuah fungsi, Anda akan menulis rumus di atas sebagai
= SUM (E4: E8)
Seperti yang dapat Anda lihat dari fungsi di atas yang digunakan untuk mendapatkan jumlah rentang sel, jauh lebih efisien menggunakan fungsi untuk mendapatkan jumlah daripada menggunakan formula yang harus banyak referensi sel.

Fungsi umum

Mari kita lihat beberapa fungsi yang paling umum digunakan di Excel. Kita akan mulai dengan fungsi statistik.
S / N FUNGSI KATEGORI DESKRIPSI PEMAKAIAN
01 JUMLAH Matematika & Trigger Menambahkan semua nilai dalam kisaran sel = SUM (E4: E8)
02 MIN Statistik Menemukan nilai minimum dalam kisaran sel = MIN (E4: E8)
03 MAX Statistik Menemukan nilai maksimum dalam kisaran sel = MAX (E4: E8)
04 RATA-RATA Statistik Menghitung nilai rata-rata dalam kisaran sel = RATA-RATA (E4: E8)
05 MENGHITUNG Statistik Menghitung jumlah sel dalam kisaran sel = COUNTS (E4: E8)
06 LEN Teks Mengembalikan jumlah karakter dalam teks string = LEN (B7)
07 SUMIF Matematika & Trigger Menambahkan semua nilai dalam kisaran sel yang memenuhi kriteria tertentu = JIKA (E8 <10000, "Ya", "Tidak")
08 AVERAGEIF Statistik Menghitung nilai rata-rata dalam kisaran sel yang memenuhi kriteria yang ditentukan = AVERAGEIF (F4: F8, "Ya", E4: E8)
09 HARI Tanggal Waktu Mengembalikan jumlah hari antara dua tanggal = DAYS (D4, C4)
10 SEKARANG Tanggal Waktu Mengembalikan tanggal dan waktu sistem saat ini = SEKARANG ()

Fungsi numerik

Seperti namanya, fungsi ini beroperasi pada data numerik. Tabel berikut menunjukkan beberapa fungsi numerik yang umum.
S / N FUNGSI KATEGORI DESKRIPSI PEMAKAIAN
1 ISNUMBER Informasi Mengembalikan nilai true jika nilai yang diberikan bernilai numerik dan false jika tidak numerik = ISNUMBER (A3)
2 RAND Matematika & Trigger Menghasilkan bilangan acak antara 0 dan 1 = RAND ()
3 BULAT Matematika & Trigger Membulatkan angka desimal ke jumlah desimal yang ditentukan = ROUND (3.14455,2)
4 MEDIA Statistik Mengembalikan nomor di tengah himpunan nomor yang diberikan = MEDIAN (3,4,5,2,5)
5 PI Matematika & Trigger Mengembalikan nilai jika Pi = PI ()
6 KEKUASAAN Matematika & Trigger Mengembalikan hasil angka yang dinaikkan ke sebuah kekuatan = POWER (2,4)
7 MOD Matematika & Trigger Mengembalikan sisa saat Anda membagi dua nomor = MOD (10,3)
8 ROMA Matematika & Trigger Mengubah angka menjadi angka romawi = ROMAN (1984)

Fungsi string

Fungsi ini digunakan untuk memanipulasi data teks. Tabel berikut menunjukkan beberapa fungsi string yang umum.
S / N FUNGSI KATEGORI DESKRIPSI PEMAKAIAN
1 KIRI Teks Mengembalikan sejumlah karakter yang ditentukan dari awal (sisi kiri) dari sebuah string = KIRI ("GURU99", 4)
2 KANAN Teks Mengembalikan sejumlah karakter yang ditentukan dari akhir (sisi kanan) dari sebuah string = KANAN ("GURU99", 2)
3 PERTENGAHAN Teks Mengambil sejumlah karakter dari tengah string dari posisi awal dan panjang yang ditentukan = MID ("GURU99", 2,3)
4 ISTEXT Informasi Mengembalikan nilai true jika parameter yang diberikan adalah = ISTEXT (nilai)
5 MENEMUKAN Teks Mengembalikan posisi awal string teks ke dalam string teks yang lain. Fungsi ini case-sensitive. = TEMUKAN ("oo", "Roofing", 1)
6 MENGGANTIKAN Teks Mengganti bagian dari string dengan string lain yang ditentukan = REPLACE ("Roofing", 2,2, "xx")

Fungsi Tanggal Waktu

Fungsi ini digunakan untuk memanipulasi nilai tanggal. Tabel berikut menunjukkan beberapa fungsi tanggal yang umum
S / N FUNGSI KATEGORI DESKRIPSI PEMAKAIAN
1 TANGGAL Tanggal Waktu Mengembalikan nomor yang mewakili tanggal dalam kode excel = DATE (2015,2,4)
2 HARI Tanggal Waktu Temukan jumlah hari di antara dua tanggal = DAYS (D6, C6)
3 BULAN Tanggal Waktu Mengembalikan bulan dari tanggal = BULAN ("4/2/2015")
4 MENIT Tanggal Waktu Mengembalikan notulen dari nilai waktu = MINUTE ("12:31")
5 TAHUN Tanggal Waktu Mengembalikan tahun dari tanggal = YEAR ("04/02/2015")

Fungsi VLOOKUP

Fungsi VLOOKUP digunakan untuk menampilkan vertikal di kolom paling kiri dan mengembalikan nilai pada baris yang sama dari kolom yang Anda tentukan. Mari kita jelaskan ini dalam bahasa awam. Anggaran persediaan rumah memiliki kolom nomor seri yang secara unik mengidentifikasi setiap item dalam anggaran. Misalkan Anda memiliki nomor seri item, dan Anda ingin mengetahui deskripsi item, Anda dapat menggunakan fungsi VLOOKUP. Berikut adalah bagaimana fungsi VLOOKUP akan bekerja.
Introduction to formulas and functions in Excel
= VLOOKUP (C12, A4: B8, 2, FALSE)
SINI,
  • "= VLOOKUP" memanggil fungsi pencarian vertikal
  • "C12" menentukan nilai yang akan dicari di kolom paling kiri
  • "A4: B8" menentukan susunan tabel dengan data
  • "2" menentukan nomor kolom dengan nilai baris yang akan dikembalikan oleh fungsi VLOOKUP
  • "FALSE," memberitahu fungsi VLOOKUP bahwa kami mencari kecocokan yang tepat dengan nilai pencarian yang diberikan
Gambar animasi di bawah menunjukkan tindakan ini
Introduction to formulas and functions in Excel
 

Ringkasan

Excel memungkinkan Anda memanipulasi data menggunakan rumus dan / atau fungsi. Fungsi umumnya lebih produktif dibanding formula penulisan. Fungsi juga lebih akurat dibandingkan formula karena margin kesalahan membuat sangat minim.


EmoticonEmoticon