Excel Avanzado

Referencias de celdas en una formula

• Una referencia es el nombre de la celda donde se encuentra el dato que deseamos trabajar u operar, tiene la sintaxis A1, donde A es la referencia de columna y 1 es la referencia de fila. Existen tres tipos de referencias de celdas: Relativas, Absolutas, Mixtas.
• Referencias de celdas Relativas
• Una referencia relativa a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", se ajusta automáticamente para hacer referencia a otras celdas.
• Son las referencias más utilizadas y las que, en la mayoría de las veces, se obtienen por defecto en Excel al hacer clic en una celda para hacer referencia a ella.

Referencias de celdas  Absolutas

•Una referencia absoluta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorellenar", NO se ajusta y queda bloqueada haciendo referencia siempre a la misma celda.

•Para hacer una referencia absoluta a una celda, se deben introducir los símbolos del dólar $ antes de la letra y antes del número de una referencia normal del tipo A1. Es decir, una referencia absoluta a la celda A1, sería: $A$1

Referencias de celdas  Mixtas

• Una referencia mixta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", ajusta sólo la letra o sólo el número de la referencia, quedando bloqueado sólo el número o sólo la letra respectivamente.
• Por lo tanto, si queremos bloquear la letra, debemos colocar el símbolo del dólar "$" antes de la letra y si queremos bloquear el número, debemos colocar el símbolo del dólar "$" antes del número. 
• Las referencias mixtas son útiles en multitud de ocasiones, especialmente cuando tenemos que utilizar "Autorrellenar" en una matriz de celdas (varias celdas x varias celdas), puesto que nos permite "arrastrar" una fórmula introducida en una esquina, hasta la celda opuesta en la matriz, para operar con los encabezados de columnas y filas rápidamente y de una sola vez. Podemos ver en la siguiente imagen una referencia mixta preparada para "arrastrar"

Proteger libros pasos

Para mantener los datos de una hoja de cálculo a salvo e impedir que inesperadamente se realicen cambios en las mismas, Excel permite proteger hojas, celdas así como todo el libro.

Estas herramientas están disponibles en la pestaña Revisar y en el grupo de trabajo denominado Cambios. En donde podrá  observar que están entre otros los iconos Proteger hoja y Proteger libro.

pasos
   Abra el libro de Excel que desea proteger.
  Colóquese en la hoja a proteger vaya a la ficha Revisar, dentro del grupo Cambios pulsa en   Proteger  hoja.
  Se abre una ventana nueva, fíjese que este marcada las casillas donde dice : Proteger hoja y   contenido de las celdas bloqueadas, así como las dos opciones superiores de la lista, luego haga clic   en Aceptar.
 Si prueba introduciendo datos en la hoja vera que le muestra un mensaje indicando que la hoja es     solo de lectura y que no puede modificarla.
  Para desproteger la hoja basta con pulsar en el icono Desproteger hoja.

Si en algún momento estamos trabajando en un libro de Excel y dicho archivo será compartido a otras personas, debemos asegurarnos que se respetara  la estructura del mismo, es decir, que no se permita eliminar hojas, ni adicionar nuevas hojas y que tampoco se permita la modificación del nombre de dichas hojas, Excel por defecto lo permite.

 Si no deseas que modifiquen la estructura de tu libro de Excel la opción a utilizar es bloquear el libro con protección de la estructura, los pasos son los siguientes:

Pasos

  Seleccionar Ficha Revisar
 En grupo Cambios seleccionar opción Proteger libr
 Validar que la opción Estructura esté seleccionada, de lo contrario hacer clic sobre dicha opción
 Ingresa contraseña de bloqueo y Clic en Aceptar
 Repite la contraseña y Clic en Aceptar
 De esta forma la protección de la estructura del libro está bloqueada, para desbloquear o habilitar   nuevamente la modificación de la estructura realiza los mismos pasos que se aplicó en el bloqueo,   solo que esta vez  Excel te solicitará la clave que ingresaste en el bloqueo.

Ordenar datos en una tabla

Ordenar los datos es una parte esencial del análisis de datos. Puede que desee poner una lista de nombres en orden alfabético, compilar una lista de niveles de inventario de productos de mayor a menor u ordenar filas por colores o por iconos. Ordenar los datos ayuda a verlos y a comprenderlos mejor, así como a organizarlos y encontrarlos más fácilmente y a tomar decisiones más eficaces

Pasos para ordenar

Para ordenar rápidamente los datos, realice lo siguiente:

1.      Seleccione un rango de datos, por ej: (varias filas y columnas o una sola columna).

2.      Haga clic en orden ascendente para clasificar (de A a Z o del número menor al mayor).

3.      Haga clic en para clasificar en orden descendente (de Z a A o del número mayor al menor).

Para ordenar según un criterio específico, realice lo siguiente:

     1.      En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Ordenar.

     2.      Aparecerá el cuadro de diálogo Ordenar.

     3.      En la lista Ordenar por, seleccione la primera columna en la que desea ordenar.

     4.      En la lista Ordenar según, seleccione Valores, Color de celda, Color de fuente o Icono de celda. NOTA: Cuando se ha aplicado un color especial a las celdas, Excel habilita la opción Filtrar por color

     5.      En la lista Orden, seleccione el orden en el que desea aplicar la operación de ordenación: en forma alfabética o numérica, ascendente o descendente (es decir, de la A a la Z o de la Z a la A para texto o bien, de menor a mayor o de mayor a menor para los números).

Filtros

Aplicar filtros es una forma rápida y fácil de buscar un subconjunto de datos de un rango y trabajar con el mismo.
Un rango filtrado muestra sólo las filas que cumplen el criterio que se especifique para una columna. Microsoft Excel proporciona dos comandos para aplicar filtros a los rangos:
Autofiltro, que incluye filtrar por selección, para criterios simples
 Filtro avanzado, para criterios más complejos

Pasos para filtrar u ordenar datos

Seleccione los datos que desee filtrar.

En el grupo Ordenar y filtro de la ficha Datos, haga clic en Filtro.

Haga clic en la flecha del encabezado de columna para mostrar una lista de la cual puede seleccionar filtros.

Para seleccionar según valores, desactive la casilla de verificación (Seleccionar todo) en la lista. Mediante esta acción se desactivan todas las casillas de verificación. Luego seleccione únicamente los valores que desea ver y haga clic en Aceptar para ver los resultados.

Ejercicio de filtros

Procedimiento:

     1.      Seleccione la primera fila de la tabla que corresponde a los títulos de cada columna

     2.      Aplique filtros

     3.      Clic en la ficha datos en el grupo ordenar y filtrar, damos clic en filtro

     4.      Cuando aplique filtro aparece una flecha a la par del título de cada columna la cual la podemos ordenar o filtrar de acuerdo a lo que se pide

     5.      Ya filtrados los datos, seleccione la tabla, damos clic derecho, copiar y trasladamos a otra hoja para pegar solamente los datos filtrados que se piden en el ejercicio

     6.      Sucesivamente haciendo el mismo paso para los demás datos que se piden en el ejercicio

t    

Ejercicios CONTAR.SI, SUMAR.SI, PROMEDIO.SI

En este ejercicio vimos tres funciones condicionales: contar.si, sumar.si y promedio.si

Procedimiento:

Procedimiento:

Función conta.si

     1.      La función contar.si se muestra en la segunda tabla en la parte de identificar los números de vendedores de San Salvador y Mejicanos

     2.      Primero tuve que digitar la función contar.si o desde insertar función se puede aplicar más fácilmente la función

     3.      La función pide un rango el cual seria el rango de la columna ruta

     4.      Pide un criterio el cual seria San Salvador o Mejicanos

     5.      Di clic en insertar y la función cuenta solamente las celdas en que se encuentra la palabra San Salvador o Mejicanos

Función sumar.si

     1.      En el caso de esta función pide rango, criterio y rango de suma, el ejercicio pide obtener el total de ventas de mejicanos y también pide las ventas inferiores a

     2.      3000

     3.      El rango en este ejercicio seria la columna ruta, criterio mejicanos y el rango de suma las ventas

     4.      Di clic en aceptar y automáticamente nos mostrara el total de ventas solo de mejicanos

     5.      Igualmente se hace el mismo procedimiento para obtener las ventas inferiores a 3000

     6.      En este caso solo ocuparía el rango y el criterio el cual seria el rango la columna de ventas y el criterio inferior a 3000  (<3000)

Función promedio.si

1.      Para esta función utilice  rango, criterio y rango promedio, en el ejercicio pide el total de ventas promedios en el departamento de Sal Salvador

2.      El rango seria la columna de ruta

3.      Criterio San Salvador

4.      Rango promedio seria el total de ventas

5.      Di clic y automáticamente muestra el total promedio de las ventas de San Salvador

Procedimiento:

     1.      El ejercicio pide que el alumno con promedio igual o mayor que 7 esta aprobado de lo contrario esta reprobado

     2.      Para este ejercicio se necesita una prueba lógica, valor si verdadero y valor si falso

     3.      Como en los ejercicios anteriores damos clic en insertar función y digitamos la función que queremos aplicar

     4.      La prueba lógica seleccione la celda donde queremos aplicar la función SI más que el promedio sea mayor que o igual que 7 (n°celda>7)

     5.      El valor si verdadero sería APROBADO

     6.      Valor si falso REPROVADO

     7.      Di clic en aceptar y automáticamente se muestran los alumnos aprobados y los reprobados

     8.      Por ultimo aplique formato condicional para que los aprobados aparezcan en verde y los reprobados rojos

Función CONTAR.SI.CONJUNTO

Procedimiento:

     1.      En esta función se necesita un rango criterio 1, criterio 1, rango criterio 2, criterio 2

     2.      Para realizar el primer punto de este ejercicio  inserte función, digite la función y la aplique

     3.      Para el criterio 1 de este ejercicio seria el rango de la columna de la ciudad

     4.      Para el criterio 1 seria san salvador ya que nos pide el porcentaje de viviendas de San Salvador

     5.      Para el rango criterio 2 de este ejercicio seria el rango de la columna de viviendas

     6.      El criterio 2 sería el rango de la columna de viviendas urbanas que sean mayor al 75% (>75%)

     7.      Di clic en aceptar y así se muestra el dato pedido en el primer punto del ejercicio ya realizado

Ejercicio tabla dinámica y gráfica dinámica

Procedimiento:

     1.      Para aplicar una tabla dinámica tuve que dar clic en la ficha insertar en el grupo tabla para el caso de las tablas y clic en tabla dinámica

     2.      Antes de eso tuve que tener digitada la tabla para seleccionarla y dar clic en tabla dinámica

     3.      Al dar clic se abrirá un panel de configuración el cual nos permitirá hacer diferentes cambios para la tabla para que así aparezcan distintos datos en distintos órdenes en la tabla

     4.      Para el caso de la gráfica dinámica es el mismo procedimiento solamente que esta función se encuentra en el grupo gráficos

Ejercicio filtros avanzados

Procedimiento:

1.      Para aplicar filtros avanzados tuve que dar clic en la ficha datos en el grupo ordenar y filtrar, clic en avanzados

2.      Pero antes de ello en la tabla de datos tuve que darle nombre a toda la tabla por ejemplo le podemos nombrar DATOS

3.      Tuve que dar nombre al rango de texto el cual queria trasladar los datos a la siguiente hoja

4.      Tuve que copiar el dato que se nos pide en el primer punto del ejercicio filtre la tabla tomando como criterio el puesto de trabajo filtre a todos los de ventas y asesor. Así que copie el dato puesto

5.      A continuación, a bajo de puesto digite el dato que queremos obtener: ventas y asesor

6.      Seleccione puesto, ventas y asesor y nombramos ese rango como por ejemplo CRITERIO

7.      Después de eso di clic en filtros avanzados, se abrirá el panel el cual pide rango de lista, rango de criterio y copiar a

8.      En el rango de lista seleccione la tabla la cual nombramos DATOS

9.      En rango criterio seleccione los datos los cuales se quieren filtrar el cual nombramos SALIDA

10.  Y en copia a seleccione la hoja a la cual la queremos trasladar

11.  Y finalmente se aplica el filtro avanzado.