Bu nedenle, Excel'de 3B referansın ne olduğunu zaten öğrendik. Eğlenceli gerçek şu ki, normal Excel 3B referansı, SUMIF işlevi gibi koşullu işlevlerle çalışmaz. Bu yazıda, SUMIF işleviyle çalışan 3B referans almanın nasıl yapıldığını öğreneceğiz.
Excel'de 3B referanslı SUMIF için genel formül
Karmaşık görünüyor ama değil (o kadar).
| =TOPLA(SUMIF(DOLAYLI("'"&sayfa_adi_aralik_araligi&"'!" & "ölçüt_araligi")), ölçüt,DOLAYLI("'"&sayfa_adi_aralik_adi&"'!" &"toplam_aralik"))) |
"'"name_range_of_sheet_names"'":Sayfa adlarını içeren adlandırılmış bir aralıktır. Bu çok önemli.
"Ölçüt aralığı":Aralık içeren kriterlerin metin referansıdır. (3 Boyutlu referans çalışması için tüm sayfalarda aynı olmalıdır.)
kriterler:Basitçe özetlemek için koymak istediğiniz koşuldur. Bir metin veya hücre başvurusu olabilir.
"Toplam aralığı":Toplam aralığının metin referansıdır. (3 Boyutlu referans çalışması için tüm sayfalarda aynı olmalıdır.)
Teorinin bu kadarı, SUMIF işlevi çalışırken 3B referans verelim.
Örnek: Excel'in 3B Referansını Kullanarak Birden Çok Sayfadan Bölgeye Göre Toplama:
Basit 3D referanslama örneğinde aldığımız verileri alıyoruz. Bu örnekte, benzer veriler içeren beş farklı sayfam var. Her sayfa bir ayın verilerini içerir. Ana sayfada, tüm sayfalardan bölgelere göre birimlerin ve koleksiyonun toplamını istiyorum. Önce Units için yapalım. Birimler, tüm sayfalarda D2:D14 aralığındadır.
Şimdi, SUMIF işleviyle normal 3D referanslamayı kullanırsanız,
=TOPLA(Ocak:Nis!A2:A14, Master!B4, Oca:Nis!D2:D14)
#DEĞER döndürecek! hata. O yüzden kullanamayız. Yukarıda belirtilen genel formülü kullanacağız.
Excel'in yukarıdaki Genel 3B referanslı SUMIF formülünü kullanarak, bu formülü C3 hücresine yazın:
| =TOPLA(SUMIF(DOLAYLI("'"&Ay&"'!" & "A2:A14")),Ana!B3,DOLAYLI("'"&Ay&"'!" &"D2:D14"))) |
Buraya aylar sayfaların adlarını içeren adlandırılmış bir aralıktır. Bu çok önemli.
Enter'a bastığınızda tam çıktınızı alırsınız.
O nasıl çalışır?
Formülün özü DOLAYLI işlevidir ve adlandırılmış aralık. burada dize"'"&Aylar&"'!" & "A2:A14"içindeki her sayfanın bir dizi aralık referansına çevirir. adlandırılmış aralık.
| {"'Oca'!D2:D14";"'Şubat'!D2:D14";"'Mar'!D2:D14";"'Nisan'!D2:D14"} |
Bu dizi şunları içerir: metin referansıaralıkların, gerçek aralıkların değil. Artık bir metin referansı olduğu için, DOLAYLI işlevi tarafından onları gerçek aralıklara dönüştürmek için kullanılabilir. Bu, DOLAYLI işlevlerin her ikisi için de olur. DOLAYLI işlevlerin içindeki metinleri çözdükten sonra (sıkı tutun), formül şöyle görünür:
| =TOPLA(TOPLA(TOPLA)(DOLAYLI({"'Oca'!A2:A14";"'Şubat'!A2:A14";"'Mar'!A2:A14";"'Nisan'!A2:A14"})) , Usta!B3,DOLAYLI({"'Oca'!D2:D14";"'Şubat'!D2:D14";"'Mar'!D2:D14";"'Nisan'!D2:D14"}))) |
Şimdi, SUMIF işlevi devreye giriyor (tahmin edebileceğiniz gibi DOLAYLI değil). Koşul ilk aralıkta eşleştirilir"'Ocak'!A2:A14". Burada DOLAYLI işlevi dinamik olarak çalışır ve bu metni gerçek aralığa dönüştürür (bu yüzden önce INDIRECT'i F9 tuşu ile çözmeye çalışırsanız sonucu alamazsınız.). Sonraki, aralıktaki eşleşen değerleri özetler"'Ocak'!D2:D14".Bu, dizideki her aralık için olur. Son olarak, SUMIF işlevi tarafından döndürülen bir dizimiz olacak.
| =TOPLAÇARI({97;82;63;73}) |
Şimdi SUMPRODUCT en iyi yaptığı şeyi yapıyor. Bu değerleri özetliyor ve 3D SUMIF fonksiyonumuzun çalışmasını sağlıyoruz.
Evet arkadaşlar, 3D SUMIF işlevini bu şekilde elde edebilirsiniz. Bu biraz karmaşık, buna katılıyorum. Bu 3B formülde çok fazla hata var. Her sayfada belirli bir hücrede SUMIF işlevini kullanmanızı ve ardından bu değerleri toplamak için normal 3B referansı kullanmanızı öneririm.
Umarım yeterince açıklayıcı olabilmişimdir. Excel/VBA ile ilgili başka herhangi bir sorguya başvurma konusunda herhangi bir şüpheniz varsa, aşağıdaki yorumlar bölümünde sorun.
Excel'de Göreli ve Mutlak Başvuru | Excel'de referans vermek her yeni başlayan için önemli bir konudur. Deneyimli excel kullanıcıları bile referans vermede hata yapar.
Dinamik Çalışma Sayfası Referansı | Excel'in DOLAYLI işlevini kullanarak dinamik olarak referans sayfaları verin. Bu basit…
Excel'de Referansları Genişletme | Genişleyen referans, aşağı veya sağa kopyalandığında genişler. Bunu yapmak için sütun ve satır numarasından önceki $ işaretini kullanırız. İşte bir örnek…
Mutlak Referans Hakkında Her Şey | Excel'de varsayılan referans türü görecelidir, ancak hücre ve aralıkların referansının mutlak olmasını istiyorsanız $ işaretini kullanın. Excel'de mutlak referanslamanın tüm yönleri burada.
Popüler Makaleler:
Verimliliğinizi Artıracak 50 Excel Kısayolu | Görevinizde daha hızlı olun. Bu 50 kısayol, Excel'de daha da hızlı çalışmanızı sağlayacaktır.
Excel'de DÜŞEYARA İşlevi | Bu, farklı aralıklardan ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel 2016'da EĞERSAY | Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli bir değeri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.
Excel'de SUMIF İşlevi Nasıl Kullanılır | Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.