НЕПРЯКА функция в Excel
Непряката функция в Excel е вградена функция, която се използва за препращане и получаване на стойности на клетки от текстов низ, тази формула взема препратка от посочената клетка, тя има два аргумента, първият аргумент не е задължителен, докато вторият аргумент е незадължителен, който се отнася до вида на съвпадението, тази функция може да се използва и с друга формула.
Функцията INDIRECT в Excel има множество употреби. Той връща препратката към клетка, посочена от текстов низ. Например, ако клетка B1 съдържа низ, нека кажем, че диапазоните c4 и c4 имат стойността „Джесика“. Сега, ако използваме функцията INDIRECT и предадем референтния текст като B1, тя ще върне стойността, съдържаща се в диапазона от клетки c4, която е „Джесика“.
Функцията INDIRECT на Excel ви позволява да посочите адрес на клетка INDIRECTly. Както можете в горния пример INDIRECT Excel, ако клетка B1 съдържа текста C4, тази непряка Excel формула връща съдържанието на клетката C4 ( Jessica ). Тази функция е изключително полезна функция. Можете да използвате функцията INDIRECT, когато искате да промените препратката към клетка в непряка формула на Excel, без да променяте самата формула.
НЕПРЯКА формула на Excel
Обяснение
Ref_text е препратка към клетка, която съдържа препратка в стил А1, препратка в стил R1C1, име, определено като препратка, или препратка към клетка като текстов низ.
Аргументът a1 не е задължителен
| a1 | Невярно или 0, когато ref_text е в стил R1C1 |
| a1 | Пропуснато или Вярно (1), когато ref_text е в стил А1 |
Как да използвам непряката функция в Excel?
Нека вземем INDIRECT excel пример, преди да използваме адресната функция в работната книга на Excel:
Да предположим, че за уебсайт www.xyz.com имаме данните от Google за анализ на посетителите на уебсайта от различен канал за аудитория за пет различни държави, както е показано по-долу в таблицата:
Можем да използваме функцията INDIRECT в Excel с цел преобразуване на текстов низ в препратка, така че да се позоваваме на клетка H4 и да използваме това име като препратка. Използването на функцията INDIRECT в excel, в този случай, е да се позовават на диапазони на имена, като се използват стойности на клетка, така че в клетка I4 можем просто да използваме функцията sum, за да изчислим общия брой посетители от държава Канада.
Сега можем директно да изчислим сумата на посетителите от всяка държава, като използваме функцията за сумиране, но използвайки функцията INDIRECT в excel, можем да изчислим сумата на посетителите от различни страни, като променим препратката към името, вместо да променим самата формула за непряк Excel.
Създадохме диапазона от имена за всеки посетител на държава; това може да стане лесно, като напишете диапазона от имена в пространството точно под клипборда, както е показано по-долу.
Друг метод е, че можете да отидете на формули-> диспечер на имена -> изберете диапазона, който искате да назовете -> кликнете върху диспечера на имената -> въведете диапазона на имената.
По същия начин, той създаде имена в Excel за посетители и от други страни.
Сега, в I4, изчислявайки сумата на посетителя от държава Канада, използвайки функцията INDIRECT и използвайки диапазона от имена в H4 (Канада, име по-горе за F3: F7), получаваме общата сума на посетителя.
Ако създадем списък с данни, като използваме валидирането на данните за страните и приложим този списък към H4
И променяйки различните имена на държави от Канада на Индия, САЩ, Австралия или Сингапур, получаваме общата сума на посетителя в I4, използвайки функцията INDIRECT в Excel.
Така че, за всеки случай, ние не променяме непряката формула на Excel, която е в клетка I4; променяме препратката към името в H4 и изчисляваме сумата на посетителите за всяка държава.
Можем да се придвижим една стъпка по-нататък и да препращаме имена на ниво работен лист, също използвайки функцията INDIRECT в Excel. Например, сега ще използваме функцията INDIRECT във връзка с различни листове в работната книга.
Създадохме различни листове с имената на държавите за общия брой продажби, извършени с всеки канал за търсене за различни държави.
За Индия
За САЩ,
По същия начин и за други страни.
Отново ще изчислим общите продажби, направени чрез всеки канал, като използваме функцията INDIRECT в Excel за различни листове с имена на държави като референция.
В този случай, тъй като имаме данни за продажбите в различни листове, ще трябва да обединим различни елементи, които са обхватът на данните, така че ref_text ще започне с името на листа, което е Индия в този случай в B4, тогава ще използвайте оператора &, наречен амперсанд, който се използва за конкатенация.
И така, получаваме общата сума от продажбите за Индия; сега, ако променим името на държавата на листа в B4 от Индия в други страни като САЩ, Сингапур или Канада, ще получим общата стойност на продажбата на тези страни.
Отново, в този случай ние просто сменихме стойностите ref_text в клетка B4, без да сменим основната непряка формула на Excel, която е в C4, за да получим сумата от продажбите за различни страни, които са в различни листове, използвайки функцията INDIRECT.
Сега ще използваме функцията INDIRECT, използвайки референцията R1C1, за да получим продажната стойност за последния месец. Имаме продажби за различни месеци в колони B, C и D за три месеца Jan, Feb и Mar, които могат да нараснат до още месеци като април, май, юни и т.н. Така че таблицата за продажба винаги ще се увеличава в зависимост от добавянето на предстоящия месец за продажба. И така, в този пример за НЕПРАВЕН Excel, ние искаме да изчислим продажбата за последния месец.
И така, тук последният месец е март и ние искаме да изчислим общата продажба на месец март, която е $ 249 344, като използваме функцията INDIRECT.
В този пример INDIRECT Excel ще намерим използването на втория аргумент на функцията INDIRECT
INDIRECT (ref_text, a1)
a1: което е незадължителен аргумент, това е логическа стойност (булева стойност), която указва какъв тип препратка (ref_text) се съдържа в клетката
ако a1 е вярно (1) или е пропуснато, ref_text се счита за препратка в стил А1
ако a1 е false (0), ref_text се интерпретира като стил R1C1
По какво се различава стилът А1 от стила R1C1?
Обикновено функцията INDIRECT в Excel използва нотация A1. Всеки адрес на клетка се състои от колона и номер на ред. Въпреки това, функцията INDIRECT в Excel също поддържа нотация R1C1. В тази система клетка А1 се означава като клетка R1C1, клетка А2 като R2C1 и т.н.
За да преминете към нотация R1C1, изберете Файл-> опция, за да отворите диалоговия прозорец на опциите на Excel, щракнете върху раздела формула и поставете отметка към опция стил на референтен стил R1C1. Сега ще забележите, че всички букви в колоните се променят на цифри. И всички препратки към клетки във формулите ви също се коригират.
Така че, когато използваме стила R1C1 във функцията INDIRECT в Excel, предаваме стойността a1 като false и ако използваме A1-style, тогава предаваме стойността true на a1.
Сега, в този пример за НЕПРАВЕН Excel ще използваме стила R1C1 поради изискването, тъй като колоните с месец ще растат.
Използвайки стила R1C1, общата стойност на продажбата е в ред 10 и последната стойност на колоната, която е в колоната D, ще бъде изчислена с помощта на функцията COUNTA , която ще даде номера на последната колона, съдържаща стойност. Така че, преброяване (10:10) в този случай ще даде 4, което е номерът на колоната в стил R1C1. И така, ние се свързахме с тази стойност, за да получим R10C4, който съдържа желаната стойност, която е общата стойност на продажбата за последния месец ($ 249 344).
И тъй като тук използваме стила R1C1, ще предадем фалшива стойност за аргумента a1.
Сега нека добавим още една колона с месец април Продажба.
И така, вземете нови стойности на продажбите от миналия месец, съответстващи на тези от последния месец април. По същия начин, ако добавим още месеци, винаги ще получаваме нова продажна стойност от миналия месец, без да променяме формулата за непряк ексел за всеки месец. Това е специалността на функцията INDIRECT в excel.
Как да персонализирате масива на таблицата във функцията INDIRECT Excel VLOOKUP?
Имаме пет държави и канал за търсене и продажбата, която уебсайтът е осъществил чрез тези различни канали от различни държави. Сега, в клетка B3, искаме да получим продажбата, извършена чрез органични търсения от държава Съединени щати.
За всяка таблица за различни страни, ние сме на име всяка маса с кратко име за страната, Ind за Индия, членки на САЩ, може за Канада, и така нататък .
Ако използваме директно функцията INDRECT Excel vlookup, за да получим стойността, както е направено по-долу
Получаваме грешка, защото функцията INDIRECT vlookup на Excel не е в състояние да разпознае масива на таблицата, предаден като стойността на клетката B2, така че ще използваме функцията INDIRECT в excel, за да накара vlookup да получи масива table_ разпознат като валиден аргумент.
Ако променим стойностите на B1 и B2, ще получим различни стойности на Sale в B3, както се изисква.
Забележка: Диапазоните на имената не са чувствителни към малки и големи букви. Следователно - Ind, IND и InD са еднакви за Excel.
В следващия пример INDIRECT Excel ще използваме функцията INDIRECT в Excel, за да създадем зависим списък. Имаме списък на длъжността на служител във фирма; тогава за всяка позиция имаме имената на служителите.
Сега, в клетка I2, ние ще създадем списък за различни рангове на служителите като техните позиции, а в I3, ние ще покажем списък, който ще зависи от стойността в I2. Ако I2 е режисьор, тогава I3 трябва да отразява имената на режисьорите, които Андрю и Майкъл; ако I2 е мениджър, тогава I3 трябва да отразява имената на мениджъра, който е Камил, Дейвид, Дейвис и Уилям и по същия начин за ръководителя.
В този случай отново ще използваме функцията INDIRECT в Excel, за да създадем зависимия списък. Валидиране на данни, в настройките, които избират критерии за валидиране като списък и в полето източник, ще използваме функцията INDIRECT в Excel, както следва.
= НЕПРЯКО ($ I $ 2)
Когато избираме имена на директори в I3, се отразява като списък с имена Андрю и Майкъл, когато I2 е мениджър, тогава I3 отразява имената на мениджъра Камил, Дейвид, Дейвис и Уилям и по подобен начин за ръководителя, както е показано по-долу на фигурата.
Неща за запомняне
- Аргументът ref_text трябва да е валидна препратка към клетка, или INDIRECT ще върне #REF! Грешка. Аргументът ref_text може да се отнася към друга работна книга.
- Ако ref_text се позовава на друга работна книга (външна препратка), другата работна книга трябва да е отворена. Ако работната книга на източника не е отворена, INDIRECT връща #REF! Стойност на грешката.
- Ако ref_text се отнася до диапазон на клетки извън ограничението на редове от 1,048,576 или ограничението на колоните от 16,384 (XFD), INDIRECT връща #REF! Грешка.
