Post on 26-Dec-2015
description
MICROSOFT EXCEL 2010LUISSOLARIP@GMAIL.COM CELULAR: 91672977DOCENTE INGENIERÍA CIVIL INDUSTRIAL UVMCONSULTOR PROCESOS MODELOS BPMN, DIAGNÓSTICO, COSTEO ABC
-Qué es una hoja de cálculo?-Para qué se utilizan?-Por qué 2010, si ya existe la 2013? => Vers. 2010 es la más utilizada
HOJAS DE CÁLCULO I
Unidad mínima de información => Celda Celda => Posee coordenadas
Fila => Se utilizan números Columna => Se utilizan letras
Se nombran por (Columna)(Fila) => B4 Dentro de una celda podemos encontrar:
Texto Números Fechas fórmulas
HOJAS DE CÁLCULO
Fórmulas => Operación realizada con datos de otras celdas y cuyo resultado se muestra en la celda
Toda fórmula comienza con un “=”, pero podrían utilizarse también “+”, “-”
Principalmente se utilizan MS Excel (pagada) y Calc de Open Office (es gratis) http://www.openoffice.org/es/)
MS EXCEL
Libro de trabajo es un archivo MS Excel => Contiene varias hojas
Para ejecutar Excel :1. Start=>MS Office=>MS Excel2. Acceso directo sobre Desktop
Para Salir de Excel:1. Menú File => Exit2. Botón de cerrar X de ventana
COMPONENTES DE EXCEL
Cuadro de nombres
Barra título
Barra de herramientas de acceso rápido
Barra de fórmulas
ZoomBarra de estado
Etiquetas de Hojas
Zona de pestañas
Minimizar, Maximizar / restaurar, Cerrar
Herramientas de pestaña
Barra de desplazamiento vertical y horizontal
Botones de desplazamiento libro
Cabeceras de fila
Selección libro completo
Hoja de cálculo
TRUCOS EN EXCEL
Modo de acceso por teclas: Pulsar tecla AltÞ Aparecen pequeños recuadros junto a pestañas
y opciones indicando la tecla (o conjunto de teclas) que deberás pulsar para acceder a esa opción sin mouse
Þ Se sale pulsando tecla Alt Doble clic sobre pestañas=>Barra se
oculta para ganar más espacio. Haciendo clic sobre una pestaña vuelven a aparecer
Ejercicio: pruebe lo anterior
INTRODUCIR DATOS EN EXCEL I
Situar el curso sobre la celda donde se van a introducir los datos y teclear datos
Lo ingresado aparece en dos lados: Celda activa y Barra de Fórmulas
Para introducir datos puedes realizar…. Hacer clic sobre el cuadro de aceptación de la barra de
fórmulas (celda activara seguirá donde mismo estaba) Presionar Return (celda activa cambia de lugar) Teclas de movimiento (celda activara cambia de lugar) Hacer clic sobre la X de la barra de Fórmula Presionar Esc
Ejercicio: Pruebe lo anterior
INTRODUCIR DATOS EN EXCEL II
Return => cambia de celda y baja una línea
Alt + Return => Baja una línea, pero no cambia de celda, pudiendo ingresar datos en diferentes filas
Ejercicio: Pruebe ingresar “Hola y Saludos” de las dos formas antes mencionadas
MODIFICAR DATOS
Si aún no se ha validado la introducción de datos y se comete un error utilice tecla de Retroceso para borrar el carácter
Editar celda ya ingresada: Seleccione la celda adecuada y presione F2 o ir a barra de fórmula o doble clic sobre celda. Barra de estado cambia de listo a modificar
Se puede escribir directamente sobre la celda y sobreescribir su contenido
Ejercicio: Realice lo que se mencionó anteriormente
DESPLAZAMIENTOS HOJA
CTRL + Inicio = Comienzo Hoja CTRL + END = Fin Hoja CTRL + Flecha arriba = Margen superior región
datos CTRL + Flecha abajo = Margen inferior región
datos CTRL + Flecha izquierda= Margen izquierda
región datos CTRL + Flecha derecha= Margen derecho región
datos Ejercicio: Pruebe lo anterior
SELEC
Seleccionar un rango: Ctr+Shift+*
TIPOS DE DATOS
Caracteres: Letras o símbolos: ‘hola Numéricos: Enteros o reales Lógicos: Verdadero o falso Fechas Fórmulas: comienzan con =, + o - Ejercicio: Ingrese cada uno de estos
tipos de datos
INGRESAR FÓRMULA
Se ingresa = (o también +, -) Se hace clic sobre primera celda Se agrega operador Se hace clic sobre segunda celda Y se presiona Enter para que se
resuelva la fórmula
EJERCICIO
Ingresar una columna con cabecera de columna x y una serie del 1 al 10 con bajo ella
Ingresar una columna adyacente con cabecera de columna con nombre “y1” con la fórmula x^3-2*x+1
Ingresar una segunda columna adyacente con cabecera de columna con nombre “y2” con la fórmula x^4-2*x^3+1
RASTREAR FÓRMULAS I
Ver fórmulas y no resultados: Menú Formulas=>Show Formulas
Rastrear fórmulas: Hacer clic sobre fórmulas Menú Fórmulas=>Trace Precedents
Determinar si alguna fórmula utiliza una celda: Hacer clic sobre fórmulas Menú Fórmulas=>Trace Depends
Rastrear un error Hacer clic sobre fórmulas Menú Fórmulas=>Error Checking
Evaluar una fórmula Hacer clic sobre fórmulas Menú Fórmulas=>Evaluate Formula
Hacer seguimiento de valores de celda en varios o el mismo libro de trabajo Watch Window
EXTENSIONES
xls => Versión desde 2 hasta 2003 xlsx => Versión 2007 en adelante xlsm => Versión 2007 en adelante con vba
(macros) xlsb => Versión 2007 en adelante para archivos
que poseen una gran cantidad de datos. Reduce tiempo de apertura. Pero su problema es que no se pueden recuperar parcialmente
xltx=>Plantilla Excel, cuando repetimos un tipo de planilla
GUARDAR
Existen dos formas de guardar un libro de trabajo:
1. Guardar como. Cuando se guarda un archivo por primera vez, o se guarda una copia de uno existente en otra ubicación. Además se puede cambiar el nombre del archivo
2. Guardar. Cuando guardamos los cambios que haya podido sufrir un archivo, sobreescribiéndolo.
GUARDAR PLANTILLA EXCEL (XLTX)
Cuando utilizamos en forma frecuente una misma plantilla, podría ser conveniente agregarla en nuestras plantillas
La ubicación de las plantillas privadas, se modifica en MS Word en Herramientas => Opciones =>Archivos
Creamos la plantilla y la guardamos como xltx
Si deseamos utilizarla, nos vamos a Menú Archivo => Nuevo => Mis PLantillas
IR A UNA DETERMINADA POSICIÓN
Anotar en el cuadro de nombres la dirección, ejemplo: C99
Menú Home=>Find & Select=>Go To… Tecla de función: F5 Ctrl + I
DESACTIVAR LA OPCIÓN REEMPLAZAR TEXTO MIENTRAS ESCRIBE
Muchas veces Excel modifica nuestras fórmulas cuando las estamos escribiendo y por ello se presentan errores
Ejemplo de lo anterior es cuando nombramos una celda “dolar” y Excel lo corrige por “dólar” (acento) y la fórmula nos muestra un mensaje de error
Por ello para desactivar esta función haga lo siguiente:
Menú File=>Options=>Proofing => AutoCorrect => desactivar Replace text as you type
1.- ASOCIAR NOMBRE A UNA CELDA O RANGO
Para qué? Para que la fórmula sea más legible, ejemplo: “2*dolar” en vez de “2*A10”.
Se puede definir un nombre de celda a nivel de libro u hoja, Existen estas formas alternativas de hacerlo:
Nombre válido en todo el libro: Ir a celda y escribir en “cuadro de nombres” el nombre de la celda y presionar “enter”.
Sólo hoja activa: Sobre la celda clic derecho y desde el menú contextual elegir “Define Name...” Y elegir si es válido para todo el libro o sólo la hoja activa
Pestaña “Formulas”=>”Name Manager”=> New... Y elegir si es válido para todo el libro o sólo la hoja activa
Ejercicio: Realizar lo anteriormente mencionado con euro y dolar
NOMBRAR UN RANGO
Un rango es un conjunto de celdas Su notación es “primera celda superior:
última celda inferior” (ejemplo A4:C7) Celda superior + F8 + Celda inferior Rangos continuos y Rangos
discontinuos (CTRL)
NOMBRAR UN RANGO DINÁMICO I
Cuando definimos un nombre a un rango y este crece, debemos ir ampliándolo cada vez. Pero hay un truco para evitar esto:En el casillero de Refers To box (definición de rango), ingrese una fórmula Offset que el rango, basado en el número de elementos de la columna, e.g.:
=OFFSET(Sheet1!$A$1;0;0;COUNTA(Sheet1!$A:$A);1)
En este ejemplo el rango comienza en celda A1, los argumentos utilizados en esta función son:
Reference cell: Sheet1!$A$1 Rows to offset: 0 Columns to offset: 0 Number of Rows: COUNTA(Sheet1!$A:$A) Number of Columns: 1 Note: for a dynamic number of columns, replace the 1 with: COUNTA(Sheet1!$1:$1)
NOMBRAR UN RANGO DINÁMICO IISi es a partir de la celda b31:=OFFSET(Prices!$B$1,30,0,COUNTA(Prices!$B:$B)- COUNTA(Prices!$B1:$B30),2)Or, name the start cell for the range, and use that to find and count the cells above it:=OFFSET(Prices!$B$1,ROW(StartCell)-1,0,COUNTA(Prices!$B:$B) – COUNTA(OFFSET(Prices!B1,0,0,ROW(StartCell)-1,1)),2)
EJEMPLO RANGO DINÁMICO
Sub buscar02() Dim array01() Dim i As Integer, j As Integer Dim numLineas As Integer, numColumnas 'Lee el rango y lo asigna al array With Sheet2.Range("datos") ReDim array01(1 To .Rows.Count, 1 To .Columns.Count) array01 = Sheet2.Range("datos") 'Escribe lo leido For i = 1 To .Rows.Count For j = 1 To .Columns.Count Sheet2.Cells(i, j) = array01(i, j) Next j Next i End With End Sub
2.- COPIAR Y PEGAR
Utilizando herramienta de pestaña Utilizando menú contextual Ctrl+C (copiar) y Ctrl + v (pegar) Mouse + CtrlUtilizar portapapeles:Ejercicio: Pegado Especial
PEGADO ESPECIAL
Todo: Para copiar tanto la fórmula como el formato de la celda. Fórmulas: Para copiar únicamente la fórmula de la celda pero no el formato de
ésta. Valores: Para copiar el resultado de una celda pero no la fórmula, como
tampoco el formato. Formatos: Para copiar únicamente el formato de la celda pero no el contenido. Comentarios: Para copiar comentarios asignados a las celdas (no estudiado en
este curso). Validación: Para pegar las reglas de validación de las celdas copiadas (no
estudiado en este curso). Todo excepto bordes: Para copiar las fórmulas así como todos los formatos
excepto bordes. Ancho de las columnas: Para copiar la anchura de la columna. Formato de números y fórmulas: Para copiar únicamente las fórmulas y todas
los opciones de formato de números de las celdas seleccionadas. Formato de números y valores: Para copiar únicamente los valores y todas los
opciones de formato de números de las celdas seleccionadas.
CORTAR Y PEGAR
Ctrl+X (Cortar) Ctrl + V (pegar) Herramientas de pestaña Con Mouse arrastrar Menú contextual
3.- BUSCAR OBJETIVO
Se tiene una fórmula y se desea cambiar una celda para cambiar el valor de la fórmula y obtener un determinado valor.
Ejemplo: Se tiene Nota01 y se desea determinar qué Nota02 se debe obtener para obtener promedio 4,0?
Menú Data=>What-If Analysis=>Goal Seek... Definir la celda: Celda que posee fórmula, (promedio en este
ejemplo) Con el valor: 4,0 (valor buscado en este ejemplo) Para cambiar la celda: celda donde está Nota02
ADMINISTRAR OBJETIVOS I
Se desea plantear sensibilidad respecto a un determinado problema con variadas alternativas que se desean comparar
Datos => Administrador de escenarios => Agregar… => Agregar un nombre al escenario y definir las celdas cambiantes, que en este caso (préstamo) es la tasa de interés y la cantidad de períodos (G42:G43)
En Administrador de escenarios se elige cualquiera de los escenarios y se presiona botón Mostrar y muestra los distintos escenarios creados
ADMINISTRAR OBJETIVOS II
Al crear resumen de escenarios se presenta una tabla con cada uno de ellos
En celdas de resultado, se selecciona la mensualidad o resultado que nos interesa
TABLA DE DATOS I
Se analizan una o dos variables Se calcula el pago del préstamo en base a un monto solicitado,
tasa de interés y cantidad de cuotas Se haca referencia a el pago en otra celda y se anota hacia
abajo los porcentajes Se marca todo y se va a Datos => Análisis Y si => Tabla de
datos Por estar en columnas se agrega en Celda entrada (columna) el
porcentaje de tasa de interés de la tabla inicial
TABLA DE DATOS II
En la Celda de entrada (fila) se hace clic sobre los períodos de la tabla inicial
En la Celda de entrada (columna) se hace clic sobre la tasa de interés de la tabla inicial
4.- SEPARAR TEXTO EN COLUMNAS
Los datos están ubicados en una sola celda, pero los necesitamos en dos celdas, ejemplo Jorge Perez aparece en una sola celda y debemos separarlo en nombre y apellido
Data=>Text to Columns Delimitados: Cuando entre nombre y
apellido hay algún espacio en blanco, coma u otro carácter
De ancho fijo: Cuando el nombre y apellido ocupan siempre la misma posición
5.- SERIES
Existen dos formas de crear series (A/B):A. Colocar inicio y próximo número y arrastrar con el mouseB. Menú Home=>Fill=>Series...
1. Introducir incremento y límite
Ejercicio: Crear serie que comience en 3 y termina en 129 con
incremento de 3, en filas Crear serie que comience en 1000 y termine en 2 con
decremento de 2, en filas
6.- QUITAR CELDAS REPETIDAS
Menú Data => Remove Duplicates Seleccionar columna donde están
repetidos los valores y activar si es que existen headers
Ejercicio: 1 Pedro Pérez 2 Juan Muñoz 3 Pedro Pérez 4 Pedro Pérez
7.- ORDENAR DATOS
ordenar por una columna u ordenar por diferentes columnas a la vez, siguiendo una jerarquía
Pestaña Data=>Sort ascendente/descendente Se ordena según la columna que contenga la celda activa
Ventana con: Ampliar Selección/Continuar con la selección actual
Pestaña Datos=>Ventana Ordenar Se puede ordenar en base a más de un criterio
8.- VALIDACIÓN DE DATOS
Nos permite asegurarnos que los valores que se introducen en las celdas son los adecuados. Pudiendo mostrar un mensaje de error o aviso si nos equivocamos
Pestaña Datos=>Validación de Datos Permitir=>Lista Sólo se pueden ingresar los
números de la lista Se puede ingresar mensaje de entrada y de error Si no se ingresa el mensaje de error o se ingresa
de tipo “Información”, se puede utilizar la opción “Rodear con un círculo datos no válidos”
9.- BUSCAR Y REEMPLAZAR DATOS I
Menú Home=> Find & Select : Abrirá una ventana de Find Replace
Si tenemos un rango seleccionado, sólo buscará en esa selección
En opciones podemos determinar dónde buscar (hoja/libro de trabajo), tipo de búsqueda (por fila/columna) y dónde buscar (fórmulas, valores, comentarios)
BUSCAR Y REEMPLAZAR DATOS II
Se pueden utilizar wildcards tales como “*” y “?” s*d encuentra sad, started s?d encuentra sad
Para encontrar símbolos * y ?, se utiliza ~? y ~* Match case es para separar búsqueda de
mayúsculas y minúsculas Match entire cell contents para buscar
exactamente lo buscado en una celda y no parte de ella
Replace es para reemplazar lo encontrado
VER UN MISMO LIBRO EN UNA VENTANAS
Menú Vista => Nueva Ventana Menú Vista => Organizar Todo Menú Vista => Dividir
VER VARIOS LIBROS EN UNA MISMA VENTANA
Abrir varios libros Menú Vista => Organizar Todo Menú Vista => Dividir (Arrange All) Elegir tipo de layout Tiled, Horizontal,
Vertical, cascade
DOS HOJAS EN VERTICAL
Abrir dos hojas (del mismo o de diferentes libros de trabajo)
Ubicarlos una al lado del otra (vertical) o utilizar “View Side by Side”
Activar “Synchronous Scrolling”
GUARDAR WORKSPACE
Se pueden guardar el layout de ventanas
Menú View=>Save Workspace
CAMBIAR ENTRE VENTANAS
Si se poseen dos o más libros abiertos se desea cambiar entre ventanas para ello se debe...
Menú View=>Switch Windows
INMOVILIZAR PANELES
Si columna a inmobilizar está en B y filas en 1: Menú Vista=>Inmovilizar Inmovilizar primera fila o columna
Si datos están en otro lugar: Marcar datos Menú Vista => Inmovilizar Paneles
EJERCICIO 01
Repaso: 1.- Defina una celda con el nombre x y otra con el nombre y, y
represente la adición de éstas en otra celda que haga referencia a x e y. 2.- Utilice auditoría para comprobar la fórmula 3.- Con cuál tecla se modifican los datos ingresados 4.- Genere una serie de 1000 a 0 en filas con paso 5 5.- Ordene los números 3,4,5,6; 5,6,1,2;1,5,6,2 según columna 1 y luego
columna 2. Con cabeceras de columnas A, B, C y D 6.- Cómo se puede ir a celda f505? 7.- Obtenga el día de hoy con hora y fecha, cuál es el último laboral de
mayo? 8.- Validar ingreso de números entre 1 y 9. Si no debe indicarse mensaje
de error 9.- Abra dos libros de trabajo y ubica las ventas en forma horizontal,
vertical y vertical sincronizado
10.- DROP DOWN PARA ELEGIR VALORES
De forma de no cometer errores en ingreso se elige de una lista desplegable (drop down) una lista de valores: Seleccionar celdas que posean drop
down Menú Data=>Data Validation=>Ficha
settings => List Seleccionar columna de fuente de
datos
11.- TIME SERIES
Si se desea realizar una serie de tiempo, ejemplo comenzar el 21.01.2013 y terminar el 30.01.2013 se pueden utilizar series
Colocar la fecha inicial y dejar esa celda como activa (haz clic sobre esa celda)
Menú Home=>Fill=>Series=>Date Elegir una Date unit: día, semana, mes,
año
12.- SPECIAL BUTTONS: FORM, CAMERA I En Excel existen botones o fichas que se
deben activar primero antes de ser utilizadas File => Customize Ribbon => All Commands
(del drop down) => Buscar Form... Al otro lado en Customize the Ribbon agregar:
New Tab: Es una nueva ficha de herramientas New Group: Es dentro de ficha de herramientas
12.- SPECIAL BUTTONS: FORM, CAMERA II Form (formulario automático)
Arrastrar Form... al nuevo tab/group Aceptar y volver a planilla Crear encabezados de columnas Hacer clic sobre la tabla y llamar a Form Ingresar Datos... Buscar Datos...
13.- SPECIAL BUTTONS: FORM, CAMERA III
Camera Arrastrar Camera... al nuevo tab/group Aceptar y volver a planilla Marcar área de origen y hacer clic sobre
botón camera Ir a destino y pegar celdas
14.- LISTAS PERSONALIZADAS
A veces se requieren listas personalizadas de elementos que se repiten, ejemplo lista de naves, costos de personal, etc.
Menú File=>Options=> Advanced => Al final en General existe un botón con “Edit Custom Lists...”
Agregar los elementos de la lista ya sea escribiéndolos o importándolos
Clic sobre Ok, cerrar Excel Options y volver a la planilla
Escribir el primer elemento de la lista y arrastras celda
PERSONALIZAR SIGLAS
Cuando se repite mucho una palabra se puede automatizar y reemplazar automáticamente, ejemplo CSAV por Compañía Sudamericana de Vapores
Menú File => Options => Proofing => Autocorrect Options... => Autocorrect => Activar “Replace text as you type”
Ingresar en Replace “CSAV” Ingresar en With “Compañía Sudamericana de
Vapores” Problema: Si se define un nombre de celda “dolar”
Excel la corregirá por “dólar” y no se referenciará a la misma celda
15.- FUNCIONES DE CONDICIÓN
=IF(condición;Verdadero;Falso) IF(b3>=10;”Valor >= 10”;”Valor <10 ”) IF(OR(c3>=10;c4>=10);100;0) OR(expr01;expre02) y AND(expr01;expr02)
Si existe un error mostrará un mensaje o valor =IFERROR(celda;valor o mensaje si hay
error) Si es mensaje se debe utilizar comillas
“mensaje”
FORMATO CONDICIONAL
Marcar rango de datos Pestana inicio=>Formato condicional Distintas opciones: barras de datos,
escalas de color, conjunto de íconos, valores repetidos
17.- REFERENCIAS MIXTAS Y GLOBALES Cuando se copia una fórmula hacia
abajo se actualizan las referencias, pero a veces se deben dejar fijas algunas celdas Mixtas: $A23 o A$23 Globales: $A$23 Se marca la referencia y se presiona la
tecla F4
EJERCICIOS02
Consultar hoja Ejercicios02
18.- PROTEGER HOJA I
Se protege una hoja para restringir el acceso de edición de la hoja
Seleccionar celdas donde el usuario ingresará valores. Botón derecho del mouse sobre ellas => Format cels =>Protection => desactivar “Locked”
Si se desea esconder fórmulas se debe activar Hidden, así cuando se haga Formulas=>show formulas no mostrará la fórmula
Pestaña Celdas=>Formato=>Proteger hoja Botón derecho del mouse=>menú
contextual=>Proteger/Desproteger hoja
PROTEGER HOJA II
Realizar ejercicios de proteger hoja y drop downs
INSTRUIR, ENSEÑAR Y EDUCAR
Ideas sobre enseñar y educar Instruir y enseñar son acciones que van desde fuera hacia
adentro y se expresan en contenidos programáticos, educar, en cambio es una acción que va desde dentro hacia
afuera del individuo y sus contenidos significativos representan actitudes, conductas, realización de valores e ideales.
Educar es una acción provocada o motivada indirectamente para que nazca y renazca constantemente en el ser que se educa ese impulso auto educativo que es la expresión de la propia voluntad de perfeccionamiento. Por eso, ningún docente puede educar cabalmente; sólo puede poner al educando en la senda e indicarle la dirección en que el mismo proseguirá desarrollándose
ENLAZANDO Y CONSOLIDANDO HOJAS DE TRABAJO
Utilizar fórmulas de varias hojas/libros para combinar datos:
Hoja/Libro de trabajo dependiente: es el que contiene fórmulas
Hoja/Libro de trabajo independiente: es el que contiene datos
Pregunta antes de enlazar hojas/libros de trabajo: Realmente nos hace falta complicarnos tanto o sencillamente hacerlo todo en una misma página?
Notación =Hoja3!C3
FORMATO DE CELDAS
Excel nos permite cambiar la apariencia de los datos
Fuente: Marcar dato y haz clic en flecha que se encuentra al pie de la sección Fuente => Se abrirá el diálogo de formato de celdas:
Fuente, estilo y Tamaño
ALINEACIÓN
Haz clic en la flecha que se encuentra al pie de la sección Alineación: Aparece la ficha Alineación Horizontal: horizontal, general
(num=>derecha, texto=>izq),Izquierda (sangría), centrar, derecha (sangría),
rellenar, justificar, centrar en selección, distribuído (sangría)
BORDES
Ejercicio con bordes
FORMATO NUMÉRICO
Ejercicio con formatos
AUTOAJUSTAR
Autoajustar:Si hemos modificado la altura de una fila, podemos redimensionarla para ajustarla a la entrada más alta de la fila, utilizando dos métodos distintos: Pestaña Celdas=>Formato=>Tamaño
celda=>Alto de fila/Autoajustar alto de fila Llevar el puntero del mouse al borde de la
columna o fila y cambiarlo por mano
CAMBIAR NOMBRE A HOJA, MOVER/COPIAR HOJA, COLOR DE ETIQUETA
Doble clic sobre el nombre de la hoja para renombrarla
Menú contextual: Mover y Copiar hoja Mover hoja dentro del libro activa Borrar hoja Insertar hoja Hide / Unhide hoja
VISIBILIDAD FILAS, COLUMNAS
Marcar fila/columna y podemos: Insertar Borrar Borrar contenidos Ocultar / mostrar Arrastrarla y moverla de lugar Copiar (ctrl + arrastrar mouse)
INSERTAR / ELIMINAR FILAS O COLUMNAS Pestaña Celdas=>Insertar / Eliminar Botón derecho del mouse=>menú
contextual=>Insertar/Eliminar Filas/Columnas Observación filas: Añadir filas a nuestra hoja de cálculo no
hace que el número de filas varíe, seguirán habiendo 1.048.576 filas, lo que pasa es que se eliminan las últimas, tantas como filas añadidas. Si intentas añadir filas y Excel no te deja, seguro que las últimas filas contienen algún dato
Observación columnas: Añadir columnas a nuestra hoja de cálculo no hace que el número de columnas varíe, seguirán habiendo 16384 columnas, lo que pasa es que se eliminan las últimas, tantas como columnas añadidas. Si intentas añadir columnas y Excel no te lo permite, seguro que las últimas columnas contienen algún dato
VISTAS DE EXCEL
Normal Diseño de página (Page Layout): Para
ver y ajustar las celdas y objetos de nuestro documento
Vista Previa Salto de página (Page break preview)
La pestaña Vista permite personalizar qué elementos mostrar
ZOOM
Zoom 100% Zoom to Selection: Ampliar Selección:
aumenta el zoom a 400%, centrándolo en la celda u objeto seleccionado
EN LA VISTA NORMAL, LAS LÍNEAS DE LOS SALTOS DE PAGINA I
se muestran automáticamente después de que: a) haces uso de la "vista previa de saltos de pagina b) muestras una impresión preliminar c) imprimes la hoja
Estas se borran en File => Options => Advanced => Display options for this worksheet => Desactivar “Show page breaks”
Esto podría tener efectos colaterales si haces uso de macros que se basen en la determinación o búsqueda de esos saltos de pagina
EN LA VISTA NORMAL, LAS LÍNEAS DE LOS SALTOS DE PAGINA II
ESQUEMAS Y GRÁFICOS EN EXCEL
Insertar una imagen Insertar Clip Art Insertar formas Insertar estructuras Insertar Screenshot
LENGUAJE EN MS EXCEL
Para cambiar de idioma en MS Excel se debe comprar el módulo de idioma e instalarlo
Luego en menú File => Options => Language establecer el idioma de preferencia
Cerrar Excel y volver a abrir
CONFIGURAR PÁGINA I
Antes de imprimir una hoja de cálculo, es conveniente que configuremos la página, para modificar factores que afectan a la presentación de las páginas impresas, como la orientación, encabezados y pies de página, tamaño del papel, ...
Page Layout => Flecha de Grupo Page Up (esquina inferior derecha Print area: Definir sólo lo que se desea imprimir Print titles Print Page order
CONFIGURAR PÁGINA II
Agregar pie de página Agregar cabecera de página
EJERCICIOS03
Ver hoja ejercicios03
20.- FUNCIONES
Una función es una fórmula predefinida por Excel (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene
Las fórmulas pueden contener más de una función, y pueden aparecer funciones anidadas dentro de la fórmula.
La sintaxis de cualquier función es: = nombre_función(argumento1;argumento2;...;argumentoN)
Siguen las siguientes reglas: - Si la función va al comienzo de una fórmula debe empezar por el signo =. - Los argumentos o valores de entrada van siempre entre paréntesis. No dejes
espacios antes o después de cada paréntesis. - Los argumentos pueden ser valores constantes (número o texto), fórmulas o
funciones. - Los argumentos deben de separarse por un punto y coma ;. Ejemplo: =SUMA(A1:C8) Tenemos la función SUMA() que devuelve como resultado
la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8
AUTOSUMA Y FUNCIONES MÁS FRECUENTES
Marcar rango a sumar luego Menú Home=>AutoSum/Average/Count numbers/Max/Min
Para insertar cualquier otra función hacer clic sobre fx en la barra de fórmulas
O sobre Menú Formulas=>Elegir tipo de fórmulas
FUNCIONES
Sumar.Si: Suma elementos si cumplen cierta condición
Números romanos: roman(número) Unir texto o concatenar:
concatenate(texto1;texto2); Mayúscula: upper(celda) Minúscula: lower(celda) INDEX(array;Row;Col) entrega un valor
de una matriz
FUNCIONES DE FECHA Y HORA
Investigar las siguientes funciones: Castellano: Ahora(), Año(), dia(),
dia.lab(), fin.mes() Inglés: Today(), year(), day(), month(),
workday(), Eomonth() Ejercitar estas funciones
REDONDEAR NÚMEROS
A veces se requiere redondear valores hacia arriba o hacia abajo o al múltiplo inferior/superior
BÚSQUEDA AUTOMÁTICA
Buscar un elemento dentro de una matriz (vlookup o buscarV)
Vlookup( lo que se busca; Matriz donde se busca; De cuál columna 1, 2, 3, etc. Coincidencia debe ser aproximada (TRUE)
o exacta (FALSE) )
REFERENCIAS RELATIVAS, MIXTAS Y ABSOLUTAS
Cuando se copia la fórmula se puede perder la referencia a la cual hace mención => F4
Referencia relativa: A1 Referencia mixta: A$1 o $A1 Referencia absoluta: $A$1 Ejercicios
21.- CONTAR.SI() O COUNTIF()
Cuenta el número de celdas dentro de un rango que cumplen un criterio especificado
=CONTAR.SI(rango;criterios) Rango pueden ser una o más celdas que se van a
contar. Pueden ser números o texto. Los valores en blanco no se cuentan
Criterios puede ser un número, referencia de celda o cadena de caracteres, ejemplo 32, “>32”, B4, “manzanas” o “32”. Se pueden utilizar los wildcard (comodín) de “?” (un carácter)
y “*” (varios caracteres). Si desea buscar un “?” o “*” escribir una tilde delante del
signo “~”. No se distingue entre minúsculas o mayúsculas
SUMAR.SI
Se suman los valores si se cumple un criterio específico
CONEXIÓN CON MYSQL (NS5)
Mediante una conexión con ODBC a Mysql del NS5 se obtienen los datos directamente de la base de datos, sin necesidad de importarlos
Esto permite trabajar más rápido y los datos están siempre actualizados
EJERCICIO04
Hacer ejercicio04 en planilla Excel
=SUMAPRODUCT(MATRIZ01;MATRIZ02) Se requiere multiplicar el contenido de dos
matrices y luego sumar su resultado. Ejemplo columna “costo unitario” con “cantidad vendida por ítem” y se desea determinar la “suma total vendida
EJEMPLO DE PRUEBA
29.- GRÁFICOS
Gráfico incrustado u hoja de gráfico Seleccionar serie de datos (variable dependiente,
si es y=x, entonces sólo los valores de y. Luego Pestaña Insertar=>Seleccionar uno de los Gráficos
Seleccionar origen de datos Botón derecho del mouse para menú Pestaña=>Presentación Pestaña=>Diseño Modificar tamaño del gráfico Pestaña=>Diseño => Mover gráfico
FORMULAS DE TRENDLINE
PARA OBTENER LA FÓRMULA DE TRENDLINE Cuando se agrega un trendline a un gráfico no se
obtiene su fórmula en la celda de excel, sino sólo en el gráfico, para ello se utiliza lo siguiente…
y = (c2 * x^2) + (c1 * x^1) + b c2=INDEX(LINEST(y; x^{1\2});1) c1=INDEX(LINEST(y; x^{1\2});1;2) b=INDEX(LINEST(y; x^{1\2});1;3)
29.- MINIGRÁFICOS O SPARKLINES
Minigráficos ( a partir de MS Excel 2010) pequeño gráfico representativo en una única celda
que representará a una única serie de datos. De esta forma podrás ver de una ojeada la tendencia que representan unos determinados valores
Menú Insertar=>Minigráficos Aparece Pestaña «Herramientas para
minigráfico»=>Diseño No se pueden borrar con la tecla borrar/delete, se
debe hacer con «Herramientas para minigráfico»=>Diseño=> Borrar o Ficha Inicio=>Borrar todo
HIPERVINCULOS
Es como un botón que al hacerle un clic abre una página web, envia un email o abre un documento
Puede ser texto, una imagen (Menú Insertar => Shapes)
Sobre texto o imagen presione botón derecho del mouse para obtener menú contextual y elegir hipervínculo de él
Puede seleccionar Página web o archivo Seleccionar hoja del libro activo Crear documento nuevo Enviar un email
30.- ESQUEMAS Y VISTAS I
Existen unos requisitos previos para que Excel 2010 pueda crear automáticamente el esquema:
- Las filas sumario (sumatoria de columnas) deben estar por encima o por debajo de los datos, nunca entremezclados
- Las columnas sumario (sumatoria de columnas) deben estar a la derecha o a la izquierda de los datos, nunca entremezclados
Si la disposición de los datos no se corresponde con estas características nos veremos obligados a definir el esquema manualmente.
ESQUEMAS Y VISTAS II
Menú Datos=>Agrupar => Autoesquema
INCLUIR SUBTOTALES
ordenar los datos según alguna agrupación, ejemplo departamento Marcar datos y Menú Data=>Subtotal “At each change in” colocar departamento “Use function” colocar la función que
aglutinará datos, ejemplo “sum” “Add subtotal to” agregar subtotal en
donde se sumarán los datos Con Remove All se retiran los subtotales
ESTRUCTURA DE ESQUEMAS
Region Enero Febrero Marzo 1rTrimestreVina 12 10 9 31Valparaíso 14 11 10 35V Region 26 21 19 66La Serena 18 13 12 43Coquimbo 20 14 13 47IV Region 38 27 25 90Chile 64 48 44 156
Es una sumatoria de la columna de arriba
Los valores son sumatorias de la fila
CÓMO HACER EL ESQUEMA AUTOMÁTICO? Clic sobre esquina izquierda superior de los
datos Menú Datos=>Agrupar=>Autoesquema Vemos en la esquina superior izquierda unos
números que nos indican cuántos niveles tiene el esquema.
Por columnas podemos ver que existen dos niveles: - La tabla desplegada por completo - y la tabla con los datos trimestrales.
ESQUEMA MANUAL
Marcar filas Vina y Valparaíso, Menú Datos=>Agrupar
Marcar filas La Serena y Coquimbo, Menú Datos=>Agrupar
Marcar todas filas sin Chile, Menú Datos=>Agrupar
ESQUEMAS
Borrar esquema, Menú Datos=>Desagrupar => Borrar esquema
Esconder/Mostrar esquema Ctrl+8 (del teclado numérico)
ES BUENO SABER DE TABLAS DE EXCEL Es un conjunto de datos organizados en filas o
registros, en la que la primera fila contiene las cabeceras de las columnas (los nombres de los campos), y las demás filas contienen los datos almacenados
Cada fila es un registro de entrada, por tanto podremos componer como máximo una lista con 255 campos y 65535 registros
En versiones más antiguas de Excel, las tablas se denominaban Listas de datos. Incluso es posible que en algunos cuadros de diálogo, se referirá a las tablas como listas.
TABLAS I: CÓMO HACER UNA TABLA?
Marcar los datos, encabezados de filas y columnas
Menú Insertar=>Tabla Activaremos la casilla de verificación “La
lista tiene encabezados” Aparece la pestaña “Herramientas de tabla”
Diseño Para deshacer la tabla:
“Herramientas de tabla” Diseño => Convertir en rango
TABLA III: ¿CÓMO HACER FILTRADO AVANZADO?
Ficha Datos=>Filtro =>Avanzadas Se define „criterios de filtrado“ copiando las
cabezas de filas. Bajo ellas se ingresan los criterios. En la misma fila es la condición „AND“ y en filas distintas es „OR“
Se puede seleccionar copiar en otra área de la hoja los resultados de la selección
Sólo registros unicos se refiere a si existen dupletas de datos, se mostrarán solo una vez
TABLAS IV: AGREGAR DATOS DE UNA TABLA
Para modificar o introducir nuevos datos en la tabla podemos teclear directamente los nuevos valores al final de la tabla
P en la última fila presionar la tecla “TAB” Para modificar rango de tabla => ir a punta
derecha abajo y moverla o botón “Cambiar tamaño de la tabla”
Crear nueva fila: Estando en última fila presionar TAB
Filtro de datos, borrar filtro
TABLA V: TOTALES EN UNA TABLA
Hacer clic sobre la tabla En la nueva pestaña „Herramientas de
tabla“ activar el checkbox fila de totales
En la fila de totales seleccionar la función deseada
TABLA VI: OPERACIONES CON COLUMNAS Al insertar elementos en una tabla, se
incrementa y anexan elementos a ella Si se desea procesar una columna de
una tabla, se hace referencia a ella mediante: nombreTabla[nombreColumn], ejemplo:
TABLA VII: OPERACIONES CON COLUMNAS Si no se desea utilizar la notación ampliada
de nombreTabla[nombreColumn], se debe definir un rango con cada columna y luego se inserta la tabla
Apenas se agrega un elemento nuevo se amplia tabla y se amplían rangos automáticamente
IMPORTAR DATOS DE TEXTO O ARCHIVO PLANO A MS EXCEL
Menú File => Open o también en Menú Data=>From Text
Determinar separador de datos: Espacio en blanco, semicolon (;), etc.
Dar formato a columnas, según necesario, ejemplo columna de fechas se debe identificar si es DMY o YMD (year month day)
ERRORES EN EXCEL (VER HOJA ERRORES) ##### se produce cuando el ancho de una columna no
es suficiente o cuando se utiliza una fecha o una hora negativa.
#VALUE! cuando se ha introducido un tipo de argumento o de operando incorrecto, como puede ser sumar textos.
#¡DIV/0! cuando se divide un número por cero. #NAME? cuando Excel no reconoce el texto de la fórmula. #NUM! cuando un valor no está disponible para una
función o fórmula. #¡REF! se produce cuando una referencia de celda no es
válida.
PRUEBA Y REPASO VER HOJA EJERCICIOS06
Desarrolle los ejercicios de la hoja Ejercicios06
TABLA DINÁMICA I
Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos
Es dinámica porque nos permite modificar su estructura al ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc.
TABLA DINÁMICA II
La tabla de origen no debe contener filas o columnas vacias
Hacer un clic sobre la tabla Menú Insert=>Pivot Table (Tabla Dinámica) En la „Lista de campo“ existen 4 campos:
Report Filter, Column Labels, Row Labels, Values
En Values van los datos que serán sumados, promediados, etc.
TABLA DINÁMICA III
Report Filter: Crea un filtro con drop down
Column Labels: Son los encabezados de columna
Row Labels: Son los encabezados de Fila
Values: Son los datos y acá se puede seleccionar la operación sobre los datos, ejemplo SUM, Average, etc.
TABLA DINÁMICA IV Se arrastran campos a las áreas y se
genera la siguiente tabla
TABLA DINÁMICA V
Modificar celdas vacías => botón derecho mouse sobre la tabla=> „PivotTable Options...“ => For empty cells rows…
Para modificar formato de valores: botón derecho del mouse sobre la tabla=>Value Field Setting… (Configuración de campo valor...)=> Botón Number Format (Formato de número)
En la tabla se puede cambiar „Row Lebels“ y „Column Labels“ Si se modifica la tabla origen se debe actualizar la tabla
dinámica Si se ingresa filas o columnas en la tabla de origen, no se
actualiza la tabla dinámica Existe la opción de que cuando se abra el libro se actualice la tabla dinámica
TABLA DINÁMICA VI
En la pestaña Diseño se pueden realizar diferentes cambios en formato… activar/desactivar Totales generales y
Subtotales, Insert Blank line after each item
TABLA DINÁMICA VI
Slider nos permite automatizar categorías por botones
Hacer clic sobre tabla dinámica Menú Insert => Slicer => Elegir campo
Con Ctrl+ elegir otro campo se pueden seleccionar varios campos
TABLA DINÁMICA VII
Los filtros en una tabla dinámica se manejan haciendo clic sobre la punta de flecha
GRÁFICOS DINÁMICOS
Hacer clic sobre la tabla dinámica En Menú Options de “PivotTable Tools”
=> PivotChart Hacer clic en los filtros
CONSOLIDAR
Se poseen varias hojas de planilla con iguales estructuras, pero definidas en diferentes unidades de tiempo y se desean consolidar, sumando/promediando sus valores
Cada tabla está en un libro/hoja distinto, pero en igual posición
Hacer clic en celda de destino de consolidación Del menú Data => Consolidate => Definir los rangos
y agregar a la lista Activar Use labels in Top row Sólo se puede activar “Create links to source data” si
son de libros distintos. Se actualiza información
CREAR VINCULO A DATOS DESDE INTERNET
Se desea crear un vínculo con una página web que lea determinados valores, ejemplo valor del dólar, uf, euro, etc
Menú Data=>From Web => Ir a página Web deseada => De esta página hacer clic sobre flechas amarillas (aparece ticket verde)=> Presionar botón import
Clic sobre el botón Properties…=> Refresh data when opening the file
SUMA DE TIEMPO
MS Excel suma en base a horas en días, por ello se puede cometer un error…
CALCULAR DIFERENCIA DE TIEMPO I
Para presentar la diferencia horaria en el formato estándar de tiempo
CALCULAR DIFERENCIA DE TIEMPO II
Para presentar la diferencia horaria en algún tipo de unidad horaria (hora, minuto, segundo)