MANUAL de EXCEL - tierravirtual.cl 2007.pdf · 2Guardar libro como Borrador ... 1Trucos a la hora de trabajar con funciones ... Bienvenid@s a un nuevo Manual de Excel. La idea principal

MANUAL de EXCEL

por Elsa M.Meyer (Elsamatilde)

(para versiones Excel del 2000 al 2007 inclusive)

Copyright © 2.007-2.010 Elsa M.Meyer (Elsamatilde)

El presente manual intenta enseñar al usuario el uso decada opción de cada menú Excel.

Más de 100 trucos y soluciones para resolverlas con lasherramientas propias de la hoja Excel, sin necesitad de

recurrir a macros o código VBA.

Tabla de Contenidos

0

Cap. I Introducción 6

................................................................................................................................... 61 CARACTERISTICAS DEL MANUAL

Cap. II 1 - Temas generales 9

................................................................................................................................... 91 Las barras de Excel

................................................................................................................................... 92 El cuadro de Nombres

................................................................................................................................... 93 Formas de Selección

................................................................................................................................... 94 EL MENU ARCHIVO

................................................................................................................................... 115 El menu Edicion

................................................................................................................................... 126 El menu Ver

................................................................................................................................... 127 El menu Insertar

................................................................................................................................... 128 El menu Formato

................................................................................................................................... 129 El menu Herramientas

................................................................................................................................... 1210 El menu Datos

................................................................................................................................... 1211 El menu Ventana

Cap. III 2 - Lo nuevo en Excel 2007 14

................................................................................................................................... 141 NUEVA PRESENTACIÓN

................................................................................................................................... 142 Mas sobre la nueva interfaz de usuario

................................................................................................................................... 153 La Barra de Herramientas

................................................................................................................................... 154 Personalizar barra

................................................................................................................................... 155 Los atajos de teclado

................................................................................................................................... 156 Nueva vista Diseño de página

................................................................................................................................... 157 Más espacio de trabajo-Límites

................................................................................................................................... 158 Nuevos Formatos y Estilos

................................................................................................................................... 169 Mejoras en funciones

................................................................................................................................... 1610 Menu Macros

................................................................................................................................... 1611 Agregar complementos

Cap. IV 3 - Trabajando con Libros 18

................................................................................................................................... 181 Contenido

................................................................................................................................... 182 Guardar libro como Borrador

................................................................................................................................... 183 Guardar Area de Trabajo

................................................................................................................................... 184 Ocultar libros

................................................................................................................................... 185 Predeterminar Autor de libros

................................................................................................................................... 196 Predeterminar Libro de Inicio

Manual Excel con Trucos y Soluciones-DEMO I

© <2007>Elsa M. Meyer (Elsamatilde)

................................................................................................................................... 197 Predeterminar Número de Hojas

................................................................................................................................... 198 Predeterminar Número Archivos_Recientes

................................................................................................................................... 199 PREDETERMINAR FUENTE Y TAMAÑO

................................................................................................................................... 2010 Predeterminar Directorio de Apertura y Cierre

................................................................................................................................... 2011 Cómo se verá la Barra de Tareas

................................................................................................................................... 2012 Agregar opciones a la Barra de Herramientas

................................................................................................................................... 2113 Crear Barras de Herramientas

................................................................................................................................... 2114 Barras que acompañan al libro

................................................................................................................................... 2115 Copia de Seguridad

................................................................................................................................... 2116 Hipervinculos

Cap. V 4 - Trabajando con Hojas 23

................................................................................................................................... 231 Contenido del capitulo

................................................................................................................................... 232 Direccion de la tecla Enter

................................................................................................................................... 233 Quitar barras y encabezados

................................................................................................................................... 234 PROTEGER FORMULAS Y VALORES

................................................................................................................................... 245 Inmovilizar o dividir ventana

................................................................................................................................... 246 Barra de Estado

................................................................................................................................... 257 Personalizar la paleta de colores

................................................................................................................................... 258 Color a líneas de división

................................................................................................................................... 259 Otro estilo de comentarios

................................................................................................................................... 2510 Listas Personalizadas

................................................................................................................................... 2511 Principales teclas de Funcion

................................................................................................................................... 2512 Algunos atajos de Teclado

................................................................................................................................... 2613 Modificar el zoom con el mouse

Cap. VI 5 - Seleccionando y Modificando Datos 28

................................................................................................................................... 281 Seleccion de celdas y rangos

................................................................................................................................... 282 Mover o Copiar celdas

................................................................................................................................... 283 Ir a ... Celdas especiales

................................................................................................................................... 284 Modificar datos en celdas

................................................................................................................................... 285 Series. Llenar datos en celdas

................................................................................................................................... 296 Eliminar celdas rapidamente

................................................................................................................................... 297 Insertar celdas rapidamente

................................................................................................................................... 298 Trabajar con varias hojas a la vez

................................................................................................................................... 299 OPCIONES DEL PEGADO ESPECIAL

................................................................................................................................... 3010 Combinando celdas

Cap. VII 6 - Formatos 32

................................................................................................................................... 321 Ocultar datos para imprimir

IIContents

II


................................................................................................................................... 322 Validación: Longitud de datos

................................................................................................................................... 323 Validación: Rellenar con ceros

................................................................................................................................... 324 Formato Condicional con fórmulas

................................................................................................................................... 325 COLOREAR FILAS ALTERNAS

................................................................................................................................... 336 Utilizar símbolos

................................................................................................................................... 337 Obtener fechas con formato especial

................................................................................................................................... 338 Ingresar fechas u horas sin separadores

................................................................................................................................... 339 Formato porcentage

................................................................................................................................... 3410 Valores negativos en rojo

Cap. VIII 7 - Funciones 36

................................................................................................................................... 361 Trucos a la hora de trabajar con funciones

................................................................................................................................... 362 Quitar expresión de error o 0 en fórmulas

................................................................................................................................... 363 La función TEXTO

................................................................................................................................... 364 Obtener nombre de carpeta, libro u hoja

................................................................................................................................... 365 Incluir nombre de Hoja-Libro-Carpeta en funcion

................................................................................................................................... 366 Promedios sin incluir valores 0

................................................................................................................................... 377 Unificando Estilos en celdas con formato

................................................................................................................................... 378 Contar cantidad de cierto caracter en celda

................................................................................................................................... 379 Sumar tablas filtradas

................................................................................................................................... 3710 Obtener posición del dato encontrado

................................................................................................................................... 3711 OBTENER TITULO DE COLUMNA DEL DATO ENCONTRADO

................................................................................................................................... 3812 Cálculos con fechas

................................................................................................................................... 3813 Cálculos con horas-minutos

................................................................................................................................... 3814 Extraer Hora en celdas con fechas

................................................................................................................................... 3815 La función SI.FECHA

................................................................................................................................... 3916 La función ALEATORIO

................................................................................................................................... 3917 La función MAX y BUSCAR

................................................................................................................................... 3918 La funcion INDIRECTO y FILA

................................................................................................................................... 3919 Devolver valores a izquierda

................................................................................................................................... 3920 Devolver otros valores Min y Max

................................................................................................................................... 3921 Contar registros según criterios

................................................................................................................................... 3922 Sumar registros según criterios

................................................................................................................................... 4023 Formulas matriciales

................................................................................................................................... 4024 Buscar valores según 2 criterios

Cap. IX 8 - Especiales del menú Herramientas 42

................................................................................................................................... 421 Buscar Objetivo

................................................................................................................................... 422 Escenarios

................................................................................................................................... 423 Seguridad

Manual Excel con Trucos y Soluciones-DEMO III


................................................................................................................................... 424 Creando una firma digital

................................................................................................................................... 425 AGREGAR COMPLEMENTOS

Cap. X 9 - Especiales del menu Datos 45

................................................................................................................................... 451 Formularios

................................................................................................................................... 452 Comentarios-Otro estilo

................................................................................................................................... 453 Comentarios desde Validación de datos

................................................................................................................................... 454 Separar texto en varias columnas

................................................................................................................................... 455 CONSOLIDAR DATOS

................................................................................................................................... 466 Agrupar y Esquema

................................................................................................................................... 467 Subtotales

Cap. XI 10 - Más Soluciones 48

................................................................................................................................... 481 Ordenar por más de 3 columnas

................................................................................................................................... 482 Validar datos según lista en otra hoja

................................................................................................................................... 483 Validar datos en columnas dependientes

................................................................................................................................... 484 Evitar duplicados al ingreso de datos

................................................................................................................................... 485 Evitar duplicados según 2 celdas

................................................................................................................................... 486 Filtro avanzado en hoja protegida

................................................................................................................................... 497 Llenar tablas (sin preocuparnos del salto de col)

................................................................................................................................... 498 Autocompletar celdas vacías

................................................................................................................................... 499 Cómo agregar o quitar datos de un gráfico

................................................................................................................................... 4910 Evitar cortes en gráficos con valores 0

................................................................................................................................... 4911 IMAGENES EN SERIES DE GRAFICOS

IVContents

IV


Capítulo

I

Introducción 6


1 Introducción

1.1 CARACTERISTICAS DEL MANUAL

VERSION DEMO - MANUAL EXCEL : del 2000 al 2007(v.1.0)

Bienvenid@s a un nuevo Manual de Excel.

La idea principal de este Manual es reflejar o comentar aquellos temas que no se encuentran a primera vista,que los expertos conocen más por el uso cotidiano de la aplicación que por encontrar el tema en la Ayuda delpropio Excel.

Primero pasaré revista a los menús de la barra de Excel: Archivo, Edición,.... Ventana. Y en capítulossiguientes veremos algunos trucos y soluciones para aprovechar al máximo cada una de estas opciones queExcel nos ofrece.

El manual fue ordenado principalmente por 'objetos': temas que tienen que ver con la configuración del LibroExcel, otros temas relacionados con Hojas y así hasta llegar al último capítulo con Soluciones Generalescomo: Evitando duplicados, Validar columnas dependientes y muchas más.

Una característica a resaltar de este Manual es su formato y su potente buscador. Al solicitar un tema nosdevolverá la lista de entradas que aparecen en todo el libro, como en la imagen donde se ingresó la palabraBuscarV apareciendo 4 entradas lo que nos permite abarcar de manera completa el tema buscado.

Otra característica a resaltar de este formato, es su posibilidad de agregar comentarios en cada tema. AsíUd puede agregar sus propias observaciones, ejemplos u otros datos de interés.

Manual Excel con Trucos y Soluciones-DEMO 7


Desde el menú Edición, Anotar encontrará una ventana para escribir.Cada vez que acceda a este tema, encontrará en el margen superior un clip recordándole que el tema tieneagregados. Haciendo clic en él, aparecerán sus notas personales.

Por otra parte algunos temas presentan un hipervínculo a otras páginas donde se completa el desarrollo deltema o se presentan las imágenes o ejemplos de lo explicado. El botón 'Atrás' de la barra del manual, permitevolver al tema original que se estaba estudiando.

Y las novedades Excel 2007 en un capítulo lleno de imágenes y comentarios para conocer esta nuevaversión.

IMPORTANTE: por tratarse de una versión DEMO solo se encuentran desarrollados los temas queaparecen en el Indice, en mayúsculas. Para obtener una versión de COMPRA, puede enviar un correoa:[email protected] o visitar la siguiente página: http://aplicaexcel.galeon.com/index.htm

Capítulo

II



2 1 - Temas generales

2.1 Las barras de Excel

Este tema no se encuentra disponible en esta versión DEMO.

2.2 El cuadro de Nombres


2.3 Formas de Selección


2.4 EL MENU ARCHIVO

Podemos observar en la imagen las opciones del menú ARCHIVO, que no difieren en mucho a las del restodel paquete Office.

NOTA: Aquellas opciones que presentan iconos delante, se pueden ejecutar desde la Barra Estándar (vertema 'Las barras de Excel' en este mismo capítulo) . Algunas opciones presentan además las teclas de accesorápido o 'atajos de teclado' como Ctrl+U para abrir un nuevo libro.

IMPORTANTE: En el próximo capítulo veremos cómo se agrupan las opciones de cada menú en lanueva versión Excel 2007.

1 - Temas generales 10


NUEVO: permite abrir un libro en blanco

ABRIR : permite buscar y abrir un libro guardadoSabías que...?

1- Se puede establecer la carpeta predeterminada a dónde Excel irá al presionar el botón 'Abrir'.Ver 'Trabajando con Libros - Predeterminar Directorio de Apertura y Cierre

CERRAR : cierra el libro activo tal como lo hace el botón X de la barra azul de la ventana Excel

GUARDAR: guarda el libro activo en la misma ubicación y con el mismo nombre

GUARDAR COMO...: permite guardar el libro activo en otra ubicación, con otro nombre, con clave, o con otraextensión.Sabías que...?

2- Desde esta opción se puede establecer la opción de guardar siempre una Copia de Seguridad?Ver 'Trabajando con Libros - Copia de Seguridad

3- Se puede guardar el libro provisoriamente en el escritorio (o en otra carpeta) como borrador?Ver 'Trabajando con Libros - Guardar como Borrador

GUARDAR COMO PAGINA WEB: permite guardar el libro o la hoja activa en formato htm. Al iniciar estaopción tendrá acceso al Ayudante que lo guiará en esta tarea.

GUARDAR AREA DE TRABAJO: si el libro se guarda con esta opción al abrirlo nuevamente se verá la mismapantalla que al cierre.



Sabías que...?4- Si debemos interrumpir una tarea con varios libros abiertos podemos guardarlos tal como losdejamos y retomarlos en otro momento?

Ver 'Trabajando con Libros - Guardar área de trabajo

BUSCAR: nos permite buscar otros libros según criterios ingresados en la ventana de búsqueda. No confundircon la opción Buscar del menú Edición, que realiza la búsqueda en el 'mismo libro'.

VISTA PREVIA DE LA PÁGINA WEB: si el libro se guardará como página Web utilizar esta opción para unavista preliminar.

CONFIGURAR PÁGINA: nos permite establecer márgenes, orientación de la hoja, tipo y tamaño de hoja,encabezados y pies de página y otros detalles de impresión.(Ver novedades en Excel 2007)

AREA DE IMPRESIÓN: para imprimir solo un rango de celdas y no la hoja completa, seleccionarlas e ir a estaopción para establecerla como área a imprimir.

VISTA PRELIMINAR: nos presenta la hoja tal como saldrá impresa. Desde esta opción es posible modificarlos márgenes y modificar la configuración de la página.

IMPRIMIR: nos presenta la ventana para ajustar los detalles de la impresión. Desde aquí también se puedesolicitar una Vista Preliminar.

ENVIAR A: presenta las opciones para un envío por correo electrónico. Excel utiliza la cuenta de correo quese encuentra configurada desdel el Panel de Control de Windows, Opciones de Correo.

PROPIEDADES: desde aquí podemos establecer los comentarios que luego se verán al seleccionar elarchivo: Título, Asunto, Autor, Organización y otros datos.Sabías que...?

5- Se puede establecer la base de los hipervínculos que contendrá este libro?Ver 'Trabajando con Libros - Hipervínculos

Lista de archivos abiertos recientemente: de manera predeterminada aparecerán los 4 últimos archivosabiertos con anterioridad.Sabías que...?

6- Podemos establecer la cantidad de libros recientes que nos aparecen en la lista al final de estemenú?

Ver 'Trabajando con Libros - Predeterminar Número de Archivos Recientes.

2.5 El menu Edicion


1 - Temas generales 12


2.6 El menu Ver


2.7 El menu Insertar


2.8 El menu Formato


2.9 El menu Herramientas


2.10 El menu Datos


2.11 El menu Ventana


Capítulo

III

2 - Lo nuevo en Excel 2007 14


3 2 - Lo nuevo en Excel 2007

3.1 NUEVA PRESENTACIÓN

Si bien a primera vista los cambios son muy marcados, la esencia del programa se ha mantenido, y con el usose puede apreciar que las opciones siguen siendo las mismas aunque se han ordenado de forma diferente.

El nuevo centro de control: la cinta (o Banda) de opcionesSe ha reemplazado la barra de los menús por una Banda en la que se agrupan en forma de pestañas todaslas opciones que encontrábamos en versiones anteriores (más algunas nuevas).

Los tres componentes de la cinta de opciones son las fichas, los grupos y los comandos

1- Fichas: Aparecen en la parte superior y cada una de ellas representa una de las tareas básicas que sehacen en Excel (Inicio - Insertar - Diseño de página - .....)

2- Grupos: Cada ficha tiene grupos que muestran elementos relacionados entre sí. En la imagen podemosobservar: Fuente - Alineación - Número - ....

3- Comandos: Un comando es un botón, un cuadro en el que se escribe información o un menú. En el grupoPortapapeles observamos los comandos Cortar, Copiar, Copiar Formato....

¿Cómo acceder a más comandos ?

Presionando el botón que se encuentra a la derecha del nombre de cada Grupo.

Cuando vea esta flecha (denominada iniciador de cuadros de diálogo) en la esquina inferior derecha de ungrupo, significa que hay más opciones dentro del grupo. Haga clic en la flecha y se mostrará un cuadro dediálogo o panel de tareas.

3.2 Mas sobre la nueva interfaz de usuario




3.3 La Barra de Herramientas


3.4 Personalizar barra


3.5 Los atajos de teclado


3.6 Nueva vista Diseño de página


3.7 Más espacio de trabajo-Límites


3.8 Nuevos Formatos y Estilos


2 - Lo nuevo en Excel 2007 16


3.9 Mejoras en funciones


3.10 Menu Macros


3.11 Agregar complementos


Capítulo

IV

3 - Trabajando con Libros 18


4 3 - Trabajando con Libros

4.1 Contenido

En este capítulo se tratan temas que tienen que ver con la configuración de Excel y su comportamiento paratodos los libros en general.

Veremos cómo guardarlos y ocultarlos. También veremos cómo personalizar un libro Excel a nuestro gustocon las opciones que nos presenta el menú Herramientas, Opciones.

4.2 Guardar libro como Borrador


4.3 Guardar Area de Trabajo


4.4 Ocultar libros


4.5 Predeterminar Autor de libros




4.6 Predeterminar Libro de Inicio


4.7 Predeterminar Número de Hojas


4.8 Predeterminar Número Archivos_Recientes


4.9 PREDETERMINAR FUENTE Y TAMAÑO

La fuente predeterminada de Excel es Arial 10, pero esto puede cambiarse…..

Desde el menú Herramientas, Opciones, General. encontramos un item que dice 'Fuente estándar'

Seleccionar la fuente y tamaño de nuestra preferencia para todos los libros de Excel.

3 - Trabajando con Libros 20


Nota: Para establecer fuente y tamaño del libro activo, ver tema Estilos en capítulo: Temas generales- El menú Formato

4.10 Predeterminar Directorio de Apertura y Cierre


4.11 Cómo se verá la Barra de Tareas


4.12 Agregar opciones a la Barra de Herramientas




4.13 Crear Barras de Herramientas


4.14 Barras que acompañan al libro


4.15 Copia de Seguridad


4.16 Hipervinculos


Capítulo

V



5 4 - Trabajando con Hojas

5.1 Contenido del capitulo

En este capítulo veremos todo acerca de la configuración de nuestra hoja o libro Excel.Estas opciones tienen que ver generalmente con el libro activo.

Cómo ver la hoja, protejerla, crear barras y todas las Opciones del menú Herramientas.

También trucos y soluciones para llenar datos en una tabla y principales teclas de Función.

5.2 Direccion de la tecla Enter


5.3 Quitar barras y encabezados


5.4 PROTEGER FORMULAS Y VALORES

Siempre es conveniente ocultar o por lo menos proteger las celdas con títulos, fórmulas y datos fijos, paraevitar que sean modificadas por accidente.

Excel de manera predeterminada presenta las celdas de forma bloqueadas. Es decir que si protejemos lahoja, no se podrán ingresar datos manualmente.Esto es justamente lo que necesitamos para las fórmulas o textos fijos, pero deberemos permitir el ingreso dedatos en otras.

Entonces seleccionar todas las celdas donde SI se introducirán valores y quitar el tilde al cuadro Bloqueada,desde el menú Formato, Celdas, Proteger.

Además para que además no se vean las fórmulas en la Barra de Fórmulas, seleccionar las celdas que lascontengan y tildar la opción Oculta que se encuentra en la misma pestaña Proteger del menú Formato.

4 - Trabajando con Hojas 24


Sugerencia: si serán más las celdas con datos a ingresar que las bloqueadas, invertir el proceso.Esto es, seleccionar la hoja desde el extremo izquierdo (Ver marca entre col A y fila 1) y quitar el tilde a laopción Bloqueo. Luego seleccionar solo las de fórmulas y volver a tildar esta opción.

Estas opciones solo se comprueban cuando protegemos la hoja, desde menú Herramientas, Proteger,Proteger hoja. El ingreso de contraseña es opcional.

Nota: para cambiar el estilo de bloqueo con que cuenta la hoja (de manera predeterminada todas las celdasestán bloqueadas) ver tema Estilo en capítulo 1- El menú Formato

5.5 Inmovilizar o dividir ventana


5.6 Barra de Estado




5.7 Personalizar la paleta de colores


5.8 Color a líneas de división


5.9 Otro estilo de comentarios


5.10 Listas Personalizadas


5.11 Principales teclas de Funcion


5.12 Algunos atajos de Teclado


4 - Trabajando con Hojas 26


5.13 Modificar el zoom con el mouse


Capítulo

VI

5 - Seleccionando y Modificando Datos 28


6 5 - Seleccionando y Modificando Datos

6.1 Seleccion de celdas y rangos


6.2 Mover o Copiar celdas


6.3 Ir a ... Celdas especiales


6.4 Modificar datos en celdas


6.5 Series. Llenar datos en celdas




6.6 Eliminar celdas rapidamente


6.7 Insertar celdas rapidamente


6.8 Trabajar con varias hojas a la vez


6.9 OPCIONES DEL PEGADO ESPECIAL

Copiar y Pegar... pero conocemos todas las opciones del Pegado Especial?

El Pegado Especial nos permite obtener solo algunos parámetros de la celda o rango copiado, como ser, solofórmulas, valores, solo formatos, solo comentarios, ancho de columna, etc.

Desde el grupo Operación se nos permite realizar alguna de las operaciones básicas en todo el rangoseleccionado.

5 - Seleccionando y Modificando Datos 30


Ejemplo: si tenemos los siguientes valores a los que deseamos multiplicar por 10.

Los pasos a seguir son:1- Seleccionar con clic derecho la celda que tiene el valor 10 y optar por 'Copiar'2- Seleccionar el rango de importes, clic derecho y optar por 'Pegado Especial, Multiplicar.3- Aceptar

Saltar Blancos: Evita el reemplazo de valores en el área de pegado cuando hay celdas en blanco en el áreade copiado.

Ejemplo: Tenemos una columna Origen cuyos datos deseamos copiar en col Destino.

1- Seleccionar el rango de origen (sin título) y 'copiar'2- Seleccionar la primer celda de destino, clic derecho, opción Pegado Especial, Saltar Blancos.3- Observar que la celda con valor 150 se mantiene sin cambios.

Transponer: Pega las columnas copiadas en filas y viceversa.

Ejemplo: partiendo de una tabla como la siguiente:

1- Seleccionar el rango, clic derecho y 'copiar'2- Seleccionar la primer celda de destino, clic derecho, optar por Pegado Especial, Transponer.3- El resultado será según se observa en la imagen.

6.10 Combinando celdas


Capítulo

VII

6 - Formatos 32


7 6 - Formatos

7.1 Ocultar datos para imprimir


7.2 Validación: Longitud de datos


7.3 Validación: Rellenar con ceros


7.4 Formato Condicional con fórmulas


7.5 COLOREAR FILAS ALTERNAS

Una manera vistosa de presentar la tabla es coloreando filas alternas: las pares o las impares dejando el restoblancas o a su vez formateadas de otro color.

Para realizar esto nuevamente utilizaremos la opción Formato Condicional, con fórmulas.

1- Seleccionar el rango a colorear.

2- Ir al menú Formato, Formato Condicional.

3- Optar por Fórmula en lugar de 'Valor de celda' e introducir la siguiente fórmula:=RESIDUO(FILA();2)=0



4- Presionar el botón Formato, Tramas y seleccionar un color.

Nota:La función Fila devuelve el nro de la fila.La función Residuo devuelve el Resto de dividir el número de fila por 2. Si ese resto es 0 se tratará de filaspares.

5- Si además deseamos colorear las filas impares presionamos el botón Agregar y la fórmula en este casoserá:

=RESIDUO(FILA();2)<>0.

7.6 Utilizar símbolos


7.7 Obtener fechas con formato especial


7.8 Ingresar fechas u horas sin separadores


7.9 Formato porcentage


6 - Formatos 34


7.10 Valores negativos en rojo


Capítulo

VIII

7 - Funciones 36


8 7 - Funciones

8.1 Trucos a la hora de trabajar con funciones


8.2 Quitar expresión de error o 0 en fórmulas


8.3 La función TEXTO


8.4 Obtener nombre de carpeta, libro u hoja


8.5 Incluir nombre de Hoja-Libro-Carpeta en funcion


8.6 Promedios sin incluir valores 0




8.7 Unificando Estilos en celdas con formato


8.8 Contar cantidad de cierto caracter en celda


8.9 Sumar tablas filtradas


8.10 Obtener posición del dato encontrado


8.11 OBTENER TITULO DE COLUMNA DEL DATO ENCONTRADO

La función COINCIDIR nos permite obtener la referencia o posición de un dato encontrado.

A continuación podemos observar una tabla, donde se desea saber a qué columna corresponde el valormáximo de cada fila y su referencia en la fila de títulos.

Aquí se utilizó la función COINCIDIR, junto con la función INDICE y K.ESIMO.MAYOR

K.ESIMO.MAYOR(rango;posición) encuentra la mayor venta de la fila

7 - Funciones 38


COINCIDIR(valor buscado; matriz a buscar; tipo coincidencia) devuelve la posición donde ese valormáximo se encuentra en el rango de ventas

INDICE(matriz; nro fila; nro col): devuelve la posición encontrada pero en la fila de títulos.

En la columna 'Mayor Venta' utilizamos la siguiente fórmula que nos devuelve el primer valor máximo de la fila:

=+K.ESIMO.MAYOR(B2:D2;1)

En la columna 'Vendedor' obtenemos el título de ese valor máximo:

=+INDICE($B$1:$D$1;;COINCIDIR(E2;B2:D2;0))

Para evitar el uso de la columna 'Mayor Venta' podemos concatenar la fórmula para la columna 'Vendedor' dela siguiente manera:

=+INDICE($B$1:$D$1;;COINCIDIR(K.ESIMO.MAYOR(B2:D2;1);B2:D2;0))

8.12 Cálculos con fechas


8.13 Cálculos con horas-minutos


8.14 Extraer Hora en celdas con fechas


8.15 La función SI.FECHA




8.16 La función ALEATORIO


8.17 La función MAX y BUSCAR


8.18 La funcion INDIRECTO y FILA


8.19 Devolver valores a izquierda


8.20 Devolver otros valores Min y Max


8.21 Contar registros según criterios


8.22 Sumar registros según criterios


7 - Funciones 40


8.23 Formulas matriciales


8.24 Buscar valores según 2 criterios


Capítulo

IX

8 - Especiales del menú Herramientas 42


9 8 - Especiales del menú Herramientas

9.1 Buscar Objetivo


9.2 Escenarios


9.3 Seguridad


9.4 Creando una firma digital


9.5 AGREGAR COMPLEMENTOS

Los complementos son archivos que contienen funciones de uso masivo.

Podemos tener nuestras propias funciones en un libro, a las que accederemos a través de la lista defunciones Excel, categoría 'Definidas por el usuario', pero si estas funciones necesitan ser utilizadas enotros libros, deberemos crear un 'Complemento'

¿Cómo acceder entonces a estas funciones desde otro libro?Como este manual no se ocupa de Macros, solo aclararé que la función debe ser creada en el Editor de unlibro en blanco, en un módulo y luego el libro será guardado como Tipo 'Complemento' con extensión .xla.(en el siguiente mensaje no es necesario guardar los cambios)

Una vez hecho esto, desde cualquier libro Excel, ir al menú Herramientas, Complementos, presionar elbotón Examinar y buscar el archivo creado en el paso anterior.

Importante: si una función ya no será utilizada, desde este mismo menú destildar la opción en la lista decomplementos.



Nota: En la carpeta donde se encuentra este manual, se incluye un complemento conteniendo la función:ContarCeldasColor. Agréguela a su Excel siguiendo los pasos anteriores.

Atención: En el libro adjunto Ejemplos_TyS, hoja Función, podrá ver la sintaxis de esta función que lecontará, dentro del rango seleccionado, las celdas con fuente distinta de negro (o automática)

Capítulo

X



10 9 - Especiales del menu Datos

10.1 Formularios


10.2 Comentarios-Otro estilo


10.3 Comentarios desde Validación de datos


10.4 Separar texto en varias columnas


10.5 CONSOLIDAR DATOS

Supongamos que tenemos 2 hojas ( o más): Sucursal 1 y 2 con los mismos datos y necesitamos en otra hojao rango el total acumulado de ambas.Esto se logra desde el menú DATOS, opción CONSOLIDAR

Consolidar datos significa resumir datos procedentes de varias hojas.El proceso de consolidación nos permite elegir si los datos consolidados quedan vinculados a las hojas fuenteo no.Excel permite consolidar hojas por posición y por categoría.

1- Por posición, cuando los datos de todas las áreas de origen se organizan en idéntico orden y situación;por ejemplo, para combinar los datos de una serie de hojas de cálculo creadas desde la misma plantilla.

2- Por categorías, si se desea resumir un conjunto de hojas de cálculo que tienen los mismos rótulos peroorganizan los datos de forma diferente.

9 - Especiales del menu Datos 46


En el ejemplo siguiente tenemos 2 pequeñas tablas y la de Resúmen.

Pasos a seguir:1- Seleccionar las 3 celdas del Total en la tabla Resumen.

2- Ir al menú Datos, Consolidar. Dejar la opción Suma

3- En Referencias introducir el rango de importes de la Sucursal 1 y presionar Agregar.Nuevamente en Referencias introducir el rango de importes de la Sucursal 2 y presionar Agregar.

4- Para que los valores del Resumen se actualicen con los cambios en las distintas tablas, tildar la opciónCrear vínculos con los datos de origen.

Nota: *ver otros ejemplos en libro adjunto Ejemplos TYS

10.6 Agrupar y Esquema


10.7 Subtotales


Capítulo

XI

10 - Más Soluciones 48


11 10 - Más Soluciones

11.1 Ordenar por más de 3 columnas


11.2 Validar datos según lista en otra hoja


11.3 Validar datos en columnas dependientes


11.4 Evitar duplicados al ingreso de datos


11.5 Evitar duplicados según 2 celdas


11.6 Filtro avanzado en hoja protegida




11.7 Llenar tablas (sin preocuparnos del salto de col)


11.8 Autocompletar celdas vacías


11.9 Cómo agregar o quitar datos de un gráfico


11.10 Evitar cortes en gráficos con valores 0


11.11 IMAGENES EN SERIES DE GRAFICOS

Una opción interesante a la hora de crear gráficos es utilizar imágenes en las series, que tengan relacióncon el contenido de lo que se está graficando, en lugar de las típicas barras o columnas.En la imagen se trata de un gráfico para una Inmobiliaria.

10 - Más Soluciones 50


Pasos a seguir:

1- Una vez hecho de la forma habitual el gráfico de nuestra tabla, seleccionar una serie con clic derecho yoptar por Formato de serie

2- En la pestaña Tramas, optar por Efectos de Relleno (* ver imagen 1)

3- En la siguiente ventana, optar por la pestaña: Imagen (**ver imagen 2)

4- En la siguiente ventana presionar el botón: Seleccionar imagen. Se ha dejado el formato 'Apilar' (***verimagen 3)

FIN DEL MANUAL

Documents

MANUAL de EXCEL - tierravirtual.cl 2007.pdf · 2Guardar libro como Borrador ... 1Trucos a la hora de trabajar con funciones ... Bienvenid@s a un nuevo Manual de Excel. La idea principal