CURSO EXCEL AVANZADO SENATI.pdf

PROGRAMA PROG RAMA NACI NACIONAL ONAL DE INFORMA INFORMATICA TICA

Excel 2007 Avanzado

EXPERTO EN OFIMATICA

T BLA D CONT NIDO FUNCI

NES DE T XTO ............................................................................................................... 1

FUN IONES DE TEXTO ................................................................................................................ 2

L RGO ................................................................................................................................ .. 2 M AYUSC ............................................................................................................................. .. 3 MINUSC............................................................................................................................... .. 3 NOMPROPIO ................... ................................................................................................... .. 3 IZQUIERDA ......................................................................................................................... .. 4 DERECHA ........................................................................................................................... .. 4 E TRAE .............................................................................................................................. .. 5 H LLAR ............................................................................................................................... .. 6 E CONTRA ................... ................................................................................................... .. 7 CONCATENAR FU CIONES .......................................................................................................... 8 F nción conc tenar ............................................................................................................. .. 8

FUNCI NES DE F CHA Y HORA.............. .............................................................................. 13 L A S RIE DE FECH AS DE EXCEL ................................................................................................. 14 FOR ATO A LAS CELDAS QUE CONTIENEN FECHAS ..................................................................... 15

A licar formato general ..................................................................................................... 15 F rmatos pre efinidos ....................................................................................................... 16 F rmatos per onalizados .................................................................................................. 17 A itmética de echas ......... ................................................................................................. 18 S mar días a una fecha .................................................................................................... 18 R star dos fe has............. ................................................................................................. 20 FUN IONES DE FECHA .............................................................................................................. 21

F CHA............................................................................................................................... 21 F CHANUMERO .............................................................................................................. 22 F CHA.MES ...................................................................................................................... 23 FIN.MES ............................................................................................................................ 23 DI A.LAB ............................................................................................................................. 24 DI AS.LAB........................................................................................................................... 25 DI ASEM ............................................................................................................................. 26 DI AS360 ............................................................................................................................ 27 N M.DE.SEM ANA ............................................................................................................ 28 H Y ................................................................................................................................... 29 A ORA .............................................................................................................................. 29 DI A ..................................................................................................................................... 30 MES ................................................................................................................................... 30 A O .............. ..................................................................................................................... 30 APLI ACIONES DE LAS FUNCIO ES DE FECH ............................................................................. 30

C lcular la e ad............... ................................................................................................. 30 C lcular el 1er día del me de una fec a ................ .......................................................... 31 FUNCI NES DE B ÚSQUEDA .................................................................................................. 37 FUN IÓN BUSCARV ............................................................................................................... 38

F nción BUS ARV como parte de un fórmula o f unción. .............................................. 42 B scar valores específico .................. .............................................................................. 43 P rmitir entre a de valores aproximad os................ os.......................................................................... 44 FUN IÓN BUSCARH ............................................................................................................... 47 FUN IÓN INDICE .................................................................................................................... 49 MEJ RAR LA PRE ENTACIÓN D LOS RESUL ADOS .................................................................... 50

Programa

acional de Informática

I

Microsoft Office Excel 2007 Ava zado

F F F B F

nción ESBL ANCO ......................................................................................................... 50 nción COINCIDIR .......................................................................................................... 51 nción ESN D ................................................................................................................ 51 squeda por referencia cruzada. .................................................................................... 51 nción ELEGIR ................................................................................................................ 52

V ALI ACIÓN DE D TOS EN CELD AS ............................................................................................ 53

V lidación por Listas ......................................................................................................... 54 Q itar una vali dación ......................................................................................................... 55 V lidación por intervalo de números, ................................................................................ 55 FILTR S AVANZA DOS, HER AMIENTAS DE DATO Y ESQUE AS ................................ 61 FILT OS AVANZADOS ................................................................................................................ 62

C iterios en u a columna................................................................................................... 63 C iterios en u a Fila ......... ................................................................................................. 65 C iterios que i ncluyan fór ulas ........... .............................................................................. 66 Obtener valor s únicos...................................................................................................... 68 USO DE LAS HERR AMIENTAS DE DATOS ...................................................................................... 69

T xto en colu nas ........... ................................................................................................. 69 Quitar valores duplicados .................................................................................................. 71 V lidación de datos .......... ................................................................................................. 72 C nsolidar.......................................................................................................................... 74 A álisis Y si .. ..................................................................................................................... 78 E cenarios .... ..................................................................................................................... 78 C ear un escenario ........... ................................................................................................. 78 B scar objetiv o ................. ................................................................................................. 82 T bla de datos .................. ................................................................................................. 82 TRA AJANDO CON ESQUEMAS................................................................................................... 86

A D C C U

rupar .............................................................................................................................. 87 sagrupar ........................................................................................................................ 89 ear Subtotales............... ................................................................................................. 90 ndiciones p ara crear su btotales ...... .............................................................................. 90 ar múltiple niveles de ubtotales. ................................................................................ 92

TABLAS Y GRÁFI OS DINÁMICOS........................................................................................ 95 TRA AJANDO CON TABLAS DIN MICAS ....................................................................................... 96

Insertar una t bla dinámica ................. .............................................................................. 97 Interactuar con la tabla .................................................................................................... 1 00 C mo funcion a la lista de campos de t abla dinámi a ................ ..................................... 1 00 Modificar el di seño de una tabla diná ica .............. ........................................................ 1 01 Organizar los campos....... ............................................................................................... 1 02 A mentar o disminuir regi tros en el o igen ............ ........................................................ 1 03 Ordenar............................................................................................................................ 1 03 A rupar y mo trar detalles .................. ............................................................................ 1 04 A alizar un re gistro espec ífico ............ ............................................................................ 1 07 C nfigurar el ampo de da tos. ............ ............................................................................ 1 08 F rmato de Número ........................................................................................................ 1 09 MODIFICAR EL DISEÑO DE LA T BLA DINÁMIC .................. ........................................................ 1 11

S btotales ..... ................................................................................................................... 1 11 Mostrar u oculltar totales g enerales ................................................................................. 1 12 E tablecer el omportami nto predeterminado par mostrar u cultar totales generales .........................................................................................................................................1 13

II

Program a Nacional de Informática

Diseño de info rme ........................................................................................................... 1 14 In ertar filas e n blanco ..... ............................................................................................... 1 15 E tilos de tabl dinámica ................................................................................................. 1 15 GRÁ ICOS DINÁMI OS ............................................................................................................ 127

Cr ear un infor e de gráfic dinámico a partir de un informe de tabla dinámica existente ......................................................................................................................................... 1 29 In eractuar co gráficos di ámicos....... ........................................................................... 1 30 CRE R TABLAS DE ADMINISTRA IÓN Y ANÁLI IS DE DATOS RELACIONADOS ................................ 130

FUNCI NES DE B ASE DE DA TOS ......................................................................................... 135 B AS DE DATOS ..................................................................................................................... 136 FUN IONES BD ...................................................................................................................... 136

Sintaxis gene al de las fun ciones de base de dato .................. ...................................... 1 36 FORM

LARIOS Y MACROS .................................................................................................. 143

ACTI ACIÓN DE L FICHA PRO

RAMADO .......................................................................... 144 DISEÑO DE FORM LARIOS ....................................................................................................... 144 Mostrar y ocul tar element s ................ ............................................................................ 1 44 U o de Contr les .............. ............................................................................................... 1 47 A regar contr les ............. ............................................................................................... 1 47 F rmato de c ntrol ........... ............................................................................................... 1 49 F rmulas relacionadas a c ontroles. .... ............................................................................ 1 52 SEG RIDAD DE M CROS ......................................................................................................... 153 Firmas digital s y cómo fu ncionan ...... ............................................................................ 1 54 Firmar macro digitalment e ................. ............................................................................ 1 55 GRA AR UNA NUEVA MACRO ................................................................................................... 155 P ra ejecutar una macro ................................................................................................. 1 56 A igne su ma ro a un bot n de comandos ............. ........................................................ 1 57 V a su macro con el edito r. ................. ............................................................................ 1 58 R ferencias R elativas....... ............................................................................................... 1 60 Eliminar una acro........... ............................................................................................... 1 61 PLA TILLAS ........................................................................................................................... 162 U ar plantillas predefinidas ................. ............................................................................ 1 62 C ear plantilla s personalizadas ........... ............................................................................ 1 63 U ilizar plantill as personali zadas ......... ............................................................................ 1 65

HERR MIENTAS E REVISIÓN .............................................................................................. 167 UTILIDAD DE LAS ERRAMIENT S DE REVISI N ......................................................................... 168

Ortografía...... ...................................................................................................................... 1 68 Referencia, Sinónimos y t aducir ........ .............................................................................. 1 69 ASIGNACIÓN DE COMENTARIOS ............................................................................................... 170

A regar un comentario ....................................................................................................... 1 70 E itar un com entario .......................................................................................................... 1 71 Eliminar un co mentario ....................................................................................................... 1 71 Mostrar los co mentarios ..................................................................................................... 1 72 HER AMIENTAS DE CONTROL DE CAMBIO ................................................................................. 172

P oteger elementos de la oja de cálc lo ......................................................................... 1 72 P oteger elementos del lib ro................ .............................................................................. 1 76 Compartir libr .................................................................................................................... 1 78 P rmitir que v arios usuari s modifique n un libro al mismo tiem po .................................. 1 78 Compartir un l ibro ............................................................................................................... 1 79 P oteger y co partir un libro ............... .............................................................................. 1 81 Control de ca bios............................................................................................................. 1 82

Programa Nacional de Informátic

III

Capí ulo

Fu cio es e Te to En este capítulo apren eremos a: omparar adenas de caracteres mplear la funciones de texto rear códig os autogen erados

Programa


1


Fun iones

e text

Funciones que no s permiten trabajar con el texto q e contien n las celda s. Es importante ha er notar q e en el m mento qu utilizamo estas fun iones, en los argum ntos, tant podemos utilizar text directam nte entre omillas o u na referen ia a una elda la cu al contiene texto. En los ejempl s de las fu nciones uti lizaremos l as dos opciones.

LARGO Sintax is: =LAR O (texto)

LARG , devuelv el númer o de cara teres de resultados de apli car esta fu ción en la imagen

2

na caden

de texto. Observe los

Programa Nacional de Informática

Ca ítulo I: Funciones de Te to

MAY SC Convierte el texto a mayúscu las Sintax is: MAYU C(texto) Donde:: Texto: Es el texto que desea convertir a mayúscul as. El arg mento te to puede ser una refere cia o una adena de t exto

MINUSC Convierte el texto a minúscul as Sintax is: MINU C(texto) Donde:: Texto: Es el texto qu desea co vertir a mi úsculas. E l argument texto puede ser una re erencia o u na cadena de texto

NOM ROPIO Cambi a mayús ula la pri era letra el argume nto texto y cualquiera de las otr as letras e texto q e se enc entren de pués de n carácter que no s ea una let a. Convierte todas la s demás le tras a minú sculas. Sintax is NOMPROPIO(tex to ) Texto es el text entre co illas, una órmula qu devuelve texto o un referenci a una celda que co tiene el te to al que desea agreg ar mayúsc las


3


IZQUIERDA Devuelve el prim r carácter caractere de una c dena de te xto, según el número de caract res que es pecifique e l usuario. Sintax is IZQUI RDA(text ;núm_de_ aracteres) Donde:: Texto, es la cade a de texto que contie ne los cara teres que desea extr er. Núm_ e_caracteres , espe ifica el nú mero de IZQUI RDA.

aracteres que desea extraer con

Debe os tener e cuenta la siguiente considera iones: Núm_ e_caracter es debe se r mayor o i ual que c ro. Si nú _de_caracteres es ayor que la longitud del texto, IZQUIER A devolv rá todo el texto. Si núm _de_carac eres se o ite, se calc ulará com 1.

• •

•

DERECHA DERE HA devuelve el últim carácter caracteres de una c dena de t xto, según el númer de caract res que el usuario es pecifica. Sintax is DERE HA(texto;núm_de_c racteres) Texto, es la cade a de texto que contie ne los cara teres que desea extr er. Núm_ e_caracteres , espe ifica el nú mero de DERE HA.

4

aracteres que desea extraer con



Obser aciones Núm_ e_caracter es debe se r mayor o i ual que c ro. Si nú _de_cara teres es ayor que la longitu del text , DEREC A devolv rá todo el texto. Si núm _de_carac eres se o ite, se calc ulará com 1.

• •

•

EXTR E Devuelve un núm ero especí ico de car cteres de una caden de texto, comenzando en la posición qu especifiqu e y en func ión del nú ero de car acteres qu especifiq e. Sintax is EXTR E(texto;p sición_ini cial;núm_ e_caracteres ) Texto, es la cade a de texto que contie ne los cara teres que desea extr er. Posici n_inicial, es la posi ción del pr imer carác er que de ea extraer de texto. La posición_inicial d l primer ca rácter de t xto es 1 y sí sucesiv mente. Núm_ e_caracteres , especifica el nú ero de c racteres q ue desea devuel a del argu mento text .

ue EXTR E

Obser aciones •

•

•

•

Si posición_inicial es mayor que la long itud de tex to, EXTRA devuelve "" (texto acío). Si posición_inicial es menor que la lon itud de te to, pero p sición_inicial más n m_de_car acteres ex ede la lo gitud de t xto, EXT AE devuelve los car acteres ha ta el final e texto. Si posición_inicial es meno que 1, E TRAE de uelve el v alor de er or #¡VAL R! Si nú _de_caracteres es negativo, E TRAE de uelve el valor de er or #¡VAL R!


5


HALLAR HALL R busca na caden de texto dentro de una segu nda cadena de texto y devuel e el núm ro de la osición ini cial de la rimera cadena de t xto desde el primer carácter d la segund cadena d e texto. Sintax is HALL R(texto_b uscado;de ntro_del_texto;núm_inicial) Texto buscado es el tex o que de ea encon rar. Pued utilizar l s caracter es comodines, signo de interro ación (?) y asterisco *) en el ar umento texto_busca o. El signo de interr gación cor responde a un caráct r cualquier a y el aster isco equiv le a cual uier secue ncia de ca acteres. Si lo que de ea encont rar es un asterisco o un signo e interroga ción, escri a una tilde (~) antes del carácter . Dentro _del_tex t Núm_i nicial búsqu da.

es el tex o en el qu desea en ontrar text _buscado.

es el número de caráct r en dentr o_del_text donde de sea iniciar la

Obser aciones Utilice HALLAR ara deter inar la u icación d un carácter o de u na caden de texto dentro de otra cade a de text , de mod que pue da utilizar las funcion es EXTRA o REEM LAZAR, p ra cambiar el texto. HALL R no disti gue mayúsculas de minúscula . Si desea realizar una búsqu da que dis tinga mayúsculas de inúsculas, utilice EN ONTRAR. Si no e puede h allar el arg mento texto_buscad , la funció devuelve el valor de error #¡V LOR! Si el ar gumento núm_inicial e omite, el valor pred terminado es 1. Si el valor del ar gumento núm_inicial no es may or que 0 ( cero) o si es mayor que el lar o del argu mento dentro_del_texto, se dev elve el va lor de err r #¡VALO ! Utilice núm_inici l para s ltar un número es ecífico d caracter s. Toman do HALLA como ej mplo, sup nga que e stá utilizando la cade na de texto: "AYF00 3.AtuendoParaJóven s". Para e contrar el número de la primer "A" en l parte descriptiva de la cade a de text , establez ca núm_i icial igual a 8, de m anera que la función no busqu en la pa te corres ondiente l número e serie. H LLAR co ienza por el carácter 8, localiz texto_bu cado en el siguiente carácter y devuelve el número 9. HALL R siempre devuelve l número e caracter es desde l principio de dentro del_texto, contando l s caracter s omitido si núm_ini cial es ma or que 1.

•

•

•

• •

•

6



ENC NTRAR Encue tra una c dena de t exto dentr una segunda y dev uelven el úmero de la posición inicial de la primera cadena d texto des e el prime carácter de la segun da caden de texto. Sintax is ENCO TRAR(te to_busca o;dentro_ del_texto; úm_inicial ) Texto buscado es el texto que desea encontrar. Dentro _del_tex t

es el tex o que a su vez contie e el texto ue desea encontrar.

Núm_i nicial es ecifica el carácter a partir del cual com nzará la úsqueda. El primer carácter d dentro_d l_texto es el carácter número 1. Si omite n m_inicial, se asume que es 1. Obser aciones ENCONT AR distingue mayús ulas de m inúsculas no permi e caracter es comodín. Si no des a realizar una búsq eda que distinga m yúsculas de minúscula ni usar caracteres c modín, pu de utilizar HALLAR. Si texto_buscado es "" (texto acío), EN ONTRAR coincide on el pri er carácter d la cadena de búsqu da (es deci r, el caráct er de núm_ inicial o 1). Texto_buscado no pu ede contener ningún arácter co odín. Si texto_b scado no aparece e valor de er ror #¡VAL R! Si núm_ini cial no es #¡VALOR!

dentro_d l_texto, E CONTRA

devuelve el

ayor que cero, ENC NTRAR devuelve el alor de er or

Si núm_inicial es m yor que la longitud d e dentro_ el_texto, devuelve l valor de rror #¡VALOR!

NCONTR R

Utilice nú _inicial p ra saltar n número específic de caracteres. Utili ar ENCONT AR como jemplo, suponga que está utiliz ndo la cad ena de tex o: "AYF0093. AtuendoP raJóvenes". Para enc ontrar el n úmero de l primera " A" en la part descriptiv de la cadena de text o, establez ca núm_ini cial igual a 8, de manera que la fun ción no bu que en la arte corre pondiente al número de serie. EN ONTRAR comienza or el cará ter 8, local iza texto_buscado en el siguiente arácter y devuelve el número 9. ENCONT AR siempr e devuelve el número d caracteres desde e l principio de dentro_ del_texto, contando l os caracteres omitidos si núm_inici l es mayor que 1.


7


Con atena Funci nes Funci n concatenar Concatena dos o más caden s en una adena de t exto. Sintax is CONC TENAR (t exto1;text 2; ...) Texto1, texto2... son de 2 a 255 ele entos de t xto que s unirán en un eleme to de tex o único. L os elemen os de tex o pueden ser caden s de text , números o referencias a celd as únicas. Tambi n puede u tilizar el o erador de cálculo sí bolo de "y " comercial (&) en lu ar de la unción C NCATENAR para unir element s de text . Por eje plo =A1& 1 devuel e el mism valor que =CONCATENAR(A1; 1).

En la i agen ant rior puede notar que si usa concatenar co texto constantes estos deben estar enc rrados en re comilla , esto ta bien aplic para los espacios en blanco.

8



La función CON ATENAR también puede ser sada par concatenar funcion s, dandol e así a las funciones de texto antes vistas una pote cia extrao dinaria en el tratamiento de las cadenas de texto en xcel.

Ac tivi dad 01: Uso de la funciones de texto Se tie en los sig uientes no mbres y a ellidos qu e represen tan los cli ntes de una determinada entid ad:

Se le olicita for ar codigo autogene rados a partir de los nombres y apellidos de estos lientes de sta maner : a. Las dos pr imeras letr s de su no mbre La función sería: =IZ UIERDA( 2;2) b. La tercera letra de su apellido pa terno La función sería: =EXTRAE(B2; ;1) c. La ultima l etra de su pellido ma terno La función sería: =DERECHA(C ;1) d. Para for ar el codi go usted tendría q anteriores, escriba la siguiente f rmula en l =IZQUIERDA(D2;2) EXTRAE(B2;3;1) & e. El resultado final serí el mostra o en la im

f.

e concate nar las tr s funciones celda A2: ERECHA( 2;1) gen:

Una modificación y o ajuste de la fór mula ante riormente usada se ía empleand la funció MAYUS a fin de que los c odigos se muestren en mayúscula.


9


ctividad 2: Activid d Pro ue ta 1. Se pide e cribir una órmula qu permita ostrar sól el turno 4to carác er del código ). Use la fu ción EXT AE 2. Escriba otra fórmula para cog r el nomb re del usu ario de la dirección de correo (de l 1er carác ter hasta l arroba ). Use la f unción IZQUIERDA y la función H LLAR para poder det rminar la u bicación d l arroba 3. Luego, se pide crear otra fórmul que mue tre el códi o de área (3 caracter es después el guión d el código). Use la función EXT RAE junto a la funci ón HALLAR para determinar la posi ción del gui ón. Su tabla debe quedar co o:

Puede usar funci nes condi ionales y concatena textos de acuerdo al resultado de dicha unción. P r ejemplo, se pide allar la co lumna Tra tamiento, la cual de be mostrar el Sr o S ta (de acu rdo al sex ) y el Apell ido de los mpleados. En la celda H3, e criba:

=SI(E3="F";"Srt .";"Sr." ) & " "

10

C3



Preg ntas e Rep so 1. Si se tiene la siguient tabla, cu l es el resu ltado de la siguiente funciones :

=L RGO(B4) =IZQUIERDA(B4;3) =IZQUIERDA(B5; HALL R(“ “;B5)-1) =H LLAR(“ “; B6) – HALLAR(“,”;B6 ) 2. Escriba lo datos mo trados en la imagen e la págin siguiente, agregue u na columna ara gener ar un código el cua l estará f rmado de la siguie te manera: a. Pri era letra el nombre b. Pri era letra el apellido c. Ultima letra d l apellido d. Un guión e. Un Correlativo Tener en uenta que el nombre el apellid están en na misma celda.


11


12


Capí ulo

Fu cio es e fe ha hor En este capítulo apren eremos a: prender a usar la serie de fechas de Excel plicar las unciones de Fechas alcular la edad de una persona

Programa acional de Informática

1


La s rie de echas de Exc l Excel onsidera a las fechas como val res numéricos. Estos valores es án entre 1 el cual e considerado como 1 de enero e 1900 al 2958465 orrespondiente al 31 de diciem re de 999 . Así, el úmero 5 c rresponde al 5 de enero de 1900 y el 32 al 1 de feb ero de 19 0. Al cont ner númer os, las cel as pueden ser usadas para definir fórmul s. El ingr so de fechas debe ser muy preciso. En la hoja 1 de su libro de Excel, escrib : 12-Nov 12-11 12/11 12/11/71 Obser e que ahor a, Excel m estra estos datos co o fechas.

Si usted no escri e el año, l s fechas son conside adas del año actual. Si usted escribe : Si usted escribe :

1-4

Excel lo consi era como

de abril d 2007

20-10

Excel lo consi era como 0 de octubre de 2007.

Escrib las fecha propuestas y luego, pulse F2 p ra ver el c ntenido:

Si usted escribe na fecha y solo indic 2 dígitos para el añ , Excel co pleta el año 2000 para fechas menores a 1930 y 19 0 para añ s posterior es. Si ust d escribe : Si escr ibe

:

30-mar-10

Excel la consi era como 0-mar-2010

30-mar-72

Excel la consi era como 0-mar-1972

Escrib las fecha propuestas y observ el año:

14


Capítulo II: Funciones de fecha y h ra

For ato a as cel as que contienen fe has Excel plica un ormato au omático a las celda que contiene fecha . El form to aplicado es dd-m mm (2 dígitos para el día y 3 car acteres par a el mes).

Aplic r format general Para a licar form to general a las celdas que conti nen fechas haga lo siguiente: 1. Seleccion la celda q e contien la fecha 2. En la ficha Inicio haga clic en fl cha que d spliega el formato a las celdas en el grupo Número. (T mbién puede pulsar l combinación CTRL+ )

3. En el cuadro de diálo o seleccione la ficha úmero y aga clic e General.


15


4. Pulse Ace tar para a licar el for ato Gene al. 5. Note com ahora la celdas q e antes ostraban la fecha con formato se muestran omo núm ros.

Form tos pred finidos Para cambiar el formato d fechas a licado a sa celda o aun ran o, realice el siguiente procedi iento: • • • •

16

Seleccion las celdas a cambiar el formato. Presione TRL+1 En la ficha Número , aga clic e la categor ía Fecha Seleccion uno de lo formatos integrados.



En la iguiente il stración, e muestra algunos de los formatos de este cuadro de diálog . Para probar estas opciones, scriba en alguna celda la fecha 12-11-71 y apliqu los formatos integrados:

Form tos pers nalizado Al igu l que con los números, Excel permite cr ear formatos person lizados p ra mostrar las fecha de acuerdo a las especificacion s que ust d necesite. Por ejemplo, mostrar sólo el es de una fecha, q izás inclui el nombr del día a una fecha o incluso mostrar u texto que acompañe a una fech : La fec a 30-3

se pue e mostrar como:

Marzo


martes 30 de marzo


30 – mar Cumpleaños de Doris

Para r alizar estas acciones es necesa io conocer los código que tiene Excel para el tratamiento de fechas. Códi o d ddd dddd m mmm mmmm aa

Util idad mu stra dígito para el dí mu stra la abr viatura del día mu stra el no bre del dí mu stra dígito para el m s mu stra la abr viatura del mes mu stra el no bre del m s mu stra dígito para el año

aaaa

mu stra 4 dígi os para el ño

Tabla: Códigos a usar:

En la siguiente ta la veremo el resulta o de aplic r formatos a la fecha 12-11-71 Tabla: Ejemplos e formatos personalizados básicos Formato dd-mmm dd-mm dd/mm


Resu ltado 12-nov 12-11 12/11

17


dd/mm/aa dd-mm-aaaa

12/11/71 12-11-1971

En la siguiente ilustración, v mos más f rmatos personalizad s.

Aritmética de f echas Las fe has son t atadas co o números. Así ust d podrá u ar celdas que contiene fechas para usar funciones definir fór mulas. Podrá sumar días a una f echa o res ar dos fe has para hallar el intervalo de dí s entre ell s.

Sumar días a una fecha Al sumar número a una celda que co tenga fechas, Excel onsidera que se suman días y l resultad se repres nta con fo mato de fecha. Veam s un ejem lo práctico. En el libr o mostrad , se pide alcular el encimient o . Para r alizar esto, es neces rio sumar la Fecha de Présta o el número de días de la colu na D. La fór ula será:

= C5 + 5

18



En el caso de q e la fórm la creada no muestre como r esultado una fecha, es neces rio aplicar un format de fecha. Revise el tema For atos de Fecha en e te mismo capítulo. En el iguiente e emplo, ob erve que los que se desea inc ementar a la Fecha de Présta os son semanas, entonces, la f rmula par el vencimiento será:

= C5 + (D5 * 7)

La cel a D5 (semanas) se de Préstamo .

ultiplica p r 7 para p der incre entar 7 dí s a la Fec a

En caso de que quiera sum r meses, odría considerar que cada mes tiene 30 dí s, así en el siguient ejemplo la fórmula p ra calcular la fecha d vencimiento sería:

=C5 + ( 5*30)


19


Resta dos fechas Excel permite restar celd s que co tengan fe has, de calcular el intervalo de días ntre dos 2 fechas.

sa forma usted puede

En el iguiente ejemplo, se ide calcul r los días e atraso ara cada l tra. Obser ve que e la celda 2 se tien una fecha, entonce para cal ular los días de atraso debemos restar la fecha de ago de la echa de la celda F2 ( 0 – ene). La fór ula propu sta será:

=$F$2- 5 Se usa la referen ia absoluta para que todas las fechas se re ten con la misma celda.

En ca o de que el resultado se mue tre como una fecha, aplique u formato de númer General .

ctividad de 1: Uso de Aritmética Usar la aritmética fechas ara resolverdelosfechas caso propuest s:

1. En la siguiente imagen, escriba la fórmula para cal ular el Vencimiento . 2. Escrib otra fórmula para c lcular la f cha del A iso de C branza, que será 1 días ante de la fecha de venci iento. 3. Apliqu un format de dd-m m-aaaaa todas las echas.

20



Funci nes de Fecha Excel osee un c njunto de unciones que manipulan fechas. Entre las cuales se v a desarr llar las siguientes:

FECHA Devuelve el núm ro de serie secuencial que representa una echa deter minada. Si el format de celda era Gener l antes de escribir la unción, el esultado t ndrá form to de fec a. Sintax is FECH (año ;mes ;día ) Año El argum nto año puede tener de uno a cuatro ígitos. Mi rosoft Ex el interpr ta el arg mento año según l sistema de fecha empleado. De for a predet rminada, xcel para indows utiliza el sist ma de fec as 1900.

Si el año stá entre 0 (cero) y 1 99 (inclusive), Excel grega ese valor a 1900 para calcular el año. or ejempl , DATE(108,1,2) devuelve la fec a 2 de en ro de 2008 (1900+108). Si el año stá entre 1900 y 999 (inclusive), Excel utiliza ese valor como a o. Por ejemplo, DATE(2 08,1,2) devuelve la f cha 2 de enero de 20 8. Si el año s inferior 0 o supe ior a 10000, Excel d vuelve el alor de er or #¡NUM!. Mes s un enter positivo o negativo que representa el mes del año, desde el 1 al 12 (de en ro a dicie bre).

Si el mes s superior a 12, el m s agrega ese número de meses l primer mes del año especificado. FECHA( 008,14,2) devuelve l número de serie que representa el 2 de fe rero de 20 9. Programa Nacional de Informátic

21


Si el mes es menor ue 1, se r sta del primer mes del año especificado ese número d meses m s 1. Por ejemplo, FE HA(2008,-3,2) devuelve el núm ro de serie q e represe ta el 2 de eptiembre de 2007. Día e un entero positivo o egativo q e representa el día d l mes, des e el 1 al 3 .

Si el día s superior al númer de días ue tiene l mes es ecificado, se agrega dicho núm ro de dí s al pri er día del mes. or ejemplo, FECHA(2 08,1,35) devuelve el número e serie que repres nta el 4 de febrero de 2008. Si el valor especificado por día es menor que 1, se resta del primer día el mes dich número de días ás uno. Por ejem lo, FECH (2008,1,-15) devuelve l número de serie que represent el 16 de diciembre d 2007. La fun ión FECHA es muy útil en fór ulas en las que el año, el mes y el día son fórmul s, no constantes.

FECHANUMERO Devuelve el núm ro de serie de la fe ha repres ntada por texto_de_ echa. Puede usar F CHANUMERO para onvertir u a fecha re resentada por texto n un núm ro de serie. Sintax is FECH NUMERO(texto_de_ fecha) Texto de_fecha es el texto que repr esenta una fecha en un formato de fecha de Micros ft Excel. Por ejemplo, "30-1-2 08" o "30-ene-2008" son cadenas de te to entre comillas qu representan fechas. Con el sis ema de fe has predeterminado de Excel para Windows, texto de_fecha debe repr esentar una fecha entre el 1 de diciem re de 190 y el 31 d diciembre de 9999. ECHANU ERO dev elve un valor de err r #¡VALO ! si texto_de_fecha queda fuera e este ran o.

Si omite la parte texto_de_fecha corres ondiente al año, FECHANUME O usa el año corriente del reloj integrado a su PC. a informa ión de hor a de texto de_fecha se pasa por alto.

Para ver el núm ro como una fecha, eleccione la celda y, en la fich Hoja, en el grupo Número , haga clic e la flecha que apare e junto a Formato d e número ; a contin ación, haga clic en Fecha corta o Fecha la ga .

22



FECHA.MES Devuelve el núm ro de serie que repre enta la fe ha que indica el núm ro de meses anteriores o posteriores a la fecha especificada (argum nto fecha inicial). Use FECH .MES par calcular l s fechas e vencimi nto que c en en el ismo día el mes q e el día de emisión. Sintax is FECH .MES(fec a_inicial ; eses ) Fecha inicial es una fec a que re resenta l fecha inicial. Las f chas deben especi icarse me iante la f nción FE HA o co o resulta o de otras fórmulas o funcio es. Por ejemplo, utilic FECHA(2008;5;23) ara el día 3 de may de 2008. Meses es el nú ero de m ses anteriores o pos eriores al rgumento fecha_inicial. Cuand meses es un valor positivo el resultado s una fec a futura; uando es un valor negativo, el resultado es una fech pasada.

Si el a gumento f echa_inicial no es un fecha válida, FECH .MES dev elve el valor de err r #¡VALO !.

FIN.MES Devuelve el nú ero de s rie del último día d l mes, a terior o posterior a la fecha_inicial del úmero de mes indicado. Use FI .MES par a calcular las fechas de vencimiento que aen en el ltimo día del mes. Sintax is FIN.M S(fecha_i nicial ;mes s ) Fecha inicial es una fec a que re resenta l fecha inicial. Las f chas deben especi icarse utilizando la f unción FE HA o co o resulta o de otras fórmulas o funcio es. Por ejemplo, utilic FECHA(2008;5;23) ara el 23 de mayo de 2008. Meses es el nú ero de m ses anteriores o pos eriores al rgumento fecha_inicial. Cuand meses es un valor positivo el resultado s una fec a futura; uando es un valor negativo, el resultado es una fech pasada.

Si el argumento f echa_inicial no es un fecha válida, FIN.M S devuel e el valor de error #¡NUM!. Si el a gumento f cha_inicial más el ar umento meses da c mo resultado una fecha que no es válida, FIN.MES d vuelve el alor de err or #¡NUM!.


23


DIA.L B Devuelve un número que re resenta u a fecha q e es el nú ero de dí s laborables antes después e una fec a (la fecha inicial). Los días laborables excluyen los días de fin de sema a y cualquier fecha identificada en el argumento estivos. Use DIA.L B para excluir fines de semana o días f estivos cu ndo calcule fechas de vencimiento de f cturas, las fechas d entrega esperadas el númer o de días de trabajo realizado. Sintax is DIA.L B (fecha_i nicial ;días lab ;festiv s) Fecha inicial e una fecha que repre enta la fec a inicial. Días_l festivo argum pasad

b es el número de días labora les (días q e no sean fines de s mana ni días ) anteriores o posteri res al arg mento fecha_inicial. n valor p sitivo para el nto días_l b produce una fecha futura; un úmero negativo prod ce una fecha .

Festiv s es una lista opcional de una o varias fechas que deben excluirse el calend rio laboral, como los días festiv s nacionales y local s. La lista puede ser un rango e celdas ue conten an las fechas o una constante atricial de los númer os de serie que repr senten las fechas.

Si uno de los arg mentos n es una fecha válida DIA.LAB d vuelve el alor de er or #¡VAL R!. Si el ar gumento f cha_inicial más el argumento días_lab produce una fe ha no váli a, DIA.L B devuelv el valor d error #¡N M!.

24



DIAS. AB Devuelve el número de días laborabl labora les no incluyen los fi es de se argum nto festiv s. Utilice IAS.LAB acumulados de l s empleados basánd un período específico.

s entre fecha_inicial y fecha_fi al. Los días ana ni otr s fechas ue se identifiquen en el ara calcul r el incre ento de l s beneficios se en el úmero de días trabajados dura te

Sintax is DIAS.L AB (fecha inicial ;fec ha_final ;festivos) Fecha inicial e una fecha que repre enta la fec a inicial. Fecha final es na fecha que representa la fech final. Festiv s es un rango op ional de una o varias fechas que deben excluirse el calend rio laboral, como los días festiv s nacionales y local s. La lista puede ser un rango e celdas ue conten an las fechas o una constante atricial de los númer os de serie que repr senten las fechas.

Si uno de los arg mentos no es una fe ha válida IAS.LAB devuelve el alor de er or #¡VAL R!.


25


DIAS M Devuelve el día de la semana correspo diente al rgumento núm_de_s rie. El día se devuel e como u número e tero entre 1 (doming ) y 7 (sába do). Sintax is DIASEM(núm_de _serie ;tipo) Núm_ e_serie s un núm ro secuen ial que representa la echa del día que inte ta buscar . Las fechas deben especificarse mediante la función FECHA o c mo resultado de otras fórmulas o funcione . Tipo

26

s un núm ro que det rmina el ti o de valor que debe evolverse.

Tipo

Nú mero dev elto

1 u omitido

Números del 1 (domi go) al 7 (sábado). Igual a an eriores de Microsoft Excel.

2

Números del (lunes) al 7 (doming ).

3

Números del

n versiones

(lunes) al 6 (doming ).



DIAS360 Calcul el númer de días e tre dos fe has basándose en u año de 3 0 días (doce meses de 30 días) que se utiliza en alg nos cálcul s contables. Use esta función p ra facilitar el cálculo de pagos i su sistema de contabilidad se asa en 12 meses de 30 días. Sintax is DIAS3 0(fecha_i nicial ;fech _final ;mé odo) Fecha inicial, fecha_final son las d s fechas ntre las que se des a calcular el númer de días. i la fecha_inicial es p sterior a l fecha_fin l, DIAS36 devuelve un númer negativo. Métod es un v lor lógico ue especifica si se utilizará el m todo de cálculo europeo o amer icano. Métod o

FALS omitid

Modo de c álculo

u

Método U (NASD). i la fecha inicial es l último dí del mes, se convierte en el 30 del mismo m s. Si la fe ha final es el último ía del mes y la fecha ini ial es ante ior al 30, l fecha final se convie te en el 1 del mes sigui nte; de lo ontrario la fecha final se convie te en el 30 del mismo m s.

VERD DERO

Método eu opeo. Las fechas iniciales o finales que cor responden al 31 del mes se convier en en el 3 del mism mes.


27


NUM.DE.SEMANA Devuelve un núm ro que indica dónde ae la sem na numéri amente dentro un añ . La fun ión NUM. E.SEMANA consider a la sema a en la q e se encuentra el 1 de enero omo la primera sem na del añ . Sin emb rgo, hay un estándar europeo que define la primera semana como la que tiene la m yoría de días (cuatro o más) en el año nuevo. Esto ignifica que, para los años en los que la pr imera sem na de en ro tiene tr es días o enos, la f nción NU .DE.SEM NA devuelve números de semana que son incorrect s según el estándar europeo. Sintax is NUM. E.SEMAN (núm_de serie;tipo Núm_ e_serie

s una fecha contenid en la semana.

Tipo es un nú ero que determina en qué d a comien a la sem na. El valor predet rminado es 1.

28

Tipo

Una sem na comie za

1

El domingo. Los días de la sem na se numeran del 1 l 7.

2

El lunes. os días de la semana se numeran del 1 al 7.



A continuación m stramos o ras funcio es para el tratamient de las fe has en ex el 2007.

HOY Devuelve el núm ro de seri de la fec a actual. l número e serie es el código de fecha- ora que Microsoft Ex el usa par los cálcul s de fecha y hora. Si el formato de celda ra Generall antes de scribir la f nción, el r sultado te drá format de fecha. Sintax is HOY( )

AHO A Devuelve el núm ro de seri de la fec a y hora actuales. Si el formato de celda ra Gener l antes de escribir la unción, el esultado t ndrá formato de fecha. Sintax is AHOR ( )

En los números de serie, lo dígitos a la derecha del separador decimal representan la hora; los números a la iz uierda representan la fecha. Por ejemplo, l número de serie 0,5 representa la hora 12:00 del mediodía. La fun ión AHORA sólo ca bia cuand se realiza un cálcul en la hoja de cálcul o cuand se eje uta una macro que contien la fun ión. No se actualiza consta temente.


29


DIA Devuelve el día d una fecha, representada por un número d serie. El día se expresa como n número ntero comprendido e tre 1 y 31. Sintax is DIA (nú m_de_ser ie )

MES Devuelve el mes de una f cha repre entada por un número de seri . El mes se expresa como nú ero enter comprendido entre 1 (enero) y 12 (diciemb e). Sintax is MES(n úm_de_serie)

AÑO Devuelve el año correspon iente a u a fecha. entero comprendi o entre 1900 y 9999.

l año se devuelve como núm ro

Sintax is AÑO (n úm_de_serie)

Ejemplos de funciones. (Se onsidera c mo fecha ctual el 1 de marzo) Funci n

Valo devuelto

=HOY( )

18/0 /2007

=AHO A( )

18/0 /2007 13:10

=DIA( OY( ) )

18

=MES( HOY( ) )

3

=AÑO( HOY( ) )

2007

Aplicaciones e las unciones de f cha Calcular la ed d Una d las aplic ciones de las funcio es podría ser la de alcular la edad de una persona. En el si uiente eje plo se pide calcular l edad de los pacient s:

30



Lo pri ero que d bemos hacer el calc lar el número de día de la fec a actual a la fecha e nacimie to. La fórmula será:

=HOY ( ) - B4 Esto d como res ltado días, los cuales deben ser convertido a años, e to dividiendo entre 365 (días d un año)

= (HOY ( ) –B4) / 365 Finalmente, debe os queda nos con la parte enter a de este r esultado:

= ENTE O((HO ( ) –B4 / 365)

Otra forma de calcular la ed d, podría er a travé de la función Año. P r ejemplo, la siguiente fórmula puede ser onsiderad .

= AÑO( OY( )) AÑO(B ) Calcular el 1er ía del m s de una fecha Si ust d tiene una fecha, p drá mostr ar el 1er día del me correspondiente a esa fecha. Por ejemplo, para el 15-febrer se mostr rá 1-febrero, para el 30-marzo se mostrará 1-marzo, etc. En la hola de cálculo mostrar escriba la siguiente f rmula en l s celdas v cías:


31


=FEC A(AÑO(A11);MES(A11);1) Se escribe 1 porque se dese mostrar el 1er día d dicho me . Tambi n se pued calcular el primer día del mes siguiente de una fecha, es decir p ra el 15-f brero se ostrará 1- arzo, para el 30-mar o se mostr ará 1-abril, etc. Ac tivi dad 02: Uso de la funciones de fecha

Para reali ar esta actividad se tr abajará co la hoja d cálculo F chas , si aún no lo ha h cho pida a su instructor una copia de la misma. Calcular d da una fe ha, el primer lunes d la seman a que cor esponde y el lunes siguiente, parti ndo de la f echa origin l dada. En este jercicio, partiendo de un número de fechas especificadas, se determinar a en primer lugar l fecha de comienz de la semana a que correspon e la fecha, considera do como comienzo el día lunes. Como segunda oper ción, parti ndo siem re de la fecha origen, el comienzo de la sem na siguien e a la fech dada. En la siguiente imagen se muestra una ima en resumi a del archivo fechas:

32



1.

Ubíquese en la celda C3 y escriba la iguiente fórmula:

=C3

2.

Ahor a vamos dar formato a la celda C3 de tal man ra que s lo mue tre el día, or lo tanto haga lo si uiente: a. Con la cel a C3 sele cionada, pulse CTRL 1 b. En la fich Número el cuadro de diálogo haga clic en Personali ar , en Tip escriba “ ddd” y pul e Ac eptar

3.

Sele cione la c lda D3 y e criba la si uiente fór ula:

SI(B3=" ";"";B3-DIASEM(B3;3))

La f rmula comienza con l aplicació de la fun ión SI, donde determina que si la celd donde c ntiene la fecha orig n se enc entra vacía, devuelva celda vacía. E to se realiza para poder em lear la h ja elab rada sin q e de error, al no encontrar dato. En l segunda arte de la ormula, a l fecha ori en se le resta la función DIA EM, de e a misma elda con l parámetr o tipo 3, donde en ese caso devuelve alor 0 ( c ro), para el día lunes y 6 para el día domin o, por lo tanto como se verifica en este cas , al día omingo que corr sponde a la fecha e la celd B3, que devuelve 6 la función


33


DIA EM tipo 3, se le resta la mism función, ando com resultado 0 (cer ), que corr sponde al día lunes. La f rmula se c pia hasta la celda D33 y produc el mismo efecto. 4.

Sele cione la c lda E3 y e criba la si uiente fór ula:

SI(B3= ";"";B3 SI(RESIDUO(DI ASEM(B 3;3);7)> ;RESIDUO(-DIA SEM(B3;3);7);7)

Se mplea la f unción RESIDUO pa a obtener los días que se deben agre ar a la fe ha de la c lda B3, p ra obtener el lunes siguiente de la mis a. Aplicado de esta manera se puede bservar q e la función devuelve 0 (cero) para lunes, 6 par a martes, 5 para mi rcoles y sí sucesivamente. La f nción SI s aplica en primer lug r para determinar que en caso de no existir ning na fecha en la celd B3, dev elva celda vacía, y en segundo lugar, para d terminar ue en c so de que la función RESIDUO devuelva un valor menor o igual a ero, como el día lun s, devuelva el nu ero 7 La f rmula se c pia hasta la celda E3 5.

Grabe y cierre su trabajo.

ctividad 3: Calcular la cantid ad de días del mes a terior

Este es un ejercicio q e permite ver cómo actúan las f nciones d MES y A O en forma desagrega as. Permite determinar sobre una fecha ada cuantos días tiene el mes ant rior a la fe ha especif icada. En la celd C2 escrib la siguiente fórmula

=(FECHA (AÑO(B2);MES(B2)-1;1)-FECHA(AÑO( 2);MES( 2);1))*-1

34



Preg ntas e Rep so 1. Marque la forma inco recta para ingresar fe has en Excel: a. 15-mar b. 12-1-2003 c. 10-25-90 d. 15-2 2. El número 35 ¿qué fecha representaría en xcel? 3. Teniendo la fecha: 0-mar-72, escriba el resultado al aplicar los siguientes formatos personaliza os: dd-mmmm yyyy m m/dd m mm 4. ¿Cuál es l diferencia entre usar la función =HOY y = HORA? 5. En este jercicio s solicita eterminar, partiendo de la fe ha actual e incorporando la fech de naci iento, la dad de la persona y el día de la semana q e se produ jo el nacimiento.

6. Para reali ar el ejercicio se solicita confe cionar una hoja con un listado de personal, la fecha de ingreso de cada no y el sueldo básico según su categoría.


35


Se debe alcular la antigüeda de cada uno a la f echa, y el sueldo total, teniendo n cuenta el increm nto que e estable e por antigüedad y el descuento del 12,50 que sufre cada sueldo sobre el otal.

36


Capí ulo

Fu cio es e b squ da En este capítulo apren eremos a:

plicar las unciones de búsqued alidar dat s en celda

Programa


3


Fun ión BUSCAR Busca un valor e pecífico en la primer olumna de una matri de tabla y devuelve, en la mis a fila, un valor de otr columna e dicha m triz de tabla. La V de BUSCA V significa vertical. U ilice BUS ARV en lugar de BU CARH si los valore de comparación se ncuentran en una col umna situ da a la izq uierda de l os datos ue desea uscar BUSC RV(valor buscado ;t abla;indic dor_colu

nas ;orde ado)

Valor_ buscado

Valor que se va a buscar en la primera olumna de la matriz d e tabla. Valor_busca o puede ser un val r o una r ferencia. i valor_bu scado es inferior al enor de los valore de la primera columna de tabla, BUSCAR devuelve l valor de rror #N/A. Tabla

Dos o ás colum as de dat s. Use un referencia a un rang o un nombre de ran o. Los valores de la prime a columna de tab la son l s valores que busca valor_ uscado . Estos valores puede ser texto, números o valores lógicos. Las mayús ulas y min úsculas del texto son quivalente s. Indica or_colum nas

Númer o de columna de tabl desde la ual debe d evolverse l valor coi cidente. Si el argum nto indic dor_colu nas es i ual a 1, l función evuelve el valor de la primer columna el argume nto tabla; i el argum nto indic dor_colu nas es ig al a 2, devuelve el valor de l a segunda columna de tabla y así suce ivamente. Si indica or_colum nas es: Si es inferi or a 1, BU CARV devuelve al valor de error #VALUE! Si es sup rior al nú ero de col mnas de t abla, BUSCARV dev elve el va lor de error # EF! Orden do

Valor lógico que especifica si BUSC RV va a uscar un coincidencia exacta o aproxi ada: •

Si se mite o es VERDAD RO, se devolverá un a coincide cia exacta o aproxi ada. Si o localiza ninguna coincidencia exacta, devolverá el siguie te valor m s alto infer ior a valor buscado . Los valores de la primera col umna de t bl a deben estar clasif icados según un crit rio de ord enación a cendente; en caso c ontrario, e posible q ue BUSC RV no de uelva el valor correct .

•

Si es caso, Si hay primer devolv

ALSO, B SCARV s lo buscará una coinc idencia ex cta. En e te o es nece ario orden ar los valor es de la pri mera colu na de tab la . dos o má valores e n la primer a columna de tabla, e utilizará el valor enco ntrado. Si no se enc entra una coincidencia exacta, se rá el valor de error # /A.

Por ej mplo:

= BUSC ARV(A 2; A5: D35; 3;Falso

38


Capítulo III: Funciones de búsque a

Esta función bus ará el valo r de la cel a A2 (crit ri o ) en el rango A5: 35. Ubicado este alor, se ostrará la columna (indicad r_column as ) de dicho rango. El último arámetro falso ) indi a que la tabla no está ordenada, por lo que realizará u na búsqu da exacta. En caso de que e l valor no s ea ubicado en el rango A5: D35, se mostra á el mens je de err r: #N/A (n t available) (no disponible). Para entender mejor esta f nción, de arrollarem s un cas práctico. Abra el li ro Boletas de Pago y observe l as tablas d e la Hoja1 y Hoja3.

En la oja3 se tiene una bol ta de pag s.


39


Para l ejemplo, en la Ho ja3 se pi e mostrar el Ap ell id o del Código A0 5, introducido en la elda D5. Como habrá observad , este dato debe ser uscado en la Hoja1. Así, la fórmula de la celda D para mostrar el Ap ell id o será:

= BUSC ARV(D5; Hoja1! 3:E13; ) El valor de la cel a D5 se b scará en l rango A3 :E13 de la Hoja1. Al ncontrase el valor, se mostrar á el conte ido de la da columna. Al omiti se el último argumen o, este toma el valor de Verdadero. La fór ula de la c elda F7 pa a mostrar la Categoría será:

= BUSC ARV(D5; Hoja1! 3:E13; ) Obser e que el v lor que ca mbia es el indicador columnas para que e muestre la 5ta col mna. Es pre erible trab jar con nombres de ango para el argume nto de la t abla. Así, en lugar e usar H ja1!A3: 13 com argument , se pued e asignar un nombre a este rango y traba jar con dic o nombre. En el iguiente ej emplo, se ha asignad o el nombr e de Dato al rango A3:E13 de la Hoja1. hora la f rmula ser :

= BUSC ARV(D5; Datos; ) Puede probar la funcionali ad de estas fórmul s, basta con escribir un código diferente en la celda D5. Par nuestro e emplo, al scribir A0 1, observe el resultad o: Có i go : Apell iidos:

A001 Ma uri

Categ A

ctividad 1: Usar F nción BU CARV

1. Obser e los dato de la Hoj a2 del libr Boleta d e Pago . E ta hoja muestr a información de las oras prog amadas y horas dict das de cada tr abajador

40



2. Ahora, en la Hoja , al rango A5: C14, asígnele el ombre de Horas .

3. Luego, en la cel a C10 de la Hoja3, escriba la fórmula n cesaria para mostrar las oras Dicta as.

4. Además, en la c lda D10 escriba la f rmula par mostrar l Pago Hora. ste datos ebe salir d el rango d la Hoja1


41


5. Finalmente, calcul e el Monto (Horas Dictadas por l Pago Hor a). 6. Su hoj se debe ostrar co o la figura:

Funci n BUSC RV com parte d una fór ula o fu ción.

La fun ión BUSC RV pued ser usada como part e de una f rmula o d una funci n. Inclus puede ser parte de otra función BUSCARV. Por ej mplo, en l celda E1 de la Hoj 3 se necesita calcula r las Hora Falta. E te valor e obtiene de la di erencia d las Horas Progra adas y de las Hor as Dictad as . En est caso, en la Hoja3 a tenemo el valor d las Hora Dictadas celda C10 y nos fal aría hallar el valor de las Horas rogramad s. Este valor debe s r buscado de la Hoj 2. Así, la f órmula ser :

=BUSCA V(D5;H ras;2)- 10 Ahora, la Retenci ón será d l 10% del Monto par a los trabaj adores Co tratados. n este c so, en la oja3 tenemos que ubicar el Es tado del mpleado, alor que se encue tra en la Hoja1 y que debe ser busca o. Luego se debe valuar si es contratado o no. a fórmula erá:

=SI(B USCAR (D5;Dat os;4)=" ontratado";E10 10%;0)

42



La función BUSCARV puede, en algu os casos, reemplaz r a la fun ión =SI. or ejempl en la Ho ja3 se pid calcular la Bonificac ión de acu erdo a la ategoría el emple do. En la oja1 se m estra la B nificación ara cada ategoría. La fór ula usand la función SI sería:

= I(F7="A ";60;SI( 7="B"; 0;SI(F7 "C";30; 0))) Si usa la función BUSCAR la fórmula puede u icar la cat egoría (cel a F7) en la tabla de Bonificac iones de la Hoja1. O serve la si uiente fór ula:

=BUS ARV(F7;Bonif;2) En este caso, s debe considerar qu el rang Hoja1!A16:B19 tie e el nom re Bonif .

Buscar valores específicos

Cuand usa la función BUSCARV, pu de especi icar que d esea ubic r los valor es exacto en su rango. Para e to sólo es ecesario specificar l último ar umento. Por ej mplo, en la Hoja3, escriba el código A0 15. Obs rve que s muestra el emple do Herrer , a pesar e que dich o empleado tiene el ódigo A01 . Para evi ar esto, l fórmula de la celda 7 será:

= BUSC ARV(D5; Datos; ;Falso) El resultado será hora #N/ ya que di ho código no existe.


43


Código: A015 pellidos:

#N/A

Realic el cambio en las fór ulas definidas en la Hoja3 Permitir entrega de val res aproximados.

Orden do es un valor lógi co que e pecifica i BUSCA V debe localizar u na coincidencia exa ta o aprox imada. Si se omite o es VERD ADERO, devolverá u na coincidencia apr ximada. En otras p labras, si no localiz a ninguna coinciden ia exacta, devolverá el siguient valor más alto inferio r a valor_b uscado. En la oja4, ten mos una t bla que m uestra los descuento por faltas. Por ejem lo para faltas, se debe most rar 30 co o descuen to. Para 1 faltas se debe most ar también 30 y si tu iera 14 fal as será de 50. tc.

En la oja3 se pi e calcular el descuen o para cad a emplead . En este aso, se debe ubicar un valor a roximado d e las Horas falta ya ue en nue tra tabla de descuent os tenem s sólo los l ímites de l s faltas. A í, la fórmul a será:

=BUSC RV(E12;Dsctos ;2;Verdadero) Por ej mplo, escr iba el códi o A001. O bserve qu tiene 10 horas falta, por lo que su descu nto será 3 , recuerde Excel bus a el valor 10 en la t bla y ubica el valor más próximo, en este aso será 6 .

Ac t ividad 02: usar BUS ARV

Nuestra e presa, de icada la di stribución venta de ebidas ref escantes, ha decidido ( como mét do de promoción y vía de inv estigación de merca o) premiar a quellos consumidore que enví n las etiqu etas de los refrescos de dos litros a un determinado apar ado de cor reos. Vamos a uponer qu e nuestros consumid res partici an para los premios de la Promoci ón 2. Haga lo siguient e: 1. Abre u nuevo Li ro de Excel y llámalo remios. 2. La tabla de corre pondencia de premio , que copi rás en el r ango A9: D13 de la Hoja 1, es la siguiente:

44



3. Al cab o de un mes se elabora la lista de lo s primero ganador s, incluyendo los p untos obt nidos por cada un y el pre mio que l es corres onde. Est lista, ant s de intro ucir los p emios conseguidos or los ganadores, pr senta la siguiente ap riencia:

4. Se trata de confeccionar di ha lista, e el rango A1:C7 de la Hoja 1, de modo que el premio onseguido por ca a ganad r aparezca autom ticamente en la tercera columna sólo con i troducir el nº de punt os obteni o. 5. Seleccione el Rango A10:D13 y póngal como no bre Premi s 6. Sitúes en la celd a C2 y acti a el asiste te para fu ciones.


45


7. En Categorías d e funcion es , seleccione Búsq ueda y referencia. Nombr e de la fun ión, selecc iona BUSC ARV y pulsa Ac ept a

n

8. En el cuadro de diálogo: a. En el argu ento Valo r_buscad , seleccio e la celda 2. b. En el argu ento Mat iz_buscar en , escriba Premios c. En el argumento In dicador_c lumnas , escriba 3 (es decir, la tercera col umna de la matriz) d. En el argu ento Ord nado , no s necesari o que intro uzca nad

9. Pulsa ceptar, co ia la fórm la para tod as las celd s necesarias 10. Guard y cierre s trabajo.

46



Fun ión BUSCAR Busca un valor en la fila superior de una tabla o una matriz de valores y, a contin ación, dev uelve un v lor en la isma columna de un a fila espe ificada en la tabla o matriz. Use BUSCA H cuando los valores de compa ración se encuentren en una fil en la part e superior e una tabla de datos y desee e contrar inf rmación q ue se encuentre dentro de un número es ecificado de filas. Us BUSCAR cuando l os valore de comparación se ncuentren en una col umna a la izquierda o de los dat os que desee encont rar. La H d BUSCARH significa "Horizontal". Sintax is BUSC RH(valor buscado ;t abla;indic ador_filas ; ordenado)

es el valor que e busca en la pri era fila de la tab la. Valor_ uscado puede ser un valor, una referencia una cadena de texto. Valor_ buscado

Tabla es una tabla de in ormación en la que se busca los dato . Utilice una

referencia a un ra ngo o el no mbre de un rango. Los valore s de la pri era fila del argument tabla pue en ser texto, número o valores ló icos. Si ordena colocarse VERDAD Si ordena

o es VER DADERO, los valores de la pri era fila d tabla deben en orden ascende te: ...-2, -1, 0, 1, 2, ..., -Z, FALSO, RO; de lo contrario, USCARH uede dev lver un val or incorrec o. o es FALSO, no es n cesario or enar tabla.

El texto en mayúscul s y en minúsculas es equivalent . Ordena lo s valores en orden ascendent e, de izquierda a d recha. P ra obtener m s informa ión, vea O denar dato s. Indica or_filas es el núme ro de fila e n tabla des de el cual ebe devol erse el va lor

coincidente. Si indicador_filas es 1, devuelve el alor de la primera fil en tabla; si indicador_filas es 2, devuelv el valor d la segund a fila en ta la y así sucesivamen e. Si indi ador_filas es menor q ue 1, BUS ARH dev elve el val or de error #¡VALOR!; si indicador_filas es mayor que el número de filas en tabla, BUSCARH devuelve el va lor de err r #¡REF! es n valor lógico que specifica si BUSCARH debe localizar u na coincidencia exa ta o aproximada. Si es VERD DERO o e omite, devolverá u na coincidencia apr ximada. Es decir, si no encue ntra ningu a coincid ncia exac a, devolv rá el sigui ente valor mayor qu sea inferi r a valor_ buscado. i es FALSO, BUSC RH encontrará una c incidencia exacta. Si no encuen ra ninguna , devolverá el valor de error #N/ . Orden do

Consideraciones: •

•

•

Si BU CARH no logra enco trar valor_buscado, u tiliza el mayor valor q ue sea m nor que va lor_buscado. Si valo r_buscado es menor ue el men or valor de la primera fila de tabla, BUSC RH devuelve el valor de error # /A. Si Ordenado es ALSO y v lor_busca o es un v lor de texto, se pueden usar los caracteres comodín de signo d interroga ión (?) y a terisco (*) en el arg mento val r_buscado. El signo de interrog ación corr sponde a un solo c rácter cualquiera y e l asterisco equivale a cualquier ecuencia de


47


caract res. Si lo que dese buscar es un sign de interr gación o un asterisco, escriba una tilde ( ) antes del carácter. Ejempl o:

= BUS ARH(A ; Venta !A5:F1 ;2;Falso ) Esta función busc ará el valor de la celda A5 ( criteri o ) en el ango A5: F10 de la h ja Ventas (tabla). bicado est e valor, s mostrará la fila 2 (i ndicador_ il a) de dicho rango. El último arámetro (falso ) indi a que la t abla no es tá ordenada, por lo que realiza á una bús ueda exac ta. En caso de que el valor no s ea ubicad en el rango A5: F10, se mostra á el mens je de err r: #N/A (n t available) (no disponible) Para probar esta función, s tiene la Hoja5 con l s datos d las cotiza iones en l os último 6 años.

En est misma h ja, en la c lda D9 se debe escribir el año consultar y mostrar la cotización más al ta y baja d dicho año.

En est caso, al r ango de da tos de B3: 6 se le ha asignado el nombre e Cotiza. La fórmul para la celda D11 (c tización más alta) ser á:

= BUSC ARH(D9 ; Cotiza; 2;Falso) Se ha gregado el parámetr FALSO, para que la úsqueda s ea exacta.

48



Ac tivi dad 02: usar BUSCARH

1. Complete la tabla, ubi cando la Cotización

ás baja y el valor del Intervalo.

Su hoja debe quedar co o la figura:

Funci n INDICE Devuelve el valor de un ele ento de u a tabla o de nú ero de fila y de columna.

atriz sele cionado p r los índices

Use la forma matr icial si el pr imer argu ento de INDICE es u a constant matricial. INDIC (rango ;núm_fila;núm columna) Rango es un ra go de celd s o una c nstante de matriz.

Si rango contiene sólo una fila o col umna, el argumento núm_fila o núm_colu na corres ondiente s opcional. Si rango t iene más de una fila y más de u a columna y sólo utili a núm_fil o núm_colu na, INDICE devuelve una matri con toda na fila o c lumna. Núm_f il a selecciona, en el rango, la ila desde l

cual se devolverá u valor. Si se omite úm_fila, s requiere e l argumento núm_col mna. selecciona, en el ran o, la colu na desde la cual se devolverá un valor. i se omite núm_colu na, se req iere el argumento nú _fila. Núm_ olumna

Obser aciones

Si se utili an los arg umentos núm_fila y úm_colum na, INDIC devuelve el valor de la celda don e se produ ce la inters ección de l os argume tos. Si se define núm_fil o núm_ olumna c mo 0 (cer o), INDICE devuelve la matriz de valores d toda la columna o fila, respe tivamente. Para utili ar valores d vueltos co o una m triz, introduzca la fu ción INDI E como una fórmula de matriz en un rango h rizontal d celdas pa ra una fila en un rango vertical de celdas pa a una columna. Para especifica una fórm la de matr iz, presione TRL+MAYÚS+ENTR R. Los argumentos núm fila y núm _columna eben indic ar una cel a incluida en rango; de l o contrario, INDICE d vuelve el alor de err r #¡REF!


49


Por ej mplo, en l Hoja6, s presenta na tabla c mo:

Se necesita mostr ar el núme o en letras. Entonces, la fórmula será:

=INDI E (A3: 17; C6) Siendo un resulta o como:

Mejor r la presenta ión de los resultado Las fu ciones de Búsqueda y Referencia vistas s n muy útil s para crear solucion es como onsultas d e productos, ubicar valores apr ximados, tc. En est sección el manual, veremos cómo mej rar la pres ntación d datos, evi ando men ajes de er or debido a diversos factores c mo celdas en blanco, valores no encontrad s, etc. Funci n ESBLANCO

Esta función devu elve VERDADERO si la celda ev luada está vacía. La intaxis es:

=ESBL NCO (CELDA) Por ej mplo, en la solución creada e la Hoja3, se prese tan mens jes de er or (#N/A) si la celda a buscar ( 5} está va ía.

50



En este caso se debe most rar un me saje “Escriiba un código” si la celda B5 e tá vacía. En caso contrario debe us rle la función BU CARV q e ya te ía imple entada. Así, la fórmul será: =SI(E BLANC (D5);"Es riba un c ódigo";B SCARV( 5;Datos; 2))

Funci n COIN IDIR

Devuelve la posi ión relativ a de un el mento en na lista, que coincid con un valor especi icado en u n orden es pecificado. Utilice COINCIDIR e lugar de las funcion es BUSC R cuando necesite c nocer la p osición d un eleme to en un r ngo en lu ar del ele ento en sí.

=COINCIDIR( valor; ran o; tipoc oincide cia) Por ej mplo, si en la tabla de la Hoja6, i usted es riba la sigu iente fórm la:

=COINCIDIR("O ho";A3: A17; falso) En est ejemplo, l resultad será el nú mero 8 qu es la posi ión donde se encuen ra el text OCHO. S usa Fals para indic ar que ubiq ue el valor exacto en l rango. Funci n ESNO

La fun ión ESNO devuelve VERDADERO si el valor de la c lda se refi re al valor de error #N/A (el val r no disp nible). En el caso de las funcio es BUSC R, este er or se mu stra al no bicarse al elemento en la tabla de búsqued . Por ej mplo, la fó mula para ubicar la c tegoría es:

=BUSC RV(D5; Datos;5) Si ust d escribe un código como 0, esta fórmula muestra l error #N A . Podemos evitar ste mens je de erro mediante la función ESNOD. Lo que har mos es u ar esta función para saber si da como res ltado VER ADERO. i es Verdadero signifi ca que el código n existe, si es falso, dicho códi o existe debe buscarse con la fórmul planteada. La fórmul será como: =SI(ESNO (BUSCARV(D5;Datos; ));"No Ubi ado";BUS ARV(D5;D tos;5))

Búsq eda por eferencia cruzada

Este ti o de búsqueda ubic un valor n base a u posición de fila y c lumna de un rango de datos. Se usarán las funciones INDIC y COIN IDIR para poder ubi ar este v lor en una tabla En nu stro ejem lo, se tie en la Hoj 7 con un tabla qu muestra los contratos tramitados por dis trito


51


En la arte inferio r de la misma hoja, s desea ob tener el nú ero de contratos de un Distrito en un me determina do.

Como notará, se debe ubi ar el valo de acuer do a 2 en tradas (Di trito y Mes). Considere la sigui ente fórmul a:

=COINCIDIR(C1 ;A3:A8; FALSO) Esta f rmula dev elve el nú ero de fil en que s encuentra el Distrito escrito en la celda 11. En este caso dev elve 2. Esta otra fórmula:

=COINCIDIR(C1 ;A3:E3; FALSO) …devuelve el nú ero de c lumna en ue se en uentra el Mes escrit en la celda C12. En este cas devuelve . Así, u ando esto 2 valores junto a la función I DICE, la f órmula par a devolver el númer de contra os será: =INDI E(A3:E8; OINCIDIR C11;A3:A ;FALSO); COINCIDI (C12;A3: 3;FALSO))

Funci n ELEGI

Utiliza el argumen to núm_ín ice para d valore . Utilice E EGIR para seleccion rango del argum ento índic . Por eje semana, ELEGIR devuelve no de los como rgumento úm_índic .

volver un alor de un r uno de l s 254 val plo, si valor1 a val días cuan o se utiliz

lista de a gumentos de res posibl s a partir el r7 son los días de la un númer o entre 1 7

Sintax is ELEGIR(núm_ín ic e;valor1;valor2;...)

52



es ecifica el argument de valor que se s lecciona. l argume to núm_í dice debe ser un nú ero entre 1 y 254, o bien, una fó rmula o ref rencia a u na celda ue conten a un número entre 1 254.

Núm_índice

Si núm_ín ice es 1, LEGIR de uelve valo 1; si es 2, LEGIR devuelve val r2 y así suce ivamente. Si núm_índice es menor que 1 o mayor q e el núme ro del últi o valor de la lista, ELE IR devuel e el valor e error #¡V ALOR!. Si núm_índice es u a fracció , se trunca al enter o inferior utilizada.

ntes de er

Valor1,valor2,... son de 1 254 argumentos de valores en tre los cua es la función

ELEGI seleccio a un valo r o acción que se ej cuta basándose en l argume to núm_í dice. Los argumentos pueden ser númer s, referencias a cel as, nombr es definid s, fórmulas, funcione o texto. Obser aciones

Si núm_í dice es una matriz, cada val r se eval ará cuando se eval úe ELEGIR. Los argu entos de alor para LEGIR pueden ser r ferencias e rango, sí como valores individu ales. Por ej mplo, la fó mula: =SUM (ELEGIR( ;A1:A10;B1:B10;C1: 10)) evalúa:: =SUM (B1:B10) que después dev elve un val or basado en los valo es del ran o B1:B10. La fun ión ELEGIR se evalúa primero, devolviend o la referencia B1:B10. La función SUMA se evalúa a continua ión utiliza do B1:B10, que es el resultado de la funci ón ELEGI , como su argument .

Valid ción d datos en cel as La sección anteri r se encar go de cont olar los di ersos erro es que pu den surgir al usar funciones B SCAR. Otr a estrategi para evit r esos me sajes de error sería q ue se con role el ingr eso de dat s a busca , por ejem lo que se i ngrese sól códigos q ue Programa Nacional de Informátic

53


existan en su tabla, o se limite el ingreso a núme os entre un determin do interva lo, etc. Valid ción por Listas

Por ej mplo, se ide que en la celda D5 se limite el ingreso de datos a códigos q ue existan en nuest a tabla de emplead s. Para realizar esta acción, d be seguir el procedimiento en el libro Bol tas de pago: 1. Seleccione el ran go de los ódigos de la Hoja1 (A3:A12) asígnele un nombr de rango. Para nues ro ejemplo , se llamar CODIGO . 2. Luego, lleve el ind icador a la celda D5 de la Hoja3. 3. Elija la ficha Dat os , en el rupo Herr amientas de datos Haga clic en Valida ión de da os…

4. Elija d la lista Va lidación de datos…

5. En est cuadro:

6. En la lista Permitir : elija LIST 7. En Origen: escri a el sign IGUAL ( ) y el no bre del r ngo definido anteriormente. En nuestro caso escriba =CODIGO . 8. Haga lic en Acep tar.

54



9. Ahora, en la celd a D5 se muestra una lista para seleccionar el código a ubicar.

Quitar una validación

Si usted desea qu e su celda ya no teng opciones de validaci n, sólo de e: 1. Seleccione la celd a 2. Elija la ficha Dat os , en el rupo Herr amientas de datos Haga clic en Valida ión de da os…

3. En el cuadro Per itir elija C alquier v lo r 4. Haga lic en Acep tar. Valid ción por intervalo de números,

Otra f rma de v lidar el ingreso de atos es a través d controlar que sólo se ingres n valores entre un ra ngo de números. Por ejemplo, n la Hoja , los valor es permitidos para l celda D9 van de 19 8 a 2003. Realice lo s siguientes pasos p ra aplicar este tipo de validació : 1. Lleve l indicador a la celda 9 de la H ja5 2. Elija la ficha Dat os , en el rupo Herr amientas de datos Haga clic en Valida ión de da os …

3. En la lista Permitir : elija Número Entero 4. En Dat os : elija E tr e 5. Como alor Míni o: escriba 1998. Como valor Má imo: escriba 2003.


55


6. Haga lic en Ac e tar . 7. Para probar esta opción, es riba un va lor fuera d l intervalo definido. or ejempl , escriba 2005, para ver los v lores escr itos no validos haga lo siguiente: a. En la ficha Datos, en el grupo H erramientas de datos, haga clic en Validación de datos y elija: Rode ar con círc lo datos n válidos

b. Se muestr

Mensa jes de Ing eso y de

rror.

Inclus , puede d finir un m nsaje de ntrada (m nsaje que se muest a al llevar el indicador sobre la celda vali ada) o un mensaje d error (mensaje pers nalizado q ue se mu stra al ingr esar un val or fuera del rango defi nido). Para d finir un m nsaje entr nte.

56



1. Lleve l indicador a la celda definir. E nuestro c so a la cel a D9 2. Elija l ficha Datos , gru o Herram ientas d datos haga clic en Valida ión de da os … 3. En la ficha Mensaje ent rante, es riba com Cotiza iones. 4. En el ensaje es riba: Escriba el año a consultar.

Título: Consulta de

5. Haga lic en Ac e tar . Si llev el indicad r a la celd D9 obser e el mens je:

Para d finir un m nsaje de e rror. 1. 2. 3. 4. 5.

Lleve el in icador a la celda a d finir. En nu estro caso a la celda 9 Haga clic n Validaci ón de dato s… En la ficha Mensaje e error , e criba com Título: Consulta de otizacione . En el Men saje escrib : Error en el ingreso e datos. Valor no per itido. Haga clic n Ac ept ar .


57


1.

En la celd D9 escrib un valor f uera del int ervalo. Por ejemplo e criba 2005 .

Preg ntas e Rep so 1. ¿Qué dif rencias h y entre sar VER ADERO argument de las funciones BU CAR?

FALSO en el últi o

2. ¿Qué valor devolverá la función =BUSCA V("AZ35"; 2:B6;2;FA SO)? Si u sa =BUSCA V("AZ35"; 2:B6;2;VE DADERO) que val r será ah ra? (Use la siguiente t abla)

3. En el libro Costos de Producci n se tiene la tablas:

58



Hoja1

oja2

Considera do las tab las de la fi ura anteri r, en la Ho ja2 se desea hacer u na consulta d acuerdo las siguie tes especi icaciones: El ódigo y la unidades a producir on ingresados por te lado La Descripció y el Porcentaje deben ser bus cados de la Hoja1. La bú queda debe ser de valores exactos. De la semana 2 a la 4 se increment rá de acu rdo al Por entaje. Par a Hallar el Costo Pr ducción e debe multiplicar el Total (celda G11) por el C sto Unid d (valor que debe ser buscado d la Hoja1) 4. Valide la t bla de acu erdo a las iguientes specificaciones: La Descri ción y el Porcentaje nunca d ben mostr ar errores e tipo #N/ A. Use las fu ciones ES LANCO, SNOD y o ras que co nsidere. Las unida es sólo de en aceptar valores e teros entr 250 y 55 unidades. El código e debe mostrar en una lista.

Finalment , personali ce los mensajes entrantes y de rror para l celda de l as Unidades . 5. Inserta un nueva ho a (Hoja 2) en el Libro Premios. rea en ell el siguie te modelo de pedido (ra go A1:D1 ):


59


H RMA OS L PEZ Jr. De a Unión Cercad de Lima PEDIDO Nº

FECHA:

Cód. destin tario

Destin atario:

CONDICIONES Forma envío

Plazo ntrega

Forma pago

Lugar ntrega

Ca tidad

Art ículo

Prec io unit.

Impo rte total

6. En la mis a hoja, más abajo, cr a la siguie nte tabla d correspondencias: Cód igo Destinatario destin tario T32

Talleres Ramírez

A 7 N12

Forma envío

For ma pa go

Plazo ntrega

Lugar entrega

Aéreo

Al contado

24 hs.

Fábrica

Mayoristas Centrales

Camió

Aplaz do (30 d. vta.)

3 días

Almacén

El edal, SL

Tren

Al contado

2 días

Almacén

A c ntinuación , en las celdas del m delo de p dido corre spondiente s a los dat os de Destinatario, Forma envío, Fo ma pago, Plazo en trega y L gar entrega intr duce funciones BUS ARV de f rma que al escribir e l código d l destinata rio apa ezcan aut máticame te los datos correspo dientes a icho código.

60


Capí ulo

Fil ros van ad s, H rra ien as de dat s y squ mas En este capítulo apren eremos a: rear filtros avanzado sar herra ientas de atos sar esquemas

Programa


61


Filtr s ava zados A pes r de que sean muy útiles y sen illos de m nejo los filtros auto áticos, están limitad s a sólo uno o dos riterios po cada campo. Por esto, se tien n los Filtr s avanz dos cuyo manejo es similar al de los filtr os automáticos pero con mayor es posibilidades de riterios. Los criterios se definen en la mis a hoja de cálculo. Los criterios de filtro avanzado pueden in luir varias condicion s aplicadas a una s la colum a, varios riterios aplicados a varias colu nas y co diciones creadas co o resultado de una órmula. Para usar criterios avanzad s, debe tener un Rango de la lista, don e aparece la referencia de las celdas que contienen la lista. xcel automáticamente detecta el rango e la lista cuando está el cursor olocado e ella, pero con un clic en la caja se puede escribir un nuevo ran o o cambi r el actual. También e puede u ilizar el ratón y hace un arrastr e para mar ar el área de trabajo. Además es necesario un Rango de celdas donde se etermine l criterio, los ca pos de su lista y por lo menos se est n buscan o. Con los criterios comodines.

riterios .

ste rango debe ser otra área de ebe ser u área con los nombr s exactos de na fila que contenga los criterio escritos que se pueden utilizar si nos de c mparación y

A continuación abra el libro Listas, en la hoja1 se

uestra:

Ubíqu se en la H ja3 se mu stra un ej mplo de u rango de riterios:

62


Capí tulo IV: Filtr os avanzad s, Herrami ntas de datos y Esque as

Criterios en un column .

ctividad 01: Usar cr i erios en u na column a

En el Libr o Listas, s necesita tener una relación d los pro uctos de las categorías: Lácteos, Pescados / Mariscos y de Carnes. No se pueden u ar autofiltros a que se ecesitan ás de 3 criterios en el mismo ca po. Para este caso, se saran filtr s avanzados y crear emos nue tro rango de criterios e una colu na de la si uiente ma era: 1.

Ubíqu se en la oja3 y es riba el ran o de criterios como e muestra a continuación:

2. 3.

Luego, lleve el in icador so re alguna elda de su lista en la oja1 Use la ficha Dato s, bajo el grupo Orde ar y filtrar aga clic e Avanzadas

4.

Excel detecta automáticamente el Ran o de Lista mostrando lo siguient :


63


64

5.

En el Rango de riterios debe selecci nar el ran o definido en el pas 1 (C3:C )

6.

Obser e en el cuadro de di logo Filtr avanzad que si lo desea puede indica que el resultado de aplicar este iltro se co iará en otr lugar.

7. 8. 9.

Haga lic en Ac eptar Obser e que ahora la lista uestra los productos e esas 3 categorías Para er la lista completa, use la fic a Datos en el grup Ordenar y Filtrar haga clic n Borrar .



Criterios en un Fila

Ac tivi dad 02: usar Criterios en un a fila

En el Libro istas, se necesita o tener los productos de la cate oría Carn es cu o stock sea mayor de 80 uni ades y lo Lácteos con stock menor a 30 unidades. En este caso: 1. Proceda a estable er un rango de crit rios en la hoja3 de la siguie te manera:

2. 3.

Luego lleve el indicador sobre lguna celda de la lista de la hoja Haga clic en Av anz das de la icha Datos .

4.

En esta ventana observe que el ran o de la lista se a detectado automáti amente. En el ra go de criterios debe seleccion r el rang definido n el paso 1 (C3:D5)

5.

6.

Su venta a debe quedar com lo muestra la imagen siguiente:


65


7.

Haga clic en Ac ept ar para apli ar el criterio.

Criterios que incluyan fó mulas En ciertos casos se necesitan usar f órmulas c mo criteri s para o tener ciertos registr s. Por ejemplo ver l s product s cuyo Stock se e cuentra p r debajo el prome io o aquellos product s que em iecen con la letra C. Cuand definimo un criterio a través de una fórmula no deb mos utiliz r el rótulo de la colu na como lo hicimos n los casos anteriore , sino dej r esa celd vacía y u ar ambas celdas co o rango d criterios.

Ac ti vi dad 03: usar Criterios qu e incluyan fórmulas

Se desea mostrar los registros cuya longitud del n mbre sea de hasta 15 caracteres. En este c so: 1. En la oja3 del li ro Listas , escriba en la celda B2 la siguien e fórmula:

=L RGO(B )<15 Se usa la celda B4 porque es la prim ra celda ue contiene el Nomb re del P oducto . L función L ARGO obtiene la longitud de dicha celda. El result do de est fórmula nos muestra un valor ERDADE O o FALSO, dependiendo de la evaluaci n hecha e la celda B 4. 2. Su ran o de criter ios debe q edar como:

3. Luego, lleve el indicador sob e alguna c lda de su llista.

66



4. Use la ficha Dato y haga clic en Avan adas

5. En esta ventana, observe que el Rango de la lista se a detectado autom ticamente. 6. En el ango de cr iterios, debe seleccionar el rango definido ( 1:B2).

7. Haga clic en Ac e tar 8. Obser e en resultado al apli ar este crit rio

Otro ejemplo de criterios de r angos, ser á por ejem lo para m strar los pr oductos cuyo Stock este por debajo del pr medio. En este caso, la fórmula empleada erá:

=F4

67


Aquí se usa la unción P OMEDIO para calcular el pro edio del Stock (rango F4:F80), Se eval a si la cel a F4 (pri era fila del Stock) es mayor qu el prome io obteni o.

Obte er valores únicos Filtrar alores únicos y quit r valores duplicados son dos t reas muy relacionadas porque los result dos mostr dos son l s mismos: una lista de valores únicos . in embar o, la dif rencia es important : cuando filtra por valores únicos, oculta tempor almente los valores duplicados ero cuando quita val res duplicados, elimina los val res duplic dos de manera perm nente. Un valor duplicado es aq él en el que todos los valor s de la f ila coinciden exacta ente co todos l s valore de otra. Los val res duplicados están determinados po el valor mostrado en la cel a y no necesariam nte el valor guardado. Por ejemplo, si tiene el mismo valor de echa en c ldas difere tes, una con format "8/3/2006" y la otra como "8 de marzo de 2006", los v lores son nicos. Es una buena ide Filtrar valores único antes de onfirmar q e los resultados son los desea os antes de quitar valores duplicados Proceda de la sig iente man ra para obtener valor s únicos en una lista: 1. Seleccion el rango de celdas o asegúrese de encuentra en una tabla. 2. En la ficha Datos , avanzada .

n el grup

Ordenar y filtrar , haga clic

3. En el cuadro de diálogo Filtro avanzado , siga un siguientes::

68

ue la cel a activa se n Opcion es

de los pr ocedimientos



a) Pa a filtrar el rango de las celdas o la tabla colocada, haga clic en Filt rar la lista sin mover la a otro l u gar . b) Pa a copiar lo resultado del filtro a otra ubicación, haga lo siguiente: 1. Haga lic en Copi ar a otro l gar . 2. En el cuadro Cop iar a, escri a una referencia de celda. Otr a alternati a es hac r clic en ontraer d iálogo para ocul ar te poralment el cuadro de diálog , seleccio ar una cel a de la h ja de cálculo y, a continuación, presionar Expandi r diálogo

.

4. Active la casilla de verificación Sólo registr os únicos y haga clic en Ac ept ar . Los valores únicos d l rango sel ccionado e copian en la nueva ubicación.

Uso e las erram entas e dat s Texto en columnas Utilice el Asistente para co vertir text en columnas para eparar el contenido de celdas simples, c mo nombr s y apellidos, en colu nas distintas.

Según los datos, uede dividir el contenido de las eldas en f nción de un delimitad r, como n espacio o una com , o en función de la u icación es ecífica de un salto d colum a en los d tos.


69


Ac t ividad 04: Dividir en olumnas cuando el delimitado r es el esp acio

Para r alizar estos pasos, copie los dat s de la im gen siguientes en un hoja de cálculo en blanco

1. Seleccion el rango de datos qu desea co vertir. 2. En la ficha Datos , en el grupo H rramientas de datos , haga clic en Texto en columnas .

3. En el paso 1 del Asistente para onvertir te to en colu nas, haga clic en Delimitad s y luego n Siguiente . 4. En el paso 2, active l casilla de verificació Espacio desactive las demás casillas ba jo Delimitadores .

70



En el cuadro Vista pr evia de los datos se uestra el nombre y el apellido en dos colum as distintas. 5. Haga clic n Siguient e. 6. En el paso 3, haga clic en una c lumna en l cuadro Vista previa de los datos y lu go en Tex to bajo Fo mato de l s datos e columna . Repita est paso par cada colu na del cu dro Vista previa de l os datos . 7. Si desea i sertar el c ntenido separado en las column s junto al ombre completo, haga clic e el icono situado a la derecha d l cuadro D stino y, a continuaci n, selecci ne la celd colocada junto al no bre en la l sta (B2, en este ejem lo).

Si no especifica un n evo destin para las uevas columnas, los atos divididos r emplazarán los datos originales. 8. Haga clic n el icono situado a l derecha del As is ten t e para co vertir text o en colum as .

9. Haga clic n Finaliza .

Quitar valores uplicad s Cuand quita val res duplic dos, sólo se afecta a los valores del rang de celda o tabla. o se modi ica ni se mueve ningún otro valor fuera del r ango de celdas o tabl . Debid a que bor ra los dato de mane a perman nte, es bu na idea c piar el rango original de celda o tabla e otra hoja de cálculo o libro antes de quit r los valor es duplicados. Proceda de la sig iente man ra para Q itar valore duplicados en una Lista: 1. Seleccion el rango de celdas o asegúrese de que la celda activa se encuentra en una tabla. 2. En la ficha Datos , en el grupo H rramientas de datos , haga clic en Quitar duplicado s .


71


3. Siga uno

varios de los procedi ientos siguientes:

En Columnas , seleccion una o más columna . Pa a seleccio ar rápida ente todas las columnas, haga clic en Sel eccionar t od o . Pa a borrar rápidamente todas las c lumnas, haga clic en An ul ar  sel ección . Si el rango de cel as o tabla contiene muchas columnas y sól desea seleccionar algun s, le será ás fácil h cer clic en An ul ar s el ección y, contin ación, en olumnas , seleccionar dichas columnas. 4. Haga clic n Ac ept ar . Aparece u mensaje indicándole cuántos v lores duplicados se h n quitado cuántos valores únicos permanecen o si no se han qui ado valores duplicad s. 5. Haga clic n Ac ept ar .  

Valid ción de atos En m chas hoj s de cálculo que cree, los usuari s escribir án datos para obtener los resultados y cál ulos que desee. A egurar la entrad válida de datos es na tarea i portante. Puede que desee restringir la entrada e datos a un ra go deter inado de fechas, limitar las opcion s emplea do una li ta o asegurarse de que s lo se entr an númer s enteros positivos. Tambi n es esencial propor ionar una ayuda inmediata par dar instrucciones a los usuari s y mens jes claros cuando se escriban datos no v lidos para hacer que el proceso de entrada de datos se lleve a abo sin pr blemas. Una v z que de ida qué v lidación desea utiliz r en una hoja de c lculo, puede config rarla haciendo lo siguiente: 1. Seleccion una o má celdas para validar. 2. En la ficha Datos , en el grupo H rramientas de datos , haga clic en Validació de datos .

72



Se muestr el cuadro de diálogo Validació de datos .

3. Haga clic n la ficha onfiguració n . 4. Para especificar el tipo de validación que d sea, realice una de las siguiente acciones: Permitir valores de una lista Permitir un número entero de tro de límites Permitir un número decimal d ntro de límites Permitir una fecha dentro de un período e tiempo Permitir una hora dentro de u período d tiempo Permitir texto de u a longitud específica Calcular qué se pe mite segú el conteni o de otra elda Utilizar na fórmula para calc lar lo que e permite 5. Para especificar cóm desea ad inistrar lo valores e blanco (n los), activ o desactiv la casilla e verificación Omitir lancos. Si los val res permitidos se b san en un rango de celdas co un nom re definido y existe una celda en blanco en cualquier lugar el rango, la configuración de la casilla de verificació Omitir b lancos permite escribir cualquier alor en la celda valid da. Lo mi mo puede decirse d las celda a las que se haga refer encia mediante fórmulas de vali ación: si u a celda e tá en blanco, la configur ación de la casilla de erificación Omitir blancos permite escribir cu lquier valor en la celda validada.


73


6. Otra opci n es mostrar un me saje de entrada cuando se haga clic en la celda. 1. Ha a clic en l ficha Men saje de en trada. 2. As gúrese d que la asilla de erificación Mostrar mensaje ent rada al sel eccionar l a celda está activada.

e

3. Rellene el título y el texto del mensa e. 7. Especifique cómo d sea que Microsoft especifiqu n datos n válidos.

ffice Exc l respond

cuando se

1. Ha a clic en la ficha ensaje d error y segúrese de que e té activada la c silla de v rificación Mostrar m ensaje de error si se es riben dato s no válid os . 2. Seleccione una de las si uientes op iones en l cuadro E tilo : 

Para mostrar un mensaj informativo que no evite la especi icación de datos no v lidos, seleccione Info rmación .



Para ostrar un mensaje de advertencia que no evite la especi icación de datos no v lidos, seleccione Ad v ertencia.



Para vitar la especificació de datos no válidos, seleccione Deten r .

3. Es riba el título y el texto del mensa e (máximo 225 caracteres). Si o escribe n título o un texto, de forma predeterminad el título s rá "Microsoft Ex el" y el m nsaje ser : "Valor n válido. El usuario s lo pu de escribir ciertos val res en est celda". 8. Pruebe la alidación e datos para asegurarse de que funciona c rrectamen e. Trate de escribir dat s válidos y no válido en las celdas para asegurarse de que la configuración funciona como pretende y q e los mensajes están aparecien o como espera. Si realiza cambio a la validación en u a celda, puede aplic rlos automáticament a todas l s demás eldas que tengan la isma confi uración.

Consolidar Para r esumir y registrar r sultados e hojas e cálculo independi ntes, puede consolidar datos de cada u a de esta hojas en una hoja e cálculo maestra. Las hojas ueden est r en el mismo libro q e la hoja de cálculo aestra o e otros libr s. Al consolidar dat s, lo que se hace s ensamblarlos de odo que ea más fácil actuali arlos y ag egarlos de una forma periódica o específica. Por ej oficina hoja d totales vendid

mplo, si ti ne una h ja de cálc lo de cifras de gastos para cada una de las regionales, podría utilizar una onsolidaci n para resumir estas cifras en una cálculo d gastos c rporativa. Esta hoja e cálculo aestra podría conte er de ventas y promedios, nivele de inventario actual s y los pr oductos más s de toda la organización.

Para consolidar atos, utilice el coma do Cons lidar del datos e la ficha atos .

74

rupo Herr amientas

e



1. Configure los datos que se va a consolidar en ca a una de las hojas de cálculo independient s. 

As gúrese de que cada rango de datos está n formato e lista: cada columna tiene un rótulo n la primera fila, con iene hech s similare y no tiene filas columnas en blanco.



Coloque cada rango en una hoja de cálculo dif rente. No onga ningún rango en la h ja de cálculo donde v ya a colocar la consolidación.



As gúrese de que cada r ango tiene el mismo diseño.



Asigne un no bre a cada rango: s leccione todo el rang y, luego, en la icha Fórm ulas , en el grupo Cel das con ombre, h ga clic en la fle ha situad junto a signar n mbre a n rango escriba un no bre para l rango en el cuadro ombre.

2. Haga clic n la celda superior izquierda del área don e desee q e aparezcan los datos onsolidados en la hoj de cálcul maestra. Asegúrese de dejar suficientes eldas a la erecha y or debajo e esta celda para los d tos de consolidación. El comando Consoli ar rellena l área según proceda. 3. En la fic a Datos , en el grupo Herra ientas d Consolid r .

datos ,

aga clic en

4. En el cua ro Funció n , haga clic en la función de r sumen que desea que utilice Micr osoft Offic Excel par consolidar los datos.


75


5. Si la hoja de cálculo se encuentra en otro libro, hag clic en E aminar p ra buscar el rchivo y, a continuaci n, haga clic en Ac ep ar para cerrar el cua ro de diálogo Examinar . La ruta de acceso del archivo s escribe e el cuadro Referenci a seguido de un signo de exclamación. 6. Escriba el nombre que le asignó al rango y, a co tinuación, haga clic en Ag reg ar . epita este paso para cada rang . 7. Decida cómo des a actuali ar procedimi ntos sigui ntes:

la

c nsolidació .

Siga

no

de

los



Pa a configurar la consolidación de manera que se actualice automáticamente cuand cambien l s datos d origen, a tive la casilla de verificación Crear vín ulos con l os datos e origen .



Solamente p ede activ r esta ca illa de verificación i la hoja de cálculo se e cuentra e otro libr . Cuando active es a casilla de ver ificación y no podr cambiar las celda y los ra gos que se incluyen en la consolidación.



Pa a configur ar la con olidación de modo que pued actualizarla manualmente cambiando las celdas y los rang s incluidos, desactive la ca illa de verif icación Cr ar vínculo s con los atos de o igen .

8. Deje en blanco las c sillas de Usar rótulo s en . Excel no copia los rótulos de fila o columna de lo rangos d origen a la consoli ación. Si esea rótulos para los atos consolidados, ópielos d uno de los rangos de origen o introdúzcalos manual ente.

ctividad 5: Consol idar Datos

La Empre a Frutales S.A , elab ra mensualmente una planilla en la que an ta su produc ión, se desea elabor r un cuad o consolidado del primer trimes re de produc ión. Para r ealizar est actividad bra el arc ivo FrutalesSA. El archivo FrutalesS cuenta con 3 hojas on datos (Enero, Fe rero, Marz ), en cada una de estas hojas se ha creado Rangos c n nombre que definen los rangos de celdas a consolidar, por eje plo en la Hoja Ener el rango de celdas A1:B8 tiene el nombre En ero .

1. Ha a clic en l Celda A1 de la Resu men 2. En la ficha Datos en el grupo Herramientas de datos haga clic en Co solidar

76



3. En el cuadro de diálogo que se a re; en Fu ción elija Suma y b jo Re erencias scriba En ro y pulse l botón Ag regar . Agr gue Febr ro y arzo de la misma for a, finalme te el cuad o debe mostrarse así:

4. Active las casillas de veri icación Fil Superior y Column izquierd .

5. As gúrese que la casilla de verifica ión Crear vínculos c on los dat s de origen se ncuentra esmarcad . 6. Pulse Aceptar


77


Análisis Y si Es un proceso e cambio de los valores de c ldas para ver cómo afectan esos cambi s al result do de fór ulas de la hoja de cálculo. Por e jemplo, var iar la tasa de interés que se utiliza en una tabla de amortizació para determinar el i porte de los pagos.

Escen rios Un es enario es un conjunt de valor s que Micr osoft Offic Excel gu rda y puede sustituir automáti amente e la hoja de cálculo. Puede utilizar los es enarios p ra prever el resultad de un modelo de hoja de cálcul . Puede c ear y guar ar diferentes grupos de valore en una hoja de cál ulo y, a c ntinuación, pasar a ualquiera de estos uevos esc narios par ver distin os resultados. Los e cenarios f orman par te de una serie de comandos a veces denominados herramientas de análisis Y s i. Por ej mplo, ust d podría u ilizar un escenario si quisiera preparar un presupues o, pero no supiera on exactit d sus ingr esos. En este caso, on un esc nario pod ía definir valores diferentes ara dichos ingresos y, segui amente, asar de un escen rio a otro para realiza un análisi Y si.

En el jemplo anterior, cuyo escenario podría denominarse eor opció , establezca el valo de la celd B1 como 50.000 $ y el de la celda B2 com 13.200 $.

Al seg ndo escenario podría denominarlo Mejor o pción y cambiar los alores de 1 a 150. 00 $ y de 2 a 26.00 $.

Crear un escen rio 1. En la ficha Datos , en el grupo erramient s de dato s , haga cli en An áli is Y si y, después, en A dministra or de esc narios .

2. Haga clic n Ag reg ar .

78



3. En el cuadro Nombre del escen ario , escri a un nombre para el scenario.

4. En el cua ro Celdas cambiant es , especi ique las r ferencias que desee cambiar.

e las celdas

Para preservar los alores originales de las celdas cambiantes, cree un escenario que utilic los valor es originales de las celdas antes de cr ar escenario que cambien los valores. 5. En Proteg er , selecci ne las opciones que esee. 6. Haga clic n Ac ept ar .


79


7. En el cua ro de diálogo Valor s del esc nario , intr oduzca lo valores que desee par las celda cambiant s. 8. Para crear el escenar io, haga clic en Ac ept ar . 9. Si desea crear escenarios adici nales, repi a los pasos 2 a 8. Cuando termine de crear l s escenar ios, haga lic en Ac eptar y en Cerrar en el cuadro de diálogo Ad ministrad r de esce arios . Ac t ividad 06: Usar esce arios

Queremos ha er un seg imiento d las venta de un determinado producto a lo lar o del prim r semestr , compara do el importe de las ventas de mes en mes. En este caso, existe una serie de fa tores que o varían:  El no bre del artículo  El pre io unitario del artículo  El Impuesto IGV  El descuento co ercial apli ado en la enta.

En cambio, existen dos f ctores que sí varían:  El me analizado  La ca tidad de unidades vendidas del artículo: d este fact r depende el

result do final (el importe de las ventas del mes). La pregunta quí es: ¿cuál será el importe d las ventas si cambia la cantidad ve dida? 1. Abra un nuevo libr o de Exc l y digite los datos que a co tinuación se muestran en la siguiente imagen:

5. Seleccion la ficha atos en el grupo He ramientas de datos haga clic en An áli si s si y luego en Ad m inistrador de Escen arios . En el cuadro de diálogo que aparezca, pulse Ag r egar. 6. En el cuadro de texto Nombre d el escenar io escriba nero. 7. En el cuadro de texto celdas ca biantes, selecciona, en primer l gar, la celda B3; luego, pulsando la tecla Co trol, selec iona la celda A1 (observa que, or defecto, xcel con idera las celdas omo referencias a solutas);. A continuaci n, pulsa A ceptar .

80



8. En el cua ro de diálogo que ap rece, nos olicita que introduzcamos un valor para las eldas ca biantes ( 1 y B3): aparecen or defect los valor es correspon ientes al es de ene o, es decir, Enero y 3. Pulsa Ac ptar .

9. Repite la peración para los sig escenario; cuando s te pidan escribe, para A1, l s nombre escenario) y, para B3, valores di

ientes cinco meses, cambiando l nombre el alores par las celdas cambiantes (A1 y 3) de los iferentes eses (uno para cada tintos de 3.

10. Una vez reado el escenario para Juni aparecerá los nombres de los seis esce ellos, sel cciona el escenario que qui Finalment , pulsa C rrar . A p rtir de ah momento cualquier escenario, agregar existentes.

, en el c adro de iálogo inicial arios creados. Para lternar en re ras cons ltar y pulsa Mostr r . ra, podrá consultar en cualquier tros nuevos o modificar los ya

11. Guarde el ejercicio c n el nombr e Escenari os .


81


Buscar objetiv Si con ce el resultado que esea de una fórmula, pero no el valor de entrada que necesita la fórmula para o tener dich resultad , puede u ilizar la función Bus ar objetiv . Por eje plo, utilic Buscar objetivo par cambiar el tipo de interés de la celda 3 hasta que el valor del pago de B4 sea igual a 900,00 $.

1. En la ficha Datos , en el grupo erramient s de dato s , haga cli en An áli is Y si y, después, en B uscar objetivo . 2. En el cua ro Definir la celda, scriba la r ferencia de la celda ue contenga la fórmula que desee resolver. ( n el ejemplo esta es la celda B4.) 3. En el cuadro Con el alor , esp cifique el r sultado q e desee. ( n el ejem lo sería -900.) 4. En el cuadro Cambi ando la elda, indi ue la referencia de la celda que contenga l valor que desee aju tar. (En el ejemplo es a es la cel a B3.) A esta celda debe hacer referencia la fórmula e la celda especificada del cua ro Definir la celda

Tabla de datos Una tabla de dat s es un r ngo de c ldas que uestra có o afecta el cambio de algunos valores de las fórm las a los r sultados e las mis as. Las ta las de datos constit yen un étodo abreviado p ra calcular varias ersiones n una s la operación, así c mo una anera de ver y comparar los esultados de todas las variaci nes distintas en la ho a de cálculo. Puede crear tabl s de datos de una variantes que desee probar.

dos variables, dependiendo d l número de

Tablas de datos de una var iable

Utilice una tabla de datos de una variable si desea ver e qué manera afectan distint s tipos de interés al p go mensual de una hipoteca. E el siguien e ejemplo, la celda 2 contien la fórmula de pago, =PAGO(B3/12;B4;-B5), que hac referencia a la celd de entrada B3.

82



Tabla e datos d e dos vari bles

Una ta interés celda variabl

la de datos de dos v riables pu de mostrar cómo afe tan los distintos tipos de y plazos el présta o al pago de una hi oteca. En el siguiente ejemplo, la 2 contien la fórmul de pago, =PAGO(B /12;B4;-B5), que utili a dos celdas s, B3 y B4.

Supón que has cr eado en E cel un mo elo de fac ura que in luye la aplicación de un tipo de descuento comercial del 3% y q e te interesa saber c ál sería el importe de la factura en caso d aplicar otr os tipos de descuento diferentes: 2%, 4%, 5%... De acuerdo con lo visto de Excel hasta el momento se nos posibilidades:

lantean dos

En el m delo de factura crea o, ir sustit yendo el ipo de de cuento en la celda do de hemos introducido este dato anotar lo nuevos re ultados. Crear un escenario iferente p ra cada nuevo tipo de descuento. Existe, no obstan e, una ter era posibi lidad , útil uando no interesa contemplar de una ve todos los posibles resultados: l creación e tablas . Las ta las permit n conocer la influencia de una dos varia les (dos c mo máxi o) sobre na fórmul dada, y studiar los diferentes resultados de esa fór mula cuando dichas variables t man una erie de val res concr tos. En el jemplo pr puesto, la variable s rá el tipo de descuento y la fó mula, la que calcula el importe total de la actura. Un ejemplo más interesante será el que utilizare os para mostrar el funcionamie to de las ablas en Excel.

Ac ti vidad 06: sar Tablas de datos

Vamos a confec ionar una tabla que calcule el importe los pagos mensuales a realiza en la dev lución de n préstam , variando el tipo de i terés. Para ello, el primer paso s rá crear u a hoja de cálculo q e resuelva el proble a básico: calcular el importe d los pagos mensuale , conocien o: el tipo e interés , la cantid d prestad a y el plaz de devol ción del préstamo. 1.

bre el libro de Excel


alculador de Prest mo s :

83


2.

n la celda B7 se ha utilizado la f unción PA O, que in luye vario argumentos necesarios alguno o cional. Centrándonos en los argumentos necesarios, su estructura es la siguiente: PAGO(tasa de interés por period ; nº de peri odos de pa go; cantida prestada) Ta a

Nper

Va

En nue tro caso, esto se tradu e en la siguie te funci n: =PAGO(B /12;B5*12;B3). El r esultado de esta función será un núm ro negativo; Excel lo onsidera egativo por tratarse de devolución de un préstamo. 3.

4.

84

n la celda B11 se ha introducid la siguie te fórmula:: =B7. Con esta fórm la i dicamos que el cont nido de l celda B11 ha de ser el mismo que el de la celda B7. O ra posibilidad sería reescribir la isma fórmula que aparece en B11 aunque adaptando las referencias de celdas; pero es un trabajo innecesario. elecciona el rango A11: B18 . Luego ve a la ficha Datos en el grupo erramient as de dato s selecciona An áli si s Y si luego Tabla de d atos .



5.

n el cuadr Celda de entrada (f ila) no escribas nada, dado que en esta ta la sólo hay un variable, l tipo de interés, y está dispuest en una columna. n el cuadr o Celda d entrada (columna) escribe (o selecciona con el rat n) 4, que es la celda e la que s encuentr el tipo de interés implicado en la f rmula de 7. ulsa Ac e tar y ob erva cóm se ha llenado la tabla con los valor es correspondi ntes.

6. 7.

Así, cuando se trata de crear una tabla on una sola variable l proceso ásico es: •

•

•

Situar en la primera olumna (a la izquierd ) de la ta la los difer entes valor es de la variable Situar en la primera fila la(s) referencia(s) (en for a de fór ula del tipo =CELDA ) a la fórmul o fórmula que oper (n) con dicha variable Introducir, en el cuadro de diál go Tablas , como var iable la celda en la que aparece a uel valor de la variable con el que se ha op rado en la fórmula.

Es posible, no ob tante, que nos intere e operar c n dos vari bles: por jemplo, en el caso anterior, averiguar el importe de los pago mensual s a difere tes tipos de interés y partiend de cantid des difere tes (como principal d l préstam ). En tal caso el proceso ser á algo dif rente. Antes de iniciarlo, guarda el libro. A contin ación, sigue los sigui ntes paso : 1. En A11 escribe la fór ula =B7 2. En el rango A12:A18 deja los tipos de inter s tal como están. 3. En el rango B11:F11 has de introducir valores osibles d l principal a devolver: esde 650 hasta 8000 €, variando en inter valos de 500 € (puedes utilizar el sistema de llenado de eries de la forma ant s vista). 4. En A11 escribe la fór ula =B7


85


5. Seleccion toda la t bla (el ra go A11:F18) a Tabl de datos en el grupo Herramie tas de Dat os en la Ficha Datos 6. En el cua ro Celda variable fi la selecciona la celd B3 (donde aparece el valor de la cantidad restada c n el que se ha operado en la fórmula). En el cuadro Celda variabl e columna, seleccion la celda 4. 7. Pulsa Ac ptar y o serva có o se completa la tabla con l s resultados correspon ientes. Por ta to, el proc so básico ara crear na tabla c n dos vari bles es el siguiente:  Sit ar:  en la primera colum a (a la iz uierda) d la tabla, los diferen es valores a

analizar d una de la variable  en la pri era fila d la tabla, los difere tes valores a analiz r de la o ra variable  en la celd que forma el vértice uperior izquierdo de la tabla, la r eferencia (en forma de fórmula d l tipo =C LDA ) a la fórmula que opera con ambas variables.  Intr oducir, en el cuadro e diálogo Tablas , c mo variables las celdas en la que ap recen aquellos valor s de cad variable con los que se ha operado en la fór ula. Los mismos resul ados que e consigu n con el pr ocedimiento de creación de tabl podría conseguirse también mediant el proce imiento d escribir la fórmula funció correspondiente en na de las celdas de la tabla (p. j., en B14) y copiarla las re tantes. P ro eso nos obligaría a pensar bien en la fórmula o función introducir y, sobr todo, en las referen ias a utilizar en dicha fórmula función. procedimiento de creación d tablas no ahorra to a esa tare .

s o a a l

Trabajand con e quem s Un esquema odríamos definirlo como un re sumen pr eciso que refleja lo con eptos m s importantes o de mayor trascendencia del document esq ematizad . Así pues, un squema puede ser erfectame te un índice de un libro, dond vemos todos l s puntos tr atados en l libro, ta bién pode os ver co o ejempl de squema el índice de ste curso, el cual contiene los p ntos más importante que se tratan en él y ad más está estructura o por niv les de profundizació sob e un tem en concr eto, vamo desplegando el es uema de los punto con enidos en l tema. Ant s de pon rnos a cr ear un es uema de emos ten r en cue ta alguno asp ctos. Debemos asegurarnos e que los datos sean apropiados par crear u esq ema. Los datos apropiados ara crear un esquema deben tener un jera quía o dis oner de u a estructu a por niveles, por eje plo si ten mos dato sob e las precipitaciones ocurridas a lo largo el año en toda Esp ña con la pre ipitaciones mensuales de todas las provincias, est s datos son bueno can idatos a f rmar un esquema. Pero si únic mente tenemos datos sobre lo gas os efectua os en una compra, c n una estr ctura compuesta por Producto - Pr cio, no disponemos de niveles suficientes para hacer n esquem . En na hoja s lo podem s incluir u esquema, para tener más de un esquem sob e los mismos datos, debemos c piar los datos a otra hoja.

86



Se mu stra una fil de datos e ventas agrupados por regiones geográfica y meses con varias f ilas de resumen y detalles.

Agru ar Para a rupar siga el siguient procedimiento: 1. Asegúres de que c da colum a tiene un rótulo en la primera fila, contiene datos similares en cada columna y el ra go no tie e filas ni columnas en blanco. 2. Seleccion una celda del rango. 3. Ordene la columnas que forma los grupo . 4. Seleccion las filas q e contienen datos si ilares a fin de agrupa las Por ejem lo en el libro Lista , despué de habe ordenado la lista categorías seleccion los registr s de la categoría Bebidas

5. En la ficha Datos en l grupo Es quema, haga clic en


or

grupar

87


6. En el cuadro de diálo o Ag ru pa haga clic n Filas y ulse Ac ep ar

7. Se muestr a:

8. Si lo dese puede hacer un Subgrupo, par esto ordene la lista or Catego ía y luego por Nombre de producto. 9. A continuación, dentro del g upo Bebidas cread en pasos anterior s, seleccione los registr s que em iezan con la palabra erveza… 10. Haga clic n Ag ru par y elija Filas , se muestra:

88



Obser e que a la izquierda e su rótul de filas s ven símb los, los cu les permiten ver/oc ltar los detalles de ca a uno de los grupos.



Pa a mostrar los datos de detalle de un gr po, haga clic en grupo.



Pa a ocultar l s datos de detalle de n grupo, haga clic en

el

del grupo.

En la arte super ior de su hoja se mu stran niveles de esquema, los cuales ocultan compl tamente los detalles:



En los símbolos de esq ema , haga clic en el nú ero del ni el qu desee. Los datos de detalle de los niveles inferiores s ocultan. Por ejemplo, si un esq ema tien cuatro ni eles, pue e ocultar el cuarto y mostr ar los restantes hacie do clic en .

Por lo general s agrupan datos par mostrar ubtotales, por ejemplo podríamos agrupar la categoría Bebida para mos rar el Total de bebidas, pero esto lo veremos más a elante cuando usemos Subtotal es

Desa rupar 1. Haga clic n la hoja de cálculo. 2. En la ficha Datos , en el grupo Esquema, haga clic en la flecha situada junt a Desagrup r y, despu s, en Borr r esquema.

3.


89


4. Si las filas o columna todavía están oculta , arrastre los encabe ados de filas o column s visibles a ambos lados de las filas y columnas ocultas, elija Ocultar y mostrar en el coman o Format , en el gr upo Celdas de la ficha Inicio y, a continuaci n, haga clic en Mostr ar filas o en Mostrar olumnas . Si uita un esquema mi ntras los datos de detalle están ocultos, la columna o fila de detall permanecen ocultas. Para mos rar los dat s, arrastre los númer os de fila visibles o las letras de columna adyace tes a las olumnas o filas ocult s. En la ficha Ini io, en el gr upo Celda , haga clic en Formato, elija Ocultar y most ar y, continuación, haga clic en Most ar filas o e Mostrar columnas.

Crear Subtotal s

Condiciones para crear s btotales Uno d los cálcul s más co unes que se hacen en las listas y en las b ses de datos es el de los Subt tales . Es decir, que e haga una operació parcial d los registr os que s especifiquen en el rango, a í como u a operación general de toda la búsqu da. Para usar esta herramienta, es nece ario que l lista se encuentre ordenada de acuerdo al campo que se a a usar para obte er los su totales. P r ejemplo si desea os obtener subtotales por Prov eedores , e s necesari que la ta la se ordene por No mbre del roveedor . Incluso podrá definir ás de un nivel de s btotales, or ejempl un subtotal por país y por Nom re de Proveedor. El cua ro Subtotales se muestra en la figura:

90



En la lista Para cada cam bio en , s determin cuando xcel deb insertar el Subtot l, es decir que se ba e en un determinado campo para realizar l cambio. s aquí d nde usted selecciona el campo or el que desea obtener el subto al. La lista Usar f un ión contie e las funciones dispo ibles para realizar lo cálculos con la lista. Tabla: Funciones de SubTot les Funci n

Utili ad

Suma

Sum de los ele entos

Cuenta

Cantidad de val res que no son blancos

Prome io

Promedio de los elementos en el grupo del Subtot l

Máx

Valo numérico más grande en el grup del Subto al

Mín

Valo numérico más peque o en el gru po del Sub otal

Producto

Prod cto de tod s los valor s del grup del Subto al

Cuenta núm

Cantidad de registros o fila que contie nen datos uméricos

Desve t

Esti ación de la desviació estándar

Desve tí

Desviación está dar

Var

Esti ación de la varianza

Varp

Vari nza de una población

En la lista Agreg r Subtotall a se determina que campos van a contar con Subtotal. Se pu den seleccionar todo si así se esea pero el cambio e va a realizar según lo determinado en la lista Para cada cam io en . Reem lazar Sub totales ac tuales , cambia los

ubtotales anteriores e inserta los nuevo . Si apaga esta casilla podrá aplicar más de un nivel d subtotale . Salto

e página ntre grup os inserta automática ente un salto de pá ina cada vez

que ter mina un S btotal, mu útil para l impresión. Resu en debajo de los da os va a c locar cada Subtotal al finalizar c da rango, de

otra fo ma lo coloca al inicio de cada rango. Si apaga esta asilla, los subtotales se muestr an al inicia el grupo. Para q itar todos los Subtotales, utilice l botón Qu itar todos .

Ac tividad 06:: us ar Tabl as de dato s

Veam s un ejemplo práctic : Se pide obtener el total de tock por Categoría de Produ to de su H ja1 del lib o Listas . s decir se pide calcul r la sumat ria del Stock para c da una de las catego ías de su t bla. 1. Ordene su tabla usando como cr iterio la Ca egoría . 2. Luego, elij la ficha Datos , en el grupo Esq emas haga clic en S btotal 3. En esta ventana, cambie los cuadros:


91


Para cada cambio en : Usar Función : Agregar s btotal a :

Categoría Su a ma que la casilla del Sto k

4. Haga clic n Aceptar observe l s resultad s:

Usar

últiples iveles de subtotal s.

Para aplicar 2 o más niveles de esquema es ecesario rdenar ta bién la li ta usand 2 criterios. En nue tro caso, ueremos crear un subtotal por Categoría y Prove dor. Es así que debe ealizar lo siguiente:

92



1. 2. 3.

4. 5. 6.

7. 8.

Ordene la lista: como primer crit rio use la Categoría y como se undo criterio use el No bre del Pr veedor. Luego haga clic en S btotal del grupo Esq ema en la ficha Dato s En esta ventana, cambie los cuadros: Par a cada ca bio en : Categoría Us r Función : Suma Agr egar subto al a : marqu la casilla el Stock Haga clic n Aceptar. Ahora, ha a clic nu vamente n Subtota l del grup Esquem en la ficha Datos En esta ventana, cambie los cuadros: Par a cada ca bio en : Nombr del Proveedor Us r Función : Suma Agr egar subto al a : marqu la casilla el Stock Desactive la casilla R emplazar Subtotales actuales. Haga clic n Aceptar observe l s resultad s, para ver el resumen use el ni el de esque a 2


93


Preg ntas e Rep so 1. ¿Cuándo s útil usar Filtros ava zados en l gar de los Autofiltros 2. En la im gen, se uestra u a relación de bienes de una determinada empresa. Cuál sería el criterio i quisiéra os filtrar la información por Cuen ta y por Trab ajador del área de Al acén ?

________ ________ ________ ________ ________ ________ _____ ________ ________ ________ ________ ________ ________ _____ ________ ________ ________ ________ ________ ________ _____ 3. Siga los asos necesarios par que la in ormación se muestr como en la siguiente i agen:

94


Capí ulo

Ta las Gr fico din mic s En este capítulo apren eremos a: rear tabla dinámica rear gráfi os dinámic os rear tabl s de ad inistració elacionad s

Programa


y análisis de datos

9


Trabajand con t blas d námic s Un inf rme de t bla diná grandes volúmenes de dat numéri cos en pro undidad y Un inf rme de tabla dinámic

ica es una forma interactiva e resumir rápidame te s. Las ta las dinám icas se u an para analizar dat os ara respo der pregu tas no anti cipadas so re los dat s. está especialmente diseñado pa ra:

Consultar grande cantidad s de dato de much s manera diferente y cómod as para el suario. Calcul r el subtotal y ag egar datos numéric os, resumir datos or categorías y subcategorías, crear cálc ulos y fórm ulas perso alizados. Expan ir y contr aer nivele de dato para de tacar los resultados y despla arse haci abajo par a ver los d talles de l s datos d resumen de las áreas de inter s. Desplazar filas columna y columnas a fila s para ver resúmen es diferentes de los atos de ori gen. Filtrar, ordenar, agrupar y d ar formato condicional a los su conjuntos de datos ás útiles interesantes para po er centrar e en la inf rmación que le inter esa. Presentar inform s electrónicos o im resos concisos, atractivos y con comen tarios.

•

•

•

•

•

•

A menudo utiliza un infor e de tabl dinámica cuando esea comparar total es relacio ados, so re todo si tiene una lista larga de númer s para sumar y desea realiza compara iones disti tas con c da númer . En el inf orme de t bla dinámi ca mostrado a contin uación, pu ede ver fá ilmente có o se comparan las v entas de g olf del ter er trimestr en la celd a F3 con l s ventas d cualquier otro deporte o trimest e, o con l s ventas t tales. Datos de orig en, en est e caso de una hoja de cálculo Los valores d origen de l resumen del Trim3 de Golf e el informe de tabla di námica El i forme de tabla dinám ica entero Re umen de l os valores de origen en C2 y desde los datos de origen

8

En los informes de tabla din mica, cad columna o campo d los datos de origen se convie te en un campo de tabla dinámi a que res me varias filas de inf ormación. n el eje plo anteri r, la colu na Depor e se convierte en el campo Deporte y ca da registr de Golf s resume e un solo el emento Golf.

96


Capítulo V: T ablas y Grá icos dinámi os

Un ca po de valores, com Suma d ventas , roporcion los valor s que van a resumirse. La cel a F3 del informe anterior contien e la suma el valor V ntas de cada fila de los datos de origen para la cual l columna Deporte contiene Golf y la columna Trimestre contiene Trim3. e forma predetermi ada, los atos del área Valor es resum n los dat s de orige n subyace tes en el informe d gráfico di ámico de la siguiente forma: l s valores uméricos tilizan la fu nción SU A, y los valores de te to la función CONT R. Para c ear un inf rme de ta la dinámic , debe def inir los dat s de orige n, especifi ar una ubicación en el libro y di eñar los c mpos. Insert r una ta la diná ica

ctividad 01: Insertar tabla dinámica

En este ejemplo t nemos u a Tabla de pagos realizado a distint os proveedor s en distin os meses e imputabl s a distinta s obras

Estos datos se encue ntran en el Libro Obra TD, abra ste archiv . Sería inter esante sab r: a) Cuant se le pag a cada pr veedor, o or cada o ra. b) Que onto se l pago a un determinado pro eedor en concepto de trabajos realizado s en una o ra especifica, etc. 1. Coloc r el cursor en cualqui r celda de la tabla 2. Ir a la ficha Inse tar en el rupo Tabl s , haga cli c en Tablas dinámic as finalm nte en el enú que se desplieg haga clic n Tabla di námica.


97

Microsoft Office Excel Excel 2007 Ava zado

3. Excel muestra el siguiente c uadro

Note omo Tabl o rango se ha llen do autom ticamente, sin embar go asegúrese que s a el rango correcto. 4. En Eli a dónde d esea colo ar el infor me de tab la dinámic a elija Nue a hoja d e cálculo . Es posible insertar un a hoja de álculo en l a misma h ja donde están nuestros dat s para e to debe legir Hoja de cálcu lo existe nt e. Haga clic en A eptar para empezar a diseñar nuestra ta la dinámica

98



5. Haga clic en el campo Obr a y arrástrelo hasta la zona Rótulos

e

colum na

6. Haga clic en el c ampo en el campo C ncepto y arrástrelo asta la zona Rótul s de fila. 7. Final ente tome el campo Importe y arrástrelo hasta la z na que di ce Valor s .

Note omo a me ida que u ted arrastr a los cam os, la tabl dinámica se fue for mando, qu dando de esta maner a: Programa Nacional de Informátic

99


8. Guarde su trabaj .

Interactuar con la tabla Despu s de crea un inform de tabla dinámica o gráfico di ámico, utilice la lista de campos de tabla dinámica ara agregar campos . Si desea cambiar un informe de tabla dinámica o gráfico din ámico, util ice la lista de campo s para ord enar y qui ar campos. De man ra predet rminada, l lista de campos de tabla diná ica contie ne dos secciones: u a sección de camp s en la pa rte superi r para agr egar o qui ar campos, y una sección de di eño en la arte inferi r para volv er a organi ar campo y ajustar su posició . Puede a oplar la lis ta de campos de tabl dinámica n algún la do de la entana y cambiarle e l tamaño horizontalm nte. También puede desacoplar la, en cuy caso pod rá cambiarl e el tamañ tanto vertical como h orizontalm nte. Si no parece la lista de ca mpos de t bla dinám ica, asegú ese de hacer clic en el inform de tabla dinámica o ráfico dinámico. Si aún así no a arece, en n informe de tabla dinámica, e la ficha Opciones, e el grupo ostrar u o cultar, hag clic en Li ta de ca pos, y en un inform de gráfic dinámico , en la fic a Analizar, en el grupo Datos, haga clic e n Lista de ampos.

Si no aparecen l os campos que desea utilizar e n la lista e campos, actualice el inform de tabla dinámica o gráfico inámico para que s e muestre los nuevos campos, campos calculados , medidas, medidas c alculadas dimensio es que haya agregado desde l última op ración. Es im ortante comprender el modo en que fun iona la lis ta de campos de ta la dinámi a y el mo o en que uede organizar difere ntes tipos e campos para obte er los res ltados de eados al di señar un informe de t bla dinámi ca o gráfic dinámico. Cómo funciona la lista d campos de tabla dinámic

100



Un origen de datos extern o contie e datos e tructurado organizad os como u no

o v rios camp s (denomi ados tam ién colum as) que s muestran en la lista de campos. Mu va un ca po al área de filtro d e informe en la lista de campo , lo que a su vez moverá el ampo al á ea de filtro de informe en el infor e de tabl dinámica. Mu va un campo al área de etiquet a de colu na en la li sta de campos, lo qu a su ez mover el campo al área d etiqueta e column en el info rme de ta la dinámica. Mu va un campo al área de etiquet de fila en la lista de campos, lo que a su vez mo erá el campo al área e fila de c lumna en l informe d e tabla din mica. Mu va un campo al área e valores en la lista e campos, lo que a s u vez mov rá el c mpo al ár a de valor s en el inf rme de tab la dinámic . Modif icar el diseño de u a tabla inámica Una v z terminada la tabla , existe la osibilidad e cambiar el Diseño, la estruct ra definid . Simplemente toma os los ca pos con l ratón y l s llevamo a la nueva posición, según c nvenga.


101


Orga izar los c mpos Puede cambiar el orden o la posición e los cam os existentes utilizando alguna de las cuatro áreas s ituadas en la parte inf rior de la s ección de iseño: Inform e de tabla dinám ica

Gráfi co diná ico

Descripción

Descri ción

Valores

Se utilizan para mostrar da os nu éricos de re umen.

Valo es

Se u ilizan p ra mostrar datos numéri os de resumen.

Etiquetas de fila

Se utilizan para mostrar ca pos como filas en el lado del infor e. Una fila en un posició inferior se anida con otr fila que stá justo encima e ella.

Cam po de ej (categorías)

Se tiliza p ra mostrar los campos como n eje en el gráfico.

Etiquetas de colum na

Se utilizan para mostrar ca pos com columnas en la parte superior del inf rme. Una columna en un posició inferior se anida con otr columna ue es á justo enc ima de ella .

Etiq cam leye (seri

d d

Se u ilizan p ra mostrar los campos en la leyenda el gráfico.

Filtro de inform e

Se utiliza par filtrar tod el inf rme en función del el mento seleccionado en el iltro de info rme.

Filtr infor me

d

Se utili a para filt ar todo el informe en función el elemen to selecci nado en el filtro de informe.

etas os da s)

Para o ganizar lo campos, aga clic en el nombre de campo en una de las áreas y , a contin ación, seleccione uno de los co andos sig ientes:

102

Sub ir

ube el ca po una p sición en el área.

Baj r

aja el ca po una po ición en el área.



Mov er al prin c ipio

ueve el c mpo al pri ncipio del á rea.

Mov er al final

ueve el c mpo al fin l del área.

Mov er al filt ro de inform

ueve el c mpo al ár a de filtro e informe.

Mov er a rótu lo s de fila

ueve el c mpo al ár a de etiqu tas de fila.

Mov er a rótu lo s de colu

ueve el campo al área de etiquetas de olumna.

na

ueve el c mpo al ár a de valor s.

Mov er a valor s Con figuración de cam po valo r , Con figuración cam po

de de

uestra

los

cuadros

de

diálogo

onfigura ión de c mp o o C nfiguraci n e campo de valor .

Aum ntar o di minuir registros en el origen Si usted agrega r gistros en su origen e datos, e tos se podrá “refresc r ” en la ta la dinámi a, es decir, se actu lizará cual quier cam io que realice en su datos. P ra actuali ar su tabla: 1. Lleve el in icador a s tabla dinámica 2. Haga clic n el botón Ac tu ali za en el gru o Datos

n la ficha Opciones de

Herramie tas de tab la dinámic a

Orde ar Usted uede ord nar la tabl dinámica ubicándos en la sec ión que d see orden r, por ej mplo en n uestra tabla de la a tividad 1 itúese en cualquier r egistro de la secció filas y luego haga cli c en Orde ar en la fi ha Opcion es en Herr amientas e Tabla inámica.


103


Agru ar y mos rar detalles Puede agrupar lo elemento de un ca po de for a personal izada con l fin de ais lar el sub onjunto de los datos q ue necesit para trab jar y que no puede a rupar de o ro modo, por ejempl , con las opciones de ordenació y filtrado. Ag ru p r element os numéri os

1. Seleccion el campo numérico n el infor e de tabla de dinámi a que des ea agrupar. 2. En la ficha Opciones , en el grupo Ag ru par , haga clic n Ag ru pa campos .

3. En el cuadro Comen ar en , esc iba el prim r element que dese agrupar. 4. En el cuadro Termin r en , escri a el último elemento ue desee grupar. 5. En el cua ro Por , escriba un n mero que represent el intervalo incluido en cada grup . Ag ru p r fechas

horas

1. Seleccion el campo de fecha u hora en el informe de tabla inámica que desea agr par. 2. En la ficha Opciones , en el grupo Ag ru par , haga clic n Ag ru pa campos .

104



3. Escriba la primera hora o fecha ue desee grupar en el cuadro omenzar n y la última en el cuad o Terminar en . 4. En el cuadro Por , ha a clic en u o o más p ríodos de tiempo par los grupo s. Para agru ar los ele asegúrese de que después, aga clic e hacer clic n otros pe

entos por semanas, aga clic e ías es el único período de 7 en el cuadro Núm ro de día íodos de tiempo por l s que des

Días en e l cuadro P r ,

iempo sel eccionado y, . A continuación, puede e agrupar, como Mes .

Ag ru p r element os selecci nados

1. Seleccion los elem ntos del informe de tabla diná ica que d sea agrup r; para ello, aga clic e los elem ntos y arr strelos o resione la ecla CTR o MAY S mientras hace clic en lo elemento . 2. En la ficha Opciones , en el grupo Ag ru par , haga clic n Ag ru pa selección .

Para los campos rganizado en nive les, sólo pueden a ruparse l os elementos que tenga el mismo elemento n el siguie te nivel. Por ejemplo, si el campo tiene los niv eles País y Ciudad, n podrá agr upar ciuda es de países diferentes. Desag rupar elem elem entos

1. Seleccion los eleme tos que d sea desag rupar. 2. En la ficha Opciones , en el grupo Ag ru par , haga clic n Desagr par . En una se lección de grupo, sólo se desagr upan los el ementos s leccionad s. En un campo numéri o o de fec a u hora, e desagru an todos l s grupos el campo.


105


Ac tivi dad 02: 02: A ru ar d atos en u a Tabla di námica

Para efectua esta actividad vamo a necesit ar del archivo Ventas TD. Ábral y pr ceda de la siguiente anera: 1. Seleccio e la Hoja 1, en esta hoja se ncuentra na tabla inámica que muestra los datos de venta de una empresa f icticia. La ventas or departa ento están registrad s días por día por l s 6 prime o meses el año.

2. Haga cli en el ca po Fecha y luego s eleccione grupar c mpos en la ficha Op iones de erramientas de Tabl a dinámic

3. En el cua dro que se muestra e : a. Comenzar en: 01/01/200 b. Te minar en: 1/07/2006 c. Po : elija Mes s 106



4. Pulse Ac eptar y y ob erve el res ultado 5. Se muestra una ta la mucho más fácil e leer ya que está grupada or meses.

Anali ar un re istro específico Una tabla dinámi a permite nalizar los detalles de la secció n Valores , así para el detalle de las ventas del es de Mayo debe rea lizar:

er

1. Ubique el total de donde desea ver sus detalles. En uestro ca o el total de Mayo (111 0860) 2. Haga doble clic sobr e este val r. Los detalles se m estran en otra hoja de cálculo.


107


Confi urar el c mpo de datos. Cambi ar la funci n de Res men

Cuand se asu asume funció

usted ag ega un ca po al áre de datos de una tabla dinámic , por defe to e una función de res umen. Si el dato es d tipo numérico (como el Monto), se la función SUMA en caso de s r de tipo texto (como el rubro) se asume la CONTAR. Usted puede cambiar la función usada como resumen.

En nu stro ejemplo de la actividad 2, s desea obtener el pr medio de las ventas en lugar de la sumat ria del mo to. Para r alizar esta acción: 1. Haga clic n cualquie r celda del área de dat os. 2. Haga clic n el botón derecho del ratón en cualquier d ato del áre Valores

3. Haga clic n Configu ración de ampo de alor…

108



4. En el cuadro de diálo o seleccione Promedio en la list de funcio es.

5. Pulse Ac eptar . 6. Al Ac ept a , los cambi os se aplican sobre la tabla.

Form to de N mero Los n meros obtenidos en su resum n pueden ser formateados par a mejorar la presentación. Los formatos aplicados s n los mismos que usa ba para su s números. En nu stro ejemp lo, se dese a mostrar l resumen con un for ato perso alizado p ra que se muestren dígitos. 1. Haga clic n cualquie r celda del área de dat os. 2. Haga clic en el botón derech del ratón sobre cua lquier dato del área de valores y lija Format o de Núm ro


109


3. En esta entana, h ga clic en Número . Luego, eleccione la catego ía Contabilid d y Selecc ione la mo eda S/. Español (Per )

4. Al Ac ept a , los cambi os se aplican sobre la tabla.

110



Mod ficar el diseñ de la tabla inámi a Subtotales Al trabajar con u informe de tabla din mica, puede mostrar u ocultar l os subtotales de ca pos de columnas y d filas indivi duales, mostrar u ocu ltar los totales general es de col mnas y filas para el informe ompleto, calcular los subtotales y totales generales con o s in element s filtrados. 1. Seleccion un eleme nto de un ampo de f ila o de co lumna de n informe de tabla diná ica. 2. En la ficha Opciones , en el gru o Campo ctivo , haga clic en C nfiguraci n de campo .

Se muestr a el cuadro de diálogo Configura ción de camp o .


111


3. Siga uno de los proc dimientos iguientes: Calcular el subtotal

e un rótullo de fila o columna xterior

1. Pa a calcular el subtotal con la fu ción de r sumen predetermina a, de tro de Sub totales , haga clic en utomátic s . 2. De manera o cional, par a utilizar u a función diferente o para most ar var ios tipos de subtotales, hag clic en Personali zados y, a co tinuación, eleccione una funció . 3. Pa a los rótulos exteriores en for ulario co mpacto o esquemáti o, pu de mostrar los subt tales enci a o deba o de sus elementos, o bien ocultar lo s subtotales haciendo lo siguient : 1. En la ficha Dis eño , en el grupo Diseño , haga clic en Subtot ales . 2. Siga u o de los p ocedimient os siguient es:







Calcular el subtotal

Seleccion No mostr ar subtotales . Seleccion Mostrar odos los subtotales en la pa te inferior d l grupo . Seleccion Mostrar odos los subtotales en la pa te superior el grupo . e un rótullo de fila o columna i nterior

Para selec cionar una función, e Subtotal s , haga cli c en Perso nalizados , si está dispo ible esta f nción y, a continuaci n, seleccio ne una fun ión. Quitar su totales

Haga clic n Ningun en Subto ales . Si un cam o contiene un eleme to calculado, no es p sible cam iar la funci ón de resume n de subtot ales. Active o desactive la casilla de verificació Incluir n evos ele entos en el filtro anual par a incluir o xcluir nue os eleme tos al apli ar un filtro en el que se han seleccionado elementos específico en el menú Filtro. Puede mostrar u cultar los t otales del i forme de tabla dinám ica actual. Mostr r u ocultar totales generales 1. Haga clic n el inform e de tabla inámica. 112



2. En la fich Diseño , n el grupo Diseño , h ga clic en Totales g nerales y, a continuaci n, selecci ne una de las siguien es opcion s:









De activado ara filas columna Ac ivado par filas y co lumnas De activado ólo para f ilas Ac ivado sól para colu mnas

Establecer el comporta iento predeterminado para mostrar ocultar totales general s 1. Haga clic n el inform e de tabla inámica. 2. En la ficha Opciones , en el grupo Tabla di ámica, haga clic en pciones .

Se muestr el cuadro de diálogo Opciones de tabla diinámica .


113


3. Haga clic n la ficha otales y fi ltros. 4. Siga uno de los proc dimientos iguientes: Mostrar to tales gen rales 

Active la casil la de verifi cación Tot ales gene ales de las column s , Mo strar total s general s de las fi las o amb s.

Ocultar to tales generales 

De active la asilla de verificación Mostrar to tales gen rales de l as co l umnas , M strar total es general es de las ilas o ambas.

Diseñ de infor e Ajusta la forma omo se muestra la tabla diná ica. Para realizar es o siga est os pasos: 1. Haga clic n una celd a dentro d la tabla di ámica 2. En la fich Diseño d Herramie tas de ta las dinámi as haga c lic en Dise ño de informe en el grup Diseño

3. Se muestr :

114



4. Elija la o ción deseada y vea los cambios que s dinámica.

producen en la ta la

Insert r filas en blanco Usted puede agr gar una lí nea en bl nco entre cada elem ento agrupado, haga lo siguiente: 1. Haga clic n una celd a de la tabl a dinámica 2. Haga clic n Filas en blanco e la ficha Diseño de erramientas de tabl as dinámicas

Estilos de tabla dinámic

Seleccione un val or en la ta la dinámic a, en la fic a Diseño sted puede seleccio ar un estilo disponibl e para su t bla dinámi ca


115


A continuación de sarrollare os una ser ie de activi ades para demostrar el uso de l as tablas inámicas.

ctividad 3: Crear el área de d iseño del i nforme de tabla dinámica

En este ej rcicio pre arará el ár ea de dise o del infor me para p der crear un informe d tabla din mica. Par tal fin se trabajará on el arc ivo de Ex el “Trabajar on tablas d inámicas”, pida a u in tructor un copia del ismo. Dedique unos minuto s a revisar los datos e la hoja e cálculo e Excel. Las columnas tienen títulos en la primera fila. Cada título aparece á más tar de como nombre de ca po en la L ista de ca pos de l tabla din ámica. Cada columna c ontiene un tipo de datos, ya se texto, nú eros o fe has. No hay columnas ni filas vac ías entre l s datos q e se utilic n para el informe de la tabla diná ica. Ahora ya puede mpezar. 1. Va a utilizar todo los datos de la hoja de cálculo. Haga clic en los dat s. Por ej mplo, en la celda B5. 2. Haga lic en la pestaña Inse tar . En el rupo Tabl s , haga clic en la flec ha de Tab la dinámic a y, en el enú, haga clic en Ta la dinámi a.

Aparec e el cuadro de diálog Crear t abl a dinámic .

116



3. Selecc ionar tabl o rango está selec ionado. El cuadro Tabla o Ran o muestr a todos lo datos de la hoja seleccionada, desde la elda A1 a la E800, ue es lo que quiere ( son todos los datos d e la hoja d cálculo). La opción Nueva h ja de cál ul o está elecciona a donde e ubicará el inform . Haga clic en Ac ept r . Se ins rta una n eva hoja d e cálculo (Hoja1). A l a izquierda está el ár ea de dis ño para laborar el informe d tabla din mica. A l derecha se encue tra la List de camp s de la ta la dinámi ca (si la lis a de campos no ap rece en l hoja de cálculo, haga clic en el área de diseño el inform ). En la lis ta figuran los nombre de todas las columnas de la h ja de cál ulo. Esas columnas hora son ampos . Puede colocar cualqui ra de los campos d l diseño d l informe d e la tabla inámica ( pero no tiene que utilizar todos los campos). Tenga en cuenta que Herramientas d la tabla inámica parece en la Cinta, ue incluye las fichas pciones Diseño .


117


Ac tivid ad 04:: Crear un informe d tabla din mica

Ahora cre rá su info me. Empi ce planteándose est pregunta: ¿Cuánto ha vendido c ada vend dor? Par respond r, necesit agregar los campos Vendedor e Importe del pedido a l informe. 1. En la ista de c mpos de la tabla di námica, h ga clic en la casilla de verificación junto Vendedor .

El campo se agre a al informe.

2. A continuación, e la Lista d e campos de la tabl dinámica, haga clic en la casilla de verifi ación junt a Importe del pedid . El campo se agre a al informe.

118



Ahora uede ver uánto ha vendido cad a vendedor Si hac clic en la oja de cál ulo, fuera el informe de la tabla dinámica, no puede ver la lista de campo de la tabl dinámica. Para recuperarlo, ha ga clic en cualquier l gar del inf rme; por e jemplo, ha a clic en l celda A3. Si la lista de campos no aparece, significa que se ha desactivad . En la pa te superi r de la ven ana, en la ficha Opci nes , en el grupo Mos trar/Ocult r , haga clic en Lista de campo s para volver a activar la lista de los campo .

Ac t ividad 05: Ca Cambi mbi ar el nombre el encabe ado

Como ya hemos indicado en ste capítulo, dice " uma de cantidades de pedidos" ncima del campo porque Excel ha utiliza o la funci n Sumatorio para sumar las cifras. Ahora pu de dejar e l nombre t l cual, o c mbiarlo, a lo que le ens eñaremos n este ejer cicio. 1. Haga lic en el n mbre del campo del informe, Suma de c ntidades e pedid s , de la ce lda B3.

2. En la arte superi or de la ve tana, en l Cinta, en la ficha Opciones , vaya al gru o Campo activo . E el cuadr de texto Campo ac tivo (verá el nombr "Suma d cantidad s de pedi os" allí), escriba el nuevo nom re para el campo.


119


Por ejemplo, escri ba Cantid d vendida y pulse E TRAR. El nombre nuevo, "Cantidad endida", aparece en l informe.

El nuevo ombre qu utilice no puede ser el mismo que el del campo de la lista de ca pos de la tabla diná ica (Impor e del pedi o). Si se preg unta si pue de modific r el nombr e de un encabezado, seleccióne lo. Vea si a arece en la casilla de texto ampo ac iv o . Si e así, puede cambiarlo. Simpleme te recuerde que no puede indic r el mism nombre q ue el de la lis a de cam os de la ta bla dinámi a. Tenga n cuenta ue no puede modificar l os nombre de los encabezados Etiquetas de filas y Etiquetas e columnas del inform .

Ac t ividad 06 06:: Ordenar el informe d e tabla din ámica

En este ej rcicio aver iguará cuál es el rank ing de los endedore en cuant a pedidos, verá quién ha ve dido más y quién menos. P ede hace rlo rápidamen te ordenan do por el campo Ca tidad ven ida (nuev nombre el campo Su a de la cantidad de edidos, o l nombre q ue le haya dado). 1. Haga clic con el botón secundario n una de las celda del campo Cantid ad vendid a; por eje plo, haga clic con el botón sec ndario en la celda 6. 2. Seleccione Orden ar y y haga lic en Ord nar de m yor a men or . Ahora eacock e el primero de la lista, con lo que puede ver que Peacock es el que ha ven ido más; continuación aparec Leverling, y Buchan an es el q e menos ha vendido.

120



Ac t ividad 07:

gregar ot ro campo a la tabla inámica

En este ej ercicio agr gará el c mpo Fech a del pedi do al infor e. Después agrupará l as fechas e las ventas por trimestre a fin de poder er cuánto ha vendido cada comercial por trim stre. 1. En la ista de c mpos de la tabla di námica, h ga clic en la casilla de verificación junto Fecha del pedido . Tenga puede autom no nu de los

en cuenta que dado que el ca po no contiene cifr s (las fich as ser númer os, pero s les da for ato de fecha), el campo se agre ga ticamente a la izquie da del info rme. Ya q e es el se undo campo érico (Co ercial era el primero), aparece c on sangría a la derecha ombres d los comer ciales.

El ca po Fecha del pedid aparece n el infor e como ías del a o, porque así se intr dujeron lo datos en los datos or iginales.

Cuand tiene muchos datos que c ntienen f chas u oras, puede agruparlos. Por ej emplo, con las fechas, puede ag uparlas en días, mes s, trimest es o años. 2. Haga lic en una elda del c mpo fecha. Por ejem lo, haga cl ic en la cel da A6. 3. En la arte superior de la ve tana, en l Cinta, en la ficha Opciones , en el grupo grupar , h ga clic en el Campo del grupo . En el cuadro de diálo go Agru Ag ru p ciones , eleccione Trimestre . Si tam ién selecc iona Mes s , haga clic para can celar la sel ección. Ha a clic en ceptar .


121


Ahora l informe

uestra las fechas agr upadas por trimestre.

Tambi n puede a grupar los datos haciendo clic on el botón secundario del rat n en los d tos que d sea agrup r y hacien o clic en grupar en el menú ontextual.

122



4. Antes de pasar a l siguiente ejercicio, bserve el igno men s (-) junto al nombr de Bucha nan.

Haga debajo Bucha signo datos.

lic en el igno men de Buchanan para an, y hace que Calla ás (+) junto al nom

s. Esto contrae el c mpo Fecha del pedi do ue no ve la inform ación de l fecha p ra an ascien a en el inf orme. Ahor a aparece un re de Buc anan, lo ue indica ue hay más

Ahora haga clic n el signo menos (-) junto a C llahan par a contraer el campo Fecha del pedido también. Puede controlar c uántos dat s están e puestos e el inform . Cuando ve un signo menos, uede utiliz rlo para c ntraer el i forme, y utilizar el sig no más p ra ampliarl o cuando l necesite. Ahora mplíe tod s los dato de Bucha an y Calla han haciendo clic con el botón ecundario del ratón e Callahan. Seleccion Am pl iar / ontraer y , a continuación, hag a clic en A mpliar tod o el camp o . Se amplían todos l os datos e Buchana n y Callah n.

Ac ti v idad 08: A gregar un filtro de in formes de tabla dinámica

En este ejercicio agr gará el campo País al informe para ver el desglose de las ventas por país. Agregará el campo País de dos maner s diferent s: primero como una eti ueta de fil y despué como un iltro de info rme. 1. En la ista de c mpos de la tabla di námica, h ga clic en la casilla de verificación junto País .


123


Dado ue el cam o es no n mérico, se agrega a l a izquierda . Es el ter er campo agregado a la izqui rda, y su lugar es n el cam o Fecha el pedido. No es muy fácil comparar las v entas de EE.UU con las del Rei no Unido (puede ver e si se desplaza haci el final del informe). 2. Intente mover el campo país para ver si con el botón sec ndario del ratón en ejempl , haga cli c con el botón secu Mover y vea toda las opcio es que tie

eso mejor la legibilid ad. Haga lic no de los elementos de país; or dario en l a celda A . Seleccio ne e para mo er el cam o País.

Haga lic en Mov r " País" l principio . El campo País subir á dos nivel es (por encima del c mpo Fech del pedid y el campo Comerci l).

Ahora los campo Vendedor y Fecha del pedido van debaj o del campo País.

3. Haga lic en el si gno menos (-) junto a EE.UU para contraer los datos de EE.UU. Ahora haga clic en el signo menos (-) ju nto a Rein Unido p ra contra r esos datos. Pued ver las ventas de mbos paí es, pero no puede ver las ven as por co ercial. Así pues, agre gar el cam o País co o una etiqueta de fil a no parec demasiad o útil. 124



4. En la L ista de ca pos de l tabla din mica, deseleccione l casilla ju to a País . Esto elimina el cam o del info me de la t abla diná ica para q ue pueda probar otro método. Tambi n puede el iminar el c ampo haci ndo clic c n el botón secundari y seleccionando Eli inar " País" . También puede eli minar el campo mirando al final de la Lista de camp s de la ta la dinámi ca , en el á ea Ar ras tr ar campo s entre la siguientes áreas , h ciendo cli en la flec a del cam po y, a co tinuación, en Eliminar campo . 5. En la Lista de c ampos d la tabla dinámica , haga clic con el bot ón secun ario en el ampo Paí , y en Ag r egar al fil t o del info me . (O bi n, si prefiere utilizar el método de arrastr r y soltar, arrastre el campo P ís hasta la casilla Filtro del informe.) Utilice un filtro de informe p ra centrar se en un s bconjunto de datos del informe, que suele ser una lín ea de pro ucto, un p riodo de ti mpo o un región ge gráfica.

El campo País se agrega en ima del inf rme. (Tod o) a la der cha del tít lo País significa que se visualiz an los dat s de ambos países ( eino Unid y Estados Unidos). n otras palabras, los datos no s filtran.

6. Para filtrar el info rme por p ís, haga clic en la fl cha del in orme junto a (Todas ). Selecci ne Reino Unido o Estados U idos y, a continuaci n, haga clic en A eptar . A arece un icono de filtro j nto al p ís Programa Nacional de Informátic

125


seleccionado, lo ue signifi a que los datos se iltran. Si desea ver l os datos ara el otro país, selecciónelo y h ga clic en Ac ept ar .

7. Volva os a ver los dos aíses de nuevo: Ha a clic en (Todas) y, a contin ación, hag clic en A eptar .

Ac ti idad 09:

over el inf orme de t blas diná icas

A continua ción mover á el inform desplaza do los dat s de la fila al área de la columna. 1. Haga lic con el botón sec ndario del ratón en na celda el campo de fecha. Por ejempl , haga cli con el bot ón secund rio del rat n en la cel da A5. 2. Seleccione Move y haga cli en Mover " Fecha d l pedido" Columnas . Ahora los trimestr s se aline n arriba. A l final del i forme pue e ver el to tal para c da trimestr e.

126



Ac t ividad 10:

gregar fo rmato de

oneda al informe

Para ter inar, agregue form to al inf rme. Los números del infor e representan dinero, pero su f rmato no es de mo eda. En ste ejerci io aprenderá una mane a práctica para dar f rmato a t dos los datos al mis o tiempo, en lugar de te ner que seleccionar u rango de celdas indi iduales. 1. Haga lic con el otón secu dario en l celda A3 (encabeza o del campo Cantid ades ven idas , o el nombre que haya e legido). A continuaci n, haga clic en Form ato de nú eros del enú contextual. 2. En la ficha Núme o , en la lista Categoría, haga cli en Mone a. 3. En la asilla Pos iciones d cimales , aga clic e n la flecha hacia ab jo hasta ue haya 0 posiciones decimales. 4. Haga lic en la c silla Símb lo y en u símbolo ara seleccionarlo. Dado que h seleccion ado un formato de oneda, su símbolo ya se ha ía seleccionado. Pu de modific rlo si lo desea. 5. Haga lic en Ac e tar . Ahora tod s los núm ros se for atean como divisas.

Gráficos di ámicos Un inf rme de gr fico diná ico repres nta gráfic mente los datos de un informe de tabla dinámica. P ede modif icar el dise ño y los da tos que se muestran n un infor e de grá ico dinámi o tal y co o se hace con un inf rme de ta la dinámic . Un infor e de grá ico dinámi o siempre tiene un inf orme de tabla dinámi a asociado que utiliza el diseño correspo diente. A bos infor es tiene campos que se correspond n. Cuand modifica la posició n de un ampo en uno de lo informes, también se modifi a el camp correspondiente del tro inform . Además de las series, categorías, arcadores de datos y ejes de los gráfic os estándar, los infor mes de grá ficos diná icos tiene algunos e lementos especializados que se correspon den con lo s informes de tablas dinámicas, como se uestra en la siguiente ilustraci n.


127


ampo de f iltro del inf rme ampo de alores ampo de erie lementos ampo de ategoría

Camp de filtro el inform

Camp que se ut iliza para f iltrar datos por elementos específicos. En l ejemplo, el campo de página Región m estra los atos de todas las re iones. Par a mostrar los datos e una sol región, p ede hacer clic en la flecha de l ista desplegable junt a (Todas) y selecci nar la regi n. Utilizar campos e filtro de informe e una forma cómoda de resumir y centrar se rápida ente en un subconju nto de dat s sin modi ficar la inf rmación d la serie y la categoría. Por ejemplo, si stá realiz ndo una resentación, puede acer clic en (Todos) en un ca po de filtr o Año para mostrar las ventas d todos los años y lue go centrar se en año concretos haciendo lic en un año cada v z. Cada p gina del fil ro del inf rme del g ráfico tien el mismo diseño de categoría y series ara distint os años, e modo q e resulta f cil compar ar los dato s de cada ño. Adem s, al permitir recuperar sólo u a página el filtro a la vez de un conjun o grande e datos, los campos de página del filtro conservan la memori cuando e l gráfico uti liza datos de origen externo. Camp de valores

Camp del origen de datos (datos de origen: lista o tabla qu se utiliza ara crear un inform de tabla d inámica o ráfico dinámico. Los atos de or igen puede n tomarse de una lis a o rango de Excel, de un cubo o base de datos exte na o de otro informe de tabla dinámica.) ubyacente que prop rciona val res para c omparar o medir. En el ejempl , Suma d ventas es un valor d datos que resume la ventas tri estrales or región para cada deporte. El marcado de datos de primera categoría (Trim1) llega casi a 250 en l eje de alores (y). Esta ca tidad es la suma d las ventas corres ondientes a Tenis, S fari y Golf urante el rimer trim stre. Segú el origen de datos tilizado p ra el infor me, puede cambiar l a función e resumen (función de resum n: tipo d cálculo q ue combina datos de origen n un infor me de ta la dinámi a o una t bla de con solidación, o cuando se insertan subtotale automáticos en una lista o base de datos. Algunos e emplos de funciones e resume son: Sum r, Contar y Promedi .) a Prom dio, Conta , Producto u otro cálc lo.

128



Camp de serie

Camp que se a igna a una orientación de serie en un infor me de gráf ico dinámi o. Los elementos del campo proporcionan las se ies de da tos individuales. En el ejempl , Deporte es un cam o de serie con tres el mentos: Tenis, Safar y Golf. En el gráfico, las series se representan en la leyenda. Eleme to

Los el mentos r presentan entradas nicas en n campo e column o de fila, y aparecen en las listas des legables e los ca pos de fil tro del inf rme, en los campos de categ ría y en los campos e serie. E el ejempl o, Trim1, Trim2, Trim y Trim4 on eleme tos del ca po de cat goría Tri estre, mie tras que Tenis, Safar i y Golf s n element os del ca po de ser ie Deporte. Los elem entos de n campo de categoría aparec n como ró tulos en el eje de categorías de l gráfico. L s elementos de un ampo de serie apare en en la le enda y pr porcionan los nombres de las serie de dat s individua les. Camp de categ ría

Camp de los da tos de origen que se asigna a na orienta inform de gráfi o dinámic . Un campo de cat goría pro individ ales cuyos puntos e datos se represe tan gráfic Trimestre es un c mpo de c tegoría. E un gráfico , las categ el eje , o eje hori ontal, del ráfico.

ión de categoría en un orciona l s categorías mente. E el ejemp lo, rías suele aparecer en

Perso alizar el i forme

Puede cambiar el tipo de grá fico y otras opciones, como los títulos (títulos de gráfic s: texto descriptivo ue se alin ea automá ticamente on un eje o se centr a en la pa te superi r de un gr áfico.), la ubicación d las leyen as (leyen a: cuadro que identifi ca los dis ños o col res asigna os a las s ries de da os o categ orías de un gráfico.), los rótulos de datos etiqueta d datos: etiqueta que proporcio a informa ión adicio al acerca de un mar cador de d atos, que r epresenta n solo pu to de datos o valor que se origina a partir de una cel a de una oja de cálc ulo.), la ubi cación del gráfico, etc Crear un infor e de grá ico diná ico a pa tir de un informe e tabla diná ica exist nte 1. Haga clic n el inform e de tabla inámica. 2. En la ficha Insertar , n el grupo Gráficos , aga clic e un tipo de gráfico.

Puede utili zar cualqui er tipo de ráfico exc pto los gr ficos xy (dispersión), de burbujas o de cotizaci ones.


129


Interactuar con gráficos inámico Tal como lo hici ra con gr áficos co unes los ráficos d tablas dinámicas son totalm nte perso alizables, or ejempl puede m strar los r tulos de d tos, elimi ar ciertos elementos o cambiar la alineació n de las categorías. Para c mbiar est s opciones use: 1. Seleccion ndo el gráf ico use la f icha Prese tación

2. Use la ficha Diseño

Crear tab as rela ionad s

d

administra ión

análisis

d

dat s

Para simplificar l administr ación y el análisis d un grupo de datos relacionad s, puede convertir un rango de datos en una tabla de Microsoft Office Ex el (deno inada ant riormente lista de xcel). Una tabla es un conjun o de filas y colum as que co tienen datos relacion ados que e administ ran indepe dienteme te de los atos de otras filas y columnas d la hoja de cálculo. Para h cer de un rango de d tos una tabla para administración haga lo s iguiente: 1. Ubique el i ndicador d celda en ualquier c lda dentro de su tabl 2. Haga clic n la ficha Insertar en el grupo T blas , hag clic en Ta la

130



3. En el cuadro que apa rece asegúrese que el rango es l correcto

4. Pulse Ac eptar

De for a predeterminada, c ada colum a de la ta la tiene el filtrado habilitado en el encab zado de f ila para qu e pueda f iltrar u ordenar la ta la rápida ente. Puede agregar una fila de totales a la tabl que pro orcione u a lista desplegable de funcio es de agre gado para ada celda de la fila d totales. Un controlador de tama ño situad en la esq ina inferio derecha e la tabla ermite arr strar la ta la hasta que obtenga el tamañ deseado. A trav s de la cinta de op iones puede utilizar las siguien tes caract rísticas p ra admini trar los datos de la tabla: Cambiar l as propied ades de la tabla


131


Ordenar y filtrar

A la fila e encabe ado de u a tabla s agregan desplegables de filtr os. Puede ordenar l s tablas descende te o por colores, o puede crear un personalizado. Pued filtrar las tablas par que sólo satisfacen los criterio que espe ifique, o p ede filtrar

automátic mente listas n orden scendente o criterio d ordenación muestren los datos que or colores.

Ap li car fo rmato a lo datos de la tabla

Puede dar formato rápidamente a los dato de la tabla aplicand un estilo de tabla predefinido o p ersonaliza o. Puede elegir tam ién opcion es de estilos rápidos para mostrar una tabla on o sin u a fila de e ncabezado o de total s, para aplic r bandas e filas o c lumnas con el fin de facilitar la l ectura o p ra diferenciar la primera o última columna de tras colum as de la tabla.

132



Insertar y eliminar fi las y colu

nas de la tabla

Existen varios modos de agrega r filas y col umnas a u na tabla. P uede agre ar una fila en blanco al final de la tabla, inclu ir filas o co lumnas ad acentes a la hoja en la tabla, o insertar filas y column s de tabla en el luga r que des e. Puede eli inar filas columnas cuando sea necesari . También puede qui ar rápidamente filas que contengan datos dupl icados de na tabla. Usar una columna c alculada

Para usar una fórmul que se a apte a cad a fila de un a tabla, pu de crear u na columna c alculada. a columna se amplía automátic mente pa a incluir fil as adicionale de modo ue la fórmula se exti nda inmed iatamente dichas fil s. Mostrar y calcular t tales de d atos de un a tabla

Puede hall ar el total r ápidament de los da os de una tabla most ando una ila de totales al final de la tabla y utilizando l s funcion s incluidas en las list as desplegables para ca da una de las celdas e la fila de totales.


133


Preg ntas e Rep so 1. ¿Qué utili ad tienen l s tablas dinámicas? En la im gen, se empresa.

uestra u a relación de biene s de una determinada

Siga los asos necesarios par crear un tabla y gráficos din micos co o muestra la imagen

134


Capí ulo

Fu cio es e B se d da os En este capítulo apren eremos a: plicar fun iones de b se de dat s

Programa


13


Base de da os En Excel las bases de datos no son má que tabla en las cu les se ha i troducido de maner ordenad datos ref rentes a algún tem en partic lar. Por e emplo usted podría generar una tabla en la que lmacene l s datos de sus contactos y que funcio e a modo e agenda. En las bases de datos de Ex el se conoce como re gistros a ada una d las filas que confor a la tabla y como c mp o a cada columna. El asunto más importante en la creaci n de base de datos s saber d terminar que campo son los q e deben er almac nados, es decir que es lo que va almacenar en cada registro.

Fun iones

D

Las fu ciones de Base de atos oper n sobre rangos que ueden ab rcar más de una columna o fila. Una de las ventaj s clave d estas funciones es u capacidad para e tablecer rangos de criterio para rear criteri s complej s. Aunqu gestio dinámi inform que ta de las

las funciones de b se de datos, así co o mucha otras, son útiles p ra ar y buscar datos, se deben utilizar en la medida de lo posible tablas as para la gestión de listas larg s, almace amiento de datos o base de datos tivas. Est método n es solo ás rápido y eficaz para gestion r datos, sino bién se p ede aplicar cualquier de las funciones par base de atos a través ablas diná icas.

Sinta is general de las fu nciones de base de datos Las funciones b se de dat s comien an todas con BD y tienen un sintaxis en común: Función B D(base_d _dat os , c mpo ,crit rios).

Base_ e_datos s la tabla/lista que co tiene los datos

Camp es la columna sobre cuyos dat s queremos realizar l cálculo. ampo pue de ser texto con el r ótulo ence rado entre dobles co illas, co o por eje plo “Edad” o “Campo”, o un número que r epresente la posición de la colu na en la lista: 1 para la primer columna, 2 para la s gunda y así sucesivamente. Criteri s es el rango que contiene los criteri s para fil rar la ba e de dat s.

Necesitará usar una columna con d s filas si los regist os deben cumplir una condición para un campo. Por ejem lo si ust d desea btener la cantidad de productos perten cientes a l categoría “Bebidas” ebería es ecificar un criterio co o el siguiente:

Es po ible empl ar los operadores r elacionale dentro de las con iciones. or ejempl si usted necesita c nsiderar l s product s cuyo Stock sea s perior a 100 unidades deberá tilizar un criterio com el siguien e:

136


C pítulo VI: Funciones de Base de da os

Si nec sitara tomar en cuen a los registros que c mplan con dos o má condiciones en un determina o campo, entonces debe co siderar u área de criterios que contenga una columna y m s de dos filas. Por jemplo, si usted nec sita saber la cantid d de productos que pertenecen a las categorías Bebidas y lácteos, debe especi icar un crit rio como l siguiente:

Si lo que usted desea es e plear los registros que cumplan condicio es en dos o más campos, e tonces necesitará u a column por cada campo ue necesite consid rar. Por e emplo, si sted necesita saber cuantos pr oductos d la catego ía “Condi entos” tienen stock i ferior a 20, deberá utilizar el crit rio siguiente:

Es importante qu conozca la diferenci ente un r ngo de criterio de dos columna y uno d dos filas. Las condi iones en la zona de criterios s leen por ilas, y por lo tanto Excel evalú los registr os que cu plan las c ndiciones fila por fila. Por ejemplo, si se ti ne el rango de criteri s:

En este ejemplo existen d s condici nes. La p imera indica que se productos de la categoría “Condimentos” y la seg nda indica el requeri productos cuyo Stock sea superior a 50 sin import r cual sea la categorí condiciones en di tintas filas, Excel mo trará los r gistros qu cumplan las do condicion s.

requiere los iento de los . Al estar las ualquiera de

A continuación se tiene la siguiente lista de la cual toma emos como base para explicar algunas f nciones c n más det lle:


137


El rango A5:E20 stá asocia o al nomb e "alumno s " Si qu remos calcular la antidad de alumno que cu plen dos condiciones simultáneamente: han recibi o por lo menos 80 puntos en matemática y por lo menos 75 puntos en hist ria, hacemos lo sigui nte:

138



Nótes que en la primer fila de la hoja hemos re licado los ncabezamientos de las colum as de la tabla de datos.

En la celda E22 hemos pues o la fórmula

=BDCONTAR (alumn s;1;B1: 2) Donde:: alumn s

=Hoja1!$ A$5:$E$20 es el rang de la tabla de datos

1

Indica q e queremos contar las miemb os de la columna de la tabla

B1:D2

Es el rango de crit rios con los cuales q eremos "filtrar" la tabla

rimera

En lugar de usar el número de colum a, podem s usar el encabeza iento, con lo cual la fórmula se vuelve totalmente legible =BDC NTARA(alumnos;"no br e";B1: 2) Al pon r las definiciones de los criterios en la misma fila, esta os indica do el uso el operador Y, es decir, tod s las condiciones deben cum lirse simultáneamen e. Para usar el oper ador O, ponemos las condiciones en filas istintas. P r ejemplo, si quere os contar cuantos alumnos ha en la lista que reci ieron por lo menos 80 puntos en matem tica o 75 n historia, rreglamos la hoja de la siguient manera:


139


Por su uesto, ta bién hem s modifica o el rang de los criterios en la fórmula p ra que in luya todas las filas:

=BDCON ARA(al mnos;"nombre"; 1:C3) Tambi n podem s combinar los oper dores Y O en el rango de criterios. or ejempl , si queremos saber cuantos alumnos hay que reci ieron por lo menos 80 puntos en matem tica Y 75 n historia más de 8 de promedio, usamos el model

Tambi n aquí h mos modi icado la f órmula pa a que incluya todos los criterios =BDC NTARA(alumnos;"no bre";B1:E3) Las fu ciones base de datos son fácil s de usar, una vez ue hemos entendido la sintaxi básica. Son muy fle ibles y pe miten hac r cálculos que con más facilida y velocidad que las funciones atriciales La lista de algunas funciones base de d tos: BDPR MEDIO Devuelve el promedio de las entr adas seleccionadas de la base de

datos

BDCO TAR Cuenta las celdas que contienen números en una base de atos

BDCO TARA Cuenta las celdas que n están en lanco en una base d datos

140



BDEX RAER Ex rae de la base de datos un ú ico registr o que coi cida con los

criterios especific dos BDMA Devuelv el valor m ximo de las entradas seleccionadas de la base de dat s BDMI Devuelve el valor mí imo de las entradas seleccionadas de la base de dato

ultiplica l s valores de un campo determinado de r gistros de la base de datos qu coinciden con los criterios espe ificados BDPR DUCTO

BDDE VEST Cal cula la des iación est ndar basándose en na muestr de entradas

seleccionadas de la base de datos BDDE VESTP C lcula la d sviación e tándar basándose en la poblaci n total de las

entrad s seleccio adas de u a base de datos. BDSU A Suma los número de la columna del campo de lo registros e la base de

datos ue coincid n con los riterios es ecificados BDVA Calcula la varia za basá dose en una

muestra de las entradas

seleccionadas de una base de datos BDVA P Calcul la varianza basán ose en la población total de las entradas

seleccionadas de una base de datos


141


Preg ntas e Rep so 1. Abra el lib o Listas y seleccione la Hoja1:

2. Resuelva las siguient s pregunt s: a. Cu l es la cantidad total e product s de la cat goría Bebidas b. Cu l es prom dio de precios de los productos de la cate oría Bebidas y Condimento c. Cu l es la cantidad de productos Maison”

ue propor ciona el p oveedor “ a

d. A uanto asciende el V lor de los productos de la Categoría Carnes cu o stock es mayor a 3 e. Cu ntos prod ctos de la categoría

142

arnes tien n stock m nor a 50


Capí ulo

Fo mul rio y M cro En este capítulo apren eremos a: rear form larios que incluyan el uso de macros sencill as ara automatizar tareas rutinaria

Una macro son un conjun o de instru cciones que sirven p ra automatizar rocesos. Suponga os que realizamos frec entement la acció de sele cionar un rango p ra apli arle negrit a, cambio de fuente centrado. En lugar de hac r estas acciones ma ualmente, se puede elaborar una macro e invocarla para que eje ute los tr es procesos automáticamente.

Programa


14


Acti ación e la ficha P OGRA ADOR Debe ostrar la f icha Progr mador o jecutar en modo para programa ores cuando vaya a escribir acros, ejecutar mac os que h ya grabad o previam nte, o cr ar aplicaciones para utilizar con programa de Micros ft Office. Siga los siguientes pasos pa a activar la ficha Programador: 1. Haga clic n el botón de Micros oft Office de Excel .

y, a continuación en Opcion es

2. Haga clic n Más fre uentes y, a continua ión, active la casilla d e verificaci ón Mostrar fi ha Progr mador en la cinta de opciones

Dise o de f rmul rios Un for mulario es una hoja d e cálculo c on un formato y diseñ o ya establecido. Puede contener fórmula , funcion s e incluso control s (una lis ta, botones de opci n, casilla de verific ción). Gracias a un f ormulario li mitamos el ingreso y manipulaci ón de datos a nue tras hojas, así com brindam s solucio es de ne ocios p ra nuestr s clientes. Mostr r y ocultar eleme tos Lo pri ero que ebemos odificar, es la pa talla de resentació de nues ra solución, interfaz que debe s er mucho ás elabor da, más limpia, sin los element os propio de una ho ja de cálculo, como las líneas de división o l as etiquetas de hojas. Mucho de los el mentos q e usted nota en Ex el pueden ser ocultados, de e ta maner lograr qu su pantal la sea visu lmente m s sencilla simple. Los element os que se ocultaran on: • • •

Líneas de división Encabeza os de fila Columna Etiquetas e hojas

Para o ultar o mo strar cualq iera de est os element os, debe u ar: 1. Haga clic en el bo tón de Microsoft ffice y, a continuación en Opciones de Excel . 2. Haga clic en Avanz das y lue o busque la sección a la dere ha: Most ar opciones para este ibro .

144


Capítul VII: Formu larios y Macros

3. En esta s cción pued e encontrar algunas pciones c mo Most ar fichas e hojas , de marque la casilla de v erificación ara oculta r las fichas de hojas.

4. En la se ción Opci ones par esta hoj a, puede encontrar las siguie te opciones:

5. Haga clic en la ca illa de verificación ara mos rar/ocultar el eleme to correspon iente. 6. Por ejemp lo si desact iva las líne s de divisi n la hoja e verá así:


145


Una v z que he os defini o el dise o de nue tra hoja de cálculo, es necesa rio agregar el texto estándar, aplicar for ato de c ldas y otr as. Diseñ su hoja de acuerdo a la solu ción que desee imple mentar. Escriba tamb ién las hoj s que sirv an para r alizar cálc los y búsq edas. En las actividades que reali zaremos n este ca ítulo se u ara el libr Proformas observe que la H ja1 tiene el texto estándar, así c mo un dis ño de fuentes y bord s.

En la oja2 de s libro, se t iene una t bla de Co tos por ciudad que n s va a ser vir para r alizar cálc los posteri res.

146



A ctividad 0 : Ocultar lementos

En esta actividad se ocultará l s elementos de una hoja de c álculo, co o encabeza os de fila y columna, líneas de di visión y eti uetas de hojas 1. Abra el libro Profo rmas si es que aun no lo ha hech o 2. Ubíqu se en la H ja1 y haga clic en el b otón Micr soft Offic e 3. A continuación seleccione A anzadas busque e la lista de la derecha la secció Opcione para esta hoja. 4. Haga lic en la asilla de erificación Mostrar l íneas de ivisión p ra desactivarla. 5. Desactive tambié los encabezados de ila y colum na 6. Repita el mismo p roceso par la Hoja2

Uso d Control s Los controles nos permiten i teractuar on nuestra ventana e n forma más intuitiva, a través de listas, botones d opción botones e coman o, tal co o si fuer an cuadros de diálog os de Wind ows. Agregar controles Para insertar los ontroles, d be activar la ficha Pr gramado 1. Haga clic n la ficha rogramador 2. En el grup o Controle s haga clic en Inserta

3. A continuación elija u control p ra insertar en la hoja e cálculo


147


1. Botones

Nombres Etiqueta. Sirve para agrega

2. 3.

formulario Cuadro de Grupo . Para agr upar contr les como casillas botones de opción. Botón.

4. 5. 6.

ara ejecut r una mac o.

Casilla de verific ción. Per ite definir opciones

como Si/No Botón d e Opción. Sólo pod rá elegir

na de un

grupo de opciones. Cuadro de List a.

7.

rótulos y textos al

ara mostr r distintos elementos

en una li sta Cuadro Combinad . Muestra una lista d opciones

8. 9.

Control de Númer . Para cambiar el va lor de una

celda.

Añ da los co troles de f ormulario su hoja d e cálculo, ara tener un diseño de ac erdo a la figura:

148



Prueb la funcion alidad de c ada uno d los boton s: así por ejemplo, los botones de comando (Normal y Expres s ) permite sólo elegi r una de la s opciones . Si hace lic sobre na, se desactiva la ot a. Las C sillas de v rificación, permiten hacer clic s bre ellas. Si usted elige la casilla Delive y , notará omo se m rca/desmarca con ca a clic que aga. Form to de co trol Para t rminar co el diseño , debe modificar el interac úen con celdas de s hoja de álculo. Es sólo el mentos q e no se rel acionan con la hoja d los co troles, logramos que dichos controles se constr ir fórmulas basadas e n los valor s de esas

ormato de los controles para q ue decir, por ahora los ontroles s on cálculo, a l modificar el formato de vinculen a celdas y así podemos eldas.

Para odificar el ormato de un control •

Use el me ú contextual sobre el control. Elija Formato de contro l…

Por ej mplo, se ide modifi ar el control Númer , para qu al hacer clic en dic ho control, el valor d la celda C7 se incre ente en 1. 1. Use el botón derech sobre el ontrol Nú ero y elija la opción Formato el Control . 2. En el cuadro Vincula con celd , escriba 7. 3. También, se indicará un valor ínimo (1) y máximo (50). Su v ntana debe quedar co o la figura:


149


4. Haga clic n Ac ept ar Para probar la fu cionalidad del control, haga clic obre las fl echas. Ob erve como el valor de la celda 7 se incre enta.

De igual manera, debe mo ificar el ormato de cada uno de los co troles de su formul rio. En el aso de los botones de opción ( ormal y E press) de emos indi ar una celda con la ue se vinc le. 1. Use el bot ón derech sobre el ontrol Nor mal y elija la opción Formato el Control . 2. En el cuadro Vincula con celd , escriba H1. 3. Haga clic n Ac ept ar .

150



Obser e que al h cer clic so re el botó Normal , l celda H1 muestra el valor de 1. Si selecciona el botó n Express, la celda H1 toma el valor de 2.

Además, se debe modificar l formato de la casilla de verifica ión (Deliv ry ) para q ue se vincule con un celda. 1. Use el botón derech sobre el c ontrol Deli ery y elija la opción Formato el Control . 2. En el cuadro Vincula con celd , escriba H2. 3. Haga clic n Ac ept ar .

Obser e que al acer clic obre el b tón Deliv ry , la celda H2 muestra un valor lógico, si la casilla esta marc da muestr a VERDADERO, si no, muestra ALSO.

Finalmente, debe modificar l formato e la lista. n el caso e una list o un cua ro combi ado, en necesario ind icar un Ra go de En rada, es decir el ran o que tenga los elementos qu desea m strar en s lista. Ad más, debe vincular la celda a una celda e su hoja e cálculo. 1. Use el bo ón derech sobre el control Li s ta y elija la opción Formato el Control . 2. En el cua ro Rango de entrad a, marque el rango d onde se e cuentran l as ciudades (B5:B11 de la Hoja2 )


151


3. Además, l celda vin ulada deb ser la cel a H3

4. Finalment , haga cli c en Ac e tar . Observe que la lista se llena con l as ciudades i dicadas e el rango e entrada.

Obser e que al acer clic s obre algun as de las ciudades, l a celda H3 muestra el númer del elemento selecci nado de l lista. Fórm las relacionadas a controles. Gracia a las cel as vincula as de sus controles, sted podr definir cie rtas fórmulas basad s en los valores de la celdas vi culadas. Así po ejemplo, ara calcul r el Costo Básico debe multipli ar los Kilo ramos por el Costo e acuerdo a la ciuda (vea la ta la Cargos). En la celda C16 escr iba: =BUS ARV( IN ICE( Hoj 2!B5:B11; H3); Hoj a2!B5:C11; 2) * C7

152



Gracia a la fu ción INDI E pode os ubicar la ciudad en base al eleme to seleccionado de s u lista (rec erde, la c lda H3 posee este valor). Una vez con el te to de la ciudad, ese valor e s buscado en la Hoj a2 con la función B USCARV, al ubicar e, se tom la segunda columna. Para calcular el ecargo S rvicio , debe consid rar un rec rgo del 5 % del Co to Básico sí el servicios es Exp ess . En la celda C17 escriba la f órmula:

=SI( H1=2; C16 * 50%; ) Recue de: el valo 2 en la celda H1 indi a que se ha elegido e l tipo Express Para c lcular el onto de D livery , de e conside ar: si la ca silla esta activa la cel da C18 te drá un val or de 10. n otro cas será de 0.

= I (H2 = ERDA ERO; 1 ; 0) Ac tivi dad 02: Fórmulas

Es ribir fórmulas en base las celdas vinculadas de sus co troles. Co plete su oja de cálc ulo de acu rdo al sigu iente enun iado. •

• • •

Suma del Costo, Re argo y D livery. (Incluye IGV el cual debe ser desagr gado) IGV: el 19% del Total del Servi cio Total a Pagar : Total el Servicio + IGV Pruebe la funcionalid d de sus órmulas, interactuando con sus ontroles, or ejemplo, h aga clic en su control úmero o elija otra ciu dad. Total del

ervicio:

Seguridad e macros En Microsoft Office Excel pu ede elegir la configur ción de se guridad de macros p ra controlar qué oc rre cuand se abre un libro q e contien macros. Por ejemplo, puede elegir eje utar macr s en función de si e stán firma as digital ente por un progra ador incluido en su li sta de fuentes de con ianza. La con iguración e segurida d de macr s se pued cambiar e n el Centro de confianza (botón de Micro oft Office , bot n Opcion s de Exc l , categoría Centro e confia za, botón Configura ión del C ntro de c nfianza, c ategoría C nfiguraci n de ma ro s . O bi n, en la fic ha Progra ador , en el grupo C digo , bot n Segurid d de ma ro s ). Sin mbargo, si trabaja en una empr sa, puede que el ad inistrador el sistem haya ca biado la onfiguraci n predeter minada e i mpida que los usuari os modifi uen la con iguración.


153


Utilice esta config uración d macro s

Con este objetivo

Deshabilitar to as las macro s sin notifiicación

Utilice este ajuste si no con ía en las macros. e deshabilit n todas las macr s contenidas en l os document s, así co o todos los avisos de segurid ad que infor an de qu las macr s están d shabilitad s. Si hay do umentos con macros sin firmar ue consid ra de confia za, puede colocar e os docum ntos en u na ubicación de confianza. Se per mite la eje ución de l os document s situado en ubica iones de onfianza in que los c mpruebe l sistema e segurid d del Cen ro de confianza.

Deshabilitar to as las macro s con noti ficación

Ésta es la configur ción pred eterminada . Utilícela si desea de habilitar la s macros pero desea recibir avis os de seguri ad si exist n macros. De esta manera, puede elegir cuá do habilita r las macr s caso por caso.

Deshabilitar to as las macro s excep to las firmad as digital ente

Este aju te es el mismo

Habilit ar tod s las macro s (no recom endado, puede ejecut arse código posibl emente peligroso)

Utilice est configuración para p ermitir tem oralmente la ejecución de todas las macro . Puesto ue con e te ajuste el equipo es vulnerable a código posibleme te peligroso, no es re comendabl e utilizarlo de man ra permanen te.

Confiar en el a ceso al model o de ob jet o de proye tos de VB A

Esta configuración programa ores.

la opción Deshabili ar todas las macr s con no tificación , a excepción de que, si la macro stá firmad digitalme te por un edi tor de confianza, podr á ejecutars e si ya con ía en el edit r. Si el editor no es d confianza, se le notifi ca este hecho para qu , de esa anera, pueda elegir si desea habilitar las macros fir adas o onfiar en el editor. Todas las m cros no fi rmadas di italmente se deshabilit n sin ning na notifica ción.

está

ue el d

irigida

s ólo

a

los

Firmas digitale y cómo unciona versió Office 20 7 utiliza la tecnología Authentic de de Mic osoft para permitir a l os creadores de macros firma digitalme te un archivo o un proyecto e macro. El certific do utiliza o para cr ar esta fi ma confir a que la macro o l docume to proceden del fir ante y la irma certifi ca que la macro o e l documen o no se han alterado posterior ente. Despu s de instalar su certifi cado digita l, puede fir ar archivos y proyectos de macr o.

154



Firmar macros digitalmente Sólo d bería firm r las macr s una vez probada la solución y lista para s u distribuci ón ya que, siempre ue se modifica de al una mane a el códig de una m acro firma a, se quita su firma digital. Sin embargo, si posee el certificad digital en su equipo, el proyec o de macro se firm rá de nu vo autom ticamente al guarda lo. Si desea impedi que los suarios d su soluci n cambie automáti amente su proyecto de macro e invalide así su fir a, bloquee el proye to de mac ro antes d firmarlo. u firma digital sólo i dica que g arantiza q e este pro ecto es seguro. No d muestra que es el autor del pr oyecto. Por tanto, bloqueando s u proyecto de macro no se impi de que otro usuario reemplac la firma digital po otra firma. Los administrador es corpor tivos deb n volver firmar las plantillas y los co plemento para po er controlar con exa titud lo qu los usuarios ejecutan en sus eq ipos. Si crea un compl mento qu agrega c digo a un proyecto d macro, el código de be determinar si el proyecto stá firmado digitalm nte e informar al u uario de l as conse uencias qu e puede te er cambiar un proyec to firmado.

Gra ar un nuev macr Si eje uta frecu ntemente una tare en Micr soft Excel, puede automatizarla media te una ma ro. Una macro consi te en una erie de co andos y f unciones q ue se almacenan en un módulo de Visual asic y que puede eje utarse sie pre que s ea neces rio ejecut r la tarea. Podemos crear macros para borrar el ontenido de nuestr detalle en una fact ra, para imprimir una tabla o para modi icar el zo m rápida ente. Es importante mencionar que usted puede cre ar macros directame te desde el Editor de Visual B sic, aunq e es necesario conta r con con cimientos de progra ación y c nocer los bjetos dis onibles a rogramar n Excel. Para uestro ej mplo, se nos pide crear una macro q e “limpie” los valor es ingres dos en nu stra profor ma (en el l ibro Profor a). Es decir, la macr o, debe de ar el formulario listo para ingres ar nuevos atos. PRIMERO: Plani ique corr ectamente la secue cia de pasos que d eba realizar su ma ro.

Si des a automat izar su tare a, debe conocer exactamente lo pasos ne esarios p ra hacerl . En el eje plo, se debe realizar la siguient tarea: • • •

Llevar el i dicador a l celda de los kilogra os (C7) y orrarla Seleccion r las celda H1:H3 y b rrarlas. Finalment , llevar el i dicador n evamente a la celda 7

SEGU DO: Grab e su macr .

Aquí, ebe especificar un N mbre para la macro indicar si desea que dicha ma ro trabaje en cualqui er libro de xcel o sól en el libro activo. 1. En la ficha Programado r en el grupo Códi o haga cli en Graba Macro


155


2. En esta ventana, especifique n nombre para la acro. En nuestro caso Limpiar . demás, in ique un t cla de método abrevi ado, en nuestro caso la letra h y fi almente indique el á bito. En n estro caso será Este libro . l

3. Al hacer lic en Ac eptar , Excel comien a con la grabación de todas las acciones que usted r alice en la hoja de cál culo. TERC RO: Reali ce la secu ncia que esea auto matizar

Todo l que usted haga a artir de ah ora se grabará en su macro. R alice paso a paso l tarea a automatizar. Una ve concluida haga clic en el b tón Deten er graba ió n . Para el ejemplo: 1. 2. 3. 4.

Lleve el in Seleccion Finalment Haga clic

icador a la celda de l s kilogramos (C7) y bórrela. las celdas H1:H3 y bórrelas. , lleve el indicador nu evamente la celda C7 n el botón Detener Grabación

Para jecutar na macr Para robar si la macro unciona c orrectamen te, modifi ue los v lores de su profor a, elija un tipo de ser icio, selec ione una iudad, etc. Para ejecutar la •

acro por la tecla de método abre iado:

Pulse la c mbinación de teclas asignadas a su macro : Ctrl +

Otra forma de eje utar una

etra

acro es a t ravés de:

1. En la ficha Programado r en el grupo Códi o haga cli en Macro s

156



2. Seleccion la macro usar y ha a clic en el botón Eje utar .

Al eje utar la ma cro, obser e que las celdas vin uladas se eliminan regres a la celda C7.

el indica or

Asign su macro a un botón de comandos Una v z creada y probada s u macro, se asignará la macro a un botón e comand os que se dibujará e su formul rio. En la oja1, reali e: 1. Dibuje un otón usa do la barra Formulari os . 2. Al soltar l mouse, observe q e se pres enta una entana d nde se pide relacionar la macro con el botón dibujado.


157


3. Seleccion la macro impiar y haga clic en Ac ept ar . 4. Modifique el texto del botón. Su ormulario ebe tener l a siguiente apariencia :

5. Para prob r el botón, modifique l a proforma y luego ha ga clic en ste botón.

Vea s macro con el editor. Como a se indic , una macro es una secuencia de comand os. Usted uede ver l os comandos insertados en la acro. 1. Haga clic n Macros de la ficha Programa or en el gr upo Códig o

2. En esta ca ja de dialo o, selecci ne la mac ro creada ( Limpiar ) el botón Modificar ...

158

haga clic en



3. Observe ue Excel abrirá el comandos almacena os.

ditor de

isual Basi c con la

ecuencia de

Obser e las sent ncias y c ldas absol utas de l macro Li piar . Est s comandos realiza diferente tareas, po r ejemplo: Range(“C ”).Select

(selecciona la elda C7)

Selection. learConte s

(eli ina el con enido de l celda sel ccionada)

4. Para salir el editor de Visual Ba sic, elija la opción Ce rar y volv r a Excel el menú Ar c iv o .


159


Inclus , si conoce progr amación, podrá ag regar có igo para mejorar el funcio amiento d la macro. Por ejemplo, regrese al editor d Visual Basic y escriba esta s ntencia an es del com ando END SUB

Range(" F4").Val ue = Ra ge("F4" ).Value + 1 Regre e a Excel haga clic en el botón Nueva Pr forma, observe que cada vez que hace clic en este otón, la ce lda F4 se i crementa. Refer ncias Relativas Para l grar que las celdas e pleadas en su macro sean cons ideradas c mo relativ s, debe ctivar el otón Usar Referenci as relativ s . Usted puede co binar celdas absolu as y relativ as en una acro.

Para v r un ejem lo: se pide crear una macro que permita lle ar el indic dor de cel da a la f ila siguie te del últi o registro ingresado. Es decir al ejecutar la macro, el indicador debe se r llevado a la celda A8 (vea la im gen)

Diseñe la hoja de cálculo y lu ego: 1. Empiece l grabación de una m cro 2. En la vent na Macro, escriba el ombre par a su macr Demo 3. Haga clic n Ac ept ar Luego, siga las in trucciones para resolver el caso : 1. Haga clic n la celda 4 2. Active el b otón Usar eferencia r elativas

3. Pulse la te cla End + Flecha Ab jo 4. Pulse nue amente la tecla Flec a Abajo 160



5. Apague el botón Usa Referenciia relativa 6. Detenga l grabación . Ejecut la macro observe c omo está t abajando. Vea su macro por el editor:

Obser e que esta macro pos ee sentenc ias de Visu al Basic co n celdas a solutas…

Range(“ A4” ).Select ... y otr as con cel as relativa …

Selectio n.End(x lDown). elect Para v r como trabaje, en su hoja de cálculo, escri a valores n la fila 8 Ahora, lleve el ind icador a un a celda, por ejemplo a la D3. Ejecut la macro. Observe q e el indica dor se des laza ahora a a la fila . Elimi ar una

acro

Si des a eliminar alguna de l as macros creadas, d be usar: 1. En la ficha Programado r en el grupo Códi o haga cli en Macro s

2. Seleccion la macro haga clic n el botón Eliminar


161


Planti las Una d las forma s de imple entar sol ciones a c asos o pro blemas es ecíficos e a través de crear pl antillas y f rmularios. Estas herr amientas ermiten c ear hojas de cálculo rápidamente, usar ojas modelos previ mente cr adas, cre r soluciones autom tizadas pa ra problem s de negocios, etc. Usar lantillas redefini as Excel, iene con lantillas pr definida, listas para usar. Para u sar estas plantillas: 1. Haga clic n el botón Microsoft Office 2. Haga clic n Nuevo 3. A continuación, bajo l a sección lantillas , lija Plantil las instaladas 4. Al lado de echo elija or ejemplo Informe d e gastos y haga clic n Crear

5. Se muestr a:

162



6. Observe ue en esta plantilla hay texto y formato predefinido, así co o fórmulas n algunas celdas. Además la ho ja está protegida y muchas celdas contienen comentarios y están validadas. Crear plantillas personalizadas Una plantilla es n libro co mún y cor iente que presenta u n contenido y format os especí icos y q e puede tilizarse c mo modelo para otr s libros similares. Una plantill puede in luir texto estándar como encabe zados de ágina, rót los de fila y colum as, macro de Visual Basic y barras de her ramientas ersonaliza das. Una vez creada una plantil la, puede a brirse como cualquier libro y pu den realiz rse todos los cambi s que se desee. PRIMERO: inserte el texto estándar ( títulos, en abezados, comentarios, etc.).

n

nuestr ejemplo, ebemos e cribir la ta la de la fig ura:

SEGU DO: escriba fórmula s, incluya las fórmulas necesa rias para

olucionar un

proble a o tarea específico En el e jemplo las fórmulas i plementad as son:

C3:

=Ho ( )

Monto:

= E9 * F9

Subtotal: =Su a(G9:G 3) IGV:

=G14 * 19%

Total:

=G14 + G15

TERC RO, apliq e formato

(formato de celdas, de númer os, format condicional, etc.). En el ejempl o se han a licado los siguientes ormatos p rsonalizados:


163


Columna

Formato Personalizado

Precio:

00,00

Cantidad

00

Monto

00,00;;

(Este último formato ermite que si las celd s contiene n 0 no se visualice) Subtotal, I V y Total: formato d e moneda. CUAR O: valide el ingreso de datos. Por ejempl o, debe v lidar que l as celdas de

cantid d y precio se escriban sólo valor s numéric s. 1. Seleccion las celdas de la colu na de Pr ci o 2. Elija el me nú Ficha D tos, Valid ación 3. En esta ventana, mo ifique el cu adro Perm itir a Deci al .

4. Especifique un valor ínimo (0) máximo ( or ejempl 100). 5. Además, alide la columna el campo Cantidad para que sólo sopo te números enteros entr e 1 y 30. QUINTO: Proteja su Hoja. D esta forma evita que los usuari s modifiquen los rótul os

y fórm las de la s olución qu acaba de crear. Rec erde deb desbloqu ar las celdas que el usuario SI UEDE M DIFICAR. Así, p ra el ejemplo las cel as a las q ue el usuario debe te ner acces son: Clie te (D5), irección (D 6), las celd as del Pre io, etc. SEXT : Lo más i portante sencillo, guarde el lib ro como pl ntilla:

1. Haga clic n botón Mi crosoft Office y haga lic en Gua dar Como 2. En el cuadro Guarda como tip , seleccio e la opció Plantilla

164



3. Observe que al elegi el tipo Pl ntilla, Excel cambia la ruta don de se guar da dicho libro . 4. Haga clic n Guardar . Utiliz r plantill s person lizadas Una v z creadas us plantill s es muy f cil utilizarl s: 1. Haga clic en el botó n Microso t Office 2. Haga clic en Nuevo 3. En el cua dro selecci one Mis Pl antillas…

4. Luego ha ga clic en l a plantilla q ue usted c eó y pulse Ac ept ar


165


Preg ntas e Rep so 1. ¿Cómo ac iva la ficha Programa or? 2. Mencione algunos de los eleme tos que puede Mostrar/Ocultar a. .................................................................................................... b. .................................................................................................... c. .................................................................................................... 3. ¿Cuáles s n los paso s para cre r una nuev a Macro? 4. ¿Qué utili ad tiene el botón

?

5. ¿Cómo asigna una macro a un otón de co ando? 6. ¿Qué suc de cuando se muestr :

166


Capí ulo

Herra ient s d rev sión En este capítulo apren eremos a: evisar el ontenido d una hoja de cálculo signar co entarios a las hojas d e cálculo roteger y ompartir in formación

Programa


16


Utili ad de las he ramie tas de revisi n Cuand se acerc una fech límite, no suele hab er tiempo uficiente para revisar la ortografía de un document . Microso t Excel of rece herramientas q e ayudan a corregir los errores más rápi amente. Para ello puede u sar las her ramientas de la ficha Revisar e el grupo propor iona Microsoft Excel 007

evisión que

Ortografía En la mayoría d los casos, la revisión ortográ ica es muy sencilla en todos los progra as de Microsoft Office. Presio e F7 y, a continuación, utilice el cuadro de diálog o el pane l de tareas que apar ce para recorrer el archivo o el mento en el que es é trabajan o.

Sin embargo es b ueno tener en mente stas consi eraciones: •

•

•

168

Haga clic entro de u na hoja de cálculo qu esté edit ndo para r evisar la h ja activa co pleta, incluidos los val ores de las celdas, comentarios e las celd s, gráficos incrustados, uadros de texto, botones, encab ezados de página y pi es de página. Excel no revisa las hojas de cálculo pr tegidas, l s fórmulas ni el te to resultante de una fórmula. Ad más, si la barra de fórmulas stá activa al revisar la rtografía, xcel sólo r evisará el c ontenido d la barra de fórmulas. Opcionalmente, para revisar sólo una par e específi a del text , seleccione dicha part


apítulo VIII: Herramientas de revisión

Refer ncia, Sin nimos y raducir Puede consultar n libros, e ciclopedia , etc. Algunos términ s de su h ja de cálc lo para llo bastar que sel ccione la celda qu contiene el términ y pulse la combi ación Alt + clic para a ctivar el pa el de Referencia

En est panel usted puede uscar Sin nimos incl uso traduci r de un idi ma a otro el texto de la celda seleccionada. Usted debe desplegar el cu adro comb inado que se muestr a en la ima gen para elegir la refe rencia corr spondient según su necesidad.


169


Asig ación de co

entari s

En Mi rosoft Off ice Excel, puede agregar una nota a u a celda i sertando un comen ario. Pue e editar l texto de los comentarios y liminar lo que ya no necesite.

Agregar un co entario 1. Haga clic n la celda en la que desea agregar el comentario. 2. En el gr po Comentarios de la ficha Revisar , haga cli comentari o.

en Nue o

3. En el cuad ro de texto de comentario, escriba el texto del comentario. En un co entario, Excel muestr automáti amente el nombre que aparece en la casilla Nombre e Config ración gl obal de ffice de la catego ía Personali ar del cuadro de diál go Opcio es de Exc el (Botón de Micros ft Office , botón Op ciones de Excel). Si lo necesita , puede editar el nom re de la casi lla Nombr e. Si no desea utiliz r un nombre, selec iónelo en el comentari y, a conti uación, pr sione SUPR.

4. Para dar f rmato al t xto, selec iónelo y u ilice las o ciones de ormato de la ficha Inici del grupo Fuente.

Las opcio es Color e relleno Color de fuente del grupo Fu nt e no están disponible para text de come tario. Par modificar el color d l texto, haga clic con el botón sec ndario en el coment rio y, a co ntinuación, haga clic en Comentar io de form to .

170



5. Cuando termine de e scribir y dar formato al texto, ha a clic fuer de la casilla del comen tario.

Un triángulo pequeñ de color r ojo en la e squina de la celda in ica que é ta tiene un omentario. Si deja el cursor s bre el triángulo, se isualizará el comentari .

Cuando or dena, los comentarios se ordena junto con los datos. Sin embar o, en los inf rmes de t bla dinámica, los co mentarios no se des lazan con la celda cuando modific el diseño del informe.

Edita un com ntario 1. Haga clic n la celda que contie e el come tario que d esea edita . 2. A continuación: 

En la ficha Revisar , en l grupo Co mentarios , haga clic en Modific ar co entario. Mo dificar co entario stá disponible en el grupo Co entarios en lug r de Nue vo comen tario cuando se sel ecciona una celda que contiene un c mentario.



En la ficha Revisar , en el grupo Comentarios, haga clic en Mo strar/Ocul ar comen ario para isualizarlo y, a continuación, ha ga do le clic en l texto del omentario.

3. En el cuadro de texto de comentario, edite l texto del comentario.

Elimi ar un co entario 1. Haga clic n la celda que contie e el come tario que d esea elimi ar. 2. A continuación: 3. En la ficha Revisar , n el grupo Comentari os , haga clic en Elimi nar .


171


Mostr r los co entarios Para que el com que c ntiene el Come tarios de celdas en la hoja

ntario per anezca visible en la celda, pu omentario y hacer clic en Most rar/Oculta la ficha Revisar . Para visualiz r todos l e cálculo, haga clic e n Mostrar t odos los

Herramien as de ontrol de ca

de seleccionar la cel da comenta io del grupo s comentarios con s us omentario s.

bio

Para impedir q ue un u uario ca bie, mue a o eli ine por accidente o preme itadament datos im ortantes, uede prot ger deter inados ele mentos de la hoja d cálculo o l ibro, con o sin una co traseña La pro ección de elementos del libro hoja no ebe confu ndirse con la segurid ad media te contras ña de nivel de libro. a protecci n de elem ntos no pr tege un li ro frente usuarios alintencionados.

Prote er elem ntos de l hoja de cálculo 1. Seleccion las hojas e cálculo ue desea roteger. 2. Para des loquear c ldas o rangos de f orma que otros usu rios pued an modificarl s, haga lo iguiente: 1. Seleccione cada celda o rango que esea des loquear.

172



2. En la ficha Ini io , en el grupo Celdas, haga cli en Formato y luego en Fo mato de c eldas.

3. En la ficha Pr otección, esactive l casilla de verificació Bloquea a y, continuaci ón, haga clic en Ac ep tar .

3. Para ocult r las fórm las que no desea mo trar, haga lo siguiente : 1. En la ficha Pr teger , del cuadro de diálogo an terior activ la casilla de ver ificación O ulta y, a continuación, haga clic en Ac ept a . 4. En la ficha Revisión, en el grup Cambios , haga clic n Proteger hoja.

5. En la lista Permitir a los usua ios de es a hoja de cálculo, seleccione l os elementos que desee que los usuarios pue an cambia r.


173


Des active casi lla veri ficación

esta de

Sel ccionar bl o ueadas

eldas

Para impedi r que los

suarios

Muevan el p untero a c d verificación Bloqu c adro de diálogo p edetermin da, los u bloqueadas.

ldas que t ienen activada la casi lla ado en l ficha Pr tección el ormato e celdas . De for a uarios pu den selec ionar celd as

Sel ccionar eldas des bloqueadas

Muevan el untero a celdas qu tienen d sactivada la c silla de v rificación loqueado en la fich Protecci n d l cuadro de diálog Formato de celdas. De for a p edetermin da, los u uarios pu den selec ionar celd as d sbloqueadas, y pueden presi nar la tec la TAB para d splazarse por las cel as desblo queadas d una hoja de c lculo protegida.

For ato de c ldas

Cambien cu alquiera d las opci nes de lo cuadros de diálogo For ato de c eldas o F rmato co dicional . Si a licó forma os condici nales ant s de prote er la hoja de c lculo, el formato s guirá ca biando si un usuario e pecifica u valor que satisfaga una condici n diferente.

For ato co l mnas

Utilicen cualquiera d los comandos de formato de c lumnas, incluidos los que permi en cambiar el ancho de c lumna u ocultar las columna s (ficha Inicio, grupo Celdas, bot n Formato ).

de

For ato de fil as

Utilicen cual quiera de l os coman os de for ato de fil s, incluidos lo que per iten cam iar el ancho de fila u o ultar las filas (ficha Inicio, grupo C ldas, botón F rmato).

Ins rtar colum nas

Inserten columnas.

Ins rtar filas

Inserten filas.

Ins rtar hi p rvínculos

Inserten n evos d sbloqueadas.

Eli

inar colu

nas

hipervínculos, incluso

en

celdas

Eliminen col mnas. ota Si El iminar col umnas está protegida e Insert ar c lumnas no está ta bién prote gida, un u uario pod ía insertar colu nas que o puede el iminar.

174



Eli

inar filas

Eliminen fila . ota Si Eliiminar fila está prot gida e Ins ertar filas no e tá también protegida, un usuario podría ins rtar filas que n puede eli inar.

Ord enar

Utilicen cualquier co ando par ordenar datos (ficha Datos, grup Ordenar filtrar ). ota Los usuarios o puede ordenar rangos que c ntienen celdas en una hoja de cálculo protegi a, independien emente de esta confi uración.

Usa r Autofiltr

Usen las fl chas desp legables p ara cambi r el filtro en r ngos al ap licar autofilt ros. ota Los suarios no pueden a licar ni qui ar rangos de a tofiltros en una hoja d cálculo protegi a, independien emente de esta confi uración.

Usa r inform s tabl a dinámic

Mo ificar objeto s

de

Apliquen f rmato, cambien el diseño, ctualicen o modifiquen de algún otro mo o inform s de ta la dinámica, o reen nuev s informe . Realicen alg uno de est s procedi ientos: 





Mo ificar esc narios


Efectúen cambios n los obj tos gráfic s, incluidos mapas, gráficos incrustad s, formas, uadros d texto y controles, que no se bl quearon a ntes de pr teger la h ja de cálculo. Por ejemplo, si una hoja de cálculo iene un otón que ejecuta una macro, uede hac r clic en l botón p ra ejecutar l a macro, pero no puede eliminar el botón. Efectúen cambios, como modificar el formato, en un gráfi co incrustado. El gráfi co sigue actualizándo e cuando cambian s us datos de origen. Agregue o editen omentario .

Vean escenarios que ha ocultado, realicen cambios en e cenarios rotegidos rente a modificacion s y elimin en dichos esc narios. L s usuario pueden cambiar l os v lores de l s celdas odificadas, si las cel das no est án p otegidas, agregar n evos escenarios.

175


Ac ti ve esta asilla de v erificació

P ra impedi r que los u suarios

Con tenido

E ectúen cambios en elementos que forman parte el gr áfico, como series d datos, ej s y leyendas. El gráfico c ntinúa reflejando los cambios ue se efe túan en s us d tos de origen.

Obj to s

E ectúen ca bios en bjetos gr ficos, incl idas form s, c adros de texto y controles, a m nos que d sbloquee l os o jetos ante de proteg r la hoja d e gráfico.

En el cua ro Contra eña para desproteg er la hoja, escriba una contraseña para la hoja, hag clic en A eptar y, a continuaci ón, vuelva a escribir l a contraseña para c nfirmarla. La contra eña es opcional. Si no prop rciona un contrase a, cualquier usuario p ede desp oteger la hoja y m dificar los elemento protegid s. Asegúrese de elegir una contraseña que sea fácil d recordar, porque si la pierde, no podrá te er acceso a los ele mentos pr tegidos d la hoja de cálculo.

Prote er elem ntos del libro 1. En la ficha Revisión, en el grup Cambios , haga clic n Proteger libro.

2. En Proteg er en el lib ro , siga uno de los pr cedimient s siguient s:





176

Pa a proteger la estructura de est ructura y entanas

n libro,

aga clic en Proteg er

Pa a que las ventanas el libro tengan siem pre el mis o tamaño y po ición cada vez que s abra el li ro, active la casilla d e verificaci ón Ve tanas. Programa Nacional de Informática


ctiv e est asilla de erificació n structura

Para impedir que los u uarios 





Vean h jas de cálculo que ha a ocultado. Muevan , eliminen , oculten o cambi n los nombre de las hoj as de cálc lo. Inserten nuevas hojas de c lculo u ho jas de gráfico. Los u uarios p drán ins rtar un gráfico incrustado en una hoja de c lculo exist nte.











Muevan o copien h ojas de cál culo en otr libro. En info mes de tabla dinámi ca, muestr en los datos de origen d e una cel a en el á ea de datos, o muestren páginas de campos de página n hojas de cálculo ind ependient s. En esc narios, cr en un inf rme de resumen de escenario. En el c mplement Herramie tas para a álisis, usen la herramie tas de an lisis que c olocan los resultados en u na nueva h oja de cálc ulo. Graben nuevas macros. Si ejecuta una macro que incluy una operaci n que no e puede r alizar en u n libro protegido, aparec rá un me saje y la macro dejará de ejecutars e.

entanas





Cambien el tam ño y la posición ventanas del libro uando ést se abre.

e las

Muevan , cambien de tama o o cierr n las ventanas. Los us arios podrán oculta r y mostr ar las ventanas.


177


3. Para impedir que otr os usuarios quiten la protección del libro, en el cua ro Contrase a (opcion al), escriba una contraseña, haga clic e n Ac ept ar y vuelva a escribir la co ntraseña para confirmarla.

Compartir libr

Existe muchas f rmas de c mpartir, a alizar y co unicar inf rmación e presarial en Micros ft Office Excel 2007. El modo e que decid compartir los datos ependerá de muchos factores, como la f rma en que desee q e otros usuarios vea los datos o trabajen con ello s. Por eje plo, ¿desea impedir que se odifique l información confid ncial o im ortante, o desea per itir a los usuarios q e cambie y editen los datos? Tal vez necesite co partir los datos con usuarios ue no tie en Microsoft Office Excel o q e trabajan con versi nes diferentes de E cel. Quizás sólo des ee compartir una ve sión fija d un libro que pueda enviarse or correo electrónico e imprimirse fácilm nte.

Permitir que v rios usuarios modi iquen un libro al

ismo tie po

Si tien un libro e n el que de sea que va rias perso as agregu n o modifi uen datos al mismo tiempo, p ede confi urar y gu rdar el lib ro como " ompartido" y ponerlo a disposición de e os usuarios en un r curso compartido de red (no e un servi or Web).

178



Utilice la característica Com artir libro cuando: Desee que varios usuarios puedan modificar los d tos de un libro al mis o tiempo y convenga en que l s datos or iginales de l libro se alteren, ya sea modifi ándolos, el iminándolo s o incorpo rando nue os datos. Tenga un recurs de red di ponible en el que al acenar el libro y al q ue los us arios tiene acceso. Desee conservar un registro de los cambios efectuados en el libro. No prevea que s vayan a odificar la siguiente caracterí ticas, que no se pu den cambiar después de com artir el lib ro: celdas combinad s, format s condici nales, vali ación de datos, gráf icos, imág nes, objetos (inclui os los de ibujo), hip rvínculos, escenario , esquemas, subtotal s, tablas de datos, i formes de tabla diná ica, protección de libr os y de hoj as de cál ulo, y mac os.

•

•

• •

Al gua dar un libr comparti o de Offic Excel 2007 en un fo mato de a chivo de una versió anterior, e desactiva el uso c mpartido el libro y se pierde l historial de revisio es que describe los onflictos y las soluciones. Si ne esita guar ar el libro en un for ato de un versión a terior, tal vez sea con eniente g ardar prim ro una co ia del historial de re isiones. Como autor original del libro, puede pr pararlo par a compartirlo escribie do los datos que d ben estar presentes y aplicándoles formato. Cuand termine, haga clic en Comp rtir libro n el grup Cambios de la fich Revisar , seleccione las opciones que desee y, a continuación, guarde el libro en un ecurso co partido de red que e té disponible para lo s usuarios quienes v a dirigido.

Cada ez que gu rde el libr compartido, se le avi sará de lo cambios r ealizados or los de ás usuari s desde la última ve que lo gu rdó. Si desea dejar a bierto el li ro compartido para realizar un seguimien to de los rogresos, puede hac er que Ex el actuali e los cam ios autom ticamente, a interval s de tiempo determin dos, tanto si guarda el libro pe sonalment como si no. Al guardar cam ios en un libro co partido, p ede que otra persona que e té modifi ando el lib o haya gu rdado cambios que a ecten a las mismas c ldas. Si es el caso, los cambi s entrará en conflicto y ap recerá el cuadro d diálogo de resolu ión de con lictos, en e l que podr elegir qué cambios desea cons rvar.

Compartir un libro 1. Cree un n evo libro e escriba lo datos que desea pro porcionar o abra un li ro existente que desea que esté disponible para su modificación por varios usuarios. 2. En la ficha Revisar , n el grupo Cambios, aga clic e Comparti r libro.


179


3. En la fic a Modifi ación, a tive la c silla de modificación por arios us arios a la vez. combinar libros.

erificación Permitir la sto tamb ién permi te

4. En la fich Uso ava zado, seleccione la opciones que desea utilizar p ra realizar u seguimie to de los ambios y actualizarl s; a continuación, ha ga clic en Ac ptar . 5. Siga uno de los proc dimientos iguientes: 



Si es un libr nuevo, escriba un arc hivo.

ombre en el cuadro Nombre

Si e trata de un libro e istente, haga clic en

e

ceptar para guardar el

libr o, haga cli en el bo t n de Micr osoft Offic e ha a clic en Guardar co o.

y, a continuaci n,

6. En el cua ro Guard r en, sele cione una ubicación de red a la que pued an tener acceso los usu rios previs os y haga lic en Gua rdar . Debe utili ar una car eta de red compartid , no un se vidor Web. 7. Si el libro contiene vínculos a otros libr s o docu entos, c mpruebe l os vínculos y actualice l s que est n rotos; p r último, h ga clic en Guardar en la barr de herra ientas de acceso rápido, o presione CTR +G.

Todos los usuari s con acc so al rec rso compartido de red tienen a ceso total al libro c mpartido a menos ue bloqu e celdas proteja la hoja de cálculo p ra restrin ir el acceso.

180



Prote er y co partir un libro Si el li ro ya está compartid y desea signar un contrase a para protegerlo, de be anular el uso compartido del libro media nte el proc dimiento s iguiente: 1. Ha a que todos los demás usuarios guar den y cierren el li ro co partido pa ra evitar q e pierdan u libro. 2. En el libro co partido, e la ficha R visar , en l grupo C mbios, haga clic en Comp rtir libro. 3. En la ficha Ed ición, ase úrese de que es el ún ico usuario que aparece en la lista Los siguiente usuarios tienen abi erto este li br o. 4. De active la casilla de verificación Permiti la modi icación p or va ios usuari os a la vez. Esto tam bién permi te combin ar libros. Si sta casilla de verific ción no e tá disponi le, debe d esproteger la hoj de cálcul antes de esactivar la casilla de verificación. 5. Cu ndo se le pregunte sobre los ef ectos que esta acción puede te er en los demás usuarios, haga clic en Sí.

Ahora ue el libro dejó de es ar compartido debe h cer lo sigu iente: 1. En la ficha Revisar , n el grupo Cambios, haga clic e n Proteger y Compar tir libro.

2. Active la casilla Com artir con ontrol de cambios .


181


3. Para exigi que otros usuarios p oporcione una contr seña para desactivar el historial de cambios o anular el uso c mpartido el libro, n el cua ro Contrase a (opcion al), escriba una contraseña, haga clic e n Ac ept ar y vuelva a escribirla pa a confirmarla. 4. Si se solici ta, guarde el libro.

Contr l de ca bios Al esta r comparti o un libro xiste la n cesidad d controlar/ ostrar los cambios que cada usuario del l ibro haya r ealizado, ara esto siga los sigu ientes pas s en un li ro compartido: 1.

En la ficha Revisar , n el grupo Cambios, aga clic e Control después en Resaltar cambios .

2.

En la lista Cuándo, s leccione Todos.

3.

Desactive las casillas de verifica ión Quién y Dónde.

4. Active la casilla de v rificación clic en Ac ptar .

e cambio y

ostrar cambios en una hoja n ueva y haga

Tambi n puede u ilizar la op ión Ac ept r o recha ar cambio s para real izar el cont rol de ca bios

182



Esta o ción muestra los cambios y le p rmite deci ir si se aceptarán o n


183


Preg ntas e Rep so 1. ¿Cuál es la utilidad el uso de las herra ientas de Sinónimos, Traducir, tc.?

rtografía Referencia:

2. ¿Cuál es l utilidad d insertar c mentarios en las celd as? 3. ¿Cómo in erta un co entario? 4. ¿Cómo elimina un co entario? 5. ¿Cómo m estra todo los come tarios de u na hoja de cálculo? 6. ¿Cuál es l procedimi ento para ompartir u libro? 7. Cuando mpleo co entarios en una t bla dinám ica. ¿Los comentarios también s n dinámic s? 8. ¿Es posible compartir un archiv de Excel n un servi or Web? 9. La imagen siguiente. ¿Qué signi ica?

10. Al usar la protecció para Ho as de cál ulo todas las celda de mi h ja permanec n bloquea das, que hago para q ue solo en un rango de celdas se permita la edición de datos.

184


CURSO EXCEL AVANZADO SENATI.pdf

Recommend Documents