excel

سلام

رویداد Activate در کاربرگ - وی بی ای VBA

گاهی اوقات برخی کاربران می خواهند  ماکروها و فرم هایشان  هنگام فعال شدن کاربرگ به صورت خودکار و اتوماتیک اجرا گردد. برای اینکار وارد محیط VBA Excel  اکسل شده   (  Alt + f11  ) ، از پنجره project کاربرگ مورد نظر را انتخاب کرده در قسمت بالای کد گزینه worksheet  و سپس رویداد Activate آنرا انتخاب کرده کد ها و دستورات لازم را درون آن می نویسیم.

 

همانطور که می دانید رویدادها اقتضایی هستند نه آنی .یعنی به محض نوشتن یا فشردن کلید اجرا ، عمل نمی کنند آنها تحت شرایط تعریف شده اجرا می شوند. یعنی این کد (  "msgbox "salam ) تنها در صورتی اجرا می شود که کاربرگ یک Sheet1 اکتیو یا فعال گردد.

کتاب ها و منابع آموزشی ANOVA IN EXCEL

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

 

آموزش   انجام  تحلیل واریانس ANOVA در اکسل  گیبسون

 

تابع replace در اکسل

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

این تابع دارای چهار بخش است :

1- ایتدا متن قدیمی را از کاربر گرفته   old text

2-  حال شماره کاراکتری را که باید عوض شود ( مثلا 5 برای پنجمین کاراکتر )

3- تعداد تعویضی ها

4- کاراکتر تعویضی

مثال :  در سلول  F1  داده  "ali zozih " را نوشته ایم. کاراکتر z  اول اشتباه نوشته شده و می خواهیم آنرا با کاراکتر  t  تعویض کنیم .

فرمول را در سلول روبرو یعنی G1  چنین می نویسیم :

=replace(f1,5,1,"t")

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

 

اجرای عملیات با فعال شدن کاربرگ vba sheet1

برخی اوقات کاربر لازم دارد با فعال شدن یک کاربرگ مثلا کاربرگ 1 ( sheet1 ) عملیات خاصی اجرا گردد . برای اینکار به سراغ محیط وی بی ای رفته (   alt + f11)  در پنجره پروجکتس projects  برروی کاربرگ 1 دبل کلیک می کنیم اینکار سبب می شود تا رویدادهای کاربرگ 1 ، فراخوانی شوند. ابتدا در پنجره کد گزینه workshhet  را انتخاب کرده سپس رویداد مورد نظر را انتخاب می کنیم ( مثلا رویداد فعال شدن که همان activate است ).

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

()Private Sub Worksheet_Activate

MsgBox Sheet1.Name

End Sub

کتاب آموزش ساده اکسل 2010

این کتاب با حجم ۳۲ مگ و با فرمت ی دی اف و به صورت تصویری و رنگی برای استفاده دوستان در اکسل ۲۰۱۰ در وبلاگ قرار می گیرد انشالله که مورد استفاده واقع شود.

 

دانلود کتاب plain & simple Excel 2010

 

 

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

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

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

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

 

 

 برای نوشتن این کد باید کلید های  Alt+F11 را فشرد تا به محیط VBA وارد شویم.از منوی Insert  گزینه ی Module  را انتخاب می کنیم و سپس کد مورد نظر را در آن می نویسیم.

 

Sub list()
Dim i As Integer
Dim r As Integer

'shomareh radif akharin celol database
r = Range("a1").CurrentRegion.Rows.Count

'avalis celol database
Cells(1, 13) = Cells(1, 2)

'asami bedone tekrar
For i = 1 To r
If WorksheetFunction.CountIf(Range("m:m"), Cells(i, 2)) < 1 Then

Cells(i, 13) = Cells(i, 2)

End If
Next

End Sub

 

معرفی تابع gestep

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

Syntax

GESTEP(number,step)

Number   is the value to test against step.

Step   is the threshold value. If you omit a value for step, GESTEP uses zero.

=gestep(5,4)  ==> 1

=gestep(5,5)  ==> 1

=gestep(4,5)  ==> 0

پنهان سازی کامل یک کاربرگ - very hidden

امنیت یکی از ارکان مهم آفیس می باشد که آشنایی با برخی از المان های آن رمز موفقیت یک کاربر حرفه ای محسوب می گردد.برای پنهان سازی یک کاربرگ که حتی با unhide.. نیز آشکار نگردد باید به سراغ vba رفت.همانطور که می دانید vba یک زبان شی گرا در آفیس است . یعنی در هر application یا نرم افزار گروه آفیس ( اکسل - اکسس - ورو - پاورپوینت و... ) اشیایی منحصر به فرد وجود دارد. به طور مثال اشیای موجود در اکسل  سلول ها ، کاربرگ ها ، کارپوشه ها ، پنجره ها  نمودارها و. ... می باشند.حال برای برنامه نویسی کافی است خصوصیات این اشیا را تغییر دهید. مثلا نام شی کاربرگ یک" sheet1 "را می خواهیم  به کلمه ali  تغییر دهیم . 

ابتدا با فشردن کلید های alt , f11  به محیط برنامه نویسی می رویم و در انجا در محیط وی بی ای به سراغ پنجره projects  می رویم و شی مورد نظر " sheet1"  را انتخاب می کنیم تا خصویاتش در پنجره زیر نمایش داده شود. حال در قسمت name  نام کاربرگ را تغییر می دهیم.

برای تغییر خصوصیت visible یا نوع نمایش کاربرگ نیز می توان اقدام کرد. سومین گزینه از خصوصیت visible  کاربرگ عبارت  xlsheetveryhidden  است که با انتخاب آن کاربگ به کلی پنهان شده و حتی از طریق  unhide  نیز نمایش داده نمی شود.لازم به ذکر است که حتی این گزینه فعال نمی شود که کاربر به آن شک کند و می توان با خیال راحت کاربرگ های سری را از دسترس افراد پنهان نمود.

 

 

استفاده از تابع پر کاربرد rank

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

=rank( روش مرتب سازی, محدوده, داده)

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

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

http://excel.blogfa.com/8503.aspx