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

Diyelim ki Sütun B’de bir sayı listesiniz var ve örneğin 18 gibi belirli bir sayıya en yakın değeri bulmanız gerekiyor. Excel’de bir dizi formülü kullanmak, bu değeri liste üzerinde manuel olarak taramadan verimli bir şekilde 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 yalnızca Enter tuşuna basmak yerine Ctrl + Shift + Enter tuşlarına bastığınızdan emin olun. Böylece formül, doğru çalışması için gerekli olan dizi formülü olarak yürütülecektir:

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

Bu yaklaşım, sürekli bir aralıktan tek bir en yakın sayıyı almanız gerektiğinde en uygundur. Sayısal doğruluk ve tam eşleşmelerin kritik olduğu çoğu durumda mükemmel sonuçlar verir. Ancak dizi formüllerinin çok büyük veri kümelerinde kaynak yoğun olabileceğini unutmayın. Performans sorunları ya da #DEĞER!gibi hata mesajları alırsanız, hücre referanslarınızı kontrol edin ve Ctrl + Shift + Entertuşlarına doğru şekilde bastığınızdan emin olun.

Bazen tek bir en yakın değere değil, hedef değerinizin belirli bir aralığı içindeki tüm sayılara ihtiyacınız olabilir—bu genellikle sapma aralığı olarak adlandırılır. Kutools for Excel, Özel Hücreleri Seç özelliğini kullanarak hedefinize belirli bir fark dahilindeki tüm değerleri hızlıca seçmenizi sağlayan pratik bir çözüm sunar.

Örneğin, hedef değerinizi 18 ve sapma değerini 2 olarak belirlediğinizi varsayalım. Bu durumda, aralığınız 16 (18–2) ile 20 (18+2) arasında kalan tüm değerleri kapsar. Bunu adım adım şu şekilde gerçekleştirebilirsiniz:

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

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

• Altında Seçim türünü belirtin, ardından Hücre’yi seçin.
• İçinde Türü belirtin:
  - İlk açılır liste değerini Daha Büyük veya Eşit olarak ayarlayın ve kutuya 16 girin.
  - İkinci açılır menüyü Daha Küçük veya Eşit olarak ayarlayın ve 20 girin.

3. Uygulamak için Tamam düğmesine tıklayın. Kutools, kriterlerinize uyan kaç hücre olduğunu bildirecek ve aşağıda gösterildiği gibi belirtilen sapma dahilindeki tüm en yakın değerleri vurgulayacaktır:

Bu çözüm, özellikle değişken toleranslara sahip geniş aralıklarla çalışırken tüm yakın değerleri toplu olarak hızlıca belirlemek için idealdir. Unutmayın: seçiminizin doğruluğu, sapmanızın net bir şekilde tanımlanmasına bağlıdır—sapmanız çok dar ya da çok geniş olursa İlgili Veriler’i kaçırabilir veya istenmeyen değerleri dahil edebilirsiniz.

Sayısal ya da metin verilerinde, birden fazla sayfa veya büyük veri kümeleri üzerinde özelleştirilmiş en yakın değer aramalarını otomatikleştirmek isteyen kullanıcılar için VBA makrosu verimli ve esnek bir çözümdür. Excel’i, hedefiniz ile tüm adaylar arasındaki farkı sistematik olarak değerlendirecek şekilde programlayarak yalnızca en yakın sayıyı değil, aynı zamanda metin uzaklığına göre en yakın dizgeyi de elde edebilirsiniz.

Bu yaklaşım, özellikle manuel yöntemlerin yetersiz kaldığı çok geniş aralıklarda veya tekrarlayan görevlerde entegre otomasyon gereksinimi olduğunda büyük avantaj sağlar. Ancak unutmayın: VBA makrolarının çalışması için makroların etkinleştirilmesi ve VBA ortamına temel düzeyde hakimiyet gereklidir. Herhangi bir makroyu çalıştırmadan önce, verilerinizi yanlışlıkla kaybetmemek için her zaman yedekleyin.

1. Geliştirici > Visual Basic seçeneğine tıklayın. Microsoft Visual Basic for Applications penceresinde, Ekle > Modül seçeneğine tıklayıp aşağıdaki kodu modüle 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ü boş bir hücreye girin: =FindClosest(B3:B22, E2). En yakın değeri almak için Enter tuşuna basın.

Verileri gözden geçirirken veya sunarken, verilerinizi filtrelemeden ya da yeniden düzenlemeksizin hedefe en yakın değerleri görsel olarak belirlemek genellikle büyük kolaylık sağlar. Excel’in yerleşik Koşullu Biçimlendirme Kullan özelliği, hedef değerine en yakın hücreleri vurgulayarak onları tek bir bakışta fark etmenizi sağlar. Bu yöntem tam değeri döndürmese de, hızlı veri analizi ve görsel vurgu açısından son derece etkilidir.

Bu yöntemin başlıca avantajı, veri veya hedef değerler değiştiğinde uyarlanabilen yıkıcı olmayan, dinamik vurgulamadır. Görünürlüğün önemli olduğu panolar, sunumlar ve inceleme senaryoları için özellikle uygundur. Ancak birden fazla değer aynı “yakınlık” derecesine sahipse daha az kesin olabilir ve ileri işlem için değeri kendisi çıktı olarak vermez.

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

2. Giriş sekmesinde, Koşullu Biçimlendirme > Yeni Kural seçeneğine tıklayın.

3. İletişim kutusunda Biçimlendirilecek hücreleri belirlemek için bir formül kullan seçeneğini işaretleyin. 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 seçeneğine tıklayın, bir vurgu rengi belirleyin, ardından Tamam ve tekrar Tamam düğmesine tıklayarak kuralı uygulayın.

Bu işlem, Aralık seçimi içindeki değerleri E2 hücresindeki hedef değere en yakın olacak şekilde eşleştirerek, bu koşulu sağlayan tüm hücreleri vurgulayacaktır.

Geniş aralıklarla çalışıyorsanız ya da beklenmeyen sonuçlarla karşılaşıyorsanız, referanslarınızın doğru olduğundan ve mutlak/göreli referansların amaçlandığı şekilde ayarlandığından emin olun (hedef hücreyi ve aralık referanslarını sabitlemek için $ işaretini kullanın).