یکی از مبرزترین توابعی که در نسخه های 2010 به بعد اکسل قابل استفاده می باشد aggregate است که شبیه تابع subtotal است یعنی ساختارو رفتارش به آن شبیه است.

اگر یادتان باشد ساختار تابع subtotal به شکل زیر بود:

=subtotal(  محدوده ,  شماره تابع    )

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

 

Function_num (includes hidden values)Function_num (ignores hidden values)Function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

 در میان توابع فوق شاید stdev , stdevp  که برای محاسبه میزان انحراف از معیار  داده های یک توزیع برای کل مجموعه و نمونه استفاده می شوند کمی غریب به نظر برسند اما سایر توابع کاربرد فراوانی دارند. در میان این توابع تفاوت count , counta نیز مهم است . count  شمارش سلول های حاوی عدد و counta  شمارش سلول های غیر خالی ( متن و عدد ) .

به طور مثال اگر به جای شماره تابع عدد 9 را قرار دهیم این تابع عملیات جمع را بر محدوده انتخاب شده اعمال می کند. در ضمن اگر به این شماره ها عدد 100 را نیز اضافه کنیم اکسل سطر های پنهان را نیز از محاسبه جمع (در محدوده" a1 : a10" ) خارج می کند یعنی بجای 9 از عدد 109 استفاده شود.

=subtotal(109,a1:a10)

 ویژگی مهم هر دو تابع subtotal , aggregate  درک ابزار فیلتر است و این برای گرفتن گزارش بسیار مهم است.

تابع aggregate شبیه subtotal  است با این تفاوت که علاوه بر پشتیبانی از توابع بیشتر ، قابلیت اعمال برخی آپشن ها را نیز دارد. این آپشن ها به کاربر اجازه می دهند تا برخی از اعمال مانند صرفه نظر کردن از محاسبه سلول های حاوی خطا یا سطر های پنهان را به آسانی انجام دهد. فهرست توابع aggregate  عبارتند از :

Function_numFunction
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 LARGE
15 SMALL
16 PERCENTILE.INC
17 QUARTILE.INC
18 PERCENTILE.EXC
19 QUARTILE.EXC

 در میان این توابع  mode.snlg , persentile , quartile  کمی دور از ذهنند که اگر با دقت به آنها نگاه کنید همان توابع قدیمی اکسلند که که کمی تغییر شکل یافته اند.

تابع mode.sngl  همان تابع mode  یا داده با بیشترین فراوانی است که از نسخه 2010 به بعد به آن پسوند اضافه شده است. توابع  perentile  نیز همان صدک ها هستند که با دو پسوند inc , exc  به ترتیب برای توزیع های بزرگ و کوچک استفاده می شوند.(صدک به مقداری گفته می‌شود که درصد خاصی از نمونه‌های یک متغیر تصادفی کم تر از آن‌اند. به عنوان مثال ۲۰امین صدک یک متغیر تصادفی مقداری است که ۲۰ درصد از مشاهدات این متغیر کم تر از آن هستند)

توابع quartile نیز همان چارکها هستند که با برای توزیع های کوچک inc و بزرگ exc استفاده می شوند.( در آمار توصیفی به هر یک از سه مقداری که یک مجموعه از داده‌های مرتب را به چهار بخش مساوی تقسیم می‌کند چارَک گفته می‌شود. به اینصورت هر کدام از آن بخش‌ها یک‌چهارم از نمونه یا جمعیت را به نمایش می‌گذارد. )

 

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

 رویداد Activate sheet

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

+ نوشته شده توسط علی توضیح در پنجشنبه پانزدهم مرداد ۱۳۹۴ و ساعت 11:49 |
سلام دوستان منبع مفید زیر برای انجام تحلیل واریانس یک طرفه و یا دو طرفه  در اکسل پیشنهاد می شوند:

 

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

 

+ نوشته شده توسط علی توضیح در یکشنبه هفتم تیر ۱۳۹۴ و ساعت 9:22 |
هر گاه بخواهیم در یک عبارت یا داده درون سلول کاراکتر یا مجموعه ای را جایگزین کنیم می توانیم از تابع replace  استفاده کنیم.

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

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

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

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

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

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

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

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

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

 

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


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

()Private Sub Worksheet_Activate

MsgBox Sheet1.Name


End Sub

+ نوشته شده توسط علی توضیح در جمعه چهاردهم تیر ۱۳۹۲ و ساعت 19:23 |
این کتاب با حجم ۳۲ مگ و با فرمت ی دی اف و به صورت تصویری و رنگی برای استفاده دوستان در اکسل ۲۰۱۰ در وبلاگ قرار می گیرد انشالله که مورد استفاده واقع شود.

 

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

 

 

+ نوشته شده توسط علی توضیح در پنجشنبه دوازدهم اردیبهشت ۱۳۹۲ و ساعت 12:59 |
 لیست های تکراری ، جداولی هستند که رکورد های آن مجاز به تکرار می باشند ، مثلا علی میتواند به هر تعداد که دوست دارد مراجعه و وجه نقد دریافت کند.یکی از روش های گزارش گیری از داده ها استفاده از ابزار قدرتمند جداول محوری است . ابزار 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

 

+ نوشته شده توسط علی توضیح در چهارشنبه دوم اسفند ۱۳۹۱ و ساعت 8:28 |
این تابع  دو عدد را از کاربر گرفته و بررسی می کند که آیا عدد اول از عدد دوم بزرگتر یا مساوی با آن است یا خیر . اگر شرط برقرار شد خروجی تابع عدد یک است و در غیر این صورت عدد 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

+ نوشته شده توسط علی توضیح در چهارشنبه بیست و دوم آذر ۱۳۹۱ و ساعت 11:15 |
امنیت یکی از ارکان مهم آفیس می باشد که آشنایی با برخی از المان های آن رمز موفقیت یک کاربر حرفه ای محسوب می گردد.برای پنهان سازی یک کاربرگ که حتی با unhide.. نیز آشکار نگردد باید به سراغ vba رفت.همانطور که می دانید vba یک زبان شی گرا در آفیس است . یعنی در هر application یا نرم افزار گروه آفیس ( اکسل - اکسس - ورو - پاورپوینت و... ) اشیایی منحصر به فرد وجود دارد. به طور مثال اشیای موجود در اکسل  سلول ها ، کاربرگ ها ، کارپوشه ها ، پنجره ها  نمودارها و. ... می باشند.حال برای برنامه نویسی کافی است خصوصیات این اشیا را تغییر دهید. مثلا نام شی کاربرگ یک" sheet1 "را می خواهیم  به کلمه ali  تغییر دهیم . 

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

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

 

 

+ نوشته شده توسط علی توضیح در چهارشنبه بیست و دوم آذر ۱۳۹۱ و ساعت 9:31 |
گاهی اوقات لازم می شود که کاربران نمرات یا داده های خود را بدون مرتب کردن رتبه بندی کنند . تابع rank یکی از بهترین و ساده ترین روش های رتبه بندی در اکسل است .شکل این تابع به صورت زیر است:

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

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

نمره ها بدون ترتیب زیر هم قرار گرفته اند

نشانی محدوده باید مطلق سازی شود

به داده های تکراری دقت کنید رتبه ی بعدی 3 نیست بلکه 4 است

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

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

 

 

+ نوشته شده توسط علی توضیح در جمعه بیست و یکم مهر ۱۳۹۱ و ساعت 7:8 |