Utilitzeu la funció INDIRECT a les fórmules d'Excel per canviar l'interval de referències de cel·les que s'utilitza en una fórmula sense haver d'editar la fórmula. Això assegura que s'utilitzen les mateixes cel·les, fins i tot quan canvia el vostre full de càlcul.
Nota: Les instruccions d'aquest article s'apliquen a Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel per a Mac i Excel Online.
Utilitzeu un interval dinàmic amb la fórmula COUNTIF - INDIRECT
La funció INDIRECT es pot utilitzar amb diverses funcions que accepten una referència de cel·la com un argument, com ara les funcions SUM i COUNTIF.
Usant INDIRECTAL com l'argument per COUNTIF crea un rang dinàmic de referències de cel·les que es pot comptar per la funció si els valors de les cel·les compleixen un criteri. Això ho fa mitjançant la conversió de dades de text, de vegades anomenada cadena de text, en una referència de cel·la.
Aquest exemple es basa en les dades que es mostren a la imatge de dalt. La fórmula COUNTIF - INDIRECT creada al tutorial és:
= COUNTIF (INDIRECTE (E1 & ":" & E2), "> 10")
En aquesta fórmula, l'argument per a la funció INDIRECT conté:
- La cel·la fa referència a E1 i E2, que contenen les dades de text D1 i D6.
- L'operador de rang, el còlon (:) envoltat de cometes dobles (' ') que converteix el còlon en una cadena de text.
- Dos ampersands (&) que s'utilitzen per concatenar o unir els dos punts amb les referències cel·lulars E1 i E2.
El resultat és que INDIRECT converteix la cadena de text D1: D6 en una referència de cel·la i la passa a la funció COUNTIF per comptar si les cel·les referenciades són més grans que 10.
La funció INDIRECT accepta entrades de text. Aquestes poden ser cel·les del full de treball que contenen referències de text o de cel·la de text que s'introdueixen directament a la funció.
Canvieu dinàmicament el rang de la fórmula
Recordeu que l'objectiu és crear una fórmula amb un rang dinàmic. Es pot canviar un interval dinàmic sense editar la fórmula.
Si canvieu les dades de text que es troben a les cel·les E1 i E2, des de D1 i D6 a D3 i D7, el rang totalitzat per la funció es pot canviar fàcilment des de D1: D6 a D3: D7. Això elimina la necessitat d'editar directament la fórmula a la cel·la G1.
La funció COUNTIF en aquest exemple només compta amb cel·les que contenen nombres si són més grans que 10. Encara que quatre de les cinc cel·les del rang de D1: D6 contenen dades, només tres cel·les contenen nombres. Les cèl·lules que estan en blanc o contenen dades de text són ignorades per la funció.
Comptant el text amb COUNTIF
La funció COUNTIF no es limita a comptar dades numèriques. També compta amb cel·les que contenen text si comprova si coincideixen amb un determinat text.
Per fer-ho, s'introdueix la següent fórmula a la cel·la G2:
= COUNTIF (INDIRECTE (E1 & ":" & E2), "dos")
En aquesta fórmula, la funció INDIRECT fa referència a les cel·les B1 a B6. La funció COUNTIF suma el nombre de cel·les que tenen el valor del text dos en ells.
En aquest cas, el resultat és 1.
COUNTA, COUNTBLANK i INDIRECT
Dues altres funcions de comptes d'Excel són COUNTA, que compta amb cel·les que contenen qualsevol tipus de dades mentre ignora només cel·les en blanc o buides i COUNTBLANK, que només conté cel·les en blanc o buides d'un rang.
Atès que aquestes dues funcions tenen una sintaxi similar a la funció COUNTIF, es poden substituir a l'exemple anterior amb INDIRECT per crear les següents fórmules:
= COUNTA (INDIRECTE (E1 & ":" & E2))= COUNTBLANK (INDIRECT (E1 & ":" & E2)
Per al rang D1: D6, COUNTA retorna una resposta de 4, ja que quatre de les cinc cel·les contenen dades. COUNTBLANK retorna una resposta de 1 ja que només hi ha una cel·la buida al rang. El benefici d'utilitzar la funció INDIRECT en totes aquestes fórmules és que es poden inserir cèl·lules noves en qualsevol lloc del rang. El rang gira dinàmicament dins de les diferents funcions, i els resultats s'actualitzen en conseqüència. Sense la funció INDIRECT, caldria editar totes les funcions per incloure les 7 cel·les, inclosa la nova. Els avantatges de la funció INDIRECT són que els valors de text es poden inserir com a referències de cel·les i que actualitza dinàmicament els intervals cada vegada que canvia el full de càlcul. Això fa que el manteniment general de fulls de càlcul sigui molt més fàcil, especialment per a fulls de càlcul molt grans. Per què utilitzar una funció INDIRECTA?
