satırlardan sütunlara kriterlere göre veri aktarmada devrik dönüşüm yetersiz kaldı

[ugurgur01]

Selam forumdaki uzman arkadaşlar ve üye arkadaşlar.
Ekteki excel tablomda satırlardan sütunlara veri aktarmada excel bilgilerim yetersiz kaldı. Veri alınacak dosyada satırlarda parça kodu ve tarih sırasına göre karşılığında sipariş miktarları var. veri alınacak dosyayı bu formdan edindiğim bilgilerle yaptım bu konuda tüm forum uzmanlarına tesekkür ederim. satırlarda boş hücreler -ki bazen metin karakteri gösteriyor- ve sıfır ve sıfırdan büyük sayılar var.

amacım, bu satırlardaki sıfırdan büyük sayıları diğer veri aktaracağım (x isimli) excel sayfasına parça kodu, sıfırdan büyük sayılar ve karşılığı olan tarihleri sütun olarak çekmek ve bu arada parça kodu sütununda sıfırdan büyük sayı olan hücre miktarı kadar altalta sıralatmak, karşılarına da sipariş miktarı olan sayıları ve sip. tarihlerini veri olarak çekmektir.

devrik dönüşüm ile yaptığımda sıfır ve diğer boşluk hücre ve satırlarda diğer sayfaya aktarıyor. BU boşluk ve sıfırları teizlemek çok zaman alıcı oluyor.bu nedenle birçok formül denedim. ama pek başarılı olamadım.
abzı formülleri denediğimde aynı sayısal rakamların karşısına doğru tarihleri bulduramadım. denemelerimde ekdosyada bulunuyor.

bu konuda yol gösterecek ve çözüm önerilerinde bulunacak uzman arkadaşlara şimdiden tesekkür ederim.

 

[muygun]

Merhaba;
Tablonuzu inceledim. Sizin için görsellik ve kullanım kolaylığı sağlayan tablo yapınızdan verileri istediğiniz gibi tasniflemek (yerleşik işlevlerle) oldukça zor.
Tablo yapınızı bozmadan gelebilecek bir çözümü beklerken tablo yapınızı değiştirmeyi düşünürseniz eki inceleyin. (Tablonuzdan 4 günlük verilerinizi alarak sade bir süzme oluşturdum. Kullanılabilirliğini onaylarsanız isteğinize göre gereken düzenleme ve değişiklik yapılabilir.)
İyi çalışmalar.

 

[ugurgur01]

Merhaba Sn. muygun,

gerçekten gösterdiğiniz ilgi ve emk için tesekkür ederek başlamak istiyorum.Bilginize, emeginize ve haecadığınız zamana çok teşekkür ederim. Tablonuzu dikkatle inceledim ve kullandığınız formüller, süzme mantığı için geliştirdiğiniz alternatif yaklaşımlar ve hatta kosullu biçimlendirme ile yaptığınız farklı parçaların ara bölümleri hepsi birçok excel kullanıcı arkadasa örnek olacak üst seviyede örnekler içeriyor.

yaptığınız çalışmadaki dosyada "veri" sayfasından süzerek belli tarih ve parça nosuna göre "sonuç" sayfasına aktarım belli bir aşamadan sonra mutlaka kullabileceğim bir yaklaşım.

Üzülerek belrmeliyim ki, tablo yapılarımı değiştirme olanağım yok. zira benim ugur_örnek dosyasındaki "SİP15wk" sayfasındaki tablo, 15 açık excel tablosundan derlenmiş bir sonuç tablosu, bunu değiştiremiyorum. "x" dosyasıda MRP sistemine aktarma yapacağım ve formetını değiştiremeyeceğim bir dosya. Benim x dosyasını aslında boş düşünerek öncelikle bu dosyaya "SİP15wk" dosyasındaki satırlardan elde ederek sadece 3 sütunda, yani siin éveri sayfanızdaki hale getirmem gerekiyor. "SİP15wk" sayfasındaki sarı sütunlar birçoğu boş olmasına karşın o sütunlarda eksinleşmiş siparişlerin bilgileri için kullnılıyor, bazı hücrelerde "1 k
K sirVAr" gibi notlar bunu gösteriyor.

ilk adımımız, satırlardan "x" dosyasındaki 4 sütunaa veri çekmeyi "0" ve boş hücreleri atlatarak sağlamak olcak, ardından sizin çözümünüz çok işe yarayacak. Bu aşamayı yapmanın zor oldugunu ben de biliyorum ve değişik uzman arkadaslardan bu konuda yardım ev öneriler beklemeye devam edeceğim.

benim "deneme tahtası" isimli sayfamda dikkatimi çeken unsur, V12 hücresinde kullandığım
İNDİS($U$5:$EM$5;0;KAÇINCI(DOLAYLI(ADRES(SATIR($U$8:$EM$8);KÜÇÜK((EĞER($U$8:$EM$8<>"";EĞER($U$8:$EM$8<>0;SÜTUN($U$8:$EM$8);SÜTUN()+SÜTUNSAY($U$8:$EM$8))));SATIR()-SATIR($U$12:$U$43)+1)));$U$8:$EM$8;0))
formüldeki $U$5:$EM$5 ve $U$8:$EM$8 hücre aralığını bir satır aşagıya geldiğinde $U$5 ve $U$8 hücre tanımını bir sonraki "0" dan büyük boş olmayan bir hücre tanımı ile değiştirbilirsek o zaman tarih aynı miktar olsa dahi hücre aralığı daraldığı için gerçek tarihini bulmaya yönelecek sanıyorum ama bunu nasıl yapabileceğimi blemiyorum. zira kaydır gibi formüller ile hücreyi kaydırarak diğer hücreye gidebiliyoruz ama "hücre aralığı olduğunda bunu yapmak ve formül içinde aralığın dinamik değişimini sağlamak nasıl olabilir beceremedim. ayrıca dolu hücre sayısı bittiğinde de yeni parça kodu için bir aşagıdaki satıra geçmesi ve aynı dimaik aralık uygulamasına devam etmesi gerekiyor . Sipariş rakamları eşit olduğundan büyük küçük gibi fonksiyonlarda pek kullanışlı olmadı.

son çare belki makro ile bu işleri yapmak ama makro bilgim olmadığı için bunu su anda yapılabilirliğinid ahi sorgulayamıyorum.

b,arz uzun oldu ancak çalışma yapacak arkadaslarımıza ve üstadlarımzıa biraz daha bilgi vermemin doğru olacağını düşündüm.

size tekrara tesekkür ediyor ve herkese iyi çaışmalar ve sağlık diliyorum.

 

[ugurgur01]

bazı imla ev yazım hatalarım olmuş herkesten özür diliyorum.

 

[ugurgur01]

Sn. Uzman arkadaşlar, Bu konu hakkında düşünce ve önerilerinizi dört gözle bekliyorum.

 

[ugurgur01]

bu konu da ban yardımcı olacak uzman arkadaşlara ihtiyacım var.

 

[ugurgur01]

286 kez bakılmış , ama sn. muygun hariç kimseden yanıt alamadim. Anlaşılmayan bir konu varsa lütfen sorun.

 

[Ömer]

Merhaba,

Tarih aralığını neye göre buldunuz tam olarak anlayamadım fakat aşağıdaki işlemleri sırası ile yaparsanız eklediğiniz dosyada yapmak istediğiniz olacaktır.

SİP15wk sayfasında EV6 hücresi,

=TOPLA(S7:S151)

SİP15wk sayfası EV7 hücresi,

=EĞERSAY(S7:S151;"<>0")

SİP15wk sayfası EV8 hücresine yazarak EV151 hücresine kadar kopyalayınız. ( dizi formülü )

=EĞER(SATIR(A1)>$EV$7;"";İNDİS($B$1:$B$151;KÜÇÜK(EĞER($S$7:$S$151<>0;SATIR($S$7:$S$151));SATIR(A1))))

.

x sayfası AS1 hücresi,

="AE"&KAÇINCI(D2;SİP15wk!$B$1:$B$151;0)

x sayfası AS2 hücresine yazarak 850. satıra kadar kopyalayınız. ( 850 satırı artırabilirsiniz.)

=EĞER(D2="";"";"SİP15wk!AE"&KAÇINCI(D2;SİP15wk!$B$1:$B$151;0)&":BG"&KAÇINCI(D2;SİP15wk!$B$1:$B$151;0))

x sayfası D2 hücresi,

=SİP15wk!EV8

x sayfası D3 hücresine yazarak 850. satıra kadar kopyalayınız,

=EĞER(SATIR(A2)>SİP15wk!$EV$6;"";EĞER(EĞERSAY($D$2:D2;D2)<DÜŞEYARA(D2;SİP15wk!$B$7:$S$151;18;0);DOLAYLI("SİP15wk!EV"&KAÇINCI(D2;SİP15wk!$EV$1:$EV$151;0));DOLAYLI("SİP15wk!EV"&KAÇINCI(D2;SİP15wk!$EV$1:$EV$151;0)+1)))

x sayfası H2 hücresine yazarak 850. satıra kadar kopyalayınız.( Dizi formülü)

=EĞER(D2="";"";İNDİS(DOLAYLI(AS2);KÜÇÜK(EĞER(DOLAYLI(AS2)<>0;EĞER(DOLAYLI(AS2)<>"";SÜTUN(DOLAYLI(AS2))-SÜTUN(DOLAYLI($AS$1))+1));EĞERSAY($D$2:D2;D2))))

x sayfası K2 hücresine yazarak 850. satıra kadar kopyalayınız.( Dizi formülü)

=EĞER(D2="";"";İNDİS(SİP!$AE$5:$BG$5;KÜÇÜK(EĞER(DOLAYLI(AS2)<>0;EĞER(DOLAYLI(AS2)<>"";SÜTUN(DOLAYLI(AS2))-SÜTUN(DOLAYLI($AS$1))+1));EĞERSAY($D$2:D2;D2))))

x sayfası X2 hücresine yazarak 850. satıra kadar kopyalayınız.( Dizi formülü)

=EĞER(D2="";"";İNDİS(SİP!$AE$6:$BG$6;KÜÇÜK(EĞER(DOLAYLI(AS2)<>0;EĞER(DOLAYLI(AS2)<>"";SÜTUN(DOLAYLI(AS2))-SÜTUN(DOLAYLI($AS$1))+1));EĞERSAY($D$2:D2;D2))))

.

 

[ugurgur01]

satırlardan sütunlara kriterlere göre veri aktarmada devrik dönüşüm yetersiz kaldı K

Sn. espiyonajl üstadım,

ilginize ve emeğinize çok teşekkür ederim. Çok zor olan bir işi mükemmel bir şekilde halletmişssiniz. Gönderdiğiniz formülleri uygulayarak, tüm satırları doldurdum ve sonuç olarak bir iki problem ile karşılaştım, SİP15wk dosyasında "1 K sipVar" yazılı sütunlardan kaynaklanıyordu. miktar (H2:H850) sütununda "1 K sipVar" çıkıyor ve karşısında "K" ve "X" sütunlarında tanımsız tarih çıkartıyordu. H2, K2 ve X2 deki formüllere ilave yaptım. formül su hale geldi: (dizi formülüne EĞER(DOLAYLI(AS2)<>"1 K sipVar";.. ilavesi)

=EĞER(D2="";"";İNDİS(DOLAYLI(AS2);KÜÇÜK(EĞER(DOLAYLI(AS2)<>0;EĞER(DOLAYLI(AS2)<>"";EĞER(DOLAYLI(AS2)<>"1 K sipVar";SÜTUN(DOLAYLI(AS2))-SÜTUN(DOLAYLI($AS$1))+1)));EĞERSAY($D$22;D2))))

sorunu çözdü. Ancak "2 K sipVar" veya "n K sipVar" bu sütunlarda çıkarsa yine problem yaratacak. bu konuda nasıl bir tanımlama yaparsak dizi içinde "n K sipVar" yazan hücreleri hücre aralığında yok saydırabiliriz.

iki tarih aralığında işlem yapabilmek ve dosyadaki SİP15wk sekmesinin SİP16wk, veya SİP17wk olacağını göz önüne alarak, geliştirme yaptım ve istediğim tarih ler arasında işlem yapmasını sağladım bunun için,
SİP15wk dosyasında
a2 hücresine
=PARÇAAL(HÜCRE("DosyaAdı";A1);BUL("]";HÜCRE("DosyaAdı";A1))+1;UZUNLUK(HÜCRE("DosyaAdı";A1))-BUL("]";HÜCRE("DosyaAdı";A1))) ile sekme adını buldurdum.

EV1 VE EV2 HÜCRELERİNE İKİ TARİH TANIMLADIM.

EY1 hücresine
=HÜCRE("address";İNDİS(SİP16wk!$A$1:$EQ$5;5;KAÇINCI($EV$1;SİP16wk!$A$5:$EQ$5;0)))
EY2 hücresine
0HÜCRE("address";İNDİS(SİP16wk!$A$1:$EQ$5;5;KAÇINCI($EV$2;SİP16wk!$A$5:$EQ$5;0)))
EZ1 de
=PARÇAAL(SAĞDAN($EY$1;4);1;2)
EZ2 de
=PARÇAAL(SAĞDAN($EY$2;4);1;2)
uygulayarak
iki tarihe ait sütun adlarını buldum.
daha sonra
EY7 ye
=(DOLAYLI("EZ1")&SATIR($A$5)+SATIRSAY($A$5:A5)+1)&":"&(DOLAYLI("EZ2")&SATIR($A$5)+SATIRSAY($A$5:A5)+1)
ve S7 ye
EĞERSAY(DOLAYLI(EY7);">0")
uygulayıp
iki tarih arasında boş olmayan hücre sayısını değişken bağlamında buldurdum.

x dosyasındada BA1 hücresine
=SİP16wk!A2

bc1 ve bc2 hücrelerine
SİP16wk!$EV1
SİP16wk!$EV2
tarihin bağlantısını yaptım.
daha sonra
BD2-BE2hücrelerine
HÜCRE("address";İNDİS(SİP16wk!$A$1:$EQ$5;5;KAÇINCI($BC$2;SİP16wk!$A$5:$EQ$5;0)))
HÜCRE("address";İNDİS(SİP16wk!$A$1:$EQ$5;5;KAÇINCI($BC$3;SİP16wk!$A$5:$EQ$5;0)))
yazarak
AS2
hücresindeki formülü
EĞER(D2="";"";DOLAYLI("BA1")&"!"&PARÇAAL(SAĞDAN($BD$2;4);1;2)&KAÇINCI(D2;SİP16wk!$B$1:$B$151;0)&":"&PARÇAAL(SAĞDAN($BE$2;4);1;2)&KAÇINCI(D2;SİP16wk!$B$1:$B$151;0))
olaak ;
D3deki formülü
EĞER(SATIR(A2)>SİP16wk!$EV$6;"";EĞER(EĞERSAY($D$22;D2)<DÜŞEYARA(D2;SİP16wk!$B$7:$S$151;18;0);DOLAYLI(DOLAYLI("BA1")&"!EV"&KAÇINCI(D2;SİP16wk!$EV$1:$EV$151;0));DOLAYLI(DOLAYLI("BA1")&"!EV"&KAÇINCI(D2;SİP16wk!$EV$1:$EV$151;0)+1)))
olarak değiştirdim
sonuçta istenilen tarih aralığında parça kodu sıralatmasını sağladım ve karşılığı olan mikra ve tarihler istenilen sekilde oluştu.

katkılarınız ve emeğiniz için tekrar çok teşekkür ediyorum. "n K sipVar" ile ilgili çözüm bulursanız sevinirim.

 

[Ömer]

Merhaba,

Bu şekilde tam olarak anlayamadım,

=EĞER(EHATALIYSA(MBUL("K sipVar";DOLAYLI(AS2)));formül;"")

Bu şekilde kullanabilirsiniz. Küçük bir örnek eklerseniz daha net cevap verebilirim..