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

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

برای یافتن سل­های دارای خطا در یک کاربرگ اکسل، گزینه Find & Select در قسمت Editing منوی Home انتخاب و از منوی باز شده، گزینه Go to Special برگزیده می­شود. از پنجره نمایش­یافته، گزینه Formulas  و از گزینه­ های آن Errors انتخاب می­شود. سپس، با فشردن دکمه Ok به اولین سلول  دارای خطا منتقل می­شویم.

1- ابتدا ستون یا محدوده مورد نظر خود را انتخاب کنید.

2- در سربرگ Home از   Excel  (به این نوار در Excel 2007 به بعد Ribbon می‌گویند) گزینه Conditiona Formatting را بزنید.

3- در لیست باز شده، گزینه Highlight Cell Ruls را بزنید.

4- در این لیست گزینه Duplicate Value  را بزنید.

5 – در پنجره آخر، از شما پرسیده می‌شود که سلولهای تکراری چه رنگی شوند.

جلوگیری از ورود اعداد یا مقادیر تکراریدر اکسل

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

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

ابتدا ستون مورد نظر را انتخاب نمایید.

1- دقت نماید که آدرس سلول فعال شما چیست، مثلادر شکل زیر سلول فعال A1 است.

2- از Ribbon به سربرگ Data بروید و سپس گزینه Validation را اتابع  COUNTIF(A:A,A1) در اکسل مشخص می کند که در ستون A، چند بار مقادیر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد، که تکراری نشود.                                                      3-تابع  COUNTIF در اکسل مشخص می کند که در ستون A، چند بار مقادیر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد، که تکراری نشود.

برنامه اکسل به دلیل قدرت بالایی که دارد، قابلیت های فراوانی را در اختیار کاربران با سلایق متفاوت می دهد.

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

مرحله اول:

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

مرحله دوم:

ابتدا سلولی که میخواهید منوی کشویی شود را انتخاب و از سربرگ Data  گزینه Data validation  را انتخاب و Data Validation… را اجرا نمایید.

مرحله سوم:

در پنجره باز شده منوی کشویی Allow را بر روی List  قرار داده و از قسمت Source  بر روی روی Icon  انتخاب سلول  کلیک نمایید.

مرحله چهارم:

سلول هایی که محتویات کشویی شما هست را انتخب نمایید و بر روی close پنجره انتخاب کلیک نمایید.

مشاهده می کنید که در منوی Source سلول های انتخابی شما قرار دارد. حال بر روی OK کلیک نمایید.

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

(اینکار به راحتی از طریق office button>Prepare>Encrypt document و سپس دادن پسورد امکانپذیر است.)
می توان از worksheet ها و سلولهای خاص و نمودارها، اشیای گرافیکی، نمودارها، سناریو ها، پنجره ها را نیز محافظت کرد.
فرض کنید در برخی سلول ها فرمول دارید و نمی خواهید کسی به آنها دسترسی داشته باشد و یا فرمول شما را بببیند و یا تغییر دهید، بنابراین نیاز دارید که سلول های خاصی را غیرقابل ویرایش یا غیر قابل انتخاب یا غیرقابل رویت نمایید.



محافظت سلولها در برابر تغییرات در اکسل
1- قفل کردن یا از حالت قفل خارج کردن (lock / unlock) سلولهای مورد نظردر شیت

به صورت پیش فرض تمامی سلولهای یک شیت بصورت قفل شده هستند. و تنها لازم است که سلولهای مورد نظر خود را از این حالت خارج کنیم. برای اینکار لازم است مراحل زیر صورت گیرد:


ناحیه مورد نظر یا سلول مورد نظر را انتخاب کنید و کلیک راست کنید.

به مسیر format cell>protection>lock بروید

تب lock * را تیک بزنید.

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


2- انجام عملیات محافظت شیت (Protect Sheet)در excel
در excel 2007 برای اینکار باید ابتدا به منوی review بروید.(در اکسل 2003 به منوی tools بروید) protect sheet را انتخاب کنید

برای اینکه بعدا بتوانید از حالت protect sheet(unprotect)خارج شوید می توانید پسورد بگذارید
گزینه هایی در زیر عبارت Allow all user of this worksheet وجود دارد که عملیات را درباره سلولهای قفل شده نمایش داده می شود و می توانید آنها را هم با توجه به سلولهای قفل شده یا نشده انتخاب کنید و کاربر را محدود کنید.

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

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


به مسیر review>protection> allow users to edit range می رویم.(در excel 2003 اینکار از منوی tools امکانپذیر است)

با استفاده از گرینه new مناطقی را که میخواهیم اضافه می کنیم

و درنهایت password *میدهیم

هنوز کار تمام نشده و مثل حالت قبلی باید sheet * را محافظت کنیم.

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

محافظت فایلهای اکسل workbook می توانید review /protection/protect workbook (در اکسل 2003 tools/protection/protect را انتخاب کنید تا از فایلهای اکسل نیز محافظت کنید


محافظت از یک workbook با استفاده از کلمات عبور
وقتی که protect workbook, protect sheetیاprotect and share workbook را انتخاب می کنید یک کلمه عبور برای غیرفعال کردن محافظت(unprotect) باید استفاده شود. می توانید از تنها ازی یک کلمه عبور برای هر workbook یا worksheet که محافظت کرده اید استفاده کنید.
قفل کردن یک workbook در 2 حالت structure و window انجام می گیرد




در حالت structure قابلیتهای زیر(درو اقعی بیشتر عملیات کار با شیت) از یک شیت گرفته می شود:



جابجا کردن sheet
تغییر نام و رنگ تب sheet
حذف و اضافه کردن sheet
hide و unhide کردن sheet



در حالت window protect کلید close (ضربدر) بالای worksheet حذف می شود

1- صرفه‌جویی در زمان با استفاده از الگو‌های آماده

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

ایجاد الگو‌هایی که به آن‌ها نیاز دارید، بسیار ساده است.  اکسل قالب‌های متنوع بسیاری را برای کار روی تقویم‌ها، برنامه‌ریزی و اوراق زمان‌بندی و همچنین انتقال وجوه در اختیار کاربران قرار داده است. Vertex42 می‌تواند انتخابی مناسب برای برنامه‌ریزی، بودجه‌بندی، لیست کارها و ترازنامه‌ها باشد. یکی دیگر از منابع مناسب Spreadsheet 123 است که به‌منظور ایجاد فاکتور‌ها، موجودی، اوراق ثبت‌نام و حضوروغیاب مورد استفاده قرار می‌گیرد.

2- ثابت کردن عنوان سطر‌ها و ستون‌ها

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

1- در تب View، گزینه‌ی Freeze Panes را در قسمت مربوط به ribbon انتخاب کنید.

2- در منوی کشویی مربوط به Freeze Panes، Freeze Top Row و سپس Freeze First Column را انتخاب کنید. در صورت لزوم می‌توانید هر دو گزینه‌ را انتخاب کنید.

پس از فعال‌سازی این ویژگی، هر وقت که صفحه را به سمت بالا، پایین، راست یا چپ، پیمایش می‌کنید، عنوان هر یک از ستون‌ها قابل مشاهده خواهد بود. برای غیر فعال کردن این ویژگی، تنها کافی است پس از انتخاب گزینه‌ی Freeze Panes روی عبارت Unfreeze Panes، کلیک کنید.

توجه داشته باشید که در نسخه‌های قدیمی‌تر Excel، روش کار کمی متفاوت است. در این نسخه‌ها باید سلول مشترک بین سلول‌های مربوط به سطر و ستونی که می‌خواهید ثابت بماند، انتخاب کنید و گزینه‌ی Freeze Panes را بزنید.

3- شماره‌گذاری کردن یک ستون در excel

شماره‌گذاری و دسته‌بندی یک ستون در اکسل تا حدود زیادی وقت‌گیر است؛ به‌خصوص اگر بخواهید برای چندین ستون این کار را تکرار کنید. با استفاده از این روش می‌توانید تا حدود زیادی در وقت خود صرفه‌جویی کنید و به‌سرعت لیستی از اعداد ایجاد کنید. به این ترتیب که عدد 1 و سپس 2 را در سلول‌‌های مورد نظر و زیر هم وارد کنید. در ادامه هر دو سلول را انتخاب کنید و پس از مشاهده‌ی آیکن مربوط به fill handle، کافی است با استفاده از کشیدن ماوس، خانه‌های مورد نظر خود را انتخاب کنید.

هنگامی که از این ویژگی برای واردکردن تاریخ استفاده می‌کنید، می‌توانید به‌سادگی یک ستون یا سطر را به‌صورت افزایشی و تنها با واردکردن تاریخ اولیه، پر کنید. مثلا، می‌توانید تاریخ 16/25/12 را در یک سلول وارد و آن را انتخاب کنید، هنگامی که آیکون مربوط به fill handle ظاهر شد، تعداد سلول‌های مورد نظر خود را انتخاب کنید. از این ویژگی می‌توانید برای ورود اسامی روز‌های هفته و همچنین ماه‌های سال در سلول‌های یک سطر یا ستون نیز استفاده کنید.

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

4- جابه‌جا کردن سطر‌ها و ستون‌ها در اکسل پیشرفته

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

1- سلول‌های حاوی عناوین ستون ها را انتخاب کنید.

2- سپس روی آن‌ها راست کلیک کنید و عبارت Copy را انتخاب کنید. همچنین می‌توانید با رجوع به تب Home در قسمت ribbon، دکمه‌ی کپی را انتخاب کنید.

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

4- راست کلیک کنید و Paste Special را انتخاب کنید. به‌علاوه می‌توانید در تب Home قسمت ribbon، روی عبارت Paste و پس از آن Paste Special، کلیک کنید.

5- چک باکس مربوط به Transpose را انتخاب و روی کلید OK کلیک کنید.

5- دسترسی به ماشین حساب در اکسل

علاوه بر این‌که ممکن است از یک افزونه‌ی مربوط به ماشین‌حساب در نوار کناری صفحه استفاده کنید، می‌توانید از ویژگی مربوط به ماشین‌حساب نیز بهره ببرید. این ویژگی در مواردی کاربرد دارد که نیاز دارید محاسبات بدون فرمول را به‌سرعت انجام دهید. شما می‌توانید به دو روش ماشین‌حساب را به صفحه‌ی خود اضافه کنید؛ یکی با استفاده از ribbon و دیگری با استفاده از نوار ابزار Quick Access.

برای اضافه کردن ماشین‌حساب در مکان مورد نظر خود؛ از منوی فایل، گزینه‌ی Options را انتخاب کنید. سپس یکی از دو گزینه‌ی Customize Ribbon یا Quick Access Toolbar را بر اساس جایی که می‌خواهید از آن استفاده کنید، انتخاب کنید. در قسمت Choose commands from موجود در لیست کشویی، All Commands را انتخاب کنید. لیست را به طرف پایین پیمایش و Calculator را انتخاب کنید و برای درج آن در نوار ابزار دکمه‌ی Add را بزنید. توجه داشته باشید که اگر بخواهید آن را در ribbon اضافه کنید، باید گروهی سفارشی ایجاد و ماشین‌حساب را در آنجا اضافه کنید.

6- لینک کردن به برگه‌ها و صفحه‌ها در EXCEL

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

• سلول حاوی اطلاعاتی را که می‌خواهید به‌عنوان لینک از آن استفاده کنید، انتخاب کنید.
• راست کلیک کنید و Hyperlink را انتخاب کنید، به‌علاوه می‌توانید به تب Insert بروید و روی Hyperlink در قسمت ribbon کلیک کنید.
• در پنجره‌ی بازشده Place in this Document را انتخاب کنید.
• سپس عبارتی را که می‌خواهید در آن سلول نمایش داده شود، به همراه مرجع آن و صفحاتی در گزارش که شما به اطلاعات آن لینک کرده‌اید، مشخص کنید.
• کلید OK را بزنید

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

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

• F4 (‌یا Fn+F4‌) جهت تکرار آخرین دستوری که برای تغییر قالب بندی جمله مثل رنگ یا فونت به کار رفته است.
• Alt + H,E, A  جهت حذف محتویات، قالب‌ها و سایر داده‌های متصل به سلول‌های انتخاب‌شده مورد استفاده قرار می‌گیرد.
• Ctrl + W، برای بستن یک صفحه و Ctrl + O برای باز کردن یک صفحه جدید استفاده می‌شود.
• از Ctrl + Shift + Colon برای واردکردن زمان و از Ctrl + Shift + Semicolon برای ورود تاریخ جاری سیستم استفاده می‌شود.
• Shift + F9 جهت محاسبه‌ی کاربرگ فعال مورد استفاده قرار می‌گیرد.
• Shift + Tab برای انتقال به سلول یا گزینه‌ی قبلی استفاده می‌شود.
• Ctrl + Home جهت انتقال به ابتدای صفحه، مورد استفاده قرار می‌گیرد.
• Ctrl + Page Down برای انتقال به صفحه‌ی بعد و Ctrl + Page Up برای حرکت به صفحه‌ی قبل استفاده می‌شود.

به نظر شما کدام‌یک از این ترفند‌ها کاربردی‌تر است؟

از آنجا که همه‌ی ما از مایکروسافت اکسل استفاده می‌کنیم، با استفاده از این ترفند‌های به‌ظاهر کوچک می‌توانید تا حدود زیادی در زمان صرفه‌جویی کنید.

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

در ادامه خواهیم گفت چگونه این کار قابل انجام است…

ابتدا در یک سلول تاریخ میلادی را وارد نمایید.(برای درج تاریخ جاری میلادی می توانید از تابع Now() استفاده کرده یا با زدن کلید ترکیبی Ctrl+; تاریخ جاری را در سلول انتخاب شده درج نماییم.)

اکنون سلول مورد نظر را انتخاب کرده و پس از کلیک راست روی آن گزینه Format Cells را انتخاب نمایید.(همچنین می توانید با زدن کلید ترکییبی Ctrl+1 وارد این قسمت شوید)

در پنجره فرمت سول ل نمایش داده شده در تب Number گزینه Custom را انتخاب کرده و در قسمت سمت راست پنجره در کادر Type عبارت dddd را تایپ نمایید.

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

dddd [$ -0429]

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

dddd [$ -LCID Hex Code]

LCID Hex Code در حقیقت یک لوکال کد هست که مایکروسافت برای هر کشور در نظر گرفته است. پس در کد فوق فقط کافیه بجای این عبارت کد کشور مورد نظر رو وارد کنید تا روز رو به زبان اون کشور ببینید

توجه کنید این کد می تواند بسیار مفید باشد چون وقتی شما فقط dddd رو در کد وارد می نمایید نمایش روز به زبان سیستم شخص استفاده کننده بستگی دارد و اگر در سیستمی زبان دیگری تنظیم شده باشد به همان زبان روز نشان داده می شود اما وقتی ما میخواهیم روز فقط به زبان مورد نظر ما نشTextان داده شود حتی اگر زبان پیش فرض سیستم شخص دیگر استفاده کننده زبانی غیر از آنچه ما میخواهیم باشد با استفاده از کد فوق می توانیم مطمئن باشیم که فقط به زبانی که ما میخواهیم نمایش داده خواهد شد.

مورد دیگری که می توان از این کد استفاده کرد در تابع Text می باشد. به این صورت که کد مورد نظر را به صورت زیر درون تابع text قرار می دهیم:

=text(a1,”dddd [$ -0429]”)                                            

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

برای این کار کافی است کلید Ctrl را نگه داشته و با کلیک ماوس، کاربرگهای بعدی را انتخاب کنید. پس از اضافه شدن دومین کاربرگ، عبارت [Group] در عنوان فایل ظاهر می گردد که نشان دهند فعال شدن قابلیت فوق است.

همچنین جهت انتخاب کلیه کاربرگها می بایست روی یک کاربرگ کلیک راست کرده و گزینه Select all sheets را انتخاب کنید.

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

** توجه شود که در گروه بندی در excel، کلیه تغییرات بر تمامی کاربرگهای انتخاب شده اعمال می گردد. لذا می بایست به بخش عنوان برنامه توجه شود که حالت Group فعال است یا خیر؟.                               مزایای استفاده از ماکرو در اکسل                                   

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

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

1- صرفه‌جویی در زمان با استفاده از الگو‌های آماده در excel

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

ایجاد الگو‌هایی که به آن‌ها نیاز دارید، بسیار ساده است. وب‌سایت مایکروسافت، قالب‌های متنوع بسیاری را برای کار روی تقویم‌ها، برنامه‌ریزی و اوراق زمان‌بندی و همچنین انتقال وجوه در اختیار کاربران قرار داده است. Vertex42 می‌تواند انتخابی مناسب برای برنامه‌ریزی، بودجه‌بندی، لیست کارها و ترازنامه‌ها باشد. یکی دیگر از منابع مناسب Spreadsheet 123 است که به‌منظور ایجاد فاکتور‌ها، موجودی، اوراق ثبت‌نام و حضوروغیاب مورد استفاده قرار می‌گیرد.

2- ثابت کردن عنوان سطر‌ها و ستون‌ها

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

1- در تب View، گزینه‌ی Freeze Panes را در قسمت مربوط به ribbon انتخاب کنید.

2- در منوی کشویی مربوط به Freeze Panes، Freeze Top Row و سپس Freeze First Column را انتخاب کنید. در صورت لزوم می‌توانید هر دو گزینه‌ را انتخاب کنید.

پس از فعال‌سازی این ویژگی، هر وقت که صفحه را به سمت بالا، پایین، راست یا چپ، پیمایش می‌کنید، عنوان هر یک از ستون‌ها قابل مشاهده خواهد بود. برای غیر فعال کردن این ویژگی، تنها کافی است پس از انتخاب گزینه‌ی Freeze Panes روی عبارت Unfreeze Panes، کلیک کنید.

توجه داشته باشید که در نسخه‌های قدیمی‌تر Excel، روش کار کمی متفاوت است. در این نسخه‌ها باید سلول مشترک بین سلول‌های مربوط به سطر و ستونی که می‌خواهید ثابت بماند، انتخاب کنید و گزینه‌ی Freeze Panes را بزنید.

3- شماره‌گذاری کردن یک ستون در EXCEL

شماره‌گذاری و دسته‌بندی یک ستون در اکسل تا حدود زیادی وقت‌گیر است؛ به‌خصوص اگر بخواهید برای چندین ستون این کار را تکرار کنید. با استفاده از این روش می‌توانید تا حدود زیادی در وقت خود صرفه‌جویی کنید و به‌سرعت لیستی از اعداد ایجاد کنید. به این ترتیب که عدد 1 و سپس 2 را در سلول‌‌های مورد نظر و زیر هم وارد کنید. در ادامه هر دو سلول را انتخاب کنید و پس از مشاهده‌ی آیکن مربوط به fill handle، کافی است با استفاده از کشیدن ماوس، خانه‌های مورد نظر خود را انتخاب کنید.

هنگامی که از این ویژگی برای واردکردن تاریخ استفاده می‌کنید، می‌توانید به‌سادگی یک ستون یا سطر را به‌صورت افزایشی و تنها با واردکردن تاریخ اولیه، پر کنید. مثلا، می‌توانید تاریخ 16/25/12 را در یک سلول وارد و آن را انتخاب کنید، هنگامی که آیکون مربوط به fill handle ظاهر شد، تعداد سلول‌های مورد نظر خود را انتخاب کنید. از این ویژگی می‌توانید برای ورود اسامی روز‌های هفته و همچنین ماه‌های سال در سلول‌های یک سطر یا ستون نیز استفاده کنید.   

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

4- جابه‌جا کردن سطر‌ها و ستون‌ها

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

1- سلول‌های حاوی عناوین ستون‌ها را انتخاب کنید.

2- سپس روی آن‌ها راست کلیک کنید و عبارت Copy را انتخاب کنید. همچنین می‌توانید با رجوع به تب Home در قسمت ribbon، دکمه‌ی کپی را انتخاب کنید.

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

4- راست کلیک کنید و Paste Special را انتخاب کنید. به‌علاوه می‌توانید در تب Home قسمت ribbon، روی عبارت Paste و پس از آن Paste Special، کلیک کنید.

5- چک باکس مربوط به Transpose را انتخاب و روی کلید OK کلیک کنید.

5- دسترسی به ماشین حساب

علاوه بر این‌که ممکن است از یک افزونه‌ی مربوط به ماشین‌حساب در نوار کناری صفحه استفاده کنید، می‌توانید از ویژگی مربوط به ماشین‌حساب نیز بهره ببرید. این ویژگی در مواردی کاربرد دارد که نیاز دارید محاسبات بدون فرمول را به‌سرعت انجام دهید. شما می‌توانید به دو روش ماشین‌حساب را به صفحه‌ی خود اضافه کنید؛ یکی با استفاده از ribbon و دیگری با استفاده از نوار ابزار Quick Access.

برای اضافه کردن ماشین‌حساب در مکان مورد نظر خود؛ از منوی فایل، گزینه‌ی Options را انتخاب کنید. سپس یکی از دو گزینه‌ی Customize Ribbon یا Quick Access Toolbar را بر اساس جایی که می‌خواهید از آن استفاده کنید، انتخاب کنید. در قسمت Choose commands from موجود در لیست کشویی، All Commands را انتخاب کنید. لیست را به طرف پایین پیمایش و Calculator را انتخاب کنید و برای درج آن در نوار ابزار دکمه‌ی Add را بزنید. توجه داشته باشید که اگر بخواهید آن را در ribbon اضافه کنید، باید گروهی سفارشی ایجاد و ماشین‌حساب را در آنجا اضافه کنید.

6- لینک کردن به برگه‌ها و صفحه‌ها

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

• سلول حاوی اطلاعاتی را که می‌خواهید به‌عنوان لینک از آن استفاده کنید، انتخاب کنید.
• راست کلیک کنید و Hyperlink را انتخاب کنید، به‌علاوه می‌توانید به تب Insert بروید و روی Hyperlink در قسمت ribbon کلیک کنید.
• در پنجره‌ی بازشده Place in this Document را انتخاب کنید.
• سپس عبارتی را که می‌خواهید در آن سلول نمایش داده شود، به همراه مرجع آن و صفحاتی در گزارش که شما به اطلاعات آن لینک کرده‌اید، مشخص کنید.
• کلید OK را بزنید

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

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

• F4 (‌یا Fn+F4‌) جهت تکرار آخرین دستوری که برای تغییر قالب‌بندی جمله مثل رنگ یا فونت به کار رفته است.
• Alt + H,E, A  جهت حذف محتویات، قالب‌ها و سایر داده‌های متصل به سلول‌های انتخاب‌شده مورد استفاده قرار می‌گیرد.
• Ctrl + W، برای بستن یک صفحه و Ctrl + O برای باز کردن یک صفحه جدید استفاده می‌شود.
• از Ctrl + Shift + Colon برای واردکردن زمان و از Ctrl + Shift + Semicolon برای ورود تاریخ جاری سیستم استفاده می‌شود.
• Shift + F9 جهت محاسبه‌ی کاربرگ فعال مورد استفاده قرار می‌گیرد.
• Shift + Tab برای انتقال به سلول یا گزینه‌ی قبلی استفاده می‌شود.
• Ctrl + Home جهت انتقال به ابتدای صفحه، مورد استفاده قرار می‌گیرد.
• Ctrl + Page Down برای انتقال به صفحه‌ی بعد و Ctrl + Page Up برای حرکت به صفحه‌ی قبل استفاده می‌شود.

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

• فعال‌سازی ToolPakدر اکسل
  

این افزونه بصورت پیش فرض در برنامه Excel غیر فعال است. جهت فعال‌سازی آن نشانگر را روی Ribbon برده و راست کلیک نمائید و از پنجره باز شده گزینه Customize The Ribbon را انتخاب نمائید.

پس از باز شدن پنجره Excel Option روی تب Add-Ins کلیک کنید و از لیست افزونه‌ها Analysis ToolPak را انتخاب و گزینه …Go را کلیک کنید.

به این ترتیب پنجره‌ای  باز می‌شود که برای فعال‌سازی افزونه‌ها می‌باشد. از این پنجره تیک Analysis ToolPak را فعال و گزینه OK را انتخاب نمائید.

به این صورت در تب DATA قسمت Analysis گزینه Data Analysis اضافه می‌گردد.

• صورت مسئله

فرض کنید در کسب و کارتان هر ماه مقدار درآمد ناخالص خود را بصورت جدول زیر تهیه می‌نمائید (که فرض یقین است!).

افزونه Data Analysis بصورت خودکار نمودارها را ترسیم می‌کند ولی با این حال روش ترسیم متداول نمودار بصورت زیر می‌باشد.

جهت ترسیم نمودار برای جدول، بسادگی زیر عمل کنید:

• کل بازه اعداد را Select نمائید (از 1 تا 400).

• از تب Insert بخش Chart، حالت Scatter را انتخاب نمائید.

نمودار داده‌های شما بصورت زیر ترسیم می‌گردد.

• نحوه میانه‌یابی در excel

در این مرحله قصد داریم میانگین درآمد 2، 3 و 6 ماهه برای نمودار درآمدی سال 94 ترسیم گردد. از تب DATA بخش Analysis گزینه Data Analysis را انتخاب کنید. در پنجره باز شده نوع تحلیل Moving Average را انتخاب و OK کنید تا پنجره زیر نمایش داده شود.

در باکس Input Range باید آدرس ستون داده‌های درآمد وارد شود (C4:C15) و در باکس Output Range آدرس یک ستون خالی که محاسبات افزونه در آن جای گیرد (مثلا D4 15). باکس Interval بازه میانگین‌گیری را نیاز دارد که اعداد 2، 3 و 6 ماه بترتیب باید داده شود. تعداد ماهی که شما برای بازه تعریف می‌نمایید معادل شماره ماهی است که از آن به بعد محاسبات صورت می‌گیرد. مثلا بازه 3 ماهه یعنی میانگین 3 ماه قبل پس در ماه دوم سال نتیجه نخواهد داشت. در آخر تیک Chart Output را فعال نمائید تا نمودارها را برای شما ترسیم گردد. نتیجه بصورت زیر حاصل می‌گردد.

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

• اضافه کردن نوار خطا برای یک نمودار

مرحله 1: روی نمودار کلیک کنید تا بخش جدید Chart Tools بالای تب‌های اکسل ظاهر شود.

مرحله 2: از تب‌های جدید موجود در این بخش، در تب Layout، بخش Analysis روی دکمه Error Bars کلیک کنید.
مرحله 3: مطابق شکل فوق، 5 گزینه وجود دارد:

1- گزینه None: این گزینه زمانی کاربرد دارد که بخواهید نوار خطای موجود را حذف کنید.

2- گزینه Error Bar with Standard Error: مشاهده نوار خطا با استفاده از خطای استاندارد (Standard Error)

3- گزینه Error Bars with Percentage: مشاهده نوار خطا با مقدار خطای پیش‌فرض 5 درصد (Percentage) که البته قابل تغییر است.

4- گزینه Error Bars with Standard Deviation: مشاهده نوار خطا با انحراف معیار پیش‌فرض 1 (Standard Deviation) که البته قابل تغییر است.

5- گزینه More Error Bars Options: این گزینه برای مشاهده و تغییر تنظیمات نوار خطا استفاده می‌شود.

تنظیمات نوار خطا در EXCEL
به منظور انجام تنظیمات دلخواه در مراحل طی شده انتهای لیست قبل، روی گزینه More Error Bars Options کلیک کنید تا پنجره Format Error Bars باز شود.

در قسمت بالای این پنجره یعنی Displayمی‌توانید مشخص کنید که آیا نوار خطا دارای دو جهت باشد یا تک جهته به سمت منفی یا مثبت ترسیم شود.
در قسمت End Styleمی‌توانید مشخص کنید انتهای خط نوار خطا کلاهک داشته باشد (Cap) یا نداشته باشد (No Cap).

در قسمت Error Amount، اگر میزان خطا در تمام نقاط مقداری ثابت باشد، می‌توانید از گزینه Fixed value استفاده کنید.

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

اما از آنجایی که معمولا برای هر نقطه، خطای مجزایی وجود دارد برای وارد کردن این خطاها باید از گزینه آخر یعنی Custom استفاده شود. پس از انتخاب این گزینه روی دکمه Specify Value کلیک کنید تا پنجره Custom Error Bars باز شود. اکنون پس از تعیین مقادیر مثبت و منفی خطا (positive/Negative error value)، روی OK کلیک کنید.

نحوه تعیین مقادیر مثبت و منفی خطا در اکسل:

اگر فرض کنیم مطابق داده‌های زیر در یک دمای خاص، 3 مرتبه فشار یک سیستم اندازه‌گیری شود.

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

به طور مثال در جدول زیر برای دمای برابر با 2، برای تعیین میانگین از فرمول زیر

=AVERAGE(B2 2)

و برای تعیین مقادیر مثبت و منفی خطا از فرمول زیر

=(MA B2 2)-MIN(B2 2))/2

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

بنابراین داده‌های ستون A و E برای رسم نمودار و داده‌های ستون F به عنوان مقادیر خطا در کادرهای positive error value و Negative error value از پنجرهCustom Error Bars در نظر گرفته می‌شود.