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

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

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

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

 

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

 

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

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

ابزارهایی که برروی فرم ها قرار می گیرند به کامپوننت مشهورند و کاربران می توانند با استفاده از این ابزار ورود وو خروج داده را سهل تر کنند. از جمله این ابزار LISTBOX  است که به کاربرانش اجازه می دهد تا یک لیست را در درونش مشاهده نمایند .در اینجا یک مثال از استفاده این ابزار را به همراه 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 ساخته می شود ( ضربی بجای جمعی )

 

تمرین جدید آموزش وی بی ای اکسل - vba excel 2007

این تمرین ها برای بچه های دوره  vba  قرار داده شده و موضوعش آموزش msgbox , inputbox است

لینک دانلود

لینک درست شده است

کتاب وی بی ای اکسل 2010 - Excel 2010 VBA

به نام خدا

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

Excel 2007 VBA

Programmer’s Reference

John Green

Stephen Bullen

Rob Bovey

Michael Alexander

لینک دانلود

معرفی امکانات جدید اکسل 2012

گروه فعال سایت فرساران بازهم مطلبی جدید را درباره اکسل ۲۰۱۲ ارائه نموده که خواندن آنرا به همه توصیه می کنم.

این مطلب را می توانید از لینک زیر مشاهده کنید.

http://farsaran.ir/Excel%202012%20News

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

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

 

www.farsaran.ir

 

 

ساخت یک گزارش پویا در اکسل 2010

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

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

 حل : ابتدا یک سلول از جدول داه را انتخاب کرده ، از منوی insert  گزینه pivot tableرا انتخاب می کنیم .

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

فیلد ها و منوی pivottable tools  پدیدار می گردد و کاربر قادر است فیلدها را انتخاب  کرده و عملیات محاسباتی خود را تغییر دهد.

 هر جدول محوری دارای 4 بخش است .

1- ستون ها : که داده ها به صورت ستونی در جدول نمایش داده می شوند.

2- report : که اصلی ترین فیلد مجموعه محسوب می گردد و سایر فیلد ها زیر مجموعه ی آن محسوب می گردند.

3- سیگما : یا بخش محاسباتی جدول

4- ردیف ها : که داده ها به صورت سطری در جدول نمایش داده می شوند.

 

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

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

 

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

 

 خروجی جدول بیانگر دریافتی هر فرد طی سال های مورد نظر است .

 

 

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

 

 

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

در تمرین انبار از سه تابع 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 استفاده کرد. برای این منظور تمرین طراحی و با پاسخ در اختیار شما دوستان قرار می دهم تا بیشتر تمرین کنید.

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

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