La función RESIDUO, otra forma de comprobar si un número es divisible de otro dado.

En una entrada anterior comprobamos en qué forma podíamos verificar que un valor era divisible por otro (ver). En este post aprenderemos como realizar la misma operación, esto es, cómo comprobar si un número es divisible de otro dado utilizando para ello la función RESIDUO.
Veamos la sintaxis de la función:
=RESIDUO(número; divisor)
Al igual que en el ejemplo anterior partimos de un listado de valores, de los que deseamos conocer cuáles son divisibles por 7:

La función para cada valor es entonces:
=RESIDUO(valor;7)
sabiendo que esta función devuelve el resto del cociente entre el valor y el divisor, concluimos que el número a evaluar será divisible por 7 cuando el resto o RESIDUO sea cero.
Situación que se confirma para los valores múltiplos de 7 que aparecen en nuestro listado (7, 14, 21, 28, 42, 49 y 70).
En la columna H de nuestro ejemplo, sólo hemos anidado este resultado en un condicional para que aparezca el texto 'Divisible' o 'No':
=SI(RESIDUO(A2;7)=0;"Divisible";"No").

Seguir leyendo...

Cómo comprobar si un número es divisible de otro dado.

Hoy analizaremos una utilidad de las funciones MULTIPLO.INFERIOR y MULTIPLO.SUPERIOR, usando ambas para comprobar si un valor es divisible por otro.
Conozcamos, antes de continuar, cuál es la sintáxis de ambas:

• MULTIPLO.INFERIOR(número; cifra_significativa)


• MULTIPLO.SUPERIOR(número; cifra_significativa)

para ambas funciones el significado de sus argumentos será:
número: es el valor numérico que se desea redondear;
cifra significativa: es el múltiplo al que se desea redondear.
esto es, para un valor determinado la cifra significativa es el valor o múltiplo de este al que nos acercará nuestra función.
Bien, no nos dispersemos, se trata de averiguar si un número es divisible de otro.
Disponemos de un listado de valores, al azar, del que deseamos discernir si son divisibles por 7.

Analicemos el significado de estas fórmulas. Por ejemplo, para el primer valor (celda A2 = 7), al aplicar la función MULTIPLO.INFERIOR vemos que devuelve el valor 7 al igual que aplicar la función MULTIPLO.SUPERIOR, lógico, ya que el múltiplo de 7 más cercano por arriba-superior y por debajo-inferior es él mismo. Para un valor de 17, el múltiplo de 7 más cercano a 17 si llegar a éste sería 14, y por encima alcanzaríamos el 21.
Una vez visto este punto claro, la consecuencia para resolver nuestra cuestión, parece clara, un valor será divisible exactamente por 7, cuando ambas funciones MULTIPLO.INFERIOR y MULTIPLO.SUPERIOR tengan el mismo valor; es decir, cuando el resultado de la prueba lógica
=MULTIPLO.INFERIOR(A2;7)=MULTIPLO.SUPERIOR(A2;7)
sea VERDADERO.

Seguir leyendo...

BUSCARV como argumento en una función SI.

Habitualmente trabajamos con la función BUSCARV pensando sólo en búsquedas verticales, olvidando otros usos o utilidades; en este ejemplo de hoy veremos como nos aprovecharemos de ésta, para anidarla en una función SI condicional, como parte de la prueba lógica.
En el ejemplo que trataré hoy buscaremos una respuesta al siguiente problema. Si partimos de una tabla donde vemos para diferentes elementos qué meses están operativos, en concreto tenemos un listado de cruceros y el intervalo de meses entre los cuales, dicho crucero, está operativo; para un listado de posibles reservas atendiendo a un crucero determinado y a una fecha de embarque, tendremos que obtener una SI o un NO que determine si para dicha Fecha y Crucero tal reserva es posible.
Vemos nuestras tablas de trabajo:

haz click en la imagen

La finalidad es combinar ambos requisitos solicitados en una única prueba lógica de nuestra función condicional SI, de tal forma que devuelva un SI o un NO si se verificase o no.
Debemos construir un condición que nos diga si para un Crucero concreto la fecha de reserva propuesta cumple o está dentro del intervalo de meses definido (rango E2:G5).
Esto lo conseguiremos incluyendo la función Y como prueba lógica, de tal forma que en esta unimos dos de las condiciones a cumplir, para cada Fecha de reserva sea mayor o igual que el primer mes de inicio de operaciones y que sea menor o igual que el último mes operativo del crucero:
Y(Fecha reserva>=Fecha inicio operaciones; Fecha reserva<=Fecha fin operaciones)
Como estamos trabajando con meses, trataremos los datos con la función MES.
La clave, en todo caso de esto, es determinar correctamente cuál es la Fecha de inicio y fin de operaciones para cada Crucero a reservar. Esto lo conseguiremos con la función BUSCARV, siendo las condiciones finales:
MES(Fecha reserva)>=BUSCARV(Crucero;$E$2:$G$5;2;0)
MES(Fecha reserva)<=BUSCARV(Crucero;$E$2:$G$5;3;0)
todo unido en una sóla función, y tomando las celdas a evaluar para el primer registro, tenemos la siguiente Prueba lógica:
Y(MES(B2)>=BUSCARV(A2;$E$2:$G$5;2;0);MES(B2)<=BUSCARV(A2;$E$2:$G$5;3;0))

Seguir leyendo...

Encontrar fechas con matriciales.

Hoy recuperaremos nuestra matriciales para encontrar un dato dentro de una Tabla que cumpla varios requisitos al tiempo. Se trata de localizar la primera fecha que corresponda al último trabajo desarrollado por cada empleado de un listado:

...Imaginemos que tenemos una lista de Nombres, (en el ejemplo pongo dos, Pepe (25) y Paco (50) pero pueden ser muchos más). Todos ellos tienen un histórico con los puestos que han ocupado en sus trabajos, con la fecha de inicio y los centros en los que han trabajado. Lo que yo necesito es, (y está en rojo). Sacar un listado con el nombre y LA FECHA EN LA QUE EMPEZÓ EN SU ÚLTIMO PUESTO. Por ejemplo; Pepe es Cocinero desde 01/05/2004 y Paco es arquitecto desde 01/04/2007. Necesito una fórmula que discrimine el resto de fechas y solo tenga en cuenta la fecha en la que comenzaron su trabajo y sigan actualmente en el mismo....

Asignamos nombres a los diferentes campos del listado:
centro =Hoja1!$E$2:$E$12
Fecha =Hoja1!$D$2:$D$12
ID =Hoja1!$A$2:$A$12
Nombre =Hoja1!$B$2:$B$12
Profesion =Hoja1!$C$2:$C$12
Fijémosnos en que las condiciones son tres, la primera por ID de empleado, la segunda por Profesión desarrollada y la tercera que deberá ser la menor de las fechas de la última Profesión desarrollada.
La clave de la función matricial que vamos a implementar es encontrar en primer lugar la última Profesión desarrollada de cada empleado, esto lo lograríamos con una matricial (Ctrl+Mayus+enter) de este tipo:
{=MAX(SI(ID=G2;Fecha))}
que nos dice la última Fecha en la que comenzó alguna Profesión; si anidamos esta Fecha en una función INDICE obtendremos la última Profesión:
{=INDICE(Profesion;COINCIDIR(MAX(SI(ID=G2;Fecha));Fecha;0))}
para el empleado con ID igual al valor de la celda G2, buscamos la última fecha, para luego encontrar la correspondencia sobre el rango Profesión.
Acabamos con la matricial:
{=MIN(SI(Profesion=INDICE(Profesion;COINCIDIR(MAX(SI(ID=G2;Fecha));Fecha;0));Fecha))}

Seguir leyendo...

Validación de datos personalizada.

Solucinaremos el problema de un lector con la Validación de datos de una celda formulada, o lo que es lo mismo con una Validación de datos personalizada:

...estoy trabajando en una hoja de cálculo y pretendo que en una casilla me sume un rango de celdas determinado, hasta aquí facil y sin problema; peró además he intentado introducir en esta casilla una regla de validación mediante la cual si se sobrepasa una cantidad fijada en la regla me salga un mensaje de error y no consigo que funcione, si introduzco directamente una cantidad superior a la permitida en la casilla si que sale el aviso, peró mediante la suma no, no se si es que no permite esta función o si debo hacerlo medianta algun otro sistema....

El problema es claro, en una celda tenemos introducida un fórmula de SUMA sobre otro rango de celdas, y pretendemos que la primera está validada, permitiendo sólo determinado rango de valores; sin embargo, Excel, con esta herramienta de Validación de datos sólo restringe la introducción directa de datos.
¿Cómo solucionar este inconveniente?, atacaremos por detrás a la celda a restringir.
Lo vemos con un sencillo ejemplo como siempre.
Tenemos un rango A1:C1 con diferentes valores, que sumaremos en D1. Será la celda D1 donde sumaremos el rango anterior, esto es, en D1:
=SUMA(A1:C1)
es precisamente la celda D1 la que queremos limitar entre un rango de valores, por ejemplo, que esté entre 5 y 8, es decir, pretendemos que la suma del rango A1:C1 quede siempre dentro del intervalo 5 y 8.

Seleccionamos el rango de celdas A1:C1, y desde la Ficha Datos > Herramientas de datos > Validación de datos

Seguir leyendo...

Informe de gráfico dinámico según elemento.

Tiempo atrás expliqué diferentes formas, con y sin macros, de mostrar series de un gráfico con diferentes formatos (ejemplo 1 y ejemplo 2).
En esta ocasión conseguiremos un efecto similar, empleando el Informe de gráfico dinámico.
En concreto se trata de distinguir uno de los elementos de una serie del origen de datos, elegido por nosotros mediante una celda validada.
Nuestros datos de partida están en la Tabla siguiente:

Vemos como se trata de un listado de registros, en los que para cada uno de ellos nos aporta información del producto vendido (pdto1, pdto2 ó pdto3) a un cliente concreto (clienteA, clienteB ó clienteC) por una cantidad.
Queremos obtener un gráfico de barras que muestre resumido el volumen de pedidos, marcando de otro color al cliente seleccionado o elegido por nosotros.
Para ello, comenzamos configurando una Celda validada tipo lista con los tres clientes, por ejemplo en la celda G2.
También añadiremos a nuestra 'Tabla' de datos dos columnas auxiliares (una con los datos del cliente seleccionado y otra para el resto de clientes). Como estamos trabajando con 'Tablas' la fórmula de nuestras columnas auxiliares serán:
importeCliente: =SI(Tabla1[[#Esta fila];[Clientes]]=$G$2;Tabla1[[#Esta fila];[importes]];"")
importeResto: =SI(Tabla1[[#Esta fila];[Clientes]]=$G$2;"";Tabla1[[#Esta fila];[importes]])
éstas son simplemente condicionales que devuelven el importe si el código de 'Clientes' coincide con el valor de la celda G2, es decir, con el cliente seleccionado.

Seguir leyendo...

Asignar un nombre a una fórmula en Excel.

Sí, has leído bien, no se trata de Asignar un nombre a un rango, si no de Asignar un nombre a una fórmula. La entrada de hoy explicará cuáles son las ventajas de dar nombres a nuestras fórmulas.
Como siempre, veámoslo con un ejemplo.
Tenemos una tabla de categorías con un valor correspondiente; y sobre estos valores tenemos un listado de 15 registros aleatorios:

Sobre este listado aleatorio encontraremos:

• Cálculo del minimo valor acumulado por categoría(sin contar el valor cero)


• En qué Categoría la suma de sus Valores fue mínimo(sin contar el valor cero)

Para responder ambas cuestiones, partiremos del mismo trabajo.
En primer lugar, por comodidad Asignaremos un nombre a los rangos del listado a evaluar:
Categoría =Hoja1!$D$3:$D$17
Valor =Hoja1!$E$3:$E$17

Ahora ya podemos construir una fórmula matricial a la que asignaremos un nombre.
Una para cada 'Categoría':
Cat_a =SUMA(SI(Categoría="a";Valor))
Cat_b =SUMA(SI(Categoría="b";Valor))
Cat_c =SUMA(SI(Categoría="c";Valor))
Cat_d =SUMA(SI(Categoría="d";Valor))
Cat_e =SUMA(SI(Categoría="e";Valor))
Cat_f =SUMA(SI(Categoría="f";Valor))

Seguir leyendo...

BUSCARV y COINCIDIR: una búsqueda cruzada.

Estamos acostumbrados a trabajar con la función BUSCARV pensando sólo en búsquedas verticales, digamos en una sóla dimensión, debiendo hacer uso de otras funciones, como DESREF para otros tipos de búsquedas; sin embargo, veremos hoy como combinándola con la función COINCIDIR podremos encontrar nuestro valor buscado sobre las dos dimensiones de nuestras tablas.
Nuestro ejemplo será muy sencillo, pero representativo. Necesitamos obtener un valor en función de dos variables ('Categoría hotel' y 'Tipo habitación'); ambos parámetros cruzan en una tabla, de la que obtendremos el precio para cada combinación posible.
Veámoslo:

haz click en la imagen

Se trata por tanto, en base a los valores de las columnas A (Categoría) y B (Tipo habitación), determinar cuál es el precio de la habitación.
Nos aprovecharemos de la disposición de valores en la tabla de precios (E1:I8), donde por filas podemos reconocer la 'Categoría' del hotel y por columnas el 'Tipo de habitación'; con la función
COINCIDIR(B2;$F$2:$I$2;0)+1
obtendremos el número de columna coincidente con el Tipo de habitación, que emplearemos con argumento Indicador de columna de la función BUSCARV.
Si la anidamos, tenemos
=BUSCARV(A2;$E$3:$I$8;COINCIDIR(B2;$F$2:$I$2;0)+1;0)
es decir, buscamos en la matriz o tabla E3:I8, el valor de la celda A2 (esto es, la Categoría del hotel), y buscamos su precio en el número de columna obtenido con la función COINCIDIR.

Seguir leyendo...

Conciliar una partida con Solver.

Vamos a ver cómo podemos encontrar, de un listado de valores, aquellos que suman una cantidad definida por nosotros; esto es, vamos a conciliar una partida con un listado de valores. Para esto emplearemos Solver.
Si bien, hay que advertir que esta aplicación será válida sólo en aquellos casos que la combinación de valores que suman el valor a conciliar (el valor buscado) es única.
Veamos nuestro listado de valores:

Convertiremos nuestro listado en una Tabla (Ctrl+q), y luego con la Tabla seleccionada, Insertaremos una columna de tabla a la derecha, que nombraremos como 'binario'.
También asignaremos nombre a los dos campos:
binario =Hoja1!$B$2:$B$17
Cantidades =Hoja1!$A$2:$A$17
Por último, como parte importante a la hora de configurar Solver, en la celda D2 insertaremos la función:
=SUMAPRODUCTO(binario;Cantidades)
esta función se encargará de multiplicar fila a fila, elemento a elemento, los valores del rango 'binario' y del rango 'Cantidades'.
Esta celda, será considerada en la configuración como la Celda objetivo.
Ejecutamos Solver, desde Datos > Análisis > Solver:

Seguir leyendo...

Cargar el complemento Solver en Excel.

Pocos días atrás un lector, a través del correo, me pregutaba por qué a él no le aparecía en la ficha Datos > Análisis el complemento Solver.
Bueno, el problema de ciertos complementos de Excel, entre ellos Solver, es que aunque en el proceso de Instalación de Microsoft-Office quedan instalados, no quedan cargados, esto es, quedan ocultos.
Aprovecharé esta ocasión para explicar el proceso de carga o instalación de este complemento.
Lo primero que haremos será pulsar el botón de Office y a continuación, abajo a la derecha, en el botón de Opciones de Excel...

Buscaremos en el menú de la izquierda Complementos, y una vez dentro de éste, buscaremos en la parte baja de la ventana Administrar: Complementos de Excel, pulsaremos Ir, apareciéndonos una ventana con un listado de complementos, entre los que se encuentra Solver; seleccionaremos los que nos interese:

Seguir leyendo...