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:

Validación de Caracteres No Númericos

Descargar Archivo

"Tengo una tabla en la que en uno de los campos debo introducir códigos de referencia de productos y necesito que excel compruebe que en dichas entradas no se introduce ningún carácter numérico, es decir, que sólo se pueden introducir caracteres alfabéticos (en mayúsculas o minúsculas indistintamente)".

Para solucionar este problema tendremos que comprobar cada una de las entradas carácter por carácter. Esto es debido a que excel diferencia entre entradas numéricas y no numéricas. Si escribimos un número, excel dispone de funciones y herramientas para comprobar si lo escrito es un número o no pero no ocurre lo mismo si la entrada es alfanumérica, es decir, si la entrada está compuesta por caracteres alfabéticos y caracteres numéricos. En este caso  excel considera la entrada como texto a todos los efectos. Partimos el siguiente ejemplo:

 Buscamos que excel permita entradas como las mostradas en B3 y B4 (indistintamente mayúsculas o minúsculas y con un largo entre 1 y 10 caracteres en este ejemplo) y que no permita entradas como B5 (alfanuméricas):

Para ello necesitamos "desmenuzar" carácter por carácter cada entrada. Primero vamos a realizar una lista con el número de caracteres de B3 para lo que debemos escribir las siguientes fórmulas:

copiamos la fórmula de E3 hasta M3. Finalmente copiamos el rango D3:M3 hasta D10:M10.
La primera fórmula comprueba si hay algo escrito en B3, en cuyo caso devuelve el primer valor de nuestra lista, esto es, 1. En caso de que no haya nada devuelve el texto X.
La fórmula de E3 comprueba si la celda anterior (D3) es menor que el largo total (número de caracteres) de la entrada de B3. En tal caso le suma 1 a la entrada anterior, por lo que devuelve 2 para seguir completando nuestra lista. En F3 y siguientes la fórmula comprueba lo mismo hasta que el número que aparezca supere al largo de la entrada, en cuyo caso devolverá una X:

Una vez generada una lista con el número de caracteres de cada entrada, pasamos a comprobar si cada uno de dichos caracteres es una letra o no. Para ello nos situamos en la celda N3 y escribimos la siguiente fórmula:

=--NO(ESERROR(1*(EXTRAE($B3;D3;1)))) y copiamos hasta W3 y posteriormente hasta W10 para finalizar la matriz.

Veamos como funciona esta fórmula:
La función EXTRAE nos permite extraer del texto de B3 un número de caracteres a partir de una posición inicial. El primer argumento de la función es B3 para indicarle en qué celda está el texto que nos interesa. El segundo argumento es el que hace referencia a la posición inicial, es decir, el número de carácter del texto de B3 desde el que debe de empezar la extracción. En nuestro caso ponemos D3 para que cuando copiemos hacia la derecha vaya cambiando a E3, F3, G3, etc. El último argumento indica el número de caracteres a extraer y que en nuestro caso es siempre 1. Con esta parte de la fórmula hemos conseguido desmenuzar carácter por carácter la entrada de B3.
A continuación lo multiplico por 1 para convertirlo en valor (de hecho podría utilizar también la función VALOR). Si se trata de un carácter numérico se convertirá en valor y en el caso contrario (si es una letra) me devolverá un error.
Ahora nos interesa comprobar si NO es un error, para lo que anido la función ESERROR dentro de la función NO. Aquellas entradas que no devuelvan un error me devolverán un valor VERDADERO y las que devuelvan un error mostrarán FALSO. Colocando un doble menos -- delante de la función convertimos estos valores VERDADERO y FALSO en 1 y 0 (lo podemos hacer también con la función N, como hemos visto en otros ejemplos).
En resumen, en el rango N3:W3 obtendremos un cero para aquellos caracteres de la entrada de B3 que sean alfabéticos y un 1 para aquellos que sean numéricos:

Ya sólo nos queda aplicar la Validación de datos. Para ello seleccionamos el rango B3:B10. Abrimos la herramienta de validación y  marcamos Criterio de validación Personalizada. En Fórmula escribimos: =SUMA (N3:W3)=0

De esta manera sólo permitirá introducir entradas cuya suma de cada carácter sea cero, es decir, aquellas que se compongan exclusivamente de caracteres alfabéticos:

Lista Desplegable con Rango Dinámico

"Necesito realizar una lista desplegable que vaya incorporando automáticamente los nombres que voy introduciendo en una tabla (pero sin que aparezcan espacios en blanco en dicha lista)".

Para solucionar este problema utilizaremos dos funciones, a saber, DESREF y CONTARA y la herramienta de Validación de Datos. Partimos del siguiente ejemplo:

Si utilizamos directamente la herramienta de Validación y seleccionamos como lista el rango E3:E20 entonces nos aparecerá un desplegable con 13 opciones en blanco:

Para evitar este problema vamos a crear un rango dinámico. Empezamos por crear el nombre del rango de los participantes, esto es, seleccionamos E3:E20 y en el cuadro de nombres (a la izquierda de la barra de fórmulas) escribimos el nombre Listado. A continuación vamos a la ficha Datos / Validación de datos y seleccionamos Lista. En Origen escribimos la fórmula:

=DESREF(E2;1;;CONTARA(listado))

De esta manera, el contenido de la lista desplegable se ajustará estrictamente a las entradas que se produzcan en el rango Listado (E3:E20). Con la función CONTARA calculamos el número de celdas no vacías del rango Listado. Dicho resultado será el argumento Alto de la función DESREF y crecerá o disminuirá en función de que añadamos o eliminemos registros del listado, como se puede ver en las siguientes imágenes: 

Máximos, Mínimos y Promedios por Columnas

Descargar Archivo

"He leído el post de "Resaltar Máximos, Mínimo  y Promedios con Formato Condicional" y me gustaría saber si se puede realizar el mismo cálculo pero por columnas".

La solución es muy sencilla. Partimos del siguiente ejemplo:

Nos situamos en la celda C3 y escribimos:

=MAX(C$8:C$19)  y copiamos hasta la celda G3

En C4 escribimos:

=MIN(C$8:C$19)    y copiamos hasta la celda G4

En C5 escribimos:

=PROMEDIO(C$8:C$19)   y copiamos hasta la celda G5

Seleccionamos ahora el rango C8:G19 y vamos a Formato Condicional / Utilice una fórmula que determine las celdas para aplicar formato. En Editar una descripción de regla escribimos la siguiente fórmula:

=C8=C$3  y pulsamos el botón Formato. Ahora seleccionamos el Relleno de color naranja y la fuente negrita (por ejemplo) y aceptamos.

Realizamos la misma operación de nuevo pero escribiendo ahora la fórmula:

=C8=C$4  y en el Formato seleccionamos el color verde y fuente negrita.

Finalmente vamos a resaltar aquellas zonas cuyo promedio se encuentre por encima del promedio total. Para ello seleccionamos el rango C7:G7 y vamos a Formato Condicional / Utilice una fórmula que determine las celdas para aplicar formato. En Editar una descripción de regla escribimos la siguiente fórmula:

=C$5>=PROMEDIO($C$5:$G$5)   y en el botón Formato seleccionamos, por ejemplo, relleno rosa y fuente negrita.