Gunakan TAKE dan DROP di Excel untuk membuat daftar cerdas yang memperbarui sendiri

Banyak rumus Excel yang saya gunakan untuk daftar memerlukan pengawasan. Terkadang, ketika saya menambahkan baris baru, ada yang rusak atau perlu disesuaikan. TAKE dan DROP tidak memiliki masalah itu. Kedua fungsi array dinamis ini, yang membuat tabel berkembang secara otomatis, memungkinkan Anda menarik atau menghapus baris dan kolom tertentu dari kumpulan data, dan mereka memperbarui sendiri saat data Anda berubah.

Padukan mereka dengan fungsi seperti SORT dan FILTER, dan Anda akan mendapatkan daftar yang bisa memelihara dirinya sendiri tanpa usaha tambahan. Berikut cara saya menggunakannya untuk membuat daftar yang benar-benar tetap terkini.

TAKE mengambil tepat baris atau kolom yang Anda butuhkan

Arahkan ke data Anda, beri tahu berapa baris, dan itu akan menyampaikannya

TAKE melakukan satu hal dengan baik. Ini mengambil sejumlah baris atau kolom dari sebuah rentang, mulai dari ujung mana pun yang Anda tentukan. Anda tidak memerlukan kolom tambahan atau penanaman INDEX-MATCH yang rumit. Anda menunjuknya ke data Anda, memberi tahu berapa banyak baris yang Anda inginkan, dan itu akan memberikannya.

Berikut adalah sintaksnya:

=TAKE(array, rows, [columns])
  • array: Rentang sumber atau array yang sedang Anda ekstrak.
  • baris: Jumlah baris yang akan dikembalikan. Angka positif mengambil dari atas; angka negatif mengambil dari bawah.
  • kolom (opsional): Bekerja dengan cara yang sama — positif mengambil dari kiri, negatif dari kanan.

Misalkan Anda memiliki data penjualan di A2:D20 dan ingin lima baris pertama. Anda akan menggunakan rumus berikut:

=TAKE(A2:D20, 5)

Perlu tiga terakhir? Ganti dengan -3. Hasilnya otomatis menyebar ke sel di sekitarnya, dan akan menghitung ulang setiap kali data sumber berubah.

DROP menghapus baris atau kolom yang tidak Anda inginkan

Katakan padanya apa yang harus dibuang, dan ia akan mengembalikan semuanya selain itu

DROP adalah kebalikan dari TAKE. Alih-alih menentukan apa yang ingin Anda pertahankan, Anda memberi tahu apa yang harus dibuang, dan itu mengembalikan semua yang lain. Saya menemukan ini lebih intuitif dalam situasi di mana saya tahu persis berapa banyak baris yang tidak relevan berada di bagian atas atau bawah dataset.

Sintaks mencerminkan TAKE:

=DROP(array, rows, [columns])
  • array: Rentang sumber atau array.
  • baris: Jumlah baris yang akan diabaikan. Positif menghapus dari atas; negatif menghapus dari bawah.
  • kolom (opsional): Positif menghapus dari kiri, negatif dari kanan.

Misalkan dataset Anda di A2:D20 memiliki dua baris ringkasan seperti header yang tidak Anda perlukan. Rumus berikut menghapusnya dan mengembalikan sisanya.

=DROP(A2:D20, 2)

Anda juga dapat menghapus kolom dengan menggunakan:

=DROP(A2:D20, 0, 1)

Ini menghapus kolom pertama, yang berguna ketika bidang ID membuat output Anda berantakan. Seperti TAKE, hasilnya mengalir secara dinamis dan diperbarui seiring pertumbuhan data Anda.

Gabungkan TAKE dan DROP untuk pemotongan data yang tepat

Tumpuk mereka bersama untuk mengekstrak bagian mana pun dari tengah

Sendiri, TAKE dan DROP menarik dari tepi dataset. Namun, jika Anda menyusunnya bersama, Anda dapat mengekstrak bagian mana pun dari tengah, sesuatu yang tidak dapat ditangani oleh salah satu fungsi saja.

Idenya adalah menggunakan DROP untuk menghapus baris yang tidak Anda butuhkan di bagian atas, kemudian membungkus hasilnya dengan TAKE untuk membatasi berapa banyak baris yang Anda simpan. Menggunakan data penjualan sebagai contoh, misalkan Anda ingin melewatkan empat entri pertama dan mengambil delapan berikutnya, mencakup transaksi dari 12 Januari hingga 3 Februari. Rumusnya akan:

=TAKE(DROP(A2:D20, 4), 8)

DROP menghapus empat baris pertama (entri 5 Januari), dan TAKE mengembalikan delapan baris berikutnya dari sisa yang ada. Output mencakup keempat kolom, dari Tanggal hingga Salesperson, sehingga Anda mendapatkan irisan lengkap tanpa menyentuh kolom bantuan atau memilih rentang secara manual.

Ini sangat berguna terutama ketika dataset Anda berkembang seiring waktu, karena kedua fungsi menyesuaikan secara dinamis saat baris baru ditambahkan.

Membangun daftar "top N" yang selalu diperbarui sendiri dengan TAKE dan SORT

Daftar akan memperbarui dirinya sendiri setiap kali data Anda berubah

Inilah saat TAKE mulai menunjukkan manfaatnya. Menggabungkannya dengan SORT memungkinkan Anda membuat daftar yang selalu menampilkan nilai tertinggi (atau terendah) dalam dataset Anda — dan daftar itu memperbarui dirinya sendiri setiap kali data berubah.

Menggunakan data penjualan lagi, misalkan Anda ingin daftar lima transaksi dengan penghasilan tertinggi berdasarkan Jumlah Penjualan. Rumusnya adalah:

=TAKE(SORT(A2:J20, 7, -1), 5)

SORT mengatur ulang semua 19 baris berdasarkan kolom 7 (Jumlah Penjualan) secara menurun, dan TAKE mengambil lima teratas dari hasil yang sudah diurutkan tersebut. Anda mendapatkan baris lengkap untuk setiap entri, Tanggal, Wilayah, Tenaga Penjual, dan semuanya lainnya, sehingga konteks tetap utuh. Jika Anda tidak familiar dengan fungsi SORT, panduan kami tentang mengurutkan data di Excel mencakup dasar-dasarnya.

Bagian terbaiknya adalah apa yang terjadi ketika data penjualan baru masuk. Ketika Anda menambahkan baris dengan Jumlah Penjualan yang lebih tinggi, itu secara otomatis naik ke lima teratas. Jika Anda mengubah rentang sumber Anda menjadi Tabel Excel, rentang tersebut juga akan berkembang dengan sendirinya, sehingga seluruh pengaturan menjadi sepenuhnya otomatis.

Buat daftar "entri terbaru" dinamis dengan DROP dan COUNTA

Selalu menampilkan baris terbaru

Daftar "top N" berguna, tetapi terkadang Anda hanya ingin melihat penambahan terbaru ke dalam dataset. DROP dipasangkan dengan COUNTA menangani ini dengan baik — ini menghitung berapa banyak baris yang ada dan menghapus semuanya kecuali beberapa yang terakhir.

Menggunakan data penjualan, berikut cara selalu menampilkan lima entri terakhir:

=DROP(A2:A20, COUNTA(A2:A20) - 5)

COUNTA menghitung semua sel yang terisi di kolom A — yaitu 19 dalam kasus ini. Mengurangi 5 memberikan DROP 14, jadi ini menghapus 14 baris pertama dan mengembalikan lima sisanya. Saat ini, itu akan menjadi transaksi bulan Maret dari tenaga penjual seperti Mike Wilson kepada Tom Rodriguez.

Jika Anda menambahkan baris baru untuk bulan April, COUNTA meningkat menjadi 20, DROP menyesuaikan menjadi 15, dan output bergeser untuk menampilkan lima entri terbaru. Anda juga bisa menggunakan formula berikut untuk hasil yang sama, tetapi pendekatan DROP dan COUNTA lebih fleksibel ketika rentang Anda tidak tetap.

=TAKE(A2:A20, -5)

TAKE dan DROP bekerja bahkan lebih baik dengan fungsi lain

Cobalah kombinasi ini selanjutnya

Anda sudah melihat TAKE dengan SORT dan DROP dengan COUNTA, tetapi masih banyak yang bisa dijelajahi. Bungkus FILTER di dalam TAKE untuk membuat daftar yang menampilkan, misalnya, hanya penjualan Elektronik di atas $3.000 — diurutkan dan dibatasi pada jumlah tertentu. Atau gunakan DROP dengan UNIQUE untuk menghapus header duplikat ketika menggabungkan data dari beberapa lembar. CHOOSECOLS dipadukan dengan TAKE juga bisa memangkas dataset lebar menjadi hanya kolom yang penting. Setelah Anda mulai menumpuk fungsi-fungsi ini, kombinasi menjadi cepat terasa praktis.

Artikel terkait