إعداد بيانات تجريبية بإستخدام الدوال Sample Data
إعداد بيانات تجريبية بإستخدام الدوال في أكسيل Excel
Sample Data
لا يقتصر التعامل في Excel علي الجداول بداخله فقط ولكن يمكنك إدخال بيانات قواعد البيانات Database إلي Excel أو بإرسال بيانات إلى أى من برامج التعامل مع قواعد البيانات وعلي رأسهم برنامج Access Xp .... وبالطبع ستسفيد من إمكانيات Excel في تحليل البيانات الواردة أو عرضها بصوة تفعليه أو غير تفاعلية كما سنتناول فى باقي فصول هذا الكتاب .... وفى كل هذه الحالات نحتاج لبيانات تجريبية لتسخدمها أثناء إعداد صفحات العمل .
إنشاء قاعدة بيانات Create Database
قبل البداية قاعدة البيانات هى قائمة عملنا معها ولكنها تتميز بضخمها فهى تنشأ قاعدة بيانات ..
1 – قم بإنشاء كتاب عمل جديد وذلك باختيار New من القائمة File يظهر لك Task pane اضغط الخيار Blank Workbook ليظهر لك كتاب عمل جديد .
2 – من القائمة File اختر الأمر Save as ثم اكتب " قاعدة البيانات " في مربع File Nameثم اضغط المفتاح My Document من يسار الصندوق أو حدد أو حجج مكان حفظ كتب عملك ... ثم اضغط امفتاح Save لحفظ كتاب العمل وغلق الصندوق الحواري .
3 – أذا كتنا مثلي تفضل العمل بالاتجاه العربي اضغط الأيقونة Sheet Right – to – left لتحويل إتجاه صفحة العمل إلي الاتجاه العربي ... أودعها كما هي إذا لم تكن تفضل ذلك .
اضغط مفتاح Enter اضغط الأيقونة Save لحفظ كتاب العمل .
قاعدة البيانات التي نحن بصدد إنشائها تمثل مصروفات ثلاث أفرع لشركة الطوفان ناشر هذا الكتاب هم فرع الاسكندرية ، فرع القاهرة ، فرع المنصورة ... وتغطي بيانات بنود من المصروفات هي
مصروفات الاستقبال والضيافة مصروفات الإضاءة مصروفات إدارية
مصروفات التخزين والنقل مصروفات التكييف مصروفات دعاية
مصروفات العلاقات والنقل مصروفات النظافة والأمن
وذلك خلال 12 شهر بداية من شهر 1/2005 إلي شهر 12/2005 وبذلك يمكنك الحصول من هذه القاعدة علي المبلغ المصروف ... في أى من البنود الثماني في أي فرع ... في أى شهر من شهور عام 2005 .
5 – ادخل رأس القلائمة كما يي ...
التاريخ A1
نوع المصروفات B1
الفرع C1
القيمة D1
وتذر استخدام مفتاح TAB من لوحة المفاتيح لكى تنتقل D1 C1 B1 A1
6 – حدد الصف الأول بضغط رأس الصف عندما يتحول المؤشر إلى سهم عريض ثم اضغط الأيقونة Bold B لجعهل عناوين القائمة تعرض بخط ثقيل .
7 – اضغط حد رس العمود B ضغط مزودجة عندما يكون المؤشر سهم ذو رأسين ليتسع العمود لاحتواء ما كتب به .
8 – اكتب ....
تذكر استخدم مفتاح Enter من لوحة المفاتيح لكي تنتقل من C1 C2 C3
9 – اكتب ....
10 – اكتب ....
A2 -----1/2005
A3-----2/2005
وستجد أن Excel قد حولهم إلى يناير 05 ، فبراير 05 وهذا غير مناسب لضبط تنسيق التاريخ بالصورة المناسبة لنا فى هذا العمل .
11 – حدد الخاليتان A2 ; A3 ثم اختر الأمر Cell من القائمة Format ليعرض لك الصندوق الحواري Format cell .
12 – اضغط مجموعة الخيارات Number لعرضها إذا لم تكن معروضة من القائمة Category اضغط Data يعرض لك قائمة بها مجموعة من التنسيقات اضغط أول تنسيق ثم لاحظ المربع Sample الذي يعرض لك محتوي الخلية A1 وقد نسق حسب أول خيار .
13 – ليس هذا ما نريد استخدام مفاتيح الأسهم لتجول فى القائمة بحثا عن تنسيق يعرض لك التاريخ في A1 هكذا 1/2005 أو أقرب شيء إلى ذلك ... اختر أول تنسيق واذلي يعرض لـ التاريخ في الخلة A1 01/01/2005 ثم اضغط الخيار Custom من القائمة Category لتري كود التنسيق ستجد أنه dd/mm/yyyy أظنك فطنت إلى أن .
dd تمثل اليوم
mm تمثل الشهر
yyy تمثل السنه
امسح " dd/ " أى لجعله " mm/yyyy " وذلك بالضغط في المربع type والسحب لتحديد "dd/" ثم اضغط مفتاح Delete لمسحهم ... إذا مسحت أى حروف خطأ أعد كتابتها ولاحظ الحروف الكبيرة ربما لا تعتطي نتائج مختلفة عن الحروف الصغيرة وستجد أن المربع معاينة Sample يعرض لك التاريخ كما نريد اضغط ok لغلق الصندوق الحواريوستجد أن التنسيق قد طبق علي الخلايا ... تذكر أن التاريخ يظهر في شريط المعادلات بطبيعته الكاملة .
عودا للعمود C اذلي به أسماء الفروع وفكر معي قليلا لدينا 3 فروع كل فرع لديه 8 بنود مصروفات إذا لدينا 288=12*24 بند مصروف كل عام .
ولذلك سيكون عدد صفوف قاعدة البيانات لدينا 288 صف مضافا إليهم صف العناوين إذا يصبحون 289 صف .
14 – حدد الخلايا من C2 : C4 ثم اضغط الأيقونة نسخ Copy لنسخهم .
15 – اضغط الخلية C 2 لتحديدها واسحب في العمود لتحديد 24 خلية تحتها إلى أن تصل لصف 25 وستجد "أن مربع Name box كتب لك 24R * 1C وهذا يعني أنك حدد 24 صف في عرض عمود واحد .
16 – اضغط الأيقونة Paste لتكرار نسخ الأفرع خلال الخلايا المحددة كما في الشكل .
17 – مع الاحتفاظ بالخلايا محدد اضغط أحد أيقونات سواء Ascending Sort أو Sort Desending سيظهر لك الصندوق الحواري Sort Warring والذي يحذر من فرز أحد أعمدة القائمة دون الباقيين معه لأن ذلك يفسد بيانات القائمة .
18 – اضغط Continue with the Current selection ثم اضغط ok لتجد أن القائمة تم ترتبها كما بالشكل ... كل فرع تم تكراره ثمان مرات .
19 – حدد الخلايا B2 : B9 ثم اضغط الأيقونة Copy .
20 – اضغط واسحب لحديد 25 خلية فى العمود B أى إلى الصف 25 وستجد أن المربع Name box يعرض لك 24R * C1 مما يعني أنك حدده 24 صف يعرض عمواد واحد .
21 – اضغط الأيقونة Paste لتكرار نسخ الأفرع خلال الخلايا المحددة كما فى الشكل .
22 – اضغط Esc لإخفاء إطار النسخ المنقط .
تأمل ما لدينا في القائمة وستجد أمام كل تكرار من الفروع تكرار من المصروفات .... ما نريده في جميع الأفرع .
23 – حدد الخلايا C2 : B25 ثم اضغط الأيقونة Copy لنسخ محتوي الخلايا ... أكمل الخطوة التالية مع المحافظة علي التحديد الحالي .
24 – اضغط مفتاح Shift ولا ترفع إصبعك منه حتي تنهي هذه الخطوة ... اضغط مفتاح Page Down من لوحة المفاتيح لتجد أن التحديد تمدد كرر الضغط وذلك حتي تقترب من الصف 289 ولاحظ أنك ستحتاج استخدام مفاتيح الأسهم للوصول إلى الصف 289 بالضبط لأن Page Down ستنقلك قريب منها وليس إليها تماما .... ستظهر لك تلميحه صفراء اللون كتب به .288R *2C .
25 – عندما يصل التحديد إلى الصف 289 حرر مفتاح Shift ... اضغط الأيقونة Paste لتجد أن Excel كرر ذلك البيانات خلال الصفوف ... أكمل مع المحافظة علي التحديد الحالي.
26 – استخدام الماوس وشريط التمرير لتحرك إلى أعلي القائمة وذلك بسحب مربع التمرير إلى نهاية شريط التمرير ثم اضغط Esc لإخفاء مربع التسخ مع المحافظة علي التحديد الحالي .
27 – سنوقم بعمل فرز للقائمة التي لدينا ولكن فى هذه المرة سنتسخدم الأمر Sort من القائمة Data ليعرض لنا الصندوق الحواري Sort .
أولا: تأكد أن الخيار Header row محدد تلقائيا وذلك لأن Excel أدرك أن لها شريط عناوين حتي يقوم بفرز كل الصفوف المحدد مع استبعاد أول صف .
ثانيا : يتميز هذا الصندوق عن الأيقونة Sort أنه يمكنك من الفرز باستخدام أكثر من عمود مع تحديد نوع الفرز في كل عمود هل هو تصاعدي Ascending أو تنازلي Descending .
28 – اختر من القائمة الثانية Then by اختر "الفرع " وتأكد أن تصاعدي Ascending هو المحدد ثم اضغط Ok لغلق الصندوق الحواري ... لتجد القائمة قد رتبت كما بالشكل التالي ...
لاحظ أن مصروفات الاستقبال والضيافة فى فرع الاسكندرية تم تكراراها لعام كامل ثم مصروفات الاستقبال والضيافة فى فرع القاهرة لعام كامل ثم مصروفات الاستقبال والضيافة فى فرع المنصورة لعام كامل ... وهكذا ... الآن نريد إضافة التواريخ للقائمة .
29 – حدد الخلايا A1 :A2 بالطلبع أدخلنا تايخين لنكون ما يسمي بـ Pattern او تركيبة ثم عندما نطلب من Excelملأ العمود سيفهم كيف يتم ذلك؟ اسحب مربع الملأ Auto fill للخلية إلى الصف 13 كما بالشكل لتجد أن نهاية التاريخ 12/2005 وهذا ما نريده .
ما نريده هو تكرار قائمة التاريخ 12- خلية – 24 مرة لنصل لصف –(12*24)+1-289 مع الأخذ في الاعتبار صف العناوين .
30 – إذا لم تكن خلايا التاريخ A2:A13 حددها ... اضغط مربع الملأ التلقائي Auto Fill ضغطة مزدوجة لكي يملأ الخلايا حتي نهاية القائمة.
31 – تأمل الخلية A14 ستجد أن Excel لم يكرر التواريخ ولكن قام بإكمالها بخاصة الملأ التلقائي Auto Fill وهذا ما لا نريده .. لذلك اضغط العلامة الذكية Smart tag ثم اختر Copy Cell لتصلح الأمر .
32 – اضغط الأيقونة Sort Descending حدد الخيار Expand the Selection ثم اضغط Ok لأننا الآن نتعامل مع قائمة ويجب أن تفرز حتي لا يحدث خطأ فى البيانات .
لتجد الآن قائمتك مرتبه تنازلي حسب عمود التاريخ .
33 – اضغط الأيقونة Save لحفظ عملك .
الآن يبقي لنا ملأ العمود القيمة بقيمة المصروف لكل بند ... سنبحث عن دلة تحصل منها علي أرقام عشوائية لتستخدمها فى الملأ العمود " القيمة " .
34 – تحرك إلى الخلية D2 ثم اضغط الخلية ثم اضغط الأيقونة Inert Function في شريط المعادلات ليظهر لك الصندوق الحواري Inert Function اكتب "Random Numbers " في المربع Search for Function ثم اضغط المفتاح Go.
35 – وستجد باقلائمة الدالة وهي Rand والتي تعطيك أرقام بين صفر وواحد وإنها تحدث نتائجها عن إعادة الحسابات بالصفحة أو غلقها أو فتحها أو عند ضغط مفتاح F9 وأنها تعطي أرقام مختلفة عند نسخ في خلايا مختلفة ... بالطبع كل هذا حصلت عليه بالضغط علي Help on This Function الذى عرض لي نافذة المساعدة بها شرح تفصيلي لدالة .
36 – أغلق نافذة المساعدة ... ثم اضغط Ok في الصندوق الحواري Insert Function .
37 – ليعرض لك الصندوق الحواري Function Argument ولن تكون مفاجأة أن تجد ان الدالة لا تحتاج لأي وسيطات اضغط Ok لغلق الصندوق وستجد أن الخلية D2 تحتوي رقم عشوائي من نتاج الدالة RAND .
38 – اضغط F9 عدة مرات لتري الرقم فى الخلية يتغير في كل مرة تضغط فيه .. اضغط مربع الملأ Auto Fill للخلية أن الخلايا التي نسخت إليه المعادلة قد ظهرت فيها أرقام بين 0 و1 .
الواقع عندما نتأمل الأرقام التي حصلنا عليها سنكشف أنها غير مناسبة تماماً وإننا نحتاج لأرقام بين 1000-100 لتكون مناسبة .
عندما تصفحت شرح عمل الدالة Rund() وجد به معادلة يمكنك تطبيقها لتحصل علي قيم في مدي معين هي
RAND *(a-b) +a
حيث a أقل رقم تريد الحصول عليه في حالتنا هنا 100 .
حيث b أكبر رقم تزيد الحصول عليه في حالتنا هنا 1000 .
إذا ستصبح المعادلة RAND()* 900 +100
39 – هيا ندخل المعادلة فى الخلية D2 تحرك للخلية D2 واضغطها تحرك لشريط المعادلة واكتب *900+100 كما بالشكل ثم اضغط الأيقونة فى شريط المعادلات لإدخال المعادلة الجديدة فى الخلية D2 .
قم بتكرر المعادلة في عدد صفوف لنتأمل النتنيجة وذلك بسحب مربع الملأ Auto Fill.
أظن أنها جيدة ولكن عادة المصروفات يكون بها رقم عشري واحد وليست ثلاث لأن توجد عمله تمثل المليم ، القرش في المعاملات اليومية الآن .
تحرك إلي الخلية D2 واضغطها ثم الأيقونة Insert function سيظهر لك الصندوق الحواري يذكر أن الخلية بها معادلة وليست دالة فقط اضغط Ok .
40 – ثم اضغط الخلية E2 ثم اضغط الأيقونة Insert function من شريط المعادلات ليظهر لك الصندوق الحواري Insert function واكتب "Round" في المربع Search for function وستجد أن الدالة Round تقوم بعمل تقريب للرقم حسب ما تحدد لها Ok .
41 – يظهر لك الصندوق الحواري Function Argument وبه وسيطتان الأول Number وبضغطه مربعه يوضح لك Excel أن الرقم الذي تريد تقريبه ... والثاني Num Digits وبضغطه مربعه يوضح لك أنه عدد الأرقام بعد العلامة العشرية التي تريد أن يكون فى ناتج الدالة .. جرب واكتب فى المربع Number "100.333" ... ثم اكتب في المربع Num – digits وستجد أن الناتج "100.3" .
جرب و اكت صفر سيكون الناتج 00
42- اسمح كل الأقرام اضغط Ok لإغلاق الصندوق الحواري وإدخال الدالة في الخلية E2 ....
43 – تحرك للخلية D2 واضغطها ثم تحرك لشريط المعادلات وحدد المعادلة به بالسحب عليها بالماوس اضرب مفتاح الماوس الأيمن يظهر لك قائمة اختر منها Copy نسخ المعادلة .
44 – اضغط الأيقونة Cancel في شريط المعادلات للخروج من معادلة الخلية D2 دون تغيرها.
45 – اضغط الخلية E2 ثم اضغط الأيقونة Insert function في شريط المعادلات ليظهر لك الصندوق الحواري Function Argument وبه الأرقام اتي أدخلناها في المرة السابقة حدد الرقم بالمربع number بالسحب عليه بالماوس ثم اختر الأمر Paste من القائمة Edit لتنسخ المعادلة التي كانت بالخلية D2 في المربع number كوسيط لدالة RAAND() ... ثام اكتب في المربع "1" Num – digits .
46 – اضغط Ok ... ستظهر لك رسالة خطأ تخبرك أن المعادلة بها خطأ اضغط Ok لغلق الصندوق التحذيري .
47 – ثم اضغط الأيقونة Insert Function مرة أخري لفتح الصندوق الحواري Function Argument وامسح العلامة = بالسحب عليها بالماوس وضغط فتاح Delete ثم اضغط OK لتجد أن القيمة ثم تقريبها لرقم عشري واحد .
48 – حدد الحلية D1 ثم اضغط المفتاح Delete لمسح بياناتهم حدد الخلية E2 ثم اسحبها عندما يتحول المؤشر إلى سهم ذو أربع رؤوس إلى خلية D2 .
49 – اضغط مربع الملأ fill Auto للخلية D2 ضغطه مزدوجة لتملأ خلايا العمود " القيمة" بالقيم التي تتراوح بين 1000.00 وبها رقم واحد بعد العلامة العشرية .
يعيب البيانات فى العمود القيمة أنها تستخدم الدالة RAND() دالة أى ستتغير في كل مرة تضغط فيها مفتاح F9 ... ولتحويل الأرقام بالعمود " القيمة " إلى قيم ثابتة نفذ الآتي:
50 – حدد الخلية D2 بالضغط عليها ... اضغط مفتاح Shift أثناء ضغط الحد السفلي للخلية بالماوس ضغطة مزدوجة لتحدد خلايا العمود " القيمة " التي بها بيانات فى القائمة كلها ...
51 – اضغط الأيقونة Copy لنسخ محتويات الخلايا ... ثم اضغط الأيقونة paste للصقهم مرة أخري ... وستظهر لك العلامة الذكية Smart tag اضغط السهم الصغير لتظهر لك القائمة اختر منها value only ليتم تحويل محتوي الخلايا إلى قيم بدون معادلات .
للتحديد السريع أثناء العمل مع القوائم الضخمة حدد خلية بالمدي الذي تريد تحديده ثم اضغط مفتاح Shift أثناء ضغط أحد حدود الخلية مزدوجا وذلك ليمتد التحديد في اتجاه الحد الذي تضغط إلى أخر القائمة .
في الخطوة رقم 35 عندما كتبنا Random Numbers في المربع Search for Function الخاص بالصندوق الحواري Insert Function .... خرجت الدالة RANDBETWEEN في ناتج البحث ... وهي تعمل مثل الدالة RAND() تماما فى توليد أرقام عشوائية .. ولكن هذه الدالة لن تكون متاحة عند كل مستخدمي Excel حيث أنها تم إضافتها مع إمكانيات وأدوات أخري إضافية بواسطة إحدي شركات التسويق برامج ميكروسوفت .
والدالة RANDBETWEEN تأخذ وسيطتان الأولي Bottom وهي أقل رقم تريد في الأرقام التي تولدها الدالة ... والوسيطة الثانية Top وهي أكبر رقم تريد في الأرقام التي تولدها الدالة ... مما يعي في مثالنا السابق كنا علينا كتابة "100" في المربع Bottom و"100" في المربع Top لنحصل علي ما نريد ولكن يعيب هذه ادالة فى حالتنا هذه أن المصروفات دائمة بها رقم عشري علي الأقل واحد وهو ما لا نتيجه الدالة RANDBETWEEN ... لذلك لم نستخدمها
تعليقات
إرسال تعليق