فیلتر کردن داده ها در اکسل توسط کد نویسی VBA
سلام . به سافت پلاس خوش آمدید . امروز می خواهیم با هم در خصوص فیلتر کردن داده ها در اکسل صحبت کنیم . مطمئنا با کلمه و اصطلاح فیلتر کردن به خوبی آشنایی دارید.
در نرم افزار اکسل هم ما وقتی بخواهیم یک اطلاعات خاص را از بین انبوه اطلاعات دیگر جدا کنیم به سراغ قابلیتی به نام Filtering می رویم و با استفاده از آن اطلاعات مورد نظر خود را از بقیه جدا می کنیم .
فیلترها در اکسل انواع و اقسام مختلفی دارند و به طرق مختلف می توانیم آنها را ایجاد کرده و از آنها استفاده کنیم . فیلتر بر اساس اعداد ، فیلتر متن ها فیلتر کردن بر اساس تاریخ و فیلتر کردن بر اساس رنگها .
اینها تنها نمونه هایی از انواع فیلترها هستند که در اکسل وجود دارند اما اینکه چه جوری در اکسل می توانیم فیلترها را ایجاد کنیم چیزی نیست که بخواهیم امروز به آن بپردازیم چون قبلا در داخل مطلب فیلتر کردن در اکسل چطور اطلاعات اکسل را فیلتر کنیم؟ به صورت کامل راجع به انها با هم صحبت کردم .
در عوض می خواهیم ببینیم که با استفاده از برنامه نویسی VBA چه جوری می شود فیلترها را ایجاد و از آنها استفاده کرد .
اگر تا انتهای این مطلب همراه من باشید متوجه خواهیم شد که :
۱-با انواع فیلترها در محیط اکسل آشنا شوید .
۲- به راحتی بتوانید با استفاده از کد نویسی VBA فیلترهای خود را ایجاد کنید .
۳- دیگر به سراغ استفاده از فیلترهای خود اکسل نروید و همه فیلترهای خود را با استفاده از کد نویسی ایجاد کنید .
من علیرضا شهرآئینی هستم . می خواهم این آموزش را بهانه ای قرار بدهم که دانش و سواد خود را در زمینه فیلتر کردن داده ها در اکسل با هم به اشتراک بگذاریم .پس تا پایان این مطلب با من همراه شوید .
فهرست محتوا
روش های فیلتر کردن داده ها در اکسل توسط کد های وی بی ای
در اکسل ما فیلترهای مختلف و متنوعی داریم ما در اینجا می خواهیم با استفاده از برنامه نویسی VBA تمامی این فیلترها را کد نویسی کنیم تا به راحتی بتوانیم هر موقع که به این فیلترها احتیاج داریم آنها را اجرا کنیم .
برای شروع کار اطلاعات جدول زیر را در نظر بگیرید .
فرآیند فیلتر کردن داده ها در اکسل با استفاده از متدی به نام AutoFilter شروع می شود .
پس برای شروع کار باید با متدی به نام AutoFilter در برنامه نویسی وی بی ای آشنا شویم . این متد دارای ۶ ورودی می باشد . در اینجا به بررسی همه این شش ورودی یا آرگومان برای این متد می پردازیم .
.
Expression. AutoFilter( _Field_ , _Criteria1_ ,
_Operator_ , _Criteria2_ , _VisibleDropDown)
.
آیتم اول Expression
این آیتم جزو آرگومان های این دستور نیست . Expression در واقع همان محدوده ای است که می خواهیم اطلاعات آن را به نجوی فیلتر کنیم .پس Expression شد آدرس یک محدوده از سلول ها .
حالا وارد بررسی آرگومان های این تابع می شویم .
آرگومان 1: Field
این آرگومان در واقع شماره ستونی است که اولا این ستون در محدوده مورد نظری که در آیتم معرفی کرده ایم وجود دارد و در ثانی می خواهیم بر اساس اطلاعات موجود در آن فرآیند فیلتر کردن اطلاعات را انجام دهیم .
وجود این آرگومان در ساختار این متد اختیاری است .
آرگومان 2: criteria1
Criteria یعنی شرط. در ساختار این دستور Criteria آن چیزی است که بر مبنای آن می خواهیم بر اساس آن اطلاعات خود را فیلتر کنیم . وجود این آرگومان نیز در ساختار این دستور اختیاری است .
آرگومان 3: operator
این آرگومان در حالتی به کار می آید که بخواهید از آرگومان بعدی نیز در ساختار این دستور استفاده کنید . در واقع با استفاده از این آرگومان و مقادیری که در داخل آن قرار می دهیم می توانیم دو شرط خود را با هم ترکیب کنیم .
پس با این حساب وجود این آرگومان نیز در ساختار این دستور اختیاری است.
آرگومان 4: criteria 2
این ورودی نیز شرط دومی است که برای فیلتر مورد نظر خود می توانیم در نظر بگیریم. این آرگومان نیز در ساختار این متد اختیاری است .
آرگومان 5: visibledropdown
با استفاده از این آرگومان می توانیم تعیین کنیم که آیا آیکون مربوط به فیلتر داده ها در محدوده مورد نظر ما نمایش داده شود .
این آرگومان دو مقدار True یا False می تواند به خود بگیرد .
اگر دقت کنید تمامی آرگومان ها و ورودی های این دستور حالت اختیاری دارند .
حالا به سراغ استفاده از این دستور رفته و آرگومانهای مربوط به آنها را یک به یک به کار خواهیم برد تا با نحوه استفاده و کاربرد آنها در عمل آشنا شویم .
بررسی مثال های کاربردی از فیلتر کردن اطلاعات
بررسی مثال ها را از ساده ترین حالت شروع می کنیم .
۱-نمایش آیکون های مربوط به فیلتر کردن
در قدم اول می خواهیم فقط آیکون مربوط به فیلتر کردن داده ها را نمایش دهیم . برای شروع کار کد زیر را در نظر بگیرید .
Private Sub CommandButton1_Click()
Range("A1").AutoFilter
End Sub
این خط از کد چه کاری برای ما انجام می دهد .؟
در این خط با استفاده از یک کد ساده فقط آیکون مربوط به فیلتر کردن را به سر ستون های خود اضافه می کنیم و لی هیچ فرآیند فیلتر کردنی انجام نمی شود . در اینجا فرآیند فیلتر کردن را به خود کاربر می سپاریم .
من این کدها را در داخل یک دکمه قرار داده ام که با کلیک بر روی این دکمه این کدها اجرا شده و نتیجه در اکسل نمایش داده می شود .
۲-فیلتر کردن متن ها بر اساس یک شرط .
در اینجا می خواهیم برای ستون مربوط به نام کالاهای خود یک فیلتر در نظر بگیریم . برای این کار به کدهای زیر نگاه کنید
Private Sub CommandButton2_Click()
range("A2:D16").AutoFilter Field:=2, Criteria1:="تلویزیون"
End Sub
خوب حالا برویم یک نگاه دقیقتری به این کدها بیاندازیم.
در قدم اول محدوده مورد نظر خود را برای فیلتر کردن مشخص می کنیم در اینجا محدوده مورد نظر ما بین سلول های A2 تا D16 می باشد . دقت می کنید که این آدرس در نقش همان Expression است که قبلا به آن اشاره کردم .
کمی در این دستور جلوتر برویم اولین پارامتری که به آن مقدار دهی کرده ایم پارامتر Filed است و اگر در تصویر دقت کنید ما چهار ستون از اطلاعات را داریم .
حالا در کدام یک از این ستون ها می خواهیم عملیات فیلتر را پیاده سازی کنیم . ؟ پاسخ ستون مربوط به نام کالاهاست . این ستون در بین ستون های ۴ گانه چندمین ستون است ؟
اگر دقت کنید این ستون دومین ستون از مجموعه ستون های موجود است . پس در پارامتر Filed شماره ستون مورد نظر یعنی عدد ۲ را درج می کنیم .
در انتها به پارامتر Criteria می رسیم . در این پارامتر تعیین می کنیم که فیلتر کردن داده های این ستون بر اساس چه عبارتی باشد . فقط دقت باید کرد که اگر شرط ما بصورت یک متن است باید آن را در داخل دو دابل کوتیشن قرار دهیم.
در اینجا ما عبارت شرط مورد نظر را در داخل کدهای خود قرار دادیم . اشکالی که این کار دارد این است که :
۱- این شرط برای همیشه ثابت است و نمی توان دیگر کالاها به غیر از تلویزیون را فیلتر کرد .
۲- اگر بخواهیم شرط خود را تغییر دهیم باید به محیط کد نویسی برویم و از انجا شرط را تغییر دهیم . در صورتی که ما نمی خواهیم مدام به محیط کد نویسی برویم یا اینکه به کاربر اجازه دسترسی به کدهای خود را بدهیم .
باید کاری کنیم که برنامه شرط مورد نظر خود را به نحوی از کاربر دریافت کند بدون اینکه کاربر به کدهای ما دسترسی داشته باشد .
به نظر شما برای این کار چه روش یا روش هایی وجود دارد ؟
خوب در اینجا اولین حالت از فیلتراسیون داده ها را با موفقیت انجام دادیم . حالا به سراغ حالت های دیگری از این دستور می رویم .
فیلتر کردن متن ها با دو شرط همزمان
فرض کنید که بخواهیم بطور همزمان مشخصات و اطلاعات مربوط به دو کالا را فیلتر کنیم . بعنوان مثال اگر بخواهیم دو اطلاعات مربوط به دو کالای ” موبایل ” و ” پرینتر ” را در جدول بالا بطور همزمان فیلتر کنیم از کدهای زیر استفاده می کنیم .
Private Sub CommandButton2_Click()
range("A2:d16").AutoFilter Field:=2 Criteria1:="پرینتر ", Operator:=xlOr, Criteria2:="موبایل"
End Sub
در اینجا ما به دنبال اطلاعات مربوط به دو کالا هستیم .پس قاعدتا باید از دو تا شرط همزمان استفاده کنیم . برای اینکه شرط دوم را در ساختار این دستور وارد کنیم نیاز به بخش اپراتور داریم .
اما اپراتور چیست ؟
اپراتورهای عبارت هایی هستند که با استفاده از آنها می توانیم بیشتر از یک شرط را برای خود تعریف کنیم . در این دستور ما در مجموع ۱۱ اپراتور مختلف داریم که می توانیم از آنها در جاهای مناسب استفاده کنیم .
اکر می خواهید به لیست همه این اپراتورها دست پیدا کنید می توانید به سایت مایکروسافت مراجعه کنید .
به عنوان مثال در دستور بالا ما از اپراتوری به نام XlOr استفاده کردیم . این اپراتور به معنای حرف ” یا ” می باشد و این مفهوم را می رساند که کالاهایی به اسم ” موبایل ” یا ” پرینتر ” را برای ما فیلتر کند .
اینجا ما با مبانی و اصول فیلتر کردن داده ها در اکسل آشنا شدیم . در آینده مثال های عملی تر و کاربردی از کار با فیلترها را با هم یاد خواهیم گرفت .
به نظرتان این اموزش چقدر برای شما کاربردی و مفید بود .
خوشحال می شوم که نظر خودتان را در قسمت دیدگاههای این مطلب داشته باشم تا از نظر شما هم برای تکمیل تر کردن این مطلب استفاده کنم .
امیدوارم که باز هم شما را در سافت پلاس ببینم .
مطالب زیر را حتما مطالعه کنید
ذخیره نمودارها با کدهای VBAبصورت تصویر+راهنمای گام به گام
لیست باکس ها در برنامه نویسی VBA ؛ تمام چیزی که باید بدانید .
۷ اشتباه در کدنویسی VBA که ممکن است شما هم مرتکب شوید ؟
توی این آموزش راجع به اشتباهاتی که ممکن است در برنامه نویسی VBA مرتکب آنها شوید صحبت می کنیم .
با این خطای Runtime Error 1004 در VBA چکار کنیم ؟
در این آموزش راجع به خطایی به اسم Run time error 1004 در برنامه نویسی وی بی ای با هم صحبت می کنیم .
چطور با کدهای VBA اطلاعات را از اکسس به ورد بفرستیم؟
کلمه کلیدی set و کاربردهای آن در برنامه نویسی VBA
4 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام
اگر من بخام بگم به غیر از تلویزیون همه رو فیلتر کن باید چطور بنویسم؟
می تونید از اپراتور not like استفاده کنید
سلام بخواهیم مثلا از یک ستون کلماتی که حرف آخرشون ح هست رو فیلتر کنیم و بیاد تو یه ستون دیگه باید چکار کرد
درود بر شما
من میخوام دادههامو که یک ستون از اعداد هستن و میخوام این ستون رو مثلا بین دوشرط ۲۰۰ تا ۵۰۰ فیلتر کنم و این دوشرط من (۲۰۰ تا ۵۰۰) در دو تا سلول نوشته شدن آدرس این دو سلول رو چطور باید تعریف کنم.
خودم دادهها رو براساس دوشرط از طریق فیلتر انجام دادم و ماکروی اونو ضبط کردم و بجای دوتا عدد یعنی دو شرط آدرس سلولها رو دادم ولی جواب نمیده لطفا راهنمایی بفرمایید باسپاس