نام گذاری سلولها و نواحی

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

تعریف نام ها با استفاده از Name Box

راحت ترین راه برای تعریف یک نام برای سلول مثلا C5 به این صورت است که:

1.      سلول C5 را انتخاب می کنیم.

2.      در نوار فرمول روی Name Box کلیک می کنیم.

3.      نامی را تایپ می کنیم به عنوان مثال تایپ کنید Test و سپس اینتر را بزنید.

حال هر وقت سلول C5 انتخاب شود در Name Box نام آن یعنی test دیده خواهد شد.

حرکت به نواحی نام گذاری شده

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

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

با استفاده از دستور Name که در منوی Insert موجود است می توان نواحی را نامگذاری کرد و حتی نامهای قبلی را دوباره تعریف نمود.فرض کنیم که در بالای یک ستون در سلول A1 وارد کرده ایم "کالاهای موجود " و در زیر آن نام کالاها در سلولهای A2:A10  درج خواهند شد.می خواهیم که این ناحیه را مطابق نام بالای ستون آن نامگذاری کنیم.برای این کار مراحل زیر را طی می کنیم:

1. ابتدا ناحیه A2:A10 را انتخاب می کنیم.
2. از منوی Insert گزینه Name و از آن Define را انتخاب می کنیم تا پنجره Define Name باز شود.توجه کنید که در این مثال چون ناحیه مورد نظر به یک سلول دارای داده متنی چسبیده است پس اکسل به طور خودکار نام " کالاهای موجود " را برای این ناحیه پیشنهاد می کند.چون ناحیه را نیز قبلا انتخاب کرده ایم پس نیازی به وارد کردن ناحیه در این پنجره نیست.
3. اینتر را فشار می دهیم.

نکته:   شما می توانید با فشار دادن کلیدهای Ctrl+F3 به سرعت پنجره Define Name را باز کنید.

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

تعریف Sheet-Level Names

اگر توجه کرده باشید نامهایی که تعیین می کنید در هر شیتی که باشند از شیت های دیگر قابل دسترسی هستند.یعنی اگر در شیت یک ناحیه test را نامگذاری کرده باشیم در شیت دو نیز در name box این نام موجود خواهد بود.اما اسامی را می توان طوری تعریف کرد که فقط در همان شیت قابل دسترسی باشند به این گونه اسامی  Sheet-Level Names می گویند.برای تعریف این گونه اسامی مانند دیگر اسامی عمل می کنیم منتها در هنگام تعیین نام در ابتدای نام تایپ می کنیم sheet1! مثلا برای تعریف نام exc در شیت اول باید در فیلد نام تایپ کنیم Sheet1!exc به این ترتیب نام exc فقط در شیت یک قابل رویت خواهد بود.

حالتهای کیبورد  - Keyboard mode

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

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

آقای علی زرینچه سوال فرموده اند:

- چگونه می توان اعداد ستون A و B را در ستون C در کنار هم قرار داد . مثل :

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

=B1&""&A1

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

- چگونه می توان در یک صفحه کاری بسیار گسترده که شامل اعدادی مثل ستون C در بالا دارد اعداد تکراری را پیدا کرده و آنها را حذف نمود ؟

جواب: اگر منظورتان این است که می خواهید اعداد منحصر بفرد را فقط در شیت داشته باشید ابتدا کل ستون را انتخاب کنید سپس از منوی Data گزینه filter و Advanced Filter را انتخاب کنید در پنجره باز شده دو دکمه رادیویی وجود دارد .اولی برای فیلتر کردن داده ها در همان ستون است البته داده هایی که دیده نمی شوند حذف نمی شوند بلکه فیلتر شده اند این را می توان از رنگ آبی برخی سطرها فهمید برای دیدن داده های اولیه همان مسیر قبلی را طی کرده در آخر Show All  را بزنید تا داده ها بدون فیلتر دیده شوند.اما اگر از آن دو دکمه رادیویی گزینه دوم را انتخاب کنیم این امکان را خواهیم داشت که داده ها را در یک ستون دیگر کپی کنیم حال این گزینه را انتخاب می کنیم می بینیم که یک کادر که غیر فعال بود حال فعال می شود این کادری است که باید مکان داده ها را بعد از فیلتر در آن مشخص کنیم در پایین پنجره یک تیک هست به این مضمون:Unique records only یعنی فقط داده های منحصر بفرد یعنی همان جواب مسئله ما این را نیز تیک می زنیم حال داده های اولیه در یک ستون دیگر آن هم به صورت منحصر بفرد موجودند .

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

نمی دانم چگونه می توان protection را گسترش داد validation شاید اما protection را ممکن نمی دانم.

2- ماکروهای شرطی را چطور می شود اجرا کرد ؟

اگر منظورتان این است که چگونه می توان ایجاد کرد باید عرض کنم خیلی راحت

مثلا فرض کنیم ماکرویی را ضبط کرده ایم که در آن سلول انتخابی را ایتالیک می کند.حال می خواهیم شرط بگذاریم که اگر بیش از 20 بود ایتالیک و اگر کمتر از 20 بود bold کند.با استفاده از تابع if در تغییر ماکرو به صورت زیر عمل می کنیم

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 2006/08/16 by X

'

'

   If Selection.Value < 20 Then

       Selection.Font.Bold = True

       End If

   If Selection.Value > 20 Then

      Selection.Font.Italic = True

     End If

End Sub

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

3-  آیا می توان محدوده عددی در  scroll bar   و spinner    را افزایش داد ؟ 

در حال حاضر حداقل از 0  و  حداکثر 30000 است که با این دامنه مثلا شماره چک ها را بر روی کنترلهای مزبور نمی توان بکار برد .

- دو حالت را می توان در نظر گرفت

 1- اگر همه شماره از عددی بزرگ شروع می شوند مثلا از 40000 در این صورت   می توان سلول نهایی را از جمع این عدد با عدد سلول مقصد scroll bar بدست آورد.

2- اگر شماره دامنه زیادی داشته باشند مثلا از صفر تا صد هزار در این صورت شاید راحت ترین راه استفاده از دو scroll bar باشد.

4- آیا می توان در اکسل شیت هایی از یک کتابچه را HIDE  کرد ؟

بلی .در حالی که فایل باز است ویژوال بیسیک را باز کنید و از سمت چپ نام فایل را انتخاب و آنرا باز کنید نام شیت ها را خواهید دید.حال F4 را بزنید تا پنجره دیگری باز شود.در این پنجره در آخرین ردیف visible را پیدا کرده و آنرا از 1- به صفر یا دو تغییر دهید.با این کار آن شیت hide خواهد شد.

5- چگونه می توان کنترل های فرم را در اکسل ثابت کرد ؟ بعبارتی فرم پس از آن قابلیت جابجائی بر روی شیت را نداشته باشد ؟

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

دریافت فایل

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

RepliGo Viewer .SIS package for Nokia Series 60 - 154 KB

http://www.cerience.com/downloads/getpkg?id=views60&opt=dev
RepliGo Viewer .SIS package for Symbian UIQ - 330 KB
http://www.cerience.com/downloads/getpkg?id=viewuiq&opt=dev
RepliGo Viewer .CAB file for Windows Mobile Smartphone - 180 KB
http://www.cerience.com/downloads/getpkg?id=viewsmp&opt=dev
RepliGo Viewer device installer for Windows Mobile Pocket PC - 285 KB
http://www.cerience.com/downloads/getpkg?id=viewppcarm&opt=dev
RepliGo Viewer .prc file for Palm OS 5 - 257 KB
http://www.cerience.com/downloads/getpkg?id=viewpalm5&opt=dev
RepliGo Viewer for Windows - 939 KB
http://www.cerience.com/downloads/getpkg?id=viewwin  

فایل کتاب برای موبایل:

دانلود

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

minotum@gmail.com

خدمت همه دوستان بالاخص علی آقا عرض کنم که برای دانلود وقتی ماوس را روی لینک می برید قبل از کلیک به پایین صفحه اکسپلورر سمت راست نگاه کنید.اگر آدرسی که دیده می شود در انتها pdf دارد که یعنی این لینک مستقیم به فایل است پس باید آنرا Save as target کنید به این ترتیب می توانید فایل را روی کامپیوترتان ذخیره کنید.اگر روی این گونه لینکها کلیک کنید اکسپلورر pdf را باز می کند البته سعی می کند که باز کند اما در اکثر مواقع اکسپلورر قادر به باز کردن فایل نمی باشد پس بهتر است که همان روش save as target را امتحان کنید .

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

برای دانلود اینجا کلیک کنید.

حال بهتر است به بررسی option در کادر solver بپردازیم.

Max Time: مشخص می کند که اکسل برای حل مسئله نهایتا چند ثانیه در نظر بگیرد به طور پیش فرض 100 ثانیه انتخاب شده است.اگر محاسبه بیش از این طول بکشد آخرین نتیجه در پایان 100 ثانیه جواب خواهد بود.

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

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

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

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

در پایین چهار گزینه می بینم که عبارتند از :

Assume Linear Model: معادله را خطی در نظر می گیرد

Assume Non-Negative: جوابهای غیر منفی را ارائه می دهد.می دانیم ممکن است معادله دو جواب داشته باشد یا یک جواب یا هیچ جواب.

Use Automatic Scaling: از این روش مدل سازی زمانی استفاده می شود که مقادیر ورودی و مقادیر خروجی با هم تفاوت بسیار زیادی داشته باشند.

Show Iteration Results: مراحل یافتن جواب را مرحله به مرحله نشان می دهد.

توابع تاریخ جلالی و تابع تبدیل عدد به حروف در اکسل

Solver

برای اینکه به این ابزار در اکسل دسترسی داشته باشید ابتدا باید آنرا فعال کنید.برای این کار از مسیر زیر اقدام کنید: Tools à Add–Ins  در کادر باز شده گزینه Solver را تیک بزنید. با این کار این قابلیت در اکسل فعال می شود.برای دسترسی به این قابلیت از منوی Tools گزینه Solver را که به تازگی اضافه شده است انتخاب کنید.این ابزار اکسل مانند قابلیت Goal Seek عمل می کند با این تفاوت که در اینجا سلولهایی که می توانند تغییر کنند بیش از یکی هستند و قابلیتهای بسیار بیشتری نیز دارند.

ابتدا مثالی را طرح و بعد آنرا با این ابزار حل می کنیم.

مثال 1:معادله درجه دو زیر را در نظر بگیرید:X²+4X+4=0   

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

همان گونه که مشاهده می شود متغیر x مجهول ماست.فرمول را در سلول زیر y وارد کرده ایم.حال گزینه Solver را انتخاب می کنیم تا این معادله را برای ما حل کند.

در کادر باز شده ابتدا در مقابل عبارت Set Target cell خانه ای را که قصد داریم برابر مقداری خاص باشد را انتخاب می کنیم.برای مثال ما سلول B2 در این کادر وارد می شود.در زیر آن برای عبارت Equal to از سه دکمه رادیویی مقابل آن گزینه Value of  را انتخاب کرده در کادر مقابل آن عدد صفر را درج می کنیم. در کادر بعدی برای عبارت By changing cells خانه هایی را که باید تغییر کنند تا مقدار صفر برای سلول B2 بدست آید را تعیین می کنیم در این مثال منظور خانه A1 است که همان x ما را تشکیل می دهد.حال دکمه solve را کلیک کنید تا نتیجه را در سلول مشاهده کنید.جواب این معدل -2 می باشد.در پایان حل اکسل می پرسد که آیا این جواب را اعمال کند یا نه به حالت قبل از حل برگردد .در همین کادر پایانی می توانید تعیین کنید که چه نوع گزارش هایی از این روند ارائه شود.Answer ،Sensitivity و Limits .بعدها با این گزارشها بیشتر آشنا خواهیم شد.نکته دیگر اینکه می توانید این جواب را به عنوان یک سناریو ذخیره کنید.

مثال 2 :معادله زیر را در نظر بگیرید: aX²+bX+c=Y می خواهیم ضرایب این معادله را برای X و Y خاصی بدست بیاوریم.  فرض کنیم که X=5 و Y=121 می خواهیم بدانیم ضرایب X چه اعدادی باشند تا این معادله برقرار باشد در واقع تلاش برای یافتن معادله زیر هست:   a(5)²+b(5)+c=121

داده ها را در اکسل وارد می کنیم:

Solver را اجرا می کنیم در کادر باز شده مقدار Y را به عنوان سلول هدف انتخاب می کنیم.مقدار آنرا برابر 121 درج می کنیم حال در کادر سلولهای متغیر سه سلول A2, C2, E2 را با گرفتن کلید Ctrl انتخاب می کنیم .حالSolve  را بزنید تا معادله حل شود.

با اینکه ممکن است معادله جوابهای روند داشته باشد اما لزوما همواره جواب ارائه شده همان جواب روند نیست.برای اینکه جوابها چنین اعشاری نباشند روی جوابها Limit اعمال می کنیم.دوباره Solve را اجرا کنید در قسمت Subject to the Constraints روی کلید Add کلیک می کنیم.در کادر کوچک باز شده ابتدا سلولی را که می خواهیم محدود شود انتخاب می کنیم نوع محدودیت را مشخص کرده سپس مقدار آنرا وارد می کنیم (البته برخی محدودیت ها نیازی به وارد کردن مقدار ندارند)

پس شروع می کنیم اگر بخواهیم همه جوابها اعداد صحیح باشند نه اعشاری (البته در صورت وجود چنین جوابهایی) باید ابتدا سلول A2 را در قسمت Cell Reference وارد کنیم سپس از منوی باز شدنی گزینه int را به مفهوم integer انتخاب کنیم.این محدودیت نیازی به مقدار دهی ندارد کلید Add را می زنیم تا این محدودیت اضافه شده آماده وارد کردن محدودیت بعدی می شویم .برای سلولهای C2و  E2نیز همین طور عمل می کنیم.حال شروطی را که تعیین کرده ایم در کادر یک جا می بینیم.Solve را کلیک کنید تا معادله حل شود.معادله باز هم یک جواب اعشاری دارد.برای آنکه امتحان کنیم ببینیم که آیا می شود ضریب سوم را نیز به صورت صحیح بدست آورد یا نه کادر solve را باز می کنیم.چون شروط تغییر نکرده اند پس نیازی به وارد کردن مجدد آنها نیست فقط شرط سوم را کمی اصلاح می کنیم.برای همین روی شرط مربوط به سلول E2 کلیک کرده و Change را می زنیم.شرط را از int به bin تغییر می دهیم یعنی binary (صفر و یک) دوباره معادله را solve می کنیم جوابهای بدست آمده بسیار مناسبتر از اعداد اولین مرحله حل هستند.جوابها عبارتند از :a=3  b=9  c=1

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

Go To

برای انتخاب سلولی در اعماق تاریک و نا شناخته کاربرگ راههای مختلفی وجود دارد .مثلا اگر بخواهیم سلول C302 را انتخاب کنیم یک راه این است که در name box آدرس سلول را تایپ کنیم تا یک راست به آن سلول منتقل شویم.(Name box همان کادر کوچکی است که روی سلول A1 قرار دارد) راه دیگر استفاده کردن از ابزار Go To در منوی Edit است.شاید بگویید که این راه که مشکل تر از راه قبلی است.اما این دستور اکسل در مواقع دیگری هنر نمایی می کند.فرض کنیم بخواهیم که همه سلولهای حاوی فرمول را با هم انتخاب کنیم تا مثلا رنگ آنها را تغییر دهیم.یا مثلا سلولهای حاوی متن و... را با هم انتخاب کنیم.در چنین مواقعی است که Go To به کار می آید.ابتدا از مسیر زیر کادر آنرا باز می کنیم:Edit à Go To

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

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

Constants: برای انتخاب مقادیر ثابت به کار می رود.با انتخاب این گزینه می توان تعیین کرد که چه نوع ثابتی انتخاب شود.اعداد ثابت (Number) ،متن (Text) ، روابط منطقی (Logical) و خطاها (Errors).

Formulas: سلولهای حاوی فرمول را انتخاب می کند.اما همه فرمولها نتیجه عددی ندارند.برای همین 4 حالت را می توانیم انتخاب کنیم.

 اعداد (Number)  که برای فرمولهای با نتیجه عددی به کار می رود.

متن (Text) برای فرمولهای با نتیجه متن به کار می رود.مثلا تابع CONCATENATE از سری توابع text نتیجه متنی دارد.

 روابط منطقی (Logical):سلولهایی را که حاوی فرمولهایی با نتایجی مانند True و False هستند را انتخاب می کند

 خطاها (Errors):اگر در نتیجه فرمول خطا (#) دریافت کنیم این گزینه این سلولها را انتخاب می کند.

Blanks: برای انتخاب سلولهای خالی به کار می رود.البته همه سلولها خالی محسوب نمی شوند.بلکه سلولهای خالی که بین دورترین داده تا سلول A1 قرار دارند انتخاب می شوند.مثلا اگر در یک شیت خالی در سلول C20 یک داده وارد کنید اکسل سلولهای A1 تا C20 را در نظر می گیرد.

Current Region: در سلول منتخب فعلی ، انتخاب را به سلولهای اطراف گسترش می دهد تا جایی که سطر و یا ستونی خالی عمل گسترش را محدود کند.

Current array: برای انتخاب همه سلولهایی است که توسط یک فرمول آرایه ای ایجاد شده اند.

Objects: برای انتخاب همه نمودارهای موجود در صفحه فعلی به کار می رود.

Row difference: اگر یک سطر داده داشته باشیم عدد یا متن فرقی نمی کند با این گزینه می توان سلولهایی را که داده غیر مشابه با اولین داده سطر دارند را انتخاب کرد.

مثال:در زیر سه سطر را می بینیم:

اگر تعداد سطرها بیش از یکی باشد هر سطر با سلول ابتدای خود سطر مقایسه می شود.اگر در این مثال از سلول A1 شروع به انتخاب سلول ها کنیم تا به سلول E3 سلول های ستون A مبنای مقایسه خواهند بود چون انتخاب را از آنجا شروع کردیم.

حال گزینه Row Difference را انتخاب می کنیم.در سطر اول عدد 15 مبنا است.پس سلول B1، D1و E1  چون متفاوت با سلول مبنا هستند انتخاب خواهند شد.در سطر دوم هم کلمه Excel مبنا است پس سلولهای B2،D2 وE2  انتخاب خواهند شد.

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

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

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

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

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

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

 All levels: این گزینه تمامی سلولهای مولد را انتخاب می کنید.البته برای یک سلول خاص مورد نظر.

مثال:

اگر روی سلول A4 قرار بگیریم و گزینه Precedent را در حالت

Direct only انتخاب کنیم فقط سلول A3 انتخاب خواهد شد

ولی در حالت ALL LEVELS سه خانه مولد یعنی A1,A2,A3

 انتخاب خواهند شد.

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

Last Cell: آخرین سلول را با توجه به سطر و ستون انتخاب می کند.می توان این کار را با کلیدهای Ctrl + End نیز انجام داد.

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

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

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

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

Data validation: مانند گزینه قبلی است با این تفاوت که در مورد validation به کار می رود.

Goal Seek

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

دانشجویی سه درس دارد که دو درس آنرا امتحان داده است:

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

جدول را در اکسل وارد کنید.در قسمت عدد معدل فرمول میانگین سه درس را وارد کنید.معدل فعلی با این دو نمره 16 است.حال از منوی Tools گزینه Goal Seek را انتخاب کنید.در پنجره باز شده سه کادر وجود دارد در کادر اول سلول حاوی معدل را به عنوان سلول مورد نظر انتخاب می کنیم در کادر دوم مقدار مطلوب را برای سلول مورد نظر وارد می کنیم در این مثال ما می خواهیم که معدل حداقل 17 شود پس 17 را وارد می کنیم.در کادر سوم تعیین می کنیم که کدام سلول باید تغییر کند تا این 17 بدست آید سلول خالی هندسه را انتخاب می کنیم.بعد از اینتر اکسل مقدار مورد نیاز را محاسبه می کند این دانشجو باید در هندسه 19 بگیرد.این روند را می توان در هر موردی با n پارامتر که n-1 پارامتر آن مشخص شده اند برای محاسبه پارامتر nام بکار برد.

پاسخ به دو سوال:

1- آقای علی اطمینان سوال فرموده اند که آیا می توان اکسل را طوری تنظیم کرد که ممیز را به جای نقطه با / نشان دهد؟

پاسخ:بلی .برای این کار مسیر زیر را طی کنید: Tools àoption.در کادر باز شده سربرگ international را باز کرده در کادر کوچک decimal separator نقطه را پاک کرده به جای آن / را تایپ کنید.بعد از این ممیزها را با علامت / نشان خواهد داد که البته قابلیت محاسباتی نیز دارد.

2- آقای زرینچه سوال فرموده اند که آیا می توان مثلا کاربرگی را به صورت زیر طراحی کرد که در شیت 2 موجودی انبار قرار داشته باشد و ما با وارد کردن اطلاعات فروش در شیت 1 از این موجودی انبار کاسته شود؟

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

Data àList àTotal Row

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

Scenario - 2 (سناریو)

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

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

2-  امکان دیگر summary می باشد برای رویت یک جای سناریوها برای بررسی تفاوت ها می توان از آن استفاده کرد.در آن دو نوع گزارش وجود دارد معمولی و pivot table هر کدام را که خواستید می توانید انتخاب کنید.خانه هایی را که دوست دارید وضعیت و مقدار فعلی آنها در گزارش ذکر شود در کادر وارد کنید به این ترتیب یک لیست کامل از سناریوها را خواهید دید و مقدار فعلی برای یک سلول (یا سلولها) خاص را نیز در گزارش خواهید داشت.در گزارش نواحی خاکستری رنگ مقادیری هستند که می توانند تغییر کنند در واقع متغیر هستند.

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

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

http://amar80.blogfa.com/post-91.aspx

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

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

Excel Custom Format Cell

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

ب) 0 : دقیقاً مانند # عمل می کند با این تفاوت که صفر را نشان می دهد!

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

ج) ؟ : همانند 0 و # است اما بجای هر عدد خالی و صفر یک Space قرار می دهد! وقتی استفاده می شود که بخواهیم ممیزها زیر هم قرار بگیرند. (و Align Right را هم می زنیم )

سمت راست خانه 12.         (حالت راست چین) ؟؟؟.#    ( یعنی اگر صفر آمد، نشان نده! )       

                          12.2

                         123.45   

د) , : علامت کاما ، یعنی هزار- اگر فرمت به صورت و# باشد، یعنی  به جای هزارتای اول یک و آمده و آنها را نشان نده و بقیه را نشان بده، بنابراین اعداد کمتراز1000 را نشان نخواهد داد.

برای اینکه اعداد سه رقمی، سه رقمی جدا شوند از فرمت # ,# می کنیم

N همیشه صفرها را تحت کنترل داشته باشید مانند 0 # , # یعنی اگر 0 بود 0 بگذار که البته این دستور دقیقاً معادل فرمت Number است یا فرمت 0 # # ,# همین نتیجه را دارد

ه) @ : معادل Text است که دارای موضوع همراه با علامت ; مفهوم پیدا خواهد کرد!

و) * : همراه با کاراکتر بعدیش کل فضای خالی سلول را پر می کند، مانند:

             Apple -----          apple  - * @

یعنی متن را بنویس و بقیه جای خالی سلول را با علامت ------- پر کن

ز) علامت – (under line ) : عجیباً غریبا ! باعث می شود که اندازه عرض کاراکتری که بعدش می آید فضای خالی در نظر گرفته می شود و معمولاً برای همترازی اعداد مثبت و اعداد منفی که داخل پرانتز نشان داده می شوند بکار می رود و در خانه ها به شکل (- نوشته می شود!

کار کردن با اعداد منفی – مثبت -  متن - صفر
علامت Semicolon یا ; برای اینکار استفاده میشود و حالت کلی آن از قرارداد زیر است :

حالت متن ; حالت  صفر ; حالت عدد منفی ; حالت عدد  مثبت

منظور من از  کلمه حالت همان Format یا شکل ظاهری است.

0.0 "ریال" ; [red] "Error!" ; 0 ; [color 13] "Error!"

تفسیر: یعنی اعداد مثبت را تا یک رقم اعشار، اگر هم نداشت 0 بگذار و به جای  اعداد منفی کلمه Error! را با رنگ قرمز و به جای صفر همان 0 بگذار و به جای متن کلمه Error! را با رنگ شمار 13 بنویس( مثلاً در فاکتورها.......)

IF- EISEIF- EISE

می توان برای یک خانه در Format آن حداکثر، دو شرط گذاشت.

"Average" * … 0 ;  0* ...  "High" [4=<] ; 0 ...* "Low" [2=>]

تفسیر: اگر عدد کمتر یا مساوی 2 بود کلمه Low را بنویس و خود را عدد را و بین آنها به اندازه عرض خانه Space قرار بده ، اگر عدد بزرگتر یا مساوی 4 بود کلمه High را بنویس و خود را عدد را و بین آنها به اندازه عرض خانه Space قرار بده، هر عدد که بود بجز دو شرط بالا کلمه Average را بنویس و خود را عدد را و بین آنها به اندازه عرض خانه Space قرار بده .

0000- 000 ; 0000- 000 (000) [99999999>]          شماره تلفن   

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

 فرشید میدانی farshid_mi@yahoo.com