AP Excel 2016 AVANZADO Plus -Libro Completo .pdf

Excel Avanzado +

Se prohíbe la reproducción total o parcial de este material – incluido incluido el diseño gráfico-, sea cual fuere el medio, electrónico electrónico o mecánico, mecánico, sin el consentimiento consentimiento por por escrito del autor. autor. Escríbanos a: [email protected] Página 2

Antonio P. Peralta Ch.

Página 3

Excel Avanzado +

“ Toda Toda buena dádiva y todo don perfecto desciende de lo alto, del Padre de las luces, en el cual no hay mudanza, ni sombra de variación.” Santiago Santiago 1:17 (RVR 1960)

Para Ti , Dios en tres personas, bendita bendita trinidad.

Agradecido de ti, Padre, Hijo y Espíritu Espíritu Santo, mi Señor y Salvador Jesucristo, Jesucristo, por tu amor, amor, tu misericordia, y tus capacidades impartidas para poder llevar este proyecto a ser una realidad. Muchísimas gracias, a ti, mi Señor, EL REY. “Al único y sabio Dios, nuestro Salvador, sea gloria y majestad, imperio y potencia,

ahora y por todos los siglos. Amén. ” Judas 1:25 (RVR 1960)

Página 4


“

Y vi a los muertos, grandes y pequeños, de pie ante Dios; D ios; y los libros fueron abiertos, y otro libro fue abierto, el cual es el libro de la vida; y fueron juzgados los muertos por las cosas que estaban escritas en los libros , según sus obras … Y el que no se halló inscrito en el libro de la vida fue lanzado al lago de fuego. Apocalipsis 20:12, 15 ”

Mucho antes de existir una computadora, y que tuviéramos un concepto de listado de datos, bases de datos y principios de normalización; Dios inspiró al escritor de Apocalipsis (vea Apocalipsis 20:12,15), y le mostró un sistema de registro de libros ( libros y el libro de la vida ), con un modernísimo sistema de consultas o filtros, donde pueden identificar los que están inscritos en el libro de la vida…En otras palabr as, Dios en la Biblia muestra lo importante que es llevar registros, tener controles, libros y poder realizar consultas en estos. Imitemos a Dios y creemos buenas bases de datos, controles, consultas y tablas dinámicas agiles, con Excel pudiéramos hacer algo similar. Excel es el programa más completo de la Office© de Microsoft©, y siempre le comento a los participantes al inicio de cualquier tipo de entrenamiento sobre Excel, que Microsoft Excel© es el programa más subutilizado de la suite Office©; ya que la mayoría de empresas, instituciones y personas alrededor del mundo tienen Excel en sus computadoras, y la mayoría de los problemas que tienen en sus áreas y departamentos, se podrían resolver perfectamente con Excel, pero necesitan capacitación especializada para aprovechar todo el potencial de Excel. Este material ha sido desarrollado completamente por el profesor Antonio Peralta, con fin de usarse como material de apoyo en el curso Excel Avanzado+. Uno de los enfoques más interesantes de este material, es ayudar de forma práctica a cada participante para sus labores empresariales, mejorando de forma significativa el análisis de datos y la producción de información de calidad a través de cada c ada departamento, impactando así positivamente positivamente cada área de la empresa. Si sigue los pasos en cada tema, te ma, al finalizar usted aprenderá a: Dominar la producción de gráficos estadísticos; Convertirse en un experto de la impresión de cualquier trabajo en Excel; Trabajar con fórmulas de referencias absolutas; Normalizar listados de datos; Realizar R ealizar consultas simples, combinadas y avanzadas; Aplicar subtotales, consolidaciones; Identificar patrones y tendencias; Controlar los datos digitados en las celdas; Analizar hipótesis con escenarios, buscar objetivos y tablas de datos; y Crear tablas y gráficos dinámicos. • • • • • • • • •

En este completo material sobre Microsoft Excel©, el profesor Antonio Peralta, certificado internacionalmente por Microsoft©, un informático con vasta experiencia en el área y además docente profesional, le conducirá paso a paso (de forma práctica, expositiva, sistemática y con la ilustración requerida según cada herramienta) para que usted suba de nivel hasta convertirse en un usuario Avanzado+.

Página 5

Excel Avanzado +

Vista General del Temario Avanzado Módulo 1. NIVELANDO CONOCIMIENTOS        

Atajos del teclado Formato de celdas Operación con celdas relativas Operación con celdas absolutas Listas personalizadas Pegado especial Formato condicional Fijar paneles

Módulo 2. ANÁLISIS DE HIPÓTESIS    

Creación de escenarios Búsqueda de objetivos Tabla de datos Manejando Prestamos Mensuales y Anuales

Módulo 3. DOMINANDO LAS FUNCIONES      

Lógicas: If, AND, OR, NOT, otras Condicionadas: SumIf, AverageIf, CountIf… Estadísticas: Max, Min, Median Texto y referencia: Upper, Lower, Proper, Conc Búsqueda: Vlookup, Hlookup Fecha y Hora: Today, DateValue

Módulo 4. SEGURIDAD EN EXCEL EXCEL Y AUDITORIA     

Permitir que usuarios editen rangos Bloquear hojas y libros Control de accesos a los libros Encontrar precedentes y dependientes Evaluando fórmulas

BONOS Módulo 1. GENERANDO CONOCIMIENTO             

Creación de una base de datos Ordenar una base de datos Filtros simples Filtros avanzados Subtotales Funciones de base de datos Consultas a base de datos Importar y exportar Formularios y plantillas Creando formulario personalizado Validación de datos Tablas dinámicas Gráficos dinámicos

Página 6

Módulo 2. AUTOMATIZACION DE TAREAS TAREAS con MACROS MACROS    

Macros Creación Modificación Ejecución


Avanzado Módulo 1. NIVELANDO CONOCIMIENTOS        

Atajos del teclado Formato de celdas Operación con celdas relativas Operación con celdas absolutas Listas personalizadas Pegado especial Formato condicional condicional Fijar paneles

Página 7

Excel Avanzado +

ATAJOS DEL TECLADO La palabra ‘atajo’ ‘atajo’ en este contexto se relaciona con el término ‘ Shortcut ’ en inglés. Su

significado tiene que ver con tomar un camino más corto para lograr lo mismo que si hubiese tomado el camino más largo.

Atajos del teclado (shortcut (shortcut ): ): Son combinaciones de dos o más teclas que te permitirán acceder más rápido a comandos y funciones de Excel sin usar el Mouse.

La tecla ALT Está localizada a ambos lados de la barra espaciadora (Spacebar (Spacebar ). ).

1

Al pulsar ALT, el programa Excel mostrará todos los atajos para acceder a las pestañas, secciones e iconos.

Práctica: Vamos a crear un archivo nuevo usando solo el teclado: 1. Pulse la tecla ALT 2. En la pestaña Archivo se ve la letra A, pulse la A, lo mismo que ALT + A 3. Observe las letras en las opciones, para nuevo es e s la N. 4. Y finalmente muestra en libro en blanco la combinación Y1. 5. Esto se resume en pulsar: ALT + A , luego N y por ultimo Y1

2

Resultado 3

4

Página 8


Variaciones por el Idioma Los atajos de teclado pueden variar, dependiendo del idioma, es decir, que si Excel esta en Ingles, la combinacion variara en Español.

Ejemplo: En inglés, para crear un archivo (libro) nuevo en Excel, se utiliza la combinación de teclas (CTRL+N), mientras que en español, se usa CTRL+U.

Atajos del Teclado en español e inglés Español

Inglés

Crear un libro

Ctrl + U

Ctrl + N

Guardar

Ctrl + G

Ctrl + S

General

Vista previa e Imprimir

Ctrl + P

Cerrar un libro

Ctrl + R

Ctrl + W

Abrir un libro

Ctrl + A

Ctrl + O

Ayuda

F1

Revisar ortografía

F7

Celdas absolutas, mixta o mixta o normal

F4

Calcular hojas de cálculo

F9

Movimientos Moverse entre celdas Una celda a la derecha

Tab

Una celda a la izquierda

Shift + Tab

Bajar una celda

Enter

Subir una celda

Shift + Enter

Una pantalla abajo

AvPág / Page Down

Una pantalla arriba

RePág / Page Up

Celda A1 Ultima Celda Cuadro para Referencia IR A

Ctrl + Inicio (Home) Ctrl + Fin (End) F5

Página 9

Excel Avanzado +

Selección Seleccionar la fila

Shift + Espacio (Spacebar)

Seleccionar la columna

Ctrl + Espacio (Spacebar)

Seleccionar todo

Shift + Ctrl + Espacio (Spacebar)

Ocultar filas seleccionadas

Ctrl + 9

Ocultar columnas seleccionadas

Ctrl + 0

Eliminar Borrar a la derecha del cursor

Retroceso (Backspace)

Borrar a la izquierda del cursor

Suprimir (Delete)

Página 10


FORMATO DE CELDA formato’ se ’ se refiere a cambiar o modificar la ‘ forma La palabra ‘ formato forma’, pero no debemos suponer que es cambiar la forma rectángulo de la celda a círculo ¡NO! Más bien, se refiere a cambiar las propiedades que tiene cada celda.

Las Pestañas en Formato de Celdas Para acceder a la opción formato de celdas en Excel pulse el atajo de teclado: CTRL + 1 (el mismo en español e inglés), y verá que está dividido en seis (6) pestañas: Número, Alineación, Fuente, Borde, Relleno, y Proteger • • • • • •

Tres Principios de Diseños Aplicados a Formato de Celdas 1. Principio de Contraste (Colores (Colores)) Lo podemos resumir en que nunca los colores superiores y de fondo deben ser semejantes, sino contrarios. c ontrarios. Consejo: Si el color superior es oscuro, el color de fondo debe ser claro; y viceversa, si el superior es claro, el de fondo debe ser oscuro.

2. Principio de Legibilidad (Fuentes/Letras (Fuentes/Letras)) Como su nombre lo indica, el enfoque en este punto es que las letras, los datos, la informacion sea legible, facil de ver, entender, leer e interpretar. Consejo: Recuerde que no estamos diseñando una tarjeta para bodas o cumpleaños, por lo tanto, elija tipos de letras que no sean cursivas, sino más bien tipo cajón.

3. Principio de Prioridad (Distribución (Distribución)) Cuando presentamos nuestras facturas, reportes e informes, debemos aplicar formato a las celdas, que reflejen la prioridad de los distintos datos, de modo que los usuarios finales de estos informes no se desenfoquen, y que aun cualquiera que lo vea identifique lo relevante en nuestra información. Consejo: Divida el informe en Encabezado y Detalles (Títulos, contenido, detalles).

Página 11

Excel Avanzado +

Aplicando Los Principios con Formato a las Celdas Principio de Contraste 1. Antes de aplicar el formato a una celda, lo primero es seleccionarla. 2. En la pestaña Inicio (Home), en la sección Fuente, haga clic en Sombreado (Fill), y elija un contraste adecuado. Observe que cuando se coloca sobre el color le aparece el nombre de color junto con otros detalles: Azul, Enfasis 1,

Claro 40%.

Principio de Legibilidad 1. Antes de aplicar el formato a una celda, seleccione primero los títulos de los encabezados.

2. En la pestaña Inicio (Home), en la sección Fuente, haga clic en Tipo de letra (Font), y elija una letra que no sea cursiva (corrida), sino en estilo molde. En la imagen seleccionado:

Verdana.

Página 12

he

Antonio P. Peralta Ch. Principio de Prioridad 1. Antes de aplicar el formato a una celda, y practicar la prioridad, usted debe identificar, las informaciones y datos que deben tener más prioridad, y que debe ser enfatizado para que quien lea el informe preste más atención en dicha parte. 2. Observe la imagen y analicemos: Todos los datos con el mismo tamaño, forma y color. Nada se destaca ahora mismo. mismo . Algo bueno es que la alineación es coherente en todo el reporte, y esto ayuda. Apliquemos la prioridad : ENCABEZADO : Destacar el título de la compañía con un tipo de letra le tra diferente al resto del reporte, más tamaño, efecto de negrita (Bold), apliquemos al subtitulo un efecto de cursiva (Italic) y que hagamos el tamaño de la fecha menor.

CONTENIDO:

Resaltemos los títulos de cada dato, aplicándole un fondo con buen contraste y un efecto negrita que destaque un poco los títulos del color de fondo.

DETALLES : No debemos poner los datos a competir, por lo tanto, no hagamos muchos cambios a los detalles. Déjelo en un tipo de letra molde similar, pero diferente al título principal del reporte y aunque de forma opcional, no obligatorio, pudiera aplicarle un efecto tipo cursiva a la primera columna o en la fila final de los totales con igual fondo.

OTROS FORMATOS para CELDAS Excel nos facilita otras opciones automáticas , de gran ayuda para aplicar estos principios. A) En la pestaña INICIO (Home), sección Estilos, Estilos, haga clic en Estilos de celda: celda: B) En la pestaña INICIO (Home), sección Estilos, Estilos, haga clic en Dar formato como tabla: tabla :

Página 13

Excel Avanzado +

OPERACIONES CON CELDAS Para realizar operaciones de cálculos en Excel, necesitamos crear fórmulas, y estas se pueden clasificar en:

1) 2) 3) 4)

Directas Referencias Relativas Referencias Absolutas Mixtas

La Sintaxis de una Fórmula en Excel La sintaxis es el patrón o modelo a seguir para completar correctamente una indicación. Al estudiar español, por ejemplo, nos enseñan la sintaxis de la oración: SUJETO + VERBO + PREDICADO Si aprendes la sintaxis correctamente, y sigues esa guía exactamente, SIEMPRE podras crear fórmulas en Excel.

Así mismo, cuando vamos a crear una fórmula en Excel, también tenemos una sintaxis: 1) 2) 3) 4)

Símbolo (= o +); CANTIDADES (DIRECTAS/CELDAS); OPERADOR; y CANTIDADES (DIRECTAS/CELDAS)

Los 3 Componentes en la Sintaxis de una Fórmula

Fórmula Directa en Excel Son aquellas que utilizan las cantidades o valores numéricos directamente dentro de la formula, sin hacer referencia a celdas donde están los valores, sino a los mismos valores en sí.

Ejemplo: En la celda H7, en la columna etiquetada como Subtotal , vemos la formula directa que multiplica la cantidad por el precio.

= 2 * 700

Página 14


Fórmula de Referencia en Excel Son aquellas que en lugar lugar de usar las cantidades o valores numéricos directos dentro de la formula, utilizar referencias a celdas donde están los valores.

Ejemplo: En la celda H7, en la columna etiquetada como Subtotal , vemos la fórmula de referencia que multiplica la cantidad por el precio. Como queremos multiplicar la cantidad por el precio, y el valor de la primera cantidad está en la celda F3; y esta debe ser multiplicada por el precio, y el primero está en la celda G3, la formula f ormula debe ser: = F3 * G3

Referencias Relativas en Excel Son aquellas fórmulas, que hacen referencia a celdas que no son fijas , y que al copiarlas, Excel incrementa las filas automáticamente automáticamente de las celdas implicadas en la fórmula.

Ejemplo: Colóquese en la celda H7 para Copiar La fórmula de referencia a celda:

= F3 * G3 Usando el puntero de relleno ( + ) Al terminar de extenderlo extenderlo Excel automáticamente generará las fórmulas incrementando las filas; por lo cual = F3 * G3 serán en la próxima celda (la H8): = F4 * G4 y así sucesivamente.

Página 15

Excel Avanzado +

Referencias Absolutas en Excel Cuando se crean fórmulas, que hacen referencia a valores constantes en una celda , y queremos multiplicar una o más columnas de fórmulas por la celda constante, constante , debemos FIJAR esa celda en las fórmulas para que al copiarlas, Excel NO INCREMENTE las filas automáticamente de ESA CELDA fija implicada en la fórmula.

Fijar Celdas para Referencia Absoluta El simbolo de moneda $ es usado para indicar dentro de una formula que deseamos fijarla y que no se mueva. 1) Para fijar la columna: Coloque el signo de moneda $ al lado de la columna: $G4 2) Para fijar la fila: Coloque el signo de moneda $ al lado de la fila: G$4 3) Para fijar ambas: Coloque el signo de moneda $ al lado de la ambas: $G$4

3 Tipos de Referencia Absoluta 1) Celda Absoluta: = F4 * $G$4 2) Fila Absoluta: = F4 * G$4 3) Columna Absoluta = F4 * $G4 Fórmulas Mixtas en Excel Son las fórmulas, que combinan los diferentes tipos de fórmulas (directas, relativas, absolutas) según el cálculo a realizar.

Algunos Ejemplo de Fórmulas Mixtas en Excel: 1) = 3 * G4 (Directa combinada por Referencia Relativa ) 2) = F4 * $G$4 (Referencia Relativa por Referencia Absoluta ) 3) = F4 * $G$4 + 1 ( Referencia Relativa por Referencia Absoluta mas Directa )

Página 16


LISTAS PERSONALIZADAS Muchas veces trabajando con datos, nos vemos en la necesidad de crear listados con frecuencia fija (diario, semanal, quincenal, mensual, etc.). Algunos ejemplos de estos son: listas de productos, de estudiantes, de compras, de ventas, artículos, entre otras. En esta sección, trataremos una poderosa herramienta de Excel, que nos permite crear nuestros listados y reusarlos de forma exacta y fácilmente sin tener que volver a digitarlos.

Listas Personalizadas Predeterminadas Predeterminadas Excel incluye por defecto (por omisión) algunas listas predeterminadas, es decir, que no son creadas por usted, pero que le ayudan a ver lo útil que pueden ser las listas.

Ejemplo en pasos: 1) Escriba el primer mes del año ( Enero), 2) Pulse el puntero de relleno con el botón izquierdo del Mouse, y 3) Arrastre hasta que le aparezca Junio y suelte.

Creando Mis Propias Listas En nuestros trabajos, constantemente tenemos que crear listas de datos con la misma información. Una lista personalizada para estos casos me hace más productivo; ya que en lugar de digitarlo solo arrastro y de inmediato tengo mi listado, sin errores ortográficos y ahorrando tiempo.

1

Pasos:

1) Haga clic en Archivo (File), 2) Elija Opciones (Options), 3) Haga clic en Avanzadas (Advanced), Muévase hacia abajo (casi al final), y 4) Elija el botón Modificar Listas Personalizadas (Custom List)

2

3

4

Página 17

Excel Avanzado +

Creando Mis Propias Listas (continuación) 5) Elija Nueva Lista 6) Y en Entradas de lista : Digite el contenido de su lista exactamente como usted quiere que aparezca. 7) Pulse el botón Agregar 8) Finalmente Clic en Aceptar 9) Y luego Clic en Aceptar

5

7

6

8

Página 18


Otra forma de Crear Listas Personalizadas en Excel Invertimos un poco el orden.

1 2

Pasos: 1) Escriba la lista en Excel 2) Selecciónela completa 3) Clic en Archivo 4) Clic en Opciones 5) Clic en Avanzadas 6) Clic en Modificar listas personalizadas 7) Clic en Importar 8) Clic en Aceptar Listas personalizadas 9) Clic en Aceptar opciones Avanzadas 10) Pruébelas

7

10

8 Eliminando Mis Propias Listas 1) En la ventana de Listas Personalizadas, 2) Haga clic en la lista que desea eliminar 3) Pulse el botón Eliminar 4) Clic en Aceptar para Eliminar 5) Clic en Aceptar ventana Listas personalizadas 6) Clic en Aceptar opciones Avanzadas

Nota Importante de Listas Personalizadas Solo funcionan localmente, es decir, en la computadora donde se crearon. Por lo tanto, si quiere usarlas en otro computador tendrá que crearlas.

Página 19

Excel Avanzado +

PEGADO ESPECIAL Usado continuamente después del comando Copiar , el comando Pegar es uno de los más usados al trabajar de manera digital. En esta sección, trataremos sobre Pegado Especial en Excel. Se utiliza evidentemente, después de haber seleccionado algo y hacer clic en Copiar, pero en lugar del clásico Pegar, el Pegado especial tiene tiene muchas opciones extras fabulosas para ayudarnos en nuestro trabajo.

Encontrando El Comando Pegado Especial Dos formas fáciles para encontrarlo: Elija un dato, una formula o un texto que quiere copiar.

Primera 1) Clic en la pestaña Inicio, 2) En la sección Portapapeles, 3) Clic en el triángulo invertido debajo de Pegar, Pe gar, para que aparezcan las opciones alternativas de Pegar, y 4) Elija la opción Pegado especial…

Segunda 1) Elija la celda a donde quiere pegar lo copiado, 2) Pulse el botón derecho del Mouse, y 3) Elija la opción Pegado especial…

Cuando elija Pegado especial… le aparecerá la siguiente ventana:

Página 20


Las Opciones de Pegado Especial Veamos que sucede dependiendo de la opción que elija en la ventana Pegado especial : La ventana Pegado especial se se divide en cuatro (4) áreas:

1) 2) 3) 4)

Pegar, Operación, Cajas de Chequeo, y Pegar vínculos

El área Pegar de Pegado especial •

Todo: Como el nombre lo indica, pega todo, tal y como lo hace el comando clásico Pegar .

• •

• • • •

Fórmula: Solo pega las fórmulas. Valores: Inverso al anterior, solo trae los valores, pero no las fórmulas que generan esos datos. Formato: Solo pega el formato y no los datos, similar al comando Copiar formato. Comentario: Pega solo el comentario que tenía t enía asignado la celda original. Validación: Pega solo las reglas de validación para que sean aplicadas también aquí. Las demás opciones del área Pegar: Se explican fácilmente con tan solo leerlas.

El área Operación de Pegado especial • • • •

•

cálculo . Ningún: Esta es la opción por defecto, para que no haga ningún cálculo. Suma: Cuando los valores que pegas quieres que se sumen a sumen a los que la celda tiene. resten a los que la celda tiene. Resta: Cuando los valores que pegas quieres que se resten a multipliquen a los que la Multiplica: Cuando los valores que pegas quieres que se multipliquen a celda tiene. dividan entre los que la celda Dividir: Cuando los valores que pegas quieres que se dividan entre tiene.

El área Cajas de Chequeo en Pegado especial • •

Salta blanco: Marque esta caja para no copiar las celdas originales que esta vacías. Trasponer: Marque esta caja para cambiar la orientación de las celdas pegadas (si las originales estaban verticales –columnas-, que las pegadas las ponga horizontales –en filas-, y viceversa).

El botón Pegar vínculo en Pegado especial •

Esta opción es de tremenda utilidad y automatización; ya que crea un vínculo entre las celdas originales (copiar) con las de destino (a donde pegar); de manera que al ocurrir cualquier cambio en las originales se actualiza automáticamente en las celdas de destino donde se pegaron los datos.

Página 21

Excel Avanzado +

FORMATO CONDICIONAL   

¿Qué son los Patrones y Tendencias? Tipos de Patrones y Tendencias Usando El Formato Condicional en Excel Opciones Automáticas o Reglas o

Descubra lo que está pasando a su alrededor… Ya que todas las personas e instituciones producen o generan datos, y estos datos en el transcurso del tiempo, reflejan situaciones (comportamientos, posturas, indicadores) que pueden afectar nuestras vidas (proyectos, negocios, empleos e instituciones) de forma positiva o negativa, es bueno contar con herramientas (y en Microsoft Excel Ex cel las tenemos) que nos permitan descubrir esos patrones y tendencias a nuestro alrededor.

Al finalizar esta sección usted aprenderá: Explicar qué son los patrones y tendencias; Reconocer los diferentes tipos de patrones y tendencias; Realizar los pasos para aplicar formato condicional a sus datos; e Identificar patrones y tendencias en sus datos. • • • •

Página 22


Patrones y Tendencias ¿Qué son los Patrones y Tendencias? Según algunos estudiosos del área de estadísticas y pronósticos para negocios, los patrones son clasificados como una serie en el tiempo . En palabras más llanas, lo que esto quiere decir, es cómo se comporta (cuales son los valores que adquiere) un producto, un servicio, al pasar el tiempo (lo cual debe ser registrado por periodos específicos: años, meses, semanas, quincenas, etc.) y así verificar dicha variación. Veamos los diferentes tipos de patrones de serie en el tiempo.

Tipos de Patrones y Tendencias Patrón estacionario (tipo horizontal) Cuando los valores se mantienen en un nivel constante o medio .

Patrón de tendencia Cuando el periodo de tiempo es muy largo y los valores muestran un crecimiento o decrecimiento consistente, tenemos un patrón llamado tendencia .

Patrón cíclico El patrón cíclico, es aquel que tiene un patrón de tendencia fijo de crecimiento o decrecimiento durante un ciclo de tiempo (de por lo menos tres años o más).

Patrón estacional Este tipo de patrón, se repite durante una estación del año. Algunos ejemplos: como los reyes, la navidad, el black Friday, las madres, san valentín, entre otros.

Variación irregular Estos son cambios en las series de corto plazo, que se pueden presentar en cualquiera de los patrones anteriores.

Página 23

Excel Avanzado +

Usando el Formato Condicional en Excel Opciones Automáticas en Formato Condicional: BARRA DE DATOS 1. Seleccione los datos a los cuales aplicará el formato condicional;

2. Asegúrese de estar en la pestaña: INICIO; 3. Diríjase a la sección: ESTILOS;

4. Haga clic en: Formato Condicional; 5. Elija: Barra de datos;

Elegiremos una de las 6 opciones de Relleno degradado en lugar de Relleno sólido sólido 6. Haga clic el degradado azul:

7. Vea el resultado:

Página 24


Usando el Formato Condicional en Excel Opciones Automáticas en Formato Condicional: ESCALAS DE DE COLOR COLOR 1. Seleccione los datos a los cuales aplicará el formato condicional;


4. Haga clic en: Formato Condicional; 5. Elija: Escalas de color;

Elija una de las 12 combinaciones de color:


Página 25

Excel Avanzado +

Usando el Formato Condicional en Excel Opciones Automáticas en Formato Condicional: …) CONJUNTO DE ICONOS ( 1. Seleccione los datos a los cuales aplicará el formato condicional;


4. Haga clic en: Formato Condicional; 5. Elija: Conjunto de iconos;

Elija entre Direccional, Formas, Indicadores o Valoración:


Página 26


Usando el Formato Condicional en Excel Creando Las Reglas para Formato Condicional: NUEVA REGLA 1. Seleccione los datos a los cuales aplicará el formato condicional;


4. Haga clic en: Formato Condicional; 5. Elija: Nueva regla: Seleccione un tipo de regla: ( Aplicar formato a todas las celdas según sus valores ) y Un estilo de formato:

Página 27

Excel Avanzado + 6. Dependiendo del estilo de formato que usted elija, le aparecerán una, dos o tres opciones (Mínima, Punto medio y/o Máxima) para que se calcule el formato condicional, y en cada una de estas opciones debe elegir una de las siguientes: Valor más bajo, porcentual, número, fórmula o percentil. Mire este ejemplo basado en Escala de 3

colores: 7. Para cada opcion, he elegido número: 8. Y he especificado el valor para cada opción, opción, tomando en cuenta que son calificaciones escolares, donde hay un mínimo para pasar, una media para ser estudiante de honor, y la nota máxima para los estudiantes de más alto honor. Mínima: 70, Punto medio: 85, colores para Máxima: 100. Los colores cada caso, Excel los asigna automáticamente, pero usted los puede cambiar y elegir según le parezca mejor. Solo tiene que hacer clic en cada uno de estos renglones de cada color. 9. Veamos los resultados:

Página 28


FIJAR PANELES Los datos crecen constantemente (sea que estemos registrando compras, ventas, movimientos de cuentas, entre otros casos), y este crecimiento incrementa el número de filas por cada registro; por lo cual, en casos como este, cuando nos movemos hacia abajo (scroll) no vemos los títulos que identifican cada dato ni los datos que están más arriba. En esta sección, trataremos sobre Fijar Paneles en Paneles en Excel. La cual es la solución ideal a la situación que he descrito en el párrafo anterior. Veamos de que se trata y como la podemos usar de forma eficaz.

Pasos para encontrando Fijar Paneles en Excel 1. Clic en la pestaña Vista 2. En la sección Ventana, haga Clic en Inmovilizar 3. Y podrá ver las tres (3) opciones de Inmovilizar

a. Inmovilizar paneles b. Inmovilizar fila superior c. Inmovilizar primera columna

Condiciones para Aplicar Correctamente Fijar Paneles 1. Decida que quiere hacer a. Fijar títulos para ver los nombres de los datos cuando baje en mi listado b. Fijar datos a partir de una fila especifica c. Fijar la fila superior d. Fijar la primera columna 2. Dependiendo de lo que ha elegido en el paso 1 de esta sección, entonces debe colocarse en una fila o celda especifica.

Página 29

Excel Avanzado +

Ejemplo: Caso 1a Fijar títulos para ver los nombres de los datos cuando baje en mi listado 1. Colóquese debajo del primer título (en mi ejemplo es la celda A5) 2. Clic en la pestaña Vista 3. En la sección Ventana, haga Clic en Inmovilizar 4. Clic en Inmovilizar paneles

Ahora automáticamente se ha agregado una línea, parecida a un subrayado, que indica la inmovilización.

Bajemos (scroll) y veamos que sucede ahora:

Observe que los títulos están en la fila 4 y he descendido hasta el final de los datos (fila 42), y puedo ver los títulos, a pesar de bajar hasta el final.

Para Movilizar los Paneles Si quiero volver a poner la hoja como estaba, es decir, que los paneles no estaban fijados, debo hacer lo siguiente: 1. 2. 3. 4.

Clic en la pestaña Vista En la sección Ventana, haga Clic en Inmovilizar Y ahora elijo la opción: Movilizar paneles Ya los paneles no están fijos.

NOTA: Deshacer (CTRL+Z) no funciona para esto. Página 30


Avanzado Módulo 2. ANÁLISIS DE HIPÓTESIS    

Creación de escenarios Búsqueda de objetivos Tabla de datos Manejando Prestamos Mensuales y Anuales

Página 31

Excel Avanzado +

ANALISIS DE HIPOTESIS (What If Analysis) Esta expresión (Análisis de Hipótesis) corresponde, al equivalente en inglés en Excel: What if Analysis…? ¿Qué sucedería, que pasaría si…? Esta Esta eficaz herramienta nos permite plantear hipótesis de tres (3) formas, y ver los resultados de estas.

¿Qué es Análisis de Hipótesis? Es el proceso de cambiar valores en una o más celdas para ver cómo estos cambios afectaran los resultados en la hoja de cálculo.

Tres Herramientas para Hipótesis 1. Administrador de escenarios…

2. Buscar objetivo….

3. Tabla de datos

1

Página 32


Como Acceder a Análisis de Hipótesis (What If Analysis) Pasos: 6. Haga clic en la pestaña pe staña Datos

7. En la sección Herramientas de datos , elija la opción Análisis de hipótesis

8. Aparecen tres (3) herramientas para analizar hipótesis: h ipótesis: a. Administrador de esc enarios…(Scenario Manager ) b. Buscar objetivo…(Goal Seek ) c. Tabla de datos…(Data Table) Table) 9. Haga clic en la opción que desea usar.

Página 33

Excel Avanzado +

Administrador de Escenarios ( Scenario Manager ) Un escenario (en este contexto), es el conjunto de datos que muestra el estado de un área o departamento o proyección de una institución en un periodo de tiempo especificado.

Ejemplo: La compañía Nosotros, SRL. Presenta su plan estratégico a varios años (2018, 2019, 2020), tomando como base el año actual (2017), ellos han establecido la siguiente proyección.

Hipótesis: Reducir por cada año el costo de inventario, de tal forma, que a medida que los años avancen el costo de inventario sea menor.

¿Cómo funciona la herramienta Administrador de Escenarios? El planteamiento en el pasado ejemplo, es que de esa proyección, proyección , ellos han decidido variar el inventario (en los tres años), van a crear dos escenarios: el peor caso y el mejor caso, y finalmente ver como esto afecta los resultados. La base: Son los datos anteriores La hipótesis en Inventario: Peor Caso: 2018 -> 1,850,000; 2019 -> 1,750,000; 2020 -> 1,650,000 Mejor Caso: 2018 -> 1,500,000; 2019 -> 1,300,000; 2020 -> 1,200,000

Pasos: Clic en Datos Clic en Análisis de hipótesis hipótesis Clic en Administrador de de escenarios Aparecerá la ventana de administrador de escenarios 5. Clic en el botón Agregar… para crear el primer escenario: a. Nombre del escenario: Escriba InventarioAlto b. Celdas cambiantes: $D$7:$D$9 1. 2. 3. 4.

c. Quite el check a Evitar cambio d. Haga Clic en Aceptar 6. Aparecerá la ventana para que digite el inventario alto para cada año.

Página 34

Antonio P. Peralta Ch. 7. Los valores actuales son 2018 -> 1,700,000; 2019 -> 1,500,000; 2020 -> 1,400,000 8. Como ya sabemos, InventarioAlto, sería el peor caso, porque es más costoso: Digite y sustituya En $D$7: 1850000 y En $D$8: 1750000 y En $D$9: 1650000 9. Y luego haga clic en Aceptar 10. Aparecerá: 11. A continuación pulse el botón Agregar para para crear el otro escenario, el de InventarioBajo.

12. Haga Clic en Aceptar en Aceptar y y digite los valores para el nuevo escenario InventarioBajo como ves en la imagen de la derecha:

13. Ahora se debe ver así:

Página 35

Excel Avanzado + 14. Ahora haga clic en el botón Resumen 15. Le aparecerá la ventana sobre Resumen del escenario, con la primera opción seleccionada por defecto Resume) y mostrando en que celdas presentara los (Resume) resultados. HAGA CLIC en el botón Aceptar 16. Excel automáticamente agregará una hoja nueva con el nombre: Resumen del escenario y le mostrara en esta hoja, bajo dos grandes grupos (Celdas cambiantes y celdas de resultados) lo siguiente: a. Valores actuales; b. Los escenarios creados por usted (InventarioAlto e InventarioBajo) sombreados con fondo gris claro; y c. Los resultados de cada caso.

Página 36


Buscar Objetivo (Goal Seek ) La opción Buscar objetivo ( Goal Seek ) se utiliza para obtener el resultado, de uno de los varios parámetros dentro de una fórmula, utilizando 2 celdas y un valor. Buscar objetivo solo trabaja con una variable a la vez.

Elementos de Buscar Objetivo Definir la celda: Escriba la celda fija, la celda •

•

•

que tiene la fórmula; Con el valor: Una cantidad, un número que es mi objetivo a pagar. Debe colocarlo en negativo; ya que es un pago (un deducible). Celda cambiante: Es la celda que será alterada, modificada automáticamente por Excel para buscar el objetivo (que podría ser el Interés o el Plazo en e n caso de préstamos).

Ejemplo: Imagínese que una persona desea hacer un préstamo personal para comprar una computadora de última generación. El monto de dicho préstamo es de sesenta mil pesos (RD$60,000.00), en un plazo de dos (2) años, es decir, veinticuatro meses (24), con una tasa de interés de un diez por ciento (10%), y su cuota a pagar es de RD$2,768.70, pero resulta que este cliente solo puede pagar mensual $1,400.00. ¿Cómo podemos lograr ese objetivo?

Hipótesis: Como variaran los pagos y/o intereses para lograr e l objetivo: Pagar RD$1,400.00 pesos mensuales.

Pasos para aplicar Buscar Objetivo (Goal (Goal Seek ): 1. En Excel digite los siguientes datos:

2. Haga clic en la pestaña pe staña Datos

Página 37

Excel Avanzado + 3. En la sección Herramientas de datos , elija la opción Análisis de hipótesis

4. Aparecen tres (3) herramientas para analizar hipótesis: a. Administrador de escenarios…(Scenario Manager ) b. Buscar objetivo…(Goal Seek ) c. Tabla de datos…(Data Table) Table) 5. Haga clic en Buscar objetivo... objetivo... 6. Aparece el siguiente cuadro de dialogo:

7. Elija ahora las celdas correspondientes a cada caso: •

•

•

Definir la celda: Escriba la celda fija, la celda que tiene la fórmula =PAGO(B13/12,B14,B12); es B16. Con el valor: Una cantidad, un número que es mi objetivo a pagar. Debe colocarlo en negativo; ya que es un pago (un deducible); es -1400 Celda cambiante: Es la celda que será alterada, modificada automáticamente por Excel para buscar el objetivo (que en este caso es los plazos a pagar); es B14.

8. Haga clic en Aceptar y veamos los resultados:

9. La herramienta ha encontrado una solución para el valor objetivo: -1400, y nos muestra a la izquierda la alteración en la celda B14 (plazos, tiempo en meses), la cual fue la celda elegida como celda cambiante, y el resultado fue: 53.24 meses (unos cuatro años con unos cuatros meses ( meses (4.41)). Manteniendo el mismo monto, el mismo interés, y el objetivo de pagar mensual 1,400 pesos, nos dice la solución: Aumentar el tiempo tiempo de pago pago (de 24 meses a 53.24). 10. En otros casos, en lugar de poner como celda cambiante, la celda de plazos (tiempo), usamos la celda interés.

NOTA: En la práctica adjunta (archivo de Excel) a este material están explicadas las formulas y sus detalles. Página 38


Tabla de Datos (Data Table) Como su nombre lo indica, con esta opción, usted puede generar (con la ayuda de Excel y siguiente el procedimiento de esta herramienta) una tabla completa con todos los resultados para una o dos variables al mismo tiempo. Sin duda alguna, entre Buscar objetivo y Tabla de Datos, mi favorito para cálculos de préstamos mensuales, anuales, detalles de plazos e intereses es Tabla de Datos ( Data Data Table ). Recuerde: Con esta opción puede trabajar con dos variables a la vez.

Cómo Funciona la Opción Tabla de Datos CASO 1 VARIABLE EN COLUMNA 1. Crear los datos para la fórmula, estos son: Capital; Interés; Tiempo (en meses); y Pago mensual, calculado con la función: en español PAGO(), y en ingles PMT()

2. Escribir la lista del dato que va a variar (una variable): En este ejemplo es una lista de intereses que va desde el 4% hasta el 12% de interés mensual.

3. Debe hacer referencia a la celda de la formula pago mensual (celda K13): En este ejemplo, colóquese en la celda N9, y escriba =K13 y pulse ENTER. 4. Finalmente, seleccionemos (sombrear) el rango de celdas , desde la celda M9 (que está en blanco) hasta la celda N18.

5. Haga clic en la pestaña Datos

Página 39

Excel Avanzado + 6. En la sección Herramientas de datos , elija la opción Análisis de hipótesis

7. Aparecen tres (3) herramientas para analizar hipótesis: a. Administrador de escenarios…(Scenario Manager ) b. Buscar objetivo…(Goal Seek ) Table) c. Tabla de datos…(Data Table) 8. Haga clic en Tabla de datos... datos...

9. Aparece el siguiente cuadro de dialogo:


Celda de entrada (fila fila): ): Es cuando el listado de datos esta en fila (horizontal), y escribes o elijes la celda de la fórmula que corresponde a esos valores. Ejemplo: Tiempo (Meses) -> Celda K11.

Celda de entrada (columna ( columna): ): Es cuando el listado de datos esta en columna ( vertical), y escribes o elijes la celda de la fórmula que corresponde a esos valores. Ejemplo: Interés (Celda K10). Observe en el ejemplo de la imagen, los valores de Interés desde el 4% hasta el 12% , están en forma de columna; por lo cual en celda de entrada, entrada , elegimos la que dice (columna), y como la celda donde automáticamente Excel sustituirá esos valores para calcular automáticamente el pago mensual según dicho Interés es la K10 (para este ejemplo). 11. Haga clic en Aceptar y veamos los resultados:

Página 40


12. Aparece la tabla de datos de pagos mensuales automáticamente según la variación de cada interés (en la tabla de intereses del 4% al 12%), 12%), para 120,000 pesos, con un tiempo (en meses) de 240 (que equivale en años a 20).

Página 41

Excel Avanzado +

Cómo Funciona la Opción Tabla de Datos CASO 1 VARIABLE EN FILA 1. Crear los datos para la fórmula, estos son: Capital; Interés; Tiempo (en meses); y Pago mensual, calculado con la función: en español PAGO(), y en ingles PMT() 2. Escribir la lista del dato que va a variar (una variable): En este ejemplo es una lista de tiempo (plazos en meses) que va desde 180 meses hasta 420. Si quiere saber el equivalente en años, solo tiene que dividir la cantidad de meses entre 12. Ejemplo: 180/12 = 15 años.

NOTA: Para poder obtener el PAGO mensual como resultado, en lugar del pago anual, todos los datos deben ser dados a la función (la función que en español es PAGO, y en inglés es PMT) en la formula formula en meses. meses. Por eso, eso, el interés que hemos hemos colocado colocado lo dividimos en la formula entre 12, para que el resultado sea el mensual. 3. Debe hacer referencia a la celda de la formula pago mensual (celda K30 K30)): En este ejemplo, colóquese en la celda M23, y escriba=K30 y pulse ENTER.

4. Finalmente, seleccionemos (sombrear) el rango de celdas , desde la celda M22 (que está en blanco) hasta la celda R23. 5. Haga clic en la pestaña Datos


Página 42

Antonio P. Peralta Ch. 7. Aparecen tres (3) herramientas para analizar hipótesis: a. Administrador de escenarios…(Scenario Manager ) b. Buscar objetivo…(Goal Seek ) c. Tabla de datos…(Data Table) Table) 8. Haga clic en Tabla de datos... datos... 9. Aparece el siguiente cuadro de dialogo:


Celda de entrada (fila (fila): ): Es cuando el listado de datos esta en fila (horizontal), y escribes o elijes la celda de la fórmula que corresponde a esos valores. Ejemplo : Tiempo (Meses) -> Celda K28. Celda de entrada (columna ( columna): ): Es cuando el listado de datos esta en columna ( vertical), y escribes o elijes la celda de la fórmula que corresponde a esos valores. Ejemplo: Interés (Celda K10). Observe en el ejemplo de la imagen, los valores de Tiempo (Meses) desde el 180 hasta el 420 , están en forma de fila; por lo cual en celda de entrada, entrada, elegimos la que dice (fila), y como la celda donde automáticamente Excel sustituirá esos valores para calcular automáticamente el pago mensual según dicho Tiempo es la K28 (para este ejemplo).

11. Haga clic en Aceptar y veamos los resultados: 12. Aparece la tabla de datos de pagos mensuales automáticamente mensuales automáticamente según la variación de cada plazo, plazo , para 120,000 pesos a un interés de 16.00%

Página 43

Excel Avanzado +

Cómo Funciona la Opción Tabla de Datos CASO de 2 VARIABLES UNA en FILA y OTRA en COLUMNA 1. Crear los datos para la fórmula, estos son: Capital; Interés; Tiempo (en meses); y Pago mensual, calculado con la función: en español PAGO(), y en ingles PMT()

2. Escribir las dos listas de datos que van a variar (las dos variables):

La Lista Horizontal (FILA) En este ejemplo es una lista de tiempo (plazos en meses) que va desde 180 meses hasta 420. Si quiere saber el equivalente en años, solo tiene que dividir la cantidad de meses entre 12. Ejemplo: 180/12 = 15 años.

NOTA: Para poder obtener el PAGO mensual como resultado, en lugar del pago anual, todos los datos deben ser dados a la función (la función que en español es PAGO, y en inglés es PMT) en la formula formula en meses. Por eso, el interés que que hemos colocado lo dividimos dividimos en la formula entre 12, para para que el resultado resultado sea el mensual. mensual. La Lista Vertical (COLUMNA) En este ejemplo es una lista de intereses que va desde el 4% hasta el 20% de interés mensual.

Página 44

Antonio P. Peralta Ch. 3. Debe hacer referencia a la celda de la formula pago mensual (celda K40 K40)): En este ejemplo, colóquese en la celda M36, y escriba=K40 y pulse ENTER.

4. Finalmente, seleccionemos seleccionemos (sombrear) el rango de celdas , desde la celda M36 (que está en blanco) hasta la celda R53.

5. Haga clic en la pestaña Datos


Página 45

Excel Avanzado + 7. Aparecen tres (3) herramientas para analizar hipótesis: a. Administrador de escenarios…(Scenario Manager ) b. Buscar objetivo…(Goal Seek ) Table) c. Tabla de datos…(Data Table) 8. Haga clic en Tabla de datos... datos...

9. Aparece el siguiente cuadro de dialogo:


Celda de entrada (fila (fila): ): Listado horizontal. Ejemplo: Ejemplo: Tiempo (Meses) -> Celda K38.

Celda de entrada (columna columna): ): Es cuando el listado de datos esta en columna (vertical), y escribes o elijes la celda c elda de la fórmula que corresponde a esos valores. Ejemplo: Interés (Celda K37). 11. Haga clic en Aceptar y veamos los resultados: 12. Aparece la tabla de datos completa de completa de pagos mensuales automáticamente según la variación de cada plazo y de interés, para comprar una casa con RD$2,500,000 pesos

Página 46


AVANZADO Módulo 3. DOMINANDO LAS FUNCIONES      

Lógicas: SI (If) , Y (And), O (Or), NO(Not) Condicionadas: Suma.SI(SumIf), Promedio.SI(AverageIf), Contar.Si(CountIf) Estadísticas: Max, Min, Moda, Mediana (Median) Texto y referencia : Mayusc(Upper), Minusc(Lower), NomPropio(Proper), Concatenar(Concatenate), & Búsqueda: Vlookup Fecha y Hora: Hoy(Today), Series Cronologicas, Series Dias Lab, Dias.Lab(NetworkDay), Texto()

Página 47

Excel Avanzado +

FUNCIONES LOGICAS: SI (español) IF (ingles) Antes de abordar las funciones logicas, quiero explicarle que es una funcion y la sintaxis general de estas.

Concepto de Función Una funcion es una palabra definida (reservada) por Excel, que recibe unos parametros o argumentos (que son los datos), y que internamente posee una estructura para devolver un resultado basado en el cumplimiento de la sintaxis y de los datos proporcionados.

Sintaxis General de una Función Recordemos que la sintaxis es el patron o modelo, que nos presenta el orden correcto en que se deben colocar los elementos, e lementos, para obtener el resultado planteado. Ejemplo: Sintaxis Basico de Oracion en Lenguaje = Sujeto + Verbo + Predicado Tambien en Excel, las funciones tienen una sintaxis. En la mayoria de los casos, la sintaxis tiene los siguientes elementos en este orden:

1. Simbolo de formula a. Signo de igualdad (=); o b. Signo de Mas (+)

2. Nombre de la Funcion 3. Parentesis abierto a. (

4. Valores a evaluar a. Puede ser una celda (A8); b. Un rango de celda, se define como todas las celdas que hay entre la primera y la ultima celda incluyendolas a ambas, ejemplo: (A4:A20) c. Condiciones, para otras funciones

5. Parentesis Cerrado a. ) Por lo tanto, guiandonos de la sintaxis, podemos dominar el uso de las funciones en Excel. =SUMA(F4:F20)

Tipos de Funciones en Excel Como siempre comento: ¡Excel es tan amplio! Y tiene tantas funciones, que nos tomaria un curso para cada grupo, si nos dedicamos a desglosar cada funcion. Por ejemplo, tenemos el curso Excel Financiero que trabaja funciones financieras y sus aplicaciones al mercado financiero, entre otros relacionados.

Excel agrupa las funciones por sus tipos: Financieras, Logicas, Texto, Fecha y hora, Busqueda y referencia, Matematicas y Trigonometricas, Entre muchas otras • • • • • • •

Página 48


FUNCIONES LOGICAS: SI (español) IF (ingles) Evalua o comprueba si se cumple una condicion y dependiendo del resultado verdadero o falso devuelve un valor, de acuerdo a lo que usted le haya establecido.

Sintaxis Función SI (IF) Simbolo NombreFuncion ( PruebaLogica PruebaLogica , Verdadero, Falso ) Los 9 elementos de la Sintaxis en IF / SI Condicional 1. 2. 3. 4. 5. 6. 7. 8. 9.

Símbolo -> = o + Nombre de la Función en español (SI) en inglés (IF) Paréntesis abierto -> ( Prueba Lógica -> Condición Coma -> , Qué hacer cuando sea VERDADERO Coma -> , Qué hacer cuando sea FALSO Paréntesis cerrado -> )

Ejemplo SI / IF =SI(H21>=90,"Excelente",SI(H21>=80,"Muy Bien",SI(H21>=70,"Bien",SI(H21>=60,"Animate",SI(H21<60,"Fajate a Estudiar MUCHOOOO","Y que le paso a Usted?")))))

=SI(H21>=90,"Excelente", SI(H21>=80,"Muy Bien", SI(H21>=70,"Bien", SI(H21>=60,"Animate", SI(H21<60,"Fajate a Estudiar MUCHOOOO ", "Y que le paso a Usted?" )))))

Página 49

Excel Avanzado +

FUNCIONES LOGICAS: Y (AND) / O (OR) / No (NOT) Para comprender mejor esta seccion, recordemos la tabla de verdad que vimos en logica matematica, cuando cursamos el bachillerato (high school).

Tabla de Verdad y La Lógica Aprendimos sobre negacion, conjuncion, disyuncion, implicacion, la doble implicacion, etc.

Simbolo

Significado

Ejemplo

Se Interpreta

~

NO (not)

~P

No P o Negacion de P

^

Y (and)

P^Q

PyQ

v

O (or)

PvQ

PoQ

P

Q

~P

P^Q

PvQ

V

V

F

V

F

V

F

F

F

V

F

V

V

F

V

F

F

V

F

F

Excel y Los Operadores Lógicos: NO / Y / O Combinando la función SI (IF) con los operadores lógicos. La sintaxis de NO . =SI(NO(Algo es verdadero), Valor si es verdadero, Valor si es falso) =SI(Y(B11>=$B$6, C11>=$B$4),B11*$B$7,0) La sintaxis de O . =SI(O(Algo es verdadero, Algo diferente es verdadero), Valor si es verdadero, Valor si es falso)

=SI(O(B10>=$B$6, C10>=$B$4),B10*$B$5,0) La sintaxis de Y . =SI(Y(Algo es verdadero, Algo diferente es verdadero), Valor si es verdadero, Valor si es falso) fal so) =SI(Y(B11>=$B$6, C11>=$B$4),B11*$B$7,0)

Página 50


FUNCIONES CONDICIONADAS SUMAR.SI (SUMIF) / PROMEDIO.SI (AVERAGEIF) /CONTAR.SI (COUNTIF) Este grupo de funciones tienen en comun que todas usan un criterio o condicion que debe cumplirse para realizarse la accion propia de cada funcion. f uncion.

SUMAR.SI (SUMIF) Calcula el total de todas las celdas que cumplen cu mplen con la condicion especificada en criterio.

Sintaxis Función SUMAR.SI (SUMIF) = SUMAR.SI ( Rango-donde-Buscar Rango-donde-Buscar , Criterio, Rango-A-Totalizar ) Rango-donde-Buscar Celdas donde Excel buscara el criterio o condición especificada. Ejemplo: A2:A10

Criterio Condición a evaluar dentro del rango especificado anteriormente. Ejemplo: F1

Rango-A-Totalizar Celdas donde están digitados los valores, y de donde Excel solo sumará (automáticamente) los que cumplan con el criterio especificado. Ejemplo: C2:C10

Ejemplo1: Total Vendido en el Trimestre por Vendedores =SUMAR.SI(A2:A10,F1,C2:C10) En la celda F1, está el nombre de uno de los vendedores (Jaime Casado). La función Sumar.SI buscara en el rango de celdas (A2:A10) todas las veces que aparece ese nombre y tomara del rango de celdas (C2:C10) SOLO los valores que pertenezcan a ese vendedor y los totalizara.

Ejemplo2: Total Vendido en cada Mes =SUMAR.SI(B2:A10,F4,C2:C10) En la celda F4, está el nombre del mes (Enero). La función Sumar.SI buscara en el rango de celdas (B2:B10) todas las veces que aparece ese mes y tomara del rango de celdas (C2:C10) SOLO los valores que pertenezcan a ese mes y los totalizara.

Página 51

Excel Avanzado +

Un Sistema de Consultas Automático con SUMAR.SI() AUTOMATIZANDO: Para lograr automatizar mejor la búsqueda de criterios, creemos una LISTA desplegable, que tenga todos los criterios posibles a buscar; de tal forma que no tengamos que escribirlo ni que cometamos errores al escribirlo (ya que el criterio especificado debe ser escrito EXACTAMENTE).

Ejemplo3: Total Vendido en el Trimestre por Vendedores en LISTA Este exactamente el mismo ejemplo1, solo con dos diferencias: 1. La celda del criterio está en H2; y 2. Que los nombres de los vendedores no tenemos que digitarlos, sino elegirlos de la lista desplegable ; por lo tanto, previamente debemos crear esa lista basada en todos los vendedores posibles que existen en su base de datos de ventas trimestrales.

PASOS Ejemplo3: Total Vendido en el Trimestre por Vendedores en LISTA Colóquese en la celda H3 y escriba esta fórmula: =SUMAR.SI(A3:A11,H2,C3:C11)

Página 52


1. Colóquese en un área de la hoja actual o en una hoja nueva celda;

NOTA: Para el ejemplo rápido, estoy usando el rango de celdas (S3:S5). Aunque debe tener presente, la recomendación que he hecho antes, tenga una hoja de configuración o de constantes dentro de su libro de trabajo de Excel, y referencia su listado de vendedores a las celdas asignadas en dicha hoja.

2. Digite o copie y pegue el listado de vendedores; 3. Ahora colóquese en la celda H2 de la hoja donde realizara la consulta; 4. Haga clic en Datos y elija Validación de datos

5. En la pestaña Configuración, haga clic en el área Permitir: para elegir el tipo de datos Lista; 6. En la sección Origen, haga clic en para seleccionar el rango de celdas o la hoja donde hoja donde tiene la lista de los vendedores (en este ejemplo es el rango de celdas S2:S5); 7. Haga clic en el cuadrado al lado de Omitir blancos, para que no permita dejarlo en blanco la celda; 8. Haga clic en Aceptar; 9. Al colocarse en la celda H2 al lado derecho aparecerá este símbolo que indica que hay una lista desplegable; 10.Haga 10.Haga clic en ese símbolo y elija el nombre de un vendedor. 11.Ahora 11.Ahora en H3 aparece el total del vendedor que usted eligió.

Página 53

Excel Avanzado +

PROMEDIO.SI (AVERAGEIF) Calcula el promedio de todas las celdas que cumplen con la condicion especificada en criterio.

Sintaxis Función PROMEDIO.SI (AVERAGEIF) = PROMEDIO.SI ( Rango-donde-Buscar Rango-donde-Buscar , Criterio, Rango-A-Promediar ) Rango donde Buscar Celdas donde Excel buscara el criterio o condición especificada. Ejemplo: B3:B9

Criterio Condición a evaluar dentro del rango especificado anteriormente. Colóquela entre doble comillas. Ejemplo: “F”

Rango-A-Promediar Celdas donde están digitados los valores, y de donde Excel solo promediará (automáticamente) las que cumplan con el criterio especificado. Ejemplo: A3:A9

Ejemplo1: Promedio de Edad de Mujeres que Más Consumen … =PROMEDIO.SI(B3:B9,“F”,A3:A9) La función Promedio.Si buscará en el rango de celdas (B3:B9) todas las veces que aparece el criterio especificado, que en este caso es “F”, refiriéndose a sexo femenino (F); y tomará del rango de celdas (A3:A9) SOLO los valores que pertenezcan a ese sexo (F) y los promediará.

Ejemplo2: Promedio de Ingresos Mayores que 60,000 =PROMEDIO.SI(G3:G10, “>60000”) La función Promedio.SI buscara en el rango de celdas (G3:G10) los ingresos que cumplan con la condición (“>60000”); y tomará

SOLO esos y los promediará.

Página 54


Ejemplo3: Promedio de Ingresos Basado en Egresos menores que 40,000 PROMEDIO.SI(H3:H10,“<40000”, G3:G10)

La función Promedio.SI buscara en la columna Egresos, que es el rango de celdas (H3:H10) todos los que cumplan con la condición menores que 40,000 (“<40000”), y tomará los ingresos de esos egresos según el rango de celdas (G3:G10) y SOLO esos serán promediados. promediados.

Página 55

Excel Avanzado +

CONTAR.SI (COUNTIF) Cuenta las celdas del rango dado que cumplen con la condicion especificada en el criterio. c riterio.

Sintaxis Función CONTAR.SI (COUNTIF) = CONTAR.SI ( Rango-donde-Buscar Rango-donde-Buscar , Criterio ) Ejemplo1: Cuantas Veces Aparece el Vendedor: Winston Perez Rango donde Buscar Celdas donde Excel buscara el criterio o condición especificada para CONTAR el número de celdas que no están en blanco. Ejemplo: A3:A18

Criterio Condición a evaluar en forma de número, número, texto o texto o expresión dentro del rango especificado anteriormente. Ejemplo: “Winston Perez”

Resultado en este caso:

Solo dos (2).

Ejemplo2: Cuantos Clientes Facturaron Más de $80,000 =CONTAR.SI(F3:F14,“>80000”) La función Contar.Si buscará en el rango de celdas (F3:F14) todas las veces que aparece el criterio especificado, que en este caso es “>80000”, refiriéndose a clientes que facturaron más de ese monto; y mostrará cuantos hay, en este caso, solo tres (3). (3). Si cambiáramos la condición a “<60000”, entonces la función mostraría nueve (9) clientes. clientes.

Página 56


FUNCIONES ESTADISTICAS MAX / MIN / MODA / MEDIANA (MEDIAN) Este grupo de funciones tienen en comun que todas evaluan un rango de celdas con valores para solucionar de manera muy rapida, un resultado estadistico sobre el valor mas alto, el valor mas bajo, la moda o la mediana.

MAX () Devuelve el valor mas alto alto de un rango de valores en un rango de celdas. No toma en cuenta ni los textos ni los valores logicos.

Sintaxis Función MAX () = MAX ( RangodeCeldasDeValores RangodeCeldasDeValores ) ) =MAX(B3:B18) Resultado: 344,000.00

MIN () Devuelve el valor mas bajo bajo de un rango de valores en un rango de celdas. No toma en cuenta ni los textos ni los valores logicos.

Sintaxis Función MIN () = MIN ( RangodeCeldasDeValores RangodeCeldasDeValores ) ) =MIN(F3:F14) Resultado: 6,573.00

MODA () Devuelve el valor que mas se repite de repite de un rango de valores en un rango de celdas. No toma en cuenta ni los textos ni los valores logicos. Si existen mas de un valor con igual repeticion devuelve el primero.

Sintaxis Función MODA () = MODA ( RangodeCeldasDeValores RangodeCeldasDeValores ) ) =MODA(F3:F14) Resultado: El Monto Facturado Mas Frecuente o que Mas se Repite en este caso es: 84,462.00

Página 57

Excel Avanzado +

MEDIANA () La mediana es el numero que se encuentra justo justo en la mitad de un conjunto de numeros ordenados. La funcion Mediana(), devuelve la mediana de mediana de un rango de numeros dados en un rango de celdas. Toma en cuenta los textos y los valores logicos.

ENTENDIENDO EL PROCESO MANUAL: Como Se Calcula La MEDIANA 1. Ordenar los numeros dados según su valor de menor a mayor;

Ordenar

2. Encontrar el medio; y 3. El numero que este ahí esa es LA MEDIANA. 4. EXCEPCION: Si la cantidad de números en el conjunto es par (ejemplo 12 números como en el ejemplo), a. Encontremos primero el par central de números (el número 6 y el 7, que son el 7,946.40 y el 8,176.60); y b. Luego calculamos su valor promedio ((7,946.40 + 8,176.60) / 2); y c. Esa es LA MEDIANA = 8,061.50

Sintaxis Función MEDIANA () = MEDIANA ( RangodeCeldasDeValores RangodeCeldasDeValores ) =MEDIANA(F3:F14)

Resultado: La Mediana de los Montos Facturados en este caso es: 8,061.50 Pero si en lugar de darle 12 numeros, le dieramos 11 (que es impar), la funcion automaticamente automaticamente (de forma interna) ordenaria los numeros, y el que queda en el centro ese es la mediana, el cual seria:7,946.40 seria: 7,946.40 Aplicación de La Mediana en la Vida Cotidiana: La Mediana nos presenta La Tendencia Central 1. Hemos dividido en dos mitades (50% - Percentil 50 en Estadistica) los valores facturados de nuestros clientes, y esto nos permite hacer las siguientes inferencias: 2. El 50% de las facturaciones son menores de (<=8,061.50); y 3. El 50% de las facturaciones son mayores que >=8,061.50.

Aplicación: CUIDADO Con Las Diferencias Entre El Promedio y La Mediana El Promedio y La Mediana mostraran resultados muy similares SIEMPRE que los valores de las muestras sean homogeneos (similares, parecidos), pero cuando sean valores muy diferentes, La Mediana presentara un valor mas apropiado que le permitira identificar la tendencia real de su situacion. Resultados de ambos en este caso: Promedio: 31,436.42 (El Promedio de Facturacion NO ES de 31,436.42) Mediana: 8,061.50 (La Mitad esta por encima de (>=) y el otro 50 % esta por debajo de(<=)). Página 58


FUNCIONES DE TEXTO MAYUSC (Upper) / MINUSC (Lower) / NOMPROPIO (Proper) / CONCATENAR (Concatenate) Este grupo de funciones tienen en comun que todas trabajan con textos; ya sea que esten en celdas o directamente pasando los valores entre comillas, según la utilidad de cada una.

MAYUSC (Upper) Convierte un texto o cadena de texto (dada en una celda o de forma directa entre doble comillas) en letras mayusculas.

Sintaxis Función MAYUSC () = MAYUSC ( ( Celda Celda ) =MAYUSC(A4) Resultado: PEDRO

MINUSC (Lower) Convierte un texto o cadena de texto (dada en una celda o de forma directa entre doble comillas) en letras minusculas.

Sintaxis Función MINUSC () = MINUSC ( (Celda C elda ) =MINUSC(A4) Resultado: pedro

NOMPROPIO (Proper) Convierte un texto o cadena de texto (dada en una celda o de forma directa entre doble comillas) en la primera letra en mayuscula y las demas en minusculas.

Sintaxis Función NOMPROPIO () = NOMPROPIO NOMPROPIO( ( Celda Celda ) =NOMPROPIO(G4) Resultado: Pedro

Página 59

Excel Avanzado +

CONCATENAR (Concatenate) Une varios elementos de textos (dados en celdas o de forma directa entre doble comillas) en uno solo.

Sintaxis Función CONCATENAR () = CONCATENAR CONCATENAR( ( Celda Celda ,“ ”,Celda ) =CONCATENAR( A4, “ ”, C4 ) Resultado: Pedro Mejia

CONCATENAR usando el simbolo Ampersand (&) Une varios elementos de textos (dados en celdas o de forma directa entre doble comillas) en uno solo.

Sintaxis símbolo Ampersand (&) = Celda&“ ”&Celda =A4&“ ”&C4

Resultado: Pedro Mejia

Página 60


FUNCIONES DE BUSQUEDA / CONSULTA BUSCARV (Vlookup) Significa Buscar Vertical; ya que la busqueda es por columnas y los resultados a extraer de la base de datos tambien son son en columnas y estas, como sabemos, son verticales. Esta funcion busca un valor en la primera columna de la izquierda de una tabla y luego devuelve un valor relacionado al encontrado (de la misma fila) basado en el numero de una columna especificada de esa base de datos.

Sintaxis Función BUSCARV () = BUSCARV ( (Valor_Buscado, V alor_Buscado, BaseDatosDondeBusca , ColumnaATraer , ,TipoCoincidencia TipoCoincidencia ) ) =BUSCARV( B15 B15 , A26:G185 , A26:G185 , 2 , FALSO ) Valor Buscado: Es el valor a buscar en la primera columna de la tabla: puede ser un valor, una referencia o una cadena de texto. Base Datos Donde Busca : Es la tabla donde se busca el valor clave de la primera columna, y a partir de este se extraen las columnas de datos solicitadas.

Columna A Traer : Es el numero de la columna solicitada según el orden de la tabla para mostrar los datos buscados partiendo del elemento clave.

Tipo Coincidencia: Coincidencia: Este es un valor logico y se refiere a la coincidencia mas cercana a la primera columna, solo puede tener uno de dos valores: a) VERDADERO :Coincidencia aproximada b) FALSO:Coincidencia exacta

Página 61

Excel Avanzado +

FUNCIONES DE FECHA HOY (Today) / SERIES (Cronológicas / Días Laborables) / DIAS.LAB (NetworkDays) Este grupo de funciones tienen en comun que todas trabajan con fechas; con sus sintaxis particulares, pero que en la mayoria de los casos, es recibir una celda que tiene una fecha, o una fecha digitada directamente.

HOY (Today) Devuelve fecha Devuelve fecha actual (según la fecha que tenga su Sistema Operativo) en formato de fecha..

SERIES de Fecha Cronológica Para crear esta serie de fecha, hagamos lo siguiente:

1. Coloquese en una celda, y digite la funcion =HOY() para tener la fecha del dia actual o escriba la fecha calendario (en formato (en formato de fecha) fecha) a partir de la cual usted quiere calcular los dias laborables. 2. Estando en la pestana Inicio, dirijase al grupo Edicion, y haga clic en este icono 3. Elija ahora Series…

4. Tenemos que elegir algunas opciones: Como distribuir la fecha en la hoja de Excel: Dejare seleccionado Filas porque quiero que las fechas aparezcan una al lado de la otra, otra, si usted quisiera que fuera una debajo de la otra, entonces elija columna. En tipo, quiero que sea Cronologica En unidad de tiempo: Fecha Incremento: En 1 (un dia) Limite (o donde finalizar): el 25 de Abril del 2017. El formato en que usted la va a colocar depende de como aparece la fecha en su sistema operativo. 5. Haga clic en Aceptar

Página 62


SERIES de Fecha Días Laborables Para crear esta serie de fecha, hagamos lo siguiente:

6. Coloquese en una celda, y digite la funcion =HOY() para tener la fecha del dia actual o escriba la fecha calendario (en formato (en formato de fecha) fecha) a partir de la cual usted quiere calcular los dias laborables. 7. Estando en la pestana Inicio, dirijase al grupo Edicion, y haga clic en este icono 8. Elija ahora Series…

9. Tenemos que elegir algunas opciones: Como distribuir la fecha en la hoja de Excel: Dejare seleccionado Filas porque quiero que las fechas aparezcan una al lado de la otra, otra, si usted quisiera que fuera una debajo de la otra, entonces elija columna. En tipo, quiero que sea Cronologica En unidad de tiempo: Dia laborable Incremento: En 1 (un dia) Limite (o donde finalizar): el 25 de Abril del 2017. El formato en que usted la va a colocar depende de como aparece la fecha en su sistema operativo. 10. Haga clic en Aceptar y Observe como Excel hace el salto automatico entre los fines de semana (ya que no considera sabado y domingo laborables)

Página 63

Excel Avanzado +

CALCULAR los Días Laborables entre Dos Fechas Para realizar este calculo entre fechas, hagamos lo siguiente:

1. Coloquese en una celda, y escriba la fecha calendario (en formato (en formato de fecha) fecha) que sera su fecha inicial. Esta es D11 en nuestro ejemplo: 3/1/2017. 2. Coloquese en otra celda, y escriba la fecha calendario fecha) que sera su fecha final. Esta (en formato (en formato de fecha) E sta es D12 en nuestro ejemplo:3/31/2017 3. Finalmente, coloquese en una celda donde usaremos la funcion DIAS.LAB (que en ingles es NetworkDays). NetworkDays). La sintaxis de la funcion Dias Laborables es: =DIAS.LAB(fechaInicial, = DIAS.LAB(fechaInicial, fechaFinal) Escriba en D13: =DIAS.LAB(D11,D12) 4. Y pulse la tecla Enter.

OBTENER el Nombre del Día de Una Fecha Para solucionar esto, siguiendo las referencias de nuestro ejemplo: 1. Coloquese en D16 2. Usaremos la funcion TEXTO que sirve para aplicar un formato a una celda. La sintaxis ”) ]. de Texto es [ =TEXTO(Celda a formatear , “entre doble comillas el formato a aplicar ”) 3. Y escriba en D16: =TEXTO(C16, “DDDD”) 4. Y arrastre el puntero de reproducir y automaticamente automaticamente aparecen los nombres de los dias.

Página 64


AVANZADO Módulo 4. SEGURIDAD EN EXCEL Y AUDITORIA     

Bloquear hojas y libros Control de accesos a los libros Permitir que usuarios editen rangos Encontrar precedentes y dependientes Evaluando fórmulas

Página 65

Excel Avanzado +

SEGURIDAD EN EXCEL Siempre la seguridad ha sido importante, pero en nuestros dias, este tema se ha vuelto vital, y mucho mas cuando hablamos de tecnologia, software, acceso a los datos, a nuestra informacion. Por eso, despues de ver tantas herramientas utiles, poderosas y desarrollar en Excel, tantos trabajos y aplicaciones criticas para nuestra vida, es necesario saber: ¿Cómo aseguro todo mi trabajo en Excel? Pensemos en 4 niveles: Celdas niveles: Celdas / Hojas / Libro y Archivo

Primer principio de Seguridad en Excel Todas las celdas en una hoja de calculo en Excel vienen (estan) bloqueadas. En la imagen de al lado, todas las celdas estan seleccionadas, y al ver la pestana Proteger de Formato de Celdas, podemos observar que estan marcadas como BLOQUEADA .

Como Activar el Bloqueo de Las Celdas C eldas en Excel La reflexion mas comun despues de lo citado anteriormente es: Si todas las celdas vienen bloqueadas: ¿Por qué puedo trabajar en cualquier celda, sin ningun tipo de obstaculo (de bloqueo)? Aunque todas las celdas vienen bloqueadas, para que se active de forma efectiva, nosotros debemos utilizar la opcion Proteger Hoja y Hoja y colocarle una clave para clave para que se active el bloqueo de cada celda.

Compartir La Hoja de Excel Sin que Modifiquen NADA 1. Coloquese en cualquier celda de la hoja que desea proteger 2. Debe estar en la pestana Inicio (si no esta ahí haga clic en Inicio) Inicio) 3. Dirijase al grupo Celdas, y haga clic en Formato 4. En el area de Proteccion, elija la opcion: Proteger hoja …

Página 66


5. Le saldra la siguiente ventana:

6. Deje todo como esta y escriba en el rectangulo una clave para desproteger la hoja, por favor: NO OLVIDE SU CLAVE . Al terminar Pulse Enter o haga clic en Aceptar NOTA: Al crear una clave, es responsabilidad de usted, combinar una serie de simbolos que dificulten el acceso a su hoja por intrusos. Consejo: Use por lo menos 8 caracteres, caracteres , y combinelos: numeros, mayusculas, minusculas y caracteres especiales ($, #, %, etc).

7. Ahora le aparece la ventana de confirmacion, de la clave que usted escribio anteriormente, para que la digite otra vez, tal y como lo hizo la primera vez.

NOTA: Este proceso intenta garantizar, que usted aprenda la clave, ya sea de memoria o escribiendola en algo fisico (no en su disco, ni celular, nada digital). 8. Cuando termine de escribirla, pulse Enter o haga clic en Aceptar. 9. Ahora intente

cambiar una celda de esta hoja protegida. 10. Le el mensaje:

Como todas las celdas estan marcadas como bloqueadas , bloqueadas , al proteger la hoja con una clave, todas las celdas son bloqueadas, y no se pueden hacer cambios. Esto es util, cuando comparto una hoja con alguien que no quiero que modifique nada.

Página 67

Excel Avanzado +

Compartir La Hoja de Excel Permitiendo Algunos Cambios Algunas veces, necesitamos compartir nuestra hoja de trabajo, y permitir algunos cambios.

Por ejemplo: Usted quiere que alguien le ayude a digitar articulos o calificaciones o cualquier otro dato, pero no queremos que esa persona, pueda cambiar las formulas, ni borrarlas accidentalmente, ni cambiar algunos precios fijos en esta hoja. 1. Haga clic en este simbolo para seleccionar la hoja completa (todas las celdas); 2. Sin hacer clic, muevase con el mouse y coloquese dentro del area sombreada; 3. Pulse el boton derecho del Mouse y Celdas ; Haga clic en la opcion: Formato de Celdas;

4. Le saldra la ventana de Formato de Celdas Esta ventana tiene seis (6) pestanas, la que nos interesa para esta ocasión es la denominada: Proteger

Página 68

Antonio P. Peralta Ch. 5. Haga clic en la pestana Proteger

6. Observe que la opcion Bloqueada aparece activada, lo cual (como habia escrito antes) indica que todas las celdas estan bloqueadas ( y como aclare: permiten entrar y cambiar datos, porque la hoja no esta protegida con una clave)

7. Haga clic en el cotejo (check) para quitarlo, y asi como tenemos la hoja completa seleccionada (TODAS LAS CELDAS queden

DESBLOQUEADAS).

8. Pulse el boton Aceptar NOTA: Si ahora usted decidiera proteger esta hoja con una clave (password), esto no evitaria que alguien haga cambios en cualquier celda. ¿Por qué? Porque todas las celdas estan desbloqueadas. Por lo tanto, no olvide, que cuando hablamos LA SEGURIDAD hasta este punto, tiene que ver con celdas bloqueadas y hoja protegida con clave (mas clave (mas adelante en este modulo, veremos otras opciones que se combinan con estas para fortalecer la seguridad).

Recuerde el objetivo que perseguimos con todo esto: a. Compartir una una hoja de trabajo de Excel con otra persona (por eso es que no bloqueo todas las celdas, para que esa persona pueda trabajar en la parte de la hoja que puedo delegarle a otro) b. Bloquear algunas celdas para que no puedan hacerles ningun cambio;

¿Cuáles son esas celdas? i. Las que tienen formulas formulas que no queremos que sean cambiadas ni borradas accidentalmente, y ii. Celdas con valores constantes, fijos, fijos , que no deben ser modificados 9. Ahora solo tenemos que seleccionar en la hoja, las celdas que tienen formulas, y las celdas que tienen valores fijos;

Página 69

Excel Avanzado + 10. Sin hacer clic, muevase con el mouse y coloquese dentro del area sombreada; 11. Pulse el boton derecho del Mouse y

Haga clic en la opcion: Formato de Celdas; Celdas;

12. Elija la pestana Proteger Observe que la opcion Bloqueada, NO APARECE MARCADA.

13. Haga clic en la caja cuadrada para colocarle un colocarle un cotejo (check), y asi TODAS LAS CELDAS QUE TENEMOS SELECCIONADAS (sombreadas) queden BLOQUEADAS , y asi no

se puedan hacer cambios

14. Pulse el boton Aceptar Y como hemos escrito antes, para que el bloqueo se haga efectivo, tenemos que proteger la hoja con hoja con una clave. 15. Haga clic en pestana en pestana Revisar Revisar , dirijase al grupo Cambios y pulse el boton Proteger hoja

Página 70


16. Le saldra la siguiente ventana:

17. Deje todo como esta y escriba en el rectangulo una clave para desproteger la hoja, por favor: NO OLVIDE SU CLAVE . Al terminar Pulse Enter o haga clic en Aceptar NOTA: Al crear una clave, es responsabilidad de usted, combinar una serie de simbolos que dificulten el acceso a su hoja por intrusos. Consejo: Use por lo menos 8 caracteres, caracteres , y combinelos: numeros, mayusculas, minusculas y caracteres especiales ($, #, %, etc).

18. Ahora le aparece la ventana de confirmacion, de la clave que usted escribio anteriormente, para que la digite otra vez, tal y como lo hizo la primera vez.

NOTA: Este proceso intenta garantizar, que usted aprenda la clave, ya sea de memoria o escribiendola en algo fisico (no en su disco, ni celular, nada digital). 19. Cuando termine de escribirla, pulse Enter o haga clic en Aceptar. 20. Ahora compruebe: Puede modificar todo, todo, menos:

porcentaje de ITBIS, ITBIS, los Precios ni las celdas de formulas (subtotal, (subtotal, itbis y total), si intenta hacerles cambios tendra este error:

Página 71

Excel Avanzado +

Protegiendo El Libro de Trabajo en Excel Cuando hablo de un libro de trabajo en Excel , es el documento de trabajo en Excel, que generalmente, esta compuesto por mas de una hoja de calculo . Por lo tanto, al pensar en seguridad, proteccion, no debemos abarcar solo celdas o rangos de celdas, ni tampoco solo hojas; ya que un libro de Excel es el contenedor principal de nuestros datos.

Pasos para Proteger El Libro de Trabajo en Excel 1. Haga clic en pestana Revisar , 2. Dirijase al grupo Cambios; y 3. Pulse el boton Proteger libro 4. Le aparecera la ventana, donde podra digitar su clave secreta. NOTA: Dos cajas de cotejo son mostradas: La primera (1ra.) Estructura: aparece marcada (elegida). Se refiere a IMPEDIR que otros usuarios: Cambien los nombres a las hojas de calculos, vean hojas ocultas, agreguen, eliminen, muevan u oculten hojas de calculos. • • •

La segunda (2da.) Ventana: aparece desmarcada y deshabilitada, no es posible elegirla en las versiones mas recientes de Excel, ni en 2013 ni en Excel 2016. Pero si usted usa Excel 2010 o algunas de las versiones anteriores, podra seleccionar Ventana e IMPEDIR que otros usuarios: Cambien el tamaño de la ventana del libro, Muestren, Oculten, Muevan o Cierren Cierren ventanas • •

5. Escriba la clave secreta y pulse Enter o el boton Aceptar

6. Escriba la clave exactamente igual para confirmar y pulse Enter o el boton Aceptar

7. Compruebe ahora intentando cambiar el nombre de una hoja del libro (ej. La Hoja Listado) y le saldra un error como este:

Página 72


Protegiendo Un Archivo en Excel (Control de accesos a los libros) Ya hemos visto, como proteger celdas, rangos de celdas, una hoja de calculo, y recientemente les mostre tambien, como proteger un libro de trabajo de Excel. Ahora, veamos como proteger un archivo de Excel. Quizas algunos de nuestros lectores, hasta aquí, habian pensando que proteger un libro de Excel y un archivo de Excel se hacian de la misma forma.

Como Proteger Un Archivo en Excel Tenemos dos formas de lograr esto:

1. Encriptación o Cifrado 2. Configuración de Contraseña para Abrir o Modificar un Archivo de Excel

Encriptación o Cifrado La encriptación o cifrado de archivos, es un medio (opción o herramienta) que hace que los datos de un archivo queden completamente indescifrables. El objetivo es, volver ese archivo inservible, para cualquier persona no autorizada a leerlo; inclusive si logra tenerlo o copiarlo, pero no tiene la contraseña , no podrá leerlo o verlo.

Pasos para Encriptar Un Archivo en Excel archivo (el libro de trabajo de Excel) que usted quiere encriptar; 1. Tener abierto el archivo (el 2. Haga clic en Archivo; 3. Elija Informacion;

4. Elija Proteger Libro ;

5. Elija Cifrar con contraseña

Página 73

Excel Avanzado + 6. Escriba una contraseña

ADVERTENCIA Para escribir su contraseña: Lea el mensaje en esta ventana en Precaución.

7. Ahora en la ventana Confirmar contraseña, vuelva a escribir la contraseña que escribió anteriormente.

8.

Ahora el libro muestra que esta protegido:

9.

Cierre el archivo e intente abrirlo para que vea lo que aparecerá no solo a usted sino a cualquiera cuando intente abrir este archivo: Le pide la

contraseña para poder abrirlo.

Página 74


Como Proteger Un Archivo en Excel Ahora vamos a ver la segunda forma de proteger un archivo en Excel.

Configuración de Contraseña Abrir o Modificar Archivo de Excel Puede establecer dos contraseñas en el archivo: lectura ; y 1. Para abrir el archivo como de sólo lectura; 2. Para modificar el el archivo.

¿Cómo me puede ayudar esto en la seguridad? Una de las sugerencias más útiles en este sentido es, dar esas contraseñas a las personas según el nivel de acceso que ellas deben tener en el departamento o en la estructura que el departamento de TI haya designado.

Ejemplo: a) Comparta la clave de sólo lectura, con lectura, con personas que solo deben ver , leer , consultar datos o informaciones del libro de trabajo, pero que no necesitan o no deben tener acceso a cambiarlos; y b) Súplales la contraseña para modificar el el archivo, a aquellas personas que, según las directrices internas de la institución y los departamentos implicados, pueden hacer cambios (recuerde regresar a la sección 1 de este módulo de seguridad, para refrescar como bloquear rangos de celdas que no quiero que sean modificados, y como permitir que otros rangos o celdas si puedan ser cambiados.)

¿Cómo hacer esto? Siga estos pasos para establecer una contraseña para abrir o modificar un archivo de Excel: 1. Tener abierto el archivo (el archivo (el libro de trabajo de Excel) que usted quiere encriptar; 2. Haga clic en Archivo;

3. Elija Guardar como;

Página 75

Excel Avanzado + 4. Seleccione la ubicación de su archivo (Haga (Haga Clic en Este PC , Examinar u u otras en linea)

5. Elija el folder donde va a guardar y en el area inferior haga clic en Herramientas y Herramientas y elija Opciones generales

6. Escriba una o ambas contraseñas: Una para abrir el archivo y otra para modificar modificar el archivo,

dependiendo de los dependiendo requerimientos.

7. Confirme escribiendo de nuevo las mismas contraseñas. La primera ventana de confirmación confirmació n se refiere a la contraseña de apertura y la

siguiente ventana de confirmación confirmació n se refiere a la contraseña escritura .

Página 76

Antonio P. Peralta Ch. 8. Finalmente, haga clic en Guardar

9. Al intentar ABRIR este libro de Excel , primero pedira la apertura, y contraseña de apertura, luego le pedira la la escritura, en contraseña de escritura,

el caso de no tener esta ultima, la persona tendra la opcion de elegir solo lectura para lectura para abrirlo y verlo, pero sin poder modificarlo.

10. Si hace clic en el boton Solo lectura, abrira el archivo y podra hacer

modificaciones, modificaciones, y guardarlas con otro nombre, pero no podra sobre-escribir el archivo abierto. Si tiene la contraseña de escritura, escritura, podra hacer cambios al libro especifico y sobreescribirlo (grabarlo con el mismo nombre que tiene el libro abierto).

Página 77

Excel Avanzado +

Permitir a Usuarios Editen Rangos En el tema de seguridad, también podemos trabajar trabajar concediendo permisos, según sea usuarios de la red local o eligiendo de los diferentes usuarios que están en nuestra computadora local (usted, guest, y otros).

Como Funciona Permitir a Usuarios Modificar Rangos Aunque haga todos los pasos correctamente, para elegir los usuarios que podrán modificar sus rangos de celdas, recuerde activar la protección con contraseña de la hoja de cálculo, para que entonces funcione la restricción de las celdas y rangos de celdas.

¿Cómo hacer esto? Siga estos pasos para permitir a usuarios específicos modificar rangos de celdas: 1. Tener abierto el archivo (el archivo (el libro de trabajo de Excel) donde quiere aplicar esto; 2. Elegir la hoja de calculo del libro donde esta el o los rangos a trabajar; 3. Haga clic en la pestana Revisar (Review) Review); 4. Dirijase al grupo Cambios; Cambios;

5. Elija Permitir a Usuarios Modificar Rangos; Rangos ; Le aparecera esta ventana: 6. Haga clic en boton Nuevo … y mostrara esta ve ntana:

7. En Título: Escriba un nombre para el rango, como Datos, Cantidad , etc. En Correspondiente a las celdas (haga clic en y seleccione las celdas) En Contraseña del rango , escriba la clave con la que el usuario podrá acceder a modificar el rango de celdas, y pulse Aceptar. Finalmente, debe proteger la hoja con contraseña para que esto funcione.

Página 78


Asignando Permisos a Usuarios del Sistema Los permisos son derechos de accesos que se otorgan a usuarios, según el esquema que la empresa haya definido y validado previamente, con fin de mantener la data segura y asequible.

¿Cómo hacer esto en Excel? 1. Vuelva a acceder a la ventana: Permitir a usuarios modificar rangos de celdas ; 2. Haga clic en el botón Permisos… 3. Aparece la ventana de Permisos para ese rango (Cantidad)

4. Pulse el botón Add…(Agregar) para poder asignar usuarios o grupos de usuarios y entonces el programa marcara en Permissions (Permisos) la opción Allow (Permitir) en Modificar rango sin contraseña. 5. Escriba el nombre del usuario y pulse Check Names Names (Verificar Nombres) para validar que existe el usuario de forma local o en la red a la cual usted está conectado.

6. Finalmente pulse el botón OK (Aceptar) 7. Y verá en la pestaña Security (Seguridad), el usuario agregado con el nombre del equipo, y en el área de Permissions para ese usuario, marcado en Allow (permitir) Modificar rango sin contraseña. 8. Clic en Aceptar. 9. Proteja la Hoja con Contraseña para que todo esto funcione.

Página 79

Excel Avanzado +

AUDITORIA EN EXCEL El diccionario digital de la Real Academia de la Lengua Espanola en su sitio Web: http://dle.rae.es/, define la Auditoria como: 1. f. Revisión sistemática de una actividad o de una u na situación para evaluar el cumplimiento de las reglas o criterios objetivos a que aquellas deben someterse. 2. f. Revisión y verificación de las cuentas y de la situación económica de una empresa o entidad. Microsoft Excel incluye algunas herramientas que nos ayudan a revisar, verificar y evaluar, los procesos de cálculos (Fórmulas) que hemos efectuados en nuestras hojas y libros de trabajo en Excel, y están agrupadas bajo el título Auditoria de Formulas.

¿Qué es Auditoria de Fórmulas en Excel? Es una división (un grupo) en la pestana F órmulas, que incluye varias herramientas, de las cuales trataremos aquí las siguientes: Rastrear precedentes Rastrear dependientes Mostrar f órmulas • • •

•

Comprobación de errores Rastrear error Referencias circulares

•

Evaluar f órmulas

• •

Rastrear precedentes y dependientes Para entender estas herramientas bien, debemos recordar que estamos AUDITANDO FORMULAS; por lo tanto, debemos estar colocado en una CELDA ACTIVA que tenga una f órmula, de lo contrario, un mensaje de error nos advertira sobre esta condicion.

Los precedentes

de una formula son las celdas implicadas para obtener esa fórmula.

precedentes son las Ejemplo: En la fórmula de Subtotal , los precedentes celdas necesarias para tener el subtotal, quizás usted piense: ¡Eso es evidente! Pero cuando usted está trabajando, auditando, una hoja o un libro de trabajo de Excel que usted no creo, no siempre todo es tan evidente, y mire lo bien que lo representa Excel y lo ayuda a usted con el menor esfuerzo. Colóquese en L6 y haga clic en la pestaña Formulas, y en el grupo Auditoria de Formulas haga

clic en Rastrear precedentes. precedentes. rastro (las flechas color azul), una flecha que marca el origen Automáticamente aparece el rastro (las con un primer círculo azul de izquierda a derecha, luego otro circulo azul especificando el próximo precedente, hasta señalar con una punta de flecha la celda donde está la fórmula que estamos auditando. Página 80


¿Cómo limpiar el rastro? 1. Haga clic en la pestaña Formulas; Formulas; 2. Observe el grupo Auditoria de Formulas; 3. Haga clic en Quitar flechas.

Los dependientes de una formula son las celdas que dependen de esa fórmula donde estoy colocado. dependientes son las celdas que dependen del Ejemplo: En la fórmula de Subtotal, los dependientes subtotal. Colóquese en L6 y haga clic en la pestaña Formulas, y en el grupo Auditoria de Formulas haga

clic en Rastrear dependientes. Automáticamente aparece el rastro rastro (las flechas color azul), en este caso, como estamos partiendo del subtotal (celda L6) inicia con un circulo de izquierda a derecha, luego puntas de flechas que marcan los dependientes (dos dependientes (dos en este caso: ITBIS y TOTAL) de la fórmula que estamos auditando. Finalmente, limpie el rastro.

Página 81

Excel Avanzado +

Evaluar Fórmulas Al igual que con las herramientas anteriores, recuerde que estamos auditando formulas; por lo tanto, colóquese en una celda donde hay una formula. La herramienta Evaluar Fórmulas nos muestra un cuadro, donde nos permite depurar dicha fórmula, evaluando cada una de sus partes individualmente, empezando por la izquierda hacia la derecha hasta culminar la evaluación.

Observe de izquierda a derecha, el primer elemento (en este caso la celda J6) esta SUBRAYADA , para evaluar este elemento, haga clic en el botón Evaluar . El cuadro muestra que el resultado de la evaluación de la celda J6, es el valor 6.

Observe de izquierda a derecha, el segundo elemento (en este caso la celda K6) esta SUBRAYADA , para evaluar este elemento, haga clic en el botón Evaluar . El cuadro muestra que el resultado de la evaluación de la celda K6, es el valor 59. Y si vuelve a pulsar Evaluar, le mostrara el resultado: 354 Siempre que sea correcto, en caso contrario, si tiene un error, entonces use la herramienta Rastrear Error .

Página 82


Rastrear Error El objetivo con esta herramienta, al igual que las anteriores, es auditar (revisar / verificar) formulas, en este caso específico, celdas con fórmulas con mensajes de error.

1. 2. 3. 4.

Haga clic en una celda con formula y con mensaje de error Haga clic en la pestaña Formulas; Formulas; Observe el grupo Auditoria de Formulas;

Haga clic en Icono Rastrear Error

5. Observe las opciones 6. Elija Rastrear error

7. En 1er ejemplo, estamos en celda ce lda L6 (Subtotal) y hacemos clic en rastrear error.

8. En 2do ejemplo, estamos en celda ce lda N6 (Total) y hacemos clic en rastrear error.

Para limpiar el rastro del Error 1. Haga clic en la pestaña Formulas; Formulas; 2. Observe el grupo Auditoria de Formulas; 3. Haga clic en Quitar flechas.

Página 83

Excel Avanzado +

BONO OCULTAR LAS FORMULAS Normalmente las fórmulas que hemos creado en nuestras hojas de trabajo en Excel, se pueden ver con mucha facilidad; ya que basta, con colocarse en la celda, y mirar la barra de contenido/formulas y ya.

Pero quiero darle este regalo (bono) incluido en este cierre, le mostraré lo fácil que puede ocultar sus fórmulas para que nadie las vea; y recuerde que todo esto para ser más efectivo, usted debe combinarlo con las opciones de seguridad que explicamos en la sección de Seguridad en este módulo.

¿Cómo ocultar las Formulas? - Para que no se vean en la barra de contenido 1. Seleccione las celdas que tienen las fórmulas que desea ocultar; 2. Pulse el botón derecho del Mouse; y elija Formato de Celdas 3. Elija la pestaña Proteger

4. Y haga clic en el cuadrado de Oculta, para poner un cotejo, y que en las celdas seleccionadas no muestren la formula en la barra de contenido. Página 84

Antonio P. Peralta Ch. 5. Para que esta configuración funcione recuerde que usted debe proteger la hoja con una contraseña, entonces las celdas dejarán de mostrar las formulas en la barra de contenido.

6. Después que confirme la contraseña, haga clic en el botón Aceptar 7. Ahora colóquese en cualquiera de las celdas que tienen formulas, y al observar en la barra de contenido, podrá darse cuenta que no muestra la formula.

Como puede notar, al proteger la hoja con contraseña, ya las formulas de las celdas seleccionadas estan ocultas. Gracias a Dios lo hemos logrado!

Página 85

Excel Avanzado +

BONOS Módulo 1. GENERANDO CONOCIMIENTO             

Creación de una base de datos Ordenar una base de datos Filtros simples Filtros avanzados Subtotales Funciones de base de datos Consultas a base de datos Importar y exportar Formularios y plantillas Creando formulario personalizado Validación de datos Tablas dinámicas Gráficos dinámicos

Página 86


CREACION DE UNA BASE DE DATOS Llamamos base de datos a datos que un conjunto de datos guardan algún tipo de relación entre ellos.

Datos vs Información Toda información contiene datos. De hecho, todas las opiniones que emitimos de forma verbal o escrita, están compuestas de datos. Ejemplo: El verano fue tan caluroso que tuvimos temperaturas temperaturas de más de 30 grados Celsius.

Identifiquemos los datos en la frase anterior: Verano : Estación Caluroso: Característica Temperaturas: > 30 Celsius • • •

Los datos son datos son los componentes básicos de cualquier información. Por ende, podemos definir la información información como un conjunto de datos ordenados para emitir un mensaje o una idea con significado.

Caracteres

Datos

Informacion

En resumen, para crear datos necesitamos caracteres y/o símbolos, símbolos, para crear información necesitamos datos que formen unidos significado.

Lo Primero es La Visión: Visión : ¿Para qué usare esos datos? Antes de crear nuestros listados de datos, pensemos primero, para que servirá todo ese trabajo. Piense que se digitarán cientos, miles o millones millone s de registros (filas de informaciones en Excel), y con esa base de datos, usted podrá: • • • • • • • • •

Crear tabla de datos rápidamente Consultas de datos por cualquier nivel de datos Consultas avanzadas combinadas Segmentación de datos por tablas Reportes categorizados por áreas Tablas dinámicas Segmentación de datos avanzadas combinadas Gráficos dinámicos por áreas Combinación de las características citadas antes

Página 87

Excel Avanzado +

NORMALIZACION Un Principio para Crear EXCELENTES Listados de Datos En este contexto, un principio es como una regla, un patrón, que si usted lo pone en práctica, economizará mucho tiempo, será más productivo y conseguirá (si Dios quiere) casi cualquier cosa con los datos digitados. Normalizar un un listado de datos en palabras sencillas es lograr que en cada columna de datos, no haya más de un tipo de datos.

Ejemplo1: En el artículo digitado en la celda B6, tenemos varios datos:

a) b) c) d) e) f)

El nombre del articulo; Detalle; La marca; Especificación de Grasa; Unidad; y Cantidad de esa unidad Ese dato no está normalizado; por lo tanto, para NORMALIZARLO , debo crear una columna para cada uno de los tipos de datos.

Ejemplo1 Normalizado: Ahora sí, el listado de datos, tiene las columnas que permitirán que cada dato encuentre su lugar UNICO, para estar

normalizado.

Ejemplo2: En el artículo digitado en la celda B6, tenemos varios datos:

a) El primer nombre; b) El segundo nombre; c) El primer apellido; y d) El segundo apellido Ese dato no está normalizado; por lo tanto, para NORMALIZARLO , debo crear una columna para cada uno de esos datos.

Ejemplo2 Normalizado: Ahora sí, el listado de datos, tiene las columnas que permitirán que cada dato encuentre su lugar UNICO, para estar

normalizado.

Página 88


Beneficios de NORMALIZAR Los Listados de Datos Cuando tenemos nuestros listados (de artículos, de productos, de compras, de ventas, de empleados, entre otros) normalizados, entonces podemos ordenar, consultar, crear reportes por cualquiera de esos datos lo que queramos, y así facilitar las búsquedas, los reportes, los resúmenes y los detalles de nuestras operaciones registradas en nuestra inmensa base de datos.

Nuestro Listados de Datos NORMALIZADO

Página 89

Excel Avanzado +

ORDENANDO Los Datos de Nuestro Listados Básicamente, existen tres formas de organizar los datos en Excel: 1) Ascendente: Ordenar de A a Z 2) Descendente: Ordenar de Z a A 3) Personalizar la organización de datos

Orden Ascendente o Descendente La mayoría de veces, cuando pensamos en el orden de A a Z, suponemos que en lugar de ascender está descendiendo, pero lo que sucede es que como la A es la primera letra de nuestro alfabeto y la Z (zeta) es la última, si le colocáramos números A sería igual a 1 y Z sería un número mayor que 20; por lo tanto, en el orden de A a Z está aumentando de 1 en adelante, por eso le denominamos ASCENDENTE , mientras que en el orden de Z a A va disminuyendo de mayor que 20 a 1, por eso le llamamos DESCENDENTE .

CUIDADO Con Ordenar UN SOLO DATO en un Listado de Datos Cuando todos mis datos están digitados digitados como un listado de datos, es decir, que no son una tabla formal de Excel, entonces las filas de datos (o registros), no están unidas entre sí, sino que son individuales; por lo cual, si ordeno solo los artículos (de A a Z) del rango B6:B11 (en el ejemplo del listado de abajo ), Cornflakes vendrá a ser el primero, pero el detalle que le quedará al lado será Vaca, porque no están unidos como registro (fila en Excel); por lo tanto

el orden en cuanto al dato Artículos será hecho, pero el orden en cuanto a todos los datos relacionados entre sí, NO SERA HECHO… Pruébelo conmigo: 1) Seleccionar los datos de los artículos; 2) Haga Clic en la pestaña Inicio (si no está seleccionada); 3) Y en la última sección (Modificar), haga Clic en Ordenar y filtrar:

4) Y elija Ordenar de A a Z

Página 90


5) A continuación Excel presenta una advertencia que dice: “Microsoft

Excel ha encontrado datos junto a su selección. Estos datos no se ordenaran, ya que no se han seleccionado.”

6) Si usted decide Ampliar decide Ampliar la selección, selección , pulsando Enter o eligiendo Ordenar, Excel hará esto: Los datos están en orden, pero con algunos errores de presentación con el título y otros datos. 7) Para evitar este mensaje y otros errores: Podemos seleccionar todos los datos del listado (sin incluir los títulos principales del reporte) ANTES de elegir la opción de ordenar de A a Z.

8) Notaras que Excel, deja el listado sin alteración; ya que asume el primer dato (Código Articulo) como el dato clave para la organización de todo el listado; y ya que este dato está organizado de forma Ascendente (de A a Z, que es lo mismo que de menor a mayor) no hay cambios. 9) Por lo tanto, para lograr que Excel organice este listado de datos de A a Z, seleccione como antes, pero sin el dato Código Articulo. Página 91

Excel Avanzado +

FILTROS SIMPLES Un filtro es una herramienta automática que Excel coloca en cada columna de dato para poder realizar consultas en esos datos de forma rápida y exacta. e xacta.

Creando una Tabla La forma más rápida y fácil de activar filtros a cada dato en un listado de datos, es aplicar un FORMATO DE TABLA a todo el listado de datos, pero solo seleccione el conjunto de datos, NO TODA LA HOJA .

Pasos: 1) Seleccione el conjunto de datos del listado de datos 2) Haga Clic en la pestaña Inicio (si no está seleccionada); 3) En la sección Estilos, haga Clic en Dar formato como tabla:

4) Los formatos de tabla están divididos en tres tipos: Claro, Medio y Oscuro. Elija uno, en la imagen tengo seleccionado Estilo de tabla medio 2 5) Excel identifica el rango de datos y marca que tiene encabezados.

6) Pulse Aceptar

Página 92


CONSULTA CON FILTROS SIMPLES Cuando convertimos nuestro listado de datos en una tabla, Excel le coloca automaticamente un filtro ( ) a cada columna de los datos de la tabla.

Consulta En este contexto (de los filtros), una consulta es aplicar una condición a una de las columnas de una tabla de datos, para que presente solo los datos que cumplen con el criterio previamente especificado.

Tipos de Filtros Los filtros son los operadores lógicos que lógicos que podemos aplicar, según sea el tipo de dato de la columna elegida (sea texto o numérico). Existen dos tipos de filtros: 1. Filtros de texto 2. Filtros de número

Filtros de texto: Los operadores lógicos para columnas cuyos tipos de datos son textos:

1) 2) 3) 4) 5) 6) 7)

Es igual a… No es igual a… Comienza por… Termina con… Contiene… No contiene… Filtro personalizado…

Página 93

Excel Avanzado +

Filtros de número: Los operadores lógicos para columnas cuyos tipos de datos son numéricos:

1) 2) 3) 4)

Es igual a… No es igual a… Mayor que…

Mayor o igual

que… 5) Menor que…

6) Menor o igual que… 7) Entre… 8) Diez mejores…

9) Superior del promedio 10) Inferior al promedio 11) Filtro personalizado…

Página 94


Como Ver Todos Los Datos: Los datos no han sido eliminados, sino que solo están siendo mostrados los que cumplen con la condición aplicada a la tabla de datos completa.

Pasos para Ver Todos Los Datos Después de Haber Aplicado un Filtro 1) Haga clic en la columna donde antes aplico el filtro: Observe dos casos en las dos imágenes siguientes: Tanto en la columna Marca (de texto);

y luego en la columna Precio (de número) 2) Haga clic en Borrar filtro (Clear Filter…)de entre doble comillas, aparecerá el nombre de la columna de datos; y 3) Le son mostrados todos los datos de su tabla.

Página 95

Excel Avanzado +

SEGMENTACIÓN Una de las herramientas que más disfruto al usar una tabla en Excel E xcel para analizar datos, es la segmentación (Slicer en en inglés), ya se dará cuenta cue nta por qué. La palabra segmentar significa dividir, separar en porciones que guardan algún tipo de relación o igualdad.

¿Cómo Funciona La Segmentación? En el caso de la tabla de datos, cuando le aplico la herramienta de segmentación, puedo elegir una o varias de las columnas de datos de mi tabla, y entonces por cada una de las columnas que elegí, Excel crea un segmento que contiene todas las opciones de ese tipo de dato, y automáticamente me filtra toda la tabla cuando hago clic en ese segmento, y me permite hacer clic en los otros segmentos también, permitiéndome así crear una consulta interactiva (‘inteligente’ ) por varios tipos tipos de columnas al mismo tiempo tiempo . Veamos.

Pasos para Aplicar Segmentación 1) Colóquese en cualquier celda de su tabla de tabla de datos; 2) Le aparecerá en la parte superior de la barra de título a la derecha: Herramientas de Tabla con una opción llamada Diseño; Diseño; 3) Haga clic en Diseño; Diseño;

4) Observe que la pestaña Diseño está dividida en 5 grupos ( Excel versión 2016) 2016 ) que son de izquierda a derecha: Propiedades, Herramientas, Datos externos de tabla , Opciones de estilo de tabla y Estilos de tabla . Note Note que segmentación está en el grupo Herramientas.

5) Haga clic en Insertar segmentación de datos; datos ; 6) Le aparece un cuadro con la lista de los títulos de las columnas de datos, para que elija todos los datos por los que quiera segmentar: Haga clic en los cuadrados a la izquierda de sus datos de los que quiera crear segmentación de datos; datos ;

Página 96


7) Para este ejemplo voy a elegir : Artículos, Marca, SubMarca y Tipo; y haga clic en Aceptar 8) Le aparecerá lo siguiente:

9) Ordénelos de tal forma que pueda ver la tabla de datos; ya que cada vez que presione un segmento, sus datos se verán de acuerdo a estos filtros.

10) Ahora voy a segmentar todos los datos: Haciendo clic en el artículo Leche, Leche, y la marca Rica. Rica. Observe en su tabla de datos como está ocurriendo una consulta interactiva.

11) Finalmente, puede cambiar algunas opciones, haga clic en de la parte superior, y pruebe, por ejemplo: cambiando el color o o el título que aparece en cada segmento:

Página 97

Excel Avanzado + 12) Vea los cambios a los segmentos:

APLIQUELO AHORA: Ahora aplique estos conocimientos a los listados de datos de su departamento, en la institución donde usted labora: 1) Normalice sus datos, aplicando el principio de: UN SOLO DATO EN CADA COLUMNA; 2) Ordénelos: ASCENDENTE o DESCENDENTEMENTE; 3) Saque UNA o DOS COPIAS a su listado completo ordenado; 4) Tome una de esas copias y CONVIERTALA en Tabla de Datos; 5) Consulte aplicando FILTROS de texto y Filtros de números; 6) Intente también PERSONALIZAR filtros, haciendo combinaciones; y 7) Finalmente haga SEGMENTACION de sus datos.

Página 98


CONSULTA CON FILTROS AVANZADOS Tomando como fundamento, lo que vimos v imos anteriormente en filtros simples, ahora vamos un paso mas adelante, para seguir haciendo consultas, pero con los filtros avanzados.

Filtros Simples vs Filtros Avanzados: LAS DIFERENCIAS Características

FILTROS SIMPLES

FILTROS AVANZADOS AVANZADOS

Condiciones

1 por columna

Más de 1 por columna

Datos originales

Se ocultan los que no cumplen con el criterio especificado

Se muestran todos

Datos resultantes condiciones

Se muestran los que cumplen con el criterio especificado

Puedo elegir mostrarlo en otra área de la hoja

Ejemplo de Filtros Avanzados

Página 99

Excel Avanzado +

Requisitos para Filtros Avanzados En la imagen sobre esta sección, podemos notar que para usar filtros avanzados, necesito tres elementos:

1) La tabla de datos; 2) La sección de criterios o condiciones, es donde voy a especificar las condiciones que se aplicarán a la tabla de datos y se compone de exactamente los mismos títulos de la tabla de datos y de algunas filas en blanco para especificar las condiciones; y por ultimo 3) El área de resultados, es donde Excel de forma automática, pondrá los datos que cumplan con las condiciones especificadas y se compone de exactamente los mismos títulos de la tabla de datos y de muchas filas en blanco que serán posteriormente llenadas con aquellos datos que cumplen el o los criterios especificados en la sección sec ción de condiciones.

Pasos para Hacer Filtros Avanzados 1) Seleccione los títulos de los datos de la tabla;

2) Coloque el puntero del Mouse (flecha) en el área sombreada, y pulse el botón derecho del Mouse; 3) Haga clic en Copiar; 4) Colóquese algunas filas después del último dato de la tabla; 5) Y en la pestaña Inicio, en el grupo Portapapeles, haga clic en Pegar;

6) Debajo de estos títulos, deje algunas filas en blanco, para que pueda escribir las condiciones;

Página 100

Antonio P. Peralta Ch. 7) Asumiendo que usted dejo dos filas en blanco, ya esta colocado donde van los títulos de la sección resultados, re sultados, entonces, vaya a la pestaña Inicio, en el grupo Portapapeles, y haga clic c lic en Pegar;

8) Si usted ha seguido los pasos hasta aquí, su trabajo debe lucir parecido al ejemplo de la imagen superior: La Tabla de Datos está en el rango de celdas (A5:L11); La Sección de Criterios está en el rango de celdas (A15:L17): Incluye los títulos de cada columna y dos filas en blanco; La Sección de Resultados está en el rango de celdas (A19:L29): Incluye los títulos de cada columna y diez filas en blanco como espacio suficiente para mostrar los resultados que cumplan con las condiciones especificadas en criterios; 9) Con todo eso listo, colóquese en cualquier celda de la tabla de datos, haga clic en la pestaña Datos y Datos y elija Avanzadas elija Avanzadas;;

10) Le aparecerá el cuadro de dialogo de filtros avanzados;

Página 101

Excel Avanzado + 11) Colóquese en Rango de la lista y pulse el botón y especifique el rango de celdas de la tabla de datos, sombreando desde los títulos de los datos hasta la última fila de datos de la tabla y vuelva a pulsar el mismo botón; 12) Colóquese en Rango de criterios y pulse el botón y especifique el rango de celdas de criterios, seleccionando desde los títulos de los datos en la sección criterios e incluya las filas donde especifica los criterios y vuelva a pulsar el mismo botón; 13) Haga clic en el círculo Copiar a otro lugar

14) Colóquese en Copiar a: y pulse el botón y especifique el rango de celdas de Copiar a:, seleccionando desde los títulos de los datos de la sección resultados y las filas en blanco suficientes para los resultados que cumplan con las condiciones especificadas en el rango de criterios (en el ejemplo se han seleccionado 11 filas: 1 la de los títulos de los datos y 10 para los resultados); y 15) Finalmente haga clic en el botón Aceptar

Los resultados son: Las condiciones múltiples fueron: Columna Detalle (Vaca) y en Marca (Rica y Dos Pinos). Y como vemos los resultados van de acuerdo a la realidad del contenido de la tabla de datos.

Los filtros avanzados avanzados nos permiten hacer múltiples y complejas condiciones , los datos originales no son ocultados y podemos pedirle que nos muestre los resultados en otro rango que no afecte ninguna otra fila e incluso podemos aprovechar esta opción para crear reportes , colocando nuestros resultados debajo de los títulos de la institución.

Página 102


SUBTOTALES Es una poderosa herramienta para resumir y agrupar datos según los grupos que contenga mi lista de datos ( no funciona para tabla de datos ), otra razon mas para tener nuestros listados de datos normalizados, como ya explicamos anteriormente en este modulo. Personalmente, llamo a esta herramienta: Reporte en 1 Minuto; ya que agrupa automaticamente (según le especifiquemos), todos los datos de mi listado, e incluso puedo separarlo en paginas distintas por grupos. Veamos como trabaja.

Pasos para Usar la Herramienta SUBTOTALES 1) Seleccione su listado de datos normalizado completo (desde los títulos de los datos hasta la última fila de datos de su listado);

Datos y observe en el grupo Esquema, Esquema, la opción Subtotal; 2) Haga clic en la pestaña la pestaña Datos y

3) Haga clic en Subtotal; Especifiquemos como queremos el resumen, la agrupación: Entendamos lo que dice este cuadro c uadro de diálogo: a) Cada vez que cambie de Artículos, b) Usará la función de Sumatoria (u otra que yo elija de la lista) para generar un total (o promedio, cuenta, máximo, entre otras) para cada grupo de artículos, c) Agregara subtotal a cada columna de datos de mi listado que sea numérica y que yo marque aquí, d) Están marcadas: Reemplazar subtotales actuales; actuales ; y Resumen debajo de los datos, datos, e) Si marcamos la opción: Salto de página entre grupos, grupos , cada cambio de articulo (cada grupo) saldrá en una página diferente, lo cual nos permitiría con muy poco esfuerzo crear reportes por grupos.

Página 103

Excel Avanzado + 4) Elija las siguientes opciones y pulse Aceptar

5)

Veamos los resultados:

Observe que hay dos totales de Leche, cuando solo deberíamos tener un grupo de Leche, por eso, antes de elegir la opción Subtotal y marcar las opciones, debe organizar sus datos para que todos los grupos queden en secuencia no importando el orden en que fueron digitados; ya que después de aplicar subtotal NO PODRA DESHACERLO NI CON CONTROL ZETA(UNDO / CTRL+Z NO FUNCIONARA); así que ANTES DE SUBTOTAL, ORDENE SUS DATOS . Veamos. Seleccione sus datos (como ve en la imagen) y en Inicio, Inicio, elija Ordenar de A a Z 6) Seleccione sus

Página 104


7) Observe el cambio en el orden desde A a Z.

Datos, y elija Subtotal 8) Ahora haga clic en Datos,

Ahora sí, perfecto, observe: a) Agrupación automática automática por artículos; b) Subtotales en cantidad, precio, subtotal, itbis y total; c) Un resumen con el total general ; y por si esto fuera poco, mire los tres(3) niveles de grupos en el 1, 2 y 3 a la izquierda de las letras de la columna: d) Al pulsar 1 verás el resumen general :

e) Al pulsar 2 verás el resumen general por cada grupo: grupo :

Página 105

Excel Avanzado + f) Y al pulsar el 3 verás el resumen con todos los detalles de cada grupo :

9) Seleccione desde A5 hasta L15, y haga clic en Datos y Datos y elija Subtotal otra vez, y marque la opción: Salto de página entre grupos; grupos ;

10) Observe las líneas de la división (los saltos de página)

11) Haga clic en Vista previa de salto de

pagina

Página 106

Antonio P. Peralta Ch. 12) Vera esto:

13) Colóquese en la línea punteada azul para moverla hacia la derecha y así abarcar todas las columnas (desde Artículos hasta Artículos hasta Total ). ). Observe que ahora en lugar de 6 páginas tenemos solo 3 páginas y se ve la división por grupo (líneas gruesas azules);

14) Ahora haga clic en Archivo en Archivo y luego clic en Imprimir (o (o en el acceso Vista preliminar)

Página 107

Excel Avanzado + 15) Al observar las 3 páginas nos damos cuenta que solo la primera contiene el título del reporte y el título de las columnas de datos, para solucionar esto, vamos a hacer clic en la pestaña Diseño de página, en la opción Imprimir títulos;

16) Colóquese en la opción: Repetir filas en extremo superior y y pulse el botón Y con el Mouse haga clic en el número 1 de la Fila1 y manténgalo presionado arrastrando hasta la Fila5, Fila5 , para elegir el título de la institución y el título de los datos de cada columna, para que sean repetidos en cada página de este reporte automáticamente.

17) Finalmente, Finalmente, haga clic sobre el botón 18) Pulse el botón Vista

Preliminar

Página 108


Página 109

Excel Avanzado +

VALIDACION DE DATOS Es una herramienta que nos permite establecer reglas en cada celda de nuestra hoja de trabajo en Excel para controlar efizcamente la entrada de datos, y de esa forma garantizar la exactitud y pulcritud de nuestros datos.

Algunos Usos de la Herramienta VALIDACION VALIDACION DE DATOS • • • • • • • •

Establecer reglas para cada entrada de datos en cada celda; Presentar un mensaje para orientar y asistir al usuario en la entrada de datos; Garantizar que los valores digitados esten dentro del rango establecido; Controlar que el dato digitado se corresponda con su tipo (numerico, texto, etc.); Poder elegir entre opciones pre-establecida; Advertir al usuario en el momento que viole la regla establecida para control; No permitir al usuario violentar las reglas establecidas; y Mantener nuestros listados y tablas de datos ‘limpios’, utiles para los propositos posteriores de consultas, reportes, tablas dinamicas, entre otras.

Pasos para Encontrar la Herramienta VALIDACION DE DATOS 1. Haga clic en la pestaña Datos; datos y haga clic en Validacion de datos; 2. Observe en el grupo Herramientas de datos y

3. Excel le mostrara este cuadro de dialogo:

Página 110


Las 3 Fichas de Las Reglas en la Herramienta VALIDACION VALIDACION DE DATOS Cuando accedes a la herramienta Validación de Datos encontraras (como ves en la imagen

a la derecha de este párrafo) tres (3) fichas para establecer una una regla efectiva de de control de entrada de datos. Veamos las fichas: 1. Configuración Es donde se especifica el criterio o condición de validación. En el área Permitir: Elija el tipo de dato al que corresponde la columna de la celda que esta configurando. configurando. Las opciones van desde Cualquier valor hasta Personalizada

Ejemplo: En una celda donde se han de digitar sueldos, precios, cantidades, etc. Elija Numero entero ( en ese caso, no permitirá decimales ) Datos: Está relacionado con el tipo de dato que elegí en Permitir anteriormente. En el ejemplo de la imagen a la izquierda, en la sección Datos, Excel pone entre , y habilita dos rectángulos: Mínimo y Mínimo y Máximo. Máximo.

Ejemplo para calificaciones Creando una regla para que, en la NOTA FINAL, solo se puedan digitar números enteros , cuyos , con valores estén entre 40 y 100 , el propósito de que no se digite una calificación ni menor a 40 ni mayor a 100. Y quite la marca de Omitir blancos para que NO DEJEN la celda en blanco.

Página 111

Excel Avanzado +

2. Mensaje de entrada

Este es el mensaje que le aparecerá al usuario desde que entre en la celda donde esta aplicada la regla de validación de datos.

3. Mensaje de error

Este mensaje se mostrará solo cuando se digite un dato que va en contra de la regla de validación de datos, para bloquear y advertir al usuario. Elija uno de

los tres (3) estilos: Cada estilo tiene un símbolo asociado al mensaje: Estilo Símbolo Alto

Advertencia

Información

Página 112


4. Finalmente, haga clic en Aceptar. 5. Para APLICAR esta regla ( configuración, mensaje de entrada y mensaje de error ) a las otras celdas de la misma columna de datos, use el puntero de relleno, y arrástrelo hasta la última celda que debe tener esa regla en dicha columna.

6. Ahora puede digitar con seguridad y confianza en cualquiera de las celdas de la columna de datos Nota Final (H21:H26).

NOTA: Refiérase a este ejemplo en el archivo práctico del Módulo 3 de Excel Avanzado+.

Página 113

Excel Avanzado +

VALIDACION DE DATOS -Creando Listas de Datos Muchos de los datos tipo texto o alfanumericos, que los usuarios deben digitar en sus listados y tablas de datos, pueden ser regulados con la herramienta validacion de datos, a traves de una lista de datos.

Ventajas de Lista de Datos en vs Digitar los Datos Digitar los datos siempre trae mas riesgos de cometer errores al escribir, y por ende, traeria consecuencias que afectarian nuestras futuras consultas y reportes; ya que no es lo mismo manzanas que mansana. La lista de datos reduce practicamente a cero, el error de digitacion, LO UNICO que TENGO que tener PENDIENTE es ELEGIR de la lista EL VALOR CORRECTO, es decir, el valor que corresponde según el registro que estoy digitando.

Creando Lista de Datos para Herramienta Validación de Datos 1. Coloquese en un area de la hoja de calculo al extremo derecho, o en otra hoja de calculo que puedes denominar ‘Configuracion’, para que los datos

que sirven de fuente a su lista no sean accedidos facilmente, o ocultar dicha columna de datos. Tambien puede bloquear los valores de esa lista en la hoja local o en la de configuracion, y asi lograr mayor restriccion y seguridad; 2. En la imagen del ejemplo, hemos elegido la columna AA, desde la fila 17 hasta fila 22. 3. Regrese a la celda donde vamos a crear la regla de validacion de datos, en nuestro ejemplo es la celda D6

4. Haga clic en la pestaña Datos;

Página 114

Antonio P. Peralta Ch. datos y haga clic en Validacion de datos; 5. Observe en el grupo Herramientas de datos y

6. Excel le mostrara este cuadro de dialogo: Elija para cada ficha en validacion de datos, según los valores mostradas en cada imagen.

Página 115

Excel Avanzado +

7. Finalmente haga clic en ACEPTAR 8. Para APLICAR esta regla ( configuración, mensaje de entrada y mensaje de error ) a las otras celdas de la misma columna de datos, use el puntero de relleno, y arrástrelo hasta la última celda que debe tener esa regla en e n dicha columna.

9. Ahora pruébelo haciendo clic en el puntero de la l a lista ( de cada uno de sus artículos.

NOTA: Refiérase a este ejemplo en el archivo práctico del Módulo 5 BONO1 de Excel Avanzado+ Especial.

Página 116

) y elija las unidades


TABLAS DINAMICAS Las tablas dinamicas ( pivot pivot table en table en ingles), son una de las herramientas mas poderosas para comparar , analizar , resumir y y presentar datos datos de forma rapida, atractiva, y lo mejor de todo, nos permite agregar y quitar datos con tan solo arrastrar y soltar (sin afectar los datos de ). origen) y los cambios se muestran al instante (de ahí el calificativo ‘ dinamicas’ ).

Las Fuentes de Datos para Tablas Dinámicas 1. Listado de datos 2. Tabla de datos 3. Fuente externa (como bases de datos)

Las tablas dinámicas son una herramienta con la que puedes resumir todos tus datos y realizar cambios según sea requerido: analizar, consultar, crear informes o realizar gráficos, todo esto de forma dinámica. El autor

Creando una Tabla Dinámica de un Listado de Datos 1) Seleccione todos los datos de su listado (desde los títulos de cada columna hasta el último dato de la última columna).

2) Haga clic en la pestaña Insertar;

Tablas, elija Tabla dinámica; 3) En la sección Tablas,

Página 117

Excel Avanzado + 4) El cuadro de dialogo para crear tabla dinámica, esta dividido en tres (3) áreas: a) Seleccionar los datos que desea

analizar Las opciones para elegir son: -De una tabla local -Un rango de celdas local -Fuente de datos externa Aquí aparece la tabla o el rango de celdas que contienen los datos para analizar En nuestro ejemplo es un rango de celdas local (Desde A5 hasta N18). Observe que dice ListadoParaTablaDinamica! (Ese es el nombre de la hoja donde están esos datos). b) Elegir donde donde desea colocar el informe de tabla dinámica Las opciones para elegir son:

-Nueva hoja de cálculo (Esta es la opción por defecto. Significa una hoja nueva) -Hoja de cálculo c álculo existente UBICACIÓN (Esta significa en la misma hoja donde están los datos, pero en un rango de celdas diferente). Prefiero la nueva hoja de cálculo, para más claridad, menos confusión. c) Elegir si si quiere analizar varias tablas Por ahora, esta opción la vamos a dejar sin si n marcar.

5) Finalmente, haga clic en Aceptar 6) Excel automáticamente crea la hoja nueva ( Hoja1) donde coloca la recién creada tabla dinámica de los datos del rango (A5:N18) de la hoja ListadoParaTablaDinamica. ListadoParaTablaDinamica.

Página 118


Las 5 Partes Claves de una Tabla Dinámica Tal como lo muestra la imagen, para dominar bien una tabla dinamica, hay cinco (5) elementos claves: tabla , que se 1. Los campos de la tabla, corresponden con las columnas de datos que usted selecciono en el rango(A5:N18). Debajo de campos de la tabla aparecen cada uno de ellos, con cajas cuadradas a la izquierda, para que usted los selecciones e inmediatamente aparecerian en la hoja de calculo de la tabla dinamica. 2. Los cuatro (4) elementos siguientes son las areas donde puedo arrastrar los campos según quiero colocarlos para que interactuen y asi analizarlos, consultarlos, crear reportes o graficos dinamicos. Estas areas son: a. Filtros (para consultar) b. Columnas (para colocar en columnas esos campos que arrastre hasta aqui) c. Filas (para colocar en filas esos campos que arrastre hasta aqui); y por ultimo d. Valores, los campos que coloque en esta area, Excel automaticamente les creara una formula para totalizar, usando la funcion SUM (en ingles), SUMA (en espanol), esto lo podemos cambiar configurando ese dato que pongamos, para que en lugar de totalizar, queremos promediar, contar, o tener los valores maximos, entre otras funciones.

Analizando Datos con Tablas Dinámicas Caso 1

Arrastremos campos a las areas de filas, columnas y valores, según queramos ver como interactuan, o según se nos ha requerido algo previamente.

Página 119

Excel Avanzado +

Caso 2: PRESENTANDO DETALLES en LA TABLA DINAMICA Ya en el anterior obtuvimos en segundos, la cantidad o total de articulos vendidos. articulos ; ya que, hay leche entera, Ahora queremos detalles de los tipos de esos articulos; semidescremada, etc., y asi en cada tipo hay mucha variedad, queremos ver el desglose del resumen anterior, y ademas el precio. precio .

CAMBIE La Funcion en Valores en LA TABLA DINAMICA 1. Coloquese sobre el campo que esta en Valores, y haga Clic izquierdo, izquierdo, y elija CONFIGURACION DE CAMPO DE VALOR

2. Elija Promedio y pulse

Aceptar Vea el cambio

Página 120


Caso 3: ACTUALIZANDO DATOS en LA TABLA DINAMICA Algunos dicen (en blanco) porque hay registros en el listado de datos que no tienen ese detalle digitado. Entonces, solicitamos que digiten esto en el listado de datos d atos original. Asuma que ya se hizo. La pregunta p regunta es: ¿Cómo los traigo a mi tabla dinamica? SENCILLO, solo tenemos que ACTUALIZAR : 1. Coloquese en cualquier celda de la tabla dinamica; dinamica ); y haga 2. Observe en la parte superior a la derecha ( Herramientas de tabla dinamica); clic en Analizar;

Datos, y haga clic en Actualizar en Actualizar ; 3. Vea el grupo Datos, 4. Y elija con clic ACTUALIZAR

RESULTADOS Observe detenidamente, el cambio en un instante, al actualizar.

Ejemplos: Mire los que estaban antes en blanco: En Cornflakes, que solo teniamos Integral, y despues que se pidieron esos detalles a nuestros digitadores de fuente de datos, y nosotros actualizamos, ahora tenemos: Integral y Maiz. En Leche, que solo teniamos Entera y Semidescremada, y despues que se pidieron esos detalles a nuestros digitadores de fuente de datos, y nosotros actualizamos, ahora tenemos: Entera, Semidescremada, Soya y Almendra.

Página 121

Excel Avanzado +

Caso 4: SEGMENTADO DATOS en LA TABLA DINAMICA Como vimos al trabajar con tablas, la herramienta de segmentar datos es sumamente util; tambien aquí en tabla dinamica.

1. 2. 3. 4.

Coloquese en cualquier celda de la tabla dinamica;

Arriba a la derecha ( Herramientas de tabla dinamica); dinamica ); haga clic en Analizar; Vea el grupo Filtrar , y haga clic en INSERTAR SEGMENTACION DE DATOS ; Y elija los datos por los que quiere qu iere segmentar, dividir, separar para analizarlos, haciendo clic sobre cada cuadro a cuadro a mano izquierda del nombre del dato; y 5. Finalmente, haga clic en Aceptar

6. Ahora mueva los segmentos para segmentos para hacer consultas multiples interactivas en segundos

7. Excelente! Solo un clic en Leche, y la tabla filtra todo por ese dato. Ahora pulse marca, tipo, y vea los cambios.

Página 122


CAMBIANDO el DISENO de LA TABLA DINAMICA 1. Coloquese en cualquier celda de la tabla dinamica; dinamica ); haga clic en Diseno ; 2. Arriba a la derecha ( Herramientas de tabla dinamica);

3. Vea el grupo Diseno, Diseno, y haga clic en SUBTOTALES;

Diseno, y en el grupo estilos de tabla dinamica, dinamica , 4. En Diseno, clic para ver todos los estilos, y elija uno

Página 123

Excel Avanzado +

GRAFICOS DINAMICOS Los gráficos dinámicos, son dinámicos, son la representacion grafica de una tabla dinamica , y mantienen el mismo dinamismo de la tabla; ya que cualquier cambio en la tabla, sea en dato, en interaccion, en filtros, en consultas, en intercambio de datos de columnas a filas, o en cualquier cambio en la configuracion de las funciones para el calculo de los valores, sera reflejado de inmediato en el grafico dinamico.

Encontrando Los Gráficos Dinámicos 1. En Herramientas de tabla dinamica, haga clic en Analizar, y en el grupo Herramientas, haga clic en Grafico dinamico para elegirlo.

2. Aparecera este cuadro de dialogo:

3. Elija el grafico de su preferencia (para este ejemplo voy a elegir de columnas 3Dimensional).

4. Pulse Aceptar

NOTA: Refiérase a este ejemplo en el archivo práctico del Módulo 5 BONO1 de Excel Avanzado+ Especial. Especial. Página 124


5. Quitemos ahora el precio y veamos el cambio

6. Observe que dentro del grafico hay varios filtros. Abra el filtro de Articulos, y note qu e tiene la posibilidad de filtrar por etiqueta y filtrar por valor al mismo tiempo, en etiqueta elija igual a Leche.

7. Wao! Observe como todo es DINAMICO, cualquier cambio en el grafico afecta la tabla.

Página 125

Excel Avanzado +

BONOS Módulo 2. AUTOMATIZACION DE TAREAS con MACROS    

Macros Creación Modificación Ejecución

Página 126


MACROS Al referirnos al tema de Macros en Excel, debo dejarle claro desde el principio, que los Macros vienen en dos (2) tipos:

1. Son formas automatizadas en Excel para realizar las operaciones rutinarias (del día a día) con una combinación de teclas. Estos no requieren que se active ninguna pestaña o función adicional.

2. Serie de instrucciones en lenguaje de programación VBA (Visual Basic Applications) creando ventanas y subrutinas para solucionar problemas de forma automatizada. Estos requieren que se active la pestaña de programador para desarrollarlos.

Automatizando Las Rutinas Diarias Independientemente del tipo de trabajo que usted realice (como administrador, gerente, secretaria, contable, cajero, entre muchas otras), diariamente necesita repetir ciertos pasos, y este conjunto de pasos es parte de su rutina diaria.

Construya su Primer Macro 1. Haga Clic en la pestaña Vista (View) si no está seleccionada; y

2. Observe en la sección Macros, y haga Clic el símbolo ( justamente debajo de Macros

) de punta de flecha

P á g i n a | 127 de 166

Excel AVANZADO + 3. Al hacer Clic verás las opciones de Macros. Elija Grabar Elija Grabar macro…

NOTA: Otra manera sencilla de lograr esto es haciendo Clic en e n el icono de Grabar macro… en la barra de estado de Excel, justamente debajo de donde donde dice Hoja1 (Sheet1).

4. Le aparecerá la siguiente pantalla:

5. Escriba un Nombre para la macro: Ejemplo: MacroPlantillaTimbrada 6. En Teclado de método abreviado: usted debe pulsar una letra letra (no se permiten números). NOTA: En caso de que la combinación este tomada como en el caso de CTRL + C (utilizada para Copiar), usted podría pulsar en este paso, la tecla SHIFT junto con la C, de tal manera que su atajo sea CTRL+SHIFT+C. 7. En Guardar macro en: usted debe elegir donde guardar la macro. Las opciones son: a. Libro de macros personal Use esta opción cuando desea que la macro esté disponible para ser usada, en cualquier libro y hoja de Excel. b. Libro nuevo Con esta opción, solo estará disponible un libro nuevo. c. Este libro Con esta opción, solo estará disponible para el libro actual. Ahora elija

esta.

P á g i n a | 128 de 166

Antonio P. Peralta Ch. 8. Nos falta solo completar la Descripción. Esto es opcional, es decir, no es obligatorio llenarlo (lo puede dejar vacío y al pulsar aceptar funcionara). NOTA: Pero es muy útil cuando lo llenamos con fin de documentar el trabajo para que otros se orienten al usarlo. 9. Finalmente, pulse el botón Aceptar. 10. Inmediatamente después de pulsar el botón Aceptar, Excel comienza a grabar TODAS las acciones y movimientos que usted haga. 11. En nuestro ejemplo: Nos movemos a la celda A1, y escribimos el título de la compañía: Supermercado Los

Tres Hermanos, SRL. Luego colocados en la celda A2, y escribimos el título del listado o informe: Listado de

Ventas Ahora nos posicionamos en la celda A3, y escribimos el título MES: para que el usuario los digite, y así este macro se adapte a cualquier otro mes. Dejaremos la fila 4 en blanco. Nos ponemos en la celda A5, y digitamos los títulos de cada columna de datos.

A5: Artículos B5: Marca C5: Submarca D5: Unidad E5: Especific. Unidad F5: Cantidad G5: Precio H5: Subtotal I5: ITBIS J5: Total 12. Seleccione (sombree) desde A1 hasta J1 ;

P á g i n a | 129 de 166

Excel AVANZADO + 13. En la pestaña INICIO (HOME), INICIO (HOME), en el grupo Alineación, elija Combinar y centrar

14. Haga lo mismo con los otros dos títulos, seleccione: A2:J2 y elija Combinar y centrar 15. Haga lo mismo con los otros dos títulos, seleccione: A3:J3 y elija Combinar y centrar

16. Seleccione ahora desde A1 hasta J3

17. En Inicio, en el grupo Fuente, elija en el icono de relleno, color azul marino

18. Y ahora manteniéndolo sombreado (A1 a J3), cambie el color de las letras de los títulos a blanco. 19. Y haga lo mismo con el rango de celdas de A5 a J5. P á g i n a | 130 de 166

Antonio P. Peralta Ch. 20. Su trabajo debe verse similar a este:

21. Ahora para FINALIZAR esta MACRO, haga clic en la pestaña Vista, clic en Macros y elija la opción Detener grabación

22. Para probar esta macro, haga clic hoja nueva en blanco;

+

para agregar una

23. Ahora colóquese en A1 y pulse la combinación c ombinación de teclas que le asignó a su macro, para este ejemplo, el atajo asignado a la macro fue: CTRL + SHIFT + C. NOTA: Recuerde que en atajos el signo + significa que estas teclas se pulsan simultáneamente. Ejemplo: Con un dedo pulse la tecla CTRL, (SIN SOLTAR ESTE), con otro dedo pulse SHIFT y SIN SOLTAR ESTE, con otro dedo pulse C. 24. Y saldrá esto:

P á g i n a | 131 de 166

Excel AVANZADO +

ELIMINAR UNA MACRO Para eliminar una macro, necesitara primero ver la lista de los macros existentes en su hoja de calculo, luego elegirlo y hacer clic en eliminar. Veamos como hacerlo.

Pasos para ELIMINAR UNA MACRO 1. Haga clic en la pestaña Vista, clic en Macros y elija la opción Ver macros

2. Le aparecerá la siguiente pantalla: 3. Elija la macro que desea eliminar; NOTA: Puede lograr abrir esta ventana con el siguiente atajo: ALT+F8 (Pulse la tecla ALT) y sin soltarla pulse la tecla tec la F8. Recuerde que en algunas laptops para poder usar las teclas de funciones, necesita pulsar la tecla FN. Entonces el atajo se vuelve para su situacion local en:

ALT+FN+F8.

4.

Y finalmente pulse el boton Eliminar. Al pulsarlo le aparecerá la siguiente caja de diálogo para que usted confirme si en verdad la desea borrar. Si pulso por error el botón Eliminar o si la macro que estaba elegida (marcada) no es la que usted desea borrar, ELIJA el BOTON con la respuesta NO.

5. En caso contrario, Elija el BOTON con la respuesta SI.

Si quiere confirmar que la macro esta borrada (eliiminada). Haga clic de nuevo en Vista, Macros y elija Ver macros, y como notara ya no esta la macro que usted elimino.

P á g i n a | 132 de 166


INTRODUCCION A EXCEL MACROS con VBA (Visual Basic Applications) En esta parte, trabajaremos la introduccion a crear macros usando el lenguaje de programacion VBA ( Visual Basic Applications) ligado a Excel. Nuestro objetivo primordial es crear una ventana de entrada de datos, y usar ese objeto para interactuar con la hoja de calculo de Excel. En esta sección aprenderemos: Activar la pestaña de Programador (Developer); Acceder a Visual Basic Applications desde Excel; Identificar y crear objetos: Userform, Label, TextBox y Buttons; Identificar y usar propiedades para cada objetos; Crear una interfaz grafica (una ventana) con VBA; Escribir el codigo para los objetos TextBox y Buttons; Interpretar los codigos escritos; y finalmente Ejecutar, usar y corregir su programa Macro de Entrada de Datos. • • • • • • • •

P á g i n a | 133 de 166

Excel AVANZADO +

ENCONTRANDO A VBA (VISUAL BASIC APPLICATIONS) en EXCEL La configuracion que viene por defecto en Microsoft Excel, no trae activada la pestaña de

o Developer en en ingles). Para poder acceder al Desarrollador (tambien llamada: Programador o lenguaje de programacion VBA, es necesario activar dicha pestaña . Veamos.

ACTIVANDO LA PESTAÑA DESARROLLADOR (DEVELOPER) en EXCEL 1. Haga clic en Archivo; 2. Le aparecerá la siguiente pantalla:

3. Haga clic en Opciones

4. Le mostrará esta pantalla:

5. Haga clic en Personalizar cinta de opciones

P á g i n a | 134 de 166


6. Le mostrará esta pantalla:

7. Haga clic en la pestaña Desarrollador para activarla

8. Pulse el botón Aceptar 9. Y ahora si aparecerá activada la pestaña Desarrollador en Excel:

10. La razón de activar la pestaña Desarrollador, es encontrar a VBA (Visual Basic). Observe el primer elemento y haga clic en el para comenzar a programar.

P á g i n a | 135 de 166

Excel AVANZADO +

COMENZANDO A PROGRAMAR con VBA en EXCEL

Otra forma de acceder e l editor de VBA (Visual Basic Applications), es pulsar la combinacion (el

atajo ALT+F11), pulse la tecla ALTERNA ( ALT) y sin soltarla, pulse tambien la tecla de funcion (F11), entonces le saldra lo siguiente:

Las Ventanas de Proyectos y de Propiedades en VBA La Ventana

Ex lora lorado dorr de Pro Pro ectos ctos

P á g i n a | 136 de 166

La Ventana

Propiedades


GUARDANDO UNA HOJA DE CALCULO en formato MACROS VBA Antes de comenzar a escribir instrucciones en Visual Basic Applications para programar en Excel, un paso importante, es modificar el tipo de archivo del libro de Excel (que por defecto defecto es .XLSX) y elegir a la hora de guardarlo: libro habilitado para macros(.XLSM) .

P á g i n a | 137 de 166

Excel AVANZADO +

CREANDO UNA VENTANA DE ENTRADA DE DATOS 1. En la barra de herramientas, haga clic en el segundo (2do) icono ; 2. Le presentará tres (3) opciones, haga clic en la primera: UserForm; 3. Le mostrará esta pantalla:

herramientas , Observe el cuadro de herramientas, con este podra crear los objetos graficos y colocarlos en la ventana para completar el diseno de su ventana de entrada de datos.

Los Iconos del Cuadro de Herramientas objetos que formaran la ventana de Para crear los objetos que entrada de datos, sera suficiente con dominar solo cuatro (4) elementos del cuadro de herramientas, herramientas , veamos:

Icono

Espannol

Ingles

Funcion

Etiqueta

Label

Usaremos esta herramienta para escribir titulos de la ventana y de cada datos.

TextBox

Tendremos cuadros de texto para los dat os que el usuario debe digitar en la ventana de datos, y que luego estaran en la hoja de Excel.

ComboBox

Con el cuadro combinado pondremos una lista de datos fijos para que el usuario no tenga que digitarlos solo elegirlos.

CommandButton

Finalmente, el boton de comando, nos permitira darle una instrucción que afecte la ventana completa, como Insertar o Terminar.

Cuadro de texto

Cuadro combinado

Boton de comando

P á g i n a | 138 de 166


Diseñando La Ventana de Entrada LAS ETIQUETAS (LABELS) Etiqueta (Label): Son usadas para escribir titulos, rotular, nombrar, identificar los datos que componen nuestra ventana. 1. Vaya al cuadro de herramientas, y haga clic en Etiqueta; 2. Coloquese en Coloquese en la ventana (UserForm1) y haga clic o dibuje un rectangulo;

3. Observe la ventana Propiedades, cambia dependiendo del objeto que esta seleccionado. Ahora seleccionado. Ahora esta esta seleccionado Label1. Cambie estas propiedades: Propiedad

Significado

Funcion

Name

Nombre interno

Esta propiedad nos ayudara a identificar el objeto y a diferenciarlo de otros cuando estemos en modo codigo. codigo.

Caption

Mensaje de texto publico

Caption es la propiedad que se relaciona con el texto, que presenta el objeto en la interfaz de usuario.

Font

Fuente (tipo de letra)

En esta propiedad puedes cambiar el tipo de letra, tamano, color y efectos con que se mostrara en la pantalla.

4. Haga clic en la propiedad Name, Name, y escriba lblCodigo NOTA: Para nombrar la propiedad usaremos el siguiente patron: las tres primeras letras en minuscula para identificar el tipo de objeto, luego con la primera letra en mayuscula escribe el nombre del objeto. Ejemplo: lblCodigo. Cuando veamos este nombre en el modo codigo, sabremos que se trata de un label, por iniciar con lbl, y que es el del Codigo del producto. Observe que aunque cambiamos el name, name, la pantalla sigue mostrando: Label1. Para cambiar esto, tenemos que ir a la propiedad Caption.

P á g i n a | 139 de 166

Excel AVANZADO + 5. Haga clic en la propiedad Caption, Caption, y escriba Codigo NOTA: La diferencia entre la propiedad Name y la propiedad Caption, es que lo que escribimos en Name es su nombre del objeto, con el cual nos referimos al objeto para llamarlo, mientras Caption es el texto que el usuario que llena el formulario ve en la pantalla, para indicarle cual dato es el que va a digitar. Observe que al cambiar el Caption, Caption , la pantalla cambia y muestra en lugar de Label1, Codigo. Codigo.

6. Seleccione la propiedad Font , y haga clic en los puntos suspensivos (…) 7. Le aparecerá el siguiente cuadro de diálogo:

8. Ahora haga los siguientes cambios: Font style (Estilo de letra): Bold (Negrita). Size(tamano): 10 Cuando haga estos cambios, entonces pulse el boton OK.

9. Se verá asi:

P á g i n a | 140 de 166

Antonio P. Peralta Ch. seleccionarla. 10. Ahora haga clic en la etiqueta codigo que esta en la ventana, para seleccionarla.

11. Teniendo seleccionada la etiqueta Codigo, haga clic derecho con el Mouse y Elija Copiar NOTA: Ya que necesitaremos varias etiquetas, similares a Codigo en las propiedades, usaremos copiar copiar para para reproducir todas las etiquetas que necesitamos, necesitamos, y de esa manera ser mas productivos; ya que solo cambiaremos en lo que difieren.

Mouse en una area vacia de la ventana, y elija Pegar. 12. Ahora haga clic derecho con el Mouse en NOTA: Repita este paso (el paso (el 12), tantas veces como etiquetas necesite. necesite. Recuerde guiarse de la fila de nombre de datos que hizo en el macro automatico. Necesitara siete (7) mas. Código Descripción Cantidad Unidad Precio Subtotal Itbis Total

Este sera el resultado de hacer varios pegar. Se acumularan uno encima del otro. Entonces, usted tendra que seleccionarlo, arrastrar y soltarlo en otra posicion (direccion) dentro de la ventana.

P á g i n a | 141 de 166

Excel AVANZADO + 13. Haga clic en la ventana, y pulse la combinacion CTRL + A para seleccionar todas las etiquetas:

14. Siga los pasos en la imagen para alinear todas estas etiquetas: a. Clic en Formato b. Elija Alinear c. Clic en Izquierda Ahora todas las etiquetas deben estar a la izquierda

15. Observe el resultado de alinear:

P á g i n a | 142 de 166

Antonio P. Peralta Ch. 16. Ahora haga clic en la 2da etiqueta Codigo, y modifique las propiedades: Nota: Recuerde el patron, en Name: las tres primeras letras identifican el tipo de objeto, luego con la primera en mayuscula el nombre del objeto. Y en Caption, ponga el nombre del dato, con la primera en mayuscula. Name:lblDescripcion Caption: Descripcion

17. Lo mismo que hicimos en el e l paso anterior, lo hara con cada etiqueta de la tercera (3ra) en adelante, aplicandolo de la misma manera: Etiqueta

Name

Caption

3ra

lblCantidad

Cantidad

4ta

lblUnidad

Unidad

5ta

lblPrecio

Precio

6ta

lblSubtotal

Subtotal

7ma

lblItbis

Itbis

8va

lblTotal

Total

P á g i n a | 143 de 166

Excel AVANZADO +

Diseñando La Ventana de Entrada LOS CUADRO DE TEXTOS (TEXTBOX) Cuadro de texto (TextBox): Son usadas para digitar o entrar datos. 1. Haga clic en la ventana UserForm1 y le saldra el Cuadro de herramientas. herramientas . Elija Cuadro de texto .

2. Ahora dibujelo al lado de la etiqueta Codigo.

NOTA: Ahora esta Ahora esta seleccionado el objeto TextBox. La propiedad que cambiaremos es (Name).

Propiedad

Significado

Funcion

Name

Nombre interno

Esta propiedad nos ayudara a identificar el objeto y a diferenciarlo de otros cuando estemos en modo codigo. codigo.

3. Haga clic en la propiedad Name, Name, y escriba txtCodigo NOTA: Para nombrar la propiedad usaremos el siguiente patron: las tres primeras letras en minuscula para identificar el tipo de objeto, luego con la primera letra en mayuscula escribe el nombre del objeto. Ejemplo: txtCodigo. Cuando veamos este nombre en el modo codigo, sabremos que se trata de un textbox (objeto utilizado usado para digitar dato), por iniciar con txt, y que es el del Codigo del producto.

4. Seleccione el cuadro de texto del Codigo, y haga clic con el boton derecho del Mouse y elija Copiar.

P á g i n a | 144 de 166

Antonio P. Peralta Ch. 5. Seleccione la ventana. y haga

clic con el boton derecho del Mouse y elija Pegar.

NOTA: Pegue otros seis (6) TextBox. Para completar las entradas de cada dato.

6. Seleccione todos los cuadros de texto para alinearlos.

7. Haga clic en Formato , seleccione Alinear y elija

Derecha

8. Observe los resultados de la alineacion de todos los cuadros de textos a la derecha :

P á g i n a | 145 de 166

Excel AVANZADO + 9. Ahora haga clic en el 2do cuadro de texto, y modifique la propiedad name: Nota: Recuerde el patron, en Name: las tres primeras letras identifican el tipo de objeto, luego con la primera en mayuscula el nombre del objeto. Name:txtDescripcion

10. Lo mismo que hicimos en el paso anterior, lo hara con cada cuadro de texto del tercero (3ro) en adelante, aplicandolo de la misma manera: TextBox

Name

3ro

txtCantidad

4to

txtUnidad

5to

txtPrecio

6to

txtSubtotal

7mo

txtItbis

8vo

txtTotal

11. Haga clic en la ventana UserForm y cambiele tambien las propiedades Name y Caption: Name: frmEntradaDeDatos Name: frmEntradaDeDatos Caption: Sistema de Entrada de Datos

P á g i n a | 146 de 166

Antonio P. Peralta Ch. ventana de Entrada de Datos, para agregarle un 12. Ahora aumentemos el tamaño de la ventana de titulo en la parte superior, y los botones de comandos al final. Nota: Coloquese en una de las esquinas y esquinas y cuando vea el puntero de doble punta de flecha, flecha, agarre el boton izquierdo del Mouse y extiendalo, aumente el tamaño, hasta tener el espacio requerido para el titulo y los botones de comando.

13. Antes de crear el titulo, centremos mejor el contenido que contenido que tenemos. Seleccionelo todo :

14. Coloquese en el borde de un label o de un textbox , agarre el boton izquierdo y mueva todo al centro de la ventana

15. Ahora agregue un label en en la parte superior para el titulo de la ventana de entrada de datos. Modifique el Name: lblTitulo Caption: Entrada de Datos Font : Tahoma, Bold, 16 Recuerde ampliar el cuadro del label

para que el texto texto se vea completo completo y centrelo. centrelo.

P á g i n a | 147 de 166

Excel AVANZADO + 16. Veamos los resultados:

17. Elija desde el cuadro de herramientas command button ( boton de comandos), y dibuje un rectangulo en la parte inferior de la ventana.

18. Modifique las propiedades name, caption y font de cada boton.

Propiedad

Insertar

Terminar

Name

btnInsertar

btnTerminar

Caption

Insertar

Terminar

Font

Tahoma / Bold / 10

P á g i n a | 148 de 166


Codificando con VBA La Ventana de Entrada PROGRAMANDO LOS CUADRO DE TEXTOS (TEXTBOX) Codificar: En este contexto, esta palabra, se refiere a programar, escribir codigos conforme a las reglas, sintaxis y patrones de un lenguaje de programacion, en este caso, VBA. 1. Para acceder a la ventana donde codificamos en VBA, haga doble clic con el boton izquierdo del Mouse,

dentro del cuadro de texto de Codigo. Nota: Private Sub… End Sub Significa en VBA: Sub rutina privada. Reune un conjunto de sentencia dentro de ella para solucionar un problema. Al ser privada solo es accequible dentro del mismo modulo.

txtCodigo_Change() Significa en VBA: txtCodigo: Caja de texto (TextBox) para digitar datos, en este caso, se espera que el usuario digite el Codigo ( aunque pudiera digitar cualquier cosa que este objeto permita, lo cual cual es texto, numeros y simbolos simbolos ), esto podemos regularlo y limitarlo a un solo tipo de datos con otras instrucciones VBA. _Change(): Es _Change(): Es un evento que notifica al sistema un cambio en un objeto ob jeto en VBA, en este caso, un cambio en el objeto TextBox llamado txtCodigo. Range(“A4”).Select

Significa en VBA: Range(“A4”): La Sintaxis de Range es: RANGE(CELL1, CELL2…).

Siempre el formato de las funciones es: NombreDeLaFuncion junto con Parentesis, y dentro de los parentesis recibe el parametro o argumento de datos que usara para devolver una respuesta, valor o datos. La funcion Range significa rango. rango. Y el rango se refiere a las celdas de una hoja de calculo en Excel. Recuerde: A4 depende de cual es la celda debajo del titulo Codigo en su hoja de calculo. Observe: Use A7

Siempre que especifiques valores de textos o alfanumericos, deben ser colocados entre doble comillas, por eso, el valor que pasamos dentro de Range va entre comillas; ya que es letra ) con una fila( numero numero ). una celda, y una celda es la combinacion de una columna( letra .Select: Esta instrucción junto a Range(“A4”) selecciona la celda especificada. Como cuando haces clic con el Mouse o te mueves con las teclas de flechas a una celda.

Nota: ActiveCell.FormulaR1C1 ActiveCell.FormulaR1C 1 = txtCodigo ActiveCell Significa en VBA: Celda Activa. Que Activa. Que en este caso, es la celda especificada en Range, que es A4. NOTA: Para mas detalles, uso, desarrollo, aplicación y sistemas en VBA, escribanos por nuestros cursos especializados en VBA: Excel Macros + VBA ; y Sistemas Completos con Excel Macros+VBA .

P á g i n a | 149 de 166

Excel AVANZADO +

FormulaR1C1 Significa en VBA: Al estar unidas por el punto, Formula, es una propiedad. Esta propiedad. Esta devuelve el valor asignado, sea una formula, un texto, un numero, una constante, etc. R1C1: Se refiere a Row(Fila) y Column(Columna). = Signo de igualdad, que se refiere a una asignacion (formula, texto, numero), para que sea colocada en la celda activa, la cual, en este caso es A4.

txtCodigo Es el nombre del objeto TextBox que contiene el valor (que se le digito), y que ahora se esta colocando en la celda activa de forma automatica. 2. De la misma forma, que lo hicimos con el cuadro de texto txtCodigo, lo vamos a hacer con todos los TextBox, excepto, con en los que se hacen calculos (Subtotal ( Subtotal , Itbis y Itbis y Total ). ). Seleccione el siguiente

TextBox: txtDescripcion Y haga doble clic en este. Y copie el siguiente codigo, lo unico que variara es la celda a donde lo pondra. 3. Usando los mismos pasos y el codigo que le suplimos con este manual, complete el codigo VBA para cada TextBox de la ventana de Entrada de Datos. TextBox

Codigo VBA Private Sub txtCantidad _Change() Range("C04").Select

Observe la función VAL() usada en los

ActiveCell.FormulaR1C1 = Val(txtCantidad)

TextBox que van a recibir valores numéricos o van a realizar cálculos.

txtCantidad End Sub

Private Sub txtPrecio _Change()

txtPrecio

Ya que todos los datos que reciben los cuadros de textos son asumidos como ‘textos’, es necesario usar una función

como VAL() para convertir esos

Range("D04").Select

valores en números ‘verdaderos’, es

ActiveCell.FormulaR1C1 = Val(txtPrecio)

decir, que puedan ser usados para calcular, y no obtengamos un error.

txtTotal = Val(txtPrecio) * Val(txtCantidad) End Sub Private Sub txtTotal _Change() _Change() Range("E04").Select

txtTotal ActiveCell.FormulaR1C1 = Val(txtTotal) End Sub

P á g i n a | 150 de 166


Codificando con VBA La Ventana de Entrada PROGRAMANDO LOS BOTONES DE COMANDO (COMMAND BUTTON) Un boton de comando: A traves de este este podemos podemos ejecutar un conjunto conjunto de instrucciones a traves de un evento como el Click, y asi ejecutar una accion. El objetivo del boton Insertar , es agregar una fila, un registro nuevo para la ventana Entrada de Datos y la hoja de calculo. 1. Para acceder al evento Click del boton Insertar y codificar en VBA, haga doble clic con el boton

izquierdo del Mouse en el boton Insertar .

2. Digite el siguiente codigo VBA en la subrutina del evento Click del boton Insertar

Selection.EntireRow.Insert Selection Significa Seleccione EntireRow Significa Significa La Fila Entera Insert Significa Significa que Agregue o Inserte

En otras palabras, selecciona la fila entera e inserta una fila. txtCodigo = Empty txtDescripcion = Empty txtPrecio = Empty txtCantidad = Empty txtTotal = Empty Significa Aquí usted ve el nombre de cada TextBox (asumimos que tiene un dato digitado), y ahora queremos dejarlos a todos (a cada uno) limpios (eso es lo que significa Empty), en blanco, sin datos; ya que estamos insertando una fila nueva, para que el usuario digite un registro nuevo.

txtCodigo.SetFocus Significa SetFocus es una propiedad que hace que el cursor se coloque en el TextBox al que estamos aplicando esta propiedad. En este caso, queremos que despues de insertar el registro, y limpiar cada dato, el cursor sea colocado en el TextBox del Codigo, cuyo name es txtCodigo. Por eso, la instrucción es: txtCodigo.SetFocus

P á g i n a | 151 de 166

Excel AVANZADO + oc upa El objetivo del boton Terminar T erminar, va mas alla de cerrar la ventana Entrada de Datos; y se ocupa tambien de quitarla de la memoria, de tal forma, que no este activa. 1. Para acceder al evento Click del boton Terminar y codificar en VBA, haga doble clic con con el

boton izquierdo del Mouse en el boton Terminar .

2. Digite el siguiente codigo VBA en la subrutina del evento Click del boton Terminar

Unload frmEntradaDatos frmEntradaDatos Unload Significa No cargar Esta instrucción cerrara la ventana cuyo nombre se indique a su lado y eliminara de la memoria todo rastro de esta y de sus datos. frmEntradaDatos Este es el nombre del objeto al que queremos aplicarle el Unload. En este caso, es el nombre que en la propiedad name le asignamos a la ventana UserForm1.

Crea un sistema completo de Contabilidad, de Facturacion, de Inv entario, etc… ¿Te interesa? Inscribete en: Sistemas Completos con Excel Macros+VBA. Y crea sistemas personalizados con Excel VBA como un experto.

P á g i n a | 152 de 166


Llamando Un Programa VBA Desde La Hoja de Calculo CODIFICANDO EL BOTON ENTRADA DE DATOS (DESDE LA HOJA DE CALCULO) Boton de comando (control ActiveX): Lo usaremos para llamar el programa VBA, desde la hoja de calculo de Excel, es decir, la ventana de Entrada de datos que creamos. 1. Para acceder haga clic Desarrollador, y elija Modo Diseno 2. Estando en Desarrollador, seleccione Insertar y en el grupo Controles ActiveX , elija Boton de comando (control ActiveX)

3.

Dibujelo en el area de la hoja de calculo que sea mas accequible para el usuario.

4. Ahora haga doble clic con el boton

izquierdo del Mouse en el boton que acabas de crear , eso le llevara a la zona de Visual Basic para Aplicaciones

5. Modifique las propiedades Name y Caption de este boton Name: btnEntradaDeDatos Caption: Entrada de Datos

P á g i n a | 153 de 166

Excel AVANZADO + 6. Ahora escriba el codigo

7. Ahora elija el nombre del boton de la lista de objetos: btnEntradaDeDatos

8. Y escriba el codigo VBA para que al pulsar este boton desde la hoja de calculo, llame la ventana de Entrada de Datos

9. Ahora guarde todo y vamos a probarlo

P á g i n a | 154 de 166

Antonio P. Peralta Ch. 10. Para ejecutarlo, haga clic en la ventana Entrada de Datos y presione este boton O pulse la tecla de F5

11. Y ya esta listo

12. Ahora para usarlo solo debe pulsar el boton desde la hoja de calculo, y llenar los datos de la ventana de Entrada de Datos y automaticamente se agregaran.

P á g i n a | 155 de 166

Excel AVANZADO +

PARA LOGRAR MEJORES RESULTADOS Conviertase en Un Profesional de Macros + VBA Recuerde que lo que acabamos de ver es un BONO, y esto ha sido a modo de introduccion. Si desea desarrollar controles automaticos y seguros, profesionalizar su diseno de pantallas, combinar macros con tablas dinamicas, trabajar con datos completos, entre otras otras cosas, inscribase ahora en el curso Excel Macros + VBA.

Con nivel basico, intermedio y avanzado en VBA.

P á g i n a | 156 de 166


P á g i n a | 157 de 166

Excel AVANZADO +

PARA LOGRAR MEJORES RESULTADOS Desarrolle Sistemas Completos con Excel VBA Si desea desarrollar sistemas completos, personalizados, entre otras opciones en VBA, le recomiendo inscribirse en el curso Sistemas Completos con Excel Macros+VBA.

P á g i n a | 158 de 166


Finalmente, en el nivel avanzado+, se puede apreciar un panorama lleno de posibilidades increíbles para usted, para su negocio e inclusive para la proyección haciendo uso de la identificación de patrones, lo cual le llevará a una toma de decisión más inteligente, no importa cuál sea el tipo ni el tamaño de su negocio, este nivel lo impulsará, controlando sus usuarios, consultando a otro nivel y dominando las famosas tablas dinámicas y gráficos dinámicos. ¿Qué debes hacer después de aquí?

Puedes tomar otro de los entrenamientos y materiales que el profesor Peralta ha desarrollado, como son: • • •

Inteligencia de Negocios (Business Negocios (Business Intelligence: Excel + Power BI), Macros + VBA , Sistemas Contables Completos con VBA Excel , Excel , Sistema de Facturación e Inventario (esta es una serie de automatización con Programación de Macros en Excel con el

lenguaje de programación VBA -Visual Basic for Applications-). • • •

Excel Financiero, Financiero, Certificación Internacional en Excel , Entre otros. Lo cual lo convertirá a usted en todo un profesional, un experto en Excel.

Por favor, déjenos sus comentarios y/o sugerencias sobre cómo le pareció este entrenamiento, y cómo podemos mejorar este entrenamiento a: [email protected] Para más detalles e información contáctenos. Muchísimas gracias

P á g i n a | 159 de 166

Excel AVANZADO +

ANEXOS Especificaciones y límites de Excel 2016 y Excel 2013 Tal y como lo presenta la compañía Microsoft en el siguiente enlace: https://support.office.com/es-es/article/Especificaciones-y-l%25C3%25ADmites-de-Excel1672b34d-7043-467e-8e27-269d656771c3?ui=es-ES&rs=es-ES&ad=ES&fromAR=1

Especificaciones y límites de las hojas de cálculo y de los libros Característica

Límite máximo

Libros abiertos

En función de la memoria disponible y los recursos del sistema

Tamaño de hoja

1.048.576 filas por 16.384 columnas

Ancho de columna

255 caracteres

Alto de fila

409 puntos

Saltos de página

1.026 horizontal y vertical

Número total de caracteres que puede contener una celda

32.767 caracteres

Caracteres en un encabezado o un pie de página

255

Hojas en un libro

Limitado a la memoria disponible (el valor predeterminado es 1 hoja)

Colores en un libro

16 millones de colores (32 bits con acceso completo al espectro de colores de 24

Vistas con nombre en un libro

En función de la memoria disponible

Formatos o estilos de celdas distintos

64.000

Estilos de relleno

256

Grosor y estilos de línea

256

Tipos de fuentes distintas

1.024 fuentes globales disponibles; 512 para cada libro

Formatos de número en un libro

Entre 200 y 250, según el idioma de la versión de Excel instalada

Nombres en un libro


Ventanas en un libro


Hipervínculos en una hoja de cálculo

66.530 hipervínculos

Paneles en una ventana

4

Hojas vinculadas


P á g i n a | 160 de 166

Antonio P. Peralta Ch. Característica

Límite máximo

Escenarios

En función de la memoria disponible, un informe de resumen sólo muestra los pr 251 escenarios

Celdas cambiantes en un escenario

32

Celdas ajustables en Solver

200

Funciones personalizadas


Escala de zoom

del 10 % al 400 %

Informes


Criterios de ordenación

64 combinados en una única operación; ilimitado en operaciones de ordenación secuenciales

Niveles de deshacer

100

Campos en un formulario

32

Parámetros del libro

255 parámetros por libro

Elementos mostrados en listas de filtros desplegables

10.000

Celdas discontinuas que pueden seleccionarse

Celdas 2,147,483,648

Límites máximos de almacenamiento en memoria y tamaños de archivos para libros del modelo de datos

El entorno de 32 bits está sujeto a una limitación de 2 gigabytes (GB) de espacio direcciones virtuales, compartido por Excel, el libro y los complementos que se ej el mismo proceso. La parte de dirección del modelo de datos podría alcanzar de 700 megabytes (MB), pero podría ser inferior si se cargan otros mod elos de dato complementos. El entorno de 64 bits no está sujeto a ninguna limitación estricta, sino que el tam libro solo se ve limitado por los recursos disponibles en el sistema y la memoria. Excel 2016 ofrece la función Detección de direcciones largas, que permite que la de 32 bits de Excel 2016 use el doble de memoria cuando los usuarios trabajan e sistema operativo Windows de 64 bits. Para obtener más información, vea Cambi función Detección de direcciones largas de Excel Excel.. NOTA: Agregar tablas al modelo de datos aumenta el tamaño de archivo. Si no t

previsto crear relaciones del modelo de datos complejas usando numerosos oríg datos y tipos de datos en su libro, desactive la casilla Agregar estos datos al m datos cuando importe o cree tablas, tablas dinámicas o conexiones de datos. Para obtener más información, consulte Especificación y límites del modelo de d

P á g i n a | 161 de 166

Excel AVANZADO +

Especificaciones y límites de los cálculos Característica

Límite máximo

Precisión numérica

15 dígitos

Número negativo más bajo permitido

-2.2251E-308

Número positivo más bajo permitido

2.2251E-308

Número positivo más alto permitido

9.99999999999999E+307

Número negativo más alto permitido

-9.99999999999999E+307

Número positivo más alto permitido mediante fórmula

1.7976931348623158e+308

Número negativo más alto permitido mediante fórmula

-1.7976931348623158e+308

Longitud del contenido de una fórmula

8.192 caracteres

Longitud interna de la fórmula

16.384 bytes

Iteraciones

32.767

Matrices en una hoja


Rangos seleccionados

2.048

Argumentos en una función

255

Niveles anidados de funciones

64

Categorías de funciones definidas por el usuario

255

Número de funciones de hoja de cálculo disponibles

341

Tamaño de la pila de operandos

1.024

Dependencia entre hojas de cálculo

64.000 hojas de cálculo pueden hacer referencia a otras h

Dependencia de fórmulas de matriz entre hojas de cálculo


Dependencia de áreas


Dependencia de áreas en cada hoja de cálculo


Dependencia en una sola celda

4.000 mil millones de fórmulas pueden depender de una

Longitud del contenido de celdas vinculadas de libros cerrados

32.767

Primera fecha permitida en un cálculo

1 de enero de 1900 (1 de enero de 1904, si se utiliza la fec sistema)

Última fecha permitida en un cálculo

4/3/07

Período de tiempo máximo que puede escribirse

9999:59:59

P á g i n a | 162 de 166


Especificaciones y límites de los gráficos Característica

Límite máximo

Gráficos vinculados a una hoja


Hojas a las que hace referencia un gráfico

255

Series de datos en un gráfico

255

Puntos de datos en una serie de datos para gráficos 2D


Puntos de datos en una serie de datos para gráficos 3D


Puntos de datos en todas las series de un gráfico


Especificaciones y límites de los informes de tablas dinámicas y gráficos dinámicos Característica

Límite máximo

Informes de tabla dinámica en una hoja


Número de elementos únicos por cada campo

1.048.576

Campos de fila o columna en un informe de tabla dinámica


Filtros de informe en un informe de tabla dinámica

256 (puede estar en función de la memoria d

Campos de valores en un informe de tabla dinámica

256

Fórmulas de elementos calculados en un informe de tabla dinámica


Filtros de informe en un informe de gráfico dinámico

256 (puede estar en función de la memoria d

Campos de valores en un informe de gráfico dinámico

256

Fórmulas de elementos calculados en un informe informe de gráfico dinámico dinámico

En función función de la memoria memoria disponible

Longitud del nombre MDX para un elemento de tabla dinámica

32.767

Longitud de una cadena de tabla dinámica relacional

32.767

Elementos mostrados en listas de filtros desplegables

10.000

P á g i n a | 163 de 166

Excel AVANZADO +

Especificaciones y límites de los libros compartidos Característica

Límite máximo

Usuarios que pueden abrir y compartir simultáneamente un libro compartido

256

Vistas personales en un libro compartido


Días que se mantendrá el historial de cambios

32.767 (el valor predeterminado es 30 días)

Libros que pueden combinarse a la vez


Celdas que pueden resaltarse en un libro compartido

32.767

Colores utilizados para identificar los cambios realizados por diferentes usuarios si el resaltado de cambios está activado

32 (cada usuario se identifica mediante un color; los c realizados por el usuario actual se resaltarán en color marino)

Tablas de Excel en un libro compartido

0 (cero) NOTA: No se puede compartir un libro que contiene

tablas de Excel.

P á g i n a | 164 de 166


FUENTE (Bibliografía / Web grafía) Especificaciones y límites de Excel 2016 y Excel 2013 https://support.office.com/es-es/article/Especificaciones-y-l%25C3%25ADmites-de-Excel1672b34d-7043-467e-8e27-269d656771c3?ui=es-ES&rs=es-ES&ad=ES&fromAR=1

P á g i n a | 165 de 166

Excel AVANZADO +

En

el experto en Microsoft Excel© Antonio

P. Peralta Ch., entrega un material completo y didáctico para dominar los niveles Intermedio y Avanzado… ¿Sabes la diferencia entre una formula relativa y una absoluta? ¿Cómo puede usted aplicar la identificación de patrones a su trabajo? ¿Crear tablas y gráficos dinámicos en Excel?

Con la ayuda de Dios, con este material y usted poniéndolo en práctica, todas esas preguntas de arriba y muchas más quedarán resueltas de forma práctica. Este comprensivo y didáctico material, está orientado a resultado, asumiendo que el prerequisito de un excelente nivel básico, ya el participante lo ha alcanzado previamente. A partir partir de un fundamento fundamento tipo roca, roca, el profesor Peralta te lleva lleva a trabajar una segunda segunda planta robusta, dominando las encuestas, las tabulaciones de datos, las estadísticas, las poderosas fórmulas de referencias referencias absolutas, entre otros muchos muchos temas más, logrando así así un sólido nivel Intermedio en Excel. Finalmente, en el nivel avanzado, se puede apreciar un panorama lleno de posibilidades increíbles para usted, para su negocio e inclusive para la proyección haciendo uso de la identificación de patrones, lo cual le llevará a una toma de decisión más inteligente, no importa cuál sea el tipo ni el tamaño de su negocio, este nivel lo impulsará, controlando sus usuarios, consultando a otro nivel y dominando las famosas tablas dinámicas y gráficos dinámicos. ¿Qué debes hacer después de aquí? Puedes Puedes tomar otro de los entrenamientos y materiales que el profesor Peralta ha desarrollado, como son: Macros + VBA , Sistemas Contables Completos con VBA Excel , Sistema de Facturación e Inventario Inventario (esta es una serie de

automatización con Programación de Macros en Excel con el lenguaje de programación VBA -Visual Basic for Applications-). Excel Financiero, Financiero, entre otros. Lo cual lo convertirá a usted en todo un profesional, un experto en Excel. Antonio P. Peralta Ch. Es un experto en Microsoft Excel©, certificado internacionalmente por Microsoft (Microsoft Office© Specialist Excel 2013) , con más de una década de experiencia práctica y docente en esta

poderosa herramienta. Con formación profesional en Informática, Docencia, Diseño Gráfico, Diseño Web, entre otras. Fundador de ExcelDesdeCero.com y el creador inédito de una serie de cursos y diplomado sobre Microsoft Excel©. Vive en República Dominicana junto a su amada esposa Teresa Rodríguez y sus tres hijos Anthony, Esther y Sarah. Usted puede encontrar recursos gratuitos de calidad en el blog de www.ExcelDesdeCero.com y también en nuestro Facebook : https://www.facebook.com/ExcelDesdeCero/

P á g i n a | 166 de 166

AP Excel 2016 AVANZADO Plus -Libro Completo .pdf

Recommend Documents