[=]

En este capítulo se tratan aquellos temas que le permitirán al usuario alcanzar una mayor familiaridad con la hoja de cálculo, en lo que se refiere a facilidades de selección de información evitando errores de digitación, se presenta además la forma de colocar alarmas (formatos de colores) que ayudan al usuario a identificar determinadas condiciones mediante formatos especiales a la información

3.1 APLICACIÓN DE FORMATOS CONDICIONALES

Es bastante útil, poder aplicar formato a la información, que permanecen estáticos hasta que se cumpla una determinada condición de la información. A esto se le denomina detectar un valor. En ocasiones a estas tablas se les denomina tablas semáforo, ya que se usan por lo general para llamar la atención sobre determinada situación de un negocio.

Por ejemplo visualizar entre una serie grande de valores, aquellos que cumplen con una condición determinada; tal el caso si se desea saber de una serie de números de rifas, cuales están en el rango 0-2000, como se aprecia en la Figura 18

3.1.1 Aplicación del Formato Condicional

Aplicar un formato condicional, es similar a cuando a una celda se le va a aplicar un color, un borde o determinado tipo de letra, lo adicional en este caso es que se debe indicar una condición específica la cual el Excel evaluará y ante una condición de Verdadero o Falso, aplica el formato que se le indique.

Las ventanas donde se definen las condiciones se presentan en la Figura 19 y en la Figura 20

Para un mejor entendimiento de cómo aplicar el formato, se realizará un ejemplo:

· Prepare una hoja de cálculo con la información adjunta, donde se relacionan registros con nombre y edad, ver Figura 21

· Se diseñará un formato condicional a efectos de resaltar las celdas de edad, con valores mayores a 50. Para ello ubique el Mouse sobre la celda b2, donde se indican las condiciones y luego se copia el formato al resto de celdas

· Una vez se ubique sobre la celda, va la menú de herramientas y selecciona formato, tal como se aprecia en la Figura 22

· Una vez se selecciona Formato condicional aparece la ventana de las condiciones (Figura 23)

· Se presenta dos opciones, valor y fórmula. Se indicarán con este ejemplo las dos opciones:

Al seleccionar valor, Excel evaluará como su nombre lo indica el valor que hay en la celda; y facilita opciones como entre, igual, mayor que, menor que, etc. (Figura 24)

AL seleccionar fórmula, se le indica a Excel una fórmula determinada (Figura 25)

· Continuemos con la opción de valor. Luego de indicar la condición, se hace clic sobre la pestaña formato, y aparece lo que se aprecia en la Figura 26

Como se aprecia en la Figura 26, se puede dar un formato a la letra o colocar un borde o un fondo a la celda. Seleccionemos para este caso un color para el fondo (si el lector desea, puede dar los otros dos formatos adicionales). Al hacer esto de hace clic sobre aceptar

. Se hace clic en aceptar, y debe procederse ha copiar el formato en las otras celdas (Figura 28)

. Una vez copiado el formato, la información quedará entonces como se presenta en la Figura 29

La opción de formato condicional permite colocar tres condiciones a una celda, tal como se aprecia en Figura 30

Dichas condiciones se pueden cambiar, simplemente entrando a cada una. Igualmente se pueden eliminar, para ello hace clic sobre la pestaña eliminar y aparece lo que se indica en la Figura 31

3.1.2 Taller de Formatos Condicionales

En el desarrollo cotidiano de actividades, es común encontrar dependencia entre las actividades, es decir, que para poder continuar con una actividad se debe tener culminada alguna anterior.

Diseñe una hoja de cálculo, con formatos condicionales, que indique mediante colores rojo (para)-verde (sigue), la secuencia de cumplimiento del cambio de la llanta de un carro. Recuerde igualmente que no pueden tenerse cumplimientos parciales, ya que en esta actividad si es totalmente necesario que estén cumplidas las actividades anteriores al suceso de la cadena del proceso.

3.2 VALIDACIÓN DE DATOS

Poder elegir datos o controlar la información de entrada al modelo de datos que se este creando, en una hoja de cálculo, es de gran utilidad y Excel provee ese mecanismo.

La validación de datos es tan rígida o flexible como el usuario la plantee. Puede especificarse el tipo de datos permitidos como se presenta en la Figura 32.

Normalmente todas las celdas de Excel presentan la validación de cualquier valor dependiendo de los criterios requeridos o reglas de validación, que el usuario le asigne. Las reglas que se crean pueden ser de estricta validación o de simple advertencia. Si la regla es obligatoria, Excel rechazará la entrada y obliga al usuario a dar la información correcta (Figura 33)

Si la regla es de advertencia, Excel mostrará un cuadro de dialogo (con un mensaje predeterminado optativo que el usuario puede indicar previamente) y dará al usuario la oportunidad de rehacer la entrada (Figura 34)

3.2.1 Ejemplo 1 de validación datos – Validación de Números Enteros

Se desea crear una aplicación para digitar información de personas con la edad correspondiente, y para evitar errores se planteará una regla de validación, que permita solamente números positivos con un rango 10-60 años.Se debe empezar por digitar los nombres, tal como se indica en la Figura 35

Figura 35 Modelamiento del ejemplo para validar información

Para modelar la regla de validación, debe seleccionarse la celda donde la información se digitará y posteriormente dicha regla se copia a las celdas donde se requiera. Ubíquese entonces en la celda B2; y una vez allí haga clic en la barra de herramientas sobre Datos y aparece lo que se presenta en la siguiente figura

Al hacer clic sobre validación, aparece una ventana como la indicada en la Figura 37

SI usted activa el combo de selección, puede apreciar lo que la regla de validación permitirá evaluar

Para el ejemplo que se viene tratando debe indicarse que se permiten números enteros positivos entre 10 y 60 años. Para ello debe indicarse esto en la opción de número entero, y aparece entonces la ventana indicada en la Figura 38

La opción de validación, permite colocar un mensaje entrante y uno errores, esto a efectos de hacer más amena la pantalla de Excel al momento de digitar la información y así mismo de tener un modelo de datos auto contenido

· Para generar la venta de mensaje entrante, haga clic sobre dicha pestaña, y colocar un titulo a la ventana como al contenido interno, tal como se aprecia en la Figura 39

De igual forma se diseña la venta de errores, donde adicionalmente se define la regla de validación como obligatoria o de información. Al hacer clic sobre dicha ventana, aparece algo similar a la de entrada de datos Figura 41

Como se aprecia en la Figura 41, en el combo de selección para el estilo, aparecen 3 opciones:

· Límite, esto hace que la regla sea de obligatorio cumplimiento y no permite introducir el dato

· Advertencia e información, permiten digitar el dato y es decisión del usuario si los deja o no

El tipo de mensaje que se presenta en estos casos, se indicó en la Figura 34

Figura 41 Venta de mensajes de error

Una vez se ha definido la regla de validación, se da aceptar. Y debe copiarse a las otras celdas que se requiera, con la opción de Copiar-Pegado especial – Validación, tal como se presenta en la Figura 42

3.2.2 Ejemplo 2 de validación de datos– Validación de Datos mediante listas

Otra de las opciones que proporciona la opción de validación de datos, es la de poder seleccionar datos de una lista, y así no tener la necesidad de digitar. Esto es útil por ejemplo, cuando se requiere tipear nombre de empresas lo que puede conducir a errores de digitación.

Para entender el concepto, asumamos que se requiere asignar diferentes destinos a los empleados de una compañía a efectos de que estos realicen visitas de mercadeo a dichas ciudades, se digitará entonces una única vez la lista de ciudades y al momento de la validación solo será necesario escoger.

Se aprecia entonces que en una zona de la hoja se relacionan diferentes ciudades, ahora entonces en la celda B2, se realizará la regla de validación. Para ello se procede de igual forma que en e ejemplo 1 (Ítem 3.2.1), hasta el punto de llegar al combo de PERMITIR (para criterios de validación). (Figura 44)
	Figura 44 Validar listas

Debe entonces en la pestaña de origen, señalar con el Mouse, el rango de celdas, donde se digitaron las ciudades, tal como se indica en la Figura 45

Al igual que el ejemplo anterior, se copia la opción de validación en las celdas donde es requerido, tal como se indicó en la Figura 42

3.3 EJEMPLO UNIFICANDO DEL CAPÍTULO

Para unificar los conceptos de validación de datos y formatos condicionales, se presenta el siguiente ejemplo

Vamos a programar en este ejercicio la facturación de una empresa; la idea es hacer una facturación muy simple, pero de forma que el usuario pueda fácilmente adaptarla y complementarla a una empresa real


· Aplicando el procedimiento anterior, el libro de cálculo para nuestro ejercicio debe quedar con 5 hojas de cálculo	· La Barra indicadora de hojas de cálculo, presenta un menú para cambiar los nombre, copiar o eliminar hojas, este menú se obtiene parándose sobre una de las etiquetas o hojas de cálculo y presionado el botón derecho del Mouse

El menú presentado aquí corresponde al Office XP, versiones anteriores del Excel presenta menos opciones, pero las básicas como insertar – eliminar –o copiar la tienen las versiones de Excel anteriores

Continuando con el ejercicio, se deben renombrar las 5 etiquetas u hojas de cálculo con los siguientes nombres:

· Usted se ubica en una etiqueta, al activar el menú (botón derecho del mouse), selecciona “cambiar nombre”

· Este quedará resaltado, usted solo debe colocar el nuevo nombre y dar ENTER o RETURN

Aplicando lo anterior, la hoja que venimos trabajando debe quedar así:

· En el caso en que no se visualicen las 5 etiquetas en la línea inferior de la pantalla, siempre se puede ganar espacio al reducir el tamaño de la barra de desplazamiento horizontal, de la siguiente forma:

· En la hoja ARTÍCULOS, se digitarán los diferentes productos del almacén, a efectos de luego implementar una validación de datos en la FACTURACIÓN

Recuerde que la práctica de validación de datos es útil para evitar errores de digitación, pues se elimina esta actividad y se cambia por selección.

Esta hoja debe ser modificada cada vez que se provea un nuevo artículo.

· Digite entonces en ARTÍCULOS lo siguiente:

· En la hoja CLIENTES, se digitarán los diferentes compradores del almacén, a efectos de luego implementar una validación de datos en la FACTURACIÓN

Esta hoja debe ser modificada cada vez que llegue un nuevo cliente.

· La hoja FACTURACIÓN, servirá para recibir la solicitud del cliente y establecer si se dispone de los solicitado o no, posteriormente en FACTURA, se hace la liquidación respectiva.

Vamos a establecer las validaciones de datos tanto de clientes como de artículo.

Primero la sección de Clientes: Para ello, debe hacerse una formulación entre la Hoja FACTURACIÓN y la Hoja Clientes. Dicha formulación únicamente consistirá en traer los datos de los clientes a la hoja FACTURACIÓN, enlazando la razón social y el NIT.

Para ello, ubíquese en la celda U2, de la hoja FACTURACIÓN , y desarrolle la siguiente fórmula, debe adicionalmente copiarla hasta la U50.

· Aquí se ha hecho una suma de caracteres mediante el apostrofe, Cuando se requiere unir dos celdas a nivel de texto, se hace una SUMA de textos mediante el operador &, con la siguiente estructura TEXTO1 & TEXTO2, todo lo que se quiera adicionar el la suma y que no este en celdas debe incluirse dentro de comillas, así por ejemplo:

· Así es como en el ejemplo que venimos trabajando se une la razón social y el Nit de los clientes
· De igual, forma se fórmula la sección de artículos en la misma hoja FACTURACIÓN, y deber quedar de la siguiente forma:

· Procedemos entonces a establecer la validación de datos:

· Primero se establecerán dos nombres de rangos en la hoja llamados LISTACLIENTES y LISTAARTICULOS.

Para ellos ubíquese primero en la sección de ARTICULOS, seleccionado las celdas R2 a R50

Al seleccionar definir, aparece:

Una vez seleccionadas las celdas en la Barra de Menú, se llega la opción de DEFINIR UN NOMBRE. Con esto las celdas r2:r50, tendrán un nombre específico

Excel, sugiere un nombre que corresponde a lo que aparece en la primer celda, pero coloquemos el nombre LISTAARTICULOS, y se da ACEPTAR

Debe hacerse lo mismo con la lista de clientes, es decir colocar el nombre LISTACLIENTES al rango U2:U50:

Debe ubicarse en la celda donde se indica el cliente, (F4), la idea es que usted no digite el nombre sino que lo seleccione, de la siguiente manera:

· Ubíquese en la celda F3 y seleccione del menú DATOS, la opción VALIDACION	· Al activar VALIDACION, aparece algo como:
· Como los datos de los clientes se encuentran en una lista, se selecciona en Permitir LISTA, y aparece lo siguiente:	· En origen, se coloca el nombre del rango que se ha definido:
· Se apreciará esto en la hoja de cálculo:	· La flecha, en la celda F3, se llama Combo de Selección y al activarla con el Mouse, aparecerá:

· A efectos de ver mas amplio el nombre del cliente, se centrará este en las celdas de las columnas E-F-G, mediante el icono de alineación

Para la validación del número de factura, se le indicará al usuario que solo puede dar números positivos y enteros y mayores que cero:

Recuerde que siempre que encuentre una fecha de combo, ejemplo:

Al activar la fecha hay mas opciones:

Aplicar validaciones a la celda Unidades (A6) y disponible (C6), para unidades solo se permiten valores positivos y mayores que cero. Para Disponible, se acepta solo Si o No, esta última es para que el vendedor al momento de llenar la solicitud, indique si se dispone o no del artículo

La validación de datos, del pedido del cliente, solo se hizo en a8-b8-c8. La validación debe copiarse hacia las celdas inferiores. Para nuestro ejemplo se hará hasta la fila 15.

· Se agregará algo mas a la hoja facturación. Lo que se denomina FORMATO CONDICIONAL Esto se refiere a un detalle visual de las celdas, dependiendo de una condición específica, en este caso, se desea que se destaque el artículo que el vendedor indique no dispone, y debe destacarse la celda en un color que resalte. Así mismo, si se indicó un artículo, pero el vendedor no indica su disponibilidad, esto también debe destacarse. Ver la siguiente figura:

La primera opción es indicar en la Celda B6, que si la disponibilidad C6 es negativa, la celda B6 quede resaltada en un color.

· En condición 1, aparece Valor de la Celda ó Fórmula. En este caso es necesario indicar una fórmula para la evaluar la condición si la celda B6 dice NO. Si esto se cumple se le indica un formato:

· Al activar formato, aparece lo siguiente:

· Como se aprecia, es factible indicar un tipo de letra, colocarle bordes a la celda o colocarle un color de fondo a la celda. Indicamos en nuestro ejemplo que resalte la celda de amarillo:

Y se da aceptar

Ahora debe procederse a copiar el formato hasta la fila 10, pero con copiado especial de FORMATO

El segundo formato condicional, consiste en si no se ha indicado la disponibilidad de un artículo pedido por el cliente, esto se hace en la celda C6 y se copia hasta c19, la formulación en este caso es:

· El siguiente paso es construir la Factura

En la hoja Factura, implemente el siguiente formato

· Formule un enlace entre las hojas facturación y factura

· Para evitar que en la formulación se generen ceros en las celdas donde no hubo pedido, configure el Excel de forma que los ceros no se vean, esto se hace en el menú HERRAMIENTAS-OPCIONES

· Allí en la opción VER, se le indica que no despliegue los ceros:

· Ahora mediante la función de búsqueda vertical, se indicará el valor unitario únicamente de los artículos que dispone el almacén y los cuales fueron verificados por el vendedor (columna disponibilidad).

Recuerde que en la hoja Artículos, se especifica el valor unitario de cada uno de los artículos. La función será entonces:

Esto luego de copiar en las celdas respectivas, y complementando con el cálculo del IVA y el total de la factura es:

Ahora en Histórico, haga un enlace entre la hoja factura y esta última, con la siguiente estructura:

· Copie la formulación las celdas que usted desee hacia abajo , y la primera celda conviértala a valor. Adicionalmente implemente un formato condicional, que permita desplegar la información de las filas, solo si en la celda de la columna A dice SI, de forma que se aprecie la hoja de la siguiente manera:

3.4 TALLERES

A efectos de afianzar los temas de Validación de datos y formatos condicionales, se presenta los siguientes talleres

3.4.1 Taller 1

Elabore una hoja de cálculo que le permita calcular tasas de interés no importa su naturaleza, (efectiva, nominal, periódica, anticipadas o vencidas, su periodo de tiempo), según el siguiente cuadro:

Donde por ejemplo Ip_a(IEA,n), significa que se debe calcular la tasa periódica anticipada dada una tasa efectiva y el periodo solicitado

Los periodos a manejar son los que se especifican en el siguiente cuadro y se dan con las iniciales respectivas:

Debe desarrollar el taller con opciones de validación de datos para los periodos de pago y la forma de liquidación (Anticipado o vencido)

3.4.2 Taller 2

Debe generarse un resumen de la tasa solicitada y la tasa pedida., tal como lo presenta la siguiente figura

3.4.3 Taller 3

De forma que se pueda calcular la cuota de pago mediante la función financiera de Excel - PAGO(), la cual se explica a continuación:

Función Pago(): Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante.

· Va: Es el valor actual o lo que vale ahora la cantidad total de una serie de pagos futuros, también se conoce como el principal.

· Vf: Es el valor futuro o un saldo en efectivo que desea lograr después de efectuar el último pago. Si el argumento vf se omite, se asume que el valor es 0 (es decir, el valor futuro de un préstamo es 0).

· Tipo: Es el número 0 (cero) e indica los pagos al final del periodo ó 1 e indica el vencimiento de los pagos al inicio del periodo.

· El pago devuelto por PAGO incluye el capital y el interés, pero no incluye impuestos, pagos en reserva ni los gastos que algunas veces se asocian con los préstamos.

· Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper. Si realiza pagos mensuales de un préstamo de cuatro años con una tasa de interés anual del 12 por ciento, use 12%/12 para el argumento tasa y 4*12 para el argumento nper. Si efectúa pagos anuales del mismo préstamo, use 12 por ciento para el argumento tasa y 4 para el argumento nper.

· Para encontrar la cantidad total que se pagó durante la duración del préstamo, multiplique el valor devuelto por PAGO por el argumento nper.

La tabla de amortización debe considerar la posibilidad que los préstamos se hagan con el pago de la cuota en forma anticipada o vencida

La hoja debe estar diseñada para reportar hasta 500 periodos de pago, pero usted debe diseñar formatos condicionales, de tal forma que no se presente información donde no debe ser.

Aquí es claro que se tiene un préstamo de 5’000.000 a 5 años con pago semestral, se deben realizar 10 pagos, usted puede ver que en la tabla no se presenta ni información ni ceros del periodo 10 en adelante

Debe desarrollarse la aplicación con campos de validación de información para determinar el número de periodos y la forma de pago (Anticipado o vencido)

Debe agregarse a la tabla, un consolidado del préstamo mas los intereses pagados.

Recuerde que la aplicación debe tener opciones de validación, formatos condicionales, funciones lógicas simples o anidadas, funciones lógicas con operadores lógicos (y-o), funciones de búsqueda y fijación de celdas para facilitar la copia de fórmulas.

3.4.4 Taller 4

La empresa Rent- a-car, empieza sus operaciones en febrero de 2004, y su actividad es la del alquiler de diferentes tipos de vehículos.

De dichos costos, el alquiler básico y el seguro lo cobran por día de uso del auto.

La empresa cobra por cada día de alquiler, sin importar el número de horas que se ha tenido el vehículo, de modo que si se alquila un coche a las 11 p.m, y se devuelve a las 2:00 a.m. del día siguiente se contabilizan dos días.

Existen diferentes categorías de vehículos, de manera que cada categoría tienes asociados precios diferentes. Dichas tarifas se presentan en la Tabla 9

Por otra parte, la empresa participa como proveedor de servicios del club de automovilistas Rent-a-car, el cual proporciona tarjetas a sus asociados. La posesión de dicha tarjeta da derecho a un 5% por el alquiler de un auto.

Desarrolle un modelo de hoja de cálculo que permita realizar una factura por alquiler de un coche de forma rápida y eficiente, y además, de manera que se evite al máximo cometer errores, como por ejemplo insertar una categoría que no existe


· Al activar el combo de selección de Datos aparecen varias opciones a saber	· Como debemos dar un rango, se indica entonces un valor mínimo (10) y uno máximo (60)


Figura 45 Selección del rango para listas	En esta opción no es necesario generar ventana para mensaje entrante y mensaje de error, debido a que acá no se digita nada, sino que se seleccionan valores, al dar aceptar, puede probarse entonces la regla de validación


	· La forma de insertar hojas de cálculo en un libro es simple. Vaya a insertar (En la barra Menú) -

· Haga una nueva hoja de cálculo. De entrada en nuestro libro de cálculo necesitamos 5 hojas de cálculo	Y allí va al submenú Hoja de cálculo


Figura 34 Mensaje de Advertencia o de Información

3 PERSONALIZAR LA HOJA DE TRABAJO