planillas de cálculo electrónico - conceptos básicos

PLANILLAS DE CÁLCULO ELECTRÓNICO Conceptos Básicos

Profesor Ricardo Leithner

versión 2012

Inf

or

má

tic

a

ÍNDICE

LAS PLANILLAS DE CÁLCULO............................................................................................... 3 ORÍGENES Y EVOLUCIÓN............................................................................................................................3 APLICACIONES DE LA PLANILLA DE CÁLCULO..........................................................................................3 ESTRUCTURA Y ORGANIZACIÓN................................................................................................................3

ELEMENTOS BÁSICOS.............................................................................................................................3 NOMBRE DE LAS CELDAS – RANGOS....................................................................................................4

DATOS Y FORMATOS...................................................................................................................................4 INGRESANDO DATOS ..............................................................................................................................4 TIPOS Y CATEGORÍAS DE DATOS ..........................................................................................................5 APLICANDO FORMATOS..........................................................................................................................5 CÓMO SELECCIONAR CELDAS Y RANGOS............................................................................................5

ORDENANDO DATOS...................................................................................................................................5 CÁLCULO Y AUTOMATIZACIÓN...................................................................................................................6

FÓRMULAS - OPERADORES MATEMÁTICOS BÁSICOS.........................................................................6 FUNCIONES..............................................................................................................................................7 COPIAR, CORTAR Y PEGAR CELDAS .....................................................................................................7 RELLENAR DATOS AUTOMÁTICAMENTE EN CELDAS ADYACENTES..................................................8 REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS..............................................................................8 MENSAJES DE ERROR ............................................................................................................................9

MICROSOFT EXCEL ...................................................................................................................................10 ÁREA DE TRABAJO DE MS EXCEL........................................................................................................10 ORGANIZACIÓN DE LOS PRINCIPALES COMANDOS Y HERRAMIENTAS..........................................11

CREACIÓN DE GRÁFICOS CON PLANILLAS DE CÁLCULO......................................................................12 GENERACIÓN DE UN GRÁFICO BÁSICO ..............................................................................................12 TIPOS DE GRÁFICOS.............................................................................................................................13

GUÍA (MUY) BÁSICA DE FUNCIONES EN MS EXCEL ........................................................ 15

Bibliografía Ayuda de Microsoft® Windows XP, Microsoft® Corporation, 2002. Ayuda de Microsoft® Office 2000, Microsoft® Corporation, 1999. Elementos Básicos de la Informática, Educar S.E., 2005. Ayuda de OpenOffice.org Calc, Sun Microsystemsoft Inc, 2000-2007. Manual del usuario de Microsoft® Excel, Microsoft® Corporation, 1993. Revista Users, MP Ediciones (Informes Varios). Wikipedia® , Wikimedia Foundation, Inc., 2008.

Versión y Licencias Febrero 2012

PLANILLAS DE CÁLCULO ELECTRÓNICO - Conceptos Básicos por Ricardo Leithner se encuentra bajo una Licencia Creative Commons Atribución-NoComercial-CompartirIgual 3.0 Unported. Basada en una obra en www.calameo.com.

PLANILLAS DE CÁLCULO - PROFESOR RICARDO LEITHNER

pág 3

LAS PLANILLAS DE CÁLCULO

ORÍGENES Y EVOLUCIÓN A fines de los años ’70 un estudiante universitario llamado Dan Bricklin observaba a su profesor de la universidad de Harvard crear un modelo financiero (del tipo tabla) en el pizarrón. Cuando el profesor encontraba un error o deseaba cambiar un valor, tenía que tomarse el trabajo de borrar y reescribir gran cantidad de datos en la tabla. Al observar la incomodidad que este proceso manual generaba, Bricklin comenzó a trabajar para poder realizar el mismo proceso interactivo, pero en una computadora. El resultado de su trabajo fue una hoja de cálculo electrónica que mostraba en celdas los resultados de las fórmulas escritas. La

creación de Dan Bricklin se llamó VisiCalc (ver imagen) y fue la primera aplicación de hoja o planilla de cálculo electrónico disponible para computadoras personales. Curiosamente, Bricklin no patentó su invento y a partir de entonces surgieron muchas planillas de cálculo electrónico (electronic spreadsheet) que fueron superando a la original: SuperCalc (1980), MultiPlan (1982), Lotus 1-2-3 (1983), AppleWorks (1984), QuattroPro, etc. Probablemente la hoja de cálculo más difundida en la actualidad sea Microsoft Excel, del paquete de aplicaciones para oficina Microsoft Office. Cuando su primera versión apareció en 1987 fue revolucionaria ya que, además de superar las prestaciones de las hojas existentes, popularizó el novedoso concepto de incluir una interfaz gráfica, permitir el uso del mouse, etc. Hoy existen además otras alternativas libres y gratuitas como LibreOffice y OpenOffice Calc, Gnumeric y Kspread que pueden utilizarse en múltiples sistemas operativos. Y actualmente es posible por Internet utilizar aplicaciones que permiten trabajar con planillas de cálculo en línea, visitando las páginas de Google Spreadsheets, ZohoSheets, EditGrid, la versión de Excel para WindowsLive, etc.

APLICACIONES DE LA PLANILLA DE CÁLCULO

¿Qué nos permiten hacer? Las amplias posibilidades de las planillas de cálculo permiten:

crear tablas y listas trabajar con bases de datos organizar y ordenar datos resolver cálculos mediante fórmulas matemáticas, lógicas, estadísticas, etc. crear gráficos de diversos tipos con los datos insertar dibujos e imágenes Utilizar herramientas de auditoría (control) para la edición y organización de la planilla.

El verdadero problema La informática siempre nos acerca herramientas que permiten resolver más fácilmente un problema que manualmente nos resultaría más complejo o interminable. El uso de las planillas de cálculo es uno de esos casos. Pero debemos recordar que estas soluciones no surgen automáticamente y que más allá de las obvias ventajas, la mayor dificultad para utilizar una planilla que resuelva un problema cotidiano está en saber convertir y adecuar esa situación de la vida real a la estructura de trabajo propuesta en el software. Y eso no lo realiza ningún programa: sólo se logra trabajando y ejercitando nuestra habilidad e inteligencia.

ESTRUCTURA Y ORGANIZACIÓN

ELEMENTOS BÁSICOS El producto del trabajo con una planilla de cálculo se denomina libro. De hecho el nombre predeterminado para un trabajo nuevo es Libro1. Un libro está compuesto por hojas. Un libro nuevo puede tener muchas hojas (depende de la capacidad de memoria de la computadora) aunque originalmente solo muestra tres (ver imagen).

Al guardar el trabajo en un archivo debemos recordar asignarle un nombre apropiado, que nos permita reconocer su contenido. Por ejemplo, en Excel, en lugar del nombre Libro1.xls propuesto podemos utilizar costos.xls.


pág 4

Cada hoja, a su vez, está compuestas por celdas (la unidad básica) organizadas en una grilla de columnas y filas.

NOMBRE DE LAS CELDAS – RANGOS Para trabajar con las celdas y pode hacer referencia a ellas es importante poder identificarlas. El identificador de una celda es una coordenada única, compuesta por el nombre de la columna (una letra) y el número de la fila a las que pertenece (como en el juego de la batalla naval...). Por ejemplo: C6 es la celda activa en la figura que está en la columna C, fila 6.

RANGOS Un rango es un conjunto de celdas. Un rango continuo se compone nombrando la celda inicial (la superior izquierda) y la final (inferior derecha) separada por dos puntos “ : ”. Por ejemplo: el rango de la tabla de la figura es A3:C10. A veces es necesario marcar rangos múltiples o no continuos. En esos casos se especifican los rangos separados por un punto y coma. Por ejemplo, para sumar los contenidos de dos grupos de celdas separados como los valores del micro, la memoria RAM y el disco rígido de la planilla de la figura se debería aplicar la fórmula =SUMA(C4:C5;C10) siendo C4:C5 la 1er parte y C10 la 2da del rango. ASIGNANDO NOMBRES Para facilitar un poco las cosas, es posible emplear un nombre que identifique a esas celdas especificándolo en el Cuadro de nombres (p.e.: llamar a D1 como “Descuento” y no emplear sus coordenadas). CELDAS EXTERNAS A LA PLANILLA Muchas veces es necesario identificar una celda de otra página e incluso de otra planilla. Por ejemplo, para referirse al dato que está en la celda B7 de la Hoja1 del archivo GEOGRAFIA.xls el identificador es algo así:

en Calc de StarOffice: ‘GEOGRAFIA.xls’#Hoja1.B7

en Excel de MS Office: ‘[GEOGRAFIA.xls]Hoja1’!B7 Si bien de esta manera puede las referencias pueden resultar algo complejas, en todos los casos se pueden editar gráficamente marcando las celdas con el puntero del mouse, por ejemplo, al construir una fórmula. Las referencias se escribirán automáticamente.

DATOS Y FORMATOS

INGRESANDO DATOS Para introducir un texto debemos posicionarnos en la celda correspondiente (con las teclas de desplazamiento o haciendo clic en ella). Al escribir, además de aparecer en la celda, podremos verlo en la barra de fórmulas. En esta barra aparecen también: el cuadro de cancelación (la cruz en rojo), el cuadro de introducción (el visado en verde) y el asistente para funciones (signo =). Para confirmar lo escrito, podemos hacer clic en el botón de Introducción o simplemente pulsar <Enter> y el texto se introducirá en la celda. Si nos equivocamos o arrepentimos, hacer clic en el cuadro de cancelación (X).

Nota: Como vemos, existen diferencias según el programa utilizado. En algún caso el separador entre hoja y celda es el punto y en otro el signo de exclamación. O bien el nombre del archivo debe aparecer entre corchetes o no. Los programas informan de estos criterios en sus Manuales y en la Ayuda (F1).

Especificaciones típicas. La capacidad de las planillas varía según la versión de los programas. Por ejemplo, para la versión Excel 2002 el tamaño de la hoja es de 65.536 filas por 256 columnas y una celda puede contener 255 caracteres, pero en otros casos es diferente (consultar siempre la Ayuda correspondiente). La cantidad de hojas en un libro es en función de la memoria disponible (el número predeterminado es 3).

Rango total de la tabla: “A3:C10”

Celda activa: “C6”

Columna de la celda activa: “C”

Fila de la celda activa: “6”

Hoja activa: “Hoja1”


pág 5

Notas: PARA EDITAR UNA CELDA: Debemos hacer un clic sobre ella y corregirla en la barra de fórmulas o bien

hacer doble clic o seleccionarla y pulsar <F2>. Hecho esto ubicamos el cursor en la posición en donde queremos hacer la modificación y la editamos normalmente.

PARA AGREGAR RENGLONES A UNA CELDA: No es posible pulsar normalmente <ENTER> para generar un renglón en el interior de una celda. Para poder hacerlo debemos primeramente pulsar <Alt+ ENTER> en Excel o bien <Ctrl+ ENTER > si estamos trabajando con Calc de OpenOffice.

TIPOS Y CATEGORÍAS DE DATOS Un dato escrito en una celda puede ser del tipo texto o numérico. Para identificar el tipo, las planillas muestran predeterminadamente los textos alineados a la izquierda y los números a la derecha de su correspondiente celda. En el caso de que se trata de un dato numérico es conveniente asignarle alguno de las categorías posibles (ver el cuadro Formato de Excel en la figura). En el ejemplo de la figura al aplicar formato de moneda, con sólo escribir el número en la celda (120) se verá con el signo $ y los decimales especificados ($120,00). Además de las categorías predeterminadas es posible configurar alguna Personalizada según nuestra necesidad (Por ejemplo para agregar alguna unidad: 20cm ó 1,500gr)

APLICANDO FORMATOS Más allá de la categoría dada al dato contenido en una celda es posible aplicarle diferentes formatos con el fin de modificar su aspecto o destacarlo mediante su tamaño tipografía o fuente, color de carácter, de sombreado (o relleno), alineaciones verticales y horizontales, dirección del texto, entre otros formatos (Ver las otras fichas que aparecen en el cuadro de texto de la imagen anterior).

CÓMO SELECCIONAR CELDAS Y RANGOS Para poder aplicar formatos a una o varias celdas, debemos seleccionarlas. Veamos algunas técnicas: Para seleccionar una celda, basta con moverse con el puntero hasta ella y hacer clic o bien emplear las

flechas de desplazamiento. Si queremos seleccionar una fila, podemos hacer clic en el encabezado de la fila. Para seleccionar una columna, hacerlo sobre el encabezado de la columna. Para seleccionar un rango, nos posicionamos en la primera celda de dicho rango, hacemos clic en ella y

sin soltar el botón del ratón arrastramos hacia abajo, hacia la derecha o la izquierda hasta completar la selección del rango. Soltamos entonces el botón y veremos que todo el rango se ha seleccionado, quedando resaltado en pantalla en modo inverso.

Para seleccionar un rango múltiple, seleccionar el primer grupo de celdas que contienen los datos que desea incluir. Mientras mantenemos presionada la tecla <Ctrl>, seleccionar otros grupos de celda que desea incluir.

Para seleccionar la hoja completa, haga clic en el botón que está situado en la intersección de los números de filas y las letras de las columnas.

Si queremos utilizar el teclado en vez del mouse, podemos seleccionar toda la hoja pulsando la combinación de teclas <Ctrl + SHIFT + Barra espaciadora> o bien cubrir rangos manteniendo pulsada la tecla <SHIFT> mientras utilizamos las flechas de desplazamiento.

ORDENANDO DATOS Los datos en las hojas de cálculo se suelen introducir de una manera desordenada, y eso a veces hace complicado tener una visión general, o tener alguna respuesta completa con facilidad. El comando Ordenar, hace más fácil tener distintas visiones de una misma hoja de cálculo. Las hojas de cálculo permiten ordenar columnas por orden alfabético, si se trata de palabras, o de menor a mayor (o a la inversa) si se trata de números.

La forma más sencilla de ordenar en orden alfabético normal y en orden inverso es mediante los botones de orden ascendente y descendente de la barra Estándar.

Se pueden ordenar las filas completas o sólo algunas celdas seleccionadas, según la primera columna de la selección.


pág 6

La forma más completa para ordenar datos es mediante el comando “Ordenar…” dentro del menú “Datos”, que permite más posibilidades que los botones. Desde este cuadro se pueden seleccionar las distintas columnas que guiarán el ordenamiento, según hasta tres niveles distintos de prioridades.

El cuadro ordenar tiene en su parte inferior una casilla de verificación con las opción

Esto permite avisar si en nuestra selección de datos a ordenar están incluidos o no los títulos de cada columna (Artículos, Cantidad, etc)

¡Atención! Hay que tener mucho cuidado con ordenar sólo una columna cuando en el resto de columnas hay datos que van por filas, y no olvidarse de comprobar la correspondencia de datos, porque podemos acabar con un buen lío, eso sí, bien ordenado alfabéticamente.

CÁLCULO Y AUTOMATIZACIÓN

FÓRMULAS - OPERADORES MATEMÁTICOS BÁSICOS Una fórmula es una ecuación que realiza cálculos en una celda. Es posible crear una fórmula que simplemente muestre el contenido de otra celda o bien que realice operaciones matemáticas como sumar, multiplicar, etc. Las fórmulas siempre deben comenzar con un signo “=” (igual). Por ejemplo: = 20 * A1 multiplica por 20 el valor almacenado en la celda A1. Los operadores matemáticos que podemos emplear en fórmulas son los que se ven en la tabla:

Notas: Una buena planilla no utiliza valores en sus fórmulas (sólo referencias) y debe diseñarse para ser fácilmente modificable si en el

futuro necesita un ajuste en los coeficientes empleados. Si bien podemos aplicar en una fórmula una operación con valores (= 355 + 421, por ejemplo) el verdadero poder de la planilla de cálculo está en saber utilizar para las fórmulas las referencias a valores contenidos o calculados en otras celdas (= A4 + C56, por ejemplo).

= 355 + 421 ASÍ NO ESTÁ MAL, PERO... De esta manera, cada vez que se modifique algún valor en las celdas involucradas en la fórmula, el resultado se

actualizará automáticamente (¡evitando así los problemas del Profesor de Dan Bricklin!

= A4 + C56 ...EMPLEANDO REFERENCIAS APROVECHAMOS MUCHO MEJOR LA PLANILLA DE CÁLCULO

Las planillas calcula los valores almacenados, no los mostrados. No importa que por el formato aplicado a un número lo redondee a

sólo dos decimales. El programa almacena en realidad el valor completo y lo considerará para el cálculo.

1 El símbolo ^ (acento circunflejo) se obtiene con Alt+94 (Código ASCII)

Operador Significado Ejemplo +

(signo más) Suma 3+3

Resta 3-1 - (signo menos) Negación -1

* (asterisco)

Multiplicación 3*3

/ (barra oblicua)

División 3/3

^ (acento circunflejo1)

Exponente 3^2 (o sea, 3*3)

Las planillas realizan las operaciones de izquierda a derecha comenzando por el signo igual (=) y respetando el siguiente orden de precedencia de los operadores: 1ro Elementos entre paréntesis ( ) 2do Negación (como en -1) 3ro Porcentaje (%) 4to Exponente (^) 5to Multiplicación y división (* y /) 6to Suma y resta (+ y -)

Normalmente, lo que una celda ya editada muestra no es la fórmula que contiene sino su resultado pero esto es configurable desde el menú Herramientas /Opciones en Excel.


pág 7

FUNCIONES Una función es una fórmula incorporada en el programa, que toma una serie de valores denominados argumentos, en una estructura determinada o sintaxis. Estos valores son utilizados para realizar una operación, devolviendo un resultado. Por ejemplo:

Nota: Al escribir una función, la referencia a una celda o un rango podemos escribirla o bien editarla gráficamente seleccionando los argumentos con el mouse.

En general los programas disponen de funciones de búsqueda, matemáticas, financieras, lógicas, etc. Como son muchas y es difícil recordar todos los detalles para utilizarlas, muchas veces se incluye un Asistente para funciones que podemos utilizar al seleccionar la función adecuada ya que nos brindará toda la ayuda necesaria. Veamos algunas de las funciones más utilizadas: FUNCIÓN DESCRIPCIÓN SUMA

Es sencilla y se usa con mucha frecuencia. Suma los valores en el rango especificado. Sintaxis: SUMA (Rango o valores). En la planilla ejemplo, el resultado de calcular =SUMA(C2:C8) será igual a $1335,00

PROMEDIO Calcula la media aritmética (promedio) de una serie de valores. Sintaxis: PROMEDIO (Rango o valores). En la planilla ejemplo, el resultado de calcular =PROMEDIO(B2:B8) será igual a 8,57142857.

CONTAR Cuenta el número de celdas que contienen números. Sintaxis: CONTAR(Rango o valores). En el ejemplo, el resultado de calcular =CONTAR(B2:B8) será igual a 7.

MAX Devuelve el valor máximo de un conjunto de valores. Sintaxis: MAX(Rango o valores). En el ejemplo, el resultado de calcular =MAX(C2:C8) será igual a $420,00.

RAIZ Devuelve la raíz cuadrada de un número. Sintaxis: RAIZ(número) donde número es el valor o referencia a una celda cuya raíz cuadrada desea obtener. Si número es negativo, RAIZ devuelve el valor de error #¡NUM!

Para ver una Guía Básica de Funciones consultar el Apéndice A de este apunte. Para una lista completa con todas las funciones en MS Excel, poner en marcha el Asistente para Funciones, elegir el comando Función del menú Insertar. Mediante Categorías (en el cuadro de diálogo que aparece) podemos elegir verlas según los distintos tipos o bien ver todas por orden alfabético.

COPIAR, CORTAR Y PEGAR CELDAS El Portapapeles de Windows es una herramienta que permite hacer copias o mover distinto tipo de objetos: archivos, carpetas, textos, imágenes, sonidos, etc. dentro de la misma o entre diferentes aplicaciones. Pero dentro del contexto de las planillas de cálculo, las funciones para copiar o cortar una celda deben emplearse contemplando ciertas particularidades:

CORTAR: (Menú Edición ó Ctrol+X) Combinado con Pegar, funciona como Mover, ya que guarda en el Portapapeles la celda seleccionada hasta aplicar Pegar en la celda destino. Luego lo elimina de la posición original. En las planillas de cálculo, sólo pega el contenido una vez.

COPIAR: (Menú Edición ó Ctrol+C) Copia sin borrar y guarda en el Portapapeles lo que está seleccionado. Permite pegar múltiples veces lo copiado hasta pulsar <Esc>.

PEGAR: (Menú Edición ó Ctrol+V) pega en el punto marcado el contenido del Portapapeles.

Al copiar o mover el contenido de una celda con fórmulas se produce la corrección automática de las referencias a otras celdas contenidas en la misma. Esto se realizará contemplando el tipo de referencia según sea relativa o absoluta (ver más adelante REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS).

Nota: Existen varias formas de realizar gráficamente el copiado o cortado de celdas. como siempre, lo más conveniente es consultar la Ayuda del programa correspondiente.


pág 8

RELLENAR DATOS AUTOMÁTICAMENTE EN CELDAS ADYACENTES La opción rellenar y el comando series permiten rellenar celdas adyacentes con datos en forma automática. Son herramientas de edición y su forma de aplicación más habitual es mediante un control o manejador que toda celda posee en su extremo inferior izquierdo. Se puede emplear para repetir un dato, una fórmula (ver detalles más adelante) o bien armar una serie. Las series pueden ser las predefinidas en el programa como las de los días de la semana, los meses, numéricas, etc. o bien otras definidas por el usuario. Veamos paso a paso un ejemplo de aplicación:

Operando de esa forma se pueden obtener listas y series verticales u horizontales del tipo:

DÍAS MESES SERIES LINEALES

SERIES GEOMÉTRICAS

lunes lun enero ene 1 250 1 0,00001 martes mar febrero feb 2 251 2 0,0001 miércoles mié marzo mar 3 252 4 0,001 jueves jue abril abr 4 253 8 0,01 viernes vie mayo may 5 254 16 0,1 sábado sáb junio jun 6 255 32 1 domingo dom julio jul 7 256 64 10 lunes lun agosto ago 8 257 128 100 martes mar septiembre sep 9 258 256 1000 miércoles mié octubre oct 10 259 512 10000 jueves jue noviembre nov 11 260 1024 100000 viernes vie diciembre dic 12 261 2048 1000000

REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS En muchas ocasiones es necesario trabajar con planillas extensas. En esos casos es muy conveniente aplicar las ventajas del rellenado automático para el armado de las fórmulas. Por ejemplo, supongamos que la tabla de la figura debería extender su estructura por cientos de filas más. Sería sumamente tedioso tener que escribir una a una las fórmulas de cálculo que vemos en la columna TOTAL. Este inconveniente ha sido solucionado en las planillas de cálculo ya que mediante una correcta definición del tipo de referencia a las celdas y la aplicación de la herramienta de rellenado, es posible automatizar por completo esta tarea.

1) Escribimos en una celda el dato original. Aceptamos el dato y

seleccionamos la celda (sin editarla)

Luego tomamos con el puntero del mouse el manejador en el extremo inferior izquierdo de la celda.

2) Ahora arrastramos en el sentido en que deseamos completar

los datos.

Así se verá mientras

arrastramos el puntero.

3) Finalmente así quedará la serie

completa:

Nota: normalmente no se ven las fórmulas sino sus resultados.


pág 9

Referencias relativas Una referencia relativa desde una celda localiza a otra en forma relativa, dependiendo de la posición en que se encuentre la celda donde se escribió la fórmula (la primera). Es decir que representa internamente para el programa la distancia o camino hacia esa celda en términos de cuántas celdas hacia abajo, hacia arriba, hacia la izquierda o derecha existen hasta llegar a ella. Para ejemplificarlo, es como saber ir hacia la casa de alguien pero sin orientarse por su dirección. La referencia relativa es la forma predeterminada o natural de indicar una celda (B6) o rango (A1:D20). Este es el tipo de referencias que se corrigen y ajustan automáticamente al copiar las fórmulas. En el ejemplo anterior, al poner la fórmula original con referencia relativa, se corregirá automáticamente al copiar o rellenar de acuerdo con la nueva posición. Es que, en realidad, el contenido de la celda original expresa que el resultado de la fórmula es el producto de las dos celdas anteriores de su misma línea. (Para D4=B4*C4, para D11=B11*C11 en el ejemplo de la figura).

Referencias absolutas En algunas ocasiones necesitaremos copiar fórmulas en donde la referencia a una celda debe permanecer fija y constante sin modificarse relativamente según la nueva posición dentro de la planilla. En esos casos decimos que necesitamos una referencia absoluta a una o varias celdas. Estas referencias representan una dirección específica y cuando la fórmula se copia, se conservan en la fórmula en forma íntegra y textual. La referencia absoluta se determina colocando signos de pesos ($) antes de la letra de la columna y antes del número de la fila. Ejemplo: $B$1.

En el ejemplo se ha creado la fórmula en E4 =D4*$B$1. Al hacer una referencia de este tipo a B1, cuando copiemos la fórmula en el resto de la columna, se modificarán automáticamente las referencias relativas (D4, D5, D6, etc.) pero no la absoluta ($B$1).

Referencias mixtas Cuando es necesario construir fórmulas que hagan referencia a las correspondientes filas y columnas de manera tal que fuese conveniente una referencia absoluta a la fila y relativa a la columna (o viceversa), se emplean las referencias mixtas que combinan las relativas con las absolutas. Estas tienen la forma $B1 (columna absoluta, fila relativa) o bien B$1 (columna relativa, fila absoluta). En el ejemplo anterior podría haberse empleado la fórmula E4 =D4*B$1 con el mismo éxito ya que no hay una modificación de la posición relativa entre las columnas (siempre se trata de “la tercera columna a la izquierda”).

MENSAJES DE ERROR En caso de que la planilla no pueda resolver una fórmula por alguna razón, se mostrará un mensaje de error. Por ejemplo, los más comunes son:

#¡VALOR! indica que uno de los operandos de un cálculo matemático no es un número.

#¡DIV/0! indica que se está intentando calcular el valor de un número dividido por cero, resultado que tiende a infinito y por lo tanto fuera de los límites de cálculo de toda planilla.

#¡NUM! indica que se ha utilizado un argumento inaceptable en una función que necesita un argumento numérico o bien que el número obtenido supera los límites de cálculo posibles

#¿Nombre? indica que hay algún error en el enunciado de la fórmula, algún espacio o alguna letra incorrecta.

##### En realidad no es un error, simplemente indica que el resultado o contenido de la celda no puede mostrarse correctamente por ser muy angosta la columna. Se soluciona sencillamente ampliando el ancho de la misma.

Tanto para Excel como para OpenOffice se puede modificar durante la edición de una fórmula, el tipo de referencia a una celda pulsando la tecla F4 sucesivamente para obtener, por ejemplo, $B$1, B$1, $B1 y B1.


pág 10

MICROSOFT EXCEL Como referencia para conocer el uso de una planilla de cálculo adoptaremos al Microsoft Excel por su amplia difusión entre los usuarios. Si bien ha habido numerosas versiones del programa a lo largo del tiempo, no siempre los cambios han sido sustanciales en lo que a sus funciones respecta. De hecho, un viejo Excel 97 es aún hoy perfectamente utilizable para la mayoría de las tareas que se necesitan realizar en la oficina o el hogar. A partir de la versión perteneciente al paquete de aplicaciones MS Office 2007 sí ha habido importantes cambios en la interfaz gráfica y la organización de las herramientas y funciones. Pero como aún su presencia en el mercado no es masiva, este apunte se basa en las versiones anteriores, que son mucho más difundidas entre los usuarios e igualmente útiles para nuestro objetivo final que es aprender a utilizar una planilla de cálculo electrónico.

ÁREA DE TRABAJO DE MS EXCEL Con el programa instalado, para comenzar a trabajar, lo buscamos en el menú Inicio/ Programas (o Todos los programas) y ejecutamos la opción Microsoft Office/Microsoft Excel. Observemos entonces los elementos de su entorno de trabajo. En este caso, el área de trabajo propuesta para la versión Excel 2000:

Barra de Título: Identifica el programa y el documento contenidos en la ventana. Es el lugar por donde se la puede tomar para arrastrarla y moverla.

Barra de Menús: contiene organizadas en menús todas las funciones y herramientas del programa. Barras de Herramientas: permiten organizar accesos rápidos a distintas funciones por grupos de íconos.

Por ejemplo Barra estándar, Formato, Imagen, etc. Barra de Estado: muestra información sobre la ventana y sus contenidos. Barras de desplazamiento: cuando la ventana es demasiado chica como para mostrar todos sus

contenidos en forma completa, estos controles en sus bordes permiten desplazarse por su interior para verlo por sectores

Botones de control: se encuentran en la esquina superior derecha. Permiten Minimizar Maximizar, Restaurar o Cerrar la ventana. Los de la ventana externa controlan la aplicación y todos sus documentos activos. Los de las ventanas internas sólo al documento correspondiente.

Barra de fórmulas: Barra situada en la parte superior de la ventana de Microsoft Excel que se utiliza para introducir o modificar los valores o las fórmulas de las celdas o los gráficos. Muestra el valor constante o la fórmula almacenada en la celda activa.

Cuadro de Nombres: Situado en el extremo izquierdo de la barra de fórmulas, identifica la celda seleccionada, el elemento de gráfico o el objeto de dibujo. Permite asignar un nombre a una celda o a un rango seleccionado. También permite desplazarse a una celda nombrada previamente.

BARRA DE TÍTULO BARRA DE

MENÚS

BARRAS DE HERRAMIENTAS

COLUMNA ACTIVA TIPO DE

DATO NUMÉRICO

HOJA ACTIVA

TIPO DE DATO DE TEXTO

BARRA DE FÓRMULAS

CELDA ACTIVA

BARRA DE DESPLAZAMIENTO

TIPO DE DATO MONEDA

CUADRO DE NOMBRES

FILA ACTIVA

BARRA DE TÍTULO DE LA VENTANA DEL TRABAJO


pág 11

Cursor: marca en la barra de fórmulas y en las celdas en donde se escribirá el próximo caracter. Normalmente se ve como una línea vertical y titilante de aproximadamente la misma altura que los caracteres (no debemos confundirlo con el puntero del mouse).

ORGANIZACIÓN DE LOS PRINCIPALES COMANDOS Y HERRAMIENTAS Todos los comandos de un programa son accesibles mediante sus menús. La organización de estos menús está estrechamente relacionada con el tipo de función que cumplen. De esta manera es posible apreciar las posibilidades que la aplicación nos brinda recorriendo sus menús. De acuerdo con sus funciones podemos asociar funciones a los menús de la siguiente manera:

MENÚ FUNCIONES ASOCIADAS Archivo En este menú encontramos todo lo relacionado al manejo del archivo que contiene el libro.

También utilidades para su publicación (impresa o web) y envío (correo o fax). Edición Funciones que facilitan la automatización de tareas como escribir, editar, corregir. Ver Mediante las funciones de este menú se configuran los distintos elementos del área de trabajo.

No realizan modificaciones sobre el documento; sólo en su visualización. Insertar Permite incorporar al trabajo numerosos elementos adicionales que mejoran su funcionamiento,

diseño y composición. Por ejemplo: celdas, funciones, comentarios, gráficos, imágenes, etc. Formato Dar formato al texto o a las celdas tanto sea en forma manual o automatizada mediante la

aplicación de estilos, autoformatos y formatos condicionales Herramientas Combinar su uso con otras aplicaciones, aplicar protecciones, herramientas de auditoría, y

personalización del programa. Datos Permite ordenar, controlar, obtener datos. Gráfico Al trabajar con gráficos permite su creación, edición y administración. Ventana Permite organizar la vista de los trabajos activos Ayuda (?) Obtener ayuda útil para nuestra tarea.

Como en muchas aplicaciones la exploración de los menús será una excelente opción para conocer organizadamente cuáles son sus posibles comandos y herramientas. Al hacerlo, tengamos en cuenta que: En gran parte las tareas son comunes a cualquier aplicación (creación,

edición y diseño de planillas) y fueron tratadas en el presente capítulo y en Manejo operativo de la computadora de la Parte I.

Recordar que el empleo del menú contextual para cada tipo de elemento muestra las opciones y funciones más comunes para trabajar con él.

La organización de los menús no es estricta y puede tener algunas diferencias de acuerdo con la versión del programa que se considere e incluso de acuerdo con la manera en que haya sido instalado (p.e. que falten componentes o herramientas de Idioma).

Debido a posibles diferencias por versiones, recomendamos para conocer con mayor profundidad los distintos comandos, consultar la ayuda del programa que estamos utilizando.

---------------------------------------------------

¡AYUDA! Al conocer el nombre de

una herramienta podemos ingresar a las opciones de ayuda y consultar acerca

de ella con alguna palabra clave (¡cuidado,

hay que escribirla correctamente!).

Sólo debemos elegir entre las opciones que se

presenten la que está más de acuerdo con la tarea que queremos realizar.


pág 12

CREACIÓN DE GRÁFICOS CON PLANILLAS DE CÁLCULO Un gráfico es la representación gráfica de los datos de una hoja de cálculo, normalmente organizados en una tabla. Es una ilustración que permite mostrarlos de una manera más clara, sencilla y atractiva. Pero para que esto sea realmente útil es muy importante que el tipo de gráfico sea el adecuado y que lo que muestra sea coherente con lo registrado en los valores numéricos.

EDAD (en años) Total 14 4 15 6 16 4

NACIONALIDAD Total ARGENTINA 7 BOLIVIANA 4 PARAGUAYA 3

EDAD (en años)

NACIONALIDAD 14 15 16 TotalesARGENTINA 2 3 2 7

BOLIVIANA 1 2 1 4

PARAGUAYA 1 1 1 3

Totales 4 6 4 14 Tablas de Frecuencias Tabla de doble entrada

Con esta organización de datos es posible construir gráficos como los siguientes:

0

2

4

6

14 años 15 años 16 años

Totales por Edades

Gráfico de Columnas

Nacionalidades

ARGENTINA50%

BOLIVIANA29%

PARAGUAYA21%

Gráfico Circular (Torta)

POBLACIÓN SEGÚN LA NACIONALIDAD Y EDAD

0 1 2 3 4 5 6

ARGENTINA

BOLIVIANA

PARAGUAYA

14 años

15 años

16 años

Gráfico de Barras con series

GENERACIÓN DE UN GRÁFICO BÁSICO

Para crear un gráfico lo mejor es generarlo a partir de los datos organizados en una tabla y aplicar un Asistente para gráficos. Tomaremos como ejemplo el de MS Excel.

EJEMPLO – Bicicletas La siguiente tabla registra las ventas de bicicletas por año y estación en un local del rubro y se encuentra en una hoja de una planilla de cálculo Para generar a continuación un gráfico de columnas debemos seguir estos pasos:

1. Seleccionar el rango de celdas que se va a representar. Se deben incluir los datos numéricos que se desean presentar en el gráfico. También se pueden incluir los títulos del eje X (o eje de abscisas) y los nombres de las series de datos (rótulos)

2. Hacer clic en la herramienta “Asistente para gráficos”.

3. Ajustar las opciones deseadas en las siguientes fichas o Pasos del Asistente:

Paso 1 - Tipos de gráfico: Permite elegir entre los numerosos tipos de gráfico disponibles (Columnas, barras, circular, líneas, entre otros.). Además es posible personalizar mediante subtipos dentro de la opción de gráfico elegido. Para tener una idea del aspecto que tendrá el gráfico es muy conveniente el uso del botón Presionar para ver muestra.

Paso 2 - Datos de origen: La opción Rango de Datos define cuáles datos serán representados. Además es posible definir si los valores para las series de datos se tomarán de las filas o de las columnas de la tabla (Series en: ). La ficha Series permite editar con una mayor precisión las referencias a las celdas de origen de rótulos y datos del gráfico.


pág 13

Paso 3 - Opciones de gráfico: En esta ficha se controla la incorporación y el formato de los diferentes elementos del gráfico para lograr una presentación adecuada. Por ejemplo, Títulos, Ejes, Líneas de división, Leyenda, Rótulos de datos, Tabla de datos.

Paso 4 - Ubicación: Indica dónde será colocado el gráfico una vez creado. Puede ser como elemento incrustado o como una hoja de gráfico. El incrustado es más apropiado cuando se desea mostrar los datos junto a sus correspondientes tablas. En cambio, en una hoja de gráfico resulta más conveniente para una presentación como lámina o transparencia.

Finalmente, luego de haber seguido los pasos anteriores se debe obtener un gráfico similar al de la imagen. Si los resultados obtenidos no son los esperados, podrán modificarse mediante las opciones Tipos de gráfico, Datos de origen, Opciones de gráfico y Ubicación del menú contextual o bien desde el menú Gráfico. Éste sólo aparece en la barra correspondiente al seleccionar un gráfico seleccionado y reemplaza en ese contexto al menú Datos.

Notas: La forma más rápida de crear un gráfico en MS Excel en un solo paso es seleccionar una celda o todas las

que se desea graficar y entonces pulsar F11. Se generará un gráfico en una Hoja de gráfico con el formato predeterminado de Excel.

TIPOS DE GRÁFICOS La aplicación de un tipo de gráfico de todos los que el programa provee, deberá estar en función del tipo de información y de la manera en que se pretenda representar esos datos. A continuación se muestra una tabla con los tipos de gráfico recomendados según el objetivo que se persiga y un resumen que ilustra los principales tipos de gráficos disponibles. APLICACIONES TÍPICAS

OBJETIVOS TIPOS DE GRÁFICO EJEMPLOS Ilustrar datos que cambian con el paso del tiempo.

De líneas, de barras o columnas, simples o apiladas, de áreas.

Ventas anuales en una década.

Centrar la atención sobre los valores alcanzados.

De barras horizontales. Ventas de N productos en un año determinado. Costos por mes en un año.

Estudiar la relación entre el total y las partes.

Circular, columnas o barras apiladas, de áreas.

Porcentaje de composición de una cámara legislativa por partido político.

Analizar distribuciones de frecuencia.

De columnas. Cantidad de personas por edades (de 0 a 9 años, de 10 a 20 años, etc.).

Representar el grado de correlación entre distintas variables.

De columnas o de XY Dispersión.

Variación de la temperatura a lo largo de un día, costos de producción por unidades fabricadas.

Representar gráficamente una función de dos variables

De superficie. Representación tridimensional de una superficie. Representación topográfica.


pág 14

EJEMPLOS DE TIPOS DE GRÁFICO

COLUMNAS BARRAS

ÁREAS LÍNEAS

CIRCULAR ANILLOS

XY-DISPERSIÓN SUPERFICIE

COLUMNAS EN 3D OTROS MARCADORES

(Ilustraciones de la Ayuda de MS Excel ©)


pág 15

GUÍA (MUY) BÁSICA DE FUNCIONES EN MS EXCEL MATEMÁTICAS =SUMA( rango) =SUMA( número1; número2;...)

Suma los argumentos o componentes del rango. Se pasan por alto las celdas vacías, valores lógicos, texto o valores de error en la matriz o en la referencia.

=SUMAR.SI( rango; “criterio”; rango suma)

Suma los componentes del rango suma, que cumplan el criterio indicado en la columna rango.

= PRODUCTO(rango) = PRODUCTO( número1; número2; ...)

Multiplica todos los números que figuran como argumentos y devuelve el producto. Se pasan por alto las celdas vacías, valores lógicos, texto o valores de error en la matriz o en la referencia.

= COCIENTE( numerador; denominador)

Devuelve la parte entera de una división. Use esta función cuando desee descartar el residuo de una división. Si núm_divisor es 0, RESIDUO devuelve el valor de error #¡DIV/0! Ejemplos: COCIENTE(-5; 2) es igual a –2. COCIENTE(4,5; 3,1) es igual a 1

= RESIDUO( número; núm_divisor)

Devuelve el residuo o resto de la división entre número y núm_divisor. El resultado tiene el mismo signo que núm_divisor. Si núm_divisor es 0, RESIDUO devuelve el valor de error #¡DIV/0!

= POTENCIA( número; potencia)

Devuelve el resultado de elevar el argumento número a una potencia. Número es el número base. Puede ser cualquier número real. Potencia es el exponente al que desea elevar el número base. Se puede utilizar el operador "^" en lugar de la función POTENCIA para indicar a qué potencia se eleva el número base, por ejemplo 5^2.

= RAIZ(número) Devuelve la raíz cuadrada de un número. Número es el número cuya raíz cuadrada desea obtener. Si número es negativo, RAIZ devuelve el valor de error #¡NUM!

= ABS(número)

Devuelve el valor absoluto de número. El valor absoluto de un número es el número sin su signo. Ejemplos: ABS(-2) es igual a 2

Si A1 contiene -16, entonces: RAIZ(ABS(A1)) es igual a 4 = ALEATORIO.ENTRE( inferior; superior)

Devuelve un número aleatorio (cada vez que se calcula la hoja de cálculo) entre los números que especifique.

= M.C.D(número1; número2; ...)

Devuelve el máximo común divisor de dos o más (hasta 29) números enteros. El máximo común divisor es el mayor número entero por el cual número1 y número2 son divisibles sin dejar residuo. Si un valor no es un número entero, se trunca. Si uno de los argumentos es un valor no numérico, M.C.D devuelve el valor de error #¡VALOR!. Si uno de los argumentos es menor que cero, M.C.D devuelve el valor de error #¡NUM!

= M.C.M(número1; número2; ...)

Devuelve el mínimo común múltiplo de números enteros. El mínimo común múltiplo es el menor entero positivo múltiplo de todos los argumentos enteros número1, número2, etcétera.

= PI() Devuelve el número 3,14159265358979, o la constante matemática pi, con una exactitud de 15 dígitos. Ejemplo: =PI()/2 es igual a 1,57079...

CONTAR VALORES =CONTAR(rango) Cuenta cuántos números hay en el rango. =CONTARA(rango) Cuenta cuántos valores no vacíos hay en el rango. =CONTAR.BLANCO(rango) Cuenta cuántos blancos hay en el rango. =CONTAR.SI(rango; “condición”) Cuenta el número de celdas no vacías que cumplen la condición.


pág 16

MÁXIMOS MÍNIMOS Y PROMEDIOS =MAX(rango) Devuelve el mayor valor de la lista de valores indicados. =MIN(rango) Devuelve el menor valor de la lista de valores indicados. =PROMEDIO(rango) Calcula el promedio o media aritmética de los valores que recibe como parámetros. FUNCIONES DE USO CON FECHAS =AHORA() Devuelve la fecha y hora actuales. =HOY() Devuelve la fecha actual (sin la hora). =AÑO(valor_fecha) Devuelve el año de la fecha indicada. =DIA(valor_fecha) Devuelve el día del mes de la fecha. =FECHA(año; mes; día) Devuelve la fecha correspondiente al año, mes y día indicados. =DIAS360(fecha inicial; fecha final; método)

Devuelve la diferencia entre las dos fechas, basándose en un calendario de 360 días. Ejemplo: =DIAS360 (“30/1/93”;”1/2/93”)

SIFECHA(fecha inicial; fecha final; método);

Permite calcular tiempos exactos entre fechas en año, mese y días. En método se especifica si se desea considerar en años (“Y”), meses(“M”) o días(“D”), o bien en forma combinada. Por ejemplo empleando “YD” se podría calcular los días que pasaron desde el último cumpleaños. Nota: esta función no está documentada en la ayuda de MS Excel.

FUNCIONES LÓGICAS =O(valor lógico1; valor lógico2;...) Devuelve verdadero si alguno de los argumentos es verdadero. =Y(valor lógico1; valor lógico2;...) Devuelve verdadero si todos los argumentos son verdaderos. =SI(prueba lógica; valor si verdadero; valor si falso)

Ejecuta una prueba lógica y devuelve el 2º argumento si es cierto y el 3º si es falsa. Ejemplos: =SI(8>5; “Mayor”; “Menor”) Devuelve “Mayor” =SI(8<=5; “Mayor”; “Menor”) Devuelve “Menor”

FUNCIONES ANIDADAS En algunos casos, se hace necesario utilizar una función como uno de los argumentos de otra función. Por ejemplo, la fórmula en la figura utiliza una función PROMEDIO anidada y compara el resultado con el valor 50.

Cuando se utiliza una función anidada

como argumento, deberá devolver el mismo tipo de valor que el que utilice el argumento. De otro modo, se mostrará #¡VALOR!

En general, una fórmula puede contener como máximo siete niveles de funciones anidadas.

Es sumamente útil utilizar la Paleta de fórmulas para editar funciones anidadas como argumentos. ya que permite verificar durante la edición los resultados parciales obtenidos.

Ejemplo de la Ayuda de MS Excel ®

EJEMPLOS DE FUNCIONES ANIDADAS =SI(Y(valor lógico1; valor lógico2; valor si verdadero; valor si falso)

Ejecuta unas pruebas lógicas y devuelve el 2º argumento si es cierto y el 3º si es falsa. Ejemplos: =SI(Y(a5>5;b5<25); “Mayor”; “Menor”) Devuelve “Mayor” si la celda a5 es menor de 5 y b5 es mayor de 25

=SI(O(valor lógico1; valor lógico2; valor si verdadero; valor si falso)

Ejecuta unas pruebas lógicas y devuelve el 2º argumento si es cierto o si es cierto el 3º, si no se cumple ninguna de las dos es falsa. Ejemplo: =SI(O(a5>5;b5<25); “Mayor”; “Menor”) Devuelve “Mayor” si se cumple alguna de las dos consignas.

-------------------------------------

planillas de cálculo electrónico - conceptos básicos

Documents