کتاب آموزش ساده اکسل 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

 

 

آشنایی با یک سایت عالی

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

www.mehdizenhari.com

          

ترفند advance filter

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

با استفاده از advance filter  میتوانیم اینکار را انجام دهیم

تمرین

یک جدول ضرب ۵*۵ طوری طراحی کنید که تنها سلول اول آن فرمول نویسی شده باشد .

سلول های زرد رنگ باید در ستون سبز رنگ ضرب شود

فیلتر سازی پیشرفته - advance filter

گاهی اوقات لازم است که از یک ساختار پیچیده تر برای فیلتر سازی داده ها استفاده کنیم . برای این منظور به منوی data وراد شده ابزار advanced filter را انتخاب می کنیم . این ابزار قابلیت ترکیت شروط را به صورت شرط های and , or دارد .

همانطور که در تصاویر مشاهده می نمایید شرطهایی که در کنار یکدیگر قرار می گیرند( علی و tejarat) حکم and و انهایی که زیر هم قرار می گیرند (علی و رضا )حکم or  دارند. این شرط به صورت زیر خوانده می شود :

"رکوردهایی از علی که از بانک تجارت دریافتی داشته است و یا رکوردهایی از رضا که از بانک ملی دریافتی داشته است."

 

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

 

فرم سازی در اکسل - VBA

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

ابزارهایی که برروی فرم ها قرار می گیرند به کامپوننت مشهورند و کاربران می توانند با استفاده از این ابزار ورود وو خروج داده را سهل تر کنند. از جمله این ابزار LISTBOX  است که به کاربرانش اجازه می دهد تا یک لیست را در درونش مشاهده نمایند .در اینجا یک مثال از استفاده این ابزار را به همراه ADVANCE FILTER به دوستان گرامی تقدیم می کنم تا شاید بخشی از مشکلاتشان را مرتفع نماید.

دانلود مثال

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

 اگر رکوردی به لیست اضافه شود با زهم عمل کرده نام را به صورت غیر تکراری نمایش می دهد.

مثال های بیشتر را در اینده دنبال خواهیم کرد.

موفق باشید