التعامل مع خلايا ورقة العمل في Excel

تسمية خلايا ورقة العمل

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

فماذا لو كتب معني كل خلية في مكانها في المعادلة كما في الشكل التالي ...

لعمل ذلك حدد الخلايا من B5 :A1 ثم من القائمة Insert أختر Name تظهر لك قائمة فرعية أختر منها Create يظهر لك الصندوق الحواري Create Name حدد الخيار Right Column وألغي أي تحديد أخر ... وذلك لأن الأسماء التي تعبر عن الخلايا كتب في العمود في اليمين ثم أضغط Ok.

لاستعراض الأسماء أضغط السهم الصغير بجوار المربع Name Box يعرض لك قائمة بأسماء الخلايا أختر أحد الأسماء وستجد أن Excel تحرك للخلية التي تحمل الأسم الذي أخترته ...

لاحظ أن Excel إضافة العلامة Under Score "_" بين الكلمات في أسماء الخلايا بدل المسافات الفارغة بين الكلمات ... وعند اختيار الخلية المسماة " قيمة القسط الشهري " تظهر لنا المعادلة بشكلها السابق في شريط المعادلات ودون أي تغيير .

لتغيير المعادلة لتعكس الأسماء الجديدة نفذ الأتي :

تحرك إلي القائمة Insert وأختر Name ... تظهر لك قائمة أختر منها Apply ... ليظهر لك الصندوق الحواري Apply Name ... ستجد في الغالب جميع الأسماء التي أضفتها محددة ...

الأسماء المحددة تكون مظللة بلون أزرق كما بالشكل وإذا لم تجد أحد الأسماء محدد أضغطه لتحديده ثم أضغط المفتاح Ok .

وستجد أن المعادلة أصبحت كما بالشكل التالي ...

جدول استهلاك الدين Amortization :-

بعد حصولك علي القرض سيدور بذهنك سؤال دائما ما يتردد بين الحين والحين كم بقي عليك في هذا القرص ؟ ... يسمي الجدول استهلاك الدين لحساب كم دفعت كفائدة وكم دفعت كسداد للقرض .

1- قم بنسخ ورقة العمل التي سميناها "حساب القرض" بسحبها أثناء ضغط مفتاح Ctrl من لوحة المفاتيح وسيظهر لك علامة "+" لتوضح لك أنك تقوم بعملية نسخ حرر مفتاح الماوس بحيث تصبح الورقة الجديدة هي ثاني ورقة عمل في كتاب العمل ثم حرر مفتاح Ctrl .

2- أضغط اسم الورقة المنسوخة "حساب القرض (2)" مزدوجة وأكتب "جدول استهلاك الدين ثم أضغط Enter لتغير اسم الورقة .

3- اختر "عدد_سنوات_سداد_القرض" من القائمة التي تظهر بضغط السهم الصغير بجوار Name box وستجد أن Excel حدد لك الخلية التي بها عدد سنوات القرض أكتب 1 ثم أضغط Enter وذلك لتغير فترة السداد إلي سنة واحدة .

تغير "عدد_سنوات_القرض" هنا لتسهل عليك متابعة إنشاء جدول إستهلاك الدين ليس إلا !!؟ .. إضغط حد العمود B مزدوجا ليتسع للقيمة الجديدة لقيمة القسط الشهري .

تحرك إلي الخلية B2 ثم أضغط الأيقونة Copy من شريط الأدوات لنسخ تاريخ بداية القرض لأنه هو أول تاريخ في جدول استهلاك الدين ... ثم تحرك إلي الخلية D1 وإضغطها ثم أضغط الأيقونة Paste للصق تاريخ بداية القرص بها ... أضغط حد العمود الأيسر D مزدوجا ليتسع لقيمة التاريخ الذي تم لصقه به .

4- اسحب مربع الملأ التلقائي إلي النصف 13 نعم 13 ثم أضغط سهم العلامة الذكية Smart Tag ومن القائمة التي تظهر أختر Fill Mouth لتصبح كل خلية في العمود تمثل تاريخ بداية شهر من شهور القرض .

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

3- تحرك للخلية E1 وأضغطها وأكتب فيها علامة "=" ثم أضغط الخلية B2 ستجد أن Excel

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

أضغط الخلية E1 لتظهر لك في شريط المعادلة "قيمة_القرض=" وبذلك عند تغير قيمة القرض ستتغير القيمة في الخلية E1 تلقائيا ... لا تنسي أن "قيمة(القرض" هي أسم الخلية B2 .

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

4- تحرك إلي الخلية E2 وأضغطها ... ثم أضغط الأيقونة Insert Function من شريط المعادلات يظهر لك الصندوق الحواري Insert Function ... في المربع Search Function for أكتب ''Future Value of a Loan'' أي القيمة المستقبلية للقرض ثم أضغط المفتاح Go ... وستري عدد من الدوال عند ضغط أي دالة يظهر لك Excel شرح لعمل الدالة .

5- ستجد أن الدالة (Future Value)FV هي التي تريد أضغطها ثم أضغط Ok ليعرض لك Excel الصندوق الحواري Function Arguments والذي يمكنك منه إدخال قيمة البيانات التي تحتاجها الدالة لحساب قيمة القرض ... وستجد أم هذه الدالة تحتاج لثلاث بيانات أساسية وأثنان اختيارين وذلك كما وضحنا عن كتابة اسم الوسيط بالأسود العريض فهذا يعني أنه ضروري لعمل الدالة أما عند كتابة الوسيط بدون الأسود العريض فهذا يعني أنه اختياري .

6- أضغط المربع Rate يعرض لك Excel شرح لمعني هذا الوسيط وهو نسبة الفائدة لكل مدة قسط كما أدخلناه قبل ذلك اضغط الخلية B2 ستجد لأن Excel كتب لك "الفائدة_السنوية" اكتب "12/" .

7- ثم تحرك للوسيط الثاني و أضغط المربع Nper يظهر لك شرح لمعني هذا الوسيط وهو ما يوضح أن هذه الوسيطة تأخذ عدد المدد التي تريد حساب قيمة القرض بعدها بالطبع نريد حسابه بعد مدة واحدة ... أكتب "1" .

8- أضغط الوسيطة الثالثة Pmt يظهر لك شرح لمعني الوسيطة وهذا ما يوضح أن هذا الوسيطة هي قيمة القسط لكل مدة ... أضغط الخلية B5 لتجد أن Excel كتب لك قيمة القسط الشهري ... وستلاحظ أنه بدأ يظهر لك نتيجة بجوار Formula result لأنك أدخلت كل الوسيطات الأساسية ... ولن الدالة لم تعمل كما نريد !! .

9- تحرك إلي الوسيط الثالث PV وهي وسيط اختيارية ولكن سنحتاجها هنا لإدخالها عند ضغط المربع PV يظهر لك Excel شرح لمعني الوسيطة وهذا ما يوضح أن هذه الوسيطة هي قيمة القرض عند بداية فترة حساب القيمة المستقبلية ... بالطبع سوف تضغط الخلية E1 ثم أضغط OK ... لتجد أن Excel كتب لك المعادلة في شريط المعادلات ولكن يعيب هذه الدالة إنها تعبر عن القيمة كقيمة سالبة وذلك لأنه دين .

10- يمكننا إضافة علامة (-) قبل اسم الدالة في شريط المعادلات للحصول علي قيمة موجبة ثم ضغط الأيقونة لإدخال التعديل لمعادلة في الخلية E2 .

11- اسحب مربع الملأ التلقائي Auto Fill للخلية E2 وذلك لملأ باقي الجدول وستجد أن قيمة الدين في 1/08/2006 صارت صفر أي تم استهلاكه تماما كما يقول التجاريين .

بعد إدخال المعادلة في الخلية E2 الخلية أضغط مربع الملأ AutoFill ضغطه مزدوجه ستجد أن Excel نسخ المعادلة الي باقي الجدول تلقائية ... وتعد هذه أسرع طرق إستخدام الملا التلقائي Auto Fill لملأ خلايا الجدول .

12- قم بتنسيق الخلية E1 بنسخ التنسيق من الخلية E2 وذلك بضغطها ثم أضغط الأيقونة Format Painter ثم أضغط الخلية E1 لنسخ التنسيق لها لتصبح ورقة العمل كما يلي ....

13- أبدأ في اختيار العمل الذي قمنا به ... أدخل 5% في الخلية B3 الفائدة السنوية ثم أدخل 6% ... ستجد أن كل قيم جدول استهلاك الدين تتغير لتعكس التغيير .... أدخل 18000 في الخلية B2 قيمة القرض ثم أدخل 8000 ...سنجد أن كل قيم جداول استهلاك الدين تكفي لتعكس التغيير .

ولكن إذا غير التاريخ لن يتغير أي من التواريخ في جدول استهلاك الدين فماذا تفعل لنصلح هذا الخطأ ... غالبا ما سيخطر علي بالك هو التعديل في الخلية D1 وذلك بضغط الخلية لنا Excel "التاريخ=" في الخلية D1 وبذلك عند تغير التاريخ في B1 يتغير في D1 ولكن ماذا عن باقي العمود D .

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

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

1- تحرك إلي الخلية F1 ثم أضغطها ثم أضغط الأيقونة Insert Function في شريط المعادلات يظهر لك الصندوق الحواري Insert Function في المربع Search for function أكتب ''Year'' ثم أضغط المفتاح Go لتعرض لك الدوال التي تتعال مع جزء السنوات في التاريخ عندما نضغط أي دالة يظهر لك شرح لعملها في أسفل الصندوق الحواري ...

ستجد أن الدالة Year ترجع لك جزء السنة من التاريخ أضغطها ثم أضغط Ok .

2- يظهر لك الصندوق الحواري Function Argument وبه متغير واحد أضغط مربع المتغير Serial_number ليظهر لك شرح في منتصف الصندوق الحواري لمعني المتغير يوضح أنه التاريخ المراد استخلاص السنة منه ... أضغط الخلية D1 ثم أضغط Ok .

يظهر لك في الخلية F1 "2005" وهذا يعني أن الدالة Year قدمت لنا جزء السنة منفصل عن باقي التاريخ أكتب تواريخ مختلفة في D1 وجرب عمل الدالة ...

3- تحرك إلي الخلية G1 ثم أضغطها ثم أضغط الأيقونة Insert Function في شريط المعادلات . يظهر لك الصندوق الحواري Insert Function أكتب في المربع Search for Function الكلمة ''Month'' ثم أضغط المفتاح Go لتظهر لك الدوال التي تتعامل مع جزء الشهر في التاريخ عندما تضغط أي دالة يظهر لك شرح لعملها في أسفل الصندوق الحواري ستجد أن الدالة Month ترجع لك جزء الشهر من التاريخ ... اضغطها ثم أضغط المفتاح Ok .

4- يظهر لك الصندوق الحواري Function Argument وبه متغير واحد أضغط مربع Serial_number ليظهر لك شرح لمعني المتغير في منتصف الصندوق الحواري يوضح أنه التاريخ المراد استخلاص الشهر منه أضغط الخلية D1 ثم أضغط Ok .

ليظهر لك في الخلية G1 "8" وهو شهر التاريخ في الخلية D1 ...

أكتب تواريخ مختلفة في الخلية D1 وجرب عمل الدالة .

5- تحرك للخلية H1 ثم أضغطها ثم أضغط الأيقونة Insert Function في شريط المعادلات يظهر لك الصندوق الحواري Insert Function أكتب في المربع Search for Function الكلمة ''Day'' ثم أضغط المفتاح Go لتظهر لك الدوال التي تتعامل مع اليوم عندما تضغط أي دالة يظهر لك شرح لعملها في أسفل الصندوق الحواري ستجد أن الدالة Day ترجع لك جزء اليوم في التاريخ أضغطها ثم أضغط المفتاح Ok .

6- ليظهر لك الصندوق الحواري Function Argument وبه متغير واحد أضغط المربع Serial_number ليظهر لك شرح لمعني المتغير في منتصف الصندوق الحواري يوضح أنه التاريخ المراد استخلاص اليوم منه أضغط الخلية D1 ثم أضغط Ok .

ليظهر لك في الخلية H1 "1" وهو يوم التاريخ في D1 ...

 

والأن ننتقل إلي الخطوة التالية وهي جمع التاريخ من اليوم والشهر والعام بعكس ما فعلناه في الخطوات السابقة مع إضافة +1 للشهر .

1- تحرك للخلية ثم أضغطها ثم أضغط الأيقونة Insert Function وأكتب في المربع Search for Function الجملة ''Date From Year AND Month and Day'' ثم أضغط المفتاح Go يعرض لك Excel بعض الدوال عندما تضغط أي دالة يظهر لك شرح لعملها في أسفل الصندوق ستجد أن الدالة Date ترجع التاريخ من أجزائه الثلاثة هي المطلوب أضغطها ثم أضغط مفتاح OK .

2- يظهر لك الصندوق الحواري Function Argument أضغط المربع الأول Year يظهر لك شرح لعمل هذا الوسيط والذي يمثل العام في التاريخ أضغط الخلية F1 لإدخال السنة ...

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

أضغط المربع الثالث Day يظهر لك شرح لعمل هذا الوسيط والذي يمثل اليوم في التاريخ أضغط الخلية H2 لإدخال اليوم ثم أضغط Ok .

لتحصل بذلك في الخلية I1 علي تاريخ يزيد بشهر عن التاريخ في الخلية D1 ...غير قيم التاريخ في D1 ولاحظ القيم في الخلية I1 أنه مثل D1 مع زيادة شهر وهذا ما نريد هيا نكمل عملنا بالصورة المطلوبة .

3- تحرك للخلية D2 واضغطها ثم اضغط الأيقونة Insert Function ليظهر لك الصندوق الحواري Insert Function أختر Most Recently من القائمة Select a Category لم تكن مختارة وهي تعرض لك أخر دول تعاملت معها ستجد فيها الدالة date اضغطها ثم اضغط Ok .

4- ليظهر لك الصندوق الحواري Function Argument وبه ثلاث وسيطات Year , Month , Day سندخل دالة في كل وسيطة تحسب قيمة من التاريخ في D1 .

اضغط المربع Year ثم أكتب "1)Month" ثم أضغط الخلية D1 ثم أكتب "1+(" ليصبح ''Month (D1)+1'' .

أضغط المربع Day ثم أكتب ''Day('' ثم أضغط المربع D1 ثم أكتب "(" ليصبح ''Day (D1)'' ... أضغط ok .

وجرب عمل الدالة ... بتغيير التاريخ في D1 .

يمكنك إدخال أي عدد من الدوال كوسيط لدوال أخري كما في الخطوة السابقة وتعد هذه التقنية من أنجح التقنيات في Excel لعمل ورقة العمل work sheet ذات الاستخدامات المتعددة .

5- اضغط مربع الملأ التلقائي Auto Fill ضغطة مزدوجة لملأ باقي العمود بالتواريخ الصحيحة .

6- اضغط الخلية D1 ثم أكتب = ثم أضغط الخلية B1 ثم أضغط Enter لإدخال التعديل في الخلية D1 ليكتب لنا Excel "التاريخ =" في الخلية D1 وبذلك عند تغير التاريخ في B1 يتغير في D1 ... لا تنسي التاريخ هو اسم الخلية B1 .

جرب صفحة العمل يتغير التاريخ في الخلية B1 وستجد أن الأمور تسير بالطريقة الصحيحة تتغير الخلية D1 وباقي العمود D بصورة جيدة .

7- حدد الخلايا من F1 إلي I1 ثم أضغط المفتاح Delete لمسحهم حتي لا يتسببوا في إرباك من تصفح صفحة عملك .