Мајкрософт Ексел има некои кул трикови и со користење на SUM и ИНДИРЕКТНИ динамички опсег формули се само два начина за лесно да манипулираат со податоците што ги имате.
SUM - ИНДИРЕКТНА Преглед на формула
Користењето на функцијата INDIRECT во Excel формулите го олеснува менувањето на опсегот на референци на ќелии што се користат во формулата, без да мора да ја уредувате самата формула.
INDIRECT може да се користи со голем број на функции кои прифаќаат референца на ќелијата како аргумент како функции OFFSET и SUM.
Во вториот случај, со користење на INDIRECT како аргумент за функцијата SUM може да се создаде динамичен опсег на референци на ќелиња, кои функцијата SUM потоа додава.
INDIRECT го прави ова со упатување на податоците во ќелиите индиректно преку посреден локација.
Пример: SUM - INDIRECT Формула се користи за вкупно динамички опсег на вредности
Овој пример се базира на податоците прикажани на сликата погоре.
Формулата SUM - INDIRECT создадена со користење на чекорите за упатства подолу е:
= SUM (INDIRECT ("D" & E1 & ": D" & E2))
Во оваа формула, аргументот на вгнездена INDIRECT функција содржи референци на клетките E1 и E2. Броевите во тие ќелии, 1 и 4, кога се комбинирани со останатиот аргумент на INDIRECT, ги формираат референтните ќелии D1 и D4.
Како резултат на тоа, опсегот на броеви зафатени со функцијата SUM е податоците содржани во опсегот на ќелии од D1 до D4 - што е 50.
Со менување на броевите лоцирани во ќелии Е1 и Е2; сепак, опсегот што треба да се постигне може лесно да се промени.
Овој пример прво ќе ја искористи горенаведената формула за да ги комплетира податоците во ќелиите D1: D4 и потоа да го смени сумираниот опсег на D3: D6 без да ја уредува формулата во ќелијата F1.
01 од 03
Внесување на Формулата - Опции
Опциите за внесување на формулата вклучуваат:
- пишувајќи ја погоре формулата директно во ќелијата F1 и притискање на копчето Enter на тастатурата
- внесете ја функцијата INDIRECT како аргумент користејќи го дијалогот за SUM функција
Повеќето функции во Excel имаат дијалог-кутија, која ви овозможува да ги внесете сите аргументи на функцијата на посебна линија, без да морате да се грижите за синтаксата .
Во овој случај, дијалогот за функцијата SUM може да се користи за да се поедностави формулата до одреден степен. Бидејќи функцијата ИНДИРЕКТ се вгнездува во SUM, функцијата INDIRECT и неговите аргументи се уште треба да се внесат рачно.
Чекорите подолу го користат дијалогот SUM за да влезат во формулата.
Внесување на податоците за прирачникот
Податоци на клетката D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Внесете ги следните податоци во ќелиите D1 до E2
Започнување на SUM - INDIRECT Формула - Отворање на дијалогот за SUM функција
- Кликнете на ќелијата F1 - тука ќе бидат прикажани резултатите од овој пример
- Кликнете на јазичето Формула на менито на лентата
- Изберете го Math & Trig од лентата за да ја отворите паѓачката листа на функции
- Кликнете на SUM во списокот за да го отворите дијалогот за функцијата
02 од 03
Внесување на функцијата INDIRECT - Кликни за да ја видите поголема слика
Формулата INDIRECT треба да се внесе како аргумент за функцијата SUM.
Во случај на вгнездени функции, Excel не дозволува отворање на дијалогот за втората функција за да ги внесе своите аргументи.
Затоа, функцијата ИНДИРЕКТ мора да се внесе рачно во линијата Number1 од дијалоговото поле SUM Function.
- Во дијалогот, кликнете на линијата Number1
- Внесете ја следнава ИНДИРЕКТНА функција: ИНДИРЕКТНА ("D" & E1 & ": D" & E2)
- Кликнете на OK за да ја завршите функцијата и да го затворите дијалогот
- Бројот 50 треба да се појави во ќелијата F1 бидејќи ова е вкупниот износ за податоците лоцирани во ќелиите D1 до D4
- Кога ќе кликнете на ќелијата F1, се појавува комплетната формула = SUM (INDIRECT ("D" & E1 & ": D" & E2)) во формуларот над работниот лист
Прекинување на функцијата ИНДИРЕКТ
Со цел да се создаде динамичен опсег во колоната D користејќи INDIRECT, мораме да ја комбинираме буквата D во аргументот INDIRECT со броевите содржани во ќелиите E1 и E2.
Ова се постигнува со следново:
- ampersand ( & ) се користи за конкатенирање или спојување на текстуалните податоци (во овој случај со буквата D) со референца на ќелијата (E1 и E2)
- понатаму, текстуалните податоци што се конкатенирани со референци на ќелиите мора да бидат опкружени со двојни наводници ( "" )
- На крај, крајните точки од опсегот се одделени со дебелото црево (:)
Затоа, почетната точка на опсегот е дефинирана со знаците: "D" & E1 .
Вториот сет на знаци: ": D" & E2 го комбинира дебелото црево со крајната точка. Ова е направено затоа што дебелото црево е текстуален карактер и затоа мора да биде вклучен во наводници.
Третиот амперсанд во средината се користи за обединување на двата дела во еден аргумент :
"D" & E1 & ": D" & E203 од 03
Динамично менување на опсегот на SUM функцијата
Целата поента на оваа формула е да се олесни измената на опсегот на функцијата SUM без да се уредува аргументот на функцијата.
Со вклучување на функцијата ИНДИРЕКТ во формулата, менувањето на броевите во ќелиите E1 и E2 ќе го смени опсегот на клетки што ги чита функцијата SUM.
Како што може да се види на сликата погоре, ова, исто така, резултира со одговорот на формулата се наоѓа во ќелијата Ф1 се менува, како што тоа го претставува новиот опсег на податоци.
- Кликнете на ќелијата Е1
- Внесете го бројот 3
- Притиснете го копчето Enter на тастатурата
- Кликнете на ќелијата Е2
- Напишете го бројот 6
- Притиснете го копчето Enter на тастатурата
- Одговорот во ќелијата F1 треба да се промени на 90 - што е вкупниот број на броеви содржани во ќелиите D3 до D6
- Понатаму проверете ја формулата со менување на содржината на клетките Б1 и Б2 на било кој број помеѓу 1 и 6
ИНДИРЕКТЕН и #REF! Вредност на грешка
#REF! вредноста на грешка ќе се појави во ќелијата F1 ако аргументот INDIRECT е:
- не е валидна референца на ќелијата
- содржи надворешна референца за друга работна книга и таа работна книга не е отворена
- се однесува на опсегот на ќелиите надвор од границите на работниот лист (ред 1,048,576 или колона XFD)