La Rebotica de Excel

Personalizando Fórmulas con Validación de Datos

2012-01-21T17:41:00.021+01:00

"He leído el anterior artículo y me gustaría saber si hay forma de limitar la entrada de datos, por medio de la validación de datos, para conseguir que no permita introducir un código en la celda si no cumple la siguientes reglas: a)que el número total de caracteres sea de siete; b) que los dos primeros caracteres sean dos letras y estén escritos en mayúscula; c) que los siguientes caracteres sean 5 números."

Lo que queremos conseguir requiere del uso de la herramienta Validación de Datos por un lado y del uso de unas cuantas funciones para garantizar que se cumplen todas las restricciones indicadas por nuestro lector. En concreto, las restricciones que debe contemplar la fórmula que realicemos son:

1. Que el número de caracteres totales introducidos ha de ser 7

2. Que los dos primeros caracteres han de ser texto

3. Que los dos primeros caracteres han de ser mayúsculas

4. Que los caracteres del 3 al 7 han de ser números

Supongamos que tenemos una zona de introducción de datos como la mostrada en la siguiente imagen:

Lo que debemos hacer es seleccionar el rango B4:B12 que es nuestra zona de entrada de datos. Vamos a la ficha Datos/Validación/Validación de Datos y seleccionamos Permitir Personalizada. En Fórmula escribimos la siguiente (es un poco larga):

=Y(LARGO(B4)=7;IGUAL(B4;MAYUSC(B4));ESERROR(VALOR(EXTRAE(B4;1;1)));

ESERROR(VALOR(EXTRAE(B4;2;1)));

ESERROR(VALOR(EXTRAE(B4;3;5)))=FALSO)=VERDADERO

Voy a explicar las distintas funciones y partes de la fórmula. Empezamos con la función Y. Esta función nos sirve para comprobar si se cumplen una serie de pruebas que vamos a realizar. En caso de que se cumplan todas las pruebas que realicemos el valor que nos devolverá esta función será VERDADERO y en caso contrario FALSO (precisamente por este motivo podemos utilizarla dentro de validación de datos como fórmula personalizada).

La primera prueba que realizamos es la de el número total de dígitos. Para ello hacemos uso de la función LARGO, que nos devuelve el número de caracteres existentes en una celda, y comprobamos si es igual a 7.

La segunda comprobación que realizamos es si los dos primeros caracteres son mayúsculas. Para ello utilizamos la función IGUAL y la función MAYUSC. La función IGUAL comprueba si dos cadenas de texto son idénticas o no diferenciando entre mayúsculas y minúsculas.

Ahora comprobamos si los dos primeros caracteres son letras. Para ello debemos extraer dichos 2 caracteres para analizarlos. Hacemos uso de la función EXTRAE. Al utilizar esta función excel considera como texto los caracteres extraídos. En caso de que se trate de números será sencillo convertirlos nuevamente haciendo uso de la función VALOR. Finalmente utilizo la función ESERROR para comprobar que si tras convertir los dos primeros caracteres a número me devuelve un mensaje de error sólo puede ser debido a que se trate de texto. La misma operación pero al revés, es decir, comprobando que el valor devuelto es FALSO, resolverá la última parte de la fórmula donde verifico que los últimos 5 dígitos son números.

Una vez introducida esta fórmula en la validación de datos ya puede comprobar que el funcionamiento es el deseado y que sólo nos permitirá introducir valores correctos. Cada vez que cometamos un error nos aparecerá el mensaje que definamos dentro de la validación de datos, tal y como se muestra en los siguientes ejemplos:

Evitar Espacios Innecesarios al Introducir Textos

2011-11-16T05:17:00.016+01:00

"Quisiera saber si hay alguna manera de evitar que por descuido se puedan introducir espacios al principio y al final de una introducción de texto en una celda de Excel y de igual forma impedir que se introduzca más de un espacio entre dos palabras".

Vamos a resolver este problema de una manera bien sencilla con dos funciones (ESPACIOS y LARGO) y una herramienta (Validación de datos). Partimos del siguiente ejemplo:

Lo que queremos conseguir es que si al introducir texto en las celdas de entrada (en este ejemplo rango B3:B7) introducimos espacios en blanco, ya sea al principio, al final o en el medio excel nos devuelva un mensaje de error y no nos permita continuar hasta que corrijamos dicho problema. Como se puede ver en la siguiente imagen, si introducimos espacios innecesarios en la entrada de texto, excel nos devuelve el mensaje de error correspondiente:

Para conseguir esto tenemos que seguir los siguientes pasos:

1. Seleccionamos el rango B3:B7

2. Vamos al menú Datos/Validación de datos

3. En Criterio de validación/Permitir seleccionamos la opción de Longitud de texto.

4. En Criterio de validación/Datos seleccionamos Igual a.

5. En Criterio de validación/Longitud escribimos la siguiente fórmula:

=LARGO(ESPACIOS(B3))

6. En la pestaña de Mensaje de error seleccionamos el Estilo Límite o Grave (según la versión) y personalizamos el mensaje que queremos que aparezca si cometemos un error.

Espacios(B3) quita todos los espacios del texto excepto los espacios entre palabras. La función Largo cuenta el número de caracteres que hay en una celda. Al anidar la función Espacios dentro de la función Largo LARGO(ESPACIOS(B3)) lo que estamos haciendo es calcular el número de caracteres que tiene una entrada de texto una vez hecha la limpieza de espacios en blanco innecesarios. Si dicho número coincide con la longitud del texto que introducimos en B3 entonces significará que no hay espacios en blanco y que, por lo tanto, la entrada es correcta. Si por el contrario el número de caracteres que tiene la entrada de texto difiere del calculado por la fórmula explicada entonces sólo puede ser debido a que existan espacios en blanco que deben ser corregidos.

Cálculo Acumulados con Referencias Mixtas

2011-09-03T13:16:00.012+02:00

"Me gustaría me dijeses como puedo realizar acumulados parciales para poder compararlos. Me explico; tengo una hoja con las ventas realizadas, en las columnas los meses y el total año, y en las filas los años (2009, 2010, 2011...). Me gustaría hacer otra columna que acumule parcialmente al último dato introducido, es decir, si el último dato que tengo es agosto 2011, que en la columna de acumulado me refleje el acumulado por año al mes de agosto para poder comparar un año con otro".

La solución es bastante sencilla utilizando un condicional y las Referencias Mixtas dentro de la función SUMA. Empecemos por el ejemplo de partida:

Lo que queremos conseguir es replicar la tabla de la imagen pero que vaya presentando la cifra de ventas acumulada para cada mes de cada año, a medida que vayamos introduciendo nuevos datos. Para ello primero creamos la tabla que se muestra a continuación:

Nos situamos en la celda H5 y escribimos la siguiente y única fórmula (cosa posible gracias al correcto uso de las referencias mixtas):

=SI($E5="";"";SUMA(B$5:B5))

Podemos copiar esta fórmula para el resto de la tabla y problema resuelto. Cuando vayamos introduciendo el último dato de ventas, en nuestro ejemplo agosto 2011, nos irá mostrando las cifras de ventas acumuladas para los distintos años y meses hasta agosto 2011:

Sumar Matrices

2011-03-19T08:24:00.008+01:00

"Me gustaría saber qué métodos se pueden aplicar para sumar y restar matrices".

Básicamente podemos utilizar dos métodos. Cualquiera de los dos son muy sencillos. A saber:

1. Con la función SUMA

2. Fórmula Matricial

Supongamos que partimos del siguiente ejemplo:

Queremos proceder a sumar las cuatro matrices M1+M2+M3+M4. Utilizando el método más conocido simplemente nos situamos en la celda F24 y escribimos la fórmula (podemos utilizar la selección discontinua para seleccionar dichas celdas):

=SUMA(B3;B10;F3;F10)

Posteriormente copiamos esta fórmula hasta la celda H24 y finalmente la copiamos hacia abajo hasta la celda H28.

También podemos resolver esta suma utilizando fórmulas matriciales. Para ello nos situamos en la celda F17 y seleccionamos el rango F17:H21. Con el rango seleccionado escribimos la fórmula:

=B3:D7+B10:D14+F3:H7+F10:H14

y finalizamos pulsando (ya que se trata de entrada matricial) ctrl + shift + enter.

Alinear Números en Base a los Decimales

2011-03-17T17:57:00.010+01:00

"Necesito alinear los números de diversas celdas en base a los decimales (tres en concreto) y no como los presenta por defecto excel ¿es posible?"

Por supuesto que es posible y de una manera bastante sencilla. Para resolver esta cuestión simplemente vamos a personalizar el formato de celdas. Partimos del ejemplo mostrado en la columna B de la siguiente figura y queremos conseguir lo que se muestra en la columna D:

Para ello seleccionamos las celdas a las que que queremos cambiar el formato, en nuestro ejemplo seleccionamos el rango B4:B6 y pulsando el botón derecho del ratón accedemos a Formato de Celdas. Dentro de la pestaña Número seleccionamos la Categoría Personalizada. En Tipo escribimos lo siguiente: 0,???

Tras pulsar Aceptar tendremos los números del rango seleccionado alineados en base a los decimales (mostrará 3 decimales ya que hemos utilizado 3 interrogantes).

Años, Meses y Días Transcurridos entre Fechas

2011-03-06T06:34:00.018+01:00

"Necesito calcular los años, meses y días transcurridos desde una fecha concreta hasta hoy".

La solución es bastante sencilla utilizando, en términos de John Walkenbach, la función misteriosa de Excel, esto es, SIFECHA. Dicha función no aparece en la lista de funciones desplegables de la categoría Fecha y Hora. Tampoco aparece en el cuadro de diálogo Insertar Función, por lo que tendremos que introducirla manualmente ¿Por qué? Los caminos de Microsoft son inescrutables... Lo cierto es que se trata de una función muy útil que paso a describir:

=SiFECHA(Fecha_Inicial;Fecha_Final;Argumento_tiempo)

Los dos primeros argumentos no requieren explicación mientras que el tercer argumento se trata de un código que representa la unidad de tiempo que nos interesa. A saber:

"Y" Devolverá el número de años completos entre fecha inicial y fecha final.

"M" Devolverá el número de meses completos totales entre fecha inicial y fecha final.

"D" Devolverá el número de días totales entre fecha inicial y fecha final.

"YM" Devolverá los meses transcurridos entre las fechas y que no completen un año.

"MD" Devolverá los días del mes entre fechas que no completen un mes.

"YD" Devolverá los días entre fechas que no completen un año.

Partimos del siguiente ejemplo y entrada de datos:

Nos situamos en la celda C5 y escribimos la fórmula que calculará el número de años enteros transcurridos entre la fecha inicial y la fecha final:

=SIFECHA($C$2;$C$3;"Y")

Una vez hecho esto, y dado que hemos colocado referencias absolutas a la fecha inicial y a la fecha final, podemos copiar hacia abajo hasta la celda C7 y simplemente modificar después el tercer argumento en la fórmulas de C6 y C7. A saber:

En C6 =SIFECHA($C$2;$C$3;"YM") que nos devuelve el número de meses transcurridos entre las fechas y que no completan un año.

En C7 =SIFECHA($C$2;$C$3;"MD") que nos devuelve el número de días que no completan un mes.

Si queremos que aparezca el resultado completo en una sola celda entonces deberemos utilizar la función CONCATENAR (usaremos el operador &) para unir las distintas partes de la ecuación. En la celda B9 escribimos la siguiente fórmula:

A continuación puedes ver el resultado de aplicar las distintas opciones de argumento de tiempo en nuestro ejemplo:

% sobre Totales en Tablas Dinámicas

2010-12-10T22:15:00.021+01:00

"He leído tu post titulado Campos Calculados en Tablas Dinámicas y me gustaría saber que debo hacer para calcular el peso de cada columna sobre el total general"

El ejemplo de partida de dicho post es la siguiente tabla dinámica:

Como se puede ver se trata de una tabla dinámica con el detalle por zonas de los ingresos, los costes directos y el margen bruto. Lo que queremos conseguir es que esta información nos la muestre como porcentajes sobre los totales de cada columna para ver "el peso" de cada zona sobre el total.

Si lo que queremos es simplemente sustituir las cifras por porcentajes la operación es muy sencilla. A saber:

Nos situamos en la celda B4 y pulsamos el botón derecho del ratón. Aparecerá el siguiente menú emergente, en el que seleccionamos la opción Configuración de campo:

En la ventana que se abre pulsamos el botón Opciones:

Abrimos Mostrar datos como y seleccionamos la opción % del total:

Pulsamos Aceptar y objetivo conseguido (obviamente tendremos que repetir la misma operación para las otras dos columnas):

Si lo que queremos es que aparezcan ambos datos (la cifra y el porcentaje que representa sobre el total) sólo tendremos que duplicar las tres columnas que tenemos (arrastrando los campos a la zona de datos de la tabla dinámica) y posteriormente seguir los pasos que acabamos de explicar. Vamos a verlo con la columna de Ingresos Zona:

Tabla inicial:

Campos de la tabla dinámica:

"Arrastramos" el campo Ingresos dentro de la tabla dinámica en la zona de Datos y soltamos. Aparecerá, al final de la tabla, nuevamente la suma de ingresos por zona:

Aplicamos en esta columna los pasos explicados para calcular el porcentaje sobre el total (podemos aprovechar para cambiar el nombre de este campo dentro del menú Configuración de campo/Nombre) :

Ya sólo nos queda colocar esta columna a la derecha de Ingresos Zona. Para ello nos situamos dentro de la columna que hemos denominado % Ingr/Total y pulsamos el botón derecho del ratón. Utilizamos la opción Ordenar:

El resultado será el esperado:

Mostrar/Ocultar Información con Formato Condicional

2010-12-02T01:58:00.010+01:00

Descargar el Archivo

"Me gustaría tener una casilla de verificación que al marcarla apareciera una tabla con un análisis de sensibilidad de una cuenta de resultados y que al desactivar dicha casilla de verificación desapareciera la tabla (incluidos los bordes)".

Vamos a utilizar el siguiente ejemplo:

Tenemos una entrada de datos para el precio de un producto y otra entrada para la cantidad. Debajo hemos colocado una casilla de verificación con el texto "ver análisis de sensibilidad". Para crear esta casilla de verificación vamos al menú Ver/barras de herramientas/Formulario y, en la nueva barra que aparece, hacemos clic encima de la casilla de verificación:

La dibujamos en la hoja, nos ponemos encima de la casilla dibujada y hacemos clic en el botón derecho del ratón. Se abrirá un menú emergente del que seleccionamos la opción Formato de control. Se abrirá una ventana y en la pestaña Control en Valor seleccionamos Sin Activar y en Vincular con la celda hacemos clic en la celda G2. Pulsamos Aceptar. A partir de este momento cada clic en la casilla de verificación se convertirá en un VERDADERO o FALSO en la celda G2.

Lo que queremos conseguir es lo que se muestra en las dos siguientes imágenes:

1. Seleccionamos el rango E3:F11 y dibujamos los bordes de la tabla.

2. En el rango E3:E11 introducimos los precios que queremos evaluar en el análisis de sensibilidad.

3. En la celda F2 introducimos la fórmula =C3*C4

4. Seleccionamos el rango E2:F11 y vamos al menú Datos/Tabla.

5. En la ventana que se abre en Celda de entrada columna seleccionamos la celda C3 y pulsamos Aceptar.

El resultado obtenido será el siguiente:

6. Ocultamos la fila 2 para que no se vea la fórmula de enlace ni el resultado de la casilla de verificación (el verdadero o falso).

7. Seleccionamos el rango E3:F11 y vamos al menú Formato/Formato condicional.

8. En Condición 1 seleccionamos Fórmula y en el cuadro de la derecha escribimos lo siguiente =$G$2=FALSO

9. Pulsamos el botón Formato y en la pestaña Fuente optamos por el color de fuente blanco. En la pestaña Bordes pulsamos la opción Ninguno:

10. Pulsamos Aceptar y objetivo conseguido.

Resaltar un Dato Concreto de una Tabla

2010-12-01T01:25:00.003+01:00

"Aprovechando el ejemplo utilizado en el anterior artículo ( Destacar Toda una Fila de una Tabla de Datos) me gustaría saber cómo destacar un dato concreto de dicha tabla, es decir, el cruce entre una fila y una columna (un año y un mes concreto en el ejemplo)".

La solución es muy parecida a la mostrada en el anterior post. Tendremos que incluir una nueva entrada de datos que haga referencia al mes y utilizar la función lógica Y dentro del Formato Condicional. La tabla de la que partimos es la misma que ya utilizamos en el anterior artículo, a saber:

Para destacar un sólo dato necesitamos incluir en el modelo una nueva entrada de datos que será el mes. Para ello introducimos el rótulo en E2 como se ve en la imagen y nos situamos en la celda F2 y vamos al menú Datos/Validación/Permitir/Lista y en Origen seleccionamos el rango C10:N10 y pulsamos Aceptar.

Ahora ya podemos indicar el año y el mes que queremos destacar. A continuación seleccionamos el rango C11:N19 y vamos al menú Formato/Formato condicional. En Condición 1seleccionamos Fórmula y en el cuadro de la derecha escribimos la siguiente:

=Y($B11=$C$2;C$10=$F$2)

Pulsamos el botón Formato y elegimos, por ejemplo, trama amarilla y número en negrita y pulsamos Aceptar. El resultado será el siguiente:

En realidad sólo se destacará el dato dentro de la tabla. Para que también destaque el año y el mes debemos hacer lo siguiente:

Para el mes:

1. Seleccionamos el rango C10:N10

2. Vamos al menú Formato/Formato condicional y seleccionamos Fórmula.

3. En el cuadro de la derecha escribimos la fórmula: =C$10=$F$2

4. Pulsamos el botón Formato y elegimos los colores de trama y de fuente

5. Pulsamos Aceptar.

Para el año:

1. Seleccionamos el rango B11:B19

2. Vamos al menú Formato/Formato condicional y seleccionamos Fórmula.

3. En el cuadro de la derecha escribimos la fórmula: =$B11=$C$2

4. Pulsamos el botón Formato y elegimos los colores de trama y de fuente

5. Pulsamos Aceptar.

Destacar toda una Fila de una Tabla de Datos

2010-11-30T01:09:00.015+01:00

Descargar el Archivo

"Tengo una tabla con datos históricos de ventas por meses. La utilizo habitualmente en presentaciones y me ayudaría mucho el poder seleccionar un año y que se destacara la información de dicho ejercicio".

No problema. La solución es muy sencilla (cosa que se agradece a estas alturas del año) haciendo uso del Formato Condicional. Partimos del siguiente ejemplo:

Introducimos la entrada de datos como sigue:

Nos situamos en la celda C2 y vamos al menú Datos/Validación y seleccionamos Permitir/Lista. En el apartado Origen seleccionamos el rango B11:B19 y pulsamos Aceptar. De esta manera ya tenemos nuestra lista desplegable con los años representados en la tabla:

A continuación seleccionamos todos los datos de la tabla, es decir, el rango C11:N19 (los rótulos no). Vamos al menú Formato/Formato condicional y lo configuramos de la siguiente manera:

En la Condición 1 seleccionamos la opción Fórmula y en el cuadro de la derecha escribimos la fórmula:

=$B11=$C$2

Pulsamos el botón Formato y seleccionamos el formato concreto que queremos que adopte toda la fila a destacar (por ejemplo trama amarilla y números en negrita) y pulsamos Aceptar:

¡Objetivo conseguido! Cuando seleccionemos un año en la celda C2 toda la fila de la tabla correspondiente a dicho año se formateará como le hayamos indicado:

Búsqueda con Columna Variable (3 soluciones)

2010-11-21T13:44:00.017+01:00

Descargar el Archivo

"Tengo una tabla con información de empleados y me gustaría tener como variable una celda donde indicarle la información requerida para que me devuelva la información existente en la intersección entre el empleado que aparece en la primera columna de la tabla y una columna variable".

Aunque una posible solución ya la tenéis en el post "Búsqueda con Tablas e Indicador de Columna variable", y dado que me han llegado varias consultas muy similares, vamos a ampliar dicho artículo planteando tres soluciones distintas con las siguientes funciones: doble BUSCARV, BUSCARV e INDIRECTO y, finalmente, INDICE y COINCIDIR. Partimos del siguiente ejemplo:

Planteamos la siguiente entrada de datos:

En la celda C2 seleccionaremos el empleado del que queremos la información y en la celda C3 la información concreta que necesitamos. Empezamos creando con la herramienta de Validación de Datos las listas de entrada de datos. Para ello nos situamos en la celda C2 y vamos a l menú Datos/Validación y lo formulamos como se muestra en la imagen:

Pulsamos Aceptar y ya tendremos nuestra lista desplegable en C2. Para hacer lo mismo en C3 debemos dar algún paso intermedio. El motivo es que el contenido de la lista de C3 serán los rótulos de los campos de la tabla. Dicho rótulos se encuentran en disposición horizontal y la herramienta de validación no nos permite que el origen de la lista tenga dicha disposición. Lo único que tenemos que hacer es seleccionar el rango B10:F10 y pulsar Copiar. A continuación nos situamos en la celda H3 y vamos a Edición/Pegado Especial y seleccionamos Transponer, y pulsamos Aceptar. En la columna I añadimos la numeración que se muestra en la imagen y que después veremos su uso:

Ahora sí podemos situarnos en C3 y proceder a genera la lista desplegable (Datos/Validación):

1. Solución con Doble BUSCARV

Seleccionamos el rango B11:F20 y vamos al cuadro de nombres (a la izquierda de la barra de fórmulas). Hacemos un clic y escribimos el nombre Datos y pulsamos Enter. A continuación hacemos lo mismo con el rango H3:I7 y le damos el nombre, por ejemplo, ncol. Esta tabla no es otra cosa que el nombre de los distintos campos de la tabla y el número de columna que representa dentro de dicha tabla (que hemos denominado Datos). Nos situamos en la celda B5 y escribimos la siguiente fórmula:

=BUSCARV(C2;datos;BUSCARV(C3;ncol;2;FALSO);FALSO)

Lo que estamos haciendo es anidar un BUSCARV en el argumento número de columna de dicha función. Como se puede ver en la siguiente imagen, seleccionamos en C2 el empleado que queremos consultar (empleado7) y después en C3 seleccionamos de la lista la información que necesitamos:

2. Solución con BUSCARV e INDIRECTO

Podemos obtener el mismo resultado anidando la función INDIRECTO en el argumento número de columna de la función BUSCARV. Para ello seleccionamos el rango H3:I7 y vamos al menú Insertar/Nombre/Crear. Aparecerá la ventana que se muestra en la siguiente imagen y en donde debemos seleccionar (ya aparece seleccionada por defecto) la opción columna izquierda:

Pulsamos Aceptar. A partir de este momento en el cuadro de nombres aparecerán todos los nombres de los rótulos de la tabla de datos. Al pulsar cualquiera de estos nombres nos llevará al número de columna que representan. Y esto es lo que precisamente nos ayudará con la función INDIRECTO. Nos situamos en la celda D5 y escribimos la siguiente fórmula:

=BUSCARV(C2;datos;INDIRECTO(C3);FALSO)

3. Solución con INDICE y COINCIDIR

La función INDICE nos devuelve la intersección de una fila y columna de una matriz. Nuestra matriz es la tabla que hemos denominado Datos. El número de fila que queremos dependerá del empleado que seleccionemos en C2. Para calcular a qué fila se refiere introducimos la función COINCIDIR. El número de columna dependerá de la información solicitada en C3. Utilizamos también la función COINCIDIR para obtener el número de columna. La fórmula que debemos escribir en F5 es:

=INDICE(datos;COINCIDIR(C2;B11:B20;0);COINCIDIR(C3;B10:F10;0))

Problemas en el Cálculo del Valor Actual Neto

2010-10-27T01:19:00.009+02:00

"Estoy intentando realizar el cálculo del Valor Actual Neto (VAN) de una inversión y obtengo un resultado incorrecto".

El ejemplo que nos envía es el siguiente:

La función VAN en excel presenta diversas peculiaridades:

a) El nombre de la función en excel es VNA, y no VAN.

b) El desembolso inicial o inversión no debe introducirse dentro de la función, sino fuera de ésta sumando.

c) El rango de flujos de caja considerado en la función no debe contener ninguna celda en blanco. Si, como es el caso, esperamos un flujo igual a cero en algún periodo del proyecto debemos escribir cero como valor en dicho periodo. Este es precisamente el error que nos encontramos en este caso.

Nos situamos en la celda C13 y escribimos la fórmula:

=VNA(C3;C6:C11)+C5

El primer argumento hace referencia a la tasa de descuento. En nuestro ejemplo se trata del Tipo de Interés Nominal Anual (TIN) que se encuentra en la celda C3.

El segundo argumento es el rango donde se encuentran los flujos de caja (sin incluir el desembolso inicial, como ya he indicado). En nuestro caso el rango C6:C11.

Finalmente, fuera de la función VNA sumamos (ya que se encuentra con signo negativo) el desembolso inicial.

La fórmula está perfectamente planteada. El problema en este caso es que en los años 3 y 4, en los que esperamos un flujo de caja igual a cero, hemos dejado las celdas en blanco. Con este planteamiento excel interpreta que el flujo del año 3 (que se encuentra en blanco) es el de la siguiente celda del rango que contenga un valor, es decir, el del año 5 en nuestro ejemplo. En el año 4 interpreta que su valor es el del año 6. Evidentemente esto genera un error a la hora de actualizar los valores. La solución es tan sencilla como introducir los valores cero en las celdas que hemos dejado en blanco, tal y como se muestra a continuación:

Cálculo de la Frecuencia de la Moda

2010-10-16T00:14:00.008+02:00

"En un post de tu blog , explicas cómo hallar el valor más repetido en un rango (la moda). Lo que yo necesito es, además de saber dicho valor, saber cuántas veces se repite".

El post al que te refieres es Cálculo del Valor más Frecuente (MODA) de un Rango. Para dar solución a tu pregunta utilizaremos las funciones CONTAR.SI y MODA. Partimos del siguiente ejemplo que muestra las valoraciones obtenidas en las encuestas realizadas a cuatro tiendas:

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

=CONTAR.SI(B4:B15;MODA(B4:B15))

De esta manera lo que estamos haciendo es utilizar como criterio para contar el valor de la moda, es decir, que cuente cuántas veces se repite el valor de la moda en el rango B4:B15 . En nuestro ejemplo sólo nos quedaría copiar la fórmula introducida en B17 en el rango C17:E17 .

Si además queremos tener disponible el valor en si de la Moda podemos escribir en B19 la fórmula:

=MODA(B4:B15) y copiar esta fórmula en el rango C19:E19

Como se puede comprobar, el valor de la Moda de las encuestas de la Tienda 1 es 2 y dicho valor se repite 4 veces.

Cálculo de Número de Días

2010-09-09T01:31:00.012+02:00

"Necesito calcular, cada día, cuántos días llevan transcurridos desde el 1 de enero del año en curso y los días que faltan para concluir el año ¿hay alguna función que realice estos cálculos?"

No existe ninguna función directa que realice estos cálculos, pero la solución es bien sencilla utilizando las funciones FECHA y AÑO. Supongamos que deseamos contar con la siguiente información actualizada diariamente:

1. la fórmula de B2 es muy sencilla:

=HOY()

2. La fórmula de B3 es igualmente sencilla utilizando la función NUM.DE.SEMANA Esta función tiene tan sólo dos argumentos NUM.DE.SEMANA(Num_de_serie;Tipo). El primer argumento se refiere a la fecha de la que queremos saber el número de semana del año que le corresponde. El segundo argumento sirve para determinar el tipo de semana, esto es, si la semana comienza el domingo, en cuyo caso escribiremos el valor 1, o si queremos considerar semanas cuyo primer día sea el lunes, en cuyo caso el valor de este argumento será 2. Así las cosas, la fórmula resultante será:

=NUM.DE.SEMANA(B2;2)

3. Para calcular los días transcurridos desde el 1 de enero del año en curso hasta la fecha actual, utilizamos la siguiente fórmula:

=B2-FECHA(AÑO(B2);1;1) Si queremos añadir un texto descriptivo al resultado podemos utilizar el operador & (CONCATENAR):

=B2-FECHA(AÑO(B2);1;1)&" días desde principio del año"

4. Finalmente para saber cuántos días restan para concluir el año en curso:

=FECHA(AÑO(B2);12;31)-B2 Añadiendo un texto descriptivo sería:

=FECHA(AÑO(B2);12;31)-B2&" días para terminar el año"

Si queremos mantener esta información siempre visible en la hoja, podemos utilizar la opción de Inmovilizar Paneles. Para ello nos situamos en la celda A7 y vamos al menú Ventana/Inmovilizar Paneles. Aparecerá un borde superior en toda la fila 7 quedando inmovilizadas las celdas que se encuentren por encima de dicha linea.

Repetición de Caracteres Concretos en una Celda

2010-09-08T00:44:00.010+02:00

Pues parece que fue ayer pero ya ha pasado más de un mes desde mi última entrada. Prometí a mis hijos no acercarme a una hoja de cálculo durante las vacaciones y... ¡casi lo cumplo!

Me habéis mandado numerosas preguntas (supongo que debo daros las gracias...) que intentaré contestar lo antes posible. Una de las más repetidas ha sido la siguiente:

"Necesito saber el número de espacios en blanco que contiene un grupo de celdas".

Vamos a utilizar una fórmula bastante sencilla, que sé que leí hace tiempo en algún lado pero no recuerdo donde, con las funciones LARGO y SUSTITUIR. Partimos del siguiente ejemplo:

Queremos saber cuántos espacios en blanco contiene cada celda (cuestión que aunque no lo parezca puede resultar muy útil para, por ejemplo, realizar fórmulas que separen en distintas celdas los nombres y los apellidos). Lo que vamos a hacer en esencia es:

1. Contar el número total de caracteres que contiene la celda B3

2. Borrar todos los espacios en blanco del texto de B3 y escribir el resultado en C3.

3. Contar el número total de caracteres de C3.

4. Hallar la diferencia entre ambos totales. Dicha diferencia será, obviamente, el número de espacios en blancos que tiene la celda B3.

Vamos a empezar, con el objetivo de que se entienda mejor, por el paso 2. Para ello utilizaremos la función SUSTITUIR. Esta función sustituye dentro de una cadena de texto un texto original por otro nuevo específico. La sintaxis de esta función es:

SUSTITUIR(texto;texto_original;texto_nuevo; núm_de_ocurrencia)

Texto:es el texto o la referencia a una celda que contiene texto en el que desea sustituir caracteres.

Texto_original: es el texto que desea reemplazar.

Texto_nuevo: es el texto por el que desea reemplazar texto_original.

Núm_de_ocurrencia: especifica la instancia de texto_original que desea reemplazar por texto_nuevo. Si especifica el argumento núm_de_ocurrencia, sólo se remplazará esa instancia de texto_original. De lo contrario, todas las instancias de texto_original en texto se sustituirán con texto_nuevo.

Así las cosas, nos situamos en la celda C3 y escribimos la siguiente fórmula:

=SUSTITUIR(B3;" ";"") Fíjese que el segundo argumento es "espacio" y que el tercero es "" sin ningún espacio. Le estamos pidiendo a excel que sustituya los espacios en blanco que se encuentre por nada. El resultado de esta fórmula será este:

Como puede comprobar, el contenido de la celda C3 es el mismo que el de la celda B3 pero sin espacios en blanco.

Una vez hecho esto el resto es muy sencillo. Sólo nos queda "medir" el largo de ambas celdas y hallar la diferencia. Para ello hacemos lo siguiente:

en D3 escribimos: =LARGO(B3)

en E3 escribimos: =LARGO(C3)

en F3 escribimos: =B3-C3

¡Problema resuelto! Evidentemente si sólo necesitamos el número de espacios en blanco podemos resumir todas estas fórmulas en la siguiente (que escribo en la celda C3):

=LARGO(B3)-LARGO(SUSTITUIR(B3;" ";""))

En este caso hemos contado el número de espacios en blanco pero puede utilizar esta fórmula con otros caracteres simplemente sustituyendo la expresión " " por "a" ,por ejemplo.

Buscar Dentro de Fórmulas

2010-07-24T03:11:00.016+02:00

Descargar el Archivo

"Tengo una hoja con muchos datos y con cálculos de sumatorios parciales dispersos por dicha hoja. Además de dichos sumatorios tengo otras fórmulas. Necesito localizar todas las celdas en donde existe una fórmula que esté utilizando la función SUMA, ya que debo revisar que los rangos aplicados en esta función sean correctos".

La solución a este problema es muy sencilla utilizando la herramienta Buscar. Para ello vamos a utilizar el siguiente ejemplo:

Se trata de una tabla en la que tenemos diversos datos y diversas fórmulas y lo que pretendemos es localizar aquellas que contengan la función SUMA. Para ello seguimos los siguientes pasos:

1. Vamos a l menú Edición/Buscar. Se abrirá la siguiente ventana:

2. Dentro del campo Buscar: escribimos la palabra SUMA y comprobamos que dentro de del campo Buscar dentro de: tenemos seleccionado Fórmulas. Pulsamos el botón Buscar todo. El resultado será el siguiente:

Una vez resuelto el problema planteado en esta consulta, tenemos muchas opciones para trabajar. Lo primero que cabe destacar es que esta herramienta nos presenta un listado de todas las celdas que contienen la función buscada con su fórmula correspondiente. De esta manera ya podemos comprobar desde la propia herramienta si el rango de SUMA es el que nos interesa. Además podemos hacer clic en cada uno de los resultados de la búsqueda y excel selecciona automáticamente la celda a la que hace referencia:

En esta imagen puede comprobar que al seleccionar el primer resultado de la búsqueda excel selecciona la celda en la hoja a la que hace referencia (celda B11).

También podemos realizar una selección múltiple marcando, por ejemplo, el primer resultado de la búsqueda y haciendo clic con la tecla Shift pulsada en el último resultado de la búsqueda:

Como puede comprobar en la imagen excel selecciona las tres celdas implicadas, a saber: B11, C11 y E11.

Finalmente también puede realizar una selección discontinua pulsando la tecla Ctrl y haciendo clic en los resultados de la búsqueda que le interese (por ejemplo el primero y el último):

Evidentemente, las opciones de esta herramienta son mucho más amplias y, por lo tanto, serán objeto de futuros posts.

Días Laborables Entre Dos Fechas

2010-07-20T00:25:00.016+02:00

Descargar el Archivo

"Necesito calcular los días laborables transcurridos entre dos fechas ¿Hay alguna función que lo calcule?"

Sin problema. Resolveremos esta cuestión con la función DIAS.LAB

Partimos del siguiente ejemplo:

Queremos que en la celda C6 aparezca la diferencia de los días laborables transcurridos entre la fecha inicial indicada en la celda C2 y la fecha final indicada en la celda C4. Aplicando la función DIAS.LAB la solución es sencilla.

Nota: La función DIAS.LAB no aparece por defecto en la categoría Fecha y Hora de Excel (en versiones anteriores a Excel 2007). Para añadirla debe hacer lo siguiente: vaya al menú Herramientas/Complementos y active la casilla de verificación Herramientas para análisis. Pulse Aceptar y nuevas funciones, incluida la que nos ocupa, le aparecerán en las distintas categorías.

La sintaxis de esta función es:

=DIAS.LAB(Fecha inicial;Fecha final;Festivos)

Es importante destacar que la función DIAS.LAB considera los sábados y domingos como no laborables. Por otro lado, vamos a necesitar un listado de los días festivos del periodo a analizar. En nuestro ejemplo hemos introducido una lista de las fechas festivas en 2009 y 2010 (calendario que debe actualizarse y completarse con festivos locales):

Para que excel nos advierta si introducimos incorrectamente una fecha vamos a utilizar la herramienta de Validación de datos y la función lógica SI.

1. Seleccionamos el rango B16:B38 y le damos el nombre Fiestas (haciendo clic en el cuadro de nombres -a la izquierda de la barra de fórmulas- y escribiendo directamente dicho nombre y pulsando después Enter)

2. Nos situamos en la celda C6 y escribimos la fórmula:

3. Nos situamos en la celda C4 y vamos al menú Datos/Validación y realizamos la configuración que se muestra en las siguientes imágenes:

De esta manera además de obtener el cálculo que estábamos buscando:

si introducimos incorrectamente la fecha final excel nos advertirá:

Generar Agenda de Tareas (Gráficos Gantt)

2010-07-07T00:55:00.015+02:00

Durante esta última semana he tenido varias consultas relativas a la generación de gráficos para el control de agendas de proyectos, o lo que es lo mismo, gráficos Gantt. Excel no presenta este tipo de gráficos por defecto pero resulta bastante sencillo conseguirlo siguiendo unos pasos...

Partimos del siguiente ejemplo donde podemos ver en la columna B la tarea; en la columna C la fecha de inicio de dicha tarea; y en la columna D la duración en días desde la fecha de inicio de cada tarea:

1. Seleccionamos el rango B2:D8 y procedemos a insertar gráfico tipo barras horizontales; subtipo barras apiladas:

2. Pulsamos directamente Finalizar. El gráfico obtenido será el siguiente:

3. Ya tenemos la base para empezar a trabajar... Lo que debemos hacer a continuación es un clic con el botón derecho del ratón sobre el eje de las Y (eje vertical) y seleccionar Formato de ejes...

4. En la pestaña Escala marcamos la opción Categorías en orden inverso. De esta manera excel colocará las tareas en el orden correcto (no como las presentaba en el gráfico anterior) y las fechas en la parte superior del gráfico. Hacemos clic encima de la leyenda y suprimimos:

5. Hacemos doble clic encima de la serie de datos de color azul, que se refiere a las fechas de inicio, y le quitamos el relleno y el borde:

6. Hacemos doble clic encima de las fechas que se encuentran en la parte superior del gráfico, para darles formato, y seleccionamos en la ventana que se abre la pestaña Escala. Excel no reconoce aquí los formatos de fecha, por lo que debe utilizar el formato general. Para hacer esto debe volver a la hoja y situarse, por ejemplo en la celda F3. En esta celda copie la fecha inicial de la primera tarea y vaya a Formato/Celda/Número y seleccione la opción General. Póngase en F4 y copie la fecha de inicio del último proyecto más el número de días de duración (en nuestro ejemplo la última fecha es el 7/8/2010 y la duración de esta última tarea es de 7 días, por lo que la fecha final será el 14/8/2010) y haga lo mismo que en el anterior caso. De esta manera obtendremos los números de serie que corresponden a estas dos fechas que nos interesan (en concreto el 40369 y el 40404 ¿Para qué? Pues volviendo a nuestra ventana de Formato de ejes en la pestaña Escala debemos escribir como Mínimo el 40369 (que es la fecha de inicio de la primera tarea) y como Máximo el 40404 (que es la fecha de finalización de la última tarea):

Y con algunas mejoras de formato (color de fuentes, color de lineas de división, etc) conseguimos un sencillo gráfico Gantt como el mostrado a continuación:

Búsquedas de Derecha a Izquierda

2010-06-12T07:28:00.010+02:00

Descargar el Archivo

"Tengo una tabla con diversas columnas, cuyo orden no puedo modificar, y necesito realizar una búsqueda en la segunda columna y que me devuelva lo que contiene la primera columna. No puedo utilizar BUSCARV porque esta función realiza la búsqueda de izquierda a derecha y yo necesito lo contrario".

Para solucionar este problema utilizaremos las funciones INDICE y COINCIDIR. Partimos del siguiente ejemplo:

En la tabla de la figura nos encontramos con que el código de empleado se encuentra en la columna C. Lo que queremos es introducir uno de estos códigos en la celda C2 y que en C4 nos aparezca el nombre del empleado en cuestión (búsqueda de derecha a izquierda). Para ello seguimos los siguientes pasos:

1. Nos situamos en C2 y vamos al menú Datos/Validación. Seleccionamos Permitir Lista y en Origen marcamos el rango C9:C16, que es donde tenemos los códigos que queremos que aparezcan en la lista. Pulsamos Aceptar.

2. Nos situamos en la celda C4 y escribimos la fórmula:

=INDICE(B9:B16;COINCIDIR(C2;C9:C16;0))

La parte de la fórmula COINCIDIR(C2;C9:C16;0) nos proporciona el número de fila en el que se encuentra el contenido de C2 dentro del rango C9:C16. Si seleccionamos el código G17 en la celda C2 y probamos a introducir este "trozo" de fórmula en una celda, por ejemplo en C6, veremos que nos proporcionará el resultado 7. Esto es debido a que el código G17 se encuentra en la fila 7 del rango C9:C16. Incorporando este resultado como segundo argumento de la función INDICE ya obtendremos la solución deseada (búsqueda de derecha a izquierda):

Consolidar Informes

2010-06-06T18:40:00.022+02:00

"Habitualmente recibo resúmenes de ventas trimestrales de más de 12 comerciales distintos ¿Existe alguna manera de que excel "junte" toda la información en una tabla sin que yo tenga que andar copiando/pegando?"

Sí, existe: la herramienta Consolidar. Partamos como siempre de un ejemplo. Supongamos que tenemos 4 comerciales, a saber, Carlos, Javier, José y Santiago, y que cada uno nos remite un archivo de excel donde indica su cifra de ventas por trimestres de los distintos productos que componen su cartera. En las siguientes imágenes vemos esta información:

Queremos consolidar toda esta información en un documento que denominaremos TOTALES. Para ello debemos seguir los siguientes pasos:

1. Desde el archivo TOTALES vamos al menú Datos/Consolidar. Aparecerá la siguiente ventana:

2. Elegimos la función que queremos utilizar en nuestra consolidación (Suma, Cuenta, Promedio, etc). En nuestro caso utilizaremos la SUMA.

3. En el apartado Referencia debemos seleccionar el primero de los rangos que nos interesa. Para ello pulsamos el icono con la flechita roja, nos vamos a la hoja llamada Ventas Carlos y seleccionamos el rango A6:E12 . En pantalla aparecerá la ruta completa:

4. Pulsamos Enter y veremos lo siguiente:

5. Pulsamos el botón Agregar. Una vez hecho esto la referencia aparecerá en el cuadro Todas las referencias.

6. Volvemos a pulsar el icono para seleccionar un nuevo rango en el campo Referencia. En esta ocasión nos dirigimos a la hoja llamada Ventas Javier y seleccionamos el rango A5:E8 . Al igual que en el caso anterior excel mostrará la ruta completa. Pulsamos Enter y después el botón Agregar. A estas alturas podremos ver en pantalla lo siguiente:

7. Repetimos los pasos anteriores para agregar las hojas de venta de José y de Santi:

8. Mantenemos marcadas las opciones de Usar rótulos en / Fila superior y Columna izquierda. Esto es debido a que tenemos rótulos en la fila superior (los trimestres) y rótulos en la columna izquierda (los productos). Finalmente decidimos si dejamos marcada la opción Crear vínculos con los datos de origen. Si quitamos la marca excel no guardará los vínculos y se limitará a realizar la consolidación "pegando valores". Por el contrario, si mantenemos marcada esta opción, excel generará un esquema con la información de dónde proviene cada dato y además mantendremos vinculada la hoja TOTALES con el resto de las hojas, de tal manera que si modificamos cualquier dato en dichas hojas (dentro de los rangos considerados para la consolidación) se actualizará automáticamente la hoja TOTALES. Pulse el botón Aceptar y el resultado obtenido será este:

Como puede comprobar excel ha generado un esquema con la información consolidada. Pulse el nivel 2 de información y obtendrá el máximo detalle:

Cálculo de la Edad en Años

2010-06-02T23:56:00.011+02:00

"Partiendo de un listado con la fecha de nacimiento del personal necesito calcular su edad en años. Haciendo uso de la función AÑO no obtengo el resultado requerido ya que no tiene en cuenta fechas exactas".

Efectivamente, si necesitamos calcular la edad en años de un individuo, la función AÑO no nos proporcionará, por diferencia entre fecha de nacimiento y la fecha actual, el resultado deseado ya que no considera ni meses ni días. Por ejemplo, si tomamos como base el día de hoy, 2 de junio de 2010, y queremos saber cuántos años tiene alguien que nació el 28 de noviembre de 1969, la fórmula =AÑO(A1)-AÑO(A2) -suponiendo que en A1 tenemos la fecha actual y en A2 la fecha de nacimiento- nos devolverá un resultado de 41 años, que es la diferencia entre 2010 y1969. Es decir, que la operación que realiza la fórmula mostrada es 2010-1969=41. Pero evidentemente esta no es la edad del individuo en cuestión, ya que a 2 de junio todavía no habrá cumplido los 41.

Para realizar el cálculo correctamente utilizaremos la función FRAC.AÑO. Supongamos la siguiente lista:

Nos situamos en la celda D4 y escribimos la fórmula:

=FRAC.AÑO(C4;HOY();1)

La función FRAC.AÑO calcula la fracción de año que representa el número de días enteros entre una fecha inicial y una fecha final. Su sintaxis es FRAC.AÑO(Fecha_inicial;Fecha_final;Base). En nuestro ejemplo utilizamos como fecha inicial la fecha de nacimiento del empleado y como fecha final la generada por la función HOY(), es decir, el día actual. Una vez copiada la fórmula de D4 en el rango D5:D13 el resultado es el siguiente:

Para evitar los números decimales podemos utilizar la función TRUNCAR. En la celda E4 escribimos:

=TRUNCAR(D4) y copiamos hasta E13:

Evidentemente podemos resolver todo el problema con una sola fórmula. En F4 escribimos:

=TRUNCAR(FRAC.AÑO(C4;HOY();1))

Comparar Cadenas de Texto

2010-05-18T23:54:00.010+02:00

"A menudo tengo que comparar más de 500 entradas de texto (listados de nombres y apellidos). Es importante que las columnas que comparo sean idénticas (mayúsculas, tildes, etc...) ¿Es inevitable utilizar condicionales? ¿Hay alguna fórmula sencilla que lo solucione?"

En respuesta a las dos preguntas: No es necesario utilizar condicionales; y sí hay una fórmula muy sencilla que solucione el problema descrito. Empecemos como siempre poniendo un ejemplo:

Para comparar los nombres existentes en la lista 1 y en la lista 2 vamos a utilizar dos métodos distintos. Con el primero vamos a comparar si el texto introducido en, por ejemplo, la celda B4 es el mismo que el introducido en la celda C4, independientemente de si se han escrito con mayúsculas o no. Para ello nos situamos en la celda E4 y escribimos la siguiente fórmula:

=B4=C4

Los resultados posibles ante este tipo de fórmulas son VERDADERO o FALSO. En nuestro caso la solución será VERDADERO ya que el texto de B4 es el mismo que el de C4. Pero si lo que nos interesa es comprobar que si además de contener el mismo texto está escrito exactamente de la misma manera entonces tendremos que utilizar la función IGUAL. Para ello nos situamos en la celda F4 y escribimos:

=IGUAL (B4;C4)

La función IGUAL compara dos cadenas de texto y devuelve VERDADERO si son exactamente iguales y FALSO si no lo son. Esta función diferencia mayúsculas y minúsculas, pero pasa por alto las diferencias de formato. En la siguiente tabla puede ver distintos ejemplos de cadenas de texto y el resultado de aplicar el primer y el segundo método:

Diferenciar entre mayúsculas y minúsculas es una tarea bastante sencilla (aunque, evidentemente, es mejor automatizarla, especialmente si el número de entradas es muy elevado) pero no lo es tanto diferenciar cadenas de texto aparentemente iguales pero que contienen espacios antes o después de dicha cadena (localizar espacios en el medio de una cadena de texto también resulta bastante sencillo, en principio). Para evitar este problema puede utilizar la función ESPACIOS. Esta función elimina los espacios del texto, excepto el espacio normal que se deja entre palabras. Fíjese que las celdas B6 y C6 son aparentemente idénticas. Pero B6 contiene un espacio después del nombre. Para "limpiar" dicho espacio aplique la fórmula ESPACIOS (B6).

Generar Números Aleatorios sin Repeticiones

2010-05-14T01:26:00.008+02:00

Descargar el Archivo

"Necesito generar 10 números aleatorios entre 1 y 20 sin que se repita ninguno ¿Es posible sin hacer uso de VBA?"

Vamos a ver cómo solucionar esta problema sin hacer uso de macros. Para ello vamos a utilizar dos funciones: ALEATORIO y JERARQUIA.

La función ALEATORIO devuelve un número aleatorio mayor o igual que 0 y menor que 1, distribuido uniformemente. Cada vez que se calcula la hoja de cálculo, se devuelve un número aleatorio nuevo. No tiene argumentos por lo que su sintaxis es =ALEATORIO()

La función JERARQUIA puede consultarla en el post Jerarquía de un valor dentro de un rango o matriz.

1. Comenzamos construyendo la siguiente hoja:

2. En el rango B4:B23 vamos a generar 20 números aleatorios. Para ello seleccionamos dicho rango (B4:B23) y con el rango seleccionado comenzamos a escribir la siguiente fórmula:

=ALEATORIO() y pulsamos Ctrl + Enter. De esta manera rellenaremos de una sola atacada todo el rango seleccionado (también podemos escribir la misma fórmula en B4 y después copiar hacia abajo hasta la celda B23). El resultado es el que se muestra en la siguiente figura. Puede comprobar que pulsando la tecla F9 se recalculan todos los números aleatorios.

3. Seleccionamos nuevamente el rango B4:B23 y hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre Lista, y pulsamos Enter.

4. Nos situamos en la celda E3 y escribimos la fórmula:

=JERARQUIA(B4;lista)

De esta manera excel "ordenará" el valor aleatorio de B4 en función del orden que ocupe entre el 1 y el 20 ¿Por qué entre el 1 y el 20? Pues porque hemos generado 20 números aleatorios recogidos en el nombre de rango lista.

5. Copiamos la fórmula de E3 hacia abajo hasta la celda E12 y problema resuelto.

Pruebe a pulsar la tecla F9 para generar nuevos números aleatorios. Obviamente puede ocultar la columna B si no le interesa que se vean los 20 números aleatorios resultantes.

Moverse en un Rango sólo con el Ratón

2010-05-13T01:22:00.007+02:00

Uno de los blogs de excel que suelo visitar es Bacon Bits. Leyendo su artículo del 10 de mayo he descubierto un nuevo truco que desconocía: la posibilidad de moverte en un rango haciendo doble clics en los bordes de la celda seleccionada. Veámoslo con un sencillo ejemplo. Supongamos que tenemos la siguiente tabla:

Nos encontramos en la celda B4. Si hacemos doble clic en el borde inferior de la celda seleccionada excel nos desplaza a la última celda con contenido por debajo de la celda en la que se encuentra:

Lo mismo podemos hacer hacia la derecha, hacia la izquierda o hacia arriba, haciendo doble clic en el borde derecho, izquierdo o superior respectivamente.

A este truco le podemos "sacar un poco más de jugo" si pulsamos la tecla Shift mientras realizamos el doble clic. De esta forma seleccionaremos áreas a la derecha, izquierda, arriba o abajo rápidamente.

Número de Días de un Mes

2010-05-05T00:31:00.004+02:00

"Tengo una tabla con fechas y para periodificar unos datos necesito que a la derecha de la fecha me aparezca el número de días que tiene el mes de dicha fecha ¿Alguna solución sencilla?"

Por supuesto no problemo. Veamos el ejemplo de partida:

La solución es bien sencilla utilizando las funciones DIA y FIN.MES . En concreto vamos a anidar FIN.MES dentro de la función DIA. Seguimos los siguientes pasos:

1. En la celda C3 escribimos la siguiente fórmula:

=FIN.MES(B3;0)

La función FIN.MES tiene dos argumentos: Fecha_inicial y Meses. Fecha_inicial es el número de serie que representa la fecha inicial; Meses es el número de meses antes o después de la Fecha_inicial. En nuestro caso la fecha inicial es la fecha en cuestión que nos interesa y, por lo tanto, hay que añadirle cero meses. De esta manera la fórmula indicada calculará el último día del mes de la celda B3. Si ahora anidamos esta fórmula dentro de la función DIA (que nos devuelve el número de día de una fecha en concreto) ya tendremos el número de días del mes de la fecha en cuestión.

2. En la celda C3 añadimos a la fórmula que teníamos la función DIA. Debe quedar como sigue:

=DIA(FIN.MES(B3;0))

3. Nos situamos en la parte inferior derecha de la celda C3 y hacemos doble clic (copiado inteligente) para rellenar el resto de celdas. El resultado obtenido será el buscado:

Proteger Celdas y Hojas en Excel

2010-04-10T14:04:00.013+02:00

Vamos a ver cómo se pueden proteger celdas y hojas en excel.

1. Para proteger toda una hoja de un libro (archivo) de excel, sólo tenemos que ir directamente al menú Herramientas/Proteger/Proteger hoja:

Se abrirá la siguiente ventana:

Las opciones que vienen seleccionadas por defecto son "Seleccionar celdas bloqueadas", lo que nos permite situarnos en las celdas que protejamos pero no nos permitirá realizar ninguna modificación en ellas, y "seleccionar celdas desbloqueadas", que nos permitirá situarnos y modificar aquellas celdas que no protejamos. La lista de acciones que podemos realizar a pesar de que protejamos las celdas son, como se puede ver en la ventana de la imagen, muchas más (cambiar formato de celdas, de filas, de columnas, etcétera)..

2. Si deseamos introducir una contraseña para desactivar posteriormente la protección debemos escribirla en el recuadro "Contraseña para desproteger la hoja".

3. Pulsamos Aceptar tras seleccionar las opciones deseadas y ya está. Si a continuación intentamos escribir o borrar cualquier contenido de cualquier celda, excel no nos dejará y nos presentará un mensaje como el que sigue:

Cuando hacemos las cosas bien en excel, proteger celdas concretas es muy sencillo. Me refiero al uso del Modelo de Entrada-Salida de Datos. Para proteger celdas concretas de una hoja debemos seguir los siguientes pasos:

A) Seleccionamos las celdas que NO deseamos proteger, es decir, las entradas de datos.

B) Vamos al menú Formato/Celdas y seleccionamos la última pestaña de la ventana:

C) Desactivamos la opción Bloqueada y pulsamos Aceptar. Al volver a la hoja todavía no ha ocurrido nada. Si prueba a escribir en las celdas que, por ejemplo, contienen fórmulas verá que puede hacerlo sin problema.

D) Vamos al menú Herramientas/Proteger/Proteger hoja y pulsamos directamente Aceptar. A partir de este momento sólo podrá modificar las celdas de entradas de datos, mientras que sus fórmulas y resto de celdas vacías no podrán ser modificadas.

Sugerencia

En muchas ocasiones las entradas de datos son muy numerosas y se encuentran dispersas por distintas partes de la hoja. Seleccionar una a una podría ser un trabajo tedioso. Es el momento de usar el menú Edición/Ir a... y pulsar el botón, de la ventana que aparecerá, Especial. Excel nos presentará la siguiente ventana, donde debemos activar la opción Constantes (se refiere a las celdas con contenido pero sin fórmulas):

Al pulsar Aceptar excel habrá seleccionado todas nuestras celdas correspondientes a Entrada de Datos (y ya podríamos proceder desde el paso B para proteger celdas específicas).

Manejo de Decimales con Botones de Formulario

2010-03-23T02:14:00.010+01:00

Descargar el Archivo

"He utilizado la barra de desplazamiento de los botones de formulario para una entrada de datos en la que tengo que manejar porcentajes pero excel no me permite introducir dicho formato (decimal) en la configuración de la barra ¿Es que no se puede? ¿Hay alguna forma de solucionarlo?"

La configuración de los botones de formulario en excel es muy limitada. Los incrementos que nos permite tanto la barra de desplazamiento como el control de número han de ser números enteros (no permite decimales). Pero hay una manera muy sencilla de conseguir el mismo resultado.

1. Comenzamos introduciendo el rótulo y el valor cero en las celdas B3 y C3 respectivamente. En la celda D3 escribimos la fórmula =C3/100 y en la celda E3 dibujamos la barra de desplazamiento (la puede encontrar en el menú Ver/Barras de herramientas/Formularios):

2. Hacemos clic con el botón derecho del ratón encima de la barra de desplazamiento y seleccionamos la última opción del menú emergente: Formato de Control. Se abrirá la siguiente ventana:

3. Ponemos como valor mínimo el 0 y como máximo el 100, ya que vamos a manejar porcentajes, y vinculamos con la celda C3. Aceptamos.

4. Ya hemos conseguido que al pulsar nuestra barra de desplazamiento hacia la derecha la celda D3 cambio un punto porcentual cada clic. Ya sólo nos queda ocultar la columna C y objetivo conseguido.

Sugerencias:

A) Para evitar que escriban en la celda D3, ya que se trata de una fórmula y no de una entrada de datos, le sugiero que utilice el truco visto en mi anterior post "Proteger celdas con validación de datos".

B) Si desea que el cambio que se produzca en cada pulsación de la barra de desplazamiento sea menor que un punto porcentual en cada clic, en la celda D3 cambie el denominador de 100 a 1.000, si quiere que cada clic suponga un cambio de 10 puntos básicos (0,10) o cámbielo por 10.000 si quiere que cada pulsación suponga un cambio de un punto básico (0,01). En ambos casos deberá modificar también el valor máximo e la barra de desplazamiento a 1.000 y 10.000 respectivamente. También puede probar a dejar esta parte como variable y hacerlo depender de una entrada de datos en la que seleccione de una lista el cambio que necesita.

"Proteger" Celdas con Validación de Datos

2010-03-21T23:53:00.009+01:00

En muchas ocasiones resulta muy recomendable "proteger" ciertas celdas que contengan fórmulas o entradas de datos pasados que no queremos modificar por descuido. Normalmente se utiliza la herramienta específica de protección de excel para realizar esta labor. Pero existe una manera más sencilla y, sobre todo, más flexible para nuestro cometido. Se trata de una sencilla fórmula aplicada a la herramienta de Validación de datos que pasamos a ver a continuación.

Imagínese que tiene la siguiente tabla con fechas y ventas realizadas:

Lo que perseguimos es que excel no permita cambiar estas celdas que contienen las ventas realizadas o, mejor dicho, que antes de que se modifiquen estos datos excel "nos pregunte" si realmente las queremos modificar. A saber:

1. Seleccionamos el rango C4:C18 y vamos al menú Datos/Validación.

2. Elegimos Permitir/Personalizada e introducimos la siguiente fórmula: =""

3. Activamos la última pestaña, que es mensaje de error, y seleccionamos Estilo Advertencia. A la derecha podemos personalizar el mensaje de aviso que queremos que nos aparezca:

4. Pulsamos Aceptar y problema resuelto. A partir de este momento si cambia por error, por ejemplo, las ventas de marzo de 2010 antes de proceder a modificar la celda excel le mostrará el mensaje que usted haya diseñado y podrá decidir si continuar o no:

Este sistema es muy sencillo y práctico pero es importante resaltar que está pensado sólo para errores de sobreescritura, ya que si alguien copia y pega encima de estas celdas (las que teóricamente no queremos modificar) la validación no servirá para nada.

Texto más Repetido en un Campo

2010-03-21T10:46:00.012+01:00

Descargar el Archivo

"Tengo varios campos de una tabla que contienen texto en los que me gustaría saber cuál es el nombre más repetido. Si es posible, me gustaría que los más repetidos destacasen en un color".

Este problema es idéntico al cálculo de la MODA sólo que en este caso, al tratarse de texto, no podemos utilizar dicha función. Lo solucionaremos con las función CONTAR.SI y con la herramienta de Formato Condicional. Partimos del siguiente ejemplo:

1. Procedemos a crear el nombre de este rango. Seleccionamos B4:B25 y hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre Colores, y pulsamos enter.

2. Pasamos a generar una lista con los registros únicos a partir de la celda D4. Para realizar esta tarea puede consultar Copiar Registros Únicos en este blog. Obtenemos lo siguiente:

3. En la columna E vamos a proceder a contar el número de registros de cada taexto del rango colores. Para ello nos situamos en la celda E4 y escribimos la fórmula:

=CONTAR.SI(colores;D4)

Hacemos doble clic en la parte inferior derecha de la celda E4 y obtendremos el siguiente resultado:

De esta manera ya tendremos la información deseada de cuáles son los textos más repetidos.

4. Seleccionamos el rango Colores (B4:B25) y vamos al menú Formato/ Formato condicional y escribimos lo siguiente:

La fórmula que hemos escrito es:

=SI(BUSCARV(B4;$D$4:$E$8;2;)=MAX($E$4:$E$8);1;0)

De esta manera buscará cada texto del rango B4:B25, ya que lo tenemos preseleccionado, y comprobará si se corresponde con el valor máximo del rango E4:E8, que es donde hemos contado el número de repeticiones de cada texto. Si es, o son, el/los más repetido/s entonces lo/s sombreará con trama naranja:

Aclaraciones de la Función SUBTOTALES

2010-03-02T00:28:00.010+01:00

Me habéis realizado varias preguntas de las que deduzco que no queda suficientemente claro algún aspecto de la función SUBTOTALES (en concreto el primer argumento de esta función, Num_Funcion).

Tal y como vimos en el anterior post, la función SUBTOTALES tiene dos argumentos, a saber:

Num_funcion: Es un número del 1 al 11 o del 101 al 111 que indica que función debe ser aplicada a la lista. Las correspondencias de dichos números de función son las que se muestran a continuación:

Ref1: Es la referencia o rango de los que queremos calcular el subtotal (podemos incluir hasta 29 referencias o rangos).

Aclaraciones:

1. La función SUBTOTALES pasa por alto las filas que no se hayan incluido en el resultado de un filtro, independientemente del valor de núm_función que utilice.

2. Para las constantes núm_función de 1 a 11, la función SUBTOTALES incluye los valores de filas ocultos por el comando Ocultar en el submenú Fila del menú Formato. Puedes utilizar estas constantes cuando desees calcular subtotales ocultos y números visibles de una lista. Para las constantes núm_función de 101 a 111, la función SUBTOTALES pasa por alto los valores de filas ocultos por el comando Ocultar en el submenú Fila del menú Formato. Puedes utilizar estas constantes cuando sólo desees calcular subtotales de números visibles de una lista.

Para que no quede ninguna duda veámoslo con un ejemplo. Recuperando la tabla utilizada en mi anterior artículo, he añadido otro cálculo adicional de subtotales en el rango C10:D14 que se corresponde con los códigos del argumento Num_Funcion 109, 101, 104, 105 y 102, es decir para que la función SUBTOTALES pase por alto los valores ocultos por el comando Ocultar en el submenú Fila del menú Formato:

Si filtramos la tabla los resultados son idénticos independientemente del num_funcion que utilicemos (da igual poner 9 que 109 para la SUMA, por ejemplo):

Volvemos a nuestra tabla original y procedemos ahora a ocultar, por ejemplo, las filas 21 a 34. Entonces es cuando comprobamos la diferencia del uso de uno u otro código. En el primer caso realiza el cálculo con todos los valores de la tabla, incluyendo los no visibles, mientras que en el segundo caso realiza los cálculos sólo para los valores visibles:

3. La función SUBTOTALES está diseñada para columnas de datos o rangos verticales. No está diseñada para filas de datos ni para rangos horizontales.

4. La función SUBTOTALES no está diseñada para trabajar con referencias tridimensionales. En caso de existir este tipo de referencia devolverá valor de error #¡VALOR!

5. Si hay otros subtotales dentro del argumento ref1 (o subtotales anidados), estos subtotales anidados se pasarán por alto para no repetir los cálculos.

Uso de la Función SUBTOTALES

2010-02-27T02:37:00.020+01:00

Descargar el Archivo

"Tengo una tabla con mucha información sobre la que aplico habitualmente filtros para realizar cálculos ¿Hay alguna manera de, por ejemplo, sumar sólo las celdas que aparecen una vez filtrada la tabla?"

Para realizar esta labor vamos a ver la función SUBTOTALES. Partimos del siguiente ejemplo:

Como se puede apreciar en la imagen, he aplicado autofiltros a la tabla. Para ello sólo tenemos que situarnos en cualquier celda de dicha tabla, debajo de los rótulos (nombres de campo), e ir al menú Datos/Filtro/Autofiltro. La función SUBTOTALES está especialmente pensada para realizar cálculos en tablas. Veamos cómo:

1. Disponemos la siguiente salida de datos encima de nuestra tabla:

2. En la celda C2 escribimos la fórmula:

=SUBTOTALES(9;C$9:C$28)

La función SUBTOTALES tiene dos argumentos, a saber:

Ref1: Es la referencia o rango de los que queremos calcular el subtotal (podemos incluir hasta 29 referencias o rangos).

De esta manera, la fórmula =SUBTOTALES(9;C$9:C$28) calculará la SUMA (el 9 es el número de función correspondiente a la suma) del rango C9:C28. Pero con una peculiaridad (ya que en caso contrario podríamos realizar directamente el sumatorio) y es que si procedemos a filtrar la tabla realizará la suma de los valores visibles. Para comprobarlo procedemos a filtrar el campo denominado Variable. Vamos a ver sólo aquellos cargos que tienen como sueldo variable 20.000€:

Como puede apreciar en la imagen, hay seis cargos con este variable y la función SUBTOTALES de la celda C2 nos muestra el sumatorio del sueldo bruto pero precisamente de sólo esos seis cargos (y no del total de la tabla).

3. Copiamos la fórmula de C2 en el rango C3:C6 y procedemos a sustituir el número de función para que realice el cálculo correspondiente:

En C3 =SUBTOTALES(1;C$9:C$28)

En C4 =SUBTOTALES(4;C$9:C$28)

En C5 =SUBTOTALES(5;C$9:C$28)

En C6 =SUBTOTALES(2;C$9:C$28)

4. Como hemos utilizado referencias mixtas en el rango de la función podemos proceder a copiar el rango C2:C6 en D2:D6. El resultado es el que podemos ver en la siguiente imagen:

Puede comprobar que si cambiamos el filtro y le pedimos que, por ejemplo, nos muestre los cargos con un sueldo variable mayor o igual que 15.000€ y menor o igual que 25.000€, la función SUBTOTALES recalculará y mostrará los siguientes resultados:

Truco en el Copiado Inteligente por Bloques

2010-02-25T00:20:00.009+01:00

Descargar el Archivo

"Tengo una tabla con datos de ingresos y gastos mensuales. Necesito calcular el margen bruto bimestral. He utilizado el sistema de copiado inteligente por bloques. A continuación he necesitado añadir una nueva columna de reporte con el mismo dato del margen bruto pero en porcentaje. El problema es que como las primeras celdas de la izquierda están en blanco no funciona el copiado inteligente por bloques ¿Hay alguna manera de solucionarlo?"

Hay una manera muy sencilla y rápida. Para entender mejor el problema recomiendo la lectura del post Copiado Inteligente . Partimos del siguiente ejemplo:

Queremos añadir en la columna F el dato del margen bruto en porcentaje. Para ello realizamos los siguientes pasos:

1. En la celda F4 escribimos la fórmula =E4/C4

2. Si quisiéramos aplicar el copiado inteligente por bloques seleccionaríamos el rango F3:F4 y haríamos doble clic en la parte inferior derecha de la selección. Si lo hacemos no ocurre nada ya que la celda E3 está en blanco. Y este es precisamente el problema que me planteáis.

3. SOLUCIÓN: Ocultamos la columna E.

4. Seleccionamos el rango F3:F4 y ahora sí aplicamos el copiado inteligente por bloques como indico en el punto 2:

5. Volvemos a mostrar la columna E y problema resuelto:

Campos Calculados en Tablas Dinámicas

2010-02-10T00:55:00.013+01:00

Siguiendo las indicaciones de tu DNI "oficialmente" hoy es tu día y eso nadie lo puede discutir...

¡¡ FELICIDADES NESO !!

"La vida es muy dura"

Y ahora a trabajar que hay que levantar el país...

Descargar el Archivo

"Cada día recibo una tabla dinámica con los datos clasificados por zonas referentes a las ventas y a sus costes asociados. Sólo recibo la tabla dinámica (no la tabla de origen) y me gustaría realizar algunos cálculos con estos campos, tales como calcular el margen y el porcentaje de los costes sobre los ingresos ¿Hay alguna manera de hacerlo dentro de la propia tabla dinámica para que si luego reordeno los campos siga funcionando correctamente?"

Mi sufrido lector, la solución es bastante sencilla. Para ello vamos a utilizar la herramienta de las Tablas Dinámicas Fórmulas / Campo calculado. Supongamos que partimos del siguiente ejemplo:

1. Nos situamos en cualquier celda de la Tabla. En la barra de herramientas de Tablas Dinámicas abrimos el menú desplegable Tabla dinámica y seleccionamos Fórmulas / Campo calculado:

Aparecerá la siguiente ventana:

2. En el campo Nombre escribimos el título que deseemos dar al campo que vamos a calcular. En nuestro ejemplo utilizaremos Margen.

3. En el campo Fórmula escribiremos la fórmula a calcular. Para realizar esta tarea podemos hacer doble clic encima de los nombres que tenemos en el cuadro Campos (o seleccionar el campo en cuestión y pulsando después el botón Insertar campo). En nuestro caso la primera fórmula será: =Ingresos-C.Directo

4. Pulsamos Aceptar. Fíjese que en la ventana de Lista de campos de la tabla dinámica aparecerá ahora un nuevo campo denominado Margen:

y nuestra Tabla Dinámica mostrará lo siguiente:

Puede proceder a continuación a "reordenar" los campos de la tabla y verá que los cálculos siguen siendo correctos. Por ejemplo:

Eliminar Celdas en Blanco

2010-02-09T06:41:00.010+01:00

"Todas las semanas importo un listado de ventas a excel. El resultado que obtengo presenta varias filas en blanco intercaladas ¿Existe alguna manera rápida de borrar dichas filas?"

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

1. Seleccionamos el rango B3:C19

2. Vamos al menú Edición/Ir a (o directamente la tecla F5) y pulsamos el botón Especial.

3. Seleccionamos la opción Celdas en Blanco y aceptamos.

4. Pulsamos la combinación de teclas Ctrl - (tecla control y el signo menos). Se abrirá la siguiente ventana en la que seleccionamos la opción Toda la fila:

El resultado obtenido será el que se muestra a continuación:

Enumerar Listas con Celdas Ocultas y Condiciones

2010-01-28T20:30:00.014+01:00

Descargar el Archivo

"Mil gracias Kiko. Aprovechando el ejemplo de tu anterior artículo, qué ocurre si hay una fila de separación entre cada cinco o seis filas y la numeración no debe ir en las celdas en donde la celda de la derecha no hay valor".

En esta ocasión vamos a solucionar el problema con un par de fórmulas y las funciones ESNUMERO y CONTAR.

Partimos del siguiente ejemplo:

Se trata de generar una enumeración a partir de la celda B3 sin que afecten las celdas ocultas y siempre que en la columna C haya valores.

1. Nos situamos en la celda E3 y escribimos la siguiente fórmula:

=SI(ESNUMERO(C3);CONTAR($C$3:C3);0)

La función ESNUMERO comprueba si la celda de referencia (en nuestro ejemplo C3) es un número o no. Los resultados posibles son VERDADERO ó FALSO. La función CONTAR cuenta el número de celdas que contienen números en el rango indicado.

2. Copiamos la fórmula de E3 en el rango E4:E31 (en nuestro ejemplo). Obtendremos el siguiente resultado:

3. Nos situamos en la celda B3 y escribimos la siguiente fórmula:

=SI(ESNUMERO(C3);E3;"")

4. Copiamos la fórmula de B3 en el rango de nuestro ejemplo B4:B31 y problema resuelto:

Evidentemente podemos ocultar la columna E (o podríamos haberla desarrollado en otra hoja) para que no afecte a la presentación de la información.

Rellenar Series con Celdas Ocultas

2010-01-28T01:29:00.010+01:00

"Tengo que realizar una enumeración del 1 al 1.500. El problema es que tengo unas 2.000 filas que deben estar ocultas y que no deben entrar en la enumeración. Si uso rellenar termino con 3500 en lugar de las 1500 ¿Alguna solución?"

La solución es muy sencilla. Para resolver este problema utilizaremos la herramienta Ir a Especial y Rellenar Series. Para hacer más manejable el ejemplo vamos a generar una lista del 1 al 20 ocultando 5 filas.

1. Procedemos a ocultar las filas número 11, 12, 13, 14 y 15 por ejemplo.

2. Nos situamos en la celda B2 y escribimos un uno.

3. Seleccionamos un rango que contenga 25 filas, ya que vamos a generar una lista del 1 al 20 pero hemos ocultado 5 filas. Para realizar la selección de filas podemos fijarnos en el cuadro de nombres (a la izquierda de la barra de fórmulas) ya que cuando procedemos a seleccionar un rango nos va indicando cuántas filas y columnas estamos seleccionando.

4. Vamos al menú Edición/Ir a y en la ventana que se abre pulsamos el botón Especial. En la ventana que se abre marcamos Sólo celdas visibles y aceptamos.

5. Vamos al menú Edición/Rellenar/Series. Se abrirá la siguiente ventana:

6. Seleccionamos Series en Columnas, ya que deseamos que la enumeración aparezca en columna; Tipo lineal; Incremento 1; y Límite 20 (ya que vamos a realizar una lista del 1 al 20).

7. Pulsamos Aceptar y problema resuelto (he puesto un relleno en azul en las celdas A10 y A16 para destacar que las filas 11 a 15 están ocultas):

Rellenar Celdas en Blanco con Contenido de Celda Anterior

2010-01-15T02:44:00.010+01:00

"A menudo tengo que copiar parte de una tabla dinámica y el resultado (ejemplo reducido) es el que muestro en la imagen:

Necesito rellenar las celdas en blanco con los respectivos rótulos. Manejo más de 500 filas ¿hay alguna manera rápida de efectuar esta tediosa tarea?"

Como casi siempre, hay varias formas de realizar esta labor. Vamos a solucionarlo utilizando la herramienta Ir a Especial.

1. Seleccionamos el rango B2:B14 y vamos al menú Edición/Ir a y pulsamos el botón Especial. Se abrirá la siguiente ventana en la que debemos marcar el botón de opción Celdas en blanco:

Al pulsar Aceptar excel selecciona automáticamente las celdas en blanco del rango que teníamos seleccionado:

2. Sin modificar esta selección, escribimos la fórmula:

=B2 que es la primera celda en la que tenemos un rótulo.

3. Acabamos pulsando Ctrl + Enter y problema resuelto:

Copiar Registros Únicos

2010-01-15T02:10:00.008+01:00

"Tengo una lista con 200 entradas de tres dígitos que representan el código de referencia de unos productos. En dicho listado hay códigos que se repiten en múltiples ocasiones. Lo que necesito es generar una lista con todos los códigos únicos".

Existen diversas maneras de resolver este problema. La solución que propongo a continuación (utilizar Filtro Avanzado) me parece la más rápida y sencilla. Supongamos que tenemos la lista que se muestra en la imagen:

Como se puede observar hay códigos que se repiten en varias ocasiones (el 108,118, 120, el 103, etc.). Debemos seguir los siguientes pasos:

1. Nos situamos en cualquier celda por debajo del rótulo, es decir, en cualquier celda del rango B3:B24 y vamos al menú Datos/Filtro/Filtro Avanzado.

2. Aparecerá la siguiente ventana:

Tal y como se muestra en la imagen, debemos ´marcar el botón de opción Copiar a otro lugar. Como Rango de la lista seleccionamos nuestro rango, es decir B2:B24. Rango de criterios lo dejamos en blanco ya que no existen en este caso concreto. En Copiar a marcamos la primera celda a partir de la cual queremos que aparezca nuestra lista con códigos no repetidos, por ejemplo seleccionamos D2. Finalmente marcamos la casilla de verificación Sólo registros únicos y pulsamos Aceptar.

3. El resultado de esta operación será el buscado:

Menú Contextual de Etiquetas de un Libro

2010-01-14T01:21:00.009+01:00

"Tengo un archivo (libro) que contiene 14 hojas con sus correspondiente etiquetas. Cuando me quiero desplazar de una hoja a otra tengo que ir pulsando los botones para desplazar fichas ¿Existe alguna solución más cómoda y rápida para ver y desplazarse por las distintas hojas de un libro?"

Este es un problema (incomodidad, mejor dicho) tan habitual como fácil de resolver. Supongamos que tenemos un libro con distintas hojas:

La forma más habitual en la que los usuarios se desplazan entre las distintas hojas es la de ir pulsando los botones de desplazamiento de ficha (hoja o etiqueta):

La solución más rápida y cómoda para realizar esta tarea es muy sencilla pero, por desgracia, bastante desconocida. Sólo tiene que situarse encima de dichos botones (botones para desplazar fichas, situados a la izquierda de las etiquetas de las hojas) y hacer clic en el botón derecho del ratón (botón secundario del ratón). Automáticamente se abrirá un menú contextual con una lista de todas las hojas que tenga su libro. Para ir a cualquiera de ellas sólo tiene que seleccionarla en dicha lista:

Seleccionando Celdas con Fórmulas

2010-01-13T23:47:00.003+01:00

Acaba de nacer mi ahijado Jacobo, hijo de mi queridísimo hermanito Javier y, por supuesto, de mi supercuñadita Emma.

¡¡ FELICIDADES PAPÁS !!

Aprovecho también para desearos a todos los que tenéis el humor y la paciencia de leerme que el 2010 haga honor a sus dos últimos dígitos. Y ahora a trabajar un poco...

" ¿Existe alguna manera de seleccionar todas aquellas celdas en las que haya fórmulas?"

Si necesitáis localizar todas aquellas celdas de vuestra hoja que contengan fórmulas sólo tenéis que utilizar la herramienta Ir a Especial. Supongamos que tenemos el modelo que se muestra en la imagen:

Necesitamos que excel seleccione las celdas que contienen fórmulas. Para ello vamos al menúEdición/Ir a. Se abrirá la siguiente ventana:

Pulsamos el botón Especial y aparecerá la siguiente ventana:

Pulsamos la tercera opción: Celdas con fórmulas y aceptamos. El resultado es el que se puede observar en la imagen. Excel ha seleccionado todas aquellas celdas que contienen una fórmula (en este ejemplo son las celdas C5, C8, B11 y el rango G3:G17)

Tipos de Fórmulas:

Dentro de la opción Celdas con Fórmulas podemos observar 4 subopciones, a saber: Números;Texto;Valores Lógicos; ó Errores. Por defecto aparecen los cuatro tipos marcados.

Números: Fórmulas que devuelven valores numéricos
Texto: Fórmulas cuyo resultado es una cadena de texto.
Valores Lógicos: Fórmulas cuyo resultado es un valor lógico, es decir, VERDADERO o FALSO.
Errores: Fórmulas cuyo resultado es algún tipo de error (#N/A, DIV/0, NOMBRE?, #¡REF!, etcétera).

Si necesitamos localizar las celdas que contienen fórmulas pero sólo aquellas que devuelvan un resultado concreto podemos marcar la opción que deseemos y excel hará el resto del trabajo.

Evitar Valores Cero y Blancos en un Gráfico

2009-12-20T01:12:00.009+01:00

"Tengo que realizar un gráfico y tengo algunas celdas que contienen el valor cero y otras están en blanco ¿es posible que no aparezcan dichos valores en el gráfico?"

Sí es posible. Lo solucionaremos por medio de la función NOD. El problema planteado es el siguiente:

Como se puede comprobar existen dos celdas con el valor cero y una celda en blanco. Si realizamos un gráfico de esta tabla el resultado obtenido será:

Lo que pretendemos conseguir es que excel "ignore" estos valores y realice una gráfica continua (interpole). Para ello hacemos lo siguiente:

1. Nos situamos en la celda D3 y escribimos la siguiente fórmula:

=SI(O(C3=0;C3="");NOD();C3)

La función NOD() no tiene argumentos. Simplemente devuelve el error #N/A. Aunque pueda parecer inútil, será precisamente la solución a nuestro problema, ya que excel interpola automáticamente este tipo de valor en los gráficos.

2. Copiamos la fórmula de D3 hasta D14:

3. Realizamos nuestro gráfico utilizando los rangos B3:B14 y D3:D14. El resultado obtenido será el deseado:

Frecuencia por Rango de Edades

2009-12-19T23:16:00.010+01:00

Descargar el Archivo

"Tengo una tabla con las edades de distintas personas encuestadas y me gustaría agruparlas por rangos de edades y calcular cuántas personas se encuentran en cada grupo".

No problemo. Utilizaremos la función FRECUENCIA en Fórmula Matricial. Partimos de los siguientes datos:

Queremos clasificar toda esta información en los siguientes grupos de edades:

Es decir, queremos saber cuántas valores están comprendidos en cada rango. Para ello utilizaremos la función FRECUENCIA. Dicha función tiene dos argumentos, a saber =FRECUENCIA(Datos;Grupos). Se trata de indicarle en qué Grupos queremos organizar los Datos. Para ello lo primero que necesitamos saber es cómo trasladar los grupos a la hoja. El formato mostrado en el rango H3:H8 no es válido para excel. Para que excel pueda entender la información debemos realizar la siguiente tabla:

Como puede comprobar, se trata de trasladar el límite superior de cada rango de manera ordenada (obviamente muestro la información de la columna H sólo para facilitar la comprensión, pero no es necesario tenerla en la hoja).

Una vez presentada la tabla hacemos lo siguiente:

1. Seleccionamos el rango J3:J8

2. Con este rango seleccionado escribimos la siguiente fórmula (se trata de una fórmula matricial y, por lo tanto, acabaremos pulsando la combinación de teclas Ctrl + Shift + Enter):

{=FRECUENCIA(B3:F16;I3:I8)}

El rango B3:F16 contiene el conjunto de datos que deseamos clasificar. Por otro lado, el rango I3:I8 contiene los grupos dentro de los que queremos clasificar los datos. El resultado será:

El significado es sencillo: 9 de los encuestados se encuentran en el rango de edad de 18 a 25 años; otros 9 en el rango 26 a 30 años; 7 tienen entre 31 y 35 años; etcétera. Es importante recalcar que hemos seleccionado el rango de salida de datos J3:J9 antes de empezar a escribir la fórmula. Al tratarse de una entrada matricial excel nos devolverá todos los resultados "de golpe" sin necesidad de copiar/pegar nada.

Cálculos a Partir de un Valor Mínimo

2009-12-18T23:35:00.008+01:00

Descargar el Archivo

"Necesito realizar el promedio de una serie de números que se encuentran en una tabla pero quiero realizar el promedio de aquellos que superan un valor mínimo".

Para resolver este problema vamos a utilizar las funciones PROMEDIO y SI en Fórmula Matricial. Partimos del siguiente ejemplo:

En C2 hemos establecido un valor mínimo y queremos calcular el promedio de cada una de las clases pero sólo de aquellos valores que superen dicho mínimo.

Nos situamos en la celda B22 y escribimos la siguiente FÓRMULA MATRICIAL (lo que implica que al terminar de escribir la fórmula, en vez de pulsar Enter debemos pulsar la combinación de teclas Ctrl + Shift + Enter):

{=PROMEDIO(SI(B$5:B$20>=$C$2;B$5:B$20))}

De esta manera excel calcula el promedio de aquellos valores del rango B5:B20 que superen o sean iguales al valor indicado en C2. Puede copiar la fórmula de B22 en C22 y D22 para calcular el promedio de cada clase.

Esta misma fórmula la podríamos adaptar para realizar el cálculo de la SUMA, CONTAR o, por ejemplo, calcular el MINIMO desde un valor:

En B24 {=SUMA(SI(B$5:B$20>=$C$2;B$5:B$20))}

En B25 {=CONTAR(SI(B$5:B$20>=$C$2;B$5:B$20))}

En B26 {=MIN(SI(B$5:B$20>=$C$2;B$5:B$20))}

Cálculo Repetición de un Día de la Semana entre Fechas

2009-12-12T16:46:00.025+01:00

Descargar el Archivo

"Necesito saber cuántos domingos hay entre dos fechas determinadas, siendo, evidentemente, dichas fechas variables".

No existe ninguna función específica para resolver directamente este problema, por lo que tendremos que realizar algunos pasos y fórmulas. Vamos a desarrollar el modelo para calcular cuántos domingos, o cualquier otro día de la semana que queramos especificar, hay entre dos fechas determinadas.

Lo primero que vamos a realizar es un calendario diario de tos el año 2009. Evidentemente si las fechas que necesitemos manejar se encuentran entre 2009 y 2010, por ejemplo, entonces deberíamos realizar dicho calendario diario. Para ello nos situamos en la celda G1 y escribimos 1/1/2009. En G2 escribimos la fórmula =G1+1 y copiamos dicha fórmula hasta que aparezca el 31/12/2009 (que terminará lógicamente en la fila 365):

Como se puede apreciar en la imagen le hemos dado el formato de fecha en el que aparece también el nombre del día de la semana. Seleccionamos el rango H1:H365 y le damos el nombre tablafecha.

Preparamos ahora la entrada de datos de la forma que se muestra en la imagen e introducimos dos fecha, por ejemplo del 7 de Febrero al 15 de Mayo:

A continuación nos situamos en la celda H1 y escribimos la siguiente fórmula:

=RESIDUO(G1;7)

Hacemos doble clic en la parte inferior derecha de la celda H1 (copiado inteligente) y la fórmula se copiará hasta la fila 365. Si nos fijamos en esta nueva columna calculada la función RESIDUO nos devuelve el mismo resultado para cada uno de los días de la semana. A saber:

Preparamos la siguiente tabla:

Seleccionamos el rango J2:K8 y le damos el nombre diaresiduo.

Nos situamos en C4 y vamos a Datos/Validación. Seleccionamos permitir Lista y en Origen seleccionamos el rango J2:J8. De esta manera ya tendremos nuestra lista desplegable en la celda C6 para elegir el día de la semana.

En la celda B6 escribimos la fórmula =C4

A continuación necesitamos realizar algunos "cálculos intermedios" para llegar a nuestra solución. Para ello preparamos las siguientes celdas:

Nos situamos en K12 y vamos a calcular la fila de la tabla de fechas en la que se encuentra la fecha inicial. Para ello escribimos la siguiente fórmula:

=COINCIDIR(C2;tablafecha)

en K13 escribimos (para calcular la fila con la que se corresponde la fecha final):

=COINCIDIR(C3;tablafecha)

Ya tenemos la fila que se corresponde con las fechas de inicio y de fin introducidas en C2 y C3. Ahora, en K14, concatenamos estos resultados para generar un rango:

="H"&K11&":"&"H"&K12

Fíjese que la letra de la columna la hemos introducido como dato (no la calculamos) porque todas las fechas se encuentran en la columna H. El resultado de la fórmula indicada será el texto: H38:H135. También podríamos escribir estas tres fórmulas que acabamos de realizar en una sola:

="H"&COINCIDIR(C2;tablafecha)&":"&"H"&COINCIDIR(C3;tablafecha)

Para calcular cuántos, por ejemplo, domingos hay entre las fechas 7 de febrero y 15 de mayo, sólo nos queda contar cuántas veces se repite entre dichas fechas el número de residuo

Una vez hecho esto, nos situamos en la celda C6 y escribimos la siguiente fórmula que explicamos a continuación:

=CONTAR.SI(INDIRECTO(K16);"="&BUSCARV(C4;diaresiduo;2;FALSO))

La función CONTAR.SI nos permite realizar la cuenta en un rango de aquellas celdas que cumplan una determinada condición. El rango que nos interesa (en función de las fechas introducidas) es el que tenemos en la celda K16. El problema es que en K16 tenemos un texto que representa a un rango. Para convertir dicho texto en referencia valida para excel debemos utilizar la función INDIRECTO. De esta manera INDIRECTO(K16) es lo mismo que si introdujéramos manualmente el rango H38:H135 .

Una vez tenemos el rango considerado nos queda establecer la condición que debe cumplir para que excel proceda a contar. La manera de hacer esto es poniendo la condición entre comillas. Por ejemplo, si queremos que cuente el número de celdas del rango H38:H135 que son igual a 1 escribiríamos =CONTAR.SI(H38:H135;"=1") . Ya hemos visto como resolver la parte del rango de manera automática (con la función INDIRECTO). Si dejáramos la solución así sólo podríamos calcular el número de domingos entre dos fechas. Como queremos tener la posibilidad de calcular el número de repeticiones de cualquier día de la semana entre dos fechas, necesitamos dejar como variable la condición. En realidad sólo necesitamos dejar como variable el residuo de la condición. Como ya hemos visto, cada día de la semana se corresponde con un residuo. Dicha información la tenemos en la tabla que hemos llamado diaresiduo (J2:K8). Por lo tanto podemos asociar el día de la semana seleccionado en la celda C4 con el número de residuo de dicho día). Esto es lo que hace la parte de la fórmula BUSCARV(C4;diaresiduo;2;FALSO)

El resultado será:

Contar Fechas

2009-11-27T01:50:00.025+01:00

Hoy no puedo estar más feliz... Mi hijo Miguel cumple 2 añitos (todo un personaje...)

¡¡ FELIZ CUMPLEAÑOS HIJO !!

"Tengo una lista de fechas con formato 27/11/2009 y quiero contar el número de fechas de mi lista que pertenecen a un determinado mes"

Partimos del siguiente ejemplo:

Una manera sencilla de solucionar este problema es con Tablas Dinámicas:

1. Nos situamos en E3 y vamos a Datos/Informe de Tablas y gráficos dinámicos:

2. Pulsamos Siguiente.

3. Seleccionamos el rango E2:E22:

4. Seleccionamos Diseño:

5. Arrastramos el campo Fecha a la zona DATOS y también a FILA. Aceptamos y pulsamos Finalizar:

6. Obtendremos la siguiente Tabla Dinámica:

7. Nos situamos en cualquier celda del rango A5:A24 y pulsamos el botón derecho del ratón. En el menú emergente seleccionamos Agrupar y mostrar detalle/Agrupar. En la ventana que aparecerá realizamos la siguiente elección y aceptamos:

8. El resultado obtenido será el objetivo buscado:

Solución con FÓRMULAS

1. Nos situamos en F3 y escribimos la fórmula: =MES(E3)

2. Hacemos doble clic en la esquina inferior derecha de la celda F3 (Copiado inteligente) y obtendremos el siguiente resultado:

3. Preparamos la siguiente entrada de datos en B2:C2 para introducir el número de mes que deseamos contar (puede incluir un Control de número en C2 para facilitar el uso). Nos situamos en C4 y escribimos la fórmula que puede ver en la barra de fórmulas de la imagen:

De esta manera obtendremos el número de veces que se repite en nuestra tabla el mes que escribamos en C2.