• Author / Uploaded
• Rosmel Condezo Reyna

Citation preview

OFIMATICA EXCEL 2016 CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

1

PROGRAMA GENERAL DEL CURSO DE EXCEL

I.

FÓRMULAS Y FUNCIONES, REVISIÓN DE CONCEPTOS BÁSICOS •

Manejo de formatos: Darle clic en la herramienta de formato de celdas en la ficha Inicio, grupo Número.

•

Formatos de número Número: Contiene una serie de opciones que permiten especificar el número de decimales, también permite especificar el separador de millares y la forma de visualizar los números negativos.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

2

Formatos de fecha Fecha: Contiene números que representan fechas y horas como valores de fecha. Puede escogerse entre diferentes formatos de fecha.

Crear un formato personalizado

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

3

Caracteres Especiales para aplicar formatos numéricos.

En las siguientes imágenes se muestra el tipo de formato personalizado y el resultado obtenido para cada uno de los seleccionados:

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

4

Si

un número tiene más dígitos a la derecha de la coma decimal que

marcadores de posición en el formato, el número se redondea a tantas posiciones decimales como marcadores de posición haya. Si hay más dígitos a la izquierda de la coma decimal que marcadores de posición, se muestran los dígitos adicionales. Si el formato sólo contiene signos de almohadilla (#) a la izquierda de la coma decimal, los números menores que 1 comienzan con un punto decimal; por ejemplo, ".5". •

Prioridades de los operadores OPERADORES:

Excel permite que en una función tengamos varios operadores para tratar los datos. Los operadores sn símbolos que identifica Excel con operaciones aritméticas y es de enlace entre 2 argumentos. En la tabla podemos ver los operadores mas utilizados

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

5

En una formula o función pueden utilizarse tanto operadores como sea necesario teniendo en cuenta siempre que los operadores hacen siempre referencia a dos argumentos. Veamos un ejemplo: =SI(Y(B13="SI";C13>=3;D13=(mayor o igual), Herramientas de Datos > Validación de datos. Por el contrario, si quieres aplicar el mismo criterio de validación a un rango de celdas, deberás seleccionar dicho rango antes de ejecutar el comando Validación de datos y eso hará que se aplique el mismo criterio para todo el conjunto de celdas. Ya que es común trabajar con una gran cantidad de filas de datos en Excel, puedes seleccionar toda una columna antes de crear el criterio de validación de datos.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 1

Para seleccionar una columna completa será suficiente con hacer clic sobre el encabezado de la columna. Una vez que hayas hecho esta selección, podrás crear la validación de datos la cual será aplicada sobre todas las celdas de la columna. La opción Omitir blancos Absolutamente todos los criterios de validación mostrarán una caja de selección con el texto Omitir blancos. Ya que esta opción aparece en todos ellos, es conveniente hacer una breve explicación.

De manera predeterminada, la opción Omitir blancos estará seleccionada para cualquier criterio, lo cual significará que al momento de entrar en el modo de

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 2

edición de la celda podremos dejarla como una celda en blanco es decir, podremos pulsar la tecla Entrar para dejar la celda en blanco. Sin embargo, si quitamos la selección de la opción Omitir blancos, estaremos obligando al usuario a ingresar un valor válido una vez que entre al modo de edición de la celda. Podrá pulsar la tecla Esc para evitar el ingreso del dato, pero no podrá pulsar la tecla Entrar para dejar la celda en blanco. La diferencia entre dejar esta opción marcada o desmarcada es muy sutil y casi imperceptible para la mayoría de los usuarios al momento de introducir datos, así que te recomiendo dejarla siempre seleccionada. Crear validación de datos en Excel Para analizar los criterios de validación de datos en Excel podemos dividirlos en dos grupos basados en sus características similares. El primer grupo está formado por los siguientes criterios: •

Número entero

•

Decimal

•

Fecha

•

Hora

•

Longitud de texto

Estos criterios son muy similares entre ellos porque comparten las mismas opciones para acotar los datos que son las siguientes: Entre, No está entre, Igual a, No igual a, Mayor que, Menor que, Mayor o igual que, Menor o igual que.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 3

Para las opciones “entre” y “no está entre” debemos indicar un valor máximo y un valor mínimo pero para el resto de las opciones indicaremos solamente un valor. Por ejemplo, podemos elegir la validación de números enteros entre los valores 50 y 100 para lo cual debemos configurar del criterio de la siguiente manera:

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 4

Por el contrario, si quisiéramos validar que una celda solamente acepte fechas mayores al 01 de enero del 2015, podemos crear el criterio de validación de la siguiente manera:

Una vez que hayas creado el criterio de validación en base a tus preferencias, será suficiente con pulsar el botón Aceptar para asignar dicho criterio a la celda, o celdas, que hayas seleccionado previamente. Lista de validación de datos A diferencia de los criterios de validación mencionados anteriormente, la Lista es diferente porque no necesita de un valor máximo o mínimo sino que es necesario indicar la lista de valores que deseamos permitir dentro de la celda. Por ejemplo, en la siguiente imagen he creado un criterio de validación basado en una lista que solamente aceptará los valores sábado y domingo.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 5

Puedes colocar tantos valores como sea necesario y deberás separarlos por el carácter de separación de listas configurado en tu equipo. En mi caso, dicho separador es la coma (,) pero es probable que debas hacerlo con el punto y coma (;). Al momento de hacer clic en el botón Aceptar podrás confirmar que la celda mostrará un botón a la derecha donde podrás hacer clic para visualizar la lista de opciones disponibles:

Para que la lista desplegable sea mostrada correctamente en la celda deberás asegurarte que, al momento de configurar el criterio validación de datos, la opción Celda con lista desplegable esté seleccionada. En caso de que los elementos de la lista sean demasiados y no desees introducirlos uno por uno, es posible indicar la referencia al rango de celdas que

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 6

contiene los datos. Por ejemplo, en la siguiente imagen puedes observar que he introducido los días de la semana en el rango G1:G7 y dicho rango lo he indicado como el origen de la lista.

Lista de validación con datos de otra hoja Muchos usuarios de Excel utilizan la lista de validación con los datos ubicados en otra hoja. En realidad es muy sencillo realizar este tipo de configuración ya que solo debes crear la referencia adecuada a dicho rango. Supongamos que la misma lista de días de la semana la he colocado en una hoja llamada DatosOrigen y los datos se encuentran en el rango G1:G7. Para hacer referencia a dicho rango desde otra hoja, debo utilizar la siguiente referencia:

=DatosOrigen!G1:G7 Para crear una lista desplegable con esos datos deberás introducir esta referencia al momento de crear el criterio de validación.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 7

Personalizar el mensaje de error Tal como lo mencioné al inicio del artículo, es posible personalizar el mensaje de error mostrado al usuario después de tener un intento fallido por ingresar algún dato. Para personalizar el mensaje debemos ir a la pestaña Mensaje de error que se encuentra dentro del mismo cuadro de diálogo Validación de datos.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 8

Para la opción Estilo tenemos tres opciones: Detener, Advertencia e Información. Cada una de estas opciones tendrá dos efectos sobre la venta de error: en primer lugar realizará un cambio en el icono mostrado y en segundo lugar mostrará botones diferentes. La opción Detener mostrará los botones Reintentar, Cancelar y Ayuda. La opción Advertencia mostrará los botones Si, No, Cancelar y Ayuda. La opción Información mostrará los botones Aceptar, Cancelar y Ayuda. La caja de texto Título nos permitirá personalizar el título de la ventana de error que de manera predeterminada se muestra como Microsoft Excel. Y finalmente la caja de texto Mensaje de error nos permitirá introducir el texto que deseamos mostrar dentro de la ventana de error. Por ejemplo, en la siguiente imagen podrás ver que he modificado las opciones predeterminadas de la pestañaMensaje de error:

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

8 9

Como resultado de esta nueva configuración, obtendremos el siguiente mensaje de error:

Cómo eliminar la validación de datos Si deseas eliminar el criterio de validación de datos aplicado a una celda o a un rango, deberás seleccionar dichas celdas, abrir el cuadro de diálogo Validación de datos y pulsar el botón Borrar todos.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 0

Al pulsar el botón Aceptar habrás removido cualquier validación de datos aplicada sobre las celdas seleccionadas.

VII.

TABLAS DINÁMICAS Definición Una tabla dinámica combina y compara en forma rápida grandes volúmenes de datos. Permitiendo el análisis multidimensional de los datos al girar las filas y las columnas creando diferentes formas de visualizar reportes con los datos de origen. Yendo desde lo general a lo específico. Una tabla dinámica permite modificar el aspecto de una lista de elementos con mayor facilidad, de manera cómoda y resumida. Además, se puede modificar su aspecto y cambiar de lugar los campos. Funcionamiento de una tabla dinámica Los campos que estos contienen controlan la organización

de los datos,

especifique que datos desea incluir y de qué modo desea organizarlos eligiendo de la tabla de origen o de la lista los campos y elementos que aparecerán en la tabla Creación y edición de una tabla dinámica

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 1

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 2

•

Modificación de una tabla dinámica MODIFICAR EL DISEÑO DE UNA TABLA DINAMICA

Una vez creada la tabla dinámica puedes agregar más rótulos de filas o columnas y así mismo agregar un filtro.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 3

•

Estilos de tablas dinámicas

•

Actualización de información de una tabla

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 4

•

Segmentación de datos Insertar segmentación de datos (slicers) La segmentación o S/icers como es conocido en inglés,

permite utilizar un

conjunto de registros que cumplan con una determinada condición al igual que un filtro de tal forma que agiliza y facilita el filtrado de las funciones de cubos o tablas dinámicas

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 5

Seleccionamos el número de hijos y la tabla quedara así

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 6

Herramientas de las tablas dinámicas Campo activo

Agrupar

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 7

ORDENAR

•

Diseño de informes

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 8

•

Gráficas dinámicas

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

9 9

Permite crear un gráfico tomando como fuente de datos la tabla dinámica. Es decir un informe de grafico dinámico representa gráficamente los datos de un informe tabla dinámica.

Para crear un gráfico dinámico,

1. Haz clic en la ficha insertar 2. Haz clic en grafico dinámico. Aparecerá un cuadro de diálogo 3. Seleccionar una tabla o rango el valor aparecerá por defecto 4. Eliges nueva hoja cálculo. 5. Finalmente haz clic en aceptar

O de una tabla dinámica clic en las herramientas de tabla dinámica, clic en analizar, escoger la gráfica deseada y aceptar, tenemos la gráfica y editar de acuerdo a lo que necesitamos

VIII.

MANEJO DE VÍNCULOS o Características, creación de links o Rango de celdas con links o Guardado de hojas con links o Vínculos en un mismo libro de trabajo

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

o Vínculos entre varios libros de trabajo o

Creación

de

fórmulas

con

links

o

Actualización de vínculos o Eliminación de vínculos o Links abiertos

Un hipervínculo es un elemento dentro de un documento electrónico que hace referencia a otro documento o a un punto específico dentro del mismo documento. Los hipervínculos en Excel nos permiten crear enlaces entre las hojas de un libro o enlaces a otros libros de Excel u otros documentos. También es posible crear hipervínculos a páginas Web que abrirán automáticamente el navegador de Internet con la dirección electrónica especificada. Tipos de hipervínculos en Excel Antes de pasar a los ejemplos de creación de hipervínculos en Excel es conveniente conocer los tipos de hipervínculos que tenemos disponibles: • Archivo o página Web existente • Lugar de este documento • Crear nuevo documento • Dirección de correo electrónico

Cada tipo de hipervínculo en Excel tiene características especiales en su funcionamiento pero todos constan de las mismas partes. Partes de un hipervínculo en Excel Todos los hipervínculos en Excel están formados por dos partes: ubicación y descripción. La ubicación de un hipervínculo es la que define la ruta de acceso al otro documento, a la sección del mismo documento o una página Web. La descripción es el texto que se mostrará en pantalla y sobre el cual el usuario hará clic para dirigirse al recurso vinculado. Por ejemplo, en la siguiente imagen puedes observar un hipervínculo cuyo texto descriptivo es “Visitar Microsoft” y su ubicación es la página Web de Microsoft (http://microsoft.com):

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

Ahora que ya conocemos la manera en que están formados los hipervínculos veamos un poco más sobre el comando Hipervínculo en Excel. Cómo crear hipervínculos en Excel El primer paso en la creación de hipervínculos en Excel es abrir el cuadro de diálogo Insertar hipervínculo y eso lo podemos lograr de dos maneras diferentes. La primera opción es hacer clic derecho sobre la celda donde se insertará el hipervínculo y elegir la opción de menú Hipervínculo:

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

La segunda alternativa que tenemos para abrir el cuadro de diálogo Insertar hipervínculo es utilizar el comando que se encuentra en la ficha Insertar dentro del grupo Vínculos:

Ambas opciones anteriores abrirán el mismo cuadro de diálogo Insertar hipervínculo donde podremos elegir cualquiera de los diferentes tipos de hipervínculos mencionados anteriormente: Otra manera de abrir este cuadro de diálogo es utilizar el atajo de teclado Ctrl + Alt + K para las versiones de Excel en español. En caso de tener instalado Excel en inglés deberás utilizar la combinación de teclas Ctrl + K.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

Hipervínculos a un archivo existente Para crear un hipervínculo que abra un archivo existente debes abrir el cuadro de diálogo Insertar hipervínculoy seleccionar la opción Archivo o página web existente. El panel central mostrará los archivos de la carpeta actual y podrás elegir cualquiera de ellos: La opción Buscar en te permitirá elegir los diferentes dispositivos conectados a tu ordenador de manera que puedas encontrar fácilmente el archivo deseado. Al seleccionar algún archivo se mostrará su ubicación en el cuadro Dirección,

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

y aunque podrás modificar dicha dirección, no te recomiendo hacerlo a menos que conozcas la ruta exacta al archivo. El botón Marcador te permitirá elegir un lugar dentro del documento seleccionado, pero esta opción solo funcionará con documentos que puedan ser abiertos por la suite de Microsoft Office. Por ejemplo, después de seleccionar un libro de Excel, el botón Marcador te permitirá elegir la hoja que deseas abrir:

También podrás indicar una referencia a la celda específica que deseas mostrar, o si tu libro tiene algún nombre definido podrás seleccionarlo de la lista. Ya para terminar, en el cuadro de diálogo Insertar hipervínculo, he modificado la opción Texto con el valor “Abrir Libro 2” lo cual ocasionará que dicho texto se muestre dentro de la celda al crear el hipervínculo:

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

También puedes crear un hipervínculo a uno de los archivos abiertos recientemente y para eso deberás pulsar la opción Archivos recientes dentro del cuadro de diálogo Insertar hipervínculo y el panel central mostrará la lista de archivos recientes de la cual podrás elegir alguno. Hipervínculos a una página Web Si en lugar de crear un hipervínculo a un archivo existente deseas abrir una página Web, entonces deberás utilizar el cuadro Dirección para colocar el URL de la página Web y el campo Texto para la descripción del hipervínculo.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

En este ejemplo he creado un hipervínculo hacia la página principal de Excel Total y que se mostrará de la siguiente manera:

Hipervínculos a un lugar del documento Uno de los tipos de hipervínculos en Excel mencionados al inicio de este artículo, es el que nos permite crear un vínculo hacia una celda específica de la hoja, hacia otra hoja de nuestro libro o hacia un nombre previamente definido. Para crear este tipo de hipervínculo debemos seleccionar la opción Lugar de este documento dentro del cuadro de dialogo Insertar hipervínculo:

Este tipo de hipervínculo es el que nos permite crear un sistema de navegación entre las hojas de un libro.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

Hipervínculos para crear un nuevo documento Tal vez uno de los hipervínculos que se utiliza con menor frecuencia es aquél que nos permite crear un nuevo documento al momento de hacer clic sobre el vínculo. Por ejemplo, si deseo crear el libro de Excel llamadoLibro35.xlsx tendré que crear el hipervínculo de la siguiente manera: De manera predeterminada el nuevo libro será colocado en la misma carpeta que el libro Excel abierto pero si deseas indicar una ubicación diferente bastará con hacer clic en el botón Cambiar y elegir una carpeta diferente. Hipervínculos de dirección de correo electrónico El objetivo de los hipervínculos de dirección de correo electrónico es facilitar la creación de un nuevo mensaje. En este caso el cuadro de diálogo Insertar hipervínculo nos permitirá indicar el texto descriptivo, la dirección de correo electrónico y el asunto del mensaje.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

10

Al hacer clic sobre el hipervínculo creado, se abrirá el programa de edición de correos predeterminado en nuestro equipo, que en la mayoría de los casos será Microsoft Outlook, y se mostrará la ventana de mensaje nuevo la cual tendrá precargados los campos de dirección de correo electrónico del destinatario y el asunto del mensaje los cuales serán los valores especificados en la creación del hipervínculo en Excel. Editar los hipervínculos en Excel Si una vez que hayas creado un hipervínculo deseas editarlo, debes hacer clic derecho sobre la celda que lo contiene y elegir la opción Modificar hipervínculo. Eso abrirá el cuadro de diálogo con las opciones del hipervínculo y podremos modificar cada una de ellas.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

Si en lugar de modificar las opciones del hipervínculo deseas removerlo, entonces deberás elegir la opción de menú Quitar hipervínculo. Esto no borrará el texto de la celda sino que solamente eliminará el vínculo creado. Hipervínculos conectados a objetos Es posible hacer que un objeto (formas, imágenes, gráficos) esté asociado a un hipervínculo. Para crear este tipo de enlaces debemos insertar el objeto y posteriormente hacer clic derecho sobre dicho objeto para elegir la opción Hipervínculo y poder crear el tipo de enlace deseado.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

Crear hipervínculos con la función HIPERVINCULO Otra alternativa para crear hipervínculos en Excel es utilizar la función HIPERVINCULO la cual tiene los siguientes dos argumentos: •

Ubicación_del_vínculo (Obligatorio): Texto con la ruta de acceso y el nombre de archivo que se abrirá en el disco duro, en una dirección UNC o en una ruta URL.

•

Nombre_descriptivo (Opcional): Es texto que aparecerá en la celda. Si se omite, la celda presentará el texto de Ubicación_del_vínculo.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

A continuación crearemos cada uno de los tipos de hipervínculos mostrados en las secciones anteriores pero utilizando la función HIPERVINCULO. Hipervínculo a un archivo existente

=HIPERVINCULO("C:UsersMoisesDocumentsLibro2.xlsx", "Abrir Libro2")

Con esta fórmula se mostrará el texto “Abrir Libro2” en la celda y el hipervínculo abrirá el Libro2.xlsx. Cuando se crea este tipo de vínculos es probable que Excel muestre un aviso de seguridad para recordarnos que el archivo no se encuentra en una fuente de confianza. Si quieres que Excel deje de mostrar dicho mensaje puedes agregar la carpeta a la lista de fuentes de confianza desde Archivo > Opciones > Centro de confianza > Configuración del centro de confianza > Ubicaciones de confianza > Agregar nueva ubicación. Hipervínculo a una página Web

=HIPERVINCULO("https://exceltotal.com", "Visitar Excel Total")

Este hipervínculo abrirá la página principal de Excel Total. Hipervínculo a otra hoja del libro

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

=HIPERVINCULO("[Libro1.xlsx]Hoja3!A1", "Ir a Hoja3")

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

1

Para este tipo de hipervínculos es obligatorio especificar tres cosas en el primer

2

argumento de la función: el nombre del archivo encerrado en corchetes, el

3

nombre de la hoja y la celda a la que deseamos crear el vínculo. Si falta alguno

4

de estos tres elementos la función nos devolverá un mensaje de error. También

5

es posible reemplazar el nombre del libro por el símbolo # y de esa manera

6

Excel sabrá que nos estamos refiriendo al libro de trabajo actual:

7

=HIPERVINCULO("#Hoja3!A1", "Ir a Hoja3")

8

La única ventaja de la primera opción es que dentro de los corchetes podemos

9

especificar el nombre de otro archivo y de esa manera crear un hipervínculo

10

que abrirá otro libro en alguna hoja y celda específica.

11

Hipervínculo de envío de correo

12

=HIPERVINCULO("mailto:[email protected]?Subject=Contactando a

13

Microsoft", "Contáctanos")

14

Para estos hipervínculos es mandatorio iniciar con la palabra “mailto:” que

15

indicará a Excel el tipo de hipervínculo que deseamos crear. A continuación

16

debemos especificar la dirección de correo electrónico a quien va dirigido el

17

mensaje y de manera opcional podemos agregar el asunto del mensaje que

18

deberá separarse por el símbolo de interrogación (?) y por la palabra “Subject”.

19

Crear hipervínculos con VBA

20

Es posible crear hipervínculos en Excel desde una macro utilizando el método

21

Hyperlinks.Add el cual nos permitirá especificar todo el detalle del vínculo. Por

22

ejemplo, para crear un vínculo en la celda A1 de la Hoja1 que nos llevará a la

23

Hoja3 del mismo libro podemos utilizar la siguiente instrucción:

24

With Worksheets("Hoja1")

25

.Hyperlinks.Add _

26

Anchor:=.Range("A1"), _

27

Address:="#Hoja3!A1", _

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

5

TextToDisplay:="Ir a Hoja3"

6

End With

En este código puedes observar que dentro del método Hyperlinks.Add están especificados los valores para el parámetro Anchor que indica la celda en donde se insertará el hipervínculo, el parámetro Address que es la ubicación del hipervínculo y finalmente el parámetro TextToDisplay que será el texto mostrado en la celda.

IX.

GRÁFICAS ESPECIALIZADAS Crear un diagrama de flujo y un organigrama 1. Clic en l ficha INSERTAR 2. Clic en grafico SmartArt 3. Elegir una categoría de gráfico, en caso del organigrama

se

encuentra en Jerarquía y Diagrama de Flujos en Proceso 4. Clic en Botón Aceptar. 5. Escribir el texto que necesite.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

X.

ASEGURAR Y COMPARTIR LIBROS

Proteger y compartir un libro

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

Para Proteger el libro

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

Para Compartir el libro: 1. Clic en la Ficha REVISAR 2. Clic en Compartir libro 3. Elegir las opciones deseadas

4. Clic en el botón Aceptar 5. Verificar que en la barra de título aparezca el texto Compartido al costado del nombre del archivo.

o

Proteger libros compartidos

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

o

Permitir a usuarios modificar rangos

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

11

o

Establecer permisos de acceso a un libro

o

Quitar el estado compartido de un libro

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

Rechazar cambios de otro usuario

XI.

INTRODUCCIÓN A LAS MACROS o Definición y tipos de Macros

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

Una Macro es una serie de pasos que se guardan bajo un nombre y se pueden activar con alguna tecla de control y una letra, un botón o manualmente.

Por ejemplo, si repetidamente se emplea una serie de pasos como: Cambiar el Tamaño de Fuente o Tamaño de la letra, poner Negrita, cambiar la Fuente o Tipo de letra y el Color de Fuente o Color de Letra, para no estar repitiendo estos pasos se pueden almacenar en una macro y se ejecutara la macro las veces que se desee.

En resumen una macro consiste en una serie de comandos y funciones que se almacenan en un módulo de Microsoft Visual Basic y que puede ejecutarse siempre que sea necesario realizar la tarea.

Para esto Excel 201O para Windows cuenta con dos herramientas básicas: las macros y los módulos. En este capítulo se revisarán las macros.

Para explicar lo que es una macro, vamos a hacer una analogía entre un micrófono y una cinta, con la computadora y la grabadora de macros.Las macros Excel es un conjunto de instrucciones programadas en la hoja de cálculo electrónica Excel, las cuales automatizan las operaciones que realiza la aplicación ofimática Excel con el objetivo de eliminar tareas repetitivas o realizar cálculos complejos en un corto espacio de tiempo y con una nula probabilidad de error.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

La grabadora de macros funciona de manera similar a una grabadora de cintas. Una graba lo que la persona dice, En la computadora se graba lo que el usuario hace.

o

Creación de archivos habilitados para macros

Configurar el Centro de Confianza

Realice lo siguiente:

1. Clic en la ficha Archivo 2. Luego, Clic en el botón Opciones. 3. Haga clic en Centro de confianza, haga clic en Configuración del Centro de confianza .

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

4. Haga clic en la operen : Mostrar la todas

las aplicaciones

barra de mensajes en

cuando se haya bloqueado el contenido

activo, como macros y controles ActiveX

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

Cambiar la configuración de seguridad de las macros de todos los documentos en el Centro de confianza

La configuración de seguridad de las macros se encuentra en el Centro de confianza. Sin embargo, si trabaja en una organización, es posible que el administrador del sistema haya cambiado predeterminada; por tanto, quizá no pueda

la configuración

hacer cambios en ella.

Nota Al cambiar la configuración de la macro en el Centro de confianza. sólo cambia para el programa de Office que esté utilizando. La configuración

de la macro no cambia para todos los programas de

Office.

Realice lo siguiente:

1.

Clic en la ficha Archivo.

2.

Luego, Clic en el botón Opciones.

3.

En el cuadro de diálogo mostrado, realice lo siguiente:

a) Haga clic en Centro de confianza, en Configuración del Centro de confianza y, a continuación, en Configuración de ActiveX. 4.

Haga clic en las opciones que desee:

Deshabilitar

todas las macros sin

notificación

Haga clic en esta

opción si no confía en las macros. Todas las macros y alertas de seguridad de los documentos se deshabilitan. Si hay documentos con macros sin firmar en las que sí confíe, puede poner estos documentos en una ubicación

de confianza.

Los documentos de ubicaciones

de confianza se pueden ejecutar sin la comprobación del sistema de seguridad del Centro de confianza.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

• la

Deshabilitar

todas

las macros

con notificación

Esta es

configuración predeterminada. Haga clic en esta opción si desea

deshabilitar

las macros, pero desea recibir alertas de seguridad

si

hay macros presentes. De este modo, puede elegir cuándo habilitar esas macros caso por caso. •

Deshabilitar

digitalmente

todas

las

macros

excepto

las

firmadas

Esta configuración es igual que Deshabilitar todas las

macros con notificación

excepto en que si la macro está firmada

digitalmente por un editor de confianza, puede ejecutarse si ya se ha confiado en el editor. De lo contrario, se le notifica. De este modo, puede decidir habilitar esas macros firmadas o confiar en el editor. Todas las macros sin firmar se deshabilitan sin notificación. •

Habilitar

ejecutarse

todas

las

macros

código posiblemente

para permitir

(no

recomendado,

puede

peligroso) Haga clic en esta opción

que se ejecuten todas las macros. Esta configuración

hace que el equipo

sea vulnerable

a código posiblemente

malintencionado y no se recomienda. •

Confiar en el acceso al modelo de objeto de proyectos de VBA

Esta configuración es sólo para programadores.

Sugerencia. seguridad

Puede abrir el cuadro de diálogo de macros desde la ficha

de configuración de

Programador

en la cinta de

opciones.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

i

o

Macros grabadas

Antes de Grabar una Macro

Antes de grabar o escribir una macro hay que tener en cuenta los siguientes puntos:

1. Planifique los pasos y los comandos que desea que ejecute la macro. 2. Si se comete algún error mientras se graba la macro, también se grabarán las correcciones a esos errores que se realicen. 3. Cada vez que se grabe una macro, ésta se almacenará en un nuevo módulo adjunto a un libro.

Al grabar una macro, Excel almacena información

sobre cada paso

dado cuando se ejecuta una serie de comandos. A continuación, se ejecuta la macro para que repita los comandos.

Grabar una nueva macro Veamos un ejemplo sencillo: Generar los días de la semana.

Ejemplo: Generar los días de la semana

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

¿Qué pasos se deben realizar?

1 .

Seleccionar Celda Inicial.

2.

Escribir Lunes en la celda seleccionada.

3.

Arrastrar hasta generar el Día Domingo.

4.

Clic en la celda del día lunes.

Todas estas acciones se deben guardar en un macro con un nombre que la identifique.

Tenga en cuenta que antes de proceder a grabar la nueva macro debe tener la seguridad de realizar las acciones sin error.

Procedimiento de grabación

1.

Seleccione la ficha Programador.

2.

En el grupo Código, realice lo siguiente:

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

3.

Se muestra el cuadro de diálogo Grabar macro.

4.

Se iniciará el proceso de grabación, justo después de hacer clic en el botón de comando [Aceptar).

5.

Seleccione la celda A1.

6.

Estando activa la celda A 1: Escriba Lunes

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

12

7.

Arrastre hasta la celda A7

Arrastre desde aquí, hasta la celda A7

8.

Se generan los días.

9.

Clic en la celda A 1.

10. Detenga la grabación.

Enlazar macro a un

botón

de comando

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

1. Inserte un Botón en la celda insertarlo

E 1, cuando termine de

2. Se muestra el cuadro de diálogo Asignar macro. 3. Haga clic en la macro días. 4. Finalmente

clic en el

botón de comando [Aceptar).

5. Se muestra de la siguiente forma 6. Haga clic sobre el texto botón1, y escriba Genera días.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

7. Resultado Final.

8. Haga clic en cualquier celda vacía y luego clic en el botón Genera días Se muestran los días de la semana a partir de la celda seleccionada. Como se emplea el código VBA al generar una macro es posible:

o

Estrategia para crear macros grabadas

Iniciar la grabación desde la barra de estado Para iniciar con la grabación de una macro en Excel puedes pulsar el botón Grabar macro que se encuentra en la parte inferior izquierda de la barra de estado de Excel.

Iniciar la grabación desde la ficha Programador Si tienes activada la ficha Programador entonces podrás también iniciar la grabación de una macro con el comando Grabar macro que se encuentra dentro del grupo Código.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

Cualquiera de los dos métodos mencionados anteriormente hará que se muestre el cuadro de diálogo Grabar macro donde especificaremos el Nombre de la macro así como algún Método abreviado que deseemos asignar a la macro.

Después de pulsar el botón Aceptar, todas las acciones que realices en Excel serán guardadas como parte de la macro que está siendo grabada. Detener la grabación de la macro Para detener la grabación de la macro debes hacer clic en el comando Detener grabación que se encuentra en la ficha Programador o también puedes pulsar el botón que se encontrará en la barra de estado.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

Editar una macro previamente grabada La edición de una macro de Excel se puede hacer solamente a través de código VBA. Para poder editar una macro debes pulsar el botón Macros que se encuentra en la ficha Programador y dentro del cuadro de diálogo Macro elegir aquella macro que necesites editar:

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

Al pulsar el botón Modificar se abrirá el Editor de Visual Basic con el código de la macro. Debemos tener mucho cuidado con el código de una macro porque cualquier edición incorrecta puede impedir el buen funcionamiento de la misma.

Eliminar una macro Para eliminar una macro debes pulsar el botón Macros, posteriormente seleccionar la macro que será eliminada y finalmente pulsar el botón Eliminar.

o

Referencias relativas y absolutas

Grabando macros con referencias absolutas y relativas Posted on 28 julio, 2014 by Miguel Ángel Simón Royo Cuando utilizamos el grabador de macros, podemos crear una macro usando referencias absolutas o relativas. Si no especificamos nada, el grabador aplicará cada una de las instrucciones en celdas absolutas, idénticas a las modificadas en el momento de la grabación.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

Para ver un ejemplo de cada modelo de grabación, abriremos Excel y reduciremos el tamaño de la ventana de Excel para que ocupe solo la mitad izquierda de la pantalla. Una vez hayamos redimensionado la ventana de Excel a más o menos la mitad de la pantalla, nos dirigiremos a la pestaña “Desarrollador” y pulsaremos el botón de “Visual Basic” del grupo “Código”.

Una vez se abra la aplicación VBA, ajustaremos el tamaño de la ventana de Visual Basic en la mitad de la derecha, para poder ver ambas aplicaciones simultáneamente.

Al disponer de la ventana Excel a un lado y la de Visual Basic al otro, podemos ver los códigos generados con cada paso que demos e incluso hacer cualquier corrección al momento, que podemos trabajar en la ventana de Visual Basic durante la grabación, pues sólo se graba en la macro aquello que realicemos en Excel. Activaremos alguna hoja vacía del documento de Excel y cambiaremos su nombre por “Absolutas”. En la barra de tareas de Excel, ubicada en la base de su interfaz, pulsaremos el conmutador “Grabar macro” que es el botón al lado de “Listo”. De este modo, se abrirá la ventana de “Grabar macro”. Llamaremos a la nueva macro “Meses_absolutos” y estableceremos que se guarde en el libro activo.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

Pulsaremos el botón “Guardar” y en vez de comenzar a grabar la macro, haremos doble clic en el módulo 1 que se acaba de crear dentro del libro de Excel.

Seleccionaremos la celda A1 y escribiremos la palabra “Enero”. Conforme vayamos avanzando, comprobaremos en la ventana de código cómo éste se va completando paso a paso a medida que realizamos cualquier acción. Una vez escrito “Enero” arrastraremos desde la esquina inferior derecha de la celda activa para seleccionar hasta la celda A12 y aplicar el autorrelleno.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

De esta manera se completan los nombres de los doce meses del año y se amplía el código en la ventana de Visual Basic con las instrucciones precisas.

Una vez llegado a este punto, pulsaremos el mismo conmutado de grabación de macros de la barra de estado de Excel que ahora muestra el botón con el icono cuadrado para indicarnos que sirve para detener la grabación de la macro. Si nos fijamos en el código Visual Basic, la primera instrucción indica que se selecciona la celda concreta A1. En la segunda nos indica que se introduce en la celda activa el texto “Enero” y en la tercera que se aplica el autorelleno predeterminado al rango de celdas A1 a A12 que son seleccionadas para terminar.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

Ahora, en el documento que tenemos abierto de Excel, insertaremos una hoja de cálculo nueva que llamaremos “Relativas” y seleccionaremos una celda distinta a A1. En el grupo “Código” de la ficha de “Desarrollador” de Visual Basic activaremos el botón “Usar referencias relativas”.

Pulsaremos

el

botón

“Grabar

macro”

y

la

nombraremos

como

“Meses_relativos”. Comprobaremos en la ventana de Código VBA que en este caso la nueva subrutina se crea directamente en el mismo módulo que teníamos abierto en pantalla.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

13

Pulsaremos en la celda A1 e introduciremos el texto “Enero”. Comprobaremos en el código que la referencia a la celda A1 se indica en relación a la celda seleccionada incialmente, en nuestro caso “ActiveCell.Offset(-8, -2)” que indica que se trata de 8 filas arriba y dos columnas a la izquierda. A continuación se indica la celda efectivamente seleccionada “.Range(“A1”).Select”.

De igual modo que en el ejemplo que hemos seguido con las referencias absolutas, arrastraremos la esquina inferior izquierda para volver a autocompletar los nombres de los doce meses del año, detendremos la grabación y comprobaremos el código creado.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

Insertaremos una nueva hoja en blanco en el libro de Excel a la que llamaremos “Práctica” y seleccionaremos una celda distinta a la A1. Pulsaremos el botón “Ejecutar macro” de la barra de herramientas de VBA y en cuadro Macros seleccionaremos la macro “Meses_absolutos”,

que como comprobaremos insertará los meses del año en las mismas celdas que lo hicimos al grabar la macro (A1:A12), mientras que si ahora ejecutamos la macro “Meses_relativos”, Excel escribirá los meses 8 celdas más arriba y dos columnas a la izquierda de la celda activa.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

o

Ejecución de Macros

Ejecutar una macro grabada Podemos ver las macros que hemos grabado con el comando Macros que se encuentra en la ficha Programador. Al pulsar dicho botón se abrirá el cuadro de diálogo Macros y deberemos pulsar el botón Ejecutar para iniciar con la ejecución de cada una de las instrucciones de la macro.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

XII.

HERRAMIENTAS PARA EL MANEJO DE BASES DE DATOS

o

Texto en columnas

o

Validación de datos

La validación de datos se usa para controlar el tipo de datos o los valores que los usuarios pueden escribir en una celda. Por ejemplo,es posible que desee restringir

la entrada de datos a un intervalo

determinado de fechas, limitar las opciones con una lista o asegurarse de que sólo se escriben números enteros positivos.

La validación

de datos es una función de Excel

que permite

establecer restricciones respecto a los datos que se pueden o se deben escribir en una celda. La validación de datos puede configurarse para

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

impedir que los usuarios escriban datos no válidos. puede permitir

Si lo prefiere,

que los usuarios escriban datos no válidos en una

celda y advertirles cuando intenten hacerlo.

También puede proporcionar mensajes para indicar qué tipo de entradas se esperan en una celda, así como instrucciones para ayudar a los usuarios a corregir los errores.

VALIDAR INTERVALO DE NÚMERO

1. De la tabla siguiente Seleccionar

el

rango

E2:E71

ya que

agregaremos una validación para que solo acepte un número de hijos entre 0 y 5.

2. Vaya a la ficha datos y ejecute el comando "Validación de datos" tal como lo muestra la siguiente imagen indicando la secuencia.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

3. En la

siguiente

ventana

seleccionar

el

criterio

de validación

para que permita números enteros.

4. Ingresar los siguientes valores: 0 como mínimo y 5 como máximo tal como lo indica la siguiente imagen.

5. Seleccionar la ficha "Mensaje de entrada" e ingresar los valores tal como lo indica la imagen.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

6. Seleccionar la ficha "Mensaje de error" e ingresar los valores tal como lo indica la imagen.

Verifique que al posicionarse en cualquier celda del rango validado aparecerá un mensaje como el que muestra la imagen inferior.

o

Lista de datos dependientes

Crear listas desplegables dependientes Ahora que ya tenemos listos nuestros rangos nombrados podemos crear las listas desplegables. Para eso iré a una nueva hoja de mi libro de Excel, seleccionaré la celda A2 e iré a la ficha Datos > Herramientas de Datos > Validación de datos. En el cuadro de diálogo elegiré la opción Lista y en el cuadro Origen colocará el valor “=Paises” que es el nombre del rango que contiene la lista de países únicos.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

Al hacer clic en el botón Aceptar podremos comprobar que la celda A2 contiene una lista desplegable con los países.

Ahora crearemos la lista desplegable dependiente de la celda B2 y para eso seleccionaré dicha celda e iré a la ficha Datos > Herramientas de datos > Validación de datos. En el cuadro de diálogo mostrado seleccionaré la opción Lista y el en cuadro Origen colocaré la siguiente fórmula:

=INDIRECTO(A2)

La función INDIRECTO se encargará de obtener el rango de celdas cuyo nombre coincide con el valor seleccionado en la celda A2.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

Es muy probable que al hacer clic en el botón Aceptar se muestre un mensaje de advertencia diciendo que: El origen actualmente evalúa un error ¿Desea continuar? Este error se debe a que en ese momento no hay un País seleccionado en la celda A2 y por lo tanto la función INDIRECTO devuelve error, así que solo deberás hacer clic en la opción Si para continuar. En el momento en que selecciones un país de la celda A2, las ciudades de la celda B2 serán modificadas para mostrar solamente aquellas que pertenecen al país seleccionado.

Con estos pasos hemos crear un par de listas desplegables dependientes en Excel las cuales muestran las ciudades correspondientes a un país determinado. Limpiar selección de lista dependiente Las listas dependientes que acabamos de crear en la sección anterior tienen un pequeño inconveniente y es que después de hacer una primera selección de

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

País y Ciudad, al hacer una nueva selección de País, la celda que muestra las ciudades permanecerá con la selección anterior. Para que me entiendas mejor hagamos un ejemplo sencillo. Seleccionaré el país Colombia en la celda A2 y posteriormente en la celda B2 seleccionaré la ciudad Medellín. Hasta ahí todo va bien, pero si ahora selecciono el país México en la celda A2, la celda B2 seguirá mostrando la ciudad Medellín.

Si en ese momento guardamos el libro, tendremos una incongruencia en los datos. La mala noticia es que no existe un comando de Excel para solucionar este problema. La buena noticia es que podemos utilizar código VBA para pedir a Excel que limpie la celda B2 cada vez que haya un cambio en la celda A2. Para agregar el código debemos hacer clic derecho sobre el nombre de la hoja y seleccionar la opción Ver código.

En

las

listas desplegables mostradas

debemos

elegir la opción

Worksheet y Change tal como se muestra en la siguiente imagen.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

14

El código que debemos pegar en esta ventana es el siguiente: 1

Private Sub Worksheet_Change(ByVal Target As Range)

2 3

If Target = Range("A2") Then

4

Range("B2").Value = ""

5

End If

6 7

End Sub

El evento Worksheet_Change se dispara cada vez que se realiza un cambio en una celda de la hoja. Pero ya que estamos interesados en un cambio de la celda A2, comparamos la variable Target para saber si el cambio proviene de dicha celda. En caso afirmativo, limpiamos el valor de la celda B2. Si aplicas esta solución a tus archivos, deberás guardarlos como un Libro habilitado para macros de manera que pueda ejecutarse adecuadamente el código VBA. Agregar datos a las listas desplegables dependientes

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

Si deseas agregar nuevos datos a las listas desplegables, deberás tener cuidado de mantener las referencias adecuadas en cada uno de los rangos nombrados. Por ejemplo, para agregar una nueva ciudad para México insertaré una nueva fila debajo de la ciudad Guadalajara.

Ahora el país México tiene 4 ciudades en lugar de 3 así que será necesario modificar el rango nombrado para sus ciudades. Para hacer este cambio debemos ir a la ficha Fórmulas y hacer clic en el botón Administrador de nombres. Al abrirse el cuadro de diálogo notarás dos cosas: 1. Aunque las ciudades de Perú fueron desplazadas hacia abajo por la inserción de la nueva fila, Excel modificó automáticamente la referencia para indicar que dicho nombre ahora se refiere el rango B18:B20.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

2. Excel no modificó el rango correspondiente a México y en este momento dicho rango termina en la celda B16 por lo que es necesario que modifiquemos manualmente dicha referencia. Para que todo funcione correctamente debo indicar lo siguiente:

=Datos!$B$14:$B$17

Para ingresar esta nueva referencias puedes seleccionar el nombre México y hacer clic en el botón Editar. Se mostrará un nuevo cuadro de diálogo donde podrás indicar la nueva referencia.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

Con este cambio será suficiente para ver la nueva ciudad al momento de seleccionar el país México dentro de las listas desplegables.

Así que, ya sea que vas a agregar nuevas Ciudades o Países deberás poner especial atención a las referencias de los rangos nombrados y deberás editarlas en caso de ser necesario desde el Administrador de nombres. Puedes descargar el archivo de trabajo que he utilizado en este artículo de manera que puedas comprobar por ti mismo la creación de listas desplegables dependientes en Excel. Practica la creación de este tipo de listas e implementa la misma lógica de solución en tus propios datos.

o

Resaltar y quitar datos duplicados en una columna

A veces, los datos duplicados son útiles y a veces solo dificultan la comprensión de los datos. Use el formato condicional para buscar y resaltar datos duplicados. De esa manera puede revisar duplicados y decidir si desea eliminarlos.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

1. Seleccione las celdas en las que desea comprobar duplicados. NOTA: Excel no puede resaltar duplicados en el área Valores de un informe de tabla dinámica. 2. Haga clic en Inicio > Formato condicional >Resaltar reglas de celdas > Valores duplicados.

3. En el cuadro que se encuentra junto a valores con, elija el formato que desea aplicar a los valores duplicados y haga clic en Aceptar.

Quitar valores duplicados Cuando use la característica Quitar duplicados, los datos duplicados se eliminarán de manera permanente. Antes de eliminar los duplicados, es una buena idea copiar

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

los datos originales a otra hoja de cálculo para que no pierda ninguna información de forma accidental. 1. Seleccione el rango de celdas con valores duplicados que desea quitar. SUGERENCIA: Quite esquemas o subtotales de sus datos antes de tratar de quitar duplicados. 2. Haga clic en Datos > Quitar duplicados y, a continuación, debajo de Columnas, active o desactive las columnas donde desea eliminar los duplicados.

Por ejemplo, en esta hoja de cálculo, la columna Enero tiene información de precios que deseo conservar.

Por tanto, he desactivado Enero en el cuadro Quitar duplicados.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

3. Haga clic en Aceptar.

o

Manejo de esquemas

Crear un esquema de columnas 1. Asegúrese de que cada fila de los datos a los que desea aplicar un esquema tiene una etiqueta en la primera fila, contiene datos similares en cada columna y el rango no tiene filas ni columnas en blanco. 2. Seleccione una celda del rango. 3. Ordenar las filas que componen los grupos. 4. Inserte sus propias columnas resumidas con fórmulas inmediatamente a la derecha o a la izquierda de cada grupo de columnas de detalle. NOTA: Para trazar esquemas de los datos por columnas, debe tener columnas de resumen que contengan fórmulas que hagan referencias a las celdas de cada una de las columnas de detalle para dicho grupo. 5. Especifique si la ubicación de la columna de resumen está a la derecha o a la izquierda de las columnas de detalle. Cómo especificar la ubicación de la columna de resumen

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

a. En el grupo Esquema de la pestaña Datos, haga clic en el selector de cuadro de diálogo Esquema.

b. Para especificar una columna de resumen a la izquierda de la columna de detalles, desactive la casilla de verificaciónColumnas resumen a la derecha del detalle. Para especificar una columna de resumen a la derecha de la columna de detalles, active la casilla de verificación Columnas resumen a la derecha del detalle. c. Haga clic en Aceptar. 6. Para aplicar un esquema a los datos, siga uno de estos procedimientos: Aplicar un esquema a los datos automáticamente a. En caso necesario, seleccione una celda del rango. b. En la pestaña Datos, em el grupo Esquema, haz clic en la flecha junto a Grupo y, a continuación, haga clic enAutoesquema.

Aplicar un esquema a los datos manualmente IMPORTANTE: Cuando agrupe manualmente niveles de esquema, es mejor tener todos los datos mostrados para evitar agrupar las columnas incorrectamente. c. Aplique un esquema al grupo externo. Cómo aplicar un esquema al grupo externo i.

Seleccione todas las columnas de resumen subordinadas, así como sus datos de detalle relacionados.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

En el ejemplo siguiente, la columna E contiene los subtotales para las columnas B a D, y la columna I contiene los subtotales para las columnas F a H, y la columna J contiene los totales generales. Para agrupar todos los datos de detalle para la columna J, seleccione las columnas B a I.

A

B

C

D

E

F

1

Regn

Ene

Feb

Mar

T1

Abr

2

Este

371

504

880

1.755

186

3

Oeste

192

185

143

520

773

4

Norte

447

469

429

1.345

579

5

Sur

281

511

410

1.202

124

IMPORTANTE: No incluya la columna de resumen J en la selección. ii. En la pestaña Datos, en el grupo Esquema, haga clic en Agrupar.

Los símbolos del esquema aparecen en la pantalla al lado del grupo. d.

Opcionalmente, aplique un esquema a un grupo interno anidado. Cómo aplicar un esquema grupo interno anidado

i.

Para grupo interno anidado, seleccione las columnas de detalle adyacentes a la columna que contiene la fila de resumen. En el ejemplo siguiente, para agrupar las columnas B a D, que tiene una columna resumida E, seleccione las columnas B a D. Para agrupar columnas F a H, que tiene una fila de resumen I, seleccione las columnas F a H.

A

B

C

D

E

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

F

15

A

B

C

D

E

F

2

Este

371

504

880

1.755

186

3

Oeste

192

185

143

520

773

4

Norte

447

469

429

1.345

579

1

Regn Ene

Feb

Mar

T1

Abr 5 Sur

281

511

410

1.202 124

IMPORTANTE: No incluya en la selección la columna resumida para dicho grupo. ii.

En la pestaña Datos, en el grupo Esquema, haga clic en Agrupar.

Los símbolos del esquema aparecen en la pantalla al lado del grupo. e. Siga seleccionando y agrupando columnas internas hasta que haya creado todos los niveles que desee en el esquema. f.

Si desea desagrupar columnas, selecciónelas y, a continuación, en el grupo Esquema de la pestaña Datos, haga clic enDesagrupar.

g. NOTA: También puede desagrupar secciones del esquema sin quitarlo todo. Mantenga presionada la tecla Mayús mientras hace clic en

del grupo y, a

continuación, en el grupo Esquema de la pestaña Datos, haga clic enDesagrupar. IMPORTANTE: Si desagrupa un esquema mientras los datos de detalle están ocultos, las columnas de detalle permanecen ocultas. Para mostrar los datos, desplácese por las letras de columna visibles adyacentes a las columnas ocultas. En el grupo Celdas de la pestaña Inicio, haga clic en Formato, elija Ocultar y mostrar y, después, haga clic enMostrar columnas.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

15

o

XML

XML es una tecnología diseñada para administrar y compartir datos estructurados en un archivo de texto legible para el usuario. XML sigue directrices estándar del sector y puede ser procesado por una amplia gama de bases de datos y aplicaciones. El uso de XML permite a los diseñadores de aplicaciones crear sus propias etiquetas, estructuras de datos y esquemas personalizados. En resumen, XML facilita considerablemente la definición, la transmisión, la validación y la interpretación de datos entre bases de datos, aplicaciones y organizaciones.

Proceso básico para usar datos XML en Excel El siguiente diagrama muestra cómo los diferentes archivos y operaciones trabajan conjuntamente cuando se utiliza XML con Excel. Básicamente, el proceso se compone de cinco fases:

Agregar un archivo de esquema XML (.xsd) a un libro Asignar elementos del esquema XML a celdas individuales o tablas XML Importar un archivo de datos XML (.xml) y enlazar los elementos XML a celdas asignadas

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

Introducir datos, mover celdas asignadas y aprovechar la funcionalidad de Excel sin perder la estructura y las definiciones XML Exportar datos revisados de las celdas asignadas a un archivo de datos XML IMPORTAR FICHERO XML A EXCEL 1. Abrir un nuevo libro de Excel. 2. Activar la Ficha PROGRAMADOR 3. Seleccionar la categoría Personalizar cinta de opciones

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

4. Activar la ficha programador del listado en la parte derecha “Fichas Principales” tal como lo muestra la siguiente imagen.

5. Verificar que se ha activado la ficha Programador de Excel

6. Abrir el panel de tareas origen

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

7. Después del paso anterior aparecerá la siguiente ventana donde ejecutaremos el botón Asignaciones XML

8. Dele clic en el botón Agregar de la ventana Asignaciones XML

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

9. Buscar y seleccionar el archivo y agregarlo

10. Aceptar la siguiente ventana y aparecerá la asignación XML asignada.

11. Arrastrar cada elemento del panel Origen XML a las celdas A1 y B1 respectivamente hasta que la hoja de Excel quede como la imagen de la derecha

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

12. Posicionar el cursor dentro del rango de celdas de las etiquetas XML arrastradas a la Hoja de cálculo y dale clic en el comando Actualizado datos tal como lo muestr la imagen

13. Por último puede guarde el libro de Excel en el formato de Ud. Desee. La siguiente imagen muestra parte del código fuente del archivo. o

Subtotales

Al insertar subtotales:

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

• Subtotales

se calculan con un función de resumen, como suma o promedio,

mediante la función subtotales. Puede mostrar más de un tipo de función de resumen para cada columna. • Totales generales

derivan de datos de detalle, no de los valores de los

subtotales. Por ejemplo, si usa la función de resumen promedio, la fila total general mostrará el promedio de todas las filas de detalle en la lista, no un promedio de los valores de las filas de subtotales.

Si el libro se establece para calcular fórmulas automáticamente, el comando Subtotal vuelve a calcular valores totales subtotales y automáticamente cuando se modifiquen los datos de detalle. El comando Subtotal también describe la lista para que puedan mostrar y ocultar las filas de detalle de cada subtotal. ¿Qué desea hacer? Insertar subtotales Quitar subtotales Insertar subtotales NOTA: Si filtra datos que contiene los subtotales, los subtotales pueden aparecer ocultos. Para mostrar de nuevo, borrar todos los filtros para obtener más información sobre cómo aplicar filtros, consulte Inicio rápido: filtrar datos con un filtro automático.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

1. Asegúrese de que cada columna de una intervalo de datos para el que desea calcular subtotales tiene una etiqueta en la primera fila, contiene datos similares en cada columna y el rango no incluye las filas en blanco o las columnas. 2. Seleccione una celda del rango. 3. Siga uno de los procedimientos siguientes: Insertar un nivel de subtotales Puede insertar un nivel de subtotales para un grupo de datos, como se muestra en el ejemplo siguiente.

1. En cada cambio en la columna Deporte... 2. …calcula el subtotal de la columna Ventas. a. Para ordenar la columna que contiene los datos que desea agrupar, seleccione dicha columna y, en la ficha Datos, en el grupo Ordenar y filtrar, haga clic en Ordenar de A a Z o en Ordenar de Z a A. b. En el grupo Esquema de la ficha Datos, haga clic en Subtotal.

Se mostrará el cuadro de diálogo Subtotales. c. En el cuadro Para cada cambio en, haga clic en la columna cuyos subtotales desee calcular. Por ejemplo, en el ejemplo anterior seleccionaría Deporte.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

d. En el cuadro Usar función, haga clic en la función de resumen que desee usar para calcular los subtotales. Por ejemplo, en el ejemplo anterior seleccionaría Suma. e. En el cuadro Agregar subtotal a, active la casilla correspondiente a cada columna que contenga valores cuyos subtotales desee calcular. Por ejemplo, en el ejemplo anterior seleccionaría Ventas. f.

Si desea un salto de página automático después de cada subtotal, active la casilla de verificación Salto de página entre grupos.

g. Para especificar una fila de resumen encima de la fila de detalles, desactive la casilla Resumen debajo de los datos. Para especificar una fila de resumen debajo de la fila de detalles, active la casilla Resumen debajo de los datos. Por ejemplo, en el ejemplo anterior esta casilla se desactivaría. h. O bien, puede utilizar de nuevo el comando Subtotales repitiendo los pasos del uno al siete para agregar más subtotales con diferentes funciones de resumen. Para evitar que se sobrescriban los subtotales existentes, desactive la casilla de verificación Reemplazar subtotales actuales. Insertar niveles anidados de subtotales Puede insertar subtotales para grupos internos, anidados dentro de sus grupos externos correspondientes, como se muestra en el ejemplo siguiente.

1. En cada cambio en la columna externa Región...

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

2. …calcula el subtotal de las Ventas para esa región y en cada cambio en la columna interna Deporte. i.

Para ordenar la columna que contiene los datos que desea agrupar, seleccione dicha columna y, en la ficha Datos, en el grupo Ordenar y filtrar, haga clic en Ordenar de A a Z o en Ordenar de Z a A.

j.

Inserte los subtotales externos. Cómo insertar los subtotales externos

i.

En el grupo Esquema de la ficha Datos, haga clic en Subtotal.

Se mostrará el cuadro de diálogo Subtotales. ii.

En el cuadro Para cada cambio en, haga clic en la columna de los subtotales externos. En el ejemplo anterior, haría clic en Región.

iii.

En el cuadro Usar función, haga clic en la función de resumen que desea usar para calcular los subtotales. Por ejemplo, en el ejemplo anterior seleccionaría Suma.

iv.

En el cuadro Agregar subtotal a, active la casilla de verificación correspondiente a cada columna que contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, seleccionaría Ventas.

v.

Si desea un salto de página automático después de cada subtotal, active la casilla de verificación Salto de página entre grupos.

vi.

Para especificar una fila de resumen encima de la fila de detalles, desactive la casilla Resumen debajo de los datos. Para especificar una fila de resumen debajo de la fila de detalles, active la casilla Resumen debajo de los datos. Por ejemplo, en el ejemplo anterior esta casilla se desactivaría.

vii.

O bien, puede utilizar de nuevo el comando Subtotales repitiendo los pasos del uno al seis para agregar más subtotales con diferentes funciones de resumen. Para evitar que se sobrescriban los subtotales existentes, desactive la casilla de verificación Reemplazar subtotales actuales.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

16

k. Inserte los subtotales anidados. Cómo insertar los subtotales anidados i.

En el grupo Esquema de la ficha Datos, haga clic en Subtotal.

Se mostrará el cuadro de diálogo Subtotales. ii.

En el cuadro Para cada cambio en, haga clic en la columna del subtotal anidado. En el ejemplo anterior, seleccionaría Deporte.

iii.

En el cuadro Usar función, haga clic en la función de resumen que desea usar para calcular los subtotales. Por ejemplo, en el ejemplo anterior seleccionaría Suma. Seleccione las demás opciones que desee.

iv.

Desactive la casilla de verificación Reemplazar subtotales actuales.

l. Repita el paso anterior para más subtotales anidados, empezando desde los más externos. Sugerencia

Para mostrar un resumen solamente de los subtotales y de los

totales generales, haga clic en los símbolos de esquema

para mostrar u

ocultar las filas de detalle de los subtotales individuales. Principio de página Eliminar subtotales 1. Seleccione una celda del rango que contiene los subtotales. 2. En el grupo Esquema de la ficha Datos, haga clic en Subtotal.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

17

3. En el cuadro de diálogo Subtotal, haga clic en Quitar todos.

o

Filtros y autofiltros avanzados

Filtro avanzado En Excel puedes realizar un filtrado de datos totalmente personalizado proporcionando los criterios que deseas aplicar a la información. Este tipo de filtrado es conocido como Filtro avanzado y en esta ocasión te mostraré cómo utilizarlo. Supongamos la siguiente tabla de datos.

Los criterios por los cuales se realizará el filtrado deben especificarse dentro de celdas de la misma hoja. Supongamos que deseo filtrar los registros del departamento de Finanzas.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

17

Para este ejemplo coloqué los criterios por arriba de la tabla de datos aunque realmente su ubicación no es de importancia. Aplicar un filtro avanzado a los datos Antes de aplicar el filtro avanzado debo seleccionar la tabla de datos (A4:D13) y posteriormente ir a la ficha Datos y pulsar el botón Avanzadas que se encuentra en el grupo Ordenar y filtrar. Se mostrará el cuadro de diálogo Filtro avanzado.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

17

Observa cómo en el cuadro de texto para Rango de criterios he seleccionado el rango que contiene las condiciones del filtro avanzado necesarias. Solamente resta pulsar el botón Aceptar para aplicar el filtro.

El resultado es precisamente lo que necesito, así que el filtrado avanzado ha sido exitoso. Pero ahora tengo un desafío mayor ¿Cómo podría hacer para especificar condiciones para una segunda columna? Supongamos que deseo filtrar la información de las personas que tienen apellido Hernández y que además pertenecen al departamento de Finanzas. Para filtrar los datos por estos criterios debo agregar dichas condiciones en otra celda.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

17

Observa cómo el Rango de la lista es el mismo que en el caso anterior ya que son los mismos datos, pero elRango de criterios ahora abarca también las celdas que contienen el criterio para el Apellido. Al aceptar los cambios Excel aplicará el filtro avanzado adecuadamente.

De la misma manera puedes especificar un criterio para cada columna. Hasta ahora solamente he especificado una sola condición por columna, pero ahora deseo agregar a los resultados del filtro la información del departamento de Informática. Para ello solamente agregaré una fila adicional al rango de criterios de la siguiente manera.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

17

Al aceptar los cambios Excel filtrará la información de los empleados de Finanzas con apellido Hernández y además mostrará la información de los empleados del departamento de Informática sin importar el apellido que tengan.

Como podrás observar, es factible especificar una condición por cada fila del rango de criterios. De esta manera puedes crear un filtro avanzado en Excel.

CERTIFICACIÓN UNIVERSITARIA EN OFIMATICA

17