جستجوی مقادیر در اکسل

در این بخش از آموزش اکسل 2016 شیرازلرن به سراغ جستجوی مقادیر در اکسل می رویم.

ساخت فرمول هایی که به جست و جوی مقادیر می پردازند

در این فصل به بررسی تکنیک های مختلفی پرداخته می شود که می توانید از آنها برای جست و جوی یک مقدار در دامنه ای از داده ها استفاده کنید پرداخته می شود. در اکسل، سه تابع LOOKUP، VLOOKUP و HLOOKUP برای این کار طراحی شده اند.

معرفی فرمول های جست و جو یا LOOKUP

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

تصویر زیر کاربرگی را نشان می دهد که از چندین فرمول LOOKUP استفاده می کند. این کاربرگ حاوی جدولی است که داده های مربوط به کارمندان از ردیف 7 آن گرفته است. این دامنه EmpData نامیده می شود. وقتی نام فامیلی را در سلول C2 وارد می کنید فرمول LOOKUP که دامنه D2:G2 درج شده، اطلاعات تطبیق یافته با نام خانوادگی را از جدول بازیابی می کند. اگر نام خانوادگی در ستون C وجود نداشته باشد، فرمول عبارت خطای #N/A را به عنوان پاسخ بر می گرداند.

جستجوی مقادیر در اکسل

درفرمول جست و جوی سلول های زیر از تابع VLOOKUP استفاده شده است:

سلول D2

= VLOOKUP(C2,EmpData,2,FALSE)

سلول E2

= VLOOKUP(C2,EmpData,3,FALSE)

سلول F2

= VLOOKUP(C2,EmpData,4,FALSE)

سلول G2

= VLOOKUP(C2,EmpData,5,FALSE)

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

توابع مرتبط با عملیات جست و جو

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

تابع

.شرح

CHOOSE

یک مقدار خاص از فهرست مقادیری که به عنوان آرگومان درج شده اند را بر می گرداند

HIOOKUP

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

IF

اگر پاسخی شرطی که مشخص کرده اید TRUE باشدف یک مقدار و اگر پاسخ شرط FALSE باشد، مقدار دیگری را بر می گرداند

IFERROR

اگر آرگومان نخست، پیغام خطا را بر گرداند، ارگومان دوم مورد بررسی قرار گرفته و به عنوان پاسخ بر می گردد، اما اگر آرگومان اول، پیغام خطا را بر می گرداند خودش مورد ارزیابی قرار گرفته و به عنوان پاسخ بر می گردد.

INDEX

یک مقدار از یک جدول یا دامنه را بر می گرداند.

LOOKUP

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

MATCH

موقعیت نسبی یکی از داده های دامنه که با مقدار مشخص شده تطبیق دارد را برر می گرداند.

OFFSET

ارجاع به دامنه ای که به تعداد ردیف و ستون مشخص شده تطبیق دارد را بر می گرداند

VLOOKUP

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

 

استفاده از تابع IF برای جست و جوی ساده

تابع IF بسیار کارآمد و چند بعدی و اغلب برای مسایل مربوط به تصمیم گیری های ساده مناسب است. تصویر زیر، کاربرگی که نمرات دانش آموزان در ستون B درج شده را نشان می دهد. فرمول های ستون C از تابع IF استفاده مرده اند تا در صورتی که نمره ی دانش آموز برابر 65 یا بیشتر از آن بود، عبارت Pass و در صورتی که نمره دانش اموز کمتر از 65 بود عبارت Fail  را به عنوان پاسخ بر می گرداند. برای مثال، فرمول سلول C2  به این صورت است.

=IF ( B2>=65 , “Pass” , “Fail” )

می توانید از توابع IF تودرتو برای بالا بردن توانایی تصمیم گیری استفاده کنید. برای مثال در فرمول زیر، یکی از چهار رشته ی Excellent، Very Good، Fair یا Poor به عنوان پاسخ برمی گرداند:

=IF ( B2>=90 , “Excellent” , IF (B2>=70 , “Very Good” , IF (B2>=50 , “Fair” , “Poor”) )

این تکنیک تنها در مواردی که حوزه ی گزینه های انتخابی محدود به چند مورد است کارامد خواهد بود. چون اگر به صورت گسترده وبه دفعات زیاد از توابع IF تودرتو استفاده کنید به زودی کلاف کار از دستتان در می رود. بنابراین برای مواردی که گزینه های انتخابی زیاد هستند، بهتر است از تکنیک های LOOKUP استفاده کنید.

جستجوی مقادیر در اکسل

 

فرمول های اصلی LOOKUP

می توانید از توابع اصلی LOOKUP برای جست وجو در یک ردیف یا یک ستون استفاده کنید تامقداری دیگر به عنوان نتیجه برگردد. اکسل از سه تابع اصلی  LOOKUP، VLOOKUP و HLOOKUP برای عملیات جست وجو استفاده می کند. به علاوه توابع MATCH  و INDEX نیز اغلب همراه باهم برای پیدا کردن سلول یا ارجاع نسبی سلولی که با مقدار LOOKUP تطبیق دارد، به کار گرفته می شوند.

 

تابع VLOOKUP

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

VLOOKUP ( lookup_ value, table_array , col_ index_num , range_lookup )

آرگومان های تابع VLOOKUP به شرح زیر هستند:

lookup_value: مقداری که قرار است در نخستین ستون جدول جست وجو به دنبال آن بگردید.

table_array: دامنه ای که جدول جست وجو در آن قرار دارد.

:col_index_numشماره ی ستونی در جدول که مقدار تطبیق یافته در عملیات جست وجو، در آنجا قرار دارد.

:range_lookup این آرگومان اختیاری است. اگر از آن استفاده نکنید یا مقدار آن TRUE باشد، یک معادل تقریبی را به عنوان جواب برمی گرداند. این بدان معناست که اگر تطبیق دقیقی در جدول پیدا نشود، بزرگ ترین مقداری که بیش از دیگر مقادیر به lookup_value نزدیک است را به عنوان پاسخ برمی گرداند. اما اگر مقدار این آرگومان FALSE باشد، تابع VLOOKUP به دنبال یک تطبیق دقیق می گردد. در صورتی که تابع VLOOKUP نتواند تطبیق دقیقی پیدا کند، عبارت #N/A را به عنوان پاسخ برمی گرداند.

یکی از کاربرد های متداول فرمول LOOKUP برای جدول "نرخ مالیات بر درامد" است که آن را در تصویر14-3 مشاهده می کنید. جدول نرخ مالیات بر درامد، نرخ مالیات بر درامد برای سطوح مختلف درامد را نشان می دهد. فرمول زیر که در سلول B3 قرار دارد، نرخ مالیات درامدی که در سلول B2 درج شده را برمی‌گرداند:

=VLOOKUP ( B2, D2 : F7 ,3 )

جستجوی مقادیر در اکسل

 

جدول جست وجو در دامنه ای قرار دارد که از سه ستون (D2:F7)  تشکیل شده است. چون آخرین آرگومان تابع VLOOKUP برابر3 است پس فرمول، مقدار مربوطه در ستون سوم جدول جست وجو را به عنوان جواب برمی گرداند.  

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

تابع HLOOKUP

تابع HLOOKUP  درست شبیه تابعVLOOKUP  عمل می کند به استثنای این که جدول جست وجو به جای حالت عمودی به صورت افقی آرایش یافته است.

تابع HLOOKUP به دنبال مقداری در نخستین ردیف جدول LOOKUP می گردد و مقدار متناظر در ردیف مشخص شده ی جدول را برمی گرداند. ساختار دستوری تابع HLOOKUP به صورت زیر است:

HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)

آرگومان های تابع HLOOKUP به شرح زیر هستند:

:lookup_valueمقداری که قرار است در نخستین ردیف جدول جست وجو به دنبال آن بگردید.

:table_arrayدامنه ای که جدول جست وجو در آن قرار دارد.

:row_index_numشماره ی ردیفی در جدول که مقدار تطبیق یافته در عملیات جست وجو، در  آنجا قرار دارد.

:range_lookupاین آرگومان اختیاری است. اگر استفاده نشود یا مقدار آن TRUE باشد، یک تطبیق تقریبی را به عنوان پاسخ برمی گرداند. این بدان معناست که اگر تطبیق دقیقی در جدول پیدا نشود، بزرگ ترین مقداری که بیش از دیگر مقادیر به lookup_value نزدیک است را به عنوان پاسخ برمی گرداند. اما اگر مقدار آن، FALSE باشد، تابع HLOOKUP به دنبال یک تطبیق دقیق می گردد. در صورتی که تابع HLOOKUPنتواند تطبیق دقیقی پیدا کند، عبارت #N/A را به عنوان برمی گرداند.

مثال نرخ مالیات را با جدول جست وجوی افقی (در دامنه E1:J3)  نشان می دهد. فرمول سلول B3 به صورت زیر است:

=HLOOKUP ( B2 , E1 : J3 , 3 )

جستجوی مقادیر در اکسل

تابع LOOKUP در دامنه ای تک ردیفی یا تک ستونی (lookup_vector) به دنبال مقداری (lookup_value) می‌گردد واز محل متناظر در دامنه‌ی تک ردیفی یا تک ستونی دوم (lookup_vector)، مقداری را برمی گرداند. ساختار دستوری تابع LOOKUP به صورت زیر است:

LOOKUP ( lookup_value, lookup_vector, result_vector )

آرگومان های تابع LOOKUP به شرح زیر هستند:

:lookup_valueمقداری که قرار است به دنبال آن بگردید.

:lookup_vectorدامنه ای تک ستونی یا تک ردیفی که حاوی مقادیری که به دنبال آنها می گردید است. این مقادیر باید به صورت صعودی مرتب شده باشند.

دقت کنید که مقادیر موجود در دامنه ی lookup_vector باید به صورت صعودی مرتب شده باشند. همچنین اگرlookup_value، کوچک ترین مقدار موجود در lookup_vector باشد تابعLOOKUP، عبارت خطای #N/A را به عنوان پاسخ برمی گرداند. 

تصویر زیر، دوباره جدول نرخ مالیات را نشان می دهد. این بار، فرمول سلول B3 از تابع LOOKUP برای برگرداندن نرخ مالیات متناظر استفاده می کند. فرمول سلول B3 به صورت زیر نوشته می شود:

=LOOKUP ( B2 , D2 : D7 , F2 : F7 )

دقت کنید که اگر مقادیر موجود در ستون نخست به صورت صعودی مرتب نشده باشند، ممکن است تابع LOOKUP، مقدار نادرستی را به عنوان پاسخ برگرداند.

تابع LOOKUP برخلاف تابع VLOOKUP نیازمند دو ارجاع دامنه است; یکی از دامنه ها مورد جست وجو قرار می گیرد و دامنه ی دیگر حاوی مقادیر نتیجه خواهد بود. از سوی دیگر، تابع VLOOKUP از یک دامنه برای جدول جست وجو استفاده می کند و آرگومان سوم آن مشخص می کند که از کدام ستون برای درج نتایج استفاده شود. البته این آرگومان می تواند یک ارجاع سلولی نیز باشد.

جستجوی مقادیر در اکسل

 


نویسنده : --

تگ ها :
تاپ سایت 98
عضویت طلایی
محصولات آموزشی
ثبت نظر