علت خطای جمع اعداد در اکسل

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

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

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

توضیح Circular Reference در Excel

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

Circular Reference در Excel

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

 

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

 

آوردن فلش آبی رنگ در اکسل

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

 

 

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

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

 

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

 

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

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

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

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

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

درج جداکننده هزارگان در اکسل

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

 

روش اول) سریع‌ترین روش برای درج جداکننده هزارگان در اکسل این است که سلول یا سلول‌های مورد نظر را انتخاب کنید و سپس از منوی Home، قسمت Number روی دکمه Comma Style کلیک کنید.

 

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

۱- روی آن راست کلیک نمایید و گزینه Format Cells را برگزینید.

۲- یا از منوی Home، قسمت Number روی دکمه گوشه پائین سمت راست کلیک کنید.

 

۳- یا کلیدهای ترکیبی Ctrl + 1 را فشار دهید (عدد ۱ کیپد).

پس از انجام اینکار کادر Format Cells باز می‌شود.

 

در تب Number از قسمت Category به روش‌های زیر نیز می‌توان می‌توان جداکننده هزارگان را درج کرد:

روش دوم) انتخاب گزینه Number:

• Decimal places: تعداد ارقام بعد از ممیز را مشخص می‌کند.
• Use 1000 separator (,): با انتخاب این گزینه، بعد از هر سه رقم از سمت راست یک «,» درج می‌شود.

 

روش سوم) انتخاب گزینه Accounting (حسابداری):

 پس از کلیک روی دکمه Comma Style، فرمت عدد که در بالای دکمه نمایش داده می‌شود به طور خودکار به Accounting تغییر پیدا کرده است. در واقع یک روش درج جدا کننده هزارگان، انتخاب فرمت Accounting در پنجره Format Cells است، اما به غیر از این فرمت، از فرمت Currency نیز می‌توان استفاده کرد. با انتخاب گزینه Currency، عدد از حالت عدد معمولی به فرمت پول تبدیل می‌شود. که به صورت خودکار علامت جداکننده هزارگان با انتخاب این گزینه درج می‌شود. این حالت شامل موارد زیر می‌شود:

• Decimal places: تعداد ارقام بعد از ممیز را مشخص می‌کند.
• Symbol: جهت انتخاب واحد پول می‌باشد که اگر عدد پول نیست می‌توانید گزینه None‌را انتخاب کنید تا هیچ علامت واحد پولی قبل از عدد قرار نگیرد.

 

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

تغيير شکل کادر توضيحات در اکسل

درنرم افزار اکسل  پس از باز کردن اکسل روي File در گوشه بالا، سمت چپ صفحه کليک و آخرين گزينه يعني Options را انتخاب کنيد تا پنجره Excel Option ظاهر شود. در قسمت سمت چپ اين پنجره، عنوان Customize Ribbon را انتخاب کنيد.

هنگام قراردادن توضيحات در  اکسل (insert comment) هميشه يک مستطيل ساده ظاهر مي شود، اما شما با دنبال کردن ترفندي ساده، مي توانيد ظاهر اين کادر را تغيير دهيد.

براي اين کار پس از باز کردن اکسل روي File در گوشه بالا، سمت چپ صفحه کليک و آخرين گزينه يعني Options را انتخاب کنيد تا پنجره Excel Option ظاهر شود. در قسمت سمت چپ اين پنجره، عنوان Customize Ribbon را انتخاب کنيد.

در کادر سمت چپ تعدادي از دستورات و ابزارهاي اکسل فهرست شده است و در بالاي اين کادر عبارت Popular Commands ديده مي شود. روي مثلث کوچک سمت راست اين کادر کليک و عبارت All Commands را انتخاب کنيد. نوار لغزنده اين کادر را درگ کنيد تا به دستور Change Shape برسيد.

اکنون بايد اين دستور را به کادر سمت راست با استفاده از دکمه Add اضافه کنيد. قبل از اين که بتوانيد هر دستور يا ابزاري را به کادر سمت راست اضافه کنيد، بايد در يکي از زيرگروه هاي منوهاي فعلي (مثل Home-Insert-Design) يک گروه با تب جديد اضافه کنيد.

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

سپس روي دکمه New Group در پايين همين صفحه کليک کنيد تا يک گروه جديد در منوي Home ايجاد شود؛ در ادامه روي دکمه Add که بين 2 کادر قرار گرفته است، کليک کنيد تا گروه ابزار Change Shape به انتهاي منوي Home اضافه شود و سپس دکمه Ok را انتخاب کنيد تا اکسل بسته شود.

اکنون مشاهده مي کنيد که عبارت Change Shape در قسمتي به نام New Group در انتهاي منوي Home قرار گرفته است.

حال براي تغيير شکل و رنگ زمينه توضيحات روي يک سلول دلخواه راست کليک و از کادر ظاهر شده عبارت Insert Comment را انتخاب کنيد. عبارتي به عنوان توضيح را وارد و سپس روي سلول ديگري کليک کنيد. مثلث کوچک قرمز رنگ، نشان مي دهد که سلول داراي توضيحات است. دوباره روي اين سلول راست کليک و اين بار عبارت Edit Comment را انتخاب کنيد.

روي کادر مستطيل توضيحات کليک کنيد و از گروه جديد که در منوي Home به نام Change Shape ايجاد کرديد، کليک کنيد و شکل دلخواه را برگزينيد.

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

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

 

🔵  نوشتن بالانویس و زیرنویس در سلول‌های اکسل:

مثال زیر را در نظر بگیرید که می‌خواهیم درون سلول عبارت H₂O (اندیس ۲) نمایش داده شود. برای اینکار پس از انتخاب عدد ۲، راست کلیک کرده و در منوی باز شده روی گزینه Format Cells کلیک کنید.

 

پنجره Format Cells باز می‌شود. البته برای نمایش این پنجره می‌توانید کلیدهای میانبر Ctrl+1 را نیز فشار دهید. در این پنجره در تب Font، دو گزینه بالانویس و زیرنویس در قسمت Effects وجود دارد:

• Superscript: برای تبدیل عبارت انتخاب شده به حالت بالانویس
• Subscript: برای تبدیل عبارت انتخاب شده به حالت زیرنویس

برای مثال فوق گزینه Subscript را انتخاب و روی دکمه OK کلیک کنید.

 

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

 

---

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

عنوان نمودار و محورها چون قابلیت انتخاب دارند براحتی می‌توانید پس از انتخاب قسمت مورد نظر مشابه بالا آن‌ها را به بالانویس یا زیرنویس تبدیل کرد. در این حالت پس از راست کلیک کردن، روی گزینه Font کلیک کنید،    نجره Format Cells باز می‌شود. البته برای نمایش این پنجره می‌توانید کلیدهای میانبر Ctrl+1 را نیز فشار دهید. در این پنجره در تب Font، دو گزینه بالانویس و زیرنویس در قسمت Effects وجود دارد:

• Superscript: برای تبدیل عبارت انتخاب شده به حالت بالانویس
• Subscript: برای تبدیل عبارت انتخاب شده به حالت زیرنویس

 

گزینه Subscript را انتخاب و روی دکمه OK کلیک کنید.                                                                     

 

 

برای انجام تغییرات فوق در راهنمای نمودار (Legend)، با توجه به اینکه در Legend امکان انتخاب متن وجود ندارد اگر روی Legend راست کلیک کنید گزینه Font قابل مشاهده است. با انتخاب این گزینه، در پنجره فونت اگر گزینه Superscript را انتخاب کنید کل عبارت، به حالت بالانویس تبدیل می‌شود که این مدنظر ما نیست.

 

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

 

 

 

اما چاره چیست؟ برای این حالت باید بجای بالانویس و زیرنویس‌های مدنظرتان از سمبل‌های موجود در قسمت Symbol در اکسل استفاده کرد.

 

 

 

تمام اعداد ۰ تا ۹ به صورت بالانویس و زیرنویس در قسمت Symbol موجود است. با انتخاب عدد ۲ از بخش Symbol، در قسمت نوار فرمول نیز به صورت بالانویس مشاهده می‌شود که همین موجب می‌شود در نمودار نیز اینگونه باشد.

 

 

 

 

 

 

 

 

افزونه Power Query معجزه دیگری در اکسل

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

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

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

 

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

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

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

 

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

این آپشن جدید تبدیل داده ها ، امکان محاسبه مجموع سالها را طریق ستونی از نوع ” مدت زمان” فراهم میکند. (اکسل مجموع تعداد روزها را بر ۳۶۵ تقسیم خواهد کرد ) برای دسترسی به این امکان کاربر باید از طریق سربرگ Transform یا Add columns  گزینه ی Total years را انتخاب کند.

 

نگه داشتن تکراری ها

در مسیر Home > Remove Duplicates  گزینه ی جدیدی تحت عنوان Keep duplicates در زیر گزینه ی Remove duplicates اضافه شده است . گزینه ی جدید این امکان را به کاربر میدهد که تنها ردیف هایی را که در ستون حاضر دارای مقادیر مشترک و تکراری هستند نگه داشته و سایر ردیف های را حذف کند.

 

افزودن راهنما برای sample input values در پنجره ی Change Type with Locale در اکسل 

این قابلیت تبدیل جدید ؛ به کاربر این امکان را میدهد تا فیلد های موجود در یک ستون را بر حسب تنظیمات محلی تغییر دهد .

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

(Data >New Query > Query Options > Current Workbook > Data Load)

با کلیک راست بالای ستون در Query Editor preview و انتخاب Change Type > Using Locale  پنجره ی Change Type with Locale به نمایش در خواهد آمد که این امکان را به کاربران می دهد که در زمان انتخاب نوع خاصی از داده یا محلی سازی ؛ به تعدادی مقادیر نمونه دسترسی داشته باشند.

 

پشتیبانی از فضای سفید در Query Editor preview

Query Editor preview به کاربران اجازه خواهد داد تا نمایش هر نوع کاراکتر های سفید را در سلول ها مشاهده کنند

هرچند برخورد پیشفرض برنامه با این کاراکتر ها بر روی show whitespace تنظیم شده ، اما کاربران همچنان این امکان را خواهند داشت از سربرگ View در نوار Query Editor این تنظیم را تغییر دهند

 

قابلیت غیرفعال سازی پیشنمایش در پنجره ی Navigator

در منوی Display options کاربر این امکان را خواهد داشت تا پیشنمایش را در پنجره ی Navigator غیرفعال کند

 

پشتیبانی از Technical name در پنجره ی Navigator

این قابلیت جدید به کاربر اجازه می دهد تا نحوه ی آدرس دهی را از physical name به technical name تغییر دهد .

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

 

تغییر نام کوئری ها از کوئری موجود در Query Editor

درون Query Editor کاربر میتواند به سرعت با کلیک راست بر روی کوئری ها یا دابل کلید و انتخاب آنها و سپس فشردن کلید F2  کوئری ها را تغییر نام دهد.

 

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

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

 

امکان غیرفعال سازی تنظیمات حریم خصوصی موجود در سطح ماشین (شامل کلید های رجیستری)

از آنجا که Power Query به کاربران اجازه میدهد داده ها را از منابع متعدد در یک ورک بوک ادغام کنند تنظیمی تحت عنوان Privacy Levels وجود دارد که کاربران میتوانند برای هر منبع که برای ادغام اطلاعات به آن وصل میشوند یکی از سطوح عمومی ، سازمانی و یا خصوصی را تعیین کنند .

از آنجا که سطوح متفاوت privacy کاربران را از انواعی از ترکیبات و ادغام داده های که ناقض این تنظیمات است باز میدارد ، تیم آفیس به کابران اجازه میدهند تا یکی از سه حالت زیر را از طریق پنجره ی Query Options انتخاب کنند.

• ترکیب داده های بر پایه ی سطوح Privacy برای هر منبع – این امکان جدید به کاربر اجازه می دهد تا سطوح Privacy موجود را بر روی تمام فایل های .XLSX موجود اعمال کند.
• ترکیب داده ها بر اساس سطوح Privacy هر فایل – عملکرد پیشفرض که با عملکرد موجود در نسخه های قبلی یکیست این گونه است.

 

 

• نادیده انگاشتن سطوح Privacy – این قابلیت جدید به کاربر اجازه می دهد تا همیشه بدون توجه به تنظیمات ورک بوک حاضر ، از محدودیت Privacy برای هر کدام از فایلهای .XLSX عبور کنند.

 

 

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

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

ترکیب کردن رشته های متنی یکی از رایجترین کارها برای کاربران اکسل است اما تا به امروز اگر شما می خواستید رشته های متنی موجود در یک محدوده ای از سلول ها را ترکیب کنید مجبور بودید تک تک سلول ها را به تنهایی انتخاب کنید. توابع جدید CONCAT و TEXTJOIN در اکسل  به شما اجازه می دهند رشته های متنی موجود در محدوده ای از سلول ها (چه با استفاده از جداگرها همانند کاما و چه بدون استفاده از آنها) را با یکدیگر ترکیب کنید. شما می توانید به سادگی اشاره کنید به محدوده تان و جداگرتان را یکبار انتخاب کرده و به اکسل اجازه دهید تا همه­ ی این بار سنگین را بلند کند.

با استفاده از جداگرها

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

  بدون استفاده از جداگرها

CONCAT(text1, [text2],…)                                                                                                                                                                

• IFS
• SWITCH

 توابع IFS و SWITCH   جایگزینی برای فرمول نویس های تو در تو یا همان IF  تو IF  هستند. تابع IF  یکی از رایجترین توابع در اکسل است و استفاده ی تو در تو از این تابع یکی از کارهای متداول کاربران است که در برخی موارد بسیار خسته کننده و گیج کننده است.

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

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

 

 

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

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

 

• MAXIFS
• MINIFS

اگر شما با COUNTIFS, SUMIFS و AVERAGEIFS آشنا هستید پس MAXIFS و MINIFS نیاز به هیچ توضیحی ندارند. توابع MAX و MIN ، ماکزیمم و مینیمم مقادیر را درون یک محدوده ای از سلول ها مشخص می کنند ولی اگر شما نیاز داشته باشید که این محاسبات در یک شرایط خاص انجام بشود باید چه کنید؟ این دقیقا همان کاری است که MAXIFS و MINIFS انجام می دهند. شما می توانید شرایطتان را قبل از این که محاسبات انجام شود مشخص کنید.

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

 

 

چگونه ازAutoSum در اکسل استفاده كنیم

تابع، یك فرمول از پیش نوشته شده در برنامه است. آرگومان های (متغییرهای) مورد نیاز به تابع داده می شود و تابع، نتیجه را محاسبه می كند. برای تابعSUM ، تنها چیزی كه باید مشخص كنید محدوده ای است كه می خواهید جمع كنید نوار ابزارStandard ، امكان دستیابی سریع به SUM و نیز توابع پر استفاده دیگر مانند AVERAGE (محاسبه معدل)، MIN (كوچكترین عدد)، MAX (بزرگترین عدد) وCount (محاسبه تعداد آیتم ها) را فراهم می كند. در آموزش اکسل پیشرفته امروز   یاد می‌گیریم كه چگونه از توابع استفاده كنید.

 

 یك محل برای نتیجه، انتخاب كنید  

 

سلولی كه می خواهید نتیجه تابع در آن ظاهر شود را انتخاب كنید (معمولاً در پایان یك سطر یا ستون از اعداد).

 

 روی AutoSum كلیك كنید  

 

در نوار ابزار Standard ، روی دكمه AutoSum كلیك كنید.

 

 فرمول SUM درج می شود  

 

دكمه AutoSum، تابع SUM را درج می كند و یك خط چشمك زن، پیرامون سلول هایی كه با هم جمع می شوند قرار می دهد. یك Screen Tip زرد رنگ، آرگومان های مورد استفاده در تابع SUM را لیست می كند.

 

 درج داده ها را تكمیل كنید  

 

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

 

 با استفاده از AutoSum ، معدل حساب كنید  

 

از طریق دكمه AutoSum برای درج سریع توابع استفاده كنید. می توانید از یك منو از توابع موجود برای استفاده از تابعی كه میانگین را محاسبه می كند، سلولی كه می خواهید نتیجه در آن نشان داده شود را انتخاب كنید و بعد روی فلش دكمه AutoSum كلیك كنید و از لیست گزینه ها، Average را انتخاب كنید.

 

 ورود اطلاعات را كامل كنید  

 

یك نوار چشمك زن، دور محدوده ای كه Excel فكر می كند می خواهید در این تابع استفاده كنید را فرا می گیرد. اگر لازم باشد با كشیدن ماوس، محدوده دیگری را انتخاب كنید وبعدEnter را بزنید. نتیجه در سلول مورد نظر نشان داده می شود و فرمولی كه برای محاسبه این نتیجه بكار رفت در نوار فرمول ظاهر می شود.

*توجه  *

 جمع كردن كل سلول های یك جدول در اکسل

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

 

جمع كردن بیش از یك محدوده در excel

 

برای جمع كردن مقادیر بیش از یك محدوده، روی دكمه AutoSum كلیك كنید، محدوده اول را انتخاب كنید، كلید كاما (,) را بفشارید و بعد محدوده بعدی را انتخاب كنید. بین تمام محدوده های بعدی هم باید كاما تایپ كنید.

 

چگونه در اکسل میتوان فرمت اعداد را تغییر دهیم

 

در نرم افزار اکسل  مقادیر عددی كه در برگه های كاری خود وارد می كنید معمولاً فقط عدد نمی باشند بلكه نشان‌دهنده مقدار دلار، تاریخ، درصد و مقادیر واقعی دیگر می باشند. بصورت پیش فرض، وقتی كه عددی را وارد می كنید، به فرمت General نمایش داده می شود كه معمولاً به همان صورتی است كه آن را تایپ می كنید. ولی بجای تایپ $12.95 ، می توانید تایپ كنید 12.95 و بعد یك فرمت عددی مانند فرمت Currency را روی آن اعمال كنید. برای مثال، 5.05 می‌تواند معانی گوناگون داشته باشد ولی اگر فرمت Currency آن را اعمال كنید به صورت $5.05 در‌می‌آید و اگر فرمت Percent age را روی آن اعمال كنید بصورت 505% در می آید. در این كار عملی، یاد می گیرید كه چگونه با استفاده از دكمه های نوار ابزار و كادر محاوره ای Format Cells ، فرمت اعداد را تغییر دهید.

 

 سلول یا محدوده مورد نظر را انتخاب كنید  

 

 سلول یا محدوده ای كه می خواهید فرمت داده های عددی آنها را تغییر دهید را انتخاب كنید.

 

 رویCurrency كلیك كنید  

 

 برای تغییر فرمت به فرمت پولی (كه $ را اضافه می كند، عدد را تا دو رقم اعشار گرد می كند و$ را طوری قرار می دهد كه تمام علامت‌های دلار در ستون تراز شوند) ، روی دكمه Currency Style در نوار ابزارFormatting كلیك كنید.

  

 روی Percent كلیك كنید  

 

برای تغییر فرمت به فرمت درصد (كه علامت % را اضافه می كند و عدد را ازحالت كسری یا عدد صحیح به مقدار درصد تبدیل می كند)، روی دكمه Percent Style در نوار ابزارFormatting كلیك كنید.(می دانید كه درصد یعنی صدم. بنابراین 0.12 بصورت 12% در می آید ولی 12 بصورت 1200% در می آید).

 

 رویComma كلیك كنید  

 

 برای بكار بردن فرمت كاما (كه در ایالات متحده به این معنی است كه عدد تا دو رقم اعشار گرد شود و بصورت سه رقم سه رقم با كاما جدا شود)، روی دكمه Comma Style در نوار ابزار Formatting كلیك كنید. برای كنترل نقطه اعشاری همچنین می‌توانید روی دكمه های Increase Decimal و Decrease Decimalدر نوار ابزار Formatting كلیك كنید تا نقطه اعشاری جابجا شود.

 

 از كادر محاوره ای Format Cells استفاده كنید  

 

 برای بكار بردن فرمت هایی كه در نوار ابزار Formatting موجود نباشند، از منوی Format ، گزینه Cells را انتخاب كنید تا كادر محاوره ای Format Cells باز شود. روی برگه Number كلیك كنید و از لیست Category ، یكی از فرمت ها را انتخاب كنید. مقدار سلول انتخاب شده با آن فرمت در قسمت Sample نشان داده می شود.

 گزینه های مربوطه به Format را تنظیم كنید  

 

فرمت های مختلف در كادر محاوره ای Format Cells ، گزینه متفاوتی را ارائه می كنند. یكی از گروه ها (Category) را انتخاب كنید و گزینه های مربوط به آن فرمت را تنظیم كنید. شرح گروه انتخاب شده در پایین كادر محاوره ای ظاهر می شود. برای بستن كادر محاوره ای و اعمال كردن فرمت جدید روی Ok كلیك كنید.

* توجه *

كپی كردن فرمت

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

Carrency واقعاً Carrency نیست

اگر چه یكی از دكمه های نوار ابزار، Carrency Style نامیده می شود فرمت Carrency را اعمال نمی كند، بلكه فرمتAccounting را اعمال می كند. برای اعمال كردن فرمت Carrency واقعی (كه در آن، علامت بی‌توجه به اینكه ستون چه پهنایی دارد، بلافاصله در سمت چپ عدد قرار می گیرد) از كادر محاوره‌ای Format Cells استفاده كنید.

حذف فرمت دهی در اکسل

برای حذف تمام فرمت دهی ها (از جمله فرمت عددی)، بدون حذف داده ها از یك سلول، آن سلول یا محدوده را انتخاب كنید و بعد از منویEdit ، گزینه Clear و بعد گزینه Formats را انتخاب كنید.

                                                                                                                    

توابع جستجو و جایگزینی در اکسل

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

• توابع جستجودر excel
  

۱-  تابع Find:

با استفاده از این تابع می‌توان در متن مورد جستجو موقعیت آغاز یک کاراکتر (حرف، عدد و کلمه) را پیدا کرد. لازم به ذکر است که فاصله (space) نیز یک کاراکتر محسوب می‌شود.  ساختار (Syntax) این تابع به شکل زیر است:

=FIND(find_text , within_text , start_num)

این تابع دارای ۳ آرگومان (پارامتر یا شیء) است:

1-   find_text (آرگومان ۱): عبارتی که بایستی جستجو شود که می‌تواند یک حرف یا یک کلمه یا متن باشد.

2-  within_text (آرگومان ۲): متن مرجع که در آن به جستجو خواهیم پرداخت. این متن نیز می‌تواند یک حرف یا یک کلمه یا متن طولانی باشد.

-   Start_num (آرگومان ۳): نقطه آغاز جستجو است. در واقع مشخص می‌کنیم که در آرگومان ۲ از چندمین کاراکتر شروع به جستجو کند که در این صورت کاراکترهای قبلی جستجو نخواهند شد. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان ۲ می‌تواند باشد.

توجه: درصورتیکه عدد صفر یا عددی بیش از تعداد کاراکتر موجود در آرگومان ۲ به آن اختصاص یابد خطای #VALUE! را نشان خواهد داد.

توجه: اگر آرگومان ۱ در آرگومان ۲ موجود نباشد خطای #VALUE! را خواهیم دید.

 

 

۲- تابع  :Search

تمامی آرگومان‌ها و همچنین عملکرد تابع  Searchبا تابع Find یکسان است و تنها تفاوتشان این است که تابع Find به بزرگی یا کوچکی حرف حساس است ولی تابع Search اینگونه نیست. ساختار (Syntax) این تابع به شکل زیر است:

=Search(find_text , within_text , start_num)

 

 

---

• توابع جایگزینی در اکسل :

۱- تابع REPLACE:

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

=REPLACE(old_text,start_num,num_chars,new_text)

این تابع دارای ۴ آرگومان است:

1-   old_text (آرگومان ۱): متن قدیمی که قصد دارید عملیات جایگزینی را در آن انجام دهید.

 -   Start_num (آرگومان ۲): نقطه آغاز جایگزینی در متن قدیمی. در واقع مشخص می‌کنیم که در آرگومان ۱ از چندمین کاراکتر جایگزینی شروع شود. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان ۱ می‌تواند باشد.

 2-  num_chars (آرگومان ۳): تعداد کاراکتری که در متن قدیمی باید با متن جدید جایگزین شوند.

      new_text (آرگومان ۴): متن جدید که باید در متن قدیم جایگزین شود.

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

=REPLACE(“09122048458″,1,1, “+98″)

فرمول بالا عدد صفر ابتدای شماره تماس را با عبارت ۹۸+ جایگزین می‌کند.
 

۲- تابع SUBSTITUTE:

تابع SUBSTITUTE همانند تابع REPLACE برای جایگزین کردن بخش از یک رشته‌ی متنی با رشته‌ی متنی جدید مورد استفاده قرار می‌گیرد با این تفاوت که در این تابع دقیقاً مشخص می‌کنیم چه بخشی (دقیقاً کدام کاراکترها) از یک رشته‌ی متنی با رشته‌ی متنی جدید باید جایگزین شود، ساختار این تابع مانند زیر است:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

این تابع دارای ۴ آرگومان است:

1-   text (آرگومان ۱): متن مرجع که قصد دارید عملیات جایگزینی را در آن انجام دهید.

1-   old_text (آرگومان ۲): کاراکترهایی از رشته‌ی متنی مرجع می‌باشد که قرار است با کاراکترهای جدید جایگزین شوند.

      new_text (آرگومان ۳): متن جدید که باید در متن قدیم جایگزین شود.

      [instance_num] (آرگومان ۴): این آرگومان اختیاری است و زمانی استفاده می‌شود که آرگومان ۲ چند مرتبه در متن مرجع تکرار شده باشد و بخواهیم تنها یک مورد از آن‌ها جایگزین شود. در این حالت با استفاده از آرگومان ۴ مکان آن را مشخص می‌کنیم.

حال می‌خواهیم مانند مثال تابع REPLACE عدد صفر را با ۹۸+ جایگزین کنیم:

=SUBSTITUTE(“09122048458″,”0″, “+98″)

خروجی این فرمول عبارت “۹۸۹۱۲۲+۹۸۴۸۴۵۸+” خواهد بود، در واقع تمام صفرهای پیدا شده با عبارت “۹۸+” جایگزین می‌شوند. در این حالت باید از آرگومان ۴ استفاده کرد.اگر این آرگومان خالی باشد (مانند مثال بالا) به این معنی است که تمام صفرها باید جایگزین شوند و اگر به جای این آرگومان عدد ۱ نوشته شود، تنها صفر اول (اولین صفر پیدا شده در رشته) با عبارت “۹۸+” جایگزین می‌شود، مانند زیر:

=SUBSTITUTE(“09122048458″,”0″, “+98″, 1)

 

اضافه کردن یا حذف کردن سطرها و ستون‌ها و وارونه کردن جای سطرها و ستون ها در اکسل

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

 

 نوار ابزار Tables را باز كنید 

صفحه حاوی جدولی كه می‌خواهید تغییر دهید را باز كنید. از منوی View ، گزینه Toolbars و بعد گزینه Tables را انتخاب كنید تا نوار ابزار Tables باز شود.

  

 درج سطردر اکسل  

نقطه درج را در سلولی قرار دهید كه می‌خواهید سطر جدید را زیر آن درج كنید. (سطر جدید، بالای نقطه درج، درج می‌شود). روی دكمه Insert Rows در نوار ابزار Tables كلیك كنید. برای درج كردن چند سطر، دوباره روی این دكمه كلیك كنید. همچنین می‌توانید روی سلول انتخاب شده كلیك راست كنید و گزینه Insert Rows را انتخاب كنید.  

 درج ستون  

نقطه درج را در سلولی قرار دهید كه می‌خواهید ستون جدید را در سمت چپ آن قرار دهید (ستون جدید در سمت چپ نقطه درج، قرار می‌گیرد). روی دكمه Insert Columns در نوار ابزار Tables كلیك كنید. برای درج ستون‌های بیشتر، چند بار روی این دكمه كلیك كنید.

 سلول‌هایی را برای حذف كردن انتخا ب كنید

 

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

   

  سلول‌ها را حذف كنید  

 

روی دكمه Delete Cells در نوار ابزار Tables كلیك كنید یا روی سلول‌های انتخاب شده كلیك راست كنید و گزینه Delete Cells را انتخاب كنید تا سلول‌های انتخاب شده، حذف شوند.

   

*توجه *

حذف و درج سریع‌تردر اکسل

وقتی كه نوار ابزار Tables، در دسترس نباشد، با انتخاب گزینه Rows یا Columns از گزینه Insert در منوی Table می توانید سطرهایی را درج كنید. همچنین با انتخاب گزینه Delete Cells از منوی Table می توانید سلول‌ها را حذف كنید.

 

عنوان

برای اضافه كردن یك عنوان به جدول، نقطه درج را در جدول قرار دهید و از منوی Table، گزینه Insert و بعد گزینه Caption را انتخاب كنید. بصورت پیش فرض، عنوان، بالای جدول قرار می‌گیرد. برای منتقل كردن عنوان به پایین جدول، روی عنوان كلیك راست كنید و گزینه Caption Properties را انتخاب كنید. بعد گزینه Bottom Of Table را انتخاب كنید و روی OK كلیك كنید.

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

 

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

 

تمام اطلاعاتی که قصد وارونه کردن آن را دارید، انتخاب نمایید

                                                                                                                                       بر روی علامت کپی کلیک کرده و یا از میانبر Ctrl + C استفاده کنید

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

 

بر روی علامت پیکان در زیر دکمه ی Paste کلیک کرده و در منویی که باز می شود، گزینه ی Paste Special را انتخاب نمایید.

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

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

 

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

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

- جدول مورد نظر رو انتخاب کنید
۲- روی منوی Home کلیک کنید
۳- در قسمت Font ابزار Border رو پیدا کنید
۴- روی فلش کنار ابزار کلیک کنید
۵- روی فلش کنار گزینه‌ی Line Style کلیک کنید
۶- خط نقطه چین مورد نظر رو انتخاب کنید
۷- دوباره روی فلش کنار ابزار کلیک کنید
۸- گزینه‌ای که همه‌ی خطوط جدول رو مشخص می‌کنه (All Borders) رو انتخاب کنید
۹- دوباره روی فلش کنار ابزار کلیک کنید
۱۰- روی فلش کنار گزینه‌ی Line Style کلیک کنید
۱۱- خط دوتایی رو انتخاب کنین
۱۲- دوباره روی فلش کنار ابزار کلیک کنید
۱۳- گزینه‌ای که خط دور جدول رو بدن خطوط داخلی رو مشخص مي‌کنه (Outside Borders) رو انتخاب کنید

روش دوم

۱- جدول مورد نظر رو انتخاب کنید
۲- روی منوی Home کلیک کنید
۳- در قسمت Font ابزار Border رو پیدا کنید
۴- روی فلش کنار ابزار کلیک کنید
۵- گزینه‌ی More Borders رو انتخاب  کنید
۶- در قسمت Line و Style خطوط دوتایی راانتخاب کنید
۷- از قسمت Presets گزینه‌ی Outline را انتخاب کنید
۸- حالا از قسمت Line و Style خطوط نقطه چین رو انتخاب کنید
۹- از قسمت Presets گزینه‌ی Inside رو انتخاب کنید
۱۰- OK کنید                                                                                                                    رسم خط دور یک جدول به صورت نقطه‌چین در اکسل                                                                 جدول مورد نظر رو انتخاب کنین (سلول‌های جدول)
۲- روی منوی Home کلیک کنید
۳- از قسمت Font ابزار Border رو انتخاب کنید
۴- روی فلش کنار ابزار کلیک کنید
۵- گزینه‌ی Line Style رو پیدا کنید
۶- روی فلش سمت راست این گزینه کلیک کنید
۷- خط چین مورد نظر رو انتخاب کنین
۸- دوباره روی فلش کنار ابزار کلیک کنید
۹- گزینه‌ی مورد نظر رو از بین کادرهای موجود انتخاب کنید   

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

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

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

 3. در منوی Data  زیر منوی  Pivot table and Pivot chart report… را کلیک نمایید

 4. در پنجره ظاهر شده کارهای درخواست شده را انجام دهید.

5. متغیرهای مورد نظر خود را در ستون و ردیف معرفی شده قرار دهید.

 

6. متغیری که قرار است شمارش شده و در جدول ثبت شود را به قسمت وسط جدول انتقال دهید. عمل انتقال متغیرها با Click and                                                                                                                                                                                                                                    محاسبه ی میانگین وانحراف معیار در اکسل                                                                                                                                                           1- ابتدا اعداد مورد نظر خود را در یک ستون در نرم افزار اکسل وارد نمایید.

2. اعداد نوشته شده را انتخاب نمایید.

3. Insert Function را در منوی Formulas کلیک نمایید.

4. Average و یا STDEV را انتخاب نمایید.

5. در خانه ای که می خواهید نتیجه محاسبه در آن قرار بگیرد ، کلیک نمایید.

6. در پنجره ظاهر شده محدود اعداد دلخواه خود را انتخاب نمایید.

7. میانگین و یا انحراف معیار در جای مورد نظر ثبت می شود.

ترکیب محتویات تعداد زیادی سلول در یک سلول در اکسل

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

برای حل این مشکل ۳ روش وجود دارد:

روش ۱) با استفاده از اکسل:

فرض کنید لیستی مشابه لیست زیر (A1 تا A11) دارید و قصد دارید متن تمام سلول‌ها را با هم ترکیب کنید.

 

قبل از حل مشکل فوق ابتدا باید با تابع Transpose آشنا شویم.

تابع Transpose:
کلمه Transpose به معنای ترانهادن است و در اکسل با استفاده از تابع Transpose می‌توان مکان ردیف‌ها و ستون‌ها را تغییر داد. همانطور که در تصویر زیر مشاهده می‌کنید جدول زیر دارای ۷ ردیف و ۵ ستون می‌باشد.

 

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

=TRANSPOSE(A1:E7)

برای اینکه عمل ترانهادن برای همه ۳۵ سلول جدول فوق انجام شود باید از فرمول‌نویسی آرایه‌ای استفاده کرد که آموزش داده شده است. بنابراین برای اینکه فرمول فوق بصورت آرایه‌ای عمل کند بعد از وارد کردن فرمول بالا، کلیدهای Ctrl+Shift+Enter را همزمان فشار دهید تا نتیجه زیر حاصل شود.

 

پس از آشنایی با تابع Transpose، برای ترکیب محتویات تعداد زیادی سلول در یک سلول، مراحل زیر را طی کنید:

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

=CONCATENATE(TRANSPOSE(A1:A11))

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

=CONCATENATE(TRANSPOSE(A1:A11&" "))

یا

=CONCATENATE(TRANSPOSE(A1:A11&&","))

۲- پس از تایپ فرمول، کلید اینتر را فشار ندهید. قسمت (" "&TRANSPOSE(A1:A11 را انتخاب کنید و کلید F9 را فشار دهید. اگر به هر دلیلی نمی‌توانید از F9 استفاده کنید کلیدهای Ctrl و = را همزمان فشار دهید.

نکته: برای نمایش مقدار واقعی یک فرمول، کافیست فرمول مورد نظر را انتخاب کرده و کلید F9 را فشار دهید. در صورتیکه کلید Esc را فشار دهید مجدداً فرمول مربوط به آن سلول نشان داده می‌شود.

۳- مشاهده می‌کنید که متن تمام سلول‌های A1 تا A11 ظاهر شده است درحالیکه بین‌ آن‌ها ویرگول و اطرافشان کروشه {} قرار دارد. کروشه‌های دو طرف را پاک کنید.

۴- حالا کلید اینتر را فشار دهید و نتیجه را ببینید. تمام.

 

نکته: اگر سلول‌هایی که قصد دارید با هم ترکیب کنید بصورت ردیفی بودند مثلا A1 تا K1 دیگر نیازی نیست از دستور TRANSPOSE استفاده کنید. فقط پس از فشردن F9 کروشه‌ها را پاک کنید.

منبع: (2, 1) chandoo.org 

---

روش ۲) با استفاده از Notepad و Word:

برای ترکیب سلول‌ها در خارج از اکسل، باید از Word استفاده کرد. اما ابتدا باید کلمات را از جدول خارج کنیم. می‌توان جدول را مستقیما در ورد از بین برد یا اینکه ابتدا در Notepad اینکار را انجام داد و سپس به ورد منتقل کرد. کلمات را اگر از لیست اشاره شده در روش ۱ را کپی کنید و سپس در Notepad پیست کنید جدول از بین می‌رود و تبدیل به متن می‌شود بصورت زیر:

 

سپس می‌توان آن‌ها را به ورد انتقال داد تا سایر عملیات روی‌ آن‌ها انجام شود. البته بدون نیاز به Notepad هم می‌توان جدول را در ورد از بین برد. اگر بعد از کپی کلمات در اکسل، آن‌ها را بصورت معمولی (Ctrl+V) در ورد پیست کنید کلمات همراه با جدول در ورد پیست می‌شوند و نمی‌توان آن‌ها را با هم ترکیب کرد. برای از بین بردن جدول کافیست زمانیکه می‌خواهید لیست را در ورد پیست کنید در هنگام پیست گزینه Keep text only را انتخاب کنید. در اینصورت کلمات بصورت text و بدون جدول در ورد کپی می‌شوند.

 

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

---

روش ۳) با استفاده از ++Notepad

 

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

ما ميتوانيم ليست موجود را بر اساس هر يك از ستونهاي آن به صورت صعودي يا نزولي مرتب كنيم. براي اين كار كافي است به يكي از دو روش زير عمل كنيم :
روش اول :
1- يك سلول از ستوني را كه ميخواهيم ليست بر اساس آن مرتب شود انتخاب ميكنيم.
2- براي مرتب سازي صعودي از آيكون Sort Ascending  و براي مرتب سازي نزولي از آيكون Sort Descending در نوار ابزار Standard استفاده ميكنيم.
نکته
بايد توجه داشته باشيم كه در اين حالت نبايد يك ستون انتخاب شود بلكه فقط يك سلول از ستوني كه ميخواهيم بر اساس آن ليست مرتب شود را انتخاب ميكنيم.
روش دوم :
1- كل جدول يا يك سلول از جدول را انتخاب ميكنيم.
2- منوي Data را انتخاب ميكنيم.
3- گزينه Sort را انتخاب ميكنيم.
4- پنجره اي باز ميشود كه بايد قسمتهاي زير را با توجه به نياز در آن پر كنيم :
الف- Sort By : در اين كادر ستوني را كه ميخواهيم ليست بر اساس آن مرتب شود ، انتخاب ميكنيم.
ب- Then By : در اين كادر ستوني را تعيين ميكنيم كه اگر اطلاعات ستوني که در کادر اول مشخص شده براي مرتب سازي درexcel  مانند هم بود ليست بر اساس اين ستون مرتب شود.
ج- 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- براي مرتب سازي صعودي از آيكون 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- Enter : داده پذیرفته شده و سلول جاری به طور پیش فرض سلول زیرین می شود .

2- Tab: داده پذیرفته شده و سلول جاری سلول بعدی می شود.

نكته :

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

Arrow key : داده پذیرفته شده و سلول با توجه به جهت دكمه فشرده شده یكی از سلولهای اطراف می شود .

داده ها در Excel

داده ها در محیطExcel می توانند اعداد، حروف، تاریخ، زمان و یادداشت باشند كه در زیر به شرح تك تك آنها می پردازیم:

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

انواع داده ها :

1- اطلاعات عددی در EXCEL

كاركردن با اعداد :

Excel تمام اعداد را یكسان فرض می كند. بنابراین دقت زیادی در نمایش اعداد به صورت مبلغ ، تاریخ و كمیت یا هر شكل دیگری از اعداد ندارد. در Excel اعداد را به دو روش می توان وارد نمود:

1- با استفاده از كلیدهای عددی موجود در بالای حروف

2- با استفاده از كلیدهای موجود در سمت راست صفحه كلید ( در صورتی كه اعداد این قسمت كار می كنند كه كلید Num Lock روشن باشد.)

 

علاوه بر ارقام 0 تا 9 نمادهای خاص + و – و ، و . و $ و % و E و e را وارد نمود. كه در آینده با مفهوم هر یك از این علائم بیشتر آشنا شویم.

عكس العمل Excel نسبت به عدد تایپ شده :

1- اگر طول عدد از سلول كوچكتر باشد تغییری در سلول داده نمی شود.

2- اگر طول عدد به اندازه چند كاراكتر بزرگتر از سلول باشد اندازه سلول بزرگ شده تا عدد در آن بگنجد.

3- اگر طول اعداد برای خانه مورد نظر بزرگ باشد ظاهراً عدد سلولهای بعدی را اشغال می كند ولی بعد از Enter كردن یكی از اتفاقات زیر روی می دهد :

الف – اعداد در نماد علمی نمایش داده می شوند.

ب- عدد گرد می شود .

ج- سلول با علامت # پر می شود. این حالت زمانی اتفاق می افتد كه فرمت سلول General نباشد.

تبدیل داده عددی به متنی :

اگر اعدادی كه رقم اول آنها صفر است مثل (01234) تایپ كنیم Excel ، صفر را حذف می كند زیرا صفر قبل از عدد معنی ندارد و یا اگر عدد مثبت را به همراه آن تایپ كنیم (مثل 23 +) ، Excel ، + آن را حذف می كند. همچنین اگر 2/6 را تایپ كنیم سلول 6 مارس را نمایش نمی دهد یعنی آن را تبدیل به فرمت تاریخ می كند برای رفع این مشكلات باید عدد را تبدیل به متن كنیم برای این كار می توانیم یكی از علائم زیر را قبل از عدد بنویسیم :

‘ (آپستروف) : این علامت باعث می شود عدد به همان صورت و با تراز چپ نوشته شود.

” : این علامت باعث می شود عدد به همان صورت و با تراز راست نوشته شود .

^ : این علامت باعث می شود عدد به همان صورت و با تراز وسط نوشته شود.

= : این علامت باعث می شود سلول با عدد تایپ شده پر شود.

1- داده های متنی : می توانیم هر نوع داده متنی را در سلولهای Excel وارد كنیم.

2- داده های نوع تاریخ : می توانیم بدون تنظیم خاصی داده های نوع تاریخ در سلولهای Excel وارد كنیم ، كافی است در سلول تاریخی با فرمت yy/mm/dd یا yy-mm-dd وارد كنیم ، در آینده با قالب بندی های تاریخ آشنا می شویم . با انجام این كار (-) تبدیل به (/) شده و سال به صورت چهار رقمی (19yy) نمایش داده می شود .

3- داده های نوع زمانی : می توانیم بدون تنظیم خاصی داده های نوع زمان در سلولهای Excel وارد كنیم ، كافی است در سلول تاریخی با فرمت hh:mm:ss وارد كنیم ، در آینده با قالب بندی های زمان آشنا می شویم.

2- اطلاعات متنی :

میتوانیم در سلولهای Excel هر متن دلخواهی را چه به فارسی و چه انگلیسی تایپ كنیم.

توانایی Excel برای در نظر گرفتن اعداد مثل متن :

اگر عددی مثل 2/6 را وارد کنیم ، پس از Enter کردن ، Excel، 6 مارس را نمایش میدهد. یعنی این اعداد را به تاریخ در نظر میگیرد. برای اینکه این اعداد با همین قالب نوشته شد قبل از آن علامت (‘) آپستروف قرار میدهیم. یعنی بنویسیم ( ‘6/2 )

3- اطلاعات از نوع تاریخ :

اگر در سلول Excel یك تاریخ به فرم dd-mm-yy یا dd/mm/yy وارد كنیم به طور اتوماتیك قالب سلول تبدیل به قالب تاریخ شده و خط فاصله ها تبدیل به ( / ) شده و سال در چهار رقم نمایش داده میشود.

4- اطلاعات از نوع زمان :

میتوانیم در سلول Excel یك زمان را با فرمت H:M:S وارد كنیم كه قالب سلول به طور اتوماتیك زمان میشود.

5- اطلاعات از نوع یادداشت :

این نوع اطلاعات توضیحات یا یادداشتهایی هستند كه بر روی سلول ظاهر شده و در مورد آن توضیح میدهند. در مباحث بعدی با نحوه ایجاد توضیحات آشنا میشویم.

توجه:

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

فرمول نویسی در Excel

عملگرها در Excel :
1- عملگرهای محاسباتی : از عملگرهای ریاضی + (برای جمع) ، – (برای تفریق) ، * (برای ضرب) و / (برای تقسیم)، ^(برای توان) و % (برای درصد) در Excel استفاده كنیم.

ایجاد یك فرمول ریاضی

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

روش اول : در سلولی كه می خواهیم نتیجه نوشته شود یا در خط فرمول علامت = را تایپ كرده ، سپس آدرس عملوند اول را تایپ كرده عملگر را می نویسیم و بعد آدرس عملوند دوم و بهمین ترتیب ادامه می دهیم .

روش دوم : در سلولی كه می خواهیم نتیجه نوشته شود علامت = را تایپ كرده ، سپس بر روی سلولی كه حاوی اولین عملوند است كلیك كرده ، سپس عملگرد را تایپ كرده و بعد بر روی سلول حاوی در بین عملوند كلیك می كنیم و به همین ترتیب ادامه می دهیم .

* تقدم عملگرهای ریاضی :

اگر در یك فرمول چندین عملگر ریاضی داشه باشیم ، این عملگرها دارای تقدم هستند كه عبارتند از :

()

^

%

* و /

+ و –

می بینیم كه * و / در یك خط نوشته شده اند زیرا این دو دارای تقدم یكسان هستند و اگر در فرمولی هر دو عملگر بودند از سمت چپ اولین عملگر انجام می شود .

2- عملگر متنی : برای عبارتهای متنی می توانیم از عملگر & (الحاق) استفاده كنید. با قرار دادن این عملگر بین دو متن آن دو را به هم پیوند می دهید.

3- عملگر آدرس : برای بیان محدوه ای از آدرس می توانیم از عملگر : استفاده كنیم. كافی است آدرس ابتدای محدوده را نوشته، سپس عملگر آدرس را تایپ كنیم و بعد آدرس انتهای محدوده .

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

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

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

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

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

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

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

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

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

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

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

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

2- توسط ماوس :

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

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

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

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

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

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

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

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

2- توسط ماوس

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

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

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

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

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

نكته :

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

 

دستور برگشت :

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

روش اول :

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

2- انتخاب Undo

روش دوم :

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

روش سوم :

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

دستور Redo :

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

روش اول :

1- منوی Edit

2- انتخاب Redo

روش دوم :

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

روش سوم :

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

انتقال سلولها در اکسل  :

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

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

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

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

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

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

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

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

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

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

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

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

2- انتخاب Office clipboard

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

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

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

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

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

حذف سلول

پاك كردن سلول و حذف سلول :

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

پاك كردن محتویات خانه ها :

روش اول :

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

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

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

روش دوم :

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

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

روش سوم :

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

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

حذف سلول :

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

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

الف – Edit – delete

ب – R.C – delete

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

الف – Shift Cell Left :

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

ب- Shift Cells Up :

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

ج- Entire Row :

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

د- Entire Column :

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

نكته :

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

توابع گرد کردن (رند کردن) اعداد در اکسل

خانه‌های اکسل مثل آدمها و سکه‌ اند، یعنی دو رو دارند . یک ظاهر و و یک باطن .  مانند خانه‌ای که تاریخ 7/27/2006 را نشان می‌دهد اما باطن آن عدد 38925 است.

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

 

          

در مثال زیر این امر به وضوح مشخص است که در خانه عدد 123.1 نشان داده می‌شود اما در نوار فرمول مقدار واقعی این خانه که 123.1233 است را می‌بینیم.

 

 

 

منظور از روند کردن یک عدد، یعنی تغییر دائمی در رقمهای آن عدد و می‌توان آنرا به انواع زیر دسته بندی کرد:

• روند کردن قسمت اعشار یک عدد
• روند کردن قسمت صحیح یک عدد

 

تابع  Roundدر اکسل 

برای گرد کردن تا تعداد رقم دلخواه اعشار بکار می‌رود ، بدین صورت که :

ROUNDUP(Number,Num_digits)

Number : عددی که می‌خواهیم آنرا روند کنیم.

Num_digits : دقت اعشار

 

اگر پارامتر دوم عدد مثبت باشد، رقم داده شده را با آن دقت اعشار داده شده گرد می‌کند (عدد 5 بستگی به رقم بعدش دارد ، اگر عدد بعد از 5 از 5 بیشتر باشد 5 به 6 گرد می‌شود)

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

 

=ROUND(2.15, 1)	عدد 2.15 را تا یک رقم اعشار گرد می‌کند که می‌شود 2.1
=ROUND(2.149, 1)	Rounds 2.149 to one decimal place (2.1)
=ROUND(-1.475, 2)	Rounds -1.475 to two decimal places (-1.48)
=ROUND(21.5, -1)	Rounds 21.5 to one decimal place to the left of the decimal point (20)

 

 

ROUNDDOWN	گرد کردن اعداد به سمت صفر	=ROUNDDOWN(2.578;2)   ⇒   2.57
ROUNDUP	گرد کردن اعداد دور از صفر	=ROUNDUP(2.578;2)  ⇒   2.58
CEILING	عدد 2.4 را به نزدیکترین مضرب 2 که از خودش بیشتر است گرد می‌کند .	=CEILING(2.4;2)   ⇒   4
FLOOR	عدد 24 را به نزدیکترین مضرب صحیح 7 که از 24 کمتر است گرد می‌کند	=FLOOR(24;7)  ⇒   21
EVEN	عددی را به نزدیکترین عدد زوج بعداز خودش گرد می کند.	=EVEN(2.5)   ⇒   4
ODD	عددی را به نزدیکترین عدد فرد بعد از خودش گرد می‌کند.	=ODD(1.5)  ⇒   3
TRUNC	قسمت اعشاری را حذف می‌کند.	=TRUNC(8.9)   ⇒   8
MROUND	عددی را به مضربی دلخواه از عدد دیگر، گرد می‌کند.	=MROUND(10;3)  ⇒   9
INT	جزء صحیح یک عدد را می‌دهد.	=INT(5.4)  ⇒   5

 

 

تابع ROUNDUP 

فرض کنید که قرار است امتیاز افرادی را پس از یک سری محاسبات بدست آورید، اگر شما از تابع ROUND استفاده کنید ، چون اعداد 12.44 را تا یک رقم اعشار می‌کند 12.4  ، ممکن است فردی اعتراض کند که امتیاز او نادیده گرفته شده است ! آنهم در حد یکصدم ! بنابراین بهتر است از ROUNDUP استفاده کنیم که در هر حال امتیازی بیشتر را محاسبه می‌کند.

 

تابع CEILING 

این تابع کار مثل راننده تاکسی‌ها عمل می‌کند، یعنی اگر کرایه شما 118 تومان شده باشد ، می‌گویند 125 تومان یا مثلا اگر کرایه شما شده باشد 233 تومان می‌گویند 250 تومان . البته این به این دلیل نیست که می‌خواهند پول بیشتری بگیرند ! بلکه به خاطر این است که در سیستم پولی ما 25 تومانی داریم اما 33 تومان نه. در واقع آنها عدد را به اولیل مضرب 25 بالاتر از کرایه گرد می‌کنند و ما در اکسل می‌نویسیم :

=CEILING(کرایه , 25 )    

توجه: عکس این عمل یعنی راننده تاکسی منصف تر (عدد کمتر) را تابع Floor انجام می دهد.

 

تابع TRUNCدر اکسل

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

 

گرد کردن قسمت صحیح یک عدد نه قسمت اعشاری!!

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

فرمول	خروجی
=ROUND(13570517,-3)	13571000
=TRUNC(13570517,-3)	13570000

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

در خیلی از موارد و به ویژه فایلهای اکسل صورت های مالی، برای خوانایی بیشتر آنها را بر 1000 یا 1000000 تقسیم می کنند. مثلا عدد 1357051700 را به صورت 1357 نشان می‌دهند. تا اینجا که مشکلی نیست اما مشکل آنجا پیدا می شود که دیگر حاصل جمع این اعداد اختلافی جزیی با جمع واقعی خواهد داشت و کارشناسان مالی با کلی زحمت این مشکلات را حل می کنند.

 یک راه بسیار ساده برای اینکار است. اصلا به اصل اعداد دست نزنید.

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

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

 

 

چرا باید از گانت چارت در اکسل استفاده کنیم؟

- تصور کنید که چقدر بالا انداختن و کنترل دوجین توپ به‌طور همزمان دشوار است!

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

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

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

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

در این آموزش  نگاهی به این موضوع خواهیم داشت که چرا گانت چارت‌ها سودمند هستند و اینکه چطور می‌توانید آنها را به کار گیرید تا پروژه‌های خود را سازماندهی کنید .

تاریخچه این ابزار

در اواخر سال 1800، یک مهندس لهستانی به نام کارل آدامیک نمودار گردش کار تصویری ایجاد کرد که نام آن «هارمونوگرام» بود.

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

 

چرا باید از گانت چارت استفاده کنیم؟ 

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

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

ساخت یک گانت چارت درEXCEL

برای ساخت گانت چارت برای پروژه خود، مراحل زیر را انجام دهید: 

قدم اول: وظایف ضروری را شناسایی کنید. 

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

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

مثال:

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

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

* قدم دوم: شناسایی ارتباط میان وظایف 

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

این وظایف، وظایف «زنجیری» یا «خطی» نامیده می‌شوند. 

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

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

گانت چارت را آغاز کنید.

نکته:

در گانت چارت سه رابطه اصلی بین وظایف زنجیره‌ای وجود دارد:

* پایان به شروع: وظایف نمی‌توانند قبل از به پایان رسیدن وظایف قبلی (مرتبط) به پایان برسند. اگرچه، می‌توانند بعدا شروع شوند. 

* شروع به شروع: وظایف نمی‌توانند قبل از شروع وظیفه قبلی شروع شوند. اگرچه می‌توانند بعدا شروع شوند. 

* پایان به پایان: وظایف نمی‌توانند قبل از به پایان رسیدن وظیفه قبلی به پایان برسند.

* نوع چهارم، شروع به پایان، بسیار نادر است.

تذکر 1: 

وظایف می‌توانند بطور همزمان زنجیره‌ای و موازی باشد- برای مثال، دو وظیف (ب و پ) وابسته و وظیفه‌ای دیگر (الف) است و ممکن است بطور همزمان به پایان برسند. وظیفه (ب) که به‌دنبال وظیفه (الف) انجام می‌شود، زنجیره‌ای و با توجه به وظیفه (ت) موازی است.

تذکر 2:

برای کاهش زمان تحویل، باید تا آنجایی که ممکن است به‌صورت موازی کار کنید. همچنین باید گستره پروژه را تا حد ممکن کوچک نگه دارید. (جدول شماره 2) 

* قدم سوم: وارد کردن اطلاعات

می‌توانید به‌صورت دستی گانت چارت بکشید یا از نرم‌افزارهای تخصصی مانند Gantto، Matchware و Microsoft Project استفاده کنید. برخی از این نرم‌افزارها قابلیت این را دارند که شما و همکارانتان بطور همزمان و از هر نقطه‌ای به آنها دسترسی پیدا کنید. 

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

* قدم چهارم: پیشرفت چارت 

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

گانت چارت را برای انعکاس تغییرات به محض وقوع به روز‌رسانی کنید. این به شما کمک می‌کند تا برنامه‌ها، گروه و اسپانسر خود را به‌روز نگاه دارید.                                                                   

نکات کلیدی

 

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

 

 

غیر فعال کردن Auto Fill Auto Fill در Excel

ll بطور خودکار فعال است. جهت غیر فعال نمودن آن مراحل زیر را طی میکنیم :
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 : اگر اندازه متن از سلول بیشتر باشد میتوان سلولهایی را که متن اشغال کرده را انتخاب نمود و سپس در کنار این گزینه تیک زد. این عمل باعث میشود که این سلول‌ها بهم پیوسته و یک سلول شوند.

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

برنامه نویسی در محیط اکسل برای تازه کارها کار سختی است و بهمین دلیل می توانید از برنامه های آماده نوشته شده در به زبان VBA اکسل، در اینترنت استفاده نمایید. برای شروع کار باید با اصطلاحات Subroutine - Module  آشنا باشید.

 

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

 

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

 

نامهای procedure, method, function و routine همه نامهای دیگری برای سابروتین هستند و معادل آن در زبان فارسی "رویه" است.

 

در زبان برنامه نویسی ویژوال بیسیک اکسل اگر بخواهید دستوری را بنویسید که مثلا عدد 2+2 را محاسبه کنید ، باید این دستور را در داخل یک سابروتین قرار دهید:

 

Sub Test()

 

a = 2 + 2

 

End Sub

 

در مثال ساده بالا شما با کلمه SUB مواجه می شوید که شروع سابروتین و پایان یک سابروتین را نشان می دهد و هر گاه این سابروتین که اسم آن Test است را اجرا کنید ، تمامی دستورات این سابروتین خواهند و پردازش و در نهایت اجرا می شوند. در ادامه این آموزش با نحوه اجرای یک سابروتین نوشته شده در محیط VBA اکسل آشنا خواهید شد.                                                                                                                                         

توابع جدید به چه کاری می‌آیند

 

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

 

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

• جلوگیری از کارهای تکراری در اکسل

 

• انجام محاسبات پیچیده

 

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

 

• به اشتراک گذاشتن توابع با سایر کاربران

 

• استفاده سریعتر از نرم افزار

 

• جلوگیری از اشتباهات کاربران

آشنایی با ویژوال بیسیک

 

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

 

لازم به ذکر است که نسخه جدید نرم افزار VB به نام VB.NET نیز وجود دارد که امکان استفاده آن در OFFICE 12 که نسخه بعدی افیس است گنجانده شده و بد نیست بدانیم که VB.NET در واقع قابلیتهای زبان برنامه نویسی C# را دارد.

 

 

 

شما برای استفاده از VB در اکسل نیاز ندارید که نرم افزار VISUAL BASIC را نصب کنید ، همراه با نصب افیس خود این نرم افزار نیز نصب می‌شود.

در ضمن VBA همان زبان برنامه نویسی ویژوال بیسک می‌باشد که در آن قابیلیتهای ویژه‌ای برای کار با اکسل،اکسس، ورد و ... گنجانده شده است.

 

 

 

گام اول ورود به محیط ویژوال بیسیک

 

ابتدا بایستی وارد محیط VB شویم. برای اینکار چندین راه وجود دارد که عبارتند از:

• زدن کلید ALT+F11 

 

• از منوها : Tools à Macro à Visual Basic Editor

 

• از Toolbar  :

 

 

محیط ویژوال بیسک

 

 

 

 

 

گام دوم ایجاد یک ماژول

 

شما باید دستورات تابع خود را در یک Module (ماژول) بنویسید ،  از منوی Insert گزینه Module را بزنید . و اگر به project explorer نگاه کنید متوجه خواهید شد که یک ماژول جدید ایجاد شده است.

 

 

نام ماژول جدید

اگر این پنجره نیست کلید Ctrl+R را بزنید

گام سوم ایجاد یک تابع درماژول

 

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

 

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

 

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

 

قانون سوم ، نوع داده ورودیها (و خود تابع) باید مشخص شود.

 

این دستورات عبارتند از :

 

Private Function Test(Num As Integer) as Double

 

 

 

End Function

 

نام تابع ما test است و عبارت داخل پرانتز می‌گوید که این تابع یک ورودی دارد که نام آن ورودی Num است و integer  بیانگر آن است که این ورودی عددی صحیح است . (-32,768  تا 32,767 )

 

خروجی تابع از نوع double است و البته گذاشتن آن در همه موارد الزامی نیست ، گرچه بهتر است که مشخص شود. (برای اطلاع بیشتر به کتابهای برنامه نویسیم مراجعه کنید.)

 

عبارت Private Function نشانگر شروع تابع و End Function برای پایان تابع است.

 

 

 

 

 

گام چهارم – نوشتن تابع در محیط VBA

 

فرض کنید می‌خواهیم تابعی بنویسیم که یک عدد را بگیرد و آنرا در 10 ضرب کند!

 

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

 

خوب اسم آنرا Test می‌گذاریم و می‌دانیم که این تابع باید یک ورودی داشته باشد و خوب چون به تازگی با نوع عدد Integer  آشنا شدیم (عدد صحیح) نوع این ورودی را هم Integer می‌گذاریم.

 

باید نامی برای این ورودی در نظر بگیریم ، این نام نباید یک نام آشنا ! برای VB باشد و بهتر است نامی با مسما در نظر بگیریم ، اینجا اسم این ورودی را Num می‌گذاریم.

                         

 

 

 

تعریف ماژول:

 

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

 

درج یک ماژول و سابروتین در VBA:

 

1- در ابتدا با زدن کلید Alt+F11 وارد محیط VBA اکسل شوید.

 

از منوی Insert گزینه Module را انتخاب کنید تا یک ماژول ایجاد شود. در سمت راست صفحه شما قاب Project Explorer را اگر مشاهده کنید خواهید دید که ماژول شما با نام Module1 نمایش داده می شود . (اگر Project Explorer را نمی بینید از کلید Ctrl+R استفاده نمایید)

 

3- سابروتین خود را بنویسید.

 

 

 

ذخیره فایل اکسل حاوی برنامه

 

در اکسل 2007 اگر فایل خود را با پسوند XLSX ذخیره نمایید ، تمامی برنامه های (ماکروها) یا به عبارت صحیح تر تمامی ماژولها پاک می شوند. در واقع این اقدام امنیتی اکسل 2007 است که فایل حاوی یک برنامه وِیژوال بیسیک را با پسوند XLSA ذخیره می کند.

 

شما در اکسل 2003 نمی توانستید تعیین کنید که آیا فایل حاوی ماکرو - ماژول - برنامه (همه این اصطلاحات دارای یک معنی هستند) است یا نه و حتما باید فایل را باز می کردید تا متوجه این موضوع می شدید.

 

 

 

در شکل زیر تفاوت Icon فایلهای اکسل 2007 که حاوی ماکرو (برنامه) است با Icon یک فایل عادی اکسل 2007 را مشاهده می نمایید.

 

 

 

باز کردن فایل حاوی برنامه ویژوال بیسیک (ماکرو)

 

اکسل به صورت پیش فرض برنامه موجود در یک فایل را غیر فعال (Disable) می کند زیرا ممکن است که این برنامه به نوعی یک ویروس باشد و این موضوع یک اقدام پیشگرانه است که در تمامی نرم افزارهای خانواده آفیس از جمله اکسل وجود دارد.

 

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

 

سپس گزینه Enable this Content بعد از باز کردن فایل در اکسل2007 بر روی دکمه را بزنید

 

 

 

 

 

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

 

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

 

اگر مایل باشید می توانید برای اجرای ماکرو (سابروتین) در اکسل یک Shortcut key (یا Hotkey) نیز تعریف                                                                                                                                                 کلیک کنید

ایجاد دکمه جدید در صفحه اکسل:

 

از ابزار های DESIGN MODE  در منوی ماکر COMMAND BUTTON  را انتخاب می کنیم دکمه جدید را انتخاب کرده و بر روی آن دابل کلیک می کنیم تا صفحه ویژوال بیسیک باز شود.

 

در صفحه ایجاد شده دستور زیر را وارد می کنیم:

 

Range("A1:A10").Value = 10

 

 

 

با این دستور به اکسل گفته شده که A1 تا A10   را گرفته و به آن مقدار 10 را بدهد.

 

از عبارت  RANGE("@") برای مشخص کردن ناحیه ای در اکسل و از عبارت VALUE=.  برای تغییر مقدار سلولها استفاده می کنیم.

 

حال از این صفحه خارج شده و بر روی دکمه گونیا(در منوی ویژوال بیسیک نوار خاکستری رنگ بالای صفحه) کلید کرده و EXIT DESIGN MODE  را می زنیم.

 

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

 

حال از این صفحه خارج شده و بر روی دکمه گونیا(در منوی ویژوال بیسیک نوار خاکستری رنگ بالای صفحه) کلید کرده و EXIT DESIGN MODE  را می زنیم.

 

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

 

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

 

توجه: در صورتی که از علامت '  در ابتدای یک خط برنامه نویسی در محیط VB استفاده شود آن خط سبز شده و عملیاتی بر روی آن خط اجرا نخواهد شد. این خط اصطلاحا Comment (توضیحات)‌نامیده می شود. Comment حالت اجرایی ندارد و از آن برای ارایه توضیحات استفاده میشود. Comment ها به خوانایی برنامه بسیار کمک میکنند.

 

 

 

دستور زیر را می نویسیم:

 

Range("D1").Value = Range("B1") + Range("C1")

 

دستور زیر را در یک سط دیگر می نویسیم:

 

Range("E1").Value = "=A1+A2"

 

ملاحظه می شود که در سطر 1E ارزشهای موجود در A1 و A2 جمع زده می شود.

 

نکته : به علامت = در داخل کوتیشن توجه شود.

 

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

 

دستور SELECT

 

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

 

دستور زیر را وارد کنید:

 

Range("F1:F10").Select

 

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

 

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

 

مواردی که در بالا اشاره گردید خلاصه ای از سه دستور RANGE VALUE SELECT بود که اشاره ای مختصر به آن گردید.

 

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

 

 

 

 

 

1. شروع برنامه نویسی

 

روشهای مختلفی برای شروع  و ایجاد یک برنامه با VBA وجود دارد. روش اصلی و مستقیم  نوشتن کدها در داخل  ویرایشگر VB  یا همان     ‌VBE  می‌باشد. یک روش ساده دیگر نیز وجود دارد و آن ایجاد یک command button بر روی صفحه گسترده محیط اکسل و شروع برنامه نویسی با کلیک بر روی  آن است. کار را با روش اول شروع می‌کنیم

 

1.1        روش اصلی: ایجاد و اجرای یک برنامه در VBE

 

برای ایجاد یک برنامه در محیط برنامه نویسی اکسل مراحل زیرانجام می‌شود:

 

1ورود به محیط برنامه نویسی یا همان Visual Basic Editor

 

 برای انجام اینکار چند روش وجود دارد:

 

·        استفاده از کلیدهای میانبر:  F11  + ALT ‌

 

·        استفاده از منوی اکسل:  Tools-> Macro-> Visual Basic Editor

 

·        استفاده از ToolBar یا همان نوار ابزار Visual Basic و فشردن آیکون Visual Basic Editor

 

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

 

2- ایجاد Module: ورود به  منوی  Insert  و انتخاب گزینه Module  

 

3- ایجاد یک رویه یا در اکسل

 

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

 

فشردن دگمه  Enter   

 

بلافاصله بطور اتوماتیک فرمان End Sub  در یک خط جدید اضافه می‌شود.

 

4- نوشتن کدهای برنامه VBA: کدهای برنامه را خط به خط  بین فرمانهای Sub  و End Sub می‌نویسیم.

 

 

 

 به عنوان مثال:

 

Sub MyProgram()

 

Range("A1:A10").Value = "Visual Basic For Applications"

 

Range("A11")=10

 

Range("B11").Value = 20

 

Range("C11").Value = "=A11+B11"

 

End Sub

 

5- اجرای برنامه : برای اجرای برنامه چندین روش وجود دارد

 

·         فشردن کلید F5

 

·         فشردن آیکن Rub Sub/User Form از   Toolbar

 

·         بازگشت به محیط   Excel و استفاده از F11  + ALT 1.2        روش دیگر: ایجاد یک برنامه با استفاده از command button

 

در این روش مراحل زیر را انجام می دهیم

 

 6- ایجاد command button

 

برای قرار دادن  command button بر روی محیط اکسل مسیر زیر را طی می‌کنیم:

 

View منوی  àToolbaràControl ToolBox

 

(البته اگر قبلا نوار ابزار Visual Basic Editor نیز فعال شده باشد میتوانید Control ToolBox را بطورمستقیم از آن انتخاب کنید.)

 

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

 

 

 

  نوشتن کدهای VBA:

 

 با کلیک کردن بر روی command button  بلافاصله  Visual Basic Editor یا  همان  VBE  ظاهر می‌شود. بلافاصله میتوانید دستورات خود را همانند شکل تایپ کنید. (شکل بعدی)

 

 

 

با دقت در این کدهای VBA ملاحظه می‌شود که:

دستور اول مقدار(Value) سلولهای A1 تا  A10را با عبارت "Visual Basic for Application" پر می‌کند.

دستور بعدی نیز حاصل جمع سلولهای  A11  و B11 را در سلول C11 قرار میدهد.

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

 خروجی و رابط برنامه

 

اجرای برنامه:

 

 برای اجرای این برنامه به محیط اکسل برمی‌گردیم. (میتوان از F11  +   ALT ‌نیز استفاده کرد). با فشردن آیکن Exit Design Mode   از    Control Toolbox از حالت طراحی خارج می‌شویم. پس از آن با کلیک بر روی  command button برنامه اجرا می‌شود که خروجی آن در شکل نشان داده شده است.

 

دستور CELLS:

 

این دستور با استفاده از مختصات سلولها آنها را مشخص میکند. به عنوان مثال  CELLS(2,5)  سطر 2 و ستون 5 یعنی E2را مشخص می کند.

 

در دستور: 'Cells(2,5).Value = 15 در سل 2و5 ارزش 15 را قرار می دهد

 

 

 

 

 

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

 

Cells(2,5).Select

 

RANGE("E2").SELECT

 

در دستور زیر

 

Range(Cells(2, 5), Cells(1, 5)).Value = 10

 

 در سلهای مشخص شده ارزش 10 را می گذارد

 

دستور:

 

Range(("A1"), Cells(1, 5)).Value = 10

 

یک دستور ترکیبی می باشد. که هم از Range استفاده شده و هم از دستور CELLS

 

ملاحظه شد که به 2 صورت می توان سلها را از دو دستور  دستورRANGE  و دستور CELLS  انتخاب کرد و از علامتهای با علامتهای ":" یا  " ," استفاده نمود.

 

 

 

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

 

 

 

 

 

 

 

انتخاب سطر و ستون

 

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

 

Range("A:F").Value = 100

 

Range("1:5").Value = 100

 

که مشخص شده در آنها مقدار100 گذارده شود.در این دستورها تمامی ستونهای مابین و تمامی سطهای ما بین نیز ارزش مشخص شده را می گیرند.

 

 

 

دستور زیر:

 

Range("A:A,F:F").Value = 100

 

دستوری است که به برنامه می گوید ستون A و ستون F را به تنهایی ارزش 100 بگذار.

 

می توان به جای ستونها از سطرها نیز استفاده نمود.

 

 

 

دستور زیر:

 

Range("A1:A5,C1:C5,D1:D5").Value = 4

 

را وارد کنید و نتیجه را با RUN  نمودن ببینید.

 

معرفی سلولها با استفاده از متغیرها 

 

دستور زیر را واد کنید:

 

A = 1

 

B = 2

 

C = 3

 

Range(Cells(A, A), Cells(B, B)).Value = 6

 

که در این صورت متغیرهای A B C  را می توان برای برنامه تعریف کرد و در دستور CELLS  ا

 

 

 

 

 

RExcel  آنالیز آماری در Excel

 

در نرم افزار Excel  توانایی کار با ریاضیات و آمار در حد متوسطی قرار داده شده است و برای حل مسائل آمار پیشرفته و جوابهای قابل اطمینان تر به Add-in هایی نیازمندیم. یکی از بهترین این Add-in ها R است.

 

R بسته ای قابل توسعه (برنامه نویسی) برای کار با داده ها ، انجام آنالیزهای آماری و نمایش داده ها به صورت نموداری .

 

نرم افزار RExcel محیط R را بیش از هزار قابلیت بر روی نرم افزار Excel به صورت یک Addins آماده استفاده برای کاربران این نرم افزار می نماید.

 

بنابراین R نرم افزاری برای آنالیز آماری و نمایش گرافیکی داده ها.

 

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

 

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

 

* مدیریت داد ها و ابزارهای ذخیره آنها.

 

* ابزاری برای کار با ماتریس ها

 

* قابلیت توسعه و برنامه نویسی قدرتمند با زبانی که به آن S می گویند، که با در این زبان می توان حلقه ها، شرطها و انواع توابع را برنامه نویسی کرد.

 

 

 

RExcel یک Interface – رابطه گرافیکی با کاربر – می باشد که نرم افزار R را به شکل یک Add-in در اکسل نصب می کند.

 

توسط RExcel  می توان داده ها را بین Excel   و R رد و بدل نمود و در Excel می توان از توابع R استفاده نمود. سایر امکاناتی که RExcel در اختیار ما قرار می دهد:

 

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

 

* کنترل Missing Data

 

* نصب خط فرمان R Commander و همچنین Menu در اکسل

  .  Ctrl+R ، برای اینکار در شکل بالا روی گزینه Options کلیک کنید.

مرتب سازی سفارشی (Custom Sort)در اکسل

برای مرتب کردن داده‌ها (Sort)  میتوان از ابزار Sort در تب Data استفاده کرد که شامل دو حالت مرتب سازی از کوچک به بزرگ و بزرگ به کوچک (یا به ترتیب حروف الفبا و عکس حروف الفبا) می‌باشد. ولی در برخورد با داده های کیفی ترتیبی هیچ یک از این دو گزینه مد نظر ما نیست.                                                                                                                                    با آموزش اکسل امروزهمره باشید

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

 

در این حالت، ابتدا باید ترتیب داده ها برای اکسل مشخص شود (این عمل یکبار صورت میگیرد و در تمامی فایلها قابل استفاده می‌باشد) به این صورت که ابتدا لیست تمامی ماه‌ها را به ترتیب در یک ستون وارد می‌نماییم و از تب File، قسمت Option، حالت Advanced را انتخاب کرده و در بخش General بر روی گزینه Edit Custom Listدراکسل  کلیک نمایید.                                                                                       سپس در قسمت پایین ادرس سلولهایی که ماههای شمسی در آنها ثبت شده است را وارد نموده و بر روی گزینه Import کلیک کرده تا لیست ماهها در قسمت Custom List مشاهده شود. از این مرحله به بعد در تمامی فایلهای اکسل، لیست و ترتیب ماههای شمسی تعریف شده است و میتوان با نوشتن اسم یک ماه و اعمال کردن برای سلول های دیگر، لیست ماهها را به ترتیب داشته باشیم.

سپس در قسمت پایین ادرس سلولهایی که ماههای شمسی در آنها ثبت شده است را وارد نموده و بر روی گزینه Import کلیک کرده تا لیست ماهها در قسمت Custom List مشاهده شود. از این مرحله به بعد در تمامی فایلهای اکسل، لیست و ترتیب ماههای شمسی تعریف شده است و میتوان با نوشتن اسم یک ماه و اعمال کردن برای سلول های دیگر، لیست ماهها را به ترتیب داشته باشیم.

 

 

 

بعد از تعریف ترتیب ماههای شمسی، به منظور مرتب سازی اطلاعات بر اساس ماههای شمسی، کافی است که از تب Data، بر روی گزینه Sort کلیک کرده و در صفحه باز شده، در قسمت Column نام ستونی که اسامی ماهها در آن ثبت شده است را انتخاب نماییم و در قسمت Order گزینه Custom List را و در پنجره باز شده، از قسمت Custom List حالت ماههای شمسی را بر گزیده تا با Ok نمودن تمامی پنجره ها، داده ها به ترتیب ماههای شمسی مرتب شوند.

 

 

 

 اگر در یک ستون شماره سال و در ستون دیگر نام ماه شمسی ثبت شده باشد و بخواهیم داده ها به ترتیب سال مرتب شوند و داده های هر سال به ترتیب ماه، کافی است در پنجره Sort بر روی گزینه Add Level کلیک کرده تا دو قسمت برای تعریف معیار مرتب سازی ایجاد شود، سپس در قسمت بالایی ستون سال و در قسمت پایینی ستون ماه به صورت Custom List انتخاب شود.

 

 

                                                                                          

52 تابع کاربردی نرم افزار اکسل

۱- درج تابع

جهت درج تابع در یک سلول، بعد از علامت مساوی یا = تابع مورد نظر درج می شود .                                     2 - ناحیه انتخابی در توابع

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

۳-تابع LEN

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

(LEN(A1=

۴-تابع LEFT

تابع زیر، ۳ کاراکتر از سمت چپ سلول A1 را جدا می کند.

(LEFT(A1,3=

۵- تابع RIGHT در اکسل 

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

(RIGHT(A1,3=

۶- تابع MIDدر اکسل 

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

(MID(A1,3,4=

۷- تابع LOWER

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

(LOWER(A1=

۸- تابع UPPER

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

(UPPER(A1=

۹- تابع PROPER

تابع زیر، اولین کاراکتر سلول A1 را به کاراکتر بزرگ و سایر کاراکترهای آنرا به کاراکتر کوچک تبدیل می کند.

(PROPER(A1=

۱۰- تابع T

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

برای مثال فرض کنید در داخل سلول A1 عبارت متنی “Good” وارد شده است. در این صورت تابع زیر، مقدار “Good” را نشان می دهد.

(T(A1=

توجه: در این مثال اگر مقدار داخل سلول A1 عدد ۴۵ باشد حاصل تابع فوق تهی خواهد بود.

۱۱- تابع UNICODE

تابع UNICODE، کد عددی مربوط به اولین کاراکتر داخل تابع را نشان می دهد.

 

(“UNICODE(“Ali=

توجه: خروجی دو تابع زیریکسان و برابر با عدد ۶۵ است. دلیلش این است که کاراکتر اول عبارت داخل توابع یکسان هستند.

(“UNICODE(“Ali=

(“UNICODE(“Amin=

۱۲- تابع CODE

تابع CODE، کد عددی مربوط به کاراکتر داخل تابع را نشان می دهد.

 

توجه: این تابع برعکس تابع CHAR می باشد.

۱۳- تابع CHAR

تابع CHAR، کاراکتر مربوط به کد عددی داخل تابع را نشان می دهد.

برای مثال تابع زیر، کاراکتر مربوط به کد ۶۵ که همان حرف “A” می باشد، را نشان می دهد.

(CHAR(65=

توجه: این تابع برعکس تابع CODE می باشد.

۱۴- تابع ROW

تابع ()ROW، شماره سطر سلولی که این تابع در آن وارد شده، نشان می دهد.

برای مثال اگر این تابع را در سلول B5 وارد کنیم، شماره سطر را که ۵ می باشد نشان می دهد.

()ROW=

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

 

۱۵- تابع COLUMN

تابع ()COLUMN ، شماره ستون سلولی که این تابع در آن وارد شده، نشان می دهد.

 

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

 

۱۶- تابع ROWS

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

 

۱۷- تابع COLUMNS

تابع COLUMNS، تعداد ستون های موجود در محدوده انتخابی را نشان می دهد.

 

۱۸- تابع SHEET

این تابع شماره کاربرگ (Worksheet) ناحیه انتخابی را نشان می دهد.

 

 

۱۹- تابع SHEETS

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

 

۲۰- تابع TODAY

تابع زیر، تاریخ امروز  بر می گرداند.

()TODAY=

۲۱- تابع NOW

تابع زیر، تاریخ و زمان حال را بر می گرداند.

()NOW=

۲۲- تابع MAX

تابع زیر، بزرگترین مقدار را در ناحیه A1 تا A5 بر می گرداند.

(MAX(A1:A5=

۲۳- تابع MIN

تابع زیر، کمترین مقدار را در ناحیه A1 تا A5 بر می گرداند.

(MIN(A1:A5=

۲۴- تابع SUM

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

(SUM(A2:A4=

۲۵- تابع AVERAGE

تابع زیر، میانگین اعداد ناحیه انتخاب شده را محاسبه می کند.

(AVERAGE (A2:A4=

۲۶- تابع PRODUCT

تابع زیر، ضرب اعداد ناحیه انتخاب شده را محاسبه می کند.

(PRODUCT(A2:A4=

۲۷- تابع عددی ABS

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

(ABS(A2=

۲۸- تابع عددی INT

تابع زیر، جزء صحیح عدد مندرج در ناحیه انتخاب شده را محاسبه می کند.

(INT(A2=

۲۹- تابع عددی MOD

تابع زیر باقیمانده تقسیم مقدار سلول A1 بر مقدار سلول B1 را نشان می دهد.

(MOD(A1,B1=

برای مثال تابع زیر باقیمانده تقسیم عدد “۴۷”  بر ۶ را که برابر با ۵ می باشد نشان می دهد.

(MOD(47,6=

۳۰- تابع عددی EVEN

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

(EVEN(A1=

 

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

۳۱- تابع عددی ODD

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

(ODD(A1=

 

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

۳۲- تابع عددی RAND

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

()RAND=

۳۳- تابع عددی RANDBETWEEN

 تابع زیر، عددی تصادفی را در بازه ۱۰ تا ۲۰ نشان می دهد.

(RANDBETWEEN(10,20=

۳۴- تابع ROUND

تابع زیر، مقدار سلول A1 را تا K رقم اعشار گرد می کند.

(ROUND(A1,K=

 

 

۳۵- تابع ROUNDDOWN

تابع زیر، مقدار سلول A1 را تا K رقم اعشار به پایین (عدد کوچکتر) گرد می کند.

(ROUNDDOWN(A1,K=

 

۳۶- تابع ROUNDUP

تابع زیر، مقدار سلول A1 را تا K رقم اعشار به بالا (عدد بزرگتر) گرد می کند.

(ROUNDUP(A1,K=

۳۷- تابع IF

تابع زیر، صحت شرطی را بررسی می نماید و در صورت صحت داشتن شرط، عبارت دوم و در غیر این صورت، عبارت آخر را نشان داده یا محاسبه می نماید.

مثال: تابع زیر، صحت شرط بزرگتر بودن مقدار سلول A1 از عدد ۲۰ را بررسی می نماید و در صورت صحت داشتن شرط، عدد ۵ و در غیر این صورت، عدد ۳۰ را نشان می دهد.

(IF(A1>۲۰,۵,۳۰=

مثال: تابع زیر، صحت شرط بزرگتر بودن مقدار سلول A1 از ۲ برابر مقدار سلول A2 را بررسی می نماید و در صورت صحت داشتن شرط، حاصل جمع سلول های B1 و B2 و در غیر این صورت، عدد ۳۰ را نشان می دهد.

(IF(A1>(۲*A2),B1+B2,30=

۳۸- تابع EXACT

تابع زیر، برابر بودن مقادیر سلول های A1 و B1 را بررسی کرده و در صورت برابر بودن آنها مقدار TRUE و در غیر این صورت مقدار FALSE را برمی گرداند.

(EXACT(A1,B1=

۳۹- تابع AND

تابع زیر، صحت چند شرط را بررسی کرده و در صورت صحت تمامی آنها مقدار TRUE و در غیر این صورت مقدار FALSE را برمی گرداند.

(AND(A1>۲,B1=5=

۴۰- تابع OR

تابع زیر، صحت چند شرط را بررسی کرده و در صورت عدم صحت تمامی آنها مقدار FALSE و در غیر این صورت مقدار TRUE را برمی گرداند.

(OR(A1>۲,B1=5=

۴۱- تابع CLEAN

تابع زیر، مقدار سلول A1 را بدون تمامی کاراکترهای ALT+ENTER آن بر می گرداند.

(CLEAN(A1=

۴۲- تابع TRIM

تابع زیر، مقدار سلول A1 را با حذف کاراکترهای SPACE اضافه (بیش از یک عدد) بر می گرداند.

(TRIM(A1=

۴۳- تابع ISBLANK

تابع زیر، در صورت خالی بودن سلول A1 مقدار TRUE و در غیر این صورت مقدار FALSE را برمی گرداند.

(ISBLANK (A1=

۴۴- تابع ISTEXT

تابع زیر، در صورت متن بودن یا فرمت TEXT داشتن محتویات سلول A1 مقدار TRUE و در غیر این صورت مقدار FALSE را برمی گرداند.

(ISTEXT(A2=

۴۵- تابع ISNUMBER

تابع زیر، در صورت عدد بودن محتویات سلول A1 مقدار TRUE و در غیر این صورت مقدار FALSE را برمی گرداند.

(ISNUMBER(A2=

۴۶- تابع FIND

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

(FIND(“TEACHER”,A1,1=

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

۴۷- تابع SEARCH

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

(SEARCH(“TEACHER”,A1,1=

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

۴۸- تابع VLOOKUP

تابع زیر، مقدار سلول A1 را در اولین ستون محدوده B1 تا D5 جستجو کرده و در صورت یافتن آن در همان سطر شروع به حرکت کرده تا به دومین ستون در همان سطر برسد و آن مقدار را نشان می دهد.

(VLOOKUP(A1,B1:D5,2,FALSE=

۴۹- تابع HLOOKUP

تابع زیر، مقدار سلول A1 را در اولین سطر محدوده B1 تا D5 جستجو کرده و در صورت یافتن آن در همان ستون شروع به حرکت کرده تا به دومین سطر در همان ستون برسد و آن مقدار را نشان می دهد.

(HLOOKUP(A1,B1:D5,2,FALSE=

۵۰- تابع COUNTIFS

تابع زیر، تعداد سطرهایی را می شمارد که در ناحیه D1 تا D5 مقداری برابر با A1 و در در ناحیه E1 تا E5 مقداری برابر با A2 و در در ناحیه F1 تا F5 مقداری برابر با A3 داشته باشند.

(COUNTIFS(D1:D5,A1,E1:E5,A2,F1:F5,A3=

۵۱- تابع SUMIFS

تابع زیر، حاصل جمع ناحیه A2 تا A6 را محاسبه می کند البته به شرطی هر یک از سلول های این ناحیه در مجموع در نظر گرفته می شوند که در سطر متناظر آنها در ناحیه B2 تا B6 مقداری برابر با B11 و در سطر متناظر آنها در ناحیه C2 تا C6 مقداری برابر با C11 موجود باشد.

(SUMIFS(A2:A6,B2:B6,B11,C2:C6,C11=

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

 

۵۲- تابع REPT

تابع زیر مقدار سلول A1 را به دفعاتی برابر با مقدار سلول B1 تکرار می کند.

(REPT(A1,B1=

مثال: تابع زیر با تکرار کلمه ALI به میزان ۳ بار دارای خروجی ALIALIALI می باشد.

(REPT(“ALI”,3=

                         

برقراری ارتباط میان چند فایل اکسل ونکات امنیتی وکاربردی برای حفظ فایل ها دراکسل

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

لینک کردن در اکسل به روش زیر صورت می‌گیرد:

1- ابتدا عبارت مورد نظر به ‌عنوان گزینه‌ فهرست را در یکی از سلول‌های دلخواه وارد کنید.

2- سلول بالا را برگزیده و روی آن کلیک راست کنید سپس گزینه‌ hyperlink را انتخاب کنید.

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

1- لینک به سلول مشخص در همان فایل: در پنجره‌ افزودن لینک به Place in This Document بروید و در کادرType the Cell reference نام سلول مورد نظر را وارد و از فهرست صفحات در کادر زیرین آن، همان صفحه‌ فعلی را انتخاب کنید.

2- لینک به صفحه‌ دیگر و سلول مشخص در همان فایل: در پنجره‌ افزودن لینک به Place in This Document مراجعه و در کادر Type the Cell reference نام سلول موردنظر را وارد کرده و از فهرست صفحات در کادر زیرین آن، هریک از صفحات دیگر را که قصد دارید به آنها لینک شوید، انتخاب کنید.

3- لینک به فایل دیگر: در پنجره‌ افزودن لینک، گزینه‌ Existing File and Web page را انتخاب کرده و با استفاده از ابزار مسیردهی، مسیر فایل اکسل موردنظر را برگزیده و در پایان روی Ok کلیک کنید.

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

’sheet’!A1#

توجه داشته باشید عبارت sheet عنوان صفحه‌ و A1 نیز سلول موردنظر در آن صفحه به‌شمار می‌رود.              اینکار به راحتی از طریق office button>Prepare>Encrypt document و سپس دادن پسورد امکانپذیر است.)
میتوان از worksheet ها و سلولهای خاص و نمودارها، اشیای گرافیکی، نمودارها، سناریو ها، پنجره ها را نیز محافظت کرد.
فرض کنید در برخی سلول ها فرمول دارید و نمی خواهید کسی به آنها دسترسی داشته باشد و یا فرمول شما را بببیند و یا تغییر دهید، بنابراین نیاز دارید که سلول های خاصی را غیرقابل ویرایش یا غیر قابل انتخاب یا غیرقابل رویت نمایید.



محافظت سلولها در برابر تغییرات در اکسل شامل دو مرحله است:
1- قفل کردن یا از حالت قفل خارج کردن (lock / unlock) سلولهای مورد نظردر شیت دراکسل

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


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

به مسیر format cell>protection>lock بروید

تب lock * را تیک بزنید.

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


2- انجام عملیات محافظت شیت (Protect Sheet)
در excel 2007 برای اینکار باید ابتدا به منوی review بروید.(در اکسل 2003 به منوی tools بروید) protect sheet را انتخاب کنید

برای اینکه بعدا بتوانید از حالت protect sheet(unprotect)خارج شوید میتوانید پسورد بگذارید
گزینه هایی در زیر عبارت Allow all user of this worksheet وجود دارد که عملیات را درباره سلولهای قفل شده نمایش داده میشود و میتوانید آنها را هم با توجه به سلولهای قفل شده یا نشده انتخاب کنید و کاربر را محدود کنید.

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

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


به مسیر review>protection> allow users to edit range می رویم.(در excel 2003 اینکار از منوی tools امکانپذیر است)

با استفاده از گرینه new مناطقی را که میخواهیم اضافه می کنیم

و درنهایت password *میدهیم

هنوز کار تمام نشده و مثل حالت قبلی باید sheet * را محافظت کنیم.

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


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

محافظت از یک workbook با استفاده از کلمات عبور
وقتی که protect workbook, protect sheetیاprotect and share workbook را انتخاب میکنید یک کلمه عبور برای غیرفعال کردن محافظت(unprotect) باید استفاده شود. می توانید از تنها ازی یک کلمه عبور برای هر workbook یا worksheet که محافظت کرده اید استفاده کنید.
قفل کردن یک workbook در 2 حالت structure و window انجام می گیرد .



در حالت structure قابلیتهای زیر(درو اقعی بیشتر عملیات کار با شیت) از یک شیت گرفته می شود:



جابجا کردن sheet
تغییر نام و رنگ تب sheet
حذف و اضافه کردن sheet
hide و unhide کردن

چگونه در اكسل توابع را به سرعت بنویسیم ؟

فرمولهای Excel در دسته های مختلفی دسته بندی شده اند كه برای كارهای ریاضی، مالی ، آماری و كار با رشته های متنی و كار با داده های زمان و …. به كار می روند.
توابع ریاضی مشتمل است بر انواع و اقسام توابع مثلثاتی، قدر مطلق، تقسیم باقیمانده ، توان، مجموع مربعات و ….
توابع متنی در اکسل  شامل جستجو در یك متن، حذف و جایگزینی بخشی از یك متن و …. می باشد. توابع آماری برای محاسبه واریانس، انحراف از معیار و كلی چیزهای دیگر كه آرزویشان را داریم هم وجود دارد برای داشتن پیش زمینه ذهنی سعی كنید حتماً سری به لیست این فرمولهای بزنید تا در مواقع لزوم از امكانات تعبیه شده در  استفاده كنید. تابع نویسی از یك علامت = شروع می شود به محض وارد كردن یك علامت مساوی در یك خانه خالی Excel می فهمد مقدار این خانه یك داده تنها نیست بلكه باید آنرا حساب كند.
وقتی نیاز به یك تابع را احسای می كنید به عنوان نخستین سؤال این پرسش مطرح می شود كه از كجا تابع مورد نظر خود را بیاییم؟
ممكن است شما دقیقاً بدانید می خواهید چه كاری انجام دهید ولی اطلاعی نداشته باشید كه آیا در اكسل توابع مورد نیاز شما پیش بینی شده است یا خیر؟ از كجا بدانیم تابعی كه مورد نیاز ما است كجاست؟ شاید هم تابعی را چند ماه پیش به كار برده اید ولی هم اكنون آنرا فراموش كرده اید. فرض كنید می خواهید میزان بازپرداخت ماهانه برای یك وام 180 هزار دلاری را كه ظرف ده سال با بهره 6 درصد باید بازپرداخت شود محاسبه كنید. كافی است در خط فرمول قرار بگیرید و دكمه function insert را بفشارید و در آنجا عبارت مورد نظر خود را در بخش جستجو وارد كنید. یا حتی می توانید نوع تابع مورد نظر خود را از بخش category انتخاب نموده و در لیست مربوطه تك تك روی اسامی هر یك از توابع بایستید و تعریف آنرا بخوانید. مثلاً تابع PMT همان تابعی است كه ما بدنبال آن هستیم و در تعریف آن آورده شده است، محاسبه میزان باز پرداخت ماهانه برای وام با اقساط ثابت و بهره ثابت با انتخاب این تابع كادری باز می شود كه آرگومانهای تابع را از ما دریافت می نماید.
قبل از این كار با كلیك روی گزینه This Topic Help on می توانید نكات راهنما در مورد این تابع را بخوانید. در این راهنما syntax یا به عبارتی فرم دقیق تابع و مشخصات آرگومانهای ورودی آن آورده شده است و همچنین یك مثال كامل از یك نمونه كاربرد تابع ذكر شده است. كه می توانید این مثال را كپی كرده و در فایل خود بچسبانید و نحوه كار آن را ببینید.
پنجره ای كه آرگومانهای تابع را دریافت می كند به تفكیك مقادیر ورودی تابع ما را نشان داده است و منتظر می ماند شما مقدار هر یك از وارد نمایید. در اینجا می توانید به هر یك از این آرگومانها یك مقدار عددی یا آدرس یك خانه تخصیص دهید. آرگومانهایی كه ارائه مقدار آنها ضروری است با رنگ مشكی تیره مشخص شده اند و حتماً باید مقدارشان را مشخص كنید. بقیه آرگومانها مقدارشان اختیاری است و اگر معلوم نكنید به صورت پیش فرض به آنها مقدار داده می شود.
در كادر مقابل هر آرگومان كه قرار بگیرید در پایین ترین بخش صفحه تعریفی از ویژگیها و مشخصات این آرگومان آورده می شود.
تابع 5 PMT(Pate. Noper.Pv.type)‌آرگومان دریافت می دارد كه سه تای اول آن اجباری هستند و دو تای آخری اختیاری.
برای محاسبه اقساط ماهانه وام ده ساله 180 هزاردلاری با بهره 6 درصد در بخش Rate كه نشانگر بهره است مقدار 12/6% را باید وارد كنید. بهره 6 درصد سالیانه برای هر ماه معادل نیم درصد است.
توجه داشته باشید كه نرخ سود را باید ماهانه وارد نمود نه سالانه. آرگومان Noper مبین تعداد كل اقساط وام است كه در اینا معادل 12*10=120 خواهد بود. Pv هم نشانگر كل میزان وام دریافتی بدون در نظر گرفتن بهره و معادل 180000 است. با فشردن دكمه ok نتیجه كار در كاربرگ شما پدیدار می شود ($.79 . 19) یعنی شما هر ماه تقریباً هزار و هفتاد و نه دلار باید بپردازید. چون این مقدار را باید بپردازید و به اصطلاح حسابداری پرداختنی است در داخل پرانتز به عنوان یك عدد منفی – و با رنگ قرمز نشان داده می شود.
مراحلی كه در بالا توضیح دادیم برای وقتی است كه شما خود تابع و یا آرگومانهای آنرا به درستی نمی شناسید. ولی در دفعات بعدی كه نام تابع را به خاطر می آورید كافی است در داخل خود كاربرگ نام تابع را وارد نمایید. بدیهی است باید محتوای خانه ای كه قرار است از طریق یك تابع محاسبه شود با یك علامت مساوی شروع شود. اكسل با نشان دادن نوشته هایی با زمینه زرد رنگ به نام tips به شما راهنمایی های لازم را در خصوص ترتیب و مشخصات آرگومانها می نماید. آرگومان جاری كه در حال وارد نمودن آن هستید پررنگ تر دیده می شود. پارامترهایی كه مقدار دادن به آنها اختیاری است در داخل كروشه نشان داده می شوند.                                                                                                 امید واریم از آموزش اکسل امروز نیز استفادهکنید .

رسم نمودار توابع مثلثاتی (Sin(x و(Cos(x در اکسل

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

برای این کار ابتدا در ستون A از Sheet1 مقادیز زاویه ها را به فاصله ۱۰ تا ۳۶۰ وارد می کنیم. (می توانید به دلخواه فواصل زاویه ها را کم یا زیاد کنید) 

در حالت پیش فرض مقدار زاویه در اکسل در واحد رادیان محاسبه می شود. مثلاً می دانیم که سینیوس زاویه ۳۰ درجه برابر ۰٫۵ می باشد اما اگر فرمول را به صورت =sin(30) بنویسیم نتیجه عدد -۰٫۹۸۸۰۳ خواهد شد. این مقدار برابر سینیوس زاویه ۳۰ رادیان می باشد.

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

برای تبدیل رادیان به درجه به این شکل عمل می کنیم. R*pi()/180=D یعنی مقدار رادیان را در عدد پی که تقریباً برابر (۳٫۱۴) ضرب کرده و بر ۱۸۰ تقسیم می کنیم. (اگر از مقدار ۳٫۱۴ به جای عدد پی استفاده کنیم مقادیر بدست آمده با مقادیر واقعی اختلاف اندکی خواهند داشت)

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

برای این منظور از سربرگ Developer یک کامبوباکس را در یک سلول دلخواه قرار می دهیم.

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

 

 

برای قرار دادن کامبوباکس، پس از انتخاب بر روی قسمتی از صفحه کلیک کرده و کلید چپ موس را نگهداشته و به یک سمت بکشید. بر روی کامبو باکس راست کلیک کرده و Format control را انتخاب می کنیم. در پنجره تنظیمات و در قسمت Input range  محدوده  C2:C3 که در این مثال در آنها عبارت "سینوس و کسینوس " نوشته شده است را انتخاب می کنیم. و در قسمت Cell link یک سلول مثل C4 را انتخاب می کنیم. (سلول لینک با انتخاب سینوس عدد ۱ و با انتخاب کسینوس عدد ۲ را نمایش می د

اکنون با استفاده از اولین دستور IF که در سلول مقابل اولین زاویه یعنی سلول B2 نوشته می شود، اگر عدد سلول لینک شده به کامبوباکس ۱ باشد مقدار سینوس و اگر عدد ۲ باشد مقدار کسینوس زاویه محاسبه میگردد و نمودار بر اساس این مقدار رسم میگردد.

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

((IF($C$4=1,SIN(A2*3.14/180),COS(A2*3.14/180=

برای بقیه زوایا این فرمول را تا مقابل زاویه ۳۶۰ رادیان کپی می کنیم

برای رسم نمودار، ابتدا محدوده داده های جدول (مقادیر ستون B که براساس درجه هستند) را انتخاب کرده و از سربرگ Insert از گروه Chart یکی از انوع نمودار Scatter را انتخاب می کنیم.

می توانید ظاهر نمودار را به دلخواه تغییر دهید

 

 

 

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

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

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

 رسم چنین نموداری سخت نخواهد بود! ابتدا به دوگام اصلی (تغییر نوع سریها و اضافه نمودن یک محور) اشاره می کنیم. سپس مراحل نهایی آن را شرح خواهیم داد.

گام اول: با اطلاعات زیر شروع می کنم.

 

ترکیب چارت های مختلف در اکسل

در اینجا می خواهیم چارتی داشته باشیم که ترکیبی از ستون (Column) و خط (Line) باشد. پس با یک نمودار معمولی ستونی شروع می کنیم (ضمن اینکه هیچ تفاوتی ندارد که با کدام نوع چارت شروع کنیم).

گام دوم: داده های گام اول را انتخاب کنید. از زبانه Insert در ریبون بالا insert a column chart را انتخاب نمایید.

 

حالا یک نمودار ستونی با دو سری داریم. که هر دو با یک محور عمودی (Axis) نمایش داده شده است. تمام داده های ما همین است اما این چارت برای ما اطلاعات خیلی معناداری را نشان نمی دهد. زیرا مقیاس در گردش مالی (Transactions) و مقدار فروش (Units Sold) متفاوت است. گام بعدی تغییر نوع چارت گردش مالی (Total Transactions) از ستونی به خطی است.

گام 3: یکی از سری ها را انتخاب نمایید. که در اینجا ما سری (Total Transactions) را انتخاب می کنیم. شما از راههای متعدد موجود می توانید یکی را برای انتخاب سری مورد نظر انتخاب کنید.

گزینه 1: بر روی سریها روی چارت کلیک نمایید. (لطفا روی legend کلیک نکنید. روی ستونهای قرمز روی چارت کلیک کنید).

گزینه 2: بر روی ریبون روی زبانه Chart Layout کلیک نمایید. (دقت نمایید که این زبانه فقط در هنگام انتخاب چارت فعال می شود و در بقیه موارد قابل رویت نیست. پس اگر این زبانه را نمی بینید روی چارت دوباره کلیک کنید). در قسمت انتهایی چپ ریبون Current Selection را می بینید. که در آن یک منو dropdown به نام  Chart Area وجود دارد.                                                                                                           

از داخل این منو سریی که می خواهید در آن تغییر ایجاد کنید انتخاب نمایید. (در اینجا (Total Transactions.

گام 4: نوع چارت برای سری انتخاب شده را تغییر دهید. برای این کار ابتدا بر روی ریبون به زبانه Design بروید. سپس در قسمت انتهایی سمت چپ روی Change Chart Type کلیک نمایید.

این کار باعث رویت پنجره Change Chart Type خواهد شد. در این حالت نوع چارت دلخواه را انتخاب نمایید مثلات چارت خطی را انتخاب می کنیم.

 

اکنون چارتی داریم با دو نوع نمودار! می توانیم هر تعداد که بخواهیم نمودارهای مختلف را در این چارت با هم ترکیب کنیم.

 

اضافه نمودن یک محور دیگر (Secondary Axis) در نمودار در اکسل 

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

گام 5: در این مرحله سری داده هایی که می خواهید در محور دوم قرار گیرد را انتخاب نمایید. در اینجا ما سری Total Transactions را انتخاب می کنیم. این کار را مانند گام 3 انجام دهید.

گام 6: دوباره به زبانه Layout بروید. و در Current Selection گزینه Format Selection را انتخاب نمایید.

گام 7: با کلیک Format Selection پنجره زیر باز خواهد شد. در این پنجره Primary Axis انتخاب شده است. شما Secondary Axis را انتخاب کنید و پنجره را ببندید.

فوق العاده ست!!!! با موفقیت توانستید محور دوم را در چارت ایجاد کنید!

 تنظیم فرمت چارت در اکسل

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

گام 8: در قسمت Layout روی ریبون، بر Show Legend at Bottom روی قسمت Legend کلیک کنید.

 

گام 9: محور عمودی را تغییر دهید تا علامت دلار را نمایش دهد. برای این کار بر روی لیبل محور عمودی کلیک راست کنید و Format Axis را انتخاب کنید.

 

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

 

گام 10: اضافه کردن عنوان برای محورها. دوباره به زبانه Layout بروید و Axis Titles را انتخاب کنید. از لیست dropdown، Primary Vertical Axis را انتخاب نمایید. هر کدام را می خواهید انتخاب کنید. من Rotated Title را انتخاب کردم.

 

گام 11: به چارتمان یک عنوان هم اضافه می کنیم. بازهم گزینه Layout را در ریبون انتخاب می کنیم. سپس بر روی گزینه Chart Title کلیک می نماییم. هر عنوانی که می خواهید انتخاب کنید. نتیجه نهایی کار بدست آمد!

 

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

نحوه استفاده از تابع SUBTOTAL اکسل

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

از تابع SUBTOTAL اکسل برای انجام عملیاتی مانند جمع کردن، میانگین گرفتن و … روی یک محدوده خاص استفاده میکنیم. بر خلاف توابعی مانند SUM، AVERAGE و … که تنها یک عملیات مشخص رو روی یک محدوده یا RANGE انجام میدهند، تابع SUBTOTAL اکسل این قابلیت را دارد  که با استفاده از پارامتری که به آن  میدهیم عملیات های مختلفی ا روی محدوده مدنظر انجام میدهد

قاعده کلی تابع SUBTOTAL اکسل:

=SUBTOTAL(محدوده مدنظر; شماره تابع)

شماره تابع: این پارامتر عملیاتی راکه میخواهیم روی محدوده مد نظر انجام انجام دهیم . این عملیات ها شامل موارد زیر  ا ست. دقت کنید که در شماره تابع های 1 تا 11، تنها سلول هایی که فیلتر شده باشند لحاظ خواهند شد. شماره های 101 تا 111 دقیقا مشابه توابع 1 تا 11 هستند با این تفاوت که در این شماره توابع، سلول هایی که HIDE شده باشند نیز لحاظ نخواهند شد :

شماره تابع	تابع	توضیحات
1	AVERAGE	میانگین سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
2	COUNT	تعداد سلول های محدوده مدنظر رو شمارش میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
3	COUNTA	تعداد سلول های محدوده مدنظر رو شمارش میکنه. سلول هایی که محتوی متن باشند هم شمارش خواهند شد. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
4	MAX	ماکزیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
5	MIN	مینیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
6	PRODUCT	حاصلضرب سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
7	STDEV.S	انحراف معیار نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
8	STDEV.P	انحراف معیار سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد

محدوده مدنظر: این محدوده سلولهایی که قراراست  عملیات روی آنها  انجام شود رانشان می دهد.        

تعیین شماره تابع SUBTOTAL اکسل:

همانطور که میدانیم تابع جمع در اکسل SUM است. پس کافیست  شماره تابع را  برابر 9 قرار بدیم. (در صورتی که بخواهیم سلول های HIDE شده هم در جمع لحاظ شوند  میتوانیم بجای 9 از شماره 109 استفاده کنیم).

تعیین محدوده مدنظر:

 اگر تعداد محصولات در سلول های D5 تا D14 قرار دارد  پس محدوده مدنظر ما برای جمع، محدوده D5:D14 است.

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

=SUBTOTAL(9;D5:D15)

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

 

نکته:

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

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

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

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

 

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

 

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

 

 

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

 

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

تبدیل جدول متقاطع (Cross Table) به جدول معمولی (List Table) واستفاده از جمع شرطی برای چند شیت در اکس

گاهی از اوقات برای آنالیز داده‌ها (بطور مثال استفاده در نرم افزاری نظیر TableCurve برای برازش منحنی) نیاز می شود تا یک جدول متقاطع از اعداد را به یک جدول لیستی معمولی تبدیل کنیم. شاید تغییرات بصورت دستی برای یک جدول ۴ در ۴ کار آسانی باشد ولی برای یک جدول ۳۰ در ۲۰ چطور؟ در ادامه مطلب با ماهمراه باشید تا با نحوه انجام این کار بصورت خودکار بوسیله نرم افزار اکسل آشنا شوید.

 

تبدیل جدول معمولی به جدول متقاطع در excel                                                                                ظاهرا در نرم‌افزار اکسل چنین امکانی وجود ندارد. اما می‌توان با نصب افزونه Kutools for Excel در اکسل، این عملیات را انجام داد. پس از نصب این افزونه و اطمینان از فعال شدن آن در نرم‌افزار اکسل (اضافه شدن یک تب جدید با نام Kutools در اکسل)، مراحل زیر را طی کنید:

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

مرحله ۲: پس از کلیک روی تب Kutools، در قسمت Range Converter، گزینه ….Transpose Table Dimensions را انتخاب کنید.

 

مرحله ۳: در پنجره Transpose Table Dimensions، گزینه List to cross table را انتخاب کنید. البته در این مرحله هم می‌توانید از قسمتSource range، محدوده داده‌های ورودی را تعیین کنید. محدوده خروجی نتایج را در قسمت Results range مشخص کنید. اگر لیست داده‌های ورودی دارای سربرگ یا عنوان هستند گزینه My data has headers را انتخاب نمایید (مطابق شکل زیر).

 

 

مرحله ۴: در پایان روی Ok کلیک کنید و نتایج را مشاهده کنید.

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

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

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

 

روش اول) استفاده از تابع Sumif در هر شیت و محاسبه مجموع آن‌ها در اکسل

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

---

روش دوم) تابع Sumproduct

مثال زیر را در نظر بگیرید. می‌خواهیم مجموع اعداد ستون H در سه شیت ۱ تا ۳، البته با شرطی که در ستون G حرف A باشد را محاسبه کنیم و نتیجه آن در شیت ۴ درج شود.

 

برای اینکار می‌توان از فرمول زیر استفاده کرد. البته باید در شیت ۴، نام شیت‌ها تایپ شود (A2:A4):

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!G2:G5"),C2,INDIRECT("'"&A2:A4&"'!H2:H5")))

خروجی رابطه فوق برابر می‌شود با ۱۵۰.

 

---

روش سوم) تعریف تابع جدید SUMIF3D

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

برای ساخت ماکرو کلیدهای Alt + F11 را فشار دهید

در واقع این توابع، مجموع اعداد محدوده B2:B5 در سه شیت مشخص شده را محاسبه می‌کنند البته در صورتیکه در محدوده A2:A5 عبارت موجود در سلول A2 یعنی حرف B وجود داشته باشد.

=SUMIF3D(A2:A5,A2,B2:B5,"Sheet1","Sheet2","Sheet3")

یا

=SUMIF3D(A:A,A2,B:B,"Sheet1","Sheet2","Sheet3")

خروجی رابطه فوق برابر می‌شود با ۶۰.

توجه: در تابع SUMIF3D نام شیت‌ها باید بین علامت نقل قول " " قرار گیرد. همچنین ترتیب نام‌ها اهمیت ندارد.

مرتب کردن لیست ایمیل بر اساس دامنه (دامین) در اکسل

ممکن است برای شما هم پیش آمده باشد که لیستی از ایمیل افراد از دامنه‌های مختلف (yahoo.com، Gmail.com، Hotmail.com و ...) را در اکسل دارید و به هر دلیلی قصد دارید تا آن‌ها را بر اساس دامنه مرتب کنید. در ادامه مطلب با آموزش اکسل امروز   همراه باشید تا با روش انجام این کار آشنا شوید.

 

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

روش اول) جدا کردن نام کاربری (User name یا ID) را از دامنه ایمیل و سپس مرتب کردن بر اساس دامنه

ابتدا باید آی‌دی را از دامنه ایمیل جدا کرد. اینکار را می‌توان به دو طریق انجام داد:

•  Text To Columnsدر اکسل
   

   

  ---

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

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

  پنجره جستجو را باز کنید (Ctrl+F) به طور نمونه در کادر جستجو عبارت yahoo.com@ را تایپ کنید. با کلیک روی دکمه Find All اکسل تمام ایمیل‌های یاهو را پیدا می‌کند و شما می‌توانید از آن‌ها کپی بگیرید و در جای دیگر پیست کنید.

   

  
  
  
  Text To Columns

ابتدا ستون ایمیل‌ها (ستون B) را انتخاب کرده و از تب Data، روی گزینه Text to Columns کلیک کنید.

 

در پنجره Convert Text to Column Wizard گزینه Delimited را تیک بزنید و سپس روی دکمه Next کلیک کنید:

 

در گام ۲ از این پنجره، در قسمت Delimiters فقط گزینه‌ی Other را تیک بزنید و علامت @ را روبروی گزینه‌ی Other وارد و روی دکمه Next کلیک کنید.در گام ۳ در قسمت Data Preview مشاهده می‌شود که آی‌دی‌ها از دامنه‌ها جدا شده‌اند ولی چون ما فقط به دامنه‌ها نیاز داریم پس از انتخاب ستون آی‌دی‌ها گزینه (Do not import column (skip را تیک می‌زنیم و در قسمت Destination ستونی که می‌خواهید دامنه‌ها در آن‌جا قرار گیرند (مثلا ستون C) را انتخاب کنیم و در پایان در روی دکمه Finish کلیک کنیم.   نتیجه نهایی پس از جدا سازی را در شکل زیر مشاهده می‌کنید: 

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

 

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

 

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

---

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

 

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

 

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

 

---

 

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

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

نرم افزار Excel را اجرا نمایید.
سپس فایلی که در آن داده های شما در آن ثبت است را باز نمایید، یا هم اکنون داده های خود را به شکل جداگانه در هر سلول وارد نمایید.
در این ترفند جهت سادگی کار از یک مثال استفاده میکنیم.
فرض کنید 10 عدد یا داده دارید و قصد دارید میانگین، میانه و مد این 10 داده را محاسبه کنید.
ابتدا 10 عدد را به شکل منظم در زیر هم در ستون A و در دریف های 1 تا 10 در 10 سلول وارد نمایید.
در این مثال این اعداد را 97،99،84،93،95،95،93،89،90 و 86 فرض میکنیم. ب

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

(دقت کنید آن را کپی نکنید بلکه تایپ کنید).
در این دستور محدوده سلول   در داخل پرانتز مشخص شده است. یعنی سلول اول A1 (ستون A و ردیف 1) و سلول آخر A10 (ستون A و دریف 10) در میان پرانتز در فرمول درج شده است. شما  بایستی بسته به نوع وارد کردن اطلاعات در سلول ها این دو مقدار را جابجا کنید. نحوه آدرس دهی هر سلول نیز همانطور که واضح است با حرف ستون و شماره ردیف نمایش داده میشود.
اکنون پس از وارد کردن دستور Enter بزنید، حال در همان سلول خالی مقدار میانگین نمایش داده میشود. در این مثال، میانگین 92.1 خواهد بود.
 نمودار دستیابی به هدف  دراکسل                                                                                                 نمودارهای اکسل در ۸ گروه تقسیم شده اند، با این وجود با ترکیب نمودارهای موجود می‌توان نمودارها و گیج های جذابی را ایجاد نمود. برای مثال در حالتی که تعدادی شاخص با هدف گذاری های متفاوت موجود است، میخواهیم سطح تحقق از هدف در هر شاخص را به صورت ذیل نمایش دهیم.

برایرسم آن  نیاز است داده ها در سه ستون به ترتیب، اسم شاخص، مقدار شاخص و مقدار هدف برای شاخص، درون اکسل ثبت شوند، سپس بر روی یکی از سلولها کلیک کرده و از تب Insert نمودار Column Chart حالت Cluster را انتخاب نموده . (یا کلید Alt+f1 را فشار داده)

سپس بر روی ستون مربوط به حالت اهداف کلیک کرده و  با زدن Ctrl+1 صفحه مربوط به تنظیمات نمودار باز میشود، در قسمت Fill & Line رنگ نمودار را بر روی No Fill قرار داده و در قسمت Border حالت Solid Line را انتخاب نموده و ضخامت آن را بر روی ۲pt قرار داده و در نهایت از قسمت Series Option حالت Series Overlap را بر روی +۱۰۰ درصد قرار داده.

نمودار دایره در دایره

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

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

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

 

 به منظور تعیین تعداد قطاع‌های نمودار دایره ای کوچک، کافی است یکی از دایره ها را انتخاب کرده کلید Ctrl+1 را زده تا صفحه Format Data Series باز شود و به قسمت Option رفته و  گزینه Split Series By را بر روی حالت Position قرارداده و در  قسمت Value in Second Plot تعداد قطاع های مد نظر را وارد نموده تا تعداد قطاع‌ها فیکس شود.