پرفروش ترين
برترين
آخرين محصولات فروشگاه
فایل الکترونیکی آموزش اکسل پیشرفته ۲۰۱۰
آموزش ایجاد فایل چندکاربره با سطح دسترسی مشخص
یوزرفرم پیشرفته -Advanced  User Form
داشبورد (مقدماتی) – Dashboards(Elementary)
ابزارهای اعتبار سنجی و اخطار دهنده
بسته آموزشی userform
مدیریت و کنترل خطا در اکسل
توابع متنی
گرافیک در اکسل
جزوه آموزشی جداول و نمودارهای پاشنه ای
فایل آموزشی نحوه تهیه فرمت عددی دلخواه یا custom number format cell
آموزش ایجاد فایل چندکاربره با سطح دسترسی مشخص
نمایش نتایج: از شماره 1 تا 2 , از مجموع 2

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

  1. #1


    محصولات کاربر

    <a href=
    آخرین بازدید
    یک هفته پیش
    تاریخ عضویت
    February_2010
    محل سکونت
    مشهد
    نوشته ها
    426
    امتیاز
    887
    سپاس
    253
    سپاس شده
    818 در 183 پست

    mrexcel به Yahoo ارسال پیام

    توابع جستجو: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]
    اخطار: این یک موضوع قدیمی است
    به دلیل قدیمی بودن موضوع، ممکن است برخی فایل های ضمیمه به درستی کار نکنند. لطفا در صورت عدم ضرورت، از بالا آوردن موضوعات قدیمی خودداری نمایید.

  2.  

  3. #2
    komeilex
    مهمان

    تابع Vlookupall

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


اطلاعات موضوع

کاربرانی که در حال مشاهده این موضوع هستند

در حال حاضر 1 کاربر در حال مشاهده این موضوع است. (0 کاربران و 1 مهمان ها)

موضوعات مشابه

  1. جستجوی تمامی اقلام یک فاکتور ذخیره شده
    توسط khorasan66 در انجمن توابع اکسل - Excel Functions
    پاسخ ها: 1
    آخرين نوشته: 21 _ 02 _ 2013, 00:39
  2. جواب به سوالی در مورد جستجو با دو فاکتور
    توسط ~M*E*H*D*I~ در انجمن توابع اکسل - Excel Functions
    پاسخ ها: 7
    آخرين نوشته: 24 _ 12 _ 2012, 10:41
  3. دسترسی به محتوای کامنت از طریق فرمول
    توسط ~M*E*H*D*I~ در انجمن سوالات پیرامون ويژوال بيسيك - VBA Questions
    پاسخ ها: 0
    آخرين نوشته: 13 _ 06 _ 2012, 11:44
  4. (شرح)دسترسی به توابع داخلی اکسل از طریق VB
    توسط Javid Mokhtari در انجمن سوالات پیرامون ويژوال بيسيك - VBA Questions
    پاسخ ها: 0
    آخرين نوشته: 17 _ 02 _ 2012, 13:58
  5. اضافه شدن توابع تخصصي تر در توابعي مانند توابع آماري
    توسط exceliran در انجمن توابع اکسل - Excel Functions
    پاسخ ها: 1
    آخرين نوشته: 10 _ 08 _ 2011, 06:00

بازدید کنندگان با جستجو های زیر این صفحه را پیدا کرده اند

مفهوم R1C1 در VBE

R1C1در اکسل چیست فرمان

s3.picofile.comfile7417431826vlookupall.xlsm

مجوز های ارسال و ویرایش

  • شما نمیتوانید موضوع جدیدی ارسال کنید
  • شما امکان ارسال پاسخ را ندارید
  • شما نمیتوانید فایل پیوست کنید.
  • شما نمیتوانید پست های خود را ویرایش کنید
  •  
  • BB code ها فعال هستند
  • شکلک ها فعال هستند
  • کد [IMG] فعال است
  • کد [VIDEO] فعال است
  • کد HTML غیر فعال است