مفاهیم کلیدی تاریخ و زمان در اکسل

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

یکی از مهمترین مسائلی که کاربران حسابداری در Excel با آن هر روزه سرو کار دارند محاسبه زمان ورود و خروج پرسنل و زمان کارکرد روزانه آنها است که اگر از فرمول IF استفاده کنید به سادگی نخواهید توانست آنرا پیاده سازی نمایید اما با مطالعه فرمول های شرطی زمان برای محاسبه زمان کارکرد به سادگی قادر به انجام اینکار خواهید بود.

مثال های  و موضوعات این فایل عبارتند از :

• منطق تاریخ و زمان در اکسل
• کلیدهای میانبر format cells
• مفهوم ساعت در اکسل
• مثال: محاسبه دستمزد بر اساس ساعت کارکرد
• مثال: جمع ساعات کارکرد در یک ماه
• نکاتی در مورد ساعت و زمان اکسل
• نحوه تایپ صحیح تاریخ میلادی در اکسل
• تنظیمات control panel

ابزار paste picture link در اکسل

ابزار paste picture link در اکسل ابزاری بسیار جالب و پر استفاده جهت حفاظت از محتوای فایل اکسل می باشد.

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

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

سپس در روبان Home دکمه Paste را باز کرده و در زیر منوی as picture گزینه paste as picture را انتخاب کنید.

 

مشاهده خواهید کرد که یک تصویر تولید می شود که دقیقا محتوای کادر انتخاب شده را نمایش می دهد و با تخییر در محتوای اصلی، این تصویر نیز تغییر می کند.

حال می توانید این تصویر را در یک صفحه دیگر قرار دهید و صفحه جاری را از دید مخاطب مخفی نگاه دارید.

 

فعال کردن ماکروی یک فایل اکسل

                                                                                                                                         ماکرو (Macro) یک برنامه کامپیوتری است که فایل Excel اضافه می‌شود. این برنامه به زبان VBA نوشته شده است و از آن پس در در آن فایل خاص می‌توان از آن برنامه استفاده کرد.

Macro به چه کاری می‌آیند؟

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

منظور از فعال سازی یک Macro چیست؟

همانطور که گفتیم ماکرو یک برنامه کامپیوتری که در داخل یک فایل اکسل ذخیره شده است. حال ممکن است که این برنامه مخرب باشد و همین که فایل باز (open) شود، این برنامه کار خود را آغاز می‌کند و کامیپوتر ما با مشکل مواجه می‌شود. به طور خلاصه ، اگر فایل Excel دارای Macro باشد، ممکن است که مانند یک ویروس عمل کند.

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

حال اگر فایلی داشته باشیم که حاوی ماکرو است، و می دانیم که ماکروی آن مورد اعتماد است، باید آنرا از حالت قرنطینه (Block) در آوریم یا اصطلاحا «ماکروی آنرا فعال کنیم».

 

چگونه فایلی که ماکرو دارد را بشناسیم؟

اگر فایل اکسل با پسوند XLSM بود ، بدانید که آن فایل Macro دارد. دقت داشته باشید که پسوند فایل‌های عادی XLSX است.

در ضمن اگر آیکون فایل اکسلی دارای علامت ! بود، یعنی آن فایل XLSM است و یعنی که Macro دارد.

 

فعال کردن ماکرو در Excel 2007 و Excel 2010 تفاوت اندکی دارد.

فعال کردن ماکروی یک فایل در Excel 2007

بلافاصله بعد از باز کردن یک فایل در Excel 2007 پیغام  را در بالای صفحه Excel خواهید دید:

 اگر این پیغام هشدار را مشاهده نکردید، در ادامه همین مقاله ، دلیل آن و نحوه فعال کردن «پیغام هشدار» را مطالعه نمایید.

با زدن بر روی گزینه Options، پنجره ای باز می شودکه می‌توانید Macro  فایل را فعال کنید.

گزینه Enable this Content را انتخاب کنید و سپس کلید Ok را بزنید.

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

فعال کردن ماکروی یک فایل در Excel 2010

 در Excel 2010 کار کمی ساده تر است.

به محض باز کردن یک فایل که Macro دارد، پیغام زیر را خواهید دید. کافیست که روی گزینه Enable Content کلیک کنید.

 

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

 

 در Excel 2010 اگر یکبار ماکروی فایلی را فعال کنید، در دفعات بعدی لازم نیست که اینکار را انجام دهید و در دفعات بعدی، اکسل به صورت هوشمند، آن را به صورت ماکرو فعال (Macro Enabled) باز خواهد کرد.

چرا پیغام هشدار فعال کردن ماکرو ، نمایش داده نمی‌شود?

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

برای تغییر این تنظیم به مسیر زیر بروید:

File --> Excel Options --> Trust Center --> Trust Center Setting --> Macro Setting

و حالت تنظیم را به «ماکرو رو فعال نکن اما هشدار را نشان بده» ، تغییر دهید.

 

چند نکته در خصوص ماکروها:

1- به یاد داشته باشید که اگر فایلی که Macro دارد را  در حالت xlsx ذخیره کنید، ماکروی آن کاملا حذف می‌شود. البته Excel پیغام هشدار مناسبی به شما نمایش می‌دهد.

2- با وجود اینکه گزینه «نمایش هشدار» در Excel 2010 را فعال کرده اید، باز هم پیغامی نشان داده نشد، گزینه زیر را نیز بررسی نمایید که در حالت "show the messange" تنظیم شده باشد.

Excel 2010: 

File --> Excel Options --> Trust Center --> Trust Center Setting --> Message Bar

توابع جستجو ومرجع در اکسل

توابع جستجو و مرجع در اکسل یا Lookup & Reference Functions مانند توابع ریاضی و توابع متنی که پیشتر با آنها آشنا شدیم، از جمله توابع عمومی اکسل محسوب میشوند که تمام کاربران اکسل به آنها احتیاج دارند، توابع جستجو و مرجع در فایل های اکسلی که محتوی بانک های اطلاعاتی میباشند بسیار کاربرد دارد، برخی از توابع جستجو و مرجع در اکسل مانند توابع VLOOKUP و HLOOKUP بسیار معروف و پرکاربرد هستند. همواره در فرمول ها و توابع اکسل میتوان از سلول های دیگر به عنوان آرگومان استفاده نمود.                                                            

معرفی توابع جستجو و مرجع در اکسل

توابع جستجو و مرجع در اکسل یا Lookup & Reference Functions توابعی هستند که در بانک های اطلاعات در اکسل به صورت گسترده مورد استفاده قرار میگیرند، کارهایی مانند جستجو در بانک های اطلاعاتی، ارجاع دادن و بسیاری موارد دیگر از جمله کاربردهای توابع جستجو و مرجع در اکسل میباشند. در این مقاله برخی از توابع “مرجع” مورد بررسی قرار میگیرند .

 

توابع مرجع در اکسل

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

 

توابع  COLUMN و  COLUMNS در اکسل

از تابع COLUMN در اکسل میتوان شماره ستون یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:

COLUMN (reference)

  

تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد چهار خواهد بود یعنی شماره ستون سلول D10.

اگر مجموعه ای از سلول ها (Range) را بجای reference  قرار دهیم خروجی تابع شماره سمت چپ ترین ستون خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره ستون سلولی که تابع در آن نوشته شده است میباشد.

تابع COLUMNS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد ستون های یک آرایه را بدست آورد.

توابع  ROW و  ROWS در اکسل

دقیقاً مانند تابع COLUMN عمل میکند ولی این تابع اطلاعات ردیف (Row) را به ما میدهد. از تابع ROW در اکسل میتوان شماره سطر یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:                      

ROW (reference)

تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد ده خواهد بود یعنی شماره سطر سلول D10.

اگر مجموعه ای از سلول ها (Range) را بجای reference  قرار دهیم خروجی تابع شماره بالا ترین سطر خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره سطر سلولی که تابع در آن نوشته شده است میباشد.                                                          تابع COLUMNS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد ستون های یک آرایه را بدست آورد.                                                                                       

توابع  ROW و  ROWS در اکسل

دقیقاً مانند تابع COLUMN عمل میکند ولی این تابع اطلاعات ردیف (Row) را به ما میدهد. از تابع ROW در اکسل میتوان شماره سطر یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:

ROW (reference)

تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد ده خواهد بود یعنی شماره سطر سلول D10.            

 

اگر مجموعه ای از سلول ها (Range) را بجای reference  قرار دهیم خروجی تابع شماره بالا ترین سطر خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره سطر سلولی که تابع در آن نوشته شده است میباشد.

تابع ROWS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد سطر های یک آرایه را بدست آورد.

تابع ADDRESS در اکسل

از تابع ADDRESS در اکسل برای بدست آورد آدرس یک سلول در Worksheet استفاده میشود، Syntax این تابع به صورت زیر است:

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• در ساختار این تابع، آرگومان اول و دوم آرگومان های اجباری و عدد هستند و به ترتیب شماره سطر و ستون سلولی هستند که میخواهیم آدرس آن را بدست بیاوریم، آرگومان سوم، چهارم و پنجم آرگومان های اختیاری هستند.
• آرگومان سوم در مورد نسبی یا مطلق بودن آدرس سلول تعیین تکلیف میکند. این آرگومان میتواند خالی بماند و یا یکی از اعداد ۱، ۲، ۳ یا ۴ باشد، اگر خالی یا عدد یک باشد آدرس دهی به صورت مطلق خواهد بود و اگر عدد ۴ باشد آدرس دهی به صورت نسبی میباشد، اگر میخواهیم آدرس سلول به نحوی بیان شود که سطر به صورت مطلق و ستون نسبی باشد، عدد ۲ و برای برعکس این حالت عدد ۳ را بجای آرگومان سوم قرار دهید.
• آرگومان چهارم هم یک آرگومان اختیاری و یک مقدار منطقی (Logical Value) میباشد، اگر این آرگومان خالی بماند و یا True باشد، آدرس سلول به فرمت آشنای A1 یعنی شماره سطر عدد و شماره ستون حرف بیان میشود و اگر این آرگومان False باشد فرمت بیان آدرس سلول به صورت R1C1 میشود که سطر و ستون هر دو عدد هستند.
• و نهایتاً، آرگومان پنجم نام شیت در برگیرنده ی سلول را در صورتی که تمایل داشته باشیم در آدرس سلول ذکر شود را مشخص میکند.

به مثال زیر توجه کنید:

ADDRESS(8;13;3;FALSE;”Sheet1″)= Sheet1!R[8]C13

ADDRESS(8;13;3;TRUE;”Sheet1″)= Sheet1!$M8

برای نشان دادن مطلق بودن آدرس ها در فرمت R1C1 از قرار دادن عدد سطر یا ستون در براکت استفاده میکنیم.

تابع AREAS در اکسل

در اکسل، Area به یک محدوده ی پیوسته از سلول ها شامل یک یا چندین سلول که در حالت انتخاب قرار دارد گفته میشود، مثلاً سلول A1 به تنهایی میتواند یک Area باشد، همینطور محدوده ی A1:C5 هم اگر توسط کاربر انتخاب شود یک Area است. تابع AREAS تعداد Area های یک محدوده (reference) را مشخص میکند. سینتکس این تابع به صورت زیر است:

AREAS (reference)

در عبارت بالا reference تنها میتواند یک Area باشد، برای انتخاب چند Area باید از پرانتز استفاده کنیم، به مثال های زیر توجه نمایید:

AREAS(B2:D4) =1

AREAS((B2:D4,E5,F6:I9)) = 3

AREAS(B2:D4B2) = 1

در مثال سوم دو Area وجود دارند، اولی B2:D4 و دومی B2 است، در واقع منظور از این نوع نوشتن Area ها بدست آوردن Area فصل مشترک دو Area اصلی میباشد که یک Area است و خروجی این تابع عدد یک میباشد، اگر دو Area قرار گرفته به عنوان reference با هم فصل مشترک نداشته باشند، خروجی تابع خطای !NULL# خواهد بود.

 

تابع CHOOSE در اکسل

سینتکس تابع CHOOSE به صورت زیر است:

CHOOSE(index_num,value1,value2,…)

در عبارت بالا، آرگومان اول عددی است بین ۱ و ۲۵۴ که در واقع شماره مشخصه (Index Number) مربوط به یکی از ۲۵۴ مقدار داده شده به تابع در آرگومان های دوم تا دویست و پنجاه و پنجم میباشد.

آرگومان های Value1، Value2،… تا Value254 میتواند یک فهرست دلخواه کاربر باشد (مانند نام پرسنل، شماره اقلام، آدرس سلول و…) که به ترتیب شماره مشخصه (Index Number) یک تا ۲۵۴ را به خود اختصاص میدهند، خروجی تابع CHOOSE آیتم مربوط به Index_num مورد نظر کاربر میباشد.

اگر عدد قرار گرفته در آرگومان اول کمتر از یک یا بیشتر از تعداد Value ها باشد، خروجی تابع خطای !VALUE# خواهد بود. و اگر عدد Index_num یک عدد اعشاری باشد تنها ارقام صحیح آن عدد در نظر گرفته میشود.

به مثال های زیر توجه کنید:

SUM (CHOOSE (2,A1:A10,B1:B10,C1:C10)) = SUM(B1:B10)

CHOOSE (2,A1:A10,”Excelpro”,۱۳)) = Excelpro

تابع INDEX در اکسل

این تابع دو حالت آرایه ای (Array) و Reference دارد، حالت اول مربوط به آرایه ها میباشد که در مورد آن بعداً بحث خواهیم نمود، سینتکس تابع INDEX به صورت زیر است:

INDEX(reference,row_num,column_num,area_num)

• این تابع چهار آرگومان دارد، reference که آرگومان اول میباشد آدرس یک یا چند محدوده یا Area میباشد، توجه داشته باشید مانند آنچه در تابع AREAS یاد گرفتیم در اینجا هم اگر بخواهیم به چند محدوده یا Area به صورت همزمان اشاره کنیم نیاز به استفاده از پرانتز داریم.
• آرگومان دوم و سوم به ترتیب یک شماره سطر و ستون مربوط به reference میباشد که کاربر میخواهد به آن اشاره کند، مثلاً فرض کنید شما محدوده ای B2:E8 را به عنوان reference به تابع معرفی کرده اید، حال میخواهید مقدار سلول C5 را بدانید، بجای آرگومان دوم عدد ۴ و بجای آرگومان سوم عدد ۲ را قرار دهید (زیرا در محدوده ی انتخاب شده B2:E8 سلول C5 در سطر چهارم و در ستون دوم محدوده قرار دارد)، در ضمن اگر reference معرفی شده تنها دارای یک سطر یا ستون باشد میتوانید آرگومان دوم یا سوم را خالی بگذارید.
• همانطور که گفته شد، میتوانید چند Area را در محل reference به تابع معرفی کنید، در این صورت آرگومان چهارم تعیین میکند کدام Area مورد نظر شما است، به عنوان مثال اگر سه Area به تابع معرفی کنید و آرگومان چهارم عدد دو باشد، تابع در Area دوم به دنبال سطر و ستون مورد نظر شما میگردد

اگر شماره سطر و سلول قرار گرفته به عنوان آرگومان دوم و سوم در محدوده ی Area مورد نظر نباشد خروجی تابع برابر خطای !REF# خواهد بود.

توابع اطلاعات در اکسل

توابع اطلاعات یا گزارش گیری در اکسل (Information Functions) از جمله توابع عمومی اکسل محسوب میشوند که تمام کاربران اکسل به آنها احتیاج دارند، رسالت اصلی این نوع توابع، اطلاع از وضعیت سلول ها و فایل اکسل میباشد که در ادامه به آن پرداخته میشود. با توجه به کاربرد وسیع این توابع در رفع اشکال فایل های اکسل یادگیری آنها به افراد در تمامی تخصص ها توصیه میشود.                                                                            

تعریف و دسته بندی توابع اطلاعات در اکسل

توابع اطلاعات در اکسل و یا Information Functions در واقع ابزارهای گزارش گیری از وضعیت سلول ها میباشند، در بانک های اطلاعاتی و در رفع اشکال فایل های اکسل، توابع اطلاعات بسیار کاربرد دارند. با استفاده از این توابع میتوان به محتوای سلول ها و یا به اطلاعاتی مفید در مورد آنها پی برد و یا میتوان از نوع خطای رخ داده در سلول با خبر شد. توابع اطلاعات در اکسل را به سه دسته تقسیم میشود ، توابع مربوط به “بدست آوردن اطلاعات” و “توابع دیگر” در این مقاله مورد بررسی قرار میگیرند و در مقاله ی بعدی به معرفی توابع “بررسی محتوای سلول ها” پرداخته میشود.

توابع بدست آوردن اطلاعات در اکسل

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

 

تابع CELL در اکسل

از تابع CELL برای بدست آوردن اطلاعاتی در مورد یک سلول استفاده میگردد. سینتکس این تابع به صورت زیر است:

CELL (info_type, [reference])

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

آرگومان اول یک داده ی متنی میباشد که نوع اطلاعات مورد نیاز را تعیین میکند

تابع ERROR.TYPE در اکسل

تابع ERROR.TYPE یک آرگومان دارد و Syntax آن به صورت زیر است:

ERROR.TYPE (error_val)

خروجی این تابع عددی متناظر با نوع خطا آرگومان این تابع میباشد، در صورتی که آرگومان این تابع خطا نباشد خروجی تابع ERROR.TYPE خطای N/A# خواهد بود. از این تابع میتوان برای تشخیص نوع خطاها و تصمیم گیری بر اساس آن استفاده نمود و یا اینکه مثلاً با استفاده از یک ساختار IF برای هر نوع خطا یک رشته متنی در نظر بگیرید که به کاربر توضیح دهد چه چیزی را باید اصلاح کند.                                                                 تابع TYPE در اکسل

از این تابع برای بدست آوردن نوع داده استفاده میشود، سینتکس تابع TYPE به صورت زیر است:

TYPE (value)

آرگومان value میتواند هر نوع داده ی معتبر در اکسل و یا آدرس یک محدوده از اکسل باشد که با توجه به نوع آن و طبق جدول زیر خروجی تابع TYPE تعیین میگردد.

 

تابع SHEET در اکسل

تابع SHEET در اکسل شماره شیت سلول مرجع را ارائه میدهد، “شماره شیت” یعنی اینکه شیت مورد نظر چندمین شیت در مجموعه شیت های موجود میباشد. سینتکس این تابع به صورت زیر است:

SHEET (value)

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

• شیت شامل تمام انواع شیت های اکسل مانند Chart Sheet، Macro Sheet، Dialog Sheet و Worksheet میشود و شیت های Visible، Hidden و Very Hidden را در بر میگیرد.
• اگر آرگومان تابع SHEET یک آرگومان معتبر نباشد، خروجی تابع خطای !REF# خواهد بود.
• اگر آرگومان تابع نام یک شیت نا معتبر (مثلاً نوشتن نام اشتباه یا نوشتن نام شیتی که وجود ندارد) باشد، خروجی تابع خطای N/A# خواهد بود.

تابع SHEETS در اکسل

از این تابع برای بدست آوردن تعداد شیت ها استفاده میگردد. سینتکس تابع SHEETS به صورت زیر است:

SHEETS (reference)

تنها آرگومان این تابع نام فایل اکسلی است که میخواهیم تعداد شیت آن را بدانیم. آرگومان این تابع اختیاری است و در صورت نادیده گرفته شدن خروجی تابع برابر با تعداد شیت های فایل اکسلی هست که فرمول در آن نوشته شده است.

• شیت شامل تمام انواع شیت های اکسل مانند Chart Sheet، Macro Sheet، Dialog Sheet و Worksheet میشود و شیت های Visible، Hidden و Very Hidden را در بر میگیرد.
• اگر آرگومان تابع SHEETS یک آرگومان معتبر نباشد، خروجی تابع خطای !REF# خواهد بود.

تابع INFO در اکسل

این تابع برای بدست آوردن اطلاعات در مورد فایل اکسلی که فرمول در آن نوشته شده است کاربرد دارد، سینتکس این تابع به صورت زیر است:

INFO (type_text)

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

 

توابع دیگر اطلاعات در اکسل

تابع NA در اکسل

این تابع یک تابع بدون آرگومان میباشد و خطای N/A# را نمایش میدهد. نمایش این خطا برای کاربر به معنی “هیچ داده ای موجود نیست (No Value is Available)” میباشد. در فرمول نویسی ها گاهی میتوان بجای خالی گذاشتن سلول ها این خطا را استفاده کرد تا کاربر گمان نکند که سلول اشتباهاً خالی مانده. سینتکس این تابع به صورت زیر است:

NA ( )

تابع N در اکسل

این تابع یک آرگومان دریافت میکند و عدد معادل آن آرگومان را به عنوان خروجی میدهد:

• اگر آرگومان این تابع یک عدد باشد، خروجی تابع همان عدد است.
• اگر آرگومان ورودی تابع تاریخ یا ساعت باشد، خروجی تابع N عدد معادل با آن تاریخ و ساعت میباشد.
• اگر ورودی تابع داده ی منطقی True یا False باشد، خروجی تابع ۱ یا صفر است.
• اگر آرگومان ورودی تابع یک خطا باشد، خروجی تابع هم همان خطا خواهد بود.
• در مواردی غیر از موارد فوق، خروجی تابع N عدد صفر است.

 

 

 

Consolidate در اکسل

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

معرفی ابزار Consolidate در اکسل

ابزار Consolidate در اکسل در تب Data و در گروه Data Tools قرار گرفته است.                  

کار با ابزار Consolidate در اکسل

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

برای تهیه گزارش فروش سالیانه شرکت بالا توسط ابزار Consolidate در اکسل، ابتدا یک شیت خالی ایجاد میکنیم و یک سلول را انتخاب مینماییم، سپس بر روی ابزار Consolidate کلیک میکنیم. پنجره Consolidate در اکسل در زیر نشان داده شده است، در این پنجره و در منوی Function تصمیم میگیریم که چه نوع عملیاتی باید بر روی داده های عددی انجام گیرد، میتوان هر یک از عملیات های SUM، COUNT، AVERAGE، MAX، MIN، PRODUCT، COUNT NUMBERS، STDDEV، STDDEVP، VAR، VARP را بر روی داده های عددی انجام داد.                                                                                                                                   برای اضافه کردن جداول گزارشات فصلی، بر روی دکمه Reference در قسمت Reference  کلیک نمایید و محدوده مورد نظر را انتخاب کنید، پس از انتخاب محدوده ی مورد نظر بر روی دکمه Add کلیک کنید تا آن محدوده در قسمت All References اضافه گردد. (برای این مثال ابتدا محدوده ی جدول در شیت یک را انتخاب کنید، آن را Add نمایید و سپس محدوده ی جدول در شیت دو را انتخاب و Add کنید و به همین ترتیب چهار جدول مربوط به فصول را از چهار شیت متفاوت انتخاب و Add نمایید.)

 

 

• برای حذف یک محدوده از قسمت All References آن محدوده را انتخاب و بر روی دکمه ی Delete کلیک نمایید

 

 

 چهار محدوده ار فایل اکسل نمونه انتخاب می کنیم و عملیات SUM قرار است بر روی آنها صورت گیرد. اگر جداول انتخاب شده، مانند جداول این نمونه، دارای عنوان سطر (در ستون سمت چپ) وعنوان ستون (در سطر اول) باشند گزینه های Top Row و Left Column را تیک میزنیم.

 

اگر گزینه ی Create Link to Source Data را تیک بزنیم، با تغییر داده ها در هریک از جداول مرجع (References)، اطلاعات خروجی ابزار Consolidate نیز تغییر خواهند کرد و جدول خروجی به صورت خودکار به روز رسانی خواهد شد.

 

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

 

Data Validation در اکسل

                                                                                                                                            ابزار Data Validation در اکسل یکی از پر کاربرد ترین ابزار ها در فرمول نویسی ها، Formula Sheet ها، نرم افزار های محاسباتی نوشته شده توسط اکسل و… میباشد. توسط ابزار Data Validation در اکسل میتوانید کاربر را طوری محدود کنید که فقط داده های مورد نظر شما را در سلول های تعیین شده وارد نماید، به عنوان مثال میتوانید کاربر را محدود کنید در سلولی فقط بتواند عددی زوج وارد کند، یا اینکه فقط اعداد بزرگتر از ۱۳ قابل وارد شدن در سلول باشند یا اینکه کاربر فقط یکی از گزینه های مورد نظر شما را بتواند در سلول بنویسد و بسیاری محدودیت های دیگر که در ادامه با آنها و روش اعمال آنها آشنا میشویم.                                                     

محدودیت ها در Data Validation در اکسل

برای استفاده از ابزار Data Validation ابتدا سلول مورد نظر را انتخاب نمایید و سپس از تب Data و در گروه Data Tools بر روی دکمه Data Validation کلیک کنید،

گزینه ی اول (Data Validation…) پنجره Data Validation را باز میکند که برای ایجاد محدودیت ها توسط این ابزار به کار برده میشود. با کلیک بر روی گزینه ی دوم (Circle Invalid Data) سلول هایی از صفحه اکسل که ابزار Data Validation در مورد آنها به کار رفته و محتوی داده ای نا معتبر میباشد مشخص میشوند. برای مشخص کردن سلول های دارای داده ی نا معتبر،  دور آن ها خطی قرمز ترسیم میگردد که با کلیک بر روی گزینه ی سوم (Clear Validation Circle) خطوط رسم شده پاک میشوند.

در تب اول پنجره Data Validation در اکسل میتوان تنظیمات مربوط به Validation Criteria را انجام داد، در کادر Allow ابتدا نوع داده یا نوع محدودیت ورود داده به سلول (داده معتبر) را تعیین مینماییم، داده های معتبر را به سه دسته تقسیم کرده ایم، دسته اول شامل گزینه های Whole Number (عدد)، Decimal (عدد علمی)، Date و  Time (تاریخ و زمان) و Text Length (طول رشته متنی) میباشد. دسته دوم گزینه List و دسته سوم Custom است.

به عنوان مثال، اگر گزینه ی Whole Number را انتخاب کنیم، به این معنا است که تنها داده های عددی (اعداد) به عنوان محتوای سلول مورد نظر معتبر میباشند.

در کادر دوم (Data) جزییات مربوط به گزینه ی انتخاب شده در کادر Allow تعیین میگردد، جزییات مربوط به گزینه های قرار گرفته در دسته اول (شامل Whole Number، Decimal، Date، Time و Text Length) یکسان میباشد زیرا همگی ماهیتی عددی دارند. در مثال بالا با فرض تعیین Whole Number یا هریک از گزینه های دسته اول در کادر Allow، در این قسمت اعداد و محدوده های مجاز برای ورود به سلول را به ترتیب زیر تعیین میکنیم:

• اگر میخواهیم اعداد بین دو عدد خاص معتبر باشند گزینه ی Between،
• برای معتبر بودن اعداد خارج از بازه دو عدد خاص گزینه ی Not Between،
• برای معتبر بودن تنها یک عدد خاص گزینه ی Equal to،
• برای معتبر بودن اعدادی غیر از یک عدد خاص گزینه ی Not Equal to،
• برای معتبر بودن اعداد بزرگتر از یک عدد خاص گزینه ی Greater Than،
• برای معتبر بودن اعداد کوچکتر از یک عدد خاص گزینه ی Less Than،
• برای معتبر بودن اعداد بزرگتر مساوی یک عدد خاص گزینه ی Greater than or Equal to،
• و در نهایت برای معتبر بودن اعداد کوچکتر مساوی یک عدد خاص گزینه ی Less than or Equal to.

اگر میخواهید سلول مورد نظر در صورت خالی بودن هم مورد بررسی قرار بگیرد تیک گزینه ی Ignore Blank را بردارید.

        گزینه ی لیست (List) در کادر Allow، در صورتی که بخواهید کاربر تنها داده هایی خاص که شما تعیین میکنید را بتواند در سلول وارد کند کاربرد دارد، با انتخاب گزینه ی List، کادری به عنوان Source نشان داده میشود که میتوانید لیست داده های دلخواه که در سلول های اکسل نوشته شده است را انتخاب کنید همچنین با تیک زدن گزینه In-Cell Dropdown با انتخاب سلول مورد نظر توسط کاربر، لیست تعیین شده نشان داده میشود که کاربر میتواند گزینه ی دلخواه را از میان آنها انتخاب کند.                                                 

تعیین محدودیت دلخواه در Data Validation

تا اینجا با تمام گزینه های تعیین داده ی معتبر برای سلول ها آشنا شدیم. گزینه ی آخر از فهرست Allow گزینه ی Custom میباشد. با استفاده از گزینه ی Custom کاربران میتوانند محدودیت های دلخواه در ورود داده ها به سلول ها را ایجاد نمایند، مثلاً شما میتوانید کاربر را ملزم به وارد نمودن اعداد زوج نمایید و بسیاری کاربرد های دیگر.

برای اینکار پس از انتخاب سلول مورد نظر از پنجره Data Validation و در قسمت Allow گزینه ی Custom را انتخاب نمایید. همانند آنچه در Conditional Formatting و ایجاد قوانین دلخواه دیدیم، در اینجا نیز فرمول نوشته شده حتماً باید خروجی Boolean داشته باشد یعنی خروجی فرمول حتماً باید True یا False باشد. بنابراین توابع Is که جز توابع اطلاعات (Information Functions) میباشند در این مقوله بسیار کاربرد دارند از طرفی توابع منطقی (Logical Functions) مانند توابع IF، AND و OR بسیار مهم و کاربردی میباشند.

                             

گروه Formula Auditing در اکسل

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

با توجه به توضیحات بالا، گروه Formula Auditing در اکسل ابزارهایی را در اختیار کاربران قرار میدهد تا بتوانند علاوه بر دنبال کردن روند بدست آمدن خروجی از فرمول ها، خیلی سریع پی به ساختار فرمول ها و ارتباط سلول های اکسل با یکدیگر و با فرمول های نوشته شده در سلول ها ببرند. گروه Formula Auditing در اکسل در تب Formulas قرار گرفته است.            

ابزار های Formula Auditing در اکسل

• در گروه Formula Auditing در اکسل با انتخاب یک سلول و کلیک کردن بر روی گزینه ی Trace Dependents اکسل از سلول انتخاب شده فلش هایی به سمت سلول های دیگر اکسل رسم میکند به شرطی که سلول انتخاب شده در فرمول نوشته شده در آن سلول ها استفاده شده باشد، به این ترتیب به راحتی میتوان فهمید که سلول انتخاب شده در کدام فرمول ها مورد استفاده قرار گرفته است.
• برعکس حالت فوق، برای اینکه بفهمیم در فرمول نوشته شده در یک سلول کدام سلول ها مورد استفاده قرار گرفته اند، میتوانیم بر روی سلول حاوی فرمول کلیک کرده و گزینه ی Trace Precedents را انتخاب کنیم.
• برای حذف فلش های رسم شده  میتوانیم بر روی گزینه ی Remove Arrows کلیک نماییم.
• در اکسل به صورت پیشفرض نتیجه ی فرمول های نوشته شده در یک سلول به کاربر نشان داده میشود و برای مشاهده ی فرمول ها باید از Formula Bar استفاده کرد. با انتخاب گزینه ی Show Formulas در گروه Formula Auditing در اکسل، فرمول های اکسل در سلول ها نمایش داده میشوند.

ارزیابی و بررسی فرمول ها در اکسل

برای بررسی روند اجرای یک فرمول در اکسل میتوان از گزینه ی Evaluate Formula در گروه Formula Auditing در اکسل استفاده کرد. ابتدا سلولی را که قصد بررسی فرمول نوشته شده در آن را دارید انتخاب کنید و سپس بر روی گزینه ی Evaluate formula کلیک کنید. با کلیک بر روی گزینه ی Evaluate میتوانید گام به گام روند اجرای فرمول را بررسی نمایید.

به عنوان مثال، در سلول A2 دمای هوا را درج نمایید، با نوشتن فرمول زیر در سلول B2 با توجه به دمای هوای ذکر شده در شده در سلول A2 دمای هوا را توصیف کنید:

=IF(A2<=0;”Freezing”;IF(AND(A2>0;A2<=8);”Cold”;IF(AND(A2>8;A2<=15);”Cool”;IF(AND(A2>15;A2<=25);”Warm”;IF(A2>25;”Hot”)))))

حال دمای هوا را در سلول A2 وارد نمایید و با انتخاب سلول B2 و کلیک بر روی گزینه ی Evaluate روند تصمیم گیری و بدست آمدن خروجی توسط این فرمول را بررسی نمایید.

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

با کلیک کردن بر روی گزینه ی Step In در مراحل Evaluate کردن فرمول، مقدار های جایگزین شده برای متغیرها و آرگومان های فرمول (که آدرس سلول میباشند) نشان داده میشود، مثلاً اگر زیر A1 خط کشیده شده باشد با کلیک کردن بر روی Step In مقدار سلول A1 نشان داده میشود و با کلیک کردن بر روی گزینه ی Step Out میتوان به Evaluate کردن فرمول ادامه داد.

بررسی کردن خطاها در اکسل

با انواع خطاها در اکسل قبلاً آشنا شدیم، اگر سلولی دارای خطا باشد، با انتخاب آن سلول و کلیک بر روی دکمه ی Error Checking در گروه Formula Auditing در اکسل میتوان خطای رخ داده را بررسی کرد.

با کلیک کردن بر روی گزینه ی Error Checking، اکسل گزینه هایی برای بررسی علت خطا و اصلاح آن در اختیار کاربران قرار میدهد که با تمامی آنها آشنا هستیم.

از طرفی در صورتی که در هریک از سلول های اکسل فرمولی نوشته شده باشد که از خود آن سلول در فرمول استفاده شده باشد (مثلاً در A1 بنویسیم A1*5=) خطای Circular اتفاق میفتد و خروجی فرمول صفر نشان داده میشود، برای اطلاع از خطاهای Circular رخ داده در شیت اکسل میتوان بر روی گزینه ی Circular Reference کلیک نمود.                                           

تکنیک های اشکال زدایی فرمول ها

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

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

معرفی تکنیک های اشکال زدایی فرمول ها در اکسل

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

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

  

   

  توابع IS و توابع ترکیبی IF در اشکال زدایی فرمول ها

   توابع بر اساس محتوا در اکسل شامل سه تابع ISODD، ISEVEN و ISFORMULA و توابع IS هستند. توابع IS در اکسل شامل توابع ISBLANK، ISERR، ISERROR، ISLOGICAL، ISNA، ISNONTEXT، ISNUMBER، ISREF و ISTEXT میباشند. تمامی این توابع ساختار یکسانی دارند و خروجی آنها Boolean میباشد، توابع IS در اکسل تنها یک ورودی میگیرند و آن را برای شرط خاصی بررسی میکنند و خروجی تمام این توابع تنها TRUE یا FALSE میباشد.

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

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

  با استفاده از تابع TYPE میتوانیم نوع داده های وارد شده به سلول و یا خروجی فرمول ها را بررسی نماییم و در صورت مغایرت با نوع مورد نظر خود اقدام مناسب را صورت دهیم.

  فرض کنید قرار است کاربر در سلول A1 تعداد کالای مورد درخواست را وارد کند و این عدد در فرمول های دیگر برای محاسبات پارامترهای مورد نیاز استفاده شود، حال اگر کاربر A1 را خالی بگذارد و یا سهواً داده ای غیر عددی در آن وارد نماید مسلماً فرمول های ما با خطا مواجه خواهند شد، برای این کار میتوانیم در سلول A2 فرمول مشابه فرمول زیر بنویسیم:

  =IF(OR(ISBLANK(A1);TYPE(A1)<>1);”Wrong Data, Number Only please”; “Let’s Do Next Step”)

  در ساختار فرمول بالا از تابع OR (توابع منطقی در اکسل) و توابع TYPE و ISBLANK استفاده شده، به این ترتیب در صورتی که سلول A1 خالی باشد و یا داده ای غیر عددی در آن وارد گردد پیامی مبنی بر خطا بودن نوع داده ی ورودی به کاربر نشان داده میشود. اگر قرار بود عدد وارد شده در A1 به عنوان ورودی تابع POWER (توابع ریاضی) باشد، مسلماً غیر عددی بودن A1 باعث بروز خطا در فرمول میگردید، در چنین موردی ساختاری مانند ساختار زیر را میتوان استفاده کرد:

  =IF(OR(ISBLANK(A1);TYPE(A1)<>1);”Wrong Data in A1″;POWER(A1;2))

  در فرمول بالا در صورتی که A1 داده ای غیر عددی باشد پیام خطا نوشته میشود و در غیر اینصورت روال عادی فرمول نویسی طی میشود.

  همانطور که از مثال های بالا متوجه شدیم از ترکیب تابع IF و توابع IS و دیگر توابع اطلاعات در اکسل میتوان ساختارهای بسیار مفید در جلوگیری از ورود خطاها و داده های اشتباه در فرمول ها تشکیل داد. در این میان دو تابع ISERR و ISERROR از اهمیت ویژه ای برخوردارند، زیرا این دو تابع در نهایت میتوانند از نشان داده شدن پیام های خطا در فایل اکسل شما جلوگیری کنند و به عبارتی خطاها را به دام اندازند. ساختارهای زیر کمک میکنند که با استفاده از این توابع و تابع IF از نشان داده شدن پیام خطا در فایل های اکسل خود جلوگیری نمایید:

  =IF(ISERROR(A1);”This Formula Contains Error”; “Acceptable Outcome”)

  حتی در فرمول نویسی حرفه ای تر میتوان به کاربر با توجه به نوع خطای اتفاق افتاده پیام متناسب داد، مثلاً در صورت بروز خطای N/A# در خروجی تابع VLOOKUPحتماً میتوان این نتیجه را گرفت که عبارت مورد نظر در جدول مورد جستجو وجود نداشته، این پیام را میتوان از ساختاری مشابه ساختار زیر به کاربر داد:

  =IF(ERROR.TYPE(A1)=7;”The Required Item is not in Table”;””)

  در فرمول بالا در صورت بروز خطای خطای N/A# پیامی برای کاربر نمایش داده میشود و او را در جریان قرار میدهد، حتی میتوان ساختار زیر را مستقیماً در سلول A1 استفاده کرد:

  =IF(ERROR.TYPE(VLOOKUP(“Excelpro”;A2:C13;2;FALSE))=7;”The Required Item is not in Table”;VLOOKUP(“Excelpro”;A2:C13;2;FALSE))

  در ساختار بالا عبارت Excelpro در محدوده A2:C13 جستجو میشود و در صورت پیدا نشدن پیامی متناسب به کاربر داده میشود.

   

  استفاده از گروه Formula Auditing در اشکال زدایی فرمول ها

  گروه Formula Auditing در تب Formulas برای رصد کردن وضعیت فرمول ها در اکسل مورد استفاده قرار میگیرد.

Go To در اکسل

                                                                                                                                       به صورت خلاصه، ابزار Go To در اکسل برای انتخاب محدوده ها (Range) کار برد دارد. فرض کنید شما مشغول فرمول نویسی یا رفع اشکال فرمول ها در یک Spreadsheet حرفه ای هستید یا اینکه میخواهید فرمول های نوشته شده در یک فرمول شیت را بررسی کنید. برای اینکار، در وهله ی اول نیاز به شناخت سلول های حاوی فرمول دارید. یا فرض کنید در یک فایل اکسل طولانی میخواهید به آخرین سلول را فعال نمایید و یا یک محدوده پیوسته از اکسل (Area) را انتخاب کنید. ابزار Go To در اکسل از جمله Tools های کاربردی میباشد که در ادامه با آن آشنا خواهیم شد.

معرفی Go To در اکسل

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

لازم به ذکر است پنجره Go To Special هم به عنوان قسمتی از ابزار Go To به شمار می آید که در این مقاله مورد بررسی قرار میگیرد.

کاربرد ابزار Go To در اکسل به اینجا ختم نمیشود.

 

کار با ابزار Go To در اکسل

در تب Home و در گروه Editing، بر روی منوی Find & Select کلیک نمایید. با کلیک بر روی گزینه ی Go To، پنجره Go To باز میشود،(با فشردن کلید Ctrl + G میتوان پنجره Go To را باز کرد):

 در پنجره Go To، تمام نام های تعریف شده در فایل اکسل فعال نشان داده میشوند که کاربر میتواند با کلیک بر روی هریک، سلول های مربوط به آنها را انتخاب نماید.  در قسمت Reference میتوان نام هر محدوده ی دلخواه در فایل اکسل را وارد نموده با فشردن کلید Ok آن محدوده را انتخاب کرد. از طرفی کلیک کردن بر روی دکمه Special در پنجره Go To در اکسل و یا انتخاب گزینه Go To Special از منوی Find & Select پنجره Go To Special را باز میکند .

پنجره Go To Special در اکسل

پنجره Go To Special در اکسل برای انتخاب سلول های اکسل به کار میرود.

 

گزینه Comment: سلول های حاوی کامنت را انتخاب میکند.

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

گزینه Formulas: سلول های حاوی فرمول را انتخاب میکند، اگر تنها گزینه ی Number انتخاب شده باشد، سلول های حاوی فرمول که خروجی آنها عدد باشد انتخاب میگردد. گزینه ی Text، سلول های حاوی فرمول که خروجی آنها داده متنی باشد را انتخاب میکند. گزینه Logical سلول های حاوی فرمول که خروجی آنها داده منطقی باشد را انتخاب میکند و در نهایت گزینه Error سلول ها حاوی فرمول که خروجی آنها خطا باشد را انتخاب مینماید. از این گزینه میتوان برای مشخص کردن سلول های حاوی فرمول در بحث رفع اشکال فرمول ها در اکسل استفاده نمود.

گزینه Blanks: سلول های خالی را انتخاب میکند. از این گزینه، پس از انتخاب سلول های خالی، میتوان با کلیک روی دکمه های Delete Row یا Delete Column در منوی Delete ، گروه Cells و در تب Home برای Delete کردن سطر ها و ستون ها استفاده کرد.

گزینه Current Region: سلول های محدوده (Area) مربوط به سلول انتخاب شده را انتخاب میکند، برای انتخاب تمام سلول های یک جدول یا یک بانک داده از این گزینه میتوان استفاده نمود.

گزینه Objects: تمام اشیا را انتخاب میکند، مانند شکل ها، نمودار ها، Smart Art ها و غیره.

 

گزینه Row Differences: فرض کنید تعدادی سطر داده داریم که در هریک از آنها فرضاً سه ستون قرار دارد، فرض کنیم داده های این سه ستون قرار است یکسان باشند (یعنی هر سطر داده های یکسان داشته باشد)، ، شما میخواهید آنها را با هم مقایسه کنید و داده های متفاوت را مشخص نمایید. برای پیدا کردن و انتخاب داده های غیر یکسان در هر سطر، کافیست از گزینه Row Difference در پنجره Go To Special استفاده کنید. قبل باز کردن پنجره Go To Special محدوده مورد نظر را انتخاب کنید.

گزینه Column Differences: فرض کنید تعدادی ستون داده داریم که در هریک از آنها فرضاً سه سطر قرار دارد، فرض کنیم داده های این سه سطر قرار است یکسان باشند (یعنی هر ستون داده های یکسان داشته باشد)، شما میخواهید آنها را با هم مقایسه کنید و داده های متفاوت را مشخص نمایید. برای پیدا کردن و انتخاب داده های غیر یکسان در هر ستون، کافیست از گزینه Column Difference در پنجره Go To Special استفاده کنید. قبل باز کردن پنجره Go To Special محدوده مورد نظر را انتخاب کنید.

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

گزینه Dependents: تمام سلول هایی که در آنها از سلول فعال استفاده شده است را انتخاب میکند، با انتخاب گزینه Direct Only، تنها سلول هایی که سلول فعال مستقیماً در آنها استفاده شده است انتخاب میگردند و با انتخاب گزینه All Levels تمام سلول هایی که در فرمول نوشته شده در آنها (مستقیم و غیر مستقیم) سلول فعال استفاده گردیده انتخاب میگردند.

گزینه Last Cell: آخرین سلول محدوده استفاده شده در اکسل را انتخاب میکند.

گزینه Visible Cells Only: سلول های Visible را انتخاب میکند، یعنی سطر ها و ستون های Hide شده را انتخاب نمیکند. این گزینه زمانی کار میکند که سطرها و ستون های Hide شده وجود داشته باشد.

گزینه Conditional Formats: سلول هایی که ابزار Conditional Formatting در مورد آنها به کار رفته انتخاب میکند.

گزینه Data Validation: سلول هایی که ابزار Data Validation برای آنها استفاده شده است را انتخاب میکند. انتخاب گزینه Same به ما این امکان را میدهد که سلول هایی را که Data Validation در آنها با محدودیتی مشابه محدودیت به کار رفته برای سلول فعال استفاده شده است را انتخاب کنیم و گزینه All تمام سلول هایی که این ابزار در آنها استفاده شده است را انتخاب میکند.