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

اکسل یکی از نرم‌افزارهای کاربردی گروه آفیس شرکت مایکروسافت محسوب می‌شود. از آنجایی که کار با این «صفحه گسترده» (Spread Sheet) ساده است، طرفدارهای زیادی در بین کاربران رایانه از حسابدارها تا مهندسین دارد. توابع و فرمول‌نویسی در اکسل یکی از جذابیت‌های اصلی در به کارگیری و استفاده از آن محسوب می‌شوند. در این نوشتار به بررسی نحوه استفاده از تابع INDIRECT برای آدرس دهی سلول‌ها در «کاربرگ» (Worksheet) اکسل خواهیم پرداخت. همچنین با توجه به شیوه‌های مختلف آدرس دهی سلول‌ها در اکسل با نحوه به کارگیری این تابع برای آدرس دهی استاندارد و R1C1 آشنا خواهیم شد.

معمولا زمانی از تابع INDIRECT برای آدرس دهی سلول‌ها استفاده می‌کنیم که می‌خواهیم ناحیه‌ای را در یک فرمول و یا توابع دیگر به کار ببریم. به همین سبب اغلب از تابع INDIRECT به صورت ترکیبی در توابع دیگر استفاده می‌کنیم. برای آگاهی بیشتر با نحوه فرمول نویسی و توابع در اکسل بهتر است آموزش ویدیویی استفاده از توابع و فرمول نویسی در اکسل را مشاهده کنید. همچنین خواندن نوشتارهای آموزش مقدماتی اکسل (Excel) — به زبان ساده و فرمول نویسی در اکسل – آموزش مقدماتی نیز خالی از لطف نیست.

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

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

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

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

طبق روش استاندارد و پیش‌فرض اکسل، هر سلول با نام ستون (Column) و شماره ردیف (Row) مشخص می‌شود. از آنجایی که ستون‌ها با حروف الفبای لاتین (...,A, B, C) مشخص شده و ردیف‌ها نیز شماره‌گذاری شده‌اند، آدرس‌ها به صورت ترکیبی از حروف و اعداد مشخص می‌شوند. برای مثال، اولین سلول جدول (محل تقاطع اولین سطر و اولین ستون) با آدرس A1 معرفی می‌شود زیرا در ستون A و در ردیف 1 قرار دارد. البته توجه دارید که همیشه آدرس سلول فعال در قسمت یا «کادر نام» (Name Box) قابل مشاهده است. در تصویر زیر محل این کادر و نمایش آدرس سلول‌ها دیده می‌شود.

شیوه دیگر تعیین آدرس برای سلول‌ها در کاربرگ اکسل، شیوه قدیمی است که در صفحه گسترده‌های قدیمی مانند Lotus 1-2-3 به کار گرفته می‌شد که البته برای هماهنگی با این گونه نرم‌افزارها در اکسل نیز گنجانده شده است. البته شاید در بعضی از مواقع استفاده از این نوع آدرس نویسی باعث بهبود کارها و سرعت بخشیدن به امور محاسباتی شود. در این شیوه آدرس دهی در اکسل، سطرها و ستون‌ها با اعداد شماره‌گذاری شده‌اند و آدرس هر سلول با مشخص کردن شماره سطر و ستون سلول تعیین می‌شود.

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

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

همانطور که دیده می‌شود علامت : برای جداکردن ناحیه شروع و پایان در تابع 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 تغییر دهیم تا عمل جمع برای دوره‌های ۱۰ ساله حاصل شود. البته توجه داشته باشید که برای انجام این کار باید سلول‌هایی که برای تعیین آغاز محل جمع در نظر گرفته شده را نیز تغییر دهید.

برای آشنایی بیشتر با شیوه محاسبات و آدرس‌دهی در تابع INDIRECT و فراگیری نکاتی کاربردی برای حل مسائلی که با اکسل قابل حل هستند، بهتر است دروه آموزش ویدیویی ترفندهای کاربردی در اکسل را مشاهده کنید.

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

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

^^