Clase 2 - Validacion

  • Published on
    07-Mar-2016

  • View
    217

  • Download
    0

Embed Size (px)

DESCRIPTION

clases de excel validaciones 2013

Transcript

  • 2. Rangos dinmicos: funcin desref. (III)

    No podemos pasar por alto una de las utilidades ms empleadas a la hora de trabajar en Excel: los 'rangos

    dinmicos'.

    Definiremos primero qu entendemos por rango dinmico en Excel; sabemos que podemos nombrar los

    rangos que utilizamos, pero lo habitual es que estos rangos no sean fijos, es decir, no tenga siempre (a lo largo de

    nuestro trabajo con la Base de datos en cuestin) la misma dimensin, i.e., el mismo nmero de registros. Es en este

    punto cuando Excel nos proporciona la posibilidad de crear un rango dinmico. Y lo haremos desarrollando la funcin

    DESREF, anidando otra funcin importante como CONTARA en ella.

    Para visualizar el resultado que obtendremos combinando ambas funciones, ejercitaremos otra herramienta de

    Excel como es la validacin (se explicar en su momento). Supongamos un listado de pases, que no tenemos an

    finalizado, i.e., se incrementa (o decrece) con el tiempo. Lo primero que tenemos que realizar es el crear un nombre,

    al que asignaremos la caracterstica de dinmico mediante la funcin:

    Para crear el rango dinmico hemos hecho uso de la funcin Desref, cuya sintaxis es la siguiente:

    DESREF(ref;filas;columnas;alto;ancho) Ref

    es la referencia a una celda o a un conjunto de celdas a partir de la(s) cual(es) nos desplazaremos (supongo que de

    ah el nombre de la funcin DesRef: Desplazar referencia).

    Filas

    es el nmero de filas que nos desplazaremos, hacia arriba o hacia abajo, a partir de la referencia original (Ref).

    Columnas

    es el nmero de columnas que nos desplazaremos, hacia la izquierda o hacia la derecha, a partir de la referencia

    original.

    Estos dos primeros argumentos pueden ser tanto positivos como negativos. Si nuestra celda de referencia es la

    C5 y nos queremos desplazar a la celda B2, entonces podramos usar algo as:

    Frmula Descripcin

    =DESREF(C

    5;-3;-1)

    Muestra el valor en la

    celda B2

  • Los dos ltimos argumentos (Alto y Ancho) son opcionales y si se omiten devolvern el alto o el ancho del

    rango de referencia:

    Alto

    es el alto, en nmero de filas, que se desea que tenga la referencia devuelta.

    Ancho

    es el ancho, en nmero de columnas, que se desea que tenga la referencia devuelta.

    Tanto el alto como el ancho debe ser un nmero positivo.

    =DESREF(Hoja1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;1)

  • 3. Los datos Validacin

    Ya hemos visto que Excel se utiliza principalmente para introducir datos, ya sea literales como frmulas. En

    este tema nos vamos a centrar en algunas de las operaciones tpicas que se pueden realizar sobre ellos.

    3.1. Eliminar filas duplicadas

    Frecuentemente, cuando trabajamos con un gran volumen de informacin o recopilamos datos desde varios

    orgenes diferentes, aparecen en nuestro libro de trabajo filas idnticas. A menos que lo que nos interese sea

    estudiar la frecuencia con la que aparece un determinado registro, la mayora de las veces no nos interesar

    tener duplicados, porque no aportan informacin adicional y pueden comprometer la fiabilidad de las

    estadsticas basadas en los datos.

    Por ejemplo, si disponemos de un listado de trabajadores y queremos saber la media de edad, el clculo se

    vera comprometido en el caso de que un mismo trabajador apareciese varias veces.

    Para eliminar filas duplicadas:

    - Deberemos tener como celda activa uno de los registros a comprobar, de forma que, si

    existen varias tablas distintas, Excel sepa interpretar a cul nos referimos. Visualizars un marco

    alrededor de todos los registros que se vern afectados por la comprobacin.

    - En la pestaa Datos pulsamos Quitar duplicados .

    Si te quieres asegurar de que realmente se comprueban las filas que deseas, puedes seleccionarlas

    manualmente antes de pulsar el botn.

    3.2. La validacin de datos

    La validacin de datos nos permite asegurarnos de que los valores que se introducen en las celdas son los

    adecuados; pudiendo incluso mostrar un mensaje de error o aviso si nos equivocamos.

    La validacin de datos se usa para controlar el tipo de datos o los valores que los usuarios pueden escribir en

    una celda. Por ejemplo, es posible que desee restringir la entrada de datos a un intervalo determinado de fechas,

    limitar las opciones con una lista o asegurarse de que slo se escriben nmeros enteros positivos.

    En este artculo se describe el funcionamiento de la validacin de datos en Excel y las diferentes tcnicas de

    validacin de datos existentes. No analiza la proteccin de celdas que es una caracterstica que permite

    "bloquear" u ocultar ciertas celdas de una hoja de clculo para que no se puedan editar ni sobrescribir.

    Para aplicar una validacin a una celda.

  • - Seleccionamos la celda que queremos validar.

    - Accedemos a la pestaa Datos y pulsamos Validacin de datos. En l podremos escoger

    remarcar los errores con crculos o borrar estos crculos de validacin. Pero nos vamos a

    centrar en la opcin Validacin de datos....

    Nos aparece un cuadro de dilogo Validacin de datos como el que vemos en la imagen donde podemos

    elegir entre varios tipos de validaciones.

    En la seccin Criterio de validacin indicamos la condicin para que el dato sea correcto.

    Dentro de Permitir podemos encontrar Cualquier valor, Nmero entero, Decimal, Lista, Fecha, Hora,

    Longitud de texto y personalizada. Por ejemplo si elegimos Nmero entero, Excel slo permitir nmeros

    enteros en esa celda, si el usuario intenta escribir un nmero decimal, se producir un error.

    Podemos restringir ms los valores permitidos en la celda con la opcin Datos, donde, por ejemplo, podemos

    indicar que los valores estn entre 2 y 8.

    Si en la opcin Permitir: elegimos Lista, podremos escribir una lista de valores para que el usuario pueda

    escoger un valor de los disponibles en la lista. En el recuadro que aparecer, Origen: podremos escribir los

    distintos valores separados por ; (punto y coma) para que aparezcan en forma de lista.

  • En la pestaa Mensaje de entrada podemos introducir un mensaje que se muestre al acceder a la celda. Este

    mensaje sirve para informar de qu tipos de datos son considerados vlidos para esa celda.

    En la pestaa Mensaje de error podemos escribir el mensaje de error que queremos se le muestre al usuario

    cuando introduzca en la celda un valor incorrecto.

    3.3. Aplicar validacin de datos a celdas

    La validacin de datos se usa para controlar el tipo de datos o los valores que los usuarios pueden escribir en una celda. Por

    ejemplo, es posible que desee restringir la entrada de datos a un intervalo determinado de fechas, limitar las opciones con

    una lista o asegurarse de que slo se escriben nmeros enteros positivos.

    En este artculo se describe el funcionamiento de la validacin de datos en Excel y las diferentes tcnicas de validacin de

    datos existentes. No analiza la proteccin de celdas que es una caracterstica que permite "bloquear" u ocultar ciertas celdas

    de una hoja de clculo para que no se puedan editar ni sobrescribir.

    QU ES LA VALIDACIN DE DATOS?

    La validacin de datos es una funcin de Excel que permite establecer restricciones respecto a los datos que se pueden o

    se deben escribir en una celda. La validacin de datos puede configurarse para impedir que los usuarios escriban datos no

    vlidos. Si lo prefiere, puede permitir que los usuarios escriban datos no vlidos en una celda y advertirles cuando intenten

    hacerlo. Tambin puede proporcionar mensajes para indicar qu tipo de entradas se esperan en una celda, as como

    instrucciones para ayudar a los usuarios a corregir los errores.

    Por ejemplo, en un libro de marketing, puede configurar una celda para permitir nicamente nmeros de cuenta de tres

    caracteres. Cuando los usuarios seleccionan la celda, puede mostrarles un mensaje como el siguiente:

  • Si los usuarios no tienen en cuenta este mensaje y escriben datos no vlidos en la celda, como un nmero de dos o de cinco

    dgitos, puede mostrarles un mensaje de error especfico.

    En un escenario un poco ms avanzado, podra usar la validacin de datos para calcular el valor mximo permitido en una

    celda segn un valor que se encuentra en otra parte del libro. En el siguiente ejemplo, el usuario ha escrito 4.000 dlares en

    la celda E7, lo cual supera el lmite mximo especificado para comisiones y bonificaciones.

    Si se aumentara o redujera el presupuesto de nmina, el mximo permitido en E7 tambin aumentara o se reducira

    automticamente.

    3.4. PASOS PARA VALIDAR

    Las opciones de validacin de datos se encuentran en la ficha Datos, en el grupo Herramientas de datos.

    La validacin de datos se configura en el cuadro de dilogo Validacin de datos.

  • 3.5. Cundo es til la validacin de datos?

    La validacin de datos es sumamente til cuando desea compartir un libro con otros miembros de la organizacin y desea

    que los datos que se escriban en l sean exactos y coherentes.

    Puede usar la validacin de datos para lo siguiente, entre otras aplicaciones:

    Restringir los datos a elementos predefinidos de una lista Por ejemplo, puede limitar los tipos de

    departamentos a Ventas, Finanzas, Investigacin y desarrollo y TI. De forma similar, puede crear una lista de

    valores a partir de un rango de celdas que se encuentren en otra parte del libro.

    Restringir los nmeros que se encuentren fuera de un intervalo especfico Por ejemplo, puede especificar un

    lmite mnimo de deducciones de dos veces el nmero de hijos en una celda especfica.

    Restringir las fechas que se encuentren fuera de un perodo de tiempo especfico Por ejemplo, puede

    especificar un perodo de tiempo entre la fecha actual y los 3 das siguientes.

    Restringir las horas que se encuentren fuera de un perodo de tiempo especfico Por ejemplo, puede

    especificar un perodo de tiempo para servir el desayuno entre la hora en que abre el restaurante y cinco horas

    despus.

  • Limitar la cantidad de caracteres de texto Por ejemplo, puede limitar el texto permitido en una celda a 10

    caracteres o menos. De forma similar, puede establecer la longitud especfica de un campo de nombre completo

    (C1) en la longitud actual de un campo de nombre (A1) y un campo de apellidos (B1), ms 10 caracteres.

    Validar datos segn frmulas o valores de otras celdas Por ejemplo, puede usar la validacin de datos para

    establecer un lmite mximo para comisiones y bonificaciones de 3.600 dlares, segn el valor de nmina

    proyectado general. Si los usuarios escriben un valor de ms de 3.600 dlares en la celda, aparecer un mensaje

    de validacin.

    3.6. Mensajes de validacin de datos

    Lo que los usuarios vean al escribir datos no vlidos en una celda depende de cmo se haya configurado la validacin de

    datos. Puede elegir mostrar un mensaje de entrada cuando el usuario seleccione la celda. Los mensajes de entrada suelen

    usarse para ofrecer a los usuarios orientacin acerca del tipo de datos que debe especificarse en la celda. Este tipo de

    mensaje aparece cerca de la celda. Si lo desea, puede mover este mensaje y dejarlo visible hasta que el usuario pase a otra

    celda o presione ESC.

    Tambin puede elegir mostrar un mensaje de error que solo aparecer cuando el usuario escriba datos no vlidos.

  • 3.7. Tipos de mensajes de error:

    Icono Tipo Se usa para

    Detener Evitar que los usuarios escriban datos no vlidos en una celda. Un mensaje de alerta Detener tiene dos opciones:

    Reintentar o Cancelar.

    Advertencia Advertir a los usuarios que los datos que han escrito no son vlidos, pero no les impide escribirlos. Cuando aparece

    un mensaje de alerta Advertencia, los usuarios pueden hacer clic en S para aceptar la entrada no vlida, en No para

    editarla o en Cancelar para quitarla.

    Informacin Informar a los usuarios que los datos que han escrito no son vlidos, pero no les impide escribirlos. Este tipo de

    mensaje de error es el ms flexible. Cuando aparece un mensaje de alerta Informacin, los usuarios pueden hacer clic

    en Aceptar para aceptar el valor no vlido o en Cancelar para rechazarlo.

    Puede personalizar el texto que los usuarios ven en un mensaje de error. Si elige no hacerlo, los usuarios vern un mensaje

    predeterminado.

    Los mensajes de entrada y de error slo aparecen cuando los datos se escriben directamente en las celdas. No aparecen en

    los siguientes casos:

    El usuario escribe datos en la celda mediante copia o relleno.

    Una frmula en la celda calcula un resultado que no es vlido.

    Una macro (macro: accin o conjunto de acciones utilizados para automatizar tareas. Las macros se graban en el

    lenguaje de programacin de Visual Basic para Aplicaciones.) especifica datos no vlidos en la celda.

    3.8. Sugerencias para trabajar con la validacin de datos

    En la siguiente lista, encontrar sugerencias para trabajar con la validacin de datos en Excel.

    Si tiene previsto proteger (proteger: configurar una hoja de clculo o un libro para impedir que los usuarios puedan

    ver o tener acceso a los elementos de la hoja o del libro especificado.) la hoja de clculo o el libro, hgalo despus

    de haber terminado de configurar la validacin. Asegrese de desbloquear cualquier celda validada antes de

    proteger la hoja de clculo. De lo contrario, los usuarios no podrn escribir en las celdas.

    Si tiene previsto compartir el libro, hgalo nicamente despus de haber configurado la validacin y la proteccin

    de datos. Despus de compartir un libro, no podr cambiar la configuracin de validacin a menos que deje de

    compartirlo pero Excel continuar validando las celdas que haya designado mientras el libro est compartido.

    Puede aplicar la validacin de datos a celdas en las que ya se han escrito datos. No obstante, Excel no le notificar

    automticamente que las celdas existentes contienen datos no vlidos. En este escenario, puede resaltar los datos

  • no vlidos indicando a Excel que los marque con un crculo en la hoja de clculo. Una vez que haya identificado los

    datos no vlidos, puede ocultar los crculos nuevamente. Si corrige una entrada no vlida, el crculo desaparecer

    automticamente.

    Para quitar rpidamente la validacin de datos de una celda, seleccione la celda y a continuacin abra el cuadro de

    dilogo Validacin de datos (ficha Datos, grupo Herramientas de datos). En la ficha Configuracin, haga clic en

    Borrar todos.

    Para buscar las celdas de la hoja de clculo que tienen validacin de datos, en la ficha Inicio en el grupo

    Modificar, haga clic en Buscar y seleccionar y a continuacin en Validacin de datos. Una vez que haya

    encontrado las celdas que tienen validacin de datos, puede cambiar, copiar o quitar la configuracin de validacin.

    Cuando crea una lista desplegable, puede usar el comando Definir nombre (ficha Frmulas, grupo Nombres

    definidos) para definir un nombre para el rango que contiene la lista. Despus de crear la lista en otra hoja de

    clculo, puede ocultar la hoja de clculo que contiene la lista y proteger el libro para que los usuarios no tengan

    acceso a la lista.

    3.9. Si la validacin de datos no funciona, asegrese de que:

    Los usuarios no estn copiando datos ni rellenando celdas. La validacin de datos est diseada para

    mostrar mensajes y evitar entradas no vlidas slo cuando los usua...