سالها ساخت داشبوردها در اکسل به معنای مدیریت ستونهای کمکی، کشیدن فرمولها در میان سلولهای بیپایان و دعا کردن بود تا جدول محوریتان تحت وزن خود فرو نرود. اما این وضعیت تغییر کرده است. با ورود آرایههای دینامیک، اکسل روشی هوشمندانهتر و تمیزتر برای کار معرفی کرده است: فرمولهای پراکنده.
سالها، ساخت داشبوردها در اکسل به معنای دست و پا زدن با ستونهای کمکی، کشیدن فرمولها در سلولهای بیپایان و دعا کردن بود تا جدول محوریتان زیر وزن خود فرو نرود. اما این تغییر کرده است. با آمدن آرایههای پویا، اکسل روشی هوشمندتر و تمیزتر برای کار معرفی کرده است: فرمولهای ریلیز.
با استفاده از تنها یک فرمول، میتوانید یک جدول کامل ایجاد کنید، نمای فیلتر شدهای تولید کنید، یک مجموع تجمعی محاسبه کنید، یا فهرست فشردهای بسازید که هر بار که دادههای شما تغییر میکند، خود بهروز میشود. اگر آمادهاید تا اکسل کار سنگین را انجام دهد در حالی که شما بر بینشها متمرکز شوید، اینها ممکن است توابعی باشند که نمیدانستید به آنها نیاز دارید.
برای استفاده از این توابع، به اکسل برای Microsoft 365، اکسل برای Microsoft 365 برای مک، یا اکسل برای وب نیاز خواهید داشت. برخی ممکن است در اکسل 2021 یا اکسل 2024 نیز موجود باشند، اما در دسترس بودن متغیر است.
UNIQUE
راه هوشمندانه برای ساخت فهرستهای پویا
تابع UNIQUE فهرستی از مقادیر متمایز را از یک بازه یا آرایه برمیگرداند. میتوانید از آن برای استخراج نام محصولات، بخشها، ماهها یا هر دستهبندی دیگری از دادههای منبع خود استفاده کنید. همانطور که مجموعه داده شما بهروز میشود، فهرست استخراجشده بهصورت خودکار تازه میشود، که آن را برای منوهای کشویی، جدولهای محوری یا نمودارهایی که با افزودن ورودیهای جدید گسترش مییابند، ایدهآل میکند.
دستورالعمل به این صورت است:
=UNIQUE(array, [by_col], [exactly_once])
آرگومان array بازهای را که باید ارزیابی شود، مشخص میکند. آرگومان اختیاری by_col تعیین میکند مقایسه بر اساس ستونها (TRUE) یا ردیفها (FALSE) انجام شود. اگر این آرگومان حذف شود، اکسل بهطور پیشفرض ردیفها را مقایسه میکند. آرگومان اختیاری نهایی، exactly_once، فقط مقادیر یا ردیفهایی را برمیگرداند که یکبار در بازه ظاهر میشوند (زمانی که TRUE تنظیم شود).
تصور کنید فهرستی از نام محصولات در ستون A دارید که از فرمی وارد شدهاند و اغلب تکرار میشوند. با UNIQUE میتوانید فوراً این فهرست پراکنده را به منبع کشویی یا اسلایسری تمیز تبدیل کنید که هر زمان مقادیر جدیدی اضافه شد، خود بهروز میشود:
=UNIQUE(A:A, FALSE, TRUE)
این فرمول فقط مقادیر متمایز را از مجموعه داده شما برمیگرداند و بهصورت خودکار در سلولهای زیر آن پخش میشود. اگر مایل باشید فهرست را به ترتیب حروف الفبا نمایش دهید، آن را با SORT بپیچید:
=SORT(UNIQUE(A:A, FALSE, TRUE))
پس از اینکه نتایج UNIQUE در جای خود قرار گرفتند، میتوانید بهعنوان منبع لیست اعتبارسنجی دادهها به آن ارجاع دهید. منوی کشویی شما اکنون بهصورت پویا با رشد دادهها گسترش مییابد—بدون نیاز به کار اضافی.
FILTER
نمایشهای دادهای فوری و بهروز خودکار
داشبوردها برای پاسخ به سوالات وجود دارند. اغلب این سوالات شرطی هستند: «فقط معاملات با ارزش بالا را نشان بده» یا «آمار درآمد فصل چهارم چیست؟» با FILTER میتوانید یک یا چند معیار را بر روی یک بازه اعمال کنید و فقط ردیفهایی را که با شرایط سوال شما مطابقت دارند، برگردانید.
دستورالعمل پایه به این صورت است:
=FILTER(array, include, [if_empty])
آرگومان include ردیفها یا ستونهایی را که باید فیلتر شوند، مشخص میکند. آرگومان if_empty به اکسل میگوید اگر هیچ نتیجهای یافت نشد چه نمایش دهد؛ استفاده از این آرگومان الزامی نیست، اما من عادت دارم بهعنوان مقدار خالی (“”) یا پیام «none» اضافه کنم.
مثالی با یک مجموعه داده واقعی:
=FILTER(A2:N100, D2:D100="Online", "")
=FILTER(A2:N100, D2:D100=O2, "")
در این مثالها، اکسل بازه A2:N100 را اسکن میکند و فقط ردیفهایی را برمیگرداند که ستون «کانال فروش» (ستون D) برابر با «Online» باشد. برای راحتی تغییر فیلتر میتوانید reference a cell in Excel (مانند O2) را با شرط مشخص استفاده کنید.
همچنین میتوانید FILTER را با SORT ترکیب کنید تا نتایج را بهصورت حروف الفبا یا عددی مرتب کنید. با استفاده از عملگر + یا * داخل آرگومان include میتوانید معیارهای OR/AND متعدد را در یک فرمول اعمال کنید:
=SORT(FILTER(A2:N100, (D2:D100=O2)*(I2:I100>1500)))
این مثال تمام محصولاتی را که به صورت آنلاین سفارش داده شدهاند و بیش از ۱٬۵۰۰ واحد فروختهاند، بهصورت حروف الفبا مرتب میکند. بهعنوان یک نمودار یا عنصر داشبورد به آن لینک کنید و نمایی خواهید داشت که همیشه آخرین اعداد را نمایش میدهد—بدون نیاز به دکمهٔ تازهسازی.
BYROW
محاسبات هوشمندتر برای هر ردیف بدون ستونهای کمکی
پس از استخراج یا تمیز کردن دادههای منبع، اغلب نیاز به انجام محاسبهای برای هر ردیف دارید. این میتواند ضرب تعداد در قیمت، محاسبهٔ درصد تغییر از دورهای به دورهٔ دیگر، یا اعمال منطق متفاوت برای هر ردیف باشد. تابع BYROW دقیقاً برای اینگونه وظایف طراحی شده است.
این تابع یک تابع LAMBDA را بر هر ردیف یک آرایه مشخص اعمال میکند و نتایج را بهصورت یک ستون عمودی ریلیز میدهد. نحو آن به این صورت است:
=BYROW(array, lambda(row))
BYROW یک array (یا بازه) میگیرد، محاسبهٔ سفارشی شما را بر هر ردیف اعمال میکند و نتایج را در یک آرایهٔ تکستونی ریلیز میکند. برای این کار، آرگومان lambda باید یک تابع LAMBDA معتبر داشته باشد که بر روی یک ردیف محاسبهای انجام داده و یک مقدار بازگرداند. اگر مقادیر متعددی برگرداند (مثلاً سعی در مرتبسازی ردیف)، اکسل خطای #CALC! میدهد.
در عین حال میتوانید آرگومان row را هر نامی بدهید (r، data یا row). این فقط یک جایدار برای ردیف جاری است.
بهعنوان مثال، میتوانید از BYROW برای یافتن بزرگترین مقدار در هر ردیف بدون افزودن ستونهای کمکی استفاده کنید:
=BYROW(AM2:AR100, LAMBDA(r, MAX(r)))
این فرمول ستونهای Units Sold، Unit Price، Unit Cost، Total Revenue، Total Cost و Total Profit را برای هر ردیف از رکوردهای فروش من بررسی میکند و بزرگترین مقدار را در آن تراکنش خاص گزارش میدهد.
در همان مجموعه داده، میتوانید از BYROW برای محاسبهٔ حاشیهٔ هر ردیف با تفریق هزینهٔ واحد از قیمت واحد استفاده کنید:
=BYROW(AN2:AO100, LAMBDA(r, INDEX(r, 1, 1) - INDEX(r, 1, 2)))
بهطور کلی، زمانی که میخواهید یک فرمول واحد برای هر ردیف یک نتیجه بدهد بدون افزودن یا تغییر دادههای اصلی، از BYROW استفاده کنید.
REDUCE
خلاصههایی که بهصورت خودکار سازگار میشوند
در حالی که BYROW دادهها را ردیف به ردیف پردازش میکند، REDUCE تمام آرایه را به یک نتیجهٔ تجمعی واحد خلاصه میکند با اعمال مکرر یک تابع LAMBDA بر هر مقدار. نحو آن به این صورت است:
=REDUCE([initial_value], array, lambda(accumulator, value, body))
شما با یک initial_value (معمولاً صفر برای جمع یا یک برای ضرب) شروع میکنید و سپس هر عنصر در array را طی میکنید، بهطور مداوم accumulator را بهروزرسانی میکنید. وقتی تابع به آخرین مقدار رسید، نتیجهٔ نهایی بهدست میآید. مثال واقعی:
=REDUCE(0, AP2:AP100, LAMBDA(total,value, IF(value>100000, total+value, total)))
این فرمول هر مقدار در ستون «Total Revenue» را اسکن میکند و تنها زمانی که بیش از ۱۰۰٬۰۰۰ باشد، به مجموع تجمعی اضافه میکند. اگرچه تابع SUMIF میتوانست این شرط ساده را برآورده کند، اما REDUCE زمانی برتری مییابد که منطق شما پیچیدهتر شود. میتوانید چندین قانون را ترکیب کنید، شرایط تو در تو اعمال کنید یا محاسبههای سفارشی بسازید که با تغییر دادهها بهطور خودکار تطبیق پیدا میکنند.
در یک داشبورد، REDUCE میتواند به شما کمک کند متریکی مثل «تراکنشهای با ارزش بالا»، «میانگین ارزش سفارش برای مشتریان فعال» یا «تأثیر تجمعی سفارشات برگشتی» را محاسبه کنید و همه اینها در یک سلول خود‑بهروزرسانی که با تغییر مجموعه داده بهروزرسانی میشود، نمایش دهد.
SCAN
مشاهدهٔ تغییرات بهمرور زمان
در حالی که REDUCE تنها یک نتیجهٔ نهایی را برمیگرداند، SCAN تمام نتایج میانی را در طول مسیر نشان میدهد. نحو آن به این صورت است:
=SCAN ([initial_value], array, lambda(accumulator, value, body))
مانند REDUCE، با یک initial_value اختیاری که نقطه شروع accumulator را تعریف میکند، آغاز میشود. اگر با متن کار میکنید، میتوانید یک رشتهٔ خالی (“”) را بهعنوان مقدار اولیه تنظیم کنید. مثال مشابه به فرمول REDUCE قبلی:
=SCAN(0, AP2:AP100, LAMBDA(total,value, IF(value>100000, total+value, total)))
مانند REDUCE، این فرمول هر مقدار در ستون «Total Revenue» را طی میکند و فقط وقتی که بیش از ۱۰۰٬۰۰۰ باشد، به مجموع اضافه میکند. اما بر خلاف REDUCE که فقط مقدار نهایی را نشان میدهد، SCAN هر گام از تجمع را نشان میدهد.
برای داشبوردها، این موضوع امکانات جدیدی را میآفریند. میتوانید نتایج SCAN را مستقیماً به نمودارها متصل کنید تا نه تنها عدد نهایی، بلکه مسیر رسیدن به آن را هم بهصورت بصری نمایش دهند. یک نمودار خطی مبتنی بر فرمول SCAN بهصورت خودکار با ورود دادههای جدید بهروزرسانی میشود و روند را بهروزرسانی میکند. این بهخصوص در پیشبینیهای دورهای یا داشبوردهای پیشرونده که نمایش حرکت و مومنتوم همانقدر اهمیت دارد که خروجی نهایی، مؤثر است.
داشبوردی که خود بهخود بهروز میشود
این توابع بلوک ساختاری داشبوردهایی هستند که خودشان کار میکنند. منطق را یکبار تعریف میکنید و اکسل بقیه کارها را انجام میدهد. همانطور که دادههایتان رشد میکند، فیلترها تطبیق مییابند. وقتی دستهبندیهای جدید ظاهر میشوند، بهصورت خودکار منوهای کشویی شما را پر میکنند. وقتی تراکنشها تغییر میکنند، خلاصهها بهصورت لحظهای بهروز میشوند.
بهعلاوه توابع ریلیز دیگری نیز وجود دارند—SORT، XLOOKUP، SEQUENCE و غیره—but these are the ones that solve my biggest dashboard challenges day after day. They’re the difference between spending hours tweaking ranges and actually analyzing what your data is saying.
میتوانید بر بینشها متمرکز شوید و به اکسل بگذارید کارهای سنگین را انجام دهد.