viernes, 27 de febrero de 2015

Evitar Códigos Duplicados en Varias Columnas

"Tengo una tabla con códigos en las columnas A y C, y quiero introducir nuevos códigos en la columna B, pero evitando que se puedan repetir los que ya se encuentran en dichas dos columnas y que, por otro lado, tampoco se puedan repetir los nuevos que voy introduciendo en la columna B".

Partimos del siguiente ejemplo:
Lo primero que hacemos es crear los nombres de las tres columnas de esta tabla. Vamos a la ficha Fórmulas y en el grupo Nombres definidos seleccionamos Crear desde la selección y marcamos Fila superior
Ya hemos generado los tres nombres:
Seleccionamos ahora el rango B3:B16 y vamos a la ficha Datos/Validación de datos. Como criterio de validación permitimos Personalizar, y en fórmula escribimos la siguiente:

=Y(CONTAR.SI(CodigoA;B4)=0;CONTAR.SI(CodigoC;B4)=0;CONTAR.SI($B$4:B4;B4)=1)

Una vez aceptemos, excel verificará, para cada una de estas celdas (B4:B16), que los valores que vayamos introduciendo no se encuentran repetidos en las columnas A y C 
=Y(CONTAR.SI(CodigoA;B4)=0;CONTAR.SI(CodigoC;B4)=0     y, además, verifica que no se duplican los nuevos valores que vayamos introduciendo en la columna B    CONTAR.SI($B$4:B4;B4)=1)

En caso de que se repita alguno de los códigos introducidos aparecerá un mensaje de error que nos obligará a realizar una nueva entrada correcta:

viernes, 30 de enero de 2015

Selección Aleatoria de un Valor de un Rango (2)

"Necesito seleccionar aleatoriamente un número de un rango determinado. He visto una solución en tu blog en el post Seleccionar Aleatoriamente un Valor de un Conjunto. El problema que me encuentro es que si el rango en cuestión contiene celdas en blanco, entonces, la solución que propones devuelve valores cero. Me gustaría saber si existe la forma de que evite dichas celdas en blanco y elija un valor entre aquellas que contienen número".

Para solucionar esta variación sobre el caso visto en el post Seleccionar Aleatoriamente un Valor de un Conjuntovamos a generar una tabla auxiliar que nos permita "apartar" las celdas que se encuentran vacías para que no se puedan generar valores cero en el resultado. Partimos del siguiente ejemplo:
Queremos obtener aleatoriamente un valor de esta lista pero sin considerar las celdas en blanco. En la solución proporcionada en la primera versión de este problema, generamos un número aleatorio de posición para que excel me devuelva el valor existente en dicha celda. Es decir, si mi lista tiene 20 valores, genero un número aleatorio entre el 1 y el 20 y le pido a excel que vaya a la celda del número obtenido, por ejemplo la 4, y me devuelva el valor de dicha celda. Aplicando dicha solución a esta lista, nos podríamos encontrar con que en la cuarta celda de la lista la celda esté vacía (obtendríamos valor cero).

Lo primero que hacemos es seleccionar el rango B5:B18 y le damos el nombre de Valores. A continuación generamos una nueva lista auxiliar de dos columnas, cuya primera columna es una serie de número de orden de menor a mayor (en este caso del 1 al 14, ya que tenemos 14 datos):
Nos situamos en la celda G5 y escribimos la fórmula:
=K.ESIMO.MAYOR(valores;F5)  y copiamos hasta G18
De esta manera hemos conseguido generar una nueva lista ordenada de mayor a menor con los valores de nuestra lista original pero ahora ya no tenemos celdas en blanco por el medio del rango, ya que hemos conseguido que se "vayan al final" de nuestra nueva lista y que se muestren como error del tipo #¡NUM!
Esto nos permite ahora, aplicando la función CONTAR, calcular cuántas celdas de mi nueva lista contienen un valor númerico. Si calculamos  =CONTAR(G5:G18) nos devolverá el resultado 10, porque es el número de valores existentes en dicho rango de 14 celdas (las otras 4 contienen el error #¡NUM!). De esta manera ya sé que debo generar un número aleatorio entre 1 y 10. Por seguir trabajando con nombres de rango, seleccionamos G5:G18 y le creamos el nombre NewLista.
Nos situamos en la celda D10 y escribimos la fórmula definitiva:
=DESREF(G4;ALEATORIO.ENTRE(1;CONTAR(NewLista));)
Cada vez que pulsemos la tecla F9, excel recalculará un valor aleatorio y lo mostrará en la celda D10. Una fórmula alternativa en D10 sería utilizar la función INDICE:
=INDICE(NewLista;ALEATORIO.ENTRE(1;CONTAR(NewLista))) 

viernes, 23 de enero de 2015

Intercalar 1 Fila en Blanco cada n Filas (sin macros)

"Necesito dejar una fila en blanco cada 4 filas, es decir, que aparezcan 4 registros y en el quinto que incorpore una fila en blanco, y así sucesivamente hasta un total de 5.000 registros ¿Hay alguna manera de hacerlo sin hacer uso de macros?".

Para evitar solucionarlo de manera manual, lo que nos llevaría bastante tiempo, existe una manera razonablemente sencilla y razonablemente automática y que no requiere de programación. Partimos de una tabla con distintos datos. En nuestro ejemplo utilizaremos 50 registros:
Vamos a manejar dos hojas dentro del archivo para mayor seguridad. La tabla de la imagen la tenemos en la hoja 2. Lo primero que debemos hacer es crear un número de orden para los registros. Para ello escribimos un 1 en A3, un 2 en A4 y seleccionamos ambos (A3:A4). Hacemos doble clic en la parte inferior derecha del rango seleccionado y de esta manera rellenamos la serie hasta el último dato. 
Nos vamos ahora a la HOJA1. Preparamos la siguiente entrada de datos:
En D1 introducimos el número de fila que debe quedar en blanco. Si queremos dejar en blanco la quinta fila de nuestra tabla escribiremos un 5. En D2 introducimos el valor del último dato de nuestra tabla de la HOJA2. En nuestro ejemplo hay 50 registros y, por lo tanto, el valor será 50.
En E4 vamos a calcular cuántas filas se van a insertar en total. La fórmula es:
=SI(RESIDUO(D2;D1-1)=0;(D2/(D1-1))-1;TRUNCAR(D2/(D1-1)))

En E5 calculamos cuál será el último número de nuestra nueva lista. La fórmula es:
=E4+D2  es decir, el número de filas inicial más las que se van a insertar.

Ahora a partir de la celda C8 generamos nuevamente la serie comenzando con el número 1 y acabando con el 50 (igual que hicimos en la hoja 2).
En D8 escribimos un 1. En D9 la siguiente fórmula:
=SI(RESIDUO(D8+1;$D$1)=0;D8+2;D8+1)  y hacemos doble clic para completar la Nueva Lista.
En E8 la siguiente fórmula:
=SI($D$1*C8<=$E$5;$D$1*C8;"- - -")   y hacemos doble clic para completar la Lista de Múltiplos. El resultado será el siguiente:

Copiamos la Nueva Lista y la pegamos COMO VALORES en la HOJA2 encima del rango A3:A52  y obtenemos lo siguiente:

Ya estamos terminando... Ahora volvemos a la hoja 1 y seleccionamos el rango que contiene números en nuestra Lista de Múltiplos. En nuestro caso el rango E8:E19 de la HOJA1. Pulsamos Copiar y vamos a la HOJA2 y lo pegamos COMO VALORES a continuación del último dato, es decir, a partir de la fila 53.

Ya sólo nos queda ir a Ordenar y filtrar y seleccionar Orden Personalizado. En la ventana que se abre seleccionamos Ordenar por la Columna A de menor a mayor:
Aceptamos y... problema resuelto! (podríamos ahora borrar los número de orden generados en la hoja2 en la columna A):

Aunque pueda parecer un poco tedioso, una vez realizada la plantilla no lleva más de 30 segundos resolver cada caso que se nos plantee.

jueves, 22 de enero de 2015

Copiar Registros Intercalados

"Tengo cientos de registros relativos a números de unidades vendidas y debajo de cada registro el porcentaje que representa cada uno respecto a la suma total. Necesito copiar un listado pero sólo de las unidades vendidas sin dejar filas intercalas en el medio". 

Tal y como les prometí, este post va dedicado a mis alumnos del Master in Management del IE Business School, que con tanta paciencia me soportan los lunes y martes...
Partimos del siguiente ejemplo:

Lo que queremos conseguir es hacer una sola fórmula que podamos copiar hacia abajo para obtener un listado como el que aparece en la siguiente imagen con las cantidades de cada dato:
Para ello vamos a utilizar varias funciones, a saber: INDIRECTO, DIRECCION, FILA y COLUMNA.
Nos situamos en la celda G4 y escribimos la siguiente fórmula que copiaremos hasta G11:

=INDIRECTO(DIRECCION(FILA(C4)*2-4;COLUMNA(C4)))

La función FILA nos devuelve el número de fila de la celda en cuestión. En nuestro ejemplo FILA(C4) nos devolverá el valor 4. multiplicamos el resultado por 2 para generar una serie de números pares. FILA(C4)*2=8; FILA(C5)*2=10; FILA(C6)*2=12; etc. Como nuestro primer dato se encuentra en la fila número 4 (y no en la 8) tendremos que restarle 4 para que la serie comience en dicho número, en 4: FILA(C4)*2-4=4; FILA(C5)*2-4=6; FILA(C6)*2-4=8; etc. Nuestro datos precisamente se encuentran en las filas 4, 6, 8, etcétera. La columna es siempre la misma COLUMNA(C4). Si colocamos estas funciones dentro de la función DIRECCION, lo que obtendremos es la dirección de las celdas en cuestión, a saber:
=DIRECCION(FILA(C4)*2-4;COLUMNA(C4)) resulta C4
=DIRECCION(FILA(C5)*2-4;COLUMNA(C5)) resulta C6
=DIRECCION(FILA(C6)*2-4;COLUMNA(C6)) resulta C8
=DIRECCION(FILA(C7)*2-4;COLUMNA(C7)) resulta C10, etcétera.

De esta manera ya tenemos las direcciones de las celdas que queremos obtener. Sólo nos falta utilizar una función que transforme el nombre de la celda en el valor que contiene la misma. Esta función es INDIRECTO. 
Aunque el problema ya está resuelto, nos podríamos encontrar un pequeño problema y es que si ahora añadimos filas por encima de la fila 4 entonces dejará de funcionar. Para evitar esto podemos hacer uso del siguiente "truco". Creamos un nombre para el primer dato. Nos situamos en la celda C4, vamos a la lista de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre, por ejemplo, Dato1 y pulsamos Enter. A partir de ahora la celda C4 se llama Dato1. Nos situamos en G2 y escribimos la fórmula:
=FILA(Dato1)
De esta manera obtendremos el número de fila en el que se encuentra el primer dato  de forma variable. Podemos ahora incorporarlo a nuestra fórmula original y problema resuelto:
=INDIRECTO(DIRECCION(FILA(C4)*2-$G$2;COLUMNA(C4)))

miércoles, 24 de diciembre de 2014

Detectar Códigos Alfanuméricos Pares

"Tengo más de 500 entradas en una columna de un código compuesto de letras y, al final, 4 dígitos. Necesito localizar cuáles de esos códigos son pares y que me escriba en una columna anexa dichos dígitos (sólo los que son pares)".

Antes de meterme en materia me permitiréis que siendo hoy el día que es os felicite a todos, primero por tener la paciencia de leerme de vez en cuando y, segundo y sobre todo, porque hoy sea un día que podáis disfrutar en familia y no dejéis que os lo estropee nadie (ni siquiera los políticos con o sin coleta...)

Partimos del siguiente ejemplo: (ups! Se me ha "colao" un señor de barba blanca en el ejemplo y haciendo publicidad para mi hermano Santi...)

Nos situamos en D3 y escribimos la siguiente fórmula que copiamos hasta D15 y que paso a desmenuzar a continuación:

=SI(N(ES.PAR(VALOR(DERECHA(B3;1))))=0;"";VALOR(DERECHA(B3;4)))

DERECHA(B3;1)   esta parte de la fórmula extrae 1 dígito empezando por la derecha del texto existente en B3. Aunque se trata "visualmente" de un número, excel lo trata como texto por formar parte precisamente de una cadena de texto. Para convertirlo en número utilizamos la función VALOR, a saber: VALOR(DERECHA(B3;1)).

Una vez hecho esto, procedemos a comprobar si el dígito que acabamos de extraer es par o no. Para ello utilizamos la función ES.PAR, ES.PAR(VALOR(DERECHA(B3;1)))  que nos devolverá el resultado VERDADERO o FALSO. Para convertir este VERDADERO ó FALSO en 1 ó 0 utilizamos la función N (también podríamos poner dos signos negativos consecutivos -- en vez de dicha función)  N(ES.PAR(VALOR(DERECHA(B3;1)))).

Ya sólo nos queda anidar esta fórmula dentro de un condicional para que si el último dígito no es un número par (y por lo tanto la fórmula N(ES.PAR(VALOR(DERECHA(B3;1)))) será igual a 0) no escriba nada o, en caso contrario, que escriba los 4 dígitos del código como valor:  VALOR(DERECHA(B3;4)).

El resultado final es el que se muestra a continuación:
Feliz Navidad a todos y recordad: Para ser feliz hay que venir a pasar la Navidad al Balneario de Mondariz!!

miércoles, 3 de diciembre de 2014

Buscar la última Entrada de un Concepto Repetido

"Tengo una tabla con nombres de vendedores y, en la siguiente columna, las unidades vendidas en cada pedido. Necesito buscar la última entrada realizada de un vendedor concreto pero sólo consigo obtener la primera entrada (con la función BUSCARV)".

Para resolver este problema vamos a trabajar con varias funciones, a saber: INDICE, COINCIDIR, CONTAR.SI y CONCATENAR (&). Partimos del siguiente ejemplo:
Lo que queremos conseguir es que al introducir en C2 el nombre del vendedor excel nos devuelva el último valor existente de dicho comercial. Evidentemente, si utilizamos la función BUSCARV nos va a devolver el primer valor que se encuentre en la tabla del vendedor que le indiquemos. Aunque también se podría resolver con esta función, vamos a solucionarlo de otra manera que se me antoja "más elegante". Lo primero que hacemos es dar nombre a las dos columnas de datos. Seleccionamos el rango B6:C22 y en la ficha Fórmulas/Nombres Definidos pulsamos Crear desde la selección. En la ventana que se abre elegimos crear nombres a partir de los valores de la Fila superior. De esta manera, el rango B6:B22 pasa a denominarse Vendedor y el C6:C22 Unidades.

Ahora generamos una columna auxiliar para generar un número de orden de los distintos vendedores. Nos situamos, por ejemplo, en la celda F7 y escribimos la fórmula:

=B7&CONTAR.SI($B$7:B7;B7)  y la copiamos hasta F22.

La parte de CONTAR.SI($B$7:B7;B7) lo que hace es ir generando un contador para cada vendedor. Utilizando como ejemplo el primero, Pedro Flores, cada vez que aparezca en el rango de vendedores le irá sumando una unidad. Al primer Pedro Flores le asigna el 1 al segundo un 2 y así sucesivamente. Y esto para cada vendedor. Lo que hacemos con la parte de la fórmula B7& es preceder a este número de orden del nombre del vendedor y los concatenamos. De esta manera obtendremos Pedro Flores1, Pedro Flores2, Pedro Flores3, Joaquín Voz1, etcétera. Es decir, el nombre unido (concatenado) al número de orden:

Este paso me proporciona un nombre unido al número máximo de repeticiones de dicho nombre. Es decir, si Pedro Flores aparece, como es el caso, 4 veces entonces sé que el último valor de Pedro Flores será el asociado a Pedro Flores4.
Sólo nos queda una fórmula más para obtener nuestro objetivo. Nos situamos en C4 y escribimos:

=INDICE(unidades;COINCIDIR(C2&CONTAR.SI(vendedor;C2);F7:F22;0))

Veamos por partes esta fórmula:

C2&CONTAR.SI(vendedor;C2)  Une el nombre introducido en la celda de entrada C2 al número máximo de repeticiones del mismo dentro de la columna de Vendedor. En nuestro ejemplo el resultado será Pedro Flores (dato de C2) y el número 4, es decir, Pedro Flores4.

COINCIDIR(C2&CONTAR.SI(vendedor;C2);F7:F22;0)   Ahora buscamos este resultado (Pedro Flores4) dentro del rango F7:F22  con la función COINCIDIR. Con esta función lo que obtendremos es el número de fila en el que se encuentra dicho dato. En nuestro ejemplo el resultado de este "trozo" de fórmula será 16. Sabiendo el número de fila en el que se encuentra, ya sólo me queda incorporar este resultado a la función INDICE:
=INDICE(unidades;COINCIDIR(C2&CONTAR.SI(vendedor;C2);F7:F22;0))  para que busque dentro de la columna Unidades la fila 16 y me devuelva el valor:

lunes, 17 de noviembre de 2014

Especificar Tipo de Formato de una Celda

"Regularmente me envían un listado con diferentes entradas en una columna y necesito detectar, por medio de fórmulas, cuáles de dichas entradas son fechas".

Para solucionar este problema haremos uso de la función CELDA. Partimos del siguiente ejemplo:
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_infoDevuelve
"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.

He destacado en naranja "formato" porque es el tipo_de_info con el que vamos a trabajar. Para ello nos situamos por ejemplo en la celda E3 y escribimos la fórmula:

=CELDA("formato";B3)   y copiamos hasta E9:
Como se puede ver, en aquellas celdas que tenemos formato de fecha obtenemos la referencia D1. Haciendo uso de la ayuda de Excel, la siguiente lista describe los valores de texto que devuelve la función CELDA cuando el argumento tipo_de_info es "formato":

Si el formato de Excel es
La función CELDA devuelve
Estándar
"G"
0
"F0"
#.##0
".0"
0,00
"F2"
#.##0,00
".2"
$#,##0_);($#,##0)
"C0"
$#.##0;(rojo)-$#.##0
"-M0"
$#.##0,00_);($#.##0,00)
"C2"
$#.##0,00;(rojo)-$#.##0,00
"-M2"
0%
"P0"
0,00%
"P2"
0,00E+00
"C2"
# ?/? o # ??/??
"G"
d/m/aa o d/m/aa h:mm o dd/mm/aa
"D4"
d-mmm-aa o dd-mm-aa
"D1"
d-mmm
"D2"
mmm-aa
"D3"
mm/dd
"D5"
h:mm a.m./p.m.
"D7"
h:mm:ss a.m./p.m.
"D6"
h:mm
"D9"
h:mm:ss
"D8"

Como se puede comprobar, todos los formatos de fecha comienzan por la letra D. Por ello hacemos ahora la siguiente fórmula en la celda D3:
=SI(IZQUIERDA(E3;1)="D";"Sí";"No")   y copiamos hasta la celda D9:
Evidentemente, podríamos resolver el modelo con una única fórmula en D3 que nos evitaría la columna E, a saber:
=SI(IZQUIERDA(CELDA("formato";B3);1)="D";"Sí";"No")

IMPORTANTE: Si el argumento tipo_de_info de la función CELDA es "formato", como en nuestro caso, y procedemos a asignar un formato diferente al inicial a la celda a la que se hace referencia, es necesario volver a calcular la hoja de cálculo (o pulsar F9) para poder actualizar los resultados de dicha función.

Si queremos destacar en otro color aquellas entradas que son fechas entonces tenemos que hacer uso de la herramienta de Formato condicional. Para ello seleccionamos el rango B3:B9 y vamos a Formato condicional y formulamos como se detalla en la imagen a continuación: