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.