UNIK Dari Lajur Tidak Bersebelahan - Petua Excel

Pada hari yang lain, saya akan membuat gabungan unik dua lajur yang tidak bersebelahan di Excel. Saya biasanya melakukan ini dengan Remove Duplicates atau dengan Advanced Filter, tetapi saya fikir saya akan mencubanya dengan fungsi UNIK yang baru datang ke Office 365 pada tahun 2019. Saya mencuba beberapa idea dan tidak akan berjaya. Oleh itu, saya berjumpa dengan master Dynamic Arrays, Joe McDaid, untuk mendapatkan bantuan. Jawapannya cukup hebat, dan saya pasti akan melupakannya, jadi saya mendokumentasikannya untuk anda dan saya. Saya yakin, dua tahun dari sekarang, saya akan Google bagaimana melakukan ini dan menyedari "Oh, lihat! Akulah yang menulis artikel tentang ini!"

Sebelum sampai ke fungsi UNIK, lihatlah apa yang saya cuba lakukan. Saya mahukan setiap gabungan unik Penjualan dari lajur B dan Produk dari lajur C. Biasanya, saya akan mengikuti langkah-langkah berikut:

Salin tajuk dari B1 dan D1 ke bahagian kosong lembaran kerja
Dari B1, pilih Data, Filter, Advanced
Dalam dialog Penapis Lanjutan, pilih Salin Ke Lokasi Baru
Tentukan tajuk dari Langkah 1 sebagai julat Output
Pilih kotak untuk Nilai Unik Sahaja
Klik OK

Salin dua tajuk ke bahagian kosong yang menjadi julat keluaran

Hasilnya adalah setiap kombinasi unik dari dua bidang. Perhatikan bahawa Penapis Lanjutan tidak menyusun item - item tersebut muncul dalam urutan asal.

Mendapatkan senarai unik adalah salah satu kegunaan kegemaran saya untuk Advanced Filter

Proses ini menjadi lebih mudah di Excel 2010 berkat arahan Remove Duplicates pada tab Data Ribbon. Ikut langkah-langkah ini:

Pilih B1: D227 dan Ctrl + C untuk Salin
Tampal pada bahagian lembaran kerja yang kosong.

Buat salinan data kerana Hapus Penduaan merosakkan
Pilih Data, Buang Pendua
Dalam kotak dialog Hapus Pendua, batalkan pilihan Tarikh. Ini memberitahu Excel untuk melihat Rep dan Product sahaja.
Klik OK

Beritahu Keluarkan Pendua untuk hanya mempertimbangkan Rep dan Tarikh

Hasilnya hampir sempurna - anda hanya perlu memadamkan ruangan Tarikh.

Padamkan lajur tambahan

Persoalannya: Adakah cara untuk fungsi UNIK hanya melihat lajur B & D? (Sekiranya anda belum melihat fungsi UNIK baru, baca: Fungsi UNIK di Excel.)

Meminta =UNIQUE(B2:D227)akan memberi anda setiap kombinasi unik Rep, Date, dan Product yang bukan yang kami cari.

Bagaimana kita boleh meneruskan dua lajur yang tidak bersebelahan ke fungsi UNIK?

Semasa Dynamic Arrays diperkenalkan pada bulan September, saya mengatakan bahawa kita tidak perlu lagi risau tentang kerumitan formula Ctrl + Shift + Enter lagi. Tetapi untuk menyelesaikan masalah ini, anda akan menggunakan konsep yang disebut Lifting. Mudah-mudahan sekarang, anda telah memuat turun e-book Excel Dynamic Arrays Straight To The Point. Buka halaman 31-33 untuk penjelasan lengkap mengenai Lifting.

Lihat buku saya untuk penjelasan lengkap mengenai Lifting (dan kemudian, apabila anda pergi untuk menyusun hasilnya, Pairwise Lifting)

Ikuti fungsi Excel yang mengharapkan satu nilai. Sebagai contoh, =CHOOSE(Z1,"Apple","Banana")akan mengembalikan Apple atau Pisang bergantung kepada jika Z1 mengandungi 1 (untuk Apple) atau 2 (untuk Pisang). Fungsi PILIH mengharapkan skalar sebagai argumen pertama.

Tetapi sebaliknya, anda akan meneruskan pemalar laras (1,2) sebagai argumen pertama untuk PILIH. Excel akan melakukan operasi Lifting dan mengira PILIH dua kali. Untuk nilai 1, anda mahukan wakil jualan di B2: B227. Untuk nilai 2, anda mahukan produk dalam D2: D227.

Suruh PILIH untuk mengembalikan dua jawapan

Biasanya, di Excel lama, persimpangan tersirat akan mengacaukan hasilnya. Tetapi sekarang Excel dapat menumpahkan hasil ke banyak sel, formula di atas berjaya mengembalikan pelbagai semua jawapan dalam B dan D:

Berjaya! Semuanya menurun dari sini

Saya rasa saya akan menghina kepandaian anda untuk menulis artikel yang lain, kerana dari sini sangat mudah.

Bungkus formula dari tangkapan skrin sebelumnya secara UNIK dan anda hanya mendapat kombinasi unik dari Penjualan dan Produk Penjualan yang unik =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Masih belum disusun

Untuk memeriksa pemahaman anda, cuba ubah formula di atas untuk mengembalikan semua kombinasi unik tiga lajur: Jualan, Produk, Warna.

Pertama, ubah pemalar array untuk merujuk kepada (1,2,3).

Kemudian, tambah hujah keempat PILIH untuk kembali warna dari E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Kembalikan gabungan unik tiga lajur

Adalah baik untuk menyusun keputusan tersebut, jadi kami beralih ke Susun dengan formula menggunakan SORT dan SORTBY.

Biasanya, fungsi untuk mengisih mengikut lajur pertama menaik adalah =SORT(Array)atau =SORT(Array,1,1).

Untuk mengisih mengikut tiga lajur, anda perlu mengangkatnya secara berpasangan =SORT(Array,(1,2,3),(1,1,1)). Dalam formula ini, apabila anda sampai ke argumen SORT kedua, Excel ingin mengetahui lajur yang hendak disusun. Daripada satu nilai, hantarkan tiga lajur di dalam pemalar larik: (1,2,3). Apabila anda sampai ke argumen ketiga di mana tentukan 1 untuk Menaik atau -1 untuk Menurun, hantarkan laras larik dengan tiga 1 untuk menunjukkan Ascending, Ascending, Ascending. Tangkapan skrin berikut menunjukkan =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Untuk lebih lanjut mengenai mengangkat berpasangan, lihat halaman 34 Excel Dynamic Arrays Straight to the Point.

Sekurang-kurangnya sehingga akhir 2018, anda boleh memuat turun buku Excel Dynamic Arrays secara percuma menggunakan pautan di bahagian bawah halaman ini.

Saya terdorong untuk mengetahui bahawa jawapan untuk soalan hari ini agak rumit. Ketika Dynamic Arrays keluar, saya segera memikirkan semua formula luar biasa yang disiarkan di Papan Mesej oleh Aladin Akyurek dan yang lain dan bagaimana formula tersebut akan menjadi jauh lebih mudah di Excel baru. Tetapi contoh hari ini menunjukkan bahawa masih ada keperluan untuk genius formula untuk membuat kaedah baru untuk menggunakan Dynamic Arrays.