Excel'de en yakın veya en yakın değeri nasıl bulurum?

Diyelim ki Sütun B’de bir sayı listeniz var ve hangisinin belirlediğiniz bir sayıya (örneğin18) en yakın olduğunu bulmak istiyorsunuz. Excel’de bir dizi formülü kullanmak, listenizi tek tek incelemeden bunu hızlıca belirlemenizi sağlar.

Başlamak için, boş bir hücre seçin ve aşağıdaki formülü girin. Formülü yazdıktan sonra, sadece Enter yerine Ctrl + Shift + Enter tuşlarına basmayı unutmayın. Bu işlem, formülün bir dizi formülü olarak çalışmasını ve doğru şekilde işlev görmesini sağlar:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

Bu yöntem, sürekli bir aralıktan tek bir en yakın sayıyı almak istediğinizde en uygunudur. Özellikle sayısal doğruluk ve tam eşleşmeler önemli olduğunda etkili şekilde çalışır. Ancak, dizi formülleri çok büyük veri kümelerinde kaynak yoğun olabilir. Performans sorunları yaşarsanız veya #DEĞER! gibi hata mesajları alırsanız, hücre başvurularınızı kontrol edin ve Ctrl + Shift + Enter'a doğru bastığınızdan emin olun.

Bazen yalnızca tek bir en yakın değere değil, aynı zamanda hedef değerinize belirli bir aralıkta olan tüm sayıların seçilmesine ihtiyaç duyabilirsiniz—bu aralık genellikle sapma aralığı olarak adlandırılır. Kutools for Excel’in Belirli hücreleri seçin özelliği sayesinde, hedefinizden belirli bir fark içinde kalan tüm değerleri hızlıca seçebilirsiniz.

Örneğin, hedef değerinizin18 ve sapma değerinizin2 olduğunu düşünelim. Bu durumda, aralığınızda16 (18–2) ile20 (18+2) arasındaki tüm değerleri seçmek istersiniz. Bunu adım adım şu şekilde yapabilirsiniz:

1. Aramak istediğiniz aralığı seçin (örneğin B3:B22), ardından Kutools > Seç > Belirli hücreleri seçin menüsüne gidin.

2. Belirli hücreleri seçin iletişim kutusunda:

• Seçim türünü belirtin bölümünde, Hücre’yi seçin.
• İçinde Türü belirtin:
  - İlk açılır listeyi ayarlayın Büyük veya eşittir ve kutuya girin 16 kutucuğuna.
  - İkinci açılır menüyü ayarlayın Küçük veya eşittir ve kutuya girin 20.

3. Tıklayın Tamam işlemini başlatmak için. Kutools, kriterlerinize uyan kaç hücrenin olduğunu bildirir ve aşağıda gösterildiği gibi belirtilen sapma sınırları içinde kalan tüm en yakın değerleri vurgular:

Bu çözüm, özellikle toleransı değişken olan geniş aralıklarla çalışırken, yakın değerleri toplu olarak hızlıca bulmak için idealdir. Dikkat edilmesi gereken nokta, sapmanızın doğru ayarlandığından emin olmaktır—sapmanız çok dar veya geniş olursa, ilgili verileri kaçırabilir veya gereksiz değerler dahil edebilirsiniz.

Otomasyon arayan veya çeşitli sayfalarda ya da büyük veri kümelerinde hem sayısal hem de metin için özelleştirilmiş en yakın değer araması yapmak isteyen kullanıcılar için, VBA makrosu etkili ve esnek bir çözümdür. Excel’i hedefinizle tüm adaylar arasındaki farkı sistematik olarak kontrol edecek şekilde programlayarak, yalnızca en yakın sayıyı değil, aynı zamanda metin mesafesine göre en yakın metni de elde edebilirsiniz.

Bu yaklaşım, özellikle tümü elle işlenemeyecek kadar büyük aralıklarda veya tekrarlayan görevlerde entegre otomasyon gerektiğinde avantaj sağlar. Ancak, VBA makroların çalışabilmesi için makroların etkinleştirilmiş olması ve temel VBA ortamı bilgisine sahip olunması gerekmektedir. Herhangi bir makroyu çalıştırmadan önce, olası veri kayıplarını önlemek için daima verilerinizi yedekleyin.

1. Geliştirici > Visual Basic’e tıklayın. Microsoft Visual Basic for Applications penceresinde, Ekle > Modül‘e tıklayın ve aşağıdaki kodu ilgili modüle yapıştırın:

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. Ardından, çalışma sayfanıza gidin ve şu formülü boş bir hücreye girin: =FindClosest(B3:B22, E2). En yakın değeri almak için Enter tuşuna basın.

Verileri incelerken veya sunarken, hedefe en yakın değerleri filtrelemeden veya verilerinizi yeniden düzenlemeden görsel olarak belirlemek faydalı olabilir. Excel’in yerleşik Koşullu Biçimlendirme özelliği, hedef değerinize en yakın olan hücreleri vurgulamanızı sağlar ve böylece bu hücreleri kolayca fark edebilirsiniz. Bu yöntem, tek başına değerin kendisini döndürmese de, hızlı veri analizi ve görsel vurgu için oldukça etkilidir.

Bu yöntemin en önemli avantajı, veri veya hedef değerleriniz değiştikçe uyum sağlayabilen, verilerinize zarar vermeyen dinamik bir vurgulama sunmasıdır. Özellikle panolarda, sunumlarda ve görünürlüğün önemli olduğu inceleme senaryolarında uygundur. Birden fazla değer aynı “yakınlığa” sahipse kesin sonuç vermez ve vurgulanan değeri işleme almak için ek adım gerektirir.

1. Analiz etmek istediğiniz hücre aralığını seçin (örneğin B3:B22).

2. Giriş sekmesinde, Koşullu Biçimlendirme > Yeni Kural'a tıklayın.

3. İletişim kutusunda, biçimlendirilecek hücreleri belirlemek için bir formül kullan seçeneğini seçin. Ardından, formül kutusuna aşağıdaki formülü girin:

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. Biçim’e tıklayın ve istediğiniz vurgu rengini seçin, ardından Tamam’a ve bir kez daha Tamam’a tıklayarak kuralı uygulayın.

Bu işlem, seçtiğiniz aralıktaki değerleri E2’deki hedef değere eşit şekilde en yakın olan tüm hücreleri vurgulayacaktır.

Geniş aralıklarla çalışıyorsanız veya beklenmeyen sonuçlar elde ediyorsanız, başvurularınızın doğru olduğundan ve mutlak/göreli başvuruların amaçlandığı gibi ayarlandığından emin olmak için ($ kullanarak hedef hücreyi ve aralık başvurularını sabitleyebilirsiniz) tekrar kontrol edin.