10 Esensi Fungsi Microsoft Excel untuk Analisis Data

Microsoft Excel menawarkan pengguna ratusan fungsi dan formula yang berbeda untuk berbagai keperluan. Apakah Anda harus menganalisis keuangan pribadi Anda atau kumpulan data besar, itu adalah fungsi yang membuat pekerjaan mudah. Juga, ini menghemat banyak waktu dan upaya. Namun, menemukan fungsi yang tepat untuk kumpulan data Anda bisa sangat rumit.

Jadi jika Anda telah berjuang untuk menemukan fungsi Excel yang sesuai untuk analisis data, maka Anda telah datang ke tempat yang tepat. Berikut adalah daftar beberapa fungsi penting Microsoft Excel yang dapat Anda gunakan untuk analisis data dan Anda dapat meningkatkan produktivitas Anda dalam proses tersebut.

Note: Untuk yang belum tahu, fungsi-fungsi yang disebutkan dalam posting ini perlu ditambahkan di Formula Bar dari spreadsheet Excel yang berisi data atau di dalam sel di mana Anda menginginkan hasilnya.

1. CONCATENATE

= CONCATENATE adalah salah satu fungsi terpenting untuk analisis data karena memungkinkan Anda untuk menggabungkan teks, angka, tanggal, dll. Dari banyak sel menjadi satu. Fungsi ini sangat berguna untuk menggabungkan data dari sel yang berbeda ke dalam sel tunggal. Misalnya, berguna untuk membuat parameter pelacakan untuk kampanye pemasaran, membuat kueri API, menambahkan teks ke format angka, dan beberapa hal lainnya.

Dalam contoh di atas, saya ingin bulan dan penjualan bersama dalam satu kolom. Untuk itu, saya telah menggunakan rumus = CONCATENATE (A2, B2) di sel C2 untuk mendapatkan Jan $ 700 sebagai hasilnya.

Rumus: = CONCATENATE (sel yang ingin Anda gabungkan)

2. LEN

= LEN adalah fungsi lain yang berguna untuk analisis data yang pada dasarnya menampilkan jumlah karakter dalam sel yang diberikan. Fungsi ini sebagian besar dapat digunakan saat membuat tag judul atau deskripsi yang memiliki batas karakter. Ini juga dapat berguna ketika Anda mencoba mencari tahu perbedaan antara pengidentifikasi unik yang berbeda yang seringkali cukup panjang dan tidak dalam urutan yang benar.

Dalam contoh di atas, saya ingin menghitung angka untuk jumlah tampilan yang saya dapatkan setiap bulan. Untuk ini, saya menggunakan rumus = LEN (C2) di sel D2 untuk mendapatkan 5 sebagai hasilnya.

Rumus: = LEN (sel)

3. VLOOKUP

= VLOOKUP mungkin adalah salah satu fungsi yang paling dikenal bagi siapa pun yang akrab dengan analisis data. Anda bisa menggunakannya untuk mencocokkan data dari tabel dengan nilai input. Fungsi ini menawarkan dua mode pencocokan – tepat dan perkiraan, yang dikendalikan oleh rentang pencarian. Jika Anda mengatur rentang ke FALSE, itu akan mencari kecocokan yang tepat, tetapi jika Anda mengaturnya ke TRUE, itu akan mencari perkiraan kecocokan.

Dalam contoh di atas, saya ingin mencari jumlah tampilan di bulan tertentu. Untuk itu, saya menggunakan rumus = VLOOKUP ("Jun", A2: C13, 3) di sel G4 dan saya mendapat 74992 sebagai hasilnya. Di sini, "Jun" adalah nilai pencarian, A2: C13 adalah array tabel di mana saya mencari "Jun" dan 3 adalah jumlah kolom di mana rumus akan menemukan tampilan yang sesuai untuk Juni.

Satu-satunya downside menggunakan fungsi ini adalah hanya berfungsi dengan data yang telah diatur ke dalam kolom, maka nama – pencarian vertikal. Jadi, jika data Anda disusun dalam baris, pertama-tama Anda harus mengubah baris menjadi kolom.

Rumus: = VLOOKUP (lookup_value, table_array, col_index_num, (range_lookup))

4. INDEKS / PERTANDINGAN

Sama seperti fungsi VLOOKUP, fungsi INDEX dan MATCH berguna untuk mencari data tertentu berdasarkan nilai input. INDEX dan MATCH, ketika digunakan bersama-sama, dapat mengatasi keterbatasan VLOOKUP dalam memberikan hasil yang salah (jika Anda tidak hati-hati). Jadi, ketika Anda menggabungkan kedua fungsi ini, mereka dapat menentukan referensi data dan mencari nilai dalam array dimensi tunggal. Itu mengembalikan koordinat data sebagai angka.

Dalam contoh di atas, saya ingin mencari jumlah tampilan di bulan Januari. Untuk itu, saya menggunakan rumus = INDEX (A2: C13, MATCH ("Jan", A2: A13,0), 3). Di sini, A2: C13 adalah kolom data yang saya ingin rumus untuk kembali, "Jan" adalah nilai yang ingin saya cocokkan, A2: A13 adalah kolom di mana rumus akan menemukan "Jan" dan 0 menandakan bahwa saya ingin rumus untuk menemukan kecocokan tepat untuk nilai.

Jika Anda ingin menemukan kecocokan perkiraan Anda harus mengganti 0 dengan 1 atau -1. Sehingga angka 1 akan menemukan nilai terbesar kurang dari atau sama dengan nilai pencarian dan -1 akan menemukan nilai terkecil kurang dari atau sama dengan nilai pencarian. Perhatikan bahwa jika Anda tidak menggunakan 0, 1, atau -1, rumus akan menggunakan 1, oleh.

Sekarang jika Anda tidak ingin meng-hardcode nama bulan, Anda dapat menggantinya dengan nomor sel. Jadi kita bisa mengganti "Jan" dalam rumus yang disebutkan di atas dengan F3 atau A2 untuk mendapatkan hasil yang sama.

Rumus: = INDEX (kolom data yang ingin Anda kembalikan, MATCH (titik data umum yang Anda coba cocokkan, kolom sumber data lain yang memiliki titik data umum, 0))

5. MINIFS / MAXIFS

= MINIFS dan = MAXIFS sangat mirip dengan fungsi = MIN dan = MAX, kecuali untuk fakta bahwa mereka memungkinkan Anda untuk mengambil set nilai minimum / maksimum dan mencocokkannya pada kriteria tertentu juga. Jadi pada dasarnya, fungsi mencari nilai minimum / maksimum dan mencocokkannya dengan kriteria input.

Dalam contoh di atas, saya ingin menemukan skor minimum berdasarkan jenis kelamin siswa. Untuk itu, saya menggunakan rumus = MINIFS (C2: C10, B2: B10, "M") dan saya mendapatkan hasilnya 27. Di sini C2: C10 adalah kolom di mana rumus akan mencari skor, B2: B10 adalah kolom di mana rumus akan mencari kriteria (jenis kelamin), dan "M" adalah kriteria.

Demikian pula, untuk skor maksimum, saya menggunakan rumus = MAXIFS (C2: C10, B2: B10, "M") dan mendapatkan hasil 100.

Formula untuk MINIFS: = MINIFS (min_range, criteria_range1, criteria1, …)

Formula untuk MAXIFS: = MAXIFS (max_range, criteria_range1, criteria1, …)

6. AVERAGEIFS

Fungsi = AVERAGEIFS memungkinkan Anda menemukan rata-rata untuk kumpulan data tertentu berdasarkan satu atau lebih kriteria. Saat menggunakan fungsi ini, Anda harus ingat bahwa setiap kriteria dan rentang rata-rata dapat berbeda. Namun, dalam fungsi = AVERAGEIF, rentang kriteria dan rentang jumlah harus memiliki rentang ukuran yang sama. Perhatikan perbedaan singular dan jamak antara fungsi-fungsi ini? Nah, di situlah Anda perlu berhati-hati.

Dalam contoh ini, saya ingin menemukan skor rata-rata berdasarkan jenis kelamin siswa. Untuk itu saya menggunakan rumus, = AVERAGEIFS (C2: C10, B2: B10, "M") dan mendapatkan 56,8 sebagai hasilnya. Di sini, C2: C10 adalah rentang di mana rumus akan mencari rata-rata, B2: B10 adalah rentang kriteria, dan "M" adalah kriteria.

Rumus: = AVERAGEIFS (average_range, criteria_range1, criteria1, …)

7. COUNTIF

Sekarang jika Anda ingin menghitung jumlah instance set data yang memenuhi kriteria tertentu, Anda harus menggunakan fungsi = COUNTIFS. Fungsi ini memungkinkan Anda untuk menambahkan kriteria tanpa batas ke kueri Anda, dan dengan demikian menjadikannya cara termudah untuk menemukan penghitungan berdasarkan kriteria input.

Dalam contoh ini, saya ingin menemukan jumlah siswa pria atau wanita yang mendapat nilai lulus (mis.> = 40). Untuk itu saya menggunakan rumus = COUNTIFS (B2: B10, "M", C2: C10, "> = 40"). Di sini, B2: B10 adalah rentang di mana rumus akan mencari kriteria pertama (jenis kelamin), "M" adalah kriteria pertama, C2: C10 adalah kisaran di mana rumus akan mencari kriteria kedua (tanda), dan "> = 40" adalah kriteria kedua.

Rumus: = COUNTIFS (criteria_range1, criteria1, …)

8. SUMPRODUCT

Fungsi = SUMPRODUCT membantu Anda mengalikan rentang atau array bersama-sama dan kemudian mengembalikan jumlah produk. Ini fungsi yang cukup serbaguna dan dapat digunakan untuk menghitung dan menjumlahkan array seperti COUNTIFS atau SUMIFS, tetapi dengan tambahan fleksibilitas. Anda juga dapat menggunakan fungsi lain dalam SUMPRODUCT untuk memperluas fungsinya lebih jauh.

Dalam contoh ini, saya ingin mencari jumlah total dari semua produk yang dijual. Untuk itu, saya menggunakan rumus = SUMPRODUCT (B2: B8, C2: C8). Di sini, B2: B8 adalah array pertama (jumlah produk yang dijual) dan C2: C8 adalah array kedua (harga setiap produk). Formula kemudian mengalikan jumlah setiap produk yang dijual dengan harganya dan kemudian menambahkan semuanya untuk menghasilkan total penjualan.

Rumus: = SUMPRODUCT (array1, (array2), (array3), …)

9. TRIM

Fungsi = TRIM sangat berguna ketika Anda bekerja dengan kumpulan data yang memiliki beberapa spasi atau karakter yang tidak diinginkan. Fungsi ini memungkinkan Anda untuk menghapus spasi atau karakter ini dari data Anda dengan mudah, memungkinkan Anda untuk mendapatkan hasil yang akurat saat menggunakan fungsi lain.

Dalam contoh ini, saya ingin menghapus semua ruang ekstra antara kata-kata Mouse dan pad di A7. Untuk itu saya menggunakan rumus = TRIM (A7).

Formula hanya menghapus ruang ekstra dan mengirimkan hasil Mouse pad dengan satu ruang.

Rumus: = TRIM (teks)

10. TEMUKAN / CARI

Pembulatan adalah fungsi FIND / SEARCH yang akan membantu Anda mengisolasi teks tertentu dalam kumpulan data. Kedua fungsi sangat mirip dalam apa yang mereka lakukan, kecuali untuk satu perbedaan utama – fungsi = FIND hanya mengembalikan kecocokan peka huruf besar-kecil. Sementara itu, fungsi = SEARCH tidak memiliki batasan seperti itu. Fungsi-fungsi ini sangat berguna ketika mencari anomali atau pengidentifikasi unik.

Dalam contoh ini, saya ingin menemukan berapa kali 'Gui' muncul dalam Guiding Tech yang saya gunakan rumus = FIND (A2, B2), yang memberikan hasil 1. Sekarang jika saya ingin mencari berapa kali ' gui 'muncul di dalam Guiding Tech sebagai gantinya, saya harus menggunakan rumus = SEARCH karena tidak peka huruf besar-kecil.

Formula untuk Temukan: = FIND (find_text, within_text, (start_num))

Formula untuk Pencarian: = SEARCH (find_text, within_text, (start_num))

Kuasai Analisis Data

Fungsi-fungsi penting Microsoft Excel ini pasti akan membantu Anda dengan analisis data, tetapi daftar ini hanya menggores ujung gunung es. Excel juga mencakup beberapa fungsi lanjutan lainnya untuk mencapai hasil tertentu. Jika Anda tertarik mempelajari lebih lanjut tentang fungsi-fungsi tersebut, beri tahu kami di bagian komentar di bawah.

Selanjutnya: Jika Anda ingin menggunakan Excel lebih efisien, maka Anda harus memeriksa artikel berikutnya untuk beberapa pintasan navigasi Excel yang berguna yang harus Anda ketahui.