من هو كسول جدًا أو ليس لديه وقت للقراءة - شاهد الفيديو. التفاصيل والفروق الدقيقة في النص أدناه.
صياغة المشكلة
إذن ، لدينا جدولين - جدول الطلب и قائمة الاسعار:
تتمثل المهمة في استبدال الأسعار من قائمة الأسعار في جدول الطلبات تلقائيًا ، مع التركيز على اسم المنتج بحيث يمكنك لاحقًا حساب التكلفة.
الحلول
في مجموعة وظائف Excel ، ضمن الفئة المراجع والمصفوفات (بحث ومرجع) هناك وظيفة VPR (البحث).تبحث هذه الوظيفة عن قيمة معينة (في مثالنا ، هذه هي الكلمة "التفاح") في العمود الموجود في أقصى اليسار من الجدول المحدد (قائمة الأسعار) تتحرك من أعلى إلى أسفل ، وبعد العثور عليها ، تعرض محتويات الخلية المجاورة (23 روبل) كيميائيًا ، يمكن تمثيل تشغيل هذه الوظيفة على النحو التالي:
لسهولة الاستخدام الإضافي للوظيفة ، افعل شيئًا واحدًا في وقت واحد - أعط نطاق الخلايا في قائمة الأسعار اسمك الخاص. للقيام بذلك ، حدد جميع خلايا قائمة الأسعار باستثناء "الرأس" (G3: H19) ، حدد من القائمة إدراج - اسم - تعيين (أدخل - اسم - تعريف) أو اضغط على المفتاح كترل + فسنومك وأدخل أي اسم (بدون مسافات) مثل السعر... الآن ، في المستقبل ، يمكنك استخدام هذا الاسم للارتباط بقائمة الأسعار.
الآن نستخدم الوظيفة VPR... حدد الخلية حيث سيتم إدخالها (D3) وافتح علامة التبويب الصيغ - إدخال الوظيفة (الصيغ - إدراج الوظيفة)... في الفئة المراجع والمصفوفات (بحث ومرجع) ابحث عن الوظيفة VPR (البحث) والصحافة OK... ستظهر نافذة لإدخال الوسائط الخاصة بالوظيفة:
نملأها بدورنا:
- القيمة المطلوبة (ابحث عن القيمة) - اسم المنتج الذي يجب أن تجده الوظيفة في العمود الموجود في أقصى اليسار من قائمة الأسعار. في حالتنا ، كلمة "تفاح" من الخلية B3.
- طاولات ومكاتب (مصفوفة الجدول) - جدول تؤخذ منه القيم المرغوبة uXNUMXbuXNUMX ، أي قائمة الأسعار لدينا. كمرجع ، نستخدم الاسم الخاص بنا "السعر" المقدم سابقًا. إذا لم تذكر اسمًا ، يمكنك فقط تحديد الجدول ، ولكن لا تنس الضغط على الزر F4لتثبيت الرابط بعلامات الدولار، لأنه بخلاف ذلك ، سينزلق عند نسخ الصيغة الخاصة بنا إلى باقي الخلايا في العمود D3: D30.
- Column_number (رقم فهرس العمود) - الرقم التسلسلي (ليس حرفًا!) للعمود في قائمة الأسعار الذي سنأخذ منه قيم الأسعار. العمود الأول من قائمة الأسعار مع الأسماء مرقم 1 ، لذلك نحتاج إلى السعر من العمود المرقم 2.
- الفاصل_البحث (مجموعة البحث) - يمكن إدخال قيمتين فقط في هذا الحقل: FALSE أو TRUE:
- إذا تم إدخال قيمة 0 or يكذب أو ملقاه (خاطئة)، فهذا يعني في الواقع أنه لا يُسمح إلا بالبحث تطابق تام، على سبيل المثال ، إذا لم تعثر الوظيفة على العنصر غير القياسي المحدد في جدول الطلبات في قائمة الأسعار (إذا تم إدخال "Coconut" ، على سبيل المثال) ، فسيتم إنشاء الخطأ # N / A (لا توجد بيانات).
- إذا تم إدخال قيمة 1 or الحقيقة (صحيح)، فهذا يعني أنك لا تسمح بالبحث عن بالضبط ، ولكن تطابق تقريبي، على سبيل المثال في حالة "جوز الهند" ، ستحاول الوظيفة العثور على منتج باسم أقرب ما يمكن إلى "جوز الهند" وإرجاع سعر هذا الاسم. في معظم الحالات ، يمكن لمثل هذا الاستبدال التقريبي أن يلعب خدعة على المستخدم من خلال استبدال قيمة المنتج الخطأ الذي كان موجودًا بالفعل! لذلك بالنسبة لمعظم مشاكل العمل الحقيقية ، من الأفضل عدم السماح بالبحث التقريبي. الاستثناء هو عندما نبحث عن أرقام وليس نص - على سبيل المثال ، عند حساب خصومات الخطوة.
كل شىء! يبقى أن تضغط OK وانسخ الوظيفة التي تم إدخالها إلى العمود بأكمله.
# N / A الأخطاء وقمعها
المسمى الوظيفي VPR (البحث) إرجاع الخطأ # N / A (# N / A) اذا كان:
- تمكين البحث الدقيق (وسيطة عرض الفاصل = 0) والاسم المطلوب ليس موجودًا طاولات ومكاتب .
- تم تضمين البحث التقريبي (عرض الفاصل = 1)، ولكن طاولات ومكاتب ، التي يتم فيها البحث لا يتم فرزها بترتيب تصاعدي للأسماء.
- يختلف تنسيق الخلية التي تأتي منها القيمة المطلوبة للاسم (على سبيل المثال ، B3 في حالتنا) وتنسيق خلايا العمود الأول (F3: F19) من الجدول (على سبيل المثال ، رقمي ونص. ). هذه الحالة نموذجية بشكل خاص عند استخدام الرموز الرقمية (أرقام الحسابات والمعرفات والتواريخ وما إلى ذلك) بدلاً من الأسماء النصية. في هذه الحالة ، يمكنك استخدام الوظائف Ч и TEXT لتحويل صيغ البيانات. سيبدو شيئا من هذا القبيل:
= VLOOKUP (TEXT (B3) ، السعر ، 0)
تستطيع ان تقرأ المزيد عن هذا هنا.
- لا يمكن للدالة العثور على القيمة المطلوبة لأن الرمز يحتوي على مسافات أو أحرف غير قابلة للطباعة (فواصل الأسطر ، وما إلى ذلك). في هذه الحالة ، يمكنك استخدام وظائف النص TRIM (تقليم) и طباعة(ينظف) لإزالتها:
= VLOOKUP (TRIMSPACES (CLEAN (B3)) ، السعر ، 0)
= VLOOKUP (TRIM (CLEAN (B3)) ؛ السعر ؛ 0)
لمنع رسالة الخطأ # N / A (# N / A) في الحالات التي لا تجد فيها الوظيفة تطابقًا تامًا ، يمكنك استخدام الوظيفة خطأ مرجّح (معجل)... لذلك ، على سبيل المثال ، يعترض هذا البناء أي أخطاء ناتجة عن VLOOKUP ويستبدلها بالأصفار:
= IFERROR (VLOOKUP (B3، السعر، 2، 0)، 0)
= IFERROR (VLOOKUP (B3 ؛ السعر ؛ 2 ؛ 0) ؛ 0)
PS
إذا كنت بحاجة إلى استخراج ليس قيمة واحدة ، ولكن المجموعة بأكملها مرة واحدة (إذا كان هناك عدة قيم مختلفة) ، فسيتعين عليك استخدام صيغة الصفيف. أو استخدم ميزة XLOOKUP الجديدة من Office 365.
- نسخة محسنة من وظيفة VLOOKUP (VLOOKUP 2).
- حساب سريع لخصومات الخطوة (النطاق) باستخدام وظيفة VLOOKUP.
- كيفية عمل "VLOOKUP الأيسر" باستخدام وظائف INDEX و MATCH
- كيفية استخدام وظيفة VLOOKUP لملء النماذج ببيانات من القائمة
- كيفية سحب ليس الأول ، ولكن كل القيم من الجدول مرة واحدة
- وظائف VLOOKUP2 و VLOOKUP3 من الوظيفة الإضافية PLEX