Съдържание
На пръв поглед (особено когато четете помощта), функцията НЕПРЯК (НЕПРЯК) изглежда просто и дори ненужно. Същността му е да превърне текст, който прилича на връзка, в пълноценна връзка. Тези. ако трябва да препратим към клетка A1, тогава можем или обичайно да направим директна връзка (въведете знак за равенство в D1, щракнете върху A1 и натиснете Enter), или можем да използваме НЕПРЯК за същата цел:
Моля, обърнете внимание, че аргументът на функцията – препратка към A1 – се въвежда в кавички, така че всъщност това е текст тук.
„Е, добре“, казвате вие. „И каква е ползата?“
Но не съдете по първото впечатление – то е измамно. Тази функция може да ви помогне в много ситуации.
Пример 1. Транспониране
Класика на жанра: трябва да завъртите вертикалния диам
бразда към хоризонтала (транспониране). Разбира се, можете да използвате специална вложка или функция ТРАНСПОРТ (ТРАНСПОНИРАНЕ) във формула за масив, но можете да се справите с нашата НЕПРЯК:
Логиката е проста: за да получим адреса на следващата клетка, залепваме буквата „А“ със специалния знак „&“ и номера на колоната на текущата клетка, който ни дава функцията КОЛОНА (КОЛОНА).
Обратната процедура е по-добре да се направи малко по-различно. Тъй като този път трябва да формираме връзка към клетки B2, C2, D2 и т.н., е по-удобно да използваме режима на връзка R1C1 вместо класическата „морска битка“. В този режим нашите клетки ще се различават само по номера на колоната: B2=R1C2, C2=R1C3, D2=R1C4 и т.н.
Тук идва вторият незадължителен аргумент на функцията. НЕПРЯК. Ако е равно ЛЪЖА (НЕВЯРНО), тогава можете да зададете адреса на връзката в режим R1C1. Така че можем лесно да преместим хоризонталния диапазон обратно във вертикален:
Пример 2. Сумиране по интервал
Вече анализирахме един начин за сумиране върху прозорец (диапазон) с даден размер на лист, използвайки функцията ИЗХВЪРЛЯНЕ (ИЗМЕСТВАНЕ). Подобен проблем може да бъде решен и с помощта на НЕПРЯК. Ако трябва да обобщим данни само от определен диапазон-период, тогава можем да ги слепим от парчета и след това да ги превърнем в пълноценна връзка, която да вмъкнем във функцията SUM (СУМА):
Пример 3. Падащ списък с интелигентна таблица
Понякога Microsoft Excel не третира интелигентните имена на таблици и колони като пълни връзки. Така например, когато се опитвате да създадете падащ списък (таб Данни – Валидиране на данни) въз основа на колона Служители от интелигентна маса Хора ще получим грешка:
Ако „увием“ връзката с нашата функция НЕПРЯК, тогава Excel лесно ще го приеме и нашият падащ списък ще се актуализира динамично при добавяне на нови служители в края на интелигентната таблица:
Пример 4. Неразрушими връзки
Както знаете, Excel автоматично коригира референтните адреси във формулите при вмъкване или изтриване на редове-колони в лист. В повечето случаи това е правилно и удобно, но не винаги. Да кажем, че трябва да прехвърлим имената от указателя на служителите в отчета:
Ако поставите обикновени връзки (въведете =B2 в първата зелена клетка и я копирайте надолу), тогава, когато изтриете, например Даша, ще получим #LINK! грешка в зелената клетка, съответстваща на нея. (#REF!). В случай на използване на функцията за създаване на връзки НЕПРЯК няма да има такъв проблем.
Пример 5: Събиране на данни от множество листове
Да предположим, че имаме 5 листа с отчети от един и същи тип от различни служители (Михаил, Елена, Иван, Сергей, Дмитрий):
Да приемем, че формата, размерът, позицията и последователността на стоките и месеците във всички таблици са еднакви – само числата се различават.
Можете да събирате данни от всички листове (не ги сумирайте, а ги поставяйте един под друг в „купчина“) само с една формула:
Както можете да видите, идеята е същата: залепваме връзката към желаната клетка на дадения лист и НЕПРЯК го превръща в „на живо“. За удобство над таблицата добавих буквите на колоните (B,C,D), а вдясно – номерата на редовете, които трябва да се вземат от всеки лист.
Клопките
Ако използвате НЕПРЯК (НЕПРЯК) трябва да запомните неговите слабости:
- Ако се свържете към друг файл (чрез залепване на името на файла в квадратни скоби, името на листа и адреса на клетката), тогава това работи само докато оригиналният файл е отворен. Ако го затворим, получаваме грешка #LINK!
- INDIRECT не може да препраща към динамичен наименуван диапазон. Статично – няма проблем.
- INDIRECT е променлива или „променлива“ функция, т.е. тя се преизчислява за всяка промяна в която и да е клетка на листа, а не само върху клетките, както при нормалните функции. Това има лош ефект върху производителността и е по-добре да не се увличате с големи INDIRECT таблици.
- Как да създадете динамичен диапазон с автоматично оразмеряване
- Сумиране върху диапазонен прозорец на лист с функцията OFFSET