Çözüldü Alttoplam ve Toplaçarpım formülünü vba koduna çevirme

[mert25]

Merhabalar,

Bir konuda daha ustaların yardımını isteyeceğim. Basketbol adlı sayfada K2-L2-M2-N2-O2-P2-Q2-R2-AE2-AF2-AG2-AH2-AK2-AL2-AM2-AN2 hücrelerindeki formüller nedeni ile maçları Ana Sayfaya attığımda işlem uzun sürüyor. Formülleri silince sorun kalmıyor. Bu formüllerin vba kodu lazım, o zaman işlemin uzun sürmesi sorununun ortadan kalkacağını zannediyorum.

Bir başka sorun da şu; Örneğin oynanmış bir maçı (26. satırdaki Nagoya takımını) filtrelediğimde o takımın son oynadığı maçın formüle (istatistiğe) dahil edilmemesini istiyorum.

Aşağıdaki formülde AJ5:AJ5393 arasının filtrelenmesini istiyorum ama Nagoya 26. satırda olduğu için formülün 27. satırdan itibaren (AJ27:AJ5393) hesap yapması lazım. Excelde bunun mümkün olması gerek.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Ustalar bu benim için çok önemli, inşallah bir yardım eden çıkar.

 

[KarıncaZ]

Merhaba.

Formüllerin VBA karşılıklarını en kısa yoldan şöyle elde edebilirsiniz.

Makro Kaydet düğmesine basın, bir kısayol belirleyerek makro kaydetmeye başlayın.
Formülün bulunduğu hücreyi seçin ve F2 fonksiyon düğmesine basın. Ardından hiçbir değişiklik yapmadan Enter düğmesine basın.
Makro Kaydetmeyi durdurun.
VBA ekranına geçince Makro1 olarak göreceğiniz kodlar Formülünüzün VBA karşılığıdır.

İyi çalışmalar.

 

[mert25]

Cevap için teşekkürler, fakat benim isteğimin o yöntemle olması pek mümkün değil. Çünkü kodların bir butona bağlı olarak değil, otomatik çalışması gerek.

 

[arzuhalci]

Nagoya 26. satırda ve filtre uygulandığında ortalama hesabının 27. satırdan itibaren yapılmasını istiyorsun.

Filtrenin üstünde kalan satırları doğrudan hariç tut:
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

6. satır (Nagoya) hiç hesaba girmez.Filtrelenmiş satırlar doğru şekilde dikkate alınır.Ortalama (109 / 102) doğru çalışır

eğer “Nagoya”nın satırı sabit olmayabilir diyorsan:
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

A sütununda “Nagoya”yı bulur Bir alt satırdan itibaren hesap yapar Filtre uyumlu çalışır

 

[mert25]

Cevap için teşekkürler, fakat ikinci formülünüzde şöyle bir sorun var; Nagoya yerine başka bir takım seçildiğinde formül işlevsiz kalır. Herhangi bir takım seçtiğimde o takımın sıra numarasının bir altından başlamasını istiyorum. Yani seçtiğim takımın olduğu satır istatistiğe dahil olmasın. Aradığım bu fakat galiba çözümü yok.

 

[arzuhalci]

E2 hücresinde bir takım seçiyorsun (ör. Nagoya)
Aynı takım üstte bir blokta ve altta istatistik bloğunda var

İstatistik:
Seçilen takımın bulunduğu satır HESABA GİRMEYECEK
O satırın bir altından itibaren
Filtreye duyarlı (ALTTOPLAM)
Ortalama = 109 / 102

Aşağıdaki formül hangi takımı seçersen seç,
o takımın istatistik tablosundaki satırını bulur ve bir alt satırdan başlatır:

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Sonuç

Nagoya seç → Nagoya satırı hariç
Kawasaki seç → Kawasaki satırı hariç
Filtre uygula → sadece görünenler
Takım değiştir → formül bozulmaz

 

[mert25]

Üstat, formülü eklemeyi yaptım ama bir sonuç alamadım, size zahmet ilk mesajımdaki dosyaya ekleyebilir misiniz?

 

[mert25]

Soruma cevap gelmediği için konuyu kapatmak istiyorum. Galiba excelde bu sorumun cevabı yokmuş.

 

[arzuhalci]

bu formüller özellikle KAYDIR (OFFSET) + TOPLA.ÇARPIM + tüm sütun aralığı (5:5393) + filtre görünürlük kontrolü (ALTTOPLAM(103;KAYDIR(...))) yüzünden çok maliyetli olduğu için (özellikle OFFSET/KAYDIR) sayfada her hareket/kopyalama/hesaplamada yeniden çalışıp “Ana Sayfa’ya maç atma” işlemini ciddi yavaşlatıyor.

vba kilitli olduğu için bunu bir deneyiniz ben denemek için K:R arasındaki formüllere göre hareket ettim AE2–AN2 katmadım
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

VBA’ya eklendiğinde
Basketbol_Ozetleri_VBA makrosunu maçları Ana Sayfa’ya atmadan önce veya sonra bir kez çalıştır.
K2:R2 hücreleri değer olur.

 

[arzuhalci]

Aşağıdaki formülde AJ5:AJ5393 arasının filtrelenmesini istiyorum ama Nagoya 26. satırda olduğu için formülün 27. satırdan itibaren (AJ27:AJ5393) hesap yapması lazım. Excelde bunun mümkün olması gerek diyorsun burada istediğin şey: filtreleme AJ5’ten başlasın ama hesaplama AJ27’den itibaren yapılsın (26. satır “Nagoya” olduğu için).Bunu tek formülle, ek sütun kullanmadan yapabilirsin.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Filtre AJ5’ten başlar, ama ALTTOPLAM sadece verdiğin aralığı dikkate alır.
Filtre üst satırlarda olsa bile, Excel görünürlük bilgisini tüm sütun için tutar.

Eğer 26. satır sabit değilse (Nagoya her zaman 26 olmayabilir)
O zaman “Nagoya”yı otomatik bulup altından hesaplayan dinamik çözüm gerekir:

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Bu formül:
“Nagoya”yı AJ sütununda bulur
Bir alt satırdan itibaren hesaplar
Filtreyi bozmadan çalışır

 

[mert25]

Şifreyi kaldırmayı unutmuşum.

vba şifresi: 123456

Formülleri henüz deneyemedim, deneyince dönüş yaparım.

 

[mert25]

Aşağıdaki formülde AJ5:AJ5393 arasının filtrelenmesini istiyorum ama Nagoya 26. satırda olduğu için formülün 27. satırdan itibaren (AJ27:AJ5393) hesap yapması lazım. Excelde bunun mümkün olması gerek diyorsun burada istediğin şey: filtreleme AJ5’ten başlasın ama hesaplama AJ27’den itibaren yapılsın (26. satır “Nagoya” olduğu için).Bunu tek formülle, ek sütun kullanmadan yapabilirsin.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Filtre AJ5’ten başlar, ama ALTTOPLAM sadece verdiğin aralığı dikkate alır.
Filtre üst satırlarda olsa bile, Excel görünürlük bilgisini tüm sütun için tutar.

Eğer 26. satır sabit değilse (Nagoya her zaman 26 olmayabilir)
O zaman “Nagoya”yı otomatik bulup altından hesaplayan dinamik çözüm gerekir:

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Bu formül:
“Nagoya”yı AJ sütununda bulur
Bir alt satırdan itibaren hesaplar
Filtreyi bozmadan çalışır

Bu formülde takım ismi yazıyor, başka bir takım seçildiğinde formül nasıl çalışacak ki? Ben mi yanılıyorum?
Takım ismi yerine satır numarasına göre işlem yapcak bir formül olsa o zaman hata olmaz değil mi?

 

[arzuhalci]

En pratik düzeltme (takım adı bir hücreden seçilsin) Diyelim takım seçimi B1 hücresinde.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

“Satır numarası hücreden gelsin” versiyonu
Eğer başlangıç satırını direkt bir hücreye yazacaksan (ör. C1 = kaç satır aşağı kayılacak):

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Bu tamamen satır numarasına bağlı olur — takım metni hiç geçmez.

 

[mert25]

Üstat emekleriniz için teşekkür ederim fakat formülü dosyaya uyguladığımda sonuç alamıyorum. Nerede yanlış yaptığımı bulamadım. Eğer zamanınız olursa formülü ilk mesajdaki örnek dosyaya uygulayabilirseniz memnun olurum.

 

[arzuhalci]

Açıklamalı tarif edermisiniz formulü şu hücrede denedim olmadı veya ekran resmi alıp izah ediniz.
Dosyanız bana hitap etmediği için tek tek çözümleyemem şunu şu hücreye uyguladım sonuç şu oldu
bu olması gerekir demeniz lazımki bakabilelim.şifrenizde yanlış makroda eksik bir durum varmı hangi hücredeki formülün ne şekilde olmasını istiyrosunuz

 

[mert25]

Verdiğiniz aşağıdaki kodu Basketbol sayfasında AK5 hücresine uyguladım, sonra Nagoya takımını seçip Takım Filtrele dedim fakat AK5 hücresine sonuç gelmiyor.

Bir de bu formüldeki B1'in mantığını anlayamadım.

VBA şifresini kaldırdım.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

 

[arzuhalci]

Formülünüzdeki temel sorun, KAYDIR (OFFSET) fonksiyonunun yapısı ve ALTTOPLAM (SUBTOTAL) ile olan etkileşimidir. Özellikle filtreleme yapıldığında, KAYDIR fonksiyonu filtrelenmiş satırları "atlayıp" sadece görünür olanları saymakta zorlanabilir veya yanlış referans noktasına odaklanabilir.

Ayrıca, KAÇINCI(B1;AJ5:AJ5393;0) kullanımı, B1 hücresindeki takım ismini AJ sütununda (sayıların olduğu sütun) aramaya çalışıyor gibi görünüyor. Takım isimleri muhtemelen başka bir sütundadır (örneğin A veya B sütunu).Eğer amacınız filtreleme yapıldığında AK5 hücresinde seçili takımın (B1) ortalamasını görmekse, şu yapıyı deneyin:

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

 

[arzuhalci]

Eğer "Filtreleme Yapmadan" B1'deki Takımı Bulmak İstiyorsanız:
Filtre butonuna basmadan direkt B1'deki veriye odaklanmak için şu mantığı kullanın (Takım isimlerinin A sütununda olduğu varsayılmıştır):
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Eğer "Takım Filtrele" dediğinizde tabloda sadece Nagoya satırları kalıyorsa, hücreye şu basit formülü yazmanız yeterlidir. Siz filtreyi değiştirdikçe bu rakam otomatik güncellenir:
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

109: Filtrelenmiş (görünür) satırları toplar.
102: Filtrelenmiş (görünür) satırlardaki sayıları sayar.

Hem Filtreye Hem B1 Hücresine Bağlı Yol
Eğer formülün sadece B1'de seçili olan takımı, filtre açık olsa da olmasa da hesaplamasını istiyorsanız (E sütunundaki takımlara bakarak), şu formülü kullanmalısınız:

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

 

[mert25]

Üstat, ben mi yanlış anlıyorum acaba, B1'de takım yok ki? Her şekilde B1 boş, bu nedenle B1'in formüldeki mantığını sormuştum. Ve inan hala bunun nedenini anlayamadım.

Zaman ayırdın, emek verdin, çok çok teşekkür ederim ama formülsüz çözüm olmuyorsa dosya her şekilde yavaşlayacak demektir.

İstediğim, o formüllerin VBA koduydu, olmuyorsa tadından bırakmak gerek diye düşünüyorum.

 

[arzuhalci]

Sayın Mert55
İlk mesajınızda aşağıdaki formülde AJ5:AJ5393 arasının filtrelenmesini istiyorum ama Nagoya 26. satırda olduğu için formülün 27. satırdan itibaren (AJ27:AJ5393) hesap yapması lazım. Excelde bunun mümkün olması gerek. diyorsunuz.Bizde bundan yola çıkarak yukardaki formüllerle yardımcı olmak istedim. Çünki sayfa vba şifreli olduğu için makro üretilemedei.Ayrıca verdiğiniz şifrede yanlış açılmıyor.

En pratik düzeltme (takım adı bir hücreden seçilsin) Diyelim takım seçimi B1 hücresinde varsayımı üzerinden formüller geliştiirldi.
B1 bir kriter (anahtar) hücresidir.Yani:
A sütununda B1’de yazan değere eşit olan satırlar bulunur
Sadece o satırlar üzerinden:Toplam alınır.Sayım yapılır.Ortalama hesaplanır

Kısaca : Siz bize makro için şunu ifade ediniz
Basketbol adlı sayfada K2-L2-M2-N2-O2-P2-Q2-R2-AE2-AF2-AG2-AH2-AK2-AL2-AM2-AN2 hücrelerindeki kırmızı hücrelerdeki formüllerimi makro ile yapalım diyorsunuz.