Çözüldü Excel Solver'de nasıl filtreleme eklerim?

[mavisiyah01]

Arkadaşlar merhaba. Excel’de Solver eklentisini kullanıyorum. Amacım belirlediğim bir sayıya kombinasyonla ulaşmak. Örneğin:

A1 Hücresi: 10

A2 Hücresi: 10

A3 Hücresi: 30

A4 Hücresi: 40



Sayılarından 20 sayısına ulaşmaya çalışıyorum. Solver’de bana A1 ve A2 Hücresini işaretliyor. Buraya kadar normal çalışıyor. Fakat amacım filtreleme yapmak. Örneğin:

A1 Hücresi: 10 B1 Hücresi: 01.01.2025

A2 Hücresi: 10 B2 Hücresi 01.01.2025

A3 Hücresi: 30 B3 Hücresi 02.01.2025

A4 Hücresi: 40 B4 Hücresi 02.01.2025

Diyelim. Ben Solver de çöz dediğimde Sadece 01.01.2025 dikkate almasını istiyorum. Bunu nasıl yapabilirim? Çünkü bazen 60'dan fazla değişken oluyor ve solverin çözmesi 1 saati geçebiliyor.

Solver kullanmayı bilmeyenler için aşağıdaki link, 1. Çözüm:

Ziyaretçiler için gizlenmiş link,görmek için Giriş yap veya üye ol.

 

[atoykan]

Solver'da filtre eklemek için yardımcı sütun kullanmanız gerekir. Örneğinizden yola çıkarak A'da veri setiniz B'de tarihleriniz var ise C yardımı sütununa

Değerli Misafirimiz İçeriği Görebilmek İçin Üyemiz İseniz Giriş Yap'ın Ya da Üye Ol'un.

formülünü girerek filtrenize uygun değerlerin C sütununda listelenmesini sağlayarak Solver için C sütunu değerlerini kullanın.

 

[mavisiyah01]

Solver'da filtre eklemek için yardımcı sütun kullanmanız gerekir. Örneğinizden yola çıkarak A'da veri setiniz B'de tarihleriniz var ise C yardımı sütununa

Değerli Misafirimiz İçeriği Görebilmek İçin Üyemiz İseniz Giriş Yap'ın Ya da Üye Ol'un.

formülünü girerek filtrenize uygun değerlerin C sütununda listelenmesini sağlayarak Solver için C sütunu değerlerini kullanın.

değerli hocam. size bir örnek dosya atıyorum, bu örnek dosya üzerinde sayfa2 üzerinde demek istediğinizi anlatabilir misiniz? Yani sizin çözüp atmanız benim için yeterli.

 

[mavisiyah01]

Solver'da filtre eklemek için yardımcı sütun kullanmanız gerekir. Örneğinizden yola çıkarak A'da veri setiniz B'de tarihleriniz var ise C yardımı sütununa

Değerli Misafirimiz İçeriği Görebilmek İçin Üyemiz İseniz Giriş Yap'ın Ya da Üye Ol'un.

formülünü girerek filtrenize uygun değerlerin C sütununda listelenmesini sağlayarak Solver için C sütunu değerlerini kullanın.

ek olarak bir sorum daha olacaktı. Sizce Yaptığım işlem için Solver kullanmak makro kullanmaktan daha mı mantıklı? Makrolarda şöyle bir sorun var. Diyelim ki 15’den fazla sayı var. Donuyor ve hata veriyor, ya da bulması çok uzun sürüyor. 2. Olarak sonucu göstermesi için bitmesini bekliyor. Diyelim ki 1. Dakikada sonucu buldu ama o 15 dakika bekliyor bitmesi ve göstermesi için.

 

[atoykan]

Solver zannettiğiniz gibi bir VBA kodlaması değildir C++ temellidir. Makrolarda kullanılamayan sistem kaynakları ve bellekleme yöntemlerini kullanır. Makro işleminin devam edip etmediğini ancak VBA editor'de başlık çubuğnda running yazıp yazmadığından takip edebilirsiniz aksi yönde bir geliştirme yapmadıysanız ve sizin donma kilitlenme dediğiniz süreçte Excel yanıt vermiyor olarak görünürken arka planda makro işlemeye devam ediyordur ve işlem yoğunluğuna, iterasyon sayısına vs. bağlı olarak bu bazen çok uzun zaman alabilir. Solver sayfa üzerinde işlem yaptığında süreci devam ediyor gibi izlerken örneğin makroda screenupdating, calculation, events vs. disable edilerek performans artışı hedeflendiğinde ekranda sadece bir kum saati veya excel yanıt vermiyor gördüğünüz için siz kilitlendi diye yoruma varıyorsunuz halbuki arka planda işlem devam ediyor ve bazen bu işlem saatlerde sürebilir.
Burada efektiflik konusunun göreceliği devreye girer. Örneğin makonun çalışmasının 2 saat sürmesi ve bu 2 saat boyunca excelin yanıt vermiyor ibaresi ile kilitlenmiş gibi görünmesi sizi manuel olarak yaptığınızda 1 hafta sürecek işten kurtarıyor ise bu süreyi göze alıp işlem yaparsınız, ancak manuel yaptığınızda 1 saatte bitecek işlem makro ile saatler sürüyor veya hemen hemen aynı sürede bitiyor ise bu kez uğraşmak isteyip istemediğinize göre karar verirsiniz, ama 5 dakikanızı alacak bir işlem için 1 saat dediğiniz zaman ne makrosu hallederim hemen deyip kullanmazsınız. "2. Olarak sonucu göstermesi için bitmesini bekliyor. Diyelim ki 1. Dakikada sonucu buldu ama o 15 dakika bekliyor bitmesi ve göstermesi için." kısmına gelince kod çalışmayı bitirmeden sonuç göstermez zira işlemler tamamlanana kadar kesin sonuç değildir. Ancak siz kodunuzda hedefe ulaşıldığı anda dur ve sonuçları listele kurgusu yaparsanız hedefe ulaşıldığı anda diğer iterasyonları bırakıp sonuçları listeler. Doğru bir yaklaşımı hayır değil. Neden örneğin 1,2,3.2,5,4 gibi bir veri setinde 4 toplamını verenler dediğinizde 2+2 = 4 sonucunu aldığınız anda işlemi sonlandırırsanız 3+1=4 sonucnu alamazsınız.

 

[Alicimri]

işlemi başlatmadan önce, geçici olarak verileri istenen tarihe göre filtreleyip, ,bunları başka bir yere kopyalayıp, böylece daha az veriyle işlemi yapmak çözüm olabilir.

 

[mavisiyah01]

Solver zannettiğiniz gibi bir VBA kodlaması değildir C++ temellidir. Makrolarda kullanılamayan sistem kaynakları ve bellekleme yöntemlerini kullanır. Makro işleminin devam edip etmediğini ancak VBA editor'de başlık çubuğnda running yazıp yazmadığından takip edebilirsiniz aksi yönde bir geliştirme yapmadıysanız ve sizin donma kilitlenme dediğiniz süreçte Excel yanıt vermiyor olarak görünürken arka planda makro işlemeye devam ediyordur ve işlem yoğunluğuna, iterasyon sayısına vs. bağlı olarak bu bazen çok uzun zaman alabilir. Solver sayfa üzerinde işlem yaptığında süreci devam ediyor gibi izlerken örneğin makroda screenupdating, calculation, events vs. disable edilerek performans artışı hedeflendiğinde ekranda sadece bir kum saati veya excel yanıt vermiyor gördüğünüz için siz kilitlendi diye yoruma varıyorsunuz halbuki arka planda işlem devam ediyor ve bazen bu işlem saatlerde sürebilir.
Burada efektiflik konusunun göreceliği devreye girer. Örneğin makonun çalışmasının 2 saat sürmesi ve bu 2 saat boyunca excelin yanıt vermiyor ibaresi ile kilitlenmiş gibi görünmesi sizi manuel olarak yaptığınızda 1 hafta sürecek işten kurtarıyor ise bu süreyi göze alıp işlem yaparsınız, ancak manuel yaptığınızda 1 saatte bitecek işlem makro ile saatler sürüyor veya hemen hemen aynı sürede bitiyor ise bu kez uğraşmak isteyip istemediğinize göre karar verirsiniz, ama 5 dakikanızı alacak bir işlem için 1 saat dediğiniz zaman ne makrosu hallederim hemen deyip kullanmazsınız. "2. Olarak sonucu göstermesi için bitmesini bekliyor. Diyelim ki 1. Dakikada sonucu buldu ama o 15 dakika bekliyor bitmesi ve göstermesi için." kısmına gelince kod çalışmayı bitirmeden sonuç göstermez zira işlemler tamamlanana kadar kesin sonuç değildir. Ancak siz kodunuzda hedefe ulaşıldığı anda dur ve sonuçları listele kurgusu yaparsanız hedefe ulaşıldığı anda diğer iterasyonları bırakıp sonuçları listeler. Doğru bir yaklaşımı hayır değil. Neden örneğin 1,2,3.2,5,4 gibi bir veri setinde 4 toplamını verenler dediğinizde 2+2 = 4 sonucunu aldığınız anda işlemi sonlandırırsanız 3+1=4 sonucnu alamazsınız.

peki bu işlem için sizin öneriniz nedir hocam?

Sayılar var ve bu sayılardan bir rakama ulaşmak istiyoruz.

Benim elimde bir excel vba dosyası var. Şifresi: 123456

Aslında yapmak istediğim işlemi yapıyor tam anlamıyla ama A sütununa girilen rakam arttıkça işlem süresi çok uzuyor. Aslında sizin dediğiniz gibi bu makro düzenlenebilir mi? Yani sonucu bulur bulmaz ekranda göstersin ve sonra devam etmek istiyorsam devam etsin.

Buyrun

 

[mavisiyah01]

peki bu işlem için sizin öneriniz nedir hocam?

Sayılar var ve bu sayılardan bir rakama ulaşmak istiyoruz.

Benim elimde bir excel vba dosyası var. Şifresi: 123456

Aslında yapmak istediğim işlemi yapıyor tam anlamıyla ama A sütununa girilen rakam arttıkça işlem süresi çok uzuyor. Aslında sizin dediğiniz gibi bu makro düzenlenebilir mi? Yani sonucu bulur bulmaz ekranda göstersin ve sonra devam etmek istiyorsam devam etsin.

Buyrun

atoykan hocam. Ek olarak bu attığım makro var olan tüm kombinasyonları ve hatta hedefe yakınlarda dahil olmak üzere gösteriyor. Sanırım ondan uzun sürüyor olabilir mi?

 

[atoykan]

Dosyanızı ve makronuzu ilk fırsatta incelemeye çalışacağım.

atoykan hocam. Ek olarak bu attığım makro var olan tüm kombinasyonları ve hatta hedefe yakınlarda dahil olmak üzere gösteriyor. Sanırım ondan uzun sürüyor olabilir mi?

Tabiki etkiler. Kullanılan sayı miktarı arttıkça işlem sayınız geometrik olarak büyür dolayısı ile her eklenen rakamda bu süreç bir o kadar daha uzar.

 

[atoykan]

Dosyanızı ve makronuzu ilk fırsatta incelemeye çalışacağım.

Bu makronun işlem yapmasının uzun sürmesinin başlıca sebebi programlama mantığı ile değil matematik sorusunu en uzun yoldan kağıt üzerinde çözme mantığı ile yazılmış olması. Dolayısı ile her iterasyon için gerekli kodlar tek tek ve defalarca tekrar edilerek yazılmış. Sağlıklı bir çalışma değil.
Ekli dosyayı inceleyin. Benzer işlem için benim yaptığım çalışmada işlem süresi paylaştığınız dosyadakine benzer 20 sayılık veri seti ile max 7 sn. Tam ve ondalık sayılarla işlem yapabilirsiniz. Açıklamalar dosya içinde mevcut. 20 sayıdan sonrasında VBA ve Excel'in bellek yönetimi sorun çıkartmaya başlar, sayfa, ad yönetimi, tablolama ve collectionları buna göre organize etmek yada sonuçları bir access tablosuna işleyip oradan kriterlere göre çekmek vs. gibi daha komplike bir çözüm geliştirmek gerekir ancak o kadar üzerinde çalışabileceğim zamanım yok ne yazıkki.

 

[mavisiyah01]

Bu makronun işlem yapmasının uzun sürmesinin başlıca sebebi programlama mantığı ile değil matematik sorusunu en uzun yoldan kağıt üzerinde çözme mantığı ile yazılmış olması. Dolayısı ile her iterasyon için gerekli kodlar tek tek ve defalarca tekrar edilerek yazılmış. Sağlıklı bir çalışma değil.
Ekli dosyayı inceleyin. Benzer işlem için benim yaptığım çalışmada işlem süresi paylaştığınız dosyadakine benzer 20 sayılık veri seti ile max 7 sn. Tam ve ondalık sayılarla işlem yapabilirsiniz. Açıklamalar dosya içinde mevcut. 20 sayıdan sonrasında VBA ve Excel'in bellek yönetimi sorun çıkartmaya başlar, sayfa, ad yönetimi, tablolama ve collectionları buna göre organize etmek yada sonuçları bir access tablosuna işleyip oradan kriterlere göre çekmek vs. gibi daha komplike bir çözüm geliştirmek gerekir ancak o kadar üzerinde çalışabileceğim zamanım yok ne yazıkki.

sayın hocam çok teşekkür ederim, işimi gördü. Teşekkür eder, iyi çalışmalar dilerim.

 

[mavisiyah01]

Bu makronun işlem yapmasının uzun sürmesinin başlıca sebebi programlama mantığı ile değil matematik sorusunu en uzun yoldan kağıt üzerinde çözme mantığı ile yazılmış olması. Dolayısı ile her iterasyon için gerekli kodlar tek tek ve defalarca tekrar edilerek yazılmış. Sağlıklı bir çalışma değil.
Ekli dosyayı inceleyin. Benzer işlem için benim yaptığım çalışmada işlem süresi paylaştığınız dosyadakine benzer 20 sayılık veri seti ile max 7 sn. Tam ve ondalık sayılarla işlem yapabilirsiniz. Açıklamalar dosya içinde mevcut. 20 sayıdan sonrasında VBA ve Excel'in bellek yönetimi sorun çıkartmaya başlar, sayfa, ad yönetimi, tablolama ve collectionları buna göre organize etmek yada sonuçları bir access tablosuna işleyip oradan kriterlere göre çekmek vs. gibi daha komplike bir çözüm geliştirmek gerekir ancak o kadar üzerinde çalışabileceğim zamanım yok ne yazıkki.

acaba tarih filtreleme de eklenebilir mi hocam son olarak? Yani diyelim ki C sütununa da tarih koysak ve 06.01.2025 tarihindekileri dikkate alsın.

 

[mavisiyah01]

Bu makronun işlem yapmasının uzun sürmesinin başlıca sebebi programlama mantığı ile değil matematik sorusunu en uzun yoldan kağıt üzerinde çözme mantığı ile yazılmış olması. Dolayısı ile her iterasyon için gerekli kodlar tek tek ve defalarca tekrar edilerek yazılmış. Sağlıklı bir çalışma değil.
Ekli dosyayı inceleyin. Benzer işlem için benim yaptığım çalışmada işlem süresi paylaştığınız dosyadakine benzer 20 sayılık veri seti ile max 7 sn. Tam ve ondalık sayılarla işlem yapabilirsiniz. Açıklamalar dosya içinde mevcut. 20 sayıdan sonrasında VBA ve Excel'in bellek yönetimi sorun çıkartmaya başlar, sayfa, ad yönetimi, tablolama ve collectionları buna göre organize etmek yada sonuçları bir access tablosuna işleyip oradan kriterlere göre çekmek vs. gibi daha komplike bir çözüm geliştirmek gerekir ancak o kadar üzerinde çalışabileceğim zamanım yok ne yazıkki.

Çünkü şöyle oluyor. Biz bunu genel olarak faturaların ödemeleri yapılmış mı diye kullanıyoruz.

A1 Hücresi: 10 B1 Hücresi: 01.01.2025

A2 Hücresi: 10 B2 Hücresi 01.01.2025

A3 Hücresi: 30 B3 Hücresi 02.01.2025

A4 Hücresi: 40 B4 Hücresi 02.01.2025

Tarih filtresini de ekleyebilirsem tüm işim çözülüyor. Örneğin burada ben 01.01.2025 02.01.2025 dediğimde sadece bu dört tanesini arayacak ve diğer 29 günü de değişkene ekleyip uzatmaktan kurtaracak.

 

[atoykan]

collectiona alınan sayılarda bir tarih kriteri eklenebilir, gece bakmaya çalışırım.

 

[mavisiyah01]

collectiona alınan sayılarda bir tarih kriteri eklenebilir, gece bakmaya çalışırım.

Örneğin P sütununu tarih kriteri dersiniz hocam.
P2 01.01.2024
p3.02.01.2024
p4:03.01.2024

yazarsam sadece yukarıdaki tarihleri colelctiona alır ve sallıyorum 150 tane veriyi tek tek hesaplamaktansa sadece bu söylediklerimi hesaplar.

bir de sizin şimdi gönderdiğinizde 20den fazla sayı ekleyince ve ondalıklı sayı yazınca overflow hatası aldı. dosyayı da ekliyorum. bu dosya üzerinde yaparsanız memnun olurum. İlginiz için çok teşekkür ederim hocam.

 

[mavisiyah01]

collectiona alınan sayılarda bir tarih kriteri eklenebilir, gece bakmaya çalışırım.

overflowun sebebi acaba belirlediğiniz posops değişkenini integer olarak belirttiğiniz için mi diye düşündüm, long olarak değiştirdim ama yine aynı hatayı aldım.

 

[mavisiyah01]

atoykan hocam. modülde poscell değişkeninin yanında i değişkenini integer tanımlamışsınız. onu long olarak düzelttim, poscellin kendisini de long olarak düzeltince sorun çözüldü fakat bu sefer de şöyle bir hata verdi. Cevabı buluyor, report kısmında AA AB kısmında gösteriyor ama nothing hatası veriyor, garip. Bu da dosya. Hedef sayıyı 1587 yani oradaki rakamların tamamı olarak belirledim.

 

[atoykan]

Sayın mavisiyah01 bugün toplantılardan kafamı kaldıramıyorum, ancak gece bakabilirim dosyanızı inceleme şansım yok şu anda.

 

[Alicimri]

Ekteki dosyadaki makro Sayfa2'deki verileri tarihe göre süzüp, süzülen verileri Sayfa1'e aktarıyor.
Siz Solver eklentisi veya makroyu bu Sayfa1 üzerinden çalıştırsanız olmuyor mu?

 

[atoykan]

1. 10. mesajda açıkça belirttim 20 sayıdan sonra sorun çıkartır diye örneğinizde 28 sayı var tabiki overflow hatası verecek sınırları aşıyor.
2. 20 sayı demek 2^20 kombinasyon demek boş kombinasyonu saymazsanız 1,048,575 tane işlem demektir = tam 1 excel sayfası kadar. Bunu 30 yaparsanız 1024 excel sayfasına çıkar, 100 yaparsanız 10^24'ten fazla excel sayfası yapar.
3. VBA bellek yönetimi yapamadığı için Excel motorunu kullanmak amacı ile veriler sayfaya yazdırılmak sureti ile boşaltılmakta. VErileri işlerken de tek bir sayfa üzerine işlemesine yönelik düzenledim yine aynı mesajda belirttiğim gibi sayfa, ad yönetimi, tablolama ve collectionları buna göre organize etmek yada sonuçları bir access tablosuna, text dosyasına vs. yazdırıp sort ederek geri yüklemek gibi daha komplike çalışma yapılabilir ancak buna çalışacak zamanım malesef yok, keza bence zerre faydası da olmayan bir uygulama olur.
4. i değerinin integer veya long tanımlanmasının sorununuz ile alakası yok. i bir döngü değişkeni ve +-32,764 değerlerini alabilir integer olarak, long olarak ise +-2,147,483,648 değerlerini alabilir DE böyle bir döngüyü değil excel bilgisayarınızın donanımı kaldırmaz. 1 döngü 1ms sürse 25 gün sadece döngünüz sürer!!!! o nedenle bellek yönetimi ve excel sınırlamalarına uygun olarak integer tanımlanmıştır.
5. 20 sayıya kadar ondalık sayı yazarsanız da kod sorunsuz çalışır.
6. Tarih kriterinin eklenmesi ile ilgili bakacağım ancak bugün ilgilenmem pek mümkün görünmüyor mevcut iş yüküm arasında.