USO DE LAS FUNCIONES DE EXCEL PARA LA IMPORTACION DE DATOS.

Es posible importar a Excel datos de archivos creados con otros programas, con Excel puedes abrir fácilmente archivos de texto, guardados con extensión txt y con extensión csv.

Las utilidades son enormes, así como el ahorro de tiempo, el aumento de productividad, la eliminación de errores,…Y como ejemplo de la aplicación que le puedes dar entre otras importar datos de cuentas de correo o números de teléfono, importar cuadro de existencias generado por el programa de almacén, importar los datos del programa de contabilidad cuando este no te los da en Excel.

Explorar datos importados es sencillo mediante una tabla dinámica. En una tabla dinámica, arrastre los campos (parecidos a las columnas de Excel) de las tablas (como las tablas que acaba de importar de la base de datos de Access) a distintas áreas de la tabla dinámica para ajustar el modo en que se presentan los datos. Una tabla dinámica tiene cuatro áreas: FILTROS, COLUMNAS, FILAS y VALORES.

Puede que se requieran varias pruebas para determinar a qué área se debe arrastrar un campo. Puede arrastrar todos los campos que quiera a las tablas hasta que la tabla dinámica presente los datos del modo en que desea verlos. No dude en probar arrastrar campos a diferentes áreas de la tabla dinámica; los datos subyacentes no se ven afectados cuando arrastra campos a una tabla dinámica.

El libro de Excel incluye una tabla denominada Anfitriones. Hemos importado Anfitriones copiándola y pegándola en Excel y dando formato de tabla a los datos. Para agregar la tabla Anfitriones al modelo de datos, debemos establecer una relación. Vamos a utilizar Power Pívot para representar visualmente las relaciones en el modelo de datos. Luego crearemos la relación.

Para conectar Anfitriones al modelo de datos, es necesario un campo con valores únicos que identifiquen cada fila de la tabla Anfitriones. Después, podemos realizar una búsqueda en nuestro modelo de datos para ver si existen los mismos datos en otra tabla. La búsqueda en la vista de diagrama no nos permite hacer esto. Con Anfitriones seleccionada, vuelva a la vista de datos.

Después de examinar las columnas, nos damos cuenta de que Anfitriones no tiene una columna de valores únicos. Tendremos que crearla usando una columna calculada y Expresiones de análisis de datos (DAX).

Es interesante cuando los datos del modelo de datos tienen todos los campos necesarios para crear relaciones y mezclar datos para visualizar en Power View o en tablas dinámicas. Sin embargo, las tablas no siempre cooperan tanto, por lo

que en la siguiente sección se describe cómo crear una columna nueva usando DAX, que sirve para crear una relación entre las tablas.

Extender el modelo de datos con columnas calculadas

Para establecer una relación entre la tabla Anfitriones y el modelo de datos, y así extender nuestro modelo de datos para incluir la tabla Anfitriones, Anfitriones debe tener un campo único que identifique cada fila. Además, ese campo debe corresponder a un campo del modelo de datos. Esos campos correspondientes, uno en cada tabla, son lo que permite asociar los datos de las tablas.

Ya que la tabla Anfitriones no tiene este tipo de campo, deberá crearlo. Si queremos mantener la integridad del modelo de datos, no podemos usar Power Pívot para editar o eliminar los datos existentes. Sin embargo, puede crear nuevas columnas mediante campos calculados basados en los datos existentes .Si busca en la tabla Anfitriones y después en otras tablas del modelo de datos, encontrará un buen candidato para campo único que podrá crear en Anfitriones y asociar a una tabla en el modelo de datos. Ambas tablas requerirán una columna calculada nueva para cumplir con los requisitos para establecer una relación.

En Anfitriones, podemos crear una columna calculada única combinando el campo Edición (el año del evento olímpico) y el campo Temporada (verano o invierno). En la tabla Medallas también hay un campo Edición y un campo Temporada, por lo que, si creamos una columna calculada en cada una de esas tablas que combine los campos Edición y Temporada, podremos establecer una relación entre Anfitriones y Medallas.

Establecer la categoría de datos para mostrar correctamente las imágenes

Para que los informes de Power View muestren correctamente las imágenes, debe establecer bien la categoría de datos para la dirección URL de imagen. Power Pívot intenta determinar el tipo de datos que tiene en el modelo de datos, en cuyo caso agrega el término (sugerido) después de la categoría autoseleccionada, pero es buena idea estar seguro

NOTA En ocasiones, los sitios web cambian, de modo que el diseño de Worldbank.org podría ser un algo diferente del que se describe. Como alternativa, puede descargar un libro de Excel denominado Population.

Una vez que haga estas selecciones, haga clic en el botón DESCARGAR y, a continuación, elija Excel como el tipo de archivo. El nombre del libro, tal como está descargado, no es muy legible. Cambie el nombre del libro a Population.xls y, a continuación, guárdelo en una ubicación a la que obtenga acceso.

También hay algunos campos que no son útiles para la creación de informes. Los datos subyacentes pueden ser importantes, pero si se ocultan campos de las herramientas cliente, como tablas dinámicas y Power View, la navegación y la selección de los campos para incluir en los informes.

Excel cuenta con una gran variedad de funciones dependiendo del tipo de operación o cálculo que realizan. Estas funciones pueden ser matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda, referencia y de información.

MANEJO DE FUNCIONES DE EDICIÓN DE EXCEL:

Una función es una fórmula predefinida por Excel 2007 (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene.

La sintaxis de cualquier función es:

nombre_función(argumento1;argumento2;...;argumentoN)

Siguen las siguientes reglas:

• Si la función va al comienzo de una fórmula debe empezar por el signo =.

• Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis.

• Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones.

• Los argumentos deben de separarse por un punto y coma ;.

Ejemplo: =SUMA (A1:C8).

Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información.

Excel permite que en una función tengamos varios operadores para tratar los datos. Los operadores son símbolos que identifica Excel con operaciones aritméticas y es el enlace entre 2 argumentos.

La mayoría de las funciones pueden necesitar uno o más argumentos. Si la función contiene más de un argumento, éstos han de ir separados por el carácter

Punto y coma (;) o según el definido como separador de listas para Windows. Al escribir una función no se han de incluir espacios en blanco, a no ser que dichos espacios vayan entre comillas.

Para introducir una función conjuntamente con sus argumentos en una hoja de cálculo y mediante el asistente de funciones, seguir el siguiente proceso:

1. Seleccionar la celda en la que se desee introducir la función.

2. Hacer clic sobre el botón Pegar Función, y se accede al cuadro de

Diálogo en el que se debe elegir la función que se desea utilizar. En cuadros de diálogo posteriores se podrán elegir los argumentos para dicha función.

3. El cuadro de diálogo muestra las distintas categorías de funciones

Disponibles en Excel. Estas categorías clasifican el gran número de funciones disponibles en grupos más reducidos. Excel muestra también una lista de las funciones utilizadas más recientemente. Si la función deseada no aparece entre las más recientemente utilizadas y no se está Operación de Microsoft Excel

Página 58 Guía del Usuario

Centro de Capacitación en Informática seguro de la categoría a la que pertenece, se selecciona la categoría.

Todas y se busca en el conjunto de todas las funciones disponibles. En la parte inferior del cuadro de diálogo aparece una breve descripción de la función seleccionada, así como de sus argumentos. Si se desea más información sobre esa función se puede hacer clic en el botón Ayuda.

Edición de fórmulas y funciones

Luego de haber introducido funciones en una fórmula, existe la posibilidad de modificarlas o editarlas. Para esto, se puede volver a usar el Asistente de Funciones o bien se pueden editar manualmente.

Para editar las funciones de una fórmula usando el Asistente de Funciones,

Seguir los siguientes pasos:

1. Seleccionar la celda que contiene en la fórmula, la función a modificar.

2. Hacer clic en el botón Pegar función o seleccionar de la barra de fórmulas el botón Modificar fórmulas. Aparecerá el Asistente de Funciones mostrando la primera función que aparezca en la fórmula contenida en la celda activa.

3. Cambiar los argumentos que se deseen en esa primera función de la

Fórmula.

4. Cuando se terminen de realizar los cambios, seleccionar el botón aceptar. Si hay alguna otra función que se desee editar en la misma fórmula, hacer clic sobre dicha función en la barra de fórmulas, así de esta manera Excel colocará el cuadro de diálogo para dicha función.

5. Repetir los pasos tres y cuatro para cada función que se desee editar

Excel nos permite buscar la función que necesitamos escribiendo una breve descripción de la función necesitada en el recuadro Buscar una función: y a continuación hacer clic sobre el botón, de esta forma no es necesario conocer cada una de las funciones que incorpora Excel ya que él nos mostrará en el cuadro de lista Seleccionar una función: las funciones que tienen que ver con la descripción escrita. Para que la lista de funciones no sea tan extensa podemos seleccionar previamente una categoría del cuadro combinado O seleccionar una categoría: esto hará que en el cuadro de lista sólo aparezcan las funciones de la categoría elegida y reduzca por lo tanto la lista. En el cuadro de lista Seleccionar una función: hay que elegir la función que deseamos haciendo clic sobre ésta. Observa como conforme seleccionamos una función, en la parte inferior nos aparecen los distintos argumentos y una breve descripción de ésta. También disponemos de un enlace Ayuda sobre esta función para obtener una descripción más completa de dicha función. Al final, hacer clic sobre el botón Aceptar. Una vez elegida la función con la que se quiere trabajar se abre una nueva ventana en la que se piden los argumentos de la función, dependiendo de con cual estemos trabando ésta será diferente. En el caso del ejemplo siguiente continúa siendo la SUMA:

Todas las funciones tienen en común, el nombre de la función la cual la identifica y una serie de argumentos, todos ellos separados por puntos y comas, todos ellos dentro de paréntesis. Veremos que puede haber alguna función que no tenga ningún tipo de argumento, pero siempre existirán los paréntesis después del nombre de la función.

Para poder trabajar con las funciones es importante conocerlas todas por separado ya que cada una de ellas nos ofrece un resultado diferente. Sabiendo lo que deseamos y conociendo las funciones es más fácil poder encontrar la combinación de funciones con las que podemos encontrar el resultado deseado.

EXPORTAR DE EXCEL A TXT:

Crear un archivo de texto delimitado por comas

El procedimiento para crear un archivo de texto delimitado por comas (CSV) es prácticamente similar al ejemplo anterior con la diferencia de que en el cuadro de diálogo Guardar como debemos seleccionar el tipo de archivo CSV (delimitado por comas):

Exportar datos de Excel a un archivo CSV

Obtendremos las mismas advertencias que en el ejemplo anterior pero como resultado tendremos el siguiente archivo de texto

Supongamos que para nuestra hoja de datos de ejemplo necesitamos crear un archivo de texto que tenga un ancho máximo de 15 caracteres, tanto para la columna Nombre como para la columna Apellido, y una longitud máxima de 25 caracteres para el correo electrónico. Los datos los generaré en una hoja diferente y utilizaré la siguiente fórmula para la primera columna: a función principal de esta fórmula es la función CONCATENAR que como primer argumento tiene el texto original de la Hoja1. El segundo argumento de la función CONCATENAR es la función REPETIR que tendrá el objetivo de generar una cadena de espacios en blanco cuyo número estará determinado por la diferencia entre el número máximo de caracteres admitidos (15) menos el número de caracteres de la cadena de texto original el cual obtenemos con la función LARGO. Por ejemplo, para la celda con el nombre Carlos, la función REPETIR generará una cadena de 9 espacios en blanco que será concatenada a la derecha de la cadena de texto

Exportar datos a un archivo de texto guardándolo

Puede convertir una hoja de cálculo de Excel en un archivo de texto con el comando Guardar como.

Haga clic en el botón de Microsoft Office Imagen del botón de Microsoft Office y, a continuación, haga clic en Guardar como. Aparecerá el cuadro de diálogo Guardar como. En el cuadro Guardar como tipo, elija el formato de archivo de texto para la hoja de cálculo.

Por ejemplo, haga clic en Texto (delimitado por tabulaciones) o CSV (delimitado por comas).

NOTA Cada formato admite grupos de características diferentes. Para obtener más información sobre los conjuntos de características admitidos por los diferentes formatos de archivo de texto, vea El formato y las funciones de Excel no se transfieren a otros formatos de archivo

Exportar datos a Excel: Nociones básicas

Cuando exporta datos a Excel, Access crea una copia de los datos seleccionados y, a continuación, almacena los datos copiados en un archivo que se puede abrir en Excel. Si copia datos de Access a Excel con

Frecuencia, puede guardar los detalles de una operación de exportación para usos futuros e incluso programar la operación de exportación para que se ejecute automáticamente en intervalos establecidos.

Escenarios comunes para exportar datos a Excel

Su departamento o grupo de trabajo utiliza Access y Excel para trabajar con datos. Guarda los datos en las bases de datos de Access pero utiliza Excel para analizar los datos y distribuir los resultados de sus análisis. Su equipo exporta actualmente datos a Excel cuando y como lo necesita pero desea aumentar la eficacia de este proceso.

Acerca de la exportación de datos a Excel

Access no incluye ningún comando “Guardar como” para el formato Excel. Para copiar datos en Excel, debe usar la característica de exportación descrita en este artículo, o bien copiar los datos de Access en el portapapeles y luego pegarlos en una hoja de cálculo de Excel.

Conectar con datos de Access desde Excel

Para traer datos de Access que se puedan actualizar en Excel, puede crear una conexión, almacenada normalmente en un archivo de conexión de datos de Office (.odc), con la base de datos de Access y recuperar todos los datos de una tabla o consulta. La ventaja principal de crear una conexión con los datos de Access en lugar de importarlos es que puede analizar periódicamente estos datos en Excel sin tener que copiarlos o exportarlos constantemente desde Access.

GUARDAR LA ESPECIFICACIÓN DE EXPORTACIÓN:

1., Haga clic en Sí para guardar la información de la operación de exportación para un uso futuro. Guardar la información ayuda a repetir la misma operación de exportación en el futuro sin necesidad de configurar todo el Asistente cada vez.

2., En el cuadro Guardar como, escriba un nombre para la especificación de exportación. Opcionalmente, escriba una descripción en el cuadro Descripción.

3., Si desea realizar la operación a intervalos fijos (por ejemplo, semanal o mensualmente), active la casilla Crear tarea de Outlook.

Al hacerlo, se crea una tarea de Microsoft Office Outlook 2007 con la que podrá ejecutar la especificación con sólo hacer clic en un botón.

4., Haga clic en Guardar exportación.

Si está instalado Outlook, Access lo inicia.

NOTA: Si Outlook no está instalado, aparece un mensaje de error. Si Outlook no está configurado correctamente, se inicia el Asistente para inicio de Outlook. Siga las instrucciones del Asistente para configurar Outlook.

5., En Outlook, revise y modifique la configuración de la tarea (por ejemplo, Vencimiento y Aviso).

Para que se repita la tarea, haga clic en Periodicidad. Esta figura siguiente muestra el programador de tareas con algunos valores típicos:

1., Haga clic en Guardar y cerrar.

Ejecutar una tarea guardada

1., En el panel de navegación de Outlook, haga clic en Tareas y, a continuación, haga doble clic en la tarea que desea ejecutar.

2. En la ficha Tarea, en el grupo Microsoft Office Access, haga clic en Ejecutar importación.

3. En Excel, abra el libro de destino y compruebe que todos los datos se hayan copiado a las celdas correctas.

Si eligió copiar sólo los datos y no el formato en un libro existente, éste puede contener varias hojas de cálculo. Busque la hoja que corresponda a la operación de exportación y revise las celdas de esa hoja.

Solucionar valores faltantes o incorrectos en la hoja de cálculo. En la tabla siguiente, se describen distintas maneras de solucionar errores comunes.

SUGERENCIA: Si observa que sólo faltan unos pocos valores, corríjalos en el libro de Excel. Si no es así, corrija el objeto de origen en la base de datos de Access y repita la operación de exportación.

Problema Descripción y resolución

Campos multivalor

Los campos que admiten varios valores se exportan como una lista de valores separados por signos de punto y coma (;). Vea la Ayuda de Excel para obtener más información.

Imágenes, objetos y datos adjuntos

No se exportan elementos gráficos (como logotipos, contenido de campos Objeto OLE y datos adjuntos que son parte de los datos de origen). Agréguelos manualmente a la hoja de cálculo después de completar la operación de exportación.

Graph Al exportar formularios o informes que contienen un objeto de Microsoft Graph, no se exporta el objeto de Graph.

Datos en la columna incorrecta

Los valores nulos de la hoja de cálculo resultante son reemplazados a veces por datos que deberían estar en la columna siguiente.

Valores de fecha que faltan

No se exportan los valores de fecha anteriores al 1 de enero de 1900. Las celdas correspondientes de la hoja de cálculo contendrán un valor nulo.

Faltan expresiones

No se exportan a Excel las expresiones que se utilizan para calcular valores. Sólo se exportan los resultados de los cálculos. Agregue manualmente la fórmula a la hoja de cálculo de Excel después de completar la operación de exportación.

Faltan subformularios, sub-informes y hojas secundarias de datos

Al exportar un formulario, un informe o una hoja de datos, sólo se exportan los principales. Repita la operación de exportación para cada subformulario, sub-informe y hoja secundaria de datos que desee exportar.

Columnas faltantes o con formato incorrecto

Si ninguna de las columnas tiene formato en la hoja de cálculo resultante, repita la operación tras activar la casilla Exportar datos con formato y diseño en el Asistente. A la inversa, si sólo algunas columnas parecen tener un formato diferente de las versiones en el objeto de origen, aplique el formato que desee manualmente en Excel.

Dar formato a una columna en Excel

Problema Descripción y resolución

1. Abra el libro de Excel de destino y muestre la hoja de cálculo que contiene los datos exportados.

2. Haga clic con el botón secundario en una columna o un rango de celdas seleccionado y haga clic en Formato de celdas.

3. En la ficha Número en Categorías, seleccione un formato como Texto, Número, Fecha, Hora.

4. Haga clic en Aceptar.

Valor "#" de una columna

Es posible que vea el valor # en una columna que corresponde a un campo Sí/No de un formulario. Esto puede ocurrir si inicia la operación de exportación desde el panel de navegación o en la vista Formulario. Para resolver este problema, abra el formulario en la vista Hoja de datos antes de exportar los datos.

Indicadores de error o valores de error

Compruebe las celdas por si hubiera indicadores de error (triángulos verdes en las esquinas) o valores de error (cadenas que comienzan por el carácter # en lugar de los datos correspondientes).