Redondear en Excel

A menudo me preguntáis sobre las distintas opciones de redondeo que existen en Excel. En el siguiente post vamos a ver las diversas funciones existentes al respecto. En concreto vamos a utilizar las funciones REDONDEAR, REDONDEA.IMPAR, REDONDEA.PAR, REDONDEAR.MENOS, REDONDEAR.MAS, REDOND.MULT y TRUNCAR. Partimos del siguiente ejemplo:

REDONDEAR  

Sintaxis: REDONDEAR(número; núm_decimales)

Esta función redondea un número, en nuestro ejemplo los números existentes en el rango B5:B12, a un número de decimales especificado. Nos situamos en la celda C5 y escribimos la fórmula:

=REDONDEAR(B5;$C$2)

REDONDEA.IMPAR

Sintaxis: REDONDEA.IMPAR(número)

Redondea un número positivo hacia arriba y un número negativo hacia abajo hasta el próximo entero impar. Nos situamos en D5 y escribimos la siguiente fórmula:

=REDONDEA.IMPAR($B5)

REDONDEA.PAR
Sintaxis: REDONDEA.PAR(número)
Redondea un número positivo hacia arriba y un número negativo hacia abajo hasta el próximo entero par. Nos situamos en E5 y escribimos la siguiente fórmula:
=REDONDEA.PAR($B5)


En la imagen podemos observar el resultado de aplicar estas tres funciones:

REDONDEAR.MENOS

Sintaxis: REDONDEAR.MENOS(número; núm_decimales)

Redondea un número hacia abajo, en dirección hacia cero. La función REDONDEAR.MENOS es similar a la función REDONDEAR, excepto que siempre redondea un número acercándolo a cero. Nos situamos en la celda F5 y escribimos la siguiente fórmula:

=REDONDEAR.MENOS($B5;F$2)

REDONDEAR.MAS
Sintaxis: REDONDEAR.MAS(número; núm_decimales)
Redondea un número hacia arriba, en dirección contraria a cero. La función REDONDEAR.MAS es similar a la función REDONDEAR, excepto que siempre redondea al número superior más próximo, alejándolo de cero. Nos situamos en la celda G5 y escribimos la siguiente fórmula:
=REDONDEAR.MAS($B5;G$2)

REDOND.MULT

Sintaxis: REDOND.MULT(número; múltiplo)

Redondea un número al múltiplo deseado. REDOND.MULT redondea hacia valores positivos, hacia arriba, si el residuo de dividir número entre múltiplo es mayor o igual a la mitad del valor de múltiplo. Nos situamos en la celda H5 y escribimos la siguiente fórmula:

=REDOND.MULT($B5;H$2)

TRUNCAR

Sintaxis: TRUNCAR(número; [núm_decimales])

Trunca un número a un entero, suprimiendo la parte fraccionaria de dicho número. TRUNCAR y ENTERO son similares ya que ambos devuelven enteros. TRUNCAR suprime la parte fraccionaria del número. ENTERO redondea los números al entero menor más próximo, según el valor de la porción fraccionaria del número. ENTERO y TRUNCAR son diferentes solamente cuando se usan números negativos: TRUNCAR(-4,3) devuelve -4, pero ENTERO(-4,3) devuelve -5 porque -5 es el número entero menor más cercano.

Nos situamos en la celda I5 y escribimos la siguiente fórmula:

=TRUNCAR($B5;I$2)

En la imagen podemos observar el resultado de aplicar estas cuatro nuevas funciones:

Validación de Múltiplos

"Necesitamos que al realizar la introducción de cantidad en la Entrada de Datos sólo permita introducir cantidades múltiplos de un determinado número (por ejemplo 50)".

¡¡Lo primerísimo hoy es felicitar a mi maravillosísima mujercita que está de cumpleaños!!

Y ahora a trabajar un poquito que buena falta nos hace... Este problema se puede solucionar de diferentes maneras. Vamos  a resolverlo con dos fórmulas distintas:

1. Solución con Validación de Datos y la función REDOND.MULT

Partimos del siguiente ejemplo:

Lo que queremos es que excel nos permita introducir cantidades en C2 múltiplos de la indicada en E3. Para ello nos situamos en la celda C2 y vamos a la ficha Datos y seleccionamos Validación de datos. En Permitir seleccionamos Personalizada. En Fórmula escribimos la siguiente:

=C2=REDOND.MULT(C2;E3)

Pulsamos Aceptar y ya está. A partir de este momento, y como se puede ver en las siguientes imágenes, excel sólo permitirá introducir en C2 números múltiplos de aquel que indiquemos en la celda E3. En nuestro ejemplo sólo permitirá múltiplos de 50, esto es, 50, 100, 150, 200, etcétera.

La función REDOND.MULT redondea un número al múltiplo deseado. Su sintaxis es REDOND.MULT(numero;multiplo) donde numero es el argumento del valor que se quiere redondear y multiplo el argumento del múltiplo al que se quiere redondear el número.

2. Solución con Validación de Datos y la función RESIDUO 

Seguimos exactamente los mismos pasos pero la fórmula que escribimos en la Validación de datos es:

=RESIDUO(C2;E3)=0

La sintaxis de la función RESIDUO es RESIDUO(número; núm_divisor) Esta función devuelve el residuo o resto de la división entre número y núm_divisor. Cuando un número es múltiplo de otro, al dividirlos su resto o residuo es cero, que es precisamente lo que contrastamos con la fórmula propuesta.

Obtener Nombre y Extensión del Archivo

"Necesito realizar, si es posible, una fórmula que me ponga en una celda el nombre y extensión del archivo con el que estoy trabajando".

Sí, es posible. Para la resolución de este problema vamos a utilizar tres funciones de excel, a saber: CELDA, ENCONTRAR y EXTRAE.

Lo primero que debemos hacer es darle nombre al archivo y guardarlo. En nuestro caso bautizaremos el archivo con la denominación Minombre. A continuación nos ponemos en una celda, por ejemplo en B2, y escribimos la fórmula:

=CELDA("nombrearchivo")

La función CELDA devuelve información acerca del formato, la ubicación o el contenido de una celda. La sintaxis de esta función es CELDA( tipo_de_info;referencia), tal y como muestra la ayuda de excel, y tiene los siguientes argumentos:

tipo_de_info: Es un valor de texto que especifica el tipo de información de la celda que se desea obtener. La siguiente lista muestra los posibles valores del argumento de tipo_de_info y los correspondientes resultados:

tipo_de_info	Devuelve
"DIRECCION"	la referencia, en forma de texto, de la primera celda del argumento ref.
"COLUMNA"	El número de columna de la celda del argumento ref.
"COLOR"	Valor 1 si la celda tiene formato de color para los valores negativos; de lo contrario, devuelve 0 (cero).
"CONTENIDO"	Valor de la celda superior izquierda de la referencia, no una fórmula.
"ARCHIVO"	Nombre del archivo (incluida la ruta de acceso completa) que contiene la referencia, en forma de texto. Devuelve texto vacío ("") si todavía no se ha guardado la hoja de cálculo que contiene la referencia.
"FORMATO"	Un valor de texto correspondiente al formato numérico de la celda. Los valores de texto para los distintos formatos se muestran en la siguiente tabla. Si la celda tiene formato de color para los números negativos, devuelve "-" al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve "()" al final del valor de texto.
"PARENTESIS"	Valor 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores; de lo contrario, devuelve 0 (cero).
"PREFIJO"	Un valor de texto que corresponde al "prefijo de rótulo" de la celda. Devuelve un apóstrofo (') si la celda contiene texto alineado a la izquierda, comillas (") si la celda contiene texto alineado a la derecha, un acento circunflejo (^) si el texto de la celda está centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío ("") si la celda contiene otro valor.
"PROTEGER"	Valor 0 (cero) si la celda no está bloqueada; de lo contrario, devuelve 1 si la celda está bloqueada.
"FILA"	El número de fila de la celda del argumento ref.
"TIPO"	Un valor de texto que corresponde al tipo de datos de la celda. Devolverá "b" (para blanco) si la celda está vacía, "r" (para rótulo) si la celda contiene una constante de texto y "v" (para valor) si la celda contiene otro valor.
"ANCHO"	El ancho de columna de la celda redondeado a un entero. Cada unidad del ancho de columna es igual al ancho de un carácter en el tamaño de fuente predeterminado.

referencia: (argumento opcional) La celda sobre la que desea información. Si se omite, se devuelve la información especificada en el argumento tipo_de_info para la última celda cambiada. Si el argumento de referencia es un rango de celdas, la función CELDA devuelve la información sólo para la celda superior izquierda del rango.

Importante: Aunque la ayuda de excel nos indica que para obtener el nombre del archivo debemos utilizar el argumento "ARCHIVO" esto es incorrecto. Debemos utilizar el argumento "NOMBREARCHIVO".

Volviendo a nuestro ejemplo, tras introducir la citada fórmula en la celda B2 obtendremos la ruta y nombre del archivo en cuestión. En mi caso el resultado es:

Ahora tenemos que conseguir "limpiar" el nombre y la extensión del archivo. Para ello utilizaremos la función ENCONTRAR. Nos situamos en la celda B3 y escribimos:

=ENCONTRAR("[";B2) que es el corchete de apertura

y en la celda B4:

=ENCONTRAR("]";B2) que es el corchete de cierre

La función ENCONTRAR busca un texto o cadena de texto especificado dentro de una segunda cadena y devuelve el número de la posición inicial de la primera cadena de texto. Su sintaxis es ENCONTRAR(texto_buscado; dentro_del_texto; [núm_inicial]):

Texto_buscado Obligatorio. El texto que desea encontrar.

Dentro_del_texto Obligatorio. El texto en el que se quiere buscar.
Núm_inicial Opcional. Especifica el carácter a partir del cual comenzará la búsqueda. El primer carácter de dentro_del_texto es el carácter número 1. Si omite núm_inicial, se supone que es 1.

Con estas dos fórmulas obtenemos la posición en la que se encuentra el corchete de apertura y cierre que delimita el nombre y extensión de un archivo de excel:

De esta manera ya sólo nos queda aplicar ahora la función EXTRAE para obtener lo que queremos. Resolviéndolo todo en una única fórmula quedaría así:

=EXTRAE(B2;ENCONTRAR("[";B2)+1;ENCONTRAR("]";B2)-B3-1)

Asignar % Aleatorio que Sume 100%

"Necesito generar números aleatorios en 5 columnas y que la suma de cada fila resulte siempre el 100%".

Esta pregunta es bastante recurrente así que, aunque la solución no tiene mucho misterio, paso a resolverla. Empezamos planteando la tabla:

Nos situamos en la celda C13, seleccionamos el rango C13:G20 e introducimos la fórmula para generar los números aleatorios, esto es:

=ALEATORIO( ) acabamos pulsando Ctrl + Enter para generar de una sola vez la matriz:

Nos ponemos en la celda C3 y escribimos la fórmula:

=C13/SUMA($C13:$G13)

Copiamos esta fórmula hasta G3 y finalmente hasta G20 y... problema resuelto:

Asignación Aleatoria por Filas

Descargar Archivo

"Quiero realizar la asignación aleatoria de un número de anuncios determinado de distintas empresas en una parrilla dispuesta por bloques como aparece en la imagen:

De tal manera que la suma de los 5 bloques de cada anunciante debe resultar el número de anuncios contratados por cada empresa".

Pues nos ponemos manos a la obra. Empezamos creando la correspondiente tabla en nuestra hoja, a saber:

A continuación seleccionamos el rango E17:I23 y con dicho rango seleccionado escribimos la fórmula:

=ALEATORIO() y pulsamos Ctrl + Enter (así rellenamos todo el rango de una sola vez):

Nos situamos ahora en E5. Para que se entienda mejor vamos a realizar una primera fórmula que no es la definitiva. Escribimos (en E5) la fórmula:

=JERARQUIA(E17;$E17:$I17)

Copiamos dicha fórmula para toda la tabla (rango E5:I11). De esta manera conseguimos ordenar del 1 al 5 los resultados aleatoriamente obtenidos:

Ahora ya sólo nos queda aprovechar aquellos valores de orden que sean igual o inferiores al número de anuncios contratados por cada empresa. Por ejemplo, para el Balneario de Mondariz, que ha contratado 3, nos interesarán los valores 1, 2 y 3. A partir de aquí utilizaremos el formato condicional y la función SI para dar el formato final. Nos situamos nuevamente en la celda E5 y escribimos la fórmula:

=SI(JERARQUIA(E17;$E17:$I17)>$C5;"";1)

Nuevamente copiamos esta fórmula para el rango E5:I11. Al añadir el condicional si el número aleatorio es superior al número de anuncios, excel dejará la celda correspondiente al bloque en blanco. Pero si el número aleatorio obtenido es igual o inferior al número de anuncios contratados entonces escribirá un 1 en la celda del bloque correspondiente:

Ya sólo queda aplicar formato condicional para rematar la tarea. Seleccionamos el rango E5:I11. Dentro de la ficha Inicio vamos al módulo de Estilos y pulsamos el icono de Formato Condicional. De la lista que se abre seleccionamos nueva regla. En la ventana que se abre seleccionamos Utilice una fórmula que determine las celdas para aplicar formato (la última opción de la lista). Nos situamos en Dar formato a los valores donde esta fórmula sea verdadera y escribimos la siguiente fórmula: =1

Acabamos pulsando el Botón Formato. Ahora seleccionaremos el formato con el que queremos que excel resalte los bloques en los que sí hay anuncio. En nuestro ejemplo le pediremos relleno azul y color de fuente blanco y negrita. Aceptamos y...

Cada vez que se recalcule la hoja o que pulsemos la tecla F9 excel generará nuevos números aleatorios y, en consecuencia, un nuevo mapa como se puede ver a continuación en varios ejemplos:

Evidentemente también podemos modificar el número de anuncios contratados por cada empresa y el modelo seguirá funcionando correctamente (obviamente con un máximo, en este ejemplo, de 5 anuncios por empresa).