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
"Autorrellenar", 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
Con ello,
conseguimos que al copiar y pegar o bien al utilizar "Autorrellenar"
siempre mantenemos la referencia a la misma celda:
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.
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.
Como desbloquear algunas celdas
Por lo general las celdas se mantienen bloqueadas, el candado está cerrado.
Antes de bloquear la hoja debemos romper el candado dándole un clic a la opción:
Seleccionar las celdas a desbloquear.
Ficha inicio grupo celdas
Comando formato
Opción Bloquear celdas.
Por lo general las celdas se mantienen bloqueadas, el candado está cerrado.
Antes de bloquear la hoja debemos romper el candado dándole un clic a la opción:
Seleccionar las celdas a desbloquear.
Ficha inicio grupo celdas
Comando formato
Opción Bloquear celdas.
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.
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
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.
Ejercicios de Excel Avanzado
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
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
Ejercicio función SI
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.
0 comentarios: