الدرس 60 (إجراء المعادلات بين ملفات الاكسل المختلفة) فى الاكسل Excel

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

الحالة الأولى: فى حالة كون ملف الاكسل مفتوح

عند الرغبة فى إجراء عملية حسابية على ملف اكسل مفتوح open workbook فنتبع الخطوات الآتية:-
1- نقوم بكتابة علامة = فى خلية الاكسل التى نريد إجراء المعادلة بها
2- من التبويب view نضغط على الأمر switch windows ثم نختر ملف العمل المفتوح الآخر
3- نضغط على الخلية التى نريد إدخالها فى المعادلة الحسابية فى الاكسل
4- نلاحظ أن برنامج الاكسل قام تلقائيا بكتابة اسم ملف الاكسل excel workbook متبوعا بامتداد الاكسل xlsx ثم متبوعا بصفحة العمل فى الاكسل worksheet  متبوعاً بعلامة التعجب ! ثم اسم الخلية.

الحالةالثانية: فى حالة ملفات الاكسل مغلقا

1- نقوم بكتابة علامة = فى خلية الاكسل التى نريد إجراء المعادلة بها
2- نقوم بكتابة امتداد ملف الاكسل المغلق بين علامتى اقتباس واسم الملف هنا الذى نريد إجراء العملية الحسابية عليه اسمه jan ويوجد على الامتداد d مع ملاحظة اسم الملف يكون بين قوسين مربعين [ ]
ثم نتبعه باسم ورقة العمل worksheet  واسم ورقة العمل فى هذا الملف khalid ثم نغلق علامة الاقتباس ثم نضع علامة التعجب ! ثم نضع اسم الخلية التى نريد إدخالها فى المعادلة الحسابية فى الاكسل وهى هنا الخلية F11
وليكن الامتداد هنا

='D:\[jan.xlsx]khalid'!F11

الدرس 59 (إجراء المعادلات بين صفحات العمل فى الاكسل )فى الاكسل excel

تحدثنا سابقا عن كيفية إجراء المعادلات والعمليات الحسابية فى صفحة عمل الاكسل worksheet
يمكن مراجعة درس العمليات الحسابية فى الاكسل بالضغط هنا 
 ولكن ماذا لو أردنا إجراء معادلة بين أكثر من صفحة من صفحات الاكسل
فمثلا إذا كان لدينا صفحة عمل worksheet  فى الاكسل تقوم بتجميع عدة أرقام من صفحات عمل أخرى
فمثلا لدينا ملف اكسل excel workbook يحتوى على عدد 3 شيت   worksheets كل شيت يحتوى على مجموع مبيعات أحد مندوي المبيعات وفى الشيت الثالث نريد إجمالى مبيعات مندوبي المبيعات الثلاث

ولإجراء العمليات الحسابية بين أكثر من صفحة عمل نتبع الآتى:-

1- لابد من كتابة اسم صفحة العمل متبوعة بعلامة التعجب! يعقبها مباشرة اسم الخلية التى نريد إجراء العملية الحسابية عليها
فمثلا لجمع الخلية  A6 فى صفحة عمل تسمى salary ونريد وضع الناتج فى خلية اخرى فى شيت آخر نكتب

=salary!A6

ولجمع محتوى الخلية A6 التى توجد فى صفحة عمل تسمى salary  مع محتوى الخلية F5 فى صفحة عمل أخرى تسمى total ونريد وضع الناتج فى صفحة عمل ثالثة نكتب اسم صفحة العمل salary متبوعة بعلامة ! ثم اسم الخلية A6 +صفحة العمل total متبوعة بعلامة! ثم اسم الخلية f5 لا ننسى وضع علامة = قبل العملية الحسابية كما هو موضح.

=ِsalary!A6+total!F5

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

ويمكن تسمية الخلايا التى تدخل فى العملية الحسابية كما سبق وتعلمنا كيفية تسمية الخلايا ونطاق الخلايا 

ثم نقوم بالعملية الحسابية مباشرة بإدخال اسماء الخلايا
فمثلا نقوم بتسيمة الخلية F15 والتى توجد فى شيت اسمه total باسم aly ونقوم بتسمية الخلية E10 الموجودة فى شيت اسمه salary باسم ahmed ولجمع الخليتين F15  و الخلية E10

=aly+ahmed

 مثال 2

سنقوم بإجراء نفس المثال السابق ولكن باستخدام تسمية نطاق الخلايا 

الدرس 58 (طريقة إخفاء المعادلات والصيغ Hidden function and formula ) فى الاكسل Excel

من الأمور الهامة فى برنامج الاكسل excel application هو تعلم كيفية إخفاء المعادلات عن الأشخاص غير المخول لهم هذا الأمر أو عن الأشخاص غير المرغوب إطلاعهم على معادلة معينة فى الاكسل .
وكنا قد تحدثنا فى الدرس السابق عن كيفية حماية ورقة العمل عن التعديل سوى فى خلايا محددة .

ولإخفاء المعادلات فى الاكسل نتبع الخطوات الآتية:-

1- نقوم أولاً بتحديد الخلايا التى تحتوى على معادلات ونريد إخفاء هذه المعادلات. ويمكن الضغط على مفتاح ctrl أثناء التحديد وذلك لتحديد أكثر من نطاق من الخلايا.
2- نقوم بالضغط كليك يمين Right click ثم نختار format cells
3- من التبويب protection نضغط على hidden ثم نضغط Ok
4- من التبويب review نضغط على protect sheet
5- نقوم بكتابة كلمة السر لحماية ملف الاكسل بحيث لا يستطيع سوى الشخص الذى معه كلمة السر التعديل عليه وإظهار المعادلات مرة أخرى .
6- نلاحظ أنه عند الضغط على أى خلية تحتوى على معادلة عدم ظهور هذه المعادلة فى شريط الصيغ formula bar.

ولإظهار المعادلات مرة أخري فى الاكسل

من التبويب review نضغط على Unprotected sheet وندخل كلمة السر
نلاحظ أنه عند الوقوف على الخلايا التى بها معادلات اكسل تظهر هذه المعادلات فى شريط الصيغ

ملحوظة
يمكن إخفاء شريط الصيغ فى الاكسل hidden formula bar
وذلك من التبويب view  ثم نزيل علامة الصح من أمام formula bar فنلاحظ عدم ظهور شريط الصيغ فى الاكسل

الدرس 57 (حماية ورقة العمل protect worksheet) فى الاكسل excel

يوفر برنامج الاكسل إمكانية حماية ورقة العمل فى الاكسل protect worksheet in excel application

فيمكن حماية بعض الخلايا    cells فى شيت الاكسل excel worksheet من التعديل عليها أو العبث بمكوناتها.

ولحماية بعض الخلايا من تعديلها وترك بعض الخلايا فقط للتعديل نتبع الخطوات الآتية:-

1- جميع الخلايا فى الاكسل افتراضيا تكون محمية locked ولكن تأثير هذه الحماية  لا يظهر فعليا إلا بعد تطبيق الأمر protect sheet
2-نحدد الخلايا التى نريد أن نتركها للمستخدم بدون حماية بحيث يمكن تعديل محتوياتها
3- نضغط كليك يمين right click فتظهر قائمة منسدلة نختر منها الامر format cells
4- فتظهر نافذة نضغط على التبويب protection ثم نزيل التحديد أمام الأمر locked
5- لتطبيق هذه الحماية من خلال التبويب Home نختر الأمر format ومنه نختر protect sheet
6- نلاحظ أنه الأمر   protect worksheet and contents of locked cells مفعل
7- يمكن وضع كلمة سر بحيث لا يمكن لأى شخص التعديل على هذه الحماية من خلال الأمر password to unprotect sheet
8- يمكن السماح للمستخدم بإجراء بعض الإجراءات على الخلايا المحمية من خلال الأمر allow all users of this worksheet to
9- ثم اخيرا نضغط ok
10- نلاحظ انه يمكن فقط التعديل على الخلايا غير المحمية
11- عند محاولة تغيير محتويات الخلايا المحمية نلاحظ ظهور رسالة من برنامج الاكسل تخبرك بعدم إمكانية تعديل محتويات هذه الخلايا حيث أن هذه الخلايا للقراءة فقط.

ملاحظة: 

يمكن حماية الخلايا المحددة  من خلال التبويب home ثم الأمر format نختر منه locked cell وبإعادة الضغط مرة أخرى على هذا الأمر يتم إلغاء الحماية من الخلايا المحددة.

يمكن تطبيق أمر حماية ورقة العمل من خلال التبويب review ثم protect sheet

يمكن أيضا تطبيق حماية ورقة العمل فى الاكسل من خلال الضغط كليك على الشيت المراد حماية الخلايا الموجودة به من التعديلات ثم نختر protect sheet

الدرس 56 (تشفير ملف الاكسل بكلمة سر encrypt with password ومنع التعديل على ملف الاكسل mark as final) فى الاكسل excel

تكلمنا فى الدرس السابق عن طريقة تعيين كلمة سر لملف الاكسل لفتحه  open password وكلمة سر أخرى لتعديله edit password  ولكن كان يمكن حفظ التغيرات التى تحدث على ملف الاكسل وذلك بحفظ ملف الاكسل باسم آخر .
وفى هذا الدرس سنشرح كيفية منع التعديل على ملف الاكسل وكيفية تشفيره بكلمة سر

كيفية تشفير ملف الاكسل بكلمة سر encrypt excel file with password

1- من قائمة file اضغط على Info
2- اضغط على protect workbook فتظهر قائمة منسدلة اختر منها encrypt with password
3- عند الضغط على encrypt with password ستظهر نافذة تطلب منك ادخال كلمة سر لتشفير وحماية ملف الاكسل protect excel file قم بادخال كلمة السر سيطلب منك البرنامج إعادة إدخال كلمة السر لتأكيدها فقم بإعادة إدخال كلمة وبهذا نكون قد انتهينا من حماية ملف الاكسل بوضع كلمة سر له.
4- عند إغلاق ملف الاكسل ومحاولة فتحه مرة أخرى تلاحظ ظهور رسالة تخبرك بضرورة إدخال كلمة السر حتى تتمكن من فتح الملف .
ملحوظة:
بعد إدخال كلمة السر فى ملف الاكسل ستلاحظ عند الضغط على قائمة file ومنها الضغط على Info ظهور رسالة أمام protect workbook بأن هذا الملف مشفر ويحتاج كلمة سر لفتحه.

منع تعديل ملف الاكسل

يمكن منع المستخدم من تعديل ملف الاكسل وذلك من خلال تمييز ملف الاكسل كملف نهائي  mark as finale
وذلك كالآتى:-
1- من قائمة file اختر Info ومنها اضغط على protect workbook ومنها قم باختيار الأمر mark as final
2- ستلاحظ أن جميع أوامر الاكسل اصبحت باللون الباهت دلالة على عدم امكانية تعديل الملف وستظهر رسالة فى أعلى ملف الاكسل تخبرك بأنه تم وسم الملف كملف نهائي
3- عند الرغبة فى تعديل الملف اضغط على الأمر edit any way الذى يظهر بجوار الرسالة
4- ويمكنك تعديل الملف أيضا بإلغاء الامر mark as final وذلك بالضغط عليه مرة اخرى من القائمة file ثم Info ثم protect workbook

الدرس 55 (تعيين كلمة سر set password لفتح أو تعديل open or modify ملف الاكسل) فى الاكسل excel

من أسهل طرق حماية ملفات الاكسل هو وضع أو تعيين كلمة سر لفتح أو تعديل ملف الاكسل excel work book
وهذه الطريقة مفيدة للحفاظ على حماية البيانات بحيث لا يستطيع أى شخص الوصول إلى أو تعديل ملف الاكسل excel

خطوات تعيين كلمة سر password لفتح Open أو تعديل modify ملف الاكسل excel workbook

1- قم بفتح ملف الاكسل المراد حمايته بكلمة سر
2- من قائمة file اختر save as
3- تلاحظ ظهور أمر بجوار كلمة save اسمه tools اضغط على السهم الأسود الصغير الموجود بجوار الأمر tools واختر general options
4-تلاحظ ظهور نافذة بها عدة أوامر منها أمر كلمة السر للفتح password to open وهنا تكتب كلمة السر التى تريدها عند الرغبة فى فتح ملف الاكسل وكلمة السر عند التعديل password to modify وفى هذه الخانة تكتب كلمة السر التى تريدها عند الرغبة فى تعديل ملف الاكسل
وبهذا يكون قد تم تعيين كلمة سر password لملف الاكسل excel أثناء الفتح أو التعديل
وعند فتح ملف الاكسل الذى تم حمايته بتعيين كلمة سر لفتحه وتعديله تلاحظ ظهور شاشة تطلب منك إدخال كلمة السر لفتح الملف وكلمة سر لتعديل الملف وإذا لم تدخل كلمة سر تعديل الملف سيظهر الملف فى وضع القراءة فقط read only وفى هذا الوضع لا يمكن تعديل الملف وإنما يمكن حفظ التعديلات التى تقوم بها على الملف باسم آخر

الدرس 54 (إنشاء قائمة منسدلة فرعية معتمدة على قائمة منسدلة رئيسية) فى الاكسل excel

تحدثنا فى درس سابق عن طريقة إنشاء قائمة منسدلة فى الاكسل من خلال data validation
يمكن مراجعة درس إنشاء قائمة منسدلة عن طريق الضغط على هذا الرابط  http://webw3s.blogspot.com.eg/2016/09/50-drop-down-list-excel.html
وستناول فى هذا الدرس طريقة إنشاء قائمة منسدلة drop down list معتمدة على أو مرتبطة بقائمة أخرى فى برنامج الاكسل excel .
وسنتذكر سويا طريقة إنشاء قائمة منسدلة عن طريق الأمر data validation
1- نقوم أولاً بتسمية نطاق الخلايا التى نريد وضعها فى القائمة المنسدلة drop down list
2 - نحدد نطاق الخلايا التى نريد تسميتها
3- من التبويب formula نضغط على create form selection
4- نلاحظ ظهور نافذة تخبرنا أنه سيتم انشاء اسم لهذا النطاق من خلال نختار على top row فيتم اختيار اسم النطاق هنا courses
5- من خلال التبويب data نختر data validation ومن allow نختر list,
6- ثم من التبويب setting من source نكتب courses=حيث أن course هو اسم النطاق الذى تم تحديده
7- نلاحظ أنه تم انشاء قائمة منسدلة

استخدام الدالة Indirect فى إنشاء قائمة منسدلة معتمدة على قائمة منسدلة أخرى

وظيفة الدالة indirect 

تعمل على إرجاع المرجع المحدد  وعرض محتوياته

خطوات إنشاء قائمة منسدلة فرعية معتمدة على قائمة منسدلة رئيسية

1- نقوم بكتابة بيانات القائمة المنسدلة الرئيسية ثم من التبويب formula نضغط على define name ونختار اسم معين لهذا النطاق 

2- نقوم بكتابة بيانات القائمة المنسدلة الفرعية ثم من التبويب formula نضغط على create form selection

3- نقوم بتحديد نطاق الخلايا التى نريد أن نطبق عليها القائمة الرئيسية ثم من التبويب data نختر data validation ومن التبويب setting  من الأمر allow نختر list ومن source  نكتب اسم نطاق الخلايا التى تم تحديدها فى القائمة الرئيسية. 

فلو مثلا قد قمنا بإعطاء القائمة الرئيسية اسم "محافظات" فإننا فى خانة source نكتب محافظات=

المقصود بنطاق الاسماء الخاص بالقائمة الرئيسية هنا هو " name range" او الاسم الذى قمنا بإعطاءه للقائمة الرئيسية عندما قمنا بتسميتها.

وبهذا نكون قد قمنا بإنشاء القائمة الرئيسية

ولإنشاء القائمة الفرعية المعتمدة على القائمة الرئيسية

4- ثم نقوم بتحديد نطاق الخلايا التى نريد أن نطبق   عليها القائمة الفرعية ثم من التبويب data نختر data validation   ومن التبويب setting  نختر من الأمر allow   القائمة list ومن الخانة source نكتب 

=indirect(اسم أول خلية فى نطاق الخلايا الخاصة بالقائمة الرئيسية)

فلو كانت الخلايا الخاصة بالقائمة الرئيسية تبدأ بالخلية E5 فإننا نقوم بكتابة الأمر  فى الخانة source 

=indirect(e5)  

نتأكد جيدا من إزالة تثبيت الخلايا 

الدرس 53 (شرح طريقة تحديد طول المدخلات ونوع البيانات فى الخلية ) فى الاكسل excel

يمكن التحكم فى تحديد طول المدخلات وكذلك نوع البيانات فى خلية الاكسل excel
وللتحكم فى طول المدخلات فى خلية الاكسل

عن طريق data validation

1- حدد أولاً الخلايا المراد تطبيق قاعدة التحقق من صحة المدخلات data validation والمراد إدخال رقم البطاقة فيها بحيث يساوى 14 رقم
2- من التبويب data اختر data validation
3- تلاحظ أنه تم فتح نافذة data validation من التبويب setting اختر text length من الأمر allow
4- من الأمر data اختر equal to  ومن الأمر length اختر 14
5- يمكن من التبويب error alert ادخال رسالة الخطأ التى ستظهر لمدخل البيانات فى حالة إدخال بيانات خاطئة .
6- يعيب هذه الطريقة أنها تقبل الحروف أيضا ولذا ففى حالة نسيان المستخدم وقيامة بإدخال أى حرف سيتم قبوله .

تعديل الطريقة بحيث يتم إدخال أرقام فقط.

 يمكن تعديل هذه الطريقة بحيث تقبل أرقام فقط وذلك كالتالي:-د
1- يوجد شرطان أساسيان لابد من أن يتحقق كلاهما حتى تكون المدخلات صحيحة
أ- أن يكون طول المدخلات =14
ب- أن يكون نوع المدخلات أرقام
2- نستخدم دالة and  لأنها الدالة التى تتطلب تحقيق جميع الشروط حتى تعطى true
وذلك كالتالي:-
1- نحدد جميع الخلايا التى نريد تطبيق قاعدة التحقق من صحة المدخلات عليها data validation
2- من التبويب settings  من allow نختر custom
3- من formula نكتب

 =AND(LEN(F5)=14,ISNUMBER(F5))

4- نلاحظ أنه لن يتم قبول سوى عدد 14 رقم على الخلايا التى تم اختيارها ولن يسمح مطلقا بدخول الحروف.

الدرس 52 (شرح دالة حساب طول النص len) فى الاكسل excel

فى هذا الدرس سنقوم بشرح دالة حساب (عدد الحروف فى سلسلة نصية ) len فى الاكسل excel

الصيغة العامة للدالة

=len(text)

وكما نرى تأخذ هذه الدالة وسيط واحد فقط هو text أو نص

فلمعرفة طول نص معين نقوم بإدخال النص بعد أقواس الدالة len ويجب وضع النص بين علامتى تنصيص ""

ويمكن أيضا وضع اسم الخلية التى تحتوى على النص الذى نريد حساب طوله بين أقواس الدالة Len

مثال

=len("aly")

3

حيث تقوم الدالة بحساب عدد الحروف المكونة لكلمة "aly" وإرجاع الناتج وهو عدد الحروف 3 حروف

مثال آخر

=len(" aly")

4

نلاحظ فى المثال السابق أننا  قمنا بوضع مسافة قبل الكلمة "aly " وهذه المسافة يتم احتساب طولها لذا فنجد طول هذا النص هو 4

الدرس 51 (تسمية الخلية أو نطاق من الخلايا ) فى الاكسل excel

يتيح لنا برنامج الاكسل تسمية الخلية أو نطاق من الخلايا باسم معين وذلك لأن تذكر الأسماء أسهل من تذكر الأرقام
وتوجد عدة طرق لتسمية الخلية أو نطاق من الخلايا

- عن طريق name box

• قم بتحديد الخلية أو نطاق الخلايا التى تود تعيين اسم لها
• من خانة name box قم بكتابة اسم معين لهذا النطاق ثم اضغط enter
• إذا ضغطت على السهم الصغير الموجود بجوار خانة Name box تلاحظ وجود قائمة لجميع الأسماء التى تم تعيينها للخلايا أو لنطاقات معينة من الخلايا وبمجرد الضغط على أى اسم من هذه الأسماء تلاحظ أنه تم تحديد نطاقات الخلايا الخاصة به.

عن طريق الأمر define name

يمكن تغيير اسم الخلية أو نطاق من الخلايا المحددة عن طريق الأمر define name الموجود بالتبويب formulas
وذلك باتباع الخطوات الآتية:-
 حدد نطاق الخلايا التى تريد تعيين اسم له
من التبويب formulas اختر الأمرdefine name
عند الضغط على الأمر  define name  ستظهر نافذة موضحة بالصور يمكن عن طريق name اختيار اسم نطاق الخلايا المحددة ومن comment يمكن وضع تعليق للنطاق المحدد
ومن الأمر refer to يشير إلى موضع الخلايا ويمكن إعادة تحديد الخلايا من هذا الأمر.

عن طريق الأمر create from selection

يمكن أيضا تعيين أسماء لنطاقات الخلايا المحددة وذلك عن طريق الأمر create from selection الموجود فى التبويب formulas وهذا الأمر  يمتاز عن مجموعة الأوامر السابقة بأنه يقوم بتعيين الاسم عن طريق عنوان النطاق فمثلا  عند اختيار نطاق الخلايا من G9:G17  وهو النطاق المكون للدرجات اللغة الانجليزية فى الجدول نلاحظ أنه يظهر أمر create names form values in the
ويتبعه عدة اختيارات كما هو موضح بالصورة ونختار مكان العنوان فإذا كان العنوان الأعلى نختار top row
وإن كان عنوان الخلايا جهة اليمين right column  وهكذا

من خلال name manager

يمكن أيضا تعيين أسماء لنطاقات الخلايا المحددة وذلك عن طريق الأمر name manager الموجود فى التبويب formulas وهذا الأمر يمتاز بأنه يمكن من خلاله إنشاء اسم لنطاق الخلايا عن طريق الأمر new  او تعديل نطاق الخلايا المحددة أو تغيير الاسم عن طريق edit أو حذف نطاق محدد عن طريق الأمر delete

الدرس 50 (إنشاء قائمة منسدلة drop down list) في الاكسل excel

يمكن إنشاء قائمة منسدلة drop down list فى الاكسل
ويوجد نوعان من القوائم المنسدلة
1- القائمة المنسدلة الثابتة
وهى تمتاز بعدد عناصر ثابتة ومحددة
2- القائمة المنسدلة المطاطية:
ويمكن من خلالها زيادة عدد العناصر تبعا لاحتياجنا
وسنتناول بالشرح المبسط الطريقتان

1- إنشاء قائمة منسدلة ثابتة

• ضع المؤشر فى الخلية التى تريد إنشاء قائمة منسدلة فيها
• من التبويب data اذهب إلى الأمر data validation
• اضغط على data validation و من التبويب setting  اختر list
• من source اضغط على السهم الصغير وحدد الخلايا التى تريد إدخالها فى القائمة المنسدلة
• تلاحظ ظهور سهم أسود صغير عند الضغط عليه تظهر القائمة المنسدلة

ملاحظة:

يمكن كتابة عناصر القائمة المنسدلة مباشرة فى الامر source

ويتم الفصل بين كل عنصر وآخر من عناصر القائمة المنسدلة بالفاصلة المنقوطة ;

2: إنشاء قائمة منسدلة مطاطية

من عيوب القائمة المنسدلة الثابتة أننا إذا أضفنا عناصر أخرى فى الخلايا الموجودة أسفل القائمة لا يتم إدخالها إلى القائمة المنسدلة

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

(adsbygoogle = window.adsbygoogle || []).push({});
• ضع المؤشر فى الخلية التى تريد إنشاء قائمة منسدلة فيها
• من التبويب data اذهب إلى الأمر data validation
• اضغط على data validation و من التبويب setting  اختر list
• من source اضغط على السهم الصغير وحدد العمود الذى به العناصر إلى تريد إضافتها للقائمة 
• تلاحظ ظهور سهم أسود صغير عند الضغط عليه تظهر القائمة المنسدلة

نلاحظ أننا قمنا هنا بتحديد العمود بأكمله حتى يمكن إضافة بسهولة أى عناصر إلى القائمة المنسدلة

الدرس 49 (وضع دائرة حول البيانات غير الصحيحة circle invalid data ) فى الاكسل excel

تكلمنا فى الدرسين السابقين عن آداة التحقق من صحة البيانات المدخلة .
وفى هذا الدرس سنتناول شرح كيفية وضع دائرة حول البيانات غير الصالحة circle invalid data ( البيانات غير المطابقة للمعايير) التى تم وضعها فى أداة التحقق من صحة البيانات data validation  فى الاكسل excel.

فمثلا فى مسابقة لإحدى الوظائف كان المطلوب أن يكون عمر المتقدم للوظيفة يترواح بين 20 و30 عاماً والآن أمامنا قائمة كبيرة بالأسماء وأمام كل اسم عمره ونريد وضع دائرة حول الأعمار غير المطابقة للمعايير وذلك لتمييز هذه البيانات لأنها غير صحيحة عن باقى البيانات الأخرى المطابقة للمعايير

 ولوضع دائرة حول البيانات غير الصحيحة invalid data

اذهب إلى  التبويب data اضغط على السهم الصغير الموجود بجوار الأمر data validation واختر منه circle invalid data

لإزالة الدائرة حول البيانات غير الصحيحة invalid data

اذهب إلى  التبويب data اضغط على السهم الصغير الموجود بجوار data  validation  واختر منه clear  validation circles

الدرس 48 (آداة التحقق من صحة البيانات data validation) في الاكسل excel الجزء الثانى

تكلمنا فى الجزء الأول من شرح آداة التحقق data validation عن كيفية إنشاء هذه الآداة وفى هذا الجزء سنكمل الشرح

إنشاء رسالة إدخال للمستخدم

تتيح آداة التحقق data validation خاصية إدخال رسالة للمستخدم تظهر هذه الرسالة عند تحديد الخلية للكتابة بداخلها .
وهذه الرسالة يمكن الكتابة بداخلها بحيث توضح للمستخدم نوع البيانات التى يمكن إدخالها فى هذه الخلايا
ولحذف هذه الرسالة نضغط على clear all من التبويب input message

إنشاء رسالة تحذيرية للمستخدم

عند قيام المستخدم بإدخال بيانات خاطئة يمكن إدخال رسالة تحذيرية توضح للمستخدم أنه قام بإدخال بيانات خاطئة مع كتابة البيانات الصحيحة التى يمكن إدخالها  وذلك من التبويب error alert
ويوجد ثلاثة أنواع من رسائل التحذير

stop

وهى الوضع الافتراضي وعند اختيار هذا النوع من الرسائل غير مسموح بإدخال قيم غير القيم التى تم إدخالها فى أداة التحقق data validation وحين حدوث خطأ وقمت بإدخال قيم مخالفة للمعيار الذى تم وضعه تلاحظ أن الرسالة  تظهر هكذا

warning 

عند اختيار الاختيار warning وقام المستخدم بإدخال بيانات خاطئة لا تخضع للمعيار الذى قمت بوضعه نلاحظ ظهور الرسالة وتوضح هذه الرسالة أنه يمكن الاستمرار فى كتابة القيمة الخاطئة أو التراجع عنها فإذا قمت بالضغط على yes سيتم قبول البيانات المخالفة للمعايير إما إذا اخترت No فلن يتم إدخال البيانات المخالفة وسيتم العودة للخلية فى وضع التحرير.

information
عند اختيار هذا الاختيار وفى حالة إدخال بيانات مخالفة للمعيار الذى قمت بوضعه نلاحظ ظهور رسالة التحذير ويمكن تخطى المعايير بالضغط على Ok أما عند الضغط على الامر cancel فيتم العودة إلى الخلية المحددة.

حذف آداة التحقق data validation

يمكن بسهولة حذف أمر التحقق من صحة البيانات data validation وذلك بالضغط على  الآداة data validation واختيارclear all من التبويب setting

الدرس 47 (آداة التحقق من صحة البيانات data validation) في الاكسل excel الجزء الأول

قد يقوم المستخدم بإدخال بيانات خاطئة ..لذا قام برنامج الاكسل بتوفير آداة رائعة تعمل على التحقق من صحة البيانات المدخلة ومنع أو إظهار رسائل فى حالة إدخال بيانات خاطئة invalid data وهذه الآداة هى آداة data validation

الوصول إلى آداة التحقق من صحة البينات المدخلة  data validation

من التبويب data اختر data validation

ونلاحظ عند الضغط على data validation ظهور نافذة data validation ومن التبويب setting يظهر الأمر allow حيث يتحكم هذا الأمر فى نوعية البيانات المدخلة 
any value : يقوم بالسماح بأى نوع من المدخلات
whole number : يقوم بالسماح بإدخال الأرقام الصحيحة ولا يسمح بإدخال الأرقام العشرية .
decimal number : يسمح بإدخال الأرقام العشرية
list:  يسمح بإدخال قائمة منسدلة drop menu
date: يسمح بإدخال تاريخ 
time: يسمح بإدخال الوقت
text length: يسمح بإدخال نص بطول محدد
custom  : تخصيص لإدخال بيانات أخرى أو معادلات

ومن الأمر data نلاحظ أنه يحتوى على العديد من الأوامر الفرعية

between: 
حيث تكون الأرقام المدخلة بين قيمتين قيمة عظمى وقيمى صغرى

not between
حيث تكون الأرقام المدخلة لا تكون بين قيمتين قيمة عظمى وقيمة صغرى
equal to
حيث تكون البيانات المدخلة تساوى قيمة معينة

not equal to
حيث تكون البيانات المدخلة لا تساوى قيمة معينة
greater than
حيث تكون الأرقام المدخلة أكبر من قيمة صغرى يتم تحديدها

less than
حيث تكون الأرقام المدخلة أقل من قيمة عظمى يتم تحديدها
greater than or equal to
حيث تكون الأرقام المدخلة أكبر من أو تساوى قيمة صغرى يتم تحديدها
 less than or equal to
حيث تكون الأرقام المدخلة أقل من أو تساوى قيمة عظمى يتم تحديدها

مثال
عند إدخال عمر الموظف يجب أن يكون العمر بين 20 و59 سنة
لذا نقوم بإدراج قاعدة تحقق من صحة البيانات المدخلة data validation على هذا النحو الموضح بالصور 

الدرس 46 (البحث عن البيانات المكررة وحذفها remove duplicates) فى الاكسل excel

من أدوات الاكسل الهامة المفيدة آداة حذف البيانات المكررة remove duplicates
وكما هو واضح من اسمها هذه الآداة تقوم بحذف البيانات المكررة أو البيانات المتشابهة

ولكن قبل التطرق لكيفية حذف البيانات المكررة فى الاكسل سنتناول كيفية البحث عن البيانات المكررة 

1- حدد نطاق الخلايا التى تريد البحث فيها عن البيانات المكررة
2- من التبويب Home اختر conditional formatting
3- اضغط على highlight cells rules
4- اختر duplicate values
5- اختر من النافذة التى تظهر duplicate واختر التنسيق المناسب
ستلاحظ أنه تم تحديد البيانات المتشابهة بلون معين  كما هو واضح بالمثال

كيفية حذف البيانات المكررة remove duplicates

1- حدد نطاق الخلايا التى تريد حذف البيانات المكررة منها.
2- من التبويب data اختر remove duplicates
3- من النافذة التى تظهر حدد الأعمدة التى تريد البحث فيها عن البيانات المتشابهة ليتم حذفها ثم اضغط Ok
4- تلاحظ ظهور رسالة توضح عدد الصفوف التى تم حذفها وعدد الصفوف التى تحتوى على بيانات فريدة .

الدرس 45 (التنسيق الشرطى conditional formatting باستخدام المعادلات formula) فى الاكسل excel

إن استخدام التنسيق الشرطى  conditional formatting المعتمد على المعادلات formula فى الاكسل excel يخرج بالتنسيق الشرطى من الحيز الضيق إلى أفكار اكثر تنوعا وتطويع المعادلات فى أفكار اكثر ابتكارا
مثل تنسيق وتلوين الصفوف الفردية فى الاكسل بتنسيق ولون مختلف عن لون الصفوف الزوجية

خطوات تطبيق التنسيق الشرطى :-

لعمل تنسيق شرطى يقوم بتنسيق الصف الفردية وتلوينها بلون وتنسيق مختلف عن الصفوف الزوجية نقوم بعمل الآتي:-

1- نقوم أولاً بتحديد كامل النطاق الذى نريد تطبيق التنسيق الشرطى عليه.
2- من التبويب Home نختار conditional formatting  ومنها نختر new rule
3- نختر use a formula to determine which cells to format
4- نكتب فى خانة format values where this formula is true المعادلة التالية:-

 =mod(row();2)

6- من format نقوم باختيار التنسيق المناسب الذى نريد ظهوره فى حالة تحقق الشرط . ثم Ok

شرح المعادلات:-

تكلمنا سابقا عن معادلاتىmod وrow

1- mod تقوم بإرجاع باقى القسمة

2- row تقوم بإرجاع رقم الصف 

لذا فهذه المعادلة mod(row();2) تقوم هذه المعادلة بإرجاع باقى قسمة الصف على 2 فإذا كان باقى القسمة يساوى 1 يظهر التنسيق الذى قمنا بإعداده وإذا كان باقى القسمة يساوى صفر لا يظهر التتنسيق الخاص بالصفوف

مثال آخر : فى البحث وتظليل الخلية التى تطابق نتيجة البحث 

ولعمل ذلك نتابع الخطوات فى الصورة التالية:-

1- نقوم أولاً بتحديد كامل النطاق الذى نريد تطبيق التنسيق الشرطى عليه.
2- من التنسيق Home نختار conditional formatting  ومنها نختر new rule
3- نختر use a formula to determine which cells to format
4- نكتب فى خانة format values where this formula is true المعادلة التالية:-

 =$K$2=$F5

حيث أن K2 هو اسم الخلية التى نكتب فيها جملة البحث وقد قمنا بتثبيتها 

F5 هو اول خلية فى عمود البحث وقد قمنا بتثبيت هذا العمود.

6- من format نقوم باختيار التنسيق المناسب الذى نريد ظهوره فى حالة تحقق الشرط . ثم Ok

الدرس 44 (شرح الدالة ROW) فى الاكسل excel

تعتبر الدالة ROW من دوال الاكسل EXCEL البسيطة ,وهى تندرج تحت  دوال البحث وهى دالة غير معقدة ولا تحتاج سوى إلى مرجع واحد فقط هو مرجع الخلية التى يتم إدخالها للدالة فتقوم الدالة بإرجاع رقم الصف الذى به الخلية  وقلما أن تستخدم هذه الدالة بمفردها إذ أنها تستخدم عادة مع دوال آخرى لتحقيق مهام متقدمة.

وظيفة الدالة ROW

تقوم الدالة ROW بإرجاع رقم الصف للخلية المرجعية التى تم إدخالها للدالة

بناء الدالة row

ROW([reference])‎

حيث أن reference هو الخلية التى تريد إرجاع رقمها.

ملاحظة 

• فى حالة إدخال نطاق من الخلايا للدالة ROW فإن الدالة سوف تقوم بإعادة أول رقم صف من الخلايا الداخلة فى النطاق.
• إذا لم يتم إدخال أى قيم للدالة row فستقوم الدالة بإرجاع رقم الصف للخلية  التى بها الدالة.

استخدام الدالة row فى الترقيم التسلسلى للأرقام

يمكن استخدم الدالة row فى عمل الترقيم التسلسلى للأرقام وذلك من خلال الأمر

=row(a1)

وبعد ذلك قم بنسخ الدالة  لأخر العمود مما يلاحظ معه إنشاء ترقيم تسلسلي للأرقام 1-2-3-...

.

الدرس 43 (شرح دالة countifs) فى الاكسل excel

تعتبر دالة countifs إحدى دوال الاكسل excel الإحصائية وهى دالة حديثة نسبيا حيث أول استخدام لها كان فى نسخة اكسل excel 2007
وهى شبيهة فى عملها بدالة countif ولكنها تختلف عنها فى استخدامها أكثرمن معيار ولكن دالة countif تستخدم معيار واحد فقط.
يمكنك الرجوع إلى درس شرح دالة countif بالضغط هنا

وظيفة الدالة countifs

تقوم دالة countifs بحساب عدد تكرار مجموعة من البنود فى جدول محدد.

بناء الدالة countifs

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)‎

حيث أن :-

 criteria_range1        المدى الأول أو النطاق الأول 

 criteria1: هو الشرط أو المعيار الأول 

criteria_range2: المدى الثانى  أو النطاق الثانى 

 criteria1: هو الشرط أو المعيار الثانى 

مثال :

فى الجدول التالي:-  مطلوب معرفة عدد الذين يعملون على وظيفة معلم - معلم أول - معلم مساعد- معلم خبير- كبير معلمين فى كل مادة دراسية على حدة .

لذا نقوم باستخدام دالة countifs كما هو موضح فى المثال التالي:-

اضغط لتحميل ملف الاكسل ومعه المثال