Съдържание
Честито! Стигнахте до последния ден на маратона 30 Excel функции за 30 дни. Беше дълго и интересно пътуване, по време на което научихте много полезни неща за функциите на Excel.
На 30-ия ден от маратона ще посветим изучаването на функцията НЕПРЯК (INDIRECT), който връща връзката, указана от текстовия низ. С тази функция можете да създавате зависими падащи списъци. Например, когато избирате държава от падащ списък, определяте кои опции ще се появят в падащия списък с градове.
И така, нека разгледаме по-подробно теоретичната част на функцията НЕПРЯК (НЕПРЯКО) и проучете практически примери за неговото приложение. Ако имате допълнителна информация или примери, моля, споделете ги в коментарите.
Функция 30: НЕПРЯКО
функция НЕПРЯК (INDIRECT) връща връзката, указана от текстовия низ.
Как можете да използвате функцията INDIRECT?
Тъй като функцията НЕПРЯК (INDIRECT) връща връзка, дадена от текстов низ, можете да го използвате за:
- Създайте непроменлива начална връзка.
- Създайте препратка към статичен наименуван диапазон.
- Създайте връзка, като използвате информация за лист, ред и колона.
- Създайте непроменлив масив от числа.
Синтаксис INDIRECT (INDIRECT)
функция НЕПРЯК (INDIRECT) има следния синтаксис:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- референтен_текст (link_to_cell) е текстът на връзката.
- a1 – ако е равно на TRUE (ВЯРНО) или не е посочено, тогава ще се използва стилът на връзката A1; и ако FALSE (FALSE), тогава стилът R1C1.
Капани НЕПРЯКО (НЕПРЯКО)
- функция НЕПРЯК (INDIRECT) се преизчислява всеки път, когато стойностите в работния лист на Excel се променят. Това може значително да забави вашата работна книга, ако функцията се използва в много формули.
- Ако функцията НЕПРЯК (INDIRECT) създава връзка към друга работна книга на Excel, тази работна книга трябва да е отворена или формулата ще отчете грешка #REF! (#ВРЪЗКА!).
- Ако функцията НЕПРЯК (INDIRECT) препраща към диапазон, който надвишава ограничението за ред и колона, формулата ще отчете грешка #REF! (#ВРЪЗКА!).
- функция НЕПРЯК (INDIRECT) не може да препраща към динамичен наименуван диапазон.
Пример 1: Създаване на начална връзка без изместване
В първия пример колоните C и E съдържат едни и същи числа, техните суми са изчислени с помощта на функцията SUM (SUM) също са еднакви. Формулите обаче са малко по-различни. В клетка C8 формулата е:
=SUM(C2:C7)
=СУММ(C2:C7)
В клетка E8, функцията НЕПРЯК (INDIRECT) създава връзка към началната клетка E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Ако вмъкнете ред в горната част на листа и добавите стойността за януари (януари), тогава сумата в колона C няма да се промени. Формулата ще се промени, реагирайки на добавянето на ред:
=SUM(C3:C8)
=СУММ(C3:C8)
Функцията обаче НЕПРЯК (INDIRECT) фиксира E2 като начална клетка, така че януари автоматично се включва в изчисляването на сумите на колона E. Крайната клетка е променена, но началната клетка не е засегната.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Пример 2: Връзка към статичен именуван диапазон
функция НЕПРЯК (INDIRECT) може да създаде препратка към именуван диапазон. В този пример сините клетки съставляват диапазона NumList. Освен това се създава динамичен диапазон от стойностите в колона B NumListDyn, в зависимост от броя на числата в тази колона.
Сумата за двата диапазона може да се изчисли, като просто се даде името му като аргумент на функцията SUM (SUM), както можете да видите в клетки E3 и E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Вместо да въвеждате име на диапазон във функция SUM (SUM), можете да се обърнете към името, написано в една от клетките на работния лист. Например, ако името NumList се записва в клетка D7, тогава формулата в клетка E7 ще бъде като тази:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
За съжаление функцията НЕПРЯК (INDIRECT) не може да създаде препратка към динамичен диапазон, така че когато копирате тази формула в клетка E8, ще получите грешка #REF! (#ВРЪЗКА!).
Пример 3: Създайте връзка, като използвате информация за лист, ред и колона
Можете лесно да създадете връзка въз основа на номерата на редовете и колоните, както и да използвате стойността FALSE (FALSE) за втория аргумент на функцията НЕПРЯК (НЕПРЯК). Така се създава стиловата връзка R1C1. В този пример добавихме допълнително името на листа към връзката – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Пример 4: Създаване на непроменлив масив от числа
Понякога трябва да използвате масив от числа във формули на Excel. В следващия пример искаме да осредним 3-те най-големи числа в колона B. Числата могат да бъдат въведени във формула, както се прави в клетка D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Ако имате нужда от по-голям масив, тогава едва ли ще искате да въведете всички числа във формулата. Вторият вариант е да използвате функцията РЕД (РЕД), както е направено във формулата на масива, въведена в клетка D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Третият вариант е да използвате функцията РЕД (STRING) заедно с НЕПРЯК (ИНДИРЕКТНО), както се прави с формулата на масива в клетка D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Резултатът за всичките 3 формули ще бъде един и същ:
Ако обаче редовете са вмъкнати в горната част на листа, втората формула ще върне неправилен резултат поради факта, че препратките във формулата ще се променят заедно с изместването на реда. Сега, вместо средната стойност на трите най-големи числа, формулата връща средната стойност на 3-то, 4-то и 5-то най-големи числа.
Използване на функции НЕПРЯК (INDIRECT), третата формула запазва правилните препратки към редове и продължава да показва правилния резултат.