تقریباً همه کسانی که از اکسل استفاده میکنند، یک تابع خاص دارند که از زمان دانشگاه به آن متکی هستند چون بهانداز کافی کار میکند و اجازه میدهد از یادگیری چیز جدید اجتناب کنند. به همین دلیل هنوز میتوانید VLOOKUP و CONCATENATE را در فایلهای تازه، حتی در سال ۲۰۲۶، پیدا کنید. من هم این را میفهمم، زیرا هنوز در تعویض SUBTOTAL با AGGREGATE تردید دارم، چون این تابع مدتهاست که همراه من بوده است.
تقریباً همه کسانی که از اکسل استفاده میکنند یک تابع خاص دارند که از زمان دانشگاه به آن تکیه میکنند چون به اندازه کافی کار میکند تا نیازی به یادگیری چیز جدید نداشته باشند. به همین دلیل هنوز VLOOKUP و CONCATENATE را حتی در فایلهای جدید در سال 2026 میبینید. من درک میکنم، چرا که من هنوز در تصمیمگیری برای جایگزینی SUBTOTAL با AGGREGATE تردید دارم پس از این که مدت طولانی به من کمک کرده است.
با این حال، اکسل رشد کرده است و بسیاری از فرمولهایی که سالها پیش یاد گرفتیم اکنون سنگینتر، شکنندهتر یا سختتر برای نگهداری هستند نسبت به آنچه باید باشد. اگر فرمولهای شما همانند گذشته به نظر میآیند و برگههای شما سنگینتر از حد معمول احساس میشود، ممکن است نشانهای باشد که زمان ارتقاء فرا رسیده است.
XLOOKUP بهطور کامل VLOOKUP و HLOOKUP را جایگزین میکند
یک تابع که در هر جهت کار میکند
VLOOKUP و HLOOKUP در واقع بستهی اولیهی جستوجوهای اکسل هستند. آنها کار میکنند، آشنا هستند و در همهجا حضور دارند. مشکل این است که آنها به صورت سختگیرانهای عمل میکنند که با رشد برگههای شما بیشتر ناامیدکننده میشود. حتی کاری ساده مانند افزودن یک ستون در وسط جدول میتواند نتایج شما را خراب کند زیرا هر دو تابع به شمارههای ثابت ستون یا ردیف وابستهاند:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
علاوه بر این، VLOOKUP نمیتواند به سمت چپ ستون کلید شما نگاه کند، که شما را مجبور میکند دادهها را بازآرایی کنید یا ستونهای کمکی بسازید فقط برای اینکه یک جستوجوی پایه کار کند. XLOOKUP تمام این مشکلات را در یک مرحله حل میکند. به جای اشاره به جدول بزرگ و شمارش ستونها یا ردیفها، به اکسل دقیقاً میگویید کجا نگاه کند و چه چیزی بازگرداند:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
این رویکرد به این معناست که فرمولهای شما فقط به این دلیل که کسی یک ستون اضافه کند یا فیلدها را در جدول دوباره ترتیب دهد، خراب نخواهند شد. XLOOKUP همچنین از چپ به راست، راست به چپ و از بالا به پایین کار میکند که این باعث میشود در زمانی که دادههای شما به شکل منظم و جدول سنتی جستوجو قرار ندارند، بسیار انعطافپذیرتر باشد.
یک ارتقاء مهم دیگر این است که XLOOKUP شامل آرگومان if_not_found است، بنابراین دیگر نیازی به IFERROR برای جلوگیری از پیامهای ناخوشایند #N/A نیست. این دو فرمول تفاوت را نشان میدهند:
=IFERROR(VLOOKUP(B2, C2:E7, 4, TRUE)" ")
=XLOOKUP(B2, C2:E7, D2:D7, "Not found")
فرمول اول به دنبال B2 در ستون اول C2:E7 میگردد و ستون چهارم را برمیگرداند، در حالی که فرمول دوم به دنبال B2 در C2:E7 میگردد و مقدار مطابق از D2:D7 را برمیگرداند. XLOOKUP به طور پیشفرض بهجای تقریبی، یک مطابقت دقیق انجام میدهد، که خطر دریافت نتایج تقریبی از لیستهای بدون ترتیب را کاهش میدهد، و مقادیر گمشده را بدون نیاز به بستهبندی هر جستوجو در IFERROR مدیریت میکند.
XMATCH نسخه کارآمدتر MATCH است
کنترل دقیق بر انواع مطابقت و جهتهای جستجو
احتمالاً هنوز بهدلیل عادت از MATCH استفاده میکنید. این تابع همیشه در یافتن موقعیت یک مقدار در یک بازه عالی بوده است، بهویژه وقتی که pair MATCH with INDEX. مسئله این است که MATCH کمی آسان است که بهنادرستی استفاده شود. اگر فراموش کنید نوع مطابقت دقیق را مشخص کنید، اکسل ممکن است نزدیکترین نتیجه را بر اساس فرضهای ترتیببندی که در واقع به دادههای شما اعمال نمیشود، برگرداند:
=MATCH(lookup_value, lookup_array, [match_type])
از آنجا که این نوع اشتباه غالباً نتیجهای تولید میکند که معقول به نظر میرسد، تشخیص و رفع آن دشوار است. با XMATCH، فرمولهای شما بهصورت پیشفرض به مطابقت دقیق تنظیم میشوند، که در بیشتر موارد چیزی است که اکثر افراد میخواهند.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
اگر بهعنوان عادت از =MATCH(25, A1:A3, 0) استفاده میکنید، ارزش دارد به =XMATCH(25, A1:A3, 1) سوئیچ کنید و از این پیشرفت بهرهمند شوید. علاوه بر پیشفرضهای ایمنتر، XMATCH به شما امکان کنترل جهت جستجو را میدهد. میتواند از انتهای لیست به جای ابتدای آن جستجو کند، که مخصوصاً وقتی میخواهید آخرین ورودی یا آخرین وقوع یک مقدار را بدون افزودن ستونهای کمکی پیدا کنید، مفید است:
=XMATCH(E3, C3:C100, 0, -1)
MATCH هنوز قابل استفاده است، اما در این زمان، استفاده از آن مثل این است که گزینهای را انتخاب میکنید که فقط کار میکند در حالی که گزینهای کارآمدتر در کنار آن قرار دارد.
فرمولهای FILTER، UNIQUE و SUM پویا جایگزین ترفندهای SUMPRODUCT میشوند
آسانتر برای خواندن، اشکالزدایی و نگهداری در طول زمان
با SUMPRODUCT میتوانید محاسبات چندمعیاره، مجموع وزندار و منطق آرایهای را در یک فرمول مدیریت کنید. این انعطافپذیری باعث شد سالها بهعنوان یک راه حل محبوب شناخته شود. اما عیب این است که فرمولهای SUMPRODUCT اغلب شبیه کد میشوند که خواندن، اشکالزدایی و نگهداری آنها را دشوار میکند. اکنون که Excel has native support for dynamic arrays، بسیاری از مواردی که برایشان از SUMPRODUCT استفاده میکردیم، دیگر نیازی به چنین فرمولهای فشرده و رمزی ندارند.
بهدلیل آرایههای پویا در اکسل مدرن، توابع استانداردی مانند SUM اکنون میتوانند محاسبات آرایهای را بهصورت مستقیم انجام دهند. وقتی این دو را کنار یکدیگر میگذارید، رویکرد جدید معمولاً واضحتر و آسانتر برای استدلال است:
|
محاسبه |
SUMPRODUCT |
SUM |
|---|---|---|
|
جمع ساده |
=SUMPRODUCT($A$20:$A$50) |
=SUM($A$20:$A$50) |
|
جمع وزندار کلاسیک |
=SUMPRODUCT(A2A5, C2:C5) |
=SUM(A2:A5 * C2:C5) |
|
جمع چندمعیاره |
=SUMPRODUCT((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) |
=SUM((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) |
علاوه بر این، با توابع جدیدی مانند FILTER و UNIQUE، بهجای ساختن یک فرمول طولانی و سختخوان، میتوانید مسئله را به گامهای کوچک و با خوانایی بیشتر تقسیم کنید. شما دادههای موردنظر را فیلتر میکنید، در صورت نیاز مقادیر یکتا را استخراج میکنید و سپس نتیجه را خلاصه میسازید. منطق همانند قبل است، اما هدف فرمول برای هر کسی که بعداً باید آن را بخواند یا نگهداری کند، واضحتر میشود.
برای مثال، اگر فهرستی از فروشها در ستون B و نام محصولات در ستون A داشته باشید و بخواهید فروشهای محصول خاصی مانند «Apples» را جمع کنید، میتوانید یکی از این فرمولها را بنویسید:
=SUMPRODUCT((A2:A10="Apples")*(B2:B10))
=SUM(FILTER(B2:B10, A2:A10="Apples", 0))
در نسخه SUMPRODUCT، اکسل یک آرایه از مقادیر TRUE و FALSE برای بررسی «Apples» ایجاد میکند، آنها را به 1 و 0 تبدیل میکند و سپس با مقادیر فروش ضرب مینماید. هر چیزی که «Apple» نباشد به صفر تبدیل میشود، که از مجموع نهایی حذف میشود. در فرمول دوم، FILTER فقط مقادیر فروش را که محصول «Apples» است برمیگرداند و SUM این لیست کوچک و تمیز را جمع میکند. نتیجه همان است، اما روش دوم هدف فرمول را بهصورت یک نگاه واضحتر نشان میدهد.
دستوپا مرتبسازی برگههایکار را متوقف کنید وقتی FILTER داخلی اکسل بار سنگین را بر عهده میگیرد.
ممکن است هنوز SUMPRODUCT را بهدلیل ساختار array1, array2 که هنگام آزمایش آسان است، مناسب ببینید. با این حال، برای مجموعهدادههای بزرگ با صدها هزار یا میلیون ردیف، میتواند به طور قابل توجهی کندتر از گزینههای مدرن و spill‑friendly باشد. علاوه بر کارایی، توابع جدید فرمولهایی تمیزتر، آسانتر برای حسابرسی و کمتر ترسناک برای شخص بعدی که برگهتان را باز میکند، ارائه میدهند.
IFS و SWITCH حذف فرمولهای IF تو در تو را انجام میدهند
منطق تمیزتر بدون پرانتزهای بیپایان
از نظر فنی، عبارات IF تو در تو کار میکنند، اما به سرعت تبدیل به کابوسی برای خواندن یا نگهداری میشوند، بهویژه وقتی خودتان آنها را ننوشیدهاید. با IFS، بهجای تو در تو کردن IF داخل IF داخل IF، شرایط و نتایج را به ترتیب فهرست میکنید که باعث میشود فرمول شبیه منطق ساده باشد: اگر این، پس آن؛ اگر اینچیز دیگر، پس چیز دیگری. SWITCH یک قدم جلوتر میرود وقتی یک مقدار واحد را در مقابل چندین امکان ثابت مقایسه میکنید؛ خواناتر است و شامل گزینه پیشفرضی است تا نیازی به افزودن مدیریت خطای اضافی در انتها نداشته باشید.
=IF(test1, result1, IF(test2, result2, IF(test3, result3, ...)))
=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...)
=SWITCH(expression, value1, result1, value2, result2, ... [default])
بهعنوان مثال، اگر بخواهید نمرات را به امتیازها اختصاص دهید، میتوانید از عبارات IF تو در تو استفاده کنید، اما IFS همان منطق را بهصورت واضحتری بیان میکند:
=IF(A1>90, “A”, IF(A1>80, “B”, IF(A1>70, “C”, “F”)) )
=IFS(A1>90, “A”, A1>80, “B”, A1>70, “C”, TRUE, “F”)
TRUE در انتهای فرمول IFS بهعنوان شرط پیشفرض عمل میکند که به اکسل میگوید اگر هیچیک از شرایط قبلی برآورده نشد چه بازگرداند. در این مورد، هر نمرهای زیر ۷۰ به «F» تبدیل میشود بدون نیاز به تو در تو کردن یک IF دیگر. SWITCH الگوی کمی متفاوتی دارد، مانند نگاشت کدهای عددی به بخشها، و شامل آرگومان پیشفرض در انتهاست، بنابراین نیازی به کار اضافی TRUE نیست:
=SWITCH(A1, 101, “Sales”, 102, “Marketing”, “Other”)
اگر سلول A1 حاوی 101 باشد، اکسل «Sales» را برمیگرداند. اگر 102 باشد، «Marketing» را برمیگرداند. اگر مقدار دیگری باشد، «Other» را برمیگرداند. منطق حتی وقتی تعداد موارد رشد میکند، واضح باقی میماند.
TEXTJOIN و CONCAT جایگزین CONCATENATE میشوند
محدودهها را ترکیب کنید، نه فقط سلولهای منفرد
CONCATENATE در واقع یک قطعه موزه در این زمان است. هنوز در بسیاری از برگهها میبینید، اما بهصورت رسمی جایگزین شده و برای دلایل خوبی نیز اینگونه است. این تابع با محدودههای کامل کار نمیکند زیرا باید هر سلول را بهصورت دستی ارجاع دهید و مفهوم جداکنندهای ندارد.
اشتراک در خبرنامه برای ارتقاءهای عملی اکسل
CONCAT، جایگزین مدرن آن، بهطور مشابه کار میکند اما پشتیبانی از ارجاع به کاملمحدودیتها را اضافه میکند:
=CONCAT(text1, ... )
بهجای انتخاب دستی هر سلول، میتوانید فرمولی مانند این بنویسید و اجازه دهید اکسل بقیه کار را انجام دهد:
=CONCAT(B2:C8)
وقتی میخواهید جداکنندهای مانند کاما یا فاصله تعریف کنید و به اکسل بگویید سلولهای خالی را نادیده بگیرد، TEXTJOIN ابزار مناسبتر است:
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
=TEXTJOIN(", ", TRUE, B2:C8)
آرگومان اول جداکننده شماست که میتواند هر چیزی باشد که نیاز دارید، و آرگومان دوم به اکسل میگوید آیا سلولهای خالی را نادیده بگیرد یا نه. این ویژگی بهویژه هنگام ساختن لیستهای خوانا، تولید کوئریهای SQL، یا ترکیب متن قالببندی شده از یک ستون یا محدوده داده مفید است.
فرمولهای کوچک، صرفهجویی زمانی بزرگ.
وقت آن رسیده که از عادتهای قدیمیتان در اکسل جدا شوید
خبر خوب این است که هیچیک از این ارتقاءها نیازمند یادگیری مجدد اکسل از ابتدا نیست. در بیشتر موارد، توابع مدرن در واقع آسانتر از توابعی هستند که جایگزین میشوند؛ فقط کمی زمان میبرد تا به آنها عادت کنید.
اگر این هفته فقط یک عادت را بهروزرسانی کنید، با تابعی که زیاد استفاده میکنید شروع کنید و جایگزین مدرن آن را به کار بگیرید. سپس میتوانید بخشی کوچک از یک کتابکار را با این پنج تعویض بازنویسی کنید و بهدقت به سادگی فرمولهایتان توجه کنید.