انجام عملیات ریاضی روی آرایه در اکسل

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

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

تصویر زیر، نتیجه را هنگامی که این فرمول در یک دامنه درج شود را نشان می دهد .

انجام عملیات ریاضی روی آرایه در اکسل

فرمول آرایه ای زیر، هر عنصر آرایه را در خودش ضرب می کند :

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

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

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

اگر آرایه در دامنه ای مانند B8:E10 ذخیره شده باشد، فرمول آرایه ای، مجذور هر مقدار در دامنه را به وسیله فرمول زیر برمی گرداند :

{= B8 : E10 ^2 }

استفاده از توابع در آرایه ها

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

{ =SQRT ( { 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 } ) }

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

{ =SQRT (A1 :A10 ) }

وارونه کردن آرایه

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

ثابت آرایه ای یک بعدی افقی زیر را در نظر بگیرید :

{ 1, 2, 3, 4, 5 }

می توانید با استفاده از تابع TRANSPOSE این آرایه را در یک دامنه ی عمودی درج کنید. برای این کار ابتدا

یک دامنه ی پنج سلولی که پنج ردیف و یک ستون را اشغال می کند انتخاب کنید .سپس فرمول زیر را تایپ

وکلیدهای ترکیبی Ctrl + Shift + Enter را فشار دهید :

=TRANSPOSE ( {1, 2, 3, 4, 5} )

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

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

فرمول دامنه ی A6:C9 به صورت زیر است:

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

البته می توانید از تابع TRANSPOSE برای وارونه کردن آرایه ای که در یک دامنه ذخیره شده هم استفاده کنید. برای مثال، فرمول زیراز آرایه ی ذخیره شده در دامنه ی A1:C4 استفاده می کند.. می توانید این فرمول آرایه ای را در دامنه ای که شامل 3ردیف و4 ستون است هم درج نمایید :

{ =TRANSPOSE (A1:C4) }

انجام عملیات ریاضی روی آرایه در اکسل

ساخت آرایه ای شامل اعداد صحیح متوالی

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

{ =ROW ( 1 : 12 ) }

این فرمول، یک آرایه ی دوازده عنصری که شامل اعداد صحیح 1تا12 است را می سازد. برای تشریح قضیه، دامنه ای شامل دوازده ردیف و یک ستون را انتخاب کنید و فرمول آرایه ای را در آن دامنه درج کنید. متوجه خواهید شد که در دامنه ی مذکور با دوازده عدد صحیح متوالی (تصویر زیر) پرشده است .

انجام عملیات ریاضی روی آرایه در اکسل

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

{= ROW (2 : 13) }

در واقع فرمولی که در اصل، اعداد صحیح بین 1تا12 را ایجاد می کرد حالا اعداد صحیح بین 2تا13 را ایجاد می کند. فرمول زیر نتیجه ای بهتر از فرمول قبلی ارایه می کند :

{ =ROW (INDIRECT (“ 1:12 “ ) ) }

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

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

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

شمارش کاراکتر های موجود در دامنه

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

=LEN (A1)

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

{ =SUM (LEN (A1 : A14) ) }

انجام عملیات ریاضی روی آرایه در اکسل

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

{ 10, 9, 8, 5, 6, 5, 5, 10, 11, 14, 6, 8, 8, 7 }

پس فرمول آرایه ای به صورت زیر در می آید:

=SUM ( { 10, 9, 8, 5, 6, 5, 5, 10, 11, 14, 6, 8, 8, 7 } )

پاسخ فرمول بالا که همان حاصل جمع عناصر آرایه است برابر با 112 خواهد بود.

جمع کردن سه مورد از کوچک ترین مقادیر موجود در دامنه

اگر مقادیری در دامنه ای به نام Data درج شده باشند، می توانید با استفاده از تابع SMALL ، کوچک ترین

مقدار آن دامنه را مشخص کنید:

= SMALL (Data, 1)

با استفاده از فرمول های زیر هم می توانید دومین وسومین کوچک ترین مقدار را مشخص کنید :

= SMALL ( Data, 2 )

=SMALL ( Data,  3 )

برای جمع کردن این سه مقدار با یکدیگر می توانید از فرمولی مانند فرمول زیر استفاده کنید:

=SUM ( SMALL (Data, 1) ,   SMALL (Data , 2) ,  SMALL (Data , 3)

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

{ =SUM ( SMALL (Data , (1, 2, 3, ) ) ) }

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

تصویر زیر، مثالی را نشان می دهد که در آن، دامنه ی A1:A10 ، Data نامیده شده است . تابع SMALLسه بار مورد ارزیابی قرار گرفته و هر بار آرگومان دوم آن متفاوت است .در مرتبه ی نخست، آرگومان دوم تابع SMALL برابر با 1بوده و مقدار 5- را به عنوان پاسخ برگردانده است . در مرتبه دوم، آرگومان دوم، تابع SMALL برابر 2 بوده ومقدار صفر (که دومین کوچک ترین مقدار در دامنه است) را به عنوان پاسخ برمی گرداند. در مرتبه سوم، آرگومان دوم تابع SMALL برابر با 3 بوده و عدد 2 (که سومین کوچک ترین مقدار در دامنه است) را به عنوان پاسخ برمی گرداند.

انجام عملیات ریاضی روی آرایه در اکسل

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

{-5, 0, 2 }

فرمول بالا، حاصل جمع عناصر آرایه (3-) را به عنوان پاسخ برمی گرداند.

شمارش سلول های متنی در یک دامنه

فرض کنید می خواهید تعداد سلول های متنی در یک دامنه را شمارش کنید. به نظر می رسد تابع COUNTIF کارامد باشد، اما چنین نیست. تابع COUNTIF تنها زمانی مناسب است که بخواهید مقادیر موجود در یک دامنه ، که با ملاک خاصی تطبیق دارند (مثلا بزرگ تر از 12 هستند) را شمارش کنید وبرای شمارش تعداد سلول های برای بررسی هرکدام از IFمتنی موجود در یک دامنه نیازمند یک فرمول آرایه ای هستید . فرمول زیر از تابع سلول های موجود در دامنه استفاده می کند. سپس آرایه ای جدید به همان اندازه و ابعاد دامنه ی اصلی ایجادخواهد کرد که شامل مقادیر 1 وصفر (بسته به این که آیا سلول حاوی عبارت متنی باشد یا خیر) است. سپس این آرایه ی جدید به تابع SUM انتقال پیدا می کند واز آنجا، حاصل جمع عناصر موجود در آرایه به عنوان نتیجه برگردانده می شود. این نتیجه در واقع شمارش تعداد سلول هایی در دامنه است که شامل عبارت متنی هستند:

{ =SUM ( IF ( ISTEXT ( A1 : D5 ) , 1, 0) ) }

تصویر 15-16، مثالی از فرمول پیش در سلول C7 را نشان می دهد. آرایه ی ایجاد شده به وسیله ی فرمول بالا به صورت زیر است:

{ 0, 1, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0 }

انجام عملیات ریاضی روی آرایه در اکسل

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

{ =SUM ( ISTEXT (A1 : D5) *1 ) }

در این تابع دیگر نیازی به تابع IF نیست و از این حقیقت که دو تساوی زیر همیشه برقرار هستند استفاده می شود :

TRUE * 1 = 1

 

FALSE * 1 = 0

حذف فرمول های واسطه

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

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

=AVERAGE ( D2 : D15 )

با به کارگیری فرمول آرایه ای می توانید ستون D را حذف کنید. فرمول آرایه ای زیر، میانگین تغییرات را بدون نیاز به فرمول های ستون D محاسبه می کند :

{ =AVERAGE ( C2 : C15 ) }

انجام عملیات ریاضی روی آرایه در اکسل

در این فرمول از دو آرایه استفاده شده که مقادیر آنها به ترتیب در دو دامنه ی B2 : B15و C2 : C15 درج شده اند. این فرمول، یک آرایه ی جدید ایجاد می کند که شامل مقدار تفاوت بین هر کدام از عناصر متناظر در این دو آرایه خواهد بود. توجه داشته باشید که این آرایه ی جدید در حافظه ی اکسل ذخیره می شود و نه در یک دامنه ی مجزا. سپس تابع AVERAGE از این آرایه ی جدید به عنوان آرگومان استفاده می کند و نتیجه ی  مورد نظر را بر می گرداند: آرایه ی جدید شامل عناصر زیر است:

{ 11, 15, -6, 1, 19, 2, 0, 7, 15, 1, 8, 23, 21, -11 }

بنابراین فرمول بالا معادل فرمول زیر خواهد بود:

=AVERAGE ( { 11, 15, -6, 1, 19, 2, 0, 7, 15, 1, 8, 23, 21, -11 } )

اکسل، تابع را ارزیابی می کند و خروجی آن (57/7) را نمایش می دهد .

می توانید از دیگر فرمول های آرایه ای برای محاسبه ی عملیات دیگر روی داده های موجود در این مثال استفاده کنید. برای مثال، فرمول آرایه ای زیر، بزرگ ترین تغییر بین مقادیر دو آرایه را به عنوان پاسخ بر می گرداند. پاسخ این فرمول، عدد23 و نمره ی آزمون Linda است :

{= MAX ( C2 : C15 – B2 : B15 ) }

فرمول آرایه ای زیر، کوچک ترین مقدار در ستون Change را برمی گرداند. نتیجه ی این فرمول ، عدد11- ونشان دهنده ی نمرات آزمون Nancy است :

{ =MIN (C2 : C15 – B2 : B15) }

استفاده از آرایه به جای ارجاع دامنه

اگر فرمول شما از تابعی استفاده می کند که نیازمند ارجاع دامنه است. ممکن است بتوانید از یک ثابت آرایه ای به جای آن ارجاع دامنه استفاده کنید. این امر در مواردی که در آنها، مقادیر موجود در دامنه ی ارجاع شده تغییر نمی کند، سودمند است. در تصویر، کاربرگی را نشان می دهد که از یک جدول جست وجو برای نمایش کلمه ای که متناظر با یک عدد صحیح است استفاده می کند. برای مثال در صورت جست وجو مقدار9 ، عبارت Nine از جدول جست وجو که در دامنه ی D1:E10 قرار دارد به عنوان پاسخ برگردانده می شود. فرمول سلول C1 به صورت زیر است:

=VLOOKUP (B1, D1 : E10, 2, FALSE)

انجام عملیات ریاضی روی آرایه در اکسل

 

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

= VLOOKUP ( B1, {1, “One”; 2, “Two”; 3, “Three”; 4, “Four”; 5, “Five”; 6, “Six”; 7, “Seven”; 8, “Eight”; 9, “Nine”; 10, “Ten” } ,2, FALSE )

 


نویسنده : --

تگ ها :
عضویت طلایی
ثبت نظر