ExcelCastellano PDF
ExcelCastellano PDF
ExcelCastellano PDF
ÍNDICE DE CONTENIDOS
EDICION .................................................................................................................... 5
Buscar y reemplazar datos .................................................................................................................5
Selección especial..............................................................................................................................7
Pegado .............................................................................................................................................9
Pegado especial ................................................................................................................................9
Panel Portapapeles .......................................................................................................................... 11
FÓRMULAS ............................................................................................................... 12
Operadores:.................................................................................................................................... 12
Operadores Aritméticos .......................................................................................................................... 12
Operadores Relacionales o de Comparación .......................................................................................... 12
Operadores de Texto ............................................................................................................................... 12
Operadores de Referencia ....................................................................................................................... 12
Precedencia de operadores ..................................................................................................................... 12
Referencias: concepto y tipos ........................................................................................................... 13
Referencias relativas ............................................................................................................................... 13
Referencias absolutas .............................................................................................................................. 13
Referencias mixtas ................................................................................................................................... 14
Modo de especificar o cambiar las referencias a una celda. ................................................................. 14
Modo de extender una fórmula ......................................................................................................... 14
Ejemplos de tipos de referencias....................................................................................................... 15
Referencias relativas ............................................................................................................................... 15
Referencias absolutas .............................................................................................................................. 15
Referencias mixtas ................................................................................................................................... 16
Nombres......................................................................................................................................... 17
Definir nombres ....................................................................................................................................... 17
Dar nombres a celdas o rangos ............................................................................................................... 17
Cuadro de nombres: ................................................................................................................................ 18
Utilizar nombres: ..................................................................................................................................... 18
Gestionar nombres: ................................................................................................................................. 19
Dar nombres a constantes y fórmulas internas ...................................................................................... 19
FORMATO ................................................................................................................ 20
Formatos predefinidos ..................................................................................................................... 20
Formatos personalizados .................................................................................................................. 21
Formato condicional ........................................................................................................................ 23
EDICION
Buscar y reemplazar datos
Se pueden localizar datos en una hoja de Excel o en todas las hojas de un cierto libro mediante el comando
Buscar o Reemplazar situados en la ficha Inicio, grupo Modificar, opción Buscar y seleccionar. Al hacer clic
en cualquiera de ellos aparece el cuadro de dialogo Buscar y Reemplazar siguiente.
Dicho cuadro nos permite también reemplazar lo buscado por nuevos valores, utilizando para ello la ficha
Reemplazar.
Se puede buscar por contenido, es decir escribiendo el texto, número, o fecha a buscar en el cuadro de
texto Buscar, se puede buscar según el formato de los datos o ambas posibilidades combinadas.
Al hacer la búsqueda se puede avanzar de una ocurrencia a otra con el botón “Buscar siguiente” o bien
hacer que Excel confeccione una lista con todas las ocurrencias, para lo cual clicaremos el botón “Buscar
todos”.
En las fichas Buscar y Reemplazar podemos buscar según el tipo de datos (valores, fórmulas, comentarios),
teniendo en cuenta el ámbito de búsqueda y reemplazo (a nivel hoja o todos el libro)y diversas opciones que
se resumen en la tabla siguiente:
Tabla: Controles y funcionalidad asociada el cuadro de dialogo Buscar y Reemplazar
Control Función
Campo Buscar Contiene el valor que se quiere buscar o reemplazar.
Botón Buscar todos Selecciona cada celda que contiene el valor del campo Buscar.
Botón Buscar siguiente Selecciona la siguiente celda que contiene el valor del campo Buscar.
Campo Reemplazar con Contiene el valor para sobrescribir el valor del campo Buscar.
Botón Reemplazar todos Reemplaza cada ocurrencia del valor del campo Buscar con el valor del
campo Reemplazar.
Botón Reemplazar Reemplaza la siguiente ocurrencia del valor del campo Buscar y marca o
selecciona la siguiente celda que contiene dicho valor.
Botón Opciones Expande el cuadro de diálogo Buscar y reemplazar para mostrar diversas
posibilidades adicionales en la búsqueda y reemplazo.
Botón Formato Despliega el cuadro de diálogo Buscar Formato, que se puede utilizar para
especificar el formato de los valores a buscar o a reemplazar
Cuadro de lista Dentro de Permite seleccionar el ámbito de búsqueda. Se puede buscar en la hoja de
Cuadro de lista Buscar Permite seleccionar el orden o dirección de búsqueda: Se puede buscar por
columnas o filas.
Cuadro de lista Buscar Sirve para definir el tipo de dato o búsqueda a realizar. Permite seleccionar si
dentro de. busca dentro del contenido de fórmulas, valores o comentarios de las celdas
seleccionadas.
Casilla de verificación Busca todas aquellas celdas cuyo contenido sea el mismo valor que en el
Coincidir con el contenido cuadro Buscar.
de toda la celda
Selección especial
En muy diversas circunstancias es necesario seleccionar todas las celdas dentro de un rango seleccionado
o que tengan una o varias características comunes. Para ello Excel nos facilita el comando ·”Ir a Especial”.
Para activarlo debemos seleccionar la ficha Inicio, grupo Modificar, opción Buscar y seleccionar, elemento
“Ir a Especial..” o bien pulsando la tecla F5 y el dialogo “Ir A” clicando sobre el botón “Especial…”. En ambos
casos aparece el cuadro de dialogo “Ir a Especial”:
Previo al uso de cualquiera de las opciones del cuadro de dialogo anterior, se debe definir el alcance o ám-
bito de la selección a efectuar. Existen dos posibilidades:
Ámbito a nivel de hoja entera: Es el existente por defecto cuando solo tenemos seleccionada una
celda cualquiera.
Ámbito a nivel de un cierto rango de la hoja: Solo buscara en las celdas del rango seleccionado. El
rango puede ser continuo o discontinuo.
Nota: El ámbito de actuación de este comando es solo a nivel de rango o de hoja, no de libro.
Opción Selección
Comentarios Celdas que contienen comentarios.
Constantes Celdas que contienen constantes, es decir valores que no varían, ya
sean texto, números, fechas, valores lógicos, etc. Se puede seleccio-
nar según tipos de contenido (números, texto etc.), marcando la co-
rrespondiente casilla de verificación que haya debajo.
Fórmulas Celdas que contienen fórmulas. Se puede seleccionar según el tipo
de resultado que produzcan (números, texto etc.), marcando la co-
rrespondiente casilla de verificación que haya debajo.
Celdas en blanco Celdas en blanco.
Región actual Selecciona el rango de celdas adyacentes a la que estemos situado
extendiéndose en todas las direcciones hasta hallar la hasta la prime-
ra fila o columna vacía en cada una de dichas direcciones, que es lo
que delimita la “región actual”.
Matriz actual Selecciona todas las celdas que compongan el rango de la matriz
actual. Esta opción solo tiene sentido si estamos situados en alguna
de las celdas de un rango-matriz, caso contrario nos aparecerá el
mensaje informativo. “No se encontraron celdas”.
Objetos Selecciona los objetos gráficos (gráficos, botones, cuadros de texto,
fotografías, etc.)
Diferencias entre filas Todas las celdas que se diferencian de la celda activa y que se hallan
en la misma fila. Por defecto la celda activa es la primera celda de la
fila o parte de fila seleccionada, pero se puede cambiar presionando
ENTRAR o TAB. Si se ha seleccionado más de una fila, la compara-
ción se hace para cada fila individual de dicha selección y la celda
que se utiliza en la comparación para cada fila adicional se encuentra
en la misma columna que la celda activa.
Diferencias entre columnas Análogo a la “Diferencia entre filas”. Selecciona todas las celdas que
se diferencian de la celda activa en una columna seleccionada. Por
defecto la celda activa es la primera celda de la columna o parte de
columna seleccionada. Para cambiar la ubicación de la celda activa,
basta presionar la tecla ENTRAR o TAB.
Celdas precedentes Celdas a las que hace referencia la fórmula de la celda activa. Se
pueden buscar sólo las directamente relacionadas, es decir aquellas
que se mencionan en la fórmula o todas, lo que incluye tanto las refe-
renciadas directamente como indirectamente a cualquier nivel.
Celdas dependientes Celdas con fórmulas que hacen referencia a la celda activa. Es el
reciproco de “Celdas precedentes”, y al igual que en dicha opción,
tenemos dos posibilidades: seleccionar solo las directamente relacio-
ProInf.net, SCP Página 8
667 551 698
www.proinf.net
Excel 2010 Avanzado
Pegado
Cuando se mueve o se copia una información, al final del proceso se debe de Pegar mediante alguna de las
diversas posibilidades existentes., por ejemplo usando el menú contextual de la celda de destino o bien la
ficha Inicio > Pegar. Una vez pegada la información, aparece al lado del área de pegado el icono de “Opcio-
nes de pegado”, que al desplegarlo haciendo clic en la flecha que hay en su lateral derecho , presentara una
serie de opciones de pegado, que son las siguientes:
Mantener formato de origen: Mantiene el formato de
las celdas que se han copiado
Mantener tema de destino: Mantiene el tema del libro
u hoja de destino, es decir el tipo de letra, esquema
de color y diseño.
Coincidir con formato de destino: Lo copiado adopta el
formato de las celdas de destino.
Formatos de números y valores: Mantiene el formato
numérico de los datos copiados.
Mantener ancho de columnas de origen: Le aplica a
las celdas destino de la copia la misma anchura de
columna que la que tenia la celda donde estaban los datos originales.
Sólo formato: Copia y aplica únicamente el formato.
Vincular celdas: Establece un vinculo entre las celdas origen copiadas y las celdas destino de la co-
pia. Si se cambia el contenido de las celdas origen, automáticamente cambiara también el de la co-
pia.
Estas opciones y algunas otras más especializadas existen en las opciones de “Pegado especial” que se
vera en el próximo apartado.
Pegado especial
Usualmente, cuando se copia y pega algo dentro de un mismo libro de Excel, o entre diferentes libros, se
hace con la opción por defecto, que pega todo, dado que es lo generalmente se quiere, no obstante existen
una serie de opciones o posibilidades de pegado más específicas y muy interesantes.
Una vez seleccionada la celda o celdas a copiar y habiéndonos desplazado a la celda o celdas de pegado,
para tener acceso a dicha opciones al cuadro de dialogo “Pegado especial” tenemos dos posibilidades:
Usar el menú contextual de la celda y hacer clic en la opción “Pegado especial…”
Seleccionar ficha Inicio, grupo Portapapeles, desplegable Pegar, opción “Pegado especial…”
ProInf.net, SCP Página 9
667 551 698
www.proinf.net
Excel 2010 Avanzado
en ambos casos nos aparecerá el cuadro de dialogo “Pegado especial” según figura adjunta:
Las opciones disponibles de “Pegado especial” se agrupan en tres bloques diferenciados: Opciones de pe-
gado en “Pegar”, opciones de pegado con operación en “Operación” y varios
Las opciones de ”Pegar” son las siguientes:
Todo: Realiza la misma función que el pegado normal, es decir pega todo en la nueva ubicación,
tanto datos como formato de la celda o celdas copiadas.
Fórmulas: Pega únicamente las fórmulas existentes en la celda copiada.
Valores: Pega solo el valor de la celda o en el caso de una fórmula, solo el resultado, no la fórmula.
Formatos: Pega solo el formato de la celda seleccionada en la celda o celdas de destino, es el equi-
valente a usar el icono “Copiar formato” existente en ficha Inicio, grupo Portapapeles.
Comentarios: Pega los comentarios asociados a las celdas seleccionadas.
Validación: Pega únicamente las reglas de validación de datos. La validación es una característica
que permite poner condiciones o restricciones respecto al tipo y rango de datos que se pueden in-
troducir en una celda o rango determinado.
Todo utilizando el tema de origen: Pega el contenido de la celda copiada usando como formato el
tema que tenga asociado el documento.
Todo excepto bordes: Pega el contenido de la celda y el formato completo de dicha celda, excepto
los bordes.
Ancho de las columnas:Cambia el ancho de las columnas de pegado de destino por el ancho de las
celdas de origen seleccionadas.
Formatos de números y fórmulas: Es una variante o ampliación de la opción “Fórmulas”, aquí ade-
más de pegar la fórmula pega también el formato numérico de las celdas seleccionadas.
Formatos de números y valores: Variante o aplicación de la opción “Valores”. Además de pegar el
valor pega todas las opciones de formato numérico que tuviera la celda de origen.
El grupo de “Operación” sirve para elegir la operación a efectuar entre los datos que hemos copiado y los
datos que puedan existir en destino, de forma que si en las celdas destino hay valores, de que lo que se
pega es el resultado de dicha operación. Las operaciones disponibles son: Ninguna, Sumar, Restar, Multi-
plicar y Dividir
Nota: Las celdas destino vacías se consideran como ceros.
ProInf.net, SCP Página 10
667 551 698
www.proinf.net
Excel 2010 Avanzado
Panel Portapapeles
Existe una herramienta denominada “Portapapeles” o “Panel Portapapeles” que facilita el trabajar con mayor
comodidad a la hora de copiar y pegar, cuando hemos de hacer varios copiados y pegados sucesivos, dado
que nos permite acumular hasta 24 ítems de cualquier tipo de información y después pegar selectivamente
los que queramos y en el orden que queramos.
Para activar dicha herramienta hemos de ir a la ficha Inicio, grupo Portapapeles y darle al cuadro lanzador el
portapapeles que esta situado en la esquina inferior derecha de dicho grupo. Al hacerlo aparecerá el panel
Portapapeles a la izquierda de la pantalla, con todos los elementos copiados hasta el momento.
Para copiar información es suficiente hacerlo de las formas habituales. Dicha información se ira acumulando
en le Portapapeles de forma secuencia, quedan la más reciente siempre arriba de todo.
Para pegar la información copiada simplemente nos posicionaremos en cada caso en la celda deseada y
haciendo clic en el trozo de información deseada este se pegara en la celda activa.
FÓRMULAS
Para crear fórmulas en una celda de Excel se debe escribir primero el signo igual (=) y después la expresión
deseada. Una expresión es una combinación de operadores, operandos, referencias a otras celdas o fun-
ciones de Excel que al ser evaluada por Excel da un cierto resultado o valor. EL valor retornado por la fór-
mula puede ser un número, una fecha, un texto, un valor lógico o un valor de error. Expresión y fórmula
son sinónimos en este contexto.
Al crear fórmulas nos encontramos con dos conceptos importantes: los operadores y su precedencia y las
referencias y sus diferentes tipos.
Operadores:
Los operadores que se pueden usar para crear una fórmula en Excel son los siguientes:
Operadores Aritméticos
+ Suma
- Diferencia
* Producto
/ División
^ Exponente
Operadores de Texto
& Concatenación
Concatena (conecta o enlaza) dos valores o cadenas de texto para producir una cadena de texto continuo.
Operadores de Referencia
Operador Definición Ejemplo
: Rango =SUMA(A1:A10)
; Unión =SUMA(A1;A10;B12)
espacio Intersección =año2007 Ventas
Precedencia de operadores
Se llama precedencia o rango a la importancia de unos operadores respecto de otros. La precedencia de-
termina como evalúa Excel una fórmula para producir el resultado final.
En una fórmula siempre se evalúan primero las operaciones cuyo operador tiene mayor precedencia. En
caso de haber dos operadores con la mima precedencia o un mismo operador repetido varias veces, se
evalúan de forma secuencial de izquierda a derecha.
Ejemplo:
Usando los mismos operandos: 3, 6, 12, 4
y 2 y operadores: (), *, /, +, y – si introduci-
mos cuatro fórmulas según la figura adjun-
ta, veremos que los resultados obtenidos
(19, 24, 11,50 y 27) son todos diferentes.
Ello es debido a la precedencia asignada a
cada operador y/o al lugar en el que apa-
rece en la fórmula.
Referencias relativas
Cuando se efectúa una copia de una celda que contiene una fórmula o función con referencias relativas, las
celdas de destino desplazan (adaptan) sus direcciones tantas filas y columnas como se haya desplazado la
celda original respecto a la celda de destino.
Son las referencias por defecto de Excel. Para ponerlas basta con escribir la letra de la columna, seguida
del número de fila de la celda a la que queremos hacer referencia. No importa mayúsculas y minúsculas,
pero no debe de haber ningún espacio entre letra y número y por supuesto no podemos usar letras o núme-
ros de celdas que no existan, por ejemplo no podemos usar la referencia a la celda XXV100 porque aunque
si existe la fila 100, no existe la columna XXV.
Las referencias relativas son las más usuales en Excel dado que muchos cálculos que se efectúan usan la
misma fórmula, solo que esta se debe aplicar en diferente fila o columna a donde se ha definido.
Referencias absolutas
La referencias absolutas se usan para que la referencia a una cierta celda no cambie independientemente
de donde se copie después la fórmula que contiene dicha referencia. Son útiles para referenciar aquellas
celdas que contienen valores constantes que se usan en múltiples lugares (p.e. el valor del I.V.A.)
Las referencias absolutas se denotan poniendo delante de la letra de la columna y del número de la fila el
símbolo del dólar “$”.
Referencias mixtas
Las referencias mixtas son aquellas que en la dirección de una celda tiene una parte relativa y otra absoluta,
por ejemplo:
$A1: Seria la una referencia absoluta a la columna A y relativa a la fila 1
A$1: Seria la una referencia relativa a la columna A y absoluta a la fila 1
Referencias relativas
En el ejemplo de la figura, para calcular el Total de ventas por producto para cada producto solo ha sido
necesario escribir la fórmula en B17 y después arrastrarla con el botón o cuadro de llenado en la misma fila
hasta E17, dado que la fórmula es idéntica en todos los casos y lo que cambia es la referencia de las celdas
a sumar.
Referencias absolutas
Se trata de calcular el Importe total a pagar a cada empleado que ha hecho horas extras, teniendo en cuen-
ta que el precio de la hora extra es igual para todos los empleados. Dicho precio se halla en la celda C2.
Para calcular correctamente el pago para cada empleado se debe multiplicar las horas que ha hecho el
empleado por el precio hora. Para lo cual basta escribir la fórmula inicial en D5 arrastrarla hasta D8, pero
teniendo en cuenta que una de las referencias, la del precio hora, debe de estar fijada como absoluta, para
que no varié cuando sea arrastrada la fórmula. El detalle se puede ver en la siguiente figura
Referencias mixtas
Las referencias mixtas se aplican a aquellos problemas en los que intervienen dos elementos variables.
Están relacionadas siempre con estructuras de cálculo en forma de tabla. Por ejemplo, supongamos que
quisiéramos calcular la tabla de multiplicar del número 1 al número 10, empezando el primer cálculo en la
celda C5, según el desarrollo mostrado en la siguiente ilustración:
Para hacerla bastaría con situarse en dicha celda C5, escribir la fórmula: =$B5 * C$4 y extenderla arras-
trando el cuadro de llenado, primero horizontalmente desde C5 hasta L5 y después, manteniendo la selec-
ción de C5 a L5 arrastrarla hacia abajo hasta la fila 10. Dado que tanto los valores de la fila de referencia
(fila 4) como los de la columna (columna B) variaran al desplazarnos, las referencias para mantener esta
variación deben de ser forzosamente mixtas.
Nombres
Los nombres es un mecanismo que facilita enormemente la claridad y compresión de fórmulas y funciones
en cuyo enunciado o argumentos se hace referencia a celdas o rangos de celdas.
Al referirse a una celda o rango en una fórmula se usa por defecto el lenguaje estándar de Excel, que con-
siste en escribir la letra de la columna y el número de fila de la celda que queremos referenciar o si se trata
de un rango, escribir la celda inicia de la misma forma, el operador rango (:) y la celda final del rango. Hay
otra forma alternativa para hacer esto, y es asignar nombres a celdas individuales o rangos y después utili-
zar estos nombres en las fórmulas, en lugar del lenguaje referencial de Excel.
Se trata por tanto de crear “alias” bautizando las celdas con nombres propios que tengan un sentido práctico
y funcional y así prescindir del lenguaje referencial y más críptico que nos da Excel por defecto. Crear nom-
bres para celdas o rangos es totalmente opcional, pero muy práctico y recomendable incluso si no se crean
fórmulas de gran complejidad.
Además de nombres a rangos y celdas, se pueden dar nombres a constantes y fórmulas que no se hallen
situadas en ninguna celda, sino que son internas al libro u hoja.
Los nombres, además de para hacer más inteligibles las fórmulas y funciones, si son de celdas o rangos
sirven también para desplazarnos de forma rápida a la celda o rango que definen, para ello basta con selec-
cionar el deseado en el “Cuadro de nombres”.
Definir nombres
Se pueden dar nombres a celdas individuales y a rangos (continuos o discontinuos).
Ámbito o visibilidad:
Al definir un nombre tenemos que tener en cuenta el ámbito (visibilidad o alcance), es decir desde que par-
tes del libro se puede ver y usar. Excel soporta dos tipos de alcance diferente para los nombres:
Ámbito global o de libro: el nombre es visible y utilizable en todas las hojas del libro donde se ha de-
finido.
Ámbito local o de hoja: el nombre solo esta disponible en la hoja de cálculo en la cual ha sido defini-
do.
El ámbito de un nombre no tiene porque coincidir con la hoja a la que pertenece el rango, se puede tener un
nombre con ámbito en la Hoja2, pero que se refiera a las celdas de la Hoja5.
El ámbito para constantes o fórmulas internas es siempre global.
Reglas para nombres:
Deben empezar siempre por una letra
No pueden tener espacios. Si se desea poner un nombre compuesto, se puede usar el guion bajo
como separador o poner la primera letra de cada palabra en mayúscula. Por ejemplo: Importe_total
o ImporteTotal.
Pueden tener hasta 255 caracteres
No pueden usarse nombres que tengan una denominación que coincida con la referencia a una cel-
da. Por ejemplo A123, o IVA16 no son válidos.
Para los nombres Excel no distingue entre mayúsculas y minúsculas
No se pueden tener dos nombre iguales dentro del mismo ámbito. Si en ámbitos diferentes o libros
diferentes.
Si escribimos un nombre no permitido, el propio Excel nos avisa del error.
Una celda o rango puede tener varios nombres o alias, aunque no es muy usual dar más de un nombre a un
mismo rango.
Cuadro de nombres:
Esta situado en la parte izquierda de la barra de fórmulas. En su parte derecha tiene una flecha que al cli-
carla nos muestra todos los nombres a los que se puede acceder desde la hoja en la que estemos en ese
momento. Muestra tanto los nombres que tienen ámbito de libro, como aquellos cuyo ámbito es solo la hoja
donde estamos situados. En el cuadro de nombre no aparecen los nombres dados a constantes o fórmulas,
para poder ver estos últimos es necesario usar la herramienta "Administrador de nombres".
Seleccionando un nombre cualquiera, Excel nos desplaza y marca el rango que tiene dicho nombre.
sirven también para desplazarnos de forma rápida a la celda o rango que definen, para ello basta con selec-
cionar el deseado en el “Cuadro de nombres”.
Utilizar nombres:
Para utilizar un nombre en una fórmula o función basta con escribirlo. En la mayoría de los casos con la
nueva funcionalidad de Excel de ayuda automática en la sintaxis, en cuanto hayamos escrito las primeras
silabas del nombre aparecerá el mismo en una lista desplegable, basta entonces seleccionarlo con el ratón
o darle a la tecla TAB para que Excel termine de escribirlo completo. Caso de no acordarnos de cómo co-
mienza el nombre que hemos definido, tenemos la opción de pulsar la tecla F3 que hará aparecer el cuadro
de dialogo “Pegar nombre”, donde esta la lista de todos los nombres existentes, basta entonces buscar y
seleccionar el deseado y darle al botón Aceptar.
Gestionar nombres:
Para controlar que nombres existen en un libro, a que celda o celdas se refieren, conocer su ámbito, o si
son nombres de constantes o fórmulas disponemos de dos herramientas: "Pegar nombres" y "Administrador
de nombres"
Pegar nombres: Para disponer de un listado de todos los nombres definidos en el libro en el que estamos,
basta con pulsar la tecla F3 y elegir el botón "Pegar lista".
Excel nos creara una lista de dos columnas, a partir de la celda en la que estemos situados, hacia abajo y a
derecha. En la primera columna nos da en orden alfabético el nombre de todos nombres definidos, sean del
tipo que sean, y en la segunda columna la referencia del rango o fórmula que representan.
Administrador de nombres :ficha Fórmulas, grupo “Nombres definidos”, “Administrador de nombres”. Esta
es una herramienta de gestión o auditoria de nombres más potente, dado que nos permite editar los existen-
tes, borrarlos, ordenarlos por alguna de sus características, saber su ámbito, filtrarlos por diversos criterios,
así como crear nuevos nombres.
FORMATO
Formatear datos implica darles una apariencia distinta a la que tienen por defecto, no se cambian los datos
en si, solo su aspecto. Dar formato sirve no solo para que los datos tengan una mejor apariencia o presen-
tación y resulten más atractivos, sino que en muchos casos sirve también para facilitar mejor su compren-
sión o lectura o para marcar o analizar de una forma más rápida dichos datos.
Excel nos facilita una serie de utilidades para dar formato a los datos y resultados de fórmulas de una forma
fácil, rápida y fiable
La mayoría de las opciones directas y esenciales de formato se encuentra en la ficha Inicio, grupos Fuente,
Alineación, Número y Estilos y Celdas, según la figura siguiente:
Formatos predefinidos
Para la mayoría de tipos de datos existen una serie de formatos de uso común, ya predefinidos. Para acce-
der a ellos basta seleccionar primero las celdas a las que se desea aplicar formato y después en el grupo
Número, se halla un desplegable donde están definidos los formatos de datos más usuales, y caso de no
hallar el deseado, al final del desplegable aparece la opción "Más formatos de número…" que lanza el cua-
dro de Dialogo "Formato de celdas", donde se halla entre otras la ficha "Número" en la que están el resto de
los formatos predefinidos que Excel nos facilita.
Los formatos aparecen agrupados en función de las diversas categorías de formatos que podemos aplicar-
les, a los datos, por ejemplo si se tratase de aplicar a un grupo de celdas un cierto formato de fecha, elegi-
ríamos la categoría Fecha, donde aparecen todos los formatos disponibles. En el momento que seleccio-
nemos cualquiera de ellos, podremos ver una pre visualización de cómo quedarían los datos en la parte
superior del cuadro de dialogo, en el cuadro Muestra. De esta forma podremos decidir que formato nos in-
teresa antes de aplicarlo definitivamente.
Formatos personalizados
Excel nos permite una serie de posibilidades para que caso de no hallar un formato predefinido que se
adapte a nuestras necesidades, poder crearlo. Para ello en el dialogo anterior "Formato de celdas", ficha
“Número”, existe la opción “Personalizada”, que permite crear formatos específicos que no existan en nin-
guna de las categorías predefinidas.
Los formatos personalizados pueden constar de hasta cuatro secciones diferentes separadas por punto y
como. La primera sección define el formato para números positivos, la segunda para números negativos, la
tercera para valores 0 y la cuarta y última, para el dato que sea texto.
Para la creación de formatos personalizados se pueden emplear los siguientes símbolos o códigos:
Carácter Función
0 Especifica o reserva lugar para un número por cada cero que se ponga. Si al introducir la
cifra seleccionada, tiene menos dígitos de los indicados en el formato, en los lugares donde
no exista número se mostraran ceros.
# Especifica una cifra opcional por cada símbolo que se ponga. Es decir si el número introduci-
do tiene un valor en la posición del símbolo lo mostrara y si no lo hay, no mostrara nada.
? Agrega los espacios de los ceros sin valor a cada lado del separador para alinear los decima-
les. También puede utilizarse este símbolo para las fracciones que tengan un número de
dígitos variable.
. Especifica el lugar de aparición del punto separador de millares.
, Especifica el lugar de aparición de la coma separadora de decimales.
* Especifica que el carácter que se ponga después del símbolo sea repetido en todas las posi-
ciones de la celda hasta rellenar el espacio no utilizado de dicha celda.
_ El guion bajo representa un espacio en blanco. Se suele usar para alinear cifras numéricas
con decimales.
@ Indicador de texto. Presenta el valor de la celda cuando sea texto.
\ Muestra el carácter que siga a la barra como un literal y no con el significado especial que
pudiera tener. Por ejemplo \E mostraría una E no lo tomaría como símbolo de exponencial.
E Muestra un número en notación científica o exponencial en base diez, diez elevado a …
"€/h" Permiten introducir texto literal en el formato. Se usa usualmente en números.
[color] Se puede hacer que cualquier sección del formato tome uno de los colores básicos existente,
para ello basta poner el nombre del color entre corchetes cuadrados ([]).Los colores que po-
demos utilizar tienen los siguientes nombres: Negro, Azul, Verde, Fucsia, Rojo, Blanco, Ama-
rillo y Aguamarina.
<,>,= Permiten escribir formatos condicionales usando los operadores relacionales entre corchetes
cuadrados ([])
Ejemplos
Formato para texto:
1- Formato para un número de Teléfono: ## ### ###-##-## (# =no obligatorio)
Si escribo 9876543211 me presentará: 987 654-32-11
2- Formato para un número de Teléfono: 00 000 000 00 00 (0 = obligatorio)
Si escribo 9876543211 me presentará: 00 987 654 3211
2- El mismo ejemplo anterior, pero incluyendo formato también para la sección de los números negati-
vos.
#.##0.0; "NEGATIVO"
Así si escribo 1000,456 presentara 1.000,5.
Si escribo -45 presentara "NEGATIVO"
A los números 0 se les aplica el mismo formato que a los positivos, pues no se ha definido formato
específico para la tercera sección.
3- Mismo ejemplo, pero incluyendo formato para las tres primeras secciones.
#.##0.0; "NEGATIVO"; "CERO"
5- Mismo ejemplo anterior, al que se le añade color a la sección segunda , tercera y cuarta.
#.## "h";[rojo]"NEGATIVO";[azul]"CERO"; [Rojo]"ERROR! No es un número"
Formato condicional
Excel permite resaltar el contenido de una celda aplicando diferentes formatos en función de que el valor
que exista dentro de la celda cumpla uno o más criterios específicos o en función de la evaluación de una
cierta fórmula que esta relacionada de una forma lógica con dicha celda. Por ejemplo es posible marcar con
un color de fuente o fondo todas las celdas de un rango cuyo valor sea superior a una cantidad y marcar el
resto con otro color diferente.
El formato condicional es una herramienta de análisis visual de datos, dado que nos permite resaltar la in-
formación según nos interese y después localizarla de un vistazo. Es una herramienta que refuerza o com-
plementa al formato personalizado y se vuelve especialmente útil cuando la cantidad de datos a evaluar es
elevada y por tanto es difícil localizar rápidamente los datos de nuestro interés.
Se puede aplicar formato condicional tanto a celdas que contengan constantes como a celdas que conten-
gan fórmulas. En este último caso Excel primero evalúa la fórmula y después aplica el formato al resultado
obtenido teniendo en cuenta las condiciones o criterios especificados para la celda en cuestión.
El formato condicional permite aplicar a una celda o grupo de celdas diferente color o estilo de fuente, bor-
des, color de fondo, tramas, gradación de colores en función del valor o conjuntos de iconos semánticos
seleccionados de una colección existente en Excel.
Se puede establecer un número ilimitado de condiciones de formato condicional para cada celda o rango de
celdas.
Si queremos aplicar algún tipo de regla no existente dentro de estas opciones directas deberemos seleccio-
nar o bien la opción "Nueva regla…" o bien "Administrar reglas…", botón "Nueva regla", ambas hacen apa-
recer el cuadro de dialogo "Nueva regla de formato" donde podemos definir la regla deseada.
En cuadro de dialogo "Nueva regla de formato" que aparece en ambos casos, existen seis posibilidades o
tipos de reglas diferentes (ver figura Dialogo "Nueva regla de formato"), que se pueden sintetizar en lo si-
guiente:
A una celda o grupo de celdas se les puede aplicar formato condicional según:
El valor de la celda.
El contenido de la celda. Es decir el tipo de dato de la celda
El resultado de evaluar una cierta fórmula o función relacionada de forma lógica con dicha celda.
FUNCIONES
Una función es una fórmula programada o abreviada, que efectúa una o varias operaciones simultáneamen-
te, devolviendo un resultado determinado. Las funciones es un mecanismo para facilitarnos el cálculo de la
mayor parte de operaciones, sean del tipo que sean.
Las funciones pueden utilizarse en una fórmula o expresión de forma independiente o combinadas con ope-
radores y operandos diversos. También pueden formar parte de otra función (funciones anidadas) o usarse
para la creación de formatos condicionales, reglas de validación, campos calculados o funciones internas
personalizadas definidas por el usuario de Excel.
Tipos de argumentos
Los argumentos de una función pueden ser obligatorios y opcionales. Los obligatorios están marcados en
negrita, tanto en la ayuda automática que va apareciendo a medida que vamos escribiendo la función, como
si usamos el asistente “Insertar función”, los no obligatorios están en letra normal y aparecen entre corche-
tes en la ayuda automática.
Una función pude admitir un número fijo de argumentos (incluso ninguno) o un número variable, depende
del tipo de función. En cada caso la ayuda automática o el asistente “Insertar función” nos aclaran la cues-
tión. En el caso de funciones con un número variable de argumentos el número máximo que pueden llegar a
tener es de 225.
Los tipos de argumentos que una función puede admitir son:
Ningún argumento. Por ejemplo las funciones PI((), HOY() y AHORA();
Números: SUMA(43;300;2)
Referencias a celdas individuales o rangos: SUMA(A1;B10:C20;D50)
Nombres de celdas o rangos: PRODUCTO(Cantidad; Precio)
Texto: Cadenas de texto: MAYUSCULAS(“jesús”)
Valores lógicos: SI(A1>50;89;A2) o pruebas lógicas
Valores de Error: TIPO.DE.ERROR(#Nombre)
Matrices: SUMAPRODUCTO(A1:A10;B1:B10). Algunas funciones como TRANSPONER o TENDEN-
CIA requieren el uso de matrices como argumentos.
Otras funciones: SUMA(MAX(A1:A20);B1:B20). A estas funciones internas se les denomina funciones
anidadas. Excel admite un máximo de 64 niveles de anidamiento.
Una mezcla de algunos o todos los tipos mencionados anteriormente.
Categorías de funciones
De forma intrínseca Excel posee más de 400 funciones que permiten efectuar los más variados cálculos,
desde los más sencillos hasta los más complejos. Para un acceso más rápido a la función deseada y a su
ayuda, Excel agrupa las funciones en diversos tipos o categorías funcionales. En concreto existen las si-
guientes categorías de funciones:
Funciones de complemento y automatización
Funciones de cubo
Funciones de base de datos
Funciones de fecha y hora
Funciones de ingeniería
Funciones financieras
Funciones de información
Funciones lógicas
Funciones de búsqueda y referencia
Funciones matemáticas y trigonométricas
Funciones estadísticas
Funciones de texto
Dentro de cada categoría las funciones aparecen ordenadas en orden alfabético.
Si conocemos el nombre de una función, pero no sabemos en que categoría esta, podemos buscarla dentro
de la categoría agrupadora “Todas”, donde se hallan todas las funciones existentes en el resto de catego-
rías, ordenadas también alfabéticamente.
Para acceder a estas categorías y ver las funciones que componen cada una de ellas, podemos hacerlo de
dos formas:
Desplazándonos a la ficha Fórmulas, grupo “Biblioteca de funciones” donde aparecen las categorías
más relevantes. Las categorías restantes están dentro del desplegable “Más funciones”,
Situarnos en la celda donde deseemos escribir la función y clicar en el asistente “Insertar función” que
se halla en misma ficha Fórmulas, grupo “Biblioteca de funciones” y también en la “Barra de fórmu-
las”.
En ambos casos accederemos a todas las categorías de funciones de Excel excepto a “Funciones de com-
plemento y automatización”. Esta categoría engloba diversas funciones o subprogramás muy especializa-
dos, que es necesario cargar previo a su uso. Para ello debemos ir a “Botón de Office” >”Opciones de Ex-
cel” > ”Complementos” y allí habilitar el complemento deseado. Entre los más destacados están: “Herra-
mientas para análisis” y “Solver”.
Introducción de funciones
Existen dos procedimientos para introducir una función en una celda, manualmente o mediante el asistente
“Insertar función”
A) Procedimiento semi-manual
1. Seleccionar la celda donde se desea poner la fórmula.
2. Escribir el signo igual (=) y empezar a escribir el nombre de la función, p.e. PROMEDIO. Nada más
escribirla aparece un desplegable con todas las funciones de Excel o nombres de rangos definidos
por nosotros. A medida que vamos escribiendo más letras de la función la lista se va ajustando para
mostrarnos solo aquellas funciones que comienzan por lo escrito. En el momento que estemos en la
deseada no hace falta escribir toda la función, solo picar la tecla TAB y Excel escribe el resto del
nombre y el paréntesis de apertura necesario para toda función.
3. Escribir los argumentos, o si son referencias a celdas o rangos marcarlos con el cursor.
4. Finalmente escribir el paréntesis de cierre de la función y terminar pulsando ENTER.
4. Seleccionar la función: p.e. PROMEDIO. En la parte inferior del cuadro de dialogo “Insertar función”
no aparecerá una breve descripción del cometido de la función y un hipervínculo a la página de
ayuda donde nos explica más en detalle dicho cometido así como la sintaxis exacta de la función y
probablemente algún ejemplo clarificador de su uso.
5. Clicar sobre el botón “Aceptar” para proseguir con la creación de la función. Se escribirá el nombre
de la función en la barra de fórmulas y nos aparecerá el dialogo “Argumentos de la función” para
que introduzcamos manualmente o mediante el ratón los valores o referencias a celdas y rangos
donde estén las cantidades de las que queremos hallar el promedio.
6. Hacer clic en el cuadro de texto Número1 y después seleccionar directamente en la hoja de cálculo
el rango A1:A10. De forma similar hacerlo con Número2 y rango C1:C7 y con Número3 rango
K1:K4.
7. En la parte inferior del dialogo se mostrara el resultado de la función a medida que se vayan aña-
diendo argumentos.
8. Cuando hayamos añadido todos los argumentos basta hacer clic en el botón “Aceptar” y Excel in-
sertara debidamente la fórmula en la celda seleccionada en el punto 1.
FUNCIONES ESTADISTICAS
Las funciones estadísticas permiten analizar grupos de números o datos. Aquí se mencionaran las funcio-
nes estadísticas más usuales y utilizadas. En esta categoría existen muchas funciones especializadas y
funciones avanzadas como ESTIMACION.LINEAL, ESTIMACION.LOGARITMICA, TENDENCIA, CRECI-
MIENTO, PROBABILIDAD, JERARQUIA, etc.
CONTAR.BLANCO
Devuelve un número que representa la cantidad de cedas en blanco existentes dentro de un rango dado.
Sintaxis
CONTAR.BLANCO(rango)
Rango es el rango seleccionado dentro del cual se quiere contar el número de celdas en blanco existentes.
Si existen fórmulas que devuelven "" (cadena o texto vacío) también se cuentan. Las celdas cuyo conteni-
do son valores cero no se cuentan.
Ejemplos
A CONTAR.BLANCO(A1:A7) es igual a 3
1 CONTAR.BLANCO(A2:A7) es igual a 2
2 Felipe Rodríguez
3
4 1245,33
5 21/12/2009
6
7 1.581
CONTAR
Cuenta el número de celdas que contienen números.
Sintaxis:
CONTAR(ref1;ref2;…)
Ref1; ref2; ... son entre 1 y 225 argumentos que pueden contener o hacer referencia a distintos tipos de
datos, pero sólo se cuentan los números.
Los argumentos que son números, fechas o representaciones textuales de números se cuentan. También
se cuentan las celdas cuyo contenido son fórmulas que devuelven un número o fecha. Los argumentos que
son valores de error o texto que no puede traducirse a números se pasan por alto.
Si un argumento es una matriz o una referencia, sólo se cuentan los números de esa matriz o referencia. Se
pasan por alto las celdas vacías, valores lógicos, texto o valores de error de la matriz o de la referencia.
Utilizar la función CONTARA si se necesita contar valores lógicos, texto o valores de error.
Ejemplos
A
CONTAR(A1:A10) es igual a 3, que son las celdas que contienen un número, una
1
fecha y una fórmula que retorna un número (1.581) respectivamente.
2 Felipe Rodríguez
3 #¡NUM!
4 1245,33
5 21/12/2009
6
7 1.581
8 ""
9 Alberto San Juan
10 VERDADERO
CONTARA
Cuenta el número de celdas que no están vacías y los valores que hay en la lista de argumentos, Usar
CONTARA para contar el número de celdas que contienen datos en un rango o matriz. Si no se necesita
contar valores lógicos, texto o valores de error, usar la función CONTAR.
Sintaxis
CONTARA(valor1 ;valor2; ... )
Valor1; valor2; ... son de 1 a 225 argumentos que representan los valores que se desea contar. En este
caso, un valor es cualquier tipo de información, incluyendo texto vacío (""), pero excluyendo celdas vacías.
Si un argumento es una matriz o una referencia, se pasan por alto las celdas vacías que se encuentran en
la matriz o en la referencia.
Ejemplos
A
CONTARA(A1:A10) es igual a 8, pues también incluye el valor de A8 que es una
1
cadena vacía o de longitud cero, que es distinto de celda vacía.
2 Felipe Rodríguez
3 #¡NUM!
4 1245,33
5 21/12/2009
6
7 1.581
8 ""
9 Alberto San Juan
10 VERDADERO
CONTAR.SI
Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado. Es
una extensión de la función CONTAR.
Sintaxis
CONTAR.SI(rango; criterio)
Rango es el rango dentro del cual desea contar el número de celdas que no están en blanco.
Criterio es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar.
Por ejemplo, el argumento criterio puede expresarse como 55; "55"; ">55" o "Sevilla".
Ejemplos
Supongamos que el rango A1:A5 contiene los valores: “Madrid”, ”Barcelona”, ”Sevilla”, ”Barcelona” y ”Va-
lencia”, respectivamente, entonces CONTAR.SI(A1:A5;”Barcelona”) es igual a 2.
Supongamos que el rango B1:B5 contiene los valores: 12,50,33,20 y 31, entonces CONTAR.SI(B1:B5;
">30") es igual a 3.
CONTAR.SI.CONJUNTO
Cuenta el número de celdas en un rango para celdas que tienen múltiples criterios. Es una extensión o am-
pliación de CONTAR.SI que en lugar de un solo criterio admite múltiples criterios.
Sintaxis
CONTAR.SI.CONJUNTO(rango_criterios1;criterio1;[rango_criterios2;criterio2];…)
Rango_criterios1, Rango_criterios2,…Son los rangos dentro de los cuales se compara el criterio correspon-
diente para ver que celdas del rango correspondiente lo cumplen.
Criterio1, Criterio2,… son los criterios en forma de número, texto o fecha que determinan las celdas que se
contarán.
Esta función admite múltiples criterios a la vez, pero sobre campos o columnas distintas. Para aplicar múlti-
ples criterios sobre un mismo campo debemos usar funciones de tipo “Bases de datos”, como por ejemplo
BDCONTAR o BDCONTARA
Ejemplos
Supongamos la siguiente tabla de clientes y ventas:
A B C D E F
1 Clientes Zona Ciudad Mes Cantidad Importe
2 EL CORTE INGLES Centro Madrid 9 100 1000
3 IKEA Catalunya Barcelona 9 50 900
4 MUEBLES LA FABRICA Centro Madrid 11 25 1200
5 OFIMUEBLE Catalunya Barcelona 5 100 1800
6 LA CASA DE LAS LAMPARAS Andalucía Sevilla 6 120 2000
7 IKEA Andalucía Sevilla 9 50 1900
8 TODO HOGAR Catalunya Barcelona 9 100 800
9 NOVALINEA Aragón Zaragoza 8 200 1200
10 EL CORTE INGLES Aragón Zaragoza 3 120 1300
11 OFIMUEBLE Catalunya Barcelona 2 100 1200
Si quisiéramos hallar el número de clientes de Barcelona a los que se les facturo menos de 1000 €, escribi-
ríamos la función así: CONTAR.SI.CONJUNTO(C2:C11;"Barcelona";F2:F11;"<1000")
MIN
Devuelve el valor mínimo de un conjunto de valores.
Sintaxis
MIN(número1 ;número2; ...)
Número1;número2; ... son entre 1 a 225 números cuyos valores mínimos se desea encontrar. Se pueden
especificar argumentos que sean números, celdas vacías, valores lógicos o representaciones textuales de
números. Los argumentos que son valores de error o texto que no se puede traducir a números, provocan
errores.
Si un argumento es una matriz o referencia, sólo se usan los números de esa matriz o referencia. Las cel-
das vacías, valores lógicos o texto que se encuentren dentro de la matriz o referencia se pasan por alto.
Utilizar la función MINA si los valores lógicos o el texto no deben pasarse por alto.
ProInf.net, SCP Página 31
667 551 698
www.proinf.net
Excel 2010 Avanzado
MEDIANA
Devuelve la mediana de los números. La mediana es el número que se encuentra en medio de un conjunto
de números, es decir, la mitad de los números es mayor que la mediana y la otra mitad es menor.
Sintaxis
MEDIANA(número1 ;número2; ..)
Número1;número2; .... son entre 1 y 225 números cuya mediana desea obtener.
Los argumentos deben ser números o nombres, matrices o referencias que contengan números. Excel
examina todos los números en cada argumento matricial o de referencia.
Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se
pasan por alto; sin embargo, se incluyen las celdas cuyo valor sea 0.
Si la cantidad de números en el conjunto es par, MEDIANA calcula el promedio de los números centrales.
Ejemplos
MEDIANA(1; 2; 3; 4; 5) es igual a 3
MEDIANA(1; 2; 3; 4; 5; 6) es igual a 3,5, el promedio de 3 y 4
MODA
Devuelve el valor que se repite con más frecuencia en una matriz o rango de datos. Al igual que MEDIANA,
MODA es una medida de posición o centralización
Sintaxis
MODA(número1 ;número2; ...)
Número1;número2; ... son entre 1 y 225 argumentos cuya moda se desea calcular. También se puede usar
una matriz individual o una referencia matricial en lugar de argumentos separados por punto y coma.
Los argumentos deben ser números, nombres, matrices o referencias que contengan números.
Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se
pasan por alto; sin embargo, se incluirán las celdas cuyo valor sea 0.
Si el conjunto de datos no contiene puntos de datos duplicados, MODA devuelve el valor de error #N/A.
Ejemplo
MODA({5,6: 4; 4; 3; 2; 4}) es igual a 4
DESVEST
Calcula la desviación estándar de una muestra. La desviación estándar es la medida de la dispersión de los
valores respecto a la media (valor promedio).
Sintaxis
DESVEST(número1; número2; ...)
Número 1; número2; ...son de 1 a 225 argumentos numéricos que corresponden a una muestra de pobla-
ción. También se puede utilizar una matriz única o una referencia matricial en lugar de argumentos separa-
dos con punto y coma.
Se pasan por alto los valores lógicos como VERDADERO y FALSO y el texto. Si los valores lógicos y el
texto no deben pasarse por alto, utilizar la función de hoja de cálculo DESVESTA.
DESVEST parte de la hipótesis de que los argumentos representan la muestra de una población. Si los
datos representan la población total, utilizar DESVESTP para calcular la desviación estándar.
Ejemplo
Supongamos que tenemos una muestra aleatoria de 10 herramientas forjadas por la misma máquina du-
rante un ciclo de producción y cuya resistencia a la rotura se desea medir. Los valores de la muestra
(1345; 1301; 1368; 1322; 1310; 1370; 1318; 1350; 1303; 1299) se almacenan en las celdas A2:E3 res-
ProInf.net, SCP Página 33
667 551 698
www.proinf.net
Excel 2010 Avanzado
pectivamente. DESVEST calcula la desviación estándar de la resistencia a la rotura de todas las herra-
mientas producidas.
DESVEST(A2:E3) es igual a 27,46
VAR
Calcula la varianza de una muestra.
Sintaxis
VAR(número1 ;número2;.) .
Número 1; número2; ... son de 1 a 225 argumentos numéricos que se corresponden con una muestra de
población.
La función VAR parte de la hipótesis de que los argumentos representan una muestra de la población. Si los
datos representan fa población total;·utilizar VARP·para calcular la varianza.
Se pasan por alto los valores lógicos como VERDADERO y FALSO Y el texto Utilizar la función de hoja de
cálculo DESVESTA si los valores lógicos y el texto no se deben pasar por alto.
Ejemplo
Supongamos que toma una muestra aleatoria de 10 herramientas forjadas por la misma máquina duran-
te un ciclo de producción y cuya resistencia a la rotura desea medir. Los valores de la muestra (1345;
1301; 1368; 1322; 1310; 1370; 1318; 1350; 1303; 1299) se almacenan en las celdas A2:E3 respectiva-
mente. VAR calcula la varianza de la resistencia a la rotura de todas las herramientas producidas.
VAR(A2:E3) es igual a 754,3
FUNCIONES LÓGICAS
Las funciones lógicas sirven para evaluar pruebas lógicas, es decir comparar valores o comprobar si se
cumple una o varias condiciones. Las funciones lógicas suelen emplear los operadores relaciones o de
comparación en alguno de sus argumentos y en general retornan los valores lógicos VERDADERO o FAL-
SO.
SI
Se utiliza para realizar pruebas condicionales en valores y fórmulas. Devuelve un valor si la condición espe-
cificada es VERDADERO y otro valor si dicho argumento es FALSO.
Sintaxis
SI(prueba-lógica;valor_si_verdadero;valor_si_falso)
Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO.
Valor_si_verdadero es el valor que se devolverá si prueba_lógica es VERDADERO. Si prueba_lógica es
VERDADERO y se omite el argumento valor_si_verdadero, la función devuelve VERDADERO. Este argu-
mento puede ser a su vez otra fórmula.
Valor_si_falso es el valor que se devolverá si prueba_lógica es FALSO. Si prueba_lógica es FALSO y se
omite el argumento valor_si_falso, la función devuelve FALSO. También puede ser una fórmula.
Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para cons-
truir pruebas con más condiciones o condiciones más elaboradas.
Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor de-
vuelto por la ejecución de las instrucciones.
Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se
ejecute la instrucción SI.
Ejemplos
En el siguiente ejemplo, si el valor en la celda A:10 es 100, prueba_lógica será VERDADERO y se calcu-
lará el valor total del rango B5:B 15. De lo contrario, prueba_lógica será FALSO y se devolverá una ca-
dena de texto vacía (""') que borrará el contenido de la celda que contenga la función SI.
SI(A10=100;SUMA(B5:B15);"")
Supongamos que una hoja de cálculo contiene las cifras de los gastos actuales y los pronosticados. Las
celdas B2:B4 contienen los "Gastos actuales" para enero, febrero y marzo: 1500; 500; 500. Las cel-
dasC2:C4contienen k>s "Gastos pronosticados" para los mismos periodos: 900; 900;,925,..
Con las siguientes fórmulas puede escribir una fórmula que compruebe si se ha excedido el presupuesto:
SI(B2>C2;"Presupuesto excedido";"Aceptar") es igual a "Presupuesto excedido"
SI(B3>C3;"Presupuesto excedido";"Aceptar") es igual a "Aceptar"
O
Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los ar-
gumentos son FALSO.
Sintaxis
O(valor_lógico1 ;valor_lógico2;… )
Valor_lógico1; valor_lógico2; ... son entre 1 y 225 condiciones que se desean comprobar y que pueden ser
VERDADERO o FALSO.
Los argumentos deben ser valores lógicos como VERDADERO O FALSO, o matrices o referencias que
contengan valores lógicos.
Si un argumento matricial o de referencia contiene texto, números o celdas vacías, dichos valores se pasa-
rán por alto.
Y
Devuelve VERDADERO si todos los argumentos son VERDADERO; devuelve FALSO si uno o más argu-
mentos son FALSO.
Sintaxis
Y(valor_lógico1 ;valor_lógico2;… )
Valor_lógico1;valor_lógico2; ... son de 1 a 225 condiciones que desea comprobar y que pueden ser VER-
DADERO o FALSO.
Los argumentos deben evaluarse como valores lógicos (VERDADERO O FALSO), o los argumentos deben
ser matrices o referencias que contengan valores lógicos.
Si un argumento matricial o de referencia contiene texto o celdas vacías, esos valores se pasan por alto.
Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALORI
Ejemplos
Y(VERDADERO;VERDADERO) es igual a VERDADERO
Y(VERDADERO;FALSO) es igual a FALSO
Y(2+2=4; 2+3=5) es igual a VERDADERO
Si B1:B3 contiene los valores VERDADERO, FALSO y VERDADERO, entonces:
Y(B1:B3) es igual a FALSO
Si B4 contiene un número entre 1 y 100, entonces:
Y(1<B4;B4<100) es igual a VERDADERO
Supongamos que se desea mostrar la celda B4 sólo si contiene un número entre 1 y 100, Y que debe
mostrar un mensaje si no lo contiene. Si B4 contiene 104, entonces:
SI(Y(1 <B4; B4<100);B4;"El valor queda fuera del rango") es igual a "El valor queda fuera del rango."
Si B4 contiene 50, entonces: SI(Y(1 <B4; B4<100); B4; "El valor queda fuera del rango") es igual a 50
SI.ERROR
Sirve para desplegar un mensaje de error personalizado en la propia celda en la que se utiliza. Es decir
devuelve un valor que es el resultado de ejecutar una fórmula o función o caso de producirse un error en
dicha fórmula, retorna un mensaje de error personalizado. Se debe usar para interceptar y controlar errores
en fórmulas.
Sintaxis
SI.ERROR(valor ;valor_si_error)
Valor es una, fórmula, expresión o función que puede o no producir un error en su evaluación.
Valor_si_error es el valor que se devuelve si la fórmula lo evalúa como error. Se evalúan los tipos siguientes
de error: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!.
ProInf.net, SCP Página 36
667 551 698
www.proinf.net
Excel 2010 Avanzado
Ejemplo
Supuesta la siguiente tabla de Clientes, se desea buscar los valores de Ciudad para un cliente determi-
nado dando como información inicial el nombre del cliente en la celda A10.
A B C
1 Clientes Zona Ciudad
2 EL CORTE INGLES Centro Madrid
3 IKEA Catalunya Barcelona
4 MUEBLES LA FABRICA Centro Madrid
5 OFIMUEBLE Catalunya Barcelona
6 LA CASA DE LAS LAMPARAS Andalucía Sevilla
7 IKEA Andalucía Sevilla
Si introduzco al fórmula siguiente en la celda B10: =SI.ERROR(BUSCARV(A10;B1:C7;3;FALSO);"El
cliente no existe")
Si en A 10 escribo OFIMUEBLE el resultado que se mostrara en B10 es: Barcelona.
Si en A10 escribo OFIMUEBLES el resultado que se mostrara en B10 es: "El cliente no existe"
COINCIDIR
Devuelve la posición relativa de un elemento en una matriz que coincida con un orden especificado. Se
debe utilizar COINCIDIR en lugar de las funciones BUSCAR cuando se necesite conocer la posición de un
elemento en un rango, en lugar del elemento en sí.
Sintaxis
COINCIDIR(valor_buscado;matriz_buscada;tipo_de_coincidencia)
Valor_buscado es el valor que se usa para encontrar el valor deseado en la tabla.
Valor_buscado es el valor que desea hacer coincidir en la matriz_buscada. Por ejemplo, al buscar algún
número en la guía telefónica, se estará usando el nombre de la persona como valor de búsqueda, pero el
valor que realmente se desea es el número de teléfono.
Valor_buscado puede ser un valor (número, texto o valor lógico) o una referencia de celda a un número, a
un texto o a un valor lógico.
Matriz_buscada es un rango múltiple de celdas que contienen posibles valores a buscar. Puede ser una
matriz o una referencia matricial.
Tipo_de_coincidencia es el número -1, 0 ó 1.
Si tipo_de_coincidencia es 1, COINCIDIR encuentra el mayor valor que es inferior o igual al valor_buscado.
Los valores en el argumento matriz_buscada deben colocarse en orden ascendente (...-2; -1; 0; 1; 2;...A-Z;
FALSO; VERDADERO).
Si tipo_de_coincidencia es 0, COINCIDIR encuentra el primer valor que es exactamente igual al va-
lor_buscado. Los valores en matriz_buscada pueden estar en cualquier orden.
Si tipo_de_coincidencia es -1, COINCIDIR encuentra el menor valor que es mayor o igual al valor_buscado.
Los valores en matriz_buscada deben colocarse en orden descendente (VERDADERO; FALSO; Z-A;...2; 1;
0; -1; -2;) y así sucesivamente.
Si se omite tipo_de_coincidencia, se supondrá que es 1.
COINCIDIR devuelve la posición del valor coincidente dentro de la matriz_buscada y 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 hace coincidir valores de texto. Si COIN-
CIDlR no puede encontrar una coincidencia, devuelve el valor ·de error #N/A:
Si tipo_de_coincidencia es 0 y valor_buscado es texto, valor_buscado puede contener los caracteres como-
dines asterisco (*) y signo de interrogación (?). El asterisco equivale a una secuencia de caracteres y el
signo de interrogación a un único carácter.
Ejemplos
A
1 Importe COINCIDIR(4000;A2:A8;1) es igual a 4, usando coincidencia aproximada.
2 1000 COINCIDIR(2000;A2:A8;0) es igual a 2, usando coincidencia exacta.
3 2000 COINCIDIR(4000;A2:A8;-1) es igual al valor de error #N/A debido a que el rango
4 3000 A2:A8 está ordenado incorrectamente para el tipo_de_coincidencia (-1). El orden
5 debe ser descendente para que sea correcto.
4000
6 5000
7 6000
8 7000
INDICE
Devuelve un valor o la referencia a un valor en una tabla o rango. La función INDICE tiene dos formas: ma-
tricial y referencial. La forma matricial siempre devuelve un valor o una matriz de valores y la forma de refe-
rencia siempre devuelve una referencia.
Sintaxis Forma matricial
INDICE(matriz;núm_fila;núm_columna)
Matriz es un rango de celdas o una matriz de constantes.
Núm_fila selecciona, en el rango matriz, la fila desde la cual se devolverá un valor. Si se omite núm_fila, se
requiere el argumento núm_columna.
Núm_columna selecciona, en el rango matriz, la columna desde la cual se devolverá un valor. Si se omite
núm_columna, se requiere el argumento núm_fila.
Si se utilizan ambos argumentos núm_fila y núm_columna, INDICE devuelve el valor en la celda de inter-
sección de los argumentos núm_fiIa y núm_columna.
Si matriz contiene sólo una fila o columna, el argumento núm_fila o núm_columna que corresponde es op-
cional.
Si matriz tiene más de una fila y más de una columna y sólo utiliza núm_fila o núm_columna, INDICE de-
vuelve una matriz con toda una fila o columna.
Si se define núm_fiIa o núm_columna como 0, INDICE devuelve la matriz de valores de toda la columna o
fila, respectivamente. Si se desea utilizar valores devueltos en forma de matriz, se debe introducir la función
INDICE como una fórmula matricial en un rango horizontal de celdas. Para introducir una fórmula matricial,
pulsar Ctrl+Mayúsculas+lntro.
Los argumentos núm_fila y núm_columna deben indicar una celda contenida en matriz; de lo contrario, IN-
DICE devuelve el valor de error #iREF!
Ejemplos
INDICE({1;2\3;4};2;2) es igual a 4, si se introduce de forma referencial.
INDICE({1;2\3;4};0;2) es igual a {2/4}, si se introduce como una fórmula matricial ocupando dos celdas.
Si las celdas B5:B6 contienen el texto Manzanas y Bananas y las celdas C5:C6 el texto Limones y Peras
respectivamente, entonces:
INDICE(B5:C6;2;2) es igual a Peras
INDICE(B5:C6;2;1) es igual a Bananas
Después de que ref y núm_área hayan seleccionado un rango determinado, núm_fila y núm_columna se-
leccionan una celda específica: núm_fiIa 1 es la primera fila del rango, núm_columna 1 es la primera colum-
na y así sucesivamente. La referencia devuelta por INDICE es la intersección entre núm_fila y núm colum-
na.
Si se define núm_fila o núm_columna como 0, INDICE devuelve la referencia de toda la fila o columna, se-
gún corresponda.
Núm_fila, núm_columna y núm_área deberán dirigirse a una celda en ref, de lo contrario la función INDICE
devuelve el valor de error #¡REF! Si núm_fila y núm_columna se omiten, INDICE devuelve el área del argu-
mento ref definido por núm_área.
El resultado de la función INDICE es una referencia y será interpretada como tal por otras fórmulas. El valor
devuelto por la función INDICE se puede utilizar como una referencia o como un valor, dependiendo de la
fórmula. Por ejemplo, la fórmula CELDA("ancho";INDICE(A1:B2;1;2)) es igual a CELDA("ancho";B1). La
función CELDA utiliza el valor devuelto por INDICE como referencia a una celda. Por otra parte, una fórmula
como 2*INDICE(A1:B2;1;2) traduce el valor devuelto por INDICE en el número de la celda B1.
Ejemplos
En la siguiente hoja de cálculo, el rango A2:C6 se denomina Fruta, el rango A7:C10 se denomina Fru-
tosSecos y el rango C21:C11 se denomina Existencias.
A B C
1 Frutas Precio Existencias INDICE(Frutas;2;3) es igual a la referencia C3, que con-
2 Manzanas 40 tiene 38.
0,69
3 Plátanos 0,34 38 INDICE((A1:C6;A8:C11);2;2;2) es igual a la referencia B9,
que contiene 3,55
4 Limones 0,55 15
5 Naranjas 0,25 25
6 Peras 0,59 40 SUMA(INDICE(Existencias;0;3;1)) es igual a SUMA(C1:C11),
cuyo resultado es 218.
7 Frutos secos
8 Almendras 2,80 10
9 Anacardos 3,55 18 SUMA(B2:INDICE(Frutas;5;2)) es igual a SUMA(B2:B6),
cuyo resultado es 2,42
10 Cacahuetes 1,25 20
11 Nueces 1,75 12
Sintaxis
SUMAR.SI.CONJUNTO(rango_suma,rango_criterios1,criterio1;[rango_criterios2;criterio2];…)
Rango_suma es una o más celdas para sumar, incluidos números o nombres, rangos o referencias de celda
que contienen números. Se omiten los valores en blanco o de texto.
Rango_criterios1, Rango_criterios2,…Son los rangos dentro de los cuales se compara el criterio correspon-
diente para ver que celdas del rango correspondiente lo cumplen.
Criterio1, Criterio2,… son los criterios en forma de número, texto o fecha que determinan las celdas que se
incluirán en la suma.
Esta función admite múltiples criterios a la vez, pero sobre campos o columnas distintas. Para aplicar múlti-
ples criterios sobre un mismo campo debemos usa la función de tipo “Bases de datos”, BDSUMA.
Ejemplos
Supongamos la siguiente tabla de clientes y ventas:
A B C D E F
1 Clientes Zona Ciudad Mes Cantidad Importe
2 EL CORTE INGLES Centro Madrid 9 100 1000
3 IKEA Catalunya Barcelona 9 50 900
4 MUEBLES LA FABRICA Centro Madrid 11 25 1200
5 OFIMUEBLE Catalunya Barcelona 5 100 1800
6 LA CASA DE LAS LAMPARAS Andalucía Sevilla 6 120 2000
7 IKEA Andalucía Sevilla 9 50 1900
8 TODO HOGAR Catalunya Barcelona 9 100 800
9 NOVALINEA Aragón Zaragoza 8 200 1200
10 EL CORTE INGLES Aragón Zaragoza 3 120 1300
11 OFIMUEBLE Catalunya Barcelona 2 100 1200
Si quisiéramos hallar el importe total de ventas a clientes para clientes de Barcelona a los que se les facturo
menos de 1000 €, escribiríamos la función así:
SUMAR.SI.CONJUNTO(F6:F15;C6:C15;"Barcelona";F6:F15;"<1000")
ENTERO
Redondea un número hasta el entero inferior más próximo.
Sintaxis
ENTERO(número)
Número es el número real que desea redondear al entero inferior más próximo.
Ejemplos
ENTERO(8,9) es igual a 8 y ENTERO(-8,9) es igual a -9
REDONDEAR
Redondea un número al número de decimales especificado. Sintaxis
REDONDEAR(número;núm_de_decimales)
Número es el número que se desea redondear.
Núm_de_decímales especifica el número de dígitos al que se desea redondear el argumento número. Si el
argumento núm_de_decímales es mayor que 0, número se redondeará al número de lugares decimales
especificado. Si el argumento núm_de_decímales es 0, número se redondeará al entero más próximo Si el
argumento núm_de_decímales es menor que 0, número se redondeará hacia la izquierda del separador
decimal.
Ejemplos
REDONDEAR(2,15; 1) es igual a 2,2.
REDONDEAR(2,149; 1) es igual a 2,1.
REDONDEAR(-1 ,475; 2) es igual a -1,48.
REDONDEAR(21 ,5; -1) es igual a 20.
TRUNCAR
Trunca un número a un entero, suprimiendo la parte fraccionaria de dicho número.
Sintaxis
TRUNCAR(número; núm_de_decimales)
Número es el número que se desea truncar.
Núm_de_decímales es un número que especifica la precisión al truncar. El valor predeterminado del argu-
mento núm_de_decímales es 0. TRUNCAR y ENTERO son similares, ya que ambos devuelven enteros.
TRUNCAR suprime la parte fraccionaria del número. ENTERO redondea los números al entero menor más
próximo, según el valor de la porción fraccionaria del número. ENTERO y TRUNCAR son diferentes sola-
mente cuando se usan números negativos: TRUNCAR(-4,3) devuelve -4, pero ENTERO(-4,3) devuelve -5,
ya que -5 es el número entero menor más cercano.
Ejemplos
TRUNCAR(8,9) es igual a 8. TRUNCAR(-8,9) es igual a -8. TRUNCAR(PI()) es igual a 3.
DECIMAL
Redondea un número al número especificado de decimales, dando formato al número usando puntos y co-
más y retornando el resultado como texto.
Sintaxis
DECIMAL(número; decimales, no_separar_millares)
Número es el número que se desea redondear y convertir en texto
Decimales es el número de dígitos a la derecha del separador decimal.
No_separar_millares determina si se incluye o no el separador de miles en el resultado textual que retorna.
Es un valor lógico, que si es VERDADERO no incluirá el separador de miles en el resultado.
La principal diferencia entre dar formato a una celda que contiene un número con un comando (en ficha
Inicio, en el grupo Número) y dar formato a un número directamente con la función DECIMAL es que DE-
CIMAL convierte el resultado en texto. Un número que recibe formato con el comando CELDAS sigue sien-
do un número.
Dado que el resultado de esta función es texto, no se podrá usar dentro de funciones que requieran argu-
mentos numéricos.
ABS
Devuelve el valor absoluto de un número. El valor absoluto de un número es el número sin su signo.
Sintaxis
ABS(número)
Número es el número real cuyo valor absoluto desea obtener.
Ejemplos
ABS(2) es igual a 2
ABS(-2) es igual a 2
Si A1 contiene -16, entonces: RAIZ(ABS(A1)) es igual a 4
SUBTOTALES
Devuelve un subtotal en una lista o base de datos, aunque generalmente es más fácil crear una lista con
subtotales utilizando el comando Subtotal de la ficha Datos, grupo Esquema. Las función SUBTOTALES
engloba dentro de si once funciones diferentes con dos posibilidades cada una de ellas
Sintaxis
SUBTOTALES(núm_función;ref1)
Núm_función es un número de 1 a 11 (incluye valores ocultos) o de 101 a 111 (no incluye valores ocultos)
que indica qué función debe ser utilizada para calcular los subtotales dentro de una lista.
FUNCIONES DE TEXTO
Las funciones de esta categoría o bien manipulan cadenas de texto o bien convierten entradas de texto a
valores numéricos o entradas numéricas a valores de texto. La mayor de ellas toman como argumento un
texto y nos permiten operaciones como buscar, reemplazar o eliminar caracteres dentro del mismo, conver-
tirlo a mayúsculas o minúsculas, contar su longitud en caracteres, eliminar espacios o caracteres no impri-
mibles, etc.
CONCATENAR
Concatena argumentos de texto.
Sintaxis
ProInf.net, SCP Página 46
667 551 698
www.proinf.net
Excel 2010 Avanzado
CONCATENAR(texto1, texto2;….)
Texto1,texto2,… son de 1 a 225 elementos de teso que se unirán en un elemento de texto único. Los ele-
mentos de texto pueden ser cadenas de texto, números o referencias a celdas individuales.
Se puede realizar la misma operación de unión de elementos de texto usando el operador de concatenación
"&" que es totalmente equivalente.
Ejemplo
Suponiendo tuviésemos una base de datos sobre empleados según la siguiente tabla
A B C
1 Apellidos Nombre Concatenado
2 Abellán Bermejo Sonia Sra/Srta. Sonia, Abellán Bermejo
3 Barragán Peréz Laura Sra/Srta. Laura, Barragán Peréz
4 Cerqueda Pino Milagros Sra/Srta. Milagros, Cerqueda Pino
5 Chimeno Casas Cristina Sra/Srta. Cristina, Chimeno Casas
6 Díaz Fernández Mirian Sra/Srta. Mirian, Días Fernández
Y suponiendo que quisiéramos concatenar en la columna C, primero el nombre y después el apellido con un
calificativo inicial deberíamos introducir de C2 a C6 la siguiente fórmula:
=CONCATENAR("Sra/Srta. ";B2;", ";A2)
Para producir el resultado que aparece en la columna C de la tabla
IZQUIERDA
Extrae el carácter o los caracteres situados en el extremo izquierdo de una cadena de texto.
Sintaxis
IZQUIERDA(texto;núm_de_caracteres)
Texto es la cadena de texto que contiene los caracteres que desea extraer.
Núm_de_caracteres especifica cuántos caracteres desea que devuelva, debe ser mayor o igual a 0. Si
núm_de_caracteres es mayor que la longitud del texto, IZQUIERDA devuelve todo el texto. Si
núm_de_caracteres se omite, el valor predeterminado es 1.
Ejemplos
IZQUIERDA("Precio de venta"; 6) es igual a "Precio"
Si A1 contiene "Suecia", entonces: IZQUIERDA (A1;1) es igual a "S"
DERECHA
Devuelve el último carácter o caracteres del extremo derecho de una cadena de texto.
Sintaxis
DERECHA(texto;núm_de_caracteres)
Texto es la cadena de texto que contiene los caracteres que se desea extraer.
Núm_de_caracteres especifica el número de caracteres que se desea extraer, debe ser igualo mayor que o.
Si el argumento núm_de_caracteres es mayor que la longitud de texto, DERECHA devolverá todo el texto.
Si el argumento núm_de_caracteres se omite, se asume que es 1.
Ejemplos
DERECHA("Precio Venta"; 7) es igual a "Venta"
DERECHA("Número Inventario") es igual a "o"
EXTRAE
Devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que es-
pecifique.
Sintaxis
EXTRAE(texto;posición_inicial;núm_de_caracteres)
Texto es la cadena de texto que contiene los caracteres que se desea extraer.
Posición_inicial es la posición del primer carácter que se desea extraer del argumento texto. La posi-
ción_inicial del primer carácter de texto es 1 y así sucesivamente.
Si posición_inicial es mayor que la longitud de texto, EXTRAE devuelve texto vacío.
Si posición_inicial es menor que la longitud de texto, pero posición_inicial más núm_de_caracteres excede
la longitud de texto, EXTRAE devuelve los caracteres hasta el final de texto. Si posición_inicial es menor
que 1, EXTRAE devuelve el valor de error #¡VALOR!
Núm_de_caracteres especifica el número de caracteres de texto que se han de devolver. Si núm_de_ ca-
racteres es negativo, EXTRAE devuelve el valor de error #¡VALOR!
Ejemplos
EXTRAE("Flujo de liquido"; 1; 5) es igual a "Flujo"
EXTRAE("Flujo de liquido"; 7; 20) es igual a "de líquido"
EXTRAE("1234"; 5; 5) es igual a .... (texto vacío)
IGUAL
Compara dos cadenas de texto y devuelve VERDADERO si son exactamente iguales y FALSO si no lo son.
IGUAL reconoce mayúsculas y minúsculas, pero pasa por alto las diferencias de formato
Sintaxis
IGUAL(texto1;texto2)
Texto1 es la primera cadena de texto, entre comillas.
Texto2 es la segunda cadena de texto, entre comillas.
Ejemplos
IGUAL("palabra";"palabra") es igual a VERDADERO
IGUAL("Palabra";"palabra") es igual a FALSO
IGUAL("p alabra";"palabra") es igual a FALSO.
NSHORA
Devuelve el número de serie para una hora determinada. El número de serie devuelto por NSHORA es una
fracción decimal entre O y 0,99999999 que representa las horas entre 0:00:00 (12:00:00 AM) y 23:59:59
(11 :59:59 PM).
Sintaxis
NSHORA(hora;minuto;segundo)
Hora es un número entre 0 y 23 que representa las horas.
Minuto es un número entre 0 y 59 que representa los minutos.
Segundo es un número entre 0 y 59 que representa los segundos.
Ejemplo
NSHORA(12;0;0) es igual al número de serie 0,5, que es equivalente a 12:00:00 PM
Tipo es un número que determina que tipo de valor debe ser devuelto.
1 u omitido Números del 1 (domingo) al 7 (sábado)
2 Números del 1 (lunes) al 7 (domingo)
3 Números del 0 (lunes) al 6 (domingo)
También puede usarse la función TEXTO para convertir un valor en un formato de número especificado
usando el sistema de fechas 1900:
TEXTO("10-11-09";"dddd") es igual a martes
Ejemplos
=DIASEM("27-11-09") es igual a 6 (domingo)
FECHANÚMERO
Devuelve el número de serie de la fecha representada por texto_de_fecha.
Sintaxis
FECHANÚMERO(texto_de_fecha)
Texto_de_fecha es el texto que devuelve una fecha en un formato de fecha de Excel. Si se usa el sistema
de fecha predeterminado de Excel para Windows, el argumento texto_de_fecha debe representar una fecha
desde el1 de enero de 1900 hasta el 31 de diciembre del año 9999. Devuelve el valor de error #¡VALORI si
texto_de_fecha no está comprendido en ese rango.
Si se omite la parte texto_de_fecha correspondiente al año, se usa el año corriente del reloj integrado en el
PC. La información de hora de texto_de_fecha se pasa por alto.
Ejemplo
FECHANÚMERO("6-feb") es igual a 39850. Siendo el 6 de febrero de 2009
HORANÚMERO
Devuelve el número de serie de la hora representada por texto_de_hora. El número de serie es una fracción
decimal entre 0 y 0,99999999 que representa las horas entre 0:00:00 (12:00:00 AM) y 23:59:59 (11 :59:59
PM).
Sintaxis
HORANÚMERO(texto_de_hora)
Texto_de_hora es una cadena de texto entre comillas que devuelve una hora en uno de los formatos hora-
rios de Excel. La información de fecha en el argumento texto_de_hora se pasa por alto.
Ejemplos
HORANÚMERO("2:24 AM") es igual a 0,1
HORANÚMERO("10-nov-07 12:30") es igual a 0,520833333335759
BASES DE DATOS
Una de las funcionalidades de Excel es manejar listas o tablas de información de cualquier tipo: nombres,
direcciones, teléfonos, salarios, importes, etc..Excel puede trabajar con listas, que se componen de una sola
columna o con tablas o bases de datos que se componen de varias columnas de información.
Una base de datos en Excel es por tanto o bien una Tabla o bien un grupo de columnas que almacenan un
conjunto de información homogénea organizada de una forma estructurada y sistemática; un ejemplo usual
es una lista de empleados o clientes con sus correspondientes datos.
La información de una base de datos se estructura en forma de registros y campos:
Un registro o fila representa el conjunto de la información relativa a cada uno de los elementos indi-
viduales de una base de datos: en una base de datos de empleados la información de cada uno de
los empleados representaría un registro.
Un campo, representa cada uno de los fragmentos individuales de información que componen un
registro. Cada empleado de la base de datos está definido por su nombre, su dirección, su número
telefónico, el departamento en el que trabaja; cada uno de estos elementos constituye un campo.
Las bases de datos en Excel se construyen introduciendo cada registro en una fila de una hoja de cálculo.
Una base de datos solo puede ocupar una única hoja de cálculo. Excel no admite o permite trabajar con
Bases de datos Relacionales, que son bases de datos en las cuales la información se puede almacenar en
varias tablas o ficheros con campos relacionados, lo cual evita la redundancia (duplicación) de información.
Para trabajar con bases de datos Relacionales debemos usar Microsoft Access u otro programa similar.
A las bases de datos que se guardan en un único fichero u hoja de cálculo se les denomina “bases de datos
planas” y sus funcionalidades son más limitadas que las de tipo Relacional
Dentro de cada hoja de cálculo se pueden crear varias bases de datos independientes, utilizando la opción
“Crear tabla”, que se detallará más adelante.
Ordenación de la información: según valores de uno o varios campos y siguiendo diversos criterios
Búsqueda de registros: bajo criterios específicos para cada campo.
Filtrado o Extracción o de información: a través de diversos filtros automatizados o manuales
Creación de subtotales: usando un mecanismo automatizado.
Cálculos y filtros avanzados usando funciones de Bases de datos.
La mayor parte de estas operaciones se pueden llevar a cabo, desde la ficha Datos, principalmente en los
grupos Ordenar y filtrar ,Herramientas de datos y Esquema.
Validación de datos
La validación de datos es un mecanismo que nos facilita Excel para minimizar al máximo la posibilidad de
cometer errores durante la introducción de datos en las celdas.
En muchas ocasiones se deben introducir datos que han de cumplir unos criterios o siguen un cierto patrón,
por ejemplo podemos tener una base de datos con una columna donde se debe especificar el precio y sa-
bemos que los precios deben ser números (con o sin decimales) y que están en un rango de valores posi-
bles, además de ser evidentemente números positivos, o podemos tener una columna de fechas de pedi-
dos, donde la fecha ha de ser una fecha del año actual, etc.
Con la validación de datos podemos seleccionar una celda o rango de celdas y especificar que tipo de datos
son los permitidos en dichas celdas. Se pueden restringir los datos para que sean de uno de los tipos si-
guientes: números enteros, números decimales, fechas, horas, texto, o bien solo valores seleccionables de
una lista de referencia creada previamente. Así mismo se pueden definir limites a dichas entradas, por
ejemplo limitar el rango numérico o de fechas aceptable, o si son entradas de texto, limitar el número de
caracteres admisibles. Para especificar los límites se puede usar valores (constantes), referencias a celdas
o fórmulas.
Para llevar a cabo la operación de restricción primero debemos seleccionar las celdas que deseamos res-
tringir y después en la ficha Datos > Herramientas de datos > seleccionar la opción "Validación de datos"
que lanza el siguiente cuadro de dialogo:
El dialogo "Validación de datos" se divide en tres fichas o pestañas: Configuración, Mensaje de entrada y
Mensaje de error: Cada una de ellas con cometidos o funcionalidad específica, pero complementaria a las
otras.
Configuración
Es la pestaña más importante. Aquí es donde se debe seleccionar el tipo de dato permitido y las limitacio-
nes adicionales que se pueden aplicar sobre dicho tipo. Las limitaciones son específicas para cada tipo y
aparecerán cuando se seleccione el tipo. En la misma existen dos casillas de verificación:
Omitir blancos. Determina el tratamiento de los valores en blanco (nulos), es decir el comportamien-
to en el caso de querer dejar la celda vacía después de haber comenzado a introducir datos. Solo si
esta casilla esta activada, nos permitirá dejar la celda en blanco.
Nota: También nos permitirá dejar la celda en blanco aunque no esté habilitada la opción "Omi-
tir blancos" en el caso de escribir en una celda en la que previamente no había nada, para ello
basta con pulsar la tecla ESC.
Aplicar estos cambios a otras celdas con la misma configuración: Cuando estamos modificando una
regla de validación existente podemos decirle a Excel si queremos que aplique también los cambios
a todas las celdas dentro de la hoja actual que tengan la misma regla de validación que la de la cel-
da que estamos modificando, lo que resulta bastante útil, dado que no tenemos que seleccionar,
previamente al cambio, todas las celdas afectadas, con la posibilidad además de olvidarnos alguna.
Nota: Los mensajes de validación de datos sólo se muestran al introducir manualmente los da-
tos de uno en uno, es decir de celda en celda. Por tanto no se muestran al pegar valores co-
piados de otra celda, aunque sea de una celda individual, ni por supuesto en copiados y pega-
dos de múltiples celdas.
Mensaje de entrada
Esta pestaña nos facilita una opción complementaria que consiste en hacer aparecer un mensaje descripti-
vo en la celda con validación en el momento en que nos posicionamos en dicha celda para escribir. El obje-
tivo es servir de guía en la introducción de datos clarificando con más o menos detalle las condiciones que
deben cumplir los datos permitidos.
Es mensaje aparece como una pequeña nota de color amarillo en la parte inferior de la celda, un poco por
debajo de esta, como en el siguiente ejemplo.
Mensaje de error
Después de introducir datos no permitidos en una celda con validación Excel lanza un mensaje para adver-
tirnos del error, siempre que este activada la opción “Mostrar mensaje de error si se introducen datos no
validos”, que se halla en esta pestaña. Si esta seleccionada esta opción, pero no hemos escrito nada en el
cuadro Titulo, ni en el cuadro “Mensaje de error” Excel crea un mensaje de error genérico.
Es conveniente construir nuestro propio mensaje cuando pueda haber lugar a ambigüedad con el mensaje
predeterminado o bien porque queremos ser muy específicos respecto a lo permitido y no permitido en di-
cha celda.
En la lista desplegable “Estilo” podemos seleccionar el tipo de mensaje o mejor dicho la severidad de la
restricción. Con la opción “Grave”, la restricción es estricta, no se puede escribir en la celda ningún dato que
no cumpla la regla de validación. Las dos opciones restantes, "Advertencia” e “Información” también lanzan
el mensaje de error, pero Excel nos permite a voluntad propia continuar o no con los datos incorrectos, es
decir nos permite aceptar casos especiales para los cuales no se debe aplicar la regla de validación. Estas
dos últimas son idénticas en funcionalidad, simplemente varia el icono del mensaje y el número de botones,
cada una de ellas se adapta mejor a ciertas situaciones.
En Validación de datos hay dos casos especiales, la validación de tipo "Lista" y la "Personalizada".
Validación de Lista
La validación a través de una lista nos permitirá escribir solo un valor de los que estén en una cierta lista,
cuyo rango de referencia (posición) debemos escribir el cuadro Origen al crear la regla. Una vez creada la
regla, la validación funciona exactamente igual que el resto de casos anteriores, es decir nos posicionamos
en la celda y escribimos el valor deseado. No obstante en este caso existe la casilla opcional "Celda con
lista desplegable", que caso de activarla en cada celda, en el momento de situarnos en la misma, nos pre-
sentara en la parte derecha de la celda y fuera de esta una flecha que hace aparecer la lista con todos los
valores que tiene la lista Origen y en el mismo orden en el que están en dicha lista, por lo cual no tenemos
porque acordarnos de ningún valor.
La Lista puede ser un rango vertical u horizontal de celdas, pero de una sola fila o columna respectivamen-
te. Los datos pueden ser texto, números, fecha u horas, y en cada caso los tratara como tales cuando se
escriban en la celda correspondiente. Al introducir datos no se distingue entre mayúsculas y minúsculas.
Nota: La validación tipo "lista" solo admite como rango lista un rango que este en la misma hoja
donde se usará para validar. No obstante esta restricción es fácil de superar, basta con crear
un rango con nombre y así la lista puede estar en cualquier otra hoja del libro o en otro libro di-
ferente.
Validación Personalizada
Este tipo de validación nos permite construir reglas verdaderamente potentes y sofisticadas y ajustarlas a
cualquier necesidad por muy compleja o exigente que esta sea.
Una regla de validación Personalizada consiste en una fórmula cuyo resultado es un valor lógico. Después
de haber introducido los datos se evalúa la fórmula, y solo en caso de dar VERDADERO, se admitirán di-
chos datos escritos.
La fórmula puede ser tan sencilla o compleja como se quiera y se introduce de la misma forma en que se
escribe una fórmula en una celda, es decir, primero el signo igual y después la expresión con los operado-
res o funciones que sean necesarios.
Esta opción es un mecanismo muy potente y versátil, dado que por ejemplo no permite la introducción de
ciertos datos, si previamente no se han escrito otros, o introducir un dato si y solo si no se ha introducido
otro relacionado o viceversa, etc.
Ejemplos de reglas de validación personalizada:
Ejemplo 1: Supuesta una base de datos en la que existen entre otros los campos "Fecha pedido" y "Fecha
de entrega". Se desea que nunca se admita como "Fecha de entrega" una fecha que sobrepase en más de
30 días a "Fecha pedido" y que en ningún caso sea menor que la "Fecha de entrega". Si "Fecha pedido"
estuviese por ejemplo en la columna D de nuestra base de datos, la regla para las celdas de "Fecha de
entrega" seria:
Permitir
Fecha
Datos
entre
Fecha inicial:
=D2
Fecha final
ProInf.net, SCP Página 56
667 551 698
www.proinf.net
Excel 2010 Avanzado
=D2+30
Ejemplo 2: Supuesto una base de datos con, entre otros, los tres siguientes campos: "Precio Compra", en
columna B, "Portes" en columna C y "Precio Venta" en columna D. Se desea controlar que los valores en
"Precio Venta" nunca sean menores que la suma de los valores "Precio Compra" + "Portes" de su misma
fila. Para ello la regla de validación de las celdas de "Precio Venta" debería de ser la siguiente:
Permitir
Decimal
Datos:
mayor o igual que
Mínimo
=B2+C2
Ordenación
Excel provee varias herramientas para ordenar una base de datos o tabla. Todas ellas están situadas en la
ficha Datos, grupo “Ordenar y filtrar”.
Si se desea ordenar de forma rápida y sencilla por cualquier campo de la base de datos, basta con situarse
en cualquier registro de dicho campo y clicar en los botones “Ordenar de A a Z” para orden ascendente o
“Ordenar de Z a A” para orden descendente.
Si se quiere ordenar por más de un campo a la vez, o utilizar opciones especiales, se debe clicar el botón
“Ordenar” que hará aparecer el cuadro de dialogo Ordenar, en el cual podemos poner hasta un total de 64
condiciones de ordenación.
Para cada campo, tenemos la opción de ordenarlo según una de las cuatro posibilidades existentes en el
desplegable “Ordenar según”, que son: Valores (la más usual), Color de celda, Color de fuente e Icono de
celda.
Para cada criterio, la lista desplegable “Criterio de ordenación” nos presentará diferentes posibilidades, que
dependerán del tipo de datos del campo seleccionado. En el caso de los campos de tipo texto es particu-
larmente interesante la opción “Lista personalizada…” que nos permite hacer un orden jerárquico en base a
una lista personalizada que hayamos definido previamente.
Existen además algunas opciones adicionales que podemos usar. Para
hacerlo basta con hacer clic en el botón “Opciones…” del cuadro de dia-
logo Ordenar, lo cual hará aparecer a su vez el cuadro de dialogo "Opcio-
nes de ordenación", con las siguientes posibilidades:
La principal y más importante opción del cuadro anterior es la posibilidad
de distinguir entre mayúsculas y minúsculas en la ordenación.
Ordenar de arriba hacia abajo se refiere a ordenar por registros (filas).
Ordenar de izquierda a derecha se refiere a ordenar por campos (colum-
nas), es decir mueve columnas enteras a basándose en el nombre del
campo.
La mayor parte de opciones de ordenación también aparecen al aplicar la opción Filtro en el desplegable
que se crea en la cabecera de cada campo de la base de datos.
Filtro
Para poder usar el "Filtro" o "Autofiltro" basta ponerse en cualquier celda de un rango de datos y Excel se
encargara de seleccionar todo el rango continuo y poner en la primera fila de cada campo del rango un des-
plegable. Dicho desplegable es similar pero con opciones especificas en función del tipo de datos de cada
columna. El desplegable, además de las opciones de filtrado propias del campo muestra una lista ordenada
ascendentemente con todos los valores distintos que contiene dicho campo. En la figura siguiente podemos
ver un ejemplo para un campo de tipo texto llamado POBLACION.
Para filtrar por un valor o valores concretos basta con deseleccionar la casilla (Seleccionar todo) y seleccio-
nar las casillas de los valores deseados.
Una vez aplicado un filtro a un campo, en la cabecera de dicho campo y como indicativo de que los datos
están filtrados para dicho campo, aparece un pequeño embudo. Así mismo en la parte izquierda de la barra
de estado, si esta visible, veremos un mensaje que nos dice cuántos registros respecto al total cumplen el
criterio o criterio seleccionados, que son los que quedan visibles.
Se puede filtrar por uno, varios o todos los campos a la vez. Al filtrar dos o más campos se dice que se usan
criterios tipo "Y", dado que los registros visibles han de cumplir los criterios del primer campo y los del se-
gundo y así sucesivamente.
Después de haber aplicado un filtro es posible ordenar los datos filtrados utilizando la opción existente en el
propio desplegable.
Filtro Avanzado
El filtro avanzado es una herramienta más potente que permite sobrepasar las limitaciones del "Filtro" auto-
mático. Con Filtro Avanzado podemos:
Aplicar un número indeterminado de criterios sobre el mismo campo
Aplicar criterios "Y" , criterios "O" o ambos al mismo tiempo
Hacer referencia a celdas en la construcción de los criterios, lo cual nos permite una mayor versati-
lidad al poder configurar criterios semi-dinamicos.
Se pueden usar fórmulas en la construcción de los criterios.
ProInf.net, SCP Página 59
667 551 698
www.proinf.net
Excel 2010 Avanzado
Copiar los resultados que cumplan los criterios en otro lugar de la hoja de cálculo. A esto se le de-
nomina "Extracción de información".
Filtrado de registros únicos.
Poder definir los filtros en cualquier hoja de cálculo de cualquier libro, y no necesariamente en la
misma hoja de cálculo donde se hallan los datos
Definición de un filtro avanzado
Para crear un filtro avanzado debemos seleccionar un rango en la primera fila del cual pondremos los nom-
bres de los campos a filtrar y en las filas de debajo de esta los criterios que debe cumplir cada campo.
Todos los criterios que estén en la misma fila se denominan criterios "Y" y deben cumplirse todos a la vez
para un registro determinado.
Todos los criterios que estén en filas diferentes se denominan criterios "O" y son independientes de los crite-
rios de las demás filas.
Ejemplos:
Supuesta una base de datos sobre empleados que tiene los campos PROVINCIA y EDAD entre otros.
Criterios "O"
EDAD Todos los empleados cuya edad sea igual a 38 o a 40 o menor o
38 igual que 34 años.
40
≤34
Criterios "Y"
PROVINCIA EDAD Todos los empleados que sean de la provincia de Barcelona y
Barcelona >=40 tengan una edad igual o superior a los 40 años.
Criterios "O" e Y conjuntamente
Una vez creados los rangos que harán de filtro, se debe lanzar el cuadro de dialogo "Filtro avanzado" (ver
siguiente figura) para lo cual debemos usar la opción “Avanzadas” de la ficha Datos, grupo Ordenar y filtrar.
En dicho dialogo debemos rellenar la información de dos parámetros obligatorios:
Rango de la lista: rango donde esta situada la base de datos. Dicho rango debe incluir también la
cabecera o nombres de los campos.
Rango de criterios: rango donde hemos creado los criterios, incluyendo todo, nombre de campos y
criterios.
En ambos parámetros podemos usar nombres de rango en lugar de referencias, si tenemos creados dichos
nombres.
Además y de forma opcional podemos solicitar las opciones "Sólo registros únicos" y "Copiar a otro lugar"
La opción "Copiar a otro lugar" o extraer información es un caso particular de filtrado, donde además de
filtrar la información, Excel nos permite crear una copia de dicha información para poderla usar de forma
independiente en otros procesos, por ejemplo darle un formato especial, imprimirla, enviarla por mail a un
colaborador, etc.. La información así extraída solo se puede copiar en alguna parte de la misma hoja, si
intentásemos hacerlo en otra hoja o libro Excel nos mostraría el siguiente mensaje: “Sólo se pueden copiar
los datos filtrados en la hoja actual”.
Las opción "Registros únicos" solo presentara un registro por grupo para todo grupo de registros cuyos valo-
res sean exactamente idénticos en sus respectivos campos, es decir ocultara los duplicados.
Subtotales
Para cualquier base de datos Excel permite realizar un agrupamiento automático de valores para uno o
varios campos, creando un grupo cada vez que detecte un cambio de valor en los campos que le indique-
mos y añadiendo una o más funciones de totalización para cada grupo creado, usando para ello la función
SUBTOTALES.
La forma de proceder de Excel es la siguiente:
Crea un grupo de registros para cada conjunto de datos que tiene el mismo valor en el campo por el
cual se esta agrupando. Los registros no se mueven de posición (fila).
Por cada cambio en el valor del campo creara un nuevo grupo. Esto lleva implícito el hecho de que
en general es necesario tener debidamente ordenados los registros por el campo o campos por los
que queramos agrupar.
Una vez creados los grupos, añade una función SUBTOTALES para cada grupo, con la operación
elegida (Suma, Cuenta, Promedio, Producto, etc.) que resume el resultado de dicha operación so-
bre todos los registros de cada grupo, poniendo el resultado en una nueva fila encima o debajo de
cada grupo.
Por cada subtotal creado genera un nivel de esquema de forma que podamos ver/ocultar de forma
rápida los totales o totales y detalles que los componen.
Creación de subtotales
RESUMEN VENTAS ANUALES
NOMBRE APELLIDOS DIRECCION POBLACION PROVINCIA UNIDS P/U TOTAL
Lourdes Cruz Ros C/ Molas, 7 Barcelona BARCELONA 400 500 200.000
Rosa Perez Lopez C/ Balmes, 123 Barcelona BARCELONA 120 350 42.000
jope Puig Ros C/ Muntaner, 546 Barcelona BARCELONA 275 350 96.250
Adela Sanchez Lopez C/ Balmes, 123 Barcelona BARCELONA 325 350 113.750
Juan Sanchez Lopez C/ Balmes, 123 Barcelona BARCELONA 325 750 243.750
Juan Cruz Ros C/ Verano, 23 Sabadell BARCELONA 450 500 225.000
Carlos Puig Ros C/ Muntaner, 456 Sabadell BARCELONA 510 1.000 510.000
Eva Closgomerzindo C7 Gelabert, 13 Bordils GERONA 700 750 525.000
Adela Coll Marti C/ Cereza, 78 Bordils GERONA 310 500 155.000
Jose Coll Marti C/ Cerza, 78 Bordils GERONA 550 750 412.500
Adela Martin Marti C/ Gilabert, 12 Bordils GERONA 600 500 300.000
Adela Saez Pla C/ Soledad, 88 Bordils GERONA 275 500 137.500
Fermin Saez Pla C/ Soledad, 88 Bordils GERONA 220 750 165.000
Pedro Rodenas Gomez C/ Ajo, 45 Figueres GERONA 400 1.000 400.000
Fermin Romenas Gomez C/ Ajo, 45 Figueres GERONA 275 1.000 275.000
Eva Clos Reig C/ Sol 23 Reus TARRAGONA 400 1.000 400.000
Jose Clos Trull C/ La Luna, 145 Reus TARRAGONA 600 500 300.000
Lourdes Clos Trull C/ La Luna, 145 Reus TARRAGONA 345 350 120.750
Carlos Clos Reig C/ Sol, 23 Valls TARRAGONA 400 1.000 400.000
Juan Perez Lopez C/ Balmes, 123 Valls TARRAGONA 600 750 450.000
Para crear subtotales para un cierto campo y usando una cierta función se deben cumplimentar los siguien-
tes pasos:
Ordenar adecuadamente los datos por el campo o campos por los que se desea agrupar y crear
subtotales.
Lanzar el cuadro de dialogo Subtotales desde la ficha Datos, grupo Esquema, opción Subtotal.
Elegir el campo por el que agruparemos en el desplegable "Para cada cambio en...".
Nivel 2
Nivel 3
Para eliminar los agrupamientos de valores, sus subtotales y sus correspondientes esquemás, basta situar-
se en cualquiera de las celdas de la base de datos y clicar el botón "Quitar todos" de cuadro de dialogo Sub-
totales.
Ejemplos:
Supuesta la base de datos de la figura siguiente:
En dicha base de datos se han definido dos rangos con nombre para usarlos en la construcción de las fór-
mulas, dichos rangos son BD2_Criterio1 y BD2_Criterio2
En E24 se desea calcular la edad promedio de todos los hombres, para lo cual se debe usar la función
DBPROMEDIO, con los siguientes valores:
=BDPROMEDIO(B1:I21;"Edad";BD2_Criterio1)
En E25 se desea calcular la edad promedio de todas las mujeres que vivan en la provincia de BARCELO-
NA. Análogamente al caso anterior debemos usar la función DBPROMEDIO con la siguiente sintaxis:
=BDPROMEDIO(BDatos_2;"Edad";BD2_Criterio2)
Tablas
El concepto de Tabla se introdujo en la versión Excel 2003, aunque con el nombre de Lista. En Excel 2007
se han añadido algunas características adicionales que hacen que sea aún más útil y manejable.
Una Tabla es un conjunto de datos, con un nombre determinado, al que se le puede aplicar filtros, formatos,
ordenar, añadir o quitar filas o columnas, crear campos calculados y agregar funciones de totalización a las
diversas columnas de la tabla, trabajando siempre con dicho conjunto como una unidad.
Las Tablas solventan algunos problemas importantes que se producen con rangos de datos que represen-
taban un conjunto de información relacionado, y que son dinámicos, es decir van cambiando con el tiempo.
Así es normal que se añadan más registros al final de dicho rango, o se eliminen otros, y análogamente
ocurre con los campos o columnas.
En gráficos, fórmulas, tablas dinámicas, etc., asociadas a datos, al emplear referencias a rangos convencio-
nales de datos, no pertenecientes a una tabla, en la mayoría de los casos no se actualizan las referencias
existentes cuando el rango de datos cambia, por ejemplo no se detecta que se han añadido más datos al
principio o al final y por lo tanto el primer o último registro están en una posición diferente. La única forma de
evitar estas situaciones consiste en insertar filas después de la primera del rango o antes de la última, con
lo cual las referencias al rango si se adaptan a la amplitud de la nueva área de datos, o bien redefinir los
nombres de rangos existentes para que abarquen los nuevos registros o en el caso de fórmulas, modificar
las referencias a los rangos de origen, si en dichas fórmulas no se han empleado nombres de rangos. Al
trabajar con tablas, estas son como rangos dinámicos con nombre que se ajustan (amplían o reducen) se-
gún la evolución del área de datos.
Creación de tablas
Para crear una tabla debemos llevar a cabo los siguientes pasos:
1. Escribir los datos que formaran la tabla inicial, es decir las
los registros y campos (filas y columnas) correspondien-
tes.
2. Seleccionar dichos datos, incluyendo los encabezados e ir
a ficha Insertar, grupo Tablas, opción Tabla. Aparecerá el
cuadro de dialogo “Crear Tabla”.
3. Definir si la tabla tiene encabezados o no.
4. Pulsar el botón aceptar.
Utilización de tablas
Una vez creada una tabla, si nos posicionamos en cualquier celda de su interior nos aparecerá la ficha con-
textual “Herramientas de tabla-Diseño”, en la que existen diversas opciones para aplicar formato, crear una
tabla dinámica a partir de la misma, exportar datos, etc.. Hay dos opciones que conviene destacar:
Nombre de tabla: Sirve para darle un nombre significativo a la tabla, nombre que después se podrá
usar para gráficos, fórmulas, etc.
Convertir en rango: Si en algún momento no deseamos tener los datos como tabla o se ha de hacer
alguna modificación que la tabla no permita, podemos volver a convertir la tabla en un rango normal
de datos.
Modificador Función
#Esta fila Da el valor de la tabla que se encuentra en la misma fila que la celda donde
esta la fórmula, aunque se encuentre en distinta hoja.
Ejemplos
=NombreDeLaTabla[[#Modificador];[NombreCampo]]
=ComprasHorasExtras[[#Totales];[H.extras]]
VINCULACIÓN DE DATOS.
Ya sabemos que Excel nos permite hacer referencia a una celda o rango de celdas desde otra distinta, bien
usando fórmulas que sean directamente referencias o bien usándolas como argumentos de funciones, de-
biendo nosotros decidir el tipo de referencia a usar (relativa, absoluta o mixta). De esta manera tenemos
una vinculación dinámica de datos, de forma que cuando cambien los datos en la celda referenciada cam-
biaran también en todas las celdas o funciones donde se hallen referenciados.
Bajo el punto de vista de la vinculación de datos, Excel distingue dos casos posibles:
Referencias internas. Aquellas que establecemos entre celdas de la misma hoja de cálculo.
Referencias externas. Las que se establecen entre diferentes hojas del mismo libro o entre diferen-
tes libros.
Referencias externas
En muchos casos las referencias externas se hacen necesarias, bien porque no es posible por complejidad
poner todos los datos en la misma hoja y el tenerlos en hojas o libros distintos facilita su manipulación de
forma más sencilla, bien porque hay demasiados datos, bien porque son datos estructuralmente idénticos
pero pertenecen a distintas secciones de la empresa, o son de distinto grupo.
Para establecer referencias externas tenemos cuatro posibilidades distintas equivalentes:
Copiar la celda que queramos vincular, trasladarnos a la celda donde haremos la vinculación y allí usar la
ficha Inicio > Pegar > opción "Pegar vínculos" o bien opción "Pegado especial.." y pulsar el botón "Pegar
vínculos" situado en la parte inferior.
Copiar la celda que queramos vincular, trasladarnos a la celda donde haremos la vinculación y allí, usando
el menú contextual de las celdas, elegir la opción "Pegar" y después usando el desplegable "opciones de
pegado" que aparece al lado de la celda, seleccionar "Vincular celdas"
Para la tercera forma debemos tener visibles tanto la celda fuente, como la celda destino del vínculo, es
decir que ha de haber dos ventanas distintas, cada una mostrando una de las celdas que intervienen en la
operación. Una vez así basta con posicionarse en la celda en la que haremos la vinculación, escribir el
signo igual (=) y después clicar sobre la celda que queremos vincular, que está en la otra ventana.
La última forma consiste en escribir la referencia directamente en la celda de vinculación de forma manual.
Esta opción no es muy aconsejable por posibles errores en la sintaxis cuando los nombres de hojas o libros
sean largos.
Sintaxis de las referencias externas:
Entre hojas del mismo libro: NombreHoja!CeldaVinculada, p.e. =Enero!B13.
Entre libros diferentes: [NombreLibro]NombreHoja!CeldaVinculada: p.e.=[Fuente.xlsx]Enero!$B$13.
En el caso de referencias entre hojas del mismo libro, el movimiento de las hojas de posición o el cambio de
nombre no afectan para nada a las referencias. En el caso del cambio de nombre, Excel también cambia el
nombre automáticamente en todas las referencias en las que se mencione dicho nombre.
En este dialogo aparece una línea por cada libro distinto vinculado, no por cada celda vinculada, es decir
que aunque haya quince vínculos a un cierto libro, este solo aparecerá una vez en el listado "Modificar
vínculos".
Además de vínculos a otros libros de Excel aquí también aparecen todos los vínculos existentes que pudie-
ra haber a documentos de otros tipos, como por ejemplo un documento Word o una Base de Datos Access.
Hay dos casos especiales en los que un vínculo puede quedar desconectado:
Cuando cambiamos de nombre el archivo vinculado
Cuando movemos a otra carpeta o directorio el archivo vinculado
En ambos casos, al abrir el libro de destino donde está la referencia o referencias a dicho libro, Excel al no
poder actualizar los datos vinculados lanza el siguiente mensaje:
A fin de que podamos abrir directamente el dialogo "Modificar vínculos" y actualizar la nueva ubicación o
nombre de los archivos vinculados.
En botón Office > Opciones de Excel > Avanzadas > "Al calcular este libro" podemos elegir si queremos que
los vínculos se actualicen automáticamente al calcular el libro y "Guardar valores de vínculos externos" de
forma que aunque no podamos acceder a la información más reciente si tengamos una copia de los datos
obtenidos en la última actualización.
Resúmenes de datos
Caso de tener información diversa que tiene entre si una relación lógica, pero que esta dispersa en múltiples
hojas o libros, bien porque por falta de previsión se hizo así en su momento o bien porque es información
que pertenece a distintos grupos, divisiones o departamentos y cada uno la ha escrito en su propia hoja,
Excel nos permite "combinar" los valores de un conjunto de hojas del mismo libro o de libros distintos en una
hoja maestra u hoja de resumen, siempre que los datos presenten alguna relación o similitud estructural
entre ellos. Si no hubiera una mínima relación estructural no tendríamos otro remedio que hacer la hoja
resumen a mano, creando cada relación o referencia una por una.
Por "combinar" se debe entender hacer algún tipo de cálculo o resumen sobre los datos que se referencia-
ran ,es decir aplicar alguna función de contaje o estadística típica sobre esos datos, como por ejemplo la
Suma, el Promedio, la Desviación Estándar, etc.
En el caso de existir esas mínimas relaciones estructurales entre los datos a combinar Excel nos ofrece tres
posibilidades para resumir los datos de forma automatizada: Referencias 3D, Consolidación de datos por
posición y Consolidación de datos por categorías.
Referencias 3D
Para poder usar esta característica de Excel es necesario que se cumplan las siguientes condiciones:
Las hojas sobre las que se podemos crear referencias 3D deben ser del mismo libro de trabajo.
Las hojas a resumir deben de tener la misma estructura de datos.
Las referencias 3D no permiten elegir individualmente las hojas sobre las que operaran, solo permi-
ten elegir la primera y la última hoja que formaran parte de las referencias 3D, quedando automáti-
camente incluidas en dicha referencia todas las hojas que se encuentren entre ambas en cada mo-
mento. Así si se mueve o quita una hoja entre ellas, desaparecerá de las referencias y si por el con-
trario de mueve o agrega cualquier hoja entre la primera y la última hoja de referencia, entonces pa-
sara a formar parte de la referencia y será tenida en cuenta en los cálculos.
Un ejemplo típico de uso es cuando se tienen datos sobre ingresos, gastos, ventas, etc. con la misma es-
tructura pero en diferentes hojas, por ejemplo una hoja por cada tienda, delegación o producto, o bien agru-
pados en una hoja por cada mes o año contabilizado.
La sintaxis para crear una referencia 3D es :NombreHojaIncial:NombreHojaFinal!CeldaReferenciada
Supuesto tenemos una serie de hojas que son el resumen de ventas del año 2009 para las cuatro tiendas
de una cadena de electrónica e informática. Cada hoja contiene los datos resumen de una cierta tienda de
la cadena y tienen por nombre 2009_T1, 2009_T2, 2009_T3, 2009_T4
La forma de crear una referencia 3D sería la siguiente:
Situarnos en la hoja y celda donde queramos hacer la función de resumen. El resumen se puede
hacer en cualquier hoja y en cualquier celda, no tiene por qué coincidir posicionalmente con la celda
de las hojas que referencia. En nuestro caso crearemos una nueva hoja llamada "Ventas Totales"
nos posicionaremos en B6 y ahí haremos las referencias 3D aplicando la función SUMA.
En la celda seleccionada (B6) escribimos el signo igual (=), el nombre de la función (SUMA) y el pa-
réntesis de apertura de la función " ( ". Después del paréntesis, el nombre de la primera hoja a su-
mar, 2009_T1, seguido del operador rango (:) y el nombre de la última hoja a sumar, 2009_T4. Jus-
to a continuación, el símbolo admiración (!) y la referencia a la celda sobre la que queremos aplicar
el cálculo ( aquí B4). Una vez escrito todo realizamos la validación de la fórmula pulsando la tecla
Enter.
De esta forma en la celda B6 de nuestra hoja "Ventas Totales" aparecería el resultado de sumar todas las
celdas B4 de todas las hojas que haya entre 2009_T1 y 2009_T4, que aquí en total son cuatro.
La fórmula de B6 sería por tanto: =SUMA('2009_T1:2009_T4'!B4), según puede verse en la siguiente ilus-
tración.
Las funciones que podemos aplicar en fórmulas con referencias 3D son: SUMA, PROMEDIO, CONTAR,
CONTARA, MÁX, MÁXA, MÍN, MÍNA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, VAR, VARA,
VARP y VARPA.
Consolidación de datos
Además de resumir datos de forma manual o usando referencias tridimensionales si es posible, Excel permi-
te consolidar datos repartidos en diferentes hojas o libros, si los datos están dispuestos en forma de tabla o
listado y comparten una cierta estructura mínima común, incluso en el caso de no ser completamente igua-
les en cada hoja o hallarse en la misma posición.
Consolidar permite combinar los valores de hasta 255 hojas de cálculo en una única hoja final de resumen.
Por ejemplo, si tenemos información financiera o de ventas por cada división o tienda de nuestra compañía
en hojas o libros de trabajo separados, usando la opción Consolidar podríamos crear una hoja de cálculo
resumen que calcule los totales de los artículos correspondientes en cada libro de trabajo de cada división o
tienda.
Dependiendo de la estructura mínima común de los datos tenemos dos posibilidades de consolidación:
Consolidación por posición y Consolidación por categorías
Consolidación por posición
Para poder usarla solo es necesario que la información de los datos se halle situada en la misma posición
en cada una de las hojas referenciadas u hojas fuente.
La información no tiene por qué tener títulos o encabezados de fila y columna o los títulos no tienen por qué
ser igual, aunque es más práctico que existan esos títulos y sean los mismos en todas las hojas a seleccio-
nar, de esta forma el propio Excel usará los títulos para construir el rango resumen.
Como es la forma más sencilla de consolidación de las dos existentes también es la más fácil de realizar.
Para realizarla llevaríamos a cabo los siguientes pasos:
1. Crear la hoja de consolidación o resumen y situarnos en el lugar que queremos que aparezca el
primer resultado o título.
2. En ficha Datos > Herramientas de datos, pulsar el botón Consolidar lo que hace aparecer el dialogo
homónimo.
3. Elegir la función de resumen a utilizar en el desplegable Función. Las funciones existentes posibles
son: Suma, Cuenta (que corresponde a la función CONTARA), Promedio, Máx, Mín, Producto, Con-
tar Números (que corresponde a la función CONTAR), Desvest, Desvestp, Var y Varp.
4. En el cuadro de texto "Referencia" seleccionar el rango de datos de la primera hoja que queramos
consolidar. En dicho rango además del área de datos fuente podemos incorporar de forma opcional
los títulos o encabezados de fila y columna, siempre que sean idénticos en todos los rangos y así
Excel los usará para el rango de resumen.
5. Si los datos estuvieran en otro libro debemos pulsar el botón Examinar y localizar el libro y hoja
donde se hallen.
6. Una vez seleccionado el primer rango de datos, con o sin títulos, debemos pulsar la tecla Agregar
para añadirlo a la lista de referencias "Todas las referencias".
7. Añadir el resto de rangos de las otras hojas, para lo cual, dado que todos los rangos tienen la mis-
ma posición basta con hacer clic sobre la pestaña de la hoja para que ya aparezca en el cuadro de
texto "Referencia", después pulsamos el botón Agregar y así sucesivamente con cada rango-hoja.
8. Si queremos que Excel use los datos de la fila superior y/o de la columna izquierda de los rangos
seleccionados como títulos en la hoja resumen se deben de habilitar las casillas del grupo "Usar ró-
tulos en" sito en la parte inferior izquierda del dialogo "Consolidar"
9. Por ultimo existe la opción "Crear vínculos con los datos de origen", si no la activamos, Excel sim-
plemente creara una consolidación-resumen estático de los datos de origen, es decir que aunque
estos cambien, los cambios no se reflejaran en la hoja resumen, pero si activamos dicha casilla, Ex-
cel creara un vínculo con cada celda de datos, de forma que la hoja resumen reflejara siempre un
total actualizado de los datos.
Si activamos la opción "Crear vínculos con los datos de origen", Excel actúa de una forma similar a como lo
hace cuando creamos subtotales, es decir genera de forma automática un Esquema en el cual los elemen-
tos de primer nivel son los resultados de la función aplicada y los de segundo nivel los componentes de
cada resultado global. Al mostrar el segundo nivel de cualquier total veremos en una columna, entre los
títulos de fila y los datos, el nombre del libro de donde provienen los datos y en cada celda la fórmula con la
referencia al libro, hoja y celda de la cual toma el dato.
En la figura siguiente se muestra una consolidación por posición de cuatro tiendas de electrodomésticos y
electrónica de una cadena de almacenes. La consolidación se ha hecho sobre las hojas denominadas Bar-
celona, Hospitalet, Mataró y Tarragona, pertenecientes todas a un libro llamado ConsolidacionPorPosicion.
GRÁFICOS
Conceptos de gráficos
Para crear gráficos es necesario familiarizarse con los conceptos más comunes relacionados con los mis-
mos. Los más importantes son los siguientes:
Series: Una serie es un conjunto de datos o conjunto de valores relacionados que se disponen en
columna o en fila en la hoja de cálculo.
Categorías o Puntos: Una categoría o punto de datos es una agrupación de datos relacionados en
forma de fila o columna. Excel considera categorías todos los grupos de datos que no actúen como
series.
Valores: Cada uno de los valores individuales que componen una serie o categoría.
Rango de datos del gráfico: Es el conjunto de valores y rótulos de series y categorías que confor-
ma la información para genera el gráfico.
Series y Categorías son conceptos equivalentes, simplemente depende de la consideración que hagamos
respecto a la forma de ver los datos de las filas y columnas en que se basa un gráfico. Excel por defecto
considera como series de datos los grupos que abunden menos ya sean filas o columnas. Por ejemplo,
supuesta la siguiente tabla de datos, donde el rango de datos comprende desde B2 a E7 a partir del cual
generaríamos un gráfico:
Excel, por defecto, vería como series los grupos de datos en columnas, es decir: Móviles, MP3 y eReaders,
dado que hay menos que filas de datos; las filas de datos las vería como Categorías o Puntos de datos. No
obstante al hacer el gráfico podemos cambiar esta visión por defecto y considerar las filas como Series (An-
dalucía, Aragón, etc.) y las columnas como Categorías (Móviles, MP3 y eReaders).
Para cambiar series por categorías o viceversa debemos seleccionar el gráfico y en la ficha contextual Di-
seño, grupo Datos, pulsar sobre la opción "Cambiar entre filas y columnas".
210.000
Este es el caso inverso al anterior, Andalucia Aragón Canarias Catalunya Valencia
190.000
series en filas, categorías en co-
lumnas. Útil para comparar valores 170.000
de diferentes categorías para una 150.000
misma serie
130.000
110.000
90.000
70.000
50.000
Moviles MP3 eReaders
Tipos de gráficos
Existe una gran variedad de modelos de gráficos. Cada uno de ellos se adecua mejor a la representación de
determinado tipo de datos que otro, por lo tanto es conveniente conocer que tipos de gráficos existen y bajo
que formas de representación de datos son más adecuados.
Hay diversas formas de clasificar los gráficos, Excel lo hace en función de la forma en que el gráfico presen-
ta la información. Las categorías de gráficos de Excel están representadas en la siguiente figura:
Dic
Nov
Oct
Sep
Ago
Jul
Jun
May
Abr
Mar
Feb
Ene
5.000 10.000 15.000 20.000 25.000 30.000
Cilindro Cono 3D y Pirámide: Dentro de los de Columna o Barra encontramos los subtipos o variantes
Cilindro Cono 3D y Pirámide, idénticos en funcionalidad. Son una variación estética de los mismos. En algu-
nos casos pueden ser utilices para realzar o mejorar la presentación de los de Barras o Columnas, en espe-
cial los Cilíndricos.
Circular: Sirven para mostrar la distribución o aportación de cada valor de datos al total de la serie, es decir
relacionan las partes con el todo. Son útiles para comparar entre elementos de datos de la misma serie o
para destacar los elementos más significativos de la serie. Los datos (valores) se pueden representar de la
forma usual en valor absoluto (cantidad), como porcentaje o de ambas formas a la vez.
Son los gráficos más sencillos y por su naturaleza solo pueden mostrar una única serie de datos.
Ventas MP3
Valencia Andalucia
25% 13%
Aragón
20%
Catalunya
Canarias
26%
16%
Líneas: Representan los valores de cada serie de datos como puntos en una línea, por lo cual son útiles
para mostrar la continuidad entre puntos individuales de una serie dado que los valores están conectados
por las líneas. Especialmente prácticos cuando se trata de mostrar la tendencia de los datos a lo largo de un
intervalo de tiempo situado en el eje horizontal o de abscisas.
25.000
En Euros
20.000
15.000
10.000
5.000
Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic
Dispersión: Estos gráficos se caracterizan porque los dos ejes miden valores. Son útiles para determinar si
existe relación causa-efecto entre los valores de las series de datos que se representan. Son muy comunes
en representaciones de datos científicos.
En el siguiente ejemplo basado en dos series de datos (Gastos e Ingresos), se trata de ver si existe alguna
relación entre el incremento de gasto en publicidad y los ingresos obtenidos por las ventas de lo que se
publicita.
160
120
80
40
0
0 5 10 15 20 25 30 35
Gastos en miles de €
Subtipos de gráficos
Dentro de cada categoría de gráficos existen diversas opciones para ajustar mejor la representación de
datos. En las principales categorías (Columna, Barra, Área y Línea) existen las siguientes tres opciones o
tipos:
Agrupados: Son los que se generan por defecto. Comparan valores entre categorías y muestran
una columna por cada valor de la categoría dada, separando las categorías unas de otras.
Apilados: Solo presentan una columna por categoría, que incluye todos los valore de dicha catego-
ría. Los valores se van apilando, por lo que da una representación del valor acumulado de cada ca-
tegoría. Muestran o sirven principalmente para comparar categorías completas entre si, en lugar de
los valores individuales como hacen los agrupados.
Apilados 100%: Son similares a los Apilados, es decir solo muestran una columna por categoría,
pero en estos todas las columnas miden igual, ocupando toda la altura del "Área de trazado", por lo
que son útiles para comparar entre valores de la misma categoría y ver que con que porcentaje con-
tribuye cada punto de datos al total del grupo, es decir de la categoría.
Ejemplos
Agrupados:
De este tipo hemos visto ya varios ejemplos en apartados anteriores, uno de ellos en la definición de gráfi-
cos de columna.
Apilados
600
500
En miles de Euros
400
300
200
100
0
Andalucia Aragón Canarias Catalunya Valencia
Apilados 100%
80%
60%
Euros
40%
20%
0%
Andalucia Aragón Canarias Catalunya Valencia
Gráficos dinámicos
Para hacer un gráfico realmente dinámico, sin necesidad de reajustar la propiedad " Rango de datos del
gráfico" cada vez que este se expande o contrae, es práctico y aconsejable convertir primero el rango en
tabla y después basar el gráfico en dicha tabla. De esta forma, cada vez que ampliemos o reduzcamos la
tabla el gráfico se ajustara automáticamente. Además como beneficio añadido tenemos la posibilidad de
usar los filtros que automáticamente se generan al crear una tabla.
Gráficos Combinados
Se denominan gráficos combinados a aquellos que representan una o más series de datos de una forma y
el resto de otra, por ejemplo en un gráfico de tres series (Comparas, Ventas, Balance), dos podrían estar en
columna y la tercera en línea. Ver ejemplo en Figura: Ejemplo de gráfico combinado
Eje secundario
En ocasiones ocurre que alguna de las series tiene
un conjunto de valores muy diferente (mucho mayor
o menor) al del resto, por lo que para que se vea
mejor conviene crear un eje de valores propio para
dicha serie. Para crear un eje de valores propio o eje
secundario para una cierta serie hay que:
Seleccionar cualquier punto de datos de la
serie.
Lanzar su menú contextual haciendo clic con
el botón secundario del ratón.
Elegir la opción: "Dar formato a la serie de
datos…." que nos llevara el cuadro de dialo-
go "Formato de la serie de datos", Opciones
de la serie.
Situarnos en el apartado Trazar serie en: y
clicar sobre la opción "Eje secundario"
Finalmente pulsar el botón "Cerrar".
Figura: Ejemplo de gráfico combinado.
800
600
400
200
0
A1998 A1999 A2000 A2001 A2002 A2003 A2004 A2005 A2006 A2007
-200
50 -30
-50 -50
A1998 A1999 A2000 A2001 A2002 A2003 A2004 A2005 A2006 A2007
Opciones Especiales
En muchos gráficos para aprovechar mejor el espacio del Área del gráfico o del Área de trazado es con-
veniente hacer determinadas modificaciones opcionales que consiguen dicho propósito. Entre las principa-
les posibilidades, para tal tarea, tenemos las siguientes:
Eje de valores
En el eje de valores (eje Y o eje vertical) hay una serie de posibilidades para ajustar mejor el mínimo, máxi-
mo, marcas de gradación o reducción del ancho de las unidades de visualización. Accedemos a estas op-
ciones usando del botón secundario del ratón encima de cada uno de los ejes y eligiendo la opción "Dar
formato al eje".
Opciones del eje
Mínima, Máxima: Para determinar con precisión el rango de valores del eje y ajustarlo para eliminar
espacio vacío. Es muy adecuado cambiarlos sobre todo en la Mínima cuando el valor mínimo es
bastante mayor que cero, dado que por defecto se traza desde cero.
Unidad mayor, Unidad menor: Determina el intervalo entre cada línea divisoria del gráfico y la si-
guiente, para las líneas principales y secundarias respectivamente
Valores en orden inverso: Iinvierte el eje de ordenadas, de forma que los valores más altos apare-
cen en la parte inferior del gráfico y viceversa. Esta opción es útil si todos los valores son negativos
y queremos ver los valores absolutos de cada punto.
Unidades de visualización: Es útil para reducir la escala de los datos del eje, lo que redunda en el
uso de menos espacio y más claridad.
El plano inferior cruza en: Cambia el punto normal de intersección de los ejes X e Y. es decir
mueve el eje X. Es útil cuando hay valores menores que cero.
Automática: por defecto. El punto de intersección es el valor 0.
Valor del eje: para posicionar el plano X en el punto de intersección deseado.
Valor máximo del eje: -Eje de categorías(X) cruza en máximo valor. Hace que el eje de cate-
gorías se situé siempre en el máximo valor del eje Y, o sea en general lo desplaza a la parte
superior. Esta opción anula a la opción: “Eje de categorías(X) cruza en:”
Eje de categorías
Intervalo entre etiquetas: Para reducir el número de etiquetas o nombres de categoría que apare-
cen. Es útil cuando hay muchas categorías, o sus nombres son muy largos y no se pueden ver bien.
Poniendo el valor 1 salen todas las etiquetas, poniendo 2 se muestran la mitad, poniendo 3 se
muestra un tercio y así sucesivamente.
Distancia de la etiqueta desde el eje: Fija la distancia entre el eje y los rótulos de categorías. Nú-
meros bajos hacen que rótulos y eje estén muy próximos, números altos lo contrario. El valor más
adecuado suele ser 100.
El eje vertical cruza en: Por defecto el eje de valores se sitúa en la parte izquierda del eje de cate-
gorías, es decir al inicio del mismo, con esta opción podemos desplazar el eje de valores a cualquier
punto del eje horizontal o de categorías. Esta opción es relevante en gráficos de tipo científico, don-
de el origen de coordenadas se suele situar en medio del plano que representa el gráfico, en lugar
de en la parte inferior izquierda como hace Excel por defecto. Es muy similar a la opción del eje ver-
tical "El plano inferior cruza en:".
A mayor valor mayor separación entre categorías y menor anchura de cada marcador del gráfico
Si valor igual a 0, entonces todas las columnas (marcadores) toman el mismo ancho, quedan juntas y ocu-
pan todo el "Área de trazado". La anchura de cada columna viene dada por la fórmula: ancho ("Área de
trazado")/ (núm. de columnas)
Ambas opciones (A y B) están interrelacionadas, porque se distribuyen el espacio existente en el Área de
trazado.
Barras de error
Las barras de error sirven para mostrar gráficamente la cuantía del error en los datos que representan pro-
yecciones o extrapolaciones o bien datos obtenidos de forma experimental en tareas científicas. Las barras
de error pueden aplicarse individualmente a cualquier serie de datos.
Los tipos de gráficos que admiten barras de error son los de Columnas, de Barras, de Líneas, de Áreas, de
tipo XY (Dispersión) y de Burbujas. Estos dos últimos tipos admiten barras de error tanto para los valores X,
como para los valores Y, o ambos a la vez.
Para agregar barras de error a un gráfico se deben seguir los siguientes pasos:
Seleccionar el Área del gráfico si se quiere crear barras para todas las series, o bien marcar un va-
lor o punto de datos de la serie individual a la que se quiera añadir barras de error.
En ficha contextual Presentación, Grupo Análisis, clicar sobre la opción Barras de error, que hará
aparecer el cuadro de dialogo "Formato de barras de error" donde están todas las posibilidades re-
lacionadas con el tema.
Además de marcar la dirección de las barra y el estilo, se debe seleccionar el método a usar para determi-
nar la cuantía del error, en el conjunto de opciones excluyentes marcado como "Cuantía de error", en el que
existen las siguientes opciones:
Valor fijo: Se debe poner un cierto valor de error. Dicho valor será el mismo para todos los puntos o
datos de la serie, es decir la barra de error tendrá el mismo tamaño (ancho o alto) para todos los da-
tos.
Porcentaje: Análogo al anterior, es decir debemos escribir el porcentaje. La longitud de la línea de
error es el % elegido, pero con respecto al valor de que se trate, es decir que cada dato tendrá una
Linea de diferente tamaño, excepto si hay valores iguales.
Desviación estándar: Creara barras de error cuya longitud será el valor que se ponga. El valor o %
que se ponga se crea usando todos los puntos de datos como cuantía del error, ya que se hace su
desviación estándar, por ejemplo el 3% de la desviación estándar: Se crean líneas verticales del
mismo tamaño, que sirven de guía para ver como esta de separado cualquier punto respecto a la
media.
Error típico: Utiliza el error estándar o típico de los valores trazados como cuantía del error de cada
punto de datos. El error típico o estándar es una medida de la cuantía de error en el pronóstico del
valor de y para un valor individual de x. Todos los puntos de datos de la serie mostraran el mismo
tamaño (alto o ancho).
Personalizado: En esta opción se deben especificar uno o dos conjuntos de valores, bien introdu-
ciéndolos manualmente, separados por punto y coma, bien especificando el rango de la hoja de
cálculo que se usara como valores de la cuantía del error tanto para los valores de error positivo,
como para los de error negativo. Para ambas opciones debemos clicar primero el botón "Especificar
valor". También es posible incluir fórmulas en rango de datos para los errores
Líneas de tendencia
A partir de los datos representados en un gráfico podemos analizar que tipo de función matemática o curva
descriptiva han seguido los mismos en el pasado (es decir con los datos existentes) y también extrapolar o
hacer previsiones de evolución futura de dichos datos. Para ello, y a nivel gráfico, Excel nos permite probar
y ajustar los datos a una línea, función exponencial o logarítmica, un polinomio de grado máximo 6, una
función potencial o usar medias móviles. Se trata de probar que tipo de curva describe mejor los datos, es
decir la que mejor se adapta a los datos existentes.
Se pueden agregar líneas de tendencia en la series de datos de gráficos de Columnas, de Barras, de Lí-
neas, de Áreas 2D no apiladas, de burbujas y de Dispersión (tipo XY), siendo muy usuales en los del ultimo
tipo.
Una opción importante de las líneas de tendencia o regresión es que se pueden extrapolar hacia adelante o
hacia atrás de los límites de los datos para poder hacer predicciones o previsiones futuras. Esto se lleva
acabo con la opción Extrapolar, señalando el número de periodos hacia Adelante o Hacia atrás. Un periodo
es el siguiente valor Adelante o Hacia Atrás en el eje X de la serie de datos representada
Otras opciones interesantes son:
Señalar intersección: Sirve para definir el punto en que la curva debe intersectar con el eje "Y".
Presentar ecuación en el gráfico: Nos muestra en el gráfico la ecuación matemática que describe
la curva, excepto en el caso de medias móviles que no existe tal ecuación. En la ecuación se ven
tanto la variable X, como sus coeficientes o exponentes (caso de haberlos).
En el siguiente ejemplo se puede ver una proyección de resultados de ventas esperados extrapolada 2 pe-
riodos hacia delante, es decir dos años. Se han hecho dos líneas de tendencia (la lineal de color verde y la
polinómica de color rojo) para ver cual se ajusta mejor a los datos existentes: En ambos casos se muestra
también la ecuación que describe cada línea.
Ventas Totales
Resumen Ventas Anuales Lineal (Ventas Totales)
Polinómica (Ventas Totales)
14.000.000
12.000.000 y = 121783x2 - 5E+08x + 5E+11
10.000.000
8.000.000 y = 2E+06x - 3E+09
6.000.000
4.000.000
2.000.000
0
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
Los mismos cálculos se pueden llevar a cabo de forma cuantitativa con ciertas funciones estadísticas de
Excel. Si deseamos hacer estimaciones lineales de datos existentes, lo adecuado es usar la función ESTI-
MACION.LINEAL y caso de querer hacer estimaciones no lineales (exponenciales, polinómicas, etc.) debe-
remos usar la función ESTIMACION.LOGARITMICA. Ambas funciones trabajan sobre datos existentes y
sirven para ajustar curvas y extraer estadísticas de regresión.
Si lo que deseamos es hacer extrapolaciones o proyecciones hacia el futuro, lo adecuado es utilizar TEN-
DENCIA o PRONOSTICO, para estimaciones de tipo lineal, o CRECIMIENTO para estimaciones de tipo no
lineal.
En el caso A. si queremos ver representados en el gráfico los datos ocultos, basta con activar la casilla
"Mostrar datos en filas y columnas ocultas"
El caso B es relevante cuando el gráfico es de Líneas, dado que por defecto la opción activa es "Rangos"·
que si no hay datos no los representará, por lo que la línea queda interrumpida. Si elegimos la opción "cero"
Excel hará la línea continua, pasando por cero para los puntos sin datos
Si lo que queremos es que no se interrumpa la línea, pero que no nos dibuje el dato que falta, elegiremos la
opción "Conectar puntos de datos con línea".
Otra forma de conseguir el mismo efecto de dibujar la línea continua incluso para el caso de datos que fal-
tan, es rellenar las celdas vacías con #N/A (not available), valor que deberemos escribir a mano. Así al no
estar el dato disponible lo pasa por alto, pero mantiene la continuidad de la serie, dado que la celda no esta
ni vacía ni oculta. Esta última opción tiene las ventajas de una mayor claridad y el hecho de que no debe-
mos cambiar la configuración por defecto en "Configuración de celdas ocultas y vacías".
TABLAS DINAMICAS
Concepto y Componentes
Una tabla dinámica es una potente herramienta de análisis de datos que nos facilita Excel que permite pre-
sentar resúmenes de los datos de una base de datos de una forma fácil y rápida. Una tabla dinámica nos
permite clasificar, filtrar y organizar dinámicamente unos determinados datos para enfatizar diferentes as-
pectos. En el apartado organizativo nos permite presentar los campos de la base de datos casi de cualquier
forma deseada.
Una tabla dinámica es similar al concepto de referencias cruzadas, es decir se relacionan valores puestos
en filas y columnas con un valor numérico que está asociado a ambas y al que se le aplica una cierta fun-
ción matemática o estadística ( suma, promedio, máximo, mínimo, etc.).
Las tablas dinámicas representan un paso adelante en relación a otras herramientas de resumen y análisis
de datos, como son Consolidar o Referencias 3D, en el sentido de que nos permiten más posibilidades de
filtrado de datos, ordenación o agrupación. Poseen además una característica propia y única respecto a las
anteriores y es la posibilidad de generar nuevos datos y cálculos personalizados mediante las opciones de
campos y elementos calculados.
Supuesto tuviésemos la base de datos de la figura anterior, la cual la hemos convertido en una tabla con el
nombre de tblVentas, y usando el segundo método de creación de tabla dinámica, al clicar sobre el botón
"Resumir con tabla dinámica" nos aparecerá el cuadro de dialogo siguiente:
En el mismo ya aparece seleccionada y con su nombre el rango de la tabla en la cual estamos situados.
Caso de no ser esta la fuente de datos deseada tenemos la opción "Utilice una fuente de datos externa" que
nos permite usar datos de otros libros de Excel, conexiones Bases de Datos, como Access o conexiones a
fuentes de datos ODBC (Object Data Base Conectivity).
Una vez seleccionada la fuente de datos debemos decirle a Excel donde colocar la tabla dinámica: en una
hoja de cálculo nueva o en una hoja de cálculo ya existente en el libro, la cual elegiríamos de la lista des-
plegable de la parte inferior derecha del cuadro de dialogo.
Finalmente clicando el botón Aceptar, se nos creara una hoja con el siguiente aspecto:
En la hoja de tabla dinámica aparecen dos elementos diferenciados: a la izquierda, la zona donde se creara
la tabla dinámica y se mostraran los resúmenes que se realicen, enmarcada en un recuadro con el titulo
"Tabla dinámica1", y a la derecha el panel "Lista de campos de la tabla dinámica".
Una vez creada la tabla dinámica, vamos a ver en los próximos apartados las acciones u opciones más
interesantes que se pueden llevar a cabo con ella.
ProInf.net, SCP Página 93
667 551 698
www.proinf.net
Excel 2010 Avanzado
Filtrar Información
Se puede filtrar la información de todos los campos que se quiera, de forma análoga a como se hace en una
base de datos o tabla. El filtrado de información se puede llevar a cabo solo para campos que aparezcan en
cualquier área que no sea la de " Valores". En la parte derecha del nombre de cada campo aparece un
botón en forma de flecha que representa el desplegable con todas las opciones posibles tanto de ordena-
ción como de filtrado que existen para dicho campo Las opciones dependerán en cada caso del tipo de
campo y del área en el que se halle. Entre las más usuales están:
Filtros de Campo
Es la única opción que existe para las aéreas de Filtro de informe, Rótulos de fila y Rótulos de columna, el
resto de posibilidades solo están disponibles para las secciones de Rótulos de fila y Rótulos de columna.
El filtro de campo nos presenta la lista de todos los valores de dicho campo. Se pueden seleccionar discre-
cionalmente los que se quieran mostrar simplemente activando la casilla que esta a izquierda de cada ele-
mento. Por ejemplo: para Producto: Arenque ahumado, Caviar rojo, Cerveza tibetana Barley.
Filtros de etiqueta
Filtros disponibles solo para campos de tipo texto. Por ejemplo, se puede filtrar por todos los elementos
cuyo valor comience por t* o que contenga unas ciertas silabas 'alg', o que sea mayor o igual que G
Filtros de fecha
Tienen un amplio abanico de posibilidades para filtrar de forma general fechas que cumplan alguno de los
criterios existentes, como por ejemplo: todas las fechas del periodo trimestre1, Ayer, Hoy, Próxima semana,
Próximo mes, Este año, Año pasado, Hasta la fecha. El filtro "Hasta la fecha" mostrara todos los registros
cuya fecha sea del año actual y menor o igual a la fecha del dia de hoy.
Filtro personalizado
Esta dentro de los filtros para fechas y sirve para acceder a algunos de los filtros predefinidos que no apare-
cen por defecto en el desplegable inicial. Los filtros predefinidos extras que aparecen aquí son: "no es igual
a", "anterior o igual a", "posterior o igual a", "no está comprendido entre"
Filtro Diez mejores: Esta opción esta dentro de filtro por valor:
Se puede tener aplicado un filtro por valor: por ejemplo que solo se vea Casco y Guantes y aplicar un Filtro
10 mejores.
El filtro 10 mejores actúa sobre los totales de los elementos, estén estos o no ocultos y solo muestra los que
cumplen el criterio. Un ejemplo: si solo están visibles los productos Casco y Guantes pero Filtro los 2 supe-
riores, mostrara dos que estaban ocultos y que son los superiores: RoadTDF y TourItalia, es decir el "Filtro
10 mejores" domina sobre el filtro por valor de campo, si se aplican sobre el mismo campo en el área de
"Rótulos de fila" o "Rótulos de columna".
Ordenar Información
Orden Manual
Consiste en arrastrar el elemento de campo, de fila o columna, por su cabecera para colocarlo en la posi-
ción deseada. Para ello debe de estar habilitada la opción "manualmente" de la ficha Herramientas tabla
dinámica > Opciones > Ordenar . Si estamos situados en los títulos (elementos) de un campo al clicar Or-
denar aparecerá el cuadro de dialogo Ordenar (NombreDeCampo) con la opción; "Manualmente…".
Orden automático
Podemos ordenar según cuatro posibilidades o aéreas:
1. Ordenar por elementos (títulos) de campo: área "Rótulos de fila" o "Rótulos de columna".
2. Ordenar por valor de los elementos: área " Valores".
3. Ordenar por totales de fila o columna.
4. Ordenar en subgrupos.
Para ejecutar cualquiera de estas posibilidades basta situarnos en el área correspondiente y pulsar el botón
"Ordenar de A a Z" u "Ordenar de Z a A" de la ficha "Herramientas de tabla dinámica" > Opciones > Orde-
nar. En esta misma ficha y grupo al pulsar el botón Ordenar y según el área donde estemos posicionados,
aparecerá un cuadro de dialogo con más opciones o posibilidades en relación al orden en dicha área o po-
drá aclararnos dudas respecto a como va a ordenar Excel.
Ordenar por elementos (títulos) de campo: Rótulos de fila o columna
Existen dos comportamientos posibles, dependiendo de si se habilito o no la casilla "Usar listas personaliza-
das al ordenar" de "Opciones de tabla dinámica" > "Totales y filtros" > Ordenación. Si no habilitada, simple-
mente aplicara el orden Ascendente o Descendente según el alfabeto, o valor del número o fecha, si habili-
tada, entonces inicialmente debemos elegir una lista personalizada, que es la que aplicara para ordenar los
elementos. La lista personalizada nos sirve aquí para ordenar los datos según un orden jerárquico predefi-
nido.
Por ejemplo, podemos ordenar el campo Producto alfabéticamente por el nombre de sus elementos o bien
ordenarlo según una lista previamente predefinida, donde los productos se han organizado por nombre se-
gún el volumen de ventas que hubo en años anteriores.
Figura: dialogo Ordenar por elemento Figura: dialogo Ordenar por valor
Figura: dialogo Agrupar para fechas Figura: dialogo Agrupar para Números
Cuando existen esquemas, sus niveles y subniveles se pueden manejar de diversas formas:
Usando los botones gráficos que se pueden habilitar en ficha Herramienta de tabla dinámica > Op-
ciones > Mostrar u ocultar > Botones +/-
Usando la opción "expandir o contraer" del menú contextual de un elemento dado de uno de los
campos agrupados.
Simplemente haciendo doble clic sobre el elemento de campo deseado para expandirlo y otra vez
doble clic para contraerlo.
Mostrar detalle
Se añade un campo más a dichas áreas. Es decir lo que
hace es traspasar un campo oculto (que esta en el área
de página) a las áreas Rótulos de fila o Rótulos de co-
lumna y por lo tanto visibiliza sus datos. Para llevarlo a
cabo debemos hacer doble clic en un nombre de campo
en la áreas de áreas Rótulos de fila o Rótulos de colum-
na.
En la ficha "Resumir por" de dicho dialogo tenemos todas las funciones posibles.
Además de tener varios campos distintos también podemos tener el mismo campo repetido tantas veces
como funciones distintas queramos aplicar sobre el. Evidentemente se puede tener la combinación de am-
bas cosas, es decir varios campos distintos y al mismo tiempo alguno de ellos repetido aplicándole una fun-
ción distinta en cada caso.
" Valores" y después a la ficha "Mostrar valores como". En esta ficha encontramos las nueve posibilidades
existentes, que para una comprensión más fácil se pueden organizar en los cinco grupos siguientes:
A. Normal
1 - Desactiva el cálculo personalizado si lo había y muestra los datos de la forma usual, como valor
numérico.
B. Comparaciones entre valores de elementos respecto a un elemento base
2 - Diferencia de: Para cada valor de un cierto campo y elemento mostrara la diferencia entre ese
valor y el valor del elemento de dicho campo elegido como elemento base para la comparación.
3 - % de: Similar al anterior. Aquí todos los valores del elemento base son el 100% y los demás son
relativos a estos, extraídos mediante un simple regla de tres. Con esta opción vemos el porcen-
taje total de cada elemento-valor y en que medida sobrepasa al elemento base comparativo.
4 - % de la diferencia: Idéntico a "Diferencia de" pero expresado en %. Muestra el % que le falta o
sobra a un valor respecto al 100% del valor de referencia.
C. Total en
5 - Trabaja a nivel de campo, no de elemento. Presenta los valores del total acumulado para el
campo elegido. Por ejemplo supuesto el campo Producto en "Rótulos de columna", solo el pri-
mer elemento de la izquierda (Casco) presentaría los valores reales, el resto presentarían los
valores acumulados de la suma de todos los campos anteriores a ellos.
D. Comparaciones entre valores de elementos respecto a totales generales
6 - % de la fila: El % que representa el valor de dicho elemento respecto al total de su fila que equi-
vale al 100%.
7 - % de la columna: El % que representa el valor de dicho elemento respecto al total de su colum-
na que equivale al 100%.
8 - % del total: El % que representa el valor de dicho elemento respecto al total general de toda la
tabla que representa el 100%.
E. Índice
9 - Calcula un valor (el del índice) según la fórmula indicada a continuación:
((valor en celda) x (Suma total de sumas totales)) / ((Suma total de fila) x (Suma total de columna))
En todos los casos anteriores es importante observar para una correcta interpretación de los resultados, que
los datos por defecto no tienen en cuenta elementos o valores ocultos por filtros.
Campos calculados
Como en el caso de las Bases de Datos, además de los campos originales, podemos generar campos nue-
vos, denominados campos calculados, a partir de fórmulas que usen como parámetros o argumentos uno o
varios de los campos existentes en nuestros datos.
Para crear un campo calculado nos situamos en cualquiera de las cuatro aéreas se la tabla dinámica y se-
leccionamos la opción "Campo calculado…." en la ficha "Herramientas de tabla dinámica" > Opciones >
Herramientas > Fórmulas, así aparecerá el cuadro de diálogo "Insertar campo calculado".
En dicho cuadro debemos darle un nombre al campo (se admiten espacios) y después crear la fórmula que
generara los datos del nuevo campo calculado. La fórmula se genera como cualquier otra fórmula de Excel,
es decir poniendo inicialmente un símbolo de igual y después los nombres de los campos y operadores a
usar. Los nombres de campos podemos escribirlos manualmente o seleccionarlos de la lista campos y pul-
sando el botón "Insertar campo" el propio Excel se encargara de escribirlos.
Una vez creado un campo calculado, aparecerá en el panel "Lista de campos de la tabla dinámica" como un
campo más.
Podemos modificar el nombre o definición (es decir la fórmula del campo) en cualquier momento, para ello
basta con abrir el cuadro de dialogo "Insertar campo calculado", seleccionar el campo en la lista desplegable
ProInf.net, SCP Página 99
667 551 698
www.proinf.net
Excel 2010 Avanzado
Nombre, donde aparecen todos los campos calculados, modificar la fórmula y finalmente pulsar el botón
Modificar para aplicar y guardar la modificación.
Para eliminar un campo calculado, de forma similar al paso anterior, se selecciona el campo y después se
pulsa el botón Eliminar.
Los campos calculados siempre utilizan como función de resumen la función Suma. Como norma de Excel,
la fórmula de un campo calculado opera sobre la suma de los datos asociados a los campos que se inclu-
yen en la misma, por lo que no es posible cambiar la función de resumen en un campo calculado.
Ejemplos:
Supuesto tuviéramos una base de datos con los campos Ingresos, Gastos, Fecha Pedido y Fecha Entrega
entre otros, podríamos generar los campos calculados siguientes:
Campo 1: Saber el Beneficio Bruto obtenido.
Nombre: Beneficios Brutos
Fórmula: =Ingresos – Gastos
Campo 2: Obtener el Beneficio Neto supuesto que el impuesto de sociedades fuese del 40%.
Nombre: Beneficios Netos
Fórmula: =(Ingresos – Gastos) *0,60
Campo 3: Conocer el retardo entre la fecha de los pedidos y la fecha de entrega.
Nombre: RetardoEnEntregas
Fórmula: ='Fecha Entrega' - 'Fecha Pedido'
Elementos calculados
Es un concepto bastante similar al de campo calculado, pero en lugar de operar con todos los valores de un
campo, se opera a nivel de los valores de un elemento de campo. Es decir en los elementos calculados, a
diferencia de los campos calculados la fórmula se aplica a cada registro individual y luego se resumen todos
en el área de datos.
Solo se pueden generar elementos calculados a partir de campos que estén situados en las aéreas "Rotulo
de fila" o "Rotulo de columna", por lo cual antes de iniciar la creación de un "elemento calculado" debemos
estar posicionados en una de esas dos aéreas. Una vez hecho esto se procede de forma análoga al caso
de los campos calculados, es decir vamos a la ficha "Herramientas de tabla dinámica" > Opciones > Herra-
mientas > Fórmulas y seleccionamos la opción "Elemento calculado…" que nos lanza el siguiente cuadro de
dialogo:
ProInf.net, SCP Página 100
667 551 698
www.proinf.net
Excel 2010 Avanzado
Para crear el elemento calculado deberemos darle un nombre y crear la fórmula que generara los valores
del elemento, bien de forma manual o bien seleccionando el campo, después el elemento que usaremos
como base para nuestros cálculos y usando el botón "Insertar campo" para insertar el campo en el cuadro
de texto Fórmula.
Una vez creado un elemento calculado, este será añadido como un elemento más del campo y por tanto
aparecerá en su correspondiente área y también en los filtros por valor del campo al que pertenece.
Si los elementos de un campo se encuentran agrupados, no es posible añadir elementos calculados a dicho
campo; para hacerlo hay que desagrupar primero el campo, crear el elemento calculado y después volver a
agrupar el campo de la forma deseada.
Ejemplos:
Supuesto tuviéramos en nuestros datos un campo llamado Productos, que tuviera los elementos Casco,
Guantes, Zapatillas, etc., entre otros, podríamos generar elementos calculados del tipo siguiente:
Elemento 1: Conocer los ingresos esperados en Casco, supuesto las ventas esperadas se incrementen
un 5% en el próximo periodo.
Nombre: Casco + 5%
Fórmula: =Casco *1,05
Elemento 2: Obtener un elemento calculado que sea la suma de los dos elementos más importantes,
sin tener que recurrir a ocultar el resto de productos.
Nombre: Casco y Zapatillas
Fórmula: =Casco+Zapatillas
Orden de resolución
En ocasiones puede que existan varios elementos calculados definidos para diferentes campos o bien va-
rias fórmulas para un mismo elemento calculado, o tenemos elementos calculados tanto en rótulos de fila
como de columna, de forma que pueda darse el caso de para una misma celda se apliquen dos o más
cálculos. Si el orden puede influir en el resultado correcto tenemos la opción de decirle a Excel en que orden
queremos que se ejecuten los elementos calculados, para ello debemos lanzar el cuadro de dialogo "Orden
de resolución de elemento calculado" desde la ficha Herramientas de tabla dinámica >Opciones > Herra-
mientas > Fórmulas > Orden de resolución….Las fórmulas se ejecutaran de arriba hacia abajo, siendo la
última fórmula la que determinara el valor final de la celda.
Gráficos dinámicos
Un gráfico dinámico combina la facilidad de resumen de datos de las tablas dinámicas con la apariencia y
facilidad de análisis visual de un gráfico.
Un gráfico dinámico se debe generar a partir de una tabla dinámica. Una vez generado, ambos tabla diná-
mica y gráfico quedan totalmente vinculados, de forma que cualquier filtro que se haga en la tabla dinámica
se reflejara inmediatamente en el gráfico y viceversa. Así mismo si en la tabla se agregan, quitan o cambian
de área campos, el gráfico se actualizara automáticamente con dichos cambios nada más finalizar los mis-
mos.
Para crear un gráfico dinámico debemos situarnos en cualquier punto de la tabla dinámica y pulsar la opción
"Gráfico dinámico" de la ficha Herramientas de tabla dinámica > Opciones > Herramientas y escoger el tipo
de gráfico deseado.
Una vez creado el gráfico podemos hacerle las modificaciones visuales y de formato deseadas de la forma
usual en cualquier tipo de gráficos. Como particularidad, al estar seleccionada alguna parte del gráfico nos
aparecerá el panel "Panel de filtros de gráficos dinámico" propio solo de gráficos generados a partir de ta-
blas dinámicas, donde podremos aplicar filtros a los campos de área o "Filtro de informe" y a los campos de
los ejes (de categorías u horizontal y de series o vertical).
9.000
8.000
Millares de €
7.000
6.000
5.000
4.000
3.000
Trim.1 Trim.2 Trim.3 Trim.4
MACROS
Concepto
Excel incorpora un lenguaje de programación denominado Visual Basic para Aplicaciones (VBA). Mediante
este lenguaje es posible ir más allá de las funcionalidades facilitadas por las distintas fichas y comandos y
realizar tareas sencillas o complejas de forma automática.
Un macro es la secuencia ordenada de los pasos o instrucciones que se deben llevar a cabo para cumpli-
mentar una tarea. Excel permite guardarlos con un nombre determinado y después ejecutarlos tantas veces
como se quiera de una forma fácil y rápida. Los macros son muy útiles para tereas repetitivas, dado que una
vez grabados nos evitan tener que repetir de nuevo todos y cada uno de los pasos realizados para hacer
dicha tarea cada vez que necesitemos repetirla, con la ventaja y seguridad de no olvidarnos de ningún paso,
ni del orden correcto en que se debían ejecutar. Por tarea repetitiva se debe entender cualquiera que se
vaya a repetir más de cuatro o cinco veces, pero si la tarea consta de un gran número de pasos o es muy
compleja, entonces vale la pena hacer un macro aunque solo lo vayamos a ejecutar dos o tres veces.
Creación de macros
Existen dos métodos para crear un macro en Excel:
Automático: Se trata de usar la grabadora de macros que posee Excel. Dicha grabadora ira reco-
giendo cada uno de los pasos que demos con el ratón o con el teclado y lo traducirá a la instrucción
correspondiente de VBA, de una forma interna y automática. No necesitamos saber nada sobre
programación e instrucciones en VBA. Este método es muy adecuado cuando nos iniciamos en el
mundo de los macros, cuando los macros no son demasiado complejos y también como método de
autoaprendizaje del lenguaje VBA, dado que después de grabar el macro podemos revisar y modifi-
car el código generado por la grabadora.
Manual: Debemos abrir el entorno de programación de Excel-VBA y teclear todas las instrucciones y
sentencias necesarias, lo que implica tener conocimientos tanto de programación de VBA, como del
el modelo de objetos de Excel. Para abrir el entorno de programación debemos desplazarnos a la fi-
cha Programador, grupo Código y clicar sobre el botón visual Basic.
Antes de empezar a grabar una macro conviene tener claros diversos puntos
Es muy aconsejable conocer los pasos de la tarea que se quiere grabar, especialmente cuando esta consta
de un gran número de pasos, dado que es molesto e improductivo darse cuenta en mitad de la grabación
que no estamos seguros de cuál es el siguiente paso, o apercibirnos que nos hemos equivocado tres pasos
atrás.
Vale la pena, si el macro es complejo, ejecutar más de una vez la rutina o anotarse los pasos de la misma,
dado que cuando se graba una macro de forma automática no existen las opciones de parar la grabadora o
volver hacia atrás en algún paso y la grabadora de macros graba todo lo que se hace, incluyendo los erro-
res que comentamos. Si la macro se grabó mal, solo hay dos opciones: volver a repetirla de nuevo toda
entera, o bien si poseemos conocimientos de VBA modificarla manualmente en el entorno de programación.
Elegir un nombre adecuado para la macro, lo más descriptivo posible de la función que realiza. Ni muy lar-
go, ni muy corto. Los nombres deben de ser continuos, es decir si se componen de varias palabras debere-
mos poner la primera silaba de cada palabra en mayúsculas o usar el guion bajo (_) para separarlas.
Definir el ámbito de uso del macro. El ámbito de uso queda determinado por el lugar en el que se guarde la
macro grabada, lo cual depende a su vez de lo que haga la macro. Existen las tres posibilidades siguientes:
Libro de macros personal: Es el lugar adecuado para guardar todos los macros que sean de tipo
genérico, es decir que lleven a cabo tareas que no dependan de ningún libro en particular, como por
ejemplo organizar en horizontal o vertical las ventanas abiertas existentes, marcar todas las celdas
con errores en la hoja que este activa ese momento, dar una lista de los libros abiertos, etc.. Tam-
bién es el lugar conveniente para aquellos macros que se usen muy a menudo y por lo tanto deben
estar disponibles en cualquier sesión.
El libro de macros personal es un libro normal llamado PERSONAL.XLSB, donde Excel guarda to-
dos los macros genéricos. Es un libro que se crea de forma automática al grabar por primera vez un
macro genérico. Este libro queda oculto de forma predeterminada y se guarda en el directorio de
inicio de Excel, usualmente llamado Inicio o XLSTART. Al activar Excel, este siempre mira en el di-
rectorio de inicio y abre de forma automática todos los libros que haya en dicho directorio. Con ello
PERSONAL.XLSB siempre estará disponible.
Por tanto si nos queremos llevar las macros grabadas en PERSONAL.XLSB a otro ordenador debe-
remos copiar dicho archivo y ponerlo en el directorio de Inicio o XLSTART del ordenador de destino.
Si en el ordenador de destino existiese ya un PERSONAL.XLBS, deberíamos renombrarlo, abrirlo y
copiar todas sus macros al nuevo PERSONAL.XLBS que tenemos.
Este libro. Esta opción es la adecuada para macros que realicen tareas específicas ligadas a una
determinada hoja u hojas del libro activo. La macro se grabara dentro del libro actual y por tanto solo
estará disponible si está abierto el libro que la contiene. Si la macro se necesitase en otros libros,
entonces debería o bien guardarse en el "Libro de macros personal" o bien se deberá cargar este li-
bro para que esté disponible.
Libro nuevo. Esta es una opción mixta o intermedia entre las dos anteriores, es decir es recomen-
dable cuando las macros se necesiten para varias situaciones distintas en distintos libros, pero a la
vez no sean de uso muy frecuente o no se quiera sobrecargar el "Libro de macros personal". Las
macros grabadas estarán disponibles siempre que el libro este cargado.
Después clicaremos el botón Aceptar, a partir de este momento comienza el proceso de grabación,
toda operación que realicemos con el ratón o el teclado se transcribirá a código VBA.
En la celda B2 escribimos: =HOY() y pulsamos la tecla ENTER.
Volvemos a seleccionar la celda B2 y en su menú contextual elegimos copiar.
Pulsamos el botón secundario del ratón para volver a hacer aparecer el menú contextual y elegimos
Pegado especial.., Valores, de esta forma no se pegara la función, sino el valor que ha calculado.
Pulsar la tecla ESC.
A continuación nos situaremos en la ficha Inicio y seleccionaremos la fuente, tamaño y efecto
deseado.
En este punto ya hemos terminado nuestra macro, solo falta parar la grabadora, para lo cual volve-
mos a la ficha Programador, grupo Código y pulsaremos la opción "Detener grabación", que solo es-
ta activa durante el proceso de grabación de una macro.
Una vez grabado el macro se debe comprobar que funciona correctamente y después asociarlo a la barra
de herramientas de acceso rápido o ejecutarlo mediante alguna de las otras opciones disponibles que se
mencionan en secciones siguientes.
Opciones
A parte de los datos obligatorios existen dos opcio-
nes que se pueden usar en el momento de la crea-
ción del macro o a posteriori, a través de la opción
Macros, cuadro de dialogo Macro, botón "Opcio-
nes…", son las siguientes
Descripción: Sirve como documentación de
la macro. Se puede apuntar la fecha de
creación y una breve descripción de la tarea
o tareas que realiza. Puede ser útil espe-
cialmente cuando la macro realiza varias ta-
reas simultáneamente.
Tecla de método abreviado: Sirve para
asignar una combinación de teclas a la ma-
cro de forma que al pulsarlas se ejecute. En
un apartado posterior se comentaran con
más detalle aspectos relacionados con la asignación de teclas.
Ambas opciones se pueden asignar o desasignar en el momento deseado en el mismo cuadro de dialogo
"Opciones de la macro".
Ejecución de macros.
Existen diversas formas de ejecutar un macro:
Desde el cuadro de dialogo Macro.
Mediante una combinación de teclas.
Mediante un botón en la barra de herramientas de acceso rápido.
Mediante un botón o control de formulario o un objeto gráfico en la propia hoja de cálculo.
Esta forma no es muy versátil, está pensada esencialmente para probar los macros antes de asociarlos
definitivamente a la barra de herramientas o a algún objeto o también para aquellos macros que se usen
con poca frecuencia
La opción genérica: pulsando el botón "Visual Basic" que simplemente abre el entorno en el mismo
punto y estado en que se cerró en la última ocasión.
La opción especifica: pulsando el botón "Macros" que abre el cuadro de dialogo "Macro", donde al
seleccionar un macro y pulsar el botón "Modificar" no posicionara exactamente en el código de di-
cho macro.
La opción especifica es sin duda la más adecuada para no perdernos si tenemos muchos macros, además
nos permite también eliminar los macros no deseados, o ejecutar paso a paso los erróneos para ver en qué
punto fallan.
Nota: En caso de querer ver el código de un macro grabado en el "libro de macros personal" es necesario
previamente desocultar el archivo PERSONAL.XLSB, caso contrario recibiremos el siguiente mensaje de
Excel:
El entorno VBA es un completo entorno de programación, con un amplio abanico de menús, ventanas y
opciones. Aquí comentaremos las partes principales, para aprender a movernos dentro de el, así como el
código generado por los macros o que nosotros podemos escribir directamente y algunas opciones de los
menús. Las partes principales del entorno son las siguientes:
Ventana de Propiedades
Cada uno de los elementos de la ventana "Explorador de proyectos" tiene un conjunto de propiedades. La
cantidad de propiedades depende del tipo de elemento. Las propiedades definen características del elemen-
to, como por ejemplo título, tamaño de la letra, color del texto, etc.
Si esta ventana no apareciese visible debemos hacer lo mismo que en el caso del "Explorador de proyec-
tos", ir al menú Ver y seleccionar Ventana propiedades.
Figura: Ventana Explorador de Proyectos Figura: Ventana Propiedades
Ventana de Código
La ventana código es donde aparecen todas las instrucciones grabadas por una macro o introducidas ma-
nualmente. Para cada objeto o elemento de un proyecto podemos tener abierta su propia ventana de códi-
go, basta hacer doble clic sobre dicho elemento para que se abra su correspondiente ventana. Para gestio-
nar u organizar las ventanas iremos al menú Ventana donde aparece la lista de todas ellas.
En la parte inferior izquierda de una ventana de código aparecen dos botones o iconos: "Ver procedimiento"
y "Ver modulo completo" que nos permiten ver solo el código del procedimiento actual o todo el código exis-
tente en la hoja, formulario o modulo en el que estemos. En este ultimo caso, para distinguir mejor donde
empieza o acaba un procedimiento, entre uno y otro se inserta una línea horizontal que los separa, según
se puede ver en la figura de la página siguiente.
Entendiendo el código VBA
Si miramos lo escrito por Excel al crear una macro, es decir el código de programación, podemos observar
lo siguiente:
Todos los macros o procedimientos están limitados por las palabras reservadas del lenguaje Sub y
End Sud que indican el principio y el final del macro. Después de Sub viene el nombre del macro o
procedimiento que debe de ser continuo (sin espacios entre palabras).
Todo lo que aparece dentro después de un apostrofe ( ' ) es un comentario, es decir Excel lo omite
al ejecutar el procedimiento. Los comentarios sirven para documentar mejor el propósito de los ma-
cros o procedimientos. Por defecto aparecen en color verde.
Todas las palabras que aparecen en color azul son palabras especificas del lenguaje VBA, tienen
un significado especial y están reservadas por lo que no podemos usarlas para darle nombre a un
macro.
Códigos comunes
Si grabamos macros y observamos su código es muy posible que nos encontremos con alguna de las líneas
de código más comunes que se presentan a continuación, a fin de entender para que sirven:
Trasladarse o seleccionar una celda
Range("B5").Select
Escribir en una celda
ActiveCell.FórmulaRlCl="Importes de Ventas"
Letra Negrita
Selection.Font.Bold = True
Letra Cursiva
Selection.Font.Italic = True
Letra Subrayada
Selection Font.Underline= xlUndelineStyleSingle
Centrar texto
With Selection
.HorizontalAlignment =xlCenter
.VerticalAlignment=xlBottom
End With
Alinear a la derecha
With Selection
.HorizontalAlignment = xlRight
End With
Tipo de Letra (Fuente)
With Selection.Font
.Name = " Algerian"
End With
Tamaño de Letra (Tamaño de Fuente)
With Selection.Font
.Size = 14
End With
Copiar
Selection.Copy
Pegar
ActiveSheet.Paste
Cortar
Selection.Cut
Insertar una fila . .
Selection.EntireRow.Insert
Eliminar una fila
Selection.EntireRow.Delete
Insertar una columna . .
Selection.EntireColumn.Insert
Eliminar una columna
Selection.EntireColumn.Delete
Abrir cierto libro
Workbooks.Open Filename:="D:\Mis documentos\Ventas.xlsx"
Grabar un libro
ActiveWorkbook.SaveAs Filenarne := "C: \Mis documentos\Ventas.xlsx", FileFor-
mat:=xlNormal, Password:="", ReadOnlyRecommended:=False,CreateBackup:=False
Trasladarse a una hoja determinada
Sheets("BD_Subtotales").Select
Movernos a la ventana siguiente
ActiveWindow.ActivateNext
Movernos a la ventana anterior
ActiveWindow.ActivatePrevious
Ocultar ciertas filas o columnas
Range("7:10,15:20,23:27").Select
Selection.EntireRow.Hidden = True
Range("E:J,L:Q,S:X,AA:AF,CG:CH").Select
Selection.EntireColumn.Hidden = True
Desocultar ciertas filas o columnas
Range("7:10,15:20,23:27").Select
Selection.EntireRow.Hidden = False
Range("E:J,L:Q,S:X,AA:AF,CG:CH").Select
Selection.EntireColumn.Hidden = False
Organizar las ventanas en vertical
Windows.Arrange xlArrangeStyleVertical
Organizar las ventana en horizontal
Windows.Arrange xlArrangeStyleHorizontal
Evitar que parpadee la pantalla al ejecutar algunas acciones
Application.ScreenUpdating = False
Volver la pantalla a su estado normal
Application.ScreenUpdating = True
Ejercicios:
Macros con ámbito global (deben guardarse en Libro de macros personal)
1. Grabar una macro que organice todas las ventanas abiertas en mosaico horizontal.
2. Crear una macro que organice todas las ventanas abiertas en mosaico vertical.
3. Grabar una macro que, aplicada a cualquier hoja, disminuya el zoom al 25%.
4. Crear una macro que, aplicada a cualquier hoja, aumente el zoom al 130%.
5. Grabar una macro para desplazarse a la siguiente ventana o a la anterior.
6. Crea una macro que abra dos archivos a la vez, los organice en mosaico vertical posicionándose en
la celda A100 de la segunda hoja de cada uno de ellos.
7. Crear una macro de nombre CabeceraEmpresaAbsoluta usando referencias absolutas, empezando
por la celda B2,que comprenda el nombre y dirección de la empresa. Al hacerlo aplicarle diversas
opciones de formato (color de fondo, letra mayor para el título, bordes alrededor, etc.). El resultado
final debe presentar un aspecto similar al de la siguiente figura:
8. Crear una macro idéntica a CabeceraEmpresaAbsoluta pero usando referencias relativas. Darle en
nombre de CabeceraEmpresaRelativa.
9. Hacer un macro que escriba la fecha actual, pero de forma estática, es decir que la fecha no se ac-
tualice cada vez que se abre el libro. Al grabar la macro debemos usar la función HOY().
Se desea además que la fecha se escriba en la celda en la que estemos posicionadas en el mo-
mento de la ejecución del macro y que le de formato negrita a dicha fecha.
10. Crear una macro que prepare las propiedades de formato del documento a nivel de impresión. Se
deben fijar las siguientes características:
Márgenes superior, inferior, izquierdo y derecha a 2 cm, Encabezado y Pie a 1 cm , Orientación del
papel Horizontal, Tamaño del papel A5 y Alineación vertical centrada.
11. Supuesto tuviéramos que crear cada semana una determinada tabla que representa un planning
semanal de horarios de atención al público, que después es rellenada con el nombre de los pacien-
tes, según la siguiente ilustración:
Queremos que pueda ser creada en cualquier libro y en una celda inicial no definida previamente.
Evidentemente, si la tabla siempre estuviese en la misma posición, se podría utilizar una plantilla,
pero como queremos poder crearla en cualquier celda, deberemos grabar el proceso de generación
en una macro, llamémosle CrearTablaHorarios.
Antes de empezar a grabar las acciones, debemos recordar que se debe grabar en el "Libro de ma-
cros personal" y que se debe realizar una grabación relativa.