Excel Básico

Compartir un enlace:

Conocerá la interfaz del Excel y podrá interactuar ingresando sus datos, fórmulas y funciones. El procesamiento de dichos datos permitirá obtener los resultados presentados a través de reportes y gráficos.
Conocer el entorno de Excel: Libros, hojas, rangos, gráficos, reportes, celdas. Manejar las diversas formas de ingresar y editar datos; así como también dar formato a los mismos. Crear fórmulas y aplicar las funciones básicas en la solución de diversos requerimientos en el procesamientos de datos.

a.    La ventana de trabajo en Excel
Son hojas de cálculo que hacen fácil la creación y manipulación de tablas de datos. Esta Aplicación incluye muchas herramientas de cálculo, así como diferentes tipos de formato, representación gráfica de los resultados, análisis de datos y automatización de tareas mediante la programación en Visual Basic.
b.   El Puntero de Celda
Indica la celda activa donde se puede ingresar o modificar un dato.
c.   El Puntero del Mouse
Dependiendo donde se ubique el puntero del mouse, este cambiará de forma para realizar diferentes acciones en la hoja de cálculo: Resultado de imagen para puntero de mouse excel
d. Tipos de datos
En las celdas se pueden escribir los siguientes tipos de datos:
EJERCICIO: COPIA SERIES DE DATOS
Primero veremos como se copian aquellos datos que son considerados como elementos de serie en excel. 1.- En una hoja de  cálculo  en  blanco,  en  la celda  A3 escribir la palabra  Enero y luego arrastrar hacia abajo el cuadro de relleno de esa celda. Como resultado se obtiene la copia de los demás meses a partir de Enero.   2.-  Ahora, en la celda B3 escriba la palabra  Lunes y también arrastre su cuadro de relleno hacia abajo.   3.- En las celdas A3 y A4 escriba los números   3 y 6, luego seleccione ambas celdas y arrastre su cuadro de relleno.   4.-   En las celdas C3 y C4 escriba nuevamente los números  3 y 6.   5.-   Luego seleccione también ambas celdas pero arrastre su cuadro de relleno manteniendo presionada la tecla de  Ctrl. En esta ocasión el resultado obtenido es copia de los 2 números exactamente como fueron escritos. Ya no son copiados como serie ascendente.   6.-   En las celdas E3, E4 y E5 escriba Breña, Miraflores y San Borja.   7.-   Luego seleccione las tres celdas y arrastre su cuadro de relleno. El resultado es una copia idéntica de las tres celdas en forma sucesiva hasta donde sea arrastrado el cuadro de relleno.  (Tenga presente que en este caso cuando se arrastran únicamente celdas con texto, la tecla de Ctrl. no tiene ningún efecto.)
Son los que permiten que las formulas puedan realizar las diferentes operaciones con  los datos. Existen cuatro tipos de 4 operadores: MATEMATICOS, RELACION, TEXTO Y REFERENCIA.    
Comencemos con las fórmulas de Excel
Insertar una formula en una celda es muy fácil. Sólo escribe un signo igual (=) seguido de la fórmula. Puedes hacer clic en el ícono de formulas para abrir la pestaña "Insertar función".
También puedes acceder a todas las funciones de Excel a través del menú de "Fórmulas". Todas las fórmulas se agrupan por categorías. Haz clic en la categoría que necesites y a continuación selecciona la fórmula que quieres utilizar. Esto abrirá una pestaña donde puedes insertar los parámetros de la fórmula.
Aquí está mi lista de las 12 fórmulas Excel que todo arquitecto debería conocer:
1. SUMA
Suma todos los valores en el rango especificado. El rango puede ser una sola columna o varias columnas. Incluso puedes especificar celdas individuales utilizando una coma para separar los valores.
=SUMA(A5:A25)
2. SI
Emite un valor si una condición es verdadera y otro si la condición es falsa. Resulta útil para obtener una visión global rápida de los datos. También se puede utilizar Y u O dentro de la instrucción SI para construir una lógica más compleja.
=SI(A2>B2, “NEED AREA”, “AREA OK”)
3. SUMAR.SI
Realiza la función SUMA sólo en los casos que se cumplan ciertos criterios. Utiliza SUMAR.SI para especificar varios criterios.
=SUMAR.SI(A1:A7, “>0″)
=SUMAR.SI(A1:A7, A1:A7, “>100″, A1:A7, “<200″)
4. CONTAR, CONTARA, CONTAR.BLANCO
Cuenta el número de elementos en el rango especificado. CONTAR sólo cuenta números, no texto o celdas en blanco. CONTARA cuenta celdas que no están vacías. Esto incluye números, textos y otros tipos de datos. CONTAR.BLANCO cuenta únicamente las celdas que están vacías.
=CONTAR(A5:A25)
=CONTARA(A5:A25)
=CONTAR.BLANCO(A5:A25)
5. CONTAR.SI
Similar a CONTAR pero contará solo si se cumplen los criterios especificados. Utiliza CONTAR.SI para especificar varios criterios. Por ejemplo, las habitaciones que son mayores que 200 m2, pero menos de 500 m2.
=CONTAR.SI(A1:A8, “>100″)
=CONTAR.SI(A1:A8, “>100″, A1:A8, “<200″)
6. PROMEDIO
Calcula la media aritmética o promedio del rango especificado de celdas.
=PROMEDIO(A5:A25)
7. MIN
Emite el número más pequeño de la gama de celdas especificada. Esto podría ser útil para encontrar el área más pequeña en una hoja de cálculo de programación.
=MIN(A5:A25)
8. MAX
Similar a MIN pero esta fórmula devuelve el número más grande de un rango de celdas.
=MAX(A5:A25)
9. BUSCARV
BUSCARV ayuda a que Excel funcione como una aplicación que articula una base de datos en vez de una simple hoja de cálculo.
=BUSCARV(B3,$A$17:$B$20,2)
10. REDONDEAR
Redondea un número a un número especificado de dígitos. También se puede utilizar REDONDEAR.MAS y REDONDEAR.MENOS para especificar la dirección del redondeo.
=REDONDEAR(7.86, 1) en 7.9
=REDONDEAR.MAS(7.23, 0) en 8
=REDONDEAR.MENOS(8.85, 1) en 8.8
11. MULTIPLO.INFERIOR y  MULTIPLO.SUPERIOR
Estas dos funciones redondean un número hacia arriba (MULTIPLO.INFERIOR) o hacia abajo (MULTIPLO.SUPERIOR) al múltiplo más cercano. Útil cuando debes redondear divisas.
=MULTIPLO.INFERIOR(A1, 10)
=MULTIPLO.SUPERIOR(A2, 0.25)
12. CONCATENAR
Utilice la función CONCATENAR para unir dos celdas. Esta función es ideal para unir textos que se encuentra en columnas separadas. También se puede utilizar un signo (&) en lugar de escribir CONCATENAR.
=CONCATENAR(B1, “, “, A1)
=A3& ” ” & B3
a. La Ficha Inicio
El solo obtener correctamente los resultados en nuestros cuadros de Excel no es suficiente; también es necesario que estos cuadros tengan una apariencia que haga fácil su lectura y entendimiento. Para esto existe la posibilidad de cambiar la apariencia del contenido de las celdas con ayuda de la ficha Inicio y los botones de herramientas que se encuentran en los grupos Fuente, Alineación, Número, Estilos y Celdas:
b. Para dar Formato a un Cuadro
El el Cuadro de Pagos que se muestra en la siguiente figura solo se ingresaron datos, pero no se le dio formato alguno.
1.- Seleccione el rango A1:F1 y utilice el botón Combinar y centrar, para poner el titulo Cuadro de Pagos al centro del cuadro. Luego aumente el tamaño de fuente a 14 puntos, y subráyelo.
2.- Seleccione los títulos de la fila 3 y póngalos Negrita, Centrado, dele Color de Fuente y Color de Relleno, luego coloque borde a cada una de sus celdas con la opción Todos los bordes.
3.- Finalmente seleccione las celdas con números y dele el formato Estilo millares para separar con comas en cada millar y redondearlos a 2 decimales. De forma similar dele formato a las demás celdas del cuadro (ver figura).
Ejercicio: Cuadro de Pedidos de varios clientes
En un libro en blanco escribir los siguientes datos:
Luego completar el ingreso de datos que se muestra en la siguiente figura: En la celda B5 escribir la formula =$B5*C5 y luego copiarla con el cuadro de relleno hasta la celda D9. Finalmente seleccione la celda D10 y utilice de la ficha Inicio el comando de Autosuma para sumar todos los montos.
Escritas las formulas, el cuadro deberá verse con los siguientes resultados:
A continuación, vamos a darle formato al cuadro para mejorar la apariencia de este, para lo cual siga los siguientes pasos:
1. Seleccione A3:A4 y haga clic en el comando Combinar y centrar, luego haga clic en el comando “Alinear en el medio” para centrar verticalmente la palabra Producto.
2. Seleccione B3:B4 y haga nuevamente clic en el comando Combinar y centrar, luego haga clic en el comando “Alinear en el medio” para centrar verticalmente las palabras Precio Unitario. Finalmente haga clic en el comando “Ajustar texto” para poner en dos filas el contenido de esta celda combinada.
3. Modifique el ancho de la columna A, haciendo doble clic entre los encabezados de las columnas A y B, a fin de realizar un ajuste perfecto del ancho de esta columna.
4. Seleccione C3:D3 y haga clic en el comando Combinar y centrar a fin de que la palabra METRO se ubique al centro de estas 2 celdas.
5. Igualmente seleccione A10:B10 y otra vez haga clic en el comando Combinar y centrar a fin de que la palabra TOTALES también se ubique al centro de estas 2 celdas.
6. Seleccione las celdas C4 y D4 y haga clic en el botón de comando “Centrar”
7. Presione Ctrl+* para seleccionar todo el rango A3:D10 y desplegando la Herramienta de Bordes haga clic en la opción Todos los bordes, con la finalidad de cuadricular todo el cuadro. Entonces el cuadro deberá verse así:
Ahora daremos formato a los datos numéricos: 8. Seleccionar los precios unitarios y dale formato estilo millares. Luego a la celda B5 darle el formato monetario de soles.
9. Seleccionar el rango C5:C9 y luego clic en el botón de comando Centrar.
10. A todos los montos darle el formato estilo millares, y a las celdas D5 y D10 solamente darles el formato monetario de soles.
11. Poner en negrita los títulos de las filas 3 y 4, y también los totales de la fila 10. Hasta este punto el cuadro deberá verse así:
12. A la celda con la palabra METRO pintarla el fondo de color amarillo y las letras de color verde. Además darle el tamaño de fuente de 14 puntos.
13. Pintar el relleno de las celdas de las filas 5 y 6 del color que usted crea conveniente.
14. Pintar el relleno de las celdas A10:B10 de color negro, y las letras de color blanco.
Todas las funciones constan de dos partes: El nombre de la función y sus argumentos, que deben escribirse entre paréntesis y separados por comas:
=Nombre de función(Argumentos)
He aquí algunos ejemplos de las primeras funciones que veremos en este manual:
=SUMA(A3:A20)
=PROMEDIO(H4:J15)
=MAX(C2:C30)
=MIN(C2:C30)
=CONTAR(C2:C30)
=CONTARA(C2:C30)
=ENTERO(A3+3.1416)
=REDONDEAR(A3+3.1416)
=SI(E4>=0,RCUAD(E4),”No tiene Raíz”)
=MES(“28/7/14”)
a) Uso de las Funciones más comunes
Veamos en un ejemplo, los resultados que se pueden obtener usando algunas de las funciones más comúnmente utilizadas por la mayoría de las personas que trabajan con Excel. El siguiente cuadro muestra una relación de personas a quienes se les ha programado la fecha de su cita en una Clínica: Las funciones como cualquier fórmula deben empezar con un signo igual, y el resultado de ésta aparecerá en la misma celda donde se escribe la función.
La Función SUMA
Suma de las edades de todos los pacientes:
=SUMA(B4:B11)       Rpta: 39
Esta función considera solo las celdas con datos numéricos(ignora las celdas B7 y B8)
La Función PROMEDIO
Promedio de edades de todos los pacientes:
=PROMEDIO(B4:B11)          Rpta: 6.5
No se considera en el cálculo la celda B6 pues su contenido no es numérico
La Función MAX
Edad máxima de un paciente:
=MAX(B4:B11)              Rpta: 11
Encuentra el máximo valor numérico del rango mencionado.
La Función MIN
Edad mínima de un paciente:
=MIN(B4:B11)             Rpta: 2
Encuentra el mínimo valor del rango mencionado (ignorando la edad del bebé porque no es un dato numérico)
La Función CONTAR
Cantidad de pacientes que tienen fecha de cita programada
=CONTAR(B4:B11)           Rpta: 6
Esta función cuenta cuántas celdas tienen datos numéricos (las fechas son números)
La Función CONTARA
Cantidad Total de pacientes
=CONTARA(B4:B11)          Rpta: 7
Esta función en cambio cuenta cuántas celdas en total están ocupadas sin importar el tipo de datos.
La Función ENTERO
Edad promedio de los pacientes sin considerar los decimales:
=ENTERO(PROMEDIO(B4:B11))          Rpta: 6
La función Entero trunca la parte decimal del resultado.
La Función REDONDEAR
Edad promedio de los pacientes redondeada a 1 decimal y a cero decimales:
=REDONDEAR(PROMEDIO(B4:B11),1)        Rpta: 6.5
=REDONDEAR(PROMEDIO(B4:B11),0)        Rpta: 7
La función Redondear tiene dos argumentos separados por una coma: el valor calculado (el Promedio), y la cantidad de decimales a la que se desea redondear el resultado.
b) Coordenadas Absolutas y Coordenadas Relativas
=REDONDEAR(E4/F$2,2)
=REDONDEAR(E5/F$2,2)
=REDONDEAR(E6/F$2,2)
=REDONDEAR(E7/F$2,2)
  En la coordenada F$2 del ejercicio anterior, el signo de $ situado antes del número de fila hace que este número 2 permanezca constante al copiarse la formula hacia abajo. En cambio, si alguna fórmula en otra ocasión tuviese que ser copiada, no hacia abajo, sino hacia la derecha; entonces habría que escribir el signo $ antes de la letra de la coordenada: $F2 para que al copiarse la fórmula, la letra F permanezca constante, de otro modo la letra aumentaría alfabéticamente a G, H, I… etc. A estas fórmulas así escritas con el signo de $, se las conoce como fórmulas con coordenadas absolutas. En conclusión entonces, una coordenada puede escribirse de varias formas según sea el caso:  
F2 coordenada relativa
F$2 coordenada con número de fila absoluta
$F2 coordenada con letra de columna absoluta
$F$2 coordenadas de fila y columna absolutas totalmente
 
Cómo dar Nombre a un Rango de Celda
Para nombrar un rango de celdas existen dos maneras de hacerlo, y ambas se pueden usar por igual.
Primera forma:
1.- Seleccionar el rango de celda que se desea nombrar.
2.- En la ficha Formulas/ Nombres definidos, elegir la opción Asignar nombre a un rango.
 
3.- En la ventana de diálogo que aparece escribir el nombre que se le quiere dar al rango (lo recomendable es que el nombre sea de una sola palabra, no importa en mayúsculas o minúsculas).
4.- Finalmente hacer clic en el botón Aceptar.
 
Segunda forma:
1.- Seleccionar el rango de celda que se desea nombrar.
2.- Hacer un clic en el casillero de Cuadro de Nombres que se encuentra en la parte izquierda de la barra de fórmulas sobre los encabezados de columna.
3.- Escribir en el casillero el nombre que se le quiere dar al rango.
4.- Presionar Enter.
Ejemplo:
Suponga que se tiene un cuadro con la relación de los montos de pago de varios clientes y al rango B4:B16 se le ha dado el nombre de rango PAGOS.
Entonces… Si se deseara calcular la suma de todos los pagos, podríamos utilizar la función autosuma:
Antes se tenía que escribir la formula así:
=SUMA(B4:B16)
Ahora que B4:B16 tiene el nombre PAGOS, la formula podría escribirse así:
=SUMA(PAGOS)

1. Listas de Datos: ordenamiento y búsqueda

a. Qué es una Lista de Datos

Es un grupo de varias columnas consecutivas de celdas ocupadas con datos en su interior, y donde cada columna tiene un tipo homogéneo de datos. Por ejemplo:

b. Cómo ordenar una lista de datos usando los botones de herramientas

Una de las formas más fáciles de hacer que Excel ordene una lista de datos como la del ejemplo anterior, es utilizando los botones de herramientas orden ascendente: Ordenar de A a Z y orden descendente: . Ordenar de Z a A, que se encuentran en la ficha Datos. Por ejemplo, para ordenar la lista ascendentemente por el Nombre de cada socio.

1.- Seleccionar una celda de la columna B, como la celda B6 por ejemplo. 2.- Hacer un clic en el botón de herramientas Ordenar de A a Z. La lista entonces se vería así:

Ejercicio: Intersección de rangos y uso de la función INDIRECTO

Para entender mejor el manejo de listas de datos, vamos a llevar a cabo un nuevo ejercicio en el cual se desea llevar el control de los pedidos de artefactos de varios clientes. En una Hoja de cálculo se tiene una lista de Precios, y una lista de Pedidos de artefactos eléctricos.

El cuadro de pedidos en realidad llega hasta la fila 100, o sea que existen 90 pedidos en total. Pero como se ve, aún falta calcular el monto total de cada uno de los pedidos, así como los precios de cada artículo pedido; para lo cual primeramente vamos a escribir las fórmulas que nos ayuden a obtener estos datos faltantes.

Cómo obtener los Precios de cada Pedido:

Primeramente vamos a dar nombres de rango a cada columna y a cada fila de la lista de precios. Realice usted entonces las siguientes acciones:

1.- Seleccionar el rango de la lista de precios B3:E6. 2.- En la ficha Formulas, grupo Nombres definidos, elegir el comando Crear desde la selección y aparecerá una ventana de diálogo. 3.- Verificar que estén marcadas las casillas Fila superior y Columna izquierda, y luego presionar Enter. 4.- Luego en la celda H11 escribir la siguiente fórmula:

=INDIRECTO(E11) INDIRECTO(F11)

5.- Copie esta fórmula hacia abajo con un doble clic en el cuadro de relleno.

Para obtener el cálculo de la columna Total: 6.- En la celda I11 escribir la fórmula que multiplique la cantidad por el precio:

=G11*H11

7.- Seguidamente copie igualmente esta fórmula hacia abajo con un doble clic en el cuadro de relleno.

La Función INDIRECTO

La sintaxis de la función es la siguiente:

=INDIRECTO(Referencia)

Donde: Referencia Es una coordenada de celda o un nombre de rango dentro del cual existe escrito otra referencia de celda o nombre de rango.

c. Manejo de Listas de Datos

Para trabajar con listas de datos es muy útil conocer algunas de las herramientas más usadas de la ficha Datos y en la ficha Insertar; estas son:

Ordenar listas Filtrar datos Obtener Subtotales múltiples Crear Tablas dinámicas Consolidar múltiples cuadros

Ejercicio: Manejo de una Lista de Pedidos

Una vez realizado el ejercicio anterior, la lista de pedidos estaría completa, pero si se desea analizar cuantos pedidos deben ser entregados mensualmente, o a que clientes, o que artículos y cual vendedor atendió el pedido, entonces la labor es muy complicada debido a que la lista se encuentra totalmente desordenada. Veamos ahora un método más completo para ordenar listas de datos.

d. El Comando Datos/Ordenar

Ordenar la lista para que lo usen en Almacén:

Suponga que el día de mañana hay que realizar la entrega de todos los pedidos que corresponden al mes de Enero, así que podríamos imprimir una copia de esta lista de pedidos y entregarla al encargado del almacén para que vaya cargando el camión con toda la mercadería. Pero, como ya se ha visto, al estar desordenada la lista, ésta haría que el trabajo de extraer las diferentes cajas de artículos del almacén sea una labor algo difícil que llevaría quizás a errores de confusión de pedidos y se estaría entregando un pedido por otro.

Entonces, lo que se va ha hacer es lo siguiente: Primero ordenar la lista separando los pedidos por meses, es decir los de Ene luego Feb y finalmente Mar. De este modo se tendría separada ya toda la relación de pedidos que corresponden al mes de Ene.

Luego en la misma lista también se ordenará para cada mes los artículos, o sea Televisores, Lavadoras y Refrigeradoras por separado, y finalmente cada artículo a su vez se ordenará según las diferentes marcas, Daewoo, LG y Samsung.

Para poder lograr este ordenamiento se procederá de la siguiente forma:

1.- Primero seleccionar una de las celdas de la lista de pedidos. (B12 por ejemplo).

2.- En la ficha Datos hacer clic en el comando Ordenar, y aparecerá la siguiente ventana de diálogo:

3.- Elegir las 3 columnas a ordenar y adicionalmente también el criterio de ordenación. Clic en Aceptar, y entonces el cuadro de pedidos se vería así:

Ordenar la lista para que lo use el que realiza la entrega con el camión:

Una vez cargados todas las cajas de productos en el camión, el conductor deberá realizar las entregas de los pedidos a cada uno de los clientes que se encuentran en diferentes distritos de la ciudad, los cuales están indicados en la lista, pero para esta ocasión, no están ordenados como se desearía.

Ahora, vamos a realizar un nuevo ordenamiento, y para este caso se deberán ordenar los pedidos en base a los lugares de entrega y a los clientes, tal como se observa en la siguiente ventana de diálogo:

Entonces el cuadro de pedidos será fácil de usar para ir de distrito en distrito y realizar ordenadamente la entrega según los clientes que haya en cada lugar.

El Cuadro se vería como se muestra en la siguiente figura:

e. El Comando Datos/Filtro

Filtrar la lista para consultar ciertos pedidos:

Sabemos que la lista en total era de 90 pedidos. Pero, sin importar cuantos existan en total, en cierta ocasión se desea saber cuántos de los pedidos que se entregaron en Enero, fueron de Daewoo. Y en otra oportunidad, se desearía saber cuántos pedidos en el distrito de San Miguel fueron vendidos por el vendedor Salazar. Y así por el estilo, se pueden necesitar realizar diferentes consultas a la lista.

Esto se puede lograr con la ayuda del comando Datos/Filtro; y se procede de la siguiente forma:

1.- Seleccionar una celda cualquiera del Cuadro de Pedidos.

2.- En la ficha Datos hacer clic en el comando Filtro, entonces el cuadro de pedidos se vería así:

A continuación vamos a realizar diversas consultas sobre los pedidos de los clientes:

Cuántos pedidos del mes de Enero son de Televisores Daewoo?

1.- Hacer un clic en el botón de filtro del Mes, desactivar la casilla (Seleccionar Todo) y elegir la opción Ene. Luego Aceptar.

Esto hará que de la lista de 90 pedidos solo queden visibles los que pertenecen al mes de Ene, los demás pedidos quedarán ocultos.

2.- Luego hacer un clic en el botón de filtro del Artículo y elegir la opción Televisor.

3.- Finalmente hacer un clic en el botón de filtro de la Marca y elegir la opción Daewoo.

Como se observa, existen entonces 6 pedidos que cumplen con la condición que se había solicitado: (Televisor Daewoo para Enero)

Para volver a mostrar la lista de pedidos completa

1.- Hacer un clic en el comando Borrar Y la lista vuelve a mostrar las filas ocultas y los encabezados de fila ya no aparecen de color azul sino otra vez de color negro.

Cuántos pedidos de Salazar son de Refrigerador para la tienda Elektra?

1.- Elegir en los botones de filtro las opciones Salazar, Refrigerador y Elektra respectivamente.

Observe que en la figura está seleccionada la celda G101 en la cual aparece el número 238, que en realidad es el resultado de la fórmula que está visible en la barra de fórmulas. Esta función está sumando el rango G11:G100, pero la función solo considera las celdas que se encuentran visibles e ignora las celdas que se han ocultado por acción del filtro.

La Función SUBTOTALES

Esta función posee dos argumentos y permite obtener no solo la suma de un rango de celdas, sino 11 diferentes operaciones de cálculo:

=SUBTOTALES(# de operación, Rango a calcular)

Donde:

# de operación es un número del 1 al 11 que indica la operación a realizar:

 
  1. Promedio
  2. Contar
  3. Contara
  4. Máximo
  5. Mínimo
  6. Producto
  7. Desviación estándar
  8. Desviación estándar de la población
  9. Suma
  10. Varianza
  11. Varianza de la población
 

Rango a calcular Es un rango de celdas en el cual se han ocultado ciertas filas por acción de un filtro.

 

Para obtener los Subtotales de la fila 101

1.- Seleccionar la celda G101. (No seleccione la columna, sino una sola celda) 2.- Hacer un clic en el botón de herramientas Autosuma. 3.- Presionar Enter.

La fórmula que se habrá escrito será la siguiente función:

 

=SUBTOTALES(9,G11:G100)

 

El numero 9 indica que se va a Sumar el rango indicado.

4.- Luego seleccionar la celda I101. 5.- Hacer un clic en el botón de herramientas Autosuma. 6.- Presionar Enter.

La fórmula en esta ocasión será:

 

=SUBTOTALES(9,I11:I100)

 

7.- A continuación copie con el cuadro de relleno el contenido de la celda G101 hacia la celda F101 que está a su izquierda. 8.- Luego seleccionando la celda F101, en la barra de fórmulas modifique la formula cambiando el número 9 por el número 3.

 

=SUBTOTALES(3,F11:F100)

 

Cuál de las Tiendas tiene la mayor cantidad de pedidos en Enero?

1.- Elegir el menú Datos/Ordenar y Filtrar/Borrar. 2.- Filtrar los pedidos de Ene. 3.- Filtrar al cliente Hiraoka.

En la celda G101 aparecerá que Hiraoka tiene para Abril la cantidad de 6 pedidos.

4.- A continuación filtrar al cliente Elektra.

En la celda G101 aparecerá que Elektra tiene la cantidad de 10 pedidos.

5.- Y si luego se filtra al cliente Carsa.

Entonces en la celda G101 aparecerá que Carsa tiene la cantidad de 15 pedidos.

Cuántos pedidos de Carsa para Enero tienen un monto entre 500 y 1000 soles?

1.- Hacer clic en el botón de filtro del título Monto y elegir Filtros de número/Filtro Personalizado. 2.- En la ventana de diálogo elegir las opciones que se ven en la figura y luego hacer clic en Aceptar.

 

La lista deberá dar los siguientes resultados:

1.- Seleccionar todas las fórmulas de Subtotales que se encuentran en la fila 101 y bórrelas con la tecla Suprimir. 2.- En la ficha Datos elegir el comando Filtro. Desaparecerán los botones de Autofiltro de nuestra lista de pedidos y la lista se verá entonces como en su forma original, y estarán nuevamente visibles los 90 pedidos.

e. El Comando Datos/Subtotal

Para averiguar quién es el mejor de nuestros Vendedores:

1.- Seleccione una de las celdas con el nombre de un vendedor, y haga clic en el botón de herramientas Ordenar de A a Z. Esto hará que los pedidos se ordenen alfabéticamente por vendedor. 2.- En la ficha Datos elegir el comando Subtotal

 

Aparecerá entonces la siguiente ventana de diálogo:

 

 

Elegir en la ventana las opciones señaladas y luego clic en Aceptar. En la lista de pedidos inmediatamente aparecerán subtotales al final de cada vendedor, y a la izquierda de la hoja de cálculo aparecerán también los botones de Agrupar y Esquema.

 

3.- Hacer un clic en el botón número 2 de Agrupar y esquema que se encuentra a la izquierda de los encabezados de columna. 4.- Finalmente seleccionar el monto total de uno de los vendedores y hacer un clic en el comando Ordenar de Z a A.

Para averiguar quién es el mejor de nuestros Clientes

 

1.- En la ficha Datos/Subtotal hacer clic en el botón [Quitar todos]. Esto hará que desaparezcan los subtotales de los vendedores. Luego se repiten los mismos pasos que realizamos para averiguar quién era el mejor vendedor, pero ahora tomamos en cuenta la columna de Tienda. 2.- Seleccione una de las celdas con el nombre de una Tienda, y haga clic en el botón de herramientas Ordenar de A a Z. 3.- En la ficha Datos/Subtotal, elegir las opciones que se muestran en la ventana de diálogo adjunta, y luego clic en el botón [Aceptar]

4.- A continuación clic en el botón 2.

5.- Finalmente seleccionar el monto total de uno de las Tiendas y hacer un clic en el botón de Ordenar de Z a A.

Los gráficos son el método más útil y práctico para analizar de manera más objetiva los resultados numéricos que pueden obtenerse en nuestros cuadros. Además que en Excel la creación de los gráficos es una labor muy sencilla y relativamente rápida.

Ejercicio: Graficar un Cuadro de Exportaciones

a. Para crear un Gráfico de Columnas verticales

1.- En una hoja de cálculo en blanco escriba el siguiente Cuadro de Producción:

2.- Seleccione el rango A3:D9 en el cuadro de Producción. 4.- En la ficha Insertar elija el comando Columna y en la galería haga clic en la opción Columna agrupada 3D.

Si el grafico se encuentra seleccionado se activará automáticamente en la cinta de opciones la ficha Diseño de la barra de Herramientas de gráficos, y junto con la ficha Diseño estará también la ficha Formato. Las dos fichas servirán para diseñar, y dar diferentes formatos al grafico recién creado. La ventana de Excel entonces se verá así:

Modifique el Diseño del gráfico:

1.- En la ficha Diseño, abra la galería Diseño rápido y elija el Diseño5 para agregar títulos y una tabla de datos en la parte inferior del gráfico:

2.- Escriba “Producción del Año 2014” como título principal en el gráfico y también “Miles de Paquetes” como título del eje vertical. Luego señale la tabla de datos y cuando aparezca el mensaje “tabla de datos” haga un clic para seleccionarla; y luego en la ficha Inicio cambie el tamaño de Fuente a 8 puntos.

3.- Ahora vamos a poner la producción mensual una encima de otra. En la ficha Diseño/Cambiar tipo de grafico aparecerá una ventana de dialogo en la cual deberá elegir la figura Columna 3D apilada como se muestra en la figura, y luego Aceptar.

Al lado derecho del grafico aparecen tres botones de diseño rápido. Estos botones son los siguientes: Elementos de gráfico, Estilos de gráfico, y Filtros de gráfico.

4.- Haga un clic en el botón Estilos de grafico/Estilo/Estilo6, tal como se muestra en la figura anterior. El grafico se verá entonces así:

5.- Luego haga un clic en el botón Estilos de grafico/Color/Color3. 6.- Luego señalando los Planos laterales hacer un clic derecho y elegir Formato de planos laterales.

7.- Seleccione RELLENO/Relleno con degradado, y en Degradados preestablecidos hacer clic en el color Foco de luz superior – Énfasis 5.

8.- En el grafico seleccione el Plano inferior. Luego en el panel de tareas de la derecha elegir Relleno sólido, y en Color elegir Blanco, Fondo1.

9.- Haga un clic en el botón de diseño rápido Elementos de Grafico y marque las opciones Etiqueta de datos, Leyenda, y desactive la opción Tabla de datos, tal como se muestra en la figura.

10.- Haga un clic en una de las columnas y en la barra de tarea de la derecha elija Opciones de serie, y en Ancho del intervalo dele el valor de 60% tal como se muestra en la siguiente figura.

11.- Ahora de va a agregar al grafico los meses Abr, May y Jun. Seleccione el rango E3:G9 y presionar Ctrl+C. Luego seleccione el grafico con un clic dentro de él, y presione las teclas Ctrl+V. Finalmente el grafico deberá verse así:

b. Para crear un Gráfico de Sectores Circulares

Otro tipo de gráfico muy utilizado es el gráfico de sectores circulares, Este gráfico se emplea cuando se quiere mostrar los datos en forma porcentual. Veamos el siguiente caso para graficar la Producción en el mes de Abril.

Gráfico de Sectores Circulares:

1.- Seleccionar los rangos A3:A9 y E3:E9 (la columna de marcas y los datos del mes de abril). Luego en la ficha Insertar haga clic en el comando Circular y en la galería elija la opción Gráfico circular 3D.

2.- Aparecerá el siguiente grafico en la pantalla. Muévalo con el mouse y ubíquelo sobre el rango H10:M24. 3.- Haga un clic en el botón Estilos de Grafico/Estilo/Estilo7. 4.- En la barra de tareas de la derecha elija RELLENO/Relleno con imagen y textura, y luego haga clic en el botón Archivo, y elija un archivo de imagen para el fondo del gráfico. 5.- En la misma barra de tareas de la derecha elija BORDES/Esquinas redondeadas.

5.- Seleccione uno de los sectores del gráfico y separe del grafico el sector correspondiente a la producción de Scott arrastrándolo con el mouse radialmente hacia atrás. El grafico deberá verse como se muestra a continuación.

c. Para crear un Minigráfico de Líneas

En la versión 2010 y 2013 de Excel hay ahora la posibilidad de crear Minigráficos, que no son otra cosa que unos gráficos diminutos que aparecen en el interior de una celda. Estos gráficos pueden ser de Línea, de Columna o de Ganancia o pérdida.

Para crear minigráficos de Línea haga lo siguiente:

1.- Seleccionar el rango H4:H9 y en la ficha Insertar elegir Minigráfico de Línea.

2.- Seleccionar el rango B4:G9 y luego clic en Aceptar.

3.- Seleccionar la Ficha Diseño de Herramientas para minigráficos.

4.- En el comando Color de marcador/Marcadores, elegir el color Verde.

5.- En el comando Color de marcador/Punto alto, elegir el color Azul.

6.- En el comando Color de marcador/Punto bajo, elegir el color Rojo.

d. Formatos condicionales de iconos y barras horizontales

1.- En el rango I4:I9 calcular con autosuma el total de la producción de enero a junio con la siguiente formula:

=SUMA(B4:G4)

2.- Seleccionado el rango I4:I9 en la Ficha Inicio/Formato condicional/Conjunto de iconos, elegir 5 flechas de color como se muestra en la figura siguiente.

3.- En la celda J4 escribir =I4 y copiar esta fórmula en J5:J9 4.- Seleccionar J4:J9 y en la Ficha Inicio/Formato condicional/Barra de datos elegir uno de los modelos de barra que se muestra.

5.- Seleccionado J4:J9, en la Ficha Inicio/Formato condicional/Administrar reglas; seleccionar en la ventana la regla Barra de datos y clic en el botón Editar regla…

Luego en la siguiente ventana marcar la casilla Mostrar solo la barra, y luego Aceptar.

6.- En el rango K4:K9, escribir y copiar la siguiente fórmula para el cálculo del porcentaje:

=I4/SUMA(I$4:I$9)

y luego darle el formato de porcentaje.

 

e. Para crear un gráfico simple de barras horizontales

Lo que veremos a continuación no es un gráfico propiamente dicho como los anteriores que hemos creado. Este grafico simple, es el resultado de unas fórmulas escritas en las celdas donde se desea ver un gráfico de barras horizontales, que permitan comparar algunas cantidades numéricas existentes en las celdas adyacentes situadas a la izquierda.

Para obtener el resultado de la imagen anterior haga lo siguiente:

1.- En la celda L4 escribir la fórmula:

=REPETIR(”█”,L4/32) (El carácter █ se escribe con el código ASCII: Alt+219)

Luego copiar esta fórmula hacia abajo hasta la celda J8. 2.- A continuación, seleccione una a una las celdas con estas fórmulas y dele los colores de fuente que desee. (Utilice el comando de color de fuente, no el de color de relleno). 3.- Finalmente en las celdas L3 y M3 escriba los números 250 y 500; deles formato y bordes, tal y como se ve en la figura anterior.

1. Inmovilizar títulos en filas y columnas

Si se tiene un documento de muchas filas y columnas, que no caben en la pantalla, ¿cómo hacer para poder mirar los datos en las filas y columnas sin perderse dentro de ellas?, y esto es así; ya que los títulos de las columnas no serían visibles si nos movemos muy abajo; y si nos vamos hacia la derecha sobre las ultimas columnas del cuadro, de igual modo ya no veríamos a quien pertenecen esos datos pues las primeras columnas se habrían ocultado en la pantalla.

Para solucionar este problema en Excel existe la posibilidad de inmovilizar los títulos en filas y columnas, y la manera de lograrlo se explica a continuación:

a. Para inmovilizar paneles en la ventana de Excel

Abra un documento de Excel que contenga gran cantidad de filas y columnas escritas. Para nuestro caso abriremos una Planilla de Salarios de Obreros.

Como puede verse, si nos desplazamos en la hoja hacia abajo, ya no veríamos los títulos de la fila 7, y de igual manera si nos movemos hacia la derecha para ver los demás rubros de pago, ya no veríamos de cual obrero son esos datos.

La solución es la siguiente:

1. Acomodar las columnas y filas del cuadro a fin de que estén visibles los títulos que deseamos inmovilizar en la pantalla, tal como se observa en la figura anterior. 2. Luego ubicar el puntero en la celda C8. 3. Llamar a la Ficha Vista/Inmovilizar/Inmovilizar paneles.

4. De este modo, le hemos indicado a Excel que todas las filas que se encontraban encima del puntero de celda (filas de la 1 a la 7), y las columnas situadas a la izquierda del puntero de celda (columnas A y B) se queden inmóviles, y ya no se escondan al movernos en el cuadro. Entonces podremos irnos a cualquier parte del cuadro, y siempre veremos los títulos de la fila 7 y los códigos y nombres de los obreros, como puede verse en la siguiente figura:

5. Si en todo caso, ya no se desea tener los paneles inmóviles y se quiere volver al estado normal de la hoja, se llama a la Ficha Vista/Inmovilizar/Movilizar paneles.

 

2. Dividir la ventana de Excel en secciones

Cuando en una hoja de Excel existan diversos cuadros y tablas que por estar distantes entre sí no es posible verlos simultáneamente en la pantalla; allí es donde se puede recurrir a dividir la pantalla en secciones y desplazarse en casa sección independientemente para poder así ver los cuadros o tablas que deseamos.

a. Para Dividir secciones en la ventana de Excel

Abra un documento de Excel que contenga en una de sus hojas varios cuadros distintos. Para nuestro caso usaremos este archivo cuyo contenido son varios cuadros con créditos bancarios del año 2010 al 2014, y sus correspondientes tablas de tasas máxima y mínima, como se puede ver en la siguiente imagen.

Si se deseara ver en pantalla para comparar el cuadro de créditos bancarios del año 2010 y del año 2013; dado que estos cuadros están muy separados uno del otro se hace muy difícil verlos simultáneamente, entonces; lo que se puede hacer es dividir la ventana en dos y que cada sección muestre la parte de la hoja que se desea ver.

Entonces haríamos lo siguiente:

1. Acomodar visible en pantalla el cuadro del año 2010 y seleccionar la celda A12. 2. Elegir la ficha Vista/Dividir.

3. Luego estando el puntero en la sección inferior desplazar esta hacia abajo para mostrar en ella el cuadro de créditos bancarios del año 2013, tal como se aprecia en la siguiente imagen.

4. Si luego, se desea retirar la división de secciones; sin importar donde se encuentre el puntero de celda, bastará con que en la ficha Vista vuelva a hacer clic en el comando Dividir, y la división de secciones desaparecerán, regresando la ventana a su estado original.

Dividir la ventana en 4 secciones:

También es posible realizar una división cuádruple de la ventana. Esto nos permitiría ver simultáneamente no solo cuadros que están distantes verticalmente, sino también distantes horizontalmente.

5. En este caso ubique en pantalla el cuadro del año 2010 y seleccione la celda H14 6. Luego elegir la ficha Vista/Dividir. 7. A continuación desplazar cada sección para poder ver e ellas los cuadros y tablas que se desean.

3. Manejo de ventanas

Cuando se tenga un documento de Excel donde se hayan utilizado varias hojas del libro, quizás se desee ver simultáneamente varias de estas hojas en la pantalla; allí es donde se hace necesario crear múltiples ventanas en Excel para ver el contenido de las diferentes hojas de un mismo documento. A continuación pasamos a describir como se realiza la creación de ventanas en Excel.

b. Para crear una nueva ventana en la pantalla de Excel

Abra un documento de Excel que contenga varias hojas utilizadas como la que se muestra a continuación en este ejemplo. El libro que se ha abierto contiene una hoja de Facturas, otra de Boletas, de Pedidos, una hoja de Lista de Precios, otra de Registro de Ventas, y una de Clientes.

Para crear diferentes ventanas múltiples, siga los siguientes pasos:

1. Elija la Ficha Vista/Nueva ventana, por 2 veces.

 

c. Para Organizar las ventanas en la pantalla de Excel

2. Ahora elija la Ficha Vista/Organizar todo, y en la ventana que aparece elegir la opción Mosaico y hacer un clic en Aceptar.

3. La pantalla aparecerá dividida en 3 partes. En la ventana de la izquierda seleccionar la hoja Pedido, en la ventana superior seleccione la hoja Precios; y finalmente en la ventana inferior seleccione la hoja Registro. Aparecerá la siguiente imagen en la pantalla:

Si tuviera abiertos otros libros de Excel, al elegir la opción Organizar Todo, entonces también estos otros archivos de Excel se mostrarían simultáneamente con las ventanas del libro anterior.

Si se abriesen dos archivos en dos ventanas verticales, estas se podrían poner en paralelo y luego sincronizarlas para poder comparar los datos que cada archivo tenga en sus respectivas listas. Para esto se utilizarían los comandos de la ficha Vista/Ver en paralelo, y luego Vista/Desplazamiento sincrónico.

1. Imprimir la Hoja de Cálculo

Una vez terminado el trabajo en la hoja de cálculo, hay la necesidad de imprimir nuestro trabajo. Esto puede consistir en imprimir todo el contenido de las hojas de cálculo o quizás solo se desee imprimir parte de él, o únicamente imprimir los gráficos. A continuación pasamos a describir como se realiza la impresión de documentos en Excel.

a. Para imprimir un documento de una sola pagina

Vamos a imprimir el archivo Gráficos de Producción.xlsx, entonces; abra el archivo y muéstrelo en la pantalla. Luego siga los siguientes pasos:

1. Elija la Ficha Archivo/Imprimir. Aparecerá la siguiente imagen en la pantalla:

Como puede verse, el documento no aparece completo en el papel, el grafico esta recortado, y lo que quisiéramos es que se imprima completo y en una hoja tamaño A4 dispuesta horizontalmente y centrada.

2. Primeramente en las opciones del lado izquierdo cambiar la Orientación Vertical por Orientación horizontal.

3. Luego de ser necesario cambiar el tamaño de papel Carta por el tamaño de papel A4.

4. Ahora con el mouse haga clic en la opción Configurar página situado en la parte inferior, y en la ventana que aparece, en la ficha Márgenes, en la opción Centrar en la página de la parte inferior activar las casillas: Horizontalmente y Verticalmente. Luego Aceptar.

El documento estará entonces listo para ser impreso. Solo bastara con elegir cuantas copias desea imprimir y luego haga clic en el botón Imprimir.

b. Para imprimir únicamente un gráfico de Excel

Ahora vamos a imprimir el grafico de coordenadas cartesianas por separado en una hoja de papel.

1. En la hoja de cálculo, seleccionar el grafico que se desea imprimir haciendo un clic dentro de él. 2. Elija la Ficha Archivo/Imprimir. Aparecerá la siguiente imagen en la pantalla:

3. Como se verá, y sin importar de qué tamaño o tipo haya sido el grafico elegido, este grafico ya estará listo y centrado para ser impreso. Solo tendrá que hacer clic en el botón de imprimir.

c. Para imprimir un documento de varias páginas.

Vamos a imprimir un documento semejante al cuadro de pedidos de artefactos eléctricos, desarrollado en un capitulo anterior de este manual.

1. Abra el archivo de Cuadro de Pedidos, y elija la Ficha Archivo/Imprimir. Aparecerá la ventana de vista previa de impresión en la pantalla.

Como puede verse, el listado de pedidos no aparece completo para imprimirse en el ancho del papel. Habrá que realizar entonces lo siguiente:

2. Con el mouse haga clic en la opción Configurar página situado en la parte inferior, y en la ventana Configurar página aparecerán 4 fichas como puede verse en la figura adjunta.

3. En la Ficha Página: En Ajuste de escala, marque la opción “Ajustar a 1 página de ancho por 10 de alto”. Y en Tamaño del papel, elija el tamaño A4.

4. En la Ficha Márgenes: En “Centrar en la página” marque la casilla Horizontalmente.

5. En la Ficha Encabezado y pie de página: Haga un clic en el botón Personalizar encabezado…

En la ventana Encabezado que se muestra a continuación, haga un clic en la Sección derecha y escriba: “Pag.” luego haga un clic en el botón Insertar número de página, seguidamente escriba la palabra “de” y luego haga clic en el botón Insertar número de páginas, y Aceptar.

Ahora haga un clic en el botón Personalizar pie de página…

En la ventana Pie de página que se muestra, haga un clic en la Sección izquierda y escriba su nombre y apellido, seleccione lo escrito y con el botón Aplicar formato al texto dale tamaño de letra de 6 puntos. En la Sección central, ingrese la fecha y hora utilizando los botones correspondientes. Y en la Sección derecha inserte la Ruta del archivo con el botón respectivo. Finalmente haga clic en Aceptar, y Aceptar nuevamente. Entonces vera la vista preliminar del documento como se muestra a continuación:

Para lograr que se repita en todas las páginas los títulos del Cuadro de Pedidos, se debe hacer lo siguiente:

6. Salir del modo vista preliminar presionando la tecla Esc.

7. En la Ficha Diseño de página/Imprimir títulos, aparecerá nuevamente la ventana de Configurar página.

8. En la ficha Hoja, hacer un clic en la casilla de texto Repetir filas en extremo superior y con el mouse seleccionar en la hoja de cálculo las filas 8, 9 y 10. Finalmente hacer un clic en Aceptar.

Si ingresa nuevamente a la Ficha Archivo/Imprimir, y avanza a la página 2, verá como en esta página se repiten los títulos de las filas 8, 9 y 10 del Cuadro de Pedidos.

9. Solo bastará con hacer clic en el botón Imprimir, para enviar el documento a la impresora e imprimir todas las páginas del cuadro de pedidos.