هذا السيناريو مألوف جدًا: تحتاج إلى دمج بيانات من عدة أوراق Excel لتحليلها أو لإنشاء تقارير. قد تنجح عملية النسخ واللصق، ولكنها عرضة للأخطاء، ولن يكون النطاق الناتج ديناميكيًا (يفضل الكثيرون أن تكون بياناتهم ديناميكية). ولكن لا تقلق، Excel يوفر لك الحل مع وظيفتي VSTACK و HSTACK.
تم تقديم هاتين الوظيفتين في Microsoft 365 خصيصًا لدمج المصفوفات أو النطاقات في كيان واحد. كما أنهما تحافظان على ديناميكية البيانات، مما يجعلهما مثاليتين للبيانات التي تتغير باستمرار. سأوضح لك كيفية استخدامهما، بالإضافة إلى ما يمكنك فعله لجعلهما تأخذان في الاعتبار نمو البيانات باستخدام وظيفة FILTER.
روابط سريعة
VSTACK
لتحضير البيانات للتحليل
تجمع وظيفة VSTACK البيانات من نطاقات أو مصفوفات متعددة عن طريق تكديسها عموديًا – واحدة فوق الأخرى وبالترتيب الذي يتم إدخالها به. والنتيجة هي مصفوفة أطول يمكن استخدامها مع أدوات التحليل الإحصائي مثل الجداول المحورية (PivotTables) والمخططات.
=VSTACK(range1, [range2], ...)
المعامل range1 هو النطاق أو المصفوفة الأولى التي تريد تكديسها. المعامل range2 والمعاملات الاختيارية الأخرى هي النطاقات والمصفوفات اللاحقة التي سيتم وضعها أسفل النطاقات السابقة.
إليك مثال على الشكل الذي سيبدو عليه تكديس البيانات باستخدام وظيفة VSTACK:
=VSTACK(Sheet1!A2:D21, Sheet2!A1:D21, Sheet3!A1:D21, Sheet4!A1:D21)
لكي تعمل وظيفة VSTACK، يجب أن يكون للأعمدة نفس الهيكل عبر جميع النطاقات. وإلا، فستُرجع الوظيفة خلايا فارغة أو أصفارًا في الخلايا الفارغة (على الرغم من أنها ستظل تعمل)، وسيبدو الجدول غير مرتب.
HSTACK
الأفضل للمقارنات جنبًا إلى جنب
تقوم دالة HSTACK بدمج البيانات أفقيًا عن طريق تكديس كل نطاق أو مصفوفة محددة جنبًا إلى جنب. ينتج عن هذا مصفوفة أوسع مناسبة للمقارنات جنبًا إلى جنب في أدوات مثل التقارير ولوحات المعلومات.
=HSTACK(النطاق1, [النطاق2], ...)
المعامل النطاق1 هو النطاق أو المصفوفة الأولى المراد تكديسها. النطاق2 والمعاملات الاختيارية هي النطاقات والمصفوفات اللاحقة التي سيتم وضعها على يمين النطاقات السابقة. مرة أخرى، يعتمد هذا على الترتيب الذي يتم إدخالها به.
إليك مثال على كيفية عمل دالة HSTACK:
=HSTACK(Sheet1!A1:E3, Sheet2!A1:E3, Sheet3!A1:E3, Sheet4!A1:E3)
لكي تعمل دالة HSTACK كما هو متوقع، يجب أن تحتوي النطاقات على نفس عدد الصفوف في جميع الأوراق. كما هو الحال مع دالة VSTACK، سيؤدي عدم الالتزام بهذه الممارسة إلى خلايا فارغة أو أصفار.
جعل VSTACK و HSTACK أكثر ديناميكية
من الأفضل دائمًا مراعاة النمو
حتى الآن، البيانات ديناميكية، مما يعني أنه إذا تغيرت إحدى القيم في النطاقات المحددة، فسيتم تحديث القيم المقابلة في المصفوفة الناتجة من VSTACK أو HSTACK تلقائيًا. المشكلة هي أنه إذا نمت النطاقات، فلن تلتقط الدالات هذا تلقائيًا.
إحدى طرق التغلب على ذلك هي تحويل كل نطاق من هذه النطاقات إلى جدول. فيما يلي خطوات القيام بذلك:
- حدد النطاق، بما في ذلك العناوين.
- حدد علامة التبويب إدراج.
- انقر فوق جدول في مجموعة أوامر الجداول في الشريط.
- في النافذة المنبثقة، حدد الجدول الخاص بي يحتوي على رؤوس.
- انقر فوق موافق.
إذا كنت لا ترغب في المرور بمتاعب إنشاء الجداول، فيمكنك تحديد صفوف وأعمدة إضافية لمراعاة البيانات التي لم تتم إضافتها بعد. على سبيل المثال، إذا كانت الخلايا المستخدمة هي A2:A21، فيمكنك اختيار A2:A51 بدلاً من ذلك – أي 30 خلية إضافية.
ستلاحظ وجود صفوف أو أعمدة فارغة أو مملوءة بالأصفار حيث يجب أن تكون الخلايا الفارغة. سيجعل هذا الورقة تبدو غير مرتبة، ولكن يمكنك إزالتها باستخدام دالة FILTER.
إليك مثال على الشكل الذي ستبدو عليه الصيغة:
=FILTER(VSTACK(Sheet1!A2:D51, Sheet2!A2:D51, Sheet3!A2:D51, Sheet4!A2:D51), VSTACK(Sheet1!A2:A51<>"", Sheet2!A2:A51<>"", Sheet3!A2:A51<>
"", Sheet4!A2:A51<>""))إذا كنت تعرف طريقة عمل دالة FILTER، فالوسيطة الأولى هي النطاق الذي نريد تصفيته. أما الوسيطة الثانية فهي الشرط الذي يقوم بالتصفية باستخدام عوامل التشغيل المنطقية في Excel. باختصار، تستبعد هذه الصيغة أي صف بدون قيمة من النتيجة.
لا تنسَ تبديل البيانات عندما تحتاج إلى ذلك
تأكد من أن البيانات بالصيغة التي تحتاجها
كما ذكرنا سابقًا، فإن دالة VSTACK هي الأنسب للبيانات التي تتطلب تحليلًا، بينما دالة HSTACK مناسبة تمامًا للبيانات التي تحتاج إلى مقارنة. ولكن إذا كنت بحاجة إلى نتيجة VSTACK للمقارنة أو نتيجة HSTACK للتحليل، فيمكنك تبديل البيانات، مما سيقلب الصفوف والأعمدة.
يمكنك تبديل البيانات إحصائيًا ببضع نقرات. إليك كيفية القيام بذلك:
- حدد النطاق الذي تريد تبديله.
- انقر بزر الماوس الأيمن فوق خلية فارغة وحدد لصق خاص في القائمة.
- حدد تبديل في مربع الحوار.
- انقر فوق موافق.
إذا كنت تريد تبديل البيانات ديناميكيًا (موصى به)، فيمكنك استخدام دالة TRANSPOSE. إليك مثال:
=TRANSPOSE(VSTACK(Sheet1!A2:D21, Sheet2!A2:D21, Sheet3!A2:D21))
أتقن عملية التجميع في Excel
تتيح دالتا VSTACK و HSTACK دمج البيانات بكفاءة من أوراق متعددة مع الحفاظ على الوظائف الديناميكية. كما أنها سهلة الاستخدام تمامًا مع الدوال الأخرى إذا كنت تريد، على سبيل المثال، العمل مع مجموعات بيانات متزايدة أو تحتاج إلى تبديل البيانات.
علاوة على ذلك، ضع في اعتبارك استخدامها مع دالة LET في Excel أيضًا. سيسمح لك ذلك بإنشاء متغيرات وتعيين الأوراق لها كقيم والإشارة إليها في الصيغة. سيجعل هذا الصيغة أكثر قابلية للقراءة وفعالية.
