viernes, 30 de diciembre de 2011

Función INDICE. Forma de referencia

En la entrada anterior hemos utilizado la función INDICE en la "forma matricial". Esta función tiene una segundo modo de utilización: la "forma de referencia".

Las inversiones realizadas por una empresa entre los años 2007 y 2011 en sus sucursales de Europa, América y Asia han sido clasificadas por departamentos. Eligiendo un continente, un año y un departamento, debemos obtener la cifra invertida con una fórmula.

El ejercicio se resuelve facilmente usando la función INDICE en su forma de referencia, cuya sintaxis es:

INDICE(referencias;núm_fila;[núm_columna];[núm_área])

  • referencias son los distintos rangos en los que hay que realizar la búsqueda. Si son más de uno, se escriben separados por punto y coma y, todo ello, encerrado entre paréntesis.
  • núm_fila es la fila en la que hay que buscar.
  • núm_columna es la columna en la que hay que buscar.
  • núm_área es el rango (el área) en el que hay que realizar la búsqueda. La primera área es la 1; la segunda, la 2; la tercera, la 3, etc.

En J2 elegimos un continente; por ejemplo, Asia

En J3 elegimos el departamento: Marketing

En J4, el año: 2010

En J6:
=INDICE((B3:G9;B12:G18;B21:G27);COINCIDIR($J$3;$B$3:$B$9;0);COINCIDIR($J$4;$B$3:$G$3;0);COINCIDIR($J$2;{"Europa";"América";"Asia"};0))

  • (B3:G9;B12:G18;B21:G27), primer argumento, especifica las tres tablas en un orden concreto: Europa (que es el área 1), América (área 2) y Asia (área 3).
  • COINCIDIR($J$3;$B$3:$B$9;0) devuelve el número de la fila (de cualquiera de las tablas) donde se encuentra el departamento buscado.
  • COINCIDIR($J$4;$B$3:$G$3;0) devuelve el número de la columna (de cualquiera de las tablas) donde se encuentra el año buscado.
  • COINCIDIR($J$2;{"Europa";"América";"Asia"};0) devuelve un número del 1 al 3. En el ejemplo, como J2 contiene Asia, devuelve 3. Éste es el área donde se realizará la búsqueda.

 


jueves, 29 de diciembre de 2011

Seis métodos de búsqueda

Una operación frecuente en Excel es la búsqueda de datos en una tabla. Por ejemplo, con los datos de la tabla siguiente, queremos saber la cantidad de guantes vendidos en el mes de octubre.


En C15 escribimos el mes: Octubre

En C16 escribimos el nombre del producto: Guantes

Ahora, pondremos 6 fórmulas diferentes para hallar los guantes que se vendieron en octubre.

1ª fórmula: BUSCARV combinada con COINCIDIR

En C18:
=BUSCARV(C15;B2:F13;COINCIDIR(C16;B2:F2;0);FALSO)

2ª fórmula: BUSCARH combinada con COINCIDIR

En C19:
=BUSCARH(C16;B2:F13;COINCIDIR(C15;B2:B13;0))

3ª fórmula: INDICE combinada con COINCIDIR

En C20:
=INDICE(C3:F13;COINCIDIR(C15;B3:B13;0);COINCIDIR(C16;C2:F2;0))

4ª fórmula: SUMAPRODUCTO

En C21:
=SUMAPRODUCTO((B3:B13=C15)*(C2:F2=C16)*(C3:F13))

5ª fórmula (matricial): SUMA

En C22:
=SUMA((B3:B13=C15)*(C2:F2=C16)*(C3:F13))   [Terminar con Ctrl + Mayús + Intro]

6ª fórmula: DIRECCION, INDIRECTO y COINCIDIR

En C23:
=INDIRECTO(DIRECCION(COINCIDIR(C15;B1:B13;0);COINCIDIR(C16;A2:F2;0)))

Consideraciones: 

  • BUSCARV sólo se puede utilizar si la columna de los meses es la primera. Del mismo modo, BUSCARH requiere que la lista de productos esté en la primera fila. No se podrán utilizar en la tabla siguiente:

  • INDICE se puede utilizar en cualquier circunstancia.
  • SUMAPRODUCTO y SUMA sólo se pueden utilizar si la tabla contiene valores numéricos, ya que hemos hecho una operación matemática (que requiere números).
  • DIRECCION + INDIRECTO se pueden utilizar en cualquier circunstancia. Conviene recordar que INDIRECTO es una función volátil.

 
Descargar archivo (SE2-Seis Búsquedas.xlsx)

miércoles, 28 de diciembre de 2011

Acumular las compras por cliente

Los datos de compras de cinco clientes entre los años 2008 a 2011 han sido puestos en una hoja de cálculo en el rango B2:D24. Deseamos obtener las ventas totales de cada cliente y las acumuladas en cada año (F2:K7).


El primer paso consistirá, con ayuda de Filtro avanzado, en colocar la lista de clientes en la columna F. Para ello, seleccionamos B2:B24 y accedemos a Datos + Avanzadas.

Excel mostrará el cuadro de diálogo Filtro avanzado. Lo rellenamos indicando que queremos copiar, en otro lugar, sólo registros únicos. El lugar elegido será la celda F2.

El resultado será una lista desordenada.

Para ordenarla, seleccionamos F2:K7 y accedemos a Datos + Ordenar. En el cuadro de diálogo Ordenar, dejamos los valores por defecto y pulsamos Aceptar.

La lista habrá quedado ordenada por la columna Comprador.

Las ventas acumuladas de cada cliente (columna G) pueden obtenerse con distintas fórmulas: matriciales y no matriciales. Estudiaremos cuatro soluciones:

1ª solución (no matricial):  Con la función SUMAR.SI(rango;criterios;[rango_suma])

En G3:
=SUMAR.SI($B$3:$B$24;F3;$D$3:$D$24)

La función SUMAR.SI tiene tres argumento: el primero es el rango de datos que deben cumplir un criterio para que puedan ser admitidos; el segundo es el criterio propiamente dicho (los datos del primer argumento que no cumplan este criterio serán desechados); el tercero es el rango que se debe sumar si se cumple el criterio.

Extendemos la fórmula hasta la fila 7.

2ª solución (matricial): Con la función SUMA combinada con SI

En G3:
=SUMA(SI($B$3:$B$24=F3;$D$3:$D$24))     [Terminar con Ctrl + Mayúscula + Intro]

Únicamente se suman los datos de la columna D si en la misma fila de la columna B está el mismo dato que hay en F3.

Extendemos la fórmula hasta la fila 7.

3ª solución (matricial): Con la función SUMA

En G3:
=SUMA(($B$3:$B$24=F3)*($D$3:$D$24))     [Terminar con Ctrl + Mayúscula + Intro]

($B$3:$B$24=F3) devuelve una matriz de valores VERDADERO y FALSO. Lo comprobamos seleccionando M3:M24, escribiendo: =($B$3:$B$24=F3) y terminando con Ctrl + Mayúscula + Intro. A efectos numéricos, VERDADERO es 1 y FALSO es 0.

($B$3:$B$24=F3)*($D$3:$D$24) es el producto de dos matrices: una de VERDADEROS y FALSOS; la otra, es la matriz de la columna D. El resultado será una matriz de ceros (cuando el dato de la primera matriz es FALSO) y valores de la columna D (cuando es VERDADERO). Para comprobarlo, seleccionamos N3:N24, escribimos: =($B$3:$B$24=F3)*($D$3:$D$24) y terminamos con Ctrl + Mayúscula + Intro.

Finalmente, sumamos esta última columna con SUMA. Estamos trabajando con matrices, por lo que debemos terminar con Ctrl + Mayúscula + Intro. Sólo faltará extender la fórmula hasta la fila 7.

4ª solución (no matricial): Con la función SUMAPRODUCTO

En G3:
=SUMAPRODUCTO(($B$3:$B$24=F3)*($D$3:$D$24))

La fórmula es igual que la anterior, salvo que en vez de SUMA hemos puesto SUMAPRODUCTO. Esta función tiene la virtud de convertir fórmulas matriciales en no matriciales, por tanto, se termina con Intro. Como en los casos anteriores, extendemos la fórmula hasta la fila 7 para completar los totales acumulados de todos los compradores.

Las ventas por año de cada cliente (columnas H, I, J y K) también se pueden obtener de distintas maneras. Cualquiera de las fórmulas siguientes es válida:

En H3:

1ª fórmula:
=SUMAPRODUCTO(($B$3:$B$24=$F3)*(AÑO($C$3:$C$24)=H$2)*($D$3:$D$24))      [Pulsar Intro]

2ª fórmula:
=SUMA(SI($B$3:$B$24=$F3;SI(AÑO($C$3:$C$24)=H$2;$D$3:$D$24;0);0))     [Pulsar Ctrl + Mayús + Intro]

3ª fórmula:
=SUMA(($B$3:$B$24=$F3)*(AÑO($C$3:$C$24)=H$2)*($D$3:$D$24))     [Pulsar Ctrl + Mayús + Intro]

En los tres casos el resultado es cero ya que el primer cliente, Aitor, no ha realizado ninguna compra en el año 2008.

Extendiendo la fórmula al rango H3:K7 el ejercicio queda terminado. Se puede comprobar que, para cada comprador, la columna Acumulado es la suma de las compras realizadas durante los años 2008 a 2011.

Descargar archivo (SE1-Ventas acumuladas.xls)

Descargar archivo (SE1-Ventas acumuladas.xlsx)

lunes, 26 de diciembre de 2011

Días laborables

Si un operario tiene que realizar un trabajo durante 84 días laborables, empezando el 26 de diciembre de 2011, ¿qué día acabará, teniendo en cuenta que no trabaja los fines de semana ni los festivos?

El problema es muy sencillo de resolver utilizando la función DIA.LAB, cuya sintaxis es:

DIA.LAB(fecha_inicial;días_laborables;[festivos])

En nuestro ejemplo, la fecha_inicial sería el 26/12/2011; días_laborables, serían 84; festivos (opcional), sería la lista de días no laborables (excluidos los fines de semana) que hay entre diciembre de 2011 y mayo de 2012 (en ese intervalo hay más de 84 días). Poniéndolo todo en una hoja de cálculo quedaría así:

La fórmula de C4 es: =DIA.LAB(C2;C3;C6:C13)

Si no ponemos los datos en la hoja, utilizaremos la fórmula: =DIA.LAB("26/12/2011";84;{"6/12/2011";"8/12/2011";"6/1/2012";"5/4/2012";"6/4/2012";"9/4/2012";"30/4/2012";"1/5/2012"})

La función DIA.LAB considera no laborables los fines de semana y los festivos que explícitamente señalemos. 

Ahora, consideremos otro caso. El operario al que hemos hecho referencia trabaja los sábados pero no trabaja los domingos ni los lunes.

En este caso, no podemos utilizar la función DIA.LAB como lo hemos hecho en el ejemplo anterior. Afortunadamente, Microsoft ha introducido una nueva función en Excel 2010 para resolver este caso. La función se llama DIA.LAB.INTL y su sintaxis es:

DIA.LAB.INTL(fecha_inicial;días_laborables;[fin_de_semana];[festivos])

Como se observa, el tercer argumento permite elegir qué días se consideran fin de semana, según la tabla:

El argumento para nuestro caso es 2. Por tanto, la fórmula de C4 será: =DIA.LAB.INTL(C2;C3;2;C6:C13)

En vez de usar los números de la lista anterior, podemos poner una cifra de 7 ceros y unos. El cero indica que el día es laborable, mientras que el uno indica que es festivo. Se cuenta de lunes a domingo. De este modo, "1000001", indica que el lunes y el domingo son no laborables. Con este convenio, la fórmula de C4 queda así: =DIA.LAB.INTL(C2;C3;"1000001";C6:C13)

Lógicamente, el resultado no cambia.

Si sólo se trabaja los martes, jueves y sábados, la fórmula será: =DIA.LAB.INTL(C2;C3;"1010101";C6:C13)

"1111111" no es una cadena válida, ya que su uso implicaría que no se trabajase ningún día.

Planteemos otra situación. Un trabajo se ha empezado el día 20 de septiembre de 2011 y se ha terminado el 27 de diciembre de 2011. ¿Cuántos días se han trabajado? Naturalmente, no contaremos los fines de semana (que no se trabaja) ni los días festivos.

Como en los ejemplos anteriores hay una función para realizar este cálculo: DIAS.LAB

DIAS.LAB(fecha_inicial; fecha_final; [festivos])

La fecha_inicial es el día que se empezó el trabajo (20/09/2011); la fecha_final, el día que se terminó (27/12/2011); festivos son los días no laborables excluidos los sábados y domingos (que no se trabaja).

La fórmula de C4 debe ser: =DIAS.LAB(C2;C3;C6:C17)

En Excel 2010, también existe la función DIAS.LAB.INTL para el caso en que los fines de semana no sean el sábado y el domingo.

DIAS.LAB.INTL(fecha_inicial; fecha_final; [fin_de_semana]; [festivos])

Por ejemplo, si los días que no se trabaja son el jueves y el viernes, la fórmula será: =DIAS.LAB.INTL(C2;C3;"0001100";C6:C17)

Como jueves y viernes son dos días consecutivos, podemos utilizar el número 6 como tercer argumento de la función DIAS.LAB.INTL. En ese caso, la fórmula será: =DIAS.LAB.INTL(C2;C3;6;C6:C17)

viernes, 9 de diciembre de 2011

Funciones volátiles

En Excel las funciones pueden ser "volátiles" o "no volátiles". Las volátiles se recalculan cuando se abre el libro o cuando se modifica cualquier celda. Las no volátiles se recalculan, únicamente, cuando se modifica alguna celda que interviene en la fórmula donde se ha empleado la función. Veamos un ejemplo.

En A1 ponemos el formato "dd/mm/aaaa hh:mm:ss" y escribimos la fórmula: =AHORA()

Se mostrará la fecha y hora actuales; por ejemplo, 08/12/2011 17:03:41

Esperamos un rato y comprobamos que, a pesar de haber transcurrido un tiempo, la celda muestra el mismo valor. A continuación, escribimos algo en otra celda; la fecha y la hora se actualizan automáticamente. AHORA es una función volátil. Para asegurarnos, estando en A1, hacemos clic en el icono fx de la barra de fórmulas.

Excel muestra la siguiente ventana:

Veamos otro ejemplo basado en esta tabla:

En la celda A12 escribimos: =DESREF(A3;3;4;1;1)

Excel devolverá el valor que hay en la celda E6.

DESREF también es una función volátil y lo comprobaremos colocándonos en A12 y haciendo clic en fx. En este caso, la función tiene 5 argumentos y la ventana que muestra Excel es distinta:

Hagamos otra prueba. Guardamos el libro y cerramos Excel. A continuación, abrimos el mismo libro y volvemos a cerrarlo si hacer ninguna modificación. Excel preguntará si queremos guardar los cambios efectuados. La pregunta sorprenderá a los usuarios que ignoren que AHORA y DESREF son funciones volátiles. Al abrir el libro, Excel recalcula, sin consultar al usuario, las fórmulas en las que aparecen estas funciones, sustituyendo los valores antiguos por otros (que pueden coincidir); esto implica una modificación del libro, y de ahí que, al cerrarlo, Excel nos pregunte si queremos guardar los cambios efectuados.

Son volátiles las siguientes funciones:

ALEATORIO
AHORA
HOY
DESREF
INDIRECTO
INFO
CELDA("nombrearchivo")

Algunas funciones han dejado de ser volátiles al cambiar de versión.Por ejemplo, la función INDICE dejó de ser volátil a partir de Excel 97.

Según cómo se usen, algunas funciones pasan de ser volátiles a no serlo. Esto le ocurre, por ejemplo, a la función SUMAR.SI. En la hoja,

siempre que los valores de la columna A sean mayores o iguales a 15, sumaremos en D2 los correspondientes valores de la columna B. Si utilizamos la fórmula: =SUMAR.SI(A2:A5;">=15";B2:B5), Excel considerará que la función SUMAR.SI no es volátil. Por el contrario, si usamos la fórmula =SUMAR.SI(A2:A5;">=15";B2), que devuelve el mismo resultado, la función será considerada volátil.

En el último caso, Excel nos engaña, porque, al hacer clic en fx, muestra la ventana

en la que no se indica que la función sea volátil. Sin embargo, si guardamos el libro, salimos de Excel y, posteriormente, abrimos el libro y lo cerramos sin hacer cambios, Excel nos preguntará si queremos guardar los cambios efectuados, señal inequívoca de que ha recalculado la fórmula y, por consiguiente, ha considerado que SUMAR.SI es volátil.

jueves, 8 de diciembre de 2011

La función "N"

N es una función con un único argumento que devuelve un número o un error. Si el argumento es un número, devuelve el mismo número; si es una fecha o una hora, devuelve su número de serie; si es un valor lógico, devuelve 1 o 0; si es un error, devuelve el valor de error; en cualquier otro caso, devuelve cero. Probemos la función escribiendo los valores y fórmulas siguientes:

El resultado es:

La función no resulta muy útil que digamos. Microsoft dice que la mantiene por compatibilidad con otras hojas de cálculo. Puestos a buscarle utilidad, podemos emplearla para poner comentarios aclaratorios en algunas fórmulas. Por ejemplo, en la hoja siguiente,

el Coste total se calcula con la siguiente fórmula: =SUMA(B2:B6)*D2   [Resultado: 129.750,00 €]

Con la función N, podemos poner un comentario para indicar que el rango B2:B6 son los kilogramos vendidos en los cinco primeros meses del año. También podemos señalar que D2 contiene el precio por kilo. Lo haremos con esta fórmula: =SUMA(B2:B6;N("Ventas enero-mayo"))*(D2+N("Precio por kilo"))

A la SUMA le hemos añadido un cero y a D2, otro. El resultado no cambia.

miércoles, 7 de diciembre de 2011

Copiar el valor de una fórmula

Si en una celda tenemos una fórmula que puede ir cambiando a lo largo del tiempo y en un determinado momento queremos conservar el valor actual, tendremos que copiar ese valor en otra celda de la hoja. Podemos hacerlo de tres maneras: copiando manualmente el valor, utilizando Pegado especial o usando el truco que se explicará más adelante.

Hagamos una prueba.

Escribimos en A1 una fórmula cualquiera; por ejemplo, =5*ALEATORIO.ENTRE(1;50). Obtendremos un valor comprendido entre 5 y 250. Cada vez que pulsemos F9 o escribamos algo en otra celda, el valor cambiará porque ALEATORIO.ENTRE es una función volátil (en una próxima entrada escribiré sobre las funciones volátiles).

Vamos a copiar el valor actual de A1 en la celda D1. Para ello, nos situamos en A1 y pulsamos Ctrl + C. Hacemos clic con el botón derecho en D1 y, en el menú contextual, seleccionamos Valores (V).

También podemos seleccionar Pegado especial, marcar el botón de Valores y terminar pulsando Aceptar.

El siguiente procedimiento es más rápido. Hacemos clic en A1; colocamos el cursor en el borde de la celda hasta que se transforme en una cruz con cuatro flechas; con el botón derecho, arrastramos la celda hasta D1 y soltamos. En ese momento, se mostrará el menú siguiente:

Terminamos seleccionando Copiar aquí sólo como valores. Eso es todo.

Para sustituir la fórmula de A1 por su valor, arrastramos el borde con el botón derecho a D1 (o a otra celda) y, sin soltar el botón del ratón, volvemos a situarnos en A1. Al liberar el botón, se mostrará el menú de la figura anterior; elegimos Copiar aquí sólo como valores y la fórmula quedará sustituida por el valor actual.