Excel 2016 - Ediciones ENI · 2018-04-20 · Si ya utilizaba las tablas dinámicas en una versión...

Ediciones ENI

Excel 2016

Colección Ofimática Profesional

Extracto del Libro

Cálculos avanzados

© Editions ENI - Reproducción prohibida 171

Cálculos avanzad os

Los cá lcu losEfectuar cálculos con datos de tipo fecha

En este apartado, una vez abordados los principios de cálculo de fechas usados porExcel, procederemos a describir algunas funciones específicas al tratamiento defechas a través de una serie de ejemplos:

Principios para calcular los días

i En los cálculos realizados sobre días, siga el mismo procedimiento que con los demáscálculos. Excel registra las fechas en forma de números secuenciales llamadosnúmeros de serie. Por ese motivo pueden agregarse, sustraerse e incluirse en otroscálculos.

i De forma predeterminada, Excel para Windows inicia el calendario a partir de 1900(para Macintosh el calendario se inicia en 1904). El 1 de enero de 1900 correspondepor tanto (en Excel para Windows) al número de serie 1, y el 1 de enero de 2005 es el38 353, ya que desde el 1 de enero de 1900 han transcurrido 38 353 días.

i Para utilizar una función específica de gestión de fechas y horas, puede activar la pes-taña Fórmulas, hacer clic en el botón Fecha y hora del grupo Biblioteca de funcionesy luego en la función que corresponda para utilizar el asistente.AHORA()Devuelve el número de serie de la fecha y de la hora del día.AÑO(número_de_serie)Convierte un número de serie en año.DIA(número_de_serie)Convierte un número de serie en día del mes.DIA.LAB(fecha_inicial;días;[días_no_laborables])Devuelve el número de serie de la fecha antes o después del número de días labo-rables especificado.DIA.LAB.INTL(fecha_inicial;días;[fin_de_semana];[días_no_laborables])Devuelve el número de serie de la fecha antes y después de un número especificadode días laborables con parámetros que identifican y cuentan los días de fin de semana.DIAS(fecha_final;fecha inicial)Calcula el número de días entre las dos fechas.DIAS.LAB(fecha_inicial; fecha_final;[días_no_laborables])Devuelve el número de días laborables enteros comprendidos entre dos fechas.

Exce l 2016Los cálculos

172

DIAS.LAB.INTL(fecha_inicial;fecha_final;[fin_de_semana];[días_no_laborables])Devuelve el número de días laborables enteros comprendidos entre dos fechas usandoparámetros que identifican los días del fin de semana y su número.DÍAS360(fecha_inicial;fecha_final[método])Calcula el número de días separando dos fechas sobre la base de un año de 360 días.DIASEM(número_de_serie;[tipo_devolución]Convierte un número de serie en día de la semana.FECHA(día;mes;año)Devuelve el número de serie de una fecha precisa.FECHA.MES(fecha_inicial;mes)Devuelve el número de serie de la fecha, que es el número indicado de meses anteso después de la fecha inicial.FECHANUMERO(texto_de_fecha)Convierte una fecha representada en forma de texto en número de serie.FIN.MES(fecha_inicial;mes)Devuelve el número de serie del último día del mes antes o después del número espe-cificado de meses.FRAC.AÑO(fecha_inicial;fecha_final;[base])Devuelve la fracción del año que representa el número de días completos entre lafecha inicial y la fecha final.HORA(número_de_serie)Convierte un número de serie en hora.HORANUMERO(texto_de_hora)Convierte una hora representada como texto en número de serie.HOY()Devuelve el número de serie de la fecha del día.ISO.NUM.DE.SEMANA(fecha)Devuelve el número ISO de la semana del año correspondiente a una fecha dada.MES(número_de_serie)Convierte un número de serie en mes.MINUTO(número_de_serie)Convierte un número de serie en minuto.NSHORA(hora;minuto;segundo)Devuelve el número de serie de una hora precisa.

Cálculos avanzados


NUM.DE.SEMANA(número_de_serie;[tipo_retorno])Convierte un número de serie en número de semana del año.SEGUNDO(número_de_serie)Convierte un número de serie en segundos.

Combinar texto y fecha

i Para combinar en una celda el texto y la fecha contenidos en diferentes celdas, puedeusar la función TEXTO, cuya sintaxis es =TEXTO(valor;formato_texto):El argumento valor representa un valor numérico, una fórmula cuyo resultado es unvalor numérico o bien una referencia a una celda con un valor numérico.El argumento formato_texto representa un formato de número en forma de textodefinido en el cuadro Categoría del cuadro de diálogo Formato de celdas.

Presentamos aquí un ejemplo de uso:

Calcular la diferencia entre dos fechas (función SIFECHA)

SIFECHA es una de las funciones «ocultas» de la aplicación Excel. Por ese motivo noaparece en el asistente para funciones ni en la ayuda en línea. Las funciones ocultasse han introducido en Excel por razones de compatibilidad con otras hojas de cálculo;funcionan a la perfección, pero no forman parte de las funciones «oficiales» de Excel.

Esta función resulta muy práctica en caso, por ejemplo, de que desee calcular laantigüedad de un empleado en años y meses. La sintaxis de la función SIFECHA esSIFECHA(fecha_inicial;fecha_final;base).El argumento base representa la duración calculada y puede adoptar los valoressiguientes:

"y" para calcular la diferencia absoluta en años.

"m" para calcular la diferencia absoluta en meses.


174


He aquí otro ejemplo; este permite calcular la edad de una persona en función de lafecha actual (función=HOY()):

Calcular el número de días laborables o no entre dos fechas

Excel sabe calcular el número de días laborables (de lunes a viernes) que hay entredos fechas con ayuda de la función DIAS.LAB, cuya sintaxis es=DIAS.LAB(fecha_inicial;fecha_final)

"d" para calcular la diferencia absoluta en días.

"ym" para calcular la diferencia en meses si las dos fechas se encuentran en elmismo año.

"yd" para calcular la diferencia en días si las dos fechas se encuentran en el mismoaño.

"md" para calcular la diferencia en días si las dos fechas se encuentran en el mismomes.

Cálculos avanzados



Para que la función pueda tener en cuenta los días festivos en el cálculo, deberá agre-gar un tercer argumento que haga referencia a un día festivo o a un rango de días fes-tivos. La sintaxis de esta función es entonces =DIAS.LAB(fecha_inicial; fecha_final;[días_no_laborables]).

En este ejemplo, se han calculado los días festivos en el rango de celdas B3 a B15.

m Para calcular el número de días entre dos fechas (días festivos, no laborables, etc.,incluidos, puede utilizar la función DIAS, cuya sintaxis es =DIAS(fecha_final; fecha_inicial).


176

Calcular una fecha después de determinado número de días laborables

La función DIA.LAB permite calcular una fecha correspondiente a un día (fecha deinicio) más o menos el número de días laborables especificado. Los días laborablesexcluyen sábados y domingos y todas las fechas identificadas como días festivos.La sintaxis de esta función es la siguiente:=DIA.LAB(fecha_inicial;días;[días_no_laborables]):

Presentamos aquí un ejemplo de uso: queremos encontrar la fecha de finalización deun trabajo que debía empezar el 01 de noviembre de 2015 y que tiene una duraciónde 40 días laborables.

Se ha aplicado a la celda C3 el formato Fecha, ya que, de forma predeterminada,Excel muestra el resultado en forma de número de serie.

Si la fórmula devuelve un mensaje de error, la explicación es la siguiente:

fecha_inicial Representa la fecha de inicio.

días Representa el número de días laborables antes o después de lafecha de inicio. Un número de días positivo da una fecha futuray un número de días negativo, una fecha pasada.

días_no_laborables Representa una lista de fechas que deben excluirse del calen-dario de días de trabajo (días festivos, vacaciones, permisos,etc.). Este argumento es opcional.

#¡VALOR! Un argumento no es una fecha válida.

#¡NUM! La fecha de inicio más el número de días no da una fecha válida.

Ediciones ENI

Cuadros resumen y cuadros de mando

Tratamiento y análisis de grandes volúmenes de datos con Excel 2016

Colección Objetivo: Soluciones

Extracto del Libro

Capítulo 7: Las tablas dinámicas261

© E

dit

ion

s E

NI - A

ll r

igh

ts r

ese

rve

d

Capí tul o 7: Las tablas din ám i casCuadros resumen y cuadros de mando

A.Introducción

1. Preámbulo

En los capítulos anteriores, ha podido aplicar diferentes técnicas para obtener estadísticasconsolidadas a partir de bases de datos de diferentes orígenes. Estas técnicas (subtotales,tablas de datos, cálculos multicriterios, etc.) requieren un dominio de las funciones de cál-culo de Excel, pero no tienen límites en el diseño y configuración de las tablas. Otra funciónde Excel que debe conocer es la técnica de creación de tablas dinámicas.

En este libro abordaremos la creación de tablas dinámicas simples, ya que en la mismacolección existe un libro entero dedicado a las tablas cruzadas dinámicas.

Como para los capítulos anteriores, los ejemplos del libro se pueden descargar de la webwww.ediciones-eni.com. Esto le evitará tener que escribir todos los datos y le permitirácontrolar sus resultados. Las bases de datos propuestas son variadas y le permitirán apli-car las técnicas de creación de tablas dinámicas en muchos ámbitos.

Si ya utilizaba las tablas dinámicas en una versión anterior de Excel, Excel 2016 le permitiráimportar y relacionar importantes cantidades de datos de varias fuentes mediante un mo-delo de datos integrado. También podrá generar modelos de datos que le servirán de basepara las tablas o gráficos cruzados dinámicos.

El modelo de datos de Excel presenta como puntos fuertes:

- El tratamiento rápido de una gran masa de datos.

- Una gestión de tamaños de archivos mejorada.

- Una portabilidad de datos, ya que estos se registran dentro del libro.

Excel 2016 incluye el componente Power Pivot, un espacio de trabajo que permite tratar da-tos de varios millones de filas de múltiples y diversas fuentes (bases de datos, cubo OLAP,etc.). Power Pivot consta de un lenguaje específico DAX (Data Analysis Expressions) paraestablecer relaciones, cálculos y jerarquías.

2. Objetivo

Una tabla dinámica (TD) permite realizar una síntesis rápida a partir de una base de datos.Herramienta muy eficaz, y parte integrante de Microsoft Excel, que le ayudará a crear análi-sis eficaces y potentes a partir de sus tablas de datos.

Tabla cruzada dinámica, ¿por qué este nombre?


262

Tabla cruzada porque las síntesis que se pueden hacer pueden ser de una, dos, tres, cua-tro, etc. dimensiones. Por ejemplo, se puede obtener el importe total de los gastos porconcepto presupuestario y por departamento (dos dimensiones), o bien la cesta media men-sual por sección y por tipo de cliente concreto (tres dimensiones), etc.

A continuación, le presentamos tres ejemplos de tablas dinámicas simples.

TD de una dimensión

Total de los gastos por concepto

TD de dos dimensiones

Total de los gastos por concepto y por departamento


© E

dit

ion

s E

NI - A

ll r

igh

ts r

ese

rve

d

TD de tres dimensiones

Total de gastos mensuales por departamento para el concepto Proveedores de oficina

Tabla dinámica ya que una actualización de la tabla dinámica supone una revisión de losdatos fuente que permite poner al día el resumen. La base de datos de origen de la tabladinámica puede venir de Excel, Access, de su programa de contabilidad o de gestión comer-cial o de cualquier otra aplicación compatible.

Crear una tabla dinámica requiere unos pocos segundos. No es necesario dominar lasfunciones de cálculo avanzadas de Excel para crear una TD simple; sin embargo, crear unaTD compleja requiere conocer ciertas funciones y características de Excel.


264

3. Diagrama

B.Las tablas dinámicas

1. Definiciones

Base de datos Conjunto de datos estructurados guardados en un disco. Esteconjunto de datos se puede consultar y modificar.

Registro Cada registro corresponde a una información relativa a un ele-mento almacenado en la base de datos.

Campo Un campo representa una característica precisa de registro.Para distinguir un registro son necesarios varios campos; porejemplo, para un trabajador: N° identificador, Nombre,Apellido, Sexo, Función, Salario...


© E

dit

ion

s E

NI - A

ll r

igh

ts r

ese

rve

d

2. Los límites de las tablas dinámicas

Filtro de relación Permite filtrar los datos que se van a mostrar en función delelemento seleccionado en el filtro de la relación. Un filtro derelación le permite presentar rápidamente un subconjunto dedatos en una relación de tabla cruzada. Cuando sus datos deorigen consten de un gran número de datos, el filtro permitetrabajar en una parte de los datos fuente de la relación. Tam-bién puede ver las síntesis para una familia de productos, unperiodo de tiempo o una categoría de personal específicos.

Segmentos Autoriza el uso de botones para segmentar y filtrar rápida-mente los datos.

Modelo de datos Colección de tablas y sus relaciones entre sí.

Etiquetas de filas Permiten ver los campos en forma de filas al lado izquierdo dela relación. Una fila de posición inferior se anida en la fila quese muestra inmediatamente por encima de esta.

Etiquetas de columnas Permiten ver los campos en forma de columnas en la partesuperior de la relación. Una columna de posición inferior seanida en la columna que se muestra inmediatamente porencima de esta.

Campos de valores Sirven para ver datos de síntesis numéricas. Puede usar lasdistintas funciones de síntesis (suma, promedio, min, max,contar, etc.).

Característica Límite máximo

Número máximo de tabla dinámica por hoja Limitado por la cantidad de memoria disponible

Número máximo de elementos únicos por campo

1.048.576

Número máximo de campos de fila o de columna

Limitado por la cantidad de memoria disponible

Número máximo de filtros de relación con respecto a la tabla dinámica

256 (límite posible debido a la cantidad de memoria disponible)

Número máximo de campos de valor con respecto a la tabla dinámica

256

Número máximo de fórmulas con respecto a la tabla dinámica



266

3. Los datos de origen

Los datos de origen de una tabla dinámica deben contener siempre la misma estructura:

- Una fila representa un registro de la base de datos.

- Una columna representa un campo.

A continuación, le presentamos un ejemplo de las primeras filas de datos de origen pro-ducto de una hoja de cálculo de Excel:

j La primera fila debe contener los títulos (nombres de los campos).

Para poder cruzar los datos, la base de datos debe contener al menos dos campos porcruzar más un campo de datos numéricos.

Número máximo de filtros 256 (límite posible en función de la canti-dad de memoria disponible)

Número máximo de campos de valor 256

Fórmulas de elementos calculados con respecto al gráfico dinámico


Número máximo de elementos en una lista de filtro

10.000

Característica Límite máximo

Excel 2016 - Ediciones ENI · 2018-04-20 · Si ya utilizaba las tablas dinámicas en una versión...

Documents

Transcript of Excel 2016 - Ediciones ENI · 2018-04-20 · Si ya utilizaba las tablas dinámicas en una versión...