Apuntes Excel

2011

Microsoft Excel 2007

J E S Ú S S Á N C H E Z E S P I N O S A

Microsoft Excel 2007 Jesús Sánchez Espinosa

Página 2 de 66

Apuntes

de

Excel 2007

Profesor: Jesús Sánchez Espinosa


Página 3 de 66

Índice Índice ....................................................................................................................................................................3

Presentación de la hoja de cálculo ....................................................................................................................6

Ficha de inicio ..............................................................................................................................................6

Teclas rápidas de movimiento ......................................................................................................................7

Hojas de Excel ..............................................................................................................................................8

Rango ............................................................................................................................................................9

Normas básicas .............................................................................................................................................9

Ejercicio 02 Excel .......................................................................................................................................11

Operadores aritméticos y operadores lógicos .................................................................................................11

Operadores aritméticos ...............................................................................................................................11

Operadores lógicos .....................................................................................................................................11

Prioridad de los operadores ........................................................................................................................11

Ejercicio 03 Excel .......................................................................................................................................11

Ejercicio 04 Excel .......................................................................................................................................13

Referencias absolutas (con el $) y relativas ................................................................................................13

Ejercicio 05 Excel .......................................................................................................................................14

Funciones ........................................................................................................................................................14

Ejercicio 06 Excel .......................................................................................................................................15

Comentarios ....................................................................................................................................................16

Insertar columnas y filas .............................................................................................................................16

Ejercicio 07 Excel .......................................................................................................................................16

Ejercicio 08 Excel .......................................................................................................................................17

Formato de un documento ..............................................................................................................................18

Ficha Inicio .................................................................................................................................................18

Tablas .............................................................................................................................................................18

Ejercicio 09 Excel .......................................................................................................................................20

Distintos Tipos de formato de celda ...........................................................................................................20

Ejercicio 10 Excel .......................................................................................................................................21

Gráficos ..........................................................................................................................................................21

Llevar gráficos a Word ...................................................................................................................................22

Ejercicio 11 Excel y Ejercicio 38 Word .....................................................................................................22

Varias líneas en una celda ..........................................................................................................................23

Corrección de ortografía .............................................................................................................................24

Ordenación .................................................................................................................................................24

Formato condicional .......................................................................................................................................24

Tablas dinámicas ............................................................................................................................................25

Ejercicio 12 Excel .......................................................................................................................................26


Página 4 de 66

Protección de datos en una hoja de cálculo ....................................................................................................27

Protección de toda la hoja ...........................................................................................................................27

Protección de parte de la hoja .....................................................................................................................28

Utilizar la impresora en Excel ........................................................................................................................29

Ejercicio 13 Excel .......................................................................................................................................29

Formulas .........................................................................................................................................................30

Insertar función ...........................................................................................................................................31

Ejercicio 14 Excel .......................................................................................................................................31

Ejercicio 15 Excel .......................................................................................................................................32

Guardar como plantilla una hoja Excel ......................................................................................................32

Inmovilizar paneles (Fijamos filas y columnas) .............................................................................................33

Ordenar ventanas múltiples (Varios libros abiertos a la vez) .........................................................................34

Ejercicio 16 Excel .......................................................................................................................................34

Ejercicio 17 Excel .......................................................................................................................................34

Más Funciones ................................................................................................................................................34

Ejercicio 18 Excel (Calificaciones con SI) .................................................................................................35

Configuración Propiedades de un documento Excel ......................................................................................35

Cambiar de ventana ........................................................................................................................................36

Asignación de nombre a un rango ..................................................................................................................37


Más sobre Gráficos .....................................................................................................................................38

Insertar imágenes en Excel .............................................................................................................................39

Auditoria de formulas .....................................................................................................................................40

Validación de datos ....................................................................................................................................40

Hipervinculos .................................................................................................................................................41

Ejercicio 20 Excel .......................................................................................................................................42

Macros en Excel .............................................................................................................................................43

Buscar y seleccionar ...................................................................................................................................44

Ir a, Ir especial ............................................................................................................................................44

Buscar y seleccionar: Fórmulas, comentarios, formatos condicionales, constantes, validación de datos ..44

Selección de Celdas en Excel .....................................................................................................................45

Opciones de Ortografía ...................................................................................................................................45

Función BUSCARV y BUSCAR H ...........................................................................................................47

Función BUSCAR ......................................................................................................................................49

Ejercicio 21 Excel .......................................................................................................................................54

Datos de otra hoja de cálculo ..........................................................................................................................54

Ejercicio 22 Excel .......................................................................................................................................54



Página 5 de 66

Ejercicio 24 Excel .......................................................................................................................................55

Ejercicio 25 Excel (Calificaciones con SI) .................................................................................................55

Agregar una impresora ...................................................................................................................................55

Función SUMAR.SI ...................................................................................................................................56

Ejercicio 26 Excel (Días vividos) ...............................................................................................................58

Funciones de fecha y hora ..........................................................................................................................58

Ejercicio 27 Excel .......................................................................................................................................59

Ejercicio 28 Excel (Tablas dinámicas - Ganadería) ...................................................................................59

Referencias en Excel. Relativa, Absoluta y Mixta .........................................................................................60

Referencias relativas ...................................................................................................................................60

Referencias absolutas .................................................................................................................................60

Referencias mixtas ......................................................................................................................................60

Ejercicio 29 Excel (Tablas y más) ..............................................................................................................60

Mostrar u ocultar todos los valores cero en una hoja de cálculo ................................................................61

Utilizar un formato numérico para ocultar los valores cero en las celdas seleccionadas ...........................61

Ejercicio 30 Excel (Tabla Dinámica) .........................................................................................................61

Auditoría de formulas .................................................................................................................................62

Ejercicios 31, 32 y 33 de Excel y Ejercicio 45 Word ................................................................................62

Ejercicio 34 de Excel .................................................................................................................................64

Tipos de Errores en Excel ...............................................................................................................................65

Corrección del examen de esta parte Excel ................................................¡Error! Marcador no definido.

Prioridad de operadores ..............................................................................................................................66

Ejercicio 35 de Excel .................................................................................................................................66


Página 6 de 66

Empezamos con Excel, inicio, todos los programas, Microsoft office, Microsoft office Excel 2007

Tenemos

En Word por defecto los archivos se llamaban Documento1, 2, 3,… En Excel se les llama Libro1, 2, 3,…

Tenemos las barras típicas de Windows, la de titulo con minimizar, Restaurar y cerrar.

Hay Pestañas, herramientas que son idénticas a las de Word que ya vimos en la parte anterior del curso.

Presentación de la hoja de cálculo

Ficha de inicio

Grupo Fuente

Tipo de letra Tamaño

Negrita Cursiva Subrayado


Página 7 de 66

Grupo Alineación

Parte Superior en el medio Parte inferior

Izquierda Centrar Derecha

Debajo de la barra de herramientas tenemos el cuadro de nombre que indica la celda activa y justo por

debajo el área de trabajo:

Teclas rápidas de movimiento

Con (Control + ↓) nos lleva a la última fila (1.048.576)

Con (Control +↑) nos lleva a la primera fila (1)

Con (Control +→) nos lleva a la última columna (XFD)

Con (Control +←) nos lleva a la primera columna (A)

En Excel podemos escribir:

El texto lo alinea a la izquierda y

Los números a la derecha, sólo podemos hacer operaciones con campos alineados a la derecha

Formulas

IMPORTANTE: Formulas, hay que poner el símbolo = delante


Página 8 de 66

Series

o Días de la semana. Podemos escribir series, si ponemos lunes en una celda, posicionamos el

cursor en la parte inferior derecha y arrastramos con el cursor. Ponemos todos los días de la

semana.

o Meses del año. Podemos escribir otra serie, si ponemos Enero en una celda, posicionamos el

cursor en la parte inferior derecha y arrastro con el cursor. Ponemos todos los meses del año.

o Trimestres. Podemos escribir otra serie con los trimestres

o Pares

o Impares

o Series propias.

Etc.

Hojas de Excel

Por defecto aparecen 3, pero se puede configurar.

En ellas podemos escribir texto, insertar hojas de cálculo,

También puedo ir a la ficha Inicio, grupo celdas, opción insertar

Con doble clic cambiamos el nombre, también con botón derecho cambiar nombre


Página 9 de 66

Para cambiar el color de la etiqueta, botón derecho, Color de etiqueta:

Podemos cambiar el orden de las hojas arrastrándolas con el ratón

Rango

Es una cantidad de celdas (D5:D10), mas de una. Podemos sumarla,

Con el punto y coma (;) suma las celdas que indicamos no el rango =SUMA(D5;D10), suma la cantidad que

hay en D5 y la que hay en D10, Se puede hacer directamente sin la función suma = D5+D10

Símbolo Acción Ejemplo

: (Dos puntos) Rango, … hasta … =SUMA(B1:B5)

; (Punto y coma) Esta celda y esta y esta … =SUMA(B2;B4;B5)

Normas básicas

Pasos a seguir en Excel

Paso 1: Introducir los datos

Paso2: Hacer los cálculos con las formulas

Paso 3: Dar formato

Paso 4: Guardarlo o imprimirlo


Página 10 de 66

Continuamos con Excel

Para crear una lista numérica incrementando la cantidad pulsamos (Tecla control y arrastramos): Columna B

Si arrastramos sin pulsar control lo único que hace es copiar. Columna A

Para crear una lista de texto pulsemos o no la tecla control solo copiamos el texto: Columna C y F

Si el texto termina en un número lo incrementa simplemente arrastrando: Columna E

Si tenemos un rango marcado A1:A5 y B1:B5 y escribimos en la celda A1 Excel, si pulsamos (control+Enter)

se copia en todas las celdas seleccionadas:

Pulsamos (control + Enter) y copia el texto en toda la selección:


Página 11 de 66

Para eliminar una hoja pulsamos botón derecho sobre la hoja y eliminar

Ejercicio 02 Excel

Lo guardamos con el nombre de Ejercicio 02 Excel.xls, trabajamos con series, copiar,….

Cuando guardamos un libro guardamos todas sus hojas

Operadores aritméticos y operadores lógicos

Operadores aritméticos

Para las operaciones tenemos estos operadores aritméticos:

Operador Acción

+ Suma

- Resta

* Multiplica

/ Divide

^ Potenciación

Operadores lógicos

Para las operaciones tenemos estos operadores lógicos:

Operador Acción

= Igual

> Mayor que

>= Mayor o igual que

<> No es igual (distinto)

< Menor que

<= Menor o igual que

Prioridad de los operadores

La prioridad en las operaciones es la siguiente:

1. Cálculos entre paréntesis

2. Multiplica y divide.

3. Sumas y restas

Ejercicio 03 Excel

Lo guardamos con el nombre de Ejercicio 03 Excel.xls, trabajamos con operadores.


Página 12 de 66

Ficha Inicio grupo Modificar, pulsamos en autosuma:

Una vez que tenemos la suma con la autosuma, podemos arrastrar a la celda adyacente y se copian las

formulas automáticamente.

Una vez que tenemos los datos le damos formato.

Con doble Clic ajustamos la columna al texto que contiene la columna.


Página 13 de 66

Ejercicio 04 Excel

Lo guardamos con el nombre de Ejercicio 04 Excel.xls, Hoja Presupuesto Personal. Trabajamos con

operadores.

Creamos otra hoja llamada Saldos. Trabajamos con operadores.

Referencias absolutas (con el $) y relativas

Cuando hacemos una operación con un campo no numérico da esto en la celda: #¡VALOR!

Referencia relativa

Es aquella que cambia al arrastrar con el ratón, actualiza las posiciones C4, C5, C6,…

Formalmente, la referencia relativa de una formula se basa en la posición relativa que contiene la celda, se

cambia la referencia cuando se cambia la posición.

Referencia absoluta

Es aquella que NO cambia al arrastrar con el ratón, no actualiza, se queda fijo, anclado C4, C4, C4,…

Para hacer que una celda sea considerada valor absoluto usamos el $ delante de la columna (letras) y la fila

(números), por ejemplo: =D4*$E$2

Para anclar-fijar una celda pulsamos F4 una vez que la tenemos seleccionada.

Formalmente, la referencia absoluta de una formula siempre hace referencia a una celda en una ubicación

especifica, siempre hace referencia a una celda anclada.


Página 14 de 66

Ejercicio 05 Excel

Lo guardamos con el nombre de Ejercicio 05 Excel.xls, Hoja Valor Absoluto. Trabajamos con

Referencias absolutas y relativas.

Creamos otra hoja llamada Reparto de Gastos. Trabajamos con referencias absolutas y relativas.

IMPORTANTE: Lo de la posición absoluta con el $

Funciones Excel tiene una gran cantidad de funciones, nosotros sólo veremos algunas de ellas

Una función es una fórmula que utiliza palabras específicas que llevan una forma de actuar determinada

Tiene una estructura determinada (Cuidado con los espacios)

=NombreFuncion(Argumentos)

Ejemplos:

Operación Formula Acción

Suma =SUMA(B2:B13)

Suma los valores del rango.

Promedio =PROMEDIO(D2:D13) El promedio es la media Aritmética.

Valor máximo =MAX(D2:D13) Da el valor máximo del rango Indicado

Valor mínimo =MIN(D2:D13) Da el valor mínimo del rango indicado.

Contar numéricas =CONTAR(D2:D13) Cuenta las posiciones numéricas que

encuentra en un rango.

Contar no vacías =CONTARA(D2:D13) Cuenta el número de celdas no vacías que

encuentra en un rango.


Página 15 de 66

Ejercicio 06 Excel

Lo guardamos con el nombre de Ejercicio 06 Excel.xls, Hoja Funciones. Trabajamos con Funciones.

En la celda parece el resultado y en la barra de formulas esta la operación que nos lleva a ese resultado


Página 16 de 66

Continuamos con funciones en Excel

Comentarios Para añadir un comentario, pulsamos el botón derecho y añadimos el comentario

La celda aparece marcada con rojo, indicando que tiene un comentario

Insertar columnas y filas

Para insertar una columna, seleccionamos una columna y botón derecho insertar. Esto insertará una columna a

la izquierda de la columna seleccionada.

Para insertar una fila, seleccionamos una fila y botón derecho insertar. Esto insertará una fila en la parte

superior de la fila seleccionada.

Ejercicio 07 Excel

Lo guardamos con el nombre de Ejercicio 07 Excel.xls, Trabajamos con comentarios, y formulas con

referencias relativas y absolutas. Promedios


Página 17 de 66

Algunas funciones más:

La función CONTAR cuenta las posiciones numéricas que encuentra en un rango de datos, no cuenta ni los

alfanuméricos ni el espacio en blanco. Cuenta las celdas que tienen números, ojo porque no suma.

La función CONTARA cuenta el número de celdas no vacías que encuentra en un rango.

Ejercicio 08 Excel

Lo guardamos con el nombre de Ejercicio 08 Excel.xls, Trabajamos con la formula CONTAR y

CONTARA.


Página 18 de 66

Formato de un documento

Ficha Inicio

En esta ficha tenemos los siguientes grupos de herramientas:

Portapapeles

o Copiar

o Pegar

o Cortar

o Copiar formato

Fuentes

o Tipo de fuente

o Tamaño

o Negrita, Cursiva, subrayado

o Bordes

o Color de relleno y color de la fuente

Podemos llegar a esto con el menú contextual:

Pestañas

Número: Para el tipo de datos.

Alineación: Par al alineación del texto.

Fuente: Para los tipo de fuente

Bordes: Para los bordes de las celdas

Relleno: Para el relleno de las celdas

Proteger: Para proteger los datos de una hoja Excel

Alineación

o Alinear en la parte Superior

o Alinear en el medio

o Alinear en la parte inferior

Tablas Tenemos datos en una hoja de Excel y queremos crear una tabla

La única condición es que el cursor (la celda activa) debe estar dentro de la tabla. Vamos a la ficha

insertar, grupo Tablas, tabla:


Página 19 de 66

Nos pregunta si la tabla tiene encabezados:

Tenemos el nombre de la tabla con las Herramientas de diseño de la tabla (Hay que estar en la tabla para ver

esta ficha). Quedaría:

Podemos hacer un filtro en los encabezados, ordenar los datos


Página 20 de 66

Si lo hacemos cambia de aspecto el icono:

Al igual que en Word para crear una fila nueva en la tabla nos posicionamos en la última celda y pulsamos

tabulador.

Ejercicio 09 Excel

Lo guardamos con el nombre de Ejercicio 09 Excel.xls, Trabajamos con Tablas. Como si fuese un

formulario

Distintos Tipos de formato de celda

Los tenemos en la ficha Inicio, grupo Número

Tenemos esta tabla:

Cantidad Formato Observaciones

25 General Sólo números enteros

25,00 Número Con decimales

25,00€ Moneda

05/04/2011 Fecha Corta

martes, 05 de abril de

2011

Fecha Larga

25% Porcentaje

Dentro del grupo Número podemos aumentar o disminuir decimales


Página 21 de 66

Ejercicio 10 Excel

Lo guardamos con el nombre de Ejercicio 10 Excel.xls, Ponemos los distintos tipos de formatos de datos.

Gráficos Veamos las posibilidades de gráficos en una hoja de cálculo. Lo sacamos de los datos de una hoja de datos

Un gráfico es un resumen de una cantidad de datos. Hay que decirle que datos entran en nuestro gráfico

Seleccionamos los datos y vamos a la ficha Insertar, grupo gráficos, allí elegimos el tipo de gráfico:

Para mover el grafico lo arrastramos con el ratón.


Página 22 de 66

Llevar gráficos a Word Queremos llevar el gráfico a un documento Word y que cuando modifiquemos el gráfico se modifique en

Word. No podemos hace un copiar-pegar directamente, de esta manera no se actualizaría el gráfico.

Pasos a seguir:

1. Botón derecho sobre el gráfico en Excel, opción copiar

2. Copiar en Word como pegado especial,

Se hace en Pegar vínculo, como Gráfico de Microsoft Excel Objeto

Si actualizamos los datos del gráfico en Excel y queremos abrir posteriormente el documento Word, nos pide

confirmación para actualizarlo:

Se puede actualizar con el documento de Word abierto si pulsamos botón derecho sobre el gráfico e indicamos

actualizar vínculo:

Ejercicio 11 Excel y Ejercicio 38 Word

Lo guardamos con el nombre de Ejercicio 11 Excel.xls, Trabajamos con los distintos tipos de gráficos.

Copiamos un gráfico de Excel en Word y vemos que si se actualizan los datos en Excel se actualizan en Word

cuando lo abrimos de nuevo pidiéndonos confirmación.


Página 23 de 66

Continuamos con Excel. Modificamos la barra de herramientas de acceso rápido.

Para ocultar la cinta de opciones, botón derecho sobre ella y Minimizar la cinta de opciones. Se hace igual

que en Word.

Si queremos visualizarla de nuevo, botón derecho y desmarcamos la opción anterior

Podemos escribir en la celda directamente o en la barra de las formulas

Tenemos el Zoom para ampliar o disminuir el tamaño de la hoja. Está en la parte inferior derecha de la hoja

de cálculo. Es igual que en Word.

Varias líneas en una celda

Para escribir varias líneas en una celda pulsamos la tecla alternativa y a la vez el Enter (Alt+enter).


Página 24 de 66

Corrección de ortografía

Seleccionamos el texto vamos a la ficha revisar, grupo Revisión y pulsamos ortografía.

Ordenación

Podemos ordenar las celdas, para ello nos posicionamos en una celda y vamos a Inicio, grupo Modificar

opción Ordenar y filtrar.

Formato condicional Podemos hacer una acción u otra dependiendo del resultado obtenido.

Para ello seleccionamos el conjunto de datos a los que quiero aplicar el formato condicional, vamos a la ficha

Inicio, grupo Estilos, Formato condicional:

Barra de datos: Nuestra los datos con una barra dentro de la celda

Escalas de color: Muestra los datos con distintos colores de relleno en la celda

Conjunto de iconos: Muestra los datos con iconos

En estos caso las reglas las pone el Excel automáticamente, pero si queremos ponerlas nosotros vamos a la

opción más reglas y las modificamos a nuestro interés


Página 25 de 66

Tablas dinámicas Reglas para trabajar con tablas dinámicas:

No se pueden dejar espacios en blanco, también los ordena. CUIDADO CON ESTO.

El cursor (La celda activa) debe estar dentro del conjunto de datos

El procedimiento para crear una tabla dinámica es el siguiente:

Vamos a la ficha Insertar, grupo Tablas, opción Tabla dinámica y muestra:

Nos muestra

Es recomendable crearla en una hoja nueva porque se suelen tener muchos datos

Confeccionamos nuestra tabla, marcando y arrastrando a la zona que nos convenga (Rotulo de columna, rotulo

de fila,…)

Para actualizar la tabla dinámica, hay que ir a Herramientas de tabla dinámica, Opciones, grupo Datos,

Actualizar.


Página 26 de 66

Ejercicio 12 Excel

Lo guardamos con el nombre de Ejercicio 12 Excel.xls, Trabajamos con una tabla, condicionales simples,

ordenaciones,…

Damos formato condicional a unos datos.

Insertamos una tabla

Podemos ordenar de mayor a menor, de menor a mayor, por colores

En otra hoja creamos una tabla y a partir de esta una tabla dinámica.

Repetimos la operativa en otra hoja de cálculo y con otra tabla.

Se aprecia que podemos filtra los datos por los dos filtros creados.

Todas las opciones de diseño que hemos visto para una tabla normal podemos aplicarla a la tabla

dinámica.


Página 27 de 66

Protección de datos en una hoja de cálculo

Protección de toda la hoja

Para proteger toda la hoja, vamos a la ficha Revisar, grupo cambios y pulsamos la opción proteger hoja

Nos muestra una pantalla que permite introducir una contraseña

Pide confirmación de la contraseña

Para desproteger vamos a Ficha Revisar, grupo cambiar y pulsamos desproteger hoja


Página 28 de 66

Protección de parte de la hoja

Paso a seguir una vez escrita la hoja e introducida las formulas pertinentes:

1. Seleccionamos la zona donde queremos permitir que se escriba. Con la tecla Control pulsada.

2. Vamos a la ficha Inicio, grupo Fuente, cuadro de dialogo pestaña proteger, desmarcamos bloqueada

(Desbloqueamos) y pulsamos botón aceptar

3. Vamos a la ficha Revisar, grupo cambios, opción proteger hoja

4. Podemos introducir una contraseña y nos pedirá confirmación.

5. Cuando tramos de escribir en un celda protegida muestra este mensaje


Página 29 de 66

Utilizar la impresora en Excel La primera reacción es ir al botón de office y ver las opciones:

Pero antes hay que indicar que quiero imprimir, para ello seleccionamos lo que queremos imprimir, vamos a

Diseño de página, grupo configurar página, área de impresión:

Si modificamos la escala ajusta el texto

Ejercicio 13 Excel

Lo guardamos con el nombre de Ejercicio 13 Excel.xls, Trabajamos con celdas protegidas en una hoja de

cálculo, Función PROMEDIO,…


Página 30 de 66

Formulas Veamos la ficha de formulas, tenemos estos grupos

Biblioteca de funciones, tenemos estas opciones

o Autosuma: Elegimos la que queramos, suma, promedio, contar números, máximo, mínimo,…

Si la función no está a primera vista pulsamos Más formulas

o usadas recientemente: Están las últimas que hemos usado, podemos insertar funciones,…

o financieras

o Lógicas

o De texto

o Fecha y hora

o Búsqueda y referencia

BUSCAR

BUSCARV

o Matemáticas y trigonométricas

o Más funciones: …


Página 31 de 66

Insertar función

Vamos a la Ficha Inicio, grupo Biblioteca de funciones, opción Insertar Función, nos muestra:

Escribimos el texto en buscar una función y damos enter.

Si sabemos en qué categoría está se lo indicamos.

Ejercicio 14 Excel

Lo guardamos con el nombre de Ejercicio 14 Excel.xls, Trabajamos con FUNCIONES, y con la función

CONTAR.SI que cuenta si se cumple una condición,…

Esta función se escribe así:

=CONTAR.SI(B3:B11;">2000") La condición siempre va entre comillas


Página 32 de 66

Continuamos con Excel. Vemos las plantillas que tiene preinstaladas

Vamos al botón de Office, nuevo, plantillas instaladas, Por ejemplo Facturas.

Una vez descargado tenemos la plantilla que hemos elegido.

Ejercicio 15 Excel

Lo guardamos con el nombre de Ejercicio 15 Excel.xls (Factura), Confeccionamos una factura en equipo.

Nombre: Empresa:

Dirección: Nº Factura

C. P.:

Guardar como plantilla una hoja Excel

Se guarda de la siguiente manera, en guardar como y seleccionamos el tipo de archivo como plantilla de Excel


Página 33 de 66

Inmovilizar paneles (Fijamos filas y columnas) Se hace cuando queremos fijar una columna (o varias) para ver siempre sus datos.

Nos posicionamos en una celda vamos a la ficha Vista, grupo Ventana y pulsamos inmovilizar, de manera

visualizamos todas la columnas a la izquierda que la celda activa y todas las filas por encima de la celda

activa.

Queda:

Para movilizar otra vez las filas-columnas, vamos a la ficha Vista, Grupo Ventana, opción inmovilizar

paneles, movilizar paneles.


Página 34 de 66

Ordenar ventanas múltiples (Varios libros abiertos a la vez) Podemos tener varios libros (Documentos abiertos a la vez) para ello vamos a la ficha Vista grupo Ventana,

opción organizar todo

Nos muestra una ventana para elegir como queremos los documentos:

Ejercicio 16 Excel

Lo guardamos con el nombre de Ejercicio 16 Excel.xls, Inmovilizamos filas y columnas, Función

CONTAR.SI(rango ; condición), tablas dinámicas.

Ejercicio 17 Excel

Lo guardamos con el nombre de Ejercicio 17 Excel.xls, Tablas dinámicas.

Más Funciones Veamos alguna función más:

Función Código Observaciones

SI =SI(E2<5;"No apto";”Apto”) Comprueba si se cumple una condición, devuelve un

valor si se evalúa como VERDADERO y otro si se evalúa

como FALSO

MODA =MODA(B2:B11) Devuelve el valor más frecuente o que más se repite en

un rango de datos

ia MEDIANA =MEDIANA(E2:E11) Devuelve el número central de un conjunto de datos.


Página 35 de 66

Ejercicio 18 Excel (Calificaciones con SI)

Lo guardamos con el nombre de Ejercicio 18 Excel.xls, Trabajamos con Funciones, MODA, MEDIANA,

SI...

=SI(E2<5;"Insuficiente";SI(E2<7;"Aprobado";SI(E2<9;"Notable";SI(E2<10;"Sobresaliente";"Excelente"))))

Continuamos con Excel.

Configuración Propiedades de un documento Excel Con el documento abierto vamos al botón de Office, Preparar, Propiedades

Nos muestra una ficha en la parte superior del área de trabajo que nos permite modificar las propiedades del

documento Excel.

Si damos a propiedades avanzadas tenemos mas información que podemos modificar


Página 36 de 66

Cambiar de ventana Si tenemos abiertos varios documentos de Excel podemos movernos de uno a otro en la ficha vista, grupo

ventana, opción cambiar ventanas


Página 37 de 66

Continuamos con Excel.

Asignación de nombre a un rango Podemos asignar un nombre a un rango de datos, lo primero que hacemos es seleccionar las celdas a las que

queremos asignar el nombre, vamos a la ficha formulas, opción Asignar nombre a un rango:

Nos muestra:

Damos el nombre, el ámbito y podemos poner algún comentario. Una vez hecho cambia el nombre en el

cuadro correspondiente:

Para trabajar con ellos, lo unico que hay que hacer es referenciarlo en las formulas correspondientes, por

ejemplo:

=PROMEDIO(Notas2)

=SUMA(Notas2)

=PROMEDIO(Notas1)

=SUMA(Notas3)


Página 38 de 66

Para quitarlos vamos a la ficha formulas, grupo nombres definidos, opción administrador de nombres y

vemos todos los nombres de rango, si seleccionamos el que queremos nos da la posibilidad de editarlo

(Modificarlo) y eliminarlo. Podemos desde aqui crear uno nuevo.

Se puede hacer con celdas adyacentes o con celdas que no esten consecutivas.

Aplicamos esto en el Ejercicio 18 Excel.xls


Lo guardamos con el nombre de Ejercicio 19 Excel.xls, Trabajamos con Funciones, MODA, asignar nombre

a un rango, formato condicional, gráficos,….

Más sobre Gráficos

Para cambiar cosas sobre el gráfico botón derecho sobre él:

Podemos cambiar el rango, quitar o agregar entradas al gráfico,...


Página 39 de 66

Insertar imágenes en Excel Se hace desde la ficha Insertar, grupo Ilustraciones:

Es igual que en Word.

Se puede hacer simetría, se sujeta una esquina y se pasa como la hoja de un libro, también se hace desde aquí:

Se pueden agrupar imágenes (Objetos) para tratarlos como si fueran un solo objeto (Imagen), para ello

seleccionado las dos imágenes (con la tecla control), voy a las herramientas de imagen, formato, organizar

agrupar. Se puede hacer también con el botón derecho, agrupar.

De la misma manera se puede desagrupar.

A las imágenes se les puede dar formas. Vamos a Herramientas de imagen, formato, forma de la imagen.


Página 40 de 66

Auditoria de formulas Se hace desde la ficha Fórmulas, grupo Auditorías de fórmulas

Podemos rastrear tanto los precedentes como los dependientes, quitar las fechas que lo indican, Mostrar las

fórmulas, comprobar errores, evaluar fórmulas…

Cuidado con el grupo Cálculo, opciones para el cálculo, porque si seleccionamos la opción Manual, NO

actualiza las formulas de la hoja automáticamente, Hay veces que interesa tenerlo así, por defecto muestra

Automático.

Validación de datos

Para ello seleccionamos las celdas a validar y vamos a la ficha datos, grupo Herramientas de datos, opción

validación de datos


Página 41 de 66

Nos sale la ventana de validación:

Cuidado porque con esto no validamos los datos ya introducidos, pero podemos poner un circulo alrededor de

ellos. Podemos quitar los circulos con la otra opción.

Hipervinculos Para crear hipervínculos, botón derecho sobre el texto, opción Hipervínculo:

Indicamos a donde queremos ir:


Página 42 de 66

También podemos hacerlo desde la ficha Insertar, grupo Vínculos

Ejercicio 20 Excel

Lo guardamos con el nombre de Ejercicio 20 Excel.xls, Trabajamos con hipervínculos


Página 43 de 66

Macros en Excel Son igual que en Word, Ficha Vista, grupo Macro, opción Grabar macro

Para ejecutar la macro, ver macros

Botón Ejecutar


Página 44 de 66

Buscar y seleccionar

Es igual que en Word, ficha Inicio, grupo Modificar, opción buscar y seleccionar

Tenemos:

Ir a, Ir especial

Ir a: Está en el mismo grupo que buscar y seleccionar, permite ir a la referencia que indiquemos.

Ir especial: Permite ir a celdas con formato específico

Buscar y seleccionar: Fórmulas, comentarios, formatos condicionales, constantes, validación de

datos

Podemos buscar y seleccionar todas estas opciones en el grupo de Buscar y seleccionar.


Página 45 de 66

Selección de Celdas en Excel

Para seleccionar una fila marcamos con el puntero del ratón en el nombre de la fila, para seleccionar varias

filas, lo hacemos con la tecla control. De la misma manera seleccionamos las columnas. Depende de la

necesidad que tengamos. Esto es útil sobre todo para dar el mismo formato a las celdas seleccionadas.

Vimos los rangos de celdas.

Para seleccionar toda la hoja hacemos clic en la parte superior izquierda

Con las teclas tenemos:

Tecla Acción

Control + Barra espaciadora Selecciona la columna de la celda activa

Mayúscula + Barra espaciadora Selecciona la fila de la celda activa

Control + Mayúscula+ inicio Selecciona el rectángulo comprendido entre la celda hasta el inicio de

la hoja

ahoja Control + Mayúscula+ fin Selecciona el rectángulo comprendido entre la celda y la última celda

utilizada en la hoja. Control + Mayúscula + Av Pág Avanzo una página, voy a la siguiente hoja

Control +Mayúscula + Re Pág Retrocedo una página, vuelvo a la hoja anterior

Con doble clic Ajustamos el ancho de la columna y el alto de la fila

Alt + enter Permite escribir dos líneas en una celda

Insertar celdas, filas y columnas Botón derecho insertar, desplaza hacia donde indiquemos lo afectado

por la inserción. Eliminar celdas, filas y columnas Botón derecho eliminar

Opciones de Ortografía Por defecto usamos la ortografía tal como viene, podemos modificar estas opciones en botón de office, botón

opciones de Excel

Muestra la siguiente pantalla una vez que elegimos revisar:


Página 46 de 66

En opciones de autocorrección:

CUIDADO con lo que cambiamos con estas opciones porque se cambia a nivel de Excel no a nivel de

usuario.

Para revisar la ortografía de una hoja Excel vamos a la ficha Revisar, grupo Revisión, opción Ortografía


Página 47 de 66

Función BUSCARV y BUSCAR H

Para BUSCARV

Busca un valor específico en la primera columna de una matriz de tabla y devuelve, en la misma fila, un valor de otra

columna de dicha matriz de tabla.

La V de BUSCARV significa vertical. Utilice BUSCARV en lugar de BUSCARH si los valores de comparación se

encuentran en una columna situada a la izquierda de los datos que desea buscar.

La sintaxis es: =BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

Donde:

Valor_buscado Valor que se va a buscar en la primera columna de la matriz de tabla. Valor_buscado puede ser un valor

o una referencia. Si valor_buscado es inferior al menor de los valores de la primera columna de matriz_buscar_en,

BUSCARV devuelve al valor de error #N/A.

Matriz_buscar_en Dos o más columnas de datos. Use una referencia a un rango o un nombre de rango. Los valores de

la primera columna de matriz_buscar_en son los valores que busca valor_buscado. Estos valores pueden ser texto,

números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes.

Indicador_columnas Número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el

argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento

matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de

matriz_buscar_en y así sucesivamente. Si indicador_columnas es:

Si es inferior a 1, BUSCARV devuelve al valor de error #VALUE!

Si es superior al número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #REF!

Ordenado Valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada:

Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no localiza ninguna

coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado.

Los valores de la primera columna de matriz_buscar_en deben estar clasificados según un criterio de

ordenación ascendente; en caso contrario, es posible que BUSCARV no devuelva el valor correcto. Para

obtener más información, vea Ordenar datos.


Página 48 de 66

Si es FALSO, BUSCARV sólo buscará una coincidencia exacta. En este caso, no es necesario ordenar los

valores de la primera columna de matriz_buscar_en. Si hay dos o más valores en la primera columna de

matriz_buscar_en, se utilizará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se

devolverá el valor de error #N/A.

Esta fórmula no suma, es útil en una lisa donde no hay duplicados.

Para BUSCARH:

Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma

columna de una fila especificada en la tabla o matriz. Use BUSCARH cuando los valores de comparación se encuentren

en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número

especificado de filas. Use BUSCARV cuando los valores de comparación se encuentren en una columna a la izquierda o

de los datos que desee encontrar.

La H de BUSCARH significa "Horizontal".

La sintaxis es: =BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado)

Donde:

Valor_buscado es el valor que se busca en la primera fila de la tabla. Valor_buscado puede ser un valor, una referencia

o una cadena de texto.

Matriz_buscar_en es una tabla de información en la que se buscan los datos. Utilice una referencia a un rango o el

nombre de un rango.

Los valores de la primera fila del argumento matriz_buscar_en pueden ser texto, números o valores lógicos.

Si ordenado es VERDADERO, los valores de la primera fila de matriz_buscar_en deben colocarse en orden

ascendente: ...-2, -1, 0, 1, 2,..., A-Z, FALSO, VERDADERO; de lo contrario, BUSCARH puede devolver un

valor incorrecto. Si ordenado es FALSO, no es necesario ordenar matriz_buscar_en.

El texto en mayúsculas y en minúsculas es equivalente.

Ordena los valores en orden ascendente, de izquierda a derecha. Para obtener más información, vea Ordenar

datos.

Indicador_filas es el número de fila en matriz_buscar_en desde el cual debe devolverse el valor coincidente. Si

indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de

la segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es menor que 1, BUSCARH devuelve el

valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, BUSCARH devuelve

el valor de error #¡REF!

Ordenado es un valor lógico que especifica si BUSCARH debe localizar una coincidencia exacta o aproximada. Si es

VERDADERO o se omite, devolverá una coincidencia aproximada. Es decir, si no encuentra ninguna coincidencia

exacta, devolverá el siguiente valor mayor que sea inferior a valor_buscado. Si es FALSO, BUSCARH encontrará una

coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error #N/A.


Página 49 de 66

Función BUSCAR

Es importante indicar que los datos donde buscamos tiene que estar ordenados ascendentemente, de menor a

mayor, de no ser así, no funciona correctamente.

Devuelve un valor procedente de un rango de una fila o columna, o bien, de una matriz. La función BUSCAR tiene dos

formas de sintaxis: vectorial y matricial.

Si desea Vea Uso

Buscar en un rango de una fila o una columna (denominado vector) un valor y devolver un valor desde la misma posición en un segundo rango.

Forma vectorial Utilice la forma vectorial cuando disponga de una amplia lista de valores en los que buscar o cuando es posible que los valores cambien con el tiempo.

Buscar el valor especificado en la primera fila o columna de una matriz y devolver un valor desde la misma posición en la última fila o columna de la matriz.

Forma matricial Utilice esta forma cuando disponga de una pequeña lista de valores y éstos permanezcan siempre constantes.

NOTA También se puede utilizar la función BUSCAR como alternativa a la función SI para crear pruebas o pruebas

que excedan el límite de funciones anidadas. Vea los ejemplos de la forma matricial.

Forma vectorial

Un vector es un rango de una sola fila o columna. La forma vectorial de BUSCAR busca un valor en un rango

(denominado vector) y devuelve otro valor desde la misma posición en un segundo rango. Utilice esta forma de la

función BUSCAR cuando desee especificar el rango que incluya los valores que desea localizar. Con la otra forma de la

función se busca en la primera columna o fila.

BUSCAR(valor_buscado;vector_de_comparación;vector_resultado)

Donde:

Valor_buscado Valor que busca la función BUSCAR en el primer vector. Valor_buscado puede ser un número, texto,

un valor lógico, o un nombre o referencia que se refiere a un valor.

Vector_de_comparación Rango que sólo contiene una fila o una columna. Los valores del vector_de_comparación

pueden ser texto, números o valores lógicos.

IMPORTANTE Los valores en el vector_de_comparación deben colocarse en orden ascendente (Ordenados): ...;-2; -1;

0; 1; 2; ...; A-Z; FALSO; VERDADERO; de lo contrario, BUSCAR puede dar un valor incorrecto. El texto en

mayúsculas y en minúsculas es equivalente.

Vector_resultado Rango que sólo incluye una fila o una columna. Debe ser del mismo tamaño que

vector_de_comparación.


Página 50 de 66

Observaciones

Si con BUSCAR no se encuentra el valor_buscado, se utilizará el mayor valor de vector_de_comparación que

sea menor o igual que valor_buscado.

Si valor_buscado es menor que el valor más bajo de vector_de_comparación, BUSCAR devuelve el valor de

error #N/A.

Ejemplo

Es más sencillo comprender el ejemplo si se copia en una hoja de cálculo en blanco.

Cómo copiar un ejemplo

1. Cree un libro o una hoja de cálculo en blanco.

2. Seleccione el ejemplo en el tema de Ayuda.

NOTA No seleccione los encabezados de fila ni de columna.

Seleccionar un ejemplo de la Ayuda

3. Presione CTRL+C.

4. En la hoja de cálculo, seleccione la celda A1 y presione CTRL+V.


Página 51 de 66

5. Para cambiar entre ver los resultados y las fórmulas que devuelven los resultados, presione Alt+º (ordinal

masculino) o bien, en el grupo Auditoría de fórmulas de la ficha Fórmulas, haga clic en el botón Mostrar

fórmulas.

1

2

3

4

5

6

A B

Frecuencia Color

4,14 rojo

4,19 anaranjado

5,17 amarillo

5,77 verde

6,39 azul

Fórmula Descripción (resultado)

=BUSCAR(4,19;A2:A6;B2:B6) Busca 4,19 en la columna A y devuelve el valor de la columna B que está en la misma fila (anaranjado)

=BUSCAR(5,00;A2:A6;B2:B6) Busca 5,00 en la columna A, encuentra el siguiente valor inferior (4,19) y devuelve el valor de la columna B que está en la misma fila (anaranjado)

=BUSCAR(7,66;A2:A6;B2:B6) Busca 7,66 in en la columna A, encuentra el siguiente valor inferior (6,39) y devuelve el valor de la columna B que está en la misma fila (azul)

=BUSCAR(0;A2:A6;B2:B6) Busca 0 en la columna A y devuelve un error, ya que 0 es menor que el valor más bajo del vector_de_comparación A2:A7 (#N/A)

Forma matricial

Con la forma matricial de BUSCAR se busca el valor especificado en la primera fila o columna de una matriz y se

devuelve un valor desde la misma posición en la última fila o columna de la matriz. Utilice esta forma cuando los valores

que desee buscar se encuentren en la primera fila o columna de la matriz. La otra forma de la función se emplea cuando

se desea especificar la ubicación de la fila o columna.

SUGERENCIA En general, resulta más adecuado utilizar la función BUSCARH o BUSCARV en lugar de la forma

matricial de BUSCAR. Esta forma se proporciona por su compatibilidad con otros programas para hojas de cálculo.

BUSCAR(valor_buscado;matriz)

Donde: Valor_buscado Valor que busca la función BUSCAR en una matriz. Valor_buscado puede ser un número,

texto, un valor lógico o un nombre o referencia que se refiera a un valor.

Si con BUSCAR no se puede encontrar el valor_buscado, se utiliza el mayor valor de la matriz que sea menor o

igual que el valor_buscado.

Si el valor_buscado es menor que el valor más bajo de la primera fila o columna (dependiendo de las

dimensiones de la matriz), BUSCAR devolverá el valor de error #N/A.


Página 52 de 66

Matriz Rango de celdas que incluye el texto, los números o los valores lógicos que se desean comparar con

valor_buscado.

La forma matricial de BUSCAR es muy parecida a la de las funciones BUSCARH y BUSCARV. La diferencia es que

BUSCARH busca valor_buscado en la primera fila, BUSCARV busca en la primera columna y BUSCAR busca de

acuerdo con las dimensiones de la matriz.

Si la matriz cubre un área que es más ancha que alta (más columnas que filas), BUSCAR buscará

valor_buscado en la primera fila.

Si la matriz es cuadrada o más alta que ancha (tiene más filas que columnas), BUSCAR buscará en la primera

columna.

Con BUSCARH y BUSCARV se puede especificar un índice en vertical o en horizontal, pero BUSCAR

siempre selecciona el último valor de la fila o columna.

IMPORTANTE Los valores de la matriz deben colocarse en orden ascendente: ...;-2; -1; 0; 1; 2; ...; A-Z; FALSO;

VERDADERO; de lo contrario, BUSCAR puede dar un valor incorrecto. El texto en mayúsculas y en minúsculas es

equivalente.

Ejemplo 1







3. Presione CTRL+C.



Página 53 de 66



fórmulas.

1

A B


=BUSCAR("C";{"a";"b";"c";"d"\1;2;3;4}) Busca "C" en la primera fila de la matriz, encuentra el valor superior que es menor o igual que ("c") y, a continuación, devuelve el valor en la última fila que está en la misma columna (3)

=BUSCAR("balda";{"a";1\"b";2\"c";3}) Busca "balda" en la primera fila de la matriz, encuentra el valor superior que es menor o igual que ("b") y, a continuación, devuelve el valor en la última columna que está en la misma fila (2)

Ejemplo 2







3. Presione CTRL+C.




fórmulas.

En el siguiente ejemplo se utiliza una matriz de números para asignar una puntuación a un resultado.

1

A

Puntuación


Página 54 de 66

2

3

4

45

90

78


=BUSCAR(A2;{0;60;70;80;90},{"F";"D";"C";"B";"A"}) Busca el valor de A2 (45) en la primera fila de la matriz, encuentra el valor superior que es menor o igual (60) y, a continuación, devuelve el valor en la última fila de la matriz que está en la misma columna (F)

=BUSCAR(A3;{0;60;70;80;90},{"F";"D";"C";"B";"A"}) Busca el valor de A3 (90) en la primera fila de la matriz, encuentra el valor superior que es menor o igual (90) y, a continuación, devuelve el valor en la última fila de la matriz que está en la misma columna (A)

=BUSCAR(A4;{0;60;70;80;90},{"F";"D";"C";"B";"A"}) Busca el valor de A4 (78) en la primera fila de la matriz, encuentra el valor superior que es menor o igual (80) y, a continuación, devuelve el valor en la última fila de la matriz que está en la misma columna (C)

=BUSCAR(A2;{0;60;63;67;70;73;77;80;83;87;90;93;97},{"F";"D-";"D";"D+";"C-";"C";"C+";"B-";"B";"B+";"A-";"A";"A+"})

Busca el valor de A2 (45) en la primera fila de la matriz, encuentra el valor superior que es menor o igual (60) y, a continuación, devuelve el valor en la última fila de la matriz que está en la misma columna (F)


Busca el valor de A3 (90) en la primera fila de la matriz, encuentra el valor superior que es menor o igual (90) y, a continuación, devuelve el valor en la última fila que está en la misma columna (A-)


Busca el valor de A4 (78) en la primera fila de la matriz, encuentra el valor superior que es menor o igual (80) y, a continuación, devuelve el valor en la última fila que está en la misma columna (C+)

Ejercicio 21 Excel

Lo guardamos con el nombre de Ejercicio 21 Excel.xls, Trabajamos con funciones BUSCARV, BUSCARH y

BUSCAR.

Datos de otra hoja de cálculo Para hacer referencia a datos que están en otra hoja de cálculo lo indicamos poniendo el nombre de la hoja

justo delante de los datos y separados por admiración invertida (!)

Ejemplo =BUSCAR(B2;Hoja1!$A$11:$B$14)

Ejercicio 22 Excel

Lo guardamos con el nombre de Ejercicio 22 Excel.xls, Trabajamos con la función BUSCAR. Buscamos los

datos en una hoja diferente. Buscamos en otra columna que no sea la 2.


Lo guardamos con el nombre de Ejercicio 23 Excel.xls, Trabajamos con funciones Total (SUMA),

PROMEDIO, Máximo (MAX), mínimo (MIN). Mostramos un gráfico y lo llevamos a Word


Página 55 de 66

Ejercicio 24 Excel

Lo guardamos con el nombre de Ejercicio 24 Excel.xls, Trabajamos con la tantos por ciento. Creamos una

factura.

Ejercicio 25 Excel (Calificaciones con SI)

Lo guardamos con el nombre de Ejercicio 25 Excel.xls, Trabajamos con Calificaciones.

=SI(F6<5;"Insuficiente";SI(F6<6;"Aprobado";SI(F6<7;"Bien";SI(F6<9;"Notable";SI(F6<10;"Sobresaliente";"Excelente")))))

Agregar una impresora Vamos a inicio, impresoras y fases

Si no tenemos ninguna nos muestra un asistente y si ya tenemos alguna hay que agregar otra

Sale el asistente:


Página 56 de 66

Examinamos y buscamos la impresora.

Función SUMAR.SI

Esta función suma las celdas en el rango que coinciden con el argumento criterio.

Sintaxis: =SUMAR.SI(rango;criterio;rango_suma)

Donde:

rango es el rango de celdas que se desea evaluar según los criterios especificados. Las celdas de cada rango deben ser

números, o bien nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se

tienen en cuenta.

criterio es el criterio en forma de número, expresión o texto, que determina las celdas que se van a sumar. Por ejemplo,

los criterios pueden expresarse como 32, "32" ">32" o "manzanas".

rango_suma son las celdas reales que se deben sumar si las correspondientes celdas incluidas en el rango cumplen los

criterios. Si se omite el argumento rango_suma, las celdas del rango se evaluarán según los criterios y se sumarán si

cumplen los criterios.


Página 57 de 66

Observaciones

No es necesario que rango_suma tenga el mismo tamaño y forma que rango. Las celdas reales que se deben

sumar se determinan utilizando la celda superior izquierda de rango_suma como la celda inicial e incluyendo

las celdas que corresponden con el tamaño y la forma del rango. Por ejemplo:

Si rango es y rango_suma es las celdas reales serán

A1:A5 B5:B15 B5:B15

A1:A5 B1:B3 B5:B15

A1:B4 C1:D4 C1:D4

A1:B4 C1:C2 C1:D4

En los criterios se puede utilizar los caracteres comodín de signo de interrogación (?) y asterisco (*). El signo

de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de

caracteres. Si desea buscar un signo de interrogación o un asterisco reales, escriba una tilde (~) antes del

carácter que desea buscar.

Ejemplo

El ejemplo será más fácil de entender si lo copia a una hoja de cálculo en blanco.






3. Presione CTRL+C.



Página 58 de 66

5. Para cambiar entre ver los resultados y ver las fórmulas que devuelven los resultados, presione CTRL+` (acento

grave), o en la ficha Fórmulas, en el grupo Auditoría de fórmulas, haga clic en el botón Mostrar fórmulas.

1

2

3

4

5

A B

Valor de propiedad Comisión

100.000 7.000

200.000 14.000

300.000 21.000

400.000 28.000


=SUMAR.SI(A2:A5;">160000";B2:B5) Suma las comisiones correspondientes a los valores de propiedad superiores a 160.000 (63.000)

=SUMAR.SI(A2:A5;">160000") Suma los valores de propiedad superiores a 160.000 (900.000)

=SUMAR.SI(A2:A5;"=300000";B2:B3) Suma las comisiones correspondientes a los valores de propiedad iguales a 300.000 (21.000)

Ejercicio 26 Excel (Días vividos)

Lo guardamos con el nombre de Ejercicio 26 Excel.xls, Trabajamos con Fechas. Con la función SUMAR.SI y

algunas cosas más.

Funciones de fecha y hora

Para trabajar con fechas y horas tenemos algunas formulas interesantes:

Función Acción

=AÑO(A3)-AÑO(A2) Años transcurridos entre dos fechas

=(AÑO(A3)-AÑO(A2))*12+MES(A3)-MES(A2) Meses transcurridos entre dos fechas

=A3-A2 Días entre las dos fechas

=DIAS.LAB(A2;A3) Días laborables entre las dos fechas

=AHORA() Da la fecha y hora actuales

=HOY() Da la fecha actual

=DIA(HOY()) Da el día actual

=DIASEM(HOY()) Da el día de la semana, de 1 (domingo) a 7( sábado)

Para calcular los años, meses y días vividos usamos esta fórmula:

=SIFECHA(A17;HOY();"y")&"Año(s), "&SIFECHA(A17;HOY();"ym")&"Mes(es) y

"&SIFECHA(A17;HOY();"md")&"Día(s)"

En A17 está la fecha de nacimiento


Página 59 de 66

CURIOSIDAD sobre la función SIFECHA:

La función SIFECHA es un caso aparte. No por su simpleza o complejidad. Simplemente, porque desde Excel 5.0 no está

documentada. Es una función secreta, por decirlo de algún modo. En efecto, si buscamos la función en el listado de

funciones de Excel, veremos que no aparece, aún si como categoría seleccionamos Todas. Tampoco aparece en la ayuda

on-line de Excel. Supuestamente (y esto es solo un mito urbano) Microsoft mantiene oculta esta función porque planea

desaparecerla, ya que es una función originaria de Lotus. Por si fuera poco, está mal traducida, ya que su nombre

original, en inglés, es DATEDIF (date diference), no DATEIF.

SIFECHA devuelve la diferencia entre dos fechas, expresada en determinado intervalo. La sintaxis:

=SIFECHA(fecha_1, fecha_2, intervalo)

fecha_1 y fecha_2 deben ser fechas válidas, de otra forma, obtendremos un error #¡VALOR!. fecha_1 debe ser menor

(más antigua) que fecha_2. Si no es así, se obtiene #¡NUM! El argumento intervalo específica la unidad de medida en la

que Excel devolverá el resultado. Puede ser uno de los siguientes valores:

"m" - meses. Número de meses completos entre fecha_1 y fecha_2.

"d" - días. Número de días entre fecha_1 y fecha_2.

"y" - años. Número de años completos entre fecha_1 y fecha_2.

"ym" - meses excluyendo años. Número de meses entre fecha_1 y fecha_2, suponiendo que fecha_1 y fecha_2 son del

mismo año.

"yd" - días excluyendo años. Número de días entre fecha_2 y fecha_2, suponiendo que fecha_1 y fecha_2 son del mismo

año.

"md" - días excluyendo meses y años. Número de días entre fecha_2 y fecha_2, suponiendo que fecha_1 y fecha_2 son

del mismo mes y del mismo año.

Ejercicio 27 Excel

Lo guardamos con el nombre de Ejercicio 27 Excel.xls, Trabajamos con Formato condicional, primero

seleccionamos las celdas sobre las que actuar y luego indicamos las condiciones. Vemos como seleccionar

valores duplicados, valores únicos.

Ejercicio 28 Excel (Tablas dinámicas - Ganadería)

Lo guardamos con el nombre de Ejercicio 28 Excel.xls, Trabajamos con tablas y tablas dinámicas.


Página 60 de 66

Referencias en Excel. Relativa, Absoluta y Mixta

Referencias relativas

Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula y

de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la

referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma

predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia una referencia relativa de la

celda B2 a la celda B3, se ajusta automáticamente de =A1 a =A2.

Referencias absolutas

Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación

específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable.

Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas

fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas. Por ejemplo, si copia una

referencia absoluta de la celda B2 a la celda B3, permanece invariable en ambas celdas =$A$1.

Referencias mixtas

Una referencia mixta tiene una columna absoluta y una fila relativa, o una fila absoluta y una columna relativa. Una

referencia de columna absoluta adopta la forma $A1, $B1, etc. Una referencia de fila absoluta adopta la forma A$1, B$1,

etc. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia relativa y la referencia absoluta

permanece invariable. Si se copia la fórmula en filas o columnas, la referencia relativa se ajusta automáticamente y la

referencia absoluta no se ajusta. Por ejemplo, si se copia una referencia mixta de la celda A2 a B3, se ajusta de =A$1 a

=B$1.

Ejercicio 29 Excel (Tablas y más)

Lo guardamos con el nombre de Ejercicio 29 Excel.xls, Trabajamos con tablas. Hoja de almacén. Referencias

Relativas, Mixtas y Absolutas.

Suma de cantidades de varias hojas de cálculo

=SUMA('Hoja Almacén'!C15;'Tabla Dinámica Hoja Almacén'!B20;Referencias!C33)

Hoja contable


Página 61 de 66

Mostrar u ocultar todos los valores cero en una hoja de cálculo

Tenemos que seguir estos pasos:

1. Haga clic en el botón de Microsoft Office , haga clic en Opciones de Excel y, a continuación, haga clic en la

categoría Avanzadas.

2. En Mostrar opciones para esta hoja, seleccione una hoja de cálculo y, a continuación, siga uno de los

procedimientos siguientes:

Para mostrar los valores cero (0) en las celdas, active la casilla de verificación Mostrar un cero en celdas

que tienen un valor cero.

Para mostrar valores cero como celdas en blanco, desactive la casilla de verificación Mostrar un cero en

celdas que tienen un valor cero.

IMPORTANTE: Esto tiene efecto en la hoja seleccionada, no en todo el libro.

Utilizar un formato numérico para ocultar los valores cero en las celdas seleccionadas

Siga este procedimiento para ocultar los valores cero en las celdas seleccionadas. Si cambia el valor de una de estas

celdas a un valor que no es cero, el formato del valor será similar al formato de número general.

1. Seleccione las celdas que contengan los valores cero (0) que se desee ocultar.

2. En el grupo Celdas de la ficha Inicio, haga clic en Formato y luego en Formato de celdas.

3. En la lista Categoría, haga clic en Personalizada.

4. En el cuadro Tipo, escriba 0;-0;;@.

NOTAS

Los valores ocultos sólo aparecen en la barra de fórmulas o en la celda si realiza cambios dentro de

la celda, y no se imprimen.

En el grupo Celdas de la ficha Inicio, haga clic en Formato y luego en Formato de celdas.

Para que aparezcan de nuevo los valores ocultos, seleccione las celdas y, a continuación, en la ficha Inicio, en el

grupo Celdas, elija Formato y haga clic en Formato de celdas. En la lista Categoría, haga clic en General para

aplicar el formato de número predeterminado. Para que aparezca de nuevo una fecha o una hora, seleccione el

formato de fecha u hora correspondiente en la ficha Número.

Ejercicio 30 Excel (Tabla Dinámica)

Lo guardamos con el nombre de Ejercicio 30 Excel.xls, Trabajamos con tablas dinámicas.


Página 62 de 66

Auditoría de formulas

Está en la ficha formulas, grupo de herramientas auditoría de formulas. Sirve para detectar problemas que

tenemos con formulas,

Rastrear precedentes: Muestra una flecha indicando de donde viene la formula

Mostrar formulas: Aparecen todas las formulas de la hoja para poder revisarlas. Para

quitarlas se pulsa de nuevo en mostrar formulas.

Comprobación de errores: Nos dice la celda donde está el error y la descripción del error.

□ Podemos tener ayuda sobre el error

□ Podemos Mostrar los pasos del cálculo

□ Podemos omitir el error

□ Podemos modificar la formula en la barra de formulas

Comprobación de errores: Nos dice la celda donde está el error y la descripción del error.

Aunque no está en ese grupo es interesante también la validación de datos:

Validación: Ficha de datos, grupo herramientas de datos, opción validación de datos.

Evita la entrada de datos no validos en un rango seleccionado.

□ Podemos indicar el mensaje de entrada. Es similar a los comentarios.

□ Podemos indicar el mensaje de error.

Ejercicios 31, 32 y 33 de Excel y Ejercicio 45 Word

Lo guardamos con el nombre de Ejercicio 31(32, 33) Excel.xls, Ejercicio para mejorar la productividad

Función promedio

Añadimos un comentario, Ficha vista, grupo comentarios, opción añadir comentarios. También

botón derecho añadir comentario.

Para modificar comentario, botón derecho y modificar comentario. También en la ficha vista,

grupo comentarios, modificar comentario. CUIDADO: Para que muestre esta opción tiene que

existir el comentario ya.


Página 63 de 66

Auditoría de formulas.

Gráficos. Ficha insertar, grupos Gráficos, el gráfico que nos interese.

Llevar el gráfico a un documento Word. Pasos

o Copiar el grafico de Excel

o Pegado especial en Word. Pegar vinculo, gráfico de Microsoft Excel Objeto

Hipervínculos: Botón derecho Hipervínculo, Indicamos donde queremos ir (A un lugar de este

documento, la hoja donde vamos y la celda donde nos posicionamos)

Seguridad: Ficha revisar, grupo cambios, opción Proteger hojas, de esta manera sólo protegemos la

hoja

Indicamos la contraseña si lo creemos conveniente, en este caso tendremos que repetirla

¿¿¿De la misma manera protegemos el libro???


Página 64 de 66

Proteger parte de la hoja: Seleccionamos las celdas donde podemos escribir, vamos a la ficha inicio,

grupo Fuente, menú contextual, pestaña proteger y desmarcamos. A continuación vamos a Revisar

proteger hoja. De esta forma sólo podemos escribir en las celdas seleccionadas.

Macros: Son tareas repetitivas que se hacen a menudo. Para empezar a grabar la macro:

o Vamos a la ficha Programador, grupo Código opción grabar macro ó

o Vamos a la ficha Vista, grupo Macros, opción grabar macro ó

o Vamos al icono que aparece en la parte inferior izquierda

Nos muestra esta ventana para poner el nombre de la macro, el método abreviado para ejecutarla,

donde queremos guardarla y una pequeña descripción de la misma

o Para parar de grabar la macro, vamos al icono que aparece en la parte inferior izquierda que

ha cambiado de aspecto

o Activamos la macro con la ficha Vista, grupo Macros, ver macros, la seleccionamos y la

ejecutamos

Ejercicio 34 de Excel

Lo guardamos con el nombre de Ejercicio 34 Excel.xls, trabajamos con gráficos

Cambiamos el nombre de la serie con los siguientes pasos consecutivos: Botón derecho, seleccionar

datos, entradas de leyendas, seleccionamos la serie, pulsamos editar y cambiamos el nombre.

Cambiamos el tipo de gráfico: Con el gráfico seleccionado, Ficha Herramientas de gráficos, pestaña

diseño, grupo tipo, opción cambiar tipo de gráfico.

Cambiar datos (Ampliar o decrementar datos): Botón derecho, seleccionar datos, rango de datos

del gráfico e indicamos el nuevo rango.

Cambiar las dimensiones: Con el gráfico seleccionado, Ficha Herramientas de gráficos, pestaña

formato, grupo tamaño, cambiamos el ancho y la altura.

Insertar un cuadro de texto: Vamos a la ficha Insertar, grupo Texto, opción cuadro de texto, el

cursor cambia de forma y lo ponemos donde queramos.

Insertar imagen, formas, ... : Ficha Insertar, grupo ilustraciones.


Página 65 de 66

Tipos de Errores en Excel Al estar depurando alguna fórmula, es posible que obtengamos un resultado de error, es decir, un valor que

comienza con un signo #. Esto no siempre es malo (de hecho, puede ser un resultado correcto). Si sabemos

interpretar el error, podremos corregirlo fácilmente. Téngase en cuenta que para deshacerse del error puede ser

necesario modificar ya sea la fórmula misma, o bien alguna de las celdas a las que hace referencia la fórmula.

En Excel existen siete resultados de error:

#¡N/A!

#¡REF!

#¡NUM!

#¿NOMBRE?

#¡DIV/0!

#¡VALOR!

#¡NULO!

Veamos que significa cada uno de ellos. De esta manera, podremos depurarlo y corregirlo fácilmente.

#¡N/A!

Este error se produce cuando una fórmula de búsqueda o referencia no encuentra ninguna coincidencia exacta

en la correspondiente matriz de búsqueda. Significa que el valor buscado no existe en la matriz de búsqueda.

#¡REF!

Este tipo de error surge cuando tenemos una referencia de celda inválida en la fórmula. Por ejemplo, en la

fórmula: =BUSCARV("mi_string",A2:B8,3,FALSO), obtenemos #¡REF! ya que no podemos buscar en la

tercera columna de una matriz que solo tiene dos columnas. En esta otra: =DESREF(Hoja1!A1, -1,0,1,1)

también obtenemos #¡REF! ya que no hay ninguna fila encima de la celda A1. Siguiendo con esta fórmula, si

eliminamos la primera fila de la hoja "Hoja1", o si eliminamos la Hoja1, la fórmula mostrará #¡REF!, ya que

se ha "perdido" la referencia a la celda Hoja1!A1.

#¡NUMERO!

Este se produce cuando ingresamos algún valor no numérico como un argumento de función que Excel espera

que sea argumento numérico (o una referencia a un valor numérico). Otra posibilidad es ingresar un número

inválido, como uno negativo cuando se espera uno positivo, o un 2 cuando el argumento solo admite 0 ó 1. La

fórmula =COINCIDIR(123, B1:B10,3) devuelve #NUM!, ya que el último argumento de COINCIDIR solo

puede ser -1, 0 ó 1.

#¡NOMBRE!

Este error lo obtenemos cuando escribimos mal el nombre de alguna función. También puede surgir cuando

utilizamos alguna función personalizada y tenemos deshabilitadas las macros o el complemento

correspondiente. Otra situación que dispara este error es el escribir mal el nombre de algún rango nombrado.

La fórmula =SUMARSI(A2:A10,"criterio",C2:C10) devuelve #¡NOMBRE! porque la función SUMARSI no

existe (más bien está mal escrita. Lo correcto es SUMAR.SI). Finalmente puede suceder también que no

utilizamos comillas al ingresar un argumento de texto.

#¡DIV/0!

Este es fácil. Se produce al hacer una división por cero, o bién, por una referencia a un cero.

#¡VALOR!

Similar a #¡NUMERO!, lo obtenemos cuando el tipo de argumento solicitado por la función, es distinto al


Página 66 de 66

ingresado por el usuario. Por ejemplo, al ingresar un argumento lógico cuando la función requiere un rango, o

un número cuando la función espera texto.

#¡NULO!

Este es muy poco frecuente. Una fórmula devolverá #¡NULO! cuando la celda de intersección de dos rangos,

no existe. En Excel, el operador de intersección es un espacio en blanco. Por tanto, la fórmula =A2:D2 J1:J10,

devuelve #¡NULO! ya que los rangos A2:D2 y J1:J10 no se intersectan en ningún punto. En cambio, =A2:D2

C1:C10 devuelve C2, celda común a ambos rangos.

A menudo sucede que una celda de error está correctamente escrita pero, al hacer referencia a un resultado de

error, refleja este resultado. Para saber cuál es la celda exacta que está generando el error, podemos ejecutar

(previa selección de la celda con error) Herramientas - Auditoría de fórmulas - Rastrear error. Excel señalará

con una línea roja la celda que está produciendo el error.

Otro error común es cuando la celda aparece llena de símbolos #. Esto se debe a que la celda no es lo

suficientemente ancha para mostrar el resultado o bien, cuando contiene una fecha inválida.

Prioridad de operadores

Si se combinan varios operadores en una única fórmula, Excel ejecutará las operaciones en el orden que se

indica en la siguiente tabla. Si una fórmula contiene operadores con la misma prioridad (por ejemplo, si una

fórmula contiene un operador de multiplicación y otro de división), Excel evaluará los operadores de izquierda

a derecha.

OPERADOR DESCRIPCIÓN

: (dos puntos)

(un solo espacio)

; (punto y coma)

Operadores de referencia

– Negación (como en –1)

% Porcentaje

^ Exponenciación

* y / Multiplicación y división

+ y - Suma y resta

& Conecta dos cadenas de texto (concatenación)

=

< >

<=

>=

<>

Comparación

Ejercicio 35 de Excel

Lo guardamos con el nombre de Ejercicio 35 Excel.xls, trabajamos con tablas.

Apuntes Excel

Documents

Transcript of Apuntes Excel