Excel’de Sigorta Veri Analizi ve Raporlama

Kısa cevap: Excel’de sigorta veri analizi; poliçe, hasar ve tahsilat kayıtlarını düzenli tablolara aktarıp birkaç temel göstergeyi formülle hesaplamaktır: poliçe süresi ve kalan gün, kazanılmış ve kazanılmamış prim, branş ve acente bazında hasar/prim oranı, tahsilat ve yenileme oranı, müşteri risk skoru ve hasar dosyası kapanış süresi. Bu rehberde her hesabı Türkçe Excel formülleri ve kurgusal örneklerle gösteriyor, sonuçları PivotTable ve gösterge paneliyle nasıl raporlayacağınızı anlatıyoruz.

Örneklerdeki prim ve hasar tutarları yalnızca hesaplamayı göstermek için seçilmiştir; piyasa verisi değildir. Resmî muhasebe ve karşılık hesaplarında şirketinizin uyguladığı mevzuat ve aktüerya yöntemleri esas alınmalıdır.

Excel’de sigorta poliçe takip tablosu nasıl kurulur?

Acente veya şirket ölçeğinden bağımsız olarak verileri üç ayrı tabloda tutmak, hem formülleri hem de raporları sadeleştirir:

Tablo	Sütunlar	Amaç
tblPolice	Poliçe No, Müşteri No, Branş (Kasko, Trafik, Konut, Sağlık, Hayat, İşyeri), Başlangıç, Bitiş, Prim, Acente / Satış Kanalı, Bölge, Durum (Aktif, İptal, Yenilendi)	Üretim ve portföy
tblTahsilat	Poliçe No, Taksit No, Vade Tarihi, Tutar, Ödeme Tarihi	Tahsilat takibi
tblHasar	Dosya No, Poliçe No, Branş, Hasar Tarihi, İhbar Tarihi, Kapanış Tarihi, Ödenen, Muallak (beklenen ödeme), Statü	Hasar ve tazminat takibi

Tabloları Ctrl+T ile biçimlendirip ad verin; böylece yeni satırlar formüllere ve PivotTable’lara kendiliğinden dahil olur. Branş, acente ve durum sütunlarında Veri > Veri Doğrulama > Liste kullanmak yazım farklılıklarını (“Kasko” / “kasko “) önler. Müşteri yaşı, cinsiyeti, bölgesi gibi bilgileri ayrı bir müşteri tablosunda tutup =ÇAPRAZARA([@[Müşteri No]];tblMusteri[Müşteri No];tblMusteri[Yaş]) (XLOOKUP) ile çağırmak veri tekrarını azaltır. Bu dosyalar kişisel veri içerdiği için erişim yetkisini sınırlamak ve gereksiz kişisel bilgiyi tabloya almamak, KVKK açısından da önemlidir.

Poliçe süresi, kalan gün ve kazanılmış prim nasıl hesaplanır?

Kurgusal örnek: 12.000 TL primli bir kasko poliçesi 01.03.2026’da başlıyor, 01.03.2027’de bitiyor. Rapor tarihi 31.12.2026 (B1 hücresi).

Gösterge	Formül (Türkçe Excel)	Sonuç
Poliçe süresi (gün)	=[@Bitiş]-[@Başlangıç]	365
Rapor tarihine kadar geçen gün	=MAK(0;MİN($B$1;[@Bitiş])-[@Başlangıç])	305
Kazanılmış prim	=[@Prim]*[@[Geçen Gün]]/[@[Süre]]	≈ 10.027 TL
Kazanılmamış prim	=[@Prim]-[@[Kazanılmış Prim]]	≈ 1.973 TL
Bitişe kalan gün	=[@Bitiş]-BUGÜN()	Bugüne göre değişir

Tarih farkı için iki tarihi doğrudan çıkarmak yeterlidir; hücreyi Sayı biçimine çevirin. Sık yapılan bir hata GÜN() fonksiyonunu iki tarih arasındaki farkı bulmak için kullanmaktır; GÜN yalnızca bir tarihin ayın kaçıncı günü olduğunu döndürür. Fark için çıkarma işlemi veya =GÜNSAY(bitiş;başlangıç) (DAYS) kullanılır.

Kazanılmış prim, yazılan primin rapor dönemine düşen payıdır ve hasar/prim oranının paydasında bu değer kullanılmalıdır. Kazanılmamış primler karşılığı gün esasına göre hesaplanır; başlangıç ve bitiş gününün nasıl sayılacağı, iptal ve zeyil (ek belge) iadelerinin nasıl düşüleceği gibi ayrıntılar yürürlükteki düzenlemeye ve şirket uygulamasına göre değişebileceği için muhasebe kaydında bu tablo bir kontrol aracı olarak kullanılmalıdır.

Hasar/prim oranı ve diğer sigorta KPI’ları Excel’de nasıl hesaplanır?

Branş bazında hasar/prim (tazminat) oranı, sigorta raporlamasının merkezindeki göstergedir. Kurgusal bir acente portföyü için dönem özeti:

Branş	Kazanılmış prim (TL)	Ödenen + muallak hasar (TL)	Hasar/prim oranı
Kasko	1.200.000	840.000	%70,0
Trafik	900.000	765.000	%85,0
Konut	400.000	88.000	%22,0
Toplam	2.500.000	1.693.000	%67,7

Hasar tarafı: =ÇOKETOPLA(tblHasar[Ödenen];tblHasar[Branş];A2)+ÇOKETOPLA(tblHasar[Muallak];tblHasar[Branş];A2) (SUMIFS). Oran: =EĞERHATA(C2/B2;""). Toplam satırındaki oran, branş oranlarının ortalaması değil, toplam hasarın toplam prime bölümüdür; branş oranlarının basit ortalaması burada %59 gibi yanıltıcı bir sonuç verir.

Portföy takibinde sık kullanılan diğer göstergeler:

KPI	Tanım	Örnek formül
Tahsilat oranı	Tahsil edilen prim / vadesi gelen prim	=ÇOKETOPLA(tblTahsilat[Tutar];tblTahsilat[Ödeme Tarihi];"<>")/ÇOKETOPLA(tblTahsilat[Tutar];tblTahsilat[Vade Tarihi];"<="&$B$1)
Yenileme oranı	Yenilenen poliçe / dönemde vadesi dolan poliçe	=ÇOKEĞERSAY(tblPolice[Durum];"Yenilendi";tblPolice[Bitiş];"<="&$B$1)/ÇOKEĞERSAY(tblPolice[Bitiş];"<="&$B$1)
Hasar sıklığı	Hasar dosyası adedi / poliçe adedi	=EĞERSAY(tblHasar[Branş];A2)/EĞERSAY(tblPolice[Branş];A2)
Ortalama hasar tutarı	Toplam hasar / hasar adedi	=ÇOKEĞERORTALAMA(tblHasar[Ödenen];tblHasar[Branş];A2)
Ortalama prim	Toplam prim / poliçe adedi	=ÇOKEĞERORTALAMA(tblPolice[Prim];tblPolice[Branş];A2)
İptal oranı	İptal edilen / üretilen poliçe	=EĞERSAY(tblPolice[Durum];"İptal")/SATIRSAY(tblPolice)

Tahsilat formülünde örnek olarak ödeme tarihi dolu olan tüm taksitler sayılmıştır; vadesi henüz gelmemiş ama erken ödenmiş taksitleri ayırmak isterseniz pay tarafına da vade koşulunu ekleyin. Hasar sıklığını poliçe adedi yerine risk süresine (poliçe-yıl) bölmek, dönem içinde başlayan poliçeler çoksa daha doğru sonuç verir.

Süresi dolan poliçeler için yenileme hatırlatması nasıl yapılır?

Poliçe tablosuna iki hesaplanmış sütun eklemek yeterlidir:

• Kalan Gün: =[@Bitiş]-BUGÜN()
• Yenileme Durumu: =EĞER([@Durum]="Yenilendi";"Yenilendi";EĞER([@[Kalan Gün]]<0;"Süresi doldu";EĞER([@[Kalan Gün]]<=30;"Hatırlat";"Aktif")))

Giriş > Koşullu Biçimlendirme ile “Hatırlat” satırlarını sarı, “Süresi doldu” satırlarını kırmızı yapın. Microsoft 365’te önümüzdeki 30 gün içinde bitecek poliçeleri ayrı bir sayfada otomatik listelemek için:

=SIRALA(FİLTRE(tblPolice[[Poliçe No]:[Bitiş]];(tblPolice[Bitiş]>=BUGÜN())*(tblPolice[Bitiş]<=BUGÜN()+30);"Yaklaşan yenileme yok");5;1)

Buradaki 5, seçilen aralıkta Bitiş sütununun sırasıdır; tablo yapınıza göre değiştirin. Eski Excel sürümlerinde aynı listeyi Kalan Gün sütununa filtre uygulayarak veya PivotTable’da “Yenileme Durumu” alanını filtreye koyarak alabilirsiniz. Bu liste, ayın başında acente ekibinin arama planına doğrudan dönüştürülebilir.

Poliçe ve hasar raporları PivotTable ile nasıl hazırlanır?

Veriler tablo hâlinde olduğunda en sık istenen raporlar birkaç sürükle-bırak ile hazırlanır:

Rapor	Satırlar	Sütunlar	Değerler	Filtre / dilimleyici
Branş bazlı prim üretimi	Branş	Başlangıç (aylara gruplanmış)	Toplam Prim	Acente
Acente performansı	Acente	–	Toplam Prim, Poliçe Sayısı, Ortalama Prim	Branş, Yıl
Yıllık karşılaştırma	Yıl	Branş	Toplam Prim, Toplam Hasar	Bölge
Bölge bazlı hasar	Bölge	–	Toplam Ödenen, Dosya Sayısı	Branş
Yaş grubuna göre hasar	Yaş (Gruplandır: 10’ar yıl)	Cinsiyet	Ortalama Hasar	Branş

Prim ve hasar aynı PivotTable’da gösterilecekse iki tabloyu Poliçe No üzerinden Veri Modeli‘nde ilişkilendirin. Grafik seçiminde aylık üretim ve hasar ödemeleri için çizgi grafik, acente ve branş karşılaştırması için sütun grafik en okunaklı seçeneklerdir. Pasta grafiği yalnızca az sayıda branşın payını göstermek için kullanın; prim artışı ile hasar oranını aynı grafikte izlemek için ikincil eksenli birleşik grafik daha uygundur. Dilimleyiciler (Ekle > Dilimleyici) ile branş ve acente seçimi tek tıklamaya iner.

Müşteri risk skoru ve segmentasyon Excel’de nasıl yapılır?

Basit bir puanlama modeliyle müşterileri risk gruplarına ayırmak, yenileme ve kampanya önceliklerini belirlemekte işe yarar. Kurgusal bir kasko örneğinde üç faktör 1-5 arası puanlanır ve ağırlıklandırılır:

Faktör	Puanlama örneği	Ağırlık
Son 3 yıldaki hasar sayısı	0 → 1, 1 → 3, 2 ve üzeri → 5	0,5
Araç yaşı	0-3 → 1, 4-10 → 3, 11+ → 5	0,3
Sürücü yaşı	26-60 → 1, 61+ → 3, 18-25 → 5	0,2

Puanları bir eşik tablosundan yaklaşık eşleştirmeyle alın: =DÜŞEYARA([@[Araç Yaşı]];tblAracPuan;2;DOĞRU) (eşik tablosu küçükten büyüğe sıralı olmalıdır). Toplam skor: =TOPLA.ÇARPIM(D2:F2;$D$1:$F$1) (SUMPRODUCT; birinci satırda ağırlıklar). Segment: =EĞER(G2>=3,5;"Yüksek risk";EĞER(G2>=2;"Orta risk";"Düşük risk")).

Ağırlıklar ve eşikler bu örnekte varsayımdır. Gerçek bir modelde ağırlıkların geçmiş hasar verisiyle sınanması gerekir; örneğin “hasar oldu mu (1/0)” bağımlı değişkeniyle lojistik regresyon kurmak, hangi faktörün hasar olasılığını gerçekten etkilediğini gösterir. Bu tür bir puanlama fiyatlama yerine geçmez; portföyü izlemek ve önceliklendirmek için kullanılmalıdır.

Hasar dosyaları ve tazminat süreleri nasıl izlenir?

Hasar tablosunda üç hesaplanmış sütun, operasyonun en önemli sorularını yanıtlar:

• İhbar gecikmesi: =[@[İhbar Tarihi]]-[@[Hasar Tarihi]]
• Süre (açık dosyada dosya yaşı, kapalı dosyada kapanış süresi): =EĞER([@[Kapanış Tarihi]]="";BUGÜN()-[@[İhbar Tarihi]];[@[Kapanış Tarihi]]-[@[İhbar Tarihi]])
• Statü: =EĞER([@[Kapanış Tarihi]]<>"";"Kapandı";EĞER([@Ödenen]>0;"Kısmi ödeme";"Bekliyor"))

Branş bazında ortalama kapanış süresi: =ÇOKEĞERORTALAMA(tblHasar[Süre];tblHasar[Branş];A2;tblHasar[Statü];"Kapandı"). Açık dosyaları 0-30, 31-90 ve 90+ gün olarak yaşlandırmak (PivotTable’da Süre alanını gruplandırarak), uzun süredir bekleyen dosyaları görünür kılar. Ortalama kapanma süresi müşteri deneyimiyle doğrudan ilişkilidir; ancak birkaç çok uzun dava dosyası ortalamayı bozabileceği için ortanca (ORTANCA) değeri de raporlamak faydalıdır.

Sigorta dashboard’u ve Power Query otomasyonu nasıl kurulur?

Tek sayfalık bir yönetim panelinde şu öğeler çoğu ihtiyacı karşılar: dönem primi ve geçen yıla göre değişim, aktif poliçe sayısı, toplam hasar/prim oranı, tahsilat oranı, önümüzdeki 30 günde yenilenecek poliçe sayısı, branş bazlı prim grafiği, acente karşılaştırması ve açık hasar dosyalarının yaş dağılımı. KPI kartlarını formüllerle, grafikleri PivotChart ile oluşturup hepsini aynı dilimleyicilere bağlayın; hedef dışı değerleri koşullu biçimlendirmeyle işaretleyin.

Poliçe üretim sisteminden, CRM’den veya hasar yazılımından düzenli olarak dışa aktarılan Excel/CSV dosyalarını Veri > Veri Al > Klasörden ile Power Query’de birleştirebilirsiniz. Sorguda tarih ve tutar sütunlarının veri türlerini ayarlayın, poliçe numaralarını metin olarak tutun ve tekrar eden kayıtları Yinelenenleri Kaldır ile temizleyin. Yeni dosya klasöre eklendiğinde Tümünü Yenile ile tüm tablolar, PivotTable’lar ve panel güncellenir; aynı raporu her ay elle hazırlamak gerekmez.

Sigorta verisi analizinde en sık yapılan Excel hataları nelerdir?

• Yazılan prim ile kazanılmış primi karıştırmak: Dönem içinde yazılan primin tamamını paydaya koymak, özellikle hızlı büyüyen portföylerde hasar/prim oranını olduğundan düşük gösterir.
• Muallak hasarları yok saymak: Yalnızca ödenen hasarla hesaplanan oran, açık dosyalar kapandıkça yükselir.
• Oranların ortalamasını almak: Toplam oran, toplam hasarın toplam prime bölümüyle hesaplanmalıdır.
• İptal ve iade zeyillerini düşmemek: Üretim ve tahsilat raporlarında net prim kullanılmalıdır.
• Tarihleri metin olarak saklamak ve GÜN() ile fark almak: Tarih sütunlarının gerçekten tarih türünde olduğunu kontrol edin, farkı çıkarma ile hesaplayın.

Sık sorulan sorular

Excel'de sigorta poliçe takip tablosu nasıl yapılır?

Poliçe No, Müşteri, Branş, Başlangıç, Bitiş, Prim, Acente ve Durum sütunlarıyla bir tablo oluşturup Ctrl+T ile tabloya çevirin. Kalan gün için =[@Bitiş]-BUGÜN() sütunu ekleyip koşullu biçimlendirmeyle 30 günden az kalan poliçeleri işaretleyin. Hasar ve tahsilat kayıtlarını ayrı tablolarda Poliçe No ile ilişkilendirin.

Ücretsiz sigorta takip programı yerine Excel kullanılabilir mi?

Küçük ve orta ölçekli portföylerde poliçe, yenileme ve hasar takibi Excel ile rahatlıkla yapılabilir. Çok kullanıcılı eş zamanlı çalışma, yetkilendirme ve şirket sistemleriyle doğrudan entegrasyon gerekiyorsa bir acente yazılımı veya veritabanı daha uygundur; Excel bu durumda raporlama katmanı olarak kullanılabilir.

Hasar/prim oranı nasıl hesaplanır?

Temel hesap, dönemdeki ödenen ve muallak hasar toplamının aynı dönemin kazanılmış primine bölünmesidir. Excel’de ÇOKETOPLA ile branş bazında hasar ve prim toplamları alınır, oran =hasar/prim olarak hesaplanır. Toplam oran, branş oranlarının ortalaması değil toplamların oranıdır.

Kazanılmamış prim Excel'de nasıl hesaplanır?

Gün esasına göre basit hesapta kazanılmış prim, prim × rapor tarihine kadar geçen gün / poliçe süresi formülüyle bulunur; kazanılmamış prim ise primden kazanılmış prim çıkarılarak hesaplanır. Resmî karşılık hesabında yürürlükteki düzenleme ve şirket uygulaması esas alınmalıdır.

Poliçe bitiş tarihine göre otomatik hatırlatma listesi nasıl oluşturulur?

Microsoft 365’te FİLTRE fonksiyonuyla bitiş tarihi bugün ile bugün+30 arasında olan poliçeleri ayrı bir sayfada listeleyebilir, SIRALA ile bitiş tarihine göre dizebilirsiniz. Eski sürümlerde Kalan Gün sütununa filtre uygulamak veya PivotTable filtresi kullanmak aynı sonucu verir.

İlgili rehberler ve hizmetler

• Excel’de risk değerlendirmesi ve analiz yöntemleri
• Excel’de finansal hizmetler veri analizi
• Excel’de müşteri memnuniyeti analizi
• Excel’de gösterge paneli (dashboard) oluşturma
• Excel’de PivotTable ile veri analizinin temelleri
• Excel’de regresyon analizi nasıl yapılır?
• SPSS analizi yaptırma
• İstatistik ödev yaptırma

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.