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

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

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

()Private Sub Worksheet_Activate

MsgBox Sheet1.Name

End Sub

ترفند advance filter

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

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

تمرین

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

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

استفاده از تابع vlookup در ایجاد لیست خودکار قیمت ها

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

ساختار این تابع به شکل زیر است :

=vlookup( مقدار جستجو , آدرس کل جدول, شماره فیلد مورد نظر , نحوه جستجو   )

فرض کنید می خواهیم با نوشتن نام غذا قیمت واحد آن نمایش داده شود . برای اینکار ابتدا در یک محل خاص اسامی و قیمت واحد غذا ها را یادداشت می کنیم ، حال در یک ستون فرمول vlookup  را برای قیمت ها وارد می کنیم .

 نکته مهم  آنست که این تابع تنها در اولین ستون جدول داده به دنبال داده ی مورد نظر ( جوجه ) می گردد.

 

جوجه نام غذاست و محدوده ی سبز رنگ آدرس کل جدول است و عدد ۲ به معنی آنست که از دومین فیلد جدول دادهی متناظر را بیاور (قیمت )

 عدد صفر در نوع جستجو به معنی یافتن داده به صورت تحقیقی است نه تقریبی :

true به معنی یافتن تقریبی از داده

false  به معنی یافتن دقیق داده

ترفند autofill - series

هرگاه بخواهیم یک سری از اعداد را به دنبال هم از مقصد تا مبدا در سلول ها بنویسیم ، می توانیم از خاصیت پرکن خودکار یا همان autofill  استفاده کنیم .

مثال : در ستون a  از اولین سلول تا سلول a10000  اعداد زوج با شروع از عدد 4 را بنوسید.

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

ALT+E+I+S

تا پنجره SERIES  باز شود حال کافی است بجای Rows از گزینه Columns  استفاده کنیم تا دادهها ستونی زیر هم قرار گیرند. حال دو بخش STEPعدد(2) و STOP عدد ( 10000) را وارد می کنیم.

توجه کنید که برای درست اجرا شدن این عملیات باید در سلول اول عدد 4 نوشته شده باشد و برروی آن سایر عملیات انجام گیرد.

اگر در بخش type از گزینه  growth استفاده کنیم یک سری هندسی با قدر نسبت 2 و جمله پایانی 10000 ساخته می شود ( ضربی بجای جمعی )

 

توضیح بیشتر درباره تمرین قبل

در تمرین انبار از سه تابع match index , dsum  استفاده شده است. همانطور که قبلا توضیح دادم دو تابع match , index  برعکس یکدیگرند.

تایع  index  شماره ردیف داده را در یک مجموعه دریافت کرده خود داده را نمایش می دهد در حالیکه تابع  match  داده را دریافت کرده شماره ردیف آن در مجموعه را نمایش می دهد.

=index(   شماره ردیف داده ,  مجموعه    )  ====>   data

=match ( data ,  مجموعه ,   type )      ======>    data row

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

تابع dsum  هم عملیات جمع را با شرط انجام می دهد . این تابع سه پارامتر دارد . ابتدا نشانی کل جدول را از کاربر گرفته ، شماره فیلدی را که قرار است عملیات جمع روی آن انجام گیرد را نیز دریافت می کند آنگاه با دریافت آدرس محدوده شرط ، عملیات جمع را انجام می دهد . نکته مهم آنست که آدرس محدوده ی شرط باید شامل عناوینی باشد که در آدرس کل جدول نیز وجود دارند. مثلا اگر شما می خواهید به دنبال جمع تعداد کالای خریداری شده بگردید ( فیلد شماره 6) باید محدوده شرط را طوری انتخاب کنید که کد کالا و شماره ی آن در محدوده ی شرط باشند ( m1 :m2 )

 البته در تابع match  پارامتر آخر دارای سه مقدار 0 و 1 و -1 است که بسته به نوع جستجو از آنها استفاده می کنیم :

1 برای جستجوی مقادیری کوچکتر از مقدار داده شده

0 برای جستجوی دقیق مقادیر داده شده

-1 برای جستجوی مقادیر بزرگتر از مقدار داده شده

تمرین - یافتن داده ها روش استفاده از توابع match , dsum

در بسیاری از مواقع کاربران برای یافتن یک کالا یا شخص با استفاده از کد با مشکل روبرو می شوند و می خواهند بدون استفاده از vba  نیز بر مشکل برطرف شود.

برای اینکار می توان از تابع  match  برای یافتن اولین ردیف از داده استفاده کرد و سپس نام داده را با استفاده از تابع index شناخت , بالاخره برای یافتن جمع ها از توابعی مانند dsum یا خانواده sumifs استفاده کرد. برای این منظور تمرین طراحی و با پاسخ در اختیار شما دوستان قرار می دهم تا بیشتر تمرین کنید.

تمرین - در جدول انبار ساختاری طراحی کنید که با نوشتن کد هر کالا شماره اولین ردیف ،نام کالا و سپس جمع خریداری شده و فروخته شده و مانده ی هر کالا را نمایش دهد.

 دانلود فایل تمرین

طراحی جدول داده

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

۱- از گسستگی داده ها بپرهیزید . ( سطر یا ستون کاملا خالی در جدول نداشته باشید )

۲- از انجام عملیات merge  بپرهیزید ( اینکار مانع از مرتب سازی و فیلترینگ صحیح داده ها در جدول می گردد)

3- تمام فیلد های مربوط به یک موضوع را در یک جدول قرار دهید تا قادر به دریافت اطلاعات صحیح تر باشید ( فیلدها همان ستون های جدولند )

4- فیلد های جدول حتما باید دارای عنوان باشند (هدر )

5- حتما سعی کنید از سمت ستون A  جدول را طراحی کنید.

۶- از ورود داده های نامعتبر جلوگیری کنید (  validation).

7- از تخریب سلول هایی که نباید دستکاری شوند با protection  جلوگیری کنید.

8- سعی کنید در بالای عناوین فیلدها محاسبات و سرجمع آنهارا نشان دهید( subtotal ) نه در پایین جدول

9- هر چه بیشتر فیلد در جدول شما باشد گزارشات شما دقیقتر و بنابراین اطلاعات شما صحیحتر خواهد بود   (pivottable).

۱۰-منطق ورود داده را فراموش نکنید ( جایی که قرار است نام وارد شود فامیل وارد نکنیم )

تمرین مدیریت داده -اکسل پیشرفته

تمرین - فرض کنید یک لیست از شماره های دانشجویی یک کلاس داریم و می خواهیم نمره ی هر دانشجو را در جلوی شماره اش وارد کنیم .فرمی لازم داریم تا با تایپ شماره دانشجویی آنرا در فهرست یافته و پس از ورود نمره آنرا در مقابلش ثبت کرده َ شماره دانشجویی بعدی را از کاربر بگیرد.

 

پاسخ: ابتدا یک سلول از جدول انتخاب شود.سپس ابزار form  را از بخش ابزار های اکسل :

QAT\MORE COMMANDS \ ALL COMMANDS\FORM..

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

 

 با فشردن کلید CRITERIA کاربر می تواند شرط مورد نظر را وارد کند . مثلا شماره دانشجویی. حال با فشردن کلید اینتر رکورد مورد نظر یافت شده می توانید داده  متناظر آنرا وارد نمایید. اگر در حالت شرط ( CRITERIA )  باشید بجای این کلمه گزینه FORM فعال است.

 با کلید NEW  می توانید رکورد جدید وارد کنید و با کلید های NEXT , PREV  هم به رکوردهای قبل و بعد وارد میشوید.

آشنایی با تابع Indirect در اکسل - Excel

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

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

بحارالانوار ، دار احیاء الترا العربی ، ج 75، ص(172)

کار تابع indirect اینست که محتویات درونش را که یک آدرس است به تابع بعدی انتقال می دهد تا محتویات ادرس مذکور را نمایش دهد.درحقیقت به صورت غیر مستقیم مقدار یک سلول را انتقال می دهد.

مثلا فرض کنید در سلول g5 نشانی سلول a1 را نوشته اید، حال اگر در یک سلول دیگر فرمول زیر را بنویسید آن سلول محتویات a1 را نشان می دهد:

 

=indirect(g5)                                                        

به تصویر زیر دقت کنید 

 

حل یک مثال با این تابع : محدود های از اعداد را در ستون a تا c داریم و می خواهیم با تغییر اعداد سطر محدوده جمع نیز تغییر کند . فرمول :

=SUM(INDIRECT("a"&G2&":c"&I2))

را می نویسیم تا تابع indirect  سطر 2 تا 10 را در ستون های a تا c به تابع جمع ارسال کند.

 

تزریق یک فایل اکسل به اوت لوک 2010

فرض کنید از شماره تلفن های موبایلتان یک نسخه کپی گرفته اید و می خواهید فایل اکسل آنرا به اوت لوک تزریق کنید تا با استفاده از امکانات آن با استفاده از یک خط تلفن و یک مودم َ با دوستانتان تماس حاصل کنید.

مراحل تزریق(  import ) داده های اکسل به اوت لوک  (    outlook  ):

1- ابتدا اوت لوک 2010 را باز کرده :    file / open / import       

 

 

 گزینه   import from another program or file رااتنخاب می کنیم:

 

 

نوع فایل را اکسل انتخاب می کنیم ( در اوت لوک 2010 هنوز گزینه اکسل 2010 موجود نیست )

 

 

فایل مورد نظر را انتخاب می کنیم.در این مرحله نوع تکرار رکوردها را نیز تعیین می کنیم.

 

 

 

گزینه تماس ها contacts  را انتخاب می کنیم تا داده ها به صورت دفترچه تلفن ذخیره شوند                          

 

 

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

 

 باید مثل اکسس آنها را درگ کرد.

 

 

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

 فرد مورد نظر را جستجو کرده ، سپس شماره انرا از منوی home گزینه more  یافته یا برروی آن دبل کلیک می کنیم.

 برای جستجو هم  کلید های ctrl + e  را استفاده می کنیم.

 

 

 

 

     

 

جمع بستن ساعات ها - اکسل پیشرفته -( تکراری )

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

 

 

برای رهایی از این مشکل کافی است سلول های حاوی جمع را با قالبی دیگر نمایش دهیم . برروی آنها راست کلیک کرده گزینه ی format cells  و number  و  custom بخش  hh:mm  را انتخاب کرده  ، در اطراف hh  یک جفت علامت براکت قرار می دهیم  ([hh]) 

 

منبع : کتاب ترفند های اکسل ( محمد حسن مهدوی ) 

تکرار در اکسل 2010 - repeat all item labels

اگر در اکسل 2010 جدول محوری ترسیم کردید که یک گزارش خلاصه شده از داده های یک جدول را ارائه می کند . شما قادر خواهید بود داده های برچسب های آنرا در هر سطر تکرار کنید .این کار در اکسل 2010 ابراز مناسبی دارد به نام   repeat all item labels در قسمتreport layout / design تب pivottable tools .

 

 

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

 

کافی است جدول را کپی کرده و عملیات فیلتر و یا فرمول نویسی را آغاز کنید .

پر کردن فضای خالی بین سلول های پر - ترفند 23

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

 

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

۱- ابتدا محدوه ی نام را انتخاب کرده

۲- سلول های خالی آنرا انتخاب می کنیم . ( برای اینکار کافی است کلید های ctrl +g را فشرده و سپس گزینه ی blanks  را مطابق تصویر زیر انتخاب کنید )

۳- حال که سلول های خالی انتخاب شدند کافی است به اکسل بفهمانیم که ای سلول ها چگونه باید پر شوند. برای این منظور در همان حالی که سلول های خالی انتخاب شده اند علامت مساوی (=) را فشرده سلول بالایی را که حاوی داده (علی) است را انتخاب می کنیم .

۴- اکنون کافی است که با فشردن کلید     ctrl+enterعملیات را تایید کنیم.