58
Informática I Hoja Electrónica de Cálculo Pág. 74 Unidad Didáctica Nro. 4 Hoja de Cálculo Los programas cuyo entorno de trabajo ofrecen una “Hoja de Cálculo” son aplicaciones ofimáticas diseñadas para la gestión y operación de todo tipo datos como números, textos, valores lógicos, etc. Gráficamente una Hoja de Cálculo se asemeja a una gran tabla, identificándose a cada una de sus columnas por una letra y a cada una de sus filas por un número, por tanto, cada celda (intersección de una columna y una fila) estaría identificada por una letra y un número. Por ejemplo la celda ubicada en la columna “C” y la fila “4” está identificada por “C4”, respetando el hecho de nombrar primero a la letra de la columna. Tema N° 10 Hoja de Cálculo. Documentos Sencillos Las hojas de cálculo han sido usadas durante cientos de años por los contables antes de que apareciese un programa informático para su manejo. VisiCalc, acrónimo del inglés Visible Calculator, en el año 1979 fue el primer programa para el manejo de una hoja de cálculo. El uso de una hoja de cálculo nos permite agilizar tareas contables, financieras y matemáticas, entre otras, y podemos fácilmente responder a la pregunta “Qué pasa si…” sin necesidad de realizar miles de cálculos manuales. Una hoja de cálculo muestra la información de forma gráfica y la actualiza automáticamente. La posibilidad de convertir los datos en gráficos y diagramas hace más sencillo comprender la interrelación entre números y variables. El programa rehace todos los cálculos cada vez que modificamos alguno de sus valores, lo que nos posibilita ver la evolución de determinados parámetros. Si utilizamos una Hoja de Cálculo para representar los pagos de una hipoteca podemos modificar el tipo de interés, el tipo de amortización o cancelar parte de la deuda y comprobaremos inmediatamente cuanto serían las mensualidades en cada caso. 10.1 Hojas de Cálculo, Aplicaciones y términos Existen en el mercado informático actual diferentes aplicaciones para la gestión de hojas de cálculo, las mismas que son el producto de una evolución que se inició con VisiCal (1981) mencionado anteriormente, pasando por el Lotus 1, 2, 3 (1983) hasta las más actuales como es el caso de CALC que forma parte de la Suite OpenOffice, y también de Ms. Excel de la Suite Microsoft Office. Ms. Excel es una de las aplicaciones de esta categoría que goza de gran popularidad y aceptación a nivel mundial, por lo que será éste el software de estudio en los capítulos subsiguientes. Afortunadamente existe cierto grado de estandarización entre los términos empleados, por lo que los que trataremos en el presente documento podrán ser empleados en cualquier otro aplicativo existente en el mercado. La definición más sencilla de hoja de cálculo es aquella que la describe como una tabla de valores organizada en Filas y Columnas. Cada una de las cajas, producto de la intersección de una fila y una columna se denomina Celda y está identificada por su posición en la hoja. Cada uno de los archivos Excel recibe el nombre de Libro, porque al igual que su equivalente en la vida real, está compuesto por hojas; la extensión de este archivo es XLSX a partir de la versión 2007.

Informatica Basica Excel

Embed Size (px)

Citation preview

Page 1: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 74

Unidad Didáctica Nro. 4 Hoja de Cálculo Los programas cuyo entorno de trabajo ofrecen una “Hoja de Cálculo” son aplicaciones ofimáticas diseñadas para la gestión y operación de todo tipo datos como números, textos, valores lógicos, etc. Gráficamente una Hoja de Cálculo se asemeja a una gran tabla, identificándose a cada una de sus columnas por una letra y a cada una de sus filas por un número, por tanto, cada celda (intersección de una columna y una fila) estaría identificada por una letra y un número. Por ejemplo la celda ubicada en la columna “C” y la fila “4” está identificada por “C4”, respetando el hecho de nombrar primero a la letra de la columna.

Tema N° 10

Hoja de Cálculo. Documentos Sencillos

Las hojas de cálculo han sido usadas durante cientos de años por los contables antes de que apareciese un programa informático para su manejo. VisiCalc, acrónimo del inglés Visible Calculator, en el año 1979 fue el primer programa para el manejo de una hoja de cálculo. El uso de una hoja de cálculo nos permite agilizar tareas contables, financieras y matemáticas, entre otras, y podemos fácilmente responder a la pregunta “Qué pasa si…” sin necesidad de realizar miles de cálculos manuales. Una hoja de cálculo muestra la información de forma gráfica y la actualiza automáticamente. La posibilidad de convertir los datos en gráficos y diagramas hace más sencillo comprender la interrelación entre números y variables. El programa rehace todos los cálculos cada vez que modificamos alguno de sus valores, lo que nos posibilita ver la evolución de determinados parámetros. Si utilizamos una Hoja de Cálculo para representar los pagos de una hipoteca podemos modificar el tipo de interés, el tipo de amortización o cancelar parte de la deuda y comprobaremos inmediatamente cuanto serían las mensualidades en cada caso. 10.1 Hojas de Cálculo, Aplicaciones y términos Existen en el mercado informático actual diferentes aplicaciones para la gestión de hojas de cálculo, las mismas que son el producto de una evolución que se inició con VisiCal (1981) mencionado anteriormente, pasando por el Lotus 1, 2, 3 (1983) hasta las más actuales como es el caso de CALC que forma parte de la Suite OpenOffice, y también de Ms. Excel de la Suite Microsoft Office. Ms. Excel es una de las aplicaciones de esta categoría que goza de gran popularidad y aceptación a nivel mundial, por lo que será éste el software de estudio en los capítulos subsiguientes. Afortunadamente existe cierto grado de estandarización entre los términos empleados, por lo que los que trataremos en el presente documento podrán ser empleados en cualquier otro aplicativo existente en el mercado. La definición más sencilla de hoja de cálculo es aquella que la describe como una tabla de valores organizada en Filas y Columnas. Cada una de las cajas, producto de la intersección de una fila y una columna se denomina Celda y está identificada por su posición en la hoja. Cada uno de los archivos Excel recibe el nombre de Libro, porque al igual que su equivalente en la vida real, está compuesto por hojas; la extensión de este archivo es XLSX a partir de la versión 2007.

Page 2: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 75

10.2 La ventana de Excel, entorno de trabajo.

Al igual que las otras aplicaciones de la Suite Microsoft Office, la interfaz de Ms. Excel está compuesto por: � Barra de Título: Que muestra el nombre del archivo seguido del nombre del programa. � Cinta de Opciones: Con el conjunto de botones de comandos que ofrece el programa. � Zona de Trabajo: Muestra la Hoja de Cálculo en sí. � Barra de Fórmula: Ubicada entre la cinta de opciones y la Zona de trabajo. En la parte izquierda se muestra la

referencia (nombre) a la celda o celdas activas. A continuación una barra de lectura y escritura que muestra la operación o valor ingresado en la celda seleccionada.

10.2.1 Las Celdas Una celda es un cuadrilátero resultante de la intersección de una fila y una columna. La celda activa es la que actualmente se encuentra seleccionada y se le reconoce por tener el borde más intenso respecto a las demás celdas. Al conjunto de celdas seleccionadas se le denomina “Rango”. Para ingresar un valor (texto, número, fecha, etc.) en una celda determinada:

- Seleccionar la celda en la que se va ingresar el dato. - Digitar los datos. - Presionar la tecla “Enter”. Mientras no se presione el dato no ha sido ingresado aún.

10.2.1.1 Formato de Celdas El término formato de celda puede hacer alusión a una de dos posibilidades: a) Formato referido al tipo de contenido

Cuadro de Nombres Cancelar

Insertar Formula

Aceptar

Zona de Edición

Page 3: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 76

que puede almacenar una celda y b) Formato referido a su aspecto (bordes, relleno, fuente, etc.) a. Formato de Celda – Tipo de Contenido: Para cambiar el formato de contenido de una celda o un grupos de

éstas, empleamos la opción “Formato de Número” del grupo Número de la ficha “Inicio”

Entre las opciones disponibles se encuentra:

FORMATO DE NÚMERO DESCRIPCIÓN

General Seleccionado por defecto. Reconoce automáticamente el tipo de dato ingresado.

Número Muestra el contenido de la celda como un valor con dos decimales.

Moneda Muestra el contenido de la celda acompañado del símbolo de moneda.

Contabilidad Muestra el contenido de la celda acompañado del símbolo de moneda, en formato contable.

Fecha Corta Muestra el contenido de la celda en formato de fecha corta. P.E. 22/07/2011.

Fecha Larga Muestra el contenido de la celda en formato de fecha larga. P.E. Jueves, 03 de marzo de 2011.

Hora Muestra el contenido de la celda en formato de Hora. P.E. 02:35:00 p.m.

Porcentaje Multiplica el valor de una celda por 100.

Fracción Muestra el valor de una celda a tipo fraccionario.

Científica Muestra el valor de una celda en formato científico. En números multiplicados por múltiplos de 10.

Texto

Muestra el valor de una celda en formato de texto. Evita en algunos casos que Excel convierta automáticamente a cualquier otro formato. P.E. Si en una celda deseamos mostrar “4-5”, no podremos hacerlo si la celda tiene seleccionado el tipo de formato “General” ya que luego de presionar la tecla Enter, lo interpretará como una fecha mostrando “4-may”; lo cual no sucederá si previamente cambiamos el formato a “Texto”.

b. Formato de Celda – Aspecto: Para cambiar el aspecto gráfico de una celda o un grupo de éstas, emplearemos

tanto el grupo “Fuente” como el grupo “Alineación” de la ficha inicio.

GRUPO: FUENTE

Tipo, tamaño y color de Fuente, negrita, cursiva, subrayado y

relleno.

Al igual que otros programas de la Suite Ms. Office es posible cambiar el aspecto de las fuentes con los controles citados.

Bordes Las celdas mostradas en la hoja de cálculo son referenciales, es decir, no se imprimirán como tales. El control “Borde” permite marcar los

Page 4: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 77

bordes de una o varias celdas a fin de que se muestren en la hoja impresa (Señalado en el gráfico)

GRUPO: ALINEACIÓN

Alineación Horizontal y Vertical

Se configura con los 6 primeros botones del grupo: Alineación Horizontal: Izquierda, Centro, Derecha. Alineación Vertical: Superior, Medio, Inferior. Lo cual da un total de 9 posibles combinaciones de alineación.

Orientación

Este control permite cambiar la orientación del texto contenido en las celdas. P.E.

Ajustar Texto

Este control permite que la celda cambie de tamaño en función al texto que contiene.

Combinar y Centrar

Al ejecutar este comando sobre un grupo de celdas seleccionadas, hará que se comporten como una sola.

C) Comentarios en Celdas Los comentarios permiten especificar o dar más detalles sobre el valor que contiene una celda. Para agregar un comentario, clic derecho sobre la celda y elegir “Insertar comentario”.

� Procedimiento para insertar comentario. � Cuando una celda contiene un comentario, muestra un pequeño triangulo de color rojo en la parte superior derecha.

Page 5: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 78

� El comentario se visualiza al pasar el cursor sobre la celda.

10.2.2 Rangos Un rango es un conjunto de celdas contiguas, las que se pueden referenciar como si se tratara de una sola, facilitando su uso. Para asignar un nombre determinado a un rango: 1ro. Seleccionar las celdas que formaran parte del rango. 2do. Digitar el nombre en el “Cuadro de Nombres” (ubicado a la izquierda de barra de fórmulas) En el siguiente ejemplo se crea un rango llamado “Edad” y luego se hacen cálculos con él.

1ro. Seleccionar el grupo de celdas que formaran parte del rango. En este caso de B2 hasta B6. 2do. En el “Cuadro de Nombres” escribir el nombre del rango, para el caso del ejemplo es “Edad”

P.E. Para efectuar operaciones con las edades, ya no será necesario referenciarlas con el nombre de las columnas y filas (B2:B6) sino únicamente por el nombre de rango asignado: “Edad”

P.E. para sumar todos los valores de B2 a B6: =SUMA(Edad)

Los nombres de rangos asignados no son editables directamente, por lo que, si desea modificarlos o eliminarlos se deberá ingresar al “Administrador de Nombres”: Ficha “Formulas”, Grupo “Nombres Definidos”, botón “Administrador de Nombres”

Este comando muestra un asistente (ventana) que permite configurar los nombres de rangos.

10.2.3 Hojas de Cálculo

10.2.3.1 Administración de Hojas de Cálculo Por defecto cada libro de Ms. Excel tiene 3 hojas, identificadas como Hoja1, Hoja2 y Hoja3. En caso el número de hojas de un libro sobrepase el espacio reservado se podrá emplear los botones de desplazamiento para acceder a ellas.

� Crear Nueva Hoja de Cálculo

- Clic en el botón “Insertar hoja de cálculo”, o

Botones de Desplazamiento

Hojas Disponibles Botón: “Insertar Hoja de Cálculo”

1

2

Page 6: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 79

- Clic derecho sobre cualquiera de las hojas, y en el menú contextual elegir “Insertar”. - Presionar simultáneamente Shift + F9.

� � Eliminar Una Hoja de Cálculo

- Clic derecho sobre el nombre de la hoja a eliminar. - En el menú contextual elegir “Eliminar”

� Mover O Copiar Una Hoja De Cálculo

- Para mover una hoja, clic sobre el nombre de la hoja deseada y arrastrar hacia el lado derecho o izquierdo.

- Para copiar una hoja, clic derecho sobre la hoja a copiar, del menú contextual seleccionar “Mover o Copiar”, en la ventana del mismo nombre elegir el libro al cual se desea copiar (también debe estar abierto) y a continuación activar el check “Crear una Copia”.

� Cambiar Nombre Y Color De Etiqueta A Una Hoja

- Para cambiar el nombre, clic derecho sobre la hoja a cambiar y seleccionar en el menú contextual la opción “Cambiar nombre” y a continuación digitar.

- Para cambiar el color de la etiqueta de una hoja, clic derecho sobre la hoja a cambiar y seleccionar la opción “Color de Etiqueta”; elegir el color deseado.

Las opciones descritas se encuentran en el menú contextual de cada hoja:

� Inmovilizar Paneles En ocasiones, cuando el contenido de una hoja de cálculo sobrepasa el ancho de la ventana, es necesario que cierta información de ella se continúe mostrando así nos desplacemos hacia la izquierda o hacia abajo, PE. Los títulos. Para ello existe la opción denominada “Inmovilizar” que se ubica en el grupo “Ventana” de la ficha “Vista”. Al elegir la opción “Inmovilizar paneles” no es posible mover la fila ni la columna anterior a la celda seleccionada.

Libro a la que se copiará la hoja.

Ubicación de la hoja copiada.

Activar

Page 7: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 80

Por ejemplo, en una hoja de cálculo se desea mantener inamovible la primera fila que está completada con títulos y la primera columna que contiene un número consecutivo por cada fila. Entonces primero seleccionamos la celda “B2” y a continuación el grupo de opciones indicados.

10.2.4 Formato de Hojas de Cálculo: Gestión de Filas y Columnas � INSERTAR FILAS Y COLUMNAS Para insertar una fila o una columna dar clic derecho sobre la letra identificadora de una columna o sobre un número identificador de una fila y seleccionar la opción “Insertar”. Cabe aclarar que las columnas y filas que se insertan, se ubican antes de la columna o fila sobre la que se activó la opción de inserción. En el siguiente ejemplo se inserta una columna antes de la columna C (Ap. Materno) a fin de completar la columna de Ap. Paterno. De igual forma se adiciona una fila antes de la nro. 5 para completar la persona con código “113”.

Tabla Original

Agre

gar u

na C

olumn

a

Zona Movible

Page 8: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 81

Agre

gar u

na F

ila

� ELIMINAR FILAS Y COLUMNAS Para eliminar una fila o una columna, clic derecho sobre su identificador (Letra o número según sea el caso) y elegir la opción “Eliminar”. P.E. para eliminar la columna “A”: � MODIFICAR ALTO DE FILAS Y ANCHO DE COLUMNAS Ubicar el cursor del mouse en la intersección entre los identificadores de dos columnas o dos filas, hasta que

cambie a la forma o y arrastrar hasta el tamaño deseado. Para cambiar exactamente al mismo ancho a dos o más columnas (o filas), seleccionarlas y cambiar de tamaño a cualquiera de ellas. P.E. la siguiente acción permitirá que la columna A y B tengan el mismo ancho de 15 ptos.

10.2.5 Ver e Imprimir Hojas de Cálculo A fin de visualizar un documento antes de imprimirlo y configurar las opciones de impresión, Excel provee la opción “Imprimir”, la misma que se ubica en la ficha “Archivo” de la cinta de opciones. El panel izquierdo permite configurar las preferencias de impresión, mientras que el panel derecho muestra una vista previa de la hoja de cálculo.

10.3 Inserción De Fórmulas Y Funciones

10.3.1 Formula Una fórmula es una expresión que se ingresa a una celda y permite realizar cálculos o procesamiento de valores, generando un nuevo valor como resultado en la celda donde se introdujo la fórmula. En una fórmula por lo general, intervienen valores que se encuentran en un o más celdas de un libro de trabajo. Están conformadas por operadores, operandos y con frecuencia por funciones. Para introducir una fórmula en una celda, es necesario, en primer lugar, digitar el carácter = (igual), ya que ello le indica a Excel que lo que se escriba a continuación forma parte de una Fórmula. Por ejemplo para obtener el resultado de 15+5*3 en una celda determinada, la sintaxis sería la siguiente:

=15+5*3

Page 9: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 82

10.3.2 Componentes De Una Fórmula Una fórmula puede contener cualquiera de los siguientes elementos: Referencias a otras celdas, constantes, operadores y funciones.

10.3.2.1 OPERADORES Los operadores especifican el tipo o procesamiento que se desea realizar con los elementos de una fórmula. Ms. Excel agrupa los operadores en cuatro categorías: Aritméticos, De comparación, para Textos y de Referencia. Operadores Aritméticos: Se utilizan para realizar operaciones matemáticas básicas.

Operador Ejemplo

Símbolo Nombre

+ Suma =A3+3

- Resta =A5-B6

* Multiplicación =A2*61

/ División =C4/C2

% Porcentaje 20%

^ Exponente =C7^2

Operadores de Comparación: Se utilizan para comparar dos valores.

Operador Ejemplo

Símbolo Nombre

= Igual a =A1=B1

< Menor a =A5<70

> Mayor a =C2>D10

<= Menor igual a =C6<=8

>= Mayor igual a =G2>=45

<> Diferente =D1<>100

Cuando se utilizan éstos operadores, es el resultado es un valor lógico: VERDADERO o FALSO. Operadores para Textos: Se dispone del operador & (Ampersand) para concatenar o unir textos.

Operador Ejemplo

Símbolo Nombre

& Concatenar = A2 & A5

=”Es resultado es: “ & A1

Operadores de Referencia: Permiten especificar un grupo de celdas para emplearlas en otras operaciones.

Operador Ejemplo

Símbolo Nombre

=A1+PROMEDIO(B2:B4)*15

Referencias a otras celdas

Funciones

Operadores Constantes

Page 10: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 83

: Dos puntos: Equivale literalmente a “HASTA”

=SUMA(A1:A5) Literalmente: Sumar los valores contenidos desde la celda A1

HASTA A5.

, Coma: Equivale literalmente a “Y” =SUMA(B2;B5)

Literalmente: Sumar los valores contenidos en las celdas B2 Y B5

(espacio) Hace referencia a las celdas comunes a dos rangos.

B7:D7 C6:C8 Hace referencia a C7

10.3.2.2 Precedencia De Los Operadores Si se combinan varios operadores en una única fórmula, Excel ejecutará las operaciones en el orden correspondiente a cada operador según la tabla mostrada a continuación. Si una formula contiene operadores con la misma precedencia (P.E. si la formula contiene solo multiplicaciones y divisiones), se ejecutarán de izquierda a derecha. Si lo que se desea es alterar el orden de operación, se emplearán los paréntesis a modo de agrupadores.

Orden Operador(es)

1ro () Paréntesis

2ro : (Dos puntos) , (Coma)

3ro - Cambio de Signo

4to %

5to ^

6to * y /

7mo + y -

8vo &

9no =, <, >, <=, >=, <>

10.3.3 Referencias Una referencia a una celda o grupos de éstas, permite determinar y le indica a Excel en que celdas debe buscar los valores o los datos que se requieran en un fórmula. P.E.: En =B2+B3*B6 se está haciendo referencia a las celdas B2, B2 y B6.

10.3.3.1 Referencia en una misma Hoja de Cálculo

Expresión Hace referencia a:

A10 La celda que se encuentra en la columna A y la fila 10.

A10:A20 Rango de celdas de la columna A, de la fila 10 a la 20.

B15:E15 Rango de celdas de la fila 15, de la columna B a la E.

A10:E20 Rango de celdas de la columna A a la E, y de la fila 10 a la 20.

5:5 Todas las celdas de la fila 5.

5:10 Todas las celdas de la fila 5 a la 10.

Page 11: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 84

G:G Todas las celdas de la columna G.

G:L Todas las celdas de la columna G a la L.

10.3.3.2 Referencia a celdas de otras Hojas de Cálculo Al referenciar a celdas ubicadas en otras hojas del libro, se debe anteponer al rango de celdas el nombre de la hoja seguido del carácter “!”. En el siguiente ejemplo se calcula el promedio de las celdas B5 a la B10 de la hoja llamada “Marketing”

=PROMEDIO(Marketing!B5:B10) Si el nombre de la hoja contiene espacios, éste debe escribirse entre apóstrofes:

‘MKT Comercial’

10.3.3.3 Referencias Relativas Una referencia relativa se denomina así porque cambia cuando se modifica o se copia la fórmula de una celda a otra. En el ejemplo mostrado, si se copia el contenido de la celda D2 a D3 y D4 respectivamente, las fórmulas se actualizarán para cada producto de forma automática. Lo indicado se resume en la siguiente tabla:

D2 =B2*C2

D3 =B3*C3

D4 =B4*C4

10.3.3.4 Referencias Absolutas En caso se requiera que no cambien las referencias al mover o copiar las fórmulas de una celda a otra, emplearemos las referencias absolutas, lo cual se logrará acompañando a las referencias con el carácter $ (dólar). En el ejemplo siguiente se desea calcular el IGV de cada producto, para lo cual, se multiplica cada precio por el valor contenido en C1 (19%). De copiar la fórmula contenida en C4 se tendrá lo siguiente:

C4 =B4*C1

C5 =B5*C2

C6 =B6*C3

Como se observa, para el caso de las celdas C5 y C6 la fórmula es incorrecta. A fin de evitar que la fila de C1 cambie le aplicaremos referencia absoluta empleando el carácter $, quedando de la siguiente manera:

C4 =B4*C$1

C5 =B5*C$1

C6 =B6*C$1

Page 12: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 85

Tema Nro. 11

Funciones

11.1 Funciones de Fecha Y Hora

HOY():La función HOY devuelve la fecha actual, según el reloj interno controlado por tu ordenador. Sintaxis =HOY() La función no requiere ningún argumento. Ejemplo En este ejemplo se insertó la función HOY en la celda D18 con formato general (representa un número de serie). En la celda D19 se insertó la misma función pero con formato de fecha. A continuación se presenta la solución.

AHORA():La función AHORA devuelve la fecha, hora y minutos actuales, según el reloj interno controlado por tu ordenador. Sintaxis =AHORA() La función no requiere ningún argumento. Ejemplo En este ejemplo se insertó la función AHORA en la celda D18 con formato general (representa un número de serie). En la celda D19 se insertó la misma función pero con formato de fecha. A continuación se presenta la solución.

MES():La función MES devuelve el número de mes de una celda que contiene una fecha. Sintaxis = MES(núm de serie) núm de serie: es un valor, celda o función que devuelve un valor de tipo fecha. Ejemplo En el rango F18:F21 se extrae el número de mes correspondiente para las celdas del rango E18:E21. A continuación se presenta la solución.

AÑO():La función AÑO devuelve el año de una celda que contiene una fecha. Sintaxis = AÑO(núm de serie) núm de serie: es un valor o celda de tipo fecha. Ejemplo En el rango F18:F21 se extrae el año correspondiente para las celdas del rango E18:E21.

Page 13: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 86

DIA() : La función DIA devuelve el número de día de una celda que contiene una fecha. Sintaxis = DIA(núm de serie) núm de serie: es un valor o celda de tipo fecha o una función que devuelva una fecha. Ejemplo En el rango F18:F21 se extrae el número de día correspondiente para las fechas del rango E18:E21. A continuación se presenta la solución.

11.2 Funciones de Texto CONCATENAR(texto1,texto2,...): Esta función une o enlaza dos o más cadenas de texto, dadas como argumento, para concatenar podemos emplear el operador “&” Ejemplo, en la siguiente figura, puede observar que en la celda D2 se ha escrito la fórmula =CONCATENAR(A2, “ tiene ”,B2 , “años” ) la cual también podría desarrollarse de la siguiente manera (mediante el operador &) =A2&“ tiene ”&B2&“años”. Excel reemplaza A2 por “Pedro Pérez” y B2 por “24” que son los contenidos de las respectivas celdas. “El resultado es Pedro Pérez tiene 24 años”. Después se copia la celda D2 al rango D3:D6 y Excel automáticamente llena las celdas, tal como puede observar en la figura.

MAYUSC(texto): Convierte en mayúsculas todos los caracteres de la cadena dada como argumento. Ejemplo:

� MAYUSC(“Computador Portátil”) da como resultado “COMPUTADOR PORTATIL”. MINUSC(texto): Convierte en minúsculas todos los caracteres de la cadena dada como argumento. Ejemplo:

� MINUSC( “Computador Portátil” ) da como resultado “computador portátil” . NOMPROPIO(texto): Devuelve la cadena de caracteres del argumento texto, con la primera letra de cada palabra en mayúsculas y las demás letras en minúsculas. Ejemplo:

� NOMPROPIO(“UNO MAS UNO dos”) da como resultado “Uno Más Uno Dos” IZQUIERDA(texto,núm_de_caracteres): Toma el argumento texto y devuelve una subcadena formada por la cantidad de caracteres indicada en el argumento núm_de_caractes. La función toma los caracteres más a la izquierda. Ejemplo:

� IZQUIERDA(“educación”,4) da como resultado “educ”. � IZQUIERDA(“color azul”,5) da como resultado “color”.

DERECHA(texto,núm_de_caracteres): Toma la cadena de caracteres del argumento texto y devuelve la cantidad de caracteres más a la derecha del texto, indicada por el argumento núm_de_caracteres. Ejemplo:

� DERECHA(“cuaderno”,4) da como resultado “erno”

Page 14: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 87

� DERECHA(“color rojo”,4) da como resultado “rojo” . LARGO(texto): El resultado de esta función es la longitud del texto que viene como argumento. Ejemplo:

� LARGO(“rosa roja”) da como resultado 9 . � LARGO(“ rosa roja ”) da como resultado 12 . Aparentemente en los dos ejemplos las cadenas de

caracteres son iguales, sin embargo, son diferentes porque el de abajo tiene espacios adicionales.

MED(texto,posición_inicial,núm_de_caracteres): Esta función devuelve una subcadena de texto, a partir de la cadena dada como primer argumento. El argumento posición_inicialindica a partir de qué carácter va a tomar la subcadena, contando de izquierda a derecha. El argumento núm_de_caracteresdice cuántos caracteres va a tomar. Ejemplo:

� MED(“Mi ventana”,1,4) da como resultado “Mi v”. � MED(“color rojo”,7,4) da como resultado “rojo”. � MED(“abecedario”,6,10) da como resultado “dario”.

HALLAR(texto_buscado,dentro_del_texto,núm_inicial): Esta función se utiliza para localizar una cadena de caracteres dentro de otra. El argumento dentro_del_textoes en el cual se va a localizar la subcadena. El argumento texto_buscadoes la cadena que se va a buscar. El tercer argumento, núm_inicial, es la posición a partir de la cual va a iniciar la búsqueda, contando de izquierda a derecha. Ejemplo:

� HALLAR(“color”, “El color de la fama”,1) da como resultado 4. � HALLAR(“DOS”, “uno más uno dos”,1) da como resultado 13 . Observe que esta función al hacer

la búsqueda no hace distinción entre mayúsculas y minúsculas. � HALLAR(“TRES”,“unomás uno dos”,1) da como resultado #¡VALOR! , ya que la búsqueda no tuvo

éxito. Ejercicio Nº 1: Se solicita completar las columnas siguientes:

� Id. de cliente: será la mayúscula de los dos primeros caracteres del nombre de la empresa y los tres primeros caracteres del nombre del cliente, por ejemplo “Boticas del Perú” y el cliente “María Anders” se obtiene: BOMAR

� En Información completa, deberá concatenar el nombre del cliente, el texto “trabaja en” y el nombre del cliente.

� En la columna Ciudad en propio, como se menciona, el nombre de la ciudad en propio � El nuevo código se obtendrá con los dos primeros caracteres de la empresa del cliente, el texto

“PERU” y los tres primeros caracteres del nombre del cliente. � En letras del nombre, deberá mostrar el número de caracteres del nombre de clientes.

Ejercicio Nº 2: Se pide elaborar una hoja según el formato que se muestra en la imagen, que permita ingresar por teclado un texto desde la cual se deberá obtener lo solicitado en los cuadros respectivos.

Page 15: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 88

11.3 Funciones Matemáticas y Trigonométricas

ALEATORIO (): Esta función devuelve un valor al azar comprendido entre 0 y 1. Esta función no tiene argumentos. Cada vez que se genere un valor aleatorio será diferente al anteriormente calculado.

PI (): Función cuyo valor es aproximadamente 3,14159265358979323846…

SUMA(número1,número2,...): El resultado devuelto por esta función es la suma de los argumentos número1, número2, etc.. Los valores no numéricos no son tenidos en cuenta para el cálculo. Los argumentos número1, número2, etc., pueden ser una constante, la dirección de una celda, un rango de celdas. Ejemplo: SUMA(3,3,5,8) da como resultado 19

PRODUCTO(número1,número2,...): Multiplica todos los valores dados como argumentos y devuelve su resultado. Los valores no numéricos no son tenidos en cuenta para el cálculo. Los argumentos número1, número2, etc., pueden ser una constante, la dirección de una celda, un rango de celdas. Ejemplo: PRODUCTO(3,2,5,1) da como resultado 30

RESIDUO(número,núm_divisor): La función divide el argumento número entre núm_divisor y devuelve el residuo o resto de esta división. Si la división es exacta, el residuo da como resultado cero. Ejemplo: RESIDUO(20,5) da como resultado 0

POTENCIA (número,potencia): Toma el argumento número y lo eleva a la potencia indicada por el argumento potencia, otra forma es con el operador “^”. Ejemplo: 25 se podrá obtener con el operador =2^5 ó con la función =POTENCIA(2, 5) en ambos casos da el mismo resultado 32

RAIZ(número): Devuelve la raíz cuadrada del argumento número. Por ejemplo RAIZ(25) da como resultado 5. Importante: No existe función para la raíz cúbica, raíz cuarta,……, en estos casos trabajarlo por su equivalente en potencia. Por ejemplo: su fórmula será: POTENCIA(8, 1/3)

GRADOS: Convierte el argumento expresado en radianes a grados. Por ejemplo: GRADOS(PI()) da como resultado 180 grados

COS(número): Esta función calcula el coseno del argumento número. El ángulo viene expresado en radianes por la que deberá convertir en sexagesimal mediante la función GRADOS. Por ejemplo, al calcular el COS(60º) la fórmula es: =COS(GRADOS(PI()*60/180) ó =COS(GRADOS(PI()/3)

SENO(número): Esta función devuelve el seno del ángulo especificado en el argumento número. El ángulo va expresado en radianes por la que deberá convertir en sexagesimal mediante la función GRADOS. Por ejemplo, al calcular el SENO(30º) la fórmula es: =SENO(GRADOS(PI()*30/180) ó =SENO(GRADOS(PI()/6)

TAN(número): Esta función devuelve la tangente del ángulo especificado en el argumento número. El ángulo va expresado en radianes por la que deberá convertir en sexagesimal mediante la función GRADOS. Por ejemplo, al calcular el TAN(45º) la fórmula es: =TAN(GRADOS(PI()*45/180) ó =SENO(GRADOS(PI()/4)

Ejercicio Nº 1: En B y C calcular lo solicitado…

Page 16: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 89

Ejercicio Nº 2: en una nueva hoja de cálculo, calcular lo solicitado

� En D4 obtener el promedio de los números A � En D6 obtener el promedio de los números B � En A7 obtener la suma de los números de A � En B7 obtener la suma de los números de B � En E7 obtener la suma de todos los números A y B � En A8 obtener el máximo de los números A y en B8 de los números B respectivamente � En A9 obtener el mínimo de los números A y en B9 de los números B respectivamente � En E8 obtener el máximo de todos los números y en E9 el mínimo de todos los números

Ejercicio Nº 3: Calcular lo solicitado

Ejercicio Nº 4: Calcular lo solicitado

Ejercicio Nº 5: Calcular lo solicitado

Page 17: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 90

12.1 FUNCIONES ESTADÍSTICAS Algunas de las funciones estadísticas son:

Función Descripción

PROMEDIO

Devuelve el promedio de los argumentos, los cuales pueden ser numéricos, nombres, matrices o referencias que contengan números.

En la celda C2 escribimos:

=PROMEDIO(A2:A4)

MAX

Devuelve el valor máximo de una lista de valores. Omite los valores lógicos y el texto.

En la celda C2 escribimos:

=MAX(A2:A4)

MIN

Devuelve el valor mínimo de una lista de valores. Omite los valores lógicos y el texto.

En la celda C2 escribimos:

=MIN(A2:A4)

CONTAR

Cuenta el número de celdas de un rango que contienen números.

En la celda D2 escribimos:

=CONTAR(A2:A4) CONTARA Cuenta el número de celdas no vacías de un rango.

Page 18: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 91

En la celda D2 escribimos:

=CONTARA(A2:A6)

12.2 FUNCIONES CONDICIONALES

Estas funciones permiten obtener resultados basados en ciertos criterios de comparación y así obtener únicamente la información que necesitamos dentro de todo un conjunto de datos. Supongamos que tenemos la siguiente tabla:

A continuación explicaremos 4 de las más importantes funciones condicionales de uso común y que ayudarán a obtener únicamente la información necesaria.

FUNCIÓN SUMAR.SI

Por ejemplo en la celda G15 nos piden hallar cuánto es el Neto Pagado en total a los trabajadores que sólo pertenecen a la Categoría A. Por lo tanto sólo tendría que sumar los valores de las celdas G8 + G10 + G11 + G13; puesto que sólo esos valores cumplen con el Criterio (Categoría A). Esta labor se realiza de forma simple utilizando la función SUMAR.SI y devolverá el resultado esperado.

La sintaxis de la función es la siguiente: =SUMAR.SI(Rango_Comparación,Criterio,Rango_Suma)

En la celda G15, debemos escribir la siguiente fórmula:

=SUMAR.SI(Rango_ Comparación , Criterio , Rango_ Suma)

=SUMAR.SI(C8:C13,”A”,G8:G13)

Page 19: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 92

En la celda G16, debemos escribir la siguiente fórmula:

=SUMAR.SI(Rango_ Comparación , Criterio , Rango_ Suma)

=SUMAR.SI(F8:F13,”>0”,G8:G13)

FUNCIÓN SUMAR.SI.CONJUNTO

La aplicación de la función SUMAR.SI.CONJUNTO es muy parecida al SUMAR.SI; la única diferencia es que con esta nueva función podremos evaluar 2 a más criterios a la vez. Por ejemplo en la celda G17 nos piden hallar cuánto es el Neto Pagado en total a los trabajadores que pertenecen a la Categoría A y que a la vez perciben bonificación (2 criterios). Por lo tanto sólo tendría que sumar los valores de las celdas G10 + G11; puesto que sólo esos valores cumplen con los dos criterios establecidos (Categoría A y Bonificación > 0).

La sintaxis de la función es la siguiente:

=SUMAR.SI.CONJUNTO(Rango_Suma,Rango_Criterio1,Criterio1,Rango_Criterio2,Criterio2, …) En la celda G17, debemos escribir la siguiente fórmula:

=SUMAR.SI.CONJUNTO(G8:G13,C8:C13,”A”,F8:F13,”>0”)

=SUMAR.SI(Rango_Suma , Rango_Criterio 1, Criterio1 , Rango_Criterio 2 , Criterio 2)

FUNCIÓN CONTAR.SI

La función CONTAR.SI cuenta únicamente aquellas celdas que contengan un valor que cumpla con cierta condición.

Por ejemplo en la celda G19 nos piden hallar cuántos trabajadores en total pertenecen a la Categoría A. Por lo tanto sólo tendría que contar a 4 Trabajadores; puesto que sólo esa cantidad de Trabajadores cumplen con la condición (Categoría A). La sintaxis de la función es la siguiente: =CONTAR.SI(Rango , Criterio) En la celda G19, debemos escribir la siguiente fórmula:

=CONTAR.SI(Rango , Criterio)

Page 20: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 93

=CONTAR.SI(C8:C13,”A”)

En la celda G20, debemos escribir la siguiente fórmula:

=CONTAR.SI(Rango , Criterio)

=CONTAR.SI(F8:F13,”>0”)

FUNCIÓN CONTAR.SI.CONJUNTO

La aplicación de la función CONTAR.SI.CONJUNTO es muy parecida al CONTAR.SI; la única diferencia es que con esta nueva función podremos evaluar 2 a más criterios a la vez.

Por ejemplo en la celda G21 nos piden hallar cuántos trabajadores en total pertenecen a la Categoría A y que a la vez perciben bonificación (2 criterios). Por lo tanto sólo tendría que contar a 2 Trabajadores; puesto que sólo esa cantidad de Trabajadores cumplen con los dos criterios establecidos (Categoría A y Bonificación > 0).

La sintaxis de la función es la siguiente:

=CONTAR.SI.CONJUNTO(Rango_Criterio1 , Criterio1 , Rango_Criterio2, Criterio2 , …)

En la celda G21, debemos escribir la siguiente fórmula:

=CONTAR.SI(Rango de Criterio 1, Criterio1 , Rango de Criterio 2 , Criterio 2)

=CONTAR.SI.CONJUNTO(C8:C13,”A”,F8:F13,”>0”)

RESULTADO FINAL

Page 21: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 94

13.1 Funciones Lógicas Las funciones lógicas son importantes porque a través de una expresión se pueden evaluar datos o valores que permitan responder a muchas interrogantes. Por ejemplo ¿Cuántos alumnos aprobaron el curso de Word con una nota mayor igual a 15 y menor igual a 20?, ¿Cuántos alumnos, calificaron, si aprobaron el Examen1 ó el Examen2 con notas mayor a 15 y ,menores a 20?, ¿Cuántos alumnos calificaron si aprobaron el Examen1 y el Examen2 con notas mayor a 15 y menores a 20?. Constantemente nos hacemos preguntas o damos alguna orden; de la siguiente manera: Compra el libro de Word o compra el libro de Excel que no es igual a decir compra el libro de Word y el de Excel.

FUNCION SI

La función SI devuelve un valor entre dos posibles valores, dependiendo de una condición indicada. Veamos el siguiente ejemplo para entenderla mejor. Sintaxis

=SI(prueba lógica, [valor si verdadero], [valor si falso]) prueba lógica: es una comparación entre dos celdas usando operadores lógicos. Recuerda que los operadores lógicos son: =(igual), <(menor), >(mayor), <>(distinto), >=(mayor o igual), <=(menor o igual). [valor si verdadero]: es el valor, celda o texto especificado a devolver si prueba lógica es verdadera. [valor si falso]: es el valor, celda o texto especificado a devolver si prueba lógica es falsa. Ejemplo: Un profesor está analizando las calificaciones de sus alumnos en el rango C3:C10. En el rango D3:D10 desea que según la calificación mínima necesaria de la celda F3, aparezca un mensaje indicando si el alumno aprobó o no. Dichos mensajes están en las celdas H3 y H6. A continuación se presenta la solución.

Ejercicio Utilizando la función SI intenta distinguir los alumnos que tengan una asistencia igual o mayor a la indicada en la celda H48. En caso de tenerla debe aparecer el mensaje de la celda J48, caso contrario debe aparecer el mensaje de la celda J51. Inserta la función en la celda F48 y cópiala hasta la celda F55.

Page 22: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 95

Función SI Anidada Es aquella que tiene varios SI en su sintaxis.

Ejemplo: Es una empresa se requiere clasificar a los usuarios por código de acuerdo a lo siguiente: Si el código es igual a A100 va a pertenecer a “Informática”, si el código es igual a “A200” va a pertenecer a “Financiero”, si el código es igual a “A300” va a pertenecer a “Ventas”, si el código es igual a “A400” va a pertenecer a “Información”, si el código es igual a “A500” va a pertenecer a “Recursos Humanos” en caso contrario No hay clasificación.

En la celda E4 se debe digitar la siguiente condición lógica para obtener el resultado. =SI(A4="A100","Informática",SI(A4="A200","Financiero",SI(A4="A300","Ventas",SI(A4="A400","Información",SI(A4="A500","Recursos Humanos","No hay clasificación")))))

FUNCION Y La función Y evalúa una serie de condiciones. Si todas las condiciones son verdaderas devuelve VERDADERO. Si al menos una de ellas es falsa devuelve FALSO. Veamos el siguiente ejemplo para entenderla mejor. Sintaxis:

=Y(valor lógico1, [valor lógico2],...) valor lógico1; [valor lógico2];… : pueden ser hasta 30 condiciones que se desean probar en las cuales se obtenga como resultado un valor lógico tipo VERDADERO o FALSO. Ejemplo: En el rango D18:E25 se tiene un listado del personal de una empresa, con su respectiva edad. El gerente desea identificar aquellas personas con edad mayor a la indicada en la celda H18 y menor o igual a la indicada en la celda K18. El resultado deber aparecer en el rango F18:F25. Veamos la solución:

Page 23: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 96

Ejercicio Utilizando la función Y intenta indicar las personas de esta empresa cuya edad sea mayor a lo indicado en la celda J48 y que además residan en la zona indicada en la celda J51. Inserta la función en la celda G48 y cópiala hasta la celda G55.

FUNCION O

La función O evalúa una serie de comparaciones. Si al menos una de ellas es verdadera, la función devuelve VERDADERO. Solo si todas ellas son falsas, devuelve FALSO. Veamos el siguiente ejemplo para entenderla mejor.

Sintaxis =O(valor lógico1, [valor lógico2],...)

valor lógico1; [valor lógico2];… : pueden ser hasta 30 condiciones que se desean probar en las cuales se obtenga como resultado un valor lógico tipo VERDADERO o FALSO.

Ejemplo: En este ejemplo se tiene un listado del personal de una empresa, con su respectivo día de trabajo. El gerente desea identificar aquellas personas que trabajaron alguno de los días indicados en las celdas H18 y K18. El resultado deber aparecer en el rango F18:F25. Veamos la solución:

Ejercicio Utilizando la función O intenta distinguir a las personas que posean alguna de las categorías indicadas en el rango H48:H50. Inserta la función en la celda F48 y cópiala hasta la celda F55.

Page 24: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 97

Ejercicios Propuestos

1. Habrá una nuevo libro en el Excel 2010 y realiza lo siguiente:

2. Introduce en la columna A1 hasta a A10, una serie de números negativos y positivos y crea en la columna B1 hasta B10 una función que muestre “Verdadero” o “falso” según el número sea positivo o negativo respectivamente.

3. Añade al ejercicio anterior una columna C1 hasta C10 tal que si el número es positivo muestre la

palabra “POSITIVO”, pero si es negativo muestre “NEGATIVO”.

FUNCION NO La función NO evalúa un valor lógico (VERDADERO o FALSO) y devuelve un valor lógico opuesto. Veamos el siguiente ejemplo para entenderla mejor.

Sintaxis =NO(valor lógico)

valor lógico: es una comparación de dos valores. Este argumento puede ser también una función que devuelva valores lógicos.

Ejemplo: En este ejemplo se tiene un listado de marcas y modelos de automóviles en el rango D18:E25. En el rango F18:F25 se desea identificar aquellos cuyo modelo no sea menor a lo indicado en la celda H18. A continuación se presenta la solución.

Ejercicio Utilizando la función NO intenta distinguir los automóviles del rango D48:D55 cuya marca no sea la indicada en la celda H48. Inserta la función en la celda F48 y cópiala hasta la celda F55.

Page 25: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 98

FUNCION SI.ERROR

La función SI.ERROR devuelve un valor determinado en caso que una fórmula o celda contenga un error. Sintaxis

=SI.ERROR(valor,valor_si_error) Valor: puede ser una referencia a otra celda, o una fórmula. Valor si error: valor a mostrar en caso que el argumento valor devuelva un error.

Ejemplo: En el rango D5:F9 se tienen datos de ventas y gastos por zona. En el rango F5:F9 se desea obtener el porcentaje de gastos en relación a las ventas para cada zona. A continuación se presenta la solución.

Ejercicio Utilizando la función SI.ERROR calcula el % de margen de cada zona. El margen debe calcularse como (Ventas - Gastos) / Ventas.

13.2 Funciones de búsqueda y referencia Las funciones de búsqueda y referencia son aquellas funciones que a partir de unos argumentos nos ayudan a localizar valores o datos dentro de rangos Excel. En múltiples ocasiones disponemos de tablas con datos e informaciones (listas de precios, tablas de salarios, de impuestos, de rappels, grandes relaciones de elementos a modo de bases de datos, en las que buscar ciertos valores, mediante procedimientos que van desde los más sencillos y directos hasta otras más "rebuscados" e indirectos que nos pueden dar solución a necesidades concretas y de más difícil cálculo de no conocer estas posibilidades.

Page 26: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 99

Función CONSULTAV Esta función nos permite buscar un valor en una primera columna de una matriz, una vez localizado nos muestra dentro de la misma fila el valor que contiene la columna que deseamos obtener.

Sintaxis CONSULTAV(valor_buscado,matriz_buscar_en,indicador_columnas,ordenado

)

� Valor_buscado: es el valor buscado en la primera columna de la tabla. � Matriz_buscar_en: es la tabla donde se efectúa la búsqueda. � Indicador_columnas: es un número que especifica la columna de la tabla de donde se devuelve

un valor. � Ordenado: es un valor lógico (VERDADERO o FALSO) que indica que la primera columna de la

tabla donde se buscan los datos esta ordenada o no. Si omite este argumento se considera VERDADERO, es decir, se considera que la columna uno de la tabla esta ordenada. Si no se está seguro poner siempre FALSO.

Excel busca en la primera columna de la matriz, definida en el segundo argumento, de forma vertical el valor que ponemos en el primer argumento. Normalmente esta búsqueda Excel la hace pensando que esta primera columna está ordenada. Si los valores no lo estuvieran tenemos que indicárselo para que pueda encontrar el dato. Si la tabla no está ordenada deberemos escribir Falso en el argumento que hemos llamado Ordenado.

Ejemplo: Vamos a crear una pequeña hoja en la que según el código de un artículo nos devuelva la descripción de este dependiendo de una lista. Para ello primero de todo necesitaremos una tabla de valores. Imaginemos que la introducimos a partir de la celda A5 donde escribiremos el primer código, por ejemplo X-1. En la celda B5 la descripción: Coches. En la celda A6 escribiremos X-2 y en la B6: Camiones... y así todos los valores que querramos. Lo que desearemos es que el usuario de esta hoja introduce un código en la celda B1 y automáticamente en la celda B2 aparezca la descripción que depende del código. Para ello solamente tendremos que escribir la función siguiente en la celda B2. =CONSULTAV(B1,A5:B8,2,FALSO) Explicaremos detenidamente los argumentos de esta función para terminar de entender el funcionamiento. En esta función buscamos el valor de la celda B1, dentro de la matriz A5:B8, recuerda que Excel busca primero por la primera columna. Como resultado nos mostrará lo que encuentre en la segunda columna de la fila del valor encontrado.

Ejemplo de función =CONSULTAV

Page 27: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 100

En la casilla B9 se ha utilizado la función CONSULTAV para encontrar el nombre del producto que tiene código 2. BUSCA el valor de casilla B8 en la primera columna del Rango A2:C5 y muestra el valor correspondiente de la segunda columna de este Rango. Cambia el valor del código en la casilla B8 para ver como automáticamente cambia el nombre del producto y el precio.

Función CONSULTAH Esta función realiza lo mismo que la función anterior, pero con la diferencia que busca los valores en la primera fila de la matriz de forma horizontal y nos devuelve un valor que está dentro de la misma columna del valor encontrado.

Estructura: CONSULTAH(valor_buscado,matriz_buscar_en,indicador_filas, ordenado)

� Valor buscado: es el valor buscado en la primera fila de la tabla. � Matriz buscar en: es la tabla donde se efectúa la búsqueda. � Indicador filas: es un número que especifica la fila de la tabla donde se buscará valor. � Ordenado: es un valor lógico (VERDADERO o FALSO) que indica que la primera fila de la tabla

donde se buscan los datos esta ordenada o no. Si se omite este argumento se considera que la fila uno de la tabla esta ordenada (VERDADERO).

Ejemplo: Podemos utilizar el mismo caso que el anterior, simplemente que los códigos que vamos a utilizar deberán estar distribuidos en columnas y las descripciones en la siguiente fila. Así de esta forma podemos utilizar la siguiente función para hallar el Producto: =CONSULTAH(B7,B2:E4,2,FALSO)

Función COINCIDIR La función COINCIDIR busca un elemento especificado en un rango de celdas y, a continuación, devuelve la posición relativa de ese elemento en el rango. Por ejemplo, si el rango B2:B4 contiene los valores 5, 25 y 38, la fórmula =COINCIDIR(25,B2:B4,0) devuelve el número 2, porque 25 es el segundo elemento en el rango.

Use COINCIDIR en lugar de una de las funciones BUSCAR cuando necesite conocer la posición de un elemento en un rango en lugar del elemento en sí. Por ejemplo, puede usar la función COINCIDIR para proporcionar un valor para el argumento fila de la función INDICE.

Sintaxis

Page 28: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 101

COINCIDIR(valor_buscado; matriz_buscada; tipo_de_coincidencia]) La sintaxis de la función COINCIDIR tiene los siguientes argumentos:

� valor_buscado Obligatorio. Valor que desea buscar en matriz_buscada. Por ejemplo, cuando busca un número en la guía telefónica, usa el nombre de la persona como valor de búsqueda, pero el valor que desea es el número de teléfono. El argumento valor_buscado puede ser un valor (número, texto o valor lógico) o una referencia de celda a un número, texto o valor lógico.

� matriz_buscada Obligatorio. Rango de celdas en el que se realiza la búsqueda. � tipo_de_coincidencia Opcional. Puede ser el número -1, 0 o 1. El argumento

tipo_de_coincidencia específica cómo Excel hace coincidir el valor_buscado con los valores de matriz_buscada. El valor predeterminado de este argumento es 1. En la siguiente tabla se describe cómo la función busca valores según la configuración del argumento tipo_de_coincidencia.

Tipo_de_coincidencia Comportamiento

1 u omitido COINCIDIR encuentra el mayor valor que es menor o igual que el valor_buscado. Los valores del argumento matriz_buscada se deben colocar en orden ascendente, por ejemplo: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO.

0 COINCIDIR encuentra el primer valor que es exactamente igual que el valor_buscado. Los valores del argumento matriz_buscada pueden estar en cualquier orden.

-1 COINCIDIR encuentra el menor valor que es mayor o igual que el valor_buscado. Los valores del argumento matriz_buscada se deben colocar en orden descendente, por ejemplo: VERDADERO, FALSO, Z-A, ...2, 1, 0, -1, -2, ..., etc.

Notas

� COINCIDIR devuelve la posición del valor coincidente dentro de la matriz_buscada, no el valor en sí. Por ejemplo, COINCIDIR("b";{"a";"b";"c"};0) devuelve 2, la posición relativa de "b" dentro de la matriz {"a";"b";"c"}.

� COINCIDIR no distingue entre mayúsculas y minúsculas cuando busca valores de texto. � Si COINCIDIR no puede encontrar una coincidencia, devuelve el valor de error #N/A. � Si el tipo_de_coincidencia es 0 y el valor_buscado es una cadena de texto, puede usar los

caracteres comodín: el signo de interrogación (?) y el asterisco (*), en el argumento valor_buscado. Un signo de interrogación coincide con cualquier carácter individual; un asterisco coincide con cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco real, escriba una tilde (~) antes del carácter.

Ejemplo

Función ELEGIR Con esta función escogeremos un valor de una lista de valores dependiendo de un índice.

Page 29: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 102

Sintaxis ELEGIR(núm_índice,valor1,valor2,...)

Núm_índice especifica el argumento de valor que se selecciona. El argumento núm_índice debe ser un número entre 1 y 254, o bien, una fórmula o referencia a una celda que contenga un número entre 1 y 254. Ejemplo: En la celda A2 escribiremos una fecha. En la celda B2 utilizaremos la función DIASEM la cual nos devolverá la posición del día de la semana que ocupa la fecha de la celda A2. En la celda C2 queremos que aparezca el día de la semana con su nombre según el número que aparezca en la celda B2. Para ello simplemente escribiremos la siguiente fórmula: =ELEGIR(B2,"Lunes","Martes","Miercoles","Jueves","Viernes","Sábado","Domingo") Observa que el valor de la celda B2 nos sirve como índice y según el valor que obtenemos la función nos muestra el primer valor, el segundo...

Ejemplo de función =ELEGIR

En la casilla B8 se mostrará el valor Manzanas. Escoger el elemento que ocupa la posición indicada en B7 de la lista B2,B3,B4,B5.

Función INDICE Muestra el elemento del Rango o Nombre del Rango situado en la posición fila, columna de la tabla. =INDICE(Rango o Nombre del Rango,Fila,Columna) Ejemplo de función =INDICE

La función INDICE de la casilla B10, localiza el precio a aplicar según los valores de B8(fila) y B9(Columna). Previamente al rango B3:D5 se le ha dado el nombre Precios. El precio localizado será 52. Localiza en la tabla Precios(B3:D5) el elemento que está en la fila indicada por el valor de B8 y columna indicada por el valor de B9.

Page 30: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 103

Tema Nro. 14

Datos

14.1 Validación de Datos en Celdas Una sección anterior se encargó de controlar los diversos errores que pueden surgir al usar funciones BUSCAR. Otra estrategia para evitar esos mensajes de error sería que se controle el ingreso de datos a buscar, por ejemplo que se ingrese sólo códigos que existan en su tabla, o se limite el ingreso a números en un determinado intervalo, etc.

14.1.1 Validación por Listas Por ejemplo, se pide que en una celda D5 se limite el ingreso de datos a códigos que existan en nuestra tabla de empleados. Para realizar esta acción, debe seguir el procedimiento en el libro Boleta de Pago:

Seleccione el rango de los códigos de la Hoja1 (C6:C15) y asígnele un nombre de rango. Para nuestro ejemplo, se llamará CODIGOS

Page 31: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 104

Luego, lleve el indicador a la celda D5 de la Hoja3

Elija la ficha Datos, en el grupo Herramientas de datos haga clic en Validación de datos�

Elija de la lista Validación de datos…

En este cuadro:

Page 32: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 105

En la lista Permitir: elija LISTA En Origen: escriba el signo IGUAL (=) y el nombre del rango definido anteriormente. En nuestro caso escriba =CODIGOS Haga clic en Aceptar Ahora, en la celda D5 se muestra una lista para seleccionar el código a ubicar

14.1.2 Quitar una Validación Si usted desea que su celda ya no tenga opciones de validación, sólo debe: Seleccione la celda Elija la ficha Datos, en el grupo Herramientas de datos haga clic en Validación de datos… En el cuadro Permitir elija Cualquier valor Haga clic en Aceptar

Validación por Intervalo de Números Otra forma de validar el ingreso de datos es a través de controlar que sólo se ingresen valores entre un rango de números. Por ejemplo, en la Hoja4, los valores permitidos para la celda D9 van de 1998 a 2003. Realice los siguientes pasos para aplicar este tipo de validación: Lleve el indicador a la celda D9 de la Hoja4 Elija la ficha Datos, en el grupo Herramientas de datos haga clic en Validación de datos… En la lista Permitir: elija Número entero En Datos: elija entre Como valor Mínimo: escriba 1998. Como valor Máximo: escriba 2003

Haga clic en Aceptar Para probar esta opción, escriba un valor fuera del intervalo definido. Por ejemplo, escriba 2005, para ver los valores

Page 33: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 106

escritos no válidos haga lo siguiente: En la ficha Datos, en el grupo Herramientas de datos, haga clic en Validación de datos y elija: Rodear con un círculo datos no válidos

Se muestra:

Mensajes de Ingreso y de Error

Incluso, puede definir un mensaje de entrada (mensaje que se muestra al llevar el indicador sobre la celda validada) o un mensaje de error (mensaje personalizado que se muestra al ingresar un valor fuera del rango definido). Para definir un mensaje entrante: Lleve el indicador a la celda a definir. En nuestro caso a la celda D9 Elija la ficha Datos, grupo Herramientas de datos haga clic en Validación de datos… En la ficha Mensaje entrante, escriba como Título: Consulta de Cotizaciones En el Mensaje escriba: Escriba el año a consultar.

Haga clic en Aceptar Si lleva el indicador a la celda D9 observe el mensaje:

Para definir un mensaje de error: Lleve el indicador a la celda a definir. En nuestro caso a la celda D9 Haga clic en Validación de datos…

Page 34: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 107

En la ficha Mensaje de error, escriba como Título: Consulta de Cotizaciones En el Mensaje escriba: Error en el ingreso de datos. Valor no permitido. Haga clic en la ficha Aceptar.

En la celda D9 escriba un valor fuera del intervalo. Por ejemplo escriba 2010.

14.2 Formato Condicional Para resaltar los resultados de una fórmula u otros valores que desee supervisar, pueden identificarse las celdas aplicando formatos condicionales. El formato solo se aplica a las celdas que cumplan un criterio establecido. Por ejemplo, Microsoft Excel puede aplicar un sombreado de color verde a las celdas si las ventas reales sobrepasan a las ventas pronosticadas y un sombreado de color rojo si las ventas reales son menores a las ventas pronosticadas. Existen cinco modalidades de aplicación de Formato condicional. Resaltar reglas de celdas. Reglas superiores e inferiores. Barras de datos. Escalas de color. Conjuntos de íconos.

14.2.1 Resaltar Reglas de Celdas Permite asignar un formato de color de texto, relleno y otros a las celdas que cumplan con ciertas características tales como:

Opción Función

Cuando el valor de las celdas seleccionadas es mayor al que será especificado en esta opción.

Cuando el valor de las celdas seleccionadas es menor al que será especificado en esta opción.

Page 35: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 108

Cuando el valor de las celdas seleccionadas se encuentra entre un rango especificado.

Cuando el valor de las celdas seleccionadas es igual al que será especificado en esta opción.

Cuando el valor de las celdas seleccionadas contiene todo o parte del texto que será especificado en esta opción.

Cuando el valor de las celdas seleccionadas contiene una referencia a una fecha especificada en esta opción.

Cuando el valor de las celdas seleccionadas se encuentra duplicado o de forma única en un rango.

1) Seleccione las celdas a la que desea aplicar el formato. En este ejemplo el rango B2:B6. 2) Elija la opción Formato condicional del grupo Estilos, en la ficha Inicio de la Cinta de opciones.

En el menú que se muestra a continuación podrá elegir entre una lista de posibles casos a evaluar. En este ejercicio elegiremos la opción Resaltar reglas de celdas / Es menor que…

3) Se mostrará la ventana Es menor que, en el que podrá especificar en el cuadro de texto el valor a

evaluar (en este ejemplo escribiremos el número 3) y en la lista desplegable del lado derecho podrá seleccionar con formato quiere que se muestren las celdas seleccionadas, si el formato que necesita no se muestra en la lista predeterminada elija la opción Formato personalizado… tras lo cual se mostrará el cuadro de diálogo Formato de celdas para realizar las especificaciones necesarias (En este caso elegiremos el color de fuente Rojo y Amarillo como color de relleno).

4) Haga clic en Aceptar. 5) En la siguiente imagen, observe que todas las celdas que cumplen con la condición se muestra de

una manera diferente.

Page 36: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 109

Reglas Superiores e inferiores

Permite aplicar un formato en especial al rango de celdas que cumplan con las condiciones establecidas por el ranking de las celdas seleccionadas.

Opción Función

Permite aplicar un formato en especial a las tantas primeras celdas (debe especificar) cuyos valores sean los mayores del rango.

Permite aplicar un formato en especial a las tantas primeras celdas por ciento (debe especificar) cuyos valores sean los mayores del rango.

Permite aplicar un formato en especial a las tantas primeras celdas (debe especificar) cuyos valores sean los menores del rango.

Permite aplicar un formato en especial a las tantas primeras celdas por ciento (debe especificar) cuyos valores sean los menores del rango.

Aplicar formato a las celdas que están por encima del promedio del rango seleccionado.

Aplicar formato a las celdas que están por debajo del promedio del rango seleccionado.

Barras de Datos Permite asignar de forma automática unas barras coloreadas en un rango de celdas. La longitud de la barra representa el valor de la celda. Una barra más larga representa un valor mayor.

Opción Función

Relleno degradado.

Relleno sólido.

Escala de Colores

Page 37: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 110

Borrar Regla

Si desea eliminar cualquier asignación de formato condicional asignada a una celda o grupo de celdas, solo debe aplicar la opción Borrar regla ubicada en la ficha Inicio / grupo Estilos / opción Formato condicional / Borrar reglas. Ejercicio Propuesto 1) Diseñar lo siguiente en una hoja de Excel:

Page 38: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 111

2) Cambiar nombre de la Hoja 1 por Práctica Calificada 01, y luego cambiar el color de la etiqueta a rojo. 3) Eliminar la Hoja 2 y 3 del libro. 4) Guardar el libro en Alumno con el nombre "Apellidos del Alumno" y establecer una contraseña de apertura

con la palabra conti. 5) Ocultar la columna L hasta la XFD 6) Aplicar formato condicional para las notas:

� 0 - 10.5 (Fuente color Rojo) � 10.5 - 20.0 (Fuente color Azul)

7) Desbloquear las celdas D15:K24

Proteger hoja con la contraseña conti.

14.3 Filtros avanzados Un filtro nos permite ver solamente las filas que cumplen un determinado criterio de selección, ocultando las que no lo cumplen. Cuando aplicamos un criterio sencillo que utiliza pocas condiciones utilizamos el ya estudiado Autofiltro. Si necesitamos utilizar criterios de selección más complejos necesitaremos utilizar un Filtro avanzado. Encontramos el Filtro avanzado en el menú Datos - Filtro - Filtro avanzado...

Page 39: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 112

De igual manera que hacíamos usando un Autofiltro, cuando usamos un Filtro avanzado la lista o conjunto de datos que deseamos filtrar debe tener título o encabezado en cada una de las columnas. El rango de criterios debe escribir en otro lugar de la hoja utilizando un encabezado para cada uno de los campos que vamos a incluir en el filtro. Los criterios se deben especificar usando exactamente los mismos nombres que tienen las columnas que contiene la lista a filtrar. Operador lógico Y (AND). Es necesario que el registro cumpla todas las condiciones unidas por este operador para que aparezca. Las condiciones unidas mediante Y se escriben en la misma fila. Operador lógico O (OR). Es suficiente con que se cumpla una de las condiciones. Las condiciones escritas mediante un O se escribe en la misma columna.

A continuación se muestra una lista de productos:

Ubíquese en la Hoja 3, se muestra un ejemplo de un rango de criterios:

Page 40: Informatica Basica Excel

Hoja Electrónica de Cálculo Pág. 113

Actividad 01: Se necesita tener una relación de los productos de las categorías: Lácteos, Pescados / Mariscos, y de Carnes. No se pueden usar autofiltros ya que se necesitan más de tres criterios en el mismo campo. Actividad 02: Se necesita obtener los productos de la categoría Carnes cuyo stock sea mayor de 80 unidades y los lácteos con stock menor a 30 unidades. Actividad 03: Se desea mostrar los registros cuya longitud del nombre sea de hasta 15 caracteres. En este caso, la fórmula empleada será: =LARGO(B4)<15 Actividad 04: Se desea mostrar los productos cuyo Stock esté por debajo del promedio. En este caso, la fórmula empleada será: =F4<PROMEDIO(F4:F80)

14.4 Subtotales Con Subtotales de Excel podemos calcular el valor de un subtotal o un total general en una lista que previamente tengamos ordenada. Excel calculará el subtotal utilizando una función resumen (Suma, Contar, Promedio, etc.). Seleccionamos una celda de lista con la que vamos a trabajar y desde el menú Datos - Subtotales.

Cada vez que se realiza un cambio en la columna indicada, aplica la función resumen elegida y agrega el subtotal al valor o valores que nosotros marquemos.

Page 41: Informatica Basica Excel

Informática I

Hoja Electrónica de Cálculo Pág. 114

Page 42: Informatica Basica Excel

Informática I

Tema Nro. 15

Agrupar y esquematizar Datos

15.1 Agrupar Y Esquematizar Datos Esta funcionalidad se emplea si tiene una lista de datos que desea agrupar y resumir; puede crear un esquema de hasta ocho niveles, uno para cada grupo. Cada nivel interno, representado por un número superior de símbolos de esquema (símbolos de esquema: símbolos que se utilizan para cambiar la vista de una hoja de cálculo esquematizada. Se pueden mostrar u ocultar los datos detallados presionando en el signo más, en el signo menos y en los números 1, 2, 3 o 4 que indican el nivel de esquema.), muestra datos de detalle (datos de detalle: para subtotales automáticos y esquemas de hoja de cálculo, filas o columnas de subtotal que totalizan los datos de resumen.

Se muestra una fila de datos de ventas agrupados por regiones geográficas y meses con varias filas de resumen y detalles.

Para mostrar filas para un nivel, haga clic en los símbolos de esquema adecuados.

El nivel 1 contiene las ventas totales de todas las filas de detalle.

El nivel 2 contiene las ventas totales para cada mes en cada región.

El nivel 3 contiene las filas de detalle (sólo las filas de detalle 11 a 13 están actualmente visibles).

Para expandir o contraer datos en el esquema, haga clic en los símbolos de esquema y .

1.1. CREAR UN ESQUEMA DE FILAS 1. Asegúrese de que cada columna de los datos a los que desea aplicar un esquema tiene un rótulo en la

primera fila, contiene datos similares en cada columna y el rango no tiene filas ni columnas en blanco. 2. Seleccione una celda del rango. 3. Para ordenar la columna que contiene los datos que desea agrupar, seleccione dicha columna y, en la

ficha Datos, en el grupo Ordenar y filtrar, haga clic en Ordenar de A a Z o en Ordenar de Z a A. 4. Inserte las filas de resumen.

Para trazar esquemas de los datos por filas, debe tener filas de resumen que contengan fórmulas que hagan referencias a las celdas de cada una de las filas de detalle para dicho grupo. Siga uno de los procedimientos siguientes: a. Insertar filas de resumen utilizando el comando Subtotal

Use el comando Subtotal, que inserta la función SUBTOTAL inmediatamente debajo o encima de cada grupo de filas de detalle y crea automáticamente el esquema.

b. Insertar sus propias filas de resumen

Inserte sus propias filas de resumen con las fórmulas inmediatamente anterior o posterior a cada grupo de filas de detalle.

5. Especifique si la ubicación de la fila de resumen está encima o debajo de las filas de detalle. a. En el grupo Esquema de la ficha Datos, haga clic en Esquema. b. Para especificar una fila de resumen encima de la fila de detalles, desactive la casilla de

verificación Filas resumen debajo del detalle. Para especificar una fila de resumen debajo de la fila de detalles, active la casilla de verificación Filas resumen debajo del detalle.

6. Aplique un esquema a los datos. Siga uno de los procedimientos siguientes: a. Aplicar un esquema a los datos automáticamente

1. En caso necesario, seleccione una celda del rango. 2. En el grupo Esquema de la ficha Datos, haga clic en la flecha al lado de Agrupar y, a

continuación, haga clic en Autoesquema. b. Aplicar un esquema a los datos manualmente

Page 43: Informatica Basica Excel

Informática I

Procedimientos de diagnóstico y Solución de Problemas Pág. 116

Importante: Cuando agrupe manualmente niveles de esquema, es mejor tener todos los datos mostrados para evitar agrupar las filas incorrectamente. Aplique un esquema al grupo externo. 1. Seleccione todas las filas de resumen subordinadas, así como sus datos de

detalle relacionados.

En el ejemplo siguiente, la fila 6 contiene los subtotales para las filas 2 a 5, y la fila 10 contiene los subtotales para las filas 7 a 9, y la fila 11 contiene los totales generales. Para agrupar todos los datos de detalle para la fila 11, seleccione las filas 2 a 10. Importante No incluya la fila de resumen 11 de la selección.

2. En el grupo Esquema de la ficha Datos, haga clic en Agrupar.

Los símbolos del esquema aparecen en la pantalla al lado del grupo. De manera opcional, aplique un esquema a un grupo interno anidado. 1. Para grupo interno anidado, seleccione las filas de detalle adyacentes a la fila

que contiene la fila de resumen.

En el ejemplo siguiente, para agrupar las filas 2 a 5, que tienen una fila resumen 6, seleccione las filas 2 a 5. Para agrupar las filas 7 a 9, que tiene una fila resumen 10, seleccione las filas 7 a 9. Importante No incluya en la selección la fila resumen para dicho grupo.

2. En el grupo Esquema de la ficha Datos, haga clic en Agrupar.

Los símbolos del esquema aparecen en la pantalla al lado del grupo. 1. Siga seleccionando y agrupando filas internas hasta que haya creado todos los niveles

que desee en el esquema. 2. Si desea desagrupar filas, selecciónelas y, a continuación, en el grupo Esquema de la pestaña Datos, haga clic

en Desagrupar.

Nota También puede desagrupar secciones del esquema sin quitarlo todo. Mantenga presionada la tecla Mayús mientras hace clic en o en del grupo y, a continuación, en el grupo Esquema de la pestaña Datos, haga clic en Desagrupar.

15.1.1 CREAR UN ESQUEMA DE COLUMNAS 1. Asegúrese de que cada fila de los datos a los que desea aplicar un esquema tiene un rótulo en la primera

fila, contiene datos similares en cada columna y el rango no tiene filas ni columnas en blanco. 2. Seleccione una celda del rango. 3. Ordene las filas que forman los grupos. 4. Inserte sus propias columnas resumidas con fórmulas inmediatamente a la derecha o a la izquierda de

cada grupo de columnas de detalle. 5. Especifique si la ubicación de la columna de resumen está a la derecha o a la izquierda de las columnas

de detalle. a. En el grupo Esquema de la ficha Datos, haga clic en Esquema. b. Para especificar una columna de resumen a la izquierda de la columna de

detalles, desactive la casilla de verificación Columnas resumen a la derecha del detalle. Para especificar una columna de resumen a la derecha de la columna de detalles, active la casilla de verificación Columnas resumen a la derecha del detalle.

c. Haga clic en Aceptar. 6. Para aplicar un esquema a los datos, siga uno de estos procedimientos:

Mostrar u ocultar datos de esquemas 1. Si no ve los símbolos de esquema , y , haga clic en Archivo, seleccione Opciones, haga clic

en la categoría Avanzadas y, a continuación, en Mostrar opciones para esta hoja, seleccione la hoja de cálculo y active la casilla de verificación Mostrar símbolos de esquema si se aplica un esquema.

A B C

1 Región Mes Ventas

2 Este Marzo 9.647 $

3 Este Marzo 4.101 $

4 Este Marzo 7.115 $

5 Este Marzo 2.957 $

6 Este Total de marzo 23.820 $

7 Este Abril 4.257 $

8 Este Abril 1.829 $

9 Este Abril 6.550 $

10 Este Total de abril 12.636 $

11 Total del este 36.456 $

Page 44: Informatica Basica Excel

Procedimientos de diagnóstico y Solución de Problemas Pág. 117

2. Siga uno o varios de los procedimientos siguientes:

Mostrar u ocultar los datos de detalle de un grupo � Para mostrar los datos de detalle de un grupo, haga clic en del grupo. � Para ocultar los datos de detalle de un grupo, haga clic en del grupo.

Expandir o contraer todo el esquema hasta un nivel determinado � En los símbolos de esquema , haga clic en el número del nivel que desee. Los datos de

detalle de los niveles inferiores se ocultan. � Por ejemplo, si un esquema tiene cuatro niveles, puede ocultar el cuarto y mostrar los restantes

haciendo clic en .

Mostrar u ocultar todos los datos de detalle del esquema � Para mostrar todos los datos de detalle, haga clic en el nivel más bajo de los símbolos de esquema

. Por ejemplo, si existen tres niveles, haga clic en . � Para ocultar todos los datos de detalle, haga clic en .

15.1.2 PERSONALIZAR UN ESQUEMA CON ESTILOS En el caso de las filas del esquema, y las columnas del esquema se pueden emplear estilos. Estos estilos emplean la negrita, la cursiva y otros formatos de texto para diferenciar las filas o columnas de resumen en los datos. Si se cambia la forma en que están definidos estos estilos, se pueden aplicar distintos formatos de texto y celda para personalizar el aspecto del esquema. Puede aplicar un estilo a un esquema al crear el esquema o después de crearlo. Siga uno o varios de los procedimientos siguientes: Aplicar un estilo automáticamente a una columna o fila resume

1. En el grupo Esquema de la ficha Datos, haga clic en Esquema. 2. Active la casilla de verificación Estilos automáticos.

Aplicar un estilo a una columna o fila resume existente 1. Seleccione las celdas a las que desee aplicar estilos de esquema. 2. En el grupo Esquema de la ficha Datos, haga clic en Esquema. 3. Active la casilla de verificación Estilos automáticos. 4. Haga clic en Aplicar estilos.

15.1.3 COPIAR DATOS ESQUEMATIZADOS 1. Si no ve los símbolos de esquema , y , haga clic en Archivo, seleccione Opciones, haga clic

en la categoría Avanzadas y, a continuación, en Mostrar opciones para esta hoja, seleccione la hoja de cálculo y active la casilla de verificación Mostrar símbolos de esquema si se aplica un esquema.

2. Utilice los símbolos de esquema , y para ocultar los datos de detalle que no desee copiar. 3. Seleccione el rango de filas de resumen. 4. En el grupo Modificar la ficha Inicio, haga clic en Buscar y seleccionar y, a continuación,

en Ir a. 5. Haga clic en Ir a Especial. 6. Haga clic en Sólo celdas visibles. 7. Haga clic en Aceptar y, a continuación, copie los datos.

15.1.4 OCULTAR O QUITAR UN ESQUEMA Nota No se eliminan los datos al ocultar o quitar un esquema. Ocultar un esquema

1. Si no ve los símbolos de esquema , y , haga clic en Archivo, seleccione Opciones, haga clic en la categoría Avanzadas y, a continuación, en Mostrar opciones para esta hoja, seleccione la hoja de cálculo y active la casilla de verificación Mostrar símbolos de esquema si se aplica un esquema.

2. Vea todos los datos haciendo clic en el número más alto de los símbolos de esquema . 3. Haga clic en Archivo, seleccione Opciones, haga clic en la categoría Avanzadas y, a continuación, en la

sección Mostrar opciones para esta hoja, seleccione la hoja de cálculo y desactive la casilla de verificación Mostrar símbolos de esquema si se aplica un esquema.

Page 45: Informatica Basica Excel

Informática I

Procedimientos de diagnóstico y Solución de Problemas Pág. 118

Quitar un esquema 1. Haga clic en la hoja de cálculo. 2. En el grupo Esquema de la ficha Datos, haga clic en la flecha situada junto a Desagrupar y,

a continuación, en Borrar esquema. 3. Si las filas o columnas siguen ocultas, arrastre los encabezados de fila o columna visibles a

ambos lados de las filas y columnas ocultas, elija Ocultar y mostrar en el comando Formato (que se encuentra en el grupo Celdas de la pestaña Inicio) y, a continuación, haga clic en Mostrar filas o en Mostrar columnas.

15.2 CREAR UN INFORME DE RESUMEN CON UN GRÁFICO

Supongamos que desea crear un informe resumido de los datos que solo muestra los totales acompañados de un gráfico de dichos totales. En general, puede hacer lo siguiente: Crear un informe de resumen

1. Aplicar un esquema a los datos. 2. Oculte el detalle haciendo clic en los símbolos de esquema ,

y para mostrar sólo los totales como se muestra en el siguiente ejemplo de un esquema de fila:

Representar en un gráfico el informe de resumen 1. Seleccione los datos de resumen que desea representar.

Por ejemplo, para representar sólo los totales de Buchanan y Davolio, pero no los totales generales, seleccione las celdas A1 a C11, como se muestra en el ejemplo anterior.

2. Cree el gráfico.

Por ejemplo, si crea el gráfico con el Asistente para gráficos, tendrá un aspecto similar al del siguiente ejemplo.

15.3 Tablas Dinámicas Los informes de tabla dinámica son útiles para resumir, analizar, explorar y presentar datos de resumen. Tanto los informes de tabla dinámica como los informes de gráfico dinámico permiten tomar decisiones bien fundamentadas sobre datos críticos para su empresa.

15.3.1 ¿Qué es un informe de tabla dinámica?

Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Use un informe de tabla dinámica para analizar detenidamente datos numéricos y responder a preguntas no esperadas sobre los datos. Un informe de tabla dinámica está especialmente diseñado para:

� Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas para el usuario. � Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos

y fórmulas personalizados. � Expandir y contraer los niveles de datos para destacar los resultados y ver los detalles de los datos de

resumen de las áreas de interés. � Mover filas a columnas y columnas a filas para ver diferentes resúmenes de los datos de origen. � Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para

poder concentrarse en la información que le interesa. � Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.

En el siguiente ejemplo de informe de tabla dinámica, puede ver fácilmente cómo se comparan las ventas totales del departamento de golf del tercer trimestre en la celda F3 con las ventas de otro deporte, o trimestre, o con las ventas totales de todos los departamentos.

Page 46: Informatica Basica Excel

Procedimientos de diagnóstico y Solución de Problemas Pág. 119

Datos de origen; en este caso, de una hoja de cálculo

Valores de origen del resumen del Trim3 de Golf en el informe de tabla dinámica

Informe de tabla dinámica completo

Resumen de los valores de origen en C2 y C8 desde los datos de origen

En un informe de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica que resume varias filas de información. En el ejemplo anterior, la columna Deporte se convierte en el campo Deporte y cada registro (una colección de información sobre un campo) de Golf se resume en un solo elemento Golf. Un campo de valor, como Suma de ventas, contiene los valores que van a resumirse. En el informe anterior, el resumen Golf Trim3 contiene la suma del valor de ventas de cada fila de los datos de origen para la que la columna Deporte contiene Golf y la columna Trimestre contiene Trim3. De forma predeterminada, los datos en el área de valores resumen los datos de origen subyacentes en el informe de gráfico dinámico de la forma siguiente: los valores numéricos usan la función SUMA para sumar valores y los valores de texto usan la función CONTAR para contar el número de valores.

Para crear un informe de tabla dinámica, debe definir el origen de datos, especificar una ubicación en el libro y organizar los campos.

15.3.2 ¿Qué es un informe de gráfico dinámico?

Un informe de gráfico dinámico proporciona una representación gráfica de los datos de un informe de tabla dinámica, que en este caso se denomina informe de tabla dinámica asociado. Un informe de gráfico dinámico es interactivo, lo que significa que se puede ordenar y filtrar para mostrar subconjuntos de los datos de la tabla dinámica. Cuando se crea un informe de gráfico dinámico, se muestran filtros de informe de gráfico dinámico en el área del gráfico para poder ordenar y filtrar los datos subyacentes del informe de gráfico dinámico. Los cambios que realice en el diseño de campo y los datos en el informe de tabla dinámica asociado se reflejarán inmediatamente en el informe de gráfico dinámico.

Un informe de gráfico dinámico muestra series de datos, categorías, marcadores de datos y ejes al igual que los gráficos estándar. Puede cambiar asimismo el tipo de gráfico y otras opciones como los títulos, la ubicación de la leyenda, las etiquetas de datos y la ubicación del gráfico.

Puede crear un informe de gráfico dinámico automáticamente al crear un informe de tabla dinámica, o bien a partir de un informe de tabla dinámica ya existente.

15.3.3 Crear una tabla dinámica a partir de datos de una hoja de cálculo

Al crear un informe de tabla dinámica a partir de datos de una hoja de cálculo, los datos se convierten en los datos de origen del informe de tabla dinámica.

1. Siga uno de los procedimientos siguientes:

� Para usar datos de una hoja de cálculo como origen de datos, haga clic en una celda del rango de celdas que contienen los datos.

� Para usar datos de una tabla de Excel como origen de datos, haga clic en una celda dentro de la tabla de Excel.

Page 47: Informatica Basica Excel

Informática I

Procedimientos de diagnóstico y Solución de Problemas Pág. 120

Nota Asegúrese de que el rango tiene encabezados de columna o que se muestran los encabezados en la tabla y que no hay ninguna fila en blanco en el rango o en la tabla.

2. En la pestaña Insertar, en el grupo Tablas, haga clic en Tabla dinámica, o bien haga clic en la flecha que se encuentra debajo de Tabla dinámica y, a continuación, en Tabla dinámica.

Excel mostrará el cuadro de diálogo Crear tabla dinámica.

Sugerencia Para crear un informe de tabla dinámica y gráfico dinámico a la vez, en la pestaña Insertar, en el grupo Tablas, haga clic en la flecha situada bajo Tabla dinámica y, a continuación, haga clic en Gráfico dinámico. Excel muestra el cuadro de diálogo Crear tabla dinámica con el gráfico dinámico.

3. En Elija los datos que desea analizar, asegúrese de activar la opción Seleccione una tabla o un rango y, a continuación, en el cuadro Tabla o rango, compruebe que el rango de celdas que desea usar como datos subyacentes sea el rango correcto.

Excel determina automáticamente el rango para el informe de tabla dinámica, pero puede reemplazarlo escribiendo otro rango o el nombre que haya definido para dicho rango.

Si los datos se encuentran en otra hoja de cálculo o en otro libro, incluya el nombre del libro y de la hoja de cálculo usando la sintaxis siguiente: [nombrelibro]nombrehoja!rango.

Sugerencia También puede hacer clic en Contraer diálogo para ocultar temporalmente el cuadro de

diálogo, seleccionar el rango en la hoja de cálculo y, a continuación, haga clic en Expandir diálogo .

4. En Elija dónde desea colocar el informe de tabla dinámica, especifique el lugar siguiendo uno de estos procedimientos:

� Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la celda A1, haga clic en Nueva hoja de cálculo.

� Para colocar el informe de tabla dinámica en una hoja de cálculo existente, seleccione Hoja de cálculo existente y, a continuación, en el cuadro Ubicación, especifique la primera celda del rango de celdas donde desea colocar el informe de tabla dinámica.

5. Haga clic en Aceptar. 6. Para agregar campos al informe, siga uno o varios de estos procedimientos:

� Para colocar un campo en el área predeterminada de la sección de diseño, active la casilla que se encuentra junto al nombre del campo en la sección de campos.

� Para colocar un campo en un área específica de la sección de diseño, haga clic con el botón secundario en el nombre de campo en la sección de campos y, a continuación, seleccione Agregar a filtro de informe, Agregar a etiquetas de columna, Agregar a etiquetas de fila o Agregar a valores.

� Para arrastrar un campo al área que desee, haga clic y mantenga presionado el nombre del campo en la sección de campos y, a continuación, arrástrelo hasta un área de la sección de diseño.

� Los cambios que realice en los datos de origen después de crear el informe de tabla dinámica se reflejarán en el informe al actualizar el informe de tabla dinámica seleccionado (Herramientas de tabla dinámica, pestaña Opciones, grupo Datos, botón Actualizar).

� Si agrega filas al rango de datos de origen, puede incluir dichas filas en el informe dinámico cambiando los datos de origen (Herramientas de tabla dinámica, pestaña Opciones, grupo Datos, botón Cambiar datos de origen). Si los datos de origen están en una tabla de Excel, las filas adicionales se mostrarán automáticamente al actualizar el informe de tabla dinámica.

15.3.4 CREAR UN INFORME DE GRÁFICO DINÁMICO A PARTIR DE UN INFORME DE TABLA DINÁMICA EXISTENTE

1. Haga clic en el informe de tabla dinámica.

De esta forma, se muestran las Herramientas de tabla dinámica, agregando las pestañas

Page 48: Informatica Basica Excel

Procedimientos de diagnóstico y Solución de Problemas Pág. 121

Opciones y Diseño.

2. En la pestaña Opciones, en el grupo Herramientas, haga clic en Gráfico dinámico. 3. En el cuadro de diálogo Insertar gráfico, haga clic en el tipo y en el subtipo de gráfico que desee. Puede usar

cualquier tipo de gráfico excepto los gráficos xy (dispersión), de burbujas o de cotizaciones. 4. Haga clic en Aceptar.

15.4 GRAFICOS ESTADISTICOS Los gráficos se usan para presentar series de datos numéricos en formato gráfico y de ese modo facilitar la comprensión de grandes cantidades de datos y las relaciones entre diferentes series de datos.

Datos en la hoja de cálculo Gráfico creado con los datos de la hoja de cálculo

Cuando cree un gráfico o modifique uno existente, puede elegir entre distintos tipos de gráfico (por ejemplo, gráficos de columnas o circulares) y subtipos (por ejemplo, gráficos

de columnas apiladas o gráficos circulares 3D). También puede crear un gráfico combinado al utilizar varios tipos de gráficos en uno solo.

Ejemplo de gráfico combinado que usa un gráfico de columnas y uno de líneas.

15.4.1 TIPOS DE GRÁFICOS DISPONIBLES GRAFICOS DE COLUMNAS

En un gráfico de columnas se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo. Este tipo de gráfico es útil para mostrar cambios de datos en un período de tiempo o para ilustrar comparaciones entre elementos. En los gráficos de columnas, las categorías normalmente se organizan en el eje horizontal y los valores en el eje vertical.

GRAFICOS DE LINEAS

En un gráfico de líneas se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo (hoja de cálculo: documento principal que se utiliza en Excel para almacenar y trabajar con datos. Consta de celdas que se organizan en filas y columnas. Una hoja de cálculo se almacena siempre en un libro.). Los gráficos de líneas pueden mostrar datos continuos en el tiempo, establecidos frente a una escala común y, por tanto, son idóneos para

Page 49: Informatica Basica Excel

Informática I

Procedimientos de diagnóstico y Solución de Problemas Pág. 122

mostrar tendencias en datos a intervalos iguales. En un gráfico de líneas, los datos de categoría se distribuyen uniformemente en el eje horizontal y todos los datos de valor se distribuyen uniformemente en el eje vertical.

GRAFICOS CIRCULARES

En un gráfico circular se pueden representar datos contenidos en una columna o una fila de una hoja de cálculo. Los gráficos circulares muestran el tamaño de los elementos de una serie de datos en proporción a la suma de los elementos. Los puntos de datos de un gráfico circular se muestran como porcentajes del total del gráfico circular.

Piense en utilizar un gráfico circular cuando:

� Sólo tenga una serie de datos que desee trazar.

� Ninguno de los valores que desea trazar son negativos.

� Casi ninguno de los valores que desea trazar son valores cero. � No tiene más de siete categorías. � Las categorías representan partes de todo el gráfico circular.

GRAFICOS DE BARRAS

En un gráfico de barras se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo Los gráficos de barras muestran comparaciones entre elementos individuales. Piense en utilizar un gráfico de barras cuando:

� Las etiquetas de eje son largas. � Los valores que se muestran son

duraciones.

GRAFICO DE AREA

En un gráfico de área se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo. Los gráficos de área destacan la magnitud del cambio en el tiempo y se pueden utilizar para enfocar la atención en el valor total en una tendencia. Por ejemplo, se pueden trazar los datos que representan el beneficio en el tiempo en un gráfico de área para destacar el beneficio total.

Al mostrar la suma de los valores trazados, un gráfico de área también muestra la relación de las partes con un todo.

GRAFICOS DE TIPO XY (DISPERSION)

En un gráfico de tipo XY (dispersión) se pueden trazar datos organizados en columnas y filas de una hoja de cálculo (hoja de cálculo: documento principal que se utiliza en Excel para almacenar y trabajar con datos. Consta de celdas que se organizan en filas y columnas. Una hoja de cálculo se almacena siempre en un libro.). Los gráficos de dispersión muestran la relación entre los valores numéricos de varias series de datos o trazan dos grupos de números como una serie de coordenadas XY.

Page 50: Informatica Basica Excel

Procedimientos de diagnóstico y Solución de Problemas Pág. 123

Piense en utilizar un gráfico de dispersión cuando:

� Desea cambiar la escala del eje horizontal. � Desea convertir dicho eje en una escala logarítmica. � Los espacios entre los valores del eje horizontal no

son uniformes. � Hay muchos puntos de datos en el eje horizontal. � Desea mostrar eficazmente datos de hoja de cálculo

que incluyen pares o conjuntos de valores agrupados y ajustar las escalas independientes de un gráfico de dispersión para revelar más información acerca de los valores agrupados.

� Desea mostrar similitudes entre grandes conjuntos de datos en lugar de diferencias entre puntos de datos.

� Desea comparar muchos puntos de datos sin tener en cuenta el tiempo: cuantos más datos incluya en un gráfico de dispersión, mejores serán las comparaciones que podrá realizar.

Para organizar los datos de una hoja de cálculo para un gráfico de dispersión, debería colocar los valores de X en una fila o columna y, a continuación, escribir los valores y correspondientes en las filas o columnas adyacentes.

15.4.2 ELEMENTOS DE LOS GRÁFICOS

Un gráfico consta de numerosos elementos. Algunos de ellos se presentan de forma predeterminada y otros se pueden agregar según las necesidades. Para cambiar la presentación de los elementos del gráfico puede moverlos a otras ubicaciones dentro del gráfico o cambiar su tamaño o su formato. También puede eliminar los elementos del

gráfico que no desee mostrar.

El área del gráfico. El área de trazado del

gráfico. Los puntos de datos de la

serie de datos que se trazan en el gráfico.

Los ejes horizontales (categorías) y vertical (valores) en los que se trazan los datos del gráfico.

La leyenda del gráfico. Un título de eje y de

gráfico que puede agregar al gráfico.

Una etiqueta de datos que puede usar para

identificar los detalles de un punto de datos de una serie de datos

15.4.3 CREAR UN GRÁFICO

En la mayoría de los gráficos, como los gráficos de columnas y los gráficos de barras, puede representar gráficamente los datos que están organizados en las filas o columnas de una hoja de cálculo. Sin embargo, algunos tipos de gráfico (como el gráfico circular o el gráfico de burbujas) requieren una disposición de datos específica.

Page 51: Informatica Basica Excel

Informática I

Procedimientos de diagnóstico y Solución de Problemas Pág. 124

15.4.4 Aplicar un diseño de gráfico predefinido

1. Haga clic en cualquier parte del gráfico al que desee dar formato mediante un diseño predefinido. De este modo se muestran las Herramientas de gráficos y se agregan las pestañas Diseño, Presentación y Formato.

2. En la pestaña Diseño, en el grupo Diseños de gráfico, haga clic en el diseño que desee usar.

15.4.5 Aplicar un estilo de gráfico predefinido 1. Haga clic en cualquier parte del gráfico al que desee dar formato mediante un estilo predefinido. De este

modo se muestran las Herramientas de gráficos y se agregan las pestañas Diseño, Presentación y Formato.

2. En la pestaña Diseño, en el grupo Estilos de diseño, haga clic en el estilo que desee usar.

15.4.6 Cambiar manualmente el diseño de elementos del gráfico 1. Haga clic en el elemento de gráfico cuyo diseño quiera modificar, o haga lo siguiente para seleccionarlo

de una lista de elementos de gráfico: 1. Haga clic en cualquier parte del gráfico para mostrar las

Herramientas de gráficos. 2. En la pestaña Formato, en el grupo Selección actual, haga

clic en la flecha situada junto al cuadro Elementos de gráfico y, a continuación, haga clic en el elemento que desee.

2. En la pestaña Presentación, en los grupos Etiquetas, Ejes o Fondo, haga clic en el botón del elemento de gráfico que corresponda al elemento de gráfico que seleccionó y luego haga clic en la opción de diseño deseada.

15.5 Importar Y Exportar Datos

15.5.1 Importar O Exportar Archivos De Texto

Hay dos formas de importar datos de un archivo de texto con Microsoft Excel: puede abrir el archivo de texto en Excel o puede importarlo como un rango de datos externos. Para exportar datos de Excel a un archivo de texto, use el comando Guardar como.

Son dos los formatos de archivo de texto que se usan habitualmente:

� Archivos de texto delimitado (.txt), en los que el carácter de tabulación (el código de carácter ASCII 009) separa normalmente cada campo de texto.

Page 52: Informatica Basica Excel

Procedimientos de diagnóstico y Solución de Problemas Pág. 125

� Archivos de texto de valores separados por comas (.csv), en los que el carácter de coma (,) separa normalmente cada campo de texto.

Puede cambiar el carácter separador que se utiliza tanto en los archivos de texto delimitados como en los .csv. Esto puede ser necesario para asegurarse de que la operación de importación o de exportación se realizará de la manera deseada.

Notas :Puede importar o exportar hasta 1.048.576 filas y 16.384 columnas.

15.5.2 Importar un archivo de texto abriéndolo en excel

Un archivo de texto creado con otro programa se puede abrir como un libro de Excel con el comando Abrir. Al abrir un archivo de texto en Excel, no cambia su formato: puede verlo en la barra de título de Excel, donde el nombre del archivo conserva la extensión del nombre del archivo de texto (por ejemplo, .txt o .csv).

1. Haga clic en la pestaña Archivo y, a continuación, haga clic en Abrir. Aparece el cuadro de diálogo Abrir.

2. En la lista, seleccione Archivos de texto. 3. Busque el archivo de texto que desee abrir y haga doble clic en él.

� Si el archivo es un archivo de texto (.txt), Excel inicia el Asistente para importación de texto. Haga clic en Ayuda en cualquier página del Asistente para importación de texto para obtener más información sobre el uso del asistente. Cuando termine de realizar los pasos del Asistente, haga clic en Finalizar para completar la operación de importación.

� Si el archivo es un archivo .csv, Excel abre automáticamente el archivo de texto y muestra los datos en un libro nuevo.

IMPORTAR UN ARCHIVO DE TEXTO CONECTÁNDOSE A ÉL

Puede importar datos de un archivo de texto en una hoja de cálculo existente como un rango de datos externos.

1. Haga clic en la celda en la que desea colocar los datos del archivo de texto.

2. En la ficha Datos, en el grupo Obtener datos externos, haga clic en Desde texto.

3. Busque el archivo de texto que desee importar y haga doble clic en él.

4. En el cuadro de diálogo Importar datos, haga lo siguiente:

� Si lo desea, haga clic en Propiedades para establecer las opciones de actualización, formato y diseño de los datos importados.

� En ¿Dónde desea situar los datos?, realice uno de estos procedimientos:

� Para devolver los datos en la ubicación seleccionada, haga clic en Hoja de cálculo existente.

� Para devolver los datos en la esquina superior izquierda de una nueva hoja de cálculo, haga clic en Hoja de cálculo nueva.

5. Haga clic en Aceptar.

15.5.3 EXPORTAR DATOS A UN ARCHIVO DE TEXTO GUARDÁNDOLO

Puede convertir una hoja de cálculo de Excel en un archivo de texto con el comando Guardar como.

1. Haga clic en la pestaña Archivo y, a continuación, haga clic en Guardar como.

� Se abrirá el cuadro de diálogo Guardar como.

Page 53: Informatica Basica Excel

Informática I

Procedimientos de diagnóstico y Solución de Problemas Pág. 126

2. En el cuadro Guardar como tipo, elija el formato de archivo de texto para la hoja de cálculo.

� Por ejemplo, haga clic en Texto (delimitado por tabulaciones) o CSV (delimitado por comas). 3. Busque la ubicación en la que desee guardar el nuevo archivo de texto y, a continuación, haga clic en Guardar. 4. Aparecerá un cuadro de diálogo en el que se informa de que sólo la hoja de cálculo actual se va a guardar en el

nuevo archivo. Si está seguro de que la hoja de cálculo actual es la que desea guardar como archivo de texto, haga clic en Aceptar. Puede guardar otras hojas de cálculo como archivos de texto distintos repitiendo este procedimiento para cada hoja.

5. Aparecerá un segundo cuadro de diálogo en el que se indica que la hoja de cálculo puede contener características no compatibles con los formatos de archivo de texto. Si sólo está interesado en guardar los datos de la hoja de cálculo en el nuevo archivo de texto, haga clic en Sí. Si no está seguro y desea obtener más información sobre las características de Excel incompatibles con los formatos de archivo de texto, haga clic en Ayuda para obtener información.

Tema Nro. 16

Macros

Una macro es un conjunto de instrucciones que permite automatizar una tarea. El uso de macros permite la repetición de tareas de forma más eficiente que la ejecución manual y tediosa. Por ejemplo, si uno desea que cada celda que contiene un valor numérico se convierta a un tipo de dato monetario y que estén agrupados en cierta dirección de la página es posible de lograr con macros; otro típico ejemplo es la generación de movimiento o variación numérica de los datos en tiempo real; además también es posible definir funciones personalizadas que podrán ser invocadas desde la hoja de cálculo. En fin existen muchos modos de usar macros.

Al ser grabada una macro se escribe en un lenguaje llamado Visual Basic for Applications (VBA). Sin importar que tan simple haya sido la grabación de un macro o que tan elaborado ha sido el algoritmo utilizado para su ejecución, siempre estará escrito en VBA y almacenado en una hoja de módulo o dentro de la hoja de cálculo.

16.1 Breve Historia de las Macros

16.2 Grabación y Almacenamiento de Macros

16.2.1 Grabando un Nuevo Macro Un macro, cuando es grabado El proceso de grabación de un nuevo macro es muy sencillo, basta con ejectura el comando de grabación de macro:

1: Ribbon View (vista) >> Macros >> Record Macro (“Grabar Nueva Macro”)

Macros y Lotus 1-2-3 •Origen de los Macros. •Utilizaba una

herramienta similar al Macro Recorder.

•Permitia relacionar un macro con un acceso por combinación de teclas.

Hojas de Macros XLM •Eran hojas que

contenian funciones en forma secuencial.

•Lenguaje poderoso pero dificil de utilizar.

Macros VBA •De gran popularidad y

aceptación entre desarrolladores.

•Posee una poderosa plataforma que ha variado muy poco a través de los años.

Macros Excel •Permite el uso de 2

herramientas poderosas: Macro recorder y VBA Editor.

Page 54: Informatica Basica Excel

Procedimientos de diagnóstico y Solución de Problemas Pág. 127

2: El siguiente cuadro se mostrará, deberá ser completado adecuadamente.

3: Mientras el macro esta en grabación, se deberán ejecutar las acciones que se desea repetir en el futuro.

Se debe tener en cuenta que el estado (esq. Inf. Izq.) se mantenga.

>> Resultado después de haber aplicado modificaciones en

elformato, una celda a la vez. (usando referencias fijas)

16.1.1. Ejecutando un Macro Guardado

Un macro, cuando es grabado queda registrado en una lista de macros, la que se puede consultar en:

1: Ribbon View (vista) >> Macros >> Record Macros (“Ver Macros”)

Page 55: Informatica Basica Excel

Informática I

Procedimientos de diagnóstico y Solución de Problemas Pág. 128

2: Desde esta ventana es posible editar y ejecutar el macro, según elijamos.

3: El macro se ejecutará con la opción Run (“Ejecutar”). El macro también puede ser ejecutado a través del

acceso directo de teclado que le fue asignado al momento de su creación.

Existen dos formas de Grabación de un macro comúnmente utilizadas: - Con Referencias Fijas: Las posiciones de las celdas serán siempre las mismas al momento de

ejecutar el macro. - Con Referencias Relativas: Las acciones sobre las celdas se determinarán basándose en la

posición de la celda activa actual. Mayor explicación será dad en próximas secciones.

*Es necesario entender que al no utilizar referencias relativas, todas las acciones realizadas sobre una celda se volverán a repetir exactamente en la misma manera. Examinemos el resultado que se obtiene al utilizar el macro anteriormente usado sobre los signos en diferentes celdas:

Antes de grabar el Macro Nuevo orden

Resultado después de grabar el Macro Resultado después de ejecutar el macro

Page 56: Informatica Basica Excel

Procedimientos de diagnóstico y Solución de Problemas Pág. 129

Unidad Didáctica Nro. 5

Procedimientos de Diagnóstico y Solución de Problemas

Se define como una actividad programada de inspecciones, tanto de funcionamiento como de seguridad, ajustes, reparaciones, análisis, limpieza, lubricación, calibración, que deben llevarse a cabo en forma periódica en base a un plan establecido. Relativo a la informática, el mantenimiento preventivo consiste en la revisión periódica de ciertos aspectos, tanto de hardware como de software en un PC. Estos influyen en el desempeño fiable del sistema, en la integridad de los datos almacenados y en un intercambio de información correctos, a la máxima velocidad posible dentro de la configuración optima del sistema.

Tema Nº 7 Procedimientos de diagnóstico y solución de problemas10

En un mundo perfecto instalaríamos nuestro programa y no volveríamos a preocuparnos por ellos hasta ser sustituidos. Desafortunadamente esto no es así debemos detectar y solucionar los problemas que se nos presenten. Un correcto mantenimiento de los dispositivos y de las aplicaciones mejorara el rendimiento del sistema y nos evitara problemas más graves. Veremos cómo podemos documentar las incidencias cuando se produzcan, de modo que utilizando estándares realizaremos un mejor seguimiento de cuales son y donde se producen los errores más frecuentes En la atención al usuario estableceremos canales de comunicación que nos permitan mejorar los diferentes aspectos de nuestro sistema informático, conocer los errores más frecuentes y preparar actividades de formación para las personas que trabajan en el entorno informático.

17.1 Mantenimiento Preventivo El Mantenimiento es el conjunto de operaciones y cuidados necesario para que el sistema pueda seguir funcionar adecuadamente .Un mantenimiento preventivo nos permite anticiparnos a un posible error o problema .La prevención bien planificada y más o menos estructurada nos ayudara a evitar errores mayores que consuman más recursos temporales y económicos, Por lo tanto ,a pesar de que en ocasiones el mantenimiento preventivo es uno de los aspectos más olvidados, la correcta puesta en práctica de un programa para llevarlo a cabo supondrá un ahorro de tiempo y dinero El diagnóstico y posterior solución de un problema, cuando este se ha hecho presente, no puede considerarse mantenimiento preventivo Ejercicio resuelto 9.1 Enumera las razones por las que deberíamos hacer un mantenimiento preventivo en nuestro sistema Solución

a) Ahorra tiempo. Muchas de las tareas de mantenimiento pueden realizarse de manera automática y otras pueden realizarse en tiempos disponibles. Reparar un sistema siempre nos supondrá más tiempo y posiblemente no podremos decidir.

b) Ahorra dinero. Evitar Problemas nos ahorra coste en la reparación c) Mejorar el rendimiento. Algunos elementos de nuestro sistema se degradan con el tiempo; un

mantenimiento preventivo los mantendrá en óptimas condiciones d) Protege la información. Evitando fallos en el sistema y realizando tareas de mantenimiento protegemos

los datos de nuestro sistema. Herramientas de mantenimiento

Una de las Herramientas más útiles y sencillas de usar es la llamada “DiskDefragmenter” que nos permite recuperar

10 Implantación y Mantenimiento de Aplicaciones Ofimáticas y Corporativas. Manuela Vera Colas. p 195 – 206.

Page 57: Informatica Basica Excel

Informática I

Procedimientos de diagnóstico y Solución de Problemas Pág. 130

espacios perdidos en nuestro disco, colocando la información de forma contigua. Cuando instalamos un sistema operativo o un programa sobre un disco nuevo, la información se escribe de modo contiguo, sin dejar espacios en blanco. A medida que trabajamos sobre el disco, creando y borrando documentos, instalando y desinstalando programas, en el disco aparece un serie de fragmentos que se encuentra n vacios. En ocasiones se hace necesario dividir un archivo para que sea escrito en el soporte Cuando los archivos se encuentran en el disco en posiciones contiguas, el acceso a ellos es mas rápido que cuando se encuentran fragmentados y colocados por toda la superficie del disco. Un disco duro fragmentado ralentiza el acceso a los datos por que es necesario reunir información fragmentada en el soporte para poder acceder a ella. Existen en los mercados distintos programa como PerfectDisk que nos permite optimizar nuestro disco consolidando el espacio libre incluso en disco duros de gran capacidad. El sistema operativo Windows incorpora una herramienta de desfragmentación del disco dentro de sus herramientas del sistema a las que podemos acceder desde Inicio-programas-Accesorios-herramientas del sistema-Desfragmentación del disco

Figura 9.1.-Estrucutra de un disco duro

Tras un análisis del disco se mostrara el aspecto que presenta y nos dará una recomendación sobre la necesidad de proceder a la ejecución del programa de desfragmentado

Ejercicio Propuesto 9.1 Comprueba si tus disco duros están fragmentados mediante el análisis de los mismo y si sería conveniente ejecutar la herramienta del sistema para ”defragmentar el disco” Como es lógico suponer, no es lo mismo utilizar esta herramienta con discos de pequeño tamaño como puede ser uno de 40GB que utilizarla con disco de más de 100 GB. Debemos recodar que mientras estemos ejecutando el desfragmentado cada escritura en el disco provocara que se reinicie el programa de nuevo.

17.2 Partes de Incidencias Una incidencia refleja un problema que se da en un determinado sistema. Una hoja o una parte de incidencia nos permiten, básicamente, estandarizar y clasificar el posible error de acuerdo con unos parámetros de control, establecidos. Si deseamos realizar una buena asistencia al usuario debemos disponer de un sistema de apertura gestión de incidencias lo más completo posible, que permita cuantificar y estratificar las incidencias que se produzcan y seguirlas hasta su solución Cuando abrimos una incidencia esta debe ser registrada y tipificada como activa y permanecerá en este estado hasta que se produzca su resolución. Las incidencias que ya han sido resueltas y por tanto cerradas deberán pasar a formar parte de un base de datos o fichero histórico para que puedan ser consultadas en cualquier momento y podamos realizar con ellas diferentes tipos de estadísticas. Un parte de incidencias es un herramienta y como tal sus principales funciones serán las siguientes:

� Facilitar la localización y solución del problema � Permitir hacer un seguimiento de los principales tipos de incidencias � Determinar cuáles pueden ser los elementos a corregir � Recopilar información que será utilizada para:

-Seleccionar el tipo de hardware y software más conveniente -Establecer planes de formación de usuarios Propiedades de una buena hoja de incidencias

� Debe ser breve y concisa � Debe ser completa � Debe ser sencilla de entender y completar � Debe ser de fácil seguimiento

Page 58: Informatica Basica Excel

Procedimientos de diagnóstico y Solución de Problemas Pág. 131

El diseño de una parte incidencia varia de incidencias varia de una empresa a otra dependiendo de los sistema que incluya, de la estructura y organización de la empresa, incluso de los propios gustos personales del equipo de mantenimiento, sin embargo hay algunos elementos comunes a todos los formatos y que deberíamos incluir Identificación de la incidencia

� Número de orden consecutivo � Fecha y hora de creación del parte de la incidencia � Nombre de la persona que informa sobre el problema, departamento o lugar donde se ha producido

y equipos o equipos donde se da error � Descripción del problema, incluyendo si el equipo no está operativo o podemos seguir trabajando

con él. � Firma

A completar por el técnico Algunos de los campos de un parte de incidencias deben ser completados por técnico, o por la persona encargada de la explotación y le mantenimiento de las aplicaciones. En ocasiones deberá ponerse en contacto con el usuario para conseguir más detalladla información.

� Posible causas del error � Consecuencia del error � Valoración económica si procede � fecha y firma

Se le asignara una prioridad en función del tipo de incidencia. Por ejemplo, no es lo mismo un a incidencia que describe lentitud en Internet que un problema que impide arrancar el sistema operativo Ejercicio Propuesto 9.2 Escribir priorizadas las siguientes incidencias en nuestro sistema: *No podemos visualizar una página web, el equipo no arranca, la impresora no imprime, Word no lee un documento de texto, el sistema se cierra repentinamente Un buen seguimiento de los distintos tipos de incidencias, y la correcta interpretación de los partes nos proporcionan las claves para un mejor mantenimiento del sistema Estadísticas de las incidencias Una vez resuelta la incidencia y completado el parte correspondiente por la persona que lo ha resuelto, lo correcto sería llevar un control de las distintas incidencias que se generan en la empresa .De este modo obtendremos valores sobre:

� Que equipos y/o departamentos registran mayor número de problemas � Que tipos de usuarios informan sobre los errores � Que software es el que presenta más problemas � Tiempos de respuesta � Coste en términos económicos de las soluciones