توابع مرتبط با تاریخ و زمان در اکسل

نحوه ورود تاریخ در سلولهای اکسل
در اکسل برای ورود تاریخ باید بین اعداد روز، ماه و سال کاراکتر “/” و یا “-” آورده شود. برای وارد کردن ساعت هم از کاراکتر “:” استفاده می شود.  می توانید در یک سلول هم تاریخ و هم ساعت را وارد کنید.

تابع YEAR در اکسل

اگر بخواهیم از عدد سال یک سلول تاریخی استفاده کنیم باید از تابع YEAR کمک بگیریم. به همین ترتیب توابع DAY و month عدد روز و ماه را بر می گردانند.

تابع date در اکسل


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

تابع HOUR در excel

این تابع مقدار ساعت را بر می گرداند. توابع MINUTE عدد دقیقه و SECOND عدد ثانیه را برمی گرداند.

تابع TIME در اکسل


در صورتیکه قصد داشته باشیم به ساعت عددی را تفریق یا اضافه نماییم باید از تابع TIME استفاده کنیم.


امید است از آموزش اکسل امروز نیز لذت برده باشید

باما همراه باشید تا در ادامه با روش غیر فعال کردن آن آشنا شوید
Auto Fill بطور خودکار فعال است. جهت غیر فعال نمودن آن مراحل زیر را طی می کنیم:
1-    انتخاب منوی Tools
2-    انتخاب منوی Options
3-    انتخاب: Edit Tab
4-    کادر انتخاب Allow Cell Drag and Drop را غیر فعال می کنیم
 
آدرس دهی:
آدرس دهی نسبی:
فرض کنید سلولهای Excel را به صورت زیر پر کرده ایم:
در سلول B1 فرمول A1 A2 را می نویسیم. اگر این فرمول را copy کرده و در سلول B2. Paste کنیم و یا با استفاده از Auto fill محتوای سلولهای B2 تا B4 را پر کنیم.اعداد 5و7و4 به ترتیب برای سلولهای B2 تا B4 ظاهر می شوند. حال میخواهیم بدانیم این اعداد از کجا بدست آمده اند. وقتی ما در سلول B1 فرمول A1 A2 را تایپ می کنیم، در حقیقت سلول سمت چپی و یک سلول پایین آن با هم جمع می شوند. پس برای سلول B2، سلول سمت چپی (A2) و سلول پایینی آن (A3) با هم جمع می شوند که جواب 5 می شود. برای سلولهای بعدی هم به همین ترتیب محاسبات انجام می شود. به این نوع آدرس دهی، آدرس دهی نسبی گفته می شود چون نسبت به مکان هر سلول، فرمول سلول عوض می شود و در حقیقت فرمول درون سلول B2، A2 A3 می شود.
 
 
آدرس دهی مطلق در اکسل:
فرض کنید میخواهیم حقوق افراد یک اداره را حساب کنیم. حقوق با استفاده از فرمول زیر حساب می شود: حقوق پایه * 7% - حقوق پایه = حقوق حقوق پایه را در ستون B مینویسیم. برای محاسبه حقوق می توانیم در سلول C1 فرمول B1-B1*7% را بنویسیم و برای تمام افراد Auto fill کنیم. در این حالت در حقیقت از آدرس دهی نسبی استفاده کرده ایم.
ولی فرض کنید که درصد مالیات عوض شده و 10% شود. حالا باید دوباره فرمول جدیدی در سلول C1 نوشته و مجدد Auto fill کنیم. برای اینکه نخواهیم در هر بار عوض شدن درصد مالیات فرمول را عوض کنیم می توانیم درصد مالیات را در سلول جدا نوشته و از آدرس آن در فرمول استفاده کنیم. پس مثلاً در سلول A1 عدد 7% را مینویسیم و در سلول C1 فرمول B1-B1*A1 را مینویسیم حال Auto fill می کنیم. ولی می بینیم که برای بقیه سلولها جواب همان حقوق پایه می شود. چرا؟ چون با آدرس دهی نسبی میخواهد عمل کند و چون در سلول C1 فرمول B1-B1*A1 بوده، برای C2 فرمول B2-B2*A2 می شود که مقدار A2 صفر است. پس جواب همان مقدار حقوق پایه می شود. برای رفع این مشکل باید سلول A1 ثابت شود. یعنی در تمام فرمولها A1 در ستون حقوق پایه ضرب شود. در این حالت از آدرس دهی مطلق استفاده می کنیم. برای ثابت کردن سطر یا ستون در کنار حرف ستون یا عدد سطر علامت $ می گذاریم. اگر بخواهیم سلول را ثابت کنیم در کنار حرف ستون و عدد سطر هر دو علامت $ می گذاریم. یعنی در سلول C1 مینویسیم: B1-B1*$A$1
 
قالب بندی خانه‌ها در Excel
در Excel این توانایی را داریم که قالب بندی یا فرمت یک سلول را تغییر داده و فرمت آن سلول را مثلاً تاریخ یا درصد یا زمان یا متن یا.... کنیم. همچنین می توانیم دور سلول کادر بیاندازیم یا رنگ زمینه آنرا عوض کنیم.
برای قالب بندی خانه‌ها در Excel مراحل زیر را طی می کنیم:
روش اول:
1-    انتخاب منوی Format
2-    انتخاب گزینه Cells
روش دوم:
بر روی خانه‌های مورد نظرکلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.
روش سوم:
فشردن کلیدهای ctrl 1
با اجرای یکی از سه روش بالا پنجره ای باز می شود که دارای Tab‌های زیر است:
 
الف - Number:
توسط این Tab می توانیم نوع اطلاعات ورودی را تعیین کنیم. این Tab شامل گزینه‌های زیر می باشد:
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 آن را به صورت متن نشان می دهد و مقادیر آنرا محاسبه نمی کند.
11- Special:      مقادیر را با استفاده از قواعد قالب بندی خاص (مانند کد پستی، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) نشان می دهد. مثلاً اگر در یک سلول یک شماره تلفن (در حالت 10 رقمی) تایپ کنیم، با انتخاب این فرمت به صورت قالب بندی تلفن در می آید. مثلاً اگر شماره IT (3116681184) را وارد می کنیم، به فرمت تلفن در آمده (1184-668 (311)) و می فهمیم که 311 کد شهرستان، 668 کد محله و 1184 شماره تلفن IT می باشد.
12- Custom:     برای ایجاد یک قالب بندی جدید از این گزینه استفاده می کنیم که خارج از بحث ما می باشد.
 
ب) Alignment: توسط این Tab می توانیم جهت قرار گرفتن اطلاعات در سلول را تعیین کنیم. این Tab شامل قسمتهای زیر است:
1-     Horizontal: محل قرار گرفتن افقی متن را تعیین می کند. این قسمت شامل گزینه‌های زیر است:
�      General: هم ترازی پیش فرض
�      Left: هم ترازی داده‌ها را به سمت چپ (استفاده برای ارقام)
�      Center: هم ترازی داده‌ها در مرکز
�      Right: هم ترازی داده‌ها به سمت راست (برای متون)
�      Fill: تمام سلول را با متنی که در آن نوشته شده است، پر می کند.
�      Justify: یک تراز مناسب برای سلول در نظر می گیرد. معمولاً برای زمانی است که اطلاعات در سلول نگنجد.
�      Center Across Selection: وسط متن را در وسط سلولهای انتخابی می گذارد.
2-     Vertical: محل قرار گرفتن اطلاعات را بطور عمودی تنظیم می کند. این گزینه شامل قسمتهای زیر است:
�      Bottom: هم ترازی داده‌ها در پایین سلول
�      Top: هم ترازی داده‌ها در بالای سلول
�      Center: هم ترازی داده‌ها در وسط یا مرکز سلول
�      Justify: داده‌ها در داخل سلول هم تراز می شوند. (بدین معنی که داده‌ها در داخل سلول بصورت مساوی جاسازی می شوند. مانند متون موجود در روزنامه ها)
 
3-     Orientation: شامل قسمتهای زیر است:
�      زاویه متن را نسبت به افق بوسیله ماوس می توان تعیین کرد.
�      Degrees: زاویه متن را نسبت به افق بوسیله تایپ زاویه یا با کلید‌های Increase و Decrease تعیین کرد.
 
4-     Text Control: شامل قسمتهای زیر است:
�      Wrap Text: اگر اندازه متن بیشتر از سلول باشد توسط این گزینه می توان آنرا شکست. در نتیجه ارتفاع سطر افزایش می یابد.
�      Shrink to Fit: اگر اندازه متن از سلول بیشتر باشد با انتخاب این گزینه متن به اندازه ای کوچک می شود که در داخل سلول بگنجد.
�      Merge Cells: اگر اندازه متن از سلول بیشتر باشد می توان سلولهایی را که متن اشغال کرده را انتخاب نمود و سپس در کنار این گزینه تیک زد. این عمل باعث می شود که این سلول‌ها بهم پیوسته و یک سلول شون

1. استفاده از میانبر Ctrl + PgDn و Ctrl + PgUp برای رفتن از یک کاربرگ (worksheet) به کاربرگ دیگر د ر اکسل

2. با استفاده از کلیدهای جهت دار + Ctrl بین سلول‌ها جابه جا شوید

البته واضح است که تنها با کلیدهای جهت دار می‌توانید از یک سلول به سلول دیگری حرکت کنید. اما استفاده از کلیدهای جهت دار + Ctrl این روند را سریع‌تر می‌کند.

3. برای انتخاب داده‌ها از کلید Shift استفاده کنید

کلیدهای ترکیبی Ctrl + Shift+ و کلیدهای جهت دار کمک می‌کند محدوده‌ی داده‌ها را تا آخرین سلول دارای داده انتخاب کنید.

4برای کپی دوبار کلیک کنید

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

5.  تغییر فرمت سلول با استفاده از کلیدهای میانبر در erxcel

با استفاده از کلیدهای ترکیبی!+Ctrl + Shift می‌توانید فرمت عدد را به دو رقم اعشار تبدیل کنید. برای تبدیل به فرمت دلار کافی است از کلیدهای ترکیبی $+Ctrl + Shift و برای تبدیل به درصد از کلیدهای ترکیبی %+Ctrl + Shift استفاده کنید.

6. برای قفل سلول‌ها از کلید F4 استفاده کنید

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

7. خلاصه کردن داده‌ها با توابع CountIF و  SumIF در اکسل

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

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

8. استخراج داده‌ها با تابع VLOOKUP در excel

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

تابع VLOOKUP چهار ورودی دارد. ورودی اول مربوط به داده‌ی مورد نظر است. در این مثال ما به دنبال عدد 1 که رتبه‌ی دانشگاه است، هستیم. ورودی دوم مربوط به محدوده‌ی داده‌ها است که در این مثال کل ردیف A تا F انتخاب شده است. ورودی سوم مربوط به شماره ستون داده‌ی مورد نظر است. چون ما به دنبال اسم دانشگاه هستیم ورودی را ستون 2 در نظر می‌گیریم. ورودی آخر مربوط به وقتی است که داده‌ی مورد نظر (مثلا عدد 1) پیدا نشود که به جای آن عدد 0 یا عبارت False را قرار می‌دهیم.

9. استفاده از & برای ترکیب رشته‌های متنی

در اینجا دو ستون داریم که یکی شامل نام و دومی شامل نام خانوادگی است. با استفاده از علامت & می‌توان ستونی ایجاد کرد که نام و نام خانوادگی را در کنار هم داشته باشد. در نرم‌افزار اکسل با کمک & می‌توانید دو قسمت از متن را با هم ترکیب کنید. البته دقت کنید که بین نام و نام خانوادگی حتما فاصله قرار دهید. روش کار به این صورت است: [انتخاب سلول نام خانوادگی]& ” ” & [ّانتخاب سلول نام].

10. پاک کردن متن با توابع LEFT، RIGHT وLEN  در اکسل

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

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

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

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

حالا با کمک تعداد کاراکترهای به دست آمده از تابع LEN می‌توانید از تابع RIGHT استفاده کنید.

از آن جا که ما می‌خواهیم حروف اختصاری و خط فاصله را حذف کنیم، وردی تابع RIGHT را عدد تابع LEN منهای 3 قرار می‌دهیم.

11. ایجاد اعداد تصادفی با تابع   RANDدر اکسل

تابع ()RAND می‌تواند اعداد تصادفی بین صفر تا 1 ایجاد کند. این تابع هیچ گونه ورودی لازم ندارد و داخل پرانتز خالی است. با هر بار فشردن کلید F9 این تابع عدد جدیدی تولید می‌کند. دقت داشته باشید که با ایجاد هر گونه تغییر در فضای کار، یک عدد جدید ایجاد خواهد شد.

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

” برای رسم نمودار در اکسل نمی توانید از توابع استفاده کنید زیرا اکسل برای رسم نمودار نیاز به اعدادی تحت عنوان مقدار سطر و مقدار ستون متناظر با آن دارد. “

انواع نمودار در اکسل

1- نمودار ستونی (Column): در این نمودار می توانید عنصری را با عنصر دیگر مقایسه کنید. مثلا میزان بارش باران در یک سال در ماه های مختلف.
2-نمودار خطی (Line): برای نشان دادن تغییرات یک متغیر نسبت به زمان استفاده می شود، مثلا میزان رشد قد یک کودک در یک سال.
3- نمودار دایره ای (Pie): برای نشان دادن متغیرهای یک هدف خاص به کار می رود، مثلا نشان دادن حجم استفاده شده درایوها در یک کامپیوتر.
4- نمودار میله ای (Bar): همان نمودار ستونی است با این تفاوت که ستون ها به صورت سطری استفاده می شوند.
5- نمودار مساحتی (Area): بیشتر برای حساب کردن انتگرال بین دو بازه زمانی کاربرد دارد.
6- نمودار پراکندگی (Scatter): به نمودار (X-Y) معروف است و برای نشان دادن تاثیرات دو متغیر روی هم کاربرد دارد. مثل نمودار فشار بر حسب دما.

روش رسم نمودار ستونی در اکسل

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

 
1- ابتدا داده ها را در اکسل وارد کرده و آن ها را انتخاب می کنیم.
2- از تب Insert و از قسمت Charts نمودار ستونی یعنی Column را انتخاب می کنیم.
3- در پنجره باز شده انواع نمودارهای ستونی دیده می شوند.
4- بر روی نخستین نمودار ستونی دو بعدی کلیک کنید.
5- بعد از رسم نمودار سه زبانه  Format، Layout، Design به زبانه های اکسل اضافه می شوند.

” نمودار خطی اکسل برای مقایسه تغییرات یک متغیر در چند زمان نیز می تواند استفاده شود. مثلا روند فروش در دو سال متوالی و در ماه های مختلف. “

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

نمودار خطی در اکسل برای نشان دادن تغییرات یک متغیر نسبت به زمان مورد استفاده قرار می گیرد. به عنوان مثال در این مقاله نمودار روند فروش یک سال یک شرکت در ماه های مختلف را رسم می کنیم. برای رسم نمودار خطی در اکسل:
1- داده ها را در اکسل وارد کنید.

2- داده های وارد شده را انتخاب کنید. از منوی INSERT نمودار Line را از قسمت D-2 انتخاب کنید.

3- پس از این مرحله می توانید تنظیمات گرافیکی دلخواه را اعمال کنید.

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

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

1- با دابل کلیک روی عنوان ریبان آن را پنهان و با تکرار این کار آن را ظاهر نمایید.

2- تکنیک چرخاندن غلطک (اسکرول)

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

4٫ انتخاب چند شیت با نگهداشتن کلید Ctrl و جابجا کردن آن

5٫ انتخاب سریع چندین شیت با نگهداشتن کلید

Shift    6- کپی قالب بندی مورد نظر و اعمال آن روی سایر سلول ها با دابل کلیک روی گزینه Format Painter

7- بزرگ و کوچک کردن ناحیه فرمولا بار با موس 

8- دابل کلیک روی یک کلمه برای انتخاب کامل کلمه

9- استفاده از ابزار پرکردن (Fill handle) با استفاده از کلیک راست در اکسل

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

• Copy Cells
• Fill Series
• Fill Formatting Only
• Fill Without Formatting
• Fill Days
• Fill Weekdays
• Fill Months
• Fill Years
• Linear Trend
• Growth Trend
• Serie
•  
• 10٫ پر کردن خودکار با دابل کلیک   
• 11٫ ویرایش محدوده فرمول

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

12٫ پرش به اولین و آخرین سلول حاوی داده

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

13٫ انتخاب سریع یک محدوده به کمک کلید Shift

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

14٫ انتخاب محدوده چندگانه با نگهداشتن کلید Ctrl

15٫ جابجا کردن محدوده انتخاب شده به وسیله موس

16٫ جابجا کردن یک ستون یا محدوده انتخابی با نگهداشتن کلید Shift

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

  SHIFT + Drag   17٫ کپی کردن محدوده انتخابی با نگهداشتن کلید Ctrl   

و اما آخرین مورد کپی سریع محدوده انتخابی با نگه داشتن کلید Ctrl و درگ به محل مورد نظر می باشد.

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

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

(عرض[اختیاری]،ارتفاع [اختیاری]، تعداد ستون، تعداد سطر، سلول یا محدوده مرجع)OFFSET

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

فرض کنید در جدولی  ما میخواهیم از یک سلول A1 به یک سلول D2 برسیم. تابع OFFSET ما به شکل زیر می شود:

(OFFSET(A1,1,3

در اینحالت نتیجه عدد 20 خواهد بود.

حالا اگر بخواهیم از سلول A1 به محدوده C2 4 برسیم، باید فرمول رابه صورت زیر بنویسیم:

(OFFSET(A1,1,2,3,2

در این حالت چون نتیجه تابع یک سلول نیست و یک محدوده شامل شش سلول است درون سلول خطای !VALUE# در اکسل نمایش داده می شود. این حالت زمانی استفاده می شود که میخواهیم  برای نتیجه تابع OFFSET از توابعی مثل SUM یا AVERAGE استفاده کنیم. مثلا نتیجه فرمول ((SUM(OFFSET(A1,1,2,3,2 برابر عدد 82 هست که جمع شش سلول C2 تا D4 هست.

اکنون  تا حدودی با نحوه کار با تابع OFFSET اکسل آشنا شدیم 

اکنون به پارامترهای تابع OFFSET در اکسل  میندازیم:

• سلول یا محدوده مرجع در excel 
  
• این پارامتر سلول یا محدوده ای را  مشخص می کند که میخواهیم از آن  شروع کنیم و با حرکت کردن به تعداد سطر و ستون مشخص به سلول یا محدوده جدید برسیم. این پارامتر می تواند یک سلول مثل A1 یا یک محدوده مثل A1:B3 باشد.
• تعداد سطر: این پارامتر مشخص می کند که ما از سلول یا محدوده مرجع میخواهیم چند سطر و در چه جهتی جابجاشود. در صورتی که این عدد مثبت باشد محدوده مرجع به سمت پایین و در صورتی که این عدد منفی باشدبه سمت بالا جابجا می شود. در صورتی که محدوده مرجع  بیش از یک سلول باشد (مثلا A1:B6)، جابجایی از اولین سلول موجود در محدوده یعنی A1 محاسبه می شود.
• تعداد ستون: این پارامتر مشخص می کند که از سلول یا محدوده مرجع میخواهیم چند ستون و در چه جهتی جابجا شود. در در حالت راست به چپ در صورتی که این عدد مثبت باشد محدوده مرجع به سمت چپ و در صورتی که این عدد منفی باشد به سمت راست جابجا می شود.در صورتی که محدوده مرجع  بیش از یک سلول باشد، جابجایی از اولین سلول موجود در محدوده (A1) محاسبه می شود. به
• ارتفاع [اختیاری]: این پارامتر مشخص می کند که بعد از جابجا شدن از محدوده مرجع به تعداد سطر و ستون مشخص، ارتفاع محدوده ای که به عنوان نتیجه تابع برگردانده می شود باید چند سطر باشد. تعیین این پارامتر اختیاری هست و در صورتی که ما پارامترهای ارتفاع و عرض رو تعیین نکنیم، تابع OFFSET اکسل به صورت خودکار پارامترهای ارتفاع و عرض را مساوی با ارتفاع و عرض محدوه مرجع ما قرار می دهد. به عنوان مثال اگر ما ارتفاع و عرض رو تعیین نکنیم و سلول A1 رو به عنوان پارامتر اول تابع OFFSET اکسل انتخاب کنیم ارتفاع و عرض هر دو برابر 1 و اگر  A1:B3 رو به عنوان محدوده مرجع انتخاب کنیم، ارتفاع برابر  3 و عرض برابر 2 خواهد بود.
• عرض[اختیاری]: این پارامتر مشخص می کند که بعد از جابجا شدن از محدوده مرجع به تعداد سطر و ستون مشخص، عرض محدوده ای که به عنوان نتیجه تابع برگردانده می شود باید چند ستون باشد. تعیین این پارامتر هم مثل پارامتر ارتفاع اختیاری ست.

نکته: تابع OFFSET اکسل یک تابع VOLATILE است بدین معنی که با هر تغییر در ورکبوک شما، فارغ از اینکه تغییر موردنظر تاثیری را نتیجه این تابع  داشته باشد یا نه، این تابع مجدداً محاسبه می شود. بنابراین در استفاده از این تابع دقت کنید چون استفاده نابجا و زیاد از این تابع می تواند فایل اکسلراخیلی کند و سنگین کند.

امیدواریم از آموزش اکسل امروز نیز همچون گذشته لذت برده باشید

یز همچون گذشته لذت برده باشید

ترتیب انجام عملیات ریاضی در اکسل

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

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

➋ محاسبات مربوط به توان برای مثال 3^2

➌ ضرب و تقسیم، هر کدام که جلوتر در فرمول آمده باشند.

➍ جمع و تفریق، هر کدام که جلوتر در فرمول آمده باشند.

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

• P = parentheses: پرانتزها
• E = Exponential: توان
• MD = Multiplication and division: ضرب و تقسیم
• AS = Addition and subtraction: جمع و تفریق

ایجاد فرمولهای پیچیده در excel

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

=(D3+D4+D5)*0.075

این فرمول جمع اقلام فاکتور را محاسبه می کند، و سپس مجموع آنها را بر نرخ مالیات 7.5% (که بصورت 0.075 نوشته می شود) ضرب می کند، تا در نهایت بتواند نتیجه فرمول را محاسبه نماید.



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

(45.80+68.70+159.60) = 274.10

سپس حاصل جمع بدست آمده را بر نرخ مالیات ضرب می کند.

274.10*0.075

نتایج فرمول نشان می دهد که هزینه مالیات $20.56 می باشد.

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

ایجاد یک فرمول پیچیده با استفاده از ترتیب عملگرها در اکسل

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

➊ سلول مربوط به فرمول را انتخاب کنید، در این مثال ما سلول D5 را انتخاب می کنیم.



➋ فرمول خود را وارد کنید. در این مثال ما فرمول زیر را وارد می کنیم:

=B3*C3+B4*C4

این فرمول از ترتیب عملگرها استفاده می کند و در ابتدا عملیات ضرب را محاسبه می کند:

2.79*35 = 97.65

2.29*20 = 45.80

سپس این مقادیر را با هم جمع می کند تا به جمع کل برسد.

97.65+45.80

➌ برای اطمینان از درستی کار مجددا فرمول خود را بررسی کنید، سپس اینتر را بفشارید. فرمول محاسبه شده و نتایج را نمایش می دهد. در مثال ما نتیجه $143.45 می باشد.

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

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

➊ ابتدا فایل اکسل زیر را دانلود کنید.

 

➋ برگه Challenge را انتخاب کنید.

➌ در سلول D7 فرمولی بسازید که مالیات فاکتور را با نرخ 7.5% محاسبه کند.

➍ در سلول D8 فرمولی بسازید که مجموع اقلام فاکتور را محاسبه کند. بعبارت دیگر این فرمول باید مجموع دامنه سلولی D3 7 را محاسبه کند.

➎ در سلول D9 فرمولی بسازید که مجموع اقلام را با کسر 10% تخفیف محاسبه کند.

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

 جلوگیری از نمایش فرمول ها در Microsoft Excel 2013

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

بدین منظور:
  ابتدا تمام سلول‌هایی که قصد پنهان سازی فرمول‌های موجود در آن را دارید انتخاب کنید.
در سربرگ Home بر روی Format کلیک کنید (ترفندستان) و Format Cells را انتخاب کنید.
به سربرگ Protection رفته و تیک گزینه‌ی Hidden را زده و بر روی OK کلیک کنید.
حال دوباره بر روی Format کلیک کرده و این‎‌بار Protect Sheet را انتخاب کنید.
یک رمز عبور در کادر Password to unprotect sheet وارد کرده و OK را بزنید.
یک بار دیگر نیز رمز عبور خود را وارد کنید.
اکنون مشاهده می‌کنید که دیگر اثری از فرمول‎‌ها نخواهد بود.
برای بازگشتن به حالت اولیه بر روی t کلیک کرده و Unprotect Sheet را بزنید.

اکسل نرم افزاری قدرتمند برای محاسبه، مدیریت و تحلیل داده ها می باشد. بسیاری از کاربران عملیات آماری و اعدادی خود را با این نرم افزار انجام می دهند. اکنون قصد داریم به معرفی 3 ترفند بسیار کاربردی در اکسل بپردازیم. این ترفندها عبارتند از "کشیدن چارت در چند ثانیه"، "کپی کردن Chart Formats" و "لینک کردن Autoshape و Textbox با اطلاعات داخل سلول". یقینأ استفاده از این ترفند می تواند کار با اکسل را برای شما آسان تر و بهینه تر نماید.

کشیدن چارت در اکسل در چند ثانیه
در واقع در دو مرحله بسیار ساده شما می توانید به سرعت یک چارت در اکسل بکشید:
1- یک سلول را در میان جدولی که میخواهید اطلاعات آن به شکل چارت رسم شود انتخاب کنید.
2- کلید F11 را فشار دهید.
چارت شما آماده است! اکسل چارت شما را در یک Chart Sheet جدید ایجاد می کند. شما می توانید سایر تنظیمات را طبق روش معمول انجام دهید.

کپی کردن Chart Formats در اکسل
همانگونه که میدانید تنظیمات مربوط به فرمت چارتها در اکسل بسیار وقت گیر و گاه کسل کننده است. آیا تا به حال چارتی را در اکسل ایجاد کرده اید که به نظرتان فرمت مناسب و ایده آلی داشته باشد و دوست داشته باشید سایر چارتهایی که ایجاد می کنید یا قبلا کشیده اید همین فرمت را داشته باشند؟ در این ترفند روش بسیار ساده ای را برای کپی کردن Chart Formats حتی بین فایلهای مختلف معرفی خواهیم کرد.
1- چارتی که فرمت دلخواهتان را دارد انتخاب (select) کنید.
2- با فشردن دو کلید ctrl+C چارت را کپی کنید.
3- چارت مقصد که میخواهید فرمت آن را تغییر دهید را انتخاب (select) کنید.
4- از منوی edit بالای صفحه گزینه Paste Special را انتخاب کنید.
5- در پنجره ظاهر شده گزینه Formats را انتخاب کرده و Ok کنید. به همین سادگی کلیه فرمتهای چارت شما کپی شد.

لینک کردن Autoshape و Textbox با اطلاعات داخل سلول در اکسل
آیا تا به حال به مرتبط کردن یک text box یا یک AutoShape به یک سلول نیاز پیدا کرده اید؟ با این ترفند شما می توانید اطلاعات یک سلول را به یک text box یا یک AutoShape بصورت لینک منتقل کنید.
1- اولا یک text box یا یک AutoShape رسم کنید.
2- حالا text box یا AutoShape ایجاد شده را انتخاب (select) کنید.
3- روی formula bar (نوار بالای صفحه که فرمولها در آن نمایش داده می شوند) کلیک کنید و علامت = را تایپ کنید.
4- حال بر روی سلول مورد نظرتان کلیک کنید و کلید enter را فشار دهید. همانطور که مشاهده می کنید اطلاعات سلول مور نظرتان به text box یا AutoShape بصورت لینک منتقل شده است. حالا می توانید از روشهای معمول format دلخواهتان را به text box یا AutoShape بدهید

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

با ما همراه باشید تا با نحوه ی ایجاد این لیست کشوییی آشنا شویم

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

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

ایجاد یک لیست کشویی پایین‌افتادنی دینامیک در اکسل

سپس در تب Data از منوی Data Validation بر روی Data Validation کلیک نمایید (برای این کار می‌‌توانید کلیدهای ترکیبی Alt+L را نیز نگه داشته و سپس کلید D را فشار دهید).

در پنجره‌ی باز شده، گزینه‌ی Allow را بر روی List تنظیم نمایید.

سپس بر روی دکمه‌ی مقابل فیلد Source کلیک نمایید.

ترفندهای اکسل

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

در نهایت بر روی دکمه‌ی ضربدر کلیک کرده و پنجره‌ی باز را OK کنید.

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

لازم به ذکر است برای غیرفعال کردن این لیست کشویی، پس از انتخاب سلول مجدد کادر محاوره‌ای Data Validation را باز کرده و گزینه‌ی Allow را بر روی Any value تنظیم نمایید.

امید واریم از این آموزش اکسل نیز لذت برده باشید