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