كيفية مطابقة البيانات في Excel؟ دليل خطوة بخطوة (مع أمثلة)
طرق مختلفة لمطابقة البيانات في Excel
هناك طرق مختلفة لمطابقة البيانات في Excel ، إذا أردنا مطابقة البيانات في نفس العمود ، دعنا نقول أننا نريد التحقق من الازدواجية يمكننا استخدام التنسيق الشرطي من علامة التبويب الصفحة الرئيسية أو إذا أردنا مطابقة البيانات في اثنين أو المزيد من الأعمدة المختلفة يمكننا استخدام الدوال الشرطية مثل if function.
- الطريقة رقم 1 - استخدام وظيفة Vlookup
- الطريقة رقم 2 - استخدام الفهرس + وظيفة المطابقة
- الطريقة رقم 3 - إنشاء قيمة البحث الخاصة بك
الآن دعونا نناقش كل طريقة بالتفصيل
يمكنك تنزيل نموذج Excel لبيانات المطابقة من هنا - نموذج Excel لبيانات المطابقة# 1 - مطابقة البيانات باستخدام وظيفة VLOOKUP
لا يتم استخدام VLOOKUP فقط للحصول على المعلومات المطلوبة من جدول البيانات ، بل يمكن استخدامه أيضًا كأداة تسوية. عندما يتعلق الأمر بالتوفيق أو مطابقة البيانات ، فإن صيغة VLOOKUP تتصدر الجدول.
للحصول على مثال ، انظر إلى الجدول أدناه.
لدينا جدولا بيانات هنا ، الأول هو البيانات 1 والثاني هو البيانات 2.
نحتاج الآن إلى التوفيق بين ما إذا كانت البيانات في جدولين متطابقة أم لا. الطريقة الأولى لمطابقة البيانات هي دالة SUM في Excel لجدولين للحصول على إجمالي المبيعات.
البيانات 1 - الجدول
البيانات 2 - الجدول
لقد قمت بتطبيق وظيفة SUM لكل من عمود مبلغ البيع في الجدول. في الخطوة الأولى نفسها ، حصلنا على اختلاف في القيم. بيانات 1 جدول يبين إجمالي المبيعات من 2،16،214 و البيانات 2 جدول يبين إجمالي المبيعات من 2،10،214 .
الآن نحن بحاجة إلى فحص هذا بالتفصيل. لذلك ، دعنا نطبق وظيفة VLOOKUP لكل تاريخ.
حدد مصفوفة الجدول كنطاق البيانات 1 .
نحتاج إلى البيانات من العمود الثاني ونطاق البحث هو FALSE أي مطابقة تامة.
الإخراج معطى أدناه:
في الخلية التالية ، قم بخصم القيمة الأصلية بقيمة الوصول.
بعد الخصم نحصل على النتيجة على أنها صفر.
الآن انسخ والصق الصيغة في جميع الخلايا للحصول على قيم التباين.
في الخلية G6 و G12 حصلنا على الاختلافات.
في البيانات 1 لدينا 12104 للتاريخ 04-Mar-2019 وفي البيانات 2 لدينا 15104 لنفس التاريخ ، لذلك هناك فرق 3000.
وبالمثل ، بالنسبة لتاريخ 18 مارس 2019 في البيانات 1 ، لدينا 19351 وفي البيانات 2 لدينا 10351 ، وبالتالي فإن الفرق هو 9000.
# 2 - مطابقة البيانات باستخدام دالة INDEX + MATCH
لنفس البيانات ، يمكننا استخدام دالة INDEX + MATCH. يمكننا استخدام هذا كبديل لوظيفة VLOOKUP.
تُستخدم الدالة INDEX للحصول على القيمة من العمود المحدد بناءً على رقم الصف المقدم. لتوفير رقم الصف ، نحتاج إلى استخدام دالة MATCH بناءً على قيمة LOOKUP.
افتح دالة INDEX في الخلية F3.
حدد المصفوفة كنطاق عمود نتيجة ، مثل B2 إلى B14.
من أجل الحصول على رقم الصف ، افتح دالة MATCH الآن كوسيطة تالية.
حدد قيمة البحث كخلية D3.
بعد ذلك ، حدد صفيف البحث كعمود تاريخ المبيعات في البيانات 1.
في نوع المطابقة حدد "0 - مطابقة تامة".
أغلق قوسين واضغط على مفتاح الإدخال للحصول على النتيجة.
يعطي هذا أيضًا نفس النتيجة مثل VLOOKUP فقط. نظرًا لأننا استخدمنا نفس البيانات ، فقد حصلنا على الأرقام كما هي
# 3 - قم بإنشاء قيمة البحث الخاصة بك
لقد رأينا الآن كيفية مطابقة البيانات باستخدام وظائف Excel. الآن سنرى سيناريو مختلف في الوقت الحقيقي. لهذا المثال ، انظر إلى البيانات أدناه.
في البيانات أعلاه ، لدينا بيانات المبيعات حسب المنطقة والتاريخ كما هو موضح أعلاه. نحتاج إلى إجراء عملية مطابقة البيانات مرة أخرى. دعنا نطبق وظيفة VLOOKUP وفقًا للمثال السابق.
لدينا العديد من الفروق. دعونا نفحص كل حالة على حدة.
في الخلية I5 حصلنا على التباين 8300. لنلق نظرة على الجدول الرئيسي.
على الرغم من أن قيمة الجدول الرئيسي هي 12104 ، فقد حصلنا على القيمة 20404 من دالة VLOOKUP. السبب في ذلك هو أن VLOOKUP يمكنها إرجاع قيمة أول قيمة بحث تم العثور عليها.
في هذه الحالة ، تكون قيمة البحث الخاصة بنا هي التاريخ ، أي 20-مارس -2019. في الخلية أعلاه للمنطقة الشمالية لنفس التاريخ لدينا قيمة 20404 ، لذلك أعاد VLOOKUP هذه القيمة للمنطقة الشرقية أيضًا.
للتغلب على هذه المشكلة ، نحتاج إلى إنشاء قيم بحث فريدة. اجمع بين المنطقة والتاريخ ومبلغ المبيعات في كل من البيانات 1 والبيانات 2.
البيانات 1 - الجدول
البيانات 2 - الجدول
لقد أنشأنا الآن قيمة فريدة لكل منطقة بالقيمة المجمعة للمنطقة وتاريخ البيع ومبلغ البيع.
يتيح استخدام هذه القيم الفريدة تطبيق وظيفة VLOOKUP.
قم بتطبيق الصيغة على جميع الخلايا ، وسوف نحصل على تباين الصفر في جميع الخلايا.
على هذا النحو ، باستخدام وظائف Excel ، يمكننا مطابقة البيانات والعثور على الفروق. قبل تطبيق الصيغة ، نحتاج إلى إلقاء نظرة على التكرارات في قيمة البحث من أجل تسوية دقيقة. المثال أعلاه هو أفضل توضيح للقيم المكررة في قيمة البحث. في مثل هذه السيناريوهات ، نحتاج إلى إنشاء قيم البحث الفريدة الخاصة بنا والوصول إلى النتيجة.
