Relacionar tablas en Excel 2013 y crear tablas dinámicas con ellas

El nuevo Excel 2013 ha nacido con un modelo de datos integrado con lo cual para relacionar tablas ya no tendremos que usar las típicas funciones como BUSCARV. Podemos crear una relación entre dos tablas de datos basada en los datos que coincidan entre ellas. 

Una vez hecho esto podemos crear hojas de Power View y generar tablas dinámicas y otros informes con campos de cada tabla, incluso cuando las tablas sean de orígenes diferentes. 

Pongamos el siguiente ejemplo y vemos como se hace:

Tenemos 2 tablas, una con los productos, las cantidades y los países de origen y otra tabla con la lista de países de origen tal que así:

Debemos seguir los siguientes pasos:

• Asegurarse de que el libro del que queremos sacar la relación contiene al menos dos tablas y que cada una tiene una columna que se pueda asignar a una columna de otra tabla.

• Asigne un nombre significativo a cada tabla: en Herramientas de tabla, haga clic en Diseño > Nombre de tabla y escriba un nombre.

• Compruebe que la columna de una de las tablas tenga valores de datos únicos sin duplicados. Excel solo puede crear la relación si una columna contiene valores únicos.
  
  
• Haga clic en Datos> Relaciones
• En el cuadro Administrar relaciones, haga clic en Nueva.
• En el cuadro Crear relación, haga clic en la flecha abajo de Tabla y seleccione una tabla en la lista.
• En Columna (externa), seleccione la columna que contiene los datos que se relacionan con Columna relacionada (principal). 
• En Tabla relacionada, seleccione una tabla que tenga al menos una columna de datos relacionada con la tabla recién seleccionada en Tabla.
• En Columna relacionada (principal), seleccione una columna que tenga valores únicos que coincidan con los valores seleccionados para Columna.

Con esta relación creada cuando creemos tablas dinámicas podremos analizar datos de las otras tablas, dado que nos saldrán sus columnas para seleccionarlas como filtros, columnas valores o filtros

Análisis rápido en Excel 2013

Una de las nuevas funcionalidades nuevas de excel 2013 es el análisis rápido. Esta funcionalidad se activa cuando seleccionamos un recuadro de datos. Una vez seleccionados nos  aparecerá un pequeño icono de análisis rápido. Al hacer click, podremos visualizar una pequeña ventana en donde podremos dar formato a las celdas, crear gráficos, realizar operaciones matemáticas, crear tablas dinámicas y minigráficos..

Entres las opciones posibles de análisis de datos se encuentran las siguientes:

• Formato de Celdas: Con esta opción podremos dar color a las celdas seleccionadas según criterio que Excel asigna por un algoritmo propio. Entre las opciones tenemos:
  • Barra de Datos: Introduce una barra azul en las celdas que tengan valores, cuando más alto el valor más pintada estará la celda.
  • Escala de Colores: Aplica tonos de color desde el rojo al verde a los valores según el criterio identificado por el Excel.
  • Conjunto de Iconos: Agrega una flecha hacia abajo, derecha o arriba dependiendo el valor que esté analizando.
  • Mayores que: Cuando se hace click, permite pintar celdas en base a si el valor es mayor a un número ingresado en una ventana.
  • 10% de valores superiores: Una herramienta de estadística que se aplica a los valores seleccionados.
  • Borrar Formato: Le quita el formato a los valores seleccionados.
  
  
  
  
  
• Gráficos: permite crear distintos tipos de gráficos según los datos seleccionados. Excel es capaz de entender el tipo de datos seleccionando y nos ofrece los que mejor representarían el análisis. Después puedes ir al botón “Mas Gráficos” para buscar otros en caso de que los recomendados no sean de tu agrado.

• Operaciones Matemáticas: Con los datos seleccionados, podremos elegir entre Suma, Promedio, Recuento, Porcentaje del Total y Total Acumulado. Tenemos dos tipos de presentación, hacia abajo o hacia la derecha, si elegimos hacia abajo tomará todos los datos de forma vertical, si elegimos hacia la derecha, tomara todos los datos de izquierda a derecha fila por fila.
• Tablas: Permite crear una tabla a partir de los datos seleccionados, puede ser una tabla común con un índice superior en cada columna, o bien una tabla dinámica, en donde podemos elegir varias formas de presentarlo.
• Minigráficos: Permite incrustar monográficos hacia la derecha, podemos elegir entre Líneas, Columna o Ganancia o Pérdida.

Extraer valores de un documento XML mediante Excel con la función XMLFILTRO

Imaginaros que tenéis un documento XML y queréis manejarlo en excel extrayéndo de vuestro XML piezas de información. Por ejemplo imaginad que tenemos el siguiente XML:

<?xml version='1.0' encoding="utf-8"?>
<libreria>
  <libro genero='Poesía' fechaPublicacion='1932'
           ISBN='1-861003-11-0'>
    <titulo>Poeta en Nueva York</titulo>
    <autor>
      <nombre>Federico</nombre>
      <apellidos>García Lorca</apellidos>
    </autor>
    <precio>8.99</precio>
  </libro>
  <libro genero='Novela' fechaPublicacion='1967'
           ISBN='0-201-63361-2'>
    <titulo>The Confidence Man</titulo>
    <autor>
      <nombre>Herman</nombre>
      <apellidos>Melville</apellidos>
    </autor>
    <precio>11.99</precio>
  </libro>
</libreria>

si queremos sacar los titulos y los precios de todos los libros podemos usar la función
XMLFILTRO(xml,xpath)
Deberíamos ir a dos casillas y emplear la función de la siguiente forma:

XMLFILTRO(A1; "//libro[1]/titulo")  
XMLFILTRO(A1; "//libro[2]/titulo")
XMLFILTRO(A1; "//libro[1]/precio")
XMLFILTRO(A1; "//libro[2]/precio")


Quedando como en la siguiente imagen:

Funcion Excel para sacar una subcadena de una cadena mayor de caracteres

La función substring en Excel se llama función EXTRAER. Si por ejemplo queremos sacar los primeros cinco caracteres de una cadena mayor, por ejemplo en la cadena "PERRERA" lo haríamos así:

Imaginemos que la cadena esta en la celda primera de la fila primera

=EXTRAER("A1", 5)   daría PERRE

Si no existe esta función las funciones substring en Excel son:

IZQUIERDA(A,B) sacar B posiciones de la cadena A empezando desde la izquierda de la cadena
DERECHA(A,B) sacar B posiciones de la cadena A empezando desde la derecha de la cadena
MID(A,B,C) sacar de la cadena A, la cadena comprendida entre las posiciones B a C

Agregar 0's a la izquerda en excel

El otro día veíamos como agregar ceros a la izquierda en una consulta SQL. Cuando se exporta a Excel o bares en Excel algún fichero de texto o csv los ceros a la izquierda suelen desaparecer pues Excel da formato a las celdas y detecta que es de tipo numérico quitando estos ceros. Ahí van 2 métodos para poner 0's a la izquierda en Excel

Agregar ceros mediante "Formato de celdas"

Seleccionamos el rango que contiene los valores y hacemos clic derecho con el ratón para seleccionar la opción Formato de celdas.  Se mostrará el siguiente cuadro de diálogo:




Seleccionamos "Personalizada" y dentro de Tipo ponemos tantos ceros como posiciones de dígitos necesitamos en los datos. En el ejemplo he colocado 6 ceros para que todos los valores numéricos tengan seis posiciones y las faltantes sean rellenadas con ceros. Al hacer clic en el botón Aceptar obtenemos:



Observa que la celda A1 despliega “000115” pero la barra de fórmulas nos indica que el valor de dicha celda sigue siendo 115. Este método afecta solamente la presentación de los datos y no su valor.

Rellenar con ceros usando la función TEXTO

La alternativa que tenemos para modificar directamente el valor de la celda usar la función TEXTO la cual nos convierte un valor numérico en texto aplicando un formato específico. Observa el resultado de utilizar esta función sobre los datos de ejemplo:



La función aplica el formato indicado a cada valor numérico y lo convierte en texto. Los valores de la columna B son texto porque Excel los ha alineado a la izquierda de la celda. Es así como podemos rellenar con ceros a la izquierda en Excel utilizando la función TEXTO.