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

معرفی تابع MAP

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

به زبان ساده، MAP به اکسل می‌گوید: «برای هر مقدار داخل این محدوده، این کار را انجام بده و نتیجه را برگردان.» این تابع معمولاً همراه با تابع LAMBDA استفاده می‌شود؛ چون باید مشخص کنیم روی هر مقدار چه عملیاتی انجام شود.

مثلاً فرض کنید قیمت کالاها در محدوده A2:A6 قرار دارد و می‌خواهید به همه قیمت‌ها ۹ درصد مالیات اضافه کنید. فرمول زیر برای هر قیمت، مقدار آن را در ۱.۰۹ ضرب می‌کند:

=MAP(A2:A6, LAMBDA(price, price*1.09))

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

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

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

ساختار (Syntax)

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

=MAP(array1, [array2], ..., lambda)

ساختار مفهومی تابع MAP با آرگومان‌های فارسی به شکل زیر است:

=MAP(آرایه۱; [آرایه۲]; ...; لامبدا)

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

آرگومان‌ها

array1 / آرایه اول

اولین محدوده یا آرایه‌ای است که می‌خواهید تابع MAP روی مقادیر آن اجرا شود. این آرگومان اجباری است. برای مثال می‌تواند محدوده A2:A10 یا یک آرایه ثابت باشد.

array2, … / آرایه‌های بعدی

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

lambda / تابع LAMBDA

تابع LAMBDA مشخص می‌کند که برای هر مقدار چه کاری انجام شود. تعداد پارامترهای LAMBDA باید با تعداد آرایه‌هایی که به MAP داده‌اید برابر باشد. اگر یک آرایه دارید، LAMBDA باید یک پارامتر داشته باشد. اگر دو آرایه دارید، LAMBDA باید دو پارامتر داشته باشد.

مثلاً در فرمول زیر فقط یک آرایه داریم؛ بنابراین LAMBDA فقط یک پارامتر به نام x دارد:

=MAP(A2:A6, LAMBDA(x, x*2))

اما در فرمول زیر دو آرایه داریم؛ بنابراین LAMBDA دو پارامتر به نام price و discount دارد:

=MAP(A2:A6, B2:B6, LAMBDA(price, discount, price*(1-discount)))

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

مثال ۱: دو برابر کردن مقادیر یک ستون

فرض کنید اعداد در محدوده A2:A6 قرار دارند و می‌خواهید هر عدد را دو برابر کنید:

=MAP(A2:A6, LAMBDA(x, x*2))

این فرمول روی هر سلول از A2:A6 اجرا می‌شود و خروجی را به صورت یک آرایه در سلول‌های پایین‌تر نمایش می‌دهد.

مثال ۲: اضافه کردن ۹ درصد مالیات به قیمت‌ها

اگر قیمت‌ها در محدوده B2:B10 باشند، برای محاسبه قیمت نهایی با مالیات می‌توانید بنویسید:

=MAP(B2:B10, LAMBDA(price, price*1.09))

نتیجه برای هر قیمت، مقدار قیمت به‌اضافه ۹ درصد مالیات خواهد بود.

مثال ۳: تشخیص قبولی یا مردودی

فرض کنید نمره دانش‌آموزان در محدوده C2:C12 قرار دارد. اگر نمره ۱۰ یا بیشتر باشد، وضعیت «قبول» و در غیر این صورت «مردود» نمایش داده شود:

=MAP(C2:C12, LAMBDA(score, IF(score>=10, "قبول", "مردود")))

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

مثال ۴: محاسبه قیمت بعد از تخفیف

فرض کنید قیمت کالاها در A2:A6 و درصد تخفیف در B2:B6 قرار دارد. فرمول زیر قیمت نهایی هر کالا را محاسبه می‌کند:

=MAP(A2:A6, B2:B6, LAMBDA(price, discount, price*(1-discount)))

در این مثال، MAP هم‌زمان مقدار قیمت و تخفیف هر ردیف را می‌گیرد و محاسبه را ردیف‌به‌ردیف انجام می‌دهد.

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

مثال ۱: محاسبه فروش خالص با کسر مرجوعی

فرض کنید فروش هر محصول در B2:B8 و مبلغ مرجوعی در C2:C8 قرار دارد. برای محاسبه فروش خالص هر ردیف بنویسید:

=MAP(B2:B8, C2:C8, LAMBDA(sales, returns, sales-returns))

این فرمول برای هر ردیف، مبلغ مرجوعی را از فروش کم می‌کند و فروش خالص را برمی‌گرداند.

مثال ۲: محاسبه مجموع سه ماه فروش با SUM

فرض کنید فروش ماه اول، دوم و سوم در ستون‌های B، C و D قرار دارد. برای محاسبه مجموع هر ردیف می‌توانید از MAP همراه با SUM استفاده کنید:

=MAP(B2:B10, C2:C10, D2:D10, LAMBDA(m1, m2, m3, SUM(m1, m2, m3)))

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

مثال ۳: بررسی هم‌زمان دو شرط با AND

فرض کنید موجودی کالا در B2:B10 و وضعیت کالا در C2:C10 قرار دارد. می‌خواهید بررسی کنید کالا هم موجودی مثبت داشته باشد و هم وضعیت آن «فعال» باشد:

=MAP(B2:B10, C2:C10, LAMBDA(stock, status, AND(stock>0, status="فعال")))

خروجی این فرمول TRUE یا FALSE است. اگر هر دو شرط برقرار باشد TRUE نمایش داده می‌شود.

مثال ۴: بررسی یکی از چند شرط با OR

فرض کنید در ستون B وضعیت پرداخت و در ستون C وضعیت ارسال قرار دارد. اگر پرداخت انجام شده باشد یا ارسال شده باشد، نتیجه TRUE شود:

=MAP(B2:B10, C2:C10, LAMBDA(payment, shipping, OR(payment="پرداخت شده", shipping="ارسال شده")))

این فرمول برای هر ردیف بررسی می‌کند که حداقل یکی از دو شرط برقرار باشد.

مثال ۵: شمارش تکرار هر کد با COUNTIF

فرض کنید کد کالاها در محدوده A2:A20 قرار دارد و می‌خواهید بدانید هر کد چند بار در لیست تکرار شده است:

=MAP(A2:A20, LAMBDA(code, COUNTIF(A2:A20, code)))

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

مثال ۶: پیدا کردن قیمت کالا با XLOOKUP

فرض کنید کد کالاهای سفارش در A2:A10 قرار دارد. در جدول Products، ستون Code شامل کد کالا و ستون Price شامل قیمت است. برای پیدا کردن قیمت هر کد می‌توانید بنویسید:

=MAP(A2:A10, LAMBDA(code, XLOOKUP(code, Products[Code], Products[Price], "یافت نشد")))

این فرمول برای هر کد کالا، قیمت متناظر آن را از جدول محصولات پیدا می‌کند و اگر کد پیدا نشود، عبارت «یافت نشد» را نمایش می‌دهد.

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

• ترکیب MAP با IF برای شرط‌گذاری روی هر مقدار

=MAP(A2:A10, LAMBDA(x, IF(x>100, "زیاد", "کم")))

• ترکیب MAP با SUM برای جمع چند مقدار در هر ردیف

=MAP(B2:B10, C2:C10, LAMBDA(a, b, SUM(a, b)))

• ترکیب MAP با AND برای بررسی چند شرط هم‌زمان

=MAP(A2:A10, B2:B10, LAMBDA(score, attendance, AND(score>=10, attendance>=70)))

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

=MAP(A2:A10, B2:B10, LAMBDA(status1, status2, OR(status1="تایید", status2="فوری")))

• ترکیب MAP با COUNTIF برای شمارش تکرار هر مقدار

=MAP(A2:A15, LAMBDA(item, COUNTIF(A2:A15, item)))

• ترکیب MAP با XLOOKUP برای جستجوی جداگانه هر مقدار

=MAP(A2:A10, LAMBDA(id, XLOOKUP(id, E2:E20, F2:F20, "ناموجود")))

• ترکیب MAP با TEXT برای قالب‌بندی خروجی‌ها

=MAP(A2:A10, LAMBDA(d, TEXT(d, "yyyy/mm/dd")))

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

=MAP(B2:B10, LAMBDA(amount, ROUND(amount*1.09, 0)))

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

۱) خطای #VALUE! به دلیل تعداد نادرست پارامترهای LAMBDA

یکی از رایج‌ترین خطاها زمانی رخ می‌دهد که تعداد آرایه‌های داده‌شده به MAP با تعداد پارامترهای LAMBDA برابر نباشد. مثلاً اگر دو محدوده به MAP بدهید، LAMBDA هم باید دو پارامتر داشته باشد.

فرمول نادرست:

=MAP(A2:A6, B2:B6, LAMBDA(x, x*2))

در این فرمول دو آرایه داریم، اما LAMBDA فقط یک پارامتر دارد. شکل صحیح:

=MAP(A2:A6, B2:B6, LAMBDA(x, y, x*y))

۲) خطای #CALC! به دلیل برگرداندن آرایه در داخل LAMBDA

تابع MAP برای هر عضو باید معمولاً یک مقدار تکی برگرداند. اگر داخل LAMBDA برای هر عضو، یک آرایه کامل تولید کنید، ممکن است با خطای #CALC! روبه‌رو شوید.

برای رفع این مشکل، مطمئن شوید خروجی LAMBDA برای هر مقدار، یک نتیجه واحد باشد؛ مثلاً عدد، متن یا TRUE/FALSE.

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

چون MAP یک خروجی داینامیک تولید می‌کند، نتیجه ممکن است در چند سلول پخش شود. اگر سلول‌های مقصد خالی نباشند، اکسل خطای #SPILL! نشان می‌دهد.

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

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

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

برای رفع این مشکل، از Microsoft 365، Excel for the web یا نسخه‌های جدیدتر اکسل که از توابع داینامیک و LAMBDA پشتیبانی می‌کنند استفاده کنید.

۵) خطای ناشی از اندازه متفاوت آرایه‌ها

اگر چند آرایه به MAP بدهید، باید از نظر تعداد ردیف و ستون با هم سازگار باشند. برای مثال ترکیب یک محدوده ۵ ردیفی با یک محدوده ۱۰ ردیفی معمولاً باعث خطا یا نتیجه نامناسب می‌شود.

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

۶) خطای منطقی به دلیل نام‌گذاری نامناسب پارامترها

گاهی فرمول خطا نمی‌دهد، اما نتیجه اشتباه است؛ چون پارامترهای داخل LAMBDA گیج‌کننده یا برعکس استفاده شده‌اند. بهتر است نام پارامترها را واضح انتخاب کنید؛ مثل price، discount، score یا status.

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

• برای خوانایی بهتر، نام پارامترهای LAMBDA را معنادار انتخاب کنید. مثلاً به جای x و y، از price و discount استفاده کنید.
• اگر فقط می‌خواهید روی هر مقدار یک عملیات ساده انجام دهید، MAP می‌تواند جایگزین کپی کردن فرمول در چندین سلول شود.
• اگر عملیات شما ردیف‌محور است و با کل ردیف کار می‌کند، گاهی تابع BYROW گزینه مناسب‌تری از MAP است.
• اگر عملیات شما ستون‌محور است، تابع BYCOL را هم بررسی کنید.
• برای جلوگیری از خطای #SPILL! قبل از نوشتن فرمول، محدوده خروجی را خالی نگه دارید.
• در ترکیب با XLOOKUP، برای حالت پیدا نشدن مقدار، حتماً آرگومان نتیجه جایگزین را مشخص کنید تا خروجی تمیزتر باشد.
• برای محاسبات مالی، MAP را با ROUND ترکیب کنید تا اعشارهای غیرضروری در خروجی دیده نشود.
• اگر فرمول خیلی طولانی شد، می‌توانید از LET برای تعریف متغیرها و خواناتر کردن فرمول استفاده کنید.
• MAP برای مدل‌سازی‌های مدرن اکسل بسیار مناسب است، چون خروجی داینامیک تولید می‌کند و با تغییر داده‌ها به‌روزرسانی می‌شود.

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

تفاوت MAP با کپی کردن فرمول در سلول‌ها

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

تفاوت MAP با BYROW

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

تفاوت MAP با BYCOL

تابع BYCOL روی هر ستون یک آرایه عملیات انجام می‌دهد. MAP روی هر مقدار یا مقادیر متناظر کار می‌کند. برای تحلیل‌های ستونی، BYCOL مناسب‌تر است؛ اما برای محاسبات سلول‌به‌سلول، MAP گزینه بهتری است.

تفاوت MAP با MAKEARRAY

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

تفاوت MAP با SCAN و REDUCE

تابع MAP برای تبدیل هر مقدار به یک مقدار جدید استفاده می‌شود. اما SCAN و REDUCE برای محاسبات تجمعی کاربرد دارند. SCAN نتایج مرحله‌به‌مرحله را برمی‌گرداند، در حالی که REDUCE معمولاً یک نتیجه نهایی تولید می‌کند.

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

تابع MAP جزو توابع جدید اکسل و مرتبط با آرایه‌های داینامیک و LAMBDA است. این تابع در نسخه‌های جدید Microsoft 365 و Excel for the web پشتیبانی می‌شود. همچنین در نسخه‌های جدیدتر اکسل که توابع LAMBDA Helper را دارند، قابل استفاده است.

• در Microsoft 365 معمولاً قابل استفاده است.
• در Excel for the web پشتیبانی می‌شود.
• در نسخه‌های قدیمی مثل Excel 2016 و Excel 2019 معمولاً پشتیبانی نمی‌شود.
• اگر اکسل شما تابع MAP را نشناسد، خطای #NAME? نمایش داده می‌شود.
• برای استفاده کامل از MAP بهتر است اکسل شما از Dynamic Arrays و LAMBDA پشتیبانی کند.

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

تابع MAP دقیقاً چه کاری انجام می‌دهد؟

تابع MAP یک عملیات مشخص را روی هر مقدار از یک یا چند آرایه اجرا می‌کند و خروجی را به صورت آرایه‌ای برمی‌گرداند.

آیا تابع MAP بدون LAMBDA کار می‌کند؟

خیر. در اکسل، تابع MAP برای تعریف عملیات خود به LAMBDA نیاز دارد. آخرین آرگومان MAP باید یک LAMBDA باشد.

آیا MAP فقط روی یک ستون کار می‌کند؟

خیر. MAP می‌تواند روی یک محدوده، چند ستون، چند ردیف یا چند آرایه هم‌اندازه کار کند.

آیا می‌توان MAP را با IF ترکیب کرد؟

بله. یکی از رایج‌ترین کاربردهای MAP ترکیب آن با IF برای شرط‌گذاری روی تک‌تک مقادیر است.

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

در بسیاری از موارد بله، اما نه همیشه. برای کارهای ساده، MAP می‌تواند فرمول‌های کپی‌شده را حذف کند. اما برای بعضی تحلیل‌های ردیفی یا ستونی، توابعی مثل BYROW و BYCOL مناسب‌تر هستند.

چرا خروجی MAP در چند سلول نمایش داده می‌شود؟

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

آیا MAP در اکسل فارسی هم کار می‌کند؟

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

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

تابع MAP یکی از قدرتمندترین توابع جدید اکسل برای پردازش آرایه‌هاست. با این تابع می‌توانید یک عملیات را روی تک‌تک مقادیر یک یا چند محدوده اجرا کنید، بدون اینکه لازم باشد فرمول را در چندین سلول کپی کنید. MAP به‌خصوص در ترکیب با LAMBDA، IF، SUM، AND، OR، COUNTIF و XLOOKUP بسیار کاربردی است.

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