İLERİ EXCEL DENETİM VE MUTABAKAT
Banka ekstresinde beş dekont var, muhasebe kayıtlarında dört — hangisi eksik? Mutabakatın bütün işi bu sorudur: iki listeyi karşılaştırıp eşleşmeyeni bulmak. Gözle satır satır kıyaslamak hem yavaş hem güvenilmezdir; Excel'de aynı iş tek EĞERSAY formülü ve bir koşullu biçimlendirme kuralıyla kurulur — eksik kayıt kendini kırmızıya boyar. Bu sayfadaki örnek dosyada mekanizma kurulu; formülü satır satır inceleyeceğiz.
Çalışma dosyasını indir (.xlsx) — banka-muhasebe mutabakat düzeni, kontrol formülü kurulu1. Mutabakat Düzeni: İki Liste Yan Yana
Düzen tek sayfada kurulur: solda banka listesi (5 dekont, D-101'den D-105'e), sağda muhasebe listesi (4 kayıt) ve banka tarafına eklenmiş bir kontrol sütunu: "Muhasebede?". Karşılaştırmanın anahtarı, iki listede de ortak olan kimliktir — burada dekont numarası. Ortak anahtar yoksa mutabakat kurulamaz; tarih+tutar birleşimi gibi türetilmiş bir anahtar üretmek ilk iş olur.
2. Kontrol Formülü: EĞERSAY
Kontrol sütunundaki formül tek satırdır ve mutabakatın tamamını taşır:
=EĞERSAY($E$2:$E$5;A2)
Okunuşu: "A2'deki dekont numarası, muhasebe listesinde ($E$2:$E$5) kaç kez geçiyor?" Cevap 1 ise kayıt iki tarafta da var; 0 ise banka hareketi muhasebeye işlenmemiş demektir. Muhasebe aralığının dolar işaretleriyle sabitlenmesi ($E$2:$E$5), formül aşağı kopyalanırken aralığın kaymamasını sağlar. Aynı formülün simetriği muhasebe tarafına da yazılır (=EĞERSAY($A$2:$A$6;E2)) — böylece "muhasebede olup bankada olmayan" ters yön de yakalanır; gerçek mutabakat her zaman çift yönlüdür.
3. Farkı Görünür Kılmak
Sayı sütunu doğruyu söyler ama göze çarpmaz; beş yüz satırlık listede 0'ları aramak yine göz işidir. Koşullu biçimlendirme bunu bitirir: banka aralığına =$C2=0 formüllü kural, eşleşmeyen kaydın tüm satırını boyar. Örnekte sonuç anında okunur — D-103, 7.850 TL, muhasebede yok; dosyayı açan kişinin gözü doğrudan kırmızı satıra düşer. Formül tabanlı kuralın incelikleri (dolar işaretinin konumu dahil) ileri koşullu biçimlendirme sayfasında ayrıntılı anlatılıyor.
4. Gerçek Dünyada Mutabakat Pratikleri
- Anahtarı temizleyin: eşleşmeme çoğu zaman gerçek fark değil yazım farkıdır — baştaki/sondaki boşluklar (KIRP), büyük-küçük ayrımı, "D-103" ile "D103". Karşılaştırma öncesi her iki anahtar sütununa aynı temizlik uygulanır.
- Tutarı ikinci kontrol yapın: numara eşleşip tutar farklıysa bu da bir mutabakat bulgusudur;
=EĞER(DÜŞEYARA(A2;$E$2:$F$5;2;YANLIŞ)<>B2;"TUTAR FARKI";"")kalıbı numarası eşleşen kaydın tutarını da doğrular. - Mükerrer kaydı unutmayın: EĞERSAY sonucu 2 ve üzeriyse kayıt çift girilmiştir — 0 kadar önemli bir bulgudur; kuralı "1 dışındaki her değer" olarak boyamak ikisini birden yakalar.
- Dönem dosyası saklayın: mutabakat bittiğinde dosya o dönemin kanıtıdır; üzerine yazmak yerine tarihli kopya arşivlenir.
Bulunan her fark bir karar ister; denetim izi için farkları sınıflandırıp tabloya bir "Açıklama" sütunuyla işlemek standarttır:
| Bulgu sınıfı | Tipik neden | Aksiyon |
|---|---|---|
| Zamanlama farkı | Kayıt yolda — dekont kesildi, muhasebe henüz işlemedi | Sonraki dönemde kapanır; not düş |
| Eksik kayıt | Hareket muhasebeye hiç girmemiş | Kaydı işle, kaynağını sorgula |
| Hatalı kayıt | Yanlış tutar veya numara | Düzelt; tekrarlıyorsa giriş sürecine bak |
| Mükerrer kayıt | Aynı hareket iki kez girilmiş | Birini iptal et; anahtar disiplinini sıkılaştır |
Özetle
Excel'de mutabakatın çekirdeği üç parçadır: ortak anahtarla yan yana iki liste, her iki yöne yazılmış EĞERSAY kontrolü ve 0'ları boyayan koşullu biçim kuralı. Bu kurulum bir kez yapılır, her dönem yalnızca veri yenilenir — beş satırda da beş bin satırda da fark kendini gösterir. İndirdiğiniz dosyada D-103'ü muhasebe listesine ekleyin ve kırmızının kaybolmasını izleyin; mekanizmayı kavramanın en kısa yolu budur. Bu tür kontrol düzenlerini raporlama zinciriyle birlikte kurmak için İleri Excel eğitimi doğru adrestir.
Sıkça Sorulan Sorular
İki listede ortak kimlik numarası yoksa nasıl eşleştiririm?
Türetilmiş anahtar kurulur: iki tarafta da var olan alanlar birleştirilir — örneğin =A2&"|"&B2 ile tarih+tutar anahtarı. Birleşim ne kadar ayırt ediciyse eşleşme o kadar güvenilir olur; aynı gün aynı tutarlı iki işlem varsa anahtara saat veya açıklama parçası eklemek gerekir.
EĞERSAY yerine DÜŞEYARA kullanamaz mıyım?
Kullanılır ama işaretleri farklıdır: DÜŞEYARA eşleşmeyen kayıtta #YOK hatası döndürür, EĞERSAY temiz bir 0 verir — koşullu biçim ve filtre için 0 ile çalışmak daha rahattır. DÜŞEYARA'nın asıl yeri ikinci aşamadır: numarası eşleşen kaydın tutarını karşı listeden çekip kıyaslamak.
Binlerce satırlık listede bu yöntem yavaşlar mı?
EĞERSAY on binlerce satırda rahat çalışır; yavaşlama genellikle tüm sütun referansından (E:E) gelir — aralığı gerçek veriyle sınırlamak veya listeleri Ctrl+T tablosuna çevirip yapısal başvuru kullanmak yeter. Yüz binlerce satırlık mutabakatlarda Power Query birleştirmesi daha uygun araçtır.
Eşleşmeyen kaydı bulduktan sonra ne yapılır?
Bulgu üç sınıftan biridir: zamanlama farkı (kayıt yolda, sonraki dönem düşer), eksik kayıt (muhasebeye işlenmemiş — işlenir) veya hatalı kayıt (yanlış tutar/numara — düzeltilir). Mutabakat dosyasına bir 'Açıklama' sütunu ekleyip her farkın kararını yazmak, denetim izini tamamlar.
Aynı dekont numarası iki kez geçerse formül ne gösterir?
EĞERSAY 2 döndürür — mükerrer kayıt işareti. Koşullu biçim kuralını '0'a eşit' yerine '1'e eşit değil' biçiminde kurarsanız hem eksikler hem mükerrerler boyanır. Mükerrer bulgusunda hangi kaydın gerçek olduğuna tutar ve tarih detayıyla karar verilir; ikisi de gerçekse anahtar yeterince ayırt edici değildir.
