rumus Office 365/Excel 2016+

  • 15 menit

Unit ini memperkenalkan tiga rumus baru yang akan Anda temukan di Office 365/Excel 2016+ yang disebut XLOOKUP(), FILTER(), dan LET(). Cara lain untuk mengetahui tentang fitur dan fungsionalitas baru dalam aplikasi adalah dengan bergabung dengan Program Microsoft Office Insiders (ditautkan di bagian referensi di akhir modul ini).

XLOOKUP()

XLOOKUP() adalah versi VLOOKUP() baru yang lebih kuat. Ini lebih sederhana, lebih cepat, dan lebih fleksibel.

Alasan mengapa XLOOKUP() lebih optimal daripada VLOOKUP() adalah sebagai berikut:

  • Kolom dan baris pencarian menggabungkan VLOOKUP() dan HLOOKUP() untuk pencarian yang lebih komprehensif.

  • Cari kolom di sebelah kiri mengganti pola INDEX() MATCH(), memungkinkan Anda menggunakan kombinasi yang paling sesuai untuk pencarian Anda.

  • Rumus lebih kuat karena tidak "pecah" saat kolom ditambahkan/dihapus.

XLOOKUP() menyertakan sintaks dengan tiga parameter yang diperlukan. Fungsi melakukan kecocokan yang tepat secara default.

Cuplikan layar bilah Rumus Excel dengan sintaks fungsi XLOOKUP().

XLOOKUP() memiliki fitur-fitur berikut:

  • Mengembalikan nilai dari kolom tertentu berdasarkan nilai di kolom lain

  • Mengembalikan nilai yang berbeda jika tidak ada hasil yang ditemukan

  • Pencarian dari atas atau dari bawah

XLOOKUP() memiliki enam parameter dengan tiga parameter terakhir menjadi parameter opsional:

  • lookup_value: Parameter yang digunakan untuk menentukan nilai yang ingin Anda temukan

  • lookup_array: Parameter array yang digunakan untuk menentukan kolom untuk menemukan nilai

  • return_array: Parameter array yang digunakan untuk menentukan kolom untuk mengembalikan nilai dari

  • if_not_found: Jika tidak ada kecocokan yang ditemukan, kembalikan nilai opsional ini

  • match_mode: Parameter opsional untuk menentukan kecocokan yang tepat, pertama di atas/di bawah, atau pencarian kartubebas

  • search_mode: Tentukan pencarian dari atas atau dari bawah dengan parameter opsional ini

Cuplikan layar contoh XLookup().

Dalam contoh himpunan data sebelumnya, perhatikan rumus XLOOKUP() di sebelah kanan dalam kotak hitam yang memperlihatkan hasil yang dikembalikan. Tiga contoh menjawab pertanyaan berikut:

  • Temukan Produk menurut ID: Rumus yang menunjukkan menemukan Produk untuk ID Produk = 109 di mana hasil produk ditemukan dalam kolom di sebelah kanan kolom ID Produk.

  • Temukan Kota menurut ZIP: Contoh rumus menunjukkan menemukan Kota untuk ZIP = 21658, yang merupakan hasil yang terletak di kolom di sebelah kiri kolom ZIP.

  • Temukan Produk menurut Kota terakhir: Rumus ini menunjukkan penggunaan parameter opsional "Tidak Ada Hasil yang ditemukan" dikembalikan jika tidak ada hasil yang ditemukan, kecocokan persis, dan -1 menunjukkan untuk mencari dari bawah ke bagian atas tabel data.

FILTER()

FILTER() adalah fungsi array baru. Menambahkan rumus ke sel tunggal mengembalikan subset tabel, dan nilai lainnya meluap ke sel lain dalam hasil. FILTER() mengembalikan baris data dan memungkinkan beberapa kondisi dengan menggunakan dan/atau logika.

FILTER() memiliki fitur-fitur berikut:

  • Mengembalikan beberapa hasil kecocokan untuk satu atau beberapa nilai pencarian

  • Memfilter data tanpa perlu [refresh]{.underline}

  • Dapat ditumpuk di dalam fungsi Excel lainnya

Detail berikut menjelaskan tiga parameter yang disertakan dengan FILTER():

  • array: Parameter yang digunakan untuk menentukan rentang kolom dan baris untuk difilter

  • include: Parameter yang digunakan untuk memberikan kriteria aturan pemfilteran

  • if_empty: Nilai parameter opsional untuk dikembalikan jika tidak ada baris yang memenuhi kondisi

Cuplikan layar contoh Filter() Tunggal.

Contoh himpunan data sebelumnya memperlihatkan rumus FILTER() dalam kotak hitam dengan hasil yang dikembalikan. Perhatikan bahwa ia menggunakan tabel alih-alih rentang. Kami menyarankan agar Anda selalu menggunakan tabel jika anda bisa. Contoh sebelumnya memfilter tabel SalesTable, di mana Wilayah = Barat, dan mengembalikan semua baris yang cocok dalam hasil.

Cuplikan layar Contoh beberapa Filter().

Contoh ini menggunakan himpunan data yang sama, tetapi menerapkan tiga filter ke tabel. Rumus memfilter tabel pada kriteria berikut. Semua kriteria harus dipenuhi agar baris disertakan.

  • Produk = Palma UM-01

  • Wilayah = Barat

-Pendapatan = Lebih besar dari USD 1.215,00

Rumus menggunakan fungsi kalikan karena perbandingan logis akan menghasilkan nol (0) untuk false atau satu (1) untuk true. Jika semua kondisi TRUE, maka 1 * 1 * 1 = 1. Namun, jika ada kondisi nol (0) atau salah, maka seluruh logika salah.

Tanda bintang (*) digunakan untuk kondisi AND , dan tanda plus (+) digunakan untuk kondisi OR .

LET()

Fungsi LET() menawarkan fleksibilitas yang cukup besar untuk perhitungan kompleks dan menyediakan cara yang lebih sederhana untuk mencerna potongan rumus yang berbeda. Ini menggabungkan kemampuan untuk penyimpanan perhitungan dan nilai yang menggunakan variabel dengan sintaks rumus asli Excel.

Diagram Sintaks Fungsi LET().

Variabel digunakan untuk menetapkan nama ke nilai atau perhitungan. Variabel ini digunakan untuk memanggil kembali sintaks tanpa harus berulang kali menulis ulang rumus. Anda dapat menentukan hingga 126 variabel berbeda dalam fungsi, tetapi minimal, Anda diharuskan memiliki tiga komponen (variabel, nilai variabel, dan perhitungan). Anda juga dapat memanfaatkan fungsi array lain seperti FILTER() dalam fungsi LET(). Contoh berikut dibangun pada contoh FILTER() dari sebelumnya tetapi sekarang dengan variabel yang ditetapkan.

Cuplikan layar contoh LET().

Pada cuplikan layar sebelumnya, angka satu hingga empat adalah variabel dan definisi. Pernyataan terakhir adalah perhitungan yang menggunakan variabel.

  • ProductRange = Rentang kolom produk

  • Produk = Produk yang akan difilter

  • RegionRange = Rentang kolom wilayah

  • Wilayah = Wilayah yang akan difilter

  • Filter = Pemfilteran pada tabel untuk Produk dan Wilayah