يُعَد الهدف الأساسي من أَتَمتة الأنظمة هو الحصول على البيانات الصحيحة في الوقت المناسب دون تأخير، وذلك كي لا تَفقِد البيانات أهميتها عند اتخاذ قرار ما؛ لذلك لا بُدّ من طريقةٍ لتحقيق عمليات البحث ضمن البيانات بأساليب مُتقدمة. وسنتعرّف فيما يلي على بعض الدوال والأساليب المُختصة بذلك.

والدوال التي سَنَتطرق إليها هي INDEX وMATCH، وهما دالتان تَتَميزان بالسرعة والقوة والإمكانيات العالية، وبالتالي يُمكن القول أنها تُغني عن دوال البحث الأخرى، وأيضًا سنتعرّف على الدالة DGET وهي إحدى دوال البحث المُتقدِّم.

الدالة INDEX

تُرجِع الدالة INDEX قيمةً ضمن جدول أو نطاق محدد بواسطة فهارس أرقام الصفوف والأعمدة، أي اعتمادًا على رقم الصف ورقم العمود تُرجِع قيمة التقاطع لهما.

البناء العام لصيغة الدالة

INDEX(array, row_num, [column_num])‎

تحتاج الدالة إلى ثلاث معطيات:

• المصفوفة Array: ويُمثّل نطاق الخلايا الذي يحتوي البيانات التي تَبحث فيها الدالة، وهذا المُعطى مطلوب في صيغة الدالة.
• رقم السطر row_num: ويُمثّل رقم السطر الذي ستبحث فيه الدالة عن التقاطع، وهذا المُعطى مطلوبٌ في صيغة الدالة أيضًا.
• رقم العمود column_num: ويُمثّل رقم العمود الذي ستبحث فيه الدالة عن التقاطع.

مثال

لدينا بيانات قروض العملاء الخاصة بإحدى المصارف ونحتاج إلى إيجاد المبلغ المسدد للعميل محمد قاسم.

نضع المؤشر في الخلية التي نُريد إرجاع القيمة إليها، ونكتب الصيغة التالية في الخلية E16:

=INDEX(B2:I14,10,8)‎

يُمثّل الرقم 10 رقم صف البيانات. حيث نبدأ العد من أول صف ضمن المجال B2:I14 وليس من بداية ورقة العمل، كذلك فيما يخض رقم العمود الذي يقابل الرقم 8 في مثالنا.

تفسير عمل الدالة

تبحث الدالة عن قيمة تقاطع الصف رقم 10 الذي يُمثّل العميل محمد قاسم، مع العمود رقم 8 الذي يُمثّل قيمة المبلغ الذي سدده وهي 122466.

بالنظر إلى عمل الدالة نجدها عديمة النفع، حيث يمكنني الحصول على النتيجة مباشرةً بدون الحاجة للدالة كوني من أُحدد رقم الصف ورقم العمود يدويًا، ولكن سيكون الأمر صعبًا عندما يكون عدد سجلّات الجدول (يُقصد بالسجلات الصفوف في الجدول) 1000 سجلّ، وكلما زاد حجم البيانات زادت الصعوبة، وزادت معها أخطاء البحث اليدوي أيضًا، وهنا يأتي دور الدالة الثانية لتكمّل عمل هذه الدالة.

الدالة MATCH

تعمل هذه الدالة على البحث عن قيمةٍ ضمن صف أو عمود، وتُعطينا رقم هذا الصف أو رقم هذا العمود عند تطابق قيمة الخلية فيه مع قيمة البحث في الدالة.

البناء العام لصيغة الدالة

MATCH(lookup_value, lookup_array, [match_type])

تحتاج الدالة إلى ثلاث معطياتٍ argument:

• قيمة البحث lookup_value: وهي القيمة التي تُريد مُطابَقتها لإيجاد رقم صفها أو عمودها، وهذا المُعطى مطلوبٌ في صيغة الدالة.
• مصفوفة البحث lookup_array: وهو نطاق الخلايا الذي يحتوي البيانات التي تبحث فيها الدالة، وهذا المُعطى مطلوبٌ في صيغة الدالة أيضًا.
• نوع المطابقة match_type: وتحدد هذه الوسيطة أسلوب المطابقة الذي ستنفذه الدالة، حيث تأخذ القيم التالية:
  • (القيمة 1) إذا أردنا الحصول على نتائج أكبر قِيمةً من قيمة lookup_value أو نتائج مساويةً لها.
  • (القيمة 0) إذا أردنا الحصول على نتائج مساوية تمامًا لقيمة lookup_value. وغالبًا نستعمل هذه القيمة لأننا نُريد نتائج مطابقةً لما نبحث عنه.
  • (القيمة 1-) إذا أردنا الحصول على نتائج أصغر قيمةً من قيمة lookup_value.

نُطَبق الدالة على المثال السابق، حيث نُريد تطبيق الدالة على الصفوف لنعرف رقم الصف الخاص ببيانات العميل محمد القاسم، فنكتب الصيغة التالية في الخلية E16:

=MATCH("محمد قاسم",B2:B14,0)

ستبحث الدالة ضمن المجال، وتُعِيد لنا رقم الصف عند تطابق قيمة البحث مع القيمة الموجودة في أول خليةٍ مطابقةٍ لقيمة البحث وهو الصف رقم 10.

والآن لنطبّق الدالة على الأعمدة لنعرف رقم العمود الذي يحتوي المبلغ المسدَّد، ونكتب الصيغة التالية في الخلية E16:

=MATCH("المبلغ المسدد",B2:I2,0)

ستبحث الدالة ضمن المجال، وتُعِيد لنا رقم العمود عند تطابق قيمة البحث مع القيمة الموجودة في أول خليةٍ مطابقةٍ لقيمة البحث وهو العمود رقم 8.

كما نُلاحِظ أنه يمكننا إيجاد رقم السطر أو العمود الآن بأسلوبٍ سهل ودقيق، والآن لندمج الدالتين معًا:

INDEX(array,
         MATCH(lookup_value, lookup_array, [match_type]),
         MATCH(lookup_value, lookup_array, [match_type]))‎

وبالعودة إلى المثال السابق يُصبِح شكل الدالة كالآتي:

=INDEX(B2:I14,MATCH("محمد قاسم",B2:B14,0),MATCH("المبلغ المسدد",B2:I2,0))‎

وستكون نفس النتيجة التي حصلنا عليها في المثال الأول وهي 122466.

اقتباس

ملاحظة: عند دمج أكثر من دالة بصيغةٍ واحدة، فيجب أن ننتبه إلى إغلاق الأقواس الخاصة بالدوال جميعها ضمن الصيغة.

ولزيادة قوة الدالة أكثر، نعتمد خليتين مرجعيتين لأخذ قيم البحث منهم، ونُعدّل صيغة الدالة، حيث نستبدل قيمة البحث "محمد قاسم" بالخلية C17، ونستبدل قيمة البحث "المبلغ المسدد" بالخلية C18، ونضع النصوص داخل هاتين الخليتين لنحصل على الصيغة التالية:

 =INDEX(B2:I14,MATCH(C17,B2:B14,0),MATCH(C18,B2:I2,0))‎

الآن عند تغيير قيمة البحث لدينا إلى عميلٍ آخر، أو للبحث عن عدد دفعات العميل مثلًا، نستطيع ذلك من خلال تبديل القيم في الخلايا C17 وC18.

وبذلك تمكّنّا من البحث عن قيمةٍ ما ضمن البيانات بوجود شرطين مختلفين، أحدهما في الصفوف والآخر في الأعمدة. ويمكننا دمج هذه الدوال مع دوال بحثٍ أُخرى مثل دمج الدالة MATCH مع الدالة VLOOKUP، حيث ستُعيد لنا الدالة MATCH رقم العمود دون أن نعدّه يدويًا من خلال وضع اسم العمود في قيمة البحث لتعمل الدالة VLOOKUP بدقة وبطريقةٍ أكثر تقدمًا.

الدالة DGET

هي إحدى دوال البحث المتقدمة كونها تجاوزت الكثير من عيوب دوال البحث الأخرى مثل دالة VLOOKUP، فمن خلال هذه الدالة لن نكون مُلزَمين بأن يكون عمود البحث هو العمود الأول في نطاق البيانات، إذ يُمكن لهذه الدالة البحث على جانِبَي عمود البحث، وتُعَد من دوال قواعد البيانات database، ولهذا يجب أن تكون البيانات لدينا مجدولة، أي أن مصدر البيانات (الجدول الذي نريد إرجاع القيم منه) يجب أن يحتوي على الرؤوس (تسميات الأعمدة)، كما يجب تصميم جدول لتحديد قواعد البحث (خلايا مرجعية للبحث).

سنوضِّح الفكرة أكثر من خلال المثال لاحقًا.

البناء العام لصيغة الدالة

DGET(database, field, criteria)‎

تحتاج الدالة إلى ثلاث معطياتٍ:

• قاعدة الببانات Database: وهو نطاق الخلايا الذي تَتَألف منه القائمة أو قاعدة البيانات، وهذا المُعطى مطلوبٌ في صيغة الدالة.
• الحقل Field: وتشير إلى العمود الذي تستخدمه الدالة، وهذا المُعطى مطلوبٌ في صيغة الدالة أيضًا. وتتعرف عليه الدالة بإدخال تسمية العمود مع تضمينها بين علامتَي اقتباس مُزدوجتيْن، مثل: "اسم العميل"، أو "رقم العقد"، أو رقم يُمثّل موضع العمود في القائمة (بدون علامات اقتباس) مثل 1 للعمود الأول، و2 للعمود الثاني، وهكذا، أو عن طريق خلية مرجعية.
• المعيار Criteria: وهو نطاق الخلايا التي تحتوي على الشروط المحددة من قِبَل المستخدم. يُمكن استخدام أي نطاقٍ لوسيطة المعايير، طالما أن الوسيطة تحتوي على تسمية عمود واحد على الأقل وخلية واحدة على الأقل أسفل تسمية العمود لتحديد شرط للعمود. وهذا المُعطى مطلوبٌ في صيغة الدالة.

مثال

نُريد البحث ضمن البيانات التالية عن نوع العقد والرقم الخاص بالموظف احمد احمد باستعمال الدالة DGET.

نجد أن الشروط لدينا مُحَققة والأعمدة تَحوي على رؤوسٍ، لذا نُصمم الجدول الصغير التالي لإجراء العمليات.

من خلال جدول البيانات السابق، نَجد أن اسم العمود الذي يحوي قيمة البحث (شرط البحث) هو عمود الاسم، وأنّ اسم العمود الذي نُريد إرجاع البيانات منه هو العمود نوع العقد والعمود الرقم، بحيث يكون شرط البحث هنا هو الموظف احمد احمد من العمود الاسم. نطبق الدالة في الخلية I4 ونكتب الصيغة التالية:

=DGET(A1:F12,I3,H3:H4)

سَيَتعرف البرنامج على الوسيطة الثالثة على أن اسم عمود البحث هو القيمة الموجودة في الخانة H3، وأن قيمة تحقيق الشرط هي القيمة الموجود في الخلية H4، ويكون الناتج هو نوع العقد الخاص بالموظف أحمد أحمد وهو نصف سنوي.

لنغير الآن قيمة الوسيطة الثانية Field في الدالة، ونضع بدلًا من نوع العقد اسم العمود الأول الرقم، لنجد أن الدالة تعمل جيدًا بغضّ النظر عما إذا كانت البيانات التي نُريد البحث عنها قبل عمود الشرط (الاسم) أو بعده، وبتطبيق المعادلة لِتُرجَع القيمة 605865 التي تقابل رقم الموظف أحمد أحمد، والدالة عَمِلت جيدًا.

يُمكننا التعامل مع المُعطى الثاني Field في هذه الدالة يدويًا إما عن طريق كتابة اسم العمود بين اشارتي تنصيص "اسم العمود"، كما يلي:

=DGET(A1:F12,”نوع العقد”,H3:H4)
=DGET(A1:F12,”الرقم”,H3:H4)

أو يمكننا الاستعاضة عنها برقم العمود كما يلي:

=DGET(A1:F12,5,H3:H4)
=DGET(A1:F12,1,H3:H4)

وكل هذه الصيغ صحيحةٌ وتُعطي نفس النتيجة.

اقتباس

تنويه: عند تطبيق هذه الدالة يجب الانتباه إلى أن تكون القيم الموجودة ضمن عمود الشرط قيمًا فريدةً (لا يحوي بياناتٍ مكررةِ) وإلا فلن تعمل الدالة جيدًا وسَتُرجِع لنا القيمة !NUM#.

خاتمة

مما سبق نجد أن دوال البحث عديدة وكل حالةٍ يمكن أن نستعمل لها دالة بحثٍ تُناسبها، وكما أن لكل دالةٍ خصائص تميزها عن الدالة الأخرى، إلا أن لكل دالةٍ عيوبها أيضًا، لذا يجب الانتباه إلى توظيف الأداة المناسبة بحسب الحالة التي نعمل عليها.

اقرأ أيضًا

• أكثر الدوال الرياضية شيوعا في مايكروسوفت إكسل.
• مدخل إلى البرمجة باستخدام VBA في مايكروسوفت إكسل.
• كيفية استخراج أجزاء من النصوص أو دمجها باستخدام الدوال النصيّة في اكسل.
• دوال الوقت الأساسية في اكسل.

قد تتضمّن جداول البيانات أحيانًا صفوفًا أو أعمدة فارغة كنا قد تركناها سهوًا عند إنشاء الجدول. وفي أحيانٍ أخرى نضطر إلى التعامل مع الجداول التي أنشأها أحد الزملاء أو الأصدقاء ولم ينظّم الجدول بما مناسب، تاركًا الكثير من الفراغات فيه.
إذا كان الجدول صغيرًا، وعدد الأعمدة/الصفوف الفارغة فيه قليلًا، يمكن حذفها يدويًا بتحديد الصف/العمود الذي نريد حذفه، النقر عليه بزر الفأرة الأيمن، واختيار حذف Delete:

ومن ثم اختيار إزاحة الخلايا إلى الأعلى Shift cells up في حالة الصفوف، وإزاحة الخلايا إلى اليمين Shift cells right في حالة الأعمدة:

أمّا إذا كان الجدول كبيرًا، فستصبح هذه العملية مُضنية وتستغرق وقتًا طويلًا. لذا من الأفضل استخدام طريقة أخرى أسهل وأسرع.
غطيّنا في درس سابق خاصية Go to Special، وتعلّمنا كيفية استخدامها لتحديد خلايا بمواصفات معينة، كأن تكون فارغة مثلًا. وسنستخدم هذه الخاصية في هذا الدرس لتحديد الصفوف/الأعمدة التي نريد حذفها دفعة واحدة، ومن ثم تطبيق أمر الحذف عليها.
ملاحظة: بما أنّ هذه العملية تنطوي على حذف صفوف، أعمدة، وخلايا، من الأفضل أن تحفظ نسخة أخرى من جدول البيانات تجنبًا لفقدان أيّ من بياناتك المهمة؛ من باب الاحتياط فقط.
يجب أولًا أن نحدّد الجدول أو نطاق الخلايا الذي يحتوي على الصفوف الفارغة. في مثالنا هذا حدّدنا النطاق A1:D26:

بعد ذلك ننقر على أمر بحث واستبدال Find & Replace من تبويب الصفحة الرئيسية Home، ونختار Go to Special من القائمة المنسدلة:

سيُفتح مرّبع الحوار Go to Special، ومنه نحدّد الخيار الفراغات Blanks ثم ننقر على زر موافق OK:

ستُحدَّد الصفوف الفارغة في الجدول وتُستثنى الخلايا التي تحتوي على قيم نصية أو رقمية أو صيغ:

بعد تحديد الصفوف الفارغة، ننقر على السهم الصغير تحت الأمر حذف Delete من التبويب نفسه (الصفحة الرئيسية)، ثم نختار حذف صفوف الورقة Delete Sheet Rows من القائمة المنسدلة:

سيتم حذف الصفوف المحددّة، وإزاحة الصفوف التي تقع تحتها إلى الأعلى تلقائيًا، وبذلك تحصل على جدول مرتب بصفوف متجاورة:

إذا كان الصف الفارغ يقع على جهة اليمين من صف يحتوي على بيانات معينة، ونريد ملء الصف الفارغ بتلك البيانات، ففي هذه الحالة نحدّد نطاق الخلايا الذي يحتوي على الصفوف الفارغة:

ثم نطبّق نفس الخطوات (Find & Replace> Go to Special> Blanks> Delete). لكن هذه المرّة نختار حذف خلايا Delete Cells بدلًا من حذف صفوف الورقة:

سيظهر مربع الحوار Delete، ومنه يُحدَّد اتجاه إزالة الخلايا. الخيار المبدئي هو إزالة الخلايا إلى الأعلى، لكننا نرغب في جلب الخلايا على يسار الصفوف الفارغة ووضعها مكان تلك الفراغات، لذلك سنحدد الخيار Shift Cells Right وننقر على OK:

انتبه عند تحديد الصفوف الفارغة إلى أنّ الجدول قد يحتوي أيضًا على خلايا فارغة مفردة ستُحدَّد أيضًا عند تطبيق الأمر Go to Special> Blanks، وبالتالي تُحذَف مع الصفوف الفارغة، مما يؤدي إلى تداخل البيانات وتغيير ترتيبها.
إذا رغبت في الإبقاء على تلك الخلايا الفردية الفارغة، يمكنك أن تملأها بمحتوى معين قبل أن تطبق أمر التحديد والحذف. ومن ثم إعادة حذف محتواها بعد حذف الصفوف الفارغة.
يمكن تطبيق الخطوات المذكورة أعلاه نفسها بالضبط إذا احتوى الجدول على أعمدة فارغة نريد إزالتها.
وبذلك نكون قد تعلّمنا كيف أنّه بخطوات بسيطة يمكن أن نوفّر الكثير من الوقت والجهد عند حذف الصفوف أو الأعمدة الفارغة في جداول البيانات الكبيرة.
إذا كانت لديكم أيّة أسئلة أو أفكار حول حذف الفارغات في جداول بيانات إكسل، شاركونا بها في صندوق التعليقات أو في قسم الأسئلة والأجوبة.

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

TRIM

تعمل هذه الدالة على إزالة المسافات الزائدة بين الكلمات، أو في بداية ونهاية السلسلة النصية. والصيغة العامة لها هي:

=TRIM(text)

text: النص الذي نريد إزالة المسافات الزائدة منه.

مثال: تحتوي الخلية B4 الموضّحة أدناه على مسافات غير منتظمة بين الكلمات، ولحذف المسافات غير المرغوبة نكتب الصيغة التالية في خلية النتيجة:

بإمكاننا تحديد الخلية التي تحتوي على النص مباشرة، أو لصق النص يدويًا بين علامتي اقتباس (" "). نضغط على ENTER لإظهار النتيجة:

كما نلاحظ في خلية النتيجة، تمت إزالة كل المسافات الزائدة باستثناء المسافات الفردية بين الكلمات.

يمكننا استخدام هذه الدالة عند استيراد النصوص إلى اكسل ويكون تباعد الكلمات فيها غير منتظم. إذ ستكون عملية إزالة المسافات الزائدة أسهل وأسرع بكثير من القيام بها يدويًا.

PROPER

تعمل هذه الدالة على تحويل الحرف الأول من كل كلمة في السلسة النصية إلى حرف كبير وتبقي بقية الحروف صغيرة. ويمكننا الاستفادة منها عند استيراد نصوص معيّنة إلى اكسل وكانت حالة الأحرف للكلمات غير مرتّبة، إذ ستجعل هذه الدالة عملية تنسيق حالة الأحرف أسرع وأسهل بكثير من القيام بها يدويًا.

الصيغة العامة لهذه الدالة:

=PROPER(text)

text: النص الذي نريد تطبيق الدالة عليه.

مثال: تحتوي الخلية B5 الموضحة أدناه على نص باللغة الإنجليزية، وحالة الأحرف للكلمات غير منتظمة، لتعديل النص وجعل كل كلمة تبدأ بحرف كبير وبقية الحروف صغيرة، نكتب الصيغة التالية في خلية النتيجة:

يمكننا تحديد الخلية التي تحتوي على النص، أو لصق النص غير المرتب بين علامتي اقتباس في الصيغة. نضغط على مفتاح ENTER لإظهار النتيجة:

من خصائص دالة PROPER هو أنّه حتّى إذا كانت بداية الكلمة أرقام أو رموز (مثل 09test)، فأنّها تتجاوز الأرقام وتحوّل الحرف الأول إلى حرف كبير (أي تصبح الكلمة المذكورة 09Test).

FIND

تُستخدم هذه الدالة لتحديد موقع كلمة/عبارة معيّنة ضمن سلسلة نصيّة. فتقوم بإرجاع ترتيب الحرف الأول للكلمة التي نبحث عنها نسبة إلى عدد حروف السلسلة. وتكون هذه الدالة حساسة لتشكيل الكلمة في اللغة العربية ولحالة الأحرف (كبيرة أو صغيرة) في اللغة الإنجليزية.

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

=FIND(find_text; within_text; [start_num])

find_text: النص الذي نبحث عنه، وهو مطلوب في الصيغة.

within_text: النص الذي نريد البحث فيه، وهو أيضًا مطلوب في الصيغة.

start_num: مرتبة الحرف الذي نريد بدء البحث منه، ووجوده اختياري في الصيغة (معطيات الصيغة التي توضع بين قوسين مربعين [ ] تكون اختيارية).

مثال: لمعرفة موقع كلمة "Functions" في نص الخلية G3 نكتب الصيغة التالية:

نراعي وضع الكلمة التي نريد البحث عنها بين علامتي اقتباس في الصيغة ونتأكّد من مطابقة حالة الأحرف، ثم نضغط على مفتاح ENTER لإظهار النتيجة:

تم إرجاع النتيجة "6" وهو بالفعل تسلسل أول حرف في كلمة "Functions". فكلمة "Text" تحتل 4 مراتب، والمسافة بين "Functions" و"Text" تأخذ مرتبة واحد ليصبح المجموع 5، وحرف "F" هو السادس.

في هذا المثال لم نحدد قيمة start_num، وفي هذه الحالة سيتم افتراض هذه القيمة 1، أي يبدأ البحث من أول حرف في النص. لنغيّر الصيغة ونحدد قيمة المتغيّر start_num 17 مثلا:

بالنتيجة تم إرجاع الخطأ #VALUE! لأنّه لم يتم العثور على كلمة "Functions" بعد المرتبة 17. فكلمتي "Text" و"Functions" مع المسافة بينهما تحتل 14 مرتبة، بمعنى أنّ كلمة "Functions" تقع قبل المرتبة 17:

لنغيّر الصيغة ونبحث عن موقع كلمة "حسوب" في الخلية G2، وكالتالي:

عند الضغط على مفتاح ENTER تم إرجاع الخطأ #VALUE! وذلك لأننا قمنا بإضافة ضمّة فوق حرف السين عند كتابة الصيغة، وكما أسلفنا الذكر، تكون دالة FIND حسّاسة لتشكيل الحروف في اللغة العربية.

SEARCH

تماثل دالة FIND من حيث الوظيفة، لكنّها غير حسّاسة لتشكل الحروف في اللغة العربية وحالتها في اللغة الإنجليزية.

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

=FIND(find_text; within_text; [start_num])

find_text: النص الذي نبحث عنه، وهو مطلوب في الصيغة.

within_text: النص الذي نريد البحث فيه، وهو أيضًا مطلوب في الصيغة.

start_num: ترتيب الحرف الذي نريد بدء البحث منه، ووجوده اختياري في الصيغة.

مثال: سنطبّق هذه الدالة لتحديد موقع الكلمة "Functions" كما في المثال السابق:

سنلاحظ أنّه سيتم إرجاع نفس النتيجة، "6"، بالرغم من أننا كتبنا بداية كلمة "functions" بحرف صغير:

REPLACE

تستخدم هذه الدالة لاستبدال نص معيّن في سلسلة نصيّة بنص آخر حسب عدد الحروف الذي نحدّده. ووظيفتها مشابهة لوظيفة أداة استبدال النصوص Replace.

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

=REPLACE( old_text; start_num; num_chars; new_text )

old_text: السلسلة النصية التي نريد استبدال جزء منها.

start_num: مرتبة الحرف الأول للنص الذي نريد استبداله.

num_chars: عدد حروف النص القديم الذي نريد استبداله.

new_text: النص الجديد الذي نريد الاستبدال به.

ملاحظة: كل المتغيرات أعلاه مطلوبة في الصيغة.

مثال: لاستبدال كلمة "النصوص" في الخلية G2 بالكلمة "نصية"، نكتب الصيغة كالتالي:

قمنا بإدخال الخلية G2 في الصيغة لأنّها تحتوي على النص الأصلي الذي نريد استبدال جزء منه، يليه الرقم "6" لأنّ مرتبة أول حرف من كلمة "النصوص هي السادسة (كلمة "دوال" مع المسافة بعدها تحتل 5 مراتب، وكلمة "النصوص" تبدأ عند المرتبة 6)، يليه الرقم "6" لأنّ كلمة "النصوص" تتكوّن من ستّة حروف، وأخيرًا كلمة "نصيّة" التي نريد الاستبدال بها بين علامتي اقتباس. والنتيجة:

UPPER

تقوم هذه الدالة بجعل كل أحرف السلسلة النصيّة كبيرة. والصيغة العامة لها هي:

=UPPER(text)

text: النص الذي نريد تطبيق الدالة عليه.

مثال: لتحويل كل النصوص في الخلية B5 إلى أحرف كبيرة نكتب الصيغة التالية:

والنتيجة:

LOWER

عملها معاكس للدالة السابقة، إذ تجعل كل أحرف السلسلة النصية صغيرة. الصيغة العامة لهذه الدالة هي:

=LOWER(text)

مثال: لتحويل نفس النص في الخلية B5 إلى أحرف صغيرة نكتب الصيغة التالية:

والنتيجة:

خاتمة

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

لقد تعرّفنا في درس سابق على بعض دوال اكسل النصيّة، مثل دالة TRIM لإزالة المسافات الزائدة بين النصوص، الدوال FIND، SEARCH، وREPLACE للبحث عن النصوص واستبدالها، ودالتي UPPER وLOWER لتغيير حالة الأحرف للنصوص الإنجليزية.

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

LEFT

تُستخدم دالة LEFT لاستخراج جزء من النص من جهة اليسار للسلسلة النصية حسب عدد الأحرف الذي نحدده. والصيغة العامة لهذه الدالة هي:

=LEFT(text; [num_chars])

text: النص الذي نريد استخراج جزء منه.

num_chars: عدد الأحرف التي نريد استخراجها. وبما أنّ وجوده اختياري في الصيغة، سيتم افتراض العدد 1 عند عدم تحديد هذه القيمة.

مثال: إذا رغبنا في استخراج كلمة "Text" من نص الخلية B4 نستخدم الدالة LEFT (لأنّ الكلمة تقع في جهة اليسار من السلسلة النصية) ونكتب الصيغة التالية:

قمنا بتحديد الخلية B4 لأنها تحتوي على النص الذي نريد الاستخراج منه، وقمنا بكتابة الرقم 4 لأنّ كلمة "Text" تتكوّن من أربعة حروف. نضغط على مفتاح ENTER لإظهار النتيجة:

إذا لم نحدد قيمة num_chars في الصيغة سيتم افتراض القيمة 1 وإرجاع النتيجة "T" لأنّ هذا الحرف يحتل المرتبة الأولى من جهة اليسار:

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

سيتم استخراج كلمة "دوال" من الخلية، والتي هي في الحقيقة تقع على يمين، وليس يسار، السلسلة النصية:

RIGHT

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

=RIGHT(text; [num_chars])

text: النص الذي نريد استخراج جزء منه.

num_chars: عدد الأحرف التي نريد استخراجها. وبما أنّ وجوده اختياري في الصيغة، سيتم افتراض العدد 1 عند عدم تحديد هذه القيمة.

مثال: إذا رغبنا في استخراج كلمة "Academy" من النص في الخلية B4 نستخدم الدالة RIGHT (لأنّ الكلمة تقع على يمين السلسلة النصية) ونكتب الصيغة التالية:

قمنا بإدخال الرقم 7 لأنّ كلمة "Academy" تتكوّن من 7 حروف. نضغط على مفتاح ENTER لإظهار النتيجة:

MID

تُستخدم دالة MID لاستخراج جزء من النص يقع في مرتبة محددة من بداية السلسلة النصية وحسب عدد الأحرف الذي نحدّده.  الصيغة العامة لهذه الدالة:

=MID(text; start_num; num_chars)

text: النص الذي نريد الاستخراج منه.

start_num: موضع/مرتبة الحرف الأول للنص الذي نريد استخراجه.

num_chars: عدد أحرف النص الذي نريد استخراجه.

مثال: إذا رغبنا في استخراج كلمة "Hsoub" من النص في الخلية B4 باستخدام الدالة MID، نكتب الصيغة التالية:

قمنا بإدخال الرقم 18 لأنّ الحرف الأول من كلمة "Hsoub" يقع في المرتبة 18، والرقم خمسة لأنّ عدد حروف كلمة "Hsoub" يساوي 5. نضغط على ENTER لإظهار النتيجة:

CONCATENATE

تُستخدم دالة CONCATENATE لجميع جزأين أو أكثر من النصوص في سلسلة نصيّة واحدة. والصيغة العامة لهذه الدالة هي:

=CONCATENATE(text1; [text2];…)

text1: النص الأول الذي نريد دمجه.

text2: النص الثاني الذي نريد دمجه مع النص الأول.

يمكن أن تكون العناصر التي نريد تجميعها قيم نصّية، رقمية، أو مرجع خلية.

مثال: إذا رغبنا في تجميع النص من خلايا متفرّقة، ولتكن G3  وD7، بالإضافة إلى الرقم "2"، نكتب الصيغة التالية:

قمنا بإدخال مسافة فارغة بين علامتي اقتباس (" ") بعد G3 وD7 لأننا نريد أن نفصل النصوص بمسافة، وإلّا ستظهر الكلمات متلاصقة. نضغط على ENTER لإظهار النتيجة:

لقد نجحنا في المثال بتجميع النصوص من خلايا متفرّقة، أحد هذه النصوص هو نتيجة لصيغة قمنا بتطبيقها سابقًا (كلمة "Text")، والنص الثاني هو عبارة عن قيمة نصيّة (Functions)، أما الأخير فهو قيمة رقمية ("2") قمنا بإدخالها يدويًا في الصيغة.

بإمكاننا أيضًا استخدام علامة العطف (&) لتجميع النصوص بدلًا من الدالة CONCATENATE. على سبيل المثال، إذا رغبنا في جمع النصوص في G4 وG5 باستخدام العلامة & نكتب الصيغة التالية:

علامة & هي في الحقيقة ليست دالة، واستخدامها مشابه تقريبًا لاستخدام علامة الجمع (+) مع القيم الرقمية. نراعي عند جمع النصوص هنا أيضًا وضع مسافة فارغة بين علامتي اقتباس بين النصوص لتلافي تلاصقها. نضغط على ENTER لإظهار النتيجة:

EXACT

تُستخدم هذه الدالة للمقارنة بين النصوص والتأكّد فيما إذا كانت متطابقة. فتقوم بإرجاع القيمة المنطقية TRUE إذا كانت متطابقة، والقيمة المنطقية FALSE إن لم تكن كذلك. وتكون دالة EXACT حساسة لتشكيل الأحرف في اللغة العربية وحالتها (صغيرة أو كبيرة) في اللغة الإنجليزية لكنّها لا تقارن تنسيق النص.

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

=EXACT(text1; text2)

text1: النص الأول الذي نريد مقارنته.

text2: النص الثاني الذي نريد مقارنته.

مثال: لمقارنة النصين في الخليتين B3 وC7 نكتب الصيغة التالية:

تم إرجاع النتيجة FALSE لعدم وجود همزة فوق حرف الألف لكلمة "اكاديمية" في الخلية C7، وهذه الدالة كما ذكرنا حسّاسة لتشكيل الأحرف:

تسهّل هذه الدالة أيضًا مقارنة الأرقام، فإذا كانت الأرقام طويلة وفي مواضع متفرّقة من الورقة سيكون من الأدق مقارنتها باستخدام دالة EXACT. سنقوم مثلا بمقارنة الرقمين في الخليتين B2 وC6 بكتابة الصيغة التالية:

وسنتأكّد من تطابق الرقمين عند إرجاع النتيجة TRUE:

LEN

تقوم هذه الدالة بحساب طول السلسلة النصية فتقوم بإرجاع عدد أحرف النص. والصيغة العامة لها هي:

=LEN(text)

text: النص الذي نريد تطبيق الدالة عليه.

مثال: لحساب عدد أحرف النص في الخلية G6 نكتب الصيغة التالية:

سيتم تضمين المسافات بين الكلمات عند إرجاع النتيجة:

 بإمكاننا أن نضيف إلى نتيجة هذه الدالة كلمة توضيحية باستخدام العلامة &. على سبيل المثال، لإضافة كلمة "حرف" بعد عدد الأحرف الذي ترجعه الدالة، نكتب الصيغة كالتالي:

والنتيجة:

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

والنتيجة:

خاتمة

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

إن كان لديكم أي سؤال بخصوص دوال النصوص تفضّلوا بطرحه في صندوق التعليقات.

تُستخدم خاصية التحقق من صحة البيانات Data Validation للتحكم في القيم المُدخلة إلى خلية ما حسب معيار محدد. وتعتبر مفيدة على وجه الخصوص عندما تشارك مصنفاتك مع زملائك في الشركة وترغب في التأكّد من إدخال القيم الصحيحة في تلك الخلية. على سبيل المثال، تقييد المستخدمين بإدخال نص بعدد حروف محدد، أو إدخال قيم موجبة فقط في الخلية.

وتتوفر مع هذه الخاصية إمكانية إنشاء رسالة إدخال Input Message تُرشد المستخدمين إلى القيم المناسبة المطلوب إدخالها في الخلية، بالإضافة إلى عرض نافذة تحذيرية Error Alert تنبّه المستخدمين عند إدخال قيمة خاطئة.

مثال1: في المصنف التالي، نرغب في التأكد من أن يقوم المستخدمون بإدخال قيم لـ "نسبة الخصم" تتراوح من 15 إلى 25%.

أولا، سنحدد الخلية التي نريد تطبيق التحقق من صحة المدخلات عليها، وهي الخلية B15 في هذا المثال، ثم نذهب إلى تبويب بيانات Data وننقر على أمر التحقق من صحة البيانات Data Validation:

في مربع الحوار توجد 3 تبويبات. الأول، الإعدادات Settings، ومنه نحدد المعيار الذي يتم التحقق من المدخلات على أساسه، ونلاحظ أنّ الخيار الافتراضي هو السماح بأي قيمة Any Value. وبما أننا في هذا المثال نريد تقييد المستخدم بإدخال قيمة بين 15 و25%، سنسمح بإدخال القيم العشرية باختيار Decimal من حقل Allow، المعيار بين Between من حقل Data، ثم ندخل الحدين الأقصى والأدنى:

وفي التبويب الثاني، رسالة الإدخال Input Message، نقوم بإدخال العنوان والتلميح الذي نريد إظهاره للمستخدمين عند تحديد الخلية لتغيير محتواها:

إنّ إظهار رسالة التلميح اختياري، يمكنك عدم إظهارها بإلغاء تأشير الخيار Show input message when cell is selected.

في التبويب الثالث، التنبيه إلى الخطأ Error Alert، نقوم بإدخال الرسالة التي تظهر للمستخدم عند إدخال قيمة خاطئة، ويفضّل أن تكون رسالة واضحة تُرشد المستخدم إلى الشيء الذي يجب فعله بالضبط. بعدها نحدد نوع التنبيه Style:

تختلف أنواع التنبيه كالتالي:

• إيقاف Stop: يمنع المستخدم من إدخال قيمة غير صحيحة، ولا تؤخذ القيمة ما لم تحقق المعيار. وهذا النوع من التنبيه هو الأكثر تقييدا.
• تحذير Warning: تُحذر المستخدم بأنّ القيمة التي قام بإدخالها غير صحيحة، لكن يمكن قبول هذه القيمة عند النقر على نعم Yes حتى وإن لم تحقق المعيار، أو تعديل القيمة عند النقر على كلا No.
• معلومات Information: تُخبر المستخدم بأنّ القيمة غير صحيحة فقط، وهذا النوع من التنبيه عادة ما يكون مرنا ويتم تجاهله من قبل المستخدمين.

يمكن عدم إظهار رسالة التنبيه بإلغاء تأشير الخيار Show error alert after invalid data is entered .

عند الانتهاء من تعديل كافة الخيارات ننقر موافق OK.

وبذلك ستظهر رسالة التلميح عند تحديد الخلية:

وعندما نقوم بإدخال قيمة خارج المدى، 12% مثلا، ستظهر رسالة التنبيه حسب النوع الذي اخترناه. بما أننا اخترنا نوع الإيقاف Stop لدينا خيار إعادة المحاولة Retry وإدخال قيمة صحيحة، إلغاء عملية التحرير Cancel، أو الذهاب إلى صفحة المساعدة Help.

أما عند اختيار النوع الثاني، تحذير Warning، وإدخال قيمة خاطئة، فيمكننا المواصلة مع هذه القيمة عند النقر على نعم Yes، إعادة إدخال قيمة صحيحة عند النقر على كلا No، إلغاء عملية التحرير بالنقر على Cancel، أو الذهاب إلى صفحة المساعدة Help.

وفي النوع الثالث، معلومات Information، يمكننا تجاهل التنبيه بالنقر على موافق OK، إلغاء عملية التحرير Cancel، أو الذهاب إلى صفحة المساعدة Help.

مثال2: في هذا المثال البسيط، نريد تقييد المستخدم باختيار لغة من مجموعة لغات تظهر في قائمة منسدلة:

سنحدد أولا الخلية التي نريد إضافة القائمة إليها، وهي D3 في هذا المثال، ثم ننقر التحقق من صحة البيانات Data Validation من تبويب بيانات Data:

سنختار قائمة List من حقل Allow، ثم نحدد القيم التي نريد إدراجها في القائمة، وقد قمنا بكتابتها مسبقا على جنب:

لا نريد هنا إظهار رسالة إدخال ورسالة تنبيه، لذلك سنتجاوز هذه الخطوة وننقر موافق OK. والنتيجة ستكون ظهور سهم بجانب الخلية D3، عند النقر عليه تظهر قائمة اللغات المتاحة التي يمكن الاختيار منها:

اقرأ أيضًا

• دليلك الشامل إلى أنواع البيانات

لقد استعرضنا في الدرس السابق قائمة دوال Date و Time، وهي دوال التاريخ الأساسية، وتعلّمنا كيفية استخدامها للحصول على بيانات معيّنة تتعلّق بالتاريخ. وفي هذا الدرس سنتعرّف على دوال الوقت الأساسية ونوضّح كيفية استخدامها بواسطة الأمثلة التوضيحية.

NOW

تقوم هذه الدالة بإرجاع الوقت والتاريخ الحالي، والصيغة العامة لها:

=NOW()

مثال: لعرض تاريخ اليوم والوقت الحالي في الخلية G2 نكتب الصيغة التالية:

بما أنّ عمل الدالة لا يتطلّب تحديد أي متغيّر، نترك ما بين القوسين فارغًا ثم نضغط على مفتاح ENTER لعرض النتيجة:

HOUR

تعمل هذه الدالة على استخراج جزء الساعة من الوقت، والصيغة العامة لها هي:

=HOUR(serial_number)

serial_number: الوقت الذي نريد استخراج جزء الساعة منه. ويمكن إدخال هذا الوقت يدويًا كسلسلة نصيّة بين علامتي اقتباس، أو تحديد خلية تحتوي على صيغة تكون نتيجتها وقتًا معيّنًا.

مثال: لاستخراج جزء الساعة من ناتج الصيغة في الخلية G2، نكتب الصيغة التالية:

وبالنتيجة سيتم إرجاع الرقم "20" الذي يمثّل جزء الساعة من الوقت:

ويمكن أيضًا أن تكون الصيغة بالشكل التالي:

في هذه الحالة سيتم إرجاع الرقم "9" الذي يمثّل جزء الساعة:

MINUTE

ترجع هذه الدالة جزء الدقائق من الوقت الذي نحدّده في الصيغة، والصيغة العامة لها هي:

=MINUTE(serial_number)

serial_number: الوقت الذي نريد استخراج جزء الدقائق منه. ويمكن إدخال هذا الوقت يدويًا كسلسلة نصيّة بين علامتي اقتباس، أو تحديد خلية تحتوي على صيغة تكون نتيجتها وقتًا معينًا.

مثال: لاستخراج جزء الدقائق من الوقت في الخلية G2، نكتب الصيغة التالية:

وبالنتيجة سيتم إرجاع الرقم "31" الذي يمثل جزء الدقائق:

ملاحظة: سيتم تحديث ناتج صيغة الدالة NOW كلّما قمنا بكتابة وتطبيق صيغة ما في الورقة، سواء كانت مرتبطة بصيغة الدالة NOW أو غير مرتبطة بها. كما سيتم تحديثه كلّما قمنا بإغلاق المصنّف وفتحه مجددًا.

SECOND

تستخرج هذه الدالة جزء الثواني من الوقت الذي نحدّده في الصيغة، والصيغة العامة لها هي:

=SECOND(serial_number)

serial_number: الوقت الذي نريد استخراج جزء الدقائق منه. ويمكن إدخال هذا الوقت يدويًا كسلسلة نصيّة بين علامتي اقتباس، أو تحديد خلية تحتوي على صيغة تكون نتيجتها وقتًا معيّنًا.

مثال: لاستخراج جزء الثواني من الوقت في الخلية G2، نكتب الصيغة التالية:

والنتيجة:

بما أننا قمنا بتطبيق الصيغة على ناتج الدالة NOW، سيتم إرجاع جزء الثواني في وقت تطبيق الصيغة. وسيتغيّر هذا الرقم كلّما قمنا بتحديث الصيغة. أمّا إذا قمنا بإدخال الوقت يدويًا كنص بين علامتي اقتباس، فسيبقى ناتج الدالة ثابتًا مهما قمنا بتحديث الصيغة.

TIME

تقوم هذه الدالة بإرجاع الوقت اعتمادًا على رقم الساعة، الدقيقة، والثانية الذي نقوم بتحديده. والصيغة العامة لها هي:

=TIME(hour; minute; second)

hour: الرقم الذي يمثّل الساعة. وأي رقم أكبر من 23 سيتم تقسيمه على 24، ويعتبر الباقي جزء الساعة من الوقت.

minute: الرقم الذي يمثّل الدقائق. وأي رقم أكبر من 59 سيتم تقسمه على 60، ويتم تحويل الباقي إلى ساعات ودقائق.

second: الرقم الذي يمثّل الثواني. وأي رقم أكبر من 59 سيتم تقسيمه على 60، ويتم تحويل الباقي إلى ساعات ودقائق وثواني.

مثال: لتحويل الساعة 08، الدقيقة 20، الثانية 50 إلى تنسيق وقت نكتب الصيغة التالية:

والنتيجة:

يمكننا تغيير تنسيق الوقت لإظهار الثواني أو استخدام تنسيقات أخرى من مربّع الحوار Format Cells.

مثال آخر؛ لتحويل الساعة 26، الدقيقة 05، الثانية 63 إلى تنسيق وقت، نكتب الصيغة التالية:

والنتيجة:

لقد عملت الدالة كالتالي:

بما أنّ رقم الساعة أكبر من 24، تم تقسيم الرقم 26 على 24 واعتبار باقي عملية القسمة العدد 2 هو الجزء الذي يمثل الساعة. وهذا منطقي، فالساعة 26 يعني ساعتين بعد الساعة 24، أي الساعة الثانية صباحًا.

وبما أنّ رقم الثواني أكبر من 60، تم تقسيم الرقم 63 على الرقم 60، ومن ثم إضافة ناتج القسمة الصحيح، العدد 1، إلى الدقائق لتصبح 6 دقائق، وأخيرًا تم اعتبار المتبقي من عملية القسمة الرقم 3 هو الجزء الذي يمثّل الثواني.

TIMEVALUE

 تقوم هذه الدالة بتحويل الوقت المدخل بشكل نص إلى رقم تسلسلي يمثّل هذا الوقت حسب ما تم تخزينه في اكسل. ولكي يتوضّح عمل الدالة، من الأفضل أن نوضّح كيف يُخزن الوقت في اكسل.

كل قيمة للوقت تُخزن في اكسل كأرقام تتراوح بين 0 و1، والتي تمثّل بدورها نسبة من اليوم. على سبيل المثال، يمثل الرقم 0.25 الوقت 06:00، أما الرقم 0.5، فيمثّل الوقت 12:00... وهكذا.

الصيغة العامة لدالة TIMEVALUE هي:

=TIMEVALUE(time_text)

time_text: النص الذي يمثل الوقت.

مثال: لمعرفة الرقم التسلسلي للوقت "18:40:55"، نكتب الصيغة التالية:

نراعي وضع الوقت بين علامتي اقتباس ثم نضغط على ENTER لعرض النتيجة:

 خاتمة

لقد غطيّنا في هذا الدرس دوال الوقت الأساسية في اكسل التي يمكن الاستفادة منها في استخراج بيانات معيّنة متعلّقة بالوقت. وبالطبع المجال مفتوح أمامك كمستخدم لاستخدامها بالطريقة التي تلائم احتياجاتك.

إن كان لديك أي سؤال حول دوال الوقت أو ملاحظة ترغب في مشاركتها، تفضّل بطرحها في صندوق التعليقات.

تتألف جداول اكسل التي نقوم بإنشائها من أنواع مختلفة من البيانات، وتعتبر بيانات الوقت والتاريخ جزءًا هامًا منها. ولاستخلاص معلومات معيّنة متعلّقة بالتاريخ يوفّر اكسل مجموعة دوال تندرج تحت قائمة Date & Time في مكتبة الدوال.

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

TODAY

تقوم هذه الدالة بإرجاع تاريخ اليوم الحالي، والصيغة العامة لها هي:

=TODAY()

مثال: لمعرفة تاريخ اليوم بواسطة دالة TODAY نكتب الصيغة التالية:

عمل الدالة لا يتطلّب تحديد متغيّرات، لذا نبقي الأقواس فارغة ونضغط على ENTER للحصول على النتيجة:

MONTH

تقوم هذه الدالة باستخراج رقم الشهر لتاريخ نحدّده في الصيغة. والصيغة العامة لها هي:

=MONTH(serial_number)

serial_number: التاريخ الذي نريد استخراج رقم الشهر منه. ويمكن أن يكون هذا المتغير خلية تحتوي على تاريخ معيّن، أو خلية تحتوي على صيغة تكون نتيجتها تاريخًا معيّنًا.

مثال: لاستخراج رقم الشهر للتاريخ في الخلية G2، والذي هو عبارة عن ناتج صيغة أخرى، نكتب الصيغة التالية:

سيتم إرجاع رقم الشهر للتاريخ المحدّد:

بإمكاننا أيضًا أن نكتب الصيغة بالطريقة التالية وسنحصل على نفس النتيجة:

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

DAY

عملها مشابه لعمل الدالة السابقة، إذ تقوم باستخراج رقم اليوم لتاريخ يحدّده المستخدم في الصيغة. والصيغة العامة لهذه الدالة هي:

=DAY(serial_number)

serial_number: التاريخ الذي نريد استخراج رقم اليوم منه. ويمكن أن يكون هذا المتغير خلية تحتوي على تاريخ معيّن، أو خلية تحتوي على صيغة تكون نتيجتها تاريخًا معيّنًا.

مثال: لاستخراج رقم اليوم للتاريخ في الخلية G2، نكتب الصيغة التالية:

وبالنتيجة سنحصل على الرقم "26" والذي هو رقم اليوم للتاريخ المحدّد:

YEAR

عمل هذه الدالة أيضًا مشابه لعمل الدالتين السابقتين، إذ تقوم بإرجاع رقم السنة للتاريخ الذي نحدّده في الصيغة. والصيغة العامة لها هي:

=YEAR(serial_number)

serial_number: التاريخ الذي نريد استخراج رقم السنة منه. ويمكن أن يكون هذا المتغير خلية تحتوي على تاريخ معيّن، أو خلية تحتوي على صيغة تكون نتيجتها تاريخًا معيّنًا.

مثال: لاستخراج رقم السنة من التاريخ في الخلية G2 نكتب الصيغة التالية:

وبالنتيجة سيتم إرجاع الرقم "2016":

DATE

تقوم هذه الدالة بتحويل أرقام السنة، الشهر، واليوم التي نحدّدها إلى تنسيق تاريخ. والصيغة العامة لها هي:

=DATE(year; month; day)

year; month; day: أرقام صحيحة تمثل السنة، الشهر، واليوم على التوالي. ويمكن أن نقوم بإدخالها يدويًا في الصيغة، أو تحديدها من خلية تحتوي على رقم مدخل يدويًا أو رقم ناتج من صيغة.

مثال: لتحويل الأرقام في الخلايا G3، G4، وG5 إلى تنسيق تاريخ، نكتب الصيغة التالية:

نضغط على ENTER لإظهار نتيجة الصيغة:

طريقة أخرى لكتابة الصيغة:

والنتيجة التي سيتم إرجاعها هي:

DATEVALUE

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

الرقم 1 = 01 كانون الثاني، 1900

الرقم 2= 02 كانون الثاني، 1900

الرقم 32= 01 شباط، 1900

وهكذا...

أي بعبارة أخرى يمكننا القول أنّ اكسل يخزن التاريخ كعدد الأيام منذ 01 كانون الثاني (يناير)، 1900.

الصيغة العامة لهذه الدالة هي:

=DATEVALUE(date_text)

date_text: النص الذي يمثل التاريخ.

مثال: لمعرفة الرقم التسلسلي للتاريخ "26/11/2016" نكتب الصيغة التالية:

وكما موضّح في الصيغة أعلاه، قمنا بإدخال التاريخ بشكل نص بين علامتي اقتباس وليس كمرجع خلية كما فعلنا مع بقية الدوال. نضغط ENTER لإرجاع النتيجة:

نلاحظ أنّ تنسيق الرقم في خلية النتيجة هو عام General، وعندما نغيّر التنسيق إلى تاريخ، سيتحوّل هذا الرقم التسلسلي إلى نفس التاريخ الذي قمنا بإدخاله في الصيغة:

WEEKDAY

تقوم هذه الدالة بإرجاع رقم صحيح يمثّل تسلسل اليوم في الأسبوع حسب التاريخ المحدّد في الصيغة. والصيغة العامة لها هي:

=WEEKDAY(serial_number; [return_type])

serial_number: تاريخ اليوم الذي نريد تطبيق الدالة عليه.

return_type: قيمة اختيارية تحدّد أي رقم يخصّص لكل يوم من أيام الأسبوع. الجدول التالي يوضّح قيم return_type والأرقام المقابلة المخصصة لكل يوم في الأسبوع.

إذا لم نقم بتحديد قيمة return_type سيتم افتراضها مساوية لرقم 1.

مثال: لمعرفة رقم اليوم "26/11/2016" في الأسبوع، نكتب الصيغة التالية:

بالنتيجة تم إرجاع الرقم 7. أي أنّ اليوم، السبت، هو سابع يوم في الأسبوع على افتراض أنّ قيمة return_type= 1 لأننا لم ندخلها في الصيغة. وحسب الجدول الموضّح أعلاه، يمثل الرقم 7 يوم السبت من الأسبوع:

أمّا إذا قمنا بتحديد قيمة return_type في الصيغة، ولتكن 16 مثلا:

سيتم إرجاع الرقم 1، على فرض أنّ السبت هو أول أيام الأسبوع حسب الجدول أعلاه:

WEEKNUM

تقوم هذه الدالة بإرجاع رقم صحيح يمثّل تسلسل الأسبوع في السنة حسب التاريخ المحدّد في الصيغة. والصيغة العامة لها هي:

=WEEKDAY(serial_number; [return_type])

serial_number: التاريخ الذي نريد تطبيق الدالة عليه.

return_type: قيمة اختيارية تحدّد أي نظام ترقيم نريد استخدامه وأي يوم في الأسبوع نريد اعتباره اليوم الأول في الأسبوع.

يوجد نظامان لترقيم الأسابيع في السنة:

النظام 1: وفيه يتم اعتبار الأسبوع الذي يتضمّن تاريخ 1 كانون الثاني هو الأسبوع الأول في السنة.

النظام 2: وفيه يتم اعتبار الأسبوع الذي يتضمّن أول يوم خميس في السنة هو الأسبوع الأول في السنة.

الجدول أدناه يوضّح قيم return_type وبدايات الأسبوع التي تقابلها حسب النظام المستخدم:

إذا لم نقم بتحديد قيمة return_type سيتم افتراضها مساوية لرقم 1.

مثال: لمعرفة رقم الأسبوع الحالي في عام 2016 حسب التاريخ 26/11/2016 (المدخل في الخلية G2) نكتب الصيغة التالية:

سيتم إرجاع الرقم 48، أي أنّ الأسبوع الحالي هو الأسبوع رقم 48 في السنة على اعتبار أن الأسبوع الذي يتضمّن تاريخ 1 كانون الثاني هو الأسبوع الأول في السنة وأنّ الأسبوع يبدأ من يوم الأحد (علمًا أن التاريخ 26/11/2016 يصادف يوم السبت)

أما إذا حددنا الرقم 16 كقيمة return_type كما في الصيغة أدناه:

سنلاحظ أنّ تسلسل نفس الأسبوع أصبح 49، وذلك لأنّه عند القيمة 16 يتم اعتبار يوم السبت هو بداية الأسبوع، والتاريخ الذي قمنا بتحديد في الصيغة يصادف يوم السبت، أي بداية أسبوع جديد:

خاتمة

لقد تعرّفنا في هذا الدرس على دوال التاريخ الأساسية وعلى كيفية استخدامها لمعرفة تاريخ اليوم واستخراج رقم اليوم، الشهر، والسنة من التاريخ. كما استخدمنا إحدى الدوال لمعرفة عدد الأيام التي مرّت منذ 01/01/1900 ودالتين أخريين لمعرفة تسلسل اليوم في الأسبوع وتسلسل الأسبوع في السنة. في الدرس القادم سنغطّي القسم الثاني من قائمة دوال Date & Time، وهي دوال الوقت الأساسية.

تعتبر القوائم المنسدلة في اكسل مفيدة على وجه الخصوص عند مشاركة المصنفات مع الآخرين. فهي تُستخدم للتحكّم بمدخلات مُستخدِم المصنف بإجباره على إدخال قيمة محددة في الخلية. ويتم اختيار هذه القيمة من بين مجموعة قيم مدخلة مسبقًا بشكل قائمة منسدلة.

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

إنشاء قائمة منسدلة

تُستخدم ثلاث طرق لتحديد خيارات القائمة المنسدلة:

• الفاصلة المنقوطة
• نطاق خلايا
• نطاق مسمّى باسم محدد

تؤدي كل الطرق إلى نفس النتيجة، لكن من الأفضل استخدام الطريقتين 2 أو 3 إذا كان عدد الخيارات كبيرًا الأمر الذي يجعل عملية إدخالها مطوّلة ومربكة. بالإضافة إلى ذلك تكون الطريقة الأولى حساسة لحالة الأحرف سواء كانت صغيرة أو كبيرة إذا كانت الخيارات باللغة الإنجليزية. إذ ستظهر افتراضيًا نافذة تنبيه بخطأ إذا قام المستخدم بإدخال قيمة يدويًا وكانت حالة الأحرف غير مطابقة لحالة الأحرف لأحد خيارات القائمة المنسدلة.

بصورة عامة، لإنشاء قائمة منسدلة، نحدد الخلية التي نريد إظهار الخيارات فيها ثم نذهب إلى تبويب Data> التحقق من صحة البيانات Data Validation:

نحدد الخيار List من قائمة Allow ثم ندخل خيارات القائمة بصورة مباشرة في حقل Source مع الفصل بين كل خيار وآخر بفاصلة منقوطة (;) إذا كان عددها صغيرًا:

بعد تحديد الخيارات ننقر على OK لإنشاء القائمة المنسدلة في الخلية المحددة:

ملاحظة: إذا كانت خيارات القائمة باللغة العربية ولم يتم إنشاء القائمة بشكل صحيح، استخدم الفاصلة المنقوطة الخاصة باللغة الإنجليزية (; وليس ؛).

إذا كان عدد خيارات القائمة كبيرًا، نقوم بإدخال الخيارات مسبقًا في نطاق خلايا (صف أو عمود). وعند إنشاء القائمة ننقر على زر السهم الأحمر الصغير في حقل Source ثم نحدد النطاق:

ملاحظة: يمكن تحديد النطاق من ورقة عمل أخرى وليس من ورقة العمل الحالية حصرًا، لهذا الأمر فائدة في حماية خيارات القائمة من التعديل كما سنشرح في فقرة لاحقة.

الطريقة الأخيرة لتحديد الخيارات هي بتسمية نطاق الخلايا الذي يحتوي على هذه الخيارات. نحدد النطاق ثم ندخل الاسم المرغوب في حقل الاسم ونضغط على مفتاح ENTER من لوحة المفاتيح:

يمكن أن يبدأ اسم النطاق بحرف أو فاصلة سفلية underscore (_) فقط. ويمكن أن يحتوي في الوسط على حروف، أرقام، نقاط، أو فواصل سفلية. لكن لا يمكن أن يحتوي على مسافات space، ولا يمكن أن يطابق اسم النطاق أحد أسماء الخلايا المعرّفة مسبقًا (A1، N6، Z19... إلخ).

ملاحظة: لتغيير اسم النطاق أو حذفه، نحدد النطاق ثم نذهب إلى تبويب Formulas> Name Manager:

نحدد الاسم المرغوب وننقر على Edit لتحريره أو Delete لحذفه:

عندما نريد إنشاء قائمة منسدلة باستخدام اسم النطاق، نضع مؤشر الفأرة في حقل Source ثم نضغط على مفتاح F3. سيظهر مربّع حوار صغير نحدده منه اسم النطاق المرغوب ثم ننقر على OK> OK:

وبكل الطرق سنحصل على نفس النتيجة:

من فوائد استخدام الطريقتين 2 أو 3 هو أنّه بإمكاننا تعديل خيارات القائمة من الورقة مباشرة دون الحاجة إلى استخدام مربّع الحوار Data Validation لأننا حددنا نطاق خلايا وليس قيمًا محدّدة.

نستطيع استخدام نفس القائمة المنسدلة في أكثر من مكان سواء في المصنّف الحالي أو في مصنّفات أخرى وذلك عن طريق تحديد الخلية التي تحتوي على القائمة المنسدلة ونسخها (CTRL+ C) ثم لصقها في المكان المرغوب (CTRL+ V).

إضافة تلميح شاشة وتنبيه خطأ للقائمة المنسدلة

بإمكاننا إرشاد المستخدم إلى وجود قائمة منسدلة في الخلية يمكنه اختيار أحد خياراتها باستخدام تلميح شاشة أو رسالة إدخال Input Message تظهر للمستخدم عند النقر على الخلية.

لإضافة رسالة إدخال وتخصيصها، ننقر على تبويب Input Message في مربّع الحوار Data Validation. بعدها نتأكّد من تأشير مربع الخيار Show input message when cell is selected، ثم ندخل عنوان الرسالة ومتنها في حقلي Title وInput Message على التوالي:

وبذلك سيظهر التلميح للمستخدم عند تحديد خلية القائمة المنسدلة:

بالإضافة إلى ذلك، نستطيع التحكّم أكثر في مدخلات الخلية التي تحتوي على قائمة منسدلة باستخدام رسائل أو تنبيهات الخطأ. فبشكل افتراضي يستطيع المستخدم ترك خلية القائمة المنسدلة فارغة دون أن يحدد أي خيار وينتقل إلى الخلية التالية باستخدام أحد المفتاحين TAB أو ENTER. لكن، إذا رغبنا في إجباره على تحديد أحد خيارات القائمة، في هذه الحالة نلغي تأشير الخيار Ignore Blank عند إنشاء القائمة المنسدلة:

في هذه الحالة ستظهر رسالة تنبيه تخبر المستخدم بأنّ القيمة المدخلة غير صحيحة:

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

لتخصيص تنبيه الخطأ، ننقر على تبويب Error Alert في مربع الحوار Data Validation. نتأكّد من تأشير الخيار Show error alert after invalid data is entered، نختار نمط التنبيه من قائمة Style، وندخل العنوان ورسالة الخطأ في حقلي Title وMessage على التوالي:

وبهذه الطريقة يمكننا تخصيص التنبيه وإظهار الرسالة المرغوبة:

إذا لم نكن ترغب في إضافة تلميح شاشة وتنبيه خطأ، نلغي تأشير مربعي الخيار Show input message when cell is selected و Show error alert after invalid data is enteredفي تبويبي Input Message وError Alert على التوالي.

حماية القوائم المنسدلة من التعديل

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

لإخفاء العمود/الصف ننقر عليه بزر الفأرة الأيمن ونختار Hide:

يمكننا أن نستدل على العمود أو الصف المخفي من تسلسل عناوين الأعمدة أو الصفوف (عند اختفاء أحد العناوين)، أو من خلال الخطين المتوازيين (العموديين في حالة الأعمدة، والأفقيين في حالة الصفوف) في منطقة العناوين:

فإذا رغبنا في إعادة إظهار العمود/الصف وتعديل مدخلات الخلية، نحدد العمودين/الصفين اللّذين يقعان قبل وبعد العمود/الصف المخفي، ننقر عليهما بزر الفأرة الأيمن ونختار Unhide:

أمّا إخفاء الورقة التي تحتوي على مصدر خيارات القائمة فيتم بالنقر على اسم الورقة بزر الفأرة الأيمن واختيار Hide:

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

لإعادة إظهار الورقة المخفية، ننقر بزر الفأرة الأيمن على اسم أحد أوراق العمل ونختار Unhide:

نحدد الورقة التي نريد إظهارها ثم ننقر على OK:

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

لحماية الورقة بكلمة سرية ننقر على اسمها بزر الفأرة الأيمن ونختار Protect Sheet:

ثم نقوم بإدخال الكلمة السرية وتأكيدها مرة أخرى.

لاحقًا، عندما يحاول المستخدم إجراء أي تغيير على الورقة ستنبثق نافذة تنبّهه إلى أنّ الورقة محمية ولا يمكن تعديلها:

لفك حماية الورقة، ننقر عليها بزر الفأرة الأيمن، نختار Unprotect Sheet، ثم نؤكد إلغاء الحماية بإدخال الكلمة السرية:

الإجراء الثالث هو بحماية الخلايا بكلمة سرية، وهو برأيي الخيار الأفضل. فبدلًا من حماية أو إخفاء الورقة بالكامل، نقوم بحماية نطاق الخلايا الذي يحتوي على خيارات القائمة المنسدلة فقط. لكن قبل أن نقوم بذلك يجب أن نغيّر خيار حماية الورقة الافتراضي. ننقر بزر الفأرة الأيمن على أيقونة المثلث في زاوية الورقة لتحديد الورقة بالكامل، ثم نختار Format Cells:

من تبويبProtection نلغي تحديد مربّع الخيار Locked ثم ننقر على OK:

نحدد نطاق الخلايا الذي نريد حمايته بكلمة سرية، ننقر عليه بزر الفأرة الأيمن ونختار Format Cells:

نقوم بتأشير مربع الخيار Locked من تبويب Protection ثم ننقر على OK:

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

لمعرفة المزيد حول حماية المصنفات وأوراق العمل تفضّل بالاطلاع على هذا الدرس.

تعديل خيارات القائمة المنسدلة

لتعديل القائمة المنسدلة التي تم تحديد خياراتها باستخدام الفاصلة المنقوطة، نحدد الخلية التي تحتوي القائمة، ثم ننقر على Data Validation من تبويب Data. بعد ذلك نجري التعديل المرغوب على حقل Source ثم ننقر على OK:

أما القائمة المنسدلة التي تم تحديد خياراتها باستخدام نطاق خلايا أو النطاق المسمّى باسم معيّن، فيمكننا إجراء التعديل بشكل مباشر على محتويات الخلية وليس من مربع الحوار Data Validation. وإذا رغبنا في إضافة خيار إلى القائمة، ننقر بزر الفأرة الأيمن على إحدى خلايا النطاق ونختار Insert:

نبقي الخيار الافتراضي Shift Cells Down مؤشرًا ثم ننقر على OK:

ثم ندخل الخيار الجديد في الخلية الفارغة التي تم إدراجها:

وسينعكس التغيير الذي قمنا بإجرائه على قائمتنا المنسدلة:

والخطوات نفسها نطبّقها عندما نريد حذف أحد خيارات القائمة المنسدلة. ننقر بزر الفأرة الأيمن على خلية النطاق التي تحتوي الخيار الذي نريد حذفه ثم نختار Delete، وسينعكس هذا التغيير على قائمتنا المنسدلة:

أمّا إذا رغبنا في حذف القائمة المنسدلة برمّتها من الخلية، نحدد الخلية ثم نذهب إلى Data> Data Validation> Clear All:

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

الماكرو هو في الحقيقة عبارة عن شيفرة VBA (Visual Basic for Applications) يتم تخزينها في مستند مستقل. لكن إنشاء الماكرو لا يتطلّب منك كتابة أيّة شيفرة، وإنّما هي خطوات سهلة سنوضّحها في هذا الدرس.

كيفية تسجيل الماكرو واستخدامه

باختصار، طريقة عمل الماكرو هي عبارة عن تسجيل لخطوات تنفيذ المهمة ومن ثم إعادة استخدامها مرارًا وتكرارًا عند الحاجة إليها، بحيث يتم تنفيذ المهمة بنقرة واحدة فقط.

قبل أن نبدأ بتسجيل الماكرو، سنقوم بإضافة تبويب المطور Developer إلى تبويبات اكسل لنتمكّن من الوصول إلى كل الأوامر المتعلّقة بالماكرو. نذهب إلى ملف File> خيارات Options:

ننقر على قسم Customize Ribbon ثم نؤشر مربّع الخيار Developer من مجموعة Main Tabs وننقر على OK:

ملاحظة1: يمكن الوصول إلى أوامر الماكرو الأساسية من تبويب View أيضًا:

ملاحظة2: خاصية الماكرو غير متوفرة في إصدارات أوفيس على الأجهزة التي تستخدم نظامWindows RT ولمعرفة إصدار أوفيس الذي تستخدمه اذهب إلى File> Account وستجد اسم الإصدار ورقمه تحت عبارة "معلومات المنتج" Product Information:

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

نذهب أولًا إلى تبويب Developer> Record Macro:

نقوم بتسمية الماكرو من خلال مربع الحوار Record Macro بإدخال اسم دلالي في حقل Macro Name، علمًا أنّ اسم الماكرو لا يمكن أن يحتوي على مسافات space:

سنلاحظ بعد النقر على OK تحول زر Record Macro إلى Stop Recording، وهذا يعني أنّه جاري التسجيل، عندها نبدأ بإنشاء الجدول وتنسيقه حسب الرغبة، وعند الانتهاء ننقر على Stop Recording لإيقاف التسجيل وحفظ الماكرو:

في المرة القادمة التي نريد فيها إنشاء نفس الجدول، كل ما علينا فعله هو النقر على Macros لفتح قائمة الماكرو الذي قمنا بحفظه مسبقا:

ثم تحديد الماكرو المرغوب والنقر على زر Run:

وسيتم إنشاء جدولنا بلمحة:

ربّما سنلاحظ أنّه في كل مرة نقوم فيها بتشغيل الماكرو يتم إنشاء الجدول في نفس نطاق الخلايا، مهما كان موقع الخلية التي قمنا بتحديدها قبل تشغيله. أي أنّه حتّى لو قمنا بتحديد الخلية L15 مثلا ثم تشغيل الماكرو، سيتم إنشاء الجدول في النطاق A1:G8:

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

في مثالنا هذا سنقوم بتسجيل الماكرو من جديد باستخدام المراجع النسبية. ننقر على زر Use Relative References ثم على زر Record Macro ونتبع نفس الخطوات السابقة:

وبهذه الطريقة يمكننا تشغيل الماكرو وإنشاء الجدول في أي موضع نريده:

تحرير الماكرو

إنّ تحرير الماكرو يتم عبر محرر النصوص الخاص Visual Basic Editor، حيث أنّ كل ماكرو نقوم بتسجيله يخزن على شكل شيفرة برمجية، ولإجراء أي تغيير على الماكرو يجب أن نقوم بتعديل هذه الشيفرة.

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

ننقر على زر Macros من تبويب Developer، ثم نحدد الماكرو الذي نريد تحريره وننقر على Edit:

سيُفتح محرر Visual Basic في نافذة مستقلة تحتوي على شفرات كل ماكرو قمنا بتسجيله، وسنلاحظ أنّ شيفرة الماكرو تبدأ بعبارة "Sub" متبوعة باسم الماكرو، وتنتهي بعبارة "End Sub":

نبحث عن "الحصة 1" في الشيفرة، ونستبدلها بـ "الدرس 1":

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

بعد ذلك نغلق نافذة محرر الشفرات لحفظ التغيير ثم نختبر الماكرو بعد التعديل:

مثال آخر، قمنا بتعبئة نطاق الخلايا B2:B13 بأسماء الأشهر باستخدام التعبئة التلقائية أثناء تسجيل الماكرو، ونريد أن نعدّل الماكرو بتحويل العمود إلى صف:

نفتح شيفرة الماكرو بنفس الطريقة المذكورة أعلاه، ونستبدل نطاق التعبئة التلقائية B2:B13 في الشيفرة بالنطاق B2:M2:

ثم نختبر الماكرو بعد التعديل:

حفظ الماكرو

بإمكاننا حفظ الماكرو في مصنّف شخصي مخفي Personal Workbook واستخدامه عند الحاجة في جميع المصنفات التي نقوم بإنشائها من على نفس جهاز الحاسوب.

لحفظ الماكرو في المصنف الشخصي نحدد الخيار Personal Macro Workbook من قائمة Store macro in عند البدء بتسجيل الماكرو:

لكن إذا كنّا نريد حفظ ماكرو قمنا بتسجيله بالفعل في المصنف الشخصي نتبع الخطوات التالية:

نقوم بتسجيل ماكرو جديد فارغ بنفس اسم الماكرو الذي نريد حفظه في الملف الشخصي، مع الأخذ في الاعتبار تحديد الخيار Personal Macro Workbook عند تخصيص خيارات التخزين:

بمجرّد بدء تسجيل الماكرو ننقر على Stop Recording، فكما ذكرنا نريده فارغًا لأننا سنسخ شيفرة الماكرو الأصلي عليه.

ننقر على Macros ثم نحدد الماكرو الأصلي وننقر على Edit لتحريره:

ننسخ الشيفرة برمّتها (من Sub إلى End Sub) ثم نغلق نافذة محرر الشيفرة:

بعد ذلك ننقر على الماكرو الفارغ المحفوظ في المصنف الشخصي وننقر على Edit، لكن في هذه المرة سيظهر تنبيه على الشاشة يخبرنا أنّه يجب إظهار المصنف الشخصي أولًا لكي نتمكّن من تحريره:

في هذه الحالة نلغي عملية التحرير ونذهب إلى تبويب عرض View، ثم ننقر على زر Unhide:

نحدد المصنّف الشخصي PERSONAL ثم ننقر على OK لإظهاره:

نرجع إلى Macros> نحدد الماكرو الفارغ وننقر على Edit. عند فتح نافذة تحرير الشيفرة، نحذف كل محتوياتها ثم نلصق الشيفرة التي قمنا بنسخها سابقًا:

نغلق نافذة المحرّر، ثم نخفي المصنّف الشخصي من جديد. نذهب إلى تبويب View ونختار PERSONAL من قائمة Switch Windows:

ثم ننقر على زر Hide لإخفائه:

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

عند إغلاق المصنّف الذي نعمل عليه، ستنبثق نافذة تسألنا فيما إذا كنّا نرغب في حفظ التغييرات التي قمنا بإجرائها على المصنف الشخصي، ننقر على Yes للتأكيد:

إضافة أزرار لتشغيل الماكرو

إذا كنّا نستخدم الماكرو بكثرة، بإمكاننا توفير بعض الوقت وإضافة أزرار مخصصة لتشغيل الماكرو بسرعة عند الحاجة. يمكن إضافة زر لكل ماكرو في تبويب Developer أو على شريط أدوات الوصول السريع.

إضافة زر للماكرو في تبويب Developer

نذهب إلى تبويب File> Options> Customize Ribbon ثم نحدد تبويب Developer:

ننقر على New Group لإضافة مجموعة جديدة خاصة بالماكرو الذي نستخدمه:

ننقر على Rename لتسمية المجموعة ونختار اسمًا دلاليًا:

بعد ذلك نحدد الخيار Macros من القائمة المنسدلة Choose commands from:

نحدد الماكرو الذي نريد إضافة زر له، ثم ننقر على زر Add:

بإمكاننا إعادة تسمية الماكرو وتخصيص أيقونة له بالنقر على زر Rename:

بعد الانتهاء ننقر على OK وستتم إضافة زر للماكرو في مجموعة جديدة في تبويب Developer:

إضافة زر للماكرو في شريط أدوات الوصول السريع

نذهب إلى File> Options> Quick Access Toolbar. ومن قائمة Choose command from نختار Macros:

نحدد الماكرو الذي نريد إضافة زر له ثم ننقر على Add:

وبنفس الطريقة يمكننا إعادة تسمية الماكرو وتخصيص أيقونة له بالنقر على زر Modify:

بعد الانتهاء ننقر على OK لإضافة زر الماكرو إلى شريط أدوات الوصول السريع:

الآن، لتشغيل الماكرو، كل ما علينا فعله هو النقر على الزر الذي قمنا بإضافته سواء كان على تبويب Developer أو على شريط أدوات الوصول السريع.

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

تقسيم الأجزاء

تشابه هذه الأداة أداة تجميد الأجزاء Freeze Panes التي تحدثنا عنها في درس تثبيت صفوف أو أعمدة في أماكنها عند استعراض جداول بيانات كبيرة في اكسل. فهي تستخدم لتقسيم ورقة العمل إلى قسمين أفقيين، عموديين، أو إلى أربعة أقسام لعرض أجزاء مختلفة من الورقة في نفس الوقت. الفرق هو أن الجزء المجمّد يبقى ثابتًا في مكانه بينما يمكن تحريك الأجزاء الأخرى غير المجمّدة. أما في التقسيم، فتكون جميع الأجزاء قابلة للتمرير.

يعتبر تقسيم الأجزاء مفيدُا على وجه الخصوص عند مقارنة البيانات في الجداول المكونة من عدد كبير من الصفوف أو الأعمدة.

على سبيل المثال، الجدول أدناه يعرض مبيعات ما يقارب 130 منتجًا:

لنفترض أننا نريد مقارنة مبيعات المنتج "31111" مع منتجات أخرى تقع في أسفل الجدول. بدلا من التمرير صعودًا ونزولًا من أجل المقارنة، سنقوم بتقسيم الورقة.

نحدد أول خلية في الصف الذي يقع تحت الصف الذي نريد وضع فاصل التقسيم عنده، وهي الخلية A7 في هذا المثال، لأنّ بيانات المنتج "31111" تقع في الصف السادس:

بعد ذلك نذهب إلى تبويب عرض Viewوننقر على زر انقسام Split:

بعد التقسيم، سيكون بإمكاننا تمرير الجزء الذي يقع أسفل الصف 6 بشكل مستقل عن الجزء العلوي الذي يحتوي على المنتج الذي نريد مقارنته. مما يجعل عملية المقارنة أسهل وأسرع بكثير. علمًا أنّ الجزء العلوي قابل للتمرير بشكل مستقل أيضًا:

وبالمثل، يمكن تقسيم الورقة بشكل عمودي أيضًا. على سبيل المثال، إذا رغبنا في وضع فاصل تقسيم بعد العمود D، نحدد الخلية E1 ثم ننقر على زر Split:

كما يمكن تقسيم الورقة أربعة أقسام بتحديد تحت الصف وعلى يسار العمود حيث نريد وضع فاصل التقسيم:

وبالطبع، جميع أقسام الورقة قابلة للتمرير، مما يجعل من السهل استعراض أكثر من منطقة من الورقة في نفس الوقت.

لإلغاء التقسيم قم بالنقر على زر Split مرة أخرى وسيختفي الفاصل.

ترتيب المصنفات

يوفر اكسل أيضًا خاصية Arrange All التي تعمل على عرض أكثر من مصنف وترتيبها في شاشة واحدة. ويمكنك استخدامها عندما تريد العمل على أكثر من مصنف في نفس الوقت.

نقوم أولا بفتح جميع المصنفات التي نريد العمل عليها بالتزامن، ثم نذهب إلى تبويب عرض View من أحد المصنفات وننقر على زر Arrange All:

نحدد أحد خيارات الترتيب المتاحة: تجانب Title، أفقي Horizontal، عمودي Vertical، أو بالتتالي Cascade. المصنفات أدناه مرتبة بالتجانب:

أمّا إذا كان المصنف يتكون من أكثر من ورقة عمل، ونريد العمل على كل الأوراق في نفس الوقت، في هذه الحالة نستخدم أمر New Window.

يُستخدم هذا الأمر لفتح أكثر من نافذة لنفس المصنف في نفس الوقت. لذا سنقوم بفتح المصنف الرئيسي وننقر على أمر New Window من تبويب عرض View:

ستُفتح نافذة جديدة لنفس المصنف، وسنلاحظ إضافة ترقيم لعدد النوافذ المفتوحة بجانب اسم المصنف:

نفتح نوافذ جديدة بعدد أوراق العمل التي نريد العمل عليها، ثم نفعّل ورقة العمل الأولى في النافذة الأولى، وورقة العمل الثانية في النافذة الثانية وهكذا. بعد ذلك نقوم بترتيب النوافذ المفتوحة باستخدام أحد خيارات Arrange All. النوافذ التالية مرتبة بشكل أفقي:

تكبير جزء محدد من الورقة

التكبير والتصغير أيضًا وسيلة للتحكم بطريقة عرض الورقة. وكما هو معلوم لجميع المستخدمين، يمكننا تكبير أو تصغير محتويات الورقة بتمرير المنزلق على شريط الحالة إلى اليمين أو اليسار:

يمكن أيضًا تكبير جزء محدد من الورقة بشكل مباشر باستخدام أمر Zoom to Selection، وتعتمد نسبة التكبير على حجم التحديد. فكلما قل حجم النطاق المحدد، زادت نسبة التكبير.

نقوم أولا بتحديد الجزء المراد تكبيره ثم ننقر على زر Zoom to Selection من تبويب View:

وللعودة إلى حجم الورقة الطبيعي ننقر على زر 100%.

نحتاج في كثير من الأحيان عند العمل على مصنفات اكسل، وخصوصًا الكبيرة منها، إلى العثور على خلايا ذات مواصفات معينة وتحديدها لتنفيذ إجراء ما عليها، كالنسخ/اللصق، التحرير، أو غيره. ولحسن الحظ يوفّر اكسل خاصية Go to Special التي تسهل تلك العملية المطولة وتوفّر الكثير من الوقت.

قبل أن نبدأ بشرح خاصية Go to Special، لنوضّح أولا الفرق بينها وبين خاصية البحث Find. معلوم أنّ كلا الخاصيتين تستخدمان للعثور على بيانات محددة، لكن الفرق هو أنّه عند استخدام البحث نقوم بإدخال "قيمة" محددة، سواء كانت نصيّة أو رقمية، ومن ثم يقوم اكسل بالبحث عنها وإظهار النتائج واحدة تلو الأخرى إن وجدت. أما عند استخدام Go to Special فإننا نحدد صفة أو خاصية معينة تتصف بها الخلية، ثم يقوم اكسل بـ "تحديد" جميع الخلايا التي ينطبق عليها معيار البحث دُفعة واحدة.

كيف أستخدم Go to Special؟

يمكنك الوصول إلى مربع الحوار Go to Special بثلاث طرق؛ اختر الأنسب لك:

• الضغط على مفتاح F5 لفتح مربع الحوار Go To ثم النقر على زر Special:

• الضغط على مفتاحي الاختصار Ctrl+ G لفتح نفس مربع الحوار، ثم النقر على زر Special.
• النقر على Go to Special من قائمة Find & Select في تبويب الصفحة الرئيسية Home:

يعرض مربع الحوار Go to Special مجموعة من الخيارات التي تمثل مواصفات الخلية/الخلايا التي تريد تحديدها:

• Comment: لتحديد كل الخلايا التي تحتوي على تعليقات.
• Constants: لتحديد الخلايا التي تحتوي على ثوابت، والخيارات الفرعية هي: أرقام Numbers، نصوص Text، عبارات منطقية Logical (TRUE أو FALSE) ، أو أخطاء Errors. وهو من الخيارات المفيدة لتحديد نوع معيّن من المدخلات بشكل مستقل.
• Formulas: لتحديد كل الخلايا التي تحتوي على صيغة. والخيارات الفرعية هي نفسها لخيار Constant. يمكنك مثلا الاستفادة من هذا الخيار لتتبع جميع الصيغ التي ترجع نتيجة خاطئة بتحديد الخيار Errors.
• Blanks: لتحديد كل الخلايا الفارغة التي لا تحتوي على أي مدخلات.
• Current Region: لتحديد منطقة البيانات الحالية. يجب أولا تحديد خلية داخل المنطقة المُراد تحديدها.
• Current Array: لتحديد كل خلايا المصفوفة التي تنتمي لها الخلية المحددة، وهذا الخيار مفيد على وجه الخصوص عند استخدام صيغ الصفيف Array Formulas.
• Object: لتحديد كل العناصر في الورقة، كالصور، المخططات، SmartArt، إلخ.
• Row differences: لتحديد الخلايا المختلفة عن الخلية الفعّالة في الصف المحدد حاليًا. ويُقصد بالمختلفة؛ الاختلاف في القيمة إذا كانت مدخلات الصف ثوابت، أو الاختلاف في الصيغة إذا كانت خلايا الصف تحتوي على صيغ. وهذا الخيار مفيد لاكتشاف عدم الاتساق في الصيغ.
• Column differences: يشابه الخيار السابق فيما عدا أنّه يحدد الخلايا المختلفة عن الخلية الفعّالة في العمود المحدد حاليًا.
• Precedents: لتحديد جميع الخلايا التابعة للخلية المحددة إذا كانت تحتوي على صيغة. أي يتم تحديد الخلايا التي تحتوي على معطيات الصيغة.
• Dependents: لتحديد الخلية/الخلايا التي تعتمد على الخلية المُحددة بشكل أو بآخر. على سبيل المثال إذا كانت الخلية المحددة إحدى معطيات صيغة ما، سيتم تحديد الخلية التي تحتوي على تلك الصيغة. يمكننا اعتبار هذا الخيار معكوس الخيار السابق.
• Last Cell: لتحديد آخر خلية مستخدمة في ورقة العمل؛ تلك التي تحتوي على بيانات أو تنسيق.
• Visible cells only: إذا كان لديك أعمدة، أو صفوف مخفية، سيتم تجاهلها وتحديد الخلايا الظاهرة فقط.
• Conditional formats: لتحديد الخلايا المنسقة تنسيقًا شرطيًا.
• Data validation: لتحديد الخلايا المطبّق عليها إحدى قواعد التحقق من صحة البيانات.

ملاحظات

• عند استخدام Go to Special سيتم تحديد الخلايا في الورقة الحالية فقط وحسب الخيار المحدد، وليس في جميع أوراق المصنف.
• يتم البحث وتحديد الخلايا في النطاق المحدد فقط سواء كان صفًا، عمودًا، أو تركيبًا من الصفوف والأعمدة. فإذا رغبت أن يتم البحث في جميع خلايا الورقة، قم بتحديد خلية واحدة فقط قبل استخدام Go to special.

أمثلة

بإمكانك تحميل هذا المصنف Go to Special Example.xlsx  وتطبيق الخطوات الواردة في الأمثلة لكي تكون طريقة استخدام Go to Special واضحة ومفهومة أكثر:

تحديد الخلايا الفارغة

• نحدد الجدول (A2:E13) ونضغط F5 ثم ننقر على Special.
• نحدد الخيار Blanks وسيتم تحديد جميع الخلايا الفارغة في الجدول:

تحديد الاختلافات في العمود

• نحدد "عمود المبيعات (صيغة عادية)"، أي نطاق الخلايا E2:E13 ونضغط F5 ثم ننقر على Special (لاحظ أنّ الخلية الفعّالة لدينا هي E9):

• نؤشر الخيار Column differences وسيتم تحديد الخلية المختلفة إن وجدت. في هذا المثال تعمّدنا تغيير صيغة الخلية E7 لتصبح مختلفة عن صيغة الخلية E9 وصيغة بقية خلايا العمود. لذلك سيتم تحديد الخلية E7 عند النقر على OK:

تحديد الخلايا المعتمِدة

• نحدد إحدى خلايا عمود "عدد الوحدات المباعة" ولتكن B2 مثلا ثم نضغط F5 وننقر على Special.
• نؤشر الخيار Dependents ثم الخيار All Levels ليتم تحديد جميع الخلايا التي تعتمد على الخلية الفعّالة (B2) بجميع المستويات وليس بصورة مباشرة فقط:

تحديد المصفوفة الحالية

• نحدد إحدى خلايا عمود "المبيعات (صيغة صفيف)" لأنّه يمثل نتيجة متعددة لصيغة صفيف، ثم نضغط F5 وننقر على Special.
• نؤشر الخيار Current array وسيتم تحديد جميع خلايا المصفوفة:

تحديد الثوابت المنطقية

• نحدد إحدى خلايا الورقة ثم نضغط F5 وننقر على Special.
• نؤشر الخيار Constants ثم الخيار Logicals ونلغي تأشير بقية الخيارات الفرعية، ليتم تحديد جميع العبارات المنطقية في الورقة والتي قمنا بإدخالها مسبقًا:

وهكذا يمكنك تجربة واستكشاف بقية الخيارات المتاحة.

اللصق الخاص Paste Special

إذا كنت ستستخدم خاصية Go to Special لتحديد ونسخ خلايا ذات مواصفات معينة، فمن المفيد أن تتعرف أيضًا على خاصية اللصق الخاص التي تتيح لك المزيد من المرونة فيما يخص النسخ واللصق.

للوصول إلى مربع الحوار Paste Special، انسخ الخلايا التي تريد لصقها في مكان آخر ثم انقر على الخلية التي تريد لصق البيانات فيها بزر الفأرة الأيمن، أشر بالمؤشر فوق Paste Special ثم انقر على Paste Special في نهاية القائمة:

يعرض مربع الحوار Paste Special العديد من خيارات اللصق، أهمها:

• All: لصق جميع محتويات الخلية المنسوخة بما فيها التنسيق والصيغة.
• Formulas: لصق صيغة الخلية المنسوخة فقط.
• Values: لصق القيمة في الخلية المنسوخة فقط بدون الصيغة أو التنسيق.
• Formats: لصق التنسيق فقط بدون القيم.
• Validation: لصق قواعد التحقق من صحة البيانات المطبقة على الخلية المنسوخة.
• Column Width: لصق عرض العمود الذي يحتوي الخلية المنسوخة فقط.

بالإضافة إلى ذلك، يمكنك اللصق وإجراء العمليات الحسابية الأساسية (جمع، طرح، ضرب، قسمة) في نفس الوقت. على سبيل المثال، إذا كانت القيمة في الخلية المنسوخة 40، وتريد لصقها فوق خلية أخرى تحتوي على قيمة 50 مع القيام بعملية الجمع في نفس الوقت، في هذه الحالية يمكنك تأشير الخيار Add من مجموعة Operations.

كما يتوفر خيار Transpose لتحويل الصف إلى عمود أثناء اللصق إذا قمت بنسخ نطاق من الخلايا وليس خلية واحدة فقط.

ذاكرة الحافظة Clipboard

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

لنستخدم المصنف المرفق سابقًا كمثال. لنفترض أننا نريد نسخ القيم في عمود "المنتج" ولصقها في ورقة أخرى لكن بترتيب مختلف. بدلا من نسخ العنصر الأول ولصقه، ثم الرجوع للعنصر الثاني لنسخه ولصقه وهكذا، نقوم بنسخ العنصر الأول، ثم الثاني، ثم الثالث تباعًا وستتم إضافتها إلى ذاكرة الحافظة حسب ترتيب عملية النسخ، بحيث تكون العناصر المنسوخة حديثًا في الأعلى.

بعد ذلك ننتقل إلى الورقة الجديدة التي نريد لصق العناصر فيها ونفتح جزء ذاكرة الحافظة من تبويب الصفحة الرئيسية:

الخطوة التالية هي تحديد الخلية التي نريد لصق العنصر الأول فيها ثم النقر على العنصر الأول في ذاكرة الحافظة ليتم لصقه بشكل مباشرة. ثم نحدد الخلية الثانية وننقر على العنصر الثاني، وهكذا وحسب الترتيب المرغوب، أو يمكن النقر على Paste All للصق جميع العناصر في ذاكرة الحافظة وبترتيب عملية النسخ من الأقدم إلى الأحدث:

يمكن تخزين ما يصل إلى 24 عنصر في ذاكرة الحافظة، علمًا أنّ العناصر تحفظ على ذاكرة النظام وليس على اكسل فقط. أي يمكن استخدام عناصر الذاكرة في جميع برامج أوفيس والبرامج الأخرى التي تعمل فيها خاصية النسخ واللصق.

إذا تجاوز عدد العناصر المخزنة 24، ستتم إزالة العنصر الأقدم واستبداله بالأحدث كلما قمت بعملية النسخ. وبالإمكان حذف عناصر محددة من الذاكرة بالنقر على أيقونة السهم بجانب العنصر واختيار Delete:

أو تنظيف ذاكرة الحافظة وحذف جميع العناصر بالنقر على زر Clear All:

خاتمة

لقد تعرّفنا على خاصية Go to Special الرائعة وتعلّمنا كيفية استخدامها لتحديد أنواع مختلفة من الخلايا. وتظهر فائدتها في المصنفات الكبيرة، حيث تسهل العثور على الخلايا التي نريدها بشكل سريع. كما تعلّمنا كيفية استخدام اللصق الخاص وجزء ذاكرة الحافظة للتحكم أكثر في عمليات النسخ واللصق.

إذا كان لديك أي سؤال بخصوص الأدوات المذكورة لا تتردد بطرحة في صندوق التعليقات.

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

توجد في ميكروسوفت اكسل مجموعة قوائم مضمّنة، مثل الأشهر (كانون الثاني، شباط، آذار...إلخ)، الأيام (الأحد، الإثنين...إلخ)، وغيرها. وهذه القوائم غير قابلة للتعديل. وإذا رغبت في الفرز أو التعبئة حسب قوائم غير تلك المضمنة، فعليك بإنشاء قائمة مخصصة.

كيفية إنشاء القوائم المخصصة

لنفترض أننا نقوم بشكل متكرر وفي كثير من المصنفات بإدخال مجموعة منتجات بترتيب محدد، ونريد أن ننشئ قائمة مخصصة تتكون من أسماء تلك المنتجات وبالتسلسل المرغوب لاستخدامها في كل مرة نحتاجها. نذهب إلى ملف File> خيارات Options> خيارات متقدمة Advanced ثم ننقر على زر تحرير القوائم المخصصة Edit Custom Lists في قسم عام General:

ننقر فوق قائمة جديدة New List ثم نبدأ بإدخال عناصر القائمة من الأول إلى الأخير، ونضغط على مفتاح Enter بعد إدخال كل عنصر للفصل بينهم. بعد اكتمال القائمة ننقر على زر إضافة Add ثم OK:

إذا كانت القائمة مدخلة مسبقًا في الخلايا، فلا داعي لإدخالها يدويًا من جديد. قم بتحديد نطاق الخلايا الذي يحتوي على عناصر القائمة ثم اذهب ملف File> خيارات Options> خيارات متقدمة Advanced> تحرير القوائم المخصصة Edit Custom Lists ثم تأكّد من أنّ خلايا النطاق محددة في المربع استيراد القائمة من الخلايا Import list from cells وانقر على زر استيراد Import لإضافة القائمة:

كيفية استخدام القوائم المخصصة

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

قمنا بفتح مصنف جديد ونريد استخدام قائمة المنتجات المخصصة التي قمنا بإنشائها للتو في جدول معين. لذلك سنقوم بإدخال أول عنصر من القائمة في الخلية المرغوبة، وهو "أقلام" في مثالنا، ثم ننقر على مربع التعبئة التلقائية في زاوية الخلية ونسحب إلى الأسفل:

وهكذا نرى مدى سرعة وسهولة إدخال قائمة من البيانات، مهما كانت طويلة، باستخدام القوائم المخصصة والتعبئة التلقائية.

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

لفرز البيانات نذهب إلى تبويب بيانات Data ثم ننقر على زر فرز Sort:

من قائمة فرز حسب Sort By نختار العمود "المنتج"، ومن قائمة ترتيب Order نختار قائمة مخصصة Custom List:

من مربع الحوار Custom List نحدد القائمة التي نريد الفرز بواسطتها ثم ننقر على OK:

ننقر على OK مرة أخرى وسيتم فرز بيانات العمود حسب ترتيب القائمة المخصصة:

حذف القوائم المخصصة

بالإمكان حذف القوائم المخصصة التي نقوم بإنشائها يدويًا، ولا يمكن حذف أو القوائم المضمّنة في اكسل. لحذف قائمة مخصصة نذهب إلى > خيارات Options> خيارات متقدمة Advanced> تحرير القوائم المخصصة Edit Custom Lists ثم نحدد القائمة التي نريد حذفها وننقر على زر Delete:

خاتمة

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

إذا كان لديك أي سؤال أو استفسار بخصوص القوائم المخصصة لا تتردد بطرحه في صندوق التعليقات.

صيغة الصفيف، وتسمّى أيضًا صيغة Ctrl Shift Enter (وتُعرف اختصارًا بـ CSE ) هي عبارة عن صيغة تقوم بتنفيذ عمليات حسابية على قيمة واحدة أو أكثر من بيانات المصفوفة وترجع نتيجة واحدة أو أكثر. والمصفوفة هي عبارة عن مجموعة بيانات مرتبة في صف، عمود، أو مجموعة من الصفوف والأعمدة.

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

إنشاء صيغة صفيف Array Formula

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

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

أولًا، نحدد الخلية التي نريد إيجاد النتيجة فيها، وهي الخلية D11 في مثالنا، ونكتب علامة = ثم دالة الجمع SUM. بعد ذلك نفتح قوسًا هلاليًا ونحدد عدد الوحدات المباعة لكل المنتجات (أي نطاق الخلايا B2:B8)، وأخيرًا نحدد سعر الوحدة لكل منتج (أي نطاق الخلايا C2:C8) ونغلق القوس لتصبح الصيغة كالتالي:

نأتي الآن إلى الخطوة المهمة، وهي الضغط على المفاتيح Ctrl+ Shift+ Enter بدلا من المفتاح Enter فقط للحصول على النتيجة:

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

ستعمل الصيغة كالتالي: سيتم ضرب كل قيمة في عمود "عدد الوحدات المباعة" بما يقابلها في عمود "السعر"، ومن ثم يتم جمع النتائج للحصول على المجموع الكلي للمبيعات.

يمكن أيضًا استخدام صيغة الصفيف لإرجاع نتائج متعددة. على سبيل المثال إذا رغبنا في إيجاد مبيعات كل منتج على حدة، نحدد نطاق الخلايا D2:D8 في عمود "المبيعات" ونكتب الصيغة التالية:

ولا ننس الضغط على المفاتيح Ctrl+ Shift+ Enterلإرجاع النتائج. لأنّ الضغط على المفتاح Enter فقط سيرجع نتيجة واحدة فقط في الخلية D2:

ليست دالة الجمع هي الوحيدة التي تستخدم في صيغة الصفيف، بل يمكن استخدام الدوال الأخرى مثل المتوسط AVERAGE، الأكبر MAX، الأصغر MIN أو أيّة دالة أخرى مضمّنة في اكسل.

على سبيل المثال، لحساب متوسط المبيعات لنفس الجدول، نكتب الصيغة التالية:

ثم نضغط على المفاتيح Ctrl+ Shift+ Enter لإيجاد النتيجة. وبنفس الطريقة ستعمل الصيغة على ضرب القيم في عمود "عدد الوحدات المباعة" بما يقابلها في عمود "السعر"، ومن ثم يتم إيجاد مجموع المبيعات وقسمته على عدد المنتجات:

وإذا أردنا إيجاد المنتج ذي المبيعات الأعلى، نحدد الخلية التي نريد إيجاد النتيجة فيها ثم نكتب الصيغة التالية:

نضغط على المفاتيح Ctrl+ Shift+ Enter وسيتم حساب مبيعات كل منتج ومن ثم إرجاع أعلى قيمة للمبيعات في خلية الناتج:

تحرير وحذف صيغة الصفيف

إنّ تحرير وحذف صيغ الصفيف يتطلب بعض الخطوات الخاصة، وخصوصًا إذا كانت تطبّق على خلايا متعددة وليست خلية مفردة.

لتحرير صيغة الصفيف في خلية مفردة، حدد الخلية، ثم اضغط على مفتاح F2 لتشغيل وضع التحرير، ثم عدّل الصيغة كما هو مطلوب، كأن يكون تقليص النطاق أو تمديده. على سبيل المثال، نريد تعديل صيغة مجموعة المبيعات لأنّنا قمنا بإضافة منتج ثامن إلى الجدول، لذلك سنحرر الصيغة ونحدد النطاقات B2:B9 وC2:C9 بدلا من B2:B8 وC2:C8، ثم نضغط على المفاتيح Ctrl+ Shift+ Enter:

أما إذا كانت صيغة الصفيف مطبقة على نطاق خلايا، كما فعلنا في إيجاد قيمة المبيعات لكل منتج على حدة، ففي هذه الحالة يجب أن تحدد نطاق الخلايا بأكمله، تضغط على F2، تجري التعديل المطلوب، ثم تضغط Ctrl+ Shift+ Enter:

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

فإذا رغبت في حذف خلية ضمن المصفوفة، يجب أن تقوم بحذف المصفوفة كلّها ثم إعادة كتابة صيغة الصفيف من جديد. والأمر نفسه ينطبق على نقل (قص ولصق) جزء من المصفوفة، حيث يظهر لك نفس التنبيه. لكن بإمكانك قص المصفوفة بأكملها ولصقها في مكان آخر. ولا تتغير مراجع الخلايا عند النقل بل ستبقى كما هي:

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

إذا كان لديك أي سؤال أو استفسار بخصوص صيغ الصفيف لا تتردد بطرحه في صندوق التعليقات.

السيناريوهات هي إحدى أدوات" تحليلات ماذا لو" What-if Analysis التي تُستخدم للتنبؤ بالتغييرات التي ستحدث على البيانات عند إجراء تغيير على قيم المدخلات. وقد تعلمنا في درس سابق كيفية استخدام أداتين من أدوات تحليلات "ماذا لو"، واليوم سنكمل الشرح ونغطي الأداة الثالثة؛ السيناريوهات Scenarios.

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

تتلخص طريقة إنشاء واستخدام السيناريو بالخطوات التالية:

1. عرض الصيغ في جدول البيانات، لمعرفة المتغيرات الداخلة في بناء الصيغة.
2. تحديد المتغيرات التي تؤثر على النتيجة التي نريد دراسة التغيير الذي سيحدث عليها.
3. إنشاء السيناريو.
4. عرض السيناريوهات ومقارنة النتائج.

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

أولا: نقوم بإظهار كل الصيغ في الجدول بالضغط على مفتاحي Ctrl+ ` (ستجد هذا الزر` بجانب الرقم 1، أي مع حرف الذال في لوحة المفاتيح العربية):

ثانيًا: نلاحظ أنّ صيغة المدخرات تعتمد على الخلية B3 التي تمثل الدخل، والخلية B4 التي تمثل مجموع المصاريف الشهرية. وبما أنّ الدخل الشهري ثابت تقريبًا، إذًا ستكون قيم المصاريف هي المؤثرة في الصيغة. أي سنقوم بتغيير قيم المتغيرات من B5 إلى B8.

ننقر على Ctrl+ ` من جديد لإخفاء الصيغ.

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

نحدد الخلايا التي ستتغير قيمها (B5:B8) ثم نذهب إلى تبويب بيانات Data ونختار Scenario Manager من قائمة What-if Analysis:

ننقر على Add من مربع الحوار لإضافة سيناريو جديد:

ندخل اسم مناسب للسيناريو في حقل Scenario name. وبما أننا قمنا بتحديد الخلايا التي ستتغير قيمها مسبقًا، سنترك الحقل Changing Cells كما هو. يمكن أيضًا كتابة تعليق أو ملاحظة لوصف السيناريو في حقل Comment. عند الانتهاء ننقر على OK:

سيظهر مربع الحوار Scenario values الذي من خلاله يمكننا تغيير قيم المتغيرات B5:B8. لكننا سنترك هذه الحقول كما هي لأننا نريد حفظ سيناريو للجدول الأصلي دون تغيير، ثم ننقر على OK:

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

ثالثًا: لإنشاء السيناريو الأول، ننقر على زر Add من جديد من مربع الحوار Scenario Manager ثم نقوم بتسميته، وليكن "تأجير شقة أصغر" مثلا ثم ننقر على OK:

بما أنّ السيناريو هو تأجير شقة أقل، فالقيمة التي ستتأثر في هذه الحالة هي الإيجار. لنفترض أنّ مبلغ الإيجار سيكون أقل نتيجة لصغر المساحة. لذلك سنقوم بإدخال القيمة 670 بدلا من 750، ونبقي بقية القيم كما هي، ثم ننقر على OK:

بعد إضافة السيناريو الجديد، نقوم بتحديده ثم ننقر على Show لعرض التغيّر في قيمة المدخرات عند حدوث هذا السيناريو:

عند مقارنة السيناريو الأصلي وسيناريو "تأجير شقة أصغر"، سنلاحظ زيادة في قيمة المدخرات من 2,700$ إلى 2,780$:

لكن، ماذا لو كانت المنطقة التي تم الانتقال إليها ذات تكاليف معيشة عالية؟ كيف يمكن أن يؤثر ذلك على قيمة المدخرات؟ لنقم بعمل سيناريو لمعرفة النتيجة.

نفتح مربع الحوار Scenario Manager وننقر على زر Add، ثم ندخل اسمًا مناسبًا للسيناريو الجديد، وليكن " تأجير شقة أصغر+ زيادة المصاريف" مثلا، ولا ننسى تحديد المتغيرات B5:B8 في حقل Changing Cells:

بعد ذلك نقوم بإدخال قيم المتغيرات في حقولها المخصصة. نبقى قيمة الإيجار 670$، ونقوم بإدخال قيمة المصاريف الأخرى: الطعام 400$ بدلًا من 850$، الخدمات 815$ بدلًا من 800$، والمصاريف الأخرى 1650$ بدلًا من 1400$:

بعد إضافة السيناريو، نقوم أولا بعرض السيناريو الأصلي بتحديده والنقر على زر Show لكي تكون المقارنة صحيحة، ثم نعرض سيناريو "تأجير شقة أصغر وزيادة المصاريف":

سنلاحظ عند عرض السيناريو الأخير انخفاض قيمة المدخرات إلى 2465$ بعد أن كانت 2700$ في السيناريو الأصلي. وهكذا يمكننا إنشاء أكثر من سيناريو واستخدامها للتنبؤ بما سيحدث على بياناتنا فيما لو طرأت عليها تغييرات في المستقبل.

كما يمكن عمل جدول ملخص للسيناريوهات لتسهيل عملية المقارنة. ولإنشاء الملخص، نفتح مربع الحوار Scenarios Manager ثم ننقر على زر Summary:

سيفتح مربع الحوار Scenario Summary، ومنه نقوم بتحديد الخلية/الخلايا التي نريد معرفة تأثير التغيّر عليها، وهي C9 في مثالنا:

بعد النقر على OK، سيتم إنشاء جدول السيناريوهات ونتائجها في ورقة مستقلة:

ويمكن دائمًا تحرير السيناريوهات وتعديل القيم من نفس مربع الحوار Scenario Manager، كما يمكن حذفها إن لم نعد بحاجة إليها:

غالبًا ما تشكّل الأرقام الجزء الأعظم والأهم من جداول بيانات اكسل. وهي تختلف بأنواعها، صيغها، وأطوالها. وعلى غرار الخلايا التي يمكن تنسيقها بعدة أنماط، يمكن تنسيق محتويات الخلية، سواء كانت نصوص أو أرقام.

ما سنغطيه في هذا المقال هو تنسيق الأرقام، ونقصد بالتنسيق اختيار الصيغة المناسبة لعرض الرقم بعد إدخاله. يوفّر اكسل مجموعة خيارات لصيغ الأرقام، كالعملات، الوقت، التاريخ، الأرقام عشرية، أو مجرّد أرقام اعتيادية.

يجب أن تأخذ في الاعتبار أن تغيير تنسيق الرقم سيؤثر على مظهر الرقم فقط، ولا يؤثّر على قيمة الرقم التي يخزّنها اكسل. على سبيل المثال؛ لنفرض أنّك قمت بإدخال الرقمين 0.6666667 و 0.3333333 في جدول بيانات اكسل وتريد جمعها بعملية الجمع البسيطة. لكن قبل ذلك قمت بتغيير صيغة الرقمين لإظهار مرتبة عشرية واحدة، أي بالشكل التالي: 0.6 و 0.3.

أي شخص يعرف بالعمليات الحسابية الأساسية سيقول أنّ نتيجة جمع 0.6 + 0.3 تساوي 0.9 وهذا صحيح. إلّا أنّ النتيجة التي سيرجعها اكسل ستكون 1 بدلًا من 0.9؛ أي أنّه قام بجمع 0.6666667 + 0.3333333 والسبب كما أسلفنا هو أنّ قيمة الرقم المخزونة في اكسل لا تتغيّر عند تغيير تنسيق الرقم، وإنّما يتغيّر مظهر الرقم فقط.

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

File > Options > Advanced

انتقل إلى قسم When Calculating this workbook ثم قم بتأشير مربّع الخيار Set precisions as displayed:

مع ذلك، يُنصح بترك هذا الخيار غير مؤشّر لأنّ سيؤثّر على دقة الأرقام، وهذا غير مفيد في الحسابات التي تتطلّب أرقامًا دقيقة جدًا والتي تكون فيها أعشار الرقم مهمة جدًا.

تنسيقات الأرقام المضمنة افتراضيا في اكسل

توجد قائمة تنسيقات الأرقام المضمّنة في اكسل في تبويب الصفحة الرئيسية Home وبالتحديد في خانة رقم Number:

يمكنك تطبيق التنسيق بتحديد الخلية التي تحتوي على الرقم ومن ثم اختيار التنسيق المناسب من القائمة:

من التنسيقات المضّمنة الشائعة التي تُستخدم في أغلب جداول البيانات:

عام General

هو التنسيق الافتراضي الذي يُستخدم تلقائيًا عند كتابة أي رقم. ويعني التنسيق العام أنّه لم يتم تطبيق أي تنسيق آخر على الرقم سواء كان من التنسيقات المضمّنة أو المخصصة:

الرقم Number

يُستخدم هذا التنسيق مع الأرقام بشكل عام. سيظهر الرقم عند اختيار هذا التنسيق بفواصل عشرية:

يمكنك تحديد المنازل العشرية المعروضة بعد الفاصلة بالنقر على زر زيادة/إنقاص المنازل العشرية:

يمكنك أيضًا التحكّم في عدد المنازل العشرية، وكذلك طريقة عرض الأرقام السالبة من خلال مربع الحوار تنسيق الخلايا Format Cells. انقر على مشغّل مربّع الحوار، حدد تبويب Number ثم اختر التنسيق المرغوب:

العملة Currency

يستخدم هذا التنسيق مع القيم النقدية العامة. حيث يقوم بإظهار رمز العملة الافتراضي بجانب الرقم. وبإمكانك التحكّم في عدد المنازل العشرية للرقم، كيفية عرض القيم السالبة، وكذلك رمز العملة من خلال مربع الحوار Format Cells وكما ذكرنا أعلاه:

محاسبة Accounting

يستخدم هذا التنسيق أيضًا مع القيم النقدية، لكنّه يقوم بمحاذاة رمز العملة بشكل منفصل عن الرقم داخل الخلية:

بإمكانك تغيير رمز العملة، أو نوعها، بواسطة زر Accounting Number Format الذي يعرض مجموعة من الخيارات البديلة. ويمكن الوصول إلى المزيد من خيارات رموز العملات بالنقر على More Accounting Formats:

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

التاريخ Date

يوفّر اكسل عدة خيارات مفيدة لتنسيق الأرقام بصيغة تواريخ. وعندما تقوم بإدخال تاريخ معيّن باستخدام أي نوع من الفواصل، سيقوم اكسل بتحويله تلقائيًا إلى صيغة تاريخ، ويختار نوع الفاصل الأنسب. على سبيل المثال، إذا قمنا بإدخال التاريخ 22-03-2016 في الخلية، سيتم تطبيق تنسيق التاريخ تلقائيًا، كما يغيّر مظهر التاريخ إلى الصيغة الموضّحة أدناه:

أي أنّه استخدم الفاصلة المائلة بدلًا من التي قمنا بإدخالها.

توجد العديد من صيغ التاريخ التي يمكنك الاختيار من بينها، ويمكنك الوصول إليها من مربع الحوار Format Cells:

بعض هذه الصيغ تتغيّر بتغيّر إعدادات الوقت والتاريخ للمنطقة التي تم تحديدها لنظام تشغيل جهازك. هناك خيارات أيضًا لعرض التاريخ بصيغة طويلة، أي بكتابة اسم الشهر بدلًا من رقمه. اختر الصيغة المرغوبة ثم انقر على OK.

الوقت Time

الوقت، حاله حال التاريخ، يمكن أن يسبب لك الالتباس عند إدخاله. فقد يختلف ما تقوم بإدخاله عما يظهر في الخلية. والسبب هو أيضًا في تنوّع تنسيقات الوقت المضمّنة في اكسل. حيث يقوم البرنامج بالتعرّف على صيغة الوقت وعرضها بتنسيق معيّن قد يكون مختلفًا عمّا قصدته.

على سبيل المثال، إذا أردت إدخال 4 دقائق و 56 ثانية، يمكنك كتابتها بالشكل 4:56 داخل الخلية. لكن ستلاحظ، من خلال شريطة الصيغة، أنّ اكسل يقوم بتخزين الوقت بالشكل 04:56:00 ص. كما يتم تحديد التنسيق Custom:

وعندما تذهب إلى مربّع الحوار Format Cells، ستجد أنّ الوقت قد تم عرضه بصيغة hh:mm (أي ساعة: دقيقة)، وتم اعتبار الرقم 4 هو ما يمثّل الساعات والرقم 56 هو ما يمثّل الدقائق:

وستلاحظ أيضًا وجود العديد من التنسيقات المتنوّعة للوقت. فإذا كنت ترغب في عرض الدقائق: الثواني اختر التنسيق mm:ss. من جديد سيقوم اكسل بعرض وقت مخالف للوقت الذي قمت بإدخاله:

لذلك يجب أن تكون أكثر دقة في إدخال الوقت. وفي مثالنا هذا يجب كتابة الوقت بالشكل 00:04:56، لكي نكون أكثر تحديدًا في بيان رقم الساعة، الدقيقة، والثانية.

بعد أن تتأكد من إدخال الوقت بصورة صحيحة، يمكنك تغيير التنسيق واختيار صيغة أخرى لعرض الوقت مثل hh:mm AM/PM لإظهار ص (صباحًا) أو م (مساءً) بجانب الوقت، أو h:mm:ss إذا كنت تريد جمع الوقت وطرحه، حيث تكون هذه الصيغة مفيدة في هذه الحالة على وجه الخصوص.

نسبة مئوية Percentage

يُستخدم هذا التنسيق لعرض محتوى الخلية كنسبة مئوية، أي يقوم بضرب الرقم الموجود في الخلية × 100 ويضيف رمز النسبة المئوية. يمكنك تطبيق هذا التنسيق إما بالنقر على أمر النسبة المئوية في خانة Number، أو استخدام مفتاح الاختصار Ctrl+Shift+% أو من خلال قائمة التنسيقات. ويمكن التحكّم في عدد المنازل العشرية بواسطة مربع الحوار Format Cells:

كسور Fraction

يُستخدم هذا التنسيق لعرض الأرقام بشكل كسور صحيحة، وحسب نوع الكسر الذي تختاره (من مربع الحوار Format Cells):

علمي Scientific

في هذا التنسيق، يتم عرض الأرقام باستخدام الرمز الأسي. حيث يتم ضرب الرقم بالأساس 10 ويرفع لأس محدد حسب طول الرقم. على سبيل المثال، عند كتابة الرقم 255 وتطبيق هذا التنسيق سيتم عرض الرقم بالشكل التالي:

نص Text

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

تنسيقات خاصة Special

هناك بعض التنسيقات الخاصة المضمّنة في اكسل مثل تنسيق الرموز البريدية، أرقام الهواتف، أو أرقام الحماية الاجتماعية. يمكنك الوصول إلى هذه الخيارات من مربع الحوار Format Cells:

مثلا، عند تطبيق تنسيق رقم الهاتف Phone Number على الرقم 1112224445، ستتم إضافة فواصل وسطية صغيرة، وسيظهر بصيغة رقم هاتف. لكن يبقى الرقم في حقل الصيغة كما تم إدخاله، وهذا مثال آخر على تغيير مظهر الرقم وليس قيمته عند تغيير التنسيق:

اقرأ أيضًا

• دليلك الشامل إلى أنواع البيانات

يُتيح Microsoft Excel إمكانية ربط البيانات من مصنفات متفرّقة عن طريق إنشاء مرجع خارجي External Reference (ويُدعى أيضًا بالرابط Link). يكون هذا الرابط فعّالا على الدوام بحيث يتم تحديث المصنّف التابع تلقائيًا كلّما قمت بإجراء تعديل على مصنّفات المصدر. وهذا ما يميّز ربط المصنّفات عن دمج المصنّفات المتعددة الذي كنا قد تحدّثنا عنه في درس سابق. ففي الأخير، لا يتم تحديث البيانات ما لم يتم إعادة الدمج.

كيفية إنشاء مرجع خارجي (رابط)

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

الجدول الثاني لا يختلف عن الأول، فهو يحتوي على نفس نوع البيانات لكن لاختبارات الفصل الثاني:

المطلوب هو إيجاد متوسّط الدرجات للفصلين في مصنّف ثالث منفصل، مع إنشاء رابط للحفاظ على البيانات محدّثة:

أولًا، نحدد الخلية التي سنجد فيها متوسّط درجات الفصلين للطالب الأول (B5 في هذا المثال) ثم نكتب الصيغة. نبدأ بعلامة = ثم الدالة Average:

بعد ذلك نذهب إلى مصنّف الفصل الأول ونحدد متوسّط درجات الطالب الأول، نضع فاصلة منقوطة (;) ونذهب إلى مصنّف الفصل الثاني لنحدد متوسط درجات الطالب الأول:

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

بعد كتابة الصيغة نضغط على مفتاح Enter للحصول على النتيجة.

وبدل أن نكرر نفس الخطوة أعلاه لإيجاد المتوسط السنوي لجميع الطلّاب، سنستخدم التعبئة التلقائية لنسخ الصيغة إلى بقية خلايا العمود:

لكن كما نلاحظ، تم تكرار نفس المتوسط السنوي (72) في جميع الخلايا، والسبب هو أنّ اكسل اتخذ الخلية E5 في مصنّف الفصل الأول والخلية E5 في مصنّف الفصل الثاني كمرجع مطلق. أي أنّه قام بنسخ الصيغة إلى بقية الخلايا لكن باستخدام نفس قيمة متوسط الفصل الأول والثاني للطالب الأول. لتصحيح ذلك، نتراجع عن أمر التعبئة التلقائية (Ctrl +Z) ثم نعدّل الصيغة بحذف علامة الدولار ($) المرافقة لاسم الخلية لتحويل المرجع المطلق إلى نسبي:

بعد ذلك نعمل تعبئة تلقائية لملء بقية الخلايا:

عندما تقوم بتغيير أي جزء من البيانات في مصنّفات المصدر (وهي ما تزال مفتوحة)، سيتم تحديث المصنّف التابع ليعكس هذه التحديثات تلقائيًا.

أمّا إذا قمت بإغلاق جميع المصنّفات، ثم قمت لاحقًا بتعديل البيانات في أحد مصنّفات المصدر، أو كليهما، ستظهر رسالة تحذيرية عند فتح المصنّف التابع تخبرك بأنّ التحديث التلقائي غير مفعّل:

يمكنك تفعيل التحديث التلقائي بالنقر على Enable Content إذا كانت التغييرات قد تم إجراؤها من قبلك أو من قبل أحد زملائك، وتريد أن ترى التحديث على المصنّف التابع.

ملاحظة: عند غلق مصنّفات المصدر ستجد أنّ الصيغة في شريط الصيغة قد تغيّرت وأصبحت تشتمل أيضًا على امتداد المجلد الذي تُحفظ فيه مصنّفات المصدر:

بإمكانك تفعيل خيار التحديث التلقائي للبيانات المرتبطة بشكل دائم بالذهاب إلى File> Options> Trust Center> Trust Center Settings:

قم بتأشير الخيار Enable all Data Connections:

مع ذلك، لا يُنصح بتفعيل هذا الخيار ويفضّل إبقاؤه كما هو لأغراض الأمان. إلّا في حال كان مصدر التغييرات موثوقًا دائمًا وكنت متأكدًا من أنّها غير ضارّة ببياناتك. في النهاية، الأمر راجع إلى تفضيلك الشخصي.

تحديث الروابط وتعطيلها

عندما تقوم بتغيير اسم مصنّفات المصدر أو تغيير المجلدات التي تُحفظ فيها، لن يكون بالإمكان تحديث البيانات في المصنّف التابع تلقائيًا، لأنّ الرابط لم يعد صالحًا، وأصبح اسم أو امتداد مصنّف المصدر مختلفًا عن الاسم والامتداد الموجود في الصيغة. لذلك يجب أن تقوم بإرشاد اكسل من جديد إلى مصنّفات المصدر لتحديث البيانات.

على سبيل المثال، قمنا بتغيير اسم مصنّف "درجات الفصل الأول" إلى "درجات الفصل 1"، لذلك يجب أن نقوم بتحديث الروابط.

نفتح المصنّف التابع، نذهب إلى تبويب File ثم ننقر على زر Edit Links to Files:

ننقر على Change Source من مربّع الحوار Edit Links ثم نحدد المصنّف الذي قمنا بإعادة تسميته:

من نفس مربّع الحوار يمكنك تعطيل الروابط بتحديدها الرابط ثم النقر على Break Link، وهذا في حال لم تعد بحاجة إلى وجود ارتباط بين المصنّفات:

ستلاحظ تحوّل البيانات في المصنّف إلى مجرّد بيانات عادية واختفاء الصيغة من شريط الصيغة:

لقد غطينا في دروس سابقة كيفية تلخيص البيانات باستخدام المجاميع الفرعية، والجداول المحورية. وسنتعلّم اليوم كيفية تلخيص البيانات باستخدام خاصية الدمج.

تُتيح خاصية Consolidate إمكانية دمج وتلخيص البيانات من عدة أوراق عمل أو مصنفات في ورقة عمل واحدة أو مصنّف واحد، وهي مفيدة في الكثير من الحالات. على سبيل المثال إذا كان لديك متجر وكانت بيانات المبيعات الخاصة بكل ربع محفوظة في مصنّفات منفصلة. عندما يحين وقت إعداد التقرير السنوي ستكون خاصية الدمج مناسبة لجمع البيانات من المصنفات المتعددة وتلخيصها في مصنف واحد.

يمكن دمج البيانات بطريقتين رئيسيتين:

• حسب الموضع
• حسب الفئة

وسنشرح كيفية الدمج بالطريقتين في هذا الدرس.

دمج البيانات حسب الموضع

تُستخدم هذه الطريقة إذا كانت البيانات الموزّعة على مصنفات متعددة (أو أوراق متعددة) لها نفس الترتيب ونفس أسماء الأعمدة والصفوف في جميع المصنفات أو أوراق العمل.

مثال: لدينا هنا 4 مصنفات منفصلة، ويحتوي كل مصنف على المبيعات الخاصة بكل ربع. وكما نلاحظ، البيانات في جميع المصنفات لها نفس الترتيب ونفس عناوين الأعمدة والصفوف. سنقوم باستخدام خاصية الدمج لإيجاد مجموع المبيعات السنوي في مصنّف منفصل (لاحظ أنّ جميع الجداول تبدأ بالخلية C5 وتنتهي بالخلية F11):

أولًا: نفتح جميع المصنفات الأربعة بالإضافة إلى مصنف خامس جديد (نسميّه الملخص) لإيجاد مجموع المبيعات السنوي. في المصنف الجديد، نقوم بإنشاء جدول بنفس البنية، يحتوي على نفس عدد الصفوف والأعمدة ونفس عناوينها.

لنفترض أنّ المنتجات وأسعارها بقيت ثابتة طوال العام. نقوم بإدخالها في جدول الملخّص، ونترك عمودي "عدد الوحدات المباعة" و"المبيعات" فارغة:

ما سنقوم به هو جمع عدد الوحدات المباعة للربع الأول + الثاني+ الثالث+ الرابع، لكل منتج ومن ثم نجد على ضوئها المبيعات لكل منتج، ثم مجموع المبيعات الكلي.

ثانيًا: نحدد نطاق الخلايا الذي نريد عرض البيانات المدمجة فيه، وهو عمود "عدد الوحدات المباعة" في مصنّف الملخّص (E6:E11). من جديد أكرر، يجب أن تكون البيانات التي نريد دمجها موجودة في نفس نطاق الخلايا في مصنفات المصدر:

ثالثًا: نذهب إلى تبويب بيانات Data وننقر على أمر دمج Consolidate:

من مربّع الحوار Consolidate، نحدد الدالة التي نريد استخدامها. هناك العديد من الخيارات، لكن ما يهمنا هنا هو دالة الجمع SUM لأننا نريد إيجاد مجموع "عدد الوحدات المباعة" في كل الأرباع:

بعد ذلك، ننقر على زر السهم الصغير بجانب حقل مرجع Reference ثم نذهب إلى المصنف الأول (مبيعات الربع الأول) ونحدد نطاق الخلايا E6:E11 فيه:

لاحظ الصيغة في حقل المرجع؛ يشير اكسل أولًا إلى اسم المصنّف، ثم اسم ورقة العمل، ثم نطاق الخلايا.

انقر على زر Add لإضافة البيانات من المصنّف الثاني (مبيعات الربع الثاني)، وكرر نفس الخطوات على المصنفين الآخرين، وكما موضّح في الصورة أدناه:

نتأكّد من تأشير مرّبع الخيار Create links to source data لإنشاء ارتباط مع مصدر البيانات، وهذا لأنّ بياناتنا موجودة على مصنفات مختلفة. أي لا حاجة لتأشير الخيار إذا كنت تريد دمج البيانات من أوراق عمل مختلفة في ورقة على نفس المصنّف.

ننقر على OK، وسيقوم البرنامج بجمع كافة بيانات "عدد الوحدات المباعة" عبر جميع المصنفات. وإذا أردنا معرفة البيانات التفصيلية لكل رقم دون الرجوع إلى كل مصنّف، يوفّر لنا اكسل على مخطط تفصيلي على جانب المصنّف يمكننا استخدامه لإظهار (+) أو إخفاء البيانات (-) التي تم دمجها لنحصل على الرقم النهائي في كل خلية. وعند النقر على الرقم سيظهر في شريط الصيغة امتداد يمكننا الاستدلال به إلى اسم المصنف المصدر واسم ورقة العمل:

دمج البيانات حسب الفئة

تُستخدم هذه الطريقة إذا كانت البيانات التي تريد دمجها غير مرتبة بنفس الترتيب عبر جميع المصنفات أو أوراق العمل كأن يكون عدد عناصر الجدول أو ترتيبها مختلفًا. مع ذلك يجب أن يكون لها نفس أسماء الصفوف والأعمدة.

مثال: لدينا هذه المرة 4 أوراق عمل (ويمكن أن تكون مصنفات أيضًا) تحتوي على 4 جداول. لكن كل جدول يحتوي على عدد/ترتيب مختلف من الفئات (المنتجات)، وقد توجد نفس الفئة مكررة في أكثر من جدول مع اختلاف بياناتها. ما نريده هو إيجاد مجموع المبيعات لكل منتج (أي لكل فئة) في ورقة عمل تمثّل الملخص.

أولًا: نقوم بإنشاء ورقة عمل جديدة، ثم ننشئ جدول يبدأ من نفس الصف ونفس العمود لبقية الجداول في أوراق العمل المصدر (أي الخلية C5 في مثالنا):

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

ثالثًا: نذهب إلى تبويب بيانات Data وننقر على أمر دمج Consolidate. سيظهر لنا نفس مربع الحوار الذي ظهر في الطريقة السابقة. نحدد الدالة التي نريد استخدامها من حقل Function ثم ننقر على زر السهم الصغير في حقل Reference:

نذهب إلى الورقة الأولى ونحدد نطاق البيانات الأول (C6:F11 في مثالنا)، ثم الورقة الثانية ونحدد نطاق البيانات الثاني (C6:F11 في مثالنا) وهكذا:

بعد ذلك نحدد أين توجد الفئات التي نريد دمجها سواء كانت في الصف العلوي Top Raw أو في العمود الأيمن Right Columnأو في كليهما. سنحدد العمود الأيمن لأنّ المنتجات تقع في العمود الأيمن في هذا المثال ثم ننقر على OK:

سيتم إيجاد مجموع المبيعات لكل منتج في كل الأرباع حتّى إن كانت مواقعها مختلفة عبر أوراق العمل.

دالتا VLOOKUP و HLOOKUP هي إحدى دوال البحث والإشارة Lookup and Reference وهي تُستخدم للبحث عن قيمة محددة في صف أو عمود، ومن ثم إرجاع قيمة معيّنة في الموضع نفسه من الصف أو العمود الثاني. سنتعرّف في هذا الدرس على هاتين الدالتين وكيفية استخدامهما.

VLOOKUP

اسم هذه الدالة هو اختصار لـ "Vertical Lookup" (البحث العمودي) وهي تُستخدم للبحث في عمود محدد عن قيمة محددة حسب الصفوف، وعند العثور على تلك القيمة تقوم بإرجاع ما يقابلها في العمود الثاني (الذي نقوم بتحديده في الصيغة أيضًا). يمكنك تشبيه عمل هذه الدالة بدليل الهاتف، حيث تقوم بالبحث عن رقم شخص محدد بالبحث عن اسمه أولا في عمود الأسماء، وعندما تجد الاسم المطلوب تعثر على الرقم الذي يقابله في العمود الثاني (عمود الأرقام).

البناء العام لصيغة الدالة

VLOOKUP (lookup_value; table_array; col_index_num; [range_lookup])

• lookup_value: القيمة التي تريد البحث عنها. يجب أن تكون هذه القيمة موجودة في العمود الأول لنطاق الخلايا الذي نحدده في table_array. هذا المُعطى argument وجوده ضروري في الصيغة.
• table_array: ويمثل نطاق الخلايا الذي يحتوي البيانات التي تبحث فيها الدالة. وهذا المعطى وجوده ضروري أيضًا.
• col_index_num: رقم العمود في نطاق الخلايا table_array الذي يحتوي على القيمة التي سيتم إرجاعها. وجوده مطلوب في الدالة.
• range_lookup: هذا المعطى هو عبارة عن قيمة منطقية logical تحدد من خلالها فيما إذا كنت تريد من دالة VLOOKUP البحث عن تطابق تام (بإدخال القيمة FALSE) أو تطابق تقريبي (بإدخال القيمة TRUE) مع قيمة lookup_value، ووجوده اختياري في الصيغة.

ملاحظة: عند استخدام القيمة المنطقية TRUE يجب أن يكون العمود الأول في نطاق الخلايا table_array مرتبا تصاعديا لكي يتم إرجاع قيمة صحيحة.

مثال 1

الجدول أدناه يحتوي على بيانات الموظفين لشركة ما. عدد صفوف الجدول هو 100 صف تقريبا. في مثل هذه الجداول الكبيرة (وما يفوقها) قد يكون من المضيعة للوقت البحث في كل صف من صفوف الجدول إذا رغبنا في العثور على معلومة معينة. وسيكون الأمر أسهل وأسرع بكثير باستخدام دالة VLOOKUP. المطلوب هنا هو العثور على قسم ومقدار راتب الموظف صاحب البطاقة رقم 100، والموظف صاحب البطاقة رقم 65.

نضع المؤشر في الخلية التي نريد إرجاع القيمة الأولى فيها، وهي الخلية H2 في هذا المثال، ثم نذهب إلى تبويب:

صيغ Formulas > بحث وإشارة Lookup & Reference > VLOOKUP

في مربع الحوار Function Arguments نقوم بتحديد معطيات الدالة:

• في الحقل الأول، lookup_value، نحدد القيمة التي نريد البحث عنها، وهي رقم البطاقة "100" في هذا المثال، لذلك سنحدد الخلية G2 التي تحتوي هذه القيمة.
• في الحقل الثاني، table_array، نحدد نطاق الخلايا الذي يحتوي بياناتنا. في هذا المثال سنحدد الجدول بأكمله (A1:D100).
• في الحقل الثالث، col_index_num، ندخل رقم العمود الذي يحتوي على القيمة التي سيتم إرجاعها، وبما إننا نريد العثور على "قسم" الموظف، فسنقوم بإدخال الرقم "3"، وهو تسلسل عمود "القسم" في الجدول من اليمين إلى اليسار (أي أنّ تسلسل عمود "رقم بطاقة الموظف هو"1"، عمود "تاريخ التوظيف" هو "2"، وهكذا). إذا كان اتجاه الورقة من اليسار إلى اليمين نقوم بحساب رقم العمود من اليسار إلى اليمين.
• في الحقل الرابع، range_lookup، نقوم بإدخال الرقم صفر (والذي يعني القيمة المنطقية FALSE) لأننا نبحث عن تطابق تام مع الرقم "100"، وليس تطابق تقريبي، بسبب وجود أرقام قريبة من رقم "100" في عمود "رقم بطاقة الموظف".

ننقر على OK، وسيتم العثور على قسم صاحب البطاقة رقم 100 فورا، وهو قسم "الخدمات":

تفسير عمل الصيغة

تقوم الصيغة أولا بالبحث في صفوف عمود "رقم بطاقة الموظف" واحدا تلو الآخر، وعندما تعثر على الرقم "100" تقوم بإيجاد القيمة التي تقابلها في عمود "القسم" ثم ترجع هذه القيمة في الخلية H2.

نكرر نفس الخطوات للعثور على مقدار راتب الموظف صاحب البطاقة رقم "100"، وستكون المعطيات كما موضّح في الصورة التالية:

قمنا بإدخال الرقم "4" في الحقل الثالث لأنّ تسلسل عمود "الراتب" في الجدول هو الرابع. وكما في الخطوة السابقة، يتم إيجاد النتيجة في الخلية G2:

الخطوات نفسها تنطبق على رقم البطاقة "65".

مثال 2

الجدول أدناه يحتوي على درجات اختبارين لمجموعة من الطلاب، مع متوسط الدرجات. المطلوب هو إيجاد تقدير كل طالب باستخدام دالة VLOOKUP:

بالتأكيد يمكننا إدخال التقديرات يدويا لكل طالب اعتمادًا على قيمة المتوسط، لكن كما ذكرنا، تسّهل دالة VLOOKUP العمل وتختصر الكثير من الوقت.

ملاحظة: قمنا بترتيب القيم في عمود "الدرجة" (وهو العمود الأول من نطاق الخلايا table_array) تصاعديا لأننا سنستخدم القيمة المنطقية TRUE في حقل range_lookup.

نحدد الخلية التي نريد إرجاع النتيجة فيها، وهي الخلية E2 في هذا المثال، ثم نذهب إلى تبويب:

صيغ Formulas > بحث وإشارة Lookup & Reference < VLOOKUP

• في الحقل الأول نحدد القيمة التي نريد البحث عن التقدير الذي يقابلها، وهي قيمة المتوسط للطالب الأول، 82، في هذا المثال (أي الخلية D2).
• في الحقل الثاني نحدد نطاق الخلايا الذي سيتم البحث عن التقدير المناسب فيه، وهو النطاق H1:I7.
• في الحقل الثالث نحدد رقم العمود الذي يحتوي على القيمة التي سيتم إرجاعها. وبما أنّ تسلسل عمود "التقدير" هو الثاني في نطاق الخلايا، سندخل الرقم "2".
• في الحقل الرابع ندخل الرقم 1 (والذي يعني القيمة المنطقية TRUE) أو نتركه فارغًا، وفي الحالتين سيتم إرجاع نفس النتيجة. السبب في أنّنا اخترنا القيمة TRUE هو أننا نبحث عن تطابق تقريبي مع القيم في عمود "الدرجة" وليس تطابق تام.

ننقر على OK وسيتم إرجاع التقدير الذي يقابل قيم المتوسط:

تفسير عمل الصيغة

ستقوم الصيغة بالبحث عن القيمة "82" أو أقرب أقل قيمة إليها، وعند إيجادها، تقوم بإيجاد ما يقابلها في عمود "التقدير"، ومن ثم إرجاع النتيجة في الخلية E2.

نستخدم التعبئة التلقائية لنسخ الصيغة إلى بقية خلايا عمود "التقدير":

بعد نسخ الصيغة، نلاحظ حدوث خطأ في إيجاد القيم للخلايا من E7 إلى E23، والسبب هو أنّ البرنامج استخدم نطاق الخلايا H1:I7 كمرجع نسبي relative reference. لذلك سنعيد إدراج الصيغة ونستخدم نطاق الخلايا المذكور كمرجع مطلق absolute reference بوضع مؤشر الكتابة فوق اسم الخلية والنقر على مفتاح F4 (أو بكتابة علامة الدولار قبل وبعد حرف العمود يدويا من لوحة المفاتيح)

هذه المرة سيتم إيجاد قيم صحيحة في عمود "التقدير" بأكمله عند استخدام التعبئة التلقائية:

أو بدلا من استخدام نطاق الخلايا (H1:I7) كمرجع مطلق وتحديده يدويا كلما أردنا استخدام الدالة، نقوم بتسميته باسم مخصص. وبذلك نستطيع استخدام اسم النطاق في الصيغة. نحدد نطاق الخلايا، ندخل الاسم المرغوب في حقل الاسم (قمنا بتسميته بـ "التقدير")، ثم نضغط Enter:

في المرة المقبلة، عندما نقوم بإدراج دالة VLOOKUP (أو أيّة دالة أخرى يُستخدم فيها ذلك النطاق)، نقوم بإدخال اسمه بدلًا من تحديده على الورقة:

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

HLOOKUP

تعمل دالة HLOOKUP (اختصارا لـ Horizontal Lookup) بالضبط كما تعمل دالة VLOOKUP فيما عدا أنّها تقوم بالبحث عن القيم أفقيا وليس عموديا (أي تبحث عن قيمة محددة في صف محدد حسب أعمدة ذلك الصف).

البناء العام لصيغة الدالة

HLOOKUP(lookup_value; table_array; row_index_num; [range_lookup])

• lookup_value: هي القيمة التي تريد البحث عنها. يجب أن تكون هذه القيمة موجودة في الصف الأول لنطاق الخلايا الذي سنحدده في حقل table_array. وهذا المعطى مطلوب في الصيغة.
• table_array: وهو نطاق الخلايا الذي يحتوي البيانات التي تبحث فيها الدالة. وهذا المعطى مطلوب في الصيغة أيضًا.
• row_index_num: رقم الصف في نطاق الخلايا table_array الذي يحتوي على القيمة التي سيتم إرجاعها. وجوده مطلوب في الصيغة.
• range_lookup: هذا المعطى هو عبارة عن قيمة منطقية logical تحدد فيما إذا كنت تريد من دالة HLOOKUP البحث عن تطابق تام (بإدخال القيمة FALSE) أو تطابق تقريبي (بإدخال القيمة TRUE) مع قيمة lookup_value، ووجوده اختياري في الصيغة.

ملاحظة: عند استخدام القيمة المنطقية TRUE يجب أن يكون الصف الأول في نطاق الخلايا table_array مرتبا تصاعديا لكي يتم إرجاع قيمة صحيحة.

مثال

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

أولا، نحدد الخلية التي نريد إرجاع النتيجة فيها، وهي الخلية B9 في هذا المثال، ثم نذهب إلى تبويب:

صيغ Formulas > بحث وإشارة Lookup & Reference < HLOOKUP

في مربع الحوار Function Arguments نقوم بتحديد المعطيات:

• في الحقل الأول، lookup_value، نحدد القيم التي نريد البحث عنها، وهو رقم المشروع "5" في هذا المثال. لذلك سنحدد الخلية B8 التي قمنا بإدخال هذا الرقم فيها مسبقا.
• في الحقل الثاني، table_array، نحدد نطاق الخلايا الذي سيتم البحث فيه، وهو A1:I13 في هذا المثال.
• في الحقل الثالث، row_index_num نقوم بإدخال رقم تسلسل الصف الذي يحتوي على النتيجة التي سيتم إرجاعها. وفي هذا المثال هو صف "تاريخ الانتهاء"، تسلسله الثالث (3).
• في الحقل الرابع، lookup_range نقوم بإدخال الرقم صفر (أي القيمة المنطقية FALSE) لأننا نريد البحث عن قيمة مطابقة تماما للقيمة في حقل lookup_value:

ننقر على OK وسيتم إرجاع تاريخ الانتهاء للمشروع رقم 5:

تفسير عمل الصيغة

ستقوم الصيغة بالبحث في صف "المشروع" عن الرقم 5، وعند العثور عليه ستقوم بإيجاد القيمة المقابلة لهذا الرقم في الصف المحدد في حقل row_index_num، وهو الصف الثالث في مثالنا، ومن ثم إرجاع النتيجة في الخلية B9.

يجب أن تأخذ في الاعتبار أنّه إذا كانت قيمة lookup_range تساوي TRUE، ولم تتمكن الدالة من العثور على قيمة lookup_value فستقوم بإرجاع أقرب أقل قيمة من قيمة lookup_value.

مثال 2

إذا كنا نريد معرفة تاريخ البداية للمشروع رقم "9" (والذي هو في الحقيقة غير موجود في الجدول) ستكون الصيغة كالتالي:

وستكون النتيجة كالتالي:

تفسير عمل الصيغة

قامت الصيغة بالبحث عن الرقم "9" في صف "المشروع"، وقد تعذّر عليها إيجاده، لذلك قامت بإرجاع "تاريخ البدء" للمشروع رقم "8" وهو أقرب أقل قيمة من الرقم "9". وهذا بسبب أننا قمنا بإدخال القيمة المنطقية TRUE (أي البحث عن تطابق تقريبي).

أما إذا قمنا بتحديد القيمة المنطقية FALSE، فستكون النتيجة خطأ #N/A، لأنّ الدالة في هذه الحالة ستبحث عن تطابق تام:

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

كيفية إنشاء الحقول المحسوبة Calculated Fields

في بعض الأحيان تحتاج إلى إضافة معلومات أخرى إلى تقرير PivotTable ولا تريد تغيير مصدر البيانات الأصلي، أو قد يكون إدخالها في الجدول الأصلي يستغرق الكثير من الوقت. في هذه الحالة يمكنك استخدام الحقول المحسوبة Calculated Fields.

في المثال أدناه، نريد إضافة حقل تُحسب فيه قيم العمولة لنسبة عمولة تساوي 11%:

نحدد خلية داخل الجدول ليظهر التبويبان السياقيان. من تبويب تحليل Analyze ننقر على:

 Fields, Items, &Sets > Calculated Field

في مربع الحوار "إدراج حقل محسوب"، نقوم بإدخال اسم للحقل الجديد من خانة Name. ومن خانة Formula نقوم بكتابة صيغة الحقل. بما أننا نريد حساب العمولة التي تنتج من ضرب قيم المبيعات × نسبة العمولة، ستكون الصيغة بالشكل التالي:

=المبيعات * %11

يمكننا كتابة كلمة "المبيعات" يدويا في الصيغة أو النقر عليها بشكل مزدوج من مربع Fields. بعد الانتهاء من كتابة الصيغة ننقر على موافق OK.

سنلاحظ إضافة حقل "العمولة Sum of " إلى قائمة الحقول وإدراجه في منطقة القيم، وكذلك ظهور قيم مجموع العمولة لكل شهر لكل "صنف" في الجدول:

بإمكاننا تغيير عنوان العمود من "العمولة Sum of" إلى "مجموع العمولة" بتحديد الخلية التي تحتوي على العنوان ثم إدخال الاسم الجديد في خانة الحقل النشط Active Field:

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

 Analyze > Fields, Items, &Sets > Calculated Field

من خانة Name نحدد الحقل الذي نرغب في حذفه (وهو حقل "العمولة" في هذا المثال)، ننقر على حذف Delete ثم موافق OK:

كيفية استخدام مقسمات طريقة العرض Slicers لتصفية البيانات

مقسّمات العرض Slicers هي بمثابة لوحة تحكم بصرية بعوامل التصفية Filters، وهي تختلف عن التصفية التقليدية بكونها أسهل وأسرع للاستخدام.

مثلا، في الجدول أدناه، يمكننا استخدام الحقل في منطقة المرشحات ("الربع") لتصفية الجدول المحوري. فإذا أردنا عرض مبيعات الربع الأول من كل سنة فقط، نحدد "الربع الأول" ثم ننقر على موافق OK:

لكن إذا أردنا التصفية حسب السنوات، أو الأشهر، دون إضافة هذه الحقول إلى منطقة المرشحات (أو إلى PivotTable)، يمكننا استخدام مقسّمات العرض.

من تبويب تحليل Analyze ننقر على إدراج مقسّم طريقة العرض Insert Slicer. في مربع الحوار الذي سيظهر نحدد الحقول التي نريد إدراجها كمرشحات. سنقوم بإدراج "شهر الطلب" و"السنة" مثلا (يمكن إدراج واحد فقط أو أكثر):

بعد إدراج المقسّمات، يمكننا تغيير مكانها بسحبها إلى الموضع المرغوب، أو تغيير حجما باستخدام المقابض عند حدودها:

نلاحظ أن كل مقسّم يحتوي على عنوان، وهو نفسه عنوان الحقل، بالإضافة عدد من الأزرار التي تمثل عناصر ذلك الحقل. لعمل تصفية، ننقر على الأزرار داخل كل مقسّم. فلو أردنا عرض المبيعات لشهر نيسان من عام 2011، ننقر على زر "نيسان" من مقسّم "شهر الطلب":

وبإمكاننا عمل تصفية متعددة (أي عرض القيم لأكثر من عنصر في المقسّم) بالضغط على مفتاح Ctrl أثناء النقر على مجموعة من الأزرار في المقسّم. ففي المثال أدناه، قمنا بعرض مجموع المبيعات لشهر "نيسان"، "حزيران" و"أيلول" من عام 2011:

لإلغاء عملية التصفية، ننقر على زر إزالة التصفية في حافة المقسّم (أو نضغط مفتاحي Alt+C من لوحة المفاتيح).

هناك العديد من الخيارات الخاصة بتنسيق المقسّم يمكننا الوصول إليها من تبويب خيارات Options الذي يظهر عند تحديد أي مقسّم في الورقة.

يمكننا تغيير لون المقسّم باختيار أحد الأنماط Styles من معرض الأنماط:

أو تغيير عند الأعمدة في المقسّم من خانة الأزرار Buttons وبالتحديد حقل الأعمدة Columns. كما يمكننا تغيير أبعاد الأزرار من نفس الخانة:

يمكننا أيضا تغيير اسم المقسّم من مربع Slicer Caption في خانة Slicer:

وللوصول إلى الخيارات الإضافية للمقسّم ننقر على خيارات مقسّم طريقة العرض Slicer Setting من خانة Slicer:

من مربّع حوار "الإعدادات"، يمكننا تغيير عنوان المقسّم أيضا، أو عدم عرضه بإلغاء تأشير الخيار Display header. وبالإمكان تغيير طريقة فرز وتصفية عناصر المقسّم بتأشير الخيار تصاعديا Ascending أو تنازليا Descending. فإذا كانت العناصر نصية سيتم فرزها من الألف إلى الياء (أو بالعكس)، وإذا كانت العناصر رقمية سيتم فرزها من الأصغر إلى الأكبر (أو بالعكس).

كما يوجد خيار استخدام قائمة مخصصة عند فرز العناصر Use Custom Lists when sorting. مثلا عندما نقوم بإلغاء تأشير هذا الخيار ستترتب العناصر في مقسّم "شهر الطلب" أبجديا:

إنّ إلغاء تأشير استخدام القوائم المخصصة غير مناسب في هذه الحالة، لأننا نريد عرض الأشهر حسب ترتيبها المعتاد. لكن يكون هذه الأمر مفيدا إذا كانت مجموعة العناصر تتطلب ترتيبا محددا، يمكننا إنشاء قائمة مخصصة Custom List واستخدامها لفرز العناصر في المقسّم.

وإذا كانت هناك عناصر في المقسّم لا تحتوي على بيانات لتصفيتها، ولا نرغب في عرض هذا العنصر، يمكننا تأشير الخيار Hide items with data:

بعد تحديد جميع الخيارات المرغوبة ننقر على موافق OK.

كيفية إنشاء PivotCharts

كما هو واضح من الاسم، PivotCharts تشبه إلى حد كبير PivotTables، فبدلا من الجدول، ستُمثل القيم في مخطط يمكن تغيير طرق العرض فيه بكل سهولة كما نفعل في الجدول المحوري.

هناك أكثر من طريقة لإنشاء PivotChart، إما بتحديد إحدى الخلايا داخل الجدول المحوري، ثم الذهاب إلى تبويب تحليل Analyze والنقر على PivotChart من خانة أدوات Tools:

أو الذهاب إلى تبويب إدراج Insert والنقر على PivotChart من خانة مخططات Charts:

أو النقر على مخططات مستحسنة Recommended Charts من نفس التبويب لعرض المخططات المقترحة والمناسبة لبياناتنا:

وفي جميع الحالات سيُفتح نفس مربّع الحوار الذي يشابه مربّع الحوار الخاص بإدراج المخطط العادي (راجع درس كيفية إنشاء المخططات وتخصيصها في Excel للاطلاع على كيفية اختيار المخطط المناسب للبيانات):

بعد انتقاء نوع المخطط المناسب ننقر على موافق OK ليتم إدراجه في الورقة:

يرتبط هذا المخطط ارتباطا مباشرا بالجدول المحوري، أي أنّ أي تغيير نجريه على الجدول سيتم تحديث المخطط على أساسه. وبالمثل، أي تغيير يجري على المخطط سيتم تحديث الجدول ليعكس هذا التغيير.

فلو قمنا، مثلا، بعمل تصفية لعرض قيمة الربع الثاني في المخطط، سيتم تطبيق نفس العملية على المخطط تلقائيا:

وإذا قمنا بإلغاء تأشير حقل "الفرع" من الجدول، وتأشير حقل "السنة"، سيتغير المخطط تبعا لذلك:

وحتّى لو استخدمنا مقسّمات طريقة العرض Slicers لتصفية الجدول، سيتغير المخطط أيضا:

ولتجنّب ازدحام العناصر في الورقة، يمكننا نقل المخطط إلى ورقة جديدة فارغة. ننقر على المخطط لتحديده، نذهب إلى تبويب تحليل Analyze ثم ننقر على نقل المخطط Move Chart من خانة إجراءات Actions:

نحدد الخيار ورقة جديدة New Sheet ونقوم بإدخال اسم الورقة، ثم ننقر على موافق OK:

سيتم نقل المخطط إلى ورقة جديدة. وستصبح لدينا المزيد من المساحة مما يسّهل تحليل المخطط:

وكما في الجدول المحوري، يحتوي المخطط على أربع مناطق: منطقة عوامل التصفية Filters، منطقة حقول المحور Axis، منطقة وسائل الإيضاح Legend، ومنطقة القيم Values. وبإمكاننا تغيير طريقة عرض المخطط بإعادة ترتيب الحقول وسحبها من منطقة إلى أخرى حسب المطلوب كما فعلنا في الجدول:

توجد العديد من الخيارات لتصميم وتنسيق المخطط المحوري تماما كما في المخططات العادية. فمن تبويب تصميم Design يمكننا تغيير نمط المخطط من معرض الأنماط Chart Styles، أو تغيير ألوانه من أمر ألوان Colors:

كما يمكننا إضافة عناصر إلى المخطط من أمر Add Chart Element أو تغيير تخطيط المخطط من أمر Chart Layout (تُعرض معاينة مباشرة للتخطيط بمجرد تمرير الفأرة فوقه):

إذا، فالمخططات المحورية هي عبارة عن مخططات عادية بمرونة وخيارات الجداول المحورية.

من الرائع في برنامج اكسل أنّه يتيح إمكانية إدخال الكمية التي تريدها من البيانات مهما كانت كبيرة لتملأ الملايين من الخلايا، وكذلك إمكانية عرض هذه البيانات بالعديد من الطرق المختلفة. لكن في جداول البيانات الكبيرة جدا سيكون من الصعب تحليل جميع المعلومات في الورقة، وهنا يأتي دور الجداول المحورية Pivot Tables لتساعدك في تنظيم بياناتك، تلخيصها، وتحليلها.

كما هو واضح من الاسم، الجداول المحورية هي جداول أيضا تحتوي على أعمدة وصفوف، لكنها توفّر خاصية التلاعب بالبيانات وترتيبها بعدة طرق بسرعة وسهولة.

على سبيل المثال جدول البيانات أدناه:

لو أردنا معرفة قيم المبيعات لكل "صنف"، يمكننا تنظيم البيانات وتلخيصها البيانات باستخدام الفرز والتصفية Sort & Filter أو المجاميع الفرعية Subtotals، لكن هذا في حالة كون الجدول صغيرا.

والحقيقة هي أنّ هذا الجدول يحتوي على أكثر من 1400 صف مما يجعل عملية تلخيص البيانات صعبة بدون استخدام الجداول المحورية:

عندما نقوم بتحويل البيانات إلى جدول محوري سيكون بإمكاننا التركيز على ملخص الجدول بدلا من التعامل مع الكم الهائل من البيانات ككل:

نلاحظ في الصورة أعلاه كيف تحوّل الجدول المتكون من 1403 صف إلى جدول مبسّط بأقل من 20 صف. يتكونّ الجدول المحوري من 4 مناطق: منطقة الأعمدة Columns، منطقة المرشحات (عوامل التصفية) Filters، منطقة الصفوف Rows، ومنطقة القيم.

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

كما يمكننا التحكم في الحقول fields التي نرغب في إظهارها/إخفائها في الجدول بتأشير اسم الحقل من قسم الحقول الجانبي (أسماء الحقول هي نفسها عناوين الأعمدة للجدول الأصلي الذي قمنا بتحويله إلى جدول محوري):

وللتركيز على جزء أصغر من البيانات لتحليلها بشكل مفصّل يمكننا استخدام أمر التصفية، أو ما يُسمى بمقسم طريقة العرض Slicers والتي سنأتي إلى شرحها لاحقا في هذا المقال.

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

كيفية إنشاء الجداول المحورية

لنعد إلى الجدول الأصلي؛ مجموعة من الأعمدة والصفوف التي تعرض قيم المبيعات لعدد من الأصناف وحسب الأشهر وأرباع السنة. لتحويل هذا الجدول إلى جدول محوري ننقر على إحدى الخلايا داخل الجدول ثم نذهب إلى تبويب إدراج Insert:

في خانة جداول Tables نلاحظ وجود أمرين لإنشاء الجدول المحوري: PivotTable و Recommended PivotTable.

سننقر الأمر الأول (PivotTable) لإنشاء الجدول (أو يمكننا استخدام الاختصار Alt+N+V). سيظهر مربع الحوار التالي:

نؤشر الخيار تحديد جدول أو نطاق Select a table or range ونحدد الجدول (أو جزء منه) الذي نريد تحويله إلى جدول محوري (وهو محدد تلقائيا هنا لأننا قمنا بتحديد خلية داخله مسبقا).

إذا رغبنا في استيراد بيانات من ملف خارجي، كقاعدة بيانات Access مثلا، نحدد الخيار استخدام مصدر بيانات خارجي Use an external data source.

وكذلك لدينا خيار إنشاء الجدول في ورقة عمل جديدة بتحديد الخيار New Worksheet أو إنشائه في الورقة الحالية Existing Worksheet وتحديد الخلية التي نريده أن يبدأ منها من حقل Location.

يُحدد الخيار الأخير، إضافة هذه البيانات إلى نموذج البيانات Add this data to the data model، إذا كانت البيانات علائقية Relational (أي مقسمة على عدة جداول)، وفي هذه الحالة يمكن إضافة الجداول المتعددة إلى نموذج بيانات في اكسل، إنشاء علاقات بينها، ثم استخدام نموذج البيانات لإنشاء PivotTable.

بعد تحديد جميع الخيارات المرغوبة، ننقر على موافق OK. في الورقة الجديدة ستتم إضافة placeholder للجدول المحوري بالإضافة إلى لوحة جانبية تحتوي على جميع الحقول الموجودة التي يمكن إضافتها إلى الجدول، ومصدرها هو الجدول الأصلي.

سنقوم ببناء الجدول بسحب الحقول التي نرغب في إضافتها إلى المنطقة المرغوبة. فإذا أردنا تصفية الجدول ككل حسب الأشهر، سنقوم بسحب حقل "شهر الطلب" إلى منطقة Filters، وستتم إضافة عامل التصفية مباشرة إلى الورقة:

وسنقوم بسحب حقل "الربع" إلى منطقة الأعمدة وحقل "الصنف" إلى منطقة الصفوف:

نلاحظ أن الجدول ما زال فارغا، وذلك لأننا لم نحدد بعد الحقل الذي نضعه في منطقة القيم. كما نلاحظ أن البرنامج يقوم بإضافة صف وعمود الإجمالي الكلي Grand Total تلقائيا. سنقوم بسحب "المبيعات" إلى منطقة القيم:

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

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

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

سيتم إنشاء الجدول المحوري في ورقة جديدة:

في الشكل الذي اخترناه، يُعرض حقلا "ربع" و "شهر الطلب" كصفوف، وعدد الطلبات في منطقة القيم. ولا توجد حقول في منطقتي الأعمدة والمرشحات (وهذا يعني أنّه ليس بالضرورة استخدام جميع المناطق وإضافة الحقول إليها). يمكننا مواصلة العمل على هذا الترتيب أو تغييره يدويا حسب ما يناسبنا.

تنسيق الجداول المحورية

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

عندما ننقر على أي خلية داخل الجدول سنلاحظ ظهور التبويبين السياقيين تصميم Design وتحليل Analyze:

يحتوي تبويب تصميم على العديد من الخيارات التنسيقية، منها مجموعة أنماط PivotTable Styles تشابه أنماط الجداول العادية. نمرر الفأرة فوق أي من هذه الأنماط لعرض معاينة مباشرة، وعندما نحدد النمط المناسب ننقر عليه لاختياره:

كما يحتوي على خيار تمييز الصفوف (أو الأعمدة) بتنسيق خاص لتسهيل تمييز البيانات ومتابعتها. نحدد أحد الخيارين Banded Rows أو Banded Columns لتطبيق التنسيق الخاص:

وبإمكاننا تطبيق تنسيق خاص لعناوين الصفوف/ الأعمدة بتأشير الخيارين Row Headers/ Column Headers:

وإذا كنا نفضّل المزيد من المساحات البيضاء في الجدول يمكننا إضافة صفوف فارغة تحت كل مجموعة عناصر من خيار Insert Blank Line after Each Item:

كما أنّ هناك المزيد من الخيارات الخاصة بتخطيط الجدول يمكننا الوصول إليها من أمر تخطيط التقرير Report Layout:

في النموذج المضغوط Show in Compact Form (وهو التخطيط الافتراضي) تُعرض عناصر الحقول المختلفة في منطقة الصفوف في عمود واحد (مثلا، في الجدول أدناه تُعرض عناصر "الصنف" و "شهر الطلب" في عمود A)، كما تكون عناوين الصفوف والأعمدة عمومية ("Row Labels" و "Column Labels"):

بالإمكان استخدام النموذج المضغوط إذا كنا بحاجة إلى المزيد من المساحة لعرض القيم. وكذلك بالإمكان استخدام أيقونات +/- عند عناوين الصفوف لعرض/إخفاء التفاصيل المندرجة تحت كل عنوان:

وفي نموذج مخطط تفصيلي Show in Outline Form يتم تخصيص عمود لكل حقل من حقول منطقة الصفوف (مثلا، في الجدول أدناه خصص العمود A لحقل "الصنف" وعمود B لحقل "شهر الطلب)، كما تُعرض تسميات الحقول الحقيقة (مثلا، "الصنف" بدلا من "Row Labels" و"شهر الطلب" بدلا من "Column Labels"):

أما الخيار الأخير، النموذج الجدولي Show in Tabular Form، فهو يشابه النموذج السابق تقريبا، ولكنه يعرض قيم المجاميع (Total) أسفل المجموعات وليس أعلاها:

يمكننا اختيار إي نموذج من النماذج المتاحة، فالأمر يعتمد على التفضيل الشخصي للمستخدم.

من الخيارات الأخرى المتوفرة إمكانية إخفاء قيم الإجمالي الكلي Grand Total للصفوف والأعمدة Off for rows and columns، أو إظهارها للصفوف فقط On for rows only أو للأعمدة فقط On for columns only:

وكذلك إمكانية إخفاء قيم المجاميع الفرعية Subtotals، أو تحديد موضع عرضها في أسفل مجموعات العناصر Show all subtotals at bottom of group أو أعلاها Show all subtotals at top of group:

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

أولا، نحدد إحدى الخلايا التي تحتوي على مجموع المبيعات، ونتأكد من أننا حددنا الحقل المطلوب من مربع الحقل النشط Active Field، ثم ننقر على إعدادات الحقل Field Settings:

نلاحظ في مربع الحقل النشط أنّ الحقل المحدد هو حقل "المبيعات Sum of" وهو ما نريده.

من مربع الحوار "إعدادات حقل القيم" يمكننا إدخال اسم مخصص للحقل Custom Name، ويفضّل أن يكون مختلفا عن اسم المصدر Source Name لكيلا تختلط أسماء الحقول.

كما يمكننا تغيير الدالة التي نريد استخدامها لتلخيص البيانات. الدالة الافتراضية هي الجمع Sum، ونستطيع استخدام الدوال الأخرى كالمتوسط، الضرب، إلخ:

ولتغيير تنسيق الأرقام في منطقة القيم، ننقر على تنسيق الرقم Number Format. في مربع الحوار تنسيق الخلايا ننقر على الخيار عملة Currency، ثم نحدد نوع العملة وعدد الفواصل العشرية في الرقم، ثم موافق OK:

وسيتم تنسيق الأرقام في منطقة القيم بصيغة عملة:

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