agwebrunner.com

VLOOKUP ایکسل کے سب سے مفید کاموں میں سے ایک ہے ، اور یہ بھی کم سمجھے جانے والوں میں سے ایک ہے۔ اس مضمون میں ، ہم ایک حقیقی زندگی کی مثال کے ذریعہ VLOOKUP کو بے بنیاد بناتے ہیں۔ ہم استعمال کے قابل بنائیں گے انوائس ٹیمپلیٹ ایک فرضی کمپنی کے لئے۔

VLOOKUP ایکسل ہے تقریب. یہ مضمون فرض کرے گا کہ قارئین کے پاس پہلے سے ہی ایکسل افعال کے بارے میں سمجھنے کی صلاحیت موجود ہے ، اور وہ بنیادی افعال جیسے SUM ، اوسط ، اور آج کا استعمال کرسکتے ہیں۔ اس کے عام استعمال میں ، VLOOKUP ایک ہے ڈیٹا بیس فنکشن ، مطلب یہ ہے کہ یہ ڈیٹا بیس جدولوں کے ساتھ کام کرتا ہے - یا زیادہ آسان ، فہرستیں ایکسل ورکی شیٹ میں چیزوں کی۔ کس طرح کی چیزیں؟ ٹھیک ہے ، کوئی طرح کی چیز آپ کے پاس ایک ورک شیٹ ہوسکتی ہے جس میں ملازمین ، یا مصنوعات ، یا صارفین ، یا آپ کے سی ڈی مجموعہ میں سی ڈیز ، یا رات کے آسمان میں ستاروں کی ایک فہرست ہو۔ اس سے واقعی کوئی فرق نہیں پڑتا ہے۔

یہاں فہرست ، یا ڈیٹا بیس کی ایک مثال ہے۔ اس معاملے میں یہ ان مصنوعوں کی فہرست ہے جو ہماری فرضی کمپنی فروخت کرتی ہے۔

عام طور پر اس طرح کی فہرستوں میں فہرست میں شامل ہر آئٹم کے لئے کچھ الگ الگ پہچان ہوتا ہے۔ اس معاملے میں ، انوکھا شناخت کنندہ "آئٹم کوڈ" کالم میں ہے۔ نوٹ: VLOOKUP فنکشن کے ل a ڈیٹا بیس / فہرست کے ساتھ کام کرنے کے ل that ، اس فہرست میں ایک کالم ہونا چاہئے جس میں انفراد شناخت (یا "کلید" ، یا "ID") ہوتا ہے ، اور وہ کالم ٹیبل کا پہلا کالم ہونا چاہئے. ہمارا مذکورہ نمونہ اس معیار کو پورا کرتا ہے۔

VLOOKUP استعمال کرنے کا سب سے مشکل حص exactlyہ یہ سمجھنا ہے کہ اس کے لئے کیا ہے۔ تو ہم دیکھتے ہیں کہ کیا ہم پہلے یہ واضح کر سکتے ہیں:

VLOOKUP منفرد شناخت کنندہ کی فراہم کردہ مثال کی بنیاد پر ڈیٹا بیس / فہرست سے معلومات حاصل کرتا ہے۔

مندرجہ بالا مثال میں ، آپ VLOOKUP فنکشن کو کسی اور اسپریڈشیٹ میں آئٹم کوڈ کے ساتھ داخل کریں گے ، اور یہ آپ کے پاس اسی شے کی تفصیل ، اس کی قیمت ، یا اس کی دستیابی (اس کی "اسٹاک" مقدار) میں واپس آئے گا جیسا کہ آپ کے اصل میں بیان کیا گیا ہے۔ فہرست ان معلومات میں سے کون سا ٹکڑا آپ کو واپس کردے گا؟ ٹھیک ہے ، جب آپ فارمولا بناتے ہو تو آپ کو اس کا فیصلہ کرنا ہوگا۔

اگر آپ کو ضرورت ہو تو ڈیٹا بیس سے حاصل کردہ معلومات کا ایک ٹکڑا ہے ، اس میں VLOOKUP فنکشن کے ساتھ کوئی فارمولا تیار کرنے کے لئے جانا بہت پریشانی ہوگی۔ عام طور پر آپ اس طرح کی فعالیت کو دوبارہ استعمال کے قابل اسپریڈشیٹ میں استعمال کریں گے ، جیسے ایک ٹیمپلیٹ۔ ہر بار جب کوئی درست آئٹم کوڈ داخل کرتا ہے تو ، اس نظام سے متعلقہ آئٹم کے بارے میں تمام ضروری معلومات بازیافت کرلیتا ہے۔

آئیے اس کی ایک مثال بنائیں: این انوائس ٹیمپلیٹ کہ ہم اپنی فرضی کمپنی میں بار بار استعمال کرسکتے ہیں۔

پہلے ہم ایکسل شروع کرتے ہیں ، اور ہم خود کو ایک خالی رسید بناتے ہیں۔

اس طرح کام ہو رہا ہے: انوائس ٹیمپلیٹ کا استعمال کرنے والا شخص کالم "A" میں آئٹم کوڈز کی ایک سیریز کو پُر کرے گا ، اور سسٹم ہر پروڈکٹ کے ڈیٹا بیس سے ہر شے کی تفصیل اور قیمت بازیافت کرے گا۔ اس معلومات کا استعمال ہر آئٹم کے لئے کل لائن کا حساب کتاب کرنے کے لئے کیا جائے گا (فرض کریں کہ ہم ایک درست مقدار داخل کرتے ہیں)۔

اس مثال کو آسان رکھنے کے مقاصد کے لئے ، ہم مصنوع کا ڈیٹا بیس اسی ورک بک میں ایک الگ شیٹ پر تلاش کریں گے۔

حقیقت میں ، اس بات کا زیادہ امکان ہے کہ مصنوع کا ڈیٹا بیس ایک علیحدہ ورک بک میں واقع ہو۔ اس سے VLOOKUP فنکشن میں تھوڑا سا فرق پڑتا ہے ، جو اس بات کی پرواہ نہیں کرتا کہ اگر ڈیٹا بیس ایک ہی شیٹ ، ایک مختلف شیٹ ، یا بالکل مختلف ورک بک پر واقع ہے۔

لہذا ، ہم نے اپنا مصنوع کا ڈیٹا بیس تیار کیا ہے ، جو اس طرح لگتا ہے:

ہم لکھنے والے VLOOKUP فارمولے کی جانچ کے ل، ، ہم پہلے اپنے خالی رسید کے سیل A11 میں ایک درست آئٹم کوڈ درج کریں:

اگلا ، ہم فعال سیل کو سیل میں منتقل کرتے ہیں جس میں ہم VLOOKUP کے ذریعہ ڈیٹا بیس سے حاصل کردہ معلومات کو ذخیرہ کرنا چاہتے ہیں۔ دلچسپ بات یہ ہے کہ یہ وہ قدم ہے جس سے زیادہ تر لوگ غلط ہوجاتے ہیں۔ مزید وضاحت کرنے کے لئے: ہم ایک VLOOKUP فارمولہ تیار کرنے والے ہیں جو سیل A11 میں آئٹم کوڈ سے مماثلت پانے والی وضاحت کو بازیافت کرے گا۔ جب ہمیں یہ تفصیل مل جاتی ہے تو ہم اسے کہاں رکھنا چاہتے ہیں؟ یقینا cell سیل B11 میں۔ لہذا اسی جگہ پر ہم VLOOKUP فارمولا لکھتے ہیں: سیل B11 میں۔ ابھی سیل B11 منتخب کریں۔

ہمیں ایکسل کو پیش کردہ تمام دستیاب افعال کی فہرست تلاش کرنے کی ضرورت ہے ، تاکہ ہم VLOOKUP کا انتخاب کرسکیں اور فارمولے کو مکمل کرنے میں کچھ مدد حاصل کریں۔ یہ پہلے پر کلک کر کے پایا جاتا ہے فارمولے ٹیب ، اور پھر کلک کریں فنکشن داخل کریں:

ایک باکس ظاہر ہوتا ہے جو ہمیں ایکسل میں دستیاب افعال میں سے کسی ایک کو منتخب کرنے کی اجازت دیتا ہے۔

جس کی ہم تلاش کر رہے ہیں اسے ڈھونڈنے کے لئے ، ہم تلاش کی اصطلاح جیسے "تلاش" ٹائپ کرسکتے ہیں (کیونکہ جس فنکشن میں ہم دلچسپی رکھتے ہیں وہ ایک ہے اوپر دیکھو فنکشن)۔ یہ نظام ہمیں ایکسل میں تلاش سے متعلق تمام افعال کی فہرست لوٹائے گا۔ VLOOKUP اس فہرست میں دوسرا نمبر ہے۔ اس پر کلک کریں ٹھیک ہے.

فنکشنل دلائل باکس ظاہر ہوتا ہے ، ہمیں سب کے لئے اشارہ کرتا ہے دلائل (یا پیرامیٹرز) VLOOKUP فنکشن کو مکمل کرنے کے لئے ضروری ہے۔ آپ اس بکس کے بارے میں سوچ سکتے ہیں جیسے فنکشن ہم سے مندرجہ ذیل سوالات پوچھتا ہے:

1. آپ ڈیٹا بیس میں کون سا انوکھا شناخت کار تلاش کر رہے ہیں؟
2. ڈیٹا بیس کہاں ہے؟
3. ڈیٹا بیس سے معلومات کا کون سا ٹکڑا ، منفرد شناخت کنندہ کے ساتھ وابستہ ، کیا آپ اپنے لئے بازیافت کرنا چاہتے ہیں؟

پہلے تین دلائل دکھائے گئے ہیں نمایاں طریقے سے، اشارہ کرتے ہیں کہ وہ ہیں لازمی دلائل (VLOOKUP فنکشن ان کے بغیر نامکمل ہے اور ایک درست قدر واپس نہیں کرے گا)۔ چوتھی دلیل جر boldت مند نہیں ہے ، مطلب یہ ہے کہ یہ اختیاری ہے:

ہم اوپر سے نیچے تک ، دلائل کو ترتیب سے مکمل کریں گے۔

ہمیں پہلی دلیل مکمل کرنے کی ضرورت ہے دیکھو_ قیمت دلیل. فنکشن میں ہمیں یہ بتانے کی ضرورت ہے کہ منفرد شناخت کنندہ کہاں تلاش کیا جائے آئٹم کوڈ اس معاملے میں) کہ اس کی تفصیل واپس کرنا چاہئے۔ ہمیں پہلے داخل کردہ آئٹم کوڈ کا انتخاب کرنا چاہئے (A11 میں)۔

پہلی دلیل کے دائیں طرف سلیکٹر کے آئیکون پر کلک کریں:

پھر آئٹم کوڈ (A11) والے سیل پر ایک بار کلک کریں اور دبائیں داخل کریں:

"A11" کی قدر پہلی دلیل میں داخل کی گئی ہے۔

اب ہمیں اس کے لئے ایک قدر داخل کرنے کی ضرورت ہے ٹیبل_ریے دلیل. دوسرے لفظوں میں ، ہمیں VLOOKUP کو بتانے کی ضرورت ہے کہ ڈیٹا بیس / فہرست کو کہاں تلاش کریں۔ دوسری دلیل کے ساتھ سلیکٹر آئیکون پر کلک کریں:

اب ڈیٹا بیس / فہرست کا پتہ لگائیں اور پوری فہرست منتخب کریں - ہیڈر لائن کو شامل نہیں۔ ہماری مثال میں ، ڈیٹا بیس ایک علیحدہ ورک شیٹ پر واقع ہے ، لہذا ہم پہلے اس ورک شیٹ ٹیب پر کلک کریں:

اگلا ہم ہیڈر لائن سمیت پورے ڈیٹا بیس کو منتخب کریں:

… اور دبائیں داخل کریں. سیلز کی رینج جو ڈیٹا بیس کی نمائندگی کرتی ہے (اس معاملے میں "’ پروڈکٹ ڈیٹا بیس ’! A2: D7“) ہمارے لئے خود کو دوسری دلیل میں داخل کردیا جاتا ہے۔

اب ہمیں تیسری دلیل داخل کرنے کی ضرورت ہے ، Col_index_num. ہم اس دلیل کو VLOOKUP کی وضاحت کرنے کے لئے استعمال کرتے ہیں کہ ڈیٹا بیس سے کون سی معلومات کا ٹکڑا ، A11 میں ہمارے آئٹم کوڈ سے وابستہ ہے ، ہم چاہتے ہیں کہ ہم واپس جائیں۔ اس خاص مثال میں ، ہم چاہتے ہیں کہ آئٹم کی چیز ہو تفصیل ہمارے پاس لوٹ آئے۔ اگر آپ ڈیٹا بیس ورکشیٹ پر نظر ڈالتے ہیں تو آپ دیکھیں گے کہ "تفصیل" کالم ہے دوسرا ڈیٹا بیس میں کالم۔ اس کا مطلب یہ ہے کہ ہمیں لازمی طور پر "2" کی قدر داخل کرنا چاہئے Col_index_num ڈبہ:

یہ نوٹ کرنا ضروری ہے کہ ہم یہاں "2" داخل نہیں کر رہے ہیں کیونکہ "تفصیل" کالم اس میں ہے بی اس ورک شیٹ پر کالم۔ اگر کالم میں ڈیٹا بیس کا آغاز ہوا K ورک شیٹ میں ، ہم ابھی بھی اس فیلڈ میں ایک "2" درج کریں گے کیونکہ "ٹیبل_یری" کی وضاحت کرتے وقت ہم نے منتخب کردہ سیلوں کے سیٹ میں دوسرا کالم ہے جس کا انتخاب "وضاحت" کرتے ہیں۔

آخر میں ، ہمیں یہ فیصلہ کرنے کی ضرورت ہے کہ آیا حتمی VLOOKUP دلیل میں کوئی قیمت داخل کرنا ہے ، رینج_لوک اپ. اس دلیل میں یا تو ایک کی ضرورت ہے سچ ہے یا جھوٹا قدر ، یا اسے خالی چھوڑ دینا چاہئے۔ ڈیٹا بیس کے ساتھ VLOOKUP استعمال کرتے وقت (جیسا کہ 90٪ وقت سچ ہے) ، اس دلیل میں کیا ڈالنا ہے اس کا فیصلہ کرنے کا طریقہ ذیل میں سوچا جاسکتا ہے:

اگر ڈیٹا بیس کا پہلا کالم (کالم جس میں انوکھا شناخت کار ہے) حروف تہجی کے مطابق / عددی اعتبار سے ترتیب وار ترتیب میں ترتیب دیا گیا ہے تو اس کی قیمت درج کرنا ممکن ہے سچ ہے اس دلیل میں ، یا اسے خالی چھوڑ دیں۔

اگر ڈیٹا بیس کا پہلا کالم ہے نہیں ترتیب دیا گیا ، یا اس کو ترتیب سے ترتیب میں ترتیب دیا گیا ہے ، پھر آپ لازمی کی قدر درج کریں جھوٹا اس دلیل میں

جیسا کہ ہمارے ڈیٹا بیس کا پہلا کالم ہے نہیں الگ الگ ، ہم داخل جھوٹا اس دلیل میں:

یہی ہے! ہم نے VLOOKUP کو اپنی ضرورت والی قیمت واپس کرنے کے لئے درکار تمام معلومات داخل کردی ہیں۔ پر کلک کریں ٹھیک ہے بٹن اور نوٹ کریں کہ آئٹم کوڈ "R99245" سے متعلق تفصیل سیل B11 میں صحیح طور پر داخل کردی گئی ہے۔

ہمارے لئے جو فارمولا تشکیل دیا گیا تھا وہ اس طرح لگتا ہے:

اگر ہم داخل کریں a مختلف سیل A11 میں آئٹم کوڈ ، ہم VLOOKUP فنکشن کی طاقت دیکھنا شروع کردیں گے: نئے آئٹم کوڈ سے مماثلت کرنے کیلئے تفصیل سیل تبدیل ہوجاتا ہے۔

ہم شے کے حاصل کرنے کے ل steps اسی طرح کے اقدامات انجام دے سکتے ہیں قیمت E11 سیل میں لوٹ آیا۔ نوٹ کریں کہ نیا فارمولا E11 سیل میں بنانا ضروری ہے۔ نتیجہ اس طرح نظر آئے گا:

… اور فارمولا اس طرح نظر آئے گا:

نوٹ کریں کہ دونوں فارمولوں میں صرف تیسرا دلیل ہی ہے۔Col_index_num) ایک "2" سے "3" میں بدل گیا ہے (کیونکہ ہم ڈیٹا بیس میں تیسرے کالم سے ڈیٹا حاصل کرنا چاہتے ہیں)۔

اگر ہم نے ان میں سے 2 اشیاء خریدنے کا فیصلہ کیا تو ، ہم سیل D11 میں "2" داخل کریں گے۔ اس کے بعد ہم لائن FT حاصل کرنے کے لئے سیل F11 میں ایک آسان فارمولا داخل کریں گے۔

= D11 * E1

… جو ایسا لگتا ہے…

انوائس ٹیمپلیٹ کو مکمل کرنا

ہم نے ابھی تک VLOOKUP کے بارے میں بہت کچھ سیکھا ہے۔ در حقیقت ، ہم نے سب کچھ اس مضمون میں سیکھنا ہے۔ یہ نوٹ کرنا ضروری ہے کہ VLOOKUP کو ڈیٹا بیس کے علاوہ دوسرے حالات میں بھی استعمال کیا جاسکتا ہے۔ یہ کم عام ہے ، اور اس کا احتمال مستقبل میں کس طرح کے مضمون کے بارے میں ہوگا۔

ہمارا انوائس ٹیمپلیٹ ابھی مکمل نہیں ہوا ہے۔ اسے مکمل کرنے کے لئے ، ہم مندرجہ ذیل کام کریں گے:

1. ہم سیل A11 سے نمونہ کے آئٹم کوڈ اور سیل D11 سے "2" کو نکال دیں گے۔ اس سے ہمارے نئے بنائے گئے VLOOKUP فارمولوں میں خرابی کے پیغامات ظاہر ہوں گے۔

   
   

   
   ہم ایکسل کے جائز استعمال سے اس کا تدارک کرسکتے ہیں اگر () اور ISBLANK () افعال. ہم اس سے اپنا فارمولا تبدیل کرتے ہیں… = VLOOKUP (A11 ، ’مصنوع کا ڈیٹا بیس‘! A2: D7،2 ، غلط)… اس…= اگر (ISBLANK (A11) ، "" ، VLOOKUP (A11 ، ’مصنوع کا ڈیٹا بیس‘! A2: D7،2 ، غلط))

   
   

2. ہم خلیوں B11 ، E11 اور F11 میں موجود فارمولوں کو انوائس کے آئٹم لائنوں کے باقی حصوں میں کاپی کریں گے۔ نوٹ کریں کہ اگر ہم یہ کرتے ہیں تو ، نتیجے میں آنے والے فارمولوں کا اب صحیح طریقے سے ڈیٹا بیس ٹیبل کا حوالہ نہیں دیا جائے گا۔ ہم ڈیٹا بیس کے لئے سیل حوالوں کو تبدیل کرکے اسے ٹھیک کرسکتے ہیں مطلق سیل حوالہ جات متبادل کے طور پر - اور بھی بہتر - ہم ایک تشکیل دے سکتے ہیں رینج کا نام پورے پروڈکٹ ڈیٹا بیس (جیسے "پروڈکٹ") کے ل for ، اور سیل حوالوں کی بجائے اس رینج کا نام استعمال کریں۔ اس سے فارمولا بدلا جائے گا… = اگر (ISBLANK (A11) ، "" ، VLOOKUP (A11 ، ’مصنوع کا ڈیٹا بیس‘! A2: D7،2 ، غلط))… اس… = اگر (ISBLANK (A11) ، "" ، VLOOKUP (A11 ، مصنوعات ، 2 ، غلط))… اور پھر انوائس آئٹم کے باقی صفوں میں فارمولوں کی کاپی کریں۔
3. ہم شاید ان خلیوں کو "مقفل" کردیں گے جو ہمارے فارمولوں پر مشتمل ہیں (یا بجائے) غیر مقفل کریں دوسرے خلیات) ، اور پھر ورک شیٹ کی حفاظت کریں ، تاکہ یہ یقینی بنائے کہ جب کوئی انوائس کو پُر کرنے آئے تو ہمارے احتیاط سے تیار کردہ فارمولوں کو غلطی سے اوور رائٹ نہیں کیا جائے۔
4. ہم فائل کو بطور محفوظ کریں گے سانچے، تاکہ ہماری کمپنی میں ہر ایک اسے دوبارہ استعمال کر سکے

اگر ہم محسوس کر رہے تھے واقعی ہوشیار ، ہم کسی اور ورک شیٹ میں اپنے تمام صارفین کا ڈیٹا بیس بنائیں گے ، اور پھر سیل ایف 5 میں داخل کردہ کسٹمر آئی ڈی کا استعمال خود بخود سیل 6 ، B7 اور B8 میں گاہک کا نام اور پتہ پر کرنے کے لئے کریں گے۔

اگر آپ VLOOKUP کے ساتھ مشق کرنا چاہتے ہیں ، یا ہمارا نتیجہ انوائس ٹیمپلیٹ کو دیکھ سکتے ہیں تو ، اسے یہاں سے ڈاؤن لوڈ کیا جاسکتا ہے۔