گرفتن اطلاعات در مایکروسافت اکسل
هنگام کار با صفحات گسترده اکسل، اغلب لازم است آنها را بر اساس یک معیار مشخص یا در شرایط مختلف انتخاب کنید. این برنامه می تواند با استفاده از تعدادی از ابزارها به طرق مختلف انجام دهد. بیایید بیاموزیم چگونه با استفاده از گزینه های مختلف در اکسل نمونه برداری کنیم.
محتوا
نمونه برداری
نمونه گیری داده ها شامل روش انتخاب از آرایه کلی از نتایج که مطابق با شرایط مشخص شده است، با خروجی بعدی آنها بر روی یک ورق در یک لیست جداگانه یا در محدوده اولیه است.
روش 1: از اتوفیلتر پیشرفته استفاده کنید
ساده ترین راه برای انتخاب این است که از اتوفیلتر پیشرفته استفاده کنید. در نظر بگیرید چگونه این کار را با یک مثال خاص انجام دهید.
- منطقه ای را در ورق، در میان داده هایی که می خواهید نمونه را انتخاب کنید. در تب "Home" روی دکمه "مرتب کردن و فیلتر کردن" کلیک کنید. این در جعبه تنظیمات "ویرایش" قرار دارد. در لیستی که بعد از این باز می شود، روی دکمه "فیلتر" کلیک کنید.
![فعال کردن فیلتر در مایکروسافت اکسل]()
ممکن است متفاوت انجام شود. برای انجام این کار، پس از انتخاب منطقه در ورق، به برگه "Data" بروید. ما بر روی دکمه "فیلتر" کلیک میکنیم، که بر روی نوار در گروه مرتب سازی و فیلتر گذاشته می شود .
- پس از این عمل، آیکون ها در عنوان جدول ظاهر می شوند تا فیلتر شوند به شکل مثلث های کوچک که در لبه سمت راست سلول ها قرار می گیرند. روی این آیکون در عنوان ستون کلیک کنید که ما می خواهیم انتخاب کنیم. در منوی شروع، به آیتم "فیلترهای متن" بروید . بعد، آیتم "فیلتر سفارشی ..." را انتخاب کنید .
- پنجره فیلتر سفارشی فعال شده است. ممکن است محدودیتی را که انتخاب می شود، تنظیم کنید. در لیست کشویی ستون حاوی سلول های قالب بندی شماره، که ما به عنوان مثال به عنوان مثال استفاده می کنیم، می توانید یکی از پنج نوع شرایط را انتخاب کنید:
- برابر است؛
- برابر نیست
- بیشتر؛
- بزرگتر یا برابر؛
- کمتر
به عنوان مثال، بگذارید شرایطی را تنظیم کنیم تا فقط مقادیری را انتخاب کنیم که درآمدشان بیش از 10،000 روبل باشد. سوئیچ را به موقعیت "بیشتر" تنظیم کنید . مقدار "10،000" را در حاشیه سمت راست وارد کنید. برای انجام یک عمل، بر روی دکمه "OK" کلیک کنید.
- همانطور که می بینید، پس از فیلتر کردن، تنها خطوط وجود دارد که در آن میزان درآمد بیش از 10000 روبل است.
- اما در همان ستون می توانیم شرط دوم را اضافه کنیم. برای انجام این کار، به پنجره فیلتر سفارشی بروید. همانطور که می بینید، در بخش پایین تر آن یک سوئیچ وضعیت دیگر و فیلد ورودی مربوطه وجود دارد. اکنون اجازه می دهیم که حداکثر 15000 روبل انتخاب شود. برای انجام این کار، سوئیچ را به موقعیت "کمتر" تنظیم کنید و مقدار "15000" را در قسمت سمت راست وارد کنید.
علاوه بر این، شرایط سوئیچ وجود دارد. او دارای دو موقعیت "AND" و "OR . " به طور پیش فرض، آن را به موقعیت اول تنظیم شده است. این به این معنی است که تنها ردیفهایی که هر دو محدودیت را برآورده می کنند در انتخاب باقی خواهند ماند. اگر آن را به موقعیت "OR" تنظیم شده ، سپس مقادیری را برای هر یک از دو شرایط مناسب خواهد بود. در مورد ما، شما باید سوئیچ را به موقعیت " AND " تنظیم کنید ، یعنی این تنظیم پیش فرض را ترک کنید. پس از وارد کردن تمام مقادیر، بر روی دکمه OK کلیک کنید.
- در حال حاضر جدول تنها خطوط است که در آن مقدار درآمد کمتر از 10000 روبل نیست، اما از 15000 روبل تجاوز نمی کند.
- به طور مشابه، شما می توانید فیلتر ها را در ستون های دیگر پیکربندی کنید. در همان زمان، همچنین می توانید فیلترینگ را با شرایط قبلی که در ستون ها مشخص شده اند، ذخیره کنید. بنابراین، بیایید ببینیم چگونه انتخاب با استفاده از فیلتر برای سلول ها در فرمت تاریخ ساخته می شود. روی نماد فیلتر در ستون مربوطه کلیک کنید. به طور متوالی بر روی موارد موجود در لیست "فیلتر بر اساس تاریخ" و "فیلتر سفارشی" کلیک کنید .
- پنجره سفارشی خودکار دوباره شروع می شود. یک انتخاب از نتایج را در جدول 4 تا 6 می 2016 انجام دهید. در حالت انتخاب سوئیچ، همانطور که می بینید، گزینه های بیشتری از فرم فرمت وجود دارد. موقعیت "بعد یا برابر" را انتخاب کنید. در قسمت سمت راست، مقدار "05/04/2016" را تنظیم کنید . در بلوک پایین، سوئیچ را به موقعیت "To یا Equal" تنظیم کنید . مقدار "06/05/2016" را در قسمت سمت راست وارد کنید. سوئیچ سازگاری شرایط در حالت پیش فرض باقی می ماند - "AND" . برای استفاده از فیلتر کردن در عمل، بر روی دکمه "OK" کلیک کنید.
- همانطور که می بینید، لیست ما حتی بیشتر شده است. در حال حاضر فقط خطوط در آن باقی مانده است، که در آن میزان درآمد از 10، 000 تا 15000 روبل برای دوره از 04.05 تا 05.06.2016 شامل می شود.
- ما می توانیم فیلتر کردن را در یکی از ستونها تنظیم مجدد کنیم. این کار را برای مقادیر درآمد انجام دهید. روی نماد اتوفیلتر در ستون مربوطه کلیک کنید. در لیست کشویی، بر روی آیتم «حذف فیلتر» کلیک کنید.
- همانطور که می بینید، پس از این اقدامات، نمونه بر اساس میزان درآمد غیرفعال خواهد شد، و تنها انتخاب با تاریخ انجام می شود (از 04.05.2016 تا 06.05.2016).
- در این جدول یک ستون دیگر وجود دارد - "نام" . این شامل اطلاعات در قالب متن می باشد. بیایید ببینیم که چگونه با استفاده از این مقادیر با فیلتر کردن یک نمونه ایجاد کنیم.
روی نماد فیلتر در نام ستون کلیک کنید. پیوسته با نام لیست "فیلترهای متن" و "فیلتر سفارشی ..." بروید .
- پنجره اتوفیلتر کاربر دوباره باز می شود. بیایید انتخاب نام "سیب زمینی" و "گوشت" را انتخاب کنیم . در بلوک اول، ما وضعیت سوئیچ وضعیت را به موقعیت "برابر" . در قسمت سمت راست او کلمه "سیب زمینی" را وارد کنید. ما همچنین سوئیچ بلوک پایین را در موقعیت "Equal" قرار داده ایم. در زمینه مقابل او ما یک ورودی - "گوشت" را می سازیم. و سپس ما آنچه را که قبلا انجام نداده ایم انجام می دهیم: سوئیچ سازگاری را به موقعیت "OR" تنظیم می کنیم . در حال حاضر خط حاوی هر یک از شرایط مشخص شده بر روی صفحه نمایش داده می شود. روی دکمه "OK" کلیک کنید.
- همانطور که می بینید، در نمونه جدید محدودیت هایی در تاریخ (از 04/05/2016 تا 06/05/2016) و نام (سیب زمینی و گوشت) وجود دارد. هیچ محدودیتی در میزان درآمد وجود ندارد.
- شما می توانید فیلتر را با استفاده از روش های مشابه که برای نصب آن استفاده می شود، کاملا حذف کنید. و مهم نیست که چه روش استفاده شد برای بازنشانی فیلتر کردن، داشتن در تب "داده"، بر روی دکمه "فیلتر" کلیک کنید، که در گروه "مرتب سازی و فیلتر" واقع شده است.
![تمیز کردن فیلتر در مایکروسافت اکسل]()
گزینه دوم شامل تعویض به صفحه اصلی است . در اینجا ما روی نوار روی دکمه "مرتب کردن و فیلتر کردن" در بلوک "ویرایش" کلیک میکنیم. در لیست فعال شده، بر روی دکمه "فیلتر" کلیک کنید.
هنگام استفاده از هر دو روش فوق، فیلتر کردن حذف خواهد شد و نتایج نمونه پاک خواهد شد. به این معنی که جدول تمام آرایه ای از داده ها را نشان می دهد.
درس: عملکرد فیلتر خودکار در اکسل
روش 2: استفاده از فرمول آرایه
شما همچنین می توانید با استفاده از فرمول آرایه پیچیده، انتخاب کنید. بر خلاف نسخه قبلی، این روش نتیجه خروجی یک جدول جداگانه را فراهم می کند.
- در همین ورق، یک جدول خالی با همان نام ستون در هدر به عنوان کد منبع ایجاد کنید.
- تمام سلول های خالی را در ستون اول جدول جدید انتخاب کنید. مکان نما را در نوار فرمول قرار دهید. فقط در اینجا فرمول وارد می شود، نمونه برداری بر اساس معیارهای مشخص شده. ما خطوط را انتخاب می کنیم، مقدار درآمد که بیش از 15000 روبل است. در مثال خاص ما، فرمول شما وارد خواهد شد به شرح زیر است:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))به طور طبیعی، در هر مورد آدرس سلول ها و محدوده ها متفاوت خواهد بود. در این مثال، شما می توانید فرمول را با مختصات در تصویر مقایسه کنید و آن را با نیازهای خود سازگار کنید.
- از آنجا که این یک فرمول آرایه است، برای اعمال آن در عمل، شما باید دکمه Enter را فشار دهید، اما ترکیب Ctrl + Shift + Enter را فشار دهید. ما این کار را انجام می دهیم
- انتخاب ستون دوم با تاریخ و تنظیم مکان نما در نوار فرمول، عبارت زیر را وارد کنید:
=ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))کلید ترکیبی Ctrl + Shift + Enter را فشار دهید .
- به طور مشابه، در ستون با درآمد ما فرمول زیر را وارد کنید:
=ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))باز هم کلید ترکیبی Ctrl + Shift + Enter را تایپ می کنیم.
در هر سه مورد، تنها مقدار اول مختصات تغییر می کند و بقیه فرمول ها کاملا یکسان هستند.
- همانطور که می بینید، جدول با داده ها پر شده است، اما ظاهر آن بسیار جذاب نیست، علاوه بر این، مقادیر تاریخ به درستی آن را پر می کنند. لازم است این نواقص را اصلاح کنیم. تاریخ نادرست به دلیل این واقعیت است که قالب سلول ها در ستون مربوطه رایج است و ما باید قالب تاریخ را تنظیم کنیم. کل ستون را، از جمله سلول هایی با خطا، انتخاب کرده و با انتخاب دکمه راست موس را کلیک کنید. در لیست ظاهر می شود، به قلم "Cell cell ..." بروید .
- در پنجره قالب بندی که باز می شود، برگه "شماره" را باز کنید. در " بلوک شماره" بلوک ، مقدار "تاریخ" را انتخاب کنید. در قسمت سمت راست پنجره، می توانید نوع دلخواه مورد نظر خود را انتخاب کنید. پس از تنظیم تنظیمات، بر روی دکمه "OK" کلیک کنید.
- اکنون تاریخ به درستی نمایش داده می شود. اما، همانطور که می بینیم، قسمت پایین تر از جدول با سلول هایی که دارای مقدار اشتباه "#NUM!" هستند پر می شود . در واقع، این ها سلول هایی هستند که اطلاعات کافی از نمونه نداشتند. اگر آنها در همه خالی نمایش داده شود جذاب تر خواهد بود. برای این منظور، از قالب بندی شرطی استفاده می کنیم. تمام سلولهای جدول را انتخاب کنید، به جز هدر. در حالی که در تب "Home"، روی دکمه "Formatting Conditional" کلیک کنید، که در نوار ابزار "Styles" قرار دارد . در لیستی که ظاهر می شود، عنصر "ایجاد یک قانون ..." را انتخاب کنید .
- در پنجره ای که باز می شود، نوع حکومت را "Format only cells that contains" را انتخاب کنید . در فیلد اول زیر عنوان "فقط سلول هایی را که شرایط زیر را انجام می دهند را فرمت کنید "، موقعیت "Errors" را انتخاب کنید. بعد، روی دکمه "Format ..." کلیک کنید.
- در پنجره قالب بندی که شروع می شود، به برگه "Font" بروید و رنگ سفید را در قسمت مربوطه انتخاب کنید. پس از این اقدامات، بر روی دکمه OK کلیک کنید.
- پس از بازگشت به پنجره تهویه، بر روی دکمه با همان نام مشابه کلیک کنید.
اکنون یک نمونه آماده برای محدودیت مشخص شده در یک جدول جداگانه به درستی مرتب شده ایم.
روش 3: با استفاده از فرمول چندین مورد را انتخاب کنید
درست همانطور که با استفاده از یک فیلتر، با استفاده از فرمول، می توانید با چندین شرایط نمونه برداری کنید. به عنوان مثال، بیایید تمام جدول منبع و همچنین یک جدول خالی را که نتایج آن نمایش داده می شود، با قالب بندی عددی و شرطی که قبلا انجام شده است، برداریم. اولین حد در حد پایین تر انتخاب برای درآمد 15000 روبل را تنظیم کنید، و دومین حد بالاترین حد 20،000 روبل است.
- ما در یک ستون جداگانه شرایط مرزی برای نمونه وارد می کنیم.
- همانطور که در روش قبلی، به نوبه خود، ستون های خالی جدول جدید را انتخاب کرده و سه فرمول مربوطه را در آنها وارد کنید. در ستون اول عبارت زیر را وارد کنید:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))در ستون های بعدی ما دقیقا همان فرمول را وارد می کنیم، فقط با تغییر مختصات بلافاصله پس از نام اپراتور INDEX به ستون های مربوطه که ما نیاز داریم، به روش مشابه با روش قبلی.
هر بار پس از ورود، فراموش نکنید که کلید ترکیبی Ctrl + Shift + Enter را تایپ کنید .
- مزیت این روش در مقایسه با قبلی، این است که اگر ما می خواهیم مرزهای نمونه را تغییر دهیم، ما نباید به صورت خود فرمول آرایه را تغییر دهیم، که به خودی خود کاملا مشکل ساز است. کافی است که اعداد مرزی را در ستون شرایط ورق به اعداد مورد نیاز کاربر تغییر دهید. نتایج انتخاب بلافاصله به طور خودکار تغییر خواهند کرد.
روش 4: نمونه گیری تصادفی
در اکسل، نمونه گیری تصادفی نیز می تواند با استفاده از فرمول ویژه RLS انجام شود . در هنگام کار با مقادیر زیادی از داده ها، در بعضی موارد لازم است در صورت نیاز به ارائه یک تصویر کلی بدون تجزیه و تحلیل جامع از تمام داده ها در آرایه باشد.
- به سمت چپ جدول، یک ستون را پر کنید در سلول ستون بعدی که در مقابل سلول اول با داده ها در جدول قرار دارد، فرمول را وارد کنید:
=СЛЧИС()این تابع یک عدد تصادفی را نمایش می دهد. برای فعال کردن آن، دکمه ENTER را فشار دهید.
- برای ایجاد یک ستون کامل از اعداد تصادفی، مکان نما را در گوشه پایین سمت راست سلول قرار دهید که قبلا شامل فرمول است. نشانگر پر شدن ظاهر می شود. با کلیک بر روی دکمه سمت چپ ماوس به موازات جدول با داده ها به انتهای آن برسید.
- حالا تعداد سلول هایی که با اعداد تصادفی پر شده اند. اما، این شامل فرمول پرونده است . ما باید با ارزشهای خالص کار کنیم. برای انجام این کار، به سمت ستون خالی سمت راست کپی کنید. طیف وسیعی از سلول ها با اعداد تصادفی را انتخاب کنید. پس از حل و فصل در صفحه اصلی ، ما بر روی آیکون کپی روی نوار کلیک کنید.
- ستون خالی را انتخاب کنید و با کلیک راست موس، با کلیک بر روی منوی زمینه. در نوار ابزار «Insert Parameters»، گزینه «ارزش ها» را به عنوان یک نماد با عدد نشان داده می شود.
- پس از آن، در حال برگزیدن در برگه «خانه» ، ما بر روی نماد «مرتب سازی و فیلتر کردن» که قبلا برای ما آشناست کلیک میکنیم. در لیست کشویی انتخاب بر روی مورد «مرتبسازی سفارشی» را متوقف کنید.
- پنجره تنظیمات مرتبسازی فعال شده است در صورتی که یک سرصفحه وجود دارد، قبل از گزینه "اطلاعات من حاوی هدر" تیک بزنید، اما علامت چک وجود ندارد. در فیلد "مرتب سازی بر اساس" ، نام ستون حاوی مقادیر کپی شده از اعداد تصادفی را نشان می دهد. در قسمت "مرتب سازی" گزینه های پیش فرض را ترک کنید. در قسمت "سفارش" می توانید گزینه "صعودی" یا "نزولی" را انتخاب کنید . برای یک نمونه تصادفی، مهم نیست. پس از تنظیمات، بر روی دکمه "OK" کلیک کنید.
- پس از آن، تمام مقادیر جدول مرتب شده اند به ترتیب صعودی یا نزولی اعداد تصادفی. شما می توانید هر تعداد از خطوط اول را از جدول (5، 10، 12، 15 و غیره) ببرید و می توانید نتیجه یک نمونه تصادفی در نظر گرفته شود.
درس: مرتب کردن و فیلتر کردن داده ها در اکسل
همانطور که می بینید، نمونه در صفحه گسترده اکسل می تواند با استفاده از یک اتوفیلتر یا با استفاده از فرمول های خاص ساخته شود. در اولین مورد، نتیجه در جدول اصلی نمایش داده می شود، و در مرحله دوم - در یک منطقه جداگانه. یک فرصت برای انتخاب وجود دارد، هر دو در یک شرایط، و در چند. علاوه بر این، می توان با استفاده از تابع RLIS نمونه گیری تصادفی انجام داد.