Seleccionar Aleatoriamente un Valor de un Conjunto

"En una columna tengo un conjunto  de valores del tipo 1, 1, 2, 5, 6, 6, 8, 2, 8, 1, 5, etcétera, y quiero seleccionar de manera aleatoria uno de estos valores".

Para solucionar este problema utilizaremos las funciones DESREF, CONTAR y ALEATORIO.ENTRE .  Partimos del siguiente ejemplo:

Lo primero que hacemos es darle nombre al rango de valores. Seleccionamos desde B3 hasta B16, hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre: valores y pulsamos Enter. A continuación nos situamos en la celda D10 y escribimos la siguiente fórmula que explico a continuación:

=DESREF(B2;ALEATORIO.ENTRE(1;CONTAR(valores));) 

CONTAR(Valores) cuenta el número de valores que hay en dicho rango. En nuestro ejemplo el resultado será 14. Al anidar esta función dentro de la función ALEATORIO.ENTRE, estamos consiguiendo que genere un número aleatorio entre 1 y 14 (que es el número mínimo y máximo de filas de nuestro rango). El problema es que entre 1 y 14 hay valores que no se encuentran en nuestra lista, por ejemplo el 7, el 11, el 12, etcétera. Lo que hacemos ahora es utilizar el número aleatorio generado para ir a una posición de la lista que tenemos y obtener el número que se encuentre en dicha posición. Para ello utilizamos la función DESREF. Partimos de la celda B2 y, a partir de dicha celda, excel se posicionará en la fila del rango Valores que de manera aleatoria hemos generado con el resto de la fórmula ya explicada. Si, por ejemplo, el número generado es un 11, excel se desplazará 11 filas más abajo de la celda de partida (B2) y nos devolverá el valor de B13, esto es, 6. Pruebe a pulsar la tecla F9 y verá cómo se recalcula el número y siempre dentro de los existentes en la lista :

Insertar Filas Intercaladas

"Tengo un archivo con unas 800 filas escritas y necesito intercalar una fila en blanco entre cada una de dichas filas escritas".

Empecemos por un caso sencillo. Tenemos la siguiente tabla mostrada en la imagen y queremos añadir una fila intercalada:

La forma más sencilla es utilizar la selección discontinua. Presionando la tecla Ctrl, hacemos clic en las celdas B4, B5, B6... hasta B12. Debemos hacer un clic en cada celda y no seleccionar como rango. Una vez tengamos hecha la selección discontinua vamos al menú Insertar/ Insertar Filas de Hoja y objetivo conseguido.

El problema es que si tenemos una cantidad grande de filas este sistema se hace eterno. He encontrado en la red una solución que me parece muy original y sencilla (sin necesidad de realizar macros) que comparto con vosotros y que encontraréis en el siguiente link la web del programador .

Voy a aplicarlo a nuestro ejemplo para que se vea bien aunque donde es verdaderamente efectivo es en tablas con muchas filas. Lo primero que hacemos, siguiendo las indicaciones de Ricardo -autor del post-, es generar una lista de números impares. En nuestro ejemplo original, nos situamos en la celda A2 y escribimos el rótulo Nº (por ejemplo). En A3 escribimos un 1 y en A4 un 3. Seleccionamos A3:A4 y copiamos hasta A12, que es la última fila de nuestra tabla. A continuación generamos una lista de números pares a partir de A13. En dicha celda escribimos un 2 y en A14 un 4. Seleccionamos ambas y copiamos hasta A22 (10 filas en total). Obtenemos lo siguiente:

Ahora sólo tenemos que situarnos en A3 e ir al menú Ordenar y Filtrar/Ordenar de la A a Z:

Procedemos ahora a borrar la columna A y problema resuelto (tan sólo tendremos que retocar algún formato si ya los teníamos preestablecidos antes de realizar la operación).

Obtener Parte de una Cadena Alfanumérica

"Tengo una columna con más de 500 registros de un código alfanumérico. La estructura es: una serie de números, un espacio, una serie de letras. La cantidad de números y letras es variable pero siempre los separa un espacio. Necesito obtener sólo los números y que se queden como formato de valor (no de texto)".

La solución es muy sencilla utilizando tres funciones como VALOR, IZQUIERDA y HALLAR. Partimos del ejemplo que se muestra en la siguiente imagen y queremos obtenr lo que se muestra en la segunda imagen:

Anidando las tres funciones citadas podemos resolverlo en una sola fórmula pero empezaré detallando paso a paso para su mejor comprensión:

Lo primero es obtener la posición del espacio para cada código. Esto lo podemos hacer utilizando la función HALLAR. Esta función busca una cadena de texto dentro de una segunda cadena de texto y devuelven el número de la posición inicial de la primera cadena de texto desde el primer carácter de la segunda cadena de texto. Es muy similar a la función ENCONTRAR con la diferencia de que la función HALLAR no distingue en su búsqueda entre mayúsculas y minúsculas, mientras que la función ENCONTRAR sí lo hace. Nos situamos en la celda F4 y escribimos la fórmula:

=HALLAR(" ";B4)-1  Le estamos pidiendo que busque un espacio dentro del texto de B4. El resultado será 6 porque el espacio en blanco es el sexto carácter del código que se encuentra en la celda B4. Como además le restamos 1, el resultado será 5, que es, precisamente, el número de dígitos del primer código.

Ya tenemos el número de dígitos de todos los códigos de la columna B. A continuación tendremos que proceder a"extirparlos". Para ello nos situamos en la celda G4 y escribimos la siguiente fórmula:

=IZQUIERDA(B4;F4)   De esta manera obtendremos la parte numérica del código. Al tratarse de una función de texto, el resultado obtenido es un texto y no un valor como deseamos. Para solucionar esto procederemos con el último paso...

 Nos situasmos en la celda H4 y escribimos:

=VALOR(G4)   De esta manera convertimos los dígitos del código en valor (en vez de texto):

Como ya avancé, podemos resumir estos tres pasos anidando en una sola función que escribimos en  la celda D4:

=VALOR(IZQUIERDA(B4;HALLAR(" ";B4)-1))

Copiamos hacia abajo y trabajo terminado:

Para obtener la parte alfabética del código podemos utilizar la siguiente fórmula que escribimos en F4 y copiamos hacia abajo:

=DERECHA(B4;LARGO(B4)-HALLAR(" ";B4))

Cálculo de Días con Año en base 360

" Necesito calcular la diferencia en días entre dos fechas utilizando un año de base 360 y, más concretamente, necesito que la diferencia entre fechas como el 01/01/2013 y el 28/02/2013 me devuelva 60 días, es decir, 2 meses completos de 30 días comerciales"

Para realizar este tipo de cálculo, y en general al trabajar con fechas en excel, debemos tener en cuenta una consideración importante, a saber:  excel almacena las fechas como números de serie secuenciales. Si introducimos una fecha inicial y una final y la restamos para calcular la diferencia en días, excel no tendrá en cuenta la fecha final en el cálculo:

Como se puede ver resta 31-1=30 (en realidad 41.305 menos 41.275, que son los números de serie que corresponden a dichas fechas). En consecuencia, si queremos que cuente también el último día tendremos que escribir la fecha 01/02/2013 como fecha final.

En el caso concreto de la consulta realizada tendremos que trabajar con la función DIAS360. Esta función devuelve la diferencia en días entre dos fechas basándose en un año de doce meses de 30 días (360 días):

En la celda C7 escribimos la fórmula:

=DIAS360(C2;C3) 

Al utilizar esta función y escribir el primer día de marzo como fecha final, excel calcula dos meses completos en base 360, es decir, 2 meses de 30 días. La diferencia resultante es la deseada: 60 días.

Copia Masiva de Celdas

"Tengo una tabla cuya primera fila son celdas con datos y fórmulas y necesito copiar dicha primera fila hasta la fila 15.000 ¿Hay alguna otra forma que no sea "tirando hacia abajo" manualmente con el ratón?".

Efectivamente existe una forma más directa y menos cansina... Supongamos que tenemos una tabla con tres campos y queremos copiar el contenido de dichos campos hasta la fila 15.000 de nuestra hoja:

Para ello seleccionamos el área que deseamos copiar, en nuestro caso el rango B3:D3 y vamos al cuadro de nombres (como se puede ver en la siguiente imagen) y escribimos la última celda del rango donde deseamos pegar lo copiado. En nuestro caso escribiremos D15000 ya que la última columna del rango seleccionado es la D y queremos copiar hasta la fila 15.000

A continuación NO pulsamos Enter. Pulsamos Shift + Enter  y de esta manera tendremos seleccionado el rango B3:D15000 como se ve en la imagen:

Ya sólo nos queda ir al menú Rellenar / Hacia abajo y terminaremos el copiado en tan sólo unos segundos: