توابع جستجو:lookup & refrence

Collapse
X
 
  • زمان
  • نمایش
حذف همه
new posts
  • mrexcel

    • 2010/02/03
    • 436
    • 81.00

    توابع جستجو:lookup & refrence

    [align=RIGHT]توابع جستجو:[/align][align=RIGHT]یکی از مهمترین ابزار هاي مربوط به بانک های اطلاعاتی قابلیت جستجو و پیدا کردن اطلاعات مورد نظر می باشد. بدین ترتیب که اطلاعات مورد نظر با آرگومان تحت جستجو را از اطلاعات استخراج می نماید.
    تابع vlookup:

    Vlookup (lookup-value, table-array, col-index-num, range-
    lookup)
    این تابع مقداری را در ستون اول از جدول آرایه جستجو می کند و مقدار تابع متناظر در همان سطر موجود در ستون دیگر را برمی گرداند.
    [/align][align=RIGHT]منظورازvertical) v ) در vlookup آن است که به صورت ستونی بر روی اطلاعات جستجو را انجام می دهد. آرگومانهای این تابع به صورت ذیل می باشد. [/align][align=RIGHT]Lookup-value: مقداری است که می خواهیم در جدول اطلاعات متناظر با آن را برایمان جستجو نماید.[/align][align=RIGHT]فرض کنید که میخواهیم فامیل شخصی را بدهیم و شماره تلفن آن را بدست بیاوریم فامیل فردlookup value
    میباشد.
    همیشه سعی کنیم اطلاعات ارگومانها را به سلول ارجاع دهیم تا در موقع تغییر مقدار قابل جستجو نیاز به تغییر فرمول نباشد
    .[/align]
    [align=RIGHT]Table-array: محدوده داده ها یا جدول یا databaseکه داریم می باشد. باید دقت نمائیم در انتخاب جدول داده ها حتما ستون اطلاعات مربوط به lookup-valueدر ستون اول جدول داده ها باشد.[/align][align=RIGHT]یعنی اگر ما اسم شخصی را برای جستجو میدهیم جدول را باید طوری انتخاب کنیم که اسم در ستون اول باشد[/align][align=RIGHT]Col-index-num: نمایش دهنده شماره ستونی در جدول یا databaseاست که می خواهیم نتیجه از آن استخراج شود.
    اين شماره ستون شماره ستون در جواب مي باشد كه در محيط
    table array قرار دارد یعنی باید بگوئیم این ستون چندمین ستون در محیط انتخاب شده table array میباشد.[/align]
    [align=RIGHT]Range-lookup: اگر مقدار 0 یا falseوارد نمایید، مقدار دقیق را جستجو می نماید ولی اگر trueیا هر عدد دیگری غیر از صفر را وارد نماییم مقدار را به صورت تقریبی می دهد.یعنی اگر فامیل رضوی را پیدا ننماید فامیل رضوی زاده را جستجو مینماید.[/align][align=RIGHT]در صورتي كه چند گزينه تكراري وجود داشته باشد. اكسل اولين گزينه تكراري را به عنوان جواب در نظر ميگيرد.[/align][align=RIGHT]اگر دقت نمائیم متوجه میشویم در تابع فوق گزینه array دیده میشود در این تابع هم مانند تابع large میتوان از متد آرایه استفاده نمود.[/align][align=RIGHT]یکی از مشکلات عمده در زمینه استفاده از فرمول فوق استفاده از این تابع در سطح وسیع میباشد یعنی برای زمانی که میخواهیم تابع را در یک سلول بنویسیم و در بقیه سلولها درگ کنیم عمده مشکلات زیر بوجود می آید.[/align][align=RIGHT]اولا موقع درگ کردن باید آرگومانها را به دقت آدرس دهی کنیم(علامت$) ثانیا به جای آرگومان
    Col-index-num تابعی نوشته شود که خودش شماره ستون را تشخیص دهد تابع زیر را حل این مسئله است.[/align][align=RIGHT]زیرا در برخی موارد ما از بین تعداد زیادی ستون بانک اطلاعاتی فقط تعداد ستون اندکی را میخواهیم در جواب داشته باشیم.
    تابع match
    =Match (lookup-value, lookup-array, match-type

    این تابع موقعیت گزینه در لیست که خیلی نزدیک به یک مقدار جستجو باشد را برمی گرداند. [/align]
    [align=RIGHT]خروجی این تابع یک عدد است که نشان میدهد این گزینه چندمین گزینه در بین مجموعه انتخاب شده میباشد [/align][align=RIGHT]تابع matchخیلی نزدیک به یک تابع chooseاست. اما choose گزینه موجود در لیست را که توسط موقعیت مشخص شده توسط آرگومان index-numاشغال شده است برمی گرداند. در حالی که تابع match خروجی معکوس آن دارد . و خروجی ان شماره عددی میباشد که انتخاب نموده ایم.
    Lookup-value: مقداری است که می خواهیم برایمان جستجو نماید.[/align]
    [align=RIGHT]Lookup-array: محدوده داده که می خواهیم نتیجه جستجو را از آن استخراج نماییم.[/align][align=RIGHT]Match-type: قوانین جستجو را تعریف می نماید.[/align][align=RIGHT]چنانچه از مقدار صفر استفاده نماییم، مقدار دقیق را جستجو می نماید. [/align][align=RIGHT]فرض کنیم میخواهیم تشخیص دهیم ستون مقابل در مجموعه ستون چندم است [/align][align=RIGHT]با ترکیب تابع vlookupو تابع match به تابعی دست پیدا میکنیم که میتواند تشخیص دهد اطلاعات در چه ستونی است و با درگ کردن آن برای تمام محدوده به جواب با انعطاف و دقت بالا دست پیدا می کنیم . و میتوانیم به راحتی از بین یک مجموعه اطلاعات ستونها و ردیف های خاصی را جدا نمائیم .به آدرس دهی بسیار دقت نمائید در اینجا فرمول را h2 نوشته ایم و درگ نمودیم.[/align][align=RIGHT]تابع Hlookup[/align][align=RIGHT] Hlookup (lookup-value, table-array, row-index-num, range-lookup)=
    تابع فوق اطلاعات را در سطر اول جستجو مینماید و در سطهای بعدی جواب را ارائه میدهد. [/align]
    [align=RIGHT]محاسبه این تابع همانند محاسبه تابع vlookup می باشد. تنها تفاوت این است که جستجو براساس سطرها انجام می گیرد.[/align][align=RIGHT](منظور ازH در Hlookup اختصار کلمه horizontalمی باشد) استفاده از این تابع همواره به مراتب کمتر از تابع vlookup میباشد.[/align][align=RIGHT]فرض کنید ما میخواهیم همیشه اخرین ردیف اطلاعاتی وارد شده را در بالای مجموعه اطلاعات مشاهده نمائیم چون قراراست اطلاعات بر اساس ردیف خاصی مشاهده گردد بنابراین بهترین راه حل استفاده ازتابع hlookup میباشد
    Lookup-value: مقدار که می خواهیم اطلاعات متناظر با آن را براساس سطرها جستجو نمائیم.[/align][align=RIGHT]Table-array: جدول اطلاعاتی که داده ها در آن جستجو می شود. [/align][align=RIGHT]Row-index-num: شماره سطری که جدول آرایه ها باید مقدارش با مقدار مورد نظر تطبیق پیدا نماید. مثلاً مقدار 3 نمایش دهنده سطر سوم در جدول آرایه ها می باشد.در این قسمت هم میتوان از ترکیب تابع match نیز کمک گرفت.[/align][align=RIGHT]Range-lookup: محاسبه تابع vlookup می باشد. مقدار منطقی می باشد که مقدار دقیق با 0 یا false مشخص و مقدار تقریبی را با true مشخص می نماید.
    تابع row
    Row (reference)= شماره سطر سلول را نشان می دهد.این تابع را در حالت آرایه ای نیز میتوان استفاده نمود.
    تابع Index
    Index (array. Row-num. column-num) این تابع اطلاعات محل برخورد یک سطر وستون را بر میگرداند. [/align]
    [align=RIGHT]آرگومانها شکل آرایه ای که یک مقدار را برمی گرداند به صورت زیر است. [/align][align=RIGHT]این تابع جهت انتخاب یک عدد از یک مجموعه آرایه یک یا دو بعدی مورد استفاده قرار می گیرد. این مسئله می تواند انتخاب از یک آرایه باشد یا به صورت انتخاب از مجموعه ها باشد. [/align][align=RIGHT]Array: مجموعه اطلاعات مورد نظر برای انتخاب[/align][align=RIGHT]Row-num: شماره سطر مورد نظرColumn-num: شماره ستون مورد نظرباز هم در این حالت ما میتوانیم اطلاعات سر سطر و سرستون را به جای اعداد ستون وردیف بدهیم به آن بدهیم و محل برخورد انها را به ما بدهد.[/align][align=RIGHT]البته در این حالت هم باید به جای ستون و ردیف تابع match را در آرگومانهای ستون ور دیف بنویسیم.[/align][align=RIGHT](INDEX($A$1:$F$20,MATCH(G1,A1:F1,0),MATCH(G2,A1:F1 ,0 [/align][align=RIGHT]آرگومانهای شکل ارجاعی که یک آدرس سلول را برمی گرداند به صورت زیر است.[/align][align=RIGHT] Index (reference, row-num, column-num, area-num)[/align][align=RIGHT]مزیت شکل دوم تابع indexآن است که می توانید چند ناحیه غیر مجاور از کاربرگ را به عنوان محدوده جستجوی referenceاستفاده نمایید.[/align][align=RIGHT]Area-num: تنها زمانی نیاز است که بیش از یک ناحیه در referenceوجود داشته باشد. این آرگومان ناحیه ای که آرگومانهای row-num و column-num به آن اعمال می شود را معرفی می کند. ناحیه اول که در reference مشخص شده است به عنوان ناحیه1، دومی به عنوان ناحیه2، .... در نظر گرفته می شود. Reference: این آرگومان می تواند یک یا چند محدوده باشد که ناحیه نامیده می شود. هر ناحیه باید مستطیلی باشد و می تواند شامل عدد-متن یا فرمول باشد. اگر ناحیه ها مجاور نباشند باید آرگومان reference را در پرانتز بیاورید. و می تواند تا 29 آرگومان را بپذیرد.[/align][align=RIGHT]تابع choose
    =choose (index-num, value1, value2[/align][align=RIGHT]از تابع choose برای بدست آوردن یک گزینه از بین یک لیست از مقادیر استفاده می شود. [/align][align=RIGHT]Index-num: عدد معینی در لیست است که می خواهید آن را بازگرداند و باید مثبت باشد و بیشتر از تعداد عناصر لیست نیز نباشد.[/align][align=RIGHT]تابع hyperlink
    =hyperlink (link-location, friendly-name
    این تابع برای ساختار ارتباط از داخل سلول یک کاربرگ به کاربرگ دیگرو فایل های دیگررا دارد. [/align]
    [align=RIGHT]با توجه به استفاده خاص از این تابع میتوان این تابع را قوی ترین تابع در زمینه امکانات اکسل با محیط خارج از اکسل دانست.
    [/align][align=RIGHT]فرض کنیم هر هفته ما نامه های اطلاعاتی با فرمت یکسانی از قسمت های مختلف دریافت مینمائیم و پس از تغییر نام انها این اطلاعات را در پوشه قرار میدهیم ما میخواهیم این اطلاعات را بوسیله نرم افزار اکسل مدیریت نمائیم بعد از چند ماه ما پوشه ای داریم با هزارن فایل نامگذاری میخواهیم بوسیله نام این فایل ها را جستجو و باز نمائیم اولین گزینه به ذهن کاربران خطور مینماید استفاده از ابزار hyperlink است مشکل این کار این است که اگر ما 1000 نامه داشته باشیم 1000 لینک اطلاعاتی در فایل اکسل باید ایجاد نمائیم و امکان از بین رفتن اطلاعات ارتباطی بسیار زیاد میباشد.[/align][align=RIGHT]راه منطقی و صحیح آن این است که اسم فایل را در یک سلول بنویسیم و با کیلیک بر روی سلول فایل را باز نمائیم یعنی بتوانیم تمام اسامی را با یک سلول مدیریت نمائیم.[/align][align=RIGHT]البته در استفاده از این روش چندین تکنیک را نیز باید رعایت نمائیم ابتدا به شرح آرگومانهای این تابع میپردازیم.[/align][align=RIGHT]Link-location: یک ارگومان ضروری است، یک مقدار متنی می باشد که مقصد پیوند را مشخص می کند (باید در داخل علامت " ") قرار گیرد.[/align][align=RIGHT]روشهای مختلفی برای بدست اوردن ادرس وجود دارد یکی از روشهای آن این است که به بر روی فایل مورد نظر کلیک راست نمائیم و در قسمت properties ادرس آن را کپی نمائیم البته این ادرس بدون نام فایل و پسوند میباشد . روش دیگر استفاده از ابزار hyperlink در یک سلول خالی و کپی کردن ادرس فایل بوجود امده است [/align][align=RIGHT]Friendly-name: یک آرگومان اختیاری است متنی می باشد که در سلول ظاهر خواهد شد اگر حذف شود اطلاعات آن به صورت پیشفرض آدرس link-locationاست.[/align][align=RIGHT]جهت لینک به سایت های اینترنتی باید پیشوند http:\\ را در آرگومان link-location بیاورید.[/align][align=RIGHT]نوع دیگر کاربرد تابع hyperlink استفاده از تابع vlookup در قسمت link location میباشد بنابراین دراین حالت باید یک جدول بسازیم که تمام ادرسها را داشته باشد و ما نام آن را که به عنوان کلید میباشد جستجو نمائیم و ادرس آن که خروجی vlookup میباشد در محل link location قرار میگیرد . حال برای استفاده از این تابع به صورت روش اول در ادامه مسیر زیر را طی مینمائیم.[/align][align=RIGHT]حال برای بالا بردن انعطاف ان باید قسمتی از نام ادرس را که نام فایل میباشد از ادرس حذف و به جای ان آدرس سلولی را قرار دهید. که میخواهید نام فایل را در ان بنویسید با عطف((&- بنابراین با عطف این آدرس به صورت داینامیک میتوان با هر بار کلیک بر روی فرمول hyperlink آدرسی را باز نمود که آدرس آن را درسلول نوشته بودیم.
    به عنوان مثال ادرس: D:\e-learning1\t03- ویرایش سلول\jozve3.doc
    تبدیل به:" = D:\e-learning1\t03&" - ویرایش سلول\b3&”.doc”میشود.
    آدرس بالا باید در یک سلول نوشته شود و آرگومانlink location باید به آن ارجاع شود.[/align]
    [align=RIGHT]حال اگر ما در سلول b3 نام یک فایل دیگر با را فرمت .doc بنویسیم و بر روی فرمول کلیک کنیم فایل مورد نظر باز میگردد.[/align][align=RIGHT]تابع indirect
    indirect(ref-text,a1.

    ارگومانهای که به این تابع نسبت بدهیم به صورت ادرس فرض میشود.[/align]
    [align=RIGHT]Ref-text: یک شیوه آدرس A1 یا R1C1 یا یک نام سلول باشد. [/align][align=RIGHT]A1: مقدار منطقی است که نشان می دهد چه نوع آدرسی را استفاده می کنید.اگر برابر False باشد اکسل ref-text را به صورت قالب R1C1 تغییر می کند اگر برابر Trueیا خالی باشد. اکسل ref-text را به صورت قالب a1 تغییر می کند. [/align][align=RIGHT]به عنوان مثال اگر در داخل سلولهای b1 مقدار a2 را بنویسیم و اگر در سلول c1 مقدار a10 را بنویسیم و سپس ما در سلولb2 بنویسیم ( sum(b1:c1 قصدمان جمع کردن اطلاعات سلولهای فوق نیست بلکه ما قصد داشتیم اطلاعات سلولهای a2:a10 را جمع نمائیم بنابراین با ید به روشی به اکسل بگوئیم که اطلاعات سلولهای فوق را به صورت آدرس در سلولهای منظور شود.[/align][align=RIGHT]بنابراین تابع باید به صورت زیر نوشته شود
    ((sum(indirect(b1):indirect(c1 =
    در این حالت به جواب مورد نظر دست می یابیم. [/align]
    زنگ تفريح دنيا هميشگي نيست ، ساعت بعد حساب داريم
  • komeilex

    #2
    تابع Vlookupall

    [align=justify]اگه تا حالا با تابع vlookup کار کرده باشین حتما به این مشکل برخوردین که این تابع فقط برای مقادیر منحصر به فرد کارآیی داره. ینی اگر مقدار lookup value ما در لیست تکرار شده باشه، این تابع فقط اولین مقدار از lookup value رو پیدا کرده و مقدار متناظر با اون رو بر میگردونه. برای حل این مشکل معمولا از منحصر به فرد کردن داده ها استفاده می کردیم ولی در فایل زیر این مشکل با تعریف تابع vlookupall حل شده است. این مثال با کمک آقا میلاد با نام کاربری miladman88 وجست و جوی بی وقفه ایشان در سایت های مختلف تهیه شده است. امیدوارم مفید واقع بشه.[/align]
    http://s3.picofile.com/file/7417431826/vlookupall.xlsm.html

    کامنت

    چند لحظه..