توابع جستجو و مرجع در اکسل

در مقاله ی فرمول نویسی در اکسل، با اصول نگارش فرمول ها و ساختمان فرمول و اجزای فرمول در اکسل آشنا شدیم، همچنین در این مقاله توابع موجود در اکسل را دسته بندی کردیم. توابع جستجو و مرجع در اکسل یا Lookup & Reference Functions مانند توابع ریاضی و توابع متنی که پیشتر با آنها آشنا شدیم، از جمله توابع عمومی اکسل محسوب میشوند که تمام کاربران اکسل به آنها احتیاج دارند، توابع جستجو و مرجع در فایل های اکسلی که محتوی بانک های اطلاعاتی میباشند بسیار کاربرد دارد، برخی از توابع جستجو و مرجع در اکسل مانند توابع VLOOKUP و HLOOKUP بسیار معروف و پرکاربرد هستند. به علت طولانی شدن مقالات توابع جستجو و مرجع در سه مقاله مورد بررسی قرار میگیرد.

توابع جستجو و مرجع در اکسل

اکیداً توصیه میشود پیش از مطالعه ی این نوشته، مقاله ی فرمول نویسی در اکسل را به منظور آشنایی با ساختمان و اجزای فرمول ها و همچنین آشنایی با انواع آرگومان ها و عملگرها در اکسل مطالعه نمایید.

همواره در فرمول ها و توابع اکسل میتوان از سلول های دیگر به عنوان آرگومان استفاده نمود، در مقاله ی آدرس دهی در اکسل با روش های اشاره به سلول ها در فرمول نویسی ها و توابع اکسل آشنا میشویم و در مقاله ی نامگذاری محدوده در اکسل یاد میگیریم که نام دلخواه و متناسب با محتوای هر سلول را برای اشاره به آن سلول در فرمول ها و توابع در اکسل استفاده نماییم، لذا خواندن این مقالات نیز پیش نیاز یادگیری توابع و درک فرمول نویسی در اکسل میباشد.

معرفی توابع جستجو و مرجع در اکسل

توابع جستجو و مرجع در اکسل یا Lookup & Reference Functions توابعی هستند که در بانک های اطلاعات در اکسل به صورت گسترده مورد استفاده قرار میگیرند، کارهایی مانند جستجو در بانک های اطلاعاتی، ارجاع دادن و بسیاری موارد دیگر از جمله کاربردهای توابع جستجو و مرجع در اکسل میباشند. در این مقاله برخی از توابع “مرجع” مورد بررسی قرار میگیرند و در مقاله ی دوم باقی توابع “مرجع” را بررسی میکنیم. با توابع “جستجو” شامل VLOOKUP، HLOOKUP و LOOKUP در مقاله ی سوم آشنا خواهیم شد.

توابع جستجو و مرجع در اکسل

شکل زیر خلاصه ای از توابع جستجو و مرجع در اکسل را نشان میدهد:

فهرست توابع جستجو و مرجع در اکسل

توابع مرجع در اکسل

این دسته از توابع به منظور ارجاع به محدوده یا مشخصه ای از یک محدوده مورد استفاده قرا میگیرند، در ادامه با این توابع آشنا میشویم.

توابع  COLUMN و  COLUMNS در اکسل

از تابع COLUMN در اکسل میتوان شماره ستون یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:

COLUMN (reference)

تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد چهار خواهد بود یعنی شماره ستون سلول D10.

اگر مجموعه ای از سلول ها (Range) را بجای reference  قرار دهیم خروجی تابع شماره سمت چپ ترین ستون خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره ستون سلولی که تابع در آن نوشته شده است میباشد.

تابع COLUMNS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد ستون های یک آرایه را بدست آورد.

توابع  ROW و  ROWS در اکسل

دقیقاً مانند تابع COLUMN عمل میکند ولی این تابع اطلاعات ردیف (Row) را به ما میدهد. از تابع ROW در اکسل میتوان شماره سطر یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:

ROW (reference)

تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد ده خواهد بود یعنی شماره سطر سلول D10.

اگر مجموعه ای از سلول ها (Range) را بجای reference  قرار دهیم خروجی تابع شماره بالا ترین سطر خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره سطر سلولی که تابع در آن نوشته شده است میباشد.

تابع ROWS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد سطر های یک آرایه را بدست آورد.

توابع جستجو و مرجع در اکسل

تابع ADDRESS در اکسل

از تابع ADDRESS در اکسل برای بدست آورد آدرس یک سلول در Worksheet استفاده میشود، Syntax این تابع به صورت زیر است:

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

  • در ساختار این تابع، آرگومان اول و دوم آرگومان های اجباری و عدد هستند و به ترتیب شماره سطر و ستون سلولی هستند که میخواهیم آدرس آن را بدست بیاوریم، آرگومان سوم، چهارم و پنجم آرگومان های اختیاری هستند.
  • آرگومان سوم در مورد نسبی یا مطلق بودن آدرس سلول تعیین تکلیف میکند. این آرگومان میتواند خالی بماند و یا یکی از اعداد ۱، ۲، ۳ یا ۴ باشد، اگر خالی یا عدد یک باشد آدرس دهی به صورت مطلق خواهد بود و اگر عدد ۴ باشد آدرس دهی به صورت نسبی میباشد، اگر میخواهیم آدرس سلول به نحوی بیان شود که سطر به صورت مطلق و ستون نسبی باشد، عدد ۲ و برای برعکس این حالت عدد ۳ را بجای آرگومان سوم قرار دهید.
  • آرگومان چهارم هم یک آرگومان اختیاری و یک مقدار منطقی (Logical Value) میباشد، اگر این آرگومان خالی بماند و یا True باشد، آدرس سلول به فرمت آشنای A1 یعنی شماره سطر عدد و شماره ستون حرف بیان میشود و اگر این آرگومان False باشد فرمت بیان آدرس سلول به صورت R1C1 میشود که سطر و ستون هر دو عدد هستند.
  • و نهایتاً، آرگومان پنجم نام شیت در برگیرنده ی سلول را در صورتی که تمایل داشته باشیم در آدرس سلول ذکر شود را مشخص میکند.

به مثال زیر توجه کنید:

ADDRESS(8;13;3;FALSE;”Sheet1″)= Sheet1!R[8]C13

ADDRESS(8;13;3;TRUE;”Sheet1″)= Sheet1!$M8

برای نشان دادن مطلق بودن آدرس ها در فرمت R1C1 از قرار دادن عدد سطر یا ستون در براکت استفاده میکنیم.

تابع AREAS در اکسل

در اکسل، Area به یک محدوده ی پیوسته از سلول ها شامل یک یا چندین سلول که در حالت انتخاب قرار دارد گفته میشود، مثلاً سلول A1 به تنهایی میتواند یک Area باشد، همینطور محدوده ی A1:C5 هم اگر توسط کاربر انتخاب شود یک Area است. تابع AREAS تعداد Area های یک محدوده (reference) را مشخص میکند. سینتکس این تابع به صورت زیر است:

AREAS (reference)

در عبارت بالا reference تنها میتواند یک Area باشد، برای انتخاب چند Area باید از پرانتز استفاده کنیم، به مثال های زیر توجه نمایید:

AREAS(B2:D4) =1

AREAS((B2:D4,E5,F6:I9)) = 3

AREAS(B2:D4B2) = 1

در مثال سوم دو Area وجود دارند، اولی B2:D4 و دومی B2 است، در واقع منظور از این نوع نوشتن Area ها بدست آوردن Area فصل مشترک دو Area اصلی میباشد که یک Area است و خروجی این تابع عدد یک میباشد، اگر دو Area قرار گرفته به عنوان reference با هم فصل مشترک نداشته باشند، خروجی تابع خطای !NULL# خواهد بود.

توابع جستجو و مرجع در اکسل

تابع CHOOSE در اکسل

سینتکس تابع CHOOSE به صورت زیر است:

CHOOSE(index_num,value1,value2,…)

در عبارت بالا، آرگومان اول عددی است بین ۱ و ۲۵۴ که در واقع شماره مشخصه (Index Number) مربوط به یکی از ۲۵۴ مقدار داده شده به تابع در آرگومان های دوم تا دویست و پنجاه و پنجم میباشد.

آرگومان های Value1، Value2،… تا Value254 میتواند یک فهرست دلخواه کاربر باشد (مانند نام پرسنل، شماره اقلام، آدرس سلول و…) که به ترتیب شماره مشخصه (Index Number) یک تا ۲۵۴ را به خود اختصاص میدهند، خروجی تابع CHOOSE آیتم مربوط به Index_num مورد نظر کاربر میباشد.

اگر عدد قرار گرفته در آرگومان اول کمتر از یک یا بیشتر از تعداد Value ها باشد، خروجی تابع خطای !VALUE# خواهد بود. و اگر عدد Index_num یک عدد اعشاری باشد تنها ارقام صحیح آن عدد در نظر گرفته میشود.

به مثال های زیر توجه کنید:

SUM (CHOOSE (2,A1:A10,B1:B10,C1:C10)) = SUM(B1:B10)

CHOOSE (2,A1:A10,”Excelpro”,۱۳)) = Excelpro

تابع INDEX در اکسل

این تابع دو حالت آرایه ای (Array) و Reference دارد، حالت اول مربوط به آرایه ها میباشد که در مورد آن بعداً بحث خواهیم نمود، سینتکس تابع INDEX به صورت زیر است:

INDEX(reference,row_num,column_num,area_num)

  • این تابع چهار آرگومان دارد، reference که آرگومان اول میباشد آدرس یک یا چند محدوده یا Area میباشد، توجه داشته باشید مانند آنچه در تابع AREAS یاد گرفتیم در اینجا هم اگر بخواهیم به چند محدوده یا Area به صورت همزمان اشاره کنیم نیاز به استفاده از پرانتز داریم.
  • آرگومان دوم و سوم به ترتیب یک شماره سطر و ستون مربوط به reference میباشد که کاربر میخواهد به آن اشاره کند، مثلاً فرض کنید شما محدوده ای B2:E8 را به عنوان reference به تابع معرفی کرده اید، حال میخواهید مقدار سلول C5 را بدانید، بجای آرگومان دوم عدد ۴ و بجای آرگومان سوم عدد ۲ را قرار دهید (زیرا در محدوده ی انتخاب شده B2:E8 سلول C5 در سطر چهارم و در ستون دوم محدوده قرار دارد)، در ضمن اگر reference معرفی شده تنها دارای یک سطر یا ستون باشد میتوانید آرگومان دوم یا سوم را خالی بگذارید.
  • همانطور که گفته شد، میتوانید چند Area را در محل reference به تابع معرفی کنید، در این صورت آرگومان چهارم تعیین میکند کدام Area مورد نظر شما است، به عنوان مثال اگر سه Area به تابع معرفی کنید و آرگومان چهارم عدد دو باشد، تابع در Area دوم به دنبال سطر و ستون مورد نظر شما میگردد.

اگر شماره سطر و سلول قرار گرفته به عنوان آرگومان دوم و سوم در محدوده ی Area مورد نظر نباشد خروجی تابع برابر خطای !REF# خواهد بود.

توابع جستجو و مرجع در اکسل

اشتراک گذاری در شبکه های اجتماعی: share on facebook share on google plus share on linkedin

۹ نظر

  1. احمد

    سلام، در یک شیت اکسل می خواهم به عنوان مثال ببینم نام احمد در کدام سطر وجود دارد. یک فرمولی که وقتی نام را بدهیم در جواب شماره سطر را به مابدهد. با تشکر لطفا جواب را برایم ایمیل نمایید با تشکرmoezzi55@gmail.com

    1. حامد قدیمی

      سلام، از تابع ROWS برای مشخص شدن شماره سطر هر سلول استفاده نمایید و تابع VLOOKUP را برای جستجوی عبارت مورد نظر به کار ببرید، به این ترتیب میتوانید با پیدا شدن عبارت مورد نظر ستونی را که محتوی شماره سطر آن عبارت میباشد قرائت کنید.

  2. تقی لو

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

    1. حامد قدیمی

      برای جستجو در فایل اکسل به صورتی که شما مد نظر دارید، دو تابع VLOOKUP و HLOOKUP و یا LOOKUP میتواند مورد استفاده قرار گیرد، البته توابع CHOOSE و INDEX هم میتواند مفید باشد، برای اطلاع از نام شیت و آدرس سلول میتوانید از توابعی مانند ADDRESS یا CELL استفاده کنید، البته اینطور که شما نوشتید فکر میکنم ماکرو نویسی بتواند مناسب ترین راه برای شما باشد. در بخش پایگاه دانش سایت اسم توابع را جستجو کنید و مقلات مرتبط را مطالعه نمایید، موفق باشید.

  3. قاسم زمانیان

    سلام مرسی از وب سایت خوبتون.
    میخوام در اکسل هر گاه مبلغ حقوق نفرات را به عدد وارد کردم معادل ان را برایم به فارسی بنویسید. مثلا: ۲/۳۰۰/۰۰۰ در روبرو بنویسد دو میلیون و سیصد هزار ریال
    ممنون میشم راهنماییم کنید

    1. حامد قدیمی

      سلام، خواهش میکنم، برای انجام این کار تابعی در اکسل وجود ندارد، شما باید در قسمت کد نویسی ویژوال بیسیک تابعی بنویسید که این کار را انجام دهد. میتوانید در قسمت پرسش و پاسخ این سایت برای نوشتن تابع مورد نظر درخواست کمک کنید. دوستان علاقمند میتوانند کد مورد نظر را برای شما بنویسند. موفق باشید.

  4. N.B.A

    سلام
    من چند دسته داده در یک فایل دارم که هر دسته با یک ردیف فاصله از هم جدا شده است. میخواهم از بین هر دسته تعدادی داده را انتخاب کنم و در یک صفحه جدید کپی کنم که دوباره باید قسمت های انتخاب شده از هر دسته یک ردیف با هم فاصله داشته باشد. تعداد این دسته ها ۱۱۴۰ عدد است و توانایی کپی کردن تک تک آنها را ندارم. آیا شما می توانید به من راهی پیشنهاد کنید؟
    با تشکر

    1. حامد قدیمی

      سلام، قطعاً کاری که میخواهید انجام دهید با استفاده از کد نویسی قابل اجرا هست. اما اینکه کد نویسی مربوط تا چه حد پیچیده باشد بستگی به داده ها و عملیات مورد نظر دارد. اگر نخواهید از کد نویسی استفاده کنید شاید بتوان در اکسل هم این کار را انجام داد که البته نیاز به بررسی دارد.
      موفق باشید.

دیدگاهتان را بنویسید؟