ورقة لحساب أقساط القرض والتمويل العقاري

تخطيط الورقة — تبويب "Invoices"

العمود	العنوان	يملؤه
A	رقم الفاتورة	أنت
B	العميل	أنت (قائمة منسدلة من تبويب "Clients")
C	تاريخ الإصدار	أنت
D	المبلغ	أنت
E	مهلة السداد (أيام)	معادلة
F	تاريخ الاستحقاق	معادلة
G	تاريخ الدفع	أنت (اتركه فارغًا حتى الدفع)
H	الحالة	معادلة
I	أيام التأخير	معادلة

المعادلات (مقتطف، 6 من 10)

E2 — مهلة السداد من قائمة العملاء =IFERROR(VLOOKUP(B2, Clients!A:C, 3, FALSE), 30) تبحث عن مهلة العميل في تبويب "Clients"، وإن لم يكن العميل موجودًا تستخدم 30 يومًا.

F2 — تاريخ الاستحقاق =IF(C2="", "", C2 + E2) تضيف المهلة إلى تاريخ الإصدار، وتبقى الصفوف الفارغة فارغة.

H2 — الحالة =IF(C2="", "", IF(G2<>"", "مدفوعة", IF(TODAY()>F2, "متأخرة", "مفتوحة"))) "مدفوعة" إذا أُدخل تاريخ الدفع؛ وإلا "متأخرة" بعد تاريخ الاستحقاق، وإلا "مفتوحة".

I2 — أيام التأخير =IF(H2="متأخرة", TODAY() - F2, "")

Dashboard!B2 — إجمالي المستحقات =SUMIFS(Invoices!D:D, Invoices!H:H, "<>مدفوعة", Invoices!C:C, "<>")

Dashboard!A5 — الرصيد حسب العميل (معادلة واحدة تملأ الجدول) =QUERY(Invoices!B:H, "select B, sum(D) where H <> 'مدفوعة' and B is not null group by B order by sum(D) desc label sum(D) 'المستحق'", 1)

أين تلصقها

• ضع E2 وF2 وH2 وI2 في الصف 2 من "Invoices"، ثم اسحبها حتى الصف 500
• الصق معادلتَي Dashboard مرة واحدة؛ فهما تتحدّثان من تلقاء نفسيهما
• تنسيق شرطي على H2:H500: إذا كان النص "متأخرة" ← تعبئة حمراء فاتحة

حالات خاصة تمت معالجتها

• فاتورة مدفوعة جزئيًا: أضف صفًّا بالمبلغ المتبقي ورقمًا جديدًا مثل INV-041b
• تغيّر اسم العميل: غيّره في "Clients" فقط؛ فالقائمة المنسدلة تحافظ على اتساق الصفوف
• Excel بدلًا من Sheets: كل المعادلات تعمل عدا QUERY؛ استخدم جدولًا محوريًا على الأعمدة B وD وH

مثال للتحقق

الفاتورة INV-038، العميل عيادة الابتسامة لطب الأسنان، صدرت في 1 سبتمبر، مهلة 14 يومًا ← تستحق في 15 سبتمبر. اليوم 20 أكتوبر ولم تُدفع ← متأخرة، 35 يومًا.