Excel Düşey Ara ve Formül Yineleme

[wanted88]

Merhaba,

Aşağıdak Excel'de zorlandığım iki konu hakkında sorularım bulunmaktadır. Yardımlarınızı rica ederim.

Düşey ara formülünde aranan değer kısmında "01" yazanları içerenleri çekmek istiyorum. Nasıl çekebilirim?
=DÜŞEYARA(aranan_değer,tablo_dizisi,sütun_indis_sayısı,aralık_bak)

Sutunlarda örnek olarak aşıdaği bilgiler bulunmaktadır;
1.sutundaki - 2.sutun - 3.sutun
01,05,07 - deneme111 - evrak no111
02,03,05 - deneme222 - evrak no222
01,04,05 - deneme333 - evrak no222

Diğer sorum ise, excelde her satırda aynı formülleri yazıyorum fakat her zaman en üst satırda bulduğu bilgileri bulup alt satırdaki bilgilere geçmiyor. Bu sorunu çözecek formülasyon var mıdır? (hep deneme111'i bulmaktadır, deneme333'e geçmemektedir.)

 

[kan-nas]

Sonuncuyu bulmak istersen makro ile yapılır. Formul her zaman 1. yi Makro her zaman sonuncuyu gösterir.

 

[Hepgel]

Merhaba @wanted88

Birinci satır başlık satırıysa ve veriler A2:C4 aralığında ise, aşağıdaki formül,
dizi formülüne dönüştürülerek sonuç alınabilir. Formülü ilk hücreye uygulayıp aşağı doğru çekin.
Ctrl + Shift + Enter ile formülleri tamamlayınız.

=EĞERHATA(KAYDIR($A$1;KÜÇÜK(EĞER(ESAYIYSA(BUL("01";$A$2:$A$4));SATIR($A$2:$A$4));SATIR(A1))-1;2);"")

 

[wanted88]

Merhaba,

Cevaplar için teşekkür ederim. Gönderdiğiniz kodları tam olarak kullanamadım. Aşağıdaki linkte sorunu anlatan Excel dosyasını ekledim. Yardımlarınızı rica ederim.

Sorunu anlatan excel dosyası

 

[leguminosea]

Hata

Hata

www.dosyaupload.com

Örnek dosya ektedir

 

[kan-nas]

Merhaba @wanted88

Birinci satır başlık satırıysa ve veriler A2:C4 aralığında ise, aşağıdaki formül,
dizi formülüne dönüştürülerek sonuç alınabilir. Formülü ilk hücreye uygulayıp aşağı doğru çekin.
Ctrl + Shift + Enter ile formülleri tamamlayınız.

=EĞERHATA(KAYDIR($A$1;KÜÇÜK(EĞER(ESAYIYSA(BUL("01";$A$2:$A$4));SATIR($A$2:$A$4));SATIR(A1))-1;2);"")

inglizcesi nedir?

 

[Hepgel]

inglizcesi nedir?

=IFERROR(OFFSET($A$1;SMALL(IF(ISNUMBER(FIND("01";$A$2:$A$4));ROW($A$2:$A$4));ROW(A1))-1;2);"")

 

[AdemCan]

Alternatif olarak

Table Sayfası

A5 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)
B5 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)
C5 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)

Formüllerini ekleyiniz.

Not: Formüller Dizi Formülüdür. Ctrl + Shift + Enter tuşlarına birlikte basarak, formüllerin başına ve sonuna { } parantezlerinin gelmesini sağlayınız.

Diğer alanlar için formüllerde değiştirilecek olan bölümler
SAĞDAN($A$3;3) ve SATIR($A$4) kısımlarındaki koyu renk ile belirlenen sayılar.

Örnek olarak CHAPTER 002 için
SAĞDAN($A$3;3) kısmını SAĞDAN($A$10;3)
SATIR($A$4) kısmını SATIR($A$11) şeklinde değiştirmelisiniz.

Diğer alanlar içinde bu kısımları değiştirerek kullanabilirsiniz.

 

[wanted88]

Alternatif olarak

Table Sayfası

A5 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)
B5 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)
C5 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)

Formüllerini ekleyiniz.

Not: Formüller Dizi Formülüdür. Ctrl + Shift + Enter tuşlarına birlikte basarak, formüllerin başına ve sonuna { } parantezlerinin gelmesini sağlayınız.

Diğer alanlar için formüllerde değiştirilecek olan bölümler
SAĞDAN($A$3;3) ve SATIR($A$4) kısımlarındaki koyu renk ile belirlenen sayılar.

Örnek olarak CHAPTER 002 için
SAĞDAN($A$3;3) kısmını SAĞDAN($A$10;3)
SATIR($A$4) kısmını SATIR($A$11) şeklinde değiştirmelisiniz.

Diğer alanlar içinde bu kısımları değiştirerek kullanabilirsiniz.

Merhaba,

Öneriniz için teşekkür ederim. Paylaştığınız kodlar ile "001" ile başlayanları yaptım ama diğer kısımlar için yapamadım. Örneğin; INFORMATION sayfasındaki "003" içerenleri çektirmeye çalıştığımda hatalar çıkmaya başladı, çünkü 4.satır "003" ikinci sırada alırken 20.satırda üçüncü sıra yer almaktadır. "Eğer" ve "parçaal" komutlarını çoğaltmaya çalıştım ama olmadı. Yardımlarınızı rica ederim.

 

[çıtır]

Yardımçı sütun kullanılarak oluşturulmüş çözüm.Dosyanız.

İndir DOSYAÇEK

Dosyayı indir DOSYAÇEK

dosya.co

 

[AdemCan]

İlgili formüller.
Hepsi Dizi Formülüdür.
Ctrl + Shift + Enter tuşlarına birlikte basarak, formüllerin başına ve sonuna { } parantezlerinin gelmesini sağlayınız.

A5 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)
B5 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)
C5 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$3;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$4)));0)

A12 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$10;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$11)));0)
B12 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$10;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$11)));0)
C12 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$10;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$11)));0)

A20 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$18;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$19)));0)
B20 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$18;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$19)));0)
C20 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$18;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$19)));0)

A27 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$25;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$26)));0)
B27 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$25;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$26)));0)
C27 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$25;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$26)));0)

A34 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$32;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$33)));0)
B34 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$32;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$33)));0)
C34 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$32;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$33)));0)

A41 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$39;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$40)));0)
B41 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$39;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$40)));0)
C41 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$39;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$40)));0)

A50 hücresine =EĞERHATA(İNDİS(INFORMATION!$B$1:$B$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$48;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$49)));0)
B50 hücresine =EĞERHATA(İNDİS(INFORMATION!$C$1:$C$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$48;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$49)));0)
C50 hücresine =EĞERHATA(İNDİS(INFORMATION!$D$1:$D$10;KÜÇÜK(EĞER(PARÇAAL(INFORMATION!$A$4:$A$10;1;3)=SAĞDAN($A$48;3);SATIR(INFORMATION!$B$4:$B$10));SATIR()-SATIR($A$49)));0)

 

[ataköylü-59]

Sayın wanted88
Bende bir çalışma yaptım. Umarım istediğiniz gibi olmuştur. Eğer sorunuzu doğru anlamış isem, hem formülle ve hem de Özet Tablo ile bir çözüm sundum.

İndir DOSYAÇEK

Dosyayı indir DOSYAÇEK

dosya.co