آموزش اکسل بخش هفتم

آموزش اکسل بخش هفتم

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

آدرس دهی مطلق

فرض کنید میخواهیم حقوق افراد یک اداره را حساب کنیم. حقوق با استفاده از فرمول زیر حساب میشود: حقوق پایه 7% - حقوق پایه = حقوق حقوق پایه را در ستون B مینویسیم. برای محاسبه حقوق میتوانیم در سلول C1 فرمول B1-B17% را بنویسیم و برای تمام افراد Auto fill کنیم. در این وضعیت در حقیقت از آدرس دهی نسبی استفاده کرده ایم. اما فرض کنید که درصد مالیات عوض شده و 10% شود. حالا باید دوباره فرمول جدیدی در سلول C1 نوشته و مجدد Auto fill کنیم. برای اینکه نخواهیم در هر بار عوض شدن درصد مالیات فرمول را عوض کنیم میتوانیم درصد مالیات را در سلول جدا نوشته و از آدرس آن در فرمول استفاده کنیم.

پس به عنوان نمونه در سلول A1 عدد 7% را مینویسیم و در سلول C1 فرمول B1-B1A1 را مینویسیم حال Aut fill میکنیم. اما میبینیم که برای بقیه سلولها جواب همان حقوق پایه میشود. چرا؟ چون با آدرس دهی نسبی میخواهد عمل کند و چون در سلول C1 فرمول B1-B1A1 بوده، برای C2 فرمول B2-B2A2 میشود که مقدار A2 صفر است. پس جواب همان مقدار حقوق پایه میشود. برای رفع این مشکل باید سلول A1 ثابت شود. یعنی در تمام فرمولها A1 در ستون حقوق پایه ضرب شود. در این وضعیت از آدرس دهی مطلق استفاده میکنیم. برای ثابت کردن سطر یا ستون در کنار حرف ستون یا عدد سطر نشانه $ میگذاریم. اگر بخواهیم سلول را ثابت کنیم در کنار حرف ستون و عدد سطر هر دو نشانه $ میگذاریم. یعنی در سلول C1 مینویسیم: B1-B1$A$1 قالب بندی خانه ها در Excel در Excel این توانایی را داریم که قالب بندی یا فرمت یک سلول را تغییر داده و فرمت آن سلول را به عنوان نمونه تاریخ یا درصد یا زمان یا متن یا.... کنیم. همچنین میتوانیم دور سلول کادر بیاندازیم یا رنگ زمینه آنرا عوض کنیم. برای قالب بندی خانه ها در Excel مراحل زیر را طی میکنیم:

شیوه اول: انتخاب منوی Format، انتخاب گزینه Cells

شیوه دوم: فشردن کلیدهای ctrl +1

شیوه سوم: بر روی خانه های مورد نظرکلیک راست کرده و گزینه Format Cells را انتخاب میکنیم. با اجرای یکی ازسه شیوه بالا پنجره ای باز میشود که دارای Tab های زیر است:

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

Sample: هر فرمتی را که انتخاب کنیم، بر روی محتوای سلول انتخابی نمایش میدهد.

Category: در این بخش نوع داده را معین میکنیم که شامل انواع زیر میباشد:

General: این گزینه عددها را به صورت رشته ای از رقمهای متوالی و بدون هر گونه قالب بندی نشان میدهد و اگر عددی در سلول جا نشودآنرا به صورت نمایی نشان میدهد.

Number: مقادیر را بصورت رشته ای از رقمهای متوالی نشان میدهد. در این وضعیت اگر عدد در سلول نگنجد سلول بزرگتر میشود.

همچنین در این بخش میتوانیم تنظیمات زیر را انجام دهیم:

Decimal Places: در این بخش میتوانیم تعداد ارقام بعد از اعشار را تعیین کنیم.

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

Use 1000 Separator: اگر در کنار بگذاریم اعداد را سه رقم، سه رقم از سمت راست این گزینه میگذارد.

Currency: مقادیر را همراه با سمبل "?" جدا کرده و نشانه (،) پول رایج نشان میدهد. در این وضعیت میتوان تنظیمات زیر را انجام داد:

Symbol: نوع واحد پول را معین میکنید. در این بخش واحد پول کشورهای مختلف نمایش داده شده و میتوانیم واحد دلخواه خود را انتخاب کنیم.

Negative Number: اعداد منفی چطور نمایش داده شوند.

Accenting: نظیر وضعیت Currency است. این قالب بندی، قالب بندی حسابداری میباشد و نشانه پولی در منتهی الهیه سمت چپ آن نوشته میشود.

Date: تاریخ را با قالب بندی خاص تاریخ نشان میدهد. و ما میتوانیم انواع قالب بندی های تاریخ را دیده و انتخاب کنیم. به عنوان نمونه مدلی را انتخاب کنیم که فقط روز و ماه را نشان دهد. و یا مدلی را انتخاب کنیم که روز را به عدد و ماه را به حروف نشان دهد.

Time: زمان را با قالب بندی های خاص زمان نشان میدهد.

انواع قالب زمانی در این بخش وجود دارند. ما میتوانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد. و یا ....

Percentage: عددها را همراه با نشانه درصد نشان میدهد. نکته در تمام قالب بندی ها بجز Percentage فرقی نمیکند که ابتدا داده ها را در سلول وارد کنیم یا اول قالب بندی را تنظیم کنیم. اما در وضعیت Percentage اگر ابتدا عدد را نوشته و در ادامه فرمت را Percentage کنیم، عدد را در 100 ضرب میکند. اما اگر ابتدا فرمت را Percentage کنیم، و بعد عدد را بنویسیم، تغییری در عدد نمیدهد.

Fraction: مقادیر را بصورت عدد صحیح نشان میدهد که بدنبال آن نزدیک ترین کسر به مقدار واقعی ظاهر میشود. برای نوشتن یک عدد مخلوط کافی هست ابتدا بخش صحیح را نوشته در ادامه یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد نشانه (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 میشود.

اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در بخش Fraction مقدار 3/10 را انتخاب کنیم. در این وضعیت نگاه میکند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود در ادامه صورت را هم در همان عدد ضرب میکند.

Scientific: مقادیر را با قالب بندی علمی نشان میدهد.

Text: مقادیر را به همان صورتی که وارد شده اند نشان میدهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان میدهد و مقادیر آنرا محاسبه نمیکند. (کاردرکارگاه 7 تمرین 8 تا 13)

Special: مقادیر را با استفاده از قواعد قالب بندی خاص (مانند کد پستی، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) نشان میدهد. به عنوان نمونه اگر در یک سلول یک شماره تلفن (در وضعیت 10 رقمی) تایپ کنیم، با انتخاب این فرمت به صورت قالب بندی تلفن در می آید. به عنوان نمونه اگر شماره IT (3116681184) را وارد میکنیم، به فرمت تلفن در آمده (1184-668 (311)) و می فهمیم که 311 کد شهرستان، 668 کد محله و 1184 شماره تلفن IT میباشد.

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

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

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

General: هم ترازی پیش فرض

Left: هم ترازی داده ها را به سمت چپ (استفاده برای ارقام)

Center: هم ترازی داده ها در مرکز

Right: هم ترازی داده ها به سمت راست (برای متون)

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

Justify: یک تراز مناسب برای سلول در نظر میگیرد. معمولاً برای زمانی هست که داده ها در سلول نگنجد.

Center Across Selection: وسط متن را در وسط سلولهای انتخابی میگذارد.

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

Bottom: هم ترازی داده ها در پایین سلول

Top: هم ترازی داده ها در بالای سلول

Center: هم ترازی داده ها در وسط یا مرکز سلول

Justify: داده ها در داخل سلول هم تراز میشوند. (بدین معنی که داده ها در داخل سلول بصورت مساوی جاسازی میشوند. نظیر متون موجود در روزنامه ها)

Orientation: شامل بخش های زیر است:

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

Degrees: زاویه متن را نسبت به افق بوسیله تایپ زاویه یا با کلید های Increase و Decrease تعیین کرد.

Text Control: شامل بخش های زیر است:

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

Shrink t Fit: اگر اندازه متن از سلول بیشتر باشد با انتخاب این گزینه متن به اندازه ای کوچک می شود که در داخل سلول بگنجد.

Merge Cells: اگر اندازه متن از سلول بیشتر باشد میتوان سلولهایی را که متن اشغال کرده را انتخاب نمود و در ادامه در کنار این گزینه تیک زد.

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

Font: توسط این بخش میتوان نوع خط، اندازه، رنگ و... خطوط را تعیین نمود. این Tab شامل گزینه های زیر است:

Font: در این بخش نوع خط را تعیین میکنیم.

Font Style: در این بخش میتوانیم تعیین کنیم که خط کج (Italic) یا تو پر(Bold) یا کج و تو پر (Bold Italic) و یا معمولی (Regular) باشد.

Size: در این بخش سایز قلم را تعیین میکنیم.

Underline: در این بخش میتوانیم تعیین کنیم که زیر متن خط کشیده شود یا نه. و نوع خط را تعیین میکنیم.

None: خط نمیکشد.

Single: خط تکی زیر متن میکشد.

Double: دو خطی زیر متن میکشد.

Single Accounting: تک خط زیر کشیده ترین حرف کشیده میشود.(یعنی تمام حروف بالای خط نوشته میشوند.)

Double Accounting: دو خطی زیر تمام حروف و زیر کشیده ترین حرف کشیده میشود.

Color: در این بخش رنگ خطوط را متن را تعیین میکنیم.

Effect: شامل بخش های زیر است:

Strike Through: بر روی متن خط میکشد.

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

Sub Script: برای زیر نویسی استفاده میشود.

Preview: پیش نمایشی از انتخابات را نشان میدهد.

Border: توسط این Tab میتوانیم حاشیه بندی انجام دهیم. که شامل بخش های زیر است:

Style: در این بخش نوع خط حاشیه را معین میکنیم.

Color: در این بخش رنگ حاشیه را معین میکنیم.

Presets: در این بخش محل خطوط را معین میکنیم.

None: حاشیه نمیگدارد.

Outline: دور سلولها حاشیه میگذارد.

Inside: داخل سلولهای انتخابی را حاشیه میگذارد.

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

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

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

Color: در این بخش رنگ مورد نظر را انتخاب میکنیم.

Pattern: در این بخش میتوانیم از الگوها (هاشورها) استفاده کنیم. همچنین رنگ هاشور را تعیین کنیم. حذف قالب بندی سلولها: همانطور که در فصل سوم گفته شد برای پاک کردن قالب بندی سلول کافی هست سلول یا سلولهای مورد نظر را انتخاب کرده در ادامه از منوی Edit زیر منوی Clear، گزینه Formatting را انتخاب میکنیم. توسط این دستور می توانیم اعمال ریاضی، منطقی و دیگر توابع را بر روی داده ها داشته باشیم. برای استفاده از توابع به یکی از روشهای زیر عمل میکنیم:

شیوه اول: بر روی Paste Function , Icon کلیک می کنیم. در پنجره باز شده در بخش Function Category نوع تابع را معین می کنیم. کلیه توابع در 8 دسته طبقه بندی شده اند. در دسته All همه توابع قرار دارند و در دسته Most Recently Used توابعی که به تازگی از آنها استفاده شده هست قرار دارند. اگر گروه تابع را ندانیم میتوانیم آنرا در All پیدا کنیم. در بخش Function name تابع را معین می کنیم. در پنجره ای که باز می شود به دو طریق می توانیم عمل کنیم: الف: در کادر Number 1،محدوده آدرس اعداد را وارد کنیم. برای وارد کردن محدوده آدرس کافی هست ابتدا آدرس اولین خانه محدوده را وارد کرده در ادامه عملگر آدرس (:) را تایپ کنیم و بعد آدرس آخرین سلول محدوده را وارد کنیم. همچنین میتوانیم بر روی Collapse Dialog این کادر کلیک کرده و محدوده را با Drag انتخاب کنیم. در هر کادر یک آدرس وارد کنیم. برای این کار در کادر اول آدرس اولین سلول را تایپ کرده. بر روی کادر دوم کلیک کرده،آدرس دومین سلول را تایپ میکنیم. و الی آخر.... معمولاً زمانی از این شیوه استفاده میکنیم که سلولها پراکنده باشند.

شیوه دوم: منوی Insert را انتخاب می کنیم. زیر منوی Function را انتخاب می کنیم. بقیه مراحل نظیر مرحله 2 به بعد شیوه اول است.

شیوه سوم: در خانه تابع را همراه با آرگومانهای آن تایپ می کنیم. (توابع و آرگومانهایش در بخش بعد توضیح داده شده اند.) اگر قبل از آن = گذاشته باشیم که Enter می کنیم در غیر این صورت بر روی = در نوار فرمول Click می کنیم.

شیوه چهارم: در یک خانه (خانه ای که می خواهیم حاصل نوشته شود) نشانه = را تایپ می کنیم یا نشانه = در خط فرمول را می زنیم. در کادر اول نوار فرمول (Name Box) توابع ظاهر می شوند، کافی هست بر روی drop down آن Click کنیم تا همه توابع را ببینیم. تابع مورد نظر را انتخاب می کنیم. بقیه مراحل نظیر مرحلة 4 به بعد شیوه اول است.