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

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

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

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

الآن ، تأكد من القراءة ، ولكن أيضًا اطبع ورقة الغش أدناه واحفظها!

هيا بنا نبدأ!

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

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

• صيغ الرياضيات الوسيطة
  • المنطق الشرطي
• الصيغ الإحصائية الوسيطة
  • 1. MINA و MAXA
  • 2. كونتا و COUNTIF
• صيغ Excel للتحليل المالي
  • 1. وظيفة المنتج
  • 2. وظائف QUOTIENT و LOG
  • وظائف لسيناريوهات الاستثمار
• صيغ التاريخ والوقت الوسيطة في Excel
  • 1. وظائف الشهر الوسيط
  • 2. وظائف الأسبوع المتوسط
  • 3. وظائف الوقت المتوسط
• وظائف Excel للجبر الشرطي والمنطقي
  • 1. وظيفة IF
  • 2. ووظيفة
  • 3. أو وظيفة
  • 4. لا وظيفة
• الرجوع للخلايا
  • 1. وظيفة غير مباشرة
  • 2. وظيفة الفهرس
  • 3. وظيفة OFFSET
• صيغ نصية وسيطة
  • 1. وظيفة TEXTJOIN
  • 2. استبدال وظيفة
  • 3. وظيفة الاستبدال
• صيغ البحث الوسيطة
• صيغ لمعالجة أخطاء Microsoft Excel
  • 1. وظيفة IFERROR
  • 2. وظيفة IFNA
• اختصارات الصيغة والوظيفة
• تنسيق الخلايا الوسيطة
• افكار اخيرة

صيغ الرياضيات الوسيطة

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

• تُرجع ABS () القيمة المطلقة لرقم.

• تُرجع الدالة SQRT () الجذر التربيعي لرقم

• توفر RAND () عددًا عشوائيًا بين 0 و 1

المنطق الشرطي

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

• SUMIFS (مجموع_ترانج ، نطاق_المعايير 1 ، معايير 1 ، [...])

• COUNTIFS (فئة_المعايير 1 ، معايير 1 ، [نطاق_المعايير 2 ، معايير 2] ، ...)

• AVERAGEIFS (نطاق_المعايير 1 ، معايير 1 ، [نطاق_المعايير 2 ، معايير 2] ، ...)

تقوم الوظائف بتقييم المعايير المقدمة وتنتج قيمة منطقية لـ TRUE أو FALSE.

لنفترض أن لديك بيانات المبيعات التالية في الأعمدة A و B و C:

لحساب مجموع مبيعات القمصان الحمراء ، استخدم صيغة SUMIFS:

= SUMIFS (C2: C6، A2: A6، “T-Shirt”، B2: B6، “Red”)

لحساب عدد الصفوف ذات القمصان الحمراء ، استخدم صيغة COUNTIFS:

= COUNTIFS (C2: C6، A2: A6، “T-Shirt”، B2: B6، “Red”)

لحساب متوسط ​​مبيعات القمصان الحمراء ، استخدم صيغة AVERAGEIFS:

= AVERAGEIFS (C2: C6، A2: A6، “T-Shirt”، B2: B6، “Red”)

الصيغ الإحصائية الوسيطة

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

• مينا

• ماكسا

• كونتا

• كونتيف

1. MINA و MAXA

تتجاهل الدالتان MIN و MAX الأكثر استخدامًا القيم النصية.

تراعي الدالتان MINA و MAXA البديلتان كلاً من النصوص والأرقام عند البحث عن القيم الأعلى أو الأدنى. يتم تقييم القيم النصية كما لو كانت صفرًا.

تظهر الاختلافات في الصورة أدناه. يقوم الصف الأول بتقييم الحد الأدنى للقيمة وهو 10 ، بينما يتم تقييم الصف الثاني على أنه 0 نظرًا لوجود قيمة نصية.

2. كونتا و COUNTIF

تُستخدم الدالة COUNTA لحساب عدد الخلايا غير الفارغة في النطاق.

تعد الدالة COUNTIF أقل تحديدًا حيث يتم استخدامها لحساب عدد الخلايا داخل نطاق يلبي شرطًا أو معايير معينة.

يمكنك أيضًا استخدام هذه الوظائف لحساب عدد القيم المميزة في عمود. يوضح هذا الفيديو كيفية القيام بذلك.

صيغ Excel للتحليل المالي

هناك العديد من الصيغ التي قد ترغب في استخدامها عند إجراء التحليل المالي مثل التنبؤ بالاستثمارات:

• المنتج (الرقم 1 ، [الرقم 2 ...])

• QUOTIENT (البسط والمقام)

• LOG (رقم ، [الأساس])

1. وظيفة المنتج

لنفترض أنك تريد حساب القيمة المستقبلية لاستثمار أولي قدره 1000 دولار (الخلية B1) بمعدل فائدة سنوي قدره 4٪ (الخلية B2) بعد خمس سنوات (الخلية B3).

استخدم الدالة PRODUCT لحساب القيمة المستقبلية بهذه الصيغة:

= B1 * PRODUCT (1 + B2) ^ B3

ستعيد هذه الصيغة القيمة المستقبلية لاستثمارك.

2. وظائف QUOTIENT و LOG

تتمثل الخطوة التالية في حساب عدد السنوات التي سيستغرقها الاستثمار حتى يتضاعف في القيمة بسعر الفائدة المحدد.

استخدم الدالة QUOTIENT جنبًا إلى جنب مع وظيفة LOG لحساب ذلك على النحو التالي:

= QUOTIENT (LOG (2) / LOG (1 + B2)، 1)

ستُرجع هذه الصيغة 17 مع بيانات العينة ، مما يشير إلى أن الاستثمار سيستغرق 17 عامًا لمضاعفة القيمة بمعدل فائدة سنوي يبلغ 4٪.

تُظهر هذه الصورة الصيغ في ورقة عمل Excel :

وظائف لسيناريوهات الاستثمار

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

• NPV - ستحسب الدالة NPV (صافي القيمة الحالية) صافي القيمة الحالية للاستثمار بناءً على سلسلة من التدفقات النقدية المستقبلية بناءً على معدل الخصم.

• ACCRINT - تحسب الدالة ACCRINT الفائدة المتراكمة للأوراق المالية التي تُدفع فائدة دورية. هذا مفيد لتحديد الفائدة المكتسبة على ورقة مالية منذ تاريخ الدفع الأخير حتى تاريخ تسوية معين.

• INTRATE - تحسب الدالة INTRATE معدل الفائدة لورقة مالية مستثمرة بالكامل.

• PMT - تحسب الدالة PMT إجمالي المدفوعات لسند الدين.

• IRR - توفر وظيفة IRR المعدل الداخلي للإرجاع.

• YIELD - توفر الدالة YIELD عائد السند بناءً على سعر الفائدة والقيمة الاسمية والاستحقاق.

صيغ التاريخ والوقت الوسيطة في Excel

تتضمن وظائف التاريخ والوقت الأساسية في Excel الدالتين NOW و TODAY للتاريخ الحالي. يجب أن يعرف المستخدمون المتوسطون أيضًا كيفية استخراج المكونات من تاريخ معين باستخدام:

• تاريخ اليوم)

• تاريخ الشهر)

• YEAR (التاريخ)

سترجع الصيغة = MONTH (“23 أبريل 2023 ″) نتيجة 4 للشهر الرابع. وبالمثل ، ستعيد الدالتان DAY و YEAR 23 و 2023 على التوالي.

1. وظائف الشهر الوسيط

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

• EDATE (start_date، number_of_months)

• EOMONTH (start_date، number_of_months)

على سبيل المثال ، ستحسب الصيغة = EDATE ("23 أبريل 2023" ، 2) التاريخ بعد شهرين.

الصيغة = EOMONTH (23 أبريل 2023 "، 2) تحسب نهاية الشهر بعد شهرين.

والنتيجة هي "30 يونيو 2023" ، والتي تأخذ في الاعتبار أن هناك ثلاثون يومًا فقط في يونيو. إذا حددت 3 أشهر ، فستكون النتيجة "31 يوليو 2023".

2. وظائف الأسبوع المتوسط

يمكنك استخدام مجموعة من الدالتين SUM و WEEKDAY لحساب عدد أيام العمل بالكامل ضمن نطاق من التواريخ.

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

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

= SUM (- (WEEKDAY (B1: B40،2)> 5))

3. وظائف الوقت المتوسط

تحسب الدالة YEARFRAC جزء السنة بين تاريخين.

تُستخدم الدالة TIMEVALUE لتحويل وقت ممثل كنص إلى رقم عشري يمثل نسبة 24 ساعة في اليوم.

والنتيجة هي رقم تسلسلي لـ Excel حيث يمثل 1 يومًا كاملًا مدته 24 ساعة ، ويمثل 0.5 12 ساعة ، ويمثل 0.25 6 ساعات ، وهكذا.

افترض أن لديك وقتًا بتنسيق النص "17:45" في الخلية A1 وتريد تحويله إلى رقم عشري . هذه هي الصيغة:

= TIMEVALUE (A1)

تمثل القيمة 0.74 نسبة 24 ساعة من اليوم الذي مر في وقت خمس عشرة دقيقة قبل الساعة السادسة.

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

وظائف Excel للجبر الشرطي والمنطقي

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

1. إذا كان (الشرط ، القيمة_إذا_المسألة ، القيمة_إذا_الخطأ):

2. AND (condition1، condition2،…)

3. أو (condition1 ، condition2 ، ...)

4. NOT (condition1، condition2،…)

تقيِّم الدالة IF شرطًا وتُرجع قيمًا مختلفة بناءً على ما إذا كان الشرط صحيحًا أم خطأ.

تتحقق الدالات AND و OR و NOT من الشروط الصحيحة وتتيح لك اتخاذ القرارات وفقًا لذلك.

فيما يلي بعض الأمثلة بناءً على بيانات المبيعات التي استخدمناها من قبل.

1. وظيفة IF

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

استخدم هذه الصيغة وانسخها إلى الصفوف الأخرى:

= IF (C2 <15، "مبيعات منخفضة"، "مبيعات عالية")

2. ووظيفة

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

= AND (A3 = "T-Shirt" ، C3> = 15)

3. أو وظيفة

لنفترض أنك تريد اختبار عدة شروط وإرجاع TRUE إذا تم استيفاء أي من الشروط و FALSE بخلاف ذلك ، فإنك ستستخدم الدالة OR.

على سبيل المثال ، إذا كان لدينا درجات اختبار في الخلايا A1 و B1 ، ونريد معرفة ما إذا كانت أي من الدرجات أعلى من 80 ، فيمكننا استخدام: = OR ( A1> 80، B1> 80) . سيؤدي هذا إلى إرجاع القيمة TRUE إذا كانت أي من الدرجات (أو كليهما) أعلى من 80 ، و FALSE إذا كان كلاهما 80 أو أقل.

4. لا وظيفة

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

= NOT (A2 = "Hoodie")

الرجوع للخلايا

سوف يتعرف المبتدئ بسرعة على استخدام المراجع المطلقة والنسبية مثل $ A $ 1 أو B2. يجب أن يتعرف المستخدمون المتوسطون على استخدام المراجع والفهارس والتعويضات غير المباشرة:

1. غير مباشر (ref_text)

2. INDEX (النطاق ، row_num ، column_num)

3. OFFSET (مرجع_خل ، صفوف ، أعمدة):

1. وظيفة غير مباشرة

ترجع الدالة INDIRECT قيمة مرجع الخلية المحدد الذي تم إدخاله كنص. للإشارة إلى الخلية B3 ، استخدم هذه الصيغة:

= غير مباشر ("B3")

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

2. وظيفة الفهرس

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

للإشارة إلى الخلية B3 في جدول من ستة صفوف ، استخدم هذه الصيغة:

= INDEX (B1: B6، 3)

3. وظيفة OFFSET

تُرجع الدالة OFFSET خلية أو نطاقًا يمثل عددًا محددًا من الصفوف والأعمدة بعيدًا عن خلية مرجعية.

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

= OFFSET (A1، 2، 1)

فيما يلي الصيغ قيد التنفيذ:

صيغ نصية وسيطة

يجب أن يعرف المبتدئون وظائف نصية مثل LEFT ، التي تستخرج حرفًا واحدًا أو أكثر من الجانب الأيسر من السلسلة. يجب أن يكون المستخدم المتميز على دراية بوظائف مثل هذه:

1. TEXTJOIN (محدد ، ignore_empty ، نص 1 ، [نص 2 ، ...])

2. REPLACE (old_text، start_num، num_chars، new_text)

3. SUBSTITUTE (text، old_text، new_text، [example_num])

1. وظيفة TEXTJOIN

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

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

= TEXTJOIN ("،"، TRUE، A2: A4)

2. استبدال وظيفة

تتيح لك وظيفة الاستبدال تحديد موضع البداية وطول السلسلة التي تريد تبديلها في الهدف.

لاستبدال أول حرفين من سلسلة بـ "XX" ، استخدم هذه الصيغة:

= TEXTJOIN (A2، 1، 2، “XX”)

3. وظيفة الاستبدال

تحل الدالة البديلة محل التكرارات المحددة لسلسلة نصية ضمن سلسلة نصية أخرى بسلسلة نصية جديدة.

لاستبدال تكرارات كلمة "Jeans" بكلمة "Leggings" ، استخدم هذه الصيغة:

= الاستبدال (A3، “جينز”، “Leggings”)

فيما يلي الصيغ قيد التنفيذ:

صيغ البحث الوسيطة

تتم تغطية وظائف البحث الشائعة مثل VLOOKUP و MATCH في ورقة الغش الخاصة بالمبتدئين.

يجب أن يتعرف المستخدمون المتوسطون على وظيفة البحث CHOOSE:

• اختر (رقم الفهرس ، القيمة 1 ، [القيمة 2 ، ...])

ترجع الدالة CHOOSE قيمة من قائمة القيم بناءً على رقم فهرس محدد.

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

= اختر (A1، “صغير”، “متوسط”، “كبير”)

صيغ لمعالجة أخطاء Microsoft Excel

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

1. IFERROR (القيمة ، if_error)

2. IFNA (قيمة، value_if_na)

1. وظيفة IFERROR

قسمة صفر أخطاء شائعة ولكن عرض Excel الافتراضي # DIV / 0! لن يكون واضحًا لكل مستخدم.

استخدم الدالة IFERROR لاستبدال الخطأ القياسي بـ "لا يمكن القسمة على صفر" بهذه الصيغة:

= IFERROR (A1 / B1، "لا يمكن القسمة على صفر")

تُظهر هذه الصورة الخطأ الأولي في الصف الثالث ووظيفة IFERROR تتعامل مع الصف الرابع.

2. وظيفة IFNA

تُستخدم الدالة IFNA في Excel للقبض على أخطاء # N / A ومعالجتها.

أظهر قسم آخر من ورقة الغش هذه وظيفة الاختيار. ستُرجع هذه الدالة خطأ N / A إذا لم يكن رقم الفهرس عددًا صحيحًا.

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

لتقديم خطأ أكثر فائدة ، استخدم هذه الصيغة:

= IFNA (اختر (A2 ، "منخفض" ، "متوسط" ، "مرتفع") ، "فهرس غير صالح")

اختصارات الصيغة والوظيفة

هناك العديد من اختصارات لوحة المفاتيح التي يمكن أن تجعلك تعمل بكفاءة أكبر مع ورقة العمل الخاصة بك.

• F2 : قم بتحرير الخلية النشطة ووضع نقطة الإدراج في نهاية محتويات الخلية.

• F9 : حساب وعرض نتيجة الجزء المحدد من الصيغة.

• Ctrl + Shift + Enter : أدخل صيغة صفيف.

• Shift + F3 : افتح مربع الحوار "إدراج دالة".

• Esc : إلغاء إدخال الصيغة والعودة إلى محتويات الخلية الأصلية.

قد لا تتوفر بعض اختصارات Excel هذه بجميع اللغات أو تخطيطات لوحة المفاتيح.

تنسيق الخلايا الوسيطة

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

1. حدد الخلايا التي تريد تنسيقها.

2. انقر بزر الماوس الأيمن على الخلايا المحددة واختر "تنسيق الخلايا".

3. حدد الفئة المناسبة (على سبيل المثال ، رقم ، عملة ، تاريخ) وقم بتطبيق التنسيق المطلوب.

تتضمن بعض أنواع التنسيقات الشائعة التي يجب مراعاتها في التمويل ما يلي:

• العملة : 1234.56 دولار

• النسبة المئوية : 12.34٪

• المحاسبة : (1،234.00 دولارًا)

• التاريخ : 10 مايو 2023

افكار اخيرة

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

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

هذا مجرد طعم لما يمكن أن تفعله وظائف Excel الوسيطة لتبسيط عملك ، وتبسيط تحليلاتك ، ومساعدتك في الكشف عن الأفكار من بياناتك.

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

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