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.

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

Gambar animasi berikut menunjukkan cara untuk memilih alamat sel secara otomatis dan menerapkan formula yang sama ke baris lainnya.

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.

= 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

 

Download Kode Excel di atas

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.

Lokasi:

Share :