Clase Veinte
Uso de referencias relativas y absolutas, prueba lógica compuestas y si
condicional anidado
Referencias Absolutas y Relativas.
Referencias Absolutas y Relativas.
Las fórmulas de Excel utilizan referencias de celda para incluir
los valores de dichas celdas en los cálculos. Existen dos tipos principales de
referencias: relativas y absolutas las cuales se comportan de una manera muy
peculiar dentro de nuestras hojas de Excel y es importante aprender a
distinguir entre ambos tipos.
Referencias relativas en Excel:
De manera predetermina Excel utiliza referencias relativas al momento de
insertar una referencia de celda en una fórmula. Las referencias relativas son
actualizadas automáticamente por Excel al momento de copiar la fórmula a otra
ubicación.
EJEMPLO:
En el siguiente ejemplo tenemos una fórmula en la celda E6 que realiza
la suma de varias celdas. La imagen tiene habilitado el rastreo de precedentes
para poder visualizar las celdas involucradas en el cálculo.
Al momento de copiar la fórmula una celda hacia abajo, descubriremos que
Excel modifica todas las referencias agregando una fila a todas ellas. Observa
el cambio en las referencias después de haber copiado la fórmula:
Si en lugar de copiar la fórmula hacia abajo, la copiamos hacia la
derecha, entonces Excel modificará las referencias de celda agregando una
columna a cada una de ellas de la siguiente manera:
De esta manera comprobamos que Excel actualiza las referencias de celdas
al momento de copiar una fórmula. Estas referencias se conocen como relativas
porque serán modificadas por Excel en relación con la celda a donde se copie la
fórmula.
Referencias absolutas en Excel:
A diferencias de las referencias relativas, las referencias absolutas no
se modifican aunque copiemos la fórmula a otras celdas. Para hacer que una
referencia se convierta en absoluta utilizamos el símbolo de moneda ($)
anteponiéndolo a la columna y fila de la referencia.
Tomando el mismo ejemplo anterior, voy a convertir la referencia a la
celda A1 en una referencia absoluta. En la siguiente imagen puedes ver en la
barra de fórmulas que de todas las referencias incluidas en la fórmula solo la
referencia a la celda A1 es absoluta:
Se puede notar que es una referencia absoluta porque está escrita como
$A$1. Hasta este momento no podemos notar diferencia alguna con el ejemplo anterior
ya que la suma sigue siendo la misma, pero veremos una diferencia al momento de
copiar la fórmula. Observa lo que sucede cuando copio la fórmula hacia abajo:
Toda las referencias has sido modificadas agregando una fila, pero la
referencia a la celda A1 se mantenido fija porque es la única referencia
absoluta en nuestra fórmula. Ahora observa lo que sucede al copiar la fórmula
de la celda E6 a la derecha:
Todas las referencias se desplazan una columna a la derecha, pero la
referencia a la celda A1 permanece constante aún después de haber copiado la
fórmula. Las referencias absolutas serán de mucha ayuda cuando necesitamos
indicar a Excel que considere siempre el valor de una misma celda sin importar
que la fórmula sea copiada a otra ubicación.
Un atajo para convertir una referencia relativa en una referencia
absoluta es colocar el cursor de la barra de fórmulas sobre la referencia que
deseamos transformar y entonces pulsar la tecla F4 para que Excel introduzca
los símbolos de moneda a un lado de la columna y de la fila.
Prueba Logica Compuesta y SI Condicional anidado
El primer argumento de la función SI es siempre una prueba lógica que
hace una comparación y devuelve como resultado el valor falso o verdadero. De
manera predeterminada podemos hacer una sola comparación, pero con el uso de la
función Y podemos exigir el cumplimiento de varias condiciones dentro de la
función SI.
Ejemplo:
Con el uso de las referencias relativas y absolutas, la prueba lógica y la función SI anidado, podemos sacar las comisiones de los diferentes vendedores si ellos cumplen con los objetivos que establece este ejercicio.
Con el uso de las referencias relativas y absolutas, la prueba lógica y la función SI anidado, podemos sacar las comisiones de los diferentes vendedores si ellos cumplen con los objetivos que establece este ejercicio.
Fórmula
con la que podemos sacar la ponderación promedio.
Fórmula
con la que se logró sacar la ponderación total.
Fórmula
con la que se sacó el porcentaje de la comisión que recibirá cada vencedor,
pero si este cumple con los objetivos ya propuestos.
Fórmula
con la que se sacó la comisión de cada uno de los vendedores pero representada
en dólares.
Vídeo Tutorial
Clase Veintiuno
Validación de Datos y Funciones de Conteo
VALIDACIÓN DE DATOS
La validación de datos se usa para controlar el tipo de datos o los
valores que los usuarios pueden escribir en una celda. Por ejemplo, es posible
que desee restringir la entrada de datos a un intervalo determinado de fechas,
limitar las opciones con una lista o asegurarse de que sólo se escriben números
enteros positivos.
El funcionamiento de la validación de datos en Excel y las diferentes
técnicas de validación de datos existentes. No analiza la protección de celdas
que es una característica que permite "bloquear" u ocultar ciertas
celdas de una hoja de cálculo para que no se puedan editar ni sobrescribir.
FUNCIONES DE CONTEO
La función CONTAR.SI.CONJUNTO permite contar la cantidad de registros
que cumplen con un criterio definido por el usuario. Esta función permite
definir mas de una condición a diferencia de la función CONTAR.SI.
Se deben proporcionar parámetros de a pares y los primeros dos se
utilizan de la siguiente forma. El primero es el rango donde se desea aplicar
el criterio y el segundo es la condición que se desea cumplir. Esta función
acepta un máximo 127 criterios diferentes con lo cual se pueden definir
criterios muy específicos.
Si bien se pueden definir varios parámetros de criterio estos deben
tener la misma cantidad de celdas para poder ser utilizados en una única
función.
Cuadro
de diálogo de la validación de datos.
Cuadro
de diálogo en donde se pudo programar acerca de las edades que se pueden
ingresar en la columna D.
Cuadro
de diálogo en donde se inserta el tipo de advertencia que se desee para la
columna D.
|
La
función aplicada anteriormente acerca delas edades de los pacientes si
funciona.
|
En
este cuadro de diálogo se puede hacer aplicaciones en la columna E para
identificar a los pacientes fememinos o masculinos, se usa el mismo cuadro de
diálogo para registrar las enfermedades de la columna K, pero para identificar
las enfermedades primero debemos tener una lista de las enfermedades más
frecuentes.
En este
cuadro de diálogo se le puede poner una función con las celdas C13, C14 para
los datos refrentes a las edad de los pacientes que se registrará en la
columna D.
|
Ejemplo:
En este ejemplo se aplica las función CONTAR y sus otras fuciones como
el CONTAR SI, CONTARA, CONTAR.BLANCO, CONTAR.SI.CONJUNTO, entre otras.
CONTAR SI
La función CONTAR.SI en Excel nos da la oportunidad de contar el número de celdas de un rango que cumplan con un criterio establecido. Solamente cuando el valor de dichas celdas cumple la condición es considerada dentro de la cuenta.
CONTAR SI
La función CONTAR.SI en Excel nos da la oportunidad de contar el número de celdas de un rango que cumplan con un criterio establecido. Solamente cuando el valor de dichas celdas cumple la condición es considerada dentro de la cuenta.
Sintaxis de la
función CONTAR.SI
Solamente existen dos
argumentos para la función CONTAR.SI que son los siguientes:
·
Rango (obligatorio): El rango que
contiene los valores que se desean contar.
·
Criterio (obligatorio): La condición que
determinará las celdas que serán contadas.
El criterio puede ser un número o una expresión que indicará la
condición a cumplir, por ejemplo: 55, “>26”, “VERDE”. El criterio no hará
diferencia entre mayúsculas y minúsculas. La función CONTAR.SI solamente
permite especificar una sola condición, en caso de necesitar aplicar varias
condiciones se puede utilizar la función
CONTAR.SI.CONJUNTO.
Ejemplo
en el que se usó la función CONTAR.SI, para verificar el total de pacientes
hombres y mujeres.
CONTAR BLANCO
Cuenta el número de celdas en blanco dentro de un rango.
Sintaxis: CONTAR.BLANCO(rango)
La sintaxis de la función CONTAR.BLANCO tiene los siguientes
argumentos:
·
Rango Obligatorio. El
rango dentro del cual desea contar el número de celdas en blanco.
También se cuentan las celdas que incluyen fórmulas que devuelven
"" (texto vacío). Las celdas que contienen valores cero no se
cuentan.
Ejemplo, en que se aplicó la
función CONTAR.BLANCO, para resgitrar los cuartos libres.
|
CONTAR
La función CONTAR cuenta
la cantidad de celdas que contienen números y cuenta los números dentro de la
lista de argumentos. Use la función CONTAR para obtener la cantidad
de entradas en un campo de número de un rango o matriz de números.
Sintaxis: CONTAR(valor;[valor2],,,,,)
La sintaxis de la
función CONTAR tiene los siguientes argumentos:
valor1 Obligatorio.
Primer elemento, referencia de celda o rango en el que desea contar números.
valor2,
... Opcional. Hasta 255 elementos, celdas de
referencia o rangos adicionales en los que desea contar números.
Nota:Los argumentos pueden
contener o hacer referencia a una variedad de diferentes tipos de datos, pero
sólo se cuentan los números.
Observaciones
Se cuentan argumentos
que son números, fechas o una representación de texto de los números (por
ejemplo, un número entre comillas, como "1").
Se tienen en cuenta
los valores lógicos y las representaciones textuales de números escritos
directamente en la lista de argumentos.
No se cuentan los
argumentos que sean valores de error o texto que no se puedan traducir a
números.
Si un argumento es
una matriz o una referencia, sólo se considerarán los números de esa matriz o
referencia. No se cuentan celdas vacías, valores lógicos, texto o valores de
error de la matriz o de la referencia.
Si desea contar
valores lógicos, texto o valores de error, use la función CONTARA.
Si desea contar sólo
números que cumplan con determinados criterios, use la función CONTAR.SI o
la función
CONTAR.SI.CONJUNTO
CONTARA
Cuenta el
número de celdas que no están vacías y los valores que hay en la lista de
argumentos. Use CONTARA para contar el número de celdas que contienen datos en
un rango o matriz.
Sintaxis
CONTARA(valor1;valor2;...)
Valor1, valor2, ... son de 1 a 30 argumentos
que representan los valores que desea contar. En este caso, un valor es
cualquier tipo de información, incluyendo texto vacío ("") pero
excluyendo celdas vacías. Si un argumento es una matriz o una referencia, se
pasan por alto las celdas vacías que se encuentran en la matriz o en la
referencia. Si no necesita contar valores lógicos, texto, o valores de error,
use la función CONTAR.
Ejemplo en el cuál se aplicó la
función CONTARA para poder obtener el total de pacientes registrados en las
celdas B2 hasta B11.
CONTAR SI
CONJUNTO
La función
CONTAR.SI.CONJUNTO en Excel nos permite contar los elementos de un
rango que cumplen con los criterios definidos. Esta función nos
permite especificar hasta 127 criterios.
Sintaxis
de la función CONTAR.SI.CONJUNTO
La función
CONTAR.SI.CONJUNTO tiene dos argumentos obligatorios y los demás
opcionales.
· Rango_criterios1
(obligatorio): El rango de celdas que será evaluado con el Criterio1.
· Criterio1
(obligatorio): El criterio con que se evaluará el Rango_criterios1.
· Rango_criterios2
(opcional): El rango de celdas que será evaluado con el Criterio2.
· Criterio2
(opcional): El criterio que evaluará el Rango_criterios2.
A partir
del Rango_criterios2, todos los argumentos son opcionales y se permiten hasta
127 rangos y criterios a evaluar por la función
CONTAR.SI.CONJUNTO. Cada rango especificado debe tener la misma cantidad de
filas (o columnas) que los anteriores.
Ejemplar
en el que se usó la función CONTAR.SI.CONJUNTO, para sacar el total de
hombres con gastritis y a las mujeres de 25 años con diabetes.
|
Ejercicio Completo
Video Tutorial.
