Listeyi sıralamanız gerekiyorsa, hizmetinizde birçok yol vardır, bunların en kolayı sekmedeki veya menüdeki sıralama düğmeleridir. Veri (Veri — Sıralama). Ancak liste sıralamasının otomatik olarak yapılması gereken durumlar vardır, yani formüller. Bu, örneğin bir açılır liste için veri oluştururken, çizelgeler için veri hesaplarken vb. gerekli olabilir. Bir liste anında formülle nasıl sıralanır?
Yöntem 1. Sayısal veriler
Liste yalnızca sayısal bilgiler içeriyorsa, sıralama, işlevler kullanılarak kolayca yapılabilir. EN AZ (KÜÇÜK) и LINE (SIRA):
işlev EN AZ (KÜÇÜK) diziden (sütun A) art arda n'inci en küçük öğeyi çıkarır. Şunlar. KÜÇÜK(A:A;1) sütundaki en küçük sayıdır, KÜÇÜK(A:A;2) ikinci en küçük sayıdır, vb.
işlev LINE (SIRA) belirtilen hücre için satır numarasını döndürür, yani SATIR(A1)=1, SATIR(A2)=2 vb. Bu durumda, basitçe n=1,2,3… sıralı listemiz. Aynı başarı ile, ek bir sütun oluşturmak, onu manuel olarak 1,2,3… sayısal dizisiyle doldurmak ve SATIR işlevi yerine ona başvurmak mümkün oldu.
Yöntem 2. Metin listesi ve normal formüller
Liste sayı değil metin içeriyorsa, KÜÇÜK işlevi artık çalışmayacaktır, bu nedenle farklı, biraz daha uzun bir yol izlemeniz gerekir.
İlk olarak, gelecekteki sıralanmış listedeki her bir ismin seri numarasının fonksiyon kullanılarak hesaplanacağı bir formül içeren bir hizmet sütunu ekleyelim. COUNTIF (EĞERSAY):
İngilizce versiyonda şöyle olacak:
=EĞERSAY(A:A,»<"&A1)+EĞERSAY($A$1:A1,"="&A1)
İlk terim, mevcut olandan daha az olan hücre sayısını saymak için bir fonksiyondur. İkincisi, herhangi bir ismin birden fazla olması durumunda bir güvenlik ağıdır. O zaman aynı olmayacak, ancak art arda artan sayılara sahip olacaklar.
Şimdi alınan numaralar artan sırada sırayla düzenlenmelidir. Bunun için işlevi kullanabilirsiniz EN AZ (KÜÇÜK) ilk yoldan:
Son olarak, geriye sadece isimleri numaralarına göre listeden çıkarmak kalıyor. Bunu yapmak için aşağıdaki formülü kullanabilirsiniz:
işlev DAHA MARUZ KALMIŞ (KİBRİT) B sütununda istenen seri numarasını (1, 2, 3 vb.) arar ve aslında bu numaranın bulunduğu satırın numarasını döndürür. İşlev INDEX (İNDEKS) A sütunundan bu satır numarasındaki adı çıkarır.
Yöntem 3: Dizi Formülü
Bu yöntem, aslında, Yöntem-2'deki ile aynı yerleştirme algoritmasıdır, ancak bir dizi formülü ile uygulanır. Formülü basitleştirmek için C1:C10 hücre aralığına isim verildi. Liste (hücreleri seçin, Ctrl + F3 ve düğme oluşturmak):
E1 hücresine formülümüzü kopyalayın:
=INDEX(Liste; MATCH(KÜÇÜK(EĞERSAY(Liste; “<"&Liste); SATIR(1:1)); EĞERSAY(Liste; "<"&Liste); 0))
Veya İngilizce versiyonunda:
=INDEX(Liste, KAÇINCI(KÜÇÜK(EĞERSAY(Liste, «<"&Liste), SATIR(1:1))), EĞERSAY(Liste, "<"&Liste), 0))
ve it Ctrl + Üst Karakter + Enterdizi formülü olarak girmek için. Daha sonra elde edilen formül, listenin tüm uzunluğu boyunca kopyalanabilir.
Formülün sabit bir aralığı hesaba katmamasını, ancak listeye yeni öğeler eklerken ayarlayabilmesini istiyorsanız, stratejiyi biraz değiştirmeniz gerekecektir.
İlk olarak, Liste aralığının dinamik olarak ayarlanması gerekecektir. Bunu yapmak için, oluştururken sabit bir C3:C10 aralığı değil, sayıları ne olursa olsun mevcut tüm değerlere atıfta bulunacak özel bir formül belirtmeniz gerekir. Tıklamak Alt + F3 veya sekmeyi açın Formüller – İsim Yöneticisi (Formüller — Ad Yöneticisi), yeni bir ad oluşturun ve alanda Link (Referans) aşağıdaki formülü girin (sıralanacak veri aralığının C1 hücresinden başladığını varsayıyorum):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=KAYDIRMA(C1,0,0,SCHÖTZ(C1:C1000),1)
İkinci olarak, gelecekte girilen ek verilerin beklentisiyle, yukarıdaki dizi formülünün bir marjla genişletilmesi gerekecektir. Bu durumda dizi formülü henüz doldurulmamış hücrelerde #SAYI hatası vermeye başlayacaktır. Engellemek için işlevi kullanabilirsiniz. EĞERHATAdizi formülümüzün "etrafına" eklenmesi gereken:
=EĞERHATA(DİZİN(Liste; MATCH(KÜÇÜK(EĞERSAY(Liste; “<"&Liste); SATIR(1:1)); EĞERSAY(Liste; "<"&Liste); 0));»»)
=EĞERGÖZ(NDEX(Liste, KAÇINCI(KÜÇÜK(EĞERSAY(Liste, «<"&Liste), SATIR(1:1))), EĞERSAY(Liste, "<"&Liste), 0));"")
#SAYI hatasını yakalar ve bunun yerine bir boşluk (boş tırnak) verir.
:
- Aralığı renge göre sırala
- Dizi formülleri nedir ve neden gereklidir?
- Yeni Office 365'te SIRALAMA sıralama ve dinamik diziler