هنگام نوشتن فرمول در اکسل ، جمع و vlookup اصلی برای مبتدیان هستند. اما از آنجا که اکسل بیش از 400 کارکرد دارد ، مفید است که بیشتر از موارد بسیار مفید بدانید ، به خصوص وقتی به دنبال تبدیل شدن به یک کاربر متوسط و فراتر از آن هستید. بگذارید به شما کمک کنم توابع را کشف کنید که گردش کار اکسل شما را ساده تر می کند.
هنگام نوشتن فرمول در اکسل ، جمع و vlookup اصلی برای مبتدیان هستند. اما از آنجا که اکسل بیش از 400 کارکرد دارد ، مفید است که بیشتر از موارد بسیار مفید بدانید ، به خصوص وقتی به دنبال تبدیل شدن به یک کاربر متوسط و فراتر از آن هستید. بگذارید به شما کمک کنم توابع را کشف کنید که گردش کار اکسل شما را ساده تر می کند.
کارکردهایی که من در اینجا پوشش می دهم پیچیده نیست ، و شما قادر خواهید بود از آنها در تنظیمات مختلف استفاده کنید ، خواه دانش آموز ، حرفه ای تجاری یا تحلیلگر باشید. وقت آن است که بازی اکسل خود را با برخی از کارکردهای صرفه جویی در وقت اضافی که ممکن است از آنها اطلاع نداشته باشید ، بالا ببرید.
COUNT روش بهتری برای سلولهای تالیف ارائه می دهد
اگر آنها حاوی داده های عددی باشند
عملکرد شمارش تمام سلولها را با مقادیر عددی شمارش می کند و نتیجه را برمی گرداند. این باعث می شود تا دردسر شمارش دستی آنها را حفظ کنید ، که می تواند در مجموعه داده های بزرگ خسته کننده و وقت گیر باشد.
=COUNT(value1, value2, ... value_n)
فرمول مثال در زیر باید 10 در صورتی که تمام سلول ها حاوی اعداد هستند ، خروجی کند.
=COUNT(A1:A10)
متوسط محاسبه میانگین را ساده می کند
برای هر محدوده کار می کند
همانطور که از نام آن پیداست ، میانگین عملکرد با اضافه کردن تمام مقادیر عددی در یک محدوده و تقسیم آنها بر کل تعداد آنها ، میانگین (میانگین) را محاسبه می کند.
=AVERAGE(value1, value2, ... value_n)
به عنوان مثال ، در اینجا فرمول برای یافتن میانگین مقادیر در سلول های A1 از طریق A10 وجود دارد:
=AVERAGE(A1:A10)
Min حداقل مقدار را در یک محدوده قرار می دهد
یک عملکرد حداکثر نیز وجود دارد
فرض کنید یک مجموعه داده بزرگ دارید و باید کوچکترین مقدار را پیدا کنید. عملکرد حداقل سریعترین راه برای انجام آن است.
=MIN(value1, value2, ... value_n)
در اینجا مثالی از نحوه عملکرد آن آورده شده است:
=MIN(A1:A10)
از طرف تلنگر ، اگر می خواهید بیشترین مقدار را در یک محدوده پیدا کنید ، باید از عملکرد حداکثر استفاده کنید.
=MIN(value1, value2, ... value_n)
sumif نسخه دقیق تر عملکرد جمع است
برخی از منطق مشروط مورد نیاز است
عملکرد جمع فقط هر مقدار عددی را که به آن می دهید اضافه می کند. با این حال ، عملکرد SUMIF فقط در صورت رعایت معیار مشخص ، آنها را اضافه می کند. در اصل این جمع را ترکیب می کند و اگر عملکرد آن در اکسل است ، بنابراین لازم نیست قبل از جمع بندی ، داده ها را به صورت دستی مرتب یا فیلتر کنید.
=SUMIF(range_to_evaluate, criteria, [range_to_sum])
در اینجا ، Range_to_evaluate سلول هایی با مقادیری است که عملکرد باید در برابر شرایط مشخص شده در پارامتر معیارها بررسی کند. آرگومان اختیاری ، range_to_sum ، سلولهای خاص در محدوده ای است که می خواهید جمع کنید. اگر این پارامتر را حذف کنید ، فقط همه چیز را در محدوده ای که با شرایط مطابقت دارد ، خلاصه می کند.
در زیر نمونه ای از عملکرد در عمل آورده شده است. این فرمول فقط مقادیر A1 را خلاصه می کند: A10 اگر بیشتر از 50 باشند.
=SUMIF(A1:A10, ">50")
تنوع عملکرد SUMIF وجود دارد که می توانید به آن توجه کنید ، از جمله Countif ، Any Andameif ، Minif و Maxif. برخی از نسخه ها به شما امکان می دهند معیارهای مختلفی مانند SUMIF ها ، Countifs و AnadyIF را مشخص کنید.
Subtotal تجمع پاک کننده ای را ارائه می دهد
داده های بی نیاز نباید محاسبات شما را خراب کنند
Subtotal توسط Excel Pros استفاده می شود زیرا به طور هوشمندانه داده های فیلتر شده یا پنهان را حذف می کند ، در حالی که جمع و توابع مشابه شامل همه چیز است. این امر باعث می شود وقتی با مجموعه داده های پویا کار می کنید ، به خصوص هنگام مخفی کردن و فیلتر کردن ، از صحت آن استفاده کنید.
=SUBTOTAL(function_code, range)
Parameter function_code یک عدد از 1-11 یا 101-111 است که عملکرد استفاده را مشخص می کند (به عنوان مثال ، 1 برای میانگین ، 2 برای تعداد ، 9 برای مبلغ). اعداد 1-11 شامل تمام داده ها خواهد بود ، در حالی که 101-111 ردیف های پنهان را حذف می کند. پارامتر دامنه سلول ها به زیرنویس است.
مثال زیر دامنه A1: A10 را خلاصه می کند اما هر ردیف پنهان را حذف می کند:
=SUBTOTAL(109, A1:A10)
Xlookup بر نقص Vlookup غلبه می کند
روش مدرن برای انجام جستجوی
از آنجا که با VLookup آشنا هستید ، احتمالاً نقص اصلی آن را می دانید – این فقط می تواند از چپ به راست جستجو کند و به ستون جستجوی نیاز دارد تا در سمت چپ ستون بازگشت قرار بگیرد. Xlookup قدرتمندتر و انعطاف پذیر تر است و به شما امکان می دهد از هر جهت جستجو کنید. شما حتی نیازی به مرتب سازی ستون ها ندارید.
=XLOOKUP(lookup_value, lookup_range, return_range)
در اینجا ، lookup_value همان چیزی است که شما در جستجوی آن هستید ، lookup_range در کجا می توانید مقدار را جستجو کنید ، و Return_Range در هنگام یافتن مقدار چه چیزی است. همچنین ، به خاطر داشته باشید که این یک نسخه ساده از XLOOKUP است ، با نسخه کامل شامل رسیدگی به خطا به عنوان یک آرگومان اختیاری.
در اینجا مثالی آورده شده است که در آن محدوده A2: A5 شامل شناسه مشتری و دامنه B2 است: B5 شامل نام مشتری است. ما از Xlookup برای یافتن نام مشتری که شناسه آن در سلول D1 یافت می شود استفاده می کنیم.
=XLOOKUP(D1, A2:A5, B2:B5)
index/match دسته کوچک موسیقی جاز جستجوی کلاسیک است
قبل از اینکه Xlookup یک چیز بود
Xlookup در نسخه های اکسل قدیمی تر از سال 2021 و سایر مجموعه های اداری مانند Libreoffice یا OnlyOffice در دسترس نیست. بنابراین ، اگر می خواهید انعطاف پذیری Xlookup را در این سناریوها داشته باشید ، باید عملکردهای شاخص و مطابقت را ترکیب کنید. علاوه بر این ، این ترکیب کنترل بهتری از هر مرحله از جستجوی شما را به شما می دهد ، حتی اگر خطایی از Xlookup را نداشته باشد.
=INDEX(return_range, MATCH(lookup_value, lookup_range, match_type))
در اینجا ، Return_range دامنه سلولهایی است که حاوی مقداری است که می خواهید بازیابی کنید ، LookUp_value مقداری است که می خواهید جستجو کنید ، و LookUp_range دامنه سلولهایی است که می خواهید مقدار جستجو را جستجو کنید. پارامتر match_type مقادیر زیر را می پذیرد: 0 برای مطابقت دقیق ، 1 برای کمتر از ، و -1 برای بیشتر از.
ادامه با مثال XLOOKUP در بخش قبلی ، نسخه Match Match خواهد بود:
=INDEX(B2:B5, MATCH(D1, A2:A5, 0))
همانطور که قبلاً ذکر شد ، شما در اینجا کنترل دارید. به عنوان مثال می توانید برای جستجوی پیشرفته تر از عملکرد XMATCH به جای Match استفاده کنید. برخی از افراد حتی اگر نمی خواهند فیلتر دستی و مرتب سازی را در طول جستجوی خود انجام دهند ، از عملکرد فیلتر استفاده می کنند.
جمع و vlookup فقط آغاز است
تسلط بر اکسل فراتر از جمع و vlookup است. با یادگیری توابع مانند Count ، Meady ، Sumif ، Subtotal ، XLookup و Index/Match ، می توانید فرصت هایی برای بهبود برگه اکسل خود پیدا کنید. برخی از کارکردها به شما کمک می کنند تا داده ها را سریعتر تجزیه و تحلیل کنید ، وظایف را خودکار کنید و صفحات گسترده خود را باهوش تر کنید ، اما این برای زمان دیگری است.