هنگامی که با یک مجموعه داده بزرگ کار می کنید، اگر بدانید چگونه به سرعت آنچه را که نیاز دارید پیدا کنید، بهره وری بیشتری خواهید داشت. خوشبختانه، اکسل چند توابع دارد که می تواند در این مورد کمک کند.
خلاصه عناوین
- پیدا کردن
- SORT
- فیلتر
- INDEX
- مسابقه
- XLOOKUP
هنگامی که با یک مجموعه داده بزرگ کار می کنید، اگر بدانید چگونه به سرعت آنچه را که نیاز دارید پیدا کنید، بهره وری بیشتری خواهید داشت. خوشبختانه، اکسل چند توابع دارد که می تواند در این مورد کمک کند.
1 پیدا کنید
تابع FIND در اکسل موقعیت عددی یک کاراکتر یا رشته (توالی از کاراکترها) را در یک رشته بزرگتر برمی گرداند. این برای استخراج متن دقیق، دستکاری، و قالب بندی شرطی مفید است.
نحو تابع FIND به صورت زیر است:
FIND(search_text, text_to_search_in, [start_position])
در اینجا search_text کاراکتر یا زیررشته ای است که می خواهید پیدا کنید و text_to_search_in رشته متنی بزرگتری است که می خواهید جستجو کنید. پارامتر start_position مشخص می‌کند که می‌خواهید تابع از کجا شروع به جستجو در رشته کند.
هر پارامتر در پرانتز اختیاری است.
در اینجا مثالی از نحوه عملکرد تابع FIND در عمل آورده شده است:
=FIND("12345", "Order # 12345 - Completed")
فرمول بالا 9 را برمی گرداند، زیرا رشته فرعی 12345 در آن موقعیت شروع می شود.
تابع FIND به حروف بزرگ و کوچک حساس است. بین حروف بزرگ و کوچک تمایز قائل می شود. به عنوان مثال، اگر حرف A را در apples زیر رشته جستجو کنید، با خطا مواجه می شوید.
اگر می‌خواهید با نادیده گرفتن حساسیت حروف کوچک و بزرگ، چیزی پیدا کنید، به جای آن از تابع SEARCH استفاده کنید. این سینتکس مشابه تابع FIND دارد.
=SEARCH(search_text, text_to_search_in, [start_position])
2 مرتب سازی
اگر بخواهید داده ها را در یک محدوده به ترتیب صعودی یا نزولی مرتب کنید تا یافتن داده های خاص در برگه اکسل خود را آسان تر کنید، عملکرد SORT می تواند کمک کند.
نحو تابع SORT به صورت زیر است:
SORT(range_to_sort, [sort_index], [sort_order], [sort_by])
پارامتر range_to_sort محدوده ای است که می خواهید مرتب کنید. از پارامتر sort_index برای تعیین اینکه کدام ستون یا شماره ردیف در محدوده مرتب شود استفاده کنید (پیش فرض 1 است).
پارامتر sort_order ترتیب مرتب سازی لیست را مشخص می کند که در آن 1 صعودی و -1 نزولی است (پیش فرض صعودی است). و sort_by مشخص می کند که آیا مرتب سازی بر اساس ردیف (FALSE) که پیش فرض است یا ستون ها (TRUE) است.
قرار است محدوده را در تصویر زیر بر اساس ستون دوم و به ترتیب نزولی مرتب کنیم.
در اینجا فرمول در اکسل به نظر می رسد:
=SORT(A2:B6, 2, -1)
اکنون محدوده باید به ترتیب نزولی مرتب شود.
3 فیلتر
تابع FILTER محدوده ای از داده ها را بر اساس یک شرط ارزیابی می کند و فقط سطرها و ستون هایی را که شرایط را برآورده می کنند برمی گرداند.
نحو تابع FILTER به صورت زیر است:
FILTER(range_to_filter, condition, [value_if_empty])
پارامتر range_to_filter آرایه یا محدوده سلول هایی است که می خواهید فیلتر کنید. پارامتر شرط معیاری است که تعیین می کند چه چیزی باید در نتیجه فیلتر شده برگردانده شود. پارامتر value_if_empty مشخص می کند که اگر هیچ چیزی شرایط را برآورده نکرد، چه چیزی باید برگردانده شود.
ما محدوده را در تصویر زیر فیلتر می کنیم تا فقط کارمندان بخش فروش را نشان دهیم.
در اینجا فرمول در عمل به نظر می رسد:
=FILTER(A2:C6, B2:B6 = "Sales")
اکنون فقط باید سطرها و ستون هایی را ببینید که معیارهای مشخص شده را دارند.
4 INDEX
اگر می خواهید مقدار یک سلول خاص را در یک محدوده داده به دست آورید، می توانید از تابع INDEX استفاده کنید. فقط باید سطر و ستونی که در آن قرار دارد را مشخص کنید.
نحو تابع INDEX به صورت زیر است:
INDEX(range_to_search, row_to_search_in, [column_to_search_in])
پارامتر range_to_search محدوده ای است که مقدار را از آن بازیابی می کنید. row_to_search_in و column_to_search_in اعداد سطر و ستونی هستند که مقدار در آن محدوده قرار دارد (آنها را به عنوان مختصات در نظر بگیرید).
به عنوان مثال، در تصویر زیر، امتیاز (C4) را که آلیس در آزمون انگلیسی خود کسب کرده بود، بازیابی می کنیم.
در اینجا فرمول به نظر می رسد:
=INDEX(A1:C4, 4, 3)
این فرمول 88 را برمی‌گرداند زیرا آلیس در آزمون انگلیسی خود این امتیاز را کسب کرده است.
5 مسابقه
تابع MATCH در اکسل محدوده ای از داده ها را برای یک مقدار مشخص جستجو می کند و سپس موقعیت نسبی آن را برمی گرداند. سپس می توانید از مقدار بازگشتی با توابعی مانند INDEX برای بازیابی و دستکاری داده ها به صورت پویا استفاده کنید.
نحو تابع MATCH به صورت زیر است:
MATCH(value_to_search, range_to_search_in, [match_type])
در اینجا value_to_search مقداری است که می‌خواهید پیدا کنید و range_to_search_in محدوده‌ای است که در آن جستجو می‌کنید.
پارامتر match_type نوع تطبیق مورد استفاده را مشخص می کند. در اینجا انواعی وجود دارد که می توانید استفاده کنید:
نوع مطابقت
توضیحات
1 (پیش فرض)
بزرگترین مقدار کمتر یا مساوی value_to_search را برگردانید
مطابقت دقیق را برگردانید
-1
کوچکترین مقدار را بزرگتر یا مساوی value_to_search برگردانید
برای مثال، از تابع MATCH برای یافتن موقعیت Carol در محدوده استفاده می کنیم. ما همچنین می‌خواهیم که یک مسابقه دقیق باشد.
در اینجا تابع در اکسل به نظر می رسد:
=MATCH("Carol", A2:A6, 0)
پس از اجرای فرمول بالا، تابع 3 را برمی‌گرداند زیرا مطابقت دقیق Carol سومین مورد در محدوده است.
6 XLOOKUP
با تابع XLOOKUP، مقداری را برای جستجو در یک محدوده مشخص می کنید و سپس مقدار مربوطه را از محدوده دیگری استخراج می کنید. برخلاف عملکرد HLOOKUP و عملکرد VLOOKUP، XLOOKUP به شما امکان می دهد در هر جهت جستجو کنید و جستجوی داده ها را در برگه شما انعطاف پذیرتر می کند.
سینتکس تابع XLOOKUP به صورت زیر است:
XLOOKUP(value_to_look_up, range_to_check, range_to_return, [if_value_not_found], [match_type], [search_mode])
در این نحو، value_to_look_up مقداری است که به دنبال آن هستید، range_to_check محدوده ای است که مقدار مورد نظر شما استخراج می شود، و range_to_return جایی است که مقدار متناظر مقدار جستجو از آن به دست می آید. شما فقط باید این سه پارامتر را برای عملکرد تابع مشخص کنید، بنابراین اینها تنها مواردی هستند که ما روی آنها تمرکز خواهیم کرد.
اجازه دهید این موضوع را با یک مثال بر اساس اسکرین شات زیر روشن کنیم. می خواهیم موز (A2) را جستجو کرده و رنگ آن را از ستون مربوطه (B) برگردانیم.
در اینجا فرمول در اکسل به نظر می رسد:
=XLOOKUP("Banana", A:A, B:B)
با اجرای این، رنگ زرد (B2)، مقدار مربوطه بر اساس جستجوی مقدار Banana، برمی گردد.
تسلط بر این شش عملکرد اکسل – FIND، SORT، FILTER، INDEX، MATCH و XLOOKUP – به شما کمک می کند تا به سرعت اطلاعات را از مجموعه داده های بزرگ بیابید، سازماندهی کنید و استخراج کنید. این می تواند به طور قابل توجهی بهره وری اکسل و توانایی تجزیه و تحلیل داده ها را به طور موثر بهبود بخشد.