Bitişik Olmayan Sütunlardan EŞSİZ - Excel İpuçları

Geçen gün, Excel'de bitişik olmayan iki sütunun benzersiz bir kombinasyonunu oluşturmak üzereydim. Bunu genellikle Yinelenenleri Kaldır veya Gelişmiş Filtre ile yapıyorum, ancak bunu 2019'da Office 365'e gelen yeni UNIQUE işleviyle yapmayı deneyeceğimi düşündüm. Birkaç fikir denedim ve hiçbiri işe yaramadı. Bu yüzden yardım için Dynamic Arrays ustası Joe McDaid'e gittim. Cevap oldukça havalı ve unutacağımdan eminim, bu yüzden onu sizin ve benim için belgeliyorum. Eminim, bundan iki yıl sonra bunu nasıl yapacağımı Google'da göreceğim ve "Ah, bak! Bununla ilgili makaleyi yazan benim!"

UNIQUE işlevine geçmeden önce, ne yapmaya çalıştığıma bir bakın. Satış Temsilcisinin B sütunundan ve Ürün C sütunundan her benzersiz kombinasyonunu istiyorum. Normalde şu adımları izlerdim:

Başlıkları B1 ve D1'den çalışma sayfasının boş bir bölümüne kopyalayın
B1'den Veri, Filtre, Gelişmiş'i seçin
Gelişmiş Filtre iletişim kutusunda Yeni Bir Konuma Kopyala'yı seçin
Çıktı aralığı olarak Adım 1'deki başlıkları belirtin
Yalnızca Benzersiz Değerler kutusunu seçin
Tamam'ı tıklayın

İki başlığı çıktı aralığı olan boş bir bölüme kopyalayın

Sonuç, iki alanın her benzersiz kombinasyonudur. Gelişmiş Filtrenin öğeleri sıralamadığını, orijinal sırada göründüklerini unutmayın.

Benzersiz bir liste almak, Gelişmiş Filtre için en sevdiğim kullanımlardan biridir

Bu işlem, Şerit'in Veri sekmesindeki Yinelenenleri Kaldır komutu sayesinde Excel 2010'da daha kolay hale geldi. Bu adımları takip et:

Kopyalamak için B1: D227 ve Ctrl + C'yi seçin
Çalışma sayfasının boş bir bölümüne yapıştırın.

Yinelenenleri Kaldır yıkıcı olduğundan verilerin bir kopyasını oluşturun
Verileri Seçin, Kopyaları Kaldırın
Yinelenenleri Kaldır iletişim kutusunda Tarih'in seçimini kaldırın. Bu, Excel'e yalnızca Temsilci ve Ürün'e bakmasını söyler.
Tamam'ı tıklayın

Yinelenenleri Kaldır'a yalnızca Rep ve Tarihi dikkate almasını söyleyin

Sonuçlar neredeyse mükemmel - sadece Tarih sütununu silmeniz gerekiyor.

Fazla sütunu silin

Soru: UNIQUE işlevinin yalnızca B ve D sütunlarına bakmasının bir yolu var mı? (Yeni UNIQUE işlevini henüz görmediyseniz, şunu okuyun: Excel'de UNIQUE işlevi.)

İstemek =UNIQUE(B2:D227), aradığımız şey olmayan her benzersiz Rep, Date ve Product kombinasyonunu elde etmenizi sağlar.

Bitişik olmayan iki sütunu UNIQUE işlevine nasıl geçirebiliriz?

Eylül ayında Dinamik Diziler piyasaya sürüldüğünde, artık Ctrl + Shift + Enter formüllerinin karmaşıklığı hakkında endişelenmemize gerek kalmayacağını söylemiştim. Ancak bu sorunu çözmek için Kaldırma denen bir kavram kullanacaksınız. Umarım şimdiye kadar Excel Dynamic Arrays Straight To The Point e-kitabımı indirmişsinizdir. Kaldırma hakkında tam bir açıklama için 31-33. Sayfalara bakın.

Kaldırma işleminin tam açıklaması için kitabıma bakın (ve daha sonra sonuçları sıralamaya gittiğinizde, Çift Yönlü Kaldırma)

Tek bir değer bekleyen bir Excel işlevi alın. Örneğin, =CHOOSE(Z1,"Apple","Banana")Z1'in 1 (Apple için) veya 2 (Muz için) içermesine bağlı olarak Apple veya Muz döndürür. CHOOSE işlevi, ilk bağımsız değişken olarak bir skaler bekliyor.

Ama bunun yerine, CHOOSE'a ilk argüman olarak bir dizi sabiti (1,2) geçireceksiniz. Excel, Kaldırma işlemini gerçekleştirecek ve CHOOSE'u iki kez hesaplayacaktır. 1 değeri için, satış temsilcilerinin B2: B227'de olmasını istersiniz. 2 değeri için, ürünleri D2: D227 istersiniz.

SEÇİN'e iki yanıt döndürmesini söyleyin

Normalde, eski Excel'de örtük kesişim sonuçları altüst ederdi. Ancak Excel artık sonuçları birçok hücreye yayabildiğine göre, yukarıdaki formül B ve D'deki tüm yanıtların bir dizisini başarıyla döndürür:

Başarılı! Buradan her şey yokuş aşağı

Makalenin geri kalanını yazmak için zekanıza hakaret ediyormuşum gibi hissediyorum, çünkü buradan itibaren çok basit.

Önceki ekran görüntüsündeki formülü UNIQUE ile sarın ve yalnızca Satış Temsilcisi ve Ürünün benzersiz kombinasyonlarını kullanın =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Hala sıralanmadı

Anlayışınızı kontrol etmek için, üç sütunun tüm benzersiz kombinasyonlarını döndürmek için yukarıdaki formülü değiştirmeyi deneyin: Satış Temsilcisi, Ürün, Renk.

İlk olarak, (1,2,3) 'e başvurmak için dizi sabitini değiştirin.

Ardından, E2: E227'den rengi döndürmek için CHOOSE'a dördüncü bir bağımsız değişken ekleyin =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Üç sütunun benzersiz kombinasyonunu döndür

Bu sonuçları sıralamak güzel olurdu, bu nedenle SORT ve SORTBY kullanarak bir formülle Sırala'ya dönüyoruz.

Normalde, ilk sütuna göre artan sıralama işlevi =SORT(Array)veya olacaktır =SORT(Array,1,1).

Üç sütuna göre sıralamak için, bazı ikili kaldırma işlemleri yapmanız gerekir =SORT(Array,(1,2,3),(1,1,1)). Bu formülde, SIRALA'nın ikinci bağımsız değişkenine geldiğinizde, Excel hangi sütuna göre sıralayacağını bilmek ister. Tek bir değer yerine, bir dizi sabiti içinde üç sütun gönderin: (1,2,3). Artan için 1 veya Azalan için -1 belirlediğiniz üçüncü bağımsız değişkene geldiğinizde, Artan, Artan ve Artan'ı belirtmek için üç adet 1'li bir dizi sabiti gönderin. Aşağıdaki ekran görüntüsü gösterilmektedir =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

İkili kaldırma hakkında daha fazla bilgi için, Doğrudan Noktaya Doğru Excel Dinamik Dizileri'nin 34. sayfasına bakın.

En azından 2018'in sonuna kadar Excel Dinamik Diziler kitabını bu sayfanın altındaki bağlantıyı kullanarak ücretsiz olarak indirebilirsiniz.

Bugünün sorunun cevabının biraz karmaşık olduğunu bulmam için cesaretlendirildim. Dinamik Diziler çıktığında, Aladin Akyürek ve diğerleri tarafından Mesaj Panosunda yayınlanan tüm harika formülleri ve bu formüllerin yeni Excel'de nasıl çok daha basit hale geleceğini anında düşündüm. Ancak bugünün örneği, Dinamik Dizileri kullanmanın yeni yollarını üretmek için formül dahilerine ihtiyaç duyulacağını gösteriyor.