Dizi Formülleriyle Dinamik Hesaplamalar Yapma, diziler ile birden fazla kritere bağlı olarak işlemlerin yapılmasını ele alan bir dosya içermektedir.
Excel'in Dizi Formülleriyle Dinamik Hesaplamalar Yapma
Excel’in güçlü dizi (array) formülleri sayesinde, dinamik ve çok yönlü hesaplamalar yapılabilmektedir. Bu dosyada yer alan örnekler ile, klasik hücre işlemlerinin ötesine geçilerek, Excel'in yeni nesil formül yapılarıyla nasıl hızlı ve etkili çözümler üretilebileceği gösterilmektedir. Her sayfa farklı bir uygulamayı içermekte olup, dinamik veri analizi, filtreleme, sıralama, koşullu hesaplama gibi senaryolar ele alınmıştır.TOPLA ve TOPLA.ÇARPIM Fonksiyonlarındaki Dizi Mantığını Öğrenme
Dosyanın ilk sayfasında, mantıksal karşılaştırmalara dayalı olarak dizi formülleriyle nasıl veri analizi yapılabileceği gösterilmektedir. TOPLA ve TOPLA.ÇARPIM fonksiyonları, hem geleneksel hem de modern Excel sürümlerinde aynı amaca hizmet eden ancak yazım stilleri farklı olan yöntemlerle kullanılmıştır. Aşağıda her mantıksal yapının nasıl çalıştığı detaylı olarak açıklanmıştır.Mantıksal VE (AND) ile Koşullu Sayım
Bu bölümde, iki koşulun aynı anda sağlandığı satırların kaç adet olduğunu hesaplayan formüller yer almaktadır. TOPLA fonksiyonu içinde dizi oluşturarak VE mantığı sağlanmaktadır:
Kod:
=TOPLA((Koşul1)*(Koşul2))
Kod:
=TOPLA.ÇARPIM((Koşul1)*(Koşul2))Mantıksal YADA (OR) ile Koşullu Sayım
Bu kısımda, iki koşuldan en az birini sağlayan satırların adedi hesaplanmaktadır. Dizisel toplama yapılırken çakışan (ikisi de doğru) satırlar tekrar sayılmaması için çıkarılmaktadır:
Kod:
=TOPLA((Koşul1)+(Koşul2)-(Koşul1)*(Koşul2))Mantıksal ÖZEL VEYA (XOR) ile Sayım
Bu bölümde, yalnızca tek bir koşulu sağlayan satırların sayısı alınmaktadır. Her iki koşulun aynı anda sağlandığı durumlar hariç tutulmaktadır. MOD fonksiyonu sayesinde toplamın tek olması kontrol edilerek özel durumlar ayrıştırılmaktadır:
Kod:
=TOPLA((MOD((Koşul1)+(Koşul2);2)))Mantıksal VE DEĞİL (NAND) ile Sayım
Burada, her iki koşulu aynı anda sağlamayan satırların adedi hesaplanmaktadır. Bu işlem, hem koşul1 hem koşul2 sağlanmadığında “1” değeri dönmektedir:
Kod:
=TOPLA((Koşul1<>TRUE)+(Koşul2<>TRUE)=2)Koşullu Toplam Hesaplamaları (TOPLA ve TOPLA.ÇARPIM Farkı)
Her bölümde aynı işlem hem klasik TOPLA hem de TOPLA.ÇARPIM ile yapılmıştır. TOPLA genellikle Ctrl+Shift+Enter ile girilen geleneksel dizi formülü gerektirirken, TOPLA.ÇARPIM daha modern ve sade yazım ile tek adımda hesaplama yapmaktadır. İki fonksiyon da aynı sonuca ulaşabilir ancak TOPLA.ÇARPIM çoğu durumda daha güvenlidir.Dizi Formülleriyle Koşullu Toplama
Bu sayfa, Excel'de TOPLA, TOPLA.ÇARPIM, EĞERSAY gibi fonksiyonlarla dizi mantığı kullanılarak nasıl farklı hesaplamalar yapılabileceğini öğretmektedir. Formüller, klasik toplama işlemlerinin ötesine geçerek, mantıksal testlere dayalı olarak çalışmakta ve daha esnek analizler sunmaktadır.Belirli Bir Değerin Sayılması ve Toplanması
Kod:
=TOPLA(Miktar;"=15")
Kod:
=TOPLA.ÇARPIM(--(Miktar=F2))- TOPLA ile doğrudan metinsel kriter kullanılabilmektedir.
- TOPLA.ÇARPIM çıplak dizi mantığıyla saymaktadıır.
EĞERSAY İle Tek Koşullu Sayma
Kod:
=EĞERSAY(Miktar;"=15")Dinamik Değerle Sayma ve Toplama
Kod:
=TOPLA((Miktar=F2)*F2)- MOD, -- gibi yapılarla sayısal mantığı desteklemektedir.
Koşullu Ortalama Alma
Kod:
=ORTALAMA(EĞER(Miktar=15;Miktar;""))Birden Fazla Koşulla Sayma ve Toplama
Kod:
=TOPLA.ÇARPIM((Ad="Ali")*(Ürün="Fasulye")*Miktar)- EĞer Ad "Ali" ve Ürün "Fasulye" ise Miktar toplanmaktadır.
- Bu tarz formüller, klasik filtreleme ötesine geçerek raporlamaya esneklik sağlanmaktadır.
Koşula Göre Maksimum ve Minimum Değerler
Kod:
=MAK(EĞER(Ad="Ali";Miktar))
Kod:
=MİN(EĞER(Ad="Ali";Miktar))- Veri temizliği sağlanarak yalnızca istenen değerler üzerinde analiz yapılabilir.
Hatalı Hücrelerle Çalışırken Dizi Formüllerinin Gücü
Bazen, Excel’de hatalı hücreler işlem yapılmasını engellemektedir. Dosyanın bu sayfasında, TOPLA, ORTALAMA ve KAYDIR gibi fonksiyonlarla hatalı hücreleri göz ardı ederek nasıl doğru sonuçlar alınabileceği gösterilmektedir.Hatalı Hücreleri Göz Ardı Ederek Toplama Yapmak
Kod:
=TOPLA(C2:C10)
Kod:
{=TOPLA(EĞER(EHATALIYSA(C2:C10);0;C2:C10))}Ortalama Hesaplamasında Hataları Göz Ardı Etmek
Hatalı hücrelerin ortalama hesaplamasını bozmasını önlemek için şu dizi formülü kullanılabilmektedir:
Kod:
{=ORTALAMA(EĞER(EHATALIYSA(C2:C10);YANLIŞ;EĞER(C2:C10="";YANLIŞ;C2:C10)))}Ortalama Hesaplamasında Sıfırları Hariç Tutmak
Hücrelerde sıfır varsa ve bu değerler ortalamaya dahil edilmek istenmiyorsa:
Kod:
=ORTALAMA(EĞER(D2:D10<>0;D2:D10;YANLIŞ))Hücre Aralığında En Uzun Metni Bulmak
Bir hücre aralığında en uzun karakter sayısına sahip metni bulmak için şu dizi yapısı kullanılmaktadır:
Kod:
=KAYDIR(A1;KAÇINCI(MAK(UZUNLUK(A1:A10));UZUNLUK(A1:A10);0)-1;0;1;1)Satırlarda Dizi Karşılaştırması ile Birebir Eşitlik Kontrolü
Bu sayfa, Excel’in dizi formülleriyle satır bazında birebir karşılaştırma yaparak eşitlik durumlarının nasıl test edileceğini göstermektedir. Karşılaştırmalar VE (AND) mantıksal işlemi ile yapılmıştır.Satır Satır Eşitlik Kontrolü
A1:K1 ile B1:K1 arası gibi iki satırın birebir eşit olup olmadığını kontrol etmek için aşağıdaki yapı kullanılmıştır:
Kod:
{=VE(A1:B1=K1)}Farklı Satırlarda Aynı Sütunlara Karşılaştırma
Her satır için ayrı ayrı yapılan kıyaslamalar, VE fonksiyonu ile dizi yapısında şu şekilde kurulmuştur:
Kod:
{=VE(A2=B2:K2)}
Kod:
{=VE(A3=B3:K3)}Tüm Satır Eşitliği için Sistematik Kontrol
Bu karşılaştırmalar, satır satır diziler arasında tam uyum olup olmadığını saptamak için etkili bir yöntemdir. Özellikle veri doğrulama, sınav cevap anahtarı karşılaştırması gibi işlemlerde fayda sağlamaktadır.Karşılıklı Hücrelerde Koşullu Eşitlik Sayımı
Bu sayfada, iki ayrı sütundaki hücrelerin aynı satırda birebir eşit olup olmadığını kontrol ederek eşit olan hücre sayısının nasıl hesaplanacağı öğretilmektedir.Dizi Formülü ile Hücre Eşitliği Sayımı
Kullanılan formül:
Kod:
{=TOPLA(EĞER(B3:B11=C3:C11;1;0))}Tekrar Eden ve Tekil Değer Sayımı
Bu sayfa, bir liste içerisinde kaç farklı değerin tekrar ettiğini veya yalnızca bir kez geçtiğini hesaplamayı öğretmektedir. Kullanıcı, dizi formülleriyle benzersiz ve tekil verileri tespit etmektedir.Benzersiz Değerlerin Sayısı (Tekrar Edilenler Hariç)
Aşağıdaki formüller, listedeki kaç benzersiz değerin bulunduğunu göstermektedir:
Kod:
{=TOPLA(EĞER(KAÇINCI($B$3:$B$8;$B$3:$B$8;0)=(SATIR($B$3:$B$8)-MIN(SATIR($B$3:$B$8))+1);1;0))}
Kod:
{=TOPLA(1/EĞERSAY(B3:B8;B3:B8))}
Kod:
{=TOPLA.ÇARPIM(--(1/EĞERSAY(B3:B8;B3:B8)))}Tekrar Etmeyen (Sadece 1 Kere Geçen) Değerlerin Sayısı
Kod:
{=TOPLA(EĞER(EĞERSAY($B$3:$B$8;$B$3:$B$8)=1;1;0))}
Kod:
{=TOPLA.ÇARPIM(--(EĞERSAY($B$3:$B$8;$B$3:$B$8)=1))}İki Sütun Arasındaki Benzersiz Değerlerin Sayısı
Bu sayfa, iki farklı listede bulunan benzersiz öğeleri analiz etmek için hazırlanmıştır. Amacımız, bir listede olup diğerinde olmayan tüm değerleri tespit etmek ve toplam kaç adet benzersiz eleman bulunduğunu bulmaktır.İki Liste Arasında Benzemeyen Verilerin Sayımı
Aşağıdaki dizi formülü, Liste1 ve Liste2 adındaki iki farklı aralıkta yer alan ve karşılıklı olarak benzeme göstermeyen öğeleri sayar:
Kod:
=TOPLA(
EĞER(KAÇINCI(liste1;liste1;0)>=(SATIR(liste1)-MIN(SATIR(liste1))+1);1;0))+
TOPLA(
EĞER(EHATALIYSA(KAÇINCI(liste2;liste1;0)>=(SATIR(liste2)-MIN(SATIR(liste2))+1));
EĞER(KAÇINCI(liste2;liste2;0)>=(SATIR(liste2)-MIN(SATIR(liste2))+1);1;0);0))- KAÇINCI fonksiyonu ile her bir hücrenin listedeki sırası kontrol edilmektedir.
- SATIR fonksiyonuyla her değerin konumu ve benzersizliği değerlendirilmektedir.
- EHATALIYSA fonksiyonu, diğer listede bulunmayan değerleri ayıklamaktadır.
- TOPLA fonksiyonlarıyla her iki sütundaki benzersiz kayıtlar sayılmaktadır.
İki Tablo Arasındaki Farklı Değer Sayısı
Bu içerikte, aynı boyutlardaki iki tablo arasında fark gösteren hücre değerlerinin kaç adet olduğunun tespit edilmesi amaçlanmıştır. Excel’in dizi fonksiyonları kullanılarak tablo karşılaştırması yapılmaktadır.İki Tabloda Farklı Olan Hücre Sayısının Hesaplanması
Aşağıdaki dizi formülü, Tablo1 ve Tablo2'de yer alan verilerin karşılıklı olarak uyuşmayan hücre sayısını verir:
Kod:
=TOPLA(1/(EĞERSAY(Tablo1;Tablo1)+EĞERSAY(Tablo2;Tablo1)))+TOPLA(1/(EĞERSAY(Tablo2;Tablo2)+EĞERSAY($A$2:$D$4;Tablo2)))- Her iki tablo içerisindeki benzersiz elemanlar tekil olarak sayılmaktadır.
- EĞERSAY fonksiyonu ile aynı tabloda tekrar eden veriler kontrol edilmektedir.
- İki tablo arasında birbirini karşılamayan yani yalnızca bir tabloda yer alan hücreler hesaplanmaktadır.
- TOPLA fonksiyonu ile toplam farklı hücre sayısı döndürülmektedir.
İki Tabloda Tekrarlanmayan Değerlerin Sayısı
Bu içerikte, iki ayrı tabloda yer alan ve yalnızca bir tabloda bulunan yani eşsiz (tekrarlanmayan) değerlerin tespiti yapılmaktadır. Benzersiz öğelerin toplam sayısını öğrenmek için dizi formüllerinden yararlanılmıştır.Tekil Değerlerin Sayılması
Aşağıdaki dizi formülü ile yalnızca bir tabloda bulunan değerlerin sayısı hesaplanır:
Kod:
=TOPLA(EĞER((EĞERSAY(Tablo1;$A$2:$D$3)+EĞERSAY(Tablo2;$A$2:$D$3))=1;1;0)) +
TOPLA(EĞER((EĞERSAY(Tablo2;Tablo2)+EĞERSAY($A$2:$D$3;Tablo2))=1;1;0))- EĞERSAY fonksiyonu ile her değerin her iki tabloda tekrar edilip edilmediği kontrol edilir.
- Eğer toplam tekrar sayısı 1 ise, bu değer sadece bir tabloda yer alıyor demektir.
- TOPLA fonksiyonu ile bu tekrarsız öğeler toplanır ve sonuç verilir.
Sonuç
Bu içerikte, dizi formülleri kullanılarak Excel’de veri karşılaştırma, filtreleme ve koşullu toplama işlemlerinin nasıl yapılacağı detaylı şekilde gösterilmiştir. Özellikle TOPLA, TOPLA.ÇARPIM, EĞERSAY, KAÇINCI, EHATALIYSA gibi fonksiyonların birlikte nasıl kullanılacağı örneklerle açıklanmıştır. Farklı veri kümeleri arasında benzerlik veya fark analizleri yapmak, hatalı verileri yok saymak veya sadece tekrarsız öğeleri bulmak gibi işlemler için güçlü bir temel oluşturulmuştur. Bu yöntemler, veri analizi yapan herkesin işini ciddi anlamda kolaylaştıracaktır.İçerikten yararlanılması temennisiyle.