هنگام کار با جداول اکسل ، اغلب باید آنها را مطابق معیار معینی یا با توجه به چندین شرط انتخاب کنید. این برنامه با استفاده از تعدادی ابزار می تواند این کار را به روش های مختلف انجام دهد. بیایید با استفاده از گزینه های متنوع ، نحوه نمونه گیری در اکسل را دریابیم.
نمونه برداری
انتخاب داده ها شامل روش انتخاب از آرایه عمومی آن نتایج است که شرایط داده شده را برآورده می کند ، با خروجی بعدی آنها روی یک برگه به عنوان یک لیست جداگانه یا در محدوده اصلی.
روش 1: از فیلتر خودکار پیشرفته استفاده کنید
ساده ترین روش برای انتخاب ، استفاده از یک فیلتر خودکار پیشرفته است. در نظر بگیرید که چگونه با یک مثال خاص این کار را انجام دهید.
- منطقه مورد نظر را روی برگه انتخاب کنید ، از بین داده هایی که می خواهید انتخاب کنید. در برگه "خانه" بر روی دکمه کلیک کنید مرتب سازی و فیلتر کردن. در بلوک تنظیمات قرار دارد. "ویرایش". در لیستی که بعد از این باز می شود ، روی دکمه کلیک کنید "فیلتر".
فرصتی برای عمل متفاوت وجود دارد. برای این کار ، پس از انتخاب ناحیه روی صفحه ، به برگه بروید "داده". روی دکمه کلیک کنید "فیلتر"که بر روی نوار در گروه ارسال شده است مرتب سازی و فیلتر کردن.
- پس از این عمل ، نمودارهای تصویری در هدر جدول ظاهر می شوند تا شروع به فیلتر کردن به شکل مثلث های کوچک بصورت وارونه در لبه سمت راست سلول ها شروع کنند. در عنوان ستون که می خواهیم انتخاب کنیم ، روی این آیکون کلیک می کنیم. در منوی باز شده ، به مورد بروید "فیلترهای متن". بعد ، موقعیت را انتخاب کنید "فیلتر سفارشی ...".
- پنجره فیلتر کاربر فعال می شود. در آن می توانید محدودیتی را انتخاب کنید که انتخاب انجام شود. در لیست کشویی ستون که شامل سلولهای قالب عددی است که به عنوان نمونه از آن استفاده می کنیم ، می توانید یکی از پنج نوع شرط را انتخاب کنید:
- برابر با؛
- مساوی نیست؛
- بیشتر؛
- بیشتر یا مساوی؛
- کمتر
بگذارید نمونه ای را به عنوان شرط بگذاریم به گونه ای که فقط مقادیری را انتخاب کنیم که میزان درآمد از آن بیش از 10000 روبل باشد. سوئیچ را در موقعیت قرار دهید بیشتر. مقدار را در قسمت درست وارد کنید "10000". برای انجام یک عمل ، روی دکمه کلیک کنید "خوب".
- همانطور که مشاهده می کنید ، پس از فیلتر ، فقط خط هایی وجود داشته اند که در آن میزان درآمد از 10،000 روبل فراتر رود.
- اما در همین ستون می توانیم شرط دوم را اضافه کنیم. برای این کار ، دوباره به پنجره فیلتر کاربری برمی گردیم. همانطور که مشاهده می کنید ، در قسمت پایین آن یک سوئیچ وضعیت دیگر و فیلد ورودی مربوطه وجود دارد. اجازه دهید اکنون حد بالایی انتخاب را 15،000 روبل تعیین کنیم. برای انجام این کار ، سوئیچ را در حالت قرار دهید کمترو در قسمت سمت راست مقدار را وارد می کنیم "15000".
علاوه بر این ، سوئیچ شرط نیز وجود دارد. او دو مقام دارد "و" و "یا". به طور پیش فرض ، در حالت اول تنظیم می شود. این بدان معنی است که فقط ردیف هایی که هر دو محدودیت را برآورده می کنند ، در نمونه باقی می مانند. در صورت قرار گرفتن "یا"سپس مقادیر متناسب با هر دو شرط وجود خواهد داشت. در مورد ما ، باید سوئیچ را تنظیم کنید "و"، یعنی این تنظیم را به صورت پیش فرض بگذارید. بعد از وارد کردن تمام مقادیر ، روی دکمه کلیک کنید "خوب".
- اکنون در جدول فقط خطوطی وجود دارد که در آن میزان درآمد کمتر از 10000 روبل نیست بلکه از 15000 روبل تجاوز نمی کند.
- به همین ترتیب ، می توانید فیلترها را در ستون های دیگر پیکربندی کنید. در عین حال می توان فیلتر را با توجه به شرایط قبلی که در ستون ها تنظیم شده بود ذخیره کرد. بنابراین ، بیایید ببینیم که چگونه فیلتر برای سلول ها در قالب تاریخ انجام می شود. در ستون مربوطه روی نماد فیلتر کلیک کنید. به طور متوالی روی موارد لیست کلیک کنید "فیلتر براساس تاریخ" و فیلتر سفارشی.
- مجدداً پنجره تأیید خودکار کاربر شروع می شود. ما انتخاب نتایج را در جدول از 4 مه تا 6 مه 2016 به صورت جامع انجام می دهیم. در سوئیچ انتخاب وضعیت ، همانطور که می بینیم ، حتی گزینه های بیشتری نسبت به قالب شماره وجود دارد. موقعیت را انتخاب کنید "بعد یا برابر". در قسمت سمت راست ، مقدار را تنظیم کنید "04.05.2016". در بلوک پایین ، سوئیچ را در موقعیت قرار دهید "به یا مساوی با". مقدار را در قسمت درست وارد کنید "06.05.2016". ما سوئیچ سازگاری شرط را در موقعیت پیش فرض قرار می دهیم - "و". برای اعمال فیلتر در عمل ، روی دکمه کلیک کنید "خوب".
- همانطور که می بینید لیست ما بیشتر کاهش یافته است. اکنون فقط سطرهایی در آن باقی مانده است ، که در آن میزان درآمد بین 10،000 تا 15،000 روبل برای دوره 4 تا 6 مه 2016 متغیر است.
- ما می توانیم فیلتر را در یکی از ستون ها دوباره تنظیم کنیم. ما این کار را برای مقادیر درآمد انجام خواهیم داد. روی ستون مربوطه روی نماد autofilter کلیک کنید. در لیست کشویی ، روی مورد کلیک کنید فیلتر را حذف کنید.
- همانطور که مشاهده می کنید ، پس از این اقدامات ، انتخاب براساس میزان درآمد غیرفعال می شود و فقط انتخاب براساس تاریخ ها باقی می ماند (از تاریخ 05/04/2016 تا 05/06/2016).
- ستون دیگری در این جدول وجود دارد - "نام". این شامل داده هایی در قالب متن است. بیایید ببینیم چگونه می توان با استفاده از فیلتر کردن توسط این مقادیر ، یک انتخاب را ایجاد کرد.
بر روی نماد فیلتر در نام ستون کلیک کنید. اسامی لیست را طی می کنیم "فیلترهای متن" و "فیلتر سفارشی ...".
- مجدداً پنجره تأیید خودکار کاربر باز می شود. بیایید انتخابی را با موارد انجام دهیم "سیب زمینی" و گوشت. در بلوک اول ، سوئیچ شرط را تنظیم کنید "برابر". در قسمت سمت راست آن کلمه را وارد می کنیم "سیب زمینی". سوئیچ بلوک پایین نیز در حالت قرار داده شده است "برابر". در قسمت مقابل آن ، ثبت کنید - گوشت. و سپس آنچه را که قبلاً انجام ندادیم انجام می دهیم: سوئیچ سازگاری شرایط را تنظیم کنید "یا". اکنون یک خط حاوی هر یک از شرایط مشخص بر روی صفحه نمایش داده می شود. روی دکمه کلیک کنید "خوب".
- همانطور که مشاهده می کنید ، در نمونه جدید محدودیت هایی در تاریخ (از 05/04/2016 لغایت 05/06/2016) و با نام (سیب زمینی و گوشت) وجود دارد. هیچ محدودیتی در میزان درآمد وجود ندارد.
- می توانید فیلتر را به همان روشی که برای نصب آن استفاده کرده اید ، کاملاً حذف کنید. علاوه بر این ، فرقی نمی کند که از چه روشی استفاده شده باشد. برای تنظیم مجدد فیلتر ، در برگه بودن "داده" بر روی دکمه کلیک کنید "فیلتر"که در یک گروه قرار می گیرد مرتب سازی و فیلتر کردن.
گزینه دوم شامل رفتن به برگه است "خانه". در آنجا روی دکمه روبان کلیک می کنیم مرتب سازی و فیلتر کردن در بلوک "ویرایش". در لیست فعال شده ، روی دکمه کلیک کنید "فیلتر".
با استفاده از هر دو روش فوق ، فیلتر حذف می شود و نتایج انتخاب پاک می شود. یعنی جدول کلیه داده های موجود را نشان می دهد.
درس: عملکرد تکمیل خودکار در اکسل
روش 2: استفاده از فرمول آرایه
شما همچنین می توانید با استفاده از فرمول آرایه پیچیده انتخابی را انجام دهید. بر خلاف نسخه قبلی ، این روش خروجی نتیجه را در یک جدول جداگانه فراهم می کند.
- در همان برگه ، یک جدول خالی با همان ستون ها در عنوان موجود در عنوان ایجاد کنید.
- تمام سلولهای خالی را در ستون اول جدول جدید انتخاب کنید. مکان نما را در خط فرمول ها قرار می دهیم. فقط در اینجا فرمولی وارد می شود که بر اساس معیارهای مشخص ، گزینشی را تولید می کند. ما خطوطی را انتخاب می کنیم که در آن میزان درآمد از 15000 روبل فراتر رود. در مثال خاص ما فرمول ورودی به شرح زیر خواهد بود:
= INDEX (A2: A29؛ LOW (IF (15000 <= C2: C29؛ STRING (C2: C29)؛ "" "؛ STRING () - STRING ($ 1 $ C)) - STRING (C $ 1 $))به طور طبیعی ، در هر حالت ، آدرس سلول ها و دامنه ها متفاوت خواهد بود. در این مثال می توانید فرمول را با مختصات موجود در تصویر مقایسه کرده و آن را با نیازهای خود تطبیق دهید.
- از آنجا که این فرمول آرایه ای است ، برای استفاده از آن در عمل ، لازم است که دکمه را فشار ندهید وارد شوید، و میانبر صفحه کلید Ctrl + Shift + Enter. ما این کار را می کنیم
- با انتخاب ستون دوم با تاریخ و قرار دادن مکان نما در نوار فرمول ، عبارت زیر را معرفی می کنیم:
= INDEX (B2: B29؛ LOW (IF (15000 <= C2: C29؛ STRING (C2: C29)؛ "" "؛ STRING () - STRING ($ C $ 1)) - STRING (C $ 1 $))میانبر صفحه کلید را فشار دهید Ctrl + Shift + Enter.
- به همین ترتیب ، در ستون با درآمد فرمول را به شرح زیر وارد می کنیم:
= INDEX (C2: C29؛ LOW (IF (15000 <= C2: C29؛ STRING (C2: C29)؛ "" "؛ STRING () - STRING ($ 1 $ C)) - STRING (C $ 1 $))باز هم ، با تایپ میانبر صفحه کلید Ctrl + Shift + Enter.
در هر سه مورد ، فقط مقدار مختصات اول تغییر می کند ، و بقیه فرمول کاملاً یکسان است.
- همانطور که مشاهده می کنید جدول از داده ها پر شده است ، اما ظاهر آن کاملاً جذاب نیست ، علاوه بر این ، مقادیر تاریخ به طور نادرست پر می شوند. نیاز به رفع این نقص ها دارد. تاریخ نادرست است زیرا قالب سلول ستون مربوطه متداول است و ما باید فرمت تاریخ را تنظیم کنیم. کل ستون ، از جمله سلولهای دارای خطا را انتخاب کنید ، و با انتخاب دکمه سمت راست ماوس ، روی انتخاب کلیک کنید. در لیستی که ظاهر می شود ، به بروید "قالب سلول ...".
- در پنجره قالب بندی که باز می شود ، زبانه را باز کنید "شماره". در بلوک "قالب های شماره" مقدار را برجسته کنید تاریخ. در قسمت سمت راست پنجره می توانید نوع دلخواه نمایش تاریخ را انتخاب کنید. پس از تنظیم تنظیمات ، روی دکمه کلیک کنید "خوب".
- اکنون تاریخ به درستی نمایش داده می شود. اما ، همانطور که می بینیم ، کل قسمت پایین جدول پر از سلولهایی است که حاوی یک مقدار نادرست هستند "# شماره!". در حقیقت ، این سلولهایی هستند که داده کافی از نمونه موجود نبود. اگر به هیچ وجه خالی نمایش داده می شد ، جذاب تر می شد. برای این اهداف از قالب بندی مشروط استفاده خواهیم کرد. تمام سلولهای جدول را به جز هدر انتخاب کنید. در برگه بودن "خانه" بر روی دکمه کلیک کنید قالب بندی شرطیواقع در بلوک ابزار سبک ها. در لیستی که ظاهر می شود ، را انتخاب کنید "ایجاد یک قانون ...".
- در پنجره ای که باز می شود ، نوع قانون را انتخاب کنید "فقط سلولهایی را شامل می شوند". در اولین جعبه زیر کتیبه "فقط سلولهایی را تشکیل دهید که شرط زیر برای آنها صحیح باشد" موقعیت را انتخاب کنید "خطاها". بعد روی دکمه کلیک کنید "قالب ...".
- در پنجره قالب بندی که شروع می شود ، به برگه بروید قلم و در قسمت مربوطه ، سفید را انتخاب کنید. پس از این اقدامات ، روی دکمه کلیک کنید "خوب".
- پس از بازگشت به پنجره ، برای ایجاد شرایط ، بر روی دکمه با همان نام کلیک کنید.
اکنون یک نمونه آماده برای محدودیت مشخص شده در یک جدول جداگانه درست و مناسب طراحی کرده ایم.
درس: قالب بندی مشروط در اکسل
روش 3: نمونه گیری با توجه به چندین شرط با استفاده از فرمول
دقیقاً مانند استفاده از فیلتر ، با استفاده از فرمول ، می توانید مطابق با چندین شرط را انتخاب کنید. برای مثال ، ما از همان جدول منبع ، و همچنین یک جدول خالی که در آن نتایج نمایش داده می شود ، با قالب بندی عددی و شرطی قبلاً اجرا شده ، می گیریم. ما حد اول را برای محدوده پایین تر از انتخاب برای درآمد 15،000 روبل و شرط دوم را به حد بالایی 20،000 روبل تعیین می کنیم.
- ما در یک ستون جداگانه وارد شرایط مرزی می شویم.
- مانند روش قبلی ، ستونهای خالی جدول جدید را یکی یکی انتخاب می کنیم و سه فرمول مربوطه را در آنها وارد می کنیم. در ستون اول عبارت زیر را اضافه کنید:
= INDEX (A2: A29؛ LOW (IF ((($ D $ 2 = C2: C29)؛ LINE (C2: C29)؛ "")؛ LINE (C2: C29) -LINE (C $ 1 $) - LINE ($ ج 1 دلار))در ستون های زیر دقیقاً همان فرمول ها را وارد می کنیم و فقط مختصات را بلافاصله پس از نام اپراتور تغییر می دهیم صفحه اول به ستون های مربوطه که به آن احتیاج داریم ، به قیاس با روش قبلی.
هر بار پس از وارد کردن ، فراموش نکنید که یک ترکیب کلیدی را تایپ کنید Ctrl + Shift + Enter.
- مزیت این روش نسبت به روش قبلی این است که اگر می خواهیم مرزهای نمونه را تغییر دهیم ، دیگر نیازی نیست که فرمول آرایه را تغییر دهیم ، که به خودی خود بسیار مشکل ساز است. کافی است در ستون شرایط موجود در برگه تعداد اعداد را به موارد مورد نیاز کاربر تغییر دهید. نتایج انتخاب بلافاصله به طور خودکار تغییر می کند.
روش 4: نمونه گیری تصادفی
در اکسل با استفاده از فرمول ویژه خوشحالم انتخاب تصادفی نیز می تواند اعمال شود. لازم است در برخی از موارد هنگام کار با مقدار زیادی از داده ها تولید شود ، در صورت نیاز به ارائه تصویر کلی بدون تجزیه و تحلیل جامع از کلیه داده ها در آرایه.
- در سمت چپ جدول ، یک ستون را می پریم. در سلول ستون بعدی که در مقابل سلول اول با داده های جدول قرار دارد ، فرمول را وارد می کنیم:
= RAND ()این عملکرد یک عدد تصادفی را نشان می دهد. برای فعال کردن آن ، روی دکمه کلیک کنید وارد کنید.
- به منظور ایجاد یک ستون کامل از اعداد تصادفی ، مکان نما را در گوشه سمت راست پایین سلول که از قبل حاوی فرمول است قرار دهید. نشانگر پر شدن ظاهر می شود. ما آن را با فشار دادن دکمه سمت چپ ماوس که به طور موازی با جدول داده تا انتها فشار داده است ، به پایین می کشیم.
- اکنون ما طیف وسیعی از سلولهای پر از اعداد تصادفی را داریم. اما حاوی فرمول است خوشحالم. ما باید با ارزشهای خالص کار کنیم. برای انجام این کار ، در ستون خالی در سمت راست کپی کنید. طیف وسیعی از سلولها را با اعداد تصادفی انتخاب کنید. در برگه واقع شده است "خانه"بر روی آیکون کلیک کنید کپی کنید روی نوار
- با استفاده از فهرست زمینه ، یک ستون خالی و کلیک راست را انتخاب کنید. در گروه ابزار درج گزینه ها مورد را انتخاب کنید "ارزشها"به صورت تصویری با اعداد به تصویر کشیده شده است.
- پس از آن ، در برگه بودن "خانه"، روی نمادی که قبلاً می شناسیم کلیک کنید مرتب سازی و فیلتر کردن. در لیست کشویی ، انتخاب را متوقف کنید مرتب سازی سفارشی.
- پنجره تنظیمات مرتب سازی فعال می شود. کادر کنار پارامتر را حتماً بررسی کنید "اطلاعات من حاوی سرصفحه ها"اگر کلاه وجود دارد اما بدون علامت چک. در زمینه مرتب سازی بر اساس نام ستون را نشان دهید که حاوی مقادیر کپی شده از اعداد تصادفی است. در زمینه "مرتب سازی" تنظیمات پیش فرض را رها کنید. در زمینه "سفارش" می توانید پارامتر را به عنوان انتخاب کنید "صعود"بنابراین و "نزولی". برای نمونه گیری تصادفی ، این مهم نیست. بعد از انجام تنظیمات ، روی دکمه کلیک کنید "خوب".
- پس از آن ، تمام مقادیر جدول به ترتیب صعودی یا نزولی اعداد تصادفی مرتب می شوند. شما می توانید هر تعداد از اولین سطرها را از جدول (5 ، 10 ، 12 ، 15 و غیره) بگیرید و آنها را می توان نتیجه نمونه گیری تصادفی دانست.
درس: مرتب سازی و فیلتر کردن داده ها در اکسل
همانطور که مشاهده می کنید ، انتخاب در صفحه گسترده اکسل می تواند یا با استفاده از اتوفیلتر یا با استفاده از فرمول های خاص انجام شود. در حالت اول ، نتیجه در جدول اصلی نمایش داده می شود ، و در حالت دوم - در یک منطقه جداگانه. امکان انتخاب وجود دارد ، هم با یک شرط و هم در چند مورد. همچنین می توانید با استفاده از عملکرد به طور تصادفی انتخاب کنید خوشحالم.
