Fungsi Kondisional di Spreadsheet

Ketika seseorang mempelajari hampir semua bahasa pemrograman, salah satu hal pertama yang muncul adalah pernyataan kondisional. Itulah pernyataan klasik IF-ELSEIF-ELSE, yang memungkinkan program melakukan operasi berbeda berdasarkan kondisi logis. Namun, ini mungkin bukan hal pertama yang terpikirkan di Excel. Meski begitu, fitur ini ada dan cukup penting saat Anda mengerjakan laporan Excel dengan tingkat kompleksitas tertentu. Secara khusus, dalam tutorial ini kita akan membahas bagaimana struktur pernyataan IF-ELSE di Excel, serta beberapa fungsi lanjutan seperti COUNTIF() yang sangat berguna dalam situasi tertentu.

Pernyataan IF Dasar

Untuk mulai, mari kita lihat gambaran umum cara menulis pernyataan IF dasar di Excel. Sintaksnya sebagai berikut:

IF(condition, value_if_true, value_if_false)

Di mana:

• condition: nilai atau operasi logis yang dievaluasi menjadi TRUE atau FALSE
• value_if_true: Nilai yang dikembalikan jika kondisi bernilai TRUE
• value_if_false: Nilai yang dikembalikan jika kondisi bernilai FALSE

Baiklah, mari langsung ke contoh sederhana cara menggunakan kondisional dasar menggunakan tabel kecil:

Sederhana, bukan? Bagi Anda yang mengenal R, Anda mungkin menyadari bahwa sintaks ini pada dasarnya sama dengan fungsi ifelse(). Namun, ada beberapa hal yang agak berbeda. Misalnya, operator logika. Dalam contoh sederhana ini, saya menggunakan operator logika ">>" yang merupakan cara standar untuk menyatakan lebih besar dari. Meski demikian, yang lain tidak begitu standar. Ini termasuk operator tidak sama dengan, yang ditulis sebagai "<>"

Daftar lengkap semua operator logika yang digunakan di Excel dapat dilihat di bawah:

OR(), AND() dan NOT()

Operator perbandingan bukan satu-satunya komponen pernyataan kondisional Excel yang mungkin berbeda dari yang Anda gunakan di Python, R, atau Matlab. Faktanya, cara Anda mendefinisikan operasi boolean seperti OR, AND, dan NOT berbeda. Misalnya, di Excel, Anda akan menulis ekspresi OR seperti ini:

IF(OR(value_1 = k, value_2 = y), value_if_true, value_if_false)

Untuk melihat semua operator boolean beraksi di dalam Excel, lihat contoh di bawah:

Pernyataan IF Bertingkat (Nested)

Sama seperti Anda dapat membuat IF-ELSEIF-ELSE bertingkat di Python, R, atau Matlab, Anda juga dapat melakukannya di Excel. Caranya sesederhana menambahkan pernyataan IF lain di dalam pernyataan IF sebelumnya ketika evaluasinya TRUE atau FALSE. Ini memungkinkan Anda menguji lebih banyak kondisi dan mengembalikan lebih banyak hasil berdasarkan kondisi tersebut. Excel dapat menampung hingga 64 pernyataan IF bertingkat. Namun, saya sangat menyarankan untuk tidak menumpuk terlalu banyak pernyataan IF karena Anda harus berhati-hati dengan logikanya, dan proses debug atau perubahan di kemudian hari bisa menjadi sangat merepotkan.

Dengan begitu, berikut contoh sederhana penggunaan IF bertingkat langsung di Excel:

COUNTIF() dan COUNTIFS()

Selain pernyataan IF tradisional, Excel memiliki fungsi lain yang dapat melakukan operasi berdasarkan sekumpulan kondisi tertentu. Pada bagian ini, saya akan berfokus pada fungsi penghitungan kondisional, khususnya COUNTIF() dan COUNTIFS().

COUNTIF() memungkinkan pengguna menghitung sel yang memenuhi satu kriteria. Sintaksnya sebagai berikut:

COUNTIF(cell_range_to_count, criteria)

Di mana:

• cell_range_to_count: Rentang sel spesifik yang ingin Anda hitung
• criteria: Kriteria yang menentukan sel mana yang harus dihitung. Ini bisa sangat beragam, misalnya:
  • COUNTIF(A1:A10, 10) - Menghitung berapa banyak sel dalam rentang dari sel A1 hingga A10 yang sama dengan 10
  • COUNTIF(A1:A10, B5) - Menghitung berapa banyak sel dalam rentang dari sel A1 hingga A10 yang sama dengan nilai di sel B5
  • COUNTIF(A1:A10, "Gandalf") - Menghitung berapa banyak sel dalam rentang dari sel A1 hingga A10 yang sama dengan "Gandalf"
  • COUNTIF(A1:A10, ">20") - Menghitung berapa banyak sel dalam rentang dari sel A1 hingga A10 yang lebih besar dari 20
  • COUNTIF(A1:A10, "<>Gandalf") - Menghitung berapa banyak sel dalam rentang dari sel A1 hingga A10 yang tidak sama dengan "Gandalf"

COUNTIFS, seperti yang mungkin Anda duga, berbeda dari COUNTIF karena memungkinkan pengguna menghitung sel yang memenuhi beberapa kriteria. Sintaksnya sebagai berikut:

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

Penting untuk dicatat bahwa untuk setiap rentang tambahan yang Anda tentukan di COUNTIFS, jumlah baris dan kolomnya harus sama dengan rentang asli (cell_range_to_count_1), jika tidak Anda akan mendapatkan error.

Kedua fungsi ini sangat bagus digunakan pada laporan bisnis. Salah satu contoh terbaik bagi saya adalah betapa mudahnya mereka menghitung jumlah janji temu yang dibuat perwakilan penjualan kami setiap bulan dan di mana posisi mereka di corong penjualan.

Dalam video di bawah, saya akan memperlihatkan cara kerja kedua fungsi ini secara langsung. Pertama, mari mulai dengan menghitung total janji temu dari sekelompok tenaga penjualan fiktif:

Mudah, bukan? Anda baru saja menjumlahkan berapa banyak janji temu yang dimiliki masing-masing tenaga penjualan fiktif kami secara total. Secara teori, Anda juga bisa melakukannya hanya dengan pernyataan IF dasar, tetapi itu akan membutuhkan lebih banyak pekerjaan untuk hasil yang sama persis.

Sekarang, misalkan Anda juga ingin menghitung janji temu yang dimiliki masing-masing tenaga penjualan ini setiap bulan. Dalam hal ini, COUNTIFS() adalah jawabannya; Anda dapat melihatnya beraksi di video berikut:

Catatan: Anda mungkin memperhatikan bahwa saya menambahkan tanda $ pada rentang yang ingin saya hitung di video terakhir. Ini dilakukan untuk menguncinya dan mencegah perubahan saat saya menyeret rumus ke bawah.

Voilá, kita menghitung janji temu tenaga penjualan dummy per bulan tanpa kesulitan. Perlu disebutkan bahwa Anda bisa melakukannya dengan cara yang lebih "efisien" jika Anda bermain dengan tanda $ untuk mengunci sel dan rentang tertentu agar tidak berpindah saat Anda menyeret rumus (mis., saya bisa saja menulis rumus di F2 lalu menyeretnya ke yang lain). Namun, saya ingin lebih eksplisit menulis rumus COUNTIFS untuk Januari dan Februari agar lebih jelas. Jika Anda penasaran, saya mendorong Anda mencoba menulis ulang rumus COUNTIFS() di F2, sehingga Anda dapat menyeretnya ke sel lain dan mendapatkan hasil yang sama. Ini bisa menjadi latihan berguna karena sangat praktis bermain dengan penguncian rentang dan sel saat Anda menyeret rumus ketika laporan bisnis Excel Anda menjadi besar. Berikut tabel data yang digunakan dalam tutorial ini.

SUMIF() dan SUMIFS()

Kita telah melihat bagaimana Anda dapat menghitung sel yang memenuhi satu kriteria atau beberapa kriteria dengan COUNTIF() dan COUNTIFS(), tetapi bagaimana jika Anda perlu menjumlahkan sel alih-alih hanya menghitungnya? Di sinilah SUMIF() dan SUMIFS() berperan. Fungsi-fungsi ini sangat berguna ketika Anda harus menjumlahkan pendapatan yang dihasilkan oleh seorang tenaga penjualan secara total atau dalam bulan tertentu, misalnya. Sintaks kedua fungsi adalah sebagai berikut:

SUMIF(criteria_range, criteria, (optional) sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criterion_range_2, criteria_2...criteria_range_n, criteria_n)

Di mana:

• criteria_range: Ini adalah rentang sel tempat kriteria akan diperiksa.
• criteria: Kriteria kondisional yang digunakan untuk menentukan sel mana yang akan dijumlahkan.
• sum_range: Sel yang akan dijumlahkan. Jika tidak disertakan dalam COUNTIF(), sel dalam criteria_range yang akan dijumlahkan.

Sekarang, tanpa berlama-lama, mari masuk ke contoh yang menggunakan SUMIF() dan SUMIFS() untuk menghitung total pendapatan dan pendapatan per bulan yang dihasilkan oleh perwakilan penjualan fiktif kita:

Menarik untuk dicatat bahwa, dalam kasus SUMIFS(), sum_range adalah argumen wajib dan menjadi argumen pertama, tetapi bersifat opsional dan menjadi argumen terakhir di SUMIF(). Ini dapat membingungkan saat Anda menggunakan kedua rumus tersebut seperti yang terlihat ketika saya mengetik fungsi SUMIFS untuk Februari. Oleh karena itu, saya menyarankan kehati-hatian saat bekerja dengan kedua rumus. Pastikan selalu memeriksa bahwa Anda menjumlahkan rentang sel yang benar.

IFERROR()

Bagi Anda yang mengetahui bahasa pemrograman/scripting, Anda tahu ada konsep blok try/catch untuk penanganan error. Di Excel, kita memiliki fungsi IFERROR() untuk melakukan hal yang sama. Fungsi IFERROR memungkinkan pengguna "menangkap" error seperti #N/A, #VALUE! atau #REF!, dan memberikan keluaran yang lebih bermakna. Sintaks fungsi IRERROR adalah sebagai berikut:

IFERROR(value, value_if_error)

Di mana:

• value: Nilai atau rumus yang akan diperiksa apakah terjadi error
• value_if_error: Nilai yang dikeluarkan saat fungsi menemukan error.

Mari lihat cara kerjanya secara langsung dalam video berikut:

Saat Anda bekerja dengan laporan penjualan bisnis, biasanya merupakan praktik yang baik untuk membungkus perhitungan tertentu dengan fungsi IFERROR. Ada kalanya Anda akan mendapati tenaga penjualan baru memiliki sangat sedikit janji temu, prospek berkualitas, atau kesepakatan tertutup, yang dapat menyebabkan pembagian dengan 0 saat menghitung hal-hal seperti persentase janji temu yang menjadi prospek berkualitas bagi seorang tenaga penjualan. Ini dapat menghasilkan error yang tampak buruk dan kemungkinan membuat laporan Anda terlihat berantakan serta lebih sulit dibaca oleh pengambil keputusan. Namun, jika Anda membungkus perhitungan tersebut dengan pernyataan IFERROR(), Anda dapat mengganti error tersebut dengan sesuatu yang lebih enak dipandang seperti tanda hubung, yang akan membuat laporan Anda lebih rapi. Lebih jauh lagi, pernyataan IFERROR() dapat melampaui sekadar menampilkan pesan error yang lebih baik. Anda juga dapat membuatnya bertingkat untuk melakukan hal-hal seperti VLOOKUP berantai.

Kesimpulan

Selamat! Kini Anda mengetahui beberapa cara memanfaatkan kondisional di Excel. Dalam tutorial ini, saya berupaya membahas fungsi-fungsi yang sering saya gunakan saat diminta menyelesaikan laporan bisnis Excel. Namun, jika Anda masih ingin mempelajari lebih lanjut, ada fungsi lain seperti SWITCH(), AVERAGEIF() atau AVERAGEIFS() yang tidak saya bahas di sini dan mungkin menarik bagi Anda.

Selain itu, ada hal seperti menggunakan wildcard dalam fungsi seperti COUNTIFS() yang sepenuhnya valid untuk menghitung kecocokan string parsial. Misalnya, anggaplah Anda memiliki file Excel dengan daftar item menu restoran, dan Anda ingin menghitung berapa banyak yang menyebut kata "fish". Dalam hal ini, Anda dapat menulis COUNTIF(cell_range,"*fish") dan fungsi ini akan menghitung item seperti swordfish atau dolphinfish. Saya sangat menyarankan Anda mempelajari lebih lanjut tentang wildcard karena sangat berguna dan Anda tidak pernah tahu kapan ini akan membantu di proyek Excel Anda berikutnya.

Terakhir, Anda juga dapat melihat kursus Data Analysis with Spreadsheets di DataCamp karena memiliki satu bagian khusus untuk fungsi kondisional, yang dapat menjadi rujukan lebih lanjut.

Lihat juga Tutorial if…elif…else di Python kami.