Excel'de yerleşik veri sıralama harikadır, ancak dinamik değildir. Verileri sıralar ve ardından buna veri eklerseniz, yeniden sıralamanız gerekir.
Verileri Alfabetik Sıraya Göre Sırala
Bu yazıda, formülleri kullanarak verileri alfabetik sıraya göre sıralamanın çeşitli yollarını göstereceğim. Bu, veri ekleyebileceğiniz ve sizin için otomatik olarak sıralayacağı anlamına gelir.
Verilerin Tamamı Yinelenmeyen Metin Olduğunda
Aşağıda gösterildiği gibi bir verileriniz olduğunu varsayalım:
Bu örnekte, tüm veriler metin biçimindedir (sayı, boşluk veya kopya yok). Bunu sıralamak için bir yardımcı sütun kullanacağım. Verilerin yanındaki sütunda aşağıdaki EĞERSAY formülünü kullanın:
=EĞERSAY($A$2:$A$9,"<="&A2)
Bu formül, bir metin değerini diğer tüm metin değerleriyle karşılaştırır ve göreli sıralamasını döndürür. Örneğin, B2 hücresinde, "ABD" metninden (alfabetik sıra) küçük veya ona eşit 8 metin değeri olduğundan 8 döndürür.
Şimdi değerleri sıralamak için aşağıdaki İNDEKS, KAÇINCI ve SATIR işlevleri kombinasyonunu kullanın:
=INDEX($A$2:$A$9,MATCH(SATIR($B$2:B2),$B$2:$B$9,0))
Bu formül, isimleri alfabetik sırayla çıkarır. İlk hücrede (C2), en düşük sayıya sahip ülke adını arar (Avustralya'da 1 vardır). İkinci hücrede, 2 numaralı Kanada'yı döndürür ve böyle devam eder…
Yardımcı Sütunlara Alerjik??
İşte yardımcı sütun olmadan da aynısını yapacak bir formül.
=INDEX($A$2:$A$9,MATCH(SATIR($A$2:A2),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),0))
Bu bir dizi formülüdür, bu nedenle Kontrol + Üst Karakter + Enter Enter yerine.
Kod çözmeniz için size bırakacağım.
Kendiniz Deneyin… Örnek Dosyayı İndirin
Metin veya alfasayısal değerleriniz varsa bu formül işe yarar.
Ancak aşağıdaki durumlarda sefil bir şekilde başarısız olur:
- Verilerde kopyalarınız var (ABD'yi iki kez koymayı deneyin).
- Verilerde boşluklar var.
- Sayı ve metin karışımınız var (hücrelerden birine 123 koymayı deneyin).
Veri Sayılar, Metin, Yinelenenler ve Boşluklardan Oluşan Bir Karma Olduğunda
Şimdi bu biraz zor. Nasıl çalıştığını göstermek için 4 yardımcı sütun kullanacağım (ve sonra size yardımcı sütunlar olmadan yapacak büyük bir formül vereceğim). Aşağıda gösterildiği gibi bir verileriniz olduğunu varsayalım:
Yinelenen değerler, boş ve sayılar olduğunu görebilirsiniz. Bu nedenle, bu sorunların her birini ele almak için yardımcı sütunlar kullanacağım.
Yardımcı Sütun 1
Yardımcı Sütun 1'e aşağıdaki COUNTIF formülünü girin
=EĞERSAY($A$2:$A$9,"<="&A2)
Bu formül şunları yapar:
- Boşluklar için 0 döndürür.
- Yineleme durumunda, aynı numarayı döndürür.
- Metin ve sayılar paralel olarak işlenir ve bu formül metin ve sayı için aynı sayıyı döndürür (örneğin 123 ve Hindistan'ın ikisi de 1 alır).
Yardımcı Sütun 2
Yardımcı Sütun 2'ye aşağıdaki IS İşlevini girin:
=--EKSAYI(A2)
Yardımcı Sütun 3
Yardımcı Sütun 3'e aşağıdaki formülü girin:
=--BOŞ(A2)
Yardımcı Sütun 4
Yardımcı Sütun 4'e aşağıdaki formülü girin
=EĞER(ESAYI(A2),B2,EĞER(BÖLÜM(A2),B2,B2+$C$10))+$D$10
Bu formülün amacı boşlukları, sayıları ve metin değerlerini ayırmaktır.
- Hücre boşsa, B2 hücresindeki (her zaman 0 olur) değeri döndürür ve D10 hücresindeki değeri ekler. Özetle, verilerdeki toplam boş hücre sayısını döndürür.
- Hücre sayısal bir değerse, karşılaştırmalı sıralamayı döndürür ve toplam boşluk sayısını ekler. Örneğin, 123 için 2 döndürür (1, verilerde 123'ün sırasıdır ve 1 boş hücre vardır)
- Metin ise, karşılaştırmalı sıralamayı döndürür ve toplam sayısal değer ve boşluk sayısını ekler. Örneğin, Hindistan için, metnin metindeki karşılaştırmalı sıralamasını (1'dir) ve boş hücrelerin sayısını ve sayısal değerlerin sayısını ekler.
Nihai Sonuç - Sıralanmış Veriler
Şimdi sıralanmış listeyi almak için bu yardımcı sütunları kullanacağız. İşte formül:
=EĞERHATA(INDEX($A$2:$A$9,MATCH(KÜÇÜK($E$2:$E$9,SATIR($F$2:F2)+$D$10),$E$2:$E$9,0)) ,"")
Bu sıralama yöntemi artık kusursuz hale geliyor. Ben size 8 maddelik yöntemi gösterdim ama siz istediğiniz kadar maddeye genişletebilirsiniz.
Kendiniz Deneyin… Örnek Dosyayı İndirin
Hepsini Sıralamak için Tek Bir Formül (Yardımcı Sütunlar olmadan)
Aşırı formülleri işleyebiliyorsanız, verileri alfabetik sırayla (herhangi bir yardımcı sütun olmadan) sıralayacak hepsi bir arada formül burada.
İşte formül:
=EĞERHATA(INDEX($A$2:$A$9,MATCH(KÜÇÜK(NOT($A$2:$A$9=""))*IF(ESNUMBER($A$2:$A$9)),EĞERSAY($A$2: $A$9,"<="&$A$2:$A$9,COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(--ISNUMBER($A) $2:$A$9)), SATIR($A$2:A2)+SUM(--ISBLANK($A$2:$A$9))),NOT($A$2:$A$9="")*EĞER (ISNUMBER($A$2:$A$9),EĞERSAY($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<=" &$A$2:$A$9)+SUM(--ISNUMBER($A$2:$A$9)))),"")
Bu formülü bir hücreye girin ve sıralı listeyi almak için aşağı sürükleyin. Ayrıca, bu bir dizi formülü olduğundan, Kontrol + Üst Karakter + Enter Enter yerine.
Bu formülün gerçek dünya faydası vardır. Ne düşünüyorsun? senden öğrenmeyi çok isterim Ayak izlerinizi yorum kısmına bırakın!
