اذهب إلى المحتوى

إنشاء تقرير مبيعات – دمج التوابع، الحماية والتنسيق الشرطي


جميل بيلوني

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

دمج مجموعة من التوابع

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

التابع طريقة الاستعمال الوصف
IFERROR IFERROR(value, [value_if_error]) يُرجع هذا التابع القيمة الأولى إن لم تكن خطأً، وإن كانت خطأ فيرجع القيمة الثانية إن كانت موجودة أو يرجع قيمة فارغة. يُستعمل للتحكم في ظهور الأخطاء في الخلايا.
IF IF(logical_expression, value_if_true, value_if_false) يُرجع القيمة الأولى (value if true) إن تحقَّق التعبير المنطقي (logical expression) ويرجع القيمة الثانية إن لم يتحقق.
ISNA ISNA(value) يُستعمل للتحقُّق إن كانت القيمة (value) هي خطأ ذو الرمز "#N/A" أي غير متاح أو متوافر (not available) أو لا يتوافر جواب للعمليَّة، ويُرجع العبارة "true" إن كانت القيمة هي خطأ من النوع السابق أو يُرجع العبارة "false" إن لم تكن خطأ.
VLOOKUP VLOOKUP(search_key, range, index, [is_sorted])

يُستخدم للبحث العمودي، ومُدخلات هذا التابع هي:

  • search_key: القيمة التي نبحث عنها.
  • Range: المجال الذي نبحث فيه.
  • Index: رقم الحقل الذي نريد جلب القيمة المقابلة للقيمة التي نبحث عنها في العمود الأول من المجال المحدَّد.
  • is_sorted: إن كانت القيم في المجال المحدَّد مرتَّبة نضع "true" وإن لم تكن مرتَّبة نضع "false".

قبل استعمال التابع دعنا نفعل شيئًا يسِّهل عملنا؛ سننتقل إلى ورق المخزون ونحدِّد جدول المخزون أي عمود رقم المنتج والوصف ثمَّ نضغط بزر الفأرة الأيمن ونختار “تعريف النطاق المحدَّد” ونعطي للنطاق المحدَّد عنوان “المنتجات” ثمَّ نضغط على “تم“؛ إن أردنا استعمال النطاق السابق في التوابع أو أي مكان فما علينا سوى كتابة اسمه. مزيَّة عنونة النطاق هي بقاء النطاق ثابت مهما تحركت الخليَّة أو نُسخت إلى مكان آخر في الجدول إذ إن لم يُثبَّت النطاق فسيتغير نسبة إلى تغير عنوان الخليَّة.

define-named-range.png

نُدرج التوابع السابقة في الخليَّة E8 للبحث عن الوصف المقابل لرقم المنتج المُدخل في الخليَّة D8 في جدول المخزون وذلك كما موضح في الصورة.

insert-functions.png

استعملنا أولًا التابع IFERROR لإظهار الرسالة التي نريدها إن وُجد خطأٌ ما لتجنب ظهور رموز لا نرغب بها؛ وضعنا في الدخل الأول التابع IF والدخل الثاني الرسالة “لم يُعثر على المنتج” لإظهارها في حال وجود أي خطأ. القيمة المنطقية للتابع IF هي التابع ISNA مع التابع VLOOKUP للتأكد من وجود القيمة التي نبحث عنا؛ إذا لم يجد التابع VLOOKUP ما يبحث عنه فسيُرجع الخطأ ‎#N/A وحينئذٍ سيُرجع التابع ISNA العبارة true أي يوجد خطأ وبالتالي يأخذ التابع IF القيمة الأولى وهي “لم يُعثر على المنتج”. أمَّا إن وجد التابع VLOOKUP القيمة التي يبحث عنها فسيرجع التابع ISNA القيمة false أي لا يوجد خطأ وبالتالي سيختار التابع IF القيمة الثانية وهي التابع VLOOKUP أي القيمة التي يرجعها هذا التابع وهي التي نبحث عنها في جدول المخزون.
لاحظ أنَّنا استعملنا عبارة “المنتجات” للدلالة على عنوان جدول المخزون الذي نودُّ البحث فيه ويمكن استعمال النطاق مباشرةً مع الضغط على F4 لتثبيت عنوان النطاق.
نسحب الخليَّة E8 إلى الخلايا السفليَّة لنسخ العلاقة وستكون النتيجة كما موضح بالصورة.

check-functions-working.png

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

حماية الأوراق والخلايا

انتهينا من إنشاء تقرير المبيعات وأصبح جاهزًا للاستخدام. سنشارك هذا الجدول مع العامل ليُدخل بيانات المنتجات التي يبيعها ولكن ماذا لو غيَّر قيم خلايا لا يحق له تغييرها مثل تغيير قيم جدول المخزون أو تغيير محتوى خليَّة الوصف التي تعذبنا كثيرًا حتى أنشأناها أو في محتوى الخلايا الأخرى بقصد أو بغير قصد؟! سنضطر هنا إلى حماية الأوراق أو الخلايا من عبث الأشخاص الذين نشاركهم ملفاتنا وسنعطي كلَّ شخص الصلاحيات المناسبة للتعديل على الخلايا والأوراق.
نضغط على السهم الموجود بجانب ورقة المخزون ونختار “حماية الورقة” أو نختار من قائمة البيانات ← الأوراق والنطاقات المحميَّة وستظهر قائمة في الطرف الأيسر.

protected-sheets-and-ranges.png

نضع وصفًا لحماية الورقة ثمَّ نحدِّد إن كنا نريد حماية الورقة بأكملها أو نطاق محدَّد ضمن الورقة ثمَّ نحدِّد اسم الورقة وإن كنا نريد أن نستثني بعض الخلايا ليتاح تعديلها ثمَّ نضغط على “تعيين الأذونات“.
يظهر لدينا خيارين؛ الخيار الأول هو إظهار تحذير عند تعديل النطاق أو الورقة المحميَّة إذ تظهر رسالة تنبهك إلى أنَّه لا يفترض تعديل هذا النطاق وإن كنت واثقًا من التعديل فاضغط موافق سواءً لك أو لمن تشاركهم الملف.

warning-message-when-editing-protected-range.png

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

range-editing-permissions.png

عند حماية الورقة أو الخلايا لن يتمكن الآخرون من التعديل عليها ولكن يستطيعون رؤيتها بحسب الإذن المعطى لهم.
يمكن حماية حقول الوصف ونسبة ومقدار الضريبة والإجمالي في جدول بيانات المبيعات حتى لا يعبث أحدٌ بها فنحصل على معلومات خاطئة بالطريقة نفسها ولكن نختار “حماية نطاق” بدلًا من ورقة.
فكرة إضافية: يمكن في جدول المخزون وضع سعر لكل قطعة وعندما نختار رقم المنتج يوضع سعرها في حقل بيانات المبيعات مباشرةً مثل حقل الوصف وهنا نحتاج إلى حقل يصف عدد المنتجات المُباعة وبذلك نحمي سعر المنتج ويتبقى إدخال التاريخ والوقت ورقم المنتج وعدد القطع المُباعة فقط.

التنسيق الشرطي

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

conditional-formatting.png

يوفر تطبيق جداول بيانات google نوعين من التنسيق هما: لون فردي وتدرج ألوان؛ سنُنسق حقل مقدار الضريبة باللون الفردي لتحديد قيمة الضريبة الأقل من 200 ليرة. نحدِّد حقل مقدار الضريبة ونضغط بزر الفأرة الأيمن ونختار “تنسيق شرطي” أو من قائمة التنسيق ← تنسيق شرطي ثمَّ نختار قاعدة “أصغر من أو يساوي” ثمَّ نضع القيمة 200 ونحدِّد تنسيق الخليَّة ونضغط “تم“.

single-color-conditional-formatting.png

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

color-scale-conditional-formatting.png

توجد قواعد كثيرة في التنسيق الشرطي تنتظرك لاستكشافها والتي تسهِّل عليك تنسيق جدولك وتحليل البيانات وقراءتها بسرعة.

الخاتمة

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


تفاعل الأعضاء

أفضل التعليقات

السلام عليكم ورحمة الله 
اخي الكريم بدايتاً اشكرك على هذه المقالة المفيدا جدا 
وبالنسبة الى التنسيق الشرطي هل لديك معلومات اضافية يمكن افادتنا فيها ؟
على سبيل المثال اريد تلوين الصف بالكامل باللون الاحمر عندما يكون الطالب راسب مثلا وباللون الاخضر عندما يكون الطالب ناجح ولكن عند تطبيق الخطوات يلون فقط الخلية التي تحوي على تلك الكلمة بدلا من الصف بالكامل ؟
نرجو الافادة ولكم جزيل الشكر والامتنان 

رابط هذا التعليق
شارك على الشبكات الإجتماعية



انضم إلى النقاش

يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.

زائر
أضف تعليق

×   لقد أضفت محتوى بخط أو تنسيق مختلف.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   جرى استعادة المحتوى السابق..   امسح المحرر

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • أضف...