MARCO TEÓRICO de Planilla de Cálculo "EXCEL"

VENTANAS

Iconos de Planillas de Cálculos de Excel:

Ventanas de Microsoft Excel 

Tener en cuenta que a partir de la versión Excel 2007, no son muchos los cambios de las siguientes versiones.

CONCEPTOS

• Microsoft Excel: es un software que nos permite crear plantillas electrónicas, utilizar fórmulas y funciones para realizar cálculos, insertar gráficos estadísticos, y muchas otras funciones relacionadas con las finanzas, la contabilidad y la matemática.
• Libro: un libro es un archivo. Está compuesto por hojas, a su vez cada hoja tiene 255 hojas más.
• Hoja: es una planilla con filas enumeradas (Fila 1, 2, etc.) y columnas alfabetizadas (Columa A, B, etc.)
• Celda: es el lugar donde se introducen los datos, lugar formado por una columna y una fila.
• Rango: es un conjunto de celdas.

FORMATOS A LAS PLANILLAS

Versión ´98 y 2003 de Microsoft Excel

• Bordes y Sombras

1. Seleccionar la tabla,
2. Ir a Formato / Celdas,
3. Luego a Bordes; elegir estilo, color y contorno-interior, y
4. Después, clic en Tramas para elegir un color de fondo de la tabla.

• Colocar el signo $ y separador de los miles "."

1. Seleccionar datos
2. Ir a Formato / Celdas, y
3. En la ficha Número, elegir Contabilidad; especificar decimales y símbolo.

 

• Alineación de Encabezados:

1. Seleccionar encabezados (celdas con los títulos de las tablas),
2. Ir a Formato / Celdas,
3. En la ficha Alineación, elegir en Horizontal: centrar, en Vertical: centrar, y luego marcar la opción Ajustar texto.

Versión 2007 y 2010 de Microsoft Excel

En esta versión, para acceder al menú Formato/Celdas, basta con marcar la tabla, clic con el botón derecho del mouse sobre la selección y en el menú contextual que aparece elegir la opción Formato de celdas... 

Nota: cambia el nombre de la ficha Tramas por Relleno.

CÁLCULOS

Tanto las fórmulas como las funciones van precedidas del signo igual "="

• Fórmulas:

Uso de OPERADORES ARITMÉTICOS tales como suma (+), resta (-), multiplicación (*), división (/), porcentaje (%) y potenciación (^).

Ejemplos:

=A1+B1                      =A1*21%                               =A1/3

• Funciones:

Es una fórmula que permite realizar cálculos más complejos y con más rapidez.

1. Se marca la celda donde se desea insertar la función,
2. Clic en la pestaña de la Autosuma, 
3. Elegir la función que se desea (suma, promedio, cuenta, máx, mín) o Más funciones... para elegir otras funciones.
4. Una vez insertada la función, marcar o seleccionar las celdas con las cuales se desea operar, y luego presionar Enter.

Sintaxis de una función:

Una lista de las funciones más utilizadas (que luego se detallarán):

• Suma

Permite realizar la suma de un rango de celdas.

Sintaxis:

=suma(número1;número2;...)

• Promedio

Permite realizar el promedio de un rango de celdas.

Sintaxis:

=promedio(número1;número2;...)

• Cuenta

Permite contar las celdas que no están vacías.

Sintaxis:

=cuenta(número1;número2;...)

• Máx

Permite hallar el valor máximo de un rango de celdas.

Sintaxis:

=max(número1;número2;...)

• Mín

Permite hallar el valor mínimo de un rango de celdas.

Sintaxis:

=min(número1;número2;...)

• Buscarv

Permite buscar un valor específico en una matriz y colocar el valor que se desee.

Sintaxis:

=buscarv(valor buscado; matriz; indicador de columna)

• Cuenta.si

Permite contar las celdas dentro del rango, que no están en blanco y que cumplan con el criterio especificado.

Sintaxis:

=cuenta.si(rango;criterio)

• Sumar.si

Permite sumar celdas en el rango que coinciden con el argumento criterio.

Sintaxis:

=suma.si(rango;criterio;rango suma)

• Y 

Es una función lógica que devuelve VERDADERO si se cumplen todos los argumentos, de lo contrario, devuelve FALSO.

Sintaxis:

=y(valor lógico1; valor lógico2:...)

• O

Es una función lógica que devuelve VERDADERO si se cumplen por lo menos uno o más argumentos, de lo contrario, devuelve FALSO.

Sintaxis:

=o(valor lógico1; valor lógico2:...)

• Año

Permite insertar el año correspondiente a la fecha.

Sintaxis:

=año(número de serie)

• Ahora

Permite insertar el número de serie de la fecha y hora.

Sintaxis:

=ahora()

• Hoy

Permite insertar el número de serie de la fecha actual.

Sintaxis:

=hoy()

• Entero

Permite redondear un número hasta el entero inferior más próximo.

Sintaxis:

=entero(número)

• Entres otras.

Funciones anidadas: se les llama así cuando se coloca una función dentro de otra. 

Por ejemplo:

=SI(PROMEDIO(A1:A10)<70;"INGRESO";"RENDIR OTRA VEZ")

OPERADORES

Operadores Aritméticos:

1. Suma                        +
2. Resta                        -
3. Multiplicación           *
4. División                     /
5. Porcentaje                %
6. Exponente                ^

Operadores de Comparación:

1. Igual                           =
2. Distinto                     <>
3. Mayor                        >
4. Menor                        <
5. Mayor o igual            >=
6. Menor o igual            <=

Ejemplos:

• =A1/5

Divide el contenido que se encuentra en A1 por 5.

• =(C1+C2)*2

Primero suma el contenido que se encuentra en C1 y C2, y luego, su resultado lo multiplica por 2.

• =A1>B1

Se compara indicando que A1 es mayor que B1.

AUTOLLENADO

Permite terminar más rápido el tipeo de planillas. Se pueden autollenar números, textos, textos con números, formatos de celdas, fórmulas y funciones, de la siguiente manera:

NOTA: si se presiona la tecla CTRL mientras se arrastra, Excel rellenará las demás celdas con el número consecutivo, o en el caso de las fechas, repetirlas.

GRÁFICOS ESTADÍSTICOS

Partes de un gráfico:

Crear un gráfico:

Versión ´98 y 2003 de Microsoft Excel

1. Marcar los valores a graficar.
2. Ir a Insertar / Gráfico.
3. Elegir una Categoría de Gráfico (Ej.: columnas, circular, líneas, etc.) y luego una Subcategoría (Ej.: circular 3D, circular simple, circular seccionado, etc.).
4. Luego seguir los pasos del Asistente de Gráficos, para colocar Títulos y Subtítulos, especificar Leyenda y Rótulos.

Versión 2007 y 2010 de Microsoft Excel

1. Marcar los valores a graficar.
2. Ir a Insertar / Gráfico.
3. Elegir una Categoría de Gráfico (Ej.: columnas, circular, líneas, etc.) y luego una Subcategoría (Ej.: circular 3D, circular simple, circular seccionado, etc.).
4. Al hacer un clic sobre la Subcategoría del gráfico elegido, se insertará en la hoja el gráfico.
5. Inmediatamente como se puede visualizar se activa la pestaña Diseño para elegir Tipo, Dato, Diseño de gráfico y Estilos de diseño. Y en la pestaña Presentación encontraremos Etiquetas, Ejes, Fondo y Análisis.

Tipos de gráfico:

A modo de síntesis, a continuación se presenta las posibilidades de representaciones gráficas que tiene la planilla de cálculo, se analiza a grandes rasgos la función o el uso más usual que se le da a cada tipo de gráfico.

Los histogramas

Es un tipo de gráfico empleado habitualmente para realizar análisis estadísticos sobre el conjunto de datos. Básicamente, la función que cumple es mostrar la cantidad de veces que se produce un hecho, a lo que denomina frecuencia (o aparición de un valor) según ciertas categorías o rangos de datos, llamados intervalos de clase.

Los intervalos de clase se representan en el eje de abscisas, y sobre cada uno de ellos se grafica una barra cuya altura es proporcional a la frecuencia.

Líneas, columnas y barras

• Para representar un conjunto de observaciones hechas a lo largo del tiempo, normalmente a intervalos iguales, se usa por lo general un gráfico de líneas. Esta representación gráfica se suele denominar serie cronológica o temporal, ya que la variable independiente es el tiempo. La escala de tiempos se representa en el eje horizontal. Estos gráficos son los que mejor se adaptan a la representación de tendencias, pues ponen en evidencia inmediatamente los incrementos y decrementos de la series de valores.

• Para centrar la atención en los valores en sí, y no en su progresión y continuidad, es posible representar los datos solamente a través de símbolos. Es un caso particular de los gráficos de líneas. Es posible también combinar líneas y símbolos para indicar con precisión la localización exacta de cada uno de los valores de una serie de datos.

• Para realizar comparaciones, Excel ofrece diferentes diagramas: de columnas, de barras, etc., todos ellos de forma y posibilidades similares. Los gráficos de columnas son apropiados para comparar los valores de una o más series. En ellos se toman las áreas de las columnas proporcionales a los valores numéricos relacionados con cada categoría. Como las columnas tienen el mismo ancho, la que mantiene la proporcionalidad con los datos es la altura. Cuando se representa más de una serie, las columnas de las distintas series se visualizan una al lado de la otra, y a cada una se la asigna un patrón de relleno o un color diferente. El ancho de las columnas depende del número de valores representados: cuando más valores se presenten, más angostas serán las columnas. La dirección de izquierda a derecha que imponen la lectura de un gráfico de barras es apta para representar el progreso hacia el alcance del objetivo previsto. Estos gráficos se usan, por ejemplo, para comparar beneficios reales y presupuestados.

• Para comparar el total de las categorías consideradas, visualizando además la contribución relativa de cada serie, se utilizan los gráficos de barras o columnas apiladas. Constan de barras o columnas verticales segmentadas para representar varias series de valores, donde cada parte de las barras o columna representa un valor de cada una de las series.

Radios, áreas, sectores, anillos

• Los gráficos radiales se usan para comparar datos con respecto a un único punto central. Cada categoría considerada tiene su propio eje de valores con un epicentro en común.

• Para mostrar cómo afectan varios componentes al conjunto total a lo largo del tiempo, se usan los gráficos de áreas. Comparten las características de los gráficos de líneas y de los de barras apiladas, utilizando a la vez líneas y patrones de relleno para representar varias series de valores. Cada serie que haya en el gráfico queda ilustrada por un área con un patrón de relleno o color que la distingue. Estas áreas se superponen, de manera que el borde superior de cada una representa el total acumulado de las series de valores que haya por debajo.

• Para mostrar las proporciones de las partes en relación al total, se usan los diagramas circulares o gráficos de sectores. Éstos utilizan un círculo para representar una única serie de valores. A cada categoría se le asocia un sector del círculo, de forma que las áreas de los sectores sean proporcionales al porcentaje que representa cada valor sobre el total de la serie.

• Si se desea mostrar las relaciones de las partes con un todo incluyendo varias series de datos, se utiliza el gráfico de anillos. Cada anillo se divide en coronas circulares, que mantienen la proporcionalidad del área con respecto al porcentaje que representa cada valor sobre el total de la serie. De esta manera, se pueden establecer comparaciones entre las relaciones representadas.

• Para este mismo objetivo también se pueden utilizar los gráficos de columnas apiladas 100% o barras apiladas 100%, donde cada rectángulo se divide en porciones proporcionales al porcentaje que representa cada valor de la serie. En este tipo de gráfico también es posible utilizar varias series.

TABLA DINÁMICA

Una tabla dinámica sirva para ordenar y analizar mejor los datos de  un listado o tabla de Excel.

Crear una tabla dinámica:

Versión ´98 y 2003 de Microsoft Excel

1. Colocar el cursor al comienzo de la tabla,
2. Ir al Menú Datos / Informe de Tablas y Gráficos Dinámicos,
3. Marcamos las opciones (suelen ya estar seleccionadas): Lista o base de datos de Microsoft Excel, y más abajo, la opción Tabla Dinámica. Luego Siguiente,
4. El software marca toda la tabla, pero de no hacerlo se debe seleccionar el Rango de la Tabla incluyendo encabezados y Siguiente,
5. Clic en el botón Diseño; a la derecha hay una lista de botones, arrastramos uno de esos botones y lo soltamos donde dice Columna. Repetimos la operación y arrastramos donde dice Fila, y por último arrastramos un tercero donde dice Datos. SIEMPRE TENER EN CUENTA LOS DATOS QUE SE DESEAN ANALIZAR. Luego Aceptar.
6. Ahora se debe especificar donde se desea ubicar la Tabla Dinámica: en una Hoja de Cálculo Nueva o en una Existente (especificar lugar). Después, presionar Finalizar.

Versión 2007 y 2010 de Microsoft Excel

1. Colocar el cursor al comienzo de la tabla,
2. Ir al ficha Insertar / Tabla Dinámica,
3. El software marca toda la tabla, pero, de no hacerlo se debe seleccionar el Rango de la Tabla incluyendo encabezados, y se debe especificar donde se desea ubicar la Tabla Dinámica: en una Hoja de Cálculo Nueva o en una Existente (especificar lugar). Luego Aceptar,
4. A la derecha hay una lista de botones, arrastramos uno de esos botones y lo soltamos donde dice Columna. Repetimos la operación y arrastramos donde dice Fila, y por último arrastramos un tercero donde dice Datos. SIEMPRE TENER EN CUENTA LOS DATOS QUE SE DESEAN ANALIZAR.

Al tener la TABLA DINÁMICA, la misma ha ordenado todos los datos de la tabla inicial. Se pueden insertar más tablas dinámicas para analizar otros datos si se necesita. Además, la Tabla Dinámica tiene unas flechitas o pestañas que nos permiten mostrar u ocultar los datos. 

Actualización de datos

Si agregamos o cambiamos algún datos de la TABLA INCIAL, debemos actualizar la TABLA DINÁMICA:

1. Nos ubicamos sobre la Tabla Dinámica, en cualquier celda.
2. Desde el menú contextual / Actualizar datos se actualizará la tabla automáticamente (la opción actualizar datos se mantiene desactivada si No se encuentra sobre la tabla dinámica).

Gráficos

A partir de la Tablas Dinámicas se pueden crear gráficos estadísticos. Si observamos la barra de herramienta de la Tabla Dinámica, cuando insertamos la Tabla Dinámica tenemos la opción para crear un gráfico.

REFERENCIAS

A la hora de hacer fórmulas o funciones, y aplicar autollenado para hacer los cálculos más rápidos, surgirá la necesidad de fijar las columnas o filas, para que no cambie al autollenar. Para ellos realizaremos lo siguiente:

Para fijar una columna o una fila se utiliza el signo peso "$", siempre adelante. 

Ejemplo:

$A1   o      A$1

Si se fija una columna  o una fila se le llama en Excel Referencia Mixta.           

Ahora si deseo fijar toda la celda completa, solo basta con colocar el "$" adelante de la columna o fila en forma manual o presionando la tecla F4.

Ejemplo:

$A$1

Cuando se fija toda la celda completa se lo conoce como Referencia Absoluta.

MENSAJES DE ERROR MÁS FRECUENTES

Cuando una fórmula no se introduce correctamente, Excel presenta un mensaje de error que indica cuál es el fallo cometido:

• #¡valor!      

Hay que tener cuenta que se han incluido en la fórmula algunos caracteres de texto, o bien se ha hecho referencia a una celda en la que no hay un valor numérico sino un texto.

• #¡div/0!

Se corrige cambiando la fórmula, considerando que hemos hecho referencia en un denominador a una celda donde el valor no existe, o es cero, o es una celda en blanco.

• #¡ref!

Quiere decir error en la referencia: indica que, al actualizar una fórmula con referencias relativas, se están tomando celdas que no existen porque la referencia sale de la hoja de cálculo.

• ######

Nos indica que el resultado no cabe en ese ancho de la celda, basta con ensanchar la columna.

• #¿Nombre?

Este error quiere decir que hay algún error en el enunciado de la fórmula, algún espacio o alguna letra incorrecta.

FORMATO CONDICIONAL

Agregar, cambiar o quitar formatos condicionales

Versión ´98 y 2003 de Microsoft Excel

1. Seleccionar las celdas cuyo formato condicional se desea agregar, modificar o quitar.
2. En el menú Formato, hacer clic en Formato condicional.
3. Seguir estos procedimientos:

Agregar un formato condicional

1. Seguir estos procedimientos:

Para utilizar los valores de las celdas seleccionadas como el criterio de formato, hacer clic en Valor de la celda, seleccionar la frase de comparación e introducir un valor constante o una fórmula. Si se introduce una fórmula, iníciela con un signo igual (=).

Para utilizar una fórmula como criterio de formato (para la evaluación de datos o condiciones que no sean los valores de las celdas seleccionadas), hacer clic en Fórmula e introducir la fórmula cuyo resultado sea un valor lógico de VERDADERO o FALSO.

2. Hacer clic en Formato.
3. Seleccionar el formato que se desea aplicar cuando el valor de la celda satisfaga la condición o la fórmula devuelva el valor VERDADERO.
4. Para agregar otra condición, hacer clic en Agregar y repetir los pasos del 1 al 3.

Pueden especificarse hasta tres condiciones. Si ninguna de las condiciones que se han especificado es verdadera, las celdas conservarán los formatos existentes.

Versión 2007 y 2010 de Microsoft Excel

Los pasos son similares que en la versión de Word anterior, solo que cambian los botones. 


Para ir a Formato Condicional debemos realizar los siguiente:

1. En la ficha Inicio / Formato Conficional,
2. Allí elegir algunas de las opciones -como verán en esta versión de Word tenemos más opciones de formato condicional-, en este caso elegimos Resaltar reglas de celda, luego eligir algunas de las opciones que nos muestra o clic en Reglas.
3. Posteriormente indicar los cambios que se desean hacer y el formato aplicar.

Una muestra de lo que se puede hacer con formato condicional: 

Nota:  Utilizar varias condiciones      Si hay más una condición especificada verdadera, Microsoft Excel sólo aplicará los formatos de la primera condición verdadera, aunque haya varias que lo sean.

Cambiar un formato

Seguir estos procedimientos:

• Para cambiar los formatos, hacer clic en Formato para la condición que se desea modificar.
• Para volver a seleccionar formatos en la ficha actual del cuadro de diálogo Formato de celdas, hacer clic en Borrar y seleccionar los nuevos formatos.
• Para quitar una o más condiciones, hacer clic Eliminar y, a continuación, activar la casilla de verificación de las condiciones que se desea eliminar.

Buscar celdas que tengan formatos condicionales

1. Para buscar todas las celdas que tiene un formato condicional, hacer clic en cualquier celda.

Para buscar las celdas que tienen una configuración de formato condicional idéntica a la de una celda concreta, hacer clic en dicha celda.

2. En el menú Edición, hacer clic en Ir a.
3. Hacer clic en Especial.
4. Hacer clic en Celdas con formatos condicionales.
5. Seguir uno de estos procedimientos:

Para buscar las celdas que tengan cualquier formato condicional, hacer clic en Todos debajo de Celdas con validación de datos.

Para buscar las celdas que tengan formatos condicionales idénticos, hacer clic en la opción Iguales a celda activa en Celdas con validación de datos.

FILTRO automático de datos

La opción de filtrado automático de datos, permite hacer una elección de la visualización de los datos cargados en la planilla.

Para realizar una filtración de datos, se deben seguir los siguientes pasos:

1. Una vez tipiado los datos, se debe seleccionar la primer celda del encabezado de la planilla,
2. Luego ir a Datos / Filtro / Autofiltro,
3. Aparecerán en cada Título del encabezado unas pestañas con las cuales se podrá elegir los datos que se desearán visualizar.

VALIDACIÓN DE DATOS

La validación de datos se utiliza para evitar errores en el momento de ingresar un dato, ya que solo se podrá elegir la opción que la lista muestre.

Para crear una validación de datos, se debe realizar el siguiente procedimiento:

1. Tener tipiado la lista que se desea visualizar e ingresar,
2. Luego se selecciona la celda donde se insertará la Validación de datos, 
3. En Datos / Validación...
4. En Criterio de Validación  / Permitir elegir Lista y en Origen los datos que se desean visualizar,
5. Presionar Aceptar y listo.

Cabe destacar que también existen otros Criterios de validación, como por ejemplo,  fechas, longitud de texto, etc. -otras formas de restringir y limitar el ingreso de datos a una celda-.

FORMULARIOS

Una forma fácil y práctica de rellenar las planillas de datos es utilizando un formulario. Para ello realizar los siguientes pasos:

1. Tipiar los títulos del encabezado de la planilla,
2. Ubicarse en el primer título o marcarlos,
3. Luego en Datos / Formularios
4. Aparecerá un cuadro de diálogo en el cual se pueden ir ingresando los datos, y solos se irán agregando a la planilla.