Excel 03

Formato Avanzado de Datos

En este capitulo trataremos los siguientes Objetivos Específicos:

Formato personalizado

Formato condicional

"No podemos cambiar las cartas que nos han sido dadas,

pero sí podemos jugar la partida"

Randy Pausch

1

Microsoft Office Excel 2010

Formato Personalizado

a. Códigos usados: 0 # , “texto” *

La apariencia que pueden adquirir los números en las celdas, pueden variar según

el formato que se les asigne y para esto existen varias categorías de formatos.

Paso a Paso: Aplicar formato numérico

1. Diseñar la siguiente hoja de cálculo o abrir el Archivo

“FERRETERIA EL MAESTRITO 1”

Tipo de letra Algerian,

tamaño 22, color azul

Tipo de letra Calibri,

tamaño 11, color negro

Tipo de letra Calibri,

tamaño 9, color negro

2. Seleccionar el bloque de celdas, rango: D10:E13 y E14:E16

Para seleccionar áreas distintas de una hoja de cálculo, presionar la tecla

CTRL.

- Primero seleccionar con el mouse el rango D10:E13

- Luego, pulsar la tecla CTRL no soltarla

- Seleccionar el nuevo rango, área E14:E16

3. Hacer clic derecho sobre el bloque de celdas seleccionadas y elegir la opción

2

Formato avanzado de datos

Bloque de celdas

seleccionado: D10:D13, E10:E16

4. Se presenta el siguiente cuadro de diálogo.

Elegir la ficha Número, opción Personalizada

Elegir la opción “Personalizada”

3


Caracteres Especiales

. El punto, representa posición decimal.

, La coma, representa millares.

0 El número CERO, representa posiciones numéricas que se

mostrarán obligatoriamente.

# El símbolo #, representa posiciones numéricas que se muestran

opcionalmente.

“S/.” Las comillas, muestran el texto al lado izquierdo del número.

*. El asterisco (carácter de repetición), rellena del carácter

especificado los espacios en blanco de una celda determinada que

se encuentra con un número.

5. A continuación se muestra el formato resultante

según la opción seleccionada

Muestra dos

decimales

Muestra coma

de separación

de millares

Muestra dos

decimales y coma

separado de miles

A continuación mostraremos algunos ejemplos de códigos personalizados que Usted mismo puede crear, digitándolo en “Tipo”.

4


Digita Código Se muestra Observación

5 00 05 Para registro de notas

101 000000 000101 Para códigos

1234 #,##0 1,234 Para valores numéricos

1234 #,##0.00 1,234.00 Para dinero, con dos decimales

1234 “S/.”#,##0.00 S/.1,234.00 Para dinero, con dos decimales y

símbolo monetario.

1234 “S/.”**#,##0.00 S/.***1,234.00 Para dinero, con dos decimales,

símbolo monetario y carácter de relleno.

1234 “S/.”* #,##0.00 S/. 1,234.00 Para dinero, con dos decimales,

símbolo monetario alineado a la

Espacio en blanco izquierda de la celda y valor alineado a la derecha de la celda.

b. Formato de moneda

El formato de moneda incluye:

El símbolo de la moneda S/. (SOLES), $ (DOLARES) o € (EUROS) y/o

formatos de monedas de otros países.

Dos decimales (.00) y separador de millares (,)

Paso a Paso: Utilizar el formato de Moneda


“FERRETERIA EL MAESTRITO 2”

5


2. Seleccionar el rango de celdas D10:D13 y E10:E16

3. Hacer clic derecho sobre el bloque de celdas seleccionadas y elegir la

opción

4. Se presenta el siguiente cuadro de diálogo

Elegir la ficha Número, categoría “Moneda

A

B

C

D

E

a. Seleccione la categoría moneda

b. Indique que se utilizarán dos decimales

c. Seleccione símbolo de moneda “S/. Español (Perú)

d. Elegir el formato que especifica color rojo y símbolo negativo para

los números negativos

e. Finalmente hacer clic en el botón “Aceptar”

5. El símbolo de moneda se inserta en los datos numéricos y muestra dos

decimales, como se muestra en la gráfica.

Repita el proceso, pero ahora realícelo con otro tipo de moneda, ejemplo

Euros o Dólares.

6


c. Formato de fecha

Microsoft Office Excel almacena los fechas como números secuenciales

denominados valores de serie.

Por ejemplo, la fecha 1 de enero de 1900 es el número de serie 1 y la fecha 1 de

enero de 2008 es el número de serie 39448, porque es 39.448 días posterior al 1 de

enero de 1900.

Excel almacena las horas como fracciones decimales, ya que la hora se considera

una porción del día. Las fechas y las horas son valores y, por lo tanto, pueden

sumarse, restarse e incluirse en otros cálculos.

Paso a Paso: Utilizar el formato de Fecha


“BOLSA DE VALORES DE LIMA”

2. Seleccionar el rango de celdas C5:F7

3. Hacer clic derecho sobre el bloque de celdas seleccionadas y elegir la opción

o clic en Ficha Inicio, cuadro de diálogo Número

7


Hacer clic en la celda C5 y

arrastrar el mouse diagonalmente hasta la celda F7.

4. Se presenta el siguiente cuadro de diálogo

Elegir la ficha Número, categoría “Fecha”

5. Elegir un tipo de formato de fecha de acuerdo al gráfico y luego hacer clic en

el botón

6. Repita el proceso varias veces, para observar los cambios; si no se muestran

los resultados y salen ##### agrande la columna.

También puede utilizar código personalizado, donde día (dd), mes (mm) y año (yy). ejemplo:

Digita Código Se muestra

25/03/2008 dd/mm/yy 25/03/08

25/03/2008 dd mmm yyyy 25 mar 2008

8


d. Formatos especiales Se utilizar para crear nuestros propios formatos para datos numéricos, fecha,

texto, etc.

Tenga en cuenta que en todos los ejemplos anteriores las celdas tienen datos

numéricos y aunque el formato ha cambiado su apariencia, “el contenido de ellas

sigue siendo numérico” y puede operarse matemáticamente en cualquier fórmula.

Cuando se crean formatos personalizados, estos formatos solo tendrán validez

dentro del libro en el cual se crearon.

Ejemplo de formatos personalizados creados por el usuario:

A

6 64.5 Kg Para pesos en kilogramos 0.0 “Kg”

7 37.4 º C Grados centígrados 0.0”ºC”

8 000254 Para códigos 000000

9 IGV 19% Texto y porcentaje “IGV” 0%

10 4 puntos Número y texto 0 “puntos”

Paso a Paso: Utilizar el formato de Fecha

1. Diseñar la siguiente hoja de cálculo o abrir el archivo “FERRETERIA EL

MAESTRITO 3”

2. Seleccione el bloque de celdas al que desea aplicar formato personalizado.

Rango D10:D13, E10:E16

Bloque de celdas

seleccionado: D10:D13, E10:E16

9


3. Hacer clic derecho sobre el bloque de celdas seleccionadas y elegir la opción 4. Del cuadro de diálogo presentado elegir la opción Personalizada

En el cuadro Tipo, modifique los códigos de formato de número para crear el

formato que desee.

Puede especificar hasta cuatro secciones de códigos de formato. Las

secciones, separadas por caracteres de punto y coma, definen los formatos de:

- Los números positivos

- Los números negativos

- Valores cero

- Texto

En ese orden. Si especifica sólo dos secciones, la primera se utiliza para los

números positivos y ceros, y la segunda se utiliza para los números negativos.

Si especifica sólo una sección, todos los números utilizan ese formato. Si

omite una sección, incluya el punto y coma que separa esa sección.

Tomando como base el formato actual, edite su

propio formato.

Formato de números positivos Formato de ceros

#,##0.00_) ; [ROJO](# ,##0.00) ; 0.00 ; “Factura” @

Formato de números negativos Formato de textos

El símbolo de arroba @ representa el contenido de la celda cuando este

contenido es del tipo texto.

10


Ejemplo:

A un grupo de celdas con diferentes datos se le da el siguiente formato

personalizado:

[AZUL]#,##0.00_) ; [ROJO](# ,##0.00) ; “Sin Valor ; @ “anulada”

A

1

2

3

4

5

-8734.9 Número negativo

0 Cero

Factura Texto

6254.258 Número positivo

Boleta Texto

El resultado final en el rango formateado es el siguiente:

A

1

2

3

4

5

(8,734.90) Entre paréntesis y color rojo

Sin Valor Cero cambia a Sin Valor

Factura anulada Se le agrega la palabra anulada

6,254.26 Redondea y de color azul

Boleta anulada Se le agrega la palabra anulada

Como se puede ver, cada celda adquiere el formato que le corresponde según el contenido que esta tenga.

Códigos de formato de número para fechas y horas Días, meses y años Para presentar días, meses y años, incluya en una sección los

siguientes códigos de formato. Si utiliza una "m" inmediatamente detrás del

código "h" o "hh", o bien inmediatamente delante del código "ss", Microsoft Excel presentará los minutos en lugar de presentar el mes.

CODIGO DE FECHAS

Para ver Use este código

Los meses como 1–12 m

Los meses como 01–12 mm

Los meses como ene.–dic. mmm

Los meses como enero–diciembre mmmm

Los meses como la inicial de cada mes mmmmm

Los días como 1–31 d

Programa Nacional de Informática 11


Los días como 01–31 dd

Los días como lun.–sáb. ddd

Los días como lunes–sábado dddd

Los años como 00–99 aa

Los años como 1900–9999 aaaa

Cuando se aplica código de fechas y el resultado no alcanza en la celda, muestra

asteriscos “*******”, para solucionar este problema agrande la columna.

Horas, minutos y segundos, para presentar horas, minutos y segundos, incluya en

una sección los siguientes códigos de formato.

CODIGO DE HORAS


Las horas como 0–23 h

Las horas como 00–23 hh

Los minutos como 0–59 m

Los minutos como 00–59 mm

Los segundos como 0–59 s

Los segundos como 00–59 ss

Las horas como 4 a.m. h a.m./p.m.

La hora como 4:36 p.m. h:mm a.m./p.m.

La hora como 4:36:03 p. h:mm:ss a/p El tiempo transcurrido en horas; por

ejemplo, 25:02

El tiempo transcurrido en minutos; por

[h]:mm

[mm]:ss

ejemplo, 63:46

El tiempo transcurrido en segundos [ss]

Fracciones de segundo h:mm:ss.00

Si el formato contiene la indicación a.m. o p.m., la hora se basará en el formato de

12 horas, donde "a.m." o "a" indica las horas desde la medianoche hasta el mediodía y "p.m." o "p" indica las horas desde el mediodía hasta la medianoche.

En caso contrario, el reloj se basará en el formato de 24 horas. La letra "m" o las

letras "mm" deben aparecer inmediatamente detrás del código "h" o "hh", o bien

inmediatamente delante del código "ss"; de lo contrario, Microsoft Excel presentará el mes en lugar de presentar los minutos.

12 Programa Nacional de Informática


Códigos de formato de número para posiciones decimales, espacios, colores y condiciones

Utilice los códigos de formato de número para crear un formato de número

personalizado.

Decimales y dígitos significativos

Para dar formato a las fracciones o los números con decimales, incluya los

siguientes dígitos marcadores en una sección. Si un número tiene más dígitos a la

derecha del separador que marcadores en el formato, se redondeará para que tenga

tantos decimales como marcadores.

Si hay más dígitos a la izquierda del separador que marcadores, se presentarán los

dígitos adicionales.

Si el formato contiene solamente signos de número (#) a la izquierda del

separador, los números menores que la unidad comenzarán por el separador.

# Muestra únicamente los dígitos significativos y no muestra los ceros sin

valor.

0 (cero) muestra los ceros sin valor si un número tiene menos dígitos que la

cantidad ceros en el formato.

? Agrega los espacios de los ceros sin valor a cada lado del separador

decimal, para alinear los decimales con formato de fuente de ancho fijo,

como Courier New. También puede utilizarse ? para las fracciones que

tengan un número de dígitos variable.


1234.59 como 1234.6 ####.#

8.9 como 8.900 #.000

.631 como 0.6 0.# 12 como 12.0 #.0#

1234.568 como 1234.57

44.398. 102.65 y 2.8 con decimales alineados ???.??? 5.25 como 5¼ # ???/???

5.3 como 5 3/10

con los símbolos de división alineados

Separador de millares

Para ver una coma como separador de los millares o para ajustar la escala de un

número mediante un múltiplo de mil, incluya una coma en el formato de número.


12000 como 12,000 #,###

13


12000 como 12 #,

12200000 como 12,2 0,0,,

Color

Para definir el color de una sección del formato, escriba en la sección el nombre

de uno de los siguientes ocho colores entre corchetes. El color debe ser el primer

elemento de la sección.

[NEGRO] [AZUL] [FUCSIA] [ROJO]

[AGUAMARINA] [VERDE] [BLANCO] [AMARILLO]

Condiciones

Para definir los formatos de número que se aplicarán únicamente si coinciden con

las condiciones que se hayan especificado, encierre la condición entre corchetes. La condición consta de un operador de comparación y un valor.

Por ejemplo, el siguiente formato muestra los números iguales o inferiores a 100

en color rojo y los números superiores a 100 en color azul.

[Rojo][<=100];[Azul][>100]

En cambio, para aplicar formatos condicionales a las celdas (por ejemplo, el

sombreado de color que depende del valor de una celda)

Códigos de formato de número para texto y espaciado

Agregar caracteres

Para ver texto y números en una celda, encierre los caracteres de texto entre

comillas ("") o ponga delante de un solo carácter una barra invertida (\). Incluya

los caracteres en la sección correspondiente de los códigos de formato.

Por ejemplo, introduzca el formato $ 0.00 "Exceso";-$ 0.00 "Defecto" para ver un

importe negativo como "-$125.74 Defecto". El espacio y los siguientes caracteres

se muestran sin comillas: $ - + / ( ) : ! ^ & ' (comilla simple izquierda) ' (comilla

simple derecha) ~ { } = < >

Incluir una sección de texto

Si se incluye una sección de texto, siempre será la última sección en el formato de

número. Incluya el carácter @ en la sección en que desee presentar el texto

escrito en la celda.

Si se omite el carácter @ en la sección de texto, no se verá el texto que se haya

escrito. Si desea ver siempre caracteres de texto específicos con el texto escrito,

encierre el texto adicional entre comillas dobles (" "), por ejemplo, "facturación

bruta de "@

14


Si el formato no incluye una sección de texto, el texto que escriba no se verá

afectado por el formato.

Agregar espacio

Para crear un espacio con el ancho de un carácter en un formato de número,

incluya un subrayado _ seguido del carácter.

Por ejemplo, si después de un subrayado se cierra un paréntesis _) entonces los

números positivos se alinearán correctamente con los números negativos que

estén entre paréntesis.

Repetir caracteres

Para repetir el carácter siguiente en el formato para llenar el ancho de la columna,

incluya un asterisco (*) en el formato de número.

Por ejemplo, introduzca $*=#,##0.00 para incluir suficientes signos = después del

signo monetario para llenar una celda. El resultado sería: $=====2,716.25

Paso a Paso: Eliminar un formato personalizado

1. Hacer clic en la ficha

2. Hacer clic en el iniciador de cuadro de

diálogo Fuente

3. Del cuadro de diálogo presentado, hacer

clic en el ficha

15


4. Elegir la categoría

5. Seleccione el tipo de formato

6. Hacer clic en el botón para borrar el tipo de formato seleccionado.

También puede elegir la opción “General”.

Formato condicional Utilice un formato condicional que le ayude a explorar y analizar datos visualmente, a

detectar problemas importantes y a identificar modelos y tendencias.

A continuación se describe las opciones del formato condicional

Opción Descripción

Para encontrar más fácilmente celdas específicas

dentro de un rango de celdas, puede aplicar

formato a dichas celdas específicas basándose en

un operador de comparación.

Por ejemplo, en una hoja de cálculo de inventario

clasificada por categorías, puede resaltar los

productos con menos de 10 artículos disponibles

en amarillo.

En una hoja de cálculo de resumen de almacén al

por menor, puede identificar todos los almacenes

con beneficios superiores al 10%, volúmenes de

ventas menores de 100.000 dólares EE.UU., y

región igual a "Sudeste".

Puede buscar los valores más altos y más bajos

en un rango de celdas según un valor de corte

que especifique.

Por ejemplo, puede buscar los 5 productos más

vendidos en un informe regional, el 15% de los

productos del final de una encuesta al cliente o

los 25 mejores salarios de un análisis de personal

de departamento.

Una barra de datos le ayuda a ver el valor de una

celda con relación a las demás. La longitud de la

barra de datos representa el valor de la celda.

16


Una barra más grande representa un valor más

alto y una barra más corta representa un valor

más bajo.

Las barras de datos son útiles para encontrar

números más altos y más bajos especialmente

con grandes cantidades de datos, como las

mayores y menores ventas de juguetes en un

informe de ventas.

Las escalas de colores son guías visuales que

ayudan a comprender la variación y la

distribución de datos.

Una escala de tres colores permite comparar un

rango de celdas utilizando una gradación de tres

colores.

El tono de color representa los valores

superiores, medios o inferiores.

Por ejemplo, en una escala de colores verde,

amarillo y rojo, puede especificar que las celdas

con el valor superior tengan un color verde, las

celdas de valor medio tengan un color amarillo y

las celdas de valor inferior tengan un color rojo.

Utilice un conjunto de iconos para comentar y

clasificar datos de tres a cinco categorías

separadas por un valor de umbral.

Cada icono representa un rango de valores.

Por ejemplo, en el conjunto de iconos de 3

flechas, la flecha roja hacia arriba representa

valores más altos, la flecha hacia el lado amarilla

representa valores medios y la flecha hacia abajo

verde representa valores más bajos.

Crea una nueva regla

Elimina formato condicional

Organizador de las reglas aplicadas en la hoja

Paso a Paso: Crear un formato condicional: Barra de datos

1. Diseñar la siguiente hoja de cálculo o abrir el archivo “EVOLUCION DEL

PBI 1”

2. Aplicar formato condicional al rango de celdas B5:B16.

Seleccionar el bloque de celdas donde aplicará el formato condicional B5:B16

17


Para seleccionar el bloque de celdas, hacer clic en la celda B5 y arrastrar hasta

la celda B16.

3. Hacer clic en la ficha:

Inicio, Formato Condicional, Barra de datos, Barra de datos azul

4. Su hoja se muestra como en la gráfica, se ha agregado barras horizontales según

el valor de cada celda.

Agregar condiciones Se puede agregar diferentes especificaciones condicionales, de tal forma que el formato

sólo afecte a las celdas que cumplan la condición.

18


Paso a Paso: Crear un formato condicional: Es mayor que…


PBI 2”.

2. Aplicar formato condicional donde la variación porcentual del PBI supera el

6%

3. Seleccionar el bloque de celdas donde aplicará el formato condicional B4:B16

Hacer clic en la celda

B5 y arrastrar hasta la

celda B16.


Inicio, Formato Condicional, Resaltar reglas de celdas, Es mayor que….

19


5. Del cuadro de diálogo presentado especificar la condición que deben cumplir

las celdas a las que se les aplicará el formato condicional.

Escriba el número 6 Seleccione esta opción

6. Finalmente hacer clic en el botón para aplicar el formato

condicional.

Paso a Paso: Crear un formato condicional: Es menor que…


PBI 3” (el diseño es similar al ejercicio anterior).

2. Aplicar formato condicional al bloque de celdas B5:B16, donde la variación

porcentual del PBI es menor al 3%.

3. Seleccionar el bloque de celdas donde aplicará el formato condicional

B5:B16


Inicio, Formato Condicional, Resaltar reglas de celdas, Es menor que….

20




Escriba el número 3

Seleccione esta

opción

6. Finalmente hacer clic en el

botón para

aplicar el formato

condicional. Su hoja de

cálculo debe mostrarse como

en la gráfica, resalta los

valores menores que 3.

Paso a Paso: Crear un formato condicional: Entre


PBI 4” (el diseño es similar al ejercicio anterior).

2. Aplicar formato condicional al rango de celdas B5:B16, donde la variación

porcentual del PBI está entre 1% y 5%.


B5:B16


Inicio, Formato Condicional, Resaltar reglas de celdas, Entre….

21






Seleccione esta

opción

6. Finalmente hacer clic en el botón

para aplicar el formato

condicional. Su hoja de cálculo

debe mostrarse como en la gráfica,

resalta los valores entre 1 y 5.

Paso a Paso: Crear un formato condicional: Texto que contiene…

1. Diseñar la siguiente hoja de cálculo o abrir el archivo “NUTRICIÓN MUJER

EMBARAZADA”.

22


2. Aplicar formato condicional al rango de celdas A4:A17, donde el nombre

del nutrimento sea Vitamina


A4:A17


Inicio, Formato Condicional, Resaltar reglas de celdas, Texto que contiene...



Escriba la

palabra vitamina

Seleccione esta

opción

6. Finalmente hacer clic en el botón para aplicar el formato

condicional. Su hoja de cálculo debe mostrarse como en la gráfica.

23


Paso a Paso: Eliminar formato condicional

1. Abrir el archivo “EVOLUCION DEL

PBI 1”.

2. Aplicar el formato de Barra de datos

color azul

3. Eliminaremos el formato condicional

aplicado a la siguiente hoja de

cálculo

Seleccionar el bloque de celdas

B5:B16

4. Hacer clic en la ficha

Cambiar la apariencia de los datos en función a formatos condicionales Utilice el formato condicional para cambiar la apariencia de los datos analizados.

24


Paso a Paso: Crear formato condicional

1. Diseñar la siguiente hoja de cálculo o abrir el archivo “ASPIRACIONES Y

VERDADES 1”

2. Aplicar formato condicional conjunto de iconos, al rango de celdas B6:B12

3. Seleccionar el bloque de celdas donde aplicará el formato condicional B6:B12


Inicio, Formato Condicional, Conjunto de iconos

Elegir una de las

siguientes opciones

25


Utilizar fórmulas Si el formato condicional tiene que ser más complejo, puede utilizar una fórmula

lógica para especificar los criterios de formato.

Por ejemplo, puede que desee comparar valores con un resultado devuelto por una

función o evaluar datos de celdas que se encuentran fuera del rango seleccionado.

Paso a Paso: Crear formato condicional

1. Diseñar la siguiente hoja de cálculo o abrir el archivo “ASPIRACIONES Y

VERDADES 2”

26


Hacer clic en la

celda B4 y arrastrar

hasta la celda B15.

2. Aplicar formato condicional a los valores mayores al promedio


B4:B15


Inicio, Formato Condicional, Nueva regla

,

5. De las opciones presentes elegir

27


6. Finalmente hacer clic en el botón para aplicar el formato condicional. Su hoja de cálculo debe mostrarse como en la gráfica.

Preguntas de Repaso

1. Hemos realizado formato de millares al siguiente valor 1234 y el resultado es el

siguiente: 1.234,00

Que hacer para que el resultado sea 1,234.00

2. Hemos realizado formato de moneda Soles al siguiente valor 1234 y el resultado es

el siguiente: 1,234.00 S/.

Que hacer para que el formato de moneda salga al inicio del valor S/. 1,234.00

28


3. Tenemos 3 columnas con los siguientes valores:

1234 1234 1234 1234

6498 6498 6498 6498

9867 9867 9867 9867

Cómo especificar a todos los valores formato personalizado de millares a dos

decimales y además a la primera columna formato de Soles (S/.), a la segunda

columna formato de dólares ($), a la tercera columna formato de Euros (€) y a la

cuarta columna formato de Libra Esterlina (₤).

4. Cuál es el proceso a realizar para borrar solamente el formato personalizado

aplicado a n valores.

5. Cuándo realizamos formato de fecha larga, nos da como resultado #####. Que ha

sucedió, porqué sucede esto.

6. Que formato debemos aplicar para que los valores se muestren de la siguiente

manera.

S/.********1,234.00

S/.*******16,498.00

S/. *********867.00

7. Estamos creando un registro de notas. Describa el proceso a realizar para que los

alumnos cuyas notas estén en el rango de 0 a 9 se muestre con un cero al inicio y los

alumnos cuyas notas sean reprobadas se muestren en color rojo.

29


8. Estamos ingresando valores de 4 cifras; pero deseamos que se muestren en la

pantalla con dos ceros al inicio. Cuál es el proceso a realizar.

9. Cuál es el proceso a realizar para borrar solamente el formato condicional aplicado a

n valores.

10. Cuáles son los tipos de formatos numéricos que conoce, descríbalo con un ejemplo

su utilidad.

11. Que formato se ha aplicado a la siguiente hoja de cálculo (columna B).

30

Excel 03

Software

Transcript of Excel 03