En este video miraremos como utilizar este par de funciones de búsquedas que solucionan algunos casos en los que no se puede utilizar la función Buscarv. Espero les guste.
Clase1. Formatos Personalizados, Listas Personalizadas y Validación de Datos. Función SI
· CREAR FORMATOS PERSONALIZADOS
Los Formatos Personalizados como su nombre lo indica, permite establecer formatos de acuerdo a nuestras necesidades.
Por ejemplo si trabajamos la siguiente tabla:
Si deseamos que al digitar la cantidad, siempre veamos la unidad que en este caso podría ser kilogramos Kg. Podriamos darle un formato personalizado así:
1. Seleccionar el rango de celdas que contiene las cantidades
2. Clic derecho sobre el rango y escoger la opcion formato de Celdas (ctrl. + F1)
3. Seleccionar la pestaña número
4. Escoger categoría personalizada y formato #.##0,00
5. Ubicarse en Tipo y digitar después de #.##0,00 “ Kg”
6. Aceptar
La tabla quedaría así:
· CREAR LISTAS PERSONALIZADAS
Excel Permite crear listas personalizadas. Dentro de las listas personalizadas que Excel trae incluidas están las de meses y dias de la semana. Esto lo ve si digita Lunes o Enero en alguna celda y luego arrastra el contenido hacia abajo. La lista se completará automáticamente con los demás meses o dias según el caso. Si usted trabaja frecuentemente con un mismo listado puede crearlo como lista personalizada, asi:
Ejm: Siempre trabajamos con la siguiente lista de elementos.
La idea es crear la lista personalizada con estos elementos para no tener que digitarla siempre.
1.clic sobre el boton de office en la parte superior izquierda de la pantalla
2.Seleccione Opciones de Excel
3.Estando en mas frecuentes, seleccionar el botón Modificar Listas Personalizadas
4.Seleccione Nueva Lista y Entrada de Lista digite los elementos de la lista
presionando enter después de cada elemento.
5.Haga clic en agregar y cierre la ventana
6.Clic en aceptar
Para comprobar que las lista funciona correctamente, ubiquese en cualquier celda, digite el primer elemento de la lista y luego arrastre hacia abajo y notará que la lista se completa automáticamente.
· VALIDACION DE DATOS
La validación de Datos nos permite restringir los valores que se digitan en alguna celda en Excel. Por ejemplo si tenemos
Si deseamos podemos restringir las celdas que tienen las notas, con el fin de que no se digiten valores superiores a 5.0.
Pasos:
1.Seleccionar todas las celdas que tienen las notas.
2.Ir al menú datos y seleccionar la opción validación de datos.
3.Ubicarse en la pestaña configurar y en permitir seleccionar el tipo de dato. En
este caso debemos seleccionar un tipo de numero decimal. Ecoger valor mínimo de 1 y máximo de 5.
4.Aceptar
Si trata de digitar un 7 en las celdas validadas se desplegará un mensaje de error diciéndole que el valor no es permitido.
Ese mensaje de error se puede modificar de la siguiente manera.
1.Seleccionar todas las celdas que tienen las notas.
2.Ir al menú datos y seleccionar la opcion validación de datos.
3.Ubicarse en la pestaña mensaje de error y escoger el estilo de mensaje (limite).
4.Colocar un titulo para el mensaje
5.En entrada de mensaje colo que el texto que desea que se despliegue.
6.Aceptar.
Tambien podemos programar un mensaje de entrada para que cuando nos ubiquemos en las celdas en donde vamos a digitar lsa notas nos salga un mensaje informativo.
Cuando digite el valor errado (7 en este caso) se desplegara el mensaje de error que usted programó.
· VALIDANDO DE UNA LISTA EXISTENTE
En ocasiones queremos restringir los datos que se introducen en una celda de Excel, de manera que la información que se colocará en la celda, sea seleccionada de una lista existente.
Ejemplo:
Supongamos que una compañía x, tiene sede en las siguientes ciudades de la costa: Barranquilla, Santa Marta, Cartagena, Riohacha y Valledupar.
Si tenemos el siguiente cuadro a partir de la celda A1 de un archivo de Excel:
La idea es que en las celdas de la columna A (A2 a A5), solo se puedan colocar los nombre de las ciudades en las que la compañía tiene sucursales. Para tal fin nos ubicamos en celdas por fuera de las que conformaran el cuadro, puede ser en H1 y desde esta celda hacia abajo, escribimos los nombres de las ciudades, es decir: Barranquilla, Santa Marta, Cartagena, Riohacha y Valledupar.
Para lograr la validación, debemos realizar los siguientes pasos:
1.Seleccionar las celdas en las que vamos a colocar la restricción o validación.
(para este caso de la celda A2 hasta la celda A5)
2.Ir al menú datos y escoger la opción validación.
3.En la primera pestaña (configuración) dar clic en la parte donde dice permitir y
escoger la opción lista.
4.Posteriormente hay que seleccionar el origen de la lista, es decir cual es la
lista o donde esta la lista que contiene los valores que se podrán colocar en esta
celda. Para el ejemplo habíamos escrito nuestra lista desde H1 hasta H5. Para
seleccionarlas simplemente damos clic sostenido sobre H1 y arrastramos hasta H5.
5.Finalmente damos clic en aceptar
Cuando nos ubiquemos en cualquiera de las celdas en las que colocamos la restricción, notaremos que nos aparece en la parte derecha de la celda una pequeña flecha, en la cual debemos dar para que nos permita seleccionar cualquiera de las ciudades de la lista.
FUNCION SI
La Función SI nos permite ejecutar una de dos posibles acciones, dependiendo del resultado de evaluar una prueba lógica
Sintaxis General: =SI(PL;Rverdadero;Rfalso)
Donde PL es la prueba lógica que debemos evaluar, Rverdadero es la opción que se ejecutaría cuando el resultado de evaluar la prueba lógica sea verdadero y Rfalso es la opcion que se ejecutaría cuando el resultado de evaluar la prueba lógica sea falso.
Nota: Cuando en las evaluaciones o en las acciones a realizar se deba incluir texto, este debe ir siempre entre comillas dobles. " "
Ejemplo:
Se desea que Excel envíe un mensaje que nos diga si un estudiante será becado o no. La institución decide que solo se becará a los estudiantes con notas superiores a 4,5
Teniendo el siguiente cuadro haremos la solución al problema
Ejemplo 2
Se desea realizar un descuento del 40% sobre el valor de la matrícula a los estudiantes cuya nota fue al menos de 4.8, los estudiantes que no cumplan con esta condición no tendrán descuento sobre la matrícula.
Trabajaremos con la siguiente tabla:
.VALIDANDO CON LISTAS (RANGO DE DATOS) Y SI ANIDADOS
En el siguiente ejemplo haremos validación de datos utilizando validación de datos con lista proveniente de un rango de datos y con la utilización de la función SI anidados.
Realizaremos un cuadro donde se deben digitar en una columna los departamentos y en la columna siguiente la ciudad.
Para la columna de departamentos solo debe aceptar las opciones Atlántico y Bolívar, las cuales provienen del rango de datos que se le dio el nombre de departamentos.
Para la columna de ciudad, esta variará de la siguiente manera: Si en departamento se seleccionó la opción Atlántico, solo aceptará las opciones Barranquilla o Soledad las cuales se desplegaran de una lista. Si en departamento se seleccionó la opción Bolívar, solo aceptará las opciones Cartagena o Turbaco las cuales se desplegaran de una lista.
Debe tenerse en cuenta que previamente se debieron asignar los nombres de los rangos como se hizo en el ejercicio anterior.
Los Formatos Personalizados como su nombre lo indica, permite establecer formatos de acuerdo a nuestras necesidades.
Por ejemplo si trabajamos la siguiente tabla:
Si deseamos que al digitar la cantidad, siempre veamos la unidad que en este caso podría ser kilogramos Kg. Podriamos darle un formato personalizado así:
1. Seleccionar el rango de celdas que contiene las cantidades
2. Clic derecho sobre el rango y escoger la opcion formato de Celdas (ctrl. + F1)
3. Seleccionar la pestaña número
4. Escoger categoría personalizada y formato #.##0,00
5. Ubicarse en Tipo y digitar después de #.##0,00 “ Kg”
6. Aceptar
La tabla quedaría así:
· CREAR LISTAS PERSONALIZADAS
Excel Permite crear listas personalizadas. Dentro de las listas personalizadas que Excel trae incluidas están las de meses y dias de la semana. Esto lo ve si digita Lunes o Enero en alguna celda y luego arrastra el contenido hacia abajo. La lista se completará automáticamente con los demás meses o dias según el caso. Si usted trabaja frecuentemente con un mismo listado puede crearlo como lista personalizada, asi:
Ejm: Siempre trabajamos con la siguiente lista de elementos.
La idea es crear la lista personalizada con estos elementos para no tener que digitarla siempre.
1.clic sobre el boton de office en la parte superior izquierda de la pantalla
2.Seleccione Opciones de Excel
3.Estando en mas frecuentes, seleccionar el botón Modificar Listas Personalizadas
4.Seleccione Nueva Lista y Entrada de Lista digite los elementos de la lista
presionando enter después de cada elemento.
5.Haga clic en agregar y cierre la ventana
6.Clic en aceptar
Para comprobar que las lista funciona correctamente, ubiquese en cualquier celda, digite el primer elemento de la lista y luego arrastre hacia abajo y notará que la lista se completa automáticamente.
· VALIDACION DE DATOS
La validación de Datos nos permite restringir los valores que se digitan en alguna celda en Excel. Por ejemplo si tenemos
Si deseamos podemos restringir las celdas que tienen las notas, con el fin de que no se digiten valores superiores a 5.0.
Pasos:
1.Seleccionar todas las celdas que tienen las notas.
2.Ir al menú datos y seleccionar la opción validación de datos.
3.Ubicarse en la pestaña configurar y en permitir seleccionar el tipo de dato. En
este caso debemos seleccionar un tipo de numero decimal. Ecoger valor mínimo de 1 y máximo de 5.
4.Aceptar
Si trata de digitar un 7 en las celdas validadas se desplegará un mensaje de error diciéndole que el valor no es permitido.
Ese mensaje de error se puede modificar de la siguiente manera.
1.Seleccionar todas las celdas que tienen las notas.
2.Ir al menú datos y seleccionar la opcion validación de datos.
3.Ubicarse en la pestaña mensaje de error y escoger el estilo de mensaje (limite).
4.Colocar un titulo para el mensaje
5.En entrada de mensaje colo que el texto que desea que se despliegue.
6.Aceptar.
Tambien podemos programar un mensaje de entrada para que cuando nos ubiquemos en las celdas en donde vamos a digitar lsa notas nos salga un mensaje informativo.
Cuando digite el valor errado (7 en este caso) se desplegara el mensaje de error que usted programó.
· VALIDANDO DE UNA LISTA EXISTENTE
En ocasiones queremos restringir los datos que se introducen en una celda de Excel, de manera que la información que se colocará en la celda, sea seleccionada de una lista existente.
Ejemplo:
Supongamos que una compañía x, tiene sede en las siguientes ciudades de la costa: Barranquilla, Santa Marta, Cartagena, Riohacha y Valledupar.
Si tenemos el siguiente cuadro a partir de la celda A1 de un archivo de Excel:
La idea es que en las celdas de la columna A (A2 a A5), solo se puedan colocar los nombre de las ciudades en las que la compañía tiene sucursales. Para tal fin nos ubicamos en celdas por fuera de las que conformaran el cuadro, puede ser en H1 y desde esta celda hacia abajo, escribimos los nombres de las ciudades, es decir: Barranquilla, Santa Marta, Cartagena, Riohacha y Valledupar.
Para lograr la validación, debemos realizar los siguientes pasos:
1.Seleccionar las celdas en las que vamos a colocar la restricción o validación.
(para este caso de la celda A2 hasta la celda A5)
2.Ir al menú datos y escoger la opción validación.
3.En la primera pestaña (configuración) dar clic en la parte donde dice permitir y
escoger la opción lista.
4.Posteriormente hay que seleccionar el origen de la lista, es decir cual es la
lista o donde esta la lista que contiene los valores que se podrán colocar en esta
celda. Para el ejemplo habíamos escrito nuestra lista desde H1 hasta H5. Para
seleccionarlas simplemente damos clic sostenido sobre H1 y arrastramos hasta H5.
5.Finalmente damos clic en aceptar
Cuando nos ubiquemos en cualquiera de las celdas en las que colocamos la restricción, notaremos que nos aparece en la parte derecha de la celda una pequeña flecha, en la cual debemos dar para que nos permita seleccionar cualquiera de las ciudades de la lista.
FUNCION SI
La Función SI nos permite ejecutar una de dos posibles acciones, dependiendo del resultado de evaluar una prueba lógica
Sintaxis General: =SI(PL;Rverdadero;Rfalso)
Donde PL es la prueba lógica que debemos evaluar, Rverdadero es la opción que se ejecutaría cuando el resultado de evaluar la prueba lógica sea verdadero y Rfalso es la opcion que se ejecutaría cuando el resultado de evaluar la prueba lógica sea falso.
Nota: Cuando en las evaluaciones o en las acciones a realizar se deba incluir texto, este debe ir siempre entre comillas dobles. " "
Ejemplo:
Se desea que Excel envíe un mensaje que nos diga si un estudiante será becado o no. La institución decide que solo se becará a los estudiantes con notas superiores a 4,5
Teniendo el siguiente cuadro haremos la solución al problema
Ejemplo 2
Se desea realizar un descuento del 40% sobre el valor de la matrícula a los estudiantes cuya nota fue al menos de 4.8, los estudiantes que no cumplan con esta condición no tendrán descuento sobre la matrícula.
Trabajaremos con la siguiente tabla:
.VALIDANDO CON LISTAS (RANGO DE DATOS) Y SI ANIDADOS
En el siguiente ejemplo haremos validación de datos utilizando validación de datos con lista proveniente de un rango de datos y con la utilización de la función SI anidados.
Realizaremos un cuadro donde se deben digitar en una columna los departamentos y en la columna siguiente la ciudad.
Para la columna de departamentos solo debe aceptar las opciones Atlántico y Bolívar, las cuales provienen del rango de datos que se le dio el nombre de departamentos.
Para la columna de ciudad, esta variará de la siguiente manera: Si en departamento se seleccionó la opción Atlántico, solo aceptará las opciones Barranquilla o Soledad las cuales se desplegaran de una lista. Si en departamento se seleccionó la opción Bolívar, solo aceptará las opciones Cartagena o Turbaco las cuales se desplegaran de una lista.
Debe tenerse en cuenta que previamente se debieron asignar los nombres de los rangos como se hizo en el ejercicio anterior.
Clase 2. Funcion Contar.si, otras funciones. Validación de datos personalizada
· FUNCION CONTAR.SI
Recorre un rango de Excel y cuenta las veces que aparece un criterio dado.
La función CONTAR.SI tiene la siguiente sintaxis:
=CONTAR:SI(RANGO;CRITERIO)
Donde rango son las celdas que contienen los valores que deseamos contar.
Ejemplo
Tenemos la siguiente información:
Ante la pregunta, Si las elecciones para presidente fueran hoy por cual de los siguientes candidatos votaría? . (Santos, Mockus, Noemí.). Se tuvieron las siguientes respuestas para 20 personas.
Se desea conocer cuantas personas votarían por Santos, cuantas por Mockus y Cuantas por Noemí.
Para lograr el resultado usaremos la función CONTAR.SI de la siguiente manera.
En las filas 23,24 y 25 colocamos las palabras Santos Mockus y Noemí respectivamente. Estas son las celdas que usaremos como criterio. La idea es que Excel recorra de las celdas B2 hasta B21 y cuente las veces que aparece cada criterio, es decir cuantas veces en ese rango aparece la palabra Santos(A23), cuantas veces aparece la palabra Mockus(A24) y cuantas veces en el mismo rango aparece la palabra Noemí (A25).
No tendríamos que ubicar en la celda B23 y escribir la siguiente fórmula.
=contar.si(B2:B21;A23) Recorra desde B2 hasta B21 y cuente las veces que aparece la palabra que esté en A23.
Esa fórmula la copiamos a las celdas de abajo y quedaría.
El resultado seria:
· VALOR MAXIMO (FUNCION MAX)
Nos permite conocer cual el valor máximo de un rango de celdas:
La función MAX tiene la siguiente sintaxis:
=MAX(RANGO DE CELDAS)
Donde rango de celdas son los valores de los cuales queremos conocer cual es el máximo valor.
Para el ejemplo podríamos hacer:
· Ubicarnos en cualquier celda donde deseamos que aparezca el valor maximo (B6, según ejemplo) y digitar lo siguiente.
· = MAX(B2:B4)
· Nota: El rango B2:B4 son las celdas que contienen los valores de los cuales queremos sacar el valor máximo.
El resultado sería:
· VALOR MINIMO (FUNCION MIN)
Nos permite conocer cual el valor mínimo de un rango de celdas:
La función MIN tiene la siguiente sintaxis:
=MIN(RANGO DE CELDAS)
Donde rango de celdas son los valores de los cuales queremos conocer cual es el Mínimo valor.
Para el ejemplo podríamos hacer:
· Ubicarnos en cualquier celda donde deseamos que aparezca el valor mínimo (B6, según ejemplo) y digitar lo siguiente.
· =MIN(B2:B4)
El resultado sería:
. VALIDANDO DATOS CON LA FUNCION CONTAR.SI
Podemos utilizar la funcion CONTAR.SI para validar datos en Excel. Si deseamos que un rango de celdas no acepte datos repetidos, podriamos hacerlo validando con una forma personalizada en la que utilizemos la funcion CONTAR.SI y solo acepte los datos cuando el conteo sea igual a 1.
En el siguiente ejemplo queremos digitar los números de cédula de unos trabajadores pero no deseamos que este número se repita en las celdas, por lo que queremos que en caso que digitemos una cédula existente, Excel nos envie el mensaje de error.
Los datos se digitaran de la celda A2 a la celda A10 y estas son las celdas a validar
Inicialmente seleccionamos estas celdas, luego vamos al menú datos y seleccionamos la opción Validación de datos.
Ubicados en la pestaña Configuración, en la lista de permitir escogemos la opción Personalizada y en la parte de Fórmula escribimos lo siguiente:
=CONTAR.SI($A$2:$A$10;A2)=1
Con esta fórmula estamos estableciendo que cada vez que se digite un número en las celdas de A2 a A10, recorra ese mismo rango y cuenta las veces que aparece el número en ese rango y que el número solo será aceptado cuando el resultado del conteo sea igual a 1.
Luego nos ubicamos en la pestaña Mensaje de error para programar el mensaje.
En estilo de mensaje seleccionamos la opción Grave, en título escribimos Error y en mensaje de error escribimos. Número de cédula ya existe, verifique el valor a digitar.
Ahora probemos digitando las siguientes cédulas a partir de A2:
74111555
73777888
Ahora en A4 tratemos de digitar nuevamente el número digitado en A2, Excel nos desplegará el siguiente mensaje:
Recorre un rango de Excel y cuenta las veces que aparece un criterio dado.
La función CONTAR.SI tiene la siguiente sintaxis:
=CONTAR:SI(RANGO;CRITERIO)
Donde rango son las celdas que contienen los valores que deseamos contar.
Ejemplo
Tenemos la siguiente información:
Ante la pregunta, Si las elecciones para presidente fueran hoy por cual de los siguientes candidatos votaría? . (Santos, Mockus, Noemí.). Se tuvieron las siguientes respuestas para 20 personas.
Se desea conocer cuantas personas votarían por Santos, cuantas por Mockus y Cuantas por Noemí.
Para lograr el resultado usaremos la función CONTAR.SI de la siguiente manera.
En las filas 23,24 y 25 colocamos las palabras Santos Mockus y Noemí respectivamente. Estas son las celdas que usaremos como criterio. La idea es que Excel recorra de las celdas B2 hasta B21 y cuente las veces que aparece cada criterio, es decir cuantas veces en ese rango aparece la palabra Santos(A23), cuantas veces aparece la palabra Mockus(A24) y cuantas veces en el mismo rango aparece la palabra Noemí (A25).
No tendríamos que ubicar en la celda B23 y escribir la siguiente fórmula.
=contar.si(B2:B21;A23) Recorra desde B2 hasta B21 y cuente las veces que aparece la palabra que esté en A23.
Esa fórmula la copiamos a las celdas de abajo y quedaría.
El resultado seria:
· VALOR MAXIMO (FUNCION MAX)
Nos permite conocer cual el valor máximo de un rango de celdas:
La función MAX tiene la siguiente sintaxis:
=MAX(RANGO DE CELDAS)
Donde rango de celdas son los valores de los cuales queremos conocer cual es el máximo valor.
Para el ejemplo podríamos hacer:
· Ubicarnos en cualquier celda donde deseamos que aparezca el valor maximo (B6, según ejemplo) y digitar lo siguiente.
· = MAX(B2:B4)
· Nota: El rango B2:B4 son las celdas que contienen los valores de los cuales queremos sacar el valor máximo.
El resultado sería:
· VALOR MINIMO (FUNCION MIN)
Nos permite conocer cual el valor mínimo de un rango de celdas:
La función MIN tiene la siguiente sintaxis:
=MIN(RANGO DE CELDAS)
Donde rango de celdas son los valores de los cuales queremos conocer cual es el Mínimo valor.
Para el ejemplo podríamos hacer:
· Ubicarnos en cualquier celda donde deseamos que aparezca el valor mínimo (B6, según ejemplo) y digitar lo siguiente.
· =MIN(B2:B4)
El resultado sería:
. VALIDANDO DATOS CON LA FUNCION CONTAR.SI
Podemos utilizar la funcion CONTAR.SI para validar datos en Excel. Si deseamos que un rango de celdas no acepte datos repetidos, podriamos hacerlo validando con una forma personalizada en la que utilizemos la funcion CONTAR.SI y solo acepte los datos cuando el conteo sea igual a 1.
En el siguiente ejemplo queremos digitar los números de cédula de unos trabajadores pero no deseamos que este número se repita en las celdas, por lo que queremos que en caso que digitemos una cédula existente, Excel nos envie el mensaje de error.
Los datos se digitaran de la celda A2 a la celda A10 y estas son las celdas a validar
Inicialmente seleccionamos estas celdas, luego vamos al menú datos y seleccionamos la opción Validación de datos.
Ubicados en la pestaña Configuración, en la lista de permitir escogemos la opción Personalizada y en la parte de Fórmula escribimos lo siguiente:
=CONTAR.SI($A$2:$A$10;A2)=1
Con esta fórmula estamos estableciendo que cada vez que se digite un número en las celdas de A2 a A10, recorra ese mismo rango y cuenta las veces que aparece el número en ese rango y que el número solo será aceptado cuando el resultado del conteo sea igual a 1.
Luego nos ubicamos en la pestaña Mensaje de error para programar el mensaje.
En estilo de mensaje seleccionamos la opción Grave, en título escribimos Error y en mensaje de error escribimos. Número de cédula ya existe, verifique el valor a digitar.
Ahora probemos digitando las siguientes cédulas a partir de A2:
74111555
73777888
Ahora en A4 tratemos de digitar nuevamente el número digitado en A2, Excel nos desplegará el siguiente mensaje:
Clase 3. Funsion SI anidados y validación de datos con funcion SI
• FUNCION SI ANIDADOS
La Función SI anidada se utiliza en casos en los que necesitamos obtener una de tres o mas respuestas posibles.
SINTAXIS:
Si usariamos 2 funciones SI
=SI(Prueba Logica;Verdadero;SI(Prueba Lógica;Verdadero;Falso))
Nota1: El número de paréntesis que cierran la función al final, será igual al número de funciones SI que se creen.
Nota2: El número de funciones SI que se deben crear siempre será menor en uno al número de posibilidades, ya que la última posibilidad quedará incluida en la parte falsa del anterior SI.
Ejemplo:
La empresa xyz decide pagar las comisiones de sus vendedores de la siguiente manera:
1. Para Ventas hasta $5.000.000 se pagará el 4% sobre la venta
2. Para Ventas superiores de $5.000.000 y hasta 10.000.000 se pagará el 6% sobre la
venta.
3. Para Ventas superiores a $10.000.000 se pagará el 10% sobre la venta.
Como lo habíamos dicho anteriormente si se tienen 3 posibilidades, se utilizarán entonces 2 funciones SI. Una para el primer rango de ventas menores de $5.000.000 otra para el segundo rango de ventas superiores a $5.000.000 y hasta $10.000.000, para el último rango de ventas superiores a $10.000.000, no es necesario crear otro SI pues este rango quedará incluido en la parte falsa del segundo rango.
Ubicados en la celda C2 se debe digitar lo siguiente:
=SI(B2<=5000000;B2*4%;SI(B2<=10000000;B2*6%;B2*10%)))
Arrastre la formula digitada en las siguientes celdas.
El resultado debe ser el siguiente:
. ASIGNAR UN NOMBRE A UN RANGO DE CELDAS
En Excel es posible asignar a un rango de datos un nombre con el fin de que este rango de celdas funcione como una lista.
Una vez que hemos seleccionado las celdas a las que queremos darle el nombre, vamos al menú fórmulas y seleccionamos la opción Administrador de nombres. Clic en la opción nuevo y le asignamos el nombre. Una manera mas fácil es habiendo seleccionado las celdas ir en la barra de Fórmulas al nombre de la celda, borrar el nombre de la celda y colocar el nombre al rango.
En el ejemplo queremos asignar nombres a tres rangos de celdas:
A la información ubicada en las celdas A1 a la celda A2 la llamaremos departamentos, para lo cual las seleccionamos vamos a la barra de fórmula y en donde indicaba que la selección iniciaba en la celda A1 damos clic y escribimos departamento.
Nótese que estan seleccionadas las celdas a1 y a2 y en la barra de fórmulas donde debería decir a1 ahora dice departamentos
A la información ubicada en las celdas B1 a la celda B2 la llamaremos bquilla, para lo cual las seleccionamos vamos a la barra de fórmula y en donde indicaba que la selección iniciaba en la celda B1 damos clic y escribimos bquilla.
Nótese que estan seleccionadas las celdas b1 y b2 y en la barra de fórmulas donde debería decir b1 ahora dice bquilla
A la información ubicada en las celdas C1 a la celda C2 la llamaremos cgena, para lo cual las seleccionamos vamos a la barra de fórmula y en donde indicaba que la selección iniciaba en la celda C1 damos clic y escribimos cgena.
Nótese que estan seleccionadas las celdas c1 y c2 y en la barra de fórmulas donde debería decir c1 ahora dice cgena
.VALIDANDO CON LISTAS (RANGO DE DATOS) Y FUNCION SI
En el siguiente ejemplo haremos validación de datos utilizando validación de datos con lista proveniente de un rango de datos y con la utilización de la función SI.
Realizaremos un cuadro donde se deben digitar en una columna los departamentos y en la columna siguiente la ciudad.
Para la columna de departamentos solo debe aceptar las opciones Atlántico y Bolívar, las cuales provienen del rango de datos que se le dio el nombre de departamentos.
Para la columna de ciudad, esta variará de la siguiente manera: Si en departamento se seleccionó la opción Atlántico, solo aceptará las opciones Barranquilla o Soledad las cuales se desplegaran de una lista. Si en departamento se seleccionó la opción Bolívar, solo aceptará las opciones Cartagena o Turbaco las cuales se desplegaran de una lista.
Debe tenerse en cuenta que previamente se debieron asignar los nombres de los rangos como se hizo en el ejercicio anterior.
La información se digitará de las filas 7 a la 15, los datos que aparecen en las celdas superiores son solo las referencias o rangos a los que se les asignó un nombre.
Son dos validaciones diferentes, la primera para las celdas donde se digitaran los departamentos y otra validación para las celdas donde se digitaran las ciudades.
Validando para departamentos:
Se seleccionan las celdas A7 a A15, vamos al menú datos y en la pestaña configuración de la lista permitir seleccionamos la opcion lista y en donde dice origen escribimos =departamentos. (Quiere decir que solo acepte los valores incluidos en la lista departamentos que correspondia a un rango de celdas)
Ahora nos ubicamos en la celda A7 y se observa la flecha de la lista desplegable y al dar clic en esta se aprecian las dos opciones: atlantico y bolivar.
Ahora validaremos para la columna de ciudad.
Se seleccionan las celdas B7 a B15, vamos al menú datos y en la pestaña configuración de la lista permitir seleccionamos la opción lista y en donde dice origen escribimos:
=SI(A7="ATLANTICO";bquilla;cgena)
Con esta fórmula estamos diciéndole a Excel que si la palabra que aparece en la celda A7 es atlantico entonces aca llame a la lista bquilla de lo contrario llame a la lista cgena.
Hagamos las pruebas del caso.
Primero nos ubicamos en la celda A7 y seleccionamos atlantico y nos ubicamos en la celda b7 y veremos que en la lista se despliegan las opciones barranquilla y soledad
Ahora nos ubicamos en la celda A7 y seleccionamos bolivar y nos ubicamos en la celda b7 y veremos que en la lista se despliegan las opciones cartagena y turbaco.
La Función SI anidada se utiliza en casos en los que necesitamos obtener una de tres o mas respuestas posibles.
SINTAXIS:
Si usariamos 2 funciones SI
=SI(Prueba Logica;Verdadero;SI(Prueba Lógica;Verdadero;Falso))
Nota1: El número de paréntesis que cierran la función al final, será igual al número de funciones SI que se creen.
Nota2: El número de funciones SI que se deben crear siempre será menor en uno al número de posibilidades, ya que la última posibilidad quedará incluida en la parte falsa del anterior SI.
Ejemplo:
La empresa xyz decide pagar las comisiones de sus vendedores de la siguiente manera:
1. Para Ventas hasta $5.000.000 se pagará el 4% sobre la venta
2. Para Ventas superiores de $5.000.000 y hasta 10.000.000 se pagará el 6% sobre la
venta.
3. Para Ventas superiores a $10.000.000 se pagará el 10% sobre la venta.
Como lo habíamos dicho anteriormente si se tienen 3 posibilidades, se utilizarán entonces 2 funciones SI. Una para el primer rango de ventas menores de $5.000.000 otra para el segundo rango de ventas superiores a $5.000.000 y hasta $10.000.000, para el último rango de ventas superiores a $10.000.000, no es necesario crear otro SI pues este rango quedará incluido en la parte falsa del segundo rango.
Ubicados en la celda C2 se debe digitar lo siguiente:
=SI(B2<=5000000;B2*4%;SI(B2<=10000000;B2*6%;B2*10%)))
Arrastre la formula digitada en las siguientes celdas.
El resultado debe ser el siguiente:
. ASIGNAR UN NOMBRE A UN RANGO DE CELDAS
En Excel es posible asignar a un rango de datos un nombre con el fin de que este rango de celdas funcione como una lista.
Una vez que hemos seleccionado las celdas a las que queremos darle el nombre, vamos al menú fórmulas y seleccionamos la opción Administrador de nombres. Clic en la opción nuevo y le asignamos el nombre. Una manera mas fácil es habiendo seleccionado las celdas ir en la barra de Fórmulas al nombre de la celda, borrar el nombre de la celda y colocar el nombre al rango.
En el ejemplo queremos asignar nombres a tres rangos de celdas:
A la información ubicada en las celdas A1 a la celda A2 la llamaremos departamentos, para lo cual las seleccionamos vamos a la barra de fórmula y en donde indicaba que la selección iniciaba en la celda A1 damos clic y escribimos departamento.
Nótese que estan seleccionadas las celdas a1 y a2 y en la barra de fórmulas donde debería decir a1 ahora dice departamentos
A la información ubicada en las celdas B1 a la celda B2 la llamaremos bquilla, para lo cual las seleccionamos vamos a la barra de fórmula y en donde indicaba que la selección iniciaba en la celda B1 damos clic y escribimos bquilla.
Nótese que estan seleccionadas las celdas b1 y b2 y en la barra de fórmulas donde debería decir b1 ahora dice bquilla
A la información ubicada en las celdas C1 a la celda C2 la llamaremos cgena, para lo cual las seleccionamos vamos a la barra de fórmula y en donde indicaba que la selección iniciaba en la celda C1 damos clic y escribimos cgena.
Nótese que estan seleccionadas las celdas c1 y c2 y en la barra de fórmulas donde debería decir c1 ahora dice cgena
.VALIDANDO CON LISTAS (RANGO DE DATOS) Y FUNCION SI
En el siguiente ejemplo haremos validación de datos utilizando validación de datos con lista proveniente de un rango de datos y con la utilización de la función SI.
Realizaremos un cuadro donde se deben digitar en una columna los departamentos y en la columna siguiente la ciudad.
Para la columna de departamentos solo debe aceptar las opciones Atlántico y Bolívar, las cuales provienen del rango de datos que se le dio el nombre de departamentos.
Para la columna de ciudad, esta variará de la siguiente manera: Si en departamento se seleccionó la opción Atlántico, solo aceptará las opciones Barranquilla o Soledad las cuales se desplegaran de una lista. Si en departamento se seleccionó la opción Bolívar, solo aceptará las opciones Cartagena o Turbaco las cuales se desplegaran de una lista.
Debe tenerse en cuenta que previamente se debieron asignar los nombres de los rangos como se hizo en el ejercicio anterior.
La información se digitará de las filas 7 a la 15, los datos que aparecen en las celdas superiores son solo las referencias o rangos a los que se les asignó un nombre.
Son dos validaciones diferentes, la primera para las celdas donde se digitaran los departamentos y otra validación para las celdas donde se digitaran las ciudades.
Validando para departamentos:
Se seleccionan las celdas A7 a A15, vamos al menú datos y en la pestaña configuración de la lista permitir seleccionamos la opcion lista y en donde dice origen escribimos =departamentos. (Quiere decir que solo acepte los valores incluidos en la lista departamentos que correspondia a un rango de celdas)
Ahora nos ubicamos en la celda A7 y se observa la flecha de la lista desplegable y al dar clic en esta se aprecian las dos opciones: atlantico y bolivar.
Ahora validaremos para la columna de ciudad.
Se seleccionan las celdas B7 a B15, vamos al menú datos y en la pestaña configuración de la lista permitir seleccionamos la opción lista y en donde dice origen escribimos:
=SI(A7="ATLANTICO";bquilla;cgena)
Con esta fórmula estamos diciéndole a Excel que si la palabra que aparece en la celda A7 es atlantico entonces aca llame a la lista bquilla de lo contrario llame a la lista cgena.
Hagamos las pruebas del caso.
Primero nos ubicamos en la celda A7 y seleccionamos atlantico y nos ubicamos en la celda b7 y veremos que en la lista se despliegan las opciones barranquilla y soledad
Ahora nos ubicamos en la celda A7 y seleccionamos bolivar y nos ubicamos en la celda b7 y veremos que en la lista se despliegan las opciones cartagena y turbaco.
Funciones Logicas Y, O; Combinando SI con Y, SI con O, Seguridad en Excel
· FUNCION Y (lógica)
Se utiliza cuando es necesario evaluar mas de una condición.
SINTAXIS:Y(Valor lógico1; Valor lógico2) (caso con solo dos condiciones)
Si todos los valores lógicos son verdaderos el resultado será VERDADERO
Si alguno de los valores lógicos es falso, el resultado será FALSO
Se desea conocer si el valor de A1 es menor que el de A2 y si el de A2 es menor que el de A3
De acuerdo a la sintaxis ubicados en A4 se digita lo siguiente:
Si analizamos el caso A1 efectivamente es menor que A2; es decir esta primera parte tiene un valor verdadero. Si analizamos igualmente A2 es menor que A3, es decir que la segunda parte también tiene un valor verdadero.
De acuerdo a lo plateado anteriormente en la celda A4 debe aparecer como respuesta Verdadero
Si hubiésemos querido realizar la misma evaluación para los siguientes valores:
El resultado sería FALSO, ya que como se explicó anteriormente para que sea verdadero ambas pruebas lógicas deben ser verdaderas y en este caso la segunda prueba lógica es falsa, pues A2 no es menor que A3.
·COMBINANDO LAS FUNCIONES SI CON Y
Sintaxis: =SI(Y(Condición1;Condición2);RVerdad;RFalsa)
Con el siguiente cuadro de Notas de unos estudiantes se desea que la celda arroje un mensaje que diga BECADO para el caso de estudiantes con notas superiores a 4 y con conducta igual a E (excelente); para el caso en que no se cumplan las dos condiciones, la celda debe arrojar un mensaje que diga NO HAY BECA.
Se tienen las siguientes calificaciones:
Ubicados en la celda D2 se debe digitar lo siguiente:
Arrastre la formula a las siguientes celdas. El resultado será el siguiente.
Para el único caso en que se despliega el mensaje BECADO, es para la celda D3, pues de toda la lista es la única fila en la que ambas condiciones o pruebas lógicas son Verdaderas, ya que la nota es mayor que 4 y la conducta es igual a E. Para los demás casos alguna de las dos condiciones o pruebas es falsa por lo que el mensaje es NO HAY BECA.
·FUNCION O (lógica)
Se utiliza cuando es necesario evaluar mas de una condición.
SINTAXIS:O(Valor lógico1; Valor lógico2)(caso con solo dos condiciones)
Si alguno de los valores lógicos es verdadero el resultado será VERDADERO
El resultado solo será falso cuando todos los valores lógicos son falsos.
Se tiene la siguiente información:
Se desea saber si alguno de los estudiantes tiene la nota mayor a 4 o si su conducta es igual a E.
Ubicados en E2 digitamos lo siguiente:
Arrastramos la fórmula hacía las demas celdas y el resultado sería:
Obsérvese que solo coloca Falso para un estudiante y es porque ambos resultados lógicos son FALSO.
·COMBINANDO LAS FUNCIONES SI CON O
Sintaxis: =SI(O(Condición1;Condición2);RVerdad;RFalsa)
Se tiene la siguiente información:
Las comisiones se pagarán asi: Si el vendedor es de Barranquilla o si la venta es mayor de $5.000.000 la comisión será del 10%, en los demás casos será del 5%.
Ubicados en D2 se debe digitar la siguiente formula:
=SI(O(B2="BARRANQUILLA";C2>5000000);C2*10%;C2*5%)
Arrastre la formula a las siguientes celdas.
El resultado será el siguiente:
Obsérvese que solo a Juan le da el 5%, ya que es el único caso en que ambas pruebas lógicas tienen como resultado FALSO
·NIVELES DE SEGURIDAD EN EXCEL
Excel nos permite tener varios niveles de seguridad.
Para el caso de las validaciones realizadas anteriormente, lo ideal es tener las hojas protegidas de tal forma que cualquier persona no pueda eliminar las restricciones.
EVITAR CAMBIOS EN FORMATOS DE CELDAS, VALIDACIONES ETC.
Pasos:
1. Seleccione toda la hoja de calculo.
2. Clic con el botón derecho del mouse y seleccionar formato de celdas y en la
pestaña proteger desactivar el bloqueo para todas las celdas de la hoja de
calculo. Luego ir al menú Revisar y seleccionar la opción proteger hoja.
3. Verificar que estén seleccionadas las dos primeras opciones que permiten a los
usuarios seleccionar celdas bloqueadas y desbloqueadas
4. Establecer una contraseña de seguridad, confirmarla y aceptar.
Una vez realizados estos pasos, notará que se deshabilitan las opciones para modificar los formatos a las celdas y cualquier persona no podrá quitar las validaciones realizadas. Si alguno desea modificar o eliminar la fórmula de las celdas que están bloqueadas un mensaje
EVITAR CAMBIOS CIERTAS CELDAS DE LA HOJA DE CALCULO
Pasos:
1. Seleccione toda la hoja de calculo.
2. Ir al menú formato celdas y en la pestaña proteger desactivar el bloqueo para
todas las celdas de la hoja de calculo.
3. Seleccionar las celdas que desea bloquear y activar posteriormente el bloqueo de
estas celdas en el menú formato celdas, pestaña proteger.
4. Luego ir al menú Revisar y seleccionar la opción proteger hoja.
5. Verificar que estén seleccionadas las dos primeras opciones que permiten a los
usuarios seleccionar celdas bloqueadas y desbloqueadas
6. Establecer una contraseña de seguridad, confirmarla y aceptar.
Una vez realizados estos pasos, notará que si intenta modificar el contenido de las celdas seleccionadas Excel desplegará un mensaje informando que esas celdas se encuentran bloqueadas.
EVITAR QUE LOS USUARIOS SELECCIONEN UN RANGO DE CELDAS
Pasos:
1. Seleccione toda la hoja de calculo.
2. Ir al menú formato celdas y en la pestaña proteger desactivar el bloqueo para
todas las celdas de la hoja de calculo.
3. Seleccionar las celdas que desea bloquear y activar posteriormente el bloqueo de
estas celdas en el menú formato celdas, pestaña proteger.
4. Luego ir al menú Revisar y seleccionar la opción proteger hoja.
5. Desactivar la opción permitir a los usuarios seleccionar celdas bloqueadas.
6. Establecer una contraseña de seguridad, confirmarla y aceptar.
Una vez realizados estos pasos, notará que si intenta ubicarse en una de las celdas bloqueadas Excel no se lo permitirá.
EVITAR QUE LOS SUARIOS ABRAN UN ARCHIVO
Una vez que haya terminado de crear su archivo debe realizar los siguientes pasos para proteger el archivo contra apertura:
1. Una vez que se ha seleccionado la opción Guardar como Ir a Herramientas y
seleccionar opciones.
2. Seleccione la pestaña Seguridad
3. Digite la contraseña y confírmela
4. Clic en aceptar.
Nota: Se puede establecer contraseña para que el archivo no pueda ser modificado (contraseña de escritura), igualmente se puede seleccionar la opción de solo lectura en la cual podrá modificarse el archivo pero no podrán guardarse los cambios realizados al archivo.
Para comprobar la efectividad cierre el archivo e intente abrirlo, notará que Excel pedirá la contraseña. Digítela para poder abrir el archivo.
Nota: si pierde la contraseña no podrá abrir el archivo.
Se utiliza cuando es necesario evaluar mas de una condición.
SINTAXIS:Y(Valor lógico1; Valor lógico2) (caso con solo dos condiciones)
Si todos los valores lógicos son verdaderos el resultado será VERDADERO
Si alguno de los valores lógicos es falso, el resultado será FALSO
Se desea conocer si el valor de A1 es menor que el de A2 y si el de A2 es menor que el de A3
De acuerdo a la sintaxis ubicados en A4 se digita lo siguiente:
Si analizamos el caso A1 efectivamente es menor que A2; es decir esta primera parte tiene un valor verdadero. Si analizamos igualmente A2 es menor que A3, es decir que la segunda parte también tiene un valor verdadero.
De acuerdo a lo plateado anteriormente en la celda A4 debe aparecer como respuesta Verdadero
Si hubiésemos querido realizar la misma evaluación para los siguientes valores:
El resultado sería FALSO, ya que como se explicó anteriormente para que sea verdadero ambas pruebas lógicas deben ser verdaderas y en este caso la segunda prueba lógica es falsa, pues A2 no es menor que A3.
·COMBINANDO LAS FUNCIONES SI CON Y
Sintaxis: =SI(Y(Condición1;Condición2);RVerdad;RFalsa)
Con el siguiente cuadro de Notas de unos estudiantes se desea que la celda arroje un mensaje que diga BECADO para el caso de estudiantes con notas superiores a 4 y con conducta igual a E (excelente); para el caso en que no se cumplan las dos condiciones, la celda debe arrojar un mensaje que diga NO HAY BECA.
Se tienen las siguientes calificaciones:
Ubicados en la celda D2 se debe digitar lo siguiente:
Arrastre la formula a las siguientes celdas. El resultado será el siguiente.
Para el único caso en que se despliega el mensaje BECADO, es para la celda D3, pues de toda la lista es la única fila en la que ambas condiciones o pruebas lógicas son Verdaderas, ya que la nota es mayor que 4 y la conducta es igual a E. Para los demás casos alguna de las dos condiciones o pruebas es falsa por lo que el mensaje es NO HAY BECA.
·FUNCION O (lógica)
Se utiliza cuando es necesario evaluar mas de una condición.
SINTAXIS:O(Valor lógico1; Valor lógico2)(caso con solo dos condiciones)
Si alguno de los valores lógicos es verdadero el resultado será VERDADERO
El resultado solo será falso cuando todos los valores lógicos son falsos.
Se tiene la siguiente información:
Se desea saber si alguno de los estudiantes tiene la nota mayor a 4 o si su conducta es igual a E.
Ubicados en E2 digitamos lo siguiente:
Arrastramos la fórmula hacía las demas celdas y el resultado sería:
Obsérvese que solo coloca Falso para un estudiante y es porque ambos resultados lógicos son FALSO.
·COMBINANDO LAS FUNCIONES SI CON O
Sintaxis: =SI(O(Condición1;Condición2);RVerdad;RFalsa)
Se tiene la siguiente información:
Las comisiones se pagarán asi: Si el vendedor es de Barranquilla o si la venta es mayor de $5.000.000 la comisión será del 10%, en los demás casos será del 5%.
Ubicados en D2 se debe digitar la siguiente formula:
=SI(O(B2="BARRANQUILLA";C2>5000000);C2*10%;C2*5%)
Arrastre la formula a las siguientes celdas.
El resultado será el siguiente:
Obsérvese que solo a Juan le da el 5%, ya que es el único caso en que ambas pruebas lógicas tienen como resultado FALSO
·NIVELES DE SEGURIDAD EN EXCEL
Excel nos permite tener varios niveles de seguridad.
Para el caso de las validaciones realizadas anteriormente, lo ideal es tener las hojas protegidas de tal forma que cualquier persona no pueda eliminar las restricciones.
EVITAR CAMBIOS EN FORMATOS DE CELDAS, VALIDACIONES ETC.
Pasos:
1. Seleccione toda la hoja de calculo.
2. Clic con el botón derecho del mouse y seleccionar formato de celdas y en la
pestaña proteger desactivar el bloqueo para todas las celdas de la hoja de
calculo. Luego ir al menú Revisar y seleccionar la opción proteger hoja.
3. Verificar que estén seleccionadas las dos primeras opciones que permiten a los
usuarios seleccionar celdas bloqueadas y desbloqueadas
4. Establecer una contraseña de seguridad, confirmarla y aceptar.
Una vez realizados estos pasos, notará que se deshabilitan las opciones para modificar los formatos a las celdas y cualquier persona no podrá quitar las validaciones realizadas. Si alguno desea modificar o eliminar la fórmula de las celdas que están bloqueadas un mensaje
EVITAR CAMBIOS CIERTAS CELDAS DE LA HOJA DE CALCULO
Pasos:
1. Seleccione toda la hoja de calculo.
2. Ir al menú formato celdas y en la pestaña proteger desactivar el bloqueo para
todas las celdas de la hoja de calculo.
3. Seleccionar las celdas que desea bloquear y activar posteriormente el bloqueo de
estas celdas en el menú formato celdas, pestaña proteger.
4. Luego ir al menú Revisar y seleccionar la opción proteger hoja.
5. Verificar que estén seleccionadas las dos primeras opciones que permiten a los
usuarios seleccionar celdas bloqueadas y desbloqueadas
6. Establecer una contraseña de seguridad, confirmarla y aceptar.
Una vez realizados estos pasos, notará que si intenta modificar el contenido de las celdas seleccionadas Excel desplegará un mensaje informando que esas celdas se encuentran bloqueadas.
EVITAR QUE LOS USUARIOS SELECCIONEN UN RANGO DE CELDAS
Pasos:
1. Seleccione toda la hoja de calculo.
2. Ir al menú formato celdas y en la pestaña proteger desactivar el bloqueo para
todas las celdas de la hoja de calculo.
3. Seleccionar las celdas que desea bloquear y activar posteriormente el bloqueo de
estas celdas en el menú formato celdas, pestaña proteger.
4. Luego ir al menú Revisar y seleccionar la opción proteger hoja.
5. Desactivar la opción permitir a los usuarios seleccionar celdas bloqueadas.
6. Establecer una contraseña de seguridad, confirmarla y aceptar.
Una vez realizados estos pasos, notará que si intenta ubicarse en una de las celdas bloqueadas Excel no se lo permitirá.
EVITAR QUE LOS SUARIOS ABRAN UN ARCHIVO
Una vez que haya terminado de crear su archivo debe realizar los siguientes pasos para proteger el archivo contra apertura:
1. Una vez que se ha seleccionado la opción Guardar como Ir a Herramientas y
seleccionar opciones.
2. Seleccione la pestaña Seguridad
3. Digite la contraseña y confírmela
4. Clic en aceptar.
Nota: Se puede establecer contraseña para que el archivo no pueda ser modificado (contraseña de escritura), igualmente se puede seleccionar la opción de solo lectura en la cual podrá modificarse el archivo pero no podrán guardarse los cambios realizados al archivo.
Para comprobar la efectividad cierre el archivo e intente abrirlo, notará que Excel pedirá la contraseña. Digítela para poder abrir el archivo.
Nota: si pierde la contraseña no podrá abrir el archivo.
Suscribirse a:
Entradas (Atom)