Crear una nueva barra de herramientas

Resulta un proceso sencillo. En pocos pasos podemos crear una barra de herramientas totalmente personalizada de acuerdo a nuestras necesidades. Vamos a ver los pasos que hay que hacer para crearla.

En primer lugar crearemos dos macros que guardaremos. A continuación le asignaremos botones que colocaremos en la nueva barra.

Para grabar una macro seguiremos los pasos que nos indica la siguiente imagen:

Se abre la ventana Grabar macro que nos pide el nombre que le damos a la macro. Le damos el nombre de MostrarMensaje. Recordamos que el nombre de una macro no puede tener espacios. Pulsamos en aceptar.

A partir de ahora todas las acciones que realizamos quedarán grabadas en la macro. Nos colocamos en la celda D4 y escribimos el texto Saludos a todos.

Para detener la grabación pulsamos en el primer botón de la barra de herramientas Detener macro:

 Ya tenemos una macro. Ahora la vamos a pasar como un botón a una nueva barra de herramientas. Para crearla vamos a Herramientas / Personalizar. Y pulsamos en el botón Nueva.

 En la ventana que aparece, daremos nombre a la nueva barra de herramientas, la podemos llamar Mis funciones:

Al pulsar en el botón aceptar aparece en pantalla la nueva barra, muy pequeña y sin botones. Ahora sólo queda darle contenido, añadir botones que ejecuten macros o funciones incorporadas por Excel. En nuestro caso vamos a colocar un botón que nos permita ejecutar la macro que acabamos de crear.

Aspecto de la nueva barra de herramientas:

El título de la barra no se ve al completo debido a que no hay botones. Para añadir un botón debemos ir a Herramientas /Personalizar, hacemos clic en la pestaña Comandos, y ahora arrastramos los elementos que compongan nuestra barra de herramientas. Para añadir la macro, en el panel de categorías elegimos macros y en el segundo panel tenemos Personalizar botón. Lo arrastramos a nuestra barra de herramientas.

Cerramos la ventana Personalizar.

 

Ahora hay que asociar al botón a nuestra macro. Hacemos clic en el botón recién incorporado. Se abre la  ventana Asignar macro donde seleccionamos MostrarMensaje y pulsamos en aceptar. Esta es la forma que tenemos para asignar botones a una macro en cualquier barra de herramientas. Ahora ya podemos pulsar en el botón para que se ejecuten todas las instrucciones que componen la macro. En este caso, que aparezca la frase Saludos a todos y con un formato determinado. Por supuesto, podemos colocar más botones a la barra de herramientas, pero siempre a través de la ventana Personalizar. También podemos modificar la imagen del botón. Es interesante pues así podemos dar a cada macro un botón con una imagen diferente al de otras macros.

Botones para desplazarse entre hojas

Las etiquetas de las hojas de cálculo nos permiten desplazarnos de una hoja a otra de nuestro libro de cálculo. Sí además, las nombramos ubicaremos los contenidos de cada una más rápidamente. También podríamos darles color para distinguirlas. Pero en este post vamos a hacer lo mismo de una forma diferente. Vamos a ubicar botones en las hojas para desplazarnos de una a otra.

El primer paso es habilitar el menú Cuadro de controles. Pinchamos con el botón derecho en la zona de las barras de herramientas y se despliega un menú con la relación de barras de herramientas, donde seleccionamos Cuadro de controles:

Ahora la tenemos disponible y podemos introducir botones y configurar código para que trabajen como nos interesa. Para comprobar que funcionan correctamente vamos a colocar texto en dos celdas de hojas distintas. En hoja1, en la celda D3 escribimos y damos formato al texto Hoja de ventas:

En hoja2, en la celda D3 escribimos y damos formato al texto Hoja de compras:

Ahora vamos a poner un botón de comando en la hoja1 y otro en la hoja2. Quedará solo darles el código a cada uno de ellos. En hoja1 hacemos clic en el botón de comando de la barra de herramientas Cuadro de controles:

Lo llevamos a la hoja y arrastramos el ratón para que tenga un tamaño.

Ahora hacemos doble clic sobre el botón, que activa la ventana de Microsoft Visual Basic:

La primera fila escrita nos indica que cuando se efectúa el evento clic sobre el botón se ejecutará el código escrito en medio. El código que vamos a escribir es el siguiente:

Hoja2.Activate

Como interesa que nos lleve a la hoja de compras, escribimos el nombre de la etiqueta, Hoja2 y después del punto seleccionamos la acción que queremos que ocurra con la hoja2, activarla, de esta manera se hará activa y será la que veamos.

Modificamos la propiedad Caption del botón para que tenga otro texto, distinto al inicial. Haremos clic con el botón derecho sobre el botón

Aparece la ventana de propiedades donde daremos el nuevo nombre al botón:

En Caption escribimos Ir a compras. Repetiremos las mismas acciones para insertar otro botón en la hoja2 para volver la Hoja1 (hoja de ventas). Imagen de ambos botones:

El código del segundo botón será:

Hoja1.Activate

Sólo nos queda salir del modo diseño para poder operar con los botones. Pinchamos en el botón Salir modo diseño:

Desaparecen los puntos de control de los botones y ya podemos pulsar en ellos para desplazarnos entre  ambas hojas de cálculo.

Como comparar rangos en Excel 2007

Cuando de tenemos que comparar, determinar que elementos no coinciden en dos grandes grupos de datos, nos puede costar tiempo determinarlo con exactitud. Con este post pretendemos simplificar esta tarea para que la comparación no sea una tarea costosa y que no nos lleve demasiado tiempo.

Vamos a suponer que tenemos los siguientes datos a comparar:

En primer lugar vamos a seleccionar el rango de celdas A1:C6 que corresponde a las celdas que ocupan la primera tabla. La celda A1 será la celda activa en este momento. Ahora podemos avanzar. Abrimos el formato condicional en el ribbon o cinta Inicio donde nos dirigimos a Estilos / Formato condicional. Seleccionamos fórmula en el cuadro de la lista desplegable.

En Editar una descripción de regla escribimos:

=NO(A1=E1)

Si el contenido de A1 coincide con el contenido de E1 entonces esa igualdad es verdad. La función No la cambia a falso. Y lo mismo sucede al revés. si la igualdad es falsa la función No devuelve verdadero. En nuestro ejemplo buscamos celdas que sean distintas para que la función devuelva verdadero y así el formato condicional cambia su apariencia. Ahora pulsamos en el botón formato que nos lleva a una ventana que nos permite elegir el formato que tendrán las celdas que no coincidan en ambas tablas.

Una vez seleccionado todas las características del formato ya podemos pulsar en aceptar en las dos ventanas. El resultado será que todas las celdas distintas aparecen con el formato que le hemos dado. En nuestro ejemplo:

Como se observa, algunas celdas han cambiado su apariencia. Son distintas a la otra tabla. Si dentro de la tabla modificamos cualquier dato, si son distintos a los de la otra tabla, entonces pasarán a tener el formato establecido en el formato condicional. En grandes tablas esta herramienta permite visualizar rápidamente aquellos datos que difieren entre los rangos comparados.

Rangos dinámicos con desref

En un post anterior vimos un poco el funcionamiento de desref. Ahora vamos a avanzar un poco más. Desref nos permite crear rangos dinámicos. Así si la información de un rango aumenta o disminuye lo que deseamos es que se tengan esas modificaciones cada vez que se produzcan. Con un ejemplo lo veremos mejor.

Supongamos que tenemos un rango de datos, por ejemplo A1:A3

Vamos a Insertar / Nombre / Definir y en el cuadro de texto Nombres en el libro: escribimos RangoDin. En el cuadro de texto Se refiere a: ponemos:

=desref(A1;0;0;contara(A1:A100);1)

Al seleccionar las celdas para añadir el rango automáticamente sale con referencias absolutas, la imagen es:

Como argumento hemos introducido la función contara que nos va permitir que el rango solo sea el conjunto de celdas contiguas y con datos. pulsamos en aceptar y ya el rango está definido.

Para ver cuantas celdas ocupa el rango, escribimos su nombre en el cuadro de nombres tal como vemos:

Pulsamos en la tecla intro, con lo que tenemos lo siguiente:

Es un rango de 3 celdas. Ahora introducimos más datos en la columna A:

y volvemos a escribir en el cuadro de nombres rangodin, pulsamos en aceptar, y tenemos:

Es decir, el tamaño del rango es mayor y va en función de las celdas que están ocupadas en la columna A. Mas celdas con datos, rango mas amplio, menos celdas rango más pequeño. La función desref nos ha ayudado a lograrlo.

Función desref

Es una función de referencia de búsqueda de Excel. Nos va a permitir generar rangos de celdas que se puedan contraer o expandir. En este post vamos a ver como funciona la función.

Desref consta de cinco argumentos, de los cuales, el cuarto y quinto son opcionales, es decir, podemos darles valor o no, según nos convenga.

En primer lugar, vamos a usarla con los tres primeros argumentos, son los siguientes:

1. Ref
2. Filas
3. Columnas

Dejamos para otra entrada alto y ancho. El argumento ref  hace referencia a la celda origen. El segundo y tercer argumento muestra el desplazamiento en número de celdas con respecto al argumento ref. Por ejemplo, sí los argumentos filas y columnas son cero el resultado de la función dará el contenido de la celda marcada como referencia en el primer argumento. Entonces:

=desref(A1;0;0)

Si en la celda A1 tenemos el dato función desref, entonces el resultado de la función será función desref, es decir, el mismo.

Veamos más ejemplos. Partimos de los siguientes datos:

En una celda introducimos la siguiente fórmula:

El resultado será, de acuerdo con la tabla, Fotografía puesto que fotografía se encuentra una fila por debajo y una columna después de la celda referencia, A4 (fotografía está en la celda B5).

Otro ejemplo, ahora vamos a cambiar los argumentos para obtener otro resultado. Si queremos obtener el número de alumnos inscritos en el curso de botánica la función será la siguiente:

Ahora nos desplazamos 0 filas y dos columnas hacia la derecha con respecto a la celda A4.El resultado es entonces:

El manejo de la función con tres argumentos es sencillo de usar. En un próximo post tendremos en cuenta a todos los argumentos. Como avanzadilla diremos que el cuarto y quinto argumento sirven para hacer referencia a un rango con un número determinado de filas y columnas de tamaño. Esto permite definir rangos dinámicos, que se expanden automáticamente en función del número de celdas con datos.

La función buscarv

Normalmente cada negocio puede llevar un registro de sus clientes, proveedores o productos. Esa lista puede ser larga, lo que provoca que la búsqueda de algún dato en la misma se puede convertir en una tarea tediosa. Excel proporciona para estos casos una herramienta muy útil, la función buscarv. Vamos a ver como funciona y como podemos implementarla.

En primer lugar, se muestran los datos a partir de los cuales vamos a construir la función. Podemos imaginarnos una tabla con más filas y columnas, pero bastará para ver como funciona la función. Los datos a partir que vamos a necesitar son los siguientes:

Y ahora creamos las siguientes celdas, que nos ayudarán a conocer los datos que nos interesan de un producto determinado por su código:

Así introduciendo el código del producto que nos interesa obtenemos automáticamente el resto de los datos del  producto. Todos los datos están extraídos de la lista de datos inicial.

Buscarv nos pide cuatro argumentos:

• Valor buscado
• Matriz de datos
• Índice de columna
• Ordenado

En nuestro ejemplo, Valor buscado será el valor que colocamos en la celda H2. De acuerdo con ese valor, buscarv localizará el nombre o el número de unidades. Para que tenga éxito la búsqueda el dato colocado en H2 debe estar presente en la primera columna de la lista de datos inicial. Si los valores de la primera columna son numéricos, el valor buscado puede ser uno que esté entre esos valores, no es necesario que sean los mismos.

Matriz de datos representa el conjunto de celdas que contienen todos los datos con los que trabaja buscarv.

Indicador de columna es la columna (dada con valor numérico) que contiene el valor que deseamos que buscarv nos muestre como resultado.

Ordenado puede ser verdadero o falso. Hace referencia a la primera columna de la matriz de datos, si está ordenada su valor es verdadero, y si no será falso. Además, si es falso el valor buscado debe ser alguno de la primera columna, no se admite otro valor,  en este caso no devuelve datos.

Introducimos un código en la celda H2. Después nos posicionamos en la celda H3. Escribimos la función buscarv para que nos devuelva el nombre.

Como el código está en H2, hacemos clic en la celda, tecleamos; y como matriz de datos seleccionamos todas las celdas de la tabla de datos inicial. La primera fila contiene los títulos de cada columna, no la tenemos en cuenta, como vamos a copiar la fórmula para las unidades, la fecha de entrada y el precio unidad entonces fijamos su referencia a las celdas con una referencia absoluta. Lo mismo vamos a hacer con la referencia al valor buscado. Esto nos permite copiar la fórmula sin que cambien dichas referencias. Y como tercer argumento escribimos un 2 pues queremos que nos devuelva el nombre del producto. Como ordenado no ponemos nada pues vemos que están ordenados los códigos. En este caso también podemos colocar falso para obtener la referencia exacta del código del producto.

Solo queda copiar la fórmula en las celdas H4; H5 y H6. Para obtener los resultados esperados solo tenemos que cambiar el tercer argumento, indicador de columnas, por el número correspondiente a las unidades, fecha y para el precio (3, 4 y 5 respectivamente). Así completamos la columna H.

Quizás tengas que hacer algún cambio de formato para que los datos aparezcan como nos interesa, formato fecha o formato general. A partir de ahora, poner un nuevo código producto en H2, mostrará automáticamente el nombre, número de unidades, fecha de entrada y el precio unidad de ese nuevo producto. Por ejemplo, si en H2 escribimos P-6 nos quedará:

Resumiendo, podemos decir que buscarv es una buena herramienta para recuperar datos de grandes listas de datos de una forma sencilla y rápida.

Eliminar elementos repetidos en Excel 2007

Dada la amplitud de una hoja de cálculo, podemos introducir muchos datos. Creamos largas listas, con las que trabajamos. En estas listas los elementos introducidos pueden repetirse o no, dependiendo de nuestras necesidades.

En ciertas ocasiones puede interesar que los elementos que introducimos no se repitan. Si queremos asegurarnos de que no hay elementos repetidos, usaremos una nueva herramienta que viene incorporada en la versión 2007. Nos permite eliminar los elementos repetidos, y se denomina quitar duplicados. Se encuentra en la cinta Datos, sección Herramientas de datos:

El primer paso es tener una lista con datos. Vamos a crearla y nos vamos a preocupar de introducir datos repetidos a propósito. Por ejemplo:

Ahora tenemos la lista con varios elementos repetidos (producto 2, producto 6, etc). Lo que vamos a hacer ahora es eliminar esos elementos repetidos, para tener la lista sin repeticiones. El primer paso es seleccionar toda la lista.

Y después basta con hacer un clic en Quitar duplicados:

Aparece una ventana que nos pregunta cual es la lista, como ya está seleccionada y no hemos seleccionado encabezados, pulsamos en el botón aceptar:

El resultado es inmediato, la misma lista pero sin elementos repetidos:

Como se observa, ya no tenemos ningún producto que aparece más de una vez. Además, también aparece un cuadro de dialogo que nos informa de los elementos repetidos que han sido eliminados de la lista:

También nos proporciona el número de datos que permanecen en la lista.

La función pago

Dentro de las funciones financieras, nos permiten realizar cálculos de tipo económico, que podemos usar con Excel, tenemos la función pago. Cuando solicitamos un crédito en un banco, debemos ir pagándolo con mensualidades, anualidades... Pago nos calcula esas cantidades.

Pago tiene cinco argumentos. Los dos últimos son opcionales. Hacen referencia a si vamos a hacer el pago al principio o final de cada periodo, y el otro es si con el crédito vamos a financiar o no todo el capital prestado.

Supongamos que deseamos financiar 25000 €, a una tasa anual del 5,25% en 6 años.

Vamos a hallar la cantidad mensual que hay que pasar al banco en estas condiciones. Tecleamos la función donde queremos tener el resultado.

Como deseamos hacer el cálculo mensual, la tasa debe ser también mensual. La tasa la dividimos entre doce pues el dato que tenemos es anual. El argumento nper es el número de pagos que vamos a realizar en los 6 años. El dato será multiplicar el número de años por 12 meses. Tenemos ya así calculado el número de pagos total. Va hace referencia al capital que financiamos. Por tanto, escribimos la función con sus argumentos:

Pulsamos en aceptar, y tenemos el dato que buscamos:

Se observa que el resultado aparece en rojo, esto es así por que es un pago, salida de dinero, y Excel lo coloca en rojo. Si queremos que sea positivo y que no esté en rojo, solo hay que introducir el argumento capital (el tercero Va) con un signo menos -. Editamos la fórmula y colocamos el signo menos:

Así, el resultado será positivo:

Si se desea devolver el dinero por anualidades, un solo pago cada año, para el mismo préstamo, el cálculo del mismo será así:

El resultado es:

Con una función sencilla podemos aproximarnos un poco a los pagos que hacemos a los bancos, hipotecas, coches o vacaciones que deseamos financiar. Nos va a dar una idea de lo que vamos a pagar mientras dure el préstamo.