دالة VLOOKUP
دالة VLOOKUP

شرح دالة VLOOKUP

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

تنتمي دالة VLOOKUP الى أدوات البحث والمراجع Lookup & Reference وهي اختصار لـ Vertical Lookup والتي تعني البحث العمودي كونها تقوم بالبحث عن قيمة ما ضمن أول عمود من جهة اليسار في جدول بيانات فإذا وجدت تطابق اعادت قيمة من نفس صف التطابق من عمود أنت تحدده. كمثال تقريبي, تخيل أنك تنظر إلى جدول علامات طلاب المرحلة الثانوية تبحث عمودياً (من الأعلى للأسفل) في عمود الأسماء عن اسم ما, عندما تجده فإنك تبحث أفقياً عن علامته في مادة العلوم مثلاً والتي انت تعلم موقعها من الجدول, العمود رقم 4 على سبيل المثال.

 

بنية الدالة VLOOKUP (مكوناتها):

 

مكونات دالة Vlookup
محارف / مكونات دالة VLOOKUP

بتفصيل أكثر:

Lookup_Value: قيمة اجبارية وهي القيمة التي تبحث عنها (في مثالنا السابق, تمثل هذه القيمة اسم الطالب الذي تبحث عنه في جدول العلامات). قد تكون هذه القيمة متمثلة بقيمة رقمية (كالبحث عن الرقم الوطني أو الرقم الوظيفي) أو قيمة نصية (كالبحث عن اسم محدد او مجموعة رموز). كما أنه ومن الممكن استخدام معادلة أو دالة في هذا المحرف كاحتساب معدل النجاح لطالب ما من خلال معادلة المتوسط الحسابي ومن ثم استخدام هذه القيمة للبحث عن الاختصاصات المسموح بها لهذا المعدل.

Table_Array: قيمة اجبارية وتمثل مصفوفة (جدول) البيانات أو نطاق البيانات. في مثالنا السابق, كامل جدول علامات طلاب المرحلة الثانوية يمثل مايسمى Table Array. يجب أن يحوي أول عمود من اليسار لمستخدمي الواجهة الإنكليزية (من اليمين للواجهة العربية) على العمود الذي ستبحث فيه على قيمة البحث Lookup Value. لن يطلب اكسل منك تحديد هذا العمود لأن هذا هو مكانه الإفتراضي والذي يتوقع منك اكسل أن تحترمه وتتبعه عند استخدام دالة VLOOKUP.

يمكنك كتابة عنوان هذا الجدول أو النطاق عن طريق تحديد عنوانه مثلاً A1:B100 مع أو بدون تثبيت من خلال استخدام المراجع المطلقة, باستخدام اسماء النطاقات أو عن طريق بعض الدوال التي تنتج نطاق (سيتم شرح هكذا نطاقات لاحقاً إن شاء الله). أما مايحويه هذا الجدول, النطاق فقد يكون نصاً, رقماً أو قيماً منطقية.

Col_Index_Num: قيمة اجبارية وتمثل رقم العمود الذي يحوي النتيجة التي نبحث عنها (والتي يجب على اكسل أن يظهرها لنا كنتيجة لتطبيق دالة VLOOKUP) وذلك في جدول البيانات Table_Array المذكور اعلاه انطلاقاً من جهة اليسار للواجهة الإنكليزية (من اليمين للواجهة العربية). يجب التنويه إلى وجوب ذكر ترتيب عمود النتيجة بالنسبة لموضعه داخل جدول البيانات Table Array  وذلك بغض النظر عن ترتيب العمود الفعلي في صفحة اكسل. الترتيب يجب أن يكون رقمياً, للعمود الثالث نكتب 3 وهكذا.

ادخال الرقم 1 سيعيد نفس القيمة المبحوث عنها في حين ان ادخال رقم أقل من 1 سيعيد الخطأ !VALUE# أما ادخال رقم أكبر من عدد اعمدة جدول البيانات Table Array سيعيد الخطأ !REF#.

Range_Lookup: قيمة اختيارية وتمثل طريقة البحث التي يجب على اكسل أن يتبعها (بحث مطابق أو تقريبي). حذفها, اعطائها القيمة 1 أو True يعني أن اكسل سيبحث بحثاً تقريباً, بمعنى أنه في حال لم يجد اكسل تطابقاً للقيمة المبحوث عنها Lookup_Value فإنه سيعيد نتيجة أقرب أصغر قيمة للقيمة المبحوث عنها Lookup Value. في حين أن القيمة الفارغة, 0 أو False يعني أن اكسل سيبحث بحثاً مطابقاً ليعيد النتيجة الصحيحة أو الخطأ N/A# في حال عدم وجود تطابق مع القيمة المبحوث عنها.

خيارات طرق البحث في الدالة Vlookup
VLOOKUP Function

لاحظ أول خيار من قائمة البحث المتطابق في الصورة اعلاه تبين لنا كيف أن حذف محرف Range_Lookup كليّاً يعني لاكسل البحث المتطابق لدالة VLOOKUP في حين أن إبقاء هذا المحرف فارغاً دون حذف يعني أن بحث VLOOKUP سيكون تقريبياً كما في أول خيار من القائمة بحث تقريبي اعلاه.

 

ملاحظات على ماسبق:

  • في حال الإعتماد على VLOOKUP مع البحث المتطابق وفي حال وجود أكثر من تطابق فإن اكسل سيعيد نتيجة أول تطابق يجده.
  • في حال الإعتماد على VLOOKUPمع  البحث التقريبي, فإنه يجب عليك ترتيب جدول البيانات Table Array ترتيباً تصاعدياً باستخدام أول عمود من اليسار لمستخدمي الواجهة الإنكليزية (أول عمود من اليمين لمستخدمي الواجهة العربية).
  • عطفاً على النقطة السابقة, في حال الإعتماد على VLOOKUP مع البحث المتطابق فإنه لاحاجة لترتيب جدول البيانات Table Array.
  • في حال استخدام VLOOKUP مع البحث التقريبي عند البحث عن قيمة غير موجودة في جدول البيانات Table Array فإن اكسل سيعيد نتيجة أقرب أصغر قيمة للقيمة المبحوث عنها Lookup Value. في حال كانت القيمة المبحوث عنها Lookup Value أصغر من كل قيم عمود البحث (العمود الأول) في جدول البيانات Table Array فإن اكسل سيعيد الخطأ N/A#.
  • عند البحث عن قيم نصية في العمود الأول من Table Array، تأكد من عدم احتواء البيانات في العمود الأول من Table Array على مسافات بادئة أو مسافات زائدة، أو استخدام غير متناسق لعلامة الاقتباس المستقيمة ( ‘ أو ” ) وعلامة الاقتباس المتعرجة ( ‘ أو “)، أو أحرف غير قابلة للطباعة. في حالة وجود أي من هذه الحالات، قد تُرجع VLOOKUP قيمة غير صحيحة أو غير متوقعة.
  • عند البحث عن قيمة عددية أو تاريخ, تأكد بأن تنسيق بيانات عمود البحث (أول عمود) في جدول البيانات متطابق مع تنسيق قيمة البحث تجنباً للحصول على قيمة غير صحيحة أو غير متوقعة.
  • في حال البحث المتطابق عن قيمة نصية, يمكنك ابدال بعض الأحرف الناقصة من النص المبحوث عنه Lookup Value عن طريق استبدالها باداة الإستفهام ؟ أو يمكنك استبدال مجموعة من الأحرف المتتابعة (كتتمة جملة ما لاتريد كتابتها كاملةً) وذلك عن طريق اضافة  رمز النجمة *

 

مثل عملي:

لنفترض أنك تريد أن تبحث عن أرقام جوالات بعض عملاءك باستخدام رقم العميل وذلك عن طريق استخدام دالة VLOOKUP,

مثال تطبيقي لاستخدام دالة VLOOKUP
مثال تطبيقي لاستخدام دالة VLOOKUP

 

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

مثال عملي لاستخدام دالة VLOOKUP
مثال عملي لاستخدام دالة VLOOKUP

 

الحل بصورة أشمل,

استخدام دالة VLOOKUP
استخدام دالة VLOOKUP

 

يمكنكم تنزيل الملف التالي كمثال توضيحي لاستخدام  دالة VLOOKUP.

يجب على مستخدمي الواجهة العربية تبديل الاتجاهات المذكورة في المقالة اعلاه لتطابق مع واجهات اكسل العربية.

عن Hussein.B

مؤسس ومدير موقع viaexcel.com, اعمل في مجال الإئتمان, مهتم بمواضيع التدريب وتطوير الذات بالإضافة لحبي الشديد لبرنامج اكسل.

84 تعليقات

  1. هل من الممكن ان تقرء معادلةvlook up من عمودين بمعنى لدى عدد 2 كود هل ممكن فى حالة كتابة احدهم ان تقرء المعادلة وكيف

  2. خلدون الصالح

    السلام عليكم
    طبقت كل شي صح و عم تتطلعلي النتيجة #REF! و ما بعرف ليش
    ممكن تساعدني

    هاي الصيغة يلي كتبتها =VLOOKUP(F3;$A$3:$A$102;2;0)

    • المشكلة ان نطاق البحث لديك هو عمود واحد A في حين أنك تبحث عن التنتيجة في العمود رقم 2. اعتقد يجب ان تغير نطاق البحث ليكون A:B وليس A:A

  3. السلام عليكم ورحمه الله وبركاته
    اريد ان اعمل برنامج مكافات لجميع العاملين وعددهم اكثر 1400 موظف بحيث يشمل الاسم رقم السجل اساسي المرتب الفئة المستحق المحصل لحساب الضرائب 10% وهي (خاضع أو غير خاضع ) المحصل لحساب التامينات والمعاشات 10% ( خاضع أو غير خاضع )دمغه وتتحسب ( مقدار المكافاة -50 ×7 ÷1000) تامين صحي 1% المستقطع ـ الصافي ـ توقيع المستلم وجزاك الله خيراً

  4. ممكن ياستاذ تفيدنا لو سمحت
    كيف ممكن اكتب رقم في خلية وتظهر تلقائيا قيمته في خلية اخرى
    مثلا اكتب 3 في خلية ويظهر لي قيمة ال 3 = 713 في خلية اخرى
    جزالك الله خير

    • المضوع يعتمد على كيفية الربط بين الرقم 3 والرقم 713. اقصد هل يوجد جدول للربط بين هذين الرقمين؟

  5. السلام عليكم اخي الكريم
    اود ان تفيدني في كيفية نسخ خلايا تم تنزيلها من برنامج الرواتب علي بيان اكسيل
    وتطبيقها بملف الاكسل الاساسي الذي به جميع بيانات العمال
    وتكون الطريقه نسخ جميع العناصر المتشابهه
    (توضيح اكثر لنفس السؤال)
    الملف الاساسي به تصفيه لجميع المشاريع اي ان يتم عمل تصفيه لكل مشروع
    ولا استطيع النسخ واللصق . الرجاء الافاده

  6. السلام عليكم اخي الكريم
    اود ان تفيدني في كيفية نسخ خلايا تم تنزيلها من برنامج الرواتب علي بيان اكسيل
    وتطبيقها بملف الاكسل الاساسي الذي به جميع بيانات العمال
    وتكون الطريقه نسخ جميع العناصر المتشابهه
    ولك جزيل الشكر

  7. استاذي المتفوق. ربنا يذيك علما …ممكن تعطينا اسم كتاب شامل لشرح الدوال …شكرا,, ,,

    • شكراً لتعليقك الطيب. بالنسبة للكتاب, بأي لغة؟ افضل المراجع باللغة الإنكليزية لكن يكوجد محاولات جيدة من مدونين عرب مميزين لملئ الفجوة.

  8. السلام عليكم ورحمة الله وبركاته
    لدي أكثر من بوك و عملت ربط بالدالة vlookup لتغيير القيم لدي في التجميع و لكن يجب علي فتح كل الملفات السابقة
    لي تتغير القيمة في الملف إكسل الأول أحتاج الى عدم فتح الملفات المتربطة مع بعضها البعض وهي مغلقة و تلقائيا .

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

  9. نشكركم على المعلومات القـيمة .. حامـد حســن

  10. عندي مجموعة فواتير بقيمة 18000 دولار وهية عبارة عن ديون عليه يجب دفعها وتوفر معي مبلغ 10000 دولار وخصص لدفع جزء من الديون كيف احدد الفواتير الي بتعطيني اقرب مبلغ ل 10000 بسهولة

  11. مساء الخيرات أ/حسين
    ممكن إفادتنا عن طريقة لعمل معادلة vlookup من عمودين متفرقين أو أي معادلة أخرى تفي بنفس الغرض ؟
    و شكراً

    • مرحباً عادل. ممكن توضح سؤالك اكثر من فضلك. هل قصدك أن الشرط هو مو عمودين مختلفين؟

      • سلام عليكم
        الاخ حسين
        عند استخدامي الامر velookup – وعند وضع رقم 0 او 1 للمطابقة التامة او التقريبية
        يعطيني الناتج التقريبي الاقل –
        سؤالي اذا اردت ان يعطيني الناتج الاعلي مباشرة – ماهو الرقم او الاضافة المطلوبة لذلك
        مثال الرقم هو 13.2 والجدول الذي ابحث فيه يوجد 13.1 ويوجد الرقم 13.5 – ماذا اكتب ليختار 13.5

        • وعليكم السلام. ممكن تستخدم دالة Index مع Match. رح دولك ع ملف مثال وارسلك اياه. الرجاءا التواصل من خلال صفحة الموقع ع الفيسبوك.

  12. السلام عليكم ورحمة الله وبركاته،،

    أولاً اسمح لي أن أقدم لكم التحية على مجهودكم المتميز
    ولي سؤال لو تكرمت ، لو عندي ملف اكسل مكون من عدة شيتات مرتبطين ببعض كا كنترول شيت لمدرسة مثلا وعازر اعمل صفحة للستعلام عن النتيجة عن طريق دالة v lookup اعملها ازاي ولكم شكري وتقديري

    • اهلاً محمد, وعليكم السلام. اقترح عليك استخدام دالة Vlookup مع دالة IF بحيث يتم البحث في الملف الأول, إن كانت النتيجة خطأ, إذاً يتم البحث في الملف الثاني وهكذا.

  13. جزاك الله الف خير شرح كافي ووافس

  14. محمود السيد

    السلام عليكم ورحمة الله وبركاته،،

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

    ولكم خالص التحية

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

  15. السالم عليكم
    كيف يمكن وبابسط طريقة ممكنه اربط بين جدولين في ورقتين منفصلتين بحيث اذا دخلت البيانات في الجدول الاول تنضاف تلقائيا في الجدول الثاني
    ملاحظة انا اسيتخدم excel 2010

  16. السلام عليك يا أستاذي الكريم
    بص لدي سؤال
    انا حولت إدخال الدالة VLooKUP ولم تظهر الدلة ؟
    وانا بدي أعمل قائمة للعمال كل عامل لديه رقم التسجيل
    وعندما أقوم بإدخال رقم التسجيل لأحد العمل في الخانة أريده أن يعطيني جميع المعلومات الشخصية للعمل
    وأرجو منك مساعدتي من فضل يا أستاذي.

    • وعليكم السلام. اهلاً بك صديقي. ماهي لغة اكسل اللتي تستخدمها؟ يجب أن تظهر لك دالة vlookup بمجرد البدء بكتابة اسمها بعد اشارة المساواة إلا ان كانت النسخة لديك فرنسية او بلغة اخرى حيث الدوال لها اسماء اخرى حسب اللغة المختارة.

  17. نيهاد نورهان

    السلام عليكم استاذنا الفاضل وشكرا لك على مساعدتك للجميع اريد ان اطرح عليك مشكلة ان كان في امكانك مساعدتي لرسم ورقة عمل (une base donnée) فيها سلسلة مختلفة من العمليات وهل بامكاني التواصل معك عن طريق الاميل فرغم بساطة الشرح الذي تقدمه الا انني اجد بعض الصعوبة وشكرا مسبقا

    • أهلاً نيهاد. يمكنك التواصل معي على husseinb [at] viaexcel . com لكن يجب التنبه الى ان لدي مشكلة حالية في كمبيوتري المنزلي لذلك قد يأخذ الرد بعض الوقت حتى يتم حل المشكلة.

    • نيهاد نورهان

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

      • اهلأً أخت نيهاد مرة ثانية. قمت بارسال ايميل لحضرتك. بانتظار ردك وشرحك للمشكلة لاحاول المساعدة. شكراً لكلامك وتشجيعك الطيب.

  18. اخي حسين السلام عليكم ورحمة الله وبركاته وبعد. عندي مجلة شهرية والذين يكتبون فيها عددهم ٢٥ كاتب لذلك انشئت جدول اكسل يحتوي على ما يقارب ١٠ اعمدة وترتيبها كالتالي: رقم العدد – ميلادي- هجري – اسم الكاتب – اسم المقالة- العنوان العام – عدد الصفحات- وغيرها
    وبما ان المجلة اصبح لها اربع سنوات فقاعدة البيانات اصبحت كثيرة والبحث فيها اصبح متعبا
    سؤالي هو: هل يوجد دالة بحيث يكون عندي خلية للبحث بواسطة اسم الكاتب مثلا اكتب في البحث اسامة فيعطيني النتيجة في جدول فيه اسم الكاتب ( اسامة) والمقالات التي كتبها ورقم العدد وغير ذلك ملاحظة كل كاتب قد يكون له ٢٠ مقالة

    • حياك الله أخي أبو علي. في هذه الحالة, لدي لك اقتراحين, الأول أنك تستخدم خاصيّة الجداول (حدد جدول بياناتك ثم اضغط الاختصار Ctrl + L) إذ أنه يتيح لك امكانية فلترة جميع الأعمدة ومن ضمنها عمود اسم الكاتب. الجميل بالموضوع انك وعند اضافة اي بيانات جديدة اسفل هذا الجدول, ستم ضمها إليه تلقائياً.
      الحل الآخر سيناسبك أكثر في حال كان لديك مشكلة في سرعة جهازك (إذا كان حجم الملف كبير كما ذكرت لي), انشئ جدول محوري Pivot Table لبياناتك في ورقة عمل جديدة واربطها بجدول بياناتك. في هذه الحالة, ملف البحث سيكون ممتاز جداً من ناحية السرعة. كل المطلوب أنه وعند استخدام جدول البحث, تقوم بتحديث البيانات عن طريق الضغط على زر تحديث البيانات ومن ثم تقوم باستخدامه. (طبعاً لاداعي لتحديث البيانات في حال عدم اضافة اي جديد من بعد آخر تحديث قمت به). هل تحتاج اي مساعدة اضافية؟ انا جاهز بالخدمة.

  19. الباش مهندس حسين اسعد الله صباحك
    عندي مجموعة طلاب تحفيظ قران وارغب في عمل جدول لهم.
    المطلوب الاول هو: اريد عند كتابة عدد الاجزاء يظهر تلقائيا كم ختم هذا الطالب ختمة كما هو موضح أسفل، علما بأني مبتدأ وحاولت على الدالة if وعلى الدالة lookup ولكن لااعلم ماهي الدالة المناسبة.
    المطلوب الثاني: هل في الدالتين السابقتين لابد من الرتيب لانه عندما عملتها وكنت اكتب60 ثم اسفل30 لايقبل
    حفظك الله ورعاك يالغالي…
    عدد الختمات عدد الاجزاء
    1 30
    2 60
    3 90
    4 120

  20. الاستاذ حسين اشكرك جزيل الشكر لردودك وسعة صدرك واتمنى لو تزودني برقم اتصال للتواصل معك او ايملك .ولك جزيل الشكر.

    • حياك الله أخ ياسر. يمكنك التواصل من خلال صفحة “تواصل معنا” أو من خلال البريد الالكتروني husseinb [at] viaexcel.com طبعاً بدون فراغات مع @

  21. اولا مشكوووووووووووووووور و جزاك الله كل خير
    انا صراحة عندى استفسارات كتير
    اولها عندى جدول لمعدات الشركة و دة مصفوفة بجدول راسي مصحوب بعمودين او اكثر يمثلوا بيانت و اسعار كلل عنصر منهم و الشركة نشاطها تاجير المعدات و بكرر الجدول عدد ايام الشهر و بعد فهمى لمعادلة vlookup بقيت بدخل رقم العميل فى خانة الحجز و استحدثت خانة جديد لاستحضار اسم العميل

    المطلوب سيادتك عايز اطلع بجدول فى حجوزات اليوم مترتبة بحجوزات كل عميل يليه الاخر
    طبقتها بمعادلة vlookup لسحب اسم العميل و العنصر المؤجر و سعرة و كوده و كان املى اننى لما اعمل sort بناء على عمود رقم او اسم العميل هتترتب الاوردرات بناء على طلبى اللى هوة طلبيات عميل عميل
    بس مش راضي يرتب جدول محتواه خلياته معادلات .. خصوصا اننى استحدثت خانى بالجدول المسحوب بياناته لعرض الطلبيات اليومية و قلت ارقم يدوى و ارتب بناء على العمود المستحدث بس للاسف نفس النتيجة رافض يرتب الجدول اللى كل محتويات خلياته vlookup …… الف شكر ليكم مقدما

    • اهلاً أخ محمد. حياك الله دائماً.

      بخصوص سؤالك, المفروض ان اكسل يقبل عمل sort حتى ولو كانت الخلايا تحوي معادلات. قد يكون لديك مشكلة في كتابة دالة vlookup نفسها مثلاً؟ هل ممكن ترفع نسخة مثال عن ورقة العمل التي لديك لانظر في الأمر او بأقل تقدير, هل ممكن تنسخ لي هنا معادلة Vlookup التي كتبها؟

    • معالى الباشا
      هو انا للاسف مش عارف ازاى ممكن اعمل attach بتعليقى هذا
      فبعت لحضرتك الملف على ايميل سيادتك
      فعلا عاجز عن شكر سيادتك

      ربى يوفقكم ,, و ان كان هو ولا انتم .. كلاكما فى غنى عن دعائي
      فهو يعلم و انتم تستحقون

      • أهلاً أخ محمد. وصلني ملفك اخي الكريم والمشكلة كانت كالتالي:

        • في الجدول الأول, المشكلة كانت تتمثل بطريقة الاشارة الى جدول البحث حيث أنك لم تقم بتثبيته وبالتالي فهو سيظهر بيانات خاطئة عند نسخ المعادلة او فرز الجدول. راجع الروابط التالية لتوضيح أكثر.
        http://goo.gl/27hPSy
        http://goo.gl/epDQa2
        http://goo.gl/5CSgDg

        • في الجدول الثاني لديك نفس المشكلة ثم جرب ان تضع الجدول في ورقة عمل مستقلة. المعادلات ستعمل وسيتم الفرز دون مشاكل.

        في المرفق ستجد ملفك مع حل لمشكلة المراجع.
        http://viaexcel.com/ar/wp-content/uploads/2016/08/status-vrgn-2.xlsx

        • مشكوووووووووووووووووووووووور
          ربي يسعدك و يساعدك كيف عم تساعدنا

          • باشا انا خايف بس المشكلة ما تحلتش بملفى 🙁

            مازلت غير قادر على تريتب الحجوزات بناء على عمود العميل لحصر و عرض حجوزات كل عميل بشكل متتالى طبقا لاسم العميل

            هو حضرتك شايف انى المفروض استخدم انه نمط
            relative
            absolute
            mixed

            • أهلاً أخ محمد. ارسلت لك استفسار على ايميلك لتحديد مع اي جدول مازلت تواجه مشكلة الفرز لأنني لا اواجهها في الملف الذي ارفقته لك. بانتظار ردك.

              • و الله الواحد مش عارف يوفى اهتمامكم و جميلكم شكر

                حضرتك المشكلة بخصوص الجدول الايمن جدول حجوزات اليوم اللى بيبدا بخانة client المطلوب ترتيبة حضرتك بناء على محتويات column O اللى هو بيمثل اسم العميل صاحب الحجز

                للتوضيح حضرتك الجدول الايسر اللى بيبدا بـ status
                دة حضرتك بيمثل نواة جدول للـ items بتاعة الشركة كـ ‘data base’ و انا كان هدفى احجز من خلاله بزيارة raw لكل item و ادخل رقم العميل صاحب الحجز

                ومن خلال الجدول الايمن “اللى فيه المشكلة” اقدر اسحب عليه كل الحجوزات “items” & “clients” و اى داتا تانية اكون محتاجها فى عرض حجوزات اليوم و ارتبها من خلال column O ترتيب بناء على اسم العميل فلو العميل ليه اكتر من حجز تصتف حجوزاته بشكل متتالى و بعد كده تصنيف بناء على معاد تسليمنا الطلب للعميل و اخر اليوم تصنيف بناء على معاد استلمنا من العملاء

                اسف على الاطالة
                مليون شكر مقدمين مقدما
                وجب الاشارة على انهم غير كافيين 😀

                • معلش بجملة الجمايل يا سيدنا 😀

                  عندنا جدول افقى للعناصر و لنفرض كاميرات
                  و عندنا من نفس العنص تكرار
                  و انا عامل عمود للـ serial و منفذ طريقة ادخال بياناته بالـ
                  data validation drop down
                  كان طموحى لما اعمل sort للكاميرات من حيث المحجوز .. طبيعى هتصتف الكاميرات المحجوزة باعلى الجدول .. طوحى كان يا سيدنا فى ان drop down اللى محفوظة فىه سرايل الكاميرات اللى من نفس نوعها يصتف مع الكاميرا المحجوزة باول الصف فاختار منه السيريال بتاعها بسهولة ودون خطا كتابى لكن من الواضح ان
                  drop down عن طريق الـ data validation
                  بيرتبط بالخلية مش بالـ raw

                  الزيتونة يا سيدنا: محتاج طريقة للـ drop down الخاص بالـ validation او حتى طريقة جديدة لعمل drop down تجعله او تجبرة ينتمى للـ raw بتاعه مش للخلية بعينها

                  ربنا يوسع رحابة صدرك على احتمالنا :]

                • اهلاً أخي, استبدل معادلة اسم العمل في الجدول الثاني بالمعادلة التالية,
                  =IF(Sheet1!D4>0,Sheet1!E4,0)

                  بالنسبة للسؤال الثاني, طلبك غير واضح تماماً لي لذا افضل لو ترسل لي على الايميل مثال عما تريد لانظر في أمره واحاول المساعدة.

  22. السلام عليكم
    بالنسبه للموضوع في الاعلى لو تكررت القيمه اكثر من مره
    كيف يخرجها جميعها في خانات متعدده

    • اهلاً أخ ياسر. ممكن تحقيق ذلك لكن ليس بواسطة هذه الدالة, وانما دالة Index مع دوال اخرى حسب الحاجة. او باستخدام دوال مخصصة UDF مكتوبة بلغة VBA.

  23. السلام عليكم لوسمحتم هل توجد دالة لتطابق الاسماء ؟ مثل vlookupولكن للاسماء وشكراً.

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

  24. السلام عليكم
    لوسمحت عندي سؤال حول موضوع VLookUp uk انا عملت العملية كلها ونجحت لكن ظهر لدي ارقم صفر و بدون رقم و رقم خطا اللي انا صلحته باضافة if error سؤالي حول الصفر و البدون رقم كيف اضيف او اكمل على الفورملا .
    شكرا جزيلا وتحياتي لكم.

    =IFERROR(VLOOKUP(E4,’C:\Users\ammar\Desktop\[JUNIORS MONTH WISE.xls]Sheet1′!$B$4:$C$1648,2,FALSE), “NO Sale”)

    • أهلاً أخي. يمكنك كتابة دالة شرطيّة IF كالتالي,

      الشرط, الدالة OR وتضع لها 3 حدود, الأول نتيجة دالة البحث Vlookup = الصفر, الثاني نتيجة دالة البحث Vlookup = “” (تعني لاشئ), الثالث معادلة iserror والتي تتحقق من وجود خطأ,

      في الحال الموافقة (تحقق اي من الشروط اعلاه) إذاً اكتب لا يوجد مبيعات “No Sales”
      وإلا فاكتب قيمة دالة Vlookup ليقوم اكسل بجلبها لك.

      ارجو اعلامي في حال كان جوابي غير واضح لك. بالتوفيق.

  25. جزاك الله خيرا

  26. استاذنا الكريم

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

    اتمنى المساعدة فضلا .

    • حياك الله أخ يحيى. المشكلة ان اكسل دائماً مايقوم باعادة احتساب المعادلات المضافة بمجرد التغيير في ورقة العمل (كتغيير قيمة القائمة المنسدلة لديك او حتى حذفها). مارأيك بانشاء ماكرو يقوم باضافة دالة Vlookup في المكان المحدد ومن ثم حذف المعادلة وتثبيت القيمة. في هذه الحالة, صحيح انك ستفقد ميزة الحساب التلقائي لدالة Vlookup ولكن بنفس الوقت تحل مشكلة حذف قيمة القائمة المنسدلة.

      • شكرا لك استاذ حسين، لقيت حل بيمشي الحال، ولكن عندي استفسار بسيط واخلص من مشروعي بشكل كامل، المطلوب لو تكرمت انا عندي مجموعة كبيرة من الموظفين قرابة 10,000
        وكل موظف له الرقم الوظيفي الخاص به، عندي اللستة باسمائهم جميعا على شكل
        6 اعمدة بالشكل هذا ID ——NAME , ID —–NAME , ID ——-NAME اريد ان اعمل فورمولا
        بحيث لما اكتب الرقم الوظيف يظهر لي اسم الموظف فقط ، استخدمت الداله VLOOKUP ولكنها
        كما تعلم الدالة تبحث باول عمود بكامل الجدول وحاولت ان اجعلها تقراء العمود 1
        بالاضافة الى العمود 2 و 3 لاحصل على جميع الاسماء، ولكن لم افلح، فلا اعلم اي دالة ممكن
        انها تقوم بهذا العلم ؟ والف شكر لك استاذي حسين .

        شاهد الجدول

        http://s23.postimg.org/82t77xyaz/okoo.png

        • اهلاً أخي يحيى, يمكنك تطبيق ماطلبت عن طريق استخدام دالتي Index و Match. انظر المثال التالي رجاءاً واعلمني في حال احتجت اي مساعدة,

          http://viaexcel.com/ar/wp-content/uploads/2016/02/Book1.xlsx

          • يعطيك العافية يا استاذ حسين، الملف يعمل بشكل ممتاز، ولكن اواجه نفس مشكلة VLOOK وهي اني في حال قمت بكتابة اسم موظف مؤقت كتابه وليس عن طريق البحث تتمسح صيغة الدالة اللي في خانة الـ Name اللي في نفس الخلية اللتي تظهر النتيجة، هل هناك طريقة لتثبيت الصيغة؟ بحيث اني لو اكتب الاسم مباشرة دون خاصية البحث بالرقم تعود الصيغة بالخلية كما هي عندما اقوم مره اخرى بالبحث عن طريق رقم الموظف؟

            واشكرك على التجاوب استاذي الكريم ..

            • حياك الله أخ يحيى. يمكنك حل هكذا مشكلة عن طريق اضافة كود VBA لكن اقترح عليك حل اسهل وأوفر للمساحة. اقترح ان تقوم باضافة خليّة مساعدة تكتب فيها اسم الموظف المؤقت, مع تعديل معادلة البحث في مكانها الأصلي لتقوم باظهار اسم الموظف المساعد لو لم يكن فارغاً وإلا فانها تنفذ عملية البحث. عدلت لك المثال السابق لتطبيق الحل المقترح. الشروط كالتالي,
              – لو كان اسم الموظف المؤقت + رقم الموظف فارغين, اذاً اظهر فراغ.
              – لو كان اسم الموظف المؤقت غير فارغ, اظهر اسم الموظف المؤقت وإلا فاظهر اسم الموظف بعملية البحث.

              http://viaexcel.com/ar/wp-content/uploads/2016/02/Book1.xlsx

              بالتوفيق عزيزي.

  27. السلام عليكم
    استاذي الكريم
    حاولت بالفعل تطبيق داله VLOOKUP علي شيت اكسيل عندي دائما يأتي بالنتيجة خطأ
    اريد ان اعرف هل يوجد تحفظات لابد من وضعها في الاعتبار قبل البدء في تنفيذ المعادلة

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

  28. محاسب / محمود رزيقه

    أشكرك جدا على الشرح الطيب
    جعله الله في ميزان حسناتكم

  29. استاذ حسين

    عند وجود رقم . مثلا مئة فاتوره ، منها عدد ثلاث فواتير تم تسديدهم ببطاقة ائتمان واحده وللعملية الائتمانية رقم تفويض

    هل استطيع ان اجد معادلة تستخرج الآتى

    اجمالى قيمة الثلاث فواتير الخاصة بهذا التسديد ببطاقة الائتمان بخاصية البحث برقم التفويض مع العلم ان من ضمن بيانات الفواتير طريقة الدفع و رقم التفويض مدخله ضمن عملية البيع

    • حياك الله اخ فهد. يمكنك استخدام الدالة Countif لاكتشاف التفويضات المتكررة, والدالة Sumif لجمع قيم الفواتير التي تمت باستخدام نفس الكارد. المعادلتين المذكورتين غير مشروحات في الموقع لكن يمكنك ايجاد شروحات لهما على النت. في حال اردت اي مساعدة, اخبرنا وانا بالخدمة.

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

  31. السلام عليكم

    عندى سؤال ضرورى هل يوجد داله لكتابة التاريخ تلقائى عند ادخال رقم معين فى ورقة معينه
    مثال :- انا عندى رقم سيارة فى ورقة 1 فى خلية A1 هل عندما يتم كتابة رقم السيارة فى ورقة 2 يتم ادخال التاريخ تلقائى فى ورقة 1 خلية رقم B1

    شكرا جزيلا لحضرتك ,,,,,

    • أهلاً أخ أحمد. اعتقد أنك تقصد تاريخ اليوم اليس كذلك؟ لو كان فهمي صحيحاً, إذا عليك استخدام دالة IF ودمجها بدالة Today. المشكلة ان قيمة دالة التاريخ المذكورة, سيتم تعديلها تلقائياً من قبل اكسلفي اليوم التالي لتكتب تاريخ الغد. ففي حال كنت تهتم لاستخدام الملف لاظهار بيانات اكثر من يوم, ستضطر بهذه الحالة لاستخدام كود VBA ليوقف هذا التحديث المذكور.

  32. جزاك الله الف خير على هذا الشرح الاكثر من رائع .
    هل من الممكن لوانا عندي شيت فيه بيانات عملاء وملف فيه صور لهؤلاء العملاء ممكن استخدم معادله تجعل كل اسم عميل امامه صورته . ارجو الافاده

  33. شكرا جزيل على المعلومه

  34. شكرا لك

    بحثت في اكثر من موقع عن شرح لهذه الدالة ,, ويعلم الله ما فهمتها الا من موقعك ,,

    الله يجزاك كل خير ويكتب هذا العمل في ميزان حسناتك

  35. ربنا يجعل ذالك فى ميزان حسانتكم فى الاخرة ويجزيكم خيراً فى الدنيا والاخرة

أضف تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *