هنگامی که نحوه استفاده از تابع غیرمستقیم در اکسل را بدانید، می‌توانید اطلاعات را از برگه‌های دیگر، محدوده‌های نامگذاری شده مرجع بگیرید، و آن را با سایر توابع ترکیب کنید تا یک ابزار واقعا همه کاره ایجاد کنید. ممکن است کمی تمرین لازم باشد، اما با عملکرد غیرمستقیم، می توانید بیش از آنچه فکر می کنید انجام دهید.
دستورالعمل‌های این مقاله برای Excel برای Microsoft 365، Excel 2019 و Excel 2016 اعمال می‌شود.
تابع غیر مستقیم چیست؟
تابع غیرمستقیم راهی برای تبدیل یک رشته متن به یک مرجع است. یعنی اطلاعات را از یک مرجع به سلول یا محدوده دیگری می گیرد. این یک مرجع از متن ایجاد می‌کند و وقتی سلول‌ها، ردیف‌ها یا ستون‌ها تغییر می‌کنند، اضافه می‌شوند یا از محدوده ذکر شده حذف می‌شوند، تغییر نمی‌کند.ارجاعاتی که ایجاد می کند در زمان واقعی ارزیابی می شوند، بنابراین مرجع همیشه نسبت به داده هایی که از آن استخراج می کند دقیق است.
اگر کمی گیج کننده به نظر می رسد، نگران نباشید. درک فرمول غیرمستقیم با مثال‌های معتبر و در عمل آسان‌تر است. اگر شک دارید، مراحل زیر را دنبال کنید و به زودی آن را حل خواهید کرد.
استفاده از تابع غیر مستقیم با محدوده های نامگذاری شده
محدوده‌های نامگذاری شده در اکسل یک راه عالی برای جمع‌آوری داده‌ها تحت یک مرجع واحد است، و تابع غیرمستقیم گرفتن این اطلاعات از آنها را کمی آسان‌تر می‌کند. در اینجا نحوه انجام آن آمده است:
-
یک سند اکسل را با محدوده‌های نامگذاری شده از قبل باز کنید. در مثال ما، اطلاعات فروش از غذاها و نوشیدنی‌های مختلف را داریم، با پولی که در هر روز هفته به دست می‌آید، در محدوده‌های نامگذاری شده با عنوان محصولات جمع‌آوری شده است.
Image -
یک سلول برای محدوده نامگذاری شده خود انتخاب کنید و یکی از آنها را در آن وارد کنید. در مثال ما از Burgers استفاده کردیم. در صورت تمایل، عناوین و رنگ‌های دیگر را اضافه کنید.
Image -
سلول دیگری را انتخاب کنید که می خواهید خروجی غیرمستقیم در آن قرار گیرد. از آنجایی که ما به دنبال جمع کردن کل حجم فروش در هفته برای یک غذای خاص، در این مورد برگر هستیم، موارد زیر را در سلول تایپ می کنیم:
=SUM(INDIRECT(G5)
-
این یک تابع SUM را تعیین می کند، که از تابع غیر مستقیم برای ترسیم اطلاعات از محدوده نامگذاری شده در سلول G5 استفاده می کند، در این مورد Burgers. خروجی 3781 است، مجموع فروش هفته برای برگر.
در مثال ما، می‌توانیم برگرها را در سلول G5 با لیموناد یا دسر، دو محدوده دیگر نامگذاری شده جایگزین کنیم، و در عوض خروجی به مجموع کل آنها تغییر می‌کند.
Image
استفاده از تابع غیر مستقیم در چندین برگه
فرمول غیرمستقیم زمانی قدرتمندتر است که از آن برای استخراج اطلاعات از برگه های دیگر استفاده می کنید. برای انجام این کار نیز نیازی به استفاده از محدوده های نامگذاری شده نیست.
- سند اکسل خود را با چندین برگه باز کنید یا آنها را با تمام اطلاعات لازم ایجاد کنید.
- در برگه‌ای که می‌خواهید خروجی غیرمستقیم در آن قرار گیرد، یک سلول با نام برگه‌ای که می‌خواهید از آن اطلاعات بگیرید ایجاد کنید. در مثال ما، FoodSales است.
-
از آنجایی که می خواهیم اطلاعات را از برگه FoodSales جمع آوری کنیم تا تعداد برگر فروخته شده را جمع کنیم، موارد زیر را تایپ کردیم (به جای آن محدوده سلولی و نام(های) برگه با خودتان):
=SUM(INDIRECT(B4&"!B4:B10"))
Image - این آن را به عنوان یک تابع SUM تعیین می کند، زیرا ما در تلاش برای یافتن یک کل هستیم. سپس سلول B4 را به عنوان متن مرجع برای تابع غیر مستقیم تعیین می کند. & عناصر این تابع را گرد هم می آورد، به دنبال آن یک نقل قول و علامت تعجب، و سپس محدوده سلول هایی که می خواهیم داده ها را از آنها رسم کنیم. B4 تا B10
- خروجی کل فروش برگر در آن هفته است. وقتی یک برگه FoodSales2 جدید برای یک هفته جدید با اعداد مختلف ایجاد می‌کنیم، فقط باید سلول B4 را طوری تنظیم کنیم که بگوییم FoodSales2 برای دریافت داده‌های مربوط به فروش برگر برای آن هفته.
استفاده از تابع غیر مستقیم با مرجع سبک R1C1
برای برگه هایی که به طور مداوم در حال گسترش هستند، جایی که مرجعی که می خواهید استفاده کنید همیشه در یک سلول قرار نمی گیرد، مراجع سبک R1C1 را می توان با فرمول غیرمستقیم استفاده کرد تا اطلاعات مورد نیاز را در اختیار شما قرار دهد.ما به استفاده از نمونه‌های فروش غذا در اینجا ادامه می‌دهیم، اما تصور کنید که این برای یک کاربرگ سطح بالاتر است که مجموع فروش هفتگی را به طور کلی بررسی می‌کند.
- سند اکسل را با تمام داده‌هایی که می‌خواهید از آنها بکشید باز کنید و یک سلول برای خروجی تابع غیرمستقیم خود انتخاب کنید. در مثال خود، ما به مجموع فروش ماهانه مواد غذایی نگاه می کنیم و می خواهیم جدیدترین کل فروش ماه را بدانیم.
-
در مثال ما، فرمول به این صورت است:
=INDIRECT("R12C"&COUNTA(12:12)، FALSE)
Image - تابع غیرمستقیم از R12 (ردیف 12) و به دنبال آن یک C برای نشان دادن ستونی استفاده می کند که در داخل گیومه محصور شده است. & دو بخش تابع را به یکدیگر متصل می کند. ما از تابع COUNTA برای شمارش تمام سلول‌های غیر خالی در ردیف 12 (انتخاب ردیف یا تایپ 12:12) استفاده می‌کنیم و به دنبال آن یک کاما قرار می‌گیرد. FALSE این را به عنوان یک مرجع R1C1 تعیین می کند.
-
خروجی آخرین ورودی جدول ما است، در این مورد 8102، یا 8،102 دلار. وقتی در نهایت داده‌های فروش آوریل را اضافه می‌کنیم، آخرین شماره فروش به‌طور خودکار به‌روز می‌شود.
Image
