صيغ إكسل المتقدمة | قائمة بأهم 10 وظائف متقدمة في Excel
قائمة بأفضل 10 صيغ ووظائف إكسل متقدمة
يمكنك تنزيل قالب صيغ Excel المتقدمة هذا من هنا - قالب صيغ Excel المتقدمة# 1 - صيغة VLOOKUP في Excel
تعد وظيفة Excel المتقدمة هذه واحدة من أكثر الصيغ استخدامًا في Excel. يرجع ذلك أساسًا إلى بساطة هذه الصيغة وتطبيقها في البحث عن قيمة معينة من جداول أخرى لها متغير مشترك واحد عبر هذه الجداول. افترض أن لديك جدولين يحتويان على تفاصيل حول راتب موظف الشركة واسم معرّف الموظف هو العمود الأساسي. تريد الحصول على الراتب من الجدول ب في الجدول أ.
يمكنك استخدام VLOOKUP على النحو التالي.
سينتج عن الجدول أدناه عندما نطبق صيغة excel المتقدمة هذه في خلايا أخرى من عمود راتب الموظف.
اسحب الصيغة إلى باقي الخلايا.
هناك ثلاثة محددات رئيسية لـ VLOOKUP:
- لا يمكن أن يكون لديك عمود أساسي في يمين العمود الذي تريد تعبئة القيمة فيه من جدول آخر. في هذه الحالة ، لا يمكن أن يكون عمود راتب الموظف قبل معرف الموظف.
- في حالة وجود قيم مكررة في العمود الأساسي في الجدول ب ، سيتم ملء القيمة الأولى في الخلية.
- إذا أدخلت عمودًا جديدًا في قاعدة البيانات (على سبيل المثال ، أدخل عمودًا جديدًا قبل راتب الموظف في الجدول ب) ، فقد يختلف ناتج الصيغة بناءً على الموضع الذي ذكرته في الصيغة (في الحالة أعلاه ، الناتج سيكون فارغًا)
# 2 - صيغة الفهرس في Excel
تُستخدم صيغة Excel المتقدمة هذه للحصول على قيمة خلية في جدول معين عن طريق تحديد عدد الصفوف أو العمود أو كليهما. السابق. للحصول على اسم الموظف في الملاحظة الخامسة ، يوجد أدناه البيانات.
يمكننا استخدام صيغة Excel المتقدمة على النحو التالي:
يمكن استخدام نفس صيغة INDEX في الحصول على قيمة على طول الصف. عند استخدام كل من رقم الصف والعمود ، سيبدو بناء الجملة كالتالي:
ترجع الصيغة أعلاه "Rajesh Ved".
ملاحظة: إذا أدخلت صفًا آخر في البيانات في الصف الخامس ، فستُرجع الصيغة "Chandan Kale". ومن ثم ، سيعتمد الإخراج على أي تغييرات تحدث في جدول البيانات بمرور الوقت.
# 3 - تطابق الصيغة في Excel
تُرجع صيغة Excel المتقدمة هذه رقم الصف أو العمود عندما يكون هناك تطابق لسلسلة أو رقم معين في النطاق المحدد. في المثال أدناه ، نحاول العثور على موضع "Rajesh Ved" في عمود اسم الموظف.
ستكون الصيغة كما هو موضح أدناه:
ستُرجع الدالة MATCH 5 كقيمة.
يتم استخدام الوسيطة الثالثة للمطابقة التامة. يمكنك أيضًا استخدام +1 و -1 بناءً على متطلباتك.
ملاحظة: يمكن للمرء أن يجمع بين INDEX و MATCH للتغلب على قيود VLOOKUP.
# 4 - IF AND Formula في Excel
هناك العديد من الحالات التي يحتاج فيها المرء إلى إنشاء إشارات بناءً على بعض القيود. نحن جميعًا على دراية بالصيغة الأساسية لـ IF. نستخدم وظيفة Excel IF المتقدمة هذه لإنشاء حقل جديد استنادًا إلى بعض القيود على حقل موجود بالفعل. ولكن ماذا لو احتجنا إلى استخدام أعمدة متعددة في الاعتبار أثناء إنشاء العلم. السابق. في الحالة أدناه ، نريد وضع علامة على جميع الموظفين الذين يزيد رواتبهم عن 50 ألفًا ولكن معرف الموظف أكبر من 3.
سنستخدم IF AND في مثل هذه الحالات. يرجى الاطلاع أدناه على لقطة الشاشة لنفسه.
سيعود النتيجة على أنها 0.
يمكن أن يكون لدينا العديد من الشروط أو القيود لإنشاء علامة بناءً على أعمدة متعددة باستخدام AND.
# 5 - IF OR الصيغة في Excel
وبالمثل ، يمكننا أيضًا استخدام دالة OR في Excel بدلاً من AND إذا كنا بحاجة فقط إلى تلبية أحد شروط العديد.
في الحالات المذكورة أعلاه ، إذا تم استيفاء أي شرط واحد ، فسيتم ملء الخلية على أنها 1 آخر 0. يمكننا أيضًا استبدال 1 أو 0 ببعض السلاسل الفرعية بعلامات اقتباس مزدوجة ("").
# 6 - صيغة SUMIF في Excel
في بعض التحليلات ، قد تحتاج إلى تصفية بعض الملاحظات عند تطبيق دالة الجمع أو العد. في مثل هذه الحالات ، فإن وظيفة Excel SUMIF المتقدمة هذه في Excel هي في إنقاذنا. يقوم بتصفية جميع الملاحظات بناءً على شروط معينة واردة في صيغة Excel المتقدمة هذه ويلخصها. السابق. ماذا لو أردنا معرفة مجموع رواتب الموظفين الذين لديهم هوية موظف أكبر من 3.
بتطبيق صيغة SUMIFS:
ترجع الصيغة النتائج كـ 322000.
يمكننا أيضًا حساب عدد الموظفين في المؤسسة الذين لديهم معرف موظف أكبر من 3 عندما نستخدم COUNTIF بدلاً من SUMIF.
# 7 - صيغة CONCATENATE في Excel
تعد وظيفة Excel المتقدمة هذه إحدى الصيغ التي يمكن استخدامها مع العديد من المتغيرات. تساعدنا صيغة Excel المتقدمة هذه على ضم عدة سلاسل نصية في سلسلة نصية واحدة. على سبيل المثال ، إذا أردنا إظهار معرف الموظف واسم الموظف في عمود واحد.
يمكننا استخدام صيغة CONCATENATE هذه فقط للقيام بذلك.
سوف ينتج عن الصيغة أعلاه "1Aman Gupta".
يمكن أن يكون لدينا متغير آخر عن طريق وضع واصلة واحدة بين ID و NAME. السابق. سينتج CONCATENATE (B3، "-"، C3) في "1-Aman Gupta". يمكننا أيضًا استخدام هذا في VLOOKUP عندما تكون LOOKUP في Excel عبارة عن مزيج من أكثر من متغير واحد.
# 8 - الصيغة LEFT و MID و RIGHT في Excel
يمكننا استخدام صيغة Excel المتقدمة هذه إذا أردنا استخراج سلسلة فرعية معينة من سلسلة معينة. يمكن استخدام الصيغ المذكورة بناءً على متطلباتنا. السابق. إذا أردنا استخراج الأحرف الخمسة الأولى من اسم الموظف ، فيمكننا استخدام الصيغة LEFT في Excel مع اسم العمود والمعلمة الثانية كـ 5.
الإخراج معطى أدناه:
تطبيق صيغة RIGHT في Excel هو نفسه أيضًا ، إنه فقط أننا سننظر إلى الحرف من يمين السلسلة. ومع ذلك ، في حالة دالة MID في Excel ، يجب أن نعطي موضع البداية للسلسلة النصية المطلوبة وطول السلسلة.
# 9 - صيغة OFFSET في Excel
يمكن أن تكون وظيفة Excel المتقدمة هذه مع مجموعة من الوظائف الأخرى مثل SUM أو AVERAGE مفيدة في إضفاء لمسة ديناميكية على الحسابات. من الأفضل استخدامه في الحالات التي نقوم فيها بإدراج صفوف متصلة في قاعدة بيانات موجودة. يمنحنا OFFSET Excel نطاقًا نحتاج فيه إلى ذكر الخلية المرجعية وعدد الصفوف والأعمدة. السابق. إذا أردنا حساب متوسط أول 5 موظفين في الشركة حيث لدينا رواتب الموظفين مرتبة حسب معرف الموظف ، فيمكننا القيام بما يلي. سوف يعطينا الحساب أدناه دائمًا راتبًا.
- سوف يعطينا مجموع رواتب أول 5 موظفين.
# 10 - صيغة TRIM في Excel
تُستخدم صيغة Excel المتقدمة هذه لتنظيف المساحات غير المهمة من النص. السابق. إذا أردنا إزالة المسافات في بداية اسم ما ، فيمكننا استخدامها باستخدام وظيفة TRIM في Excel على النحو التالي:
الناتج الناتج سيكون "Chandan Kale" بدون أي مسافة قبل Chandan.
