أمثلة على VLOOKUP المتقدمة: بحث متعدد المعايير

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

• البحث في Excel بمعايير متعددة
• نستخرج القيم الثانية والثالثة وما إلى ذلك باستخدام VLOOKUP
• استرجع كل التكرارات للقيمة المطلوبة
• البحث XNUMXD حسب الصف والعمود المعروفين
• استخدام عدة VLOOKUPs في صيغة واحدة
• استبدال ديناميكي للبيانات من جداول مختلفة

البحث في Excel بمعايير متعددة

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

مثال 1: البحث بمعيارين مختلفين

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

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

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- ستعيد هذه الصيغة النتيجة 15المطابق للمنتج تفاح، لأنها أول قيمة تطابق.

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

لذلك ، يمكنك إضافة عمود إضافي إلى الجدول ونسخ الصيغة التالية على جميع خلاياه: = B2 & C2. إذا كنت تريد أن تكون السلسلة أكثر قابلية للقراءة ، فيمكنك فصل القيم المجمعة بمسافة: = B2 & »« & C2. بعد ذلك يمكنك استخدام الصيغة التالية:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

أين الخلية B1 يحتوي على القيمة المتسلسلة للوسيطة ابحث عن القيمة (lookup_value) و 4 - جدال حاد col_index_num (العمود رقم) ، أي رقم العمود الذي يحتوي على البيانات المطلوب استردادها.

مثال 2: VLOOKUP بمعيارين مع عرض الجدول على ورقة أخرى

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

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

لذا فإن الصيغة ذات VPR يمكن أن يكون مثل هذا:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

هنا ، يحتوي العمودان B و C على أسماء العملاء وأسماء المنتجات ، على التوالي ، والارتباط الطلبات! $ A و $ 2: $ D $ 2 يحدد جدولاً للبحث عنه في ورقة أخرى.

لجعل الصيغة أكثر قابلية للقراءة ، يمكنك تسمية نطاق العرض ، وبعد ذلك ستبدو الصيغة أبسط بكثير:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

لكي تعمل الصيغة ، يجب دمج القيم الموجودة في العمود الموجود في أقصى اليسار من الجدول الذي تبحث عنه بنفس الطريقة تمامًا كما هو الحال في معايير البحث. في الشكل أعلاه ، قمنا بدمج القيم u2bu2band ووضع مسافة بينهما ، بنفس الطريقة التي تحتاج إلى القيام بها في الوسيطة الأولى للوظيفة (BXNUMX & "" & CXNUMX).

تذكر! المسمى الوظيفي VPR يقتصر على 255 حرفًا ، ولا يمكنه البحث عن قيمة يزيد طولها عن 255 حرفًا. ضع ذلك في الاعتبار وتأكد من أن طول القيمة المطلوبة لا يتجاوز هذا الحد.

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

نستخرج القيم الثانية والثالثة وما إلى ذلك باستخدام VLOOKUP

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

افترض أن أحد أعمدة الجدول يحتوي على أسماء العملاء (اسم العميل) ، بينما يحتوي العمود الآخر على المنتجات (المنتج) التي اشتروها. دعنا نحاول العثور على العناصر الثانية والثالثة والرابعة التي اشتراها عميل معين.

أسهل طريقة هي إضافة عمود إضافي قبل العمود اسم العميل وتعبئته بأسماء العملاء برقم التكرار لكل اسم على سبيل المثال ، جون دو 1, جون دو 2 سنقوم بالخدعة مع الترقيم باستخدام الوظيفة COUNTIF (COUNTIF) ، بالنظر إلى أن أسماء العملاء موجودة في العمود B:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

بعد ذلك يمكنك استخدام الوظيفة العادية VPRللعثور على الطلب المطلوب. فمثلا:

• 2 - عشر العنصر الذي طلبه العميل دان براون:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• 3 - عشر العنصر الذي طلبه العميل دان براون:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

في الواقع ، يمكنك إدخال مرجع خلية كقيمة بحث بدلاً من نص ، كما هو موضح في الشكل التالي:

إذا كنت تبحث فقط عن 2 الإلكترونية التكرار ، يمكنك القيام بذلك بدون العمود الإضافي عن طريق إنشاء صيغة أكثر تعقيدًا:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

في هذه الصيغة:

• دولار أمريكي واحد - خلية تحتوي على اسم المشتري (لم يتغير ، يرجى ملاحظة - الارتباط مطلق) ؛
• $ ب $ - عمودي اسم العميل;
• Table4 - طاولتك (يمكن أن يكون هذا المكان أيضًا نطاقًا عاديًا) ؛
• $ C16 - الخلية النهائية للجدول أو النطاق.

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

إذا كنت بحاجة إلى قائمة بجميع التطابقات - الوظيفة VPR هذا ليس مساعدًا ، لأنه يُرجع قيمة واحدة فقط في كل مرة - فترة. لكن Excel له وظيفة INDEX (INDEX) ، والتي يمكنها التعامل بسهولة مع هذه المهمة. كيف ستبدو هذه الصيغة ، ستتعلم في المثال التالي.

استرجع كل التكرارات للقيمة المطلوبة

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

على سبيل المثال ، تبحث الصيغة أدناه عن جميع التكرارات للقيمة من الخلية F2 في النطاق B2: B16 وترجع النتيجة من نفس الصفوف في العمود C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

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

إذا كنت مهتمًا بفهم كيفية عملها ، فدعنا نتعمق في تفاصيل الصيغة قليلاً:

جزء 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$ F $ 2 = B2: B16 - قارن القيمة الموجودة في الخلية F2 بكل قيمة من القيم الموجودة في النطاق B2: B16. إذا تم العثور على تطابق ، ثم التعبير STRING (C2: C16) -1 تُرجع رقم السطر المقابل (القيمة -1 يسمح لك بعدم تضمين سطر العنوان). إذا لم تكن هناك تطابقات ، فإن الوظيفة IF (IF) ترجع سلسلة فارغة.

نتيجة الوظيفة IF (إذا) سيكون هناك مثل هذا المصفوفة الأفقية: {1,"",3,"",5,"","","","","","",12,"","",""}

جزء 2:

ROW()-3

СТРОКА()-3

هنا الوظيفة صف (LINE) بمثابة عداد إضافي. نظرًا لنسخ الصيغة في الخلايا F4: F9 ، فإننا نطرح الرقم 3 من نتيجة دالة للحصول على قيمة 1 في الخلية F4 (السطر 4 ، اطرح 3) لتحصل على 2 في الخلية F5 (السطر 5 ، اطرح 3) وهكذا.

جزء 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

المسمى الوظيفي صغير (صغير) يعود ن اوه أصغر قيمة في مصفوفة البيانات. في حالتنا ، يتم تحديد الموضع (من الأصغر) للعودة بواسطة الوظيفة صف (الخط) (انظر الجزء 2). لذلك ، بالنسبة للخلية F4 وظيفة صغيرة ({مجموعة}، 1) عائدات 1 - عشر (أصغر) عنصر صفيف ، أي 1. للخلية F5 عائدات 2 - عشر أصغر عنصر في المصفوفة 3، الخ.

جزء 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

المسمى الوظيفي INDEX (INDEX) تُرجع ببساطة قيمة خلية معينة في مصفوفة ج2:ج16. للخلية F4 وظيفة مؤشر ($ C $ 2: $ C $ 16) سيعود تفاحفي حالة F5 وظيفة مؤشر ($ C $ 2: $ C $ 16) سيعود حلويات وما إلى ذلك وهلم جرا.

جزء 5:

IFERROR()

ЕСЛИОШИБКА()

أخيرًا ، نضع الصيغة داخل الدالة خطأ مرجّح (IFERROR) ، لأنه من غير المحتمل أن تكون مسرورًا برسالة الخطأ #في (# N / A) إذا كان عدد الخلايا التي تم نسخ الصيغة إليها أقل من عدد القيم المكررة في النطاق الذي يتم عرضه.

البحث XNUMXD حسب الصف والعمود المعروفين

يتضمن إجراء بحث XNUMXD في Excel البحث عن قيمة بواسطة رقم صف وعمود معروف. بمعنى آخر ، أنت تستخرج قيمة الخلية عند تقاطع صف وعمود معين.

إذن ، لننتقل إلى الجدول الخاص بنا ونكتب صيغة دالة VPR، والتي ستجد معلومات حول تكلفة بيع الليمون في مارس.

هناك عدة طرق لإجراء بحث XNUMXD. تحقق من الخيارات واختر الخيار الذي يناسبك.

وظائف VLOOKUP و MATCH

يمكنك استخدام مجموعة من الوظائف VPR (VLOOKUP) و أكثر انكشافاً (MATCH) للعثور على القيمة عند تقاطع الحقول اسم المنتج (سلسلة نصية) و شهر (عمود) من المصفوفة المعنية:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

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

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

ترجمت هذه الصيغة إلى لغة بشرية ، وتعني:

• نحن نبحث عن شخصيات "مار" - حجة ابحث عن القيمة (ابحث عن القيمة)؛
• البحث في الخلايا من A1 إلى I1 - وسيطة lookup_array (lookup_array) ؛
• إرجاع المطابقة التامة - حجة نوع مباراة (نوع مباراة).

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

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

دالة SUMPRODUCT

المسمى الوظيفي SUMPRODUCT (SUMPRODUCT) بإرجاع مجموع منتجات المصفوفات المحددة:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

دالة INDEX و MATCH

سأشرح في المقالة التالية هذه الوظائف بالتفصيل ، لذا يمكنك الآن نسخ هذه الصيغة:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

النطاقات المسماة وعامل التقاطع

إذا لم تكن في كل صيغ Excel المعقدة ، فقد تعجبك هذه الطريقة المرئية التي لا تنسى:

1. حدد الجدول ، افتح علامة التبويب الصيغ (الصيغ) وانقر إنشاء من التحديد (إنشاء من الاختيار).
2. تحقق من العلب الصف العلوي (على السطر أعلاه) و العمود الأيسر (في العمود على اليسار). سيقوم Microsoft Excel بتعيين أسماء للنطاقات من القيم الموجودة في الصف العلوي والعمود الأيسر من جدول البيانات. الآن يمكنك البحث باستخدام هذه الأسماء مباشرة دون إنشاء الصيغ.
3. اكتب في أي خلية فارغة = row_name column_name، على سبيل المثال مثل هذا:

   = الليمون Mar

   … أو العكس:

   = مار ليمون

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

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

4. صحافة أدخل وتحقق من النتيجة

بشكل عام ، أيًا كانت الطرق المذكورة أعلاه التي تختارها ، ستكون نتيجة البحث ثنائي الأبعاد هي نفسها:

استخدام عدة VLOOKUPs في صيغة واحدة

يحدث أن لا يحتوي الجدول الرئيسي وجدول البحث على عمود واحد مشترك ، وهذا يمنعك من استخدام الوظيفة المعتادة VPR. ومع ذلك ، يوجد جدول آخر لا يحتوي على المعلومات التي نهتم بها ، ولكنه يحتوي على عمود مشترك مع الجدول الرئيسي وجدول البحث.

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

لإضافة أسعار من جدول البحث الثاني إلى الجدول الرئيسي ، يجب عليك تنفيذ إجراء يُعرف باسم double VPR أو متداخلة VPR.

1. اكتب دالة VPR، الذي يبحث عن اسم المنتج في الجدول جدول البحث 1استخدام SKU، كقيمة مطلوبة:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   هنا New_SKU - نطاق مسمى $ أ: $ ب في الطاولة جدول البحث 1, 2 - هذا هو العمود B الذي يحتوي على أسماء البضائع (انظر الصورة أعلاه)

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

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   هنا السعر - نطاق مسمى $ أ: $ ج في الطاولة جدول البحث 2, 3 يحتوي العمود C على الأسعار.

يوضح الشكل أدناه النتيجة التي تم إرجاعها بواسطة الصيغة التي أنشأناها:

استبدال ديناميكي للبيانات من جداول مختلفة باستخدام VLOOKUP و INDIRECT

أولاً ، دعنا نوضح ما نعنيه بالتعبير "الاستبدال الديناميكي للبيانات من جداول مختلفة" للتأكد من أننا نفهم بعضنا البعض بشكل صحيح.

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

تخيل أن لديك تقارير مبيعات لعدة مناطق بنفس المنتجات وبنفس التنسيق. تريد العثور على أرقام المبيعات لمنطقة معينة:

إذا كان لديك تقريرين فقط من هذا القبيل ، فيمكنك استخدام صيغة بسيطة مخزية مع الوظائف VPR и IF (IF) لتحديد التقرير المطلوب للبحث فيه:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

أين:

• 2 دولار هي خلية تحتوي على اسم المنتج. لاحظ أننا نستخدم المراجع المطلقة هنا لتجنب تغيير قيمة البحث عند نسخ الصيغة إلى خلايا أخرى.
• D3 دولار هي خلية باسم المنطقة. نحن نستخدم مرجع عمود مطلق ومرجع صف نسبي لأننا نخطط لنسخ الصيغة إلى خلايا أخرى في نفس العمود.
• FL_سالes и CA_ المبيعات - أسماء الجداول (أو النطاقات المسماة) التي تحتوي على تقارير المبيعات المقابلة. يمكنك بالطبع استخدام أسماء الأوراق المعتادة ومراجع نطاقات الخلايا ، على سبيل المثال ورقة FL! $ 3 $: $ B $ 10، ولكن النطاقات المسماة أكثر ملاءمة.

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

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

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

أين:

• 2 دولار - هذه خلية تحمل اسم المنتج ، ولم تتغير بسبب الارتباط المطلق.
• D3 دولار هي الخلية التي تحتوي على الجزء الأول من اسم المنطقة. في مثالنا هذا FL.
• _مبيعات - الجزء المشترك من اسم كل النطاقات أو الجداول المسماة. عند دمجها مع القيمة الموجودة في الخلية D3 ، فإنها تشكل الاسم المؤهل بالكامل للنطاق المطلوب. فيما يلي بعض التفاصيل لمن هم جدد في الوظيفة غير مباشر.

كيف يعمل INDIRECT و VLOOKUP

أولاً ، اسمحوا لي أن أذكرك بصيغة الوظيفة غير مباشر (غير مباشر):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

يمكن أن تكون الوسيطة الأولى مرجع خلية (نمط A1 أو R1C1) أو اسم نطاق أو سلسلة نصية. تحدد الوسيطة الثانية نمط الارتباط الموجود في الوسيطة الأولى:

• A1إذا كانت الحجة الكود الحقيقي (صواب) أو غير محدد ؛
• R1C1، إذا Fكما ه (خاطئة).

في حالتنا ، الرابط له النمط A1، لذلك يمكنك استبعاد الحجة الثانية والتركيز على الأولى.

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

المسمى الوظيفي غير مباشر يربط القيمة الموجودة في العمود D وسلسلة النص "_Sales" ، وبالتالي يخبرنا بذلك VPR في أي جدول للبحث. إذا كانت الخلية D3 تحتوي على القيمة "FL" ، فستبحث الصيغة في الجدول FL_Sales، إذا كان "CA" - في الجدول CA_ المبيعات وما إلى ذلك وهلم جرا.

نتيجة الوظائف VPR и غير مباشر سيكون ما يلي:

إذا كانت البيانات موجودة في كتب Excel مختلفة ، فأنت بحاجة إلى إضافة اسم الكتاب قبل النطاق المحدد ، على سبيل المثال:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

إذا كانت الوظيفة غير مباشر يشير إلى مصنف آخر ، يجب أن يكون هذا المصنف مفتوحًا. إذا تم إغلاقها ، فستقوم الوظيفة بالإبلاغ عن خطأ. #REF! (#SSYL!).