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

 توابع تبدیل حروف کوچک و بزرگ LOWER: تمامی کاراکتر‌ها را به حروف کوچک تبدیل می‌کند.
 PROPER: کاراکتر‌های موجود در یک متن را به حالت استاندارد تبدیل می‌کند.
 UPPER: تمامی کاراکتر‌ها را به حروف بزرگ تبدیل می‌کند.

 تبدیل حروف به اعداد به یکدیگر
 CHAR: تبدیل کد ASCII به حروف.
 CODE: تبدیل حروف به کد ASCII.

 جایگزین کردن قسمتی از یک رشته متن
 REPLACE: جای کاراکتر مورد نظر کاراکتر دیگری را قرار می‌دهد.
 SUBSTITUTE: جای عبارت درج شده در یک جمله عبارت دیگری را قرار می‌دهد.

 توابع دستیابی به قسمتی از یک رشته CONCATENATE: به هم چسباندن دو یا چند متن.
 LEFT: نمایش ‌تعداد حروف سمت چپ سلول مورد نظر.
 MID: نمایش بخشی از یک کلمه.
 RIGHT: نمایش ‌تعداد حروف سمت راست سلول مورد نظر
 REPT: تکرار عبارتی خاص را به تعداد مشخص.

 توابع اطلاعاتی 

 LEN: شمارش تعداد کاراکترهای موجود در یک سلول.
 FIND: پیدا کردن حروف مورد نظر خود در یک عبارت(حساس به کوچکی و بزرگی).
 SEARCH: پیدا کردن حروف مورد نظر خود در یک عبارت.
 EXACT: مقایسه عبارت موجود در دو سلول.
 T: تشخیص متن بودن.

 توابع تبدیل انواع داده‌ها در اکسل
 VALUE:
 TEXT:
 FIXED:

توابع منطقی 

 توابع عملگر بولی
 AND: گزاره‌های مختلف را چک کرده و در صورت درست بودن تمامی آنها مقدار درست و در غیر این صورت مقدار نادرست را برمی‌گرداند.
 OR: گزاره‌های مختلف را چک کرده و در صورت درست بودن یکی آنها مقدار درست و در غیر این صورت مقدار نادرست را برمی‌گرداند.
 NOT: اگر مقدار‌های وارد شده با هم برابر باشند مقدار نادرست و اگر نابرابر باشند مقدار درست را برمی‌گرداند.

 توابع شرطی
 IF: تابع شرطی.
 IFERROR: طرح یه معادله و درصورت اشتباه بودن پیغام مورد نظر به نمایش در بیاید.

 توابع بازگرداننده مقادیر ثابت منطقی
 TRUE: مقدار True را برمی‌گرداند.
 FALSE: مقدار False را برمی‌گرداند.

 توابع اطلاعاتی

 توابع اطلاعات خطا
 ISERROR: اگر مقدار داده شده بدون خطا باشد مقدار نادرست را بازمی‌گرداند.
 ERROR.TYPE: به جای نمایش error یک عدد نمایش می‌دهد.

 توابع اطلاعاتی عددی
 ISNUMBER: تشخیص عدد بودن یا متن بودن.
 ISEVEN: تشخیص زوج بودن.
 ISODD: تشخیص فرد بودن.
 N: تبدیل هر عبارتی به عدد.

 توابع دیگر انواع داده
 ISBLANK: تشخیص خالی بودن یک سلول.
 ISLOGICAL: اگر جواب یک معادله درستی یا نادرستی بود عبارت true را نمایش می‌دهد د ر غیر این صورت عبارت false را نشان می‌دهد.
 ISTEXT: شناسایی توابعی که دارای متن می‌باشد.
 ISNONTEXT: مخالف تابع ISTEXT می‌باشد.
 ISREF: مشخص می‌کند که عبارت وارد شده یک محدوده از سلول ها است یا خیر.
 TYPE: مشخص کرد در داخل هر سلول چه نوع کاراکتری وجود دارد.
 (text، number، error value و...)

 تابع اطلاعات عمومی
 CELL: هر نوع اطلاعاتی که در مورد سلول مورد نظر بخواهیم در اختیار ما قرار می‌دهد.
 INFO: نمایش مشخصات سیستم.

 توابع بازگرداننده مقادیر ثابت
 NA: نمایش پیغام خطای #N/A.

توابع تاریخ و زمان

 توابع تاریخ و زمان
 DATE: تاریخ مورد نظر.
 TIME: زمان مورد نظر.

 توابع تاریخ و زمان فعلی
 NOW: نمایش تاریخ و زمان جاری سیستم.
 TODAY: نمایش تاریخ جاری سیستم.

 توابع سازنده زمان
 HOUR: از زمان فقط ساعت را نمایش می‌دهد.
 MINUTE: از زمان فقط دقیقه را نمایش می‌دهد.
 SECOND: از زمان فقط ثانیه را نمایش می‌دهد.

 توابع سازنده تاریخ
 DAY: از تاریخ فقط روز را نمایش می‌دهد.
 MONTH: از تاریخ فقط ماه را نمایش می‌دهد.
 YEAR: از تاریخ فقط سال را نمایش می‌دهد.
 WEEKNUM: مشخص می‌کند که در چندمین هفته از سال قرار داریم.
 WEEKDAY: مشخص می‌کند که در چندمین روز از هفته قرار داریم.

 توابع محاسباتی تاریخ
 EDATE: تاریخ را به عقب یا جلو ببریم.
 EOMONTH: مشابه تابع EDATE ولی با این تفاوت که وقتی تاریخ را به جلو می‌بریم آخرین روز آن ماه را به نمایش در می‌آورد.
 WORKDAY: شمارش روزهای کاری.
 WORKDAY.INTL: کاری مشابه با تابع WORKDAY را انجام می‌دهد، با این تفاوت که روزهای تعطیل را خودمان مشخص می‌کنیم.
 DAYS360: شمارش روز‌های بین دو تاریخ.
 NETWORKDAYS: شمارش روز‌های کاری بین دو تاریخ.
 NETWORKDAYS.INTL: شمارش روز‌های کاری بین دو تاریخ.
 (روزهای تعطیل را خودمان مشخص می‌کنیم.)

 توابع آدرس دهی و جستجو

 توابع آدرس دهی به سلول
 ADDRESS: آدرس دهی در اکسل.
 INDEX: برای ارجاع از یک یا چند سلول استفاده می‌شود.
 INDIRECT: با وارد کردن یک رشته آن را تبدیل به یک مرجع سلول می‌کند.
 OFFSET: تعیین محدوده‌ای از سلول ها می‌باشد.

 اطلاعات سطر، ستون و محدوده
 ROW: مشخص می‌کند در چندمین ردیف قرار داریم.
 COLUMN: مشخص می‌کند در چندمین ستون قرار داریم.
 ROWS: همانند تابع ROW می‌باشد، ولی با این تفاوت که شما می‌توانید مقدار چندین سلول را وارد نمایید.
 COLUMNS: همانند تابع ROWS عمل می‌کند ولی با این تفاوت که مجموعه ردیف‌ها را شمارش می‌کند.
 AREAS: وظیفه شمارش محدوده‌ها را دارد.

 توابع جستجوی داده
 HLOOKUP: به صورت افقی به دنبال عبارت مورد نظر می‌گردد.
 VLOOKUP: به صورت عمودی به دنبال عبارت مورد نظر می‌گردد.
 LOOKUP: در یک مجموعه از داده‌ها به دنبال یک مقدار می‌گردد
 CHOOSE: در یک لیست به صورت رشته شما با وارد کردن یک عدد رشته مورد نظر در لیست را به نمایش در می‌آورد.
 MATCH: موقعیت نسبی موردی را در یک آرایه مشخص می‌کند.

 سایر توابع مربوط به این بخش
 HYPERLINK: لینک دادن به یک سلول.

توابع ریاضی در اکسل

 اطلاعات اولیه اعداد
 ABS: محاسبه عملیات قدر مطلق.
 SIGN: تشخیص مثبت یا منفی بودن عدد.
 GCD: بزرگترین مقسوم علیه دو یا چند عدد را بدست می‌آورید
 LCM: کوچکترین مضرب مشترک دو یا چند عدد را نمایش می‌دهد.

 عملگرهای ریاضی در excel
 SUM: محاسبه عملیات جمع.
 POWER: عدد اول را به توان عدد دوم می‌رساند.
 SQRT: محاسبه جذر عدد مورد نظر.
 QUOTIENT: استفاده از این تابع می‌توان قسمت صحیح تقسیم دو عدد را بدست آورد.
 MOD: محاسبه باقیمانده دو عدد.
 AGGREGATE: مجموعه چندین تابع.
 SUBTOTAL: مجموعه چندین برنامه کاربردی.

 توابع گرد کننده در اکسل
 CEILING: عدد ورودی اول را به ضریب بزرگتر یا مساوی عدد ورودی دوم در میاورد.
 EVEN: عدد را به اولین عدد بزرگ و زوج تبدیل می‌کند.
 FLOOR: عدد ورودی اول را به ضریب کوچکتر یا مساوی عدد ورودی دوم در میاورد.
 INT: عدد مورد نظر را گرد می‌کند و به صورت یک عدد صحیح نمایش می‌دهد.
 MROUND: عدد ورودی اول را به ضریب نزدیکترین عدد ورودی دوم در میاورد.
 ODD: تبدیل عدد به اولین عدد فرد.
 ROUND: گرد کردن اعداد.
 ROUNDDOWN: وظیفه گرد کردن در یک محدوده از تعداد رقم اعشار یا صحیح به سمت پایین را دارد.
 ROUNDUP: وظیفه گرد کردن در یک محدوده از تعداد رقم اعشار یا صحیح به سمت بالا را دارد.
 TRUNC: مقدار اعشار یا عدد صحیحی که می‌خواهیم نمایش بدهیم.

 جمع شرطی SUMIF: جمع اعداد با شرایط مورد نظر.

 عملیات‌های پیشرفته ریاضی در اکسل پیشرفته
 SUMPRODUCT: برای ضرب کردن اعداد استفاده می‌شود.
 SUMSQ: به توان دو رساندن اعداد.
 SUMX2MY2: این تابع که علاوه بر این که عددها را به توان دو می‌رساند دو به دو از هم کم می‌کند و نتیجه را با هم جمع می‌کند.
 SUMX2PY2: این تابع که علاوه بر این که عددها را به توان دو می‌رساند دو به دو با هم جمع می‌کند و نتیجه را با هم جمع می‌کند.
 SUMXMY2: اعداد مورد نظر را اول از هم کم می‌کند و سپس به توان دو می‌رساند.
 SERIESSUM: مجموعه سری توانی.

 توابع ماترسی
 MDETERM: محاسبه دترمینال ماتریس.
 MINVERSE: معکوس یک ماتریس مربعی.
 MMULT: محاسبه ضرب دو ماتریس.

 محاسبه فاکتوریل
 FACT: محاسبه فاکتوریل.
 FACTDOUBLE: ضرب اعداد بین دو عدد مورد نظر(اگر عدد وارد شده فرد باشد فقط اعدا فرد را ضرب می‌‌کند و بالعکس).
 MULTINOMIAL: محاسبه فرمول

 توابع متفرقه در اکسل
 COMBIN: محاسبه تعداد جابجایی‌ها بدون توجه به ترتیب قرارگیری.
 ROMAN: عدد ورودی را به صورت لاتین نمایش می‌دهد.

 اعداد تصادفی RAND: یک عدد تصادفی بین صفر تا یک
 RANDBETWEEN: یک عدد تصادفی بین دو عدد مورد نظر ما

 توابع مثلثات
 PI: نمایش عدد پی.
 SQRTPI: عدد مورد نظر را در عدد π ضرب می‌کند و سپس جذر آن عدد را نمایش می‌دهد.
 DEGREES: تبدیل زاویه برحسب رادیان به درجه.
 RADIANS: تبدیل درجه را به رادیان.
 COS: محاسبه مقدار کسینوس یک عدد.
 ACOS: محاسبه آرک کسینوس.
 COSH: محاسبه کسینوس هیپر‌بلیک.
 ACOSH: محاسبه آرک کسینوس هیپر‌بلیک.
 SIN: محاسبه سینوس یک عدد.
 ASIN: محاسبه آرک سینوس.
 SINH: محاسبه سینوس هیپر‌بلیک.
 ASINH: محاسبه آرک سینوس هیپر‌بلیک.
 TAN: محاسبه تانژانت.
 ATAN: محاسبه آرک تانژانت.
 TANH: محاسبه تانژانت هیپر‌بلیک.
 ATANH: محاسبه آرک تانژانت هیپر‌بلیک.

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

منوها در نسخه‌های 2007 و 2010 اکسل، به صورت روبان (ribbon) هستند. روی هر منو که کلیک کنید، روبان مربوطه باز می‌شود. اگر روبان Data را انتخاب کنید، در بخش Data Tools می‌توانید روی Data Validation یا همان "اعبتارسنجی داده‌ها در اکسل " کلیک کرده و نوع و دامنه‌ی داده‌ی ورودی در خانه‌‌ی مربوطه از  اکسل را تعیین کنید.

منوی مربوط به Data Validation سه tab دارد. tabهای دوم و سوم به ترتیب برای تنظیم پیغام ورودی و هشدار خطا می‌باشند که در صورت تمایل می‌توان پیغام‌های لازم را در آنها درج کرد.

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

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

از Data Validation می‌توان برای تعریف انواع دیگر داده نیز استفاده کرد. مثلا اگر در یک خانه‌ی اکسل قرار است طول قد بر حسب سانتی‌متر وارد شود، می‌توان تنظیم کرد که داده‌ی ورودی به صورت یک عدد صحیح بین 100 تا 300 باشد. در این صورت اگر به اشتباه عدد 1.80 (بر حسب متر) وارد شد، کاربر با پیغام خطا در EXCEL مواجه شود و این امکان را به دست می‌آورد که داده‌اش را تصحیح کند و ادامه‌ی محاسبات‌اش دچار اشکال نشود.                                                                                                                             امیواریم از آموزش اکسل امروز نیز لذت برده باشید 

بعضی اوقات لازم است که فایلی را با گذاشتن پسورد محافظت کنیم. برای این کار باید مطابق شکل بر روی Save As که در برگه File است کلیک کنیم.

 بر روی دکمه Tools و سپس General Options کلیک کنید.

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

بعد از تایید (OK) پنجره قبلی، لازم است در کادر بازشده جدید مجدداً همان پسورد را وارد نمایید.

فقط توجه کنید که رمز را فراموش نکنید وگرنه شاید مجبور شوید فایل را هم به کلی فراموش کنید.:)

آخرین مرحله هم تعیین یک نام و فشردن دکمه Save است.

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

این ترفند برای وقتیکه فایل صورت وضعیت اکسل دارید و یا هر فایلی که اطلاعات مالی و مهم را در خود دارد بسیار کاربردی است.                                                                                                               توابع متنی در اکسل                                                                                                               

اکسل دارای توابع بسیار کاربردی برای کار با متن است.

چسباندن چند متن با کاراکتر &در excel

 برای اتصال چند عبارت در سلول باید از کاراکتر & استفاده کنیم.

تابع LEFT هم برای استخراج کاراکترهایی که در سمت چپ یک متن هستند استفاده می گردد. مشابه همین رفتار را برای تابع RIGHT داریم.

تابع LEN هم برای بیرون کشیدن تعداد کاراکتر یک عبارت متنی استفاده می شود.

تابع FIND دراکسل بر اساس پارامتر ورودی داخل عبارت جستجو کرده و در صورتیکه بتواند آنرا در عبارت پیدا کند، موقعیت آنرا به کاربر نمایش می دهد.

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

کلیدهای میان بر قالب بندی متن

تغییر قلم F+Shift+‍Ctrl

تغییر اندازه قلم P+Shift+‍Ctrl

پررنگ کردن قلم B+‍Ctrl

ایتالیک کردن قلم I+‍Ctrl

زیر خط دار کردن قلم U+‍Ctrl

دو زیر خط دار کردن قلم D+Shift+‍Ctrl

حذف قالب بندی کاراکتر Space Bar+‍Ctrl

فضای خطوط تنها پاراگراف 1+‍Ctrl

فضای دو برابر خطوط پاراگراف 2+‍Ctrl

فضای 5/1 برابر خطوط پاراگراف 5+‍Ctrl

تراز بندی وسط پاراگراف E+‍Ctrl

تراز بندی چپ پاراگراف L+‍Ctrl

تراز بندی راست پاراگراف R+‍Ctrl

تراز بندی از دو طرف پاراگراف J+‍Ctrl

برجسته کردن پاراگراف از چپ M+‍Ctrl

برجسته کردن پاراگراف ازراست M+shift+‍Ctrl

حذف قالب بندی پاراگراف Q+‍Ctrl

به روز رسانی فیلدها F9

تغییر روش نمایش فیلد منتخب Shift+F9

تغییر روش تمام فیلدهای سند Alt+F9

کاهش یا افزایش اندازه قلم به اندازه یک رقم [+Ctrlا]

تغییر اندازه قلم به اندازه پیش فرض قبلی یا بعدی Ctrl+Shiftا>

تبدیل حروف عادی به حالت دو زیر خط و برعکس D+Shift+‍Ctrl

تبدیل حروف عادی به اندیس دار پایین و برعکس =+Ctrl

تبدیل حروف عادی به اندیس دار بالا و برعکس =+Ctrl+Shift

تبدیل ابتدای کلمات به حروف کوچک یا بزرگ AK+shift+‍Ctrl

تغییر قلم جاری F+Shift+‍Ctrl

تغییر قلم جاری F+Shift+‍Ctrl

تغییر اندازه قلم جاری F3+Shift

ایجاد تو رفتگی از سطر بعدی P+Shift+‍Ctrl

حذف تو رفتگی سطر بعدی +T‍Ctrl

حذف تو رفتگی سطر دوم T+Shift+‍Ctrl

انتخاب همه سند A+‍Ctrl

درج یک شکستگی سطری Enter+Shift

انتقال به سطر یا صفحه بعدی Ctrl+Enter

اشکار یا پنهان نمودن علایم H+Shift+‍Ctrl

کلیدهای میان بر صدا در اکسل

خاموش/روشن نمودن میکروفن Winkey+V

انتقال بین دو حالت دیکته و فرمان صوتی Winkey+T

فعال کردن حالت تصحیح Winkey+C

کلیدهای میان بر منوهادر EXCEL

فعال شدن اولین عنوان نوار منو ALT

فعال شدن منوی Alt + F File

فعال شدن منوی Alt + E Edit

فعال شدن منوی Alt + V View

فعال شدن منوی Alt + I Insert

فعال شدن منوی Alt + O Format

فعال شدن منوی Alt + T Tools

فعال شدن منوی Alt + A Table

فعال شدن منوی Alt + W Windows

فعال شدن منوی Alt + H Help

چاپ در excel:

امکانات چاپ در excel یکی از قوی ترین و قدرتمند ترین امکانات چاپ در مجموعه office است. در excel کنترل کامل شما روی محدوده چاپ بسیار جذاب بوده و احساس راحتی به شما می دهد. حتما” در word تجربه کرده اید که محدوده چاپ شما همان صفحه سفید رنگی است که در اختیار شما قرار داردو این شما هستید که باید طوری با فونتها و فواصل باز ی کنید تامطلب مورد نظرتان در یک صفحه جا شود ولی در excel مرزهای چاپی هر صفحه را به راحتی می توانید تعیین کنید. این مرزهای چاپ هستند که خود را با محدوده صفحاتی که شما تعیین می کنید وفق می دهند.

می توانید هر صفحه را بایک ضریبی از اندازه آن چاپ کنید. در این صورت ممکن است فونت شماره 10 شما در اندازه فونت 14 چاپ شود.

از منو view گزینه Page Break Preview را انتخاب کنید تا محدودههای چاپ در قالب خطوط مرزی آبی رنگ مشخص شود. هر چه در این محدوده قرار بگیرد چاپ خواهد شد. با رنگ توسی کمرنگ در داخل صفحه شماره صفحه نوشته می شود که فقط محض اطلاع شماست و چاپ نمی شود , می توانید اشاره گر ماوس را روی این مرزهای آبی رنگ ببرید و با تغییر شکل ماوس آنرا جابجا کنید. همیچنین با انتخاب یک سطر یا یک ستون به کمک گزینه Page Break از منوی Insert می توانید یک صفحه جدید برای چاپ ایجاد کنید. خطوط ابی رنگ نمایش داده شده به وسیله خط چین خطوطی هستند که خود excel به شما برای چاپ پیشنهاد داده است و خطوط نهایی مورد نظر شما نیستند.

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

[ آخرین بازنگری 16 مرداد 1389 ] [ نسخه چاپی ]

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

روش اول: بر روی سلول کلیک کرده و سپس در نوار فرمول کلیک کرده ویرایش را انجام می دهیم.

روش دوم: بر روی سلول Double کلیک کرده و ویرایش را انجام می دهیم.

ویرایش صفحات کاری:

انتخاب یک یا چند خانه:

1- توسط صفحه کلید:

– برای انتخاب یک خانه کافی است با مکان نما بر روی آن برویم.

– برای انتخاب تعدادی خانه مجاور کافی است ئکمه Shift را پایین نگه داشته و با مکان نما بر روی آن حرکت کنیم.

– برای انتخاب سطر جاری کافی است کلیدهای shift + spacebar را بفشاریم.

– برای انتخاب ستون جاری کافی است کلیدهای Ctrl + Spacebar را بزنیم.

برای انتخاب کاربرگ جاری کافی است کلیدهای Ctrl + shift + spacebar را بزنیم و یا Ctrl + A را می زنیم.

2- توسط ماوس:

– برای انتخاب یک خانه کافی است با ماوس بر روی آن کلیک کنیم.

انتخاب یک یا چند خانه:

1- توسط صفحه کلید:

– برای انتخاب یک خانه کافی است یا جهت نما (Arrow Key) بر روی آن برویم.

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

– برای انتخاب سطر جاری کافی است کلیدهای Shift + Spacebar را بفشاریم.

– برای انتخاب یک ستون کافی است کلیدهای Ctrl + Spacebar را بزنیم.

– برای انتخاب کاربرگ جاری کافی است کلیدهای Ctrl + Shift + Spacebar را بزنیم.

2- توسط ماوس

– برای انتخاب یک خانه کافی است با ماوس بر روی آن کلیک کنیم.

– برای انتخاب تعدادی خانه مجاور کافی است دکمه سمت چپ ماوس را پایین نگه داشته و روی خانه ها Drag کنیم.

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

– برای انتخاب یک ستون کافی است روی حرف ستون کلیک کنیم.

– برای انتخاب یک کاربرگ کافی است بر روی سلول * کلیک کنیم.

نکته:

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

دستور بازگشت در اکسل:

می توانیم عمل انجام شده را برگردانیم. فرض کنید در سلول A1 عدد 10 را می نویسیم و سپس آن را پاک می کنیم. اگر در این زمان از دستور Undo استفاده کنیم. عدد 10 بر می گردد. برای استفاده از Undo به یکی از روشهای زیر عمل می کنیم.

روش اول:

1- انتخاب منوی Edit

2- انتخاب Undo

روش دوم:

فشردن همزمان کلیدهای Ctrl + Z

روش سوم:

استفاده از آیکون Undo در نوار ابزار Standard

دستور Redo:

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

روش اول:

1- منوی Edit

2- انتخاب Redo

روش دوم:

فشردن همزمان کلیدهای Ctrl + Y

روش سوم:

استفاده ار آیکون Redo در نوار ابزار Standard

نسخه برداری و انتقال سلولهادر EXCEL

نسخه برداری از سلولها:

1- موضوعات مورد نظر را انتخاب می کنیم.

2- به یکی از روشهای زیر Copy را انتخاب می کنیم

الف – استفاده ار آیکون کپی

ب- انتخاب منوی Edit، گزینه Copy

ج – راست کلیک بر روی موضوع و انتخاب گزینه Copy

د- فشردن همزان کلیدهای Ctrl + C

3- کلیک بر روی مکانی که می خواهیم اطلاعات اضافه شوند.

4- با یکی از روشهای زیر Paste را انتخاب می کنیم:

الف- استفاده از آیکون Paste

ب- انتخاب منوی Edit، گزینه Paste

ج- راست کلیک بر روی موضوع و انتخاب گزینه Paste

د- فشردن همزمان کلیدهای Ctrl + V

انتقال سلولها:

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

1-انتخاب موضوعات مورد نظر

2- به یکی از روشهای زیر Cut را انتخاب می کنیم:

الف – استفاده از آیکون Cut

ب- انتخاب منوی Edit ‌، گزینه Cut

ج- راست کلیک بر روی موضوع مورد نظر و انتخاب گزینه Cut

د- فشردن همزمان کلیدهای Ctrl + X

3- کلیک بر روی محل انتقال موضوعات

4- به یکی از روشهای گفته شده در حالت قبل گزینه Paste را انتخاب می کنیم.

تذکر: وقتی که Copy یا Cut را انتخاب می کنیم، کادر چشمک زن به دور سلولهای انتخابی دیده می شود تا زمانی که این کادر وجود واشته باشد، Paste امکان پذیر است. و زمانی که این کادر چشمک زن از بین رفت عمل Paste انجام نمی شود. (تفاوت Excel با Word) زمانی این کادر چشمک زن از بین می رود که ما عمل دیگیر انجام دهیم.

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

1- انتخاب منوی Edit

2- انتخاب Office clipboard

3- در Task Pan منوی Clipboard ظاهر می شود و ما می توانیم در یک لحظه 24 مورد را در Clipboard نگه داریم و از آن استفاده کنیم.

4- موردی را که می خواهیم Paste کنیم انتخاب می کنیم (در پنجره Clip Board)

5- در محل مورد نظر روی Sheet کلیک می کنیم. 6- Paste می کنیم.

اگر بخواهیم همه موضوعات موجود در Clip board یک باره Paste شود کافی است در برنامه Clipboard روی Paste Cell، کلیک کنیم.

تذکر: اگر بر روی یک موضوع دوبار Ctrl+c را پشت سر هم بفشاریم. پنجره Clipboard باز می شود.

حذف سلول  و پاک کردن محتویات خانه ها در اکسل:

1-پاک کردن سلول و حذف سلول:

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

2-پاک کردن محتویات خانه ها:

روش اول:

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

2- از منوی Edit، گزینه Clear را انتخاب می کنیم.

3- زیر منویی باز می شود که از آن Content را انتخاب می کنیم.

روش دوم:

1- سلول را انتخاب کرده

2- دکمه del صفحه کلید را می زنیم.

روش سوم:

1- بر روی سلول کلیک راست کرده

2- Clear Content را انتخاب می کنیم.

حذف سلول:

1- انتخاب سلول های مورد نظر

2- انتخاب Delete به یکی از روشهای زیر:

الف – Edit – delete

ب – R.C – delete

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

الف – Shift Cell Left:

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

ب- Shift Cells Up:

خانه زیرین خانه پاک شده را بجای آن منتقل می کند.

ج- Entire Row:

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

د- Entire Column:

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

نکته:

اگر محیط فارسی بجای ستون سمت راست، ستون سمت چپ جایگزین می شود.

برای ایجاد یک فایل جدید یا یک Bookجدید به یکی از روشهای زیر عمل می کنیم.

روش اول: کلیک بر روی آیکون New

روش دوم: Ctrl + N (یک Book خالی) Blank Work Book – Task Pam ظاهر می شود – New – File

بستن یک Book باز:

روش اول: کلیک بر روی آیکون Close موجود در انتهای نوار منو

روش دوم: File – Close

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

خروج از محیط Excel:

1- کلیک بر روی آیکون Close موجود ردر نوار عنوان

2- File – Exit

3- Alt+f4
ذخیره کردن Book ایجاد شده
1- به یکی از سه روش زیر Save را انتخاب می کنیم:
الف) Icon Save

ب) File – Save

ج) Ctrl+ S
2- پنجره ای باز شده که شامل قسمتهای زیر است:

الف) در سمت چپ صفحه تابلوی دیده می شود که برای دسترسی سریع به قستهای اصلی Win، مثل My Computer , My Document, Desktop است. کافی است بر روی گزینه مورد نظر کلیک کرده تا در صفحه سفید رنگ مقابل محتویات آن پوشه را ببینیم.

ب) Save in: در این قسمت مسیر ذخیره مکردن فایل را مشخص می کنیم، به طور پیش فرض فایلها در My Document ذخیره می شوند.

ج) File Name: نام فایل را تایپ می کنیم. به طور پیش فرض فایلها با اسامی Book1، Book2 و … ذخیره می شوند.

د) Create New Folder: ممکن است بخواهیم فایل ها را در پوشه جدیدی قرار دهیم برای این کار ابتدا یا Save in مسیر مورد نظر را انتخاب کرده سپس برروی این آیکون کلیک می کنیم. پنجره ای باز می شود و نام پوشه را می خواهد. نام پوشه را تایپ کرده و Ok می کنیم. پس از انجام تنظیمات بالا بر روی دکمه Save پنجره کلیک می کنیم.

ذخیره کردن Book ذخیره شده با نام دیگر در اکسل:

در منوی File علاوه بر Save گزینه save as هم وجود دارد. تفاوت این دو گزینه چیست؟

برای بار اول ذخیره سازی یک Book این در هیچ تفاوتی با هم ندارند. بر روی هر کدام که کلیک کنیم پنجره Save as باز می شود. ولی برای دفعات بعد (بعد از اینکه Book را ذخیره کردیم) اگر Save را انتخاب کنیم، دیگر پنجره ای باز نمی شود، و تغییرات بر روی فایل با همان نامی که انتخاب کرده ایم ذخیره می شود ولی اگر Save as را انتخاب کنیم مجدداً پنجره Save as باز شده و می توانیم فایل را با نام دیگر یا در مسیر دیگر یا با نام دیگر و در مسیر دیگر ذخیره کنیم.

تذکر: وقتی فایلی را با نامی ذخیره می کنیم این فایل در نوار عنوان ظاهر می شود.

ذخیره کردن Book روی دیسکت:

برای ذخیره کردن یک فایل بر روی دیکست باید در پنجره Save as، در قسمت Save in،:3.5 floppy A را انتخاب کنیم.

ذخیره کردن Book با فرمت مناسب برای صفحات وب:

اگر بخواهیم فایل Excel  را با فرمتی ذخیره کنیم که مانند یک صفحه وب باشد و بتوان آن را در Browser دید، کافی است مراحل زیر را انجام دهیم:

1- انتخاب منوی File

2- انتخاب گزینه as a web page save

نکته:

در این حالت فایل با پسوند.htm ذخیره می شود.
بازسازی Recovery Document
اگر Excel با مشکلی مواجه شود، در صدد ذخیره کردن کارپوشه های باز در هنگام برخورد با مشکل بر می آید در این حالت اگر دوباره اکسل را شروع کنیم پنجره Document Recovery باز شده و کلیه کارپوشه هایی که باز بوده اند را نمایش می دهد. کافی است بر روی کارپوشه مورد نظر کلیک کرده تا باز شود. در مقابل نام کار پوشه دو عبارت ممکن است دیده شود:
1- [Recover]: اگر این عبارت نشان داده شده باشد به این معناست که Excel موفق به ذخیره کردن فایل شده است.

2- [original] اگر این عبارت نشان داده شده باشد به این معناست که این فایل، فایل با آخرین نسخه ذخیره شده است. و Excel در هنگام بروز مشکل آن را ذخیره نکرده است زیرا یا بعد از آخرین ذخیره، تغییری نکرده است و یا تغییرات انجام شده ولی نتوانسته ذخیره کند.

تذکر: ممکن است با هر دو عنوان فایل دیده شود می توانیم حالت Recovery را باز کرده و با فایل اصلی مقایسه کنیم و سپس ذخیره کنیم.
باز کردن صفحه ذخیره شده
می خواهیم فایلی را که قبلا ذخیره کرده ایم دوباره بیاوریم برای این کار:
1- پنجره Open را به یکی ار روشهای زیر باز می کنیم:

الف: Icon Open

ب: File – Open

ج: Ctrl + o

2- پنجره ای باز می شود که پنجره Openبوده و مشابه پنجره save بوده و شامل قسمتهای زیر است:

الف: Look in در این قسمت مسیری که فایل را در آن ذخیره کرده ایم انتخاب می کنیم.

ب: Icon View: کلیک بر روی این Icon منویی ظاهر می شود که حالتهای مختلف نمایش فایلها و پوشه های موجود در مسیر انتخاب شده را نمایش می دهد و شامل حالت های زیر است:

1- Thumbnails: این حالت مناسب فایلهای تصویری بوده و تصویر را نمایش می دهد.

2- Tiles: پوشه ها و فایلهای موجود را به صورت کاشی وار در دو ستون نمایش می دهد.

3- Icons: پوشه ها و فایلهای موجود را در 6 ستون نمایش می دهد.

4- List: پوشه ها و فایلهای موجود را در 15 سطر نمایش می دهد.

5- Details: پوشه ها و فایلهای موجود را با جزئیاتشان نمایش می دهد. در حالتهای قبل فقط نام فایلها نمایش داده می شود ولی در این حالت علاوه بر نام فایل، سایز آن، نوع و تاریخ ویرایش آن نمایش داده می شود.

6- Properties: با انتخاب این گزینه پنجره ی Open به دو تابلو تقسیم شده در تابلو سمت چپ نام فایل و پوشه ها و در تابلو سمت راست خصوصیات فایل مثل شخص ایجاد کننده آن، تاریخ تغییرات، نام کمپانی و … نمایش داده می شود.

7- Preview: پیش نمایش از فایل، نمایش داده می شود.

نکته:

برای بعضی از فایلها با انتخاب این گزینه در تابلو سمت راست پیام Preview not available نمایش داده می شود. برای رفع این مشکل باید بر روی فایل باز عملیات زیر را انجام دهیم:

File – Properties – انتخاب Tab: Summary – Save preview picture – ok

پس از انجام عملیات بالا، مجددا برروی آیکن Save، کلیک کنید.

تذکر: در صورتی که بخواهیم هر روز از کار پوشه ای استفاده کنیم و بخواهیم به طور خودکار با باز کردن Excel این کار پوشه باز شود و کافی است آن را در پوشه XL start قرار دهیم. این پوشه در هنگام نصب Excel ساخته می شود و مسیر آن یکی از دو مسیر زیر است.

C:/document and setting\ئی که در آن هستیم User \application data \ یا

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

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

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

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

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

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

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

توصیه می گردد حتماً مقاله ی مربوط به معرفی توابع IS را مطالعه نمایید.

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

ممکن است خروجی یک فرمول در زنجیره ی فرمول نویسی ها از نوع داده ی مورد نظر شما نباشد و فرمول های شما را دچار اشکال نماید. برای بررسی نوع داده ها در محیط اکسل می توانیم از تابع TYPE استفاده نماییم. با تابع 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 برای رصد کردن وضعیت فرمول ها در اکسل مورد استفاده قرار می گیرد،

شما احتمالا بارها با این خطا در Excel برخورد کرده‌اید اما این خطا را با این نام نمی‌شناسید

علائم ظاهری این خطا در Excel و نحوه بیان آن از طرف شما

* هر بار که Excel را باز می‌کنم یک پیغام می‌دهد و با OK کردن نیز رفع نمی‌شود و بار دیگر نشان داده می‌شود.
* یک فلش آبی رنگ روی صفحه Excel می‌آید و اصلا انتخاب و پاک نمی‌شود.
* نتیجه فرمول SUM عدد صفر می‌شود و این در حالی است که من کلی عدد در محدوده Sum دارم.

بله، شما به یکی از اشکال فوق سوالتان را می‌پرسید و جواب همه آنها در Excel رخ دادن خطای Circular Reference است که باید آنرا پاک یا کنترل نمایید.

توضیح Circular Reference در Excel

برای توضیح این خطا یک مثال ساده آورده می‌شود تا دقیقا بفهمید که چه بر سر Excel  می‌آید. فرض کنید که در یک اداره رفته‌اید و باید نامه‌ای امضا شود، آقای کارمند الف می‌گوید " نامه شما ابتدا باید به  تایید آقای جیم برسید " و وقتی که به اتاق آقای "جیم" می‌روید، می‌گوید "این نامه ابتدا باید توسط آقای الف کارشناسی و تایید شود".
و نتیجه این کار پاس‌کاری شما یا قرار گرفتن در یک "دور باطل" یا به قول ما کامپیوتری‌ها Loop خواهد بود که در Excel به این حالت Circular Reference می‌گویند.

Circular Reference در Excel چگونه ایجاد می‌شود

فرمولی را بنویسید که اشاره به خانه E2 داشته باشد (مهم نیست چه فرمولی، اما در آن حتما E2 آمده باشد) سپس در خانه E2 فرمولی را بنویسید که اشاره به خانه B2 داشته باشد.
بعد زدن کلید Enter در سلول دوم Excel بلافاصله پیغام زیر را به شما نشان می‌دهد و اگر کلید OK را بزنید Help نرم افزار Excel باز شده و Circular Reference را توضیح می‌دهد.

البته حالت‌های دیگری نیز می‌توان مثال زد، مثلا شما در سلول B2 فرمولی مانند زیر را بنویسید. چون خود سلول در محدوده تابع SUM است در نتیجه Circular Reference ایجاد خواهد شد.

آوردن فلش آبی رنگ

بعد اینکه این حالت را ایجاد کردید، اگر خانه E2 را ویرایش کنید (روی E2 کلید F2 را بزنید و سپس Enter) این فلش به صورت خودکار نمایش داده می‌شود و بیانگر ایجاد  Circular Reference در Excel است.

خطای هنگام باز شدن این فایل

این فایل را ذخیره کنید و ببنید و سپس باز نمایید، مشاهده می‌کنید که در هر بار بازشدن فایل پیغام خطای Circular Reference نمایش داده می‌شود.

روش دیگری برای تشخیص Circular Reference

هموار در Status Bar شما می‌توانید آدرس اولین سلولی که دچارCircular Reference  شده است را ببنید. که به محض رفع کردن خطای این سلول، آدرس سلولهای بعدی را نشان می‌دهد.

روش پیدا کردن همه Circular Referenceدر اکسل

از ریبون (به نوار ابزار Excel 2007 ریبون می‌گویند) گزینه Formula و سپس مانند شکل زیر اقدام نمایید:

نتیجه فرمولهایی که به سلول‌های Circular Reference اشاره دارند، همواره صفر است

در مثال زیر سلول C5 دچار CR شده است (فرمول آن در سلول کناری برای راهنمایی به صورت متنی کمرنگ نوشته شده) و همانطور که می‌بینید نتیجه سلول C9 که جمع سلولهای بالایی است عدد صفر شده است و این به دلیل خطای CR است.

تا اینجا با مفهوم Circular Reference در Excel آشنا شده‌اید و حال به نحوه رفع این خطای آشنا خواهیم شد.

روش رفع خطا در اکسل

دو راه کلی برای رفع این خطا وجود دارد که راه اول پاک کردن یا اصلاح فرمولی است که باعث CR شده است که سلولی که باعث CR شده است را ویرایش کرده و فرمول آنرا طوری اصلاح می‌کنیم که باعث Loop نشود.
راه حل دیگر فعال کردن گزینه Iteration در Excel Options است. در این وضعیت ما CR را کنترل می‌کنیم و نه حذف. بدین ترتیب که به Excel می‌گوییم که اگر در جایی دچار Loop شد، این Loop را 100 بار اجرا کن (این حلقه را 100  بار ادامه بده) و سپس کار را تمام کن.

برای فعال کردن وضعیت Iteration در Excel Options گروه Formula و سپس Enable iterative Calculation را در حالت فعال قرار می‌دهیم و تعداد دفعات اجرای را تعیین می‌کنیم.

استفاده از این تکنیک برای ساختن Loop در اکسل اصلا توصیه نمی‌شود زیرا شما باید Calculation رادر حالت Manual قرار دهید و... و در ضمن ابزارهای کنترلی برای خارج شدن از Loop به سختی ساخته می‌شوند و این در حالی است که در VBA این کار به سادگی با دستوراتی مانند DO-LOOP  /  FOR-NEXT قابل اجراست و اگر شما در کتابهای مرجع Excel و یا اینترنت جستجو کنید خواهید دید که از این قابلیت سخنی به میان نرفته است و دلیل آن همان است که ذکر شد.

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

تعیین ناحیه چاپ در EXCEL
گاهی اوقات در یک برگه اطلاعات زیادی وارد می شود که در مواردی مایل به چاپ همه آنها نیستیم. با استفاده از یک امکان اکسل، می توانیم ناحیه ای را به عنوان «ناحیه چاپ» مشخص کرده و فقط آن را چاپ کنیم. برای اینکار ابتدا ناحیه مورد نظر را از برگه انتخاب کرده و سپس از زبانه Page Layout و از بخش Page Setup روی دکمه Print Area کلیک کنید. سپس از دو گزینه موجود، گزینه Set Print Area را انتخاب کنید. بدین ترتیب ناحیه انتخابی چاپ مشخص می شود.

حذف ناحیه چاپ
برای حذف ناحیه چاپ شده نیز کافیست که مسیری که در بالا توضیح داده شد را طی کرده و از منوی Print Area گزینه Clear Print Area را انتخاب کنید.
جدا کردن صفحات با استفاده از امکان Page Break در ECXEL
همانطور که پیشتر اشاره شد، یک صفحه گسترده قادر به نگهداری حجم وسیعی از اطلاعات است که می تواند در قالب چندین برگه کاغذ چاپ شود. نرم افزار اکسل به صورت خودکار اطلاعات برگه ها را بر طبق ابعاد کاغذ تعیین شده و صفحه بندی می کند. بدین معنی که اگر بخشی از اطلاعات در کاغذ نخست جا نشود، در کاغذ دوم چاپ خواهد شد. مرز بین صفحات در اکسل، توسط امکان Page Break مشخص می شود. فرض کنید لیستی داریم که ممکن است کل اطلاعات آن به راحتی در یک صفحه چاپ شود اما می خواهیم آنها را در دو صفحه مجزا چاپ کنیم به طوری که در وضعیت نمایش آن تغییری حاصل نشود.
این کار را با مثالی توضیح می دهیم. فرض کنید لیست زیر را که در آن مشخصات گروهی از لپ تاپ ها درج شده، در دو صفحه چاپ کنی به طوری که از ستون Name تا ستون Hard در یک صفحه و ستون های دیگر در صفحه دوم چاپ شود.

برای اینکار نخست سلولی را که قرار است به عنوان اولین سلول صفحه درج شود را انتخاب می کنیم. در این مثال باید سلول «LCD» انتخاب شود. سپس از زبانه Page Layout و در بخش Page Setup روی دکمه Breaks کلیک کرده و از منوی باز شده، گزینه Insert Page Break را انتخاب می کنیم. به این ترتیب در این محل یک Page Break جدید ایجاد می شود.

برای حذف Page Break ایجاد شده، ابتدا سلول اول صفحه دوم را انتخاب می کنیم و سپس از منوی Breaks گزینه Remove Page Break را انتخاب می کنیم. گزینه Reset All Page Breaks کلیه Page Breakهای موجود را حذف می کند.

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

با استفاده از اکسل می توان اندازه این فضا را تعیین کرد. برای اینکار از زبانه Page Layout بخش Setup Page روی دکمه Margins کلیک کنید. این منو سه حاشیه از پیش تعیین شده را نشان می دهد. گزینه اول (Normal) بیانگر حاشیه عادی، گزینه دوم (Wide) بیانگر حاشیه پهن و گزینه سوم (Narrow) بیانگر حاشیه باریک است.
با انتخاب آخرین گزینه (Custom Margins…) نیز می توان حاشیه را به اندازه دلخواه تنظیم کرد.
یکی دیگر از تنظیمات صفحات در اکسل، تعیین جهت صفحه است. صفحات یک برگه هم به صورت عمودی و هم به صورت افقی قابل چاپ هستند. برای تعیین جهت صفحه، در زبانه Page Layout و از بخش Page Setup روی دکمه Orientation کلیک کنید. گزینه Portrait کاغذ را عمودی و گزینه Landscape آن را افقی می کند.

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

اگر صفحه چاپی دوم این لیست فاقد عنوان ستون باشد، تشخیص اینکه اطلاعات مربوط به کدام ستون است، دشوار خواهد بود. برای اینکار از زبانه Page Layout و در بخش Page Setup روی دکمه Print Titles کلیک می کنیم. در این صورت پنجره Page Setup مانند تصویر زیر باز خواهد شد.

اکنون در کادر Rows to repeat at top کلیک کرده و سپس روی شماره سطر تیتر مورد نظر در برگه کلیک می کنیم. در این صورت آدرس سطر به طور خودکار در این کادر قرار می گیرد. بدین ترتیب یک سطر را به عنوان تیتر چاپ تعیین کرد‌ه ایم و هنگام چاپ برگه مشاهده می کنیم که این سطر در بالای لیست همه صفحات چاپ می شود.

خطوط راهنما
هنگام چاپ یک صفحه گسترده، خطوط خاکستری رنگی که به عنوان کادر اطراف سلول در برگه مشاهده می شوند، به طور پیش فرض چاپ نمی شوند. برای چاپ این خطوط از زبانه Page Layout گروه Sheet Options گزینه Print از قسمت Gridlines را انتخاب می کنیم.

تغییر اندازه کاغذ
نرم افزار اکسل به طور پیش فرض اندازه کاغذ را به حالتی که آن را «Letter» تعریف کرده، تعیین می کند. برای تغییر این اندازه و همچنین مشاهده اندازه های دیگر، از زبانه Page Layout و از بخش Page Setup گزینه Size را کلیک کنید.

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

برای خارج شدن از محیط Print Preview در زبانه Print Preview روی دکمه Close Print Preview کلیک کنید.

چاپ برگه
برای چاپ برگه، همان مسیر پیش نمایش را رفته و این بار به جای انتخاب گزینه Print Preview، گزینه Print را انتخاب کنید. در این صورت کادری مانند کادر زیر باز می شود.

در بخش Print range انتخاب گزینه All باعث می شود که تمام صفحات چاپ شود. اما با انتخاب بخش Page(s) می توانید صفحات مورد نظر را با شماره مشخص کنید. برای اینکار شماره اولین صفحه را در From و شمار آخرین صفحه را در To وارد کنید.
در بخش Print What می توان موضوع چاپ را تعیین کرد. بخش Selection برای چاپ سلول های انتخاب شده، بخش Active Sheet(s) برای چاپ برگه یا برگه های فعال، بخش Entire Workbook برای چاپ کل صفحه گسترده استفاده می شود.
با انتخاب گزینه Ignore print areas نیز می توان ناحیه چاپ تعیین شده را نادیده گرفت. با استفاده از گزینه Copies نیز می توان تعداد نسخه های چاپی را مشخص کرد.

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

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

مقایسه ی دولیست در excel

  برای این منظور به روش زیر عمل می کنیم.

ابتدا ناحیه A1:A6 را انتخاب نموده و نام FirstList را برای آن انتخاب می کنیم. (برای نام گذاری در کادر اسم نام مورد نظر را بدون فاصله تایپ کرده و کلید Enter را فشار می دهیم)

به همین ترتیب ناحیه B1:B6 را انتخاب نموده و برای آن نام SecondList را انتخاب می کنیم.

ناحیه A1:A6 را انتخاب می کنیم. (می توانیم از لیست کادر نام عبارت FirstList را انتخاب نماییم)

از سربرگ Home ابزار Conditional formatting را انتخاب نمایید و سپس بر روی گزینه New rule کلیک کنید.

از پنجره ظاهر شده عبارت “Use a formula to determine which cells to format” را انتخاب نمایید.

فرمول را به صورت زیر وارد نمایید.

=COUNTIF(SecondList,A1)=0

 بر روی دکمه Format کلید کنید و از پنجره ظاهر شده یک رنگ دلخواه انتخاب نمایید.

بر روی دکمه ok کلیک کنید. 

 چک باکس stop if true را در حالت انتخاب قرار داده و بر روی دکمه ok کلیک کنید.

نتیجه را مشاهده می کنید

 همانطور کهمی بینید Delhi Daredevils  و  Royal Challengers Bangalore منحصر به فرد هستند (در ناحیه SecondList قرار ندارند.)

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

=COUNTIF(FirstList,B1)=0

 در اینجا فرمول (COUNTIF(SecondList,A1 تعدادآیتم های ناحیه SecondList  که برابر آیتم  سلول A1 هستند را شمارش می کند (به عبارت دیگر آیا آیتم واقع درسلول A1 در SecondList هست یا خیر).

پس اگر =0(COUNTIF(SecondList,A1آیتم واقع در سلول A1 در ناحیه SecondList موجود نیست. و در پایان Conditional formatting آیتم های منحصر به فرد را به رنگ مشخص شده نمایش می دهد.

 راه دوم برای مقایسه لیست ها

یک کپی از صفحه موجود کپی نموده و سپس تمام قوائد conditional formatting  را از آن حذف کنید (انتخاب هر دو ستون –  انتخاب conditional formatting از سربرگ Home- انتخاب clear rules و سپس انتخاب clear rules from selected cells)

ابتدا ناحیه داده ها را انتخاب نمایید.

از سربرگ home ابزار conditional formatting و سپس گزینه Duplicate values را انتخاب نمایید.

از پنجره ظاهر شده زیر عبارت Format cells that contain گزینه Unique را انتخاب نمایید و سپس بر روی دکمه ok کلیک نمایید.

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

انواع تابع Count تحت عنوان‌های: COUNTA-COUNT-COUNTIFS -COUNTIF – COUNTBLANK. به‌صورت کلی تابع Count به‌منظور شمارش تعداد سلول‌ها در ناحیه‌ای مشخص، استفاده می‌شود.

تابع Countدر اکسل:

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

=COUNT(A1:A7)

از بین سلول A1 تا A7، تعداد سلول‌هایی که حاوی مقادیر عددی باشند را می‌شمارد.

تابع COUNTA:

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

=COUNTA(A1:A7)

که تمام سلول‌های حاوی کارکتر را می‌شمارد.

تابع COUNTBLANK:

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

=COUNTBLANK(A1:A7)

تابع COUNTIF:

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

COUNTIF(range,”criteria”)

در قسمت Range، همانند توابع بالا، محدوده سلول‌ها جهت شمارش مشخص می‌شود و در قسمت criteria شرط خود را برای شمارش سلول‌ها درج می‌کنید. مثلاً می‌توانید مشخص کنید که صرفاً سلول‌های حاوی یک مقدار خاص (یک عدد، کلمه و…) شمارش شوند، یا صرفاً سلول‌هایی که ابتدا یا انتهای آنها یک مقدار خاص باشد، شمارش شود. مثلا:

جهت یافتن سلول‌هایی که مقداری برابر با 1000 دارند، از فرمول زیر استفاده می‌کنیم:

=COUNTIF(A1:A7,”1000″)

جهت یافتن سلول‌های حاوی مقداری بزرگ‌تر از 1500، از فرمول زیر را استفاده می‌کنیم:

=COUNTIF(A1:A7,”>1500″)

جهت یافتن سلول‌های متنی که با مقدار X شروع می‌شوند، فرمول زیر را استفاده می‌کنیم:

=COUNTIF(A1:A8,”ITPro*”)

تابع COUNTIFS:

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

COUNTIFS(criteria_range1, “criteria1”, criteria_range2, “criteria2″…)