لماذا يفوّت فحص التكرار في Excel الفواتير المدفوعة مرتين، وكيف توحّد رقم الفاتورة قبل العدّ

كشف التكرار في Excel يفوّت INV-0088 مقابل 88 والأرقام العربية والمسافات. صيغ توحيد رقم الفاتورة ومثال من 8 صفوف: صفر تكرار قبل التوحيد ومجموعتان بعده.

بقلم فريق زاديتا · نُشرت في 1 نوفمبر 2026

هذه المهمة ينفّذها على ملفاتك منتج كشف الفواتير المدفوعة مرتين على جهازك بلا إنترنت. المقالة تشرح المهمة نفسها وكيف تُنجز يدوياً.

كشف الفواتير المكررة في Excel بأمر «إزالة التكرارات» أو بدالة COUNTIF على عمود رقم الفاتورة كما ورد يفوّت أغلب الحالات الحقيقية، لأن الأمرين يقارنان النص حرفاً بحرف، والفاتورة الثانية تدخل بكتابة مختلفة: INV-0088 مقابل 88، أو ٣٣٤٩ بأرقام عربية مقابل 3349، أو مسافة زائدة، أو «شركة» بدل «مؤسسة» في اسم المورد. الحل توحيد رقم الفاتورة واسم المورد في أعمدة مساعدة، ثم العدّ على مفتاح مركّب بـCOUNTIFS. هذه المقالة تعطي صيغ التوحيد كاملة، وتطبّقها على 8 صفوف تعطي صفر تكرار قبل التوحيد ومجموعتين بعده، وتحدد ما لا يستطيعه Excel أصلاً. الأسباب التي تُدخل الفاتورة الثانية وما يُسترد منها في الفواتير المدفوعة مرتين: كيف تحدث وكيف تُكتشف، والنسخة المنهجية من التوحيد في منتج كشف الفواتير المدفوعة مرتين.

ماذا تقارن «إزالة التكرارات» وCOUNTIF فعلاً

صفحة دعم Microsoft عن إيجاد التكرارات وإزالتها تنص على أن البيانات المكررة تُحذف نهائياً عند استخدام «إزالة التكرارات»، وتنصح بنسخ البيانات الأصلية إلى ورقة أخرى قبله؛ والتنسيق الشرطي «القيم المكررة» يميّز ولا يحذف. كلاهما يحكم بالتطابق التام للقيمة في الخلية، وCOUNTIF تقارن المعيار بالقيمة كما هي. سبعة فروق شائعة تجعل الفاتورة الواحدة قيمتين:

الأصفار في البداية. 0088 و88 رقمان مختلفان نصاً ورقم واحد عند المورد. في التقييم الجنائي لمدفوعات الموردين في جهة اتحادية أمريكية (OIG، 2021) قدّم مورد فاتورته في جزأين، أحدهما بصفرين قبل الرقم والآخر بدونهما، فتجاوز الجزآن فحص Oracle الآلي للأرقام المكررة.

البادئات. INV وInvoice No و«فاتورة رقم» تُكتب في مرة وتُحذف في أخرى بحسب من أدخلها.

الأرقام العربية. ٣٣٤٩ بالأرقام العربية الهندية (من U+0660 إلى U+0669) و3349 بالأرقام اللاتينية حروف مختلفة في Unicode مهما تشابه شكلها، ولوحة المفاتيح العربية تنتج الأولى.

المسافات والرموز. مسافة في نهاية الرقم من النسخ واللصق، أو شرطة بين البادئة والرقم، أو / بدل -.

اسم المورد بكتابتين. «شركة الوسام للتغليف» و«الوسام للتغليف» و«مؤسسة الوسام للتغليف» ثلاث قيم، وإذا كان المورد مسجّلاً برقمين في النظام فالفحص داخل رقم المورد لا يراها أصلاً؛ المورد المسجّل مرتين يُكشف أيضاً من ملف الموردين نفسه ضمن مطابقة بيانات الموردين بالموظفين.

المبلغ بفارق هللات. من يعتمد على المبلغ بدل الرقم يفوّت 11,500.00 مقابل 11,500.01 بسبب تقريب مختلف أو عمولة تحويل.

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

الفروق الخمسة الأولى يحلّها التوحيد. السادس يحلّه الاعتماد على الرقم بعد التوحيد لا على المبلغ في مستوى «مؤكد». السابع لا يحلّه Excel.

مثال عملي: 8 صفوف من تقرير مدفوعات

البيانات توضيحية لشركة افتراضية، «شركة الروابي للتجهيزات»، من تقرير مدفوعات سبتمبر 2026، فاتورة في كل سطر. الصفوف 2 و4 و8 مكتوبة كما يكتبها موظف إدخال ثانٍ:

الفحص الخام: =COUNTIF($C$2:$C$9,C2) في كل صف يعطي 1، فلا يوجد مكرر، و«إزالة التكرارات» على العمود C تحذف صفر صفوف، والتنسيق الشرطي لا يميّز شيئاً. والحقيقة أن في الجدول مجموعتين مؤكدتين ومرشحاً ثالثاً.

خطوات توحيد رقم الفاتورة بصيغ Excel

العمود B اسم المورد، وC رقم الفاتورة كما ورد، وD التاريخ، وE المبلغ، والبيانات في الصفوف 2 إلى 9. كل خطوة في عمود، حتى تُراجَع النتائج الوسيطة وتُعدَّل خطوة واحدة عند الحاجة:

1. الأرقام العربية إلى لاتينية في F. الرقم العربي ٠ هو الحرف Unicode رقم 1632 والتسعة بعده متتالية، فتُستبدل بعشر دوال SUBSTITUTE متداخلة مع UNICHAR: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,UNICHAR(1632),"0"),UNICHAR(1633),"1"),UNICHAR(1634),"2"),UNICHAR(1635),"3"),UNICHAR(1636),"4"),UNICHAR(1637),"5"),UNICHAR(1638),"6"),UNICHAR(1639),"7"),UNICHAR(1640),"8"),UNICHAR(1641),"9"). في Excel 365 تختصرها =REDUCE(C2,SEQUENCE(10,1,0),LAMBDA(t,n,SUBSTITUTE(t,UNICHAR(1632+n),n))). الأرقام الفارسية (من الحرف 1776) تحتاج عشر دوال أخرى إذا ظهرت في ملفك، وUNICHAR متاحة من Excel 2016.
2. المسافات والرموز وحالة الأحرف في G: =UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(F2),"-","")," ",""),"/","")). TRIM تحذف المسافات في الطرفين والمكررة في الوسط، والباقي يحذف ما بينها.
3. البادئات في H: =IF(LEFT(G2,3)="INV",MID(G2,4,50),IF(LEFT(G2,6)="فاتورة",MID(G2,7,50),G2))، وتُضاف IF لكل بادئة تظهر في ملفك مثل BILL وSI. البادئة تُحذف من البداية فقط، لأن SUBSTITUTE كانت ستحذفها من وسط الرقم أيضاً.
4. الأصفار في البداية في I: =IF(ISNUMBER(VALUE(H2)),TEXT(VALUE(H2),"0"),H2). الرقم الخالص يعود نصاً بلا أصفار، والرقم الذي فيه حروف يبقى كما هو. للأرقام الأطول من 15 خانة تُستبدل هذه الخطوة بحذف الأصفار حرفاً حرفاً، لأن VALUE تفقد الدقة بعدها.
5. اسم المورد في J: =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"شركة ",""),"مؤسسة ",""),"أ","ا"),"إ","ا"),"ة","ه"))، مع دالة لكل كلمة شكل قانوني في ملفك مثل المحدودة وذ.م.م وCo وLLC.

النتيجة على الصفوف الثمانية:

المفتاح المركّب والعدّ بـ COUNTIFS

المفتاح في K: =J2&"|"&I2، والعدّ في L: =COUNTIFS($K$2:$K$9,K2,$E$2:$E$9,">0") مع استبعاد الإشعارات الدائنة السالبة من العدّ. تعطي 2 للصفوف 1 و2 و3 و4، و1 للباقي: مجموعتان بعد التوحيد مقابل صفر قبله. COUNTIFS تقبل حتى 127 زوجاً من النطاق والمعيار، فيُضاف المبلغ أو نافذة التاريخ شرطاً عند الحاجة. التمييز بالتنسيق الشرطي بقاعدة =$L2>1 على الجدول كله، ثم الترتيب بالمفتاح حتى يتجاور الصفان. المبلغ الممكن استرداده إن ثبت التكرار بعد فتح المستندات: (12,600.00 − 6,300.00) + (9,950.00 − 4,975.00) = 11,275.00 ر.س.

ما لا يستطيعه Excel: الخانات المبدّلة

الصفان 7 و8 لمورد واحد بمبلغ واحد بفارق يومين، ورقماهما 5612 و5621 بتبديل آخر خانتين؛ المفتاحان مختلفان، وCOUNTIFS تعطي 1 لكل منهما. وليس في Excel دالة جاهزة تقيس «اختلاف حرف واحد أو تبديل حرفين» بين نصين؛ بناؤها بـLAMBDA في Excel 365 ممكن لكنه طويل وهش. البديل اليدوي مرشّح واسع في M: =COUNTIFS($J$2:$J$9,J2,$E$2:$E$9,E2,$D$2:$D$9,">="&(D2-7),$D$2:$D$9,"<="&(D2+7))-1 يعطي 1 للصفين 7 و8، وكذلك للصفين 3 و4 المؤكدين أصلاً بالمفتاح، و0 لغيرها، ثم النظر بالعين في الرقمين. الصفان 5 و6 رسوم شهرية برقمين متتاليين وبينهما 30 يوماً، فنافذة السبعة أيام تُخرجهما، وهذا صحيح.

الأخطاء الشائعة في كشف التكرار بـ Excel

الأول تشغيل «إزالة التكرارات» على العمود الخام مباشرة، فيُحذف الصف الثاني إن وُجد تطابق حرفي ويضيع السند الذي تُبنى عليه المطالبة؛ العدّ والتمييز لا الحذف. الثاني العدّ على رقم السند أو القيد الداخلي، وهو فريد بطبيعته فيعطي صفر تكرار دائماً. الثالث ترك الإشعارات الدائنة في العدّ، فتظهر مع فاتورتها مجموعة مؤكدة وهي تصحيح لها. الرابع حذف البادئة بـSUBSTITUTE فتُحذف من وسط الأرقام التي تحوي الحروف نفسها. الخامس الاكتفاء بـTRIM، والمسافة غير القابلة للكسر القادمة من صفحات الويب (الحرف 160) لا تحذفها TRIM، فتُضاف SUBSTITUTE(x,UNICHAR(160),"") قبلها. والسادس دمج اسمين يختلفان في كلمة ذات معنى مثل «النور للمقاولات» و«النور للمقاولات العامة»؛ التوحيد يحذف كلمات الشكل القانوني فقط، والباقي يُترك لمستوى «احتمال متوسط» والنظر.

كيف يختصر المنتج المهمة

الخطوات الخمس والمفتاح والعدّ والمرشّح اليدوي هي ما ينفّذه منتج كشف الفواتير المدفوعة مرتين على الملف كاملاً في أربعة مستويات: «مؤكد» نفس المورد ونفس الرقم بعد التوحيد؛ و«احتمال مرتفع» نفس المورد والمبلغ ورقمان يختلفان بحرف واحد أو بتبديل حرفين متجاورين وبين التاريخين 7 أيام أو أقل، وهو ما لا تصل إليه صيغ Excel؛ و«احتمال متوسط» نفس المورد والمبلغ والتاريخ بأرقام مختلفة، أو نفس الرقم والمبلغ لموردين مختلفين؛ و«للمراجعة» نفس المورد والمبلغ خلال نافذة الأيام في الإعدادات، وافتراضها 7. التوحيد فيه يحذف الرموز والمسافات ويحوّل الأرقام العربية ويتجاهل البادئات INV وInvoice و«فاتورة» وبادئات إضافية تضعها في Settings.xlsx، ويتجاهل الأصفار في البداية، ويوحّد اسم المورد بحذف شركة ومؤسسة والمحدودة وذ.م.م وCo وLLC وEst. ورقة «احتمال مرتفع» تعرض حالة الخانات المبدّلة التي يتوقف عندها Excel:

الإشعارات الدائنة تُستبعد تلقائياً وتُذكر في «معلومات التشغيل»، وفي اختبار السرعة على جهاز البناء عالج المنتج ملف 100,000 سطر في 25.4 ثانية.

تنبيه مهني

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

أسئلة شائعة

المراجع

1. Find and remove duplicates — Microsoft Support — «إزالة التكرارات» تحذف البيانات المكررة نهائياً، وينصح بنسخ الأصل إلى ورقة أخرى قبلها؛ التنسيق الشرطي «القيم المكررة» يميّز ولا يحذف.
2. COUNTIFS function — Microsoft Support — الصيغة COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) حتى 127 زوجاً، ورموز البدل ? و*.
3. SUBSTITUTE function — Microsoft Support — الصيغة SUBSTITUTE(text, old_text, new_text, [instance_num])؛ بلا instance_num تُستبدل كل التكرارات.
4. UNICHAR function — Microsoft Support — تعيد حرف Unicode المقابل للرقم؛ متاحة من Excel 2016.
5. TRIM function — Microsoft Support — TRIM وحدها لا تحذف المسافة غير القابلة للكسر (الحرف 160).
6. OIG Report 2021-IT-C-002: Forensic Evaluation of the Bureau's Vendor Payment Process — Federal Reserve Board / CFPB OIG — فاتورة قُدمت في جزأين أحدهما بصفرين قبل الرقم والآخر بدونهما فتجاوزا فحص Oracle الآلي للأرقام المكررة (ص 3).
7. Detailed Guidelines for E-Invoicing (Version 2, May 2023) — هيئة الزكاة والضريبة والجمارك — الرقم المرجعي للفاتورة فريد ومتسلسل وفق المادة 53، ويجوز تسلسل مستقل لكل فرع (البند 2.14).
8. Excel specifications and limits — Microsoft Support — دقة الأرقام 15 خانة

مقالات ذات صلة

• الفواتير المدفوعة مرتين: كيف تحدث وكيف تُكتشف · كشف الفواتير المدفوعة مرتين
• مراجعة مدفوعات الموردين: ثمانية اختبارات على دورة المشتريات والمدفوعات بمثال محسوب · كشف الفواتير المدفوعة مرتين
• كيف تسترد من المورد فاتورة دُفعت مرتين: الخطاب والمستندات والقيد · كشف الفواتير المدفوعة مرتين
• تطابق بيانات الموردين والموظفين: مؤشرات احتيال المشتريات · مطابقة الموردين بالموظفين

الأداة التي تنفّذ هذه المهمة: كشف الفواتير المدفوعة مرتين. الدور: مراجع · محاسب.