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

Diyelim ki, B Sütunu'nda bir sayı listeniz var ve verilen bir sayıya—örneğin,18'e—en yakın olan değeri belirlemeniz gerekiyor. Excel'de bir dizi formülü kullanarak bunu listenin tamamını elle kontrol etmeden verimli bir şekilde bulabilirsiniz.

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

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

Bu yöntem, sürekli bir aralıktan yalnızca tek bir en yakın sayıyı almak istediğinizde en uygunudur. Sayısal doğruluk ve tam eşleşmenin önemli olduğu çoğu durumda iyi çalışır. Ancak, dizi formüllerinin çok büyük veri kümelerinde sistem kaynaklarını daha fazla kullanacağı unutulmamalıdır. Eğer performans sorunları yaşarsanız veya #DEĞER! gibi hata mesajları alırsanız, hücre referanslarınızı tekrar kontrol edin ve Ctrl + Shift + Enter tuşlarına doğru bastığınızdan emin olun.

Bazen yalnızca tek bir en yakın değere ihtiyacınız olmayabilir, bunun yerine hedef değerinize belirli bir aralık dahilinde giren tüm sayıları seçmek isteyebilirsiniz—bu aralık genellikle 'sapma aralığı' olarak adlandırılır. Kutools for Excel, Seçili Özel Hücreler özelliği ile hedefinize belirli bir fark dahilindeki tüm değerleri hızla seçmenizi sağlar.

Örneğin, hedef değeriniz18 ve sapma değerini2 olarak belirlediniz. Bu durumda, aralığınızda16 (18–2) ile20 (18+2) arasında yer alan tüm değerleri seçmek isteyeceksiniz. Bunu adım adım aşağıdaki gibi yapabilirsiniz:

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

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 şuna ayarlayın Büyük veya eşittir ve kutuya girin 16 kutusuna.
  - İkinci açılır listeyi şuna ayarlayın Küçük veya eşittir ve kutuya girin 20.

3tıklayın OK yürütmek için. Kutools, kaç hücrenin kriterlerinizi karşıladığını bildirir ve belirlediğiniz sapma içinde kalan tüm en yakın değerleri aşağıda gösterildiği gibi vurgular:

Bu çözüm, özellikle değişken toleranslar ile geniş aralıkları işlerken, toplu şekilde yakın değerleri hızlıca bulmak için idealdir. Seçiminizin doğruluğunun sapma aralığınızı doğru tanımlamanıza bağlı olduğunu unutmayın—eğer sapma aralığı çok dar veya geniş ise, ilgili verileri kaçırabilir veya istenmeyen değerleri dahil edebilirsiniz.

Otomasyon arayan veya birden fazla sayfa veya büyük veri kümelerinde sayısal ya da metin verileri için özelleştirilmiş en yakın değer araması yapmak isteyen kullanıcılar için, VBA makrosu verimli ve esnek bir çözüm sunar. Excel'i, hedefiniz ve adaylarınız arasındaki farkı sistemli olarak kontrol edecek şekilde programladığınızda, yalnızca en yakın sayıyı değil, metin mesafesi ile en yakın metin dizisini de alabilirsiniz.

Bu yöntem, özellikle el ile işlemeye göre çok büyük aralıklar söz konusu olduğunda veya tekrarlayan görevleri uygularken, entegre otomasyon gerektiğinde avantaj sağlar. Ancak unutmayın ki, VBA makrosu kullanmak için makroları etkinleştirmeniz ve temel VBA ortamına aşina olmanız gerekir. Herhangi bir makroyu çalıştırmadan önce, beklenmedik veri kaybını önlemek için daima verilerinizi yedekleyin.

1. Geliştirici' ya tıklayın > Visual Basic. Microsoft Visual Basic for Applications penceresinde, Ekle > Modül yolunu izleyin ve aşağıdaki kodu modül içine kopyalayı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 bu formülü: =FindClosest(B3:B22, E2) boş bir hücreye girin. En yakın değeri elde etmek için Enter tuşuna basın.

Verileri incelerken veya sunarken, hedefinize en yakın değerleri filtreleme ya da yeniden düzenleme yapılmadan görsel olarak belirlemek sıklıkla yardımcı olur. Excel'in yerleşik Koşullu Biçimlendirme özelliği, hedef değerinize en yakın hücreleri vurgulamanızı sağlar ve bu değerleri bir bakışta fark etmenizi kolaylaştırır. Bu yöntem tam olarak değeri getirmese de, hızlı veri analizi ve görsel vurgu için etkilidir.

Bu yöntemin başlıca avantajı, veri veya hedef değerler değiştikçe dinamik ve orijinal veriye zarar vermeden vurgulama sağlamasıdır. Kontrol panelleri, sunumlar ve görünürlüğün önemli olduğu inceleme senaryoları için özellikle uygundur. Ancak, birden fazla değerin aynı 'yakınlıkta' olması halinde hassasiyet azalabilir ve vurgulanan değeri başka bir işleme aktaramazsınız.

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. Açılan kutuda, Hücreleri biçimlendirmek için kullanılacak bir formül seçin 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çimlendir'e tıklayın ve vurgulama rengi seçin, ardından OK'a ve tekrar OK'a tıklayarak kuralı uygulayın.

Bu işlem, seçilen aralıkta E2 hücresindeki hedef değere eşit derecede en yakın olan tüm hücreleri vurgulayacaktır.

Geniş aralıklarla çalışıyorsanız veya beklenmeyen sonuçlar alıyorsanız, başvurularınızın doğru olduğundan ve mutlak/göreli referansların (hedef hücre ve aralık referanslarını $ ile sabitleyerek) beklendiği gibi ayarlandığından emin olun.