contingut
Felicitats! Has arribat a l'últim dia de la marató 30 funcions d'Excel en 30 dies. Ha estat un viatge llarg i interessant durant el qual heu après moltes coses útils sobre les funcions d'Excel.
El dia 30 de la marató dedicarem l'estudi de la funció INDIRECTE (INDIRECT), que retorna l'enllaç especificat per la cadena de text. Amb aquesta funció, podeu crear llistes desplegables dependents. Per exemple, quan seleccioneu un país d'una llista desplegable determina quines opcions apareixeran a la llista desplegable de ciutats.
Per tant, fem una ullada més de prop a la part teòrica de la funció INDIRECTE (INDIRECT) i explorar exemples pràctics de la seva aplicació. Si teniu informació addicional o exemples, compartiu-los als comentaris.
Funció 30: INDIRECTA
function INDIRECTE (INDIRECT) retorna l'enllaç especificat per la cadena de text.
Com podeu utilitzar la funció INDIRECTE?
Des de la funció INDIRECTE (INDIRECT) retorna un enllaç donat per una cadena de text, podeu utilitzar-lo per:
- Creeu un enllaç inicial sense canvis.
- Creeu una referència a un interval amb nom estàtic.
- Creeu un enllaç amb informació de full, fila i columna.
- Creeu una matriu de nombres sense canvis.
Sintaxi INDIRECTE (INDIRECT)
function INDIRECTE (INDIRECT) té la sintaxi següent:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) és el text de l'enllaç.
- a1 – si és igual a TRUE (TRUE) o no s'especifica, s'utilitzarà l'estil de l'enllaç A1; i si FALSE (FALSE), aleshores l'estil R1C1.
Traps INDIRECTE (INDIRECT)
- function INDIRECTE (INDIRECT) es torna a calcular sempre que canvien els valors del full de treball d'Excel. Això pot alentir molt el vostre llibre de treball si la funció s'utilitza en moltes fórmules.
- Si la funció INDIRECTE (INDIRECT) crea un enllaç a un altre llibre d'Excel, aquest llibre ha d'estar obert o la fórmula informarà d'un error REF! (#ENLLAÇ!).
- Si la funció INDIRECTE (INDIRECT) fa referència a un interval que supera el límit de files i columnes, la fórmula informarà d'un error REF! (#ENLLAÇ!).
- function INDIRECTE (INDIRECT) no pot fer referència a un interval amb nom dinàmic.
Exemple 1: creeu un enllaç inicial sense canvis
En el primer exemple, les columnes C i E contenen els mateixos nombres, les seves sumes es calculen mitjançant la funció SUM (SUMA) també són els mateixos. Tanmateix, les fórmules són lleugerament diferents. A la cel·la C8, la fórmula és:
=SUM(C2:C7)
=СУММ(C2:C7)
A la cel·la E8, la funció INDIRECTE (INDIRECT) crea un enllaç a la cel·la inicial E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Si inseriu una fila a la part superior del full i afegiu el valor de gener (gen), l'import de la columna C no canviarà. La fórmula canviarà, reaccionant a l'addició d'una línia:
=SUM(C3:C8)
=СУММ(C3:C8)
Tanmateix, la funció INDIRECTE (INDIRECT) fixa E2 com a cel·la inicial, de manera que gener s'inclou automàticament en el càlcul dels totals de la columna E. La cel·la final ha canviat, però la cel·la inicial no s'ha vist afectada.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Exemple 2: Enllaç a un interval amb nom estàtic
function INDIRECTE (INDIRECT) pot crear una referència a un interval amb nom. En aquest exemple, les cel·les blaves formen l'interval NumList. A més, també es crea un rang dinàmic a partir dels valors de la columna B NumListDyn, en funció del nombre de números d'aquesta columna.
La suma d'ambdós rangs es pot calcular simplement donant el seu nom com a argument a la funció SUM (SUMA), com podeu veure a les cel·les E3 i E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
En lloc d'escriure un nom d'interval en una funció SUM (SUMA), Podeu fer referència al nom escrit en una de les cel·les del full de treball. Per exemple, si el nom NumList s'escriu a la cel·la D7, aleshores la fórmula de la cel·la E7 serà així:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Malauradament la funció INDIRECTE (INDIRECT) no pot crear una referència d'interval dinàmic, de manera que quan copieu aquesta fórmula a la cel·la E8, obtindreu un error REF! (#ENLLAÇ!).
Exemple 3: creeu un enllaç amb informació de full, fila i columna
Podeu crear fàcilment un enllaç basat en els números de fila i columna, així com utilitzar el valor FALSE (FALSE) per al segon argument de funció INDIRECTE (INDIRECTE). Així és com es crea l'enllaç d'estil R1C1. En aquest exemple, també hem afegit el nom del full a l'enllaç: 'MyLinks'! R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Exemple 4: creeu una matriu de nombres sense canvis
De vegades cal utilitzar una matriu de números a les fórmules d'Excel. En l'exemple següent, volem fer la mitjana dels 3 nombres més grans de la columna B. Els nombres es poden introduir en una fórmula, tal com es fa a la cel·la D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Si necessiteu una matriu més gran, és poc probable que vulgueu introduir tots els números a la fórmula. La segona opció és utilitzar la funció FILA (FILERA), tal com es fa a la fórmula de matriu introduïda a la cel·la D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
La tercera opció és utilitzar la funció FILA (STRING) juntament amb INDIRECTE (INDIRECT), tal com es fa amb la fórmula matricial de la cel·la D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
El resultat de les 3 fórmules serà el mateix:
Tanmateix, si s'insereixen files a la part superior del full, la segona fórmula retornarà un resultat incorrecte a causa del fet que les referències de la fórmula canviaran juntament amb el canvi de fila. Ara, en lloc de la mitjana dels tres nombres més grans, la fórmula retorna la mitjana del 3r, 4t i 5è nombres més grans.
Ús de funcions INDIRECTE (INDIRECT), la tercera fórmula manté les referències de fila correctes i continua mostrant el resultat correcte.