Tarih Aralığındaki Toplamları Birleştirilmiş Alanda Gösterme

[merakli]

Excel Versiyonu

Excel 2016

Excel Sürümü

64 Bit

Excel Dili

Türkçe

FORMÜLLE YAPMAYA ÇALIŞTIĞIM İŞLEM ŞU ŞEKİLDEDİR.​

Başlama Tarihi (Rapor!$F$2) ile Bitiş Tarihi (Rapor!$F$3) arasındaki Gider!$B$2:$B$201 arasındaki uyumlu sonuçlardan Gider!$F$2:$F$201 kategorilerinin toplamını alıp Rapor$A$2:$F$11 arasına yazdırmak istiyorum.

Rapor$A$2:$F$11 arasındaki tablolar birleştirilmiş hücrelerden oluşmakta bunun için dizi fonksiyonlarını çalıştıramamaktayım.

Aklıma şu şekilde bir çözüm geldi.

G sütununda Gider!$G$2:$G$201 arasında;
TOPLA.ÇARPIM((Gider!$B$2:$B$201>=Rapor!$J$2)*(Gider!$B$2:$B$201<=Rapor!$J$3)*(Gider!$F$2:$F$201=Gider!F2)*(Gider!$E$2:$E$201-SATIR(Gider!$E$2:$E$201)/KUVVET(10;10)))

@Ömer BARAN beyin önceki yıllarda bir soruma verdiği örnekten faydalanarak bu formülü oluşturdum.

Çıkan sonuçlardan en büyük olan 1. den 10. a kadarını Rapor sayfasına alabilsem aslında yeterli olacak. BÜYÜK ya da ELEMAN ile almaya çalıştım ancak başaramadım. (Çok veri olmadığı için 10 tane sonuç çıkarmayacağını düşünerek böyle yaptım.)

Bu konuda desteklerinizi rica ediyorum.

 

ÇAPRAZEŞLEŞTİR Formülü Hatalı Sonuç Düzeltme

Boş Hücrelerin Günlük Ortalamasını Hesaplama

[Ömer BARAN]

-- Ya Rapor sayfasında hücre birleştirmesi olmayan bir alana dizi formülüyle sonuç alıp, oredeki verileri de birleştirilmiş hücrelerin olduğu nihai alana basit formüllerle çekin.
-- Ya da veri kaynağı sayfada yardımcı alan kullanabilirsiniz.
Telefondayım, ancak bu kadar yazabildim.

 

[merakli]

@Ömer BARAN
Bu şekilde yardımcı alan kullanarak
kategorilerin toplamlarını aldım.
Ancak
Rapor sayfasına
en büyük 1.
sonra 2.
....
10. olan ya da sıfırdan büyük olanları
alamıyorum.

Büyük($G$2:$G$201;1) yazdıktan sonra
Büyük($G$2:$G$201;2) yazsam da
doğru sonuç vermiyor.

1	Temizlik	1.254,00 TL
2	Gıda	1.000,00 TL
3	Kırtasiye	241,80 TL
4	Giyim	51,60 TL

bu şekilde sonuç vermiyor.
1254 ten sonra 1000 i alabilmek için yardımcı alandan nasıl bir formül kullanarak alabilirim onu bulmaya çalışıyorum.
soldaki sıra numaraları elle yazılıp o sıra numarası referans alınabilir.

 

[Ömer BARAN]

Telefondan hazırladığım için umarım hata yapmadım.
-- Veri sayfası H2 hücresine birinci formülü uygulayın ve liste boyunca kopyalayın (yardımcı alan)
-- İkinci formülü, Gider sayfası B6 hücresine uygulayın (dizi formülü)
-- Üçüncü formülü Gider sayfası C6'ya uygulayın ve aşağı doğru kopyalayın (dizi formülü olmadığından, hücre birleştirme sorun olmaz) .

Belki, madem gider sayfasına dizi formülü uygulanıyor, veri sayfasındaki yardımcı alanı kullanmadan, verdiğim dizi formülü düzerlenebilir belki. Bakmak lazım.

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

 

[PriveT]

Sayın @merakli merhaba,
Merakımı hoş görün. Hücreleri mutlaka birleşik kullanmanızı gerektirecek bir durum mu var acaba?

 

[PriveT]

Alternatif olsun.

Gider sayfası G2 hücresine:

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

Rapor sayfası F2 hücresine:

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

Rapor Sayfası C2 Hücresine:

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

yazıp aşağı doğru çoğaltınız.

 

[merakli]

@PriveT Bey
Şöyle açıklayayım.
Bu dönemlik Gelir ve Giderleri, Banka ve modülde bulunan paraları gösteren aynı zamanda yapılan işlemleri metin olarak belirten bir denetim raporu.. yani bir sayfada birden fazla tablo var (hücre genişlikleri farklı farklı) ve paragraflar şeklinde metinlerde var.
Alt tarafta ise 5 adet üyenin imza bölümleri vb. var.

Bende word şeklinde bir yapıyı kurabilmek adına 0,5 lik boşluklar oluşturarak, sütun genişliği 1 olan 59 hücreyi yan yana tek bir 4A sayfası oluşturdum.
Metin yazacağımda 59 hücreyi birleştirerek satır yüksekliklerini de ayarlayarak metin yazıyorum. Tablolarında içindeki hücre genişliklerini de istenilen hücre kadar birleştirerek oluşturuyor ve içine gelir ve giderlerden raporlaştırıyorum. Sonrasında bunu pdf formatında oluşturup kullanıyorum.
Bu şekilde yapmadığımda üstteki tabloda kullandığım 1 hücre alttaki tabloya gereğinden geniş gelebiliyor vb.
Umarım ifade edebilmişimdir.
Başka bir çözüm göremediğim için böyle yaptım. Alternatifi var mıdır? Varsa bilmekte isterim.

 

[merakli]

@Ömer BARAN Bey,

Cevabınızı önceki dosya üzerinden hazırladığınız fark ettim ve onları orijinal dosyaya göre düzenledim.
Ancak iki pürüz ortaya çıktı.

Bu paylaşımda yüklü Örnek dosyanın Gelir sayfasının 39 ve 47. satırlarındaki Tarih aralığı istenilen tarih aralığının dışında olmasına rağmen H sütunundaki formülde G sütununda 1. tekrar eden kayıtlar istenildiği için tarih kriteri göz ardı ediliyor olduğu için sonuçlarda bu iki satırda listeleniyor.
Tarih aralığındaki kayıtları listelemek için ilk formülü sanırım revize etmem gerekiyor.
Birde 3. görselde görüldüğü gibi (belki de ilk sorundan kaynaklı) toplamı sıfır (0) olan verilerde görünüyor.
Sıfır (0) dan büyük olanlar listelendiğinde sanırım çözmüş olacağız.

Birleştirilmiş hücreleride birleştirmeden kullandım. Sonuçta sayfayı yazdırdığımızda buralar görünmemektedir.

Örnek çalışan uyarlanmış dosyayı da ekliyorum

Sizin formüller aşağıdaki şekilde düzenledim.

Gelir sayfası H2 hücresine:

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

Denetim_1 sayfası C20:C26 hücrelerine: (Dizi Formülü)

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

Denetim_1 sayfası W20:W26 hücrelerine:

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

 

[Ömer BARAN]

Dizi formülü kullanmak yerine "yardımcı alan kullanılsın dedim.
A1'deki sayı, listedeki sonuç adeti.

 

[merakli]

@Ömer BARAN
Aynı anda yazdık sanırım. Sizden kısa süre önce paylaşıp bahsettiğim sizin de en son paylaştığınız dosyada Gider sayfasındaki en alttaki 4 tanesi tarih aralığının dışındakiler.

 

[Ömer BARAN]

Nihai sonuç sayfası üzerinden kontrol yaparsanız,
yardımcı alanda BİRdan küçük olan değerlerin ait olduğu (bunun anlamı tarih koşuluna uyan veri yok demek)
gider türlerinin tutarı SIFIR olarak dönecektir. SIFIR veya diğer toplam tutarların eşitlik halinde öncelik sırası kaynak taraftaki listede üstte olmalarıdır.

Sonuç sayfasında hatalı sonuç alınıyor mu alınmıyor mu?
İsteğiniz, sonucu SIFIR olanların listeye hiç girmemesi midir?

 

[PriveT]

Sayın @merakli ,
Öncelikle açıklama için teşekkürler.
Peki paylaştığım dosya ve formüller?
Hata mı var?

 

[merakli]

@PriveT Bey teşekkür ediyorum. Bugün biraz yoğun olduğum için size yazamadım. Kusuruma bakmayınız.

Sizin dosyanızıda orjinal dosyama uyarladım. Beklenilen sonucu vermişti.

Önceki yıllarda da google e-tablolar üzerinden veri alma konusunda da desteğiniz olmuştu.
Hâlâ o kodları geliştirip çalışmalarımda kullanıyor ve sizi anıyorum.

Çok teşekkür ediyorum.

Şuan da da henüz bilgisayarı açmadım mesajınızı görünce yazmak istedim.

Gecikme için özür dilerim.

@Ömer BARAN bey ve size çok şey borçluyuz.

Bayraktarın yerine olsam ben sizleri kodlama ekibine davet ederdim.

Tekrar teşekkürler. Her şey gönlünüzce olsun.

 

[merakli]

@Ömer BARAN Bey

Sonuç sayfasında hatalı sonuç alınıyor mu alınmıyor mu? (3 ve 4. sonucun görünmesi söylenebilir)
İsteğiniz sonucu SIFIR olanların listeye hiç girmemesi midir? (Evet)

 

[Ömer BARAN]

A20 hücresinde yer alan ve SIRA NO üreten formülü aşağıdakiyle değiştirmek yeterli olur.
Belirttiğiniz sorun kalmaz.

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

 

[merakli]

@PriveT Bey

Peki paylaştığım dosya ve formüller?
Hata mı var?

Sormanızın sebebi sanıyorum ki Ömer beyin formülü üzerine de yazmamdan dolayı sorduğunuz düşünerekten bir cevap yazıyorum.

Çözüme sayenizde ulaştım. Bu formülleri kullanacağım. (Formülde sadece şu çekincem var KAYDIR ve KAÇINCI kullanarak toplam para değerini listeden kategori adını çekiyoruz. Bu para değeri 2 tane aynı değer olunca acaba sorun yaşar mıyız diye aklıma geliyor)
=EĞER(W20<>"";KAYDIR(Gelir!$F$1;KAÇINCI(W20;Gelir!$G$2:$G$201;0) ;"")

Önceki paylaşımımda da yazmıştım.

Birden fazla çözüm olduğunda ben o cevapları kendimce işlem basamaklarını Formülü Değerlendir ve F9 kullanarak inceliyorum. Sizin ve Ömer beyin diğer tarafta @ExcelDepo'nun formüllerini adım adım inceliyorum. Yaklaşım tarzlarını ve onu neden bu şekilde yaptıklarını öğreniyorum.
Bunun için alternatif yolların olması beni daha çok memnun ediyor. Çünkü ufkumu genişletiyorum.

Örneğin ömer ben bir ara SATIR(Gider!$E$2:$E$203)/KUVVET(10;10)) şeklinde KUVVET(10;10) şeklinde kullanırken bu son kullanımda SATIR()/10000 e böldü bunu not aldım.

onun kullanımında hem satış toplamını hem kategoriyi araya boşluk koyarak kullanma fikrini kaptım. Bu hiç aklıma gelmemişti.

EĞERSAY'ı Siz de Ömer Bey de EĞERSAY($F$2:$F2;F2)=1 şeklinde kullandınız. Bu da benim işime bundan sonra çok yarayacak....

vb.

Bunun için her sonuç benim açımdan çok değerli ve önemli.

Emeği geçenlere tekrar çok teşekkür ediyorum.

 

[merakli]

@Ömer BARAN Teşekkürler sorun çözülmüş oldu.

 

[PriveT]

@PriveT Bey

Peki paylaştığım dosya ve formüller?
Hata mı var?

Sormanızın sebebi sanıyorum ki Ömer beyin formülü üzerine de yazmamdan dolayı sorduğunuz düşünerekten bir cevap yazıyorum.

Sorma sebebim herkes gibi ben de paylaştığım bir çözüme olumlu veya olumsuz bir tepki beklememdir.

(Formülde sadece şu çekincem var KAYDIR ve KAÇINCI kullanarak toplam para değerini listeden kategori adını çekiyoruz. Bu para değeri 2 tane aynı değer olunca acaba sorun yaşar mıyız diye aklıma geliyor)

Evet formülü düzenlerken, eldeki veriler üzerinden gidip bu ayrıntıyı atladım.

 

[Ömer BARAN]

@PriveT , @merakli

İşte bu nedenle yardımcı alandaki formülün sonuna,
asıl toplam tutarı etkilemeyecek ama eşitliği de bozmak üzere + SATIR()/1000 gibi bie ekleme yapılmak gerekiyor.
Sonuç tablosunda, KAÇINCI ile sıra numarası bulunup, yardımcı alandaki tutarın tamsayı kısmı alınıyor.

@merakli buradaki 1000 sayısını değiştirip veri satır adetini yazıyor (bence hata ediyor).
Listenin son satırında ilgili tutara 1 ekleme sonucu oluşabilir ve sonuç da yanıltıcı olabilir.
Hatta bence 1000 yerine daha büyük bir sayı kullanıp,
sonuç tablosundaki formülde de TAMSAYI yerine virgülden sonra 2 basamak olacak şekilde yuvarlanmış değeri almak en doğrusu.

İyi çalışmalar dilerim.