آرایه های پویا در اکسل

آرایه های پویا چیست؟

در سپتامبر 2018، مایکروسافت به روز رسانی را منتشر کرد که ابزار کاملاً جدیدی را به مایکروسافت اکسل اضافه می کند: Dynamic Arrays و 7 عملکرد جدید برای کار با آنها. این چیزها، بدون اغراق، به طور اساسی تمام تکنیک های معمول کار با فرمول ها و توابع را تغییر می دهد و به معنای واقعی کلمه هر کاربر را نگران می کند.

برای توضیح اصل مطلب به یک مثال ساده توجه کنید.

فرض کنید یک جدول ساده با داده های شهر-ماه داریم. اگر هر سلول خالی را در سمت راست برگه انتخاب کنیم و فرمولی را وارد کنیم که نه به یک سلول، بلکه بلافاصله به یک محدوده پیوند می دهد، چه اتفاقی می افتد؟

در تمامی نسخه های قبلی اکسل، پس از کلیک بر روی وارد محتویات تنها یک سلول اول B2 را دریافت می کنیم. دیگر چگونه؟

خوب، یا می‌توان این محدوده را در نوعی تابع تجمیع مانند =SUM(B2:C4) قرار داد و یک کل کل برای آن بدست آورد.

اگر به عملیات پیچیده تری نسبت به جمع اولیه نیاز داشتیم، مانند استخراج مقادیر منحصر به فرد یا Top 3، باید فرمول خود را به عنوان فرمول آرایه با استفاده از میانبر صفحه کلید وارد کنیم. کلیدهای Ctrl+تغییر+وارد.

حالا همه چیز فرق کرده است.

اکنون پس از وارد کردن چنین فرمولی، به سادگی می توانیم بر روی آن کلیک کنیم وارد – و در نتیجه بلافاصله تمام مقادیر uXNUMXbuXNUMXb را که به آنها ارجاع دادیم دریافت کنید:

این جادو نیست، بلکه آرایه های پویا جدیدی است که مایکروسافت اکسل اکنون دارد. به دنیای جدید خوش آمدید 🙂

ویژگی های کار با آرایه های پویا

از نظر فنی، کل آرایه پویا ما در سلول اول G4 ذخیره می شود و تعداد سلول های مورد نیاز را به سمت راست و پایین با داده های آن پر می کند. اگر هر سلول دیگری را در آرایه انتخاب کنید، پیوند در نوار فرمول غیرفعال خواهد بود و نشان می دهد که ما در یکی از سلول های "فرزند" هستیم:

تلاش برای حذف یک یا چند سلول "کودک" به چیزی منجر نمی شود - اکسل بلافاصله آنها را دوباره محاسبه و پر می کند.

در عین حال، می توانیم با خیال راحت به این سلول های "کودک" در فرمول های دیگر اشاره کنیم:

اگر اولین سلول یک آرایه را کپی کنید (به عنوان مثال، از G4 به F8)، کل آرایه (مرجع آن) در همان جهتی حرکت می کند که در فرمول های معمولی وجود دارد:

اگر نیاز به جابجایی آرایه داشته باشیم، برای جابجایی کافی است (با ماوس یا ترکیبی از کلیدهای Ctrl+X, کلیدهای Ctrl+V، دوباره، تنها اولین سلول اصلی G4 - پس از آن، به مکان جدیدی منتقل می شود و کل آرایه ما دوباره گسترش می یابد.

اگر نیاز دارید به جای دیگری از برگه به ​​آرایه پویا ایجاد شده مراجعه کنید، می توانید از کاراکتر ویژه # ("پوند") بعد از آدرس سلول اصلی آن استفاده کنید:

به عنوان مثال، اکنون می توانید به راحتی یک لیست کشویی در یک سلول ایجاد کنید که به آرایه پویا ایجاد شده اشاره دارد:

خطاهای آرایه پویا

اما اگر فضای کافی برای گسترش آرایه وجود نداشته باشد، یا سلول‌هایی که قبلاً توسط داده‌های دیگر در مسیر آن اشغال شده باشند، چه اتفاقی می‌افتد؟ با یک نوع اساسی جدید از خطاها در اکسل آشنا شوید - #منتقل کردن! (#ریختن!):

مثل همیشه، اگر روی نماد با الماس زرد و علامت تعجب کلیک کنیم، توضیح دقیق تری از منبع مشکل دریافت می کنیم و می توانیم به سرعت سلول های مزاحم را پیدا کنیم:

اگر آرایه از صفحه خارج شود یا به سلول ادغام شده برخورد کند، خطاهای مشابهی رخ خواهد داد. اگر مانع را بردارید، همه چیز بلافاصله در پرواز اصلاح می شود.

آرایه های پویا و میزهای هوشمند

اگر آرایه پویا به جدول "هوشمند" ایجاد شده توسط میانبر صفحه کلید اشاره کند کلیدهای Ctrl+T یا با صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول)، سپس کیفیت اصلی خود - اندازه خودکار - را نیز به ارث خواهد برد.

هنگام افزودن داده های جدید به پایین یا سمت راست، جدول هوشمند و محدوده دینامیکی نیز به طور خودکار کشیده می شوند:

با این حال، یک محدودیت وجود دارد: ما نمی توانیم از یک مرجع محدوده پویا در فرومول های داخل یک جدول هوشمند استفاده کنیم:

آرایه های پویا و سایر ویژگی های اکسل

باشه تو بگو همه اینها جالب و خنده دار است. مانند قبل نیازی به کشش دستی فرمول با ارجاع به سلول اول محدوده اصلی به پایین و به راست و همه اینها نیست. و این همه؟

نه کاملا.

آرایه های پویا فقط ابزار دیگری در اکسل نیستند. اکنون آنها در قلب (یا مغز) مایکروسافت اکسل - موتور محاسبات آن - جاسازی شده اند. این بدان معناست که دیگر فرمول‌ها و توابع اکسل که اکنون برای ما آشنا هستند نیز از کار با آرایه‌های پویا پشتیبانی می‌کنند. بیایید به چند مثال نگاهی بیندازیم تا به شما ایده ای از عمق تغییرات رخ داده ارائه دهیم.

ترانزیت

برای جابجایی یک محدوده (مبادله ردیف و ستون) مایکروسافت اکسل همیشه یک عملکرد داخلی داشته است TRANSP (انتقال). اما برای استفاده از آن، ابتدا باید محدوده نتایج را به درستی انتخاب کنید (مثلاً اگر محدوده ورودی 5×3 بود، پس باید 3×5 را انتخاب کرده باشید)، سپس تابع را وارد کرده و دکمه را فشار دهید. ترکیبی کلیدهای Ctrl+تغییر+وارد، زیرا فقط در حالت فرمول آرایه می تواند کار کند.

اکنون می توانید فقط یک سلول را انتخاب کنید، همان فرمول را در آن وارد کنید و روی عادی کلیک کنید وارد - آرایه پویا همه چیز را به تنهایی انجام می دهد:

جدول ضرب

این مثالی است که وقتی از من خواسته شد مزایای فرمول‌های آرایه را در اکسل تجسم کنم، بیان کردم. حال برای محاسبه کل جدول فیثاغورث کافی است در خانه اول B2 قرار بگیرید، فرمولی را وارد کنید که دو آرایه (مجموعه اعداد عمودی و افقی 1..10) را ضرب می کند و به سادگی بر روی آن کلیک کنید. وارد:

چسب و تبدیل کیس

آرایه ها نه تنها می توانند ضرب شوند، بلکه با عملگر استاندارد & (امپرسند) به هم چسبانده می شوند. فرض کنید باید نام و نام خانوادگی را از دو ستون استخراج کنیم و حروف پرش را در داده های اصلی تصحیح کنیم. این کار را با یک فرمول کوتاه انجام می دهیم که کل آرایه را تشکیل می دهد و سپس تابع را روی آن اعمال می کنیم PROPNACH (مناسب)برای مرتب کردن رجیستر:

نتیجه 3

فرض کنید یک دسته اعداد داریم که می‌خواهیم سه نتیجه برتر را از آنها استخراج کنیم و آنها را به ترتیب نزولی مرتب کنیم. اکنون این کار با یک فرمول و دوباره بدون هیچ فرمولی انجام می شود کلیدهای Ctrl+تغییر+وارد مثل سابق:

اگر می خواهید نتایج نه در یک ستون، بلکه در یک ردیف قرار گیرند، کافی است دو نقطه (جداکننده خط) در این فرمول را با نقطه ویرگول (جداکننده عنصر در یک خط) جایگزین کنید. در نسخه انگلیسی اکسل، این جداکننده ها به ترتیب نقطه ویرگول و کاما هستند.

VLOOKUP استخراج چندین ستون در یک زمان

توابع VPR (VLOOKUP) اکنون می توانید مقادیر را نه از یک، بلکه از چندین ستون به طور همزمان بکشید - فقط اعداد آنها را (به هر ترتیب دلخواه) به عنوان یک آرایه در آرگومان سوم تابع مشخص کنید:

تابع OFFSET یک آرایه پویا را برمی گرداند

یکی از جالب ترین و مفیدترین توابع (بعد از VLOOKUP) برای تجزیه و تحلیل داده ها تابع است دسترس (انحراف)، که در یک زمان یک فصل کامل از کتابم و یک مقاله در اینجا به آن اختصاص دادم. مشکل درک و تسلط بر این تابع همیشه این بوده است که آرایه ای (محدوده) از داده ها را در نتیجه برمی گرداند، اما ما نمی توانیم آن را ببینیم، زیرا اکسل هنوز نمی دانست که چگونه با آرایه های خارج از جعبه کار کند.

اکنون این مشکل در گذشته است. اکنون ببینید که چگونه با استفاده از یک فرمول واحد و یک آرایه پویا که توسط OFFSET برگردانده شده است، می توانید تمام ردیف های یک محصول معین را از هر جدول مرتب شده استخراج کنید:

بیایید نگاهی به استدلال های او بیندازیم:

• A1 - سلول شروع (نقطه مرجع)
• ПОИСКПОЗ(F2;A2:A30;0) - محاسبه تغییر از سلول شروع به پایین - به اولین کلم یافت شده.
• 0 - تغییر "پنجره" به سمت راست نسبت به سلول شروع
  
• СЧЁТЕСЛИ(A2:A30;F2) - محاسبه ارتفاع "پنجره" برگشتی - تعداد خطوطی که در آن کلم وجود دارد.
• 4 - اندازه "پنجره" به صورت افقی، یعنی خروجی 4 ستون

توابع جدید برای آرایه های پویا

علاوه بر پشتیبانی از مکانیزم آرایه پویا در توابع قدیمی، چندین عملکرد کاملاً جدید به مایکروسافت اکسل اضافه شده است که به طور خاص برای کار با آرایه های پویا تیز شده است. به طور خاص، این موارد عبارتند از:

• GRADE (مرتب سازی) - محدوده ورودی را مرتب می کند و یک آرایه پویا در خروجی تولید می کند
  
• SORTPO (مرتب سازی بر اساس) - می تواند یک محدوده را بر اساس مقادیر از دیگری مرتب کند
  
• FILTER (فیلتر) - ردیف هایی را از محدوده منبع که شرایط مشخص شده را برآورده می کنند، بازیابی می کند
• یونیک (منحصر بفرد) - مقادیر منحصر به فرد را از یک محدوده استخراج می کند یا موارد تکراری را حذف می کند
  
• SLMASSIVE (RANDARRAY) - آرایه ای از اعداد تصادفی با اندازه معین را تولید می کند
• پس از تولد (توالی) - آرایه ای را از دنباله ای از اعداد با یک مرحله معین تشکیل می دهد
  

اطلاعات بیشتر در مورد آنها - کمی بعد. آنها ارزش یک مقاله جداگانه (و نه یک) برای مطالعه متفکرانه دارند 🙂

نتیجه گیری

اگر همه چیزهایی که در بالا نوشته شده را خوانده باشید، فکر می کنم از قبل به مقیاس تغییراتی که روی داده است پی برده اید. بسیاری از کارها در اکسل اکنون می توانند آسان تر، راحت تر و منطقی تر انجام شوند. باید اعتراف کنم که از اینکه اکنون چند مقاله باید در اینجا، در این سایت و در کتاب هایم تصحیح شوند، کمی شوکه شده ام، اما من آماده هستم که این کار را با دلی سبک انجام دهم.

جمع بندی نتایج، плюсы آرایه های پویا، می توانید موارد زیر را بنویسید:

• می توانید ترکیب را فراموش کنید کلیدهای Ctrl+تغییر+وارد. اکسل اکنون هیچ تفاوتی بین «فرمول‌های معمولی» و «فرمول‌های آرایه» نمی‌بیند و به همان شیوه با آنها رفتار می‌کند.
• در مورد عملکرد SUMPRODUCT (مجموع محصول)، که قبلا برای وارد کردن فرمول های آرایه بدون استفاده می شد کلیدهای Ctrl+تغییر+وارد شما همچنین می توانید فراموش کنید - اکنون به اندازه کافی آسان است مبلغ и وارد.
• جداول هوشمند و توابع آشنا (SUM، IF، VLOOKUP، SUMIFS و غیره) اکنون به طور کامل یا جزئی از آرایه های پویا پشتیبانی می کنند.
• سازگاری به عقب وجود دارد: اگر یک کتاب کار با آرایه های پویا را در نسخه قدیمی اکسل باز کنید، آنها به فرمول های آرایه (در پرانتزهای فرفری) تبدیل می شوند و به "سبک قدیمی" به کار خود ادامه می دهند.

یک عدد پیدا شد منفی ها:

• شما نمی توانید ردیف ها، ستون ها یا سلول ها را از یک آرایه پویا حذف کنید، یعنی به عنوان یک موجودیت واحد زندگی می کند.
• شما نمی توانید یک آرایه پویا را به روش معمول مرتب کنید داده ها - مرتب سازی (داده ها - مرتب سازی). اکنون یک عملکرد ویژه برای این وجود دارد. GRADE (مرتب سازی).
• یک محدوده دینامیکی را نمی توان به یک جدول هوشمند تبدیل کرد (اما می توانید یک محدوده پویا را بر اساس جدول هوشمند ایجاد کنید).
  

البته این پایان کار نیست و من مطمئن هستم که مایکروسافت در آینده به بهبود این مکانیسم ادامه خواهد داد.

از کجا دانلود کنم؟

و در نهایت سوال اصلی 🙂

مایکروسافت برای اولین بار در سپتامبر 2018 در کنفرانسی پیش نمایشی از آرایه های پویا در اکسل را اعلام کرد و نشان داد. روشن کردن. در چند ماه آینده، ابتدا یک آزمایش کامل و اجرای ویژگی‌های جدید انجام شد گربه ها کارمندان خود مایکروسافت و سپس آزمایش‌کنندگان داوطلب از حلقه آفیس اینسایدرها. امسال، به‌روزرسانی که آرایه‌های پویا را اضافه می‌کند، به تدریج برای مشترکین عادی Office 365 عرضه شد. به عنوان مثال، من آن را فقط در ماه آگوست با اشتراک Office 365 Pro Plus (ماهانه هدفمند) دریافت کردم.

اگر اکسل شما هنوز آرایه های پویا ندارد، اما واقعاً می خواهید با آنها کار کنید، گزینه های زیر وجود دارد:

• اگر اشتراک آفیس 365 دارید، می توانید به سادگی منتظر بمانید تا این به روز رسانی به دست شما برسد. اینکه چقدر سریع این اتفاق می‌افتد بستگی به تعداد دفعات ارسال به‌روزرسانی‌ها به آفیس شما دارد (یک بار در سال، هر شش ماه یک بار، یک بار در ماه). اگر رایانه شخصی شرکتی دارید، می‌توانید از سرپرست خود بخواهید به‌روزرسانی‌ها را تنظیم کند تا بارها دانلود شوند.
  
• می‌توانید به جمع داوطلبان آزمون Office Insiders بپیوندید – سپس اولین نفری خواهید بود که تمام ویژگی‌ها و عملکردهای جدید را دریافت می‌کنید (اما البته احتمال افزایش باگ در اکسل وجود دارد).
• اگر اشتراک ندارید، اما یک نسخه مستقل جعبه‌دار اکسل دارید، حداقل باید تا انتشار نسخه بعدی آفیس و اکسل در سال 2022 صبر کنید. کاربران چنین نسخه‌هایی فقط به‌روزرسانی‌های امنیتی و رفع باگ‌ها را دریافت می‌کنند و همه «خوب‌های» جدید اکنون فقط به مشترکان Office 365 می‌رسند. غم انگیز اما واقعی 🙂
  

در هر صورت، هنگامی که آرایه های پویا در اکسل شما ظاهر می شوند - پس از این مقاله، شما برای آن آماده خواهید شد.

• فرمول های آرایه چیست و چگونه از آنها در اکسل استفاده کنیم
• جمع بندی پنجره (محدوده) با استفاده از تابع OFFSET
• 3 روش برای جابجایی یک جدول در اکسل