Dizi Formülleriyle Dinamik Hesaplamalar Yapma

Dizi Formülleriyle Dinamik Hesaplamalar Yapma, diziler ile birden fazla kritere bağlı 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. Ayrıca, 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:

=TOPLA((Koşul1)*(Koşul2))

TOPLA.ÇARPIM ise aynı mantığı daha okunabilir ve dinamik şekilde sağlanmaktadır:

=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:

=TOPLA((Koşul1)+(Koşul2)-(Koşul1)*(Koşul2))

TOPLA.ÇARPIM ile aynı işlem gerçekleştirilir, ayrıca daha doğru veri işleme imkanı sağlamaktadır.

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:

=TOPLA((MOD((Koşul1)+(Koşul2);2)))

TOPLA.ÇARPIM ile dizi formülü olarak eşdeğeri kullanılmaktadır.

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:

=TOPLA((Koşul1<>TRUE)+(Koşul2<>TRUE)=2)

TOPLA.ÇARPIM formülü ile koşulların tümü sağlanmadığında değer elde edilmektedir.

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ı

=TOPLA(Miktar;"=15")

veya

=TOPLA.ÇARPIM(--(Miktar=F2))

Bu formül, belirli bir sayının (15 gibi) kaç defa geçtiğini ve toplam etkisini hesaplamaktadır.

• TOPLA ile doğrudan metinsel kriter kullanılabilmektedir.
• TOPLA.ÇARPIM çıplak dizi mantığıyla saymaktadıır.

EĞERSAY İle Tek Koşullu Sayma

=EĞERSAY(Miktar;"=15")

Tek bir şarta göre kaç adet olduğu belirlenmek isteniyorsa, bu formül kullanılabilir. TOPLA.ÇARPIM alternatifi daha esnektir.

Dinamik Değerle Sayma ve Toplama

=TOPLA((Miktar=F2)*F2)

Kullanıcıdan gelen bir referans değer (F2 gibi) ile eşleşme sağlayan satırları saymak veya toplamak için kullanılmaktadır.

• MOD, — gibi yapılarla sayısal mantığı desteklemektedir.

Koşullu Ortalama Alma

=ORTALAMA(EĞER(Miktar=15;Miktar;""))

Koşul sağlayan satırların ortalaması hesaplanır. Bu formül, dizi yapısında çalışmakta ve klasik ORTALAMA fonksiyonundan daha esnek bir çözümdür.

Dizi Formülleriyle Dinamik Hesaplamalar Yapma – Birden Fazla Koşulla Sayma ve Toplama

=TOPLA.ÇARPIM((Ad="Ali")*(Ürün="Fasulye")*Miktar)

Bu bölümde çoklu mantıksal koşullarla filtreleme yapılıp sadece uygun olan satırların değerleri toplanmaktadır.

• 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

=MAK(EĞER(Ad="Ali";Miktar))

veya

=MİN(EĞER(Ad="Ali";Miktar))

Bu formüllerde koşulu sağlayan satırlar arasından maksimum ya da minimum değerler seçilmektedir.

• Veri temizliği sağlanarak yalnızca istenen değerler üzerinde analiz yapılabilir.

Bu sayfa, temel dizi fonksiyonlarının klasik Excel fonksiyonlarıyla birlikte nasıl öğretici şekilde kullanılabileceğini net biçimde göstermektedir.

Dizi Formülleriyle Dinamik Hesaplamalar Yapma – 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

=TOPLA(C2:C10)

formülü, aralıkta hata (#SAYI/0! gibi) varsa çalışmaz. Ancak, dizi formülü kullanılarak hatalar atlanabilmektedir:

{=TOPLA(EĞER(EHATALIYSA(C2:C10);0;C2:C10))}

Bu yapı, her hücreyi kontrol ederek yalnızca hatalı olmayanları toplamaktadır.

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:

{=ORTALAMA(EĞER(EHATALIYSA(C2:C10);YANLIŞ;EĞER(C2:C10="";YANLIŞ;C2:C10)))}

Bu formül hem hata içeren hem de boş hücreleri dışlar ve güvenilir bir ortalama hesaplamaktadır.

Ortalama Hesaplamasında Sıfırları Hariç Tutmak

Hücrelerde sıfır varsa ve bu değerler ortalamaya dahil edilmek istenmiyorsa:

=ORTALAMA(EĞER(D2:D10<>0;D2:D10;YANLIŞ))

Bu formül yalnızca sıfırdan farklı değerleri kullanarak ortalama almaktadır.

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:

=KAYDIR(A1;KAÇINCI(MAK(UZUNLUK(A1:A10));UZUNLUK(A1:A10);0)-1;0;1;1)

Bu formül, tüm metinlerin karakter uzunluğunu değerlendirerek en uzun olanı tespit etmektedir.

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:

{=VE(A1:B1=K1)}

Bu formül, satırdaki tüm hücrelerin eşit olması durumunda DOĞRU, aksi halde YANLIŞ sonucunu döndürmektedir.

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:

{=VE(A2=B2:K2)}

{=VE(A3=B3:K3)}

ve bu şekilde devam eder. Bu yapılar satırlardaki tüm hücrelerin eşitliğini kontrol etmekte kullanılmaktadır.

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.

Dizi Formülleriyle Dinamik Hesaplamalar Yapma – 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:

{=TOPLA(EĞER(B3:B11=C3:C11;1;0))}

Bu formül, her bir satırda B sütunu ile C sütunundaki hücrelerin eşitliğini kontrol etmektedir. Eğer eşitlik varsa 1, değilse 0 döner. Sonuçta eşitlik bulunan hücre çiftlerinin toplam sayısı hesaplanmış olmaktadır. Bu yöntem, özellikle sınav cevapları, veri karşılaştırmaları ya da kullanıcı girişlerinin doğruluğunun kontrolü gibi senaryolarda oldukça pratik ve kullanışlıdır.

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:

{=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))}

{=TOPLA(1/EĞERSAY(B3:B8;B3:B8))}

{=TOPLA.ÇARPIM(--(1/EĞERSAY(B3:B8;B3:B8)))}

Bu formüller EĞERSAY fonksiyonu ile her değerin kaç kez geçtiğini belirler. Daha sonra 1 ile bölünerek benzersiz olanlar tespit edilmektedir.

Tekrar Etmeyen (Sadece 1 Kere Geçen) Değerlerin Sayısı

{=TOPLA(EĞER(EĞERSAY($B$3:$B$8;$B$3:$B$8)=1;1;0))}

{=TOPLA.ÇARPIM(--(EĞERSAY($B$3:$B$8;$B$3:$B$8)=1))}

Bu formüller, yalnızca bir kez görülen değerleri saymak için kullanılmaktadır. Özellikle liste içi analizlerde hangi değerlerin yalnızca bir kez geçtiğini bulmak için oldukça faydalıdır.

İ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:

=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))

Bu formülün işleyişi şu şekilde özetlenmektedir:

• 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.

Sonuç olarak, bu yapı sayesinde iki liste arasındaki benzersiz toplam öğe sayısına ulaşılmaktadır. çünkü, liste karşılaştırmaları, veri uyuşmazlıklarını tespit etmek için oldukça kullanışlıdı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:

=TOPLA(1/(EĞERSAY(Tablo1;Tablo1)+EĞERSAY(Tablo2;Tablo1)))+TOPLA(1/(EĞERSAY(Tablo2;Tablo2)+EĞERSAY($A$2:$D$4;Tablo2)))

Formülün işleyişi şu şekilde açıklanabilir:

• Her iki tablo içerisindeki benzersiz elemanlar tekil olarak sayılmaktadır. Bu yaklaşım sayesinde, tekrarlanan kayıtlar elenmekte ve sadece gerçek çeşitlilik üzerinden analiz yapılmaktadır.
• EĞERSAY fonksiyonu ile aynı tabloda tekrar eden veriler kontrol edilmektedir. Bu, bir veri setindeki yinelenen öğeleri hızlıca belirlemenin ve saymanın en yaygın yollarından biridir. Bu sayede, benzersiz kayıtları bulmak veya veri temizliği yapmak için temel bir adım atılmış olmaktadır.
• İki tablo arasında birbirini karşılamayan yani yalnızca bir tabloda yer alan hücreler hesaplanmaktadır. Bu işlem, genellikle iki veri kümesi arasındaki farklılıkları ortaya çıkarmak ve eksik bilgileri tespit etmek için hayati öneme sahiptir.
• TOPLA fonksiyonu ile toplam farklı hücre sayısı döndürülmektedir. Bu işlem sonucunda elde edilen farklı hücre sayıları, TOPLA fonksiyonu ile bir araya getirilerek toplam farklı hücre sayısı döndürülmektedir. Bu yöntem, her iki veri setinin karşılaştırmalı analizinde eksik veya benzersiz verilerin genel bir toplamını sunmaktadır.

Bu yöntemle, veri tutarlılığı kontrollerinde farklılıkların hızlıca tespit edilmesi mümkün olmaktadır. Bu sayede, büyük veri setlerinde oluşması muhtemel hatalar ve eksiklikler kolayca saptanmaktadır. Böylece, veri kalitesi arttırılmaktadır.

İ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:

=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))

Formülün işleyişi şu şekildedir:

• EĞERSAY fonksiyonu ile her değerin her iki tabloda tekrar edilip edilmediği kontrol edilmektedir. Bu, iki tablo arasındaki ortak verileri tespit etmek ve hangi verilerin benzersiz olduğunu belirlemek için kullanılan temel bir yöntemdir.
• Eğer toplam tekrar sayısı 1 ise, bu değer sadece bir tabloda yer almaktadır. Bu durum, verilerin hangi tabloya özgü olduğunu belirleyerek, iki veri kümesi arasındaki farklılıkları saptamamızı sağlamaktadır.
• TOPLA fonksiyonu ile bu tekrarsız öğeler toplanmaktadır. Böylece sonuç verilmektedir. Bu sayede, her iki tabloda da benzersiz olan kayıtların toplam sayısına hızlıca ulaşılmaktadır.

Bu yöntem, farklı kaynaklardan gelen veri tablolarındaki eşsiz girdileri bulmaya yarayan ideal bir yaklaşımdır. Bu sayede, veri setlerini birleştirmeden önce tutarsızlıkları ve eksik bilgileri tespit etmek kolaylaşmaktadır. Özellikle farklı departmanlardan veya sistemlerden gelen müşteri listeleri gibi tabloları karşılaştırırken, yinelenen ya da kayıp kayıtları hızla ortaya çıkmaktadır. Sonuç olarak, bu yöntem, veri bütünlüğünü ve doğruluğunu sağlamak için kritik bir rol oynamaktadır.

Dizi Formülleriyle Dinamik Hesaplamalar Yapma 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 önemlidir. Çünkü, hatalı verileri yok saymak veya sadece tekrarsız öğeleri bulmak gibi işlemler için güçlü bir temel oluşturulmaktadır. Bu yöntemler, veri analizi yapan herkesin işini ciddi anlamda kolaylaştıracaktır.

Bu sayede, birden çok kaynaktan gelen verileri tek bir tabloda birleştirmeye gerek kalmadan, doğrudan karşılaştırmak mümkün olmaktadır. Bu yaklaşım, özellikle büyük veri setlerinde manuel işlem yapmaktan kaynaklanan zaman kaybını ve hata riskini önemli ölçüde azaltmaktadır. Örneğin, KAÇINCI ve EĞERSAY gibi fonksiyonlar, bir değerin başka bir listede olup olmadığını saniyeler içinde kontrol ederek, eşleşmeyen kayıtları anında bulmanızı sağlamaktadır. Böylece, sadece verilerin doğruluğundan emin olmakla kalmaz, aynı zamanda bu doğru veriler üzerinden daha bilinçli ve hızlı kararlar alabilirsiniz. Kısacası, dizi formülleri, Excel’i basit bir hesaplama aracından, dinamik ve esnek bir veri analiz platformuna dönüştürmektedir.

İçerikten yararlanılması temennisiyle.