آموزش ابزار lookup در اکسل به زبان ساده

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

دستور lookup در اکسل

زمانی از دستور lookup در اکسل استفاده می‌کنیم که قصد داریم در Microsoft excel یک سطر یا ستون یک مقدار مشخص را پیدا کنیم.

 

فرمول lookup در اکسل یا Lookup formula به صوررت زیر است:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

به‌عنوان‌مثال، فرض کنید شماره ‌قطعه خودرو را می‌دانید، اما قیمت آن را نمی‌دانید. هنگام واردکردن شماره قطعه خودکار در سلول H1 می‌توانید از تابع LOOKUP برای برگرداندن قیمت در سلول H2 استفاده کنید.

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

نکته: بسته به نسخه‌ای که استفاده می‌کنید، یکی از توابع جستجوی جدیدتر را در نظر بگیرید. (۲۰۱۳ یا ۲۰۱۶).

از VLOOKUP برای جستجوی یک سطر یا ستون یا برای جستجوی چندین سطر و ستون (مانند یک جدول) استفاده کنید. این یک نسخه بسیار بهبودیافته از LOOKUP است.

پیشنهاد مطالعه: legend در اکسل چیست؟

آموزش دستور lookup در اکسل

دو راه برای استفاده از LOOKUP وجود دارد: فرم برداری (Vector) و فرم آرایه. در ادامه هر دو این روش‌ها مورد بررسی قرار خواهند گرفت:

فرم برداری یا vector

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

فرم آرایه (array form)

توصیه می‌کنیم به‌جای فرم آرایه از VLOOKUP یا HLOOKUP استفاده کنید. فرم آرایه برای سازگاری با سایر برنامه‌های صفحه گسترده ارائه شده اما عملکرد آن محدود است.

آرایه مجموعه‌ای از مقادیر در ردیف‌ها و ستون‌ها (مانند یک جدول) است که می‌خواهید جستجو کنید. برای مثال، اگر می‌خواهید ستون‌های A و B را تا ردیف 6 جستجو کنید، LOOKUP نزدیک‌ترین مطابقت را برمی‌گرداند. برای استفاده از فرم آرایه، داده‌های شما باید مرتب شوند.

اجزای lookup در اکسل

LOOKUP در اکسل متشکل از اجزای مختلفی است که در زیر بحث شده‌اند.

Lookup function: شکل برداری LOOKUP در محدوده یک ردیفی یا یک ستونی (معروف به vector) یک مقدار را جستجو می‌کند و مقداری را از همان موقعیت در محدوده دوم یک ردیف یا یک ستون برمی‌گرداند.

• LOOKUP(lookup_value, lookup_vector, [result_vector])

lookup_value: مقداری که LOOKUP آن را در اولین بردار جستجو می‌کند. Lookup_value می‌تواند یک عدد، متن، یک مقدار منطقی یا نام یا مرجعی باشد که به یک مقدار اشاره دارد.

lookup_vector: محدوده‌ای که فقط شامل یک سطر یا یک ستون است. مقادیر در lookup_vector می‌توانند متن، اعداد یا مقادیر منطقی باشند.

result_vector: محدوده‌ای که فقط شامل یک سطر یا ستون است. آرگومان result_vector باید به‌اندازه lookup_vector باشد.

مهم: مقادیر در lookup_vector باید به ترتیب صعودی قرار گیرند:، -۲، -۱، ۰، ۱، ۲، و…، A-Z، FALSE، TRUE. در غیر این صورت، LOOKUP ممکن است مقدار صحیح را برنگرداند. متن بزرگ و کوچک معادل هستند.

کاربرد تابع lookup در اکسل

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

۱- داده‌های جدول زیر را کپی کرده و در یک کاربرگ جدید اکسل قرار دهید.

۲- سپس فرمول‌های LOOKUP را از جدول زیر در ستون D صفحه کاری خود کپی کنید.

۳- برای اینکه این فرمول‌ها نتایج را نشان دهند، ممکن است لازم باشد آن‌ها را در کاربرگ اکسل خود انتخاب کنید، F2 را فشار داده و سپس Enter را فشار دهید. در صورت نیاز، عرض ستون‌ها را تنظیم کنید تا همه داده‌ها را ببینید.

پیشنهاد مطالعه: راهنمای گام به گام استفاده از cell style در اکسل

فرمول vlookup در اکسل چیست؟

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

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

Vlookup formula یا فرمول لوکاپ در اکسل چیزی شبیه به این است:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

مثال تابع vlookup در اکسل:

• ‎=VLOOKUP(A2,A10:C20,2,TRUE)‎
• ‎=VLOOKUP(“Fontana”,B2:E7,2,FALSE)
• ‎=VLOOKUP(A2,’Client Details’!A:F,3,FALSE)

نحوه محاسبه vlookup در اکسل

برای محسابه درصد در لوکاپ اکسل، در نوار فرمول =VLOOKUP()‎ را تایپ کنید. در ادامه در داخل پرانتز، مقدار جستجوی خود را وارد کرده و به دنبال آن یک کاما قرار دهید. این می‌تواند یک مقدار واقعی یا یک سلول خالی باشد که دارای یک مقدار باشد:

• H2,‎

آرایه جدول یا search table، محدوده داده‌هایی که می‌خواهید جستجو کنید و یک کاما را وارد کنید:

• H2,B3:F25,‎

شماره فهرست ستون را وارد کنید. این ستونی است که فکر می‌کنید پاسخ‌ها در آن هستند و باید در سمت راست مقادیر جستجوی شما باشد:

• H2,B3:F25,3,‎

مقدار جستجوی محدوده را وارد کنید، TRUE یا FALSE. TRUE مطابقت‌های جزئی را پیدا می‌کند، FALSE مطابقت‌های دقیق را پیدا می‌کند. فرمول تمام شده شما چیزی شبیه به این است:

• =VLOOKUP(H2,B3:F25,3,FALSE)

مثال تابع vlookup در اکسل

به  دو جدول تصاویر زیر دقت کنید.

و همچنین تصویر زیر:

این‌ها مثال‌های بارزی از تابع لوکاپ در اکسل هستند.

خطاهای متداول lookup در اکسل

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

خطای ممیز شناور

ارور رایج در لوکاپ اکسل ممیز شناور است. اگر مقادیر زمانی یا اعداد اعشاری بزرگ در سلول‌ها دارید، اکسل به دلیل دقت ممیز شناور، خطای #N/A را برمی‌گرداند. اعداد ممیز شناور اعدادی هستند که بعد از یک ممیز اعشار دنبال می‌شوند. (اکسل مقادیر زمان را به‌صورت اعداد ممیز شناور ذخیره می‌کند.) اکسل نمی‌تواند اعدادی را با ممیز شناور بسیار بزرگ ذخیره کند، بنابراین برای اینکه تابع به‌درستی کار کند، اعداد ممیز شناور باید به ۵ رقم اعشار گرد شوند.

 

راه‌حل: اعداد را با گرد کردن آنها تا پنج رقم اعشار با تابع ROUND کوتاه کنید.

خطای N/A

اگر آرگومان range_lookup روی TRUE تنظیم شده باشد  و یکی از ستون‌های جستجوی شما به ترتیب صعودی (A-Z) مرتب نشده باشد خطای #N/A را خواهید دید.

راه‌حل: تابع VLOOKUP را تغییر دهید تا مطابقت دقیق پیدا کنید. برای انجام این کار، آرگومان range_lookup را روی FALSE قرار دهید. هیچ مرتب‌سازی برای FALSE لازم نیست. از تابع INDEX/MATCH برای جستجوی یک مقدار در جدول مرتب نشده استفاده کنید.

ترکیب if و vlookup در اکسل

VLOOKUP یک تابع قدرتمند برای انجام جستجو در excel است. تا زمانی که مطابقت پیدا شود، یک جستجوی ردیفی انجام می‌دهد. تابع IF یک تست منطقی انجام می‌دهد و یک مقدار را برای یک نتیجه TRUE و مقدار دیگری را برای یک نتیجه FALSE برمی‌گرداند.

توابع IF و VLOOKUP با هم در چندین مورداستفاده می‌شوند: برای مقایسه نتایج VLOOKUP، رسیدگی به خطاها، جستجو بر اساس دو مقدار. برای استفاده از توابع IF و VLOOKUP با هم باید تابع VLOOKUP را در داخل تابع IF قرار دهید. می‌توانید از IF و VLOOKUP با هم استفاده کنید و تابع VLOOKUP را در داخل تابع IF قرار دهید. در ادامه مروری دقیق‌تر از کاربردهای تابع IF با VLOOKUP ارائه می‌شود.

پیشنهاد مطالعه: نحوه رفرنس دهی در اکسل

آموزش VLOOKUP در اکسل 2013

در این بخش لوکاپ در اکسل ٢٠١٣ آموزش داده خواهد شد. به عنوان مثال، می خواهید به سرعت قیمت یک محصول را پیدا کنید، برای این کار به سادگی نام کالا را در excel وارد کنید و VLOOKUP بلافاصله قیمت را فیلتر می‌کند. برای مبتدی‌ها، VLOOKUP در ابتدا می‌تواند کمی ترسناک به نظر برسد. فقط این 10 مرحله را در مورد نحوه استفاده از VLOOKUP در Excel 2013 دنبال کرده و از امروز شروع به استفاده از آن کنید.

1- روی سلولی که می‌خواهید فرمول VLOOKUP محاسبه شود کلیک کنید تا ظاهر شود:

2- روی برگه “Formulas” در بالای پنجره کلیک کنید.

3- روی “Lookup & Reference” در نوار کلیک کنید

4- روی “VLOOKUP” در پایین منوی کشویی کلیک کنید.

5- سلولی را مشخص کنید که در آن مقدار داده‌ای را که به دنبال آن هستید وارد می‌کنید. در این مورد، مقدار جستجوی ما H2 است زیرا نام مسابقاتی مانند “PGA Championship” را در آن سلول وارد می‌کنیم. هنگامی که VLOOKUP را به درستی راه اندازی کردیم، اکسل زمانی که نام مسابقات را در سلول H2 تایپ می‌کنیم، مقدار کل امتیاز مسابقات را در سلول H3 نشان می‌دهد.

6- داده‌هایی را که می‌خواهید VLOOKUP برای جستجوی خود در کادر table_array استفاده کند، مشخص کنید. در این مورد، ما کل table را انتخاب کرده‌ایم (به استثنای هدرها). همچنین می‌توانید با کلیک کردن و کشیدن قسمت خاصی از table را انتخاب کنید تا سلول‌های مورد نظر مورد نیاز برجسته شوند (A2:E16).

7- شماره ستونی را که VLOOKUP برای یافتن داده‌های مربوطه در کادر col_index_num استفاده می‌کند، مشخص کنید. عددی را که به آن ارجاع می‌دهد تایپ کنید نه حرف. برای مثال اگر ستون چهارم است، 4 تایپ کنید نه D. VLOOKUP از شما می خواهد که از مقدار عددی ستون به جای مقدار حرف آن استفاده کنید.

8- با وارد کردن FALSE (exact match) یا TRUE (approximate/rounded match) در کادر range_lookup مشخص کنید که آیا به یک تطابق دقیق نیاز دارید یا خیر. در این حالت، ما یک تطابق دقیق می‌خواهیم، بنابراین FALSE را وارد می‌کنیم. برای یادگیری بهتر، بهتر است از آموزش تصویری تابع match نیز استفاده کنید.

9- روی «OK» در پایین پنجره کلیک کنید.

10- مقداری را که داده‌های آن را جستجو می‌کنید وارد کنید. در این مثال، ما می‌خواهیم رتبه‌بندی ارزش کل مسابقات قهرمانی PGA را پیدا کنیم، بنابراین «قهرمانی PGA» را در سلول H2 تایپ می‌کنیم و VLOOKUP به طور خودکار رتبه‌بندی ارزش کل (در این مورد، ۹۱۴) را در سلول H3 تولید می‌کند.

آرگومان‌های LOOKUP

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

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

مثال ساده تابع LOOKUP اکسل

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

=LOOKUP(“لب تاپ”, A2:A10, B2:B10)

در این مثال، اکسل مقدار “لپ‌تاپ” را در محدوده A2:A10 جستجو کرده و مقدار متناظر آن را از محدوده B2:B10 بازمی‌گرداند. اگر مقدار جستجو در محدوده وجود نداشته باشد، تابع نزدیک‌ترین مقدار کوچک‌تر از مقدار جستجو را انتخاب می‌کند که این نکته باید در هنگام استفاده در نظر گرفته شود.

شکل دوم استفاده از تابع LOOKUP

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

برای مثال، اگر یک جدول از داده‌های فروش محصولات در محدوده A2:C10 داشته باشیم که شامل کد محصول، نام محصول و قیمت باشد، فرمول زیر مقدار LOOKUP را به صورت آرایه‌ای اجرا می‌کند:

=LOOKUP(105, A2:C10)

در این فرمول، عدد 105 در ستون اول جستجو شده و مقدار مربوط به آن از آخرین ستون جدول (یعنی ستون C) بازگردانده می‌شود. این روش هنگامی که داده‌های شما در یک جدول ساختاریافته ذخیره شده باشد، کاربرد زیادی دارد.

نکات مهم LOOKUP

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

نکته دیگر این است که اگر مقدار مورد جستجو در محدوده وجود نداشته باشد، تابع نزدیک‌ترین مقدار کوچک‌تر از مقدار موردنظر را بازمی‌گرداند. این رفتار ممکن است در برخی سناریوها مطلوب نباشد، بنابراین در چنین مواردی بهتر است از توابع جایگزینی مانند VLOOKUP یا INDEX/MATCH استفاده شود.

حالت برداری تابع LOOKUP

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

برای مثال، فرض کنید در یک لیست، نمرات دانش‌آموزان در ستون A و اسامی آن‌ها در ستون B قرار دارد. اگر بخواهیم نام دانش‌آموزی که نمره 85 گرفته را پیدا کنیم، می‌توان از فرمول زیر استفاده کرد:

=LOOKUP(85, A2:A10, B2:B10)

در این مثال، تابع مقدار 85 را در محدوده A2:A10 جستجو کرده و نام دانش‌آموز متناظر را از محدوده B2:B10 بازمی‌گرداند. توجه داشته باشید که داده‌های ستون A باید به ترتیب صعودی مرتب شده باشند.

حالت آرایه‌ای تابع LOOKUP

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

برای مثال، فرض کنید در یک جدول، اطلاعات دانشجویان شامل شماره دانشجویی، نام و معدل در ستون‌های A، B و C ذخیره شده است. برای یافتن معدل دانشجویی که شماره دانشجویی آن 12345 است، می‌توان از فرمول زیر استفاده کرد:

=LOOKUP(12345, A2:C10)

در این مثال، تابع مقدار 12345 را در ستون A جستجو کرده و مقدار مربوطه را از آخرین ستون جدول (ستون C) برمی‌گرداند. این روش برای جستجوی داده‌ها در جداول سازمان‌یافته بسیار مفید است.

سخن پایانی

دستور lookup در اکسل از دستورات بسیار کاربری برای بازگردانی مقادیر در جدول داده است. این دستور کاربرد بسیاری زیادی دارد. در این مطلب از مجله مکتب خونه در رابطه با دستور lookup در اکسل و ابعاد مختلف آن توضیحاتی ارائه شد به امید اینکه مورد توجه شما قرار گرفته باشد.

آسانترین راه یادگیری اکسل

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