Excel formülü: İki yönlü yaklaşık birden çok ölçütle eşleşir -

İçindekiler

Özet

Birden çok ölçütle iki yönlü yaklaşık eşleşme araması gerçekleştirmek için, ölçütleri uygulamak için EĞER işlevinin yardımıyla INDEX ve MATCH tabanlı bir dizi formülü kullanabilirsiniz. Gösterilen örnekte, K8'deki formül şöyledir:

=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))

burada veriler (D6: H16), çap (D5: H5), malzeme (B6: B16) ve sertlik (C6: C16) yalnızca kolaylık sağlamak için kullanılan aralıklardır.

Not: Bu bir dizi formülüdür ve Control + Shift + Enter ile girilmelidir

Açıklama

Amaç, malzeme, sertlik ve matkap ucu çapına göre bir ilerleme hızı aramaktır. Besleme hızı değerleri, adlandırılmış aralık verilerindedir (D6: H16).

Bu, iki yönlü bir INDEX ve MATCH formülü ile yapılabilir. Bir MATCH işlevi satır numarasını (malzeme ve sertlik) hesaplarken diğer MATCH işlevi sütun numarasını (çap) bulur. INDEX işlevi, nihai sonucu döndürür.

Gösterilen örnekte, K8'deki formül şöyledir:

=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column

(Yalnızca okunabilirlik için satır sonları eklendi).

İşin zor kısmı, malzeme ve sertliğin birlikte ele alınması gerektiğidir. MATCH'ı belirli bir malzeme için sertlik değerleriyle sınırlamamız gerekir (gösterilen örnekte Düşük Karbonlu Çelik).

Bunu EĞER işlevi ile yapabiliriz. Esasen, bir eşleşme aramadan önce alakasız değerleri "atmak" için IF kullanırız.

Detaylar

İNDİS işlevi, dizide olduğu gibi adlandırılmış aralık verileri (D6: H16) olarak verilir. İlk MATCH işlevi satır numarasını hesaplar:

MATCH(K6,IF(material=K5,hardness),1) // get row num

Doğru sırayı bulmak için, malzeme üzerinde tam bir eşleşme ve sertlik için yaklaşık bir eşleşme yapmamız gerekir. Bunu, ilk önce alakasız sertliği filtrelemek için IF işlevini kullanarak yapıyoruz:

IF(material=K5,hardness) // filter

K5'teki ("Düşük Karbonlu Çelik") değerle eşleşip eşleşmediklerini görmek için malzemedeki (B6: B16) tüm değerleri test ediyoruz . Eğer öyleyse, sertlik değeri geçilir. Değilse, EĞER YANLIŞ döndürür. Sonuç şuna benzer bir dizidir:

(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)

Hayatta kalan tek değerlerin Düşük Karbonlu Çelik ile ilişkili değerler olduğuna dikkat edin. Diğer değerler artık YANLIŞ. Bu dizi, arama_dizisi olarak doğrudan MATCH işlevine döndürülür.

Eşleşme için arama değeri, verilen sertlik 176'yı içeren K6'dan gelir. MATCH, match_type'ı 1'e ayarlayarak yaklaşık eşleşme için yapılandırılır. Bu ayarlarla, MATCH FALSE değerlerini yok sayar ve tam bir eşleşmenin konumunu veya sonraki en küçük değeri döndürür .

Not: sertlik değerleri her malzeme için artan sırada sıralanmalıdır.

Sertlik 176 olarak verilen MATCH, satır numarası olarak doğrudan INDEX'e teslim edilen 6'yı döndürür. Şimdi orijinal formülü şu şekilde yeniden yazabiliriz:

=INDEX(data,6,MATCH(K7,diameter,1))

İkinci MATCH formülü, çapla yaklaşık bir eşleşme gerçekleştirerek doğru sütun numarasını bulur:

MATCH(K7,diameter,1) // get column num

Not: D5: H5 çapındaki değerler artan sırada sıralanmalıdır.

Arama değeri K7'den (0.75) gelir ve arama_dizisi adlandırılmış aralık çapıdır (D5: H5).

Daha önce olduğu gibi, MATCH, match_type'ı 1 olarak ayarlayarak yaklaşık eşleşmeye ayarlanır.

0,75 olarak verilen çapla, MATCH, sütun numarası olarak doğrudan INDEX işlevine teslim edilen 3 değerini döndürür. Orijinal formül artık şu şekilde çözülür: