كيفية استخدام فلوكوب في إكسيل

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

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

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

عادة القوائم مثل هذا لديها نوع من معرف فريد لكل عنصر في القائمة.في هذه الحالة، يكون المعرف الفريد في العمود "رمز العنصر".ملاحظة: لكي تعمل الدالة فلوكوب مع قاعدة بيانات / قائمة تلك القائمة يجب أن يحتوي

عمود يحتوي على معرف فريد( أو "مفتاح" أو "معرف") و يجب أن يكون العمود العمود الأول في الجدول.قاعدة بيانات العينة أعلاه تفي بهذا المعيار.

أصعب جزء من استخدام فلوكوب هو فهم ما هو عليه بالضبط.لذلك دعونا نرى ما اذا كنا نستطيع الحصول على هذا واضح أولا:

فلوكوب يسترجع المعلومات من قاعدة بيانات / قائمة استنادا إلى مثيل الموردة للمعرف الفريد.

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

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

دعونا نخلق مثالا على ذلك: نموذج الفاتورة الذي يمكننا إعادة استخدامه مرارا وتكرارا في شركتنا الوهمية.

أولا نبدأ إكسيل ونقوم بإنشاء فاتورة فارغة:

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

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

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

لذلك، قمنا بإنشاء قاعدة بيانات منتجاتنا، والتي تبدو كما يلي:

لاختبار صيغة فلوكوب التي نحن على وشك الكتابة، علينا أولا إدخال رمز بند صالح في الخلية A11 من فاتورتنا فارغة:

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

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

يظهر مربع

يسمح لنا بتحديد أي من الوظائف المتوفرة في إكسيل.

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

يظهر مربع وسيطات الدالة ، مما يطالبنا بجميع الوسيطات ( أو معلمات ) المطلوبة لإكمال دالة فلوكوب.يمكنك التفكير في هذا المربع حيث أن الدالة تسألنا الأسئلة التالية:

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

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

سنكمل الوسيطات بالترتيب، من أعلى إلى أسفل.

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

انقر فوق أيقونة محدد على يمين الوسيطة الأولى:

ثم انقر مرة واحدة على الخلية التي تحتوي على رمز العنصر( A11) واضغط أدخل :

يتم إدراج قيمة "A11" في الوسيطة الأولى.

الآن نحن بحاجة إلى إدخال قيمة الوسيطة Table_array .وبعبارة أخرى، نحن بحاجة إلى معرفة فلوكوب حيث العثور على قاعدة البيانات / القائمة.انقر فوق رمز محدد بجوار الوسيطة الثانية:

الآن حدد موقع قاعدة البيانات / القائمة وحدد القائمة بالكامل - لا يتضمن سطر العنوان .في مثالنا، قاعدة البيانات موجودة في ورقة عمل منفصلة، ​​لذلك نضغط أولا على علامة التبويب ورقة العمل هذه:

بعد ذلك نقوم بتحديد قاعدة البيانات بالكامل، وليس بما في ذلك سطر العنوان:

. .. ثم اضغط أدخل .يتم إدخال مجموعة الخلايا التي تمثل قاعدة البيانات( في هذه الحالة "قاعدة بيانات المنتج" A2: D7 ") تلقائيا في الوسيطة الثانية.

الآن نحن بحاجة إلى إدخال الوسيطة الثالثة Col_index_num .نحن نستخدم هذه الوسيطة لتحديد ل فلوكوب أي قطعة من المعلومات من قاعدة البيانات، المنتسبين مع رمز البند لدينا في A11، نود أن يكون عاد إلينا.في هذا المثال بالذات، نود أن يكون وصف العنصر عاد إلينا.إذا نظرتم إلى ورقة عمل قاعدة البيانات، ستلاحظ أن العمود "الوصف" العمود الثاني في قاعدة البيانات.وهذا يعني أننا يجب إدخال قيمة "2" في المربع Col_index_num :

من المهم أن نلاحظ أننا لا إدخال "2" هنا لأن العمود "الوصف" في العمود B علىورقة العمل هذه.إذا تم بدء تشغيل قاعدة البيانات في العمود K من ورقة العمل، سنقوم بإدخال "2" في هذا الحقل لأن العمود "الوصف" هو العمود الثاني في مجموعة من الخلايا التي حددناها عند تحديد "Table_array".

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

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

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

كما العمود الأول من قاعدة البيانات الخاصة بنا ليس مرتبة،ندخل كاذب في هذه الوسيطة:

هذا كل شيء!لقد أدخلنا جميع المعلومات المطلوبة ل فلوكوب لإرجاع القيمة التي نحتاجها.انقر فوق الزر موافق ولاحظ أن الوصف المطابق لرمز العنصر "R99245" تم إدخاله بشكل صحيح في الخلية B11:

الصيغة التي تم إنشاؤها بالنسبة لنا تبدو كما يلي:

إذا أدخلنا رمز العنصر مختلفة إلى الخليةA11، سنبدأ في رؤية قوة الدالة فلوكوب: تتغير الخلية الوصف لتتناسب مع رمز العنصر الجديد:

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

. .. وستبدو الصيغة كما يلي:

لاحظ أن الفرق الوحيد بين الصيغتين هو الوسيطة الثالثة( col_index_num ) قد تغير من "2" إلى "3"( لأنونحن نريد البيانات المستردة من العمود 3 في قاعدة البيانات).

إذا قررنا شراء 2 من هذه العناصر، فسندخل "2" في الخلية D11.ثم نقوم بإدخال صيغة بسيطة في الخلية F11 للحصول على مجموع الخط:

= D11 * E11

. .. الذي يبدو مثل هذا. ..

إكمال قالب الفاتورة

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

لم يتم إكمال قالب الفاتورة بعد.من أجل إكمال ذلك، ونحن سوف نفعل ما يلي:

1. سنقوم بإزالة رمز البند عينة من الخلية A11 و "2" من الخلية D11.سيؤدي هذا إلى ظهور صيغ الخطأ فلوكوب التي تم إنشاؤها حديثا رسائل الخطأ:
   
   يمكننا معالجة هذا عن طريق الاستخدام الحكيم وظائف إف() و إيسبلانك() .نقوم بتغيير صيغتنا من هذا. .. = فلوكوب( A11، 'قاعدة بيانات المنتج' A2: D7،2، فالس) . .. إلى هذا. .. = إف( إسبلانك( A11)، ""، فلوكوب( A11،'A2: D7،2، فالس))
   
2. سنقوم بنسخ الصيغ في الخلايا B11 و E11 و F11 وصولا إلى ما تبقى من صفوف البند من الفاتورة.لاحظ أنه إذا قمنا بذلك، فإن الصيغ الناتجة لن تشير بشكل صحيح إلى جدول قاعدة البيانات.يمكننا إصلاح هذا عن طريق تغيير مراجع الخلية لقاعدة البيانات إلى المطلقة مراجع الخلية .بدلا من ذلك - وحتى أفضل - يمكننا إنشاء اسم النطاق لقاعدة بيانات المنتج بأكمله( مثل "المنتجات")، واستخدام اسم النطاق بدلا من مراجع الخلية.سوف تتغير الصيغة من هذا. .. = إف( إسبلانك( A11)، ""، فلوكوب( A11، 'قاعدة بيانات المنتج'! A2: D7،2، فالس)) . .. إلى هذا. .. = إف( إسبلانك( A11)، ""، فلوكوب( A11، المنتجات، 2، فالس)) . .. و ثم نسخ الصيغ إلى بقية الصفوف البند الفاتورة.
3. من المحتمل أن "تأمين" الخلايا التي تحتوي على الصيغ لدينا( أو بالأحرى فتح الخلايا أخرى )، ثم حماية ورقة العمل، من أجل ضمان أن صيغنا بناؤها بعناية لا الكتابة عن طريق الخطأ عندما يأتي شخص ما لملءفي الفاتورة.
4. سنقوم بحفظ الملف كقالب ، بحيث يمكن إعادة استخدامها من قبل الجميع في شركتنا

إذا كنا نشعر حقا ذكي، ونحن سوف إنشاء قاعدة بيانات لجميع عملائنا في ورقة عمل أخرى، ومن ثم استخدامأدخل الرقم التعريفي للعميل في الخلية F5 لملء اسم العميل وعنوانه تلقائيا في الخلايا B6 و B7 و B8.

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