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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

روش ۱) فرمول نویسی:

بوسیله توابع Row ،Index و Column می‌توان اینکار را انجام داد.

ابتدا پس از کپی داده‌ها درون اکسل، باید مشخص کنید که می‌خواهید داده‌ها به چند ستون تبدیل شوند، تعداد این ستون‌ها را با n نمایش می‌دهیم. مثلا  می‌خواهیم تغییرات دما و فشار در طول زمان نشان داده شود بنابراین n=۳ ستون باید ایجاد شود. ستون زمان، ستون دما و ستون فشار.

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

=INDEX(محدوده,n*(ROW(A1)-1)+COLUMN(A1))

چون برای مثال بالا محدوده A1:A9 و n=3 است بنابراین فرمول بصورت زیر اصلاح می‌شود. البته دقت کنید که محدوده را در فرمول بالا با قرار دادن علامت $ در اطراف آن باید ثابت نگه داشت تا تغییر نکند.

=INDEX($A$1:$A$9,3*(ROW(A1)-1)+COLUMN(A1))

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

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

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

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

=MOD(ROW(A1)+n-1,n)

که برای مثال بالا بجای n باید عدد ۳ قرار داد.

=MOD(ROW(A1)+2,3)

تابع MOD:
تابع MOD برای تعیین باقیمانده تقسیم استفاده می‌شوند. در واقع این تابع دو ورودی را دریافت می‌کند و خروجی آن برابر است با میزان باقی‌مانده ناشی از تقسیم ورودی اول بر ورودی دوم. برای مثال اگر در سلول A1 عبارت زیر را وارد کنید، خروجی آن برابر با ۱ می‌شود، زیرا باقیمانده تقسیم ۴ بر ۳ برابر است با ۱.

=MOD(4,3)

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

ابتدا داده‌های خود را در ستون A نرم‌افزار اکسل کپی کنید. سپس کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

 

در پنجره جدید باز شده کدهای زیر را کپی کنید.

SubOneCol2nCols()'n = 3
m = 9
k = 1
For i = 1 To m / n
For j = 1 To n
Cells(k, 1).Select
  Selection.Copy
   Cells(i, j + 2).Select
    ActiveSheet.Paste
    k = k + 1
    Next
Next
End Sub

دقت کنید که در کد فوق باید بجای n و m با توجه به داده‌های خود عدد بگذارید. n تعداد ستون‌ها و m تعداد کل داده‌ها است که در مثال ما n=3 و m=9.

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

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

SubTrsPose()
Dim Area As Range, i As Long
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each Area In Range("A1:A" & LR).SpecialCells(xlCellTypeConstants).Areas
    i = i + 1
    Range("B" & i).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
End Sub

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

• افزونه ASAP Utilities:

پس از دانلود و نصب این Add-in، تب جدیدی با عنوان ASAP Utilities به اکسل اضافه می‌شود.

 

نحوه استفاده از این افزونه در این سایت توضیح داده شده است.

• افزونه Kutools for Excel:

افزونه Kutools for Excel نیز مشابه افزونه قبلی قابلیت تبدیل یک ستون به چند ردیف را دارد. پس از دانلود و نصب این Add-in، تب جدیدی به اکسل با عنوان Kutools اضافه می‌شود. نحوه استفاده از این افزونه در این سایت توضیح داده شده است.

 

---

 

 

 

۱- نصب افزونه Kutools for Excel که در بالا به آن اشاره شد

۲- ماکرو نویسی: با استفاده از ماکرو زیر می‌توانید عملیات فوق را انجام دهید. نحوه استفاده از ماکرو در بالا در قسمت روش ۳ توضیح داده شده است. فقط باید دقت کنید که عامل توقف حلقه وجود سلول خالی است نه Hello، بنابراین وجود سلول خالی بین مجموعه‌ها الزامی است. البته باید به جای عدد ۴، تعداد ردیف‌هایی که می‌خواهید ایجاد شود را قرار دهید. که در مثال فوق ۴ است.

SubOneCol2Cols()
k = 1
For i = 1 To 4
j = 1
   Do Until IsEmpty(Cells(k, 1))
   Cells(k, 1).Select
   Selection.Copy
   Cells(i, j + 2).Select
    ActiveSheet.Paste
    k = k + 1
    j = j + 1
    Loop
    k = k + 1
Next
End Sub

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

Text to Columns دراکسل

اگر بدنبال جداکردن قسمتهای مختلف یک سلول  هستید و میخواهید از هر قسمت آن جداگانه استفاده کنید نگران نباشید. اکسل ابزاری فوق العاده در اختیار شما قرار داده است.                                                  بااین آموزش همراه باشید:                                                                                                           برای این کار کافیست به تب DATA سر بزنید و در منویData Tools نگاهی به Text to Columns بیندازید.

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

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

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

 

حالا کافیست روی گزینه Finish کلیک کنید تا تمام محتواهایی که بین آنها Space وحود دارد را از هم جدا کند:

 

اما فرض کنید میخواهید نام و نام خانوادگی باهم بیاید و فقط شماره ها جدا شوند برای اینکار بجای گزینه Finish باید Next را بزنید:

در پنجره باز شده درexcel دو خط در قسمت Data Preview مشخص است. این خطها نشان دهنده مکان دو Space موجود در سلول است یکی فاصیه بین نام و نام خانوادگی و دومی فاصله بین نام خانوادگی و شماره. برای اینکه میخواهید نام و نام خانوادگی با هم بیاید باید خط اول را پک کنید. برای اینکار میتوانید روی خط اول دبل کلیک کنید.

نکته: اگر بخواهید محتوای سلول را به بخشهای بیشتری تقسیم کنید میتوانید هرجای متن را که خواستید در قسمت Preview کلیک نمایید. همچنین اگر بخواهید جای برش محتوا را تغییر دهید میتوانید با موس خطها را جابجا کنید.

 

حالا روی Next کلیک کنید تا به مرحله بعد بروید. در قسمت Destination باید مشخص کنید که محتواهای جدا شده در کجا قرار داده شود. در حالت پیش فرض روی همان ستونی است که محتوای اولیه وجود دارد و اگر finish را بزنید جایگزین متن اصلی میشود اما میتوانید مثلا سلول B1 را انتخاب کنید تا نام و نام خانوادگی را در ستون B و شماره را در ستون C قرا دهد.

در قسمت data format نیز میتوانید فرمت مورد نظرتان را انتخاب کنید.

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

تحلیل داده ها با ابزار Goal Seek اکسل

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

 

فرض کنید که شرکتی 300 کارمند داره و به هر کدوم از اونها ماهیانه 5 میلیون ریال حقوق میدهد. هزینه مواد اولیه به ازای هر کالا برابر 60 هزار ریال هست و قیمت فروش محصول در بازار هم 80 هزار ریال هست. تعداد تولید  ماهانه محصول شرکت در حال حاضر 100 هزار واحد در هر ماه هست و تمام محصولات تولید شده در بازار فروخته خواهند شد. حالا فرض کنیم که هزینه تمام شده هر واحد کالا و سود شرکت از روابط ساده زیر بدست میاد:

 قیمت تمام شده هر کالا = هزینه مواد اولیه به ازای هر کالا + ( ( تعداد پرسنل *  حقوق ماهانه) / تعداد تولید ماهانه)

سود ماهانه شرکت = تعداد تولید ماهانه * ( قیمت فروش هر کالا – قیمت تمام شده هر کالا)

 

 

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

حالا اگر تنها متغیر قابل تعیین توسط شرکت، افزایش تولید ماهانه باشد و بخواهیم ببینیم باید چه مقدار تولید ماهانه شرکت رو افزایش دهیم تا با وجود افزایش حقوق پرسنل سود شرکت مانند حالت قبل برابر 500 میلیون ریال باشه  میتونیم از ابزار Goal Seek اکسل استفاده کنیم. برای اینکار مطابق شکل زیر از تب دیتا گزینه What-If Analysis رو انتخاب میکنیم و در منوی باز شده گزینه Goal Seek رو کلیک میکنیم.

 

 

حالا در excelپنجره Goal Seek باز میشه و ما میتونیم مساله خودمون رو در اون تعریف کنیم. گزینه های این پنجره شامل موارد زیر هستند:

 

Set cell:  در این بخش متغیر وابسته خود راکه به دنبال رسیدن به مقدار مشخصی برای آن هستیم  تعریف میکنیم. در مثال ما این بخش سلول B7 هست که سود ماهانه شرکت رو نشون میده.

To value: در این بخش مقدار مورد نظر رو برای متغیر وابسته تعریف میکنیم. از آنجایی که هدف ما رسیدن به سود ماهانه دوره قبل یعنی 500 میلیون ریال است مقدار این بخش را برابر 500 میلیون قرار میدهیم.

By changing cell: در این بخش متغیری که میخواهیم با تغییر آنن مقدار متغیر وابسته را به عدد موردنظر برسانیم تعریف میکنیم. در این مثال چون ما میخواهیم با تغییر میزان تولید ماهانه سود ماهانه را افزایش دهیم مقدار این گزینه رو برابر سلول B6 قرار میدهیم.

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

 

 

اگر حقوق پرسنل افزایش پیدا کنه و مابقی متغیرها ثابت باشه، شرکت میتواند با افزایش تولید و فروش ماهانه از 100 هزار کالا به 118 هزار  کالا، سودی معادل دوره قبل یعنی 500 میلیون ریال  داشته باشد.                  امیدواریم ازاین آموزش اکسل بهره برد باشید

نمایش ممیز (/) به جای نقطه در اعداد اعشاری در اکسل

برای نمایش ممیز (/) به جای نقطه در اعداد اعشاری در اکسل سه روش وجود دارد.

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

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

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

روش سوم: تغییر نمایش علامت اعشار در کل ویندوز                                                             

---

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

مجموعا در فارسی کردن اعداد در نمودارهای اکسل (لینک ۱ و لینک ۲)، چهار نوع کد برای فارسی کردن اعداد در نمودارهای اکسل ارائه شده است. که دو مورد آن‌ها دارای عدد ۳ و دو مورد دیگر دارای عدد ۲ هستند.

[$-3010000]0.00
[$-3000401]0.00

 

[$-2000401]0.00
[$-2000000]0.00

 

اعداد نمودار اگر صحیح باشند و اعشاری نباشند تفاوتی ندارد که کدامیک از این کدها استفاده شوند ولی برای اعداد اعشاری باید از کدهایی استفاده کنید که دارای عدد ۳ باشند. با استفاده از کدهای شامل عدد ۳، چنانچه یک سری از فونت‌ها را انتخاب کنید مثلا Arial، نقطه به ممیز / تبدیل می‌شود ولی اگر از فونت‌هایی مثل Calibri یا فونت‌های فارسی سری B استفاده کنید نقطه به ویرگول (,) تبدیل می‌شود. پس اگر می‌خواهید بجای نقطه، ممیز نشان داده شود فونت اعداد باید Arial باشد. البته از فونت‌‌های دیگری نیز می‌توانید استفاده کنید که خودتان می‌توانید بررسی کنید.

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

 

---

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

در نرم افزار اکسل (نسخه ۲۰۰۷ به بعد) به مسیر زیر بروید:

File menu –> Options –> Advanced

در قسمت Editing options تیک گزینه Use system separators را بردارید و علامت / را در قسمت Decimal separator تایپ کنید.

---

روش سوم: تغییر نمایش علامت اعشار در کل ویندوز

در ویندوز به مسیر زیر بروید.

Control Panel –> Clock, Language and Region –> Change the date, time, or number format

در پنجره باز شده گزینه Additional settings را انتخاب کنید.

 

در پنجره Customize Format علامت اعشار در قسمت Decimal Symbol نشان داده شده است که می توانید علامت / را در آن تایپ کنید.

 

چگونگی ایجاد چک باکس در اکسل

ایجاد چک باکس در اکسل بسیار آسان است گزینه چک باکس در تب Developer و قسمت Tools option موجود است. چک باکس و موارد دیگر موجود در این قسمت مانند لیست کشویی، ابزار های مفیدی در هنگام طراحی یک فرم در اکسل می باشد.

این ابزار به کاربران اجازه می دهد که یک عمل را فقط با گذاشتن یک تیک انجام دهند.

در این آموزش  باماهمراه باشید

چگونه یک چک باکس در اکسل ایجاد کنیم:

 ابزار چک باکس در تب Developer موجود است اما این تب به صورت پیش فرض در اکسل پنهان می باشد جهت نمایش این تب (زبانه) میبایست مراحل زیر را دنبال کنید:

پس از باز کردن یک صفحه اکسل به ترتیب این مراحل را دنبال کنید. File > Options > Customize Ribbon tab

• سپس به صورت نمایش داده شده در شکل فوق تیک مربوط به تب Developer را بگذارید.

• پس از ظاهر شدن این تب آن را فعال کرده و با انتخاب گزینه Insert  از لیست باز شده چک باکس را انتخاب نمایید. توجه داشته باشید که از گزینه های زیر مجموعه Form Controls استفاده نمایید.
  • اکنون می توانید چک باکس خود را در هرکجای صفحه که میخواهید ترسیم نمایید
  • سپس روی چک باکس ایجاد شده کلیک راست کرده و گزینه Format Control را انتخاب نمایید.

   

  • در پنجره Format Control می توانید حالت پیش فرض را برای فعال یا غیر فعال بودن چک باکس مشخص نمایید.همچنین در قسمت Cell link میتوانید سلولی که میخواهید نتیجه فرمان تیک گذاشتن در آن نمایش داده شود را مشخص نمایید. نتیجه فعال بودن تیک عبارت True و نتیجه غیر فعال بودن آن عبارت False خواهد بود.

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

  یک لیست با عناوین اقلام انبار (Grocery Items) با یک چک باکس جلوی هر آیتم آن ایجاد شده است . این چک باکس تأیید کننده در دسترس بودن (Available) یا خارج از انبار بودن (Out of Stock) اقلام ذکر شده می باشد. به محض این که تیک مربوطه گذاشته شود عبارت در دسترس (Available) جلوی آیتم ظاهر میشود و اگر بدون تیک باشد نشان دهنده خارج از انبار بودن (Out of Stock) اقلام خواهد بود و این عبارت نشان داده خواهد شد.                                                                                    

  در ادامه طریقه انجام این کار را توضیح می دهیم.

  برای انجام این کار ابتدا باید برای هر یک از سطرها یک چک باکس به صورتی که توضیح داده شد ایجاد کنیم. و در Cell link آن را به سلول وضعیت آن (Abailability) که برای هر سطر خانه مجاور آن می باشد، مرتبط کنیم.

  به عنوان مثال برای Cell link مربوط به چک باکس سطر دوم (B2) ، میبایست سلول متناظر در ستون E انتخاب گردد. و برای بقیه سطرها نیز همین روال را میتوانید ادامه دهید. این بدان معنی است که وقتی تیک چک باکس گذاشته می شود باید عبارت True در همان سطر در ستون E نمایش داده شود و در صورتی که تیک گذاشته نشود عبارت False نشان داده شود.

  پس از انجام این کار برای ستون C می بایست از یک فرمول شرطی IF استفاده کنید. مانند فرمول زیر:

   =IF(E2=TRUE,"Available","Out of Stock")

  این فرمول بدان معنی است که در صورتی که در ستون E که خود از طریق گذاشتن و برداشتن تیک ستون B تغییر میکند، اگر عبارت True بود در نتیجه در ستون C عبارت “Available” و در غیر این صورت عبارت “Out of Stock” نمایش داده شود.

  برای مثال با گذاشتن تیک مربوط به سطر دوم در نتیجه عبارت True در ستون E ظاهر شده و همچنین با توجه به فرمول IF که قرار داده ایم در ستون C  عبارت Available ظاهر می شود.

  سپس با استفاده از فرمول COUNTIF به صورتی که در زیر نمایش داده میشود میتوانید تعداد اقلام در دسترس (Items Available) و تعداد اقلامی که در دسترس نیست (Unavailable) را مشخص کنید.همچنین تعداد کل اقلام (Total Items) نیز با استفاده از تابع ساده Count مشخص خواهند شد.

  فرمول مربوط به سلول D14 :

  =COUNT(C2:C11)

  فرمول مربوط به سلول D15 :

  =COUNTIF(C2:C11,"Available")

  فرمول مربوط به سلول D16 :

  =COUNTIF(C2:C11,"Out of Stock")

  نکات مهم:

  • برای ایجاد چک باکس در چندین سطر این کار را در یک سلول انجام دهید و سپس با استفاده از ابزار Fill تا محدوده ای که میخواهید آن را بسط دهید. فقط دقت کنید که پس از این کار میبایست Cell link هر چک باکس را جداگانه آدرس دهی کنید.
  • برای حذف همزمان چندین چک باکس میتوانید مراحل زیر را دنبال کنید.

  ‘Home’ تب> ‘Find and Select’ > ‘Go To Special’> ‘Objects’> Delete

  نکته : برای حذف چند مورد از چک باکس ها می بایست به صورت زیر عمل نمایید:

  ‘Home’ تب> ‘Find and Select’ > ‘Selection Pane’. > انتخاب موارد> deleteو

فیلتر کردن اطلاعات در اکسل

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

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

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

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

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

 

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

• گزینه Filter by Color که فیلتر بر اساس رنگ می‌باشد و با حرکت فلش ماوس بر روی این گزینه کادر زیر مجموعه‌های آن باز خواهد شد. چون از نام این فیلتر پیداست که چه کاری انجام خواهد داد مثالی از آن نخواهیم زد.
• گزینه Text Filter که برای فیلتر نمودن نوشته‌ها بکار می‌رود.

 

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

 

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

…Equals (برابر با…)

…ِDoes Not Equals (برابر نیست با…)

…Begins With  (شروع شدن با…)

…Ends With  (تمام شدن با…)

…Contains (شامل … می‌شود)

…Does Not Contain  (شامل … نمی‌شود)

…Custom Filter ( فیلتر سلیقه‌ای)

در این بخش از آموزش  میخواهیم فیلتر بر اساس حرف (س) را اعمال کنیم بنابراین به گزینه …Begins With ( شروع می‌شود با…) نیاز خواهیم داشت. با انتخاب این گزینه پنجره‌ای باز خواهد شد. در این پنجره و در کادر مشخص شده در تصویر زیر حرف (س) را وارد کنید و سپس با کلیک بر گزینه Ok فیلتر روی ستون نام خریدار اعمال میشود.

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

حال با نگاه به برگه (Sheet) مورد نظر، نتیجه کار را مشاهده خواهید کرد.

 

---

در جدول قبل می‌خواهیم بجز نام‌هایی با حرف اول (س و الف) آغاز می‌گردند، مابقی را فیلتر کنیم. برای این کار مانند قبل روی آرم Filter روی ستون مورد نظر (ستون نام‌ها) کلیک کرده و با باز شدن کادر آن گزینه Custom Filter را انتخاب کرده و در پنجره باز شده  گزینه Or را انتخاب کرده و در کادرهای سمت راست بالا و پایین حرف‌های (س) و (الف) را وارد می‌کنیم.

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

 

---

حال اگر بخواهیم بجز قیمت‌های کل بین (۵۰۰۰۰) تا (۱۰۰۰۰۰) را فیلتر کنیم، یا به عبارتی دیگر فیلتر عددی را اعمال کنیم:

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

 

…Equals (برابر با…)

…Does not Equals (برابر نبودن با…)

…Greater Than (بزرگتر از…)

…Greater Than  or Equal To  (بزرگتر یا مساوی با…)

…Less Than (کوچکتر از…)

…Less Than or Equal To (کوچکتر یا مساوی با…)

…Between (بین…)

…Top 10 (ده مورد اول… )

…Above Average (بالاتر از میانگین…)

…Below Average (پایین‌تر از میانگین…)

…Custom Filter (فیلتر سلیقه‌ای…)

برای فیلتر کردن اعداد بزرگ‌تر از ۵۰۰۰۰ تا کوچک‌تر از ۱۰۰۰۰۰ از گزینه …Between در پنجره مربوطه مانند تصویر زیر عمل می‌کنیم:

 

 

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

 

---

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

روش اول این است که از سربرگ Home به بخش Sort & Filter رفته و با کلیک بر روی این گزینه، از کادر باز شده گزینه Clear را انتخاب کنید.

 

روش دوم این است که بر روی آرم فیلتر که در بالای ستون فیلتر شده قرار دارد کلیک کرده و از کادر باز شده گزینه… Clear Filter From را انتخاب نمایید.

 

---

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

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

 

آشنایی با سه تابع Row ،Index و Column در اکسل

• تابع INDEX

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

=INDEX(شماره ستون، شماره سطر، محدوده)

=INDEX(شماره محدوده، شماره ستون، شماره سطر، یک یا چند محدوده)

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

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

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

‎=INDEX(A1:D5,3,4)‎

در حالت دوم اگر در سلولی فرمول زیر ‎ را قرار دهیم چون پارامتر آخر ما عدد 2 هست سلول موجود در سطر دوم و ستون دوم مربوط به آرایه دوم یعنی محدوده A4:D5 انتخاب می‌شود و در نتیجه قهوه در آن سلول نمایش داده می‌شود.

=INDEX((A1:D3,A4:D5),2,2,2)

اگر پارامتر آخر را به یک تغییر دهیم محدوده ما از A4:D5 به A1:D3 تغییر می‌کند و در نتیجه تابع به جای کلمه قهوه کلمه پرتقال را برمی‌گرداند.                                                                                                                                             

نکته:

۱- اگر در تابع INDEX پارامتر شماره سطر را برابر صفر قرار دهیم تابع آرایه‌ای شامل تمام سلول‌های شماره ستون مشخص‌شده را برمی‌گرداند. همینطور اگر شماره ستون را برابر صفر قرار دهیم تابع آرایه‌ای شامل تمام سلول‌های شماره سطر مشخص شده را به عنوان نتیجه برمی‌گرداند. به عنوان مثال اگر ما در سلولی فرمول زیر را در سلولی قرار دهیم، جمع تمام سلول‌های موجود در ستون سوم محدوده B1:D5 یعنی عدد 200 توسط فرمول محاسبه می‌شود.

‎=SUM(INDEX(B1:D5,0,3))‎

۲- اگر محدوده موردنظر ما دارای فقط یک سطر یا یک ستون باشد تعریف شماره سطر یا ستون برای آن آرایه اختیاری هست. به عنوان مثال اگر محدود موردنظر ما A1:D1 باشد، از آنجایی‌که این محدوده فقط یک سطر دارد نیازی به تعیین پارامتر شماره سطر نیست و می‌توانیم جای این پارامتر را در فرمول خالی بگذاریم.

تابع Column

در حالت کلی تابع Column درExcelبرای نمایش شماره ستون سلولی که به آن داده می‌شود، استفاده می‌شود که به صورت زیر نوشته می‌شود:

=Column(مرجع یا سلول مورد نظر)

مثلاً نتیجه دستور زیر عدد ۶ می‌باشد.

=COLUMN(F12)

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

• تابع Columns

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

=COLUMNS(محدوده)

بطور مثال، فرمول زیر تعداد ستون‌های موجود در محدوده انتخابی A3:C10 را نشان می‌دهد، که پاسخ آن برابر با ۳ است.

=COLUMNS(A3:C10)

تابع Row

در حالت کلی تابع Row اکسل برای نمایش شماره سطر سلولی که به آن داده می‌شود، استفاده می‌شود که به صورت زیر نوشته می‌شود:

=ROW(مرجع یا سلول مورد نظر)

مثلاً نتیجه دستور زیر عدد ۱۲ می‌باشد.

=ROW(F12)

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

• تابع Rows

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

=ROWS(محدوده)

بطور مثال، فرمول زیر تعداد سطرهای موجود در محدوده انتخابی A3:C10 را نشان می‌دهد، که پاسخ آن برابر با ۸ است.

=ROWS(A3:C10)

رنگی کردن خودکار خانه ها دراکسل

فرض كنید معلمی هستید كه می خواهید با استفاده از كاربرگ های برنامه اكسل، نمرات دانش آموزان را كنترل كنید. به این صورت كه نمرات خوب از نمرات متوسط و ضعیف به راحتی قابل تفكیك باشند. باید دید برای این امر چه كار باید بكنید؟  با این اموزش اکسل می توانید از قالب بندی شرطی یا conditional formatting در excelاستفاده نموده و تغییرات مختلفی را در ظاهر یك سلول,g (Cell)، بر اساس مقدار آن، ایجاد نمایید.
با قالب بندی شرطی قادر خواهید بود برای هر سلول، سه شرط تعیین كنید كه با فرمت فعلی آن سلول، مجموعاً چهار شرط می شود. با اعمال هر شرط، می توانید نوع فونت، خطوط حاشیه، و نوع سایه گذاری ( shading) هر سلول را تعیین كنید كه البته این تغییرات، شامل فرمت عددی و نوع چیدمان سلول نمی شود. برای استفاده از قالب بندی شرطی، باید شروطی را انتخاب كنید كه با مقادیر false و true قابل ارزیابی باشند.                                                                                                                                                    مثلا جمله "آیا عدد بزرگ تر از ۱۰ است؟" را می توان با false یا true ارزیابی كرد.
اما جمله "مقدار عدد چقدر است؟" به این روش قابل ارزیابی نیست. بدین ترتیب، اگرنتیجه شرط، true باشد، تغییرات در سلول  انجام می شود وگرنه تغییری در آن ایجاد نخواهد شد. همچنین می توان به ترتیب تا سه شرط برای یك سل تعیین كرد كه اگر شرط اول، ارزش true داشت، تغییرات انجام شده و بقیه شرط ها نادیده گرفته خواهند شد.
حال می خواهید رنگ هر سلول  را بر اساس نمره موجود در آن، تعیین كنید. بدین صورت كه خانه های دارای نمره كمتر از ۵۰ به رنگ قرمز، بین ۵۰ و ۶۵ به رنگ سبز، بین ۶۵ و ۷۵ به رنگ بنفش، و نمرات بالاتر از ۷۵، به رنگ آبی درآیند.
برای این كار، ابتدا رنگ فونت كلیه سل های حاوی نمرات را آبی كنید. سپس آن ها را انتخاب نموده و در منوی Format، روی Conditional Formating كلیك كنید. در پنجره باز شده، كادر اول را در حالت Cell Value Is باقی بگذارید. از كادر دوم، گزینه less than را انتخاب و در كادر روبه روی آن، عدد ۵۰ را تایپ كنید. سپس دكمه Format واقع در سمت راست را كلیك كرده ودر زبانه Font، رنگ قرمز را برای اعداد كمتر از ۵۰ انتخاب نمایید. با زدن OK، به كادر قبلی برگشته ودكمه Add در پایین صفحه را كلیك كنید.
می توانید تا سه شرط را برای هر سل تعیین كنید.
حالا برای تعیین شرط دوم، كادر مقابل Cell Value Is را به less than، و در كادر روبه روی آن، عدد ۶۵ را بنویسید. سپس دكمه Format را زده و در كادر باز شده، رنگ سبز را انتخاب كنید. یك بار دیگر بر روی Add كلیك كنید و شرط آخر را نیز بدین ترتیب تعیین نمایید: در كادر اول Cell Value Is، در كادر دوم less than، و در كادر روبه روی آن، عدد ۷۵ را تایپ كنید. سپس دكمه Format را كلیك كرده و برای این شرط، رنگ بنفش را انتخاب نمایید. اگر دقت كنید، می بینید كه دكمه Add بعد از اعمال سومین شرط، غیر فعال می شود. چرا كه شما نمی توانید بیش از سه شرط برای یك سل تعیین كنید. OK را بزنید و نتیجه را ملاحظه نمایید.
راه ساده تر آن است كه قالب بندی های مورد نظرتان را به صورت یكجا بر تمامی سل ها اعمال كنید. اما اگر خواستید، می توانید بااستفاده از دكمه FormatPainter، قالب های شرطی و فرمت اولیه یك سلول را به سلول های دیگر كپی نمایید.
در ضمن برای این كه در آینده بدانید قالب بندی های شرطی بر روی كدام یك از سلول های كاربرگتان اعمال شده است، از منوی Edit، گزینه Go To را كلیك و در كادر باز شده، دكمه Special را كلیك كنید. در پنجره باز شده، دكمه رادیویی Conditional Formats را علا مت زده و OK كنید. با این كار كلیه سلول  های دارای قالب بندی شرطی، با رنگ جداگانه نمایش داده می شوند.
می توانید فرمتی را روی یك سطر با توجه به تعداد یك سلول خاص، اعمال نمایید.
همچنین می توان فرمت یك ردیف را بر اساس داده های یك سلول در آن ردیف تعیین كرد. فرض كنید شماره ستون ها را در ردیف یك می نویسید، نام دانش آموزان را در ستون A ، و نمره آن ها را نیز در ستونB. كلیه سلول ها از خانه ۲ A تا آخرین آن ها در ستون B را انتخاب كنید. از مسیر Format / ConditionalFormatting در لیست اول، گزینه Formula Is و در لیست دوم، شرط ۵۰>۲= را نوشته و رنگ متن را نیز تنظیم كنید. با استفاده از دكمه Add، شروط دوم و سوم را به ترتیب ۶۵> B۲ $= و ۷۵ > ۲ B $= تعیین نمایید. برای كنترل مجدد تغییرات هر سلول، ابتدا سل A۲ را انتخاب كنید و به مسیر Format / Conditional Formatting بروید.
این سلول بر اساس مقدار موجود در سلول B۲ تغییر خواهد كرد. فرمت سل A۳ نیز براساس سلول B۳ تغییر خواهد نمود و به همین ترتیب تا آخر. بخش B$ فرمول برای مقایسه دوبه دو بین ستون ها تنظیم می شود تا بتواند تغییرات هر ردیف را به طور جداگانه انجام دهد. همچنین می توانید از شرط Formula Is برای دیگر ردیف ها نیز استفاده كنید. مثلاً بعد از انتخاب ردیف های موردنظر از فرمول های (Mod(Row(),۲= و ((Not(Mod(Row(),۲= استفاده نمایید. برای این كه به جای ردیف ها، در ستون ها تغییرات ایجاد كنید، در فرمول آن ها به جای () Row از () Column استفاده نمایید. اگر هم خواستید، می توانید در شروط ایجاد شده در ردیف های دیگر، از تغییراتی مانند رنگ و سایه گذاری نیز استفاده كنید.

استفاده از فرمول های پیچیده تر در ایجاد تغییرات شرطی، امكانات قوی تری را در اختیار شما قرار می دهد. مثلاً برای این كه در محدوده B۲:B۵۰ اعدادی كه بیش از یك بار آمده اند مشخص شوند، می توان در جلو لیست Formula Is فرمول ۱<(COUNTIF($۲:$۵۰;۲= را نوشت. برای پیدا كردن سلول حاوی بزرگ ترین مقدار (یا ردیف حاوی سلول بزرگ ترین مقدار) نیز می توان از فرمول (۵۰۲=MAX($۲:$=استفاده نمود. اگر هم خواستید فرمولتان محدوده وسیع تری را در یك ستون پوشش دهد، در این فرمول به جای ۵۰۲:$ از B:$ استفاده كنید. 

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

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

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

وقتی که می خواهید کاربرگ را انتقال و یا کپی کنید، بر روی زبانه کاربرگ راست کلیک کنید و در کادری که نمایش داده می شود گزینه move or copy را انتخاب کنید.                                                                                                                          

توجه:همچنین شما می توانید در نوار سربرگ صفحه اصلی Excel در بخش Cells، دکمه Format را انتخاب کنید. سپس، از منوی کشویی که باز می شود از قسمت Organize Sheets گزینه Move or Copy Sheet… را انتخاب کنید.

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

اگر شما در حال انتقال و یا کپی کردن برگه و یا یکی دیگر از کاربرگ های موجود هستید، قبل از آن شما می بایست برای انتقال و یا کپی برگه را در لیست برگه های قبل انتخاب کنید. انتخاب کردن (move to end) برای وارد شدن به کاربرگ بعد از تمام برگه های موجود در کاربرگ لازم است.

برای کپی کردن برگه به تنهایی روی آن کلیک کنید تا باکس Move or Copy نمایش داده شود و تیک گزینه Create a copy را بزنید و سپس Ok کنید.

 

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

 

برای کپی کردن یا انتقال کاربرگ انتخاب به کارنامه جدید روی آن کلیک کنید تا باکس Move or Copy ظاهر شود در فیلد To book  بر روی new book کلیک کنید و در آخر تیک گزینه Create a copy را بزنید و سپس Ok کنید.

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

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

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

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

برای این کار میتوانید ابتدا بازه سلولهای مورد نظر را انتخاب و در منوی insert>chart نوع چارت را تعیین کنید تا اکسل با توجه به داده های انتخابی ، نمودار مورد نیاز را ترسیم نماید. همچنین میتوانید بعد از ایجاد نمودار از منوی insert>chart ، با کلیک راست روی نمودار و انتخاب گزینه select data ، مجموعه سلولهای مورد نظر را جهت استفاده در نمودار انتخاب نمایید.

                                                                    
بازه سلولهای ورودی در نمودارهای اکسل (data range) میتواند بصورت مستقیم با انتخاب مجموعه سلولها انجام شود  و یا از بازه نامگذاری شده (named range) به عنوان داده استفاده نمود.
بازه نامگذاری شده جهت سهولت در مشخص کردن بازه سلولها در فرمول و یا نمودار به کار برده میشود، به این معنی که شما با انتخاب دسته ای از سلولهای پیوسته یا نا پیوسته، می توانید نام خاصی برای آنها در نظر بگیرید و در فرمول و یا نمودار، بجای درج محدوده سلولها ، نام اختصاص داده شده به آنها را درج نمایید.

                                                          
برای تعریف بازه نام گذاری شده ، از منوی formulas ،name manager را انتخاب نموده و در پنجره باز شده دکمه new را کلیک می کنیم.

                                                               

در پنجره جدید در کادر name نام مورد نظر را وارد کنید (در مثال شکل زیر ، نام  sales_data درج شده است)، در کادر refers to میتوانید بازه ای از سلولها را وارد نمایید( F5:G11 در شکل زیر) و یا میتوانید در این کادر از فرمول برای مشخص کردن بازه داده های named range استفاده نمایید، فرق این دو روش در این است که در حالت اول خروجی ثابتی خواهیم داشت و در حالتی که از فرمول، در کادر refers to استفاده نماییم ، خروجی ما متغییر خواهد بود و در صورتی که از این داده های نامگذاری شده به عنوان ورودی داده های نمودار (data ranges ) استفاده کنیم، نمودار ما می تواند نسبت به تغییرات عددی و حتی تعداد داده ها پویا باشد و در واقع یک نمودار داینامیک        خواهیم داشت.

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

باید مقادیر محورهای عمودی و افقی با عناوین year و sales مشخص شود بنابراین یک محدوده نامگذاری جهت محور عمودی به نام Sales_data و یک نام جهت محور افقی به نام sales_lable تعریف می نماییم و در قسمت refers to به برای نامهای فوق به ترتیب فرمولهای زیر را تعریف می کنیم:

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

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

                                                         

با کلیک روی Edit در قسمت (series) منویی  باز می شود

                                                               

در قسمت Series values نام Sales_data که قبلا تعریف کردیم (توضیحات شکل 5) درج شده است. به نحوه درج نام در این کادر توجه کنید که نام فایل اکسل یعنی dynamic_range با پسوند (.xlsx) و علامت ( ! ) قبل از نام، آورده شده است.همینطور در قسمت Categoryبا کلیک روی Edit منو  باز می شود:

                                                                   

 برای داده های مربوط به محور افقی یعنی عناوین نمودار (Axis Lable) ، نامی را که قبلا تعریف کرده اید با عنوان Sales_lable را وارد میکنیم.

 با استفاده از فرمول در قسمت data range نمودار به گونه ای طراحی شده که درصورت اضافه یا کم کردن داده ها در ستونهایyear و sales بلافاصله نمودار، با ورودی های جدید ترسیم می گردد
خروجی فرمولی که در روش فوق جهت معرفی بازه سلولها در نمودار نوشته می شود باید بازه ای از سلولها باشد. پرکاربردترین روش در این خصوص، ترکیب توابع OFFSETو COUNTA می باشد. همچنین میتوانیم از فرمولهای آرایه ای برای این منظور استفاده کنیم.                                                                              امید واریم از این آموزش اکسل لذت برده با شید

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

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

 

در excelبا استفاده از تابع Match می‌توان موقعیت یک مقدار را در یک محدوه مشخص، تعیین نمود. این محدوده باید بصورت برداری، یعنی تنها دارای یک سطر یا یک ستون باشد. البته امکان جستجو در مجموعه نیز وجود دارد. مثلا فرمول زیر حرف b را در مجموعه {a,b,c} جستجو می‌کند و موقعیت آن‌ یعنی مقدار ۲ را برمی‌گرداند، چون حرف b دومین حرف مجموعه فوق است.

=MATCH("b",{"a","b","c"},0)

ساختار تابع Match به شکل زیر است:

=MATCH (lookup_value, lookup_array,[match_type])

آرگومان اول (lookup_value): اجباری است و بیانگر عبارتی است که باید جستجو شود. این آرگومان می‌تواند عدد، رشته متنی، Logical Values (شامل صفر و یک یا True و False) یا آدرس یک سلول حاوی عبارت مورد نظر باشد.
آرگومان دوم (lookup_array): اجباری است و بیانگر محدوده‌ای است که عملیات جستجو باید در آن انجام شود. یعنی آرگومان اول قرار است با این آرگومان مقایسه شود تا در صورت تطابق، موقعیت آن بازگردانده شود.
آرگومان سوم (match_type): نوع جستجو را مشخص می‌کند، که می‌تواند یکی از حالات زیر باشد:

• اگر در این قسمت چیزی نوشته نشود یا عدد ۱ درج شود، اعدادی که در آن‌ها جستجو انجام می‌شود باید از کوچک به بزرگ مرتب شده باشد. اگر در محدوده مورد جستجو، آرگومان اول وجود نداشته باشد، تابع، شماره سطر نزدیک‌ترین عبارت کوچک‌تر از آرگومان اول را باز می‌گرداند.
• اگر عدد ۰ نوشته شود، تابع شماره سطری را که اولین تطابق رخ دهد را بر می‌گرداند. در صورتیکه مقدار موردنظر در محدوده تعیین شده یافت نشود خطای N/A# نمایش داده می‌شود.
• اگر ۱- نوشته شود، اعدادی که در آن‌ها جستجو انجام می‌شود باید از بزرگ به کوچک مرتب شده باشد. اگر در محدوده مورد جستجو، آرگومان اول وجود نداشته باشد، تابع، شماره سطر نزدیک‌ترین مقدار بزرگ‌تر از آرگومان اول را باز می‌گرداند.

نکته ۱: مرتب شدن داده‌ها بصورت صعودی یعنی از کوچک به بزرگ باید مطابق ترتیب زیر باشد:

..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

حالت نزولی برعکس ترتیب بالا است.

نکته ۲: اگر آرگومان سوم برابر با ۰ باشد و آرگومان دوم از نوع متن باشد، می‌توانید از کاراکترهای جایگزین شونده استاندارد در آرگومان اول استفاده کنید. علامت ? را می‌توان جایگزین یک کاراکتر و علامت * را می‌توان جایگزین چندین کاراکتر دانست.                                                         

 

استفاده از تکنیک What-if Analysis در تصمیم گیری

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

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

 

What-if Analysis شامل سه ابزار 

1- Data Table
2- Scenario Manager
3- Goal Seek
می باشد که در ادامه با یک مثال کاربردی به شرح هریک از آنها خواهیم پرداخت.

1- Data Table
فرض کنید با توجه به نیاز مالی خود خواهان دریافت وامی به مبلغ 100،0000،000 ریال هستیم . اکنون باید تصمیم بگیریم با توجه به شرایط خود از بین انبوهی از بانک ها ، موسسات مالی و اعتباری و موسسات قرض الحسنه باشرایط متفاوت کدام یک را انتخاب کنیم در اینجا به یکباره با همان سردرگمی که به آن اشاره شد مواجه می شویم و بدلیل کمبود زمان نیاز به تصمیم گیری سریع پیدا خواهیم کرد و Data Table کوتاه ترین مسیر برای تصمیم گیری ما خواهد بود.
بسیار خوب شروع به حل مسئله می کنیم. برای حل این مسئلهexcel تابع کلیدی PMTرا پیشنهاد می دهد که باتوجه به مبلغ وام ، نرخ بهره و مدت بازپرداخت ، مبلغ قسط ماهیانه را برای ما محاسبه می کند.

 

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

 

 

حال نوبت به استفاده از تکنیک What-if Analysis می باشد. قبل از اینکار از ابتدای بازه نرخ بهره تا انتهای بازه دوره بازپرداخت را درگ می کنیم.  سپس از سربرگ DATA قسمت DATA Tools قسمت What-if Analysis گزینه Data Table را انتخاب می کنیم .

 

 

در پنجره Data Table در قسمت Row input cell آدرس سلول نرخ بهره سالیانه (E4) و در قسمت Column input cell آدرس سلول دوره بازپرداخت (E5) را داده و بر روی  OK کلیک می کنیم .             

در ادامه  آموزش اکسل تکنیک  قدرتمند What-if Analysis  امروز به دو ابزار   Scenario Manager و Goal Seek خواهیم پرداخت.

 

  Scenario Manager :

 

یکی از محدویت هایی که در ماکروسافت اکسل وجود دارد دو بعدی بودن این نرم افزار است بصورتی که هنگام استفاده از ابزار Data Table تنها می توان تغییرات را بروی دو متغیر تابع اعمال کرد.

 

 

 

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

 

برای حل مسئله ابتدا از سربرگ DATA قسمت DATA Tools قسمت What-if Analysis گزینه Scenario Manager را انتخاب می کنیم.

 

 

 

در پنجره باز شده ناحیه ای به نام Scenarios برای ایجاد سناریو با حالت های مختلف وجود دارد و تعدادی ابزار نیز در سمت راست پنجره برای مدیریت سناریوها قرار داده شده است. برای اضافه کردن سناریو گزینه Add… را انتخاب می کنیم .

 

 

 

 در پنجره Add Scenario بعد از انتخاب نام برای سناریو در قسمت Changing cells آدرس سلول هایی که برای تغییر مد نظر داریم را وارد می کنیم در این مثال ما آدرس سلول های E3 , E4 , E5 که در تابع ما بترتیب به متغیرهای نرخ بهره سالانه ، دوره بازپرداخت و مبلغ وام اشاره دارد را وارد کرده ایم.

 

 

 

 بعد از آدرس دهی سلول های مورد نظر و تایید، در پنجره بعد نوبت به دادن مقادیر دلخواه به متغیر های انتخاب شده می رسد.

 

 

 

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

 

در Scenario Manager مانند Data Table محدودیت تعداد متغیر وجود ندارد ولی در عوض گزارشی بصورت ماتریسی نیز دریافت نخواهید کرد.

 

برای گزارش گیری از پنجره Scenario Manager بر روی گزینه Summery…  کلیک می کنیم در اینجا حق انتخاب دو نوع گزارش Scenario summery  و Scenario PivotTable Report  را داریم که اکسل هر گزارش را در شیتی جداگانه به ما می دهد.

 

در قسمت Result cells آدرس سلول پاسخ یعنی مبلغ قسط ماهانه را می دهیم.

 

 

 

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

 

 

 

 Scenario Summery :

 

 

 

 

 

 Scenario PivotTable  :

 

 

 

 

 

 Goal Seek :

 

 

 

اگر شما بخواهید با توجه به توان بازپرداخت خود وام بگیرید مثلا ماهیانه 3,000,000 ریال وبخواهید که متغیرها طوری تغییر کنند که دقیقا این مبلغ را هرماه بپردازید ماکروسافت اکسل ابزار Goal Seek را به شما پیشنهاد می کند .

 

شما کافیست مبلغ هدف را مشخص کرده و یک متغیر را اعلام کنید و Goal Seek متغیر را تا جایی که شما به مقدار هدفتان برسید تغییر می دهد. (ابزار Goal Seek تغییرات را تنها بر روی یک متغیر می تواند اعمال کند.) ما دراینجا مبلغ وام را به عنوان متغیر تعریف کرده ایم یعنی چه مقدار وام بگیریم تا هر ماه تنها مبلغ 3,000,000 ریال قسط بپردازیم.

 

سلول هدف (مبلغ قسط ماهانه) را در قسمت  Set cell ، مقدار هدف را در قسمت  To value و متغیری که قرار است تغییر کند (مبلغ وام) را در قسمت By changing cell وارد می کنیم و با زدن Ok نتیجه دلخواه ،

 

معرفی what if analyse در اکسل

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

معرفی سناریوها:

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

 

ابزارهای what if analyse شامل ابزارهای زیر میباشد .
۱- goal seek
۲- data table
۳- scenario mannager

کاربرد Goal Seek:

مثال:

برای اینکه سود ۱۰۰ ریالی بدست آوریم چند واحد محصول باید بفروشیم؟

 این ابزار از منوی Data زیر مجموعه Whats If Analysis می باشد
با این ابزار می توان بهترین حالت را برای یک مجهول تک متغیره به کار برد. در واقع در سوال مجهول ما یک مورد است و آن تعداد فروش واحد کالای مورد نیاز جهت بدست آوردن سود ۱۰۰ریالی.
نکته لازم به ذکر در استفاده از این ابزار ها این است که می بایست داده هایی که وارد می کنیم و نتیجه ای را انتظار داریم سلولها با فرمول با یکدیگر مرتبط باشند بدین منظور که نمی توان عددی را بدون فرمول وارد کرد و انتظار پاسخ داشت. یعنی باید با تغییر سلول مورد نظر ما بصورت خودکار سلول هدف ما نیز تغییر کند

 

فرضا قیمت محصول در سلول a2 برابر ۵۰ ریال باشد سپس در a3 که تعداد محصول مورد نیاز جهت فروش می باشد عددی را وارد کنیم یا صفر قرار دهیم و در خانهa4 خانه های a2 و a3 را در یکدیگر ضرب کنیم

حال از whats if analysis مورد Goal Seek را انتخاب می کنیم
در قسمت set cell خانهa4 را قرار میدهیم یعنی تنظیم شود با سود من.
در قسمت to value ارزش سود را قرار میدهیم در مثال ما ۱۰۰ ریال سود بود.
و By Changing cell خانه ای که میخواهیم تنظیم شود یعنی a2 تعداد فروش
مس از تایید مشاهده می نمایم در a2 جواب می نشیند.
لازم به ذکر است که در Goal Seek کلمهcell وجود دارد یعنی یک خانه لذا نمی توان انتظار ترکیبی از خانه ها یا چند متغیر را داشت. صرفا یک خانه و یک متغیر مجهول.

کاربرد Data Table

به ازای مقدار فروش متفاوت محصول و تقبل میزان هزینه های متغیر، چند ریال سود عملیاتی برای هر یک بدست می آورم؟

چنانچه در متن های بالاتر تشریح شده یکی از ابرازها در منوی Data از مجموعه Whats If Analysis می باشد .

با استفاده از این ابزار می توان به دو مجهول پاسخ داد لذا در دو متغیره مجهول ( در سوال فوق ، حجم فروش و هزینه) کاربرد می یابد که با ایجاد جدولی از طریق Data Table می توان سود مزبور را مشخص کرد.

در واقع ایجاد سناریو می کنیم.

ایجاد تیک و ضربدر در اکسل

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

http://www.mbaexcel.com/excel/visual-design-excel/how-to-insert-a-checkmark-symbol-in-excel

 

آموزش فرمول وی لوکاپ در اکسل

یک صفحه آموزشی بسیار مفید در زمینه آموزش اکسل:

https://www.techonthenet.com/excel/formulas/vlookup.php