فرمول SUMPRODUCT در اکسل

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

ساختار فرمول SUMPRODUCT و روش استفاده از آن

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

=SUMPRODUCT(list1,list2,…)

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

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

اگر شما دارای داده هایی به شکل {2,3,4} در یک لیست و {5,10,20} در لیست دیگر باشید و فرمول SUMPRODUCT را روی این لیست ها به کار ببرید نتیجه تابع عدد 120 خواهد بود ( زیرا 2*5+3*10+4*20 برابر 120 خواهد شد)

SUMPRODUCT (A1:A3,B1:B3)

=2*5+3*10+4*20

=10+30+80=120

 

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

فرمول SUMPRODUCT و آرایه ها در excel

فرض کنید شما جدولی از اطلاعات فروش دارید و ستونها با عنوان های  (نام – منطقه –  مقدار فروش) نامگذاری شده اند. شما قصد دارید بفهمید که تعداد محصولات فروخته شده توسط فروشنده ای به نام “رضا” چقدر است؟

این یک مسئله ساده است . برای این کار می توانید از یک فرمول SUMIF استفاده کنید به طوری که در آن criteria range برابر “نام” و sum range آن برابر “مقدار فروش” باشد.

اگر با فرمول SUMIF آشنایی ندارید به جمع شرطی در اکسل مراجعه نمایید.

اما اگر بخواهید مقدار فروش فروشنده ای به نام “سعید” را در منطقه غرب پیدا کنید چکار خواهید کرد؟

شما در اینجا دو راه دارید.

1. استفاده از فرمول های آرایه ای
2. استفاده از جدول محوری
   

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

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

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

فرض کنید اطلاعات فروش ما در ناحیه A1:C10 قرار داشته باشند. در ستون A اسم فروشنده، در ستون B نام منطقه و در ستون C مقدار فروش قرار دارند.

فرمول SUMPRODUCT به صورت زیر نوشته می شود.

=SUMPRODUCT(–(A1:A10=”سعید“),–(B1:B10=”غرب“),C1:C10)

توضیح روش کار فرمول

 (–(A1:A10=”سعید“)

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

(–(B1:B10=”غرب“)

این قسمت نیز عملی مشابه مرحله قبل را انجام می دهد. اما این بار روی ناحیه B1:B10 به دنبال عبارت “غرب” میگردد.

C1:C10

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

این سه قسمت باهم کار می کنند و به صورت مجزا کاری انجام نمی دهند.

 

 

 

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

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

ردیابی تغییرات در فایل های اکسل

برای انجام این کار ابتدا فایل مورد نظر را ایجاد کرده و آن را ذخیره نمایید. در سربرگ review گزینه track changes را انتخاب نمایید.

 

پس از انتخاب پنجره ای با عنوان highlight changes باز می شود. در این پنجره همه گزینه ها در حالت غیر فعال قرار دارند. تیک کنار گزینه track changes while editing. This also shares your workbook را فعال نمایید.

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

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

در زیر عنوان highlight which changes  سه گزینه با نام های when – who-where  قرار دارند. گزینه when به معنی” چه وقتی” که خود شامل تعدادی گزینه است به شما اجازه می دهد که تغییرات فایل را در زمانهای مشخصی پیگیری نمایید.

گزینه since I last saved در اکسل  تغییرات ایجاد شده بعد از آخرین ذخیره فایل را برای شما مشخص خواهد کرد. گزینه all هرگونه تغییر و در هر زمانی را مشخص می نماید. گزینه not yet reviewed  تغییراتی که تا کنون بررسی نشده اند را مشخص می کند. گزینه since date… از تاریخی که شما تعیین می کنید تغییرات را نمایش می دهد.

گزینه دیگر بخش “who”  به معنی چه کسی می باشد. در این قسمت می توانید لیست همکارانی که فایل را با آنها به اشتراک گذاشته اید ببینید و تغییرات توسط یکی و یا همه آنها را زیر نظر داشته باشد.

آخرین بخش این قسمت نیز “where” به معنی “کجا” می باشد و می توانید یک ناحیه از کاربرگ و یا کل آن را برای زیر نظر داشتن تغییرات انتخاب نمایید.

در پایین پنجره نیز دو گزینه وجود دارند. گزینه highlight changes on screen تغییرات صورت گرفته در فایل را روی خود ناحیه تغییر یافته مشخص نموده و گزینه list changes on a new sheet لیست تغییرات رادر یک صفحه  جدید برای شما مشخص می کند.

 

پس از تنظیم کادر ردیابی تغییرات، زیر عنوان track changes گزینه دیگری به نام accept/reject changes فعال می شود که با کلیک بر روی آن می توانید تغییرات را پذیرفته و یا آنها را نادیده بگیرید.

با کلیک بر روی گزینه accept/reject changes کادر دیگری ظاهر می شود که مانند کادرپنجره قبلی گزینه های when – who-where در آن قرار دارد و می توانید تغییرات دریک تاریخ مشخص، توسط  یک فرد مشخص و در یک ناحیه مشخص را پذیرفته و یا آن را نپذیرید.

 

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

 

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

 

برای رد یا قبول تغییرات بر روی  track changes و سپس accept/reject changes کلیک کرده و کلید ok را فشار می دهیم. پنجره دیگری باز شده و اولین سلول تغییر یافته و مقادیر تغییر یافته و همچنین نام کاربری که این تغییرات را ایجاد کرده نمایش داده می شود. برای قبول تغییرات دکمه accept و یا accept all و یا برای رد و عدم پذیرش دکمه reject و یا reject all را فشار می دهیم.

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

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

قالب بندی خانه ها در Excel

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

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

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

مثال برای این موارد زیاد می باشد اما بهتر است به سراغ تنظیمات قالب بندی خانه ها برویم

. برای قالب بندی خانه ها در Excel مراحل زیر را طی میکنیم :

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

سپس با یکی از روش های زیر عمل می نماییم.

 

روش اول :

1- انتخاب منوی Format از تب home
2- انتخاب گزینه Cells format

 

 

روش دوم :

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

 

روش سوم :

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

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

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

 

• General

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

• Number :

   

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

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

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

  3. – Use 1000 Separator : با انتخاب این گزینه اعداد وارد شده از سمت راست سه رقم سه رقم با علامت کاما (,) جدا می نماید .

      

• Currency: مقادیر را همراه با سمبل پول رایج نشان میدهد. در این حالت میتوان تنظیمات زیر را انجام داد :

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

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

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

      

• Accounting: مانند حالت Currency است . این قالب بندی ، قالب بندی حسابداری میباشد و علامت پولی در منتهاالهیه سمت چپ آن نوشته میشود. تفاوت عمده آن با currency در این است که بخش Negative Number در این فرمت وجود ندارد چون در حسابداری ماهیت اعداد هستند که باعث کاهندگی آن ها می شود نه منفی بودن آنها به طور مثال هزینه ها اعداد مثبتی هستند که بخاطر ماهیت آنها از درآمد کسر می شوند نه بخاطر منفی بودن آنها

   

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

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

• انتخاب منطقه جغرافیایی یا location وجود دارد و با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردد

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

   

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

   در این قالب بندی نیز 2 امکان در دسترس می باشد .

• انتخاب منطقه جغرافیایی یا location  که با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردند

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

  
  7- Percentage : عددها را همراه با علامت درصد نشان میدهد.

باامکان انتخاب تعداد اعشار در بخش decimal places

نکته

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

 

 

 

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

    

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

   باامکان انتخاب تعداد اعشار در بخش decimal places

    

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

    

10. Special در اکسل :

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

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

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

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

 

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

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

 اما مي توانيد به عنوان کاربر جديد، در هر کارپوشه (که به طور پيش فرض با نام Book1,2,3 نام گذاري شده است) سبک پيش فرض اکسل را تغيير دهيدو تنظيمات جديد را روي تمامي سلول ها و برگه ها اعمال کنيد.

نکاتی پیرامون تغيير سبک سلول ها در اکسل
 براي اين کار از منوي Format گزينه Style و سپس در منوي Style name سبک Normal را انتخاب کنيدو در ادامه دکمه Modify را برگزينيد. حال پنجره Format Cells باز خواهد شد. اکنون بايد سبک خود را با استفاده از گزينه هاي موجود در زبانه هاي اين پنجره اعمال کنيد و سپس OK را بزنيد.

خواهيد ديد اين سبک به تمام سلول ها، برگه هاي قبلي و برگه هايي که در آينده مي سازيد، اعمال مي شود. اما ممکن است براي يک سلول يک سبک را تنظيم کرده باشيد و بخواهيد که آن را ذخيره کنيد تا در آينده براي سلول هاي ديگر، از آن استفاده کنيد.

 براي اين کار ابتدا در نوار ابزار راست کليک و Customize را انتخاب کنيد. سپس در زبانه Commands  در اکسل از سمت چپ گزينه Format را انتخاب کنيد و در سمت راست نوار ابزار باز شونده، Style را به کمک ماوس در کنار نوار ابزارهاي ديگر قرار دهيد.

فرض کنيد که قصد داريد سبک سلول A1 را ذخيره کنيد؛ ابتدا سلول A1 را انتخاب و سپس در نوار ابزار Style نام سبک را تايپ کنيد و کليد Enter را بزنيد تا سبک ذخيره شود. حال مي توانيد بعد از انتخاب سلولي ديگر، سبک موجود در اين نوار ابزار را انتخاب کنيد تا روي سلول بعدي اعمال شود.

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

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

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

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

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

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

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

عملگرهای مقایسه ای: از عملگرهای مقایسه ای برای مقایسه مقادیر استفاده می شود. این عملگرها عبارتند از =، <، =<، >، => و <>. این عملگرها به ترتیب و از راست به چپ مساوی، بزرگ تر، بزرگ تر مساوی، کوچک تر، کوچک تر مساوی و نامساوی بودن دو عدد را مقایسه می کند. نتیجه حاصل از عملیات این عملگرها می تواند مثبت (درست) یا منفی (نادرست) باشد.

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

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

اولویت انجام محاسبات ریاضی: اگر با ریاضی آشنایی داشته باشید، عملگرها از اولویتی برای انجام محاسبات برخوردارند. این اولویت ها در نرم افزار اکسل نیز اجرا می شوند و در فرمول نویسی از اهمیت بالایی برخوردار هستند. اولویت ها در ریاضی به ترتیب پرانتز، درصد، توان، ضرب و تقسیم، جمع و تفریق و الحاق متن است که با علامت های زیر نشان داده می شوند:
()، ٪، ^، * /، + -، &
مثال: فرض کنید می خواهیم عبارت ۴*۲+۱۰ را به دو حالت مختلف اجرا کنیم و نتیجه را بر اساس اولویت عملگرها مشاهده کنیم. در حالت اول حاصل عبارت ۴*۲+۱۰ عدد ۱۸ خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است. اما اگر این عبارت را به صورت (۱۰+۲)*۴ بنویسیم، حاصل ۴۸ خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

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

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

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

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

توضیح کادر Insert Function: در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید. در بخش Or select a category می توانید یکی از بخش های موجود را انتخاب کنید. این بخش ها توابع اکسل را دسته بندی کرده است. مثلا اگر بخش Financial را انتخاب کنید، توابع مالی در کادر Select a function نشان داده خواهند شد.
با انتخاب گزینه All همه توابع اکسل و با انتخاب گزینه Most Recently used نیز توابعی که اخیرا استفاده کرده اید نشان داده می شوند. اکنون در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید و از بخش Select a function آن را انتخاب کنید.
پس از انتخاب تابع SUM در اکسل  بر روی دکمه OK کلیک می کنیم. در پنجره جدید که Function Arguments نام دارد، باید ورودی های تابع را وارد کنیم.

 

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

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

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

  رسم نمودار با دو محور عمودی ودو محور افقی در اکسل                                                                                                                                        داده هاوفشردن کلید  F11 روی صفحه کلیدمیباشدکه در این روش، نمودار در sheet 

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

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

 باشد وبعضی دوباره کاریها را انجام ندهیم  نمودار را مستقیماً در sheet وبا استفاده 

از زبانه insert   رسم میکنیم .پس ابتدا برای رسم نمودار باید داده هامون را به طور 

صحیح انتخاب کنیم یعنی چارچوب انتخاب داده هامون باید صحیح باشد  جزئیتر

 میگویم برای یک نمودار معمولی دو محور x ,y داریم که برای انتخاب داده ها باید

 تعداد داده های محورهای x ,y یکسان باشند و یا عقب وجلونباشند.در محور x 

متغیر های ما قرار میگیرد ودر محور y مقدار این متغیرها قرار خواهد گرفت.حال ما 

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

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

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

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

 

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

روی قسمت chart Area  یا همان زمینه نموداردوم وانتخاب copy  آن را کپی کرده وروی نمودار

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

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

مسئله روی خط یا سری نموداری که میخواهیم محورعمودی آن را به راست منتقل کنیم کلیک

کردهوسپس درزبانه  Layout  با کلیک روی Format Selection  وفعال کردن  Secondary Axis

 در وسپس با بستن پنجره ، محور عمودی سری یا نمودار انتخابی ( نمودار دوم) به راست 

منتقلخواهد شد ودو نموداربا مقدارهای خیلی متفاوت در یک نمودار به طور واضح دیده خواهند

شد.که محور y نمودار اول در سمت چپ ومحورy نمودار دوم در سمت راست قرار خواهد گرفت.

  

حالا برای اینکه محور افقی دو نمودار را جدا کنیم یعنی یکی پایین ودیگری بالا باشد به زبانه

Layout رفته وسپس به مسیز زیر بروید.

Axes / Secondary Horizontal Axis / Show Left to Right Axis

 

 

نکاتی در مورد ساخت لیست خودکار (AutoFill) در اکسل

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

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

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

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

• در پایان تمام داده‌ها به صورت خودکار درج می‌شوند.

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

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

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

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

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

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

    با انجام این مراحل پنجره‌ای مانند تصویر زیر برای شما باز خواهد شد:

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

    به عنوان مثال می‌خواهیم یک لیست جدید از حروف الفبای انگلیسی ایجاد کنیم، برای این منظور حروف الفبا را به ترتیب از حرف A تا Z در بخش List entries می‌نویسیم

     

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

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

نکاتی در مورد شرط گذاري در سلول هاي اكسل با گزينه Conditional Formatting

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

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

 

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

ابتدا ستون مورد نظر خود را انتخاب مي كنيم

 

سپس بر روي Conditional Formatting  در اکسل از تب Style كليك مي كنيم

بعد از آن بر روي گزينه Manage Rules كليك مي كنيم

 

سپس در پنجره Conditional formating rules manager بر روي گزينه New Rule كليك كنيد

 

 

 

و در انتها بر روي گزينه Ok در هر دو پنجره كليك مي كنيم تا تغييرات اعمال گردد و خواهيد ديد كه با وارد كردن شماره 1 يا بزرگتر از يك در سلول جاري آيكن سبز رنگ و با واردكردن شماره 0 يا كوچكتر از صفر در سلول جاري آيكن قرمز رنگ نمايش داده مي شود

 

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

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

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

 تغییر جهت اطلاعات در اکسل
گاهی اوقات نیاز است که اطلاعات را از حالت ستونی به سطری یا بالعکس تغییر دهیم. برای این منظور باید کل سلول‌های مورد نظر را انتخاب کرده و در جایی دیگر از فایل، گزینه زیر را انتخاب کنید.
Home->Paste->Transpose
خواهید دید که حالت سطر و ستون اطلاعات شما به صورت برعکس کپی می‌شود.

 

مخفی کردن حرفه ای اطلاعات در excel
به جز روش معمول کلیک‌راست و انتخاب Hide از منوی باز‌شده، می‌توان با روشی حرفه‌ای‌تر، اطلاعات سلول‌ها و ستون‌ها را مخفی کرد. کافی است سلول مورد نظر را انتخاب کرده و در قسمت Format Cells، تب Number را انتخاب کنید و سپس Custom را انتخاب کرده و در قسمت نام، عبارت ;;; را تایپ کنید. کلیه اطلاعات مورد نظر نامرئی می‌شوند و دوباره با تغییر فرمت سلول مورد نظر قابل بازیابی هستند

 

 انتقال اطلاعات چند سلول به یک سلول
برای این‌که اطلاعات چند سلول را در یک سلول کنار هم نشان دهیم از علامت & استفاده می‌کنیم. مثلا برای نمایش اطلاعات سلول‌های A2,B2,C2 در سلولی دیگر مثلا F2 عبارت زیر را تایپ می‌کنیم:
=A2&B2&C2

 

تغییر ساختار حروف کوچک و بزرگ
اگر بخواهیم اطلاعات انگلیسی یک سلول در سلول دیگر وارد شوند و ساختار حروف کوچک و بزرگ تغییر کند، از عبارت‌های UPPER، LOWER، و PROPER استفاده می‌کنیم. به طور مثال برای این‌که اطلاعات سلول A2 با حروف بزرگ در سلول F2 نوشته شوند، در سلول F2 می‌نویسیم: =UPPER(A2)

 

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

 

 

نکاتی در مورد قالب بندی در 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در اکسل

Text Control : شامل قسمتهای زیر است:
ü      Wrap Text : اگر اندازه متن بیشتر از سلول باشد توسط این گزینه میتوان آنرا شکست. در نتیجه ارتفاع سطر افزایش می یابد.
ü      Shrink to Fit : اگر اندازه متن از سلول بیشتر باشد با انتخاب این گزینه متن به اندازه ای کوچک می شود که در داخل سلول بگنجد.
ü      Merge Cells : اگر اندازه متن از سلول بیشتر باشد میتوان سلولهایی را که متن اشغال کرده را انتخاب نمود و سپس در کنار این گزینه تیک زد. این عمل باعث میشود که این سلول‌ها بهم پیوسته و یک سلول شوند.

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

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

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

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

لطفا با ما همراه باتشید

 

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

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

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

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

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

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

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

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

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

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

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

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

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

با ما همراه باشید تا با هم این موضوع بیشتر آشنا شویم

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

گام ۲: کل اطلاعات را انتخاب و از مسیر Insert > Line Charts > Line with Markers نوع نمودار را انتخاب نمایید.

 

گام ۳: برای اینکه نمودار ستونی را به اکسل معرفی کنیم؛ بر روی نمودار کلیک نمایید و به این آدرس بروید.

Design > Change Chart Type > Combo > Custom Combination

گام۴: برای ستون کمکی نوع نمودار را به  Clustered Column تغییر دهید. همچنین تیک چک باکس Secondary Axis را بزنید.

با این کار داده های ستونی شما  به صورت نمودار ستونی در نمودار نمایش داده خواهد شد. فقط توجه داشته باشید که نوع نمودار “ماه” و “فروش” تغییر نکرده باشد.

 

گام۵ : بر روی محور دوم دابل کلیک کنید تا تنظیمات آن باز شود.

مقدار Maximum را به مقدار مورد نظرتان تغییر دهید. در اینجا من مقدارش را به ۱۰۰ تغییر می دهیم. این مقدار برابر با مقداری باید باشد که در ستون کمکی وارد می کنیم.

همچنین بر روی Labels کلیک نمایید و Label Position را به None تغییر دهید. با این کار محور دوم نمایش داده نمی شود.

گام ۶ :  برای اینکه داشبورد و نمودار بهتری داشته باشیم می توانیم بر روی “ماه”راست کلیک نموده و آن را حذف نمایید.

 

گام۷:  برای اینکه ضخامت خط عمودی با نمودار متناسب باشد، بر روی آن دابل کلیک نمایید. مقدار Gap Width  را به ۵۰۰% تغییر دهید.

 

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

 

یک سوال؛ برای اینکه خواسته باشیم نموداری حرفه ای تر داشته باشیم تا بدون تایپ عدد نمودار میله ای را در نمودار جابجا کنیم باید چکار کنبم؟
گام 8: در ستون کمکی به این صورت فرمول نویسی نمایید که با بررسی عدد ماه  در ردیف آن عدد ۱۰۰ نوشته شود و در آن نقطه خطی عمودی در نمودار ترسیم شود.

 

برای این کار از تابع If  در اکسل استفاده کردیم. موضوعی که این تابع در شرطش بررسی می کند یکسان بودن عدد وارد شده با مقدار “ماه”  می باشد. در صورتی که برابر باشد در سلول مربوطه عدد ۱۰۰ تایپ شود در صورتی هم که برابر نبود خالی باشد.

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

 

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

 

File > Options > Customize Ribbon > Main Tabs > Developer

 

گام10:  بعد از فعال سازی تب Developer ابزار اسکرول را انتخاب نمایید.

Developer > Insert > Form Controls > Scroll Bar

گام 11:   اسکرولبار را بر روی نمودار ترسیم می کنیم. سپس بر روی آن راست کلیک نموده و Format Control را انتخاب می کنیم Maximum Value را به ۱۲ تغییر می دهیم. سلولی را هم برای مقدار Cell Link  معرفی می نماییم. تا با تغییر اسکرول بار مقدار این سلول نیز تغییر نماید.

 

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

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

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

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

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

حفاظت از اطلاعات سلولها در اکسل

1– فایل اکسل مورد نظر را باز نموده و با فشردن دکمه های Ctrl+A تمام سلولهای شیت را انتخاب کنید.
۲ – برروی سر ستون و سر سطر گوشه بالایی شیت کلیک راست نموده و گزینه Format Cells… را انتخاب نمائید

۳ – در پنجره باز شده تب Protection را انتخاب نموده و تیک گزینه های Locked و Hidden را برداشته و با فشردن دکمه OK پنجره را ببندید

۴ – حالا نوبت شماست ، سلولهایی که قصد قفل کردن آنها را دارید انتخاب نموده(عکس شماره۳) و برروی یکی از سلولهای منتخب شده کلیک راست نمائید . مجدداً گزینه Format Cells… و تب Protection را انتخاب نمائید . اگر قصد دارید سلولهای منتخب فقط قفل شوند تیک Locked را بزنید و اگر قصد دارید تا فرمول آنها مخفی شود گزینه Hidden را هم تیک کنید و با زدن دکمه Ok پنجره را ببندید.

۵ – در این مرحله در محیط اکسل بر روی تب Review کلیک نموده و گزینه Protect Sheet را انتخاب کنید

۶ – در پنجره نمایان شده تیک گزینه اول یعنی Protect Worksheet And Contents Of Locked Cells را بزنید و در قسمت بعد یعنی Allow All Users Of This Worksheet To: تیک گزینه های اول و دوم  (Select Locked Cells و Select Unlocked Cells) را بزنید و در کادر خالی Password To Unprotect sheet کلمه عبور مورد نظر خود را وارد نمایید و Ok کنید(عکس شماره۶) ، در پنجره Confirm Password To Protect مجدداً رمز عبور انتخابی را وارد و با زدن دکمهOk  مرحله را تمام کنید

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

[dropshadowbox align=”none” effect=”lifted-bottom-right” width=”autopx” height=”” background_color=”#8cccfc” border_width=”1″ border_color=”#07450e” ]اگر رمز گذاری کردید و رمز را فراموش کردید نگران نباشید و أیضاً خیلی مطمئن نباشید که سلولهای رمز شده به هیچ وجه قابل تغییر نباشند .

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

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

 

1- وقتی ورژن جدید نرم افزار Revit را نصب می کنید باید حداقل یک ماه منتظر بمانید تا آن افزونه برای نسخه جدید منتشر شود.

2-برخی افزونه ها نمی توانند فیلد هایی که به صورت Caleculated در متره رویت میسازید را انتقال دهند.

3-دسترسی به افزونه های درجه یک و بدون نقص برای ما دشوار و دارای هزینه های زیادی می باشد.

و ... .

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

1- برای اینکار ابتدا نرم افزار Power Pdf Advanced را نصب کنید.(با یک سرچ ساده در اینترنت به سادگی قابل دانلود می باشد).

2- در نرم افزار Revit از زبانه View > پانل Sheet Compostion > بروی Sheet کلیک کنید.

  

3- در پنجره باز شده برای انتخاب Sheet بروی None کلیک کنید تا وارد فضای Sheet شوید.

 

4- اکنون متره ایجاد شده را به این فضا اضافه کنید.

 

5- از طریق R > Print از آن یک پرینت با فرمت Pdf تهیه کنید.

6- اکنون این خروجی را با نرم افزار Power Pdf Advanced باز کنید.

7- در نرم افزار Power Pdf Advanced به زبانه Advanced Processing بروید.

8- از پانل Export > لیست Other Converter Tools را باز کنید.

 

9- گزینه Excel Spreadsheet را انتخاب کنید.

10- تنظیمات مربوط را انجام دهید و Ok کنید.

این فایل Pdf متره رویت شما دقیقا به صورت فایل اکسل و قابل ویرایش تبدیل می شود.

 

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

لحظه هایتان به زیباییی رسیدن به آرزو هایتان

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

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

تغییر نحوه راست چین و چپ چین کردن ستون های کاربرگ اکسل

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

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

ند.

همانطور که بیان  شد نحوه تغییر چینش سلول ها (ستون ها) بسیار ساده میباشد. برای این کار کافیست ابتدا کاربرگ مورد نظر و یا اکسل را باز کنید.

حال از سربرگ Page Layout به کاربرگ Sheet option بروید. در این کاربرگ گزینه Sheet-Right-to-Left قرار داده شده است.

 

در حالتی که کاربرگ چپ چین باشد این گزینه در حالت غیر فعال میباشد.

 

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

 

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

 

 

چگونگی ساخت Combo box با مقادیر متغیر دراکسل

ساخت Combo box یا صحیح تر بگوییم Drop Down list در Excel توسط Data Validation برای سلولها انجام می‌شود اما موضوع این مقاله ساخت Combo box ای است که کاربر بتواند به راحتی مقادیر آنرا کم یا زیاد کند و لازم نباشد که به صورت دستی این کار انجام شود به عبارت دیگر بتواند مقادیر لیست Combo box را به راحتی کنترل نماید.
قبل از هر کار ابتدا باید شما با ابزار بسیار مفیدی در Excel 2007 آشنا شوید به نام Table و بدانید که Table یک محدوده پویا ست یعنی Dynamic Range است.
منظور از محدوده پویا در Excel ، آدرس هایی است که با یک نام شناخته می شوند و شما به جای نوشتن آدرس سلول Excel از آن نام استفاده می نمایید.

 

ساخت یک محدوده پویا در Excel

 

برای ساخت یک محدوده پویا در Excel 2007  از ابزار Table استفاده می‌شود. فرض کنید که در یک شرکت شما ۵ انبار دارید که کاربر قرار است در لیست ورود کالا، یکی از انبارهای را انتخاب نماید.
مطابق شکل اسامی انبارها را در یک Sheet جداگانه از  Sheet لیست ورود انبار وارد نمایید و سپس یکی از سلولهای را کلیک کنید و کلید Ctrl+T را بزنید تا این لیست به Table تبدیل شود.

 

بعد از اینکه Table شما در Excel آماده شد باید یک Name تعریف کنید و منبع Name به ستون “نام انبارها” اشاره کند.
برای اینکار از سربرگ Formula گزینه Define Name را بزنید.
در قسمت Name یک اسم دلخواه تعیین کنید مثلا MyStock  (اسم فارسی نگذارید و از Space هم استفاده نکنید).
در قسمت Refer To با دقت بسیار سلولهایی که نام انبارها در آن نوشته شده است را با Drag موس انتخاب کنید . (سر ستون انتخاب نشود)
و در نهایت کار شما باید نتیجه ای مانند شکل را بدهد و OK را بزنید.

 

به Sheet “لیست کالا” بروید و در صورت تمایل می توانید این لیست را نیز به صورت یک Table در آورد و از مزایای Table استفاده کنید .
سلول یا سلولهای ستون “نام انبار” را انتخاب نمایید.
از سربرگ Data گزینه Validation را بزنید .
در پنجره Data Validation گزینه List را انتخاب نمایید و سپس در قسمت Source این پنجره Name تعریف شده در مرحله قبل را همراه با علامت  = وارد نمایید و کلید OK را بزنید:

 

حال در سلولهای نام یک Combo Box دارید که اسامی انبارها را نشان می دهد، اگر شما در Table “نام انبارها” گزینه ای را اضافه کنید و یا سطری را Delete نمایید متوجه می شوید که این Combo box خودکار به روز می شود .

 

نکاتی کلیدی یرامون تغییر دادن صفحه در اکسل

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

در حالت پایه، صفحات اکسل از سمت چپ به راست است. یعنی اگر به ستون های اکسل توجه کنید، حروف A و B و C و … از سمت چپ به راست است(۱). اعداد سطرهای شما در سمت چپ صفحه قرار دارند (۲). صفحات کاری شما در سمت چپ صفحه قرار دارند و ترتیب آنها از چپ به راست است(۳). همچنین اگر در صفحه کاری از دکمه Tab استفاده کنید سلول  انتخابی شما سمت چپ به راست حرکت می کند.

اما نکته مهم در این است که زبان فارسی از راست به چپ است و ما عادت داریم صفحات را از سمت راست به چپ ببینیم. برای اینکه جهت صفحات کاری خود در اکسل را تغییر بدهید کافی است به ریبون page layout بروید و گزینه sheet right to left را انتخاب کنید. در این صورت صفحات کاری شما در اکسل از راست به چپ خواهید شد.

بعد از ایجاد این تغییر در اکسل ستونی های شما از راست به چپ خواهد شد (۱). اعداد سطرهای اکسل شما در سمت راست صفحه قرار می گیرد (۲). صفحات کاری اکسل شما در سمت راست صفحه قرار دارند و ترتیب آنها از راست به چپ است(۳). همچنین اگر از Tab استفاده کنید حالت انتخاب سلول از سمت راست به چپ حرکت خواهد کرد.
 

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

تغییر تنظیمات پیش فرض برای تنظیمات راست به چپ شدن برگه ها و سندهای جدید در اکسل

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

File –> option–> advance –> display –> right to left

در اکسل خود به ریبون File بروید (۱). گزینه option را انتخاب کنید (۲). از منوی سمت چپ گزینه advance را انتخاب کنید (۳). در سمت راست پنجره به بخش display بروید و گزینه right to left را انتخاب کنید (۴).

 

 

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

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

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

قالب بندی خانه ها در Excel

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

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

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

مثال برای این موارد زیاد می باشد اما بهتر است به سراغ تنظیمات قالب بندی خانه ها برویم

. برای قالب بندی خانه ها در Excel مراحل زیر را طی میکنیم :

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

سپس با یکی از روش های زیر عمل می نماییم.

 

روش اول :

1- انتخاب منوی Format از تب home
2- انتخاب گزینه Cells format

 

روش دوم :

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

روش سوم :

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

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

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

• General

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

• Number 

   

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

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

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

  3. – Use 1000 Separator : با انتخاب این گزینه اعداد وارد شده از سمت راست سه رقم سه رقم با علامت کاما (,) جدا می نماید 

      

• Currency: مقادیر را همراه با سمبل پول رایج نشان میدهد. در این حالت میتوان تنظیمات زیر را انجام داد 

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

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

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

      

• Accounting: مانند حالت Currency است . این قالب بندی ، قالب بندی حسابداری میباشد و علامت پولی در منتهاالهیه سمت چپ آن نوشته میشود. تفاوت عمده آن با currency در این است که بخش Negative Number در این فرمت وجود ندارد چون در حسابداری ماهیت اعداد هستند که باعث کاهندگی آن ها می شود نه منفی بودن آنها به طور مثال هزینه ها اعداد مثبتی هستند که بخاطر ماهیت آنها از درآمد کسر می شوند نه بخاطر منفی بودن آنها

   

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

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

• انتخاب منطقه جغرافیایی یا location وجود دارد و با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردد

• در بخش TYPE با انتخاب منطقه جغرافیایی در بخش location انواع فرمت تاریخی در این بخش فعال شده و به دلخواه می توانید یکی از آنها را انتخاب نمایید 

   

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

   در این قالب بندی نیز 2 امکان در دسترس می باشد .

• انتخاب منطقه جغرافیایی یا location  که با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردند

• در بخش TYPE با انتخاب منطقه جغرافیایی در بخش location انواع فرمت زمان  در این بخش فعال شده و به دلخواه می توانید یکی از آنها را انتخاب نمایید 

  
  7- Percentage : عددها را همراه با علامت درصد نشان میدهد.

باامکان انتخاب تعداد اعشار در بخش decimal places

نکته

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

 

 

 

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

    

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

   باامکان انتخاب تعداد اعشار در بخش decimal places

    

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

    

10. Special

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

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

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

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

یکی از قابلیتهای قوی نرم افزار اکسل قابلیت جستجو و جایگزین نمودن متن است. با استفاده از این قابلیت شما به آسانی می توانید متن یا عددی را در یک صفحه یا بین چندین صفحه از فایل اکسل را جستجو می نماید یا می توانیم یک متن یا عدد و... را جستجو نماییم و بعد آن را با متن یا ... دلخواه جایگزین نماییم.
با استفاده از ترکیب کلید Ctrl+F نیز میتوانید وارد Find & Replace گردید.
در قسمت Find what موضوعی را که می خواهیم جستجو کنیم، تایپ می کنیم.
در صورتیکه برروی Format کلیک کنید پنجره Format Cell باز خواهد شد. و در این پنجره هرنوع فرمتی که شما قصد دارید جستجو کنید را مشخص می کنید. حتی در این قسمت شما قادر خواهید رنگ سلول را نیز جستجو کنید.
در قسمت Within نوع جستجو را تعیین میکنیم اگر Sheet را انتخاب کنیم جستجو فقط در همان کاربرگ صورت میگیرد ولی اگر Workbook را انتخاب کنیم جستجو در کلیه کاربرگهای اکسل صورت میگیرد.
در قسمت Search مشخص میکنید که عملیات جستجو در ردیفها صورت گیرد یا در ستونهای اکسل. به عبارت دیگر وقتی By Rows را فعال کنید جستجو به ترتیب ردیفها صورت میگیرد و هنگامی که جستجو در ردیف اول صورت گرفت، اکسل به دنبال ردیف دوم می رود ولی هنگامی که By Columns را فعال کنید عملیات جستجو ستون به ستون صورت می گیرد.
در قسمت Look in مشخص می کنید که عملیات جستجو در فرمولها، مقادیر عددی و یا در کامنتهای اکسل صورت گیرد.
قسمت Match case به کوچک و بزرگ بودن حروف حساس است.
اگر قسمت Match entire cell contents را فعال نکنیم جستجو در موارد مشابه نیز صورت میگیرد ولی اگر فعال کنیم دقیقاً همان چیزی را جستجو میکنیم پیدا خواهد کرد.
به عنوان مثال اگر کلمه "حساب" را جستجو کنید اگر گزینه فوق را فعال نکنید اکسل کلماتی از قبیل "حسابداری" ، حسابرسی" و "حساب" را پیدا خواهد کرد اما در صورت فعال نمودن گزینه گفته شده فقط کلمه "حساب" پیدا خواهد شد.
و اما چند نکته هنگام جستجو:
جهت جستجو میتوانید از از 2 علامت (*) و (؟) استفاده کنید. ستاره به معنی هر تعداد کاراکتر و علامت سؤال به معنی یک کاراکتر میباشد.
مثال:
حساب* در این حالت اکسل کلماتی که با حساب شروع میشود و ادامه آن هرتعداد کاراکتر باشد را پیدا خواهد کرد.
حساب؟؟ در این حالت اکسل کلماتی که با حساب شروع میشود و ادامه آن فقط دو کاراکتر دیگر خواهد بود را پیدا خواهد کرد این کلمه میتواند کلمه حسابرس باشد.
کلیه موارد Replace شبیه به Find عمل می کند.

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

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

 

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

➊ ابتدا ردیف ها و یا ستون هایی را که قصد گروه بندی آنها را دارید، انتخاب کنید. در مثال زیر ما ستونها B، C و D را انتخاب کرده ایم.



➋ ابتدا تب Data و سپس دستور Group را انتخاب کنید.



➌ ردیف ها یا ستونهای انتخاب شده تبدیل به یک گروه می شوند. در مثال ما ستونهای B، C و D تبدیل به یک گروه می گردند.

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

 

 مخفی کردن و نمایش دادن گروه ها درexcel

➊ برای مخفی کردن یک گروه بر روی علامت منها (-) که در هر گروه وجود دارد، کلیک کنید. این دکمه را دکمه مخفی کردن جزئیات نیز می نامند.



➋ گروه مخفی می شود. برای نمایش مجدد یک گروه بر روی علامت مثبت (+) که در هر گروه مخفی شده وجود دارد، کلیک کنید. این دکمه را دکمه نمایش جزئیات نیز می نامند.

ایجاد جمع های فرعی در اکسل

دستور Subtotal در اکسل، به شما امکان می دهد تا بصورت اتوماتیک یک گروه بسازید، و از توابع عمومی اکسل مانند SUM, COUNT, و AVERAGE برای خلاصه سازی اطلاعات استفاده کنید.

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

 

 ایجاد subtotal در اکسل

➊ ابتدا برگه اکسل خود را بر اساس داده هایی که قصد ایجاد subtotal برای آنها را دارید مرتب سازی کنید. در این مثال ما می خواهیم برای ستون T-shirt size این کار را انجام بدهیم، بنابراین ابتدا این ستون را مرتب سازی می کنیم.



➋ تب Data را انتخاب کرده و بر روی دستور Subtotal کلیک کنید.



➌ صفحه Subtotal نمایان می شود. بر روی کادر باز شدنی فیلد At each change in کلیک کنید، و ستونی را که قصد ایجاد subtotal برای آن را دارید انتخاب کنید. در این مثال ما ستون T-Shirt Size را انتخاب می کنیم.

➍ بر روی کادر باز شدنی قسمت Use function کلیک کنید. و تابعی را که می خواهید از آن استفاده کنید را انتخاب نمایید. در این مثال ما تابع COUNT را انتخاب می کنیم.



➎ در گزینه های قسمت Add subtotal to ستونهایی را که می خواهید subtotal برای آنها نمایش داده شود را انتخاب کنید. در این مثال ما ستون T-Shirt Size را انتخاب می کنیم. وقتی کارتان تمام شد OK کنید.



➏ برگه اکسل شما گروه بندی می شود، و در زیر هر گروه subtotal (زیر جمع) شما نمایان می شود. در این مثال، داده های ما بر اساس ستون T-shirt size گروه بندی می گردد و در زیر گروه تعداد سفارش داده شده از آن نوع تی شرت نمایش داده می شود.

مشاهده گروههای اکسل

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

➊ ابتدا بر روی کوچکترین سطح کلیک کنید تا کمترین اطلاعات ممکن را ببینید. در این مثال ما سطح 1 را انتخاب می کنیم، که تنها شامل مقدار grand count می شود که در واقع همان تعداد ستون T-shirts می باشد.



➋ بر روی سطح بعدی کلیک کنید تا جزئیاتش نمایان شود. در این مثال ما بر روی سطح 2 کلیک می کنیم، که شامل ردیف مجموع هر گروه (subtotal) می باشد، اما سایر جزئیات گروه بندی همچنان مخفی هستند.



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

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

 

 حذف subtotal ها (زیر جمع ها) در اکسل

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

➊ ابتدا بر روی تب Data کلیک کنید و سپس دستور Subtotal را کلیک کنید.



➋ پنجره Subtotal نمایان می شود. بر روی دکمه Remove All کلیک کنید.



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

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

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

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

 

 

CTRL+A

کل worksheet ای که در آن فعالیت می‌کنید را انتخاب می‌نماید.

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

CTRL+B

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

CTRL+C

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

CTRL+D

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

CTRL+F

دایالاگ باکس Find and Replace را برای جستجو و یا جایگزین کردن سلول‌ها نمایش می‌دهد. در حالت پیش فرض تب Find فعال است.

SHIFT+F5 نیز عمل CTRL+F را انجام می‌دهد. همچنین با ترکیب SHIFT+F4 می‌توان به جستجوی آخرین کلمه یا حروفی که در قسمت Find Wath  وارد کرده بودیم، بدون ظاهر شدن دایالاگ باکس Find and Replace، پرداخت.

CTRL+SHIFT+F نیز دایالاگ باکس فرمت سلول‌ها را باز می‌کند که تب پیش‌فرض Font در آن باز می‌باشد.

CTRL+G

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

F5 نیز عمل CTRL+G را نمایش می‌دهد.

CTRL+H

دایالاگ باکس Find and Replace، با تب پیش‌فرض Replace،  نمایش می‌دهد.

CTRL+I

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

CTRL+K

دایالاگ باکس Insert Hyperlink را جهت قرار دادن لینک در فایل نمایش می‌دهد.

CTRL+N

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

CTRL+O

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

با ترکیب CTRL+SHIFT+O نیز تمام سلول‌هایی که کامنت دارند انتخاب می‌شوند.

CTRL+P

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

CTRL+SHIFT+P نیز دایالاگ باکس Format Cells، با تب پیش‌فرض Font جهت تغییر فونت سلول‌ها باز می‌شود.

CTRL+R

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

CTRL+S

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

CTRL+T

دایالاگ باکس Create Table جهت ایجاد جدول نمایش داده می‌شود.

CTRL+U

توسط این شرتکات تعیین می‌کنید که زیر متن خط کشیده شود یا خیر.

CTRL+SHIFT+U نیز Formula Bar که همان نوار بالای صفحه و در مقابل fx است را جهت نمایش بهتر فرمول‌ها بزرگ و کوچک می‌کند.

CTRL+V

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

با ترکیب CTRL+ALT+V دایالاگ paste Special برایتان باز می‌شود که توسط آن می‌توانید روش Paste شدن داده‌ها را معین کنید. مثلا با زدن Column Width می‌گویید عرض ستون‌ها هنگام Paste،  همانند مبدأ باشد.

CTRL+W

در یک لحظه احتمالاً چندین محیط کاری یا همان Sheet در اکسل باز است. با زدن CTRL+W می‌توانید محیط کاری فعلی را ببندید.

CTRL+X

ناحیه‌‌ی انتخابی کات می‌شود تا برای paste  شدن در مقصد آماده باشد. در این حالت بر خلاف Copy در مبدأ چیزی باقی نمی‌ماند.

CTRL+Y

آخرین عملی که انجام داده‌ایم تکرار می‌‌شود (البته اگر ممکن باشد). فرض کنید در لحظه‌ای که فایلی جدید تشکیل می‌دهید کاری روی پرونده انجام نداده‌اید و آخرین عمل‌های خود را با undo کردن یا ترکیب CTRL+Z پاک نکرده‌اید. در این صورت تکرار عمل آخر معنی نخواهد داشت.

 CTRL+Z

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

 

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

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

ساختن شرت‌کات جدید

عموماً سه شرت‌کات در نوار بار اکسل وجود دارند.‌ در صورت دلخواه می‌توان با کلیک روی فلش رو به پایین کنار شرت‌کات‌ها، آیتم جدیدی به آن افزود. همچنین می‌توانید مسیر File>Options>Quick Acces Toolbar را طی کنید و مواردی که قصد افزودن آن را دارید از ستون چپ به راست منتقل کنید.

 

استفاده از Insert به جای Paste

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

 

روشی سریع برای انتقال سلول‌ها

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

انتقال سریع ناحیه‌ای در اکسل با درگ کردن

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

ممکن است تعدادی سلول خالی در فایل خود داشته باشید و مایل به حذف آن‌ها باشید. ستونی که می‌خواهید سلول‌های خالی آن را حذف کنید، انتخاب نمایید. سپس در تب Data و در بخش Sort & Filter روی Filter کلیک نمایید. با کلیک روی فلشی که در بالاترین سلول ستون انتخابی ایجاد شده است، کادری مطابق شکل باز می‌شود. گزینه‌ی (Blanks) را در حالت انتخاب قرار دهید تا سلول‌های خالی انتخاب شود. حال با زدن دکمه‌ی Delete آن‌ها را پاک کنید.

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

یافتن ؟ و * هم در حالت فارسی هم در حالت انگلیسی در excel

در اکسل می‌توان با زدن کلید ترکیبی Ctrl+F کلمات یا کاراکترها را جستجو کرد ولی در مورد علامت سوال و ستاره موضوع فرق می‌کند و به جای یافتن کاراکتر مورد نظر، به سلول بعدی که مقداری در آن است، منتقل می‌شوید. حال اگر بخواهید علامت سوال یا ستاره را جستجو کنید باید برحسب اینکه علامت سوال یا ستاره، فارسی یا انگلیسی است، قبل یا بعد آن از علامت ~ استفاده کنید.

جستجوی صحیح ؟ و * با شرتکات Ctrl+F

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

تیتر فوق کمی غلط انداز است. تصور نکنید که مقادیری که در مبدأ تکرار نشده‌اند را می‌خواهیم به یک ستون منتقل کنیم. بلکه هدف این است مقادیر را به مقصد که یک ستون می‌تواند باشد، (نمی‌توان مقصد را یک ردیف در نظر گرفت) منتقل کنیم طوری که داده‌ی تکراری در آن وجود نداشته باشد. در تب Data  و بخش Sort & Filter گزینه‌ی Advanced  را کلیک نمایید. در پنجره‌ی باز شده، List range مبدأ را نشان می‌دهد. حال پس از انتخاب Copy to another location  و uniqe records only در قسمت Copy to:، چند سلول یک ستون را انتخاب کنید تا داده‌های غیر تکراری در مقصد کپی شوند.

کپی مقادیر به شرطی که داده‌ها در مقصد غیر تکراری باشند

تعیین مقادیر مجاز برای یک ناحیه در EXCEL

گاهی مایلیم که برای مجموعه‌ای از خانه‌ها مقادیر مجاز تعیین کنیم تا در صورت وارد شدن داده‌‌ای خارج از دامنه هشدار داده شود. مثلاً نمره‌های وارد شده بین ۰ تا ۲۰ باشد. یک ناحیه را در فایل خود انتخاب نمایید. با رفتن به تب Data در بخش Data Tools روی Data Validation کلیک نمایید تا پنجره‌ای باز شود. با رفتن به تب  Setting در قسمت Allow نوع داده‌ای و در قسمت Data نیز روشی که مایلید مقدار داده‌ها را غیر مجاز اعلام کنید، مشخص کنید. حال فیلد  یا فیلدهای دیگری بر حسب انتخابتان ایجاد می‌شود که باید مقادیر را در آن وارد نمایید. دو تب دیگر در این پنجره وجود دارد. اولی با نام Input Message برای نمایش پیغام در صورت ورود به ناحیه و دومی با نام Error Alert، به منظور اعلام پیام هشدار، در صورت غیر مجاز بودن داده وجود دارد.

مطالبی ارزنده پیرامون کپی کردن جدول SQL در Excel

پایگاه داده یک سیستم کامل دیتابیس است که توسط شرکت مایکروسافت ارائه می گردد.  SQL Server یک پایگاه داده است که شا می توانید به عنوان یک مخزن داده از آن برای وب سایت ، برنامه و … استفاده کنید.

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

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

کپی کردن جدول SQL در Excel

1- ابتدا SQL Server Management را باز کنید و به سرور دیتابیس خود متصل شوید.

2- سپس بر روی جدول مورد نظر کلیک راست کرده و گزینه Select Top 1000 Rows را انتخاب نمایید.

3- با کلیک بر روی گوشه سمت چپ جدول تمام داده‌ها را انتخاب نمایید.

 

4- بر روی جدول کلیک راست کرده و گزینه Copy را انتخاب نمایید.

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

 

5- سپس یک سند اکسل ایجاد نمایید.

6- بر روی آن کلیک راست کرده و گزینه Paste را همانند تصویر انتخاب نمایید.

برای Paste کردن در فایل اکسل ، می توانید از کلیدهای ترکیبی Ctrl + V استفاده نمایید.

 

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

 

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

امیدواریم آموزش برای شما مفید واقع شده باشد.

نکاتی در مورد کاربرد انواع what-if analysis در اکسل

یکی از کاربرد های پرکاربرد اکسل انجام محاسبات پیچیده ریاضی میباشد، یکی از این ابزارها تجزیه و تحلیل what-if می باشد. این ابزار به شما کمک می کند، تا سوالها و پاسخهایی را با داده های خود آزمایش کنید، حتی در زمانیکه داده های شما تکمیل نشده باشند هم این ابزار کار می کند. در این درس، شما یاد خواهید گرفت که چگونه از ابزار تجریه و تحلیل what-if اکسل استفاده کنید، نام این ابزار Goal Seek (جستجوی هدف) می باشد.

 

ابزار Goal Seek در اکسل

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

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

فرض کنیم شما در یک کلاس ثبت نام کرده اید. نمره شما در حال حاضر 65 می باشد، و شما حداقل به نمره 70 نیاز دارید تا بتوانید آن کلاس را بگذرانید. خوشبختانه، شما یک امتحان نهایی دارید که می توانید به واسطه آن میانگین نمراتتان را بالاتر ببرید. شما می توانید از ابزار Goal Seek اکسل استفاده کنید تا متوجه شوید، در امتحان نهایی شما به چه نمره ای نیاز دارید تا بتوانید آن کلاس را با موفقیت بگذرانید (یا اصطلاحا پاس کنید).

در تصویر زیر شما نمرات 4 آزمون اول را می بینید. این نمرات 58، 70، 72 و 60 می باشند. اگر چه ما هنوز نمی دانیم نمره آزمون پنجم شما چه شده است، با این وجود می توانیم فرمولی (یا تابعی) بنویسیم که نمره آزمون نهایی شما را محاسبه کند. در این وضعیت، هز آزمون به یک اندازه در فرمول ما وزن خواهد داشت، بنابراین آنچه ما نیاز داریم اینست که میانگین 5 آزمون اول را با نوشتن دستور زیر محاسبه کنیم.

=AVERAGE(B2:B6)

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



➊ سلولی را که قصد ویرایش مقدارش را دارید انتخاب کنید. هر وقت که شما از ابزار Goal Seek اکسل استفاده می کنید، باید سلولی را که دارای یک فرمول یا یک تابع می باشد را انتخاب کنید. در این مثال ما سلول B7 را انتخاب می کنیم چون فرمول زیر داخل آن نوشته شده است :

=AVERAGE(B2:B6)

➋ در تب Data بر روی دستور What-If Analysis کلیک کنید و سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره یا سه فیلد ظاهر می شود. اولین فیلد که Set cell می باشد، شامل نتایج مطلوب (ایده آل) می باشد. در مثال ما سلول B7 برای این مورد انتخاب شده است.

فیلد دوم To value نتیجه مورد نظر می باشد. در مثال ما مقدار 70 را در آن وارد می کنیم، زیرا ما باید در نهایت 70 نمره کسب کنیم تا بتوانیم این کلاس را پاس کنیم.

فیلد سوم By changing cell، سلولی است که ابزار Goal Seek پاسخ را در آن نمایش خواهد داد. در این مثال ما سلول B6 را انتخاب می کنیم، زیرا می خواهیم نمره مورد احتیاج ما در آزمون نهایی در آنجا نمایش داده شود.

➍ وقتی این کارها را انجام دادید ok کنید.



➎ اگر ابزار Goal Seek قادر باشد نتیجه را برای شما محاسبه کند، این موضوع را به شما اطلاع می دهد. ok را کلیک کنید.



➏ نتیجه در سلولی که برای نمایش نتیجه مشخص کرده اید، ظاهر می شود. در مثال ما، ابزار Goal Seek به ما نشان می دهد که برای پاس کردن کلاس باید در آزمون نهایی نمره 90 بگیریم.

روش استفاده از ابزار Goal Seek در اکسل

فرض کنیم شما مشغول برنامه ریزی برای یک مراسم هستید، و می خواهید تا جای ممکن افراد بیشتری را دعوت کنید، البته بودجه شما برای این مراسم 500 دلار می باشد. ما می توانیم از ابزار Goal Seek اکسل استفاده کنیم تا ببینیم، چند نفر را می توانیم دعوت کنیم. در مثال زیر، سلول B5 شامل فرمول زیر می باشد که مجموع هزینه رزرو اتاق به اضافه هزینه هر شخص می باشد.

=B2 B3*B4

➊ ابتدا سلولی را که می خواهید مقدارش را تغییر بدهید، انتخاب نمایید. در این مثال ما سلول B5 را انتخاب می کنیم.



➋ در تب Data ، بر روی دستور What-If Analysis کلیک کنید، سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره با سه فیلد نمایان می شود. فیلد اول Set cell حاوی نتایج مطلوب است. در مثال ما سلول B5 انتخاب شده است.

فیلد دوم To value شامل نتیجه لازم است، ما مقدار 500 را در آن وارد می کنیم زیرا فقط می خواهیم 500 دلار خرج کنیم.

فیلد سوم By changing cell محلی است که ابزار Goal Seek نتایج محاسبه شده را در آن نمایش خواهد داد. در این مثال، ما سلول B4 را انتخاب می کنیم، زیرا می خواهیم نتیجه اینکه ما چند مهمان را می توانیم دعوت کنیم، بدون اینکه بیش از 500 دلار خرج کنیم، در آنجا نمایش داده شود.

➍ وقتی کارتان تمام شد ok کنید.



➎ اگر ابزار Goal Seek قادر به محاسبه نتایج باشد، در این پنجره به شما خبر میدهد. ok کنید.

➏ نتایج محاسبه شده در سلول مربوطه نمایان می شود. در مثال ما، ابزار Goal Seek محاسبه کرده است که پاسخ تقریبا 18.62 می باشد. از آنجا که در این مورد خاص پاسخ ما باید یک عدد صحیح باشد، پس ما نیاز داریم تا این عدد را به سمت بالا و یا به سمت پایین گرد (رند) کنیم. و طبیعتا چون بودجه ما 500 دلار است و نمی خواهیم بیش از آن هزینه کنیم این عدد را به سمت پایین گرد می کنیم تا نتیجه تبدیل به 18 گردد.

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

 

 انواع what-if analysis در اکسل

در پروژه های خیلی پیشرفته تر ممکن است از انواع دیگر دستور what-if analysis استفاده نمایید. این انواع شامل گزینه های scenarios و data tables می باشند. جای اینکه مشابه ابزار Goal Seek از نتایج مطلوب شروع کنیم و رو به عقب برگردیم، این موارد گزینه هایی به شما می دهند که مقادیر مختلف را آزمایش کنید و تاثیرش را در نتیجه نهایی ملاحظه کنید.

• Scenarios : این گزینه به شما امکان می دهد تا مقادیر سلولهای متعددی را (تا سقف 32 سلول)، هم زمان جایگزین کنید. شما می توانید سناریوهای مختلفی را با این ابزار بسازید و آنها را با هم مقایسه کنید و در عین حال از تغییر دادن دستی مقادیر پرهیز کنید. در تصویر زیر، ما از سناریوهایی استفاده می کنیم تا سالن های مختلف را برای یک مراسمی که در پیش رو داریم، با هم مقایسه کنیم.
  
• Data tables : این گزینه به شما این امکان را می دهد که یک یا دو متغیر در یک فرمول را در نظر بگیرید و آنها را با مقادیر متفاوت دیگری که می خواهید جایگزین کنید، و سپس نتایج را به شک یک جدول مشاهده کنید. این گزینه بسیار قدرتمند می باشد زیرا برخلاف دو ابزار قبلی، می تواند نتایج چندگانه ای را همزمان به شما نشان می دهد. در تصویر زیر ما می توانیم 24 نتیجه ممکن را برای یک وام خودرو مشاهده نماییم.
  

 

 

 

 

نکاتی پیرامون انجام کارهای پرکاربرد با جدول گردشی (PivotTables)

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

 

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

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

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

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



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



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



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

برش دهنده ها (Slicers) درexcel

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

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

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

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


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



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



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

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

 

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

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

روش ایجاد یک PivotChart در اکسل

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

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



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



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

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

نکاتی در مورد ساختار جداول چرخشی (PivotTables) در اکسل

استفاده از ویژگی PivotTables اکسل

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



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


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

روش ایجاد یک PivotTable در اکسل

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



➋ ابتدا تب Insert را انتخاب کرده و سپس بر روی دستور PivotTable کلیک کنید.



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



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



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



➏ فیلدهای انتخاب شده توسط شما به یکی از 4 ناحیه زیر اضافه می شوند. در مثال ما، فیلد Salesperson به ناحیه Rows (ردیف ها) اضافه می شود و فیلد Order Amount به ناحیه Values (مقادیر) اضافه می گردد. البته شما خودتان نیز امکان چرخش و تغییر محل این فیلدها را دارید. کافیست فیلدها را با ماوس و روش نگهداشتن و کشیدن در بین این 4 ناحیه جابجا کنید.



➐ ویژگی PivotTable فیلدهای انتخاب شده را محاسبه و خلاصه سازی می کند. در این مثال، PivotTable جمع مبلغ فروش هر فروشنده را به صورت جداگانه به ما نمایش می دهد.

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

 

اگر هر کدام از سلولهای اکسل را که در PivotTable استفاده شده است را تغییر بدهید، بصورت اتوماتیک PivotTable شما هم متناسب با آن تغییر بروز رسانی می شود. برای اینکه بصورت دستی بتوانید PivotTable را بروز رسانی کنید می توانید از Analyze و سپس Refresh استفاده کنید.

 

چرخش داده ها در PivotTables اکسل

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

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

تا حالا، PivotTable ما می توانست تنها یک ستون را در یک زمان نمایش دهد. برای اینکه بتوانیم چندین ستون را همزمان نمایش بدهیم، باید به ناحیه Columns (ستونها) فیلدهایی را اضافه کنیم.

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



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

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

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

➊ برای حذف هر فیلد آن را با ماوس بکشید و به ناحیه ای خارج از ناحیه خودش ببرید. همچنین برای حذف یک فیلد می توانید در قسمت فیلدها تیک آن را بردارید. در این مثال ما فیلدهای Month و Salesperson را حذف می کنیم.



➋ حالا یک فیلد جدید را به ناحیه مورد نظرتان بکشید. در این مثال ما فیلد Region را به ناحیه Rows می کشیم.



➌ جدول PivotTable ما تنظیم می شود (یا می چرخد) تا داده های جدید را نمایش بدهد. در این مثال هم اکنون ما می توانیم جمع مبلغ فروش مربوط به هر ناحیه را مشاهده کنیم.

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

در نرم افزار اکسل از پرکاربردترین عملگرها در اکسل، عملگر شرط (IF) است. این عملگر برای پردازش اطلاعات کاربردهای زیادی دارد.

 

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

 

 استفاده از عملگرهای شرطی در اکسل

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

 

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

 

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

 

{IF(AND(B3:B7 > 0) ; SUM(B3:B7) ; "")}

 

 

همان طور که می‌دانید نیازی نیست هنگام وارد نمودن یک فرمول آرایه‌ای کاراکترهای {} را تایپ کنید، ابتدا فرمول را وارد کرده و سپس کلیدهای Ctrl+Shift+Enter را فشار دهید.

 

به این ترتیب میتوانید از تکرار AND در فرمول های اکسل جلوگیری کنید.                                                 ترکیب اطلاعات چند سلول در یک سلول در اکسل                                                                             

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

 

ابتدا سلول مورد نظر جهت وارد کردن چندین متن را انتخاب کرده و کد زیر را در آن تایپ کنید (ممکن است که در سایت کد اشتباه نمایش داده شود، = در اول کد است):

 

=CONCATENATE(

 

 

بعد از وارد کردن کد، باید سلول های مود نظر را به ترتیب جهت وارد کردن متن بزنید. برای مثال نام در ستون B وجود دارد و نام خانوادگی در ستون A است. بنابر این باید به ترتیب B2 و A2 را وارد کنید.

 

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

 

=CONCATENATE(B2,A2)

 

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

 

 

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

 

=CONTACENATE(B2,””,A2)

 

 

 

 

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

 

 

 

 

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

 

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

 

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

 

همچنین در صورت تمایل برای جدا کردن نام سلول ها از هم میتوانید از & به جای , استفاده کنید. همانند این نمونه:

 

=CONCATENATE(B2,””,A2)

 

=CONCATENATE(B2&””&A2)

                                          

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

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

 

بدین منظور:

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

 چاپ سرستون‌ها در تمام صفحات در اکسل                                                                                                                                                                                        اگر در محیط اکسل بر روی فایلی کار کنید که دارای سرستون (Header) باشد و تعداد صفحاتی که قصد چاپ کردن آن‌ها را دارید بیش از 1 صفحه‌ی کاغذ باشد حتماً متوجه این نکته شده‌اید که سرستون تنها در صفحه‌ی اول چاپ می‌شود و در صفحات بعدی تنها اطلاعات هستند که چاپ می‌شوند. به عنوان مثال اگر فایلی شامل اطلاعات شخصی افراد در اختیار داشته باشید و در سطر اول عنوان‌های هر ستون درج شده باشد (به عنوان مثال «نام»، «نام خانوادگی»، «شماره ملی» و...)، این عناوین در صفحات بعدی چاپ نخواهند شد. این موضوع چندان مناسب به نظر نمی‌رسد. در این آموزش قصد داریم روشی را بازگو کنیم که از این پس می‌توانید به آسانی سرستون‌ها را در تمام صفحات چاپ کنید.

 

 

 

بدین منظور:

 

ابتدا در محیط Excel فایل مورد نظر را فراخوانی کنید.
اکنون به تب Page Layout رفته و در محدوده‌ Page Setup بر روی Print Titles کلیک کنید.

 

در پنجره‌ باز شده در قسمت Rows to repeat at top عبارت زیر را وارد کنید:

 

$1:$1

 

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

 

 

مطاتلبی ارزنده پیرامون تجزیه و تحلیل داده ها با What-if در اکسل

اکسل برای انجام محاسبات پیچیده ریاضی ابزارهای قدرتمندی دارد، یکی از این ابزارها تجزیه و تحلیل what-if می باشد. این ابزار به شما کمک می کند، تا سوالها و پاسخهایی را با داده های خود آزمایش کنید، حتی در زمانیکه داده های شما تکمیل نشده باشند هم این ابزار کار می کند. در این درس، شما یاد خواهید گرفت که چگونه از ابزار تجریه و تحلیل what-if اکسل استفاده کنید، نام این ابزار Goal Seek (جستجوی هدف) می باشد.

 

 

ابزار Goal Seek در اکسل

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

روش استفاده از ابزار Goal Seek در اکسل:

فرض کنیم شما در یک کلاس ثبت نام کرده اید. نمره شما در حال حاضر 65 می باشد، و شما حداقل به نمره 70 نیاز دارید تا بتوانید آن کلاس را بگذرانید. خوشبختانه، شما یک امتحان نهایی دارید که می توانید به واسطه آن میانگین نمراتتان را بالاتر ببرید. شما می توانید از ابزار Goal Seek اکسل استفاده کنید تا متوجه شوید، در امتحان نهایی شما به چه نمره ای نیاز دارید تا بتوانید آن کلاس را با موفقیت بگذرانید (یا اصطلاحا پاس کنید).

نمرات 4 آزمون اول را می بینید. این نمرات 58، 70، 72 و 60 می باشند. اگر چه ما هنوز نمی دانیم نمره آزمون پنجم شما چه شده است، با این وجود می توانیم فرمولی (یا تابعی) بنویسیم که نمره آزمون نهایی شما را محاسبه کند. در این وضعیت، هز آزمون به یک اندازه در فرمول ما وزن خواهد داشت، بنابراین آنچه ما نیاز داریم اینست که میانگین 5 آزمون اول را با نوشتن دستور زیر محاسبه کنیم.

=AVERAGE(B2:B6)

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



➊ سلولی را که قصد ویرایش مقدارش را دارید انتخاب کنید. هر وقت که شما از ابزار Goal Seek اکسل استفاده می کنید، باید سلولی را که دارای یک فرمول یا یک تابع می باشد را انتخاب کنید. در این مثال ما سلول B7 را انتخاب می کنیم چون فرمول زیر داخل آن نوشته شده است :

=AVERAGE(B2:B6)

➋ در تب Data بر روی دستور What-If Analysis کلیک کنید و سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره یا سه فیلد ظاهر می شود. اولین فیلد که Set cell می باشد، شامل نتایج مطلوب (ایده آل) می باشد. در مثال ما سلول B7 برای این مورد انتخاب شده است.

فیلد دوم To value نتیجه مورد نظر می باشد. در مثال ما مقدار 70 را در آن وارد می کنیم، زیرا ما باید در نهایت 70 نمره کسب کنیم تا بتوانیم این کلاس را پاس کنیم.

فیلد سوم By changing cell، سلولی است که ابزار Goal Seek پاسخ را در آن نمایش خواهد داد. در این مثال ما سلول B6 را انتخاب می کنیم، زیرا می خواهیم نمره مورد احتیاج ما در آزمون نهایی در آنجا نمایش داده شود.

➍ وقتی این کارها را انجام دادید ok کنید.



➎ اگر ابزار Goal Seek قادر باشد نتیجه را برای شما محاسبه کند، این موضوع را به شما اطلاع می دهد. ok را کلیک کنید.



➏ نتیجه در سلولی که برای نمایش نتیجه مشخص کرده اید، ظاهر می شود. در مثال ما، ابزار Goal Seek به ما نشان می دهد که برای پاس کردن کلاس باید در آزمون نهایی نمره 90 بگیریم.

روش استفاده از ابزار Goal Seek در اکسل  :

فرض کنیم  مشغول برنامه ریزی برای یک مراسم هستید، و می خواهید تا جای ممکن افراد بیشتری را دعوت کنید، البته بودجه شما برای این مراسم 500 دلار می باشد. ما می توانیم از ابزار Goal Seek اکسل استفاده کنیم تا ببینیم، چند نفر را می توانیم دعوت کنیم. در مثال زیر، سلول B5 شامل فرمول زیر می باشد که مجموع هزینه رزرو اتاق به اضافه هزینه هر شخص می باشد.

=B2+B3*B4

➊ ابتدا سلولی را که می خواهید مقدارش را تغییر بدهید، انتخاب نمایید. در این مثال ما سلول B5 را انتخاب می کنیم.



➋ در تب Data ، بر روی دستور What-If Analysis کلیک کنید، سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره با سه فیلد نمایان می شود. فیلد اول Set cell حاوی نتایج مطلوب است. در مثال ما سلول B5 انتخاب شده است.

فیلد دوم To value شامل نتیجه لازم است، ما مقدار 500 را در آن وارد می کنیم زیرا فقط می خواهیم 500 دلار خرج کنیم.

فیلد سوم By changing cell محلی است که ابزار Goal Seek نتایج محاسبه شده را در آن نمایش خواهد داد. در این مثال، ما سلول B4 را انتخاب می کنیم، زیرا می خواهیم نتیجه اینکه ما چند مهمان را می توانیم دعوت کنیم، بدون اینکه بیش از 500 دلار خرج کنیم، در آنجا نمایش داده شود.

➍ وقتی کارتان تمام شد ok کنید.



➎ اگر ابزار Goal Seek قادر به محاسبه نتایج باشد، در این پنجره به شما خبر میدهد. ok کنید.

➏ نتایج محاسبه شده در سلول مربوطه نمایان می شود. در مثال ما، ابزار Goal Seek محاسبه کرده است که پاسخ تقریبا 18.62 می باشد. از آنجا که در این مورد خاص پاسخ ما باید یک عدد صحیح باشد، پس ما نیاز داریم تا این عدد را به سمت بالا و یا به سمت پایین گرد (رند) کنیم. و طبیعتا چون بودجه ما 500 دلار است و نمی خواهیم بیش از آن هزینه کنیم این عدد را به سمت پایین گرد می کنیم تا نتیجه تبدیل به 18 گردد.

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

 

سایر انواع what-if analysis در اکسل

در پروژه های خیلی پیشرفته تر، شما ممکن است از انواع دیگر دستور what-if analysis استفاده نمایید. این انواع شامل گزینه های scenarios و data tables می باشند. جای اینکه مشابه ابزار Goal Seek از نتایج مطلوب شروع کنیم و رو به عقب برگردیم، این موارد گزینه هایی به شما می دهند که مقادیر مختلف را آزمایش کنید و تاثیرش را در نتیجه نهایی ملاحظه کنید.

• Scenarios : این گزینه به شما امکان می دهد تا مقادیر سلولهای متعددی را (تا سقف 32 سلول)، هم زمان جایگزین کنید. شما می توانید سناریوهای مختلفی را با این ابزار بسازید و آنها را با هم مقایسه کنید و در عین حال از تغییر دادن دستی مقادیر پرهیز کنید. در تصویر زیر، ما از سناریوهایی استفاده می کنیم تا سالن های مختلف را برای یک مراسمی که در پیش رو داریم، با هم مقایسه کنیم.
  
  
• Data tables : این گزینه به شما این امکان را می دهد که یک یا دو متغیر در یک فرمول را در نظر بگیرید و آنها را با مقادیر متفاوت دیگری که می خواهید جایگزین کنید، و سپس نتایج را به شک یک جدول مشاهده کنید. این گزینه بسیار قدرتمند می باشد زیرا برخلاف دو ابزار قبلی، می تواند نتایج چندگانه ای را همزمان به شما نشان می دهد. در تصویر زیر ما می توانیم 24 نتیجه ممکن را برای یک وام خودرو مشاهده نماییم.
  
  

 

 

 

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

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

ترکیب کردن رشته های متنی یکی از رایجترین کارها برای کاربران اکسل است اما تا به امروز اگر شما می خواستید رشته های متنی موجود در یک محدوده ای از سلول ها را ترکیب کنید مجبور بودید تک تک سلول ها را به تنهایی انتخاب کنید. توابع جدید 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], ...)

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