Veresiye Defteri: Müşteri Borçlarını ve Tahsilatları Excel'de Tutun
Yayın: · Son güncelleme:
Kısaca
Bu ücretsiz Excel veresiye defteri şablonunda her veresiye satışı ve ödemeyi bir satır olarak girersiniz; her müşterinin güncel borcu, son ödeme tarihi ve limit aşımı otomatik hesaplanır.
Şablon önizlemesi
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Müşteri | Telefon | Limit | Toplam veresiye | Toplam ödeme | Bakiye | Son ödeme | Durum |
| 2 | Hasan Bey (3. kat) | 0555 000 00 01 | 3.000 | =IF(A2="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A2,Hareketler!$C$2:$C$301,"Veresiye")) | =IF(A2="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A2,Hareketler!$C$2:$C$301,"Ödeme")) | =IF(A2="","",D2-E2) | =IF(A2="","",IF(SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A2)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301))=0,"",SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A2)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301)))) | =IF(A2="","",IF(F2<=0,"Borcu yok",IF(AND(N(C2)>0,F2>C2),"Limit aşıldı","Açık hesap"))) |
| 3 | Fatma Hanım | 0555 000 00 02 | 2.000 | =IF(A3="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A3,Hareketler!$C$2:$C$301,"Veresiye")) | =IF(A3="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A3,Hareketler!$C$2:$C$301,"Ödeme")) | =IF(A3="","",D3-E3) | =IF(A3="","",IF(SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A3)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301))=0,"",SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A3)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301)))) | =IF(A3="","",IF(F3<=0,"Borcu yok",IF(AND(N(C3)>0,F3>C3),"Limit aşıldı","Açık hesap"))) |
| 4 | Oto tamirci Kemal | 0555 000 00 03 | 5.000 | =IF(A4="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A4,Hareketler!$C$2:$C$301,"Veresiye")) | =IF(A4="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A4,Hareketler!$C$2:$C$301,"Ödeme")) | =IF(A4="","",D4-E4) | =IF(A4="","",IF(SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A4)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301))=0,"",SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A4)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301)))) | =IF(A4="","",IF(F4<=0,"Borcu yok",IF(AND(N(C4)>0,F4>C4),"Limit aşıldı","Açık hesap"))) |
| 5 | Selin Öğretmen | 0555 000 00 04 | 1.500 | =IF(A5="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A5,Hareketler!$C$2:$C$301,"Veresiye")) | =IF(A5="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A5,Hareketler!$C$2:$C$301,"Ödeme")) | =IF(A5="","",D5-E5) | =IF(A5="","",IF(SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A5)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301))=0,"",SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A5)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301)))) | =IF(A5="","",IF(F5<=0,"Borcu yok",IF(AND(N(C5)>0,F5>C5),"Limit aşıldı","Açık hesap"))) |
| 6 | Kuaför Derya | 0555 000 00 05 | 1.000 | =IF(A6="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A6,Hareketler!$C$2:$C$301,"Veresiye")) | =IF(A6="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A6,Hareketler!$C$2:$C$301,"Ödeme")) | =IF(A6="","",D6-E6) | =IF(A6="","",IF(SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A6)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301))=0,"",SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A6)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301)))) | =IF(A6="","",IF(F6<=0,"Borcu yok",IF(AND(N(C6)>0,F6>C6),"Limit aşıldı","Açık hesap"))) |
| 7 | =IF(A7="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A7,Hareketler!$C$2:$C$301,"Veresiye")) | =IF(A7="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A7,Hareketler!$C$2:$C$301,"Ödeme")) | =IF(A7="","",D7-E7) | =IF(A7="","",IF(SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A7)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301))=0,"",SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A7)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301)))) | =IF(A7="","",IF(F7<=0,"Borcu yok",IF(AND(N(C7)>0,F7>C7),"Limit aşıldı","Açık hesap"))) | |||
| 8 | =IF(A8="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A8,Hareketler!$C$2:$C$301,"Veresiye")) | =IF(A8="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A8,Hareketler!$C$2:$C$301,"Ödeme")) | =IF(A8="","",D8-E8) | =IF(A8="","",IF(SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A8)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301))=0,"",SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A8)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301)))) | =IF(A8="","",IF(F8<=0,"Borcu yok",IF(AND(N(C8)>0,F8>C8),"Limit aşıldı","Açık hesap"))) | |||
| 9 | =IF(A9="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A9,Hareketler!$C$2:$C$301,"Veresiye")) | =IF(A9="","",SUMIFS(Hareketler!$E$2:$E$301,Hareketler!$B$2:$B$301,A9,Hareketler!$C$2:$C$301,"Ödeme")) | =IF(A9="","",D9-E9) | =IF(A9="","",IF(SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A9)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301))=0,"",SUMPRODUCT(MAX((Hareketler!$B$2:$B$301=A9)*(Hareketler!$C$2:$C$301="Ödeme")*Hareketler!$A$2:$A$301)))) | =IF(A9="","",IF(F9<=0,"Borcu yok",IF(AND(N(C9)>0,F9>C9),"Limit aşıldı","Açık hesap"))) |
Tablodaki satırlar örnek veridir; indirdiğinizde kendi kayıtlarınızla değiştirebilirsiniz.
Şablonda neler var?
Müşterilersayfası
Sütunlar: Müşteri, Telefon, Limit, Toplam veresiye, Toplam ödeme, Bakiye, Son ödeme, Durum
- Excel tablosu (filtreli, otomatik genişleyen)
- Koşullu biçimlendirme
- Grafik
- Dondurulmuş başlık satırı
- Hazır formüller
Hareketlersayfası
Sütunlar: Tarih, Müşteri, İşlem, Açıklama, Tutar, Müşteri bakiyesi
- Excel tablosu (filtreli, otomatik genişleyen)
- Açılır listeli veri doğrulama
- Dondurulmuş başlık satırı
İçindekiler
Veresiye defteri neden Excel'de tutulur?
Kâğıt veresiye defterinde bir müşterinin toplam borcunu bulmak için sayfaları geriye doğru çevirip toplama yaparsınız. Bu şablonda her satış ve ödeme bir satır olarak girilir, her müşterinin borcu kendiliğinden hesaplanır. Bakkal, manav, büfe ya da mahalle esnafı için tasarlandı.
Dosyadaki beş müşteri ve on altı hareket varsayımsaldır. Örnekte toplam alacak 7.610 TL, borçlu müşteri sayısı dört ve müşteri başına ortalama borç 1.902,50 TL'dir.
İki sayfa, iki görev
Müşteriler sayfası her müşterinin kartıdır. Ad, telefon ve isterseniz bir borç limiti girersiniz. Toplam veresiye, toplam ödeme, bakiye, son ödeme tarihi ve durum sütunları formüldür. Sağ üstteki göstergeler toplam alacağı, borçlu müşteri sayısını ve limitini aşan müşteri sayısını verir.
Hareketler sayfası defterin kendisidir. Müşteri veresiye aldığında İşlem sütununda "Veresiye", ödeme yaptığında "Ödeme" seçersiniz. Tutar her zaman pozitif yazılır.
Son sütun o müşterinin o satırdaki güncel borcunu gösterir. Müşteri "geçen ay ne kadar borcum vardı" diye sorduğunda bu sütun cevabı verir.
Formüller ne hesaplar?
Toplam veresiye hücresi ÇOKETOPLA ile müşterinin adına girilmiş "Veresiye" satırlarını toplar; toplam ödeme aynı şekilde "Ödeme" satırlarını toplar. Bakiye bu ikisinin farkıdır. Hareketler sayfasındaki satır bakiyesi de aynı formülü kullanır, yalnız toplama aralığı ilk satırdan o satıra kadar uzanır.
Son ödeme sütunu, o müşterinin ödeme satırları arasından en yeni tarihi TOPLA.ÇARPIM içindeki MAK ile bulur. Hiç ödeme yapmamış müşteride hücre boş kalır. Örnekte Kuaför Derya 1.320 TL borçla hiç ödeme yapmamış görünür.
Limit ve durum uyarıları
Durum sütunu üç değerden birini yazar: Borcu yok, Açık hesap ya da Limit aşıldı. Limit sütunu boş bırakılırsa uyarı çalışmaz. Örnekte Kuaför Derya'ya 1.000 TL limit tanımlanmış, borcu 1.320 TL olduğu için satırı kırmızı görünür. Oto tamirci Kemal'in borcu da 28 Eylül'de 5.270 TL'ye çıkıp limiti geçmişti; 10 Ekim'deki 2.000 TL ödemeden sonra 3.270 TL'ye indi ve uyarı kalktı.
İsim yazımında dikkat
Hareketler sayfasındaki müşteri adı, Müşteriler sayfasındakiyle harfi harfine aynı olmalıdır. "Fatma Hanım" yerine "Fatma hanım" yazmak büyük küçük harf nedeniyle sorun çıkarmaz, ama fazladan bir boşluk ya da farklı bir ek o satırın toplamlara girmemesine yol açar. En güvenli yol Müşteri sütununa Müşteriler listesinden beslenen bir açılır liste eklemektir; nasıl yapılacağını dinamik açılır liste rehberinde bulabilirsiniz.
Aynı isimde iki müşteriniz varsa adın yanına bir ayırt edici ekleyin. Örnekteki "Hasan Bey (3. kat)" bu amaçla yazılmıştır.
Defteri bugün başlatmak
Kâğıt defterinizdeki her müşterinin bugünkü borcunu Hareketler sayfasına bugünün tarihiyle tek bir "Veresiye" satırı olarak girin, açıklamaya "Devir" yazın. Böylece eski hareketleri tek tek aktarmadan başlarsınız. Firmalarla karşılıklı borç alacak takibi gerekiyorsa cari hesap takip tablosu daha uygun bir yapı sunar.
Sıkça Sorulan Sorular
Müşteri borcunun bir kısmını öderse nasıl girerim?
Ödenen tutarı Hareketler sayfasına İşlem sütununda Ödeme seçerek yazarsınız. Bakiye, toplam veresiyeden toplam ödemeyi düştüğü için kısmi ödemeler de doğru hesaplanır.
Telefon numarasının başındaki sıfır neden kaybolmuyor?
Telefon sütunu metin biçimindedir. Bu nedenle 0555 ile başlayan numaralar sayıya çevrilmez ve baştaki sıfır korunur.
Borcu kapanan müşteriyi silmeli miyim?
Silmeyin. Müşteri satırını silerseniz geçmiş hareketleri toplamlarda görünmez olur. Durum sütununda Borcu yok yazan müşteriyi filtreyle gizleyebilirsiniz.