در فرمول‌نویسی در نرم‌افزار Excel می‌توان به سلول‌های همان صفحه یا Sheet و سایر صفحات و حتی سایر فایل‌های اکسل ارجاع داد. اما گاهی اوقات به فرمولی نیاز داریم که آدرس سلولی که به آن ارجاع داده می‌شود، نیاز به محاسبه دارد و متغیر است. به عنوان مثال فرمولی نیاز داریم که بر اساس محاسبه‌ای، شماره ردیف را حساب کند و به یکی از سلول‌های A1 الی A100 ارجاع دهد. تابعی INDIRECT در اکسل برای این نیاز طراحی شده است.
در این مقاله به آموزش ایندایرکت در اکسل می‌پردازیم و با مثال‌های ساده، نحوه استفاده از INDIRECT در فرمول‌ها را توضیح می‌دهیم. با اینتوتک همراه باشید.
آموزش ارجاع دادن در اکسل
فراخوانی مقدار یک سلول در اکسل به کمک فرمول بسیار ساده است، کافی است علامت = را در سلولی تایپ کنید تا نوشتن فرمول آغاز شود و سپس روی سلول موردنظر کلیک کنید تا مقدار داخل آن فراخوانی شود. به این کار ارجاع دادن گفته می‌شود. به عنوان مثال اگر می‌خواهید مقدار سلول A1 را در سلول D5 داشته باشید، کافی است در سلول D5 فرمول زیر را تایپ کنید:
=A1
برای ارجاع دادن به سلولی که در یک شیت یا صفحه دیگر واقع شده، پس از زدن علامت = روی شیت و سپس روی سلول موردنظر کلیک کنید.
آشنایی با تابع Indirect در اکسل
معمولاً فرمول‌های اکسل با ارجاع دادن به سلول‌های مختلف نوشته می‌شود. بیشتر اوقات روشن است که اعداد و عبارت‌ها در کدام سلول‌ها قرار دارد و در واقع روشن است که باید به چه سلول‌هایی ارجاع داده شود اما گاهی اوقات روشن نیست که باید از کدام سلول در محاسبه‌ای پیچیده استفاده شود. تابع ایندایرکت یا با تلفظی دیگر، تابع ایندیرکت در اکسل برای چنین مواردی طراحی شده است. به صورت مشابه در Google Sheets و برخی از نرم‌افزارهای مشابه Excel مایکروسافت، تابعی با همین نام وجود دارد و روش استفاده کردن از آن مشابه اکسل است.
کاربردهای تابع Indirect در اکسل، موارد زیر است:
- ایجاد ارجاع دینامیک به سلول‌ها که بر اساس محاسبه، آدرس ارجاع تغییر می‌کند.
- ایجاد پیوند بین شیت‌های یک فایل اکسل
- استفاده از اطلاعات موجود در شیت اکسل برای ایجاد آدرس ارجاع
- ایجاد ارجاع دینامیک که با تغییر ساختار فایل اکسل، ثابت می‌ماند.
آموزش استفاده از indirect در اکسل
به طور کلی ۲ نوع ریفرنس یا ارجاع در اکسل داریم:
- ارجاع به یک سلول خاص که آدرس سلول با مشخص کردن نام ستون و شماره ردیف تعریف می‌شود. به عنوان مثال A1 سلولی در اولین ستون و اولین ردیف است.
- ارجاع به محدوده سلول‌ها که با نوشتن آدرس اولین و آخرین سلول که با دونقطه جدا شده، انجام می‌شود. به عنوان مثال برای ارجاع به سلول‌های موجود در ستون اول الی سوم و ردیف ۱ الی ۵ از A1:C5 استفاده می‌شود.
تابع INDIRECT برای هر دو نوع ارجاع کاربرد دارد و صرفاً جای نام سلولی را می‌گیرد. این تابع دو آرگومان دارد که آرگومان اول آدرس ارجاع است و آرگومان دوم، نوع و استایل ارجاع دادن است که دو حالت دارد.
=INDIRECT(x,y)
آرگومان دوم اختیاری است اما آرگومان اول ضروری است. می‌توانید آرگومان اول را با محاسبه کردن و نوشتن فرمول تعریف کنید.
توجه کنید که اگر ارجاع به یک فایل اکسل دیگر داده شده باشد، لازم است که آن فایل باز باشد.
آرگومان دوم که استایل و نوع ارجاع است، ۲ نوع دارد. حالت پیش‌فرض این است که در ارجاع ابتدا شماره ستون و سپس شماره ردیف نوشته شود اما اگر آرگومان دوم را عبارت False وارد کنید، ترتیب برعکس می‌شود و در نتیجه باید در نوشتن آرگومان اول، ابتدا شماره ردیف و سپس شماره ستون را مشخص کنید.
قبل از بررسی مثال استفاده از تابع ایندایرکت در اکسل به ۲ نکته مهم توجه کنید:
تابع Indirect جزو توابعی است که همواره آپدیت می‌شود تا خروجی درستی داشته باشد. لذا اگر تعداد زیادی تابع ایندایرکت در یک صفحه بزرگ اکسل استفاده کنید، اکسل کند می‌شود و حتی مرتباً هنگ می‌کند!
برای چسباندن دو متن که بخش ستون و ردیف یک آدرس را می‌سازد، می‌توانید از نماد & استفاده کنید.
مثال‌های ساده برای آموزش ایندایرکت در اکسل
فرض کنید که می‌خواهید مقداری سلولی که آدرس آن مستقیماً در سلول A1 تایپ شده را در سلولی مثل B1 داشته باشید. به عنوان مثال اگر داخل سلول A1، متن D1 درج شده، مقدار سلول D1 در سلول B چاپ شود و اگر متن سلول A1، عبارت D2 است، داخل سلول B1 مقدار سلول D2 درج شود.
برای این مثال ساده کافی است فرمول زیر را در سلول B1 تایپ کنید:
=INDIRECT(A1)
خروجی این فرمول عدد 5 است که در سلول D1 نوشته شده است.
همان‌طور که اشاره شد، تابع Indirect برای ارجاع به محدوده سلول‌ها نیز مفید است. به عنوان مثال اگر بخواهید جمع اعداد سلول‌هایی که آدرسشان در سلول A2 نوشته شده را مشاهده کنید، می‌توانید از فرمول زیر استفاده کنید:
=SUM(INDIRECT(A2))
اکنون اگر داخل سلول A2 عبارت D1:D5 نوشته شده باشد، به این معنی است که خروجی تابع ایندایرکت، D1:D5 است و جمع اعداد داخل این محدوده سلول محاسبه می‌شود.
تابع ایندایرکت اکسل برای ارجاع به سایر صفحات نیز مفید است. به عنوان مثال فرض کنید که در سلول A3 آدرس سلول A1 در شیت ۲ ذکر شده است. در این صورت خروجی فرمول زیر:
=INDIRECT(A3)
مقدار موجود در سلول A1 از شیت دوم خواهد بود.
نکته‌ی جالب این است که اگر برای محدوده‌ی سلول‌ها یا برای یک سلول، نام مشخص کرده باشید، امکان استفاده کردن از نام به جای آدرس وجود دارد و تابع ایندایرکت دچار اشتباه نمی‌شود.
به عنوان مثال فرض کنید که در نام‌گذاری سلول‌های اکسل، برای سلول A1 از شیت دوم، نام TOTAL را انتخاب کرده‌اید. اکنون اگر در سلول A4 عبارت TOTAL ذکر شود و فرمول زیر را در سلول B4 وارد کنید:
=INDIRECT(A4)
خروجی این فرمول مقدار موجود در سلول A1 شیت دوم خواهد بود. به عبارت دیگر تابع Indirect عبارت TOTAL را به Sheet2!A1 تبدیل کرده است.
و اما یک نکته‌ی مهم در خصوص ارجاع به شیت‌های دیگر با تابع ایندیرکت اکسل:
اگر نام صفحات یا شیت‌های اکسل را تغییر بدهید، تابع ایندیرکت متن ارجاعات را بروزرسانی نمی‌کند و دچار مشکل می‌شود. لذا ابتدا نام صفحات را انتخاب کنید و سپس فرمول‌نویسی کنید.
مثال‌های حرفه‌ای تابع Indirect اکسل
فرض کنید که جدولی حاوی تعداد گل‌های یک تیم فوتبال در مسابقات مختلف دارید و می‌خواهید میانگین گل‌های زده در ۳ بازی آخر را محاسبه کنید. با اضافه شدن نتایج بازی‌های جدید، ردیف‌های جدیدی به جدول اضافه می‌شود و باید فرمول محاسبه میانگین را تغییر بدهید اما اگر ترکیب تابع indirect و count برای شمارش تعداد بازی‌ها را استفاده کنید چطور؟
نتیجه یک فرمول ثابت خواهد بود، نیازی به آپدیت کردن فرمول نیست و همواره میانگین گل‌های زده در سه بازی آخر نمایش داده می‌شود.
هدف این است که فرمول زیر را با تابع ایندیرکت بنویسیم به طوری که به جای B6:B8 همواره آدرس سه سلول آخر از ستون B که تاکنون پر شده، جایگزین شود:
=AVERAGE(B6:B8)
گام اول شمارش سلول‌های ستون B است تا تعداد بازی‌های انجام شده، مشخص شود. از فرمول زیر استفاده می‌کنیم:
=COUNT(B:B)
در این مثال از کاربرد تابع ایندیریک در اکسل، محدوده ۳ سلول آخر که تعداد گل زده است، ردیف ۵ تا ۸ است. ردیف ۸ همان ردیفی است که خروجی تابع شمارش سلول‌های پر شده در ستون B نشان می‌دهد و عدد ۵ نیز با کسر کردن ۲ واحد از خروجی فرمول شمارش، به دست می‌آید.
count(B:B) - 2
لازم است که نام ستون و شماره ردیف را جداگانه در تابع ایندیرکت وارد کنیم. به عنوان مثال برای ارجاع به سلول B1 می‌توان آدرس این سلول را به صورت "B"&1 نوشت. در واقع نماد & برای به هم چسباندن B و عدد 1 به کار رفته است.
بنابراین فرمول محاسبه میانگین گل زده در ۳ بازی آخر را با نوشتن تابع Average برای محاسبه میانگین آغاز می‌کنیم و سپس داخل پرانتز از تابع ایندیرکت استفاده می‌کنیم.
=AVERAGE(INDIRECT
گام بعدی نوشتن آدرس سلول B8 به صورت آپدیت‌شونده و دینامیک است که با چسباندن حرف B به خروجی تابع شمارش سلول‌های پر انجام می‌شود، یعنی:
=AVERAGE(INDIRECT("B"&count(B:B))
برای کوتاه‌تر شدن فرمول، تعداد بازی انجام شده را در سلول E1 قرار می‌دهیم و در واقع فرمول =count(B:B) را داخل سلول E1 تایپ می‌کنیم. به این ترتیب فرمول فوق ساده‌تر می‌شود:
=AVERAGE(INDIRECT("B"&E1)
اکنون باید از دو نقطه استفاده کرد و آدرس آخرین سلول محدوده را مشخص کرد که B6 است.
=AVERAGE(INDIRECT("B"&E1):INDIRECT("B"&E1-1)
فرمول فوق برای حالتی که تیم فوتبال ۸ بازی انجام داده، به همان فرمولی که در ابتدا اشاره کردیم یعنی AVERAGE(B6:B8) تبدیل می‌شود و اگر تعداد بازی انجام شده افزایش پیدا کند، به صورت خودکار B6:B8 تغییر می‌کند.
می‌توانید آدرس سلول حاوی گل زده در ۳ بازی آخر را به صورت سه آرگومان نیز به تابع محاسبه میانگین بدهید که طبعاً فرمول طولانی‌تر می‌شود.
حاصل این فرمول نویسی با تابع Indirect در اکسل را در تصویر زیر بررسی کنید:
ترکیب تابع vlookup و indirect و همین‌طور ترکیب تابع indirect و address در اکسل امکان‌پذیر است و برای حل مسائل پیچیده‌تر مناسب است. با تمرین کردن و مشورت با افرادی که در فرمول‌نویسی با اکسل و گوگل شیت و نرم‌افزارهای مشابه تخصص دارند، می‌توانید شیت‌های اطلاعات مفید و پیشرفته طراحی کنید.
howtogeekاینتوتک