فرمول های آرایه ای در اکسل

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

مفهوم فرمول آرایه ای

اگر پیش ازاین با هرکدام از زبان های برنامه نویسی کامپیوتر کار کرده باشید، احتمالا بامفهوم آرایه ها اشنا هستید.

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

برای مثال آرایه ی یک بعدی می تواند در دامنه ای که شامل یک ردیف(آرایه افقی) یایک ستون(آرایه عمودی) باشد، ذخیره شود.

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

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

فرمول آرایه ای تک سلولی: باآرایه های ذخیره شده در دامنه ها یا در حافظه ی کلیپ برد اکسل کار می کند ونتیجه ی آن در یک سلول نمایش داده می شود.

فرمول آرایه ای چند سلولی: باآرایه های ذخیره شده در دامنه ها یادر حافظه ی کلیپ برد اکسل کار می کند وخروجی آن، یک آرایه است. چون هر سلول تنها می تواند یک مقدار را در  خود نگه دارد، فرمول آرایه ای چند سلولی در دامنه ای از سلول ها درج می شود. دراین بخش، دومثال از فرمول های آرایه ای ارایه شده است: فرمول آرایه ای که چند سلول و فرمول آرایه ای که تنها یک سلول را اشغال می کند.

فرمول آرایه ای چند سلولی

تصویر زیر، کاربرگ ساده ای را نشان می دهد که برای محاسبه ی میزان فروش محصولات را (فروش کلی به ازای هر محصول) با فرمولی مانند  D طراحی شده است. اغلب مقدار ستون  فرمول زیر محاسبه کرده وسپس این فرمول در دیگر سلول های موجود در ستون کپی میشود:

=B2*C2

 پس از کپی کردن فرمول، کاربرگ شامل شش فرمول در ستون D خواهد بود.

فرمول های ارایه ای

 

روش دیگر برای انجام عملیات قبل، استفاده از یک فرمول(فرمول آرایه ای)برای محاسبه ی هر شش مقدار دامنه‌یD2:D7 است. این تک فرمول، شش سلول را اشغال می کند و آرایه ای   متشکل از شش مقدار را برمی گرداند. برای ایجاد فرمول آرایه ای که محاسبات را انجام دهد باید به شرح زیر عمل کنید:

دامنه ای که قرار است نتایج در آنجا ذخیره شود را انتخاب کنید(دامنه ای نتایج در این مثال، دامنه‌ی D2:D7 است). چون نمی توانید در هر سلول بیش از یک مقدار را نمایش دهید.پس برای  نمایش نتایج نیاز به یک آرایه دارید که از شش سلول تشکیل شده باشد .

فرمول زیر را تایپ کنید :

=B2: B7*C2:C7

کلیدهای ترکیبیCtrl+Shift+Enter را فشار دهید تافرمول ثبت شود.اغلب برای ثبت فرمول،کلید Enterرا فشار می دهند، اما در این مثال چون قرار است فرمول آرایه ای درج شود باید کلید‌های ترکیبیCtrl+Shift+Enter را فشار دهید .

نمی توانید در دامنه ای که با دستور Insert→Tables→Table  تبدیل به جدول شده، فرمول  آرایه ای چند سلولی درج کنید و برعکس، نمی توانید دامنه ای که شامل یک فرمول آرایه ای چند سلولی است را به جدول تبدیل کنید .

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

{=B2 :B7*C2 :C7}

اکسل برای اینکه نشان دهد عبارت بالا یک فرمول آرایه ای است، در دو طرف آن، کروشه قرار می‌دهد. این فرمول پس از انجام محاسبات خود، یک آرایه ی شش عنصری را برمی گرداند .فرمول آرایه ای در اصل با دو آرایه ی دیگر کار می کند که هر دوی آنها در دامنه های مختلف ذخیره می شوند .مقادیر آرایه ی نخست در دامنه یB2:B7 و مقادیر آرایه ای دوم در دامنه ی C2:C7 ذخیره خواهند شد .

 این فرمول آرایه ای، نتایجی مشابه باشش فرمول معمولی زیر که در سلول های D2 تا D7 درج شده باشند را بر می گرداند :

=B2*C2

=B3*C3

=B4*C4

=B5*C5

=B6*C6

=B7*C7

اغلب فرمول آرایه ای نسبت به فرمول های مستقل دارای مزایای زیر است :

روش خوبی برای اطمینان از این است که تمام فرمول ها در دامنه مشابه یکدیگر هستند .

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

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

البته استفاده از فرمول آرایه ای چند سلولی دارای معایب بالقوه زیر هم است :

در دامنه ای که دارای فرمول آرایه ای چند سلولی است دیگر امکان درج ردیف وجود ندارد : هرچند درمواردی، این ناتوانی به نفع شماست . مثلا ممکن است نخواهید تا کاربران ، ردیف های جدیدی را در کاربرگ درج کنند چون این کار باعث تاثیر بر بخش های دیگر کاربرگ خواهد شد .

اگر داده های جدیدی به انتهای دامنه ای که دارای فرمول آرایه ای چند سلولی است اضافه کنید، باید فرمول آرایه ای را نیز اصلاح کنید تا بتواند داده های جدید را هم به کار‌گیرد .

 

فرمول آرایه ای تک سلولی

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

فرمول های ارایه ای

 فرمول آرایه ای زیر در سلول C9 درج شده است :

{=SUM( B2: B7*C2 :C7) }

وقتی این فرمول را درج می کنید، باید از فشار دادن کلیدهای ترکیبی Ctrl + Shift + Enter مطمئن شوید. این فرمول با دو آرایه کار می کند که هر دوی انها در سلول هایی ذخیره شده اند: آرایه ی نخست در دامنه‌یB2: B7 و آرایه ی دوم در دامنه ی C2 :C7 ذخیره شده اند . این فرمول ، مقادیر متناظر در  این دو آرایه را در یکدیگر ضرب می کند و آرایه ی جدیدی می سازد که تنها در حافظه ی برنامه اکسل ذخیره شده است .

آرایه ی جدید شامل شش مقدار است که به صورت زیر نمایش داده می شوند:

{150; 1000; 100; 90; 180; 200}

سپس تابع SUM  روی این آرایه ی جدید عمل کرده و حاصل جمع مقادیر موجود در آن را برمی گرداند.

در این مثال می توانید از تابع SUMPRODUCT استفاده کنید تا بدون استفاده از هیچ فرمول  آرایه ای به نتایج بالا دست پیدا کنید :

=SUMPRODUCT (B2 :B7 , C2 :C7)

ایجاد ثابت آرایه ای

در مثال های بخش قبلی از آرایه ای ذخیره شده در دامنه های کاربرگ استفاده شد . در مثال های این بخش، یک مفهوم مهم بررسی خواهد شد و آن هم ارایه ای است که لازم نیست در دامنه ای از سلول ها ذخیره شود. این نوع آرایه که در حافظه ذخیره می شود را به اصطلاح "ثابت آرایه ای" می نامند .

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

{1,0,1,0,1}

در فرمول زیر از تابعSUM استفاده شده و ثابت آرایه ای قبلی به عنوان آرگومان ان به کار گرفته شده است. این فرمول، حاصل جمع مقادیر موجود در آرایه (که برابر با3 است) را به عنوان پاسخ بر می گرداند:

=SUM ( {1, 0, 1, 0, 1} )

دقت کنید که در این فرمول از یک آرایه استفاده شده اما خود فرمول ، فرمول آرایه ای نیست. بنابراین  نیازی نیست برای درج آن از کلیدهای ترکیبی Ctrl +Shift + Enter استفاده کنید، هر چند که اگر آن را به صورت یک فرمول آرایه ای درج کنید نیز به نتایج مشابهی دست پیدا می کنید. دقت کنید وقتی از  کلیدهای ترکیبی Ctrl + Shift + Enter برای درج فرمول استفاده نشود وفرمول ها به صورت مستقیم درج شوند، باید خود کاربر، کاراکترهای آکولاد را در دو طرف فرمول قرار دهد. در این مرحله به احتمال  هنوز متوجه هیچ کدام از مزایای استفاده از ثابت آرایه ای نشده اید. برای مثال، فرمول زیر نتیجه ای مشابه فرمول پیش را برمی گرداند. البته مزیت استفاده از آن در ادامه آشکار خواهد شد:

=SUM (1, 0, 1, 0, 1 )

این فرمول از دو ثابت آرایه ای استفاده می کند:

=SUM( {1, 2, 3, 4 } * {5, 6, 7, 8 } )

این فرمول، آرایه ی جدیدی در حافظه اکسل ایجاد می کند که شامل حاصل ضرب عناصر متناظر دو آرایه خواهد بود. آرایه ی جدید به صورت زیر است:

{5, 12, 21, 32}

سپس از این آرایه ی جدید به عنوان آرگومان تابع SUM استفاده می شود که نتیجه ی برگشتی از ان 70 خواهد بود. فرمول بالا معادل فرمول زیر می باشد که در آن از آرایه ها استفاده نشده است :

=SUM (1*5 , 2*6 , 3*7 , 4*8 )

البته می توانید از تابع SUMPRODUCT هم استفاده کنید. فرمول زیر، یک فرمول آرایه ای نیست، اما از دو ثابت آرایه ای به عنوان آرگومان استفاده می کند:

=SUMPRODUCT ( { 1, 2, 3, 4 } , { 5, 6, 7, 8 } )

فرمول می تواند هم با ثابت آرایه ای و هم با آرایه ی ذخیره شده در یک دامنه از سلول ها کار کند . برای مثال، فرمول زیر، حاصل جمع مقادیر موجود در دامنه ی A1: D1را بر می گرداند که هر کدام از آنها در عنصر متناظر آرایه ای ضرب شده اند:

=SUM ( {A1 : D1 * {1, 2, 3, 4 } )

فرمول بالا معادل با فرمول زیر است:

=SUM ( A1 * 1 , B1 * 2 , C1 * 3 , D1 * 4 )

ثابت آرایه ای می تواند شامل اعداد، عبارت های متنی ، مقادیر منطقی (TRUE یا FALSE )و حتی  مقادیر خطا مانند#N/A باشد. اعداد می توانندبه صورت صحیح ، اعشاری و نماد علمی باشند.  می توانید در ثابت آرایه ای از انواع مختلف این مقادیر به صورت همزمان استفاده کنید. به مثال زیر توجه کنید:

{1, 2, 3, TRUE, FALSE, “Moe”, “Larry”, “Curly” }

ثابت آرایه ای نمی تواند شامل فرمول، تابع یا آرایه های دیگر باشد. مقادیر عددی هم نمی توانندحاوی کاراکترS، ویرگول ، پرانتز یا کاراکتر % باشند . برای مثال، عبارت زیر، یک ثابت آرایه ای غیر معتبراست:

 { SQRT (32) , s56 . 31, 12 . 5% }

ابعاد آرایه

همانطور که پیش تر گفته شد آرایه می تواند یک بعدی یا دوبعدی باشد. جهت گیری آرایه ی یک بعدی افقی (متناظر بایک ردیف )یاعمودی ( متناظر با یک ستون ) است.  آرایه های یک بعدی عناصرموجود در آرایه ی یک بعدی افقی با کاراکتر ویرگول از هم جدا شده اند و آرایه را می توان در یک ردیف سلول نمایش داد. مثال زیر، یک ثابت آرایه ای تک بعدی افقی است :

{1, 2, 3, 4, 5 }

نمایش این آرایه در یک دامنه از سلول ها نیازمند 5سلول متوالی در یک ردیف است. اگر بخواهیداین  آرایه را در یک دامنه درج کنید ابتدا باید دامنه ای از سلول ها که شامل یک ردیف و 5 ستون است را انتخاب کنید. سپس عبارت ={1, 2, 3, 4, 5} را درج کنید و در پایان کلیدهای ترکیبی Ctrl +Shift+ Enterرا فشار دهید.

اگر این آرایه را در یک دامنه ی افقی که شامل بیش از پنج سلول باشد درج کنید، عبارت #N/A در سلول های اضافی نمایش داده خواهد شد. اگر این آرایه را دریک دامنه ی عمودی درج کنید تنها عنصر نخست آن (1) درتمام سلول ها نمایش داده خواهد شد.

مثال زیر، یک آرایه ی افقی دیگر است که هفت عنصر دارد که از رشته های متنی ساخته شده اند :

{ “Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat” }

برای درج این آرایه ابتدا هفت سلول در یک ردیف را انتخاب کرده و سپس کلیدهای ترکیبی Ctrl+ Shift + Enter را فشار دهید و عبارت زیر را تایپ کنید.

={ “Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat” }

آرایه های یک بعدی عمودی

عناصر موجود در آرایه ی یک بعدی عمودی با کاراکتر";" از هم جدا شده اند و ارایه را می توان در یک ستون سلول نمایش داد. مثال زیر ،یک ثابت آرایه ای تک بعدی عمودی شش عنصری است :

{10 ; 20 ; 30 ; 40 ; 50 ; 60 }

نمایش این آرایه در یک دامنه از سلول ها نیازمند شش سلول متوالی در یک ستون است . اگر  بخواهید این آرایه را در یک دامنه درج کنید ابتدا باید دامنه ای از سلول ها که شامل یک ستون و شش ردیف است را انتخاب و فرمول زیر را درج کنید و درپایان کلیدهای ترکیبی Ctrl +Shift + Enter را فشار دهید :

= { 10 ; 20 ; 30 ; 40 ; 50 ; 60 }

مثال زیر، یک آرایه عمودی دیگر است که چهار عنصر دارد که از رشته های متنی ساخته شده اند:

{ “Widgets” ; “Sprockets” ; “Doodads” ; “Thingamajigs” }

 

آرایه های دو بعدی

در آرایه ی دوبعدی از ویرگول برای جداکردن عناصر افقی آن و از کاراکتر نقطه ویرگول برای جدا کردن عناصر عمودی آن استفاده می شود. مثال زیر، یک ثابت آرایه ای 4 × 3 را نشان می دهد :

= { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 }

برای نمایش این آرایه در یک دامنه نیازمند دوازده سلول هستیم .برای درج این آرایه در دامنه ی  مورد نظر، ابتدا باید دامنه ای از سلول ها که شامل دو ردیف و چهار ستون است را انتخاب و سپس  فرمول زیر را تایپ کنید و در پایان کلیدهای ترکیبی Ctrl + Shift + Enter را فشار دهید :

={ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, }

تصویر زیر چگونگی نمایان شدن این آرایه در زمان درج آن در یک دامنه( که دراین مثال، دامنه‌یB3: E5است) را نشان می دهد .

فرمول های ارایه ای

اگر آرایه ای را در دامنه ای که سلول های آن بیش از عناصر آرایه ای است درج کنید، اکسل عبارت #N/A  را در سلول های اضافه نمایش می دهد .تصویر زیر، یک آرایه 4 × 3 را نشان می دهد که  در دامنه ی سلولی 5 × 10 درج شده است .

فرمول های ارایه ای

 

تمام ردیف ها در یک آرایه ی دو بعدی باید شامل تعداد مشابهی عناصر باشند . برای مثال، آرایه ی زیر چون ردیف سوم آن تنها حاوی سه عنصر است و دو ردیف دیگر دارای چهر عنصر هستند، نامعتبر و نادرست تلقی می شود :

{ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, }

همچنین اکسل اجازه نمی دهد تا فرمولی که شامل یک آرایه ی نامعتبر است را تایپ کنید .

نام گذاری ثابت های آرایه ای

می توانید یک ثابت آرایه ای ایجاد کنید، برای ان نامی مشخص و سپس از آن نام در یک فرمول استفاده کنید. از لحاظ فنی ، یک آرایه ی نام گذاری شده، فرمولی نام گذاری شده است .

تصویر15-5 ، یک آرایه ی نام گذاری شده را نشان می دهد که از طریق کادر محاوره ای New Name ایجاد شده است .از طریق اجرای دستور Formulas→Defined Names→Defined Nameمی توانید این کادر محاوره ای را فعال کنید. نام آرایه،DayNames  انتخاب شده و به ثابت آرایه ای زیر ارجاع  می کند :

{ “Sun”, “Mon”, “Tue”, “Wed”, Thu”, “Fri”, “Sat” }

دقت کنید که در کادر محاوره ای New Name، پیش از عبارت تعریف مقادیر عناصر آرایه در فیلد Refers to ، کاراکتر " = " قرار گرفته است .اگر کاراکتر " = " در عبارت تعریف آرایه قرار نگیرد  آرایه به عنوان یک رشته ی متنی مورد تفسیر قرار می گیرد . همچنین به خاطر داشته باشید که در هنگام تعریف یک ثابت آرایه ای باید خودتان کاراکترهای آکولاد را در دو طرف آن قرار دهید چون  اکسل این کار را انجام نمی دهد .

فرمول های ارایه ای

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

{= DayNames }

برای درج این فرمول ، هفت سلول در یک ردیف را انتخاب و عبارت =DayNames را تایپ کنید ، سپس کلیدهای ترکیبی Ctrl + Shift + Enter را فشار دهید .

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

{ =TRANSPOSE ( DayNames ) }

 

فرمول های ارایه ای

 

همچنین می توانید با استفاده از تابعINDEX   به عناصر مستقل آرایه دسترسی پیدا کنید. برای مثال، فرمول زیر، عبارت Wed  که در واقع چهارمین عنصر در آرایه DayNames است را به عنوان پاسخ برمی گرداند  :

=INDEX (DayNames ,4 )


نویسنده : --

تگ ها :
تاپ سایت 98
عضویت طلایی
محصولات آموزشی
ثبت نظر