Clase 01112018 - TP

Descargar  Archivo de Ejemplos. Desarrollo conjunto en clase.

Función INDICE:

Devuelve un valor o la referencia a un valor desde una tabla o rango.

Forma de matriz

Devuelve el valor de un elemento de una tabla o matriz seleccionado por los índices de número de fila y de columna.

Use la forma matricial si el primer argumento de INDICE es una constante matricial.

Sintaxis: INDICE(matriz; núm_fila; [núm_columna])

Matriz    Obligatorio. Es un rango de celdas o una constante de matriz.

Si matriz contiene solo una fila o columna, el argumento núm_fila o núm_columna correspondiente es opcional.

Si matriz tiene varias filas y columnas, y solo usa núm_fila o núm_columna, INDICE devuelve una matriz de dicha fila o columna completa.

Núm_fila    Obligatorio. Selecciona la fila de la matriz desde la cual devolverá un valor. Si omite núm_fila, se necesita el argumento núm_columna.

Núm_columna    Opcional. Selecciona la columna de la matriz desde la cual devolverá un valor. Si omite núm_columna, se necesita el argumento núm_fila.

=INDICE(A2:B3,2,2)        Valor en la intersección de la segunda fila y la segunda columna del rango A2:B3.

Forma de referencia

Devuelve la referencia de la celda ubicada en la intersección de una fila y de una columna determinadas. Si la referencia se compone de selecciones no adyacentes, puede elegir la selección donde buscar

.

Sintaxis:  INDEX(ref, núm_fila, [núm_columna], [núm_área])

La sintaxis de la función INDICE tiene los siguientes argumentos:

Ref    Obligatorio. Es una referencia a uno o varios rangos de celdas.

Si especifica un rango no adyacente como argumento ref, escríbalo entre paréntesis.

Si cada área del argumento ref contiene una sola fila o columna, los argumentos núm_fila o núm_columna serán opcionales respectivamente. Por ejemplo, use INDICE(ref,,núm_columna) para un argumento ref con una sola fila.

Núm_fila    Obligatorio. Es el número de la fila en el argumento ref desde la que se devolverá una referencia.

Núm_columna    Opcional. Es el número de la columna en el argumento ref desde la que se devolverá una referencia.

Núm_área    Opcional. Selecciona un rango en el argumento ref desde el cual se devolverá la intersección de núm_fila y núm_columna. La primera área seleccionada o especificada se numera con 1, la segunda con 2 y así sucesivamente. Si omite núm_área, INDICE usa el área 1.  Las áreas que se muestran aquí deben estar en una hoja.  Si especifica áreas que no están en la misma hoja, provocará un #VALUE! error. 

Si necesita usar intervalos que se encuentran en hojas diferentes entre sí, se recomienda usar la forma de matriz de la función INDICE y usar otra función para calcular el rango que compone la matriz.  Por ejemplo, podría usar la función ELEGIR para calcular el intervalo que se va a usar.

Por ejemplo, si ref describe las celdas (A1:B4;D1:E4;G1:H4), entonces núm_área 1 es el rango A1:B4, núm_área 2 es el rango D1:E4 y núm_área 3 es el rango G1:H4.

Función COINCIDIR

La función COINCIDIR en Excel nos ayuda a localizar un elemento dentro de un rango de celdas y nos devuelve su posición. En otras palabras, la función COINCIDIR nos ayuda a obtener el número de fila que ocupa el elemento buscado.

Sintaxis de la función COINCIDIR

La función COINCIDIR tiene tres argumentos:

Valor_buscado (obligatorio): El valor que estamos buscando.

Matriz_buscada (obligatorio): El rango de celdas donde realizaremos la búsqueda.

Tipo_de_coincidencia (opcional): Un número que indica el tipo de coincidencia en la búsqueda.

El Tipo_de_coincidencia especificado en la función tendrá un efecto en la manera en que se hace la búsqueda. Los siguientes valores son los posibles para el tercer argumento de la función COINCIDIR:

1: La función COINCIDIR encontrará el valor más grande que sea menor o igual al valor buscado. La matriz de búsqueda debe estar ordenada de manera ascendente.

0: La función COINCIDIR encontrará el primer valor que sea exactamente igual al valor buscado. La matriz de búsqueda puede estar en cualquier orden.

-1: La función COINCIDIR encontrará el valor más pequeño que sea mayor o igual que el valor buscado. La matriz de búsqueda debe estar ordenada de manera descendente.

Cuando no se especifica el tercer argumento de la función COINCIDIR se utilizará de manera predeterminada el valor 1.

Ejemplo: =COINCIDIR("EFG", A1:A10, 0)

Función INDIRECTO:

Devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee cambiar la referencia a una celda en una fórmula sin cambiar la propia fórmula.

Sintaxis:  INDIRECTO(ref; [a1])

La sintaxis de la función INDIRECTO tiene los siguientes argumentos:

Ref    Obligatorio. Una referencia a una celda que contiene una referencia de tipo A1 o F1C1, un nombre definido como referencia o una referencia a una celda como cadena de texto. Si ref no es una referencia de celda válida, INDIRECTO devuelve el valor de error #¡REF!.

Si ref hace referencia a otro libro (una referencia externa), el otro libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devolverá el valor de error #¡REF!.

Si ref hace referencia a un rango de celdas fuera del límite de filas de 1.048.576 o del límite de columnas de 16.384 (XFD), INDIRECTO devolverá el error #¡REF!.

Nota    Este comportamiento es diferente al de otras versiones de Excel anteriores a Microsoft Office Excel 2007, que ignoran el límite superado y devuelven un valor.

A1    Opcional. Un valor lógico que especifica el tipo de referencia que contiene la celda ref.

Si a1 es VERDADERO o se omite, ref se interpreta como una referencia estilo A1.

Si a1 es FALSO o se omite, ref se interpreta como una referencia estilo F1C1.

Ejemplo:
=INDIRECTO(A3)     
Valor de la referencia en la celda A3. La referencia es a la celda B3, que contiene el valor 45.

Función DESREF

Devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver.

Sintaxis: DESREF(ref, filas, columnas, [alto], [ancho])

Referencia    Obligatorio. Es la referencia en la que desea basar la desviación. La referencia debe referirse a una celda o un rango de celdas adyacentes; en caso contrario, DESREF devuelve el valor de error #¡VALOR!.

Filas    Obligatorio. Es el número de filas, hacia arriba o hacia abajo, al que desea que haga referencia la celda superior izquierda. Si el argumento filas es 5, la celda superior izquierda de la referencia pasa a estar cinco filas más abajo que la referencia. Filas puede ser positivo (lo que significa que está por debajo de la referencia de inicio) o negativo (por encima).

Columnas    Obligatorio. Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia la celda superior izquierda del resultado. Si el argumento columnas es 5, la celda superior izquierda de la referencia pasa a estar cinco columnas hacia la derecha de la referencia. Columnas puede ser positivo (lo que significa a la derecha de la referencia de inicio) o negativo (a la izquierda).

Alto    Opcional. Es el alto, en número de filas, que se desea que tenga la referencia devuelta. El alto debe ser un número positivo.

Ancho    Opcional. Es el ancho, en número de columnas, que se desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo.

Ejemplo: 

DESREF nos permite crear una referencia con tan solo indicar la celda donde comenzará el rango y posteriormente el número de filas y columnas que conforman dicho rango. Para nuestros datos de ejemplo, la celda inicial del rango se encuentra en la celda A1 por lo que comenzamos nuestra fórmula de la siguiente manera:

=DESREF($A$1,

El segundo y tercer argumento de la función DESREF nos sirven para indicar algún movimiento a partir de la celda inicial pero no son necesarios en la creación de rangos dinámicos así que los podemos indicar siempre como cero.

=DESREF($A$1, 0, 0,

Los argumentos importantes en la creación de rangos dinámicos son el cuarto y el quinto de la función DESREF ya que nos permiten indicar la cantidad de filas y columnas que deseamos incluir a partir de la celda inicial. El número de filas lo podemos obtener con la función CONTARA de la siguiente manera:

=DESREF($A$1, 0, 0, CONTARA($A:$A),

Y para obtener el número de columnas volvemos a utilizar la función CONTARA de la siguiente manera:

=DESREF($A$1, 0, 0, CONTARA($A:$A), CONTARA($1:$1))

Si introducimos esta fórmula por sí sola en una celda obtendrás un error #¡VALOR! ya que nuestra fórmula no devuelve un valor sino una referencia. Pero si colocamos la fórmula anterior dentro de la función SUMA le estaremos pidiendo a Excel que sume todos los valores del rango devuelto por  la función DESREF.

=SUMA(DESREF($A$1, 0, 0, CONTARA($A:$A), CONTARA($1:$1)))

Función  SI.ERROR()

Devuelve el valor especificado si una fórmula se evalúa como un error; de lo contrario, devuelve el resultado de la fórmula. Use la función SI.ERROR para interceptar y controlar errores en una fórmula.

Sintaxis: SIERROR(valor; valor_si_error)

La sintaxis de la función SI.ERROR tiene los siguientes argumentos:

·         Valor    Obligatorio. Es el argumento donde busca un error.

·         Valor_si_error    Obligatorio. Es el valor que se devuelve si la fórmula se evalúa como un error. Se evalúan los tipos de error siguientes: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!.

·         Si valor o valor_si_error están en una celda vacía, SI.ERROR los trata como un valor de cadena vacía ("").

·         

      Si valor es una fórmula de matriz, SI.ERROR devuelve una matriz de resultados para cada celda del rango especificado en el valor. Vea el segundo ejemplo a continuación.

Ejemplo: =SIERROR(A2/B2; "Error en el cálculo")             Comprueba si hay un error en la fórmula en el primer argumento (divide 210 entre 35), no encuentra ningún error y devuelve los resultados de la fórmula.

Función CONTAR.SI

Permitirá contar cuantas celdas diferentes de blanco de un rango cumplen con un criterio determinado.

CONTAR.SI(Rango de datos; Criterio o condición)

Ejemplos:

=CONTAR.SI(A2:A5,"Londres")

=CONTAR.SI(A2:A5;A4)

=CONTAR.SI(A2:A5;"manzanas")  Cuenta el número de celdas con manzanas entre las celdas A2 y A5. 

=CONTAR.SI(A2:A5;A4)   Cuenta el número de celdas con melocotones, el valor de A4, entre las celdas A2 y A5. 

=CONTAR.SI(A2:A5,A2)+CONTAR.SI(A2:A5,A3)     Cuenta el número de manzanas, el valor de A2, y de naranjas, el valor de A3, entre las celdas A2 y A5. 

Esta fórmula usa CONTAR.SI dos veces para especificar varios criterios, un criterio por expresión. También puede utilizar la función CONTAR.SI.CONJUNTO.

=CONTAR.SI(B2:B5;">55")     Cuenta el número de celdas con un valor superior a 55 entre las celdas B2 y B5. 

=CONTAR.SI(B2:B5;"<>"&B4)      Cuenta el número de celdas con un valor distinto de 75 entre las celdas B2 y B5. El símbolo de la "y" (&) combina el operador de comparación "<>" (no es igual a) y el valor de B4 para leer =CONTAR.SI(B2:B5,"<>75"). 

=CONTAR.SI(B2:B5;">=32")-CONTAR.SI(B2:B5;">85")         Cuenta el número de celdas con un valor superior o igual a 32 e inferior o igual a 85 entre las celdas B2 y A5.

=CONTAR.SI(A2:A5,"*")       Cuenta el número de celdas que contienen texto entre las celdas A2 y A5. El carácter comodín * se usa para reemplazar cualquier carácter. 

=CONTAR.SI(A2:A5,"?????es")     Cuenta el número de celdas que tienen exactamente 7 caracteres y que terminan con las letras es entre las celdas A2 y A5. El carácter comodín ? se usa para reemplazar caracteres individuales.