النطاق الديناميكي مع التحجيم التلقائي

هل لديك جداول بها بيانات في Excel يمكن تغيير حجمها ، أي عدد الصفوف (الأعمدة) التي يمكن أن تزيد أو تنقص في سياق العمل؟ إذا كانت أحجام الجدول "عائمة" ، فسيتعين عليك مراقبة هذه اللحظة باستمرار وتصحيحها:

• الروابط الموجودة في صيغ التقارير التي تشير إلى طاولتنا
• النطاقات الأولية للجداول المحورية التي تم إنشاؤها وفقًا لجدولنا
• النطاقات الأولية للمخططات المبنية وفقًا لجدولنا
• نطاقات القوائم المنسدلة التي تستخدم جدولنا كمصدر بيانات

كل هذا في المجموع لن يسمح لك بالملل 😉

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

الطريقة 1. الجدول الذكي

قم بتمييز نطاق الخلايا الخاص بك وحدد من علامة التبويب الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول):

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

يمكننا الآن استخدام الروابط الديناميكية إلى "جدولنا الذكي":

• الجدول 1 - الارتباط بالجدول بأكمله باستثناء صف الرأس (A2: D5)
• جدول 1 [# الكل] - ارتباط بالجدول بأكمله (A1: D5)
• جدول 1 [بيتر] - مرجع إلى عمود النطاق بدون رأس الخلية الأول (C2: C5)
• جدول 1 [#Headers] - ارتباط بـ "الرأس" بأسماء الأعمدة (A1: D1)

تعمل مثل هذه المراجع بشكل رائع في الصيغ ، على سبيل المثال:

= SUM (جدول 1 [موسكو]) - حساب مجموع عمود "موسكو"

or

= VPR (F5 ؛الجدول 1؛ 3 ؛ 0) - ابحث في الجدول عن الشهر من الخلية F5 وأصدر مجموع سانت بطرسبرغ لذلك (ما هو VLOOKUP؟)

يمكن استخدام هذه الروابط بنجاح عند إنشاء جداول محورية عن طريق التحديد في علامة التبويب إدراج - جدول محوري (إدراج - جدول محوري) وإدخال اسم الجدول الذكي كمصدر بيانات:

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

عند إنشاء قوائم منسدلة ، لا يمكن استخدام الروابط المباشرة لعناصر الجدول الذكي ، ولكن يمكنك بسهولة تجاوز هذا القيد باستخدام خدعة تكتيكية - استخدم الوظيفة غير مباشر (غير مباشر)، والذي يحول النص إلى رابط:

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

الطريقة 2: النطاق المسمى الديناميكي

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

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

سنحتاج إلى وظيفتين مضمنتين في Excel متوفرة في أي إصدار - بويكبوز (مباراة) لتحديد الخلية الأخيرة في النطاق ، و INDEX (فهرس) لإنشاء ارتباط ديناميكي.

البحث عن الخلية الأخيرة باستخدام MATCH

MATCH (lookup_value ، range ، match_type) - دالة تبحث عن قيمة معينة في نطاق (صف أو عمود) وتعيد الرقم الترتيبي للخلية التي تم العثور عليها فيها. على سبيل المثال ، ستُرجع الصيغة MATCH ("March" ؛ A1: A5 ؛ 0) الرقم 4 نتيجةً لذلك ، لأن الكلمة "March" تقع في الخلية الرابعة في العمود A1: A5. الوسيطة الأخيرة للدالة Match_Type = 0 تعني أننا نبحث عن تطابق تام. إذا لم يتم تحديد هذه الوسيطة ، فستنتقل الوظيفة إلى وضع البحث لأقرب قيمة أصغر - وهذا هو بالضبط ما يمكن استخدامه بنجاح للعثور على آخر خلية مشغولة في صفيفنا.

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

إذا كانت هناك أرقام فقط في المصفوفة الخاصة بنا ، فيمكننا تحديد رقم كقيمة مرغوبة ، والتي من الواضح أنها أكبر من أي من الأرقام الموجودة في الجدول:

للحصول على ضمان ، يمكنك استخدام الرقم 9E + 307 (9 مرات 10 أس 307 ، أي 9 مع 307 أصفار) - أقصى رقم يمكن لبرنامج Excel العمل به من حيث المبدأ.

إذا كانت هناك قيم نصية في العمود ، فعندئذٍ كمكافئ لأكبر رقم ممكن ، يمكنك إدخال الإنشاء REPEAT ("i" ، 255) - سلسلة نصية تتكون من 255 حرفًا "i" - الحرف الأخير من الأبجدية. نظرًا لأن Excel يقارن فعليًا رموز الأحرف عند البحث ، فإن أي نص في جدولنا سيكون من الناحية الفنية "أصغر" من سطر "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":

قم بإنشاء ارتباط باستخدام INDEX

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

INDEX (النطاق ؛ row_num ؛ column_num)

يعطي محتويات الخلية من النطاق حسب الصف ورقم العمود ، على سبيل المثال ، الوظيفة = INDEX (A1: D5 ؛ 3 ؛ 4) في جدولنا مع المدن والأشهر من الطريقة السابقة ستعطي 1240 - المحتوى من الصف الثالث والعمود الرابع ، أي الخلايا D3. إذا كان هناك عمود واحد فقط ، فيمكن حذف رقمه ، أي أن الصيغة INDEX (A4: A3 ؛ 2) ستعطي "Samara" في لقطة الشاشة الأخيرة.

وهناك فارق بسيط واحد غير واضح تمامًا: إذا لم يتم إدخال INDEX فقط في الخلية بعد علامة = ، كالمعتاد ، ولكن تم استخدامه باعتباره الجزء الأخير من المرجع إلى النطاق بعد النقطتين ، فإنه لم يعد يعطي محتويات الخلية ولكن عنوانها! وبالتالي ، فإن صيغة مثل $ A $ 2: INDEX ($ A $ 2: $ A $ 100 ؛ 3) ستعطي إشارة إلى النطاق A2: A4 عند الإخراج.

وهذا هو المكان الذي تأتي فيه وظيفة MATCH ، والتي نقوم بإدخالها داخل INDEX لتحديد نهاية القائمة ديناميكيًا:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100 ؛ MATCH (REP ("I"؛ 255)؛ A2: A100))

قم بإنشاء نطاق مسمى

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

يبقى أن تضغط OK ويمكن استخدام النطاق الجاهز في أي صيغ أو قوائم منسدلة أو مخططات.

• استخدام الدالة VLOOKUP لربط الجداول وقيم البحث
• كيفية إنشاء قائمة منسدلة يتم ملؤها تلقائيًا
• كيفية إنشاء جدول محوري لتحليل كمية كبيرة من البيانات