فیلتر کردن داده ها در اکسل توسط کد نویسی VBA
سلام . به سافت پلاس خوش آمدید . امروز می خواهیم با هم در خصوص فیلتر کردن داده ها در اکسل صحبت کنیم . مطمئنا با کلمه و اصطلاح فیلتر کردن به خوبی آشنایی دارید.
البته من و شما شاید از کلمه فیلتر و فیلتر کردن در بعضی جاها دل خوشی نداشته باشیم . اما فیلتر کردن در اکسل در خیلی از جاها می تواند به ما کمک کند فقط باید با نحوه استفاده از این قابلیت به خوبی آشنا شویم .
فیلتر کردن یعنی جدا کردن یا سوا کردن . در دنیای واقعی ما هر موقع که می خواهیم دو ماده را از هم جدا کنیم معمولاً به سراغ وسیله ای به اسم فیلتر – کلمه معادل فیلتر در فارسی صافی – می رویم.
در نرم افزار اکسل هم ما وقتی بخواهیم یک اطلاعات خاص را از بین انبوه اطلاعات دیگر جدا کنیم به سراغ قابلیتی به نام Filtering می رویم و با استفاده از آن اطلاعات مورد نظر خود را از بقیه جدا می کنیم .
فیلترها در اکسل انواع و اقسام مختلفی دارند و به طرق مختلف می توانیم آنها را ایجاد کرده و از آنها استفاده کنیم . فیلتر بر اساس اعداد ، فیلتر متن ها فیلتر کردن بر اساس تاریخ و فیلتر کردن بر اساس رنگها .
اینها تنها نمونه هایی از انواع فیلترها هستند که در اکسل وجود دارند اما اینکه چه جوری در اکسل می توانیم فیلترها را ایجاد کنیم چیزی نیست که بخواهیم امروز به آن بپردازیم .
در عوض می خواهیم ببینیم که با استفاده از برنامه نویسی VBA چه جوری می شود فیلترها را ایجاد و از آنها استفاده کرد .
اگر تا انتهای این مطلب همراه من باشید متوجه خواهیم شد که :
۱-با انواع فیلترها در محیط اکسل آشنا شوید .
۲- به راحتی بتوانید با استفاده از کد نویسی VBA فیلترهای خود را ایجاد کنید .
۳- دیگر به سراغ استفاده از فیلترهای خود اکسل نروید و همه فیلترهای خود را با استفاده از کد نویسی ایجاد کنید .
من علیرضا شهرآئینی هستم . می خواهم این آموزش را بهانه ای قرار بدهم که دانش و سواد خود را در زمینه فیلتر کردن داده ها در اکسل با هم به اشتراک بگذاریم .پس تا پایان این مطلب با من همراه شوید .
بررسی روش های فیلتر کردن داده ها در اکسل توسط کد نویسی وی بی ای
در اکسل ما فیلترهای مختلف و متنوعی داریم ما در اینجا می خواهیم با استفاده از برنامه نویسی VBA تمامی این فیلترها را کد نویسی کنیم تا به راحتی بتوانیم هر موقع که به این فیلترها احتیاج داریم آنها را اجرا کنیم .
برای شروع کار اطلاعات جدول زیر را در نظر بگیرید .
فرآیند فیلتر کردن داده ها در اکسل با استفاده از متدی به نام AutoFilter شروع می شود .
پس برای شروع کار باید با متدی به نام AutoFilter در برنامه نویسی وی بی ای آشنا شویم . این متد دارای ۶ ورودی می باشد . در اینجا به بررسی همه این شش ورودی یا آرگومان برای این متد می پردازیم .
rem پارامتر های متد اتوفیلتر ( Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown .
آیتم اول Expression
این آیتم جزو آرگومان های این دستور نیست . Expression در واقع همان محدوده ای است که می خواهیم اطلاعات آن را به نجوی فیلتر کنیم .پس Expression شد آدرس یک محدوده از سلول ها .
حالا وارد بررسی آرگومان های این تابع می شویم .
آرگومان اول : Field.
این آرگومان در واقع شماره ستونی است که اولا این ستون در محدوده مورد نظری که در آیتم معرفی کرده ایم وجود دارد و در ثانی می خواهیم بر اساس اطلاعات موجود در آن فرآیند فیلتر کردن اطلاعات را انجام دهیم .
وجود این آرگومان در ساختار این متد اختیاری است .
آرگومان دوم : Criteria1.
Criteria یعنی شرط. در ساختار این دستور Criteria آن چیزی است که بر مبنای آن می خواهیم بر اساس آن اطلاعات خود را فیلتر کنیم . وجود این آرگومان نیز در ساختار این دستور اختیاری است .
آرگومان سوم : Operator.
این آرگومان در حالتی به کار می آید که بخواهید از آرگومان بعدی نیز در ساختار این دستور استفاده کنید . در واقع با استفاده از این آرگومان و مقادیری که در داخل آن قرار می دهیم می توانیم دو شرط خود را با هم ترکیب کنیم .
پس با این حساب وجود این آرگومان نیز در ساختار این دستور اختیاری است.
آرگومان چهارم : Criteria2.
این ورودی نیز شرط دومی است که برای فیلتر مورد نظر خود می توانیم در نظر بگیریم. این آرگومان نیز در ساختار این متد اختیاری است .
آرگومان پنجم : 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
Criteria1="پرینتر ", =:Operator:=xlOr, Criteria2:="موبایل"Range("A2:d16").AutoFilter Field:=2
End Sub
در اینجا ما به دنبال اطلاعات مربوط به دو کالا هستیم .پس قاعدتا باید از دو تا شرط همزمان استفاده کنیم . برای اینکه شرط دوم را در ساختار این دستور وارد کنیم نیاز به بخش اپراتور داریم .
اما اپراتور چیست ؟
اپراتورهای عبارت هایی هستند که با استفاده از آنها می توانیم بیشتر از یک شرط را برای خود تعریف کنیم . در این دستور ما در مجموع ۱۱ اپراتور مختلف داریم که می توانیم از آنها در جاهای مناسب استفاده کنیم .
اکر می خواهید به لیست همه این اپراتورها دست پیدا کنید می توانید به سایت مایکروسافت مراجعه کنید .
به عنوان مثال در دستور بالا ما از اپراتوری به نام XlOr استفاده کردیم . این اپراتور به معنای حرف ” یا ” می باشد و این مفهوم را می رساند که کالاهایی به اسم ” موبایل ” یا ” پرینتر ” را برای ما فیلتر کند .
اینجا ما با مبانی و اصول فیلتر کردن داده ها در اکسل آشنا شدیم . در آینده مثال های عملی تر و کاربردی از کار با فیلترها را با هم یاد خواهیم گرفت .
مطالب زیر را حتما مطالعه کنید
ذخیره نمودارها با کدهای VBAبصورت تصویر+راهنمای گام به گام
لیست باکس ها در برنامه نویسی VBA ؛ تمام چیزی که باید بدانید .
۷ اشتباه در کدنویسی VBA که ممکن است شما هم مرتکب شوید ؟
توی این آموزش راجع به اشتباهاتی که ممکن است در برنامه نویسی VBA مرتکب آنها شوید صحبت می کنیم .
با این خطای Runtime Error 1004 در VBA چکار کنیم ؟
در این آموزش راجع به خطایی به اسم Run time error 1004 در برنامه نویسی وی بی ای با هم صحبت می کنیم .
چطور با کدهای VBA اطلاعات را از اکسس به ورد بفرستیم؟
کلمه کلیدی set و کاربردهای آن در برنامه نویسی VBA
4 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام
اگر من بخام بگم به غیر از تلویزیون همه رو فیلتر کن باید چطور بنویسم؟
می تونید از اپراتور not like استفاده کنید
سلام بخواهیم مثلا از یک ستون کلماتی که حرف آخرشون ح هست رو فیلتر کنیم و بیاد تو یه ستون دیگه باید چکار کرد
درود بر شما
من میخوام دادههامو که یک ستون از اعداد هستن و میخوام این ستون رو مثلا بین دوشرط ۲۰۰ تا ۵۰۰ فیلتر کنم و این دوشرط من (۲۰۰ تا ۵۰۰) در دو تا سلول نوشته شدن آدرس این دو سلول رو چطور باید تعریف کنم.
خودم دادهها رو براساس دوشرط از طریق فیلتر انجام دادم و ماکروی اونو ضبط کردم و بجای دوتا عدد یعنی دو شرط آدرس سلولها رو دادم ولی جواب نمیده لطفا راهنمایی بفرمایید باسپاس