بسم اهلل الرحمن الرحيم الحمــدهلل رب العالميــن والصــاة والســام علــى خيــر المرســلين ،تــم بفضل اهلل وحمــد oاالنتهــاء مــن شــرح مــا يزيــد عــن 100دالــة فــي برنامــج مايكروســوفت إكســل ،Microsoft Excelتــم تجميعهــم وكتابتهم بشــكل مفهوم ومبســط بحيــث تحتــوي كل دالــة علــى مثــال واحــد علــى األقــل ،وكذلــك شــرح بعــض خفايــا تلــك الــداالت واالســتخدام األنســب لهــا. تــم ترتيــب هــذه الــدوال حســب األهميــة واألكثــر اســتخدامًا ،مــع األخــذ بعيــن االعتبــار الفئــات وأنــواع الــدوال كالــداالت الحســابية والنصيــة والماليــة وداالت التاريــخ والوقــت ...إلــخ نرجــو مــن اهلل أن يعلمنــا مــا ينفعنــا ،وينفعنــا بمــا علمنــا ،ويجعــل هــذا العمــل خالصــً لوجهــه الكريــم ..
عزيزي القارئ ال تــدع هــذا الكتــاب يقــف عنــدك ..ســاهم فــي نشــره وشــاركه مــع غيــرك لتعــم الفائــدة ..
فالدال على الخير ،كفاعله!
المحتوى بينما تم ترتيب شرح الدوال حسب األهمية،تم ترتيب المحتوى على أساس ترتيب «األلفابت» للمعادالت .واألكثر استخدامًا مع مراعات الفئات وأنواع الداالت
رقم الصفحة
الدالة
رقم الصفحة
الدالة
37
DMIN
25
55
ABS
1
36
DSUM
26
56
ACOS
2
50
EVEN
27
15
AND
3
58
EXACT
28
56
ASIN
4
53
FACT
29
56
ATAN
5
40
FIND
30
9
AVERAGE
6
50
FLOOR
31
21
AVERAGEIF
7
48
FV
32
21
AVERAGEIFS
8
54
GCD
33
50
CEILING
9
24
HLOOKUP
34
27
CHOOSE
10
45
HOUR
35
57
CONCATENATE
11
13
IF
36
56
COS
12
30
IFERROR
37
12
COUNT
13
32
IFNA
38
12
COUNTA
14
25
INDEX
39
12
COUNTBLANK
15
41
INDIRECT
40
16
COUNTIF
16
51
INT
41
18
COUNTIFS
17
33
ISBLANK
42
42
DATE
18
32
ISERR
43
46
DATEDIF
19
32
ISERROR
44
37
DAVERAGE
20
33
ISEVEN
45
44
DAY
21
33
ISFORMULA
46
37
DCOUNT
22
33
ISLOGICAL
47
37
DCOUNTA
23
32
ISNA
48
37
DMAX
24
رقم الصفحة
الدالة
77
33
ISNONTEXT
49
PV
78
33
ISNUMBER
50
53
QUOTIENT
79
33
ISODD
51
55
RAND
80
32
ISREF
52
55
RANDBETWEEN
81
33
ISTEXT
53
40
REPLACE
82
11
LARGE
54
59
RIGHT
83
54
LCM
55
49
ROUND
84
59
LEFT
56
49
ROUNDDOWN
85
56
LN
57
49
ROUNDUP
86
56
LOG
58
45
SECOND
87
22
LOOKUP
59
54
SIGN
88
58
LOWER
60
56
SIN
89
25
MATCH
61
11
SMALL
90
10
MAX
62
53
SQRT
91
60
MID
63
39
SUBSTATUTE
92
10
MIN
64
34
SUBTOTAL
93
45
MINUTE
65
9
SUM
94
52
MOD
66
19
SUMIF
95
44
MONTH
67
20
SUMIFS
96
51
MROUND
68
56
TAN
97
43
NOW
69
42
TIME
98
50
ODD
70
43
TODAY
99
28
OFFSET
71
60
TRIM
100
15
OR
72
58
UPPER
101
56
PI
73
23
VLOOKUP
102
47
PMT
74
44
YEAR
103
52
POWER
75
57
&
104
52
PRODUCT
76
رقم الصفحة
الدالة
58
PROPER
48
9
الداالت األساسية في برنامج االكسل
دالة الجمع :SUM -
) =SUM ( number1 , [number2] , ....
:numberالخانة التي تحتوي على رقم ،أي يجب علينا أن نضع الخانات المراد جمعها.
المعدل :AVERAGE -
) =AVERAGE ( number1 , [number2] , ....
:numberالخانة التي تحتوي على رقم ،أي يجب علينا وضع الخانات المراد معرفة المتوسط الحسابي لها.
المدرب :م .المنذر سفان
10
الداالت األساسية في برنامج االكسل
القيمة األعلى :MAX -
)=MAX ( number1 , [number2] , ....
:numberالخانة التي تحتوي على رقم ،ويجب علينا وضع الخانات المراد معرفة أعلى قيمة بينها.
القيمة األصغر :MIN -
) =MIN ( number1 , [number2] , ....
:numberكذلــك األمــر هنــا نضــع بــدل كل منهــا الخانــة التــي تحتــوي علــى رقــم ،وبالتالــي ســيظهر لدينــا أقــل قيمــة فيهــا.
المدرب :م .المنذر سفان
11
الداالت األساسية في برنامج االكسل
القيمة الكبرى :LARGE -
) =LARGE ( array , k
:arrayنطاق الخانات التي نرغب بمعرفة قيمة كبرى فيها. :kعدد يشير إلى الترتيب الذي نود أن يظهره؛ كثاني أكبر قيمة (نكتب )2أو ثالث أكبر قيمة (نكتب )3وهكذا.
القيمة الصغرى :SMALL -
) =SMALL ( array , k
:arrayنطاق الخانات التي نرغب بمعرفة قيمة صغرى فيها. :kعدد يشير إلى الترتيب الذي نود أن يظهره؛ كثاني أصغر قيمة (نكتب )2أو ثالث أصغر قيمة (نكتب )3وهكذا.
المدرب :م .المنذر سفان
12
الداالت األساسية في برنامج االكسل
دوال العد :COUNT - COUNTA - COUNTBLANK - ) =COUNT ( number1 , [number2] , .... ) =COUNTA ( number1 , [number2] , .... ) =COUNTBLANK ( number1 , [number2] , .... :numberالخانات التي سيتم عدها. بالنســبة لدالــة Countفهــي تقــوم بعــد الخانــات التــي تحتــوي علــى أرقــام فقــط ،بينمــا CountAتقــوم بعــد الخانــات الحاويــة علــى قيمــة رقميــة أو نصيــة (أي غيــر فارغــة) ،أمــا CountBlankفتقــوم بعــد الخانــات الفارغــة.
المدرب :م .المنذر سفان
13
الداالت األساسية في برنامج االكسل
إذا الشرطية :IF -
) ]=IF ( logical_test , [value_if_true] , [value_if_false
:logical_testاالختبار المنطقي ،أي الشرط المراد التحقق منه. :value_if_trueالقيمة التي ستظهر في حال تحقق الشرط. :value_if_falseالقيمة التي ستظهر في حال لم يتحقق الشرط. مالحظة :القيمة ممكن أن تكون معادلة ،أو خلية أو رقمًا ،أو نص ُا يوضع بين عالمتي اقتباس.
مثال :وضع كلمة Largeإذا كانت القيمة أكبر من ،20و Smallإذا كانت أقل من ذلك:
مثال :حساب الراتب الجديد إذا كانت الزيادة هي 15بالمئة لمن يعمل بدوام كامل ( )Full Timeو 10بالمئة لبقية الموظفين:
) = I F ( C 2 = “ Full Time”, D 2 * 1 5 % + D 2 , D 2 * 1 0 % + D 2 عدم تحقق الشرط
تحقق الشرط
الشرط
المدرب :م .المنذر سفان
14
الداالت األساسية في برنامج االكسل
استخدام أكثر من شرط ( IFالمتعددة): مثال :وضع كلمة Largeإذا كانت القيمة أكبر من ،30و Mediumإذا كانت القيمة أكبر من ،20و Smallإذا كانت غير ذلك:
بمعنــى افحــص الخانــة A1فــي حــال كانــت أكبــر مــن 30ضــع ،Largeأمــا فــي حــال لــم يكــن كذلــك افتــح شــرط جديــد وتحقــق مــن جديــد ،إذا كانــت الخليــة ذاتهــا أكبــر مــن 20ضــع Mediumوإذا لــم يتحقــق كل مــا ســبق ضــع .Small وهنا نغلق أقواس بعدد الـ IFالتي فتحناها. عدم تحقق الشرط الثاني
تحقق الشرط الثاني
الشرط الثاني
)) ”=IF ( A1>30 , ”Large” , IF ( A1>20 , ”Medium” , ”Small عدم تحقق الشرط األول
تحقق الشرط األول
الشرط األول
مثــال :حســاب الراتــب الجديــد إذا كانــت الزيــادة هــي 15بالمئــة لمــن يعمــل بــدوام كامــل ( )Full Timeو 10بالمئــة لمــن يعمــل بــدوام جزئــي ( )Short Timeو 5بالمئــة لبقيــة الموظفيــن:
المدرب :م .المنذر سفان
15
الداالت األساسية في برنامج االكسل
تعدد الشروط مع ANDو :OR ) =AND ( logical1 , [logical2] , .... ) =OR ( logical1 , [logical2] , .... ANDوهــي معادلــة تحقــق جميــع الشــروط ،أي فــي حــال أردنــا ان تتحقــق مجموعــة مــن الشــروط نســتخدم AND وســوف يعطينــا القيمــة Trueفــي حــال تحققهــا جميعهــا و Falseفــي حــال لــم يتحقــق واحــد منهــا علــى األقــل. أمــا ORفهــي معادلــة تحقــق شــرط واحــد علــى األقــل مــن عــدة شــروط ،بحيــث يعطــي Trueفــي حــال تحقــق واحــد منهــا علــى األقــل ،و Falseفــي حــال لــم يتحقــق أي شــيء.
مثال :معرفة األشخاص الذين تجاوزت عالماتهم الـ 50في االمتحانات األولية والنهائية:
بينما لو قمنا باستخدام الدالة ORسيقوم بإعطائنا من تجاوزت عالمته 50في امتحان واحد على األقل.
المدرب :م .المنذر سفان
16
الداالت األساسية في برنامج االكسل
العد الشرطي :COUNTIF - ) =COUNTIF ( range , criteria :rangeنطاق الخانات المراد عدها في حال تحقق شرط معين. بناء عليه. :criteriaالمعيار الذي سيقوم بالعد ً فلــو كان لدينــا أســماء موظفيــن مــع طبيعــة عملهــم ..وأردنــا أن نعــرف عــدد الموظفيــن الذيــن يكــون دوامهــم ا ..نختــار العمــود الــذي يحتــوي علــى طبيعــة العمــل ،ثــم نضــع معيــار العــد بــأن يكــون دوامــً كامـ ً كامـ ً ا.
مثال :حساب عدد األشخاص الذين يعملون بدوام كامل (:)Full Time
بإمكاننا كذلك األمر أن نضع بدل كلمة ،Full Timeالخلية التي تحتوي عليه.
ونقصد هنا ،متى ما وجدت في النطاق B2:B8خلية مشابهة للخلية B2قم بعدها.
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
17
أما في حال أردنا أن يقوم بعد القيم التي هي أكبر أو أصغر من قيمة معينة ،فيتوجب علينا أن نضع جزء المعيار كام ً ال ضمن إشارتي اقتباس.
مثال :عد القيم التي هي أكبر من القيمة :20
وبعبــارة أخــرى نســتطيع القــول أنــه متــى مــا أردنــا أن نضــع إشــارة مقارنــة ضمــن جــزء المعيــار Criteriaيتوجــب علينــا أن نضعهــا جميعـ ُا بيــن إشــارتي اقتبــاس. واالشارات تشمل :األكبر > ،األصغر < ،اليساوي = ،المختلف (الال يساوي) <>
مثال :حساب عدد الموظفين جميعاً باستثناء من يعمل عمل حر :Free lance
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
18
العد بشروط متعددة :COUNTIFS - ) =COUNTIFS ( criteria_range1 , criteria1 , [criteria_range2] , [criteria2] , ..... بناء على المعيار األول. :criteria_range1نطاق الخانات األول المراد عدها ً :criteria1المعيار األول الذي سيقوم بالعد بناء عليه. بناء على المعيار الثاني. :criteria_range2نطاق الخانات الثاني المراد عدها ً بناء عليه. :criteria2المعيار الثاني الذي سيقوم بالعد ً مــاذا لــو أردنــا أن نضــع أكثــر مــن شــرط؟ ،هنــا نحتــاج لـــ COUNTIFSألنهــا تضــع أكثــر من شــرط ،كأن نعــد الخانــات ذات دوام كامــل وأن يكــون المرتــب أعلــى مــن قيمــة معينة.
مثال :حساب عدد الموظفين الذين يعملون بدوام كامل Full Timeومرتبهم يزيد عن الـ :1300
مثال :حساب عدد الموظفين الذين تتراوح رواتبهم بين 500والـ:1300
المدرب :م .المنذر سفان
19
الداالت األساسية في برنامج االكسل
الجمع الشرطي :SUMIF - ) ]=SUMIF ( range , criteria , [sum_range وهي من عائلة الدالة SUMإال أنها هنا ال تجمع إال في حال تحقق شرط معين. :rangeنطاق الشرط ،وهو نطاق الخانات الذي سنحدد له معيار ،في حال تحققه سيجمع قيمته المقابلة. :criteriaالمعيار المراد تحققه حتى يقوم بالجمع. :sum_rangeنطاق الجمع ،وهو نطاق الخانات الحاوية على القيم المراد جمعها في حال تحقق المعيار.
مثال :حساب مجموع رواتب الموظفين الذين يعملون بدوام كامل :Full Time
مثال :حساب مجموع رواتب الموظفين الذين تتجاوز مرتباتهم الـ:1000
يمكننا االستغناء عن الجزء الثالث من معادلة SUMIFفي حال كان النطاق المشروط هو ذاته نطاق الجمع . المدرب :م .المنذر سفان
20
الداالت األساسية في برنامج االكسل
الجمع بشروط متعددة :SUMIFS - ) =SUMIFS ( sum_range , criteria_range1 , criteria1 , [criteria_range2] , [criteria2] , ..... وهي أن يتم الجمع في حال كان لدينا أكثر من شرط. :sum_rangeنطاق الجمع ،وهو الخانات الذي سيقوم بجمعها في حال تحققت الشروط. :criteria_range1نطاق الخانات المشروط األول الذي سوف نضع له شرط حتى يقوم بعد القيمة المقابلة. :criteria1المعيار األول المراد تحققه في نطاق الخانات األول. :criteria_range2نطاق الخانات المشروط الثاني الذي سوف نضع له شرط ثاني حتى يقوم بعد القيمة المقابلة. :criteria2المعيار الثاني المراد تحققه في نطاق الخانات الثاني.
مثال :حساب مجموع رواتب الموظفين الذكور الذين يعملون بدوام كامل :Full Time
مثال :حساب كمية الالبتوبات المباعة من نوع :HP
المدرب :م .المنذر سفان
21
الداالت األساسية في برنامج االكسل
حساب المعدل عند تواجد شرط معين: الدالتــان AVERAGEIFو AVERAGEIFSتشــبهان بهيكليتهمــا معادلتــي SUMIFو SUMIFSإال أنهــا هنــا تقــوم بحســاب المعــدل وليــس الجمــع.
دالة حساب المعدل بشرط واحد :AVERAGEIF - ) ]=AVERAGEIF ( range , criteria , [average_range تقوم بحساب المعدل في حال كان لدينا شرط واحد. :rangeنطاق الشرط. :criteriaالمعيار المراد تحققه. :average_rangeنطاق حساب المعدل.
مثال :حساب معدل رواتب الموظفين الذين يعملون بدوام كامل :Full Time
دالة حساب المعدل بشرط واحد :AVERAGEIFS - ) =AVERAGEIFS ( average_range , criteria_range1 , criteria1 , [criteria_range2] , [criteria2] , ..... تقوم بحساب المعدل في حال كان لدينا أكثر من شرط. :average_rangeنطاق حساب المعدل. :criteria_range1نطاق الخانات المشروط األول. :criteria1المعيار األول. :criteria_range2نطاق الخانات المشروط الثاني. :criteria2المعيار الثاني.
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
22
دوال البحث عن قيمة معينة: توجــد ببرنامــج اإلكســل دوال كثيــرة تقــوم بالبحــث عــن قيمــة معينــة ..ســنبدأ باســتعراض بعــض منهــا ونذكــر إيجابياتهــا ومــدى دقتهــا.
دالة البحث :LOOKUP ) ]=LOOKUP ( lookup_value , lookup_vector , [result_vector :lookup_valueالقيمة المراد البحث عنها. :lookup_vectorنطاق البحث ،وهو نطاق الخانات الذي سيقوم بالبحث عن القيمة ضمنها. :result_vectorنطــاق النتيجــة ،وهــو نطــاق الخانــات التــي ســتظهر النتيجــة منهــا فــي حــال وجــدت القيمــة المبحوثــة عنهــا فــي النطــاق األول أو تجاوزتهــا.
مالحظــة :1فــي حــال قمنــا بالبحــث عــن قيمــة رقميــة معينــة ضمــن جــدول ،فــإن النتيجــة ســوف يضعهــا فــي حــال تجــاوزت هــذه القيمــة وليــس فقــط أن يســاويها. فعلــى ســبيل المثــال لــو بحثنــا عــن القيمــة 65وكان لدينــا فــي الجــدول 60و .. 70فإنه ســوف يضــع القيمــة المقابلة لـــ 60ألنــه تجاوزهــا ولــم يصــل القيمة األعلــى منها. مالحظــة :2عنــد البحــث عــن قيمــة رياضيــة معينــة فــي نطــاق ،فإننــا يجــب أن نأخــذ بالحســبان أن يكــون هــذا النطــاق مرتبــً ترتيبــً تصاعديــً حتــى تعمــل الدالــة بشــكل صحيــح.
المدرب :م .المنذر سفان
23
الداالت األساسية في برنامج االكسل
إيجابيــات معادلــة Lookupأنهــا تقــوم بتحديــد عمــود البحــث عــن القيمــة وعمــود النتيجــة ،وبالتالــي ال نحتــاج لتحديــد جــدول بكاملــه ..وكذلــك تأتينــا بنتيجــة تقريبيــة للنتيجــة المبحــوث عنهــا. ولكــن مــن ســلبياتها أنــه يجــب ان تكــون القيــم فــي عمــود النتيجــة مرتبــة بشــكل تصاعــدي ،وكذلــك بعــض األحيــان نحتــاج الدقــة بالنتيجــة وال نريــد نتيجــة تقريبيــة ،وبالتالــي فــإن هــذه المعادلــة ال تفــي بالغــرض.
دالة البحث العمودي :VLOOKUP ) =VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup :lookup_valueالقيمة المراد البحث عنها. :table_arrayنطاق الجدول الذي يحتوي على عمود البحث وعمود النتيجة. :col_index_numرقم عمود النتيجة ،وهو ترتيب العمود الذي يحتوي على النتيجة بالنسبة للجدول المحدد. :range_lookupوهنا علينا وضع كلمة Trueفي حال أردنا الحصول على قيمة تقريبية ،أو وضع كلمة Falseفي حال أردنا نتيجة مطابقة تمامًا. مالحظة :بإمكاننا أن نترك الجزء األخير [ ]range_lookupبدون استكمال ،وبهذه الحالة سيقوم البرنامج بإظهار
النتيجة كما لو أننا اخترنا القيمة .True
ايجابيــات معادلــة VLookupأنهــا أكثــر دقــة مــن ســابقتها ..وذلــك ألننــا نســتطيع تحديــد مــدى دقــة البحــث عــن طريــق Trueأو .False ولكــن مــن ســلبياتها أن العمــود الــذي يحتــوي علــى القيمــة المبحــوث عنهــا يجــب أن يكــون ترتيبــه األول بالنســبة للجــدول الــذي قمنــا بتحديــده ( أي يجــب أن يكــون بأقصــى اليســار فــي حــال جهــة الورقــة مــن اليســار لليميــن).
المدرب :م .المنذر سفان
24
الداالت األساسية في برنامج االكسل
)18دالة البحث األفقي :HLOOKUP ) ]=HLOOKUP ( lookup_value , table_array , row_index_num , [range_lookup مــاذا لــو كانــت البيانــات موضوعــة بشــكل أفقــي؟ ..هنــا لــن نســتطيع اســتخدام الدالــة Vlookupلعمــل البحــث ونحتــاج لدالــة Hlookupللقيــام بالمهمــة ،وهــي مطابقــة تمامــً للدالــة الســابقة مــع اختــاف بســيط. :lookup_valueالقيمة المراد البحث عنها. :table_arrayنطاق الجدول الذي يحتوي على صف البحث وصف النتيجة. :row_index_numرقم صف النتيجة ،وهو ترتيب الصف الذي يحتوي على النتيجة بالنسبة للجدول المحدد. :range_lookupوهنا علينا وضع كلمة Trueفي حال أردنا الحصول على قيمة تقريبية ،أو وضع كلمة Falseفي حال أردنا نتيجة مطابقة تمامًا. بإمكاننا أن نترك الجزء األخير [ ]range_lookupبدون استكمال ،وبهذه الحالة سيقوم البرنامج بإظهار النتيجة كما
لو أننا اخترنا القيمة .True
كما هو الحال في الدالة Vlookupفهنا يجب أخذ الحذر بأن الصف الذي يحتوي على القيمة المبحوث عنها يجب أن يكون ترتيبه األول عند القيام بتعيين الجدول حتى تكون النتيجة صحيحة (أي يجب أن يكون في األعلى).
المدرب :م .المنذر سفان
25
الداالت األساسية في برنامج االكسل
البحث باستخدام مزيج الدالتين :MATCH - INDEX الدالة MATCH تقوم الدالة MATCHبالبحث عن قيمة معينة في عمود أو صف وتقوم بإظهار رقم الصف أو العمود الموجود به هذه القيمة.
) ]=MATCH ( lookup_value , lookup_array , [match_type :lookup_valueالقيمة المراد البحث عنها. :lookup_arrayالعمود أو الصف الذي يحتوي على القيمة المراد البحث عنها. :match_typeنوعية المطابقة: : 0أو Exact matchأي أن يكون البحث مطابقًا تمامًا. : 1أو Greater Thanوتعني إذا لم يجد القيمة يضع أقرب قيمة أكبر من القيمة المبحوث عنها. : -1أو Less thanوتعني إذا لم يجد القيمة يضع أقرب قيمة أصغر من القيمة المبحوث عنها.
للوهلة األولى قد يتبين لنا أن دالة Matchغير مجدية بشكل كبير ..فهي ال تظهر لنا إال رقم هذا الصف أو العمود الذي توجد به هذه النتيجة ،ولكن عند استخدامها ضمن دالة INDEXسنجدها مفيدة جدًا.
الدالة INDEX تقوم الدالة Indexبإظهار النتيجة بعد تحديد الصف والعمود الذي تتواجد به هذه النتيجة.
) =INDEX ( array , row_num , column_num :arrayنطاق البيانات الحاوي على القيمة المراد البحث عنها. :row_numرقم الصف الحاوي على القيمة المبحوث عنها. :column_numرقم العمود الحاوي على القيمة المبحوث عنها.
المدرب :م .المنذر سفان
26
الداالت األساسية في برنامج االكسل
كذلــك الدالــة INDEXنجدهــا لوهلــة غيــر مجديــة ..فكيــف لنــا أن نحــدد الصــف الواقــع بــه القيمــة المــراد البحــث عنهــا ،لــو علمنــا رقــم الصــف لمــا قمنــا باســتعمال الدالــة أص ـ ً ا. لذلــك كانــت الدالــة MATCHالتــي تقــوم بتحديــد الصــف أو العمــود الــذي توجــد بــه القيمــة المبحــوث عنهــا وبالتالي باســتعمال الدالتيــن معــً ســينتج لدينــا معادلــة متميزة!
نوع التطابق
عمود البحث
القيمة المراد البحث عنها
) = I N D E X ( A 2 : C 7 , M AT C H ( E 4 , A 2 : A 7 , 0 ) , 2 رقم العمود الذي يحتوي على تلك القيمة
رقم الصف الذي تتواجد به القيمة المراد البحث عنها
نطاق البحث
المدرب :م .المنذر سفان
27
الداالت األساسية في برنامج االكسل
دالة االختيار :CHOOSE ) =CHOOSE ( index_num , value1 , [value2] , ... :index_numالقيمة الرقمية المختارة (تتراوح بين الـ 1والـ .)254 :valueالقيم التي سيتم االختيار منها. تقوم هذه الدالة باختيار قيمة معينة من بين مجموعة من القيم.
يجــب أن ننــوه إلــى أن القيــم التــي نقــوم بكتابتهــا ضمــن هــذه المعادلــة ال يمكننــا االســتغناء عنهــا بنطــاق ،فيجــب علينــا كتابــة كل قيمــة مــن القيــم ،أو تحديــد خليــة عــن كل قيمــة مــع التثبيــت حتــى تعمــل هــذه الدالــة بشــكل صحيــح.
نســتطيع اســتغالل هــذه المعادلــة فــي العديــد مــن التطبيقــات المفيــدة التــي تمكننــا مــن اســتبدال أي أمــر برقــم معيــن ،فعلــى ســبيل المثــال نســتطيع كتابــة األشــهر إنطالقــً مــن التاريــخ بمســاعدة الدالــة .. Month
المدرب :م .المنذر سفان
28
الداالت األساسية في برنامج االكسل
دالة بناء قاعدة بيانات :OFFSET - ) ]=OFFSET ( reference , rows , cols , [height] , [width تعتبــر هــذه الدالــة مــن الــدوال المهمــة فــي البرنامــج ،تقــوم بشــكل أساســي بعمــل قاعــدة بيانــات تســتخدم فــي دوال أخــرى كالجمــع أو للبحــث عــن قيمــة معينــة كمــا سنشــاهد فــي األمثلــة القادمــة. :referenceوهي الخلية األولية أو نطاق من البيانات نريد أن نجعله مركز لبدء إنشاء البيانات. :rowsعدد الصفوف التي نريد أن ينتقل لها البدء بمركز إنشاء القاعدة ،يكون سالبًا ألعلى وموجبًا ألسفل. :colsعدد األعمدة التي نريد أن ينتقل لها البدء بمركز انشاء القاعدة ،يكون سالبًا أو موجبًا حسب الجهة. :heightاالرتفاع في عدد الصفوف ،في حال لم نكتبه ستكون قيمته . 1 :widthالعرض في عدد األعمدة ،في حال لم نكتبه ستكون قيمته . 1
علــى ســبيل المثــال لــو رأينــا المثــال التالــي وأردنــا إنشــاء قاعــدة بيانــات الخاليــا المحــددة ووضعنــا النقطــة المركزيــة هــي الخليــة B3كيــف ســنقوم بكتابــة األمــر؟ ..دعونــا نشــرح ذلــك.. خليــة البــدء ( )referenceســتكون B3 وهــي المركــز. بالنســبة لعــدد الصفــوف ( )rowsالتــي نريــد أن ينتقــل لهــا المركــز لبــدء إنشــاء القاعــدة هــي الخليــة B4أي 1وتعنــي أننــا نريــد أن يتحــرك المركــز صفــً واحــدًا. بالنســبة لعــدد األعمــدة ( )colsالتــي نريــد أن يتحــرك إليهــا المركــز فهــو عمــود واحــد كذلــك أي .1 القاعــدة نريدهــا أن تكــون 4صفــوف و3 أعمــدة ،بالتالــي ( )heightهــي 4و()width هــي .3
) =O FFSET ( B3 , 1 , 1 , 4 , 3
ما هي استخدامات هذه المعادلة؟ تســتخدم هــذه المعادلــة لجمــع عــدد مــن القيــم مــع معادلــة Sumأو الحصــول علــى معدلهــم مــع Average كذلــك تســتخدم فــي عمليــات البحــث مــع Lookupوالكثيــر مــن الــدوال التــي تحتــاج لقاعــدة بيانــات. دعونا نتابع معًا هذه األمثلة التوضيحية الستخدام هذه الدالة.
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
29
مثال :إظهار اسم الشخص بناء على رقم صفه:
مثال :القيام بجمع القيم حسب الشهر:
مثال :البحث عن الراتب عند إعطاء االسم:
المثــال األخيــر لــه ميــزة قويــة جــدًا وهــي أننــا أدخلنــا المعادلتيــن Offsetوالمعادلــة Countaضمــن المعادلــة Vlookupوهــي تفيدنــا فــي حــال قمنــا بزيــادة أســماء إضافيــة للجــدول فــا نحتــاج لتعديــل المعادلــة حتــى تقــوم بالعمــل الصحيــح. المدرب :م .المنذر سفان
30
الداالت األساسية في برنامج االكسل
دالة استبدال األخطاء :IFERROR - ) =IFERROR ( value , value_if_error تختــص هــذه المعادلــة باســتبدال األخطــاء التــي قــد تظهــر عنــد تطبيــق المعــادالت بقيمــة أخــرى نعينهــا لتحســين مخرجاتها. :valueالقيمة أو المعادلة التي من الممكن أن تحتوي على أخطاء. :value_if_errorالقيمة التي سيتم إعطاؤها في حال احتوت القيمة السابقة على أخطاء. علــى ســبيل المثــال لــو قمنــا بمثالنــا اآلتــي بتقســيم القيــم فــي العمــود Aعلــى القيــم فــي العمــود Bســينتج بعــض األخطــاء ألنــه ال يمكننــا التقســيم علــى الرقــم ! 0
لنقوم بتحسين القيم المخرجة نستخدم معادلة IFERRORكي ال تظهر لدينا مثل هذه األخطاء.
حتــى تصبــح هــذه المعادلــة أكثــر عمليــة بإمكاننــا أن نضــع ضمنهــا إحــدى المعــادالت القويــة والتــي قــد تظهــر أخطــاء فــي حــال لــم تحصــل علــى نتيجــة ،كمــا فــي المثــال التالــي:
المدرب :م .المنذر سفان
31
الداالت األساسية في برنامج االكسل
مثال :البحث عن اسم معين وفي حال عدم وجوده نضع القيمة (:)Not Found
ما هي أنواع األخطاء التي يمكن أن تظهر عند تطبيق المعادالت؟ يوجد في البرنامج العديد من رموز الخطأ التي يمكن أن تظهر عند تطبيق المعادالت ،فيا هل ترى ما ً سوية. هي أنواع األخطاء وما هي دالليتها؟ ،دعونا نتعرف عليها
الخطأ
#N/A !#VALUE !#REF !#DIV/0 !#NUM ?#NAME !#NULL
الداللة تعني أن القيمة ليست متاحة في هذه المعادلة كالبحث عن قيمة وهي ليست موجودة في مجال البحث
تعني أن هنالك قيمة غير صحيحة كنص أو رقم كوضع نص بد ً ال من رقم في بعض المعادالت التي تتطلب أرقامًا
تعني أن المعادلة تحتوي على مرجعية خاطئة كالقيام بتعيين خلية بد ً ال من جدول البحث في معادالت البحث
خطأ ينتج عن القيام بالتقسيم على رقم 0 الرقم المدخل بصيغة خاطئة كوضع رقم سالب عند طلب الحصول على جذر تربيعي
إدخال قيم غير مفهومة بالنسبة للمعادلة كالقيام بتعيين عناصر ليست كمثل عناصر المعادلة
خطأ ينتج عن وجود مسافة بين مدى الخاليا كعمل مسافة بد ً ال من الفاصلة أو النقطين في معادلة الجمع المدرب :م .المنذر سفان
32
الداالت األساسية في برنامج االكسل
دالة :IFNA ) =IFNA ( value فــي كثيــر مــن األحيــان ال نحتــاج إلخفــاء جميــع األخطــاء مــع دالــة IFERRORألن بعــض األخطــاء مفيــدة جــدًا بإخبارنــا عــن بعــض المدخــات الخاطئــة ،لكــن نريــد فقــط إخفــاء الخطــأ #N/Aالخــاص بعــدم وجــود القيمــة المطلوبــة. :valueالقيمة أو المعادلة التي من الممكن أن تحتوي أو تظهر الخطأ .#N/A
هنالك أيضًا دوال فحص األخطاء السابقة: وهــي معادلــات تقــوم بالبحــث عــن األخطــاء وإعطــاء القيمــة Trueفــي حــال كانــت القيمــة تحــوي أي خطــأ ..و Falseفــي حــال لــم يكــن هنالــك خطــأ. الدالة
المهمة
) =ISERROR ( value
فحص الخلية أو المعادلة الحاوية على أي نوع من األخطاء
) =ISERR ( value
فحص الخلية أو المعادلة الحاوية على أي نوع من األخطاء باستثناء الخطأ #N/A
) =ISNA ( value
فحص الخلية الحاوية على الخطأ #N/A
) =ISREF ( value
فحص الخلية الحاوية على الخطأ !#REF
اســتخدام هــذه المعــادالت يكــون بالشــكل األمثــل مــع معادلــة ثانيــة ،علــى ســبيل المثــال نســتخدمها مــع معادلــة IFللقيــام بعمليــة تقســيم صحيحــة...
المدرب :م .المنذر سفان
33
الداالت األساسية في برنامج االكسل
فيما يأتي سنتعرف على بعض دوال الفحص الموجودة في اإلكسل: الدالة
المهمة
) =ISNUMBER ( value
فحص الخلية الرقمية
) =ISTEXT ( value
فحص الخلية النصية
) =ISNONTEXT ( value
فحص الخلية الغير نصية
) =ISBLANK ( value
فحص الخلية الفارغة
) =ISODD ( value
فحص الخلية الرقمية ذات الرقم الفردي
) =ISEVEN ( value
فحص الخلية الرقمية ذات الرقم الزوجي
) =ISFORMULA ( value
فحص الخلية الناتجة عن معادلة
) =ISLOGICAL ( value
فحص الخلية التي تحتوي على TRUEأو FLASE
المدرب :م .المنذر سفان
34
الداالت األساسية في برنامج االكسل
دالة حساب اإلجمالي :SUBTOTAL -
) =SUBTOTAL ( function_num , ref1 , ...
:function_numرقم المعادلة التي نريد حساب اإلجمالي عبرها. :ref1البيانات التي نريد حساب اإلجمالي لها. تقــوم هــذه الدالــة بحســاب اإلجمالــي لمجموعــة مــن البيانــات ،كمــا تمكــن هــذه الدالــة حســاب اإلجمالــي عــن طريــق عــدة داالت مثــل دالــة الجمــع SUMوالمعــدل AVERAGEوالقيمــة العليــا MAXوالصغــرى MIN وغيرهــا. ويجــب االنتبــاه أن كل المعــادالت تحتــوي علــى رقميــن حيــث أن أحدهــا يشــمل جميــع الخانــات المخفيــة والظاهــرة واألخــرى تختــص بالظاهــرة فقــط. الدالة
تشمل الخاليا المخفية
ال تشمل الخاليا المخفية
AVERAGE
1
101
COUNT
2
102
COUNTA
3
103
MAX
4
104
MIN
5
105
PRODUCT
6
106
STDEV
7
107
STDEVP
8
108
SUM
9
109
VAR
10
110
VARP
11
111
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
35
كمــا هــو مالحــظ فــي المثــال الســابق أننــا اســتخدمنا دالــة الجمــع لحســاب اإلجمالــي لتلــك البيانــات ،وكان الصــف الســادس مخفيــً ولــم يقــم بحســابه ألن رقــم المعادلــة المســتخدمة كان يخــص أال يشــمل الخاليــا المخفيــة، ولــو قمنــا اآلن باســتخدام الرقــم الــذي يشــمل الخاليــا المخفيــة الختلفــت النتيجــة:
بقــي أن ننــوه أن هــذه المعادلــة مفيــدة بشــكل كبيــر فــي حــال قمنــا بالفلتــرة وأردنــا الحصــول علــى المجمــوع اإلجمالــي للبيانــات مــع عــدم األخــذ بالبيانــات خــارج الفلتــرة ،فالمعــادالت العاديــة كالجمــع والمعــدل وغيرهــا ال تتغيــر مــع تغيــر الفلتــرة ،وســتقوم بإجــراء العمليــة للجميــع ،بينمــا نجــد أن معادلــة Subtotalقــد جــاءت بالحــل األمثــل لذلــك. ولكن هنالك مالحظتين يجب معرفتها عن هذه المعادلة: أولهــا :الخاليــا المخفيــة بخاصيــة الفلتــرة لــن تقــوم بحســابها بالحالتيــن ،بالتالــي أيــا مــن المعــادالت التــي ســنقوم باســتخدامها ســتأتي بنفــس النتيجــة بحيــث لــن تشــمل الخاليــا المخفيــة. ثانيها:احتســاب الخاليــا المخفيــة مــن عدمــه يكــون فقــط للصفــوف ،أي لــو كان هنالــك أعمــدة مخفيــة ،فــإن معادلــة Subtotalســتقوم باحتســابها بالحالتيــن.
المدرب :م .المنذر سفان
36
الداالت األساسية في برنامج االكسل
دوال الحساب اعتمادًا على بناء قاعدة بيانات: الدالة :DSUM هــي دالــة جمــع ولكنهــا تقــوم بالجمــع بنــاء علــى قاعــدة بيانــات ومجموعة شــروط تبنــى ضمــن قالب معيــن ،تفيد فــي عمــل برنامــج مبســط والحصــول على مجاميــع بســرعة وفعالية ،والحــرف Dهو اختصــار لكلمــة . Databace
) =DSUM ( database , field , criteria :databaseجدول البيانات الذي يحتوي على القيم التي نريد إجراء عمليات الجمع عليها. :fieldالعمود الذي يحتوي على القيم التي نحتاج جمعها بناء على شروط معينة. :criteriaنطاق البيانات الذي يحتوي على صف الرؤوس وصف الشروط.
قامــت هــذه المعادلــة بجمــع كافــة القيــم فــي العمــود Cوذلــك ألن الشــرط Criteriaفــي النطــاق E3:G4فارغــة، بمعنــى أنــه ال يوجــد شــروط هنــا. دعونا نضع شروط ،مثال نريد مجموع المبيعات للموبايالت من نوع .. Samsung
المدرب :م .المنذر سفان
37
الداالت األساسية في برنامج االكسل مثال آخر لو أردنا مجموع المبيعات لماركة LGالتي تجاوزت سعرها األلف دوالر ..
دوال مشابهة :DAVERAGE - DMAX - DMIN - DCOUNT - DCOUNTA هــي دوال مشــابهة آلليــة عمــل دالــة DSUMوكل واحــدة منهــا تقــوم بعمــل خــاص بنوعيتهــا كدالــة DAVERAGE النــي تقــوم بحســاب المعــدل بنــاء علــى قاعــدة البيانــات والشــروط ودالــة DMAXالتــي تقــوم بحســاب أعلــى قيمــة بجــدول البيانــات و DMINالتــي تقــوم بحســاب أقــل قيمــة بجــدول البيانــات و DCOUNTالتــي تعــد القيــم الرقميــة بجــدول البيانــات و DCOUNTAالتــي تقــوم بعــد الخاليــا كاملــة بجــدول البيانــات اعتمــادًا علــى قاعــدة شــروط .
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
38
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
39
دوال االستبدال: تقوم هذه الدوال بالبحث عن قيمة واستبدالها بقيمة جديدة ،ولكل منها ميزاتها.
دالة :SUBSTATUTE ) ]=SUBSTATUTE ( text , old_text , new_text , [instance_num :textالقيمة التي سنقوم بإجراء االستبدال عليها. :old_textالنص القديم أو جزء منه الذي نريد استبداله. :new_textالنص الجديد الذي نريد وضعه. :instance_numهــو اختيــاري فــي حــال كان النــص يحتــوي علــى شــيء مكــرر وأردنــا اســتبدال جــزء مــع بقــاء األجــزاء البقيــة فنضــع هنــا رقــم الجــزء الــذي نريــد اســتبداله (علــى ســبيل المثــال نضــع 1الســبتدال األول وبقــاء البقيــة و2 الســتبدال الثانــي وبقــاء البقيــة وهكــذا).
مثال :استبدال الشرطات بين األسماء بالفراغات:
مثال :استبدال اليوم 1باليوم 2فقط من دون تغيير الرقم 1ضمن السنة:
المدرب :م .المنذر سفان
40
الداالت األساسية في برنامج االكسل
دالة :REPLACE
) =REPLACE ( old_text , start_num , num_chars , new_text
تقــوم هــذه الدالــة باســتبدال أحــرف معينــة مــن موضــع معيــن بأخــرى جديــدة ،واختالفهــا عــن الدالــة الســابقة أنهــا تقــوم باالســتبدال بنــاء علــى موضــع الحــرف مــن النــص. :old_textالنص القديم أو الخلية التي نريد استبدال شيء فيها. :start_numرقم يدل على ترتيب الحرف الذي يبدأ االستبدال منه. :num_charsعدد األحرف التي نريد استبدالها بدءًا من الحرف المعين. :new_textالنص الجديد الذي نريد وضعه.
مثال :استبدال الـ comبـ netفي عناوين البريد االلكتروني:
البحث عن قيمة :FIND -
) ]=FIND ( find_text , within_text , [start_num
:find_textالقيمة المراد البحث عنها. :within_textموقع البحث ،أي الخلية المراد البحث فيها. :start_numاختيارية ،رقم الحرف المراد بدء البحث منه.
البحــث فــي الدالــة Findحساســة لحالــة األحــرف ،فيجــب أخــذ الحيطــة عنــد البحــث عــن قيمــة معينــة إن كانــت بأحــرف كبيــرة أم صغيــرة. المدرب :م .المنذر سفان
41
الداالت األساسية في برنامج االكسل
دالة :INDIRECT
) ]=INDIRECT ( ref_text , [a1
تقــوم هــذه الدالــة بتوجيــه األمــر مــن خليــة أخــرى ،أو تقــوم بإظهــار مــا بداخــل األســماء مــن قيــم (إدارة األســماء Name Mangerالموجــودة بقائمــة .)Formulas :ref_textالنص المرجعي الذي نريد توجيهه. :a1قيمة منطقية تحدد نوع المرجع الذي تم احتواؤه ،غالب األحيان ال نحتاج لكتابة هذا الجزء.
مثال :القيام بعملية الجمع وليس لدينا أسماء الخاليا ..لدينا فقط أرقام الصفوف:
هنــا قمنــا بعمليــة الجمــع مــع دالــة Sumوقمنــا بدمــج رقــم الصــف مــع اســم العمــود مــع الميــزة & وهــذه جــدًا مفيــدة فــي عمليــات الجمــع أو اســتيراد الخاليــا مــن أكثــر مــن ورقــة ،وتســتخدم الدالــة Indirectأكثــر شــيء فــي بنــاء علــى قيــم سلســلة أخــرى ..كعمــل قائمــة منســدلة Data Validationعندمــا نقــوم بعمــل سلســلة أو List ً فــي خاليــا تحتــوي الــدول العربيــة مثـ ً ا ،وقائمــة منســدلة بخاليــا أخــرى تحتــوي مــدن تلــك الــدول التــي تــم تحديدها بحيــث تتغيــر المــدن عنــد تغييــر الدولــة ،وهــذا ال يتــم إال عــن طريــق اســتخدام الدالــة .Indirect المدرب :م .المنذر سفان
42
الداالت األساسية في برنامج االكسل
حساب التاريخ :DATE -
) =DATE ( year , month , day
يقوم بتحويل مجموعة من األرقام لتاريخ يمكن االستعانة به في المعادالت .. :yearالرقم الخاص بالسنة. :monthالرقم الخاص بالشهر. :dayالرقم الخاص باليوم.
حساب الوقت :TIME -
) =TIME ( hour , minute , second
يقوم بتحويل مجموعة من األرقام لوقت يمكن االستعانة به في المعادالت .. :hourالرقم الخاص بالسنة. :minuteالرقم الخاص بالدقائق. :secondالرقم الخاص بالثواني.
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
حساب التاريخ والوقت الحاليين:
43
) ( =TODAY ) ( =NOW
تقــوم معادلــة TODAYبوضــع التاريــخ الحالــي فــي الخليــة ،بينمــا تقــوم معادلــة NOWبوضــع التاريــخ مــع الوقــت الحاليييــن فــي الخليــة ،هاتيــن الدالتيــن ال نحتــاج لكتابــة شــيء ضمــن قوســيهما ألننــا هنــا نريــد أن نضــع التاريــخ والوقــت وال شــيء أكثــر ،أي ال نريــد حســاب شــيء. يجــدر اإلشــارة أن هاتيــن المعادلتيــن ســتقوم بالتحديــث أوتوماتيكيــً عنــد أي تغييــر فــي أي خليــة وتقــوم بتغييــر التاريــخ والوقــت الحالــي تلقائي ـ ُا.
مالحظــة :بإمكاننــا إظهــار فقــط الوقــت بمعادلــة NOWمــن خــال تغييــر تنســيق هــذه الخانــة لتنســيق وقــت فقــط Timeمــن خــال قائمــة الصفحــة الرئيســية .HOME كمــا أن هاتيــن المعادلتيــن اســتخدامهما واســع جــدًا فــي المعــادالت كمــا مــر ســيمر معنــا الحقــً فــي معادلــة Datedifلحســاب فــرق التاريــخ.
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
44
حساب السنوات والشهور واأليام: ) =DAY ( serial_number ) =MONTH ( serial_number ) =YEAR ( serial_number :serial_numberالرقم الذي نريد تحويله. جميــع هــذه الــدوال تعمــل بنفــس الطريقــة وتقــوم بإظهــار مخرجــات إمــا األيــام أو الشــهور أو الســنوات ،فلــو كان لدينــا تاريــخ وأردنــا أن نأخــذ منــه فقــط الســنوات أو األيــام أو الشــهور ،أو حتــى رقــم وأردنــا تحويلــه لســنوات مث ـ ُ ا فإننــا نســتخدم إحــدى الــداالت الســابقة الذكــر.
المدرب :م .المنذر سفان
الداالت األساسية في برنامج االكسل
45
حساب الساعات والدقائق والثواني: ) =HOUR ( serial_number ) =MINUTE ( serial_number ) =SECOND ( serial_number :serial_numberالرقم الذي نريد تحويله. كذلــك األمــر هنــا فهــذه الــدوال تقــوم بتحويــل رقــم معيــن إلــى ســاعات أو دقائــق أو ثوانــي ،أو بإمكاننــا اســتخالص مــا نرغــب بــه مــن وقــت مكتــوب فــي إحــدى الخاليــا.
المدرب :م .المنذر سفان
46
الداالت األساسية في برنامج االكسل
حساب فرق التاريخ :DATEDIF - ) =DATEDIF ( start_date , end_date , interval وهي لحساب الفرق بين تاريخين معينين. :start_dateالتاريخ األقدم. :end_dateالتاريخ األحدث. :intervalوهو نمط الحساب كالتالي :”Y“ :حساب الفرق بالسنوات :”M“ ،لحساب الفرق باألشهر :”D“ ،لحساب الفرق باأليام.
مالحظــة :بإمكاننــا االســتفادة مــن خاصيــة DATEDIFبالحصــول علــى أعمــار موظفيــن أو أطفــال أو متــى انتهــاء عقــد أشــخاص كمــا فــي المثــال التالــي بحيــث إذا أردنــا الحصــول على ســنوات عمــل موظفين مــن تاريخ مباشــرتهم العمــل حتــى وقتنــا الحالــي ..وذلــك بمســاعدة الدالــة )(:TODAY
المدرب :م .المنذر سفان
47
الداالت األساسية في برنامج االكسل
الداالت المالية PMT -و PVو :FV هذه الدوال تقوم بحساب األقساط والقروض المالية وكمية الدفعات.
دالة :PMT ) ]=PMT ( rate , nper , pv , [fv] , [type :rateمعدل الفائدة للقرض. :nperعدد دفعات تسديد القرض. :pvالمبلغ اإلجمالي الذي نريد سحبه أو سداده. :fvالقيمة المستقبلية بعد زيادة الفوائد التي نريد دفعها أو سدادها (اختيارية). :typeاختيارية وتعني طريقة السداد ،وهي إما 0وتعني نهاية الفترة الزمنية ،أو 1وتعني بداية الفترة الزمنية.
قمنا بتقسيم معدل الفائدة على 12ألننا نريد الدفعات الشهرية ..أي 12شهرًا. بينما لم نقم بضرب عدد الدفعات بـ 12ألن عدد الدفعات هي ذاتها موزعة على الشهور. وهكــذا تنتــج لدينــا الدفعــات الشــهرية ..ولكــن القيمــة كانــت ســالبة ..وحتــى نتخلــص مــن اإلشــارة الســالبة نســتطيع وضــع اشــارة -قبــل المعادلــة.
مثال :حساب الدفعة الشهرية عند معرفة مدة التسديد السنوية:
المدرب :م .المنذر سفان
48
الداالت األساسية في برنامج االكسل
دالة :PV ) ]=PV ( rate , nper , pmt , [fv] , [type هــذه المعادلــة هــي اختصــار لـــ Present Valueوتقــوم علــى احتســاب أصــل المبلــغ (المبلــغ الحالــي) لــو كان لدينــا مقــدار الدفــع الــدوري (الشــهري أو الســنوي) مــع الفائــدة ومــدة الســداد. :rateمعدل الفائدة للقرض. :nperعدد دفعات تسديد القرض. :pmtالدفعة التي يتم تسديدها في كل فترة. :fvالقيمة المستقبلية بعد زيادة الفوائد التي نريد دفعها أو سدادها (اختيارية). :typeاختيارية وتعني طريقة السداد ،وهي إما 0وتعني نهاية الفترة الزمنية ،أو 1وتعني بداية الفترة الزمنية.
دالة :FV ) ]=FV ( rate , nper , pmt , [pv] , [type أمــا هــذه المعادلــة فهــي اختصــار لـــ Future Valueوتقــوم بحســاب القيمــة المســتقبلية للمبلــغ ،أي بعــد زيــادة الفوائــد وتســديد الدفعــات ضمــن الفتــرات الزمنيــة. :rateمعدل الفائدة للقرض. :nperعدد دفعات تسديد القرض. :pmtالدفعة التي يتم تسديدها في كل فترة. :pvالمبلغ اإلجمالي الذي نريد سحبه أو سداده (اختيارية). :typeاختيارية وتعني طريقة السداد ،وهي إما 0وتعني نهاية الفترة الزمنية ،أو 1وتعني بداية الفترة الزمنية.
المدرب :م .المنذر سفان
49
الداالت األساسية في برنامج االكسل
عمليات تقريب األرقام العشرية: وهي عمليات تقريب األرقام العشرية (األرقام ما بعد الفاصلة).
) =ROUND ( number , num_digits :numberالرقم الحاوي على أرقام عشرية (إما نحدد خانة أو نضع رقم مباشرة). :num_digitsرقم يدل على عدد األرقام بعد الفاصلة الذي نريد أن نقربه إليه.
الدالة
العملية
) =ROUNDUP ( number , num_digitsالتقريب لرقم أعلى محدد بعدد num_digits ) =ROUNDDOWN ( number , num_digitsالتقريب لرقم أدنى محدد بعدد num_digits
المدرب :م .المنذر سفان
50
الداالت األساسية في برنامج االكسل
الدالة
العملية
) =EVEN ( number
التقريب لرقم زوجي أعلى
) =ODD ( number
التقريب لرقم فردي أعلى
الدالة
العملية
) =CEILING ( number , significance
التقريب لرقم أعلى من مضاعات العدد significance
) =FLOOR ( number , significance
التقريب لرقم أدنى من مضاعات العدد significance
المدرب :م .المنذر سفان
51
الداالت األساسية في برنامج االكسل
الدالة
العملية
) =MROUND ( number , multiple
التقريب ألقرب رقم من مضاعات العدد multiple
الدالة
العملية
) =INT ( number
ويعطي الجزء الصحيح من العدد (أي بدون تقريب)
المدرب :م .المنذر سفان
52
الداالت األساسية في برنامج االكسل
الدوال الرياضية: دالة عملية الضرب :PRODUCT -
) =PRODUCT ( number1 , number2 , ...
وهي نفس دالة الجمع SUMإال أنه هنا عملية الضرب بدل الجمع ،أي تقوم بعملية الضرب للخانات المحددة.
الرفع إلى قوة :POWER -
) =POWER ( number , power
تقوم بإظهار نتيجة عدد numberمرفوع لقوة (أس) .power
باقي القسمة :MOD -
) =MOD ( number , divisor
تقوم بإعطاء باقي قسمة الرقم ( )numberعلى المقسوم عليه (.)divisor
المدرب :م .المنذر سفان
53
الداالت األساسية في برنامج االكسل
الرقم الصحيح من القسمة :QUOTIENT -
) =QUOTIENT ( numerator , denominator
تقوم بإعطاء ناتج قسمة البسط ( )numeratorعلى المقام ( )denominatorمع إهمال الباقي.
الجذر التربيعي :SQRT -
) =SQRT ( number
المعامل العددي :FACT -
) =FACT ( number
تقــوم بإعطــاء المعامــل العــددي لعــدد معيــن ،والمعامــل العــددي هــو حاصــل ضــرب األرقــام مــن رقــم واحــد حتــى العــدد المحــدد. مثال :المعامل العددي للرقم 5هو5 × 4 × 3 × 2 × 1 :
ويساوي 120
المدرب :م .المنذر سفان
54
الداالت األساسية في برنامج االكسل
القاسم المشترك األكبر :GCD -
) =GCD ( number1 , number2 , ....
تقول بإعطاء القاسم المشترك األكبر لمجموعة من األرقام المحددة في (.)number
المضاعف المشترك األصغر :LCM - ) =LCM ( number1 , number2 , ....
العدد السالب والموجب :SIGN -
) =SIGN ( number
تقــول بإعطــاء القيمــة 1فــي حــال كان الرقــم الموضــوع فــي ( )numberموجبــً ،و 1-فــي حــال كان ســالبًا ،و 0فــي حــال كان صفــرًا.
المدرب :م .المنذر سفان
55
الداالت األساسية في برنامج االكسل
القيمة المطلقة :ABS -
) =ABS ( number
تقول بإعطاء قيمة الرقم بغض النظر عن قيمته السالبة أو الموجبة.
توليد رقم عشوائي :RAND -
) ( =RAND
تقوم بإعطاء رقم عشوائي ال على التعيين.
توليد رقم عشوائي بين قيمتين :RANDBETWEEN - ) =RANDBETWEEN ( bottom , top تقوم بإعطاء رقم عشوائي بين القيمة السفلى المحددة في bottomوالقيمة العليا المحددة في .top
المدرب :م .المنذر سفان
56
الداالت األساسية في برنامج االكسل الدالة
العملية
) =Log ( number
اللوغاريتم العادي
) =Ln ( number
اللوغاريتم الطبيعي
) ( =PI
الرقم π
الدوال المثلثية: وهي الحسابات الخاصة بالمثلث القائم الزاوية. الدالة
العملية
) =SIN ( number
حساب جيب الزاوية
) =COS ( number
حساب التجب
) =TAN ( number
الظل
) =ASIN ( number
حساب متمم الجيب
) =ACOS ( number
متمم التجب
) =ATAN ( number
متمم الظل
المدرب :م .المنذر سفان
57
الداالت األساسية في برنامج االكسل
الدوال النصية: دمج الخانات :& - لنقوم بدمج الخانات (النصية والرقمية) في خانة واحدة ،علينا فقط أن نضع الخانات مع وصلة & في ما بينها.
لنقوم بالدمج مع وجود فراغات ..يكفي أن نضع فراغًا بين اشارتي اقتباس بين الخانتين المدموجتين.
دمج الخانات باستخدام دالة : CONCATENATE - ) =CONCATENATE ( text1 , text2 , .... هي ذاتها & ولكن هنا كمعادلة ،و textهي الخانات النصية أو الرقمية المراد دمجها.
المدرب :م .المنذر سفان
58
الداالت األساسية في برنامج االكسل
تكبير األحرف النصية: الدالة
العملية
) =PROPER ( text
تكبير الحرف األول من كل كلمة
) =UPPER ( text
تكبير جميع الحروف
) =LOWER ( text
تصغير جميع الحروف
مقارنة الخانات النصية :EXACT - ) =EXACT ( text 1 , text 2 , text 3 , ... يقــوم بمقارنــة الخانــات الحاويــة علــى النصــوص المحــددة فــي ( )textوإعطــاء القيمــة Trueفــي حــال كانــت متطابقــة و Falseفــي حــال لــم تكــن متســاوية.
المدرب :م .المنذر سفان
59
الداالت األساسية في برنامج االكسل
دوال استخالص األحرف: تمكننــا هــذه الــدوال مــن اســتخالص عــدد مــن األحــرف نقــوم بتحديدهــا أو حتــى اســتخالص الفراغــات بحيــث تســهل علينــا مــن عمليــات النســخ واللصــق وكذلــك عمليــات المقارنــة.
االستخالص من اليمين :RIGHT - ) =RIGHT ( text , num_chars :textالنص الذي نريد أن نقتطع منه. :num_charsعدد األحرف التي نريد اقتطاعها بدءًا من اليمين.
االستخالص من اليسار :LEFT - ) =LEFT ( text , num_chars :textالنص الذي نريد أن نقتطع منه. :num_charsعدد األحرف التي نريد اقتطاعها بدءًا من اليسار .
المدرب :م .المنذر سفان
60
الداالت األساسية في برنامج االكسل
االستخالص من المنتصف :MID - ) =MID ( text , start_num , num_chars :textالنص الذي نريد أن نقتطع منه. :start_numرقم الحرف الذي نريد أن نبدأ باالقتطاع منه. :num_charsعدد األحرف التي نريد اقتطاعها بدءًا من الحرف المعين سابقًا .
التخلص من الفراغات الزائدة :TRIM - ) =TRIM ( text :textالنص الذي نريد أن نتخلص من الفراغات الزائدة بداخله. قــد تكــون هــذه المعادلــة هــي األكثــر اســتخدامًا مــن بقيــة معــادالت االقتطــاع ،فهــي تقــوم بحــل الكثيــر مــن المشــاكل وخصوصــً عندمــا نقــوم بالنســخ مــن برنامــج آخــر ،فتظهــر بعــض الفراغــات اإلضافيــة التــي يجــب التخلــص منهــا ،بحيــث تحــذف الفراغــات اإلضافيــة ببدايــة النــص أو نهايتــه وتبقــي علــى فــراغ واحــد بيــن الكلمــات
المدرب :م .المنذر سفان
MICROSOFT
EXCEL 2016