10 Fungsi Penting Microsoft Excel untuk Analisis Data

1. MENGGABUNGKAN

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

Pada 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 tertentu. 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 saja yang akrab dengan analisis data. Anda dapat 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 menyetel rentang ke FALSE, rentang tersebut akan mencari kecocokan persis, tetapi jika Anda menyetelnya ke TRUE, rentang tersebut akan mencari kecocokan perkiraan.

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

Satu-satunya kelemahan menggunakan fungsi ini adalah hanya berfungsi dengan data yang telah disusun ke dalam kolom, oleh karena itu namanya — pencarian vertikal. Jadi, jika Anda telah mengatur data dalam baris, Anda harus terlebih dahulu 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, bila 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 larik dimensi tunggal. Itu mengembalikan koordinat data sebagai angka.

Pada contoh di atas, saya ingin mencari jumlah penayangan 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 rumusnya kembalikan, "Jan" adalah nilai yang ingin saya cocokkan, A2:A13 adalah kolom di mana rumus akan menemukan "Jan" dan 0 menandakan bahwa saya ingin rumus menemukan kecocokan yang tepat untuk nilai.

Jika Anda ingin menemukan perkiraan kecocokan, Anda harus mengganti 0 dengan 1 atau -1. Sehingga 1 akan mencari nilai terbesar yang lebih kecil atau sama dengan nilai pencarian dan -1 akan mencari nilai terkecil yang lebih kecil atau sama dengan nilai pencarian. Perhatikan bahwa jika Anda tidak menggunakan 0, 1, atau -1, rumusnya akan menggunakan 1, by.

Sekarang jika Anda tidak ingin 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. MINIF/MAXIFS

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

Dalam contoh di atas, saya ingin mencari nilai minimum berdasarkan jenis kelamin siswa. Untuk itu, saya menggunakan rumus =MINIFS (C2:C10, B2:B10, "M") dan saya mendapatkan hasil 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.

Rumus untuk MINIFS: =MINIF(rentang_min, rentang_kriteria1, kriteria1,...)

Rumus untuk MAXIFS: =MAXIF(rentang_maks, rentang_kriteria1, kriteria1,...)

6. RATA-RATA

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

Dalam contoh ini, saya ingin mencari 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: = RATA-RATA(rata-rata_rentang, kriteria_rentang1, kriteria1,...)

7. COUNTIFS

Sekarang jika Anda ingin menghitung jumlah contoh kumpulan data yang memenuhi kriteria tertentu, Anda harus menggunakan fungsi =COUNTIFS. Fungsi ini memungkinkan Anda untuk menambahkan kriteria tak terbatas ke kueri Anda, dan dengan demikian menjadikannya cara termudah untuk menemukan hitungan berdasarkan kriteria input.

Dalam contoh ini, saya ingin mencari jumlah siswa laki-laki atau perempuan yang mendapat nilai kelulusan (yaitu >=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 rentang di mana rumus akan mencari kriteria kedua (tanda), dan ">=40" adalah yang kedua kriteria.

Rumus: =COUNTIF(kriteria_rentang1, kriteria1,...)

8. SUMPRODUCT

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

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

Rumus: =SUMPRODUK(array1, [array2], [array3],...)

9. MEMANGKAS

Fungsi =TRIM sangat berguna saat 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 mendapatkan hasil yang akurat saat menggunakan fungsi lain.

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

Rumusnya hanya menghilangkan spasi ekstra dan memberikan hasil Mouse pad dengan satu spasi.

Rumus: =TRIM(teks)

10. TEMUKAN / CARI

Pembulatan adalah fungsi FIND/SEARCH yang akan membantu Anda mengisolasi teks tertentu dalam kumpulan data. Kedua fungsi tersebut sangat mirip dalam apa yang mereka lakukan, kecuali 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 mencari berapa kali 'Gui' muncul dalam Guiding Tech yang saya gunakan rumusnya =FIND(A2, B2), yang memberikan hasil 1. Sekarang jika saya ingin menemukan berapa kali 'gui' muncul di dalam Guiding Tech, saya harus menggunakan rumus =SEARCH karena tidak peka huruf besar/kecil.

Rumus untuk Menemukan: = TEMUKAN (temukan_teks, dalam_teks, [jumlah_mulai])

Rumus untuk Pencarian: = CARI(temukan_teks, dalam_teks, [jumlah_mulai])