Excel Forum

Excel Forum (http://www.excel.web.tr/index.php)
-   Beyin Fırtınası (http://www.excel.web.tr/forumdisplay.php?f=142)
-   -   İki değer arasında kalan değerin karşılığı (http://www.excel.web.tr/showthread.php?t=30310)

zafer 10-04-2007 13:50

İki değer arasında kalan değerin karşılığı
 
1 Eklenti(ler)
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-04-2007 13:57

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-04-2007 14:00

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-04-2007 14:14

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-04-2007 15:42

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-04-2007 15:48

Alıntı:

zafer tarafından gönderildi (Mesaj 161694)
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-04-2007 15:53

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-04-2007 16:17

1 Eklenti(ler)
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çuk 10-04-2007 17:54

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>=A 2;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-04-2007 20:44

Alıntı:

zafer tarafından gönderildi (Mesaj 161700)
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.


Saat 17:59

Powered by vBulletin Version 3.7.2
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.