ترفند های جالب در اکسل که بهتر است یاد بگیرید

 

Vlookup

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

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

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

ایجاد نمودارها

به منظور ایجاد نمودارها، اطلاعات خود را با سرخط‌های ستونی وارد اکسل کنید و سپس از منوی Insert، گزینه Chart و نهایتا Chart Type را انتخاب کنید. اکسل 2013 مجموعه‌ای از نمودارهای توصیه‌شده را بسته به نوع اطلاعاتی که با آن‌ سروکاردارید پیش روی شما قرار می‌دهد. پس از ایجاد نسخه پیش‌فرض از نمودار می‌توانید از طریق منوی Chart Tools به شخصی‌سازی هرچه بیشتر نمودار یادشده اقدام کنید. مدنظر داشته باشید که در این قسمت نباید هیچ ترسی از سروکله زدن با گزینه‌های مختلف داشته باشید زیرا گزینه‌های شگفت‌انگیز زیادی در این قسمت موجود هستند.

فرمول‌های شرطی

فرمول‌های IF و IFERROR دو فرمول مفید شرطی در اکسل هستند. فرموی IF به شما این امکان را خواهد داد تا از فرمول‌های شرطی که به بررسی درست و یا نادرست بودن یک گزاره می‌پردازند استفاده کنید. به عنوان مثال می‌توانید مشخص کنید که در ستون C جدول، برای دانش‌آموزانی که نمره 80 یا بالاتر کسب‌کرده‌اند از گزینه Pass استفاده شود و دانش‌آموزانی که 79 یا کمتر گرفته‌اند، عبارت Fail در مقابل نامشان ثبت شود.

IFERROR نمونه تغییریافته فرمول IF می‌باشد. این فرمول به شما این امکان را خواهد داد تا مقداری مشخص ( یا مقدار خالی) را در صورتی که فرمول استفاده شده همراه با مشکل بود برای شما بازگردانی کند. به عنوان مثال در صورتی که از فرمول Vlookup استفاده می‌کنید، در صورت وجود نداشتن مرجع، می‌توانید از فرمول IFERROR به منظور بازگردانی یک خانه خالی در جدول بهره ببرید.

PivotTables

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

به منظور ایجاد دستی PivotTables پیش از هرچیز اطمینان حاصل کنید که اطلاعات مورد نظر شما به طور صحیح عنوان‌گذاری شده‌اند. حال به سربرگ Insert رفته و گزینه PivotTables را انتخاب و نهایتا محدوده اطلاعات مورد نظر خود را مشخص کنید. نیمه بالایی از منوی سمت راست شامل تمامی فیلدهای در دسترس شما خواهد بود و در نیمه پایینی نیز فضای مورد نیاز برای استفاده به منظور تولید و ساخت جدول‌ها را در اختیار خواهید داشت.

برای مثال به منظور شمارش تعداد دانش‌آموزانی که قبول و یا رد شده‌اند کافی است ستون Pass/Fail را در سربرگ Row Lables قرار دهید و مجددا آن را در قسمت Values در PivotTables خود قراردهید. پس از آن معمولا ادامه عملیات براساس داده‌ها انجام خواهد شد با این وجود می‌توانید از میان عملیات پیش‌فرض موجود در پنجره بازشونده Values، تابع و نوع عملیات مورد نظر خود را انتخاب کنید. همچنین می‌توانید زیرجدول‌هایی که اطلاعات را بر اساس دسته‌های دیگر ( به عنوان مثال جنسیت افراد قبول و یا ردشده) دسته‌بندی می‌کنند نیز ایجاد کنید.

PivotChart

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

در اکسل 2013 شاهد ارائه PivotChartهای پیشنهادی هستیم که می‌توانید آن‌ها را در زیرمجموعه Recommended Charts در قسمت Chart از منوی Insert بیابید. می‌توانید پیش‌نمایشی از نمودار را با نگه‌داشتن ماوس بر روی آن مشاهده کنید. همچنین می‌توانید شخصا اقدام به ایجاد یک PivotChart کنید.

Flash Fill

یکی از بهترین قابلیت‌های جدید در اکسل 2013، Flash Fill، یکی از مشکلات موجود در اکسل را به خوبی مرتفع نمود. پیش از این استخراج قسمتی از اطلاعات موجود در یک سلول ترکیبی همواره دردسرهای خاص خود را داشت. به عنوان مثال وقتی که با ستونی حاوی اسامی اشخاص به ترتیب “نام خانوادگی، نام” سروکار دارید به طور سنتی باید به منظور جداسازی داده‌ها (مثلا نام) به تایپ مجدد نام‌ها در ستون‌ها اقدام می‌کردید اما در اکسل 2013 می‌توانید نام اولین شخص را در ستون مجاور ستونی که در آن کار می‌کنید تایپ کرده و سپس با طی کردن مسیر Home>Fill>Flash Fill اکسل به‌طور خودکار، نام اشخاص باقی‌مانده را با این الگوبرداری در جدول شما وارد خواهد کرد.

تحلیل سریع

در اکسل 2013 استفاده از ابزار جدید Quick Analaysis زمان مورد نیاز برای ایجاد نمودارها بسته به مجموعه داده‌های مشخص شده را کاهش می‌دهد. پس از انتخاب داده‌ها، یک آیکون در قسمت پایین سمت راست ظاهر خواهد شد که با کلیک بر روی آن، منویی به‌منظور انجام تحلیل‌های سریع نمایش داده خواهد شد.

در این منو می‌توانید ابزارهایی مانند Formatting,Charts,Totals,Tables,Sparklines را مشاهده کنید. با نگه‌داشتن ماوس بر روی هر یک‌ می‌توانید پیش‌نمایشی زنده از آن‌ها مشاهده کنید.

Power View

Power View روشی ایده‌آل و تعاملی برای کاوش و نمایش مقادیر گسترده اطلاعات موجود در فایل‌های اطلاعاتی جانبی است. به منظور استفاده از این ابزار به منوی Insert مراجعه و گزینه Reports را انتخاب کنید.

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

Conditional Formatting

برای اکثر جداول ایجاد شده، قابلیت Conditional Formatting در اکسل به شما امکان مشخص‌کردن و ایجاد تمایز در داده‌های مورد علاقه شما را می‌دهند. این قابلیت را می‌توانید از سربرگ Home بیابید. به منظور استفاده از این رویه، ابتدا محدوده سلول‌هایی که می‌خواهید تغییرات را بر روی آن اعمال کنید انتخاب و سپس منوی پایین‌رونده Conditional Formatting را انتخاب کنید. قابلیتی که در این قسمت معمولا بیش از سایر قابلیت‌ها مورد استفاده قرار می‌گیرد، Highlight Cells Rules است.

به عنوان مثالی کاربردی ممکن است نتایج آزمون‌های دانش‌آموزان را در اکسل وارد کرده و قصد داشته باشید اشخاصی که نمره آن‌ها با افتی چشمگیر مواجه بوده را مشخص کنید. با استفاده از فرمت Less Than می‌توانید سلول‌هایی که عدد آن‌ از ویژگی مورد نظر برخوردار بوده را با رنگ قرمز مشخص کنید.

جابجایی سطر و ستون

گاهی اوقات به مجموعه‌ای از اطلاعات در قالب ستون‌های مشخص دسترسی دارید اما بنابر دلایلی نیاز دارید این اطلاعات در قالب سطرها به شما نمایش داده شوند ( و یا بالعکس) به منظور استفاده از این رویه کافی است سطر یا ستون مورد نظر را کپی کرده و سپس بر روی سلول مقصد راست کلیک کرده و گزینه Paste Special را انتخاب کنید. اکنون یک چک‌باکس در قسمت پایین پنجره بازخواهد شد که گزینه Transpose بر روی آن نقش بسته است. اکنون کافی است این باکس را علامت زده و بر روی OK کلیک کنید تا عملیات جابجایی انجام‌گیرد.

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

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

Ctrl+ کلیدهای جهتی پایین/بالا: این عمل شما را به پایین‌ترین/بالاترین سلول ستون فعلی رهنمون خواهد‌کرد.

Ctrl+ کلیدهای جهتی چپ/راست: شما را به دورترین سلول سمت چپ/راست در ردیف جاری رهنمون خواهد‌کرد.

Ctrl+Shift+ کلیدهای جهتی پایین/بالا: کلیه سلول‌های موجود در پایین/بالای سلول فعلی را انتخاب می‌کند.

Shift+F11: یک صفحه‌کاری جدید خالی در ورک‌بوک شما ایجاد خواهدکرد.

F2: سلول‌ها را جهت ویرایش در نوار ابزار فرمول‌ها بازمی‌کند.

Ctrl+Home: شما را به سلول A1 رهنمون خواهدکرد.

Ctrl+End: شما را به آخرین سلولی که حاوی اطلاعات است رهنمون خواهدکرد.

Alt+=: به جمع‌زدن سلول‌های قرارگرفته در بالای سلول فعلی مبادرت می‌ورزد.

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

 

 

کاراکترهای Wildcard در اکسل

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

معرفی کاراکترهای Wildcard در اکسل

با مفهوم ضمیر در دستور زبان آشنا هستیم، به عنوان مثال ضمیر ” او ” در زبان فارسی به معنی یک نفر است و ضمیر “آنها” به معنی چند نفر، میتوان بجای اسم ها از ضمیرهای معادل با آنها استفاده کرد. کاراکترهای Wildcard هم مانند ضمیر عمل میکنند. کاراکتر ” ؟ ” معادل با یک کاراکتر است که میتواند هر کاراکتری باشد و کاراکتر ” * ”   معادل با هر تعداد کاراکتر است که میتوانند هر کاراکتری باشند. کاراکترهای Wildcard در اکسل کاراکترهایی هستند که میتوانند جایگزین کاراکترهای دیگر شوند. از سه کاراکتر * (Asterisk)، ؟ (Question Mark) و ~ (Tilde) به عنوان کاراکترهای Wildcard در اکسل یاد میکنیم.

کاراکترهای Wildcard در اکسل کاربرد گسترده ای در توابع و ابزار ها دارند.

کاراکتر ” ؟ ” در اکسل

یکی از کاراکترهای Wildcard در اکسل کاراکتر ” ؟ ” یا Question Mark میباشد. این کاراکتر میتواند جایگزین یک کاراکتر دیگر (هریک از کاراکترهای استاندار ASCII) باشد، فرض کنید یک فایل اکسل حاوی فهرستی طولانی از اسامی داشته باشیم، با استفاده از ابزار Find & Replace به راحتی میتوانیم هر اسمی را در این فهرست جستجو نماییم. در این فهرست به دنبال نام Hamed Ghadimi میگردیم، اما در این فهرست این نام ممکن است به هریک از حالت های زیر وارد شده باشد:

Hamed-Ghadimi

Hamed_Ghadimi

Hamed Ghadimi

Hamed/Ghadimi

مشخص است که اگر ما عبارت Hamed Ghadimi را جستجو نماییم ممکن است نتیجه مورد نظر را پیدا نکنیم و از طرفی در مورد کاراکتر جدا کننده نام از نام خانوادگی مطمئن نیستیم، یک راه انجام این جستجو استفاده از کاراکترهای Wildcard در اکسل و بالاخص کاراکتر ” ؟ ” میباشد. اگر در پنجره Find & Replace به دنبال عبارت Hamed?Ghadimi بگردیم به این معنی است که به دنبال Hamed و Ghadimi میگردیم که با یک کاراکتر (فقط یک کاراکتر-هر کاراکتری) از هم جدا شده باشند و کاراکتر جدا کننده هر کاراکتری میتواند باشد (Space یا فاصله هم یک کاراکتر میباشد).

کاراکتر ” * ” در اکسل

یکی از کاراکترهای Wildcard در اکسل کاراکتر ” * ” یا Asterisk میباشد. این کاراکتر میتواند جایگزین هر تعداد کاراکتر دیگر (هریک از کاراکترهای استاندار ASCII) باشد، کاراکتر ” * ” میتواند صفر یا چندین کاراکتر باشد، پس طبق مثال بالا اگر ما در فهرستی به دنبال Hamed*Ghadimi نتیجه ی جستجو تمام موارد زیر را شامل میشود:

Hamed-Ghadimi, Hamed_Ghadimi, Hamed Ghadimi, Hamed/Ghadimi,….

HamedGhadimi

Hamed@@@@@@@Ghadimi, Hamed.+_/#@Ghadimi,….

به این ترتیب اگر عبارت Hamed* را جستجو نمایید یعنی به دنبال تمام Hamed های فهرست با هر نام خانوادگی هستید و اگر *Hamed* را جستجو نمایید یعنی به دنبال هر متنی شامل Hamed هستید.

کاراکتر ” ~ ” در اکسل

یکی از کاراکترهای Wildcard در اکسل کاراکتر ” ~ ” یا Tilde میباشد. این کاراکتر دو کاربرد دارد اول اینکه در اکسل کاراکتر Tilde همانند کاراکتر ستاره ” * ” (Asterisk) میتواند جایگزین هر تعداد کاراکتر دیگر (هریک از کاراکترهای استاندار ASCII) باشد – کاراکتر ” ~ ” میتواند صفر یا چندین کاراکتر باشد – کاربرد دوم کاراکتر  ~ ”“ اینست که از این کاراکتر برای جستجوی کاراکترهای” * و ? و خود ~ “میتوان استفاده نمود.

در عبارت مورد جستجو در فایل اکسل اگر قبل از کاراکتر Asterisk یک کاراکتر Tilde قرار دهیم به این معنی است که منظور ما از کاراکتر ” * ” خود کاراکتر Asterisk میباشد و نه یک کاراکتر Wildcard. به عنوان مثال اگر شما بخواهید در یک فایل اکسل به دنبال ” * ” بگردید باید در پنجره جستجو عبارت” ~* ” را جستجو نمایید و اگر بخواهید” ؟ ” را پیدا کنید باید عبارت” ~? ” را جستجو نمایید و به همین ترتیب اگر به دنبال” ~ “هستید باید عبارت” ~~ “را جستجو نمایید.

ساخت Combo box با مقادیر متغیر در اکسل 2007

کنترل Combo box ورودی کاربران را در هنگام وارد کردن داده ها کنترل می کند و مقادیر خارج از لیست Combo box را قبول نمی‌کند.                                                                                

Drop Down list در Excel توسط Data Validation برای سلولها انجام می‌شود اما موضوع این مقاله ساخت Combo box ای است که کاربر بتواند به راحتی مقادیر آنرا کم یا زیاد کند و لازم نباشد که به صورت دستی این کار انجام شود به عبارت دیگر بتواند مقادیر لیست Combo box را به راحتی کنترل نماید.
قبل از هر کار ابتدا باید شما با ابزار بسیار مفیدی در Excel 2007 آشنا شوید به نام Table و بدانید که Table یک محدوده پویا ست یعنی Dynamic Range است.
منظور از محدوده پویا در Excel ، آدرس هایی است که با یک نام شناخته می شوند و شما به جای نوشتن آدرس سلول Excel از آن نام استفاده می نمایید.
برای ساخت یک محدوده پویا در Excel 2007 – 2010 از ابزار Table استفاده می‌شود. فرض کنید که در یک شرکت شما 5 انبار دارید که کاربر قرار است در لیست ورود کالا، یکی از انبارهای را انتخاب نماید.
مطابق شکل اسامی انبارها را در یک Sheet جداگانه از  Sheet لیست ورود انبار وارد نمایید و سپس یکی از سلولهای را کلیک کنید و کلید Ctrl+T را بزنید تا این لیست به Table تبدیل شود.                                     بعد از اینکه Table شما در Excel آماده شد باید یک Name تعریف کنید و منبع Name به ستون "نام انبارها" اشاره کند.
برای اینکار از سربرگ Formula گزینه Define Name را بزنید.
در قسمت Name یک اسم دلخواه تعیین کنید مثلا MyStock  (اسم فارسی نگذارید و از Space هم استفاده نکنید).
در قسمت Refer To با دقت بسیار سلولهایی که نام انبارها در آن نوشته شده است را با Drag موس انتخاب کنید . (سر ستون انتخاب نشود)
و در نهایت کار شما باید نتیجه ای مانند شکل را بدهد و OK را بزنید.                                                    به Sheet "لیست کالا" بروید و در صورت تمایل می توانید این لیست را نیز به صورت یک Table در آورد و از مزایای Table استفاده کنید .
سلول یا سلولهای ستون "نام انبار" را انتخاب نمایید.
از سربرگ Data گزینه Validation را بزنید .
در پنجره Data Validation گزینه List را انتخاب نمایید و سپس در قسمت Source این پنجره Name تعریف شده در مرحله قبل را همراه با علامت  = وارد نمایید و کلید OK را بزنید:                                                    حال در سلولهای نام یک Combo Box دارید که اسامی انبارها را نشان می دهد، اگر شما در Table "نام انبارها" گزینه ای را اضافه کنید و یا سطری را Delete نمایید متوجه می شوید که این Combo box خودکار به روز می شود .

روش رمز گذاری برای پروژه های اکسل

                                                                                                                                           اگر شما از کامپیوتر استفاده می کنید احتمال این نیز وجود دارد که چند پرونده اکسل در PC  یا مک خود داشته باشید. همانطور که میدانید میلیون ها نفر در سراسر جهان از اکسل استفاده می کنند که بیش تر بدرد تجارت و نهاد های دولتی استفاده می شود. خوشبخاته با وجود اکسل ۲۰۱۴ امکان بالا بردن امنیت پرونده ها با رمز گذاری وجود دارد.

بریا این کار ابتدا باید پرونده ای را که می خواهید رمز گذاری کنید باز کرده و گزینه Info را انتخاب کنید سپس روی Protect Workbook کلیک نمایید.

از نوار باز شده روی گزینه Encrypt with Password کلیک کنید.

excel-password-2-640×427بلافاصله اکسل پنجره ای برای وارد کردن رمز عبور باز می کند شما می توانید رمز عبور خود را به سادگی در تکست بار باز شده وارد کنید اما به یاد داشته باشید که اگر رمز عبور خود را گم کنید دیگر امکان دسترسی به پرونده شما نیست. اکسل هیچ گزینه ای برای بازیابی رمز عبور وارد شده توسط شما ندارد.

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

 

تغییر نحوه محاسبه از خودکار به دستی فقط برای شیت مشخص

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

همانطور که میدانید در اکسل از طریق تب Formulas و گزینه Calculation Options میشود نحوه محاسبات رابه صورت دستی درآورد اما مشکل اینجاست که با اینکار تمام شیت ها به حالت محاسبات دستی تغییر پیدا میکنند. اگر بخواهیم تنها یک شیت اکسل را به حالت محاسبه دستی تغییر دهیم و نحوه محاسبه در بقیه شیت ها اتوماتیک باشیت کافیست به روش زیر عمل کنیم:

ابتدا روی شیت مدنظر راست کلیک میکنیم و از منویی که باز میشه گزینه View Code را انتخاب میکنیم

 

با اینکار وارد پنجره Visual Basic Editor میشویم. در قسمت چپ گزینه EnableCalculation رو پیدا میکنیم و مقدار آن را برابر با False قرار میدهیم:

 

با اینکار فقط شیت مدنظر ما بصورت محاسبه دستی تبدیل میشود و بقیه شیت ها بصورت محاسبه خودکار باقی میمانند. حالا کافیست که هر بار که نیاز به آپدیت شدن محاسبات شیت داشتیم از تب Formulas گزینه Calculate Sheet را انتخاب کنیم. برای برگرداندن نحوه محاسبات شیت به حالت اتوماتیک هم کافیست دوباره گزینه EnableCalculation را به مقدار True تغییر دهیم.

چگونه خطای فرمول ها را اصلاح كنیم

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

 

نوار ابزار Formula Auditing را باز كنید  

 

 وقتی كه خطاها رخ می دهند، یكی از قدرتمند ترین ابزارها برای اصلاح آنها، نوار ابزار Formula Auditing است. برای باز كردن آن، از منوی Tools ، گزینه Formula Auditing و بعد گزینه Show Formula Auditing Toolbar را انتخاب كنید.

خطاها را مرور كنید 

 برای بررسی هر سلولی كه با مثلث خطا مشخص شده است، روی دكمه Error Checking در نوار ابزار Formula Auditing كلیك كنید،Excel اولین سلول خطا را متمایز می كند و گزینه هایی برای اصلاح آنها در اختیار شما قرار می دهد.

خطا را اصلاح كنید 

 در اینجا، اکسل متوجه شده است كه یك سلول از فرمولی استفاده می كند كه با فرمول‌های سلول‌های پیرامون، متفاوت است. این ممكن است یك خطا باشد یا نباشد. اگر بخواهید می توانید به اکسل بگویید كه این خطا را نادیده بگیرد. برای این كار، روی دكمه Ignore Error كلیك كنید. ولی، این یك خطاست. بنابراین روی Copy Formula from Above كلیك كنید. با كلیك كردن روی Edit in Formula Bar خودتان می توانید فرمول را تغییر دهید

 خطاهای بعدی را نیز اصلاح كنید 

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

آدرس های مرجوعی را دنبال كنید 

تمام اشتباهات، منجر به خطا نمی شوند. اگر چیزی درست به نظر نرسد، (مانند میانگین فروش) می توانید با نمایش دادن آدرس های مرجوعی فرمول، مقادیر مورد استفاده در آن را ردیابی (دنبال) كنید. روی سلولی كه می خواهید ارزیابی كنید كلیك كنید و بعد روی دكمه Trace Precedents كلیك كنید. اگر لازم باشد، دوباره روی این دكمه كلیك كنید تا مقدار این سلول ها را دنبال كنید.

ردیابی را مشاهده كنید

 فلش‌های ردیابی را نمایش می دهد كه به منبع های فرمول اشاره می كنند و به شما كمك می كنند تا خطا را دنبال كنید. در اینجا، من اشتباهاً جمع كل در ستون G را در محدوده هایی كه در تابع AVERAGE بكار بردم وارد كردم! بعد از اینكه خطا را پیدا كردید، اصلاحات لازم را انجام دهید و با كلیك كردن روی Remove All Arrows ، فلش های ردیابی را حذف كنید.
*توجه*

 استفاده از Trace Error

اگر یك مثلث سبزرنگ، وجود خطا در فرمولی كه تایپ كرده اید را نشان دهد، با بردن اشاره گر ماوس به سمت چپ سلول و متمایز كردن دكمه Trace Error ، كلیك كردن روی فلشی كه ظاهر می شود و انتخاب یكی از گزینه ها می توانید فوراً آن را بررسی كنید.
ردیابی وابسته ها

با كلیك كردن روی یك سلول و كلیك كردن روی دكمه Trace Dependents درنوار ابزار Formula Auditing می توانید بررسی كنید كه آیا مقدار آن سلول در یكی از فرمول ها استفاده شده است یا خیر و چگونه.
ارزیابی فرمول

برای ارزیابی یك فرمول، روی دكمه Evaluate Formula در نوار ابزار Formula Auditing كلیك كنید تا یك كادر محاوره ای باز شود كه شما را در تك تك قسمت های فرمول جلو می برد. برای محاسبه قسمت خط كشی شده فرمول، روی Evaluate كلیك كنید. هر اندازه كه لازم است روی Evaluate كلیك كنید.

تابع FORECAST

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

قاعده کلی استفاده از تابع FORECAST اکسل به صورت زیراست:

(محدوده مقادیر متغیر در دوره‌های گذشته، محدوده مقدار تابع برای متغیرهای گذشته، مقدار متغیر که برای آن قصد پیش بینی  تابع را داریم)FORECAST

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

برای اینکار ابتدا باید پارامترهای مختلف تابع FORECAST رادر جدول مشخص کنیم.

محدوده مقادیر متغیر در دوره‌های گذشته:                                                                             از آنجایی  که مقادیر یازده ماه گذشته را داریم پس این پارامتر شامل محدوده A2:A12 یعنی عددهای 1 تا 11 میشود.

محدوده مقدار تابع برای متغیرهای گذشته:                                                                           این محدوده باید مقدار تابع یعنی تعداد فروش رو به ازای عددهای یک تا یازده به ما نشون بده. پس این محدوده رنج B2:B12 را شامل میشود.

مقدار متغیر که برای آن قصد پیش بینی  تابع را دار یم :                                                           میخواهیم مقدار تعداد فروش رو برای ماه 12 حساب کنیم پس مقدار متغیری که ما میخواهیم  پیش بینی کنیم سلول A13 است.

با داشتن اطلاعات بالا فرمول FORECAST ما به شکل زیر در میاد:

FORECAST(A13;B2:B12;A2:A12) 

                                            

روش های کار کردن با خطوط مرزی و الگوها دراکسل

                                                                                                                                       خطوط شبكه ای كه در برگه های كاری Excel مشاهده می كنید كمی گمراه كننده می باشند. معمولاً، این خطوط چاپ نمی شوند و اگر هم آنها را چاپ كنید كمرنگ به نظر می رسند. برای اینكه سلول ها خطوط مرزی داشته باشند از گزینه هایBorder استفاده كنید. می توانید خط مرزی را به یك سلول یا به محدوده ای از سلول ها اضافه كنید. می توانید خط مرزی را در فقط یك سمت سلول قرار دهید و یا پیرامون تمام جهات سلول. اگر خطوط مرزی سلول ها به دلخواه شما نباشند از یك الگوی پس زمینه مانند تركیب رنگ، جلوه ویژه یا رنگ آمیزی استفاده كنید، ولی به یاد داشته باشید كه پس زمینه ای كه زیاد شلوغ باشد، باعث می شود كه خواننده به سختی داده ها را ببیند.

 كادر محاوره ای Format Cells را باز كنید  

 

سلول یا محدوده ای كه می خواهید خط مرزی یا الگو را به آن اضافه كنید انتخاب كنید و از منوی Format ، گزینه Cellsرا انتخاب كنید تا كادر محاوره ای Format Cells باز شود.

روی برگه Border كلیك كنید. از لیستStyle ، یك شیوه انتخاب كنید و از لیستColor برای انتخاب رنگ برای خط مرزی استفاده كنید.

 

 از Presets استفاده كنید  

 

وقتی كه شیوه خط مشخص شد، یكی از گزینه‌های Presets را كه در بالای برگه قرار دارند انتخاب كنید. برای بكاربردن یك خط مرزی حول لبه های خارجی سلول یامحدوده انتخاب شده، روی Outline كلیك كنید. برای بكار بردن خطوط شبكه، داخل سلول‌های داخلی محدوده، رویInside كلیك كنید.

برای سفارشی كردن خط مرزی، از دكمه های Border استفاده كنید و مشخص كنید كه كدام قسمت های سلول های انتخاب شده باید خط مرزی داشته باشند. برای اضافه كردن یك خط مرزی به آن قسمت، روی دكمه مورد نظر كلیك كنید. برای مشاهده نتیجه، به قسمت پیش‌نمایش دقت كنید، در صورت نیاز، بقیه خطوط مرزی را نیز اضافه كنید.

 از برگه Patterns استفاده كنید 

برای بكار بردن الگو روی سلول های انتخاب شده، روی برگه Patterns كلیك كنید. برای پر كردن سلول های انتخاب شده با رنگ، از صفحه Color یك رنگ انتخاب كنید. برای تركیب كردن دو رنگ در الگو، روی دكمه لیست كشویی Pattern كلیك كنید و رنگ دوم را انتخاب كنید. دوباره لیست را باز كنید تا یك الگو انتخاب كنید. برای مشاهده نتیجه انتخاب ها، به قسمتSample نگاه كنید.

انتخاب های فرمت دهی را اعمال كنید 

روی Ok كلیك كنید تا كادر محاوره‌ای Format Cells بسته شود و تنظیمات جدید در برگه كاری اعمال شوند.

*توجه *

چاپ كردن خطوط شبكه

بصورت پیش فرض، خطوط شبكه Excel چاپ نمی‌شوند. برای اینكه آنها را چاپ كنید، از منوی File ، گزینه Page Setup را انتخاب كنید. در كادر محاوره ای Page Setup ، روی برگهSheet كلیك كنید. در قسمت Print ، گزینه Gridlines را انتخاب كنید و روی Ok كلیك كنید. حال برگه كاری را چاپ كنید تا خطوط شبكه را مشاهده كنید.

استفاده از دكمه های Formatting

برای اضافه كردن خط مرزی به یك سلول، آن را انتخاب كنید، روی فلش دكمه Borders در نوار ابزار Formatting كلیك كنید و یك شیوه برای خط مرزی انتخاب كنید. برای اضافه كردن رنگ به پس زمینه سلول، روی فلش دكمه Fill Color كلیك كنید و از صفحه رنگ، یك رنگ انتخاب كنید. اگر بخواهید رنگ فونت را تغییر دهید، روی فلش دكمه Fill Color كلیك كنید و یك رنگ انتخاب كنید.

 

رمزگذاری روی فایل‌های اکسل

                                                                                                                                   زمانی است که میخواهیم برای محافظت از فایل های اکسل و اینکه  فایل ها توسط هر کسی خوانده نشود لازم است  که آنها رارمزگذاری کنیم تاافراد مشخصی با داشتن کلمه رمز بتوانند فایل را باز کنند

برای اینکار کافیست روی دکمه File در گوشه بالا سمت راست کلیک کنید. پس از باز شدن بخش BackStage ، روی دکمه Protect Workbook کلیک کنید و از منوی باز شده گزینه Encrypt With Password را کلیک کنید.

در پنجره باز شده پسورد مورد نظرتون را تایپ کنید و پس از تایید رمز در پنجره ای که مجددا باز میشود رمزرادوباره تایپ کنید و ok را کلیک کنید.

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

شناخت اتفاقات عجیب در اکسل

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

1- فلش آبی رنگی در شیت اکسل که پاک نمی شود!

 

یک فلش به یکباره در اکسل ظاهر می شود که بر روی آن نمی توانید کلیک کنید و آنرا پاک کنید.

دلیل این امر اصلاحا Circular References است. یعنی فرمولی که دچار یک حلقه یا loop شده است.

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

مثلادر سلول B3 ما فرمولی نوشته ایم که به سلول C7 ربط دارد و البته !!!! در C7 فرمولی نوشته ایم که به سلول B3 ربط دارد و اکسل سردرگم می شود . بدیهی است که شما باید فرمول ها را اصلاح نمایید.

2- آدرس عجیب و غریب سلول ها در فرمول نویسی!

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

تعجب نکنید این یک روش آدرس دهی است . مثلا سلول A1 که در سطر 1 و ستون 1 اکسل واقع شده است، آدرس آن می شود R1C1 و همینطور الی آخر.

دلیل این موضوع آن است که تنظیمات اکسل را به اشتباه تغییر داده اید. پس EXCEL OPTIONS بروید وR1C1 reference style را از حالت انتخاب خارج کنید.

 

3- هنگام کپی کردن فرمول ، اعداد تکرار می شوند!!!

این حالت وقتی پیش می آید که شما محاسبات اکسل را از حالت Automatic خارج کرده اید و به حالت Manual در آورده اید.

در این حالت اکسل فرمولها را آپدیت نمی کند و منتظر می ماند که شما به صورت دستی آنها را آپدیت کنید که البته کلید میانبر آن F9 است. در Status Bar نرم افزار اکسل شما می توانید کلمه Calculate را مشاهده نمایید (تصویر قبل را ببینید) که یعنی این شیت احتیاج به محاسبه دارد و اعدادش آپدیت نیست.

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

برای تنظیم این حالت (یعنی محاسبات اتوماتیک انجام شود و یا دستی) می توانید از دو مسیر زیر اقدام کنید :

Formual --> Calculation Options

یا

Excel Options --> Formulas --> Calculation Options

 

4- نمودار اکسل خالی (سفید) است!

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

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

5- صفحات سفید زیادی Print می شود

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