راهکارهای جالب در جداول چرخشی (PivotTables) اکسل

 PivotTables می توانند جهت خلاصه سازی و تجزیه و تحلیل انواع داده ها مورد استفاده قرار بگیرند. برای کمک به شما در زمینه مدیریت بهتر PivotTable و برای اینکه بینش و درک بهتری از داده هایتان پیدا کنید، اکسل سه ابزار دیگر نیز به شما ارائه می دهد. اسامی این ابزارها filters ، slicers و PivotCharts می باشد.                                                       

فیلترها (Filters) در PivotTable اکسل

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

روش افزودن فیلتر به یک PivotTable در اکسل

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

➊ یک فیلد را از قسمت فیلدها بکشید و در ناحیه فیلترها رها کنید. در این مثال ما از فیلد Salesperson استفاده می کنیم.



➋ فیلتر در بالای قسمت PivotTable ها نمایان می شود. ابتدا کادر مربوط به فیلتر را باز کنید و سپس گزینه Select Multiple Items را تیک بزنید.



➌ حالا تیک مواردی را که نمی خواهید در PivotTable مشاهده شوند را بردارید. در این مثال ما چند تا از فروشندگان را از حالت انتخاب خارج کرده ایم، در پایان OK کنید.



➍ جدول PivotTable خودش را تنظیم می کند و تغییرات ایجاد شده را منعکس می کند.

برش دهنده ها (Slicers) در اکسل

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

روش افزودن یک slicer (برش دهنده) در اکسل

➊ ابتدا یک سلول را به دلخواه در PivotTable انتخاب کنید.

➋ در تب Analyze بر روی دستور Insert Slicer کلیک کنید.



➌ یک کادر باز می شود. فیلدهای مورد نظرتان را تیک بزنید و سپس ok کنید. در این مثال ما فیلد Salesperson را تیک می زنیم.



➍ برش دهنده (slicer) در کنار PivotTable نمایان می شود. مواردی را که انتخاب کرده اید به رنگ آبی نمایش داده می شوند. در این مثال، برش دهنده شامل تمامی 8 فروشنده ما می باشد، اما 5 تایی که در حالت انتخاب هستند با رنگ آبی متمایز شده اند.



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

نمودارهای چرخشی (PivotCharts) در اکسل

نموارهای چرخشی (PivotCharts) دقیقا مانند نمودارهای معمولی هستند، با این استثنا که آنها داده های یک PivotTable را نمایش می دهند. درست مانند نمودارهای معمولی، شما می توانید از ویژگیهای نوع نمودار، سبک نمایش نمودار و ... بهره مند گردید.

 PivotChart در اکسل

➊ ابتدا سلولی را به دلخواه در PivotTable انتخاب کنید.

➋ در تب Insert بر روی دستور PivotChart کلیک کنید.



➌ صفحه Insert Chart نمایان می شود. ابتدا نوع نموارد (chart type) و همینطور سبک نمایش (layout) مورد نظرتان را انتخاب کنید.



➍ نمودار PivotChart نمایان می شود.

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

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

تبدیل جدول اکسل به بانک اطلاعاتی اکسس

فرض کنید کاربرگی از اکسل دارید که حاوی اطلاعات مشتری می‌باشد و تصمیم گرفته‌اید سایتی بر اساس همان اطلاعات داشته باشید درنتیجه باید به سراغ برنامه Access که برنامه‌ای نسبتاً ساده برای ایجاد و مدیریت پایگاه داده هست، بروید. اما اینکه بخواهید این داده‌ها را دوباره در اکسس وارد کنید کاری بسیار سخت و زمان‌گیر محسوب می‌شود. اگر هم‌حجم داده‌ها بسیار بالا باشد این کار تقریباً غیرممکن می‌‌شود. اینجاست که خود مجموعه آفیس به کمک شما می‌آید و امکان این را فراهم می‌کند که داده‌ها را از یک نرم‌افزار وارد نـرم‌افـزار دیگر کنید؛ برای نمونه از Excel به Access یا برعکس. همچنین برای این ورود داده‌ها چندین روش درنظرگرفته شده است و می‌توان گفت دست شمارا باز گذاشته است.

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

سپس از تب External Data، گزینه‌ی Excel را انتخاب نمایید.

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

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

در مرحله‌ی بعد کاربرگ موردنظر را انتخاب نموده و روی گزینه‌ی Next کلیک نمایید.

در مرحله‌ی بعد، اگر بخواهید عنوان سلول‌های جدول در بانک موجود باشد تیک گزینه First Row Contains Column Headings را بزنید و گزینه‌ی Next را انتخاب نمایید.

در این مرحله دقت داشته باشید که Sheet موردنظر انتخاب‌شده باشد و گزینه Finish را انتخاب نمایید.

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

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

تبدیل جدول اکسل به بانک اطلاعاتی SQL

برای تبدیل جدول اکسل به SQL، ابتدا باید یک بانک اطلاعاتی در محیط SQL ساخته شود.

سپس روی بانک موردنظر کلیک راست نموده و گزینه‌ی Task و سپس گزینه‌ی Export Data را انتخاب نمایید.

در پنجره‌ی بازشده گزینه Next را انتخاب نمایید.

در صفحه‌ی بعدی باید نوع دیتابیس را در قسمت Data Source و مسیر را در قسمت File Path انتخاب نمایید. سپس گزینه‌ی Next را انتخاب نمایید.

در صفحه‌ی بعدی گزینه Next را انتخاب نمایید.

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

در پنجره نمایش داده‌شده، کاربرگ موردنظر را که حاوی اطلاعات جدول می‌باشد انتخاب نمایید.

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

مطالبی درمورد قالب بندی شرطی در اکسل

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

درک قالب بندی شرطی در اکسل

قالب بندی شرطی به شما این امکان را می دهد تا بصورت اتوماتیک یکسری قالب بندی مانند رنگ ها، آیکان ها و ... را بر اساس مقادیر سلولها، به یک یا چندین سلول اعمال کنید. برای انجام این کار ابتدا باید یک قانون برای آن بسازید. به این قوانین conditional formatting rule (قوانین قالب بندی شرطی) گفته می شود. بعنوان مثال، یک قانون قالب بندی شرطی می تواند این باشد که اگر مقدار سلولی کمتر از $2000 بود رنگ آن سلول قرمز شود. با اعمال این قانون به شکل قالب بندی شرطی شما با یک نگاه از روی رنگهای قرمز می توانید به سرعت تشخیص دهید مقادیر کدام سلولها کمتر از $2000 بوده است.                                                            

روش های  ایجاد یک قانون برای قالب بندی شرطی در اکسل

در این مثال ما یک برگه اکسل داریم که داده های فروش در آن می باشد، و می خواهیم بدانیم که کدامیک از فروشندگان ما به هدف فروش ماهیانه اش رسیده است. هدف فروش ماهیانه $4000 می باشد. پس ما یک قانون قالب بندی شرطی می سازیم تا هر سلولی که مقدارش بیشتر از 4000 دلار باشد مشخص گردد.                                                                                                                                         ➊ ابتدا سلولها مورد نظر که می خواهید قالب بندی شرطی روی آنها اعمال گردد را انتخاب کنید.

➋ در تب Home بر روی دستور Conditional Formatting کلیک کنید. یک کادر پایین افتادنی باز می شود.

➌ نشانگر ماوس را بر روی قالب بندی شرطی مورد نظرتان ببرید، سپس از منوی باز شده قانون مورد نظرتان را انتخاب کنید. در این مثال، ما گزینه greater than (بزرگتر از) را انتخاب می کنیم.                                                                                                                               
➍ یک پنجره ظاهر می شود. مقدار مورد نظرتان را وارد کنید. در این مثال ما مقدار 4000 را وارد می کنیم.

➎ سبک قالب بندی را از کادر باز شدنی آن انتخاب کنید، در این مثال ما سبک قالب بندی Green Fill with Dark Green Text را انتخاب می کنیم. سپس بر روی OK کلیک کنید.                  
➏ قالب بندی شرطی بر روی سلولهای انتخاب شده اعمال می گردد.                            

قالب بندی های شرطی پیش فرض اکسل

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

• Data Bars : نوارهای داده، نوارهایی بصورت افقی هستند که به هر سلول اضافه می شوند. عملکرد این نوارها مانند نمودارها می باشد. در هر سلول بر اساس مقدار آن سلول این نوارها بصورت اتوماتیک تنظیم می شوند و جلوه بصری خیلی خوبی به اطلاعات شما می دهند.
• Color Scales : مقیاس رنگها، بر اساس مقدار هر سلول رنگ آن را تغییر می دهند. هر مقیاس رنگ از دو یا سه درجه بندی رنگ استفاده می کند. برای مثال، در مقیاس رنگ Green-Yellow-Red، بزرگترین مقدار به رنگ سبز در می اید، میانگین ها به رنگ زرد، و کمترین مقدار به رنگ قرمز در می آید.
• Icon Sets : مجموعه آیکان ها (شکلک ها) ، با توجه به مقدار هر سلول آیکانی را به سلول اضافه می کند.

روش استفاده از قالب های شرطی از پیش طراحی شده اکسل

➊ ابتدا دامنه سلولی مورد نظرتان را جهت اعمال قالب بندی شرطی انتخاب کنید.



➋ بر روی دستور Conditional Formatting کلیک کنید. یک کادر پایین افتادنی باز می شود.

➌ نشانگر ماوس را بر روی قالب بندی از پیش طراحی شده مد نظرتان ببرید، سپس از موارد نمایش داده شده بدلخواه یکی را انتخاب کنید.



➍ قالب بندی شرطی انتخاب شده بر روی سلولها اعمال می گردد.

 حذف قالب بندی شرطی در اکسل

➊ بر روی دستور Conditional Formatting کلیک کنید. یک کادر باز می شود.

➋ نشانگر ماوس را روی گزینه Clear Rules ببرید، و قوانینی را که می خواهید حذف کنید را انتخاب کنید. در این مثال ما گزینه Clear Rules from Entire Sheet را انتخاب می کنیم، این گزینه تمامی قالب بندی های شرطی موجود در برگه اکسل را حذف خواهد کرد.



➌ قالب بندی های شرطی حذف می گردند.


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

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

در اکسل ميتوانيم ليست  را بر اساس هر يك از ستونهاي آن به صورت صعودي يا نزولي مرتب كنيم. براي اين كار كافي است با ما همراه باشید تا با روش های آن آشنا شویم:
روش اول :
1- يك سلول از ستوني را كه ميخواهيم ليست بر اساس آن مرتب شود انتخاب ميكنيم.
2- براي مرتب سازي صعودي  در اکسل از آيكون Sort Ascending  و براي مرتب سازي نزولي  در اکسل از آيكون Sort Descending در نوار ابزار Standard استفاده ميكنيم.
نکته
بايد توجه داشته باشيم كه در اين حالت نبايد يك ستون انتخاب شود بلكه فقط يك سلول از ستوني كه ميخواهيم بر اساس آن ليست مرتب شود را انتخاب ميكنيم.
روش دوم :
1- كل جدول يا يك سلول از جدول را انتخاب ميكنيم.
2- منوي Data را انتخاب ميكنيم.
3- گزينه Sort را انتخاب ميكنيم.
4- پنجره اي باز ميشود كه بايد قسمتهاي زير را با توجه به نياز در آن پر كنيم :
الف- Sort By : در اين كادر ستوني را كه ميخواهيم ليست بر اساس آن مرتب شود ، انتخاب ميكنيم.
ب- Then By : در اين كادر ستوني را تعيين ميكنيم كه اگر اطلاعات ستوني که در کادر اول مشخص شده براي مرتب سازي مانند هم بود ليست بر اساس اين ستون مرتب شود.
ج- Ascending : با انتخاب اين گزينه ليست به صورت صعودي مرتب ميشود.
د- Descending : با انتخاب اين گزينه ليست به صورت نزولي مرتب ميشود.
هـ- My List Has : در اين قسمت دو گزينه Header Row ، و No Header Row وجود دارد. اگر Header Row انتخاب شده باشد هنگام باز شدن پنجره Sort كل ليست به غير از سطر اول كه سطر عنوان است انتخاب ميشود و در كادر هاي Sort By , Then By عناوين قرار ميگيرند. ولي اگر No Header Row را انتخاب كنيم هنگام باز شدن پنجره Sort كل جدول حتي سطر اول كه سطر عنوان است انتخاب ميشود و در كادر هاي Sort by , Then By بجاي عناوين, نام ستونها قرار ميگيرند. در حقيقت در اين حالت عناوين جزء اطلاعات در نظر گرفته شده و در مرتب سازي  در اکسل شركت داده ميشوند.

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

در نرم افزار اکسل جعهت ویرایش سطر ها وستون ها در اکسل راهکار های متنوعی وجود دارد مانند اضافه كردن سطر  در اکسل:                             

روش اول :

1- بر روی سطری كه می خواهیم سطر جدید قبل از آن اضافه شود ، كلیك می كنیم .

2- منوی Insert را انتخاب می كنیم .

3- گزینه Row را انتخاب می كنیم .

روش دوم:

1- بر روی سطری كه می خواهیم سطر جدی قبل از آن اضافه شود كلیك راست می كنیم .

2- از منوی باز شده Insert را انتخاب می كنیم .

نكته :

سطرهای زیرین به پایین رانده می شوند .

اضافه كردن ستون در اکسل :

روش اول:

1- بر روی ستونی كه می خواهیم ستون جدید قبل از آن اضافه شود ، كلیك می كنیم .

2- منوی Insert را انتخاب می كنیم .

3- گزینه Column را انتخاب می كنیم .

روش دوم :

1- بر روی ستونی كه می خواهیم ستون جدید قبل از آن اضافه شود راست كلیك می كنیم .

2- گزینه Insert را انتخاب می كنیم .

نكته : ستون قبلی به جلو رانده می شود.

اصطلاح : از Insert Row برای اضافه كردن سطر و از Insert Column برای اضافه كردن ستون استفاده می شود .

تغییر ارتفاع سطر در excel :

برای تغییر ارتفاع سطر به دو روش می توان عمل كرد :

روش اول:

كافی است به مرز بین سطر بعدی رفته تا مكان نما به صورت یك فلش دو سر درآید . سپس مرز سطر را به محل مورد نظر Drag می كنیم . در حین Drag كادر كوچكی ظاهر می شود كه اندازه سطر را نشان می دهد .

روش دوم:

در این روش ارتفاع به اندازه دقیق تنظیم می شود . برای این كار مراحل زیر را انجام می دهیم :

1- انتخاب منوی Format
2- انتخاب زیر منوی Row

3- انتخاب گزینه Height

4- در كادر باز شده اندازه دلخواه را وارد می كنیم .

نكته : به طور پیش فرض ارتفاع سطر 12.75 می باشد .

تغییر پهنای ستون :

برای تغییر پهنای ستون به یكی از دو روش زیر عمل می كنیم :

روش اول :

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

روش دوم:

در این روش پهنا را به صورت دقیق تنظیم می كنیم . برای این كار مراحل زیر را طی می كنیم :

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

2- انتخاب زیر منوی Column

3- انتخاب گزینه Width

4- در كادر باز شده مقدار دلخواه را وارد می كنیم .

نكته :

به طور پیش فرض پهنای ستون 8.43 می باشد .

تغییر پهنای استاندارد ستون :

می دانیم كه پهنای استاندارد ستونها 8.43 می باشد اگر بخواهیم این پهنای استاندارد را كم یا زیاد كنیم به ترتیب زیر عمل می كنیم :

(فقط به یاد داشته باشید این تغییر بر روی ستونهایی اعمال می شود كه پهنای آنها را تغییر نداده باشیم .)

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

2- انتخاب زیر منوی Column

3- انتخاب گزینه standard Width

4- در كادر باز شده پهنای باند را وارد می كنیم .

مخفی كردن ستون یا سطر:

می توانیم یك سطر یا ستون را مخفی كنیم تا محتویات آن و كلا خود سطر مشخص نباشد. برای این كار مراحل زیر را طی می كنیم :

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

2- انتخاب زیر منوی Row (برای سطر) یا Column (برای ستون)

3- انتخاب گزینه Hide

نمایان ساختن سطر یا ستون مخفی شده :

اگر بخواهیم سطر یا ستون مخفی شده را از حالت مخفی خارج كنیم مراحل زیر را انجام می دهیم :

1- انتخاب سطر (ستون) قبل و بعد از سطر (ستون) مخفی شده

2- انتخاب منوی Format

3- انتخاب زیر منوی Row (برای سطر) یا Column (برای ستون)

4- انتخاب گزینه Unhide

تغییر اندازه عرض ستون به اندازه اطلاعات داخل آن :

ممكن است اطلاعاتی كه در سلول تایپ می كنیم از عرض سلول بیشتر باشد و در هنگام تایپ ظاهرا سلول بعدی را می كیرد . برای رفع این مشكل مراحل زیر را اانجام می دهیم .

1- كلیك بر روی سلولی كه می خواهیم عرض ستون به اندازه اطلاعات داخل آن شود.

2- Format

3- Column

4- Auto fit selection

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

همانطور که می دانیم اکثر کاربران نرم افزار اکسل (excel) برای انجام کارهای محاسباتی ریاضی نیازمند استفاده از فرمول نویسی در سلول ها هستند. در این مقاله قصد داریم تا حدودی نحوه کار کردن با امکانات محاسباتی اکسل و کار کردن با فرمول های آن آموزش داده شود.

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

 

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

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

 

نحوه درج فرمول در سلول ها

برای درج فرمول در یک سلول، ابتدا کاراکتر مساوی = را تایپ می کنیم و سپس فرمول را طبق قواعدی که در این درس خواهیم آموخت، به سلول وارد می کنیم.

همانطور که در تصویر زیر می بینید، پس از وارد کردن کاراکتر مساوی در سلول E13 می توان از نوار فرمول، یکی از فرمول های پیش فرض اکسل را انتخاب کرد.

 

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

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

مقادیر ثابت

مقادیر ثابت مقادیری هستند که ثابت بوده و تغییر نمی کنند. این مقادیر می توانند عددی یا متنی باشند. در فرمول نویسی باید مقادیر ثابت متنی را بین دو علامت ”  قرار دهیم. در این صورت محاسبه ای روی این مقادیر انجام نمی شود.

آدرس سلول ها

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

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

عملگرها

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

عملگرهای محاسباتی: عملگرهای محاسباتی، عملگرهایی هستند که از آنها برای محاسبات عددی استفاده می شود. این عملگرها عبارتند از و – و * و / و ٪ و ^ که به ترتیب و از راست به چپ برای محاسبه جمع، تفریق، ضرب، تقسیم، درصد و توان به کار می روند.

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

این عملگرها عبارتند از =، <، =<، >، => و <>. این عملگرها به ترتیب و از راست به چپ مساوی، بزرگ تر، بزرگ تر مساوی، کوچک تر، کوچک تر مساوی و نامساوی بودن دو عدد را مقایسه می کند.

نتیجه حاصل از عملیات این عملگرها می تواند مثبت (درست) یا منفی (نادرست) باشد.

به طور مثال نتیجه حاصل از عملیات 5=4 یک نتیجه �نادرست� است چرا که عدد ۵ مساوی با عدد ۴ نیست.

عملگرهای رشته ای: از این عملگر برای چسباندن دو رشته به هم استفاده می شود. استفاده از عملگر & در سلول ها، برای اتصال یا الحاق داده های متنی است.

به طور مثال اگر مقدار سلول B3  =”سلام” باشد و سلول C3 =”جهان”، می توانیم در سلول D3 عبارت B3&C3 را به عنوان فرمول یعنی پس از تساوی وارد کنیم تا در این سلول عبارت “سلام جهان” درج شود.

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

به طور مثال نتیجه عبارت A1:A10 سلول های A1 تا A10 است اما برای اشاره به سلول های خاص از عبارت A1;A5;A7 استفاده می شود.

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

()، ٪، ^، * /، -، &

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

در حالت اول حاصل عبارت 4*2 10 عدد 18 خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است.

اما اگر این عبارت را به صورت (10 2)*4 بنویسیم، حاصل 48 خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

توابع

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

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

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

• توابع مالی

• توابع تاریخ و ساعت

• توابع ریاضی و مثلثاتی

• توابع آماری

• توابع جستجو و مرجع

• توابع پایگاه داده

• توابع متنی

• توابع منطقی

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

• توابع مهندسی

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (…. ; ورودی ۳; ورودی ۲; ورودی ۱) نام تابع

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

در تصویر زیر، نام تابع SUM است و ورودی های تابع نیز سلول های A1 و A2 هستند.

 

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

درج فرمول با استفاده از Function Wizard در اکسل

اکسل فرمول های زیادی دارد و همانطور که پیشتر اشاره شد، هر تابع نیاز به �ورودی هایی� برای محاسبه دارد. با استفاده از امکان Function Wizard می توانید از تمام توابع اکسل استفاده کنید و نتیجه محاسبات را در سلول مورد نظر مشاهده کنید.

برای این کار از روی نوار فرمول، بر روی fx کلیک کنید تا کادر Insert Function باز شود.

 

توضیح کادر Insert Function: در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید.

در بخش Or select a category می توانید یکی از بخش های موجود را انتخاب کنید.

این بخش ها توابع اکسل را دسته بندی کرده است. مثلا اگر بخش Financial را انتخاب کنید، توابع مالی در کادر Select a function نشان داده خواهند شد.

با انتخاب گزینه All همه توابع اکسل و با انتخاب گزینه Most Recently used نیز توابعی که اخیرا استفاده کرده اید نشان داده می شوند.

اکنون در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید و از بخش Select a function آن را انتخاب کنید.

حال فرض کنید می خواهیم در کادر Insert Function تابع جمع را درج کنیم. همانطور که در مثال درج فرمول از طریق وارد کردن علامت = نیز گفتیم، این تابع با نام SUM شناخته می شود.

پس از انتخاب تابع SUM بر روی دکمه OK کلیک می کنیم. در پنجره جدید که Function Arguments نام دارد، باید ورودی های تابع را وارد کنیم.

در این مثال سه عدد  ۱۲ و ۱۰ و ۸ را وارد می کنیم.

 

اکنون با کلیک بر روی OK نتیجه این تابع که عدد ۳۰ است در سلول انتخاب شده نمایش داده می شود.

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

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

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

شرح مسئله :

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

شرط اول : کدام طبقه

بازه  E5:E1000 ستونی است که طبقات ما در ان مشخص است و i5 سلولی است که شرط ما به ان حساس است (در اینجا STORY5 شرط مورد نظر ماست)

شرط دوم : نام تیر

بازه  B5:B1000 ستونی است که نام تیر ما در ان مشخص است و j6 سلولی است که شرط ما به ان حساس است (در اینجا b7 شرط مورد نظر ماست)

شرط سوم : ابعاد تیر

بازه  C5:C1000 ستونی است که ابعاد تیرهای ما در ان مشخص است و k6 سلولی است که شرط ما به ان حساس است (در اینجا B45xB45 شرط مورد نظر ماست)

شرط چهارم : محل قرارگیری ارماتور

بازه  D5:D1000 ستونی است که محل قرارگیری ارماتور در ان مشخص است ( چپ – میانه – راست ) و m5 سلولی است که شرط ما به ان حساس است (در اینجا End-i شرط مورد نظر ماست)

 

توجه : ممکن است بگویید تابع SUMIFS در اکسل مقدار را جمع میکند ولی بر نمیگرداند جواب من این است که با توجه به تعداد شروطی که برای ان گذاشته ایم امکان ندارد دو مقدار باشد که هر چهار شرط بالا را با هم ارضا کند البته روش دیگری است که با استفاده از ترکیب دو تابع Match و index می توانید چندین شرط ایجاد کنید.

نکاتی در مورد چگونگی کپی کردن Worksheet بدون کپی فرمول ها در اکسل

در هنگام کار با اکسل شاید برای شما هم زیاد پیش آمده باشد که بخواهید یک worksheet را به یک workbook دیگر کپی کنید اما نخواهید فرمول ها قابل مشاهده شود.

 

با روشی که در آموزرش اکسل امروز  می آموزید، فرمول ها در فایل اصلی می مانند و فقط شما داده ها را به workbook دیگر کپی خواهید کرد.

برای این کار فایل اصلی را باز کنید، و در sheet که داده ها به همراه فرمول ها در آن موجود هستند بروید. روی sheet راست کلیک کرده و “Move Or Copy” را بزنید.

 

در صفحه ای که باز می شود، از منوی انتخاب “To book”، “(new book)” را انتخاب کنید و بعد تیک  “Create a copy” را هم بزنید. در پایان هم روی دکمه OK کلیک کنید.

 

بدین ترتیب یک  workbook جدید باز خواهد شد و داده های که در فایل قبلی داشتید، اینجا paste خواهند شد.

حالا برای انتخاب همه سلول های حاوی داده ها، از کلید ترکیبی Ctrl+A  استفاده کنید، یا مطابق تصویر روی گوشه ی بالایی سلول ها کلیک کنید.

 

حالا در زبانه ی Home، روی Paste کلیک کنید، و بعد مطابق تصویر “Values” را انتخاب کنید.

 

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

 

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

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

 

نکته مهم قبل از شروع آموزش:

کاربران Excel 2016 در صورت مواجه شدن با خطاهای مختلف در هنگام وارد کردن فرمولها، توصیه میشود قبل از باز کردن اکسل فرمت فعلی نوشتار ویندوز را به English تغییر دهید. (با سرچ Region در ویندوز ۱۰ و ۸ به این بخش دسترسی خواهید داشت):

 

انتخاب یکجای همه سلولها در اکسل

 

انتخاب دسته‌ای از سلولها در اکسل کاری ندارد، روی سلولهای مورد نظر کلیک و درگ میکنید. اما اگر بخواهید همه سلولها را انتخاب کنید چه؟ دو روش وجود دارد، یا از کلیدهای ترکیبی Ctrl A در ویندوز و یا Command A در مک استفاده کنید یا بر روی سلول کوچکی که در گوشه بالا سمت چپ یا راست وجود دارد کلیک کنید.

 

رنگی کردن ردیفها در EXCEL

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

برای شروع، منطقه موردنظر را هایلایت کنید (یعنی با درگ کردن، محدوده جدول را انتخاب کنید، میتوانید با ترفند قبلی کل صفحه گسترده را انتخاب کنید)، اکنون همانطور که در تب home هستید همانند تصویر روی دکمه Formatting Conditional کلیک کرده و سپس New Rule را انتخاب کنید

بر روی منوی کشویی style در پنجره ظاهر شده کلیک کرده و گزینه Classic را برگزینید.

بعد از این کار، پنجره دیگری ظاهر میشود که بایستی Use a formula to determine which cells to format را انتخاب کنید. با استفاده از این گزینه میتوانید با تعریف فرمول به صورت خودکار آن سلولهایی که بایستی استایل داده شوند را تعیین کنید.

برای مثال این فرمول را وارد و رنگ مورد نظر را انتخاب کنید تا سطرها یک درمیان رنگ شوند:

=MOD(ROW(),2)

نمایش و یا مخفی کردن سطرها

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

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

 

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

 

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

 

نحوه استفاده از VLOOKUP  در اکسل

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

 

 مشاهده میکنید که هر محصول در ستون Item یک کد منحصر به فرد دارد و قیمت معادل آن نیز در ستون price بیان شده، ما میخواهیم تابعی تعریف کنیم تا کاربر بتواند با وارد کردن کد محصول (در سلول جلوی Enter item)، قیمت متناظر آن را در سلول پایین (جلوی Price) نمایش دهد. برای این کار در سلول جلوی Price جایی که میخواهید نتیجه نمایش داده شود این فرمول را وارد کنید:

=vlookup(I3,B2:D11,3)

آرگومان اول یعنی مقداری که باید جستجو کنم را از کدام سلول دریافت کنم (در اینجا چون در سلول مقابل Enter Item مقدار وارد میشود یعنی I3) ، آرگومان دوم محدوده جستجو را درخواست میکند و آرگومان سوم ستونی که باید مقدار آن استخراج و نمایش داده شود.

 

روش ساختن لیستهای کشویی

اگر میخواهید مقادیر ورودی را برای کاربران محدود کنید، منوهای کشویی راه حل خوبی برای این کار هستند. یک سلول خالی را انتخاب کنید، از تب Data و مطابق تصویر Data Validation را برگزینید. در تب Settings پنجره ظاهر شده گزینه List را انتخاب کنید، اکنون با انتخاب سلولهایی که مقادیر مورد نظر شما در آن است آیتمهای این لیست را تامین کنید. مراحل کار به صورت تصویری:  

 

نمایش صفرهای ابتدایی اعداد

اکسل به صورت پیش‌فرض در هنگام وارد کردن اعدادی مثل ۰۰۰۱ صفرهای ابتدایی آن را حذف میکند، برای ممانعت از این کار کافی است تا یک علامت نقل قول تکی (‘) در ابتدای عدد اضافه کنید. مثلا ۰۰۰۱’

 

 

ادغام کردن مقادیر

بعضی اوقات لازم دارید تا مقادیر چند سلول مختلف را با یکدیگر ادغام و به صورت واحد نمایش دهید. به عنوان مثال در تصویر ذیل اگر بخواهیم مقادیر سه فیلد Last name، FirstName و position را کنار هم بیاوریم از این فرمول استفاده میکنیم:

=concat(C6,B6,D6)

 

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

=concat(C6,” “,B6,” “,D6)

 

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

 

 

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

در حالت پیش‌فرض وقتی متنی طولانی وارد یک سلول میکنید، اگر محتوای مورد نظر بیشتر از اندازه سلول باشد روی سلولهای دیگر کشیده میشود که ظاهر خوشایندی ندارد. برای حل این مشکل روی سلول مورد نظر کلیک راست کرده و Format Cells را بزنید، در تب Alignment گزینه Wrap Text را انتخاب و ok کنید. طول و عرض سطر مورد نظر را نیز میتوانید به دلخواه خود تغییر دهید. مراحل کار به صورت تصویری:

نمایش تب Developer

این تب به صورت پیش فرض در اکسل مخفی است، اگر نیاز داشته باشید تا کارهایی مثل نوشتن ماکروها را تجربه کنید به وجود این تب نیاز خواهید داشت، برای نمایش آن اگر اپلی هستید مطابق تصویر پنجره Preference را باز کنید و Ribbon & Toolbar را کلیک کنید و اگر ویندوزی هستید از منوی فایل و انتخاب Options و انتخاب Customize Ribbon اقدام کنید.

 

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

 

راه کارهای پنهان ساختن سلول ها، ردیف ها و ستون ها را در اکسل (Excel)

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

 

 

پنهان سازی سلول های اکسل

در اکسل نمی توان یک سلول را بصورت کامل پنهان ساخت اما می توان آن را بصورت ظاهری جایگزین نمود. بدین منظور سلول یا سلول های (با نگه داشتن کلید Ctrl یا Shift) خود را انتخاب کنید. بعد از آن کلیک راست کرده و بر روی گزینه ی Format Cells کلیک کنید.

 

در پنجره ای که نمایش داده می شود مطمئن شوید که تب Number انتخاب شده است. در پنل Category، قسمت Custom را انتخاب کرده و سپس در فیلد Type عبارت ;;; را تایپ نمایید. توجه داشته باشید که شما قادر خواهید بود بعداً جهت نمایش دوباره محتوا، این نوع فیلد را به حالت اولیه باز گردانید. سرانجام بر روی دکمه ی OK کلیک کنید.

 

 

 

مشاهده خواهید کرد که اطلاعات موجود در سلول پنهان شده است اما مقدار یا فرمول آن هنوز موجود بوده و در داخل نوار فرمول (Formula Bar) نمایش داده می شود.

 

 

 

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

 

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

اگر یک پروژه عظیم اکسل را دارا می باشید ممکن است قصد مخفی کردن برخی از ستون ها یا ردیف ها را داشته باشید. برای ردیف ها می توانید بر روی عدد آن کلیک راست کرده و سپس گزینه ی Hide را انتخاب کنید. در صورتیکه قصد دارید چندین ردیف را پنهان کنید می توانید کلید Ctrl را نگه داشته و تمام ردیف های موردنظر خود را انتخاب نمایید. سرانجام بر روی یکی از آنها کلیک راست کرده و بر روی گزینه ی Hide کلیک کنید. 

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

 

 

 

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

 

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

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

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

 

 

نکاتی درمورد Sheet های اکسل

کته هایی در مورد sheet های اکسل

1- تغییر نام Sheet ها

2- اضافه کردن Sheet ها

3- حذف Sheet ها

4- انتقال Sheet ها در یک پوشه

5- کپی کردن Sheet ها در یک پوشه

6- انتقال یک Sheet از book هائی به book دیگر

7- کپی کردن Sheet های از یک کار پوشه به کار پوشه دیگر

 

تغییر نام Sheet ها :
روش اول :
۱- انتخاب Sheet
۲- انتخاب منوی Format
۳- انتخاب گزینه Sheet
۴- انتخاب گزینه Rename
روش دوم :
۱- راست کلیک بر روی Sheet
۲- انتخاب گزینه Rename
روش سوم :
دابل کلیک بر روی Sheet
 
 
اضافه کردن Sheet ها :
روش اول :
۱- انتخاب Sheet که می خواهیم Sheet جدید قبل از آن قرار گیرد.
۲- انتخاب منوی Insert
۳- انتخاب گزینه Work Sheet
۴- OK
روش دوم :
۱- راست کلیک بر روی Sheet هایی که می خواهیم Sheet جدید قبل از آن قرار گیرد .
۲- انتخاب گزینه Insert
۳- انتخاب گزینه Work Sheet
۴- Ok
 

حذف Sheet ها در اکسل  :
روش اول :
۱- انتخاب Sheet
۲- انتخاب منوی Edit
۳- انتخاب کزینه Delete Sheet
روش دوم :
۱- راست کلیک بر روی Sheet
۲- انتخاب گزینه Delete
 
انتقال Sheet ها در یک پوشه
روش اول :
۱- انتخاب Sheet
۲- انتخاب منوی Edit
۳- انتخاب گزینه move or copy sheet
۴- در پنجره باز شده در کادر Before sheet نام Sheet هایی را که می خواهیم این Sheet قبل از آن قرار گیرد انتخاب می کنیم یا اگر می خواهیم به انتها انتقال دهیم ، Move to end را انتخاب می کنیم .
۵- OK
روش دوم :
۱- راست کلیک بر روی Sheet
۲- انتخاب گزینه Move or Copy
۳- در پنجره باز شده در کادر Before Sheet نام Sheet یی را که می خواهیم این Sheet قبل از آن قرار گیرد انتخاب می کنیم .
۴- OK
 
 
کپی کردن Sheet ها در یک پوشه :
مانند انتقال Sheet ها می باشد فقط کافی است در انتها در کنار Create a copy تیک بزنیم .
 
 
انتقال یک Sheet از book هائی به book دیگر  در excel:
۱- راست کلیک بر روی Sheet
۲- انتخاب گزینه move or copy sheet
۳- در کادر To Bookنام فایل مورد نظر را که می خواهیم Sheet را به آن انتقال دهیم انتخاب می کنیم .
۴- در قسمت Before Sheet هم نام Sheet هایی راکه می خواهیم Sheet انتقالی قبل از آن قرار گیرد ، انتخاب می کنیم .
۵- OK
 
 
کپی کردن Sheet های از یک کار پوشه به کار پوشه دیگر  در اکسل :
تمام مراحل ماند انتقال است فقط در کادر Create a copy تیک می زنیم .
 
 
تذکر : باید توجه داشته باشیم که Book ئی که می خواهیم فایل را به آن منتقل یا کپی کنیم حتما باز باشد .

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

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

 

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

 

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

 

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

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

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

در اکسل برای ذخیره فایل هایی که دارای کد VBA و ماکرو هستنددر اکسل میتونیم از دو فرمت XLSM و XLSB استفاده کنیم. برای اینکار  در پنجره SAVE AS اکسل یکی از این دو فرمت رو برای ذخیره ماکرو انتخاب کنیم.

 

زمانی که فایل اکسل را با فرمت XSM یا XLSB ذخیره میکنیم ماکروها و کدهای VBA ما هم در فایل ذخیره میشود و زمانی که این فایل رو به سیستم دیگری منتقل کنیم میتوانیم از کدهایی که نوشتیم استفاده کنیم.

 فرض کنید بخواهیم کدی رابنویسیم که بتوانیم آن کد را به سیستم دیگه ای انتقال دهیم و در تمام فایل های اکسلی که ایجاد میکنیم (نه فقط فایل XLSM که ذخیره کردیم) از آن کد استفاده کنیم. به عنوان مثال اگر بخواهیم تابعی را وارد کنیم  که بتوانیم ازآن در  فرمول نویسی های در تمام فایل های اکسل استفاده کنیم. برای اینکار میتاونیم از فرمت XLAM اکسل استفاده کنیم که برای تهیه Add-In ها در اکسل استفاده میشود. این فرمت در واقع بسیار شبیه فرمت XLSM هست با تفاوت های جزئی. برای استفاده از این فرمت ابتدا باید فایل خودرا در پنجره SAVE AS اکسل با فرمت XLAM ذخیره میکنیم و به سیستم موردنظرانتقال بدهید. اکنون کافیست این فایل رادر نرم افزار اکسل به صورت یک Add-In اضافه کنید. در پنجره تنظیمات اکسل وارد بخش Add-Ins میشویم و در قسمت پایین پس از انتخاب Excel Add-Ins در بخش Manage رو دکمه Go کلیک میکنیم.

 

با انتخاب دکمه GO پنجره Add-Ins باز میشود.

 

حالا روی دکمه Browse کلیک میکنیم و فایل XLAM را انتخاب میکنیم. با اینکار فایل ما به عنوان یک Add-In به اکسل اضافه میشود و میتوانیم از توابع و کدهای آن استفاده کنیم.

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

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

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

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

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

 

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

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

در نرم افز ار Word این امکان وجود دارد که به راحتی یک متن تایپ شده را با یک سطر و ستون دیگر جابجا کنید اما اگر بخواهیم مقادیر وارد شده در سلول های جدول اکسل را بدون تغییر جابجا کنیم اعمال زیر را انجام میدهیم                                                                                                                              فرض کنید در یک سلول شماره تماس و در سلول دیگر آدرس را وارد کرده باشید . حال آدرس ها اشتباه وارد شده و مقادیر سلول آن به یک ردیف بالا یا پایین جدول باید انتقال پیدا کند . ساده ترین راهی که به نظر شما میرسد کپی دوباره متن یا استفاده از کلیدهای Ctrl + C و Ctrl+V می باشد . اما روش ساده تری نیز در برنامه وجود دارد که بعنوان یک ترفند مورد استفاده قرار می گیرد . در ادامه این روش را توضیح میدهم .

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

نکاتی در موردفیلتر کردن اطلاعات در اکسل با استفاده از دستور Contains

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

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

Text Filters > Containدر اکسل

2. سپس گزینه Text Filters > Containرا انتخاب کنید .                                                             

3. یک پنجره ه نمایش داده می شود . 

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

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

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

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

ابتدا تصاویر را در ستون A قرار دهید. (Inser Picture) اندازه سلول باید متناسب با سایز تصویر باشد. در ستون B اسامی یا کد پرسنلی مربوط به هر تصویر را وارد نمایید. در ستون C و در یک سلول دلخواه مثلاً C1 یک لیست بازشو از اطلاعات ستون B ایجاد نمایید.

در یک سلول دلخواه مثلاً  T1 فرمول را به شکل  INDEX($A$1:$A$4,MATCH($C$1,$B$1:$B$4,0)) بنویسید و این فرمول را در حافظه کپی نمایید. (ctrl+C) و بعد کلیک Esc را فشار دهید.

اکنون به سلول T1 نامی را اختصاص دهید. برای این کار از سربرگ Formulas بر روی Define Name کلیک نمایید. از کادر ظاهر شده و در قسمت Name یک نام دلخواه (مثلاً Picture) را وارد نموده و در کادر Refers to  فرمولی را که قبلاً در حافظه کپی کرده اید قرار دهید. (ctrl+V)

اکنون یکی از عکسها را کپی کرده و در سلول کناری لیست بازشو Paste کنید. در حالی که تصویر در حال انتخاب است در قسمت فرمول بار در بالای صفحه کلیک نموده و پس از تایپ علامت تساوی نامی را که قبلاٌ برای سلول T1 انتخاب کرده بودید (pictur) تایپ نموده و کلید Enter را فشار دهید.

تذکر: بعد از اتمام کار فرمول نوشته شده را می توان حذف نمود.                                                     حفاظت از فایل های اکسل                                                  

یکی دیگر از  ابزارهای مفید در اکسل paste picture link می باشد. این ابزار برای حفاظت از فایل های اکسل، در صورتی که قصد دارید آن را در اختیار دیگران قرار دهید بسیار مفید و پرکاربرد می باشد.

 

فرض کنیم قصد دارید فایل اکسلی را که تهیه کرده اید در اختیار دیگران قرار دهید بطوری که فقط قادر به مشاهده آن باشند و نتوانند تغییری در آن ایجاد کنند. برای این منظور یکی از روش ها این است که بر روی شیت مورد نظر پسورد قرار دهید و روش دیگر آن است که با استفاده از ابزار paste picture link آن را به یک تصویر تبدیل کرده و در اختیار سایرین قرار دهیم. استفاده از ابزار paste picture link عکس گرفته شده را به یک عکس دینامیک تبدیل می کند. یعنی با تغییر در فایل اصلی، محتوای عکس ایجاد شده تغییر می کند. حتی اگر ناحیه ای از کاربرگ (Worksheet) را هایلایت کنید، این تغییر در عکس گرفته شده نیز اعمال می شود.

 

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

 

• ناحیه مورد نظر را انتخاب می کنیم
• با کلید ctrl+c آن را در حافظه کپی می کنیم
• سربرگ Home را درحالت انتخاب قرار می دهیم
• در نوار ابزار زیر سربرگ Home روی فلش کوچک کنار ابزار Paste کلیک می کنیم
• از ابزارهای ظاهر شده بر روی Linked picture کلیک می کنیم
• برای خروج از دستور کلید Esc را فشار می دهیم
• تصویر ایجاد شده را با موس از روی ناحیه انتخابی جابجا می کنیم

 

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

 

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

در نر م افزار  اکسل برای نمایش کاربرد توابع محبوب مالی در اکسل ما یک مثال را در نظر گرفته ایم، یک وام ۲۴۰ قسطی به مبلغ ۱۵۰۰۰۰ دلار و با سود سالیانه ۶ درصد که در ماه می شود ، ۰.۵ درصد و ارزش آینده(fv)=0 !! به این معنی که در آینده این هدف را دنبال میکنیم که این پول صفر شود! یعنی تمام قسط ها پرداخت شود و بدهیی نداشته باشیم! پس در نتیجه ارزش حال(pv) این وام همان ۱۵۰۰۰۰ دلار می باشد.

 

تابع PMT در اکسل

 

 

دقت کنید که fv ارزش آتی است! و ما میخواهیم این ارزش در آینده صفر شود!!یمقدار پول ۱۵۰۰۰۰ هست که در pv یا ارزش فعلی باید قرار دهیم و Nper هم تعداد اقساط است که ۲۴۰ ماه یا ۲۰ سال می باشد، و سود ماهیانه نیز ۰.۵۰ می باشد، حال با تابع PMT قسط هر ماه را محاسبه می کنیم.

 

تابع Rate در اکسل

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

 

تابع Nper در اکسل

اگر مقادیر صود، مقدار هر قسط ، مبلغ کل وام ، و آرش آینده (۰) را داشته باشید می توانید با تایع Nper تعداد قسط ها را بدست آورید.

 

نگاه کنید که اگر مبلغ ماهانه ای که باید پرداخت کنیم را زیاد کنیم چه رخ میدهد:

 

قسط ها ۹۰ ماهه تمام می شوندو  جون ماه آخر مبلغ قسط کمتر از مبلغ وارده است به ۹۰ نرسیده است

تابع pv در اکسل

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

 

تابع fv در اکسل

اگر تعداد قسط، مبلغ هر یک، و مبلغ اولیه و درصد سود را به این تابع بدهید آن مقدار باقی مانده را به شما میدهد.

 

باقی مانده قسط بعد از ۲۴۰ پرداخت صفر شد!

اما اگر ماهی ۱۰۰۰ دلار پرداخت میکرد! بعد از ۲۴۰ پرداخت همچنان حدود ۳۴۰۰۰ دلار بدهکار بودثطزثم

چگونه علامت ( & ) را در قسمت Header و Footer برنامه Excel

اگر تا به حال علامت (&) را در قسمت Header و Footer برنامه اکسل تایپ کرده  باشید متوجه خواهید شد که این علامت در هنگام چاپ سند ناپدید می شود . در این آموزش اکسل  راه حلی را خواهیم آموخت که بتوانید علامت ها را در قسمت Header و Footer تایپ کنید و دیگر شاهد ناپدید شدن آن ها نباشید .

برای مثال امکان دارد شرکت ها در اسم خود از این گونه علامت ها استفاده کرده باشند همانند شرکت خارجی Smith & Jones که یک شرکت حقوقی است و یا کمپانی bang & olufsen که به صورت تخصصی در زمینه تولید اسپیکر کار می کند و بخواهند از اسم شرکت در قسمت Header و Footer برنامه اکسل استفاده کنند . اگرچه برنامه اکسل از علامت ها در قسمت Header و Footer به عنوان marker استفاده می کند که نیازمند یک formatting code  به خصوص است اما در این جا ما قصد داریم نحوه قرار گیری این علامت ها در متن شما در قسمت Header و Footer را بیاموزیم.
برای اضافه کردن Header و Footer در workbook در برنامه اکسل بر روی زبانه Page Layout کلیک کنید .

در این زبانه و در قسمت Page Setup بر روی پیکان کوچکی که در این قسمت و در سمت پایین و راست آن قرار دارد کلیک کنید .

کادر Page Setup به نمایش در خواهد آمد . بر روی زبانه Header/Footer کلیک نمایید .
 

در این قسمت  یکسری Header و Footer  ها به صورت آماده وجود دارند اما ما می خواهیم که یک Header و Footer به صورت شخصی سازی شده ایجاد کنیم . برای مثال ما قصد داریم که یک header را به workbook خود اضافه کنیم , بنابر این بر روی Custom Header کلیک نمایید . این مراحل برای footer هم به صورت یکسان است و تفاوتی ندارد .                                                                                                            در کادر مرتبط با Header ( و یا Footer ) متنی را که می خواهید در قسمت header یا footer به نمایش در بیاید را وارد کنید . شما می توانید بنا به سلیقه خودتان متن خود را در قسمت چپ, مرکز و یا سمت راست وارد نمایید.
زمانی که شما در حال وارد کردن متن خود هستید علامت های مورد نظر را دو بار پشت سر هم نمایید .
بعد از اتمام تایپ بر روی گزینه ok کلیک نمایید .                                                                  

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

  در نرفرم افزار اکسل برای سهولت کاربا اکسل راهکارهای زیادی وجود دارد .                          در آموزش اکسل امروز با چند نمونه از کاربرد اکسل را مورد بررسی قرارمیدهیم                                                                                                                                           باز کردن پنجره ی  Format Cells
1.راست کلیک در یک سلول و انتخاب گزینه ی Format Cells
2.کلید Ctrl+1
3.از منوی Format گزینه ی Cells را انتخاب کنید.
گرد کردن اعداد تا 2 رقم اعشار در اکسل
سلول  مذکور را انتخاب کنید و پنجره ی
Format Cells را باز کنید در زبانه ی Number و در قسمت Category گزینه ی Number
را انتخاب کنید.مقدار Decimal Places را به 2 تغییر دهید.(به طور پیشفرض مقدار ٢ هست پس نیازی به تغییر آن نیست)  OK را بزنید خواهید دید عدد به 19.88 تغییر می کند.
چینش افقی در سلول

در پنجره ی  Format Cells و در زبانه ی Aligment گزینه های Horizontal برای ترازکردن به صورت افقی تعبیه شده اند...

Left Indent:برای تراز از چپ
Right Indent:برای تراز از راست
Center:برای قرار گرفتن متن در مرکز سلول از لحاظ افقی
Center Across Selection:داده ی اولین سلول محدوده را وسط محدوده ی انتخابی قرار می دهد.

قرار دادن تیتر یک جدول در وسط آن با استفاده از   Center Across Selectio در اکسل

برای مثال در سلول A2 نام و در سلول B2 نام خانوادگی را وارد کنید.در سلول A1 تایپ کنید "اسامی کارمندان" سپس محدوده ی  A1:B1 را انتخاب کنید و پنجره ی Format Cellsرا باز کنید(کلید Ctrl+1) در زبانه ی Aligment و در قسمت Horizontal گزینه ی Center Across Selection را انتخاب کرده و OKرا بزنید.خواهید دید که تیتر جدول روی مرز سلول ها قرار می گیرد.برای جلوگیری از بروز مشکلات بعدی بهتر است به جای استفاده از ادغام سلول ها از این گزینه استفاده کنید.

چینش عمودی در سلول
در پنجره ی Format Cells و در زبانه ی Aligment گزینه های Vertical برای ترازکردن به صورت عمودی تعبیه شده اند.
نکته:در حالت معمولی با انتخاب گزینه های این بخش هیچ تغییری در داده های سلول مشاهده نمی شود،بنابراین ابتدا سلول را به صورت عمودی کشیده و سپس تغییرات این بخش را مشاهده کنید...

Top:قرار گرفتن داده در بالای سلول
Center:قرار گرفتن داده در مرکز سلول
Bottom :قرار گرفتن داده در پایین سلول

چرخش متن تا 90 درجه
در حالت معمول متن در صفر درجه نمایش داده می شود بعضی اوقات لازم است تا متن را بچرخانیم برای این منظور سلول حاوی متن را انتخاب کرده ،پنجره ی Format Cells را باز می کنیم و در زبانه Aligment در قسمت Orientation مقدار  Degreesرا بین 90 درجه ی مثبت و منفی تغییر می دهیم.

کاربرد Wrap text در پنجره ی Format Cells زبانه Aligment
در مواقعی که متنی را در سلولی تایپ کرده ایم که آن متن از سلول بزرگتر بوده می توانیم این گزینه را تیک دار کنیم تا متن در چند سطر نمایش داده شود و به سلولهای مجاور تجاوز نکند.

نوع ،اندازه و رنگ قلم
محدوده یا سلولی را انتخاب کنید .پنجره ی Format Cellsرا بازکنید(کلیدCtrl+1) در زبانه ی Font مشخصات زیر را مشخص کنید...
Font:نوع قلم را مشخص کنید.
Size:اندازه ی قلم را مشخص کنید.
color:رنگ قلم را مشخص کنید.

انتخاب رنگِ زمینه سلول یا محدوده
سلول یا محدوده ای را انتخاب کنید و پنجره ی Format Cells را باز کنید(کلیدCtrl+1) در پنجره مذکور به زبانه ی Patterns رفته و یکی از رنگ ها را انتخاب کنید.

خط کشی و رسم جدول در اکسل
ابتدا سلول یا محدوده ای را که قصد دارید آن را خط کشی کنید انتخاب کنید.پنجره ی  Format Cellsرا باز کنید(کلیدCtrl+1) به زبانه ی Border بروید.ابتدا در قسمت Line استایل خط را با کلیک ماوس مشخص کنید ، سپس در قسمت Color رنگ خط کشی را مشخص کنید و پس از آن در قسمت Presetsیکی از گزینه ها را انتخاب کنید در کادر پیش نمایش با کلیک ماوس بر روی خطوط داخل کادر یا با استفاده از دکمه های کادر ِ پیش نمایش(در اطراف کادر) خطوط را یکی یکی حذف کرده یا اضافه کنید.

حذف مرز بین سلولها در اکسل:
برای این کار از منوی Tools گزینه ی Option را انتخاب کنید و در زبانه ی View تیک گزینه ی Gridlines را بردارید.

 

نکته1:برای تیتر از ادغام سلولها استفاده نکنید.
نکته 2: اعداد باید تا 2رقم اعشار نمایش داده شوند.(اعداد بیشتر از 2 رقم اعشار باید به صورت خودکار گرد شوند)
نکته 3:تمامی عملیات را فقط با پنجره ی Format Cells  انجام دهید.

کاربرد زبانه ی Protection در پنجره ی Format Cells
این زبانه دو گزینه دارد یکی Locked برای قفل کردن و دیگری Hidden برای مخفی کردن.

قفل کردن یک سلول در یک برگه(شیت)
در حالت معمول تمام سلولهای یک شیت در حالت قفل قرار دارند بنابراین برای اینکه فقط یک سلول را قفل کنید لازم است تا بقیه ی سلولها را از حالت قفل خارج کنید برای این کار کل سلولهای یک برگه را انتخاب کنید (به وسیله ی یکی از کلیدهایCtrl+A , Ctrl+Shift+Space) . پنجره ی Format Cells را بازکنید(کلیدCtrl+1)در پنجره ی مذکور به سربرگ Protection رفته و تیک Locked را بردارید و OKرا بزنید تا تمام سلولها از حالت قفل خارج شوند.سلول حاوی داده ای را که می خواهید محافظت شود انتخاب کنید،پنجره ی Format Cells را بازکنید و  در زبانه ی Protection  گزینه ی Lockedرا تیک دار کنید و OK را بزنید.از منوی Toolsبه قسمت Protectionو سپس گزینه ی Protect Sheet بروید (انتخاب رمز در این قسمت اختیاری است) OK را بزنید.خواهید دید که دیگر نمی توان در سلول قفل شده داده ای را وارد کرد.برای قفل کردن محدوده نیز به همین روش عمل می کنیم.
نکته:پس از انجام این عملیات پنجره ی Format Cells برای آن شیت غیر فعال می شود برای فعال کردن این پنجره برگه(شیت)را از حالت حفاظت شده خارج کنید...
از منوی Tools وقسمت Protection گزینه ی Unprotect sheetرا انتخاب کنید.
نتیجه:برای حفاظت سلولها 1.سلول باید قفل شده باشد 2.برگه باید حفاظت شده باشد.

پنهان کردن یک متن یا فرمول در نوار فرمول
گزینه ی دوم یعنی Hidden موقعی استفاده می شود که بخواهیم داده های موجود در سلول در نوار فرمول نمایش داده نشوند.برای این کار متن یا فرمولی را در سلول C1 وارد کنید.سلول C1 را انتخاب کنید و پنجره ی Format Cells را باز کنید(کلید  Ctrl+1)به زبانه ی Protectionبروید تیک Locked را بردارید و گزینه ی Hiddenرا تیک دار کنید و OK را بزنید سپس از منوی Tools در قسمت Protection گزینه ی  Protect sheet را انتخاب کرده و OK را بزنید.اکنون سلول C1را انتخاب کنید و نوارفرمول را نگاه کنید همانطور که می بینید محتوای واقعی سلول در نوار فرمول نمایش داده نمی شود.

نکته:بعد از انجام این عملیات کلیه ی سلولهای شیت به غیر از سلول C1 قفل می شوند و نخواهید توانست داده ای را وارد کنید برای رفع این مشکل ابتدا از منوی Tools و قسمت Protection گزینه ی Unprotect sheet را انتخاب کنید سپس کل شیت را انتخاب کرده پنجره ی Format Cells را باز کنید و در زبانه ی Protectionتیک گزینه ی Locked را برداشته OK کنید و سپس از منوی Toolsو در قسمت  Protection گزینه ی Protect sheet را زده و OK کنید.

نتیجه:برای پنهان کردن متن یا فرمول در نوار فرمول 1.سلول باید پنهان شده باشد 2.برگه باید حفاظت شده باشد.

 

 

 

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

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

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

• ابتدا داده‌ی اول و دوم را در سلول‌های دلخواه به ترتیب بنویسید (یعنی اعداد ۰ و ۲)
• حال این دو عدد را به کمک ماوس انتخاب کنید (درگ و دراپ کردن)

 

• مربع کوچکی در انتهای بخش انتخاب شده ظاهر شده است (قسمت پایین سمت چپ) که اگر ماوستان را بر روی آن قرار دهید نشانگر ماوس به صورت علامت + نمایش داده می‌شود.

 

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

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

 ایجاد لیست خودکار دلخواه در اکسل

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

• وارد سربرگ File شوید.
• وارد قسمت Option شوید.
• از سمت چپ گزینه‌ی Advanced را انتخاب کنید.
• حال در قسمت General بر روی گزینه‌ی …Edit Custom Lists کلیک کنید.

در نسخه‌های قدیمی‌تر اکسل این گزینه در آدرس زیر قرار دارد:

Tools → Option → Custom List (Tab) → New List                                                                     

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

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

 برای ایجاد لیست جدید  در excel در ابتدا گزینه‌ی NEW LIST را برگزینید. حال در قسمت List entries داده‌های مورد نظر خود را به ترتیب وارد کنید، هر داده را در یک خط                                                  

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

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

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

مطالبی ارزنده پیرامون تغییر علامت فاصله یا separator در فرمولهای اکسل

بسیاری از ما در هنگام فرمول نویسی و ایجاد توابع در اکسل با مشکل علامت فاصله یا separator در بین فرمول ها مواجه می شوند چون در بعضی از دستگاهها این علامت به کارکتر کاما (,) بوده و در بعضی از دستگاهها سمی کالن(;) که همین مغایرت و عدم توجه کاربر باعث اجرا نشدن فرمولها در اکسل خواهد شد .

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

از منوی start  بر روی گزینه control panel کلیک نمایید.

 

درفضای control panel   بر روی گزینه region and language    کلیک نمایید

 

در پنجره باز شده بر روی دکمه Additional Settings  کلیک نمایید

 

با این عمل پنجره customize format  برای شما فعال شده که امکان تغییر فرمت های اعداد و تاریخ و فاصله ها را فراهم می نماید در بخش list saparator کلیک کرده و علامت موجود را حذف و علامت دلخواه خود یعنی کاما یا سمی کالن را وارد نموده و سپس کلیه پنجره ها را ok نمایید ..

 

نکاتی پیرامون چگونگی پنهان کردن مطالب در اکسل

 

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

 

پنهان کردن یک برگه در اکسل

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

پنهان کردن یک سلول از جدول 

پنهان شدن یکی از چهار خانه‌های جدول (Cell) مثل یک برگه نیست که آن را پنهان کنید و سپس هر گاه خواستید آن را بازگردانید. اما واقعاً راهی ندارد چون اگر مخفی شود چیزی جای آن قرار نمی گیرد؟
در پایین بیشتر می آموزید اما بدانید شما می‌توانید هر تعداد ردیف یا ستون را که می‌خواهید خالی نمایش دهید در حالتی که آن‌ها واقعاً خالی نیستند.
برای این کار روی یک یا چند سلول راست کلیک کنید و Format Cells را انتخاب کنید.
در بخش Number، گزینه Custom را از پایین کلیک کنید و سه سمیکالن (,,,) بدون پرانتز در فضای Type وارد کنید.
سپس OK را بزنید و در کمال تعجب می‌بینید که همه بخش‌های انتخابی خالی شده اند! با کلیک کردن بر روی آن‌ها همچنان خالی هستند اما در نوار فرمول ها محتوای آن دیده خواهند شد.
برای دوباره نمایان کردن اطلاعات کافی است مراحل بالا را طی کرده و به جای Custom، گزینه Original را انتخاب کنید. دقت داشته باشید اگر در سلول‌ها هر متنی نوشته شده باشد، با زدن اینتر محو خواهد شد. همچنین با تایپ بر روی سلول‌های مخفی، هر چه پنهان کرده‌اند با متن جدید تایپ شده جایگزین خواهند شد.

پنهان کردن خطوط در اکسل

 

برای پنهان شدن خطوط فاصل، کافی است زبانه VIEW را انتخاب کرده و تیک Gridlines را بردارید. همچنین می‌توانید به Page Layout رفته و تیک View زیر بخش Gridlines را نیز بردارید.

پنهان کردن ستون‌ها و ردیف ها

اگر می‌خواهید یک ردیف یا ستون را کلاً پنهان کنید، بر روی سر برگ (Header) آن راست کلیک کرده و Hide را بزنید. برای مخفی کردن چندین ردیف نیاز به راست کلیک کردن بر روی شماره ردیف آن‌ها در سمت چپ دارید. برای مخفی کردن چند ستون این عمل را در بالای سربرگ آن‌ها انجام دهید.

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

پنهان کردن فرمول ها در excel

پنهان کردن فرمول ها کمی دشوار تر از بقیه مراحل است. اگر می خواهید فرمولی را پنهان کنید ابتدا باید آن را پنهان کنید و سپس از آن محافظت کنید.
برای این کار ابتدا، بر روی ستون F، راست کلیک کرده و Format Cells را انتخاب کنید.
اکنون روی زبانه Protection کلیک کنید و تیک گزینه Hidden را بزنید. سپس برای محافظت از سلول، بر روی Review کلیک کرده و Protect Sheet را انتخاب کنید.
اگر مایل هستید تا همه نتوانند فرمول های شما را از پنهانی خارج کنند، می‌توانید برای آن گذرواژه ای تعیین کنید. با فشردن دکمه های Ctrl+~ (همان پ بالای تب)  با کلیک بر روی Show Formulas در تب Formulas دیگر قابل مشاهده نخواهند بود.

پنهان کردن کامنت ها

کامنت ها یا همان اطلاعات اضافی مربوط به هر سلول، به طور خودکار با نوشته شدن،  به صورت یک نشانه قرمز در گوشه راست هر سلول قرار خواهند گرفت. برای تغییر حالت خودکار بر روی  File  و سپس Option،  و زبانه Advanced، روی No comments or indicators کلیک کنید. برای نمایش دادن کافی است Comments and indicators را مجدداً بزنید یا از Show All Comments استفاده کنید.

 

اگر هم در یک سلول یک متن بلند بالا نوشتید و به نوعی از اندازه خود خارج تر بود، کافی است روی آن راست کلیک کرده و Format Cells را بزنید. سپس از Alignment، در زیر Horizontal، گزینه Fill را انتخاب کنید. اکنون می بینیم که مشکل طولانی بودن برطرف شده است.

مطلبی در مورد تعیین اندازه سطرها ، ستون ها و خانه های جدول در Excel

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

نحوه کار با سطرها ، ستون ها و خانه های جدول در اکسل :

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

نحوه تنظیم عرض یک ستون در excel :

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

تنظیم عرض یک ستون بر روی یک اندازه دقیق در EXCEL  :

1. ستونی که می خواهید عرض آن را تغییر دهید ، انتخاب نمایید .
2. بر روی دستور Format از لبه Home کلیک نموده تا منوی آن مطابق تصویر زیر باز شود :
   
   
    
3. از منوی باز شده ، گزینه Column Width را انتخاب نمایید .
4. کادر Column Width برای تعیین اندازه بصورت زیر باز می شود . مقدار مورد نظر خود را در آن وارد نمایید .
   
   
    
   
5. بر روی گزینه OK کلیک نمایید . عرض ستون به مقدار تعیین شده تغییر می کند .

نکته : همچنین می توانید با انتخاب گزینه AutoFit Column Width کاری کنید تا اندازه عرض ستون ها و خانه های جدول همواره به اندازه محتویات درون آنها تنظیم شود .

نحوه تغییر اندازه ارتفاع سطرها در اکسل :

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

تعیین اندازه سطرها به اندازه دقیق در اکسل :

1. سطری که می خواهید ارتفاع آن را تغییر دهید ، انتخاب نمایید .
2. بر روی دکمه Format از لبه Home کلیک نمایید تا منوی آن مطابق تصویر زیر باز می شود :
   
   
    
   
3. گزینه Row Height را کلیک نمایید .
4. کادر تغییر اندازه سطر ( v fRow Height ) مطابق تصویر زیر باز می شود . اندازه مورد نظر خود را وارد نمایید . سپس OK را بزنید :
   
   
   امید واریم از آموزش اکسل امروز نیز نهایت استفاده رابرده باشید 

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

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

 

 

پنهان کردن فرمو ل در اکسل

همان طور که می دانيد برای محاسبات در اکسل می توان به راحتی فرمول هايی را تعريف کرد؛ اما برای پنهان کردن اين فرمول ها در ظاهر برنامه ابتدا تمام سلول هايی را که قصد پنهان کردن فرمول های موجود در آن را داريد، انتخاب کنيد.

 

سپس در سربرگ Home روی Format کليک و format cells را انتخاب کنيد. در ادامه به سربرگ Protection برويد و تيک گزينه Hidden را بزنيد و ok کنيد. دوباره به سربرگ Home برگرديد و در format اين بار گزينه Protect sheet را انتخاب کنيد.

 

يک رمز در کادر Password to unprotect sheet وارد کنيد و ok بزنيد و رمز را وارد کنيد. خواهيد ديد که ديگر اثری از فرمول ها در ظاهر برنامه نيست. برای برگشت به حالت اوليه در format گزينه unprotect را انتخاب کنيد.

 

طراحی چارت در اکسل

 

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

 

برای کپی کردن Chart Formats مورد نظرتان نيز ابتدا لازم است چارتی که فرمت دلخواهتان را دارد، انتخاب و سپس آن را با استفاده از کليدهای ctrl + c کپی کنيد. چارتی را که قصد تغيير فرمت آن را داريد، انتخاب و سپس Paste Special را از منوی edit بالای صفحه انتخاب کنيد. Formats را انتخاب کنيد و ok را بزنيد.

مطالبی ارزنده پیرامون انواع داده در اکسل

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

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

آشنایی با انواع داده در اکسل

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

 

 

اعداد در اکسل

اعداد در اکسل از اهمیت بسیاری برخوردارند، در مراتب حرفه ای تر کار با اکسل و مخصوصاً در یادگیری و استفاده از برنامه نویسی ویژوال بیسیک در اکسل، به اهمیت شناخت و تفکیک انواع داده در اکسل و کار با اعداد پی خواهیم برد. حتی اعداد نیز خود به چند دسته تعریف میشوند که در توصیف نوع متغیرها در ماکرو نویسی در اکسل بسیار مهم میباشد. داده های عددی در اکسل ممکن است حتی به فرمت متنی نیز ذخیره شوند که انجام اینکار گاهی لازم میباشد. برای توضیحات بیشتر فیلم آموزشی قرار داده شده در انتهای این مقاله را مشاهده نمایید. توابع ریاضی و مثلثاتی در اکسل (Math & Trig Functions) از جمله توابعی هستند که بر روی داده های عددی عملیات انجام میدهند.

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

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

مالی و حسابداری در اکسل

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

برای کار با توابع حسابداری و انجام امور مالی در اکسل لازم است کاربران با داده های نوع Currency آشنا باشند، لذا این نکته اهمیت این نوع داده ها را آشکارتر مینماید.

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

از جمله پر کاربردترین ابزارهای اکسل در حوزه های امور اداری، حسابداری و مستند سازی، توابع تاریخ و زمان (Date & Time Functions) می باشند، برای یادگیری کار با توابع تاریخ و زمان و البته انجام عملیات بر روی سلول های حاوی تاریخ و زمان، لازم است کاربران درک درستی از داده های نوع تاریخ و زمان داشته باشند. در مقالات آینده توابع تاریخ و زمان در اکسل مورد بررسی قرار خواهند گرفت.

 متن در اکسل

استفاده از توابع و داده های متنی چه در محیط اکسل و چه در زبان برنامه نویسی ویژوال بیسیک در اکسل همواره در سطوح بالاتر و حرفه ای تر قرار گرفته و این موضوع برای فایل های اکسل با زبان فارسی یادگیری مهارت های بیشتری را می طلبد. توابع متنی در اکسل (Text Functions In Excel) از جمله توابع بسیار مهم و عمومی در اکسل به شمار میروند که ماموریت آنها انجام عملیات بر روی داده های متنی میباشد. لذا شناخت داده های متنی و روش تعریف آنها از اهمیت بسیاری برخوردار است.

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

داده های Boolean در اکسل

داده های Boolean در اکسل داده هایی هستند که صرفاً میتوانند یکی از دو حالت True و False باشند. لازم به ذکر است در اکسل و ویژوال بیسیک، همواره عبارت False برابر با عدد صفر و عبارت True برابر با عدد یک میباشد. پس داده های Boolean داده هایی با دو مقدار صفر یا یک میباشند. البته داده ی نوع Null هم وجود دارد که به معنی Not True و Not False است. در آینده در مورد این نوع داده ها بیشتر اطلاعات کسب خواهیم کرد.

توابع منطقی در اکسل (Logical Functions) که از ارکان اکسل به شمار میروند و توابع اطلاعات در اکسل (Information Functions) از جمله توابعی هستند که با داده های نوع Boolean سر و کار دارند.

نکاتی در مورد رنگ کردن یک در میان سطر هادر اکسل

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

 

ابتدا فضایی را که می خواهید سطرهایش یکی در میان رنگی شود را انتخاب کنید. ( البته می توانید کل شیت اکسلتان را انتخاب کرده و عملیات را برای کل صفحه انجام دهید)

 

سپس به مسیر Home → Styles → Conditional Formatting → New rule رفته تا پنجره New Formatting Rule برای شما باز شود. در این پنجره بر روی گزینه آخر یعنی Use a formula to determine which cells to format  کلیک کنید.

 

حال می خواهیم از دو فرمول استفاده کنیم.

 

تابع ([Row([reference شماره سطر مرجعی که درون پرانتز به آن معرفی می شود را نشان می دهد. البته مشخص کردن مرجع برای آن اختیاری است و در صورت خالی گذاشتن درون پرانتز (()Row) شماره سطر همان سلولی که درون آن این فرمول تایپ شده است را بر می گرداند.

 

تابع (ISODD(number در اکسل بررسی می کند که عدد درون آن یک عدد فرد است یا نه . اگر فرد باشد مقدار  True و اگر نباشد مقدار False را بر می گرداند.

 

حال اگر از فرمول ترکیبی (()ISODD(Row استفاده کنیم ، بررسی می شود که آیا شماره سطر سلولی که در آن این فرمول تایپ می شود فرد است یا نه ، که اگر فرد باشد True و اگر نباشد پاسخ آن False  خواهد بود.

 

به Conditional Formatting بر می گردیم. همان گونه که در تصویر زیر مشاهده می کنید ، باید فرمول (()ISODD(Row= را در قسمت  :Format values where this formula is true تایپ کنیم. در این قسمت بررسی می شود که آیا پاسخ این فرمول True هست یا نه ، اگر باشد Format مشخص شده به آن اختصاص داده می شود.

 

در قسمت Format به طور مثال در زبانه Fill یک رنگ دلخواه را انتخاب کنید و در نهایت Ok

 

مشاهده می کنید که برای تمام فضای انتخابی شما ( یا کل شیت ) سطرها یکی در میان رنگی شده اند.

 

 

 

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

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

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

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

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

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

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

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

۳- حذف فضای خالی ما بین نوشته ها در excel

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

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

=Trim(a1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

به عنوان مثال، داده های یک ستون از جدول مشخصات کارمندان بصورت تاریخ ثبت شده اند، که نیاز است برای جستجو و گروه بندی از (شماره ماه) تاریخ ثبت شده استفاده کنیم، در صورتی که ستون تاریخ به فرمت و شکل (##/##/####) درج شده است و امکان فیلتر کردن به تفکیک ماه وجود ندارد.

پس لازم است که به روش زیر تاریخ را به ستون های سال / ماه / روز تبدیل کنیم.

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

نکته: از این روش فقط برای داده هایی که یک کاراکتر مشترک و مشخص دارند می توان استفاده کرد (مانند Tab,Comma,Space,Slash,Back Slash,Semicolon)
1- ابتدا ستونی را که می خواهیم تبدیل را انجام دهیم انتخاب می کنیم

 

 

2- به تب Data رفته و گزینه Text to Colunms را کلیک می کنیم طبق تصویر زیر

 

3- در مرحله یک گزینه Delimited را انتخاب کرده و بر روی Next کلیک می کنیم

 

4- در مرحله دوم باید کاراکتر مشترک برای جداسازی داده ها در فیلد Other مشخص کنید کهکاراکتر اسلش (/) کاراکتر جدا کننده محسوب می شود و سپس بر روی Next کلیک کنید

 

 

5- و در مرحله آخر باید نوع داده جداشده در ستون های دیگر را مشخص کنید، که بهترین گزینه برای داده های تاریخ (Text) می باشد، به روش که باید هر ستون را (که با شماره های قرمز رنگ از 1 تا 3 نشان داده شده) ابتدا کلیک کنید و سپس نوع داده را Text انتخاب کنید و بعد از این کار بر روی گزینه Finish کلیک کنید

 

و در انتها خواهید دید که ستون C در تصویر اول به سه ستون تاریخ سال/تاریخ ماه/تاریخ روز تبدیل می شود

 

 

---

نکاتی پیرامون کلید های ترکیبی Ctrl در اکسل

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

CTRL+PgUp , CTRL+PgDn

بین محیط‌های کاری یا همان worksheet ها جابجا می‌شویم.

CTRL+SHIFT+(

تمام ردیف‌های ناحیه‌ی انتخاب که در حالت پنهان یا hide قرار دارند را به حالت عادی باز می‌گرداند.

CTRL+SHIFT+&

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

CTRL+SHIFT+-

ابتدا ناحیه‌ای که در آن توسط شرتکات CTRL+SHIFT+& حاشیه (Border) ایجاد شده باشد را انتخاب می‌نماییم و با زدن CTRL+SHIFT+- بردرهای داخل و روی این ناحیه از بین می‌روند.

CTRL+SHIFT+$

قبل اعداد علامت $ قرار می‌دهد. ضمناً اعداد منفی را داخل پرانتز قرار می‌دهد.

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

CTRL+SHIFT+%

فرمت اعداد ناحیه را با درصد نمایش می‌دهد.

CTRL+SHIFT+^

اعداد را با نماد علمی نمایش می‌دهیم.

در نماد علمی یک عدد را به شکل ضرب دو عدد می‌نویسیم. عدد اول عددی بین ۱ تا ۱۰ است و عدد دومی توانی است برای عدد ۱۰ تا با ضرب شدن در عدد اول، عدد اولیه به دست آید. مثلاً ۱۱ به شکل ۱٫۱E+1 و در اکسل به صورت ۱٫۱E+1 نوشته می‌شود.

CTRL+SHIFT+#

فرمت اعداد به صورت روز، ماه، سال نوشته می‌شود.

CTRL+SHIFT+@

فرمت اعداد ناحیه‌ی انتخابی را به صورت ساعت در می‌آورد. با تایپ ۱۵:۲۰ برایتان ۳:۲۰PM نمایش داده می‌شود.

CTRL+SHIFT+!

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

در گرد کردن اعداد و نمایش تا دو رقم اعشار، اگر رقم سوم از ۴ بیشتر بود رقم دوم یکی اضافه می‌شود. در غیر این صورت رقم دوم تغییری نمی‌کند.

CTRL+SHIFT+*

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

CTRL+SHIFT+:

زمان فعلی سیستم را نمایش می‌دهد.

CTRL+SHIFT+”

مقدار سلول را از سلول بالایی خود کپی می‌کند.

CTRL+SHIFT+Plus (+)

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

CTRL+Minus (-)

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

CTRL+;

تاریخ فعلی را نمایش می‌دهد.

CTRL+1

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

CTRL+2

فرمت ضخیم شدن یا همان Bold را حذف یا اعمال می‌کند.

CTRL+3

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

CTRL+4

فرمت خط کشی زیر متن یا همان Underlining را حذف یا اعمال می‌کند.

CTRL+5

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

CTRL+8

گروه هایی که توسط تب Data و بخش Outline ایجاد شده است را آشکار می‌کند یا بالعکس.

CTRL+9

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

CTRL+0

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