Función INDIRECTA en Excel
La función indirecta en Excel es una función incorporada que se utiliza para hacer referencia y obtener valores de celda de una cadena de texto, esta fórmula hace referencia a la celda referida, tiene dos argumentos, el primer argumento no es opcional mientras que el segundo argumento es opcional que se refiere a el tipo de coincidencia, esta función también se puede utilizar con otra fórmula.
La función INDIRECTA en Excel tiene múltiples usos. Devuelve la referencia de celda especificada por una cadena de texto. Por ejemplo, si la celda B1 contiene una cadena, digamos que los rangos c4 y c4 tienen el valor "Jessica". Ahora, si usamos la función INDIRECTO y pasamos el texto de referencia como B1, devolverá el valor contenido por el rango de celdas c4 que es "Jessica".
La función INDIRECTA de Excel le permite especificar una dirección de celda INDIRECTAMENTE. Como puede en el ejemplo de Excel INDIRECTO anterior, si la celda B1 contiene el texto C4, esta fórmula de Excel indirecta devuelve el contenido de la celda C4 ( Jessica ). Esta función es una función extremadamente útil. Puede usar la función INDIRECTO siempre que desee cambiar la referencia a una celda dentro de una fórmula de Excel indirecta sin cambiar la fórmula en sí.
Fórmula de Excel INDIRECTA
Explicación
Ref_text es una referencia a una celda que contiene una referencia de estilo A1, una referencia de estilo R1C1, un nombre definido como referencia o una referencia a una celda como una cadena de texto.
el argumento a1 es opcional
| a1 | Falso o 0, cuando ref_text está en estilo R1C1 |
| a1 | Omitido o verdadero (1) cuando ref_text está en estilo A1 |
¿Cómo usar la función INDIRECTA en Excel?
Tomemos un ejemplo de Excel INDIRECTO antes de usar la función de dirección en el libro de Excel:
Supongamos que para un sitio web www.xyz.com tenemos los datos analíticos de Google para los visitantes del sitio web de diferentes canales de audiencia para cinco países diferentes, como se muestra a continuación en la tabla:
Podemos usar la función INDIRECTA en Excel con el propósito de convertir una cadena de texto en una referencia, refiriéndonos a la celda H4 y usando ese nombre como referencia. El uso de la función INDIRECTO en Excel, en este caso, es hacer referencia a los rangos de nombres usando los valores de una celda, por lo que en la celda I4, podríamos simplemente usar la función de suma para calcular el número total de visitantes de un país Canadá.
Ahora, podemos calcular directamente la suma de visitantes de cada país usando la función de suma, pero usando la función INDIRECTA en Excel, podemos calcular la suma de visitantes de diferentes países cambiando la referencia del nombre en lugar de cambiar la fórmula de Excel indirecta en sí.
Hemos creado el rango de nombres para cada visitante de país; esto se puede hacer fácilmente escribiendo el rango de nombres en el espacio justo debajo del portapapeles, como se muestra a continuación.
Otro método es ir a fórmulas-> administrador de nombres -> seleccionar el rango que desea nombrar -> hacer clic en el administrador de nombres -> escribir el rango de nombres.
De manera similar, también creó un rango con nombre en Excel para visitantes de otros países.
Ahora, en I4, calculando la suma del visitante del país Canadá usando la función INDIRECTO y usando el rango de nombre en H4 (Canadá, un nombre arriba para F3: F7), obtenemos la suma total de un visitante.
Si creamos una lista de datos usando la validación de datos para los países y aplicamos esa lista a H4
Y cambiando los diferentes nombres de países de Canadá a India, EE. UU., Australia o Singapur, obtenemos la suma total del visitante en I4 usando la función INDIRECTA en Excel.
Entonces, para cada caso, no estamos cambiando la fórmula de Excel indirecto, que está en la celda I4; estamos cambiando la referencia del nombre en H4 y calculando la suma de los visitantes para cada país.
Podemos ir un paso más allá y podemos hacer referencia a nombres de nivel de hoja de trabajo también usando la función INDIRECTA en Excel. Por ejemplo, ahora usaremos la función INDIRECTO en referencia a diferentes hojas en el libro de trabajo.
Hemos creado diferentes hojas con los nombres de los países para el número total de ventas realizadas con cada canal de búsqueda para diferentes países.
Para la India,
Para los EE. UU.,
Del mismo modo, para otros países.
Nuevamente, estaremos calculando las ventas totales realizadas a través de cada canal utilizando la función INDIRECTA en Excel para diferentes hojas con nombres de países como referencia.
En este caso, dado que tenemos datos de ventas en diferentes hojas, necesitaremos concatenar diferentes elementos que son el rango de datos, por lo que ref_text comenzará con el nombre de la hoja, que es India en este caso en B4, luego lo haremos use el operador & llamado ampersand que se usa para la concatenación.
Entonces, obtenemos la suma total de las ventas de India; ahora, si cambiamos el nombre del país de la hoja en B4 de India a otros países como EE. UU., Singapur o Canadá, obtendremos el valor de venta total de esos países.
Nuevamente, en este caso, simplemente hemos cambiado los valores de ref_text en la celda B4 sin cambiar la fórmula principal indirecta de Excel, que está en C4, para obtener la suma de las ventas para diferentes países que están en diferentes hojas usando la función INDIRECTA.
Ahora, vamos a usar la función INDIRECTA usando la referencia R1C1 para obtener el valor de venta del último mes. Tenemos ventas para diferentes meses en las columnas B, C y D durante tres meses, enero, febrero y marzo, que pueden crecer hasta más meses como abril, mayo, junio, etc. Por lo tanto, la tabla de ofertas siempre crecerá dependiendo de la adición de la oferta del próximo mes. Entonces, en este ejemplo de Excel INDIRECTO, queremos calcular la venta del último mes.
Entonces, aquí el último mes es marzo, y queremos calcular la venta total del mes de marzo, que es $ 249,344, usando la función INDIRECTA.
En este ejemplo de Excel INDIRECTO, encontraremos el uso del segundo argumento de la función INDIRECTO
INDIRECTO (ref_text, a1)
a1: que es un argumento opcional, es un valor lógico (un valor booleano) que especifica qué tipo de referencia (ref_text) está contenido en la celda
si a1 es verdadero (1) o se omite, ref_text se considera una referencia de estilo A1
si a1 es falso (0), ref_text se interpreta como un estilo R1C1
¿En qué se diferencia el estilo A1 del estilo R1C1?
Normalmente, la función INDIRECTA en Excel usa la notación A1. Cada dirección de celda consta de una letra de columna y un número de fila. Sin embargo, la función INDIRECTA en Excel también admite la notación R1C1. En este sistema, la celda A1 se denomina celda R1C1, la celda A2 como R2C1 y así sucesivamente.
Para cambiar a la notación R1C1, elija la opción Archivo-> para abrir el cuadro de diálogo de opciones de Excel, haga clic en la pestaña de fórmula y marque la opción de estilo de referencia R1C1. Ahora, notará que todas las letras de las columnas cambian a números. Y todas las referencias de celda en sus fórmulas también se ajustan.
Entonces, cuando usamos el estilo R1C1 en la función INDIRECTO en Excel, pasamos el valor a1 como falso, y si usamos el estilo A1, entonces pasamos el valor verdadero a a1.
Ahora, en este ejemplo de Excel INDIRECTO, usaremos el estilo R1C1 debido al requisito, ya que las columnas con mes estarían creciendo.
Usando el estilo R1C1, el valor de venta total está en la fila 10, y el valor de la última columna, que está en la columna D, se calculará usando la función CONTAR , que dará el número de la última columna que contiene un valor. Entonces, cuente (10:10) en este caso dará 4, que es el número de columna en el estilo R1C1. Entonces, nos hemos puesto en contacto con ese valor para obtener R10C4 que contiene el valor deseado que es el valor de venta total del último mes ($ 249,344).
Y, dado que estamos usando el estilo R1C1 aquí, pasaremos un valor falso para el argumento a1.
Ahora, agreguemos una columna más con el mes de la venta de abril.
Por lo tanto, obtenga nuevos valores de ventas del último mes que correspondan a los del último mes de abril. De manera similar, si agregamos más meses, siempre obtendremos un nuevo valor de venta del último mes sin cambiar la fórmula de Excel indirecto para cada mes. Esta es la especialidad de la función INDIRECTA en Excel.
¿Cómo personalizar la matriz de la tabla en la función BUSCARV INDIRECTA de Excel?
Contamos con cinco países y un canal de búsqueda y la venta que el sitio web realizó a través de estos diferentes canales de diferentes países. Ahora, en la celda B3, queremos obtener la venta que fue a través de búsquedas orgánicas del país Estados Unidos.
Para cada tabla para diferentes países, hemos nombrado cada tabla con un nombre corto para el país, Ind de la India, los Estados de los EE.UU., Can para Canadá, y así sucesivamente .
Si usamos directamente la función vlookup de Excel INDIRECTA para obtener el valor, como se hace a continuación
Obtenemos un error porque la función vlookup de Excel INDIRECTA no es capaz de reconocer la matriz de tabla pasada como el valor de la celda B2, por lo que usaremos la función INDIRECTA en Excel para hacer que vlookup obtenga el table_array reconocido como un argumento válido.
Si cambiamos los valores de B1 y B2, obtendremos diferentes valores de Venta en B3, según sea necesario.
Nota: Los rangos de nombres no distinguen entre mayúsculas y minúsculas. Por lo tanto, Ind, IND e InD son todos iguales para Excel.
En el siguiente ejemplo de Excel INDIRECTO, usaremos la función INDIRECTO en Excel para crear una lista dependiente. Tenemos una lista del puesto de un empleado en una empresa; luego, para cada puesto, tenemos los nombres de los empleados.
Ahora, en la celda I2, crearemos una lista para diferentes rangos de empleados como sus posiciones, y en I3, mostraremos una lista que dependerá del valor en I2. Si I2 es un director, entonces I3 debería reflejar los nombres de los directores que Andrew y Micheal; si I2 es Gerente, entonces I3 debería reflejar los nombres del gerente que es Camille, David, Davis y William y de la misma manera para el supervisor.
En este caso, nuevamente, usaremos la función INDIRECTO en Excel para crear la lista dependiente. Validación de datos, en la configuración seleccionando criterios de validación como una lista y en el campo fuente, usaremos la función INDIRECTA en Excel de la siguiente manera.
= INDIRECTO ($ I $ 2)
Cuando seleccionamos los nombres de los directores en I3 se refleja como una lista con los nombres Andrew y Micheal, cuando I2 es el gerente, I3 refleja los nombres del gerente Camille, David, Davis y William y de manera similar para el supervisor, como se muestra a continuación en la figura.
Cosas para recordar
- El argumento ref_text debe ser una referencia de celda válida, o INDIRECT devolverá el #REF! Error. El argumento ref_text puede hacer referencia a otro libro de trabajo.
- Si ref_text se refiere a otro libro de trabajo (una referencia externa), el otro libro de trabajo debe estar abierto. Si el libro de trabajo de origen no está abierto, INDIRECT devuelve el #REF! Valor de error.
- Si ref_text se refiere a un rango de celdas fuera del límite de fila de 1,048,576 o el límite de columna de 16,384 (XFD), INDIRECT devuelve un #REF! Error.
