هنگام کار با جداول اکسل، اغلب لازم است که انتخاب را بر روی یک معیار خاص یا چندین شرایط انجام دهید. این برنامه می تواند این کار را به روش های مختلف با استفاده از یک سری از ابزارها انجام دهد. بیایید پیدا کنیم که چگونه یک نمونه را در Excele با استفاده از گزینه های مختلف انجام دهیم.
نمونه برداری
نمونه داده شامل روش انتخاب از کل مجموعه ای از آن نتایج است که شرایط مشخص شده را برآورده می کنند، به دنبال خروجی آنها بر روی ورق یک لیست جداگانه یا در محدوده منبع است.روش 1: Autofilt Extended را اعمال کنید
ساده ترین راه برای انتخاب استفاده از یک فانوس پیشرفته است. در نظر بگیرید که چگونه این کار را در یک مثال خاص انجام دهید.
- منطقه را بر روی ورق انتخاب کنید، در میان داده هایی که می خواهید نمونه ای را ایجاد کنید. در برگه اصلی، روی دکمه «مرتب سازی و فیلتر» کلیک کنید. این در بلوک تنظیمات ویرایش قرار می گیرد. در لیست که پس از این لیست باز می شود، روی دکمه "فیلتر" کلیک کنید.
فرصتی برای انجام و متفاوت وجود دارد. برای انجام این کار، پس از انتخاب منطقه در ورق، ما به برگه "داده" حرکت می کنیم. بر روی دکمه "فیلتر" کلیک کنید، که بر روی نوار در گروه مرتب سازی قرار داده شده است.
- پس از این عمل در هدر جدول، Pictograms به نظر می رسد شروع به فیلتر کردن در قالب لبه های مثلث کوچک در لبه سمت راست سلول ها. با کلیک بر روی این آیکون در عنوان این ستون، با توجه به آن ما مایل به ایجاد یک نمونه. در منوی منو در حال اجرا، از طریق «فیلترهای متن» بروید. بعد، موقعیت "فیلتر قابل تنظیم ..." را انتخاب کنید.
- پنجره فیلتر کردن کاربر فعال شده است. در آن، شما می توانید محدودیتی را که انتخاب آن انجام می شود تنظیم کنید. در لیست کشویی برای ستون از سلول فرمت عددی، که ما برای مثال استفاده می کنیم، می توانید یکی از پنج نوع شرایط را انتخاب کنید:
- برابر است؛
- نا برابر؛
- بیشتر؛
- بیشتر یا برابر؛
- کوچکتر.
بیایید شرایط را به عنوان مثال تنظیم کنیم تا تنها مقادیری را انتخاب کنیم که میزان درآمد بیش از 10،000 روبل است. ما یک سوئیچ به موقعیت "بیشتر" ایجاد می کنیم. در قسمت راست، ارزش "10،000" را برآورده می کند. برای انجام این عمل، روی دکمه "OK" کلیک کنید.
- همانطور که می بینیم، پس از تصفیه، تنها خطوط باقی مانده، که در آن میزان درآمد بیش از 10،000 روبل است.
- اما در همان ستون ما می توانیم شرایط دوم را اضافه کنیم. برای انجام این کار، به پنجره فیلتراسیون سفارشی بروید. همانطور که می بینیم، در قسمت پایین آن، یک سوئیچ دیگر و زمینه مربوط به ورودی وجود دارد. اجازه دهید ما در حال حاضر مرز انتخاب بالا را از 15000 روبل نصب کنیم. برای انجام این کار، سوئیچ را به موقعیت "کمتر" تنظیم کنید، و در زمینه سمت راست مناسب ارزش "15000".
علاوه بر این، هنوز یک سوئیچ شرایط وجود دارد. او دارای دو مقررات "و" و "یا" است. به طور پیش فرض، آن را در موقعیت اول نصب شده است. این به این معنی است که تنها خطوط در نمونه باقی خواهند ماند که هر دو محدودیت را برآورده می کنند. اگر به موقعیت "یا" تنظیم شود، مقادیر مناسب برای هر یک از دو شرایط باقی خواهد ماند. در مورد ما، شما باید سوئیچ را به موقعیت "و" تنظیم کنید، یعنی این تنظیم پیش فرض را ترک کنید. پس از تمام مقادیر وارد شده، روی دکمه OK کلیک کنید.
- در حال حاضر تنها خطوط در جدول باقی مانده است، که در آن میزان درآمد کمتر از 10،000 روبل نیست، اما از 15000 روبل عبور نمی کند.
- به طور مشابه، شما می توانید فیلترها را در ستون های دیگر پیکربندی کنید. ممکن است فیلتر کردن و شرایط قبلی که در ستون ها تنظیم شده است، حفظ شود. بنابراین، بیایید ببینیم که چگونه انتخاب با استفاده از فیلتر برای فرمت تاریخ گرفته شده است. روی نماد فیلتراسیون در ستون مربوطه کلیک کنید. به طور مداوم با کلیک بر روی لیست "فیلتر تا تاریخ" و فیلتر.
- پنجره Custom Autofilt Run دوباره شروع می شود. انجام انتخاب نتایج در جدول از 4 تا 6 می 2016 شامل. در سوئیچ انتخاب، همانطور که می توانید ببینید، گزینه های حتی بیشتر از فرمت عددی. موقعیت "بعد یا برابر" را انتخاب کنید. در حوزه سمت راست، مقدار "04.05.2016" را تنظیم کنید. در بلوک پایین، ما سوئیچ را به موقعیت "تا یا برابر" تنظیم می کنیم. در قسمت راست، مقدار "06.05.2016" را وارد کنید. سوئیچ سازگاری وضعیت را در موقعیت پیش فرض ترک کنید - "و". به منظور اعمال فیلتر کردن در عمل، دکمه "OK" را فشار دهید.
- همانطور که می بینید، لیست ما حتی بیشتر کاهش یافته است. در حال حاضر تنها خطوط در آن قرار دارند، که در آن میزان درآمد از 10،000 تا 15،000 روبل برای دوره از 04/05 تا 05/05/2016 متغیر است.
- ما می توانیم فیلترینگ را در یکی از ستون ها بازنشانی کنیم. بیایید آن را برای ارزش های درآمد انجام دهیم. بر روی آیکون Autofilter در ستون مربوطه کلیک کنید. در لیست کشویی، روی «حذف فیلتر» کلیک کنید.
- همانطور که می بینید، پس از این اقدامات، نمونه ای از درآمد حاصل از درآمد غیر فعال خواهد شد، اما تنها انتخاب را انتخاب می کند (از تاریخ 04/05/2016 تا 05/05/2016).
- این جدول ستون دیگری دارد - "نام". این شامل داده ها در فرمت متن است. بیایید ببینیم چگونه یک نمونه را با استفاده از فیلتر کردن از طریق این مقادیر تشکیل دهیم.
بر روی نماد فیلتر به نام ستون کلیک کنید. به طور پیوسته به نام فیلتر های متن لیست و "فیلتر قابل تنظیم ..." بروید.
- پنجره Autofilter کاربر دوباره باز می شود. بیایید نمونه ای را با نام "سیب زمینی" و "گوشت" بسازیم. در بلوک اول، شرایط را به موقعیت "برابر" تنظیم می کند. در این زمینه به سمت راست آن را به کلمه "سیب زمینی" مناسب است. سوئیچ واحد پایین تر نیز موقعیت "برابر" را قرار می دهد. در میدان مخالف آن، من یک رکورد - "گوشت" را ایجاد می کنم. و اکنون ما آنچه را که قبلا انجام نداده ایم انجام می دهیم: تنظیم سازگاری را به موقعیت "یا" تنظیم کنید. در حال حاضر خط حاوی هر یک از شرایط مشخص شده بر روی صفحه نمایش نمایش داده می شود. روی دکمه "OK" کلیک کنید.
- همانطور که می بینید، محدودیت های تاریخ در نمونه جدید (از تاریخ 04/05/05 تا 2016/06/05) و نام (سیب زمینی و گوشت) وجود دارد. هیچ محدودیتی در میزان درآمد وجود ندارد.
- شما می توانید فیلتر را به طور کامل حذف کنید با همان روش هایی که برای نصب آن استفاده می شود. و مهم نیست که روش مورد استفاده قرار گیرد. برای تنظیم مجدد فیلتر کردن، در حالی که در برگه "Data"، روی دکمه "فیلتر" کلیک کنید، که در گروه "مرتب سازی و فیلتر" قرار دارد.
گزینه دوم شامل انتقال به برگه "HOME" است. ما روی نوار روی دکمه "مرتب سازی و فیلتر" در واحد ویرایش کلیک می کنیم. در لیست فعال، روی دکمه "فیلتر" کلیک کنید.
هنگام استفاده از هر یک از دو روش فوق، فیلتر کردن حذف خواهد شد و نتایج نمونه پاک می شود. به این ترتیب، جدول کل مجموعه ای از داده ها را نشان می دهد.
درس: عملکرد فیلتر خودکار در اکسل
روش 2: استفاده از فرمول آرایه
انتخاب را نیز می توانید فرمول پیچیده آرایه را اعمال کنید. در مقایسه با نسخه قبلی، این روش برای خروجی نتیجه در یک جدول جداگانه فراهم می کند.
- در همان ورق، ما یک جدول خالی را با نام های مشابه ستون ها در هدر به عنوان منبع ایجاد می کنیم.
- تمام سلول های خالی ستون اول جدول جدید را تخصیص دهید. مکان نما را در رشته فرمول نصب کنید. فقط در اینجا توسط یک فرمول وارد می شود که یک نمونه را بر روی معیارهای مشخص تولید می کند. ما خط را انتخاب خواهیم کرد، میزان درآمد که بیش از 15000 روبل است. در مثال خاص ما، فرمول معرفی شده به شرح زیر است:
= شاخص (A2: A29؛ کوچکترین (اگر (15000)
به طور طبیعی، در هر مورد خاص، آدرس سلول ها و محدوده ها شما خواهد بود. در این مثال، می توانید فرمول را با مختصات بر روی تصویر مقایسه کنید و آن را برای نیازهای خود تطبیق دهید.
- از آنجا که این یک فرمول آرایه است، به منظور اعمال آن در عمل، شما باید دکمه ENTER را فشار دهید، اما Ctrl + Shift + ترکیب کلید را وارد کنید. ما انجامش میدیم.
- با توجه به ستون دوم با تاریخ و نصب مکان نما در رشته فرمول، ما بیان زیر را معرفی می کنیم:
= شاخص (B2: B29؛ کوچکترین (اگر (15000)
روی Ctrl + SHIFT + کلید ترکیبی کلید را وارد کنید.
- به طور مشابه، در یک ستون با درآمد، فرمول محتوای زیر را وارد کنید:
= شاخص (C2: C29؛ کوچکترین (اگر (15000)
باز هم، Ctrl + Shift + ترکیب کلید را وارد کنید.
در هر سه مورد، تنها اولین مقدار مختصات در حال تغییر است، و بقیه فرمول کاملا یکسان است.
- همانطور که می بینیم، جدول با داده ها پر شده است، اما ظاهر آن کاملا جذاب نیست، علاوه بر این، مقادیر تاریخ ها به اشتباه پر شده است. لازم است این کاستی ها را اصلاح کنید. نادرستی تاریخ مربوط به این واقعیت است که فرمت سلول های ستون مربوطه رایج است و ما باید فرمت تاریخ را تنظیم کنیم. ما کل ستون را برجسته می کنیم، از جمله سلول ها با خطاها، و بر روی دکمه ی سمت راست کلیک راست کلیک کنید. در لیست که به نظر می رسد، از طریق "فرمت سلول ..." بروید.
- در پنجره قالب بندی باز می شود، برگه "شماره" را باز کنید. در فرمت های "فرمت های عددی"، مقدار "تاریخ" را تخصیص می دهند. در سمت راست پنجره، می توانید نوع صفحه نمایش دلخواه را انتخاب کنید. بعد از اینکه تنظیمات نمایش داده می شود، روی دکمه "OK" کلیک کنید.
- در حال حاضر تاریخ به درستی نمایش داده می شود. اما، همانطور که می بینیم، کل پایین جدول با سلول هایی که حاوی مقدار اشتباه "# شماره!" هستند پر شده است. در اصل، اینها این سلول ها هستند، داده ها از نمونه ای که به اندازه کافی ندارند. اگر آنها در همه خالی نمایش داده شوند، جذاب تر خواهد بود. برای این اهداف، از قالب بندی شرطی استفاده می کنیم. ما تمام سلول های جدول را برجسته می کنیم، به جز هدر. در حالی که در برگه خانه، روی دکمه "قالب بندی شرطی" کلیک کنید، که در بلوک ابزار "سبک" است. در لیست که ظاهر می شود، مورد "ایجاد قانون ایجاد ..." را انتخاب کنید.
- در پنجره ای که باز می شود، نوع قانون را انتخاب کنید "فقط سلول هایی که حاوی" هستند. در فیلد اول، تحت کتیبه "فرمت فقط سلول هایی که شرایط زیر،" موقعیت "خطا را انتخاب کنید. بعد، روی دکمه "فرمت ..." کلیک کنید.
- در پنجره قالب بندی در حال اجرا، به برگه "فونت" بروید و سفید را در فیلد مناسب انتخاب کنید. پس از این اقدامات، روی دکمه "OK" کلیک کنید.
- بر روی دکمه با دقیقا همان نام، پس از بازگشت به پنجره Heading Conduction کلیک کنید.
در حال حاضر ما یک نمونه به پایان رسید در حد مشخص شده در یک جدول جداگانه به درستی تزئین شده.
درس: قالب بندی مشروط در اکسل
روش 3: نمونه برداری در شرایط مختلف با استفاده از فرمول
درست همانطور که هنگام استفاده از فیلتر، با استفاده از فرمول، می توانید در شرایط مختلف انتخاب کنید. به عنوان مثال، همان جدول منبع را، و همچنین یک جدول خالی، جایی که نتایج خروجی خواهد بود، با قالب بندی عددی و شرطی انجام شده است. ما اولین محدودیت مرز پایین تر انتخاب را با درآمد 15000 روبل و شرایط دوم مرز بالای 20،000 روبل ایجاد خواهیم کرد.
- در یک ستون جداگانه، شرایط مرزی برای نمونه برداری وارد کنید.
- همانطور که در روش قبلی، به طور متناوب ستون های خالی جدول جدید را تخصیص دهید و سه فرمول مربوطه را در آنها وارد کنید. در ستون اول ما بیان زیر را معرفی می کنیم:
= شاخص (A2: A29؛ کوچکترین (($ 2 $ 2 = C2: C29)؛ خط (C2: C29)؛ "")؛ رشته (C2: C29) -STRKOK ($ c $ 1)) - خط ($ c $ 1))
در ستون های بعدی، دقیقا همان فرمول های مشابه را فقط با تغییر مختصات بلافاصله پس از نام اپراتور، شاخص به ستون های مربوطه که ما نیاز داریم، به صورت مشابه با روش قبلی، مناسب است.
هر بار پس از وارد شدن، فراموش نکنید که Ctrl + Shift + ترکیب کلید را وارد کنید.
- مزیت این روش قبل از قبلی این است که اگر ما می خواهیم مرزهای نمونه را تغییر دهیم، لازم نیست که فرمول جامد را تغییر دهیم، که به خودی خود کاملا مشکل ساز است. این به اندازه کافی در ستون شرایط روی ورق کافی است تا اعداد مرزی را بر روی کسانی که توسط کاربر مورد نیاز است تغییر دهید. نتایج انتخاب بلافاصله به طور خودکار تغییر خواهد کرد.
روش 4: نمونه گیری تصادفی
در تبعید، با کمک یک فرمول خاص، همچنین ممکن است انتخاب تصادفی اعمال شود. در برخی موارد در هنگام کار با مقدار زیادی از داده ها، زمانی که شما نیاز به ارائه یک تصویر مشترک بدون تجزیه و تحلیل جامع از تمام داده های آرایه، در برخی موارد مورد نیاز است.
- به سمت چپ جدول یک ستون را پر می کند. در سلول ستون بعدی، که در مقابل سلول اول با داده های جدول قرار دارد، فرمول را وارد کنید:
= چسب ()
این ویژگی یک عدد تصادفی را نمایش می دهد. به منظور فعال کردن آن، روی دکمه Enter کلیک کنید.
- به منظور ایجاد یک ستون کامل از اعداد تصادفی، مکان نما را به گوشه پایین سمت راست سلول، که قبلا شامل فرمول است، تنظیم کنید. نشانگر پر شدن ظاهر می شود. من آن را با دکمه سمت چپ ماوس به طور موازی با جدول با داده ها تا پایان آن کشش می دهم.
- در حال حاضر ما طیف وسیعی از سلولهای پر شده با اعداد تصادفی داریم. اما، او حاوی فرمول Calc است. ما باید با مقادیر تمیز کار کنیم. برای انجام این کار، به یک ستون خالی در سمت راست کپی کنید. محدوده سلول ها را با اعداد تصادفی انتخاب کنید. واقع در برگه "HOME"، روی نماد "کپی" روی روبان کلیک کنید.
- ما یک ستون خالی را برجسته می کنیم و روی راست کلیک کلیک کردیم، منوی زمینه را فراخوانی می کنیم. در نوار ابزار "پارامترهای قرار داده شده"، بند "value" را انتخاب کنید، به عنوان پیکتوگرام با اعداد نشان داده شده است.
- پس از آن، در حالی که در برگه "HOME"، روی نماد آشنا از آیکون "مرتب سازی و فیلتر" کلیک کنید. در لیست کشویی، انتخاب را در مورد "مرتب سازی سفارشی" متوقف کنید.
- پنجره تنظیمات مرتب سازی فعال شده است. اطمینان حاصل کنید که یک تیک را در مقابل پارامتر نصب کنید "داده های من حاوی سرفصل ها" اگر کلاه در دسترس باشد، اما هیچ کنه. در فیلد "مرتب سازی بر اساس"، نام آن ستون را مشخص کنید که در آن مقادیر کپی شده از اعداد تصادفی موجود است. در قسمت "مرتب سازی"، تنظیمات پیش فرض را ترک کنید. در فیلد "سفارش"، شما می توانید پارامتر را به عنوان "صعودی" و "نزولی" انتخاب کنید. برای یک نمونه تصادفی، این مقدار ندارد پس از تنظیمات تولید، بر روی دکمه "OK" کلیک کنید.
- پس از آن، تمام مقادیر جدول به ترتیب صعودی یا کاهش اعداد تصادفی ساخته می شوند. شما می توانید هر تعداد از خطوط اول از جدول (5، 10، 12، 15، و غیره) را مصرف کنید و آنها می توانند نتیجه یک نمونه تصادفی را در نظر بگیرند.
درس: مرتب سازی و فیلتر کردن داده ها به اکسل
همانطور که می بینید، نمونه در جدول اکسل می تواند تولید شود، هر دو با استفاده از Autofilter و استفاده از فرمول های خاص. در اولین مورد، نتیجه در جدول منبع نمایش داده می شود، و در دوم - در یک منطقه جداگانه. ممکن است انتخاب شود، هر دو یک شرط و چند. علاوه بر این، شما می توانید یک نمونه تصادفی با استفاده از عملکرد چسب ایجاد کنید.