UNIDAD

FUNCIONES ESPECIALES Y
FUNCIONES DE TEXTO

Estructura de Contenidos:
FUNCIONES ESPECIALES Y FUNCIONES DE TEXTO
o

FUNCIONES ESPECIALES

Funcin SIFECHA

Funcin FRECUENCIA

FUNCIONES DE TEXTO: IZQUIERDA, DERECHA, MED, CONCATENAR,

HALLAR, LARGO, MAYUSC, MINUSC y NOMPROPIO.

Formato Condicional Avanzado.

Proteccin de Elementos

Introduccin

Estimado participante:
En esta Unidad aprender acerca del uso de funciones especiales de Excel, funciones de
texto, formato condicional avanzado y proteccin de elementos de la hoja de clculo.
Excel 2010 nos proporciona una serie de funciones que tienen una forma especial de
trabajo, diferente al resto, ya que stas permiten obtener un conjunto de resultados en
forma directa, operando de esta forma matricialmente.
Primero, estudiaremos dos funciones que tienen un carcter muy especial en Excel: la
funcin SIFECHA, que permiten trabajar con datos tipo fecha, sin embargo no figura en
ninguna de las categoras de las funciones existentes, la otra funcin es la funcin
FRECUENCIA; esta funcin trabaja de forma matricial.
Luego, utilizaremos las funciones de texto en la manipulacin de cadenas de datos.
Despus, veremos la utilizacin de formatos condicionales avanzados, los cuales se
basan en una frmula o criterio lgico en la determinacin de la aplicacin de dichos
formatos.
Finalmente, protegeremos la hoja de clculo o parte de ella, de tal manera que no pueda
ser modificada sin la autorizacin correspondiente. Le deseo xitos en el desarrollo de
esta Unidad.

Pgina |2

1 FUNCIONES ESPECIALES
a) FUNCIN SIFECHA: Es una funcin que nos permite obtener el nmero de das,
meses y aos que han transcurrido entre dos fechas determinadas.
Sintaxis:
=SIFECHA(Fecha_inicial,Fecha_final,Parmetro)
El parmetro a utilizar en la ltima parte de la frmula puede ser:

D si queremos obtener los das que han transcurrido

M si queremos obtener los meses

Y si queremos obtener los aos

YM si queremos obtener los meses sin tener en cuenta los aos

MD si queremos obtener los das sin tener en cuenta los meses

Ejercicio: Determinar la Edad y el Tiempo de servicios en aos de los siguientes

empleados.

Ingrese los datos y la funcin SIFECHA como se indica en la imagen.

Importante:
IMPORTANTE! Para calcular la edad, debes considerar la fecha de nacimiento y la
fecha actual, representada por la funcin HOY(). Adems, como la edad se est
calculando en aos, el parmetro a usar es Y. En forma similar, para calcular el
tiempo de servicio, debe considerar la fecha de ingreso y la fecha actual.

Pgina |3

Ejercicio: Determinar su record laboral en aos, meses y das de los siguientes

empleados.

Ingrese los datos y la funcin SIFECHA como se indica en la imagen.

Importante:
IMPORTANTE! Para calcular los aos del record laboral, la forma es la misma que se
utiliz para determinar el tiempo de servicios en aos. Sin embargo, para calcular los
meses restantes sin considerar los aos ya contabilizados, usa el parmetro YM.
Para determinar los das restantes sin considerar los meses y aos contabilizados,
use el parmetro MD.

Dnde est la funcin SIFECHA?

Uno de los misterios de Excel es la funcin SIFECHA. Observar que esta funcin
no aparece en la lista de funciones desplegable de la categora Fecha y Hora.
La funcin SIFECHA tiene su origen en Lotus 1-2-3 y aparentemente, Excel la
ofrece para mantener la compatibilidad. Por alguna razn, Microsoft quiere
mantener esta funcin en secreto.

Pgina |4

b) FUNCIN FRECUENCIA: calcula la frecuencia con que se repiten los valores

dentro de un rango, devolviendo una matriz vertical de nmeros.
Sintaxis
=Frecuencia (datos, grupos)
El uso de frecuencias se explicar en el siguiente ejercicio.
Ejercicio: Se hizo una encuesta para conocer las edades de los pacientes
atendidos en el Hospital de Neoplsicas, la muestra poblacional fue de 120
encuestados. Se pide clasificar dicha muestra con una de amplitud de 10,
considerando un lmite mnimo de 29 y un lmite mximo de 79, obteniendo la
frecuencia absoluta y relativa.

Para desarrollar esta aplicacin necesita tener el cuadro donde clasificars la muestra
y obtendrs las frecuencias. Entonces, ingresa siguiente:

Pgina |5

Creando los Grupos de datos:

Estos Grupos o rangos servirn para clasificar las edades de los
encuestados. Se sabe que existe un lmite mnimo de 29 para el primer
rango y un mximo de 79 para el ltimo rango, con una amplitud de 10.
o
o

Digite en la celda K10, el dato 29 como lmite del primer grupo

Ahora, en la celda K11, ingresa el dato 39 como lmite de este grupo,
este valor se obtiene sumando al lmite anterior, la amplitud que es de
10; y as sucesivamente sobre el resto de grupos. Quedar el cuadro
con los grupos as:

Pgina |6

Calculando las frecuencias absolutas:

o

Seleccione

el

rango

de

celdas

donde

se

obtendrn

todas

las

frecuencias: L10:L15
o

Puede usar la biblioteca de funciones para insertar la funcin, siga la

secuencia mostrada en la siguiente figura:

o Ahora, debe ingresar los argumentos solicitados, para obtener el resultado:

Importante:
INTERPRETACIN! En el cuadro Datos (B5:I19) contiene el rango de la
muestra, y Grupos (K10:K15), son los datos que ingreso a partir de los
lmites y la amplitud, llamados tambin intervalos de clase.

Pgina |7

o La funcin FRECUENCIA por ser una funcin de tipo matricial, los

resultados se mostrarn presionando las teclas: CRTL+SHIFT+ENTER:

Calculando las frecuencias relativas:

Estas frecuencias se obtienen con la frmula: =frecuencia Absoluta/Total
muestra.
Por ello debe calcular el total de la muestra con la funcin SUMA:
o

Haga clic en la celda L16

Escriba la funcin: =SUMA(L10:L15) y pulsa la tecla Enter.

Ahora ya puede calcular la frecuencia relativa:

o

Haga clic en la celda M10

Escriba la frmula: =L10/$L$16 y pulsa la tecla Enter.

Copie la frmula hacia abajo y ya tendrs la informacin completa.

Pgina |8

NOTA:
Observe que la frecuencia absoluta es cuantitativa y la frecuencia relativa es
porcentual.

2 FUNCIONES DE TEXTO
a) FUNCIN IZQUIERDA
Extrae el nmero indicado de caracteres, colocados a la izquierda o al principio de la
cadena de texto.
Sintaxis:
=IZQUIERDA(Texto, Nm_de_caracteres)

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

Nm_de_caracteres: Especifica el nmero de caracteres que se desea extraer con

la funcin IZQUIERDA.

NOTAS:

Nm_de_caracteres debe ser mayor o igual a cero.

Si Nm_de_caracteres es mayor que la longitud del texto, IZQUIERDA devolver

todo el texto.

Si Nm_de_caracteres se omite, se calcular como 1.

Pgina |9

b) FUNCIN DERECHA
Extrae el nmero indicado de caracteres, colocados al final de la cadena de texto.
Sintaxis:
=DERECHA(Texto, Nm_de_caracteres)

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

Nm_de_caracteres: Especifica el nmero de caracteres que se desea extraer con

la funcin DERECHA.

NOTAS:

Nm_de_caracteres debe ser mayor o igual a cero.

Si Nm_de_caracteres es mayor que la longitud del texto, DERECHA devolver

todo el texto.

Si Nm_de_caracteres se omite, se calcular como 1.

Ejercicio: Uso de las funciones IZQUIERDA y DERECHA

Los datos de referencia tienen un delimitador que es el punto y coma. Se desea separar
todas las palabras que estn antes y despus de dicho delimitador.
En el primer caso, utiliza la funcin IZQUIERDA para extraer los 15 primeros caracteres
(Amrica del Sur) del dato en referencia.
En el segundo caso, utiliza la funcin DERECHA para extraer los 4 ltimos caracteres
(Per) del dato en referencia.

P g i n a | 10

NOTA:

Las funciones ingresadas no deben ser copiadas verticalmente, ya que la cantidad

de caracteres a extraer tanto de la izquierda como de la derecha, son diferentes.

Cada caso es particular.

c) FUNCIN MED: Obtiene el nmero de caracteres de una cadena de texto a partir de

una posicin indicada.
Sintaxis:
=MED(Texto, Posicin_inicial,Nm_de_caracteres)

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

Posicin_inicial: Posicin del primer carcter que se desea extraer del texto. La
Posicin_inicial del primer carcter de texto es 1, y as sucesivamente.

Nm_de_caracteres: Especfica el nmero de caracteres que se desea que MED

devuelva del argumento texto.

A considerar:
Si posicin_inicial es mayor que la longitud de texto, MED devuelve (texto
vaco).
Si posicin_inicial es menor que la longitud de texto, pero posicin_inicial ms
nm_de_caracteres excede la longitud de texto, MED devuelve los caracteres
hasta el final de texto.
Si posicin_inicial es menor que 1, MED devuelve el valor de error #VALOR!
Si nm_de_caracteres es negativo, MED devuelve el valor de error #VALOR!
Ejercicio: Del texto que se muestra en la celda C4, se desea extraer la palabra Sur
Debes tener en cuenta la posicin de
la letra S, si cuentas, su posicin es
12. Desde esa posicin extrae 3
caracteres, que es la cantidad de
caracteres de la palabra Sur.
Utiliza la funcin MED como se indica en la imagen.

P g i n a | 11

d) FUNCIN CONCATENAR
Une varios elementos de texto en uno solo.
Sintaxis:
=CONCATENAR (texto1,texto2,)

texto1, texto2,... son de 1 a 255 elementos de texto que sern unidos en un

elemento de texto nico. Los elementos de texto pueden ser cadenas de texto,
nmeros o referencias a celdas nicas.

Ejercicio:
Se desea mostrar en una sola celda tanto los apellidos y los nombres:

Utilice

la

funcin

CONCATENAR,

donde el texto1 son los apellidos

(celda A2), el texto2 es un espacio
en blanco ( ) y el texto3 son los
nombres (celda B2).
Copie la funcin verticalmente.
Si desea separar los apellidos de los nombres, no por un espacio en blanco sino por una
coma, entonces el texto2 ser la coma ms un espacio en blanco. As:

Despus de copiar el resultado ser el siguiente:

P g i n a | 12

Ejercicio: Existe un una tienda por secciones en la cual se necesita realizar una
organizacin de los datos de tal manera que est asociado un cdigo con una seccin en
especfica, sin embargo los cdigos que se tienen estn con caracteres adicionales que no
se requieren, por lo tanto se necesita hacer la extraccin del cdigo antes para que sea
combinado con el nombre de la seccin.

Inicialmente usars la funcin MED y luego emplears la funcin de Texto CONCATENAR

para hacer la combinacin de la seccin con el cdigo.
El uso de la funcin MED simplifica el trabajo en alto grado ya que optimizamos el tiempo
que normalmente invertimos en los procedimientos manuales y adems que puede ser
anidada con una gran diversidad de funciones con lo que se crean frmulas nuevas para
solucionar problemas ms complejos.
e) FUNCIN HALLAR: Devuelve el nmero de caracteres en el cual se encuentra un
carcter en particular o cadena de texto, leyendo de izquierda a derecha (no
diferencia entre maysculas ni minsculas).
Sintaxis:
=HALLAR(Texto_buscado,Dentro_del_texto,Nm_inicial)

Texto_buscado: es el texto que desea encontrar.

Dentro_del_texto: es el texto en que se desea encontrar el Texto_buscado.

P g i n a | 13

Nm_inicial: es, contando desde la izquierda, el nmero del carcter en

Dentro_del_texto desde donde se desea iniciar la bsqueda. Si se omite, se usa 1.

Por ejemplo, para buscar la ubicacin de la letra "p" en la palabra "impresora",

puede usar la siguiente funcin:
=HALLAR("p","impresora")
Esta funcin devuelve 3 porque "p" es el tercer carcter en la palabra "impresora."
Adems, puede buscar por palabras dentro de otras palabras. Por ejemplo, la
funcin
=HALLAR("medio";"promedio")
devuelve 4, porque la palabra "medio" comienza en el cuarto carcter de la palabra
"promedio".
Ejercicio: Uso de la funcin HALLAR.
Utilice la funcin HALLAR para encontrar la posicin del carcter indicado en el dato de
referencia.

En este caso se puede omitir la posicin inicial, as

=HALLAR(C3,B3)

Importante:
IMPORTANTE! El carcter asterisco * es un carcter especial que representa un
carcter o conjunto de caracteres cualquiera, es por eso que el resultado en la
columna posicin encontrada es 1.

P g i n a | 14

f) FUNCIN LARGO: Devuelve el nmero de una cadena de texto

Sintaxis:
=LARGO(Texto)

Texto: es el texto cuya longitud se desea conocer. Los espacios cuentan como
caracteres.

En el siguiente ejemplo, se ha determinado el nmero de caracteres que tiene el dato

de referencia.

Ejercicio: Uso de funciones combinadas

Se desea separar todas las palabras que estn a lado izquierdo y derecho del delimitador
punto y coma.
Para este caso, debe usar una combinacin de las funciones de texto, as:

Al utilizar la funcin IZQUIERDA, el problema es determinar en forma general la

cantidad de caracteres a extraer hasta antes del punto y coma. Para ello, usa la
funcin HALLAR para determinar la posicin del punto y coma, luego resta menos
1, para determinar la cantidad exacta de caracteres a extraer desde el inicio.

P g i n a | 15

Al utilizar la funcin DERECHA, el problema es determinar en forma general la

cantidad de caracteres a extraer despus del punto y coma. Para ello, usa la
funcin LARGO para determinar la cantidad de caracteres de todo el texto, luego
usa la funcin HALLAR para determinar la posicin del punto y coma, que restars
de la longitud total.

Ejercicio: Una Empresa desea generar el cdigo de su nuevo grupo de empleados,

tomando en cuenta la siguiente estructura:
CODIGO = Primera letra del apellido + Dos primeras letras del nombre + la categora +
Ao Ingreso

Procedimiento:
1.

Seleccione la celda B3

2.

Digite lo siguiente:
=CONCATENAR(IZQUIERDA(C3,1),MED(C3,HALLAR(" ",C3)+1,2),D3,E3)

3.

Presione Enter

4.

Copie la frmula a las celdas siguientes y observa el resultado.

P g i n a | 16

g) FUNCIN MINUSC: Convierte todas las letras de un texto en minsculas.

Sintaxis:
=MINUSC(Texto)
Texto

es el texto que desea convertir en minsculas. Los caracteres en Texto que

no sean letras no cambiarn.

h) FUNCIN MAYUSC: Convierte todas las letras de un texto en maysculas.

Sintaxis:
=MAYUSC(Texto)
Texto

es el texto que desea convertir en maysculas. Los caracteres en Texto

que no sean letras no cambiarn.

i) FUNCIN NOMPROPIO: Pasa en maysculas la primera letra de todas las palabras

de la cadena de texto y el resto de los caracteres en minsculas. As como cualquiera
de las otras letras que se encuentren despus de un carcter que no se una letra
(espacios, nmeros, signos de puntuacin, etc.).
Sintaxis:
=NOMPROPIO(Texto)
Ejercicio: Uso de las funciones MINUSC, MAYUSC y NOMPROPIO

P g i n a | 17

3 FORMATO CONDICIONAL AVANZADO

El formato condicional avanzado utiliza una frmula como condicionante en la aplicacin
de los formatos seleccionados.
A continuacin mostramos una aplicacin en donde la condicin del formato condicional
es una frmula:
Ejercicio: Al ingresar un cdigo en la celda C9, automticamente debe resaltarse las
celdas con relleno de color amarillo (cdigo, artculo y stock) del artculo correspondiente
en la tabla.

Sigue los pasos siguientes:

1. Seleccione el rango de celdas que desea formatear, en este caso A2:A7
2. En la ficha Inicio, en el grupo Estilo, haz clic en la flecha junto a Formato
condicional y, a continuacin, haga clic en Nueva regla

En el cuadro Selecciona un tipo de regla, haz clic en la opcin Utilice una

frmula que determine las celdas para aplicar formato

P g i n a | 18

En el cuadro Dar formato a los valores donde esta frmula sea verdadera,
introduce la siguiente frmula:
=A2=$C$9

Haga clic en el botn Formato y luego selecciona la ficha Relleno y elije el color
amarillo. Finaliza haciendo clic en el botn Aceptar.

Repite los mismos pasos para los rangos B2:B7 y C2:C7, la frmula en el formato
condicional es la misma.
El resultado ser el siguiente:

Ejercicio: Resaltar con color verde, los nombres de los vendedores cuyas ventas son
mayores a 5000 soles.

Siga los pasos siguientes:

1. Seleccione el rango de celdas que desea formatear, en este caso A2:A8
2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato
condicional y, a continuacin, haga clic en Nueva regla

P g i n a | 19

En el cuadro, Seleccione un tipo de regla, haga clic en la opcin Utilice una

frmula que determine las celdas para aplicar formato

En el cuadro Dar formato a los valores donde esta frmula sea verdadera,
introduzca la siguiente frmula:
=B2>5000

Haga clic en el botn Formato y luego selecciona la ficha Relleno y elije el color
verde. Finaliza haciendo clic en el botn Aceptar.

El resultado ser el siguiente:

P g i n a | 20

PROTECCIN DE ELEMENTOS
Los resultados obtenidos en una hoja de clculo son producto de simples o complicados
procesos que exigen ser protegidos de modificaciones o vulneraciones accidentadas o
mal intencionadas.
Existen distintos niveles de proteccin dentro de un Libro de Excel disponibles segn las
necesidades de los usuarios.
A Nivel de hoja:
Permite proteger los diversos elementos, atributos y recursos que componen una hoja de
clculo detallados en una lista. Protege el contenido de todas las celdas configuradas
para acatar una proteccin de la Hoja.
Procedimiento:
1. Clic derecho en la etiqueta de la Hoja
2. Clic en la opcin Proteger Hoja
3. Activar las casillas que PERMITAN al usuario
liberar de la proteccin el elemento, atributo o
recurso que necesita.

P g i n a | 21

4. Haga clic en el botn Aceptar y confirmar la clave

A Nivel de celdas:
Permite proteger un rango de celdas.
Ejercicio: Proteger las celdas que contienen frmulas y adems hacer que no se
muestren.

Procedimiento
1. Seleccione toda la hoja de clculo
2. De la ficha Inicio, del comando Formato, elige la opcin Formatos de celdas
3. Luego, haga clic en la Ficha Proteger y desactiva la casilla Bloqueada.

P g i n a | 22

4. Ocultando la frmula:
a. Seleccione las celdas que contienen frmulas, en nuestro caso C2:C8
b. De la ficha Inicio, comando Formato, elija la opcin Formatos de celdas
c. Luego, Seleccione la ficha Proteger y marque las casillas Bloqueada y
Oculta

5. Ahora, Proteja la hoja: Inicio Formato

Proteger hoja
6. Verifique la proteccin de las celdas.

P g i n a | 23

PRCTICA GUIADA
A 40 estudiantes se les pidi que estimen el nmero de horas que habran dedicado a
estudiar la semana pasada (tanto en clase como fuera de ella), obtenindose los
siguientes resultados:
36

30

47

60

32

35

40

50

54

35

45

52

48

58

60

38

32

35

56

48

30

55

49

39

58

50

65

35

56

47

37

56

58

50

47

58

55

39

58

45

Se pide hallar las frecuencias relativas y absolutas, si se sabe que existe un lmite
mnimo de 35 para el primer rango y un mximo de 65 para el ltimo rango, con una
amplitud de 6.
Solucin:
a) Ingrese los siguientes datos en la hoja de clculo:

b) Seleccione el rango C13:C18 (donde se van obtener las frecuencias absolutas)

P g i n a | 24

c) Escriba la funcin =FRECUENCIA seleccionando el rango de datos (A5:H9) y grupos

(B13:B18), como se muestra a continuacin:

d) Para obtener las frecuencias absolutas, presiona CTRL + SHIFT + ENTER

P g i n a | 25

e) Ahora, para obtener las frecuencias relativas, primero tiene que hallar el total de las
frecuencias absolutas. As:

En la celda C19, ingrese la funcin SUMA usando el botn Autosuma.

Importante:
IMPORTANTE! El total de las frecuencias absolutas debe coincidir con la cantidad de
datos de la muestra, para este caso, debe ser 40.

f)

Luego, en la celda D13, ingrese la frmula =C13/$C$19 y luego copie hasta la celda
D18.

Importante:
IMPORTANTE! Recuerde, que para obtener las frecuencias relativas tiene que dividir
cada frecuencia absoluta entre el total de datos.

P g i n a | 26

Despus de copiar los datos, el resultado es:

g) Aplica formato estilo porcentual para mostrar las frecuencias relativas en forma de
porcentajes.

h) Finalmente, hallar la suma de las frecuencias relativas y centra los datos. El

resultado final ser:

P g i n a | 27

AUTOEVALUACIN:
Ahora demuestra lo aprendido:
1. Responda V (Verdadero) o F (Falso), marca con X:

a. La funcin SIFECHA determina la fecha actual

( )

( )

b. La funcin FRECUENCIA es de tipo matricial

( )

( )

( )

( )

( )

( )

c. La funcin MED extrae un nmero especificado de

caracteres desde el medio de una cadena de texto
d. La funcin IZQUIERDA extrae un nmero especificado de
caracteres desde el inicio de una cadena de texto

2. Es una funcin que extrae un nmero especificado de a partir de una

posicin indicada:
a. IZQUIERDA
b. DERECHA
c. MED
d. HALLAR
3. Es una funcin que permite determinar la cantidad de caracteres que
contiene una cadena de texto:
a. HALLAR
b. LARGO
c. MED
d. N.A.

Bibliografa
Dodge, M. (2007). El Libro de Microsoft Excel 2007. Espaa: Editorial ANAYA MULTIMEDIA.
Walkenbach, J. (2008). La biblia de Excel 2007. Espaa: Editorial AMAYA MULTIMEDIA.

P g i n a | 28