فهرست
در نگاه اول (به خصوص هنگام خواندن راهنما)، عملکرد غیر مستقیم (غیر مستقیم) ساده و حتی غیر ضروری به نظر می رسد. ماهیت آن تبدیل متنی است که شبیه یک پیوند به یک پیوند کامل است. آن ها اگر لازم باشد به سلول A1 مراجعه کنیم، می توانیم به طور معمول پیوند مستقیم ایجاد کنیم (یک علامت مساوی را در D1 وارد کنید، روی A1 کلیک کنید و Enter را فشار دهید)، یا می توانیم استفاده کنیم. غیر مستقیم به همین منظور:
لطفاً توجه داشته باشید که آرگومان تابع - ارجاع به A1 - در گیومه وارد شده است، به طوری که در واقع متن در اینجا است.
شما می گویید "خب، باشه." "و چه فایده ای دارد؟"
اما با برداشت اول قضاوت نکنید - فریبنده است. این ویژگی می تواند در بسیاری از شرایط به شما کمک کند.
مثال 1. جابجایی
کلاسیک این ژانر: باید دیافراگم عمودی را بچرخانید
شیار به افقی (transpose). البته می توانید از درج یا تابع مخصوص استفاده کنید TRANSP (انتقال) در یک فرمول آرایه، اما شما می توانید با ما به نتیجه برسید غیر مستقیم:
منطق ساده است: برای به دست آوردن آدرس سلول بعدی، حرف "A" را با کاراکتر ویژه "&" و شماره ستون سلول فعلی، که تابع به ما می دهد، می چسبانیم. ستون (COLUMN).
روش معکوس بهتر است کمی متفاوت انجام شود. از آنجایی که این بار باید به سلول های B2، C2، D2 و غیره پیوند ایجاد کنیم، استفاده از حالت پیوند R1C1 به جای "نبرد دریایی" کلاسیک راحت تر است. در این حالت، سلول های ما فقط در شماره ستون متفاوت خواهند بود: B2=R1C2، C2=R1C3D2=R1C4 و غیره.
اینجاست که دومین آرگومان تابع اختیاری وارد می شود. غیر مستقیم. اگر برابر باشد دروغ گویی (نادرست)، سپس می توانید آدرس پیوند را در حالت R1C1 تنظیم کنید. بنابراین ما به راحتی می توانیم محدوده افقی را به عمودی تبدیل کنیم:
مثال 2. جمع بر اساس فاصله
ما قبلاً یک روش جمع کردن یک پنجره (محدوده) با اندازه معین روی یک صفحه را با استفاده از تابع تجزیه و تحلیل کرده ایم. دسترس (انحراف). مشکل مشابهی نیز با استفاده از آن قابل حل است غیر مستقیم. اگر لازم باشد داده‌ها را فقط از یک بازه-دوره مشخص خلاصه کنیم، می‌توانیم آن را از قطعات بچسبانیم و سپس آن را به یک پیوند کامل تبدیل کنیم که می‌توانیم آن را در داخل تابع درج کنیم. مبلغ (جمع):
مثال 3. لیست کشویی جدول هوشمند
گاهی اوقات مایکروسافت اکسل با نام‌ها و ستون‌های جدول هوشمند به عنوان پیوند کامل رفتار نمی‌کند. بنابراین، به عنوان مثال، هنگام تلاش برای ایجاد یک لیست کشویی (tab داده ها - اعتبارسنجی داده ها) بر اساس ستون کارکنان از میز هوشمند مردم یک خطا دریافت خواهیم کرد:
اگر پیوند را با تابع خود "پیچ" کنیم غیر مستقیم، سپس اکسل به راحتی آن را می پذیرد و هنگام اضافه کردن کارمندان جدید به انتهای جدول هوشمند، لیست کشویی ما به صورت پویا به روز می شود:
مثال 4. پیوندهای ناگسستنی
همانطور که می دانید، اکسل به طور خودکار آدرس های مرجع در فرمول ها را هنگام درج یا حذف سطر-ستون ها در یک برگه تصحیح می کند. در بیشتر موارد، این درست و راحت است، اما نه همیشه. فرض کنید باید اسامی را از فهرست کارمندان به گزارش منتقل کنیم:
اگر لینک های معمولی را قرار دهید (=B2 را در اولین سلول سبز وارد کنید و آن را کپی کنید)، وقتی مثلا داشا را حذف کردید، #LINK را دریافت می کنیم! خطا در سلول سبز مربوط به او. (#مرجع!). در مورد استفاده از تابع برای ایجاد پیوند غیر مستقیم چنین مشکلی وجود نخواهد داشت
مثال 5: جمع آوری داده ها از چندین برگه
فرض کنید 5 برگه با گزارش های یکسان از کارمندان مختلف (میخائیل، النا، ایوان، سرگئی، دیمیتری) داریم:
فرض کنید شکل، اندازه، موقعیت و ترتیب کالاها و ماه ها در همه جداول یکسان است - فقط اعداد متفاوت هستند.
می‌توانید داده‌ها را از همه برگه‌ها جمع‌آوری کنید (آنها را خلاصه نکنید، بلکه آنها را در زیر یکدیگر در یک «شمع» قرار دهید) تنها با یک فرمول:
همانطور که می بینید، ایده یکسان است: ما پیوند را به سلول مورد نظر صفحه داده شده می چسبانیم و غیر مستقیم آن را به "زنده" تبدیل می کند. برای راحتی، در بالای جدول، حروف ستون ها (B، C، D) و در سمت راست - شماره خطوطی که باید از هر برگه گرفته شود را اضافه کردم.
مشکلات
اگر شما با استفاده از غیر مستقیم (غیر مستقیم) باید نقاط ضعف آن را به خاطر بسپارید:
- اگر به فایل دیگری پیوند دهید (با چسباندن نام فایل در پرانتز، نام برگه و آدرس سلول)، آنگاه فقط زمانی کار می‌کند که فایل اصلی باز است. اگر آن را ببندیم، با خطای #LINK مواجه می شویم!
- INDIRECT نمی تواند به محدوده ای با نام پویا اشاره کند. روی استاتیک - مشکلی نیست.
- INDIRECT یک تابع فرار یا "فرار" است، یعنی برای هر تغییری در هر سلول از صفحه، و نه فقط بر سلول‌ها، مانند توابع عادی، دوباره محاسبه می‌شود. این تاثیر بدی روی عملکرد دارد و بهتر است با میزهای بزرگ INDIRECT غافل نشوید.
- نحوه ایجاد یک محدوده پویا با اندازه خودکار
- جمع کردن در یک بازه-پنجره روی یک صفحه با تابع OFFSET