Genel formül
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Özet
Verileri hem sayı hem de metinle alfabetik sırayla dinamik olarak sıralamak için, bir yardımcı sütunda sayısal bir sıra oluşturmak için bir formül kullanabilir, ardından sıraya göre değerleri görüntülemek için DİZİN ve KAÇINCI'yı kullanabilirsiniz. Gösterilen örnekte C5'teki formül şöyledir:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
burada "veri", B5: B13 olarak adlandırılan aralıktır.
Açıklama
Bu formül ilk olarak EĞERSAY'a dayalı bir ifade kullanarak bir sıra değeri oluşturur:
=COUNTIF(data,"<="&B5)
Burada daha ayrıntılı olarak açıklanmaktadır. Veriler tüm metin değerlerini veya tüm sayısal değerleri içeriyorsa, sıra doğru olacaktır. Bununla birlikte, veriler hem metin hem de sayı içeriyorsa, sayısal değerleri hesaba katmak için tüm metin değerlerinin sırasını "kaydırmamız" gerekir. Bu, formülün ikinci kısmı ile yapılır:
+(COUNT(data)*ISTEXT(B7))
Burada, verilerdeki sayısal değerlerin sayısını elde etmek için COUNT işlevini kullanıyoruz, ardından sonucu, değerin metin olup olmadığını test eden ve DOĞRU veya YANLIŞ döndüren ISTEXT mantıksal sonucuyla çarpıyoruz. Bu, geçerli satırdaki bir sayı ile çalışırken COUNT sonucunu etkili bir şekilde iptal eder.
Yinelenenleri işleme
Veriler kopyalar içeriyorsa formül, birden fazla görünen değerlere sıralı bir sıra atamak için aşağıda gösterildiği gibi değiştirilebilir:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Bu sürüm, ilk COUNTIF işlevinin mantığını ayarlar ve yinelenenleri artırmak için genişleyen bir referansla başka bir COUNTIF ekler.
Sıralanmış değerleri görüntüle
Hesaplanan sıra değerini kullanarak sıralı değerleri alfabetik sırada almak ve görüntülemek için, E5 aşağıdaki INDEX ve MATCH formülünü içerir:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
burada "veri", B5: B13 olarak adlandırılmış aralıktır ve "sıra", C5: C13 olarak adlandırılmış aralıktır.
Bu formülün nasıl çalıştığı hakkında daha fazla bilgi için buradaki örneğe bakın.
Boşluklarla uğraşmak
Boş hücreler, sıfır derecesini oluşturacaktır. Boş hücreleri yok saymak istediğinizi varsayarsak, yukarıdaki DİZİN ve KAÇINCI formülü 1'den başladığından bu iyi çalışır. Ancak, sıralanan değerlerin sonunda, her boş hücre için bir tane olmak üzere #YOK hataları görürsünüz. Bunu halletmenin kolay bir yolu, İNDİS ve KAÇINCI formülünü EĞERHATA'ya şu şekilde kaydırmaktır:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
