البحث في الموقع

في المصنفات التي تحتوي على أكثر من ورقة عمل، نحتاج أحيانا إلى الرجوع إلى خلية أو نطاق من الخلايا في أوراق متعددة ذات نمط متشابه عند كتابة صيغة ما. في هذه الحالة يمكننا تجّنب الصيغ المعقدة، أو نسخ الخلايا من ورقة إلى أخرى باستخدام المراجع ثلاثية الأبعاد. البنية العامة للصيغة هي: =Function (first worksheet:last worksheet!cell reference) Function: أي دالة تريد استخدامها، كدالة الجمع، المعدل... إلخ. First worksheet: اسم ورقة العمل الأولى في النطاق. Last worksheet: اسم ورقة العمل الأخيرة في النطاق. Cell reference: اسم الخلية التي تريد تطبيق الدالة عليها في جميع الأوراق ضمن النطاق. وسنوضّح طريقة كتابة الصيغة ثلاثية الأبعاد في الأمثلة التالية: مثال1: في هذا المثال لدينا درجات الاختبارات لمجموعة من الطلاب لأربعة أشهر، وقد تم إدخال درجات كل شهر في ورقة منفصلة (... ,Month1, Month2). المطلوب هو حساب قيمة متوسط الدرجات في ورقة منفصلة (Average): سنقوم أولا بتحديد الخلية التي نريد إظهار نتيجة المتوسط فيها، وهي الخلية B2 في الورقة "Average". ثم سنكتب الصيغة التالية: =average( سننتقل إلى الورقة الأولى في النطاق "Month1"، سنلاحظ في شريط الصيغة إدخال اسم الورقة تلقائيا في الصيغة متبوعا بعلامة تعجب (!)، وهذا يدل على استخدام الورقة الأولى كمرجع: بعدها سنحدد أول قيمة نريد إدخالها في دالة المتوسط، وهي درجة الطالب "زيد ثابت" في الشهر الأول، أي الخلية B2 في الورقة "Month1": ثم سنضغط المفتاح Shift وننقر على آخر ورقة في النطاق "Month4" مع الاستمرار بالضغط، ثم Enter لتطبيق الصيغة: وبذلك تم إيجاد قيمة المتوسط لدرجات اختبارات الطالب الأول للأشهر الأربعة في الورقة "Average" (لاحظ الصيغة في شريط الصيغة). أي ببساطة أننا قمنا بإخبار البرنامج أن يقوم بإيجاد المتوسط لجميع القيم في الموقع B2 من الورقة الأولى حتى الورقة الأخيرة في النطاق. بعد ذلك سنقوم باستخدام التعبئة التلقائية لتطبيق الصيغة على بقية الخلايا في الجدول: من فوائد المراجع ثلاثية الأبعاد هي أنّ قيمة المتوسط ستتغير تبعا لأي تحديث نجريه على أي ورقة من أوراق النطاق. ولكي تتوضح الخطوات السابقة أكثر، شاهد الصورة أدناه: مثال2: هذا المثال يحتوي على ثلاثة أوراق. تحتوي الورقة الأولى "الفرع الأول" على مبيعات الفرع الأول لأحد المتاجر لمجموعة من المنتجات، الورقة الثانية "الفرع الثاني" على مبيعات الفرع الثاني... وهكذا. المطلوب هو إيجاد مجموع المبيعات لكل منتج لكل فرع وللأرباع الأربع: سنقوم أولا بتحديد الخلية التي سيظهر فيها ناتج المجموع للمنتج الأول، وهي B4 في الورقة "المجموع"، ثم سنستخدم دالة الجمع بالصيغة التالية: =sum( ثم سننتقل إلى الورقة الأولى "الفرع الأول" ونحدد الخلية B4: نضغط المفتاح Shift ونحدد الورقة الثالثة "الفرع الثالث" مع الاستمرار بالضغط ليتحدد جميع نطاق الأوراق، ثم نضغط Enter لإيجاد النتيجة: وسنستخدم التعبئة التلقائية لتطبيق الصيغة على جميع الخلايا في الجدول. الصورة أدناه توضح خطوات هذا المثال من البداية: فيما يلي مجموعة الدوال التي تدعم استخدام المراجع ثلاثية الأبعاد في اكسل: SUM: لإيجاد مجموع مجموعة من القيم الرقمية AVERAGE: لإيجاد متوسط مجموعة من القيم الرقمية AVERAGEA: لإيجاد متوسط مجموعة من القيم الرقمية، النصية، والمنطقية COUNT: لحساب عدد الخلايا التي تحتوي على أرقام COUNTA: لحساب عدد الخلايا غير الفارغة MAX: لإرجاع أكبر قيمة ضمن مجموعة من الخلايا MAXA: لإرجاع أكبر قيمة ضمن مجموعة من الخلايا، بما فيها النصوص والقيم المنطقية. MIN: لإرجاع أصغر قيمة ضمن مجموعة من الخلايا MINA: لإرجاع أصغر قيمة ضمن مجموعة من الخلايا، بما فيها النصوص والقيم المنطقية. PRODUCT: لإيجاد حاصل ضرب مجموعة من القيم الرقمية STDEV ،STDEVA ،STDEVP ،STDEVPA: لحساب الانحراف المعياري لمجموعة من القيم المحددة استنادا إلى عينة أو لمحتوى بأكمله. VAR ،VARA ،VARP ،VARPA: لإرجاع قيمة التباين لمجموعة من القيم المحدد استنادا إلى عينة أو لمحتوى بأكمله.

سنتطرّق في هذا الدّرس إلى آلية حماية المصنفات وأوراق العمل في إكسل بكلمة سرية لمنع الآخرين من إجراء التغييرات عليها. أحيانا تحتوي المصنفات على بيانات خاصة أو حساسة يفضل حمايتها بإضافة كلمة سرية للمصنف لمنع المستخدمين من الاطلاع على محتوياته أو تغييرها. وبذلك لا يمكن فتح المصنف إلا بإدخال الكلمة السرية. لتأمين المصنف بكلمة سرية انقر: File > Protect Workbook > Encrypt with Password بعدها قم بإدخال الكلمة السرية، انقر موافق، قم بإعادة كتابة الكلمة السرية ثم موافق: بعد إدخال الكلمة السرية احفظ الملف (Ctrl+S). عند فتح الملف مجددا سيطلب منك إدخال الكلمة السرية. احفظ الكلمة السرية في مكان آمن، لأنك لن تتمكن من استعادتها عند نسيانها، ولن تتمكن من فتح المصنف. بهذه الطريقة لا يمكن للأشخاص الآخرين فتح المصنف. أما إذا كنت لا تمانع أن يقوم الآخرين بفتح المصنف وقراءة محتوياته لكن دون أن يتمكنوا من إجراء تغييرات عليه كالحذف، الإدراج، التنسيق، إلخ، يمكنك فعل ذلك بحماية الورقة. انقر على الورقة التي تريد حمياتها بزر الفأرة الأيمن وانقر حماية الورقة Protect Sheet: ابق الخيارين الأولين مؤشرين، وقم بتأشير الخيارات التي تريد تمكين المستخدمين من تغييرها إن رغبت في ذلك، أدخل الكلمة السرية في الحقل الخاص بها، ثم انقر موافق: بعد حماية الورقة احفظ المصنف (Ctrl+S)، وبذلك ستظهر رسالة خطأ عندما يحاول أحدهم التعديل على محتويات الورقة: ومع أنّ محتويات الورقة المحمية لا يمكن التعديل عليها، إلّا أنه بالإمكان نسخ محتوياتها ونقلها إلى ورقة أخرى اعتمادا على كيفية حماية الورقة الأصلية، وبذلك يصبح التعديل عليها ممكنا. كما بإمكان الآخرين حذف الورقة، نقلها، تسميتها، إلخ. والسبب هو أنّ المصنف غير محمي بالكامل، وإنما الورقة المحددة فقط. كما بإمكان أي شخص يملك الكلمة السرية إلغاء حماية الورقة: ولمنع الآخرين من حذف وإضافة الأوراق، إظهارها وإخفاءها، أو تسميتها، استخدم خيار حماية بنية المصنف. انقر: File > Protect Workbook > Protect Workbook Structure ادخل الكلمة السرية مرتين وانقر موافق. وبذلك ستصبح أغلب خيارات الورقة غير مفعلة عند النقر على اسم الورقة بزر الفأرة الأيمن. لإلغاء حماية بنية المصنف، انقر Protect Workbook من تبويب مراجعة Review، ثم اكتب الكلمة السرية وانقر موافق. لقد قمنا في خطوات سابقة بحماية ورقة العمل، وبذلك أصبح من غير الممكن تغيير محتويات جميع الخلايا في الورقة. قد ترغب في بعض الأحيان في حماية عدد محدد من الخلايا المتفرقة أو المتجاورة، لكن ليس جميع الخلايا في الورقة. لفعل ذلك يجب أن تقوم بتغيير خيار حماية الورقة الافتراضي. انقر على أيقونة المثلث في زاوية الورقة، عند تقاطع عناوين الصفوف وعناوين الأعمدة لتحديد الورقة: انقر بزر الفأرة الأيمن واختر تنسيق الخلايا Format Cells: من تبويب حماية Protection قم بإلغاء تحديد الخيار تم تأمينه Locked: الخطوة التالية هي تحديد الخلايا التي تريد حمايتها، حدد نطاق من الخلايا إذا كانت متجاورة، وإن لم تكن متجاورة اضغط المفتاح Ctrl وحدد الخلايا مع الاستمرار بالضغط. في المثال أدناه قمت بتحديد نطاق الخلايا E4:E6 والخلايا G5 وH6 لتأمينها: بعد تحديد الخلايا، انقر بزر الفأرة الأيمن واختر تنسيق الخلايا Format Cells: من تبويب حماية Protection قم بتحديد الخيار تم تأمينه Locked ثم انقر موافق: بذلك قمنا بإلغاء تأمين جميع الخلايا في الورقة ماعدا الخلايا المحددة. ولذلك عندما نقوم بحماية الورقة في الخطوة التالية سيتم حماية الخلايا المؤمَنة فقط. انقر بزر الفأرة الأيمن على اسم الورقة الحالية، واختر حماية الورقة Protect Sheet: ادخل الكلمة السرية وحدد الخيارات التي ترغب في تمكين المستخدمين من تغييرها، كتنسيق الخلايا مثلا، ثم انقر موافق. هذه الطريقة ستمكن المستخدمين من تعديل محتويات جميع الخلايا في الورقة باستثناء الخلايا المؤمّنة والمحمية. لإلغاء حماية الورقة انقر بزر الفأرة الأيمن على اسم الورقة واختر إلغاء حماية الورقة Unprotect Sheet: ادخل الكلمة السرية وانقر موافق. وللعودة إلى خيار تأمين كامل الورقة، قم بتحديد من أيقونة المثلث في زاوية الورقة كما فعلنا سابقا. انقر بزر الفأرة الأيمن واختر تنسيق الخلايا Format Cells. من تبويب حماية Protection قم بإعادة تحديد الخيار تم تأمينه Locked ثم انقر موافق: يمكنك تغيير الكلمة السرية لحماية الورقة بإلغاء الحماية، ثم حمايتها مجددا بإدخال كلمة سرية جديدة. لحد الآن قمنا بحماية ورقة بأكملها، خلايا محددة في الورقة، حماية بنية المصنف، وحماية المصنف بحيث لا يمكن فتحه دون إدخال الكلمة السرية. يمكنك أيضا حماية المصنف بحيث يمكن فتحه واستعراضه (لكن لا يمكن التعديل عليه) دون إدخال الكلمة السرية. اذهب إلى: File > Save As اكتب اسم المصنف ثم اختر General Options من زر Tools: في مربع الحوار الذي سيظهر، أدخل الكلمة السرية في حقل Password to modify: قم بإدخال الكلمة السرية مرة أخرى، انقر موافق واحفظ الملف. عند فتح المصنف في المرة القادمة سيطلب إدخال الكلمة السرية لفتحه وإجراء التغييرات، أو بالإمكان استخدام طريقة عرض القراءة Read Only لعرضه وقراءته: لإلغاء الكلمة السرية هذه، اتبع نفس طريقة إنشائها، لكن احذف الكلمة السرية من حقل Password to modify، انقر موافق واحفظ المصنف باسم جديد. لإلغاء حماية المصنف اذهب إلى File واختر Encrypt with Password من زر Protect Workbook. احذف الكلمة السرية من الحقل الخاص بها وانقر موافق. ولتغيير الكلمة السرية لحماية المصنف، قم بإلغاء الحماية، ثم قم بحمايته مجددا بإدخال كلمة سرية جديدة.