BLOQUE 6

Automatización de cálculos ejecutivos.

Que el estudiante aprenda a utilizar de manera óptima la hoja de cálculo con macros.

EXCEL BÁSICO

PARTES DE LA HOJA DE CÁLCULO

Las hojas de cálculo estan formadas por filas y columnas que forman cuadrados llamados celdas.01de 03

Rótulos de columnas

Filas, columnas y celdas — Imprimir pantalla Microsoft Excel Y. Cuesta

Las columnas en las hojas de cálculo se determinan con letras empezando por la A, sigue la B, C, D,…02de 03

Rótulos de filas

Filas hoja Excel — Imprimir pantalla Microsoft Excel Y. Cuesta

Las filasen las hojas de cálculo se determinan con números empezando por el 1, sigue el 2, 3, 4,…03de 03

Celda

Las celdas son los cuadrados en los que se divide la hoja de cálculo, es la intersección entre fila y columna.

Así la primera celda, que se sitúa en la columna A y en la fila 1 se la denomina A1.

Se dirá que la celda esta activa cuando tienes el cursor del ratón posicionado en ella, como puedes ver en la imagen esto significa que la celda esta enmarcada por los cuatro lados con un borde.

Cuando una celda esta activa los datos que introduces se sitúan en dicha celda.

INTRODUCE DATOS EN TU HOJA DE CÁLCULO

Es posible introducir datos en tu hoja de cálculo Excel de forma rápida y fácil, para ello has de usar los formularios de datos.

De esta manera los datos se introducen de forma más eficaz incluso aunque no se tengan muchos conocimientos de Excel.

Como crear y usar un formulario

En el ejemplo harás una base de datos para registrar información sobre los clientes de la empresa para hacer una acción comercial con el correo electrónico.

En primer lugar abre una hoja de cálculo Excel, en otras hojas la sistemática es muy similar por ejemplo puedes leer Google Docs formularios.
Escribe en la celda A1 Nombre; en B1 Apellido; en C1 Teléfono; y en D1 Email.
Selecciona el rango desde A1 a D1.
En Excel 2003 pulsa en el Menú (arriba en la parte central), y luego en Formulario.
En Excel 2010 y 2007, pulsa en la Cinta de opciones el icono, Formulario.
En la nueva pantalla que se despliega, ver imagen ampliada, ya puedes ir introduciendo los datos de tus clientes.
En la parte izquierda tienes unos rectángulos blancos donde has de situar el cursor e ir escribiendo la información.
Así por ejemplo escribe en la primera fila en Nombre: Yolanda, en Apellido: Cuesta, en Dirección: 123 Avenida Hidalgo, Teléfono: 696 22 22 22 y en email: hojasdecalculo@ejemplo.com. Haz lo mismo en la segunda fila con tus datos y en la tercera con los datos de una tercera persona.

Pulsa Nuevo para pasar a cumplimentar el siguiente registro.
Así sucesivamente hasta introducir todos los registros.
Para moverte por esta ventana pulsa sobre la barra de desplazamiento que esta en la parte central y desplázate con el ratón sin soltar o pincha en los triángulos negros superior e inferior, así te iras situando en uno u otro registro.

Otras acciones importantes en el apartado de formularios son la Eliminación de registros y la Búsqueda de registros.

En cuanto a la Eliminación de registros, sitúate en el registro a eliminar, pulsa en el botón Eliminar y en el cuadro de dialogo haz clic en Aceptar, recuerda que si en el último momento decides no eliminarlo puedes pulsar Cancelar y el registro no se borrara, eso si una vez eliminado no podrás recuperarlo ni con la acción Deshacer.

También es útil que conozcas la opción Búsqueda de registros del formulario de datos, lo más sencillo es buscar de uno en uno con los botones Buscar anterior y Buscar siguiente.

Otra opción es pulsar en criterios y en los cuadro de texto, rectángulos en blanco, escribir la palabra o dato y dar a Buscar siguiente, aquí es importante señalar que la búsqueda de Excel es bajo la condicion”Y”, con lo cual si cumplimentas mas de un cuadro de texto han de estar bien escritos y ser correctos los datos para que te sitúe en el registro.

Para búsquedas más avanzadas puedes usar los Filtros y establecer diferentes condiciones en función de lo que busques.

Para continuar con la introducción de datos pulsa el botón Nuevo.

Cuando termines de introducir información pulsa en Cerrar y la ventana del Formulario desaparecerá, dejando los registros en tu hoja de cálculo.

Para volver a abrir el Formulario, sigue los pasos como explicaba al principio y se vuelve a abrir la ventada desde donde podrás continuar con la introducción de más referencias.

CATEGORÍAS FUNCIONES DE EXCEL

Descubre las diferentes funciones de Excel agrupadas por categorías y sacale el máximo partido a los programas de hojas de cálculo. Haz clic encima de cada tipo de funciónpara acceder a más información. Entre otras: Financiera, Matemáticas, Lógicas, Texto,…01de 11

Financiera

Realiza cálculos de operaciones financieras tales como tasas de interés, pagos de préstamos, amortizaciones, valor actual, tasa interna de rendimiento o TIR entre muchos otros.02de 11

Fecha y hora

Estas funciones te ayudan a fecharla hoja de Excel, indicando la hora actual, el día de la semana, año, número de la semana, etcétera.

Matemáticas y de Trigonometría

Aplícalas para el cálculo de seno, coseno, tangente, logaritmos y determinantes de la matrices entre otras muchas.

Estadísticas

Aquí tienes las funciones necesarias para calcular mediana, moda, promedio, máximos, mínimos, además de distribuciones estadísticas, desviaciones, varianzas… Con estas fórmulas podras realizar estudios e informes completos de manera fácil y rápida.

Búsqueda y referencia

Te sirve para localizar valores de un rango. Además crea hipervínculos para referenciar tus cálculos a documentos de Internet o en equipos en red.

Base de datos

Funciones para trabajar con los datos de una hoja en Excel, su nombre empieza por BD, calculan promedio, suma, contar.

Texto

Te ayudaran a trabajar con texto en la hoja de cálculo, convierte a mayúsculas, minúsculas, enlaza cadenas de texto, elimina espacios en blanco y más.

Lógicas

Úsala para obtener valores verdaderos o falsos en tu hoja de Excel en función de que una determinada condición se cumpla o no. S

Información

Te indica las características del estado de diferentes partes de tu hoja de cálculo, por ejemplo puedes comprobar si una celda esta vacía o no, obtener información sobre el formato de una celda y otras muchas.

Ingeniería

Realizarás cálculos para desarrollos en el campo de la ingeniería, entre otros conversión de unos sistemas de numeración a otros.

Otras categorías de funciones

Funciones definidas por el usuario: Excel te permite crear y grabar tus propias funciones.

INTRODUCCIÓN A LAS FÓRMULAS

A continuación se presentan las funciones más básicas en la hoja de cálculo no solo del programa Excel, sino también de la mayoría de ellos como por ejemplo el que tienes gratis en Google Drive.

En concreto estas son las funciones que te voy a explicar:

Suma
Resta
Producto
División
Porcentaje .
Máximo y Mínimo
Promedio

Funciones Básicas en Excel

Suma: Con esta función lo que calculamos es la suma de todos los números de un conjunto de datos (también denominado rango) Se representa en la hoja de Excel:

=SUMA(celda(s):celda(s))

Resta: Análogamente a la suma, pero con el signo – en vez del signo:. Y será:

=RESTA(celda(s)-celda(s))

Producto: Multiplica todos los números obteniendo el producto de dichos números. Aparecerá:

=PRODUCTO(celda(s);celda(s))

División: De forma similar al producto. La formula aparecerá:

=DIVIDIR((celda(s)/celda(s))

Porcentaje(%): Excel nos calcula el tanto por ciento de una serie de datos, dividiendo la cantidad entre el total. La expresión de esta función es

= PRODUCTO(%; celda(s))

Máximo y Mínimo: Con esta función Excel nos va a indicar el valor máximo y mínimo de un conjunto de valores. Así:

=MAX(rango) y=MIN(rango)

Promedio: Devuelve la media aritmética de una serie de valores. Con la expresión:

=PROMEDIO(rango)

Ejercicio con funciones en Excel

En el ejercicio que a continuación te explico, vas a realizar tus primeras acciones para practicar con las funciones de Excel, supón que tienes los siguientes datos:

Escribe en la celda A1: la cifra 3 y en la celda A2: escribe 2.
Estas situado en la celda A3, quieres realizar la suma de estos números, para ello vas a:
Insertar, Función, elige Suma y pulsa con el ratón o bien directamente escribe en el primer rectángulo A1 y en el segundo A2 damos a aceptar, obtenemos 5.

Procede de igual forma para realizar el cálculo del resto de funciones explicadas y así habrás realizado tus primeros pasos en Excel.

SI ANIDADO EN EXCEL

La función SI le permite realizar una comparación lógica entre un valor y el resultado que espera probando una condición y devolviendo un resultado si es Verdadero o Falso.

=SI(Algo es Verdadero, hacer algo; de lo contrario hacer algo diferente)

Por esto, una instrucción SI puede tener dos resultados. El primer resultado es si la comparación es Verdadera y el segundo si la comparación es Falsa.

Las instrucciones SI son extremadamente sólidas y forman la base de muchos modelos de hoja de cálculo, pero también son la causa principal de muchos de los problemas en las hojas de cálculo. Lo ideal es que una instrucción SI se aplique a condiciones mínimas, como Hombre/Mujer, Sí/No/Quizás, por nombrar algunos ejemplos, pero a veces es posible que deba evaluar escenarios más complejos que requieren el anidamiento* de más de 3 funciones SI juntas.

* “Anidamiento” hace referencia a la práctica de unir varias funciones en una fórmula.

Aunque Excel permite anidar hasta 64 funciones SI distintas, no se recomienda hacerlo. ¿Por qué?

El uso de varias instrucciones SI requiere una cuidada planificación para crearlas correctamente y asegurarse de que su lógica puede calcular sin errores cada una de las condiciones hasta llegar al final. Si no anida su fórmula con una precisión absoluta, podría funcionar en el 75 % de los casos pero devolver resultados inesperados en el 25 % restante. Lamentablemente, las probabilidades de detectar ese 25 % de casos son muy escasas.
El mantenimiento de varias instrucciones SI puede resultar muy difícil, especialmente si pasado un tiempo intenta averiguar lo que usted o, peor todavía, otra persona, estaba intentando hacer.

Si se encuentra con una instrucción SI que parece que no para de crecer, es el momento de soltar el mouse y volver a pensar en su estrategia.

A continuación encontrará instrucciones sobre cómo crear correctamente una instrucción SI anidada compleja con varios SI y cómo reconocer que es el momento de usar otra herramienta en su arsenal de Excel.

Ejemplos

A continuación encontrará un ejemplo de una instrucción SI anidada relativamente estándar para convertir resultados de exámenes en su equivalente de la calificación mediante letras.

=SI(D2>89,»A»,SI(D2>79,»B»,SI(D2>69,»C»,SI(D2>59,»D»,»F»))))Este instrucción SI anidada compleja sigue una lógica sencilla:

Si el resultado (en la celda D2) es mayor que 89, la calificación es A
Si el resultado es mayor que 79, la calificación es B
Si el resultado es mayor que 69, la calificación es C
Si el resultado es mayor que 59, la calificación es D
En caso contrario, la calificación es F

Este ejemplo concreto es relativamente seguro porque no es probable que la correlación entre resultados de exámenes y calificaciones mediante letras cambie, por lo que no requiere mucho mantenimiento. Pero, ¿qué pasaría si necesita aplicar grados a las calificaciones, es decir, A+, A y A-, y así sucesivamente? En ese caso, es necesario volver a escribir la instrucción SI con cuatro condiciones para que pase a tener 12. Este es el aspecto que tendría la fórmula:

=SI(celda=»»,»0″,celda$* celda)

CREACIÓN DE MACROS

Cuando tenemos que crear una hoja de cálculo, llevar una contabilidad o crear un calendario o una factura, la aplicación más completa, conocida y utilizada para este fin es Excel. Esta aplicación ofimática forma parte de la suite Office de Microsoft y tiene todo lo necesario para facilitarnos al máximo la creación de estas hojas de cálculo. Sin embargo, puede que mientras trabajemos con ellas nos encontremos con que algunas tareas pueden llegar a resultar realmente repetitivas, tareas que si se pudieran automatizar nos serían de gran utilidad, y es aquí donde entran en juego las Macros.

Una Macro es un pequeño programa, un conjunto de instrucciones, que se puede configurar en determinadas aplicaciones ofimáticas, como Word o Excel, y cuya principal finalidad es automatizar ciertas actividades rutinarias y repetitivas, así como poder dotar a los documentos de una complejidad que, de otra manera, no sería posible.

Algunas de las Macros de Excel más habituales son, por ejemplo, dar el mismo formato a una hoja concreta o crear una misma tabla. Otros usos más habituales de estas instrucciones pueden ser, por ejemplo, para intercalar ciertos contenidos entre determinadas filas u organizar algún contenido.

Las Macros de Excel la verdad es que pueden resultar complicadas para usuarios sin demasiados conocimientos. De todas formas, al final todo depende de lo que queramos complicarnos. A continuación os vamos a explicar cómo crear una Macro sencilla en Excel.

Como las Macro son una función enfocada principalmente a usuarios avanzados, la pestaña que nos permite acceder a estas funciones está oculta por defecto, por lo que para poder utilizar estas funciones lo que debemos hacer es habilitar esta pestaña. Para ello, debemos entrar en el apartado Archivo > Opciones y, en el apartado de “Personalizar la cinta de opciones” activaremos la casilla de “Desarrollador”.

Ahora podremos ver cómo en la barra de herramientas nos ha aparecido una nueva pestaña llamada “Programador”.

Para grabar una nueva Macro en Excel lo que debemos hacer es, desde esta nueva pestaña “Programador”, pulsaremos sobre el botón “Grabar Macro” que aparece en la parte izquierda de la lista Ribbon.

Al pulsar sobre este botón nos aparecerá una ventana desde la que podemos configurar la Macro que vamos a crear. Desde esta ventana podemos darla un nombre, asignarle un atajo de teclado e incluso elegir si queremos que forme parte de un libro, o grupo de macros.

En este momento la Macro empezará a grabar. Ahora debemos realizar todas las tareas que queremos automatizar en este script para que Excel las vaya guardando paso a paso. Una vez hayamos hecho lo que queríamos hacer solo nos quedará detener la grabación de la Macro para guardarla.

Para ver todas las Macros que tenemos creadas y guardadas en Excel lo que debemos hacer es, dentro de la pestaña Programador, pulsar sobre el botón “Macros”.

Podremos ver una ventana como la siguiente donde veremos una lista con todas estas tareas automáticas guardadas.

Cuando seleccionemos una Macro, a la derecha nos aparecerán una serie de botones. El primero de ellos se llama “Ejecutar”, y nos permitirá lanzar la Macro para que realice su actividad. Además de este botón también tenemos otros como “Paso a paso“, que nos permite ejecutar la macro función a función, muy útil si, por ejemplo, para detectar el punto exacto en el que hay un problema u ocurre cierto evento, y, además, el botón “Modificar”que nos va a permitir abrir el editor de código para poder analizar las funciones de la Macro y modificarla o programarla a mano.