CAPITULO 1

Manual de Excel Avanzado

CONTENIDO

41.Formato Personalizado de Celdas

4a.Cuando crear formatos personalizados

4b.Como crear formatos personalizados

7c.Cdigos de formato de nmero para fechas y horas

8d.Cdigos de formato de nmero para posiciones decimales, espacios, colores y condiciones

10e.Cdigos de formato de nmero para texto y espaciado

11f.Eliminar un formato de nmero personalizado

11g.Si las fechas introducidas en la hoja de clculo no tienen el mismo aspecto

112.Usar Funciones avanzadas para calcular valores

12Funcin SUMAR.SI

12Ejercicio N 1: Clculos condicionales en bienes inmuebles

13La Funcin CONTAR.SI

14Frmulas Matriciales:

14a.Cmo crear un total basndose en varias condiciones

15Las Funcines K.ESIMO.MENOR, y K.ESIMO.MAYOR

15Las Funciones REDONDEAR.MENOS, y REDONDEAR.MAS

16La Funcin ALEATORIO

16Las Funciones ES

19La Funcin HOY

19La Funcin AHORA

19Funciones de Bases de Datos BDfuncin

21Ejercicio N 2: Clculos con funciones de base de datos BD

22Funciones de Texto

22La Funcin IZQUIERDA

22La Funcin DERECHA

23La Funcin EXTRAE

23La Funcin HALLAR

24La Funcin ENCONTRAR

24La Funcin LARGO

24Las Funcines MAYUSC, MINUSC y NOMPROPIO

25La Funcin SUSTITUIR

25La Funcin DECIMAL

27Ejercicio N 3: Generacin de cdigos con funciones de texto

29Ejercicio N 4: Convertidor de Nmeros en Letras

31La Funcin ESPACIOS

323.Validacin de Ingreso de datos

32a.Que es la validacin

32b.Cmo validar el ingreso de datos en un rango de celdas

34c.Realizar una auditoria en un rango validado

374.Lneas de Tendencia en los Grficos

37a.Utilidad de las lneas de tendencia

37b.Tipos de grfico que admiten lneas de tendencias

38c.Agregar una lnea de tendencia a una serie de datos

415.Uso del botn Cmara Fotogrfica

41a.Cmo habilitar el botn Cmara Fotogrfica

41b.Cmo utilizar el botn Cmara Fotogrfica

426.La Programacin en Excel

42a.Uso de la grabadora de macros

43b.Ejecucin de una macro

44c.Uso del Editor de Visual Basic

45d.Para ingresar al Editor de Visual Basic

45e.Modificar una macro en el Editor de Visual Basic

477.Creacin de Macros con la grabadora de macros

47a.Que es la grabadora de macros

47b.Cmo modificar las instrucciones de las macros

478.Creacin de Plantillas en Excel

47a.Que es una Plantilla

47b.Como se crea una Plantilla

48Ejercicio N 5: Acciones repetitivas con macros

51Ejercicio N 6: Un control de Caja Chica

57Ejercicio - Resultados de una Encuesta

65La Funcin FILA

65La Funcin FILAS

669.Edicin de Macros en Visual Basic para Aplicaciones

66a.Que es Visual Basic para Aplicaciones

66b.El Editor de Visual Basic

6710.Las instrucciones de Visual Basic

68a.Continuar instrucciones en mltiples lneas

69b.Aadir comentarios

69c.Comprobar errores de sintaxis

6911.Escribir instrucciones de declaracin

7012.Escribir instrucciones de asignacin

7013.Trabajo con celdas en Visual Basic

71a.Referencia a celdas y rangos utilizando la notacin A1

71b.Para seleccionar celdas y rangos de celdas

72Ejercicio N 7: Procedimientos Sub para seleccionar rangos

79La Propiedad End

80Ejercicio N 8: Nombrar rangos, Seleccionar columnas y Borrar

83c.Para ingresar datos en celdas y rangos de celdas

84Ejercicio N 9: Asignar valores a celdas y rangos

86d.Usos del mtodo Select y la propiedad Selection

87Ejercicio N 10: Procedimientos Sub para Cambio de moneda

90Ejercicio N 11: Procedimientos Sub para Ahorro personal

94Prctica de Laboratorio N1

941ra Parte:Control de Cuentas por Pagar

982da Parte:Tabla Dinmica para Resumen de Cuentas por Pagar

99Prctica de Laboratorio N 2

991ra Parte:Creacin del cuadro Movimientos de Almacn

1002da Parte:Tabla de Saldos Finales y Grfico de Movimientos por Vehculo

1013ra Parte: Obtencin del Cuadro de Kardex

1024ta Parte:Obtencin del Grfico de Existencia en Almacn

1025ta Parte:Creacin de una macro para obtener el Kardex de cada vehculo

104Prctica de Laboratorio N 3

109Prctica de Laboratorio N 4

1101ra Parte:Creacin de la Lista de Precios

1112da Parte:Creacin de la Lista de Clientes

1113ra Parte:Creacin de la Boleta de Venta

1134ta Parte:Traer la hoja que convierte nmeros en letras

1155ta Parte:Creacin del Registro de Ventas

115Crear la macro que Registra una Boleta de Venta

116Crear la macro que prepara una Nueva Boleta de Venta

118Cdigos ASCII para caracteres especiales:

1. Formato Personalizado de Celdasa. Cuando crear formatos personalizadosLa apariencia que pueden adquirir los nmeros en las celdas, pueden variar segn el formato que se les asigne y para esto existen varias categoras de formatos. Pero si estas no satisfacen nuestras necesidades, uno mismo puede crear sus propios formatos de nmeros personalizados. Ejemplo de formatos tradicionales existentes en Excel:

A

13,473.50Estilo millares#,##0.00

2S/. 672.25Estilo monedaS/. #,##0.00

313.7%Estilo porcentual0.0%

4(79.5)Negativos entre parntesis(0.00)

54-JunFechas con da y mesd-mmm

Ejemplo de formatos personalizados creados por el usuario:

A

664.5 KgPara pesos en kilogramos0.0 Kg

737.4 CGrados centgrados0.0C

8000254Para cdigos000000

9IGV 19%Texto y porcentajeIGV 0%

104 puntosNmero y texto0 puntos

Tenga en cuenta que en todos los ejemplos anteriores las celdas tienen datos numricos y aunque el formato ha cambiado su apariencia, el contenido de ellas sigue siendo numrico y puede operarse matemticamente en cualquier formula.Nota.-Cuando se crean formatos personalizados, estos formatos solo tendrn validez dentro del libro en el cual se crearon.

Para poder crear formatos personalizados hay que seguir unas cuantas reglas, las cuales pasamos a indicar a continuacin: b. Como crear formatos personalizados

En el men Formato/Celdas, elegir la categora Personalizada:

En la casilla de texto llamada Tipo se pueden definir formatos personalizados o elegir los ya existentes en la lista inferior.

Crear un formato de nmero personalizado

1.Seleccione las celdas a las que desea dar formato.

2.En el men Formato, haga clic en Celdas y haga clic en la ficha Nmero.

3.En la lista Categora, haga clic en una categora y, a continuacin, haga clic en un formato integrado que se asemeje al que se desee.

4.En la lista Categora, haga clic en Personalizada.

5.En el cuadro Tipo, modifique los cdigos de formato de nmero para crear el formato que desee.

Puede especificar hasta cuatro secciones de cdigos de formato. Las secciones, separadas por caracteres de punto y coma, definen los formatos de los nmeros positivos, nmeros negativos, valores cero y texto, en ese orden. Si especifica slo dos secciones, la primera se utiliza para los nmeros positivos y ceros, y la segunda se utiliza para los nmeros negativos. Si especifica slo una seccin, todos los nmeros utilizan ese formato. Si omite una seccin, incluya el punto y coma que separa esa seccin.

Formato de nmeros positivos

Formato de ceros

#,##0.00_) ; [ROJO](# ,##0.00) ; 0.00 ; Factura @

Formato de nmeros negativos

Formato de textos

Nota.-El smbolo de arroba @ representa el contenido de la celda cuando este contenido es del tipo texto.

Utilice cdigos de formato que describan cmo desea mostrar un nmero, una fecha u hora, una moneda, porcentajes o notacin cientfica y un texto o espacio.Ejemplo:

A un grupo de celdas con diferentes datos se le da el siguiente formato personalizado:

[AZUL]#,##0.00_) ; [ROJO](# ,##0.00) ; Sin Valor ; @ anulada

A

1-8734.9Nmero negativo

20Cero

3FacturaTexto

46254.258Nmero positivo

5BoletaTexto

El resultado final en el rango formateado es el siguiente:

A

1(8,734.90)Entre parntesis y color rojo

2Sin ValorCero cambia a Sin Valor

3Factura anulada Se le agrega la palabra anulada

46,254.26 Redondea y de color azul

5Boleta anuladaSe le agrega la palabra anulada

Como se puede ver, cada celda adquiere el formato que le corresponde segn el contenido que esta tenga.

c. Cdigos de formato de nmero para fechas y horasDas, meses y aos Para presentar das, meses y aos, incluya en una seccin los siguientes cdigos de formato. Si utiliza una "m" inmediatamente detrs del cdigo "h" o "hh", o bien inmediatamente delante del cdigo "ss", Microsoft Excel presentar los minutos en lugar de presentar el mes. Para verUse este cdigo

Los meses como 112m

Los meses como 0112mm

Los meses como ene.dic.mmm

Los meses como enerodiciembremmmm

Los meses como la inicial de cada mes mmmmm

Los das como 131d

Los das como 0131dd

Los das como lun.sb.ddd

Los das como lunessbadodddd

Los aos como 0099aa

Los aos como 19009999aaaa

Horas, minutos y segundosPara presentar horas, minutos y segundos, incluya en una seccin los siguientes cdigos de formato.

Para verUse este cdigo

Las horas como 023h

Las horas como 0023hh

Los minutos como 059m

Los minutos como 0059mm

Los segundos como 059s

Los segundos como 0059ss

Las horas como 4 a.m.h a.m./p.m.

La hora como 4:36 p.m.h:mm a.m./p.m.

La hora como 4:36:03 p.h:mm:ss a/p

El tiempo transcurrido en horas; por ejemplo, 25:02[h]:mm

El tiempo transcurrido en minutos; por ejemplo, 63:46[mm]:ss

El tiempo transcurrido en segundos[ss]

Fracciones de segundoh:mm:ss.00

Si el formato contiene la indicacin a.m. o p.m., la hora se basar en el formato de 12 horas, donde "a.m." o "a" indica las horas desde la medianoche hasta el medioda y "p.m." o "p" indica las horas desde el medioda hasta la medianoche. En caso contrario, el reloj se basar en el formato de 24 horas. La letra "m" o las letras "mm" deben aparecer inmediatamente detrs del cdigo "h" o "hh", o bien inmediatamente delante del cdigo "ss"; de lo contrario, Microsoft Excel presentar el mes en lugar de presentar los minutos.

d. Cdigos de formato de nmero para posiciones decimales, espacios, colores y condicionesUtilice los cdigos de formato de nmero para crear un formato de nmero personalizado.

Decimales y dgitos significativos

Para dar formato a las fracciones o los nmeros con decimales, incluya los siguientes dgitos marcadores en una seccin. Si un nmero tiene ms dgitos a la derecha del separador que marcadores en el formato, se redondear para que tenga tantos decimales como marcadores. Si hay ms dgitos a la izquierda del separador que marcadores, se presentarn los dgitos adicionales. Si el formato contiene solamente signos de nmero (#) a la izquierda del separador, los nmeros menores que la unidad comenzarn por el separador.#muestra nicamente los dgitos significativos y no muestra los ceros sin valor.

0(cero) muestra los ceros sin valor si un nmero tiene menos dgitos que la cantidad ceros en el formato.

?agrega los espacios de los ceros sin valor a cada lado del separador decimal, para alinear los decimales con formato de fuente de ancho fijo, como Courier New. Tambin puede utilizarse ? para las fracciones que tengan un nmero de dgitos variable.Para verUse este cdigo

1234.59 como 1234.6####.#

8.9 como 8.900#.000

.631 como 0.60.#

12 como 12.0 y 1234.568 como 1234.57#.0#

44.398. 102.65 y 2.8 con decimales alineados???.???

5.25 como 5 1/4 y 5.3 como 5 3/10, con los smbolos de divisin alineados# ???/???

Separador de millares

Para ver una coma como separador de los millares o para ajustar la escala de un nmero mediante un mltiplo de mil, incluya una coma en el formato de nmero.

Para verUse este cdigo

12000 como 12,000#,###

12000 como 12#,

12200000 como 12,20,0,,

Color

Para definir el color de una seccin del formato, escriba en la seccin el nombre de uno de los siguientes ocho colores entre corchetes. El color debe ser el primer elemento de la seccin.[NEGRO][AZUL][FUCSIA][ROJO]

[AGUAMARINA][VERDE][BLANCO][AMARILLO]

CondicionesPara definir los formatos de nmero que se aplicarn nicamente si coinciden con las condiciones que se hayan especificado, encierre la condicin entre corchetes. La condicin consta de un operador de comparacin y un valor. Por ejemplo, el siguiente formato muestra los nmeros iguales o inferiores a 100 en color rojo y los nmeros superiores a 100 en color azul.

[Rojo][100]

En cambio, para aplicar formatos condicionales a las celdas (por ejemplo, el sombreado de color que depende del valor de una celda) utilice el comando Formato condicional en el men Formato.

e. Cdigos de formato de nmero para texto y espaciadoAgregar caracteres

Para ver texto y nmeros en una celda, encierre los caracteres de texto entre comillas ("") o ponga delante de un solo carcter una barra invertida (\). Incluya los caracteres en la seccin correspondiente de los cdigos de formato. Por ejemplo, introduzca el formato $ 0.00 "Exceso";-$ 0.00 "Defecto" para ver un importe negativo como "-$125.74 Defecto". El espacio y los siguientes caracteres se muestran sin comillas: $ - + / ( ) : ! ^ & ' (comilla simple izquierda) ' (comilla simple derecha) ~ { } = < >

Incluir una seccin de texto

Si se incluye una seccin de texto, siempre ser la ltima seccin en el formato de nmero. Incluya el carcter @ en la seccin en que desee presentar el texto escrito en la celda. Si se omite el carcter @ en la seccin de texto, no se ver el texto que se haya escrito. Si desea ver siempre caracteres de texto especficos con el texto escrito, encierre el texto adicional entre comillas dobles (" "), por ejemplo, "facturacin bruta de "@

Si el formato no incluye una seccin de texto, el texto que escriba no se ver afectado por el formato.

Agregar espacio

Para crear un espacio con el ancho de un carcter en un formato de nmero, incluya un subrayado _ seguido del carcter. Por ejemplo, si despus de un subrayado se cierra un parntesis _) entonces los nmeros positivos se alinearn correctamente con los nmeros negativos que estn entre parntesis.

Repetir caracteres

Para repetir el carcter siguiente en el formato para llenar el ancho de la columna, incluya un asterisco (*) en el formato de nmero.

Por ejemplo, introduzca $*=#,##0.00 para incluir suficientes signos = despus del signo monetario para llenar una celda. El resultado sera: $=====2,716.25f. Eliminar un formato de nmero personalizado1.-En el men Formato, haga clic en Celdas y haga clic en la ficha Nmero.

2.-En la lista Categora, haga clic en Personalizada.

3.-En la parte inferior del cuadro Tipo, haga clic en el formato personalizado que desee eliminar.

4.-Haga clic en Eliminar.

Solamente pueden eliminarse los formatos personalizados. Microsoft Excel aplicar el formato predeterminado (General) a todas las celdas del libro a las que se haya dado formato mediante el formato personalizado que se ha eliminado.

g. Si las fechas introducidas en la hoja de clculo no tienen el mismo aspectoCuando se introduce una fecha en una celda, dicha fecha aparece en un formato de fecha predeterminado o en un formato aplicado a la celda antes de introducir la fecha. El formato de fecha predeterminado se basa en la configuracin de la ficha Fecha del cuadro de dilogo Configuracin regional, al que se tiene acceso desde el Panel de control de Windows. Si estos valores de fecha han cambiado, cualquier fecha existente en los libros no formateados con el comando Formato de celdas tambin cambia.

2. Usar Funciones avanzadas para calcular valoresConociendo ya las funciones bsicas de Excel, como son:

Suma, Promedio, Max, Min, Contar, Contara, Entero, Redondear, Raz, Pi, Si, Y, O, Buscarv, Buscarh, Subtotales, Indirecto.

Las cuales se han visto ya en el primer curso inicial de Excel, ahora veamos algunas otras funciones ms avanzadas:

Sumar.si, Contar.si, K.esimo.mayor, K.esimo.menor, Redondear.mas, Redondear.menos, Aleatorio, Esblanco, Esnumero, Estexto, Eserror, BdMax, Hoy, Ahora.

Funcin SUMAR.SISuma selectivamente las celdas que coinciden con el argumento criterio.

Sintaxis

SUMAR.SI(rango;criterio;rango a sumar)

Donde:

RangoEs el rango de celdas que se desea evaluar.

CriterioEs el criterio en forma de nmero, expresin o texto, que determina qu celdas se van a sumar. Por ejemplo, el argumento criterio puede expresarse como 32; "32"; ">32"; "manzanas"; D4.

Rango a sumarSon las celdas que se van a sumar. Las celdas contenidas en Rango a sumar se suman slo si las celdas correspondientes del Rango coinciden con el Criterio. Si Rango a sumar se omite, se suman las celdas contenidas en el argumento Rango.

Ejercicio N 1: Clculos condicionales en bienes inmueblesEn una hoja de clculo en las columnas A, B y C se tiene una relacin de las valorizaciones de varios bienes inmuebles pertenecientes a tres personas.

Al lado derecho se quiere calcular a cuanto ascienden los bienes acumulados de cada propietario.

Para calcular la valorizacin acumulada de las casas de cada propietario haga usted lo siguiente:1.-En F4 escribir la frmula:=SUMAR.SI(A4:A10;E4;C4:C10)2.-Luego copie esta frmula en F5 y F6.

3.-En F7 use Autosuma para sumar las tres celdas.Si tambin se desea calcular la suma de las valorizaciones que son inferiores a 100,000 soles, entonces:

4.-La formula en F11 sera:

=SUMAR.SI(C4:C10;"32"; "manzanas";D4.La funcin CONTARSI cuenta el nmero de veces que aparece un valor en un rango de celdas, por ejemplo:

Contar el nmero de celdas en el rango B4:B10 que contienen el texto "Sur".

=CONTAR.SI(B4:B10;"Sur ")

Pero, cmo haramos si se desea sumar todas las valorizaciones de casas ubicadas en una determinada zona y que adems pertenezcan a un determinado propietario?Para resolver clculos condicionales que requieren ms de un criterio, entonces se puede hacer uso de la combinacin de la funcin SUMA y la funcin SI, o tambin la funcin CONTAR y la funcin SI; pero escritas dentro de una frmula matricial.

Frmulas Matriciales:

a. Cmo crear un total basndose en varias condiciones

Nota.-En Excel algunas frmulas pueden escribirse como frmulas matriciales. Para esto hay que escribir la frmula y al finalizar hay que presionar la combinacin de teclas Ctrl+Shift+Enter, esto hace que la formula se encierre entre llaves al momento de ingresar a la celda, quedando de esta forma: {=formula}Primer Caso (para condiciones del tipo Y)

Ahora veremos la siguiente frmula matricial que permitir calcular el valor total de las valorizaciones de las celdas C4:C10, donde el rango A4:A10 contiene al propietario "Sonia", y el rango B5:B10 contiene la zona denominada "Norte".

{=SUMA(SI((A4:A10="Sonia")*(B4:B10="Norte");C4:C10))}

Las dos condiciones estn encerradas entre parntesis y entre ellas se escribe el operador de multiplicacin, lo cual indica que las dos condiciones deben cumplirse a la vez. (Condicin del tipo Y)Segundo Caso (para condiciones del tipo O)

Para calcular el valor total de las valorizaciones de las celdas C4:C10, donde A4:A10 contiene "Carlos" o "Jorge", utilice la siguiente frmula.

{=SUMA(SI((A4:A10 ="Carlos")+(A4:A10 ="Jorge");C4:C10))}

En este caso las dos condiciones encerradas entre parntesis se escribe con el operador de suma entre ellas, lo cual estara indicando que es suficiente que se cumpla una de las dos condiciones para que se realice el clculo. (Condicin del tipo O)No olvide que ambas son frmulas matriciales (estn encerradas entre llaves) y deben introducirse presionando al final CTRL+SHIFT+ENTER.Otros ejemplos:

Contar el nmero de veces que aparecen varias condiciones

En la siguiente frmula, cada vez que Excel encuentre "Sonia" en el rango A4:A10, comprobar la presencia del texto "Norte" en la misma fila en la columna B (el rango B4:B10). A continuacin, Excel calcular de varios modos, el nmero de filas que contienen ambos textos. En los tres casos las formulas obtienen el mismo resultado.{=CONTAR(SI((A4:A10="Sonia")*(B4:B10="Norte");1;0))}

o sino as:

{=SUMA(SI((A4:A10="Sonia")*(B4:B10="Norte");1;0))}

o tambin as:

{=SUMA(SI(A4:A10="Sonia ";SI(B4:B10="Norte";1;0)))}

En las primeras dos formulas el operador de multiplicacin indica que las condiciones son del tipo Y; y en la tercera formula se est utilizando dos funciones SI anidadas, o sea una funcin SI dentro de otra funcin SI.

Se trata en estos casos de formulas matriciales y deben introducirse presionando al final CTRL+SHIFT+ENTER.

Las Funcines K.ESIMO.MENOR, y K.ESIMO.MAYOR

Encuentra los primeros menores o primeros mayores valores de una lista.

Sintaxis

K.ESIMO.MENOR(rango;posicin)

K.ESIMO.MAYOR(rango;posicin)

Rangoes el rango de celdas que se desea analizar.Posicines ubicacin del nmero menor o mayor que se desea encontrar. Esto es, para el menor de todos seria 1, para el segundo menor sera 2, etc..Si se quisiera averiguar cual es la valorizacin que ocupa el segundo lugar entre las mayores, Y cual es la tercera valorizacin mas pequea, las frmulas seran:

=K.ESIMO.MAYOR(C4:C10;2)

186,000.00

=K.ESIMO.MENOR(C4:C10;3)

114,000.00

Las Funciones REDONDEAR.MENOS, y REDONDEAR.MAS

Redondea un valor numrico al inmediato inferior o al inmediato superior segn la cantidad de decimales que se desee obtener.

Sintaxis

REDONDEAR.MENOS(valor;posicin decimal)

REDONDEAR.MAS(valor;posicin decimal)

ValorEs la cantidad numrica que se desea redondear.Posicin decimalEs la cantidad de decimales a los que se desea redondear la cantidad numrica. Si en posicin se escribe un nmero negativo se estar redondeando no los dgitos decimales sino los dgitos enteros.Si en la celda E4 estuviera escrito el numero 3267.283, las funciones arrojaran los siguientes resultados:

=REDONDEAR.MENOS(E4,1)3267.2

=REDONDEAR.MAS(E4,1)3267.3

=REDONDEAR.MENOS(E4,0)3267

=REDONDEAR.MAS(E4,0)3268

=REDONDEAR.MENOS(E4,-1)3260

=REDONDEAR.MAS(E4,-1)3270

=REDONDEAR.MENOS(E4,-3)3000

=REDONDEAR.MAS(E4,-3)4000

La Funcin ALEATORIO

Genera un nmero completamente al azar entre 1 y 0.

Sintaxis

ALEATORIO()

Esta funcin carece de argumentos.

El siguiente ejemplo simula el lanzamiento de un dado

=ENTERO(ALEATORIO()*6)+1

La funcin Aleatorio multiplicada por 6 generar un numero entre 0 y 5.9999. La funcin Entero truncar la parte decimal de stos nmeros quedando as enteros del 0 al 5, y al aumentarles 1 stos finalmente serian nmeros entre 1 y 6.

Escrita esta frmula, bastara con presionar la tecla de funcin [F9] para recalcular la frmula y entonces ALEATORIO generar otro nmero al azar.

Las Funciones ES

En esta seccin se describen 9 funciones para hojas de clculo que se utilizan para comprobar el tipo de un valor o referencia.

Cada una de estas funciones, a las que se conoce como funciones ES, comprueba el tipo del argumento valor y devuelve VERDADERO o FALSO dependiendo del resultado. Por ejemplo, ESBLANCO devuelve el valor lgico VERDADERO si valor es una referencia a una celda vaca, de lo contrario devuelve FALSO.

Sintaxis

ESBLANCO(valor)

ESERR(valor)

ESERROR(valor)

ESLOGICO(valor)

ESNOD(valor)

ESNOTEXTO(valor)

ESNUMERO(valor)

ESREF(valor)

ESTEXTO(valor)

ValorEs el valor que se desea probar. Puede ser el valor de una celda vaca, de error, lgico, de texto, numrico, de referencia o un nombre de rango que haga referencia a alguno de los anteriores.FuncinDevuelve VERDADERO si

ESBLANCOValor se refiere a una celda vaca.

ESERRValor se refiere a cualquier valor de error con excepcin de #N/A.

ESERRORValor se refiere a uno de los valores de error (#N/A, #VALOR!, #REF!, #DIV/0!, #NUM!, #NOMBRE? o #NULO!).

ESLOGICOValor se refiere a un valor lgico.

ESNODValor se refiere al valor de error #N/A (el valor no est disponible).

ESNOTEXTOValor se refiere a cualquier elemento que no sea texto (observe que esta funcin devuelve VERDADERO si el valor se refiere a una celda en blanco).

ESNUMEROValor se refiere a un nmero.

ESREFValor se refiere a una referencia.

ESTEXTOValor se refiere a texto.

Nota.-Los argumentos valor de las funciones ES no se convierten. Por ejemplo, en la mayora de las funciones en las que se requiere un nmero, el valor de texto "19" se convierte en el nmero 19. Sin embargo, en la frmula ESNUMERO("19"), "19" no se convierte y ESNUMERO devuelve FALSO.

Cuando usar las funciones ES

Las funciones ES son tiles en frmulas cuando se desea comprobar el resultado de un clculo. Al combinar esas funciones con la funcin SI, proporcionan un mtodo para localizar errores en frmulas (observe los siguientes ejemplos).Ejemplos

ESLOGICO(VERDADERO) es igual a VERDADERO

ESLOGICO("VERDADERO") es igual a FALSO

ESNUMERO(4) es igual a VERDADERO

Supongamos que en una hoja el rango A1:A5 muestra los siguientes valores de texto, numrico y de error: "Oro", "Regin1", #REF!, $ 330.92 y #N/A respectivamente.

A

1Sofa

2Trimestre1

3#!REF!

4$ 330.92

5#N/A

ESBLANCO(A1) es igual a FALSO

ESERROR(A3) es igual a VERDADERO

ESNOD(A3) es igual a FALSO

ESNOD(A5) es igual a VERDADERO

ESERR(A5) es igual a FALSO

ESNUMERO(A4) es igual a VERDADERO (si se introdujo $330.92 como nmero y no como texto)

ESREF(Regin1) es igual a VERDADERO (si Regin1 se define como un nombre de rango)

ESTEXTO(A2) es igual a VERDADERO (si Trimestre1 tiene formato de texto)

Supongamos que en otra hoja de clculo desea calcular el promedio del rango C1:C4, pero no est seguro de que las celdas contienen nmeros. La frmula PROMEDIO(C1:C4) devolver el valor de error #DIV/0! si C1:C4 no contiene nmeros. Puede utilizar la siguiente frmula para localizar posibles errores y permitir la realizacin del clculo:

=SI(ESERROR(PROMEDIO(C1:C4));"No hay Nmeros";PROMEDIO(C1:C4))

La Funcin HOY

Da como resultado la fecha del sistema.

Sintaxis

HOY()

Esta funcin carece de argumentos.

=HOY()dara como resultado: 07/06/2004La Funcin AHORA

Da como resultado la fecha y hora del sistema.

Sintaxis

AHORA()

Esta funcin carece de argumentos.

=AHORA()dara como resultado: 07/06/2004 03:21 Funciones de Bases de Datos BDfuncin

En esta seccin se describen las 12 funciones para hojas de clculo empleadas para los clculos de bases de datos (o listas) de Microsoft Excel. Cada una de estas funciones, denominadas colectivamente funciones BD, usa tres argumentos: base_de_datos, nombre_de_campo y criterios. Estos argumentos se refieren a los rangos de la hoja de clculo empleados en la funcin para base de datos.

Sintaxis

BDfuncin(Base de datos;Campo;Criterios)

Base_de_datoses el rango de celdas que compone la base de datos.Nota.-En Microsoft Excel, una base de datos es una lista de datos relacionados en la que las filas de informacin son registros y las columnas de datos, campos. La primera fila de la lista contiene los rtulos de cada columna. La referencia se puede introducir como rango de celdas o como nombre que representa el rango que contiene la lista.

En todas las funciones para bases de datos, si la referencia de base de datos corresponde a una celda dentro de una tabla dinmica, el clculo se realiza nicamente con los datos de esa tabla dinmica.

Si desea calcular subtotales en la lista, use el comando Subtotales en el men Datos para insertar los subtotales.

CampoIndica la columna en que se utiliza la funcin. Las columnas de datos en la lista deben tener un rtulo identificativo en la primera fila. Campo puede ser una cadena de texto con el rtulo de columna encerrado entre dobles comillas, como por ejemplo "Edad" o "Rendimiento" en el ejemplo siguiente, o como un nmero que representa la posicin de la columna en la lista: 1 para la primera columna (rbol en el ejemplo siguiente), 2 para la segunda (Alto) y as sucesivamente.CriteriosEs una referencia a un rango de celdas que especifican condiciones de una funcin. La funcin devuelve informacin de la lista que concuerda con las condiciones especificadas en el rango de criterios. Este rango incluye una copia del rtulo de la columna en la lista de la columna que desea que resuma la funcin. La referencia al argumento criterios puede introducirse como rango de celdas, como A9:F10 en la tabla de base de datos que se muestra a continuacin, o como nombre asignado a un rango, por ejemplo "Criterio."Sugerencias

Cualquier rango se puede usar como argumento criterios, siempre que incluya por lo menos un nombre de campo y por lo menos una celda debajo del nombre de campo para especificar un valor de comparacin de criterios.Por ejemplo, si el rango G1:G2 contiene el encabezado de campo Ingresos en la celda G1 y la cantidad 10.000 en la celda G2, el rango podra definirse como CoincidirIngresos y ese nombre podra usarse como argumento criterios en las funciones para bases de datos.Aunque el rango de criterios puede ubicarse en cualquier parte de la hoja de clculo, no coloque el rango de criterios debajo de la lista. Si agrega ms informacin a la lista utilizando el comando Formulario en el men Datos, la nueva informacin se agrega a la primera fila debajo de la lista. Si la fila de debajo no est vaca, Microsoft Excel no podr agregar la nueva informacin.

Asegrese de que el rango de criterios no se superpone sobre la lista.

Para realizar una operacin en toda una columna de la base de datos, introduzca una lnea en blanco debajo de los nombres de campo en el rango de criteriosEjercicio N 2: Clculos con funciones de base de datos BDEn la siguiente ilustracin se muestra una base de datos que contiene la descripcin y comportamiento de varios tipos de rboles. Cada registro contiene informacin acerca de un rbol. El rango A5:E11 se denomina HUERTO y el rango A1:F3 CRITERIOS.

BDCONTAR(HUERTO;"Edad";A1:F2) es igual a 1. Esta funcin examina los registros de manzanos cuyo alto vara entre 10 y 16 metros y determina cuntos campos Edad de esos registros contienen nmeros.

BDCONTARA(HUERTO;"Ganancia";A1:F2) es igual a 1. Esta funcin examina los registros de manzanos cuyo alto vara entre 10 y 16 metros, y determina el nmero de campos Ganancia de esos registros que no estn en blanco.

BDMAX(HUERTO;"Ganancia";A1:A3) es igual a 105.00 , la ganancia mxima de manzanos y perales.

BDMIN(HUERTO;"Ganancia";A1:B2) es igual a 75.00 , la ganancia mnima de manzanos con un alto superior a 10 metros.

BDSUMA(HUERTO;"Ganancia";A1:A2) es igual a 225.00 , la ganancia total de manzanos.

BDSUMA(HUERTO;"Ganancia";A1:F2) es igual a 75.00 ; la ganancia total de manzanos con un alto entre 10 y 16.

BDPRODUCTO(HUERTO;"Rendimiento";A1:F2) es igual a 10, el producto del rendimiento de los manzanos con un alto entre 10 y 16.

BDPROMEDIO(HUERTO;"Rendimiento";A1:B2) es igual a 12, el rendimiento promedio de manzanos con un alto de ms de 10 metros.

BDPROMEDIO(HUERTO;3;HUERTO) es igual a 13, la edad media de todos los rboles en la base de datos.

BDDESVEST(HUERTO;"Rendimiento";A1:A3) es igual a 2.97; la desviacin estndar estimada en el rendimiento de manzanos y perales si los datos de la base de datos son nicamente una muestra de la poblacin total del huerto.

BDDESVESTP(HUERTO;"Rendimiento";A1:A3) es igual a 2.65; la desviacin estndar verdadera en el rendimiento de manzanos y perales si los datos de la base de datos representan el conjunto de la poblacin.

BDVAR(HUERTO;"Rendimiento";A1:A3) es igual a 8.8; la varianza estimada en el rendimiento de manzanos y perales si los datos de la base de datos slo representan una muestra de la poblacin total del huerto.

BDVARP(HUERTO;"Rendimiento";A1:A3) es igual a 7.04; la varianza real en el rendimiento de manzanos y perales si los datos de la base de datos representan el conjunto de la poblacin del huerto.

BDEXTRAER(HUERTO;"Rendimiento;CRITERIOS) devuelve el valor de error #NUM! porque ms de un registro cumple con los criterios.

Funciones de Texto

La Funcin IZQUIERDAExtrae los primeros caracteres del extremo izquierdo de una cadena de caracteres

Sintaxis

IZQUIERDA(texto;num_de_caracteres)

TextoEs la cadena de texto que contiene los caracteres que se desea extraernum_de_caracteresEspecifica el numero de caracteres que se desea extraer. Si se omite extrae solo un caracterEjemplo:

=IZQUIERDA(El Peruano;7)dara como resultado: El Peru .(El espacio en blanco tambin es un carcter que se toma en cuenta)

La Funcin DERECHAExtrae los ltimos caracteres del extremo derecho de una cadena de caracteres

Sintaxis

DERECHA(texto;num_de_caracteres)

TextoEs la cadena de texto que contiene los caracteres que se desea extraernum_de_caracteresEspecifica el numero de caracteres que se desea extraer. Si se omite extrae solo un caracter=DERECHA(Los Peruanos;8)dara como resultado: Peruanos .La Funcin EXTRAEDevuelve un nmero especifico de caracteres de una cadena de texto comenzando en la posicin especificada

Sintaxis

EXTRAE(texto;posicin_inicial;num_de_caracteres)

TextoEs la cadena de texto que contiene los caracteres que se desea extraerposicin_inicialEs la posicin del primer carcter que se desea extraer del argumento Texto. l primer carcter en Texto es 1num_de_caracteresEspecifica el numero de caracteres que se desea extraer.Ejemplo:

=EXTRAE(Los Peruanos;5,4)dara como resultado: Peru .La Funcin HALLARBusca una cadena de texto dentro de otra cadena de texto y leyendo de izquierda a derecha devuelve el numero de la posicin inicial de la cadena hallada (no diferencia entre maysculas ni minsculas)

Sintaxis

HALLAR(texto_buscado;dentro_del_textol;posicin_inicial)

Texto_buscadoEs la cadena de texto o caracter que se desea ubicar. Puede usar los comodines * o ?. O usar tambinDentro_del_textoEs la cadena de texto dentro de la cual se desea ubicar el texto buscadoposicin_inicialEs la posicin del primer carcter a partir de la cual se iniciar la bsquedaEjemplo:

=HALLAR(a;Las Peruanas;1)

dara como resultado: 2.=HALLAR(a;Las Peruanas;5)

dara como resultado: 9.

Ejemplo:

Si en la celda C4 estuviese el texto Las Peruanas . entonces:

=EXTRAE(C4;HALLAR( ;C4;1)+1;4)dara como resultado: Peru .En este ejemplo, HALLAR se encarga de buscar la posicin donde se encuentra el espacio en blanco, le suma 1 a esta posicin y a partir de all la funcin EXTRAE devuelve los siguientes 4 caracteres

La Funcin ENCONTRARBusca una cadena de texto dentro de otra cadena de texto y leyendo de izquierda a derecha devuelve el numero de la posicin inicial de la cadena hallada (A diferencia de la funcin HALLAR, esta funcin si reconoce entre maysculas y minsculas)

Sintaxis

ENCONTRAR(texto_buscado;dentro_del_texto;posicin_inicial)

Texto_buscadoEs la cadena de texto o caracter que se desea ubicar. Puede usar los comodines * o ?. O usar tambinDentro_del_textoEs la cadena de texto dentro de la cual se desea ubicar el texto buscadoposicin_inicialEs la posicin del primer carcter a partir de la cual se iniciar la bsquedaEjemplo:

Si en la celda A3 estuviese el texto quien MAL anda mal acaba .entonces:

=ENCONTRAR(mal,A3;1)dara como resultado: 16.en cambio,

=HALLAR(mal,A3;1)

dara como resultado: 7.(Recuerde que HALLAR no diferencia maysculas ni minsculas)

La Funcin LARGODevuelve el nmero de caracteres de una cadena de texto

Sintaxis

LARGO(texto)

TextoEs la cadena de texto, o celda que contiene un texto

Ejemplo:

=LARGO(Zara Toledo)

dara como resultado: 12.Las Funcines MAYUSC, MINUSC y NOMPROPIOConvierte una cadena de texto en maysculas, en minsculas o solo las primera letra de cada palabra en mayscula

Sintaxis

MAYUSC(texto)

MINUSC(texto)

NOMPROPIO(texto)

TextoEs la cadena de texto, o celda que contiene un texto

Ejemplo:

=MAYUSC(monto total)

dara como resultado: MONTO TOTAL .=MINUSC(DOS MIL)

dara como resultado: dos mil .=NOMPROPIO(bill gates)dara como resultado: Bill Gates .La Funcin SUSTITUIRReemplaza el texto existente con texto nuevo en una cadena

Sintaxis

SUSTITUIR(texto;texto_original;texto_nuevo;nmero_de_ocurrencia)

TextoEs la cadena de texto, o celda que contiene un textoTexto_originalEs la cadena de texto, que se desea reemplazarTexto_nuevoEs la cadena de texto que reemplazar al texto_originalNmero_de_ocurrenciaEs el numero de aparicin del texto_original dentro de toda la cadena de texto. Si se omite se reemplazar el texto_original en todos los sitios donde aparezca

Ejemplo:

Si en la celda A3 estuviese el texto quien mal anda mal acaba .entonces:

=SUSTITUIR(A3,mal;bien)

dara como resultado: quien bien anda bien acaba .en cambio,

=SUSTITUIR(A3,mal;peor;2)

dara como resultado: quien mal anda peor acaba .

(En este caso solo sustituye la segunda palabra encontrada)

La Funcin DECIMALRedondea un nmero al nmero de decimales especificado, da formato al nmero con el formato decimal usando comas y puntos, y devuelve el resultado como texto.

Sintaxis

DECIMAL(nmero;decimales;no_separar_millares)

NmeroEs el nmero que desea redondear y convertir en texto.

DecimalesEs el nmero de dgitos a la derecha del separador decimal.

No_separar_millaresEs un valor lgico que, si es VERDADERO, impide que DECIMAL incluya un separador de millares en el texto devuelto.

Observaciones Los nmeros en Microsoft Excel nunca pueden tener ms de 15 dgitos significativos, pero el argumento decimales puede tener hasta 127 dgitos.

Si decimales es negativo, el argumento nmero se redondea hacia la izquierda del separador decimal.

Si omite el argumento decimales, se calcular con 2 decimales.

Si el argumento no_separar_millares es FALSO o se omite, el texto devuelto incluir el separador de millares.

La principal diferencia entre dar formato a una celda que contiene un nmero con el comando Celdas del men Formato y dar formato a un nmero directamente con la funcin DECIMAL es que DECIMAL convierte el resultado en texto. Un nmero que recibe formato con el comando Celdas sigue siendo un nmero.

EjemplosA

11234.518

2-1234.567

37244.325

Frmula

Resultado

Descripcin

=DECIMAL(A1;2)

1,234.52Redondea A1 2 dgitos a la derecha del punto decimal

=DECIMAL(A1;-1)

1,230

Redondea A1 1 dgito a la izquierda del punto decimal

=DECIMAL(A2;-1;VERDADERO)

-1230

Redondea A2 1 dgito a la izquierda del separador decimal, y sin coma de millar

=DECIMAL(A3)

7,244.33Redondea A3 2 dgitos a la derecha del separador decimal

Ejercicio N 3: Generacin de cdigos con funciones de texto

En el siguiente cuadro se muestra la relacin de socios que se han inscrito en un club social, y se desea completar las columnas de cdigos y nombres completos de cada socio.

Para generar los cdigos:Cada cdigo se compone de 9 caracteres que son extrados de los datos de cada socio, teniendo en cuenta ciertas condiciones.Por ejemplo para el primer socio inscrito su cdigoo sera el siguiente:

85JZH05UM

Las formulas para lograr este cdigo serian entonces:

Dos ltimos dgitos del ao de nacimiento:=DERECHA(AO(H4),2)

Primeras letras de sus apellidos y de su nombre:

= IZQUIERDA(C4,1)&IZQUIERDA(D4,1)&IZQUIERDA(E4,1)

Dos dgitos del mes de su inscripcin:

= DERECHA("0"&MES(B4),2)

Segunda letra del lugar de nacimiento:

= MAYUSC(EXTRAE(I4,2,1))

Sexo:

= G4Finalmente entonces, la formula que habra que escribir en la celda A4 para generar el cdigo sera:=DERECHA(AO(H4),2)&IZQUIERDA(C4,1)&IZQUIERDA(D4,1)&IZQUIERDA(E4,1)&DERECHA("0"&MES(B4),2)&MAYUSC(EXTRAE(I4,2,1))&G4

Como se puede observar, esta formula une todas las formulas anteriores utilizando entre ellas el operador de concatenacin &.Para escribir los nombres completos de cada socio en una sola celda:

En la columna F se tendr que escribir una formula que una el nombres del socio y luego de una coma los dos apellidos, pero en maysculas.

Por ejemplo para el primer socio deber decir:

Hugo, JARAMILLO ZAVALAEn la celda F4 hay que escribir la siguiente frmula:

=E4&", "&MAYUSC(C4&" "&D4)

Finalmente, tanto la formula de los cdigos como la de los nombres completos, deber copiarse hacia abajo para todos los dems socios.

Ejercicio N 4: Convertidor de Nmeros en Letras

Hay ocasiones en que se desea que en ciertos documentos una cantidad numrica sea expresada en letras. Tal es el caso de los montos de una factura, o de una boleta de ventas, o el monto de un cheque.

Para estos casos el siguiente ejercicio nos podra ser de utilidad:

1.-En un Libro nuevo escribir los siguientes datos tal como se muestra en la imagen siguiente:

2.-Definir los siguientes nombres de rango:

B3: N

B6: LETRAS

C9:H18: TABLA3.-Luego escribir las siguientes frmulas en las celdas:A9:=ENTERO(VALOR(DERECHA(DECIMAL(ENTERO(N/1000),0,VERDADERO),6))/100000)A10:=ENTERO(VALOR(DERECHA(DECIMAL(ENTERO(N/1000),0,VERDADERO),5))/10000)A11:=ENTERO(VALOR(DERECHA(DECIMAL(ENTERO(N/1000),0,VERDADERO),4))/1000)

A12:=ENTERO(VALOR(DERECHA(DECIMAL(ENTERO(N/1000),0,VERDADERO),3))/100)

A13:=ENTERO(VALOR(DERECHA(DECIMAL(ENTERO(N/1000),0,VERDADERO),2))/10)

A14:=ENTERO(VALOR(DERECHA(DECIMAL(ENTERO(N/100),0,VERDADERO),2))/10)

A15:=ENTERO(VALOR(DERECHA(DECIMAL(ENTERO(N),0,VERDADERO),3))/100)

A16:=ENTERO(VALOR(DERECHA(DECIMAL(ENTERO(N),0,VERDADERO),2))/10)A17:=VALOR(DERECHA(DECIMAL(ENTERO(N),0,VERDADERO),1))A18:=VALOR(DERECHA(DECIMAL(N,2,VERDADERO),2))B9:=SI(Y(A9=1,A10=0,A11=0),"CIEN",BUSCARV(A9,TABLA,6))B10:=SI(A11=0,BUSCARV(A10,TABLA,4),SI(A10=1,"",BUSCARV(A10,TABLA,5)))

B11:=SI(A10=1,SI(A11=0,"",BUSCARV(A11,TABLA,3)),SI(A11=1,"UN",BUSCARV(A11,TABLA,2)))B12:=SI(Y(A12=1,A13=0,A14=0),"CIEN",BUSCARV(A12,TABLA,6))

B13:=SI(A14=0,BUSCARV(A13,TABLA,4),SI(A13=1,"",BUSCARV(A13,TABLA,5)))

B14:=SI(A13=1,SI(A14=0,"",BUSCARV(A14,TABLA,3)),SI(A14=1,"UN",BUSCARV(A14,TABLA,2)))B15:=SI(Y(A15=1,A16=0,A17=0),"CIEN",BUSCARV(A15,TABLA,6))B16:=SI(A17=0,BUSCARV(A16,TABLA,4),SI(A16=1,"",BUSCARV(A16,TABLA,5)))B17:=SI(A16=1,SI(A17=0,"",BUSCARV(A17,TABLA,3)),BUSCARV(A17,TABLA,2))B18:=SI(A18=1000000000,DECIMAL(ENTERO(N/1000000000),0,VERDADERO)&",","")& SI(N>=1000000,DERECHA(DECIMAL(ENTERO(N/1000000),0,VERDADERO),3)&"'","")&SI(N>=1000,DERECHA(DECIMAL(ENTERO(N/1000),0,VERDADERO),3),"")&","&SI(N>0,DERECHA( DECIMAL(N,2,VERDADERO),6),"")B6:=ESPACIOS(B9&" "&B10&B11&SI(SUMA(A9:A10)>0," MILLONES, ",SI(A11>1," MILLONES, " ,SI(A11=1," MILLON, ","")))&B12&" "&B13&B14&SI(SUMA(A12:A14)>0," MIL,",SI(SUMA (A9:A11)>0," ","")))&B15&" "&B16&B17&" Y "&SI(A18>0,B18,"00")&"/100 "&"NUEVOS SOLES"

La hoja de clculo se deber ver as entonces:

4.-Guarde este libro con el nombre LetrasAhora, cada vez que escriba un nuevo nmero entero o con decimales en la celda B2, inmediatamente en la celda B6 aparecer su equivalente en letras y redondeado a dos decimales.

En la celda B5 aparecer el mismo nmero original redondeado a 2 decimales y con la siguiente puntuacin: un punto para separar decimales, una coma para separar millares y una comilla simple para separar los millones.

En otros ejercicios ms adelante veremos como se pueden utilizar estos resultados para confeccionar Facturas, Boletas de Venta y Giro de Cheques.

La Funcin ESPACIOS

Elimina los espacios del texto, excepto el espacio normal que se deja entre palabras. Use ESPACIOS en texto procedente de otras aplicaciones que pueda contener una separacin irregular.

Sintaxis

ESPACIOS(Texto)

TextoEs el texto del que desea quitar espacios innecesarios.

=ESPACIOS( Arriba Per )

El resultado ser:Arriba Per3. Validacin de Ingreso de datos

a. Que es la validacin

Es la forma en que Excel impide que ingresen los datos que escribimos en las celdas (nmeros, textos, fechas y horas, etc.) verificando que estos de encuentren dentro de lmites especificados previamente. De este modo se impide que se cometan errores al momento de digitar la informacin.

Por ejemplo:

Impedir ingresar edades de personas que sean mayores a 100 aos.

Impedir que se ingresen votos en una mesa de sufragio mayores a 200 electores

Impedir que se escriban cdigos de artculos que no existen en la lista de precios

b. Cmo validar el ingreso de datos en un rango de celdas

1.Seleccione las celdas que desee restringir.

2.En el men Datos, haga clic en Validacin y elija la ficha Configuracin.

3.En el cuadro Permitir, haga clic en el tipo de datos.

Para especificar slo nmeros, haga clic en Nmero entero o Decimal.

Para especificar fechas u horas, haga clic en Fecha u Hora.

4.Haga clic en el operador que desee en el cuadro Datos y especifique el limite inferior o superior para los datos, o bien ambos lmites, dependiendo del operador que se seleccione. Pueden introducirse valores, referencias de celda o frmulas para los lmites.

Si desea permitir que la celda que se est restringiendo est en blanco o si desea definir lmites que utilicen una referencia de celda, o una frmula que dependa de celdas que inicialmente estn en blanco, compruebe que la casilla de verificacin Omitir blancos est activada.

Para hacer obligatorias las restricciones definidas en las celdas en blanco, tratndolas como si tuvieran ceros, desactive la casilla de verificacin Omitir blancos.

5.Para que aparezcan mensajes que pidan entradas y que expliquen o impidan las entradas incorrectas, especifique los tipos de mensajes que desee en las fichas Mensaje entrante y Mensaje de error.

Cmo hacerlo?

Para omitir la presentacin de mensajes, desactive las casillas de verificacin Mostrar mensaje al seleccionar la celda en la ficha Mensaje entrante y Mostrar mensaje de error si se introducen datos no vlidos en la ficha Mensaje de error.Ejemplo:

Se desea validar un cuadro para que en la columna Cantidad solo permita escribir nmeros enteros y positivos. Entonces, cuando se trate de escribir en una celda una cantidad como 7.3 que es un numero decimal, deber aparecer un mensaje de error como el que se ve a la derecha, impidiendo ingresar este nmero.

Para esto seleccionar el rango B2:B8, llamar al men Datos/Validacin, y elegir las opciones como se muestran en la siguiente ventana de dilogo:

Nota.-Cuando se especifique el tipo de datos que se permiten, esto no afectar al formato. Para dar formato a las celdas, como nmeros, fechas u horas, haga clic en el men Formato/Celdas y, a continuacin, haga clic en la ficha Nmero. Seleccione el formato que desee en el cuadro Categora y luego seleccione las opciones que desee.

Una frmula para un lmite puede evaluar los datos nicamente en la hoja de clculo en que se hayan configurado las restricciones. Para utilizar en una frmula los datos de otra hoja de clculo, o de otros libros, introduzca una referencia de celda para los datos externos en una celda de la hoja de clculo activa o defina un nombre para los datos externos en la hoja de clculo activa. La frmula puede hacer referencia a la celda o al nombre en la misma hoja de clculo. Por ejemplo, si los datos que desea utilizar en una frmula estn en la celda A6 de la primera hoja de clculo de un libro denominado Presupuesto.xls, puede definir el nombre DatosVlidos en la hoja de clculo activa para que sea =[Presupuesto.xls]Hoja1!$A$6 y, posteriormente, introducir una referencia =DatosVlidos cuando se especifiquen los lmites para los datos.

c. Realizar una auditoria en un rango validado

Puede buscar entradas de celdas que no se ajusten a sus criterios y rodearlas automticamente con un crculo haciendo clic en el botn Rodear con un crculo datos no vlidos de la barra de herramientas Auditoria. Para hacer visibles los botones de Auditoria usar el men: Herramientas/Auditoria/Mostrar barra de auditoria.

Ejemplo11.-En una hoja de clculo escriba una lista de varios nombres de nios y sus respectivas edades, tal como se muestra en la figura siguiente.

Observe que las edades de Laura y Miguel fueron escritas errneamente.

En este cuadro se desea verificar que las edades ya ingresadas de los nios, no sean mayores de 12 aos. Para esto, haga usted lo siguiente:

2.-Seleccionar el rango B2:B8 y con el men Datos/Validacin, elegir Permitir Nmeros enteros entre un mnimo de 0, y un mximo de 12.

3.-Luego de validar, en la barra de herramientas Auditoria haga un clic en el botn Rodear con un crculo datos no vlidos, entonces aparecern marcadas con crculos rojos las cantidades incorrectas.

4.- Corrija entonces las edades de ambos nios.

A medida que corrija estas cantidades, observe que irn desapareciendo los crculos rojos que encerraban a las edades equivocadas.Ejemplo2En otro caso; suponga que se tiene un registro de ventas con la relacin de las facturas emitidas en un mes y se desea verificar que los nmeros de factura no aparezcan repetidos, en cuyo caso habra que analizar el motivo del error y corregirlo, ya sea cambiando el numero de la factura por el correcto, o eliminando el registro de la factura por estar duplicada.

1.-En una hoja en blanco escriba los datos del Registro de Ventas que se muestra en la imagen siguiente:

2.-En este cuadro seleccionar todas las celdas con los nmeros de factura de la columna B, y con el men Datos/Validacin se valida segn como se muestra en la ventana de dialogo a continuacin:

La frmula =B4B5 verificar cuando dos celdas consecutivas se repitan.

3.-Luego de validar, haga clic en el botn de auditoria Rodear con un crculo los datos no vlidos.

A medida que se vayan corrigiendo los errores en los nmeros de las facturas, al igual que en el ejemplo anterior, los crculos rojos tambin se irn desapareciendo.

4. Lneas de Tendencia en los Grficosa. Utilidad de las lneas de tendencia

Las lneas de tendencia se usan para mostrar grficamente las tendencias de los datos y analizar los problemas de prediccin. Este anlisis tambin se denomina anlisis de regresin. Mediante el uso del anlisis de regresin, puede representarse una lnea de tendencia en un grfico ms all de los datos actuales para predecir los valores futuros. Por ejemplo, en el siguiente grfico se utiliza una lnea de tendencia simple que muestra la previsin para cuatro trimestres indicando una clara tendencia de aumento en los ingresos.

Media mvil.- Tambin se puede crear una media mvil, que suaviza las fluctuaciones en los datos y muestra la trama o tendencia con ms claridad.

b. Tipos de grfico que admiten lneas de tendenciasPueden agregarse lneas de tendencia a las series de datos en los siguientes grficos:

de reas 2D no apiladas,

de barras,

de columnas,

de lneas,

de cotizaciones,

de tipo XY (Dispersin), y

de burbujas.

No pueden agregarse lneas de tendencia a las series de datos en los grficos 3D, radiales, circulares, de superficie o de anillos.

Si se cambia un grfico o una serie de datos de modo que ya no permita la lnea de tendencia asociada (por ejemplo, si se cambia el tipo de grfico por un grfico de reas 3D o si se cambia la vista de un informe de grfico dinmico o de un informe de tabla dinmica asociado), se perdern las lneas de tendencia.

c. Agregar una lnea de tendencia a una serie de datos1.-En el grfico, haga clic en la serie de datos en la que desea agregar la lnea de tendencia o la media mvil.

2.-En el men Grfico, haga clic en la opcin Agregar lnea de tendencia.

3.-En la ficha Tipo, haga clic en el tipo de lnea de tendencia de regresin o en la media mvil que desee.

Si se selecciona Polinomial, introduzca en el cuadro Orden el valor potencial ms alto de la variable independiente.

Si se selecciona Media mvil, introduzca en el cuadro Perodo el nmero de perodos que va a utilizarse para calcular la media mvil.:Ejemplo

Para analizar las proyecciones en un grfico de lneas; una vez creado el grafico sealar con el puntero del mouse la lnea y llamar al men contextual con un clic derecho del mouse, luego elegir la opcin Agregar lnea de tendencia.

En la ventana de dilogo elegir en Tipo de tendencia o regresin la opcin Lineal y en la ficha Opciones marcar la casilla Presentar ecuacin en el grfico, luego [Aceptar]. Se ver entonces en el grafico el siguiente resultado:(Si se reemplaza x en la ecuacin por el numero del mes se obtienen los pronsticos de las ventas de esos meses.)

Si luego de tener la lnea de tendencia en el grafico, se ingresan los valores de venta de los meses faltantes, la lnea de tendencia as como la ecuacin se ajustaran en el grfico actualizndose a estos nuevos valores. Posteriormente se puede agregar formato a los elementos del grafico, as como a la lnea de tendencia y a la ecuacin en el grafico, tal como se ve en la figura.

Otras formas en las que se puede mostrar el mismo anlisis de ventas con la lnea de tendencia se muestran a continuacin en los grficos siguientes:

En un grfico de reas:

En un grfico de Barras:Tenga en cuenta lo siguiente:

Si se agrega una media mvil a un grfico XY (Dispersin), la media mvil se basar en el orden de los valores X trazados en el grfico. Para obtener el resultado deseado, puede ser necesario ordenar los valores X antes de agregar una media mvil.

Las lneas de tendencia no se mantienen en los informes de grfico dinmico si se modifica la vista del grfico o de su informe de tabla dinmica asociado. Antes de agregar lneas de tendencia u otros elementos de formato a un informe de grfico dinmico, asegrese de que el diseo es satisfactorio.

5. Uso del botn Cmara Fotogrfica

a. Cmo habilitar el botn Cmara Fotogrfica

Para habilitar este botn, ingrese al men Ver/Barra de herramientas/Personalizar y en la ficha Comandos, categora Herramientas ubique el botn Cmara y arrstrelo a las barras de herramientas de la ventana de Excel o ubquelo en la barra de men.

b. Cmo utilizar el botn Cmara Fotogrfica

Este botn de herramientas podr capturar cualquier rango de celdas en el cual existan datos, grficos o dibujos y convirtiendo dicho rango en una imagen podr pegarlo en cualquier lugar del documento.

Cmo hacerlo?

1.-Seleccionar el rango de celdas conteniendo los datos u objetos que desea tomar la foto.

2.-Hacer un clic en el botn Cmara.

3.-Ubicar el puntero en el lugar donde desee revelar la foto y haga nuevamente un clic.

Al aparecer la foto esta se comporta como cualquier imagen, y entonces podr cambiar su tamao y posicin a voluntad. O sea que una foto podr ubicarse sobre un cuadro de Excel, o sobre un grfico o formando parte de un dibujo, incluso podr agruparse con grficos o dibujos como cualquier imagen sin perder su calidad de foto.

Nota.-Si el contenido del rango al que se le ha tomado una foto es modificado, entonces la foto tambin cambiar su imagen, actualizndose automticamente a esos cambios.

Ejemplo

En este ejemplo se ha tomado una foto a las celdas que contienen el tipo de cambio del dlar y se la ha pegado encima del cuadro de existencias. La foto mantendr actualizado el valor del dlar cada vez que este sea cambiado.

6. La Programacin en Excel

La palabra MACRO est muy ligada a las hojas de clculo desde hace muchos aos atrs en la poca en que Lotus 123 reinaba en el mundo de las microcomputadoras de escritorio. Una MACRO, no es sino una secuencia de ordenes escritas en los archivos de las hojas de calculo para que se encarguen de realizar automticamente aquellas acciones que son repetitivas en el manejo de nuestros datos. Esto es, si en un trabajo en la hoja, es necesarios realizar una secuencia de tareas y mas tarde hay que repetir esta secuencia otra vez, y luego otra vez; convirtindose esto en una rutina; entonces es cuando se hace necesario recurrir a una MACRO. O sea, un programa que automatice las acciones para no tener que hacerlas cada vez nosotros en cada ocasin.

En Lotus123 y en Quattro Pro que fueron dos hojas de calculo muy populares en DOS, as como en las versiones iniciales de Excel, la programacin de las macro instrucciones se escriban usando una serie de cdigos o palabras reservadas que eran propias nicamente de stas aplicaciones. Pero, a partir de la versin 5.0 de Excel, Microsoft recurre al lenguaje de Visual Basic para escribir sus programas en hojas de mdulo que se anexan en forma transparente a los libros de Excel, recurriendo para ello al Editor de Visual Basic para Aplicaciones

a. Uso de la grabadora de macros

A pesar que la programacin se realiza internamente utilizando el lenguaje de Visual Basic, no es necesario que los usuarios de Excel sepan necesariamente el uso de este lenguaje. Y esto es as, gracias a que existe la posibilidad de que se puedan grabar las acciones que necesitamos utilizando para ello una grabadora de macros, la cual almacena en memoria todas las acciones a grabar, e internamente convierte a estas en un programa en Visual Basic.

Para crear una macro haciendo uso de la grabadora de macros se siguen los siguientes pasos:

1.-Llamar al men Herramientas/ Macro/Grabar nueva macro

2.-Escribir un nombre para la macro a grabar. Este nombre no deber contener espacios en blanco.

3.-Al aparecer el mensaje Grabando en la barra de estado, realizar cuidadosamente y en forma secuencial, todas las acciones que se desean grabar.

4.-Para finalizar la grabacin hacer un clic en el botn: SHAPE \* MERGEFORMAT

, o llamar al men Herramientas/Macro/Finalizar grabacin, con lo cual deber desaparecer el mensaje Grabando en la barra de estado.

Nota.-Las macros al ser creadas pueden grabarse en el libro actual, como se puede apreciar en la ventana de dialogo anterior en la opcin: Este libro , con lo cual la macro solo funcionar en ese libro; o tambin se pudo elegir en la ventana de dilogo la opcin: Libro de macros personal, para cuyo caso la macro podra funcionar tambin en todos los libros de Excel; sin embargo, hay que tener cuidado con esto, pues si se graba en el libro de macros personal esto har que dicha macro y todas las que se encuentren all sean cargadas en memoria RAM cada vez que se ingrese a Excel, restando con ello memoria al computador.

Una vez creada, veamos a continuacin como se hace para que la macro funcione:

b. Ejecucin de una macro

Una macro puede ejecutarse con el men Herramientas/Macro/Macros, y en la ventana de dialogo, seleccionar su nombre y luego elegir el botn Ejecutar.

Sin embargo, un modo mas controlado y fcil de ejecutar una macro es asignndola a un elemento de imagen, como puede ser a un dibujo de Autoformas, a un botn de herramientas personalizado o incluso hasta a un grfico.

Para asignar una macro a un botn de Autoformas realizar lo siguiente:

1.-Sealar con el mouse el borde del botn y hacer un clic derecho para llamar al men contextual

2.-En el men elegir la opcin Asignar macro.

3.-Seleccionar el nombre de la macro de la lista y luego clic en Aceptar.

Ejemplo

Se va ha crear una macro que alinee verticalmente al centro, el contenido de una celda:

1.-Para esto primero escribir en una celda un dato.

1.-Llame al men Herramientas/Macro/Grabar nueva macro y escriba como nombre de la macro: CentradoVertical (sin dejar espacio en blanco entre las 2 palabras), y luego Aceptar.

2.-Al aparecer el mensaje Grabando en la barra de estado, realizar la accin de centrado, esto es: llamar al men Formato/Celdas y en la ficha Alineacin elegir en la opcin Alineacin del texto Vertical, la alternativa Centrar, luego elegir el botn Aceptar.

3.-Finalmente terminar la macro con el botn Detener grabacin o con el men Herramientas/ Macro/Detener grabacin.

4.-A continuacin dibuje en la hoja un botn con Autoformas y haciendo un clic derecho en l, asgnele la macro CentradoVertical.5.-Ahora, al escribir en una celda un dato podr centrar el dato verticalmente haciendo un clic en el botn de la macro.

c. Uso del Editor de Visual Basic

Para la macro del ejemplo anterior, el programa o subrutina en el lenguaje de Visual Basic que se habra generado automticamente, sera el siguiente:

Sub CentradoVertical()

'

' Macro creada por Daniel Zegarra - UNI

'

With Selection

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlCenter

.WrapText = False .Orientation = 0

.AddIndent = False .ShrinkToFit = False .MergeCells = False End WithEnd Sub

Este programa se encontrara escrito en una hoja de Modulo insertada dentro del libro actual de Excel, y para ver esta hoja de mdulo habra que ingresar a la ventana del Editor de Visual Basic.

d. Para ingresar al Editor de Visual Basic

Se puede hacer de dos formas:

i. Presionando la combinacin de teclas Alt+F11, o

ii. Llamando al men Herramientas/Macro/Editor de Visual Basic.

Si al ingresar al Editor de Visual Basic la ventana estuviese totalmente vaca, llamar al men Ver y elegir la opcin Explorador de Proyectos, o presionar Ctrl+R. Se ver entonces la siguiente ventana en la pantalla:

En la ventana de Proyecto de la izquierda, haga doble clic en el elemento Modulo1 para abrir la ventana conteniendo el cdigo en Visual Basic de la macro.

e. Modificar una macro en el Editor de Visual Basic

El Editor se comporta en forma semejante a un procesador de texto, en el se pueden escribir y modificar las instrucciones creadas por la grabadora de macros, as como escribir a mano nuevas subrutinas en la misma hoja de Modulo1 o insertando nuevas hojas al proyecto y escribiendo las macros en ellas.

Por ejemplo, la macro anterior se la puede modificar borrando algunas lneas que estn sobrando, con lo cual la macro sera ms eficiente:

Cuando se grab la macro y se ingres a la ventana de dilogo Formato de celdas, solo se eligi en ella la opcin Centrar Verticalmente, pero el resto de las opciones que se encontraban en la ventana de dilogo, aunque no se llegaron a elegir ni modificar en el momento de la grabacin, si fueron escritas tambin automticamente por la grabadora de macros dentro del programa. Estas son las lneas de la macro que se podran eliminar, ya que no representan ninguna accin efectiva dentro del programa. (observe las lneas de instrucciones sombreadas en gris)

With Selection

.HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter

.WrapText = False .Orientation = 0

.AddIndent = False .ShrinkToFit = False .MergeCells = False End With

Si se eliminan estas lneas sombreadas, as como tambin las instrucciones With y End With, entonces la macro quedara al final as:

Sub CentradoVertical()

' Macro creada por Daniel Zegarra Z.

Selection.VerticalAlignment = xlCenter

End Sub

Adicionalmente tambin podran eliminarse las lneas de comentarios, que son aquellas lneas que en la pantalla aparecen escritas con letras de color verde y que se encuentran precedidas por un apstrofe o comilla simple.

'

' Macro creada por Daniel Zegarra Z.

'

Estas lneas de comentaros no ejecutan dentro del programa ninguna accin, su finalidad es solamente descriptiva y de utilidad nicamente para el usuario.

7. Creacin de Macros con la grabadora de macros

a. Que es la grabadora de macros

Es un programa de Office que se encarga de almacenar todos los pasos y acciones que se realizan en la pantalla, ya sea usando para ello el teclado o el mouse. Todo lo que se realice ser grabado como un grupo de acciones que luego podrn repetirse cuantas veces quiera y en el momento que se desee.

El uso de la grabadora de macros ya se ha explicado en el capitulo 6, as que en este captulo vamos a ver la solucin de diferentes ejercicios en la hoja de calculo donde se utilizar para ello la grabadora de macros.

b. Cmo modificar las instrucciones de las macros

Luego de creadas las macros con la grabadora de macros, estas pueden ser modificadas para optimizar su funcionamiento o para agregar nuevas instrucciones al programa. Pero esto ya no se puede hacer con la misma grabadora de macros, sino que hay que recurrir al Editor de Visual Basic

Una manera rpida para modificar una macro es llamando al men Herramientas/Macro/Macros y seleccionando el nombre de la macro deseada se hace un clic en el botn de Modificar de la ventana de dialogo.

Una vez dentro del Editor de Visual Basic, se podrn modificar en la hoja de mdulo las instrucciones de la macro. Aunque para ello sea necesario tener algunos conocimientos de programacin, en este capitulo se le indicar a usted como lograr modificar una macro sencilla, y ya luego en los siguientes captulos se ver con mas detalle como se editan o escriben programas en el editor de Visual Basic.

8. Creacin de Plantillas en Excel

a. Que es una Plantilla

Es un archivo de Excel que contiene un libro que servir de modelo para crear otros libros semejantes a l, pero con la ventaja de que el archivo Plantilla no correr el riesgo de daarse durante la creacin de los dems libros.

b. Como se crea una Plantilla

Una vez que se ha diseado el libro que servir de plantilla, se llama al men Archivo/Guardar como y se lo graba como del tipo Plantilla eligiendo esta alternativa en la ultima casilla "Tipo de archivo:" de la parte inferior de la ventana de dilogo Guardar como.

Por defecto las plantillas de guardarn en una carpeta especial llamada a su vez Plantillas, pero si se desea las plantillas podrn guardarse en cualquier otra carpeta del disco duro o en un disquette si se desea.

Ejercicio N 5: Acciones repetitivas con macrosHay ocasiones en que se necesita en la hoja de calculo repetir un grupo de acciones muchas veces para diferentes rangos de celda. En estos casos es cuando las macros nos sern de gran ayuda.

Suponga que se dispone de una lista diaria de clientes que asisten durante un mes a realizar sus compras a una zapatera, y se desea ordenar esta lista en grupos semanales, y cada semana ordenada descendentemente segn la cantidad de clientes asistentes. Una vez ordenada cada semana en orden descendente poner en negrita y de color rojo la cantidad maxima de personas que asistieron en cada semana.Veamos como se soluciona este caso con ayuda de las macros:

1ra Parte: Crear la tabla de clientes que asisten a una zapatera.

1.- En una hoja en blanco escriba lo siguiente:

2.-Seleccionando la celda A5, llame al men Formato/Celdas y en la ficha Nmero, categora personalizada, disee el siguiente formato en la casilla Tipo:

ddd dd mmm

La fecha aparecer como: Mi 01 Jun

3.-Luego con el botn derecho del mouse arrastre el cuadro de relleno de la celda A5 hasta llegar a la celda A26, y cuando al soltar el botn del mouse aparezca un men contextual, elegir la opcin Rellenar das de la semana, para copiar la serie de das laborales del mes de Junio. (Solo aparecern 5 das laborables en cada semana)

Luego en la columna B vamos a escribir la cantidad de personas que asistieron cada da a la zapatera. Para simular la cantidad de personas vamos a hacer que Excel escriba nmeros al azar entre 10 y 150:

4.-Escriba la siguiente formula en la celda B5:

=10+ENTERO(ALEATORIO()*140)

5.-Ahora copie esta formula haciendo doble clic en el cuadro de relleno.

Luego convierta el resultado de estas formulas en valores:

6.-Seleccione el rango B5:B26 y elija el men Edicin/Copiar, luego en el men Edicin/Pegado especial, marque la opcin Valores y Aceptar. Finalmente [Esc].

2da Parte:Dividir las personas en grupos semanales ordenados descendentemente.

Como se explic al principio, se desea una macro que haga lo siguiente:

7.-Primero seleccione la celda A8.

8.-Llame al men Herramientas/Macro/Grabar nueva macro9.-Como nombre para la macro escriba la palabra Grupos y como letra de mtodo abreviado escriba la letra "k". Luego Aceptar

10.-Al aparecer el mensaje "Grabando" en la barra de estado, verifique que se encuentre activado el botn de herramientas Referencia relativa:

11.-Luego realice los siguientes pasos:

Llamar al menu Insertar/Fila Seleccionar la celda A7 y presionar las teclas Ctrl+* para seleccionar la regin actual (A5:B7)

Llamar al men Datos/Ordenar y elegir ordenar por Columna B, en orden Descendente y verifique que No se considere tener fila de encabezamiento. Luego haga clic en el botn Aceptar.

Seleccionar la celda B5 y ponerla en Negrita, y de color de fuente Roja.

Seleccione la celda A14 y haga un clic en el botn Detener grabacin.

13.-Guarde el libro con el nombre de archivo Asistencia.

Con esto ya tendremos ordenado el primer grupo delos das correspondientes a la primera semana de Julio. Y como el puntero de celda se encuentra en A14, solo bastar con presionar las teclas Ctrl+k para ejecutar la macro y que esta se encargue de ordenar el grupo de la segunda semana, y as sucesivamente hasta terminar con el resto del mes.

Ejercicio N 6: Un control de Caja ChicaVamos a crear una Plantilla en Excel para llevar el control diario del dinero de una caja chica. A traves de la plantilla se generarn diariamente archivos que contendrn los movimiento de dinero de un dia. Tanto la Plantilla que ser un archivo llamado "Control de Caja", como los archivos diarios de movimiento de caja, sern almacenados en una misma carpeta llamada "Caja".1.- En una hoja en blanco disee el cuadro que se muestra a continuacin, incluyendo los dos botones dibujados con Autoformas que aparecen en la parte superior derecha (Nueva Linea y Cerrar Caja):

2.-Dle nombre a los siguientes rangos de celda:

E4 ( InicialE10 ( FinalA8 ( Linea

3.-Reduzca la altura de la fila 8 tal como se ve en la figura anterior

4.-En la celda E7 escriba la siguiente frmula:

=Inicial+SUMA(C$7:C7)-SUMA(D$7:D7)

5.-En la celda E10 escriba la siguiente frmula:

=Inicial+SUMA(C$7:C8)-SUMA(D$7:D8)6.- De formato estilo moneda a las celdas E4 y E10

7.-De formato estilo millares a las 3 celdas del rango C7:E7

8.-Guarde ahora el libro con el nombre Control de caja dentro de una carpeta llamada Caja.

Ahora vamos a suponer que la caja tiene un saldo inicial de 1000 soles y que se ha hecho un primer retiro de 350 de la caja por una orden de compra para artculos de limpieza.

9.-Escriba 1000 en la celda E4, y en C7 escriba 350. Adems escriba el nombre del responsable de la caja y la fecha.

A continuacin vamos a crear una macro que se encargue de insertar una nueva fila en blanco con la formula respectiva en la ultima columna a fin de llevar la cuenta del saldo de caja. Entonces debe hacer usted lo siguiente.

10.-Llame al men Herramientas/Macro/Grabar nueva macro11.-Como nombre para la macro escriba la palabra Nueva y luego Aceptar

12.-Al aparecer el mensaje "Grabando" en la barra de estado, realice los siguientes pasos:

Seleccione en nombre de rango Linea

Llame al menu Insertar/Fila Seleccione la celda E7 y luego copie con el cuadro de relleno el contenido de esa celda hacia la celda de abajo

Seleccione la celda A8 y haga un clic en el botn Detener grabacin.

13.-Luego asigne al botn Nueva Linea, la macro Nueva que acaba de crear.

De este modo, cada vez que haga un clic en el botn de esta macro, se insertar una nueva lnea para registrar un nuevo movimiento de dinero en la caja.

14.-Guarde el libro presionando Ctrl+G.

Para poder ver las instrucciones de la macro que se ha creado hay que ingresar al Editor de Visual Basic presionando las teclas Alt+F11 y en la hoja de Modulo1 podr verse el siguiente procedimiento Sub:Sub Nueva()

Application.Goto Reference:="Inicial" Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Application.Goto Reference:="Linea" Selection.EntireRow.Insert

ActiveCell.Offset(-1, 4).Range("A1").Select

Selection.AutoFill Destination:=ActiveCell.Range("A1:A2"), Type:= _

xlFillDefault

ActiveCell.Range("A1:A2").Select

ActiveCell.Offset(1, -4).Range("A1").Select

End Sub

La instruccin: Application.Goto Reference:="Inicial"

es equivalente a: Range("Inicial").SelectAhora vamos ha crear otra macro para que guarde automticamente el libro de control de caja, dndole como nombre al archivo, la fecha escrita en la celda B4.15.-Llame al men Herramientas/Macro/Grabar nueva macro16.-Como nombre para la macro, escriba la palabra Guardar y luego Aceptar

17.-Al aparecer el mensaje "Grabando" en la barra de estado, realice los sigyuientes pasos:

Llamar al men Archivo/Guardar como Escribir como nombre: 1 de Julio Verifique que se est guardando el libro en la carpeta llamada Caja y luego haga clic en Guardar.

Haga un clic en el botn Detener grabacin.

18.-Luego asigne al botn Cerrar Caja, la macro Guardar que acaba de crear.

Con esto ya tenemos las dos macros que necesitamos en este libro, pero como supondrn, cuando se ejecute la segunda macro, siempre va ha guardar el libro actual con el nombre "1 de Julio", a menos que hagamos un cambio en la macro para que el nombre que le de a los libros de caja sean los contenidos escritos en la celda B4 en cada ocasin.

Para que esta macro haga lo que deseamos, hay que modificar internamente sus instrucciones dentro del Editor de Visual Basic; y esto se logra haciendo lo siguiente:

19.-Llamar al men Herramientas/Macro/Macros20.-Seleccionar en la ventana de dialogo el nombre de la macro Nueva y luego hacer un clic en el botn Modificar que se encuentra a la derecha de la ventana.

Esto abrir el Editor de Visual Basic y mostrar en una hoja de mdulo a la macro Guardar semejante a la que se muestra a continuacin:

Sub Guardar()

'

' Macro grabada el 4/06/2004 por Daniel Zegarra

'

ActiveWorkbook.SaveAs Filename:= _

"C:\Mis documentos\Caja\1 de Julio.xls", _

FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Entonces hay que buscar la instruccin donde se le est poniendo nombre al archivo y reemplazar el nombre: 1 de Julio, por la instruccin: " & Range("B4") & " incluyendo las comillas y los operadores de concatenacin &, de la siguiente manera:

Sub Guardar()

'

' Macro grabada el 4/06/2004 por Daniel Zegarra

'

ActiveWorkbook.SaveAs Filename:= _

"C:\Mis documentos\Caja\" & range("B4") & ".xls", _

FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Nota.-La ruta de la carpeta Caja quizs no sea la misma que observa en la instruccin anterior, pues esto depende donde haya usted creado la carpeta Caja En todo caso, usted solo tiene que modificar en la instruccin, la parte del nombre del archivo y no el nombre de la ruta donde se ha creado la carpeta Caja. .Seguidamente vamos a convertir el libro actual (cuyo nombre ahora es 1 de Julio) en una plantilla de Excel con el fin que nos sirva de modelo para poder crear a partir de l, diferentes archivos de control de caja para cada da que se desee.

Haga entonces lo siguiente:

21.-Primero, borre el nombre del responsable de la caja de la celda B3 y tambin la fecha de la celda B4.

22.-Seleccione la fila 8 que habamos insertado con la macro Nueva Linea, y elimnela con el men Edicin/Eliminar (Si habia insertado mas de una lnea eliminelas tambin) teniendo ciudado de no eliminar la fila que tiene la altura reducida y que le pusimos el nombre de rango Linea.

23.- Borre tambin el saldo inicial de 1000 soles en E4 y el retiro de 350 en C7.

24.-Llame ahora al men Archivo/Guardar como, y escriba como nombre para el archivo: Control de Caja. Luego debajo del nombre del archivo elija como tipo de archivo Plantilla, y cercirese que se est guardando en la carpeta Caja. Finalmente haga clic en el botn Guardar.

De este modo se habr creado un archivo de Plantilla de Excel.

25.- Cierre ahora la ventana de Excel.

Si ahora abre la carpeta Caja ver que existen alli varios archivos, pero el que nos interesa es el archivo de Plantilla de Excel llamado Control de caja y que tiene un icono de forma de una libreta con varias hojas desglosables.

26.-Seale el icono de la plantilla y haga doble clic para abrirlo, y luego elija con un clic la opcin Habilitar macro.

Al abrirse, como ocurre con las plantilla de excel, lo que se observa en la pantalla es una copia de la plantilla que tendr el nombre Control de caja 1, como puede verse asi en la barra de ttulo.

27.-A continuacin llene los datos del responsable, la fecha 2 de Julio por ejemplo, un saldo inicial y luego varios movimiento de caja, tanto de ingresos como de retiros.

28.-Finalmente haga clic en el boton Cerrar Caja para guardar su contenido.

Ejercicio - Resultados de una EncuestaSe desea averiguar cuales son las preferencias de las personas al elegir la marca de televisores que van a comprar.

Para ello se ha diseado una encuesta con una muestra de varias personas que van a ser encuestadas en diferentes distritos de la capital de Lima, y de la provincia constitucional del Callao.

1ra Parte: Diseo de los cuadros de ingreso de datos y clculo de resultados1.- En la Hoja1 de un nuevo libro disee el cuadro que se muestra a continuacin:

2.-En la Hoja2 del mismo libro anterior disee este otro cuadro de Resultados de la Encuesta, y luego dle formato a las celdas tal y como se muestra en la figura siguiente.

3.- Escriba estas formulas en la siguientes celdas:

En I5:=SUMA(B5:H5)

Luego copie esta formula hacia abajo hasta la fila 33.

En B34:=SUMA(B5:B33)

copie esta formula hacia la derecha hasta la columna H.

En B35:=SI(I34=0,0,B34/$I34)

copie tambin esta formula hacia la derecha hasta la columna H.

En B35:=SUMA(I5:I33)

En B35:=SUMA(B35:H35)4.-Definir los siguientes nombres de rango en ambas hojas:

En la Hoja1:B3 ( LugarB4:b10 ( Datos

En la Hoja2:A5:A33 ( DistritosA4:I35 ( Cuadro

5.-En la Hoja1 escriba tambin el siguiente cuadro de porcentajes con estas frmulas en sus celdas:

En B13:=Hoja2!B$35

En B14:=Hoja2!C$35

En B15:=Hoja2!D$35

En B16:=Hoja2!E$35

En B17:=Hoja2!F$35

En B18:=Hoja2!G$35

En B19:=Hoja2!H$35

En B20:=SUMA(B13:B19)

Como puede verse a travs de las anteriores formulas, este cuadro muestra los totales acumulados de todos los distritos del cuadro de resultados de la Hoja2, que como en el aun no hay datos, entonces los porcentajes en la Hoja1 son de 0%. Sin embargo cuando se empiecen a ingresar los datos de las encuestas, entonces aqu aparecern los respectivos porcentajes totales.6.-En la Hoja2 escriba los siguientes datos para el distrito de Ancn:

Regrese a la Hoja1 y en el cuadro de porcentajes aparecern automticamente los resultados para cada una de las marcas de Televisor.

7.-En la Hoja1 seleccione el rango A12:B19 y cree un grfico de columnas como el que se muestra a continuacin. O si usted lo desea disee cualquiera de los tipos de grafico que ya conocemos pero que muestre los porcentajes de cada uno de las marcas de televisor.

8.-Finalmente en la Hoja1 seleccione la celda B3, llame al men Datos/Validacin y en la casilla Permitir elija la opcin Lista, luego en la casilla Origen escriba =Distritos, y luego Aceptar.

9.-Guarde este archivo en disco con el nombre Encuesta.

2da Parte: Crear una macro para ingresar los datos de las encuestas

1.- En la celda B3 de la Hoja1 elija con la lista desplegable el nombre de uno de los distritos. Por ejemplo el distrito de Brea.

2.-Seguidamente escriba para Brea las cantidades de personas que al ser encuestadas dijeron que compraran televisores de cada una de las marcas.

A continuacin vamos a utilizar la grabadora de macros para los datos que se han escrito para el distrito de Brea en la Hoja1 sean copiados en el cuadro que se encuentra en la Hoja2.Esto significa que la macro deber seleccionar el rango de celdas B4:b10 (llamado Datos), y copiarlos en la Hoja2 y en las celdas que se encuentran a la derecha del distrito llamado Brea.3.-Llame al men Herramientas/Macro/Grabar nueva macro4.-Escriba como nombre para la macro la palabra CopiarDatos y luego Aceptar

5.-Al aparecer el mensaje "Grabando" en la barra de estado, realice los sigyuientes pasos:

Seleccionar en rango llamado Datos

Hacer un clic en el botn de herramientas Copiar, o sino presione Ctrl+C.

Pasar a la Hoja2 y seleccionar la celda A1.

Llamar al men Edicin/Buscar y en la casilla Buscar escriba la palabra Brea, hacer un clic en el botn Buscar siguiente y luego clic en el botn Cerrar.

Esto har que el puntero de celda se ubique sobre la celda que contiene el nombre del distrito de Brea.

Verifique que se encuentre activo el botn de herramientas Referencia relativa (sino hacer un clic sobre l para activarlo)

Utilizando el teclado presione una vez la tecla de desplazamiento a la derecha.

Llamar al men Edicin/Pegado especial, y en la ventana de dilogo elija la opcin Valores y adems marque la casilla Transponer, luego Aceptar

Ahora nuevamente desactivar el botn de herramientas Referencia relativa.

Hacer un clic en la celda A1.

Regresar a la Hoja1 y presionar la tecla Esc

Verificar que se encuentre seleccionado el rango llamado Datos (celdas A4:A10) y escribir la siguiente frmula:

=BUSCARV(B$3,Cuadro,FILA(A4)-2,0)y seguidamente presionar la combinacin de teclas Ctrl+Enter con la finalidad de que la formula ingrese a la vez en todas las celdas del rango Datos.

Seleccionar la celda B3.

Finalmente hacer un clic en el botn Detener grabacin.

6.-Luego asigne la macro CopiarDatos que acaba de crear al grfico que se encuentra en la Hoja1. Para esto hacer un clic derecho dentro del grfico y con la opcin Asignar macro elija la macro CopiarDatos y Aceptar. Luego haga un clic en cualquier celda fuera del grfico para liberar su seleccin.

7.-Presione las teclas Ctrl+G para guardar el archivo.

Nota.-Si en la celda B3 se elije el nombre de un distrito, aparecern entonces los datos de la encuesta de ese distrito que hayan sido copiados en la Hoja2, como ocurre con los distritos de Ancn y Brea donde ya se copiaron sus datos, y para los dems distritos que aun no tienen datos aparecern las celdas con los valores de cero. Esto se debe a las frmulas BuscarV que se escribieron en el rango llamado Datos.

De ese modo se podr saber de que distritos ya se han ingresado los datos de su encuesta y de cuales otros aun faltan ingresar.

Sin embargo, como la grabadora de macros graba exactamente todas las acciones que uno realiza, entonces cada vez que se ejecute la macro, sta siempre copiar los datos de las encuestas para el distrito de Brea e ignor los dems distritos porque as se grabaron las instrucciones en la macro.

Para modificar esto hay que ingresar al editor de >Visual Basic y alli modificar la instruccione correspondiente.

8.-Llamar al men Herramientas/Macro/Macros9.-Seleccionar en la ventana de dialogo el nombre de la macro CopiarDatos y luego hacer un clic en el botn Modificar que se encuentra a la derecha de la ventana.

Esto abrir el Editor de Visual Basic y mostrar en una hoja de mdulo a la macro CopiarDatos semejante a la que se muestra a continuacin:

Sub Macro1()

'

' Macro1 Macro

' Macro grabada el 18/09/2004 por Daniel Zegarra

'

Application.Goto Reference:="Datos" Selection.Copy

Sheets("Hoja2").Select

Range("A1:I1").Select

Cells.Find(What:="Brea", After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:= _

xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=True

Range("A1:I1").Select

Sheets("Hoja1").Select

Application.CutCopyMode = False

Selection.FormulaR1C1 = "=VLOOKUP(R3C,Cuadro,ROW(RC[-1])-2,0)" Range("B3").Select

End Sub

10--Busque la instruccin que contiene el nombre del distrito de "Brea" y reemplcela por la instruccin Range("Lugar") tal y como se muestra a continuacin:

Cells.Find(What:= Range("Lugar"), After:=ActiveCell, LookIn:=xlFormulas, _

11.-Regrese a la Hoja1 de Excel y vuelva a guardar el archivo presionando las teclas Ctrl+G.

Con esto ya estamos listos para ingresar los datos de la encuesta de cualquier otro distrito.

Por ejemplo haga lo siguiente:

Elija en la celda B3 el nombre de algn otro distrito que an no tenga datos (debern aparecer las celdas con los valores en cero). Luego escriba encima de los ceros, los datos de la encuesta en ese distrito. A continuacin seale con el mouse el grafico de la Hoja1 y haga un clic para ejecutar la macro, con lo cual los datos escritos se copiarn en el cuadro de la Hoja2.

A medida que vaya ingresando as los datos de las encuestas de los dems distritos, el grafico ira mostrando los resultados en porcentajes acumulados de todos estos distritos.

La Funcin FILA

La sintaxis de la funcin es la siguiente:

=FILA(Referencia)

Donde:

Referencia Es una coordenada de celda o un nombre de rango.Esta funcin le dice a Excel a partir de que nmero de fila se encuentra ubicada la referencia.

La Funcin FILAS

La sintaxis de la funcin es la siguiente:

=FILAS(Referencia)

Donde:

Referencia Es una coordenada de celda o un nombre de rango.Esta funcin da como respuesta la cantidad de filas que contiene la referencia.

Ejemplos:

=FILA(A6)Rpta: 6

=FILA(B4)Rpta: 4

=FILAS(A4:A7)Rpta: 4

Si el rango A3:B7 tiene el nombre CUADRO:

=FILA(CUADRO)Rpta: 3

=FILAS(CUADRO)Rpta: 5

Nota.-En forma anloga a las dos funciones anteriores tambin se pueden usar las funciones COLUMNA y COLUMNAS respectivamente.

Ejemplos:

=COLUMNA(A6)Rpta: 1

=COLUMNA(A3:B7)Rpta: 1

=COLUMNAS(CUADRO)Rpta: 2

9. Edicin de Macros en Visual Basic para Aplicaciones

a. Que es Visual Basic para Aplicaciones

Es un lenguaje de programacin por medio del cual se pueden escribir instrucciones secuenciales y detalladas dentro de uno o varios programas, para lograr as automatizar el uso de las aplicaciones del paquete Office. A este grupo de instrucciones escritas para una aplicacin se la conoce como una Macro (Macro instruccin o grupo de varias instrucciones)

b. El Editor de Visual Basic

La escritura de estas instrucciones se realiza con ayuda de un Editor de Visual Basic, que es una ventana de aplicacin adicional a Excel o a cualquier otra aplicacin de Office, pero la macro que se crea a travs del Editor ser guardada conjuntamente con el libro de Excel para el cual se han escrito estas instrucciones.

Para ingresar al Editor de Visual Basic:

Estando en cualquier libro de Excel, llamar al men Herramientas/Macro/Editor de Visual Basic, o sino presionar las teclas Alt+F11.

Si al ingresar al Editor de Visual Basic, no estuvieran visibles el explorador de proyectos o la ventana de mdulo, entonces proceda del siguiente modo:

1.-Llamar al men Ver/Explorador de proyectos.

2.-Llamar al men Insertar/Mdulo.

El Explorador de proyectos nos muestra en un rbol de carpetas los diferentes archivos o libros abiertos en Excel, y dentr