تابع VlOOKUP اکسل
شاید بسیاری از شما دوستان با نرم افزار های کتابخانه دانشگاه یا کتابخانه محل زندگی خود کار کرده باشید.وقتی کد یا نام کتاب را وارد می کنید اطلاعات مربوط به آن کتاب شامل نام نویسنده،سال انتشار،تعداد صفحه،انتشارات و… را به شما نمایش می دهد.
جدول زیر را در sheet1 اکسل ما موجود می باشد.حالا می خواهیم در sheet2 وقتی کد کتاب را وارد کردیم تمام مشخصات آن کتاب را به ما نمایش دهد.برای این کار از تابع vlookup استفاده می کنیم.
تابع vlookup با جستجو در ستون اول جدول داده ها، مقدار مشخصی(کد کتاب)را یافته سپس مقدار متناظر با آن را در ستون های بعدی(نام کتاب،نام نویسنده و تعداد صفحه) راپیدا کرده و به ما نمایش می دهد.
این تابع دارای 4 بخش می باشد:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
lookup_value:همان مقدار مورد نظر ما در ستون اول هست(کد کتاب که به سلول G5 نسبت داده شده است).دقت کنید ما در اینجا کد کتاب(عدد) را ملاک قرار دادیم در حالی که می توانستیم متن یا True و False را هم ملاک قرار دهیم.
table_array:جدول مورد نظر ما که در sheet1 موجود هست.
col_index_num:شماره ستون در جدول مورد نظز که می خواهیم مقدار آن را به ما نمایش دهد.(در این مثال ما می خواهیم نام کتاب را به ما نمایش دهدپس ستون 2 را انتخاب می کنیم)
range_lookup:این قسمت در تابع اختیاری می باشد.این گزینه به تابع می گوید که چطور مقدار مورد نظر را پیدا کند(همان کد کتاب در ستون اول) و این یک نکته بسیار مهم می باشد.
اگر عبارت TRUE یا عدد 1 را تایپ کنید:اگر lookup_value (همان کد کتاب)در ستون جدول مورد نظر یافت نشد،نزدیکترین مقدار را برای ما بر می گرداند.
اگر عبارت FALSE یا عدد 0 را تایپ کنید:دقیقا همان lookup_value را جستجو می کند.. اگر پیدا نکرد خطای #N/A را نمایش می دهد.
اگر قسمت range_lookup را خالی بگذارید،این تابع TRUE را در نظر می گیرد.
در مثال زیر کد 1090 را وارد کرده ایم و میدانیم که این کد در لیست کد های کتاب ما نمی باشد.این تابع نزدیکترین نتیجه در جدول و در ستون سوم را به ما نشان می دهد.(نویسنده E. Joseph Billo)
پس هر گاه خواستید فرمول شما دقیقا کد مورد نظر را پیدا کند، حتما عدد 0 را تایپ کنید.مانند فرمول زیر:
=VLOOKUP(G5,Sheet1!A1:C15,2,0)
اکنون می توانید تفاوت این تابع با تابع Hlookup را بهتر درک کنید.
توجه:
همان طور که مشاهده کردید تابع Vlookup در ستون اول جستجو را انجام میدهد و در ستون های بعدی مقدار متناظر آن را نمایش می دهد.اما عکس آن ممکن نیست.به تصویر زیر دقت کنید.اگر در ستون B دنیال کد ملی بگردیم و بخواهیم نتیجه را در ستون A نمایش دهد، توانایی انجام این کار را ندارد.(یعنی توانایی جستجو در جهت عکس را ندارد)
یکی از راه های رفع این مشکل استفاده از تابع Choose می باشد.
Excel
Word
PowerPoint
OneNote
Outlook
Access
Publisher
Project
Visio
با سلام فايل اكسل پيوست را ملاحظه نماييد هركاري ميكنم نميتوانم از طريق vlookup از روي شماره كد تفضيلي مبلغ بدهكار دو sheet را باهم تطابق دهم اگر راهنمايي كنيي ممنون ميشوم
خیلی خوبه فقط اگه می شد پرینت گرفت بهتر بود
خوبه
باتشکر
درود،خیلی خیلی ممنون از اینکه دانشتون رو در اختیار همه قرار میدید.
سلام خوبید ؟
متاسفانه من هر کاری کردم نتونستم تابع را راه بندازم دقیقا ثل دستورالعملتون اقدام کردم و همش n/a میزنه میشه راهنمایی کنید ممنون
ممنون از راهنماییتون
فقط یک مشکلی دارم وقتی سلول رو بسط می دم خطای ref می دهد و در فرمولش محدوده جدولی که باید پبدا کند نمی افتد. لطفا کمکم کنید
سلام یک لیست شامل اسامی ومشخصات دارم که میخواهم این لیست رو با لیست قدیمم مقایسه کنه و در صورتی که نام جدیدی به لیست اضافه شده بود نام های جدید رو برای من در یک شیت جدید نشون بده لطفاراهنمایی کنید از چه تابعی استفاده کنم
سلام
راه ها و ابزار های زیادی وجود داره.به عنوان مثال:
حذف کلمات یا اعداد تکراری
سلام.این تابع بعضی وقتها اشتباه عمل میکنه.مثلا جدول دو ستونه ای بکشیم که در ستون اول اعداد به ترتیب از 1 تا 32 باشه و در ستون دوم حروف الفبای فارسی به ترتیب نوشته شده باشه.خواهید دید در بعضی موارد اشتباه عمل میشود.لطفا جوابتان را به ایمیلم ارسال کنید.ممنون
بسیار ممنونم از اموزش تون
یه سوال:در مثال بالا اگه بخواهیم دوتا ستون رو نمایش بده (هم نام کتاب و هم نام نویسنده )در ردیف سوم فرمول(COL_INDEX_NUM) چی باید نوشته بشه.
با سلام و خسته نباشید خدمت کلیه عزیزان:
در خصوص سوال قبلی که در قبلاً پرسیده بودم؛
می خواستم بگم که تابع Vlookup تمام خروجی هاشو تو یه خونه وارد می کنه و اطلاعات قبلی پاک میشه. برای حل این مشکل چه باید کرد؟
با سلام واقعاً خسته نباشید
می خواستم بدونم که اگه بخوام یکسری اطلاعات با یک کد مشابه رو در یک صفحه نمایش بدم چیکار باید بکنم؟
مثلاً اطلاعات در مورد چند دانش آموز که در کلاسهای مختلف مشغول به تحصیلند و با نوشتن شماره کلاس، تمام اطلاعات مربوط به دانش آموزانی که در این کلاس درس میخوانند در یک فایل فراخوانی شود.
با سلام.
لطفا روش group by کردن در اکسل را توضیح دهید.
با تشکر.
با سلام.من میخوام در یک sheet بیاد ستون اول را چک کند و سلولهایی که هم نام است را مقادیرش را که در ستون سوم مقابل اسمشان در ستون اول است را باهم جمع کند.لطفا مرا راهنمایی کنید.
سلام. خیلی ممنونم از وب سایت عالیتون
سلام، دستت درد نکنه. 30 ثانیه بعد از آموزش، حرفه ای شدم.
ببخشید من یک سوال دارم
من دو تا جدول دارم که در هر دو یک سری کد مشترک وجود داره من میخوام موجودی اون کدهای مشترک در جدول یک را بیاری در کنار کد مشترک جدول دو بنویسه اما نمیتونم جواب نمیده روشم درسته فکر کنم ولی وقتی میرم سرچ میکنم میبینم یک سری کد ها رو درست آورده مقدارشو ولی یک سری رو اصلا نیاورده و نشون داده که پیدا نکرده در صورتی که هست
عالی بود
سلام و خسته نباشید
تابع مذکور یک مشکل اساسی دارد و آن این که اگر شما دو تا کد کتاب یکسان داشته باشید ( یا مثلا فاکتور فروش) این تابع آخرین کتاب مشابه را بازگردانی میکند. من هرچی تو اینتر نت جستوجو کردم راهی مفید پیدا نکردم ( تابع vlookupall مشکلش در این که همه را در یک سلول جایگزین میکنه)
با سلام و تشکر
این مشکل نیست.بلکه خاصیت vlookup این هست که کد ها یونیک باشند .و اصل سیستم کد بندی هم همین هست.یعنی کد ها تکراری نباشند.و اما برای خواسته شما:
برای اینکه بتوانید کد کتاب یکسان را پیدا کنید.به جای آن از Auto number استفاده کنید(برای پیدا کردن و شمردن کد های تکراری) و سپس به جای کد کتاب از شماره ها برای lookup_value استفاده کنید.به این طریق شما می توانید کتاب های با کد یکسان را پیدا کنید.
موفق باشید
سلام
ممنون که این تابع کاربردی را معرفی کردین خیلی به دردم خورد
اما یک سوال دارم اینکه چطور میتونم (مثلا در مثال بالا) بعد از پیدا کردن کد کتاب و برگرداندن نام کتاب آن ردیف را به طور هوشمند حذف کنم
با سلام
این کار به کمک VBA امکان پذیر هست.
موفق باشید