اگر کاربر مبتدی نیستید، حتماً قبلاً متوجه شده اید که 99٪ از همه چیز در اکسل برای کار با جداول عمودی طراحی شده است، جایی که پارامترها یا ویژگی ها (فیلدها) از ستون ها عبور می کنند و اطلاعات مربوط به اشیاء یا رویدادها قرار دارند. در خطوط . جداول محوری، جمع‌های فرعی، کپی کردن فرمول‌ها با دوبار کلیک - همه چیز به طور خاص برای این قالب داده طراحی شده است.
با این حال، هیچ قاعده‌ای بدون استثنا وجود ندارد و با فرکانس نسبتاً منظمی از من می‌پرسند که اگر جدولی با جهت معنایی افقی، یا جدولی که سطرها و ستون‌ها وزن یکسانی دارند، در اثر برخورد کرد، چه باید کرد:
و اگر اکسل هنوز می داند چگونه به صورت افقی مرتب شود (با دستور داده ها – مرتب سازی – گزینه ها – مرتب سازی ستون ها، پس وضعیت فیلتر کردن بدتر است - به سادگی هیچ ابزار داخلی برای فیلتر کردن ستون ها وجود ندارد، نه ردیف ها در اکسل. بنابراین، اگر با چنین کاری روبرو هستید، باید راه‌حل‌هایی با درجات مختلف پیچیدگی پیدا کنید.
روش 1. تابع فیلتر جدید
اگر از نسخه جدید اکسل 2021 یا اشتراک اکسل 365 استفاده می کنید، می توانید از ویژگی جدید معرفی شده استفاده کنید. FILTER (فیلتر)، که می تواند داده های منبع را نه تنها بر اساس ردیف ها، بلکه بر اساس ستون ها نیز فیلتر کند. برای کار کردن، این تابع به یک ردیف آرایه تک بعدی افقی کمکی نیاز دارد، جایی که هر مقدار (TRUE یا FALSE) تعیین می کند که آیا ستون بعدی را در جدول نشان می دهیم یا برعکس، پنهان می کنیم.
بیایید خط زیر را بالای جدول خود اضافه کنیم و وضعیت هر ستون را در آن بنویسیم:
- فرض کنید همیشه می خواهیم اولین و آخرین ستون (سرصفحه ها و مجموع) را نمایش دهیم، بنابراین برای آنها در خانه های اول و آخر آرایه مقدار = TRUE را تعیین می کنیم.
- برای ستون های باقی مانده، محتویات سلول های مربوطه فرمولی خواهد بود که شرایط مورد نیاز ما را با استفاده از توابع بررسی می کند. И (و) or OR (یا). به عنوان مثال، که کل در محدوده 300 تا 500 است.
پس از آن، فقط استفاده از عملکرد باقی می ماند FILTER برای انتخاب ستون هایی که در بالای آن آرایه کمکی ما مقدار TRUE دارد:
به طور مشابه، می توانید ستون ها را بر اساس یک لیست مشخص فیلتر کنید. در این مورد، تابع کمک خواهد کرد COUNTIF (COUNTIF)، که تعداد دفعات نام ستون بعدی را از سرفصل جدول در لیست مجاز بررسی می کند:
روش 2. Pivot table به جای معمول
در حال حاضر، اکسل تنها در جداول محوری، فیلتر افقی توسط ستون‌ها را تعبیه کرده است، بنابراین اگر بتوانیم جدول اصلی خود را به جدول محوری تبدیل کنیم، می‌توانیم از این قابلیت داخلی استفاده کنیم. برای انجام این کار، جدول منبع ما باید شرایط زیر را داشته باشد:
- یک خط سرصفحه یک خطی "صحیح" بدون سلول های خالی و ادغام شده داشته باشید - در غیر این صورت برای ساخت جدول محوری کار نمی کند.
- در برچسب های سطرها و ستون ها موارد تکراری وجود نداشته باشد - آنها در خلاصه به لیستی از مقادیر منحصر به فرد "جمع می شوند".
- فقط شامل اعداد در محدوده مقادیر (در محل تلاقی ردیف ها و ستون ها) باشد، زیرا جدول محوری قطعاً نوعی تابع جمع آوری را به آنها اعمال می کند (جمع، میانگین و غیره) و این با متن کار نمی کند.
اگر همه این شرایط برآورده شوند، برای ساختن یک جدول محوری که شبیه جدول اصلی ما باشد، باید آن را (نسخه اصلی) از جدول متقاطع به یک تخت (نرمال) گسترش دهیم. و ساده ترین راه برای انجام این کار با افزونه Power Query است، یک ابزار قدرتمند تبدیل داده که از سال 2016 در اکسل ساخته شده است.
آنها عبارتند از:
- بیایید جدول را به یک فرمان پویا "هوشمند" تبدیل کنیم صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول).
- بارگیری در Power Query با دستور داده ها - از جدول / محدوده (داده ها - از جدول / محدوده).
- خط را با مجموع ها فیلتر می کنیم (خلاصه مجموع های خاص خود را خواهد داشت).
- روی عنوان ستون اول کلیک راست کرده و انتخاب کنید ستون های دیگر را جمع کنید (لغو محور ستون های دیگر). تمام ستون های انتخاب نشده به دو ستون تبدیل می شوند - نام کارمند و مقدار نشانگر او.
- فیلتر کردن ستون با مجموعات وارد شده به ستون صفت.
- مطابق جدول مسطح (نرمال شده) حاصل با دستور یک جدول محوری می سازیم صفحه اصلی — بستن و بارگذاری — بستن و بارگیری در… (صفحه اصلی — بستن و بارگذاری — بستن و بارگیری به…).
اکنون می‌توانید از قابلیت فیلتر کردن ستون‌های موجود در جدول‌های محوری استفاده کنید - تیک‌های معمول در مقابل نام‌ها و موارد فیلترهای امضا (فیلترهای برچسب) or فیلتر بر اساس مقدار (فیلترهای ارزش):
و البته، هنگام تغییر داده ها، باید پرس و جو و خلاصه ما را با یک میانبر صفحه کلید به روز کنید کلیدهای Ctrl+دگرساز+F5 یا تیم داده - تازه کردن همه (داده ها - بازخوانی همه).
روش 3. ماکرو در VBA
همه روش‌های قبلی، همانطور که به راحتی می‌بینید، دقیقاً فیلتر نمی‌شوند - ما ستون‌ها را در لیست اصلی پنهان نمی‌کنیم، بلکه یک جدول جدید با مجموعه‌ای از ستون‌ها از جدول اصلی تشکیل می‌دهیم. اگر نیاز به فیلتر کردن (پنهان کردن) ستون‌ها در داده‌های منبع باشد، به یک رویکرد اساسی متفاوت، یعنی یک ماکرو، نیاز است.
فرض کنید می‌خواهیم ستون‌هایی را فیلتر کنیم که نام مدیر در هدر جدول، ماسک مشخص‌شده در سلول زرد A4 را برآورده می‌کند، به عنوان مثال، با حرف «A» شروع می‌شود (یعنی «آنا» و «آرتور» را دریافت کنید. " در نتیجه).
مانند روش اول، ابتدا یک ردیف دامنه کمکی پیاده سازی می کنیم که در هر سلول معیار ما با یک فرمول بررسی می شود و مقادیر منطقی TRUE یا FALSE به ترتیب برای ستون های قابل مشاهده و پنهان نمایش داده می شود:
سپس یک ماکرو ساده اضافه می کنیم. روی برگه شیت کلیک راست کرده و command را انتخاب کنید منبع (کد منبع). کد VBA زیر را کپی کرده و در پنجره باز شده پیست کنید:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" سپس برای هر سلول در محدوده ("D2:O2") اگر سلول = True سپس cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = پایان واقعی اگر سلول بعدی پایان اگر پایان فرعی است منطق آن به شرح زیر است:
- به طور کلی، این یک کنترل کننده رویداد است کاربرگ_تغییر، یعنی این ماکرو به طور خودکار در هر تغییری در هر سلول در صفحه فعلی اجرا می شود.
- ارجاع به سلول تغییر یافته همیشه در متغیر خواهد بود هدف.
- ابتدا بررسی می کنیم که کاربر دقیقاً سلول را با معیار (A4) تغییر داده است - این کار توسط اپراتور انجام می شود. if.
- سپس چرخه شروع می شود برای هر… برای تکرار روی سلول های خاکستری (D2:O2) با مقادیر نشانگر TRUE / FALSE برای هر ستون.
- اگر مقدار سلول خاکستری بعدی TRUE (true) باشد، ستون پنهان نمی شود، در غیر این صورت آن را پنهان می کنیم (ویژگی) پنهان).
- توابع آرایه پویا از Office 365: FILTER، SORT و UNIC
- جدول محوری با هدر چند خطی با استفاده از Power Query
- ماکروها چیست، چگونه می توان از آنها استفاده کرد