یادگیری اکسل نیز مثل هر مهارت دیگری به تمرین نیاز دارد. بنابراین امروز به بررسی فرمول نویسی آرایه ای و مثال های پیشرفته آرایه می پردازیم و تلاش میکنیم این کار را به صورتی معنی دار و سرگرم کننده انجام دهیم.
مثال 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 مقادیر بدست آمده را با هم جمع میکنیم.
سلام.جطور میتونم چند ردیف غیرمجاور در اکسل رو در یک صفحه چاپ بزنم؟با تشکر
باید ستون هایی که نمی خواید پرینت بشه رو hidden کنید.
با سلام
دراکسل با چه فرمولی می توان دو محدوده یک ستونی را در در یک محدوده یک ستونی قرار داد
برای مثال اگر یک سری اطلاعات در محدوده a1 تا a5 و یک سری ازاطلاعات نیز در b1 تاb7 داشته باشیم با چه فرمولی می توانیم این دو محدوده را در یک ستون مثلا از c1 تا c12 قرار دهیم
سلام
اگر از اکسل 2023 به بعد استفاده می کنید می تونید از تابع tocol استفاده کنید. در غیر اینصورت کار ساده نیست.
سلام مجدد
من دقیقا فرمول مثال 2 شما را با تمام اسامی و کارکترها و جدول کاملن مشابه، نوشتم ولی پاسخ قابل قبول نبود.
راهنمائی میفرمائید مشکلاز کجاست؟
سلام
باید گام ها رو دقیقا مثل آموزش انجام بدید. گاهی اوقات نذاشتن یک پرانتز کار رو خراب می کنه. بعد از اینکه فرمول رو می نویسید اگر آرایه ای هست حتما Ctrl+shift+enter بزنید.
ممنون
سلام یه سوال دارم.
چطور میشه یه لیست یونیک از یه لیست چند هزارتایی استخراج کرد و تعدادش رو در بیاریم.
می دونم که با تابع یونیک در اکسل ۲۰۲۱ میشه ولی من اکسل ۲۰۱۹ دارم. بدون وی بی ای.
مثلا تعدادی پرسنل در سال رفتند ماموریت به تعداد سه هزار سفر می خوایم بدونیم کلا چند نفر بودند که رفتند
سلام
از پیوت تیبل استفاده کنید.
شمارش مقدارهای یکتا در پیوت تیبل
یا می تونید از مطلب زیر با فرمول نویسی این کار رو انجام بدید:
شماره مقدارهای یکتا با فرمول نویسی
آقا عالی دمت گرم خیلی خوب بود
با سلام
یک سری داده داریم ، تعداد افرادی که واکسن زده اند
یک لیست کلی هم داریم که میخواهیم افرادی که واکسن زده اند را از این لیست خارج کنیم و بفهمیم چه افرادی واکسن نزده اند
یعنی در واقع شماره ملی های واکسن نزده ها را میخواهیم .
تیبل داده ها به صورت زیر است
ردیف
نام
نام خانوادگی
نوع واکسن
تاریخ تزریق دوز اول
تاریخ تزریق دوز دوم
سپاس
سلام از دو روش می تونید استفاده کنید: 1- استفاده از توابع جتسجو مانند vlookup یا match یا … چون شماره ملی توی دو لیست یکتا هست، می تونید توی جدول دوم که میخواید اسامی رو خارج کنید روی ستون کد ملی از توابع جستجو استفاده کنید. اونهایی که شماره ملیشون توی لیست اول هست که نمایش داده میشه، اونهایی که نیست خطای #n/a میده. حالا اگر این خطا رو فیلتر کنید در واقع افرادی هستن که واکسن نزدن. اگر با vlookup آشنا نیستید (این آموزش) رو ببینید. 2- استفاده از قابلیت merge در پاور کوئری. توی این روش هم… مطالعه بیشتر »
آقای مهندس دایی عزیز سلام وقتتون بخیر
لطفا راهنمایی بفرمایید چطور میشه مقادیر یک تابع آرایهای رو (که با زدن کلید f9 در نوار فرمول هم مشاهده میشه) رو در یک سلول و کنار هم نوشت.
سلام
اگر از اکسل 2019 به بالا استفاده می کنید، می تونید از تابع textjoin استفاده کنید.
اگر از نسخه های پایین تر استفاده می کنید باید یک تابع در Vba ایجاد کنید که براتون این کار رو انجام بده. یعنی ورودی اون نتیجه فرمولی که نوشتید باشه و بیاد نتیجه رو با هم ادغام کنه.
سلام خیلی عالی ممنون از زحمت و لطف جنابعالی
خواهش میکنم، موفق باشید.
خیلی عالی بود
سلام چطوری میتونم یک فرمول حضور و غیاب و اضافه کاری و کسری کاری در اکسل درست کنم که فقط ورود و خروج رو بزنم و بقیه محاسبات فرمولی شود
کار با تابع if به صورت کامل یاد بگیرید. بقیه تعریف ها بستگی به شرایط شما داره که مثلا اگر ساعت کاری 8 هست، شرط بذارید اگر دیرتر از 8 اومد کسری کار بزنه و ….
این مطلب مطالعه کنید:
تابع if در اکسل
سلام وقت بخیر
من یه فرمول با شرح زیر میخوام. میتونید راهنماییم کنید؟
شماره سفارش A رو در شیت 1 پیدا کن و اگر نوع سفارش (تراول جعبه) بود آنگاه از ستونهای B و C وD شیت 1 مقادیر وارد شده را در جدول فراخوان کن.
(درواقع میخوام اگر سفارشهای وارد شده در شیت یک که شیت سفارشات کلی هست مربوط به کارگاه جعبه سازی بود اونموقع ابعاد و نوع روکش و نوع بسته بندی رو در جدولی که در یک شیت جداگانه طراحی شده وارد کنه)
سلام
برای اینکه بشه دقیق راهنمایی کرد باید با مثال و دیتای نمونه سوال خودتون بپرسید. ولی به صورت کلی به نظر میرسه بتونید از تابع vlookup استفاده کنید.
این مطلب مطالعه کنید:
تابع vlookup در اکسل