قواعد البيانات هي مجموعة من البيانات المنطقية المترابطة مع بعضها البعض، حيث تتكون قاعدة البيانات من جدول أو أكثر. ويحتوي الجدول على صف واحد أو أكثر يُسمى الصف بالسجل، في حين تمثل أعمدة البيانات الحقول، كما يحتوي الصف الأول من البيانات على تسميات كل عمود.
يحتوي الإكسل على مجموعة من دوال قواعد البيانات والتي تسهِّل البحث والتصفية داخل قواعد البيانات.
سنشرح في هذا الدرس مجموعةً من دوال قواعد البيانات، وهي:
- Dcount: تَحسُب هذه الدالة عدد الخلايا التي تحتوي على أرقام تحقق الشرط المحدد في عمود السجلات في قاعدة البيانات.
- DcountA: تَحسُب هذه الدالة عدد الخلايا غير الفارغة في عمود السجلات في قاعدة البيانات، والتي تحقق الشرط المحدد.
- Dsum: تجمع هذه الدالة الأرقام في عمود السجلات في قاعدة البيانات والتي تُحقِّّق الشروط التي حددتها.
- Daverage: تحصل هذه الدالة على متوسط القيم في عمود بإحدى القوائم، أو في قاعدة بيانات تطابق الشروط المعينة.
- Dproduct: تضرب هذه الدالة القيم في عمود السجلات في قاعدة البيانات، والتي تُحقق الشروط التي حددتها.
- Dmax: تُرجِع هذه الدالة أكبر رقم في عمود من سجلات قاعدة البيانات، والتي تحقق الشروط التي تحددها.
- Dmin: تُرجِع هذه الدالة أصغر رقم في عمود من سجلات قاعدة البيانات، والتي تحقق الشروط التي تحددها.
- Dget: تستخرج هذه الدالة سجلًّا واحدًا يحقق الشروط التي حددتها من قاعدة بيانات.
لتوضيح الدوال السابقة؛ سنشرح المثال التالي والذي يشمل إجمالي مبيعات في سنوات مختلفة لعدة مناطق.
أدرج الجدول السابق في ورقة العمل في الإكسل، [نسِّقها](رابط مقال مدخل إلى مايكروسوفت إكسل أونلاين).
أولا: إنشاء قاعدة البيانات
سننشئ قاعدة البيانات والتي تتكون من عناوين البيانات (م. والفرع والسنة وإجمالي المبيعات) بالإضافة إلى البيانات، حيث أن قاعدة البيانات في الشكل التالي تقع في النطاق A1:D14.
ثانيا: إنشاء نموذج البحث
سننشئ هنا نموذج البحث الذي من خلاله سنبحث داخل قاعدة البيانات، حيث يمكننا البحث عن الفرع أو السنة أو إجمالي المبيعات. نلاحظ أن عناوين الجدول هي نفسها عناوين جدول البيانات السابق، وسيكون نموذج البحث في النطاق F1:H2.
ثالثا: إنشاء نموذج النتائج
ننشئ نموذج النتائج والذي سنطبِّق فيه دوال قواعد البيانات.
بهذا نكون قد جهَّزنا ورقة العمل التي تحتوي على قاعدة البيانات ونموذج البحث ونموذج النتائج، والذي يتمثَّل في دوال قواعد البيانات، وسنبدأ بتطبيق الدوال. ستكون ورقة العمل بالشكل التالي:
رابعا: تطبيق دوال قواعد البيانات
أدرج دالة من تبويب إدراج، واختر فئة "قاعدة بيانات" للوصول إلى جميع دوال قواعد البيانات.
1. دالة Dcount
عند إدراج الدالة سنلاحظ أن الدالة تحتاج لثلاث قيم هي database وfield وcriteria. سنلاحظ أن هذه القيم الثلاثة هي ذاتها في كل الدوال، ولكن ستختلف قيمة field على حسب الناتج الذي نريده. والقيم الثلاثة هي:
- database: وهي قاعدة البيانات، وفي المثال هي نطاق الخلايا A1:D14 الذي حددناه مسبقًا بما يحتويه من رقم تسلسلي وفرع وسنة وإجمالي مبيعات.
- field: وهو الحقل أو العمود الذي سنبحث عنه أو نوازنه (أي الذي ستستخدمه الدالة). يمكن إدخال تسمية العمود مع تضمينها بين علامتي اقتباس مزدوجتين، مثل "الفرع" أو "السنة"، أو رقم (بدون علامات اقتباس) يمثل موضع العمود في القائمة 1 للعمود الأول، و2 للعمود الثاني، وهكذا. وبما أن دالة Dcount تَحسُب عدد الخلايا التي تحتوي على أرقام، فسنحسب عمود "إجمالي المبيعات".
- criteria: وهي نطاق البحث والذي يمثل نطاق الخلايا التي ستحتوي على الشروط التي نحددها. وفي المثال التالي نطاق البحث هو نطاق الخلايا F1:H2.
سنلاحظ أن الناتج يساوي 13، وهو العدد الكلي للبيانات في الجدول. يمكننا استبدال D1 بـ "إجمالي المبيعات" أو بالرقم 4، وهو رقم عمود "إجمالي المبيعات"، حيث إن عمود "م." هو العمود رقم 1.
سندخل الآن شرطًا في نموذج البحث، ولنفترض أننا نبحث عن عدد الخلايا التي إجمالي المبيعات فيها أكبر من 5000. سنلاحظ أن العدد سيتغير إلى 4، وهو العدد الفعلي للفروع التي إجمالي المبيعات فيها أكثر من 5000.
يمكننا إضافة شروط أخرى، ولنفترض أننا نريد إجمالي المبيعات أكبر من 5000 ولكن فقط لمنطقة "غزة". سنلاحظ تغيُّر القيمة إلى 1 لأنه لا توجد إلَّا قيمة واحدة أعلى من 5000 في إجمالي مبيعات منطقة غزة.
2. دالة Dcounta
هذه الدالة تشبه دالة Dcount، ولكنها تحسب عدد الخلايا التي تحتوي على نصوص. فمثلًا نريد حساب عدد السجلات في عمود الفرع، لهذا سنطبِّق الدالة كالتالي (نلاحظ أن قيمة field هي "الفرع"، بينما قيم criteria وdatabase كما هي).
سنحصل على قيمة 13 وهي عدد السجلات الكلي في حقل الفرع.
ندخل شرطين في نموذج البحث وهما فرع "رفح" وسنة "2019"، أي أنه سيحسب فقط عدد السجلات التي ينطبق عليها الشرطان معًا لا شرط واحد، والآن سندقِّق في قاعدة البيانات وسنلاحظ أن السجل رقم 13 هو الوحيد الذي يتحقق فيه الشرطان (رفح و2019)؛ لذلك فإن نتيجة الدالة هي 1 أي سجل واحد فقط.
3. دالة Daverage
تُرجِع هذه الدالة المتوسط الحسابي لمجموعة من القيم، حيث يمكننا في المثال الحصول على المتوسط الحسابي لإجمالي المبيعات. نلاحظ أن database وcriteria كما هما في الأمثلة السابقة؛ حيث إننا نعمل على نفس قاعدة البيانات ونفس نموذج البحث. سنحدد field بأنه "إجمالي المبيعات" للحصول على متوسط القيم لإجمالي المبيعات.
سنحصل على قيمة 7627.15 وهي قيمة المتوسط الحسابي لجميع قيم إجمالي المبيعات في العمود.
سنخصص البحث للحصول على إجمالي المبيعات للفرع الذي يحتوي على حرفي "غز" وقد تسبقه مجموعة من الحروف وقد تليه مجموعة من الحروف. نعبِّر عن ذلك باستخدام إشارة النجمة (غز)، وبالتالي سيبحث عن "غزة" وعن "شمال غزة".
نلاحظ أن العدد الكلي لنتائج البحث هو 7، والمتوسط الحسابي هو 11121.86 كما في الشكل التالي.
4. دالة Dsum
تُرجِع هذه الدالة مجموع مجموعة من القيم، حيث سنستخدم في هذا المثال القيمة 4 لتحديد العمود الرابع بدلًا من تحديد نص "إجمالي المبيعات"، وكلاهما يعطي نفس النتيجة النهائية. سنلاحظ أن المجموع الكلي لإجمالي المبيعات هو 99153، وبإمكاننا التأكد منه باستخدام دالة sum.
نخصِّص البحث للحصول فقط على مجموع المبيعات التي تقل قيمتها عن 1000، فسنحصل على نتيجة 600 وهي مجموع القيمتين 100 و500 لمنطقتي رفح كما يتضح في الشكل التالي.
5. دالة Dmin
تُرجِع هذه الدالة أصغر قيمة ضمن مجموعة من القيم، ومكننا تطبيق الدالة للحصول على أصغر قيمة في عمود "إجمالي المبيعات". وسنطبِّق الدالة مثلما بالشكل التالي، وسنحصل على قيمة 100.
يمكننا تخصيص البحث للحصول على أقل قيمة في إجمالي المبيعات في سنة 2016 فقط، وسنحصل على قيمة 3000 وهي لمنطقة خان يونس.
6. دالة Dmax
تُرجِع هذه الدالة أكبر قيمة ضمن مجموعة من القيم، ويمكننا تطبيق الدالة للحصول على أكبر قيمة في عمود "إجمالي المبيعات". وسنطبِّق الدالة كما بالشكل التالي وسنحصل على قيمة 65263.
يمكننا تخصيص البحث للحصول على أكبر قيمة في إجمالي المبيعات في سنة 2016 فقط، حيث سنحصل على قيمة 5000 وهي لمنطقة خان يونس. نلاحظ وجود قيمتين لإجمالي المبيعات في 2016 وهما 3000 و5000، وعند الموازنة بينهما للحصول على القيمة الكُبرَى، فسنحصل على القيمة 5000.
7. دالة Dproduct
تُرجِع هذه الدالة حاصل الضرب لمجموعة من القيم التي نحددها. فإذا أردنا الحصول على حاصل ضرب قيم "إجمالي المبيعات" فسنحصل على القيمة التالية.
يمكننا تحديد ثلاثة شروط، وهي: فرع شمال غزة والسنة أقل من 2019، وإجمالي مبيعات أكبر من 500، وحساب حاصل ضرب إجمالي المبيعات للسجلات التي تحقق الشروط الثلاثة. لدينا سجلان يحققان الشروط الثلاثة، وهما سجل رقم 2 وسجل رقم 10، أي سنحسب حاصل ضرب 2500 و1203، وسنحصل على قيمة 3007500.
8. دالة Dget
تُرجِع هذه الدالة سجلًّا واحدًا يحقق الشرط المطلوب. ولهذا سنُدخِل قيمة إجمالي المبيعات، ونطلب من الدالة استرجاع اسم الفرع الذي يحقق هذه القيمة. أي أننا إذًا سنحدد "الفرع" ضمن field في الدالة وهي القيمة التي نريد استرجاعها.
سنُدخِل إجمالي مبيعات 1555 مثلًا، وسنحصل على "غزة"؛ حيث تعود قيمة المبيعات هذه لفرع غزة. يمكننا تغيّير قيمة إجمالي المبيعات وعندها سنلاحظ الفرق في النتيجة.
خاتمة
لقد تعرفنا في هذا الدرس على مجموعة من دوال قواعد البيانات في الإكسل 365 أونلاين. حيث تُستخدم هذه الدوال لتصفية وتلخيص القيم في قواعد البيانات التي تتكون من مجموعة من السجلات والحقول المترابطة مع بعضها البعض. ويمكننا الآن إنشاء قاعدة البيانات على الإكسل؛ ومن ثم إنشاء نموذج البحث واستخدام الدوال المختلفة للحصول على مجموع القيم أو عددها أو الحصول على أكبر قيمة أو أقل قيمة، كما يمكننا حساب المتوسط الحسابي لمجموعة من الأرقام. كل هذه العمليات يمكن إجراؤها ضمن الشروط التي نحددها، وسنحصل على النتيجة بسهولة وسرعة باستخدام الدوال البسيطة.
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.