الوظيفه

17 صيغة إكسل متقدمة - يجب أن يعرفها جميع المحترفين

- إعلان-

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

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

قم بالقائمة وأخبرنا بمفضلتك!

1. مطابقة الفهرس

Components = INDEX(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))

اختلاف رائع عن نظام VLOOKUP أو HLOOKUP في Excel يحتوي على بعض العيوب لأداء مهام البحث. INDEX MATCH عبارة عن نظام مزيج من إمكانيات منفصلة:

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

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

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

مصدر

اسمح لنا بتقسيمها خطوة بخطوة:

كيف يمكنك مزج INDEX و MATCH

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

مفاهيم رائعة لتحدي علوم المعرفة

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

2. متوسط

لتنفيذ النظام المشترك للبحث عن ضمني لأي اختلاف في الأرقام ، فيما يلي الخطوات التي يجب مراعاتها:

أدخل القيم أو الخلايا أو مقياس الخلايا الذي تقوم ببساطة بحسابه داخل التنسيق.

يجب أن يبدأ النظام بـ = AVERAGE (الرقم 1 ، الكمية 2 ، وهكذا).

إذا كنت ترغب في تنفيذ مجموعة متنوعة من الخلايا داخل الورقة ، فإليك الخطوات التي يجب مراعاتها:

  • أدخل القيم أو الخلايا أو مقياس الخلايا الذي تقوم بحسابه ببساطة ضمن التنسيق ، الذي ربما يكون مطابقًا لما فعلته أعلاه
  • يجب أن يكون النظام مثل = AVERAGE (Begin Worth: Finish Worth).

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

3. سوميف

يشار إلى هذا النظام في Excel باسم SUMIF (متنوع ، معايير ، [مجموع النطاق]). قد ينتج عن ذلك ضمن مجموع القيم الموجودة داخل مجموعة الخلايا المرغوبة التي قد تلبي الاحتياجات التي تحددها أنت. على سبيل المثال ، تُرجع = SUMIF (C3: C12، “> 70,000) مجموع القيم بين خلايا C3 و C12 من الخلايا التي تزيد قيمتها عن 70,000 فقط.

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

سيكون النظام الخاص بالموقف أعلاه = SUMIF (متنوع ، معايير ، [sum_range]) ، سيتم تحديد المكان Vary لأن تنوع الورقة الذي يجب تحديد القيم منه.

تم تحديد [sum_range] على أنه Vary الإضافي أو الاختياري الذي ستقوم به ببساطة لتضيفه مع Vary الأساسي الذي تم إدخاله.

هنا مثال:

مصدر

4. الإزاحة مجتمعة مع SUM

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

لذلك لتحديد مجموع القيم في الخلايا المتغيرة ، يجب أن يكون النظام:

= SUM (B4: OFFSET (B4,0،2، E1-XNUMX))

مصدر

هنا مباشرة ، سيتم تغيير مرجع النهاية لـ SUM Perform مع أداء OFFSET. ينتهي نظام SUM الذي يبدأ في B4 بمتغير وهو نظام OFFSET يبدأ من B4 ، ويثابر على القيمة في E2 ("3") مطروحًا منها واحد. هذا يساعد التكتيك على المناورة على خليتين وتلخيص ثلاث سنوات من المعلومات. ضمن المرجع أعلاه ، قد ترى أن الخلية F7 تحتوي على مجموع الخلايا B4: D4 = 15.

5. إذا كان و

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

على سبيل المثال ، إذا كنت ترغب في تمييز العمال الذين يتقاضون رواتب أعلى من 50 ألفًا ومعرف العامل أفضل من 3 ، فيجب أن يكون النظام:

IF(AND(E4>3,F4>50000)1,0)

مصدر

نظرًا لعدم وجود أي ظروف فعلية ضمن المعلومات الواردة أعلاه ، سيعيد النظام القيمة 0.

6. تسلسل

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

= CONCATENATE (B3، C3)

مصدر

7. PMT

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

  • الفترة الزمنية للرهن
  • أصل القرض (نقدا)
  • على المدى الطويل يستحق
  • نوع الرهن العقاري

الآن ، إذا كنت ترغب في اكتشاف التكلفة الشهرية لكمية أساسية ، يجب أن يكون النظام لمثل هذه الكمية:

= PMT (السعر ، لكل ، PV ، [fv] ، [النوع])

اسمح لنا بإدراك هذا بمثال:

مصدر

= PMT (C2 / 12.B2.A2)

وأفضل نصف هو أنه يمكنك سحب الزاوية المناسبة لخفض خلايا PMT عبر الحقول لحساب كمية التكلفة الشهرية لجميع الحقول ميكانيكيًا!

في أسلوب منوعات وظائف علوم المعرفة

8. تقليم

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

= TRIM (C6)

مصدر

قد يعيدك هذا بقيمة Chandan Kale مع عدم وجود مناطق أخرى متصلة بالمدخل أو الجزء العلوي.

9. لين

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

= LEN (B2)

جيب مليء بدراسة التعليم عبر الإنترنت

مصدر

وإذا كنت ترغب في تسليط الضوء على جميع الخلايا الموجودة في عمود التبرع ، فيجب عليك:

  • اختر علامة التبويب "البيت" في القائمة
  • انقر فوق تنسيق شرطي (داخل شريط الأدوات)
  • اختر استخدام المكونات لمعرفة الخلايا المراد تنسيقها

(* 17 *)

هنا ، إذا قمت بإجراء الموقف "> 3" ، فسيحصل شيء يزيد عن 1,000 دولار على التنسيق المميز ، والذي يمكنك تحديده بالنقر فوق خيار التنسيق.

10. اختر

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

النظام هو:

= اختر (C7، D3، D4، D5)

مصدر

11. CELL ، LEFT ، MID ، و RIGHT

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

  • يتم استخدام أداء الخلية لإرجاع عدة أنواع من التفاصيل حول محتويات الخلية
  • تُستخدم خدمة LEFT لتبديل المحتوى النصي من بداية الخلية.
  • يمكن أن يؤدي أداء MID إلى إرجاع المحتوى النصي من أي من عوامل البداية للخلية.
  • يؤدي تنفيذ RIGHT فقط إلى إرجاع المحتوى النصي من أعلى الخلية.

مصدر

12. XNPV و XIRR

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

أسئلة وحلول مقابلة علوم المعرفة

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

= XNPV (سعر التكلفة المنخفض ، تدفقات الأموال ، التواريخ)

مصدر

بدلاً من ذلك ، يخبرك أداء XIRR بالسعر الداخلي للعائد (تدفق المكان = التدفق) لمجموعة من التدفقات المالية التي تحدث على فترات غير منتظمة ، تذكرنا بما يلي:

13. كونتا ().

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

= COUNTA (A1: A10)

مصدر

14. FV

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

  • معدل الفائدة على الرهن العقاري
  • مجموعة متنوعة من الأموال
  • تكلفة كل فترة
  • الثبات بداية المضارع
  • نوع من الرهن العقاري

على سبيل المثال ، إذا كنت ترغب في تقييم عدد من الأقراص المضغوطة النظيفة وكان لديك ميراث 20,000 دولار لإنفاق الأموال على قرص مضغوط. يتم تمثيل معدلات الفائدة في شكل عشري ؛ الأموال صفر. يجب أن يكون نظام الموقف:

=FV(A2/12,B2,C2,D2)

مصدر

يجب أن تكون النتائج:

مصدر

15. راندبيتوين

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

= RANDBETWEEN (مكان البداية ، مستوى النهاية)

مصدر

16. صغير

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

بناء الجملة للنظام

= صغير (مناسبات ، متنوع)

على سبيل المثال،

مصدر

نظرًا لأن الأداء SMALL محوسب ، يجب عليك توفير مجموعة متنوعة وعدد صحيح لـ "nth" لتحديد القيمة المرتبة. الأسماء الرسمية لهذه الوسيطات هي "مصفوفة" و "موافق".

17. رباعي

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

التركيب: = QUARTILE (array، quart)

مصدر

استنتاج:

يعد Microsoft Excel أمرًا لا مفر منه فيما يتعلق بمكتب القرن الحادي والعشرين ، ولكن الحيلة هي أنه لا يجب أن يكون شاقًا للغاية. اجعله صديقك ، وشاهد إنتاجيتك ترتفع!

تابعونا على Instagram (uniquenewsonline) والفيسبوك (uniquenewswebsite) للحصول على تحديثات إخبارية منتظمة مجانًا

مقالات ذات صلة