İşlem Gören Değeleri Başka Sayfada Sıralama

Erkan Yılmaz · 30 Aralık 2015

İki tane sayfamız var. Biri Hesap Özeti diğeri ise Hareket Girişi sayfası. Hareket Girişi sayfasında ki girişlere göre Hesap Özeti sayfasında Tarih aralığına göre rakamsal verileri getiriyorum.

Yapmak istediğim ise gönderdiğim dosya ekinde de göreceğiniz üzere;

Hareket Girişi sayfasında hangi hesap adlarında işlem varsa Hesap özetindeki Gelirler Hesabının altına o metinler, değerler gelsin istiyorum. Aynı zamanda tarih aralığını da göz önünde bulundurmamız gerekiyor

Mesela hareket girişinde "Diğer Gelirler" var Hesap Özetinden GELİRLER başlığının altında sadece Diğer Gelirler gelsin. Bütün Gelir grupları olmasın tabi bunu gider grupları içinde yapmamız gerekiyor.

Ben şu anda Veri girişinden çektim bu yüzden hepsi çıkıyor haraket girişine bağlı olmadan.

İlginiz için şimdiden teşekkür ederim.

İlgili Dosya KİŞİSEL BÜTÇE - Kopya.xlsx - 332 KB

bzace · 30 Aralık 2015

Merhaba,
Ben gelirler için yaptım aynı mantık ile diğerlerini yapabilirsiniz.
İki adet ad tanımlayın. Bunlar aşağıdaki gibi olsun.

gelir2 = 'VERİ GİRİŞ'!$B$20:$B$34
hesap_adı ='HAREKET GİRİŞİ'!$E$2:$E$26

Hesap özeti sayfası B7 hücresine,

Kod:

=İNDİS(gelir2;KÜÇÜK(
  EĞER(gelir2=DEVRİK_DÖNÜŞÜM(hesap_adı);
SATIR($1:$15));SATIR(A1)))
[COLOR="Blue"]Formül dizi formülüdür. CTRL+SHIFT+ENTER ile tamamlayınız.[/COLOR]

Saygılar,
Hay marja!

Erkan Yılmaz · 30 Aralık 2015

bzace' Alıntı:
Merhaba,
Ben gelirler için yaptım aynı mantık ile diğerlerini yapabilirsiniz.
İki adet ad tanımlayın. Bunlar aşağıdaki gibi olsun.

gelir2 = 'VERİ GİRİŞ'!$B$20:$B$34

hesap_adı ='HAREKET GİRİŞİ'!$E$2:$E$26

Hesap özeti sayfası B7 hücresine,

Kod:

=İNDİS(gelir2;KÜÇÜK( EĞER(gelir2=DEVRİK_DÖNÜŞÜM(hesap_adı); SATIR($1:$15));SATIR(A1))) [COLOR="Blue"]Formül dizi formülüdür. CTRL+SHIFT+ENTER ile tamamlayınız.[/COLOR]

Saygılar,
Hay marja!

Öncelikle Teşekkür Ediyorum. Ama yapamadım ya. #Değer hatası veriyor.

Ömer BARAN · 31 Aralık 2015

Merhaba.
Sayın bzace çevrimdışı durumda.

Sayın bzace'nin önerdiği cevabı belgenize uygulayınca (B7'ye uygulayıp aşağı doğru kopyalayınca) B7'deki sonuç Diğer Gelirler şeklinde oluşuyor.

Sanırım formül uygulaması veya ad tanımlamada hata yapıyorsunuz.

-- ad tanımlamadaki formül kısmında kopyala yapıştır yaparken = işaretinden sonra ve formülün sonunda ÇİFT TIRNAK oluşmuşsa bu çift tırnakları silin sadece sayfa adının başında ve sonunda TK TIRNAK olması lazım.
-- Ayrıca formülün DİZİ FORMÜLÜ olduğunu unutmayınız, formül metnini formül çubuğuna yapıştırdıktan sonra ENTER DEĞİL, CTRL+SHIFT+ENTER şeklinde formül girişi tamamlanmalıdır.
.

Erkan Yılmaz · 31 Aralık 2015

Ömer BARAN' Alıntı:
Merhaba.
Sayın bzace çevrimdışı durumda.

Sayın bzace'nin önerdiği cevabı belgenize uygulayınca (B7'ye uygulayıp aşağı doğru kopyalayınca) B7'deki sonuç Diğer Gelirler şeklinde oluşuyor.

Sanırım formül uygulaması veya ad tanımlamada hata yapıyorsunuz.

-- ad tanımlamadaki formül kısmında kopyala yapıştır yaparken = işaretinden sonra ve formülün sonunda ÇİFT TIRNAK oluşmuşsa bu çift tırnakları silin sadece sayfa adının başında ve sonunda TK TIRNAK olması lazım.
-- Ayrıca formülün DİZİ FORMÜLÜ olduğunu unutmayınız, formül metnini formül çubuğuna yapıştırdıktan sonra ENTER DEĞİL, CTRL+SHIFT+ENTER şeklinde formül girişi tamamlanmalıdır.
.

Çok teşekkür ediyorum her ikinizede hem sayın bzace'ye hemde size. CTRL+SHIFT+ENTER şeklinde tamamladım sonuca ulaştım ama GİDERLER kısmında başaramadım açıkcası. "AD TANIMLAMA" haricinde diğer bölümler için formülde değişiklik yapmam gerekiyor mu?

Aynı mantıkla diğer bölümler içinde deniyorum. "#YOK" hatası vermekte. Satır başvurusunu mu yanlış yapıyorum acaba?

Ömer BARAN · 31 Aralık 2015

Merhaba.

-- hesap_adı ad tanımlamasını-> ='HAREKET GİRİŞİ'!$E$6:$E$30 şeklinde değiştirin,
-- gider2 şeklinde yeni ad tanımlaması yapın -> ='VERİ GİRİŞ'!$B$70:$B$94

-- B19 hücresine (DİZİ FORMÜLÜ)

Kod:

=İNDİS(gider2;KÜÇÜK(EĞER(gider2=DEVRİK_DÖNÜŞÜM(hesap_adı);SATIR($1:$25));SATIR(A1)))

hesap_adı tanımlamasına ilişkin formülün HAREKET GİRİŞİ sayfasındaki
tüm dolu satırları kapsaması gerektiğini unutmayınız. Neticede verileriniz artacak.

NOT: Gerek gelir kısmında ve gerekse de buradaki formülde 1'den fazla tekrarlanan
hesap adları özet tablosunda da tekrarlanıyor, daha evvel de belirttiğim gibi
DİZİ FORMÜLLERİ konusunda pek deneyimim yok o yüzden bunun çözümünü bilemiyorum.
Sayın bzace konunun peşini bırakmayacak ve buna da çözüm bulacaktır.

Erkan Yılmaz · 31 Aralık 2015

Ömer BARAN' Alıntı:
Merhaba.

-- hesap_adı ad tanımlamasını-> ='HAREKET GİRİŞİ'!$E$6:$E$30 şeklinde değiştirin,
-- gider2 şeklinde yeni ad tanımlaması yapın -> ='VERİ GİRİŞ'!$B$70:$B$94

-- B19 hücresine (DİZİ FORMÜLÜ)

Kod:

=İNDİS(gider2;KÜÇÜK(EĞER(gider2=DEVRİK_DÖNÜŞÜM(hesap_adı);SATIR($1:$25));SATIR(A1)))

hesap_adı tanımlamasına ilişkin formülün HAREKET GİRİŞİ sayfasındaki
tüm dolu satırları kapsaması gerektiğini unutmayınız. Neticede verileriniz artacak.

NOT: Gerek gelir kısmında ve gerekse de buradaki formülde 1'den fazla tekrarlanan
hesap adları özet tablosunda da tekrarlanıyor, daha evvel de belirttiğim gibi
DİZİ FORMÜLLERİ konusunda pek deneyimim yok o yüzden bunun çözümünü bilemiyorum.
Sayın bzace konunun peşini bırakmayacak ve buna da çözüm bulacaktır.

Sevgili Ömer Bey, Diğer hücreler için yaptım ve formül sonucuna ulaştım ama benim asıl istediğim bu değildi. Şimdi tabloyu gönderdiğim zaman baktığınız zaman daha iyi anlayacaksınız.

Ben kısa da olsa anlatmak istiyorum. "HESAP ÖZETİ" sayfamızda "GİDERLER" kısmında "HAREKET GİRİŞİ" bölümündeki veriler geliyor ama kaç tane "Cep Telefonu" gideri varsa o geliyor. Yani tabloda da gördüğünüz üzere "Cep telefonu" 3 kez geldi. Bunun "Hareket Girişinde" varsa 1 kere gelmesi gerekiyor. Çünkü sayısal verileri ben tarih aralığına göre toplayarak getiriyorum. Bu tabi Gelirler kısmında da aynı şekilde olması gerekiyor. "Diğer Gelirler" 2 tane gelmiş.

Tekrardan tabloyu gönderiyorum ilgilenirseniz sevinirim.

KİŞİSEL BÜTÇE - Kopya.xlsx - 332 KB

Ömer BARAN · 31 Aralık 2015

Merhaba.
HESAP HAREKETLERİ sayfasındaki verilerinizin çoğalacağını düşünürsek, ben olsam dizi formülünü tercih etmez, bunun yerine yardımcı sütun kullanırdım (GİDERLER için aşağıda gerekli formülleri ve uygulamasını yazdım).

Bunun için HESAP HAREKETLERİ sayfasında J6 hücresine aşağıdaki formülü uygulayıp liste boyunca kopyalardım.

Kod:

=EĞER(C6="";"";EĞER(C6='HESAP ÖZETİ'!$B$3;C6&E6&H6;""))

Ardından VERİ GİRİŞ sayfasında C ve devamındaki sütundaki verileri birkaç sütun sağa alırdım (maksat C sütunu boşaltmak ve veri bütünlüğünü sağlamak) ve C sütununda örneğin GİDERler için C70 hücresine aşağıdaki formülü uygulayıp, C94 hücresine kadar kopyalardım.

Kod:

=EĞER(B70="";"";EĞER(EĞERSAY('HAREKET GİRİŞİ'!$J$6:$J$23;'HESAP ÖZETİ'!$B$3&B70&"*")>0;MAK($C$69:C69)+1;""))

Son olarak da HESAP ÖZETİ sayfası B19 hücresine aşağıdaki formülü uygulayıp B39'a kadar kopyalardım.

Kod:

=EĞER(SATIR()-18>MAK('VERİ GİRİŞ'!$C$70:$C$94);"";İNDİS(gider2;KAÇINCI(SATIR()-18;'VERİ GİRİŞ'!$C$70:$C$94;0)))

Erkan Yılmaz · 31 Aralık 2015

Ömer BARAN' Alıntı:
Merhaba.
HESAP HAREKETLERİ sayfasındaki verilerinizin çoğalacağını düşünürsek, ben olsam dizi formülünü tercih etmez, bunun yerine yardımcı sütun kullanırdım (GİDERLER için aşağıda gerekli formülleri ve uygulamasını yazdım).

Bunun için HESAP HAREKETLERİ sayfasında J6 hücresine aşağıdaki formülü uygulayıp liste boyunca kopyalardım.

Kod:

=EĞER(C6="";"";EĞER(C6='HESAP ÖZETİ'!$B$3;C6&E6&H6;""))

Ardından VERİ GİRİŞ sayfasında C ve devamındaki sütundaki verileri birkaç sütun sağa alırdım (maksat C sütunu boşaltmak ve veri bütünlüğünü sağlamak) ve C sütununda örneğin GİDERler için C70 hücresine aşağıdaki formülü uygulayıp, C94 hücresine kadar kopyalardım.

Kod:

=EĞER(B70="";"";EĞER(EĞERSAY('HAREKET GİRİŞİ'!$J$6:$J$23;'HESAP ÖZETİ'!$B$3&B70&"*")>0;MAK($C$69:C69)+1;""))

Son olarak da HESAP ÖZETİ sayfası B19 hücresine aşağıdaki formülü uygulayıp B39'a kadar kopyalardım.

Kod:

=EĞER(SATIR()-18>MAK('VERİ GİRİŞ'!$C$70:$C$94);"";İNDİS(gider2;KAÇINCI(SATIR()-18;'VERİ GİRİŞ'!$C$70:$C$94;0)))

Sevgili,Saygıdeğer üstadım gerçekten çok teşekkür ediyorum yardımlarınız için. İyi ki varsınız sizlerde olmasınız bizler de öğrenemeyeceğiz. Ama üzülerek belirtmek isterim ki yapamadım :-( söylediklerini harfiyen uyguladım. Gönderdiğim dosyaya uygulama yapıp göndermeni rica etsem. Ve Gelirler Ve diğer bölümler içinde yapmam gerekiyor. Bunu nasıl çözebilirim peki?

Ömer BARAN · 31 Aralık 2015

Merhaba.
HESAP ÖZETİ sayfasının B3 hücresindeki tarih ile D3:E3 tarih (ay, daha doğrusu tarih aralığı) aralığı ve
G3:H3 hücrelerindeki tarih aralığı (yıl, daha doğrusu tarih aralığı) arasında MUTLAKA bir ilişki var mıdır?

B3 hücresindeki tarihten farklı ay veya yıl (tarih aralığı) yazacak mısınız yoksa B3 hücresindeki tarihin içinde
bulunduğu ay ve yıl mı yazılıyor?

bzace · 31 Aralık 2015

Ömer Bey, çözüm üretmiş, alternatif olarak kullanabilirsiniz.
B7 hücresine girip aşağı doğru çoğaltınız. #SAY hatasından kurtulmak için EĞERHATA formülünü inceleyiniz.

Kod:

=İNDİS(gelir2;KÜÇÜK(EĞER(
  ESAYIYSA(KAÇINCI(gelir2;hesap_adı;)*
    KAÇINCI(KOD(gelir2);
  KOD(hesap_adı);));SATIR($1:$15));
SATIR(A1)))
[COLOR="Blue"]Formül dizi formülüdür. CTRL+SHIFT+ENTER ile tamamlayınız.[/COLOR]

Not: Ad tanımlamaları aynı şekilde kalsın, sonuçlar GELİRLER içindir.
Bu gecelik muhtemelen benden bu kadar yanlışlık varsa daha sonra bakmaya çalışırız.

Erkan Yılmaz · 31 Aralık 2015

Ömer BARAN' Alıntı:
Merhaba.
HESAP ÖZETİ sayfasının B3 hücresindeki tarih ile D3:E3 tarih (ay, daha doğrusu tarih aralığı) aralığı ve
G3:H3 hücrelerindeki tarih aralığı (yıl, daha doğrusu tarih aralığı) arasında MUTLAKA bir ilişki var mıdır?

B3 hücresindeki tarihten farklı ay veya yıl (tarih aralığı) yazacak mısınız yoksa B3 hücresindeki tarihin içinde
bulunduğu ay ve yıl mı yazılıyor?

Sevgili Üstadım.
B3 Hücresi Yarından itibaren =BUGÜN() formülünü kullanacağım.
Aylık Periyottaki tarih aralığında ise ;
Sadece D3 Hücresini manuel gireceğim. Hangi ayda isek onun başlangıcı olarak. E3 hücresinde ki tarihi ise B3 Hücresinden alacak.
Yıllık Periyottaki tarih aralığı ise;
G3 Hücresi yılın en başlangıcı sabit olacak yani 01.01.2016.H3 Hücresi ise sabit olarak yıl sonuda olabilir yada B3 hücresinden alabilirim. Fark etmiyor.

Erkan Yılmaz · 2 Ocak 2016

bzace' Alıntı:
Ömer Bey, çözüm üretmiş, alternatif olarak kullanabilirsiniz.
B7 hücresine girip aşağı doğru çoğaltınız. #SAY hatasından kurtulmak için EĞERHATA formülünü inceleyiniz.

Kod:

=İNDİS(gelir2;KÜÇÜK(EĞER( ESAYIYSA(KAÇINCI(gelir2;hesap_adı;)* KAÇINCI(KOD(gelir2); KOD(hesap_adı);));SATIR($1:$15)); SATIR(A1))) [COLOR="Blue"]Formül dizi formülüdür. CTRL+SHIFT+ENTER ile tamamlayınız.[/COLOR]

Not: Ad tanımlamaları aynı şekilde kalsın, sonuçlar GELİRLER içindir.
Bu gecelik muhtemelen benden bu kadar yanlışlık varsa daha sonra bakmaya çalışırız.

Sevgili Bzace,

Öncelikle Herkesin, Sizin ve Ömer Bey'in Yeni yılınızı en içten dileklerimle kutluyor, Sağlık Mutluluk getirmesini diliyorum.

Teşekkür ediyorum. Ömer Beyin çözümü "GELİRLER" için sonuça ulaştırdı. "SAY" hatasınıda EĞERHATA formülü ile çözdüm.

Giderler için Ad Tanımlaması yaptım anca aynı formülü kullanarak ve satır aralıklarını değiştirerek GİDERLER için kullanmak istedim ama başaramadım.

KASA VE BANKA Hesaplarını da yaptım ama diğer aşağıdaki hesapları yapamıyorum.

=EĞERHATA(İNDİS(Gider_Hesaplar;KÜÇÜK(EĞER( ESAYIYSA(KAÇINCI(Gider_Hesaplar;Hesap_Adı

*KAÇINCI(KOD(Gider_Hesaplar);KOD(Hesap_Adı)

);SATIR($1:$43));SATIR(A13)));0)

Satır Aralıklarında mı hata yapıyorum?

Yardımcı olursanız sevinirim.

Şu an ki güncel örnek dosyayı gönderiyorum.

KİŞİSEL BÜTÇE - Kopya.xlsx - 331 KB

Erkan Yılmaz · 2 Ocak 2016

Bu arada ben ad tanımlamalarını kendi anlayacağım şekilde düzenlemeler yaptım.
gelir2 = Gelir_Hesapları
hesap_adı = Hesap_Adı
Gider_Hesapları gibi zaten gönderdiğim dosyaya baktığınız zaman ad yöneticisinden görebileceksiniz.

Ömer BARAN · 2 Ocak 2016

Tekrar merhaba.
Dizi formülünü tercih etmişsiniz.
B19'daki formülü aşağıdaki ile değiştirip aşağı doğru kopyalayınız.
Formüldeki 25 sayısı Gider Hesapları ad tanımlamasındaki satır sayısı oluyor.

Kod:

[FONT="Trebuchet MS"][SIZE="2"]=EĞERHATA(İNDİS(Gider_Hesaplar;KÜÇÜK(EĞER( ESAYIYSA(KAÇINCI(Gider_Hesaplar;Hesap_Adı;)
*KAÇINCI(KOD(Gider_Hesaplar);KOD(Hesap_Adı);));SATIR($1:$[B][COLOR="Red"]25[/COLOR][/B]));SATIR(A1)));0)
[COLOR="Blue"]DİZİ FORMÜLÜ olduğundan formülü CTRL+SHIFT+ENTER ile giriniz.[/SIZE][/FONT][/COLOR]

Erkan Yılmaz · 2 Ocak 2016

Ömer BARAN' Alıntı:
Tekrar merhaba.
Dizi formülünü tercih etmişsiniz.
B19'daki formülü aşağıdaki ile değiştirip aşağı doğru kopyalayınız.
Formüldeki 25 sayısı Gider Hesapları ad tanımlamasındaki satır sayısı oluyor.

Kod:

[FONT="Trebuchet MS"][SIZE="2"]=EĞERHATA(İNDİS(Gider_Hesaplar;KÜÇÜK(EĞER( ESAYIYSA(KAÇINCI(Gider_Hesaplar;Hesap_Adı;) *KAÇINCI(KOD(Gider_Hesaplar);KOD(Hesap_Adı);));SATIR($1:$[B][COLOR="Red"]25[/COLOR][/B]));SATIR(A1)));0) [COLOR="Blue"]DİZİ FORMÜLÜ olduğundan formülü CTRL+SHIFT+ENTER ile giriniz.[/SIZE][/FONT][/COLOR]

Ömer Bey,

Çok Teşekkür ediyorum. Şimdi mantığını daha iyi anlamamı sağladınız.

Erkan Yılmaz · 2 Ocak 2016

Ömer Bey,

Bu dizi formülünü tarih aralıklarına bağlı olarak yapabilir miyiz acaba?

bzace · 8 Ocak 2016

€rK@n;836282' Alıntı:
Ömer Bey,

Bu dizi formülünü tarih aralıklarına bağlı olarak yapabilir miyiz acaba?

Dosyanızı indiremiyorum, aklımda kalanı ile birde tarih şartı eklemek istiyorsunuz.
İlgili resme göre kendi dosyanıza uyarlayınız.

A2:A7 aralığı tarihler,
B2:B7 aralığı farklı farklı veriler (sizin dosyanızda hesap adları olması lazım)
D1 tarih kriterimiz,
Formül içerisinde kırmızı alan hangi kriterler listelenecek onu gösterir.

C2 için formül;

Kod:

=EĞERHATA(İNDİS(B$2:B$7;KAÇINCI(;
  İNDİS((EĞERSAY(C$1:C1;B$2:B$7))/
(A$2:A$7=D$1)/(B$2:B$7={[COLOR="Red"]"aa"\"bb"[/COLOR]}););));"")
[COLOR="blue"]Formül dizi formülüdür.CTRL+SHIFT+ENTER ile tamamlayınız.[/COLOR]

İşlem Gören Değeleri Başka Sayfada Sıralama

Erkan Yılmaz

bzace

Erkan Yılmaz

Ömer BARAN

Uzman

Erkan Yılmaz

Ömer BARAN

Uzman

Erkan Yılmaz

Ömer BARAN

Uzman

Erkan Yılmaz

Ömer BARAN

Uzman

bzace

Erkan Yılmaz

Erkan Yılmaz

Erkan Yılmaz

Ömer BARAN

Uzman

Erkan Yılmaz

Erkan Yılmaz

bzace