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

معرفی تابع BYROW

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

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

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

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

در این فرمول، اکسل هر ردیف از محدوده B2:D5 را جداگانه به متغیر row می‌دهد و سپس تابع SUM مجموع همان ردیف را محاسبه می‌کند. نتیجه به صورت یک لیست عمودی نمایش داده می‌شود؛ یعنی برای هر سطر، یک خروجی جداگانه خواهید داشت.

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

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

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

ساختار (Syntax)

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

=BYROW(argument1, argument2, ...)

ساختار واقعی و رایج تابع BYROW در اکسل به شکل زیر است:

=BYROW(array, LAMBDA(row, calculation))

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

=BYROW(محدوده; LAMBDA(سطر; محاسبه_برای_هر_سطر))

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

آرگومان‌ها

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

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

برای مثال، اگر اطلاعات فروش ماه‌های مختلف در محدوده B2:D10 باشد، این محدوده همان آرگومان array است.

LAMBDA(row, calculation) / تابع سفارشی برای هر سطر

آرگومان دوم یک تابع LAMBDA است. LAMBDA مشخص می‌کند برای هر سطر چه محاسبه‌ای انجام شود. در این بخش معمولاً یک نام موقت مانند row برای هر سطر تعریف می‌کنیم و سپس عملیات مورد نظر را روی آن انجام می‌دهیم.

برای مثال، در فرمول زیر، row نماینده هر سطر از محدوده B2:D5 است و تابع SUM مجموع همان سطر را محاسبه می‌کند:

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

row / سطر جاری

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

برای مثال، فرمول زیر هم از نظر مفهومی همان کار را انجام می‌دهد، فقط به جای row از r استفاده شده است:

=BYROW(B2:D5, LAMBDA(r, SUM(r)))

calculation / محاسبه مورد نظر

این بخش مشخص می‌کند که روی هر سطر چه عملیاتی انجام شود؛ مثلاً مجموع، میانگین، بررسی شرط، شمارش یا جستجو. نکته مهم این است که محاسبه داخل BYROW باید برای هر سطر فقط یک نتیجه نهایی برگرداند، نه چند مقدار جداگانه.

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

مثال ۱: محاسبه مجموع هر ردیف

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

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

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

مثال ۲: محاسبه میانگین هر ردیف

اگر بخواهید به جای مجموع، میانگین نمرات هر دانش‌آموز را محاسبه کنید، کافی است از تابع AVERAGE استفاده کنید:

=BYROW(B2:D6, LAMBDA(row, AVERAGE(row)))

اکسل هر ردیف را جداگانه بررسی می‌کند و میانگین مقادیر همان ردیف را برمی‌گرداند.

مثال ۳: پیدا کردن بیشترین مقدار در هر ردیف

فرض کنید فروش سه ماه اول هر فروشنده در ستون‌های B تا D ثبت شده است. برای پیدا کردن بیشترین فروش هر فروشنده می‌توانید بنویسید:

=BYROW(B2:D10, LAMBDA(row, MAX(row)))

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

مثال ۴: تشخیص قبولی بر اساس مجموع نمرات

فرض کنید اگر مجموع نمرات هر دانش‌آموز حداقل 180 باشد، وضعیت او قبول در نظر گرفته شود. فرمول زیر این کار را انجام می‌دهد:

=BYROW(B2:D6, LAMBDA(row, IF(SUM(row)>=180, "قبول", "مردود")))

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

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

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

فرض کنید فروش سه ماه هر فروشنده در محدوده B2:D10 قرار دارد. اگر بخواهید بررسی کنید آیا فروشنده در همه ماه‌ها فروش بالاتر از 100 داشته است یا خیر، از فرمول زیر استفاده کنید:

=BYROW(B2:D10, LAMBDA(row, AND(row>100)))

اگر تمام مقادیر یک ردیف بزرگ‌تر از 100 باشند، خروجی TRUE خواهد بود؛ در غیر این صورت FALSE نمایش داده می‌شود.

مثال ۲: بررسی اینکه حداقل یک مقدار منفی در هر ردیف وجود دارد یا نه

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

=BYROW(B2:F10, LAMBDA(row, OR(row<0)))

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

مثال ۳: شمارش تعداد غیبت‌ها در هر ردیف با COUNTIF

فرض کنید وضعیت حضور هر دانش‌آموز در چند جلسه در محدوده B2:F20 ثبت شده و کلمه غایب برای جلسات غیبت استفاده شده است. برای شمارش تعداد غیبت‌های هر دانش‌آموز می‌توانید بنویسید:

=BYROW(B2:F20, LAMBDA(row, COUNTIF(row, "غایب")))

این فرمول تعداد دفعاتی را که کلمه غایب در هر ردیف تکرار شده است محاسبه می‌کند.

مثال ۴: امتیازدهی به عملکرد هر کارمند

فرض کنید امتیازهای عملکرد هر کارمند در سه شاخص مختلف در محدوده B2:D15 قرار دارد. می‌خواهید اگر میانگین امتیازها حداقل 80 بود، عبارت عالی و در غیر این صورت نیاز به بهبود نمایش داده شود.

=BYROW(B2:D15, LAMBDA(row, IF(AVERAGE(row)>=80, "عالی", "نیاز به بهبود")))

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

مثال ۵: ترکیب BYROW با XLOOKUP برای محاسبه مبلغ سفارش

فرض کنید در محدوده A2:B8، ستون A کد کالا و ستون B تعداد سفارش است. همچنین در محدوده H2:I20، ستون H کد کالا و ستون I قیمت واحد هر کالا قرار دارد. برای محاسبه مبلغ هر سفارش می‌توانید از فرمول زیر استفاده کنید:

=BYROW(A2:B8, LAMBDA(row, XLOOKUP(INDEX(row,1,1), H2:H20, I2:I20) * INDEX(row,1,2)))

در این فرمول، تابع XLOOKUP قیمت واحد کالا را بر اساس کد کالا پیدا می‌کند و سپس آن را در تعداد سفارش ضرب می‌کند.

مثال ۶: فیلتر کردن ردیف‌هایی که مجموع آن‌ها بیشتر از 300 است

اگر بخواهید فقط ردیف‌هایی از جدول A2:D20 را نمایش دهید که مجموع مقادیر ستون‌های B تا D آن‌ها بیشتر از 300 است، می‌توانید BYROW را با FILTER ترکیب کنید:

=FILTER(A2:D20, BYROW(B2:D20, LAMBDA(row, SUM(row)>300)))

در این مثال، BYROW برای هر ردیف یک نتیجه TRUE یا FALSE تولید می‌کند و تابع FILTER فقط ردیف‌هایی را نگه می‌دارد که شرط آن‌ها TRUE باشد.

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

تابع BYROW به تنهایی قدرتمند است، اما ارزش واقعی آن زمانی مشخص می‌شود که با توابع دیگر ترکیب شود. در ادامه چند ترکیب رایج و کاربردی را می‌بینید:

• ترکیب با SUM برای محاسبه مجموع هر ردیف:

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

• ترکیب با AVERAGE برای محاسبه میانگین هر ردیف:

  =BYROW(B2:D10, LAMBDA(row, AVERAGE(row)))

• ترکیب با IF برای دسته‌بندی نتیجه هر ردیف:

  =BYROW(B2:D10, LAMBDA(row, IF(SUM(row)>=250, "مطلوب", "نامطلوب")))

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

  =BYROW(B2:D10, LAMBDA(row, AND(row>=50)))

• ترکیب با OR برای بررسی وجود حداقل یک مقدار خاص در هر ردیف:

  =BYROW(B2:D10, LAMBDA(row, OR(row=0)))

• ترکیب با COUNTIF برای شمارش مقادیر خاص در هر ردیف:

  =BYROW(B2:F10, LAMBDA(row, COUNTIF(row, "تاخیر")))

• ترکیب با LET برای خواناتر کردن فرمول‌های طولانی:

  =BYROW(B2:D10, LAMBDA(row, LET(total, SUM(row), IF(total>=300, "هدف محقق شد", "کمتر از هدف"))))

• ترکیب با XLOOKUP برای جستجو و محاسبه بر اساس اطلاعات هر ردیف:

  =BYROW(A2:B10, LAMBDA(row, XLOOKUP(INDEX(row,1,1), H2:H20, I2:I20) * INDEX(row,1,2)))

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

۱. خطای #NAME?

این خطا معمولاً زمانی رخ می‌دهد که نسخه اکسل شما از تابع BYROW یا LAMBDA پشتیبانی نمی‌کند، یا نام تابع را اشتباه تایپ کرده‌اید.

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

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

اگر تابع LAMBDA را درست ننویسید یا تعداد پارامترهای آن با چیزی که BYROW انتظار دارد هماهنگ نباشد، ممکن است با خطای #VALUE! روبه‌رو شوید.

روش رفع: در BYROW، تابع LAMBDA باید معمولاً یک پارامتر برای سطر جاری داشته باشد. الگوی صحیح به شکل زیر است:

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

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

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

روش رفع: محاسبه داخل LAMBDA را طوری بنویسید که خروجی نهایی آن یک مقدار واحد باشد؛ مثل SUM، AVERAGE، MAX، COUNTIF یا IF که در نهایت یک نتیجه برای هر ردیف برمی‌گردانند.

۴. خطای #SPILL!

چون BYROW یک تابع آرایه‌ای داینامیک است، خروجی آن در سلول‌های پایین‌تر پخش می‌شود. اگر مسیر خروجی توسط داده‌های دیگر اشغال شده باشد، اکسل خطای #SPILL! نشان می‌دهد.

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

۵. نتیجه اشتباه به دلیل انتخاب محدوده نادرست

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

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

۶. مشکل جداکننده آرگومان‌ها

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

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

=BYROW(B2:D10; LAMBDA(row; SUM(row)))

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

• برای خوانایی بهتر، داخل LAMBDA از نام‌های ساده و معنی‌دار مثل row، r یا سطر استفاده کنید.
• اگر فرمول شما طولانی شد، از تابع LET برای تعریف متغیرهای میانی استفاده کنید.
• در BYROW خروجی هر سطر باید یک مقدار واحد باشد؛ بنابراین از فرمول‌هایی که برای هر سطر چند خروجی تولید می‌کنند با دقت استفاده کنید.
• تابع BYROW برای گزارش‌های پویا بسیار مناسب است، چون با تغییر داده‌ها خروجی نیز به‌روزرسانی می‌شود.
• اگر می‌خواهید محاسبه را ستون به ستون انجام دهید، به جای BYROW از تابع BYCOL استفاده کنید.
• برای فیلتر کردن ردیف‌ها بر اساس یک محاسبه ردیفی، ترکیب BYROW با FILTER بسیار کاربردی است.
• در جدول‌های بزرگ، فرمول را تا حد امکان ساده نگه دارید تا سرعت محاسبات کاهش پیدا نکند.
• اگر قصد دارید فرمول را برای کاربران مبتدی قابل فهم‌تر کنید، از ستون‌های کمکی استفاده کنید یا فرمول را با LET ساختاربندی کنید.
• قبل از استفاده از BYROW مطمئن شوید نسخه اکسل شما از توابع آرایه‌ای داینامیک پشتیبانی می‌کند.

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

تفاوت BYROW با BYCOL

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

مثال محاسبه مجموع هر ستون با BYCOL:

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

تفاوت BYROW با MAP

تابع MAP برای اعمال یک LAMBDA روی تک‌تک عناصر یا آرایه‌های هم‌اندازه استفاده می‌شود. اما BYROW کل یک ردیف را به عنوان ورودی به LAMBDA می‌دهد. بنابراین وقتی منطق شما وابسته به کل ردیف است، BYROW ساده‌تر و مناسب‌تر است.

تفاوت BYROW با فرمول‌های کپی‌شده در هر ردیف

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

تفاوت BYROW با SUMPRODUCT

تابع SUMPRODUCT بیشتر برای محاسبات آرایه‌ای، ضرب و جمع شرطی استفاده می‌شود. اما BYROW برای اجرای یک منطق مشخص روی هر سطر طراحی شده است. اگر هدف شما تولید یک خروجی جداگانه برای هر ردیف باشد، BYROW معمولاً واضح‌تر و خواناتر است.

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

تابع BYROW جزو توابع جدید اکسل است و در نسخه‌هایی در دسترس است که از آرایه‌های داینامیک و تابع LAMBDA پشتیبانی می‌کنند.

• Microsoft 365: پشتیبانی می‌شود.
• Excel 2021: در بسیاری از نسخه‌های به‌روزشده پشتیبانی می‌شود.
• Excel for the web: پشتیبانی می‌شود.
• Excel 2019 و نسخه‌های قدیمی‌تر: معمولاً پشتیبانی نمی‌شود.
• Excel 2016 و قبل‌تر: پشتیبانی نمی‌شود.

اگر هنگام نوشتن تابع BYROW با خطای #NAME? روبه‌رو شدید، احتمالاً نسخه اکسل شما از این تابع پشتیبانی نمی‌کند یا نیاز به به‌روزرسانی دارد.

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

آیا تابع BYROW برای افراد مبتدی مناسب است؟

بله، اگر با مفهوم ردیف، محدوده و چند تابع ساده مثل SUM و IF آشنا باشید، می‌توانید به‌راحتی از BYROW استفاده کنید. فقط باید کمی با ساختار LAMBDA آشنا شوید.

آیا BYROW فقط برای عددها کاربرد دارد؟

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

آیا خروجی BYROW همیشه عمودی است؟

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

آیا می‌توان داخل BYROW از IF استفاده کرد؟

بله، یکی از رایج‌ترین کاربردهای BYROW ترکیب آن با IF برای دسته‌بندی یا برچسب‌گذاری ردیف‌هاست.

=BYROW(B2:D10, LAMBDA(row, IF(SUM(row)>200, "خوب", "ضعیف")))

آیا BYROW جایگزین کامل فرمول‌های معمولی است؟

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

فرق BYROW و BYCOL چیست؟

BYROW ردیف به ردیف محاسبه می‌کند، اما BYCOL ستون به ستون. انتخاب بین این دو به ساختار داده‌های شما بستگی دارد.

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

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

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