يتم استخدام البيانات فقط عندما يمكنك العمل عليها بالفعل و اكسل هي إحدى الأدوات التي توفر قدرًا كبيرًا من الراحة سواء عندما تضطر إلى صياغة معادلات خاصة بك أو الاستفادة من المعادلات المضمنة. في هذه المقالة ، ستتعلم كيف يمكنك بالفعل العمل مع وظائف إعلانات صيغ Excel هذه.
فيما يلي نظرة سريعة على الموضوعات التي تمت مناقشتها هنا:
- ما هي الصيغة؟
- كتابة صيغ Excel
- تحرير صيغة
- نسخ / لصق صيغة
- إخفاء الصيغ في Excel
- أسبقية عامل تشغيل صيغ Excel
- ما هي الوظائف في Excel؟
- أهم الوظائف
ما هي الصيغة؟
بشكل عام ، الصيغة هي طريقة مكثفة لتمثيل بعض المعلومات من حيث الرموز. في Excel ، الصيغ هي تعبيرات يمكن إدخالها في خلايا ورقة Excel ويتم عرض مخرجاتها كنتيجة لذلك.
يمكن أن تكون صيغ Excel من الأنواع التالية:
اكتب | مثال | وصف |
عوامل التشغيل الرياضية (+ ، - ، * ، إلخ) | مثال: = A1 + B1 | تجمع قيم A1 و B1 |
القيم أو النص | مثال: 100 * 0.5 مضاعفات 100 في 0.5 | يأخذ القيم ويعيد الإخراج |
مرجع الخلية | مثال: = A1 = B1 | تُرجع TRUE أو FALSE بمقارنة A1 و B1 |
وظائف ورقة العمل | مثال: = SUM (A1: B1) | تُرجع المخرجات بإضافة القيم الموجودة في A1 و B1 |
كتابة صيغ Excel:
من أجل كتابة صيغة إلى خلية ورقة Excel ، يمكنك القيام بما يلي:
- حدد الخلية حيث تريد عرض النتيجة
- اكتب علامة '=' في البداية للسماح لبرنامج Excel بمعرفة أنك ستدخل صيغة في تلك الخلية
- بعد ذلك ، يمكنك إما كتابة عناوين الخلية أو تحديد القيم التي تنوي حسابها
- على سبيل المثال ، إذا كنت تريد إضافة قيم خليتين ، يمكنك كتابة عنوان الخلية على النحو التالي:
- يمكنك أيضًا تحديد الخلايا التي تريد حساب مجموعها عن طريق تحديد جميع الخلايا المطلوبة أثناء الضغط على مفتاح Ctrl:
تحرير صيغة:
إذا كنت ترغب في تحرير بعض الصيغة التي تم إدخالها مسبقًا ، فما عليك سوى تحديد الخلية التي تحتوي على الصيغة الهدف وفي شريط الصيغة يمكنك إجراء التغييرات المطلوبة. في المثال السابق ، قمت بحساب مجموع A1 و A2. الآن ، سأقوم بتحريرها وتغيير الصيغة لحساب ناتج القيم الموجودة في هاتين الخليتين:
بمجرد الانتهاء من ذلك ، اضغط على Enter لرؤية الإخراج المطلوب.
نسخ أو لصق صيغة:
يكون Excel مفيدًا حقًا عندما تضطر إلى نسخ / لصق الصيغ. عندما تنسخ صيغة ، يعتني Excel تلقائيًا بمراجع الخلايا المطلوبة في هذا الموضع. يتم ذلك من خلال نظام يسمى عناوين الخلايا النسبية .
لنسخ صيغة ، حدد الخلية التي تحتوي على الصيغة الأصلية ثم اسحبها إلى تلك الخلية التي تتطلب نسخة من تلك الصيغة على النحو التالي:
كما ترى في الصورة ، تمت كتابة الصيغة في الأصل في A3 ثم قمت بسحبها لأسفل على طول B3 و C3 لحساب مجموع B1 و B2 و C1 و C2 دون كتابة عناوين الخلايا بشكل حصري.
في حالة تغيير قيم أي من الخلايا ، سيتم تحديث الإخراج تلقائيًا بواسطة Excel وفقًا لـ عناوين الخلايا النسبية و عناوين الخلايا المطلقة أو عناوين الخلايا المختلطة .
إخفاء الصيغ في Excel:
في حالة رغبتك في إخفاء بعض المعادلات من ورقة Excel ، يمكنك القيام بذلك على النحو التالي:
- حدد الخلايا التي تنوي إخفاء صيغتها
- افتح نافذة الخط وحدد جزء الحماية
- حدد الخيار المخفي وانقر فوق موافق
- ثم ، من علامة تبويب الشريط ، حدد مراجعة
- انقر فوق ورقة الحماية (لن تعمل الصيغ إذا لم تقم بذلك)
- سيطلب منك برنامج Excel إدخال كلمة مرور لإظهار الصيغ لاستخدامها في المستقبل
أسبقية عامل تشغيل صيغ Excel:
تتبع صيغ Excel قواعد BODMAS (الطرح الضرب لقسم ترتيب الأقواس). إذا كانت لديك صيغة تحتوي على أقواس ، فسيتم حل التعبير الموجود داخل الأقواس قبل أي جزء آخر من الصيغة الكاملة. انظر للصورة ادناه:
كما ترون في المثال أعلاه ، لدي معادلة تتكون من قوس. وفقًا لقواعد BODMAS ، سيجد Excel أولاً الفرق بين A2 و B1 ثم يضيف النتيجة بـ A1.
ماذا هي 'وظائف' في Excel؟
بشكل عام ، تحدد الدالة صيغة يتم تنفيذها بترتيب معين. يوفر Excel عددًا كبيرًا من ملفات وظائف مدمجة التي يمكن استخدامها لحساب نتيجة الصيغ المختلفة.
تنقسم الصيغ في Excel إلى الفئات التالية:
| فئة | صيغ مهمة |
التاريخ والوقت | التاريخ ، اليوم ، الشهر ، الساعة ، إلخ |
الأمور المالية | ACCI ، accinto ، Dollard ، INTRAATE ، إلخ |
الرياضيات ومثلثات | SUM ، SUMIF ، المنتج ، SIN ، COS ، إلخ |
الإحصاء | AVERAGE ، COUNT ، COUNTIF ، MAX ، MIN ، إلخ |
بحث ومرجع | COLUMN ، HLOOKUP ، ROW ، VLOOKUP ، CHOOSE ، إلخ |
قاعدة البيانات | DAVERAGE ، DCOUNT ، DMIN ، DMAX ، إلخ |
نص | باهتيكست ، دولار ، أقل ، أعلى ، إلخ |
منطقي | و ، أو ، لا ، إذا ، صحيح ، خطأ ، إلخ |
معلومات | INFO ، ERROR.TYPE ، TYPE ، ISERROR ، إلخ |
هندسة | COMPLEX ، CONVERT ، DELTA ، OCT2BIN ، إلخ |
مكعب | CUBESET ، CUBENUMBER ، CUBEVALUE ، إلخ |
التوافق | PERCENTILE ، RANK ، VAR ، MODE ، إلخ |
الويب | ENCODEURL و FILTERXML و WEBSERVICE |
الآن ، دعنا نتحقق من كيفية الاستفادة من بعض صيغ Excel الأكثر أهمية والأكثر استخدامًا.
أهم وظائف Excel:
فيما يلي بعض أهم وظائف Excel إلى جانب أوصافها وأمثلة عليها.
تاريخ:
تعد وظيفة التاريخ واحدة من أهم وظائف التاريخ المستخدمة على نطاق واسع في Excel. تركيبها كما يلي:
DATE (السنة ، الشهر ، اليوم)
تقوم هذه الدالة بإرجاع رقم يمثل التاريخ المحدد بتنسيق التاريخ والوقت MS Excel. يمكن استخدام وظيفة التاريخ على النحو التالي:
مثال:
- = DATE (2019،11،7)
- = DATE (2019،11،7) -7 (إرجاع التاريخ الحالي - سبعة أيام)
يوم:
ترجع هذه الدالة قيمة اليوم من الشهر (1-31). تركيبها كما يلي:
DAY (رقم مسلسل)
هنا ، serial_number هو التاريخ الذي تريد استرداد يومه. يمكن تقديمها بأي طريقة مثل نتيجة وظيفة أخرى ، يتم توفيرها بواسطة دالة DATE ، أو مرجع خلية.
مثال:
- = DAY (A7)
- = DAY (TODAY ())
- = DAY (DATE (2019، 11،8))
شهر:
تمامًا مثل وظيفة DAY ، يوفر Excel وظيفة أخرى ، مثل وظيفة MONTH لاسترداد الشهر من تاريخ محدد. بناء الجملة كما يلي:
MONTH (رقم مسلسل)
مثال:
كيفية إنشاء لوحة القيادة في power bi
- = الشهر (اليوم ())
- = MONTH (DATE (2019، 11،8))
النسبة المئوية:
كما نعلم جميعًا ، النسبة المئوية هي النسبة المحسوبة على شكل كسر من 100. ويمكن الإشارة إليها على النحو التالي:
النسبة المئوية = (جزء / كامل) × 100
في Excel ، يمكنك حساب النسبة المئوية لأي قيم مرغوبة. على سبيل المثال ، إذا كان لديك الجزء والقيم الكاملة موجودة في A1 و A2 وتريد حساب النسبة المئوية ، يمكنك القيام بذلك على النحو التالي:
- حدد الخلية حيث تريد عرض النتيجة
- اكتب علامة “=”
- ثم اكتب الصيغة كـ A1 / A2 واضغط على Enter
- من مجموعة أرقام علامة التبويب الرئيسية ، حدد الرمز '٪'
إذا:
عبارة IF هي عبارة شرطية تُرجع True عند استيفاء الشرط المحدد وتعيد Flase عندما لا يكون الشرط كذلك. يوفر Excel وظيفة 'IF' مضمنة تخدم هذا الغرض. تركيبها كما يلي:
إذا (الاختبار المنطقي ، القيمة_إذا_المسار ، القيمة_إذا_الخطأ)
هنا، اختبار منطقي هي الحالة التي سيتم فحصها
مثال:
- أدخل القيم المراد مقارنتها
- حدد الخلية التي ستعرض الإخراج
- اكتب في شريط الصيغة '= IF (A1 = A2،' Yes '،' No ')' واضغط على Enter
VLOOKUP:
تُستخدم هذه الوظيفة للبحث عن بعض البيانات المعينة وجلبها من عمود من ورقة Excel. يرمز الحرف 'V' في VLOOKUP إلى البحث الرأسي. إنها واحدة من أهم الصيغ وأكثرها استخدامًا في Excel ومن أجل استخدام هذه الوظيفة ، يجب فرز الجدول بترتيب تصاعدي. تكون صيغة هذه الوظيفة كما يلي:
VLOOKUP (lookup_value، table_array، col_index، num_range، lookup)
أين،
ابحث عن القيمة هي القيمة المراد البحث عنها
table_array هو الجدول الذي سيتم البحث فيه
col_index هو العمود الذي سيتم استرداد القيمة منه
مجموعة البحث (اختياري) ترجع TRUE لحوالي. تطابق و FALSE لمطابقة تامة
مثال:
كما ترى في الصورة ، القيمة التي حددتها هي 2 ونطاق الجدول بين A1 و D4. أريد إحضار اسم الموظف ، لذلك أعطيت قيمة العمود كـ 2 ولأنني أريد أن يكون مطابقًا تمامًا ، فقد استخدمت False للبحث عن النطاق.
ضريبة الدخل:
لنفترض أنك تريد حساب ضريبة الدخل لشخص يبلغ إجمالي راتبه 300 دولار. سوف تحتاج إلى حساب ضريبة الدخل على النحو التالي:
- قم بإدراج إجمالي الراتب وحسم الراتب والدخل الخاضع للضريبة ونسبة الضريبة على الدخل
- حدد قيم إجمالي الراتب وحسم الراتب
- ثم قم بحساب الدخل الخاضع للضريبة من خلال إيجاد الفرق بين إجمالي الراتب وحسم الراتب
- أخيرًا ، احسب مبلغ الضريبة
مجموع:
تحسب الدالة SUM في Excel النتيجة عن طريق إضافة جميع القيم المحددة في Excel. تكون صيغة هذه الوظيفة كما يلي:
SUM (رقم 1 ، رقم 2 ،…)
تجمع كل الأرقام المحددة كمعامل لها.
مثال:
إذا كنت تريد حساب مجموع المبلغ الذي أنفقته في شراء الخضار ، فقم بإدراج جميع الأسعار ثم استخدم صيغة SUM على النحو التالي:
الفائدة المركبة:
لحساب الفائدة المركبة ، يمكنك استخدام إحدى صيغ Excel المسماة FV. ستعيد هذه الوظيفة القيمة المستقبلية للاستثمار على أساس معدل الفائدة والدفعات الدورية والثابتة. تكون صيغة هذه الوظيفة كما يلي:
FV (معدل ، nper ، pmt ، pv ، نوع)
من أجل حساب المعدل ، سوف تحتاج إلى قسمة المعدل السنوي على عدد الفترات ، أي المعدل السنوي / الفترات. يتم حساب عدد الفترات أو nper بضرب المصطلح (عدد السنوات) بالفترات ، أي فترات * مصطلح. pmt تعني الدفع الدوري ويمكن أن تكون أي قيمة بما في ذلك الصفر.
تأمل المثال التالي:
في المثال أعلاه ، قمت بحساب الفائدة المركبة لـ 500 دولار أمريكي بمعدل 10٪ لمدة 5 سنوات وبافتراض أن قيمة الدفع الدوري هي 0. يرجى ملاحظة أنني استخدمت -B1 بمعنى أنه تم أخذ 500 دولار مني.
معدل:
المتوسط ، كما نعلم جميعًا ، يصور القيمة المتوسطة لعدد من القيم. في Excel ، يمكن حساب المتوسط بسهولة باستخدام وظيفة مضمنة تسمى 'AVERAGE'. تكون صيغة هذه الوظيفة كما يلي:
AVERAGE (رقم 1 ، رقم 2 ، ...)
مثال:
إذا كنت تريد حساب متوسط الدرجات التي حصل عليها الطلاب في جميع الاختبارات ، يمكنك ببساطة إنشاء جدول ثم استخدام صيغة AVERAGE لحساب متوسط الدرجات التي حصل عليها كل طالب.
في المثال أعلاه ، قمت بحساب متوسط العلامات لطالبين في اختبارين. في حالة وجود أكثر من قيمتين يجب تحديد متوسطهما ، عليك فقط تحديد نطاق الخلايا حيث توجد القيم. فمثلا:
العد:
ستحسب وظيفة العد في Excel عدد الخلايا التي تحتوي على أرقام في نطاق معين. تكون صيغة هذه الوظيفة كما يلي:
COUNT (قيمة 1 ، قيمة 2 ،…)
مثال:
في حال كنت أرغب في حساب عدد الخلايا التي تحتوي على أرقام من الجدول الذي أنشأته في المثال السابق ، فسوف يتعين علي ببساطة تحديد الخلية التي أرغب في عرض النتيجة فيها ثم استخدام وظيفة COUNT على النحو التالي:
مستدير:
لتقريب القيم إلى بعض المنازل العشرية المحددة ، يمكنك الاستفادة من الدالة ROUND. ستعيد هذه الوظيفة رقمًا بتقريبه إلى العدد المحدد من المنازل العشرية. تكون صيغة هذه الوظيفة كما يلي:
ROUND (number، num_digits)
مثال:
العثور على الدرجة:
من أجل العثور على الدرجات ، سيتعين عليك استخدام عبارات IF المتداخلة في Excel. على سبيل المثال ، في مثال المتوسط ، قمت بحساب متوسط الدرجات التي سجلها الطلاب في الاختبارات. الآن ، للعثور على الدرجات التي حصل عليها هؤلاء الطلاب ، سيتعين علي إنشاء دالة IF المتداخلة على النحو التالي:
كما ترى ، فإن متوسط العلامات موجود في العمود G. ولحساب التقدير ، استخدمت صيغة IF المتداخلة. رمز على النحو التالي:
= IF (G19> 90، 'A'، (IF (G19> 75، 'B'، IF (G19> 60، 'C'، IF (G19> 40، 'D'، 'F')))))
بعد القيام بذلك ، سيكون عليك فقط نسخ الصيغة إلى جميع الخلايا التي تريد عرض التقديرات فيها.
مرتبة:
إذا كنت ترغب في تحديد الرتبة التي حصل عليها طلاب الفصل ، يمكنك الاستفادة من إحدى صيغ Excel المضمنة ، أي RANK. ستعيد هذه الوظيفة التصنيف لنطاق محدد من خلال مقارنة نطاق معين تصاعديًا أو تنازليًا. تكون صيغة هذه الوظيفة كما يلي:
RANK (المرجع ، الرقم ، الطلب)
مثال:
كما ترون ، في المثال أعلاه ، قمت بحساب رتبة الطلاب باستخدام وظيفة التصنيف. هنا ، المعلمة الأولى هي متوسط الدرجات التي حصل عليها كل طالب والمصفوفة هي المتوسط الذي حصل عليه جميع الطلاب الآخرين في الفصل. لم أحدد أي ترتيب ، وبالتالي ، سيتم تحديد الناتج بترتيب تنازلي. لترتيب ترتيب تصاعدي ، سيكون عليك تحديد أي قيمة غير صفرية.
كونتيف:
من أجل حساب الخلايا بناءً على بعض الشروط المحددة ، يمكنك استخدام إحدى صيغ Excel المضمنة التي تسمى 'COUNTIF'. ستعيد هذه الوظيفة عدد الخلايا التي تفي بشرط معين في نطاق معين. تكون صيغة هذه الوظيفة كما يلي:
COUNTIF (النطاق ، المعايير)
ما هو key Error في Python
مثال:
كما ترى ، في المثال أعلاه ، وجدت عدد الخلايا التي تحتوي على قيم أكبر من 80. يمكنك أيضًا إعطاء بعض القيمة النصية لمعلمة المعايير.
فهرس:
ترجع الدالة INDEX قيمة أو مرجع خلية في موضع معين في نطاق محدد. تكون صيغة هذه الوظيفة كما يلي:
INDEX (صفيف، row_num ، column_num) أو
INDEX (مرجع ،row_num، column_num، area_num)
تعمل وظيفة الفهرس على النحو التالي لملف مجموعة مصفوفة شكل:
- إذا تم توفير كل من رقم الصف ورقم العمود ، فسيتم إرجاع القيمة الموجودة في خلية التقاطع
- إذا تم تعيين قيمة الصف إلى الصفر ، فستُرجع القيم الموجودة في العمود بأكمله في النطاق المحدد
- إذا تم تعيين قيمة العمود إلى الصفر ، فستُرجع القيم الموجودة في الصف بأكمله في النطاق المحدد
تعمل وظيفة الفهرس على النحو التالي لملف مرجع شكل:
- تُرجع مرجع الخلية حيث يتقاطع الصف مع قيم العمود
- سيشير area_num إلى النطاق الذي سيتم استخدامه في حالة توفير نطاقات متعددة
مثال:
كما ترون ، في المثال أعلاه ، لقد استخدمت وظيفة INDEX لتحديد القيمة الموجودة في الصف الثاني والعمود الرابع لنطاق الخلايا بين A18 إلى G20.
وبالمثل ، يمكنك أيضًا استخدام الدالة INDEX عن طريق تحديد عدة مراجع على النحو التالي:
يقودنا هذا إلى نهاية هذه المقالة حول صيغ ووظائف Excel. أتمنى أن تكون واضحًا مع كل ما تمت مشاركته معك. تأكد من ممارسة أكبر قدر ممكن وإعادة تجربتك.
لديك سؤال لنا؟ يرجى ذكر ذلك في قسم التعليقات في مدونة 'صيغ ووظائف Excel' وسنعاود الاتصال بك في أقرب وقت ممكن.
للحصول على معرفة متعمقة حول أي تقنيات تتجه إلى جانب تطبيقاتها المختلفة ، يمكنك التسجيل في البث المباشر مع دعم على مدار الساعة طوال أيام الأسبوع وإمكانية الوصول مدى الحياة.