با سلام خدمت همه دوستان. امروز میخوام در خصوص تابع 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”.
شروع با یک مثال ساده:
تصویر زیر رو در نظر بگیرید:
فرض کنید ما میخوایم در سلول C1 فرمولی بنویسیم که مقدار سلولی که در B1 رو نمایش بده. برای اینکار مثل تصویر زیر فرمول =INDIRECT(B1) رو مینویسیم:
همونطور که مشاهده میکنید با نوشتن این فرمول در سلول C1، عدد 57 نمایش داده میشه. حالا اگر ما مقدار سلول B1 رو به A4 تغییر بدیم سلول C1 به عدد 65 تغیی میکنه. در واقع ما بدون اینکه فرمول C1 رو تغییر بدیم رنج اون رو تغییر دادیم. دقت کنید که چون فرمت رفرنس ما به “A1” هست نیازی به تعیین پارامتر دوم تابع وجود نداره.
حالا به سلول B2 نگاه کنید. همونطور که میبینید رفرنس در این سلول با فرمت “R1C1” نوشته شده. حالا میخوایم اینبار مشابه تابع قبلی رو با فرمت “R1C1” در سلول C2 بنویسیم. برای اینکار مثل تصویر زیر در سلول C2 فرمول =INDIRECT(B2,0) رو وارد کنید.
همونطور که میبینیم در سلول C2 عدد 65 نمایش داده میشه. دقت کنید که چون فرمت رفرنس دهی ما R1C1 بود حتما باید پارامتر دوم رو برابر 0 قرار بدیم. حالا اگر مثلاً مقدار سلول B2 رو به عبارت “R2C1” تغییر بدیم، مقدار سلول C2 به صورت خودکار به عدد 43 تغییر میکنه و لازم نیست فرمول C2 رو دستی تغییر بدیم.
خب حالا که با کلیت نحوه کار با تابع INDIRECT آشنا شدیم بیاید یه مثال کاربردی تر رو بررسی کنیم
مثال کاربردی برای استفاده از تابع INDIRECT اکسل:
فایل اکسل زیر رو در نظر بگیرید:
در این فایل چندین شیت مختلف به ازای هر شهر وجود داره که در ستون B اون اطلاعات فروش هر شهر مثل تصویر زیر به تفکیک قرار داره. ما میخوایم در شیت اول جمع فروش همه شهرها رو در کنار هم قرار بدیم.
خب در حالت عادی برای اینکار باید به ازای هر شهر یکبار فرمول نویسی کنید. چیزی شبیه تصویر زیر:
حالا تصور کنید اگر تعداد این شیت ها خیلی زیاد باشه اینکار چه کار وقت گیری میشه. ضمن اینکه اگر بخوایم تغییری در فرمول بدیم باید دوباره تک تک فرمول ها رو تغییر بدیم. اما به کمک تابع INDIRECT اکسل میتونیم یکبار فرمول بنویسیم و تا پایین کپی کنیم. در تصویر بالا چیزی که در فرمول هر شهر متغییره و باعث میشه ما نتونیم فرمول سطر اول رو برای بقیه سطرها هم کپی کنیم اسم شهره که در هر ردیف تغییر میکنه. نکته اینجاست که ما اسم شهرها رو در ستون اول داریم. پس میتونیم فرمولمون رو طوری بنویسیم که در هر سطر اسم شهر بصورت خودکار به آدرس اضافه بشه. برای اینکار فرمول زیر رو در سلول B2 مینویسیم:
=SUM(INDIRECT(A2&”!B:B”))
حالا اگر این فرمول رو تا انتها کپی کنیم جمع فروش تمام شهرها مثل تصویر زیر به درستی در مقابل هر شهر نمایش داده میشه.
امیدوارم این آموزش هم براتون مفید بوده باشه. همینطور در صورتی که مایل باشید میتونید فیلم آموزش این تابع رو هم در اینستاگرام وبسایت مشاهده کنید.
دانلود فایل نمونه:
با سلام
آيا روشي براي برطرف كردن مشكلي كه گفتيد در مواقعي كه از فايل ديگه اي ميخوايم اطلاعات را بخونيم و اون فايل بسته هست وجود داره كه ارور #REF! ندهد؟
سلام تا جایی که من میدونم اگر فایل شما بسته باشه اطلاعات به صورت لحظه ای آپدیت نمیشضه و باید دستی آپدیت کنید
سلام و خسته نباشید .
یک مشکلی من رو چند وقته کلافه کرده . ممنون میشم راهنمایی کنید.
میخوام چند تا عدد رو از یک شیت به شیت دیگه فراخوانی کنم .
یعنی در شیت2A یه جمع کل دارم . میخوام در شیت گزارش این جمع کل رو فراخوانی کنم . اما با تاریخ . یعنی بگم جمع کل این تاریخ رو برام بیار . من باید گزارش تولید رو در هر روز بگم .
ممنون میشم کمک کنید.
بسیار عالی بود . خیلی دنبال این تابع گشتم. ممنون از توضیحات خوبتون
ممنون از شما وحید جان بابت انرژی مثبتت
درود یعنی چی فایل بسته باشه
من فرق فایل بسته بودن و باز بودن نمیدونم راهنمایی میکنید منظورتون
format cell /protection
درود بر شما
معنیش اینه که فایل قابلیت تغییر نداشته باشه
سلام خداقوت
ببخشید معنای “داینامیک” چیه و در مقابل چه هست متوجه نشدم!
سلام
من یه محدوه داینامیک با offset درست کردم حالا میخام لیست کشویی تو در تو درست کنم وقتی محدوده رو میزارم توتابع indirect هیچی نشون نمیده چرا اخه ؟
با سلام
دستور Indirect برای به صورت زیر جواب نمیدهد دلیلش چیه؟ میخواهم از شیت اول تا شیت یکی مانده به آخر یک سری سلول در بازه $D$2:$D$64 به طور مثال شمارش کند
(“COUNT(INDIRECT(“‘Sheet(1):(“&SHEETS()-1&”)’!”&”$D$2:$D$64=
سلام و عرض ادب
قصد داریم در جدول B مقادیری را فراخوانی کنیم بدین ترتیب که اگر مثلا عبارت Civil در جدول B در ستون اول جدول A وجود داشت، آنگاه مقادیر “عبارات نظیر سر ستونهای جدول “B را از جدول A فراخوانی نماید. در غیر اینصورت بجای #N/A ، عدد 0 نمایش داده شود.
سلام وقت بخیر
من میخوام تاریخ رو از شیت های مختلف طبق این فرمول در شیت اصلی بیارم آیا امکانش هست؟
سلام موسوی عزیز
بله ، انجام این کار ممکنه
بازم اگه به مشکلی برخوردین ، در سایت مطرح کنید تا همه با کمک هم ، حلش کنیم
موفق باشی – خانیکی
یعنی عدد در آدرس سلول را (مثلا عدد 1 در آدرس A1 ) را از سلولی که برای INDIRECT مشخص می کنیم می گیرد.
سلام تمام موارد برای من خطای ref ثبت شد
استاد سلام
من یه سوال داشتم که فکر کنم با این تابع قابل انجام باشه
فرض کنید ستون a هر خونه ش یه عدد داره ؛ ما میخایم با یه دستور این ستون رو از اولین خونه تا خونه ای که کاربر وارد میکنه جمع کنه
مثلا کاربر عدد 4 رو مثلا تو خونه ی B3 وارد میکنه ؛ خروجی فرمول بشه A1 + A2 + A3 + A4
حالا چه فرمودی بنویسیم که داخل دستور SUM این شماره ردیفی که کاربر وارد میکنه و بتونه بخونه و به عنوان ردیف ستون A در نظر بگیره
مثلا کاربر عدد X رو وارد میکنه ؛ با دستور SUM باید یشه : SUM(A1:AX) ، این X رو چطور باید به این دستور حالی کرد؟
سلام
من میخوام در 3تا سلول آدرس بنویسم
در سلول اول درایو
در سلول دوم نام فایل
در سلول سوم سلول وشیت مقصد
بعدش بگم با ترکیب این آدرس محتویات اون آدرس برام بیاره اینجا
عکس زیر شاید بهتر کمک کنه برای رسوندن منظور
https://s23.picofile.com/file/8448833326/Screenshot_11.png
با سلام
من اگه بخواهم به ازای هر 1000 سی سی الکل، 1000 سی سی آب در نظر بگیر
به ازای هر 2000 سی سی الکل، 2000 سی سی آب در نظر بگیر
به ازای هر 3000 سی سی الکل، 3000 سی سی آب در نظر بگیر و…
از چه فرمولی استفاده کنم؟