Excel Forum

Excel Forum (http://www.excel.web.tr/index.php)
-   Fonksiyonlar (http://www.excel.web.tr/forumdisplay.php?f=47)
-   -   Topla.çarpım da koşula göre sayı dağıtma ve toplama (http://www.excel.web.tr/showthread.php?t=170277)

McDregin 02-02-2018 18:09

Topla.çarpım da koşula göre sayı dağıtma ve toplama
 
1 Eklenti(ler)
Moderatör Ve uzman arkadaşlar merhaba,
Ekte ki tablo da sarı ile yazdığım 2 sorum olacak.
Koşullu kritere göre bir hücredeki verileri dağıtmak.
Ay bazlı koşula göre Tarihler arasındaki bu değerleri ayrı toplamak
Desteğinizi beklerim.
Teşekkürler.

Ömer BARAN 02-02-2018 22:41

1 Eklenti(ler)
Merhaba.

Ekteki belgeyi inceleyiniz, filtereler uygulayarak, elle girilen verileri değiştirerek sonuçlarını kontrol edersiniz.
.

McDregin 03-02-2018 09:38

Ömer Hocam merhaba,
16-24 satırlarındaki hücreler AAA, BBB, Kategorisine göre aşağıdaki ilgili toplamlarını almakta. Burada ort. gibi bir rakam çıkmış.
Diğeri de Örneğin 01 Ocak 2018 Pazartesi AAA malında (b34) hücresindeki rakam normali 100 ve milli tatile denk geldiği için 50 olmalıydı.
B35 salı da 100 olmalıydı.
Bu şekilde bir bakabilirmisiniz.

Alttaki açılımlı tarihi ay yıl bazında nasıl toplanabileceğini formülden anladım.
Ben sadece ay yazıyordum.
Bu şekle getirmek aklıma gelmemişti.
Teşekkür ederim.

çıtır 03-02-2018 10:34

1 Eklenti(ler)
Bende bir dosya hazırlamıştım.Değişik.Bir bakınız.

Ömer BARAN 03-02-2018 14:40

Tekrar merhaba.

►Ben, DAĞITILMASI kelimesinden hareketle; B4:K10 aralığındaki sayıların HEDEF sayılar olduğunu ve
bu hedef sayıların 34 ve devamı satırlarda GÜN ADLARI ve H sütunundaki TÜRe göre,
DAĞITILMASInı, yani dağıtım sonucunda oluşan değerlerin tür ve gün adına göre TOPLAMlarının hedef sayılara EŞİT olmasını istediğinizi düşünmüştüm.
(bu nedenle kullanılan formülün son kısmında ..../TOPLA.ÇARPIM..... kısmı vardı)

►Demekki siz; 34 ve devamı satırlarda, GÜN ADI ve H sütunundaki TÜRe göre 4-10'uncu satırlardaki verilerin AYNEN yazılmasını istiyorsunuz.

►O halde; eklediğim belgede B34 hücresindeki formülü aşağıdakiyle değiştirerek sağa (F sütununa kadar) ve aşağı doğru (457'nci satıra kadar) kopyalayın.
.
Kod:

=KAYDIR($A$3;KAÇINCI(METNEÇEVİR($A34;"gggg");$A$4:$A$10;0);KAÇINCI($H34;$B$3:$O$3;0)+EĞER(KAÇINCI($H34;$B$3:$O$3;0)>10;0;KAÇINCI(B$33;$A$1:$K$1;0)-2))

McDregin 03-02-2018 15:27

Ömer hocam
Anadolu diliyle söyleyeyim, Çok makbule geçti.
CRM Aktif gün için boş hücre mantığı ile tatilleri çıkarıp hesaplama destek formülü de şık olmuş.
Hakkınızı helal edin.
Çok teşekkürler.

McDregin 03-02-2018 15:29

Sn. Çıtır merhaba,
Farklı bir detaylandırma ile gitmişsiniz.
Teşekkür ederim.

AAA Kategori ürün için B27 >> B57 Arası toplam manuel hesaplamamda 2.750 çıkıyor. Sizin formülde 1.923.495 adet topluyor.

Ömer BARAN 03-02-2018 15:38

Estağfurullah.

Son verdiğim formül ile; istenilen sonuç tam olarak elde edildi ve DAĞITIM ile ilgili sorun kalmadı değil mi?
.

McDregin 03-02-2018 16:16

Aynen hocam.
Hesaplamalar zinciri oluşturan MPR benzeri yapının ana veri sayfasıydı bu.
Bu formülüzasyon bakış açısına göre ben diğer sayfaları revize ediyorum.
Topla Çarpım da Yıl için ayrı Ay için ayrı kriter kontrolü ile listeden veri çekebilmek aklıma bile gelmemişti. 6 saat harcadım havlu atıp danışmıştım size. Bir türlü tıpa tıp ayni Ay ve ayni Yıl ismi arasında kriter yapıp toplamları alamıyordum. Örneğin ocak.2018 >> Ocak.2018 i görmüyordu.. Sayısal şekle çevirsem de görmüyordu... Hücre biçiminden diye yeniden bile yazmıştım sayfayı.
Tekrar teşekkürler.
Saygılar hocam.

Ömer BARAN 03-02-2018 16:24

.
Ancak, fark etmiş olmasılısınız. Belgede kullanılan formül yapısı ve formül yoğunluğu nedeniyle belgenizin çalışması bir miktar yavaşlayacaktır.
Aslında bu işlem için makro kullanarak çözüm aranmalıdır diye düşünüyorum.

Siz tablo yapınızı sabit hale getirin o zaman ona da bakarız.
Örneğin alt tablonun başlangıç satırındaki tarihini (benim gönderdiğim belgeye göre A16 hücresi),
açılır listelerden yapılacak YIL ve AY seçimine bağlamak gibi şeyler düşünmenizi öneririm.

Tablonuzun üzerinde tasarım ve işleyiş yapısı bakımından biraz çalışmanız, tüm seçenekleri düşünmeniz/tasarlamanız lazım.
.

McDregin 03-02-2018 17:43

Hocam ilk hali öğle idi hesaplama her veri değişiminde 10-12 sn. tutuyordu.
Sonraki formülünüz ile 0,5 sn. ye indi.

Ay seçimi için bir kaç varyasyon deneyeceğim.
Teşekkürler.

McDregin 05-02-2018 12:53

Ömer Hocam merhaba,
32 satırdan başlayan sayı dağılımlarında ;

BBB - CCC - DDD - EEE kategori isimlerine bir sayı yazmadığımda ( satış olmadığında ) da arefe ~ milli bayramlarda ~ dini bayramlarda Tatil sayı azaltımları sayılarını getiriyor. ( B - D - H - F - J 4-10 Arası hücrelerde ) (b3 ten o3 e kadar olan taramayı k3 e kadar kısattım bu sefer #yok uyarısı verdi. Olmayan ürün gurubuna satış sayı yazmaması için.

Birde BBB kategorisi ürünlerin D 4-10 Arasına yazılan verilerinin C16-C457 Dağılımını sadece kendi satış adetleri üzerinden dağılımı için ne yapmam gerekir ? ( Tatil % Azaltımından sayı almadan. )

Teşekkürler.

Ömer BARAN 05-02-2018 15:07

1 Eklenti(ler)
Merhaba.

Belki de ekteki gibi bir tablo yapısı düşünmelisiniz.
.

McDregin 05-02-2018 15:40

Ömer hocam,
B4>>B15 manuel mi tarih değiştiriyoruz ?
Formülasyon alıyor ama B2 yi mart a getirince B4-B15 arası değişmedi.

Ömer BARAN 05-02-2018 15:53

Amacınızı bilemediğim için öyle yapmıştım.

B4 hücresinde yer alan METNEÇEVİR işlevi parantezinin içindeki TARİH(YIL($B$2);SATIR(A1);1) kısmını
TARİH(YIL($B$2);AY($B$2)+SATIR(A1)-1;1) olarak değiştirip aşağı doğru kopyalayın.

McDregin 05-02-2018 16:59

Tatil günleri için ayrı bir tablo oluşturarak ı kolonuna bağlayacağım.
Böylece tarih değiştiğinde otomatik tatil günleri de değişecek formülü yazıyorum.
Bu şekilde yeni tablo yapısını sağlamlaştırırım diye düşünüyorum.

McDregin 05-02-2018 17:01

1 Eklenti(ler)
Hocam yan sayfada Yıllık tatilleri işledim.
I 19 Hücresi
Bağladım ama örneğin Yılbaşını Normal gördü.

Ömer BARAN 05-02-2018 17:47

Tekrar merhaba.

Önce bir hatırlatma; Ulusal Bayram sadece ☾✭ Cumhuriyet Bayramı ✭☽ olup 28 Ekim öğle'den itibaren 1,5 gündür.
Diğerlerinin durumu Cumhuriyet Bayramı'ndan farklıdır.
(ULUSAL BAYRAM VE GENEL TATİLLER HAKKINDA KANUN için tıklayın)

Tablonuzda bu tatil/arefe günleri için gerekli düzenlemeleri yaparsınız artık.

►H19 hücresine;
Kod:

=EĞER(EĞERSAY('YILLIK TATİLLER'!$A$1:$A$47;$B19)=0;"";EĞER(KAYDIR('YILLIK TATİLLER'!$B$1;KAÇINCI($B19;'YILLIK TATİLLER'!$A$1:$A$47;0)-1;0)=0;"";KAYDIR('YILLIK TATİLLER'!$B$1;KAÇINCI($B19;'YILLIK TATİLLER'!$A$1:$A$47;0)-1;0)))
► I19 hücresine;
Kod:

=EĞER(EĞERSAY('YILLIK TATİLLER'!$A$1:$A$47;$B19)=0;"NORMAL";KAYDIR('YILLIK TATİLLER'!$C$1;KAÇINCI($B19;'YILLIK TATİLLER'!$A$1:$A$47;0)-1;0))

McDregin 06-02-2018 09:50

Ömer hocam,
Çok teşekkür ederim.
Her satış gurubu için ayrı % kriter tablosu oluşturdum.
Tam oturdu.
Elinize sağlık.
Hakkınızı helal edin.
Saygılar.

McDregin 06-02-2018 16:15

Ömer hocam merhaba,
M19>>O19 arasındaki yüzde oranı kadar alıyorken (100 adet %80 ise 80 adet..)
P19 >> S19 arası yazılan % kadar bırakıyor. ( 100 adet %80 de 20 adet kalıyor.)
Düzeltmem için ne yapmam gerekir ?
Teşekkürler.

McDregin 06-02-2018 16:20

Birde Ramazan sayılarında Sadece Normal olarak baz alıyor.
C.tesi & Pazar düşen sayıya göre yapmıyor.
Örneğin Normal 100 // %80 = C.tesi 80 - %50 = Pazar 50 diyelim.
Ramazan da %80 aktif satış yüzdesi verdiğimde
Normal 80 - C.tesi 64 - Pazar 40 olması gerekir. ( Ayni orantıda C.tesi %80 - Pazar %50 üzerinden.)

McDregin 06-02-2018 16:30

1 Eklenti(ler)
Tabloyu da ekledim.

McDregin 06-02-2018 16:36

Burada şu mantık yazmışsınız anlıyorum.
Normal - C.tesi - Pazar Yüzde oran kadar adet alır.
Bu adetlerin Yandaki tatil oranları kadar daha düşürür.
Normalde 100 olan - ( C.tesi aktif %80 ile.. ) c.tesi 80 - ( Ramazan %80 aktif ile.. ) ramazan c.tesi de 64 gibi Tatil günlerinden hangisine denk geliyor ise bu oranlar kadar oranlarınca düşer veya oranlarınca yansır.
Her 2 şekilde de olur. Ya 100 den 20 düşmek için %80 de olur ters formül ile %20 ile de olur.

Ömer BARAN 06-02-2018 17:28

Bu şekilde sorarsanız cevaplayabilmem pek mümkün değil.
Hem şahsi işlerimle meşgulum hem de malum forumda birçok konuya cevap yazan biriyim.

Dolayısıyla sorunuzu, cumartesi/pazar/ramazan/tatil vs şeklinde yazmak yerine;
örneğin C140 hücresinde ramazan ile cumartesi kesişmiş, B46 hücresinde pazar ile milli tatil ve dini tatil kesişmiş.

Şu hücrede şu olması gerekirken şu olmuş şeklinde net soru sorun ve
olması lazım geleni nasıl hesapladığınızı net şekilde sorunsuz basit formül yazarak (şu*bu*şu hücredeki oran gibi) kendi hücresine veya
bir metin kutusunda net şekilde açıklayın.

Anladığım kadarıyla indirimler arası öncelik sırası ve bir'den fazla indirim kesiştiğinde,
bazı indirimler diğer indirimden kalan üzerinden tekrar indirim şeklinde bir şeylerden bahsediyorsunuz.
.

McDregin 06-02-2018 20:31

Haklısınız Hocam,
Bu tabloya emek verdiniz.
Şu noktaları revize etmek mümkün müdür..
1) Ramazan - Arefe - Dini Tatil - Milli Tatil yazılan % oranı kadar sayı çevirmeli [ p19:s31 arasındaki % yüzdeler ] * . Normal - C.tesi - pazar yapısındaki gibi. [ M19 : O31 Hücre hesaplaması idealdir. ] * 100 adet %75 de 75 adet vermesi gerekirken Şuan %75 yapınca 25 adet kalıyor. [ C19 : G442 ] arası tüm hücreler hesaplamasında..
2) Ramazan - Arefe - Dini Tatil - Milli Tatil günlerinde direkt kendi % oranından sayı alıyor. örneğin; 19 mayıs hem Ramazan hem Milli tatil olarak sayı düşmeli. 100 adet >> 56 olmalı. ( Ramazandan 75 e >> Milli tatilden 56 ya inmeli) 15 Temmuz hem pazar hem miili tatil.. [ C98 : E98 ] - [ C155 : G155 ]

Ömer BARAN 09-02-2018 00:09

1 Eklenti(ler)
.
Belge içerisindeki açıklama ve değişiklikleri inceleyip;
oranlarda/ana sayılarda/başlangıç tarihinde değişiklikler yaparak sonuçlardaki değişimleri kontrol ediniz.
.

McDregin 09-02-2018 18:34

Elinize sağlık Ömer hocam.
Teşekkürler.


Saat 00:59

Powered by vBulletin Version 3.7.2
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.