این وضعیت بسیار متداول است: شما باید برای تجزیه و تحلیل داده ها یا اهداف گزارش ، داده ها را از برگه های متعدد اکسل ادغام کنید. کپی و چسباندن داده ها می تواند کار کند ، اما این یک روند مستعد خطا است و دامنه حاصل پویا نخواهد بود (بسیاری از افراد دقیقاً دوست دارند که داده های خود پویا باشند). اما نگران نباشید – Excel شما را با توابع VSTACK و HSTACK پوشانده است.
این وضعیت بسیار متداول است: شما باید برای تجزیه و تحلیل داده ها یا اهداف گزارش ، داده ها را از برگه های متعدد اکسل ادغام کنید. کپی و چسباندن داده ها می تواند کار کند ، اما این یک روند مستعد خطا است و دامنه حاصل پویا نخواهد بود (بسیاری از افراد دقیقاً دوست دارند که داده های خود پویا باشند). اما نگران نباشید – Excel شما را با توابع VSTACK و HSTACK پوشانده است.
این توابع در مایکروسافت 365 به طور خاص به منظور ترکیب آرایه ها یا دامنه ها در یک موجود واحد معرفی شدند. آنها همچنین چیزها را پویا نگه می دارند و آنها را برای داده هایی که به طور مداوم تغییر می کنند ایده آل می کنند. من به شما نشان می دهم که چگونه از آنها استفاده کنید ، و همچنین آنچه می توانید انجام دهید تا آنها را با استفاده از عملکرد فیلتر برای رشد داده ها حساب کنید.
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(range1, [range2], ...)
پارامتر Range1 اولین دامنه یا آرایه ای است که انباشته می شود. Range2 و پارامترهای اختیاری دامنه های بعدی و آرایه هایی هستند که در سمت راست نمونه های قبلی قرار می گیرند. باز هم ، این به ترتیب وارد شده بستگی دارد.
در اینجا نمونه ای از عملکرد HSTACK در عمل آورده شده است:
=HSTACK(Sheet1!A1:E3, Sheet2!A1:E3, Sheet3!A1:E3, Sheet4!A1:E3)
برای عملکرد HSTACK همانطور که انتظار می رفت ، دامنه ها باید همان تعداد ردیف ها را در تمام برگه ها داشته باشند. همانطور که با عملکرد VSTACK ، عدم رعایت این عمل منجر به سلول های خالی یا صفرها خواهد شد.
ایجاد VSTACK و HSTACK پویاتر
همیشه بهتر است که رشد را به خود اختصاص دهیم
تاکنون داده ها پویا است ، به این معنی که اگر یکی از مقادیر موجود در محدوده انتخاب شده تغییر کند ، موارد مربوطه در آرایه حاصل از VSTACK یا HSTACK به طور خودکار به روز می شود. مشکل این است که اگر دامنه ها رشد کنند ، توابع این کار را به صورت خودکار انتخاب نمی کنند.
یکی از راه های غلبه بر این ، تبدیل هر یک از این محدوده ها به یک میز است. در اینجا مراحل انجام این کار آورده شده است:
- دامنه ، از جمله هدرها را انتخاب کنید.
- برگه درج را انتخاب کنید.
- در گروه فرمان جداول روبان ، روی جدول کلیک کنید.
- در پاپ آپ ، جدول من را بررسی کنید.
- OK را کلیک کنید
اگر نمی خواهید دردسرهای ایجاد جداول را طی کنید ، می توانید ردیف ها و ستون های اضافی را انتخاب کنید تا داده هایی را که هنوز اضافه نشده است حساب کنید. به عنوان مثال ، اگر سلولهای استفاده شده A2: A21 باشند ، می توانید به جای آن A2: A51 را انتخاب کنید – این 30 سلول اضافی است.
متوجه خواهید شد که ردیف ها یا ستون هایی وجود دارند که خالی یا پر از صفر هستند که سلولهای خالی باید باشند. این باعث می شود ورق به نظر برسد ، اما می توانید آنها را با عملکرد فیلتر حذف کنید.
در اینجا نمونه ای از فرمول به نظر می رسد:
=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<>""))
اگر می دانید عملکرد فیلتر چگونه کار می کند ، اولین پارامتر آرایه ای است که می خواهیم فیلتر کنیم. پارامتر دوم شرایطی است که آن را با استفاده از اپراتورهای منطقی اکسل فیلتر می کند. در اصل ، فرمول هر ردیف را بدون ارزش از نتیجه حذف می کند.
فراموش نکنید که داده ها را در صورت نیاز انتقال دهید
اطمینان حاصل کنید که داده ها در قالب مورد نیاز شما هستند
همانطور که قبلاً ذکر شد ، عملکرد VSTACK برای داده هایی که نیاز به تجزیه و تحلیل دارند مناسب است ، در حالی که عملکرد HSTACK برای داده هایی که باید مقایسه شوند مناسب است. اما اگر برای مقایسه یا نتیجه HSTACK برای تجزیه و تحلیل به نتیجه VSTACK نیاز دارید ، می توانید داده ها را انتقال دهید که ردیف ها و ستون ها را می چرخاند.
می توانید داده ها را با چند کلیک از نظر آماری انتقال دهید. در اینجا نحوه انجام این کار آورده شده است:
- دامنه مورد نظر برای انتقال را انتخاب کنید.
- روی یک سلول خالی راست کلیک کرده و در منو Paste Special را انتخاب کنید.
- انتقال را در کادر گفتگو بررسی کنید.
- OK را کلیک کنید
اگر می خواهید داده ها را به صورت پویا (توصیه شده) انتقال دهید ، می توانید از عملکرد Transpose استفاده کنید. در اینجا یک مثال آورده شده است:
=TRANSPOSE(VSTACK(Sheet1!A2:D21, Sheet2!A2:D21, Sheet3!A2:D21))
استاد انباشت در اکسل شوید
توابع VSTACK و HSTACK ضمن حفظ عملکرد پویا ، ترکیب داده های کارآمد را از چندین برگه امکان پذیر می کنند. استفاده از سایر توابع نیز بسیار آسان است اگر به عنوان مثال ، شما می خواهید با مجموعه داده های در حال رشد کار کنید یا نیاز به انتقال داده ها داشته باشید.
علاوه بر این ، استفاده از آنها را با عملکرد LET در اکسل نیز در نظر بگیرید. این امکان را به شما می دهد تا متغیرهایی ایجاد کنید ، ورق ها را به عنوان مقادیر به آنها اختصاص دهید و آنها را در فرمول ارجاع دهید. این باعث می شود فرمول قابل خواندن و کارآمدتر انسانی باشد.