Excel 2013 Empresarial

EXCEL AVANZADO

TABLAS DE DATOS Las tablas de datos forman parte de una serie de comandos a veces denominados herramientas de análisis Y si. Es un rango de celdas que muestra cómo afecta el cambio de algunos valores de las fórmulas a los resultados de las mismas. Constituyen un método abreviado para calcular varias versiones en una sola operación, as como una manera de ver y comparar los resultados de todas las variaciones distintas en la ho!a de cálculo. E"isten dos tipos de tablas de datos# tablas de una variable y tablas de dos variables.

TABLAS DE DATOS DE UNA VARIABLE  



Los valores de entrada apare$can en una columna %orientación por columnas& o en una fila %orientación por filas&. Las fórmulas que se utilicen en la tabla de una variable deberán hacer referencia a una celda de entrada %celda en la que se sustituye cada valor de entrada de una tabla de datos&. Escriba la lista de valores que desea sustituir en la celda de entrada deba!o de una columna o en una fila.

Ejercicio: 'e desea saber qué pasara con la (tilidad si el costo de producción varia a ').*++++, '). -+++ y '). ++++. 

/latear el /roblema y calcular la (tilidad

(tilidad# 3

6ato a 7eempla$arse



0ngresar los valores cambiantes SESIÓN 3

MOSM. Ing. Edilberto Chávez Fernández

1

EXCEL AVANZADO

Cambios en el costo de /roducción



'eleccionar el rango de datos a anali$ar

0ncluir celda que contiene el valor de las utilidades. 

'eleccionar la opción 4abla de 6atos de la 8icha 6atos#



'i la tabla de datos está orientada por columnas, escriba la referencia de la celda de entrada en el cuadro Celda de entrada %columna&. 'i la tabla de datos está orientada por filas, escriba la referencia de celda de la celda de entrada en el cuadro Celda de entrada %fila&. En nuestro e!emplo, los datos están ordenados en Columnas, pues en una columna se muestran los Costos de /roducción cambiados y en otra columna se desea mostrar las utilidades respectivas. /or lo tanto para nuestro caso debemos ingresar la Celda de Entrada %el costo de producción inicial& en Columna.

 

SESIÓN 3


2

EXCEL AVANZADO



La tabla llena con los nuevos datos de utilidades sera#

TABLAS DE DATOS DE DOS VARIABLES   

Las tablas de datos de dos variables solamente utili$an una con dos listas de valores de entrada. La fórmula deberá hacer referencia a dos celdas de entrada la que se sustituye cada valor de entrada de una tabla de datos. 0nserte una lista de la segunda variable a lado de la fórmula.

Ejercicio: E!emplo# 9l e!emplo anterior agregarle la variable de deudas con los cambios de :+,+++ y ;,+++. 'eleccione el rango de celdas que contenga la fórmula y los valores de fila y de columna.

SESIÓN 3


3

EXCEL AVANZADO



Cambios en las 6eudas

0ngresar los valores cambiantes

Cambios en el costo de /roducción 

'eleccionar el rango de datos a anali$ar

0ncluir celda que contiene el valor de las utilidades.



'eleccionar la opción 4abla de 6atos de la 8icha 6atos#

SESIÓN 3


4

EXCEL AVANZADO



0ngresar las referencias de las Celdas.



La tabla llena con los nuevos datos de utilidades sera#

SESIÓN 3


5

EXCEL AVANZADO

BUSCAR OBJETIVO /ermite determinar el valor de una celda o rango de celdas, de tal manera que se llegue a cumplir un ob!etivo.

Ejemplo:



<=ué valor debe tener las deudas y los costos de producción, para que mi utilidad sea de '). +,+++> /lantear los 6atos

1alor a 9!ustar

6atos que se



'eleccionar la 8unción ?uscar @b!etivo de la 8icha 6atos#

1alor a 9!ustar



0ngresar el valor @b!etivo.

Celda @b!etivo

1alor @b!etivo Celdas Cambiantes

SESIÓN 3


6

EXCEL AVANZADO



El resultado sera#

2uevo 1alor para Costo 2uevo 1alor para 6eudas

SESIÓN 3


7

EXCEL AVANZADO

SOLVER Es una opción que permite en un modelo construido en una hoja de Excel, encontrar una meta óptima considerando ciertas restricciones sobre celdas preseleccionadas. Si se ha concluido la construcción de una hoja de cálculo estructurada adecuadamente con celdas de ingreso de información, en el cual se ha planteado un problema con una determinada celda que arroja una información de salida; este dato puede maximizarse, minimizarse o reemplazarse con un valor determinado, cambiando los valores de una celda o rango de celdas de referencias para alcanzar un resultado deseado. demás, es posible agregar restricciones !, ", # o entero, o los valores de determinadas celdas de referencia que influirán en el resultado esperado en la celda de salida. $e esta manera, se pueden resolver problemas de optimización o de programación lineal. %ara disponer de esta herramienta en Excel, debe hacer lo siguiente& '.

(lic en el botón de Office

).

(lic en el botón Opciones de Excel

*.

(lic en la opción Complementos

+.

(lic en el botón Ir…

.

(lic en la casilla Solver

-.

(lic en Aceptar

SESIÓN 3


8

EXCEL AVANZADO

$espus que esta herramienta ha sido instalada, Excel lo agregará en la ficha Datos, al final de la cinta de opciones. Ejemplo !" /tilizando la herramienta Solver, desarrolle la siguiente aplicación&

'.

Seleccione la ficha Datos, grupo An#lisis, opción Solver

).

En el cuadro (elda objetivo,

seleccione la celda 0)) 1esta celda debe contener el nuevo punto de equilibrio2 *.

(lic en el botón Valores de,

e ingrese el valor de 3. +.

En el cuadro Cambiando las

celdas, debemos indicar qu celdas van cambiar de valor para obtener el resultado deseado, en este caso son las celdas 0'4 1precio unitario2 5 E) 1sueldos2.

SESIÓN 3


9

EXCEL AVANZADO

%ero estas celdas están sujetas a ciertas restricciones. .

%ara agregar las restricciones, haga clic en el botón A$re$ar

-.

En

restricción,

la

ventana

ingrese

la

gregar primera

restricción& el precio unitario 1celda 0'42 debe ser menor o igual a *. 6uego haga clic en el botón A$re$ar para ingresar la segunda restricción& 6os sueldos 1celda E)2 deben ser ma5or o igual a ''77. hora, haga clic en el botón Aceptar.

SESIÓN 3


10

EXCEL AVANZADO

3.

8inalmente, haga clic en el botón Resolver.

El resultado será el siguiente&

SESIÓN 3


11

EXCEL AVANZADO

CONTROLES DE FORMULARIOS (na de las opciones más interesantes que tiene E"cel es la de utili$ar los controles e Form!l"rios . Estos controles se usan para crear verdaderos programas que pueden ser mucha utilidad. /ara poder tener acceso a estos controles de 8ormularios, hacer lo siguiente# :. . *. A.

-. . B.

SESIÓN 3

Clic en el #ot$n e O%%ice que se encuentra en la esquina superior i$quierda. Clic en el botón Opciones e E&cel Clic en la casilla Mostr"r %ic'" (ro)r"m"or en l" cint" e opciones

Clic en el botón Acept"r Clic en la ficha (ro)r"m"or Clic en la opción Insert"r


12

EXCEL AVANZADO

Controles e Form!l"rios Los botones, las casillas de verificación y otros controles de 8ormularios disponibles proporcionan opciones cuando se utili$a un formulario. La mayora de los controles tienen propiedades que pueden modificarse sin embargo, solamente pueden cambiarse las propiedades de formato de los botones de comando y los controles de rótulo.

Tipo e control R$t!lo C!"ro )r!po

Descripci$n 4e"to agregado a una ho!a de cálculo o formulario para proporcionar información acerca de un control, de una ho!a de cálculo o de un formulario. (n borde y un rótulo que agrupa los controles relacionados, como los botones de opción o las casillas de verificación. (n botón que e!ecuta una macro cuando se hace clic en él.

e

Bot$n C"sill" e *eri%ic"ci$n Bot$n e opci$n

C!"ro e list" C!"ro com#in"o SESIÓN 3

(n botón que activa o desactiva una opción. /uede seleccionarse más de una casilla de verificación a la ve$ en una ho!a o en un grupo. (n botón que selecciona un grupo de opciones contenido en un cuadro de grupo. 'olamente puede seleccionarse un botón de opción en un grupo. Los botones de opción se utili$an cuando se permite una de varias posibilidades. (n cuadro que contiene una lista de elementos.

(n cuadro con una lista desplegable. El elemento que se seleccione en el cuadro de lista aparecerá en el cuadro de te"to. MOSM. Ing. Edilberto Chávez Fernández

13

EXCEL AVANZADO

B"rr" e (n control que sirve para despla$arse a través de un espl"+"miento rango de valores cuando se hace clic en las flechas

Control e n,mero

de despla$amiento o cuando se arrastra el cuadro de despla$amiento. /uede despla$arse por una página de valores haciendo clic entre el cuadro de despla$amiento y una flecha de despla$amiento. (n botón que tiene una flecha arriba y otra aba!o que puede ad!untarse a una celda. /ara aumentar un valor, haga clic en la flecha arriba para disminuir un valor, haga clic en la flecha aba!o.

A)re)"r controles " !n" 'oj" con l" #"rr" e 'err"mient"s Form!l"rios :. 9bra la ho!a de cálculo a la que desee agregar controles. . En el cuadro de Controles e Form!l"rios, haga clic en el control que desee agregar. *. En la ho!a de cálculo, arrastre el control hasta que tenga el tamaDo que desee.

Not" 'i se agrega un control a una ho!a de cálculo, nicamente estará disponible cuando la ho!a de cálculo esté abierta.

Propiedades de control de las casillas de verificación

(ropie"

Descripci$n

V"lor

6etermina el estado de la casilla de verificación es decir, si está activada %Acti*"o &, desactivada %Sin "cti*"r& o ninguna de las dos %Mi&to&.

Vinc!l"r 7efle!a el estado de la casilla de verificación. 'i la casilla de con l" cel" verificación está activada, en el cuadro Vinc!l"r con l" cel" aparecerá el valor lógico 1E7696E7@. 'i está desactivada, en la celda aparecerá el valor lógico 89L'@. 'i el estado es mi"to, en la celda aparecerá el valor lógico F2)9. 'i la celda vinculada está vaca, Gicrosoft E"cel interpreta el estado de la celda como 89L'@.

Propiedades de control de los botones de opción

(ropie"

Descripci$n

V"lor

6etermina el estado del botón de opción es decir, si está

SESIÓN 3


14

EXCEL AVANZADO

activado %Veri%ic"o& o desactivado %Sin *eri%ic"r&.

Vinc!l"r 7efle!a el botón de opción activado. 6ado que un botón de con l" cel" opción se utili$a para elegir una opción entre un grupo de opciones, coloque los botones de opción que estén relacionados en un cuadro de grupo y, a continuación, vincule cada botón de opción con la misma celda de la ho!a de cálculo mediante el cuadro Vinc!l"r " l" cel". (na ve$ seleccionado un botón de opción, la celda incluirá el nmero del botón de opción en el grupo. Este nmero puede utili$arse en una fórmula para devolver un resultado basándose en la opción seleccionada.

Propiedades de control de los cuadros de lista

(ropie"

Descripci$n

R"n)o e entr""

Hace referencia a una lista de valores en una ho!a de cálculo. Este rango facilita los valores en el cuadro de lista.

Vinc!l"r cel"

con

l"

Tipo e selecci$n

Una celda que devuelve un val! que !e"!e#en$a el ele%en$ #elecc&nad en la l&#$a' E#$e n(%e! "uede u$&l&)a!#e en una *+!%ula "a!a devlve! un !e#ul$ad ,a#-nd#e en el ele%en$ #elecc&nad en la l&#$a'

Especifica el modo en que pueden seleccionarse los elementos de la lista. 'i se define el tipo de selección como M,ltiple o E&teni", la celda especificada en el cuadro Vinc!l"r con l" cel" no se tendrá en cuenta.

Propiedades de control de los cuadros combinados

(ropie"

Descripci$n

R"n)o e entr""

Hace referencia a una lista de valores en una ho!a de cálculo. Este rango facilita los valores en la lista desplegable.

Vinc!l"r cel"

con

L-ne"s e *ertic"les SESIÓN 3

l" (na celda que devuelve un valor que representa el elemento seleccionado en la lista. Este nmero puede utili$arse en una fórmula para devolver un resultado basándose en el elemento seleccionado en la lista.

!ni$n Especifican el nmero de lneas que aparecerá en la lista desplegable. MOSM. Ing. Edilberto Chávez Fernández

15

EXCEL AVANZADO

Propiedades de control de las barras de desplazamiento

(ropie"

Descripci$n

V"lor "ct!"l

7epresenta la posición relativa de éste dentro de la barra de despla$amiento.

V"lor m-nimo

7epresenta la posición más pró"ima a la parte superior de una barra de despla$amiento vertical o al e"tremo i$quierdo de una barra de despla$amiento hori$ontal.

V"lor m.&imo

7epresenta la posición más le!ana a la parte superior de una barra de despla$amiento vertical o al e"tremo derecho de una barra de despla$amiento hori$ontal.

Incremento

El tamaDo del espacio que se despla$ará el cuadro de despla$amiento cuando se haga clic en la flecha situada en cualquier e"tremo de la barra de despla$amiento.

C"m#io e p.)in"

El tamaDo del espacio que se despla$ará el cuadro de despla$amiento cuando se haga clic entre el cuadro de despla$amiento y una de las flechas de despla$amiento.

Vinc!l"r con l" cel"

6evuelve el valor actual del cuadro de despla$amiento. Este nmero puede utili$arse en una fórmula para devolver un resultado basándose en la posición del cuadro de despla$amiento.

Propiedades de control de los controles numéricos Las propiedades de control de un botón de nmero son las mismas que las de una barra de despla$amiento, e"cepto que los botones de nmero no tienen la propiedad de C"m#io e p.)in".

FUNCI/N INDICE Es una función de bsqueda que devuelve un valor o la referencia a un valor de una tabla o rango. 'inta"is#

INDICE0R"n)o1 N,m2%il"1 N,m2col!mn"3

R"n)o N,m2%il" SESIÓN 3

rango de celdas 'elecciona en el rango, la fila desde la cual se devolverá un valor. MOSM. Ing. Edilberto Chávez Fernández

16

EXCEL AVANZADO

N,m2col!mn"

'elecciona en el rango, la columna desde la cual se devolverá un valor. 'i se omite, toma el valor de :.

EJEM(LO 4 La siguiente aplicación hace uso de uno de los controles más tiles que tiene la barra de herramientas de 8ormularios, este es el Cuadro Combinado.

(ASO 4 Escriba los siguientes datos

(ASO 5 0nserte un Cuadro Combinado

(ASO 6 'eleccione

el

botón

(ropie"es de la c&n$a de "c&ne# ./!u"

Cn$!le# 



También se puede hacer clic derecho sobre el control insertado y seleccionar Formato de control En la propiedad R"n)o e Entr"" seleccione el rango que contenga los datos que desea mostrar en el cuadro combinado, para este caso 9#9

SESIÓN 3


17

EXCEL AVANZADO





En la propiedad Vinc!l"r con l" cel" seleccione la celda E:. Esta celda almacenará la posición del elemento seleccionado en el cuadro combinado. Clic en el Acept"r

'alga del modo diseDo y pruebe el e!emplo. /odrá comprobar que en el Cuadro Combinado se encuentran los datos del rango especificado.

'eleccione un curso de la lista, por e!emplo 6iseDador Ieb. En la celda E: se mostrará el nmero ordinal de la lista, para este caso, el nmero *. 'i quisiéramos a partir de este nmero ordinal, determinar la 6uración %horas& e 0nversión correspondiente al curso seleccionado, tendramos que usar la función 0260CE. SESIÓN 3


18

EXCEL AVANZADO

Cuadro Combinado 7ecuerde que esto se aplica tanto a un Cuadro Combinado o a un Cuadro de Lista.

Cuadro de Lista

SESIÓN 3


19

EXCEL AVANZADO

PRÁCTICA DE REFORZAMIENTO En la Hoja 2 &n/!e#e l# #&/u&en$e# da$#

A#&/ne l# #&/u&en$e# n%,!e# de !an/#

Nombre de rango

Rango

LAPTOP

A25

ESCANER

A810

IMPRESORA

A1317

MONITOR

A2023

SESIÓN 3


20

EXCEL AVANZADO

A#&/ne l# #&/u&en$e# n%,!e# de !an/#

Nombre de rango

Rango

MEMORIA

D2E5

MOUSE

D8E10

PROYECTOR

D13E16

ANTIIRUS

D20E22

SESIÓN 3


21

EXCEL AVANZADO

En la Hoja ! d&#ee la #&/u&en$e &n$e!*a) aad&end cn$!le# de *!%ula!&# ca#&lla# de ve!&*&cac&+n  cuad!# c%,&nad#'

En la celda 5 &n/!e#e la *unc&+n HOY"#  a"l&que *!%a$ "e!#nal&)ad'

Con$%g&ra'%(n de 'on)ro*e+ CONTROL

PROPIEDAD

Ca#&lla de ve!&*&cac&+n 1

V&ncula! cn la celda 1















Cuad! c%,&nad 1

an/ de en$!ada LAO V&ncula! cn la celda :1

SESIÓN 3


22

EXCEL AVANZADO


an/ de en$!ada E;CANE V&ncula! cn la celda :2


an/ de en$!ada <=E;OA V&ncula! cn la celda :3


an/ de en$!ada =ON<O V&ncula! cn la celda :4


an/ de en$!ada =E=O

an/ de en$!ada =OU;E V&ncula! cn la celda :6


an/ de en$!ada O>ECO V&ncula! cn la celda :7


an/ de en$!ada AN

Ce*da

F(rm&*a

E10

?;<.1
E11

?;<.2
E12

?;<.3
E13

?;<.4
E14

?;<.5
E15

?;<.6
E16

?;<.7ECO:720

E17

?;<.8
E18

?;U=A.E10E17

El !e#ul$ad #e!- el #&/u&en$e

SESIÓN 3


23

EXCEL AVANZADO

SESIÓN 3


24

Excel 2013 Empresarial

Recommend Documents