كيف تصنّف معاملات البنك في Excel؟
غالبًا ما تتضمّن إدارة الشؤون المالية الشخصية أو التجارية مراجعة قائمة مفصلة بالمعاملات البنكية الشهرية. وقد تتضمّن سجلات هذه المعاملات مجموعة واسعة من الأوصاف، بدءًا من المطاعم والمحلات ووصولًا إلى المرافق والخدمات. ويصبح تتبع الإنفاق وتحليله أكثر وضوحًا بكثير عندما يمكنك تصنيف كل معاملة ضمن فئة معيّنة—مثل «طلبات خارجية» أو «بقالة» أو «مرافق» أو «مصروف عائلي». ومن خلال أتمتة عملية التصنيف في Excel بناءً على كلمات مفتاحية موجودة في أوصاف المعاملات، يمكنك اكتساب رؤية دقيقة لنفقاتك وأنماط إنفاقك كل شهر.
كما هو موضح في لقطة الشاشة أدناه، افترض أن لديك بيانات أولية مع تفاصيل البائع أو الخدمة في العمود B، وترغب في الحصول على مجموعة مبسّطة من الفئات (مثل أن أي معاملة تحتوي على ")Mc Donalds« تُصنّف تلقائيًا كـ »طلبات خارجية«، بينما تُصنّف »Walmart« كـ »مصروف عائلي") وفقًا لقواعدك الشخصية أو المؤسسية. ويستعرض هذا الدليل خطوة بخطوة عدة حلول عملية تشمل طرقًا تعتمد على الصيغ وأساليب أتمتة متقدمة.
المحتويات:
تصنيف معاملات البنك باستخدام صيغة في Excel
رمز VBA - أتمتة التصنيف باستخدام ماكرو يعتمد على قائمة مسبقة التحديد
طرق أخرى مضمنة في Excel - استخدام Power Query مع منطق الأعمدة الشرطية
تصنيف معاملات البنك باستخدام صيغة في Excel
إذا كنت تفضّل اتباع نهج مباشر يعتمد على الصيغة لتصنيف معاملاتك البنكية في Excel، فاتبع هذه الخطوات. إنها الطريقة المثالية للمستخدمين الذين يبحثون عن نتائج سريعة ويرتاحون للحفاظ على جدول بحث صغير يحتوي الكلمات المفتاحية والفئات ذات الصلة.
أولاً، أنشئ عمودين «مساعدين» خارج قائمة المعاملات الرئيسية: أحدهما يحتوي على الكلمات المفتاحية (المقابلة لما قد يظهر في وصف المعاملة)، والآخر يحتوي على الفئة التي تريد ربطها بكل كلمة مفتاحية.
1. في هذا المثال، أدخل جميع الكلمات المفتاحية التي تريد مطابقتها (مثل «Mc Donalds» و«Walmart» وما إلى ذلك) في العمود A من الصف 30 إلى 41، وأدخل أسماء الفئات المقابلة (مثل «طلبات خارجية» و«مصروف عائلي» وما إلى ذلك) في العمود B من الصف 30 إلى 41.
وإذا كانت قائمة كلماتك المفتاحية أو فئاتك أطول أو تتغير باستمرار، فما عليك سوى تعديل النطاقات لتغطي جميع احتياجاتك. انظر لقطة الشاشة:
2. بعد ذلك، انقر على الخلية الأولى في عمود الإخراج المطلوب (مثل F3 بجانب آخر عمود يحتوي على بياناتك)، ثم أدخل صيغة المصفوفة التالية. اضغطCtrl + Shift + Enter (وليس مفتاح Enter وحده) لتأكيدها، لأنها صيغة مصفوفة. وستقوم هذه الصيغة بالبحث عن أول كلمة مفتاحية موجودة في الوصف وإرجاع الفئة المطابقة.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),«Other»)
بمجرد أن تعمل الصيغة بشكل صحيح في الصف الأول، اسحبمقبض الملء التلقائيلأسفل العمود لتطبيق صيغة التصنيف على جميع صفوف المعاملات المتبقية.
شرح المعلمات:
تحليل الإيجابيات والسلبيات:يتيح لك هذا النهج القائم على الصيغ إعدادًا سريعًا وصيانةً سهلة، بشرط أن تظل قواعد التصنيف الخاصة بك مستقرة. ولكن إذا أصبحت قائمة كلماتك المفتاحية أكثر تعقيدًا أو احتجت إلى تحديث الفئات بشكل متكرر، فقد يتحول التعامل مع الأعمدة المساعدة والصيغ إلى مهمةٍ روتينية مملة — وهنا قد تجد أن الحل الأمثل هو الانتقال إلى الأتمتة عبر VBA أو Power Query.
نصيحة عملية:إذا تطابقت عدة كلمات مفتاحية في وصفٍ ما، فسيتم تحديد الفئة بناءً على أول كلمة مفتاحية تظهر في قائمتك. ولإعطاء أولوية لكلمة مفتاحية معيّنة، ضعها في بداية عمود البحث الخاص بك.
استكشاف الأخطاء الشائعة وإصلاحها:إذا حصلت على نتائج غير متوقعة، فتحقق مجددًا من نطاق البحث وتأكد من أن كلماتك المفتاحية مكتوبة بشكل متسق وكامل. تحقق أيضًا من وجود مسافات زائدة أو اختلافات في التنسيق في أوصاف معاملاتك.
رمز VBA - أتمتة التصنيف باستخدام ماكرو يطابق أوصاف المعاملات مع الفئات بناءً على قائمة مسبقة التحديد
يستخدم هذا الحل ماكرو VBA لأتمتة عملية مطابقة المعاملات مع الفئات، مع تحسين التحكم والقابلية للتوسّع. وهو مثالي خصوصًا للمستخدمين الذين يتعاملون مع أحجام كبيرة من المعاملات، أو لمن يرغب في جعل مرجع الكلمة المفتاحية/الفئة ديناميكيًّا وتقليل الاعتماد على الإدارة اليدوية للصيغ.
السيناريو المناسب:عندما تكون قائمة المعاملات طويلة، أو كانت التحديثات متكررة، أو إذا أردت تجنب الصيانة اليدوية للصيغ، فإن ماكرو VBA يستطيع معالجة كل وصف وتعيين الفئة المناسبة بكفاءة أعلى، مع منطق قابل للتخصيص ورسائل توجيهية.
خطوات التشغيل:
- أعد قائمة تربط الكلمات المفتاحية بالفئات، مشابهة للأعمدة المساعدة المستخدمة في طريقة الصيغة (مثلًا، في العمودين A وB بدءًا من الصف 30 فما دون).
- اضغطAlt + F11 لفتح محررVisual Basic for Applications. في نافذة VBA، انقر فوقإدراج > وحدة نمطيةلإضافة وحدة جديدة.
انسخ والصق الكود التالي في الوحدة النمطية:
Sub CategorizeTransactions()
Dim lastRow As Long
Dim i As Long
Dim descCell As Range
Dim kwRow As Long
Dim kwRange As Range
Dim catRange As Range
Dim kwCount As Long
Dim catResult As String
Dim matched As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
kwCount = Cells(Rows.Count, "A").End(xlUp).Row - 29
Set kwRange = Range("A30:A" & 29 + kwCount)
Set catRange = Range("B30:B" & 29 + kwCount)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To lastRow
Set descCell = Cells(i, "B")
catResult = "Other"
matched = False
For kwRow = 1 To kwCount
If InStr(1, descCell.Value, kwRange.Cells(kwRow, 1).Value, vbTextCompare) > 0 Then
catResult = catRange.Cells(kwRow, 1).Value
matched = True
Exit For
End If
Next kwRow
Cells(i, "F").Value = catResult
Next i
End Subطريقة الاستخدام:
- انقر فوق زر
تشغيلأو اضغطF5في محرر VBA لتنفيذ الماكرو. سيقوم الماكرو بمعالجة كل وصف معاملة في العمود B، ومقارنته بقائمة الكلمات المفتاحية، ثم كتابة الفئة المطابقة (أو «أخرى» إذا لم يُعثر على تطابق) في العمود F من نفس الصف. - يمكنك تعديل تحديد عدد الصفوف أو النطاقات داخل الماكرو ليتناسب بدقة مع تنسيق بياناتك—مثل تغيير موضع بدء أوصاف المعاملات أو موقع القائمة المساعدة. تأكد من أن قائمة كلماتك المفتاحية خالية من الخلايا الفارغة، وأن تكون الفئات واضحة وفريدة لتسهيل الرجوع إليها.
تشغيلأو اضغطF5في محرر VBA لتنفيذ الماكرو. سيقوم الماكرو بمعالجة كل وصف معاملة في العمود B، ومقارنته بقائمة الكلمات المفتاحية، ثم كتابة الفئة المطابقة (أو «أخرى» إذا لم يُعثر على تطابق) في العمود F من نفس الصف.تحليل الإيجابيات والسلبيات:يتميّز حل VBA بمرونته الفائقة في التعامل مع قواعد أكثر تعقيدًا، ويُمكنك إعادة تشغيله في أي وقت بعد تحديث قائمة الكلمات المفتاحية أو الفئات، كما يلغي الحاجة إلى استخدام صيغ المصفوفة. ومع ذلك، يتطلب هذا الماكرو تفعيل العمليات الآلية (Macros) في Excel، وهو ما قد لا يناسب جميع المستخدمين أو البيئات.
نصيحة عملية:احفظ ماكرو VBA الخاص بك في ملف قابل لإعادة الاستخدام، واحفظ دائمًا نسخة احتياطية من بياناتك قبل تشغيل الماكرو تحسبًا لأي استبدال عرضي.
اقتراحات استكشاف الأخطاء وإصلاحها:إذا لم يتم تحديث الفئات كما هو متوقع، فتحقق من أن قوائم كلماتك المفتاحية والفئات متوافقة ولا تحتوي الخلايا على أحرف مخفية. يكون VBA غير حساس لحالة الأحرف إذا استخدمت «vbTextCompare»، لكن قد تظل هناك حالات عدم تطابق بسبب التنسيق.
طرق أخرى مضمنة في Excel - استخدام Power Query لإعداد تصنيف قائم على القواعد عبر منطق الأعمدة الشرطية
إذا كنت تفضّل اتباع نهج أتمتة عصري خالٍ من البرمجة، فإن Power Query يوفّر وسيلة قوية لتصنيف معاملاتك البنكية. وهي الطريقة المثالية عند استيراد بيانات المعاملات من ملفات CSV أو مصادر عبر الإنترنت، أو عند إدارة قواعد تصنيف ديناميكية ومتطورة—حيث يتركّز المنطق في مكان واحد ويتيح تحديثات سهلة دون الحاجة إلى صيغ معقدة أو نصوص VBA.
خطوات التشغيل:
- أولاً، تأكد من أن معاملاتك منسَّقة كجدول أو أن نطاق البيانات واضح. حدد أي خلية في جدولك، ثم انتقل إلىبيانات > من جدول/نطاقلتحميل البيانات في Power Query.
- في محرر Power Query، انقر فوقإضافة عمود > عمود شرطيلإنشاء عمود جديد قائم على قواعد الفئات.
- عرِّف قواعد المطابقة الخاصة بك، مثل:
- إذا كانالوصفيحتوي على «Mc Donalds»، فقم بإخراج «طلبات خارجية»
- إذا كانالوصفيحتوي على «Walmart»، فقم بإخراج «مصروف عائلي»
- خلاف ذلك، قم بإخراج «أخرى»
- انقر فوق «موافق» لإنشاء العمود الشرطي، ثم اخترإغلاق وتحميللإرجاع نتائج الفئات إلى Excel.
تحليل الإيجابيات والسلبيات:يوفّر Power Query مرونةً عاليةً في تعديل القواعد، ويتميّز باتصالٍ سلسٍ مع مصادر البيانات الخارجية، كما يُحدّث النتائج فورًا بمجرد تحديث بيانات المعاملات أو القواعد. بالإضافة إلى ذلك، فهو أكثر كفاءةً بكثيرٍ في التعامل مع مجموعات البيانات الضخمة مقارنةً بالصيغ اليدوية. ومع ذلك، يتطلب Power Query إعدادًا أوليًّا وفهمًا جيّدًا لواجهته، وهو ما قد يشكّل تحديًا لبعض المستخدمين الجدد.
نصائح عملية:رتّب قواعدك حسب ترتيب الأولوية (من الأعلى إلى الأسفل) داخل مربع حوار العمود الشرطي؛ سيتم تطبيق أول قاعدة مطابقة. يمكنك بسهولة تعديل القواعد أو إضافة قواعد جديدة أو حذفها في Power Query دون تغيير صيغ ورقة عمل Excel الرئيسية.
تذكيرات بالأخطاء:إذا قمت باستيراد معاملات جديدة ولم يتم تحديث النتيجة كما هو متوقع، فانقر علىتحديثفي علامة تبويب البيانات في Excel. انتبه إلى التهجئة والمطابقة الدقيقة للكلمات المفتاحية في Power Query، فقد تحول المطابقات الجزئية دون تعيين الفئة الصحيحة.
اقتراح لحل المشكلات:إذا لم تظهر فئاتك بشكل صحيح، راجع قواعد العمود الشرطي الخاصة بك بحثًا عن تعارضات أو حالات مفقودة. من المفيد اختبار بعض الإدخالات النموذجية قبل تطبيق التغييرات على مجموعة البيانات الكاملة.
باختصار، سواء اخترت الصيغ المصفوفية أو مرونة ماكرو VBA أو أتمتة Power Query المبسَّطة، يوفّر Excel عدة طرق عملية لتصنيف معاملاتك البنكية بكفاءة. تأكد دائمًا من مراجعة قواعد التصنيف واتساق النتائج كلما تطورت بيانات المعاملات أو معايير التصنيف.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل