سلام

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

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

2-     سرقت – زمانی که متوجه شدم کتاب آموزش اکسل  سریع و راحت من را شخصی بدون اجازه به دیگران می فروشد از ادامه کارم دلسرد شدم، اما مایوس نشدم. اگر یک نفر بتواند از یک صفحه کتاب من مشکلش را حل کند برای من کافی است. چه بسیار افرادی که با استفاده از این کتاب در جایی تدریس کردند یا در آزمون استخدامی جایی قبول شدند و ... اینها برای من کافی است. ( توجه کنید که هیچ کتاب فارسی با این حجم (۵۶۵ صفحه A4 که در صورت تبدیل به قطع کتابی بیش از 1000 صفحه خواهد شد ) و عمق مطلب تا این لحظه در مورد VBA منشر نشده است. البته مقالاتی جسته و گریخته در سایتها قرار دارد اما کتابی منسجم به این شکل که حاوی چندین پروژه بزرگ و عملی است به  گواهی اغلب دوستان در اینترنت یافت نمی شود.)

3-     هزینه ها – اگر بخواهم که این روند ترجمه و تالیف کتب همچنان ادامه یابد می بایست فکری برای برخی هزینه های کتاب می کردم.

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

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

نحوه خرید کتاب از بانک

                        خرید کتاب از طریق خودپرداز( کارت به کارت )

سوالات متداول و جواب

                                          تاریخ آخرین به روز رسانی ۱۲/۰۶/۸۸

در آستانه انتشار جدیدترین کتابم ( پروژه های حرفه ای با Excel VBA ) بهتر دیدم که مجموعه کتابهای قبلی را کنار هم قرار دهم چرا که شاید برخی دوستان فقط یک یا دو کتاب بنده را مشاهده کرده باشند. برای دانلود کتابها می توانید روی لینک راست کلیک کرده و از منوی باز شده گزینه Save Target As را انتخاب نمایید. همه کتابها بر روی دو سرور قرار گرفته اند تا در صورت بروز مشکل بتوانید از سرور دیگر اقدام به دانلود نمایید. توجه کنید که در مورد سایت esnips باید روی لینک کلیک کنید و در صفحه باز شده روی دکمه دانلود کلیک نمایید

Microsoft Excel VBA Professional Projects کسانی که می خواهند قدرت اکسل را با تمام وجود حس کنند ، نیاز دارند که زبان برنامه نویسی VBA را یاد بگیرند. این کتاب شما را با انجام چند پروژه حرفه ای و بسیار عظیم با VBA آشنا می سازد. این کتاب هنوز منتشر نشده است ولی شما می توانید سه فصل نخست آن را از لینکهای زیر دانلود کنید. پس از دانلود به فهرست موجود در آن نگاهی بیاندازید. این کتاب ارزش دو سال زحمت ترجمه در سربازی را داشت. اگر از سرعت و قدرت زیاد نمی‌ترسید پس شروع کنید. PersianGig  فهرست   فصل 1   فصل 2     فصل 3 esnips فهرست   فصل 1   فصل 2      فصل 3
آموزش اکسل سریع و راحت دوستانی که می خواهند اکسل را از پایه و به صورت اصولی بیاموزند به کتابی احتیاج دارند که با آنها گام به گام حرکت کند. اکثر کتابها پس از چند صفحه آغازین به یکباره به مواردی در اکسل می پردازند که برای یک کاربر مبتدی هنوز مناسب نیست. این کتاب شما را قدم به قدم برای آشنایی با اکسل همراهی خواهد کرد و مانند برخی کتب دیگر شما را در میانه راه تنها نخواهد گذاشت. دانلود (PersianGig) دانلود (esnips)
جداول و نمودارهای محوری در اکسل آنالیز داده های عظیم کاری است که اکسل برای آن ساخته شده است. با ابزارهای PivotTable (جدول محوری) و PivotChart (نمودار محوری) آنالیز داده ها از یک کار هولناک و مبهم به یک لذت تبدیل خواهد شد. کافی است بدانید که چگونه از این ابزارها استفاده نمایید. این کتاب به طور تخصصی به این مبحث می پردازد.   دانلود (PesrianGig)   دانلود (esnips)
با اکسل دوست شویم در این کتاب مقالاتی را که در طول زمان در وبلاگ منتشر شده بود بصورت یکجا خواهید داشت. ویرایش چهارم این کتاب علاوه بر افزایش تعداد مقالات مندرج در آن ، دارای مثالهای کتاب در قالب فایل اکسل می باشد که می توان پس از مطالعه مطلب با کلیک روی آن مثال به فایل آماده موجود که به کتاب الصاق شده است دسترسی پیدا کرد.   دانلود(PersianGig)   دانلود(esnips)
توابع آماری در اکسل یکی از مجموعه کتابهای توابع ، کتاب توابع آماری است که به شرح این توابع در اکسل می پردازد. امروزه آمار دیگر فقط در یک رشته تدریس نمی شود بلکه اکثر رشته ها درسی به نام آمار دارند. پس بهتر است که بدانیم اکسل چه کمکی می تواند در این زمینه به ما بکند.  دانلود (PersianGig) دانلود (esnips)
توابع ریاضی و مثلثات در اکسل اکسل فقط برای جمع زدن اعداد نیست. بسیاری از توابع ریاضی در اکسل موجود است که شاید از آن بی خبر باشیم. شاید یکی از این توابع مشکل شما را در محاسبات حل کند.    دانلود (PersianGig) دانلود (sharemation)
توابع تاریخ و زمان اکسل با تاریخ و زمان چگونه رفتار می کند؟ چگونه برخی افراد می توانند دو تاریخ را از هم کم کنند؟ برای پاسخ به این سوالها و دیگر مشکلهایی که ممکن است به هنگام کار با تاریخ و زمان برای شما پیش آید ، بهتر است سری به این کتاب بزنید.    دانلود(PersianGig) دانلود (sharemation)
توابع جستجو و مرجع اگر در کارخانه ای مشغول کار هستید و می خواهید اطلاعات کارمندان را از کاربرگهای دیگری استخراج و در دیگر توابع بکار برید، اگر می خواهید از اکسل به عنوان یک پایگاهی که داده های اصلی را نگهداری می کند استفاده کنید، در این صورت به توابع جستجو و مرجع نیاز خواهید داشت. این کتاب شما را در درک مفهوم هر تابع راهنمایی می کند.  دانلود (PersianGig) دانلود (sharemation)

آقای علی محمدزاده سوال کرده اند که :

سوال :من میخوام برای یک سلول مثلا F1 بصورت زير بنويسم بطوری که جواب آن در خانه مجاور باشد
مثلاً اگر F1 عدد کمتر از 10 باشد ضعیف
اگر F1 عدد بین 10 و 12 باشد متوسط
اگر F1 عدد بین 12 تا 16 باشد خوب
اگر F1 عدد بین 16 تا 20 باشد عالی
مشکل این است که شرط تو در تو همراه عملگر & , Orجواب نمی دهد.

لطفا مرا راهنمایی کنید.

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

=IF(F1<,"ضعیف",10IF(F1<,"متوسط",12IF(F1<,"خوب",16IF(F1<=20,"عالی"عدد را درست وارد کنید"))))

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

ابتدا عدد چک می شود اگر <10 آنگاه ضعیف و اگر بزرگتر از 10 باشد به قسمت False می رود چون آنجا نیز یک فرمول IF هست پس در این جا نیز عدد چک می شود. چون عدد بزرگتر از 10 بوده که ما به این قسمت آمده ایم پس نیازی نیست که شرط را 10 قرار دهیم بلکه همان <12 کفایت می کند.و به همین ترتیب تا آخر.

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

آرایه ها

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

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

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

1. ناحیه A3:E3را انتخاب می کنیم.
2. تایپ کنید:

                 =A1:E1+A2:E2

3. کلیدهای Ctrl + Shift +Enter را با هم فشار دهید.

شکل مقابل نتیجه را نشان می دهد.همان طوری که می بینید یک فرمول آرایه ای مجموع هر گروه از داده ها را محاسبه می کند.این تک فرمول در هر پنج سلول یکسان دیده می شود.اکسل فرمولهای آرایه ای را در آکولاد قرار می دهد.در این مثال نواحی A1:E1 و A2:E2 آرگومان های آرایه بودند.(پارامتر)

مثالی که زدیم یک فرمول آرایه بود که به صورت افقی محاسبه می کرد.حال مثالی برای آرایه عمودی می آوریم:

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

1. ناحیه جواب را انتخاب می کنیم.
2. فرمول آرایه را تایپ می کنیم:

=A1:A6*B1:B6

3. کلیدهای سه گانه را با هم فشار می دهیم.

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

1. یک سلول خالی انتخاب می کنیم.
2. تایپ می کنیم:

=AVERAGE(A1:A6*B1:B6)

3. کلیدهای Ctrl +Shift +Enter را با هم فشار می دهیم.

با این فرمول ابتدا دو سطر د رهم ضرب می شوند و سپس از آنها میانگین گرفته    می شود.

آرایه های دو بعدی

مثالهایی که در بالا آوردیم مثالهایی از آرایه های تک بعدی بودند.یعنی  سطر یا  ستون پارامتر آنها بود اما آرایه هایی وجود دارند که در آنها هم سطر و هم ستون پارامتر هستند.به مثال زیر توجه کنید.فرض کنید می خواهیم جزء صحیح اعداد مثال بالا را پیدا کنیم.برای این کار

1. یک ناحیه هم اندازه با ناحیه داده ها انتخاب می کنیم.در مثال بالا 6 سطر و 3 ستون داده داشتیم برای همین یک ناحیه معادل با آنرا انتخاب می کنیم      مثلاF1:H6
2. تایپ می کنیم

=INT(A1:C7)

3. کلیدهای سه گانه را با هم فشار می دهیم.

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

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

 نکته:   شما نمی توانید آرایه های سه بعدی بسازید که در آن پارامترها از شیت های دیگر نیز باشند.

 قوانین فرمول آرایه

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

برای قفل شدن فرمول آرایه باید سه کلید Ctrl + Shift + Enter را با هم فشار دهید.با این کار اکسل دو آکولاد به اول و آخر فرمول اضافه می کند.نمی توان این آکولادها را دستی تایپ کرد چون در آن صورت اکسل فرمول را به عنوان آرایه قبول نمی کنید.بلکه آنرا به عنوان یک داده و یا یک برچسب در نظر می گیرد.

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

نکته:   برای انتخاب سریع کل یک آرایه یکی از سلولهای آرایه را انتخاب کرده و کلیدهای Ctrl+/ را فشار می دهیم.

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

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

آرایه برای اعداد ثابت

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

می خواهیم جزء صحیح اعداد 3.5  و 1.75 و 5.2 را پیدا کنیم.برای این کار:

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

=INT({3.5,1.75,5.2})

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

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

جذر اعداد زیر را با آرایه پیدا کنید. 4،9،16،25،36،49،64،81،100،121،144،169

تعداد داده ها 12 تا است پس می توان یک محدوده 6در 2 یا 3 در 4 را انتخاب کرد فرض کنیم 3 در 4 محدوده انتخابی باشد.در فرمول آرایه تایپ می کنیم:

=SQRT({4,9,16,25;36,49,64,81;100,121,144,169})

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

Data Tables

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

Data Table بر اساس یک متغیر ورودی

فرض کنیم شخصی می خواهد خانه ای را به قیمت 200,000 $ خریداری کند اما بصورت اقساط..او می خواهد بداند که میزان قسط های این خانه بر اساس بهره های مختلف چند دلار در ماه می شود.او برای حل این مسئله راه زیر را انتخاب می کند ما نیز با او همراه می شویم:

1. ابتدا نرخ های بهره را وارد می کنیم .در سلولهای B3:B8 وارد کنید:

6, 6.5, 7, 7.5, 8, 8.5 درصد

2. حال فرمول محاسبه اقساط را وارد می کنیم.این  فرمول را در سلول C2 وارد می کنیم:  

           =PMT(A2/12,360,200000)

A2/12 نرخ بهره به صورت ماهانه است.360 تعداد اقساط و 200000 هم که قیمت خانه می باشد.چون سلول A2 خالی است پس این فرمول بدون بهره محاسبه می شود

3. حال ناحیه داده های Table را انتخاب می کنیم.برای این کار سلولهای B2:C8 را انتخاب می کنیم.در واقع نرخ بهره ها و فرمول را انتخاب کردیم.
4. Table را از منوی Data انتخاب می کنیم.چون ما از سلول A2 در فرمول استفاده کردیم پس این سلول مرجع است.حال باید تعیین کنیم که این سلول مرجع عمودی است یا افقی.چون داده ها را در این مثال به صورت عمودی وارد کرده ایم پس مرجع ما در کادر Column input cell قرار می گیرد.در این کارد وارد می کنیم$A$2  یا با ماوس آنرا انتخاب می کنیم.

5. OK را کلیک می کنیم .اکسل برای هر نرخ بهره اقساط را محاسبه می کند.

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

{=Table(,A2)}

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

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

جدول تک متغیر با بیش از یک فرمول

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

فرض کنیم که شخص خریدار می خواهد که مقایسه ای بین اقساط وام 200000 و 180000 انجام دهد.برای این کار اینگونه عمل می کنیم:

1.   مثال وام 200000 را که به خاطر دارید.حال در سلولD2  فرمول زیر را وارد می کنیم:=PMT(A2/12,360,180000) 

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

2. ناحیه جدول را انتخاب می کنیم.در این مثال یعنی سلولهای B2:D8
3. پنجره Table را باز می کنیم.سلول مرجع باز هم همان $A$2 به صورت عمودی است.شکل زیر نتیجه را نشان می دهد.

Data Table بر اساس دو متغیر ورودی

جدول یک متغیره با دو فرمول را دیدیم حال می خواهیم جدول دو متغیره را با یک فرمول بررسی کنیم.همان مثال قبلی را ادامه می دهیم.فرض کنیم شخص خریدار می خواهد که علاوه بر اقساط وام به ازای هر بهره مقدار قسط را به ازای تعداد اقساط جدول بندی کند.طوری که یک جدول متقاطع را تشکیل دهد.یعنی به ازای نرخ بهره های گفته شده و تعداد اقساط 180 و240 و 300 و یا 360 ماهه.برای ساختن این جدول مراحل زیر را طی می کنیم:

1. ابتدا متغیر نرخ بهره را در یک ستون وارد می کنیم.مثلا در سلولهای B3:B8.شاید لازم باشد که به صورت درصد وارد کنیم.اگر درصدهای اعشاری گرد می شوند به علت این است که تعداد ارقام اعشاری آنها صفر است با راست کلیک روی این سلولها از قسمت format cell تعداد ارقام اعشاری را به 1 رقم افزایش دهید.
2. سپس متغیر دوم را که تعداد اقساط است را در یک ردیف وارد می کنیم طوری که این ردیف در بالا و سمت راست سری اول قرار گیرد.مثلا در سلول های C2:F2 .
3. حال می توانیم فرمول جدول را تولید کنیم.چون این یک جدول دو متغیری است پس فرمول باید در سلولی درج شود که محل تقاطع این دو سطر و ستون است یعنی سلول B2 .با اینکه در جدول یک متغیری می توانستیم چندین فرمول را داشته باشیم اما در جدول دو متغیری تنها یک فرمول می توانیم داشته باشیم.برای فرمول وارد می کنیم:=PMT(A2/12,B1,200000) 
4. ناحیه داده های جدول را انتخاب می کنیم .یعنی سلولهای B2:F8
5. فرمان Table را اجرا می کنیم.در این مثال چون متغیر سطری و ستونی را داریم پس در کادر Row Input cell سلول $B$1 و در کادر Column Input cell سلول $A$2 را وارد می کنیم.
6. OK را کلیک می کنیم.به این ترتیب محاسبات انجام می شوند.شکل زیر نتیجه را نشان می دهد.

 نحوه تفسیر جدول بالا ساده است مثلا عدد سلول D6 برابر -1611.19 است یعنی خریدار اگر بخواهد خانه 200000 دلاری را با نرخ بهره 7.5 درصد و با تعداد اقساط 240 ماه خریداری کند باید هر ماه این مبلغ قسط بپردازد.

همان طور که می بیند در این مثال چون هر دو متغیر سطر و ستون را داشتیم بنابراین در فرمول جدول داریم :

{=TABLE(B1,A2)}

نکته

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

-    می توان اعداد و نتایج را کپی کرد و در جای دیگر Paste کرد.به این ترتیب با تغییر فرمول جدول داده ها تغییر نمی کنند و می توان فرمول های دیگر را نیز امتحان کرد.

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

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

تعریف نام ها با استفاده از 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

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

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

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 کنیم البته با از کار انداختن امکان انتخاب اشیاء قفل شده.

دریافت فایل

حال بهتر است به بررسی 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  

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

Toolsà Macroà Record new macro

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

Inputbox("Message",["Title"],[Default],[Xpos],[Ypos],[Helpfile],[Context])

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

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 2006/07/21 by بهرام صمديان

'

' Keyboard Shortcut: Ctrl+s

'

     report = InputBox("شماره صفحه ای را که می خواهید چاپ شود وارد کنید")

       ActiveWindow.SelectedSheets.PrintOut From:=report, to:=report, Copies:=1, Collate _

        :=True

End Sub

جایی را که تغییر دادم را به رنگ قرمز مشخص کرده ام.اگر بتوانید این ماکرو را در ماکروهای شخصی خودتان (Personal) ذخیره کنید همواره می توانید از این ماکرو استفاده کنید.حال ویژوال بیسیک را ببندید و ماکرو را اجرا کنید.به سوال پاسخ دهید تا صفحه مورد نظر شما چاپ شود.

 http://support.microsoft.com/default.aspx?scid=KB;EN-US;213360

امیدوارم که به بزرگواری خود ببخشید.

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

در خانه ای فرمول زیر را وارد کنید:

=SUMPRODUCT(A2:A4;B2:B4)/SUM(B2:B4)

اما توضیح فرمول:قسمتی که با رنگ قرمز مشخص شده است یک تابع آماده اکسل می باشد که می توانید از قسمت Math & Trig به تابع Sumproduct دسترسی داشته باشید.در کادر این تابع نمرات را به عنوان آرایه 1 و واحدها را به عنوان آرایه 2 وارد می کنیم.با این کار مجموع حاصل ضرب نمرات در واحدها بدست می آید.اما برای میانگین گرفتن باید تغییراتی در این تابع بدهیم.پس در حالت ادیت فرمول بقیه فرمول را تایپ می کنیم.به این ترتیب عدد بدست آمده بر مجموع تعداد واحدها تقسیم می شود.و میانگین بدست می آید.البته این سریع ترین راه بود .امیدوارم مفید بوده باشد.

Filter

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

داده های زیر را در نظر بگیرید تا روی آنها کار کنیم: