Dağıtım Problemi

Ömer BARAN' Alıntı:

Merhaba Sayın @ozgur_ayaz .

Formülle çözüm arandığına göre (kırmızı sayının, mevcut listenin son satır numarası+1 olduğuna dikkat)

-- D1 hücresine birinci
-- E1 hücresine ikinci
-- C1 hücresine üçüncü
formülü uygulayıp liste boyunca kopyalayın.

VBA:

Görüntülemek için giriş yapmanız gerekmektedir.
(3 satır)

.

Genişletmek için tıklayın ...

52779' Alıntı:

Selamlar @ozgur_ayaz
Sorunuzu, Sayın @Ömer BARAN cevaplamış

Ben de Hazırlamışken göndereyim istedim.
Dosyanız ektedir...

Açıklarsak eğer,

1 - C kolonunda TAMSAYI olarak minimum koli adedi hesaplanır.
=TAMSAYI(B2*$K$1) ve K1 de de Oran hesaplanmaktadır. =I1/TOPLA(B2:B31)
2 - D kolonunda, min koli adedi ile, ondalıklı olarak Alması gereken Koli Adedi arasındaki FARK hesaplanır
=B2*$K$1-C2
3 - Farkların Büyükten Küçüğe sıralaması yapılır.
=RANK(D2;$D$2:$D$31)+EĞERSAY($D$2 : D2;D2)-1
4 - F kolonunda ise, Dağıtılan Koli adedi hesaplanır.
Burada, 500 - TOPLA(C2:C31) = 21 dir. Yani C kolonundaki miktarlardan sonra dağıtılacak daha 21 koli vardır.
Sıralamadaki ilk 21 kişiye, 1 er koli daha verilecektir.
Buradaki problem şudur.
Dağ. Kolisi alan son mağaza ile, Aynı FARKa sahip olup da Koli alamayan Mağaza vardır.
Çünkü, liste sıralamasında ilk sıralarda olanlar Avantajlıdır.
Örnekte, 22., 23., 24. ve 25. sıradaki Mağazalar, 21. sıradaki Mağaza ile AYNI FARK a sahip olmalarına rağmen koli alamamıştır.

Genişletmek için tıklayın ...

Merhaba
Yaptığım denemelerde kendi adıma şu tür bir sonuca vardım. .

İlk önce B sütununu A sütunu ile beraber küçükten büyüğe sıralarım.

Dolayısı ile “satış oranı” küçükten büyüğe sıralanmış olur.

F sütunundaki sayılar da küçükten büyüğe doğru sıralanmış olacaktır. Ve böylelikle koliyi eksik alan ”mağaza”, en alttaki satış oranı büyük mağaza olmuş olacaktır. Yani azdan az çokdan çok gitmiş gibi bir şey olacaktır. Zaten en alttaki mağazaya çok koli gideceğinden bence bu çok normaldir.

Şöyle düşünelim, elimizde 4 tane şeker var ve bu 4 şekeri tamsayı olarak 3 çocuğa paylaştırmamız gerekiyor. Çocukların sıralamasına göre mecburuz kalan bir şekeri çocuklardan birine vermeye. Ama bu çocuk, ya adı alfabede önce gelen (A sütunu) olacaktır, ya satış oranı en küçük olan (B sütunu) olacaktır. Yani bulduğunuz formüller çok iyi.

Yalnız bir şeyi denedim ama yapamadım. Bilmiyorum yapılabilir mi, tüm formüller birleştirilip tek formül oluşturulabilir mi?

Teşekkürler

Ömer BARAN' Alıntı:

Merhaba Sayın @ozgur_ayaz .

Formülle çözüm arandığına göre (kırmızı sayının, mevcut listenin son satır numarası+1 olduğuna dikkat)

-- D1 hücresine birinci
-- E1 hücresine ikinci
-- C1 hücresine üçüncü
formülü uygulayıp liste boyunca kopyalayın.

VBA:

Görüntülemek için giriş yapmanız gerekmektedir.
(3 satır)

.

Genişletmek için tıklayın ...

Sayın Ömer Baran merhaba
İkinci sıradaki formülde bir hata olabilir mi?
Bir kaç kez denedim ama olmadı

hakki83' Alıntı:

Merhaba
...............

Yalnız bir şeyi denedim ama yapamadım. Bilmiyorum yapılabilir mi, tüm formüller birleştirilip tek formül oluşturulabilir mi?

Teşekkürler

Genişletmek için tıklayın ...

Selamlar,
Eklediğim dokumandaki mantık çerçevesinde olmak üzere, yani, tamsayılardan sonraki ondalık kısmın büyüklük durumuna ve eşitlik halinde de liste sıralamasına göre dağıtım planlandığında,

Tek formül olarak, G2 hücresine yazılıp aşağıya doğru kopyalanır... (DİZİ Formüldür... CTRL+SHIFT+ENTER ile giriniz)

=TAMSAYI($B2*$I$1/TOPLA($B$2:$B$31))+EĞER(MOD($B2*$I$1/TOPLA($B$2:$B$31);1)-SATIR()/10^9<BÜYÜK(DÇARP(--MOD(($B$2:$B$31)*$I$1/TOPLA($B$2:$B$31);1)-SATIR($B$2:$B$31)/10^9;{1});$I$1-TOPLA(TAMSAYI(($B$2:$B$31)*$I$1/TOPLA($B$2:$B$31))));0;1)

Formülü kısaltmak ve DİZİ formülden kurtulmak amacıyla, ad tanımlamaları yaparsak
Satış =Sayfa1!$B$2:$B$31
Miktar = Sayfa1!$K$1
oran =Miktar/TOPLA(Satış)
d =BÜYÜK(DÇARP(--MOD((Satış)*Miktar/TOPLA(Satış);1)-SATIR(Satış)/10^9;{1});Miktar-TOPLA(TAMSAYI((Satış)*Miktar/TOPLA(Satış))))

Tek formül olarak, G2 hücresine yazılıp aşağıya doğru kopyalanır...

=TAMSAYI($B2*oran)+EĞER(MOD($B2*oran;1)-SATIR()/10^9<d;0;1)

Satış ad tanımlamasını da Dinamik olarak yapmak mümkün. Fakat burada yapılmadı...

Yeni dokuman ektedir. İncelersiniz...

Konu çok ilgimi çektiği için birkaç gündür denemeler yapıyorum.

Sayın Ömer Baran’ın vermiş olduğu dizi formülü de çok iyi. Sayın 52779’un vermiş olduğı ad tanımlama formülü de çok iyi.

Fakat enteresan birşey dikkatimi çekti. Bu iki formülde eğer;
“dağıtılacak miktar” , B sütunundaki “satış hızı oranlarının” toplamı veya katları olursa, bu iki formül de hata veriyor. Diğer sayılarda hiç sorun yok.

Örnek verecek olursam şöyle ki; elimdeki örnekte B sütununun toplamı 127 görünüyor.
Dağıtılacak miktarı 127, 254, 381, 508 gibi sayıları girersek, bu iki formül çalışmıyor.

hakki83' Alıntı:

Konu çok ilgimi çektiği için birkaç gündür denemeler yapıyorum.

Sayın Ömer Baran’ın vermiş olduğu dizi formülü de çok iyi. Sayın 52779’un vermiş olduğı ad tanımlama formülü de çok iyi.

Fakat enteresan birşey dikkatimi çekti. Bu iki formülde eğer;
“dağıtılacak miktar” , B sütunundaki “satış hızı oranlarının” toplamı veya katları olursa, bu iki formül de hata veriyor. Diğer sayılarda hiç sorun yok.

Örnek verecek olursam şöyle ki; elimdeki örnekte B sütununun toplamı 127 görünüyor.
Dağıtılacak miktarı 127, 254, 381, 508 gibi sayıları girersek, bu iki formül çalışmıyor.

Genişletmek için tıklayın ...

Selamlar,
Söylediğiniz doğru....Bu durumlar, Dizi formül olarak verdiğim formülde dikkate alınmamış...
Dağıtım miktarı, satış miktarlarının katları olması durumunda HATA verecektir. Çünkü, Oran = Miktar/Toplam(Satış) tamsayı olacaktır. Ondalık kısımlar ise daima 0 olacaktır. Yani MOD(B2:B31;1)=0 dır. Büyüklük sıralaması diye bir şey söz konusu olmayacaktır.....

Bu durumu da dikkate alırsak;

Not : Şimdi farkettim ki, formülde DÇARP kısmına gerek yok.. Çünkü zaten 30 satır x 1 sütun şeklinde dizi elde ediliyor ve {1} şeklinde de 1 satır x 1 sütun matrisle çarpılıyor. Komple revize edilerek formülü yeniden vereceğim....

DİZİ Formül :

=TAMSAYI($B2*$K$1/TOPLA($B$2:$B$31))+EĞER(MOD($B2*$K$1/TOPLA($B$2:$B$31);1)=0;0;EĞER(MOD($B2*$K$1/TOPLA($B$2:$B$31);1)-SATIR()/10^9<BÜYÜK(MOD(($B$2:$B$31)*$K$1/TOPLA($B$2:$B$31);1)-SATIR($B$2:$B$31)/10^9;$K$1-TOPLA(TAMSAYI(($B$2:$B$31)*$K$1/TOPLA($B$2:$B$31))));0;1))

Ad Tanımlamaları
Satış =Sayfa1!$B$2:$B$31
Miktar = Sayfa1!$K$1
oran =Miktar/TOPLA(Satış)
d =BÜYÜK(MOD((Satış)*oran;1)-SATIR(Satış)/10^9;Miktar-TOPLA(TAMSAYI((Satış)*oran)))

ve Ad Tanımlamaları ile yapılmış formül :

=TAMSAYI($B2*oran)+EĞER(MOD(oran;1)=0;0;EĞER(MOD($B2*oran;1)-SATIR()/10^9<d;0;1))

Dokumanda gerekli değişiklikleri ( formüller ve Ad Tanımlamalarında d ad tanımlaması) yaparsanız, bu durum da dikkate alınmış olacaktır.

hakki83' Alıntı:

.................Fakat enteresan birşey dikkatimi çekti. Bu iki formülde eğer;
“dağıtılacak miktar” , B sütunundaki “satış hızı oranlarının” toplamı veya katları olursa, bu iki formül de hata veriyor. Diğer sayılarda hiç sorun yok.

Örnek verecek olursam şöyle ki; elimdeki örnekte B sütununun toplamı 127 görünüyor.
Dağıtılacak miktarı 127, 254, 381, 508 gibi sayıları girersek, bu iki formül çalışmıyor.

Genişletmek için tıklayın ...

Sayın @hakki83 .
Hangi örnek belgeden, hangi formüllerden söz ediyorsunuz anlaşılmıyor doğrusu.
Deneme yapmadan cevap yazdığım pek vaki değildir.

Konu sayfasında 14 ve 17 numaralı cevaplara eklediğim belgelerden mi söz ediyorsunuz?
Bu belgeler üzerinde denemeler yapar mısınız.

Hatalı haliyle bir örnek belge ekleyin ki sorun neymiş görüp çözümüne bakılsın.

.

Ömer BARAN' Alıntı:

Sayın @hakki83 .
Hangi örnek belgeden, hangi formüllerden söz ediyorsunuz anlaşılmıyor doğrusu.
Deneme yapmadan cevap yazdığım pek vaki değildir.

Konu sayfasında 14 ve 17 numaralı cevaplara eklediğim belgelerden mi söz ediyorsunuz?
Bu belgeler üzerinde denemeler yapar mısınız.

Hatalı haliyle bir örnek belge ekleyin ki sorun neymiş görüp çözümüne bakılsın.

.

Genişletmek için tıklayın ...

Sayın Ömer Baran merhaba, haklısınız
2 nolu mesajınızdaki en üstteki formülü uyguladığımda hata vermişti. İlk fırsatta dosya ekleyeceğim.
Teşekkürler

hakki83' Alıntı:

Sayın Ömer Baran merhaba, haklısınız
2 nolu mesajınızdaki en üstteki formülü uyguladığımda hata vermişti. İlk fırsatta dosya ekleyeceğim.
Teşekkürler

Genişletmek için tıklayın ...

Sayın Ömer Baran, örnek dosya ektedir

Fakat aklımda yanlış kalmış, 2 nolu mesajınızdaki en üstteki formül değil, en alttaki formülde (C1 hücresine uygulanması gereken) bir hata vermişti.

Ömer BARAN' Alıntı:

Sayın AYAZ, isteğinizi, 17 numaralı cevaba eklediğim belge üzerinden açıklayıp örneklendirir misiniz?
Örneklendirmenin, hücreye elle veri yazıp, elle veri yazdığınız hücreye farklı bir arkaplan rengi uygulayarak işaretlerseniz iyi olur.
Ayrıca veri satır sayısı sabit midir? Mağazaların sabit bir listesi var mıdır?
Eski dağıtım nedir? Hangi alandır?
Yeni dağıtım nedir? Hangi alandır?

Genişletmek için tıklayın ...

Ömer Hocam biraz geç oldu kusura bakma, verileri kaldırıp aynı şekilde uyarlamak istedim. Ben dosyanın son halini ekledim. Dosyanın kapasitesini aşağıdaki gibi yaptım.

• 250 mağazalık
• 30 ürünlük

Dosyada dağıtım yapılan 2 adet sayfa var. Bunlar Tekli ve Çoklu Dağıtım sayfaları. Tekli Dağıtımda farklı bir formül işliyor. Çoklu dağıtımda da sizin yazdığınız formül. Günlere göre dağımı olan mağazalar ekrana geliyor. Burda yapmak istediğimiz olay: Mağazalara her iki sayfada da dağıtım yaptıktan sonra onu kayıt altına almak ve bir sonraki dağıtımda o mağazaya 0 miktar gitmiş ise tekrar 0 göndermemek. O mağazayı dikkate aldı, 1-2 koli gönderdi diyelim bir sonrakinde onu da dikkate alıp tekrar göndermemeli.

Buradaki amaç kısıtlı dağıtım ile (Her gün ürün gelebilir veya gelemez). Bir haftalık dağıtımda mağazalara 0 koli gitmeyecek ve satışlara göre dağılım yapacak şekilde bir sistem kurmak.

hakki83' Alıntı:

Sayın Ömer Baran, örnek dosya ektedir

Fakat aklımda yanlış kalmış, 2 nolu mesajınızdaki en üstteki formül değil, en alttaki formülde (C1 hücresine uygulanması gereken) bir hata vermişti.

Genişletmek için tıklayın ...

Sayın Ömer Baran merhaba
Ekteki dosyada bahsi geçen formül hata veriyordu. Acaba kopyalama hatası mı yaptım.

Sayın @hakki83 .

Formüllerde sorun yok, sadece cevap metnindeki hücre adrelerinde satır numaralarını yanlış yazmışımb
Formüllerin C2, D2, E2 hücrelerine uygulanacağını yazmak yerine hataen C1, D1, E1 hücrelerine uygulanması gerektiğini yazmışım.

Sorunuzun cevabı, verdiğim ilgili formüllerin belgeye uygulanmış hali, 7 numaralı cevap ekindeki belgenin
H, I, J sütunlarında uygulanmış olarak mevcut.
Bahsettiğim 7 numaralı cevap ekindeki belgeyi indirerek inceleyebilirsiniz.

.

ozgur_ayaz' Alıntı:

......Dosyanın kapasitesini aşağıdaki gibi yaptım.
............
Burda yapmak istediğimiz olay: Mağazalara her iki sayfada da dağıtım yaptıktan sonra onu kayıt altına almak ve bir sonraki dağıtımda o mağazaya 0 miktar gitmiş ise tekrar 0 göndermemek. O mağazayı dikkate aldı, 1-2 koli gönderdi diyelim bir sonrakinde onu da dikkate alıp tekrar göndermemeli.

Genişletmek için tıklayın ...

Bu tür sorularda çözüme ulaşmayı kolaylaştırmak bakımından;
-- satır ve sütun sayısını sınırlı tutmak (gerçek belgedeki sayfa/satır/sütun yapısına uygun olacak şekilde, 5-10 satır ve 3-5 sütunluk veri),
-- bu başlangıç verilerine göre olması gereken sonuçları, olması gereken konumlarına elle yazarak örneklendirmek.
-- çözüm için herbir hücreye yazılan olması gereken sonucun nasıl bulunduğuyla ilgili kısa net açıklamalar eklemek
en iyisi olur.

Küçük çaplı bu örnek üzerinden konu netleşip çözüm üretildikten sonra da, çözümün asıl belgeye uyarlama konusuna geçmek doğru olur gibi görünüyor.

.

Ömer BARAN' Alıntı:

Bu tür sorularda çözüme ulaşmayı kolaylaştırmak bakımından;
-- satır ve sütun sayısını sınırlı tutmak (gerçek belgedeki sayfa/satır/sütun yapısına uygun olacak şekilde, 5-10 satır ve 3-5 sütunluk veri),
-- bu başlangıç verilerine göre olması gereken sonuçları, olması gereken konumlarına elle yazarak örneklendirmek.
-- çözüm için herbir hücreye yazılan olması gereken sonucun nasıl bulunduğuyla ilgili kısa net açıklamalar eklemek
en iyisi olur.

Küçük çaplı bu örnek üzerinden konu netleşip çözüm üretildikten sonra da, çözümün asıl belgeye uyarlama konusuna geçmek doğru olur gibi görünüyor.

.

Genişletmek için tıklayın ...

@Ömer BARAN

Dosya içeriğine açıklamalar ekledim ve sütun-satır sayısını azalttım. Yorumlarınız için teşekkürler.

DOSYANIN ÇALIŞMA MANTIĞI

*Gün kısmında seçilen güne göre sevkiyatı olan mağaza listelenir (Bilgiler sayfasından alıyor, her mağazanın aynı değil)

*Kırmızı alandaki dağıtılacak koli miktarları kısmına girilen koli miktarlarını satışlara göre (sağ yeşil alan)dağıtır

İHTİYAÇ OLANLAR

Bu dosya normalde düzgün bir şekilde çalışmaktadır fakat bazı mağazalara 0 ürün dağıtabiliyor, bunu haftalık dağıtım bazında engellemek ve mağazalara minimum 1 koli gitmesini sağlamamız gerekiyor. Burda bakiye tarzı bir sistem düşündüm. Dağıtılan miktarları dağılım sayfasına atan ve bakiyelere ekleyen DAĞITIM YAP BUTONU, hafta başına dönünce bakiyeleri sıfırlayan SIFIRLA BUTONU, 0 olanları 1 yapıparak dağılım sayfasına atan ve bakiyelere ekleyen 1 YAPARAK DAĞIT BUTONU. Bir de dosya listede olan ve 0 ürün dağıtılan mağazaları yukarıda göstermeli veya doğrudan 0 olan hücreyi kırmızya boyamalı

ozgur_ayaz' Alıntı:

@Ömer BARAN.............
İHTİYAÇ OLANLAR

Bu dosya normalde düzgün bir şekilde çalışmaktadır fakat bazı mağazalara 0 ürün dağıtabiliyor, bunu haftalık dağıtım bazında engellemek ve mağazalara minimum 1 koli gitmesini sağlamamız gerekiyor. Burda bakiye tarzı bir sistem düşündüm. Dağıtılan miktarları dağılım sayfasına atan ve bakiyelere ekleyen DAĞITIM YAP BUTONU, hafta başına dönünce bakiyeleri sıfırlayan SIFIRLA BUTONU, 0 olanları 1 yapıparak dağılım sayfasına atan ve bakiyelere ekleyen 1 YAPARAK DAĞIT BUTONU. Bir de dosya listede olan ve 0 ürün dağıtılan mağazaları yukarıda göstermeli veya doğrudan 0 olan hücreyi kırmızya boyamalı

Genişletmek için tıklayın ...

Merhaba Sayın AYAZ.

İsteğinizi TAM OLARAK anlamamış olabilirim.
-- SIFIR sonucunun alınmasını önleyen bir çözüm önerisi hazırladım.
SIFIR sonucunun alınıp alınmayacağını hücreden yapılacak seçimle belirleyebilirsiniz.

-- Belgede, DİNAMİK AD TANIMLAMALARI oluşturulup, bu AD TANIMLAMALARI formüllerde kullanılıyor.
Böylece veri miktarı değiştiğinde de formülleri değiştirme/güncelleme ihtiyacı olmayacaktır.

Ekteki belgede oluşturduğum senaryoyu inceleyeniz, denemeler yapınız.

.

Ömer BARAN' Alıntı:

Merhaba Sayın AYAZ.

İsteğinizi TAM OLARAK anlamamış olabilirim.
-- SIFIR sonucunun alınmasını önleyen bir çözüm önerisi hazırladım.
SIFIR sonucunun alınıp alınmayacağını hücreden yapılacak seçimle belirleyebilirsiniz.

-- Belgede, DİNAMİK AD TANIMLAMALARI oluşturulup, bu AD TANIMLAMALARI formüllerde kullanılıyor.
Böylece veri miktarı değiştiğinde de formülleri değiştirme/güncelleme ihtiyacı olmayacaktır.

Ekteki belgede oluşturduğum senaryoyu inceleyeniz, denemeler yapınız.

.

Genişletmek için tıklayın ...

@Ömer BARAN elinize sağlık fakat bu şekilde çalıştığımız zaman haftalık 0 gönderip-göndermediğimizi elle takip etmemiz gerekiyor. Ben haftalık bakiye kısmını o yüzden eklemiştim. Bir mağazaya bir hafta boyunca dağıtım yaparken kaç defa 0 gittiğini bilemeyiz, onu bakiye usulü ile hafızada tutup hatırlatmasını düşünüyordum. Mevcut formülleri değiştirmemesi, aynı zamanda da dağıtım yapılacak miktarları kopyalamaya izin vermesi için ayrı bir dağıtım sayfası koymuştum. Bir de ben mağaza listesini daha önceki çalıştığımız dosyadaki gibi 250 mağaza yapmıştım yeni mağaza eklerseler diye. O yüzden formül eksik mi diye hesaplamaya gerek kalmıyordu.

Dağıtım Yap butonu 0 lara izin vererek, 1 Yaparak Dağıt butonu ise 0 ları 1 yaparak atması içindi ama onun kurgusunu kafamda yapamadım bir türlü.

Amaç: Haftanın günlerinde, değişken mağazalara kasa dağıtımı yapmak, hafta içerisinde ürünlerden 0 koli giden mağazaları tespit etmek ve kullanıcının müdahale etmesini sağlamak.

Alternatif düşüncem: Her gün için farklı sayfa açmak, dağıtım sütunlarının yanına kullanıcıların koli ekleyebilecekleri ek sütun açmak ve Dağıtım Yap butonu ile dağıtım miktarı+elle girilen miktarı Bakiye Sayfası'ndaki hücrelerle toplamak. Dağıtım yaparken de mağazanın ilgili ürünün haftalık bakiyesi sıfır ise o hücreyi kırmızıya boyamak.

Makroları yeni öğrenmeye başladığım için halledemedim bir türlü. Yardımlarınız için çok sağolun.