ورقة الغش في صيغ Excel: دليل متقدم

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

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

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

يرجى تنزيل ورقة الغش وطباعتها والاحتفاظ بها في متناول اليد.

حسنًا ، لنبدأ.

Excel-formulas-cheat-sheet-advanced-1 تنزيل

أولاً ، دعنا ندخل في صيغ الصفيف.

جدول المحتويات

• صيغ الصفيف
  • 1. وظيفة فريدة
  • 2. SORT وظيفة
  • 3. وظيفة التصفية
• توزيع وظائف Excel بشكل عشوائي
  • 1. وظيفة RANDBETWEEN
  • 2. RANDARRAY وظيفة
• صيغ التنبؤ المتقدمة في Microsoft Excel
  • 1. وظيفة FORECAST.ETS
  • 2. وظيفة الاتجاه
• الصيغ الإحصائية المتقدمة
  • 1. وظيفة النسبة المئوية
  • 2. الدالة الرباعية
• صيغ تحليل ومعالجة البيانات المتقدمة
  • 1. وظيفة TRANSPOSE
  • 2. وظيفة التردد
  • 3. وظيفة GETPIVOTDATA
• معالجة متقدمة للأخطاء
• صيغ البحث المتقدمة
  • 1. وظيفة XLOOKUP
  • 2. XMATCH وظيفة
• افكار اخيرة

صيغ الصفيف

توضح لك ورقة الغش الخاصة بالمبتدئين كيفية فرز بياناتك وتصفيتها يدويًا. يقوم المستخدمون المتقدمون بهذا برمجيًا باستخدام صيغ الصفيف.

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

1. فريد

2. نوع

3. منقي

تتوفر بعض هذه الوظائف فقط في أحدث إصدارات Microsoft Excel.

1. وظيفة فريدة

تقبل الدالة UNIQUE نطاقًا وتُرجع قائمة بالقيم الفريدة.

افترض أن لديك بيانات مبيعات لعناصر الملابس. للعثور على العناصر الفريدة في العمود A ، استخدم هذه الصيغة:

= فريد (A2: A6)

2. SORT وظيفة

تقوم الدالة SORT بفرز محتويات النطاق. الصيغة هي:

SORT (مصفوفة ، [فهرس_فرز] ، [ترتيب_فرز] ، [حسب_كول])

• المصفوفة : نطاق القيم المطلوب الفرز.

• Sort_index : العمود المطلوب الفرز (1 افتراضيًا)

• Sort_order : 1 لتصاعدي (افتراضي) أو 2 تنازلي).

• by_col : صواب للفرز حسب العمود (افتراضي) أو FALSE للفرز حسب الصف.

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

لفرز البيانات النموذجية حسب العمود الأول ، استخدم هذه الصيغة:

= SORT (A2: C6)

3. وظيفة التصفية

تتيح لك وظيفة FILTER تصفية النطاق وفقًا لحالة معينة. هذا هو بناء الجملة:

= FILTER (مصفوفة ، تتضمن ، [if_empty])

• المصفوفة : النطاق المطلوب تصفيته.

• include : الشرط الذي يحدد القيم المراد تصفيتها.

• if_empty : يحدد ما سيتم إرجاعه إذا لم تكن هناك قيم تفي بمعايير التصفية (الافتراضي هو "").

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

= عامل التصفية (A2: C6، C2: C6> 15)

توزيع وظائف Excel بشكل عشوائي

يوضح غشنا المتوسط ​​كيفية استخدام وظيفة RAND التي تنتج رقمًا عشوائيًا بين 0 و 1.

يعرف مستخدمو Excel المتقدمون كيفية استخدام وظائف التوزيع العشوائي لإنشاء بيانات نموذجية بسرعة.

1. وظيفة RANDBETWEEN

تعتبر وظيفة RANDBETWEEN أكثر مرونة من RAND لأنه يمكنك تحديد الأرقام السفلية والعلوية كشيء آخر غير 0 و 1.

لإنشاء بيانات بأرقام بين 1 و 100 ، أدخل هذه الصيغة في الخلية A1:

= RANDBETWEEN (1 ، 100)

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

2. RANDARRAY وظيفة

قد تعتقد أنه سيكون من الجيد تجنب النسخة اليدوية لوظيفة RANDBETWEEN. للحصول على تقدم فائق ، يمكنك استخدام وظيفة RANDARRAY الجديدة في أحدث إصدار من Microsoft Excel.

الصيغة هي:

RANDARRAY ([الصفوف] ، [الأعمدة] ، [الحد الأدنى] ، [الحد الأقصى] ، [العدد الكامل])

• الصفوف : عدد الصفوف

• الأعمدة : عدد الأعمدة

• الحد الأدنى : أقل عدد

• الحد الأقصى : أعلى رقم

• العدد الصحيح : القيمة الافتراضية هي TRUE ، وإلا تستخدم الأرقام العشرية.

لإنشاء أرقام عشوائية بين 1 و 100 عبر ستة صفوف وتسعة أعمدة ، استخدم هذه الصيغة:

= RANDARRAY (6، 9، 1، 100، صحيح)

صيغ التنبؤ المتقدمة في Microsoft Excel

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

1. وظيفة FORECAST.ETS

تم استبدال وظيفة FORECAST الأقدم بمجموعة من الوظائف الأحدث في Excel 2016.

تختار الوظيفة بناءً على نموذج التنبؤ المحدد الذي تريده. على سبيل المثال ، تستخدم الدالة FORECAST.ETS خوارزمية التجانس الأسي .

الصيغة هي:

FORECAST.ETS (target_date والقيم والجدول الزمني)

• target_date : التاريخ الذي تريد قيمة محسوبة له.

• القيم : البيانات التاريخية.

• التسلسل الزمني : مجموعة من التواريخ

افترض أن لديك تواريخ من 1 إلى 5 يناير في العمود A ومبالغ المبيعات في العمود B. ستتنبأ هذه الصيغة بمبلغ المبيعات التالي:

= FORECAST.ETS ("6 يناير 2023"، B2: B6، A2: A6)

2. وظيفة الاتجاه

تعرض الدالة TREND مجموعة من القيم بناءً على طريقة المربعات الصغرى. تقوم بإرجاع مصفوفة. الصيغة هي:

TREND (known_y، [known_x]، [new_x]، [const])

• known_y : نطاق قيم y

• known_x : نطاق قيم x

• new_x : نطاق القيم المحسوبة

غالبًا ما تكون known_y هي نقاط البيانات ، بينما known_x هي التواريخ.

باستخدام نفس البيانات الموجودة في المثال السابق ، يمكنك إدخال الصيغة أدناه في الخلية C2. سيتم إنشاء مجموعة من القيم.

= TREND (B2: B6، A2: A6)

الصيغ الإحصائية المتقدمة

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

1. وظيفة النسبة المئوية

تحسب هذه الوظيفة النسبة المئوية لنقاط البيانات التي تقع تحت قيمة معينة. الصيغة هي:

PERCENTILE.INC (مجموعة ، ك)

• المصفوفة : نطاق الخلايا

• ك : النسبة المئوية من 0 إلى 1

افترض أنك تريد حساب النسبة المئوية السبعين للبيانات في العمود ب. استخدم هذه الصيغة:

= PERCENTILE.INC (B2: B6، 0.7)

2. الدالة الرباعية

هذه الوظيفة هي تباين في دالة النسبة المئوية ولكنها تستخدم الأرباع لتقسيم البيانات. هذا هو بناء الجملة:

QUARTILE.INC (صفيف ، رباعي)

• المصفوفة : نطاق البيانات

• ربع : 1 للشريحة المئوية 25 ، 2 للشريحة الخمسين ، 3 للشريحة الخامسة والسبعين ، و 4 للحد الأقصى.

ستحسب الصيغة أدناه الربع الأول من البيانات في العمود A.

= QUARTILE.INC (A2: A5، 1)

صيغ تحليل ومعالجة البيانات المتقدمة

تتيح لك العديد من الوظائف المتقدمة تبديل تنسيق البيانات وتحليل توزيعات التردد واستخراج البيانات من الجداول المحورية.

1. تبديل موضع

2. تكرار

3. الحصول على البيانات

1. وظيفة TRANSPOSE

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

افترض أن لديك العناصر "T-Shirt" و "Hoodie" و "Jeans" في الخلايا A2 و A3 و A4. تريد تحويلها إلى عناوين أعمدة. ترجع هذه الدالة القيم في صف واحد:

= نقل (A2: A4)

2. وظيفة التردد

تحسب هذه الوظيفة التوزيع التكراري للقيم ضمن مجموعة بيانات. هذا هو بناء الجملة:

التردد (data_array، bins_array)

• data_array : مجموعة من القيم.

• bins_array : فترات الاستخدام.

افترض أن لديك بيانات مبيعات في العمود B ، وتريد تحليل التوزيع التكراري للقيم بناءً على عدد المبالغ:

• أقل من 20.

• من 20 إلى 80.

• فوق 80.

يمثل ذلك ثلاثة صناديق ويمكن حسابه بهذه الصيغة:

= FREQUENCY (A2: A6، {20،80})

لمزيد من المعلومات حول توزيعات التردد في Excel ، شاهد هذا الفيديو:

3. وظيفة GETPIVOTDATA

تتيح لك هذه الوظيفة استخراج معلومات ملخصة من الجداول المحورية. هذا هو بناء الجملة:

GETPIVOTDATA (data_field، pivot_table، [field1، item1]، [field2، item2]،…)

• data_field : حقل البيانات أو القيمة التي تريد استردادها من الجدول المحوري.

• pivot_table : مرجع للجدول المحوري.

• field1 ، item1 ، إلخ .: أزواج الحقل / العناصر المراد التصفية من أجلها.

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

= GETPIVOTDATA ("المبيعات" ، A1 ، "اللون" ، "الأحمر")

معالجة متقدمة للأخطاء

حتى أبسط صيغ Excel يمكن أن ينتج عنها أخطاء. يجب أن يعرف المستخدمون المتوسطون كيفية استخدام ISERROR لمعالجة الأخطاء. يجب أن يكون المستخدمون المتقدمون على دراية بالدالة ERROR.TYPE لتحديد الخطأ.

تساعد الدالة ERROR.TYPE في تحديد نوع الخطأ المحدد داخل خلية أو صيغة.

تقوم بإرجاع قيمة رقمية مطابقة لأنواع أخطاء مختلفة ، مثل # N / A ، و # VALUE! ، و # REF !، والمزيد.

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

= ERROR.TYPE (A1)

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

1. #باطل! (لم يتم العثور على خلية مشتركة في نطاق)

2. # DIV / 0! ( قسمة على صفر أو خلية فارغة)

3. #قيمة! (نوع بيانات غير مناسب أو وسيطة في صيغة)

4. # REF! (تم حذف خلية مرجعية أو يوجد مرجع معاد )

5. #اسم؟ (لا يتعرف Excel على الوظيفة أو النطاق)

6. #NUM! (قيمة عددية غير صالحة)

7. # N / A (لا يمكن العثور على القيمة)

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

= IF (ISERROR (C2)، IF (ERROR.TYPE (C2) = 2، "قسمة على صفر خطأ" ، IF (ERROR.TYPE (C2) = 3 ، "خطأ قيمة غير صالحة" ، IF (ERROR.TYPE (C2) ) = 7 ، "خطأ غير موجود في القيمة" ، "خطأ آخر"))) ، C2)

صيغ البحث المتقدمة

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

1. XLOOKUP

2. XMATCH

1. وظيفة XLOOKUP

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

إنه يوفر تنوعًا أكثر من وظائف البحث الأبسط مثل VLOOKUP . هذا هو بناء الجملة:

XLOOKUP (lookup_value ، lookup_array ، return_array ، [match_mode] ، [search_mode] ، [if_not_found])

• lookup_value : القيمة التي تريد البحث عنها.

• lookup_array : نطاق البحث.

• return_array : النطاق الذي سيُظهر القيمة المقابلة.

• match_mode : المطابقة التامة (0) ، التالي الأصغر (1) ، التالي الأكبر (-1) ، أو مطابقة أحرف البدل (2).

• search_mode : -1 للأعلى إلى الأسفل ، 1 من الأسفل إلى الأعلى ، أو 2 للبحث الثنائي.

• if_not_found : يعيّن القيمة المراد إرجاعها إذا لم يتم العثور على تطابق.

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

= XLOOKUP ("Hoodie"، A2: A6، B2: B6)

2. XMATCH وظيفة

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

XMATCH (lookup_value ، lookup_array ، [match_type] ، [search_mode])

• lookup_value : القيمة التي تريد البحث عنها.

• lookup_array : النطاق الذي تريد البحث فيه.

• match_type : المطابقة التامة (0) ، التالي الأصغر (-1) ، التالي الأكبر (1).

• search_mode : البحث الثنائي (1) أو البحث الخطي (2).

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

= XMATCH ("أصفر"، B2: B6، 0)

افكار اخيرة

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

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

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

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