EXCEL 2010 Guía visual pra seguir as clases de Ofimática. Débense completar na clase JULIO GONZÁLEZ SOTO 19/10/2011

TABOA DE CONTIDOS 1

2

3

ASPECTOS XERAIS ...................................................................................................... 4 1.1

A pantalla de Excel ........................................................................................................ 4

1.2

Propiedades do documento .......................................................................................... 5

1.3

Moverse pola folla de calculo ....................................................................................... 5

1.4

Buscar e substituír información .................................................................................... 6

1.5

Introdución de información .......................................................................................... 6

1.5.1

Valores numéricos ................................................................................................. 6

1.5.2

Texto ...................................................................................................................... 6

1.5.3

Datas e horas ......................................................................................................... 7

1.5.4

Series de datos ...................................................................................................... 7

1.5.5

Matrices................................................................................................................. 8

1.5.6

Anotacións ............................................................................................................. 9

1.5.7

Formulas ................................................................................................................ 9

1.5.8

Formato ................................................................................................................. 9

FOLLAS DE CÁLCULO ............................................................................................... 10 2.1

Operacións básicas ...................................................................................................... 10

2.2

Nomear a información ................................................................................................ 11

CÁLCULOS ................................................................................................................ 14 3.1

Fórmulas ...................................................................................................................... 14

3.1.1

Uso de referencia a celas dentro das fórmulas ................................................... 14

3.1.2

Crear unha formula cunha función ..................................................................... 15

3.1.3

Referencia absoluta e relativa ............................................................................. 15

3.1.4

Auditoría .............................................................................................................. 16

3.2

Cálculos con Matrices ................................................................................................. 16

3.2.1

Introducir fórmulas matriciais de varias celas .................................................... 16

3.2.2

Introducir fórmulas matriciais dunha cela .......................................................... 16

3.2.3

Crear unha constante matricial ........................................................................... 17

3.2.4

Dar nome a unha constante matricial ................................................................. 17

3.2.5

Editar unha fórmula matricial ............................................................................. 17

3.2.6

Cálculos cunha matriz ......................................................................................... 18

3.2.7

Funcións con matrices ......................................................................................... 18

3.2.8

Traspoñer unha matriz ........................................................................................ 18

Guía de Ofimática: Excel

Páx. 2

3.2.9 3.3

FUNCIONS.................................................................................................................... 19

3.3.1 3.3.1.1 3.3.2 3.3.2.1 3.3.3 3.3.3.1

4

5

Ver se hai un valor nunha matriz ........................................................................ 18

Funcións lóxicas ................................................................................................... 20 Exemplos ......................................................................................................... 21 Funcións de información ..................................................................................... 21 Exemplos ......................................................................................................... 22 Funcións de bases de datos................................................................................. 23 Exemplos ......................................................................................................... 23

3.3.4

Funcións diversas ................................................................................................ 24

3.3.5

Exemplos ............................................................................................................. 24

LISTAS DE DATOS ..................................................................................................... 25 4.1

Creación de listas ........................................................................................................ 25

4.2

Operacións sobre as listas ........................................................................................... 26

ANALISE DE DATOS ECONÓMICOS .......................................................................... 27 5.1

Táboas i escenarios ..................................................................................................... 27

5.2

Táboas dinámicas ........................................................................................................ 28

5.3

Solver ........................................................................................................................... 31

Guía de Ofimática: Excel

Páx. 3

1 ASPECTOS XERAIS

1.1 A pantalla de Excel

Barra de ferramentas

Barra de estado

Guía de Ofimática: Excel

Barra de formulas

Cadro de nomes

Cela activa

Páx. 4

1.2 Propiedades do documento Acudindo a Ficheiro, Información; podemos acceder á información do documento que se amosa a continuación. En Propiedades temos mais información significativa.

1.3 Moverse pola folla de calculo Usando o teclado temos algunhas TECLAS DE DIRECCIÓN INTRO TAB MAY+INTRO MAY+TAB INICIO, HOME CTROL+INICIO, HOME CTROL + FIN CTROL + PgUp CTROL + PgDn CTROL + Frechas

Guía de Ofimática: Excel

Activa a cela indicada pola dirección. Move para abaixo Move para a dereita Move para arriba Move para a esquerda Vai a columna A da fila actual Move a cela A1. Move a cela da última fila e columna con datos. Vai a folla anterior Vai a folla seguinte Movese entre extremos de grupos de información

Páx. 5

1.4 Buscar e substituír información Polo seu contido, formato … Acudimos a ficha inicio, localizar e seleccionar

A mesma opción podémola usar para substituír contidos

1.5

Introdución de información Permite a introdución dos seguintes tipos de datos:

1.5.1 Valores numéricos Seleccionar a cela, colocar o número y pulsar INTRO. A barra de fórmulas fai de espazo de edición. Alí pódese de ir pra dar entrada a información o pra corrixir a que xa existe. Aquí hai que premer o botón de introdución pra asumir o valor na cela. Un valor numérico pode ser:    

enteiro decimal fracción enteira en notación científica

34 34,5 3/4 hai que identificar a cela con formato de fracción. 4,8E+5

Si e demasiado grande Excel amosa ####### como aviso, pero o valor subxacente e o introducido. Hai un problema cos números en fraccións. Si e menor que 1 hai que poñer un 0 diante (0 3/4) senón interprétao como data. Vale poñer o símbolo ´=´, e especificar o número. 1.5.2 Texto A introdución faise activando a cela, escribindo o texto e pulsando INTRO. Si queremos que un número sexa interpretado como texto temos que poñerlle diante a coma simple. Ex: ´55 e texto e non número. Si queremos concatenar texto usamos o operador &.

Guía de Ofimática: Excel

Páx. 6

1.5.3 Datas e horas Excel almacena as datas e horas como números de serie (contan a partir do 1/1/1990) e pódense converter a diferentes formatos de data e horas e ser utilizados en cálculos cronolóxicos. 1.5.4 Series de datos Podemos encher un conxunto de celas con unha serie de números. Primeiro seleccionamos a cela ou conxunto de celas, logo acudimos a Enchemento, i eliximos a opción que desexemos (por exemplo serie).

Guía de Ofimática: Excel

Páx. 7

Podemos encher as celas cunha serie de datos personalizada. Para elo temos que crear primeiro a serie de datos. Acudimos a Ficheiro, Opcións, Opcións avanzadas, Xeral, editar lista personalizadas. Despois creamos unha nova lista.

1.5.5 Matrices Unha matriz e un conxunto de datos estruturado en filas e columnas que son tratados como unha unidade. Para crear unha matriz primeiro temos que identificar as celas que vai ocupar, despois colocamos o signo (=), identificamos os datos de orixe e pulsamos CTROL+SFIFT+ENTER. Os datos de orixe pódense identificar indicando o rango de datos (B2:D4). Si se escriben os datos directamente hai que separar as filas por (\) e as columnas por (;)1, como neste exemplo: ={1\2\3;4\5\6;7\8\9}

1

En Excel 2003 e a inversa

Guía de Ofimática: Excel

Páx. 8

1.5.6

Anotacións Pra introducir unha anotación nunha cela, debemos colocarnos na cela i elixir a orde inserir comentarios como opción no botón auxiliar. A presenza de notas nas celas sábese pola presenza dun punto na esquina superior dereita da mesma.

1.5.7 Formulas Descríbense no apartado 3.1Fórmula

1.5.8 Formato Para especificar o formato acudimos a ficha Inicio. Aquí podemos indicar as características do texto, aliñamento, tipo de dato que admite a cela, e outros aspectos.

Guía de Ofimática: Excel

Páx. 9

2 FOLLAS DE CÁLCULO E o soporte da información Un libro componse de varias follas de cálculo que podemos etiquetar. Activamos cada unha premendo no seu nome.

2.1 Operacións básicas 

Activar unha folla: premendo no deu nome



Cambiar nome a unha folla: facendo dobre clic no nome e cambiando o mesmo. Podemos utilizar o menú contextual



Seleccionar follas: premer nunha folla e apoiándose no Shift ou no Ctrol seleccionar o resto



Modificar a cor das etiquetas: usando o menú contextual



Amosar e ocultar follas: usando o menú contextual



Desprazar e copiar follas: usando o menú contextual



Inserir i eliminar follas: usando o menú contextual



Inserir filas e columnas: colocámonos na posición onde queremos inserir o elemento e acudimos a : Inicio, Celas, Inserir

Da mesma forma procedemos para eliminala 

Modificar o ancho da columna e o alto da fila: facendo clic na liña da dereita da columna axustamos o ancho o noso xeito, facendo dobre clic na mesma, axustámolo o contido.



Inserir celas bacías: axudámonos do menú contextual

Guía de Ofimática: Excel

Páx. 10



Desprazar e inserir celas, filas e columnas: sinalar un borde do elemento a operar ata que apareza unha estrela, premendo Shitf podemos desprazar o elemento seleccionado; premendo Ctrol + Shift podémolo copiar.



Repartir o contido de celas en columnas: podemos repartir o contido dunha cela entre diversas celas, para elo acudimos a: Datos, Ferramentas de datos, texto para columnas e seguimos o asistente



Eliminar filas con repeticións: permite eliminar filas que teñen os mesmos datos nas columnas. Acudimos a Datos, Ferramentas de datos, Eliminar duplicados

2.2 Nomear a información Podemos aplicarlle nomes a información, tanto das celas individuais como por grupos. Seguir algún destes estes pasos:

Guía de Ofimática: Excel

Páx. 11



Acudindo o cadro de nome e cambiando a referencia a columna e fila por un nome.



Seleccionar a cela ou rango de celas as que lle queremos por u nome. Acudir a ficha Fórmulas, Nomes definidos e proceder o caso.

Guía de Ofimática: Excel

Páx. 12



O terceiro método parte do feito de que os nomes xa existen nunha fila ou columna. Neste caso utilizamos a opción crear desde a selección

O xestor de nomes. Podemos acudir a Fórmulas, Nomes definidos, Xestor de nomes para traballar o respecto.

Podemos aplicarlle nome a un conxunto de datos e operar colectivamente identificando o nome. Pódese modificar o rango de celas asociado a un nome

Guía de Ofimática: Excel

Páx. 13

3 CÁLCULOS Especifícanse sobre cada unha das celas.

3.1 Fórmulas As fórmulas poden conter números, operadores matemáticos, referencias a celas i ecuacións xa creadas ou funcións. As fórmulas comezan co signo =. Si non aparece este símbolo, interpreta que se trata dun texto. A continuación especificamos os operadores recoñecidos por Excel:

Aritméticos + * / ^

Suma Resta Multiplicación División Potencia (2^4 = 16)

&

Concatenación (“estamos “& 25 & “na clase”  estamos 25 na clase)

= > >= < 4;B24, se B23)

SE

HAI VALOR

NO

FALSO

SI(A3;"HAI VALOR";"NON HAI VALOR") NO(B5)

OU

VERDADERO

O(B5;B6)

O

FALSO

O(B7;B20)

VERDADERO

2

VERDADERO()+B7

FALSO

FALSO

FALSO()

3.3.2

FALSO

Y(A1>A2;B1>B2)

Funcións de información

FUNCION

ACCION

FUNCION ACCION

CELDA

Devolve información acerca do formato, a colocación e o contido da cela Devolve un número que corresponde a un tipo de erro Devolve información acerca del entorno operativo en uso Devolve VERDADERO si o valor está en branco Devolve VERDADERO si o valor e calquera valor de erro excepto #N/A Devolve VERDADERO si o valor e calquera valor de error Devolve VERDADERO si o número e par Devolve VERDADERO si o valor e un valor lóxico Devolve VERDADERO si o valor e o valor de erro #N/A

ESNOTEXTO Devolve VERDADERO si o valor non e texto

TIPO.DE.ERROR

INFO

ESBLANCO ESERR

ESERROR

ES.PAR ESLOGICO ESNOD

Guía de Ofimática: Excel

ESNUMERO

Devolve VERDADERO si o valor e un número

ES.IMPAR

Devolve VERDADERO si o número es impar

ESREF

Devolve VERDADERO si o valor e unha referencia Devolve VERDADERO si o valor e texto

ESTEXTO

N

Devolve un valor convertido nun número

ND

Devolve o valor de erro #N/A

TIPO

Devolve un número que indica o tipo de datos dun valor

Páx. 21

3.3.2.1 Exemplos

Guía de Ofimática: Excel

Páx. 22

3.3.3

Funcións de bases de datos

FUNCION

ACCION

FUNCION

ACCION

BDPROMEDIO

Devolve a media das entradas seleccionadas na base de datos

BDPRODUCTO

BDCONTAR

Conta o número de celas que conteñen números na base de datos Conta o número de celas non bacías da base de datos

BDDESVEST

Extrae da base de datos un único rexistro que cumpre los criterios especificados Devolve o valor máximo das entradas seleccionadas da base de datos Devolve o valor mínimo das entradas seleccionadas da base de datos

BDSUMA

Multiplica os valores dun campo concreto de rexistros da base de datos que cumpren os criterios especificados Calcula a desviación estándar a partir dunha mostra de entradas seleccionadas na base de datos Calcula a desviación estándar en función da poboación total das entradas seleccionadas na base de datos Agrega os números da columna de campo des rexistros da base de datos que cumpren os criterios Calcula a varianza a partir dunha mostra de entradas seleccionadas na base de datos Calcula a varianza a partir da poboación total de entradas seleccionadas na base de datos

BDCONTARA

BDEXTRAER

BDMAX

BDMIN

BDDESVESTP

BDVAR

BDVARP

3.3.3.1 Exemplos

Guía de Ofimática: Excel

Páx. 23

3.3.4 Funcións diversas Se describe a continuación algunhas funcións que creemos de interese.

FUNCION

ACCION

SUMARSE (sumar.si) BUSCARV

Suma as celas que cumpren determinado criterio ou condición. Busca un valor na primeira columna da esquerda dunha táboa e devolve un valor na mesma fila desde unha columna especificada. Por defecto a táboa ordénase de forma ascendente. Busca na primeira fila dunha táboa ou matriz de valores e devolve o valor na mesma columna desde unha fila especificada.

BUSCARH

3.3.5

Exemplos

Guía de Ofimática: Excel

Páx. 24

4 LISTAS DE DATOS Unha lista é unha táboa de datos almacenados nunha folla de cálculo e organizados en columnas e filas. Unha lista é en esencia unha base de datos. Unha lista consta dunha colección de filas con cabeceiras de columna uniformes e datos consistentes. As listas deben conter un número fixo de columnas, inda que poden ter un número variable de filas. Cada columna debe ter o mesmo tipo de información, Excel identifica automaticamente.

4.1 Creación de listas Para crear una lista hai que seguir os seguintes pasos: 1. Abrir unha nova folla de cálculo. Este paso mellora a utilidade das listas en Excel. 2. Crear una cabeceira de columna para cada campo da lista. 3. Dar formato as celas que se atopa debaixo das cabeceiras da cordo o tipo de dato que se vaia utilizar. 4. Engadir información os campos.

E necesario que a táboa este completamente illada da outra información, e dicir debe ter como mínimo unha fila ou columna entre a táboa e o resto da información Pra evitar problemas en operacións posteriores, non se debe colocar mais información nas filas que ocupa a lista.

Guía de Ofimática: Excel

Páx. 25

4.2 Operacións sobre as listas  

Ordenación: acudimos a datos, Ordenar Filtrado: acudimos datos, Filtro



Subtotais: acudimos a Datos, Esquema, Subtotal

Guía de Ofimática: Excel

Páx. 26

5 ANALISE DE DATOS ECONÓMICOS Excel conta ca posibilidade de analizar datos que permitan a valoración de alternativas.

5.1 Táboas i escenarios Unha táboa de datos ou táboa de sensibilidade, resume o efecto dunha ou dúas variables sobre fórmulas que as utilizan.    

Táboas de datos baseadas nunha variable de entrada Táboas de datos baseadas en dúas variables de entrada Escenarios. Permiten analizar situacións modificando mais de dúas variables Atinxir obxectivo

Para acceder a estas operacións acudimos a: datos, Ferramentas de datos, análise de hipóteses.

Guía de Ofimática: Excel

Páx. 27

5.2 Táboas dinámicas Unha das ferramentas mais potentes de análises de datos estruturados en táboas e a través de táboas dinámicas. Esta opción está dispoñible en Inserir, Táboa dinámica

A través de esta opción entramos nun cadro de dialogo que nos permite deseñar a estrutura de esta táboa. As táboas dinámicas teñen certa terminoloxía:       

Campo columna: un campo que contén orientación de columna. Campo fila: un campo que contén orientación de fila. Total xeral: unha fila ou columna que amosa a os totais de todas as celas dunha fila ou columna da táboa. Subtotais: fila ou columna que amosa un subtotal dun elemento campo fila ou columna Grupo: conxunto de elementos que se trata como un único elemento Elemento: un elemento dun campo que aparece como encabezado de fila ou columna Área de valores: área que contén os datos do informe.

Guía de Ofimática: Excel

Páx. 28

Por exemplo buscamos unha relación entre dous elementos (categoría) con ríspeto a un terceiro (dato).

Neste exemplo, vendedor e Zoa actúan de categoría e Importe de dato.

Guía de Ofimática: Excel

Páx. 29

Premendo sobre o dato temos acceso á configuración do campo.

Aquí podemos especificar o calculo que desexamos.

Guía de Ofimática: Excel

Páx. 30

5.3 Solver Tratase dunha ferramenta que permite solucionar problemas de programación lineal. Está dispoñible en datos, Análises. Tratase dun suplemento de Excel, se non está dispoñible hai que acudir a botón de Office, Opcións de Excel, Suplementos, Ir.

O uso de solver implica especificar os segintes datos:   

Restricións Función obxectivvo Variables a calcular

Establecidos estes datos, chamase a solver e lle indicamos as posicións dos mesmos na folla así como o tipo de obxetivo a calcular os mesmos:    

Guía de Ofimática: Excel

Cela que contén a función obxetivo Restricions Tipo de obxetivo Celas das variables a calcular

Páx. 31