Migrar tablas transaccionales en excel a BD en Access

Como migrar tablas transaccionales en Excel a BD en Access

Una forma muy interesante para quienes quieren trabajar con Access y tienen una gran cantidad de información organizada en tablas en Excel es importar tablas o libros que se encuentran en nuestros archivos excel, convirtiendo la información de formato Excel a formato Access.

Microsoft Access permite importar o vincular una tabla de datos contenida en otra base de Access, e incluso datos de otros programas y formatos de archivo.

¿Qué diferencias existen entre importar y vincular?

Si se importan los datos se crea una copia de la información en una tabla nueva de la base de datos. En este caso la tabla o archivo de origen no se modificará. Tras realizar esta operación de importar, la tabla creada en Access no depende en absoluto de la original, no existe nexo de unión entre ambas.

Si se vinculan los datos se establece una conexión a los datos de otra aplicación de modo que los datos se puedan ver y modificar tanto en la aplicación original como en Microsoft Access. Es por ello que el formato del origen de datos externo no se modifica, pero se pueden agregar, eliminar o editar sus datos utilizando Microsoft Access. La vinculación o conexión entre el archivo de datos de la otra aplicación y la tabla vinculada de Access es bidireccional, es decir, si modificamos los datos en Access, quedan modificados en el archivo original, y si se modifican en el archivo original, también repercute dicha modificación en Access.

Hay que tener en cuenta que al eliminar el icono que representa a una tabla vinculada se elimina el vínculo con la tabla pero no la tabla externa, manteniéndose el archivo original intacto. Se rompe "el cordón umbilical" que existe entre ambas fuentes de datos.

Microsoft Access utiliza iconos diferentes para representar las tablas que están guardadas en la base de datos actual y las tablas vinculadas, siendo el de estas últimas distinto en función del tipo de archivo vinculado.

Importar o Vincular datos.

En la nueva versión de Access 2007, todas las opciones de Importación se han agrupado en un mismo grupo dentro de la misma ficha en la cinta de opciones: La Ficha Datos Externos. Esto nos va a facilitar muchísimo la labor de importación de datos externos, haciendo más sencilla la búsqueda de las opciones correspondientes.

Acá nos muestra los iconos de los programas desde los cuales queremos importar los datos, en donde a la vez también podemos exportar datos hacia otros programas.

Los pasos a seguir para importar o vincular datos son los que se detallan a continuación:

Dependiendo del formato de archivo origen del que deseamos importar los datos en una tabla de Access, seleccionaremos el botón correspondiente del grupo Importar de la Ficha Datos Externos. En nuestro ejemplo, vamos a realizar la importación de datos desde de una hoja de cálculo de MS Excel. Haremos clic, por tanto, en el botón de Excel

Se inicia el Asistente de obtención de datos externos que incluye Access 2007. En esta primera pantalla deberemos indicar el origen de datos, es decir, el archivo de Excel (en nuestro ejemplo) que contiene los datos que queremos importar. También indicaremos el tipo de importación: en una nueva tabla (sin vinculación) o vincular los datos en una nueva tabla.

Para especificar el origen de datos hacemos clic en Examinar.

Indicaremos la ubicación de la carpeta que contiene el archivo de Excel cuya importación de datos queremos realizar. Lo seleccionamos el archivo en concreto y hacemos clic en Abrir.

Especificaremos el tipo de importación: en una nueva tabla de la base de datos actual.

Hacemos clic en ACEPTAR.

Se iniciará el Asistente de importación de hojas de cálculo. En el primer paso, se deberá indicar la hoja del libro de Excel que contiene los datos (Hoja1, Hoja2... o el nombre que tuviera). Hacemos clic en Siguiente.

Tras seleccionar la hoja y pulsar el botón Siguiente, en el cuadro de diálogo que se muestra indicar si la primera fila de la tabla corresponde a los títulos de la misma o no (generalmente la primera fila de una tabla de datos suele contener los rótulos o nombres de campos, no siendo por lo tanto datos).

Tras hacer clic en Siguiente, seguir las indicaciones del Asistente, el cual permite establecer ciertas características para cada uno de los campos de la tabla que se va a crear (el nombre que va a tener cada campo, si van a ser indexados o no...Pensar que una vez importada, la tabla va a poder ver modificado su diseño de estructura entrando a la pantalla de Vista Diseño como para cualquier tabla existente en la base de datos actual).

El siguiente paso nos indicará la posibilidad de agregar una clave principal a la nueva tabla, o elegir un campo de los ya existentes como clave principal o bien, prescindir de la misma.

Siguiendo con nuestro ejemplo, vamos a establecer como campo Clave principal el campo DNI. Para ello activamos la opción "Elegir la clave principal" y seleccionamos del desplegable el campo DNI.

Tras hacer clic en Siguiente, en el último cuadro de diálogo indicar el nombre de la tabla que se va a crear y para finalizar, hacer clic sobre el botón "Finalizar".

Como último paso novedoso en esta nueva versión de Access 2007, la aplicación nos permite guardar todos los pasos dados durante el proceso de importación para que, en caso de tener que realizar una misma importación, no tengamos que repetirlos nuevamente.

Al cerrar el cuadro final, vemos como aparece en el panel de exploración de objetos de Access la nueva tabla con los datos de Excel importados.

Finalmente queda como comentario recomendar que cuando tenemos muchas funciones en excel y queremos importar datos a access, es recomendable tener los datos sin ecuaciones, es decir como VALORES, para que así no se produzcan posibles errores en las importaciones de datos.

Leer más...

Cómo realizar pronósticos (de ventas, precios, etc) con Excel

Cómo realizar pronósticos (de ventas, precios, etc) con Excel

Excel posee varias facultades que pueden facilitar el trabajo de realizar pronósticos de diferente índole. A continuación se nombran algunas.
Primero que todo, está la función Estimacion.Lineal, que realiza una estimación por Mínimos Cuadrados Ordinarios.Devuelve estadísticas que describen una tendencia lineal que coincide con puntos de datos conocidos, usando el método anteriormente mencionado (MCO). Debido a que esta función devuelve una matriz de valores, debe ser introducida como una fórmula de matrices.
La ecuación para la línea es:
y = mx + b o
y = m1x1 + m2x2 + ... + b (si hay varios rangos de valores X)
donde el valor Y dependiente es función de los valores X independientes. Los valores m son coeficientes que corresponden a cada valor X, y b es un valor constante. Observe que Y, X y m pueden ser vectores. La matriz que devuelve ESTIMACION.LINEAL es {mn,mn-1,...,m1,b}. ESTIMACION.LINEAL también puede devolver estadísticas de regresión adicionales.


Sintaxis
ESTIMACION.LINEAL(conocido_y,conocido_x,constante,estadística)
Conocido_y     es el conjunto de valores de y que se conocen en la relación y = mx+b.


Si la matriz definida por el argumento conocido_y ocupa una sola columna, cada columna de conocido_x se interpreta como una variable separada.
Si la matriz definida por el argumento conocido_y ocupa una sola fila, cada fila de conocido_x se interpreta como una variable separada.
La matriz definida por el argumento conocido_x puede incluir uno o varios conjuntos de variables. Si se usa una sola variable, conocido_y y conocido_x pueden ser rangos con cualquier forma, siempre y cuando sus dimensiones sean iguales. Si se usa más de una variable, conocido_y tiene que ser un vector (es decir, un rango compuesto por una fila o por una columna).
Si se omite conocido_x, se asume que ésta es la matriz {1;2;3;...} que tiene el mismo tamaño que conocido_y.
Si el argumento constante es VERDADERO o se omite, b se calcula normalmente.
Si constante es FALSO, b se establece como igual a 0 y los valores m se ajustan para encajar en y = mx.
Si estadística es VERDADERO, ESTIMACION.LINEAL devuelve las estadísticas de regresión adicionales, de forma que la matriz devuelta es {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.
Si estadística es FALSO o se omite, ESTIMACION.LINEAL sólo devuelve los coeficientes m y la constante b.


Conocido_x     es un conjunto opcional de valores x en la relación y = mx+b.
Constante     es un valor lógico que especifica si se ha de forzar a la constante b a ser igual a 0.
Estadística     es un valor lógico que especifica si se deberán devolver estadísticas de regresión adicionales.


Las estadísticas de regresión adicional son las que se indican a continuación.

ESTADÍSTICA	DESCRIPCIÓN
se1,se2,...,sen	Los valores de error estándar para los coeficientes m1,m2,...,mn.
seb	El valor de error estándar para la constante b (seb = #N/A cuando constante es FALSO).
r2	El coeficiente de determinación. Compara los valores y estimados y reales, y los rangos con valor de 0 a 1. Si es 1, hay una correlación perfecta en la muestra, es decir, no hay diferencia entre el valor y estimado y el valor y real. En el otro extremo, si el coeficiente de determinación es 0, la ecuación de regresión no es útil para predecir un valor y. Para obtener información sobre el cálculo de r2, consulte la sección de "Observaciones" más adelante en este mismo tema.
sey	El error estándar para la estimación y.
F	La estadística F o valor F observado. Utilice la estadística F para determinar si la relación observada entre las variables dependientes e independientes se produce por azar.
df	Grados de libertad. Utilice los grados de libertad para encontrar valores F críticos en una tabla estadística. Compare los valores que encuentre en la tabla con la estadística F devuelta por ESTIMACION.LINEAL para determinar un nivel de confianza para el modelo. Para obtener información sobre el cálculo de df, consulte el apartado "Observaciones" más adelante en este mismo tema. El ejemplo 4 muestra el uso de F y df.
ssreg	La suma de regresión de los cuadrados.
ssresid	La suma residual de los cuadrados. Para obtener información sobre el cálculo de ssreg y ssresid, consulte la sección "Observaciones" más adelante en este mismo tema.

La ilustración siguiente muestra el orden en que se devuelven las estadísticas de regresión adicionales.

Observaciones
Puede describir cualquier línea recta con la pendiente y la intercepción Y:
Pendiente (m):
Para hallar la pendiente de una línea, frecuentemente indicada por m, tome dos puntos de la línea, (x1,y1) y (x2,y2): la pendiente es igual a (y2 - y1)/(x2 - x1).
Intercepción Y (b):
La intercepción Y de una línea, frecuentemente indicada por b, es el valor de Y en el punto en que la línea cruza el eje X.
La ecuación de una línea recta es y = mx + b. Cuando conozca los valores de m y b podrá calcular cualquier punto de la línea insertando el valor Y o el valor X en esa ecuación. También puede utilizar la función TENDENCIA.

Si sólo tiene una variable X independiente, puede obtener los valores de la pendiente y de la intercepción Y directamente utilizando las fórmulas siguientes:
Pendiente:
=INDICE(ESTIMACION.LINEAL(conocido_y,conocido_x),1)
Intersección Y:
=INDICE(ESTIMACION.LINEAL(conocido_y,conocido_x),2)

La exactitud de la línea calculada por ESTIMACION.LINEAL depende del grado de dispersión de los datos. Cuanto más lineales sean los datos, más exacto será el modelo ESTIMACION.LINEAL. ESTIMACION.LINEAL utiliza el método de mínimos de los cuadrados para determinar el mejor ajuste para los datos. Si sólo tiene una variable X dependiente, los cálculos para m y b se basan en las fórmulas siguientes:

donde x e y son medias de muestras, es decir, x = PROMEDIO(conocido_x) e y = PROMEDIO(conocido_y).

Ejemplo 1   Pendiente e intercepción Y

1 2 3 4 5

A B Valor de y conocido Valor de x conocido 1 0 9 4 5 2 7 3 Fórmula Fórmula =ESTIMACION.LINEAL(A2:A5,B2:B5,,FALSO)

Ejemplo 2   Regresión lineal simple

1 2 3 4 5 6 7

A B Mes Ventas 1 3100 2 4500 3 4400 4 5400 5 7500 6 8100 Fórmula Descripción (Resultado) =SUMA(ESTIMACION.LINEAL(B2:B7, A2:A7)*{9,1}) Calcula las ventas del noveno mes (11000)

En general, SUMA({m,b}*{x,1}) igual a mx + b, el valor Y estimado para un valor X dado. También puede utilizar la función TENDENCIA.También está la función Estimacion.Logaritmica, que hace básicamente lo mismo que la primera, sólo que para una función logarítmica.

Por otro lado, está la función Pronóstico, que calcula o pronostica un valor futuro a través de los valores existentes. La predicción del valor es un valor y teniendo en cuenta un valor x. Los valores conocidos son valores x y valores y existentes, y el nuevo valor se pronostica utilizando regresión lineal. Esta función se puede utilizar para realizar previsiones de ventas, establecer requisitos de inventario o tendencias de los consumidores.

Sintaxis
PRONOSTICO(x;conocido_y;conocido_x)
X     es el punto de datos cuyo valor desea predecir.
Conocido_y     es la matriz o rango de datos dependientes.
Conocido_x     es la matriz o rango de datos independientes.
Observaciones
Si x no es numérico, PRONOSTICO devuelve el valor de error #¡VALOR!
Si no se ha especificado ningún valor para conocido_y o conocido_x, o si contienen un número diferente de puntos de datos, PRONOSTICO devuelve el valor de error #N/A.
Si la varianza de conocido_x es igual a cero, PRONOSTICO devuelve el valor de error #¡DIV/0!
La ecuación de la función PRONOSTICO es a + bx, donde:

y:

y donde x e y son las medias de muestra PROMEDIO(conocido_x) y PROMEDIO(conocido y).

Ejemplo

1 2 3 4 5 6

A B Y conocido X conocido 6 20 7 28 9 31 15 38 21 40 Fórmula Descripción (Resultado) =PRONOSTICO(30;A2:A6;B2:B6) Predice un valor para y dado un valor de 30 para x (10,60725) Fuente: office.microsoft.com

Leer más...

Diferencias en el uso de funciones “.si” y funciones anidadas a la función Si

Diferencias en el uso de funciones “.si” y funciones anidadas a la función Si

Dentro de las herramientas de Excel, se encuentran las Funciones Condicionales que son unas de las más usadas. Sin embargo, a pesar de que muchas de éstas pueden ser escritas de manera muy similar, poseen grandes diferencias entre sí, tal como lo muestra la siguiente figura.

	CONDICIONAL MATRICIAL	CONDICIONAL CON UN CRITERIO	CONDICIONAL CON MÁS DE UN CRITERIO
DESCRIPCIÓN	Permite realizar cálculos entre uno o más campos de una matriz, que cumplan con un cierto criterio o condición.	Considera las celdas que cumplen con un determinado criterio o condición.	Considera las celdas que cumplen con un determinado conjunto de criterios o condiciones.
EJEMPLOS	Promedio(si()); Sumar(si()); Contar(si())	Promedio.si; Sumar.si; Contar.si	Promedio.si.conjunto ; Sumar.si.conjunto; Contar.si.conjunto

La función.si.conjunto es una manera de hacer más eficiente el uso de la funcion.si, dado que puedo utilizar una condición o más, y las funciones matriciales son aún más eficientes, sin embargo, no son muy conocidas, tan así, que algunos expertos señalan que éstas hacen la diferencia entre  quién es especialistas en Excel y quién no.

Además, cabe destacar que antes de que existiera contar.si o contar.si.conjunto se utilizaba la modalidad de contar(si()), al igual que para los otros tipos de funciones como sumar.si o promedio.si.

Pero, ¿para qué se utilizan las funciones(si())?

Las funciones(si())  son utilizadas para realizar cálculos matriciales que cumplan con un determinado criterio o condición, por ejemplo, cuando queremos saber cuál es la cantidad de un cierto producto que ha sido vendida de una lista que incluye a todos los productos ofrecidos por una empresa, o  para saber el monto de dinero total que “vendió” un determinado trabajador, etc…

De manera de hacer más fácil la explicación del uso de estas fórmulas se realizará un ejemplo comparativo entre sumar.si y sumar(si()). Para esto, asumamos una empresa que desea saber cuántas unidades del producto A fueron vendidas durante el día, para lo que se entrega la siguiente tabla resumen de las transacciones:

Comenzaremos obteniendo cálculos básicos, como el total de venta final.

Sin conocer matriciales, lo calcularíamos con =(D3*E3) para cada una de las transacciones, y así finalmente en la celda roja escribir =suma(F3:F11).

Pero, este proceso podría ser más eficiente si utilizáramos una multiplicación matricial, para lo cual se debe seleccionar la matriz (en este caso columna) donde queremos que se nos entregue el resultado, y luego en una de las celdas escribir las dos columnas que queremos que se multipliquen. Para nuestro caso sería =D3:D11*E3:E11 y en vez de hacer click en enter se debe presionar Shift + Control + Enter, como se muestra a continuación.

Sin embargo, muchas veces las empresas necesitan saber el valor de las ventas realizadas por un vendedor, o el total vendido en pesos de un determinado producto. Para esto, buscaremos el valor de las ventas realizadas por cada empleado y de cada producto.

El primer ejemplo muestra cómo se debería utilizar la función sumar.si() para obtener las ventas por cada empleado o producto

Obteniendo los siguientes resultados

Sin embargo, este mismo proceso se puede realizar de manera más eficiente gracias a la fórmula matricial sumar, seleccionado la celda donde queremos que nos entreguen el resultado y escribiendo =suma(si(C3:C11=H3;F3:F11;0)) como lo muestra el siguiente ejemplo. (Recordar que para que la fórmula funcione se debe apretar shift + control + enter).

Pero, ¿cuál es la lógica detrás de esta fórmula?

Nuestro ejemplo tiene una columna con los nombres de los vendedores según cada transacción, y es ahí donde nosotros le pedimos a Excel que busque dónde encuentra la palabra “Olave” y nos entregue una segunda columna con los valores que cumplan con el criterio. De manera gráfica tendríamos algo como:

  

Cuando una celda es FALSO nos entregará un 0, dado que Olave no realizó la transacción, y el valor de la transacción si es VERDADERO, dado que el sí realizó la transacción. Es decir,

Y luego, como le solicitamos sumar, nos entrega el valor de la suma de la última columna generada, es decir,

También podemos averiguar cuál fue la cantidad vendida del producto A por el empleado Gutierrez, sólo es necesario realizar un =SUMA(SI(C3:C11=H5;SI(B3:B11=H8;F3:F11;0);0)) como muestra el siguiente ejemplo:

En este caso el procedimiento que realiza Excel es muy similar que el anterior, sólo que en este caso desarrolla dos matrices de VERDADERO y FALSO, y sólo cuando las dos columnas poseen una misma celda con la palabra VERDADERO entrega el valor de la transacción, sino entrega valor 0.

Por lo tanto, las funciones(si()) es una ampliación a las funciones.si, dado que podemos considerar más de un criterio o condición para resolver nuestro problema o estimación. En ese sentido, es recomendable utilizar la función(si()) cuando tenemos más de una restricción o criterio para encontrar el valor necesario.

Max.si ; Desvest.si ; etc…

Dado que no se cuenta con funciones del estilo max.si o desvest.si, utilizar funciones matriciales es de gran ayuda para poder resolver esta falta de herramientas.

Utilizando el mismo ejemplo anterior, si necesitáramos encontrar el valor máximo de venta de un determinado empleado podríamos utilizar la función MAX(SI(C3:C11=H3;F3:F11;0)), como se muestra en el ejemplo.

Leer más...