Excel'de Dışa Aktarımdan Rapor Hazırlama: Aylık Toplam, Artış ve Sıralama

Muhasebe programı, ERP, satış sistemi: hepsinin bir "Excel'e aktar" düğmesi vardır. Çıkan dosya ise ham bir listedir; binlerce satır, tarihler, kategoriler, cariler, tutarlar. Yönetici ise bir sayfalık özet ister: aylara göre toplam, geçen yıla göre artış, en çok satan beş kategori.

Aynı özeti her ay elle hazırlıyorsanız zaman kaybediyorsunuz. Formülleri bir kez kurup yeni dışa aktarımı yapıştırmak yeterli olmalı. Aşağıda bunu Excel'de nasıl kuracağınızı anlatıyorum.

Adım 1: Veri sayfası ve yapıştırma düzeni

Ham veri için ayrı bir sayfa açın ve ilk satıra başlıkları yazın: tarih, kategori, cari, miktar, tutar. Dışa aktarımın sütun sırası değişse bile formüller bozulmasın diye başlık adlarını bir ayar hücresinde tutup formülleri bu adlara bağlamak ideal yoldur; bunu basitçe yapmak için sütun sıranızın aynı kalmasını sağlayın.

Yapıştırırken "değer olarak" yapıştırın; biçimler tabloyu bozmasın. Rapor sayfasındaki formülleri veri sayfasına değil, ona bağlı yardımcı sütunlara kurun.

Adım 2: Yardımcı sütunlar

Tarihten ay ve yılı türetin (tarih A sütununda):

=AY(A2) ay, =YIL(A2) yıl.

Bunlar aylık toplamı almanızı kolaylaştırır. Ayrıca kategori ve cari adlarında fazladan boşluk varsa temizleyin: =KIRP(B2). Aynı kategori sonunda boşlukla ve boşluksuz yazılmışsa Excel iki ayrı kategori sayar.

Adım 3: Aylara göre toplam

Rapor sayfasında 1'den 12'ye ay numaralarını yazın, rapor yılını bir hücrede tutun. Aylık tutar toplamı:

=ÇOKETOPLA(Veri!$E$2:$E$5001;Veri!$G$2:$G$5001;A2;Veri!$H$2:$H$5001;$B$1)

G ay, H yıl yardımcı sütunudur, B1 rapor yılıdır. Örnek: mart ayı tutarı 245.000 TL, şubat 220.000 TL ise önceki aya göre artış:

=EĞERHATA(C3/C2-1;"") → 245.000/220.000 − 1 = %11,4.

Geçen yılın aynı ayına göre artış için aynı formülü yıl−1 ile hesaplayıp oranlayın. İki ölçüt birbirini tamamlar: önceki aya göre mevsimsellik yanıltır, geçen yılın aynı ayına göre karşılaştırma bu etkiyi azaltır.

Adım 4: Kategori ve cari sıralaması

En çok 5 kategoriyi bulmak için önce kategori başına toplam gerekir. Benzersiz kategorileri bir listede toplayın (veya elle yazın) ve yanına ÇOKETOPLA koyun. Sıralamak için BÜYÜK işlevi:

=BÜYÜK($D$2:$D$31;1) en büyük, ardından 2, 3... ve adı bulmak için =İNDİS($C$2:$C$31;KAÇINCI(E2;$D$2:$D$31;0)).

Aynı tutarda iki kategori varsa KAÇINCI ilkini bulur; bu uç durumu bilin. Örnek sıralama: Kategori A 410.000, B 360.000, C 290.000... Bu tablo bir pasta veya sütun grafiğine dönüştürülerek sunumda kullanılabilir.

Adım 5: Kategori × ay matrisi

Hangi kategori hangi ayda yükseldi sorusunu bir matris yanıtlar: satırlarda kategori, sütunlarda ay, hücrede iki ölçütlü ÇOKETOPLA. Formülü bir kez yazıp sürükleyin. Koşullu biçimlendirme olarak renk skalası uygulayın; sıcak noktalar hemen görünür.

Adım 6: Veri kontrolü

Rapor, ham verinin kalitesi kadar doğrudur. Aşağıdaki dört kontrolü sayaçla yapın:

• Boş tarih: =EĞERSAY(A2:A5001;"")
• Sayı yerine metin: tutar sütununda =SAY(E2:E5001) satır sayısına eşit olmalı.
• Dönem dışı tarih: rapor yılı dışındaki satırlar.
• Mükerrer kayıt: =ÇOKEĞERSAY(A:A;A2;C:C;C2;E:E;E2)>1.

Bu kontroller, "rakam tutmuyor" tartışmasını başlamadan bitirir. En sık sebep, tutarın metin olarak gelmesi ve toplamda sayılmamasıdır; virgül-nokta ayracı yanlış olan tutarlar da böyle davranır.

Sık yapılan hatalar

• Aralığı kısa bırakmak. Veri 5.000 satıra çıktıysa formül 2.000'de bitiyorsa sessizce eksik toplar.
• Dışa aktarım başlığını değiştirmek. Program güncellemesiyle başlık adı değişirse formüller boş sonuç verir.
• Rapor yılını unutmak. Yıl hücresini güncellemeden yeni yılı yapıştırırsanız eski yılın raporunu görürsünüz.
• Yapıştırırken eski verinin kalması. Yeni dışa aktarım daha kısaysa alttaki eski satırlar raporu bozar; önce eski veriyi silin.

Hazır şablon: Dışa Aktarımdan Evrensel Rapor

Bunları baştan kurmak istemezseniz ücretli bir Excel şablonu mevcut. Makro ve yeni işlevler yoktur; Excel 2007 ve üzeri ile LibreOffice'te çalışır.

• “Ayarlar” sayfasında rapor yılı ve dışa aktarımınızdaki sütun adları belirlenir.
• “Veriler” sayfası başlık ve yapıştırma için 5000 satır ile yardımcı normalleştirme sütunlarını (ay, yıl, anahtarlar) içerir.
• “Özet” sayfasında aylara göre toplamlar, önceki aya ve geçen yılın aynı ayına göre artış, en çok 5 kategori, en çok 10 cari, “kategori × ay” matrisi ve sütun ile pasta grafikleri bulunur.
• “Dönem seçimi” sayfası seçilen kategorilere göre herhangi bir dönem için tutar ve miktar verir.
• “Veri kontrolü” sayfası boş tarihleri, sayı yerine metni, dönem dışı tarihleri ve mükerrer kayıtları sayaçlar ve açıklamalarla gösterir.

Tarih, kategori, miktar ve tutar içeren her tablo “Veriler” sayfasına yapıştırılabilir. Sınırlar: en çok 5000 satır, 30 kategori ve 200 cari. Dosya Türkçedir.

Sık sorulan sorular

Dışa aktarımda tarih metin olarak geliyorsa ne yapmalıyım?

Sütunu seçip Veri sekmesinde "Metni Sütunlara Dönüştür" ile tarih biçimini seçin; ya da =TARİHSAYISI(A2) formülüyle çevirin.

Binlerce satırda Excel yavaşlarsa?

Yardımcı sütunları gereksiz uzatmayın, gerekmedikçe bütün sütun başvurusu (A:A) yerine sabit aralık kullanın ve hesaplamayı gerekirse elle moda alın.

Aynı raporu birden fazla şube için nasıl alırım?

Her şube için ayrı bir dosya kopyası kullanabilir ya da şube sütununu ölçüt olarak formüllere ekleyebilirsiniz.

Raporu yönetime nasıl göndermeliyim?

Özet sayfasını PDF olarak kaydedin; ham veriyi göndermeyin, özellikle cari adları gibi hassas bilgiler içeriyorsa.