108555009-Excell2007-Vip Genial Fantastico-15 Ejercicios Planteados Para Clase-gran Maquetacion-nivel Medio-para Clase

EXCEL 2007

MANUAL PRACTICO

[email protected]@gmail.com

mailto:[email protected]


INTRODUCCION

El presente documento consta de 21 clases desarrolladas con sus respectivas prácticas. Al reunirlas se llego a formar un manual práctico para el uso de esta he-rramienta de cálculo: Excel 2007.

La teoría ha sido desarrollada no tan abundante; pero detallada para el uso de la formulas o comandos a utilizar. En esta forma, se ha tratado de realizar tareas en cada una de las opciones de Excel 2007. Hay muchos comandos que no se han utilizado y podría ser de un tratamiento mayor.

Para un usuario interesando en el manejo de esta hoja de calculo, bien se adapta este pequeño manual para su uso. Los ejemplos se han tomado de la realidad concreta del distrito de Imperial, Cañete. Ubicado en la Región Lima Provincias, país Perú.

En esta ciudad existe un camal de aves y de animales menores. Por otro lado, el comercio utiliza muy poco las herramientas para control y valuación de sus activi-dades, lo que posibilito la aplicabilidad del curso, orientando al estudiante a buscar o crear su propia fuente de ingresos.

El lector puede ajustar los ejemplos a su propio interés y realidad. Lo importante es que la herramienta Excel 2007 nos permite obtener resultados precisos a nues-tras actividades y problemas cotidianos o empresariales.

En espera de sus comentarios o sugerencias y los errores son de mi absoluta res-ponsabilidad. Lo cual estaré eternamente agradecido. Pueden contactar a

[email protected] , [email protected] , [email protected]

Educación para el cambio




INTERFAZ DE MICROSOFT EXCEL 2007

(1). Barra de título : muestra el nombre de archivo del documento que está edi-

tando y el nombre del programa que está utilizando.(2). Botón de Office : haga clic en este botón al usar comandos básicos, como

Nuevo, Abrir, Guardar como, Imprimir y Cerrar.(3). Barra de herramientas de acceso rápido : incluye comandos de uso fre-

cuente como Guardar y Deshacer. También puede agregar sus comandos favoritos.

(4). Cinta de opciones : aquí encontrará los comandos necesarios para traba-jar. Es igual que "Menús" o "barras de herramientas" en otro software.

(5). Cuadro de Nombres: Muestra la celda activa y nombres de rangos(6). Barra de fórmulas: Muestra el contenido de la celda activa.(7). Hoja de Trabajo : muestra la matriz de celdas de trabajo.(8). Barras de desplazamiento : permite cambiar la posición en la pantalla del

documento que está editando.(9). Botones de visualización : permiten cambiar la forma en que se muestra el

documento que está editando para que se adapte a sus necesidades.(10). Control deslizante del zoom : permite cambiar la configuración de zoom

del documento que está editando.(11). Barra de estado : muestra información acerca del libro que está editando.(12). Hojas de Trabajo : contiene áreas independientes de hojas de trabajo.(13). Celda Activa : Celda donde se insertará o mostrará los datos.


12 3

4

5 6

7

9

8

10

8

11

12

13

REAFIRMANDO CONCEPTOS a) ¿Número de Hojas por defecto en un Libro de MS Excel 2007?

.........................................................................................................................

.........................................................................................................................

b) ¿Número de Columnas y filas que tiene una Hoja de Cálculo de MS Excel 2007, para:

Columnas: …………............ Identificadas desde: ………… hasta:…………….

Filas:…………………….......

c) Elementos de la Barra de Fórmulas

1. 2.

3. 4.

d) Mencione las tareas que se pueden efectuar desde el Cuadro Nombres

1. 2.

3. 4.

e) Tipos de datos que acepta cada celda de Microsoft Excel:

a.

b.

c.

d.

e.

f.

g.


LAS HOJAS EN MICROSOFT EXCEL 2007

Por defecto Microsoft Excel muestra 3 hojas como se indica en el recuadro supe-

rior.

Al hacer clic derecho sobre una de ellas nos muestra un menú

desplegable; tal como en el recuadro de la derecha.

Puede apreciar que este menú desplegable nos permite Inser-

tar, eliminar, cambiar nombre, etc. a hojas del libro en que es-

tamos trabajando.

Ejemplos: Trabajando con hojas en libros de Excel:

a) Clic derecho sobre la Hoja 1

b) Cambie el nombre a la Hoja1 por Enero, a la Hoja2 por Febrero y a la Hoja3

por Marzo.

c) Inserte 9 Hojas de Cálculo. Cambie el nombre según el orden que se muestra

a continuación, Abril, Mayo, Junio, Julio, Agosto, Septiembre, Octubre, Noviem-

bre y Diciembre.

d) Ahora mueva la hoja “Abril” y colóquelo antes de la hoja “Enero”.

e) La hoja Enero, colóquelo después de Agosto

f) La hoja Marzo colóquelo antes de Diciembre

g) La hoja Setiembre muévalo y colóquelo antes de Febrero

Una forma rápida de renombrar una Hoja es haciendo doble sobre ella.


USANDO ALGUNAS TECLAS DE DESPLAZAMIENTO Y SELECCION

Presione Para

INICIO Ir hasta el comienzo de una fila

CTRL+INICIO Ir al inicio de la hoja, celda A1

CTRL+FIN Ir a la intersección de la última fila y columna, edi-

tadas.

CTRL + Barra Espaciadora Seleccionar toda la Columna

SHIFT + Barra Espaciadora Seleccionar toda la Fila

CTRL + [ + ] Insertar Fila(s) o columna(s) seleccionada(s)

CTRL + [ - ] Eliminar Fila(s) o columna(s) seleccionada(s)

CTRL+ tecla de dirección Ir hasta a la próxima celda que contenga datos.

AV PÁG Desplazarse una pantalla hacia abajo

RE PÁG Desplazarse una pantalla hacia arriba

ALT+AV PÁG Desplazarse una pantalla hacia la derecha

ALT+RE PÁG Desplazarse una pantalla hacia la izquierda

CTRL+AV PÁG Ir a la siguiente hoja del libro

CTRL+RE PÁG Ir a la hoja anterior del libro

[←] [ ↑ ] [ ↓ ] [→ ] Moverse una celda a la izquierda, arriba, abajo o

derecha

OTRAS:

[Ctrl] + [U] - Abrir nuevo libro

[Ctrl] + [A] - Abrir libro existente

[Ctrl] + [G] - Guardar

[Ctrl] + [F6] - Cambiar a otro libro.

[Ctrl] + [P] - Imprimir

[Ctrl] + [Z] - Recupera el (los) último(s) cambio(s)

[Ctrl] + [X] - Cortar o mover, celdas o rangos

[Ctrl] + [C] - Copiar, celdas o rangos

[Ctrl] + [V] - Pegar celdas a moverse o copiarse

[Ctrl] + [B] - Buscar datos en la hoja activa

[Ctrl] + [1] - Activa la ventana “Formato de Celdas”


1. Escriba el nombre de las Opciones que posee Excel 2007.

2. __________________________ 5__________________________

3. __________________________ 6__________________________

4. __________________________ 7__________________________

5. __________________________

2. Escriba el nombre de los Grupos que se encuentran en la Opcion Inicio.

1. __________________

___

6. ________________________

2. __________________

_______

7. _________________________

3. __________________

_______

8. _________________________

4. __________________

_______

Ingresando nuestros primeros valoresIngresando nuestros primeros valores

3. Abra un libro en Excel. Cambiar el nombre de la Hoja1 por Ventas.

4. Ingresar los siguientes valores.


5. En la Hoja2, renombrar por Gastos, para luego ingresar los siguientes valores.

6. Finalmente:

a) Crear la carpeta Trabajos, en el disco local.

b) Guardar el Libro con el nombre Ejercicio1.


Los documentos en Excel se denominan libros. Excel puede guardar nuestros

libros en otros formatos. Un libro está compuesto por varias hojas de cálculo. Es

posible seleccionar varias hojas para realizar las mismas tareas en todas ellas de

forma simultánea.

Al abrir un libro nuevo de trabajo se abrirá con tres

hojas de cálculo.

La hoja de cálculo es uno de los distintos tipos de hojas que puede contener un li -

bro de trabajo. Es una herramienta útil para trabajar con gran cantidad de números

y realizar cálculos u operaciones con ellos. Es como una gran hoja cuadriculada

formada por 16384 columnas y 1048576 filas.

Para organizar el libro de manera conveniente, es posible

insertar hojas nuevas, eliminar hojas, cambiar su nombre,

moverlas, copiarlas, etc.

También se puede organizar la pantalla usando varias ventanas

para ver partes diferentes de una hoja, hojas distintas del mismo libro de trabajo o

bien varios libros de trabajo.

Podemos seleccionar varias hojas para realizar las mismas

tareas en todas ellas simultáneamente, así mismo, eliminar

varias hojas en un solo paso o introducir los mismos datos en

varias hojas a la vez.


Para seleccionar varias hojas se debe hacer clic en la

etiqueta de la primera hoja y manteniendo presionada

la tecla Ctrl hacemos clic en la etiqueta de cada una

de las hojas que queramos seleccionar.

Cuando las hojas a seleccionar se encuentren contiguas lo

haremos de la siguiente forma, se hace clic en la primera

hoja que queramos seleccionar y manteniendo presionada la

tecla Shift o Mayúsculas, se hace clic en la última hoja a

seleccionar.

La manera más sencilla de moverse a través del libro de

trabajo es utilizar los botones de desplazamiento, que aparecen en la siguiente

figura:

Un libro de trabajo contiene inicialmente tres hojas, pero pueden

insertarse otras nuevas. La cantidad máxima de hojas vendrá

limitada tan sólo por la memoria disponible del computador.

Para insertar nuevas hojas de cálculo disponemos de las

siguientes opciones:

1. Hacer clic en el ícono

Las nuevas hojas aparecerán a la derecha de la última hoja existente.

2. Hacemos clic con el botón secundario del mouse, sobre una

hoja apareciendo el siguiente menú de opciones:


Primera hoja Ultima hoja

Hoja anterior

Siguiente hoja

Insertar hoja de cálculo

a) Seleccionamos la opción “Insertar…”

b) En cuadro de diálogo que se activa, seleccionamos “Hoja de cálculo” ;

c) Luego, presionamos el botón Aceptar.

La nueva hoja aparecerá a la izquierda de la hoja existente.

3. Para insertar una hoja de cálculo nueva antes de otra ya existente, seleccione

esa hoja de cálculo y, a continuación, en la ficha Inicio, en el

grupo Celdas, haga clic en Insertar y, a continuación, en

Insertar hoja

1. Mantenga presionada la tecla MAYÚS o SHIFT y, a

continuación, seleccione un número de fichas de hoja

existentes equivalente al número de hojas de cálculo que

desee insertar en el libro abierto.

2. Por ejemplo, si desea agregar tres hojas de cálculo

nuevas, seleccione tres fichas de hoja de las hojas de cálculo existentes.

3. En la ficha Inicio, en el grupo Celdas, haga clic en

Insertar y luego en Insertar hoja.

Para eliminar una hoja de trabajo, los pasos a

seguir son:

1. Un clic con el botón derecho del mouse sobre la

hoja a eliminar.

2. En el menú que se despliega seleccionamos

“Eliminar”.


OTRA FORMA

1. Un clic sobre la hoja a eliminar.

2. En la ficha Inicio, en el grupo Celdas, haga clic en Eliminar

y luego en Eliminar hoja.

Se puede asignar un nombre de hasta 31 caracteres a

cualquier hoja. Hacer doble clic en la etiqueta y Editar el

nuevo nombre, tal como se muestra en la siguiente figura:

Otra forma de cambiar el nombre a la hoja es haciendo clic con el botón derecho

del mouse sobre la etiqueta, apareciendo el menú contextual que se

muestra en la figura.

Seleccionamos la opción Cambiar nombre, a continuación

escribimos el nuevo nombre de la hoja.

Es posible mover las hojas, tanto dentro de un libro de trabajo, como a

otro libro ya existente o creado especialmente, pero no se podrá hacer

utilizando los comandos Cortar, Copiar y Pegar.

Para mover una hoja a otra posición del libro de trabajo basta

con arrastrar la etiqueta de la hoja que se desea mover.

Aparecerá un triángulo que indica dónde se va a insertar la

hoja. Una vez en la posición deseada, se suelta el botón del

mouse, con lo cual la hoja se moverá a la nueva posición.


Se puede mover más de una hoja, seleccionando varias hojas y arrastrándolas a

la posición deseada. Aunque las hojas seleccionadas no fueran contiguas, en la

nueva posición serán insertadas juntas.

Las hojas de un libro pueden moverse a otro ya existente, o a uno nuevo,

mediante los siguientes pasos:

1. Seleccionar las hojas que se desean mover.

2. Hacemos clic con el botón derecho del mouse sobre alguna de las etiquetas de

las hojas seleccionadas, del menú contextual que aparece seleccionamos

Mover o Copiar…

3. Aparece el siguiente cuadro de diálogo.

4. Desplegamos la lista “Al libro” y seleccionamos el libro

de trabajo destino al que queremos copiar o mover la

hoja seleccionada (debe estar abierto).

5. En el recuadro “Antes de la hoja” aparecerán todas las

hojas de que dispone el libro seleccionado en el paso

anterior. Hacemos clic en la hoja donde queramos que se inserten las hojas.

6. Por último, marcamos la casilla “Crear una copia” si lo que queremos es copiar

las hojas, si no marcamos, las hojas se moverán, y presionamos Aceptar.

Si existe alguna hoja con el mismo nombre en el libro de trabajo de destino,

se cambiará el nombre de la hoja que se ha movido o copiado.

Si se quiere crear un libro nuevo con las hojas seleccionadas, entonces se

elige como libro de destino “Nuevo libro” en la lista “Al libro”.


En hojas muy extensas, nos puede resultar cómodo dividir la

ventana de visualización en partes que nos permitan ver, al

mismo tiempo, distintas zonas de la hoja no contiguas. En este

caso utilizaremos la opción “Inmovilizar Paneles”.

Realizar las siguientes acciones:

1. Se selecciona una celda a partir de la cual se quiere hacer la división, en

nuestro caso haremos clic en la celda H6.

2. A continuación activamos la opción “Vista”.

Dentro de esta opción disponemos del grupo de herramientas “Ventana”.

Seleccionamos la opción “Inmovilizar Paneles”.

3. Aparece el siguiente menú desplegable.

4. Si desea colocar las divisiones justo en la celda

activa haga clic en “Inmovilizar paneles”.

También puede inmovilizar la fila 1 o la primera

columna de la hoja.

5. En nuestro caso al encontrarse activa la celda H6 y

como seleccionamos “Inmovilizar paneles” Excel

coloca dos divisiones una vertical y una horizontal tal

como se muestra en la siguiente figura:

Para desactivar las divisiones que se ha creado, bastará con seleccionar

la opción “Inmovilizar paneles”

como se muestra en la figura:


Microsoft Excel nos brinda la posibilidad de mejorar el aspecto de la presentación

de datos, como textos, números, bordes y sombreados, etc. Los formatos pueden

ser modificados desde la cinta de Opciones Inicio.

a) FORMATO DE CELDAS

1. FORMATO DE NUMEROS.- Establece la presentación de

los datos numéricos, tales como, moneda, porcentaje, cien-

tífica, contabilidad, etc.

2. FORMATO DE ALINEACION.- Establece la alineación y orientación de

los textos, combinar varias celdas en una, etc.

3. FORMATO DE FUENTES.- Establece el tipo de

fuente, así como su estilo, tamaño, color, etc.

4. FORMATO DE BORDES.- Establece las líneas de

borde en el contorno de las celdas.

5. FORMATO DE TRAMAS.- Establece los efectos

de sombreados de las celdas con diferentes estilos

y colores.

6. FORMATO DE PROTECCION.- Permite proteger/Despro-

teger las celdas; así mismo, ocultar su contenido a través

del grupo Celdas.

b) FORMATO DE FILAS.- Podemos modificar el alto de una

fila, desde el grupo Celdas, especificando el nuevo alto de

la fila.


c) FORMATO DE COLUMNAS.- En el grupo Celdas, podemos modificar el

ancho de la columna, especificando el nuevo ancho de la columna.

d) AUTOFORMATOS.- Aplica a un rango, uno de los forma-

tos preestablecidos. Es aplicable a las tablas.

Nuestras tablas pueden tener un formato adecuado a la

información que debe mostrar. Para ello, podemos hacer

uso del grupo Alineación, en la ficha Inicio. En este

grupo, encontramos.

a) Alinear en la parte superior

b) Alinear en el medio

c) Alinear en la parte inferior

d) Alinear texto a la izquierda

e) Centrar

f) Alinear texto a la derecha

g) Orientación

h) Aumentar sangría

i) Disminuir sangría

j) Ajustar texto

k) Combinar y centrar

Cada celda puede ser editada con un formato adecuado a la información que con-

tiene. En este grupo encontramos:

a) Formato de numero

b) Formato de numero de contabilidad

c) Estilo porcentual

d) Estilo millares

e) Aumentar decimales

f) Disminuir decimales


Las celdas de nuestras tablas, también, pueden te-

ner un estilo con un adecuado color de resaltado, en

caso se requiera resaltar la información contenida

en ella. En el grupo Estilos, encontramos una varie-

dad de colores para mejorar la presentación de

nuestras celdas.

A.- Abrir Libro1 y realizar lo siguiente:

a) En la hoja1, debe editar lo siguiente:

1. En la celda B2 escriba LISTADO DE ALUMNOS Y ASISTENCIA.

2. Combinar celdas desde A2:F2:

Seleccione desde la celda A2 hasta F2

En la Opción Inicio, ubicamos el grupo Alineación.

Desplegamos las opciones del comando Combinar y centrar.

Elegimos Combinar y Centrar.


3. En la celda A4 escribir Nº ORDEN.

Seleccionar la celda A4.

En el grupo Alineación, hacer clic en el comando Ajustar texto.

Hacer clic en el comando Alinear en el medio y en el comando

Centrar.

4. En la celda B4 escribir Apellidos y nombres.

Seleccionar la celda B4.

En el grupo Alineación, hacer clic en los comandos Alinear en

el medio y el comando Centrar.

5. Edite 10 alumnos.

6. Ordenar en forma ascendente.

7. Seleccionar la celda combinada A2:F2:

En el grupo Fuente, ubicar el co-

mando Borde.


Desplegar las opciones y elegir Todos los bordes.

8. Seleccionar A4:F14 y dar borde

b) En la hoja2, debe editar lo siguiente:

1. En la celda B2 escriba LISTADO DE

NOTAS.

2. Combinar celdas desde A2:F2

3. En la celda A4 escribir Nº ORDEN.

4. En las celdas B4, C4, D4, escribir No-

ta1, Nota2, Nota3, respectivamente.

5. Edite las tres notas para 10 alumnos.

c) En la hoja 3, debe editar lo siguiente:

1. En la celda B2 escriba PROME-

DIOS.

2. Combinar celdas A2:C2.

3. Copiar A4:B14 de la hoja 1 en la Ho-

ja3.

4. En la celda C4 escribir PROMEDIO.

5. En la celda C5 escribir =PROME-

DIO(Hoja2!B5:D5)


6. Copie la formula hasta C14.

7. En la hoja 4, debe tener la planilla completa, copiando de las hojas res-

pectivas.

8. Guardar El Libro1 con el nombre de LISTADO.

B.- Genere un nuevo libro y realice las siguientes acciones:

1. En la hoja 1 debe contener código y listado de vendedores.

2. En la hoja 2 debe contener el código y los montos por ventas de tres me-

ses.

3. En la hoja 3 debe contener el código y el promedio de ventas de los tres

meses.

4. En la hoja 4 debe mostrar el listado completo.

C.- Genere un nuevo libro y realice las siguientes acciones:

1. En la hoja 1 debe contener código y listado de productos electrodomésticos.

2. En la hoja 2 debe contener el código y la cantidad de ventas de tres meses.

3. En la hoja 3 debe contener el código y el promedio de ventas de los tres

meses.

4. En la hoja 4 debe mostrar el listado completo.

OPERADORES Y FUNCIONES

Una fórmula es una secuencia formada por valores constantes, referencias a otras

celdas, nombres, funciones, u operadores. Esta técnica, permite realizar diversas

operaciones con los datos de las hojas de cálculo como *, +, -, Seno, Coseno, etc.

En una fórmula se pueden mezclar constantes, nombres, referencias a otras cel-

das, operadores y funciones.

La fórmula se escribe en la barra de fórmulas y debe empezar con el signo = ó el

signo +

OPERADORES


Los distintos tipos de operadores que se pueden utilizar en una fórmula son:

1. OPERADORES ARITMÉTICOS se emplean para producir resultados nu-

méricos. Tenemos: + - * / % ^

2. OPERADOR TIPO TEXTO se emplea para concatenar celdas que conten-

gan texto. Tenemos: &

3. OPERADORES RELACIONALES se emplean para comparar valores y

proporcionar un valor lógico (verdadero o falso) como resultado de la com-

paración. Tenemos: < > = <= >= <>

4. OPERADORES DE REFERENCIA indican que el valor producido en la cel-

da referenciada debe ser utilizado en la fórmula.

En Excel pueden ser:

a) Operador de rango indicado por dos puntos (:), se emplea para indicar

un rango de celdas. Ejemplo: A1:G5

b) Operador de unión indicado por una coma (,), une los valores de dos o

más celdas. Ejemplo: A1,G5

JERARQUIA DE OPERADORES

Cuando hay varias operaciones en una misma expresión, cada parte de la misma

se evalúa y se resuelve en un orden determinado. Ese orden se conoce como

prioridad o jerarquía de operadores.

Cuando hay expresiones que contienen operadores de más

de una categoría, se resuelve antes las que tienen operado-

res aritméticos, a continuación las que tienen operadores de

comparación y por último las de operadores lógicos .


Los operadores de comparación tienen toda la misma prioridad, es decir que son

resueltos de izquierda a derecha, en el orden en que aparecen.

Los operadores lógicos y aritméticos son resueltos en el siguiente orden de priori-

dad (de mayor a menor):

ARITMÉTICOS LÓGICOS

Exponenciación (^) Not

Negación (-) And

Multiplicación (*) y División (/) Or

Adición (+) y Sustracción (-)

Concatenación de caracteres (&)

Cuando hay multiplicación y división en la misma expresión, cada operación es re-

suelta a medida que aparece, de izquierda a derecha. Del mismo modo, cuando

se presentan adiciones y sustracciones en una misma expresión, cada operación

es resuelta en el orden en que aparece, de izquierda a derecha.

El operador de concatenación de cadenas de caracteres (&) no es realmente un

operador aritmético pero es prioritario respecto a todos los operadores de compa-

ración.

FUNCIONES

Una función es una fórmula especial escrita con anticipación y que acepta un valor

o valores. Realiza unos cálculos con esos valores y devuelve un resultado.

Todas las funciones tienen que seguir una sintaxis y si ésta no se cumple, Excel

nos mostrará un mensaje de error.

1) Los argumentos o valores de entrada van siempre entre paréntesis. No de-

jes espacios antes o después de cada paréntesis.

2) Los argumentos pueden ser valores constantes (número o texto), fórmulas

o funciones.

3) Los argumentos deben de separarse por un punto y coma ";".


1.- SUMA.- Esta función obtiene la suma de todos los datos numéricos que se en-

cuentran en el rango especificado.

SINTAXIS: +SUMA(rango)

Donde:

Rango.- Es un bloque rectangular de una o más celdas que Excel trata como una

unidad.

2.- Min.- Esta función obtiene el mínimo valor numérico de los datos numéricos

que se encuentran en el rango especificado.

SINTAXIS: +Min(rango)

3.- Max.- Esta función obtiene el máximo valor numérico de los datos numéricos

que se encuentran en el rango especificado.

SINTAXIS: +Max(rango)

4.- Promedio.- Obtiene el promedio aritmético de los datos numéricos que se en-

cuentran en el rango especificado.

SINTAXIS: +Promedio(rango)

5.- Abs.- Obtiene el valor absoluto de un numero.

SINTAXIS: +Abs(expr)

Donde:

Expr: Es una expresión numérica; cuyo valor absoluto se desea obtener.

6.- Redondear.- Redondea un numero real con la cantidad de cifras decimales es-

pecificado.

SINTAXIS: +Redondear(expr;n)

7.- Entero.- Redondea un numero al entero inferior más próximo.

SINTAXIS: +Entero(expr)


8.- Contara.- Cuenta las celdas que no están vacías en el rango especificado. Se

utiliza para contabilizar elementos de una lista.

SINTAXIS: +CONTARA(rango)

9.- Contar.- Se encarga de contabilizar únicamente aquellas celdas que contienen

datos numéricos en el rango especificado.

SINTAXIS: +Contar(rango)

10.- Contar.SI.- Cuenta todas las celdas del rango que cumplan con el criterio es-

pecificado.

SINTAXIS: +Contar.SI(rango;Criterio)

11.- Sumar.SI.- Suma los datos numéricos de un rango, cuyas celdas correspon-

dientes a evaluar coincidan con el argumento y criterio.

SINTAXIS: +Sumar.SI(rango_evaluar;Criterio;rango_suma)

12.- Función SI.- Devuelve un valor si la expresión es VERDADERO y otros valor

si la expresión es FALSO.

SINTAXIS: +SI(expr;valor_V;valor_F)

13.- Función Y.- Devuelve VERDADERO si todos los argumentos son VERDADE-

RO; devuelve FALSO si uno o más argumentos son FALSO. La

función Y se emplea dentro de la función SI.

SINTAXIS: +Y(expr1;expr2;…)

14.- Función O.- Devuelve VERDADERO, si alguno de los argumentos es VER-

DADERO; Devuelve FALSO si todos los argumentos son FAL-

SO. La función, se emplea dentro de la función SI.

SINTAXIS. +(EXPR;EXPR2;…)

15.- BuscarV.- Busca un valor específico en la primera columna de una tabla y de-

vuelve el dato correspondiente a la fila donde encontró el valor.


SINTAXIS: +BuscarV(valor_buscado;tabla;índice;ordenado)

Donde:Valor_buscado: Es el valor que se busca en la primera columna de la tabla.

Tabla: Es un rango de celdas donde se buscan los datos.

Índice: Es el indicador de columna de la tabla, que determina que dato

se va a obtener.

Ordenado: Puede tomar dos valores Verdadero o Falso. Sera verdadero si

los datos de la primera columna de la tabla están ordenados en

forma ascendente; caso contrario será Falso.

16.- BuscarH.- Busca un valor específico en la primera fila de una tabla y devuel-

ve el dato correspondiente a la columna donde encontró el valor.

SINTAXIS: +BuscarH(valor_buscado;tabla;índice;ordenado)

Donde:Valor _buscado: Es el valor que se busca en la primera fila de la tabla.

Tabla : Es el rango de celdas donde se buscan los datos.

Índice : Es el indicador de la fila de la tabla, que determina que dato se va

a obtener.

Ordenado : Puede tomar dos valores Verdadero o Falso. Sera verdadero si los

datos de la primera fila están ordenados ascendentemente, caso

contrario será falso.

1.- En la siguiente tabla, escriba las funciones para obtener los resultados corres-

pondientes.


+Min(C8:C13)

+Max(C8:C13)

2.- Obtener el promedio de notas; luego el promedio redondeado con dos decima-

les.

3.- En la bodega “El Detalle”, se desea determinar el pedido de bebidas gaseosas.

Se tiene 135 botellas de Sprite de litro, 28 de Kola Inglesa mediana y 35 bote-

llas de Coca Cola de 2 Litros.


+ENTERO(E4/D4)

+REDONDEAR(PROMEDIO(C4:E4),2)

+PROMEDIO(C4:E4)

+Promedio(C8:C13)

+Suma(C8:C13)

Posteriormente, copie la formula en las celdas F5 y F6.

4.- Se tiene la planilla de información de reservaciones para viajes al interior del

país.

Resolver en la hoja Resumen:

a) Cantidad de viajeros.

b) Cantidad de personas que dieron su edad.

c) Canti-

d) Pago acu-mula-do de pasa-jes, según des-tino de viaje.


+CONTAR(C5:C15)

+CONTARA(B5:B15)

Las siguientes funciones se refieren a conceptos contables y financieros, que per-

miten dar valor agregado a una organización empresarial, buscando ser competiti-

va y aumentar un valor económico de sus operaciones.

Función Descripción Argumentos+PAGO(interes;tiempo,capital) Esta función calcula los pagos anuales periódi-


cos que tendremos que amortizar sobre un prés-tamo, a un interés determinado, y en un tiempo x.

+PAGO(interes/12;tiempo*12;capital) En este caso, podemos ver los pagos mensua-les, el interés se divide entre 12 y el tiempo se multiplica por 12

+ PAGOINT(tasa;periodo;nper;va;vf;tipo) Calcula el interés pagado en un periodo específico por una inversión basán-dose en una tasa de interés constante y pagos en periodos constantes.

Tasa: Es la tasa de interés del periodoPeriodo: Es el periodo para el que se desea calcular el interés y deben estar entre 1 y el argumento nperNper: Es numero total de pagos del préstamoVa: Es el valor actual de una serie de pagos futurosVf : Es el valor futuro de una serie de pagos futuros. Si se omite se calcula como cero.Tipo: Es un numero 0 o 1 e indica el vencimiento de pagosTipo 0. Al final del periodoTipo 1: Al inicio del periodo

+INT.EFECTIVO(int_nominal,num_per_año) Devuelve la tasa de interés efectiva anual, si se conoce la tasa de interés nominal anual y el nu-mero de periodos de capitalización de interés en un año.

+TASA.NOMINAL(tasa_efectiva,num_per) Devuelve la tasa de interés nominal anual, si se conoce la tasa de interés efectiva anual y el nu-mero de periodos de capitalización de interés en un año.

+ PAGOPRIN(tasa;periodo;nper;va;vf;tipo) Calcula el pago sobre el capital de una inversión durante un periodo determinado, basándose en una tasa de interés constante y pagos periódicos constantes.

+ PAGO.PRINC.ENTRE(tasa;nper;vp;per_inicial;per_final;tipo)Calcula la cantidad acumulada de capital pagado de un préstamo entre dos periodos (per_inicial y per final)

1.- El supermercado “Las Verduras”, desea extender un crédito de S/. 1000 000.00

a un interés del 10% durante un periodo de 10 años a la

cooperativa “La Parcela”. Se desea saber cual es el monto

mensual y anual a pagar.


En la celda B8 debe editar lo siguiente: =+PAGO(B5;B6;B4)

En la celda B9 debe editar lo siguiente: =+PAGO(B5/12;B6*12;B4)

El Resultado aparecerá en rojo (negativo). Para convertirlo en positivo, utilizar

=+ABS(PAGO(B5;B6;B4))

2.- El Banco “El Préstamo”, otorga un crédito de S/. 2 000 000.00 con un interés

del 9% en un plazo de 2 años, es decir 24 meses. Al cliente se le debe

entregar un documento impreso del pago mes a mes, indicando el capital real,

interés y el pago pendiente.

Edite en las celdas que se indican:

B9: =+ABS(PAGO($B$5/12;$B$6*12;$B$4))

C9: =B9-D9

D9: =+ABS(PAGOINT(B5/12;1;B6*12;B4))

E9: =C9

F9: =+B4-E9

En la segunda fila las celdas que cambian son:

D10: =+ABS(PAGOINT($B$5/12;1;$B$5*12;F9))

E10: =+E9+C10

F10: =+F9-C10

Seleccione cada una de estas celdas y copie hasta el mes 24.


3.- Calcular el interés efectivo para un capital de S/. 1000000.00, con un rendi-

miento nominal de 27% anual, a un plazo de 90 días. (Desde el 15 de abril hasta

15 de julio).

El resultado que se obtiene es en decimales y debe

darse el formato de porcentaje.

En la celda B33, escribir: =+(1+B12/B10)^B10-1

En la celda B36, escribir: =+INT.EFECTIVO(B12;B10)

4.- Se desea saber cual es el saldo, después de dos años, de

un crédito de vivienda por S/. 50 000.00, adquirido a 180 me-

ses a una tasa del 3%.

En la celda B5, escribir: =PAGO(3%;180;-50000000;0;0)*24

En la celda B6, escribir:

=-PAGO.INT.ENTRE(3%;180;50000000;1;24;0)

En la celda B7, escribir: =+B4-(B5-B6)

En la celda B9, escribir:

=50000000+PAGO.PRINC.ENTRE(3%;180;50000000;1;24;0)

La función PAGO.PRINC.ENTRE, es igual a la función PAGO.INT.ENTRE; pero

se puede obtener una ventaja adicional: combinándola con el valor del crédito es

posible saber cual es el saldo de la deuda en un momento determinado.

Microsoft Excel permite asignar nombres a

las celdas y rangos para facilitar su refe-

rencia en las formulas. Cuando una celda o rango tiene un nom-

bre, esta podrá ser utilizada en cualquier hoja del libro.


Cuadro de nombres

Para nombrar la celda o rango, realizar las siguientes acciones:

Seleccione la celda o rango a nombrar.

Hacer clic con el botón izquierdo del mouse, en el

cuadro de nombres.

Escriba el nombre que desea asignarle.

Presione la tecla Enter.

Otra forma de realizarlo:

Seleccione el rango a nombrar.

Activar la opcion formulas.

En el grupo Definir Nombres, desplegar el

iniciador de dialogo de Asignar nombre a

un rango.

Elegir Definir Nombre.

En el espacio Nombre, asignar el nombre al rango.

Hacer clic en el botón Aceptar.

Si desea cambiar de nombre siga el mismo procedimiento.

Para eliminar el rango, realizar las siguientes acciones:

En el cuadro de nombres, escribir el nombre del rango a eliminar.

En la opcion Inicio, ir al grupo Celdas y hacer clic en Eliminar.

Cuando se han dado varios nombres a rangos, el cuadro de nombres, muestra

los nombres que se han creado.


Asignar nombre al rango.

Referencia de las celdas que comprende el rango.

Se ha seleccionado dos rangos de cel-

das y se ha establecido los nombres de

PRECIOS y ZONAS. En este caso, se

ha establecido nombre a dos columnas

para cada nombre.

Puede establecer nombre a una sola columna. Como en este caso, el rango

C2:C6 se ha establecido el nombre de Departamento, el rango F2:F6 tiene el nom-

bre Precio y los rangos B2:B6 y E2:E6 con el nombre Zona.

Por otro lado, se puede establecer nombre al rango de datos a utilizar, por ejem-

plo:


Hacer clic para desplegar los nombres.

En el primer caso, se pide Sumar.Si

en las celdas E5:E15, existe la palabra (destino) “TACNA”, entonces, sumar el va-

lor correspondiente que se encuentra en el rango F5:F15. Se sigue el mismo pro-

ceso en la siguiente celda, cambiando la palabra TACNA, por AREQUIPA y por ul-

timo, cambiamos por la palabra CUZCO.

En el segundo caso, el rango E5:E15, tiene como nombre DESTINOS y el rango

F5:F15, tiene como nombre PRECIOS. Se indica Sumar.Si en el rango DESTI-

NOS, existe la palabra “TACNA”, sumar su correspondiente valor en el rango

PRECIOS. Se aplica la misma formula, en la siguiente celda, cambiando la pala-

bra TACNA por AREQUIPA, finalmente, cambiamos con la palabra CUZCO.

1.- Se tiene la siguiente plani-lla de in- for-mación estu-diantil:


Se requiere saber:

a) La cantidad de alumnos por sexo.

b) La cantidad de alumnos por especialidad.

c) Cuantos alumnos dieron su edad.

d) El pago acumulado por especialidad.

Alumnos por sexo:

2.- La tienda agraria “La Parcela”, tiene a disposición de los agricultores los si-

guientes productos para la actividad agrícola:


+Contar.Si(D5:D15;”F”)

+Contara(C5:C15)

+Contar.Si(E5:E15;”Computacion”)

+Sumar.Si(E5:E15;”Computacion”;F5:F15)

Se requiere saber:

a) Cantidad de nombres de productos por marca.

b) Cantidad total de agroquímicos por marca.

c) Cantidad total de productos por clase.

d) Total de inversión por marca.

3.- Se tiene la siguiente relación de alumnos ingresantes a un centro de estudios:


=+CONTAR.SI(D5:D17;"Bayer")

=+SUMAR.SI(D5:D17;"Bayer";E5:E17)

=+SUMAR.SI(C5:C17;"Fungicida";E5:E17)

=+SUMAR.SI(D5:D17;"Bayer";G5:G17)

Se pide obtener:

a) Cantidad de alumnos ingresantes por canal.

b) Cantidad de alumnos por modalidad.

c) Puntaje mas alto obtenido

d) Puntaje mínimo obtenido.

Sugerencia para el desarrollo de la practica:

Emplear las formulas, del ejercicio anterior, cambiando las palabras que co-

rrespondan para la busqueda.


La función =SI( ), es una de las mas potentes que tiene Excel. Esta función com-

prueba si se cumple una condición para dar como resultado VERDADERO, caso

contrario, da como resultado FALSO.

Esta función puede ser compleja o simple. En su variante mas simple se presenta

como: +SI(Condicion;Verdadero;Falso).

Ejemplo, si se trata de otorgar un descuento,

cuando la operación es al “contado”, ejecuta-

ra el descuento; caso contrario, colocara un

cero o no realizara ningún calculo.

En este caso, tomara el dato en la celda que se indica el tipo de operación.

En este caso, la operación fue al “crédito” y

coloco un cero en la casilla correspondiente.

Cuando se tiene que cumplir más de una condición, por ejemplo dos condiciones:

a) Que la función =SI realizara algo, solo si se tuvieran que cumplir las dos

condiciones.

b) Que la función =SI realizara algo, solo si se cumpliese una de las dos con-

diciones.

Se puede controlar una u otra forma, con dos operadores lógicos: Y, O.

La sintaxis de la orden seria la siguiente:

a) =SI(Y(Condición1;Condición2)), se deben cumplir una de las condiciones.

b) =SI(O(Condición1;Condición2)), en caso que se deba cumplir una o las

dos condiciones.


Ejemplo: La empresa “El Frutal”, realiza operaciones de ventas y compras diarias.

Necesita gestionar su información para el manejo del efectivo y la toma de decisio-

nes en sus operaciones.

Si tenemos el siguiente modelo de solu-

ción, debemos observar que si es una

compra, la operación se registrara en el

Debe, si es una venta, la operación se

registrara en el Haber.

Para un control de estas operaciones, introduciremos en la celda E5, una función

como esta: +SI(O(C6>0;D6>0);E5+C6-D6;” “); luego, copiamos hacia las celdas

inferiores que se van a utilizar ( E6, E7, …).

Observa a continuación las partes de la función:

+SI(O La letra O controla que se cumpla una de las dos condiciones: C6>0; D>0.

C6>0, primera condición que en la celda C6 haya un numero mayor a cero; es de-

cir, un numero positivo.

D>0, la segunda condición controla que en la celda D>0 haya un numero positivo.

De cumplirse una de las dos condiciones, se ejecutara esta formula: E5+C6-D6.

;” “), caso de no cumplirse ninguna condición, no saldrá nada. Las dos comillas

quieren decir carácter nulo.

En caso de no llevar las comillas, al copiar la

formula hacia las demás celdas, saldrá un

valor FALSO.

La formula explicada anteriormente, es lo

mismo que: =E5+C6-D6. En este caso, si se

copia a las demás celdas, siempre nos mos-

trara el saldo anterior, aun introduciendo o no cantidades en el debe o haber.


La función “Y” devuelve verdadero si todos los argumentos son verdaderos, de-

vuelve falso si uno o más argumentos son falsos. La función Y se emplea dentro

de la función SI.

SINTAXIS: Y(expr1; expr2; …)

Donde:

expr1, expr2, …: son los argumentos que se van a evaluar, hasta un máximo de

255 condiciones.

Generalmente la función Y es usada para expandir la utilidad de otras funciones

que realizan pruebas lógicas.

Al utilizar la función SI se realiza una prueba lógica y; luego, devuelve un valor si

la prueba se evalúa como Verdadero y otro valor si la prueba se evalúa como Fal-

so.

Al introducir la función Y como argumento prueba_lógica de la función SI, se pue-

de probar varias condiciones diferentes en lugar de solo una.

FORMULA DESCRIPCION RESULTADO

=Y(VERDADERO;VERDADERO) Todos los argumentos son verdaderos

VERDADERO

=Y(VERDADERO;FALSO) Un argumento es Falso FALSO

Edite los datos que aparecen en el grafico y en la celda B5, escribimos la siguiente

formula: =Y(5<B3;B3<B4).

En la celda D3, edite la siguiente formula:=Y(3+3=6; 3+4=7)

El resultado es verdadero.

Para un resultado Falso, escribir lo siguiente:

=Y(5<B3;B3>B4)

Mostrando un mensaje. Edite la siguiente formula en la celda C7:

=SI(Y(150<B3; B3<4); B3; "El valor está fuera del rango")


Devolverá verdadero, si alguno de los argumentos es verdadero, caso contrario,

devolverá falso si todos los argumentos son falsos.

SINTAXIS: =O(expr1; expr2; …)

Donde:

expr1, expr2, … Son los argumentos que se van a evaluar y que pueden tener

como resultado Verdadero o Falso.

Para cualquiera de las dos funciones, es necesario tener en cuenta lo siguiente:

Los argumentos deben evaluarse como valores lógicos: Verdadero o Falso;

pueden ser matrices o referencias que contengan valores lógicos.

Si algún argumento de matriz o de referencia contiene texto o celdas va-

cías, esos valores se pasaran por alto.

Si se especifica un rango que no contiene valores lógicos, la función Y/O

devuelve el valor de error #¡VALOR!

FORMULA RESULTADO

=O(VERDADERO) VERDADERO

=O(Todos loas argumentos falsos) FALSO

=O(VERDADERO;FALSO;VERDADERO) Al menos un argumento es VERDA-DERO el resultado es VERDADERO

En una nueva hoja, edite la siguiente formula:

=O(5>3)

=O(2+3=7;4+6=11)

=O(3+3=6;4+7=12;6+8=14)

Observe los resultados de cada uno de ellos.


1.- Controlar la fecha de vencimiento de facturas por cobrar a varias empresas.

La formula tendrá en cuenta la fecha de la factura y la fecha actual. Cuando detec-

te que han pasado mas de 30, 60 o 90 días, automáticamente la cantidad saltara a

la siguiente columna recordándo que ha sobrepasado los días de vencimiento.

a)

En la celda B1 escribir lo siguiente: +HOY( )

Esta función muestra la fecha actual del ordenador.

b) En la celda F4 escribir lo siguiente: +D4+E4

Suma los días de vencimiento mas la fecha actual y nos da la fecha de ven-

cimiento.

c) En la celda G5 escribir lo siguiente: +SI(F4=$B$1;C4;0)

Aparece la cantidad facturada si la fecha de vencimiento coincide con la de

hoy. Modifique las fechas para ver su comportamiento y anote los cambios.

d) En la celda H4 escribir lo siguiente: +SI(Y(F4<$B$1;($B$1-30)<F4);C4;0)


Si la fecha de vencimiento es menor a la de hoy y mayor que la actual me-

nos 30 días, aparecerá la cantidad.

e) En la celda I4 escribir lo siguiente: +SI(Y(F4<$B$1-30;($B$1-60)<F4);C4;0)

Si la fecha de vencimiento es menor a la fecha actual menos 30 días, y ma-

yor a la fecha actual menos 60 días, aparecerá la cantidad.

f) En la celda J4 escribir lo siguiente: +SI(F4<$B$1-90;C4;0)

Si la fecha de vencimiento es menor a la actual menos 90 días, aparecerá

la cantidad.

2.- Se tiene un listado de personas que van adquirir entradas para el festival de

danzas por el aniversario de la ciudad. El

precio de las entradas es de S/. 10.00. El

municipio esta realizando una promoción, de

tal manera que todos los niños menores de

12 años y adultos mayores a 60 años no pa-

garan el precio de la entrada.


1. La celda C2 se le asigna el nombre de Precio.

2. En la celda D5, escribir la siguiente formula: =SI(O(C5<12;C5>60);0;Precio)

3. Copiar la formula a las demás celdas.

3.- Con los datos de la tabla CONTROL DE PASAJEROS:

a) Efectuar una exoneración de pago a pasajeros mayores de 60 años.

b) Efectuar un descuento del 10 por ciento a pasajeros menores de 10 años.

4.- Con los datos de la tienda agraria “La Parcela”, determinar un descuento para

los productos Bayer.


5.- Con los datos de la tienda “El Detalle”, elaborar una tabla que muestre el men-

saje “Compra menor a S/. 1000.00” y aplicar un descuento para los consumidores

de la zona “Norte”.

En algunos casos, puede que deba utilizar una función

como uno de los argumentos (argumento: valores que utili-

za una función para llevar a cabo operaciones o cálculos). El

tipo de argumento que utiliza una función es específico de esa función. Los argu-

mentos más comunes que se utilizan en las funciones son números, texto, referen-

cias de celda y nombres de otra función.

=FuncionA(FuncionB+FuncionC(funcionD))

En la siguiente fórmula se utiliza una función anidada PROMEDIO y compara el

resultado con el valor 50.

Cuando se utiliza una función anidada como argumento, deberá

devolver el mismo tipo de valor que el que utilice el argumento.

Ejemplo, si el argumento devuelve un valor VERDADERO o FAL-

SO, la función anidada deberá devolver VERDADERO o FALSO. Si éste no es el

caso, Microsoft Excel mostrará el valor de error #¡VALOR!

Una fórmula puede contener como máximo siete niveles de fun-

ciones anidadas. Si la Función B se utiliza como argumento de

la Función A, la Función B es una función de segundo nivel.

En el ejemplo anterior, la función PROMEDIO y la función

SUMA son ambas funciones de segundo nivel porque son argumentos de la fun-

ción SI. Una función anidada dentro de la función PROMEDIO será una función de

tercer nivel, etc.


Función de 3er nivelFunción de 2ª nivelFunción de 1er nivel

Puede anidarse de 1 a 30 condiciones.

Se puede anidar hasta 7 niveles de funciones anidadas.

1.- La empresa “El Farolito”, desea realizar una hoja de facturación, donde el tipo

de IGV se introduce de la siguiente forma: 0 (0%), 1(4%), 2(7%), 3(18%).


a) En la celda E4, escribir: =C4*D4 y copiar hasta la celda E11.

b) En la celda G4, escribir:

=+SI(F4=0;0;SI(F4=1;(E4*4%);SI(F4=2;(E4*7%);SI(F4=3;(E4*18%);"ERROR"))))

Copiar hasta la celda G11.

Para realizar los cálculos no podemos utilizar el valor

de la celda del tipo de IGV, porque no corresponde al

valor, debemos utilizar su equivalente. Por lo tanto,

debemos utilizar la función SI, de tal manera que cal-

cule los porcentajes adecuados, según el tipo de IGV

y controlando la posibilidad de error.

c) En la celda H4, escribir: =E4+G4, copiar hasta la celda H11.


2.- La municipalidad Local, desea proteger del medio ambiente a la población y

mediante una ordenanza, especifica que los vehículos pesados y los Diesel de-

ben pagar el 0,75% sobre el valor del vehículo y el resto de vehículos pagaran

0,5%.


a) En la celda D4, escribir: =SI(O(A4="Pesado";B4="Diesel");0,75;0,5)

b) Copiar hasta la celda D8

c) En la celda E4, escribir: +C4*D4

d) Copiar hasta la celda E8

3.- Una empresa decide otorgar bonificación a sus trabajadores, de acuerdo al

sueldo y el número de hijos que tiene, en base a la siguiente tabla:



a) En la celda D4, escribir:

=SI(Y(B4<800;C4>=3);300;SI(B4<800;150;SI(Y(B4<1000;C4>=2);200;SI(B4

<1000;100;SI(Y(B4<1200;C4>=3);120;SI(Y(B4>=1200;C4>=3);60;50))))))

b) En la celda E4, escribir: =B4+D4

4.- La bodega “La Yapa”, esta realizando una promoción por el aniversario de la

provincia de la ciudad. Determinar en una hoja de cálculo los precios de venta

de los productos si se dan las siguientes especificaciones:

Si:


1. En la celda E3, escribir:

=+SI(C3="A";D3+0,5;SI(C3="B";D3+20%*D3;SI(C3="C";D3+10%*D3;"Verifique")))

2. Copiar hasta la celda E11.

3. Analizar los resultados.


La función BUSCARV devuelve un valor determinado de una columna indicada

perteneciente a una tabla, según un índice.

SINTAXIS:

+BUSCARV(Celda;Rango;Columna)

También puede ser:

=BUSCARV(valor buscado; matriz de comparación; indicador columnas; [ordenado])

valor buscado:

matriz de comparación:

indicador columnas:

ordenado:

Es el valor buscado en la primera columna de la tabla.

Es la tabla donde se efectúa la búsqueda.

Es un número que especifica la columna de la tabla de

donde se devuelve un valor.

Es un valor lógico (VERDADERO o FALSO) que indica

que la primera columna de la tabla donde se buscan los

datos esta ordenada o no. Si omite este argumento se

considera VERDADERO, es decir, se considera que la

columna uno de la tabla esta ordenada. Si no se está se-

guro poner siempre FALSO.

Se tiene el siguiente listado de productos:

En la celda C4, escribir: =+BUSCARV(C3;A8:C16;2)

En la celda C5, escribir: =+BUSCARV(C3;A8:C16;3)

EXPLICACION:

La formula permite buscar lo que hay en la celda C3 y

lo buscara en el rango A8:C16. Una vez que lo que lo

encuentre (lo encontrara en la 1ª columna); mostrara

lo que hay dos columnas a su derecha (contándose

ella); es decir, la descripción del producto.


El significado de la V en la función es Vertical.

Cuando se posee un conjunto de datos y necesitas obtener un valor

basándote sólo en un campo de referencia, puedes utilizar la fun-

ción de Excel ® BUSCARH.

Podemos usar BUSCARH cuando los valores

de comparación se encuentren en una fila en

la parte superior de una tabla de datos y de-

seas encontrar información que se encuentre

dentro de un número especificado de filas, el

significado de la “H” en la función es horizon-

tal.

Sintaxis

+BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas;ordenado)

Podemos explicar de la siguiente manera:

=BUSCARH(¿Qué valor referencial deseas?;¿Dónde buscas ese valor refe-

rencial?;¿El valor de qué fila deseas que te muestre?;¿Deseas que el valor

sea exacto o aproximado?)

Estructura de la función BUSCARH

Valor_buscado: es el valor que se busca en la primera fila de la tabla. 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.

Indicador_filas: es el número de fila en matriz_buscar_en desde el cual debe de-

volverse el valor coincidente.


Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_bus-

car_en.

Si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_bus-

car_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.


1.- La bodega “Blanca” tiene una lista de control de almacén de productos. Utili-

zando la función BUSCARV realice una consulta por código de producto.

Considerar lo siguiente:

a) El valor a buscar es el código del

producto, en la celda D12.

b) La tabla de datos es el rango B3:E7.

c) El nombre del producto, la unidad

de medida y el stock en almacén se

encuentran en las columnas 2, 3 y 4

respectivamente.

En la celda D14, escribir lo siguiente: +BUSCARV(D12;B3:E7;2;VERDADERO)



2.- La empresa ganadera “La Oveja Negra”, presenta una lista de control de alma-

cén de productos. Realizar una consulta por código de producto.


a) El valor a buscar es el código del producto.

b) La tabla de datos es el rango B·:B7

c) El nombre del producto, la unidad de medida y el stock en el almacén se

encuentran en las columnas 2, 3 y 4 respectivamente.

Los productos son: queso, carne, cuero, Leche y mantequilla.

Las unidades de medida son: molde, kilo, pies, litro y potes


Considere una cantidad como stock para cada producto.

3.- Se tiene una lista de información sobre los cuatro últimos mundiales de futbol.

Se desea obtener la sede del evento y los nombres de los países campeón y

subcampeón para un año cualquiera.


a) El valor a buscar es el año donde se llevo a cabo el mundial de futbol.

b) La tabla de datos es el rango C2:F5.

c) El nombre del país sede, campeón y subcampeón se encuentran en las fi-

las 2, 3 y 4 respectivamente.

En la celda C12, escribir lo siguiente: +BUSCARH(D10;C2:F5;2;VERDADERO)



4.- La granja “La gallinita Ciega” presenta una lista de cuatro tipos de animales:

Gallinas, patos, pavos y

codornices. Se desea el

tipo de ave, cantidad, lugar

de crianza y alimento que

consumen.


Función Descripción ArgumentosHOY( ) Devuelve el número de se-

rie de la fecha actual. El nú-mero de serie es el código de fecha-hora que Microsoft Excel usa para los cálculos de fecha y hora.

FECHA(año,mes,dia) Devuelve el número de se-rie que representa una fe-cha determinada.

Año: El argumento año puede tener de uno a cuatro dígitos.Mes: Es un numero que re-presenta el mes del año.Dia: Es un numero que repre-senta el día del mes.

AÑO(fecha) Devuelve el año correspon-diente a una fecha, devol-viendo un número entero comprendido entre 1900 y 9999.

Fecha: es la fecha del año que desea buscar. Las fe-chas pueden introducirse como cadenas de texto entre comillas, como números de serie o como resultado de otras formulas o funciones.

MES(fecha) Devuelve el número de mes correspondiente a una fe-cha. El mes se expresa como numero entero com-prendido entre 1 (enero) y 12 (diciembre).

Fecha: Es la fecha del mes que desea buscar.

DIA(fecha) Devuelve el día de una fe-cha, representada por un número de serie. El día se expresa como un número entero comprendido entre 1 y 31.

DIASEM(fecha;tipo) Devuelve el día de la sema-na correspondiente a una fecha. El día se devuelve como un numero entero en-tre 1 (domingo) y 7 (sába-do).

Fecha: Es la fecha del día que se intenta buscar.Tipo: Es un numero que de-termina que tipo de valor debe ser devuelto.Números del 1 (domingo 9 al 7 (sábado).Números del 1 (lunes) al 7 (domingo).Números del 0 (lunes) al 6


(domingo).

1.- Ingresar en una hoja de cálculo, la fecha del último aniversario patrio del siglo

pasado, luego, realice lo siguiente:

a) Desglose la fecha ingresada en celdas diferentes con los valores corres-

pondientes al número de día, mes

y año respectivamente.

b) Determine la cantidad de días

transcurridos desde esa fecha

hasta la fecha actual del sistema.

c) Determine que día de la semana

se festejo el aniversario patrio.

En la celda C3, escribir:

=+FECHA(1999;7;28)

En la celda C6, escribir: =+DIA(C3)

En la celda C9, escribir: =+MES(C3)

En la celda C12, escribir: =+AÑO(C3)

En la celda C15, escribir: =+F3 - C3

En la celda F3, escribir: =+HOY()

En la celda E6, escribir:

=+ELEGIR(DIASEM(C3;2);"Lu";"Ma";"Mi";"Ju";"Vi";"Sa";"Do")

La celda C15 aplicar el formato General.

Las celdas C3 y F3, aplicar el formato fecha.

2.- Determinar la cantidad de días

de vencimiento de cada factura en

el siguiente cuadro:


Función Descripción Argumentos

IZQUIERDA(texto;N)

Esta función devuelve N ca-racteres situados en el extre-mo izquierdo de una cadena de texto.

Texto: es la cadena de ca-racteres.N: especifica el número de caracteres que se desea extraer.

DERECHA(texto;N)

Esta función devuelve N ca-racteres situados en el extre-mo derecho de una cadena de texto.

Texto: es la cadena de ca-racteres.N: especifica el número de caracteres que se desea extraer.

EXTRAE(texto;P;N)

Esta función devuelve N ca-racteres de una cadena de texto, empezando en la posi-ción en que se especifique.

Texto: es la cadena de ca-racteres.N: especifica el número de caracteres que se desea extraer.P: Es la posición a partir del cual se van a extraer los caracteres.

VALOR(texto)

Esta función devuelve un tex-to en un valor numérico.

Texto: es la cadena de ca-racteres, que deberá estar conformado por caracteres de números, fechas y horas que reconoce Excel.

TEXTO(valor;formato)

Esta función devuelve un nú-mero en una cadena de ca-racteres, con un formato nu-mérico especifico.

Valor: es un número o ex-presión numérica.Formato: es un formato de número que reconoce Ex-cel.

LARGO(texto)

Esta función devuelve la cantidad de caracteres de un texto.

Texto: es el texto, cuya lon-gitud se desea obtener. Los espacios también se consi-deran como caracteres.

HALLAR(texto1;texto2;P) Esta función obtiene el núme-ro de posición e que empieza un texto dentro de otro, leyen-do de izquierda a derecha. Esta función se emplea ma-yormente para encontrar un carácter dentro de un texto.

Texto1: es el texto que se desea encontrar.Texto2: es el texto donde se hallara la búsqueda.P: es la posición en el tex-to2, a partir del cual empe-zara la búsqueda del tex-


to1.

1.- Mostrar los cinco primeros caracteres de una cadena de texto.

a) En la celda B3 escribir: Titulo

b) En la celda C3 editar un texto compuesta por dos palabras.

c) En la celda C6, escribir: Los cinco primeros caracteres son:

d) En la celda D6, escribir: +IZQUIERDA(C3,5)

2.- Mostrar los cuatro últimos caracteres de una cadena de texto.

a) En la celda C8, escribir: Los cuatro últimos caracteres son:

b) En la celda D8, escribir: +DERECHA(C3,4)

3.- Mostrar los cuatro caracteres de texto, a partir de una determinada posición.

a) En la celda C10, escribir: Los cuatro caracteres, a partir de la letra (elegir

letra) son:

b) En la celda D10, escribir: +EXTRAE(C3,3,4)

4.- Obtener un valor desde una cadena alfanumérica.

a) En la celda B12, escribir: Código de ingreso.

b) En la celda C12, escribir: 98001ª

c) en la celda D14, escribir: +VALOR(IZQUIERDA(C3,2))+1900

5.- Obtener la cantidad de caracteres en una cadena de texto.

a) En la celda B18, escribir Titulo

b) En la celda C18 escribir un texto con dos palabras.

c) En la celda C20 escribir: El titulo tiene:

d) En la celda D20, escribir: +LARGO(C18)

e) En la celda E20, escribir caracteres.


6.- Hallar la posición de un carácter en una cadena de texto.

a) En la hoja2, celda B2 escribir: Titulo.

b) En la celda C2 escribir un texto con dos palabras.

c) en la celda C4 escribir: La posición de la letra “(elegir una letra dentro del

texto)”es.

d) En la celda D4 escribir: +HALLAR(“(letra elegida)”,C2,1)

e) En la celda E4 escribir: caracteres.

7.- Obtener los datos que se indican en la siguiente tabla.

a) En la hoja Pagos, editar las siguientes tablas:

b) Especificaciones:

Obtener la sección de trabajo, según el código de sección (tercer carác-

ter del código del trabajador).

Obtener el haber básico, según el código de la sección (tercer carácter

del código del trabajador)

Utilizar diversas funciones.


8.- Obtener los datos que se indican en la siguiente tabla.

a) En la hoja Abarrotes, editar las siguientes tablas:

b) Obtener la unidad y el stock según el código.

c) Generar otra tabla con código, stock, precio y total.

d) Generar una tercera tabla con código, producto, unidad y precio

9.- En una hoja PLANILLA, editar los siguientes datos:

Especificaciones:


a) Antigüedad; según año de ingreso y fecha actual.

b) Asignar producción; según la tabla adjunta.

10.- En una hoja llmada Bonifica, editar los siguientes datos:

Es-pe-cifi-ca-

ciones:

a) Antigüedad; según tabla anterior.

b) Pago por unidad; según tabla de turnos.

c) Bonificación por categoría; según tabla de categorías.

d) Bonificación por antigüedad: Si antig >15 y Categ= C, Bonif=5% * produc-

ción; sino no le corresponde.


1.- Elabore la siguiente planilla de pagos:

Especificaciones:

a) Antigüedad: según año de ingreso y fecha actual.

b) Pago unidad: según tabla de turnos.

c) Pago bruto: unidades producidas x pago unidad.

d) Bonif_Turno: según tabla de turnos.

%Bonif x pago_bruto

e) Bonif_Cat: según tabla de categorías

f) Bonif_Antiguedad:

Si antigüedad > 15 años y Categ=”C” 5% x pago_bruto

Sino 0

g) Adelanto: 30% x pago bruto, el % de adelanto esta en la celda N3.

h) Neto: Pago_bruto – suma de bonificaciones – adelanto.


i) Total: suma de netos.

j) Usar nombres de celdas y/o rangos.

2.- Elabore la siguiente planilla de sueldos.

a) En la hoja1 debe

ingresar lo siguiente:

b) En la hoja2

debe ingresar

los siguientes

datos:

c) en la hoja3 debe mostrar la boleta de pagos de cada trabajador.

Especificaciones:

Ingresar 10 trabajadores.

Ingresar la hora de ingreso y salida.

Horas netas = 8 horas

Horas extras: (hora de salida – hora de

ingreso) – 8


Monto de hora extra: 1.5 * 40

Para generar la boleta de pago, al ingresar el código del trabajador, los de-

más datos deben autocompletarse.

Un grafico es una representación de los datos de una hoja de cálcu-

lo a través de figuras o líneas que permiten un

análisis e interpretación con mayor claridad.

En muchas ocasiones resulta muy útil que la

información contenida en un libro de Excel se

visualice gráficamente.

Excel posee una herramienta que permite la

construcción de gráficos simples y complejos,

brindando claridad en el momento de analizar la información.

En general, la representación grafica de los datos hace que

estos se vean más interesantes, atractivos y fáciles de leer,

que en otros formatos, dado que Excel provee varios tipos de gráficos, con lo cual

el usuario puede elegir el mas adecuado para cada situación.

El proceso a seguir, para la definición e inserción de un grafico, ya sea en la mis-

ma hoja donde están los datos o en otra hoja del mismo libro, es bastante sencillo.

Las opciones necesarias se encuentran en la cinta de opciones, en la opción In-

sertar; luego, el comando Gráfico.

Los pasos para crear un grafico son los si-

guientes:

1. Seleccionamos los datos que quere-

mos graficar

2. Seleccionamos la opción Insertar

3. Elegimos gráfico de la Cinta de Opcio-

nes

4. Seleccionamos el tipo de gráfico que queremos usar.


Es necesario tener seleccionados los datos que queremos graficar antes de crear

el gráfico. De lo contrario el gráfico se mostrará en blanco y tendremos que ingre-

sar las series de datos posteriormente.

Describiremos algunos tipos de gráficos que cuenta el programa y la forma que re-

presentan los datos de nuestras planillas de cálculo.

Los gráficos circulares permiten representar una serie de datos de

acuerdo al porcentaje que

cada uno de ellos repre-

senta sobre la suma de to-

dos los valores de la serie.

En la tabla se presentan

cada uno de los valores y

abajo la representación circular de cada

uno de esos valores. Este tipo de grafico

expresa gráficamente la diferencia en

porcentaje de un grupo de datos en rela-

ción al total.

Los gráficos bidimensionales permiten representar las series de datos en dos di-

mensiones, o sea los valores se representan alineados en dos ejes perpendicula-

res: el eje horizontal X abscisas) y el eje vertical Y (ordenadas).


Hay tres tipos principales de gráficos bidimensionales: columnas, xy, y líneas.

Salvo en caso de los gráficos xy, las series de valores numéricos se representan

en el eje vertical y las categorías se alinean a lo largo del eje horizontal.

En la siguiente imagen mostramos un ejemplo de un gráfico bidimensional, en este

caso, los datos representados en el

gráfico corresponden a los datos de

la tabla.

En el eje x se representan los me-

ses y en el eje y las ventas.

Los gráficos XY permiten por ejemplo visualizar la variación de un dato en el trans-

curso del tiempo, tal como en la si-

guiente imagen, mostrando la evolu-

ción de la población mundial desde

los años 1800 al 2025.

Un gráfico XY de dispersión tiene dos

ejes de valores y los datos pueden


mostrarse en rangos desiguales o grupos. Es muy usado para datos científicos en

las planillas de cálculo.

A diferencia del anterior estos gráficos no consideran como valores los datos del

eje x, sino como rótulos, por eso, si tomamos como ejemplo la evolución de la po-

blación mundial, vemos que la curva del grafico varía.

Vemos otro ejemplo de grafico de lí-

neas:

Aquí se muestran las ventas de un

producto determinado en distintas

regiones del país.


Los gráficos tridimensionales permiten representar datos en tres dimensiones, o

sea valores que se represen-

tan alineados en tres ejes: el

eje horizontal X (abscisas),

el eje vertical Z (ordenadas),

y el eje Y (series).

El siguiente ejemplo muestra

la comparación de los datos

de ventas para tres regiones

(Sur, Este y Oeste) en los

cuatro trimestres del año.

Un grafico está compuesto de varios objetos: área de trazado, Área de gráfico,

leyenda, títulos, series, rótulos de datos, etc.

La gran mayoría de estos objetos los podemos personalizar. Si seleccionamos el

grafico, aparecen distintas opciones, sobre la cinta de opciones desde las cuales

podemos trabajar. Básicamente se organizan en tres categorías: Diseño, Presen-

tación, Formato.

Desde estas opciones podemos personalizar, agregando información, modificando

el diseño, cambiando la forma de presentación y muchas cosas más.


MANEJO DE DATOS I

LISTA.- Es un rango de celdas que contiene

datos relacionados, tales como, datos de

clientes, productos, etc. Una lista puede utili-

zarse como una base de datos, donde las fi-

las corresponden a los registros y las colum-

nas a los campos. La primera fila de la lista

contiene los nombres de los campos.

CREACION DE LISTAS

Para crear una lista, tener en cuenta las siguientes recomendaciones:

Evite que haya mas de una lista en una hoja de calculo.

Coloque elementos con datos similares en una misma columna de la lista.

Mantenga la lista separada de los demás datos de la hoja de calculo.

Evite las filas y columnas en blanco, para Excel detecte y seleccione con

mayor facilidad la lista.

ORDENAMIENTO DE CAMPOS

a) POR UN SOLO CAMPO.- Para ordenar los registros de una lista por un

solo campo, realice los siguientes pasos:

1. Seleccione el nombre del campo a considerar en el ordenamiento, por

ejemplo, elegir el campo edad.


2. Hacer clic en el comando Orden ascendente o des-

cendente, por ejemplo, elegir orden ascendente.

3. Luego, observe el resultado del ordenamiento, que será similar al de la

siguiente figura.


Campo selec-cionado

b) POR VARIOS CAMPOS A LA VEZ.- Para ordenar los registros de una lis-

ta, por mas de un campo a la vez, realizar los siguientes pasos:

1. Seleccione la lista a ordenar.

2. En el menú de Opciones, seleccione la Opción Datos; luego, Ordenar.

3. En el siguiente cuadro de dialo-

go, seleccione los campos y cri-

terios a considerar en el ordena-

miento de la lista, por ejemplo,

ascendente por apellido paterno

y materno respectivamente.

La lista se ordenara primero ascendentemente por los apellidos paternos de

las personas y luego, se ordenara ascendentemente por sus apellidos ma-

ternos, solo para aquellas personas que tengan el mismo apellido paterno.

Si la opción SI esta seleccionado, solo se ordenaran los registros, queda fija

la fila de encabezados.

Si la opción NO esta seleccionado, se ordenaran todos los datos de la lista,

incluyendo el encabezado (no es muy común).

4. Luego, observe el resultado del ordenamiento:


1.- Realiza una pequeña encuesta en tu clase para completar la siguiente tabla:

A continuación realiza las siguientes tareas:


Ordena la lista de alumnos de mayor a menor estatura.

Debajo de la columna altura vas a calcular la mínima altura, la máxima altu-

ra y el valor medio de la altura.(utiliza las funciones MIN, MAX y PROME-

DIO):

Cambia cualquiera de los datos anteriores y observa cómo cambian los re-

sultados de los cálculos.

Guarda el libro con el nombre ENCUESTA.

Para rellenar una celda rápidamente con el contenido de la celda situada

encima o a la izquierda, puede presionar CTRL+J o CTRL+D.

2.- La empresa QUÍMICA S.A. ha llevado a cabo tres proyectos de investigación

en los cuales han trabajado 12 empleados.

Los empleados que participan en el Proyecto 1 cobran un sueldo de S/.

12.45 /hora, los del Proyecto 2, de S/. 10.39 /hora; y los del Proyecto 3, de

S/. 9.45 /hora.

Cada trabajador ha realizado gastos de diferente cuantía en la realización

del proyecto (o proyectos) en que participa, en dos conceptos diferentes:

material y desplazamientos.

Los datos concre-

tos aparecen en la

tabla siguiente:

Abre un nuevo libro en Excel y guárdalo como Proyectos.


En la Hoja 1, en el rango A2:G14, introduce la tabla de arriba. En la Hoja 2,

rango A1:B4, introduce la siguiente tabla:

En la celda D3 introduce la función necesaria (función BUSCARV) para que

aparezca automáticamente el sueldo por hora de cada empleado al teclear

el proyecto al que ha sido asignado.

En la celda E3 introduce la fórmula necesaria para calcular el sueldo total a

percibir por cada empleado.

Una vez introducidos los datos:

A.- Ordenar la lista alfabéticamente, atendiendo a los apellidos y nombres de los

empleados.

S e utiliza para buscar un valor especifico como resultado de una formula, modifi-

cando el contenido de una celda. Excel buscara que valor debería tomar esa celda

para conseguir el resultado esperado. Esta celda se le denomina Valor indepen-

diente y a la celda que contiene la formula se le denomina Dependiente.

Para obtener este resultado, se utiliza la opción Datos. En el grupo Herramientas

de Datos, hacemos clic en el comando Análisis Y si.

Dentro de este menú, elegimos Buscar objetivo…


Nos muestra los siguientes campos:

Definir la celda: indica la celda que contiene la

formula. Al haber situado el curso en ella aparece

por defecto.

Con el valor: es el valor que tomara la celda ante-

rior, o sea, el valor que se desea obtener.

Para Cambiar la celda: celda que se utiliza en la

formula.

Ejemplo:

Se desea saber cual es el valor

de un televisor, cuyo precio de

venta al publico es de S/.

5000.00

Después de haber creado la tabla anterior, realizamos lo siguiente:

1.- En la celda C8, escribimos: + C4*C6

2.- En la celda C10, escribimos: +C4+C8

3.- La celda C4, C8 y C10, deben tener el formato Número.

4.- Las celdas C4, C8 y C10 deben tener el formato de dos posiciones decimales.

5.- Ubicar el cursor en la celda C10.

6.- En la opción Datos, seleccionar Análisis Y si.

7.- Aparece la siguiente ventana:

8.- Introducimos los datos que se indican.

9.- Hacer clic en el botón Aceptar.

10.- Nos muestra los resultados esperados:


Nelson Lévano, decide solicitar un préstamo a la caja municipal “El Dolor”. Presen-

ta una capacidad de pago de S/. 1500.00 para pagar en 60 meses, a una tasa de

interés de 5% mensual y solicita a la caja municipal, cual puede ser el monto de

préstamo que debe solicitar.

Para encontrar la solución creamos un modelo sencillo en Excel:

1. En la celda C6, editar: +ABS(Pago(C4/12;C5;C7))

a) La celda debe tener formato Número.

b) El formato de moneda debe ser: S/. Español (Perú).


2. En la celda C8, editar: +C6*C5

a) La celda debe tener formato Número.

b) Ajustar a dos decimales.

3. En la celda C9, editar: +C8 – C7

a) Formato Número.

b) Ajustar a cero decimales.

4. Ubicar el cursor en la celda C7 y editar los datos que se indican, en la ventana

Buscar Objetivo:


5. El resultado es el siguien-te:

Conclusión:

Nelson Lévano podrá acceder a

un préstamo de S/. 79 486.06

(setenta y nueve mil cuatrocien-

tos ochenta y seis y 06 nuevos

soles), a una tasa de interés de

5%, con 60 meses para pagar.

Pagando un total de interés de

S/ 10 514.00, siendo el monto total a pagar de S/. 90 000.00 (noventa mil y 00

nuevos soles).

Veamos la utilidad de los grupos Estilos y Celdas. Para obtener mayores resulta-

dos, estos dos grupos, se pueden usar en forma complementaria.

En el grupo estilos encontramos lo siguiente:

Formato Condicional.- nos permite resaltar celdas o rangos

de celdas interesantes, desatacar valores y ver datos, em-

pleando barra des estado, escala de colores y un conjunto de

iconos. Un formato condicional, cambia el aspecto de un rango

de celdas en función de una condición o criterio.

Dar formato como tabla.- proporciona una variedad de esti-

los de tabla predeterminados o predefinidos, que se utilizan para dar formato


a una tabla en forma rápida. Sin embargo, se puede crear y aplicar un estilo

de tabla personalizado.

Estilos de Celda.- nos permite aplicar formatos a una celda con estilos pre-

definidos; además, crear estilos personalizados para las celdas.

En el grupo Celdas encontramos lo siguiente:

Insertar.- permite insertar celdas, filas, columnas y hojas a un

libro.

Eliminar.- permite eliminar celdas, celdas, filas, colum-

nas y hojas a un libro.

Formato.- permite modificar el tamaño de la celda, visibilidad, organizar ho-

jas o proteger y ocultar celdas.

Ejemplo.

Inicie un nuevo libro, ingrese los datos de la tabla en la hoja 1.


Una vez terminado, aplicaremos formato a la tabla.

Seleccionar la tabla.

Hacer clic en el iniciador de dialogo de Dar formato como tabla, en el grupo

Estilos.

Elegir Estilo de tabla claro 9.

Se muestra un cuadro de dialogo, indicando el rango de datos seleccionados

(=$A$3:$G$17).


Hacer clic en el botón Aceptar. La tabla muestra flechas desplegables, en

cada uno de los encabezados de columna,

permitiendo realizar algún tipo de filtro.

Se muestra la Opción Diseño.

Selecciona la flecha correspondiente al campo Marca,

seleccionar la casilla Bayer; luego, hacer clic en el bo-

tón Aceptar.

Se mostrara un pequeño embudo al lado derecho de

Marca, indicando que

este campo se ha filtra-

do. El resultado que se

muestra son los regis-

tros correspondientes a la marca Bayer.

Para deshabilitar el filtro, hacer un clic en el embudo del filtro; luego, hacer

clic en Borrar filtro de Marca y se muestran todos

los datos.


Si en nuestra tabla existen valores duplicados, Excel presenta la opción Eli-

minar registro duplicados. Hacer clic en Quitar duplicados, en la opción

Diseño, dentro del grupo Herramientas de Tabla.

Seleccionamos la casilla Código de la lista de columnas; luego, hacer clic en

el botón Aceptar.

Nos muestra un mensaje y daremos clic en

el botón Aceptar.

En el grupo Opciones de estilo de tabla, activamos la casilla Fila de Totales

y nos muestra una cantidad en la celda G17.

Hacer clic en la celda G17 y se muestra un botón, desplegamos para utilizar

las diversas opciones que brinda.

Si nuestra tabla se muestra desde la fila A1 y deseamos agregar un titulo, es

necesario insertar filas en la parte superior de la tabla. Podrá observar que la

opción Insertar del grupo Celdas, en la Opción Inicio, esta deshabilitada. En

este caso, es necesario convertir en rango la tabla.

Hacer clic en la tabla, elegir la opción Diseño.

Hacer clic en Convertir en rango, se muestra un cuadro de mensaje y hacer

clic en el botón Si.

Hacer clic en la celda A1; luego, hacer clic en Insertar filas de la

hoja. En este momento podemos insertar las filas necesarias para

el titulo.


Finalmente, podemos establecer los formatos de estilo que nos guste.

Visor de Imágenes en Excel

Nuestro objetivo final es seleccionar un dato, de una lista desplegable y se mues-

tre el gráfico o imagen asociado al dato. Los datos que en este paso listemos ali-

mentaran tanto la lista desplegable como el gráfico final. Así que previamente de-

ben analizar con cuidado la totalidad de datos a incluir.

Paso 1: Editando los datos a incluir.

En nuestro caso para facilitar la visualización y compren-

sión de los datos a través de un ejemplo intuitivo seleccio-

namos Artefacto; pero se puede aplicar a otros productos

o servicios que se desee. La hoja 1 le asignamos el nom-

bre Visor.


Paso 2: Insertar en el archivo Excel las imágenes:

Luego de crear la lista de datos a incluir, en

la hoja 2 inserta las imágenes asociadas a

estos en el archivo. Puedes emplear cual-

quier tipo de formato gráfico (PNG, JPG,

GIF, BMP entre otros) aunque recomenda-

mos el uso de imágenes JPG, formato que

ofrece una buena calidad gráfica sin ocupar

mucho espacio en disco.

La hoja 2 le asignamos el nombre gráficos.

Paso 3: Crear una tabla adicional con valores referenciales.

En esencia nuestro visor de datos es una gráfica de dispersión, que contiene

tantas series como imágenes queramos incorporar. Con los ejes haremos “visi-

bles” o “no visibles” las imágenes de acuerdo a nuestro criterio.

En la misma hoja, creamos una tabla referencial con los valores a asignar a un de-

terminado punto de datos, dependiendo si quere-

mos que esta sea visible o no. En nuestro caso

asignamos el valor 5 a los puntos de datos que

haremos visibles y -10 a los que declararemos

como no visibles. Esta tabla solo será de carác-

ter referencial para recordar como configura-

mos la visibilidad de los datos.

Paso 4: Crear una lista auto desplegable con los ítems a considerar en nues-tro visor de datos.


Para evitar la entrada de valores no válidos en la celda que controlará que imagen

se va a visualizar en la gráfica crearemos una lista desplegable a partir de una va-

lidación de datos.


a) Hacer clic en la celda F2.

b) Desplegar la opción Datos.

c) En el grupo Herramientas de datos, elegir Validación de datos.

d) En Configuración, ubicarse en Criterios de

evaluación y elegir Lista.

e) En Origen de datos escribir: =$A$2:$A$10

f) Hacer clic en Aceptar.

Paso 5: Emplear la fórmula lógica SI para crear data de coordenadas para

nuestros puntos “Visibles” y “No Visibles”.

Completaremos ahora la lista de datos a incluir creada en el Paso 1, incluyendo

una lista con lo que podríamos llamar las “coordenadas de visibilidad” para cada

dato; es decir, lo que hará “Visible” o “No Visible” ante el usuario una imagen de-

terminada de acuerdo a nuestra selección en la lista despegable.

La propuesta lógica en este caso es:

“Si un determinado campo “Artefacto” coincide con el valor del campo “Valor Visi-

ble”; entonces, muestra el valor de un elemento “visible” (5), caso contrario mues-

tra el valor de un elemento “No visible” (-10)”

La sintaxis es: =SI([propuesta_lógica];[valor_si_verdadero ]; [valor_si_falso ])


Esto quiere decir, Si el dato en la celda A2 es igual al dato en la celda F2, mostrar

imagen (5); caso contrario, no visible (-10).

La función en nuestro archivo será: =SI($A2=$F$2;5;-10)

Tal como lo muestra la imagen;

esta fórmula la extendemos para

todos los datos en las dos colum-

nas, que llamaremos coordena-

das X y coordenadas Y.

El resultado nos permite

ver el cambio entre el va-

lor de la lista desplegable

y las coordenadas para

cada dato, tal como se

muestra en la imagen.

Paso 6: Creando la Gráfica de Dispersión.

Procedemos a crear la gráfica de dispersión y a definir las series implícitas, con

una particularidad:

Crearemos serie por serie con el objeto de poder editar los puntos de datos y defi-

nir la imagen que tendrá asociada.


Para hacerlo marcamos una celda que no tenga ningún dato asocia-

do: elegimos la celda F5 y nos dirigimos a la opción Insertar, luego,

seleccionamos la opción Dispersión y finalmente seleccionamos el

primer gráfico de las opciones, correspondiente a Dispersión sólo

con marcadores.

Ubicamos el área del grafico en las celdas correspondientes para su visibilidad.

Esto permitirá que al seleccionar

el dato en la celda desplegable

nos muestre la imagen corres-

pondiente.

Agregaremos la primera serie haciendo clic sobre el comando Seleccionar Datos,

de la opción Diseño.

En el cuadro de dialogo Seleccionar origen

de datos, hacer clic en Agregar para definir

Nombre, Valor X y Valor Y de la serie.

En nuestro caso decidimos para mayor

estética borrar el título y la leyenda del

mismo.

Nombre de la serie: =visor!$A$2


Valores X de la serie: =visor!$B$2

Valores Y de la serie: =visor!$C$2

Hacer clic en Aceptar.

En este caso se ha creado la serie para el primer artefacto: Televisor.

Para la radio seria:




Hacer clic en Aceptar.

Observe que hay cambios en el valor de las celdas. Debe tener en cuenta estos

cambios para las otras imágenes.

En la imagen se ve como se ha ejecutado el proceso completo, ingresando todos

los datos y las coordenadas correspondiente

para cada uno de ellos, mediante el botón Agre-

gar. Si por algún motivo, nos equivocamos al in-

gresar un dato, podemos Quitar o en todo caso

subir o bajar de nivel.

Una vez ejecutado este paso solo nos queda asociar una imagen a la serie para

culminar la definición visual de la primera serie.

Para lograrlo simplemente seleccionaremos la

imagen a asociar, la copiamos (Ctrl + C).

En nuestra gráfica de dispersión

seleccionamos(hacer clic) el punto de datos y

pegamos la imagen en este (Ctrl + V).


Este procedimiento debemos repetirlo tantas veces como series tengamos hasta

incorporar la totalidad de las mismas.

Puede resultar quizás la parte más te-

diosa del trabajo, si la lista de series a

incluir es muy larga; pero vale la pena

el esfuerzo.

En nuestro caso quedó como en la si-

guiente imagen.

Solo nos falta ajustar unos detalles adicionales y obtendremos nuestro visor de

imágenes en pleno funcionamiento.

Paso 7: Ajustar las escalas de la gráfica para maximizar la visualización de los resultados.

Una vez agregadas todas las series, solo nos resta realizar algunos ajustes a los

ejes, escalas y líneas de división del gráfico para maximizar la correcta visualiza-

ción de las imágenes; en nuestro caso emplearemos una escala predefinida para

el eje X de 0 a 10, y una escala predefinida para el eje Y de 0 a 10, en ambos ca -

sos con una graduación de 1.

Para ajustar las escalas solo debes hacer clic

con el botón izquierdo sobre el eje a ajustar; lue-

go, hacer clic con el botón derecho sobre la mis-

ma para desplegar las opciones, seleccionar

“Dar formato a eje”, y en la pestaña “Opciones

del eje” seleccionar:

Mínima: Fija y 0

Máxima: Fija y 10

Unidad Mayor: Fija y 1.

Unidad Menor: Fija y 0.


En la pestaña “Color de Línea” elegimos “Sin Línea”.

Luego borramos los números de ambos ejes y las líneas de división.

Una vez completado este proceso ya tenemos nuestro “Visor de Imágenes” listo

para ser ubica-

do donde quera-

mos.

El resultado fi-

nal luce como

en esta imagen.

Si hacemos clic

en la primera imagen, en el cuadro de formulas aparece la siguiente función: =SE-

RIES(Visor!$A$2;Visor!$B$2;Visor!$C$2;1)

Como puede notar muestra los valores:




El valor 1 se refiere a la primera imagen.

1.- Crear una tabla con una relación de 5 alumnos. Mostrar la fotografía de cada

uno de ellos con sus respectivos datos (Dirección, Teléfono, D.N.I., Área de

trabajo).

2.- Una empresa de transporte de pasajeros interprovincial, por seguridad graba

cada uno de sus pasajeros. En una tabla “Control de pasajeros”, se edita los

datos de cada pasajero, apellidos y nombres, sexo, edad, documento de iden-

tidad, destino y pago. Para el control policial muestra la imagen de cada pasa-

jero y los datos correspondientes.


3.- Una cadena de restaurantes pone a disposición de sus clientes el servicio de

menú digitalizado. Cada cliente puede elegir el menú, revisando las imágenes

y los datos de pago correspondiente.

4.- La oficina de procesos electorales ha digitalizado el símbolo de cada partido

político, para facilitar al elector la decisión de su voto. Mostrar en una tabla los

partidos políticos y su símbolo.

Función SUMAPRODUCTO

En algún momento en nuestras actividades nos hemos encontrado en la situación

de tener varias agrupaciones de datos en las cuales por ejemplo tenemos que ob-

tener el resultado de las ventas de acuerdo a su precio, lo que normalmente hace-

mos es utilizar la función matemática SUMAR, multiplicamos y luego sumamos

los subtotales para obtener el resultado requerido, entonces una muy buena op-

ción para este caso es emplear la función SUMAPRODUCTO ya que a través de

ella se ahorra tiempo de calculo

que hace la tarea mucho más

sencilla y practica. Veamos a

continuación una breve pero inte-

resante descripción de esta fun-

ción.

La función SUMAPRODUCTO

multiplica los componentes co-

rrespondientes de las matrices

suministradas y devuelve la

suma de esos productos.


La función SUMAPRODUCTO la podemos encontrar dentro del grupo de las fun-

ciones Matemáticas y Trigonométricas.

Veamos a continuación la sintaxis de esta función:

+SUMAPRODUCTO(matriz1;matriz2;matriz3;…)

Matriz1, matriz2, matriz3,… son de 2 a 255 matrices cuyos componentes desea multiplicar y después de haber sumado.


Los argumentos matriciales deben tener las mismas dimensiones. De lo

contrario SUMAPRODUCTO devuelve el valor de error #¡VALOR!.

La función SUMAPRODUCTO considera las entradas matriciales no numé-

ricas como 0.

Veamos el siguiente ejemplo:

Se desea saber la cantidad total de ingresos de acuerdo al precio del producto du-

rante un mes; según los datos de la siguiente tabla:

D6:E9: Representa a la primera matriz.

F6:G9: Representa a la segunda matriz.


+SUMAPRODUCTO(D6:E9;F6:G9)

+SUMAPRODUCTO(D6:E6;F6:G6)

La función SUMAPRODUCTO puede ser de gran utilidad al momento de presen-

tarse casos en donde se amerite su uso; sin embargo, el buen uso de la misma

dependerá de la destreza que tienen adquirida lo que hará que se lleve mucho

menos tiempo para el calculo.

La función SUMAPRODUCTO es una excelente opción para obtener el promedio

ponderado de una operación en conjunto con la función SUMA, no obstante las

opciones para esta función son muy variadas lo cual la hace una opción muy

versátil para nuestras tareas, no dudes en poner en practica tus conocimientos.

1.- Una empresa de transportes presenta la siguiente planilla de venta de pasajes

durante seis días.

Obtener:


a) Ingreso total en lo seis días.

b) Ingreso por cada destino.

2.- Una institución educativa presenta la siguiente planilla de ingresos mensuales

por pagos de mensualidad por enseñanza.

Obtener:

a) El total de ingresos en los seis meses.

b) Total de ingresos por especialidad durante el periodo.

c) Total de ingresos bimestrales

3.- La administración de un camal de aves, presenta la siguiente planilla de ingre-

sos por cada concesionario.

Obtener:

a) Total de ingreso de la semana.

b) Total de ingreso por concesionario.


4.- Una empresa distribuidora de gas presenta la siguiente planilla de ventas.

Obtener:

a) Venta total de la semana.

b) Venta total por marca.

c) Venta total por día.

CONSOLIDAR DATOS DE VARIOS LIBROS EN EXCEL

El comando Consolidar lo encontramos en la menú de opciones dentro de la op-

ción Datos en el grupo Herramientas de datos tal como se ve en la siguiente

imagen:

Supongamos que tenemos una empresa que tiene sede en tres regiones distintos

(Piura, Huaraz y Arequipa) y la suma total de las ventas se calcula a partir de los

datos de tres archivos diferentes, donde cada uno contiene la totalidad de las ven-

tas por cada región.

Es en esta parte donde entra la utilidad del comando Consolidar; pues mediante

el mismo vamos a obtener los totales de la suma de las ventas de todas las regio-

nes en un solo libro.


Teniendo en cuenta que son 3 libros, cada uno llamado de la siguiente manera:

Ventas_Piura

Ventas_Huaraz

Ventas_Arequipa

Cada libro debe tener los datos que se consiga en la siguiente tabla:

Debemos crear un nuevo libro donde vamos a consolidar los totales de las ventas

realizadas en estas regiones, ese archivo lo vamos a nombrar Ventas_Totales.

Antes de realizar la consolidación de los valores debemos tener

abiertos tanto los libros que contienen las ventas realizadas en

las tres regiones como también el libro de consolidación.

Es preferible tener el mismo encabezado de los libros a consolidar, en el archivo

donde se va a consolidar la información (Ventas_Totales).

El primer paso que debemos realizar es posicionarnos en la celda a partir de don-

de queremos que se consoliden los datos en el libro Ventas_Totales para este


caso seleccionamos la celda B5, dado que, es donde inicia el rango que vamos a

tomar en cuenta en el resto de los libros.

En seguida vamos al menú de opciones y en la opción Datos, grupo Herramien-

tas de datos seleccionamos el comando Consolidar en donde se desplegara el

siguiente cuadro de dialogo:

Por defecto en Función dentro de la lista

desplegable aparece la función Suma, para

este caso se deja tal cual, ya que se está

buscando una sumatoria total de ventas

realizadas en tres archivos; no obstante,

adicionalmente disponemos de 10 funcio-

nes mas con las que podemos trabajar.

En el Campo Referencia es a partir de

donde vamos a seleccionar el rango celdas de los otros libros que deseamos con-

solidar.

En Todas las referencias vamos a visualizar la totalidad de las referencias que

tenga el libro.

El Botón Agregar permite agregar una referencia y el botón Eliminar permite su-

primir una referencia seleccionada.

En Usar rótulos: Aparecen dos casillas de verificación en este cuadro de diálogo,

indicando la inclusión de rótulos en la fila superior y/o en la columna izquierda,

por que se pueden dar los dos casos de manera simultánea. Al marcar estas casi-

llas estamos informando al programa de que no deseamos que los datos incluidos

en la fila superior y/o en la columna izquierda se tengan en consideración a la hora

de realizar las operaciones correspondientes.

En Crear vínculos con los datos de origen: Es importante activar esta casilla si

queremos que los datos originales queden vinculados con los datos consolidados,

de manera que si en las hojas que contienen los datos de origen se realiza algún


tipo de modificación, la hoja resumen actualizará los datos consolidados en la me-

dida que sea necesario. También esta opción crea en la hoja consolidada un es-

quema que permite identificar el origen de cada uno de los datos consolidados; es

decir, de dónde proviene ese dato de resumen en concreto.

Una vez revisados estos puntos procedemos a realizar la consolidación de los to-

tales, con el cuadro de dialogo abierto en el campo Referencia vamos a seleccio-

nar el rango de las celdas que deseamos consolidar del primer libro (Ventas_Piu-

ra) a partir de la celda B5 hasta la celda G8 tal como se ve en la imagen:

Luego seleccionamos el botón Agregar dentro del cuadro de dialogo, este paso lo

vamos a repetir exactamente; pero haciendo referencia a los dos libros restantes

(Ventas_Huaraz) y (Ventas_Arequipa) quedando el campo Todas las referencias

tal como lo mostrado en la siguiente imagen:

Las casillas Fila Superior, Columna izquierda y Crear vínculos con los datos

de origen no la vamos a seleccionar para la consolidación de estos valores.


Por último hacemos clic en el botón Aceptar y los resultados se consolidarán de

manera exitosa en el libro Ventas_Totales.

El comando Consolidar es de suma utilidad cuando tenemos un gran número de

libros y queremos consolidarlos todos en uno solo, el mismo nos ofrece la posibili-

dad de hacer esta operación.

1.- Trabajar con la tabla control de pasajeros y presentar los consolidados de in-

gresos.

2.- Trabajar con la tabla Agroquímicos y presentar el consolidado por marca.

3.- En la tabla Ingresos mensuales por pensión de enseñanza, de la clase SUMA-

PRODUCTO, presentar el consolidado de ingresos.

4.- En la tabla Venta de pasajes, de la clase SUMAPRODUCTO, presentar el con-

solidado de pasajeros transportados durante la semana.

ESCENARIOS

Es un conjunto de celdas cambiantes que puede grabarse para estudiar diferentes

resultados. En el área financiera, es útil crear escenarios para evaluar varios su-

puestos, por ejemplo en la evaluación de créditos con varios tipos

de interés y varios posibles periodos.

Para utilizar Escenarios, se recurre a la opción Datos y en el grupo

Herramientas de datos, desplegamos Análisis Y si, para hacer

clic en Administrador de escenarios.

En el cuadro Administrador de escenarios, encontramos lo si-

guiente:

Escenarios: espacio donde definiremos los escenarios o su-

puestos que se deben analizar.


Agregar: Nos permite agregar escenarios o supuestos a evaluar. Al hacer clic en

este botón, nos muestra un cuadro Agregar escenario, con los siguientes ele-

mentos:

Nombre del escenario: aquí escribiremos el nombre del escenario que deseamos

evaluar.

Celdas cambiantes: especificamos las referencias

de las celdas que queremos cambiar.

Para preservar los valores originales de las

celdas cambiantes, cree un escenario que

utilice los valores originales de las celdas an-

tes de crear escenarios que cambien los va-

lores.

En la sección Comentarios, puede escribir algo de referencia para e comentario,

como por ejemplo el autor y fecha de creación del escenario.

El la sección Protección, podemos elegir las opciones que deseamos para nues-

tro escenario.

Al hacer clic en Aceptar, nos muestra un cuadro, Valores del escenario, con los

siguientes elementos:

En la sección Introduzca un valor para cada celda cam-

biante, debemos ingresar el valor que corresponda.

Si deseamos introducir otros valores en otras celdas, ha-

cer clic en Agregar, caso contrario en Aceptar, creando el escenario.

Si deseamos crear otros escenarios repetimos los pasos anteriores.

Al terminar de crear escenarios, hacer clic en Aceptar y Cerrar, en el cuadro de

dialogo Administrador de escenarios.

MOSTRAR UN ESCENARIO


Para mostrar, hay que tener en cuanta que se cambian los valores de las celdas

que se guardan como parte de este escenario. Seguir los siguientes pasos:

1. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análi-

sis Y si y, después, en Administrador de escenarios.

2. Haga clic en el nombre del escenario que desee mostrar.

3. Haga clic en Mostrar.

CREAR UN INFORME RESUMEN DE ESCENARIO

Para crear un informe resumen de un escenario, realizar los siguientes pasos:

1. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análi-

sis Y si y, después, en Administrador de escenarios.

2. Haga clic en Resumen.

3. Haga clic en Resumen del escenario o en Informe de tabla dinámica de

escenario.

4. En el cuadro Celdas de resultado, escriba las referencias de las celdas

que hacen referencia a las celdas cuyos valores cambian los escenarios.

Separe las referencias múltiples mediante comas.

A.- Un cliente desea evaluar un crédito de S/. 2000.00 para pagar en 4 años, a

una tasa de interés de 5%. Su cuota de pago mensual seria de la siguiente mane-

ra:

En la celda B8 editamos: =+ABS(PAGO(B5/12;B6*12;B4))

Pero si deseamos evaluar varios supuestos:

Interés al 5% y 5 años para pagar.

Interés al 5% y 6 años para pagar.

Interés al 4,5% y 3 años para pagar.

Otro monto de capital al 4% y 5 años de pago.


En este caso es necesario crear escenarios. Realizando las siguientes acciones:

1. Acceder a la opción Datos.

2. En el grupo Herramientas de datos, elegir Análisis Y si.

3. En el menú que se despliega, elegir Administrador de escenarios.

4. Hacer clic en el botón Agregar.

5. En el cuadro de dialogo que aparece, escribir un nombre para el escenario.

6. En Celdas cambiantes, escribir B4:B6

7. Hacer clic en Aceptar

8. Aparecen tres campos o celdas que permitirán los cambios.

9. En la primera celda escribir 2000, en la segunda celda 0,05 y en la tercera

celda escribir 5.

10.Hacer clic en Aceptar.

11.Aparece el cuadro de dialogo Administrador de escenarios, mostrando el

primer escenario.

12.Hacer clic en el botón Agregar, para introducir los tres restantes escena-

rios.

13.Después de haber completado los escenarios, hacer clic en el botón Resu-

men

14.Aparece el cuadro de dialogo Resumen.

15.Como celdas de resultado seleccionar B8.

16.Hacer clic en el botón Aceptar.

17.Se muestra el siguiente resultado:


B.- Crear la siguiente tabla:

En la celda B8, editar: =+ABS(PAGO(B5/12;B6*12;B4))

En la celda A10, se ha colocado el valor con el que se desea jugar: +B8


1. Seleccionar A10:F19

2. Acceder a la opción Datos y desplegar Análisis Y si.

3. Del menú, elegir Tabla de datos.

4. En el cuadro de dialogo Tabla de datos, editar:

a) En celda de entrada (fila): B6

b) Como celda de entrada (columna): B5

5. Hacer clic en el botón Aceptar.

Realizar el análisis correspondiente.



Documents

108555009-Excell2007-Vip Genial Fantastico-15 Ejercicios Planteados Para Clase-gran Maquetacion-nivel Medio-para Clase