Използвайте функцията INDIRECT в формулите на Excel, за да промените обхвата на референтните клетки, използвани във формулата, без да е необходимо да редактирате самата формула. Това гарантира, че се използват същите клетки, дори когато електронната Ви таблица се промени.
Забележка: Инструкциите в тази статия се отнасят за Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel за Mac и Excel Online.
Използвайте динамичен диапазон с формулата COUNTIF - INDIRECT
Функцията INDIRECT може да се използва с редица функции, които приемат референция на клетката като аргумент, като например функциите SUM и COUNTIF.
Използването на INDIRECT като аргумент за COUNTIF създава динамичен диапазон от референтни клетки, които могат да бъдат отчетени от функцията, ако стойностите на клетките отговарят на критериите. Това става чрез преобразуване на текстови данни, понякога наричани текстови низове, в референтна клетка.
Този пример се основава на данните, показани на изображението по-горе. Формулата COUNTIF-INDIRECT, създадена в урока, е:
= COUNTIF (непряка (Е1 и ":" и Е2), "> 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 (непряка (Е1 и ":" и Е2), "две")
В тази формула функцията INDIRECT препраща към клетки В1 до В6. Функцията COUNTIF обобщава броя клетки, които имат текстовата стойност две в тях.
В този случай резултатът е 1.
COUNTA, COUNTBLANK и INDIRECT
Две други функции за преброяване на Excel са COUNTA, които броят клетки, съдържащи всякакъв вид данни, като пренебрегват само празни или празни клетки, и COUNTBLANK, който отчита само празни или празни клетки в обхват.
Тъй като и двете функции имат подобен синтаксис на функцията COUNTIF, те могат да бъдат заменени в горния пример с INDIRECT, за да се създадат следните формули:
= COUNTA (непряка (Е1 и ":" и Е2))= COUNTBLANK (непряка (Е1 и ":" и Е2)
За диапазона D1: D6, COUNTA връща отговор от 4, тъй като четири от петте клетки съдържат данни. COUNTBLANK връща отговор от 1, тъй като има само една празна клетка в диапазона. Ползата от използването на функцията INDIRECT във всички тези формули е, че новите клетки могат да бъдат вмъкнати навсякъде в диапазона. Обхватът динамично се измества вътре в различните функции, а резултатите се актуализират съответно. Без функцията INDIRECT, всяка функция би трябвало да бъде редактирана, за да включва всички 7 клетки, включително новата. Предимствата на функцията INDIRECT са, че текстовите стойности могат да се вмъкват като референтни клетки и динамично да обновява диапазовете, когато се промени таблицата ви. Това прави цялостната поддръжка на електронната таблица много по-лесна, особено при много големи електронни таблици. Защо да използваме ИНДИКАЛНА функция?
