آخرین به‌روزرسانی: 4 مهر 1400

در فصل گذشته به بررسی مدل سازی ساده با اکسل “تابع Match” پرداختیم و به طور مفصل این تابع اکسل را توضیح دادیم, در این فصل به بررسی توابع متنی و Flash Fill میپردازیم, در ادامه مقاله باما همراه باشید.

 

توابع متنی ‌و Flash Fill

 

سؤالاتی که در این فصل پاسخ داده می‌شوند:

• کاربرگی دارم که هر سلول آن حاوی توصیف یک محصول خاص، کد شناسایی محصول و قیمت آن محصول است. چطور می‌توانم تمام توصیف‌های محصولات را در ستون A ، تمام کدهای شناسایی را در ستون B و تمام قیمت‌ها را در ستون C قرار دهم؟
• هر روز داده‌هایی در ارتباط با فروش کل محصولات درایالات متحده دریافت می‌کنم که در سلولی به شکل جمع فروش منطقه‌ای شرق، شمال و جنوب محاسبه شده‌اند. چطور می‌توانم فروش شرق، شمال و جنوب را در سلول‌های جداگانه‌ای به دست آورم؟
• در انتهای هر دوره آموزشی مدرسه، دانش‌آموزانم عملکرد تدریس مرا با استفاده از اعداد 1 تا هفت ارزشیابی می‌کنند. می‌دانم که بسیاری از دانش‌آموزان رتبه های مختلفی به من داده‌اند. چطور می‌توانم به‌راحتی نموداری ستونی از رتبه‌های ارزشیابی آموزشم ایجاد نمایم؟
• تعدادی داده‌های عددی را از اینترنت یا یک پایگاه داده دانلود کرده‌ام. وقتی‌که سعی می‌کنم محاسباتی با این داده‌ها انجام دهم همیشه یک پیام خطای#VALUE دریافت می‌کنم. چگونه می‌توانم این مشکل را حل کنم؟
• از توابع متنی خوشم می‌آید اما آیا راه آسان‌تری (که در آن از توابع متنی استفاده نشود) وجود دارد تا اسم و فامیل را از داده‌ای استخراج نموده و فهرست ایمیلی از یک فهرست از نام‌ها ایجاد نمایم و یا سایر عملیات معمول دیگر را روی داده‌های متنی اعمال نمایم؟
• کاراکترهای یونیکد چه هستند؟
• تابع جدید TEXTJOIN چگونه تابع قدیمی CONCATENATE یا & را بهبود می بخشد؟
• من عاشق توابع متنی هستم اما چگونه باید از تابعTEXT‌ اکسل استفاده کنم؟

هنگامی‌که کسی برای شما داده‌ای می‌فرستد یا داده‌ای را از اینترنت دانلود می‌کنید، اغلب این داده‌ها به شکلی که شما می‌خواهید پیکربندی و مرتب نشده‌اند. مثلاً امکان دارد در داده‌های مربوط به خریدی که بارگذاری کرده‌اید، تاریخ و میزان خرید در یک سلول‌ درج شده باشند، اما شما نیاز دارید که آنها را در سلول‌های جداگانه‌ای مشاهده کنید. چگونه می‌توانید داده‌ها را چنان دست‌کاری کنید که به شکلی که می‌خواهید ظاهر شوند؟ جواب آن است که می‌بایست در استفاده از توابع نرم‌افزار مایکروسافت اکسل خبره شوید. در این فصل به شما نشان می‌دهم چگونه از توابع متنی زیر در اکسل استفاده کنید.

• LEFT
• RIGHT
• MID
• TRIM
• LEN
• FIND
• SEARCH
• REPT
• CONCATENATE
• REPLACE
• VALUE
• UPPER
• LOWER
• PROPER
• CHAR
• CLEAN
• SUBSTITUTE
• TEXTJOIN
• TEXT

 

در این فصل خواهید دید که چگونه از امکان جدید Flash Fill استفاده کنید تا به شکلی جادویی داده‌های خود را چنان دست‌کاری کنید که به همان شیوه‌ای که می‌خواهید ظاهر شوند. دست آخر مطالبی درباره مجموعه گسترده کاراکترهای یونیکد موجود در اکسل 2019 چیزهایی خواهید آموخت.

دستور زبان تابع TEXT

فایل textfunctions.xlsx نشان‌داده‌شده در تصویر 1-6  شامل نمونه‌هایی از توابع متنی است. بعداً در طول فصل خواهید دید چگونه این توابع را بر مسئله خاصی اعمال کنید، اما حالا بیائید کار را با مشاهده اینکه هریک از این توابع چه‌کار می‌کنند آغاز کنیم. پس از آن توابع را با هم ترکیب می‌کنیم تا تغییراتی به ‌مراتب پیچیده روی داده‌ها انجام دهیم.

تصویر 1-6  مثال‌هایی از توابع متنی

 

تابع LEFT

تابع LEFT(text,k)  تعداد k (k عدد است ) کاراکتر در رشته متنی را از ابتدا (سمت چپ) بر می گرداند. مثلا سلولC3  حاوی فرمول LEFT(A3.4) است. اکسل چهار حرف ابتدا یعنی مقدار Regg را برمی‌گرداند.

تابع RIGHT

تابع RIGHT(text,k) تعداد k (k عدد است ) کاراکتر در رشته متنی را انتها (سمت راست) بازمی‌گرداند. مثلاً در سلول C4 فرمول RIGHT(A3.4) عبارت ller  را برمی‌گرداند.

تابعMID

تابعMID(text.K.m) از کاراکتر k‌ ام (k عدد است ) در یک رشته شروع به کار می‌کند و مقدار  m (m عدد است) کاراکتر را از متن باز می گرداند. بعنوان مثال فرمول MID(A3.2.5)‌ در سلول C8 کاراکترهای 6-2  را از سلول A3  بر می گرداند و نتیجه آنeggie‌است.

تابع TRIM

تابع TRIM (text) تمام فاصله های (Spaces) یک‌رشته متنی را حذف می‌کند و فقط فاصله میان کلمات ( یک Space) را باقی می‌گذارد. مثلاً در سلولC5 فرمول TRIM(A3) سه فاصله ( سه Space) میان Reggie و Miller را حذف می‌کند و عبارت Reggie Millerرا  با یک فاصله ( یک Space)  به ما می‌دهد. تابعTRIM همچنین فاصله های (Space) آغاز و انتهای سلول را نیز حذف می‌کند.

تابع LEN

تابعLEN(text) تعداد کاراکترهای موجود در یک رشته متنی را (که شامل فاصله نیز می‌شود) را به ما می‌دهد. مثلا در سلولC6 فرمولLEN(A3) عدد 15 را به ما می‌دهد چرا که سلول A3 دارای 15 کاراکتر است. در سلول C7 فرمول LEN(C5) عدد 13 را به ما می‌دهد. چرا که در نتیجه فرمول TRIM دو تا از فاصله های خالی حذف شده‌اند، سلولC5 نسبت به متن اصلی در سلول A3  دو کاراکتر کمتر دارا می‌باشد.

توابع FIND‌ و SEARCH

تابع FIND(text_to_find,actyal-text,k) مکان اولین کاراکتر از عبارت قابل جستجو (text_to_find) را در موقعیتK یا پس از آن را در یک متن به ما باز می گرداند.

تابع FIND به بزرگ یا کوچک بودن حروف حساس است.

تابعSEARCH‌ هم دستور زبانی شبیه به تابع FIND‌دارد اما به بزرگ یا کوچک بودن حروف حساس نیست. مثلا فرمول FIND(“r”,A3,1) در سلول C10 مقدار، 15 یعنی جایگاه حرفr (که با حروف کوچک نوشته شده) در رشته متنی Reggie Miller را به ما می‌دهد. (حرف R بزرگ نادیده گرفته شده چرا که تابع FIND‌ نسبت به بزرگی و کوچکی حروف حساس است.) با واردکردن فرمول SEARCH(“r”,A3,1)  در سلول C11 می‌بینیم که عدد یک به ما داده می‌شود مورد مطابق با جستجوی ما یعنی حرف r هم می تواند با حروف بزرگ نوشته شده باشد و هم با حروف کوچک . واردکردن فرمولFIND(“ “,A3,1) در سلول C9  عدد 7 را به ما می‌دهد چرا که اولین فاصله خالی در رشته متنی Reggie Miller هفتمین کاراکتر محسوب می‌شود.

تابع REPT

تابعREPT به شما اجازه می‌دهد تا رشته متنی را به دفعات مشخصی تکرار نمایید. دستور زبان این تابع: REPT(text,number-of-times) می‌باشد. مثلا REPT(“I”,3) خروجی زیر را ایجاد می نماید: III.

توابع CONCATENATE و &

از تابع CONCATENATE(text,text2,. . .,text30)می توان برای تلفیق بیش از 30 رشته متنی به یک رشته متنی استفاده نمود. از عملگر& می توان به‌جای CONCATENATE استفاده کرد. مثلاً با واردکردن فرمول A1&” “ &B1 عبارت Reggie Miller را به ما می‌دهد. واردکردن فرمول CONCATENATE(A1,” “,B1) در سلول D12 همان نتیجه را به ما می‌دهد.

تابع TEXTJOIN

تابع TEXTJOIN متنی را از چندین محدوده (Ranges) و یا چندین رشته متنی باهم ترکیب می‌کند و شامل حائلی (Delimiter) است که شما بین هر مقدار متنی که قرار است ترکیب شود قرار می‌دهید. اگر حائل شما رشته متنی خالی باشد، این تابع با موفقیت تمامی محدوده‌ها را به هم الحاق می‌کند. تابع TEXTJOIN تنها درصورتی‌که از نرم‌افزار  Office365‌استفاده کنید در دسترس شما خواهد بود. دستور زبان تابعTEXTJOIN‌عبارت است از:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …).

در اینجا به حائل نیازمندیم و از آن برای جداکردن متون انتخابی استفاده می‌کنیم. در این فرمول به آرگومان Ignore empty نیز نیازمندیم و اگر مقدار آن صحیح باشد سلول‌های خالی نادیده گرفته خواهند شد تابع TEXTJOINهنگامی‌که بخواهید متونی را به هم الحاق کنید که با یک حائل از هم جدا شده باشند بسیار کاربردی است. با استفاده از تابعTEXTJOIN شما دیگر نیازی به اضافه کردن کاما بعد از هر سلول استفاده شده در توابعCONCATENATE  و&‌ نخواهید داشت. در ادامه این فصل مثال‌هایی جامع در باره تابعTEXTJOIN  در اختیار شما قرار خواهیم داد.

تابع TEXT

تابعTEXT اجازه می‌دهد با استفاده از شناسه های تغییر شکل ( Format codes  ) شیوه ظاهر شدن اعداد یا تاریخ را تغییر دهید. دستور زبان تابعTEXT‌عبارت است از:

TEXT(Value you want to format,”Format Code you want to apply”)

در ادامه این فصل مثال‌هایی درباره نحوه استفاده از تابع Text‌و پیدا کردن شناسه های تغییر شکل صحیح ارائه خواهیم کرد.

تابع REPLACE

تابع REPLACE(old-text,k,m,new_text) با کاراکتر Kام از old text کار خود را شروع کرده و m کاراکتر بعدی را با new text‌ جایگزین می نماید. مثلا در سلول C13، فرمول:

REPLACE(A3,3,2,”nn”) سومین و چهارمین کاراکترها (gg) را در سلولA3  را با nn جایگزین می نماید. حاصل این فرمول Rennie Miller‌است.

تابع VALUE

تابعVALUE(text) رشته متنی که نمایانگر عددی است را به عدد تبدیل می‌کند. مثلا اگر در سلول B15  فرمول VALUE(A15)‌را وارد کنیم، این فرمول رشته متنی 31 در سلول A15  را به عدد 31 تبدیل می‌کند. شما می‌توانید عدد 31 در سلول A15 بعوان متن شناسایی کنید چرا که به صورت چپ چین نوشته شده و به همان شکل می‌توانید ارزش 31 در سلول B15  را بعنوان عدد در نظر بگیرید چرا که به شکل راست چین نوشته شده است.

توابع UPPER,LOWER و PROPER

تابع UPPEER(text) تمامی متن را به حروف بزرگ تغییر می‌دهد، مثلاً در سلول C16 فرمول

LOWER(C12) تمام حروف بزرگ را به کوچک تبدیل کرده و عبارتreggie miller را به ما می‌دهد. در سلول C17 فرمول UPPER(C16) تمامی حروف را به حروف بزرگ مبدل نوده و REGGIE MILLER‌ را به ما می‌دهد. بالاخره در سلول C18 فرمول PRPOER(C17) شکل صحیح نوشتاری (از لحاظ بزرگ یا کوچک نویسی حروف) را باز گردانده و Reggie Miller  را به ما می‌دهد.

تابع CHAR

تابعCHAR(Number) (برای عددهایی مابین 1 الی 255) کاراکتر ASCII مرتبط با آن عدد را به ما نشان می‌دهد. مثلا CHAR(65) کاراکتر A را به ما می‌دهد، CHAR(66)‌ کاراکتر B

را به ما نشان می‌دهد. بخشی از فهرست این کاراکترها در تصویر 2-6 نشان‌داده‌شده است.

 

تصویر 2-6 بخشی از فهرست کاراکترهای ASCII

تابعCLEAN

اگر به فایلی بنام ASCIIcharacters.xlsx نگاهی بیندازید، متوجه کاراکترهای خاصی همانند کاراکتر شماره 10(که نمایشگر یک خط جدید یا line feed است) می‌شوید که نامرئی هستند. استفاده کردن از تابعCLEAN در یک سلول باعث حذف شدن برخی (و نه همه) کاراکترهای ASCII‌ نامرئی (یا چاپ نشدنی) می‌شود. تابع CLEANاما مثلا CAHAR(160) را  که کاراکتر فاصله نشکن (nonbreaking space) است را حذف نمی‌کند. بعد ها در این فصل تمرینهایی را برای حذف کاراکترهای دردسر ساز مثلCHAR(160) ‌ از یک سلول انجام خواهیم داد.

تابع SUBSTITUTE

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

SUBSTITUTE(cell,old_text,new_text,instance_number). آخرین آرگومان اختیاری است. چنانچه حذف شود، هرجایی که old_text در متن ظاهرشده باشد، با new_text  جایگزین می‌گردد. اگر آخرین آرگومان اضافه شده باشد (مثلاً باارزش n) تنها n امین نمونه از old_text با new_text جایگزین می‌شود. برای نمایش استفاده از تابع SUBSTITUTE فرض کنید که می‌خواهید فاصله‌های درون سلولD17 را با ستاره هایی جایگزین نمایید. ابتدا ما در سلول C19 فرمول SUBSTITUTE(D17,” “,”*”) را درج می کنیم. این فرمول، هر فاصله خالی را با * پر کرده و عبارتI*LOVE*EXCEL*2019!  را به ما می‌دهد.

واردکردن فرمولSUBSTITUDE(D17,” “,”*”,3) تنها فاصله سوم را با کاراکتر * پر کرده و عبارت I LOVE EXCEL*2019!  را به ما می‌دهد.

پاسخ به سؤالات فصل:

شما قدرت توابع متنی را با استفاده از آنها برای حل مسائل واقعی که توسط دانشجویان سابق من فرستاده شده ،که برای شرکت های فهرست فورچون 500 کار می‌کنند، مشاهده می‌کنید. اغلب اوقات کلید حل مسائل ترکیب چند تابع متنی در یک فرمول است.

کاربرگی دارم که هر سلول آن حاوی توصیف یک محصول خاص، کد شناسایی محصول و قیمت آن محصول است. چطور می‌توانم تمام توصیف‌های محصولات را در ستون A ، تمام کدهای شناسایی را در ستون B و تمام قیمت‌ها را در ستون C قرار دهم؟

در این مثال، کد شناسایی محصول همیشه با اولین دوازده کاراکتر مشخص می‌شود و قیمت نیز همیشه در هشت کاراکتر آخر (با دو فاصله در آخر هر قیمت) مشخص شده است. راه‌حل این مسئله در فایلی بنام Lenora.xlsx ارائه و ( در تصویر 4-6 ) نشان‌داده‌شده ، که در آن از توابعLEFT، RIGHT، MID، VALUE، TRIM، LEN‌ وCONCATENATE استفاده شده است.

کار را با ازبین‌بردن فواصل اضافی شروع کردن،  همیشه ایده خوبی است، می‌توانید این کار را با کپی‌کردن فرمول TRIM(A4) از سلول B4 به محدوده B5:B12 انجام دهید. معلوم می‌شود تنها فاصله اضافی در ستون A دو فاصله قرارداده شده بعد از هر قیمت است. برای دیدن این مورد، نشانگر ماوس را در سلولB4 قرار داده و کلید F2 را فشار دهید تا سلول را ویرایش نمایید. اگر به انتهای سلول بروید می‌بینید که در آنجا دو فاصله خالی موجود است. نتیجه استفاده از تابعTRIM در ستون B از تصویر 3-6 نمایش‌داده‌شده است. برای اینکه اثبات نمایید که تابع TRIM دو فاصله اضافی انتهای سلول A4 را حذف کرده است می‌توانید از فرمول‌های: =LEN(A4) و=LEN(B4) استفاده کنید تا نشان دهید که سلول A4 حاوی 52 کاراکتر و سلولB4 حاوی 50 کاراکتر است.

تصویر 3-6 استفاده از تابعTRIM برای حذف فواصل اضافی

برای به‌دست‌آوردن کد محصول می‌بایست 12 کاراکتر چپ نوشته شده را از ستون B استخراج نمایید. جهت انجام این کار از سلول C4 فرمول LEFT(B4,12)‌را درمحدوده C5:C12 کپی کنید. این فرمول 12 کاراکتر سمت چپ را از متن سلول B4  و سلول‌های بعد از آن جدا کرده و کد محصولات را به شما ارائه می‌کند. می‌توانید این فرآیند را در تصویر6-4 ببینید.

تصویر 4-6، استفاده از توابع متنی برای استخراج کد محصولات، قیمت و توصیف محصولات از یک رشته متنی.

 برای استخراج قیمت محصولات، می‌دانیم که قیمت شش رقم آخر هر سلول را اشغال کرده است بنابراین می‌بایست شش کاراکتر سمت راست را از هر سلول جدا کنیم. من فرمول VALUE(RIGH(B4,6)) را از سلول D4 به محدوده D5:D12 کپی کردم. از تابعVALUE استفاده کردم تا متن استخراج شده را به عدد تبدیل کنم. اگر شما متن را به عدد تبدیل نکنید قادر نخواهید بود عملیات ریاضی روی قیمت‌ها انجام دهید.

استخراج توصیف مربوط به محصولات کمی پیچیده‌تر است. اگر داده‌ها را بررسی کنید می‌بینید که اگر استخراج را از کاراکتر سیزدهم شروع کرده و تا وقتی‌که شش کاراکتر از انتهای سلول باقی‌مانده باشد ادامه دهیم، می‌توانیم به داده‌های مورد نظر خود برسیم. کپی‌کردن فرمولMID(B4,13,LEN(B4)_6_12)‌ از سلول E4  به محدوده E5:E12 این کار را برای ما انجام می‌دهد. تابعLEN(B4)  تعدادکل کاراکترهای متن دست‌کاری شده را به ما می‌دهد. این فرمول (MID به معنی میان) با کاراکتر سیزدهم کار را شروع کرده و بعد شماره کاراکترهای مطابق با کل شماره های کمتر از دوازده را از آغاز (کد محصولات) و شش کاراکتر انتهایی (قیمت) استخراج می‌کند.با این تفکیک تنها توصیف محصولات است که باقی می ماند.

حالا فرض کنید به شما داده‌هایی با کد محصولات در ستون C، قیمت محصولات در ستونD و توصیف آنها در ستون E‌داده شده است. آیا می‌توانید این داده‌ها را باهم ترکیب کنید تا متن اصلی را دوباره به همان شکل اولیه بازگردانید؟

متن را می توان به‌راحتی با استفاده از تابعCONCATENATE ترکیب نمود. کپی‌کردن فرمول    CONCATENATE(C4,E4,D4) متن اصلی تفکیک شده را به شکل اول بازمی‌گرداند که می‌توانید آن را در تصویر 4-6 تماشا کنید.

فرمول الحاق با کد محصول درC4 آغاز می‌شود، بعد شما توصیف محصول را از سلولE4 به آن اضافه می‌کنید و بالاخره قیمت را از سلولD4 به آن می افزائید.حالا شما تمامی متن توصیف کننده هر کامپیوتر را به حال اول باز گردانده اید. عملیات الحاق را می توان با استفاده از نشان & نیز انجام داد. شما می‌توانید با فرمول C4&D4&E4 کدمحصول، توصیف محصولات و قیمت آنها را به شکل اولیه در سلولی نمایش دهید. توجه کنید که سلولE4 قبل و بعد از توصیف محصول دارای فاصله است. اگر سلول E4 دارای این فاصله نباشد شما می توایند از فرمول

C4&” “&E4&” “&D4 استفاده کنید تا فاصله خالی مورد نظر را درج نمایید. توجه کنید که فاصله بین هر جفت از گیومه‌ها باعث جای‌گذاری یک فاصله می‌شود.

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

اگر قیمت همواره حاوی شش کاراکتر نباشد، استخراج قیمت ممکن است کمی پیچیده شود. مسئله 15 را به‌عنوان مثالی در باره اینکه چگونه می توان آخرین کلمه در رشته‌ای متنی را استخراج نمود بررسی کنید.

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

این مسئله توسط یکی از کارمندان بخش مالی شرکت مایکروسافت برایم فرستاده شد. او هر روز کاربرگی حاوی فرمول‌هایی از قبیل=50+200+400، =5+124+1-25 و غیره دریافت می‌کرد و نیاز داشت هر عدد را در سلول ستون مخصوص خود استخراج نماید. مثلاً می‌خواست اولین عدد (فروش منطقه شرق) را در هر سلول از ستون C، دومین عدد (فروش منطقه شمال) را در ستون D و سومین عدد (فروش منطقه جنوب) را در ستونE  قرار دهد.چیزی که این مسئله را چالش برانگیز می‌کند آن است که ما دقیقا مکان کاراکتری را که دومین و سومین عدد در هر سلول با آن آغاز می شوند را نمی دانیم. در سلولA3 فروش شمال با کاراکتر چهارم آغاز می‌شود. در سلولA4  فروش شمال با کاراکتر سوم آغاز می‌شود.

داده‌های این مثال در فایلی بنام Salesstripping.xlsx موجود و در تصویر 5-6 نشان‌ داده ‌شده است. شما می‌توانید موقعیت فروش مناطق مختلف را با موارد زیر شناسایی نمایید:

• فروش‌های منطقه شرق در سمت چپ اولین علامت (+) نمایش داده شده‌اند.
• فروش‌های منطقه شمال در بین اولین و دومین علامت (+) مشخص شده‌اند.
• فروش‌های جنوب در سمت راست دومین علامت (+) مشخص شده‌اند.

با ترکیب توابعFIND، LEFT،LEN و MID شما می‌توانید به آسانی این مسئله را به روال زیر حل کنید.

• از فرمان‌های Edit، Replace استفاده کنید تا هر علامت مساوی را با یک فاصله جایگزین کنید. برای حذف کردن علامت مساوی محدوده A3:A6 را انتخاب کنید. بعد در سربرگHome گزینه Editing group را انتخاب کرده بر روی گزینه Find & select کلیک کرده و پس از آن روی Replace  کلیک کنید. سپس در بخش جستجو (Find What field) علامت مساوی را وارد کرده و بعد در بخش جایگزینی (Replace With field) یک فاصله خالی قرار دهید. بعد روی عبارت Replace all یا جایگزینی همه کلیک کنید. این کار تمامی فرمول های درون متن را با تغییر علامت مساوی به فاصله خالی (Space)  تغییر می‌دهد.
• از تابعFIND برای پیدا کردن دو علامت + در هریک از سلول‌ها استفاده کنید.

تصویر 5-6، جداکردن فروش شرق، شمال و جنوب با ترکیب توابعFINDT، LEFT، LEN و MID

برای یافتن فروش منطقه شرق می‌توانید از تابعLEFT برای استخراج تمامی کاراکترهای سمت چپ اولین علامت +  استفاده کنید، برای این کار فرمول LEFT(A3,B3-1) را از سلول D3 به سلول‌های محدودهD4:D6 کپی کنید. برای استخراج فروش منطقه شمال از تابعMID برای تفکیک همه کاراکترهای بین دو علامت + به اضافه استفاده نمایید. با یک کاراکتر بعد از اولین علامت +  شروع کرده و عدد کاراکترکه برابر با فرمول: مکان دومین + منهای مکان اولین + منهای یک است را استخراج نمایید. اگر عدد1- را حذف کنید آن ‌وقت دومین علامت به اضافه را بدست خواهید آورد ( این کار را آزمایش کنید) بنابراین برای بدست آوردن فروشهای منطقه شمال فرمول MID(A3,B3+1,C3-B3-1 را از سلول E3  به محدوده E4:E6 کپی نمایید. به بخش تابعTEXT در ابتدای همین فصل مراجعه کنید تا هریک از آرگومانهایی که در تابع استفاده شده است را مرور نمائید.

برای استخراج فروش‌های منطقه جنوب، از تابعRIGHT تمام کاراکترهای سمت راست دومین علامت + استفاده می کنیم. فروش منطقه جنوب تعداد کاراکترهایی برابر با فرمول: تعداد همه کاراکترها در سلول منهای مکان دومین علامت +  را دارا می‌باشد. تعداد کل کاراکترهای هر سلول را با کپی کردن فرمول LEN(A3) از سلول F3 در محدوده F4:F6‌ محاسبه کنید. بالاخره شما می‌توانید فروش‌های منطقه جنوب را با کپی فرمولRIGHT(A3,F3,C3) از سلولG3 به محدوده G4:G6 بدست آورید.

استخراج داده‌ها با استفاده از ابزار تبدیل متن به ستون (Convert Text to Columns Wizard)

برای استخراج فروش‌های مربوط به شرق، شمال و جنوب (و داده‌هایی شبیه به این مثال) راهی ساده‌تر بدون نیاز به توابع متنی نیز وجود دارد. برای انجام این کار خیلی ساده سلول‌های A3:A6 را انتخاب کنید. بعد در روی سربرگ Data روی نوار ریبون، در قسمت Data Tools group روی گزینه Text To Columns(تبدیل متن به ستون) کلیک کنید. گزینه Delimited   (محدود شده یا حائل دار) را انتخاب کرده و دکمهNext  را کلیک کنید و سپس کادر محاوره ای را همان‌طور که در تصویر 6-6 نشان‌داده‌شده پر کنید.

 

تصویر 6-6 استفاده از ابزار تبدیل متن به ستون

واردکردن علامت + در محدوده حائل، اکسل را به جداکردن هریک از سلول‌ها در ستون‌هایی وامی‌دارد و این تفکیک با رسیدن به کاراکتر + بعدی متوقف می‌شود. توجه کنید که گزینه‌های موجود برای جداکردن داده‌ها Tabs ، سمی‌کالن، کاما و یا فاصله خالی (space) هستند. حالا روی دکمه Next‌ کلیک کنید، گوشه بالای سمت چپ از محدوده مورد نظرتان را انتخاب کنید( مثلا من سلولA8 را انتخاب کردم و بعد روی دکمه Finish‌ کلیک نمایید. نتیجه در تصویر 7-6  نشان‌داده‌شده است.

تصویر 7-6  نتیجه استفاده از ابزار تبدیل متن به ستون

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

فایلی بنام Repeatedhisto.xlsx حاوی ارقام ارزشیابی تدریس معلم (از رقم یک تا هفت) می‌باشد. دو نفر به من نمره 1 داده‌اند، سه نفر به من نمره 2 داده‌اند و غیره. با استفاده از تابع REPT می‌توانید به‌آسانی نموداری خلق کنید تا این داده‌ها را خلاصه نماید. خیلی ساده فرمول =REPT(“|”,C4) را از سلول D4 به محدوده D5:D10 کپی کنید. این فرمول در ستون D  به‌اندازه مقادیر ستونC کاراکتر | قرار می دهد. تصویر 8-6 برتری امتیاز های خیلی خوب (ارقام شش و هفت) را و به همان نسبت تعداد کم امتیازهای ضعیف( ارقام یک و دو) را نشان می دهد. تکرار یک کاراکتر مثل همان کاراکتر پایپ  (|) شما را قادر می سازد تا به آسانی یک نمودار ستونی یا هیستوگرام را شبیه سازی نمایید.در فصل 43 : خلاصه کردن داده‌ها با استفاده از نمودارهای هیستوگرام و پارتو را برای بحث های مفصل تری درمورد ایجاد نمودارهای هیستوگرام با اکسل مشاهده خواهید کرد.

تصویر 8-6 استفاده از تابع REPT برای ایجاد نمودارهای نمایشگر فراوانی

 

تعدادی داده‌های عددی را از اینترنت یا یک پایگاه داده دانلود کرده‌ام. وقتی‌که سعی می‌کنم محاسباتی با این داده‌ها انجام دهم همیشه یک پیام خطای#VALUE دریافت می‌کنم. چگونه می‌توانم این مشکل را حل کنم؟

در فایلی بنام Cleanexample.xlsx (تصویر 9-6  را ببینید) آرگومان های نامرئی char(10) و Char(160)  را در جلوی عدد 33 در سلول‌های E5 وH6  به ترتیب باهم الحاق کرده‌ام. ما در سلول‌های E8 و H8 تابع VALUE را برای انجام تلاشی جهت تغییر محتوی سلول‌های E5 و E8 به اعداد اعمال کرده‌ایم، اما نتیجه پیام خطای#VALUE می‌باشد که نشان می‌دهد اکسل نمی‌تواند بفهمد که ما می‌خواهیم با این سلول‌ها به‌عنوان اعداد کار کنیم. در سلول E11 ما با استفاده از فرمول CLEAN(E5) محتوی سلول را پاک کرده‌ایم. در سلول E12 ارزش عددی 33 را می‌بینیم بنابراین استفاده از فرمولVALUE(E11) پس از پاک‌کردن فرمولCHAR(10) موفقیت‌آمیز به نظر می‌رسد. هرچند در سلول H11 فرمول VALUE(H10) با شکست روبرو می‌شود چرا که تابع CLEAN آرگومان CHAR(160) را پاک نمی‌کند. هرچند ما می‌توانیم از تابع SUBSTITUTE برای جایگزینی CHAR(160) با فاصله خالی استفاده کنیم و در آن صورت همه چیز روبه‌راه می‌شود. در سلول H14  از فرمول FIND(CHAR(160),H10) استفاده می‌کنیم تا مطمئن شویم که CHAR(160) در سلول H10 وجود دارد. ما درمی‌یابیم که اولین کاراکتر موجود در سلول H10 ، CHAR(160) بوده است؛ اگر تابع FIND به ما پیغام خطا بدهد، آن‌وقت خواهیم فهمید کهCHAR(160) در سلول H10 وجود نداشته است. در سلولH15  فرمول SUBSTITUTE (H6,CHAR(160),””) تمام جاهایی را که آرگومان CHAR(160)  وجود دارد را با فاصله خالی جایگزین می‌کند. در سلول H16 فرمول VALUE(H15) عدد 33 را به ما می‌دهد بنابراین اکنون می‌توانیم عملیات ریاضی را روی محتویات سلول H15 انجام دهیم.

تصویر 9-6  استفاده از توابع CLEAN‌ و SUBSTITUTE برای از بین بردن کاراکترهای چاپ نشدنی

 

از توابع متنی خوشم می‌آید اما آیا راه آسان‌تری (که در آن از توابع متنی استفاده نشود) وجود دارد تا اسم و فامیل را از داده‌ای استخراج نموده و فهرست ایمیلی از یک فهرست از نام‌ها ایجاد نمایم و یا سایر عملیات معمول دیگر را روی داده‌های متنی اعمال نمایم؟

 

ابزار Flash Fill (اولین‌بار در اکسل 2013 ارائه شد) از فناوری پیشرفته شناسایی الگوها

Pattern –Recognition)) برای شبیه‌سازی بسیاری از وظایفی که قبلاً توسط توابع انجام می‌شد استفاده می‌کند. فایلی بنام Flashfill.xlsx حاوی مثال‌های زیر است که استفاده از Flash Fill در مسائل مختلف را نشان می‌دهد.

• استخراج نام کوچک و نام خانوادگی (در اولین و آخرین کاربرگ)
• ایجاد آدرس‌های پست الکترونیک برای دانشگاه یواکس وای زد با اضافه‌کردن @UXYZ.edu به آخر نام خانوادگی شخص (در کاربرگ پست الکترونیک)
• استخراج مقادیر دلار و سنت از لیست قیمت‌ها (در کاربرگ دلار و سنت‌ها)

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

معمولاً اگر کلید Enter و سپس کلیدهای Ctrl+E را فشار دهید، Flash Fill  خواسته شما را به شکل صحیحی پیش‌بینی کرده و سلول‌های باقی‌مانده را پر می‌کند.

همان‌طور که در تصویر 10-6  نشان‌داده‌شده، می‌خواهیم نام و نام خانوادگی هر فرد را از ستون‌های E و F استخراج نماییم. در اولین و آخرین کاربرگ به‌سادگی عبارت TRICIA را در سلول E6 تایپ کرده و کلید Enter‌را فشار دهید.بعد کلیدهای Ctrl+E را فشار دهید تا اکسل محدوده E7:E13 را بانام هر شخص پر کند. درست شبیه کار قبلی اگر نام Lopez را در سلول F6 تایپ کرده و بعد کلید Enter  را فشار داده و پس از آن Ctrl+E را فشار دهیم، ابزار Flash Fill نام خانوادگی هر شخص را در محدوده F7:F13 وارد می کند.

تصویر 10-6  خاصیت Flash Fill به شکل خودکار نام‌ها را استخراج و به شکل نام‌هایی با حرف اول بزرگ در ستون درج می‌نماید.

همان‌طور که در تصویر 11-6 نشان‌ داده ‌شده ، می‌خواهیم با اضافه‌کردن@UXYZ.edu به نام خانوادگی هر فرد، آدرس پست الکترونیکی برای هریک از آن‌ها ایجاد کنیم. کار را با تایپ کردن:

Loper@UXYZ.edu در سلول E6  کاربرگ ایمیل شروع می‌کنیم. بعد از فشاردادن کلید Enter کلیدهای Ctrl+E را فشار می‌دهیم تا Flash Fill به شیوه‌ای جادویی آدرس‌های پست الکترونیک را در محدوده E7:E13 ایجاد نماید.

تصویر 11-6  ایجاد آدرس پست الکترونیکی با استفاده از Flash Fill

همان‌طور که در تصویر 12-6  نشان‌داده‌شده می‌خواهیم مقادیر دلار و سنت را از قیمت‌های نشان ‌داده ‌شده در سلول‌های محدوده D6:D11  کابرگی بنام Dollars and Cents استخراج کنیم. برای شروع عدد 6 را در سلول E6 تایپ کرده و کلید Enter را فشار دهید. بعد از آنکه کلیدهای Ctrl+E را فشار دادید مقادیر دلار صحیح، محدوده E7:E11 را پر می‌کنند، اگر ما عدد 56 را در سلول F6 وارد کرده و کلید Enter  را به همراه کلیدهای Ctrl+E فشار دهیم، ویژگی Flash Fill مقادیر صحیح سنت را در محدوده F7:F11 پر خواهد کرد.

تصویر 12-6  استفاده ازFlash Fill‌برای استخراج دلارها و سنت ها به شکل جداگانه

باید در نظر داشت که ابزار Flash Fill ممکن است اشتباه کند، مخصوصاً وقتی‌که داده‌ها خیلی یک جور نباشند، همچنین (برخلاف فرمول‌هایی که دارای توابع متنی هستند) نتیجه حاصل از استفاده Flash Fill نتیجه پویایی نیست و چنانچه داده‌های اصلی تغییر کنند، به‌روز نمی‌شود.

اگر دوست داشته باشید می‌توانید ویژگی Flash Fill  را با انتخاب گزینه Options از منوی File در نوارریبون ، انتخاب سربرگAdvanced ، پیدا کردن کادر محاوره ایEditing Options   و غیر فعال کردن گزینه Automatically Flash Fill‌ ، غیر فعال نمایید.

کاراکترهای یونیکد چه هستند؟

کاراکترهای یونیکد مجموعه‌ای از 120000 کاراکتر شامل بسیاری از نمادهای مورد استفاده دانشمندان و کاراکترهایی از زبان‌های مختلف می‌باشند. هر کاراکتر یونیکد دارای عدد شناسه‌ای است بهترین مرجع برای کاراکترهای یونیکد در آدرس اینترنتی http://www.alanwood.net/unicode/ یافت می‌شوند.

می‌توانید ببینید که حروف الفبای یونانی (که برای یونانی‌ها و دانشمندان بسیار مهم هستند) کدهایی  در محدوده 900 دارند (فایلی بنام  Unicodefinal.xlsx و تصویر 13-6 را مشاهده کنید). همچنین می‌توانید کاراکتر مرتبط با یک شناسه عددی را با استفاده از تابعUNICHAR(code number)  مشاهده کنید. مثلا فرمول=UNICHAR(F67) عدد یونانی μ  را برمی گرداند، چرا که کد شناسه حرف μ، عدد 956 است. همچنین می‌توانید کد شناسه یک کاراکتر خاص را با استفاده از تابعUNICHAR(character) پیدا کنید. مثلا فرمول: =UNICODE(G67) کد شناسه حرف μ یعنی عدد 956 را بازمی گرداند.

تصویر 13-6  مثال‌هایی از استفاده از توابع UNICODE و UNICHAR

 

تابع جدید TEXTJOIN چگونه تابع قدیمی CONCATENATE یا  & را بهبود می بخشد؟

فایلی به نام Textjoinfinal.xlsx (تصویر 14-6) نحوه استفاده از تابع جدیدTEXTJOIN‌را نشان می دهد. هدف ما آن است که داده‌های محدوده H1:L2 را در یک سلول باهم ترکیب کنیم. در سلولM2  از متد( از مد افتاده) الحاق (Concatenate)  استفاده کرده‌ایم تا داده ها را با فرمول H2&” “&12&” “&12باهم ترکیب کنیم. در سلول G2 از فرمول:

TEXTJOIN(“ “,TRUE,H2:L2)‌ استفاده می کنیم تا به همان هدف دست یابیم. اولین آرگومان” ”  باعث اطمینان از این مساله می شود که ورودی های محدوده H2:L2 با فاصله خالی از هم جدا شده باشند. دومین آرگومان یعنی TRUE اطمینان حاصل می کند که تمامی سلول‌های خالی( در این مورد J2 ) ‌نادیده گرفته شوند. در سلول G3 فرمول =TEXTJOIN(“ “,FALSE,H3:L3) را وارد می کنیم. تنها تفاوت آن است که فاصله خالی در سلول J3 شامل این الحاق ما شده است. دوباره تکرار می کنیم که برتری استفاده از تابعTEXTJOIN آن است که نیاز به تکرار یک حائل را از بین می برد.

تصویر 14-6 استفاده از تابع TEXTJOIN

 

من عاشق توابع متنی هستم اما چگونه باید از تابعTEXT‌ اکسل استفاده کنم؟

فرض کنید شما در بخش منابع انسانی کار می‌کنید و فهرستی از نام کارمندان و تاریخ تولدشان به شما داده شده است (تصویر 15-6 را ببینید). می‌خواهید برای هر کارمند جمله‌ای شبیه به ” جن در تاریخ 14 اکتبر 1988 به دنیا آمد”را بنویسید. در سلول F14 ما از فرمول D3&”was born on “&E3  استفاده کرده‌ایم تا این جمله را ایجاد نماییم. مشکل این است که به ما گفته شده که جان در 32430 به دنیا آمده است! برای تاریخ تولد 10/14/1988 این عدد شماره روزهایی بعد از اول ژانویه 1900 می‌باشد که اکسل به ما نشان داده است. مشکل اینجاست که اکسل فرمت نامناسبی را برای نمایش انتخاب کرده است. این همان جایی است که تابع TEXT به یاری ما می‌آید. همین‌که فرمت مناسب را برای نمایش تاریخ پیدا کردید، (که در اینجا m/d/yyy است) به ‌راحتی می‌توانید فرمول را به D3&” was born on ” &TEXT(E3,”m/d/yyyy”) تغییر داده تا اکسل فرمت مناسب را برای نمایش انتخاب نماید.

 

تصویر 15-6  استفاده از تابعTEXT

حالا چگونه شناسه فرمت (Format Code) مناسب را پیدا می‌کنید؟

خیلی ساده به سلولی که از فرمت مناسب مورد نظرتان استفاده کرده است رفته (مثلا سلولE3)  و بعد کلیدهای Ctrl+f1 را فشار می دهید تا کادر محاوره‌ای فرمت سلول باز شود. حالا سربرگ Number را انتخاب کرده و بعد گزینه Custom‌را انتخاب می‌کنیم. شما این صفحه پیچیده را در تصویر16-6 مشاهده می‌کنید. خیلی ساده فرمت مناسب را از بخشType کپی کنید( در اینجاm/d/yyy) و آن را در تابع TEXT‌در میان گیومه قرار ‌دهید.

تصویر 16-6 پس از انتخاب گزینه  Custom می‌توانیم فرمت مناسب موردنیاز را پیدا کنیم.

 

همان‌طور که در محدوده سلولD8:G10 نشان داده شده، می‌توانید از فرمت مناسب برای استخراج نام یک ماه یا روز هفته از یک تاریخ استفاده کنید.

“mmm” نام مختصر شده ماه در تاریخ مورد نظر را به ما می‌دهد.

“mmmm”نام کامل ماه در تاریخ مورد نظر را به ما می‌دهد.

“ddd” نام مختصر شده روز هفته در تاریخ مورد نظر را به ما می‌دهد.

“dddd” نام کامل روز هفته در تاریخ مورد نظر را به ما می‌دهد.

 

مسائل این فصل

سلولهایB2:B5 از کتاب کاری بنام Showbiz.xlsx حاوی آدرس های ساختگی اشخاص مورد علاقه مان می‌باشد. از  توابع متنی استفاده کنید تا نام هر شخص را در یک ستون و آدرس او را در ستونی دیگر استخراج نمایید.

کتاب کاری بنام IDprice.xlsx حاوی کد شناسایی و قیمت محصولات مختلف می‌باشد. از توابع متنی برای درج کد شناسایی محصولات و قیمت‌ها در ستون‌هایی جداگانه استفاده کنید. سپس از دستور تبدیل متن به ستون در سربرگ Data در نوارریبون استفاده کنید تا همان نتیجه را به دست آورید.

کتاب کاری بنام Quarterlygnpdata.xlsx حاوی داده‌های تولید ناخالص ملی فصلی ایالات متحده آمریکا (به میلیارد دلار در سال 1996) است. این داده‌ها را در سه ستون جداگانه استخراج کنید به شکلی که ستون اول حاوی سال، ستون دوم حاوی نام هر فصل و ستون سوم حاوی ارزش تولید ناخالص ملی باشد.

فایلی بنام Textstylesdata.xlsx حاوی اطلاعاتی درباره مدل، رنگ و اندازه تعداد متنوعی از پیراهن‌ها است. مثلاً اولین پیراهن مدل 100(که توسط ارقامی بین دونقطه و خط ربط مشخص شده) را دارد، رنگ آن 65 است و اندازه آن L می‌باشد. از توابع متنی برای جداکردن مدل، رنگ و اندازه هر یک از پیراهن‌ها استفاده کنید.

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

@microsoft.com‌را به انتهای آن اضافه کنید. از توابع متنی استفاده کنید تا آدرس ایمیل را ایجاد کنید.

فایلی بنام Lineupdata.xlsx به ما تعداد دقایقی را که توسط ترکیب پنج‌نفره بازیگران فوتبال انجام شده (به ترتیب هر گروه) به ما داده است (مثلاً گروه اول 10.4 دقیقه بازی کرده است و غیره) از توابع متنی استفاده کنید تا این داده‌ها را به شکلی مرتب کنید تا برای محاسبات عددی آماده باشند، مثلاً 10.4m را به عدد 10.4  تبدیل کنید.

فایلی بنام Reversenames.xlsx نام، نام میانی و نام خانوادگی چند نفر را به ما می‌دهد. این نام‌ها را چنان تغییر دهید تا نام خانوادگی اول ظاهر شود و پس ازویرگولی به دنبال آن، نام و نام میانی ظاهر شوند مثلاً Gregory William Winston  را به Wisnton, Gregory,William تغییر دهید.

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

به‌خاطر بیاورید که CHAR(65) حرف A را  و Char(66) حرفB  را برمی‌گرداند و غیره. در فایل جدیدی از این حقایق استفاده کنید تا به طرزی کار‌آمد، سلول های محدوده B1:B26 را با ترتیبی از حروف A،B، C الی حرفZ پر کنید.

فایلی Capitalizefirstletter.xlsx حاوی نام ترانه‌هایی متنوع و یا عباراتی مثل”باران در اسپانیا مرتب درجلگه‌ها می‌بارد” می‌باشد. اطمینان حاصل کنید که حرف اول هر ترانه با حروف بزرگ نوشته شده باشد.

فایلی بنام Ageofmachine.xlsx حاوی داده‌هایی به شکل زیر می‌باشد:

S/N: 160768, vib roller,84” smooth drum,canopy Auction: 6/2–4/2005 in Montgomery, Alabama

هر ردیف به خرید یک ماشین ارتباط دارد. سال هر خرید را مشخص کنید.

وقتی‌که داده‌های شرکتی را از سایت تحلیل، آنالیز و جمع‌آوری داده‌های الکترونیک بورس اوراق بهادار ایالات متحده دانلود می‌کنید اغلب داده‌های شرکت را به این شکل دریافت می‌نمایید: Cash and Cash Equivalents $31,848 $ 31,881

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

فایلی بنام Lookuptwocolumns.xlsx مدل، سال و قیمت یک سری از اتومبیل‌ها را ارائه داده است. فرمولی بنویسید که شما را قادر سازد تا مدل و سال یک ماشین را وارد کرده و قیمت آن را دریافت نمایید.

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

فایلی بنام Moviedat.xlsx حاوی نام چندین فیلم به همراه تعداد کپی‌های دی‌وی‌دی آنها که توسط فروشگاه محلی خریداری شده می‌باشد. برای هر فیلم، تعداد کپی‌هایی خریداری شده را از این داده‌ها استخراج کنید. اشاره: احتمالاً بخواهید از تابع SUBSTITUTE استفاده نمایید. دستور زبان تابع SUBSTITUTE عبارت است از: SUBSTITUTE(text,old_text,new_text,instance_number).. اگر آرگومان Instance_number حذف شود هر جایی که   old_text ظاهر شده باشد باnew_text جایگزین می شود. مثلاSUBSTITUTE(A4,1,2) هر عدد 1 در سلول A4  را با عدد 2 جایگزین می نماید، اما SUBSTITUTE(A4,1,2,3) تنها سومین ظهور از عدد 1 در سلول A4  را با عدد 2 جایگزین می نماید.

فایلی به نام Problem16data.xlsx حاوی تعداد افرادی است که در پرسش‌نامه بازاریابی پاسخ‌هایی بین اعداد 1 تا پنج داده‌اند (عدد 1 یعنی احتمالاً محصول را نمی‌خرند و…. عدد پنج یعنی به‌احتمال زیاد محصول را می‌خرند) این داده‌ها را با استفاده از نماد ستاره به شکل تصویری خلاصه نمایید. برای اینکه نتیجه این خلاصه کردن شما شکیل‌تر به نظر برسد. به نظر برسد می‌توانید به سربرگ Home  در نوارابزار رفته و در گروه گزینه‌های ترازبندی (Alignment) گزینه Orientation  یا تنظیم جهت متن را انتخاب کرده و متن خود را عمودی نمایید. بعد روی بخش row number‌ یا تعداد ردیف ها کلیک کرده و بلندای ردیف را افزایش دهید. بالاخره در گروه گزینه های ترازبندی گزینهWrap Text را انتخاب کنید تا متن تصویر شما با سلول هماهنگ شود( به صورت خطی نوشته نشود و با بزرگ و کوچک شدن سلول اندازه خود را با آن تنظیم نماید)

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

فایلی بنام Weirddata.xls حاوی سه عدد می‌باشد که از سایتی اینترنتی استخراج شده‌اند. وقتی‌که سعی می‌کنید عددها را با هم جمع بزنید می‌بینید که جمع آنها صحیح نیست. این داده‌ها را چنان تغییر دهید که تابعSUM‌ بتواند جمع صحیح سه عدد را بدست بیاورد. اشاره: از تابعFIND‌ استفاده کنید تا دریابید کدام کاراکترهای نامرئی در اینجا وجود دارند.

از ویژگی Flash Fill استفاده کنید تا نام‌های کاربرگی بنام Flashfilltemplate.xlsx را به نام‌هایی با حروف کوچک تغییر دهید.

از Flash Fill استفاده کنید تا اعداد هریک از ردیف‌ها در فایلی بنام Movienumbers.xlsx را استخراج نمایید.

برای داده‌های فایلی بنام Movienumbers.xlsx از ویژگی Flash Fill استفاده کنید تا ستونی ایجاد نمایید که عددهای موجود در عناوین هریک از فیلم‌ها را در انتهای عبارت ” عدد موجود در عنوان این فیلم این است” قرار دهد.

هر ردیف از فایلی بنام Problem22data.xslx نامی از یک شهر، ایالت، و جمعیت شهر را به شما ارائه می‌دهد. از توابع متنی برای استخراج نام ایالت‌ها در ستون جداگانه‌ای استفاده کنید.

فایلی بنام Problem23data.xlsx به شما نام بازیکن خط حمله (QB) به همراه رده‌بندی آن بازیکن خط حمله و همچنین تعداد پاس‌های پرت شده توسط آن بازیکن خط حمله را به شما ارائه می‌دهد. از توابع متنی استفاده کنید تا هریک از پاس‌های کامل آن بازیکن خط حمله را از این داده‌ها استخراج نمایید.

در هریک از ردیف‌های فایلی بنام Problem24data.xlsx شماره کد پستی شهر، نام شهر، ایالت (و همیشه هم تگزاس) عرض جغرافیایی و طول جغرافیایی را مشاهده می‌کنید. از فرمول‌هایی برای استخراج نام شهر در هریک از ردیف‌ها استفاده نمایید. می‌توانید فرض کنید که هریک از کدهای پستی پنج کاراکتر طول دارد. اشاره: تابع FIND به بزرگی و کوچکی حروف حساس است.

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

فایلی با نام Problem26data.xlsx حاوی داده‌های فروش 2000 عدد از بزرگ‌ترین شرکت‌های دنیا است. اگر عدد صحیحی بین 1 و 2000 در سلول B3 وجود داشته باشد آن‌وقت سلول C3 می‌بایست جمله‌ای شبیه به” رتبه این شرکت در سوددهی شماره 1 است و سوددهی آن 328.21دلار می‌باشد” نشان دهد. از تابع TEXT استفاده کنید تا اطمینان حاصل کنید که مقدار عددی مبلغ دلار به شکل و فرمت صحیح نوشته شود.