Manual Excel Inicial

7/25/2019 Manual Excel Inicial

1/94

Prof. Edwin Chuquipul PizarroGRUPO IDAT | www.idat.edu.pe

Excel InicialPROGRAMA DE EXTENSION Y ESPECIALIZACION


2/94

2

Prof. Edwin Chuquipul Pizarro

UNIDAD 1INTRODUCCION

QU ES EXCEL

Microsoft Excel permite analizar, administrar y compartir informacin de ms formas quenunca, lo que le ayuda a tomar decisiones mejores y ms inteligentes. Las nuevas herramientas deanlisis y visualizacin le ayudan a realizar un seguimiento y resaltar importantes tendencias dedatos. Obtenga acceso fcilmente a datos importantes dondequiera que vaya desde prcticamentecualquier explorador web.

APLICACIONES EN EXCELRealice comparaciones rpidas y efectivas

Excel ofrece nuevas caractersticas y herramientas eficaces para ayudarle a descubrir patrones otendencias que pueden llevar a decisiones ms informadas y mejorar la capacidad de analizargrandes conjuntos de datos.

Obtenga anlisis eficaces desde el escritorio

Los refinamientos de Excel y las mejoras en su rendimiento le permiten llevar a cabo su trabajocon mayor rapidez y facilidad.

Ahorre tiempo, simplifique el trabajo y aumente la productividad

Crear y administrar los libros es mucho ms fcil cuando puede trabajar como desea.

Incluso puede cargar sus archivos en Internet ytrabajar simultneamente con otras personas enlnea. Tanto como si produce informesfinancieros o administra sus gastos personales,Excel le brinda ms eficiencia y flexibilidad para

lograr sus objetivos


3/94

3


Rompa las barreras y trabaje en equipo de nuevas maneras

Excel ofrece formas simples de permitir que las personas trabajen conjuntamente en libros, locual sirve para mejorar la calidad de su trabajo. Lo mejor de todo es que incluso las personas conversiones anteriores de Excel pueden participar sin problemas.

Obtenga acceso a los libros en cualquier momento y lugarObtenga la informacin que necesita, en el lugar y del modo en que la desea. Ahora puede

obtener acceso fcilmente a los libros llevando la experiencia de Excel con usted y mantenerse alda dondequiera que est.

Acceder a Excel

1. Seleccione el botn Inicio y luego haga clic en la opcin Todos los programas

2. Luego elija la carpeta Microsoft Office y seleccin Excel

3. De manera opcional, puede escribir en el control Buscar del botn inicio la palabra Excel, estaforma es mas rpida que la anterior


4/94

4


DESCRIPCIN DEL ENTORNO DE EXCEL

MI PRIMER LIBRO

Los documentos en Excel se denominan libros. Un libro est compuesto por varias hojas declculoque es almacenado en el disco duro como un archivo de extensin .xlsx. En la parte inferiorizquierda de la ventana del libro existen unas etiquetas que permiten seleccionar la hoja de clculorequerida. Cada vez que abrimos un nuevo libro de trabajo este se abrir con tres hojas de clculo.

Hojas de clculo

Una hoja de clculoes una matriz bidimensionalque est compuesta por columnas(en sentidovertical) y filasen sentido horizontal). Las columnasse encuentran etiquetadas con letrasdesde laAhasta la XFD(totalizan 16,384 columnas). Las Filasestn etiquetadas con nmerosdesde el1hasta 1,048,576filas)

Una Celdaes la interseccin entre una columna y una fila, es donde se ingresa informacin. Todacelda tiene una direccin, conformada por el nombre de la columna y el nmero de la fila. Porejemplo: B4 .Tambin puede tener un nombre propio.

Bloque o Rango de Celda est compuesto por una o varias celdas seleccionadasque tienen como

caracterstica principal su forma rectangular. Un rango tiene una direccin, conformada por la


5/94

5


primera celda del lado izquierdo y la ltima celda del vrtice opuesto. Por ejemplo: C8:J20 (los :denotan un rango). Tambin puede tener un nombre propio.

Puntero de celdaes la celda especial de color inverso que se trasladapor toda la matriz y permiteacciones de desplazamiento, selecciny rellenoo copia de frmulas.

Desplazamientos y selecciones

Cambiar de celda activa es tan fcil como seleccionar otra celda de la hoja, ya sea con el mouseo con las teclas direccionales del teclado.

La siguiente tabla muestra las teclas que puede utilizar para moverse cmodamente por lasceldas de la hoja, utilizando el teclado:

Para Pulse Lo Siguiente

Ir hasta el extremo de la regin de datos actual CTRL + Tecla De Direccin

Ir hasta el comienzo de una fila Inicio

Ir hasta el comienzo de una hoja de clculo Ctrl + Inicio

Ir a la ltima celda de la hoja de clculo Ctrl + Fin

Desplazarse una pantalla hacia abajo AvPg.

Desplazarse una pantalla hacia arriba RePg.Desplazarse una pantalla hacia la derecha Alt + AvPg.

Desplazarse una pantalla hacia la izquierda Alt + RePg.

Ir a la siguiente hoja del libro Ctrl + AvPg.

Ir a la hoja anterior del libro Ctrl + Re Pg.

Ir al siguiente libro o la siguiente ventana Ctrl + F6 Ctrl + Tab

Ir al libro a la ventana anterior Ctrl + Shift + F6 Ctrl + Shift + Tab

Ir al siguiente panel F6

Ir al panel anterior Shift + F6

Desplazarse para ver la celda activa Ctrl + Retroceso

Seleccionar (rangos) celdas continuas y discontinuas:


6/94

6


Seleccionar columnas y filas:

Tipos de datosUna celda en Excel acepta tipos de datos constantes o frmulas.

CONSTANTES

Dentro de los datos contantes se agrupan los tipos de datos texto, numricos y fecha/hora.

1. Tipo de dato Texto

Un dato de tipo texto puede contener cualquier serie de caracteres (letras, nmeros y smbolos),es decir, es de naturaleza alfanumrica y puede tener hasta un mximo de 32.000 caracteres.

Un dato de tipo nmero puede incluir dgitos del 0 al 9 y los signos especiales + ( ) / %. Pordefecto los nmeros aparecen alineados a la derecha en la celda.

2. Tipos de datos Fecha/Hora

Un dato tipo fecha/hora es tratado como un nmero, correspondiendo a cada fecha el nmeroequivalente al de das transcurridos desde el 1 de Enero de 1.900 hasta la fecha en cuestin. A lahora le corresponde la fraccin resultado de dividir el nmero de segundos transcurridos desde las0 horas, por el nmero de segundos que tiene un da (86.400).

FORMULAS

Una frmula es una secuencia formada por: valores constantes, referencias a otras celdas,nombres, funciones, u operadores. La frmula se escribe en la barra de frmulas y debe empezarsiempre por el signo =. Las frmulas permiten realizar clculos, ms o menos complejos, con losdatos introducidos en la hoja de clculo.

GUARDAR LIBROCuando se ingresa informacin en un libro por primera vez, es recomendable guardar o

almacenar su informacin, para evitar prdidas posteriores de datos.

CASO PRATICO 01

Crear un libro para registrar las ventas de electrodomsticos de una tienda comercial, durante laprimera temporada de ventas.

1. Ubicado en Excel ingrese los siguientes datos considerando las operaciones de relleno,indicadas:

Por ejemplo elnmero 10equivale en

formato fecha alda 10-Enero-1900,y el nmero 10,75equivale enformato fecha alda 10-Enero-1900a las 18:00 horas.De esta forma esposible realizaroperacionesmatemticas conceldas quecontengan fechas uhoras.


7/94

7


2. Luego de modificar el ancho de columnas, considerando lo siguiente:

3. Modificar el alto de la fila 2, segn lo indicado:

4. Realizar la operacin rpida de la Autosuma para el rango [D5:H15],


8/94

8


Guardar el libro para tener registradas las ventas de los productos, en la carpeta Excel-Inicial delUSB, con el nombre Ventas_2011

1. Haga clic en el men Archivo

2. Luego seleccione Guardar

3. Especifique la carpeta, Excel-Inicial, del USB4. Digite el nombre, en este caso: Ventas_2011

5. Y luego haga clic en el botn Guardar


9/94

9


CASO PRATICO 02Ahora se requiere cerrar el libro, sin cerrar Ms-Excel


2. Luego seleccione la opcin Cerrar

CASO PRATICO 03Ahora se requiere abrir el libro Ventas 2014, que se grabo en el USB


2. Seleccione la opcin Abrir

3. Seleccione la carpeta Excel-Inicial, del USB

4. Seleccione el archivo que requiera abrir, en este caso: Ventas_2014

5. Y luego haga clic en el botn Abrir

CASO PRATICO 04Verifique la otra manera de abrir un archivo utilizando la opcin Reciente

1. Seleccione el men Archivo

2. Luego elija la opcin Reciente

3. Seleccione de la lista el archivo que requiera activar


10/94

10


4. Para salir de la aplicacin de Excel elija la opcin Salir

CASO PRATICO 05Verifique la otra manera de abrir un archivo utilizando la opcin Reciente

1. Seleccione el men Archivo

2. Luego elija la opcin Reciente

3. Seleccione de la lista el archivo que requiera activar


11/94

11


UNIDAD 2MANIPULANDO HOJAS DE CLCULO

Vamos a ver los diferentes mtodos de seleccin de celdaspara poder modificar el aspecto destas, as como diferenciar entre cada uno de los mtodos y saber elegir el ms adecuado segn laoperacin a realizar.

OPERACIONES BSICAS CON FORMATOS Y HOJAS

La hoja de clculo es una herramienta informtica, que nos ahorra muchsimo trabajo diario atodos aquellos que trabajamos habitualmente con nmeros, cifras, y datos. Una hoja de clculo nospuede servir para elaborar un presupuesto, para hacer facturas, para calcular las cuotas de unprstamo, para hacer un balance y una cuenta de resultados, o para llevar el control de notas deunos alumnos, por poner unos cuantos ejemplos. Todo aquello que sea cuantificable, y valorableeconmicamente, puede ser tratado, medido, ordenado y controlado, mediante una hoja declculo.

FORMATOS DE CELDAMuchas veces necesitaremos que lo mostrado en las celdas tengan determinado formato (por

ejemplo, podemos necesitar que un nmero de una celda se muestre en color rojo si es negativo).

Para modificar el formatode una celda o rango de celdas, luego de seleccionarlas, hacemos clicen un comando (caracterstica) de los grupos: Fuente, Alineacin, Numero y Estilos de la cinta deopcionescorrespondiente a la Ficha Iniciotambin es posible activar alguna caja de dialogo paraelegir otras caractersticas.

CASO PRATICO 01Active el libro Ventas_Artefactos.xlsx, en la Hoja1, se deben realizar varias operaciones de

cambio de formato de Fuente, Alineacin y Nmero:

1. La hoja1 mostrara originalmente lo siguiente:


12/94

12


2. La celda A1, tendr las siguientes caractersticas: Fuente: Brush Script Std, 18, Neg, Curs,Subrayado doble.

3. Seleccione el rango: [A2:H2], aplique lo siguiente: Combinar y centrar, Alinear en el medio, 16,Arial Narrow, Neg, Borde de cuadro grueso, Color de Relleno purpura claro, Color de Fuentepurpura oscuro.

4. Los encabezados [A4:H4] debern tener: Neg, Centrar, Alinear al medio, Ajustar texto, Todoslos bordes, Color de Relleno azul claro, Color de Fuente azul oscuro. El ancho de la columna Hser de 12.5 y en la interseccin de la fila 4 y 5 haga doble clic.

5. El rango [A5:H14] junto con el rango [B15:H15] debern tener: Todos los bordes. La celda A4se le asigna la Orientacin: Girar texto hacia arriba. Combinar y centrar el rango [B15:C15],

asigne el mismo formato de los encabezados al rango combinado. Luego de los cambios deformato de fuente y alineacin, se deber obtener:

6. Los cambios de formatos de nmero se aplicaran: el rango [D5:G14] tendr el Estilo millarescon un decimal, los rangos [H5:H15] y [D15:G15] se le asigna el Formato de numero decontabilidad Dlar (EE.UU) con un decimal, adems de negrita y con un Color de relleno purpuraclaro. Como resultado se mostrara:


13/94

13


7. Guardar los cambios (Ctrl + G)

CASO PRATICO 02

En el libro Ventas_Artefactos.xlsx en donde se requiere cambiar el nombre de la Hoja1 porDatos, as como tambin asignarle el color de etiqueta rojo a dicha hoja. La Hoja2 se llamaraInformes y el color de etiqueta ser Verde.

1. La manera convencional seria: En la barra de etiquetas haga clic derecho sobre una hoja, elegirCambiar nombrey coloque el nuevo nombre

2. La manera directa de cambiar el nombre a una hoja es:

3. Realice un procedimiento similar para cambiar el nombre de la: Hoja2 por Informes y coloqueel: Color de etiqueta verde.

4. Guarde los cambios y cierre el libro actual (Ctrl + F4).

MANIPULAR FILAS Y COLUMNAS

En el archivo Empleados_Taller.xlsx, se encuentran registrados los datos de los trabajadores del

rea de produccin de la empresa textil Yhassir & Co.


14/94

14


Las operaciones de insercin dependen de las selecciones (filas, columnas, celdas) de referenciaque haga el usuario. La manera formal de realzar inserciones implica utilizar de la Ficha Inicio, elGrupo Celdas y la orden Insertar, luego se elige la opcin correspondiente:

CASO PRATICO 03En el libro Empleados_Taller.xlsx, en la hoja Datos, el encargado del rea de Produccin va a

insertar dos columnas antes del turno para los apellidos paterno y materno respectivamente.

1. Seleccione las columnas D y E (columnas de referencia)

2. El modo convencional: Haga clic derecho en la seleccin

3. Elija la opcin Insertar.

4. El modo rpido: Con las columnas seleccionadas, pulsar:


15/94

15


5. Ingresamos los datos que faltan, como los encabezados para D y E, A-Paterno y A-Materno,respectivamente, luego los registros como se muestran:

CASO PRATICO 04

En el libro Empleados_Taller.xlsx, en la hoja Datos, el encargado del rea de Produccin va ainsertar tres nuevas filas, debajo del cdigo TR-03. Para que se muestren de manera ordenada losregistros

1. Seleccione las filas 7, 8 y 9 (filas de referencia)

2. El modo convencional: Haga clic derecho en la seleccin

3. Elija la opcin Insertar

4. El modo rpido: Con las filas seleccionadas, pulsar:

5. Ingresamos los datos que faltan, en las filas 7,8 y 9 respectivamente, como se muestran.


16/94

16


6. Guarde los cambios (Ctrl + G) y cierre el libro (Ctrl + F4).

CASO PRATICO 05Ahora active el libro Ventas_ Temporadas en donde estn registradas las ventas por cada

campaa. Adems se desea insertar una hoja nueva para realizar los resmenes, la nueva hoja sellamara Resumen.

1. En la ficha Inicio, ubique el grupo Celdas, habr el botn Insertar y seleccione la opcin Insertarhoja.

2. El modo rpido es: Haga clic en el control Insertar hoja de calculo

3. Ahora cambie el nombre de la Hoja1 por el de Resumen,

4. Tambin asgnele un color de etiqueta a su criterio

5. Luego grabe los cambios del libro y cirrelo.

CASO PRATICO 06Ahora active el libro Ventas_Ene_Abr se desean eliminar las hojas Mayo y Junio. Guarde los

cambios.

1. En la ficha Inicio, ubique el grupo Celdas, habr el botn Eliminar y seleccione la opcin Eliminar

hoja.


17/94

17


2. El modo rpido es: Haga clic derecho en la hoja de calculo: Mayo

3. Luego haga clic en el botn Aceptar de la caja de confirmacin

4. El resultado ser el siguiente:

CASO PRATICO 07

Trabajando en el libro Ventas_Ene_Abrse deben ordenar las hojas con los meses del ao enforma ascendente.

1. Haga clic en la etiqueta de la hoja que desea mover y arrstrela hasta la posicin requerida

2. Arrastre la hoja Enero hasta antes de la hoja Febrero.

3. El resultado mostrar el siguiente resultado. (Guarde los cambios).


18/94

18


ESTILOS

ESTILOS DE CELDA

Aplica rpidamente un formato a una celda o rango de celdas con los estilos predefinidos.Tambin se puede definir estilos propios para las celdas.

CASO PRATICO 08

En el libro Ventas_Temporadas.xlsx, en la hoja 2da Campaa, se deben realizar operaciones deasignacin de Estilos de celda:

1. La hoja 2da Campaa mostrara lo siguiente:

2. Copiar los formatos de la A1 y A2 de la hoja 1ra Campaa hacia la hoja 2da Campaa, aplicar alrango [A4:H4] el Estilo de celda nfasis 4, luego centrar y asignar Todos los bordes.

3. Ahora aplicar al rango [D5:G14] el Estilo de celda Moneda [0], luego centrar y asignar Todos los

bordes. Los rangos [H5:H15] y [D15:G15] se le asigna el Estilo de celda 20% - Enfasis 1, Negr., elEstilo de celda Moneda, tambin todos los bordes.


19/94

19


4. Aplicar al rango [B5:B14] el Estilo de celda 20% - Enfasis 3, el rango [B15:C15] asigna el Estilo decelda Enfasis 4 y asigne Todos los bordes a los rangos. El resultado mostrara:

FORMATOS COMO TABLAPermite aplicar rpidamente un formato a un conjunto de celdas y lo convierte en una tabla

seleccionando un estilo predeterminado, esta herramienta es muy til en el manejo de grandesvolmenes de informacin y tambin cuando se quieren manejar las tablas dinmicas.

CASO PRATICO 09En el libro Ventas_Temporadas.xlsx, en la hoja 3ra Campaa, se deben realizar operaciones de

asignacin Dar formato como tabla:

1. La hoja 3ra Campaa mostrara lo siguiente:

2. Copiar los formatos de la A1 y A2 de la hoja 2da Campaa hacia la hoja 3ra Campaa, ubicarsedentro de la lista, aplicar Dar Formato como tabla: Estilo de tabla medio 5. A continuacin hagaclic en Aceptar de la caja de confirmacin


20/94

20


3. En la Ficha Diseo de las Herramientas de tabla active el control Fila de totales (del grupoopciones de estilo de tabla). El rango [D5:H15] asgnele el Formato de numero de contabilidadS/. Espaol (Per)

4. En las lista de la celda D15 elija Promedio, en la celda E15 elija Cuenta, en la celda F15 elijaMax, en la celda G15 elija Min y en la celda H15 elija Suma.

5. Es importante considerar que cada campo se convierte una lista, donde se pueden mostrarsolamente aquellos registros que cumplan con una determinada condicin. (Guarde loscambios).

6. Cuando se convierte una lista en Dar formato de tabla, la lista automticamente se nombre.,tambin es posible reconfigurar la asignacin de Formato como tabla, utilizando los controlesde la Ficha Diseo con las Herramientas de tabla


21/94

21


UNIDAD 3FORMULAS Y OPERADORES

Una frmula es una expresin que relaciona valores con operadores para producir un nuevovalor. Las frmulas pueden tener varias formas, ya que pueden utilizar referencias, funciones, textoy nombres para realizar diferentes tareas.

Para introducir frmulas, deber editar la celda o bien seleccionar la celda y despus introducirdatos. Una frmula siempre debe comenzar con el signo igual ( = ) y puede producir diferentesresultados: Un valor concreto, un valor lgico (VERDADERO o FALSO) o un texto.

CALCULOS CON OPERADORESLos operadores son smbolos que identifica Excel con operaciones aritmticas y es el enlace entre

dos argumentos. Los clculos con operadores empiezan con el signo =.

TIPOS DE OPERADORES

Los operadores especifican el tipo de clculo que se desea realizar con los elementos de unafrmula. Microsoft Excel incluye cuatro tipos diferentes de operadores de clculo: aritmtico,comparacin, texto y referencia.

OPERADORES ARITMETICOSPara ejecutar las operaciones matemticas bsicas como suma, resta o multiplicacin, combinar

nmeros y generar resultados numricos, utilice los siguientes operadores aritmticos.

SIGNO OPERACIN EJEMPLO

^ Exponenciacin =8^3

% Porcentaje =850*15%

/ Divide =42/3

* Multiplica =14*7

+ Suma =53+87

- Resta =62-18

OPERADORES DE COMPARACIONSe pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores

utilizando estos operadores, el resultado es un valor lgico: VERDADERO o FALSO.


= Igual Que 5=8

> Mayor Que 21>16

< Menor Que 43= Mayor Igual Que 14>=14


22/94

22


OPERADORES DE TEXTOUtilice el signo (&) para unir o concatenar una o varias cadenas de texto con el fin de generar un

solo elemento de texto.


& Microsoft & Office Microsoft Office

OPERADORES DE REFERENCIACombinan rangos de celdas para los clculos con los siguientes operadores.


:Indica un rango de celdas entre dos

coordenadasC8:J20

,Separa argumentos en una funcin

=Contar.Si(Turno,Tarde)(espacio)

Hace referencia a la interseccin dedos rangos diferentes.

D7:E10 G15:R22

(operacin) Agrupa operaciones combinadas. =4/(2+8)*3+(7-2)/3

CASO PRATICO 01La empresa Yhassir Data, distribuidora de productos de computo, requiere realizar el control

de compras, proveedores y crditos del mes de junio del presente ao. Para ello se debe activar ellibro Ventas_Junio, y realice los siguientes clculos:

a) En la hoja Compras, obtener el resultado de los siguientes campos:

PREVENTA = PRECOSTO x %GANA + PRECOSTO

IMPORTE = PREVENTA x CANT

IGV = 18% x IMPORTE

SUBTOTAL = IMPORTE + IGV

1. Para calcular el P-VENTA, ubicarse en la celda E4, escriba la formula =C4*D4+C4 y presioneENTER:

2. Regrese a la celda E4, seale la esquina inferior derecha, cuando aparece el control de relleno(+), arrastre hacia abajo, hasta la celda E13, para copiar la formula.


23/94

23


3. Para calcular el IMPORTE, ubicarse en la celda G4, ingrese la formula correspondiente y copiela formula hasta la celda G13.

4. Para calcular el IGV, ubicarse en la celda H4, ingrese la formula, presione ENTER, regrese a lacelda H4 y cuando aparezca el control de relleno (+) haga Doble Clic para copiar la formula.

5. Realice un procedimiento similar al IGV para calcular el SUBTOTAL.

b) En la hoja Proveedores, obtener el IGV y el P-COSTO a partir de Montos donde estn incluidosel Impuesto General a las Ventas :

IGV = MONTOMONTO / 1.18

P-COSTO = MONTOIGV

1. Considerando que el MONTO ya tiene incluido el IGV, ubquese en la celda D4 para obtener elIGV y en la celda E4 el P-COSTO

c) En la hoja Crditos, considerando las formulas estndar de Inters Ganado y monto Final,realice los siguientes clculos:


24/94

24


P= MONTO INICIAL I= INTERES GANADO = [( + ) ]

i = INTERES MENSUAL S= MONTO FINAL = ( + )

n= NUM. DE PERIODOS

1. Ubquese en la celda F4 y G4 para obtener el Inters Ganado y el Monto Final respectivo:

2. Guarde los cambios.

REFERENCIAS DE CELDASCuando hacemos usos de frmulas y funciones casi es seguro que coloquemos referencias a

celdas o conjunto de celdas que no son propiamente la misma celda donde tenemos la formula.

Las referencias son enlaces a un lugar, es decir, cuando en una formula escribimos =SUMA(A2:A5)nos estamos refiriendo a que sume el contenidodesde A1hasta la celda A5.

Es muy importante considerar que cuando se copia una Formula en sentido:

Vertical (a nivel de columnas), cambian las direcciones de las filas,

Horizontal (a nivel de filas), cambian las direcciones de las columnas.

Los tipos de celdas durante una copia de frmulas son: Relativas, Absolutas y Mixtas.

- RELATIVAS.- Si copiamos una frmula con referencias relativas, la frmula cambiardependiendo de donde vamos a copiarla.Las direcciones de las celdas son VARIABLES. Esta es laopcin que ofrece Excel por defecto.

Ej.: H5

-ABSULTAS.- Si copiamos una frmula con referencias absolutas, la frmula NO variar. Lasdirecciones de las celdas son CONSTATES.

Ej.: $H$5

- MIXTAS.- Si copiamos una frmula con referencias mixtas, la frmula cambiar la columna o lafila que no tiene el signo $ delante dependiendo de donde vamos a copiarla y no variar la que stiene el signo $ delante.Combina celdas absolutas con celdas relativas.

Ej.: $H5 | H$5


25/94

25


IMPORTANTE: Para asignar el smbolo dlar ($), se pulsa la tecla F4 (sobre la celda), tambin sepuede digitar dicho smbolo. Una celda es absoluta o mixta cuando se encuentra fuera de la listade datos

CASO PRATICO 02La empresa Yhassir Import, distribuidora de muebles de oficina de computo, requiere realizar

el control de ventas, del presente mes. Para ello se debe activar el libro Importadora, en la hojaVentas, realice los siguientes clculos, considerando que las celdas F2 y H2 deben ser absolutas:

IMPORTE = CANTIDAD * VALOR VTA.

IGV = IMPORTE * 19% (F2)

PRECIO VTA. S/. = IMPORTE + IGV

PRECIO VTA. $ = PRECIO VTA. S/. / TIPO DE CAMBIO (H2)

1. Ubicado en la hoja Ventas: a) seleccione la celda E4, escriba la frmula del IMPORTER indicada

y luego cpiela, b) seleccione la celda F4, escriba la frmula del IGV (la celda F2 ser absoluta)indicada y luego cpiela, c) seleccione la celda G4, escriba la frmula del PRECIO VTA S/.indicada y luego cpiela, d) seleccione la celda H4, escriba la frmula del PRECIO VTA $. (la celdaH2 ser absoluta) indicada y luego cpiela

2. Seleccione el rango [E4:H15] ubique el botn de Autosuma (

) y haga clic.

Importante:

1. Cambie el contenido actual (IGV) de la celda F2 (19%) por 18%, se actualizan automticamentelos campos relacionados (IGV, Precio Vta. S/. y Precio Vta. $), tambin puede cambiar elcontenido de la celda H2 (Precio Venta $) por 2.90 (segn el costo actual del dlar. Se mostraranlos siguientes resultados:


26/94

26


2. Guardar los cambios.

CASO PRATICO 03La empresa Yhassir Import, requiere realizar la proyeccion estimada para los siguientes tres

meses (febrero, marzo, abril). Ubicado en la hoja Proyeccion, realice el clculo:

Febrero = MesAnterior * %GanMesActual + MesAnterior

Considerando que en la formula la celda C$2 (GanMesActual), solo la fila 2 debe ser absoluta:

1. Ubicado en la celda D5, escriba la frmula: =C5*C$2+C5

2. Regrese a la celda D5 y copia la formula (hacia la derecha) hasta la celda F53. Con el rango [D5:F5] seleccionada, ubique el control de copia y arrastre (hacia abajo) hasta la

fila 15.

RANGOS DE CELDAS

Podemos dar a un rango de celdas en Excel un nombre para hacer operaciones de clculo

o validaciones de datos. Un nombre de rango es reconocido en cualquier hoja de un libro, perotambin se puede asignar un nombre solo para una determinada hoja.


27/94

27


Seleccionamos todas las celdas (continuas o discontinuas) que le vamos asignar unnombre, luego en la Ficha Formulas, seleccionamos el botn Administrador de nombres, en dondepodemos: asignar, editar y hasta eliminar un nombre de rango.

CASO PRATICO 04En el libro Ventas_Tienda_01.xlsx, en la hoja X Trimestre, se debe seleccionar el rango: [B4:E10]

y asignarle el nombre VENTAS, utilizando el mtodo rpida de nombrar un rango de celdas.

1. Seleccione el rango: [B4:E10]

2. Luego hacer clic en el control: Cuadro de nombres, digite VENTAS

3. Luego presione la tecla ENTER.

CASO PRATICO 05En el libro Ventas_Tienda_01.xlsx, en la hoja X Semana, se deben seleccionar los rangos

numricos y asignarle el nombre SEMANAS.

1. Seleccione el rango: [C7:E12]

2. Luego presione la tecla CTRL, y seleccione los otros rangos

3. Hacer clic en el control: Cuadro de nombres, digite SEMANAS

4. Luego presione la tecla ENTER.


28/94

28


5. Una forma de comprobar que los rangos existen, es hacer clic al control desplegable Cuadro denombres, seleccione un nombre y compruebe su direccin: (Guarde los cambios).

CASO PRATICO 06

La empresa Yhassir Import, requiere realizar una bonificacin extraordinaria del 20% a sustrabajadores. Para ello se debe activar el libro Importadora, en la hoja Trabajadores, debernombrar la celda E2, esta accin es equivalente a convertir una celda en celda absoluta.

1. Ubicado en la celda E2, haga clic en el control Cuadro de nombres, digite BONIF y presione

ENTER.2. Luego ubquese en la celda E4, escriba la frmula: =D4*BONIF, presione ENTER y luego copie la

formula hasta la celda E18.

3. Ubquese en la celda F4, digite la frmula: =D4+E4, presione ENTER y luego copie la formulahasta la celda F18.

4. Seleccione el rango [E4:F18] y realice la operacin de Autosuma.

5. Finalmente guarde los cambios.


29/94

29


UNIDAD 4FORMATO CONDICIONAL

Mediante la aplicacin de formato condicional a los datos, es posible identificar rpidamentevarianzas en un intervalo de valores con solo observar los formatos aplicados. Resalta celdasinteresantes, enfatiza valores poco usuales y visualiza datos usando barras de datos, escalas decolor y conjunto de iconos basados en criterios.

CASO PRATICO 01

En el libro Inventario_Almacen.xlsx, en la hoja Almacn Empleando Formato Condicional:Caso1) Poner en negrita, de color verde las Marca: Vouge. Caso 2)Poner en negrita, cursiva, rojoel tamao Medium y de color azul, negrita el tamao Large.

1. La secuencia para el Caso 1 es: a) Seleccione el rango [C2:C25], b) en el grupo Estilos activeFormato condicional, c) seleccione Resaltar reglas de celdas y d) luego seleccione el evento Esigual a

2. Luego: a) Escriba Vouge en el cuadro, b) Active la lista y elija Formato Personalizado, c) luegoactive Negrita y elija el color verde, d) Luego Aceptar y e) seleccione el botn Aceptar.


30/94

30


3. La secuencia para el Caso 2, para el formato Medium: a) Seleccione el rango [D2:D25], b) en elgrupo Estilos active Formato condicional, c) seleccione Resaltar reglas de celdas, d) luegoseleccione el evento Es igual a, d1) Digite MEDIUM, d2) Elija Formato personalizado,

asigne negrita, cursiva y color rojo d3) Luego seleccione Aceptar.

Para el formato Large: a) Mantenga Seleccionado el rango [D2:D25], b) en el grupo Estilos activeFormato condicional, c) seleccione Resaltar reglas de celdas, e) luego seleccione el evento Textoque contiene, e1) Digite LARGE, e2) Elija Formato personalizado, asigne negrita y color

azul e3) Luego seleccione Aceptar.


31/94

31


CASO PRATICO 02En el libro Inventario_Almacen.xlsx, en la hoja Almacen Empleando Formato Condicional:a).

Poner el formato Barra de datos a los Stocks, b) Poner el formato Escala de color al Pre-Costo y c)

Poner el formato Conjunto de iconos al Pre-Venta.1. Para la Barra de datos: a) Seleccionado el rango [F2:F25], b) elija Formato condicional, c) seale

Barras de datos y d) haga clic en Barra de datos verde.

2. Para la Escala de color: a) Seleccionado el rango [G2:G25], b) elija Formato condicional, c) sealeEscalas de color y d) haga clic en Escala de colores rojo, amarillo, verde.

3. Para el Conjunto de iconos: a) Seleccionado el rango [H2:H25], b) elija Formato condicional, c)

seale Conjuntos de iconos y d) haga clic en 5 flechas (de color).


32/94

32


4. El resultado mostrara lo siguiente:

CASO PRATICO 03

Aplicando un Formato condicional con Formula, en la hoja Almacen del libroInventario_Almacen.xlsx, Poner en color azul y con relleno azul claro los nombres de los Productosdonde el precio de venta sea superior a S/. 82.00.

1. La secuencia es: a) Seleccione el rango [B2:B25], b) en el grupo Estilos active Formato

condicional, c) seleccione Nueva regla, d) luego seleccione el evento Utilice una frmula quedetermine...., e) escriba la formula =H2


33/94

33


VALIDACIN DE DATOS

La validacin de datos se usa para controlar el tipo de datos o los valores que los usuarios puedenescribir en una celda. Por ejemplo, es posible que desee restringir la entrada de datos a un intervalo

determinado de fechas, limitar las opciones con una lista o asegurarse de que slo se escribennmeros enteros positivos.

CASO PRATICO 4En el libro Inventario_Almacen.xlsx, en la hoja Personal Empleando Validacin, realiza las

siguientes restricciones:

CAMPO VALORES PERMITIDOS

a) TURNO Maana, Tarde

b) HIJOS (tiene hijos?) Si, No

c) FEC-NAC Fechas menores de 1994

d) BASICO Entre 700 y 1600e) COMISION No debe pasar del 8% del Bsico

a) Para la Barra de datos: a) Ingresar datos a partir de la celda J2, b) seleccione rango [J3:J4] y c)en el control Cuadro de nombre digite TURNO y presione la tecla Enter. (el tema Nombrar

rangos se revisa con mayor detalle en la Unidad 3).

c) Seleccione el rango a validar [C2:C8], d) En la ficha Datos, elija Validacin de datos, e) Luegoen el control Permitir elija Lista, f) Digite en el control Origen Turno y finalmente Aceptar.


34/94

34


b) Seleccione el rango a validar [D2:D8], active la Validacin de datos y configure: en el controlPermitir seleccione Lista, en el control Origen digite Si,No y Aceptar.

c) Seleccione el rango a validar [E2:E8], active la Validacin de datos y configure: en el controlPermitir seleccione Fecha, en el control Datos elija Menor que, en el control Fecha final digite01/01/1994 y Aceptar.

d) Seleccione el rango a validar [F2:F8], active la Validacin de datos y configure: en el controlPermitir seleccione Decimal, en el control Datos elija entre, en el control Mnimo digite 700, en el control Mximo digite 1600 y Aceptar.


35/94

35


e) Seleccione el rango a validar [G2:G8], active la Validacin de datos y configure: en el controlPermitir seleccione Personalizada, en el control Formula digite =G2


36/94

36


UNIDAD 5FUNCIONES

Una Funcin es una frmula predefinida por Excel que opera sobre uno o ms valores y temuestra un resultado que aparecer directamente en la celda introducida. En otras palabraspodemos decir que una funcin es una frmula predefinida que se utiliza para realizar clculoscomplejos de una manera sencilla.

La sintaxisde cualquier funcin es:

nombre_funcin(argumento1,argumento2,...,argumentoN)

Siguen las siguientes reglas:

Si la funcin va al comienzo de una frmula debe empezar por el signo =.

Los argumentos o valores de entrada van siempre entre parntesis. No dejes espacios antes odespus de cada parntesis.

Los argumentos pueden ser valores constantes (nmero o texto), frmulas o funciones.

Los argumentos deben de separarse por una coma ,

Para insertar una funcin:

1. Ubicado en la celda donde requiere obtener un resultado

2. Ubquese en la Ficha Formulas, en el grupo Biblioteca de funciones, Haga clic en la lista de lacategora de funcin y luego seleccione la funcin:

3. Configure la caja de dialogo: Argumentos de la funcin (seleccione rangos o indique nombres):


37/94

37


4. El resultado se mostrara en la celda:

Otra manera, ms usual, de insertar una funcin es:

1. Ubicado en la celda donde requiere obtener un resultado

2. Coloque el signo =, luego empiece a escribir la funcin

3. Excel le mostrara, mientras escribe, un filtro con una lista de funciones relacionadas, tambinmuestra un breve concepto de la funcin elegida:

4. Puede elegir la funcin requerida de dos maneras:

a. Hace doble clic sobre la funcin

b. Selecciona la funcin y presiona la tecla TAB (recomendada)


38/94

38


5. Luego completa los parmetros de la funcin elegida y presiona ENTER.

6. Mientras completa la funcin Excel le muestra en un recuadro gris, una ayuda con losparmetros o argumentos de la funcin.

7. El resultado se mostrara en la celda correspondiente:

8. Es importante observar la barra de formulas donde se muestra el contenido real de la celda(sea la funcin insertada).

FUNCIONES FRECUENTES

FUNCION SUMA

Suma todos los nmeros que se encuentren en sus argumentos, no toma en cuenta las celdasen blanco tampoco las celdas que contienen texto' y Los argumentos que sean valores de error otexto que no se pueda traducir a nmeros provocan errores.

Sintaxis:

SUMA(Arg1,Arg2,Arg3,..,Argn)

Donde n puede ser de ser de 1 a 255

FUNCION MAX

Devuelve el valor mximo de todos los valores que intervengan en sus argumentos.

Sintaxis:

MAX(Arg1,Arg2,Arg3,....,Argn)


Observaciones:

Los argumentos pueden ser nmeros. nombres de celdas. Rangos de celdas que contengannmeros.


39/94

39


Si el argumento es un rango de celdas. slo se utilizarn los nmeros. Se pasarn por alto lasceldas vacas, los valores lgicos o los valores de tipo texto.

Los argumentos que sean valores de error o texto que no se pueda traducir a nmeros

provocan errores.FUNCION MIN

Devuelve el valor mnimo de todos los valores numricos que intervengan en sus argumentos.

Sintaxis

MIN(Arg1, Arg2, Arg3, ... , Argn)


Observaciones

Los argumentos pueden ser nmeros, nombres de celdas, Rangos de celdas que contengannmeros.

Si el argumento es un rango de celdas, slo se utilizarn los nmeros. Se pasarn por alto lasceldas vacas, los valores lgicos o los valores de tipo texto.

Los argumentos que sean valores de error o texto que no se pueda traducir a nmerosprovocan errores.

FUNCION PROMEDIO

Devuelve el promedio (media aritmtica) de los todos los valores que se encuentran comoargumentos.

Sintaxis

PROMEDIO(Arg1,Arg2,Arg3,...,Argn)

Donde n puede ser de ser de 1 a 255 argumentos

Observaciones

Los argumentos pueden ser nmeros, nombres de celdas, Rangos de celdas que contengannmeros.

Si el argumento es un rango, slo se utilizarn las celdas que contengan nmeros. Se pasarnpor alto las celdas vacas, los valores lgicos o los valores de tipo texto.

Los argumentos que sean valores de error o texto que no se pueda traducir a nmerosprovocan errores.

CASO PRATICO 01

La empresa Breshka s.a., distribuidora de productos, requiere realizar la estadstica decompras de los meses octubre y noviembre respectivamente. Para ello se debe activar el libroVentas_Oct-Nov, en la hoja Octubre realice los siguientes clculos:

1. En la hoja Octubre, realice el siguiente manejo de funciones:

2. Considerando el manejo de direcciones de rangos:

CELDA RANGO DE CELDAS

F18: =SUMA(F2:F17)

J3: =SUMA(E2:E17)

J4: =MAX(E2:E17)

J5: =MIN(E2:E17)


40/94

40


CELDA RANGO DE CELDAS

J6: =PROMEDIO(E2:E17)

E30 =Suma(F2:F17)

E31 =MAX(F2:F17)E32 =MIN(F2:F17)

E33 =PROMEDIO(F2:F17)

3. Luego de las operaciones se mostrara el siguiente resultado:

4. Luego nos ubicamos en la hoja Noviembre y nombramos los siguientes rangos:

RANGO NOMBRE

[F2:F17] TVEN

[E2:E17] UVEN

5. Considerando ahora que se utiliza los nombres de rango:

CELDA RANGO DE NOMBRE

F18: =SUMA(TVEN)

J3: =SUMA(UVEN)

J4: =MAX(UVEN)

J5: =MIN(UVEN)

J6: =PROMEDIO(UVEN)

E30 =SUMA(TVEN)

E31 =MAX(TVEN)

E32 =MIN(TVEN)

E33 =PROMEDIO(TVEN)

6. Luego de las operaciones se mostrara el siguiente resultado:


41/94

41


7. Luego Guarde los cambios


42/94

42


UNIDAD 6

FUNCIONES ESTADISTICASFUNCION CONTAR

Cuenta el nmero de celdas que contienen nmeros, adems de los nmeros incluidos dentrode la lista de argumentos.

Sintaxis

CONTAR(Arg1,Arg2,Arg3,...,Argn)Donde n puede ser de ser de 1 a 255 argumentos que pueden contener o hacer referencia a

distintos tipos de datos, pero slo se cuentan los nmeros, Fechas y horas.

Se pasan por alto o no los toma en cuenta a las celdas vacas, valores lgicos, texto o valores deerror.

FUNCION CONTARACuenta el nmero de celdas que no estn vacas en un rango y los valores que hay en la lista de

argumentos.

Sintaxis

CONTARA(Arg1,Arg2,Arg3,..,Argn)Donde n puede ser de ser de 1 a 255 argumentos que representan los valores que se desea

contar.

Los valores pueden ser de error, texto y vaco (). No cuenta las celdas vacas.

FUNCION CONTAR.BLANCOCuenta el nmero de celdas vacas (en blanco) dentro de un rango de celdas.

Sintaxis

CONTAR.BLANCO(rango)

Rango es el rango dentro del cual desea contar el nmero de celdas en blanco.

FUNCION CONTAR.SICuenta las celdas, dentro del rango, que no estn en blanco y que cumplen los criterios

especificados.

Sintaxis

CONTAR.SI(rango,criterio)

Rango son las celdas que se van a contar cuantas veces cumple el criterio que se especifique enel segundo argumento.

Criterio es el criterio en forma de nmero, expresin, referencia a celda o texto, Por ejemplo,los criterios pueden expresarse como 75, "75", "


43/94

43


FUNCION CONTAR.SI.CONJUNTOCuenta las celdas, dentro del rango, que cumplen los criterios especificados.

Sintaxis

CONTAR.SI.CONJUNTO(Rang1,Crit1,Rang2,Crit2,....)

Rang1,Rang2,.... son de 1 a 127 rangos en los que se van a evaluar los criterios asociados.

Crit1,Crit2,.... son de 1 a 127 criterios en forma de nmero, expresin, referencia de celda otexto que determinan las celdas ~ue se van a contar. Por ejemplo, los criterios pueden expresarsecomo 75, "75", ">75", "DISEADOR" o la direccin de una Celda como 84.

FUNCION K.ESIMO.MAYORDevuelve el k-simo mayor valor de un conjunto de datos. Esta funcin puede usarse para

seleccionar un valor basndose en su posicin relativa.

Sintaxis

K.ESIMO.MAYOR(matriz;k)

Matriz es la matriz o rango de datos cuyo k-simo mayor valor desea determinar.

K representa la posicin (a partir del mayor valor), dentro de la matriz o rango de celdas, delos datos que se van a devolver.

Por ejemplo, se puede utilizar K.ESIMO.MAYOR para devolver el mayor valor de un resultado, elsegundo resultado o el tercero.

FUNCION K.ESIMO.MENORDevuelve el k-simo menor valor de un conjunto de datos. Utilice esta funcin para devolver

valores con una posicin relativa especfica dentro de un conjunto de datos.

Sintaxis

K.ESIMO.MENOR(matriz;k)

Matriz es una matriz o un rango de datos numricos cuyo k-simo menor valor deseadeterminar.

K es la posicin, dentro de la matriz o del rango de datos, de los datos que se van a devolver,determinada a partir del menor de los valores.

FUNCIN MEDIANA

Es el nmero intermedio de un grupo de nmeros; es decir, la mitad de los nmeros sonsuperiores a la mediana y la mitad de los nmeros tienen valores menores que la mediana. Porejemplo, la mediana de 2, 3, 3, 5, 7 y 10 es 4.

Sintaxis

MEDIANA(nmero1,[nmero2], ...)

Nmero1,nmero2... Nmero1 es obligatorio, los nmeros siguientes son opcionales. De1 a 255 nmeros cuya mediana desea obtener.

Observaciones:

Si la cantidad de nmeros en el conjunto es par, MEDIANA calcula el promedio de los nmeroscentrales. Vea la segunda frmula del ejemplo.

Los argumentos pueden ser nmeros, o nombres, matrices o referencias que contengan

nmeros.


44/94

44


Se tienen en cuenta los valores lgicos y las representaciones textuales de nmeros escritosdirectamente en la lista de argumentos.

Si el argumento matricial o de referencia contiene texto, valores lgicos o celdas vacas, estos

valores se pasan por alto; sin embargo, se incluirn las celdas con el valor cero. Los argumentos que sean valores de error o texto que no se pueda traducir a nmeros provocan

errores.

FUNCIN MODA.UNO

Es el nmero que aparece ms frecuentemente en un grupo de nmeros. Por ejemplo, la modade 2, 3, 3, 5, 7 y 10 es 3.

Sintaxis

MODA.UNO(nmero1,[nmero2],...])

Nmero1 Obligatorio. El primer argumento para el que desea calcular la moda.

Nmero2, ...Observaciones:

Los argumentos pueden ser nmeros, o nombres, matrices o referencias que contengannmeros.

Si el argumento matricial o de referencia contiene texto, valores lgicos o celdas vacas, estosvalores se pasan por alto; sin embargo, se incluirn las celdas con el valor cero.

Los argumentos que sean valores de error o texto que no se pueda traducir a nmeros provocanerrores.

Si el conjunto de datos no contiene puntos de datos duplicados, MODA.UNO devuelve el valor

de error #N/A.CASO PRATICO 01

En la hoja de clculo Personal del libro Datos_del_Personal.xlsx Se registr los datos de lostrabajadores de la empresa Breshka s.a., los datos que no dieron o no se acordaban esta enblanco o con el signo interrogacin "?" y los trabajadores que le falta algn dato est con asterisco*.


45/94

45


1. Ubicado en la hoja Personal, crear los siguientes nombres de rango: (utilizar la herramienta:Crear desde la seleccin)

RANGO NOMBRE RANGO NOMBRE

[B2:B28] PERSONAL [I2:I28] FONO[C2:C28] FECHA [J2:J28] OCUPACION

[D2:D28] SEXO [K2:K28] CONDICION

[E2:E28] EDAD [L2:L28] AFP

[F2:F28] ESTADO [M2:M28] FALTA_DAT

[G2:G28] CATEGORIA [N2:N28] SUELDO

[H2:H28] HIJOS

2. Luego pase a la hoja Estadisticas, ubicarse en las celdas correspondientes y realice las siguientesoperaciones:

Repta. 1:

C2: =CONTARA(PERSONAL)

C3: =CONTAR(EDAD)

C4: =CONTAR.BLANCO(EDAD)

Repta. 2:

C6: =CONTARA(HIJOS)

C7: =CONTAR.BLANCO(HIJOS)

Repta. 3:

C9: =CONTARA(FONO)

C10: =CONTAR.BLANCO(FONO)Repta. 4:

C12: =CONTAR.SI(SEXO,"M")

C13: =CONTAR.SI(SEXO,"F")

Repta. 5:

G2: =CONTAR.SI(ESTADO,"S")

G3: =CONTAR.SI(ESTADO,"C")

G4: =CONTAR.SI(CONDICION,"ESTABLE")

G5: =CONTAR.SI(CONDICION,"CONTRATADO")

Repta. 6:

G9: =CONTAR.SI(OCUPACION,"DISEADOR")

G10: hasta G13: el manejo es el mismo, solo debe cambiar el criterio.

Repta. 7:

C16: =CONTAR.SI(AFP,"UNION VIDA")

C17: hasta C20: el manejo es el mismo, solo debe cambiar el criterio.

Repta. 8:

G16: =CONTAR.SI(AFP,"?")

G17: =CONTAR.SI(AFP,"?")Repta. 9:


46/94

46


K3: =K.ESIMO.MAYOR(SUELDO,1)

K4: y K5: la matriz es la misma, solo debe cambiar la posicin.

Repta. 10:

K10: =K.ESIMO.MENOR(SUELDO,1)

K11: y K12: la matriz es la misma, solo debe cambiar la posicin.

Repta. 11:

L16: =CONTAR.SI(FECHA,"=01/01/2006",FECHA,"


47/94

47


1. En la hoja Sueldos considerando a SUELDO como rango de suma, ingrese las siguientesfunciones:

Total de sueldo por Ocupacin:

C4: =SUMAR.SI(OCUPACION,"DISEADOR",SUELDO)C5: hasta C8: el manejo es el mismo, solo debe cambiar el criterio.

C9: sume el rango [C4:C8]

Total de sueldo por Categora:

F4: =SUMAR.SI(CATEGORIA,"A",SUELDO)

F5: hasta F7: el manejo es el mismo, solo debe cambiar el criterio.

F8: sume el rango [F4:F7]

Total de sueldo por Condicin:

C12: =SUMAR.SI(CONDICION,"ESTABLE",SUELDO)

C13: =SUMAR.SI(CONDICION,"CONTRATADO",SUELDO)

C14: sume el rango [C12:C13]

2. El resultado del manejo de las operaciones ser:



48/94

48


UNIDAD 7RESUMENES ESTADISTICOSORDENAR LISTAS

Los datos en las hojas de Excel se suelen introducir de una manera desordenada, y eso a veceshace complicado tener una visin general, o tener alguna respuesta completa con facilidad.Ordenar, de Excel, hace ms fcil tener distintas visiones de una misma hoja de clculo. Las hojas

de clculo de Excel permiten ordenar columnas por orden alfabtico, si se trata de palabras, o demenor a mayor (o a la inversa) si se trata de nmeros.

La forma ms sencilla de ordenar en orden alfabtico normal y en orden inverso es mediante losbotones orden ascendente y orden descendente. Se pueden ordenar las filas completas o slo

algunas celdas seleccionadas, segn la primera columna de la seleccin.

Ordenar los datos es una parte esencial del anlisis de datos. Puede que desee poner una listade nombres de los clientes en orden alfabtico, compilar una lista de niveles de inventario deproductos de mayor a menor u ordenar filas por colores o por iconos. Ordenar los datos ayuda averlos y a comprenderlos mejor, as como a organizarlos y encontrarlos ms fcilmente y a tomarlas decisiones ms favorables para la empresa.

CASO PRACTICO 01

La empresa textil Breshka s.a. tiene en cartera clientes nacionales e internacionales, esimportante tener un registro ordenado y filtrado de los mismos. Inicialmente se requiere tenerordenado la lista de los clientes por Pas (en la hoja de clculo ORDEN01del Libro Listado_Clientes).

1. Seleccione toda (CTRL + E) la lista de la hoja ORDEN01.

2. En la Ficha Datos, en el grupo Ordenar y Filtrarseleccione el botn Ordenar.

3. Configure los controles: Ordenar por: seleccione el campo PAIS, Ordenar segn: Valores,Criterio de ordenacin: Ordenar de A a Zy finalmente Aceptar.


49/94

49


4. El resultado mostrara la lista ordenada por el campo PAIS:

CASO PRACTICO 02En la hoja ORDEN02se desea tener ordenado la lista de clientes por Sexo, Pas y Cepart.Reg.

1. Seleccione toda (CTRL + E) la lista de la hoja de clculo ORDEN02.


3. Luego configure los controles: a) seleccione el campo SEXO, en forma ascendente (A a Z), b)Agregar dos niveles, c) seleccione PAIS, en forma descendente (Z a A), seleccione DEPART./REGen forma ascendente (A a Z) y finalmente Aceptar


50/94

50



CASO PRACTICO 03Se desea tener ordenado del ms joven al cliente de ms edad en la hoja de clculo ORDEN03.

1. Seleccione toda (CTRL + E) la lista de la hoja de clculo ORDEN03.


3. Luego configure los controles: a) Orden Por: seleccione FECHA NACIM., Luego en el Criterio deordenacin seleccione De ms recientes a ms antiguosy finalmente Aceptar


51/94

51


CASO PRACTICO 04

En la hoja de clculo ORDEN04se tiene la lista de datos, donde al PAIS se le aplic un formato derelleno color de acuerdo a la frecuencia de compra que se indica en la siguiente tabla.

Tipo de cliente Color Aplicado

Compra Frecuentemente Azul

Compra Habitual Rojo

Compra Ocasional Verde

1. Seleccione toda (CTRL + E) la lista.


3. Luego configure los controles: a) Orden Por: seleccione PAIS., b) Luego agregar dos niveles,tambin elija PAIS, c) en criterio de ordenacin elija los colores Azul, Rojo y Verde, finalmente

Aceptar

AUTOFILTRO DE LISTASLos datos filtrados solamente muestran las filas que cumplen los criterios que haya especificado

y ocultan las filas que no desea ver. Despus de filtrar los datos, puede copiar, buscar, modificar,aplicar formato, representar mediante grficos e imprimir el subconjunto de datos filtrados sintener que volver a organizarlo ni moverlo.

Tambin puede filtrar por ms de un columna. Los filtros son aditivos, lo que significa que cada

filtro adicional se basa en el filtro actual y adems reduce el subconjunto de datos.


52/94

52


CASO PRACTICO 01En la hoja de clculo AFILTRO1se desea que la lista muestre solo a los clientes de Argentina,

Per y que sean del sexo femenino, para ofrecerles un nuevo producto que es exclusivamente para

damas.1. Haga clic en cualquier celda de las cabeceras de la hoja AFILTRO1.

2. En la Ficha Datos, en el grupo Ordenar y Filtrar.

3. Luego haga clic en el comando Filtro.

Luego en el campo PAIShaga clic en la flecha del encabezado de columna.

4. En la lista de valores, desactive todo (Con Seleccionar todo), y luego active el check deArgentinay Espaa

5. Luego haga clic en Aceptar.

6. Luego seleccione el campo SEXO, solo marque la F(femenino), tambin seleccione la opcinOrdenar de A a Zy luego Aceptar.


53/94

53


7. Seleccione nuevamente el filtro del campo PAIS, elija la opcin Ordenar de A a Zy finalmenteAceptar.

8. Como resultado tendremos una lista filtrada y ordenada por PAIS y que solo muestra losregistros del sexo Femenino:


54/94

54


CASO PRACTICO 02En la hoja de clculo AFILTRO2se desea tener una lista de todos los clientes que en su nombretenga como carcter la Cy que las compras realizadas sean de color de relleno Rojo.

1. Indique la orden de FILTRO. En l un campo APELLIDO PATERNOhaga clic en la flecha de filtro.

2. Luego seleccione Filtro de textohaga clic en el evento Contiene

3. En la caja Autofiltro personalizado, escriba en el control Contienela letra C y luego Aceptar.


55/94

55


4. Luego active el filtro de COMPRAS, seleccione Filtrar por Color, luego elija el color ROJO yAceptar.

5. El resultado ser el siguiente:

CASO PRACTICO 03En la hoja AFILTRO3 se requiere filtrar los clientes cuya compra se encuentre en el intervalo de

1500 a 2000. Luego ordene el filtro de mayor a menor.

Solucin

1. Haga clic en cualquier celda de la lista de la hoja AFILTRO3.

2. Indique la orden de FILTRO, active el filtro del campo COMPRAS, elija Filtro de nmeroyluego la opcin Entre


56/94

56


3. Configure la caja de dialogo, digite (o seleccione) los valores 1500y 2000respectivamente,como se muestra y luego Aceptar.

4. Luego de ordenar de mayor a menor, el resultado se mostrara de la siguiente manera:



57/94

57


UNIDAD 8FUNCION LOGICA

Pueden utilizarse las funciones lgicas para ver si una condicin es cierta o falsa o, paracomprobar varias condiciones.

FUNCION SIDevuelve un valor si la condicin especificada es VERDADERO y otro valor si dicho argumento es

FALSO.

Utilice SI para realizar pruebas condicionales en valores y frmulas.Sintaxis

SI(prueba_lgica,valor_si_verdadero,valor_si_falso)

Prueba_lgica es cualquier valor o expresin que pueda evaluarse como VERDADERO o FALSO.

Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lgica es VERDADERO.

Valor_si_falso es el valor que se devuelve si el argumento prueba_lgica es FALSO.

Observaciones

Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falsopara construir pruebas ms elaboradas.

Cuando los argumentos valor_si_verdadero y valor_si_falso se evalan, la funcin SI devuelveel valor devuelto por la ejecucin de las instrucciones.

Si uno de los argumentos de la funcin SI es una matriz, cada elemento de la matriz se evaluarcuando se ejecute la instruccin SI.

FUNCION SI ANIDADAPara utilizar correctamente la funcin SI anidadadebemos utilizar una funcin SI como el tercer

argumento de la funcin principal. Por ejemplo, para evaluar si una celda tiene alguna de las tresletras posibles (A, B, C) podemos utilizar la siguiente frmula:

=SI(A1="A",100,SI(A1="B",80,60))

Si el valor de la celda A1 tiene la letra A obtendremos el valor 100. Si la celda A1 tiene la letra B,entonces obtendremos el valor 80, de lo contrario obtendremos el valor 60. En este ejemplotenemos dos pruebas lgicas que nos ayudan a decidir correctamente sobre las tres opcionesposibles. Observa cmo esta frmula regresa el equivalente adecuado para cada letra de la columnaA:


58/94

58


Si por el contrario, en lugar de tres opciones necesitamos considerar cuatro, solo debemosagregar otrafuncin SI anidadaa nuestra frmula de la siguiente manera:

=SI(A1="A",100,SI(A1="B",80,SI(A1="C",60,40)))

Observa el resultado de esta frmula, especialmente para los valores que tienen la letra D:

Si tuviramos una quinta opcin, entonces remplazara el ltimo parmetro de la funcin SI quetiene el mayor nivel de anidacin para insertar una nueva funcin SI de la siguiente manera:

=SI(A1="A",100,SI(A1="B",80,SI(A1="C",60,SI(A1="D",40,20))))

Como podrs ver, con cada funcin SI que agregamos a nuestra frmula su complejidad ir enaumento y es muy comn que muchos usuarios de Excel comiencen a tener problemas para escribircorrectamente mltiples funciones SI anidadas.

Mi recomendacin es no exceder el mximo de 4, o a lo mucho 5, funciones SI anidadas demanera que se pueda comprender fcilmente la intencin de la frmula y detectar cualquier erroren su escritura. Pero si decides utilizar variasfunciones SI anidadasdebes recordar que nunca

podrs exceder el mximo de 64 funciones anidadas que permite Excel.


59/94

59


CASO PRACTICO 01Abrir el libro Registro_Plantilla y Ventas, en la hoja de clculo Planilla deber realizar los clculos

de remuneraciones, descuentos y totales, la hoja original se muestra de la siguiente manera:

Asignacin Familiar: es el 8% del bsico si tiene hijos.

Tiempo de Servicio: es el 20% del bsico si tiene a partir de 10 aos, en caso contrario ser 10%del bsico.

Incentivos: es el 15% del bsico para todos.

Movilidad. Si tiene menos de 10 aos recibir 9%, de lo contrario, si es mas antiguo recibir el18% del bsico.

Refrigerio: si tiene de 10 aos a mas recibir el 20%, sino recibir el 14% del bsico.

TOTAL REMUNERACION: Es la suma de todas las remuneraciones.

AFP: es el 13.8% para todos por estar en la misma AFP.

Adelanto: si el tiempo de servicio es mayor igual a 12 ser del 20% del bsico.

Tardanzas: si tiene mas de 99 minutos es el 15% del bsico.

TOTAL DESCUENTO: es la suma de todos los descuentos.

NETO PAGAR: es el TOTAL REMUNERACIONTOTAL DESCUENTO.

1. Ubicado en la hoja Planilla, seleccione la celda correspondiente,

2. escriba las operaciones y luego copie la frmula:

I3: =SI(F3>0,8%*H3,0)

J3: =SI(E3>=10,20%,10%)*H3

K3: =15%*H3

L3: =SI(E3=10,20%,14%)*H3

N3: =SUMA(H3:M3)

O3: =13.8%*H3


60/94

60


P3: =SI(E3>=12,20%*H3,0)

Q3: =SI(G3>99,15%*H3,0)

R3: =SUMA(O3:Q3)

S3: =N3-R3

3. el resultado mostrara lo siguiente:

CASO PRATICO 021. Ubicado en la hoja: Comision

2. Calcular las comisiones de un periodo de ventas que se deben pagar a los vendedores de unaempresa, de acuerdo al siguiente plan de incentivos. Cancelar el 6% de comisin a cadaempleado que haya vendido 150,000 UM (Unidades Monetarias) o ms, de lo contrario laempresa solo pagara el 1.5% de comisin sobre las ventas.


61/94

61


3. El Salario Base es la suma de las Ventas ms la Comisin

4. Para Comprobar debe dividir la Comisin entre las Ventas y lo multiplica por 100

5. El resultado deber mostrar lo siguiente:

CASO PRACTICO 03En el mismo el libro Registro_Plantilla y Ventas, active la hoja de clculo Ventas deber realizar

los siguientes clculos:

En el campo EVALUACION, se debe considerar: Si el PROMEDIOde ventas es mayor igual a 150se deber mostrar el mensaje FAVORABLE, en caso contrario se mostrar DESFAVORABLE

En el campo OBSERVACION, se debe considerar:

Si el PROMEDIO se encuentra entre:

0120 INSUFICIENTE

121- 150 REGULAR

151 - 180 NOTABLE

181 - Ms EXCELENTE

1. Seleccione la celda correspondiente, escriba las operaciones y luego copie la formula:

G3: =PROMEDIO(D3:F3)

H3: =SI(G3>=150,"FAVORABLE","DESFAVORABLE")


62/94

62


I3:=SI(G3


63/94

63


UNIDAD 9FUNCIONES DE BUSQUEDA

FUNCIN BUSCARVPuede usar la funcin BUSCARVpara buscar un valor especfico en la primera columna de un

rango de celdas y, a continuacin, se devuelve un valor de la columna de la misma fila que seespecifique en su tercer argumento.

Sintaxis:

BUSCARV(Valor_buscado,Matriz_busqueda,Indice,[Ordenado]) Valor_buscar Obligatorio. Valor que se va a buscar en la primera columna de la tabla o rango.

El argumento valor_buscadopuede ser un valor o una referencia.

matriz_busqueda Obligatorio. El rango de celdas que contiene los datos. Los valores de laprimera columna de matriz_busquedason los valores que busca valor_buscado. Estos valorespueden ser texto, nmeros o valores lgicos.

indicador_columnas Obligatorio. Un nmero de columna del argumento matriz_busquedadesde la cual debe devolverse el valor coincidente.

ordenado Opcional. Un valor lgico que especifica si BUSCARVva a buscar una coincidenciaexacta (VERDADERO / 1) o aproximada (FALSO / 0).

Si ordenadose omite o es VERDADERO, se devolver una coincidencia exacta o aproximada. Sino encuentra ninguna coincidencia exacta, devolver el siguiente valor ms alto inferior avalor_buscado.

Importante Si ordenado se omite o es VERDADERO, los valores de la primera columna dematriz_busquedadeben estar clasificados segn un criterio de ordenacin ascendente; en casocontrario, es posible que BUSCARVno devuelva el valor correcto.

Si el argumento ordenadoes FALSO, BUSCARVslo buscar una coincidencia exacta. Si hay doso ms valores en la primera columna de matriz_buscar_enque coinciden con el argumentovalor_bsqueda, se usar el primer valor encontrado. Si no se encuentra una coincidenciaexacta, se devolver el valor de error #N/A.

FUNCIN BUSCARHBusca un valor en la fila superior de una tabla o una matriz de valores y, a continuacin, devuelve

un valor en la misma columna de una fila especificada en la tabla o matriz. Use BUSCARH cuandolos valores de comparacin se encuentren en una fila en la parte superior de una tabla de datos ydesee encontrar informacin que se halle dentro de un nmero especificado de filas. Use BUSCARVcuando los valores de comparacin se encuentren en una columna a la izquierda de los datos quedesee encontrar.

La H de BUSCARH significa "Horizontal".

BUSCARH(Valor_buscado,Matriz_busqueda,Indice,[Ordenado])


64/94

64


Valor_buscado Obligatorio. El valor que se busca en la primera fila de la tabla. Valor_buscadopuede ser un valor, una referencia o una cadena de texto.

Matriz_buscar_en Obligatorio. Una tabla de informacin en la que se buscan los datos. Useuna referencia a un rango o el nombre de un rango. Los valores de la primera fila del argumentomatriz_buscar_en pueden ser texto, nmeros o valores lgicos.

Si ordenado es VERDADERO, los valores de la primera fila de matriz_buscar_en deben colocarseen orden ascendente: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo contrario, BUSCARHpuede devolver un valor incorrecto. Si ordenado es FALSO, no es necesario ordenarmatriz_buscar_en.

Indicador_filas Obligatorio. El nmero de fila en matriz_buscar_en desde el cual debedevolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila enmatriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila enmatriz_buscar_en y as sucesivamente. Si indicador_filas es menor que 1, BUSCARH devuelveel valor de error #VALOR!; si indicador_filas es mayor que el nmero de filas en

matriz_buscar_en, BUSCARH devuelve el valor de error #REF!. Ordenado Opcional. Un valor lgico que especifica si BUSCARH debe localizar una

coincidencia exacta o aproximada. Si es VERDADERO o se omite, devolver una coincidenciaaproximada. Es decir, si no encuentra ninguna coincidencia exacta, devolver el siguiente valormayor que sea inferior a valor_buscado. Si es FALSO, BUSCARH encontrar una coincidenciaexacta. Si no encuentra ninguna, devolver el valor de error #N/A.

FUNCIONES ANIDADASEn algunos casos, puede que deba utilizar una funcin como uno de los argumentos (argumento:

valores que utiliza una funcin para llevar a cabo operaciones o clculos. El tipo de argumento queutiliza una funcin es especfico de esa funcin. Los argumentos ms comunes que se utilizan en lasfunciones son nmeros, texto, referencias de celda y nombres.) de otra funcin. Por ejemplo, lasiguiente frmula utiliza una funcin anidada PROMEDIO y compara el resultado con el valor 50.

Resultados vlidos Cuando se utiliza una funcin anidada como argumento, deber devolverel mismo tipo de valor que el que utilice el argumento. Por ejemplo, si el argumento devuelve un

valor VERDADERO o FALSO, la funcin anidada deber devolver VERDADERO o FALSO. Si ste no esel caso, Microsoft Excel mostrar el valor de error #VALOR!

Lmites del nivel de anidamiento Una frmula puede contener como mximo siete niveles defunciones anidadas. Si la Funcin B se utiliza como argumento de la Funcin A, la Funcin B es unafuncin de segundo nivel. Por ejemplo, la funcin PROMEDIO y la funcin SUMA son ambasfunciones de segundo nivel porque son argumentos de la funcin SI. Una funcin anidada dentrode la funcin PROMEDIO ser una funcin de tercer nivel, etc.

CASO PRACTICO 01

Abrir el libro Consultas.xlsx, en la hoja de clculo Productos deber realizar la consulta de los

productos segn su cdigo, adems deber validar el ingreso de cdigos, tambin el rango de listadeber convertirse en tabla.


65/94

65


1. Ubicado en la hoja Productos, seleccione una celda dentro del rango.

2. En el grupo Estilos, active la lista Dar Formato como tabla, luego seleccione Estilo de tablaMedio 2. Se recomienda realizar los Formatos de tabla cuando se requiere hacer Consultas dedatos.

3. A continuacin Aceptar el mensaje de confirmacin,

4. Debe considerar que la lista se ha convertido en una tabla llamada Tabla1. A cada columna dela Tabla1 Excel le asigna (internamente) un ndice correlativo.


66/94

66


5. Personalice la Tabla y cambie el nombre de la Tabla1 por ARTEFACTOS

6. Luego seleccione el rango [A3:A14] y asgnele el nombre CODIGO.

7. A continuacin ubquese en la celda G3 y realice la validacin con el rango CODIGO, el resultadomostrara:

8. Luego ingrese las formulas en las celdas correspondientes:

H5: =BUSCARV(G3,ARTEFACTOS,2,1)

H6: =BUSCARV(G3,ARTEFACTOS,3,1)H7: =BUSCARV(G3,ARTEFACTOS,5,1)

K5: =BUSCARV(G3,ARTEFACTOS,4,1)

K6: =K5*2.78

9. El resultado mostrara lo siguiente, por ejemplo, cuando seleccione el cdigo PR-012

10.Es importante validar la celda G3, para evitar un dato o cdigo inexistente.

11.Comprobar que al ingresar uno o ms registros nuevos a partir de la fila 15 de la hoja, estos sepueden consultar automticamente. Debido a que la el rango o matriz de bsqueda en unatabla.


67/94

67


CASO PRACTICO 02Ahora seleccione la hoja de clculo Marcas, donde deber realizar la consulta de las Marcas, para

obtener datos como ganancia, margen, etc. Debe validar la celda donde ingrese la Marca a

consultar.1. Seleccione el rango [B1:E4] y asgnele el nombre: RESUMEN, considerar que ahora cada fila de

la lista se convierte en un ndice. Cuando los registros de una lista estn en sentido vertical NOse puede utilizar Formato de tabla.

2. Luego seleccione el rango [B1:E1] y asgnele el nombre: MARCAS

3. A continuacin validar la celda B7, que permita una Lista, con el contenido del rango MARCAS.

4. Luego ingrese las formulas en las celdas correspondientes

B9: =BUSCARH(B7,RESUMEN,2,0)

B10: =BUSCARH(B7,RESUMEN,3,0)

D9: =B10*2.77

D10: =BUSCARH(B7,RESUMEN,4,0)

5. El resultado mostrara lo siguiente, por ejemplo, cuando seleccione la Marca Imaco:

6. Guarde los cambios y cierre el libro.

CASO PRACTICO 03La empresa Yhassir & Co. desea calcular los gastos de la planilla del mes actual y luego realizar

las consultas de trabajadores por cdigo. Para elle debe activar el libro Planilla.xlsx, en la hojaplanilla debe realizar las operaciones de calculo correspondiente y convertir la lista en Formato detabla.

1. Ubicado en la hoja Planilla, realizar los clculos correspondientes a Remuneraciones,Descuentos y Neto a pagar.


68/94

68


2. En la hoja Planilla, seleccione el rango [O4:P6] y crear el rango CONDICION, en este caso solose nombra el rango de bsqueda para que posteriormente la funcin BUSCARV reemplace a lafuncin SI.

3. Ingrese las siguientes formulas y luego los deber copiar:

Sueldo Bsico = se obtiene segn la CONDICION

F3: =BUSCARV(C3,CONDICION,2,0)

Asignacin Familiar = 8% Solo para los que tienen Hijos

G3: =SI(E3>0,8%*F3,0)

Tiempo De Servicio = 15% del bsico para los que tienen de 10 a mas aos de antigedad, casocontrario 8% del bsico.

H3: =SI(D3>=10,15%,8%)*F3

TOTAL REMUNERACIN = Suma de Remuneraciones

I3: =SUMA(F3:H3)

AFP = 5% para los estables, 4% para los contratados del Basico

J3: =SI(C3="ESTABLE",5%,SI(C3="CONTRATADO",4%))*F3

Adelanto = 10% del bsico solo para los Estables

K3: =SI(C3="ESTABLE",10%,0)*F3

TOTAL DE DSCTO. = Suma de Descuentos

L3: =SUMA(J3:K3)

NETO PAGAR = TOTAL REMUN.TOTAL DSCTO.M3: =I3-L3


69/94

69


4. El resultado mostrara los siguientes resultados:

5. Seleccione el rango [A2:M17] y convirtalo en Formato de tabla, el estilo queda a su criterio,Aceptar la caja de confirmacin.

6. Personalice la Tabla y cambie el nombre de la Tabla1 por EMPLEADOS

7. Seleccione el rango [A3:A17] y asgnele el nombre CODIGO.

8. Ahora ubquese en la Hoja Consulta, Valide la celda D2 y convirtalo en una Lista

9. Luego ingrese las siguiente formulas:

B6: =BUSCARV(D2,EMPLEADOS,2,1)

B9: =BUSCARV(D2,EMPLEADOS,3,1)

D9: =BUSCARV(D2,EMPLEADOS,4,1)

F9: =BUSCARV(D2,EMPLEADOS,5,1)


70/94


71/94

71


UNIDAD 10FUNCIONES DE TEXTO

Las funciones de texto permitirn realizar operaciones concaracteres o con parte de un valor de tipo texto, en esta sesinveremos las funciones principales de Texto.

FUNCIN IZQUIERDADevuelve el primer carcter o caracteres de una cadena de texto, segun el nmero de caracteres

que especifique en su segundo argumento.

SintaxisIZQUIERDA(texto,nm_de_caracteres)

Texto Es la cadena de texto que contiene los caracteres que se desea extraer.

Nm_de_caracteres Aqu se indica el nmero de caracteres que se desea extraer desde el primercarcter izquierdo del texto.

Nm_de_caracteres debe ser mayor o igual a cero.

Si num_de_caracteres es mayor que la longitud del texto, IZQUIERDA devolver todo el texto.

Si num_de_caracteres se omite, se calcular como 1.

FUNCIN DERECHALa funcin derecha devuelve el ltimo carcter o caracteres de una cadena de texto, segn el

nmero de caracteres que se especifique en el segundo argumento.

Sintaxis

DERECHA(texto, nm_de_caracteres)

TextoEs la cadena de texto, que contiene los caracteres que se desea extraer.

Nm_de_caracteres Aqu se especifica el nmero de caracteres de la derecha que se deseaextraer

Observaciones:

Nm_de_caracteres debe ser mayor o igual a cero.

Si nm_de_caracteres es mayor que la longitud del texto, DERECHA devolver todo el texto.

Si nm_de_caracteres se omite, se calcular como 1

FUNCIN EXTRAEDevuelve un nmero especfico de caracteres de una cadena de texto, empezando en la posicin

que especifique en el segundo argumento, la cantidad de caracteres hacia la derecha indicado enel tercer argumento.

Sintaxis

EXTRAE(texto, posicin_inicial, nm_de_caracteres)

Texto Es la cadena de texto que contiene los caracteres que se desea extraer.


72/94

72


Posicin_inicial Posicin del primer carcter que se desea extraer del texto. La posicin inicialdel primer carcter de texto es 1, Y as sucesivamente,

Nm_de_caracteres Aqu se indica el nmero de caracteres que se desea extraer desde laposicin inicial hacia la derecha.

Observaciones

Si posicin_inicial es mayor que la longitud de texto, EXTRAE devuelve " " (texto vaco).

Si posicin_inicial es menor que la longitud de texto, pero nm_de_caracteres excede lalongitud de texto, EXTRAE devuelve los caracteres hasta el final de texto.

Si posicin_inicial es menor que 1, EXTRAE devuelve el valor de error #VALOR!

Si nm_de_caracteres es negativo, EXTRAE devuelve el valor de error #iVALOR!

FUNCIN HALLARHALLAR busca una cadena de texto dentro de una segunda cadena de texto y devuelven el

nmero de la posicin inicial de la primera cadena de texto desde el primer carcter de la segundacadena de texto. No distinguen maysculas y minsculas. Es importante destacar que la funcinENCONTRAR funciona de manera similar que la funcin HALLAR pero ENCONTRAR realiza eldiferenciado entre maysculas y minsculas.

Sintaxis

HALLAR(texto_buscado,dentro_de_texto,nm_inicial)

Texto_buscado es el texto que desea encontrar.

Dentro_del_texto es el texto en el que se requiere encontrar el texto buscado.

Nm_inicial es el nmero de carcter dentro del texto donde desea iniciar la bsqueda.

Observaciones Utilice HALLAR para determinar la ubicacin de un carcter o una cadena de texto dentro de

otra cadena de texto.

Si no se puede hallar el argumento texto_buscado, la funcin devuelve el valor de error#iVALOR!.

Si el argumento nm_inicial se omite, el valor predeterminado es 1.

Si el valor del argumento nm_inicial no es mayor que O (cero), o si es mayor que la longituddel argumento dentro_del_texto, se devuelve el valor de error #VALOR!

FUNCIN LARGODevuelve el nmero de caracteres de una cadena de texto.

Sintaxis

LARGO(texto)

Textoes el texto cuya longitud desea saber. Los espacios se cuentan como caracteres.

FUNCIN VALORPermite convierte una cadena de texto en un nmero, si los caracteres del texto se puede

representar en un nmero.

Sintaxis

VALOR(texto)


73/94

73


Texto es el texto entre comillas o una referencia a una celda que contiene el texto que se deseaconvertir.

Observacin

El argumento texto puede tener cualquiera de los formatos de nmero constante, fecha u horareconocidos por Microsoft Excel. Si no tiene uno de estos formatos, VALOR devuelve el valor deerror #VALOR!

FUNCIN CONCATENARUne varios elementos de texto en uno solo.

Sintaxis

CONCATENAR(texto1,texto2; ...)

Texto1, texto2, ... son de 1 a 30 elementos de texto que se unirn en un elemento de textonico. Los elementos de texto pueden ser cadenas de texto, nmeros o referencias a celdas nicas.

ObservacionesPuede utilizar el operador "&" (ampersat) en lugar de CONCATENAR para unir elementos de

texto.

CASO PRACTICO 011. Demostrar el manejo de las diferentes funciones de texto que se han revisado lneas arriba,

para ello abrir el Libro Manejo_de_Textosy en la hoja Funciones desarrollar las operacionesindicadas:

2. Ahora ingrese las siguientes formulas segn las celdas indicadas:

A5: =IZQUIERDA(B2,8)

C5: =DERECHA(B2,11)

C7: =EXTRAE(B2,11,9)

D12: =VALOR(IZQUIERDA(B9,2))+2000

D14: =C7&" "&C5

D16: =HALLAR(":",B2)

D18: =LARGO(B2)


74/94

74


3. El resultado de la operaciones efectuadas mostrara lo siguiente:

4. Guardar los cambios realizados

CASO PRACTICO 02

La empresa Yhassir & Co. Requiere realizar un control de su personal de la Seccin Taller de talmanera que a partir de un cdigo generado se pueda obtener:

a) Determinar el nombre del Area a la que pertenece el trabajador segn la tabla de datos,teniendo como referencia el tercer carcter del cdigo.

b) Determinar el sueldo bsico del trabajador segn la tabla de datos, teniendo como referenciael tercer carcter del cdigo

1. Activar el libro Relacion_Personal, luego seleccione la hoja Taller, seleccione el rango [G2:I4] yasgnele el nombre DATOS

2. Luego ubicado en la celda D2, ingrese la siguiente formula:

=EXTRAE(A2,3,1)

3. Luego de copiar la formula, analice el resultado que mostrara lo siguiente:


75/94

75


4. Vuelva a activar la celda D2 y complete la frmula, utilizando la funcin BUSCARV, que en estecaso reemplaza el uso de la funcin SI:

=BUSCARV(EXTRAE(A2,3,1),DATOS,2,1)5. Luego copie la formula

6. De manera similar se calcula el S-BASICO, en la celda E2, con la variacin de la ubicacin delndice en el rango de bsqueda.

=BUSCARV(EXTRAE(A2,3,1),DATOS,3,1)

7. Despus de copiar la formula, este ser el resultado final:


76/94

76


CASO PRACTICO 03La empresa Yhassir & Co, tambin requiere complementar la informacin del personal del rea

de Servicios, seleccione la hoja Serviciospara ello se debe realizar:

a) Generar el cdigo: primer carcter del apellido paterno + los 2 primeros del apellido materno +categora + "-0" + correlativo de nmeros. Ejemplo el cdigo para el primer empleado deberser: RSOA1-01.

b) El Haber Bsico se obtendr segn la tabla Bsicos, teniendo en cuenta la Categora deltrabajador.

1. En el libro Relacion_Personal, seleccione la hoja Servicios, ubicado en la celda B3, ingresar:

=IZQUIERDA(C3,1)&EXTRAE(C3,HALLAR(" ",C3,1)+1,2)&E3&"-0"&FILA(A1)

Aqu una breve explicacin de la formula:

Luego copie la formula.

2. Se mostrara el siguiente resultado:

OBS: Si utiliza la funcin CONCATENAR, lo hara de la siguiente manera:

=CONCATENAR(IZQUIERDA(C3,1),EXTRAE(C3,HALLAR(" ",C3)+1,2),E3,"-0",FILA(A1))

3. Para calcular el Haber Bsico, deber nombrar el rango [J3:K6] como BASICOS

4. Luego se debe ubicar en la celda F3 e ingresar la siguiente formula:

=BUSCARV(E3,BASICOS,2,0)



77/94

77


6. Guarde los cambios

Evaluacin 2

Planteamiento de trabajo domiciliario

Utilizando el libro Relacion_Personal, en la hoja ConsultaServiciosrealice la consulta por cdigode los trabajadores del rea de Servicios de la siguiente forma:

a) Debe ingresar el cdigo y de manera automtica deber aparecer la informacincorrespondiente al empleado, desglosado por Apellidos y Nombres. (La celda donde se ingresael cdigo deber ser una lista)

b) La Asignacin Familiar (A.FAMILIAR) ser el 5% del Haber Bsico, solo para aquellos

trabajadores que tienen hijos.

c) El Sueldo Neto (NETO) es la suma del Haber Bsico + la Asignacin Familiar

Procedimiento:

1. Convierta en Formato de tabla la lista de la hoja Servicios (el nombre de tabla generado ser:PERSONAL)

2. Seleccione el rango [B3:B19] y asgnele el nombre CODIGO

3. Luego en la hoja ConsultaServicios, la celda B2 debe validarse para que muestre en forma deLista el rango CODIGO.

4. Seleccione un cdigo e ingrese y analice las funciones en las celdas correspondientes:


78/94

78


5. Para completar el desarrollo se deber ingresar las siguientes funciones:

En la celda C4:

En la celda C5:

En las siguientes celdas se completara de la siguiente forma:

6. El resultado deber mostrar lo siguiente:

7. Compruebe el manejo correcto de la consulta, agregando un nuevo trabajador en la tablaPERSONAL de la hoja Servicios

8. Finalmente Guarde los cambios.


79/94

79


UNIDAD 11GAFICOS ESTADISTICOS

Un grfico es la representacin grfica de los datos de una hoja de clculo y facilita suinterpretacin.

La utilizacin de grficos hace ms sencilla e inmediata la interpretacin de los datos.Frecuentemente un grfico nos dice mucho ms que una serie de datos clasificados por filas ycolumnas.

Cuando se crea un grfico en Excel, podemos optar por crearlo:

- Como grfico incrustado: Insertar el grfico en una hoja normal como cualquier otro objeto.

- Como hoja de grfico: Crear el grfico en una hoja exclusiva para el grfico, en las hojas degrfico no existen celdas ni ningn otro tipo de objeto.

Para crear un grfico en Excel, lo primero que debe hacer es especificar en una hoja de los datosnumricos que presentar el grfico. A continuacin, slo tiene que seleccionar el tipo de grficoque desea usar en la pestaa Insertar del grupo Grficos.

Excel ofrece muchos tipos de grficos que le ayudarn a mostrar los datos de forma comprensibleante una audiencia. Cuando cree un grfico o modifique uno existente, puede elegir entre distintos


80/94

80


tipos de grfico (por ejemplo, grficos de columnas o circulares) y subtipos (por ejemplo, grficosde columnas apiladas o grficos circulares 3D). Tambin puede crear un grfico combinado alutilizar varios tipos de grficos en uno solo.

Elementos de los grficosUn grfico consta de numerosos elementos. Algunos de ellos se presentan de forma

predeterminada y otros se pueden agregar segn las necesidades. Para cambiar la presentacin delos elementos del grfico puede moverlos a otras ubicaciones dentro del grfico o cambiar sutamao o su formato. Tambin puede eliminar los elementos del grfico que no desee mostrar.

Caso prctico 1La empresa Yhassir & Co. Requiere realizar un anlisis estadstico de la venta de prendas de vestir

durante el ltimo ao, para poder tomar decisiones en cuanto a la inversin para el siguiente ao,as como tambin las estrategias para mantener una produccin sostenible y ascendente. Losgrficos a crear sern de diversos tipos, adems ser necesario crear monogrficos.

1. Activar el libro Grficos, en la hoja Movimiento seleccione una celda cualquiera (C6).

2. Luego seleccione la Ficha Insertar, abra el tipo Grafico en Columna y seleccione: Columnaagrupada 3D:


81/94

81


3. Se insertara automticamente el grafico, considerando que se puede configurar el grafico dediversas maneras utilizando las fichas Diseo, Presentacin y Formato de la ficha Herramientasde grficos.

4. En la ficha Diseoy en los grupos de comando se pueden realizar operaciones como:


82/94

82


a) Tipo: Permite Cambiar el tipo de grfico (modificar el tipo de grafico actual), Guardar comoplantilla (graba el grafico en un archivo de plantilla para que sea utilizado ms adelante).

b) Datos: Permite Cambiar entre filas y columnas (Intercambia los valores de datos y las seriesque se estn representando), Seleccionar datos (Modifica el rango de datos, las series y lacategora que se est representando).

c) Diseo de grfico: Permite asignar diseos con formatos rpidos pre definidos.

d) Estilos de diseo: Permite cambiar estilos generales del grfico. Abra el botn Mas ySeleccione el Estilo 34.

e) Ubicacin: Permite elegir una ubicacin distinta a la hoja actual.

5. En la ficha Presentacin y en los grupos de comando se pueden ejecutar operaciones como:

a) Seleccin actual: Aplica formato a la seleccin (asigna caractersticas personalizados a loselementos del grafico), Restablecer para hacer coincidir el estilo (borra los formatospersonalizados del elemento para volver al estilo global)

b) Insertar: Permite insertar imagen, formas y cuadro de texto dentro del grafico

c) Etiquetas: Pe

Manual Excel Inicial

Documents

Transcript of Manual Excel Inicial