http://www.allexcel.com.ar/generales/tutoriales/VALIDACION%20DE%20DATOS/vali dacion.de-datos.html
FUNCIONES EN EXCEL 2003 Y EXCEL 2007 INTRODUCCION
Esta página está dedicada a definicion funciones de EXCEL, EXCEL 2007 y EXCEL 2003, con tutoriales paso a paso, ya que las FUNCIONES DE EXCEL son uno de los temas mas importantes de este valioso programa y cuando se combinan entre si, efectuando anidamiento de funciones tienen una potencia enorme, tan es así que podríamos pasarnos escribiendo libros enteros de funciones sin que el tema quede agotado..
Empezaré diciendo que Excel tiene más de 327 funciones divididas por categorías (sin contar claro está con las que el usuario puede definir), estas son:
1.- Funciones de complemento y automatización 2.- Funciones de cubo
3.- Funciones de base de datos 4.- Funciones de fecha y hora 5.- Funciones de ingeniería 6.- Funciones financieras 7.- Funciones de información 8.- Funciones lógicas
9.- Funciones de búsqueda y referencia 10.- Funciones matemáticas y trigonométricas 11.- Funciones estadísticas
12.- Funciones de texto
Aquí les dejo un archivo con la mayoría de ellas, con traducción ingles/castellano DEFINICION DE FUNCIONES
Empezaré con las mas comunes para luego ir agregando. La clasificación que hago es un tanto personal, como podrán ver, hay algunas funciones puestas en varias categorías en forma simultanea.
LAS MAS COMUNES
A estas funciones se puede acceder en forma rápida desde el icono ya que
haciendo clic en el triangulito se despliega un menú emergente con botones a todas ellas( Excel 2007 y 2003 ).
MAX MIN CONTAR
FUNCIONES CONDICIONALES
SI
CONTAR.SI
CONTAR.SI.CONJUNTO
SUMAR.SI
SUMAR.SI.CONJUNTO
FUNCION SI
La función SI sirve para tomar decisiones de acuerdo a una condición, por eso podríamos decir que es una función condicional, siendo la condición el resultado de la evaluación de una proposición lógica ( VERDADERO o FALSO), es decir; si el resultado es
VERDADERO se hace una cosa, y si es FALSO se hace otra.. Esta función tiene 3 argumentos
Ejemplo:
para lo cual estos datos se ponen en una Hoja de Excel y se usa la función SI de la siguiente manera
a esta empresa no le fue demasiado bien-
FUNCION CONTAR.SI
Esta función es una combinación de las funciónes CONTAR ySI , tiene dos argumentos, el primero es el rango cuyas celdas se desean contar y el segundo es el criterio que determina que celda sera contada o no
FUNCION CONTAR.SI.CONJUNTO
Excel 2007 incorpora una función nueva, CONTAR.SI.CONJUNTO, que es parecida a CONTAR.SI, que cuenta celdas teniendo en cuenta un solo criterio, en tanto que CONTAR.SI.CONJUNTO, cuenta celdas teniendo en cuenta múltiples criterios. SINTAXIS:
CONTAR.SI.CONJUNTO(rango1; criterio1; rango2; criterio2…) pudiendo especificarse mas de 127 rangos/criterios.
donde :
rango1: es el rango donde se cuentan las celdas, debido al criterio1, criterio2, etc. Los primeros dos argumentos son obligatorios.
Veremos un ejemplo donde se consideran 3 criterios:
y quiere saber cuantas veces el vendedor Juan Lopez aparece en la misma, en el sector Electrodomésticos con ventas que superan los 200 $.
Como se ve los criterios son:
1. "Juan López" 2. "Electrodomésticos" 3. ">200
entonces para responder a lo que el negocio quiere saber , se introduce la tabla en una Hoja de Excel y se usa la fórmula ( en la tabla se resaltan las filas que cumplen los 3 criterios)
como se ve la función CONTAR.BLANCO que está en la celda C9 cuenta las celdas en blanco que están en el rango C1:D7 que son 3.
FUNCION SUMAR.SI
INTRODUCCION
La función SUMAR.SI permite sumar valores de un rango de acuerdo a un criterio o condición.
La función SUMAR.SI tiene 3 parámetros:
El primero es la referencia o el rango que contiene los valore sobre los que se evaluará la condición.
El segundo es el que contiene el criterio a aplicar con el objeto de determinar que se suma y que no
El tercero es opcional, esto quiere decir que si la condición esta en el mismo rango donde se efectúa la suma, no hace falta el tercer parámetro, pero si el criterio esta en un rango y donde se hace la suma en otro (u otros )rangos, entonces tiene que colocarse el tercer parametro.
En este caso con dos parámetros alcanza puesto que el criterio esta en la rango E2:E5, que el mismo rango donde se efectúa la suma con la condición dada y no hace falta poner =SUMA(E2:E5;">160000";E2:E5)..Si en cambio tenemos esta otra tabla
aquí si hace falta el tercer parámetro ya que el rango donde se efectúa el criterio (D2:D5) no es el mismo que el rango donde se efectúa la suma (E2:E5).
Dejo como ejercicio averiguar las comisiónes que se cobran al vendedor por propiedades cuyo costo es inferior a $ 400.000.
Excel 2007 incorpora una función nueva, SUMAR.SI.CONJUNTO, que es parecida a SUMAR.SI, que suma celdas teniendo en cuenta un solo criterio, en tanto que
SUMAR.SI.CONJUNTO, suma celdas teniendo en cuenta múltiples criterios. SINTAXIS:
SUMAR.SI.CONJUNTO(rango de sumas; criterio_rango1; criterio1; criterio rango2; criterio2..)
donde:
rango de sumas: es un argumento obligatorio, en el que se suman una o mas celdas. criterio_rango1: es un argumento requerido que es el primer rango en el que se evalúa el criterio asociado ( criterio1).
criterio1: argumento obligatorio que es un criterio asociado a criterio_rango1 en la forma de número, expresión, referencia de celda o texto, que define en cual celda, en el rango de sumas, se adicionará debido al criterio_rango1.
Los otros argumentos son opcionales.
Veremos un ejemplo donde se consideran 3 criterios:
y se quiere saber cual fue la suma de las ventas de Juan López, en el sector Electrodomésticos con montos que superaron los 200 $.
Como se ve los criterios son:
1. "Juan López" 2. "Electrodomésticos" 3. ">200
entonces para responder a lo que el negocio quiere saber , se introduce la tabla en una Hoja de Excel y se usa la fórmula ( en la tabla se resaltan las filas que cumplen los 3 criterios)
FUNCIONES LOGICAS
Una proposición lógica es una afirmación que puede se VERDADERA o FALSA pero no ambas a la vez y justamente los argumentos de las funciones lógicas son proposiciones lógicas. También las funciones lógicas devuelven resultados VERDADERO o FALSO. Esta es una definición que se cumple en forma estricta solamente para las siguientes funciones: Y O NO FUNCION Y()
La función Y() ,como O() es una función lógica ya que sus argumentos son proposiciones lógicas, la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO ( aclaro que esta función puede tener un solo parametro sin dar error, aunque no tiene mucho sentido práctico)
Su sintaxis es:
Y(parámetro1;parámetro2;parámetro3;...)
La función devuelve VERDADERO si la evaluación de todos los parámetros es VERDADERA y dara FALSO si la evaluación al menos uno de sus parámetros es FALSA o si todos son FALSOS.
Veamos un ejemplo
FUNCION O()
Como Y() la función O() es una función lógica, porque sus argumentos son proposiciones lógicas o pruebas lógicas la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO., su sintaxis es
O(parámetro1;parámetro2;parámetro3;...)
La función devuelve FALSO si la evaluación de todos los parámetros es FALSO y dará VERDADERO si la evaluación al menos uno de sus parámetros es VERDADERO o si todos son VERDADEROS.
Veamos un ejemplo
FUNCION NO()
La función NO() invierte el valor lógico de los argumentos de las funciónes Y() y O(), por consiguiente se utiliza en combinación con ellas, su sintaxis es:
NO(Y(argumento1; argumento2; argumento3;...)) como puede verse en siguiente ejemplo
FUNCIONES DE BÚSQUEDA Y REFERENCIA
BUSCARV
HIPERVINCULO
FUNCION BUSCARV
La función BUSCARV busca datos que están en primera columna de una tabla(a esta tabla se la denomina matriz de búsqueda o de datos), si el valor es encontrado devuelve el dato asociado (valor que esta en la misma fila que el dato a buscar) de una columna especificada, la sintaxis es;
los primeros tres argumentos son obligatorios y el cuarto es opcional Veamos el siguiente ejemplo:
Un profesor tiene una tabla con las notas de un alumno puestas en números y quiere completarla poniendo las notas en palabras
vuelca estos datos en un libro de Excel poniendo en la Hoja1 la tabla a completar y en la Hoja2 la tabla con las equivalencias pero sin los rótulos para tener directamente la matriz de datos
despues coloca la siguiente fórmula en la celda E3 de la Hoja 1:
en la que D3 es una referencia donde está el contenido , que en este caso es el valoor 2, aunque hay casos en que por la naturaleza del problema, por ejemplo una consulta, la referencia puede al principio estar vacia, dando el error #N/A (no aplicable), en el tutorial ELIMINAR MESAJE DE ERROR EN BV, daremos una solución a este antiestético mensaje.
A continuación se arrastra la función hasta completar la tabla
en este caso la matriz de búsqueda está en otra hoja, pero puede estar en cualquier lado, incluso dentro de otra tabla.
FUNCION HIPERVINCULO
La función HIPERVINCULO nos permite enlazar:
1. Una celda, con una ubicación especifica de un libro.. 2. Una celda, con un documento en nuestra computadora. 3. Una celda, de con un sitio o pagina web.
Su sintaxis general es:
Estamos en la celda C1 de la Hoja1 y queremos enlazar con la celda D1 de la Hoja3, suponiendo que en D1esta escrita la palabra "EMPLEADOS", el enlace se escribe como se nuestra
llevandonos a "EMPLEADOS" en la Hoja3.
Un mismo resultado se hubiera obtenido si en lugar de poner "Libro1" se hubiera puesto "#".
que nos lleva a una imagen visualizada en algún programa para ver imágenes.
Finalmente si queremos un vínculo a una sitio web o a una página de este, lo que pondremos en el primer parámetro sera su dirección completa. Por ejemplo:
que nos llevará a Google de argentina.
FUNCIONES DE TEXTO CONCATENAR
FUNCION CONCATENAR
La función CONCATENAR permite unir dos o mas elementos de texto que estan contenidos en celdas diferentes. También permite unir textos puestos entre comillas directamente en los argumentos de la función. Su sintaxis es:
Esta función puede tener como máximo 30 argumentos. Ejemplos:
Si tenemos en la celda A1 el texto YAHOO y en la celda B5 ARGENTINA, nos queda YAHOO ARGENTINA en la celda donde se introduce la fórmula. Veamos
notar que el segundo argumento es un espacio (" ") para separar ambas palabras y que el formato color no se tiene en cuenta.
Uniendo textos directamente usando comillas
También se pueden concatenar textos, sin usar la función CONCATENAR y empleando en su lugar el símbolo ampersand ( & ), como podemos ver:
FUNCIONES MATEMATICAS SUMAPRODUCTO
Si en una Hoja de Excel tenemos las tablas A (con borde rojo) y B (con borde verde), las cuales tienen el mismo nùmero de filas y de columnas, podemos definir celdas que ocupan la misma posición relativa respecto de A y B, a estas celdas se las denomina "celdas correspondientes". Por ejemplo en la figura
las celdas C5 y G5 son correspondientes.
Ahora estamos en condiciones de definir la función SUMAPRODUCTO.
La función SUMA PRODUCTO multiplica el contenido de las celdas correspondientes de hasta 30 tablas y devuelve la suma de esos productos.
La sintaxis de SUMAPRODUCTO es:
SUMAPRODUCTO(taba1; tabla2; tabla3;...)
En la figura de arriba tenemos un ejemplo con 2 tablas. Notar que hubiéramos llegado al mismo resultado con la función SUMA usando como argumentos los productos de las celdas correspondientes
FUNCIONES DE FECHA Y HORA
HOY
FUNCION HOY
ANALISIS Y SI
Hay una herramienta no muy conocida en Excel 2007 que se llama “Análisis y si”, presentada de esta manera no dice mucho pero paro si la llamamos “¿Que pasaría… si…?” me parece que aclara mas las cosas pues nos induce a pensar en muchas cosas que nos podemos preguntar en nuestra vida cotidiana como: ¿Que pasaría si el dólar aumenta? o ¿Que pasaría en mi presupuesto familiar si disminuyo los gastos de diversión? o ¿Que pasaría con la cuota que tengo que pagar al banco si este me disminuye el interés en un punto? y podría seguir dando ejemplos. Excel nos puede ayudar a responder a estas preguntas y a otras mucho mas complejas , pues considera los “gastos de diversión”, “la cotización del dólar” y “el interés en un préstamo” como variables y responde con un informe de acuerdo al cambio que se produzca en estas. En este punto aprovecho para decir que en Excel 2003 “Análisis y si” se llama “Escenarios” . Después de lo dicho en el párrafo anterior, nada mejor que un ejemplo concreto.
Supongamos que todos los meses hacemos un presupuesto familiar y queremos saber que variables debemos ajustar para el porcentaje remanente sea de un 20% sobre los ingresos, para esto hacemos un modelo de gastos familiares
manera (teniendo en cuenta que hay gastos que no se pueden alterar, como la hipoteca y los impuestos).
o sea que tenemos 4 escenarios o dicho de otra manera, ¿Que pasaria con mi remanente si mi combinación de variables fueran esta?. Para que Excel nos haga un informe,
debemos introducir las variables de los distintos escenarios.
Como ya mencionamos la herramienta "Analisis y si" es la adecuada. Para acceder a ella , vamos a la pestaña Datos y de allí al panel "Herramientas de datos" , pulsamos en el ícono , luego aparece un manu desplegable en el que pulsamos en "administrador de escenarios"
vamos a introducir el Escenario1pulsando en agregar, en el panel que aparece, le
ponemos el nombre, establecemos las celdas cambiantes y finalmente podemos hacer un comentario
finalmente al aceptar se presenta nuevamente el panel "Administración de escenarios" pero esta vez con el Escenario1 definido
Este mismo proceso se repite hasta que terminemos de ingresar los datos de todos los escenarios
y es aquí cuando podemos apretar el botón Mostrar. Esto hará que los resultados en el modelo cambien de acuerdo al escenario elegido. Por ejemplo, si elegimos el Escenario3 y apretamos Mostrar, veremos en la hoja que el porcentaje remanente respecto de los ingresos disminuye en a un 36%. Así podemos elegir cada vez un escenario distinto y ver el resultado directamente en la hoja, por ejemplo el Escenario4 nos da justo un remanente del 20%.
Sin embargo la verdadera utilidad del "Análisis y si", es cuando apretamos el botón "Resumen", al hacer esto se presenta la siguiente pantalla
donde en "Celdas de resultado" deberemos poner la o las celdas donde queremos que aparezcan los resultados, en nuestro modelo es la celda D15.
que tiene nuestro modelo al momento de hacer el resumen (en este caso particular es el scenario4)
Observar que si se oprime el signo + señalado por la flecha, aparecen los comentarios, esto es lo que se llama Agrupación y Esquema para ocultar ciertas filas. .
Para que la tabla quede mas clara, es conveniente ponerles nombre tanto a las celdas cambiantes como a las cedas de resultado..
Una forma de dar nombres a muchas celdas en forma simultánea es, en nuestro modelo, seleccionar el rango :A4:B12 y a continuación ir a la pestaña "Fórmulas" y en el panel "Nombres definidos" pulsar en "Crear desde selección"
en el que marcamos "columna izquierda", el nombre de la celda de resultados se da como en el tutorial PONER NOMBRES A RANGOS
Finalmente la tabla queda como se muestra
VALIDACION DE DATOS
Cuando se introducen numerosos datos en una tabla, existe el riesgo de que cometamos errores, estos errores pueden ser por tipeo o debido a que la información ingresada sea incorrecta. Se puede disminuir el riesgo de cometer errores, con una herramienta de Excel llamada VALIDACION DE DATOS, la cual impide introducir datos erróneos a medida que llenamos una tabla. Ejemplos de esto podrían ser no introducir números superiores a 2000 o no introducir números decimales, 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. Otra de las utilidades de esta herramienta es evitar incoherencias cuando se comparte un libro con otras personas(multiusuarios).
Los tipos de datos que se pueden validar son:
1. Números
Se puede establecer que el número que se introduce a una celda sea decimal o entero, que este entre un mínimo y un máximo, excluir un número o intervalo de números y finalmente se puede utilizar una fórmula para hacer el cálculo de validación
2. Fechas y horas
En este caso se pueden excluir fechas u horas, establecer una fecha máxima, mínima o un intervalo y también establecer una fórmula para determinar las fechas válidas. 3. Longitud
4. Lista desplegable de valores
Se puede establecer una lista desplegable con los valores admitidos por una delda Veamos un ejemplo del funcionamiento de la herramienta VALIDACION DE DATOS. Si queremos validar la entrada de números entre 0 y 1 inclusive, lo primero que debemos hacer es seleccionar el rango de validación, o sea las celdas que se deseen controlar
luego en la pestaña "Datos" vamos al panel "Herramientas de datos"
donde pulsando en la flecha se despliega el siguiente menú
en la figura aparece la pestaña "Configuración", que ya tiene los valores para validar la entrada de números entre 0 y 1 inclusive.
se puede dejar este mensaje o pasar directamente a la pestaña "Mensaje de error"
hay 3 estilos de mensajes de error
Grave: representado por el ícono que aparte del mensaje tiene una advertencia sonora.
Advertencia: representado por el ícono con una advertencia sonora mas leve.
donde podemos elegir entre las 4 opciones que nos ofrece.
Lo último que nos queda por decir, antes de empezar con los tutoriales, es como eliminar la validación.
En el panel "Validación de datos" pulsamos en "Borrar todos" , como indica la flecha roja, y luego aceptamos
MACROS INTRODUCCION:
Excelsa es programable, lo que puede sorprender a muchos, y si las funciones y el
anidamiento de funciones por ellas mismas le dan mucha potencia, esta nueva cualidad le agrega mucha mas, toda la potencia que el conocimiento y la habilidad que un
estos van desde simples instrucciones de una o dos líneas de código hasta cosas mucho mas complejas. Lo interesante de todo esto es que para hacer MACROS no hace falta saber programar y aun así se pueden generar macros muy útiles y que nos pueden ahorrar mucho tiempo y trabajo. Este tipo de MACROS es la que se puede generar con la GRAVADORA DE MACROS.
Es muy común, en el trabajo diario , tener que hacer tareas repetitiivas como por ejemplo hacer una plantilla con un cierto formato, título, encabezamientos ,etc y sería muy
conveniente poder realizarla apretando un botón, bueno, pues esto se pude realizar con la grabadora de macros, la cual grava estas acciones y las traduce a código ( programación VBA)
Supongamos que todos los dias tengo que generar una lista con el movimiento de un comercio de computadoras, para organizar los datos que iremos ingresando, es conveniente escribir los encabezados de cada columna, supongamos que estos encabezados son
y quiero que esten como se ve en la figura de arriba, también quiero que estos
encabezados aparezcan automáticamente todas las mañanas(al apretar un botón por ejemplo). Como no se nada de programación VBA voy a utilizar la grabadora de macros. Hay tres formas de poner en funcionamiento la grabadora de macros:
1. Vamos a la pestaña Vista y en la sección Macros desplegamos el submenú Macros y dentro de este submenú seleccionamos la opción Grabar macro
de cualquiera de estas dos formas aparece el cuadro de dialogo Grabar Macro
donde podemos dar un nombre a la macro, sin dejar espacios en blanco.
En Método abreviado: elegimos una combinación de la tecla CTRL + "una tecla del teclado", para ejecutar la macro, cuidando de no poner una combinación utilizada por Excel en alguno de sus atajos.
En Guardar macro en: podemos elegir guardar la macro en el libro activo, en el libro de macros personal o en otro libro.
En Descripción: podemos describir que hace de la macro o cualquier otro comentario.
Finalmente pulsamos en aceptar y el botón que estaba en la barra de estado se
(Con este botón también se puede detener la grabación.)
No tenemos porque entender este código, solo dire que se puede hacer lo mismo con un código mas reducido o dicho de otra manera, la grabadora de macros genera un código redundante, que se puede reducir aprendiendo Programación VBA
Para que la macro haga su tarea, se puede:
1. utilizar el método abreviado, que en nuestro caso es CTRL+ r .
2. Ir a las pestaña Programador, sección Código y luego pulsar en Macros
luego aparece el cuadro de diálogo Macro, donde aparece la macro creada, para luego apretar el botón ejecutar
También con este cuadro de diálogo podemos eliminar macros.
Para crear un botón vamos a la pestaña Programador, sección controles y luego pulsamos en el icono Insertar
En los Controles de formulario pulsando donde señala la flecha aparece un puntero en forma de cruz que nos permite colocar el botón en un lugar cualquiera y automáticamente aparece la ventana Asignar macrodonde se puede seleccionar la macro a la que le queremos asignar el botón
Veremos que cuando el mause pasa por el botón, este se transforma en una mano, luego hacemos clic y se dispara la macro con el resultado esperado
PROGRAMACION VBA PARA EXCEL
INTRODUCCION:
Si bien la grabadora de macros es muy útil y genera un código siempre correcto, tiene dos desventajas:
1. genera mas código que el necesario.
2. sólo puede hacer macros con instrucciones secuenciales y sin nada de lógica, o sea que no pueden tomar desiciones ante un evento.
Ambas desventajas se pueden solucionar con la programación VBA que quiere decir programación visual basic para aplicaciones, lo lamento pero aquí no tenemos mas remedio que aprender a programar y eso es lo que van a ir aprendiendo con los tutorarles de este apartado.
VBA es una programación que está íntimamente relacionada con los libros y las hojas de cálculo y para esto Excel cuenta con un editor de programación donde se pone el código, a este se puede acceder, en Excel 2007, yendo a la pestaña programador y luego a la sección código donde hacemos clic en Visual Basic
las macros que se escriban aquí, estaran relacionadas con la Hoja1.
Comencemos por lo mas simple y escribamos una macro que seleccione la celda B5de la Hoja1 del libro VBAProject (Libro2)
donde podemos ver que el código
no tiene espacios y termina con "( )" . Para ejecutar este código pulsamos en el icono o en la tecla F5 para que aparezca el panel Macros
donde puede verse el nombre de la macro que ya está seleccionada, luego pulsamos en "ejecutar" y despues en el icono , o seleccionando " Alta + F5 que nos lleva a la pantalla con el resultado
que es la selección de la celda B5.
y si lo queremos borrar
A estas alturas estamos en condiciones de explicar estos sencillos códigos:
En la programación VBA se trabaja con OBJETOS ( Hojas, celdas, Rangos, etc) que como todo objeto, tiene propiedades, por ejemplo el objeto celda pude tener la propiedad de alto, ancho, estar seleccionada, tener un valor, o no tener ninguno, etc
En los códigos que hemos escrito tenemos los objetos Range("B5") ( celda B5) con la propiedad de estar seleccionada y el objeto Range("D8") ( celda D8) con la propiedad de tener un número (2007) y despues estar vacía.
CODIGOS MAS SIMPLES PARA EMPEZAR 1-Seleccionar una Celda
Range("A1").Select
2-Escribir en la celda que está seleccionada en el momento actual Activecell.FormulaR1C1="Pedro"
la combinación los códigos 1 y 2 es equivalente a esta sola línea: Range("A1").Value=" pedro"
El uso de FormulaR1C1 sera explicado mas adelante 3-Letra Negrita
Selection.Font.Bold = True
4-Letra Cursiva
5-Letra Subrayada
Selection.Font.Underline = xlUnderlineStyleSingle
6-Centrar Texto With Selection
.HorizontalAlignment = xlCenter
End With
7-Alinear a la izquierda With Selection
.HorizontalAlignment = xlLeft
End With
8-Alinear a la Derecha With Selection
.HorizontalAlignment = xlRight
End With
9-Tipo de Letra(Fuente) WithSelection
.Font .Name = "Arial"
EndWith
10-Tamaño de Letra(Tamaño de Fuente) With Selection.Font .Size = 12
End With
11-Copiar Selection.Copy
ActiveSheet.Paste
13-Cortar Selection.Cut
14-Ordenar Ascendente
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
15-Orden Descendente
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
16-Buscar
Cells.Find(What:="César", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate
17-Insertar Fila Selection.EntireRow.Insert 18-Eliminar Fila Selection.EntireRow.Delete 19-Insertar Columna Selection.EntireColumn.Insert 20-Eliminar Columna Selection.EntireColumn.Delete
21-Abrir un Libro
Workbooks.OpenFilename:="C:\Mis documentos\Tablas dinamicas.xls"
22-Grabar un Libro
ActiveWorkbook.SaveAsFilename:="C:\Mis documentos\tablas.xls", FileFormat_ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False
Significado de la FORMULA R1C1
La FORMULA R1C1 se emplea para colocar el resultado de una línea de código en la celda que actualmente está activa.
Veamos el siguiente caso
supongamos que queremos sumar los números de de la columna D y que el resultado aparezca en la celda F6 que es la que está seleccionada, el código que se debería escribir es el siguiente
El paréntesis destacado en rojo tiene por objetivo cubrir el tango donde están los números a sumar, o sea, desplazarme 2 columnas a la izquierda [-2] con 5 y 2 filas hacia arriba es decir
[-5] y [-2]. Se entiende que R significan filas y C columnas y que anteponemos un - si nos desplazamos hacia la izquierda o hacia arriba. Cuando escribimos una función, como en el caso anterior, siempre debe ser escrita ActiveCell.FormulaR1C1 =
"=SUM(R[]C[]:R[]C[])", pues el segundo igual es que caracteriza a la función y el
paréntesis el rango donde se aplica. Lo que se acaba de hacer es lo mismo que dolocar =SUMA(D1:D3) en la celda F6
Estructuras de iteracion
Frecuentemente algunas líneas de código de repiten muchas veces con el consiguiente aumento del tamaño del programa. Esto se solucionado mediante los llamados
estructuras de iteración, también llamadas ciclos de repetición o bucles. Estos son:
While - Wend Do - While - Loop Do - Until --Loop For - Next
Estructuras de desicion:
.If - Then - Else Select - Case
Las tablas dinámicas (I)
17.1. Crear una tabla dinámica
Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc.
Para aquellos que tengais conocimientos de Access es lo más parecido a una consulta de referencias cruzadas, pero con más interactividad.
Veamos cómo podemos crear una tabla dinámica a partir de unos datos que ya tenemos.
Supongamos que tenemos una colección de datos de los artículos del almacen con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo.
Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.
Para ello vamos a la pestaña Insertar y hacemos clic en Tabla dinámica (también podemos desplegar el menú haciendo clic en la flecha al pie del botón para crear un gráfico dinámico).
En nuestro caso indicamos que vamos a seleccionar los datos de un rango de celdas y que queremos ubicarla en una hoja de cálculo nueva.
Podríamos crear una conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel.
En el caso de seleccionar la opción Selecciona una tabla o rango debemos seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras.
Pulsamos Aceptar para seguir.
Desde este panel podemos personalizar la forma en que van a verse los datos en
la tabla dinámica.
Con esta herramienta podríamos contruir una tabla dinámica con la siguiente
estructura:
- Una fila para cada una de las Referencias de la tabla.
- Una columna para cada uno de los Meses de la tabla.
- En el resto de la tabla incluiremos el total del Importe para cada
Para ello simplemente tendremos que arrastrar los elementos que vemos listados a
su lugar correspondiente al pie del panel.
En este ejemplo deberíamos arrastrar el campo REFa , el campo
MES a y finalmente el campo IMPORTE a la sección .
Tras realizar la tabla dinámica este sería el resultado obtenido.
Podemos ver que la estructura es la que hemos definido anteriormente, en los
rótulos de fila tenemos las referencias, en los rótulos de columnas tenemos los
meses y en el centro de la tabla las sumas de los importes.
Con esta estructura es mucho más fácil analizar los resultados.
El panel lateral seguirá pudiéndose utilizar, así que en cualquier momento
podremos quitar un campo de un zona arrastrándolo fuera.
Con esto vemos que en un segundo podemos variar la estructura de la tabla y
obtener otros resultados sin casi esfuerzos.
Si arrastrásemos a la zona de datos los campos Cantidad y Total, obtendríamos la
Puede que no visualices la tabla de la misma forma, al añadir varios campos en la
sección Valores el rótulo aparecerá en una las secciones de rótulos, si te
aparece en Etiquetas de columna despliega la lista asociada a él y selecciona la
opción Mover a rótulos de fila.
Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su
totalidad y presionar la tecla Supr.
17.2. Aplicar filtros a una tabla dinámica
Otra característica útil de las tablas dinámicas es permitir filtrar los resultados y así visualizar unicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante.
Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable.
Por ejemplo, si pulsamos sobre la flecha del rótulo Etiquetas de columna nos aparece una lista como vemos en la imagen con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para marcar todas las opciones en este caso todos los meses.
Si dejamos marcados los meses Enero y Febrero, los otros meses desaparecerán de la tabla, pero no se pierden, en cualquier momento podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Seleccionar todo).
Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas, por ejemplo podemos seleccionar ver los articulos con referencia 1236 de Abril.
17.3. Obtener promedios en una tabla dinámica
Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio, puede interesarnos modificar esas fórmulas por otras como pueden ser sacar el máximo o el mínimo, el promedio, etc.
Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos aparece un menú emergente con diferentes opciones, debemos escoger la opción Configuración de campo de valor... y nos aparece un cuadro de diálogo como el que vemos en la imagen.
En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc.
También podemos abrir el cuadro de diálogo con el botón de la pestaña Opciones.
17.4. Gráficos con tablas dinámicas
Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos.
Al pulsar este botón se abrirá el cuadro de diálogo de Insertar gráfico, allí deberemos escoger el gráfico que más nos convenga.
Luego, la mecánica a seguir para trabajar con el gráfico es la misma que se vió en el tema de gráficos.
Para practicar estas operaciones te aconsejamos realizar Ejercicio trabajando con tablas dinámicas.
Unidad 5. Los datos (I)
Ya hemos visto que Excel se utiliza principalmente para introducir datos, ya sea literales como fórmulas. En este tema nos vamos a centrar en algunas de las operaciones típicas que se pueden realizar sobre ellos.
5.1. Eliminar filas duplicadas
idénticas. A menos que lo que nos interese sea estudiar la frecuencia con la que aparece un determinado registro, la mayoría de las veces no nos interesará tener duplicados, porque no aportan información adicional y pueden comprometer la fiabilidad de las estadísticas basadas en los datos.
Por ejemplo, si disponemos de un listado de trabajadores y queremos saber la media de edad, el cálculo se vería comprometido en el caso de que un mismo trabajador apareciese varias veces.
Para eliminar filas duplicadas:
- Deberemos tener como celda activa uno de los registros a comprobar, de forma que, si existen varias tablas distintas, Excel sepa interpretar a cuál nos referimos. Visualizarás un marco alrededor de todos los registros que se verán afectados por la comprobación.
- En la prestañaDatos pulsamos Quitar duplicados .
Si te quieres asegurar de que realmente se comprueban las filas que deseas, puedes seleccionarlas manualmente antes de pulsar el botón.
Para practicar estas operaciones te aconsejamos realizar el Ejercicio Eliminar filas duplicadas.
5.2. La validación de datos
La validación de datos nos permite asegurarnos de que los valores que se introducen en las celdas son los adecuados; pudiendo incluso mostrar un mensaje de error o aviso si nos equivocamos.
Para aplicar una validación a una celda.
- Seleccionamos la celda que queremos validar.
Nos aparece un cuadro de diálogo Validación de datos como el que vemos en la imagen donde podemos elegir entre varios tipos de validaciones.
En la sección Criterio de validación indicamos la condición para que el datos sea correcto.
Dentro de Permitir podemos encontrar Cualquier valor, Número entero, Decimal, Lista, Fecha, Hora, Longitud de texto y personalizada. Por ejemplo si elegimos Número entero, Excel sólo permitirá números enteros en esa celda, si el usuario intenta escribir un número decimal, se producirá un error.
Podemos restringir más los valores permitidos en la celda con la opción Datos, donde, por ejemplo, podemos indicar que los valores estén entre 2 y 8.
Si en la opción Permitir: elegimos Lista, podremos escribir una lista de valores para que el usuario pueda escoger un valor de los disponibles en la lista. En el recuadro que aparecerá, Origen: podremos escribir los distintos valores separados por ; (punto y coma) para que aparezcan en forma de lista.
En la pestaña Mensaje de entrada podemos introducir un mensaje que se muestre al acceder a la celda. Este mensaje sirve para informar de qué tipos de datos son considerados válidos para esa celda.
En la pestaña Mensaje de error podemos escribir el mensaje de error que queremos se le muestre al usuario cuando introduzca en la celda un valor incorrecto.
Unidad 5. Ejercicio paso a paso: Crear una validación de datos Objetivo.
Practicar cómo crear una validación de datos en Excel2010.
Ejercicio paso a paso.
1. Si no tienes abierto Excel2010, ábrelo para realizar el ejercicio.
2. Abre un nuevo libro de trabajo (si acabas de abrir Excel ya dispondrás de un libro en blanco).
3. Selecciona la celda A2.
4. Accede a la pestaña Datos.
5. Haz clic en el botón Validación de datos.
6. En la pestaña Configuraciónescoge Número entero en Permitir.
7. En Datos selecciona entre.
8. En Mínimo escribe 5.
9. En Máximo escribe 500.
10. En la pestaña Mensaje de entrada escribeen Título:esta celda solo admite.
11. En Mensaje de entrada escribe números enteros entre 5 y 500.
12. En la pestaña Mensaje de errorescogeen Estilo: el tipo Advertencia.
13. En el Título escribe Se ha producido un error
14. En Mensaje de error escribe Debe escribir una cifra sin decimales que se encuentre entre los valores 5 y 500.
15. Pulsa sobre Aceptar.
Vamos a comprobar el funcionamiento.
16. Sitúate en la celda A2. Fíjate como aparece, en forma de etiqueta, el mensaje entrante que has definido, el título aparece en negrita y debajo aparece el mensaje.
17. Escribe 3 y pulsa Intro para aceptar el valor.
Se debe abrir el mensaje de aviso, fíjate en el icono de advertencia, en el título de la ventana y en el mensaje.
18. Pulsa sobre No para volver a la celda e introducir un valor correcto.
19. Escribe ahora 300 y pulsa Intro.
Al salir de la celda ya no sale el mensaje de error, es número introducido es correcto.
Vamos a eliminar la validación
20. Sitúate sobre la celda A2.
21. Haz clic en el botón Validación de datos.
22. Pulsa sobre Borrar todos.
23. Pulsa sobre Aceptar.
