تابع INDIRECT اکسل و نحوه استفاده از آن

نحوه استفاده از تابع INDIRECT اکسل

تابع INDIRECT اکسل و نحوه استفاده از آن

با سلام خدمت همه دوستان. امروز میخوام در خصوص تابع INDIRECT اکسل صحبت کنم. این تابع یکی از توابعی هست که کمتر راجع بهش صحبت میشه اما دونستن نحوه کار با تابع INDIRECT اکسل خیلی مواقع میتونه کمک کننده باشه. در ادامه با نحوه کار با تابع INDIRECT اکسل بیشتر آشنا میشیم. تابع INDIRECT به ما کمک میکنه که یک رنج اکسل رو به صورت داینامیک تغییر بدیم بدون اینکه لازم باشه فرمول اصلی رو تغییر بدیم. قبل از اینکه بریم سراغ آموزش این تابع، باید بگم که درک کاربرد تابع INDIRECT اکسل شاید برای کسانی که زیاد با اکسل حرفه ای نیستند یکم سخت باشه. پس اگر توضیحات رو خوندید و اولش فکر کردید یکم براتون گنگ هست نگران نباشید. وقتی که مثال ها رو هم مطالعه کنید مطمئناً متوجه کاربرد این تابع خواهید شد.

قاعده کلی تابع INDIRECT اکسل:

=INDIRECT(نوع رفرنس, متن رفرنس)

رفرنس: در این پارامتر ما متنی رو که به سلول یا رنج مدنظر ما اشاره میکنه مینویسیم. این پارامتر متنی هست و میتونه به صورت داینامیک از ترکیب مقادیر چند سلول مختلف ایجاد بشه. مثلاً اگر در یک سلول A1 نوشته باشه A و در سلول A2 نوشته شده باشه 10 میتونیم بنویسیم =INDIRECT(A1&A2) که در واقع معادل نوشتن =INDIRECT(“A10”) هست و مقدار سلول A10 رو نمایش میده.

  • نکته 1: اگر رفرنس ما به سلول یا رنجی در یک ورکبوک دیگه باشه، اون ورکبوک باید حتما باز باشه. در غیر اینصورت خطای #REF! نمایش داده میشه.
  • نکته 2: رفرنسی که ما میدیم باید به یک رنج یا سلول معتبر اشاره کنه مانند “A1” یا “A1:A10”. در غیر اینصورت خطای #REF! نمایش داده میشه. مثلاً اگر عبارت “Sheet3!A:A” به عنوان رفرنس به تابع داده بشه و ما در فایلمون sheet3 نداشته باشیم خطای #REF! نمایش داده میشه.
  • نکته 3: همونطور که میدونیم هر شیت اکسل تعداد سطر و ستونهای محدودی داره. یعنی هر شیت اکسل میتونه حداکثر 1,048,576  سطر داشته باشه و حداکثر 16,384 ستون (ستون XFD). حالا اگر شما رفنرسی رو بدید که خارج از این رنج باشه (مثلا سلول A2000000 ) با خطای #REF! مواجه خواهید شد.

فرمت رفرنس: این در این پارامتر ما مشخص میکنیم که فرمت رفرنس ما به چه صورت هست. همونطور که میدونید ما در نرم افزار اکسل دو نوع فرمت رفرنس دهی داریم. یکی همون فرمت آشنای “A1” و نوع دیگه فرمت R1C1. 

  • اگر برای این پارامتر مقداری تعیین نکنیم یا اون رو برابر با TRUE یا 1 قرار بدیم فرمت رفرنس ما از نوع آشنای “A1” خواهد بود. یعنی برای اشاره به سلولی که در ردیف سوم و ستون دوم قرار داره مینویسیم “C2”.
  • اگر این پارامتر رو برابر با  False یا 0 قرار بدیم فرمت رفرنس ما از نوع “R1C1” خواهد بود. یعنی برای اشاره به سلولی که در ردیف سوم و ستون دوم قرار داره باید بنویسیم “R3C2”.

 

شروع با یک مثال ساده:

تصویر زیر رو در نظر بگیرید:

تابع INDIRECT اکسل و نحوه استفاده از آن

فرض کنید ما میخوایم در سلول C1 فرمولی بنویسیم که مقدار سلولی که در B1 رو نمایش بده. برای اینکار مثل تصویر زیر فرمول =INDIRECT(B1) رو مینویسیم:

تابع INDIRECT اکسل و نحوه استفاده از آن

همونطور که مشاهده میکنید با نوشتن این فرمول در سلول C1، عدد 57 نمایش داده میشه. حالا اگر ما مقدار سلول B1 رو به A4 تغییر بدیم سلول C1 به عدد 65 تغیی میکنه. در واقع ما بدون اینکه فرمول C1 رو تغییر بدیم رنج اون رو تغییر دادیم. دقت کنید که چون فرمت رفرنس ما به “A1” هست نیازی به تعیین پارامتر دوم تابع وجود نداره. 

حالا به سلول B2 نگاه کنید. همونطور که میبینید رفرنس در این سلول با فرمت “R1C1” نوشته شده. حالا میخوایم اینبار مشابه تابع قبلی رو با فرمت “R1C1” در سلول C2 بنویسیم. برای اینکار مثل تصویر زیر در سلول C2 فرمول =INDIRECT(B2,0) رو وارد کنید. 

تابع INDIRECT اکسل و نحوه استفاده از آن

همونطور که میبینیم در سلول C2 عدد 65 نمایش داده میشه. دقت کنید که چون فرمت رفرنس دهی ما R1C1 بود حتما باید پارامتر دوم رو برابر 0 قرار بدیم. حالا اگر مثلاً مقدار سلول B2 رو به عبارت “R2C1” تغییر بدیم، مقدار سلول C2 به صورت خودکار به عدد 43 تغییر میکنه و لازم نیست فرمول C2 رو دستی تغییر بدیم.

خب حالا که با کلیت نحوه کار با تابع INDIRECT آشنا شدیم بیاید یه مثال کاربردی تر رو بررسی کنیم

 

مثال کاربردی برای استفاده از تابع INDIRECT اکسل:

فایل اکسل زیر رو در نظر بگیرید:

تابع INDIRECT اکسل و نحوه استفاده از آن

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

تابع INDIRECT اکسل و نحوه استفاده از آن

خب در حالت عادی برای اینکار باید به ازای هر شهر یکبار فرمول نویسی کنید. چیزی شبیه تصویر زیر:

تابع INDIRECT اکسل و نحوه استفاده از آن

حالا تصور کنید اگر تعداد این شیت ها خیلی زیاد باشه اینکار چه کار وقت گیری میشه. ضمن اینکه اگر بخوایم تغییری در فرمول بدیم باید دوباره تک تک فرمول ها رو تغییر بدیم. اما به کمک تابع INDIRECT اکسل میتونیم یکبار فرمول بنویسیم و تا پایین کپی کنیم. در تصویر بالا چیزی که در فرمول هر شهر متغییره و باعث میشه ما نتونیم فرمول سطر اول رو برای بقیه سطرها هم کپی کنیم اسم شهره که در هر ردیف تغییر میکنه. نکته اینجاست که ما اسم شهرها رو در ستون اول داریم. پس میتونیم فرمولمون رو طوری بنویسیم که در هر سطر اسم شهر بصورت خودکار به آدرس اضافه بشه. برای اینکار فرمول زیر رو در سلول B2 مینویسیم:

=SUM(INDIRECT(A2&”!B:B”))

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

تابع INDIRECT اکسل و نحوه استفاده از آن

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

 

دانلود فایل نمونه:

indirect.sample.worksheet.ir

 

 

18 Comments
  • فاطمه
    Posted at 12:45h, 17 ژوئن پاسخ

    با سلام
    من اگه بخواهم به ازای هر 1000 سی سی الکل، 1000 سی سی آب در نظر بگیر
    به ازای هر 2000 سی سی الکل، 2000 سی سی آب در نظر بگیر
    به ازای هر 3000 سی سی الکل، 3000 سی سی آب در نظر بگیر و…
    از چه فرمولی استفاده کنم؟

  • محمود
    Posted at 00:14h, 05 آوریل پاسخ

    سلام
    من میخوام در 3تا سلول آدرس بنویسم
    در سلول اول درایو
    در سلول دوم نام فایل
    در سلول سوم سلول وشیت مقصد

    بعدش بگم با ترکیب این آدرس محتویات اون آدرس برام بیاره اینجا
    عکس زیر شاید بهتر کمک کنه برای رسوندن منظور
    https://s23.picofile.com/file/8448833326/Screenshot_11.png

  • کامران
    Posted at 16:17h, 16 مارس پاسخ

    استاد سلام
    من یه سوال داشتم که فکر کنم با این تابع قابل انجام باشه
    فرض کنید ستون a هر خونه ش یه عدد داره ؛ ما میخایم با یه دستور این ستون رو از اولین خونه تا خونه ای که کاربر وارد میکنه جمع کنه
    مثلا کاربر عدد 4 رو مثلا تو خونه ی B3 وارد میکنه ؛ خروجی فرمول بشه A1 + A2 + A3 + A4
    حالا چه فرمودی بنویسیم که داخل دستور SUM این شماره ردیفی که کاربر وارد میکنه و بتونه بخونه و به عنوان ردیف ستون A در نظر بگیره
    مثلا کاربر عدد X رو وارد میکنه ؛ با دستور SUM باید یشه : SUM(A1:AX) ، این X رو چطور باید به این دستور حالی کرد؟

  • سحر
    Posted at 16:49h, 06 سپتامبر پاسخ

    سلام تمام موارد برای من خطای ref ثبت شد

  • علی
    Posted at 12:34h, 04 آوریل پاسخ

    یعنی عدد در آدرس سلول را (مثلا عدد 1 در آدرس A1 ) را از سلولی که برای INDIRECT مشخص می کنیم می گیرد.

  • mousavi
    Posted at 09:43h, 09 فوریه پاسخ

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

    • محمد قاسم خانیکی
      Posted at 09:36h, 16 فوریه پاسخ

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

  • جواد نعمتی
    Posted at 15:24h, 01 دسامبر پاسخ

    سلام و عرض ادب
    قصد داریم در جدول B مقادیری را فراخوانی کنیم بدین ترتیب که اگر مثلا عبارت Civil در جدول B در ستون اول جدول A وجود داشت، آنگاه مقادیر “عبارات نظیر سر ستونهای جدول “B را از جدول A فراخوانی نماید. در غیر اینصورت بجای #N/A ، عدد 0 نمایش داده شود.

  • Ashkan
    Posted at 11:54h, 21 نوامبر پاسخ

    با سلام
    دستور Indirect برای به صورت زیر جواب نمیدهد دلیلش چیه؟ میخواهم از شیت اول تا شیت یکی مانده به آخر یک سری سلول در بازه $D$2:$D$64 به طور مثال شمارش کند
    (“COUNT(INDIRECT(“‘Sheet(1):(“&SHEETS()-1&”)’!”&”$D$2:$D$64=

  • موسی
    Posted at 15:10h, 18 نوامبر پاسخ

    سلام
    من یه محدوه داینامیک با offset درست کردم حالا میخام لیست کشویی تو در تو درست کنم وقتی محدوده رو میزارم توتابع indirect هیچی نشون نمیده چرا اخه ؟

  • m
    Posted at 05:06h, 29 می پاسخ

    درود یعنی چی فایل بسته باشه
    من فرق فایل بسته بودن و باز بودن نمیدونم راهنمایی میکنید منظورتون
    format cell /protection

    • یاسین اسماعیل پور
      Posted at 16:15h, 30 می پاسخ

      درود بر شما
      معنیش اینه که فایل قابلیت تغییر نداشته باشه

      • حسین پور
        Posted at 22:57h, 02 دسامبر پاسخ

        سلام خداقوت
        ببخشید معنای “داینامیک” چیه و در مقابل چه هست متوجه نشدم!

  • وحید مزینی
    Posted at 09:48h, 19 می پاسخ

    بسیار عالی بود . خیلی دنبال این تابع گشتم. ممنون از توضیحات خوبتون

    • یاسین اسماعیل پور
      Posted at 10:14h, 19 می پاسخ

      ممنون از شما وحید جان بابت انرژی مثبتت

  • maghsoomi
    Posted at 09:24h, 20 آگوست پاسخ

    سلام و خسته نباشید .
    یک مشکلی من رو چند وقته کلافه کرده . ممنون میشم راهنمایی کنید.
    میخوام چند تا عدد رو از یک شیت به شیت دیگه فراخوانی کنم .
    یعنی در شیت2A یه جمع کل دارم . میخوام در شیت گزارش این جمع کل رو فراخوانی کنم . اما با تاریخ . یعنی بگم جمع کل این تاریخ رو برام بیار . من باید گزارش تولید رو در هر روز بگم .
    ممنون میشم کمک کنید.

  • mostafa
    Posted at 16:05h, 06 آگوست پاسخ

    با سلام
    آيا روشي براي برطرف كردن مشكلي كه گفتيد در مواقعي كه از فايل ديگه اي ميخوايم اطلاعات را بخونيم و اون فايل بسته هست وجود داره كه ارور #REF! ندهد؟

    • یاسین اسماعیل پور
      Posted at 14:12h, 17 آگوست پاسخ

      سلام تا جایی که من میدونم اگر فایل شما بسته باشه اطلاعات به صورت لحظه ای آپدیت نمیشضه و باید دستی آپدیت کنید

Post A Comment