• DİKKAT

    DOSYA İndirmek/Yüklemek için ÜCRETLİ ALTIN ÜYELİK Gereklidir!
    Altın Üyelik Hakkında Bilgi

tablodaki bilginin belli bir sütundaki karşılığı

  • Konbuyu başlatan Konbuyu başlatan amelie
  • Başlangıç tarihi Başlangıç tarihi
A

amelie

Katılım
7 Ocak 2005
Mesajlar
236
Excel Vers. ve Dili
Office Excel 2003 Tr/İng.
Arkadaşlar merhaba.Bir konuda yine takılıverdim.aslında cevabı alınca yine ah edeceğimden eminim ancak aşırı yoğunluktan basmadı kafam yine mazur görünüz lütfen.
Ekteki örnekte göreceğiniz üzere bir tablodaki alanlarda herhangi bir hücredeki bilginin o tablonun belli bir sütundaki değerini almak istiyorum.vlookup,index match dededim ancak başaramadım.
Yardımlarını rica ediyorum.
Saygılarımla.
 
amelie' Alıntı:
Arkadaşlar merhaba.Bir konuda yine takılıverdim.aslında cevabı alınca yine ah edeceğimden eminim ancak aşırı yoğunluktan basmadı kafam yine mazur görünüz lütfen.
Ekteki örnekte göreceğiniz üzere bir tablodaki alanlarda herhangi bir hücredeki bilginin o tablonun belli bir sütundaki değerini almak istiyorum.vlookup,index match dededim ancak başaramadım.
Yardımlarını rica ediyorum.
Saygılarımla.
Genişletmek için tıkla ...

Dosyanız ekte.

.
 
merhaba
b17 ye arayacağınız değeri yazıp aşağıdaki formülü denermisiniz
=TOPLA.ÇARPIM(--(B2:I15=B17)*--(J2:J15))
 
Sayın yurttas ve Sayın uzmanamele çok teşekkür ediyorum.Harika iki çözüm.yalnız bu topla.çarpım nelere kadirmiş birkez daha anlıyorum.:)
en içten sevgi ve saygılarımla.
 
merhaba
siz syn Yurttaş'ın formülünü kullanın, asıl doğru olan o formül.
iyi çalışmalar.
 
Buda farklı bir örnek,

=İNDİS($J$1:$J$15;MİN(EĞER($B$2:$I$15=$F24;SATIR($B$2:$I$15)-SATIR($B$1)+1)))

yazılıp CTRL+SHIFT+ENTER tuşlarına basılarak dizi formülü oluşturulur.

Örnek dosya ektedir.
 
Haklısınız ancak Öncelikle topla.çarpımla bir test etmek istiyorum çünkü veri çok fazla dosyanın şişmesine yolaçabilir diye düşünüyorum.
Zaten üstadın (sayın yurttas'ın) çözümü özenle incelenmesi bir çözüm,ayrıca yudumlayıp sindirmek lazım kanaatindeyim.
Sevgilerimle.
 
Ali bey size de çok teşekkür ediyorum katkınızdan ötürü.henüz test edemedim ancak yurtta'ın çözümü gibi ayrıca incelemek istiyorum,önce cevabı yazmak istedim.
ek olarak şunu belirtmek istiyorum ki excel ve acces'te ne kadar geliştirdiysem kendimi inanın bu forum sayesindedir.
Herkese teşekkür ediyorum.
Saygılarımla.
 
merhaba
topla.çarpım bu örnekte doğru sonuç verebilir ama siz syn Yurttaş'ın veya syn Ali'nin formüllerini kullanın.
kodlarınız 0019,0020 değilde K0019, K0020 gibi olduğu durumda her 3 çözüm yolu arasındaki farkı görün.
 
merhaba
kendi önerdiğim formülü kullanmasın diye yırtınıyorum :) :)
 
:)) Gerçekten de öyle bi durum var..Aslında durumun farkındayım değer hatası almamam için yapıyorsunuz zannımca ancak ilgili sütundaki bilgiler sayısal değer olduğundan hata vereceğini sanmıyorum.hani benim sıkıntım da 40.000 satır için uzun formülün dosyayı yormasına yol açacağını düşündüğümden.zira zaten fazlasıyla formül mevcut ancak yine de önce bir adapte edip daha sonra diğer cevapları da test ederek farkları görmek istiyorum.Zannediyorum bunun yanıtını öğleden sonra anlayacağım.
İçten sevgilerimle.
 
Kullandığım Formülün açıklaması:

1. Tablo içindeki herhangi bir verinin adresini tespit etmek

Önce adres formülüne bakalım:

ADDRESS - ADRES

Verilen belirli satır ve sütun sayıları ile bir hücre adresini metin olarak oluşturur.

Sözdizimi:
ADRES(satır_sayısı; sütun_sayısı; mutlak_sayı; a1; tablo_metni)
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

Satır_sayısı hücre başvurusunda kullanılacak satır sayısıdır.
Sütun_sayısı hücre başvurusunda kullanılacak sütun sayısıdır.
Mutlak_sayı sonuç olarak verilecek başvurunun türünü belirler.
Mutlak_sayı Bu başvuru türünü verir
1 veya belirtilmemiş Mutlak
2 Mutlak satır; göreceli sütun
3 Göreceli satır; mutlak sütun
4 Göreceli

Tablodaki satır sayısını bulmak için:
SUM(IF(B2:I15=F24;ROW(B2:I15)))
Dizi formülünü veya;
SUMPRODUCT((B2:I15=F24)*(ROW(B2:I15)))
formülünü kullanabiliriz.

Aynı mantıkla bir de sütun sayısını bulmaya çalışalım:
SUM(IF(B2:I15=F24;COLUMN(B2:I15)))
Dizi formülü veya;
SUMPRODUCT((B2:I15=F24)*(COLUMN(B2:I15)))
Formülünü kullanabiliriz.

Şimdi bu formülleri
ADRES(satır_sayısı; sütun_sayısı; mutlak_sayı; a1; tablo_metni)
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

Temel formülde yerlerine koyalım.
=ADDRESS(SUM(IF(B2:I15=F24;ROW(B2:I15)));SUM(IF(B2:I15=F24;COLUMN(B2:I15)));4)
Dizi formülünü veya;
=ADDRESS(SUMPRODUCT((B2:I15=F24)*(ROW(B2:I15)));SUMPRODUCT((B2:I15=F24)*(COLUMN(B2:I15)));4)

Şeklini alır.
Bu formülle Tablo içindeki örneğin 182 verisinin adresi olan C8’i verir.

Benim aradığı değer J sutününda bulunmaktadır. O zaman C’yi J yapmam yeterli olacaktır.

2. Bunu yapmak için REPLACE fonksiyonundan yararlanabiliriz.
REPLACE - DEĞİŞTİR
Bir metnin yerine başka bir metin koyar.

Sözdizimi
DEĞİŞTİR(eski_metin; başlangıç_sayısı; sayı_karakterler; yeni_metin)
REPLACE(old_text,start_num,num_bytes,new_text)

Eski_metin, içinde bazı karakterleri değiştirmek istenilen metindir.
Başlangıç_sayısı, yerine yeni_metin'i koymak istenilen eski_metin'de karakterin konumudur
Sayı_karakterler, yerine yeni_metin'i koymak istenilen eski_metin'de karakterlerin sayısıdır
Yeni_metin, eski_metin'deki karakterlerin yerini alacak metindir

Bu formülü bunu içine yerleştirirsem, formül aşağıdaki şekli alacaktır.
=REPLACE(ADDRESS(SUM(IF(B2:I15=F24;ROW(B2:I15)));SUM(IF(B2:I15=F24;COLUMN(B2:I15)));4);1;1;"J")

Şeklinde bir dizi formülü veya,
=REPLACE(ADDRESS(SUMPRODUCT((B2:I15=F24)*(ROW(B2:I15)));SUMPRODUCT((B2:I15=F24)*(COLUMN(B2:I15)));4);1;1;"J")

Şeklinde olur. Bu formüller J8 gibi bir değer üretecek. Oysa bana J8’in içerdiği değer lazım.

3. Bu kez de INDIRECT fonksiyonundan yararlanabiliriz.

INDIRECT - DOLAYLI
Metin dizesiyle belirlenen başvuruyu verir. Başvurular, içerikleri görüntülenmek üzere derhal değerlendirilir. Formülün kendisini değiştirmeden formül içindeki hücre başvurusunu değiştirmek isterseniz, DOLAYLI fonksiyonunu kullanın.

Sözdizimi:
DOLAYLI(başv_metni;a1)
INDIRECT(ref_text,a1)

Formülümüzü bunun içine yazarsak;
=INDIRECT(REPLACE(ADDRESS(SUM(IF(B2:I15=F24;ROW(B2:I15)));SUM(IF(B2:I15=F24;COLUMN(B2:I15)));4);1;1;"J"))

Şeklinde dizi formülü veya;

=INDIRECT(REPLACE(ADDRESS(SUMPRODUCT((B2:I15=F24)*(ROW(B2:I15)));SUMPRODUCT((B2:I15=F24)*(COLUMN(B2:I15)));4);1;1;"J"))

Şeklinde bir formülle Tablodaki, 182 değerine J sütununda karşılık gelen 0024 sonucu bulunur.

.
 
merhaba
syn Yurttaş
ben ilk önce sizin formül üzerinde çalışmıştım ama becerememiştim.
açıklamanız için çok teşekkürler.
 
Sayın yurttas, muhtesem çözümünüzü yine muhteşem açıklamanızla süslemişsiniz tebrik ve teşekkür ediyorum.Açıklamanız öyle güzel ki en ufacık soru işareti dahi kalmadı aklımda...
Sayın uzman uzmanamele her iki çözümü de mevcut dosyaya uyguladım ancak sumproduct daha iyi çözüm oldu çünkü sayın yurttasın çözümü ki (derslik bir çözüm bence) dosyayı epeyce yordu.
Sayın yurttas,sayın Ali ve Sayın uzmanamele katkılarınızdan ötürü tekrar teşekkür ediyorum.
Saygılarımla.
 
merhaba
okadar uğraştık, vazgeçiremedik desenize :) :)
iyi çalışmalar.
 
Bu da başka bir yöntem olsun,

4 tane 182 varsa bunlara karşılık gelen tüm değerleri bulur.
 
Dosya normalde 234 Kb idi sumproduct'le birlikte 2,8 MB sayın yurttasın formülü ile 8,9 MB oldu...bir nevi çaresizlik durumu...:)
 
Yukarıdaki eklediğim formüle göre boyut nedir?
 
Sayın Ali, sizin formülünüz yeni uyarladım ve 7,5 mb oldu...Aslında şunu da sanırım eklemeliydim mevcut formülleri ISERROR ile kullandığım için olduklarından daha uzun oldular bunun da etkisi büyüktür boyut açıksından zannediyorum.
Sevgilerimle.
 
Cevap yazmak için giriş yap yada kayıt ol.
Geri
Üst