Sn
@Believing
Öncelikle, çok teşekkür ediyorum...
Formülün açıklamasını yazacak olursam;
** D - E - F kolonlarında koşulların arattırılacağı verileriniz var...
** P - Q - R kolonlarında ise koşul olarak aranılacak verileriniz var... Ancak, bu kolondaki verilerde, her 8 satır STATÜ verisi için TEK bir satır belirlenmiş. (Bu belirlenmiş satır 8 satır için Birleştirilmiş Hücre olarak da uygulanabilirdi.. formül bu durumda da çalışacaktır)....
** H kolonunda ise, S kolonuna göre eşitliği arattırılacak Statü kısaltmaları mevcut.
** S kolonunda da aranılacak KOŞUL Statü verileriniz mevcut...
Formül :
=EĞERHATA(1/(1/TOPLA.ÇARPIM(($H$8:$H$500<>"")*($D$8:$D$500&"#"&$E$8:$E$500&"#"&$F$8:$F$500=ARA(2;1/($P$8: P8<>"");($P$8: P8))&"#"&ARA(2;1/($Q$8:Q8<>"");($Q$8:Q8))&"#"&ARA(2;1/($R$8:R8<>"");($R$8:R8)))*(PARÇAAL($H$8:$H$500&"_";1;UZUNLUK(S8)+1)=S8&"_")));"")
Parça parça gidersek
$D$8:$D$500&"#"&$E$8:$E$500&"#"&$F$8:$F$500
D-E-F kolonlarındaki verileri Birleştiriyoruz.... Birleştiriken de aralarına kolonlarda bulunmayan bir karakter ekliyoruz....
Örnek :
D8, E8 ve F8 hücrelerini ele alalım...
D8 : GARSON 1
E8 : GARSON 1 / 2
F8 : MAIN REST
Birleştirme formülü ile 8. satırdaki verinin birleştirilmiş hali
GARSON 1#GARSON 1 / 2#MAIN REST
elde edilecektir....
Burada # karakterinin önemini bir örnekle verecek olursak
D8 : Ali
E8 : Can
F8 : Arslan
olsun.... # gibi bir karakter kullanmazsak ve D8&E8&F8 şeklinde yazarsak sonuç : AliCanArslan olacaktır.
Şimdi koşul hücrelerimizde, yani P8, Q8 ve R8 hücrelerinde
P8 : Ali
Q8 : Canarslan
R8 : BOŞ bırakılmış hücre
P8&Q8&R8 dediğimizde de sonuç : AliCanarslan olacaktır.
D8&E8&F8 = P8&Q8&R8 eşitliğini yazarsak sonuç DOĞRU olur. Yani her ikisi birbirine eşittir.
Oysa ki, # işaretini kullanırsak
D8"#"&E8"#"&F8 : Ali#Can#Arslan
P8"#"&Q8"#"&R8 : Ali#Canarslan
D8"#"&E8"#"&F8 = P8"#"&Q8"#"&R8 eşitliğini ararsak Ali#Can#Arslan ≠ Ali#Canarslan olacağından sonuç YANLIŞ olacaktır.
Aradığımız olay budur.... D8 = P8 , E8 = Q8 ve F8 = R8 olmalı...
Birleştirilmelerde o nedenle # gibi kolonlarda bulunmayan ÖZEL bir işaret aralara konularak birleştirilmeli ki biz bunu uyguladık...
Formülümüzdeki ikinci kısım
ARA(2;1/($P$8: P8<>"");($P$8: P8))&"#"&ARA(2;1/($Q$8:Q8<>"");($Q$8:Q8))&"#"&ARA(2;1/($R$8:R8<>"");($R$8:R8))
P- Q - R kolonlarında her bir 8 satır için TEK bir satır veri olup, 8. satırdaki P8 - Q8 - R8 verisi P15 - Q15 - R15 olarak 15. satıra kadar geçerlidir. Diğer bir deyişle, 8 satırdan 15. satıra kadar Hücre Birleştir uygulanabilir..... (Formülde 8 satır olarak bir koşul yoktur. 10 da olabilir 3 de olabilir fark etmez)....
ARA(2;1/($P$8: P8<>"");($P$8: P8)) : formülü ile P8 den başlayıp, P8 : P8 , P8 : P9, P8: P10..... şeklinde giden bir aralıkta, SON Dolu değeri arattırıyoruz....
P8 dolu olduğundan, ve P15 e kadar BOŞ olduğundan 15. satıra kadar değerlendirildiğinde P8 : P15 BOŞ değilse P8 : P15 deki dolu değer olarak P8 de yazan değeri verecektir. Yani, örneğin P13 e bakalım... P13 Boş, P12 Boş....P9 Boş, P8 Dolu..... Öyleyse P13 de ARA(2;1/.... işlevinin sonucu P8 deki değerdir.....
Aynı şekilde, ARA(2;1/($Q$8:Q8<>"");($Q$8:Q8)) formülü ile Q kolonunda ve ARA(2;1/($R$8:R8<>"");($R$8:R8)) işlevi ile de R kolonunda , aynen P kolonunda yaptığımız gibi SON Dolu değer arattırması yapıyoruz....
Bulduğumuz bu SON değerleri de aralara # karakterini koyarak Birleştiriyoruz.....
Aradığımız eşitlik olayı , D8 = P8 , E8 = Q8 ve F8 = R8 gibi olduğundan, # karakterleri ile birleştirerek
D8"#"&E8"#"&F8 = P8"#"&Q8"#"&R8 biçimini ve tüm kolonda yazarsak da
formülümüzdeki
($D$8:$D$500&"#"&$E$8:$E$500&"#"&$F$8:$F$500=ARA(2;1/($P$8: P8<>"");($P$8: P8))&"#"&ARA(2;1/($Q$8:Q8<>"");($Q$8:Q8))&"#"&ARA(2;1/($R$8:R8<>"");($R$8:R8)))
kısmını yazmış oluyoruz....
Formülümüzün diğer kısmı ise,
"_" karakterinin hemen önündeki kısmı H kolonunda aratarak, S kolonunda yazan Statülerle olan EŞİTLİĞİ sağlayanları bulmak.
Bunun için yazdığımız formül parçası
PARÇAAL($H$8:$H$500&"_";1;UZUNLUK(S8)+1)=S8&"_" şeklinde....
Örnek vererek açıklamaya çalışırsam,
H10 : A_RT yazmakta
S8 de ise A yazıyor ve ilk karakteri A olanı arattığımızı düşünelim.....
H10 ilk karakteri A dolayısıyla ilk karaktere bakarsak, H10 = S8 yani DOĞRU olmakta.....
Ancak H8 de A-OFF yazmakta... Onun da ilk kararkteri A.... Ama biz onu saymak istemiyoruz... Çünkü A-OFF başlıca bir STATÜ ve A Statüsünden Farklı...
O halde, ilk karakteri değil, ilk 2 karakteri (A arattığımız için 2 karakter , A-OFF için ise 6 karakter) almamız gerekmekte....
O zaman da şöyle bir problemimiz olacak... A, K, C... gibi TEK karakter yazan hücrelerde ilk 2 karakter nasıl olacak ve bu TEK karakterli hücrelerde de "_" karakteri mevcut değil. Aynı zamanda A-OFF gibi yazan hücrlerde de "_" karakteri mevcut değil.
O nedenle, arattırmamızı, H kolonunda ne yazarsa yazsın sonlarına "_" karakteri ekleyerek yapmalıyız,,,,, yani formüldeki $H$8:$H$500&"_" kısmı...
Burada;
eşitliği H10 a göre yazıp, S8 deki statüyü aradığımızı kabul edersek
PARÇAAL($H10&"_";1;UZUNLUK(S8)+1)=S8&"_"
H10 da A_RT yazmakta, S8 ise A
PARÇAAL("A_RT"&"_";1;UZUNLUK("A")+1)="A"&"_" = PARÇAAL("A_RT_";1;1+1)="A_" = PARÇAAL("A_RT_";1;2)="A_"ve sonuçta "A_" = "A_"
ve DOĞRU olmakta.... (Aradığımız durum)
eşitliği H8 e göre yazıp, S8 deki statüyü aradığımızı kabul edersek
PARÇAAL($H8&"_";1;UZUNLUK(S8)+1)=S8&"_"
H10 da A-OFF yazmakta, S8 ise A
PARÇAAL("A-OFF"&"_";1;UZUNLUK("A")+1)="A"&"_" = PARÇAAL("A-OFF_";1;1+1)="A_" = PARÇAAL("A-OFF_";1;2)="A_" ve sonuçta "A-" = "A_"
ve YANLIŞ olmakta.... (Koşulu sağlamayan durum)
Formüldeki diğer kısımları da yazarsak,
($H$8:$H$500<>"") : H kolonu BOŞ olmamalı (Bizim formülde, eşitlikleri aradığımızdan bu kısma gerek yok.. Ancak ben alışkanlıktan dolayı hemen ekliyorum bunu)
EĞERHATA(1/(1..... kısmı ile de formül sonucu 0 olduğunda "" (BOŞ) yazması sağlanmakta.....
İsteğiniz....
D kolonu eşitlikten laldırıldığında formülümüz ne olacak?
Birleştirilmeli kısımda D kolonu eşitliği P kolonu ile aranmakta.... onun kaldırılması ile formül kurulabilir... Yani
=EĞERHATA(1/(1/TOPLA.ÇARPIM(($H$8:$H$500<>"")*($E$8:$E$500&"#"&$F$8:$F$500=ARA(2;1/($Q$8:Q8<>"");($Q$8:Q8))&"#"&ARA(2;1/($R$8:R8<>"");($R$8:R8)))*(PARÇAAL($H$8:$H$500&"_";1;UZUNLUK(S8)+1)=S8&"_")));"")
şeklinde yazılabilir..
