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

در مقاله ی فرمول نویسی در اکسل، با اصول نگارش فرمول ها و ساختمان فرمول و اجزای فرمول در اکسل آشنا شدیم، همچنین در این مقاله توابع موجود در اکسل را دسته بندی کردیم. توابع جستجو و مرجع در اکسل یا 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. حامد قدیمی

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

  5. مفاخر

    سلام
    آیا تابعی وجود ندارد که با مشخص کردن شماره سطر و ستون دو گوشه از محدوده بتوان آدرس آن محدوده را بصورت مثلاً A1:E6 استخراج نمود؟
    ممنون

    1. حامد قدیمی

      سلام، خیر وجود ندارد. میتوانید این تابع را از طریق کد نویسی در محیط ویژوال بیسیک در اکسل ایجاد نمایید.

  6. حسن

    سوال = اگر در اکسل نمراتی داشته باشیم که بخواهیم با رنگ خاص یا علامتی مشخص شود مثلا نمرات کمتراز ۱۰ را برایمان مشخص نماید باید چه کار کنیم

    1. حامد قدیمی

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

  7. مهدی تاجیک

    با سلام
    میخواستم در یک شیت سرچ سه مرحله ای انجام دهم به این صورت که اگر از بین کدهای بطور مثال a یکی از انها که دارای تاریخ b می باشد (تاریخ مورد نظر) انگاه قیمت ان کد را (c) به من نشان دهد
    میشه راهنمایی کنید

    1. حامد قدیمی

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

  8. اسماعیل

    سلام.آیا در اکسل تابعی وجود دارد که یک مقدار را در یک بازه جستجو نماید و در صورت وجود پیغام TRUE و در صورت عدم وجود پیغام FALSE بدهد.؟

  9. فرشید

    با سلام
    میخواهم با سرچ شماره یک فاکتور که مبلغ آن در طی مدت مثلا یک سال دریک کاربرگ اکسل ثبت شده(بطور مجزا) تمام مبالغ مربوط به آن شماره فاکتور را بدست بیارم .مثل یک لیست.خیلی سپاسگذارم

  10. علیرضا

    سلام و وقت بخیر
    بنده یک ستون از اعداد در اکسل دارم که میخوام بدونم جمع کدوماشون میشه مثلا ۲۱۰۰۰
    در واقع میخوام مجموعه اعدادی رو پیدا کنم که جمعشون بشه ۲۱۰۰۰
    ممنون میشم کمک کنید
    با تشکر

  11. سایه

    با سلام
    امکانش هست تابع رفرنس رو آموزش بدید.. مثلا ی شیت مرجع داریم ک توش کد نوشتیم و شیت دیگمون پر از اطلاعات که وقتی اون کد هارو میزنیم ی کلمه رو نشون بده☺️

    1. حامد قدیمی

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

  12. فرهاد

    با سلام و تشکر از مطالب خوبتون
    یه سوال داشتم
    فرض کنید یک بانک اطلاعاتی شامل تعداد زیادی شماره پلاک ماشین در اختیار داریم
    می خوایم در یک سلول قسمتی از شماره پلاک را وارد کنیم و اکسل اون شماره مورد نظر رو نمایش بده. مثلا بنویسیم ۶۱۱ و تمام پلاک هایی که شامل عدد ۶۱۱ هستند که حداکثر ۲ یا ۳ مورد هست نمایش داده بشه و ما شماره پلاک مورد نظر رو انتخاب کنیم. آیا همچین امکانی در اکسل هست؟

    1. حامد قدیمی

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

  13. محسن

    با سلام
    اگر اطلاعات چند شرکت در دو ستون آورده شده باشد مثلا کد حسابداری و مبلغ. کد شرکت اول از ردیف ۱ تا ۱۰ مثلا ۳۰ باشد از ردیف ۱۰ تا ۲۰ زیر ستون کد، کد شرکت دوم ۴۰ و کد بقیه شرکتها به همین صورت در ردیفهای زیر ستون کد حسابداری نوشته شده باشد و مبالغ در ستون کنار آن و بخواهیم اطلاعات هر شرکت را به شیتهای مجزا انتقال دهیم مثلا اطلاعات شرکت اول که از ردیف ۱ تا ۱۰ بود به شیت ۲ وارد شود و … باید چکار کنیم. لطفا راهنمایی کنید.
    با تشکر

    1. حامد قدیمی

      سلام، به صورت اتوماتیک نمیتوان اطلاعات شرکت های مختلف را به شیت های مختلف اناقال داد حتما باید کد نویسی انجام شود
      باید در شیت های مختلف فرمول های متفوت برای شرکت های نتفاوت ایجاد نماید . موفق باشید.

  14. عباس

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

  15. مجید کرمی

    سلام
    مطلبتون خیلی خوب بود
    راستش من با vlookup کار کردم
    یه مشکل دارم الان
    اگه بخوایم تو چند دسته ستون سرچ کنیم چیکار باید بکنیم؟
    مثلا نام، تلفن ، آدرس ۲۰۰ نفر رو داریم که میشه ۲۰۰ ردیف ۳ ستونه
    با Vlookup راحت میشه با سرچ نام مثلا تلفنشون رو بیاریم
    حالا اگر مجبور باشیم به ۴ تا دسته ۵۰ تایی تقسیم کنیم که میشه ۵۰ ردیف ۱۲ ستونه
    حالا چجوری میتونیم سرچ کنیم؟
    تو ستون اول انجام میده ولی بقیشو نه
    میشه راهنمایی کنید
    ممنون

    1. حامد قدیمی

      سلام، از توابع INDEX و MATCH استفاده کنید، البته باید فایل شما رو ببینم براتون انجامش بدم، برام ارسال کنید، موفف باشید.

  16. حامد

    با سلام و خسته نباشید
    من در یک فایل اکسل تعدای ستون با عناوین مختلف دارم . آیا میشه بر اساس مقدار یک ستون در خروجی مقدار یک ستون دیگه رو دریافت کرد ؟ مثلا ستونی داریم با عنوان “نام پیمانکار” و ستون دیگری داریم با عنوان “نام مشتری” , حالا می خواهیم تمام مشتری هایی که پیمانکارشون مثلا آقای قدیمی هست رو بدست بیاوریم ؟ آیا شدنی هست ؟
    با تشکر فراوان

    1. حامد قدیمی

      سلام، بله، میتوانید از ابزار Advanced Filter استفاده کنید. همچنین میتوانید از فرمول نویی ماتریسی هم استفاده کنید که البته Advanced Filtering راحت تر است. لطفاً مقاله مربوطه را مطالعه کنید. موفق باشید.

  17. مهمان

    سلام وقتتان بخیر
    من یک جدول تو اکسل دارم ک شامل اطلاعاتی در مورد بیماری هاست این جدول شامل ۵ تا ستون هس که نام دوتا از انها “نام بیماری” و “علائم بالینی ” است که بعضی از این بیماری ها در علائم بالینی مشترک هستند من میخوام وقتی ی علائم بالینیو سرچ کردم بیماری هایی ک اون علائم را دارند نشون بده
    ممونم میشم اگ کمک کنین خیلی ضروریه

    1. حامد قدیمی

      سلام، در استفاده از توابع جستجو و مرجع شما این مشکل رو دارید که الولین مورد یاد شده به عنوان خروجی به شما نشان داده میشود. برای رفع این مشکل باید با استفاده از توابع جستجو و مرجه فرمول نویی ماتریسی انجام دهید. راه دوم استفاده از ابزار Advanced Filter است که میتوانید جداول را به دلخواه فیلتر کنید و راه سوم کد نویسی میباشد. موفق باشید.

  18. یوسف

    سلام
    سایت شما خیلی زیبا و مفید هست . دستتون درد نکنه ومتشکرم که جوانان عزیز کشورمان را در فراگیری مطالب یاری میکنید .
    من برای نوشتن یک ماکرو به دستوری نیاز دارم که وقتی یک ردیف را انتخاب و ماکرو را اجرا میکنم شماره ردیف را در ماکرو دریک متغیر ذخیره کنم . قبلا استفاده کرده ام ولی الان یادم رفته خیلی ممنون میشم اگه راهنمایی کنید . تقریبا به شکل زیر بود .

    AS=activrow namber

  19. مهدی

    سلام. من یه مشکل دارم کسی میتونه کمک کنه ؟
    من یه فایل اکسل دارم حاوی ۳۱ شیت مختلف مربوط به روزهای ماه – که داخل هرکدوم اقلا ۳۰۰ داده ورودی وارد شده و حالا به دست من رسیده .
    من میخام بدونم چطوری میتونم تو یه فایل جدید و جداگانه که خودم دارم فرضا داخل فایل قدیمی ۳۱ شیته من جستجو انجام بشه و درصورتی که تاریخی که من تو فایل جدید دادم رو پیداکرد در اونصورت بهم داده موجود در یک سطر و ستون خاص رو بده. ( داخل فایل ۳۱ شیته من یه سلول اختصاصی وجود داره که تاریخ اون رو نوشته شده – اگه بشه به اون رفرنس داد که از فایل جدید اون تاریخ جستجو بشه و درصورت پیداکردنش مقادیر موجود رو بهم بده)
    از اونجا که فایل من قراره یه سری خروجی و نمودار تهیه کنه به همین خاطر میخام طوری باشه که مثلا من تو فایل جدید تاریخ رو بزنم ۹۷/۰۳/۳۱ و اون جستجو کنه تو بانک اطلاعاتی ۳۱ شیته من و مثلا سلول d2 رو از همون شیت که مربوط به ۹۷/۰۳/۳۱ میشه برای من استخراج کنه
    از چه فرمولهایی میتونم کمک بگیرم/ کمک کنینی ممنون

    1. حامد قدیمی

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

  20. shahrokh

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

    1. حامد قدیمی

      سلام، در پاسخ به سوال اول، از توابع VLOOKUP و بهتر از آن از ترکیب توابع INDEX، MATCH و OFFSET میتوانید استفاده کنید. اما در موارد تکراری فقط اولین مورد یافت میشود و برای پیدا کردن موارد دیگر راه دیگری جز کد نویسی (مامرو نویسی) به ذهن من نمیرسد. در پاسخ به سوال دوم، خیر، این مکان در اکسل نیست مگر اینکه کد نویسی انجام دهید. موفق باشید.

  21. shahrokh

    سوال دیگر این که ایا روشی وجود داره که نه مثل vlook & lookup &hlook فقط یک سطر و یا یک ستون رو جستجو کنه بلکه یک عدد رو در یک جدول جستجو کنه.و درضمن وقتی اون عدد رو جستجو کرد در سلول کناری آدرس اون سلول نوشته بشه

    1. حامد قدیمی

      سلام، بله، برای این کار شما باید توابع جستجو و مرجع INDEX، OFFSET ، ADDRESS و MATCH را با هم ترکیب کنید. این کار ممکن است. موفق باشید.

  22. محمد

    سلام ، وقت به خیر
    من می خواهم تابع vlookup مقدار همان سطر یا ستون مورد جستجو را به عنوان خروجی برگرداند نه سطرهای روبه رو را
    برای این کار باید از چه توابعی استفاده کنم و چگونه؟

    1. حامد قدیمی

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

  23. امیر

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

    1. حامد قدیمی

      سلام، انجام این کار در محیط اکسل ممکن نیست و باید ماکرو نویسی انجام دهید. موفق باشید.

  24. مریم

    باسلام
    من میخوام در اکسل که مقادیر تکراری زیاد ، جست و جو کنم و اخرین مقدار رو برام پیدا کنه
    کسی میدونه چجوری میشه؟با دستور if فقط نباشه یه چیز مثل vlookup باشه

    1. حامد قدیمی

      سلام، تابعی برای جستجوی مقادیر تکراری در اکسل وجود ندارد. برای این کار ابزاری مانند Conditional Formatting وجود دارد. که مقادیر تکراری را رنگی میکند، بنابراین تنها راه استفاده از کد نویسی میباشد که البته بسیار حرفه ای و در صورتی که یادبگیرید بسیار کاربردی است. موفق باشید.

  25. مصطفی اسدی

    سلام دوست عزیز
    میشه در این مورد منو راهنمایی کنید
    در این ادرس یک مثال هست ولی من یک سئوال دیگه دارم با توجه به مثال اگر بعد از پیدا کردن شخص مورد نظر دو درامد جداگانه باشه چه جور میشه به دومین یا آخرین درامد دستسرس داشت؟

      1. مصطفی اسدی

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

  26. سارا دهباشی

    سلام
    من با استفاده از تابع if یک فرمولل نوشتم که خروجی اون “بالاتر از حر مراقبت و کمتر از حد مجاز” هست. حالا میخوام عبارت “بالاتر از حد مراقبت” رو قرمز بنویسه و نصف دیگر عبارت “کمتر از حد مجاز” رو سبز بنویسه.
    از conditional formating که استفاده میکنم فقط میتونم رنگ کل عبارت رو عوض کنم اما من میخوام نصف عبارت سبز و نصف دیگه قرمز باشه.
    لطفا راهنماییم کنید.
    ممنون میشم امکانش باشه که پاسخ رو برام ایمیل کنید.
    باتشکر

    1. حامد قدیمی

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

  27. Hamid

    میخوام در یک ردیف تعداد سلول هایی که شامل یک اسم نمی باشند را مشخص کنم؟
    مثلا لیست تعدادافراد بجز علی را در یک سطر مشخص کنم؟

    1. حامد قدیمی

      سلام، برای این کار تنها ابزار استفاده از Conditional Formatting است. با اسفتفاده از این ابزار و توابع Lookup & Reference مانند SEARCH میتوانید سلول های مورد نظر را مشخص نمایید. موفق باشید.

  28. Fereydoon_gh

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

    1. حامد قدیمی

      سلام، برای انجام این کار از توابع Lookup & Reference میتوانید استفاده نمایید و پیشنهاد من استفاده از ترکیب توابع INDEX و MATCH است. موفق باشید.

  29. Behi

    سلام ممنون از سایت خوبتون
    ممنون میشم به منم راهنمایی کنید
    من یه فایل دارم شامل ادرس خونه ها و قیمت فروششون
    میخواستم ببینم چطور میتونم سرچ کنم ادرس خونه هایی رو بهم نشون بده که رنج قیمتش از مثلا صد تا دویست هستش؟
    از چه تابعی باید استفاده کنم ؟

    1. حامد قدیمی

      سلام، خواهش میکنم، برای این کار شما نیاز به استفاده از ابزارهای فیلتر و Advanced Filter دارید، انجام دادن این کار با استفاده از فرمول نویسی نیاز به استفاده از فرمول نویسی ماتریسی و آرایه ای دارد و امری نسبتاً پیچیده است.
      موفق باشید.

یک دیدگاه بگذارید برای اسماعیل برای لغو پاسخ اینجا کلیک کنید.