İki değer arasında kalan değerin karşılığı

zafer · 10 Nisan 2007

Merhaba

Açıklamamı ekli, dosyada yazdım.
istenen : İki değer arasında kalan verinin karşılığı olan değerin istediğimiz sütuna getirilmesi.
Bir bakıma düşeyara diyebiliriz ama nasıl..

Daha önce Türker arkadaşım tarafından sütun ilavesi yapılarak formülle çözülmüştü.
Bende aynı konuyu soran başka bir arkadaşıma aynı çözümü önermiştim

Alternatifleri olabilirmi...

Burada ilk çözüm excel'in kendi işlevlerini kullanmak.Tabiiki diğer arkadaşlara yol göstermek açısındam Kullanıcı tanımlı fonk.ve makro ilede çözümler üretebiliriz.

Ali · 10 Nisan 2007

H2 hücresine

=ARA(G2;{1;4;7;10;13;16};{"A";"B";"C";"D";"E";""}) yazılıp aşağı doğru çekilir.

Levent Menteşoğlu · 10 Nisan 2007

H2 Hücresine aşağıdaki formülü yazıp, aşağıya doğru kopyalayabiliriz.

=DOLAYLI("C"&TOPLA.ÇARPIM(($A$2:$A$6<=G2)*($B$2:$B$6>=G2)*(SATIR($C$2:$C$6))))

Formülün açıklaması:

TOPLA.ÇARPIM (SUMPRODUCT) çok kriterli aramalarda büyük avantaj sağlayan bir dizi fonksiyonudur. Her parantez içi bir kriteri belirlerken son parantez içi bulunacak verinin bulunduğu aralığı temsil eder.

Fonksiyon içindeki ($A$2:$A$6<=G2) parantezi "G SÜTUNUNDAKİ DEĞER A SÜTUNUDAKİ DEĞERE EŞİT veya BÜYÜKSE" kriterini ararken,

($B$2:$B$6>=G2) parantezide "B SÜTUNUNDAKİ DEĞERE EŞİT veya KÜÇÜKSE" kriterini arar.

Her iki kritere uyan satırdaki veride (SATIR($C$2:$C$6))) parantezi ile bulunur. Peki SATIR fonksiyonu burada neden kullanılmıştır. Bunuda açıklamaya çalışayım.

TOPLA.ÇARPIM fonksiyonu sadece sayısal değer döndürür, halbuki formülden getirmesi istenen C sütunundaki veriler sayısal değildir, bu sebeple fonksiyon hata verecektir. Bu hatayı engellemek ve sayısal bir veri döndürmek için bulunan verinin bulunduğu satır nosunu getirmesini uygun gördüm. Satır ve sütun nosu belli olan bir verinin değerini bulmak artık çok kolay olacaktır. Bu işlem içinde DOLAYLI fonksiyonu kullanılmıştır.

=DOLAYLI("C"&...) fonksiyonunda "..." olan yere satır nosunun yazılması gerekir, işte bu değeri bize TOPLA.ÇARPIM fonksiyonu vermektedir.

DOLAYLI("C"&1) = DOLAYLI("C1") = C1 dir.

AS3434 · 10 Nisan 2007

Eğer veriler örnekteki gibiyse (Yani A3>B2....A6>B5 gibi)

H2 hücresine

=DÜŞEYARA(G2;$A$2:$C$7;3)

Yazabiliriz. Yalnız dezavantajı, Örnekteki en büyük rakam 15, ama biz H2'ye 20 yazarsak 15 'in karşılığı "E"'yi yazar.

Onu da şöyle aşabiliriz;

=EĞER(G2>BÜYÜK($B$2:$B$7;1);"";DÜŞEYARA(G2;$A$2:$C$17;3))

zafer · 10 Nisan 2007

Merhaba

Teşekkürler.
Sayın Ali , Sayın AS3434

Kullandığım verier sizleri yanıltmış olabilir.Düşünmem gerekirdi özür dilerim burada istenen iki değer arasında kalan değeri bulmalı.(değerler birbirini takip etmeyebilir bir birini satır bazında takip eden hücrelerdeki değerlerde büyük sayı farklılıkları olabilir)

Bulunan değer en yakın değerde olmamalı

Çözüm için A ve B sütunundaki değerlerin ikisinide kullanmak gerekir diye düşünüyorum.

Kodlar arasında sayı atlayabilir.Kod aralıklarıda 50 (daha az , daha fazla)satır olabilir.

şöyleki.

1 3 A
4 8 B
17 23 C
45 49 D

Ali · 10 Nisan 2007

zafer' Alıntı:
Bulunan değer en yakın değerde olmamalı

Çözüm için A ve B sütunundaki değerlerin ikisinide kullanmak gerekir diye düşünüyorum.

Kodlar arasında sayı atlayabilir.

şöyleki.

1 3 A
4 8 B
17 23 C
45 49 D

Zafer Bey sayı atlasın önemli değil aralığı biz belirlediğimize göre

Bu seferde

=ARA(G2;{1;4;17;45;50};{"A";"B";"C";"D";""}) şeklinde düzenleyebiliriz.

50 ve yukarısı için boş hücre yazacaktır.

Dizi 1 sayısı aldığında karşılığındaki A sayısını oku. 3 kadar A kabul et 4 olduğunda ise
4 {1;4;17;45;50} dizisinin ikinci elemanı olduğudan {"A";"B";"C";"D";""} dizisinin ikinci elemanı olan B harfini getirecektir.

Diğer sayılarda buna göre takip edecektir.

zafer · 10 Nisan 2007

Merhaba

Sayın Ali çözümünüz tabiiki oluyor.

=ARA(G2;{1;4;17;45;50};{"A";"B";"C";"D";""})

burada 5 satır üzerinden hesaplama var, 50 satır olarak düşünürsek 256 karakteri geçecektir.
yani kodlar A1 hücresinden C70 kadar gidebilir.

anlatmak istediğim oydu.

Recep İpek · 10 Nisan 2007

Sanırım Kullanıcı Tanımlı Fonksiyon ile bu şekilde olabilir.

Burada alan1 bölümüne A kolonunu, alan2 bölümüne B kolonunu, alan3 bölümüne C kolonunu,
alan4 bölümüne ise kriteri yani ilki için G2 hücresini seçmek yeterlidir.

Kod:

Function SONUC(alan1, alan2, alan3, alan4 As Range)
For i = 1 To alan1.Count
If alan4 >= alan1(i) And alan4 <= alan2(i) Then
SONUC = alan3(i)
End If
Next i
End Function

Selçuk1 · 10 Nisan 2007

Değerli üstadlarımızın yanında cevap vermek bize düşer mi bilmem ama, bir alternatif de ben düşünmüştüm..

=EĞER(EHATALIYSA(DOLAYLI("C"&KAÇINCI(EĞER(VE(G2>=A2;G2<=B2);A2;"1");A:A;0)));"MARKA YOK";DOLAYLI("C"&KAÇINCI(EĞER(VE(G2>=A2;G2<=B2);A2;"1");A:A;0)))

sanırım çözüme gidiyor bu da..

saygılar.

Ali · 10 Nisan 2007

zafer' Alıntı:
Merhaba

Sayın Ali çözümünüz tabiiki oluyor.

=ARA(G2;{1;4;17;45;50};{"A";"B";"C";"D";""})

burada 5 satır üzerinden hesaplama var, 50 satır olarak düşünürsek 256 karakteri geçecektir.
yani kodlar A1 hücresinden C70 kadar gidebilir.

anlatmak istediğim oydu.

Belirttiğiniz durum için;

=DÜŞEYARA(MAK(EĞER($A$2:$A$6<=G2;$A$2:$A$6));$A$2:$C$6;3;0))

CTRL+SHIFT+ENTER tuşları girilerek dizi formülüne çevrilerek yapılabilir.

B sütunundaki maksimum değerden büyük yazıldığında değer vermemesi için ise

=EĞER(G2>MAK($B$2:$B$6);"";DÜŞEYARA(MAK(EĞER($A$2:$A$6<=G2;$A$2:$A$6));$A$2:$C$6;3;0))

CTRL+SHIFT+ENTER tuşları girilerek dizi formülüne çevrilerek yapılabilir.

Ali · 10 Nisan 2007

Başka bir alternatif bu daha kısa oldu

=DÜŞEYARA(ARA(G2;$A$2:$A$6;$B$2:$B$6);$B$2:$C$6;2;0)

Yine 15'ten büyük değer için sonuç göstermesin istersek;

=EĞER(G2>MAK($B$2:$B$6);"";DÜŞEYARA(ARA(G2;$A$2:$A$6;$B$2:$B$6);$B$2:$C$6;2;0)) olarak kullanabiliriz.

Necdet · 10 Nisan 2007

Fonksiyon ile çözümleri bir araya getirmeye çalıştım. Hata yaptım mı bilmiyorum. Türkçe fonksiyonları kopyalayıp yapıştırdım ve ingilizce karşılıklarını yazdım.

Sayın Zafer'in örnek dosyasında B3 hücresini kurala uymaması açısından değiştirdim. Bu duruma göre çözümleri ekli dosyada görebilirsiniz.

İdris SERDAR · 10 Nisan 2007

Biribirlerine benzeyen dört formülle çözüm ekteki dosyada.

..

Zafer beyin son açıklamasına dikkat etmemmişim. Onun için kullandığım yöntem bazı hallerde hata veriyor. Bu bakımdan dosyayı geri alıyorum.

Ali · 11 Nisan 2007

Aslında bu kadar uzun formüllere gerek yok sadece

=ARA(G2;$A$2:$C$9)

formülü yeterlidir.

Ali · 11 Nisan 2007

Veriler karışık ise düzenli gitmiyorsa bu durumda daha önce verdiğim

Kod:

=EĞER(E2>MAK($B$2:$B$100);"";DÜŞEYARA(MAK(EĞER($A$2:$A$100<=E2;$A$2:$A$100));$A$2:$C$100;3;0))

formülü

CTRL+SHIFT+ENTER tuşlarına basılarak dizi formülü haline getirilir.

Örnek dosya ektedir.

zafer · 11 Nisan 2007

Merhaba

Çok değerli arkadaşlarımın göndermiş olduğu çözümleri ekli dosyada 4 ayrı sayfa olarak düzenledim.

İlk mesajımda konuyu daha detaylı anlatamamamdan (düşündüğümü yazıya dökememeden)dolayı konu ile ilgili olarak değişik formüller üreten arkadaşlarımın zamanı aldığım için özür dilerim.

Yalnız bu çözümlerde daha değişik çözümler isteyen arkadaşlara alternatif olur diye düşünüyorum.

İlgilenen arkadaşlara çok teşekkür ediyorum.

Çözümlerde kullanılan formül açıklamalarını isteyen arkadaşlar dosyaya ekleyebilirler ve çözüm üretebilirler.

alibaskan · 12 Nisan 2007

=DÜŞEYARA(TOPLA(EĞER($G2>=$A$2:$A$6;EĞER($G2<=$B$2:$B$6;$B$2:$B$6)));$B$2:$C$6;2;0) formülü cevabı verir. Fakat A ve B sütunlarında birden fazla koşula uyan seçenek varsa çalışmaz. Zaten böyle bir şey olursa C sütunundaki değerlerin yanyana mı yazılacağı belli olmadığı için yukarıdaki formül yeterli olacaktır. Formül Ctrl+Shift+Enter ile kapatılmalıdır. Formül takip eden satırlara kopyalanmalıdır.

Ali · 12 Nisan 2007

Sn alibaskan veriler düzenli gitmiyor eğer öyle olsa idi

=ARA(G2;$A$2:$C$9)

formülü yeterli idi. Uzun formüle gerek yok.

1 3 A
6 4 B şeklindede olması sözkonusudur.

emrexcel123 · 21 Nisan 2007

basit

üstadlar belki komik gelebilir ama şu formül olmazmıydı >>
=+EĞER(YADA((G2>=A2);(G2<=B2));C2;"")
bu formül aşağıya doğru kopyalanırsa sonucu görürsünüz...

Ali · 21 Nisan 2007

Sn emre tek bir formül ile bulmaya çalıştığımız için ve Sn zafer'in bir başka istediği ise verilerin büyük küçük sırası ile gitmemeside sözkonusudur.

15'inci mesajımdaki örnek dosyayı indirip bakarsanız soru dahada netleşecek sanırım.

Saygılar.

İki değer arasında kalan değerin karşılığı

zafer

Super Moderator

Ekli dosyalar

Ali

Uzman

Levent Menteşoğlu

Administrator

AS3434

Özel Üye

zafer

Super Moderator

Ali

Uzman

zafer

Super Moderator

Recep İpek

Selçuk1

Ali

Uzman

Ali

Uzman

Necdet

Moderatör

İdris SERDAR

Moderatör

Ali

Uzman

Ali

Uzman

zafer

Super Moderator

Ekli dosyalar

alibaskan

Ali

Uzman

emrexcel123

Ali

Uzman