آموزش تابع BYCOL در اکسل

معرفی تابع BYCOL

تابع BYCOL یکی از توابع جدید و بسیار کاربردی اکسل است که برای انجام یک محاسبه روی هر ستون از یک محدوده یا آرایه استفاده می‌شود. نام این تابع از عبارت By Column گرفته شده؛ یعنی «ستون به ستون».

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

تابع BYCOL معمولا همراه با تابع LAMBDA استفاده می‌شود. LAMBDA مشخص می‌کند که روی هر ستون چه عملیاتی انجام شود.

مثال ساده: فرض کنید فروش محصولات در محدوده B2:E6 قرار دارد و هر ستون مربوط به یک ماه است. برای محاسبه جمع فروش هر ماه می‌توانید از فرمول زیر استفاده کنید:

=BYCOL(B2:E6,LAMBDA(col,SUM(col)))

در این فرمول، اکسل هر ستون از محدوده B2:E6 را جداگانه بررسی می‌کند، سپس تابع SUM را روی همان ستون اجرا می‌کند و در نهایت جمع هر ستون را به صورت افقی نمایش می‌دهد.

کاربردهای اصلی تابع BYCOL

• محاسبه جمع، میانگین، بیشترین و کمترین مقدار برای هر ستون از یک جدول
• بررسی شرط‌ها به صورت ستون‌به‌ستون، مثل اینکه آیا همه مقادیر یک ستون از عدد خاصی بیشتر هستند یا نه
• شمارش تعداد مقادیر خاص در هر ستون، مثل تعداد نمرات بالاتر از ۱۸ یا تعداد سلول‌های خالی
• ساخت گزارش‌های خلاصه از داده‌های جدولی بدون نیاز به فرمول‌نویسی تکراری برای هر ستون
• ترکیب با توابعی مثل SUM، AVERAGE، MAX، MIN، COUNTIF، AND، OR، IF و XLOOKUP
• کاهش تعداد فرمول‌ها و افزایش خوانایی فایل‌های اکسل
• استفاده در داشبوردها و گزارش‌های داینامیک

ساختار (Syntax)

ساختار انگلیسی تابع BYCOL به شکل زیر است:

=BYCOL(array,lambda(column))

ساختار فارسی و مفهومی تابع به شکل زیر است:

=BYCOL(محدوده یا آرایه;عملیات قابل اجرا روی هر ستون)

نکته مهم این است که آرگومان دوم باید معمولا با تابع LAMBDA نوشته شود و LAMBDA باید مشخص کند که با هر ستون چه کاری انجام شود.

آرگومان‌ها

array / محدوده یا آرایه

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

مثلا در فرمول زیر، محدوده B2:E6 آرایه‌ای است که BYCOL روی ستون‌های آن کار می‌کند:

=BYCOL(B2:E6,LAMBDA(col,SUM(col)))

lambda(column) / عملیات روی هر ستون

این آرگومان مشخص می‌کند که برای هر ستون چه محاسبه‌ای انجام شود. معمولا از تابع LAMBDA استفاده می‌کنیم و داخل آن یک نام دلخواه برای ستون می‌گذاریم. در بسیاری از مثال‌ها از نام col استفاده می‌شود، اما می‌توانید نام‌های دیگری هم انتخاب کنید.

در مثال زیر، col نماینده هر ستون است و تابع AVERAGE میانگین هر ستون را محاسبه می‌کند:

=BYCOL(B2:E6,LAMBDA(col,AVERAGE(col)))

تابع LAMBDA در BYCOL باید برای هر ستون فقط یک نتیجه برگرداند. اگر برای هر ستون چند مقدار برگرداند، معمولا با خطا روبه‌رو می‌شوید.

مثال‌های ساده و پایه

مثال ۱: جمع هر ستون

فرض کنید در محدوده B2:D6 فروش سه ماه مختلف ثبت شده است. هر ستون مربوط به یک ماه است. برای محاسبه جمع فروش هر ماه از فرمول زیر استفاده می‌کنیم:

=BYCOL(B2:D6,LAMBDA(col,SUM(col)))

نتیجه این فرمول یک ردیف افقی شامل جمع هر ستون است. مثلا اگر ستون B مربوط به فروردین، ستون C مربوط به اردیبهشت و ستون D مربوط به خرداد باشد، خروجی فرمول جمع فروش هر ماه را جداگانه نمایش می‌دهد.

مثال ۲: میانگین هر ستون

فرض کنید نمرات دانش‌آموزان در چند درس در محدوده B2:E10 قرار دارد و هر ستون مربوط به یک درس است. برای محاسبه میانگین هر درس از فرمول زیر استفاده کنید:

=BYCOL(B2:E10,LAMBDA(col,AVERAGE(col)))

اکسل میانگین هر ستون را محاسبه کرده و نتایج را به ترتیب در سلول‌های کنار هم نمایش می‌دهد.

مثال ۳: بیشترین مقدار هر ستون

برای پیدا کردن بیشترین مقدار در هر ستون، می‌توانید BYCOL را با MAX ترکیب کنید:

=BYCOL(B2:E10,LAMBDA(col,MAX(col)))

این فرمول بیشترین مقدار هر ستون را جداگانه برمی‌گرداند؛ مثلا بالاترین نمره هر درس یا بیشترین فروش هر ماه.

مثال ۴: کمترین مقدار هر ستون

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

=BYCOL(B2:E10,LAMBDA(col,MIN(col)))

این فرمول کوچک‌ترین مقدار هر ستون را محاسبه می‌کند.

مثال‌های کاربردی و واقعی

مثال ۱: شمارش تعداد نمرات بالاتر از ۸۰ در هر درس

فرض کنید نمرات دانش‌آموزان در محدوده B2:E20 قرار دارد و هر ستون مربوط به یک درس است. برای شمارش تعداد نمرات بالاتر از ۸۰ در هر درس، از COUNTIF داخل BYCOL استفاده می‌کنیم:

=BYCOL(B2:E20,LAMBDA(col,COUNTIF(col,">80")))

خروجی این فرمول نشان می‌دهد در هر ستون چند مقدار بزرگ‌تر از ۸۰ وجود دارد.

مثال ۲: بررسی اینکه آیا همه مقادیر هر ستون قابل قبول هستند یا نه

فرض کنید نمرات قبولی باید حداقل ۱۰ باشند. می‌خواهیم بررسی کنیم آیا همه نمرات هر درس در محدوده B2:E15 حداقل ۱۰ هستند یا خیر:

=BYCOL(B2:E15,LAMBDA(col,AND(col>=10)))

نتیجه برای هر ستون TRUE یا FALSE است. اگر همه مقدارهای یک ستون ۱۰ یا بیشتر باشند، نتیجه آن ستون TRUE می‌شود؛ در غیر این صورت FALSE نمایش داده می‌شود.

مثال ۳: بررسی وجود یک مقدار خاص در هر ستون

فرض کنید در یک جدول حضور و غیاب، کلمه «غایب» در سلول‌ها ثبت شده است. برای اینکه بفهمیم در هر روز حداقل یک نفر غایب بوده یا نه، می‌توانیم از OR استفاده کنیم:

=BYCOL(B2:F20,LAMBDA(col,OR(col="غایب")))

اگر در یک ستون حداقل یک سلول برابر با «غایب» باشد، نتیجه آن ستون TRUE خواهد بود.

مثال ۴: دریافت اطلاعات یک محصول در چند ستون با XLOOKUP

فرض کنید کد محصولات در محدوده A2:A100 قرار دارد و فروش ماه‌های مختلف در محدوده B2:E100 ثبت شده است. اگر کد محصول مورد نظر در سلول G2 باشد، می‌توانید با BYCOL و XLOOKUP فروش همان محصول را برای هر ماه استخراج کنید:

=BYCOL(B2:E100,LAMBDA(col,XLOOKUP(G2,A2:A100,col)))

این فرمول در هر ستون فروش، مقدار مربوط به محصول انتخاب‌شده را پیدا می‌کند و خروجی را به صورت افقی نمایش می‌دهد.

مثال ۵: جمع فقط مقادیر مثبت هر ستون

فرض کنید در محدوده B2:E20 مقادیر سود و زیان ثبت شده است. برای جمع کردن فقط مقادیر مثبت هر ستون، می‌توانید از FILTER و SUM داخل BYCOL استفاده کنید:

=BYCOL(B2:E20,LAMBDA(col,IFERROR(SUM(FILTER(col,col>0)),0)))

در این فرمول، فقط مقادیر مثبت هر ستون فیلتر و سپس جمع می‌شوند. اگر ستونی هیچ مقدار مثبتی نداشته باشد، IFERROR نتیجه را صفر نمایش می‌دهد.

ترکیب تابع BYCOL با فرمول‌های دیگر

• ترکیب با SUM برای محاسبه جمع هر ستون

=BYCOL(B2:E10,LAMBDA(col,SUM(col)))

• ترکیب با AVERAGE برای محاسبه میانگین هر ستون

=BYCOL(B2:E10,LAMBDA(col,AVERAGE(col)))

• ترکیب با MAX برای پیدا کردن بیشترین مقدار هر ستون

=BYCOL(B2:E10,LAMBDA(col,MAX(col)))

• ترکیب با MIN برای پیدا کردن کمترین مقدار هر ستون

=BYCOL(B2:E10,LAMBDA(col,MIN(col)))

• ترکیب با COUNTIF برای شمارش مقادیر دارای شرط در هر ستون

=BYCOL(B2:E20,LAMBDA(col,COUNTIF(col,">=18")))

• ترکیب با AND برای بررسی درست بودن یک شرط در تمام سلول‌های هر ستون

=BYCOL(B2:E20,LAMBDA(col,AND(col>=50)))

• ترکیب با OR برای بررسی وجود حداقل یک مقدار مطابق شرط در هر ستون

=BYCOL(B2:E20,LAMBDA(col,OR(col="تایید")))

• ترکیب با IF برای ساخت نتیجه‌های متنی بر اساس محاسبات هر ستون

=BYCOL(B2:E10,LAMBDA(col,IF(AVERAGE(col)>=70,"قبول","نیاز به بررسی")))

• ترکیب با XLOOKUP برای جستجوی یک ردیف خاص و برگرداندن مقدار متناظر از هر ستون

=BYCOL(B2:E100,LAMBDA(col,XLOOKUP(G2,A2:A100,col)))

• ترکیب با ROUND برای گرد کردن نتیجه محاسبه هر ستون

=BYCOL(B2:E10,LAMBDA(col,ROUND(AVERAGE(col),2)))

خطاهای رایج و روش رفع آنها

۱. خطای #VALUE! به دلیل تعریف اشتباه LAMBDA

اگر تابع LAMBDA تعداد پارامترهای درستی نداشته باشد یا به شکل اشتباه نوشته شود، ممکن است خطای #VALUE! دریافت کنید.

نمونه فرمول اشتباه:

=BYCOL(B2:E10,LAMBDA(SUM(B2:E10)))

روش رفع خطا: باید برای LAMBDA یک پارامتر تعریف کنید که نماینده هر ستون باشد، سپس عملیات را روی همان پارامتر انجام دهید.

=BYCOL(B2:E10,LAMBDA(col,SUM(col)))

۲. خطای #CALC! به دلیل برگرداندن چند مقدار برای هر ستون

تابع BYCOL انتظار دارد LAMBDA برای هر ستون فقط یک نتیجه برگرداند. اگر داخل LAMBDA فرمولی بنویسید که چند مقدار تولید کند، ممکن است خطای #CALC! ببینید.

نمونه فرمول مشکل‌دار:

=BYCOL(B2:E10,LAMBDA(col,FILTER(col,col>0)))

روش رفع خطا: خروجی هر ستون را به یک مقدار خلاصه تبدیل کنید؛ مثلا از SUM، COUNT، AVERAGE یا TEXTJOIN استفاده کنید.

=BYCOL(B2:E10,LAMBDA(col,IFERROR(SUM(FILTER(col,col>0)),0)))

۳. خطای #SPILL! به دلیل نبود فضای کافی برای خروجی

تابع BYCOL یک خروجی آرایه‌ای افقی تولید می‌کند. اگر سلول‌های سمت راست محل فرمول خالی نباشند، اکسل نمی‌تواند نتیجه را نمایش دهد و خطای #SPILL! ظاهر می‌شود.

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

۴. خطای #NAME? به دلیل پشتیبانی نشدن تابع در نسخه اکسل

اگر نسخه اکسل شما از توابع آرایه‌ای جدید پشتیبانی نکند، اکسل ممکن است BYCOL یا LAMBDA را نشناسد و خطای #NAME? نمایش دهد.

روش رفع خطا: از Microsoft 365 یا نسخه‌های جدید اکسل که از Dynamic Arrays پشتیبانی می‌کنند استفاده کنید.

۵. خطا به دلیل استفاده از جداکننده اشتباه بین آرگومان‌ها

در برخی تنظیمات منطقه‌ای، جداکننده آرگومان‌ها کاما است و در برخی دیگر نقطه‌ویرگول. اگر فرمول شما خطا می‌دهد، ممکن است مشکل از همین جداکننده باشد.

نمونه با کاما:

=BYCOL(B2:E10,LAMBDA(col,SUM(col)))

نمونه با نقطه‌ویرگول:

=BYCOL(B2:E10;LAMBDA(col;SUM(col)))

روش رفع خطا: مطابق تنظیمات اکسل خود از جداکننده درست استفاده کنید.

۶. خطای #DIV/0! هنگام محاسبه میانگین ستون‌های بدون عدد

اگر از AVERAGE استفاده کنید و یک ستون هیچ مقدار عددی نداشته باشد، ممکن است خطای #DIV/0! دریافت کنید.

روش رفع خطا: از IFERROR برای کنترل خطا استفاده کنید.

=BYCOL(B2:E10,LAMBDA(col,IFERROR(AVERAGE(col),0)))

نکات حرفه‌ای و ترفندهای مهم

• برای خوانایی بهتر، در LAMBDA از نام‌های واضح مثل col، month یا score استفاده کنید.
• تابع BYCOL برای محاسبات ستونی عالی است؛ اگر می‌خواهید محاسبه را ردیف‌به‌ردیف انجام دهید، تابع BYROW مناسب‌تر است.
• خروجی BYCOL به صورت افقی پخش می‌شود؛ قبل از نوشتن فرمول مطمئن شوید سلول‌های سمت راست خالی هستند.
• اگر احتمال خطا در داده‌ها وجود دارد، از IFERROR داخل LAMBDA استفاده کنید.
• برای گزارش‌های خلاصه، می‌توانید BYCOL را با توابعی مثل SUM، AVERAGE، MAX، MIN و COUNTIF ترکیب کنید.
• اگر می‌خواهید عنوان ستون‌ها را کنار نتایج نمایش دهید، می‌توانید از VSTACK یا HSTACK در نسخه‌های جدید اکسل کمک بگیرید.
• در فایل‌های آموزشی یا تیمی، بهتر است کنار فرمول توضیح کوتاهی درباره نقش LAMBDA بنویسید تا دیگران راحت‌تر فرمول را درک کنند.
• اگر خروجی هر ستون باید یک متن باشد، می‌توانید از TEXTJOIN استفاده کنید، اما دقت کنید خروجی هر ستون باید یک مقدار نهایی باشد.

تفاوت تابع BYCOL با توابع مشابه

تفاوت BYCOL با BYROW

تابع BYCOL عملیات را روی هر ستون انجام می‌دهد، اما BYROW عملیات را روی هر ردیف انجام می‌دهد. اگر جدول شما چند ستون ماهانه دارد و می‌خواهید جمع هر ماه را بگیرید، BYCOL مناسب است. اگر هر ردیف مربوط به یک مشتری است و می‌خواهید جمع خرید هر مشتری را بگیرید، BYROW مناسب‌تر است.

=BYROW(B2:E10,LAMBDA(row,SUM(row)))

تفاوت BYCOL با SUM

تابع SUM به تنهایی معمولا کل محدوده را جمع می‌کند، اما BYCOL می‌تواند SUM را جداگانه روی هر ستون اجرا کند.

=SUM(B2:E10)

=BYCOL(B2:E10,LAMBDA(col,SUM(col)))

فرمول اول جمع کل محدوده را می‌دهد، اما فرمول دوم جمع هر ستون را جداگانه نمایش می‌دهد.

تفاوت BYCOL با فرمول‌نویسی دستی برای هر ستون

در روش سنتی ممکن است برای هر ستون یک فرمول جدا بنویسید؛ مثلا برای B، C، D و E جداگانه SUM بگیرید. اما با BYCOL فقط یک فرمول می‌نویسید و اکسل خودش آن را برای همه ستون‌ها اجرا می‌کند.

تفاوت BYCOL با MAP

تابع MAP برای اجرای عملیات روی هر عنصر یا چند آرایه به صورت عنصر به عنصر استفاده می‌شود، اما BYCOL واحد پردازش را ستون در نظر می‌گیرد. بنابراین وقتی هدف شما تحلیل ستونی است، BYCOL ساده‌تر و مناسب‌تر است.

سازگاری با نسخه‌های مختلف اکسل

تابع BYCOL از توابع جدید اکسل است و در نسخه‌های قدیمی اکسل وجود ندارد. این تابع معمولا در نسخه‌های زیر در دسترس است:

• Microsoft 365
• Excel for Microsoft 365 در ویندوز و مک
• Excel for the web
• نسخه‌های جدید اکسل که از Dynamic Array Functions پشتیبانی می‌کنند

در نسخه‌هایی مثل Excel 2016 یا Excel 2019 ممکن است تابع BYCOL در دسترس نباشد. اگر در فایل خود خطای #NAME? می‌بینید، احتمالا نسخه اکسل شما این تابع را پشتیبانی نمی‌کند.

همچنین توجه کنید که تابع BYCOL به همراه LAMBDA بهترین عملکرد را در اکسل‌های جدید دارد. اگر فایل را برای دیگران ارسال می‌کنید، مطمئن شوید نسخه اکسل آن‌ها نیز از این تابع پشتیبانی می‌کند.

سؤالات پرتکرار درباره تابع BYCOL

تابع BYCOL چه کاری انجام می‌دهد؟

این تابع یک محدوده را ستون‌به‌ستون بررسی می‌کند و یک عملیات مشخص را روی هر ستون انجام می‌دهد.

آیا BYCOL بدون LAMBDA کار می‌کند؟

در عمل، BYCOL معمولا همراه با LAMBDA استفاده می‌شود، چون LAMBDA مشخص می‌کند روی هر ستون چه محاسبه‌ای انجام شود.

خروجی تابع BYCOL عمودی است یا افقی؟

خروجی BYCOL معمولا به صورت افقی نمایش داده می‌شود، چون برای هر ستون یک نتیجه تولید می‌کند.

اگر بخواهم محاسبه را روی هر ردیف انجام دهم چه تابعی مناسب است؟

برای محاسبه ردیف‌به‌ردیف باید از تابع BYROW استفاده کنید.

چرا فرمول BYCOL من خطای #SPILL! می‌دهد؟

چون فضای کافی برای نمایش خروجی وجود ندارد. سلول‌های سمت راست فرمول را خالی کنید.

آیا می‌توانم BYCOL را با COUNTIF ترکیب کنم؟

بله، یکی از کاربردهای رایج BYCOL ترکیب با COUNTIF برای شمارش شرطی در هر ستون است.

=BYCOL(B2:E20,LAMBDA(col,COUNTIF(col,">0")))

آیا BYCOL در اکسل ۲۰۱۶ وجود دارد؟

خیر، در بیشتر نسخه‌های قدیمی مثل Excel 2016 این تابع وجود ندارد و باید از Microsoft 365 یا نسخه‌های جدیدتر استفاده کنید.

جمع‌بندی و پیشنهاد یادگیری بعدی

تابع BYCOL یکی از بهترین ابزارهای اکسل برای تحلیل ستونی داده‌هاست. با این تابع می‌توانید فقط با یک فرمول، روی هر ستون از یک جدول عملیات جداگانه انجام دهید؛ مثل جمع، میانگین، شمارش شرطی، بررسی شرط‌ها یا جستجوی اطلاعات.

مهم‌ترین نکته در استفاده از BYCOL این است که آن را همراه با LAMBDA به کار ببرید و مطمئن شوید خروجی هر ستون فقط یک مقدار است. اگر این اصل را رعایت کنید، می‌توانید فرمول‌های بسیار تمیز، کوتاه و حرفه‌ای بسازید.

برای یادگیری بهتر، پیشنهاد می‌شود بعد از BYCOL سراغ توابع BYROW، LAMBDA، MAP، FILTER، HSTACK و VSTACK بروید. این توابع در کنار هم قدرت زیادی برای ساخت گزارش‌های داینامیک و فرمول‌های پیشرفته در اکسل به شما می‌دهند.