ما هي صيغ دالة Excel شائعة الاستخدام؟ في مكان العمل، يُستخدم برنامج Excel بشكل شائع في التحليل الإحصائي للبيانات. إذا كنت لا تتقن قدرًا معينًا من المهارات، فسوف تلجأ بالتأكيد إلى الآخرين للحصول على المساعدة أثناء العمليات المكتبية اليوم سيقدم لك المحرر 43 صيغة لوظيفة Excel لمساعدتك في التوقف عن طلب المساعدة في مكان العمل.
إنها وحدة وظيفية تم تحديدها مسبقًا في برنامج Excel وتقوم بإجراء العمليات الحسابية والتحليل ومهام معالجة البيانات الأخرى بترتيب وبنية محددة. لذلك، تسمى الدالات "صيغ خاصة"، وتكون نتيجة الإرجاع النهائية لدالة Excel عبارة عن قيمة. تحتوي الوظيفة على اسم فريد غير حساس لحالة الأحرف ويحدد وظيفتها والغرض منها.
لإعطاء مثال بسيط - عند معالجة الجدول، كيفية تغيير الحرف الأول من الاسم إلى كل الحروف الكبيرة؟
إذا كنت لا تفهم الوظائف، فهل يمكنك تعديلها يدويًا واحدة تلو الأخرى؟ إذا كنت تعرف الوظيفة المناسبة، فلن تقوم بتعديلها واحدة تلو الأخرى. أدخل صيغة الوظيفة وقم بإنجازها خلال 3 ثوانٍ!
الصيغ هي حسابات صممها المستخدمون وتم دمجها مع البيانات الثابتة ومراجع الخلايا وعوامل التشغيل وعناصر أخرى لمعالجة البيانات وحسابها. يستخدم المستخدمون الصيغ لحساب النتائج لغرض ما، لذلك يجب على صيغ Excel (ويمكنها فقط) إرجاع القيم.
بنية الصيغة: =(C2+D2)*5 من منظور بنية الصيغة، تتضمن العناصر التي تشكل الصيغة عادةً عناصر مثل علامات المساواة والثوابت والمراجع وعوامل التشغيل. ومن بينها، علامة = لا غنى عنها. ولكن في التطبيقات الفعلية، يمكن أيضًا تشغيل الصيغ باستخدام المصفوفات أو وظائف Excel أو الأسماء (الصيغ المسماة).
عادةً، يقوم Excel بتنفيذ عمليات الصيغة بالترتيب من اليسار إلى اليمين. عند استخدام عوامل تشغيل متعددة في الصيغة، سيقوم Excel بتنفيذ العمليات وفقًا لأولوية كل عامل. بالنسبة إلى عوامل التشغيل من نفس المستوى، سيتم تنفيذ العمليات من اليسار إلى اليمين. والعملية المتتابعة على اليمين. ترتيب الأولوية المحدد هو كما يلي:
عند استخدام صيغ Excel لإجراء العمليات الحسابية، قد لا يتم الحصول على النتيجة الصحيحة لسبب ما ويتم إرجاع قيمة خطأ. تظهر قيم الأخطاء الشائعة ومعانيها في الجدول أدناه.
عندما تقوم نتيجة الصيغة بإرجاع قيمة خطأ، يجب العثور على سبب الخطأ على الفور ويجب تعديل الصيغة لحل المشكلة.
تتكون وظائف Excel عادةً من اسم الوظيفة، والقوس الأيسر، والمعلمات، والفاصلة بنصف العرض، والقوس الأيمن.
بنية صيغة الدالة: =IF(A1>0,"positive number", IF(A1<0,negative number,"")) بالنسبة لمعلمات الدالة، يمكن أن تتكون من عناصر مثل القيم الرقمية والتواريخ و يمكن استخدام النص أو الثوابت أو المصفوفة أو مرجع الخلية أو وظيفة أخرى.
عندما تكون معلمات الدالة وظائف أيضًا، يطلق عليها Excel اسم تداخل الوظائف. هناك 11 نوعًا من الوظائف في المجموع، بما في ذلك وظائف قاعدة البيانات، ووظائف التاريخ والوقت، والوظائف الهندسية، والوظائف المالية، ووظائف المعلومات، والوظائف المنطقية، ووظائف الاستعلام والمرجع، والوظائف الرياضية والمثلثية، والوظائف الإحصائية، والوظائف النصية، والوظائف المحددة من قبل المستخدم .
محتوى هذه المقالة بتنسيق كتالوج، يعرض ما تفعله كل وظيفة، والوظيفة التي يمكن استخدامها لحل مشكلة معينة، وما إلى ذلك. يمكنك معرفة طريقة الاستخدام المحددة على Baidu.
بالنسبة للوظائف، لا تحتاج إلى حفظها عن ظهر قلب، ما عليك سوى معرفة نوع الوظيفة التي يجب تحديدها، وما هي المعلمات المطلوبة وكيفية استخدامها! على سبيل المثال، حدد الحقول، واستخدم وظائف LEFT/RIGHT/MID...اترك التفاصيل الأخرى لبايدو القدير!
فيما يلي تصنيف ومقدمة لهذه الوظائف الضرورية شائعة الاستخدام وفقًا لسيناريوهات التطبيق المختلفة.
1. فئة مطابقة الرابطة
البيانات المطلوبة ليست في نفس ورقة Excel أو نفس ورقة Excel في أوراق مختلفة، مما يجعل نسخ الكثير من البيانات أمرًا مزعجًا وعرضة للخطأ. كيفية دمجها؟ يتم استخدام الوظائف التالية لربط الجداول المتعددة أو مقارنة الصفوف. كلما كان الجدول أكثر تعقيدًا، كلما كان استخدامه أكثر متعة.
01.البحث
الوظيفة: تستخدم للعثور على العناصر في العمود الأول التي تنطبق عليها الشروط.
بناء الجملة: =VLOOKUP (lookup_value,table_array, col_index_num, [range_lookup])
*ملاحظات: [ ] هي معلمات اختيارية، والباقي معلمات مطلوبة، وينطبق الشيء نفسه أدناه. =VLOOKUP (العنصر المطلوب البحث عنه، الموقع المطلوب البحث عنه، رقم العمود في النطاق الذي يحتوي على القيمة المراد إرجاعها، إرجاع تطابق تقريبي أو تام - يُشار إليه بـ 1/TRUE أو 0/FALSE). مثال: استعلم عن منصب الموظف الموجود اسمه في الخلية F5.
02. هلوكوب
الوظيفة: البحث عن قيمة في الصف الأول من جدول أو مصفوفة رقمية، ثم إرجاع القيمة في عمود الصف المحدد في الجدول أو المصفوفة. يشير الحرف H في HLOOKUP إلى "الخط".
بناء الجملة: =HLOOKUP(lookup_value,table_array,row_index_num, [range_lookup])
مثال: =HLOOKUP("Axle",A1:C4, 2, TRUE) يبحث عن المحور في الصف الأول ويعيد القيمة الموجودة في الصف 2 في نفس العمود (العمود A).
الفرق بين LOOKUP وHLOOKUP: عندما تكون قيمة المقارنة موجودة في الصف الأول من جدول البيانات، إذا كنت تريد عرض العدد المحدد من الصفوف للأسفل، فيمكنك استخدام HLOOKUP. يمكن استخدام VLOOKUP عندما تكون قيمة المقارنة موجودة في العمود الأيسر من البيانات التي سيتم العثور عليها.
03. الفهرس
الدالة: إرجاع قيمة أو مرجع إلى قيمة في جدول أو نطاق.
بناء الجملة: =INDEX(array,row_num, [column_num])
مثال: =INDEX(B2:D11,3,3) هي القيمة الموجودة عند تقاطع الصف الثالث والعمود الثالث في النطاق A2:B3.
04.مباراة
الوظيفة: تستخدم لإرجاع موضع المحتوى المحدد في المنطقة المحددة (صف أو عمود معين).
بناء الجملة: =MATCH(lookup_value,lookup_array, [match_type])
مثال: =MATCH(41,B2:B5,0) موضع القيمة 41 في نطاق الخلايا B2:B5.
نوع_المطابقة:
1 أو تم حذفه: تبحث MATCH عن الحد الأقصى للقيمة الأقل من أو تساوي lookup_value.
0: MATCH للعثور على القيمة الأولى التي تساوي تمامًا lookup_value.
-1: MATCH للعثور على أصغر قيمة أكبر من أو تساوي lookup_value.
05.الرتبة
الوظيفة: البحث عن ترتيب قيمة معينة بين مجموعة من القيم في منطقة معينة.
بناء الجملة: =RANK(number,ref,[order])
مثال: =RANK(A3,A2:A6,1) طريقة ترتيب A3 في A2:A6 في الجدول أعلاه: 0 تنازلي، 1 تصاعدي، الافتراضي هو 0
06.صف
الوظيفة: إرجاع رقم السطر المشار إليه.
بناء الجملة: = الصف([المرجع])
مثال: = ROW() رقم الصف الذي توجد فيه الصيغة
07.عمود
الوظيفة: إرجاع العمود الذي توجد به الخلية.
بناء الجملة = عمود (مرجع)
مثال: =COLUMN (D10) يُرجع 4 لأن العمود D هو العمود الرابع.
08.الإزاحة
الوظيفة: إرجاع مرجع إلى خلية أو نطاق من الخلايا يحتوي على عدد محدد من الصفوف والأعمدة. يمكن أن يكون المرجع الذي تم إرجاعه خلية واحدة أو نطاقًا من الخلايا. يمكنك تحديد عدد الصفوف والأعمدة المراد إرجاعها.
بناء الجملة: =OFFSET(المرجع، الصفوف، الأعمدة،[الارتفاع]، [العرض])
مثال: =OFFSET(D3,3,-2,1,1) يعرض القيمة في الخلية B6، حيث 3 هو الصفوف الثلاثة الموجودة أدناه، و-2 هو الصفين الموجودين على اليسار، و1 هو ارتفاع الصف وعرض العمود .
قبل معالجة البيانات، يجب تنظيف البيانات المستخرجة مبدئيًا، مثل مسح مساحات السلسلة، ودمج الخلايا، واستبدال السلاسل، واعتراضها، والعثور على الموقع الذي تظهر فيه السلاسل.
سلسلة الاعتراض: استخدم MID /LEFT/RIGHT
استبدال المحتوى في الخلية: SUBSTITUTE /REPLACE
دمج الخلايا: استخدم CONCATENATE
مسح مسافات السلسلة: استخدم TRIM/LTRIM/RTRIM
ابحث عن موقع النص في خلية: FIND/SEARCH
09.منتصف
الوظيفة: اعتراض السلسلة من المنتصف
بناء الجملة: =MID(text,start_num, num_chars)
مثال: =MID(A2,1,5) بدءًا من الحرف الأول في السلسلة في A2، يتم إرجاع 5 أحرف.
استخراج السنة والشهر بناء على رقم الهوية.
10.اليسار
الوظيفة: اعتراض السلسلة من اليسار.
بناء الجملة: =LEFT(text,[num_chars])
مثال: =LEFT(A2,4) الأحرف الأربعة الأولى في السلسلة الأولى.
11. يمين
الوظيفة: اعتراض السلسلة من اليمين.
بناء الجملة: =RIGHT(text,[num_chars])
مثال: =RIGHT(A2,5) آخر 5 أحرف من السلسلة الأولى
12.البديل
الوظيفة: استبدال old_text بـ new_text في سلسلة نصية.
بناء الجملة: =SUBSTITUTE(text,old_text, new_text, [instance_num])
مثال: =SUBSTITUTE(A2, "Sales"، "Cost") يستبدل "Sales" بـ "Cost" (بيانات التكلفة) ويستبدل جزءًا من رقم الهاتف.
13. استبدال
الوظيفة: استبدال السلسلة في الخلية.
بناء الجملة: =REPLACE(old_text,start_num, num_chars, new_text)
مثال: =REPLACE(A2,6,5,"*") في A2، بدءًا من الحرف السادس (f)، استبدل خمسة أحرف بحرف واحد *.
الفرق بين REPLACE و SUBSTITUTE: الوظيفتان متقاربتان جدًا. الفرق هو أن REPLACE ينفذ الاستبدال وفقًا للموضع، ويحتاج إلى توفير الاستبدال بدءًا من الموضع وعدد الاستبدالات والنص الجديد بعد الاستبدال؛ يستبدل SUBSTITUTE وفقًا لمحتوى النص ويحتاج إلى توفير النص القديم والنص الجديد، والنص القديم الذي تم استبداله، وما إلى ذلك. لذلك، يقوم REPLACE بتنفيذ استبدال نص الموضع الثابت، ويقوم SUBSTITUTE بتنفيذ استبدال نص الموضع الثابت.
14. متصل
الوظيفة: ربط سلسلتين نصيتين أو أكثر في سلسلة واحدة.
بناء الجملة: =CONCATENATE(text1,[text2], ...)
هناك طريقة أخرى لدمج المحتويات في الخلايا وهي &. عندما يكون هناك الكثير من المحتوى المطلوب دمجه، يكون CONCATENATE أكثر كفاءة.
مثال: =CONCATENATE(B2, " ", C2) يربط ثلاثة أجزاء: السلسلة في الخلية B2، وحرف المسافة، والقيمة في الخلية C2.
15.تريم
الوظيفة: إزالة جميع المسافات في النص باستثناء المسافات المفردة بين الكلمات.
بناء الجملة: =TRIM(نص)
النص هو النص لإزالة المسافات.
مثال: =TRIM("First Quarter Earnings ") يزيل المسافات البادئة والزائدة من نص الصيغة.
16.لتريم
الوظيفة: إزالة المسافات أو الأحرف الأخرى المحددة مسبقًا من الجانب الأيسر من السلسلة.
بناء الجملة: =LTRIM (سلسلة، [charlist])
17.رتريم
الوظيفة: إزالة المسافات أو الأحرف الأخرى المحددة مسبقًا من الجانب الأيمن من السلسلة.
بناء الجملة: = LTRIM(سلسلة، [charlist])
18.ابحث عن
الوظيفة: البحث عن موضع النص
بناء الجملة: =FIND(find_text,within_text, [start_num])
مثال: =FIND("M,A2) موضع أول "M" في الخلية A2
19. بحث
الوظيفة: إرجاع الموضع الذي يظهر فيه حرف محدد أو سلسلة نصية لأول مرة في السلسلة، مع البحث من اليسار إلى اليمين.
بناء الجملة: =SEARCH(find_text,within_text,[start_num])
مثال: =SEARCH("e",A2,6) في السلسلة الموجودة في الخلية A2، بدءًا من الموضع السادس، موضع الحرف "e" الأول.
الفرق بين FIND وSEARCH: وظائف هاتين الوظيفتين متماثلة تقريبًا، ويمكنهما العثور على موقع الأحرف، والفرق هو أن وظيفة FIND تبحث بدقة وتكون حساسة لحالة الأحرف؛ حساسية الموضوع.
20.لين
الوظيفة: إرجاع عدد الأحرف في السلسلة النصية.
بناء الجملة: =LEN(نص)
مثال: =LEN(A1) طول السلسلة في الخلية A1
21.لينب
الوظيفة: إرجاع عدد البايتات المستخدمة لتمثيل الأحرف في سلسلة نصية.
بناء الجملة: =LENB(نص)
مثال: =LEN(A1)عدد البايتات في سلسلة الخلية A1.
المنطق، كما يوحي الاسم، لا يدخل في التفاصيل، فقط انتقل إلى الوظيفة.
22. إذا
الوظيفة: عند استخدام الدالة المنطقية IF، إذا كان الشرط صحيحًا، فستُرجع الدالة قيمة؛ وإذا كان الشرط خاطئًا، فسوف تُرجع الدالة قيمة أخرى.
بناء الجملة: =IF(منطقي،Value_if_true،Value_if_false)
ترجع الدالة IF قيمة إذا تم تقييم الشرط المحدد إلى صواب وقيمة أخرى إذا تم تقييم الشرط إلى خطأ.
23.COUNTIF
الوظيفة: تستخدم لحساب عدد الخلايا التي تستوفي شرطًا معينًا، على سبيل المثال، حساب عدد المرات التي تظهر فيها مدينة معينة في قائمة العملاء.
بناء الجملة: =COUNTIF(الخلية 1: الخلية 2، الشرط)
حساب عدد المرات التي يظهر فيها متجر معين في القائمة.
24.و
الوظيفة: الحكم المنطقي، أي ما يعادل "الاتحاد".
بناء الجملة: إذا كانت جميع المعلمات صحيحة، فسيتم إرجاع True غالبًا ما يتم استخدامه للحكم متعدد الشروط.
مثال: =AND(A2>1,A2<100) إذا كانت A2 أكبر من 1 وأقل من 100، فسيتم عرض TRUE، وإلا فسيتم عرض FALSE.
25.أو
الوظيفة: الحكم المنطقي، أي ما يعادل "أو".
بناء الجملة: طالما أن المعلمة تحتوي على True، فسيتم إرجاع Ture، وغالبًا ما يستخدم للحكم متعدد الشروط.
مثال: =OR(A2>1,A2<100) إذا كانت A2 أكبر من 1 أو أقل من 100، فسيتم عرض TRUE، وإلا فسيتم عرض FALSE.
عند استخدام إحصائيات جدول Excel، غالبًا ما يكون من الضروري استخدام صيغ مختلفة تأتي مع Excel، وهو أيضًا النوع الأكثر استخدامًا. (لهذه الأغراض، يأتي برنامج Excel مزودًا بوظائف الاختصار)
26 دقيقة
الوظيفة: ابحث عن الحد الأدنى للقيمة في منطقة معينة.
بناء الجملة: =MIN(number1, [number2], ...)
مثال: =MIN(D2:D11) الحد الأدنى للرقم في النطاق D2:D11.
27.ماكس
الوظيفة: ابحث عن القيمة القصوى في منطقة معينة.
بناء الجملة: =MAX(number1, [number2], ...)
مثال: =MAX(A2:A6) الحد الأقصى للقيمة في المنطقة A2:A6.
28. متوسط
الوظيفة: حساب متوسط القيمة في منطقة معينة.
بناء الجملة: =AVERAGE(number1, [number2], ...)
مثال: =AVERAGE(D2:D11) متوسط الأرقام الموجودة في نطاق الخلايا من D2 إلى D11.
29.العدد
الوظيفة: حساب عدد الخلايا التي تحتوي على أرقام.
بناء الجملة: =COUNT(value1, [value2], ...)
مثال: =COUNT(A2:A7) لحساب عدد الخلايا التي تحتوي على أرقام في نطاق الخلايا من A2 إلى A7.
30.الأرقام
الوظيفة: حساب عدد الخلايا المحددة بواسطة مجموعة معينة من الشروط.
بناء الجملة: COUNTIFS(criteria_range1,criteria1, [criteria_range2, المعايير2],...)
مثال: =COUNTIFS(A2:A7,"<6",A2:A7,>1") يحسب عدد الأرقام بين 1 و6 (باستثناء 1 و6) الموجودة في الخلايا من A2 إلى A7.
31.SUM
الوظيفة: حساب مجموع كل القيم في نطاق الخلايا.
بناء الجملة: =SUM(الخلية 1:الخلية 2)
مثال: =SUM(A2:A10) يجمع القيم الموجودة في الخلايا A2:10 معًا.
32.سوميف
الوظيفة: العثور على مجموع الخلايا التي تحقق الشروط.
بناء الجملة: =SUMIF(النطاق،المعايير، [sum_range])
مثال: =SUMIF(A2:A7,"Fruit",C2:C7) مجموع مبيعات جميع الأطعمة ضمن فئة "الفاكهة".
32.SUMIFS
الوظيفة: جمع مجموعة من الخلايا التي تستوفي الشروط المحددة.
بناء الجملة: =SUMIFS(sum_range,criteria_range1, ومعايير 1, [criteria_range2, معايير 2], ...)
مثال: =SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "LUNING") احسب إجمالي كمية المنتجات التي تبدأ بـ "香" والتي يتم بيعها بواسطة "LUNING".
33.المنتج النهائي
الدالة: إرجاع مجموع منتجات المصفوفة أو المساحة المقابلة.
بناء الجملة: =SUMPRODUCT (array1، [array2]، [array3]، ...)
مثال: =SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) احسب مجموع منتجات A1 إلى A100 في الجدول 1 وB1 إلى B100 في الجدول 2، أي A1*B1+A2 *ب2+أ3*ب3+…
34.STDEV
الوظيفة: تقدير الانحراف المعياري بناءً على العينة.
بناء الجملة: STDEV(number1,[number2],...)
مثال: =STDEV(D2:D17) الانحراف المعياري للعمود
35.المجموع الفرعي
الوظيفة: إرجاع الإجمالي الفرعي في القائمة أو قاعدة البيانات.
بناء الجملة: =SUBTOTAL(function_num,ref1,[ref2],...)
مثال: =SUBTOTAL(9,A2:A5) يستخدم 9 كمعلمة أولى لحساب مجموع قيم الإجمالي الفرعي في الخلايا A2:A5.
http://36.INT／ROUND
الدالة: تقوم الدالة ROUND بتقريب رقم إلى عدد محدد من الأرقام.
بناء الجملة: =ROUND(A1, 2)
مثال: =ROUND(2.15, 1) يقرب 2.15 إلى منزلة عشرية واحدة
الدالة: INT تقوم بتقريب رقم إلى أقرب عدد صحيح.
بناء الجملة:=INT(8.9) تقريب 8.9 للأسفل إلى أقرب عدد صحيح.
مصمم خصيصًا للتعامل مع تنسيقات الوقت والتحويلات.
37.اليوم
الوظيفة: إرجاع الرقم التسلسلي للتاريخ الحالي.
بناء الجملة: =TODAY()
تقوم الدالة li'z=TODAY()+5 بإرجاع التاريخ الحالي بالإضافة إلى 5 أيام. على سبيل المثال، إذا كان التاريخ الحالي هو 1/1/2012، فسترجع هذه الصيغة 1/6/2012.
38.الآن
الوظيفة: إرجاع الرقم التسلسلي للتاريخ والوقت الحاليين.
بناء الجملة: =الآن ()
=NOW()+7 يُرجع التاريخ والوقت بعد 7 أيام.
39. سنة
الوظيفة: إرجاع السنة المقابلة لتاريخ معين.
بناء الجملة: =YEAR(الرقم التسلسلي)
=YEAR(A3) سنة التاريخ الموجود في الخلية A3
40 شهرًا
الوظيفة: إرجاع الشهر في التاريخ.
بناء الجملة: =MONTH(الرقم_التسلسلي)
=MONTH(A2) شهر التاريخ الموجود في الخلية A2
41.DAY
الوظيفة: إرجاع عدد الأيام في تاريخ معبرًا عنه برقم تسلسلي.
بناء الجملة: =DAY(الرقم التسلسلي)
=DAY(A2) عدد الأيام في التاريخ الموجود في الخلية A2
42. ويكداي
الوظيفة: إرجاع يوم الأسبوع الموافق لتاريخ معين. افتراضيًا، يكون عدد الأيام عددًا صحيحًا يقع في النطاق من 1 (الأحد) إلى 7 (السبت).
بناء الجملة: =WEEKDAY(الرقم التسلسلي،[return_type])
=WEEKDAY(A2) يوم الأسبوع من 1 (الأحد) إلى 7 (السبت)
=WEEKDAY(A2, 2) يوم الأسبوع من 1 (الاثنين) إلى 7 (الأحد).
43.تاريخ
الوظيفة: حساب عدد الأيام أو الأشهر أو السنوات بين تاريخين.
بناء الجملة: =DATEDIF(start_date,end_date,unit)
=DATEDIF(Start_date,End_date,"Y")عدد السنوات في الفترة
=DATEDIF(Start_date,End_date,"D")عدد الأيام في الفترة
=DATEDIF(Start_date,End_date,"YD") يتجاهل السنة في التاريخ وعدد الأيام في الفترة
مُرفَق:
