آدرس دهی در اکسل با تابع INDIRECT – راهنمای کاربردی

تابع INDIRECT برای آدرس دهی در اکسل

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

آدرس دهی سلول‌ها در اکسل

اگر می‌خواهید نوع آدرس دهی به سلول‌ها را به این شکل درآورید باید از فهرست File گزینه Options را انتخاب و در پنجره ظاهر شده در قسمت Formulas گزینه R1C1 reference style را انتخاب کنید. تنظیمات مربوطه برای انجام این کار در تصویر زیر دیده می‌شود.

نکته: هنگام فرمول نویسی باید توجه داشته باشید که کدام شیوه آدرس دهی در اکسل فعال است. کافی است که به ستون‌های کاربرگ توجه کنید تا مشخص شود کدام شیوه آدرس دهی به کار گرفته شده است.

تابع INDIRECT برای آدرس دهی A1

همانطور که گفته شد، از تابع INDIRECT برای اشاره به آدرس یک سلول به صورت غیر مستقیم استفاده می‌کنیم. در حقیقت کافی است که یک آدرس را به تابع INDIRECT بدهید تا مقدار و محتویات آن سلول را نمایش دهد. شکل دستوری این تابع و پارامترهای آن به صورت زیر است. مشخص است که این تابع دارای دو پارامتر است که اولی یعنی ref_text، اجباری و دومی (a1) اختیاری است.

INDIRECT(ref_text, [a1])

• پارامتر ref_text: در این پارامتر آدرس سلولی که احتیاج به محتویات آن دارید را وارد کنید. این پارامتر مقدارهای متنی را می‌پذیرد. در نتیجه کافی است آدرس مورد نظر را به صورت یک رشته متنی در این پارامتر ثبت کنید؛ مثلا بنویسید B2.
• پارامتر a1: در این پارامتر مشخص می‌کنید که از کدام شیوه آدرس دهی سلول‌ها در پارامتر ref_text استفاده کرده‌اید. این پارامتر، یک مقدار منطقی است. اگر از نحوه آدرس دهی A1 استفاده کرده باشید، مقدار این پارامتر را TRUE و اگر از شیوه آدرس دهی R1C1 کمک گرفته‌اید مقدار FALSE را ثبت کنید. در صورتی که مقدار این پارامتر وارد نشود، اکسل به طور پیش‌فرض مقدار TRUE را در نظر گرفته و شیوه آدرس دهی را طبق استاندارد اکسل به کار می‌گیرد.

در ادامه به مثال‌هایی در این زمینه می‌پردازیم. ابتدا نحوه به کارگیری تابع INDIRECT برای آدرس دهی با شیوه A1 را معرفی می‌کنیم. به تصویر زیر دقت کنید. با توجه به اینکه آدرس‌های مورد نیاز در سلول‌های ستون A معرفی شده‌اند، تابع INDIRECT آدرس را دریافت کرده و محتویات سلول با آدرس مشخص شده را نمایش می‌دهد.

برای مثال از آنجایی که در سلول A1، رشته متنی B2 نوشته شده است، تابع INDIRECT محتویات آدرس سلول B2 را که برابر با 45 است نمایش می‌دهد. همچنین تابع دوم که پارامتر آن به صورت B"&A3" نوشته شده، مشخص می‌کند که باید محتویات سلول B3 نمایش داده شود.

نکته: اگر می‌خواهید دو عبارت متنی را با یکدیگر ترکیب کنید از عملگر & استفاده کنید. در اینجا عبارت "B" با مقدار 3 ترکیب شده و رشته متنی B3 را می‌سازد.

همانطور که دیده می‌شود علامت : برای جداکردن ناحیه شروع و پایان در تابع SUM به کار رفته است و قسمت آغاز برای جمع زدن با ترکیب "A" با محتویات سلول B2 مشخص شده است. قسمت پایان ناحیه جمع نیز با ترکیب "A" با محتویات سلول C2 آدرس دهی شده.

نکته: البته می‌توانیم همه آدرس ناحیه برای جمع کردن را با استفاده از یک تابع INDIRECT و مشخص کردن کل ناحیه در آن مشخص کنیم. به این ترتیب فرمول به صورت زیر در خواهد آمد و همان ناحیه قبلی مشخص و در محاسبه مجموع به کار می‌رود.

=SUM(INDIRECT("A"&B2&":"&"A"&C2))

تابع INDIRECT برای آدرس دهی R1C1

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

=SUM(INDIRECT("R"&B2&"C1"&":"&"R"&C2&"C1",FALSE))

به این ترتیب مشخص است که ترکیب R با محتویات سلول B2 به همراه عبارت C1 محل شروع را  ستون اول از سطر تعیین شده برای جمع تعیین می‌کند. همین کار نیز برای پایان ناحیه جمع انجام شده است. این دقیقا همان کاری بود که با استفاده از آدرس دهی استاندارد یعنی شیوه آدرس دهی A1 در اکسل می‌توان انجام داد.

فیلم آموزش ساخت داشبورد مدیریتی در گوگل‌شیت در فرادرس

کلیک کنید

ولی اهمیت استفاده از تابع INDIRECT در آدرس دهی R1C1 در وجه دیگری است که به کمک تغییر آدرس‌ها بوسیله جابجا کردن نسبی محل و آدرس مورد نظر امکان پذیر است. فرض کنید در جدولی به مانند زیر می‌خواهید مجموع مقدارهای سطر دوم مربوط به سال ۱۳۸۱ تا ۱۴۰۰ را هر پنج سال یکبار با هم جمع کنید. در اینجا می‌توانیم با به کارگیری نحوه آدرس‌دهی R1C1 در زمان صرفه‌جویی کرده و به سادگی محاسبات را انجام دهیم. برای انجام این کار و نشان دادن محل پایان جمع برای هر دوره، محل آغاز را که در سلولی که با عنوان "از" مشخص کرده‌ایم، با مقدار ۵ جمع می‌کنیم. به این ترتیب مثلا برای مجموع ۵ ساله سوم یعنی بازه زمانی 1391 تا 1395 از فرمول زیر کمک می‌گیریم.

=SUM(INDIRECT("R2C"&I6&":"&"R2C"&I6+5,FALSE))

نکته: کافی است مقدار 5 را در این فرمول‌ها به 10 تغییر دهیم تا عمل جمع برای دوره‌های ۱۰ ساله حاصل شود. البته توجه داشته باشید که برای انجام این کار باید سلول‌هایی که برای تعیین آغاز محل جمع در نظر گرفته شده را نیز تغییر دهید.

اگر مطلب بالا برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شود:

• مجموعه آموزش‌های نرم‌‌ افزار اکسل Excel
• آموزش ترفندهای کاربردی در اکسل
• مجموعه آموزش نرم‌ افزارهای Microsoft Office (آفیس)
• آموزش استفاده از توابع و فرمول نویسی در اکسل
• آموزش مقدماتی اکسل (Excel) — به زبان ساده
• مدیریت نوار (Ribbon) اکسل با چهار ترفند کاربردی — راهنمای جامع
• فرمول نویسی در اکسل – آموزش مقدماتی

^^