Microsoft Excel té alguns trucs interessants i l'ús de les fórmules de rang dinàmic SUM i INDIRECT són només dues maneres de manipular fàcilment les dades que teniu.
Sumari - Visió general de la fórmula INDIRECT
L'ús de la funció INDIRECT a les fórmules d'Excel fa que sigui fàcil canviar l'interval de referències de cel·les que s'utilitza a la fórmula sense haver d'editar la fórmula.
INDIRECT es pot utilitzar amb diverses funcions que accepten una referència de cel·la com un argument com ara les funcions OFFSET i SUM.
En aquest últim cas, utilitzar INDIRECT com a argument per a la funció SUM pot crear un rang dinàmic de referències de cel·la que la funció SUM afegeixi.
INDIRECTE fa això referint-se a les dades de les cèl·lules indirectament a través d'una ubicació intermèdia.
Exemple: FUMA SUM - INDIRECTA utilitzada per assolir un rang dinàmic de valors
Aquest exemple es basa en les dades que es mostren a la imatge de dalt.
La fórmula SUM - INDIRECT creada mitjançant els passos de tutorial a continuació és:
= SUM (INDIRECTE ("D" & E1 & ": D" & E2))
En aquesta fórmula, l'argument de la funció INDIRECT anidada conté referències a les cel·les E1 i E2. Els números d'aquestes cel·les, 1 i 4, quan es combinen amb la resta de l'argument INDIRECT, formen les referències cel·lulars D1 i D4.
Com a resultat, el rang de nombres representat per la funció SUM és la informació continguda en el rang de cel·les D1 a D4, que és de 50.
En canviar els números ubicats a les cel·les E1 i E2; No obstant això, el rang que cal sumar es pot canviar fàcilment.
Aquest exemple usarà per primera vegada la fórmula anterior per completar les dades a les cel·les D1: D4 i, a continuació, canviar el rang sumat a D3: D6 sense editar la fórmula a la cel·la F1.
01 de 03
Introduir la fórmula - Opcions
Les opcions per introduir la fórmula inclouen:
- escrivint la fórmula anterior directament a la cel·la F1 i prement la tecla Enter al teclat
- introduint la funció INDIRECT com a argument utilitzant el quadre de diàleg de la funció SUM
La majoria de les funcions d'Excel tenen un quadre de diàleg que permet introduir cadascun dels arguments de la funció en una línia diferent sense haver de preocupar-se de la sintaxi .
En aquest cas, el quadre de diàleg de la funció SUM es pot utilitzar per simplificar la fórmula en certa mesura. Atès que la funció INDIRECT està anidada a l'interior de SUM, la funció INDIRECT i els seus arguments encara s'han d'introduir manualment.
Els passos següents utilitzen el quadre de diàleg SUM per introduir la fórmula.
Introduir les dades del tutorial
Dades de la cel·la D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Introduïu les dades següents a les cel·les D1 a E2
Comença la FITXA SUM - INDIRECT - Obertura del quadre de diàleg SUM Function
- Feu clic a la cel·la F1: aquí es mostraran els resultats d'aquest exemple
- Feu clic a la pestanya Fórmules del menú de la cinta
- Trieu Math & Trig de la cinta per obrir la llista desplegable de la funció
- Feu clic a SUM a la llista per obrir el quadre de diàleg de la funció
02 de 03
Introduir la funció INDIRECT - Feu clic per veure la imatge més gran
La fórmula INDIRECT cal introduir com a argument per a la funció SUM.
En el cas de funcions anidadas, Excel no permet obrir el quadre de diàleg de la segona funció per introduir els seus arguments.
La funció INDIRECT, per tant, s'ha d'introduir manualment a la línia Número1 del quadre de diàleg SUM Function.
- Al quadre de diàleg, feu clic a la línia Número1
- Introduïu la següent funció INDIRECTA: INDIRECTE ("D" i E1 i ": D" i E2)
- Feu clic a D'acord per completar la funció i tancar el quadre de diàleg
- El número 50 hauria d'aparèixer a la cel·la F1 ja que aquest és el total de les dades ubicades a les cel·les D1 a D4
- Quan feu clic a la cel·la F1, la fórmula completa = SUM (INDIRECTE ("D" & E1 & ": D" & E2)) apareix a la barra de fórmules que hi ha a sobre del full de càlcul
Trencant la funció INDIRECTA
Per crear un rang dinàmic a la columna D utilitzant INDIRECT, hem de combinar la lletra D en l'argument de la funció INDIRECT amb els números continguts a les cel·les E1 i E2.
Això s'aconsegueix amb el següent:
- l'ampersand ( & ) s'utilitza per concatenar o unir dades de text (en aquest cas, la lletra D) amb una referència de cel·la (E1 i E2)
- a més, les dades de text que s'estan concatenant amb referències de cel·la han d'estar envoltades de cometes dobles ( "" )
- Finalment, els punts finals del rang estan separats per dos punts ( :)
Per tant, el punt d'inici del rang es defineix pels caràcters: "D" i E1 .
El segon conjunt de caràcters: ": D" i E2 combina el doble amb el punt final. Això es fa perquè el còlon és un caràcter de text i, per tant, s'ha d'incloure dins de les cometes.
El tercer ampersand en el medi s'utilitza per concatenar les dues parts en un sol argument :
"D" i E1 i ": D" i E203 de 03
Canvi dinàmic del rang de la funció SUM
El punt sencer d'aquesta fórmula és facilitar el canvi del rang totalitzat per la funció SUM sense haver d'editar l'argument de la funció.
En incloure la funció INDIRECT a la fórmula, canviar els números a les cel·les E1 i E2 canviarà el rang de cel·les llegit per la funció SUM.
Com es pot veure a la imatge anterior, això també dóna lloc a la resposta de la fórmula que es troba a la cel·la F1 canviant ja que totalitza el nou rang de dades.
- Feu clic a la cel·la E1
- Escriviu el número 3
- Premeu la tecla Enter al teclat
- Feu clic a la cel·la E2
- Escriviu el número 6
- Premeu la tecla Enter al teclat
- La resposta a la cel·la F1 hauria de canviar a 90 - que és el total dels números continguts a les cel·les D3 a D6
- A continuació, prova la fórmula canviant el contingut de les cel·les B1 i B2 a qualsevol número entre 1 i 6
INDIRECTE i el #REF! Valor d'error
El #REF! el valor d'error apareixerà a la cel·la F1 si l'argument de la funció INDIRECT:
- no és una referència de cel·la vàlida
- conté una referència externa a un llibre diferent i aquest llibre no està obert
- es refereix a un interval de cel·les fora dels límits del full de treball (fila 1.048.576 o columna XFD)