Excel’de Hükümet ve Kamu Hizmetleri Veri Analizi ve Raporlama

Kısa cevap: Kamu hizmetleri veri analizi, belediye, il müdürlüğü veya bakanlık gibi kurumların bütçe, hizmet ve vatandaş taleplerine ait verileri ölçülebilir göstergelere dönüştürmesidir. Excel’de bu iş beş temel analizle yapılır: bütçe gerçekleşme oranı, nüfusa göre kişi başı hizmet göstergeleri, vatandaş başvurularının çözüm süresi analizi, yatırım projelerinin fiziki ve nakdi gerçekleşme takibi ve performans programındaki hedef–gerçekleşen karşılaştırması.

Bu rehberde kurgusal bir ilçe belediyesinin verileri üzerinden her hesabı Türkçe Excel formülleriyle gösteriyoruz. Rakamlar örnektir; gerçek bir kurumun verisi değildir.

Kamu hizmetleri veri analizi için veriler nereden gelir?

Kamu kurumlarında veri genellikle birden fazla sistemde dağınık halde bulunur. Bütçe ve harcama verileri muhasebe yazılımından, başvurular çağrı merkezi veya e-belediye sisteminden, nüfus verileri ise TÜİK’in Adrese Dayalı Nüfus Kayıt Sistemi sonuçlarından alınır. Ayrıca birçok büyükşehir belediyesi açık veri portallarında hizmet verilerini CSV olarak yayımlamaktadır; bu dosyalar karşılaştırmalı analiz için değerli bir kaynaktır.

Veri kümesi	Örnek sütunlar	Kaynak
Bütçe	Kurumsal kod, Ekonomik kod, Başlangıç ödeneği, Eklenen, Düşülen, Harcanan	Muhasebe / bütçe sistemi
Başvurular	Başvuru No, Mahalle, Kategori, Kanal, Açılış, Kapanış	Çağrı merkezi, e-belediye
Nüfus	Mahalle, Yıl, Toplam nüfus, Yaş grubu	TÜİK ADNKS
Yatırımlar	Proje, Birim, Sözleşme bedeli, Ödenen, Fiziki ilerleme	Fen işleri, yatırım takip
Performans	Hedef, Gösterge, Plan değeri, Gerçekleşen	Performans programı

Bu tabloları birleştirmek için ortak anahtarlar standartlaştırılmalıdır. Örneğin mahalle adları farklı sistemlerde “Cumhuriyet Mah.”, “CUMHURİYET MAHALLESİ” gibi farklı yazılabilir. Power Query’de Biçim > BÜYÜK HARF ve Değerleri Değiştir adımlarıyla tek tip hale getirmek, sonraki tüm eşleştirmelerin doğru çalışmasını sağlar.

Kamu hizmetleri veri analizi öncesinde veri kalitesi nasıl kontrol edilir?

Kamu verisinde en sık karşılaşılan sorunlar eksik kayıtlar, mükerrer başvurular ve tutarsız kodlamalardır. Bu nedenle analiz öncesinde kısa bir kalite kontrol listesi uygulamak, yanlış sonuçların meclis veya faaliyet raporuna girmesini önler.

• Mükerrer kayıt: Aynı vatandaşın aynı gün aynı konuda iki kanaldan başvurması sık görülür. Yardımcı sütuna =ÇOKEĞERSAY([T.C. Maskeli];[@[T.C. Maskeli]];[Kategori];[@Kategori];[Açılış];[@Açılış]) yazarak 1’den büyük olanları işaretleyin.
• Eksik alan: Mahalle veya kategori bilgisi boş olan kayıtların oranını =BOŞLUKSAY(tblBasvuru[Mahalle])/SATIRSAY(tblBasvuru[Mahalle]) ile hesaplayın. Oran yüksekse mahalle bazlı yorumlar temkinli yapılmalıdır.
• Mantık hatası: Kapanış tarihi açılıştan önce olan kayıtlar =[@Kapanış]<[@Açılış] koşuluyla bulunur ve kaynak birime düzeltme için gönderilir.
• Kod tutarlılığı: Bütçe tablosunda ekonomik kodların analitik bütçe sınıflandırmasıyla uyumlu olduğu kontrol edilir.

Ayrıca kişisel verilerin korunması açısından analiz dosyasında T.C. kimlik numarası, telefon ve açık adres tutulmamalıdır. Bunun yerine maskelenmiş veya kodlanmış bir kimlik kullanmak, KVKK yükümlülüklerine uygun bir çalışma düzeni sağlar. Son olarak her veri kümesi için kaynak, alınma tarihi ve sorumlu birim bilgisini içeren küçük bir veri sözlüğü sayfası hazırlayın. Böylece rapor bir yıl sonra yeniden üretilmek istendiğinde hangi dosyanın kullanıldığı açıkça bilinir.

Bütçe gerçekleşme oranı Excel’de nasıl hesaplanır?

Bütçe gerçekleşme oranı, harcanan tutarın yıl sonu ödeneğine oranıdır. Yıl sonu ödeneği ise başlangıç ödeneğine eklenen tutarlar ve aktarmalar dahil edilip düşülen tutarlar çıkarılarak bulunur. Bu ayrım önemlidir; çünkü yalnızca başlangıç ödeneğiyle hesaplanan oran, yıl içindeki revizyonları gizler.

• Yıl sonu ödeneği: =[@[Başlangıç]]+[@Eklenen]-[@Düşülen]
• Gerçekleşme oranı: =EĞERHATA([@Harcanan]/[@[Yıl Sonu Ödeneği]];0)
• Birim bazında toplam: =ÇOKETOPLA(tblButce[Harcanan];tblButce[Birim];A2)/ÇOKETOPLA(tblButce[Yıl Sonu Ödeneği];tblButce[Birim];A2)

Ekonomik sınıf	Yıl sonu ödeneği (milyon TL)	Harcanan (milyon TL)	Gerçekleşme
01 Personel giderleri	310	301	%97
03 Mal ve hizmet alımları	420	368	%88
05 Cari transferler	45	39	%87
06 Sermaye giderleri	380	224	%59

Örnek tabloda sermaye giderlerindeki düşük gerçekleşme hemen dikkat çeker. Dolayısıyla yatırım projelerinin takvimi ayrıca incelenmelidir. Bunun yanı sıra aylık gerçekleşmeyi kümülatif olarak izlemek, yıl sonuna yığılan harcamaları erken fark etmeyi sağlar. Örneğin eylül sonunda sermaye giderlerinde gerçekleşme %30’un altındaysa, yıl sonu hedefine ulaşmak gerçekçi değildir.

Kişi başı hizmet göstergeleri nasıl karşılaştırılır?

Mahalleler veya ilçeler arasında adil karşılaştırma yapmak için mutlak sayılar yerine nüfusa oranlanmış göstergeler kullanılır. Örneğin park alanı, kütüphane üyesi veya sosyal yardım sayısı mahalle nüfusuna bölünür.

Nüfusu başka bir tablodan çekmek için =ÇAPRAZARA([@Mahalle];tblNufus[Mahalle];tblNufus[Nüfus];"Bulunamadı") kullanılır. Ardından kişi başı yeşil alan =[@[Park Alanı m2]]/[@Nüfus], bin kişi başına başvuru sayısı ise =[@Başvuru]/[@Nüfus]*1000 formülüyle hesaplanır.

Mahalle	Nüfus	Kişi başı yeşil alan (m²)	Bin kişi başı başvuru
Cumhuriyet	42.300	6,8	118
Yeşiltepe	18.900	11,2	74
Sanayi	27.600	2,1	166
Merkez	35.100	4,5	139

Örneğin Sanayi mahallesinde hem yeşil alan düşük hem başvuru yoğunluğu yüksekse, bu mahalle yatırım önceliği için güçlü bir aday olur. Ayrıca yaş grubu verisi varsa, çocuk oyun alanları veya yaşlı bakım hizmetleri ilgili yaş grubunun nüfusuna göre oranlanmalıdır. Böylece hizmet dağılımı ihtiyaca göre değerlendirilmiş olur.

Bununla birlikte nüfus yılı ile hizmet verisinin yılı eşleşmelidir. Örneğin 2025 başvuru sayısını 2022 nüfusuyla oranlamak, hızlı büyüyen mahallelerde göstergeyi olduğundan yüksek gösterir. Bu nedenle nüfus tablosunda yıl sütunu tutun ve eşleştirmede ÇAPRAZARA işlevine mahalle ile yıl bilgisini birlikte verin: =ÇAPRAZARA([@Mahalle]&[@Yıl];tblNufus[Mahalle]&tblNufus[Yıl];tblNufus[Nüfus]).

Kamu hizmetleri veri analizi ile vatandaş başvuruları nasıl izlenir?

Çağrı merkezi, e-belediye ve CİMER gibi kanallardan gelen başvurular, hizmet kalitesinin en doğrudan göstergesidir. Her başvuru için çözüm süresi gün olarak hesaplanır: =EĞER([@Kapanış]="";BUGÜN()-[@Açılış];[@Kapanış]-[@Açılış]). İş günü bazında ölçmek için =TAMİŞGÜNÜ([@Açılış];[@Kapanış];tblTatil[Tarih]) formülü resmi tatilleri de hesaba katar.

PivotTable’da Kategori satırlara, Ay sütunlara alınır ve değer alanında başvuru sayısı ile ortalama çözüm süresi gösterilir. Ancak ortalama yerine medyan kullanmak daha doğru bir tablo verir; çünkü birkaç yıllık altyapı başvurusu ortalamayı bozabilir. Medyan için =ORTANCA(FİLTRE(tblBasvuru[Süre];tblBasvuru[Kategori]=A2)) yazılır.

Örneğin “yol ve kaldırım” kategorisinde medyan çözüm süresi 21 gün, “temizlik” kategorisinde 2 gün çıkabilir. Bu fark doğaldır; dolayısıyla kategoriler için ayrı hedef süreler belirlenmelidir. Bunun yanı sıra açık başvuruların yaş dağılımını izlemek önemlidir. 30 günü aşan açık başvuruları koşullu biçimlendirmeyle işaretleyip sorumlu birime haftalık liste olarak göndermek, birikmeyi önler.

Yatırım projelerinin fiziki ve nakdi gerçekleşmesi nasıl takip edilir?

Yatırım programındaki her proje için iki ilerleme ölçüsü vardır: fiziki gerçekleşme (işin yüzde kaçı tamamlandı) ve nakdi gerçekleşme (sözleşme bedelinin yüzde kaçı ödendi). İkisi arasındaki fark, projede sorun olup olmadığını gösterir.

• Nakdi gerçekleşme: =[@Ödenen]/[@[Sözleşme Bedeli]]
• Zaman ilerlemesi: =MİN(1;(BUGÜN()-[@Başlangıç])/([@[Planlanan Bitiş]]-[@Başlangıç]))
• Gecikme bayrağı: =EĞER([@[Fiziki İlerleme]]<[@[Zaman İlerlemesi]]-0,15;"Gecikmede";"")

Örneğin süresinin %70’i geçmiş bir projenin fiziki ilerlemesi %40 ise proje gecikmededir. Öte yandan fiziki ilerleme %40 iken nakdi gerçekleşme %65 ise, işin önünde ödeme yapılmış olabilir ve bu durum denetim açısından ayrıca incelenmelidir. Ayrıca proje bazında ödenek kullanımı, bütçe tablosundaki sermaye gideri gerçekleşmesiyle karşılaştırılmalıdır. İki tablo arasındaki toplam farkı bir kontrol hücresiyle izlemek, kayıt eksiklerini erkenden gösterir. Son olarak bu tabloyu Gantt görünümüne çevirmek için yığılmış çubuk grafik kullanılabilir; ilk seri başlangıç tarihini gösterir ve dolgusu kaldırılır.

Performans programı göstergeleri nasıl raporlanır?

5018 sayılı Kamu Mali Yönetimi ve Kontrol Kanunu kapsamında kamu idareleri stratejik plan ve performans programı hazırlar. Bu programlardaki her performans göstergesi için plan değeri ve gerçekleşen değer yıl sonunda faaliyet raporunda karşılaştırılır. Excel, bu karşılaştırmayı sistematik hale getirmek için idealdir.

Gösterge tablosuna “Yön” sütunu ekleyin: artması istenen göstergeler için 1, azalması istenenler için −1. Böylece başarı oranı tek formülle hesaplanır: =EĞER([@Yön]=1;[@Gerçekleşen]/[@Plan];[@Plan]/[@Gerçekleşen]). Ardından %90 üzeri yeşil, %70–90 sarı ve altı kırmızı olacak şekilde simge kümesi uygulanır.

Ayrıca her kırmızı gösterge için sapma nedeni sütunu doldurulmalıdır. Örneğin “ihale süreci uzadı” veya “mevsim koşulları” gibi açıklamalar, faaliyet raporunun anlatı bölümünü yazmayı kolaylaştırır. Kısacası kamu hizmetleri veri analizi, yalnızca rakam üretmek değil, hesap verebilirliği desteklemek için yapılır.

Analiz sonuçları kamuoyuna ve meclise nasıl sunulmalı?

Meclis veya kamuoyu sunumlarında teknik tablolar yerine sade göstergeler tercih edilmelidir. İyi bir sunum dört bloktan oluşur: bütçe gerçekleşme özeti, mahalle bazında hizmet haritası, başvuru çözüm süreleri ve öne çıkan yatırımların durumu.

Mahalle haritası için Excel’in Ekle > Haritalar > Dolgulu Harita grafiği il ve ilçe düzeyinde çalışır; mahalle düzeyinde ise koordinat verisiyle 3B Harita veya sınır dosyalarıyla bir CBS aracı gerekir. Bunun yanı sıra açık veri yayımlanacaksa kişisel veriler mutlaka çıkarılmalı ve tablolar makine tarafından okunabilir CSV biçiminde paylaşılmalıdır. Son olarak her grafiğin altına veri kaynağını ve tarihini yazmak, şeffaflık açısından vazgeçilmez bir alışkanlıktır.

Pano tasarımı için iş zekası dashboard rehberimizden, eğitim hizmetleri verisi için ise eğitim veri analizi rehberimizden yararlanabilirsiniz.

Sık sorulan sorular

Kamu hizmetleri veri analizi için Excel yeterli mi?
İlçe belediyesi veya il müdürlüğü ölçeğindeki çoğu analiz için yeterlidir. Ancak milyonlarca satırlık başvuru veya sensör verisinde veritabanı ve iş zekası araçları daha uygundur.

Bütçe gerçekleşme oranında hangi ödenek kullanılmalı?
Yıl sonu ödeneği kullanılmalıdır; yani başlangıç ödeneğine eklenen ve aktarılan tutarlar dahil edilip düşülen tutarlar çıkarılmalıdır. Başlangıç ödeneğine göre oran ise ayrıca bilgi amaçlı raporlanabilir.

Nüfus verisine nereden ulaşabilirim?
TÜİK’in Adrese Dayalı Nüfus Kayıt Sistemi sonuçları il, ilçe ve mahalle düzeyinde yayımlanır. Bu veriler TÜİK veri portalından tablo olarak indirilebilir. Analizde kullandığınız yılın nüfusunu ve kaynağını raporda belirtmeniz gerekir.

Açık veri yayımlarken nelere dikkat edilmeli?
Kamu hizmetleri veri analizi sonuçları açık veri olarak paylaşılacaksa kişisel veriler ve kişiyi tanımlanabilir kılan ayrıntılar çıkarılmalı, sütun adları açıklanmalı ve veri sözlüğü eklenmelidir. Ayrıca dosyanın güncellenme sıklığı belirtilmelidir.

İlgili rehberler ve hizmetler

• Çevresel veri analizi rehberi
• Eğitim veri analizi rehberi
• İnşaat ve mühendislik projelerinin takibi
• TÜİK veri portalı
• 5018 sayılı Kamu Mali Yönetimi ve Kontrol Kanunu

Veri, modern dünyanın en değerli varlıklarından biri haline gelmiştir ve iş dünyasında, araştırmada ve karar verme süreçlerinde kritik bir rol oynamaktadır. Biz, veri analizi konusundaki tutkulu bir topluluk olarak, veri biliminin ve analizinin gücünü vurguluyoruz. Web sitemiz, işletmelerden akademisyenlere ve veri meraklılarına kadar herkesin veri analizi becerilerini geliştirmesine yardımcı olmayı amaçlayan bir kaynaktır. Misyonumuz, veri analizi sürecini anlaşılır ve erişilebilir hale getirmek, verilerin içinde gizlenen değeri açığa çıkarmak ve daha bilinçli kararlar almalarını desteklemektir.

Web sitemiz, geniş bir veri analizi yelpazesi sunmaktadır. Hangi sektörde olursanız olun veya hangi veri analizi aracını kullanıyorsanız kullanın, ihtiyaçlarınıza uygun hizmetler sunuyoruz. SPSS, R, Python, Excel veya diğer analiz araçlarını kullanarak veri madenciliği, hipotez testleri, regresyon analizi, zaman serisi tahmini, segmentasyon ve daha fazlasını içeren çeşitli analiz türlerini kapsarız. Ayrıca, öğrenmeyi kolaylaştırmak için zengin kaynaklar, öğreticiler ve interaktif araçlar sunuyoruz.