Lectura Módulo 5

Descargar como pdf o txt
Descargar como pdf o txt
Está en la página 1de 17

1 Validación de datos en Excel

1. ¿Qué es la validación de datos?


La validación de datos es una función de Excel que permite establecer
restricciones respecto a los datos que se pueden o se deben escribir en una celda.
La validación de datos puede configurarse para impedir que los usuarios escriban
datos no válidos. Si lo prefiere, puede permitir que los usuarios escriban datos no
válidos en una celda y advertirles cuando intenten hacerlo. También 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 números 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 válidos en la


celda, como un número de dos o de cinco dígitos, puede mostrarles un mensaje
de error específico.

En un escenario un poco más avanzado, podría usar la validación de datos para


calcular el valor máximo permitido en una celda según un valor que se encuentra
en otra parte del libro. En el siguiente ejemplo, el usuario ha escrito 4.000 dólares
en la celda E7, lo cual supera el límite máximo especificado para comisiones y
bonificaciones.
Si se aumentara o redujera el presupuesto de nómina, el máximo permitido en E7
también aumentaría o se reduciría automáticamente.

Las opciones de validación de datos se encuentran en el grupo Herramientas de


datos.

La validación de datos se configura en el cuadro de diálogo Validación de datos.


2. ¿Cuándo es útil la validación de datos?
La validación de datos es sumamente útil cuando desea compartir un libro con
otros miembros de la organización y desea que los datos que se escriban en él
sean exactos y coherentes.

1. Aplicaciones:
 Restringir los datos a elementos predefinidos de una lista Por ejemplo,
puede limitar los tipos de departamentos a Ventas, Finanzas, Investigación y
desarrollo y TI. De forma similar, puede crear una lista de valores a partir de
un rango de celdas que se encuentran en otra parte del libro. Para obtener
más información, vea Crear una lista desplegable a partir de un rango de
celdas.

 Restringir los números que se encuentren fuera de un intervalo


específico Por ejemplo, puede especificar un límite mínimo de deducciones
de dos veces el número de hijos en una celda específica.

 Restringir las fechas que se encuentren fuera de un período de tiempo


específico Por ejemplo, puede especificar un período de tiempo entre la
fecha actual y los 3 días siguientes.

 Restringir las horas que se encuentren fuera de un período de tiempo


específico Por ejemplo, puede especificar un período de tiempo para servir
el desayuno entre la hora en que abre el restaurante y cinco horas después.

 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 específica de un campo de nombre completo
(C1) en la longitud actual de un campo de nombre (A1) y un campo de
apellidos (B1), más 10 caracteres.

 Validar datos según fórmulas o valores de otras celdas Por ejemplo,


puede usar la validación de datos para establecer un límite máximo para
comisiones y bonificaciones de 3.600 dólares, según el valor de nómina
proyectado general. Si los usuarios escriben un valor de más de 3.600
dólares en la celda, aparecerá un mensaje de validación.

1.2.2 Mensajes de validación de datos


Lo que los usuarios verán al escribir datos no válidos en una celda depende de
cómo se haya configurado la validación de datos. Puede elegir mostrar un mensaje
de entrada cuando el usuario seleccione la celda. Este tipo de mensaje aparece
cerca de la celda. Si lo desea, este mensaje puede moverse y dejarse visible hasta
que el usuario pase a otra celda o presione ESC.

Por lo general, los mensajes de entrada se usan para orientar a los usuarios
acerca del tipo de datos que deben escribirse en una determinada celda.

1.2.3 Mensaje deerror


También puede elegir mostrar un mensaje de error que sólo aparecerá cuando el
usuario escriba datos no válidos.

1.2.3.1Tipos de mensaje de error


Icon Tipo Se usa para
o
Detener Evitar que los usuarios escriban datos no válidos en una celda.
Un mensaje de alerta Detener tiene dos opciones: Reintentar o
Cancelar.
Advertenci Advertir a los usuarios que los datos que han escrito no son
a válidos, pero no les impide escribirlos. Cuando aparece un
mensaje de alerta Advertencia, los usuarios pueden hacer clic en
Sí para aceptar la entrada no válida, en No para editarla o en
Cancelar para quitarla.
Información Informar a los usuarios que los datos que han escrito no son
válidos, pero no les impide escribirlos. Este tipo de mensaje de
error es el más flexible. Cuando aparece un mensaje de alerta
Información, los usuarios pueden hacer clic en Aceptar para
aceptar el valor no válido o en Cancelar para rechazarlo.

Puede personalizar el texto que los usuarios ven en un mensaje de error. Si elige
no hacerlo, los usuarios verán un mensaje predeterminado.

Los mensajes de entrada y de error sólo 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 fórmula en la celda calcula un resultado que no es válido.

 Una macro especifica datos no válidos en la celda.

2 Formulas y constantes
1. Descripción general de formulas
Las fórmulas son ecuaciones que efectúan cálculos con los valores de la hoja de
cálculo. Una fórmula comienza por un signo igual (=). Por ejemplo, la siguiente
fórmula multiplica 2 por 3 y, a continuación, suma 5 al resultado.

=5+2*3

Una fórmula también puede contener lo siguiente: función, referencias, función


operador y función operador constante.

2.1.1 Partes de una fórmula


1. Funciones: la función PI() devuelve el valor de pi: 3,142...

2. Referencias: A2 devuelve el valor de la celda A2.

3.Constantes: números o valores de texto escritos directamente en una fórmula,


por ejemplo, 2.
4. Operadores: el operador ^ (acento circunflejo) eleva un número a una potencia,
y el operador * (asterisco) multiplica.

2.1.2 Utilizar constantes enfórmulas


Una constante es un valor que no se calcula. Por ejemplo, la fecha 9-10-2008, el
número 210 y el texto "Ganancias trimestrales" son constantes. Una expresión, o
un valor obtenido como resultado de una expresión, no es una constante. Si utiliza
valores constantes en la fórmula en vez de referencias a celdas (por ejemplo,
=30+70+110), el resultado cambia sólo si modifica la fórmula.

3 Tipos de datos que pueden contener las celdas


Todos los libros de Excel están formados por hojas, cada una de las hojas contiene
más de 17 mil millones de celdas y en cada una de esas celdas podremos
almacenar nuestros datos. Por esta razón es importante conocer los tipos de datos
que podemos ingresar en cada una de las celdas de Excel.

1. Valores numéricos en Excel


El primer tipo de dato que analizaremos serán los valores numéricos los cuales
representan cantidades, montos, porcentajes, etc. Cuando ingresamos un valor
numérico en una celda el contenido será alineado a la derecha:

Un valor numérico puede tener un formato especial, es decir, Excel puede


desplegar el valor numérico de diferentes maneras. Por ejemplo, en la siguiente
imagen puedes observar que las columnas B y C tienen los mismos valores
numéricos que la columna A pero son desplegados de una manera diferente:
Es importante distinguir entre el valor de una celda y el valor desplegado por la
celda. En el ejemplo anterior las celdas de una misma fila tienen el mismo valor
pero son desplegados de manera diferente debido al formato aplicado a la celda el
cual modifica la apariencia pero no su valor. Para este ejemplo el formato de las
columnas B y C fue aplicado utilizando la lista de formatos que se encuentra en la
pestaña Inicio, dentro del grupo Número.

3.2 Las fechas en Excel sonnúmeros


Uno de los datos que vemos frecuentemente en Excel son las fechas y que
pueden desplegarse de diferentes maneras. En la siguiente imagen puedes
observar diferentes formatos que Excel puede aplicar a una misma fecha:

Aunque cada celda muestra un formato de fecha diferente, en realidad todas las
celdas tienen el mismo valor. Una manera de conocer el valor numérico de una
fecha es cambiando el formato de la celda al formato general. En la siguiente
imagen he copiado los mismos valores de la columna A en la columna B y he
aplicado el formato General.
datos como los nombres de empleados o códigos de clientes que necesitamos
incluir para dar significado a otros valores numéricos de nuestra hoja.

Por texto entendemos aquellas cadenas que están formadas por caracteres
alfanuméricos y/o símbolos especiales. Una manera de identificar fácilmente una
celda con un valor de texto es porque dicho valor se alineará de manera
automática a la izquierda. Inclusive, un valor que comience por un número seguido
de algunas letras es considerado texto y por lo tanto estará alineado a la izquierda
de cualquier celda. A continuación puedes observar algunos ejemplos de celdas
que contienen texto:

3.5 Formulas enExcel


A parte de los valores numéricos y el texto, una celda de Excel puede contener
una fórmula que no es más que un conjunto de “instrucciones” que manipularán
los valores numéricos o texto y nos devolverán un resultado.

Ya que las fórmulas son la base de Excel trataremos su uso en varias lecciones
posteriores. Por ahora solo debes recordar que una fórmula hace uso de los
valores numéricos y el texto para hacer algún cálculo u operación y devolvernos un
resultado que a su vez será un valor numérico o un texto.

4 Edición de celdas
El contenido de las celdas se puede modificar directamente en ellas. También se
puede modificar el contenido de las celdas en la barra de fórmulas.

NOTA En modo de edición, muchos comandos de la cinta de opciones


permanecen inactivos, por lo que no podrá utilizarlos.

1. Procedimientos
1. Para poner la celda en modo de edición, siga uno de los procedimientos
siguientes:
o Haga doble clic en la celda que contiene los datos que desea
modificar.

o Haga clic en la celda que contiene los datos que desea modificar y, a
continuación, haga clic en cualquier punto de la barra de fórmulas.

De este modo se coloca el punto de inserción en la celda o en la barra de


fórmulas.

2. Sugerencia Para llevar el punto de inserción hasta el final del contenido


de la celda, haga clic en la celda y presione F2.

3. Para modificar el contenido de una celda, siga uno de los procedimientos


siguientes:

o Para eliminar caracteres, haga clic en el punto en el que desea


eliminarlos y presione RETROCESO, o selecciónelos y presione
SUPRIMIR.

o Para insertar caracteres, haga clic en el punto en el que desea


insertarlos y, a continuación, escriba los caracteres nuevos.

o Para reemplazar caracteres específicos, selecciónelos y, a


continuación, escriba los caracteres nuevos.

o Para activar el modo Sobrescribir para que los caracteres existentes


se puedan reemplazar por caracteres nuevos a medida que se
escribe, presione INSERTAR.

NOTA El modo Sobrescribir se puede activar o desactivar cuando se está en modo


de edición. Cuando está activado, el carácter a la derecha del punto de inserción
se resalta en la barra de fórmulas y se sobrescribirá cuando escriba.

o Para comenzar una línea de texto nueva en un punto específico de


una celda, haga clic en el punto en el que desea interrumpir la línea
y, a continuación, presione ALT+ENTRAR.

2. Para confirmar los cambios, presione ENTRAR.

Sugerencia Antes de presionar ENTRAR, puede cancelar los cambios hechos si


presiona ESC. Tras presionar ENTRAR, podrá cancelar los cambios realizados
haciendo clic en Deshacer en la barra de herramientas de acceso rápido.

NOTA
 Una celda mostrará ##### cuando contenga datos con un formato de
número más amplio que el ancho de la columna. Para ver todo el texto,
debe aumentar el ancho de la columna.

2. Cómo cambiar el ancho de columna


a. Haga clic en la celda cuyo ancho de columna desea cambiar.

b. En el grupo Celdas de la ficha Inicio, haga clic en Formato.

5 Referencias
Las referencias en Excel son parte fundamental de la hoja de cálculo ya que a
través de ellas podemos identificar cada una de las celdas de un libro de trabajo y
de esa manera acceder al valor contenido en cualquiera de ellas.

El objetivo de este artículo es aclarar la mayor cantidad de dudas sobre el tema de


referencias en Excel y será útil para aquellas personas que comienzan a utilizar la
herramienta pero también para aquellos que ya tienen un poco de tiempo
utilizando la hoja de cálculo y no han escuchado sobre temas como las referencias
3D o el estilo de referencia F1C1.

Ya que todos los valores de una hoja de cálculo están almacenados en las celdas,
es de suma importancia para los usuarios de Excel conocer la manera en que
deberán acceder a dichos valores utilizando las referencias. Sin más preámbulo,
comenzaremos con este estudio de las referencias en Excel.

1. ¿Qué son las referencias en Excel?


Todo usuario de Excel debe saber que todas las hojas están divididas en columnas
y filas. Esta división se hace evidente en la interfaz gráfica de Excel de la siguiente
manera:

 Por debajo de la barra de fórmulas se encuentran los encabezados de


columna identificados por una letra.

 En el extremo izquierdo están los encabezados de fila que muestran un


número.

 En el área de trabajo tenemos una cuadrícula que delimita el área de cada


celda de la hoja y donde se puede observar que cada una de ellas
pertenece a una determinada columna y fila.
En la imagen anterior, la celda seleccionada (celda activa) es la celda que se
encuentra bajo la columna B y en la línea 3 y por lo tanto su dirección dentro de la
hoja será B3. La referencia de una celda siempre constará de dos partes: la
primera parte indicará la letra (o letras) de la columna a la que pertenece y la
segunda parte indicará su número de fila.

Excel siempre nos ayudará a conocer fácilmente la referencia de la celda activa.


En primer lugar, el encabezado de la columna y fila de la celda seleccionada
estarán resaltados en un color diferente al resto de los encabezados. Además el
Cuadro de nombres siempre mostrará la referencia de la celda activa tal como lo
puedes observar en la siguiente imagen:

En conclusión, la referencia de una celda nos indica su ubicación dentro de una


hoja, es decir, nos ayuda a conocer rápidamente la columna y fila a las que
pertenece.

5.2 Tipos de referencias enExcel


Una referencia siempre será la dirección de una celda dentro de una hoja y
siempre estará formada por la columna y fila en donde se ubica la celda. Así que,
cuando hablamos de tipos de referencias en Excel estamos hablando sobre los
diferentes tipos de comportamiento que pueden tener las referencias al momento
de ser copiadas a otras celdas.

Es muy fácil copiar fórmulas en Excel, pero ¿qué sucede con las referencias de
dicha fórmula al momento de hacer la copia? Es ahí en donde su comportamiento
dependerá del tipo de referencia que se haya utilizado y analizaremos las
alternativas que tenemos en las próximas tres secciones.

5.2.1 Referencias relativas en Excel


De manera predeterminada, las referencias en Excel son relativas. El término
relativo significa que al momento de copiar una fórmula, Excel modificará las
referencias en relación a la nueva posición donde se está haciendo la copia de la
fórmula. Por ejemplo, supongamos que en la celda C1 tenemos la fórmula
=A1+B1. En la siguiente imagen puedes observar el resultado de esta operación si
tenemos los valores 5 y 3 en dichas celdas:

Si ahora copio (Ctrl+C) la fórmula de la celda C1 y la pego (Ctrl+V) en la celda C3,


obtendré el siguiente resultado:

La fórmula que ha sido copiada a la celda C3 nos devolverá como resultado el


valor cero. Al revisar la fórmula de dicha celda nos daremos cuenta que eso se
debe a que Excel ha modificado automáticamente las referencias de la fórmula. En
lugar de la celda A1 ahora tenemos la celda A3, y en lugar de la celda C1 que
teníamos originalmente, ahora tenemos la celda C3.

En este ejemplo hemos copiado la fórmula hacia una celda de la misma columna,
pero dos filas por debajo y eso es lo que indicará a Excel el ajuste a realizar en las
referencias. Ya que no hubo cambio en la columna, ésta permanecerá igual en la
nueva fórmula, pero ya que copiamos una celda que está dos filas hacia abajo,
Excel realizará dicho ajuste sumando la misma cantidad de filas a las referencias.
Ahora observa lo que sucede si vuelvo a copiar la fórmula de la celda C1 y la pego
en la celda F2.
En esta ocasión la nueva celda se encuentra una fila hacia abajo y tres columnas a
la derecha de la celda original y por lo tanto las referencias se adecúan a tal
movimiento añadiendo tres columnas y una fila a cada referencia de la fórmula
copiada.

Las referencias relativas son muy útiles cuando necesitamos aplicar una misma
fórmula sobre un rango de datos. Por ejemplo, en la siguiente imagen puedes ver
una hoja donde las columnas A y B tienen valores numéricos y ahora necesito
colocar la multiplicación de ambos valores en la columna C. Para hacerlo
rápidamente, coloco la fórmula =A1*B1 en la celda C1 y posteriormente copio
dicha fórmula hacia debajo:

De esta manera obtendremos automáticamente el resultado correcto para cada fila


ya que Excel modifica las referencias en cada fórmula mientras es copiada hacia
abajo.

5.2.2 Referencias absolutas en Excel


Hay ocasiones en las que necesitamos “fijar” la referencia a una celda de manera
que permanezca igual aún después de ser copiada. Si queremos impedir que
Excel modifique las referencias de una celda al momento de copiar la fórmula,
entonces debemos convertir una referencia relativa en absoluta y eso lo podemos
hacer anteponiendo el símbolo “$” a la letra de la columna y al número de la fila de
la siguiente manera:
PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR
TEMA 6
APPLICATIONS

1. INTRODUCCIÓN A LAS MACROS DE EXCEL

Una macro es un conjunto de comandos que se almacenan en Excel de manera que están
siempre disponibles cuando se necesita ejecutarlas. Las macros se utilizan principalmente para
evitar tener que repetir los pasos de aquellas tareas que se realizan una y otra vez.

Las macros se escriben en un lenguaje de programación que se denomina Visual Basic for
Applications (VBA). Aunque no es necesario conocer a fondo este lenguaje para crear una
macro, ya que Excel contiene una herramienta especial de programación, manejar este
lenguaje permite acceder a todas las funcionalidades de Excel y ampliar las posibilidades del
programa.

Como para cualquier otro lenguaje de programación, hay que aprender a utilizar los comandos
que le son propios, de manera que la aplicación pueda ejecutar las tareas programadas.

Las macros se pueden crear de dos maneras, utilizando un Editor de Visual Basic o utilizando la
Grabadora de Macros de Excel. Ambas opciones se ejecutan desde la Ficha Programador de la
cinta de opciones. Al igual que ocurría con el Solver, la Ficha Programador no aparece por
defecto al abrir la aplicación, sino que es necesario activarla. Para ello, desde la Ficha Archivo,
hay que seleccionar Opciones  Opciones de Excel  Personalizar la cinta de opciones 
seleccionar la opción Programador:

2
PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR
TEMA 6
APPLICATIONS

La Ficha Programador tiene el siguiente aspecto:

 El Grupo Código contiene los comandos necesarios para inicial el Editor de Visual Basic
donde se puede escribir directamente código VBA. También permite ver la lista de macros
disponibles para poder ejecutarlas o eliminarlas. También contiene el comando Grabar
Macro, que permite crear una nueva macro sin necesidad de conocer programación en
VBA.
 El Grupo Complementos permite habilitar y administrar complementos como el Solver.
 El Grupo Controles permite agregar controles especiales a una hoja de Excel, tales como
botones, casillas de verificación, botones de opciones, etc.
 El Grupo XML permite trabajar con ficheros XML.
 El Grupo Modificar contiene únicamente el comando Panel de Documentos.

1.1 LA GRABADORA DE MACROS

La Grabadora de Macros es la forma más sencilla de crear una macro. Consiste básicamente en
grabar todos los pasos que el usuario ejecuta para resolver un problema y seguir ese mismo
esquema para resolver problemas similares en el futuro. Por tanto, al utilizar la grabadora es
conveniente planificar previamente los pasos que se van a seguir y no realizar acciones
innecesarias mientras se realiza la grabación.

La grabadora se activa con el comando Grabar macro. Al pulsar el botón se abre un cuadro de
diálogo en el que hay que introducir el nombre de la macro y la ubicación donde se desea
guardar1. Una vez creada una macro, el comando se transforma en Detener grabación.

1
Si la macro se guarda en un libro nuevo puede ser ejecutada desde cualquier libro creado durante la
sesión actual de Excel. Si se guarda en un libro de macros personal, la macro se podrá utilizar en
cualquier momento sin importar el libro de Excel que se esté utilizando.

3
PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR
TEMA 6
APPLICATIONS

Ejemplo 1: Grabar una macro sencilla.

Una macro puede ser cualquier conjunto de comandos, desde cambiarle el color a una celda,
hasta introducir una función y resolverla. En este ejemplo, se ha grabado un ejercicio de tipo
Buscar Objetivo igual que uno de los que se hizo en el tema 2. Se ha cambiado el formato de la
celda del título para combinar y centrar un conjunto de celdas, cambiar la fuente a negrita en
blanco y el fondo a rojo, se ha escrito el título y los rótulos de las casillas, se ha recuadrado el
conjunto de casillas y se ha utilizado el comando Buscar objetivo para resolver el problema. La
macro se ha guardado con el nombre Y_Si en el libro personal de macros.

El resultado al ejecutarla con el comando Macros es el esperado:

Para ver el código exacto que contiene esta macro, en lugar de ejecutarla se puede abrir para
modificar. Esto abre el Editor de Visual Basic:

Podemos ver en detalle cada uno de los pasos que incluye el programa:

4
PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR
TEMA 6
APPLICATIONS

1.2 SEGURIDAD DE MACROS

De manera predeterminada Excel no permite ejecutar macros automáticamente. Así se evita


que al abrir un archivo que no ha creado el propio usuario se ejecute una macro con código
malicioso. No obstante, esta configuración se puede modificar desde la Ficha Archivo 
Opciones  Centro de confianza  Configuración del centro de confianza. Se abre un cuadro
de diálogo en el que se puede elegir, para el submenú Configuración de macros, lo siguiente:

 Deshabilitar todas las macros sin notificación: permite ejecutar únicamente las macros
que están almacenadas en un lugar confiable, que, por defecto, son ciertos directorios
dentro de la carpeta que contiene los archivos de programa de MS Office en el ordenador
del usuario.
 Deshabilitar todas las macros con notificación: muestra una alerta de seguridad
advirtiendo de la intención de ejecutar una macro, de manera que el usuario decida si la
ejecuta o no. Es la opción predeterminada.
 Deshabilitar todas las macros excepto las firmadas digitalmente: permite ejecutar
únicamente las macros con una firma digital y, por tanto, autor conocido.
 Habilitar todas las macros: permite ejecutar todas las macros sin enviar ninguna
notificación al usuario, tanto de fuentes conocidas como desconocidas.

También podría gustarte