martes, 23 de marzo de 2010

Manejo de Decimales con Botones de Formulario



"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.

domingo, 21 de marzo de 2010

"Proteger" Celdas con Validación de Datos


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


"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:


martes, 2 de marzo de 2010

Aclaraciones de la Función SUBTOTALES


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.