curso de excel avanzado
TRANSCRIPT
CURSO AVANZADO DE EXCEL 2007
Elaborado por: RICARDO A. FRANCO
CONFAMILIARES
2009
CURSO DE EXCEL AVANZADO
Validación de datos: Permite crear listados en el menú de datos selecciona validación
de datos, en configuración se despliega el menú permitir y se selecciona lista, en origen
se seleccionan la tabla de datos para crear la lista (tabla de 1 columna).
Buscar valor: Buscarv esta función busca un valor en la primera columna referenciada
y devuelve el valor en la fila de la columna indicada en el ovalo en verde del indicador
de columna (el numero 2 es contigua, 3, 4 etc.). Se coloca falso ó 0 para que el valor
sea idéntico al valor buscado.
Referencia relativa: Si se corre una fórmula Excel va corriendo las celdas.
Referencia absoluta: consiste en anclar la celda con el signo $ o la tecla F4.
Referencia mixta: La fila o la columna esta anclada.
Uso del cursor Si se hace doble click en el recuadro inferior derecho negro se llena la
parte inferior hasta el final.
Prueba lógica Si: debe ir expresada con <>(diferente), <=, >=.
Formato condicional: En la barra de inicio en la ficha de estilos esta el formato
condicional, se selecciona administrar reglas y se empiezan a insertar paulatinamente.
Cuenta con muchas opciones mediante un menú desplegable.
CTRL + SHIFT + FIN: Para seleccionar celdas nos lleva hasta la parte inferior de la tabla
seleccionando la primera fila.
Protección de libros: Primero mediante el formato de celda, en la ficha de proteger se
desmarca Bloqueada para las celdas a las que se les va a permitir modificación y luego
en la barra revisar se selecciona proteger hoja y se seleccionan las condiciones de
protección, puede agregarse una clave si se prefiere. “MARIANA”.
Las celdas vienen por definición bloqueadas.
Ocultar las celdas: En la opción formato de celda ficha bloquear esta la opción de oculta.
En Revisar proteger hoja se selecciona celdas bloqueadas y de esta manera el usuario no
puede ver las formulas que están dentro de la celda. Se selecciona celdas bloqueadas y
celdas desbloqueadas.
Si se copian no se trae las formula únicamente los valores numéricos en otra hoja.
Guardar el libro con una contraseña para apertura: En el logo Office, guardar como
archivo Excel, aparece el menú de herramientas dar click a opciones generales, hay una
contraseña de apertura y de escritura.
Excel pide una validación de la contraseña.
Para quitar la contraseña se procede igual a la forma anterior y se elimina la contraseña.
FILTROS
Creando códigos: Se tienen varias columnas y se quiere generar un código.
La función de texto concatenar reúne contenidos hasta 255 celdas diferentes.
La función de texto derecha devuelve los caracteres al final de una celda.
La función de texto izquierda devuelve los caracteres al principio de una celda.
Estas funciones devuelven los números convertidos en textos.
La función de texto extraer permite seleccionar en medio de la celda, es muy útil para
extraer la información de archivos planos.
Se pueden hacer 8 funciones en una misma celda.
Para concatenar (union de varias celdas) se agregan las funciones en el menu
desplegable de la izquierda.
Para incluir una funcion dentro de otra se selecciona de la viñeta desplegable del
recuadro inferior de y una vez metidos los argumentos se selecciona la función inicial
concatenar para seguir agregando fórmulas, se debe para en el cuadro de texto para
que le permita agregar más funciones (este es el que esta con el ovalo en verde).
Inmovilizar panelas ancla la esquina superior izquierda, esta en el menú de vista.
Copiar una hoja: Una forma es oprimir sostenido con click izquierdo del mouse y
arrastrar a donde se quiere ubicar antes de soltar se oprime CTRL (sale una +) y se
suelta.
Filtros: Se usa el embudo. Se quiere encontrar una palabra determinada se demarca
todo y se selecciona el criterio de búsqueda deseado. En la barra inferior salen
estadísticas de la búsqueda.
La columna que tenga un filtro aplicado aparece con un grafico en la pestaña.
Se puede filtrar también con condicionales con la función del ovalo que sale al oprimir
las pestaña:
Si se quieren quitar los filtros se hace seleccionando en la barra el símbolo de embudo.
Los filtros se deben actualizarse pues no trabajan en línea.
FILTROS AVANZADOS: El criterio es seleccionar los jefes de Manizales y los ingenieros
de Pereira: Se usa la opción de filtros avanzados. Primero los criterios deben definirse
en un sitio diferente, la tabla debe ser textual con la lista que se va a filtrar, esta es la
tabla de condiciones:
Horizontalmente se lee con un “y” y verticalmente con un “o”, por eso la tabla va en
forma horizontal, el filtro avanzado no se puede deshacer por lo que debe realizarse una
copia a otro lugar para no afectar la tabla original.
Para ordenar los datos:
Excel ordena por el primer criterio (profesión) y luego el siguiente (sueldo) este se
ingresa con el botón de agregar nivel.
Filtros avanzados con criterios numéricos:
Otro ejemplo:
Si la celda está en blanco Excel escoge todos los conductores en este caso, es decir, no
utiliza ningún criterio de búsqueda.
Totalización parcial: En datos esquema subtotal, la tabla debe estar previamente
ordenada.
Salto de página entre grupos nos cambia la página a la hora de imprimir para cada
grupo.
En la opción de agregar subtotal se escoge el criterio para realizar la operación.
En agregar subtotal a se marca el campo al que se le quiere aplicar una función.
Después de realizada la sub-totalización queda en la parte izquierda de la hoja:
Esto permite ver resultados parciales al oprimir los números 1,2 ó 3.
PARA DIVIDIR VENTANAS: Se arrastra el mouse sostenido del botón en el recuadro
rojo, se puede hacer vertical y horizontal.
ENCABEZADOS Y PIE DE PAGINA: En márgenes personalizados.
Para agregar títulos en hojas que se cortan (en saltos de página):
Excel imprime en forma de N invertida como muestra el gráfico, la otra forma de acceder
al cuadro de dialogo es mediante márgenes personalizadas en la ficha de hoja:
Se pueden personalizar los encabezados y pie de página en la siguiente opción:
Área de impresión: Deja fija un área para ser imprimida siempre que se dé la opción
impresión. Esta opción está en la ficha de configurar página.
TABLAS DINAMICAS
Hay 3 dimensiones: Filas, columnas y páginas.
Fuente de datos externa es utilizar un archivo plano.
En el menú de Opciones lista de campo recupera el cuadro de diálogo de la tabla
dinámica. El filtro de información define la variable con la que se crearán informes
filtrados.
Para seleccionar diferentes criterios se usa el menú desplegable de la tabla dinámica y
se marca seleccionar varios elementos:
Cuando se cambian datos de la tabla original en el menú de opciones está la función
actualizar porque las tablas no están Online, otra forma es usar el botón derecho del
mouse para acceder a esta función.
Al dar click en el menú desplegable en el ovalo de grafico anterior se llega a las opciones
para las operaciones en la columna que se muestra a continuación:
TRABAJANDO CON HOJAS MULTIPLES: Se seleccionan con SHIFT sostenido y el botón
derecho del mouse, las hojas que están seleccionadas adquieren un sombreado blanco
como muestra la figura. En todas las hojas marcadas se desarrollan las mismas
operaciones.
BUSQUEDA Y REFERENCIA
Validación de valores en una celda: Click en datos validación de datos se ingresan
las condiciones:
Se puede ingresar un mensaje de entrada y otro de error.
Para seleccionar una tabla (nombre de rango) se sombrea toda el área y se le da un
nombre a la tabla (no se pueden dejar espacios) en el recuadro del óvalo y se oprime
enter.
Una vez ingresado el nombre de la tabla se puede seleccionar del recuadro del ovalo
desplegando en el menú el nombre dado a la tabla como muestra la siguiente figura:
Para verificar que la selección quede bien hecha se usa:
Al utilizar los nombres de rangos se debe acceder por el menú de formula a Utilizar en la
Formula:
FUNCIONES VARIAS
Función contar: Cuenta solo celdas con números.
Función contara: Cuenta todas las celdas que contienen datos (Alfanuméricos).
Función contar.si: Realiza el conteo con una condición dada. Para el criterio
condicional no influye que vayan mayúsculas o no (Aprobó ó aprobó), pero las tildes y
caracteres especiales si influyen.
Funcion Y: Solo arroja valores de falso y verdadero, debe ser precedida de una función
lógica como SI, esta Permite reunir varias condiciones en las pruebas lógicas como si, su
funcionamiento se muestra en el siguiente ejemplo:
Luego se selecciona en la pestaña la Función Y del menú desplegable:
Se llega al menú de dialogo de la función Y en donde se meten las funciones:
Una vez ingresadas las condiciones de Y se le da click sobre el si en la barra de menú de
formula para terminar los argumentos de dicha función:
Otro ejemplo para el caso de endeudamiento, ingresando las condiciones para analizar el
prospecto:
La función O se emplea cuando necesito que cualquiera de varias condiciones se
cumpla, ojo debe estar acompañada de una prueba lógica como O:
Si es secretaria o supervisor cumpliría la condición que se vaya a plantear.
Promedio.si: Calcula la media aritmética de los datos que cumplen una condición dada.
Promedio.si.conjunto: Calcula la media aritmética de los datos que cumplen un
conjunto de condiciones dadas.
Si.ERROR: Me permite colocar el valor que desee si al calcular la formula me resulta un
error, la sintaxis es SI.ERROR(La formula, y el argumento con el que llena la celda si
tiene un error):
Ahora(): Sirve para colocar la fecha y la hora actual.
GRAFICOS
Anteponer el signo pesos en los ejes de gráfico ($2000 en vez de 2000 $):
Se da click derecho sobre el eje, se selecciona Dar formato a eje, se escoge el formato
moneda:
En el recuadro de código de formato se antepone el argumento entre los corchetes y se
oprime AGREGAR:
COMBINAR TIPOS DE GRAFICOS: Para los gráficos planos se tiene la opción de
combinar varios tipos, se da click sobre la serie que se desea cambiar y se seleccionar el
tipo de gráfico.
GRAFICOS CON 2 EJES
Se tienen los siguientes datos:
ARTICULO ENERO CANTIDAD
ENERO VALOR
CAMISAS 163 $ 4.075.000PANTALONES 97 $ 4.365.000ZAPATOS 29 $ 1.595.000MEDIAS 111 $ 888.000FALDAS 72 $ 3.240.000BLUSAS 148 $ 5.180.000
Al elaborar el grafico de columnas se tiene:
CAMISAS
PANTALO
NES
ZAPATO
S
MEDIAS
FALD
AS
BLUSA
S0
100000020000003000000400000050000006000000
ENERO CANTIDADENERO VALOR
Por la escala de pesos no se aprecia la serie de cantidades, por lo tanto se tiene la
necesidad de utilizar un gráfico con dos ejes, se procede de la siguiente manera, en el
menú de herramientas de grafico, barra presentación, se selecciona en la ventana del
ovalo la serie de cantidades que no se visualiza:
Posteriormente se selecciona aplicar formato a la selección:
Se llega al siguiente cuadro de menú en donde se aumenta el ancho del intervalo y se
selecciona eje secundario:
Se puede llevar hasta 325% para que no queden las dos series superpuestas y se
puedan diferenciar para combinar varios formatos.
El resultado:
CAMISAS PANTALONES ZAPATOS MEDIAS FALDAS BLUSAS$ 0
$ 1,000,000
$ 2,000,000
$ 3,000,000
$ 4,000,000
$ 5,000,000
$ 6,000,000
0
20
40
60
80
100
120
140
160
180
$ 4,075,000$ 4,365,000
$ 1,595,000
$ 888,000
$ 3,240,000
$ 5,180,000
VENTAS ENERO
ENERO VALOR ENERO CANTIDAD
ARTICULOS
VEN
TAS
(PES
OS)
Se selecciona la serie azul y se cambia el tipo de grafico a dispersión con líneas haciendo
click derecho sobre las barras azules. Obteniendo como resultado el siguiente:
CAMISAS PANTALONES ZAPATOS MEDIAS FALDAS BLUSAS$ 0
$ 1,000,000
$ 2,000,000
$ 3,000,000
$ 4,000,000
$ 5,000,000
$ 6,000,000
0
20
40
60
80
100
120
140
160
180
$ 4,075,000$ 4,365,000
$ 1,595,000
$ 888,000
$ 3,240,000
$ 5,180,000
VENTAS ENERO
ENERO VALOR ENERO CANTIDAD
ARTICULOS
VEN
TAS
(PES
OS)
Se puede observar el eje secundario de cantidades a la derecha.
GRAFICOS DE COTIZACIONES
Permiten representan varios datos en un mismo grafico, ejemplo en un negocio muestra
el máximo, mínimo, apertura y cierre.
Lunes MartesMiércoles Jueves Viernes SábadoDomingo18001850190019502000205021002150220022502300
Cierre
Cuadros blancos es cuando el cierre es mayor que la apertura y negros cuando ocurre lo
contrario.
GRAFICOS DINAMICOS
Esta opción se encuentra debajo de tablas dinámicas como muestra la ilustración:
FORMULARIOS
Activando el menú de programador:
En el icono de office se da click y luego a opciones de Excel:
Y luego se activa la opción de programador:
Y en la barra de Excel debe aparecer ahora el menú de programador:
Agregando controles de formulario: En el menú programador se da click y sale la
diversidad de botones que se tiene:
Se selecciona el control de número y sale la siguiente cruz:
Se extiende el botón con el botón izquierdo del mouse sostenido y se construye el
siguiente botón:
Se da click derecho y se obtiene el menú de formato de control:
En la ficha de formato se ingresan las condiciones del botón de formulario y se vincula a
la celda requerida:
Formulario de cuadro combinado:
Líneas de unión verticales es el numero de datos que se visulizan en la lista desplegable,
en este caso corresponde a 8:
Función VF.PLAN: Devuelve el valor futuro de un capital inicial después de aplicársele
una serie de tasas compuestas:
Principal es el capital y programación es la matriz con las diversas tasas.
FINANCIERA VA: Calcula el valor presente de una inversión donde la tasa es el % de
interés, Nper es el número de periodos y el pago se debe ingresar con signo negativo y
puede ser por ejemplo un préstamo determinado.
El símbolo &: Es una forma rápida de concatenar:
FUNCIONES DE BASE DE DATOS
LAS FUNCIONES DE BASE DE DATOS DEBE SELECCIONARSE CON LOS NOMBRES
DE LAS COLUMNAS (Campos).
Función SUMAR.SI: Suma los datos que cumplen una condición, rango es el vector
donde busca la condición y rango suma es el vector de valores que va acumulando.
K.ESIMO.MAYOR: Busca el valor k (1,2,3,4,5…) de una matriz.
BDMAX: Extrae un valor máximo de una base de datos que cumple los requisitos, base
de datos es el rango total de registros que conforman la lista, nombre del campo es la
columna en numero o el encabezado de la columna de la cual se va a extraer el valor,
criterio es la columna de la base de datos de donde se va a extraer el valor buscado.
BDMIN: Extrae un valor mínimo de una base de datos que cumple los requisitos, base
de datos es el rango total de registros que conforman la lista, nombre del campo es la
columna en numero o el encabezado de la columna de la cual se va a extraer el valor,
criterio es la columna de la base de datos de donde se va a extraer el valor buscado.
BDEXTRAER: Extrae un registro de una base de datos que cumple los requisitos
definidos, base de datos es el rango total de datos, nombre del campo es la columna en
número o encabezado de columna del cual se va a extraer el valor, criterio es el nombre
de campo y con una celda debajo que como el valor que debe buscar.
BDPROMEDIO: Calcula el promedio de una base de datos de acuerdo a los criterios
especificados, nombre del campo es la columna a la que calculará el promedio y los
criterios son los valores con los cuales se calculará el valor promedio. Ojo se deben
seleccionar las bases de datos desde los títulos.
BDCONTAR: Realiza un conteo de registros de una base de datos que cumplen los
requisitos, base de datos es el rango de registros que conforman la lista, nombre del
campo es la columna en número o el encabezado de columna del cual se va a extraer el
valor, el criterio esta compuesto por el nombre de campo y una celda debajo con el valor
a buscar.
FUNCIONES DE ANALISIS Y SI
La función se encuentra en la ficha de datos como se muestra en la figura:
BUSCAR OBJETIVO: Permite realizar cálculo de un valor de una variable para conseguir
un objetivo planteado.
Define la celda B3 que es el valor de compra del artículo para poder venderlo al público
a 60000, a partir de la celda B7 que es el P.V.P.
ESCENARIOS: Las celdas deben tener nombres para que el reporte salga con los
nombres de las variables
La herramienta se encuentra en la ruta:
Al agregar escenarios se llega al siguiente cuadro de diálogo, en nombre de escenario
usan valores alfanuméricos y los espacios se llenan con underline (_):
Al aceptar se llega al siguiente dialogo, nótese que aparecen los nombres de las
variables porque se habían agregado inicialmente de lo contrario aparecerían celdas (B1
o C3):
Una vez agregados los escenarios se oprime el menú de resumen:
Y posteriormente se crea el tipo de informe y se selecciona la celda de resultado:
El resumen queda como se muestra:
Resumen de escenario
Valores
actuales: Actual Optimista Pesimista LogicoSubir_preci
oCeldas cambiantes: Costo_Variable $4,00 $4,00 $3,50 $4,00 $4,00 $4,00 Precio_de_Venta 6,00 4,50 5,00 5,00 4,30 6,00 Unidades_Vendidas 130.000 130.000 140.000 110.000 150.000 130.000Celdas de resultado:
Utilidad 160.000,00 -35.000,00 110.000,0010.000,0
0-
55.000,00160.000,0
0Notas: La columna de valores actuales representa los valores de las celdas cambiantesen el momento en que se creó el Informe resumen de escenario. Las celdas cambiantes decada escenario se muestran en gris.
Si se quieren cambiar los escenarios se debe acceder por el administrador de escenarios
y cambiar los valores en cada escenario, finalmente se genera un nuevo resumen.
TABLAS: Permite llenar todas las celdas de una tabla de destino con una formula
preestablecida.
Se ingresa la formula en la esquina superior izquierda de la tabla a crear y se selecciona
la tabla en donde se van a llenar los datos:
Luego se selecciona en el menú de datos la ficha Análisis Y si opción tabla, se definen las
entradas de fila y columna:
Y al aceptar el resultado es una tabla con todas las celdas llenas creadas con base en la
formula inicial planteada en el extremo superior derecho de la tabla:
Esta función va modificando los valores de filas y columnas dentro de la fórmula
paulatinamente, si la formula tiene más de dos argumentos deja los otros fijos.
SOLVER: Aparece al final de datos, se activa activa en el icono de Windows opciones de
Excel, menú complementos y click en el botón de ir y se selecciona solver, a veces no se
encuentra instalada la opción y debe instalarse
Es similar a buscar objetivo pero más amplia, el valor de la celda tiene más posibilidades
como valor máximo, mínimo, restricciones es decir problemas de algebra lineal.
Para agregar unidades y mantener el formato numero, en formato de celda se escogen
personalizados y se digita 0 “Horas”.
Función SUMAPRODUCTO: Calcula la suma total del producto de dos matrices de igual
dimensión.
El menú SOLVER viene dado como sigue, celda objetivo es la celda a calcular y debe
tener una formula, se seleccionar en este caso un máximo y se seleccionan las celdas de
cantidades a producir como variables a calcular, se deben agregar las restricciones del
caso y en el menú de opciones solo valores positivos.
Las celdas que cambiara el SOLVER son las cantidades a producir con la finalidad de
definir la mezcla de productos que maximice la utilidad (ejercicio solverproductos.xls).
Las restricciones para el caso de la fábrica de productos serían:
La mano de obra utilizada no puede ser mayor a la disponible.
La materia prima utilizada no puede ser mayor que la disponible.
Las unidades producidas no pueden ser mayores a las demandadas.
El menú de opciones del SOLVER es el siguiente, aquí se puede seleccionar que no sean
valores negativos (en el recuadro de opción adoptar no negativos):
Se tienen las siguientes opciones de informe:
Excel realiza el informe en una hoja de cálculo nueva.
RESOLVIENDO UN SISTEMA DE ECUACIONES SIMULTANEAS CON SOLVER
Se tiene el siguiente sistema de ecuaciones:
Se construye una tabla con los valores de las soluciones (se ingresa cualquier valor):
Se expresan las ecuaciones en forma de fórmula en otra tabla como sigue:
Se puede observar que los valores asignados a las variables no satisfacen las ecuaciones
ya que los valores de la columna de la izquierda difieren de los de la derecha.
Se activa EL SOLVER y se llega al siguiente cuadro de dialogo:
Los argumentos para resolver el problema son los siguientes:
La celda objetivo es la primera de la columna amarilla (puede ser cualquiera de
esta zona).
El valor de dicha celda (zona amarilla) debe ser igual al valor correspondiente en la
zona naranja.
Las celdas que cambia el SOLVER en su cálculo son las de la zona verde.
Y la restricción, por tratarse de una igualdad, consiste en que las celdas en
amarillo den ser iguales a las que se encuentran en naranja.
Se da resolver y los resultados quedan satisfaciendo la igualdad planteada:
MACROS
Hay dos tipos de macros, uno para operaciones repetitivas y otro de programación para
tareas más complejas.
Macro operaciones repetitivas: en el menú programador se oprime grabar macro y
se llega al siguiente dialogo:
El nombre de la macro no puede llevar espacios.
Para las teclas abreviadas se debe asignar una letra mayúscula para evitar
problemas con teclas rápidas preestablecidas por el fabricante de Excel, no
pueden ser números.
Guardar macro en se tienen 3 rutas como se ve en el gráfico, lo más recomendado
es grabarla en el libro permitiendo ejecutarla con facilidad en otros computadores.
La descripción se indica su finalidad y toda la información adicional que se quiera
registrar.
Después de darle aceptar empieza la grabación de la macro, en la parte inferior se
informa que inicia proceso de grabación y aparece el botón de parar:
Se inician las operaciones requeridas y el macro va grabando la secuencia.
Se detiene la grabación en la parte inferior de Excel o se ubica en vista opción
macro hay aparece detener grabación:
Si hay errores se pueden corregir en el código fuente de la macro o durante la
grabación de acuerdo a lo que más convenga.
Para Excel 2007 se debe guardar el libro como archivo con macro(.xlsm) de lo
contrario se borra la macro o como libro de excel 97 – 2003 y esta incluye per se
las macros:
Para cambiar las teclas de modo abreviado se por la barra ingresa por macros y
opciones:
Oprimiendo el botón ejecutar también se puede correr la macro.
La otra forma de ejecutar una macro es agregando un botón, por el menú de
programador:
Después de seleccionar el botón se llega al siguiente cuadro de diálogo:
Se asigna la macro deseada y se oprime aceptar, se debe tener en cuenta que el
botón no se puede salir de la cela A1:
Se oprime en el botón y este ejecuta el macro previamente grabado.
Seguridad de macros: Excel puede tener deshabilitados los macros y debe
modificarse en la siguiente ruta, se cierra el libro de EXCEL y se vuelve a abrir el
archivo:
Código fuente: Se puede acceder al código de la macro por la siguiente ruta:
Y se llega a la siguiente pantalla de Microsoft visual basic con el código fuente:
Las comillas sencillas en visual basic se usan para introducir texto y estas quedan
de color verde como se muestra, estas instrucciones son solo de tipo informativo:
Siempre empezar la macro en una celda diferente en donde necesito hacer el
calculo.
ALGORITMOS
Serie de pasos lógicos y secuenciales que buscan solucionar un problema.
Hay dos tipos: Cualitativos y cuantitativos.
Ej. Cualitativos: Hacer un jugo, construir una casa.
Algoritmo de hacer un jugo:
Inicio
Seleccionar la fruta.
Definir la cantidad.
Lavar la fruta.
Pelar la fruta.
Picar la fruta.
Agregar la fruta a la licuadora.
Agregar la leche y/o el agua.
Agregar el azúcar o endulzante.
Licuar.
Colar.
Servir.
Fin
Ej. Cuantitativos: Raíz cuadrada, nómina, P & G, inventario.
DIAGRAMAS DE FLUJO
Representación gráfica de un algoritmo.
Las siguientes son las representaciones más utilizadas:
INICIO O FIN
ASIGNACION O PROCESO
DECISION
ENTRADA DE
DATO
SALIDA
CONECTOR
Ejemplo un ejercicio para calcular la comisión de venta:Inicio
Leer el precio artículoSi precio articulo <1000
Comisión= precio articulo * 10%Si no
Comisión= precio articulo * 10%Fin de siSumar 50 al precio del artículo Precio articulo <=2000
Sumar 50 al precio de artículoVolver al primer si
SinoFinalizar
SALIDA
Inicio
Precio
articulo
Precio
<1000
Si
PROGRAMACION EN EXCEL
Con la tecla F8 se depura la macro. Cuando se tiene en modo detenido
antes de comenzar se activa el código col la tecla:
Si se hace una macro dirigida a una hoja como “Hoja 2” no se puede cambiar el nombre
de la misma pues se descuadra la macro.
La palabra Rem reemplaza la comilla para comentarios en visual basic.
El inicio de la macro por Excel fue:
Comisión=
precio*10%
No
Comisión= precio
*15%
Precio
<=2000
No
Precio= precio+50
Fin
S
Se personaliza para hacer mas corta las líneas de programación en la parte de entrada
de variables (Value: define las variables):
A continuación defino variables con la palabra Dim, se definirá la variable como entera
(integer):
Continuamos introduciendo la función de Si para desarrollar el cálculo de la comisión:
Se asigna la salida del cálculo de comisión como muestra el recuadro rojo:
Se utiliza el comando for para calcular de la fila 2 to 32:
Se ejecuta el comando para que precio suba de 50 a 50:
El resultado es el calculo del ultimo precio:
Se modifica la orden en el recuadro para que no sobreescriba siembre en las celdas A1 y
B1:
El resultado es el calculo de los precios de 50 en 50:
Programa opcional 2:
Se mejorará el programa agregando más instrucciones, una caja de dialogo:
El resultado en la hoja de cálculo es el que sigue:
Agregar un cuadro de dialogo para ingresar el máximo precio:
El resultado en Excel:
Calculando hasta el precio máximo definido se tiene:
Ingresamos el límite inferior:
Se organiza el formato del precio mínimo y máximo para que sean datos numéricos y no
datos de texto, se usa la función val (para convertir en dato numérico) para esto hay que
usar una variable que convierta el texto (string) en un valor numérico (LimInf2 y
LimSup2):
Elaborar un algoritmo y un diagrama de flujo que encuentre el número más pequeño entre 3 números ingresados.Inicio
Leer el número 1Leer el número 2Leer el número 3
Si número 1 < número 2Si número 1 < número 3
El número menor = número 1Si no
Si el número 2 < número 3El número menor = número 2
Si noEl número menor = número 3
Fin de siFin de si
Finalizar
El diagrama de flujo queda de la siguiente manera:
Inicio
Número 1
Número 1 < Número 2No
Número 2
Número 3
Si
Número 1 < Número 3
Si
El número 1 es el menor
Número 2 < Número 3
Si
El número 2 es el menorEl número 3 es el menor
No
Fin
El número 3 es el menor
No
En EXCEL se carga visual basic por la ficha programador y se da insertar modulo:
Se inicia la digitación del código fuente:
La función Range nos muestra el valor en la parte de la hoja de cálculo que deseamos.
Teclas abreviadas: FORMATO DE MONEDA CTRL + SHIFT + 4
PROGRAMACION ORIENTADA A OBJETOS
Se activa con visual basic y use form
Cada botón tiene diversas propiedades:
FORMULARIO DE INGRESO DE DATOS
Se activa con visual basic y use form:
Estos iconos permiten navegar entre el formulario y el código fuente:
La tecla F5 ejecuta el formulario para poder visualizar el avance
del mismo:
Se agregan los botones de comando:
Con el click izquierdo se seleccionan todas las etiquetas, la referencia es el cuadro con
los puntos blancos en el ovalo rojo:
Se da click derecho y sale el siguiente menú:
ALINEAR AL MEDIO, permite que dos cuadros se pongan a igual altura por su parte
central:
En el CAPTION se da el nombre personalizado al formulario, se cambia el use form:
Se recomienda grabar por el libro de Excel y no por el menú de visual basic.
Para igualar al tamaño se selecciona, click derecho y se llega al menú de igualar
tamaño:
En un formulario los botones de opción funcionan simultáneamente, si fuera a
seleccionar el tipo de documento y el genero no se podría hacer:
La solución es cortar masculino y femenino, seleccionar marco, trazarlo en el área que
quiero independizar y al salir Frame se pega adentro las opciones de masculino y
femenino:
Y para borra el frame se va a caption y se borra este texto.
Si usamos el tabulador para navegador se realiza una navegación desordenada y no
secuencial acorde al orden de los objetos, esto se soluciona parándose en el objeto y en
TABINDEX corregir el orden en que deben aparecer:
Para que se ejecute el formulario apenas se abra la hoja de calculo se va a this workbook
y se agrega la secuencia de la pantalla:
En use Form 1 se puede dar Name al Useform1 y colocarle el nombre de capturador.
Posteriormente se le dan nombres a cada objeto en Name:
Se da doble click sobre el formulario y se llega a:
Se deja la cedula activada (1) y el TI apagado (0):
La instrucción Categoria.AddItem K, quiere decir que a la variable categoría se le va a
adicionar K que es 1,2 o 3 (From 1 to 3).
Tip_Cedula.Set Focus ubica el cursor en el campo de cédula:
Esta instrucción ingresa una fila después de la fila 3 de la hoja de cálculo:
Range("A3").Select
Selection.EntireRow.Insert
La función case evita los si anidados:
USO DE CICLOS Y VECTORES EN EXCEL
Ver ejercicio Factura_Martes
Vectores: Representa los elementos que forman un conjunto, es un arreglo que tiene
una sola columna, ej:
123456
Articulo 1Articulo 2Articulo 3Articulo 4Articulo 5Articulo 6
Se definen de la siguiente manera:
Matrices: albergan más de un conjunto, por lo tanto tienen 2 o más columnas.
AS DOUBLE: Se usa para definir variables que albergan números de gran tamaño.
AS STRING: Para variables de texto
EL MENU DE OPCIONES AVANZADAS
Se accede por la ventana, click en opciones de EXCEL y avanzadas:
Este menú me permite configurar varias funciones de acuerdo a las preferencias de
trabajo:
IMPORTAR ARCHIVO PLANO
Se debe colocar en abrir todos los archivos o archivo de texto y se le da abrir, sale el
siguiente asistente:
Los delimitados están separados por algún carácter como una coma.
El archivo normalmente es hecho en Windows Ansi:
Se da siguiente y llega al menú del carácter, se analiza cual es el separador adecuado
mirando la vista preliminar del archivo:
Dar Siguiente y sale otro asistente que permite modificar el formato de cada columna,
parándose encima y seleccionado el formato y se da finalizar:
Importar datos filtrando los no deseados:
Por la ficha datos, obtener datos externos y se le da importar.
