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