Şartları sağlayan satır numaralarını bulma

[modoste]

merhabaların sayın hocalarım ekleyeceğim dosyada
daha önce bu dosya ile ilgili soruma cevaplamalar yapılmıştı çok kullanışlı bi çizelge oldu ama ben formülasyondan dolayı bana çok ağır geliyo anlaması
ben kendimce aynı sonuçları almak için;
ekli dosyada O16:O51 arasına (mavi renkli yer) nasıl bi formül yazmalıyımki
P16 :X16 dan başlayan mükemmel sonuç çizelgesine diğer bi yönden ulaşmak için
koşulları sağlayan satır numaralarını buldurup İNDİS yada Düşeyara ile aynı sonuç tablosuna ulaşayım.

 

[Ömer]

Merhaba,

Doğrumu anladım bilmiyorum,

P16 hücresindeki formülün aynısını O16 hücresine yazarak,

İNDİS(B$16:B$201; başındaki bu ibrayi silerek yazınız. Oluşan yeni formülüde Dizi formülüne çevirmeyi ihmal etmeyiniz..

İstediğiniz bu olabilir mi? Bu değilse olması gereken değerleri tablo üzerinde manuel yazarak nerden geldiğinide açıklarsanız sonuca ulaşmamız daha kolay olacaktır.

.

 

[modoste]

Hocam teşekkür ederim dediğinizi yaptım istediğim satır numaraları sırayla yazıyo
sonuç olmayanlarda #SAYI yazıyo
hocam daha öncede bu tarz bi formülün açıklamasını yaptınız fakat ben hep bocalıyorum tam kavrayamıyorum
iki isteğim olcak sizden
1.cisi bu buldurduğumuz listedeki sıraları diyelim başka değişik bi formüller yapabiliyomuyuz.
mesela TOPLA.ÇARPIM ile
2. cisi az önceki formülün anlatıını bir daha yapabilirmisiniz
=KÜÇÜK(ile başlarken eğer iele aralıklardaki koşulları dizide belirtip .........)

 

[modoste]

Hocam tekrardan anlatabilrmisiniz demiştim ama gerek yok yazmanıza
biraz anlamış gibi oldum bu sorumda O sutununu satır numaralarını buldurmada kullanınca daha iyi oldu bende indis formülüyle tabloyu ve sonucu oluşturmuş oldum.
başka bi formülle bu dizideki(listedeki) koşullara uyan satır numaraları bulunurmu yu öğrenmek istiyorum

 

[Ömer]

Bu tabloyu size ben hazırlamıştım sanırım.

P sütunundaki formülde de sonuç olmayınca #SAYI yazıyor fakat bu hatayı koşullu biçimlendirme ile halledilmiş. Aynı şekilde O sütununuda halledebilirsiniz, yada sağlanan koşulların sayısını bularak bu işi halledebiliriz.

=eğer(satırsay($o$16:16)>sayı;"";formül) gibi. Diğerinde biçimlendirme kullanıdığınız için aynı şekilde bunu çözmenizi tavsiye ederim, eğer eğer ile yapalım derseniz,

sayı dediğim yer için,

=TOPLA.ÇARPIM((ESAYIYSA(KAÇINCI(METNEÇEVİR($B$16:$B$201;"aaaa");$P$2:$P$13;0))*(ESAYIYSA(KAÇINCI($C$16:$C$201;$Q$2:$Q$13;0))*(ESAYIYSA(KAÇINCI($D$16:$D$201;$R$2:$R$13;0))*(ESAYIYSA(KAÇINCI($E$16:$E$201;$S$2:$S$13;0)))))))

formülünü bir hücreye yazarak sayı yerine hücre adını yazınız..

=eğer(satırsay($o$16:16)>$o$1;"";formül) gibi..

tablo topla.çarpım formülüne uygun bir çalışma değidir, yaparsak bile emin olun daha karışık olur. Zaten yukardaki formülde aynı ibreleri topla.çarpım da kullanarak koşulları saydırdık. topla.çarpım fonksiyonuna hakimseniz yukardan olayı çözebilirsiniz..

Formülün mantığını uzun uzun anlatmam birşey ifade etmez sanırım. Zaten sorudan istediğinizi siz biliyorsunuz bu yönden giderseniz sizde anlarsınız.

1. mesajdaki sorunuzda satır sayısını bulursam indis kullanırım demişsiniz, mantık aynı işte. Formülünde zaten indis kullanılmıştır. Hangi satıra denke geldiğini bulmak için diğer kısımlar yazılmıştır. Kısaca açıklamaya çalışayım.

formülün iskeleti,

=indis(aralık,satır indisi)

aralık zaten belli olduğu için sorun yok, önemli olan şartları sağlayan satır numarasını bulmaktır,

KAÇINCI($C$16:$C$201;$Q$2:$Q$13;0) bu parça sizin verdiğiniz 4 aralıktada aynı mantıkla kullanılmıştır.


formülde C16:C201 aralığında Q2:Q13 değerleri var olup olmadığını buluruz.

bu formül sonucunda olmayan değerler hata sonucu vereceği için esayıysa ile bu hatalı değerler 0 ( yanlış ) olarak diziye girer.

kaçıncıdan oluşan dizi örneği,

{2;3;2;3;5;7;2;3;#YOK;2;3;#YOK...} gibi.

bu diziye esayıysa eklenince hatalı değerlerden kurtulmuş oluruz.

İşte bu şekilde oluşturduğumuz 4 ayrı aralık için koşul bir biriyle çarpılarak yeni bir dizi oluşturur. Dizinin son halinin,

{doğru;yanlış;doğru;yanlış;doğru;doğru;doğru;yanlış;yanlış;yanlış;....}

şeklide oluştuğunu düşünürsek, eğer fonsiyonun sonudaki satır(aralık:aralık)-satır(aralık)+1 ile;

{1;2;3;4;5;...................} satır dizisi oluştururuz. Kırmızı dizide doğru olanların mavi diziden karşılğı olan satır numaraları gelir yanlış lar aynı şekilde yanlış olarak diziye girer.

{1;2;yanlış;4;5;yanlış;7...}

küçük fonksiyonunu kullanarak bu dizideki sırası ile küçük olan değerleri alırız.

küçük(dizi;satırsa($p$16: p16)) gibi. Burdaki satırsay formülü alt hücrelere kopyaladıkça 1 - 2 - 3...şeklinde artar buda;

küçük(dizi;1) - küçük(dizi;2)... gibi her satırda dizideki küçük satırdan başlayarak değerleri alır. Bu şekilde her satırda istediğimiz değeri bulmuş oluruz. Küçük değilde büyük kullanılsaydı tablonun veri düzeni tersten sıralanırdı.

indis formülü ilede bulunan satır indislerinin karşısındaki değerler alnır.

Formülllerin mantığını daha kolay anlamak için uzun ve karmaşık formüllerden değil kısa sade olan formüllerin mantığını anlamaktan başlamalısınız..

.

 

[modoste]

hocam bi kaç ay önce mesaja sizin hazırladığınız belirttiğimde sayın ali hocam yanlış anlamıştı
bende öyle bi yanlış anlamaya meyil vermemek için sizin adınızdan bahsetmedim
evet sizle beraber yine çok yormuştum sizi
bu tarz çözümleri alacağım çizelgeleri kullanmaya başladım.
Tekrar teşekkür ederim sizlere

 

[Ömer]

Rica ederim. Ayrıca isim belirttin demek istemedim tabloyu hatırladığım için o şekilde yazdım.

İyi çalışmalar..

.

 

[modoste]

eğer(satırsay($o$16:16)>$o$1;"";formül) O16 hücresine bu formülü yazıyorum ben $O$1 olan yeri $O$2 hücresi olarak değiştim yani koşula uyan dizideki satır sayısını buldurdum
ama O16 hücresine bu formülü yazoyrum aşağıya doğru çekiyorum hepsinde 9 cevabı çıkıyo
9 cevabıda O2 deki cevap
formül olan değerde sizin belirttiğiniz topla.çarpımlı uzun ifade ama ben nereyi anlayamadım bilemiyorum

 

[Ömer]

formülün tamamınız yazarmısınız..

.

 

[modoste]

O16 hücresine şu formülü yazdım
"=EĞER(SATIRSAY($O$16:O16)>$O$2;"";TOPLA.ÇARPIM((ESAYIYSA(KAÇINCI(METNEÇEVİR($B$16:$B$201;"aaaa");$P$2:$P$13;0))*(ESAYIYSA(KAÇINCI($C$16:$C$201;$Q$2:$Q$13;0))*(ESAYIYSA(KAÇINCI($D$16:$D$201;$R$2:$R$13;0))*(ESAYIYSA(KAÇINCI($E$16:$E$201;$S$2:$S$13;0))))))))


O2 hücresindede sizin 5. mesajdaki yazdığınız
=TOPLA.ÇARPIM((ESAYIYSA(KAÇINCI(METNEÇEVİR($B$16:$B$201;"aaaa");$P$2:$P$13;0))*(ESAYIYSA(KAÇINCI($C$16:$C$201;$Q$2:$Q$13;0))*(ESAYIYSA(KAÇINCI($D$16:$D$201;$R$2:$R$13;0))*(ESAYIYSA(KAÇINCI($E$16:$E$201;$S$2:$S$13;0))))))) var

 

[Ömer]

O16 hücresine topla.çarpım kullanın demedimki,

O16;

=EĞER(SATIRSAY($O$16:O16)>$O$2;"";P16 daki formülün =İNDİS(B$16:B$201; kısmınız silerek yazmanız gerekir.

.

 

[modoste]

Hocam O16 hücresine nasıl bi formül yazalım ki şartların doğrualndığı satır numaralarını alt alta sıralasın o formülü topla çarpımla bulmak istiyorum tablonun tamamını değil şu anda tablonun tamamını doldurmak değil koşulları sağlayan satır numaralarını O16 dan başlayarak alt alta yazmalı topla. çarpım formülü yada diğer bi formül ile
ilk çözümü anladım onda sorun yok

 

[Ömer]

Bende aynı formülü yazıyorum zaten, P16 daki formülün =İNDİS(B$16:B$201;başındaki kısmı silip gerisini yazın demiştim.

O16;

=EĞER(SATIRSAY($O$16:O16)>$O$2;"";KÜÇÜK(EĞER(ESAYIYSA(KAÇINCI(METNEÇEVİR($B$16:$B$201;"aaaa");$P$2:$P$13;0));EĞER(ESAYIYSA(KAÇINCI($C$16:$C$201;$Q$2:$Q$13;0));EĞER(ESAYIYSA(KAÇINCI($D$16:$D$201;$R$2:$R$13;0));EĞER(ESAYIYSA(KAÇINCI($E$16:$E$201;$S$2:$S$13;0));SATIR($C$16:$C$201)-SATIR($C$16)+1))));SATIRSAY($P$16:P16)))

Dizi formülü.

.

 

[modoste]

Hocam ben bu formülle olan çizelgeyi tamamladım.
acaba bu formül değilde başka bi formül ile
topla.çarpım gibi yada dahada başka varsa buldurabilirmiyiz.
sizi yordum biraz kusura bakmayın

 

[Ömer]

İstediğinizi yapmak maceraya kaçar. Denemedim fakat satırın kaçıncı sırada olduğunu bulma için bu tabloda topla.çarpım kullanmak bana göre mantıklı olmaz emin olun formül dahada uzar. topla.çarpıma neden bu kadar takıldığınızı anlamadım. topla.çarpımda dizi oluşturur bu fonksiyonda topla.çarpımın mantığını anlayabiliyorsanız bunuda anlamanız gerekir.

O2 ye yazılan topla.çarpım ile küçük fonksiyonun içindeki eğerli formülün tamamına yakını aynıdır.

.

 

[modoste]

ben alternatif çözümlerde öğrenmek istediğimden sormuştum
İlginizden dolayı tekrar teşekkür ettim sayın hocam

 

[Necdet]

Merhaba,

Makrolu çözüm isterseniz aşağıdaki kodları deneyininiz.

Sub Aktar()
Dim i As Long, j As Long
Dim c As Range
Dim Ay As Integer
Dim Aylar()
Aylar = Array("", "OCAK", "ŞUBAT", "MART", "NİSAN", "MAYIS", "HAZİRAN", "TEMMUZ", "AĞUSTOS", "EYLÜL", "EKİM", "KASIM", "ARALIK")
Application.ScreenUpdating = False
j = 15
Range("P16:X65").ClearContents
For i = 16 To [B65536].End(3).Row
    Ay = Month(Cells(i, "B"))
    Set c = Range("P2:P13").Find(Aylar(Ay), LookIn:=xlValues)
    If c Is Nothing Then GoTo Devam
 
    Set c = Range("Q2:Q13").Find(Cells(i, "C"), LookIn:=xlValues)
    If c Is Nothing Then GoTo Devam
 
    Set c = Range("R2:R13").Find(Cells(i, "D"), LookIn:=xlValues)
    If c Is Nothing Then GoTo Devam
 
    Set c = Range("S2:S13").Find(Cells(i, "E"), LookIn:=xlValues)
    If c Is Nothing Then GoTo Devam
 
    j = j + 1
    Range("B" & i & ":J" & i).Copy
    Range("P" & j).PasteSpecial xlPasteValues
Devam:
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
[O16].Activate
End Sub

 

[modoste]

sayın nejdet hocam çözüm için teşekkür ederim
aça tıkla düğmesine basınca çalıştıramadım bir yerlerden güvenlik düzeyi ile ilgili değşim yapıyoduk herhalde onu bulamadım yardımcı olurmusunuz

 

[Ali]

hocam bi kaç ay önce mesaja sizin hazırladığınız belirttiğimde sayın ali hocam yanlış anlamıştı
bende öyle bi yanlış anlamaya meyil vermemek için sizin adınızdan bahsetmedim

O formülleri bu siteye ilk kazandıran kişi olduğumu özel mesajla belirttim. Hala sizin hazırladığınızda söylediğimde Ali Bey yanlış anladı diyorsunuz. Formüllerin hızlı nasıl çalışacağı hakkında birde ipucu verip size gönderdim.
İsim vermemenizi istemedim hala aynı hataya devam ettirip açtığınız bu konuya yine adımı veriyorsunuz.

Hala neden bu şekilde davrandığınızı görünce üzülmekten başka birşeyde elimden gelmiyor.


.

 

[modoste]

Sayın Ali hocam bu konuda galiba karşılıklı yanlış anlama falan var
ben 1 senedir bu forumdan ve sizlerden bilgilerinizden faydalanıyorum ve devam ediyorum faydalanmaya
Kişileri ön plana atacak tarzda bi yaklaşımda bulunmadım
Lüfen sizlerde böyle düşünmeyin
ben bilgi paylaştığım belkide geleceğimle işimle ilgili bilgileri pekiştirdiğim bu internet ortamında
bu tarz kıyaslama yada ne bilim ona benzer davranışlar sergilemek değil amacım
hem neden yapayım ki sizleri zaten imrenerek izliyorum ah keşke onların seviyelerine gelebilsek gibi şeyler düşünüyorum.
Art niyetli biri değilim lütfen hocam ben 30 yaşında inş. mühendisiyim
sizler gibi değerli hocalar tanımışım kendimi geliştirmişim. Asla sizleri kırmak üzmek istemem
Bunu lütfen anlayın
Tekrardan özür dilerim yanlış anlamalara sebebiyet verdiğim için