آیا می خواهید محاسبات پیچیده ای را انجام دهید که با توابع ساده امکان پذیر نیست؟ از فرمول آرایه در محدوده ای از ردیف ها و ستون ها استفاده کنید.
فرمول های آرایه بخش مهمی از کیت ابزار هستند که اکسل را همه کاره می کند. با این حال، این عبارات می تواند برای مبتدیان دلهره آور باشد.
اگرچه ممکن است در نگاه اول پیچیده به نظر برسند، اما درک اصول اولیه آنها آسان است. یادگیری آرایه ها مفید است زیرا این فرمول ها مدیریت داده های پیچیده Excel را ساده می کنند.
فرمول آرایه در اکسل چیست؟
آرایه هر مجموعه ای از سلول ها در یک ستون، ردیف یا ترکیب است که در یک گروه جمع شده اند.
در مایکروسافت اکسل، اصطلاح فرمول آرایه به خانواده‌ای از فرمول‌ها اطلاق می‌شود که یک یا چند عملیات را بر روی چنین محدوده‌ای از سلول‌ها، به طور همزمان انجام می‌دهند، نه یک سلول در یک زمان.
نسخه‌های قبلی اکسل از کاربران می‌خواست که Ctrl + Shift + Enter را فشار دهند تا یک تابع آرایه ایجاد کنند که در نتیجه نام توابع CSE (Ctrl، Shift، Escape) ایجاد می‌شود، اگرچه این دیگر برای Excel 365 صدق نمی‌کند.
یک مثال ممکن است تابعی برای بدست آوردن مجموع فروش بیش از 100 دلار در یک روز معین باشد. یا برای تعیین طول (به رقم) پنج عدد مختلف ذخیره شده در پنج خانه.
فرمول های آرایه همچنین می توانند بهترین راه برای استخراج داده های نمونه از مجموعه داده های بزرگ باشند. آنها می‌توانند از آرگومان‌هایی استفاده کنند که شامل یک محدوده سلولی از یک ستون واحد، محدوده‌ای از سلول‌ها از یک ردیف واحد، یا سلول‌هایی که چندین ردیف و ستون را در بر می‌گیرند.
همچنین، عبارات شرطی را می توان هنگام کشیدن داده ها به فرمول آرایه، مانند کشیدن اعداد روی یا کمتر از یک مقدار خاص یا کشیدن هر n ام استفاده کرد.
این کنترل دانه‌ای به شما امکان می‌دهد اطمینان حاصل کنید که زیرمجموعه‌ای دقیق از داده‌های خود را می‌کشید، مقادیر سلول‌های ناخواسته را فیلتر می‌کنید، یا نمونه‌ای تصادفی از موارد را برای اهداف آزمایشی می‌گیرید.
نحوه تعریف و ایجاد آرایه در اکسل
فرمول های آرایه، در ابتدایی ترین سطح خود، به راحتی ایجاد می شوند. برای یک مثال ساده، می‌توانیم یک فاکتور برای سفارش قطعات مشتری با اطلاعات خط زیر در نظر بگیریم: SKU محصول (واحد نگهداری سهام)، مقدار خریداری شده، وزن تجمعی محصولات خریداری‌شده، و قیمت هر واحد خریداری‌شده.
برای تکمیل فاکتور، سه ستون باید به انتهای جدول پایین اضافه شود، یکی برای کل فرعی قطعات، یکی برای هزینه حمل و نقل و سوم برای قیمت کل هر خط.
می توانید از یک فرمول ساده برای محاسبه هر آیتم خطی به طور مستقل استفاده کنید. اما اگر فاکتور برای بسیاری از اقلام خطی باشد، می تواند به سرعت پیچیده تر شود. بنابراین، در عوض، “Subtotal” را می توان با یک فرمول منفرد در سلول E4 محاسبه کرد:
=B4:B8 * D4:D8
هر دو عددی که ما ضرب می کنیم، محدوده سلولی هستند تا سلول های منفرد. در هر ردیف، فرمول مقادیر سلولی مناسب را از آرایه ها بیرون می کشد و نتیجه را بدون ورودی بیشتر از کاربر در سلول صحیح قرار می دهد.
اگر نرخ حمل و نقل ثابت را 1.50 دلار در هر پوند فرض کنیم، می توانیم ستون حمل و نقل خود را با فرمول مشابهی که در سلول F4 قرار داده شده است محاسبه کنیم:
=C4:C8 *B11
این بار فقط از یک آرایه در سمت چپ عملگر ضرب استفاده کردیم. نتایج همچنان به طور خودکار وارد می شوند، اما هر کدام در برابر یک مقدار ثابت این بار ضرب می شوند.
در نهایت، می‌توانیم از همان فرمولی که برای Subtotal استفاده کردیم، استفاده کنیم تا با اضافه کردن آرایه‌هایی از Subtotals و Shipping Costs در سلول G4، یک کل بدست آوریم:
=E4:E8 + F4:F8
یک بار دیگر، یک فرمول آرایه ساده آنچه را که در غیر این صورت پنج فرمول بود را به یک فرمول تبدیل کرد.
در آینده، افزودن محاسبات مالیاتی به این صفحه‌گسترده، به جای تغییر هر آیتم خط، تنها به تغییر یک فرمول نیاز دارد.
مدیریت شرایط در آرایه های اکسل
مثال قبلی از فرمول‌های حسابی پایه در سراسر آرایه‌ها برای تولید نتایج استفاده می‌کند، اما ریاضیات ساده دیگر برای موقعیت‌های پیچیده‌تر کار نمی‌کنند.
برای مثال، فرض کنید شرکتی که فاکتور را در مثال قبلی ایجاد می‌کند، به یک شرکت حمل‌ونقل بار دیگر تغییر کرده است. در آن صورت، ممکن است قیمت حمل و نقل استاندارد کاهش یابد، اما می‌توان برای اشیاء با وزن معین هزینه‌ای اعمال کرد.
برای نرخ های جدید حمل و نقل، حمل و نقل استاندارد 1.00 دلار در هر پوند و حمل و نقل با وزن بالاتر 1.75 دلار در هر پوند خواهد بود. حمل و نقل با وزن بالا برای هر چیزی بیش از هفت پوند اعمال می شود.
به جای بازگشت به محاسبه هر خط به خط نرخ حمل و نقل، یک فرمول آرایه می تواند شامل یک عبارت شرطی IF برای تعیین هزینه حمل و نقل مناسب برای هر خط باشد:
=IF(C4:C8 < 7، C4:C8*B11، C4:C8*B12)
با یک تغییر سریع به یک تابع در یک سلول و افزودن نرخ حمل و نقل جدید در جدول نرخ‌ها، اکنون می‌توانیم ببینیم که نه تنها کل ستون حمل‌ونقل بر اساس وزن محاسبه می‌شود، بلکه مجموع‌ها نیز به‌طور خودکار نرخ جدید را دریافت می‌کنند. قیمت.
بنابراین فاکتور برای آینده اثبات می شود و اطمینان حاصل می کند که هر تغییری در آینده در محاسبه هزینه نیاز به تغییر تنها یک تابع دارد.
ترکیب شرطی های متعدد یا متفاوت می تواند به ما امکان انجام اقدامات مختلفی را بدهد. این منطق را می توان چندین روش در یک آرایه از داده ها استفاده کرد. برای مثال،
- اگر مقدار فرعی بیش از مقدار مشخصی باشد، می‌توان یک تابع IF اضافی برای نادیده گرفتن حمل و نقل اضافه کرد.
- یک جدول مالیاتی می تواند به مالیات دسته های مختلف محصولات با نرخ های مختلف اضافه شود.
مهم نیست که چقدر فاکتور پیچیده باشد، ویرایش یک سلول تنها برای محاسبه مجدد هر قسمت از صورت حساب لازم است.
مثال: میانگین اعداد N
فرمول‌های آرایه علاوه بر ساده‌سازی و به‌روزرسانی بخش‌های بزرگ داده، می‌توانند تکه‌ای از یک مجموعه داده را برای اهداف آزمایشی بکشند.
همانطور که مثال زیر نشان می دهد، بیرون کشیدن یک تکه داده از یک مجموعه داده بزرگ برای اهداف اعتبارسنجی با استفاده از فرمول های آرایه آسان است:
در مثال بالا، سلول D20 از یک تابع ساده برای میانگین هر n نتیجه حسگر 1 استفاده می‌کند. در این مورد، می‌توانیم nام را با استفاده از مقدار موجود در سلول D19 تعریف کنیم، که به ما کنترل اندازه نمونه را می‌دهد که میانگین را تعیین می‌کند:
=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16″”))
این به شما امکان می دهد تا به سرعت و به راحتی یک مجموعه داده بزرگ را به زیر مجموعه ها تقسیم کنید.
تسلط بر فرمول های آرایه در اکسل ضروری است
این مثال های ساده نقطه شروع خوبی برای درک منطق پشت فرمول های آرایه هستند. کمی به آنها فکر کنید تا بتوانید چند عملیات را به سرعت امروز و در آینده با تغییر دادن چند مرجع و توابع سلول انجام دهید. فرمول های آرایه روشی سبک و کارآمد برای برش و تاس کردن داده ها هستند. در ابتدای سفر یادگیری اکسل خود به آنها مسلط شوید تا بتوانید محاسبات آماری پیشرفته را با اطمینان انجام دهید.