KDV Ayrıştırma İki Oranlı Matrah Çözme

İki Farklı KDV Oranı İçeren Toplamlardan Matrahı Excel ile Ayrıştırın​

Bir faturada, bir satış raporunda ya da bir dönem toplamında iki farklı KDV oranı bir arada bulunduğunda, elde çoğu zaman yalnızca üç tutar olur: toplam matrah, toplam KDV ve genel toplam. Hangi tutarın birinci orana, hangi tutarın ikinci orana tabi olduğu ise ayrıca görünmez. Muhasebe kaydı yapılırken, KDV beyannamesine veri hazırlanırken ya da bir mutabakat kontrol edilirken bu ayrımın yapılması gerekir.
Bu Excel dosyası, toplam matrah ve toplam KDV tutarından yola çıkarak matrahı iki orana göre ayrıştırır. Her oran için matrahı ve KDV'yi ayrı ayrı hesaplar, ardından sonucun doğruluğunu bir sağlama sütunuyla kontrol eder. Dosyadaki bütün hesap tek bir dinamik formülle yapılır; veri girilen satır sayısı arttıkça sonuçlar kendiliğinden genişler. Formülü aşağı doğru kopyalamaya, satır sayısına göre aralık güncellemeye veya makro çalıştırmaya gerek yoktur.

Dosya Ne İşe Yarar​

İki farklı oranın bulunduğu bir toplamda bilinmeyen iki tutar vardır: birinci orana tabi matrah ve ikinci orana tabi matrah. Bu iki tutarın toplamı toplam matrahı verir. Her birinin kendi oranıyla çarpılmasıyla bulunan KDV tutarlarının toplamı da toplam KDV'yi verir. Ortaya iki bilinmeyenli ve iki denklemli bir sistem çıkar. Dosya bu sistemi her satır için ayrı ayrı çözer.
Örneğin toplam matrahı 2.512,35 TL, toplam KDV'si 126,36 TL olan ve yüzde 1 ile yüzde 8 oranlarını içeren bir kayıtta dosya; yüzde 1 oranlı matrahı 1.066,11 TL, yüzde 8 oranlı matrahı 1.446,24 TL olarak bulur. Birinci orana düşen KDV 10,6611 TL, ikinci orana düşen KDV 115,6992 TL olarak hesaplanır ve sağlama sütununda 0 sonucu görülür. Elle deneme yanılma yapmak yerine, aynı işlem yüzlerce satır için aynı anda gerçekleştirilir.

Bu Hesaba Hangi Durumlarda İhtiyaç Duyulur​

Matrahın oranlara bölünmesi, kaynağı yalnızca toplam tutar veren her kayıtta gündeme gelir. Başlıca örnekler şunlardır:

• Farklı oranlı ürünlerin birlikte satıldığı işletmelerde, gün sonu ya da dönem sonu raporlarından yalnızca toplam tutarların elde edildiği durumlar.
• Tedarikçiden gelen özet listelerde fatura bazında toplam matrah ve toplam KDV bulunduğu, ancak oran kırılımının yer almadığı durumlar.
• Eski dönem kayıtlarının yeniden düzenlendiği, oran bazında ayrımın geriye dönük çıkarılması gereken durumlar.
• Muhasebe programına aktarılacak verinin oran bazında ayrı satırlar hâlinde hazırlanması gereken durumlar.
• Bir tutarın hangi orandan hesaplandığının kontrol edilmek istendiği mutabakat çalışmaları.

Bu durumların ortak noktası, kayıtta en fazla iki farklı oranın bulunması ve toplam matrah ile toplam KDV'nin bilinmesidir. Bu iki bilgi bulunduğunda ayrıştırma tek ve kesin bir sonuç verir.

KDV Oranları Hakkında Kısa Bilgi​

Türkiye'de 10 Temmuz 2023'ten bu yana uygulanan KDV oranları yüzde 1, yüzde 10 ve yüzde 20'dir. Bu tarihten önce indirimli oran yüzde 8, genel oran yüzde 18 olarak uygulanıyordu. Dosyadaki örnek verilerde yüzde 1 ve yüzde 8 oranları kullanılmıştır. Oranlar her satırda D ve E sütunlarına ayrıca girildiği için güncel oranlarla, eski dönem oranlarıyla ya da birbirinden farklı herhangi iki oranla çalışmak mümkündür. Örneğin yüzde 10 ve yüzde 20 içeren bir kayıt için D sütununa 10, E sütununa 20 yazılması yeterlidir.

Sayfa Sayfa İçerik​

Çalışma kitabında aynı yapıda iki sayfa bulunur: Sheet1 ve Sheet2. Her iki sayfada da birinci satırda dosya başlığı, ikinci satırda sütun başlıkları yer alır; veriler üçüncü satırdan itibaren girilir. Sayfaların her birinde birbirinden farklı yüzer satırlık örnek veri bulunur. İki sayfa, örneğin farklı dönemlerin, farklı şubelerin ya da farklı tedarikçilerin verilerini ayrı ayrı tutmak için kullanılabilir. Her sayfadaki formül yalnızca kendi sayfasındaki veriyi kullanır; sayfalar birbirini etkilemez.
Sütunların görevi şöyledir:

• A - MATRAH: İki orana ait matrahların toplamı.
• B - KDV: İki orana ait KDV tutarlarının toplamı.
• C - GENEL TOPLAM: Matrah ile KDV'nin toplamı; sağlama için kullanılır.
• D - 1.ORAN: Birinci KDV oranı, yüzde puanı olarak (örneğin 1).
• E - 2.ORAN: İkinci KDV oranı, yüzde puanı olarak (örneğin 8).
• F - 1.MATRAH: Birinci orana tabi matrah (hesaplanır).
• G - 1.KDV: Birinci orana düşen KDV (hesaplanır).
• H - 2.MATRAH: İkinci orana tabi matrah (hesaplanır).
• I - 2.KDV: İkinci orana düşen KDV (hesaplanır).
• J - SAĞLAMA (0 OLMALI): Genel toplamın, bulunan tutarlarla tutup tutmadığını gösterir (hesaplanır).

A'dan E'ye kadar olan sütunlar kullanıcı tarafından doldurulur; F'den J'ye kadar olan sütunlar formül tarafından üretilir. Girdi ve sonuç sütunları farklı renklerle ayrılmıştır. F3 hücresinde ayrıca formülün mantığını kısaca açıklayan bir hücre notu bulunur; formülün nasıl çalıştığı dosya içinden de görülebilir.

Hesabın Matematiği​

Toplam matrah M, toplam KDV K, birinci oran a ve ikinci oran b olarak gösterilirse, bilinmeyen iki matrah x ve y için şu iki eşitlik yazılır:

• x + y = M
• a × x + b × y = K

Birinci eşitlikten y = M − x elde edilir ve ikinci eşitlikte yerine konur: a × x + b × (M − x) = K. Bu ifade düzenlendiğinde x = (K − M × b) ÷ (a − b) sonucuna ulaşılır. İkinci matrah ise y = M − x olarak bulunur. Formül bu eşitlikleri doğrudan kullanır; tahmin, döngü veya hedef ara yöntemi gerekmez.

Kullanılan Formüller ve Hesaplama Mantığı​

F3 hücresindeki tek formül, Microsoft 365 Excel'in dinamik dizi işlevlerini birlikte kullanır. Kullanılan işlevlerin görevi şöyledir:

• TRIMRANGE: A:E aralığındaki dolu alanı otomatik olarak belirler. Boş satırlar hesaba katılmaz ve veri alanı için sabit bir son satır yazılmaz.
• DROP: Bulunan alanın ilk iki satırını, yani dosya başlığını ve sütun başlıklarını dışarıda bırakır.
• INDEX: Alanın istenen sütununu döndürür.
• LAMBDA: INDEX ve DROP adımlarını küçük bir yardımcı işlev hâline getirir; böylece aynı ifade her sütun için tekrar yazılmaz.
• LET: Matrah, iki oran ve iki matrah gibi ara sonuçlara ad verir. Formül hem daha okunaklı olur hem de aynı hesap birden fazla kez yapılmaz.
• ROUND: Birinci matrahı ve sağlama sonucunu iki ondalığa yuvarlar.
• HSTACK: Beş sonuç sütununu yan yana dizer ve bütün satırlar için tek seferde F:J aralığına yayar.

Formül adım adım şu sırayla çalışır:

1. Veri alanı bulunur ve başlık satırları çıkarılır.
2. D ve E sütunlarındaki oranlar yüzde işaretiyle orana çevrilir; 1 değeri 0,01, 8 değeri 0,08 olarak hesaba girer.
3. Birinci matrah, (Toplam KDV − Toplam Matrah × 2. Oran) ÷ (1. Oran − 2. Oran) eşitliğiyle bulunur ve kuruş hassasiyetine yuvarlanır.
4. İkinci matrah, toplam matrahtan birinci matrah çıkarılarak bulunur.
5. Her matrah kendi oranıyla çarpılarak KDV tutarları hesaplanır. KDV tutarları ayrıca yuvarlanmaz; böylece ayrıştırmanın hassasiyeti görülebilir. Hücrelerde iki ondalık gösterilir.
6. Sağlama sütununda genel toplamdan matrah ve iki KDV tutarı düşülür, sonuç iki ondalığa yuvarlanır.

Sağlama sonucu 0 olduğunda mevcut sayı biçimi nedeniyle hücrede çizgi işareti görünür. Bu, girilen genel toplamın bulunan tutarlarla uyumlu olduğunu gösterir.

Örnek Satırın Adım Adım Çözümü​

Sheet1 sayfasının ilk veri satırında toplam matrah 2.512,35 TL, toplam KDV 126,36 TL, genel toplam 2.638,71 TL, oranlar ise 1 ve 8'dir. Formül bu satırı şu sırayla çözer:

1. Toplam matrah ikinci oranla çarpılır: 2.512,35 × 0,08 = 200,988.
2. Bu tutar toplam KDV'den çıkarılır: 126,36 − 200,988 = −74,628.
3. Sonuç iki oranın farkına bölünür: −74,628 ÷ (0,01 − 0,08) = 1.066,11. Bu, yüzde 1 oranlı matrahtır.
4. Yüzde 8 oranlı matrah kalan tutardır: 2.512,35 − 1.066,11 = 1.446,24.
5. KDV tutarları 1.066,11 × 0,01 = 10,6611 ve 1.446,24 × 0,08 = 115,6992 olarak bulunur.
6. Sağlama: 2.638,71 − 2.512,35 − 10,6611 − 115,6992 işleminin iki ondalığa yuvarlanmış sonucu 0'dır.

Öne Çıkan Özellikler​

• Tek formül: Bütün sonuçlar F3 hücresindeki tek bir formülden gelir; satır satır formül kopyalanmaz.
• Kendiliğinden genişleyen alan: Yeni satır eklendiğinde sonuçlar otomatik olarak uzar, boş satırlar hesaba katılmaz.
• Satır bazında oran: Oranlar her satırda ayrı girildiği için farklı satırlarda farklı oran çiftleri kullanılabilir.
• Kontrol sütunu: Her satırın doğruluğu sağlama sütunuyla anında görülür.
• Kuruş hassasiyeti: Matrahlar iki ondalığa yuvarlanarak belge tutarlarıyla uyumlu sonuç verir.
• İki ayrı çalışma sayfası: Farklı veri grupları aynı dosyada ayrı sayfalarda tutulabilir.
• Makrosuz yapı: Dosyada makro bulunmaz; yalnızca formülle çalışır, açılırken makro izni istenmez.

Nasıl Kullanılır​

1. Sheet1 veya Sheet2 sayfası açılır.
2. Örnek veriler A ile E sütunları arasından silinerek üçüncü satırdan itibaren kendi kayıtlarınız girilir. F ile J arasındaki hücrelere veri yazılmaz; bu alan formülün sonuç alanıdır.
3. A sütununa toplam matrah, B sütununa toplam KDV, C sütununa genel toplam yazılır.
4. D ve E sütunlarına kayıttaki iki oran yüzde puanı olarak girilir. İki oranın birbirinden farklı olması gerekir.
5. F ile I arasındaki sütunlarda her orana ait matrah ve KDV tutarları kendiliğinden görünür.
6. J sütunundaki sağlama değerinin 0 olduğu, yani hücrede çizgi işareti göründüğü kontrol edilir.

Dikkat Edilmesi Gereken Noktalar​

• Oran yazımı: Oranlar yüzde puanı olarak girilir. Yüzde 8 için 8 yazılmalıdır; 0,08 yazılırsa formül bunu yüzde 0,08 olarak değerlendirir ve sonuç hatalı çıkar.
• Eşit oran: İki oran birbirine eşit girilirse bölen sıfır olacağı için ayrıştırma yapılamaz ve sonuç sütunlarında hata görülür.
• Sonuç alanının boş kalması: F ile J arasındaki hücrelerden birine elle veri yazılırsa formül sonuçlarını yayamaz ve F3 hücresinde #TAŞMA! hatası görülür. Bu durumda yazılan veri silindiğinde sonuçlar geri gelir.
• Negatif matrah: Bulunan matrahlardan biri negatif çıkarsa, girilen oranların kayıttaki oranlarla uyuşmadığı ya da toplam KDV'nin yanlış girildiği düşünülmelidir.
• Kuruş farkları: Belgede KDV kalem kalem hesaplanıp yuvarlanmışsa, toplam KDV'den geriye doğru bulunan matrahlar belgedeki tutarlardan birkaç kuruş farklı olabilir. Bu fark sağlama sütununda da görülebilir.
• İkiden fazla oran: Kayıtta üç ya da daha fazla oran varsa, yalnızca toplam matrah ve toplam KDV ile tek bir çözüm bulunamaz. Bu dosya iki oranlı kayıtlar için hazırlanmıştır.

Excel Sürümü ve Online Hesaplama Seçeneği​

Formülde kullanılan TRIMRANGE işlevi yalnızca Microsoft 365 Excel'in güncel sürümlerinde bulunur. Excel 2021, Excel 2019 ve daha eski sürümlerde formül hata verir. LET, LAMBDA, DROP ve HSTACK işlevleri de eski sürümlerde yer almaz.
Dosyayı indirmeden tek bir kayıt için hızlıca hesap yapmak isteyenler ya da eski bir Excel sürümü kullananlar, aynı hesabı tarayıcıda yapan online iki bilinmeyenli KDV hesaplama aracını kullanabilir. Çok sayıda kaydın toplu olarak ayrıştırılması ve sonuçların başka bir çalışmaya aktarılması gerektiğinde ise bu Excel dosyası daha pratiktir.

Kimler İçin Uygun​

Dosya; muhasebe meslek mensupları, ön muhasebe çalışanları, mali işler birimleri ve işletme sahipleri için hazırlanmıştır. Özellikle satış raporları, fiş toplamları ya da fatura özetleri yalnızca toplam tutarlarla elde edildiğinde matrahın oranlara bölünmesi için kullanılabilir. Eğitim amacıyla, iki bilinmeyenli denklem sisteminin Excel'de tek bir formülle nasıl kurulduğunu ve dinamik dizi işlevlerinin birlikte nasıl kullanıldığını göstermek için de uygun bir örnektir.
Toplam matrah ve toplam KDV elinizdeyse, iki orana düşen tutarlar artık elle deneme yanılma yapılmadan, tek formülle ve sağlamasıyla birlikte bulunur.