excel avanzado - 2016 curso uba capacitarte
TRANSCRIPT
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
1/15
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
2/15
2
Funcin SI
La funcin SI es una funcin lgica. Comprueba si una cierta condicin es
verdadera o falsa.
Sintaxis
La funcin Si tiene la siguiente sintaxis:
SI(prueba_lgica;valor_si_verdadero;valor_si_falso)
Dnde:
prueba_lgica= la condicin puesta a prueba.
valor_si_verdadero= texto, valor numrico o frmula si la condicin es verdadera
valor_si_falso = texto, valor numrico o frmula si la condicin es falsa.
Para poner a prueba si una condicin es verdadera o no, se puede usar losoperadores, lgicos (< > diferente, < Inferior a, > Superior a, = Superior o igual a, = Igual.
Sintaxis de las funciones SI anidadas:
=SI(condicin1; valor_si_verdadero1, SI(condicin2; valor_si_verdadero2;valor_si_falso2))
En palabras, funciona de la siguiente forma:
Bsicamente, si la condicin es NO VERDADERA, aadiremos ms funciones SI.
Primero, comprobamos si A2 es inferior a B2. SI eso es cierto, introducimos A B.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
3/15
3
Anidar con otras funciones:
En algunos casos, puede que deba utilizar una funcin como uno de los
argumentos (argumento: valores que utiliza una funcin para llevar a cabooperaciones o clculos). El tipo de argumento que utiliza una funcin es especfico
de esa funcin. Los argumentos ms comunes que se utilizan en las funciones son
nmeros, texto, referencias de celda y nombres.) de otra funcin. Por ejemplo, la
siguiente frmula utiliza una funcin anidada PROMEDIO y compara el resultado
con el valor 50.
Datos:
Subtotales.
Los subtotales constituyen una manera rpida y sencilla de resumir datos en
listado. No es necesario crear frmulas. Excel crea la frmula, inserta las filas
correspondientes al subtotal y al total y efecta un esquema de los datos,
automticamente. As, los datos resultantes son fciles de formatear, colocar en un
grfico e imprimir. En resumen, aade lneas de subtotal a columnas de la lista.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
4/15
4
Para insertar subtotales, primero se ordena la lista para agrupar las filas cuyos
subtotales se desea calcular. Despus pueden calcularse los subtotales de
cualquier columna que contenga nmeros.
Si los datos no estn organizados en una lista, o si slo necesita un total, puede
utilizar Autosuma en lugar de subtotales automticos.
Cmo se calculan los subtotales
Excel calcula los subtotales con una funcin de resumen, como Suma o Promedio.
Puede mostrar subtotales en una lista con ms de un tipo de clculo a la vez.
Consolidar:
Teniendo informacin en tres hojas distintas o bien en tres archivos distintos,
podemos consolidar la informacin en una sola tabla.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
5/15
5
Se mostrar el cuadro de dilogo Consolidar en donde se deber especificar cada
uno de los rangos de las diferentes hojas. Puedes hacer clic en el botn de
seleccin de referencia para seleccionar los rangos fcilmente.
Al pulsar el botn Aceptar, Excel realizar la consolidacin de los datos y los
colocar en la nueva hoja de Excel.
Consolidar permite la utilizacin de distintas funciones como: suma. Promedio,
max, min, cuenta, contar nmeros, entre otras.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
6/15
6
Filtros avanzados:
Para realizar filtros avanzados en una tabla de Excel necesitamos copiar la
cabecera de la tabla para imponer luego los criterios por los cuales queremos
realizar la bsqueda.
Colocaremos el criterio debajo del campo que corresponda.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
7/15
7
Segn el ejemplo la tabla quedara filtrada por aquellos campos que contenga
Finanzas en la columna Departamento.
Tambin podemos utilizar operados, funciones como Y, O, etc.
Buscar Objetivo
Buscar objetivo es una tcnica utilizada para encontrar fcilmente el nmero que
cumple las condiciones necesarias para alcanzar un objetivo.
Si sabemos el resultado de una frmula, pero no estamos seguros del valor de uno
de sus argumentos, entonces Buscar objetivo nos ayudar a probar diversos
escenarios hasta encontrar el valor exacto que necesitamos. Por ejemplo,
supongamos que tenemos el siguiente problema.
Tengo un nmero que multiplicado por otro nmero X me deber dar el resultado
mostrado. Aunque este es un problema muy fcil de resolver matemticamente
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
8/15
8
ser de utilidad para mostrar la funcionalidad de Buscar objetivo. Empezar por
definir una frmula importante. En la celda D3 colocar la frmula que se deber
cumplir para obtener el resultado deseado, es decir, =C1*C2 deber dar como
resultado 14240.
Ahora debers hacer clic en la ficha Datos y seleccionarAnlisis Y si donde se
desplegar un men de opciones y debers elegir Buscar objetivo.
Mostrar el cuadro de dilogo Buscar objetivodonde debers llenar los siguientes
cuadros de texto.
Definir la celda. Es la celda que contiene la frmula
Con el valor. Este es el nmero que deseamos como resultado de la
frmula.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
9/15
9
Para cambiar la celda. La celda que ser modificada hasta tener el resultado
deseado.
Una vez que se han establecido los parmetros debes hacer clic en el botn
Aceptar y Excel comenzar a realzar los clculos necesarios. Cuando haya
encontrado un resultado adecuado se mostrar el cuadro de dialogo Estado de la
bsqueda de objetivo.
Tablas Dinmicas:
Las tablas dinmicas en Excel reciben su nombre por su capacidad de cambiar
dinmicamente la informacin agrupada con tan solo rotar las columnas o filas de
la tabla.
Como crearla:
Posicionados dentro de la tabla
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
10/15
10
Selecciona el comando Tabla dinmica que se encuentra dentro del grupo Tablas
de la ficha Insertar.
Abrir el cuadro de dilogo Crear tabla dinmica. Si es necesario podrs ajustar el
rango de datos que se considerar en la tabla dinmica.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
11/15
11
All se puede elegir si se desea colocar la tabla dinmica en una nueva hoja de
Excel o en una ya existente. Haz clic en el botn Aceptar y se crear la nueva
tabla dinmica.
En la parte izquierda del libro la tabla dinmica yen la parte derecha la lista de campos. Esta listade campos est dividida en dos secciones,primero la lista de todoslos campos de los cualespodremos elegir y por debajo una zona a dondearrastraremos los campos que darn forma alreporte ya sea como columna, fila, valor o como
un filtro.
Para completar la tabla dinmica debemosarrastrar los campos al rea correspondiente,colocar como columna el campo Producto y comofila al campo Ciudad. Finalmente como valorescolocar el campo Ventas.
Predeterminadamente Excel aplica la funcinSUMA a los valores y la tabla dinmica que resulta despus de hacer estaconfiguracin es la siguiente:
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
12/15
12
De esta forma cumple con el propsito del dinamismo de crear, partiendo de una
tabla, es posible crear un reporte de una manera fcil y sin la necesidad de utilizar
frmulas.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
13/15
13
Macros:
Nos permiten automatizar tareas que realizamos cotidianamente de manera que
podamos ser ms eficientes en nuestro trabajo. Una macro no es ms que una
serie de comandos o instrucciones que permanecen almacenados dentro de Excel
y que podemos ejecutar cuando sea necesario y cuantas veces lo deseemos.
En la pestaa Programador, haga click en Grabar Macro
Una ventana emergente le preguntar el nombre, el atajo, una localizacin para
guardarla y la descripcin de la macro.
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
14/15
14
Escriba lo que quiera aqu. Le ayudar el ser descriptivo con los ttulos de sus
macros. Puede acabar usando docenas de macros en una hoja de clculo grande.
Ser capaz de encontrar rpidamente macros individuales por el nombre le ahorrar
horas de frustracin ms tarde. Tambin es una buena prctica aadir una
descripcin para recordar lo que la macro hace realmente.
Dos cosas importantes a considerar aqu:
Mtodo abreviado: Las teclas de atajo le proporcionan un rpido acceso a la
macro. Un montn de funciones Excel por defecto ya tienen asignadas
atajos de la forma CTRL + Letra/Nmero. Para evitar conflictos con atajos ya
existentes, aada al menos dos teclas al atajoalgo como CTRL + SHIFT +A, o CTRL + A + 1
Guardar Macro en: En este men desplegable, puede decidir dnde guardar
la macro. Si elige Este Libro la macro estar disponible slo para su uso en
ese libro solamente. Eligiendo Nuevo libro, como habr adivinado, guardar
la macro en un nuevo libro. La opcin ms potente aqu es guardar la macro
en Libro Macro Personal. Las macros almacenadas en este libro estn
disponibles en todos los libros. Piense en ello como un repositorio central
para cada macro que crea. El fichero de macros del libro actual estar oculto
en la carpeta Windows AppData, pero se cargar automticamente cuando
empiece Excel, dndole acceso completo a todas las macros.
Pulsar el botn Aceptar todos los movimientos que realices quedan grabados.
Acciones a grabar en la Macro:
A partir de este momento toda accin que cometas
quedara grabada en tu macro: no tiene lmites, se puede realizar desde una simple
lista hasta inclusive armar una extensa tabla con instrucciones de frmulas y
funciones, tambin darle formato de pgina e inclusive enviar a imprimir, etc. Todo
aquello que desees puede estar incluido en tu macro.
Presionar el botn que antes era grabar, se convierte en Detener Macro
-
7/25/2019 Excel Avanzado - 2016 Curso UBA Capacitarte
15/15
15
Presionando el icono se abrir la siguiente ventana
En esta se mostrara todas macros que fueron guardadas, desde esta se podr
seleccionar para: Ejecutar, ver el Paso a Paso, Modificar. Crear una macro nueva,
Eliminar una existente, etc.