CE.CA.T.I. No. 89 Celaya, Gto. (Secretaria de Educación
Pública)
FUNCIONES ESPECIALES EN EXCEL
Ordenamiento, Búsqueda especifica
de datos; Búsquedas en Vertical y Horizontal, selección especifica
de datos como búsqueda, estadísticas de: Máximos, mínimos,
promedio, varianza, generación de mensajes por condición, formatos
condicionales.
Indice de este módulo:
(hacer un clic sobre el
enlace para ir directamente a la sección:)
En algunas oportunidades anteriores
del curso se aplico el ordenamiento, ahora lo vernos a detalle el
ordenamiento.
Al momento de ordenar una tabla,
Excel puede hacerlo de forma simple, ordenar por un único
campo u ordenar la lista por diferentes campos a la vez. Para
hacer una ordenación simple, por ejemplo ordenar la lista anterior
por el primer apellido, debemos posicionarnos en la columna del
primer apellido, después podemos acceder a la pestaña Datos y
pulsar sobre Ordenar... y escoger el criterio de ordenación o bien
pulsar sobre uno de los botones 
de la sección
Ordenar
y filtrar para que la ordenación sea ascendente o
descendente respectivamente.
Estos botones también están
disponibles al desplegar la lista que se presenta pulsando la
pestaña junto al encabezado de la columna.
Como comienzo debemos tener en
cuenta que nuestro cursor deberá estar en una de la celdas donde
están contenidos los datos.
Para ordenar la lista por más de un
criterio de ordenación, por ejemplo ordenar la lista por el primer
apellido más la
fecha de nacimiento, en la cinta Datos,
Pulsamos sobre Ordenar... vemos el
cuadro de diálogo Ordenar donde podemos seleccionar los campos por
los que queremos ordenar (pulsando Agregar Nivel para añadir un
campo), si ordenamos según el valor de la celda, o por su color o
icono (en Ordenar), y el Criterio de ordenación, donde elegimos si
el orden es alfabético (A a Z o Z a A, Ascendente o Descendente) o
sigue el orden de una Lista personalizada. Deberás de colocar el
puntero del ratón en un elemento de la lista para qué al momento
de activar la herramienta de ordenamiento seleccione de forma
automática el listado en su totalidad, si estuviera fuera del
listado te indicaría un error tendrías que seleccionar toda lista
y si es grande se complicaría esta operación.
Por ejemplo, si en la columna de la
tabla se guardan los nombres de días de la semana o meses, la
ordenación alfabética no sería correcta, y podemos escoger una
lista donde se guarden los valores posibles, ordenados de la forma
que creamos conveniente, y así el criterio de ordenación seguirá
el mismo patrón.
Seleccionando un nivel, y pulsando
las flechas hacia arriba o hacia abajo, aumentamos o disminuimos
la prioridad de ordenación de este nivel.
Los datos se ordenarán, primero,
por el primer nivel de la lista, y sucesivamente por los demás
niveles en orden descendente.
En la parte superior derecha
tenemos un botón Opciones..., este botón sirve para abrir el
cuadro Opciones de ordenación dónde podremos especificar más
opciones en el criterio de la ordenación.
Si aplicamos el ordenamiento por el
titulo nombre
estos quedaran ordenados, por este elemento, seleccione el titulo
Nombre y haga clic en aceptar, lo nombres quedaran ordenados así
como sus datos que corresponden a cada registro, pero con
referencia al Nombre.
así el resultado de esto es:
Observemos que se han ordenado los
nombres en la lista anterior.
Repetiremos el ordenamiento, pero
ahora agregando niveles, esto es útil cuando son listas de datos
muy extensos y se necesita ordenarlos, con más de un campo de para
analizar los criterios de ordenamiento.
Haga clic en el botón Agregar
nivel, en la nueva línea, debajo de Ordenar por, donde se observa
el titulo Luego por, que ordenara el nombre primeramente y después
por la dirección.
Observaremos que hay 2 direcciones
que tienen la misma calle y con números diferentes.
Como son nombres de personas diferentes quedan algo separados,
recordemos que el primer elemento de ordenamiento es el nombre.
Ejercicio 1:
De la lista actual, ordene por dirección como primer elemento
(Ordenar por), y después ordene por (Luego por) Nombre, observe el
resultado, y guarde este libro con el nombre de:
listado personas 2.xlsx .en
el directorio Mis Documentos o Documentos.
Ejercicio 2:
Ordene la hoja de la siguiente forma: Por Nombre y población,
guarde el libro de trabajo con el nombre de gasolineras_ord1.xlsx
en el directorio Mis Documentos o Documentos.
Ordene la misma hoja de la siguiente forma: Por Razón Social,
Colonia y Domicilio guarde el libro de trabajo con el nombre de
gasolineras_ord2.xlsx en el directorio Mis Documentos o
Documentos.
BUSQUEDA
DE DATOS CON FILTROS
La búsqueda de información tiene varias formas
de realizarse, una de ellas es por medio de la herramienta
filtros, el cual tiene muchas ventajas de búsqueda y selección de
datos, teniendo la hoja de calculo anterior gasolinerias.xlsx,
usemos el separador Datos, y Filtro:
hacemos un clic en el icono: 
se activaran en la primer fila una serie de iconos de esta
forma: 
al lado derecho de cada columna
al seleccionar el icono de búsqueda en la columna NOMBRE nos
mostrara, el siguiente menú
En la sección Filtros de texto, existen un grupo de condiciones
de búsqueda en el grupo de datos:
Es igual a...
No es igual a...
Comienza por...
Termina con...
Contiene...
No contiene...
Filtro personalizado
usemos el filtro de texto Comienza por..
escribo la secuencia de letras SERV y clic sobre el botón
Aceptar
el resultado es el siguiente, del lado derecho
la numeración de las filas y se ocultan las filas que no cumplan
esta condición, y aquellas que estan de color azul son las que
corresponden a la búsqueda.
para regresar al estado inicial en la misma
columna se observa el icono de forma de filtro: 
hacemos un clic en este icono y
nos presenta lo siguiente:
nos mostrara Borrar filtro de "NOMBRE", al hacer clic el filtro
se elimina, y regresara al estado inicial
Ahora usemos el cuadro de búsqueda :
realizara una búsqueda con la secuencia GASOL, y veremos el
resultado así:
regrese al estado anterior de la hoja, ya se describió
previamente.
Ejercicio:
Realiza estos mismos pasos para las otras 5
columnas buscando coincidencias o no, esta hoja tiene muchos datos
y le permitirá evaluar las columnas con datos, y en tu experiencia
puedes hacer esto en una hoja que uses de forma regular...
BÚSQUEDA DE DATOS CON FILTRO AVANZADO
De la forma anterior de búsqueda de datos
seleccionamos algin valor especifico y se filtrara según un
criterio especifico, ahora sera de la forma algo mas interesante
no es difícil, así que comenzamos usando la misma hoja de trabajo
anterior, gasolineras.xlsx.
Para comenzar debemos de decidir que campo debemos de realizar
la búsqueda, en este caso decidimos usar el titulo POBLACION,
pasamos a este titulo y lo copiamos (es importante copiar este
titulo, no lo escribas, no funciona correctamente si lo escribes
de nuevo):
nos desplazamos hasta el final del contenido de los datos pero
deberá ser en la columna A, y pegamos ahi :
debajo de la celda copiada en la columna A, establecemos
el criterio de busqueda en este caso CELAYA
usamos la pestaña Datos, -> Ordenar y filtrar ->
Avanzadas, imagen308.jpg
mostrara un cuadro de dialogo de Filtro avanzado
se activara el Rango de la lista el rango total
de los datos, seleccionamos en la caja de Rango de criterios, y
llevamos a las celdas que hemos copiado de POBLACION (recorremos
hasta la posición, que esta las celdas para búsqueda)
clic en el cuadro con la flecha hacia abajo
seleccionamos copiar a otro lugar
y usando Copiar a: seleccionamos uno o dos celdas debajo del
criterio de busqueda
volvemos al cuando y clic en la flecha hacia abajo, deberá verse
de esta forma:
y aplicamos el botón aceptar, el resultado es este:
Ejercicio:
Deberán de realizar varios procesos de búsquedas debajo del
ejemplo anterior
BÚSQUEDA ESPECIFICA
DE DATOS EN VERTICAL Y HORIZONTAL.
La busqueda especifica de datos es
muy versátil en Excel, se utiliza una función de Excel llanada
BUSCARH y BUSCARV, que significa Búsqueda Horizontal, y Búsqueda
Vertical, ya que realizan en ese sentido las búsquedas de datos,
para hacer esto necesitamos que los datos de la hoja cumplan
ciertos requisitos:
1.- La primera fila de la izquierda de la lista deberá estar
ordenada, ya sea ascendente o descendente, pero es la primer
condición, el orden.
2.- No deberá de haber columnas vacías entre los grupos de
datos, es decir las columnas deberán ser continuas y adyacentes.
3.- El uso de estas funciones requiere que el punto de búsqueda
sea siempre la columna izquierda, ya que de ahí se parte para
relacionar las demás celdas.
NOTA: es indispensable que consideres los puntos
anteriores porque no funcionara si no se aplican estas reglas.
La sintaxis para hacer las búsquedas son:
BUSCARH(valor_buscado,matriz_buscar_en,indicador_filas,ordenado)
Valor buscado.- Es el valor que se
va a localizar en la primer
fila de la matriz de datos. (Siempre se buscara de este
lado, (fila 1) hay que tenerlo en cuenta cuando se forme la matriz
de datos)
Matriz_buscar – Es el rango de
datos (celdas) donde se va hacer la búsqueda y relacionar los
datos, del resultado de la búsqueda, (se incluirá la fila de datos
donde se hace la búsqueda)
Indicador_filas – Es la fila donde
queremos extraer el dato que esta relacionado con la columna de
búsqueda (fila 1), dependerá de la filas usada y consideradas para
relacionar el dato buscado.
Ordenado – Es un parámetro donde
indica si la lista esta ordenada o no, 1= ordenado, 0= no
ordenado, por defecto se toma 1).
BUSCARV(valor_buscado,matriz_buscar_en,indicador_columnas,ordenado)
Valor buscado.- Es el valor que se
va a localizar en la
columna del lado izquierdo de la matriz de datos.
(siempre se buscara de este lado, (columna 1) hay que tenerlo en
cuenta cuando se forme la matriz de datos)
Matriz_buscar – Es el rango de
datos (celdas) donde se va hacer la búsqueda y relacionar los
datos, del resultado de la búsqueda. (se incluirá la columna de
datos donde se hace la búsqueda).
Indicador_filas – Es la fila donde
queremos extraer el dato que esta relacionado con la columna de
búsqueda (columna 1) dependerá de la filas usada y consideradas
para relacionar el dato buscado.
Ordenado – Es un parámetro donde
indica si la lista esta ordenada o no, 1= ordenado, 0= no
ordenado, por defecto se toma 1).
Ejemplo del uso de búsqueda
vertical (BUSCARV) Descargar el libro de trabajo indice_viscosidad.xlsx,
en la celda F12 pondremos la formula para hacer la búsqueda, nos
interesa ubicar una temperatura en particular y que nos regrese el
valor de la viscosidad y la densidad, una característica
importante a tomar en cuenta, es que la función tiene una
aproximación a tomar, cuando un valor no es localizado en la tabla
toma el dato inmediato inferior de la lista como aproximación, si
el dato es el que se encuentre.
Veamos como funciona:
en la celda F12.
=BUSCARV(F10,A11:C43,2) en la celda
F10 estará el valor a localizar en la tabla, por ejemplo el valor
:200 y de la celda A11:C43 es toda la matriz de datos a
usar, incluye la primer columna del valor de búsqueda. 2 es la
columna donde debe de regresar el dato relacionado con el dato a
buscar de la columna 1, en este caso el valor de la viscosidad.
Celda F14
=BUSCARV(F10,A11:C43,3) en la celda
F10 estará el valor a localizar en la tabla, por ejemplo el valor
:200 A11:C43 es toda la matriz de datos a usar, incluye la primer
columna del valor de búsqueda. 3 es la columna donde debe de
regresar el dato relacionado con el dato a buscar de la columna 1,
en este caso el valor de la densidad.
Nuestros resultados serian si usamos 200, en la celda F10:
Viscosidad :1500 Densidad: 0.32
A lo cual podemos comprobar viendo
la tabla y ubicando los valores relacionados con la columna
izquierda de 200. Si cambiamos el valor de la celda F10, por
ejemplo a 132, que no esta en al tabla, buscara el inmediato
inferior a ese dato.
Nuestros resultados serian si
usamos 132, en la celda F10:
Viscosidad :770 Densidad: 0.46
Estos resultado serian iguales si
en lugar de 132, escribiéramos 135, o 139, ya que no están en la
tabla y busca el inmediato inferior al dato buscado, debemos tener
una tabla con mas a detalles (mas datos) para que pueda ser mas
exacta la cifra, esto no sucede con texto que son precisos los
nombres / datos.
Ejercicio:
Descargue el libro de trabajo busqueda_artículos.xlsx
aplique la búsqueda Vertical, para:
Al buscar un artículo, me
proporcione su precio y el inventario inicial
La celda que tendrá el dato de
búsqueda deberá estar en la celda K2
La celda donde estará el precio del
artículo será en la K3
La celda donde estará el valor del
inventario Inicial será en la K4.
De la celda J2 a la J4, coloque el
nombre mas adecuado para cada elemento, a mostrar.
Recuerde los requisitos necesarios
para que funcione correctamente esta búsqueda.
Guarde el libro de trabajo, con el
nombre de busqueda_artículos_final.xlsx
Selección especifica de datos como búsqueda.
La búsqueda especifica de datos
comprende, una herramienta muy simple, y fácil de usar, por
supuesto que necesitamos el conocimiento previo, de cómo se ordena
una lista, y que es una lista de datos, teniendo en cuenta de esto
veremos la forma de búsqueda de datos.
Usaremos de ejemplo el libro de Excel llamado: .
Descarga el libro de trabajo papeleria.xlsx
Ordene la lista, y como referencia debe ser la clave de
articulo, la columna que deberá estar ordenada.
Ya que este ordenada, usar la herramienta Buscar y seleccionar
de la ficha Inicio:
Donde desplegara la lista que se muestra, usaremos la opción
Buscar…
En el cuadro Buscar: ALAMBRE. Y haga clic en el botón Buscar
siguiente
Localizara el primer registro que
contenga la palabra ALAMBRE, en ese lugar se detendrá, si desea
seguir buscando, haga clic, en el mismo botón Buscar siguiente, y
se detendrá en cada celda donde se encuentra la palabra.
Guarde el libro de trabajo en el directorio Mis Documentos o
Documentos con el nombre de papeleria1.xlsx.
La estadística
es un área de la matemáticas que se ocupa de análisis de datos y
su interpretación, aquí un enlace para una descripción de la estadística
y su origen.
Las lista que tiene datos numéricos
son susceptibles de ser analizados, con diferentes métodos, de
análisis estadísticos, algunos de ellos los vinos en la primera
sección de este curso sin embargo realizaremos una breve reseña de
estos, usando una hoja de trabajo de Excel, realizaremos unos
elementos estadísticos:
Máximos
Para recordar lo que se puede hace con ellos:
MAX(número1,número2,...) Devuelve el valor máximo de la lista de
valores.
Ejemplo: =MAX(5,5,2,15,12,18) devuelve 18
Mínimos
MIN(número1,número2,...) Devuelve el valor mínimo de la lista de
valores
Ejemplo: =MIN(5,5,2,15,12,18) devuelve 2
Promedio
PROMEDIO(número1,número2,...) Devuelve la media aritmética de la
lista de valores
Ejemplo: =PROMEDIO(5,5,2) devuelve 4
Varianza
VAR(número1,número2,...) Devuelve la varianza de una lista de
valores.
Ejemplo: =VAR(5,5,2,7,12) devuelve 13.7
Realizaremos las 4 funciones estadísticas que nos ocupan, al
final de la columna C, agregaremos los títulos: Maximo, Minimo,
Promedio, y Varianza usando las celdas C10, C11, C12, C13,
respectivamente.
Y pondremos a su lado derecho, las
funciones que corresponden a cada caso mencionado, usando la
columna D y el rango de 2 a la fila 8, para cada caso.
Realizado esto completar las columnas E, F, G, H siguientes, con
las funciones solicitadas. El resultado que se espera seria el
siguiente:
Esto también se puede hacer de
forma horizontal, esto es posible cuando los datos sean comunes
entre si, en este caso en columnas son similares, en esta hoja no
es posible hacerlo, ya que estaríamos mezclado los diferentes
tipos de datos.
Ahora en este libro de trabajo pase
a la Hoja 2, ahí encontrara, una lista más grande, realice las
mismas funciones, a partir de las celdas C30, D30, E30, F30, G30,
H30.
Al terminar Guarde el libro de
Excel en el Directorio Mis Documentos o Documentos, con el nombre
de claves_articulos_est.xlsx.
GENERACIÓN DE
MENSAJES POR CONDICIÓN, FORMATOS CONDICIONALES.
EL FORMATO CONDICIONAL
El formato condicional sirve para que dependiendo del valor de
la celda, Excel aplique un formato especial o no, sobre esa celda.
El formato condicional suele utilizarse para resaltar errores,
para valores que cumplan una determinada condición, para resaltar
las celdas según el valor contenido en ella, etc...
Cómo aplicar un formato condicional a una celda:
- Seleccionamos la celda a la que vamos a aplicar un formato
condicional.
- Accedemos al menú Formato condicional de la pestaña Inicio.
Aquí tenemos varias opciones, como
resaltar algunas celdas dependiendo de su relación con otras, o
resaltar aquellas celdas que tengan un valor mayor o menor que
otro.
Utiliza las opciones Barras de
datos, Escalas de color y Conjunto de iconos para aplicar diversos
efectos a determinadas celdas.
Nosotros nos fijaremos en la opción
Nueva regla que permite crear una regla personalizada para aplicar
un formato concreto a aquellas celdas que cumplan determinadas
condiciones.
Vemos un cuadro de diálogo Nueva
regla de formato :
En este cuadro seleccionaremos un tipo de regla.
Normalmente queremos que se aplique el formato únicamente a
las celdas que contengan un valor, aunque puedes escoger otro
diferente.
En el marco Editar una descripción de regla deberemos indicar
las condiciones que debe cumplir la celda y de qué forma se
marcará.
De esta forma si nos basamos en el Valor de la celda podemos
escoger entre varias opciones como pueden ser un valor entre un
rango mínimo y máximo, un valor mayor que, un valor menor que y
condiciones de ese estilo.
Los valores de las condiciones pueden ser valores fijos o
celdas que contengan el valor a comparar.
Si pulsamos sobre el botón Estilo de Formato entramos en un
cuadro de diálogo donde podemos escoger el formato con el que se
mostrará la celda cuando la condición se cumpla.
El formato puede modificar, el color de la fuente de la letra,
el estilo, el borde de la celda, el color de fondo de la celda,
etc.
Al pulsar sobre Aceptar se creará la regla y cada celda que
cumpla las condiciones se marcará.
Si el valor incluido en la celda no cumple ninguna de las
condiciones, no se le aplicará ningún formato especial.
Si pulsamos sobre Cancelar, no se aplicarán los cambios
efectuados en el formato condicional.
Ejercicio paso a paso.
Crear formato condicional
Objetivo. Practicar cómo crear un
formato condicional en Excel.
1.- Si no tienes abierto Excel,
ábrelo para realizar el ejercicio.
2.- Abre un nuevo libro de trabajo.
3.- Selecciona la celda A5.
4.- Accede a la pestaña Inicio.
5.- Selecciona el menú Formato
condicional y selecciona la opción Nueva regla. Se mostrara el
cuadro de diálogo Nueva regla de formato. Seleccionaremos el tipo
de regla Aplicar formato únicamente a las celdas que contengan...
6.- En el primer cuadro combinado
escoge la opción Valor de la celda.
7.- En el segundo recuadro
selecciona entre.
8.- En el tercer recuadro escribe
50.
9.- En el último recuadro escribe
250.
10.- Pulsa sobre Formato...
11.- En el cuadro de diálogo
Formato de celdas, en la pestaña Fuente en Estilo selecciona
Negrita cursiva.
12.- En Color selecciona el color
(Color Estandard, en la parte inferior derecha del cuadro de
dialogo Rojo Obscuro.)
13.- En la pestaña Relleno
selecciona el color gris claro (el primer color gris del lado
izquierdo.)
14.- Pulsa el botón Aceptar en los
dos cuadros de diálogo. Vamos a comprobar su funcionamiento.
15.- Sitúate en la celda A5.
17.- Escribe el número 5. La celda
debe quedar tal cual estaba, el valor no cumple ninguna de las dos
condiciones incluidas en el formato condicional.
18.- Ahora escribe en la celda A5
el número 120. Ahora se debe de haber activado el formato
condicional y el número cambiara a negrita cursiva, de color rojo
con fondo gris.
19.- Ahora escribe en la celda A5
el número 300. El número estará de forma normal. Guarda la hoja de
trabajo en Mis Documentos o Documentos, con el nombre de
formato_condicional.xlsx. Ahora eliminaremos el formato
condicional.
20.- Haz clic en la pestaña Inicio.
21.- Accede al menú Formato
condicional.
22.- Selecciona la opción Borrar
reglas. Se abrirá un cuadro de diálogo donde podrás ver todas las
condiciones para borrar reglas. Borrar reglas de las celdas
seleccionadas. Borrar reglas de toda la hoja.
23.- Selecciona Borrar reglas de
las celdas seleccionadas, la regla dejará de actuar.
24.- El número 300 sigue viéndose
con el formato normal, pero si escribes en la celda el número 120,
ya no se verá con el formato, hemos eliminado la condición del
formato.
25.- Cerrar el libro sin guardar
cambios.
Generación de mensajes por condición
Es de extrema utilidad.
Sirve cuando necesitamos que el
resultado de una celda que depende de alguna condición.
La estructura de la función es:
=SI(Prueba_lógica,Valor_si_verdadero,Valor_si_falso)
Veamos cómo funciona con un
ejemplo:
Descarga la hoja de trabajo funcion_si.xlsx, con el detalle
de Ejecutivos de venta, las ventas realizadas así como el objetivo
y queremos ver si cumplió o no con él.
En esta instancia es donde
realizamos la “función_lógica“.
Una función lógica es una
comparación cuyo resultado es Verdadero o Falso. En este caso
necesitamos comparar si las ventas superan (o igualan) al
objetivo. O, alternativamente, si las resta de las ventas – el
objetivo es mayor que cero.
Por lo tanto, la función lógica
será simplemente:
Ventas => Objetivo
En este caso usamos la combinación de signos “>=” para
indicar que el valor de las ventas debe ser mayor o igual.
Igualmente funciona a la inversa “<=”. Menor o igual, para el
caso de “diferente de”, debemos utilizar “<>”.
Si efectivamente las Ventas superan el Objetivo
(Ventas>=Objetivo es VERDADERO).
Queremos que la celda (de la columna D) nos muestre
“Cumplió”.
Y, en caso que no se cumpla (es decir, Ventas>=Objetivo es
FALSO), nos muestre “No cumplió”.
Entonces ya tenemos las tres partes:
La función lógica: Ventas>=Objetivos
Valor si Verdadero: Mostrar “Cumplió”
Valor si Falso: Mostrar “No cumplió”.
=SI(B2>=C2,”Cumplió”,”No Cumplió”)
Es totalmente equivalente escribirla de la forma: =SI(B2<C2,"No
Cumplió","Si Cumplió")
SI las Ventas son menores al Objetivo entonces mostrar “No
Cumplió”, en caso contrario (es decir, si es FALSA la comparación
Ventas<Objetivo), mostrar "Sí Cumplió").
Guarde el libro de trabajo en Mis Documentos o Documentos, con el
mismo nombre.
FUNCIÓN, SI ANIDADA
Se dice que una función está anidada cuando se utiliza una
función dentro de si misma.
Haciendo un poco más complicado el ejemplo anterior, queremos
saber si superó el objetivo y si la diferencia fue mayor de
$5.000. Esto es, tenemos que hacer DOS comparaciones: Si supera el
objetivo y si lo supera por más o menos de $5.000 Empezamos con la
función:
=SI(Ventas<Objetivo) Entonces (caso VERDADERO)
=> “No Cumplió” De lo contrario (caso FALSO)
=SI(Ventas-Objetivo<5000) Entonces (caso
Verdadero) => “Pasó menos de $5,000″
De lo contrario (caso FALSO)=> “Pasó más de 5,000″.
La fórmula completa quedaría para el ejemplo anterior:
=SI(B2<C2,"No Cumplió",SI(B2-C2<5000,"Pasó menos de
$5,000","Pasó más de $5,000"))
Observese que el segundo argumento tiene la misma función si( ),
aquí no es necesario poner el signo "=" porque ya esta establecido
al inicio de la formula.
Ejercicio:
Instrucciones: En la columna
status, (columna I) establecer por medio de la función =SI( ) Un
mensaje que diga Aprobado o Reprobado cuando el valor de la celda
anterior si el promedio sea mayor o igual a 80 es Aprobado, de lo
contrario, Reprobado cuando sea menor de 80.
En la columna Oportunidad, si esta
reprobado, tiene oportunidad de presentar de nuevo el examen,
siempre y cuando, tenga un rango de 60 a 80 en su promedio, poner
si hay oportunidad de examen colocando en la celda la palabras si
hay oportunidad de examen, si no hay esa oportunidad colocar el
mensaje "repetir curso".
Guarde el libro de trabajo en el directorio Mis Documentos o
Documentos
Las macros de Excel nos permiten
automatizar tareas que realizamos cotidianamente de manera que
podamos ser más eficientes en nuestro trabajo.
Una macro es una serie de comandos
o instrucciones que permanecen almacenados dentro de Excel y que
podemos ejecutar cuando sea necesario y cuantas veces lo deseemos.
Por ejemplo, si todas las mañanas
creas un reporte de ventas y en ese reporte siempre das el mismo
formato a los textos, se podría crear una macro para que lo haga
automáticamente por ti. Las macros se utilizan principalmente para
eliminar la necesidad de repetir los pasos de aquellas tareas que
realizas una y otra vez.
Las macros se escriben en un
lenguaje de computadora especial que es conocido como Visual Basic
for Applications (VBA). Este lenguaje permite acceder a
prácticamente todas las funcionalidades de Excel y con ello
también ampliar la funcionalidad del programa.
Si quieres escribir una nueva macro
o ejecutar una macro previamente creada, entonces debes habilitar
la ficha Desarrollador dentro de la cinta de opciones. Para
mostrar esta ficha sigue estos pasos:
Haz clic en la ficha Archivo y
elige la sección Opciones.
Se mostrará el cuadro de diálogo
Opciones de Excel donde deberás seleccionar la opción Personalizar
cinta de opciones.
Haz un clic en el botón Aceptar, al regresar a la hoja de
trabajo de excel veras una nueva ficha en la parte superior :
Desarrollador
El grupo Código tienes los comandos
necesarios para iniciar el Editor de Visual Basic donde se puede
escribir directamente código VBA. También nos permitirá ver la
lista de macros disponibles para poder ejecutarlas o eliminarlas.
Y no podríamos olvidar mencionar que en este grupo se encuentra el
comando el cual nos permite crear una macro sin necesidad de saber
sobre programación en VBA.
El grupo Complementos nos permite
administrar y habilitar complementos como el Utilizando Excel
Solver
El grupo Controles incluye
funcionalidad para agregar controles especiales a las hojas de
Excel como los controles de formulario que son botones, casillas
de verificación, botones de opción entre otros más que serán de
gran utilidad para ampliar la funcionalidad de Excel.
El grupo XML permite importar datos
de un archivo XML así como opciones útiles para codificar archivos
XML.
Finalmente el grupo Modificar
solamente contiene el comando Panel de documentos. Aunque pueden
parecer intimidantes los comandos de la ficha Programador con el
paso del tiempo te irás familiarizando poco a poco con cada uno de
ellos.
La grabadora de macros
Puedes crear una macro utilizando
el lenguaje de programación VBA, pero el método más sencillo es
utilizar la grabadora de macros que guardará todos los pasos
realizados para ejecutarlos posteriormente.
La grabadora de macros almacena
cada acción que se realiza en Excel, por eso es conveniente
planear con antelación los pasos a seguir de manera que no se
realicen acciones innecesarias mientras se realiza la grabación.
Para utilizar la grabadora de macros debes ir a la ficha
Programador y seleccionar el comando Grabar macro.
Al pulsar el botón se mostrará el cuadro de diálogo Grabar macro
En el cuadro de texto Nombre de la
macro deberás colocar el nombre que identificará de manera única a
la macro que estamos por crear.
De manera opcional puedes asignar
un método abreviado de teclado el cual permitirá ejecutar la macro
con la combinación de teclas especificadas.
La lista de opciones Guardar macro
en permite seleccionar la ubicación donde se almacenará la macro.
Este libro. Guarda la macro en el
libro actual.
Libro nuevo. La macro se guarda en
un libro nuevo y que pueden ser ejecutadas en cualquier libro
creado durante la sesión actual de Excel.
Libro de macros personal. Esta
opción permite utilizar la macro en cualquier momento sin importar
el libro de Excel que se esté utilizando.
También puedes colocar una
Descripción para la macro que vas a crear.
Finalmente debes pulsar el botón
Aceptar para iniciar con la grabación de la macro.
Al terminar de ejecutar las
acciones planeadas deberás pulsar el botón Detener grabación para
completar la macro.
Se mostrará cómo crear una macro en
Excel utilizando la grabadora de macros.
La macro será un ejemplo muy
sencillo pero permitirá ilustrar el proceso básico de creación.
Se creara una macro que siempre
introduzca el nombre de tres departamentos de una empresa y
posteriormente aplique un formato especial al texto.
Para iniciar la grabación debes ir
al comando Grabar macro que se encuentra en la ficha Programador
lo cual mostrará el siguiente cuadro de diálogo.
Observa se ha colocado un nombre a
la macro y además he especificado el método abreviado CTRL+d para
ejecutarla posteriormente. Una vez que se pulsa el botón Aceptar
se iniciará la grabación.
Al terminar los pasos se pulsa el comando Detener grabación y la
macro habrá quedado guardada. Para ejecutar la macro recién
guardada seleccionaré una nueva hoja de Excel y seleccionaré el
comando Macros.
Al pulsar el comando Macros se
mostrará la lista de todas las macros existentes y de las cuales
podrás elegir la más conveniente. Al hacer clic sobre el comando
Ejecutar se realizarán todas las acciones almacenadas en la macro
y obtendrás el resultado esperado. Por supuesto que si utilizas el
método abreviado de teclado de la macro entonces se omitirá este
último cuadro de diálogo.
Una manera muy interesante de
descubrir y aprender más sobre código VBA es analizar el código
generado por la Grabadora de macros. Para este ejemplo grabaremos
una macro muy sencilla que solamente cambie el color de la fuente
de la celda actual.
Para comenzar debemos ir a la ficha Programador y pulsar el
comando Grabar macro lo cual mostrará el cuadro de diálogo donde
asignaré un nombre a la macro.
Pulsa el botón Aceptar y se
comenzarán a grabar todas las acciones, así que debes actuar con
cuidado porque se grabará absolutamente todo.
Para la macro que estoy grabando
solo haré lo siguiente: iré a la ficha Inicio y pulsaré el comando
Color de fuente y seleccionaré el color rojo para la celda activa.
Una vez hecho esto debo detener la
grabación de la macro y una alternativa para hacerlo es pulsar el
icono que se muestra en la barra de estado.
Ahora que ya hemos generado la
macro, pulsa el botón Macros que se encuentra en el grupo Código
de la ficha Desarrollador. Se mostrará el cuadro de diálogo Macro
que en-lista todas las macros que hemos creado.
Selecciona la macro recién creada y
pulsa el botón Modificar. Esto abrirá el Editor de Visual Basic y
mostrará el código generado para la macro.
Observando este código podemos
aprender varias cosas.
Para empezar observamos que el
objeto Selection tiene una propiedad llamada Font que es la que
hace referencia a la fuente de la celda o rango seleccionado. A su
vez, la propiedad Font tiene otra propiedad llamada Color que es
precisamente la que define el color rojo de nuestra celda.
Aunque este ha sido un ejercicio
muy sencillo, cuando tengas curiosidad o duda sobre qué objetos
utilizar al programar en VBA considera utilizar la Grabadora de
macros para darte una idea del camino a seguir.
Haremos un ejemplo de macro con un botón en la hoja para ordenar
una lista:
EJEMPLO DE CREACIÓN DE BOTONES USANDO MACROS
Usamos la hoja: busqueda_articulos.xlsx que previamente se
descargo
Creamos dos macros para ordenar dos columnas diferentes en la
hoja.
1.- Para ordenar la Clave (Macro1)
2.- Para ordenar Descripción (Macro2)
Las macros guardadas quedaran asi:
Verificamos o Activamos el menú desarrollador:
Archivo -> Opciones:
y personalizar cinta de opciones, y del lado derecho
Desarrollador :
El resultado será en Excel:
Se activa el modo diseño (clic sobre el icono se verá un fondo
de color gris )
Después sobre el icono Insertar, en el cuadro inferior
(Controles ActiveX) la primer figura de la izquierda “Botón”).
a lo cual se mostrará un cursor
pequeño con el cual dibujamos un cuadro en un área independiente
de los datos en la hoja de cálculo, se mostrara un cuadro donde se
establecerá el botón que se asocia con el macro creado
anteriormente.
Observamos que en la barra de
fórmulas esta una instrucción para agregar el botón (Incrustar).
Sobre el objeto creado hacemos un clic con el botón derecho
mostrándose un menú contextual, y de este seleccionamos la opción
de Ver código:
Aquí se encuentra la sección donde
se agregará el código de la macro formada en el inicio, para ello
seleccionamos del lado izquierdo la carpeta llamada Módulos,
seleccionarlo y hacer doble clic con botón izquierdo.
En donde se encuentra modulo1 se
encontrarán las 2 macros creadas desde el inicio. Al abrirlo se
observará el código del macro en color negro seleccionamos esto y
lo copiamos dentro del área de código del botón:
Se copia la selección y se pega en la sección del botón:
Cerramos la ventana completa queda almacenado, regresamos a la
hoja de cálculo,
Volvemos a seleccionar el cuadro para el botón y hacemos clic
con el botón derecho y seleccionamos Propiedades:
lo que nos lleva a:
En la casilla Caption escribimos el nombre que tendrá el botón
en la hoja de cálculo.
Podemos cambiar el aspecto del botón, usando ForeColor (color
del frente):
Así como algún efecto de sombra (shadow pasar de False a True):
También el color del mismo botón se cambia con BackColor:
Y solo cerramos el cuadro de propiedades
Ahora desactivamos el icono de modo diseño, y quedara listo el
botón para ordenar la lista que se realizó con el macro
Te toca a ti hacer el otro botón para ordenar Descripción.
La consideración final es cuando guardes tu trabajo con Macros
debemos guardarlo con una extensión diferente y es Libro de Excel
habilitado con Macros .xlsm, esto se realiza en Archivo Guardar
como... y debajo del nombre que se asigna a la hoja de trabajo,
esta una sección llamada tipo es donde deberás seleccionar el tipo
de archivo a guardar (.xlsm)
