برنامج إدارة الأقساط باستخدام Excel

إدارة الأقساط باستخدام Excel و VBA: دليل شامل

إدارة الأقساط من أهم الأمور التي تواجهها العديد من الشركات، حيث تتيح متابعة الدفعات الشهرية أو السنوية بشكل منتظم وتسهيل عملية السداد للعملاء. في هذه المقالة، سنستعرض كيفية بناء نظام متكامل لإدارة الأقساط باستخدام Excel و VBA (Visual Basic for Applications). سنتعرف على كيفية إنشاء جداول أساسية، وتحليل البيانات باستخدام الجدول المحوري (Pivot Table)، ثم نستخدم VBA لإنشاء واجهات تفاعلية تسهّل عمليات الإدخال والتحليل.

لماذا Excel و VBA لإدارة الأقساط؟

يجمع Excel بين البساطة والمرونة في إدارة البيانات، بينما يضيف VBA قوة برمجية تتيح أتمتة العمليات المعقدة وإنشاء واجهات مخصصة لتسهيل التفاعل مع البيانات. باستخدام VBA، يمكنك إنشاء واجهة متكاملة لإدخال بيانات الأقساط، حساب الدفعات المتبقية، وتنظيم العمليات بسهولة.

1. إنشاء جدول الأقساط في Excel

أول خطوة في إنشاء نظام إدارة الأقساط هي إعداد جدول البيانات. في هذا الجدول، نحدد الأعمدة الأساسية التي نحتاج إليها مثل:

هذا الجدول يوفر لنا الأساسيات اللازمة، مثل تتبع المبالغ الكلية والمدفوعة، وعدد الأقساط المتبقية، والتاريخ المستحق للسداد.

2. استخدام الجداول المحورية (Pivot Table) لتحليل البيانات

بمجرد إدخال بيانات الأقساط، يمكنك الاستفادة من الجداول المحورية لتحليلها بطرق متعددة. الجداول المحورية تتيح لك:

• عرض الإجمالي المدفوع لكل عميل.
• تحليل الأقساط المتبقية على مستوى العملاء أو مجموعات منهم.
• إنشاء تقارير شهرية أو سنوية عن الأداء المالي.

خطوات إنشاء الجدول المحوري:

1. حدد جميع البيانات التي تريد تحليلها (بما في ذلك رؤوس الأعمدة).
2. من شريط الأدوات العلوي، اختر إدراج ثم جدول محوري (Pivot Table).
3. اختر ورقة جديدة لإنشاء الجدول.
4. اسحب اسم العميل إلى منطقة الصفوف، والمبلغ الكلي والمبلغ المدفوع إلى منطقة القيم.

الآن، ستحصل على تقرير مبسط يعرض لك بيانات العملاء والمدفوعات بوضوح.

3. تحسين تجربة المستخدم باستخدام VBA

لتحويل ملف Excel إلى أداة تفاعلية لإدارة الأقساط، سنستخدم VBA لإنشاء نماذج مخصصة (UserForms) تتيح إدخال البيانات بسهولة وحساب الأقساط تلقائيًا.

1- كود حساب إجمالي المبالغ والأقساط باستخدام ComboBox

هذا الكود يسمح بحساب المبالغ الكلية وعدد الأقساط والمدفوعات بناءً على اختيار المستخدم من ComboBox. عندما يختار المستخدم عميلًا من القائمة المنسدلة، يتم حساب الإجمالي وعرضه تلقائيًا.

Private Sub ComboBox1_Change()
Dim wsAqs As Worksheet
Dim wsCus As Worksheet
Dim lastRowAqs As Long
Dim lastRowCus As Long
Dim totalAmountAqs As Double
Dim installmentsCount As Long
Dim paidInstallments As Long
Dim i As Long
Dim monthlyInstallment As Double

‘ تحديد ورقة العمل
Set wsAqs = ThisWorkbook.Worksheets(“aqs”)
Set wsCus = ThisWorkbook.Worksheets(“CUS”)

‘ البحث عن آخر صف في ورقة aqs
lastRowAqs = wsAqs.Cells(wsAqs.Rows.Count, 1).End(xlUp).Row
lastRowCus = wsCus.Cells(wsCus.Rows.Count, 1).End(xlUp).Row

‘ حساب إجمالي المبلغ في العمود B من ورقة aqs
totalAmountAqs = 0
For i = 2 To lastRowAqs
totalAmountAqs = totalAmountAqs + wsAqs.Cells(i, 2).Value ‘ العمود B
Next i

‘ تحديث Label13
Label13.Caption = totalAmountAqs

‘ حساب عدد الأقساط من العمود C في ورقة aqs
installmentsCount = 0
For i = 2 To lastRowAqs
installmentsCount = installmentsCount + wsAqs.Cells(i, 3).Value ‘ العمود C
Next i

‘ حساب القسط الشهري
If installmentsCount > 0 Then
monthlyInstallment = totalAmountAqs / installmentsCount
Else
monthlyInstallment = 0
End If

‘ تحديث Label18 بالقسط الشهري للعميل
Dim totalPaid As Double
totalPaid = 0
paidInstallments = 0

For i = 2 To lastRowCus
If wsCus.Cells(i, 1).Value = ComboBox1.Value Then
totalPaid = totalPaid + wsCus.Cells(i, 4).Value ‘ العمود D
paidInstallments = paidInstallments + 1 ‘ عدد الصفوف التي تم دفع الأقساط لها
End If
Next i

Label18.Caption = totalPaid ‘ القيمة في العمود D
Label17.Caption = paidInstallments ‘ عرض عدد الأقساط المدفوعة
End Sub

شرح الكود:

• يتم تحديد أوراق العمل الخاصة بالأقساط والعملاء.
• يتم حساب المبالغ الكلية وعدد الأقساط المدفوعة بناءً على البيانات المدخلة في الجداول.
• يتم عرض النتائج مباشرة في الحقول المخصصة على واجهة المستخدم.

2- كود إضافة بيانات جديدة إلى جدول الأقساط

الكود التالي يتيح للمستخدم إدخال بيانات جديدة إلى الجدول عند الضغط على زر. يتم إدخال العميل، القسط، والتاريخ المستحق في الخانات المناسبة.

Private Sub CommandButton2_Click()

Dim wsAqs As Worksheet
Dim lastRow As Long

‘ تحديد ورقة العمل
Set wsAqs = ThisWorkbook.Worksheets(“aqs”)

‘ البحث عن آخر صف في العمود A
lastRow = wsAqs.Cells(wsAqs.Rows.Count, 1).End(xlUp).Row + 1 ‘ الصف الجديد

‘ ترحيل البيانات
wsAqs.Cells(lastRow, 1).Value = ComboBox1.Value ‘ العمود A
wsAqs.Cells(lastRow, 4).Value = Label18.Caption ‘ العمود D
wsAqs.Cells(lastRow, 5).Value = TextBox7.Value ‘ العمود E
wsAqs.Cells(lastRow, 6).Value = Label14.Caption ‘ العمود F

‘ مسح البيانات من الخانات
ComboBox1.Value = “”
Label13.Caption = “”
Label18.Caption = “”
Label17.Caption = “”
TextBox7.Value = “”
Label14.Caption = “”

‘ إعادة التركيز على ComboBox1
ComboBox1.SetFocus
MsgBox “تم إضافة البيانات بنجاح”
End Sub

شرح الكود:

• يتم تحديد آخر صف في الجدول لإدخال البيانات الجديدة.
• ترحيل البيانات المدخلة من ComboBox و TextBox إلى خلايا ورقة العمل المناسبة.
• مسح الحقول بعد إدخال البيانات، مع إعادة التركيز على الحقول للإدخال الجديد.

3- كود حساب تاريخ الشهر التالي

هذا الكود يستخدم لحساب تاريخ القسط التالي بناءً على التاريخ المدخل في TextBox7. يقوم بإضافة شهر تلقائيًا إلى التاريخ المعطى.

Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim inputDate As Date
Dim nextMonth As Date

‘ التحقق من صحة التاريخ المدخل
If IsDate(TextBox7.Value) Then
inputDate = CDate(TextBox7.Value)
nextMonth = DateAdd(“m”, 1, inputDate) ‘ إضافة شهر
Label14.Caption = Format(nextMonth, “dd-mm-yyyy”)
Else
Label14.Caption = “خطأ”
End If
End Sub

شرح الكود:

• يتحقق من أن التاريخ المدخل صالح.
• إذا كان صحيحًا، يتم إضافة شهر إلى التاريخ وعرضه في Label14

الخاتمة

باستخدام Excel و VBA، يمكنك إنشاء نظام متكامل لإدارة الأقساط، بدءًا من إعداد جدول البيانات الأساسي، وصولاً إلى إنشاء واجهات تفاعلية باستخدام النماذج البرمجية. هذه الحلول تتيح للشركات تتبع الدفعات بسهولة وضمان تفاعل سلس مع العملاء.

سواء كنت تدير شركة صغيرة أو كبيرة، يمكن أن يكون استخدام VBA لتحسين إدارة الأقساط أمرًا يوفر لك الوقت والمجهود، مع ضمان دقة المعلومات وسرعة الوصول إليها.

وعلاوة على ذلك، موقع جعفر نت يسهل عليك كل شيء! فهو يقدم لك نماذج مجانية ومدفوعة تساعدك في إدارة أعمالك بشكل سلس وفعال. كل ما عليك هو قراءة هذه المقالة لتعلم كيفية إنشاء النموذج بنفسك، أو ببساطة يمكنك شراء النموذج الجاهز بسهولة من الموقع. سواء اخترت تصميم النموذج بنفسك أو الحصول عليه جاهزًا، فإن جعفر نت هو الحل الأمثل لتلبية احتياجاتك!