Herramientas básicas II

Proteger celda, hojas y libros.

Un aspecto importante en Excel es su carácter manipulativo. Los usuarios pueden modificar datos para su análisis posterior, aunque  en ocasiones se plantea la necesidad de que ciertos datos no puedan manipularse. Para ello se establece la posibilidad de bloquear, el libro, determinadas hojas o determinadas celdas.

Realmente por defecto todas las celdas están protegidas o bloqueadas para que no sufran cambios, pero no nos damos cuenta ya que la hoja no está protegida, para que realmente se bloqueen las celdas antes hay que proteger la hoja de cálculo.

Para desbloquear las celdas:

Seleccionar el rango de celdas que queremos desbloquear para poder realizar variaciones.

  1. Seleccionar la pestaña Inicio.
  2. Hacer clic sobre la flecha que se encuentra bajo la sección Fuente.
  3. Hacer clic sobre la pestaña Proteger.
  4. Aparecerá la ficha:

5. Desmarcar “Bloqueada”.

Desactivar la casilla Bloqueada y Hacer clic sobre el botón Aceptar.
Si se activa la casilla Oculta, lo que se pretende es que la fórmula o el valor de la celda no se pueda visualizar en la barra de fórmulas.

Seleccionar la pestaña Revisar.

Hacer clic sobre el botón Proteger hoja que se encuentra en la sección Cambios.

Aparecerá el cuadro de diálogo Proteger.

Dejar activada la casilla Proteger hoja y contenido de celdas bloqueadas para proteger el contenido de las celdas de la hoja activa.

Activar las opciones deseadas de la casilla Permitir a los usuarios de esta hoja de cálculo para que no tenga efecto la protección para la modificación seleccionada y desactivarla para tener en cuenta la protección.

Si queremos asignar una contraseña para que solamente pueda desproteger la hoja la persona que sepa la contraseña, escribirla en el recuadro Contraseña.

Hacer clic sobre el botón Aceptar.

Recordemos como se procedía en 2003:

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:

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

Hipervínculos y comentarios

Los hipervínculos no cambian demasiado respecto a la versión 2003. Supongamos que deseamos establecer un hipervínculo a la página de Movistar desde el área del gráfico. Sólo debemos seleccionar el mismo (clic en el borde del mismo) seguidamente Menú>insertar>Hipervínculo o directamente con Ctrl+Alt+k.  Nos saldrá la misma pantalla que en la versión 2003. Sólo insertar la dirección URL y listo.

Con el fin de proporcionar información al usuario se puede agregar en las celdas que deseemos un comentario que aparecerá como texto emergente al pasar el puntero por la celda en cuestión.  Por ejemplo en la celda F2 insertamos el comentario “el IVA puede variar” –> Revisar / Comentarios / Nuevo comentario. Para modificar o eliminar un comentario abrimos el menú contextual desde la celda en cuestión.

Insertar función

Excel dispone de un gran número de funciones, la versión 2003 contaba con 329 y la versión 2010 unas 340 más o menos.

En la primera versión de Microsoft Excel 2.010 se modificaron los nombres de algunas funciones. Luego, mediante el SP1 (Service Pack 1) se volvieron a los nombres originales. Estos cambios no afectan la compatibilidad entre las distintas versiones de Excel  (la conversión de los nombres se hace automáticamente). A continuación mostramos una tabla donde se ven las funciones que cambiaron de nombre:

Para insertar una función se procede igual que en la versión 2003, podemos hacerlo desde la misma barra para insertar fórmulas pinchando en fx con lo que se nos abre el asistente. La segunda forma es a través de la pestaña Fórmulas. Si vamos a trabajar con operaciones comunes como autosuma, promedio, etc también contamos con la herramienta directa en la cinta de opciones dentro del grupo Modificar.

Utilización del condicional

Si deseamos establecer una acción sobre una celda si cumple una condición podemos utilizar las funciones lógicas. Por ejemplo observe la siguiente tabla:

En la celda c29 hemos procedido a insertar la función promedio. Y desde la cinta de opciones se ha seleccionado número desplegando la flechita hacia abajo para colocar sólo dos decimales. En G9 se ha procedido a insertar la siguiente función lógica =SI(F5<0;”RECESION”;””) . Para colocar el formato condicional hemos seleccionado el ítem en el icono estilos. Esos estilos además se pueden editar simplemente desplegando la pestaña:

Buscar objetivo

Sin duda una de las mejores utilidades que tiene Excel, la función Buscar objetivo, nos facilita averiguar el valor de la variable que determina un resultado en una ecuación sencilla. En el siguiente ejemplo analizaremos su aplicación en Excel 2010.

Supongamos que deseamos conocer el importe periódico a satisfacer para amortizar un préstamo de 4500 € al 1,5 mensual. Para ello podemos diseñar una tabla como la siguiente:

En la celda B9 hemos insertado una cantidad aleatoria, en este caso 200, como cuota inicial. Después hemos calculado los intereses en base al saldo pendiente de amortizar, o sea en C9 hemos insertado =E8*$b$5/100 ; y la amortización es la parte de la cuota que resta una vez descontados los intereses es decir en D9–>=$B$9-C9.

Con esto ya tendríamos la segunda línea del cuadro de amortización. Rellenamos hacia abajo hasta alcanzar las 12 mensualidades.

Evidentemente y como podrá comprobar, con esa cuota en el período 12 nos quedaría todavía un saldo pendiente de 2772,04 €. Bien aquí es cuando entra en juego nuestra función Buscar Objetivo.

Nos posicionamos en la celda E20 y seleccionamos Datos>Análisis Y si> Buscar Objetivo. En la ventana emergente insertaremos los datos tal y como aparecen en la imagen:

Es decir definimos la celda en donde se refleja el último saldo pendiente con el valor cero, ya que al final de los 12 períodos deseamos tener amortizado el préstamo, para cambiar la cuota a abonar es decir. Dicho de otra forma a cuánto debe ascender el importe periódico para devolver el préstamo en 12 cuotas.

Evidentemente que podemos utilizar otra función financiera más práctica como puede ser Pago, (por ejemplo en E3 podemos insertar =PAGO(B5/100;B4;B3)) lo que nos daría el mismo resultado.

Buscar objetivo con VBA

En alguna ocasión se nos puede presentar la necesidad de programar la función buscar objetivo con VBA. Para ello debemos utilizar la notación GoalSeek. Al objeto de conocer mejor su uso lo voy a explicar con el siguiente ejemplo, sobre el que además profundizaré un poquito más en el uso del formato condicional.

Imaginemos un envasador de café que presenta los siguientes datos en su cuenta analítica:

  • Precio coste del kg de café –>13.95 €
  • Costes directos por kg——–> 0.75 €
  • Costes indirectos por kg—— >0.25€
  • Margen inicial——————->15 %
  • IVA inicial————————>7 %

Con esos datos de partida planteamos un escenario inicial, y otros tres alternativos. En el escenario inicial haciendo clic en el botón correspondiente nos debe presentar cuáles serían los resultados de aplicar una subida de 1 punto en el IVA (del 7 al 8%). Los datos de las celdas D22, D23, D24 y D26, son iniciales y fijos. En E22 y E24 se ha aplicado un incremento del coste del 1% por el impacto del IVA soportado. En H25 e I25 se presenta el precio final inicial, y el precio final revisado con el IVA al 8%.

Para ello se ha procedido a redondear este último dato con la función correspondiente (celda I25):

=REDONDEAR.MAS(((E22+E23+E24)*(1+E26))*(1+G22);1)

Como podrá observarse en el ejemplo una subida de 1 punto en el IVA llevaría un desplazamiento de incremento del precio en un 8,69%. evidentemente ello supondría un aumento del beneficio marginal potencial ,  que no  real dado el menor nivel de demanda que supondría un encarecimiento del producto. Por ello se plantea en el ejemplo otro escenario denominado Escenario Costes en el que se plantea la posibilidad de mantener los mismos precios, reduciendo los costes directos. Para ello se ha insertado otro botón mediante la Vista Programador. Esta opción es posible que no esté presente en la cinta de opciones, para insertarla en la misma seleccionamos Inicio>Opciones>Personalizar vista de opciones (en la ventana de la derecha marcamos Programador) y aceptamos.

A continuación y una vez que tenemos disponible la vista Programador hacemos clic en ella y desplegamos el botón Insertar Controles. A diferencia de la versión 2003 estos controles vienen recopilados en dos grupos, los de formulario y los Active x. Seleccionaremos un botón de comando de este segundo grupo, y lo insertamos en nuestra hoja. Para cambiar el nombre, con el botón secundario seleccionamos Objeto botón de comando->Modificar o bien a través de Propiedades -> Caption ; para cambiar el color del botón de igual forma que en VBA 2003 Propiedades ->BackColor.

El siguiente paso es insertar el código asociado al evento clic. Para este Escenario Inicial solo queremos que se presenten los valores de partida, lo podemos hacer mediante una matriz o simplemente uno a uno:

Private Sub CommandButton1_Click()
Range(“D22”) = “13.95”
Range(“D23”) = “0.75”
Range(“D24”) = “0.25”
Range(“D26”) = “0.15”

 

Range(“E22”) = “15.07”
Range(“E23”) = “0.75”
Range(“e26”) = “0.15”

 

End Sub

Una vez insertado el código volvemos a Excel e insertamos el siguiente botón Escenario Costes que nos va a permitir replantear los costes directos en función del objetivo de mantener el precio final. El código asociado al evento clic en este caso sería:
Private Sub CommandButton2_Click()
‘buscamos el objetivo de aguantar el precio final
‘para mantener márgenes reducimos costes directos

Range(“E22”) = “15.07”
Range(“e24”) = “0.26”
Range(“e26”) = “0.15”
Range(“I25”).GoalSeek  Goal:=18.4, ChangingCell:=Range(“E23”)
End Sub

 

Las correspondencias de la sintáxis en VBA con la función buscar objetivo es la siguiente:

Range(“I25″).GoalSeek—>”Definir la celda”

Goal:=18.4 –>”Con el valor” (en nuestro caso 18.4)

ChangingCell:Range(“E23″)–>”Para cambiar la celda” (en el ejemplo la correspondiente a los costes directos la E23).

 

Una vez insertado procedemos a configurar los escenarios Margen y Mixto de forma similar al anterior:

Escenario margen

Private Sub CommandButton3_Click()
Range(“E22”) = “15.07”
Range(“e24”) = “0.26”
Range(“E23”) = “0.75”
Range(“e26”) = “0.15”
Range(“I25”).GoalSeek Goal:=18.4, ChangingCell:=Range(“E26”)
End Sub

Escenario Mixto

Private Sub CommandButton4_Click()
Range(“E22”) = “15.07”
Range(“e24”) = “0.26”
Range(“E23”) = “0.70”

 

Range(“I25”).GoalSeek Goal:=19, ChangingCell:=Range(“E26”)
End Sub

Para rematar podemos establecer un conjunto de iconos por ejemplo flechas que nos permitan visualizar la evolución que van teniendo los datos que analizamos respecto a una situación anterior.

Si escogemos el escenario Costes, seleccionamos la celda E23. Vamos a Inicio->Formato condicional –>Nueva regla  nos saldrá el cuadro siguiente:

 

Desplegamos la ventana Estilo de formato y seleccionamos Conjunto de iconos. Para escoger a su vez el tipo de icono abrimos el desplegable Estilo de icono para seleccionar el que deseemos.

El siguiente paso será establecer la regla como tal, en nuestro caso marcamos que el icono sea verde en la celda E23 cuando el valor sea mayor que el de la celda D23, desplegando  como tipo número. Con el resto procedemos de forma similar.

 

 

 

 

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: