تلميحات خاصة في برنامج اكسل Excel

م. محمد الحمامي
نقدم في هذه المقالة بعض التلميحات في برنامج معالجة البيانات الجدولية من شركة Microsoft تتعلق بإنشاء المخططات البيانية والدوال الرياضية وبرمجيات Macro وكيفية إنشاء ورقة تضم قائمة بورقات العمل التي ترغب بالتعامل معها باستمرار.
كيفية عمل مخطط بياني في برنامج اكسل

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

2. بعد ذلك تظهر لك هذه النافذة  وهي معالج المخططات (هذه النافذة تسألك ما هو شكل الرسم البياني الذي يريحك ويخدم عملك ثم بعد ذلك تضغط أمر التالي .

3. ثم بعد ذلك تظهر نافذة تبين لك الشكل الذي اخترته لعمك ثم تضغط على زر التالي .

4. ثم بعد ذلك تظهر لك هذه النافذة كي تضع عنوان للمخطط البياني وللمحور السيني والمحور الصادي مثل كما هو موضح بالرسم ثم تضغط على زر التالي .

5. ثم بعد ذلك تظهر لك نافذة تسألك أين ستضع المخطط البياني في صفحة مستقلة أم في الصفحة الموجودة فيها جدولك .

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

1.  ندخل أسماء الطلاب في العمود الأول وتقابلها درجاتهم في العمود الثاني كما في الصورة التالية .

2.  الآن ننشئ الدالة ( دالة احتساب النسبة المئوية ) في أول خانة ( حقل ) في العمود الثالث أي الحقل C2 كما في مثالنا هذا . والدالة هي عبارة عن الصيغة التالية الموجود بين القوسين بدون القوسين ( =B2/1800*100 ) حيث :
   • B2 هي خانة الدرجة المجاورة للحقل C2 بمعنى أنها هي الدرجة التي نرغب في احتساب النسبة المئوية لها .
   • 1800 هي الدرجة العظمى ( القصوى ) أي أعلى درجة يمكن أن يحصل عليها الطالب .
   • 100 هذا ثابت لاحتساب النسبة المئوية وبدونه تكون النسبة مابين صفر وواحد .
   والصورة التالية توضح أكثر :

3. بعد ذلك نسحب من الزاوية السفلى من اليسار للحقل C2 حتى نصل إلى الحقل C17 ثم نتوقف عن السحب وستظهر النسب المئوية لجميع الطلاب.
   
4. بعد ذلك نختار كل العمود من دون العنوان طبعا ( أي كل النسب المئوية الموجودة). ثم ( نعمل نسخ وذلك من قائمة Edit>Copy) أو نضغط على Ctrl+C معا، ونبقي الاختيار في مكانه ونذهب إلى Edit>Paste Special>Value ثم نضغط OK وستظهر القيم نفسها ولكن نحن بذلك نكون قد تخلصنا من صيغة المعادلة ( معادلة احتساب النسبة المئوية وثبتنا نتائجها فقط بدون العبارة أو الصيغة ) والشكل التالي يوضح أكثر :
   
   
   

3. بعد أن نضغط على الزر Ok في مربع الحوار (Paste Special) ونعود إلى ورقة العمل نضغط على الزرEnter مرة واحدة فقط لنلصق البيانات في نفس مكانها .  بعد ذلك ننسخ الأسماء نسخ عادي ونلصقها إلصاق عادي في العمود D.
4.  الآن سنجري عملية الترتيب، في البداية نختار العمودين C وD بدأ من الحقل D2 وحتى C17 ثم نذهب إلى Data>Sort أو بيانات > ترتيب، وبعد ذلك سيظهر لنا مربع حوار في البداية ذهب لأسفل مربع الحوار ونختار No Header أي أن الأعمدة التي اخترتها لا تحتوي رؤوس أو عناوين لها ( أي أنها قيم فقط ) بعد ذلك نختار في الخيار الأول Sort By Column C Descending Then by Column D Descending  وهذا يعني ( رتب حسب العمود C تنازليا ثم حسب العمود D تنازليا ) واختيار تنازليا أو تصاعديا يكون حسب رغبتنا فإذا أردناه أن يبدأ بالقيمة الكبرى ثم الصغرى وهكذا نضع تنازليا وإذا أردناه أن يبدأ بالصغرى ثم العظمى نضع تصاعديا .
   وفي الشكل التالي إيضاح أكثر :
   
   
   
5. بعد ذلك سوف يتم الترتيب من الأعلى للأسفل بحسب النسبة المئوية. ولكن لاحظ ترتيب النسب لا يرتبط بترتيب الأسماء والدرجات في العمودين الأولين بل يرتبط بترتيب الأسماء في العمود الأخير .

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



سنبدأ بتطبيق دالة (أقل قيمة وأعلى قيمة ) في مثالنا أقل درجة وأعلى درجة يفضل أن تكون درجات الطلاب في العمود D ومابين D3 و D25 لتنطبق الدوال التي في المثال دون الحاجة لتعديلها أو انك ستضطر إلى تعديل مجال الدالة بحسب مجال الدرجات التي تضعها .

لنكتب مثلا في الحقل G3 "أعلى درجة" وفي الحقل المقابل له H3 نكتب في الحقل =Max(D3:D25) وكلمة Max هي دالة استخراج القيمة العظمى وصيغتها كالتالي Max(Range) وبإمكاننا تجاوز كتابة الدالة وذلك بالذهاب إلى إدراج > دالة ومن مربع الحوار نختار الكل ونختار Max وعندما يظهر لنا مربع الحوار الخاصة بالدالة Max نختار القيم أو الدرجات وذلك عن طريق السحب بمؤشر وبذلك نكون قد اخترنا المجال أو مدى القيم التي يتم استخراج الدالة منها وفي الصور التالية زيادة في الإيضاح .









بعد ذلك نكتب في الحقل G4 "أقل درجة" وفي الحقل المقابل له H4 نكتب الدالة =Min(D3:D25) وطبعا كلمة Min هي دالة استخراج القيمة الصغرى وبإمكاننا اتباع الطريقة التي في الصور السابقة مع التغيير عند اختيار الدالة بدلا من اختيار Max نختار Min ، وهكذا نكون قد استخرجنا القيمة الصغرى والكبرى من درجات الطلاب الآن سنتعلم كيف نستخرج المتوسط (متوسط درجات الطلاب ) لنذهب إلى الحقل G5 ونكتب " متوسط الدرجات" ونذهب للحقل المقابل له H5 ونكتب الدالة التالية =Average(D3:D25)  أو باتباع نفس الخطوات السابقة وبعد ذلك سنتعلم كيفية استخراج الانحراف المعياري لدرجات جميع الطلاب عن المتوسط وذلك باستخدام الدالة STDEVP فنذهب للحقل G6مثلا ونكتب فيه الانحراف المعياري ونذهب بعد ذلك إلى الحقل H6 نكتب فيه الدالة =STDEVP (D3:D4) أو نتبع الطريقة الموضحة في الصور السابقة مع تغيير ما يلزم.
والآن سنتعلم كيف نستخرج عدد الطلاب أو عدد القيم وهنا لدينا خيارين إما أن نجعل الدالة تعد قائمة أسماء الطلاب أو تعد قائمة درجاتهم ولنبدأ بالدالة التي تعد حقول درجات الطلاب وصيغتها كالتالي =count(D3:D25) أو نتبع أسلوب الصور مع تغيير ما يلزم أيضا ، أما في حالة أردنا عد الأسماء فلن نستخدم الدالة نفسها لان الدالة السابقة لا تعد إلا الأرقام فقط أي لا تعد Text والقيم المنطقية وغيرها ، ففي حالة أردنا العد عن طريق قائمة الأسماء نضيف الحرف a على كلمة Count فتصبح الدالة كالتالي Counta(A3:A25) وبهذا يتم العدّ عن طريق الأسماء ويمكننا اتباع الخطوات الموضحة في
الصور مع تغيير ما يلزم أيضا .
أما الآن سنتعلم كيف نصنف الطلاب إلى مجموعتين بحسب درجاتهم مجموعة الناجحين ومجموعة الراسبين . وهنا سنستخدم دالة شرطية ولكن قبل البدء في ذلك نضع عمود جديد ونضع عنوان الحالة مثلا بجانب عمود الدرجات.
بعد عمل ذلك يجب نذهب لأول حقل في الحالة نضع الدالة التالية =IF(D3>60;"PASS";"FAIL") وهنا سأشرح هذه الدالة لأنها الهم في وجهة نظري، كلمة IF تعني إذا D3>60 وها هو الشرط وقد افترضت أن درجة النجاح هي 60 فوضعت الشرط أي إذا أن الدرجة الموجودة في الحقل D3 أعلى من الدرجة 60 ;"PASS" وتعني انه إذا تحقق الشرط ضع كلمة PASS والتي تعني اجتياز المادة (النجاح). "FAIL" وتعني إذا لم يتحقق الشرط ضع كلمة FAIL وتعني رسوب الطالب في المادة بعد أن تكتب الصيغة السابقة في الحقل المجاور لدرجة الطالب الأول عليك أن تسحب النقطة مربعة الشكل التي في زاوية الحقل ، تسحب حتى تصل آخر حقل تريد تعبئته بالصيغة نفسها وفي الصورة التالية إيضاح أكثر.

أما الآن سنتعرف على دالة العد المشروطة فمثلا سنستخرج من البيانات الموجودة كم عدد الطلاب الناجحين للمادة وكم عدد الراسبين فيها، فلنذهب إلى G7 ونكتب " عدد الطلاب الناجحين " ونذهب بعدها إلى H7 نضع الدالة التالية =countif(E3:E25;"PASS") وبهذا نستخرج عدد الطلاب الناجحين أما بالنسبة للراغبين فتكون الدالة =countif(E3:E25;"FAIL") وطبعا في المثال كان مجال عمود الحالة من E3 إلى E25 .


إنشاء قائمة في مصنف اكسل
وهي طريقة للوصول إلى ورقات العمل سريعاً . إذا كنت مثل معظم مستخدمي مايكروسوفت اكسل، فإنك ربما تملك مجموعة من الجداول الممتدة التي تتعامل معها كثيراً، لكنك تضطر إلى الانتقال في أشجار الأدلة كي تجد كلاً منها وهو أمر مزعج أحياناً. توجد طريقة ممتازة لوضع جميع ملفات العمل قرب أطراف أصابعك، و هي إنشاء قائمة مصنف عمل (Workbook) ترتبط بجميع الملفات التي تستخدمها، بحيث تصبح مسألة فتح الملف مجرد النقر على أيقونته أو وصفه . القوائم وسيلة جيدة في بيئة المكتب، حيث يتشارك العديد من المستخدمين، وحتى بعض المستخدمين المؤقتين على التعامل مع المصنفات. ولأن قائمة المصنف تظهر أينما يتم فتح اكسل، فإنه لا حاجة بالمستخدم أن يعرف أين توجد الملفات فعلياً، ويمكنه البدء بالتعامل معها مباشرة .
قائمة المصنف هي ببساطة مصنف اكسل طبيعي مفصل حسب الطلب كي يبدو شبيه بالقائمة، أكثر من المصنف. وتستخدم وصلات متشعبة في المصنف لربط الملفات التي تفتحها بشكل متكرر، ويمكن أن تربطها بالصور أو النصوص أو كليهما معاً. تتصرف قائمة المصنف وكأنها صفحة ويب، ويمكن أن تحتوي على وصلات إلى ملفات أخرى، مثل وثائق وورد، أو صفحات ويب .
لإنشاء قائمة مصنف، افتح مصنفاً جديداً، وأزل جميع الورقات (sheets) فيه باستثناء الورقة الأولى مستخدماً "تحرير|حذف ورقة " (Edit|Delete Sheet) ، ثم كبر ورقة العمل إلى الحد الأقصى لملء نافذة اكسل، ونظّف العرض باختيار لسان التبويب "أدوات|خيارات|عرض " (Tools|Options|View) ، وإزالة اختيار مربعات اختيار "رؤوس الصفوف والأعمدة " (Row & column) ، و"خطوط الشبكة " (Gridlines) ، و"شريط التمرير الأفقي " (Horizontal scroll bar) ، و"شريط التمرير العمودي " (Vertical scroll bar) ، و"علامات تبويب الأوراق " (Sheet tabs). ويمكنك أن تجعل الصفحة أكثر جاذبية بإضافة صورة خلفية، من "تنسيق|ورقة|خلفية " (Format|Sheet|Background) ، واختيار صورة من قرصك الصلب، أو يمكنك أن تستخدم زر "لون التعبئة " (Fill Color) ، على شريط الأدوات "تنسيق " (Formatting) ، لاختيار لون معين للأرضية .
تمثل الأيقونات أو الصور طريقة سهلة للتعرف على الملفات ثم تحميلها. أضف صورة إلى ورقة العمل لكل ملف تريد أن تفتحه منها، ويمكنك أن تجلب الصورة من مجموعة clip-art المتوفرة لديك، أو من قرصك الصلب. بعد أن تختار الصورة، احفظ المصنف إذا لم تكن فعلت بعد، واحفظ أيضاً الملفات التي تريد أن تفتحها من القائمة .
  

أنشئ ارتباطاً تشعبياً من صورة إلى أول ملف مصنف تريده. ولعمل هذا اختر الصورة بالنقر عليها، ثم اختر "إدراج|ارتباط تشعبي " (Insert|Hyperlink) ، وانقر على "ملف موجود أو صفحة ويب موجودة " (Existing File or WebPage) ، ثم انقر على زر "استعراض بحثاً عن ملف " (Browse for File) ، وابحث عن المصنف، ثم انقر على "موافق " (OK). ولفتح ورقة معينة أو مجال مسمى، اختر "إشارة مرجعية " (Bookmark) ، ثم اختر الاسم (أو المجال المسمى في المصنف)، وانقر على "موافق " (OK). انقر على "تلميح الشاشة " (ScreenTip) ، وأدخل النص الذي تريد أن تظهره عندما يقف مؤشر الفأرة على الصورة، ثم انقر على "موافق " (OK) ، ثم على "موافق " (OK) مرة أخرى لإنهاء الخطوة . كرر هذه الخطوة لإضافة وصلات إلى الصور الأخرى الموجودة في المصنف الخاص بك .
إذا كنت تستخدم إصداراً أقدم من برنامج اكسل، تستطيع اتباع إجراء مشابه جداً. في اكسل 2000 ، اختر "ملف موجود أو صفحة ويب موجودة " (Existing File or WebPage) ، وانقر على زر "ملف " (File) ، أو تصفح للوصول إلى ملف المصنف المطلوب واختره، ثم انقر على "موافق " (OK). استخدم زر "إشارة مرجعية " (Bookmark) لاختيار موقع في الملف، وزر "تلميح الشاشة " (ScreenTip) لإضافة تلميح. اختر في اكسل 97، "ارتباط إلى ملف أو URL" (Link to file or URL) ، وانقر على "استعراض " (Browse) لإيجاد الملف، ثم انقر على "موافق " (OK). ولربط الصورة مع موقع مسمى في الملف، انقر على "موقع مسمى في الملف (Named location in file) ، ثم على "استعراض " (Browse) ، واختر الموقع ثم انقر على "موافق " (OK). . إذا كنت تفضل استخدام نص بدلاً من صورة، تستطيع اتباع الخطوات ذاتها تقريباً. أدخل وصفاً لمصنف في خلية، ثم أنشئ وصلة بشعبية بالطريقة ذاتها، التي تنفذ على الصور. عدّل هيئة وصلات النص، باختيار "تنسيق|نمط " (Format|Style) ، واختر من اللائحة المنسدلة "اسم النمط " (Style) الاسم Hyperlink ، وانقر على "تعديل " (Modify) ، ثم أجر تغييراتك من الخيارات المعروضة .

الأفضل أخيراً أن تحمي الورقة والمصنف، بحيث لا يعدل قائمتك أحد بالصدفة. لفعل هذا اختر "أدوات|حماية|حماية ورقة " (Tools|Protection|Protect Sheet) ، وانقر على "موافق " (OK) ، ثم اذهب إلى "أدوات|حماية|حماية مصنف " ( Tools|Protection|Protect Workbook) ، ضع إشارة على مربعات الاختيار "البنية " (Structure) ، و"الإطارات " (Window ) ، وانقر على "موافق " (OK) ، وما لم تكن بحاجة فعلاً إلى كلمة سر، لا تعيّنها، سيفتح هذا الملف تلقائياً كلما فتحت برنامج اكسل إذا وضعت له اختصاراً في مجلد XLStart ، لإكسل (وهو موجود في المسار C:Program FilesMicrosoft   OfficeOffice10  بالنسبة لإكسل 2002، أو انظر في الملف C:Program FilesApplication DataMicrosoft Excel".
احفظ الملف الذي أنشأته، واختبر كل وصلة بالنقر عليها لفتح الملف المرتبط بها. وإذا فتحت ملفاً باستخدام القائمة، ثم أغلقته، سيغلق ملف القائمة أيضاً. لتجنب هذا استخدم المفتاح "السابق " (Back) الموجود على شريط أدوات ويب للعودة إلى قائمة المصنف واختيار مصنف آخر. وستظهر قائمة المصنف أيضاً في قائمة الملفات أسفل قائمة ويندوز .

عدّ الكلمات في برنامج اكسل
يعرف معظمنا أن برنامج وورد يوفر وظيفة لعد الكلمات الموجودة في الوثيقة التي تحررها، فماذا عن برنامج اكسل؟
الواقع أن اكسل لا يقدم هذه الوظيفة بشكل افتراضي، وعليك أن تنشئها بنفسك . ما تحتاجه هو الماكرو التالي :
Sub WordCount()
cellContents = Trim(ActiveCell .Formula)
cellWordCount = 1
prevCharacter = ""
For i = 1 To Len(cellContents)
nextCharacter = Mid(cellContents ، i ، 1)
If (nextCharacter = " " Or nextCharacter = Chr(10)) And _
(prevCharacter <> " " And prevCharacter <> Chr(10)) Then
cellWordCount = cellWordCount 1
End If
prevCharacter = nextCharacter
Next i
If Len(cellContents) = 0 Then
cellWordCount = 0
End If
MsgBox "عدد الكلمات هو :" Str (cellWordCount)
End Sub
تعدّ شيفرة هذا الماكرو الكلمات الموجودة في الخلية المختارة. وإذا وجد أنك قد اخترت مجالاً، فإنه يعدّ الكلمات في الخلية الفعّالة ضمن ذلك المجال. يقوم برنامج الماكرو بعملية عدّ الكلمات عبر عدّ الفراغات الموجودة بين الكلمات في النص، ورموز الانتقال إلى سطر جديد، الناتجة عن ضغط Alt-Enter ، لإنشاء سطر جديد، متجاوزاً الفراغات المتتالية. ويشبه الماكرو وظيفة عدّ الكلمات في وورد بأنه يعامل الكلمات التي تحتوي على واصلة على أنها كلمة واحدة. يمكنك أن تضيف هذا الماكرو إلى ملف اكسل الذي تعمل عليه، وأن تضعه كزر على شريط أدوات اكسل ليكون في متناول يدك سريعاً.