5 صيغ Excel متقدمة مفيدة يجب أن تعرفها

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

1. ابحث عن القيم في جدول البيانات الخاص بك: XLOOKUP

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

ملاحظة: حتى كتابة هذه السطور ، يتوفر XLOOKUP فقط لمشتركي Microsoft 365. بالنسبة لإصدارات Excel الأخرى ، تحقق من تركيبة INDEX و MATCH في القسم التالي.

صيغة الوظيفة هي:

XLOOKUP(lookup_value, lookup_range, return_range, not_found, match_mode, search_mode)

مطلوب فقط الحجج الثلاث الأولى. المدرجة أدناه وصف لكل وسيطة:

• Lookup_value: القيمة المراد البحث عنها.
• Lookup_range: النطاق الذي يحتوي على القيمة المراد البحث عنها.
• Return_range: النطاق الذي يحتوي على القيمة المراد إرجاعها.
• Not_found: النص الذي سيتم إرجاعه إذا لم يتم العثور على القيمة. “#N/A” هو الخيار الافتراضي إذا تم حذفه.
• Match_mode: نوع المطابقة باستخدام “0” لمطابقة تامة و “#N/A” لعدم العثور عليها (افتراضي إذا تم حذفها) ، و “1” للمطابقة التامة والعنصر الأصغر التالي إذا لم يتم العثور على أي منها ، “-1” للمطابقة التامة والعنصر الأكبر التالي إذا لم يتم العثور عليه ، أو “2” لمطابقة حرف البدل باستخدام علامة استفهام أو علامة النجمة أو التلدة.
• Search_mode: وضع البحث باستخدام “1” للبدء من العنصر الأول (الافتراضي إذا تم حذفه) ، أو “-1” للبدء عند العنصر الأخير ، أو “2” عندما يكون “lookup_range” بترتيب تصاعدي ، أو “-2” عندما “lookup_range” بترتيب تنازلي.
  على سبيل المثال ، نحن نبحث عن المبيعات للقطاع 2 ، لذلك نستخدم الصيغة التالية:

=XLOOKUP(2,A19:A24,D19:D24)

بتقسيم الصيغة ، 2 هي “lookup_value” ، و A19: A24 هي “lookup_range” ، و D19: D24 هي وسيطة “return_range”.

النتيجة ، 74000 ، هي القيمة المقابلة الصحيحة للقطاع 2.

إذا كنت تريد شيئًا أكثر ديناميكية ، حيث تبحث عن قيمة في خلية بدلاً من ثابت ، ثم تتلقى النتيجة المحدثة ، كلما قمت بتغيير القيمة في تلك الخلية ، راجع المثال التالي:

نحن نستبدل “lookup_value” 2 بالخلية F19:

=XLOOKUP(F19,A19:A24,D19:D24)

عندما ندخل 2 في الخلية F19 ، نتلقى النتيجة 74000 ، وإذا أدخلنا قيمة مختلفة ، مثل 5 ، فإننا نتلقى تلقائيًا النتيجة المحدثة ، وهي 61000.

يمكنك تضمين الوسيطة “not_found” حتى لا تضطر إلى إلقاء نظرة على رسالة خطأ إذا لم يتم العثور على تطابق. نحن نضيف “لا نرد” للحجة.

=XLOOKUP(F19,A19:A24,D19:D24,"No dice")

عندما يتم إدخال قيمة في الخلية F19 ليس لها تطابق ، سترى رسالتك بدلاً من “#NA”.

2. البحث عن القيمة البديلة في ورقة: INDEX و MATCH

بينما يمنحك XLOOKUP طريقة قوية للبحث عن القيم ، فهو متاح حاليًا فقط لمشتركي Microsoft 365 Excel. إذا كنت تستخدم إصدارًا مختلفًا من Excel ، فيمكنك استخدام INDEX و MATCH لفعل الشيء نفسه.

باستخدام الدالة INDEX ، يتم إرجاع القيمة الموجودة في الخلية بناءً على الموضع الذي تدخله في الصيغة. صيغة INDEX هي:

INDEX(range, row_number, column_number)

باستخدام دالة MATCH ، يتم إرجاع موضع القيمة التي تدخلها في الصيغة. صيغة MATCH هي:

MATCH(value, range, match_type)

لدمج هاتين الدالتين والصيغ الخاصة بهما ، ضع صيغة MATCH في صيغة INDEX كموقع البحث (“row_number” و “column_number”).

باستخدام نفس البيانات مثل مثال XLOOKUP أعلاه ، نريد أن نرى مبيعات القطاع التي ندخلها في الخلية F19. الصيغة هي:

=INDEX(D19:D24,MATCH(F19,A19:A24))

لكسر هذه الصيغة ، نبدأ بـ INDEX ووسيطة “النطاق” الخاصة بها ، وهي D19: D24. هذا هو النطاق الذي يحتوي على النتيجة التي نريدها.

تستخدم صيغة MATCH F19 كوسيطة “value” و A19: A24 كوسيطة “النطاق” التي تحتوي على تلك القيمة.

عندما ندخل 4 في الخلية F19 ، نتلقى النتيجة 75000 ، وهي صحيحة.

إذا أدخلنا قطاعًا مختلفًا في الخلية F19 ، مثل 6 ، يتم تحديث الصيغة تلقائيًا لإعطاء القيمة الصحيحة البالغة 58000.

3. أضف أو عد بالمعايير: SUMIF و SUMIFS و COUNTIF و COUNTIFS

تم تصميم الدالتين SUM و COUNT في Excel لإضافة الأرقام وعدد الخلايا ، لكنهما يقتصران على العمليات الحسابية البسيطة. تسمح لك الاختلافات في هذه الوظائف بالجمع أو العد باستخدام المعايير. على سبيل المثال ، يمكنك فقط جمع الأرقام الأكبر من 12 أو حساب الخلايا التي تحتوي على الاسم Bill.

باستخدام SUMIF و COUNTIF ، يمكنك إضافة أو حساب بشرط واحد ، وباستخدام SUMIFS و COUNTIFS ، يمكنك الإضافة أو العد بشروط متعددة. المدرجة أدناه هي بناء الجملة لكل:

SUMIF

SUMIF(range, criteria, sum_range)

COUNTIF

COUNTIF(range, condition)

SUMIFS

SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2,…)

COUNTIFS

COUNTIFS(condition_range1, condition1, condition_range2, condition2,…)

انظر أدناه للحصول على مثال لكل وظيفة.

لإضافة الأرقام الموجودة في النطاق الخاص بنا من B2 إلى B7 فقط للأرقام الأكبر من 12 ، نستخدم الدالة SUMIF وهذه الصيغة:

=SUMIF(B2:B7,">12")

تكون النتيجة 31 ، حيث أضافت الصيغة 16 و 15 ، الرقمان الوحيدان الأكبر من 12 في مجموعة البيانات.

في المثال التالي ، نحسب الخلايا ذات القيم الأقل من 12 في نفس النطاق ، من B2 إلى B7 ، بالصيغة التالية:

=COUNTIF(B2:B7,"<12")

نتيجة هذه الصيغة هي 4 ، حيث أن هذا هو عدد الخلايا في النطاق بقيم أقل من 12.

دعنا نتناول الأمر مع SUMIFS ونستخدم شرطين. لإضافة الأرقام في الخلايا من B2 إلى B7 فقط لأولئك الذين اشتركوا في رسالتنا الإخبارية (Y) في الخلايا من C2 إلى C7 ومدينتهم San Diego في الخلايا من D2 إلى D7 ، نستخدم الصيغة التالية:

=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")

نتيجة هذه الصيغة هي 19 ، حيث لا يوجد سوى عميلان لديهما Y للنشرة الإخبارية و San Diego للمدينة. يحتوي Bill Brown على 11 عنصرًا و Sue Smith لديها 8 عنصرًا بإجمالي 19 عنصرًا.

للحصول على مثال باستخدام وظيفة COUNTIFS ، نقوم بحساب عدد الخلايا بنفس الشرطين الموصوفين أعلاه. نحسب عدد العملاء الذين اشتركوا في النشرة الإخبارية (Y) ومدينتهم San Diego.

=COUNTIFS(C2:C7,"Y",D2:D7,"San Diego")

كما هو متوقع ، كانت النتيجة 2 لـ Bill Brown و Sue Smith ، الاشتراك في النشرة الإخبارية الوحيدة في San Diego.

4. بيانات الاختبار بشروط مختلفة: IFS

تعد وظيفة IF في Excel أداة قوية لاختبار البيانات مقابل الظروف. على سبيل المثال ، يمكنك استخدامه لعرض تقدير بالحرف للدرجة الرقمية للطالب أو “نعم” إذا جلب مندوب المبيعات عائدًا كافيًا لكسب مكافأته و “لا” إذا لم يفعل ذلك.

تكمن المشكلة في وظيفة IF في أنه لاختبار بياناتك مقابل معايير متعددة ، يجب عليك دمج جميع عبارات IF معًا. لعلاج هذا الارتباك الجماعي ، استخدم وظيفة IFS.

على غرار SUMIFS و COUNTIFS أعلاه ، يتيح لك IFS إضافة معايير متعددة إلى صيغتك بطريقة واضحة وسهلة القراءة.

بناء الجملة هو IFS(test1, if_test1_true, test2, if_test2_true, …) ، حيث يمكنك اختبار ما يصل إلى 127 معيارًا مختلفًا.

باستخدام أحد أمثلة السيناريوهات الخاصة بنا ، بدأنا ببساطة في الإشارة إلى المكافآت. إذا كان المبلغ في الخلية B2 أعلى من 20000 ، اعرض “نعم”. إذا لم يكن كذلك ، اعرض “لا”. تصبح الصيغة:

=IFS(B2>20000,"Yes",B2<20000,"No")

في الاختبار الأول ، إذا كانت القيمة في B2 أكبر من 20000 ، فإن النتيجة إذا كانت صحيحة هي “نعم”. في الاختبار الثاني ، إذا كانت القيمة في B2 أقل من 20000 ، فإن النتيجة إذا كانت صحيحة هي “لا”.

لحسن الحظ ، يتلقى مندوب المبيعات لدينا مكافأته. نظرًا لأن مبيعاته تبلغ 21000 ، يتم إدخال “نعم” في عمود المكافأة.

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

للحصول على مثال آخر من IFS ، لدينا قائمة المديرين المناوبين لكل يوم من أيام الأسبوع. عندما يتم عرض اليوم الحالي في الخلية D2 ، يتم عرض اسم المدير المقابل في الخلية E2. تصبح الصيغة:

=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)

يوضح هذا أنه إذا كانت القيمة في الخلية D2 هي يوم الإثنين ، فقم بعرض الاسم في الخلية B2 ، وإذا كانت القيمة في الخلية D2 هي الثلاثاء ، فقم بعرض الاسم في الخلية B3 ، وإذا كانت القيمة في الخلية D2 هي الأربعاء ، فقم بعرض الاسم في الخلية B3 ، وما إلى ذلك وهلم جرا.

أثناء تغيير يوم الأسبوع في الخلية D2 ، يتم عرض الاسم المقابل للمدير المناوب في الخلية E2.

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

5. التصفية باستخدام معايير متعددة: عامل التصفية

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

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

من المهم معرفة كيفية عمل وظيفة FILTER مع شرط واحد أولاً قبل إضافة معايير متعددة. على سبيل المثال ، يمكنك تصفية البيانات في الخلايا من A2 إلى C9 بواسطة Harold Hill الموجودة في B2 إلى B9. الصيغة لهذا هي:

=FILTER(A2:C9,B2:B9="Harold Hill")

لدينا نتيجتين لـ Harold Hill. الحق سهلة؟

دعونا نضيف المزيد من الشروط إلى الصيغة. تتم إضافة معايير متعددة باستخدام علامة النجمة (*) لـ AND وعلامة الجمع (+) لـ OR.

على سبيل المثال ، نقوم بتصفية كل من Harold Hill و Electronics باستخدام الصيغة التالية:

=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))

لتقسيم الصيغة ، A2: C9 هي مجموعة البيانات ، B2: B9 = “Harold Hill” هو الشرط الأول ، وتمثل العلامة النجمية AND ، و A2: A9 = “Electronics” هو الشرط الثاني.

لقد تلقينا نتيجة واحدة مع الفلتر الخاص بنا ، حيث يجب أن تتطابق الصيغة مع الشرطين ، Harold Hill و Electronics.

في مثال آخر ، يمكنك التصفية بحسب الملابس أو السيارات:

=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))

بتقسيم هذه الصيغة ، A2:C9 هي مجموعة البيانات ، A2: A9 = “Apparel” هو الشرط الأول ، وعلامة الجمع تمثل OR ، و A2: A9 = “Automotive” هو الشرط الثاني.

هذه المرة ، تلقينا نتيجتين ، حيث يجب أن تتطابق الصيغة مع أي من الشرطين – وليس كليهما.

أسئلة شائعة

س1: كيف يمكنني العثور على بنية صيغة الدالة مباشرة في Excel؟
الجواب: يوفر Excel أداة مضمنة للعثور على الوظيفة التي تحتاجها. باستخدامه ، يمكنك مشاهدة بناء جملة صيغة تلك الوظيفة.

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

س2: كيف يمكنني تصحيح أخطاء صيغة لا تعمل بشكل صحيح؟
الجواب: حدد الصيغة التي تعاني منها وانتقل إلى علامة التبويب “الصيغ”. اختر “تقييم الصيغة” في قسم تدقيق الصيغة على الشريط.

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

س3: كيف يمكنني معرفة سبب تلقيي خطأ صيغة؟
الجواب: عندما ترى خطأً في صيغة قمت بإنشائها ، يمكنك القيام بأمرين للحصول على مزيد من المعلومات. أولاً ، انقر فوق زر الخطأ الذي يظهر بجوار الخلية ، ثم حدد “Help on This Error” للحصول على مزيد من التفاصيل من Microsoft في الشريط الجانبي الذي يظهر.

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