Excel'deki metin dizilerinde minimumu ve sayıları bulma

Karışık metin ve sayılar içeren bir dizede (örneğin, "Ürün 12 adet, fiyat 5, sapma -3") minimum değeri bulmak için TEXTSPLIT, VALUE ve MIN fonksiyonlarına dayalı bir dizi formülü kullanın. Excel'in modern sürümleri (365, 2021) için optimal çözüm şu şekildedir:

=MIN(FILTER(VALUE(TEXTSPLIT(A1; " ")); ISERROR(VALUE(TEXTSPLIT(A1; " ")); "")=FALSE))

Bu formül metni parçalara böler, bunları sayılara dönüştürür, hataları (metinleri) filtreler ve en küçük değeri bulur. Aşağıda programın farklı sürümleri için yöntemleri ve tüm sayıları aynı anda ayıklamanın yollarını detaylıca inceleyeceğiz.

Görevin özgüllüğü: Tek hücrede metin ve sayılar

MIN() gibi standart Excel fonksiyonları metin değerlerini yok sayar. Bir hücrede "10 kg" yazıyorsa, fonksiyon bunu metin olarak algılar ve 0 veya hata döndürür. Görevimiz, şu türdeki dizelerden sayısal bileşeni "çekip çıkarmaktır":

• "Sipariş №45, ağırlık 12.5 kg, bozuk 2 adet"
• "Sıcaklık: -5, normal 20, maksimum 25"
• "A12 B7 C99" (ayırıcı olmayan karmaşık durum)

Çözüm, yazılımınızın sürümüne ve verilerin yapısına bağlıdır.

Excel 365 ve 2021 için çözüm (Dinamik diziler)

Yeni sürümlerde, dizeleri boşluklara veya virgüllere göre ayırmak için ideal olan TEXTSPLIT fonksiyonu ortaya çıktı.

Adım 1: Diziyi bölme

Elemanlardan oluşan bir dizi elde etmek için TEXTSPLIT(hücre; " ") kullanıyoruz.

Adım 2: Sayılara dönüştürme

VALUE fonksiyonu her elemanı sayıya dönüştürmeye çalışır. Metinler #DEĞER! hatasına dönüşür.

Adım 3: Filtreleme ve minimumu bulma

Her şeyi sadece sayıları bırakacak şekilde FILTER içine sarıyoruz ve MIN uyguluyoruz.

Hazır formül:

=MIN(FILTER(VALUE(TEXTSPLIT(A1; " ")); ISERROR(VALUE(TEXTSPLIT(A1; " ")); "")=FALSE))

Not: İngilizce sürümde: =MIN(FILTER(VALUE(TEXTSPLIT(A1, " ")), ISERROR(VALUE(TEXTSPLIT(A1, " ")))=FALSE))

Eski sürümler için yöntemler (Excel 2016, 2019)

Dinamik dizilerin ve TEXTSPLIT fonksiyonunun olmadığı sürümlerde görev daha karmaşıktır. İki yol vardır: yardımcı sütunlar kullanmak veya makrolar.

Seçenek A: Yardımcı sütunlar

1. Kaynak diziyi kopyalayın.
2. "Metni Sütunlara Dönüştür" özelliğini kullanın (Veri sekmesi -> Metni Sütunlara Dönüştür), ayırıcı olarak "boşluk" seçeneğini belirleyin.
3. Veriler bitişik hücrelere dağıtılacaktır (B1, C1, D1...).
4. Yeni bir satırda kontrol formülü uygulayın: =IFERROR(VALUE(B1); ""). Formülü sağa doğru sürükleyin.
5. Elde edilen serinin minimumunu bulun: =MIN(2:2) (burada 2, temizlenmiş sayıların bulunduğu satır numarasıdır).

Seçenek B: Kullanıcı tanımlı fonksiyon (VBA)

İşlem sıkça gerekiyorsa, kendi fonksiyonunuzu oluşturmak daha iyidir. Alt+F11 tuşlarına basın, yeni bir modül ekleyin ve aşağıdaki kodu yapıştırın:

Function GetMinFromText(rng As Range) As Double
    Dim arr() As String
    Dim i As Integer
    Dim minVal As Double
    Dim currentVal As Double
    Dim firstNum As Boolean
    
    arr = Split(rng.Value, " ")
    firstNum = True
    
    For i = LBound(arr) To UBound(arr)
        If IsNumeric(arr(i)) Then
            currentVal = CDbl(arr(i))
            If firstNum Then
                minVal = currentVal
                firstNum = False
            ElseIf currentVal < minVal Then
                minVal = currentVal
            End If
        End If
    Next i
    
    If firstNum Then
        GetMinFromText = 0 ' Veya sayı yoksa hata
    Else
        GetMinFromText = minVal
    End If
End Function

Artık hücreye =GetMinFromText(A1) yazabilirsiniz.

Bir listeden dizideki TÜM sayıları nasıl ayıklarız

Bazen sadece minimumu bulmak değil, ileri analiz için tüm sayıların listesini almak da gerekir.

Excel 365'te optimizasyon için LET yapısını kullanın (TEXTSPLIT'in iki kez hesaplanmasını önlemek için):

=LET(
  tokens; TEXTSPLIT(A1; " ");
  nums; VALUE(tokens);
  FILTER(nums; ISERROR(nums; "")=FALSE)
)

Bu formül bulunan tüm sayıların dikey bir dizisini döndürür. Sonuca doğrudan diğer fonksiyonları uygulayabilirsiniz: SUM(...), AVERAGE(...) veya ikinci en büyük değeri bulmak için SMALL(...; 2).

Tablo: İşleme yöntemlerinin karşılaştırması

Sık yapılan hatalar ve yerelleştirme nüansları

Metin içindeki sayılarla çalışırken kullanıcılar genellikle şu sorunlarla karşılaşır:

1. Ondalık ayırıcı: Rus yerel ayarlarında ondalık ayırıcı virgüldür (,), İngilizce yerel ayarlarında ise nokta (.). Metinde "3.14" varsa ve sizde Rus yerel ayarı kullanılıyorsa, VALUE hata döndürür.
   • Çözüm: Dönüştürmeden önce SUBSTITUTE kullanın: VALUE(SUBSTITUTE(token; "."; ",")).
2. Bitişik veriler: "A12B3" gibi dizeler boşluklara göre ayrılmaz.
   • Çözüm: Düzenli ifadelerle (VBA veya Power Query aracılığıyla) karmaşık bir formül veya ön veri temizliği gerekir.
3. Negatif sayılar: Eksi işaretinin doğru tanındığından emin olun. Bazen normal - yerine VALUE fonksiyonunu bozan uzun tire — kullanılır.

SSS

Soru: Dizide hiç sayı yoksa ne yapmalı? Cevap: FILTER içeren formül #BOŞ! hatası döndürür. Bunu önlemek için her şeyi IFERROR(...; "Sayı yok") içine sarın.

Soru: Aynı yöntemle maksimum değeri arayabilir miyim? Cevap: Evet, önerilen formüllerde MIN fonksiyonunu MAX ile değiştirin. Sayı ayıklama mantığı aynı kalır.

Soru: Bu Google E-Tablolar'da çalışır mı? Cevap: Evet, sözdizimi neredeyse aynıdır. Fonksiyonlar aynı adlara sahiptir (SPLIT, VALUE, FILTER, MIN), ancak formüllerdeki ayırıcı noktalı virgül yerine virgüldür.