مقارنة جدولين

لدينا جدولين (على سبيل المثال ، الإصدارات القديمة والجديدة من قائمة الأسعار) ، والتي نحتاج إلى المقارنة بينها والعثور على الاختلافات بسرعة:

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

لأي مهمة في Excel ، يوجد دائمًا أكثر من حل واحد (عادةً 4-5). لمشكلتنا ، يمكن استخدام العديد من الأساليب المختلفة:

• وظيفة VPR (البحث) - ابحث عن أسماء المنتجات من قائمة الأسعار الجديدة في القديم واعرض السعر القديم بجوار الجديد ، ثم اضبط الاختلافات
• دمج قائمتين في قائمة واحدة ثم بناء جدول محوري بناءً عليها ، حيث ستكون الاختلافات مرئية بوضوح
• استخدم الوظيفة الإضافية Power Query لبرنامج Excel

لنأخذهم جميعًا بالترتيب.

الطريقة الأولى. مقارنة الجداول بوظيفة VLOOKUP

إذا لم تكن معتادًا تمامًا على هذه الميزة الرائعة ، فابحث أولاً هنا واقرأ أو شاهد مقطع فيديو تعليميًا عنها - وفر لنفسك بضع سنوات من العمر.

عادةً ما تُستخدم هذه الوظيفة لسحب البيانات من جدول إلى آخر عن طريق مطابقة بعض المعلمات الشائعة. في هذه الحالة ، سنستخدمها لدفع الأسعار القديمة إلى السعر الجديد:

هذه المنتجات ، التي ظهر خطأ # N / A في مقابلها ، ليست في القائمة القديمة ، أي تمت إضافتها. تغيرات الأسعار واضحة للعيان أيضًا.

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

سلبيات هناك أيضا. للبحث عن المنتجات المضافة إلى قائمة الأسعار الجديدة ، سيتعين عليك القيام بنفس الإجراء في الاتجاه المعاكس ، أي سحب الأسعار الجديدة إلى السعر القديم بمساعدة VLOOKUP. إذا تغيرت أحجام الجداول غدًا ، فسيتعين تعديل الصيغ. حسنًا ، وعلى الطاولات الكبيرة حقًا (> 100 ألف صف) ، ستتباطأ كل هذه السعادة بشكل لائق.

الطريقة 2: مقارنة الجداول باستخدام المحور

دعنا ننسخ جداولنا واحدة تحت الأخرى ، ونضيف عمودًا باسم قائمة الأسعار ، بحيث يمكنك لاحقًا فهم أي صف من القائمة:

الآن ، بناءً على الجدول الذي تم إنشاؤه ، سننشئ ملخصًا من خلاله إدراج - PivotTable (إدراج - جدول محوري). دعونا نرمي مجالا منتج إلى مجال الخطوط ، المجال السعر إلى منطقة العمود والميدان ЦENA في النطاق:

كما ترى ، سيقوم الجدول المحوري تلقائيًا بإنشاء قائمة عامة بجميع المنتجات من قوائم الأسعار القديمة والجديدة (بدون تكرار!) وفرز المنتجات أبجديًا. يمكنك أن ترى بوضوح المنتجات المضافة (ليس لديها السعر القديم) ، والمنتجات التي تمت إزالتها (ليس لديها السعر الجديد) وتغييرات الأسعار ، إن وجدت.

المجاميع الكلية في مثل هذا الجدول لا معنى لها ، ويمكن تعطيلها في علامة التبويب المُنشئ - الإجماليات الكلية - تعطيل للصفوف والأعمدة (التصميم - المجاميع الكلية).

إذا تغيرت الأسعار (ولكن ليس كمية البضائع!) ، يكفي تحديث الملخص الذي تم إنشاؤه ببساطة عن طريق النقر بزر الماوس الأيمن عليه - تحديث.

الايجابيات: هذا الأسلوب هو ترتيب من حيث الحجم أسرع مع الجداول الكبيرة من VLOOKUP. 

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

الطريقة الثالثة: مقارنة الجداول باستخدام Power Query

Power Query هي وظيفة إضافية مجانية لبرنامج Microsoft Excel تتيح لك تحميل البيانات إلى Excel من أي مصدر تقريبًا ثم تحويل هذه البيانات بأي طريقة مرغوبة. في Excel 2016 ، تم بالفعل تضمين هذه الوظيفة الإضافية بشكل افتراضي في علامة التبويب البيانات (بيانات)، وبالنسبة لبرنامج Excel 2010-2013 ، تحتاج إلى تنزيله بشكل منفصل عن موقع Microsoft على الويب وتثبيته - احصل على علامة تبويب جديدة استفسار الطاقة.

قبل تحميل قوائم الأسعار الخاصة بنا إلى Power Query ، يجب أولاً تحويلها إلى جداول ذكية. للقيام بذلك ، حدد النطاق مع البيانات واضغط على المجموعة على لوحة المفاتيح CTRL+T أو حدد علامة التبويب الموجودة على الشريط الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول). يمكن تصحيح أسماء الجداول التي تم إنشاؤها في علامة التبويب منشئ (سأترك المعيار الجدول 1 и الجدول 2، والتي يتم الحصول عليها بشكل افتراضي).

قم بتحميل السعر القديم في Power Query باستخدام الزر من الجدول / النطاق (من جدول / نطاق) من علامة التبويب البيانات (تاريخ) أو من علامة التبويب استفسار الطاقة (حسب إصدار Excel). بعد التحميل ، سنعود إلى Excel من Power Query بالأمر إغلاق وتحميل - إغلاق وتحميل ... (إغلاق وتحميل - إغلاق وتحميل إلى ...):

... وفي النافذة التي تظهر ثم حدد فقط قم بإنشاء اتصال (اتصال فقط).

كرر الأمر نفسه مع قائمة الأسعار الجديدة. 

لنقم الآن بإنشاء استعلام ثالث يجمع البيانات من الاستعلامين السابقين ويقارنها. للقيام بذلك ، حدد في Excel في علامة التبويب البيانات - إحضار البيانات - تجميع الطلبات - دمج (بيانات - إحضار بيانات - استعلامات دمج - دمج) أو اضغط على الزر دمج (دمج) علامة التبويب استفسار الطاقة.

في نافذة الانضمام ، حدد جداولنا في القوائم المنسدلة ، وحدد الأعمدة التي تحتوي على أسماء البضائع الموجودة فيها ، وفي الجزء السفلي ، قم بتعيين طريقة الانضمام - استكمال خارجي (خارجي كامل):

بعد النقر على OK يجب أن يظهر جدول من ثلاثة أعمدة ، حيث تحتاج في العمود الثالث إلى توسيع محتويات الجداول المتداخلة باستخدام السهم المزدوج في الرأس:

نتيجة لذلك ، نحصل على دمج البيانات من كلا الجدولين:

من الأفضل بالطبع إعادة تسمية أسماء الأعمدة في الرأس بالنقر نقرًا مزدوجًا فوق أسماء أكثر قابلية للفهم:

والآن الأكثر إثارة للاهتمام. انتقل إلى علامة التبويب إضافة عمود (إضافة عمود) وانقر على الزر العمود الشرطي (عمود شرطي). ثم في النافذة التي تفتح ، أدخل عدة شروط اختبار مع قيم الإخراج المقابلة لها:

يبقى أن تضغط OK وتحميل التقرير الناتج إلى Excel باستخدام نفس الزر إغلاق وتحميل (إغلاق وتحميل) علامة التبويب الصفحة الرئيسية (الرئيسية):

الجمال.

علاوة على ذلك ، إذا حدثت أي تغييرات في قوائم الأسعار في المستقبل (تمت إضافة الخطوط أو حذفها ، وتغير الأسعار ، وما إلى ذلك) ، فسيكون ذلك كافيًا فقط لتحديث طلباتنا باختصار لوحة المفاتيح. CTRL+قديم+F5 أو عن طريق الزر تحديث كافة (تحديث كافة) علامة التبويب البيانات (تاريخ).

الايجابيات: لعل أجمل وأنسب طريق للجميع. يعمل بذكاء مع الطاولات الكبيرة. لا يتطلب تعديلات يدوية عند تغيير حجم الجداول.

سلبيات: يتطلب الوظيفة الإضافية Power Query (في Excel 2010-2013) أو Excel 2016 ليتم تثبيتها. يجب عدم تغيير أسماء الأعمدة في بيانات المصدر ، وإلا فسنحصل على الخطأ "لم يتم العثور على العمود كذا وكذا!" عند محاولة تحديث الاستعلام.

• كيفية جمع البيانات من جميع ملفات Excel في مجلد معين باستخدام Power Query
• كيفية البحث عن التطابقات بين قائمتين في Excel
• دمج قائمتين بدون تكرارات