خطاهای اکسل

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

- تقسیم عدد بر صفر!

اگر با اکسل سروکار دارید حتما با عبارات Div/0#! روبرو شده اید. همانطوری که همه مان می دانیم تقسیم کردن عدد بر صفر در ریاضیات مفهمومی ندارد. این موضوع در نرم افزار اکسل که بر اساس ریاضیات بنا شده است نیز صادق است.

اگر به طور عمد در فرمول نویسی عبارتی را بر صفر تقسیم کنید یا اینکه این عمل سهوا انجام گیرد خروجی خطای #Div! خواهد بود. منظور از سهوا این است که در فرمول نویسی به سلولی ارجاع دهید که خالی هست یا به اصطلاح اکسل Blank cell باشد.

2- رفرنس های نامعلوم!

فرمول نویسی اکسل بدون ارجاع و رفرنس دادن که نمی شود. ولی خطایی که ممکن است بر اثر این رفرنس دادن ها رخ دهد مربوط به حذف این رفرنس ها می باشد. فرض کنید فرمولی نوشته اید بعد از نگارش فرمول، سلولی که به آن ارجاع داده شده است حذف شود یا اینکه جابجا شود خطای!Ref# به صورت خروجی نشان داده خواهد شد.

3- غلط املایی!

3 حالتی که ممکن است خطای!Name# رخ دهد را با هم بررسی کنیم:

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

این 3 حالت معمولا بیشتر اتفاق می افتد.

4- ورودی های عجیب!

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

5- موجود نیست!

یک سری توابع با جستجو مقداری را از یک محدوده به عنوان خروجی نمایش می دهند. حال اگر این مقدار در محدوده ی مورد نظر ما نباشد خطای #N/A نمایش داده خواهد شد.

6- خیلی بزرگ!

خطای Num# معمولا زمانی رخ می دهد که محاسبات فرمول نویسی خیلی خیلی بزرگ یا خیلی خیلی کوچک باشد. حالاتی دیگر نیز این خطا رخ می دهد که خیلی به کار ما نمی آید.

7- فراموشکاری!

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

=sum(I A;By)

در این فرمول قرار است سه کاراکتر I,A,By با هم جمع شوند ولی کاراکتر جداکننده مابین I,A فراموش شده است و خطای Null# به عنوان خروجی نمایش داده خواهد شد. یا اینکه فرمولی را به این نحو بنویسیم I A*By= که کاراکتر ضرب را در بین I,A فراموش کرده باشیم.

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

=A1:A10 A3:E3

خروجی این فرمول سلول A3 که در هر دو ناحیه مشترک هست می باشد حال اگر این دو ناحیه تداخلی نداشته باشند خطای Null# را خواهیم داشت.

8- زمان منفی!

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

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

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

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

طریقه ایجاد TABLE

برای تبدیل یک محدوده به جدول می توان به 2 طریق زیر عمل کرد:

1. محدوده ی سلول هایی که قصد تبدیل آنها به جدول دارید را انتخاب کنید. سپس از طریق Home > Styles > Format As Table با انتخاب یکی از حالات، استایلی برای محدوده انتخاب کنید. در اینجا پنجره ای باز خواهد شد و این سوال را از شما می پرسد که آیا این محدوده را تبدیل به جدول کنم که با زدن دکمه ok محدوده تبدیل به جدول خواهد شد.
2. محدوده را انتخاب کنید و از Insert > Tables > table ابزار table را انتخاب نمایید یا اینکه کلید ترکیبی Ctrl+T را فشار دهید.

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

1. برای جدول ایجاد شده به طور خودکار اکسل نامی در نظر می گیرد. از این نام در فرمول نویسی ها می توانیم استفاده کنیم.
2. اگر در زمان ایجاد جدول چک باکس مربوط به سربرگ را تیک زده باشید، ردیف اول داده ها به عنوان سربرگ جدول در نظر گرفته می شود. نام هر یک از ستون ها نیز با عنوان سربرگ شناخته می شود.
3. زمانی که به سمت پایین حرکت کنید دیگر حروف الفبایی مربوط به نام ستون ها نشان داده نمی شود و به جای آن عنوان سربرگ نمایان خواهد شد.
4. در پایین جدول می توانید ردیف خودکاری اضافه کنید که این ردیف می تواند جمع، شمارش تعداد سلول ها و… را به صورت خودکار انجام دهد. این ردیف با کلیک بر روی جدول و فعال شدن Table Tools از آدرس Design>Table Style Options>Total Row انجام می شود. با زدن تیک چک باکس Total Row ردیف Total اضافه خواهد شد. نوع خروجی که این ردیف قرار است نمایش بدهد از لیست مربوط به هر سلول قابل انتخاب می باشد.
5. جدول قابلیت بزرگ شدن دارد. زمانی که در آخرین سلول جدول مقداری را وارد کنید و بعد از آن بخواهید به ردیف بعدی بروید با نگارش داده ای در سلول، آن ردیف به جدول اضافه خواهد شد. حتی ردیف جمع که در شماره 4 بیان کردیم هم یک ردیف به سمت پایین جابجا می شود.
6. در صورتی می خواهید داده های تکراری را از جدول حذف کنید می توانید با کلیک بر روی سلولی از جدول و فعال شدن Table Tools از Design>Tools>Remove Duplicates حذف کردن داده های تکراری را فعال کنید. پنجره ای باز خواهد شد و این موضوع را باید در این پنجره مشخص کنید که داده ها از کدام ستون بررسی و حذف شوند.
7.  مرتب سازی هوشمندانه در EXCEL  در جدول قابل انجام می باشد. در جدولی که ایجاد کردیم سربرگ آن در حالت عادی به صورت فیلتر در خواهد آمد. با زدن هر کدام از این فیلترها و انتخاب نوع آن می توانیم مواردی که قصد فیلتر آنها را داریم انتخاب کنیم.
8. در صورتی که  خواهیم دکمه فیلتر در سربرگ نمایش داده نشود با فعال کردن table Tools و بعد از آن از Design>Table Style Options>Filter Button چک باکس بودن یا نبودن فیلتر در سربرگ را انتخاب می کنیم.
9. در جداول لازم نیست فرمولی که می نویسیم را برای بقیه سلول ها کپی کنیم بلکه با نوشتن فرمول برای اولین سلول و زدن کلید اینتر بقیه سلول های جدول به صورت خودکار تکمیل خواهند گشت.
10. ابزاری که در جدول خیلی مورد توجه هست، Structure Reference ها هستند. در زمان فرمول نویسی با انتخاب هر سلول، نام عادی آن در نظر گرفته نمی شود بلکه نامی از ستون آن انتخاب می شود که به این حالت خواهد بود:

=[@عنوان سربرگ ستون]

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

Structure Reference از طرفی خوانایی برنامه و از سوی دیگر با جابجایی و انتقال سلول ها بازه های مشخص شده تغییر نخواهند کرد.

فرمول در کسل

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

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

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

 قالب بندی شرطی درEXCEL

• ابتدا سلول های مورد نظر خود را که قصد دارید قالب بندی بر روی آنها اعمال شوند را انتخاب نمایید.
• از تب Home و از گروه Style گزینه Coditional Formmating را انتخاب تا منوی کشویی نمایش داده شود.
• گزینه Highlight Cells Rules را انتخاب زیر منوی نمایش داده شده شامل چندین قانون می باشد. به عنوان مثال اولین قانون برای قالب بندی اعداد بزرگتر از یک مقدار خاص و یا دومین گزینه برای قالب بندی اعداد کمتر از یک مقدار خاص می باشد 

قانون Greater Than را انتخاب کنید. با کلیک بر روی آن پنجره ای نمایش داده خواهد شد. فرض کنید در این مثال قصد داریم اعداد بزرگتر از 5٫000 دلار را با رنگ زرد قالب بندی نماییم. بنابراین عدد 5٫000 دلار را در کادر سمت چپ و از سمت راست گزینه Yellow fill …. را انتخاب نمایید و بر روی Ok کلیک نمایید. خواهید دید که با انجام این کار اعداد بزرگتر از 5٫000 دلار با رنگ زرد قالب بندی خواهند شد.

الگوهای قالب بندی شرطی

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

DataBars

نوارهای افقی هستند که به هر یک از سلول ها اضافه خواهند شد.

Color Scales

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

Icon Sets

در این قالب بندی یک آیکون خاص بر اساس مقادیر موجود در انها اضافه خواهد شد.

نحوه استفاده از الگوهای از پیش تعریف شده

• ابتدا سلول های مورد نظر خود را که قصد دارید قالب بندی بر روی آنها اعمال شوند را انتخاب نمایید.
• از تب Home و از گروه Style گزینه Coditional Formmating را انتخاب تا منوی کشویی نمایش داده شود.
• یکی از گزینه های Databars یا Color Scales یا Icon Sets را انتخاب و از زیر منوی آن گزینه مورد نظر الگوی مورد نظر خود را انتخاب نمایید. قالب بندی شرطی بر روی سلول انتخاب شده اعمال خواهد شد.

نمودار ستونی Column Chart

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

نمودار ستونی خود به دو حالت عمودی (Column Chart) و افقی (Bar Chart) تقسیم می‌شود که هر یک از آنها شامل حالات تجمعی (Stacked Chart) و خوشه‌ای (Clustered Chart) می‌باشند.                                                        

نمودار خطی Line Chart

در صورتی که نقاط ماکسیمم هر میله در نمودار میله ای را با یک خط به یکدیگر متصل کنیم، نمودار میله ای حاصل می‌شود که زمینه های کاربردی همچون نمودار ستونی دارد. در زمانهایی که بازه زمان مورد بررسی مربوط به داده ها بسیار زیاد می‌باشد (مثلا میزان نرخ تورم از سال 1350 تا 1390) این نمودار به عنوان بهترین گزینه می‌باشد.

در محیط اکسل حالتی از نمودار خطی وجود دارد که در آن، مساحت زیر خط به صورت رنگی نمایش داده می‌شود که به آن نمودار سطحی (Area Chart) می‌گویند.

نمودار دایره‌ای Pie Chart

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

لازم به ذکر است که در حالاتی که تعداد قسمتهای نمودار دایره‌ای از 9 قسمت فراتر رود، این نمودار کارایی خود را از دست خواهد داد.

نمودار پراکندگی Scatter Plot

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

 
نمودار حبابیBubble Chart

این نمودار همانند نمودار پراکندگی‌می باشد، با این تفاوت که توانایی نمایش سه ویژگی به صورت همزمان را دارا می‌باشد به گونه‌ای که دو ویژگی X و Y بر روی محورها مشخص می‌گردد و ویژگی سوم با اندازه حباب مشخص می‌شود.

نمودار رادار Radar Chart

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

نمودار شمعی Candel Chart

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

نمودار جعبه ای Box and whisker

این نمودار به منظور بررسی و مقایسه وضعیت دو یا چند جامعه (گروه) متفاوت از داده‌ها از نظر پارامترهای مکانی (میانگین، میانه) و پراکندگی (واریانس) مورد استفاده قرا می گیرد. در نسخه های قبلی اکسل این نمودار به صورت آماده وجود ندارد ولی می توان با افزودن Error Bar به نمودارهای ستونی آن را ایجاد نمود ولی در اکسل 16 (اکسل 2016) این نمودار به صورت آماده در تب Insert و قسمت نمودارها اضافه شده است.

نمودار هیستوگرام Histogram Chart

این نوع نمودار جهت نموایش نوع پراکندگی مقادیر در یک جامعه (گروه) مورد استفاده قرار می گیرد و هدف از استفاده از ان تعیین مرکزیت دادهها و حالت پراکندگی آنها می‌باشد. این نوع نمودار در ورژنهای قدیمی اکسل از طریق افزونه Analysis Toolpak  قابل ترسیم است ولی در نسخه 16 اکسل، این نمودار به مجموعه نمودارهای اکسل اضافه شده است.

نمودار نقشه درختی TreeMap Chart

این نمودار که در نسخه 2016 اکسل، به آن اضافه شده است و جزء نمودارهای نمایش سلسله مراتب میان داده‌ها می‌باشد، جهت نمایش درصد مشارکت هر گروه و زیر گروهها در یک رویداد مناسب می‌باشد، برای مثال اگر بخواهیم مشخص کنیم که کدام گروه از کالاها بیشترین فروش را داشته و در هر گروه کدام کالا سهم بیشتری از فروش را دارد، این نمودار بسیار مناسب می‌باشد.

نمودار خورشیدی SunBurst Chart

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

نمودار آبشاری Waterfall Chart

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

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

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

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

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

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

1 ابتدا نرم‌افزار اکسل را اجرا کنید (در این آموزش ما اکسل 2013 را اجرا کرده‌ایم و گزینه‌های ذکر شده مطابق با منوهای موجود در این نرم‌افزار است؛ اما شما می‌توانید با کمی تغییر همین مراحل را در نسخه‌های دیگر این نرم‌افزار نیز مورد استفاده قرار دهید).

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

3 سلول‌های دربردارنده اطلاعات موردنظر برای رسم نمودار را انتخاب کنید (تمامی سلول‌های موجود در دو ستون مشخص شده در مرحله دو را انتخاب کنید)

4 از ریبون بالای نرم‌افزار به تب INSERT بروید و پس از کلیک روی Insert Bar Chart گزینهClustered Bar را از بخش 2-D Bar انتخاب کنید.

5 تا این مرحله از کار شما یک نمودار ساده در اختیار خواهید داشت که در محورهای X,Y آن تعداد مهره‌های هر رنگ و نام رنگ‌ها مشخص شده است.

6 حال باید روی یکی از نوارهای به‌نمایش درآمده در نمودار کلیک راست کرده و گزینه Format Data Series را انتخاب کنید.

7- در بخش Series Options با کلیک روی آیکون سطل رنگ (Fill and Line)، از منوی به ‌نمایش درآمده گزینه Picture or texture fill را انتخاب کنید.

8 پس از انتخاب گزینه فوق از بخش Insert picture from روی یکی از گزینه‌های Online, File, Clipboard کلیک کنید. (می‌توانید تصاویر را از روی رایانه فراخوانی کنید، از حافظه موقت ویندوز آنها را به‌کار بگیرید یا حتی به‌صورت آنلاین تصویر موردنظرتان را پیدا کنید)

9 در این مرحله باید تصویر موردنظرتان را در برنامه فراخوانی کنید. به‌عنوان مثال شما می‌توانید از گزینه Online کمک بگیرید و عبارت red ball را در کادر جستجو وارد کنید تا تصاویری از مهره‌هایی به رنگ قرمز برای شما یافت شود. با انتخاب تصویر موردنظر و کلیک روی گزینه Insert آن را در نرم‌افزار فراخوانی کرده و برای رفع مشکل اندازه تصویر نیز گزینه Stack را فعال کنید.

10 چنانچه مراحل را بدرستی به انجام رسانده باشید باید یکی از نوارهای نمایش‌ دهنده مهره‌ها به تصویری از مهره‌های قرمز رنگ تغییر کرده باشد. این نوار می‌تواند مربوط به رنگ قرمز باشد. تصویر انتخاب شده براحتی بیننده را قادر می‌سازد تا درک کند چه مهره‌ای، به چه رنگی و در چه تعدادی موجود است. با تکرار عملیات ذکر شده در مراحل 6 تا 9 می‌توانید نوارهای دیگر را نیز با مهره‌هایی به همان رنگ‌ها مشخص کنید.

11 معمولا یک pictograph (نمودار علائم تصویری) به‌صورت مرتب شده از مقادیر بزرگ به کوچک نمایش داده می‌شود. چنانچه شما هم می‌خواهید یک pictograph واقعی و مرتب را در اختیار داشته باشید می‌توانید سلول‌های مربوط به تعداد مهره‌ها را در نرم‌افزار اکسل انتخاب کرده و از کلیک راست روی آنها از بخش Sort گزینه Sort Largest to Smallest را انتخاب کنید.

12 مشاهده می‌کنید که pictograph شما از مقادیر بزرگ به کوچک مرتب شده و با جذابیت بیشتری به‌نمایش درآمده است.

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

• Tab Page:

این Tab شامل قسمتهای زیر است:

1- Orientation: در این قسمت جهت قرار گرفتن متن در کاغذ را تعیین می کنیم. Portrait برای چاپ عمودی یا Landscape برای چاپ افقی است.

2- Scaling: توسط این قسمت می توانیم اندازه کاربرگ خروجی را بدون هر گونه تغییر بر روی نمایشگر بزرگ یا کوچک کنیم. این قسمت شامل گزینه های زیر است:

الف- Adjust to: می توانیم میزان بزرگی را بر اساس درصدی از بزرگی تعیین کنیم. حداقل این میزان 10 و حداکثر ان 400 می باشد.

ب- Fit to: در این قسمت می توانیم تعیین کنیم که اطلاعات ما در چند صفحه گنجانده شود.

3- Paper Size: اندازه کاغذ را می توان تعیین کرد.

4- Print Quality: قدرت تفکیک پذیری یا کیفیت چاپ را تعیین می کنیم که واحد آن DPI(Dot Per Inch) است.

5- First Page Number: شماره گذاری را از صفحه مشخص شده شروع می کند.

• Tab Margins:

توسط اینTab می توان حاشیه های صفحه، سر صفحه و پا صفحه را تعیین کرد. این Tab شامل قسمتهای زیر است:

1- Tab , Bottom , Left, Right: فاصله حاشیه های راست , چپ , پایین و بالا را تعیین می کند.

2- From Edge: محل سر صفحه یا پاصفحه را تعیین می کند (باید کمتر از حاشیه های بالا یا پایین باشد)

3- Center On Page: مرکز فعلی را به را به طور افقی یا عمودی نسبت به حاشیه ها تعیین می کند.

4- Preview: پیش نمایش تنظیمات در این محدوده نمایش داده می شود.

• Tab Header/ Footer:

توسط این Tab می توانیم سر صفحه و پا صفحه ها را وارد کنیم.

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

این Tab شامل قسمتهای زیر است:

1- کادرهای Header , Footer:

در این کادر یک سری Header ها و Footer های آماده و متداول مثل شماره صفحه , نام کاربرگ، نام کاربر، تاریخ و ترکیب این موارد وجود دارد که می توانیم با توجه به نیاز یکی از آنها را انتخاب کنیم.

2- دکمه های Custom Header , Custom Footer:

توسط این دکمه ها میتواتنیم Header ها و Footer های شخصی ایجاد کنیم. با کلیک بر روی این دکمه ها پنجره ای باز می شود که باید محل استقرار پاصفحه یا سر صفحه را بوسیله ماوس انتخاب کرده (یکی از سه قسمت Left, Center, Right). قسمت سمت چپ، چپ چین خواهد شد، محتوای قسمت میانی نسبت به میانه قسمت تنظیم خواهد شد.

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

دکمه هایی که در میانه صفحه برای قالب بندی صفحه قرار داده شده اند عبارتند از:

Format Font: با فشردن این دکمه پنجره Font باز شده و می توان فرمت نوشته ها را تغییر داد.

Insert Page Number: برای درج شماره صفحه

Insert Number Of Pages: برای درج تعداد صفحات

Insert Date: برای درج تاریخ جاری

Insert Time: برای درج زمان جاری

Insert Book Name:برای درج نام Book

Insert Sheet Name: برای درج نام کاربرگ

• Tab Sheet در اکسل:

این Tab شامل قسمتهای زیر است:

1- Print Area: می توانیم ناحیه ای از کاربرگ را برای چاپ انتخاب کنیم یا آدرس آن را تایپ کنیم.

2- Print Titles: در این قسمت می توانیم یک سطر یا یک ستون را به عنوان سطر یا ستونی که میخواهیم در تمامی صفحات به عنوان سطر یا ستون عنوان تکرار شود انتخاب کنیم.

3- Print: این قسمت شامل گزینه های زیر است:

الف- Gridline: به طور پیش فرض خطوط شطرنجی محیط Excel در نسخه چاپی ظاهر نمی شود. برای ظاهر شدن این شبکه کافی است این گزینه را تیک بزنیم.

ب- Black and White: همه چیز را با رنگ سیاه و زمینه سفید چاپ می کند. و رنگها بدون سایه خواهند بود.

ج- Draft Quality: با انتخاب این گزینه قدرت تفکیک پذیری کمتر شده و زمان چاپ کاهش می یابد.

د- Row And Column Heading: به طور پیش فرض عنوان سطر ها (1و2و3و...) و عنوان ستونها (A, B, C, …)در نسخه چاپی ظاهر نمی شود. ولی اگر این گزینه را تیک بزنیم این عناوین در نسخه چاپی , چاپ می شوند.

ه- Comments: در این فسمت می توانیم نحوه چاپ توضیحات را تعیین کنیم:

• None: توضیحات چاپ نمی شوند.

• At End Of Sheet: در انتهای هر Sheet توضیحات چاپ می شوند.

• As Display on Sheet: در محلی که در Sheet قرار دارند چاپ می شوند.

4- Page Order: ترتیب چاپ صفحه ها را برای کاربرگهای چند صفحه ای انتخاب می کنیم.

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

قبل از بیان روش‌های تبدیل آدرس‌های نسبی به مطلق، ابتدا به صورت خلاصه با این دو مدل آدرس‌دهی آشنا می‌شویم:

• آدرس‌ نسبی (Relative Address):

آدرس نسبی یعنی آدرسی که هنگام کپی کردن و جابجایی ثابت نماند و تغییر کند. به طور مثال اگر درون سلولی فرمول C4*10 را بنویسیم و این سلول را چه بصورت دستی و چه با استفاده از قابلیت AutoFill، در سه سلول پایین‌تر کپی کنیم فرمول قبلی به C7*10 تغییر پیدا می‌کند. این تغییر به علت نسبی بودن فرمول است که آدرس C4 به علت جابجا شدن به سه سلول پایین‌تر به C7 تبدیل می‌شود.

• آدرس مطلق (Absolute Address):

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

=$C$4

نکته: علاوه بر فرم‌های فوق الذکر، آدرس‌های ترکیبی یا مختلط (Mixed Reference) نیز داریم که بصورت نیمه‌مطلق - نیمه‌نسبی هستند:

الف) حالت مطلق سطری (Row absolute) مثل C$4: در این حالت فقط سطر به صورت مطلق ارجاع داده شده است. یعنی در صورت کپی شدن این فرمول، نام ستون یعنی ستون C بسته به محل جدید کپی شدن، می‌تواند به صورت نسبی تغییر کند، اما شماره سطر یعنی سطر شماره 4 که قبل از آن $ آمده، همیشه ثابت خواهد ماند.

ب) حالت مطلق ستونی (Column absolute) مثل o$C4: در این حالت فقط ستون به صورت مطلق ارجاع داده شده است. یعنی در صورت کپی شدن این فرمول، شماره سطر یعنی سطر شماره 4، بسته به محل جدید کپی شدن، می‌تواند به صورت نسبی تغییر کند، اما نام ستون یعنی ستون C که قبل از آن $ آمده، همیشه ثابت خواهد ماند.

• روش‌های تبدیل آدرس‌های نسبی به مطلق و بالعکس دراکسل:

روش 1: درج دستی علامت $

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

---------------------------------------

روش 2: با استفاده از کلید F4

اگر قبل یا بعد یا حتی وسط یک آدرس نسبی (C4) کلیک کنید یا چنانچه آنرا انتخاب کنید و سپس کلید F4 را یک بار فشار دهید آدرس مورد نظر به آدرس مطلق (o$C$4) تبدیل می‌شود. اگر همین کلید را برای بار دوم فشار دهید از حالت مطلق به حالت مطلق سطری (C$4) تبدیل می‌شود و اگر برای بار سوم نیز این کلید را فشار دهید از حالت مطلق سطری به حالت مطلق ستونی (o$C4) در می‌آید. فشردن کلید F4 برای بار چهارم موجب می‌شود آدرس به حالت اولیه یعنی نسبی (C4) برگردد.

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

-----------------------------------------------------

روش 3: با استفاده از ماکرونویسی دراکسل

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

یکی از مشکلاتی که کاربران اکسل در هنگام کار با متون با آن مواجه می‌شوند وجود فاصله اضافی در ابتدا، وسط و یا انتهای متن می‌باشد. برای مثال اگر در سلولی کلمه “علی” و در سلول دیگر کلمه “علی ” (همراه با یک فاصله اضافی در انتهای کلمه) ثبت شده باشد، اکسل مقادیر درج شده در این دو سلول را متفاوت در نظر می‌گیرد. این فواصل اضافی را براحتی می‌توان حذف کرد. در ادامه مطلب با آموزش امروز  همراه باشید تا با نحوه انجام اینکار آشنا شوید.

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

حذف فواصل اضافی را به چهار بخش تقسیم می‌کنیم:

• حذف تمامی فواصل اضافی در ابتدا، وسط و انتهای متن در اکسل
  

1- با استفاده از تابع Trim

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

مثلاً فرض کنید در یک ستون اکسل اسامی دانش آموزان یک کلاس را وارد کرده و در حین تایپ کردن به طور ناخواسته در ابتدا، وسط و یا انتهای اسامی فاصله اضافی درج کردید. در هنگام مرتب سازی (Sort) اسامی بر اساس حروف الفبا می‌بینید که اسامی به صورت صحیح مرتب نشده‌اند و از آنجایی که فاصله از نظر کامپیوتر به عنوان یک کاراکتر ذخیره می‌شوند، آن اسامی که دارای فاصله اضافی در اول اسم باشند در ابتدای لیست قرار می‌گیرند. اگر حجم اطلاعات زیاد باشند تشخیص و حذف فواصل اضافی کار آسانی نخواهد بود. برای انجام این کار از تابع Trim به شکل زیر استفاده می‌کنیم.

=Trim(text)

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

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

در یک سلول خالی راست کلیک کنید و گزینه paste Special را انتخاب نمایید.

در پنجره ظاهر شده گزینه Values را تیک بزنید و در پایان بر روی Ok کلیک کنید.

اکنون می‌توانید اطلاعات اولیه را حذف کنید.

توجه: اگر با استفاده از تابع Trim فاصله‌ها حذف نشد از تابع زیر استفاده کنید:

=TRIM(SUBSTITUTE(text,CHAR(160),CHAR(32)))

یا

=TRIM(SUBSTITUTE(text,CHAR(160)," "))

عبارت (CHAR(160 بیانگر کاراکتر فاصله نشکن است که در معرفی شده است.

2- با استفاده از ابزار Kutools for Excel

• حذف فواصل اضافی در ابتدای متن

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

1- با استفاده از فرمول:

با فرض اینکه متنی که می‌خواهید فواصل اضافی ابتدای آن حذف شود در سلول A1 باشد با کپی فرمول زیر در یک سلول خالی، عملیات حذف انجام می‌شود:

=REPLACE(A1,1,FIND(LEFT(TRIM(A1),2),A1)-1,"")

در فرمول فوق از توابع Replace،Find،Left و Trim استفاده شده است که نحوه استفاده از تابع Left در اینجا و دو تابع Replace و Find در اینجا شرح داده شده است و نحوه کار با تابع Trim هم که در بالا ارائه شده است.

2- با استفاده از ماکرو:

کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

• حذف فواصل اضافی در انتهای متن

1- با استفاده از ماکرو:

2- با استفاده از ابزار Kutools for Excel

• حذف فواصل اضافی در ابتدا و انتهای متن، بدون تغییر در وسط متن

1- با استفاده از ابزار Text To Columns (در هر بار اجرا قابلیت حذف فواصل اضافی در سلول‌های یک ستون را دارد.)

ابتدا ستون مورد نظرتان را انتخاب کنید. سپس از تب Data، بر روی گزینه Text to columns کلیک کنید. در گام اول گزینه Fixed Width را انتخاب نمایید و روی دکمه Next کلیک کنید. در گام دوم فلش‌های عمودی را با استفاده از ماوس به انتهای متون سلول‌ها منتقل کنید و روی دکمه Finish کلیک نمایید.

حالا ستون دامنه جدا شده را انتخاب کنید و از تب Data با توجه به نیازتان روی دکمه Sort A to Z یا Sort Z to A کلیک کنید.

پس از کلیک پیغامی ظاهر می‌شود گزینه Expand the selection راتیک بزنید و روی دکمه Sort کلیک کنید. همه ستون‌ها بر اساس لیست دامنه مرتب می‌شود.

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

• بوسیله فرمول نویسی

پس فشردن کلید Enter مشاهده خواهید کرد دامنه جدا می‌شود و با کپی فرمول در سایر ردیف‌ها، در تمام ستون این اتفاق می‌افتد:

عمل مرتب کردن را مشابه حالت قبل انجام دهید

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

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

اکنون از زبانه Insert گزینه Table را کلیک کنید. در این صورت پنجره ای باز می شود که سوال «Where is the data for your table» به معنای «داده های شما برای جدول کجاست؟» از شما پرسیده می شود. در همین پنجره سلول های G7 تا H14 نوشته شده است. با کلیک بر روی دکمه OK جدول شما ایجاد می شود.

همانطور که در تصویر فوق می بینید، زبانه مخفی Design به زبانه های موجود در اکسل اضافه شده است. گزینه های این زبانه برای ویرایش جدول درج شده به کار می رود. مثلا از گزینه های بخش Table Styles برای تفکیک رنگی سلول ها و از گزینه های بخش Table Styles Optionبرای متمایز کردن سلول های داخلی با یکدیگر استفاده می شود.

درج تصویر و اشکال گرافیکی در excel
برای درج تصویر در یک سند اکسل از زبانه Insert بر روی گزینه Picture کلیک کنید. در این صورت پنجره ای باز می شود که می توانید تصویر مورد نظرتان را از حافظه کامپیوتر انتخاب و با کلیک بر روی دکمه OK آن را در سند درج کنید. همچنین برای درج اشکال گرافیکی، از زبانه Insert، بر روی گزینه Shape کلیک کرده و سپس یکی از اشکال موجود را انتخاب کنید.

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

وارد کردن اطلاعات خارجی به برگه ها
همانطور که پیشتر گفته شد، اکسل می تواند داده هایی را از منابع نرم افزاری گوناگون و یا صفحات وب و پرونده های متنی در برگه ها فعال کند. برای وارد کردن اطلاعات از منابع خارجی، از گزینه های بخش Get External Data از زبانه Data استفاده می کنیم.
فرض کنید می خواهیم یک فایل متنی را که در حافظه کامپیوتر ذخیره شده، در برگه اکسل وارد کنیم. برای این کار از گزینه From Text استفاده می کنیم.

وارد کردن یک فایل متنی در اکسل، در سه مرحله انجام می شود. در این مراحل می توان تنظیماتی را پیش از درج اطلاعات انجام داد. پس از انتخاب فایل متنی مورد نظر (Test) بر روی گزینه Import کلیک می کنیم.

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

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

پس از پایان کار، بر روی دکمه Finish کلیک کنید. در این صورت پنجره محاوره ای Import Data باز می شود که آدرس سلولی که قرار است اولین داده را در خود جای دهد در آن نشان داده می شود. با کلیک بر روی دکمه OK اطلاعات متنی در اکسل درج می شود اما پیش از آن می توانید آدرس نخستین سلول را تغییر دهید.

مخفی کردن سطرها، ستون ها و برگه ها در اکسل
گاهی اوقات و در پروژه هایی که از جداول تو در تو استفاده می کنید، لازم است که سطرها، ستون ها و یا برگه ها را مخفی کنید تا حجم زیاد اطلاعات باعث سردرگمی شما نشود. البته پس از مخفی کردن، می توان آنها را مجددا آشکار کرد. برای مخفی کردن سطر، ستون و یا کاربرگ ها، ابتدا باید سطر، ستون و یا کاربرگ مورد نظر را انتخاب کرده، سپس از زبانه Home و از بخش Cells بر روی گزینه Format کلیک کنید.
از منوی باز شده، گزینه Hide and Unhide را انتخاب کنید. در مقابل این گزینه و در بخش نخست آن، سه عبارت Hide Rows و Hide Columns و Hide Sheets قرار دارد که به ترتیب برای مخفی کردن سطر، ستون و برگه به کار می رود.

آشکار کردن سطرها، ستون ها و برگه ها برای آشکار کردن سطرها، ستون ها و برگه ها مراحل بالا را طی کنید، اما در آخرین مرحله و به جای انتخاب گزینه های مخفی کردن (Hide) گزینه های در آوردن سطرها، ستون ها و برگه ها از حالت مخفی (Unhide) را انتخاب کنید تا سلول ها مجددا آشکار شوند.