كيفية تعيين مجموعة من القيم في Excel

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

تغيير نطاقات الخلايا تلقائيًا في Excel

تتمثل ميزة النطاقات التلقائية في Excel في أنها تجعل استخدام الصيغ أسهل بكثير. بالإضافة إلى ذلك ، فإنها تجعل من الممكن تبسيط تحليل البيانات المعقدة التي تحتوي على عدد كبير من الصيغ ، والتي تتضمن العديد من الوظائف ، بشكل ملحوظ. يمكنك تسمية هذا النطاق ، وبعد ذلك سيتم تحديثه تلقائيًا اعتمادًا على البيانات التي يحتوي عليها.

كيفية إجراء تغيير تلقائي للنطاق في Excel

افترض أنك مستثمر وتحتاج إلى الاستثمار في شيء ما. نتيجة لذلك ، نريد الحصول على معلومات حول المبلغ الذي يمكنك كسبه إجمالاً طوال الوقت الذي ستعمل فيه الأموال لهذا المشروع. ومع ذلك ، من أجل الحصول على هذه المعلومات ، نحتاج إلى مراقبة مقدار الربح الإجمالي الذي يجلبه هذا الكائن إلينا بانتظام. قم بعمل نفس التقرير كما في لقطة الشاشة هذه.

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

1. إذا تم حل المشكلة بهذه الطريقة ، فلن يكون من الممكن استخدام الخلايا المدرجة في العمود B لأغراض أخرى.
2. سيستهلك هذا الجدول الكثير من ذاكرة الوصول العشوائي ، مما يجعل من المستحيل استخدام المستند على أجهزة الكمبيوتر الضعيفة.

وبالتالي ، من الضروري حل هذه المشكلة من خلال الأسماء الديناميكية. لإنشائها ، يجب عليك تنفيذ التسلسل التالي من الإجراءات:

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

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

دالة OFFSET في Excel

لنلقِ نظرة على الدوال التي سجلناها في حقل "النطاق" سابقًا. استخدام الوظيفة تصرف يمكننا تحديد مقدار النطاق بالنظر إلى عدد الخلايا في العمود B المملوءة. وسيطات الدالة كما يلي:

1. خلية البداية. باستخدام هذه الوسيطة ، يمكن للمستخدم الإشارة إلى الخلية الموجودة في النطاق التي سيتم اعتبارها أعلى اليسار. سوف يقدم تقريرا إلى أسفل وإلى اليمين.
2. نطاق الإزاحة بالصفوف. باستخدام هذا النطاق ، قمنا بتعيين عدد الخلايا التي يجب أن يحدث بها الإزاحة من أعلى الخلية اليسرى من النطاق. لا يمكنك استخدام القيم الموجبة فحسب ، بل استخدام الصفر والسالب. في هذه الحالة ، قد لا يحدث الإزاحة على الإطلاق ، أو سيتم تنفيذها في الاتجاه المعاكس.
3. نطاق تعويض الأعمدة. تشبه هذه المعلمة المعلمة السابقة ، فهي فقط تسمح لك بضبط درجة التحول الأفقي للنطاق. هنا يمكنك أيضًا استخدام كل من القيم الصفرية والسالبة.
4. مقدار النطاق في الارتفاع. في الواقع ، فإن عنوان هذه الحجة يوضح لنا ما تعنيه. هذا هو عدد الخلايا التي يجب أن يزداد النطاق بواسطتها.
5. قيمة النطاق في العرض. الحجة مشابهة للحجة السابقة ، إلا أنها تتعلق بالأعمدة.

لا تحتاج إلى تحديد آخر وسيطتين إذا لم تكن بحاجة إلى ذلك. في هذه الحالة ، ستكون قيمة النطاق خلية واحدة فقط. على سبيل المثال ، إذا قمت بتحديد الصيغة = OFFSET (A1 ؛ 0 ؛ 0)، ستشير هذه الصيغة إلى نفس الخلية مثل تلك الموجودة في الوسيطة الأولى. إذا تم تعيين الإزاحة الرأسية على وحدتين ، فستشير الخلية في هذه الحالة إلى الخلية A2. الآن دعنا نصف بالتفصيل ما تعنيه الوظيفة التحقق.

وظيفة العد في Excel

باستخدام الوظيفة التحقق نحدد عدد الخلايا في العمود B في المجموع. أي باستخدام وظيفتين ، نحدد عدد الخلايا التي يتم ملؤها في النطاق ، وبناءً على المعلومات التي يتم تلقيها ، نحدد حجم النطاق. لذلك ستكون الصيغة النهائية كما يلي: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

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

وهكذا ، باستخدام الوظيفة التحقق يمكن للمستخدم استخدام الذاكرة بأكبر قدر ممكن من الكفاءة عن طريق تحميل تلك الخلايا التي تحتوي على بعض القيم فقط. وفقًا لذلك ، لن تكون هناك أخطاء إضافية في العمل مرتبطة بالأداء الضعيف للكمبيوتر الذي سيعمل عليه جدول البيانات.

وفقًا لذلك ، من أجل تحديد حجم النطاق اعتمادًا على عدد الأعمدة ، تحتاج إلى تنفيذ تسلسل مماثل من الإجراءات ، فقط في هذه الحالة تحتاج إلى تحديد الوحدة في المعلمة الثالثة ، والصيغة في المعلمة الرابعة التحقق.

نرى أنه بمساعدة صيغ Excel ، لا يمكنك فقط أتمتة العمليات الحسابية. هذه مجرد قطرة في محيط ، لكنها في الحقيقة تسمح لك بأتمتة أي عملية تخطر على بالك.

مخططات ديناميكية في Excel

لذلك ، في الخطوة الأخيرة ، تمكنا من إنشاء نطاق ديناميكي ، يعتمد حجمه تمامًا على عدد الخلايا المملوءة التي يحتوي عليها. الآن ، بناءً على هذه البيانات ، يمكنك إنشاء مخططات ديناميكية ستتغير تلقائيًا بمجرد قيام المستخدم بإجراء أي تغييرات أو إضافة عمود أو صف إضافي. تسلسل الإجراءات في هذه الحالة هو كما يلي:

1. نختار النطاق الخاص بنا ، وبعد ذلك نقوم بإدراج مخطط من نوع "المدرج التكراري مع التجميع". يمكنك العثور على هذا العنصر في قسم "إدراج" في قسم "الرسوم البيانية - المدرج التكراري".
2. نقوم بالنقر بالماوس الأيسر على عمود عشوائي من المدرج التكراري ، وبعد ذلك ستظهر الوظيفة = SERIES () في سطر الوظيفة. في لقطة الشاشة يمكنك رؤية الصيغة التفصيلية. 
3. بعد ذلك ، يجب إجراء بعض التغييرات على الصيغة. يجب استبدال النطاق بعد "الورقة 1!" لاسم النطاق. سينتج عن ذلك الوظيفة التالية: = ROW (Sheet1! $ B $ 1 ؛؛ Sheet1! الدخل ؛ 1)
4. يبقى الآن إضافة سجل جديد إلى التقرير للتحقق مما إذا كان المخطط قد تم تحديثه تلقائيًا أم لا.

لنلق نظرة على الشكل.

دعونا نلخص كيف فعلنا ذلك. في الخطوة السابقة ، أنشأنا نطاقًا ديناميكيًا ، يعتمد حجمه على عدد العناصر التي يحتوي عليها. للقيام بذلك ، استخدمنا مجموعة من الوظائف التحقق и تصرف. صنعنا هذا النطاق المسمى ، ثم استخدمنا الإشارة إلى هذا الاسم كنطاق للرسم البياني الخاص بنا. إن النطاق المحدد الذي يجب اختياره كمصدر بيانات في المرحلة الأولى ليس مهمًا للغاية. الشيء الرئيسي هو استبداله باسم النطاق لاحقًا. بهذه الطريقة يمكنك توفير الكثير من ذاكرة الوصول العشوائي.

النطاقات المسماة واستخداماتها

دعنا نتحدث الآن بمزيد من التفاصيل حول كيفية إنشاء نطاقات مسماة بشكل صحيح واستخدامها لأداء المهام التي تم تعيينها لمستخدم Excel.

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

كل شيء يبدأ بتسمية الخلية. للقيام بذلك ، ما عليك سوى تحديده ، ثم كتابة الاسم الذي نحتاجه في حقل اسمه. من المهم أن يكون من السهل تذكرها. هناك بعض القيود التي يجب مراعاتها عند التسمية:

1. الحد الأقصى للطول هو 255 حرفًا. هذا يكفي تمامًا لتعيين اسم يرغب فيه قلبك.
2. يجب ألا يحتوي الاسم على مسافات. لذلك ، إذا كانت تحتوي على عدة كلمات ، فمن الممكن فصلها باستخدام حرف الشرطة السفلية.

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

الخطوة التالية هي إنشاء نطاق مسمى. الإجراء هو نفسه في الأساس. تحتاج أولاً إلى تحديد نطاق ، ثم تحديد اسمه. بعد ذلك ، يمكن استخدام هذا الاسم في جميع عمليات البيانات الأخرى في Excel. على سبيل المثال ، غالبًا ما تُستخدم النطاقات المسماة لتحديد مجموع القيم.

بالإضافة إلى ذلك ، من الممكن إنشاء نطاق مسمى باستخدام علامة التبويب الصيغ باستخدام أداة Set Name. بعد تحديده ، ستظهر نافذة حيث نحتاج إلى اختيار اسم لمجموعتنا ، وكذلك تحديد المنطقة التي سيتم تمديدها يدويًا. يمكنك أيضًا تحديد المكان الذي سيعمل فيه هذا النطاق: داخل ورقة واحدة أو في جميع أنحاء الكتاب.

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

يجب أن يؤخذ في الاعتبار أنه عند استخدام النطاقات المسماة في الصيغ ، ثم بعد حذفها ، لن يتم استبدال الصيغ تلقائيًا بالقيم الصحيحة. لذلك ، قد تحدث أخطاء. لذلك ، قبل حذف نطاق مسمى ، تحتاج إلى التأكد من عدم استخدامه في أي من الصيغ.

هناك طريقة أخرى لإنشاء نطاق مسمى وهي الحصول عليه من جدول. للقيام بذلك ، هناك أداة خاصة تسمى "إنشاء من التحديد". كما نفهم ، من أجل استخدامه ، يجب عليك أولاً تحديد النطاق الذي سنقوم بتحريره ، ثم تعيين المكان الذي لدينا فيه العناوين. نتيجة لذلك ، بناءً على هذه البيانات ، سيعالج Excel تلقائيًا جميع البيانات ، وسيتم تعيين العناوين تلقائيًا.

إذا كان العنوان يحتوي على عدة كلمات ، فسيقوم Excel تلقائيًا بفصلها بشرطة سفلية.

وبالتالي ، اكتشفنا كيفية إنشاء نطاقات مسماة ديناميكية وكيف تسمح لك بأتمتة العمل باستخدام كميات كبيرة من البيانات. كما ترى ، يكفي استخدام العديد من الوظائف وأدوات البرنامج المضمنة في الوظيفة. لا يوجد شيء معقد على الإطلاق ، على الرغم من أنه قد يبدو كذلك للمبتدئين للوهلة الأولى.