ایجاد یک گزارش جدول محور در اکسل

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

Microsoft Excel را باز کرده و تب Data را از نوار منو انتخاب کنید.  روی From Other Sources کلیک کرده و گزینه ی From Other Sources را، انتخاب کنید.

در مرحله ی بعدی نام سرور SSAS را تعیین کرده و اعتبارات را وارد کنید. اگر موارد را در ماشین داخلی دارید، می توانید از localhost نیز به عنوان نام سرور استفاده کنید.

 

اگر توانستید به نمونه ی SSAS تعیین شده با موفقیت متصل شوید، در مرحله ی بعد باید قادر به انتخاب دیتابیس باشید و SSAS "Sales" بوده و مکعب Sales را پیدا کنید. مکعب Sales را انتخاب کنید و به مرحله ی بعدی بروید.

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

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

 
پس از انتخاب “PivotTable Report”با گزینه هایی برای انتخاب بعد، ویژگی ها و اندازه گیری ها برای قرار گرفتن در جدول محور، باز خواهد شد. همانطور که در تصویر زیر نمایش داده شده، مقادیر را انتخاب کنید. هدف ما نمایش رده بندی ایجاد شده در بعد Sales Territory  روی ستون های محور، مجموعه ی نام گذاری شده ی Internet Sales Top 25 روی ردیف های محور و اندازه گیری محاسبه ی شده ی Total Sales  در محدوده ی مقادیر می باشد.

پس از ایجاد گزینش های بالا گزارش شما باید مانند تصویر زیر به نظر برسد. با استفاده از ویژگی های موجود در تب Options  در اکسل می توانید این گزارش را فرمت کرده و به آن ظاهر حرفه ای تری بدهید.

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

1- عدم نمایش تبها و شیتها در قسمت پائینی اکسل

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

• روی گزینه file کلیک کنید و به قسمت option بروید.
• در پنجره باز شده به تب Advanced بروید.
• در قسمت Display option for this workbook تیک گزینه Show Sheets Tab را بزنید.

2- حل مشکل اعدادی که به صورت متن وارد شده اند

فرض  کنید در ستون A1 الی A12 مجموعه ای از اعداد را وارد کرده اید و در A13 فرمولی می نویسید که A1:A12 را جمع کند و در کمال تعجب می بینید جواب اشتباه است و با کمی دقت متوجه می شوید که در گوشه بالا سمت راست برخی از سلولها رنگ سبزی جلب توجه می کند که می خواهد یادآوری کند که در این سلول داده عددی با قالب متن وارد شده و در نتیجه در حاصلجمع محاسبه نمی شود. راه حل چیست؟

• شما می توانید تک تک سلولهایی که این مشکل را دارند انتخاب کرده و گزینه Convert to number را بزنید که راه خسته کننده با درصد خطای بالایی است.
• راه دوم این است که در یک سلول که مطمئن هستید از نوع عددی است عدد 1 را وارد کنید و روی سلول کلیک راست کرده و Copy را بزنید و سپس محدوده ای که مشکوک هستید در آن اعداد به صورت متنی ذخیره شده باشند راست کلیک کرده و گزینه Paste Special را بزنید و مانند تصویر زیر گزینه Multiply را انتخاب کرده و ok کنید.

3- حذف فضای خالی ما بین نوشته ها در  اکسل

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

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

=Trim(a1)

4- حذف سلولهای خالی موجود در محدوده در اکسل و یا تغییر نام آنها

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

• کلیدهای ترکیبی Ctrl+G را بزنید
• روی گزینه Special کلیک کنید تا پنجره Go To Special باز شود.
• حال گزینه Blank را انتخاب کرده و OK کنید.
• می بینید که سلولهای خالی به حالت انتخاب در آمده است که می توانید آنها را حذف کنید.
• اگر خواستید آنها را تغییر نام دهید در همان حالت انتخاب متن مورد نظر خود را تایپ کرده و کلیدهای ترکیبی Ctrl+Enter را بزنید.

5- جدا کردن نام و نام خانوادگی که در یک ستون وارد شده و تقسیم به دو ستون

فرض می کنیم ستونی داریم که در آن وارد شده    “نادر بحری تهران” و شما می خواهید این ستون به سه ستون   “نادر” “بحری” “تهران” تبدیل شود.

• ستون مورد نظر خود را انتخاب کرده و کلیدهای ترکیبی Alt+A+E بزیند
• پنجره Convert Text To Column نمایش داده خواهد که مانند فیلم آموزشی عمل کنید

6- مشاهده داده های تکراری در محدوده

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

• محدوده مورد نظر را انتخاب نمائید و کلیدهای ترکیبی Alt+H+L+H+D را بزنید (در تب Home روی Conditional Formatting کلیک کنید) که منوی مربوط به Conditional Formatting باز می شود که باید Duplicate values را انتخاب کرده و نتیجه را ببینید.

7- جایگزین کردن عدد صفر با سلول خالی

• محدوده مورد نظر را انتخاب نمائید روی گزینه File کلیک کنید
• به Option بروید  و از تب Advanced بروید
• در قسمت Display Option for this worksheet تیک گزینه Show a Zero in cells… را بزنید

8- پاک کردن فرمت و تغییراتی که در شیت اعمال کرده اید.

• محدوده مورد نظر را انتخاب نمائید و به تب Home بروید
• در قسمت Editing روی Clear کلیک کنید و گزینه Clear Formatting را انتخاب کنید.

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

1) فرمول‌های اکسل را به شکل دستی وارد کنیم

Long Lists: =SUM(B4:B13)

Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7)

یا ماوس را روی اولین سلول خالی در انتهای لیست‌تان بیاورید (یا هر سلول دیگر) و بر روی علامت «+» کلیک کنید، سپس روی B4 کلیک کنید، دوباره علامت «+» را بزنید و اینبار B5 را کلیک کنید و به همین صورت تا آخر؛ در نهایت کلید اینتر را فشار دهید. اکسل لیست شما را به شکل =+B4+B5+B6+B7 جمع خواهد زد.

2) با کلیک بر روی کلید وارد کردن تابع

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

=COUNT(B4:B13)

اعداد قرار گرفته در دامنه را می‌شمارد

=COUNTA(B3:B13)

تمامی کراکترهای موجود در دامنه را می‌شمارد
3) تابعی را از 1 گروه انتخاب کنید

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

=TODAY()

وارد کردن تاریخ روز.
4) دکمه استفاده‌های اخیر

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

=AVERAGE(B4:B13)

لیست را جمع می‌زند، میانگین را محاسبه می‌کند.
5) توابع خودکار زیر دکمه جمع‌زدن خودکار در اکسل

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

=MA B4:B13)

بیشترین مقدار لیست را نشان می‌دهد،

=MIN(B4:B13)

کمترین مقدار لیست را نشان می‌دهد.

نکته: چنان چه ماوس شما روی سلول خالی زیر دامنه سلول‌ها قرار داشته باشد، اکسل گمان خواهد کرد که این مکان انتهای لیست شما است و آن‌را پررنگ خواهد کرد.

نکته: در میان فرمول‌های پایه، دکمه (AutoSum) «جمع‌زن خودکار» بهترین انتخاب است، زیرا با مشخص شدن خودکار دامنه سلول‌ها فقط کافی است دکمه اینتر را بزنید و نتایج را مشاهده کنید.

نکته: سریع‌ترین راه برای محاسبه مجموع یک لیست قرار دادن ماوس در انتهای لیست و فشار دادن کلیدهای ترکیبی (Alt+) و سپس اینتر است. اکسل لیست را پررنگ کرده و مجموع را محاسبه می‌کند.

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

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

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

1) =DAYS

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

مثال: تاریخ اتمام 12 اکتبر 2015 منهای تاریخ شروع 31 مارچ 2015 = 195 روز

=DAYS(A30,A29)

2) =NETWORKDAYS

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

مثال: تاریخ شروع 31 مارچ 2015 منهای تاریخ اتمام 12 اکتبر 2015 = 140 روز

=NETWORKDAYS(A33,A34)

3)=TRIM

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

مثال: =TRIM به همراه آدرس سلول در داخل پارنتز

=TRIM(A39)

4) =CONCATENATE

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

مثال: =CONCATENATE به همراه (ماه،”space”،روز،”comma space”،سال) بدین شکل موارد ترکیب شده با فاصله و کاما از هم جدا خواهند شد.

فرمول برای وارد کردن تاریخ:

=CONCATENATE(E33,” “,F33,”, “,G33)

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

=CONCATENATE(E37,”-“,F37,”-“,G37)

5) =DATEVALUE

DATEVALUE تمامی فرمول‌های فوق را در غالب یک تاریخ اکسل در می‌آورد، در نتیجه اگر می‌خواهید از این تاریخ‌ها برای محاسبات بعدی استفاده کنید استفاده از این فرمول بسیار مفید خواهد بود. کار با آن آسان است، کافیست DATEVALUE را از لیست فرمول‌ها انتخاب کنید، بر روی فیلد «Date_Text» کلیک کنید، بر روی سلول مورد نظرتان کلیک کنید، روی OK کلیک کنید و کپی را انجام دهید. خروجی شماره سریال‌های اکسل خواهند بود پس باید به نحو مقابل فرمتی برای لیست‌تان انتخاب نمایید: Format>Format Cells>Number>Date

=DATEVALUE(H33)

3 روش مفید اضافی برای کار با فرمول‌ها

• روش اول: برای تبدیل متن یا عدد به فرمول‌های اضافی نیاز ندارید. فقط کافیست آن‌ها را کپی کنید و به عنوان نوع‌ خاص (Special>Values) پیست‌شان کنید. چرا با استفاده از فرمول‌های تبدیل نوع خودتان را اذیت کنید؟ شاید با اینکار محتوای سلول‌ها شبیه به شماره تلفن‌ به نظر برسند اما در واقع فرمول هستند که نه می‌شود جا‌به‌جایشان کرد و نه می‌توان تغییرشان داد.
• روش دوم: اگر از روش فوق برای تبدیل نوع تاریخ‌ها استفاده کنید (Paste>Special>Values)، نتیجه متن شده و قابل تبدیل نوع تاریخ واقعی نخواهد بود. تاریخ‌ها به فرمول DATEVALUE نیاز دارند تا به صورت تاریخ‌های واقعی دربیایند.
• روش سوم: فرمول‌ها همیشه با حروف بزرگ نمایش داده می‌شوند، اما اگر آنها را با حروف کوچک تایپ کنید، اکسل به صورت خودکار آنها را به حروف بزرگ در‌می‌آورد. همچنین به یاد داشته باشید که هیچ فاصله‌ای در فرمول‌ها و توابع وجود ندارد. اگر فرمولی کار نکرد مطمئن شوید که فاصله‌ای بین نوشته‌ها وجود نداشته باشد

برنامه های متفاوتی می‌توانند برای رسم نمودار به کار برده شوند. پرکاربردترین، آسان ترین و جامع ترین برنامه موجود، برنامه‌ی Excel از مجموعه‌ی Microsoft Office می‌باشد. این نرم افزار هرچند حرفه‌ای نیست و خیلی از پژوهش‌گران از برنامه های دیگری مثل SPSS‌ یا msTAT استفاده می‌کنند، اما شروع خوبی برای یادگیری کاربرد نمودارها و رسم آن‌ها به کمک کامپیوتر می‌باشد.

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

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

نمودار میله ای در اکسل:

زمانی که داده ها یا متغیرهای گسسته یا دسته بندی شده داریم، نمودار میله‌ای یک نمودار واضح برای نمایش داده‌هاست. برای نمونه تعداد فرزندان خانواده یا تعداد افرادی که به یک سوال پاسخ درست داده‌اند، داده‌های گسسته‌اند. متغیر دما را می‌توان به عنوان نمونه به 3 دسته‌ی 34-25، 44-35 و 54-45 طبقه بندی کرد. برای تحلیل خیلی از پرسش‌نامه‌ها نیز نمودار میله‌ای می‌تواند مناسب باشد.

برای کشیدن نمودار در Excel داده‌ها را وارد کنید:

پس از آن ستون فراوانی‌ها را انتخاب کنید.

آنگاه از منوی بالا سربرگ Insert را انتخاب کنید. انواع نمودار را در این جا می‌بینید. برای کشیدن نمودار میله‌ای از الگوی Column یا Bar استفاده کنید.

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

در وضعی که بیش‌تر از 1 متغیر وجود داشته باشد نیز می‌توان از نمودار میله‌ای استفاده کرد. برای مثال اگر غلظت یک ماده را در سه دمای مختلف اندازه‌گیری کرده باشیم، برای رسم نمودار میله‌ای داده‌ها را وارد می‌کنیم:

متغیرهای مورد نظر را انتخاب کنید. دقت داشته باشید که متغیر دما را انتخاب نکنید زیرا احتیاج نیست و نباید درون نمودار کشیده شود. سپس در قسمت نام‌گزاری محورهای نمودار می‌توانید آن را در نظر بگیرید.

دوباره با رفتن به قسمت insert و انتخاب Column یا Bar نمودار میله‌ای مورد نظرتان را کلیک کنید. با انتخاب روی نمودار مورد نظر، نمودار میله‌ای کشیده می‌شود.

نمودار دایره‌ای در excel:

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

برای کشیدن نمودار در Excel ابتدا داده‌ها را وارد کنید:

با رفتن به برگه Insert و کلیک کردن Pie نمودار دایره‌ای مورد نظرتان را انتخاب کنید. با کلیک روی آن، نمودار دایره ای کشیده می‌شود.

نمودار پراکنش:

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

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

بعد ستون‌های مورد نظر را انتخاب می‌کنیم.

با رفتن به برگه Insert و انتخاب الگوی Scatter نمودار مورد نظرتان را  با کلیک روی آن انتخاب کنید. سپس نمودار پراکنش نمایش داده می‌شود.

هم‌چنین برای پراکندگی داده‌هایی که دارای دو صفت هستند و مقایسه‌ی فراوانی، این نمودار مطلوب به نظر می‌رسد.

نمودار خطی:

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

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

این نمودار پرکاربردترین نمودار در Excel است.

برای تغییر مشخصات نمودار می‌توانید روی آن کلیک کنید. در بالای صفحه، قسمت منو، سربرگ‌های Design، Layout و Format نمایش داده می‌شوند. از آن‌ها برای تغییرات گوناگون در نمودارتان استفاده کنید.

به طور خلاصه می توان کاربردهای Data Validation را بصورت زیر عنوان کرد:

• ایجاد لیستهای کشوئی یا Drop-Down که به واسطه آن کاربران داده های خود را سریعتر، دقیقتر و با اشتباه کمتر وارد می نمایند، در ضمن به واسطه آن کاربران ملزم به انتخاب یک داده مشخص و از پیش تعیین شده در لیست می باشند.
• ایجاد محدودیت برای ورود داده در سلولها بر اساس شروط مختلفی از قبیل (کوچکتر یا بزرگتر بودن از مقدار خاصی، دقیقاً برابر بودن با مقداری خاص، مابین دو مقدار خاص بودن و …)
• ایجاد راهنما برای ورود داده ها در سلولها
• نمایش پیغام خطا هنگام رعایت نکردن شروط مذکور و …                                                

  ---

  ایجاد لیستهای کشوئی یا Drop-Down دراکسل:
  برای تفیهم بهتر موضوع، کاربرگ یا Sheet زیر را در نظر بگیرید:

   

  در کاربرگ مذکور، ستونهائی از قبیل محل تولد، مدرک و رشته تحصیلی می توانند مواردی از پیش تعیین شده و مشخص باشند که برای آنها از لیستهای کشوئی یا Drop-Down List استفاده می نمائیم. بدین منظور ابتدا می بایست داده هایی که در هر لیست هستند در ستونی جداگانه از کاربرگ جاری و یا در کاربرگی دیگر درج گردند. به عنوان نمونه همانند تصویر زیر، لیست محل تولد را که دربرگیرنده نام استانهای کشور است در کاربرگی دیگر ایجاد می کنیم:

  لهای مورد نظر را برای ایجاد لیست انتخاب می کنیم و بر روی گزینه Data Validation کلیک می کنیم:

   

  در ویزارد Data Validation در تب Setting همانگونه که ملاحظه می نمائید در قسمت Allow گزینه Any value بصورت پیش فرض انتخاب شده است یعنی ورود هر داده ای در سلولهای انتخاب شده مجاز می باشد.
  حال برای ایجاد لیست کشوئی در قسمت Allow همانند تصویر زیر گزینه list را انتخاب می کنیم، با انجام این کار فیلدی با عنوان Source نمایان می شود که برای آدرس دهی به همان لیست ایجاد شده از قبل (استانهای ذکر شده برای محل تولد) می بایست مورد استفاده قرار گیرد، برای این منظور شما می توانید آدرس دادهها را در کادر تایپ نمائید و یا با استفاده از آیکن مشخص شده در کنار کادر، با استفاده از اشاره گر موس ناحیه داده ها را به حالت انتخاب درآورده و طبق تصویر زیرین بر روی آیکن مشخص شده کلیک کنید:

   

   

  
  با انجام این کار آدرس ناحیه انتخاب شده همانند تصویر در کادر Source قرار می گیرد:

   

  حال بر روی OK کلیک کلیک کنید، زین پس طبق تصویر زیر در کاربرگ خود و در ستون محل تولد، در کنار هر سلول شکل مثلثی ظاهر می گردد که با کلیک کردن بر روی آن، قادر به انتخاب یک استان از لیست کشوئی می باشید:

   

  ---

  گزینه In-cell DropDown :
  حال در ستون مدرک نیز مطابق موارد مذکور در بالا، لیستی شامل موارد (زیر دیپلم-دیپلم-کاردانی-کارشناسی-ارشد-دکتری) را ایجاد می کنیم، اما این بار تیک گزینه In-cell DropDown را بر می داریم:

   

  با انجام این کار طبق تصویر زیر، شکل مثلث جهت باز شدن لیست کشوئی نمایش داده نمی شود و شما می بایست مقدار مورد نظر خود را تایپ نمائید. حال اگر مقدار وارد شده با یکی از موارد موجود در لیست مطابقت داشته باشد، داده ثبت می شود، اما اگر در کوچکترین حرفی مطابقت نداشته باشد، خطای (The value you entered is not valid) ظاهر می گردد:

   

  نکته: پس از انجام تنظیمات فوق، در حالی که سلولهای مورد نظر در حالت انتخاب قرار دارند، با کلیک کردن مجدد بر روی گزینه Data Validation در Ribbon قادر به تغییر تنظیمات مذکور خواهید بود، به عنوان نمونه با فعال کردن گزینه In-cell DropDown در سلولهای ستون مدرک، مجدداً لیست کشوئی برای این سلولها همانند تصویر زیر نمایان خواهد شد:

   

   

  ---

  حال فرض کنید، ستونی با عنوان «کد ملی» دارید که می بایست در هر سلول دقیقاً 10 رقم جهت کاهش خطا درج گردد، بدین منظور همانند تصویر زیر ابتدای سلولهای مورد نظر در ستون کد ملی را انتخاب می کنیم و پس از انتخاب گزینه Data Validation از منوی Data، در تب Setting، گزینه Text Length را در قسمت Allow، گزینه Equal To را در قسمت Data و مقدار 10 را در قسمت Length تنظیم می نمائیم:

   

  پس از انجام تنظیمات فوق، در هریک از سلولهای ستون کد ملی، صرفاً داده ای با طول 10 کاراکتر می بایست وارد شود در غیر این صورت پیغام خطائی ظاهر می گردد:

   

  ---

  تب Input Message:
  حال برای جلوگیری از بروز خطا در وارد کردن اطلاعات می توانید با استفاده از تب Input Message راهنمائی لازم را انجام دهید به عنوان نمونه همانند تصویر زیر برای ستون کد ملی پیام زیر را در نظر می گیریم که با انتخاب هر یک از سلولهای این ستون ظاهر می گردد

   

  ---

  Circle Data Validation در اکسل :
  حال فرض کنید در برخی از ستونها از قبل داده هایی وارد شده داشته باشیم و پس از آن نسبت به اعمال Data Validation اقدام کنیم، به عنوان نمونه همانند تصویر زیر در ستون «رشته تحصیلی» از قبل داده هایی وارد می کنیم و سپس نسبت به اعمال محدودیت تعداد کاراکترهای مجاز بین 6 تا 20 اقدام می کنیم:

   

  همانگونه که ملاحظه می نمائید برخی از سلولها دارای شرط مذکور نمی باشند، برای مشخص شدن این سلولها کافیست طبق تصویر زیر گزینه Circle Data Validation را انتخاب کنید، با انجام این کار سلولهای فاقد شرط ذکر شده، با کادری قرمز رنگ مشخص می گردند:

   

  حال اگرزیر بر روی گزینه Clear Validation Circle کلیک کنید، این کادرها حذف خواهد گردید.

   

  علاوه بر موارد مذکور در این آموزش، همانند موارد مذکور، از طریق Data Validation قادر به ایجاد محدودیتهای دیگری در داده های ورودی در سلولهای اکسل خواهید بود از جمله؛ ایجاد بازه های عددی، زمان و تاریخ:

   

روش ساده تایپ عدد فارسی در اکسل که اکثر کاربران از آن استفاده می کنند، استفاده از Reginoal & Language می باشد. برخی از کاربران بر این باورند که تنظیم اکسل همانند تنظیم Word می باشد. ولی با اندکی دقت متوجه می شویم که راه فارسی کردن اعداد در برنامه Excel متفاوت می باشد.
اما یک راه کار بسیار ساده برای تایپ اعداد به شکل فارسی اینست که فونت خانه ای که درون آن عبارت خود را تغییر میدهیم را به: B lotus B Nazanin تغییر دهیم. و یا کلا از فونتهایی استفاده کنیم که با حرف B آغاز می شود.

راه حل کاملتر برای تنظیم اعداد فارسی در اکسل

برای انجام اینکار به قسمت Format Cell بروید و گزینه ی Custom را انتخاب کنید و کد زیر را برای فارسی کردن اعداد وارد کنید:
[$-3020429]0  در این روش نیاز به نصب فونت نیست.با فونت arial هم کاملا خوانا می شود.

حل مشکل حرف ی در اکسل

ممکن است حرف "ی" بروی کیبوردتان مدل های مختلفی داشته باشد یا فایل های موجود شما دارای چند مدل از حرف "ی" باشد که هر کدام از این مدل "ی" ها دارای کد خاصی باشند.
این مشکل راه حلهای مختلفی دارد از راههای وقت گیر و زمان بر باید راه کدنویسی را در پیش بگیریم.
یکی از راههای به وجود آمدن این مشکل فایلهای فارسی ساز ویندوز است که نسخه های مختلفی دارند. برای حل این مشکل می توانید از فونت های اصلی ویندوز مثل Tahoma استفاده کنید یا اینکه از فونت هایی که اول آنهاB است همچنین می توانید از فونتهای B zar و یا B zar , B Lotus , B Nazanin استفاده کنید.
اگر با اینکار مشکل حل نشد باید ویندوز خود را عوض کنید و در طی مراحل نصب ویندوز زبان فارسی را انتخاب کنید خوب است بدانید در اکثر مواقع این مشکل زمانی به وجود می آید که زبان فارسی را از داخل محیط ویندوز انتخاب می کنید.

اصطلاحات پر کاربرد نمودارها در اکسل  :
1-         Gird Line: خطوطی هستند که در امتداد مقادیر محور X , Y ظاهر می شوند. این خطوط در تعیین مقدار دقیق یک نقطه به ما کمک می کنند.
2-         Tick: خطوط کوتاهی هستند که با محور متقاطع بوده و قسمتهایی از یک مقیاس سری با گروه را جدا می کنند.
3-         Chart Title: متن بکار برده شده برای شناسایی عنوان نمودار می باشد.
4-         Axis: به محور‌ها در نمودار‌ها گفته می شود.
5-         Legend: هر نمودار دارای راهنمایی است که نشان دهنده این است که هر رنگ مربوط به کدام سری است.
 
روشهای ایجاد نمودار  در  excel:
روش اول:
1-                انتخاب منوی Insert
2-                انتخاب گزینه Chart
روش دوم:
1-    انتخاب آیکون Chart Wizard
نکته: در زمان ایجاد نمودار با کلیک بر روی دکمه Next به صفحات بعدی میرویم و کلیک بر روی دکمه Back باعث برگشت به صفحه قبلی می شود و با کلیک بر روی Finish کادر نمودار‌ها بسته می شود. نکته می توانیم اطلاعاتی را که میخواهیم بر اساس آن نمودار رسم کنیم ابتدا انتخاب کنیم بعد به یکی از دو روش بالا عمل کنیم. می توانیم هم در حین کار انتخاب را انجام دهیم.
با انجام یکی از دو راه بالا پنجرهای زیر به ترتیب باز می شود:
-          پنجره Chart Type: این پنجره شامل دو Tab زیر می باشد:
1)         Standard Type: که شامل قسمتهای زیر است:
الف)  Chart Type: که در این قسمت نوع اصلی نمودار را انتخاب می کنیم.
ب)   Chart Sub-Type: در این قسمت نوع فرعی نمودار را مشخص می کنیم.
ج)    Press and Hold to View Sample: در این قسمت پیش نمایشی از نمودار با اطلاعاتی که خودمان داده ایم نشان می دهد. برای این کار کافی است روی این دکمه Click کرده و نگه داریم.
2)         Custom Type: در این قسمت می توان از نمودارهای سفارشی استفاده کرد یا یک نوع نمودار ایجاد کرد.
 
-          پنجره Chart Source Data: این پنجره شامل دو Tab زیر می باشد:
1)         Data Range: که شامل قسمتهای زیر است:
الف) Data Range: در این قسمت آدرس داده هایی را که قرار است نمودار برای آنها کشیده شود، می توان تغییر داد همچنین می توانیم آدرس را با کلیک بر روی Collapse Dialog و با Drag کردن وارد کنیم. و یا می توانیم با استفاده از فرمت زیر آدرس را تایپ کنیم. شماره سطر پایان $ نام ستون پایان$ شماره سطر شروع$ نام ستون شروع$! نام Sheet =
ب) Series in: این قسمت جهت نمایش داده‌ها را مشخص می کند. یعنی کدام یک از این دو برچسب از داده‌ها در پایین نمودار قرار خواهد گرفت. اگر Row را انتخاب کنیم سطر‌ها به عنوان سری و اگر Column را انتخاب کنیم ستونها به عنوان سری قرار می گیرند.
2)         Series: که شامل قسمتهای زیر است:
الف) Series: در این قسمت نام سری‌های موجود نوشته شده است.
ب)   Name: توسط این قسمت می توانیم نام سری را عوض کنیم.
ج)    Value: در این قسمت می توانیم آدرس مقادیر هر سری را مشخص کنیم. همچنین می توانیم مقادیر را تایپ کنیم. برای تایپ مقادیر باید مقدار سری را در علامت { } وارد کنیم.
د)    Category(x) Axis labels: در این قسمت می توان آدرس خانه هایی را داد که محتوای آنها بر روی محور X نوشته می شوند.
ه)     دکمه Add: یک سری جدید ایجاد می کند.
و)     دکمه Remove: بر روی نام هر سری در قسمت Series کلیک کنیم و سپس این دکمه را بزنیم، سری حذف می شود.
-          پنجره Chart Option: این پنجره شامل 6 Tab زیر است:
1)             Titles: این قسمت شامل موارد زیر است:
الف) Chart Title: در این قسمت عنوان نمودار را مینویسیم.
ب)   Category(x) Axis labels: در این قسمت عنوان محور X را مینویسیم.
ج)    Value (Y) Axis: در این قسمت عنوان محور Y را مینویسیم.
2)             Axes: این قسمت شامل گزینه‌های زیر است:
الف)  Category(x) Axis اگر در کنار این کادر تیک خورده باشد، مقادیر روی محور X نشان داده می شوند و اگر تیک نخورده باشد نشان داده نمی شوند.
ب)   Value (Y) Axis اگر در کنار این کادر تیک خورده باشد، مقادیر روی محور Y نشان داده می شوند در غیر این صورت نمایش داده نمی شوند.
3)             Grid Line: که این Tab شامل قسمتهای زیر است:
الف)  Category(x) Axis که خود شامل دو قسمت است:
×      Major Gridline: اگر این قیمت تیک خورده باشد، خطوط شبکه رسم می شوند (تمامی خطوط رسم نمی شوند)
×      Minor Gridline: اگر این قسمت تیک خورده باشد خطوط دیگری بین خطوط بالا رسم می شوند. این دو قسمت برای خطوط شبکه موازی محور Y‌ها هستند.
ب)   Value (Y) Axis: که شامل دو قسمت زیر است:
×      - Major Gridline: خطوط شبکه را به موازات محور X رسم می کند.
×      - Minor Gridline: خطوط دیگری را به موازات محور X بین خطوط بالا رسم می کند.
4)             Legend: این Tab شامل قسمتهای زیر است:
الف)  Show Legend: اگر این گزینه تیک خورده باشد راهنما نمایش داده می شود.
ب)   Placement: مکان راهنما را نشان می دهد. Bottom: پایین، Corner: گوشه بالا سمت راست، Top: بالا، Right: سمت راست، Left: سمت چپ
5)             Data Label: شامل قسمتهای زیر است:
الف) None: هیچ مقداری را بر روی ستونها نمایش نمی دهد.
ب)   Show Value: مقدار هر سری رابر روی ستون آن نشان می دهد.
ج)    Show Label: برچسب هر سری را روی آن نشان می دهد.
د)    Legend Key Next to Label: اگر یکی از حالتهای ب و ج را انتخاب می کنیم. این گزینه فعال شده و در کنار هر مقدار رنگ سری نمایش داده می دهد.
6)              Data Table: شامل قسمتهای زیر است:
الف)  Show Data Table: اگر این گزینه فعال شود، جدولی مشابه آنچه که ما، در Sheet کشیده ایم در زیر نمودار ظاهر می شود.
ب)   Show Legend Keys: اگر گزینه الف فعال باشد، این گزینه نیز فعال می شود. و اگر در کادر آن تیک بزنیم، رنگ هر سری را در کنار اطلاعات آن سری در جدول نشان می دهد.
نکته:   باید توجه داشته باشیم که که با توجه به نوع نمودار گزینه‌های موجود در این پنجره می توانند متفاوت باشند.
-           پنجره Chart Location:
الف)  As New Sheet: این گزینه نمودار را در یک Sheet جدید با اسم دلخواه (اسم پیش فرض Chart 1 است) ایجاد می کند. نموداری که در این حالت ایجاد می شود، قابل جابجا شدن و تغییر سایز نیست ولی هر گونه تغییرات بر روی اطلاعات اصلی بر روی نمودار تاثیر دارد.
ب)   As Object in: این گزینه نمودار را در هر Sheet که ما انتخاب می کنیم، (از Sheet‌های موجود) رسم می کند و قابل جابجا کردن و تغییر سایز نیز می باشد.
 
تغییر بر روی نمودار ایجاد شده:
برای تغییر نمودار ایجاد شده باید ابتدا نمودار را انتخاب کرد. سپس مجدداً دکمه Chart Wizard راکلیک کرد. یا از روی Toolbar، Chart که باز می شود، تغییرات را اعمال کرد.
 
نوار ابزار Chart در اکسل:
این نوار ابزار را می توانیم در صورتی که فعال نبود با Right Click بر روی نوار ابزار‌ها و انتخاب گزینه Chart فعال کنیم این نوار ابزار دارای آیکونهای زیر است:
×      Chart Object: در این قسمت می توانیم قسمتی از نمودار را که میخواهیم بر روی آن تغییر دهیم انتخاب کنیم. با انتخاب هر گزینه قسمت مربوط به آن روی نمودار انتخاب می شود.
×      Format: با توجه به اینکه در Chart Object چه انتخاب شده باشد این آیکون پنجره Properties آن را باز می کند.
نکته: با Double کلیک کردن بر روی هر موضوع از نمودار نیز پنجره Properties آن باز می شود.
×      Chart Type: در این قسمت می توانیم نوع نمودار انتخاب شده را تغییر دهیم.
×      legend: توسط این آیکون کادر راهنما را ظاهر یا پنهان می کنیم.
×      Data Table: توسط این آیکون می توانیم جداول داده‌ها را فعال یا غیر فعال کنیم.
×      By Rows:با انتخاب این آیکون سطر‌ها به عنوان سری انتخاب می شوند.
×      By Column: با انتخاب این آیکون ستونها به عنوان سری انتخاب می شوند.
×      Angle Text up: جهت متن نمودار را تغییر می دهد. یعنی متن از بالا به پایین، کج نوشته می شود. به شرطی این آیکون فعال است که قسمتهای متنی نمودار انتخاب شده باشد.
×      Angle Text down: متن نمودار را تغییر جهت می دهد. یعنی متن از پایین به بالا و کج نوشته می شود. به شرطی این آیکون فعال است که قسمتهای متنی نمودار انتخاب شده باشد.
تغییر اندازه:
1)         روی نمودار کلیک می کنیم تا انتخاب شود.
2)         روی یکی از مربع‌های سیاه اطراف نمودار کلیک می کنیم.
3)         مربع را تا زمانی که به اندازه دلخواه تغییر اندازه دهد Drag می کنیم.

برای تغییر پهنای ستون به یکی از دو روش زیر عمل می کنیم:
روش اول:
کافی است به مرز بین ستون مورد نظر و ستون بعدی رفته تا مکان نما به صورت یک فلش دو سر در آید سپس مرز ستون را در جهت مورد نظر Drag می کنیم.
روش دوم:
در این روش پهنا را به صورت دقیق تنظیم می کنیم. برای اینکار مراحل زیر را طی می کنیم:
1-    انتخاب منوی Format
2-    انتخاب زیر منوی Column
3-    انتخاب گزینه Width
4-    در کادر باز شده مقدار دلخواه را وارد می کنیم.
نکته: به طور پیش فرض پهنای ستون 8.43می باشد.
 
تغییر پهنای استاندارد ستون:
میدانیم که پهنای استاندارد ستونها 8.43 می باشد اگر بخواهیم این پهنای استاندارد را کم یا زیاد کنیم به ترتیب زیر عمل می کنیم: (فقط به یاد داشته باشید این تغییر بر روی ستونهایی اعمال می شود که پهنای آنها را تغییر نداده باشیم.)
1-    انتخاب منوی Format
2-    انتخاب زیر منوی Column
3-    انتخاب گزینه Standard Width
4-    در کادر باز شده پهنای مورد نظر را وارد می کنیم.
 
جستجوی داده ها:
اگر بخواهیم رشته کاراکترهای خاصی را در کاربرگ خود پیدا کنیم، مشاهده هر یک از سلولها کار سختی خواهد بود. پیدا کردن یک رشته از کاراکتر‌ها که باید تغییر یابند نیز مشکل تر می باشد. اکسل ویژگیهایی دارد که به ما در یافتن داده‌ها و تعویض آنها کمک می کند.
 
استفاده از Find: برای فعال کردن Find به یکی از دو روش زیر عمل می کنیم:
روش اول:
1- انتخاب منوی Edit
2- انتخاب find
روش دوم:
فشردن همزمان کلید‌های Ctrl F
جهت جستجوی داده‌ها مراحل زیر را انجام میدهیم:
1-        لغتها یا عددهایی را که میخواهیم مکان یابی کنیم در کادر متن Find What وارد می کنیم.
2-        دکمه Find Next را کلیک می کنیم و Excel اولین مورد از شرط جستجو را پیدا می کند و آن را انتخاب می نماید. کادر مکالمه Find بر روی صفحه نمایش باقی می ماند تا بدین ترتیب بتواند جستجو برای متن یا اعداد مشخص شده را ادامه دهیم.
3-        جهت جستجو برای مورد بعدی داده‌های مشخص شده Find Next را مجدداً کلیک می کنیم.
4-        جهت برگشت به محیط دکمه Esc را فشرده و یا بر روی دکمه Close کلیک می کنیم. تا کادر مکالمه Find بسته شود و سپس با داده‌های داخل سلول به کارمان ادامه میدهیم.
5-        جهت جستجو پس از بستن کادر مکالمه، با فشار دادن Ctrl F کادر مکالمه را می توانیم مجدداً باز نماییم.
نکته: اگر محدوده ای از سلول‌ها را در یک کاربرگ , قبل از باز نمودن کادر مکالمه Find را انتخاب کنیم، جستجو را برای این سلولها محدودکرده ایم.
 
برقراری شروط بیشتر برای Find:
کادر مکالمه Find با ارائه گزینه‌های زیر جستجو را با تخصصی تر نمودن آن محدود می سازد.
-      Search: می توان جهت جستجو را مشخص کرد. جستجو به صورت سطری باشد (By Row) یا به صورت ستونی باشد
-      By Column: با استفاده از این گزینه می توانیم جستجو را به یک عنصر خاص محدود کنیم در این کادر گزینه‌های Formula , Values, Comments را خواهیم داشت.
-      Match Case: با انتخاب این گزینه محیط به حروف بزرگ و کوچک حساس شده و کلماتی را پیدا می کند که از نظر حروف بزرگ و کوچک مطابق متن تایپ شده در کادر Find What باشد.
-      Find Entire Cell Only: این گزینه از Excel میخواهد سلولهایی را که داده‌های اضافه بر رشته کاراکتر جستجو دارند نادیده بگیرد.
 
تعویض داد ه‌ها استفاده از Replace:
اگر بخواهیم داده پیدا شده توسط Find را با داده‌های دیگر جایگزین کنیم می توانیم پس از هر بار جستجو پنجره جستجو را بسته، ویرایش را انجام داده، دوباره پنجره Find را باز کنیم و عمل جستجو را ادامه دهیم که این کار وقت گیر می باشد. Excel دستوری ساده برای انجام این کار در نظر گرفته است. با استفاده از Replace می توانیم این کار را انجام دهیم. جهت انجام جایگزینی مراحل زیر را انجام میدهیم:
1-    متن جستجو را در کادر Find What وارد می کنیم.
2-    متن تعویضی را در کادر متن Replace With وارد می کنیم.
3-    جهت تایید هر یک از تعویض‌ها مراحل زیر را دنبال می کنیم.
الف)     Find Next را کلیک کرده، Excel به مورد بعدی داده جستجو حرکت می کند و سلول را انتخاب می کند.
ب)      در صورتی که بخواهیم تعویض را انجام دهد دکمه Replace را کلیک می کنیم.
ج)       Find Next را جهت حرکت به موردبعدی کلیک می کنیم.
د)       جهت تعویض تمام موارد داده‌های جستجو با با دادههای تعویضی، Replace All را کلیک می‌کنیم.
 
پر کردن خودکار چند خانه در  excel
پر کردن خودکار چند خانه یکی از مفید ترین ویژگیهای برنامه Excel، قابلیت پر کردن داده‌های متوالی بصورت خودکار می باشد. که به این عمل Auto fill گویند. داده‌های متوالی می توانند مجموعه ای از داده‌ها بصورت 1و2و3و... یا 4و5و.... یا از حروف A و B و C و... تشکیل شده باشند. همچنین می تواند تاریخ یا روزهای هفته یا ماههای سال باشند. حتی می توان مجموعه ای از داده‌های خاص را تعریف کرد. برای این کار مراحل زیر را انجام میدهیم:
1-    چند نمونه اولیه از مقادیر متوالی مزبور را مینویسیم.
2-    این مقادیر را انتخاب می کنیم.
3-    مکان نمای ماوس را به گوشه پایین سمت راست قسمت انتخابی می آوریم تا به شکل ()در آید. به اشاره گر ماوس در این حالت Fill Handle می گوییم.
4-    حال به طرف پایین یا راست Drag می کنیم.
5-    به این ترتیب با رها کردن کلید ماوس، خانه‌های مورد نظر با مجموعه داده‌های متوالی پر می شوند.
 
ایجاد لیست جدید خودکار:
اگر برای حروف الفبا Auto fill را اجرا کنیم، پر کردن خودکار انجام نمی شود. چون حروف الفبا جزء لیستهای آماده نیست پس باید ایجاد شود. برای ایجاد لیست جدید مراحل زیر را انجام میدهیم:
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب Custom list: Tab
4-    انتخاب New List در کادر Custom List
5-    تایپ اطلاعات در قسمت List Entries (بعد از وارد کردن هر اطلاعات Enter می کنیم.)
6-     انتخاب دکمه Add
7-    OK
ایجاد یک لیست خودکار بر اساس لیست موجود در صفحه در EXCEL:                                                 اگر لیستی بر روی صفحه داشته باشیم و بخواهیم بصورت لیست دائمی در آوریم باید مراحل زیر را انجام دهیم.:
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب Custom list: Tab
4-    روی دکمه Collapse dialog که در انتهای کادر فهرست Import List From Cells قرار دارد Click می کنیم. تا پنجره مینیمایز شود.
5-    انتخاب لیست‌ها در صفحه
6-     روی دکمه Collapse Dialog که در انتهای کادر فهرست Import list From Cell قرار دارد Click می کنیم. تا Resize شود.
7-    انتخاب دکمه Import
8-    OK
نکته: می توانیم بجای انجام مراحل 4و 5و 6، در کادر Import، آدرس سلولهایی که لیست در آنها قرار دارد را تایپ کنیم. ویرایش لیست خودکار می توانیم لیستهایی را که ایجاد کرده ایم ویرایش کنیم. یعنی اطلاعاتی را حذف کنیم، ویرایش کنیم یا اضافه کنیم. برای این کار مراحل زیر را انجام میدهیم:
1-    انتخاب منوی Tools
2-    انتخاب گزینه Option 3
3-    انتخاب:Custom list Tab
4-    انتخاب لیست مورد نظر در کادر Custom list
5-    کلیک در محل مورد نظر در کادر List Entries
6-     ویرایش متن
7-    OK
 
پاک کردن لیست خودکار در اکسل:
اگر بخواهیم یک لیست خودکار حذف کنیم تا دیگر پر کردن خودکار بر روی آن عمل نکند مراحل زیر را طی می کنیم:
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب:Custom list Tab
4-    انتخاب List مورد نظر در کادر Custom List
5-    انتخاب دکمه Delete
6-     OK

 در نرم افزار اکسل برای تنظیم سلول ها می توان از روروش های زیر اسیتفاده نمود                          برای قالب بندی خانه هادر اکسل  مراحل زیر را طی می کنیم:
 روش اول: از تب Home  منو Number  را انتخاب کنید.
روش دوم: بر روی خانه های مورد نظرکلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.
روش سوم: فشردن کلیدهای ctrl +

با اجرای یکی ازسه روش بالا پنجره ای باز می شود که  Tab Number را انتخاب می کنیم:

در Tab ذکر شده  Sample  و Category مشاهده می شود که هر کدام به شرح زیر می باشد.

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

1- General: این گزینه عددها را به صورت رشته ای از رقمهای متوالی و بدون هر گونه قالب بندی نشان می دهد. و اگر عددی در سلول جا نشودآنرا به صورت نمایی نشان می دهد.
2- Number: مقادیر را بصورت رشته ای از رقمهای متوالی نشان می دهد. در این حالت اگر عدد در سلول نگنجد سلول بزرگتر می شود.
- Decimal Places: در این قسمت می توانیم تعداد ارقام بعد از اعشار را تعیین کنیم.
- Negative Number: در این حالت می توانیم مشخص کنیم که عدد منفی به همان صورت نشان داده شود یا به رنگ قرمز یا سیاه با علامت منفی نشان داده شود. یا به رنگ قرمز بدون علامت منفی نشان داده شود. (توجه داشته باشید که این گزینه فقط روی اعداد منفی عمل می کند.)
- Use 1000 Separator: اگر در کنار بگذاریم اعداد را سه رقم، سه رقم از سمت راست این گزینه می گذارد.
3- Currency: مقادیر را همراه با جدا کرده و علامت (،) پول رایج نشان می دهد. در این سمبل را انجام داد:
- Symbol: نوع واحد پول را مشخص کرد می توان از تنظیمات زیر می کنید. در این قسمت واحد پول کشورهای مختلف نمایش داده شده و می توانیم واحد دلخواه خود را انتخاب کنیم.
- Negative Number: اعداد منفی چطور نمایش داده شوند.
4- Accounting: مانند حالت Currency است. این قالب بندی، قالب بندی حسابداری می باشد و علامت پولی در منتهاالهیه سمت چپ آن نوشته می شود.
 5- Date: تاریخ را با قالب بندی خاص تاریخ نشان می دهد. و ما می توانیم انواع قالب بندی های تاریخ را دیده و انتخاب کنیم. مثلاً مدلی را انتخاب کنیم که فقط روز و ماه را نشان دهد. و یا مدلی را انتخاب کنیم که روز را به عدد و ماه را به حروف نشان دهد.
 6- Time: زمان را با قالب بندی های خاص زمان نشان می دهد. انواع قالب زمانی در این قسمت وجود دارند. ما می توانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد. و یا....
7- Percentage: عددها را همراه با علامت درصد نشان می دهد. نکته در تمام قالب بندی ها بجز Percentage فرقی نمی کند که ابتدا اطلاعات را در سلول وارد کنیم یا اول قالب بندی را تنظیم کنیم. ولی در حالت Percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage کنیم، عدد را در 100 ضرب می کند. ولی اگر ابتدا فرمت را Percentage کنیم، و بعد عدد را بنویسیم، تغییری در عدد نمی دهد.
 8- Fraction: مقادیر را بصورت عدد صحیح نشان می دهد که بدنبال آن نزدیک ترین کسر به مقدار واقعی ظاهر می شود. برای نوشتن یک عدد مخلوط کافی است ابتدا قسمت صحیح را نوشته سپس یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 می شود. اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در قسمت Fraction مقدار 3/10 را انتخاب کنیم. در این حالت نگاه می کند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب می کند.
9- Scientific: مقادیر را با قالب بندی علمی نشان می دهد.
 10- Text: مقادیر را به همان صورتی که وارد شده اند نشان می دهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان می دهد و مقادیر آنرا محاسبه نمی کند. (کاردرکارگاه 7 تمرین 8 تا 13)
11- Special: مقادیر را با استفاده از قواعد قالب بندی خاص (مانند کد پستی، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) نشان می دهد. مثلاً اگر در یک سلول یک شماره تلفن (در حالت 10 رقمی) تایپ کنیم، با انتخاب این فرمت به صورت قالب بندی تلفن در می آید. مثلاً اگر شماره IT (3116681184) را وارد می کنیم، به فرمت تلفن در آمده (1184-668 (311)) و می فهمیم که 311 کد شهرستان

، 668 کد محله و 1184 شماره تلفن IT می باشد.

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

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

این لیست بر اساس اکسل 2016 تهیه شده است اما بیشتر آن‌ها بر روی اکسل 2013 نیز کار خواهد کرد.

1. دسترسی به منوی ابزار توسط صفحه کلید: اکسل مثل افزونه Vim-inspired در کروم و فایرفاکس قابلیتی به نام Key Tips دارند که وقتی کلید Alt را می‌فشارید، منوی Ribbon با یک سری حروف پر می‌شود. حرف مورد نظر بر روی صفحه کلید را بزنید تا آن گزینه‌ی مشخص فعال شود.
2. Ctrl + PgDn: سوئیچ میان سربرگ‌های صفحه گسترده. حرکت از چپ به راست.
3. Ctrl + PgUp: سوئیچ میان سربرگ‌های صفحه گسترده. حرکت از راست به چپ.
4. F12: نمایش کادر “Save As”
5. Ctrl + Shift + $: (اکسل 2016) تغییر فرمت سلول فعلی به پول با دو رقم اعشاری
6. Ctrl + Shift + %: (اکسل 2016) تغییر فرمت سلول فعلی به درصد بدون رقم اعشاری
7. Ctrl + Shift + #: (اکسل 2016) تغییر فرمت سلول فعلی به تاریخ با روز، ماه، سال
8. Ctrl + Shift + “:”: قراردادن زمان فعلی
9. Ctrl + Shift + “;”:قراردادن تاریخ فعلی
10. F4: تکرار آخرین فرمان و عمل، در صورت امکان
11. Shift + Arrow key: گسترش سلول انتخابی فعلی با یک سلول دیگر در جعت انتخابی
12. Ctrl + F1: نمایش یا مخفی کردن نوار ابزار
13. Alt + Shift + F1: ایجاد یک سربرگ صفحه گسترده جدید
14. Ctrl + F4: بستن صفحه گسترده فعلی
15. Ctrl + D: اجرای فرمان Fill Down برای سلول‌های انتخابی زیرین. Fill Down محتوا و فرمت بالاترین سلول را در کل ستون کپی می‌کند.