| تبليغات | X |
دوست عزیز برای مرتب کردن این تیمها شما باید همه مراجع را نیز با مرتب کردن جابجا کنید.مثلا اگر بارسلونا در ردیف اول باشد و به قسمت نتایج لینک شده باشد با مرتب کردن اگر بارسلونا به پایین جابجا شود دیگر مرجع آن به همان نتایج بارسلونا برنمی گردد بلکه به تیمی دیگر ارجاع داده می شود برای همین باید همه آنها را در یک ردیف قرار داد تا با تغییر مکان تیم نتایج آنها هم تغییر کند.فایل زیر را که شما فرستاده بودید کمی تغییر دادم ببینید منظورتان را برآورده می کند.در ضمن برای مرتب کردن بر اساس چند فیلد باید در قسمت مربوط مشخص کنید که ابتدا بر اساس امتیاز مرتب بشه در فیلد دوم تفاضل گل و در فیلد سوم هم احتمالا گل زده.برای دسترسی به این قسمت هم Data-->Sort
برای این کار می توانید نام تیم ها را به همراه امتیازاتشان در یک شیت وارد کنید.سپس از اکسل بخواهید که این نتایج را بر حسب امتیاز سورت (مرتب) کند و در درجه دوم بر اساس تفاضل گل عمل کند به این ترتیب تیم های برتر در بالا قرار می گیرند و در مواردی که دو تیم امتیاز یکسانی داشته باشند بر اساس تفاضل گل عمل خواهد شد.امیدوارم فایل زیر راهنمای خوبی برای شما باشد.
در اکسل کاربر میتواند توابعی را تعریف کند که User Defined Function یا به اختصار UDF نامیده میشوند، در این مقاله نحوه تعریف این توابع و بکار گیری آن توضیح داده شده است.
مطالب به صورت خود آموز و قدم به قدم توضیح داده شده است .
دقیقا یادم است که روزی قرار ملاقاتی با یکی از اساتید اکسل داشتم ، ایشان استاد دانشگاه بودند و گویا دوتا هم مدرک دکتری . تقریبا یادم نیست که چه صحبتهایی شد اما این را خوب یادم است که بعد از آن ملاقات بود که میخواستم خودم در اکسل یک تابع بنویسم ، خوب از کجا میدانستم که میشود اینکار را کرد ؟
فکر کنم که روزی فایلی را از اینترنت گرفته بودم و بعد از نصب آن وقتی به Insert à function رفته بودم گزینهای به نام User Function را دیدم و همان موقع شصتم خبر دار شد که بله میشود خودمان توابع دلخواهمان را در اکسل بنویسیم . اما چطوری ؟
در دبیرستان ساعتها وقت ما را همین کلمه گرفت و انصافاٌ من با کامپیوتر بود که تازه فهمیدم تابع چیست. در واقع تابع یک عملگری است که چیزی را میگیرد و روی آن کاری را انجام میدهد و بعد چیز دیگری که خروجی میگوییم را به ما میدهد.
مثلا همین تابع SUM را در نظر بگیرید ، چند تا عدد میگیرد و جمع آنها را به ما میدهد.
اکسل بیش از 300 تابع دارد که اکثر کارهایی که ممکن است بخواهیم انجام دهیم با این توابع قابل انجام است ، در ضمن ترکیب این توابع نیز برای ما امکانات فراوانی را بهمراه دارد و این را هم مد نظر داشته باشیم که شرکتی به عظمت ماکروسافت و تجربه چندین سالهاش مطمئنا نیازهای تمامی کاربران در سطح دنیا را در نظر داشته و تا آنجایی که امکان داشته توابع مختلف را پیش بینی کرده است .
البته این سوال ممکن است به ذهن شما متبادر شود که چرا باید تابع جدیدی اضافه کرد.
شاید دلایل زیر بتواند گوشهای از ارزش تابع را برای ما بیان کند:
برای شروع بهتر است کمی در خصوص ویژوال بیسیک بدانیم . VB یک زبان برنامه نویسی بسیار متداول است. برای اینکه یک تابع جدید نوشته شود لازم است که کمی با برنامه نویسی با این زبان آشنا باشیم. برای این منظور پیشنهاد میکنم که نرم افزار VB را تهیه کنید و بعد از آن هم چند CD آموزشی و یک کتاب . با کمی تمرین با اصول ابتدایی این زبان آشنا خواهید شد و قول میدهم کار بسیار ساده تر از آنچه فکر میکنید باشد.
لازم به ذکر است که نسخه جدید نرم افزار VB به نام VB.NET نیز وجود دارد که امکان استفاده آن در OFFICE 12 که نسخه بعدی آفیس است گنجانده شده و بد نیست بدانیم که VB.NET در واقع قابلیتهای زبان برنامه نویسی C# را دارد.
شما برای استفاده از VB در اکسل نیاز ندارید که نرم افزار VISUAL BASIC را نصب کنید ، همراه با نصب آفیس خود این نرم افزار نیز نصب میشود.
ابتدا بایستی وارد محیط VB شویم. برای اینکار چندین راه وجود دارد که عبارتند از:
· زدن کلید ALT+F11
·
· از Toolbar :
محیط ویژوال بیسک
شما باید دستورات تابع خود را در یک Module (ماژول) بنویسید ، از منوی Insert گزینه Module را بزنید . و اگر به project explorer نگاه کنید متوجه خواهید شد که یک ماژول جدید ایجاد شده است.
یک تابع در ویژوال بیسیک قواعد استانداردی دارد که شما باید از این قواعد اطاعت کنید .
اولین قانون آن این است که یک تابع با دستورات استانداردی شروع و به پایان میرسد.
قانون دوم این است که هر تابع یک نوع دارد و ورودیهای یک تابع در داخل پرانتز مشخص میشوند.
قانون سوم ، نوع داده ورودیها (و خود تابع) باید مشخص شود.
این دستورات عبارتند از :
Private Function Test(Num As Integer) as Double
End Function
نام تابع ما test است و عبارت داخل پرانتز میگوید که این تابع یک ورودی دارد که نام آن ورودی Num است و integer بیانگر آن است که این ورودی عددی صحیح است . (-32,768 تا 32,767 )
خروجی تابع از نوع double است و البته گذاشتن آن در همه موارد الزامی نیست ، گرچه بهتر است که مشخص شود. (برای اطلاع بیشتر به کتابهای برنامه نویسی مراجعه کنید.)
عبارت Private Function نشانگر شروع تابع و End Function برای پایان تابع است.
فرض کنید میخواهیم تابعی بنویسیم که یک عدد را بگیرد و آنرا در 10 ضرب کند!
اول باید تصمیم بگیریم که اسم این تابع را چه بگذاریم ، در حقیقت این اسم همان کلمهای است که در اکسل برای استفاده از این تابع استفاده خواهیم کرد.
خوب اسم آنرا Test میگذاریم و میدانیم که این تابع باید یک ورودی داشته باشد و خوب چون به تازگی با نوع عدد Integer آشنا شدیم (عدد صحیح) نوع این ورودی را هم Integer میگذاریم.
باید نامی برای این ورودی در نظر بگیریم ، این نام نباید یک نام آشنا ! برای VB باشد و بهتر است نامی با مسما در نظر بگیریم ، اینجا اسم این ورودی را Num میگذاریم.
پس در ماژول خود خواهیم نوشت :
Private Function Test(Num As Integer)
Test = Num * 10
End Function
حال از ویژوال بیسیک خارج میشویم ( Alt + Q) و به اکسل بر میگردیم .
مثل توابع استاندارد اکسل میتوان از این تابع هم استفاده کرد مثلا بنویسید :
= test(8)
= test(A1)
اگر به جای کلمه Private ، Public بنویسیم، میتوانیم نام تابع جدیدمان را در UserFunction ببینیم.
میخواهیم تابعی بنویسیم که شماره رنگ یک سلول (fill color) یا شماره رنگ قلم (font color) را مشخص کنیم.
(میدانیم که در اکسل از 56 رنگ میتوان استفاده کرد که هر رنگ یک کد دارد مثلا کد رنگ قرمز 3 و آبی 5 است.)
· نام تابع : CellColor
· ورودی : تابع دو ورودی دارد ، ورودی اول آدرس سلول است و ورودی دوم مشخص میکند که ما میخواهیم رنگ زمینه سلول را داشته باشیم یا رنگ قلم آنرا .
اگر ورودی دوم عبارت fill بود رنگ زمینه مد نظر است و اگر font بود رنگ قلم.
· نام ورودی اول MyRange و از نوع Range است
· نام ورودی دوم Mode و از نوع String است
· اگر ورودی دوم داده نشده بود و یا مقادیری غیر fill و font بود ، خروجی تابع یک خطا به شکل #Mistake باشد .
برای نوشتن این تابع از دستور شرطی IF به صورت زیر استفاده میکنیم :
Public Function Colorindex(MyRange As Range, Mode As String)
Application.Volatile True
If Mode = "font" Then
Colorindex = MyRange.Font.Colorindex
ElseIf Mode = "fill" Then
Colorindex = MyRange.Interior.Colorindex
Else
Colorindex = "#Mistake"
End If
End Function
عبارت As Range بیان میکند که ورودی اول یک خانه است.
عبارت String As بیان میکند که ورودی دوم یک رشته (متن – غیر عدد) است .
دستور Application.Volatile True به اکسل میگوید که هر وقت هر خانهای را مجدد محاسبه کرد، باید تابع ما را نیز مجدد محاسبه کند . ( این حالت مانند تابع now() خود اکسل است که زمان را مرتب محاسبه و نشان میدهد.) زدن کلید F9 نیز باعث میشود که این تابع مجدد محاسبه شود.
حال خانه A1 را به رنگ زرد و متن آنرا قرمز میکنیم و تابع را روی آن آزمایش میکنیم.
فرمولهای بکار رفته در خانه B1 و B2 را به ترتیب در D1 و D2 مشاهده میکنید.
|
|
A |
B |
C |
D |
|
1 |
far |
13 |
|
=colorindex(A1,"font") |
|
2 |
|
6 |
|
=colorindex(A1,"fill") |
بهتر است از مثال قبل یک استفاده کاربردی کنیم. مثلا در یک محدوده میخواهیم سلولهای قرمز رنگ را با هم جمع بزنیم. (این سلولها میتوانند با Conditional Formationg قرمز شده باشند.)
Public Function SumByColor(InRange As Range, WhatColorIndex As Integer) As Double
Application.Volatile True
For Each C In InRange.Cells
If C.Interior.Colorindex = WhatColorIndex Then
SumByColor = SumByColor + C.Value
End If
آقای امین حیدرپور مولف محترم کتاب مرجع کامل اکسل لطف کردند و عناوین این کتاب را برای بنده ارسال کردند.من هم برای اینکه دوستان نیز از این کتاب اطلاع یابند این عناوین را در وبلاگ قرار می دهم.امیدوارم که شاهد کارهای بهتر و بهتری از این مولف کشورمان باشیم.
آقای علی محمدزاده سوال کرده اند که :
سوال :من میخوام برای یک سلول مثلا 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
به چنین فرمولهایی فرمول IF در IF می گویند.البته تا آنجایی که من می دانم تعداد این IF های تو در تو محدود می باشد .
آرایه ها
آرایه ها می توانند چندین محاسبه را با یک فرمول انجام دهند یا به جای دریافت یک داده گروهی از داده ها را دریافت کنند.بهترین راه برای فهم چگونگی عملکرد آرایه ها حل چند مثال است.
فرض کنیم که دو ردیف عدد داشته باشیم.
می خواهیم مجموع هر ستون را تنها با یک فرمول بدست بیاوریم.برای این کار:
=A1:E1+A2:E2
شکل مقابل نتیجه را نشان می دهد.همان طوری که می بینید یک فرمول آرایه ای مجموع هر گروه از داده ها را محاسبه می کند.این تک فرمول در هر پنج سلول یکسان دیده می شود.اکسل فرمولهای آرایه ای را در آکولاد قرار می دهد.در این مثال نواحی A1:E1 و A2:E2 آرگومان های آرایه بودند.(پارامتر)
مثالی که زدیم یک فرمول آرایه بود که به صورت افقی محاسبه می کرد.حال مثالی برای آرایه عمودی می آوریم:
=A1:A6*B1:B6
حال تابع دیگری را با آرایه ترکیب می کنیم.فرض کنیم که می خواهیم میانگین حاصل ضرب های بالا را یکجا بدست بیاوریم.برای این کار :
=AVERAGE(A1:A6*B1:B6)
با این فرمول ابتدا دو سطر د رهم ضرب می شوند و سپس از آنها میانگین گرفته می شود.
آرایه های دو بعدی
مثالهایی که در بالا آوردیم مثالهایی از آرایه های تک بعدی بودند.یعنی سطر یا ستون پارامتر آنها بود اما آرایه هایی وجود دارند که در آنها هم سطر و هم ستون پارامتر هستند.به مثال زیر توجه کنید.فرض کنید می خواهیم جزء صحیح اعداد مثال بالا را پیدا کنیم.برای این کار
=INT(A1:C7)
همانطور که می بینید اکسل جزء صحیح هر سلول را توسط آرایه محاسبه کرده است.
برای درج یک فرمول آرایه ای باید ابتدا سلول یا سلولهایی را که نتیجه در آنها درج می شود انتخاب کرد.اگر آرایه نتایج ضربی را ارائه می دهد باید ناحیه جواب مطابق با ناحیه داده ها باشد تا محاسبات به درستی انجام شوند.
برای قفل شدن فرمول آرایه باید سه کلید Ctrl + Shift + Enter را با هم فشار دهید.با این کار اکسل دو آکولاد به اول و آخر فرمول اضافه می کند.نمی توان این آکولادها را دستی تایپ کرد چون در آن صورت اکسل فرمول را به عنوان آرایه قبول نمی کنید.بلکه آنرا به عنوان یک داده و یا یک برچسب در نظر می گیرد.
نمی توان یک سلول از آرایه را به تنهایی ویرایش کرد یا پاک کرد و یا جابجا نمود.برای ادیت یا هر کار دیگری باید همه آرایه را با هم انتخاب کنیم و در نوار فرمول به تغییر فرمول بپردازیم.در این هنگام دو آکولاد ناپدید می شوند در انتها باید دوباره آن سه کلید را با هم فشار دهیم.
می توان به بخشی از آرایه فرمت خاصی اعمال کرد .یا می توان بخشی را کپی کرد و در جای دیگر Paste کرد.
آرایه برای اعداد ثابت
مقادیر ثابتی که در آرایه می توان استفاده کرد عبارتند از عدد ، متن یا مقادیر منطقی.در این حالت باید به صورت دستی اطراف مقادیر ثابت آرایه آکولاد درج کنیم.مقادیر ثابت در درون فرمول آرایه با ویرگول و سمی کولن جدا می شوند.ویرگول برای نشان دادن نتیجه در ستون های مجزا و سمی کولن برای نشان دادن نتیجه در سطرهای مجزا بکار می رود.به مثالهای زیر توجه کنید:
می خواهیم جزء صحیح اعداد 3.5 و 1.75 و 5.2 را پیدا کنیم.برای این کار:
=INT({3.5,1.75,5.2})
اگر بخواهیم آرایه را به صورت عمودی داشته باشیم باید بین پارامترها از سمی کولن استفاده کنیم.مثال زیر مبیّن این مطلب است:
جذر اعداد زیر را با آرایه پیدا کنید. 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 بر اساس یک متغیر ورودی
6, 6.5, 7, 7.5, 8, 8.5 درصد
=PMT(A2/12,360,200000)
A2/12 نرخ بهره به صورت ماهانه است.360 تعداد اقساط و 200000 هم که قیمت خانه می باشد.چون سلول A2 خالی است پس این فرمول بدون بهره محاسبه می شود
{=Table(,A2)}
چون این مثال سلول مرجع سطری ندارد بنابراین جای آن در فرمول خالی گذاشته شده است.
خریدار خانه تصمیم می گیرد که فقط 185000 دلار را قسطی پرداخت کند و کمی را نقد بپردازد.برای محاسبه اقساط این وام باید فرمول را تغییر دهیم.یعنی در فرمول سلول C2 عدد 185000 را به جای 200000 وارد کنیم.محاسبات به طور خودکار دوباره انجام می شوند.
جدول تک متغیر با بیش از یک فرمول
می توان بر اساس یک سری داده فرمولهای زیادی را محاسبه کرد.اگر داده ها را به صورت ستون وارد کنیم باید فرمولها را در ستونهای بعدی وارد کنیم.مثال زیر را ببینید.
فرض کنیم که شخص خریدار می خواهد که مقایسه ای بین اقساط وام 200000 و 180000 انجام دهد.برای این کار اینگونه عمل می کنیم:
1. مثال وام 200000 را که به خاطر دارید.حال در سلولD2 فرمول زیر را وارد می کنیم:=PMT(A2/12,360,180000)
اگر دقت کنید می بینید که این فرمول نیز به همان سلول A2 رجوع می کند.
Data Table بر اساس دو متغیر ورودی
جدول یک متغیره با دو فرمول را دیدیم حال می خواهیم جدول دو متغیره را با یک فرمول بررسی کنیم.همان مثال قبلی را ادامه می دهیم.فرض کنیم شخص خریدار می خواهد که علاوه بر اقساط وام به ازای هر بهره مقدار قسط را به ازای تعداد اقساط جدول بندی کند.طوری که یک جدول متقاطع را تشکیل دهد.یعنی به ازای نرخ بهره های گفته شده و تعداد اقساط 180 و240 و 300 و یا 360 ماهه.برای ساختن این جدول مراحل زیر را طی می کنیم:
همان طور که می بیند در این مثال چون هر دو متغیر سطر و ستون را داشتیم بنابراین در فرمول جدول داریم :
{=TABLE(B1,A2)}
نکته
- نمی توان بخشی از جدول را بصورت جداگانه ادیت کرد چون جدول دارای ساختار آرایه است.
- می توان اعداد و نتایج را کپی کرد و در جای دیگر Paste کرد.به این ترتیب با تغییر فرمول جدول داده ها تغییر نمی کنند و می توان فرمول های دیگر را نیز امتحان کرد.
شکلبندی با Style
برای اعمال چندین شکلبندی به یک فایل بهتر است که از Style استفاده کنیم .برای دسترسی به این دستور مسیر زیر را طی کنید:Format à Style
در کادر باز شده نام style به کار رفته در فایل فعلی را می بینیم .و در پایین آن چندین گزینه تیک دار وجود دارد که مربوط به فرمت عدد و حفاظت و ... می باشد.برای اینکه یک Style را اعمال کنیم باید نام آنرا از لیست باز شدنی موجود در پنجره Style پیدا کرده و آنرا انتخاب کنیم و OK را فشار دهیم.
می توان style جدیدی را تعریف کرد و از آن برای فرمت سریع فایل استفاده کرد.برای این کار مراحل زیر را طی می کنیم:
چون اینجا یک سلول را ابتدا انتخاب کرده بودیم پس style ما بعد از این برای یک سلول اعمال خواهد شد.این امکانی مفید است چون شاید بخواهیم که برای یک سلول style خاص و برای سلول دیگر style دیگری را در نظر بگیریم.
تغییر دادن یک style
شاید به جای اینکه یک style جدید تعریف کنید بهتر باشد که style موجود را اصلاح کنید برای این کار :
ترکیب style از فایل دیگر
برای کپی کردن یک style از فایل دیگر به فایل جاری مراحل زیر را طی می کنیم:
حذف Style
برای حذف style ابتدا آنرا از پنجره style انتخاب کرده سپس روی Delete کلیک کنید.البته نمی توان style نرمال را حذف کرد.
