UNIK dari Kolom yang Tidak Berdekatan - Tips Excel

Suatu hari, saya akan membuat kombinasi unik dari dua kolom yang tidak bersebelahan di Excel. Saya biasanya melakukan ini dengan Hapus Duplikat atau dengan Filter Tingkat Lanjut, tetapi saya pikir saya akan mencoba melakukannya dengan fungsi UNIK baru yang masuk ke Office 365 pada 2019. Saya mencoba beberapa ide dan tidak ada yang berhasil. Jadi, saya pergi ke master Dynamic Arrays, Joe McDaid, untuk meminta bantuan. Jawabannya cukup keren, dan saya yakin saya akan melupakannya, jadi saya mendokumentasikannya untuk Anda dan saya. Saya yakin, dua tahun dari sekarang, saya akan Google bagaimana melakukan ini dan menyadari "Oh, lihat! Akulah yang menulis artikel tentang ini!"

Sebelum masuk ke fungsi UNIQUE, lihat apa yang saya coba lakukan. Saya ingin setiap kombinasi unik dari Sales Rep dari kolom B dan Product dari kolom C. Biasanya, saya akan mengikuti langkah-langkah ini:

Salin judul dari B1 dan D1 ke bagian kosong di lembar kerja
Dari B1, pilih Data, Filter, Advanced
Dalam dialog Filter Tingkat Lanjut, pilih Salin Ke Lokasi Baru
Tentukan judul dari Langkah 1 sebagai kisaran Output
Pilih kotak untuk Unique Values Only
Klik OK

Salin dua judul ke bagian kosong yang menjadi kisaran keluaran

Hasilnya adalah setiap kombinasi unik dari kedua bidang tersebut. Perhatikan bahwa Filter Lanjutan tidak mengurutkan item - item muncul dalam urutan aslinya.

Mendapatkan daftar unik adalah salah satu kegunaan favorit saya untuk Filter Tingkat Lanjut

Proses ini menjadi lebih mudah di Excel 2010 berkat perintah Hapus Duplikat pada tab Data di Pita. Ikuti langkah ini:

Pilih B1: D227 dan Ctrl + C untuk Menyalin
Tempel ke bagian kosong di lembar kerja.

Buat salinan data karena Remove Duplicates bersifat merusak
Pilih Data, Hapus Duplikat
Dalam kotak dialog Hapus Duplikat, batal pilih Tanggal. Ini memberitahu Excel untuk hanya melihat Rep dan Produk.
Klik OK

Beri tahu Hapus Duplikat untuk hanya mempertimbangkan Rep dan Tanggal

Hasilnya hampir sempurna - Anda hanya perlu menghapus kolom Tanggal.

Hapus kolom ekstra

Pertanyaan: Adakah cara agar fungsi UNIK hanya melihat kolom B & D? (Jika Anda belum melihat fungsi UNIQUE baru, baca: fungsi UNIQUE di Excel.)

Meminta =UNIQUE(B2:D227)akan memberi Anda setiap kombinasi unik dari Rep, Tanggal, dan Produk yang bukan yang kami cari.

Bagaimana kita bisa melewatkan dua kolom yang tidak berdekatan ke fungsi UNIQUE?

Ketika Dynamic Arrays diperkenalkan pada bulan September, saya berkata bahwa kita tidak perlu lagi mengkhawatirkan kerumitan rumus Ctrl + Shift + Enter. Tetapi untuk mengatasi masalah ini, Anda akan menggunakan konsep yang disebut Pengangkatan. Mudah-mudahan sekarang, Anda telah mengunduh e-book Excel Dynamic Arrays Straight To The Point saya. Buka halaman 31-33 untuk penjelasan lengkap tentang Mengangkat.

Lihat buku saya untuk penjelasan lengkap tentang Lifting (dan nanti, saat Anda pergi untuk mengurutkan hasil, Pairwise Lifting)

Ambil fungsi Excel yang mengharapkan satu nilai. Misalnya, =CHOOSE(Z1,"Apple","Banana")akan mengembalikan Apel atau Pisang bergantung pada apakah Z1 berisi 1 (untuk Apple) atau 2 (untuk Pisang). Fungsi CHOOSE mengharapkan skalar sebagai argumen pertama.

Namun sebaliknya, Anda akan meneruskan konstanta array (1,2) sebagai argumen pertama untuk MEMILIH. Excel akan melakukan operasi Pengangkatan dan menghitung PILIH dua kali. Untuk nilai 1, Anda menginginkan perwakilan penjualan di B2: B227. Untuk nilai 2, Anda menginginkan produk di D2: D227.

Beri tahu PILIH untuk mengembalikan dua jawaban

Biasanya, di Excel lama, persimpangan implisit akan mengacaukan hasil. Tapi sekarang Excel bisa memberikan hasil ke banyak sel, rumus di atas berhasil mengembalikan array dari semua jawaban di B dan D:

Keberhasilan! Semuanya menurun dari sini

Saya merasa seperti saya akan menghina kecerdasan Anda untuk menulis sisa artikel, karena dari sini sangat sederhana.

Bungkus rumus dari tangkapan layar sebelumnya dalam UNIK dan Anda hanya mendapatkan kombinasi unik dari Perwakilan Penjualan dan Produk yang digunakan =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Masih belum diurutkan

Untuk memeriksa pemahaman Anda, coba ubah rumus di atas untuk mengembalikan semua kombinasi unik dari tiga kolom: Sales Rep, Product, Color.

Pertama, ubah konstanta array menjadi (1,2,3).

Kemudian, tambahkan argumen keempat untuk MEMILIH untuk kembali warna dari E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Kembalikan kombinasi unik dari tiga kolom

Alangkah baiknya untuk mengurutkan hasil tersebut, jadi kita beralih ke Sortir dengan rumus menggunakan SORT dan SORTBY.

Biasanya, fungsi untuk mengurutkan berdasarkan kolom pertama naik adalah =SORT(Array)atau =SORT(Array,1,1).

Untuk mengurutkan berdasarkan tiga kolom, Anda perlu melakukan pengangkatan berpasangan =SORT(Array,(1,2,3),(1,1,1)). Dalam rumus ini, saat Anda masuk ke argumen kedua dari SORT, Excel ingin mengetahui kolom mana yang akan diurutkan. Alih-alih satu nilai, kirim tiga kolom di dalam konstanta array: (1,2,3). Saat Anda sampai ke argumen ketiga di mana tentukan 1 untuk Ascending atau -1 untuk Descending, kirim konstanta array dengan tiga 1 untuk menunjukkan Ascending, Ascending, Ascending. Tangkapan layar berikut menunjukkan =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Untuk informasi selengkapnya tentang pengangkatan berpasangan, lihat halaman 34 dari Excel Dynamic Arays Straight to the Point.

Setidaknya hingga akhir 2018, Anda bisa mendownload buku Excel Dynamic Arrays secara gratis menggunakan link di bagian bawah halaman ini.

Saya terdorong untuk menemukan bahwa jawaban atas pertanyaan hari ini agak rumit. Ketika Dynamic Arrays keluar, saya langsung memikirkan semua formula menakjubkan yang diposting di Message Board oleh Aladin Akyurek dan lainnya dan bagaimana formula tersebut akan menjadi jauh lebih sederhana di Excel baru. Tetapi contoh hari ini menunjukkan bahwa masih akan ada kebutuhan bagi para jenius formula untuk membuat cara-cara baru untuk menggunakan Array Dinamis.