تابع INDIRECT

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به بررسی تابع OFFSET پرداختیم، در این فصل به آموزش تابع INDIRECT می پردازیم.

سؤالات این فصل:

• فرمول‌های کاربرگ معمولاً به سلول‌ها، محدوده‌ها یا هر دوی این‌ها ارجاعاتی دارند. آیا به‌جای اینکه این ارجاعات را در فرمول‌ها تغییر داد می‌توان آنها را در سلول‌های خود قرار دهیم تا به‌آسانی بتوان ارجاع‌های سلول یا محدوده را بدون تغییر فرمول‌ها تغییر داد؟
• هریک از کاربرگ‌های یک فایل اکسل فروش ماهیانه محصولی را در سلول D1 فهرست کرده‌اند. آیا راه آسانی برای نوشتن و کپی فرمولی وجود دارد که بتواند هر فروش ماهیانه کالایی را در یک کاربرگ جداگانه فهرست کند؟
• فرض کنید مقادیر موجود در محدوده A5:A10 را با فرمول SUM(A5:A10) جمع زده‌ایم. چنانچه ردیفی خالی جایی بین ردیف 5 و ردیف 10 قرار دهیم، فرمول به طور خودکار به شکل SUM(A5:A11) به‌روز می‌شود. چگونه می‌توان فرمولی نوشت که هنگامی‌که ردیفی خالی بین ردیف‌های 5 و 10 قرار می‌دهیم باز مقادیر موجود در محدوده اولیهA5:A10 را جمع زند؟
• چگونه می‌توان از تابع INDIRECT در فرمولی برای تشخیص بخش نام محدوده یک فرمول موجود در کاربرگ استفاده کرد؟
• فایلی داریم که حاوی فروش محصولات شرکت در کشور یا مناطقی خاص است که فروش هر قاره در کاربرگ جداگانه‌ای فهرست شده است. چگونه می‌توان به‌آسانی این داده‌ها را به شکلی خلاصه شده در یک کاربرگ جمع‌آوری کرد؟
• آیا راه آسانی وجود دارد که بتوان تمامی کاربرگ‌ها را در یک فایل فهرست کرد؟
• فایلی حاوی کاربرگ‌های فراوان داریم. آیا می‌توان به‌آسانی جدول اقلامی دارای هایپر لینک‌هایی ایجاد کرد که دارای لینک‌هایی به هریک از کاربرگ‌های این فایل باشند؟

تابع INDIRECT احتمالاً یکی توابع مایکروسافت اکسل است که مهارت پیداکردن در آن بسیار مشکل می‌باشد. هرچند با دانستن نحوه استفاده از تابع INDIRECT می‌توانید از پس بسیاری مسائل به‌ظاهر غیرقابل‌حل برآیید. اساساً هرگونه ارجاع به سلول از طریق تابع INDIRECT منجر به ارزیابی فوری سلول مرجع می‌شود تا محتوای آن سلول در تابع گنجانده شود.

برای نمایش نحوه استفاده از تابع INDIRECT به فایلی به نام Indirectsimpleex.xlsx که در تصویر 1-23 نشان‌داده‌شده نگاه کنید.

تصویر 1-23 مثال ساده‌ای از تابع INDIRECT

در سلول C4 فرمول =INDIRECT(A4) را وارده کرده‌ایم. اکسل مقدار 6 را به ما می‌دهد چراکه ارجاع به سلول A4 بلافاصله با رشته متنی “B4” جایگزین شده است و فرمول به‌عنوان =B4 ارزشیابی می‌شود که مقدار 6 را به دست می‌آورد. مشابه همین وضعیت با واردکردن فرمول =INDIRECT(A5) در سلول C5 مقدار سلول B5 را که 9 است را به ما می‌دهد.

پاسخ به سؤالات این فصل:

فرمول‌های کاربرگ معمولاً به سلول‌ها، محدوده‌ها یا هر دوی این‌ها ارجاعاتی دارند. آیا به‌جای اینکه این ارجاعات را در فرمول‌ها تغییر داد می‌توان آنها را در سلول‌های خود قرار دهیم تا به‌آسانی بتوان ارجاع‌های سلول یا محدوده را بدون تغییر فرمول‌ها تغییر داد؟

داده‌هایی که در این مسئله از آنها استفاده می‌کنیم در فایلی به نام Sumindirect.xlsx قرارداده و در تصویر 2-23 نشان‌داده‌شده‌اند. محدوده سلول‌هایB4:H16 داده‌های فروش ماهیانه شش کالا را در طول دوره 12 ماهه‌ای نشان می‌دهد.

 تصویر 2-23 استفاده از تابع INDIRECT برای تغییر ارجاع‌های سلول در فرمول بدون تغییر خود فرمول‌ها

به‌تازگی جمع کل فروش هر یک از کالاها در طول ماه‌های 2 الی 12 را محاسبه کرده‌ایم. یک راه ساده برای انجام این محاسبه کپی‌کردن فرمول=SUM(C6:C16) از سلول C18 به محدوده D18:H18 می‌باشد. حالا فرض کنید که می‌خواهید ماه‌های جمع زده شده را تغییر دهید. مثلاً می‌خواهید جمع کل فروش ماه‌های 3 الی 12 را محاسبه کنید. می‌توانید فرمول را در سلول C18 به این شکل تغییر دهید=SUM(C7:C16) و بعد این فرمول را به محدوده D18: H18 کپی کنید، اما استفاده از این روش مشکل‌زا است چراکه شما مجبورید فرمول را از سلول C18‌به محدوده D18:H18‌کپی کنید و به این شکل بدون نگاه‌کردن به فرمول‌ها هیچ‌کس نخواهد فهمید که کدام ردیف‌ها اضافه شده‌اند.

تابع INDIRECT روش دیگری در اختیار قرار می‌دهد. در سلولD2 و E2 ردیف اول و انتهایی جمع خود را قرار داده‌ایم. حالا با استفاده از تابع INDIRECT، تمامی آنچه نیاز داریم انجام دهیم آن است که ارجاع ردیف‌های اول و انتهایی در سلول‌های D2و E2 را تغییر دهیم و حالا جمع‌های موردنظر با افزوده شدن ردیف‌های دلخواه به‌روز می‌شوند. همین‌طور با یک نگاه به مقادیر موجود در سلول‌های D2 و E2 خواهید دید که کدام ردیف‌ها (ماه‌ها) اضافه شده‌اند. تنها کاری که می‌بایست انجام داد کپی فرمول =SUM(INDIRECT(C$3&$D$2& “:”&C$3&$E$2)) از سلول C18 به محدوده D18:H18 می‌باشد.

اگر می‌خواهید ببینید اکسل چگونه ارجاع به تابع INDIRECT را ارزشیابی می‌کند می‌توانید از این حقه استفاده کنید: بر بخشی از فرمول (به‌عنوان‌مثال C$3) کلیک کنید و بعد کلید F9 را فشار دهید. اکسل حاصل مقدار بخش انتخاب شده از فرمول را به شما نشان می‌دهد. مثلاً بخش C$3 به C ارزشیابی می‌شود. (اطمینان حاصل کنید که پس از این کار کلیدEsc را فشار دهید تا از این حالت خارج شده به اکسل بازگردید). هر ارجاع درون تابع INDIRECT ارزشیابی شده تا برابر با محتوای سلول موردنظر قرار گیرد. بخش C$3 به‌عنوان C ارزیابی می‌شود، $D$2 به مقدار 6 ارزیابی می‌شود و $E$2 به مقدار 16 ارزیابی می‌شود.

با اضافه‌کردن کاراکتر امپرساند (&) به‌عنوان نماد الحاق، اکسل این فرمول را به‌عنوان SUM(C6:C16) ارزیابی می‌کند که دقیقاً همان است که در نظر داریم. فرمول موجود در سلول C18 مقدار 38+91+…69=607 را برمی‌گرداند. در سلول D18 فرمول به‌ صورت SUM(D6:D16) ارزیابی می‌شود که همان نتیجه‌ای است که در نظر داریم. البته چنانچه قصد داشته باشیم فروش ماه‌های 4 الی 6 را اضافه کنیم به‌سادگی در سلول D2 عدد 8 و در سلول E2‌عدد 7 را وارد می‌کنیم و آنگاه فرمول سلول C18 نتیجه 33+82+75=190 را به ما برمی‌گرداند. (برای اطلاعات بیشتر درباره استفاده از کاراکتر امپرساند جهت الحاق مقادیر فصل 6 بنام توابع متنی و ابزارFlash Fill را مشاهده کنید)

هریک از کاربرگ‌های یک فایل اکسل فروش ماهیانه محصولی را در سلول D1 فهرست کرده‌اند. آیا راه آسانی برای نوشتن و کپی فرمولی وجود دارد که بتواند هر فروش ماهیانه کالایی را در یک کاربرگ جداگانه فهرست کند؟

فایلی به نام Indirectmultisheet.xlsx (تصویر 3-23 را ببینید) حاوی هفت کاربرگ است. در هریک از کاربرگ‌ها، سلول D1 حاوی داده‌هایی درباره فروش محصولی در طول ماه معینی می‌باشد. بیایید فرض کنیم که کاربرگ 1 حاوی فروش ماه 1، کاربرگ 2 حاوی فروش ماه 2 و باقی کاربرگ‌ها به همین روال ادامه پیدا می‌کنند. مثلاً فروش ماه اول برابر 1، در ماه دوم برابر 4 و به همین صورت ادامه پیدا کرده است.

تصویر 3-23 فروش ماهیانه (ماه 1 الی 7) یک کالا که با استفاده از تابع INDIRECT فهرست شده است.

حالا فرض کنید که می‌خواهید فهرستی از فروش هر ماه را در یک کاربرگ گردآوری کنید. یک روش کسل‌کننده برای انجام این کار آن است که فروش ماه 1 را با فرمول=Sheet1!D1، ماه 2 را با فرمول =Sheet2!D1 و به همین روال ادامه دهید تا فروش ماه هفتم را با فرمول =Sheet7!D1 به دست آورید. اما این روش بسیار وقت‌گیر است. یک روش بسیار ظریف‌تر آن است که با فرمول =INDIRECT($C$10&D10&”!D1″) فروش‌های ماه 1 را در سلول E10 از کاربرگ شماره 1 فهرست کنید. اکسل $C$10 را به‌عنوان “Sheet” (کاربرگ) ارزیابی می‌کند که در نتیجه مقدار فروش‌های ماه 1 را در سلول D1 از کاربرگ 1 را به ما می‌دهد. با کپی‌کردن این فرمول در محدوده E11:E16 تمامی ارقام وارد شده در سلولD1 از کاربرگ‌های 2 الی 7 را در یک کاربرگ فهرست می‌نماید. وقتی‌که فرمول سلولE10 در سلول E11 کپی می‌شود، ارجاع سلول D10 به سلول D11 تغییر پیدا می‌کند و سلول E11 مقدار قرار گرفته درSheet2!D1 را به ما می‌دهد. مجدداً یادآوری می‌کنیم که چنانچه نشانگر ماوس را فرضاً در سلول E12 قرار دهید و گزینه Evaluate Formula را انتخاب کنید خواهید دید که اکسلSheet3!D1 را نشان میدهد .

فرض کنید مقادیر موجود در محدوده A5:A12 را با فرمول SUM(A5:A10) جمع زده‌ایم. چنانچه ردیفی خالی جایی بین ردیف 5 و ردیف 10 قرار دهیم، فرمول به طور خودکار به شکل SUM(A5:A11) به‌روز می‌شود. چگونه می‌توان فرمولی نوشت که هنگامی‌که ردیفی خالی بین ردیف‌های 5 و 10 قرار می‌دهیم باز مقادیر موجود در محدوده اولیهA5:A10 را جمع زند؟

کاربرگی به نامSUM(A5A10) در فایلIndirectinsertrow.xlsx(در تصویر 4-23 نمایش‌داده‌شده است) چندین راه برای جمع زدن اعداد در محدوده سلول‌های A5:A10 را نشان می‌دهد (توجه کنید که کاربرگ Row Inserted که به آن شباهت دارد درواقع بسیار با آن متفاوت است) در سلول A12 فرمول سنتیSUM(A5:A10) را وارد می‌کنیم که مقدار6+7+8+9+1+2=33 را به ما می‌دهد.

تصویر 4-23 چندین روش برای جمع زدن مقادیر موجود در محدوده سلول A5:A10‌را نشان می‌دهد.

فرمول SUM($A$5:$A$10) در سلول E9 همان مقدار 33 را به ما می‌دهد. همان‌طور که به‌زودی خواهید دید، اگر ردیفی بین ردیف‌های 5 و 10 قرار دهید هر دو فرمول‌ها تلاش می‌کنند تا سلول‌های محدوده A5:A11 را جمع بزنند.

با استفاده از تابع INDIRECT حداقل با دو روش می‌توانید مقادیر محدودهA5:A10 را جمع بزنید. در سلول F9 فرمول SUM(INDIRECT(“A5:A10”)) را وارد کرده‌ایم. ازآنجایی‌که اکسل با بخش INDIRECT(“A5:A10”) به‌عنوان رشته متنی “A5:A10” برخورد می‌کند، اگر ردیفی را در کاربرگ قرار دهید این فرمول هنوز تمامی محدوده سلول‌های A5: A10 را جمع خواهد زد.

راهی دیگر برای استفاده از تابع INDIRECT برای جمع زدن کل محدوده A5:A10 فرمول =SUM(INDIRECT(“A”&C4&”:A”&D4)) می‌باشد که در سلول C6 وارد شده است. اکسل ارجاع به سلول C4 را به‌عنوان مقدار 5 و ارجاع به سلول D4 را به‌عنوان رقم 10 در نظر می‌گیرد، بنابراین این فرمول بهSUM(A5:A10) تبدیل می‌شود. قراردادن یک ردیف خالی بین ردیف 5 و ردیف 10 تأثیری روی این فرمول ندارد چراکه ارجاع به سلولC4‌هنوز به‌عنوان رقم 5 و ارجاع به سلول D4‌هنوز به‌عنوان رقم 10 در نظر گرفته می‌شود. در تصویر 5-23 می‌توان جمع محاسبه شده توسط هر چهار فرمول را پس از اضافه‌کردن ردیفی خالی در زیر ردیف شماره 7 مشاهده کرد. می‌توانید این داده‌ها را در کاربرگRow Inserted در فایلی بنام Indirectinsertrow.xlsx مشاهده کنید.

تصویر 5-23 نتایج فرمول SUM پس از اضافه‌کردن یک ردیف به محدوده اولیه

همان‌طور که می‌بینید، فرمول کلاسیکSUM که از تابع INDIRECT استفاده نمی‌کند، تغییر داده شده تا تمامی محدودهA5:A11 را جمع بزند، بنابراین تمامی این فرمول‌ها همچنان مقدار 33 را به ما می‌دهند. دو فرمول SUM که از تابع INDIRECT‌استفاده می‌کنند همچنان مقادیر ورودی در محدوده‌های A5:A10 را جمع می‌زنند، بنابراین مقدار 2(که حالا در سلولA11) قرار گرفته است دیگر مشمول محاسبات جمع نمی‌شود. فرمول‌های SUM‌که از تابع INDIRECT‌استفاده می‌کنند مقدار عددی 31 را به ما می‌دهند.

چگونه می‌توان از تابع INDIRECT در فرمولی برای تشخیص بخش نام محدوده یک فرمول موجود در کاربرگ استفاده کرد؟

فرض کنید که چندین محدوده در کاربرگی را برای مطابقت با فروش فصلی کالاها نام‌گذاری کرده‌اید (تصویر 6-23 و فایل Indirectrange.xlsx را مشاهده کنید) مثلاً محدوده D4:E6 (به نام Quarter1) حاوی فروش ساختگی محصولات مختلف شرکت مایکروسافت در اولین فصل سال می‌باشد.

تصویر 6-23 استفاده از تابع INDIRECT برای ایجاد ارجاع به نام محدوده درون فرمول

عالی خواهد بود اگر بتوانیم فرمولی بنویسیم که به‌آسانی آن را کپی کرد تا بتوانیم فروش هر محصول در هر فصل را همان‌طور که در محدوده H17:J20 نشان‌ داده‌شده، در محدوده چهارضلعی از کاربرگ به دست آوریم. احتمالاً فکر می‌کنید که می‌توان در سلول H17 فرمول=VLOOKUP(H$16,$G17,2,FALSE) را وارد کرد و بعد این فرمول را در محدوده H17:J20 کپی نمود. متأسفانه اکسل $G17 را به‌عنوان ارجاع به نام محدوده Quarter1 تشخیص نمی‌دهد. اکسل در عوض $G17 را به‌عنوان رشته متنی “Quarter1”‌در نظر می‌گیرد؛ بنابراین فرمول به ما پیغام خطای#NA می‌دهد. برای حل این مشکل به‌سادگی در سلول H17 فرمول =VLOOKUP (H$16,INDIRECT($G17),2,FALSE) را وارد کرده و بعد این فرمول را به محدودهH17:J20‌کپی می‌کنیم. این فرمول به‌خوبی جواب می‌دهد! INDIRECT($G17) به‌عنوان Quarter1 ارزیابی شده و حالا به‌عنوان نام محدوده شناخته می‌شود. حالا به‌آسانی می‌توانید فروش همه کالاها در طول هر چهارفصل را به دست آورید.

فایلی داریم که حاوی فروش محصولات شرکت در کشور یا مناطقی خاص است که فروش هر قاره در کاربرگ جداگانه‌ای فهرست شده است. چگونه می‌توان به‌آسانی این داده‌ها را به شکلی خلاصه شده در یک کاربرگ جمع‌آوری کرد؟

سلول‌های E7:E9 از فایلی به نام Indirectconsolidate.xlsx حاوی فروش اتومبیل‌ها، کامیون‌ها و هواپیماها در هر قاره است. هر قاره دارای کاربرگ مخصوص خود است. چگونه می‌توان این داده‌ها را در یک کاربرگ خلاصه کرد؟

خلاصه این داده‌ها را همان‌طور که در تصویر 7-23 نشان‌داده‌شده در کاربرگ Summary ایجاد کرده‌ایم.

تصویر 7-23 استفاده از تابع INDIRECT برای ایجاد خلاصه داده‌هایی از فروش محصولات

 در اینجا فرمول =INDIRECT(E$6&”!”&$C7) را از سلول E7 به محدوده E7:I9 کپی کرده‌ایم. این کار، فرمول =LA!E7 را ایجاد می‌نماید که فروش اتومبیل‌ها در قاره آمریکای لاتین را محاسبه می‌کند(که در کاربرگ LA است) . کپی‌کردن این فرمول فروش هریک از انواع محصولات در هر قاره را جمع می‌زند.

همان جدول را به شکلی متفاوت در کاربرگی بنام Another Summary(تصویر 8-23 را ببینید) ایجاد کرده‌ایم. با کپی‌کردن فرمول =ADDRESS(ROW(),COLUMN()+2) از سلول C7 به محدوده C8:C9، فرایند استخراج آدرس سلول‌ها را که از آنها فروش اتومبیل، کامیون و هواپیماها را به دست آوردیم را به شکل خودکار انجام داده‌ایم. این فرمول باعث ایجاد ستون E‌ در آدرس سلول و سپس ردیف‌های 8 و 9 خواهد شد. تابع ROW() وارد شده در سلول مقدار شماره ردیف سلول را به ما می‌دهد و تابع COLUMN() وارد شده توسط خود در یک سلول مقدار شماره ستون سلول حاضر (در اینجا عدد 3) را به ما می‌دهد. عبارت COLUMN()+2 وقتی به‌عنوان سومین آرگومان تابع ADDRESS وارد می‌شود به شکلی مؤثر نام ستون سلول واقع در دو ستون سمت راست سلول جاری (در اینجا ستون E) را به ما می‌دهد. حالا از سلولE7 فرمول =INDIRECT(E$6&”!”&$C7) را به محدوده E7:I9 کپی می‌کنیم.

تصویر 8-23 استفاده مؤثر از آدرس سلول کپی شده برای خلاصه کردن یک کاربرگ

آیا راه آسانی وجود دارد که بتوان تمامی کاربرگ‌ها را در یک فایل فهرست کرد؟

فهرست کردن نام‌های کاربرگ‌ها در مثال آخر کار آسانی بود. اما فرض کنید که فایلی با 100 کاربرگ یا بیشتر داریم. اگر بخواهیم از قدرت کامل تابع INDIRECT برای بیرون کشیدن داده از مجموعه‌ای از کاربرگ‌ها استفاده کنیم به روشی کارآمد جهت فهرست کردن نام هریک از کاربرگ‌ها در فایل نیاز داریم. برای آنکه تمامی نام کاربرگ‌ها را به شکلی مؤثر در فایلی فهرست کنید به روشی که به شکل فهرست‌وار در زیر ارائه شده عمل نمایید. (فایلی به نام Worksheetnames.xlsx و تصاویر 9-23 و 10-23 را مشاهده کنید.)

تصویر 9-23 ایجاد یک محدوده نام که می‌توان برای استخراج نام کاربرگ‌ها بکار برد

تصویر 10-23 استخراج نام‌های کاربرگ در ردیف 16 و استفاده از تابع SHEET() برای استخراج عدد کاربرگ‌ها

از سربرگ Formulas گزینه Define Names را انتخاب کنید. حالا در کادر محاوره‌ای New Name یک محدوده نام ایجاد می‌کنیم که آن را Worksheet می‌نامیم. همان‌طور که در تصویر 9-23 می‌بینید فرمول وارد شده در بخش Refers To فرمول =GET.WORKBOOK(1) ‌است. GET.WORKBOOK(1) درواقع یک ماکرو است (ماکروها در فصل 92 به نام ماکروهای ضبط کننده موردبحث قرار گرفته‌اند) بنابراین پسوند نام این فایل می‌بایست.xlsm باشد.

• از آرایه افقی استفاده کنید که حاوی هر تعداد ردیفی از کاربرگ‌های فایل موردنظرتان می‌باشد، اگرچه در مثال حاضر به‌آسانی می‌توان دید که تنها سه کاربرگ وجود دارد. اما چنانچه تعداد کاربرگ‌های فایل را نمی‌دانید، می‌توانید از تابع SHEETS() استفاده کنید (که از اکسل 2013 اضافه شده). تابع SHEETS() را در سلول H13 وارد می‌کنیم. این تابع تعداد کاربرگ‌های موجود در فایل را (در این مورد 3 عدد) به ما می‌دهد. راستی، فرمولSHEET() تعداد کاربرگ‌های فعال در فایل را وارد می‌کند. مثلاً اگر فرمول=SHEET() را هرجایی از کاربرگ Bob وارد کرده باشید فرمول عدد 1 را وارد می‌کند اگر این فرمول را هرجایی در کاربرگ Allen ‌وارد کرده باشید، فرمول عدد 2 را وارد می‌کند و اگر آن را هرجایی در کاربرگ Jill وارد کرده باشید، این فرمول عدد 3 را وارد می‌کند.
• در گوشه سمت چپ محدوده انتخاب شده (در این مورد H15) فرمول =Worksheet را تایپ کرده و کلیدهای ترکیبی Ctl+Shift+Enter را فشار دهید. محدوده نام Worksheet به‌عنوان فرمول آرایه‌ای عمل می‌کند (فصل 91 به‌عنوان فرمول‌ها و توابع آرایه‌ای را برای بحث‌های بیشتر در مورد فرمول‌های آرایه‌ای مشاهده کنید)؛ بنابراین می‌بایست کلیدهایCtrl+Shift+Enter. را فشار دهید تا فرمول عمل کند. حالا می‌بینید که در محدوده سلول‌های H15:J15 نام فایل در میان آکلاد قرار گرفته است و به دنبال آن نام‌های کاربرگ‌ها به ترتیب قرارگرفتنشان فهرست شده‌اند.

برای انتقال هریک از نام کاربرگ‌ها به سلولی جداگانه‌، به‌سادگی از توابع RIGHT و FIND که در فصل 6 توضیح داده شدند استفاده می‌کنیم تا نام هر کاربرگ را با استخراج هریک از کاراکترهای سمت راست آکولادها جدا نماییم. برای انجام این کار به‌سادگی فرمول =RIGHT(I15,LEN(I15)-FIND(“]”,I15,1)) را از سلول H16 به محدوده I16:J16 کپی می‌کنیم .حالا می‌بینید که نام‌های کاربرگ‌ها در محدوده H16:J16 درج شده‌اند.

فایلی حاوی کاربرگ‌های فراوان داریم. آیا می‌توان به‌آسانی جدول مندرجاتی دارای هایپر لینک‌هایی ایجاد کرد که دارای لینک‌هایی به هریک از کاربرگ‌های این فایل باشند؟

بسیار مشکل است که فایلی حاوی صفحه گسترده‌های فراوان را پیمایش کنیم. اما اگر نام کاربرگ‌ها را بدانیم می‌توان به‌سادگی جدول مندرجات بسیار شکیلی ایجاد کنیم که حاوی هایپر لینک‌هایی به هریک از کاربرگ‌ها باشد.

پیش از آنکه به شما نشان دهیم چگونه جدول مندرجاتی ایجاد کنید، می‌بایست توضیح دهیم چگونه تابع INDIRECT را با نام کاربرگ‌هایی که دارای یک فاصله (SPACE) هستند بکار برید. فایلی به نام Indirect with spaces.xlsx حاوی دو کاربرگ اس که نام‌های هر دو حاوی یک فاصله است: Data Table و Goal Seek. می‌خواهیم بتوانیم نام کاربرگی را در سلول D9(تصویر 11-23 را ببینید) و احتمالاً یک آدرس سلول را در سلول E9‌وارد کنیم؛ همچنین می‌خواهیم فرمول موجود در سلول F10 مقدار ورودی در ترکیب سلول‌های کاربرگ منتخب را به ما برگرداند. در سلولF11 فرمول = INDIRECT(D9&”!”&E9) را امتحان کرده‌ایم. این فرمول با شکست روبرو می‌شود چراکه در میان نام کاربرگ فاصله‌ای وجود دارد. حالا فرمول تصحیح شده را وارد می‌کنیم: =INDIRECT(“‘”&D9&”‘”&”!”&E9)قراردادن آپستروف در میان دو علامت نقل‌قول کاری می‌کند که فرمول جواب دهد و در این مورد فرمول ورودی (5) از سلولC1 کاربرگی به نام Goal Seek‌را برمی‌گرداند. فرمول =INDIRECT(“‘”&D9&”‘”&”!b4″) در سلول F9 همواره مقدار ورودی در سلول B4 کاربرگ انتخاب شده در سلولD9 را به ما می‌دهد.

تصویر 11-23 استفاده از تابع INDIRECT وقتی‌که نام کاربرگ‌ها حاوی فاصله است

بعد از بحث درباره تابع CELL می‌توانیم به شما نشان دهیم چگونه توابع HYPERLINK,CELL و INDIRECT را باهم ترکیب کنید تا به شکلی کارآمد جدول مندرجاتی برای کاربرگی تهیه کنید.

تابع Cell (فایلی به نام Cellfunction.xlsx و تصویر 12-23 را مشاهده کنید) اطلاعاتی مهم درباره یک سلول یا محدوده‌ای از سلول‌ها را به ما می‌دهد. مهم‌ترین ویژگی‌های مرتبط با تابع Cell در تصویر 12-23 نشان‌داده‌شده‌اند. برای اطلاعات بیشتر به راهنمای اکسل درباره تابع Cell که می‌توان از طریق Function Wizard به آن دسترسی پیدا کرد مراجعه کنید.

تصویر 12-23 تابع Cell در حین کار

فایلی به نام Hyperlinktoc.xlsx نشان می‌دهد که چگونه به شکلی کارآمد جدول مندرجات برای یک کاربرگ تهیه کنیم (تصویر 23-13را ببینید) برای شروع از روش GET.WORKBOOK(1) استفاده می‌کنیم تا نام‌های کاربرگ‌ها را استخراج کنیم (در این مورد ما نام‌های Pivot Table، Goal Seek وSheet1 را به دست آورده‌ایم) پس از آن به‌سادگی فرمول

= HYPERLINK(“#”&CELL(“address”,INDIRECT(“‘”&E13&”‘!$A$1”)),E13).

را از سلول F13 به محدوده F14:F15 کپی می‌کنیم.

علامت # فرمول را قادر می‌سازد تا کاربرگ فعال را تشخیص دهد. تابع CELL با گزینه آدرس که بر بخش INDIRECT فرمول اعمال شده سلول A1‌را از کاربرگ Pivot Table برمی‌گرداند. بخش E13 انتهایی ما را قادر می‌سازد تا از نام هریک از کاربرگ‌ها به‌عنوان متونی خودمانی برای هریک از هایپر لینک‌ها استفاده کنیم.

جدول 13-23 ایجاد جدول مندرجات

مسئله‌های این فصل:

تابع ADDRESS آدرس حقیقی سلول‌های مرتبط با یک ردیف یا ستون را به ما می‌دهد مثلاً فرمول ADDRESS(3,4) مقدار$D$3 را به ما می‌دهد. چنانچه فرمول =INDIRECT(ADDRESS(3,4)) را وارد کرده باشید چه نتیجه‌ای به ما داده می‌شود؟

کاربرگی به نام P23_2.xlsx حاوی داده‌های فروش پنج کالا در چهار منطقه می‌باشند (شرق، غرب، شمال و جنوب) از تابع INDIRECT برای نوشتن فرمولی استفاده کنید که با آن بتوان به‌آسانی کل فروش هریک از کالاهای دارای اعداد ترکیبی پشت‌سرهم مثل کالاهای 1 الی 3 و یا کالاهای 2 الی 5 و غیره را حساب کرد.

فایلی به نام P23_3.xlsx حاوی شش کاربرگ است. کاربرگ 1 حاوی فروش ماهیانه کالاهای 1 الی 4 می‌باشد. این فروش‌ها همیشه در محدوده سلول‌های E5:H5 فهرست شده‌اند. از تابع INDIRECT استفاده کنید تا بتوانید به شکلی کارآمد فروش هر محصول را بر اساس ماه در کاربرگ جداگانه‌ای ترتیب دهید.

فرمولی بنویسید که بتواند تمام ورودی‌های محدوده سلول‌های G2:K2 راحتی اگر یک ستون یا بیشتر از آن میان ستون‌های G و K اضافه کنید، جمع بزند.

فایلی به نام Marketbasketdata.xlsx حاوی فروش اقلام مختلفی است. برای هر ردیف، یک عدد 1 در ستون B الی ستون K نمایشگر جنس خریداری شده است، درحالی‌که رقم 0 نمایانگر اقلام فروخته نشده می‌باشد. در ستون Day Week رقم 1 نشان می‌دهد که معامله در روز دوشنبه انجام شده، رقم 2 نشان می‌دهد که معامله در روز سه‌شنبه انجام شده و به همین روال ادامه پیدا می‌کند. برای هر یک از اقلام فهرست شده در محدوده K9 :K14 درصد معاملاتی که در آنها جنس فروخته شده را حساب کنید. همچنین کسری از مبادله که در هر روز صورت‌گرفته است را نیز محاسبه نمایید.

فایلی به نام Verizonin directdata.xlsx حاوی ساعت‌های کار هریک از کارمندان و رتبه آنها در ماه‌های ژانویه تا مه می‌باشد. یک کاربرگ ترکیبی ایجاد کنید تا به کمک آن بتوانید با انتخاب هر شخص، ساعت‌های کاری آن شخص را در طی هر ماه به دست آورده و جمع ساعت‌های کاری وی در آن ماه را محاسبه نماید.