Tabla dinámicaContaWin le ofrece esta importante herramienta de análisis interactivo de datos, que puede utilizar para analizar dinámicamente los datos de su contabilidad. Puede utilizar una lista de tabla dinámica para ver y organizar los datos de cualquier tabla de ContaWin, buscar información o detalles y crear resúmenes e informes personalizados.

Usted puede comparar los saldos de las cuentas agrupando por fecha, cuentas, situando los campos de datos como el debe, haber, etc. formando una tabla cruzada que puede ser tan compleja como el usuario diseñe.

A continuación explicaremos los pasos a seguir para construir una tabla dinámica:

-Abrir la tabla de datos (diario, balances, extractos, plan, etc.) de la que desea construir la tabla dinámica. En el ejemplo se hará la tabla dinámica en base al diario de apuntes.

-Acceda al menú Herramientas, opción Office 2000 y elija Tabla dinámica, visualizará la siguiente pantalla:

Construcción tabla dinámica del diario de apuntes

Como usted observará en la parte derecha de la ventana se muestran todos los campos disponibles de la tabla de datos seleccionada, en este caso el diario.

El diagrama de la izquierda se compone de cuatro partes fundamentales: 

Campos de una Tabla Dinámica

Drop Filter Fields here Coloque campos de filtro aquí

Drop Column Fields here Coloque campos de columna aquí

Drop Row Fields here Coloque campos de fila aquí

Drop Totals or Detail Fields here Coloque  campos  de  totales  o  campos detallados aquí

Construya la tabla dinámica arrastrando los campos de la derecha sobre el diagrama a la izquierda.

 

 1.            En el área rotulada como "Drop totals", se debe especificar un campo de datos, en el ejemplo

se ha colocado el Debe y el Haber. Un campo de datos resume datos numéricos, los campos disponibles aparecen a la derecha de la ventana. Arrastre un campo y suéltelo dentro del área rotulada como"Drop totals". Usted observará que al colocar campos numéricos en el área de datos, se resumirá como el resultado de su suma.

2.            Arrastre un campo del área de columna al lugar "Drop column", por ejemplo "Fecha". Para comparar el debe y el haber de cada cuenta en cada mes, puede mover el campo Fecha por

mes al área de columna, como se muestra en el ejemplo. Los elementos de los campos de columna aparecen en laparte superior de la lista de tabla dinámica, sobre los campos de datos.

3.            Arrastre un campo del área de fila al lugar "Drop row", por ejemplo "Cuenta". Mueva el campo de Cuenta al área de fila para organizar los datos por cuenta. Los elementos de los campos de fila aparecen a la izquierda de la lista de tabla dinámica.

4.            Arrastre un campo del área de filtro que puede utilizar para filtrar toda la lista de tabla dinámica con el fin de mostrar los datos resumidos de un elemento en específico. En el ejemplo se ha escogido:Diario (filtrar por tipo de diario)

Cada campo corresponde a una columna de datos y puede reunir varias filas de información. Entre los tipos de campos se incluye: campos de fila, campo de columna, campos de datos y campos de filtro.

El elemento corresponde a datos exclusivos de un campo. Los elementos de datos e los campos de fila y de columna aparecen en las columnas y en las filas. La celda situada en la intersección de una fila y una columna muestra los datos resumidos de cada uno de los elementos en la parte superior de la columna y a la izquierda de la fila.

La flecha de la lista desplegable del campo situada a la derecha de cada campo, haga "click" en esta flecha para mostrar una lista desplegable de la que  podrá seleccionar los elementos que desee mostrar u ocultar.

Con Excel 2013: Tablas Dinámicas perfectas con 3 clicks inShare2

¿Es exagerado pensar que podemos hacer una Tabla Dinámica según nuestras necesidades con tan sólo 3 clicks?

Pues con Excel 2013 esto es perfectamente posible. Vamos a demostrarlo a continuación, basándonos en el nuevo asistente que la aplicación del nuevo Office 2013 ha incorporado para esta última versión. Adiós a largas clases aprendiendo a realizar Tablas Dinámicas:

Teniendo previamente seleccionados los datos que deseamos formen parte de nuestra Tabla Dinámica y estando en la ficha "Insertar"...

CLICK 1:

Hacemos click en el nuevo asistente para Tablas Dinámicas de Excel 2013:

Con ello, obtenemos este cuadro de diálogo:

Vemos que a la izquierda, Excel nos propone 7 modelos de Tablas Dinámicas. Estos modelos que propone el asistente ofrecen distintas posibilidades:

1. Ventas clasificadas por Rutas2. Ventas clasificadas por Rutas y por Clientes3. Ventas clasificadas por Rutas y Agentes4. Ventas por Agentes5. Nº Clientes a los que se ha vendido en cada Ruta6. Nº Pedidos que ha vendido cada Agente en cada Ruta7. Nº Clientes que maneja cada Agente 

Tenemos que elegir cuál se adapta a nuestras necesidades y....

CLICK 2:

Hacemos click en la Tabla Dinámica elegida a la izquierda:

CLICK 3:

Hacemos click en el botón "Aceptar" del asistente:

y tenemos nuestra Tabla Dinámica totalmente utilizable:

... donde podemos apreciar que hemos elegido el modelo nº 2 que decíamos se refería a las Ventas clasificadas por Ruta y Cliente.

Impresionante, ¿no?

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

TABLAS DINAMICAS

 

INTRODUCCIÓN:

Las Tablas Dinámicas son una forma alternativa de presentar o resumir los datos de una lista, es decir, una forma de ver los datos desde puntos de vista diferentes.

El nombre Tabla Dinámica se debe a que los encabezados de fila y columna de la lista pueden cambiar de posición y también pueden ser filtrados.

Con las Tablas Dinámicas también podremos preparar los datos para ser utilizados en la confección de gráficos.

 La comprensión cabal de este tema se obtiene con la práctica y es así como se verá que es uno de los tópicos mas potentes de Excel, principalmente en las versiones mas recientes.

Empezaremos con un ejemplo:

Una empresa de exportación de máquinas agrícolas tiene la siguiente tabla en una Hoja de Excel 2007.donde figuran los datos del 1º trimestre del año.

 

 

a partir de ella se quiere crear una nueva tabla en la que se informe la cantidad de maquinarias exportadas y el detalle de cuantas se vendieron de cada una.

Para crear la tabla que nos responda a estas preguntas, nos ubicamos en cualquier celda de la tabla, luego vamos a la pestaña "insertar" panel "Tablas"

 

en el que pulsamos en "Tabla dinámica", al hacer esto aparece un menú en el que tenemos las opciones de "Gráfico dinámico" y "Tabla dinámica" , pulsaremos en este último como se puede ver

a continuación aparece el panel "Crear tabla dinámica"

en este caso el rango de la tabla base (tabla de partida) queda automáticamente seleccionado, incuyendo los rótulos, también seleccionamos "Nueva hoja de cálculo" y  pulsamos en aceptar y automáticamente se crea la Hoja4 en la que se destacan:

 

1. Una nueva cinta de opciones denominada "Herramienta de tabla dinámica"

 

 

2. Un panel llamado "Lista de campos de tabla dinámica" que es una novedad de Excel 2007 y que tiene un rectángulo en la parte superior, donde se ubican los campos o rótulos de la tabla de origen, también hay cuatro rectángulos, en la parte inferior, denominados " Filtro de informe", "Rótulos de columna", "Rótulos de fila" y "Valores" donde irán apareciendo los rótulos de la tabla a medida que los seleccionemos en la parte suprior en forma de botones como el que se nuestra

 

Los botones se pueden arrastrar de un rectángulo a otro aunque los rótulos que tienen valores numéricos, siempre aparecen en rectángulo "Valores".

como se ve, hasta este momento, tiene las casillas de verificación de rótulos sin marcar , pues bien, es justamente seleccionar las casillas  "MAQUINA"   y   "CANTIDAD"  lo debemos hacer en el próximo paso

Observar que aparecen automáticamente 2 botones.

Listo ya tenemos la primera tabla con las respuestas pedidas recuadradas en rojo

si nos interesara saber solamente el dato de cuantas fertilizadora y sembradora se exportaron, junto con su total, tenemos un comando de filtrado en la parte superior y así obtenemos la siguiente tabla

luego de aplicar el filtro.

Este es un ejemplo didáctico para hacer una introducción, pero se le puede sacar mucho mas el jugo a esta herramienta.

Podemos querer saber el detalle de las máquinas que fueron exportadas y por cual vendedor. En este caso tendremos que seleccionar la casilla del rótulo VENDEDOR y en la nueva Hoja aparece una tabla y el panel "Lista de campos de tabla dinámica" 

 

se ve que en los rectángulos,  mas precisamente en el llamado Rótulo de fila, aparece un nuevo botón, el botón  VENDEDOR  en forma simultánea a la la selección de la casilla de verificación VENDEDOR.

La tabla responde a lo que queremos saber, pero le podemos dar otro aspecto arrastrando el botón VENDEDOR al rectángulo  "Rótulo de columna"

y la tabla queda como la que esta abajo , luego de haberle dado algo de formato

En esta tabla se puede ver, por ejemplo, que Peña vendió 16 fertilizadoras y un tractor.

Sería interesante saber el número de maquinarias exportadas a que país y por cual vendedor.

Para hacer lo , verificamos la casilla "País" y filtramos MAQUINA

y se genera la tabla

 

donde se puede ver que Chuan le vendió 30 máquinas a China.

Hasta ahora nuestra tabla dinámica efectúa sumas, pero puede hacer otras operaciones tales como porcentajes, máximos, mínimo y otras mas que iremos viendo.

Podemos preguntarnos cual fue la máxima cantidad de maquinarias que vendió Peña. Para hacer esto nos ubicamos en una celda cualquiera de la tabla de arriba y apretando el botón derecho del mouse aparece el siguiente menú emergente

en el que pulsamos en "Configuración de campo de valor", como indica la flecha, con lo que aparece el panel también llamado "Configuración de campo de valor"

en el que si vamos a la pestaña "Resumen por" están las opciones de resumen en las que elegiremos Máx y  luego de aceptar, la tabla se transforma en

que nos dice que la cantidad Máxima de maquinarias que vendió Peña es 9, como se ve en el recuadro rojo, en forma adicional podemos ver que esta cantidad fue vendida a Brasil ( verificar con la tabla de partida o tabla base)

Este resultado se puede ver con una simple inspección de los datos, que en este caso son  tres, pero cuando estos aumentan es donde vemos la utilidad del cálculo de un máximo.

TABLAS DINAMICAS CON RANGOS DINAMICOS

 

Una aplicación de los RANGOS VARIABLES, es cuando trabajamos con TABLAS DINAMICAS, ya que podemos agregar o quitar elementos de la tabla origen de datos (tabla base)sin necesidad de actualizar la referencia al rango en forma manual, o sea que se hace en forma automática. Para hacer esto vamos a utilizas NOMBRES , pero no le vamos a dar un nombre a un rango, le daremos un nombre a una fórmula ( Excel considera a las fórmulas como si fueran rangos)dicha fórmula sera el ANIDAMIENTO entre las funciones DESREF Y CONTARA

como ya se vio, la sintaxis de DESREF es

                                       DESREF(referencia ;filas;columnas;alto;ancho)

 

donde los argumentos serán:

referencia: la celda en el ángulo superior izquierdo de la tabla base (A1 si consideramos la tabla del tutorial TABLAS DINAMICASfilas:para este caso es 0columnas: para este caso es 0alto: la cantidad de filas en nuestra tabla baseancho: la cantidad de columnas en nuestra tabla base

Esta fórmula se anidara con CONTARA  para que DEREF se transforme en dinámica quedando

=DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),CONTARA(Hoja1!$1:$1))

 

a esta fórmula le daremos el nombre tabla_base_dinamica

 

con este nombre creamos la tabla dinámica

en este momento ya estamos en condiciones de agregar datos a la tabla base original

si con ella hubiéramos confeccionado la siguiente tabla dinámica

y agregamos otro país, por ejemplo Italia

esta misma tabla se actualiza incorporando este país

luego de ir al panel "DATOS" de la pestaña "OPCIONES" y pulsar en actualizar

MACROS

INTRODUCCION:

Excelsa es programable, lo que puede sorprender a muchos, y si las funciones y el anidamiento de funciones por ellas mismas le dan mucha potencia, esta nueva cualidad le agrega mucha mas, toda la potencia que el conocimiento y la habilidad que un programador pueda darle. Los programas que se realizan en Excel se llaman MACROS y estos van desde simples instrucciones de una o dos líneas de código hasta cosas mucho mas complejas. Lo interesante de todo esto es que para hacer MACROS no hace falta saber programar y aun así  se pueden generar macros muy útiles y que nos pueden ahorrar mucho tiempo y trabajo. Este tipo de MACROS es la que se puede generar con la GRAVADORA DE MACROS.

Es muy común, en el trabajo diario , tener que hacer tareas repetitiivas como por ejemplo hacer una plantilla con un cierto formato,  título, encabezamientos ,etc y sería muy conveniente poder realizarla apretando un botón, bueno, pues esto se pude realizar con la grabadora de macros, la cual grava estas acciones y las traduce a código ( programación VBA)

Supongamos que todos los dias tengo que generar una lista con el movimiento de un comercio de computadoras, para organizar los datos que iremos ingresando, es conveniente escribir los encabezados de cada columna, supongamos que estos encabezados son

y quiero que esten como se ve en la figura de arriba, también quiero que estos encabezados aparezcan automáticamente todas las mañanas(al apretar un botón por ejemplo). Como no se nada de programación VBA voy a utilizar la grabadora de macros. Hay tres formas de poner en funcionamiento la grabadora de macros:

 

1. Vamos a la pestaña Vista y en la sección Macros desplegamos el submenú Macros y dentro de este submenú seleccionamos la opción Grabar macro

2. Pulsando el botón que se encuentra en la parte inferior izquierda de la pantalla de Excel( barra de estado)

 

  de cualquiera de estas dos formas aparece el cuadro de dialogo Grabar Macro

donde podemos dar un nombre a la macro, sin dejar espacios en blanco.

En Método abreviado: elegimos una combinación de la tecla CTRL + "una tecla del teclado", para ejecutar la macro, cuidando de no poner una combinación utilizada por Excel en alguno de sus atajos.

En Guardar macro en: podemos elegir guardar la macro en el libro activo, en el libro de macros personal o en otro libro.

En Descripción: podemos describir que hace de la macro o cualquier otro comentario.

 

Finalmente pulsamos en aceptar y el botón que estaba en la barra de estado se transforma en el que está marcado por la flecha en la figura, comenzando la grabación.

 

(Con este botón también se puede detener la grabación.)

En estas circunstancias, empezando por una celda cualquiera  vamos luego a la celda  A1, escribimos los encabezados con mayúsculas los centramos, hacemos los recuadros correspondientes y detenemos la grabación ( no  olvidarse), esto va a generar un código VBA en forma automática

No tenemos porque entender este código,  solo dire que se puede hacer lo mismo con un código mas reducido o dicho de otra manera, la grabadora de macros genera un código redundante, que se puede reducir aprendiendo Programación VBA

Para que la macro haga su tarea, se puede:

 

1. utilizar el método abreviado, que en nuestro caso es CTRL+ r .2. Ir a las pestaña Programador, sección Código y luego pulsar en Macros

     

luego aparece el cuadro de diálogo Macro, donde aparece la macro creada, para luego apretar el botón ejecutar

También con este cuadro de diálogo podemos eliminar macros.

3. La tercera manera de que una macro grabada se manifieste, es creando un botón.

Para crear un botón  vamos a la pestaña Programador, sección controles y luego pulsamos en el icono Insertar

En los Controles de formulario pulsando donde señala la flecha  aparece un puntero en forma de cruz que nos permite colocar el botón en un lugar cualquiera y automáticamente aparece la ventana Asignar macrodonde se puede seleccionar la macro a la que le queremos asignar el botón

aceptamos y le damos un nombre; en nuestro caso podría ser Encabezados.

Veremos que cuando el mause pasa por el botón, este se transforma en una mano, luego hacemos clic y  se dispara la macro con el resultado esperado

FORMULAS

INTRODUCCION:

Una FORMULA es una expresión que sirve para realizar cálculos produciendo un valor que se asigna a la celda en la que se introduce dicha expresión. Las fórmulas estan formadas por operadores( símbolos matemáticos ) y operandos, que pueden ser números, referencias a celdas y funciónes, estas funciónes deben entregar un valor numérico o lógico, esto último es debido a que para Excel VERDADERO es equivalente al numero uno y FALSO al cero.

Para evitar las confusiones que pueden surgir en el orden en que se realizan las operaciónes, existe una jerarquía que determina que operaciónes se realizan primero y cuales despues, tal jerarquía se denomina GERARQUIA DE PRECEDENCIA DE OPERADORES y esta en la siguiente tabla

TABLA DE PRECEDENCIAS

por ejemplo: -5+7*8 da como resultado 51, pues se realiza primero el producto(56) y luego se resta el 5. Este orden se puede alterar colocando paréntesis, ya que estos tienen el orden de precedencia mas alto, veamos como se puede transformar una expresión según donde se coloque el paréntesis, veamos

en la primera fórmula la prioridad es del producto que tiene un orden de precedencia mayor y el resultado es 61.Esto se altera en la segunda fórmula poniendo paréntesis,  se efectúa primero la suma y luego el producto y el resultado es 96

FORMULAS LOGICAS

Las fórmulas lógicas se diferencian de las fórmulas, en que estas últimas devuelven un resultado numérico, en tanto que las primeras entregan un resultado lógico, es decir: verdadero o falso. Las mas simples sirven para hacer la comparación entre el contenido numérico de dos celdas utilizando los operadores lógicos que se muestran en la siguiente tabla

estos operadores se llaman binarios ya que la operación se realiza entre dos operandos, para Excel estos operandos son el contenido numérico de dos celdas, por lo tanto podemos compararlas.

Supongamos que una empresa quiere saber si el balance semanal de un determinado mes tuvo ganancias o pérdidas, para lo que confeccionó la siguiente tabla

 

donde se introdujo la fórmula

                                        

en la celda D3 y luego se la arrastró hasta la fila 6

 

Cuando esta fórmula se anida   con la función SI , por ejemplo, deja de ser una fórmula para transformarse en una proposición lógica y en este caso podríamos poner en la celda D3 la función

y la tabla queda

que hace que la tabla tenga un aspecto mas claro.

Podemos hacernos una pregunta: quiero saber si 12.000 es menor que el numero que esta B3 y mayor que esta en C3 ¿es correcto poner la fórmula

                                         =B3<12.0000<C3

pongan esta fórmula en D3 y verán que el resultado es 0, Excel no nos dice que esta mal( no da error) pero tampoco da el resultado correcto pues este debería ser " VERDADERO". El problema está en que esta expresión no es la comparación entre dos celdas sino  la comparación de un número entre dos celdas, que es diferente; no es una operación binaria. Esto no quiere decir ( por suerte) que no podamos hacer nada mas con las fórmulas lógicas usando los operadores de la tabla, pero debemos complementarlas con las funciones Y(), O(), NO(), por ejemplo para la expresión que pusimos mas arriba debemos usar la función Y() para obtener la fórmula

que nos da el resultado correcto "VERDADERO". De esta forma podemos escribir muchísimas fórmulas lógicas, que serán proposiciones lógicas si las anidamos con otras funciones.

FORMULAS MATRICIALES

 

INTRODUCCION:

Con las fórmulas matriciales se pueden hacer muchas cosas, es una herramienta de gran potencia,  en general estas fórmulas o funciones se usan para hacer 2 tipos de cosas.:

1. Ejecutar varias operaciónes y devolver un único valor en la celda donde se la introduce.2. Ejecutar varias operaciónes y devolver múltiples valores en distintas celdas.

Las fórmulas matriciales actúan en 2 o mas rangos de valores, los que se denominan, argumentos matriciales, los cuales tienen la característica de tener el mismo número de filas y de columnas, por ejemplo, podrían actuar sobre los rangos A1:A12  y BI:B12.Una fórmula matricial se introduce de la misma forma que la fórmula común, la diferencia es que luego de introducirla hay que apretar las teclas Control+shift+ENTER, con lo que automáticamente es rodeada por llaves y es por eso

que se las conoce como fórmulas CSE. Para una formula matricial multiplicar 2 argumentos matriciales, como A1:A12 *BI:B12. significa multiplicar las celdas A1*B1, A2*B2, A3*B3......A12*B12 si quiero sumar estos resultados parciales uso la formula matricial {SUMA(A1:A12*B1:B12)}, para aclarar los conceptos vamos a tener que hacer mas de un ejemplo, Empecemos por un ejemplo del tipo 1-.

El dueño de una mueblería quiere aumentar la variedad de los productos que vende para lo que decide comprar, parte de los tradicionales, muebles de computación, para lo que cuenta con la siguiente planilla

 

y quiere saber  cuanto tiene que gastar. Decide tomar el camino corto y usa una simple fórmula matricial, veamos lo que hizo

se ve que introdujo la fórmula matricial

de esta forma hizo 3 pasos en uno. Los 3 pasos hubieran sido:

          1- Introducir la fórmula =D2*E2 en la celda F2.

          2- Arrastrar esta fórmula hasta la celda F7

          3- Ubicarnos en la celda F8 y pulsar el icono

o ubicarnos en la celda D9( por ejemplo ) e introducir la función

=SUMA(D2*E2;D3*E3;D4*E4;D5*E5;D6*E6;D7*E7)

se ve que ambas maneras, si bien dan el mismo resultado, son mucho mas tediosas

Se puede aprovechar este mismo ejemplo para mostrar como usar las fórmulas matriciales que devuelven múltiples valores y así explicamos todo el proceso.Usando la misma tabla que al principio vamos a obtener todos los productos parciales

          1º  seleccionamos la columna donde queremos que aparezcan los valores

.

         2º introducimos la fórmula, seleccionando los rangos D2:D7 y  E2:E7 y multiplicándolos

        3º apretamos las teclas Control+shift+ENTER

y obteniendo los productos parciales y por lo tanto múltiples resultados como se ve en el recuadro rojo.

PROMEDIO CON UNA CONDICION

 

Excel 2007 tiene una función que nos da el promedio de un conjunto de valores con una condición, tal función se llama PROMEDIO.SI, con las fórmulas matriciales podemos hacer lo mismo. Haremos un ejemplo con ambas opciones, aclarando que estando la función PROMEDIO.SI, siempre es preferible usar esta alternativa debido a que utiliza menos recursos de nuestro equipo.

Otra historia es para Excel 2003, puesto que en esta versión la única alternativa son las fórmulas matriciales, siendo la razón que PROMEDIO.SI directamente no existe.

Supongamos que una fábrica de autos, lanzó un nuevo modelo en el mes de Enero y quiere saber cual fue el promedio de ventas de los 3  primeros días del mes en cada una de las zonas en las que esta divide al país. Las zonas son: Norte, Sur, Este, Oeste y centro. Para lograr su objetivo se vuelcan los datos de las ventas de esos días en una tabla, con un sector a la derecha para los resultados

la fórmula, para la zona Norte es:

en la figura se hace la comparación con la función  PROMEDIO.SI y se ve que coinciden

MAXIMO CON UNA CONDICION

 

Sería muy útil que Excel 2003/2007 tuviera la fórmula del tipo "máximo con una condición", algo como MAX.SI o MAXIMO.SI , pero si se fijan en el conjunto de funciones de Excel, esta no aparece nada parecido, ni siquiera en Excel 2007, esta es una mala noticia ya que, sin duda seria muy útil; hay muchos problemas que se podrían resolver. Por suerte podemos resolver esta falencia con las FORMULAS MATRICIALES.

Supongamos este problema:

Un grupo de alumnos rindio 3 materias, Matemática, física y química  y quieren saber el puntaje máximo que sacaron.

Los datos se podrían representar en una tabla como la siguiente

Para responder a la inquietud de los alumnos se vuelca la tabla en una Hoja de Excel poniendo una tabla a la derecha para los resultados

donde la fórmula matricial usada es, por ejemplo para el alumno Marquez

como puede verse, es muy parecida a la del tutorial PROMEDIO CON UNA CONDICION

http://www.allexcel.com.ar/formulas%20matriciales/tutoriales/PROMEDIO-CON-UNA-CONDCION/PROMEDIO-CON-UNA-CONDICION.html