ms excel 2010

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)