excel intermedio
TRANSCRIPT
CLASE DE EXCEL
FUNCIONES
CONTROL + ESCAbre inicio, tecla de bandera
TECLA BANDERA + RSe puede abrir todos los programas del sistema (winword, powerpoint, Excel)
ALT + TABULADOR TECLA BANDERA + TABULADORBuscar aplicacin
TECLA BANDERA + DLlevar al escritorio
TECLA BANDERA + MMinimizar
TECLA BANDERA + LCerrar
ALT + BARRA ESPACIADORAAbre men de control
ALT + [F4]Cerrar aplicaciones, apagar el computador
Shif () + [F11]Hoja nueva
CONTROL + [F11]Hoja nueva macro
[F11]Hoja nueva grafico
ALT + [F11]Abre Visual Basic
CONTROL + tecla [AvPag] [RePag]Pasar de una hoja a otra CONTROL + CONTROL + Ultima Celda Primera Celda
CONTROL + [inicio]Se sita en primera celda A1
[F5] CONTROL + iIr a una celda especifica
[F8] Shif () + LAS FLECHASSeleccionar celdas
[Bloq Desp]Bloquear o desbloquear lo seleccionado
[F10] ALTLeer el men
[F2]Editar la celda
[F5]Seleccionar un rango especfico
Shif () + BARRA ESPACIADORASelecciona fila
CONTROL + BARRA ESPACIADORASelecciona columna
CONTROL + [+] y CONTROL + [-]Insertar Eliminar
CONTROL + [F4]Cerrar libro
CONTROL + ULibro nuevo
CONTROL + ENTERCopiar celda
CONTROL + *Seleccionar CONTROL + 1Formato celdas
Un libro de Excel tiene 255 hojas
COMO INSERTAR UN MAYOR # DE HOJAS EN UN NUEVO LIBRO1. Archivo - opciones2. Categora general3. Opcin incluir este # de hojas y digitar la cantidad que se requiera4. Crear un nuevo libro o archivo (CONTROL + U)
HOJA DE CALCULO, CUDRICULA COMPUESTA POR 1.04.576 FILAS Y 16.384 COPLUMNAS (A-XFD)
[Fin] + flecha hacia abajoltima fila[Fin] + flecha hacia derechaltima columnaCONTROL + [Inicio]Celda 1
COMO CAMBIAR LAS REFERENCIAS DE LAS COLUMNAS DE LETRAS A # VICEVERSA1. Archivo opciones2. Categora formulas3. Activar la opcin estilo de referencia F1C1
PARA IR DIRECTAMENTE A UNA CELDA, SE PRESIONA LA TECLA [F5] SE DIGITA LA REFERENCIA EN EL CUADRO DE NOMBRE
COMO MOSTRAR EN LAS CELDAS LAS FORMULAS EN LUGAR QUE RESULTADOS1. Click ficha formulas2. Click icono mostrar formulas
PARA CREAR UNA LISTA CONSECUTIVA DE #, SE DEBE PRESIONAR LA TECLA CONTROL SOSTENIDA AL MOMENTO DE REALIZAR EL AUTOLLENADO
EJERCICIOHacer una lista en 100 con incremento de 20 y termina en 400Se digita los dos primeros #s, osea 100 y 120 y luego se hace autollenado
LISTAS PERSONALIZADAS1. Ficha archivo opciones2. Avanzadas3. Click en la opcin modificar listas personalizadas4. Ubicarse en entrada de lista y digitar los elementos que conformaron la lista5. Agregar y aceptar
NOTASi la lista ya existe, se puede importar as:1. Seleccionar la lista2. Ficha archivo opciones avanzadas3. Modificar listas personalizada, click en importar
TRABAJO CON HOJAS1. RENOMBRAR: Doble click en la etiqueta
2. MOVER: Click sostenido sobre la etiqueta
3. DUPLICAR: Hoja en el mismo libro. Click sostenido y arrastrar la hoja desde la etiqueta
4. MOVER HOJAS A OTROS LIBROS: Click derecho sobre la etiqueta, mover o copiar al libro, elegir el archivo o nuevo libro
5. COPIAR HOJAS EN OTROS LIBROS: Se debe seguir el mismo procedimiento anterior, pero se debe activar la opcin Crear una copia.
COMO REPETIR UN DATO EN VARIAS CELDAS A LA VEZ1. Seleccionar los rangos que se requieran presionando la tecla CONTROL SOSTENIDA2. Digitar el dato que se requiera3. CONTROL + ENTER
COMO CAMBIAR LA DIRECCION DE UNA TECLA ENTER1. Ficha archivo opciones2. Categora avanzadas3. Ubicarse en la opcin direccin y elegir la que se requiera
Vista preliminar CONTROL + P Click en configurar pgina (parte inferior) Para sar de vista preliminar: ESC
COMO AJUSTAR LA INFORMACIN A UN NUMERO DETERMINADO DE PAGINAS1. CONTROL + P2. Configurar pagina3. Ubicarse en la opcin o ajustar de ancho o de alto
COMO CREAR SALTOS DE PGINADeben estar activa la opcin ajustar al 100% del tamao normal1. Ubicarse en la columna A y la fila que se requiera2. Ficha diseo de pagina3. Click en el icono salto insertar saltoNOTA: Para eliminar un salto se debe ubicar el puntero en la celda donde fue creado y click - eliminar salto Para eliminar todos los saltos click en saltos restablecer todos los saltos
COMO REPETIR LOS TITULOS CUANDO SE TIENEN VARIAS PAGINAS1. Ficha diseo de pagina2. Icono imprimir ttulos3. Ubicarse en la opcin: repetir filas en extremo superior Hacer click y elegir los ttulos de la planilla
AREAS DE IMPRESIN1. Seleccionar los rangos de celdas que se deben imprimir (presiona CONTROL SOSTENIDA)2. Click en la ficha diseo de pagina3. Click en el icono rea de impresin y elegir establecer rea de impresinNOTA:Una vez se imprima, se debe borrar el rea de impresinSiempre debe ser mayor el margen superior que el margen del encabezadoEjemplo: Superior 4cm, encabezado 2cm
INMOVILIZAR PANELES1. Ficha vista2. Click en el icono inmovilizar y elegir fila superior (si se requiere inmovilizar los ttulos)
QUE SALGA FECHA Y HORA EN UNA CELDA=hoy()fecha del sistema=ahora()fecha del sistema y hora
CLASE DEL 22 DE OCTUBRE/2014TEMAS A TRATAR EN EL CURSOFORMULAS Directas - Indirectas Absolutas Relativas Mixtas Funciones bsicas Trabajo con varios hojas de clculos Funciones SI Sencillo y SI Anidado Conectores Y-O Auditorias de Frmulas Funcin Buscar Grficos
MANEJO DE DATOS Ordenar Filtros sencillos y avanzados Importar datos Formato condicional Quitar duplicados Validacin de datos Consolidar Subtotales Buscar objetivos Tablas dinmicas y grficos dinmicos ( con una variable, con 2 o mas variables, porcentuales y frmulas)
REFERENCIAS ABSOLUTASSe utilizan cuando se hace necesario inmovilizar una celda, se inmoviliza presionando la tecla [F4]Ejemplo: $B$1
REFERENCIAS MIXTASSe utilizan y se hace necesario inmovilizar toda la columna o toda la filaEjemplo: $B$1Inmoviliza la celda $BInmoviliza columna B$1Inmoviliza fila
PERSONALIZAR DATOSNOTA: Para abrir la caja de dialogo de formato de celdas se presionan las teclasCONTROL + 1El #1 del teclado alfanumrico
Como Cambiar la configuracin del (.) para miles y (,) para decimales desde Excel. 1. Ficha archivo opciones2. Avanzadas3. Desactivar la opcin Usar separadores del sistema y digitar los separadores correspondientesDecimales (,)Miles (.)NOTA: Al digitar una flecha con el formato DD/MM/AAAA, esta se tiene que ubicar al lado derecho de la celda si aparece al lado izquierdo, se debe cambiar as:1. Click sobre el reloj2. Click opcin cambiar configuracin de fecha y hora3. Click en el botn cambiar fecha y hora4. Click en la opcin cambiar configuracin del calendario5. Ubicarse en la opcin fecha corta y elegir el formato DD/MM/AAAA6. Borrar la fecha y digitar nuevamente.Si quiero saber el da de la semana que nac, as: Se digita la fecha de nacimiento (dia/mes/ao) Control + 1 Fecha personalizar Tipo se borra y se digita dddd (4d) y ENTER
PERSONALIZAR UN NUMERO TELEFONICO Se para en la celda donde est el nmero telefnico CONTROL + 1 Categora personalizada Tipo: Se borra lo que aparece Se digita como quiere que le aparezcaEjemplo: 3008289769 (###) ###-##-## y ACEPTARQuedara as (300) 828-97-69
PERSONALIZAR UNA FECHA
Se digita la fecha militar CONTROL + 1 Categora hora Configuracin regional (ubicacin): Espaol (Espaa) Aceptar
TALLER 1
FORMULA INCREMENTOV * % + V V * 115%
FORMULA DECREMENTOV V * % V * 85%
CLASE DEL 24 DE OCTUBRE/2014
FORMULA = Dias360(fecha_inicial;fecha_final)Calcula la diferencia de das que hay entre dos fechas basando en un ao de 360 das.
NOTA:Se requiere contar el da en que se ingresa a la empresa, se debe sumar +1
NOTA:Para activar en la barra de estado todas las funciones (max, min, prom, recuento, contar), se debe hacer click derecho sobre la barra inferior y activarlas (barra de estado).Para visualizar los resultados, se selecciona el rango que se requiera.
FUNCION CONTAR RECUENTO NUMERICO:Se utilizar para contar nmeros
FUNCION CONTAR RECUENTO NUMERICO:Se utiliza para contar textos alfabticos o alfanumricos
=Promedio (L6:L11)L6 desde, Consecutivos y L11 hasta
Para aplicar cualquier funcin con datos no consecutivos, se debe seleccionar la funcin y con la tecla CONTROL sostenida se selecciona los datos.
TRABAJO EN DIFERENTES HOJAS DE CALCULOS
1. Antes de pasar a la otra hoja se debe digitar el operador que correspondaEjemplo: A3*
2. Click sobre la hoja que se requiera y click sobre la celda3. Si no es necesario devolverse a otra hojay ya termin la formula, se debe presionar la tecla ENTER.
CLASE DEL 31 DE OCTUBRE/2014
FUNCION SI SENCILLOEvala una condicin, devuelve una respuesta por el verdadero y una respuesta por el falsoSINTAXIS: =Si(condicin;verdadero;falso)
La condicin siempre tiene 3 partes:1. Por lo general una celda2. Operador de comparacin3. Celda valor lista caracteres
>, =, Texto alfabtico o alfanumrico
Verdadero falso: Puede ser celda, formula, valor lista caracteres
FUNCION SI ANIDADOEs la misma funcin SI, pero con ms alternativas de respuestas porque permite evaluar hasta 64 condiciones.NOTA:No es necesario hace la ltima pregunta, se debe digitar la respuesta que ser el xxxx de la condicin anterior.=si(cond1;verd;si(cond2;verd;si(cond3;verdsi(cond63;verd;falso))))
CLASE DEL 5 DE NOVIEMBRE/2014
CONECTORES Y O
CONCECTOR YDevuelve una respuesta por el verdadero, solo cuando se cumplan todas las condiciones, sino se cumple por lo menos una de ellas, la respuesta ser por el falso.SI SENCILLO =si(Y(cond1cond2010);verd;si(y(cond1cond210);verd;falso))
SI ANIDADO =si(y(cond1cond210);verd;si(y(cond1cond210);verd;falso))
CLASE DEL 7 DE NOVIEMBRE/2014
FUNCIONES CONCATENARPermite unir celdasEjemplo: ABC Juanmarin =concatener(A1; ;B1)
TODO EN MAYUSCULA Mayusc(texto) Permite convertir todo el texto en mayscula
NomPropio(texto)Permite cambiar el texto a mayscula inicialSeleccionar celda
Minus(texto)Permite cambiar el texto en minsculaSeleccionar celda
CLASE DEL 12 DE NOVIEMBRE/2014
COMO DIVIDIR UN TEXTO EN VARIAS COLUMNAS1. Seleccionar la columna que se desea separar2. Click en la ficha datos3. Click en el icono texto en columnas, elegir delimitados y click en siguiente4. Elegir el separador que se requiere y finalizar
VALIDARSe validan los datos para aplicar restricciones en el ingreso de ellos1. Seleccionar el rango que se desea validar2. Click en la ficha datos validacin de datos3. Ubicarse en la opcin permitir de la ficha configuracin y elegir nmeros enteros, o decimal, lista, fechas, horas, entre otros4. Crear el mensaje de entrada y el mensaje de errorFUNCION BUSCAR VPermite buscar un valor especfico, en una base de datos en forma verticalPara trabajar esta funcin, se debe tener en cuenta lo siguiente:1. Renombrar la base de datos, seleccionando esta informacin desde la cabecera de las columnas2. Click en las fichas formular y digitar el nombre para esta base de datos3. Ubicarse en la hoja en la que se aplicar buscar V, en la celda donde traern los cdigos, click en la ficha datos, click en el icono validacin de datos, click en la opcin permitir y elegir lista, ubicarse en la opcin origen y seleccionar los cdigos que estn en la hoja.NOTASe deben seleccionar celdas en blanco para agregar luego los registros.SINTAXIS=buscarV(valorbuscado;matriz_buscaren;indicador_columnas;ordenado)
VALOR BUSCADOEs la celda donde se digita el cdigo o donde se crea la lista para traer los cdigos
MATRIZ_BUSCAR ENEs toda la base de datos (recordar que se asign un nombre a la lista)
INDICADOR_COLUMNASEs el nmero de la columna que contiene el dato que se debe mostrar
ORDENADOSe debe digitar falso; si los cdigos no estn consecutivos
NOTASi no se digita cdigo aparece #NA en la celda donde se aplic la funcin, para que no aparezca este texto se debe:a. Ubicarse en la celda donde se hizo la formula buscarb. Ubicarse al comienzo de esta frmula y digitar si.error(buscar(); )BUSCAR HSe utiliza para buscar de forma horizontal
CLASE DEL 19 DE NOVIEMBRE/2014
MANEJO DE DATOS
1. ORDENAR: POR UNA SOLA COLUMNANOTA:Nunca se debe seleccionar la columna por la cual se requiere ordenar
EJEMPLO:Ordenar la base de datos por ciudad en forma ascendentea. Ubicarse en cualquier ciudadb. Click en la ficha datos y elegir ascendente o descendente
2. ORDENAR POR VARIOS CRITERIOS (MAXIMO 64 CONDICIONES)
a. Ubicarse en cualquier celda de la base de datosb. Ir a la ficha de datos, click en el icono ordenarc. Click en el iconod. Click en el botn
FILTROS SENCILLOSNOTA:Se debe ubicar en los ttulos para aplicar los filtrosa. Ubicarse en el ttulo de la base de datos (en cualquier celda)b. Ficha de datosc. Click en el icono
NOTA:Para eliminar todos los filtros, hacer click en el icono filtro
CLASE DEL 21 DE NOVIEMBRE/2014
FILTROS AVANZADOSPermite traer a una nueva hoja los datos que se requiera.Los filtros avanzados maneja el conector Y, cuando los criterios se digitan en forma horizontal.El conector O, cuando los criterios se digitan en forma vertical.
PASOS1. Asignar nombre a toda la base de datos2. Insertar una nueva hoja3. En esta nueva hoja, copiar los campos o ttulos de los datos que se quieren traer de la base de datos, adems, pegar los criterios o condiciones4. Ubicarse en la ficha Datos y avanzados5. Aparece ventana, seleccionar copiar a otro lugarRango de la lista: Click y digitar el nombre que se asign a la base de datosRANGO DE CRITERIOS: Seleccionar todas las condiciones (incluyendo los ttulos)COPIAR A: Click y seleccionar los campos o ttulos que se copiaron
EJERCICIOS1. Mostrar todos los hombres casados de Medelln2. Mostar todos los datos de la ciudad de Cartagena, Cal o Barranquilla3. Mostar todos los datos de los hombres que vivan en Medelln o en Bogot4. Mostrar todos los datos cuyas fechas de registro estn entre 15/01/2009 y 10/04/2009Fecha registroFecha Registro =15/01/2009 CRITERIOS5. Mostrar las mujeres casadas o solteros cuyas ventas estn entre 1.000.000 y 5.000.000
FILTROS CON MACROS
1. Habilitar la ficha desarrollador o programador, as:a. Click derecho sobre un rea libre de la cinta de opcionesb. Elegir personalizar cinta de opcionesc. Activar la opcin Desarrollador
2. Guardar el archivo habilitado para macrosPresionar F12 o guardar comoEn la opcin tipo: Elegir libro Excel habilitado para macros (x15m)3. Insertar nueva hoja4. Pegar en la nueva hoja, el ttulo de los campos que se desean traer los criterios y condiciones5. Asignar nombre a la base de datos6. Click en la ficha desarrollador, click en grabar macro (sin espacios, sin maysculas y sin ttulos)
Ejercicio:Mostrar todas las mujeres de Medelln
7. Aplicar el filtro avanzado y click en la ficha desarrollador, detener grabacin
NOTA: Para eliminar una macro se debe hacer click desarrollador, click en el icono macros, seleccionar y eliminar
8. Crear un botn para asignarle la macro, as:
a. Click en la ficha desarrolladorb. Click en el icono insertarc. Ubicarse en controles de formulario y elegir el primer icono botn d. Dibujarlo o hacer click sobre la nueva hoja donde se cre el filtroe. De la ventana que aparece, hacer click en el nombre de la macro. Ejm: filtro1f. Digitar un nombre en el botn. Ejm: FILTRO1
CLASE DEL 26 DE NOVIEMBRE/2014
Base de datos para subtotales, formatos condicionales, grficos.
BASE DE DATOSPermite crear un resumen de la informacin, aplicando determinadas funcionesEJEMPLO:Mostrar el promedio de las ventas por cada cambio de ciudadPASOS:1. Ordenar la base de datos por el campo que se requiere. Para el ejemplo: ordenarla por ciudad2. Ir a ficha de datos y click en el icono subtotal3. Aparece un cuadro Para cada cambio en :Seleccionar el campo por el cual se orden la base de datos Usar funcin: Seleccionar la funcin que se requiera Agregar subtotal a:Seleccionar el campo al cual se aplicar la funcin segn ejemplo: ventas.
COMO AGREGAR OTRAS FUNCIONES AL SUBTOTAL1. Ubicarse sobre el informe con el subtotal2. Click icono subtotal3. Desactivar la opcin reemplazar subtotales actuales4. Buscar la nueva funcin y aplicarla al campo que se requieraNOTA: Para eliminar los subtotales se da click en quitar todos.EJEMPLO:Mostrar el mximo de las ventas y el minimo de la edad por cada cambio de sexo, adems mostrar el promedio de la comisin por cada cambio de estado civilNOTAS:1. Se debe ordenas la base de datos por los dos campos que se requieren, para el ejemplo: por sexo y estado civil2. Para imprimir estos subtotales de manera independiente se debe hacer click en el icono subtotales y activar la opcin salto de pgina entre grupos.
FORMATO CONDICIONALPermite aplicar unos formatos especficos a un rango de celdas que cumplan con las condiciones:1. Seleccionar la columna a la cual se aplicar el formato2. Ir a ficha inicio, elegir formato condicional3. Elegir la opcin que se requiera
COMO CREAR REGLAS PARA EL FORMATO CONDICIONALEJEMPLO: Relleno amarillo a las ciudades que sean Medelln, relleno azul a Barranquilla, relleno rojo a Bogot y verde a Cartagena1. Seleccionar la columna para el ejemplo la ciudad2. Ficha inicio - formato condicional3. Click en la opcin administrar reglas4. Click icono nueva reglaNOTA: Se la base de datos no requiere ser ordenada, se puede seleccionar el dato al cual se aplicar el formato, de lo contrario el dato se debe digitar5. Elegir aplicar formato nicamente a las celdas que contengan
Elegir texto especifico digitar el dato a seleccionar de la base de datos
Click en el formato y elegir lo que se requiera
Click aceptar y aceptar
COMO MODIFICAR LOS FORMATOS QUE YA FUERON APLICADOS
NOTA: Se debe seleccionar la columna, administrar reglas, click sobre la regla que se va a modificar y click en el icono editar regla
COMO APLICAR FORMATO CONDICIONAL CON SEMAFOROS O CONJUNTO DE ICONOSEJEMPLO: Aplicar semforo verde a las ventas superiores a $3.000.000, amarillo a las ventas que estn $1.000.000 y $3.000.000 y rojo a las ventas inferiores a $1.000.000.
1. Seleccionar la columna que se requiera para el ejemplo ventas2. Click en inicio formato condicional3. Ubicarse en conjunto de iconos y elegir el semforo que se requiera4. Click formato condicional, elegir administrar reglas5. Seleccionar las reglas a modificar y click en editar regla6. Ubicarse en la opcin tipo y elegir nmero para el ejemplo7. Digitar los valores que se requierenEJEMPLO: Descuentos superiores o igual a $150.000 verde, entre $145.000 y $150.000 amarillo y rojo inferiores a $450.000