توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی (+ دانلود فیلم آموزش گام به گام)

۸۱۹ بازدید
آخرین به‌روزرسانی: ۰۷ خرداد ۱۴۰۲
زمان مطالعه: ۴۲ دقیقه
توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی (+ دانلود فیلم آموزش گام به گام)

در نسخه اکسل ۲۰۱۹ که به تازگی توسط شرکت مایکروسافت منتشر شده است، توابع جدیدی اضافه و به امکانات محاسباتی اکسل افزوده شده است. در این مطالب آموزشی در مورد توابع جدید اکسل ۲۰۱۹ صحبت خواهیم کرد. در صورتی که احتیاج به آشنایی بیشتر با اکسل دارید به مطلب آموزش مقدماتی اکسل (Excel) — به زبان ساده مراجعه کنید. از طرفی برای آگاهی از شیوه نوشتن توابع و درج آن‌ها درون سلول‌های کاربرگ مطلب کاربرد توابع Cells و Range در اکسل – به زبان ساده را مطالعه کنید.

فیلم آموزشی توابع جدید اکسل 2019

دانلود ویدیو

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

  • تابع CONCAT: به منظور الحاق عبارت‌های متنی به یکدیگر استفاده شده و نسخه به روز شده تابع Concatenate است.
  • تابع IFS: این تابع را می‌توان جایگزینی برای حل ساده مسئله‌ای در نظر گرفت که برای حل آن احتیاج به مقایسه چندین حالت مختلف داریم و باید از روش ترکیب تابع IF به صورت «تو در تو» (Nested Function) کمک بگیریم.
  • MAXIFS: با استفاده از این تابع قادر به محاسبه حداکثر مقدار در یک ناحیه با توجه به یک یا چند شرط هستید. این تابع پارامترهایی مشابه تابع SUMIFS دارد.
  • MINIFS: از این تابع برای محاسبه حداقل مقدار در یک ناحیه با توجه به چندین شرط استفاده می‌شود.
  • SWITCH: زمانی که می‌خواهید براساس مقایسه مقادیر با لیستی که از پیش تعیین شده، مقداری را برای سلول انتخاب کنید، از تابع SWITCH استفاده کنید.
  • TEXTJOIN: این تابع برای الحاق عبارت‌های متنی به همراه علائم جداکننده به کار می‌رود. در حقیقت این تابع نسخه به روز شده تابع Concatenate محسوب می‌شود.

توابع جدید اکسل 2019

بعضی از این توابع به عنوان مکمل یا توسعه توابع قبلی هستند (مانند تابع CONCAT) و بعضی نیز به تازگی طراحی و توسعه داده شده‌اند (مانند تابع SWITHC). در ادامه به بررسی هر یک از این توابع پرداخته و به همراه مثال‌هایی کاربردهایشان را متذکر، خواهیم شد.

نکته: این تابع در نسخه ۲۰۱۹ اکسل برای سیستم‌های عامل ویندوز و مک طراحی شده است. کاربرانی که از مجموعه «آفیس 365» که نسخه «برخط» (Online) آفیس محسوب می‌شود استفاده می‌کنند باید از آخرین به روز رسانی‌ها را انجام داده باشند.

تابع CONCAT

این تابع از گروه توابع متنی در اکسل محسوب می‌شود. توسعه دهندگان نرم‌افزار اکسل سعی دارند این تابع را جایگزین تابع قبلی به نام CONCATENATE کنند که البته هنوز در اکسل ۲۰۱۹ قابل استفاده است ولی ممکن است در نسخه‌های بعدی ارائه نشود. تابع CONCAT، عبارت‌های متنی را به یکدیگر الحاق کرده و به صورت یک عبارت واحد در می‌آورد. این متن‌ها می‌توانند درون سلول‌ها بوده یا بطور مستقل در خود تابع مشخص شوند. شکل دستوری و پارامترهای این تابع به صورت زیر هستند.

1=CONCAT(text1, [text2],)

که در آن text2 , text1 , ... پارامترهایی هستند که مقدار یا آدرس سلول‌ها با مقدارهای متنی را شامل می‌شوند. برای مثال فرض کنید تابع زیر را در یک سلول وارد کرده‌ایم.

1=CONCAT("The"," ","sun"," ","will"," ","come"," ","up"," ","tomorrow.")

نتیجه یا حاصل تابع نیز به صورت زیر در خواهد آمد. به وجود فاصله بین هر کلمه که بوسیله یک علامت فاصله (" ") ایجاد شده است توجه کنید.

The sun will come up tomorrow.

نکته: اگر نتیجه الحاق متن‌ها بوسیله تابع CONCAT بیشتر از تعداد حروف مجاز در سلول (32767 حرف) باشد، این تابع، خطای $$#VALUE!$$ را نشان می‌دهد.

مثال ۱

جدول اطلاعاتی زیر را در نظر بگیرید. ستون‌های A و ‌B در جدول مشخص شده‌اند.

concat function in Excel 2019

نتیجه تابع $$=CONCAT(A:A, B:B)$$ برابر با $$a1a2a4a5a6a7b1b2b4b5b6b7$$ خواهد بود. همانطور که می‌بینید در این تابع امکان استفاده از یک ناحیه (Range Reference) وجود دارد. مشخص است که الحاق به صورتی است که ابتدا محتویات ستون A با یکدیگر ادغام شده سپس نتیجه با ترکیب محتویات ستون B، ادغام می‌شود.

مثال ۲

اطلاعات کاربرگ اکسل را به صورت تصویر زیر در نظر بگیرید. ستون Data  مربوط به ستون A کاربرگ است. همچنین مقدار Data نیز در ردیف اول کاربرگ قرار گرفته است.

concat function example

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

نتیجهشرحفرمول
Stream population for brook trout species is 32/mile.ساخت یک عبارت با الحاق داده‌های ستون A و متن‌های دیگر=CONCAT("Stream population for ", A2," ", A3, " is ", A4, "/mile.")
Andreas Hauserالحاق متن سلول B2 و C2 با ثبت یک فاصله بین آن دو=CONCAT(B2," ", C2)
Hauser, Andreasالحاق متن سلول B2 و C2 به ههمراه یک علامت کاما برای جداسازی- به فاصله‌ای که به همراه کام قرار داده شده توجه کنید.=CONCAT(C2, ", ", B2)
Fourth & Pineالحاق دو عبارت متنی به همراه علامت &=CONCAT(B3," & ", C3)
Fourth & Pineالحاق دو عبارت متنی بدون استفاده از تابع CONCAT و فقط با عملگر &=B3 & " & " & C3

تابع IFS

با استفاده از این تابع قادر هستید مقدار یک سلول را با توجه به تحقق چندین شرط تعیین کنید. این تابع در حقیقت جایگزین تابع شرطی (IF) «تو در تو» (Nested IF Function) است.

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

1=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3,...)

به این ترتیب ۱۲۷ شرط مختلف می‌تواند مورد بررسی قرار بگیرد تا مقدار یک سلول تعیین شود.

نکته: البته استفاده از شرط‌های متعدد در IFS یا IF تو در تو برای حل مسئله‌هایی که بیش از ۱۰ شرط دارند مناسب نیست زیرا کنترل و در نظر گرفتن اولویت شرط‌ها در نتیجه بسیار تاثیر گذار است و باید با دقت زیاد صورت گیرد.

جدول زیر پارامترهای تابع IFS را با شیوه و شکل تابع و پارامترهای IF بهتر نشان داده است.

شرحپارامتر
شرط یا گزاره شرطی که نتیجه درست (TRUE) یا غلط (FALSE) دارد. وجود این پارامتر برای محاسبه تابع IFS ضروری است.logical_test1
نتیجه تابع در صورتی که نتیجه عبارت logical_test1 درست باشد. چنانچه این پارامتر وارد نشود در سلول مقدار TRUE قرار خواهد گرفت.value_if_true1
شرایط و عبارت‌های شرطی دیگر که با توجه به ترتیب قرارگیری در نظر گرفته می‌شوند. این پارامترها اختیاری هستند.logical_test2....logical_test127
نتیجه متناسب با هر یک از عبارت‌های شرطی قبلی. اگر هر یک از شرط‌ها برابر با TRUE باشند، نتیجه متناسب با آن شرط درون سلول قرار می‌گیرد. این پارامتر اختیاری است.value_if_true2... value_if_true127

مثال ۳

در تصویر زیر با توجه به نمراتی که درون سلول‌های ستون A یا (Grade) قرار گرفته است، نمره فرد به صورت متنی نیز در ستون B یا (Letter) به کمک تابع IFS محاسبه شده است. مثلا فرمول مربوط به سلول B2 به صورت زیر نوشته شده است.

1=IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F")

در اینجا فرمول نوشته شده، بیان می‌کند که مقدار سلول B2 برای نمراتی که در سلول A2 بیشتر از ۸۹ هستند با A، نمراتی که بیشتر از ۷۹ هستند با حرف B، نمرات بیشتر از ۶۹ با حرف C و نمراتی که بیشتر از ۵۹ هستند نیز با حرف D مشخص شوند. اگر نمره فردی کمتر از ۵۹ باشد، مقدار سلول B2 برابر با F خواهد بود، زیرا در این مرحله نمره فرد در هیچ یک از شرط‌های قبلی تایید نشده و در تابع IFS، آخرین شرط که با مقدار TRUE مشخص شده است را مبنا قرار داده و مقدار متناظر با آن که F است را درون سلول B2 قرار می‌دهد.

IFS function in excel 2019

مثال ۴

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

ifs function example in excel 2019

به این ترتیب فرمولی که در سلول G2 قرار خواهد گرفت به صورت زیر است.

1=IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)

این فرمول بیان می‌کند که اگر مقدار سلول F2 برابر با ۱ باشد، مقدار D2‌ درون سلول G2 ثبت می‌شود. اگر مقدار سلول F2 برابر با 2 باشد، مقدار D3 درون سلول G2 قرار می‌گیرد و به همین ترتیب این شرط‌ها ادامه پیدا خواهد کرد. به توجه به اینکه هفت روز هفته داریم، هفت شرط نیز در فرمول به کار رفته است.

موارد زیر را هنگام استفاده از تابع IFS به خاطر داشته باشید.

  • اگر می‌خواهید مقداری را به عنوان پیش‌فرض برای تابع IFS در نظر بگیرید، به عنوان آخرین شرط مقدار "TRUE" را وارد کرده و سپس مقدار پیش‌فرض را بنویسید. به این ترتیب اگر هیچ یک از شرط‌های قبلی محقق نشود، مقداری که برای شرط آخر در نظر گرفته‌اید ثبت خواهد شد. معمولا در این حالت مقدار ثبت شده را «مقدار پیش‌فرض» (Default Value) می‌نامیم.
  • اگر پارامتر شرط (logical_test)، هیچ مقداری به عنوان مقدار مرتبط با شرط (value_if_true) وجود نداشته باشد، تابع IFS پیغام خطای "You've entered too few arguments for this function" را نشان می‌دهد که بیانگر عدم کامل بدون پارامترهای تابع است.
  • اگر پارامتر logical_test مقداری به غیر از TRUE یا FALSE داشته باشد، تابع IFS پیغام خطای $$#VALUE!$$ را نشان می‌دهد.
  • اگر هیچ یک از پارامترهای logical_test‌ مقدار TRUE نداشته باشند، تابع IFS‌ پیغام خطای $$#N/A$$ را درون سلول قرار خواهد داد.

تابع MAXIFS و MINIFS

اگر لازم باشد که مقدار حداکثر (Maximum) یا حداقل (Minimum)، در یک ناحیه از کاربرگ‌ها با توجه به شرط یا شرط‌هایی، محاسبه و نمایش داده شود، باید از تابع MAXIFS یا MINIFS استفاده کرد.

از آنجایی که شکل دستوری و پارامترهای این دو تابع مشابه یکدیگر است، توضیحات را در مورد تابع MAXIFS ارائه می‌دهیم. ولی می‌توان همه مثال‌ها و توضیحات را برای تابع MINIFS هم در نظر گرفت به شرطی که بدانیم با این تابع، مقدار حداقل محاسبه خواهد شد. شکل دستوری و پارامترهای این تابع به صورت زیر است.

1MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

همانطور که مشخص است پارامترهای اول و دوم و سوم به نظر ضروری می‌رسند (پارامترهای که داخل علامت [ ] قرار دارند ضروری نیستند). جدول زیر به معرفی این پارامترها پرداخته است. شرط‌هایی که در این تابع به صورت پارامتر نوشته می‌شوند با یکدیگر به صورت عطفی، ترکیب می‌شوند. یعنی محاسبه مقدار حداکثر یک ناحیه برای سطرهایی صورت می‌گیرد که در همه شرط‌ها صادق هستند.

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

شرحپارامتر
ناحیه‌ای از سلول‌ها که باید مقدار حداکثر از آنجا استخراج شود. این پارامتر ضروری است.max_range
ناحیه‌ای از سلول‌ها که باید شرط روی آن اعمال شود. این پارامتر نیز الزامی است.criteria_range1
شرطی که باید روی ناحیه criteria_range1 در نظر گرفته شود. این شرط می‌تواند یک مقدار عددی، عبارت، متن باشد. به هر حال مقداری در این قسمت وارد می‌شود با مقدارهای مربوط به ناحیه criteria_range1 مقایسه شده و یک نتیجه TRUE یا FALSE حاصل می‌شود. با توجه به مقدارهای TRUE در سلول‌های مرتبط مقدار حداکثر در ناحیه max_range، استخراج می‌شود.criteria1
نواحی شرط و شرط‌ها مربوطه دیگرcriteria_range2, criteria2, ...,

criteria_range127, criteria127

نکته: باید توجه داشت که ابعاد با اندازه ناحیه criteria_range با max_range مطابقت داشته باشد در غیر اینصورت اکسل درون سلول خطای $$#VALUE!$$ را نشان می‌دهد.

مثال 5

فرض کنید مقدار ستون اول و دوم جدول زیر به ترتیب در ستون A و B کاربرگ اکسل ثبت شده‌اند.

maxifs function in excel 2019

اگر در سلولی تابع زیر را وارد کنیم، نتیجه برابر با ۹۱ خواهد شد.

1=MAXIFS(A2:A7,B2:B7,1)

زیرا، در اینجا قرار است حداکثر مقدار در سلول‌های از A2 تا A7 محاسبه شود که در سلول‌های B2:B7 متناظر آن‌ها مقدار ۱ نوشته شده باشد. مشخص است که چنین مقدارهای برابر با ۸۸ و ۹۱ هستند.

از آنجایی که به دنبال بیشترین (حداکثر) مقدار در این میان هستیم، نتیجه تابع برابر با ۹۱ خواهد شد. همانطور که گفتیم، اگر نااحیه شرط در پارامتر criteria_range با max_range مطابقت نداشته باشد، اکسل پیغام خطا ظاهر می‌سازد. بنابراین تابعی که در زیر نوشته شده، پیغام خطای $$#VALUE!$$ را ظاهر خواهد ساخت، زیر در اینجا criteria_range دو ستونی است.

1=MAXIFS(A2:A5,B2:c6,"a")

مثال 6

در این مثال قرار است براساس رتبه a در ستون Grade، حداکثر مقدار وزن (Weight) را بدست آوریم. این کار را به کمک تابع جدید اکسل یعنی MAXIF اجرا می‌کنیم. به تصویر زیر توجه کنید.

maxifs function example

اگر در سلولی تابع زیر نوشته شود، حاصل برابر با ۱۰ است. به این ترتیب مشخص می‌شود که بیشترین وزن برای گروه مربوط به رتبه a برابر با ۱۰ است.

1=MAXIFS(A2:A5,B3:B6,"a")

نکته: هر چند در این مثال پارامتر criteria_range1 با ناحیه max_range مطابقت ندارد ولی دارای ابعاد یکسانی است. بنابراین به طور خودکار اکسل ناحیه criteria_range1 را مطابق با ناحیه max_range در نظر گرفته و محاسبات را با توجه به ناحیه B۳:B۶ بصورتی انجام می‌دهد که ارتباط بین سطرها، مطابق با تصویر زیر باشد.

maxifs function example1 -details

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

مثال ۷

قرار است براساس جدولی که در تصویر زیر دیده می‌شود، حداکثر میزان وزن (Weight) را با توجه به شرط‌های Grade برابر با ۲ و Level بزرگتر از ۱۰۰ محاسبه کنیم.

maxifs function example2

با توجه به تابعی که در زیر دیده می‌شود، مقدار برابر با ۵۰ خواهد بود.

1=MAXIFS(A2:A7,B2:B7,"b",D2:D7,">100")

همانطور که دیده شد، در این مثال مطابقت بین نواحی مربوط به همه پارامترها وجود دارد. اگر تابع را به صورت زیر بنویسم، به نظر می‌رسد که باید ناحیه شرط D2:D7 شامل مقدار سلول A8 باشد که در حال حاضر مقداری ندارد. در این حالت مقدار A8 صفر در نظر گرفته شده و در نتیجه جواب تابعی که در ادامه دیده می‌شود، برابر با ۱۲ خواهد بود.

1=MAXIFS(A2:A7,B2:B7,"b",D2:D7,A8)

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

1=MAXIFS(A2:A6,B2:B6,"a",D2:D6,">200")

تابع SWITCH

تابع switch به عنوان توابع جدید اکسل قادر است مقدار یک سلول را براساس لیستی از مطابقت‌ها زوجی ارائه دهد. این محاسبه بر مبنای اولین مطابقت با لیست صورت می‌گیرد.

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

1=SWITCH(Value to switch, Value to match1...[2-126], Value to return if there's a match1...[2-126], Value to return if there's no match)

مشخص است که می‌توان ۱۲۶ مقدار را در لیست مطابقت قرار داد. آخرین پارامتر نیز نشانگر مقداری است که در صورت عدم مطابقت باید در سلول ظاهر شود. به این ترتیب مشخص است که این پارامتر، مقدار پیش‌فرض (Default) را تعیین می‌کند. برای روشن شدن موضوع به مثال زیر توجه کنید. در تصویر زیر تاریخ مربوط به یک روز خاص در سلول A2 ثبت شده است. می‌خواهیم نشان دهیم که این تاریخ چه روزی از هفته است.

احتمالا اطلاع دارید که تابع $$WEEKDAY()$$ شماره روز هفته را از یک تاریخ استخراج می‌کند. ولی می‌خواهیم براساس اینکه شماره روز ۱، ۲ الی ۷ است تشخیص دهیم تاریخ مورد نظر مربوط به روز یکشنبه (Sunday)، دوشنبه (Monday) و  ... است و این مقدار را در سلول A5 قرار دهیم.

switch function in excel 2019

پارامتر اول که value to switch نام دارد در اینجا همان تابع weekday است که شماره روز مربوط به تاریخ سلول A2 را محاسبه می‌کند. اگر مقدار این تابع برابر با ۱ باشد، مشخص است که روز هفته، «یکشنبه» (Sunday) است. مطابقت با مقدار ۲ نشانگر «دوشنبه» (Monday) بودن تاریخ است و به همین ترتیب می‌توان از شماره روز مربوطه، اسم روز را استخراج کرد. همانطور که در انتها نیز دیده می‌شود، زمانی که هیچ مطابقتی صورت نگیرد مقدار No match در سلول ثبت خواهد شد.

مثال 8

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

نتیجهشرحفرمولمقدار سلول A2
mondayاز آنجایی مقدار A2=2 است، روز انتخابی دومین گزینه یعنی Monday است.=SWITCH(WEEKDAY(A2),1,"Sunday",2,

"Monday",3,"Tuesday","No match")

#N/A!چون هیچ مطابقتی در فرمول وجود ندارد و مقدار پیش‌فرض نیز مشخص نیست، تابع مقدار N/A را نشان می‌دهد.=SWITCH(A2,1,"Sunday",2,"Monday",3,"Tuesday")99
No matchبا توجه به تعریف عبارت No match به عنوان مقدار  پیش‌فرض No match نتیجه مطابق ستون بعدی حاصل خواهد شد.=SWITCH(A2,1,"Sunday",2,"Monday",3,"Tuesday","No match")99
weekdayمانند حالت بالا=SWITCH(A2,1,"Sunday",7,"Saturday","weekday")2
Tuesdayمانند حالت بالا=SWITCH(A2,1,"Sunday",2,"Monday",3,"Tuesday","No match")3

تابع TEXTJOIN

به کمک این توابع جدید اکسل قادر هستید عبارت‌هایی که در ناحیه‌ای متنی قرار گرفته‌اند، به یکدیگر الحاق کرده و حتی بین محتویات هر پارامتر نیز از «علامت‌های جداکننده» (Delimiter) استفاده کرد.

شکل دستوری و پارامترهای این تابع به صورت زیر است.

1TEXTJOIN(delimiter, ignore_empty, text1, [text2],)

این تابع قادر به ترکیب محتویات متنی ۲۵۴ ناحیه یا آدرس مختلف است. پارامتر اول در این تابع یعنی delimiter، علامت یا حرفی است که باید برای جداکردن متن‌ها به کار رود. اگر این پارامتر را به صورت "" وارد کنید، محتویات متنی پشت سر هم بدون هیچ فاصله‌ای در سلول مورد نظر قرار خواهند گرفت. ورود این پارامتر، اجباری است.

پارامتر دوم (ignore_empty) مشخص می‌کند که آیا سلول‌های خالی باید در هنگام ترکیب در نظر گرفته شوند یا خیر. در نتیجه به نظر می‌رسد مقدار پارامتر ignore_empty باید یکی از دو مقدار TRUE یا FALSE باشد. در صورتی که مقدار TRUE را وارد کنید، سلول‌های خالی هنگام الحاق نادیده گرفته می‌شوند ولی با توجه به مقدار FALSE برای این پارامتر، سلول‌های خالی در ترکیب محتویات به کار گرفته خواهند شد. فرض کنید براساس تصویر زیر تابع textjoin را به شکل زیر به کار برده‌ایم.

1=TEXTJOIN(", ", TRUE, A2:A7)

textjoin function in excel

مشخص است که این محتویات در سلول‌های A2 تا A7 ثبت شده‌اند. با توجه به تصویر، نتیجه تابع بالا و ترکیب این عبارت‌ها به صورت زیر خواهد بود. واضح است که هر عبارت با استفاده از علامت "," از عبارت‌های دیگر جدا شده است.

US Dollar, Australian Dollar, Chinese Yuan, Hong Kong Dollar, South Korean Won, Russian Ruble

مثال ۹

فرض کنید قرار است محتویات ستون A با ستون B به صورت سطری ترکیب شود. اطلاعات جدول مطابق تصویر زیر از سلول A1 تا A7 تنظیم شده است. توجه کنید که در ستون A و B‌ مقدار b3 , a3 وجود ندارند.

textjoin function example

نتیجه تابعی که در ادامه قابل مشاهده است، به صورت a1, b1, a2, b2, a4, b4, a5, b5, a6, b6, a7, b7 خواهد بود.

1=TEXTJOIN(", ", TRUE, A2:B8)

از آنجایی که پارامتر ignore_empry (پارامتر دوم) برابر با TRUE در نظر گرفته شده، از به کار بردن سلول‌های a3, b3 در نتیجه خودداری شده است.

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

اگر در تابع بالا در پارامتر دوم به جای TRUE از عبارت منطقی FALSE استفاده می‌کردیم، نتیجه به صورت زیر ایجاد می‌شد.

a1, b1, a2, b2, , , a4, b4, a5, b5, a6, b6, a7, b7

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

1=TEXTJOIN(", ", FALSE, A2:B8)

مثال ۱۰

تصویر زیر، یک جدول اطلاعاتی اکسل را نشان می‌دهد. فرض کنید ستون city مربوط به ستون A باشد. ستون‌های بعدی نیز مرتبط با آدرس ستون‌های B تا D هستند. اولین سطر جدول هم متناسب با سطر اول کاربرگ اکسل در نظر گرفته شده است.

textjoin function example detail

فرض کنید از تابع زیر برای الحاق متنی‌های این چهار ستون در ناحیه A2 تا D8 استفاده شده است.

1=TEXTJOIN(A8:D8, TRUE, A2:D7)

نتیجه به صورت زیر خواهد بود.

Tulsa,OK,74133,US;Seattle,WA,98109,US;Iselin,NJ,08830,US;Fort Lauderdale,FL,33309,US;Tempe,AZ,85285,US;end

اولا از آنجایی که پارامتر دوم برابر با TRUE است، سلول‌های خالی C7 , D7 در ترکیب متنی به کار نمی‌روند هر چند در ناحیه‌ای که توسط پارامتر سوم ذکر شده قرار دارند. از طرفی برای جداسازی عبارت‌های متنی از علامت‌هایی که در سطر هشتم ذکر شده استفاده شده است.

به آدرس A8:D8 در پارامتر اول توجه کنید. از آنجایی که چهار علامت ( ; , , , ) در این قسمت معرفی شده‌اند بین عبارت‌های اول و دوم و سوم و چهارم از علامت , و بین عبارت چهار و پنجم از علامت ; به منظور جداسازی استفاده می‌شود. همچنین در ادامه این ترکیب تکرار می‌شود. یعنی برای جداسازی عبارت پنجم و ششم از علامت , و به همین ترتیب از تا جداسازی عبارت هفتم و هشتم از علامت , استفاده می‌شود ولی بین دو عبارت هشتم و نهم علامت ; دیده می‌شود. این کار به همین ترتیب تا اتمام ترکیب عبارت‌ها، ادامه پیدا خواهد کرد.

نکته: اگر نتیجه الحاق متن‌ها بوسیله تابع TEXTJOIN بیشتر از تعداد حروف مجاز در سلول (32767 حرف) باشد، این تابع، خطای $$#VALUE!$$ را نشان می‌دهد.

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

اگر مطلب بالا برایتان مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

بر اساس رای ۳ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *