Функцията INDIRECT променя диапазона от препратки към клетки във формула, без да редактира формулата.
Използвайте INDIRECT като аргумент за COUNTIF, за да създадете динамичен диапазон от клетки, които отговарят на определени критерии.
Критериите се установяват от функцията INDIRECT и се броят само клетки, които отговарят на критериите.
Тази статия обяснява как да използвате функцията INDIRECT във формули на Excel, за да промените диапазона от препратки към клетки, използвани във формула, без да се налага да редактирате самата формула. Това гарантира, че се използват едни и същи клетки, дори когато вашата електронна таблица се промени. Информацията се отнася за Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel за Mac и Excel Online.
Използване на динамичен диапазон с формулата COUNTIF - INDIRECT
Функцията INDIRECT може да се използва с редица функции, които приемат препратка към клетка като аргумент, като например функциите SUM и COUNTIF.
Използването на INDIRECT като аргумент за COUNTIF създава динамичен диапазон от препратки към клетки, които могат да бъдат преброени от функцията, ако стойностите на клетката отговарят на критерии. Той прави това, като превръща текстови данни, понякога наричани текстов низ, в препратка към клетка.
Image
Този пример се основава на данните, показани на изображението по-горе. Формулата COUNTIF - INDIRECT, създадена в урока, е:
=COUNTIF(INDIRECT(E1&":"&E2), ">10")
В тази формула аргументът за функцията INDIRECT съдържа:
Клетката препраща към E1 и E2, които съдържат текстовите данни D1 и D6.
Операторът за диапазон, двоеточието (:), заобиколено от двойни кавички (" "), което превръща двоеточието в текст низ.
Два амперсанда (&), които се използват за свързване или обединяване на двоеточието с препратките към клетки E1 и E2.
Резултатът е, че INDIRECT преобразува текстовия низ D1:D6 в препратка към клетка и я предава на функцията COUNTIF, която да бъде преброена, ако посочените клетки са по-големи от 10.
Функцията INDIRECT приема всякакъв текст. Това могат да бъдат клетки в работния лист, които съдържат текст или препратки към текстови клетки, които се въвеждат директно във функцията.
Динамична промяна на диапазона на формулата
Запомнете, целта е да създадете формула с динамичен диапазон. Динамичен диапазон може да се променя без редактиране на самата формула.
Чрез промяна на текстовите данни, разположени в клетки E1 и E2, от D1 и D6 на D3 и D7, общият обхват от функцията може лесно да се промени от D1:D6 на D3:D7. Това елиминира необходимостта от директно редактиране на формулата в клетка G1.
Функцията COUNTIF в този пример брои само клетки, съдържащи числа, ако са по-големи от 10. Въпреки че четири от петте клетки в диапазона D1:D6 съдържат данни, само три клетки съдържат числа. Клетките, които са празни или съдържат текстови данни, се игнорират от функцията.
Отчитане на текст с COUNTIF
Функцията COUNTIF не е ограничена до преброяване на числови данни. Той също така брои клетки, които съдържат текст, като проверява дали съответстват на определен текст.
За да направите това, в клетка G2 се въвежда следната формула:
=COUNTIF(INDIRECT(E1&":"&E2), "две")
В тази формула функцията INDIRECT препраща към клетки B1 до B6. Функцията COUNTIF събира общия брой клетки, в които има текстова стойност две.
В този случай резултатът е 1.
COUNTA, COUNTBLANK и INDIRECT
Две други функции за броене на Excel са COUNTA, която брои клетки, съдържащи всякакъв тип данни, като игнорира само празни или празни клетки, и COUNTBLANK, която брои само празни или празни клетки в диапазон.
Тъй като и двете от тези функции имат подобен синтаксис на функцията COUNTIF, те могат да бъдат заменени в горния пример с INDIRECT, за да се създадат следните формули:
=COUNTA(INDIRECT(E1&":"&E2))
=COUNTBLANK(INDIRECT(E1&":"&E2)
За диапазона D1:D6, COUNTA връща отговор 4, тъй като четири от петте клетки съдържат данни. COUNTBLANK връща отговор 1, тъй като има само една празна клетка в диапазона.
Защо да използвате INDIRECT функция?
Ползата от използването на функцията INDIRECT във всички тези формули е, че нови клетки могат да бъдат вмъкнати навсякъде в диапазона.
Диапазонът динамично се измества вътре в различните функции и резултатите се актуализират съответно.
Image
Без функцията INDIRECT всяка функция ще трябва да се редактира, за да включва всичките 7 клетки, включително новата.
Предимствата на функцията INDIRECT са, че текстовите стойности могат да бъдат вмъкнати като препратки към клетки и че тя динамично актуализира диапазони, когато вашата електронна таблица се промени.
Това прави цялостната поддръжка на електронни таблици много по-лесна, особено за много големи електронни таблици.
Функцията COUNTIF в Excel преброява броя на клетките, които отговарят на определен критерий, което може да бъде полезно в редица случаи. Ето как да използвате COUNTIF с единични критерии в Excel
Научете как да създавате, редактирате и дефинирате именуван диапазон в Microsoft Excel, както и обхвата и ограниченията за именуване. Актуализиран, за да включва Excel 2019
