10 Fungsi Penting Microsoft Excel untuk Analisis Data:
Microsoft Excel menawarkan kepada pengguna ratusan fungsi dan formula yang berbeda untuk berbagai tujuan. Apakah Anda harus menganalisis keuangan pribadi Anda atau kumpulan data besar apa pun, itu merupakan fungsi yang membuat pekerjaan menjadi mudah.
Selain itu, ini menghemat banyak waktu dan tenaga. Namun, menemukan fungsi yang tepat untuk kumpulan data Anda bisa sangat rumit.
Jadi jika Anda kesulitan menemukan fungsi Excel yang sesuai untuk analisis data, 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 prosesnya.
Catatan: Untuk yang belum tahu, fungsi yang disebutkan dalam posting ini perlu ditambahkan di Bilah Rumus spreadsheet Excel yang berisi data atau di dalam sel tempat Anda menginginkan hasilnya.
1. RANGKAIAN
=CONCATENATE adalah salah satu fungsi paling penting untuk analisis data karena memungkinkan Anda 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, 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 digabungkan dalam satu kolom.
Untuk itu, saya 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 praktis lainnya 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 berguna saat Anda mencoba mencari tahu perbedaan antara pengidentifikasi unik berbeda yang seringkali cukup panjang dan tidak dalam urutan yang benar.
Dalam contoh di atas, saya ingin menghitung jumlah penayangan 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 oleh siapa pun yang terbiasa dengan analisis data.
Anda dapat menggunakannya untuk mencocokkan data dari tabel dengan nilai masukan. Fungsi ini menawarkan dua mode pencocokan — tepat dan perkiraan, yang dikontrol oleh rentang pencarian.
Jika Anda menyetel rentang ke FALSE, ini akan mencari kecocokan persis, tetapi jika Anda menyetelnya ke TRUE, ini akan mencari kecocokan perkiraan.
Pada contoh di atas, saya ingin mencari jumlah penayangan pada bulan tertentu.
Untuk itu, saya menggunakan rumus =VLOOKUP(“Jun”, A2:C13, 3) di sel G4 dan hasilnya 74992. 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 downside menggunakan fungsi ini adalah hanya bekerja dengan data yang telah diatur ke dalam kolom, oleh karena itu namanya — pencarian vertikal. Jadi, jika Anda mengatur data dalam baris, pertama-tama Anda harus mengubah urutan 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 masukan.
INDEX dan MATCH, bila digunakan bersama, dapat mengatasi batasan VLOOKUP dalam memberikan hasil yang salah (jika Anda tidak hati-hati). Jadi saat Anda menggabungkan kedua fungsi ini, keduanya 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 ingin saya kembalikan rumusnya, “Jan” adalah nilai yang ingin saya cocokkan, A2:A13 adalah kolom tempat rumus akan menemukan “Jan” dan 0 menandakan bahwa saya ingin rumus untuk menemukan pencocokan tepat untuk nilai.
Jika Anda ingin menemukan perkiraan yang cocok, Anda harus mengganti 0 dengan 1 atau -1. Sehingga 1 akan mencari nilai terbesar lebih kecil atau sama dengan nilai pencarian dan -1 akan mencari nilai terkecil lebih kecil atau sama dengan nilai pencarian.
Perhatikan bahwa jika Anda tidak menggunakan 0, 1, atau -1, rumus akan menggunakan 1, by.
Sekarang jika Anda tidak ingin meng-hardcode nama bulan, Anda dapat menggantinya dengan nomor sel.
Jadi kita bisa mengganti “Jan” pada rumus diatas 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 kenyataan bahwa mereka 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 masukan.
Pada contoh di atas, saya ingin mencari nilai minimum berdasarkan jenis kelamin siswa. Untuk itu, saya menggunakan rumus =MINIFS (C2:C10, B2:B10, “M”) dan hasilnya 27.
Disini C2:C10 adalah kolom yang akan dicari skornya oleh rumus, B2:B10 adalah kolom di mana rumus akan mencari kriteria (jenis kelamin), dan “M” adalah kriterianya.
Demikian pula, untuk skor maksimum, saya menggunakan rumus =MAXIFS(C2:C10, B2:B10, “M”) dan mendapatkan hasil 100.
Rumus untuk MINIFS: =MINIFS( min_range, criteria_range1, criteria1,… )
Rumus untuk MAXIFS: =MAXIFS( max_range, criteria_range1, criteria1,… )
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, rentang kriteria dan 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 hasilnya 56,8. Di sini, C2:C10 adalah rentang di mana rumus akan mencari rata-rata, B2:B10 adalah rentang kriteria, dan “M” adalah kriteria.
Rumus: =AVERAGEIFS( rata-rata_rentang, kriteria_rentang1, kriteria1,… )
7. COUNTIFS
Sekarang jika Anda ingin menghitung jumlah instance kumpulan 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 hitungan berdasarkan kriteria masukan.
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 kriteria kedua.
Rumus: =COUNTIFS( kriteria_rentang1, kriteria1,… )
8. SUMBER PRODUK
Fungsi =SUMPRODUCT membantu Anda mengalikan rentang atau larik bersama-sama, lalu mengembalikan jumlah hasil kali.
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 di dalam SUMPRODUCT untuk memperluas fungsinya lebih jauh.
Dalam contoh ini, saya ingin mencari jumlah total dari semua produk yang terjual. Untuk itu, saya menggunakan rumus =SUMPRODUCT(B2:B8, C2:C8).
Di sini, B2:B8 adalah larik pertama (jumlah produk yang terjual) dan C2:C8 adalah larik kedua (harga setiap produk). Rumus tersebut kemudian mengalikan jumlah setiap produk yang dijual dengan harganya dan kemudian menjumlahkan semuanya untuk menghasilkan total penjualan.
Rumus: =SUMPRODUCT( array1, [array2], [array3],… )
9. POTONG
Fungsi =TRIM sangat berguna saat Anda bekerja dengan kumpulan data yang memiliki beberapa spasi atau karakter yang tidak diinginkan.
Fungsi ini memungkinkan Anda 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 menghapus ruang 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 fungsinya, kecuali untuk satu perbedaan utama — fungsi =FIND hanya mengembalikan kecocokan peka huruf besar-kecil. Sementara itu, fungsi =SEARCH tidak memiliki batasan seperti itu.
Fungsi ini sangat berguna saat mencari anomali atau pengidentifikasi unik.
Dalam contoh ini, saya ingin mengetahui berapa kali ‘Gui’ muncul dalam Teknologi Pemandu yang saya gunakan rumus =FIND(A2, B2), yang memberikan hasil 1.
Sekarang jika saya ingin mencari berapa kali ‘ gui’ malah muncul di Guiding Tech, saya harus menggunakan rumus =SEARCH karena tidak peka huruf besar-kecil. Formula untuk Menemukan: =FIND( find_text, within_text, [start_num] )
Rumus Pencarian: =SEARCH( find_text, within_text, [start_num] )
Kuasai Analisis Data
Fungsi-fungsi penting Microsoft Excel ini pasti akan membantu Anda dalam analisis data, tetapi daftar ini hanya menggores puncak gunung es.
Excel juga menyertakan beberapa fungsi lanjutan lainnya untuk mencapai hasil tertentu. Jika Anda tertarik mempelajari lebih lanjut tentang fungsi tersebut, beri tahu kami di bagian komentar di bawah.
Selanjutnya: Jika Anda ingin menggunakan Excel lebih efisien, maka Anda harus membaca artikel berikutnya untuk mengetahui beberapa pintasan navigasi Excel praktis yang harus Anda ketahui.