صيغة لمقارنة البيانات في Excel. كيفية المقارنة بين عمودين في برنامج Excel - طرق مقارنة بيانات Excel

دعونا نقارن بين جدولين لهما نفس البنية تقريبًا. تختلف الجداول في القيم في الصفوف الفردية؛ حيث تظهر بعض أسماء الصفوف في جدول واحد، ولكنها قد لا تكون في جدول آخر.

فليكن على الأوراق ينايرو فبرايريوجد جدولان مع دوران الفترة للحسابات المقابلة.

وكما يتبين من الأشكال فإن الجداول تختلف:

1. وجود (غياب) خطوط (أسماء الحسابات). على سبيل المثال، في جدول على ورقة ينايرلا يوجد عدد 26 (انظر ملف المثال)، وفي الجدول الموجود على الورقة فبرايرالحساب 10 وحساباته الفرعية مفقودة.
2. قيم مختلفة في الخطوط. على سبيل المثال، وفقًا للحساب 57، لا يتطابق حجم التداول لشهري يناير وفبراير.

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

خيار بسيط لمقارنة جدولين

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

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

لتحديد أي من الجدولين هو الأكثر اكتمالا، تحتاج إلى الإجابة على سؤالين: ما هي الحسابات في جدول فبراير المفقودة في جدول يناير؟ وما هي الحسابات الموجودة في جدول يناير والمفقودة من جدول يناير؟

يمكن القيام بذلك باستخدام الصيغ (انظر العمود E): = IF(END(VLOOKUP(A7,January!$A$7:$A$81,1,0));"لا"،"نعم")و = IF(END(VLOOKUP(A7,فبراير!$A$7:$A$77,1,0));"لا"،"نعم")

سنقوم بمقارنة معدل دوران الحسابات باستخدام الصيغ: = IF(END(VLOOKUP($A7,فبراير!$A$7:$C77,2,0))),0,VLOOKUP($A7,فبراير!$A$7:$C77,2,0))-B7و = IF(END(VLOOKUP($A7,فبراير!$A$7:$C77,3,0))),0,VLOOKUP($A7,فبراير!$A$7:$C77,3,0))-C7

إذا لم يكن هناك صف مطابق، فإن الدالة VLOOKUP() ترجع الخطأ #N/A، الذي تتم معالجته من خلال مجموعة من الدالتين END() وIF()، مع استبدال الخطأ بـ 0 (إذا كان الصف مفقودًا) أو بقيمة من العمود المقابل.

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

خيار أكثر وضوحًا لمقارنة جدولين (ولكنه أكثر تعقيدًا)

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

للقيام بذلك تحتاج:

1. باستخدام = IFERROR(IFERROR(INDEX(January,MATCH(0,COUNTIF(A$4:$A4,January),0))), INDEX(February,MATCH(0,COUNTIF(A$4:$A4,February),0)))) ;"")إنشاء قائمة بالحسابات من كلا الجدولين في العمود أ (بدون تكرار)؛
2. باستخدام = IFERROR(INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") حيث القائمة هي قائمة الحسابات من كلا الجدولين (العمود أ)، الحسابات التي تم الحصول عليها في المرحلة السابقة؛
3. باستخدام الصيغة = IF(END(VLOOKUP($B5,يناير!$A$7:$C$81,2,0))),0,VLOOKUP($B5,يناير!$A$7:$C$81,2,0)) - IF( UND(VLOOKUP($B5، فبراير!$A$7:$C$77,2,0)))،0,VLOOKUP($B5، فبراير!$A$7:$C$77,2,0))مقارنة معدل دوران الحساب؛
4. استخدم اللون لتسليط الضوء على التناقضات، بالإضافة إلى تمييز الحسابات الموجودة في جدول واحد فقط (على سبيل المثال، في الشكل أعلاه، يتم تمييز الحسابات الموجودة في جدول يناير فقط باللون الأزرق، ويتم تمييز الحسابات الواردة فقط من جدول فبراير باللون الأصفر).

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

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

القائمة الأولى هي A2:B31، ويسمى هذا النطاق القائمة القديمة. القائمة الثانية هي D2:E31، ويسمى النطاق قائمة جديدة. تمت تسمية النطاقات باستخدام الأمر الصيغ الأسماء المعرفة تعيين اسم. ليس من الضروري تسمية النطاقات، لكن ذلك يجعل العمل معها أسهل.

لنبدأ بإضافة التنسيق الشرطي إلى القائمة القديمة.

1. حدد الخلايا في نطاق القائمة القديمة.
2. يختار.
3. في النافذة إنشاء قاعدة التنسيقحدد العنصر المسمى استخدم الصيغة
4. أدخل هذه الصيغة في حقل النافذة (الشكل 164.2): =COUNTIF(NewList;A2)=0.
5. انقر فوق الزر شكلوحدد التنسيق الذي سيتم تطبيقه عندما يكون الشرط صحيحًا. من الأفضل اختيار ألوان تعبئة مختلفة.
6. انقر فوق موافق.

الخلايا في النطاق قائمة جديدةاستخدم صيغة تنسيق شرطي مماثلة.

1. حدد الخلايا في نطاق قائمة جديدة.
2. يختار الصفحة الرئيسية التنسيق الشرطي إنشاء قاعدةلفتح مربع حوار إنشاء قاعدة التنسيق.
3. في النافذة إنشاء قاعدةتنسيق العنصر المحدد استخدم الصيغةلتحديد الخلايا التي سيتم تنسيقها.
4. أدخل هذه الصيغة في حقل النافذة: =COUNTIF(OldList;D2)=0 .
5. انقر فوق الزر شكلوقم بتعيين التنسيق الذي سيتم تطبيقه عندما يكون الشرط صحيحًا (لون تعبئة مختلف).
6. انقر فوق موافق.

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

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

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

هناك عدة طرق لمقارنة مساحات الجدول في إكسيل، ولكن يمكن تقسيمها جميعًا إلى ثلاث مجموعات كبيرة:

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

بالإضافة إلى ذلك، ينبغي القول أنه من المنطقي مقارنة مناطق الجدول فقط عندما يكون لها بنية مماثلة.

الطريقة الأولى: صيغة بسيطة

أسهل طريقة لمقارنة البيانات في جدولين هي استخدام صيغة مساواة بسيطة. إذا كانت البيانات متطابقة، فإنها تُرجع TRUE، وإذا لم تكن كذلك، تُرجع FALSE. يمكنك مقارنة كل من البيانات الرقمية والنصية. عيب هذه الطريقة هو أنه لا يمكن استخدامها إلا إذا كانت البيانات الموجودة في الجدول مرتبة أو مصنفة بنفس الطريقة ومتزامنة ولها عدد متساو من الصفوف. دعونا نرى كيفية استخدام هذه الطريقة عمليًا باستخدام مثال جدولين موضوعين على ورقة واحدة.

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

1. للقيام بذلك، نحتاج إلى عمود إضافي على الورقة. اكتب لافتة هناك «=» . ثم اضغط على الاسم الأول الذي تريد مقارنته في القائمة الأولى. نضع الرمز مرة أخرى «=» من لوحة المفاتيح. بعد ذلك، انقر فوق الخلية الأولى من العمود الذي نقوم بمقارنته في الجدول الثاني. والنتيجة هي تعبير من النوع التالي:

   

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

2. انقر على المفتاح يدخلللحصول على نتائج المقارنة. كما ترون، عند مقارنة الخلايا الأولى من كلا القائمتين، أشار البرنامج إلى المؤشر "حقيقي"، وهو ما يعني تطابق البيانات.



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

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



4. كما ترون، يعرض العمود الإضافي الآن جميع نتائج مقارنة البيانات في عمودين من المصفوفات الجدولية. في حالتنا، البيانات الموجودة في صف واحد فقط غير متطابقة. عند مقارنتها، أعطت الصيغة النتيجة "كذب". بالنسبة لجميع الخطوط الأخرى، كما نرى، أعطت صيغة المقارنة المؤشر "حقيقي".



5. بالإضافة إلى ذلك، من الممكن حساب عدد التناقضات باستخدام صيغة خاصة. للقيام بذلك، حدد عنصر الورقة حيث سيتم عرضه. ثم انقر على الأيقونة "إدراج وظيفة".



6. في النافذة معالجات الوظائففي مجموعة المشغل "رياضي"تسليط الضوء على الاسم منتج مصغر. انقر على الزر "نعم".



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

   SUMPRODUCT(array1,array2,...)

   في المجمل، يمكن استخدام عناوين تصل إلى 255 مصفوفة كوسيطات. لكن في حالتنا، سنستخدم صفيفين فقط كوسيطة واحدة.

   ضع المؤشر في الحقل "المصفوفة 1"وحدد نطاق البيانات المراد مقارنته في المنطقة الأولى على الورقة. بعد ذلك، ضع علامة في الميدان "لا يساوي" (<> ) وحدد النطاق المقارن للمنطقة الثانية. بعد ذلك، نلف التعبير الناتج بين قوسين، ونضع قبله علامتين «-» . وفي حالتنا حصلنا على التعبير التالي:

   --(أ2:أ7<>د2:د7)

   انقر على الزر "نعم".



8. يقوم المشغل بإجراء الحساب ويعرض النتيجة. كما ترون، في حالتنا النتيجة تساوي الرقم "1"أي أن هذا يعني أنه تم العثور على عدم تطابق واحد في القوائم المقارنة. ولو كانت القوائم متطابقة تماما لكانت النتيجة مساوية للعدد «0» .

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

B2=الورقة2!B2

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

الطريقة الثانية: تحديد مجموعات من الخلايا

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

الطريقة الثالثة: التنسيق الشرطي

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

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

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

وبالتالي، سيتم تسليط الضوء على تلك المؤشرات التي لا تتطابق.

الطريقة الرابعة: صيغة معقدة

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

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

COUNTIF (النطاق، المعيار)

دعوى "يتراوح"هو عنوان المصفوفة التي يتم فيها حساب القيم المطابقة.

دعوى "معيار"يحدد حالة المطابقة. في حالتنا، سيمثل إحداثيات خلايا محددة لمنطقة الجدول الأول.

وبطبيعة الحال، يمكن استخدام هذا التعبير بشكله الحالي لمقارنة المؤشرات الجدولية، ولكن من الممكن تحسينه.

دعونا نتأكد من أن تلك القيم الموجودة في الجدول الثاني، ولكن ليس في الجدول الأول، يتم عرضها في قائمة منفصلة.

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



2. يتم فتح نافذة وسيطات الوظيفة لو. كما ترون، الحقل الأول من النافذة مملوء بالفعل بقيمة عامل التشغيل كونتيف. ولكننا بحاجة إلى إضافة شيء آخر إلى هذا المجال. ضع المؤشر هناك وأضف إلى التعبير الموجود «=0» بدون اقتباسات.

   بعد ذلك ننتقل إلى الميدان "القيمة إذا كانت صحيحة". هنا سوف نستخدم وظيفة متداخلة أخرى - خط. أدخل الكلمة "خط"بدون علامتي الاقتباس، ثم افتح الأقواس وحدد إحداثيات الخلية الأولى مع الاسم الأخير في الجدول الثاني، وبعد ذلك نغلق الأقواس. وتحديداً في حالتنا الميدانية "القيمة إذا كانت صحيحة"تم الحصول على التعبير التالي:

   الصف (D2)

   الآن المشغل خطسوف تقرير الوظائف لورقم السطر الذي يوجد فيه اللقب المحدد، وفي حالة استيفاء الشرط المحدد في الحقل الأول، يتم استخدام الوظيفة لوسيتم إخراج هذا الرقم إلى الخلية. انقر على الزر "نعم".



3. كما نرى، يتم عرض النتيجة الأولى كما "كذب". وهذا يعني أن القيمة لا تستوفي شروط المشغل لو. أي أن اللقب الأول موجود في كلتا القائمتين.



4. باستخدام علامة التعبئة، نقوم بنسخ تعبير عامل التشغيل بالطريقة المعتادة لوللعمود بأكمله. كما ترون، بالنسبة للموضعين الموجودين في الجدول الثاني ولكن ليس في الأول، تنتج الصيغة أرقام الصفوف.



5. نتراجع من منطقة الجدول إلى اليمين ونملأ العمود بالأرقام بالترتيب بدءًا من 1 . يجب أن يتطابق عدد الأرقام مع عدد الصفوف في الجدول الثاني الذي تتم مقارنته. يمكنك أيضًا استخدام علامة التعبئة لتسريع عملية الترقيم.



6. بعد ذلك، حدد الخلية الأولى على يمين العمود الذي يحتوي على أرقام وانقر على الأيقونة "إدراج وظيفة".



7. يفتح معالج الوظائف. اذهب إلى الفئة "إحصائية"وحدد اسمًا "الأقل". انقر على الزر "نعم".



8. وظيفة الأقل، التي تم فتح نافذة الوسيطة الخاصة بها، تهدف إلى عرض أصغر قيمة محددة بواسطة الحساب.

   في الميدان "مصفوفة"يجب عليك تحديد إحداثيات نطاق العمود الإضافي "عدد المباريات"، والتي قمنا بتحويلها مسبقًا باستخدام الدالة لو. نحن نجعل جميع الروابط مطلقة.

   في الميدان "ك"يشير إلى أصغر قيمة ليتم إخراجها. نشير هنا إلى إحداثيات الخلية الأولى من العمود المرقّم الذي أضفناه مؤخرًا. نترك العنوان النسبي. انقر على الزر "نعم".



9. يقوم المشغل بإخراج النتيجة - رقم 3 . وهو أصغر ترقيم للصفوف غير المتطابقة في صفائف الجدول. باستخدام علامة التعبئة، انسخ الصيغة إلى الأسفل.



10. الآن، بعد معرفة أرقام صفوف العناصر غير المتطابقة، يمكننا إدراج قيمها في الخلية باستخدام الدالة فِهرِس. حدد العنصر الأول من الورقة التي تحتوي على الصيغة الأقل. بعد ذلك، انتقل إلى شريط الصيغة وقبل الاسم "الأقل"أضف الاسم "فِهرِس"بدون علامات الاقتباس، افتح القوس على الفور وضع فاصلة منقوطة ( ; ). ثم حدد الاسم في شريط الصيغة "فِهرِس"وانقر على الأيقونة "إدراج وظيفة".



11. بعد ذلك، يتم فتح نافذة صغيرة تحتاج فيها إلى تحديد نوع المرجع الذي يجب أن تحتوي عليه الوظيفة فِهرِسأو مصممة للعمل مع المصفوفات. نحن بحاجة إلى خيار ثان. يتم تثبيته بشكل افتراضي، لذلك في هذه النافذة نضغط ببساطة على الزر "نعم".



12. يتم فتح نافذة وسيطات الوظيفة فِهرِس. تم تصميم هذا العامل لعرض القيمة الموجودة في مصفوفة محددة في سطر محدد.

    كما نرى الميدان "رقم السطر"مليئة بالفعل بقيم الوظيفة الأقل. من القيمة الموجودة بالفعل، يجب عليك طرح الفرق بين ترقيم ورقة Excel والترقيم الداخلي لمنطقة الجدول. كما ترون، لدينا فقط رأس فوق قيم الجدول. يعني الفرق سطر واحد لذلك، نضيف في هذا المجال "رقم السطر"معنى "-1"بدون اقتباسات.

    في الميدان "مصفوفة"تشير إلى عنوان نطاق قيم الجدول الثاني. وفي نفس الوقت نجعل جميع الإحداثيات مطلقة، أي نضع أمامها علامة الدولار بالطريقة التي وصفناها سابقاً.

    انقر على الزر "نعم".



13. بعد عرض النتيجة على الشاشة، استخدم علامة التعبئة لسحب الوظيفة لأسفل حتى نهاية العمود. كما ترون، يتم عرض كلا اللقبين، الموجودين في الجدول الثاني ولكن ليس في الأول، في نطاق منفصل.

الطريقة الخامسة: مقارنة المصفوفات في مصنفات مختلفة

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

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

سؤال من مستخدم

مرحبًا!

لدي مهمة واحدة، ولليوم الثالث الآن كنت أتعب عقلي - لا أعرف كيفية إكمالها. يوجد جدولين (حوالي 500-600 صف في كل منهما)، عليك أن تأخذ العمود الذي يحمل اسم المنتج من جدول واحد وتقارنه باسم المنتج من الآخر، وإذا كانت المنتجات متطابقة، انسخ و الصق القيمة من الجدول 2 في الجدول 1. تم شرحها بشكل مرتبك، ولكن أعتقد أنك ستفهم المهمة من الصورة ( تقريبا. : تم قطع الصورة من قبل الرقابة، فهي لا تزال معلومات شخصية).

شكرا لكم مقدما. أندريه، موسكو.

يوم جيد للجميع!

يشير ما وصفته إلى المهام الشائعة إلى حد ما والتي يسهل حلها نسبيًا وسريعًا باستخدام Excel. كل ما عليك فعله هو وضع الجدولين في البرنامج واستخدام وظيفة VLOOKUP. المزيد عن عملها أدناه ...

مثال على العمل مع وظيفة VLOOKUP

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

كيف نفعل ذلك...

ضع مؤشر الماوس في خلية ب2- أي في الخلية الأولى من العمود حيث ليس لدينا قيمة واكتب الصيغة:

=VLOOKUP(A2,$E$1:$F$7,2,FALSE)

أ2- القيمة من العمود الأول من الجدول الأول (ما سنبحث عنه في العمود الأول من الجدول الثاني)؛

$E$1:$F$7- جدول ثانٍ محدد بالكامل (نريد فيه البحث عن شيء ما ونسخه). انتبه إلى علامة "$" - فهي ضرورية حتى لا تتغير خلايا الجدول الثاني المحدد عند نسخ الصيغة؛

2 - رقم العمود الذي نسخنا القيمة منه (لاحظ أن الجدول الثاني الذي اخترناه يحتوي على عمودين فقط. إذا كان يحتوي على 3 أعمدة، فيمكن نسخ القيمة من العمود الثاني أو الثالث)؛

كذب- نحن نبحث عن تطابق تام (وإلا سيتم استبدال أول تطابق مماثل، وهو ما لا يناسبنا بوضوح).

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

لتعيين السعر لأسماء المنتجات الأخرى، ما عليك سوى توسيع (نسخ) الصيغة إلى خلايا أخرى. المثال أدناه.

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

ملحوظة: يجب أن أقول أن وظيفة VLOOKUP تتطلب الكثير من موارد الكمبيوتر. في بعض الحالات، مع وجود مستند كبير جدًا، قد يستغرق الأمر وقتًا طويلاً لمقارنة الجداول. في هذه الحالات، يجدر النظر في الصيغ الأخرى، أو الحلول المختلفة تماما (كل حالة على حدة).

هذا كل شيء، حظا سعيدا!

لنفترض أنك تريد مقارنة إصدارات أحد المصنفات، أو تحليل المصنف بحثًا عن المشكلات أو عدم الاتساق، أو الاطلاع على الارتباطات بين المصنفات أو أوراق العمل. إذا تم تثبيت Microsoft Office 365 أو Office Professional Plus 2013 على جهاز الكمبيوتر الخاص بك، فستتوفر الوظيفة الإضافية "الاستعلام عن جدول البيانات" في Excel.

يمكنك استخدام الأوامر الموجودة في علامة التبويب "استعلام" للقيام بكل هذه المهام والمزيد. تحتوي علامة التبويب "استعلام" الموجودة على شريط Excel على أزرار للأوامر الموضحة أدناه.

إذا كنت لا ترى استفسرفي شريط Excel، راجع تشغيل الوظيفة الإضافية للاستعلام عن جدول البيانات.

قارن بين مصنفين

ال مقارنة الملفاتيتيح لك الأمر رؤية الاختلافات، خلية بعد خلية، بين مصنفين. يجب أن يكون لديك مصنفان مفتوحان في Excel لتشغيل هذا الأمر.

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

ال مقارنة الملفاتيستخدم الأمر Microsoft Spreadsheet Compare لمقارنة الملفين. في نظام التشغيل Windows 8، يمكنك بدء مقارنة جداول البيانات خارج Excel بالنقر فوق مقارنة جدول البياناتعلى تطبيقاتشاشة. في نظام التشغيل Windows 7، انقر فوق Windows يبدأزر ثم > جميع البرامج > مايكروسوفت أوفيس 2013 > أدوات مكتب 2013 > مقارنة جداول البيانات 2013.

لمعرفة المزيد حول مقارنة جداول البيانات ومقارنة الملفات، اقرأ مقارنة إصدارين من المصنف.

تحليل المصنف

ال تحليل المصنفيقوم الأمر بإنشاء تقرير تفاعلي يعرض معلومات تفصيلية حول المصنف وبنيته وصيغه وخلاياه ونطاقاته وتحذيراته. تُظهر الصورة هنا مصنفًا بسيطًا للغاية يحتوي على صيغتين واتصالات بيانات بقاعدة بيانات Access وملف نصي.

إظهار روابط المصنف

يمكن أن تصبح المصنفات المتصلة بالمصنفات الأخرى من خلال مراجع الخلايا مربكة. استخدم لإنشاء خريطة رسومية تفاعلية لتبعيات المصنف التي تم إنشاؤها بواسطة الاتصالات (الارتباطات) بين الملفات. يمكن أن تتضمن أنواع الارتباطات الموجودة في الرسم التخطيطي مصنفات أخرى وقواعد بيانات Access وملفات نصية وصفحات HTML وقواعد بيانات SQL Server ومصادر بيانات أخرى. في الرسم التخطيطي للعلاقة، يمكنك تحديد العناصر والعثور على مزيد من المعلومات حولها، وسحب خطوط الاتصال لتغيير شكل الرسم التخطيطي.

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

إظهار روابط أوراق العمل

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

يوضح هذا الرسم التخطيطي العلاقات بين أوراق العمل في أربعة مصنفات مختلفة، مع التبعيات بين أوراق العمل في نفس المصنف بالإضافة إلى الروابط بين أوراق العمل في مصنفات مختلفة. عندما تضع المؤشر فوق عقدة في الرسم التخطيطي، مثل ورقة العمل المسماة "الغرب" في الرسم التخطيطي، يظهر بالون يحتوي على معلومات.

إظهار علاقات الخلايا

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

يُظهر هذا الرسم التخطيطي مستويين من علاقات الخلايا للخلية A10 في الورقة5 في Book1.xlsx. تعتمد هذه الخلية على الخلية C6 في الورقة 1 في مصنف آخر، Book2.xlsx. تعتبر هذه الخلية سابقة لعدة خلايا في أوراق عمل أخرى في نفس الملف.

لمعرفة المزيد حول عرض علاقات الخلايا، اقرأ الاطلاع على الروابط بين الخلايا.

تنظيف تنسيق الخلايا الزائدة

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

إدارة كلمات المرور

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