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

تابع MIN: پیدا کردن کوچکترین عدد

تابع MAX: پیدا کردن بزرگترین عدد

تابع Vlookup:بررسی آن که عدد مد نظر در لیست وجود دارد یا نه

تابع Countif: تعداد تکرار عدد مد نظر

 در این پست با استفاده از ترکیب توابع اکسل، نزدیکترین عدد موجود در لیست به عدد مد نظر را با استفاده از کی مثال توضیح خواهیم داد.

در جدول ذیل، دو لیست وجود دارد که در هر یک، مجموعه ای از اعداد ثبت شده است. می خواهیم با استفاده از توابع اکسل، نزدیکترین عدد از لیست شماره ۱، به هر یک از اعداد لیست شماره ۲ را پیدا کنیم.

 

محاسبه نزدیک ترین عدد در یک لیست به عدد مد نظر

به منظور حل این مسئله، ابتدا روش حل را برای عدد ۵۴۲۸ (موجود در لیست دوم) انجام می دهیم. لذا به منظور پیدا کردن نزدیک ترنی عدد از لیست اول به عدد ۵۴۲۸ مراحل ذیل را باید انجام داد.

ابتدا باید اختلاف هر یک از اعداد لیست اول را از عدد ۵۴۲۸ به دست آورد ولی از آنجایی که مقدار اختلاف برای ما مهم است (علامت مثبت یا منفی بودن اختلاف برای ما در این مسئله اهمیت ندارد) از تابع ABS به صورت ذیل برای سلول C2 استفاده می کنیم.

=ABS(B2-5428)

مقدار تابع فوق برابر با میزان اختلاف عدد ثبت شده در سلول B2 با عدد ۵۴۲۸ می باشد که برای به دست آوردن اختلاف کلیه اعداد موجود در لیست ۱، از عدد ۵۴۲۸، می بایست فرمول فوق را برای محدوده سلولهای B2 تا B8 اعمال نمود تا نتیجه مشابه شکل ذیل حاصل شود.

 

حال به منظور پیدا کردن نزدیک ترین عدد از لیست اول به عدد ۵۴۲۸، می بایست ردیف با کمترین اختلاف را پیدا نمود. بدین منظور حاصل تابع ذیل برابربا نزدیکترین عدد از لیست اول به عدد ۵۴۲۸ می باشد.

=INDEX(B2:B8,MATCH(MIN(C2:C8),C2:C8,0))

که مقدار آن برابر با ۴۵۰۴ می باشد.

با انجام مراحل فوق نزدیکترین عدد به عدد ۵۴۲۸ به دست آمد اما فرمول فوق را نمیتوان برای سایر اعداد لیست دوم اعمال نمود، چرا که محدوده ستون C اختلاف تک تک رکوردهای لیست ۱ از عدد ۵۴۲۸ را نمایش می دهد. پس با این روش تنها میتوان نزدیک ترین عدد از لیست ۱ به یکی از اعداد لیست دوم را به دست آورد.

آدرس دهی مطلق در فرمول

به منظور برطرف کردن این مشکل با استفاده از فرمول نویسی آرایه ای به حل این مسئله می پردازیم. بدین منظور کافی است فرمول ذیل را درون سلول F2 به صورت آرایه ای تعریف کرد.

=INDEX($B$2:$B$8,MATCH(MIN(ABS($B$2:$B$8-D2)),ABS($B$2:$B$8-D2),0))

با اعمال این فرمول برای کل سلولهای محدوده F2 تا F8 نتیجه به صورت ذیل حاصل خواهد شد.