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

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

مثال 1: شمارش سلول هایی که دارای شرایط خاصی هستند

معرفی توابع sumif، countif ،sumifs و countifs در اکسل 2007 باعث شد که توابع آرایه و کاربردهای آنان در این زمینه در حاشیه قرار بگیرند. با اینحال چالشهایی وجود دارد که ممکن است استفاده از فرمول های آرایه تنها راه حل ممکن برای آنان نباشد ولی موثرترین راه حل است.

برای آشنایی با تابع sumfi، تابع sumifs، تابع countif و تابع countifs مطالب زیر را مطالعه کنید:

چگونگی استفاده از SUMIF در اکسل، مثال های فرمولی برای جمع زدن مشروط سلول ها

نحوه استفاده از توابع SUMIFS و SUMIF با شرط های چندگانه – مثال های فرمولی

تابع sumif در اکسل – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

تابع sumifs در اکسل – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

تابع COUNTIF در اکسل – شمارش به شرط خالی نبودن، بزرگتر بودن، تکراری بودن یا منحصر به فرد بودن

تابع countif در اکسل – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

نحوه استفاده از توابع COUNTIF و COUNTIFS با شرط های چندگانه

تابع countifs در اکسل-قسمت اول – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

تابع countifs در اکسل -قسمت دوم– دوره فرمول های اساسی در اکسل (فیلم آموزشی)

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

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

بنابریان شرط های ما به شکل، (B2:B10>=A2:A10)  و (B2:B10>0) خواهند بود که آنها را بوسیله علامت * که به معنی “و” در فرمول های آرایه است به هم متصل میکنیم و سپس این عبارت را در درون تابع Sum قرار میدهیم.

=SUM((B2:B10>=A2:A10) * (B2:B10>0))

همچنین به یاد داشته باشید که برای وارد شدن فرمول آرایه به صورت صحیح، در انتهای فرمول کلیدهایCtrl + Shift + Enter را فشار دهید.

دو پاراگراف بعدی برای کسانی مناسب است که میخواهند این مبحث را به صورت عمیق یاد بگیرند، در صورتی که علاقمند به مباحث عمیق فنی نیستید بهتر است مستقیماً به مبحث بعدی منتقل شوید.

برای درک بهتر این مسئله به صورت مشخص و فرمول نویسی آرایه ای به صورت عمومی، دو عبارت را در فرمول خود و در قسمت formula bar انتخاب کنید و دکمه f9 را فشار دهید تا آرایه پشت این عبارت ها مشخص شود.

چیزی که در زیر این دو عبارت قرار دارد مقادیر boolean به شکل true/false است.که true برابر با 1 و false برایر با 0 است. از آنجایی که از عملگر آرایه and(*) استفاده میکنیم، تابع sum فقط مقادیری را با هم جمع میکند که در هر دو آرایه مقدار true را داشته باشند، چیزی که در تصویر زیر نشان داده شده است:

نکته: همه توابع اکسل که از آرایه ها پشتیبانی میکنند مقدار true و false را به 0 و 1 تبدیل نیمکنند. در توابع پیچیده تر آرایه، ممکن است نیاز باشد که از — استفاده کنید که به صورت فنی به آن عملگر های  double unary میگویند، تا مقدار های boolian را به 0 و 1 تبدیل کنید.

استفاده از — در فرمول بالا نیز آسیبی به آن وارد نمیکند، بلکه محکم کاری نیز محسوب میشود:

=SUM(--(B2:B10>=A2:A10) * (B2:B10>0))

در زیر مثال فرمولی پیچیده تری از آرایه در اکسل آورده شده است که در آن می بایست حتما از عملگر های double unary استفاده کرد.

مثال2. استفاده از چند تابع در فرمول نویسی آرایه ای اکسل

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

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

=MAX(IF((salesmen_range="نام") * (products_range="نام"),sales_range,""))

با فرض اینکه اسامی فروشنده ها در ستون a قرار دارد، نام محصول در ستون b و مقدار فروش در ستون c است، فرمول زیر بیشترین مقدار فروش سیب برای mike را نشان میدهد:

=MAX(IF(($A$2:$A$9="امیر") * ($B$2:$B$9="سیب"), $C$2:$C$9,""))

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

در تصویر بالا، فرمول نویسی آرایه ای در زیر آمده استفاده شده است (فراموش نکنید که حتما دکمه های Ctrl + Shift + Enter را فشار دهید:

ماکسیمم:

=MAX(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

مینیمم:

=MIN(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

میانگین:

=AVERAGE(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

مجموع:

=SUM(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

مثال 3. فرمول های آرایه برای شمارش تمام کاراکتر ها در یک محدوده

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

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

=SUM(LEN(range))

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

برای مثال، فرمول آرایه

=SUM(LEN(A1:A10))

تعداد کل کاراکترها و فاصله ها را در محدوده A1:a10 نشان میدهد.

مثال4. فرمول نویسی آرایه ای برای شمارش کاراکترهای مشخص در محدوده مشخص

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

=SUM((LEN(range) - LEN(SUBSTITUTE(range, character, ""))) / LEN(character))

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

با فرض اینکه سفارشات در سلول های b2:b5 قرار دارد و کد سفارش در سلول E1 فرمول به شکل زیر خواهد شد:

=SUM((LEN(B2:B5) - LEN(SUBSTITUTE(B2:B5, E1, ""))) / LEN(E1))

در دل این فرمول، تابع subsititue مقدار تمام کاراکترهایی را که یافت میکند با یک رشته خالی (“”) جایگزین میکند.

رشته جایگزین شده به تابع len داده میشود تا طول رشته را بدون در نظر گرفتن کاراکتر مورد نظر(در اینجا k) شمارش کند. آنگاه طول رشته جایگزین شده را از رشته اصلی کم میکنید. نتیجه این عملیات آرایه ای شمارش کاراکترها میشود (هر سلول یک شمارش) که شما آن را بر طول زیررشته تقسیم میکنید. عملیات تقسیم در زمانی که شما یک کاراکتر را شمارش میکنید (شبیه به مثال بالا) ضروری نیست. ولی اگر به دنبال مشخص کردن تعداد وقوع زیر رشته در یک محدوده هستید (برای مثال، سفارشاتی که با “KM” شروع میشود) می بایست حتماً مقدار خود را بر طول زیر رشته تقسیم کنید. در غیر این صورت هر کاراکتر در زیررشته به صورت جداگانه شمارش میشود.

در آخر نیز با استفاده از SUM مقادیر بدست آمده را با هم جمع میکنیم.

Function textJoinUDF(deliminator As String, myCells As Range)
        Dim joinText As String
        Dim myCell As Range
        Dim i As Integer
        
        joinText = ""
        For Each myCell In myCells
            joinText = joinText & deliminator & myCell
        Next myCell
        textJoinUDF = Mid(joinText, 2, 10000)
End Function