الاستبدال "من العدم"

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

حدد وظيفة

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

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

مصفوفة الثوابت في الصيغة

لنفترض أن لدينا قائمة بالمدن ، حيث يتم استخدام الوظيفة VPR (VLOOKUP) يتم استبدال قيم معاملات الراتب من العمود الثاني من الجدول الأصفر على اليمين:

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

حدد أي خلية فارغة. أدخل علامة المساواة من لوحة المفاتيح وحدد النطاق مع الجدول - يجب عرض عنوانه في شريط الصيغة:

حدد بالماوس الرابط E3: F5 في شريط الصيغة واضغط على المفتاح F9 - سيتحول الارتباط إلى مصفوفة من الثوابت:

يبقى نسخ المصفوفة الناتجة ولصقها في صيغتنا باستخدام VLOOKUP ، وحذف الجدول نفسه على أنه غير ضروري:

مصفوفة من الثوابت المسماة

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

صحافة OK وإغلاق مدير الاسم. الآن يمكن استخدام الاسم المضاف بأمان على أي ورقة من الكتاب بأي صيغة - على سبيل المثال ، في وظيفة VLOOKUP الخاصة بنا:

مدمجة وجميلة ، وبمعنى ما ، تحميها من الأيدي المرحة لغير المحترفين

• كيفية استخدام دالة VLOOKUP للبحث عن البيانات من جدول إلى آخر
• كيفية استخدام البحث التقريبي مع وظيفة VLOOKUP
• حسابات بدون صيغ