با اینکه نرم‌افزار اکسل، تابع مشخصی به اسم BETWEEN ندارد، با کمی خلاقیت و کمک گرفتن از دیگر توابع اکسل و فرمول‌نویسی مناسب می‌توان عملکرد آن را شبیه‌سازی کرد. کاربرد این تابع این است که نشان می‌دهد آیا یک عدد، تاریخ یا متن بین دو مقدار داده شده در جدول اکسل قرار دارد یا خیر. برای انجام این عملیات، ترکیبی از تابع IF با دیگر توابع مانند MIN، MAX و MEDIAN به‌کار می‌رود. در این مطلب از مجله فرادرس به تشریح کامل نحوه فرمول‌نویسی برای ایجاد عملکرد مشابه تابع BETWEEN در اکسل در سه بخش مجزا می‌پردازیم. در بخش اول استفاده از تابع IF را در ترکیب با سایر توابع با هدف تعیین مقدار میانی بین داده‌های عددی تشریح می‌کنیم. سپس در بخش دوم و سوم به ترتیب نحوه تعیین مقدار میانی بین داده‌های تاریخ و متنی را مورد بررسی قرار می‌دهیم. در انتها نیز با کاربردهای عملی و فواید استفاده از این تابع آشنا می‌شویم.

۱. نحوه ایجاد عملکرد BETWEEN در اکسل برای داده‌های عددی

برای شبیه‌سازی عملکرد تابع BETWEEN در محاسبات عددی اکسل از تابع IF در ترکیب با توابع مختلفی مانند MIN ،MAX ،AND و MEDIAN استفاده می‌کنیم. بنابراین با توجه به اهمیت آشنایی با نحوه کار با توابع اکسل در این مبحث، پیشنهاد می‌کنیم فیلم آموزش توابع و فرمول‌نویسی در اکسل در فرادرس را نیز مشاهده کنید. لینک این آموزش در ادامه آورده شده است.

اما در ادامه به بررسی نحوه فرمول‌نویسی تابع IF با سایر توابع اکسل برای ایجاد عملکرد BETWEEN و شناسایی یک عدد بین دو مقدار عددی دیگر می‌پردازیم.

ترکیب تابع IF با MIN ،MAX و AND

برای ایجاد عملکرد تابع BETWEEN در اکسل با استفاده از تابع IF و ارزیابی اینکه آیا یک مقدار مشخص در جدول داده‌ها بین دو عدد دیگر قرار دارد یا خیر، از فرمول زیر استفاده می‌کنیم.

=IF(AND(value>=MIN(number 1, number 2),value<=MAX(number 1, number 2)), "Yes", "No")

در این فرمول عبارت «value» عدد مورد نظر برای ارزیابی و عبارات «number1» و «number2» دو عدد از جدول داده‌ها برای مقایسه هستند. به‌عنوان مثال در جدول اکسل تصویر زیر می‌خواهیم با استفاده از تابع IF و ایجاد عملکردی مشابه تابع BETWEEN، ارزیابی کنیم که آیا اعداد ستون C بین اعداد درج شده در ستون‌های A و B قرار دارند یا خیر. در این حالت برای اولین ردیف جدول فرمول را به شکل زیر تعریف می‌کنیم.

=IF(AND(C2>=MIN(A2,B2),C2<=MAX(A2,B2)), "Yes","No")

حال ببینیم عملکرد این فرمول به چه شکل است.

• بخش ابتدایی فرمول یعنی عبارت C2>=MIN(A2,B2) بررسی می‌کند که آیا مقدار عددی سلول C2 یا همان عدد ۵۰ بزرگتر یا مساوی کوچکترین عدد در سلول‌های A2 یا B2 است یا خیر.
• در مرحله بعد بخش دوم فرمول یعنی C2<=MAX(A2,B2) بررسی می کند که آیا عدد ۵۰ کوچکتر یا مساوی بزرگترین عدد در سلول‌های A2 و B2 است یا خیر.
• در نهایت تابع AND دو شرط فرمول‌های قبلی را از لحاظ درستی یا نادرستی تطابق می‌دهد. به این معنی که اگر هر دو شرط هم‌زمان برقرار باشد، عبارت «Yes» و در غیر این صورت عبارت «No» را نمایش می‌دهد.

همان‌طور که در تصویر می‌بینیم، از آنجا که عدد ۵۰ در ردیف دوم بزرگتر از عدد ۳۰ و کوچک‌تر از عدد ۸۰ است، عبارت «Yes» به‌معنای برقرار بودن شرط نمایش داده می‌شود. اما در ردیف دوم جدول، عدد ۵۰ بزرگ‌تر از عدد ۴۵ است. یعنی شرط دوم فرمول برقرار نیست و عبارت «No» در جدول درج می‌شود.

ترکیب تابع IF با تابع MEDIAN

تابع MEDIAN برای محاسبه مقدار عددی میانه بین یک سری از داده‌های جدول اکسل به کار می‌رود. در حالت عادی برای محاسبه میانه با فرمول‌های دستی بین یک سری از اعداد، ابتدا آن‌ها را از کوچک به بزرگ مرتب می‌کنیم، سپس در صورت فرد بودن تعداد، عدد وسط را به عنوان میانه در نظر می‌گیریم. اما اگر تعداد اعداد زوج باشد، میانگین دو عدد وسط را به‌عنوان میانه لحاظ می‌کنیم. مطلب «میانه چیست و چگونه محاسبه می‌شود» در مجله فرادرس راهنمای تکمیلی خوبی برای آشنایی بیشتر در این خصوص است.

حال ببینیم چگونه می‌توانیم در اکسل با استفاده از تابع MEDIAN این عملیات را به‌صورت خودکار انجام دهیم. فرمول کلی برای این منظور به شرح زیر است.

=IF(value=MEDIAN(number 1:value), "Yes","No")

در این فرمول عبارت «value» مقدار عددی مورد نظر جهت ارزیابی و «number1» اولین عدد در ردیف داده‌ها است.

به عنوان مثال در جدول داده‌های زیر می‌خواهیم بررسی کنیم که آیا مقدار عددی ۵۰ در ستون C عدد میانه بین اعداد مندرج در ستون‌های A تا C است یا خیر. در صورت درستی شرط، عبارت «Yes» در ستون E درج می‌شود. فرمول ترکیبی برای ردیف اول به شرح زیر خواهد بود.

=IF(C2=MEDIAN(A2:C2),"Yes","No")

همان‌طور که در تصویر مشخص است، اگر سه عدد ردیف اول را به شکل صعودی مرتب کنیم، عدد میانه آن ۵۰ خواهد بود و نتیجه نهایی با کلمه «Yes» نمایش داده می‌شود. اما در ردیف دوم عدد ۴۰ میانه است نه عدد ۵۰. بنابراین شرط برقرار نیست.

مسیر یادگیری فرمول‌نویسی و کار با توابع اکسل در فرادرس

همان‌طور که تا به این بخش از مطلب دیدیم، با استفاده از توابع مختلف در اکسل می‌توانیم عملکردهای مشابه توابع دیگر را ایجاد کنیم. بنابراین در مرحله اول آشنایی با انواع توابع و عملیات مربوط به آن‌ها بسیار اهمیت دارد. فیلم‌های آموزشی فرادرس مجموعه کاملی در جهت یادگیری این مفاهیم است. بنابراین برای افزایش مهارت در این زمینه پیشنهاد می‌کنیم فیلم آموزش‌های زیر را مشاهده کنید.

همچنین برای افزایش مهارت در زمینه کار با نرم‌افزار اکسل نیز می‌توانید از طریق دو مجموعه فیلم آموزش زیر، کلیه مباحث مرتبط را به‌شکل دسته‌بندی شده دنبال کنید.

با افزایش مهارت در استفاده از ابزارها و توابع مختلف اکسل درک ادامه مطلب ساده‌تر خواهد بود.

۲. نحوه ایجاد عملکرد BETWEEN در اکسل برای داده‌های تاریخ

برای انجام محاسبه تاریخ میانی بین دو داده با فرمت تاریخ در جدول اکسل نیز همان فرمول ترکیبی تابع IF و تابع MEDIAN برای اعداد را به‌کار می‌بریم.

به عنوان مثال در تصویر زیر می‌خواهیم برای ردیف اول داده‌ها عملکرد تابع BETWEEN را برای شناسایی یک تاریخ مشخص بین تاریخ‌های موجود در جدول اکسل شبیه‌سازی کنیم. فرمول مورد نظر، مشابه روش اول و به‌صورت =IF(C4=MEDIAN(A4:C4),”Yes”,”No”) خواهد بود. با این تفاوت که فرمت داده‌ها در سلول‌های اکسل به‌شکل تاریخ است.

همان‌طور که مشخص است در ردیف اول تاریخ ۲۲ شهریور ۱۴۰۳ در سلول C2 تاریخ میانه بین ۱۱ خرداد ۱۴۰۳ و ششم مرداد ۱۴۰۳ نیست. بنابراین نتیجه فرمول با عبارت «No» نمایش داده می‌شود. اما در ردیف دوم این تاریخ در میانه دو تاریخ درج شده در سلول‌های A3 و B3 قرار دارد.

۳. نحوه ایجاد عملکرد BETWEEN در اکسل برای داده‌های متنی

با استفاده از تابع AND و چند عملگر محاسباتی دیگر می‌توانیم قرارگیری یک کلمه را در بین دو کلمه دیگر بر اساس ترتیب حروف الفبا بررسی کنیم. این عملیات برای شناسایی نام کارکنان شرکت یا سایر داده‌های متنی مانند موجودی یک کالای خاص در انبار بر حسب حروف الفبا بسیار کاربرد دارد.

فرمول کلی مورد استفاده برای این منظور به شرح زیر است.

=IF(AND(value>=text 1,value<=text 2),"Yes","No")

در این فرمول عبارت «value» متن مورد نظر برای مقایسه و عبارت‌های «text1» و «text2» دو داده متنی دیگر در جدول هستند. نکته جالب توجه در این فرمول استفاده از عملگرهای >= و <= برای مقایسه ترتیب الفبایی کلمات است.

به‌عنوان مثال در تصویر زیر دو دسته کلمه برای مقایسه در ردیف‌های دوم و سوم جدول وجود دارند. اگر در ردیف دوم بخواهیم کلمه «فرادرس» را از لحاظ ترتیب الفبایی با دو کلمه «آموزش» و «اکسل» مقایسه کنیم، فرمول زیر را تعریف می‌کنیم.

=IF(AND(C2>=A2,C2<=B2),"Yes","No")

برای مشاهده تصویر بزرگ‌تر بر روی آن کلیک کنید.همان‌طور که در تصویر مشخص است، در ردیف اول، حرف «ف» در کلمه «فرادرس» از لحاظ ترتیب الفبای فارسی بالاتر از حرف «الف» در کلمه «اکسل» قرار دارد. بنابراین شرط اول فرمول برقرار است. اما حرف «ف» در رتبه بالاتری از لحاظ ترتیب الفبای فارسی نسبت به حرف «آ» در کلمه «آموزش» قرار دارد. بنابراین شرط دوم فرمول برقرار نیست و نتیجه کلی فرمول با عبارت «No» نمایش داده می‌شود. به‌عبارت دیگر کلمه «فرادرس» از لحاظ ترتیب الفبایی بین دو کلمه «آموزش» و «اکسل» قرار ندارد. اما در ردیف دوم جدول با توجه به درست بودن هر دو شرط فرمول، عبارت «Yes» نشان داده شده است.

لازم است توجه داشته باشیم در صورتی‌که حرف اول کلمه مورد نظر برای مقایسه با حرف اول دو کلمه دیگر یکسان باشد، فرمول نوشته شده برای انجام مقایسه حرف دوم یا سایر حروف را از لحاظ ترتیب الفبایی در نظر می‌گیرد.

نکات مهم در فرمول‌نویسی تابع BETWEEN در اکسل

در هنگام استفاده از توابع مختلف برای شبیه‌سازی عملکرد تابع BETWEEN لازم است به دو نکته مهم توجه کنیم.

۱. نحوه فرمول نویسی با در نظر گرفتن اعداد مرزی یا حذف آن‌ها

در بسیاری موارد برای تعیین مقادیر بین دو عدد در جدول اکسل با استفاده از فرمول نویسی BETWEEN نیاز به رعایت شرایط اعداد مرزی داریم. به این معنا که می‌خواهیم کاملا مشخص کنیم که در شرط‌های تعریف شده، مقادیر عددی بین دو مقدار دیگر شامل خود آن اعداد نیز باشد یا خیر. به‌عنوان مثال اگر می‌خواهیم در جدول داده‌ها، افرادی که سن‌ آن‌ها بین ۱۸ تا ۷۰ سال است را شناسایی کنیم، این انتخاب به دو حالت می‌تواند انجام گیرد. اینکه دو عدد ۱۸ و ۷۰ به عنوان اعداد مرزی در فرمول‌نویسی اکسل لحاظ شوند یا خیر. در این صورت اگر هدف ما در نظر گرفتن هر دو عدد باشد، توابع فرمول را با عملگرهای >= یا <= تعریف می‌کنیم. در غیر این صورت باید علامت مساوی را از این عملگرها حذف کنیم.

۲. مدیریت دقیق خطاها و استثناها

برای زمان‌هایی که یک مقدار عددی در محدوده تعیین شده جدول تعریف نشده است یا رشته متنی نامتناسبی در میان داده‌های عددی وجود دارد، می‌توانیم از توابع IFERROR یا ISERROR برای مدیریت خطاهای احتمالی استفاده کنیم. این توابع برای جلوگیری از بروز خطاهای پیش‌بینی نشده به‌جای تابع IF در فرمول به‌کار می‌روند. به این شکل می‌توانیم خطاها را اصلاح یا علامت‌گذاری کنیم. در نتیجه یکپارچگی داده‌ها حفظ می‌شوند.

کاربردهای عملی BETWEEN در اکسل

فرمول‌نویسی شرطی برای ایجاد عملکرد تابع BETWEEN در اکسل کاربردهای بسیاری در دنیای واقعی کسب و کار دارد که مهم‌ترین آن‌ها به‌شرح زیر است.

• تجزیه و تحلیل داده‌های فروش
• عملیات در محدوده داده‌های تاریخی حساس به زمان

در صورت علاقه‌مندی به یادگیری کاربردهای مختلف نرم‌افزار اکسل در محاسبات فروش پیشنهاد می‌کنیم فیلم آموزش تحلیل داده‌های کسب و کار در فرادرس را مشاهده کنید.

تجزیه و تحلیل داده‌های فروش

استفاده از توابع ترکیبی اکسل برای شناسایی اعداد میانی در بین تعداد زیادی از داده‌های فروش عملیات تجزیه و تحلیل آن‌ها را ساده می‌کند. این موضوع به‌خصوص در زمان‌هایی که داده‌های فروش زیر مجموعه‌های متعددی بر حسب مواردی مانند محصول یا بخش‌بندی‌های خاص تعریف شده برای مشتریان دارد، بسیار کاربرد دارد. زیرا به این شکل می‌توانیم عملیات شناسایی یک سری داده مشخص را سریع‌تر کنیم. به عنوان مثال اگر بخواهیم داده‌های سه ماهه فروش محصول بین ۲۰ میلیون تا ۱۰۰ میلیون تومان را از میان کل فروش سالیانه یک شرکت شناسایی کنیم، استفاده از این نوع فرمول‌نویسی ترکیبی، انجام کار را بسیار ساده می‌کند.

عملیات در محدوده داده‌های تاریخی حساس به زمان

در صورتی‌که یک سری از داده‌های حساس به زمان داشته باشیم، طبقه‌بندی آن‌ها بر اساس یک محدوده تاریخی مشخص نحوه مدیریت و تفسیر اطلاعات را در دوره‌های زمانی خاص ساده می‌کند. این عملیات به‌خصوص در پیگیری تاریخ مهلت انجام پروژه، تجزیه و تحلیل روندهای فصلی یا برنامه‌ریزی موجودی انبار بر اساس داده‌های تاریخی کاربرد دارد. در این حالت می‌توان با فرمول‌نویسی ترکیبی و ایجاد شرط تابع BETWEEN بازه زمانی دقیق مورد نظر را شناسایی کرد. به‌عنوان مثال اگر بخواهیم عملکرد کمپین بازاریابی در طی تعطیلات نوروزی را بررسی کنیم، استفاده از این نوع فرمول‌نویسی کمک می کند تا بتوانیم داده‌های مورد نظر در این بازه را برای تجزیه و تحلیل دقیق تفکیک کنیم.

فواید استفاده از BETWEEN در اکسل

فرمول‌نویسی شرطی برای شبیه‌سازی عملکرد تابع BETWEEN در اکسل برای اعداد در جدول داده‌ها دو فایده زیر را دارد.

افزایش دقت تجزیه و تحلیل داده‌ها

با تعریف فرمول‌های دقیق از این طریق اطمینان پیدا می‌کنیم که فقط داده‌های مشخص و مرتبط در جدول بررسی می‌شوند. این عملیات به‌خصوص برای تفکیک داده‌های مهم از میان مجموعه‌ بزرگی از اعداد بسیار کاربرد دارد. بنابراین دقت تجزیه و تحلیل داده‌ها افزایش می‌یابد.

فراهم کردن زمینه تصمیم‌گیری‌های دقیق

فرمول‌نویسی در قالب تابع BETWEEN ابزاری برای کمک به تصمیم‌گیری‌های دقیق‌تر است. زیرا به‌عنوان یک فیلتر بین داده‌های مختلف عمل می‌کند و می‌توانیم تجزیه و تحلیل خود را فقط در بین داده‌های مشخص و مورد نیاز انجام دهیم. ایجاد این محدودیت انجام اقدامات آگاهانه و موثرتر بعدی را ساده می‌سازد. در نتیجه به‌عنوان مثال تصمیم‌گیری‌های دقیق‌تری در مورد عملکرد کسب و کار، سلامت مالی یا سایر شاخص‌ها خواهیم داشت.

جمع‌بندی پایانی

با استفاده از ترکیب توابع مختلف در اکسل می‌توان تعیین کرد که آیا یک عدد، متن یا تاریخ بین دو مقدار مشخص از داده‌های موجود در جدول اکسل وجود دارد یا خیر. به عبارتی با وجود تعریف نشدن تابعی مشخص برای این‌کار می‌توانیم عملکرد تابع BETWEEN را در اکسل شبیه‌سازی کنیم. در این مطلب از مجله فرادرس با سه روش فرمول‌نویسی برای تابع BETWEEN در اکسل آشنا شدیم. با استفاده از این روش‌ها می‌توانیم ضمن افزایش دقت تجزیه و تحلیل داده‌های متعدد، زمینه تصمیم‌گیری‌های موثرتری را با احتمال خطای کمتر در کسب و کار خود فراهم کنیم.