EXCEL
آشنایی با نرم افزار گمنام ولی جادویی اکسل
10 آبان 88

VLOOKUP

10 آبان 88

موضوع :توابع، 

به نام خدا                                                

سلام

به اعتقاد من فرمولهای خانواده look up یكی از مهمترین دسته  فرمولهای موجود در اكسل است كه باز به نظر من مهمترین آنها vlook up  هستش كه من به امید خدا می خواهم توضیح بدهم كه امید وارم بتونم مطلب را برسونم .

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

تعداد برد تیم استقلال اهواز را بدست بیاورید :

 

A

B

C

D

E

F

G

H

1

جدول لیگ فوتبال ایران

2

رتبه

نام تیم

بازی

برد

باخت

مساوی

تفاضل

امتیاز

3

1

استقلال

334

333

-

1

100

1000

4

2

سپاهان

334

290

42

2

80

872

5

3

ذوب اهن

334

270

61

3

70

813

6

4

پیروزی

334

260

70

4

50

784

7

5

استقلال اهواز

334

216

113

5

20

653

 

خوب توی این جدول  جواب مورد نظر ما  216 هستش كه برای پیدا كردنش از فرمول زیر استفاده می كنیم به این صورت :

= VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COLUMN_INDEX_NUMBER,RANGE_LOOKUP)

= VLOOKUP(B7,$B$3:$H$7,3,0)

خب حالا توضیح :

LOOKUP_VALUE : این پارامتر مقدار ارزش نشان میده كه مبنای جستجوی ماست كه در مثال تیم استقال اهواز كه در خانه B7 قرار داده . یعنی اگه ما می خواستیم  در مورد ذوب آهن اطلاعات بدست بیاوریم باید B5 را استفاده می كردیم .

TABLE_ARRAY : این پارامتر محدوده ای كه باید جستجو در آن محدوده انجام شود را مشخص می كند كه هم میتواندبا LOOKUP_VALUE در یك SHEET  یا در  SHEET  دیگر باشد و اینكه از $ استفاده كردن دلیلش اینه كه خانه شروع و پایان ثابت به ماند چون اگه از $ استفاده نشه محدوده مورد نظر معلق می شه وا با توجه به LOOKUP_VALUE تغییر میكنه و ممكن جواب تغییر كنه  .البته نكته لازم به ذكر اینه كه شما می توانید از یك دامنه كه نام گذاری كرده اید نیز استفاده كنید ( یعنی مجموعه های از سلول ) .

COLUMN_INDEX_NUMBER : این پارامتر ستونی كه جواب مورد نظر ما در آن قرار دارد را نشان می دهد كه در مثال ما اگر تعداد باخت را می خواستیم عدد به 4 تغییر می كرد یا اگر تفاضل گل را می خواستیم به 6 می بایست استفاده می شد .

RANGE_LOOKUP : اما آخرین پارامتر كه فقط یا 0 هستش یا 1 كه اگه صفر باشه عینا دنبال نام استقلال اهواز می گردد اما اگه یك باشه استقلال را پیدا خواهد كرد كه معمولا از صفر بیشتر استفاده می شود .

نكته : اگه چند LOOKUP_VALUE مشابه در TABLE_ARRAY باشد اولین ردیف پیدا خواهد شد لطفا دقت كنید .

چند تا مثال :

امتیاز  استقلال :                                                = VLOOKUP(B3,$B$3:$H$7,7,0)

تعداد باخت پیروزی :                                          = VLOOKUP(B6,$B$3:$H$7,4,0)

تعداد تساوی ذوب اهن :                                       = VLOOKUP(B5,$B$3:$H$7,5,0)

تفاضل گل  سپاهان :                                           = VLOOKUP(B4,$B$3:$H$7,6,0)


شاد ، سلامت و با دلی آرام 

 «سؤال ها و جواب ها»

 باسلام و خسته نباشی خدمت شما تابع  vlookup
برای داده تکراری در یک شیت یاشیتهای یک کاربرگ 
چطور کارمیکنه؟

سلام  واقعیتش این است كه بسیار سخت می شود 
بزودی 2 فرمول  جدید در وبلاگ قرار میدهیم 
كه با تركیب وی لوكاپ آفست مچ این سه فرمول 
می توانید  سلول های تكراری را پیدا كنید .

 ***

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

سلام   لطفا این مطلبو بخونید فقط لازمه
 كه هنگام مسیر دادن مسیر شیت قبل را  وارد كنید

 http://excel.mihanblog.com/post/57

 ***

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

 ***

 با سلام. ضمن تشکر در مورد اطلاعات مفیدتان
اگر بخواهیم در یک ستون در اکسل  عدد
 به صورت تصادفی (Random) بین اعداد نوشته شده
 در ستون (مثلا ستون B) را اتخاب کنیم
 چگونه باید اینکار را انجام دهیم؟ با تشکر

سلام ، اگر عدد تصادفی می خواهید از تابع راند بیتوین 
استفاده کنید ولی اگر می خواهید از اعداد موجود در ستون B
عددی را انتخاب کند با ترکیبی از ویلوکاپ و راند بیتوین
می توانید این کار را بکنید به این صورت:

راند بیتوین را در قسمت  LOOKUP_VALUE  بنویسید
و حتما در ستون A شماره ردیف را بنویسید.

A               |               B

34                              1
22                              2
45                              3

در خانه ی مورد نظر بنویسید:

(vlookup(randbetween(1;3);A1:B3;2;0=

نتیجه:
 بعد از هر بار رفرش شدن صفحه یکی از اعداد
ستون B نمایش داده می شود( 34 یا 22 یا 45 )
این فرمول ترکیبی در واقع یک عدد بین 1 تا 3 رندوم 
انتخاب می کند و آن عدد را در ستون A پیدا کرده
و عدد متناظرش را نمایش می دهد.

 ***

 
س15) با عرض سلاملطفاً اگر امكان دارد 
فرمول تركیبی از LEN را كه چندین سلول كه
 در آنها كلماتی نوشته شده، كدام بزرگترین
 كراكتر را دارد از بین آنها مشخص شود.با تشكر

برای این کار در ستون قبل خانه های مورد نظر
فرمول len را می نویسید ( برای یکی می نویسید 
و برای بقیه کپی می کنید ) سپس از طریق MAX و
 Vlookup کلمه ای که بیشترین حرف را دارد پیدا می کنید.