من به تازگی یک مقاله در نحوه استفاده از توابع خلاصه در اکسل نوشتم که مقادیر زیادی داده را به راحتی خلاصه کرد اما این مقاله تمام داده های مربوط به برگه را در نظر گرفت. اگر شما فقط می خواهید زیر مجموعه ای از داده ها را نگاه کنید و زیر مجموعه داده ها را خلاصه کنید؟
در اکسل، می توانید فیلترهایی را روی ستون هایی که ردیف هایی با فیلتر شما مطابقت نمی کنند ایجاد کنید. علاوه بر این، شما همچنین می توانید از اکسل استفاده کنید تا داده ها را با استفاده از داده های فیلتر شده خلاصه کنید.
در این مقاله، شما را از طریق مراحل برای ایجاد فیلتر در Excel و همچنین استفاده از ساخته شده در
ایجاد فیلترهای ساده در اکسل
در اکسل، شما می توانید فیلترهای ساده و فیلترهای پیچیده ایجاد کنید. بیایید با فیلترهای ساده شروع کنیم. هنگام کار با فیلترها، همیشه باید یک ردیف را در بالای صفحه قرار دهید که برای برچسب ها استفاده می شود.
در بالا، من برخی از داده های جعلی را دارم و من می خواهید یک فیلتر را در ستون شهرایجاد کنید. در اکسل این واقعا آسان است. برو جلو و بر روی زبانه دادهدر نوار کلیک کنید و سپس روی دکمه فیلترکلیک کنید.
هنگامیکه روی Filter کلیک میکنید، هر ستون در ردیف اول به طور خودکار یک دکمه کشویی کوچک اضافه شده در سمت راست درست می شود.
اکنون به جلو بروید و روی فلش کشویی در ستون شهر
در بالای صفحه، می توانید به سرعت تمام سطرها را مرتب کنید مقادیر در ستون شهر. توجه داشته باشید که هنگام مرتب سازی داده ها، کل ردیف، و نه فقط مقادیر ستون شهر را بارگذاری می کنید. این اطمینان حاصل خواهد کرد که اطلاعات شما همانطور که قبلا بود، باقی بماند.
همچنین، بهتر است یک ستون را در قسمت جلو بنام ID بنویسید و شماره آن را از یک تا چند ردیفی که در برگه به این ترتیب، شما همیشه می توانید بر اساس ستون شناسایی مرتب کنید و اطلاعات خود را با همین ترتیب در ابتدا دریافت کنید، در صورتی که برای شما مهم است.
همانطور که می بینید، تمام داده های صفحه گسترده در حال حاضر بر اساس مقادیر ستون شهر مرتب شده اند. تا کنون هیچ ردیف پنهان نیستند. اکنون اجازه دهید نگاهی به جعبه های چک در پایین کادر گفتگوی فیلتر بزنیم. در مثال من فقط سه مقدار منحصر به فرد در ستون شهر وجود دارد و آن سه در لیست نشان داده می شوند.
من پیش رفتم و بدون کنترل دو شهر و یکی را ترک کرد اکنون فقط 8 ردیف داده نشان داده شده است و بقیه پنهان هستند. اگر شماره های ردیف در سمت چپ را بررسی کنید، به راحتی می توانید بگویید که به اطلاعات فیلتر شده نگاه می کنید. بسته به اینکه چند ردیف پنهان شده اند، چند خط افقی اضافی را مشاهده خواهید کرد و رنگ اعداد آبی خواهد بود.
اکنون بگذارید بگوییم میخواهم روی ستون دوم فیلتر کنم تا تعداد بیشتری را کاهش دهد از نتایج در ستون C، تعداد کل اعضا در هر خانواده را می خواهم و می خواهم فقط نتایج را برای خانواده هایی که بیش از دو عضو دارند ببینم.
پیش بروید و روی فلش کشویی در ستون C کلیک کنید و برای هر یک از مقادیر منحصر به فرد در ستون، کادرهای همان نمایش را خواهید دید. با این حال، در این مورد، ما می خواهیم بر روی فیلترهای شمارهکلیک کرده و سپس روی بزرگتر ازکلیک کنید.
پنجره جدیدی باز می شود و در اینجا می توانید مقدار را تایپ کنید برای فیلتر شما همچنین می توانید بیش از یک معیار را با عملکرد AND یا OR اضافه کنید.
اکنون می توانم به شما بگویم که شما می خواهید ردیف هایی که ارزش بیش از 2 و نه برابر 5 باشد. فقط 5 ردیف داده: خانواده ها فقط از نیواورلئان و با 3 یا بیشتر عضو. به حد کافی ساده است؟ توجه داشته باشید که شما میتوانید به راحتی یک فیلد را روی یک ستون پاک کنید و با کلیک بر روی کشویی و سپس روی پیوند پاک کردن فیلتر از "ستونکلیک کنید.
بنابراین در مورد فیلترهای ساده در Excel است. آنها بسیار آسان برای استفاده هستند و نتایج بسیار ساده است.
ایجاد فیلتر های پیشرفته در اکسل
اگر می خواهید فیلتر های پیشرفته تر ایجاد کنید، شما می توانید فیلتر های پیچیده را با استفاده از فیلتر پیشرفتهفیلتر کنید. باید از کادر گفتگوی فیلتر پیشرفتهاستفاده کنید. به عنوان مثال، بگذارید بگوییم می خواهم همه خانواده هایی که در نیواورلئان زندگی می کنند با بیش از 2 عضو در خانواده خود یاتمام خانواده های کلارکسویل با بیش از 3 عضو در خانواده خود ANDتنها آنهایی که دارای آدرس ایمیل پایان .EDUهستند. حالا شما نمیتوانید این کار را با یک فیلتر ساده انجام دهید.
برای انجام این کار، ما باید ورق اکسل را کمی متفاوت تنظیم کنیم.
اکنون می توانید از دو ردیف بالای مجموعه داده خود وارد کنید و برچسب های عنوان را دقیقا به ردیف اول همانند زیر نشان دهید. در اینجا این است که چگونه فیلترهای پیشرفته کار می کنند. ابتدا باید معیارهایتان را در ستونهای بالا قرار دهید و سپس بر روی دکمه Advancedدر زیر مرتب سازی & amp؛Data
بنابراین دقیقا چه می توانیم در آن سلول ها تایپ کنیم؟ خوب، بگذارید با مثال ما شروع کنیم.
هنگام تایپ کردن مقادیر در متن های مختلف، فقط می خواهید داده ها را از نیواورلئان یا کلارکسویل ببینید. ردیف، یعنی OR. اکنون ما خانواده های نیواورلئان را با بیش از دو عضو و خانواده های کلارکسویل که بیش از 3 عضو دارند، می خواهیم. برای انجام این کار در & gt؛ 2در C2 و & gt؛ 3در C3 تایپ کنید.
از آنجا که & gt؛ 2 و نیواورلئان در ردیف یکسان هستند، آن یک عملگر AND خواهد بود. همین امر برای ردیف 3 بالا نیز صادق است. در نهایت، ما فقط خانواده هایی با END آدرس ایمیل END را می خواهیم. برای انجام این کار، فقط *. eduرا در دو D2 و D3 تایپ کنید. نماد * به معنای هر تعداد کاراکتر است.
پس از انجام این کار، در هر کجا در مجموعه داده خود کلیک کنید و سپس روی پیشرفتهرا فشار دهید فیلد لیست ردیفe به طور خودکار از مجموعه داده های شما کشف می شود، زیرا قبل از کلیک روی دکمه پیشرفته، روی آن کلیک کرده اید. در حال حاضر بر روی دکمه کوچک کوچک در سمت راست محدوده معیارکلیک کنید.
همه چیز را از A1 تا E3 انتخاب کنید و سپس بر روی همان دکمه کلیک کنید تا دوباره به کادر جستجوی پیشرفته فیلتر شوید. بر روی OK کلیک کنید و داده های شما باید فیلتر شوند!
همانطور که می بینید، در حال حاضر من تنها 3 نتیجه دارد که با تمام معیارهای آنها مطابقت دارد. توجه داشته باشید که برچسب ها برای محدوده معیار باید دقیقا با برچسب ها برای مجموعه داده ها مطابقت داشته باشند تا بتوانند این کار را انجام دهند.
شما می توانید با استفاده از این روش، پرسش های بسیار پیچیده ای را ایجاد کنید، بنابراین با آن بازی کنید برای به دست آوردن نتایج مورد نظر خود. در نهایت، بیایید درباره استفاده از توابع جمع بندی برای داده های فیلتر شده بحث کنیم.
خلاصه کردن داده های فیلتر شده
حالا بگویم من می خواهم تعداد اعضای خانواده را در داده های فیلتر شده من خلاصه کنم، در مورد انجام این کار؟ خوب، اجازه دهید فیلتر ما را با کلیک بر روی دکمه پاک کردندر نوار تنظیم کنیم. نگران نباشید، فیلتر کردن پیشرفته را بسیار ساده می توان با کلیک کردن بر روی دکمه Advanced و با کلیک کردن دوباره مجدد، بسیار آسان است.
پایین مجموعه داده هایمان، اجازه دهید یک سلول به نام Totalاضافه کنیم و سپس یک تابع جمع اضافه کنیم تا کل اعضای خانواده را جمع کنیم. در مثال من، من = SUM (C7: C31)را تایپ کردم.
بنابراین اگر نگاه کنم همه خانواده ها، من 78 اعضای کل. اکنون بروید و فیلتر پیشرفته خود را دوباره ببندید و ببینید چه اتفاقی می افتد.
به جای نشان دادن شماره درست، 11، من هنوز هم کل را 78 را می بینم! چرا اینطور است؟ خوب، عملکرد SUM رد پنهان را نادیده می گیرد، بنابراین هنوز محاسبات را با استفاده از تمام ردیف انجام می دهد. خوشبختانه، چندین توابع وجود دارد که میتوانید از آنها برای نادیده گرفتن ردیفهای پنهان استفاده کنید.
اولین SUBTOTALاست. قبل از استفاده از هر یک از این ویژگی های خاص، شما می خواهید فیلتر خود را پاک کنید و سپس تابع را تایپ کنید.
هنگامی که فیلتر پاک شود، پیش بروید و = SUBTOTAL (
در مثال ما می خواهم از SUM، بنابراین من در شماره 9 تایپ می کنم یا فقط از آن بر روی آن کلیک کرده و سپس یک کاما را وارد کنید و سلول را انتخاب کنید.
هنگامی که شما وارد می کنید، باید مقدار 78 را همانند قبل ببینید. با این حال، اگر شما فیلتر را دوباره دوباره اعمال کنید، ما 11 را می بینیم.
عالی است این دقیقا همان چیزی است که ما می خواهیم. حالا شما می توانید فیلتر های خود را تنظیم کنید و ارزش همیشه فقط ردیف هایی که در حال نمایش هستند را نشان می دهد.
تابع دوم که بسیار دقیق عمل می کند همانند عملکرد SUBTOTAL AGGREGATEاست. تفاوت این است که پارامتر دیگری در عملکرد AGGREGATE وجود دارد که در آن شما باید مشخص کنید که می خواهید ردیف های پنهان را نادیده بگیرید.
اولین پارامتر تابع summation شما می خواهید استفاده کنید و به عنوان با SUBTOTAL، 9 تابع SUM را نشان می دهد. گزینه دوم، جایی است که شما باید 5 را تایپ کنید تا رد پنهان را نادیده بگیرید. آخرین پارامتر یکسان است و دامنه سلول ها است.
شما همچنین می توانید مقاله من در توابع خلاصه را بخوانید تا جزئیات بیشتری در مورد از تابع AGGREGATE استفاده کنید و سایر توابع مانند MODE، MEDIAN، AVERAGE و غیره را مطالعه کنید.
امیدوارم این مقاله به شما یک نقطه شروع خوب برای ایجاد و استفاده از فیلترها در اکسل می دهد. اگر سوالی دارید، لطفا نظر خود را بنویسید لذت ببرید!