Contidos
Parabéns! Chegaches ao último día do maratón 30 funcións de Excel en 30 días. Foi unha viaxe longa e interesante durante a que aprendeu moitas cousas útiles sobre as funcións de Excel.
O día 30 do maratón dedicaremos o estudo da función INDIRECTO (INDIRECT), que devolve a ligazón especificada pola cadea de texto. Con esta función, pode crear listas despregábeis dependentes. Por exemplo, ao seleccionar un país dunha lista despregable determina as opcións que aparecerán na lista despregable da cidade.
Entón, vexamos a parte teórica da función INDIRECTO (INDIRECTO) e explora exemplos prácticos da súa aplicación. Se tes información adicional ou exemplos, compárteos nos comentarios.
Función 30: INDIRECTA
función INDIRECTO (INDIRECT) devolve a ligazón especificada pola cadea de texto.
Como pode usar a función INDIRECTA?
Xa que a función INDIRECTO (INDIRECT) devolve unha ligazón dada por unha cadea de texto, podes usala para:
- Crea unha ligazón inicial non cambiante.
- Crea unha referencia a un intervalo con nome estático.
- Crea unha ligazón usando información de follas, filas e columnas.
- Crea unha matriz de números non cambiante.
Sintaxe INDIRECTO (INDIRECTO)
función INDIRECTO (INDIRECT) ten a seguinte sintaxe:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- texto_ref (link_to_cell) é o texto da ligazón.
- a1 – se é igual a VERDADEIRO (VERDADEIRO) ou non se especifica, empregarase o estilo da ligazón A1; e se FALSO (FALSO), entón o estilo R1C1.
Trampas INDIRECTAS (INDIRECTAS)
- función INDIRECTO (INDIRECTO) recalcúlase sempre que cambian os valores da folla de cálculo de Excel. Isto pode ralentizar moito o teu libro de traballo se a función se usa en moitas fórmulas.
- Se a función INDIRECTO (INDIRECTO) crea unha ligazón a outro libro de Excel, ese libro debe estar aberto ou a fórmula informará dun erro # Ref! (#LINK!).
- Se a función INDIRECTO (INDIRECTO) fai referencia a un intervalo que supera o límite de filas e columnas, a fórmula informará dun erro # Ref! (#LINK!).
- función INDIRECTO (INDIRECT) non pode facer referencia a un intervalo con nome dinámico.
Exemplo 1: crea unha ligazón inicial non cambiante
No primeiro exemplo, as columnas C e E conteñen os mesmos números, as súas sumas calcúlanse mediante a función SUM (SUMA) tamén son iguais. Non obstante, as fórmulas son lixeiramente diferentes. Na cela C8, a fórmula é:
=SUM(C2:C7)
=СУММ(C2:C7)
Na cela E8, a función INDIRECTO (INDIRECTO) crea unha ligazón á cela inicial E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Se insire unha fila na parte superior da folla e engade o valor de xaneiro (xaneiro), a cantidade da columna C non cambiará. A fórmula cambiará, reaccionando á adición dunha liña:
=SUM(C3:C8)
=СУММ(C3:C8)
Con todo, a función INDIRECTO (INDIRECTO) fixa E2 como cela inicial, polo que xaneiro inclúese automaticamente no cálculo dos totais da columna E. A cela final cambiou, pero a cela inicial non se viu afectada.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Exemplo 2: ligazón a un intervalo con nome estático
función INDIRECTO (INDIRECT) pode crear unha referencia a un intervalo nomeado. Neste exemplo, as celas azuis compoñen o intervalo Lista Num. Ademais, tamén se crea un rango dinámico a partir dos valores da columna B NumListDyn, dependendo do número de números desta columna.
A suma de ambos intervalos pódese calcular simplemente dando o seu nome como argumento para a función SUM (SUMA), como podes ver nas celas E3 e E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
En lugar de escribir un nome de rango nunha función SUM (SUMA), Podes facer referencia ao nome escrito nunha das celas da folla de traballo. Por exemplo, se o nome Lista Num está escrito na cela D7, entón a fórmula da cela E7 será a seguinte:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Desafortunadamente a función INDIRECTO (INDIRECTO) non pode crear unha referencia de rango dinámico, polo que cando copies esta fórmula na cela E8, obterás un erro # Ref! (#LINK!).
Exemplo 3: crea unha ligazón usando información de folla, fila e columna
Podes crear facilmente unha ligazón baseada nos números de fila e columna, así como usar o valor FALSE (FALSO) para o segundo argumento da función INDIRECTO (INDIRECTO). Así é como se crea a ligazón de estilo R1C1. Neste exemplo, engadimos ademais o nome da folla á ligazón: 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Exemplo 4: crea unha matriz de números non cambiante
Ás veces cómpre usar unha matriz de números nas fórmulas de Excel. No seguinte exemplo, queremos facer a media dos 3 números máis grandes da columna B. Os números pódense introducir nunha fórmula, como se fai na cela D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Se necesitas unha matriz máis grande, é improbable que queiras introducir todos os números na fórmula. A segunda opción é usar a función Row (FILA), como se fixo na fórmula matricial introducida na cela D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
A terceira opción é usar a función Row (STRING) xunto con INDIRECTO (INDIRECT), como se fixo coa fórmula matricial na cela D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
O resultado das 3 fórmulas será o mesmo:
Non obstante, se se insiren filas na parte superior da folla, a segunda fórmula devolverá un resultado incorrecto debido ao feito de que as referencias na fórmula cambiarán xunto co desprazamento da fila. Agora, en lugar da media dos tres números máis grandes, a fórmula devolve a media dos 3º, 4º e 5º.
Usando funcións INDIRECTO (INDIRECT), a terceira fórmula mantén as referencias de fila correctas e segue mostrando o resultado correcto.