استخدام دالة Offset فى ارجاع قيمة من شيت آخر

[علاء رسلان]

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

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

Book1.zip

[طارق محمود]

السلام عليكم

أخي الكريم

لابد من التوضيح

 

(1) مامعني عناوين الأعمدة في الورقتين

Q. , A. , C

2 , 5 , 21 , 101 , 111

 

(2) ماذا تريد تحديدا إذا أخذنا اول عنصر (Code 10038) كمثال

[علاء رسلان]

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

هذا جزء من ملف كبير خاص بخروج أصناف بمخزن انا اختصرت اشياء كثيرة

العناوين هى اختصار لكمية و مبلغ و ادارة

الأرقام هى للادارات

لدينا الكود الأول 10038 مع الأدارة 2 مثلا المفترض تقاطع الصف مع العمود بالورقة المصدر كون الناتج بالورقة الهدف  Spare و هو المطلوب الحصول عليه

اما اذا كان 10038 مع الأدارة 101 يكون تقاطع الصف مع العمود بالورقة المصدر يكون الناتج بالورقة الهدف USA

و هكذا

و هذا ما اريده تحديدا حاولت بإستخدام دالة Vlookup

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

و الناتج يعتمد على شرطين معا الكود و الأدارة

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

[طارق محمود]

ضع المعادلة التالية في الخلية F2 من الورقة Target

=OFFSET(Source!$A$1,MATCH(A2,Source!A:A,0)-1,MATCH(E2,Source!$1:$1,)-1)

ثم انسخها للأسفل

[علاء رسلان]

الله عليك يا أستاذ .. بارك الله لك

هو بس الكود به خطأ بسيط فى الفواصل

=OFFSET(Source!$A$1;MATCH(A2;Source!A:A;0)-1;MATCH(E2;Source!$1:$1;)-1)

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

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

[ابو اياد ( الاسيوطى )]

بعد اذن استاذنا الكبير العلامه الفذ // طارق محمود

حين ألصق المعادله اوفسيت لا تقبل ارجو من سيادتكم مراجعتها لآنى فى حاجه أليها

 

تقبل تحياتى

 

تم ارفاق الملف بعد التعديل 

 

جل من لا يسهو

OFFSET&INDEX&MATCH.rar

[قنديل الصياد]

[عطاء الله]

أشكر الأستاذ طارق ومحمود على حلهما وبعد إذن الأستاذ طارق والأخ محمود السيوطي هذا حل آخر الذي طلبت على ما أعتقد والله أعلم وهذ حسب ما

Book1.rar

[علاء رسلان]

جزيل الشكر لكم جميعا

دالة الأستاذ طارق محمود حصلت على نتائج صحيحة بعد تعديل فواصل الكود

دالة الأستاذ محمود الأسيوطى النتائج غير صحيحة

دالة الأستاذ عطاء الله حصلت على نتائج صحيحة و اعتقد هى تعديل لدالة الأستاذ محمود الأسيوطى ..

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

شكرا لكم كثيرا

دمتم بخير و اعزكم الله .

[ابو اياد ( الاسيوطى )]

ارجو ان ترى المرفق الجديد المشاركه 6#

بعد تعديله الاجابه مقارنه مع اجابة  المهندس العلامه // طارق محمود 

 

تقبل تحياتى

 

 

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

 

جميع الحلول لا توجد فيها عمود اضافى الحلول كلها مثل بعضها

 

ارجو التوضيح ماذا تقصد بالعمود الاضافى

[علاء رسلان]

شكرا جزيلا للاستاذ محمود الأسيوطى و المعادلة الجديدة بالفعل تعطى نتائج سليمة..

العمود الأضافى ستجده فى مرفق الاستاذ عطاء الله بصفحة المصدر حيث و ضع عمود مسلسل بدأ من 1 .. العمود B

لا ادرى ما الغرض منه الا انه لازم لصحة النتائج

الصراحة ما شاء الله عليكم جميعا .. شكرا جزيلا لكم .

[عطاء الله]

اخي علا أنا إستعملت العمود الإضافي لأبين لك كيفة إستعمال الدالة v في هذه الحالة لأنك طرحت السؤال عن الدالة لماذ ام تعطي نتيجة

أم عن طريقة الحل فهي توجد عدة طرق من بينها التي تفضل بها لأستاذ طارق وكذا السيوطي وشكرا

[علاء رسلان]

شكرا جزيلا لكم جميعا .. أسعدنى كثيرا هذا الأهتمام الكريم منكم

ووفرة الحلول لمشكلتى هذه .. وافر التحية لكم جميعا

دمتم بخير و أعزكم الله

محدثكم علاء رسلان .

[ابو اياد ( الاسيوطى )]

أخى علاء أليك دعوه منى لتغيير اسمك الى العربيه فلغتنا هى هويتنا العربيه

 

تقبل فائق احترامى وحبى

[علاء رسلان]

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