الحصول على الأرقام المفقودة من بين مجموعة أرقام
السلام عليكم و رحمة الله و بركاته
موضوع اليوم هو سؤال لأحد الأخوة في منتدى
Excel4Us
على هذا الرابط
من هنا
و فكرة السؤال حول إمكانية الحصول على الارقام المفقودة من ضمت تسلسل فواتير, كما هو في الصورة التالية :
فكما نلاحظ أن بعض أرقام الفواتير في السلسلة أعلاه غير موجود مثل الرقم 4 و 6 و 7 و غيرها من الأرقام
و السؤال كيف يمكنني معرفة هذه الأرقام المفقودة
الحل يكون بإستخدام معادلات الصفيف array formula
حيث سنستخدم معادلة تقوم بإسترجاع الرقم المفقود, و لبناء هذه المعادلة نحتاج لمعرفة الحد الأعلى و الحد الأدنى للقيم و من ثم بناء سلسلة رقمية بينهم و مقارنتها مع الأرقام الموجودة في النطاق أعلاه و هو من :
A2:A11
و لعمل ذلك في الخلية C2 سنقوم بكتابة المعادلة التالية :
ثم نقوم بالضغط على Ctrl+Shift+Enter
و لفهم آلية عملها سنقوم بتجزئتها لعدة أجزاء:
فمعادلة min تقوم بإيجاد الحد الأدنى من السلسلة الرقمية
ومعادلة max تقوم بإيجاد الحد الأعلى من السلسلة الرقمية
و لبناء سلسلة رقمية بينهما نستخدم معادلتي
row
indirect
ثم بإستخدام معادلة match لمطابقة أرقام المصفوفة و أرقام النطاق الذي إخترناه
A2:A11
سيووم الإكسيل بإعطائنا تسلسل الأرقام حسب ما هي في النطاق أعلاه, و لكن ما يهمنا هنا هو الارقام التي لا تملك تسلسل و التي ستعطي خطأ
#N/A
في المعادلة
و هي ما يحقق طلبنا, لذلك نستخدم معادلة ISNA للحصول عليها
و عند هذه النقطة نكون قد حصلنا على مصفوفة جديدة بالأرقام المفقودة و لترتيبها و إعطائها أرقام نقوم بربطها من خلال معادلة IF مع سلسلة رقمية جديدة تعتمد على بناء تسلسل رقمي من 1 إلى أكبر قيمة
و من ثم يتم ربط المتغيرات بالمعادلة الأخيرة Small للحصول على الارقام بتسلسل ثابت
و هي المرحلة الأخيرة المرتبطة بالمعادلة index
كما هو في العامود C
و بذلك تكون القضية قد تم حلها بشكل كامل
لتحميل ملف الإكسيل و به المعادلات من خلال
الرابط
أتمنى أن يستفيد الجميع منها
دمتم في حفظ الله
يحيى حسين
Excel MVP
موضوع اليوم هو سؤال لأحد الأخوة في منتدى
Excel4Us
على هذا الرابط
من هنا
و فكرة السؤال حول إمكانية الحصول على الارقام المفقودة من ضمت تسلسل فواتير, كما هو في الصورة التالية :
فكما نلاحظ أن بعض أرقام الفواتير في السلسلة أعلاه غير موجود مثل الرقم 4 و 6 و 7 و غيرها من الأرقام
و السؤال كيف يمكنني معرفة هذه الأرقام المفقودة
الحل يكون بإستخدام معادلات الصفيف array formula
حيث سنستخدم معادلة تقوم بإسترجاع الرقم المفقود, و لبناء هذه المعادلة نحتاج لمعرفة الحد الأعلى و الحد الأدنى للقيم و من ثم بناء سلسلة رقمية بينهم و مقارنتها مع الأرقام الموجودة في النطاق أعلاه و هو من :
A2:A11
و لعمل ذلك في الخلية C2 سنقوم بكتابة المعادلة التالية :
ثم نقوم بالضغط على Ctrl+Shift+Enter
و لفهم آلية عملها سنقوم بتجزئتها لعدة أجزاء:
فمعادلة min تقوم بإيجاد الحد الأدنى من السلسلة الرقمية
ومعادلة max تقوم بإيجاد الحد الأعلى من السلسلة الرقمية
و لبناء سلسلة رقمية بينهما نستخدم معادلتي
row
indirect
ثم بإستخدام معادلة match لمطابقة أرقام المصفوفة و أرقام النطاق الذي إخترناه
A2:A11
سيووم الإكسيل بإعطائنا تسلسل الأرقام حسب ما هي في النطاق أعلاه, و لكن ما يهمنا هنا هو الارقام التي لا تملك تسلسل و التي ستعطي خطأ
#N/A
في المعادلة
و هي ما يحقق طلبنا, لذلك نستخدم معادلة ISNA للحصول عليها
و عند هذه النقطة نكون قد حصلنا على مصفوفة جديدة بالأرقام المفقودة و لترتيبها و إعطائها أرقام نقوم بربطها من خلال معادلة IF مع سلسلة رقمية جديدة تعتمد على بناء تسلسل رقمي من 1 إلى أكبر قيمة
و من ثم يتم ربط المتغيرات بالمعادلة الأخيرة Small للحصول على الارقام بتسلسل ثابت
و هي المرحلة الأخيرة المرتبطة بالمعادلة index
كما هو في العامود C
و بذلك تكون القضية قد تم حلها بشكل كامل
لتحميل ملف الإكسيل و به المعادلات من خلال
الرابط
أتمنى أن يستفيد الجميع منها
دمتم في حفظ الله
يحيى حسين
Excel MVP
ممكن اعادة رفع الملف
ردحذف