EXCEL FORMÜLLERİ
Merhaba arkadaşlar.Bugünkü yazımda sizlere Excel formülleri hakkında bir kaç kısa bilgi vereceğim..
Ortalama Formülü
Bir dizi Excel değerlerinin ortalamasını almak için = ORTALAMA formulünü kullanabilirsiniz. Bunu kullanarak
diğer fonksiyonları ile, onun işlevselliğini genişletebilirsiniz.
Aşağıda verilen formüller için, bizim veri aralığında A1 olduğunu varsayalım: A60.
İki Sayılar arasında ortalama değerler
= ORTALAMA (EĞER ((A1: A60> = Düşük) * (A1: A60 <= Yüksek), A1: A60))
Alçak ve Yüksek size ortalama istediğiniz arasındaki değerlerin nerede olduğunu gösterir.
Bir Kapsama Alanında En Yüksek Yok Numaraları ortalama
= ORTALAMA (BÜYÜK (A1: A60, SATIR (DOLAYLI ("01:10"))))
N ortalama değerlerin sayısıdır: "N 1" için "01:10" olarak değiştirin.
Bir Kapsama Alanında En Düşük Yok Numaraları ortalama
= ORTALAMA (KÜÇÜK (A1: A60, SATIR (DOLAYLI ("01:10"))))
N ortalama değerlerin sayısıdır: "N 1" için "01:10" olarak değiştirin.
Yukarıdaki formüllerde, her olarak toplamı yerine = ORTALAMA = kullandık.
İki Sayılar arasında Sayma Değerleri
Aranızda örneğin, iki sayı arasında olan bir aralıktaki değerleri saymak gerekirse
= TOPLA ((A1: A10> = 5) * (A1: A10 <= 10))
= TOPLA ((A1: A10> = 5) * (A1: A10 <= 10) * A1: A10)
A String olarak sayma Karakterler
Aşağıdaki formül B1 dize, büyük ve küçük harf hem de "B" nin sayısını olacaktır.
= LEN (B1)-LEN (YEDEK (YEDEK (B1, "B", ""), "b", ""))
Dinamik aralıkları
Kimin boyutu içeriğini bağlı olarak değişen bir dizi başvurmak için bir isim tanımlayabilirsiniz. Örneğin, sadece boş değil numaralarının listesi kısmını başvuran bir aralık adı isteyebilirsiniz. A2 gibi sadece ilk N boş olmayan hücreleri: A20. MyRange adı verilen bir ad tanımlamak ve özellik için gelir ayarlayın:
= OFFSET (Sayfa1! $ A $ 2,0,0, BAĞ_DEĞ_DOLU_SAY ($ A $ 2: $ A $ 20), 1)
Bir Range kullanılmış Bölüm bulma
I25, ve bu: biz veri bir dizi H7 olarak tanımlanan, DataRange2 adı var herhalde
hücreleri H7: I17 aslında değerler içerir. Gerisi boştur. Biz çeşitli özellikleri bulabilirsiniz
aralığı, aşağıdaki gibidir:
= ADRES (SATIR (DataRange2), KOLON (DataRange2), 4) & ":" &
ADRES (MAX ((DataRange2 <> "") * SATIR (DataRange2)), KOLON (DataRange2) +
KOLON (DataRange2) -1,4)
I17: Bu aralık H7 dönecektir.
= ADRES (SATIR (DataRange2), KOLON (DataRange2), 4,, "MySheet") & ":" &
ADRES (MAX ((DataRange2 <> "") * SATIR (DataRange2)), KOLON (DataRange2) +
KOLON (DataRange2) -1,4)
! I17: Bu MySheet H7 dönecektir.
Veri içeren satır sayısını bulmak için, aşağıdaki dizi formülünü kullanın :
= (MAX ((DataRange2 <> "") * SATIR (DataRange2)))-ROW (DataRange2) +1
Bu DataRange2 ilk 11 satır veri içeren belirten, 11 numara dönecektir.
DataRange2 ilk sütununda en son giriş bulmak için, aşağıdaki dizi formülünü kullanın :
= DOLAYLI (ADRES (MAX ((DataRange2 <> "") * SATIR (DataRange2)),
KOLON (DataRange2), 4))
DataRange2, ikinci sütununda en son giriş bulmak için, aşağıdaki kullanın dizi formülü :
= DOLAYLI (ADRES (MAX ((DataRange2 <> "") * SATIR (DataRange2)),
KOLON (DataRange2) +1,4))
İlk Ve Son İsimler
Eğer insanların ilk ve son adları oluşan veri dizisi varsayalım.
Ilk ve son adları içine ayrı isimleri kıracak çeşitli formüller vardır
ayrı ayrı.
A2 hücresi adı "John A Smith" içerir varsayalım.
Soyadı dönmek için kullanın
= SAĞ (A2, UZUNLUK (A2)-BUL ("*", YEDEK (A2, "", "*", LEN (A2) -
UZUNLUK (YEDEK (A2 ",", "")))))
Göbek adı (varsa) dahil olmak üzere ilk isim, dönmek için kullanın
= SOL (A2, ("*", YEDEK (A2, "", "*", LEN (A2) BUL -
LEN (YEDEK (A2, "", "")))) -1)
Göbek adı (varsa) olmadan, ilk isim dönmek için kullanın
= SOL (B2, BUL ("", B2, 1))
A String yılında İlk Word dönen
Bu kelime "İlk" dönecektir.
= SOL (A1, BUL ("", A1, 1))
A String yılında Son Yazı dönen
= SAĞ (A1, LEN (A1)-MAX (SATIR (DOLAYLI ("1:" & LEN (A1))) * (MID (A1, SATIR (DOLAYLI ("1:" & LEN (A1))), 1) = "")))
Dizi formülü olarak bu formül.
(Bu formül Laurent Longre geliyor). Bu kelime "Son" döndürür
A String Hepsi İçinde Ama İlk Yazı dönen
= SAĞ (A1, LEN (A1)-BUL ("", A1, 1))
Bu kelime "İkinci Üçüncü Son döndürür"
A String Herhangi Kelime veya Kelimeler dönen
Aşağıdaki iki dizi formülleri Laurent Longre övgü geldi. Kelimelerin tek aralıklı dizeden tek bir kelime dönmek için aşağıdakileri kullanın dizi formülü:
= MID (A10, KÜÇÜK (EĞER (MID ("" & A10, SATIR (DOLAYLI
("1:" & LEN (A10) +1)), 1) = "", SATIR (DOLAYLI ("1:" & LEN (A10) +1))),
B10), SUM (KÜÇÜK (EĞER (MID ("" & A10 & "", SATIR (DOLAYLI
("1:" & LEN (A10) +2)), 1) = "", SATIR (DOLAYLI ("1:" & LEN (A10) +2))),
B10 + {0,1}) * {-1,1}) -1)
A10 metni içeren hücre ve B10 almak istediğiniz kelime sayısıdır.
Bu formül dizide kelimeleri herhangi bir set almak için uzatılabilir.Kelimeler için M (örneğin, 3 5. kelime, ya da, 5., 6., ve 7. kelime) gelen kelimeleri almak için, aşağıdaki kullanın dizi formülü :
= MID (A10, KÜÇÜK (EĞER (MID ("" & A10, SATIR (DOLAYLI
("1:" & LEN (A10) +1)), 1) = "", SATIR (DOLAYLI ("1:" & LEN (A10) +1))),
B10), SUM (KÜÇÜK (EĞER (MID ("" & A10 & "", SATIR (DOLAYLI
("1:" & LEN (A10) +2)), 1) = "", SATIR (DOLAYLI ("1:" & LEN (A10) +2))),
B10 + C10 * {0,1}) * {-1,1}) -1)
A10 metin hücre olduğu yerlerde, B10 elde etmek için kelime sayısı, ve C10 elde etmek için, B10 başlayan kelimelerin sayısını ifade eder.
Yukarıdaki dizi formüllerinde, {0,1} ve {-1,1} dizisi parantez (süslü parantez {}) değil parantez içine unutmayın.
Sınıflar
Sık sorulan bir soru.Sayısal bir değere bir harfi nasıl atarım?. Bu basit. İlk diziyi ifade eden "Sınıflar" adlı bir tanım adı oluşturun:
= {0, "F", 60, "D", 70, "C", 80, "B", 90, "A"}
Daha sonra, sınıf için sayı dönüştürmek için DÜŞEYARA kullanın:
= DÜŞEYARA (A1, Sınıflar, 2)
A1 hücreyi sayısal bir değer içerir. Sizin gibi diğer sınıflar için Sınıflar diziye girdi ekleyebilirsiniz C-ve C +. Sadece dizideki sayısal değerler artan sırada olduğundan emin olun.
Yüksek ve Düşük Değerler
Şimdiye kadarki en yüksek değeri içeren bir hücre için Excel'in Genelgesi Referans aracını kullanabilirsiniz. Örneğin, takım puanları izlemek için kullanılan bir çalışma olduğunu varsayalım. O puan listeden silinmiş olsa bile, ulaştığı en yüksek puanı içeren bir hücre ayarlayabilirsiniz. A10: skor A1 olduğunu varsayalım. İlk olarak, Araçlar-> Seçenekler iletişim gidin, Hesaplama sekmesini tıklayın ve Interations onay kutusunu işaretleyin. Daha sonra, B1 hücresine aşağıdaki formülü girin:
= MAX (A1: A10, B1)
Bu değer aralığı silinir olsa bile, A10: B1 hücresine hiç A1 mevcut olan en yüksek değer hücresi olacaktır. En düşük hiç değeri elde etmek için = MIN işlevini kullanın.
Döngüsel başvurular kullanmadan, bunu yapmak için başka bir yöntem, Laurent Longre tarafından sağlanan ve Excel4 makro fonksiyon kütüphanesi erişmek için ARA işlevini kullanır.
Sol Lookups
Tablo aramalarını yapmanın en kolay yolu = DÜŞEYARA fonksiyonudur. Bununla birlikte, = DİKEYARA fonksiyonunuda gerektirir
Aradığınız değer kadar aradığınız değerin sağında olan döndü. Örneğin, eğer
Eğer gerekiyorsa B sütununda bir değer ararken, size A sütunundaki değerleri alamıyor
arama değeri içeren sütunun solunda bir sütun bir değer almayı, kullanmayı
ya da aşağıdaki formülleri kullanmayı düşünebilirsiniz :
= DOLAYLI (ADRES (SATIR (Aralığı) + MAÇ (C1, Aralığı, 0) -1, KOLON (Aralığı)-ColsToLeft)) veya
= DOLAYLI (ADRES (SATIR (Aralığı) + MAÇ (C1, Aralığı, 0) -1, KOLON (A: A)))
Aralığı arama değerlerini içeren bir aralık olması durumunda, ve ColsToLeft sütun sayısı
alma değerleri olduğunu RNGnin solunda. İkinci sözdiziminde, "A: A" yerine ile
alma verileri içeren sütun. Her iki örnekte de, C1 size aramak istediğiniz değerdir.
Bir Kapsama Alanında Minimum ve Maksimum Değerler
Tabii ki minimum ve maksimum dönmek için = MIN ve MAX = işlevlerini kullanabilirsiniz
bir dizi değeri. Biz Sayı Aralığı denilen sayısal bir değer aralığı var varsayalım.
Sayı Aralığı yinelenen değerler içerebilir. Aşağıdaki formülleri aşağıdaki örnekte kullanın:
Bir Kapsama Alanında İlk Minimum Of adresi
Bir listenin en az birinci (veya tek) örneği içeren hücre adresini geri dönmek için,
aşağıdakileri kullanın dizi formülü :
= ADRES (MIN (IF (Sayı Aralığı = MIN (Sayı Aralığı), SATIR (Sayı Aralığı))), KOLON (Sayı Aralığı), 4)
Bu fonksiyon, B2, birinci '1 'aralığında bir adresini uygulamaya geri döndürmektedir.
Bir Kapsama Alanında Son Minimum Of adresi
Bir liste en az son (veya tek) örnek içeren hücrenin adresini dönmek için,
aşağıdakileri kullanın dizi formülü :
= ADRES (MAX (IF (Sayı Aralığı = MIN (Sayı Aralığı), SATIR (Sayı Aralığı) * (Sayı Aralığı <> ""))),
KOLON (Sayı Aralığı), 4)
Bu fonksiyon, son '1 'aralığında bir adres B4 döndürür.
Bir Kapsama Alanında İlk Maksimum Of adresi
Bir liste en fazla ilk örneği içeren hücrenin adresini dönmek için,
aşağıdakileri kullanın dizi formülü :
= ADRES (MIN (IF (Sayı Aralığı = MAX (Sayı Aralığı), SATIR (Sayı Aralığı))), KOLON (Sayı Aralığı), 4)
Bu fonksiyon, B1, birinci '5 'aralığında bir adresini uygulamaya geri döndürmektedir.
Bir Kapsama Alanında Son Maksimum Of adresi
Bir listenin en son örneğini içeren hücrenin adresini dönmek için,
aşağıdakileri kullanın dizi formülü :
= ADRES (MAX (IF (Sayı Aralığı = MAX (Sayı Aralığı), SATIR (Sayı Aralığı) * (Sayı Aralığı <> ""))),
KOLON (Sayı Aralığı), 4)
Bu fonksiyon B5, son '5 'aralığında en adresini döndürür.
Bir Kapsama Alanında En Yaygın Dize
Aşağıdaki dizi formülü bir dizi en sık kullanılan giriş döndürür:
= INDEX (Aralığı, KAÇINCI (MAX (EĞERSAY (Aralığı, Aralığı)), EĞERSAY (Aralığı, Aralığı), 0))
Sıralama Numaraları
Çoğu zaman, veri bir dizi en yüksek veya en düşük değerler döndürmek için yararlıdır.
Biz RankRng denilen sayısal veriler bir dizi olduğunu varsayalım. Yanında bir dizi oluşturma
TopRng denilen (satır aynı sayıda, aynı satırda başlayarak) RankRng.
Ayrıca, istediğiniz değerlerin sayısını TOPN adlı bir adlandırılmış hücre oluşturmak ve içine girin
dönüş (örneğin, RankRng en iyi 5 değerleri için 5). Ilk hücresine aşağıdaki formülü girin
TopRng, ve aralık doldurmak için Aşağı doldurun kullanın:
= ((RankRng, SATIR ()-SATIR (TopRng) +1) BÜYÜK, "", SATIR ()-SATIR (TopRng) +1> TOPN) IF
RankRng en TOPN küçük değerler döndürmek için kullanın
= EĞER (SATIR ()-SATIR (TopRng) +1> TOPN, "", KÜÇÜK (RankRng, SATIR ()-SATIR (TopRng) +1))
Değiştirmek gibi bu işlevleri tarafından döndürülen numaraları listesi otomatik olarak değişecek
RankRng veya TOPN içeriği.
Her N. Değerin toplanmasıyla
Kolayca bir sütun aralığında (veya ortalama) her N. hücre özetleyebilirim. Örneğin, her 3 hücre toplamak istediğiniz varsayalım.
Veri A1 olduğunu varsayalım: A20, ve N = 3 D1 bulunmaktadır. Aşağıdaki dizi formülü A3, A6, A9, vb değerleri toplamı olacaktır
= TOPLA (EĞER (MOD (SATIR ($ A $ 1: $ A $ 20), $ D $ 1) = 0, $ A $ 1: $ A $ 20,0))
A1 değerleri toplamak istiyorsanız, A4, A7, vb, aşağıdaki dizi formülü kullanın:
= TOPLA (EĞER (MOD (SATIR ($ A $ 1: $ A 20 $) -1, $ D $ 1) = 0, $ A $ 1: $ A $ 20,0))
Veri aralıkları satır 1 başlamazsa, formüller biraz daha karmaşıktır. Bizim veri B3 olduğunu varsayalım: B22, ve N = 3 D1 bulunmaktadır. Satır 5, 8, 11, vb değerleri toplamak için aşağıdaki kullanın dizi formülü :
= TOPLA (EĞER (MOD (SATIR ($ B $ 3: $ B $ 22)-SATIR ($ B $ 3) +1, $ D $ 1) = 0, $ B $ 3: B $ 22,0))
Eğer satır 3, 6, 9, vb değerleri toplamak istiyorsanız, aşağıdakileri kullanın dizi formülü :
= TOPLA (EĞER (MOD (SATIR ($ B $ 3: $ B $ 22)-SATIR ($ B $ 3), $ D $ 1) = 0, $ B $ 3: B $ 22,0))
Çeşitli Formüller
Levha Adı
\ Files \ Kitabım.xls: bizim etkin sayfa dosya C "MySheet" adlı varsayalım.
Bir hücreye tam sayfa adı (dosya yolu dahil) dönmek için kullanın
= HÜCRE ("dosyaadı", A1)
= HÜCRE işlev için bağımsız değişken kelimesini tırnak içinde "dosya" değil, unutmayın
gerçek dosya.
": \ Files \ [MyBook.xls] MySheet C" Bu dönecektir
Yolu olmadan, sayfa adı dönmek için kullanın
= MID (HÜCRE ("dosyaadı", A1), BUL ("]", HÜCRE ("dosyaadı", A1)) +1,
LEN (HÜCRE ("dosyaadı", A1))-BUL ("]", HÜCRE ("dosyaadı", A1)))
Bu "MySheet" döndürür
Dosya Adı
\ Files \ Kitabım.xls: bizim etkin sayfa dosya C "MySheet" adlı varsayalım.
Yolu olmadan dosya adını döndürmek için kullanın
= MID (HÜCRE ("dosyaadı", A1), (, "[", HÜCRE ("dosyaadı", A1)) +1, (BUL "]" BUL
HÜCRE ("dosyaadı", A1))-BUL ("[", HÜCRE ("dosyaadı", A1)) -1)
Bu "MyBook.xls" döndürür
Yolu ile dosya adını döndürmek için kullanabilirsiniz
= SOL (HÜCRE ("dosyaadı", A1), ("]", HÜCRE ("dosyaadı", A1)) BUL) Veya
= YEDEK (YEDEK (SOL (HÜCRE ("dosyaadı", A1), BUL ("]",
HÜCRE ("dosyaadı", A1))), "[", ""), "]", "")
": \ Files \ [MyBook.xls] C" İlk sözdizimi dönecektir
": \ Files \ MyBook.xls C" İkinci sözdizimi dönecektir
Yukarıdaki örneklerin tümünde, = HÜCRE işlevi güçlerine A1 argüman formülü içeren sayfasından sayfa adını almak için Excel. Başka bir sayfaya aktif olduğunda ve Excel= HÜCRE işlevi hesaplar olmadan, hücrenin etkin sayfa adını, aslında formülü içeren levha içerecektir.
Bugünlük yazımızda bu kadar arkadaşlar.Tekrar görüşmek dileğiyle
Hiçbir geçiş
Yatay jaluzi
Dikey jaluzi
Kutusunda
Box Out
Karşısında Dama
Dama Aşağı
Yatay Tarak
Dikey Tarak
, Yukarıdaki şemada gösterildiği gibi.