TABLAS PIVOT

- Como armarlas:
o Elegir los datos que usará la tabla dinámica.
o Ir a la pestaña insert de ribbon y elegir PivotTable.
o Elegir en dónde insertar la nueva tabla (casi siempre es mejor en una nueva hoja de cálculo).
o Elegir ok!
- En una tabla dinámica las variables pueden cumplir dos funciones, pueden ser:
o Labels: asignan los valores que irán en los títulos de las filas y columnas.
o Values: serán el contenido interno de la tabla.
- Aunque no es necesario, labels suelen ser variables categóricas y values suelen ser numéricas.
- Lo que se hace para agregar información a una tabla dinámica es arrastrar las variables de la lista de
variables a alguno de los cuatro roles que puede cumplir:
o Column labels: asigna los valores de las columnas.
o Row labels: asigna los valores de las filas.
o Values: da lugar a los valores internos de la tabla.
o Filter: se usa para filtrar los datos de la tabla.

Si queremos cambiar las variables lo único que tenemos que hacer es cambiar los campos que ponemos en
cada valor para que haga distintas cosas la tabla, reorganizar los datos es muy simple
o Podemos poner también dos variables dentro del coso de columnas entonces te va a aparecer para
una misma categoría el precio por unidad y el total de ventas por ejemplo
- Las tablas dinámicas no se actualizan si cambio los datos en el origen entonces para que se actualicen los
datos tengo que ir a table analyze y darle a refresh
- Las cosas de diseño en gral no la vamos a usar pero en la parte de totales y eso hay cosas útiles para sacar
datos que no nos interesen o agregar cosas que nos interese ver en la tabla
- Noten que cuando uno se posiciona sobre una tabla dinámica, aparecen dos nuevas pestañas en ribbon:
Analyze y Design.
o Analyze: tiene opciones que hacen al contenido de la tabla, así como herramientas para refrescar los
datos o actualizar de dónde se obtienen.
 o Design: tiene opciones referidas al diseño de la tabla y es en donde se encuentran herramientas que
permiten agregar o quitar filas o columnas de totales y subtotales.

TABLAS DINAMICAS MULTINIVEL:

- Tablas dinámicas multi-layered:

o Supongamos que queremos tener diferentes niveles de resumen de los datos, por ejemplo en el
caso de ventas de productos queremos que en la tabla figuren las cantidades vendidas por país y
también por ciudad. Hacer esto con fórmulas podría ser MUY pero MUY tedioso, pero con tablas
dinámicas son 5 pasos.
§ Arrastrar Ship country a Row Labels.
§ Arrastrar Ship city a Row Labels debajo de Ship country.
§ Arrastrar Category a Column Labels.
§ Arrastrar Quantity a Values.
- Cuando se arman tablas dinámicas con varios niveles, uno puede colapsar y expandir los niveles de las filas
para visualizar mejor la información. Para ello están los recuadros con los signos "+" y "-" al lado del título de
cada fila. También se puede hacer esto (con más opciones) haciendo click derecho sobre el título de las filas
y yendo a las opciones de "Expand/Collapse" (son muy útiles las opciones expand entire field y collapse
entire field).
- Hasta ahora hemos mostrados la suma de los valores, pero puede ser que nos importe mostrar otro valor
de resumen (el promedio, el máximo, el mínimo, etc.). La forma de hacer esto es acceder a "Value Field
Settings" haciendo click sobre la flecha que figura en el nombre de la variable que se encuentre en values.
Dentro de "Value Field Settings" se puede elegir qué valor tomará la celda, esto se encuentra en la pestaña
"Summarize Values By".
o La pestaña "Show Values As" es ligeramente más compleja y tiene opciones como por ejemplo:
mostrar los valores como el porcentaje que representa del total de la fila o columna

FILTRADO EN TABLAS DINAMICAS:

- Filtrado en tablas dinámicas: Uno puede llegar a querer mostrar solamente los datos que cumplen con
determinados requisitos. Para hacer algo así, uno debe aplicar filtros. Hay 2 formas principales de hacer esto
en tablas dinámicas:
 o Usando el recuadro filter y arrastrando la variable por la que uno quiere filtrar al mismo. De modo
que encima de la tabla aparezca un campo que permite filtrar los datos.
o Usando filtros directamente sobre las variables usadas como labels, haciendo click sobre las flechas
que aparece al lado cuando uno pone encima el mouse.
- ES MUY IMPORTANTE IR FIJANDOSE QUE LOS DATOS ESTAN SIENDO FILTRADOS. LA FORMA DE HACERLO ES
VER QUE VARIABLES TIENEN EL SIMBOLO DE FILTRADO AL LADO
- Recuadro filter: Agregando variables en el recuadro filter (puede agregarse más de una): por ejemplo en la
siguiente tabla se filtran los datos en base a la categoría del producto vendido.

o Otra manera de filtrar es con los menús desplegables que aparecen en las filas y columnas de la
tabla dinámica
o La tercera opción es filtrar usando los menús desplegables de las variables que aparecen en la lista
de variables y que están siendo usadas como titulo de filas o columnas. Con este método aparecen
más opciones de filtrado

PIVOT CHARTS:

- Estamos viendo que las tablas dinámicas son muy buenas para organizar y entender los datos. A su vez ya
vimos que los gráficos ayudan a entender los patrones que siguen los datos à PIVOT CHARTS COMBINA
TABLAS PIVOT Y GRAFICOS
o Pivot Charts son gráficos que muestran la información contenida en una tabla dinámica y que se
actualizan a medida que se actualiza la table
§ LA OPCION PARA CREARLOS ESTA EN LA PESTAÑA DE ANALYZE
- Más allá del hecho de que se actualizan automáticamente en base a la información de la tabla, los demás
aspectos de los Pivot Charts son similares al de los gráficos comunes. De hecho generan las mismas
pestañan en ribbon, a la cual se adiciona "analyze" (que contiene opciones básicas de la tabla dinámica)

Las tablas dinámicas son muy buenas para hacer exploración de datos. Pero no dejan de tener sus bemoles
(como todas las herramientas que vimos, hay que saber cuándo usarlas). Por ejemplo, no se actualizan
automáticamente cuando se modifican los datos de origen (hay que apretar refresh). A su vez, puede ser
complicado trabajar con fórmulas y funciones que referencien datos que están en una tabla dinámica.

Tutoriales para pivots:

- Tutorial 1 en español (va primero): https://templates.office.com/es-ar/tutorial-de-tablas-dinámicas-

tm16400647?omkt=es-AR
- Tutorial 2 en español (va después): https://templates.office.com/es-ar/sacar-más-partido-a-las-tablas-
dinámicas-tm16410255?omkt=es-AR

MATRICES
- En Excel a las matrices se las llama “arrays” y existen funciones para hacer todas las operaciones
previamente mencionadas. Las funciones de “arrays” tienen dos particularidades (van a quedar más clara
con los ejemplos):
o Para que se calculen bien no hay que apretar “Enter” sino “Crtl + Shift + Enter”
o Antes se debe saber la dimensión del “array” resultado, seleccionar un rango de celdas de la misma
dimensión y escribir la fórmula sólo en la primera celda de ese rango seleccionado (una vez escrita
hay que apretar “Crtl + Shift + Enter” y se calcula todo el rango).
§ MUY IMPORTANTE HACER LO DE CONTROL + SHIFT + ENTER PORQUE SINO NO ANDA
• Esto es clave para poder hacer las operaciones porque si no se hacen en este orden
no funciona
o Cuando hacemos el ctrl + shift + enter a la formula se le ponen unas {} a los
bordes que indica que son operaciones de matrices
- Adicionalmente se puede multiplicar una matriz por un escalar y además sumarle a toda la matriz un escalar
(esta operación suele no estar permitida en “lápiz y papel”, porque es muy fácil hacerla de manera
matricial).
o Para ello simplemente se tiene que elegir el rango en donde estará la matriz resultante, seleccionar
el rango que contiene la matriz que se quiere modificar (por ejemplo “A1:B2”) y realizar una
operación del tipo “A1:B2*2” o “A1:B2+2”, en vez de “2” puede ser también una referencia a una
celda que tenga un número.
- Para el resto de las operaciones existen funciones especiales que nos permiten hacerlas:
o Para trasponer se usa “TRANSPOSE()”.
o Para multiplicar dos matrices se usa “MMULT”.
§ Cuando hacemos multiplicaciones de matrices para ver si nos dieron bien los cálculos, hacer
la mult de la inversa por la original, que nos tiene que dar la identidad, podemos tener -0
que es algo que se acerca mucho a 0 desde lo negativo y Excel no puede procesar tantos
decimales entonces pone -0 pero en realidad podemos interpretarlo como 0 directamente
o Para calcular el determinante de una matriz se usa “MDETERM()” (en este caso se puede apretar
sólo “enter”).
§ LO DEL DETERMINANTE NOS SIRVE PORQUE PARA QUE LA MATRIZ PUEDA INVERTIRSE TIENE
QUE DAR DIFERENTE A 0
o Para obtener la inversa de una matriz se usa “MINVERSE()”.
En todos los casos hay que tener de antemano seleccionado el rango en donde quedará la matriz.

EJEMPLO:
- Un sistema de ecuaciones lineales puede expresarse como una matriz de coeficientes “A”, por un vector de
variables “x” que debe ser igual a un vector resultados “b”, o sea A*x = b. para resolverlo tengo que sacar la
inversa en ambos lados para que se me cancelen y me quede la identidad
PROCEDIMIENTO:
A*x=b
A^(-1)*A*x=b*A^(-1)
Como la inversa por la original es la identidad, se cancelan y te queda:
X=b*A^(-1)

OPTIMIZACION
GOAL SEEK:

- “Goal Seek” es una herramienta relativamente simple de Excel que nos permite hacer este tipo de análisis.
Para poder usarla bien se deben tener que cumplir 3 condiciones:
o Debe haber un único objetivo numérico y este objetivo debe estar en una celda como fórmula.
o Hay sólo una variable a modificar.
o Existe una única solución válida y estable (Well-posed problem).
- Para usar Goal Seek hay que seguir los siguientes pasos:
o Abran la herramientas “Goal Seek” (ribbon -> data -> what-if analysis -> Goal Seek”).
o En “Set Cell” elijan la celda que quieren optimizar.
o En “To value” elijan el valor que quieren que tenga la celda anterior.
o En “By Changing Cell” elijan la celda que se modifica para optimizar y hagan click en ok.
- Adicional que usamos en casos de financiera
o PMT, indica a cuánto asciende la cuota mensual que uno debe pagar por un crédito
= PMT(interest_rate, number_payments, PV, [FV], [Type])
Los parámetros que vamos a usar son:
§ interest_rate: la tasa de interés de un periodo.
§ number_payments: la cantidad de pagos.
§ PV: valor del principal del préstamo.
§ FV: valor que queda después de finalizado el pago.
§ Type: si se paga al comienzo o final del periodo.
- EJEMPLO: préstamo de $5.000 a dos años con una tasa de interés efectiva anual de 7.5% pagará una cuota
mensual de:
o = PMT((1+7.5%)^(1/12)-1, 2*12, 5000)
§ la cuota mensual será de $224 por dos años.
o El (1/12)-1 lo ponemos porque tenemos la tasa anual y tenemos que ponerla por periodo de pago
o TASA DEL PERIODO MENSUAL= 1 + interés anual ^ (1/12)-1
- PMT TE DEVUELVE EL VALOR DE LA CUOTA

PAQUETE ESTADISTICO:

- Uno puede agregar funcionalidades a Excel a través de “Addins” , dos que suelen estar disponibles por
defecto cuando uno instala Excel son “Analysis ToolPack” y “Solver”.
o Uno puede acceder a “Data Analysis” haciendo click en el botón que aparece en la pestaña “Data”.
 § Una vez que hagan click, se abrirá una ventana en donde figurarán diferentes herramientas
que permiten realizar análisis estadístico de datos (histogramas, regresiones, cálculo de
matrices de varianza y covarianza, entre otras cosas).

CALCULO DE RENDIMIENTO Y VARIANZA DE UN PORTAFOLIO:

- Al rendimiento de un activo se lo puede modelar como una variable aleatoria. Cada activo tendrá un
rendimiento esperado y un riesgo (desviación estándar), de este modo el portafolio mismo tendrá también
un rendimiento y riesgo que vendrá dado por su composición. A su vez para medir la relación lineal entre los
rendimientos de los activos se pueden usar la covarianza de los rendimientos.
- Si 𝑤𝑖es la participación porcentual de un activo i en el portafolio, 𝑅𝑖 el rendimiento esperado del activo i, 𝜎𝑖
el riesgo del activo i, y 𝜌𝑖𝑗 la correlación entre los retornos de los activos.
- En términos matriciales, podemos calcular el riesgo y el rendimiento con los siguientes cálculos:

Esta es un sumproduct

Esto ya es un MMULT
- ¿Cuál es el chiste? Combinando activos uno puede alcanzar combinaciones de retorno/riesgo que no eran
alcanzables por los activos de manera individual. La frontera eficiente está dada por el máximo rendimiento
alcanzable dado un nivel de riesgo. Esto es un problema de maximización que eventualmente
solucionaremos en Excel.
- Para estos ejercicios nos va a ser útil saber multiplicar y transponer matrices en Excel. Para ello podemos
usar las funciones MMULT y TRANSPOSE.
o Recuerden que para que estas funciones anden bien, uno le tiene que “avisar” a Excel que las
trabaje con arrays.
§ Por ejemplo, el cálculo de la varianza del portafolio se podría hacer en Excel con la siguiente
combinación de funciones. {=MMULT(W, MMULT(COV,TRANSPOSE(W)))} En donde W es el
rango de celdas que tiene a las participaciones y COV es el rango de celdas que tiene las
covarianzas.

MODELADO
EJEMPLO MOCHILA:

- Nuestro objetivo es encontrar el subconjunto de productos a incluir que maximice el beneficio sin exceder la
capacidad (esto lo vimos con el ejemplo de la mochila)
- ¿Cómo estructurara estos problemas?
 o Variables del Modelo (x): Formalizan la decisión que debemos tomar (¿qué productos incluimos en
la mochila?)

o Función Objetivo F(x): Función que queremos optimizar. En este caso, la función sería el beneficio
total obtenido de incluir los productos en la mochila.

o Restricciones: Condiciones que las variables de decisión deben cumplir para garantizar que el
problema sea factible

EJEMPLO MOCHILAS MULTIPLES:

- Nuestro objetivo es encontrar el subconjunto de productos a incluir en cada mochila que maximice el
beneficio sin exceder la capacidad de las mismas.

EJEMPLO DISTRIBUCION DE PRODUCTOS:

- Nuestra empresa produce un determinado commodity (por ejemplo, crudo) en distintas plantas productoras
(P), que debe ser distribuido a distintos centros de almacenamiento (C).
o Cada planta posee un nivel de producción del commodity.
o Cada centro una demanda del commodity.
o Conocemos el costo de transporte por unidad entre cada par planta-centro, que puede variar por
diferentes razones.
o Asumimos que la producción total equipara a la demanda total.
o La demanda de un centro debe ser cubierta por una o más plantas.
 o Una planta puede abastecer a uno o más centros.
- Buscamos una planificación de distribución que minimice los costos de transporte

SOLVER:
- Nuestro objetivo es dado un rendimiento esperado elegir el portafolio de mínima varianza. Si esto lo
hacemos para distintos valores de rendimientos esperados, vamos a ir obteniendo la frontera de Markowitz
o ¿Qué necesitamos formalizar para modelar matemáticamente el problema de decisión?
§ Cómo representar un portafolio (variables de decisión).
§ Qué condiciones debe cumplir para ser un portafolio válido (restricciones de factibilidad)
§ Qué métrica se busca optimizar (función objetivo)
- Esto no lo podemos resolver con goal seek porque no cumple con las condiciones para usar ese add in.
Solver es un optimizador más sofisticado que goal seek, nos permite:
o Encontrar soluciones a sistemas de ecuaciones: lineales, no lineales
o Maximizar o minimizar funciones "complejas":
§ sin restricciones
§ con restricciones: igualdad, desigualdad, enteros, valores binarios, valores diferentes
- Solver no resuelve los problemas de manera simbólica o analítica, sino que utiliza algoritmos iterativos que
buscan acercarse a una solución. No existe un mejor algoritmo para todos los problemas, sino que hay que
analizar el problema, ver qué condiciones cumple y en base a ellos elegir un algoritmo a usar.
- Algunas cosas para mirar del problema:
o ¿Todas las funciones involucradas son lineales?
§ Si lo son usamos SIMPLEX como método. Si no lo son usamos GRG non linear. Después
tenemos el plan C que es EVOLUTIONARY
• En caos no cóncavos o convexos, solver va a fallar casi siempre entonces tenemos
que ver como operar estos casos à evolutionary o DG con inicios múltiples (muchos
X0) para ver si lleva al mínimo
o ¿La función a optimizar es cóncava (o convexa)?
o ¿Las funciones involucradas son diferenciables?
SOLVER PARA OPTIMIZAR:

- Tenemos que completar las ventanas de solver:

o Objetivo: tenemos que poner una celda que tenga una fórmula, para que solver la optimice.
o Definir que hacer con ese objetivo: max, min o igualar a un valor x
o Changing cells: acá tenemos que poner la o las celdas que el optimizador puede cambiar para llegar
al objetivo
o Restricciones: podemos poner restricciones para que la optimización se haga dentro de límites que
definimos
§ Pueden asignarse restricciones a las variables independientes y a la dependiente, para ello
hay que hacer click en “agregar”. Las mismas pueden ser de: igualdad (=), mayor igual(>=),
menor o igual(<=), que sean enteros (int), que sean binarias (bin) o que un grupo de
variables sean todas diferentes entre sí (dif).
o El check de variables sin restricciones: hace que todas las variables que no están en las restricciones
sean no negativas
o El método de optimización:
§ “GRG Non linear” es bueno para casos diferenciables no lineales.
§ “Simplex LP” es bueno para el caso de problemas lineales
§ “Evolutionary” (que es una heurística) es menos malo para casos combinatoriales
(pequeños) y/o no diferenciables.
- Cuando Solver termina de optimizar (haya encontrado o no una solución), devuelve una ventana en donde
detalla qué es lo que ocurrió y donde da opciones para tener reportes, volver al cuadro de parámetros o
guardar el resultado obtenido como un escenario.
- Para aminorar el riesgo de caer en óptimos locales tenemos 2 opciones disponibles en Solver:
o Usar el optimizador "GRG Non linear” con múltiples pruebas que comiencen en distintos valores
iniciales.
§ en este caso deben ir a opciones de “GRG Nonlinear” y activar la opción de inicios múltiples
o Utilizar el optimizador “Evolutionary”.
En ambos casos vamos a tener que imponer restricciones a los valores que pueden tomar las variables
independientes.

SOLVER PARA SISTEMAS DE ECUACIONES:

- Para resolver sistemas de ecuaciones, la forma más simple es dejar en blanco el campo “Establecer
Objetivo” y agregar las ecuaciones del modelo como restricciones. Solver buscará una valuación que cumpla
con todas las restricciones.

La versión de Solver que viene de fábrica tiene límites a la cantidad de variables independientes y restricciones
que se pueden agregar a los modelos (depende de qué modelo se use, igual son muchas las que se pueden
agregar!). No hay que creer que Solver es una herramienta que puede dar una solución a todo problema. Existen
Optimizadores más potentes que Solver, pero también existen problemas que ni estos optimizadores pueden
resolver (por ejemplo el problema del viajante con muchas ciudades). No se puede sustituir el conocimiento
matemático o el del dominio por una herramienta simple de optimización
MACROS
- Cuando uno trabaja con Excel es común tener que llevar adelante tareas repetitivas. Las macros nos sirven
para tratar de automatizar la tarea de alguna manera y hacer que la computadora haga el cálculo por
nosotros. Esto está muy relacionado con programar.
- Dentro de Excel se pueden escribir programas. Los programas que uno puede armar en Excel deben ser
escritos en un lenguaje puntual que se llama “Visual Basic for Applications” (VBA). Dado que VBA es un
lenguaje, vamos a tener que saber escribir en VBA y leer algo de VBA para poder programar bien en Excel
- ¿Cómo podemos escribir programas en VBA? Por lo menos hay dos formas, la difícil y la fácil:
o La difícil: escribirlos uno mismo como si se escribiese texto.
o La fácil: hacer que Excel transcriba en tiempo real lo que nosotros hacemos con teclado y mouse a
código de VBA.
Las dos formas tienen ventajas y desventajas, y lo mejor es usar una combinación de las dos.

GRABAR MACROS:

- Una macro es una colección de comandos (o sea un programa) que se pueden ejecutar con un simple click.
Pueden automatizar prácticamente todo lo que hacemos en Excel.
o ¿Armar un Macro implica programar? Claramente sí, pero para armarlas y usarlas no hace falta
saber escribir VBA (aunque ayuda mucho!).
- La herramienta que hace esto se llama “Macro Recorder” y nos permite grabar, administrar y correr macros.
o Las herramientas asociadas a Macros se encuentran en la pestaña “Developer” en el grupo “Code”.
A la grabadora de Macros se accede haciendo click en “Record Macro”
- Cuando uno ejecuta la grabadora de macros aparece la siguiente ventana, en donde uno puede completar
una serie de campos (no obligatorios):
o “Macro name” es el nombre que tendrá la serie de comandos guardados, tiene sentido ponerle un
nombre que ayude a entender qué hace la macro.
o “Shortcut key” define una combinación de teclas que podemos definir para correr la macro.
§ Ojo que si uno usa por ejemplo “Ctrl + c” ya no podremos usar más esa combinación para
copiar en el libro, sino que correrá la macro.
o “Store macro in” define en donde se va a guardar la “traducción” en código VBA de lo que hicimos.
§ Hay tres opciones: en un nuevo libro, en el libro actual o en el libro personal de macros.
o “Description” aquí podemos agregar una descripción completa de qué hace la macro y de cómo lo
hace.
- Apretando “Ok” Excel pasa a grabar todo lo que estemos haciendo en Excel. Esto incluye entre otras cosas:
o Qué celdas seleccionamos.
o Qué celdas copiamos y pegamos.
o Qué escribimos en una celda y cómo la formateamos.
o Qué gráficos armamos y cómo lo formateamos.
o Si corrimos Solver, con qué modelo y en donde guardó lo que se guarda
o Si usamos las herramientas de escenarios.
… y básicamente todo lo que vimos en el curso!
- PARA HACER UN EXCEL CON MACROS TENEMOS QUE GUARDARLO COMO XLSM
o Si haces macros y no lo guardas en esa extensión perdes todo
- PARA CORRER LA MACRO Y QUE DE BIEN, DEBEMOS TENER CIERTAS PRECAUCIONES
 o Si queremos que cuando corremos algo, lo haga en una celda en específico, tenemos que empezar a
grabar antes de ponernos en la celda que debe empezar a correr todo
§ Por ej acá si queremos que siempre lo haga empezando de A1 tenemos que grabar parados
en una celda cualq y como primera acción movernos a la celda A1

DETRÁS DE LAS MACROS:

- Hasta ahora sólo vimos cómo grabar macros, mencionamos que se guardan en algún lugar y que en el fondo
son código VBA de las acciones que ejecutamos mientras se grababa. Veamos mejor todo esto, la
herramienta “Macros” nos permite acceder a las mismas
o En estas herramientas aparece todas las macros que están en donde indica el campo “Macros in”.
§ Cuando uno elige una macro y aprieta “Run”, se ejecuta la macro
§ Cuando uno elige una macro y aprieta “Step Into”, se ejecuta la macro pero paso por paso
(para ejecutar la siguiente instrucción debemos apretar fn+f8). Esto es muy útil para limpiar
(debuggear) código.
§ Cuando uno elige una macro y aprieta “Edit”, se abre el editor de VBA que trae Excel y nos
muestra el código VBA de la macro
§ Cuando uno elige una macro y aprieta “Delete”, se borra la macro.
§ Cuando uno elige una macro y aprieta “Options”, aparecen opciones para modificar el
shortcut o descripción de la Macro
- Interpretemos esos cogidos que nos aparecen
o “Sub” es una palabra clave del lenguaje VBA que indica que va a comenzar una macro. Le sigue el
nombre que tendrá la macro (en este caso Macro1).
o “End Sub” indica a Excel que hasta allí llega el código de la macro
o Las líneas que comienzan con ‘ son comentarios.
§ Aparecen coloreadas en verde y VBA no las interpreta como órdenes sino como líneas que
sirven para que el programador entienda su programa. Por ejemplo: la descripción de la
macro que escriben cuando graban una macro, se copia aquí
o Lo que aparece en negro y más apartado del margen es el código propiamente dicho
- La grabadora de macros es una herramienta excelente, sin embargo, no reemplaza saber programar. Una de
las razones es que el código que escribe suele ser malo y lleno de redundancias. Otra razón, quizás más
importante, es que no permite grabar estructuras de control (tema que vamos a ver más adelante). Como
todo lo que hemos visto, es importante saber cuándo sirve y cuando no!

REFERENCIAS ABSOLUTAS Y RELATIVAS:

- Cuando uno graba una Macro, existe la opción de grabar con referencias absolutas o relativas. Es similar a lo
que vimos en fórmulas. Cuando uno trabaja con referencias relativas importa en donde estamos
posicionados cuando empezamos a correr la macro, si uno trabaja con referencias absolutas no importa. Al
igual que con fórmulas esto puede ser muy útil.
- Supongamos que estamos en la celda A1, y grabamos una Macro con referencias absolutas en donde lo
único que hacemos es elegir la celda B1.
o ¿Si seleccionamos la celda C3 y corremos la macro, qué pasa?
o COMO TIENE REFERENCIAS FIJAS TE DEVUELVE A B1
o El código le dice IR A b1 directamente
§ Range("B1").Select
- Ahora repitamos todo idéntico pero teniendo elegido referencias relativas.
o ¿Si seleccionamos la celda C3 y corremos la nueva macro, qué pasa?
o COMO TIENE REFERENCIAS RELATIVAS TE LLEVA A LA DE AL LADO DE DONDE ESTAS
o El código le indica cantidad filas y columnas que moverse desde donde esta, ahí va la relatividad
§ ActiveCell.Offset(0, 1).Range("A1").Select
• El sentido de desplazamiento natural de Excel es hacia abajo y a la derecha. como
acá no cambiamos fila dice 0 en mov vert y 1 en mov hori porque va a la derecha, si
fuese a la izquierda diría números negativos en las columnas
• ¡¡Offset va a ser clave!! y lo que dice es dada una celda activa, moverse i filas hacia
abajo (en este caso 0 filas) y j columnas (en este caso 1 columnas) hacia la derecha.
Valores negativos será moverse para arriba o para la izquierda.
- TENES QUE APRETAR O NO EL BOTON DE REFERNECIAS ANTES DE PONER A GRABAR LA MACRO

ESTRUCTURA Y EDITOR DE VBA:

- Noten que el explorador de proyectos tiene elementos con el nombre de los libros de Excel con que estamos
trabajando (en el caso de la imagen es “Book1”).
- Dentro de cada uno de esos objetos que representan los libros, se encuentran las hojas de cálculo del libro y
los módulos. Los módulos son en donde se guarda el código de VBA, es en donde la grabadora agrega las
macros y en donde debemos escribir nuestro código.
- Si no hay ningún módulo en el libro, se pueden agregar eligiendo “Insert” y luego “Module”.
o Esto lo tenemos que hacer cuando no hay ninguna macro guardada que estemos mirando en la
consola

OBJETOS EN VBA:

- Los objetos tienen propiedades, que son características de los objetos. Una tostadora tiene un color, un
motor tiene una cantidad de pistones, un libro de Excel tiene una determinada cantidad de hojas.
- También los objetos tienen métodos, son cosas que pueden hacer (se asocian con funciones). A una
tostadora uno le da energía y pan y le puede pedir que haga tostadas (otro objeto), a un auto uno le da
combustible y un conductor y le puede pedir que se desplace, a un libro de Excel uno le puede pedir que
solucione un modelo de solver
- A la familia que agrupa todas las variantes de un tipo de objetos se la llama “clase”
- VBA sabe entender la idea de que existen objetos e interpreta prácticamente todo lo que Excel tiene como
objetos (con sus propiedades y métodos) y pueden tener una jerarquía. Entre ellos genera una jerarquia:

- Hay muchísimos objetos en Excel, nosotros vamos a ver bien sólo un subgrupo (pero tengan en mente que
hay más):
o Selection: Son las celdas que tenemos seleccionadas, que podemos formatear, borrar, etc.
o ActiveCell: Es la celda sobre la que introduciríamos datos si escribiésemos algo, sea fórmula o texto
o Range: Es cualquier rango de celda (incluso una sola o celdas disjuntas), no tiene una representación
gráfica en Excel (en realidad todas las combinaciones de subconjuntos de celdas la grilla lo serían).
- Para acceder a las propiedades y métodos de los objetos se usa el “.”
o Range(“A1:A52”).Select
§ (Traducción: “Select” es método de “Range”, y hace que la nueva “Selection” sea ese rango
de celdas)
• El nuevo seelction va a ser el rango y lo que va a pasar es que se pinta el interior de
las celdas de A1 a A52
- Uno puede modificar las propiedades de los objetos.
o Por ejemplo “ActiveCell” tiene una propiedad que es su “Value” y uno con código le puede ingresar
al valor que quiera (Range también tiene esta propiedad), para ello se debe usar el “=“, el siguiente
código hace eso:
§ ActiveCell.Value = 3
- LA ONDA ES QUE SELECCIONAS EL OBJETO Y DSP PONES EL . PARA VER QUE VAS A HACER CON ESE OBJ
En este caso el operador "=" debe interpretarse como un operador que asigna un valor
- Hay muchos objetos y cada objeto tiene muchas propiedades y métodos. No hace falta saberlos todos, si
sabemos qué queremos hacer, podemos usar la grabadora de macros, ver cómo se hace lo que queremos
hacer, entender el código y así aprender.
- Excel también ofrece otra ayuda, cuando escribimos código a mano, Excel nos va dando tips sobre qué
métodos y propiedades tiene el objeto:
- EJEMPLO
¿Cómo se ven los objetos?
o Supongan que tienen el valor 3 en la celda A1, 4 en la A2 y 1 en la A3. Activen la grabadora de
macro, en la celda B1 escriban una fórmula que calcule el promedio de estos datos, apaguen la
grabadora de macros y traten de entender el código que obtuvieron.
§ DEBEMOS TENER EN CUENTA QUE NOS QUEREMOS PONER EN B1 à lo configure en macro
prueba promedio dentro de Excel 1NOV
La línea que obtuvieron que introduce la fórmula debiera ser:
o ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[2]C[-1])“
“FormulaR1C1” es una propiedad de la celda activa y es en donde dice qué formula tiene dentro una celda.
Algunas consideraciones sobre la sintaxis:
o Las letras “R” representa filas y las letras “C” columnas.
o “R[2]C[-1]” es la celda que está dos filas debajo y una columna a la izquierda.
o “RC[-1]” es lo mismo que “R[0]C[-1]”

SENTENCIA WITH:

- Nos sirve para hacer varias cosas al seleccionar un solo objeto y macro. Si miramos un ejemplo, podemos
hacer dos cosas con Selection.Interior:

VARIABLES EN VBA:

- Todo lenguaje de programación tiene lo que se llama “variables”. Son objetos temporales en donde uno
puede guardar información. En variables podemos guardar texto, números enteros, reales y prácticamente
cualquier objeto.
- Para usar una variable primero hay que crearla asignándole un nombre y un tipo, esto se hace escribiendo
algo de la forma: “Dim” + “Nombre elegido” [+ “As” + Tipo de Variable”] (lo que está en corchetes es
opcional).
o String es variable texto
o Integer es variable número entero
o Range es declararlo como rango
o Long es para números mas grandes que 32k, entonces si ponele tenes que hacer una variable
numérica que se va muy grandes usas eso directamente
- Las variables existen mientras se ejecuta la macro que la contiene, después se borra
- Para poder ver las variables, tenemos que ir a view dentro del visual basic y poner view local à ahí te
muestra la lista de variables que tenes dentro de ese código
o La diferencia es que en este caso no permanecen las variables ahí por todo el tiempo sino que solo
se ven mientras dura la vuelta del código

MACROS Y SOLVER
BOTÓN PARA MACROS:

- Otra forma de correr macros es asignar la macro a un botón que uno inserta en la hoja de cálculo, de modo
que cuando se aprieta el botón se ejecuta la macro. Para hacer esto debemos usar herramientas del grupo
“Controls” que tenemos dentro de solver
o Clickeando “insert” deben elegir el “botón” y con el mouse situarlo en donde quieran en la hoja de
cálculo.
§ Excel le pedirá que le asignen algún macro al mismo (si no lo hacen al insertar el botón, lo
pueden hacer luego haciendo clic derecho sobre el botón). Pueden asignarle algún texto
descriptivo al botón

SOLVER PARA UNA MACRO:

- Vamos a ver el caso de cómo la grabadora de macros trabaja con Solver. Nos va a servir porque es un caso
típico de cuando no obtenemos buenos resultados usando la grabadora.
o Tomen el archivo “Assets.xlsx”. Completen las fórmulas del rendimiento y riesgo del portafolio.
Minimicen el riesgo del portafolio con la restricción de un rendimiento de 43%. Atención! Al armar
el modelo de Solver y ejecutarlo, tengan activada la grabadora de macros.

o Las líneas que comienzan con “SolverOk” son las que definen qué celda se optimiza, cómo se
optimiza, con qué optimizador y cuáles son las celdas que se modificarán.
 § ¿Tiene sentido que esta línea esté más de una vez en el código? ¿Cuántas veces está?
Saquemos las veces que esté duplicada menos la última (que parece razonable que quede
antes de la línea SolverSolve).
o Las líneas que comienza con “SolverAdd” agregan las restricciones que tienen el modelo.
o Ejecutemos muchas veces la macro y abramos Solver de la manera tradicional. ¿Notan algo raro? La
macro estuvo agregando las restricciones cada vez que ejecutamos el código. Esto podría no ser tan
malo, pero lo cierto es que tenemos un límite a la cantidad de restricciones que podemos usar,
entonces no hay que desperdiciarlas.
§ Si agregamos al comienzo del código la línea “SolverReset” la macro antes de empezar a
configurar Solver, va a limpiar todo lo que tenían de antes.
o Hay algo un poco molesto, cada vez que ejecutamos la macro, nos aparece el cartel que dice
aceptemos el modelo que obtuvimos.
§ Para que no aparezca el cartel tenemos que escribir al lado de “SolverSolve” la opción
“True”.
- Código limpio:

- El ejemplo es bueno para confirmar que no siempre la grabadora de macro transcribe buen código, y que
siempre es mejor chequear qué es lo que hizo. A su vez, este código les va a servir como guía para otros
problemas que podemos hacer con VBA y Solver combinados.
- SIEMPRE QUE VAYAMOS A HACER MACROS TENEMOS QUE PONERLES CONDICION DE FINALIZACION – PARA
ESO NOS SIRVEN LAS ESTRUCTURAS DE CONTROL

FUNCIONES EN VBA:

Hay tres funciones que son útiles para interactuar con los programas que escribimos y para chequear que
estamos haciendo las cosas bien

- MSG BOX: es una función con un argumento que al ejecutarse hace aparecer una ventana que muestra lo
que uno le pasa como argumento. Es útil para ver qué está haciendo el programa y para mostrar a un
usuario resultados importantes.
 o Se usa para comunicar resultados, errores o demás cosas a quien use el Excel que armamos o a
nosotros mismos – vamos a usar concatenación de texto y demás más adelante para usar este
código
- DEBUG.PRINT: muestra los resultados en la ventana “Inmediate” del editor de VBA sin parar la ejecución del
programa.
o Detalle: una diferencia mínima de sintaxis es que para Debug.print no debemos usar paréntesis,
mientras que con MsgBox sí.

o Este código no nos sale en una cajita, sirve más como punto de control que nos va mostrando los
resultados en la ventana de inmediate que abrimos en view de la visual basic
§ No aparece en Excel normal sino que solo en inmediate y nos sirve cuando hacemos código
muy largo para ir viendo donde puede haber errores
- INPUT BOX: es diferente y lo que hace es pedirle al usuario que ejecuta la macro que ingrese un valor
mientras se ejecuta la macro. El único argumento que tiene es el mensaje que se le pasará al usuario,
indicando el valor esperado.

Típicamente guardaremos el valor ingresado en una variable, para utilizarlo después dentro de la macro

ESTRUCTURAS DE CONTROL
Las estructuras de control son bloques de código que nos permiten modificar el orden de ejecución de las
instrucciones en función de una expresión que define su comportamiento. En resumen:

- Una estructura de control nos permite modificar el orden en que se ejecuta el programa.
- Debe tener una expresión que defina su comportamiento, en el caso del “If” la llamamos guarda.
o Debe ser una expresión que devuelva “True” o “False”, o sea un valor Booleano. Es muy similar a lo
que vimos con el tipo de dato Booleano en Excel. En VBA las variables que toman valores de
verdadero o falso se llaman “Boolean” y pueden tomar el valor “True” o “False”. Para crear una
variable booleana hay que escribir “Dim Nombre As Boolean” Al igual que con fórmulas en Excel,
 existen operadores de comparación que permiten evaluar si algo es verdadero o falso. Los mismos
son: “=“, “<>”, “>”, “=“, “<=“
o Combinación de guardas: Para conectar dos condiciones se debe insertar “And” o “Or” en el medio
de ambas (las cuales conviene poner entre paréntesis). Podemos tb usar “Not” para armar una
guarda
- Debe tener indicado en dónde comienza, en el caso del “If” donde comienza es en donde dice “If”, y donde
termina es en donde dice “End If”.

CONDICIONALES

ESTRUCTURAS IF, ELSE Y ELSEIF:

- En el caso de “If”, el código que está dentro de un “If” se ejecuta si se cumple o no alguna condición (a la
que se la llama “guarda”). Que se cumple quiere decir que la condición sea “True”; si no es “True”, esa
porción de código no se ejecuta.
o Dónde comienza? donde dice “If” + la Guarda + “Then”.
o Dónde termina? donde dice “End If”.
EJEMPLO:

o Este código solo nos dice que hacer en caso de que el valor de la celda sea mayor a 100
- Asociado al “If” está “Else”, que dice que si no se cumple la guarda en vez de saltearse todo el código del
“If”, haga lo que está dentro del “Else”.
EJEMPLO

o En este caso le podemos pedir que haga algo cuando el valor el mayor a 1000 pero tb le indicamos
que debe hacer si el valor no cumple con esa condición y no entra al if
- Finalmente también existe “ElseIf” que hace que se evalúe una guarda adicional (se pueden agregar tantos
“ElseIf” como uno quiera).
EJEMPLO
 o Con elseif podemos pedirle que haga algo si mayor a mil, con el elseif que haga otra cosa si es mayor
a 500 y una tercera cosa con else en caso de que no entre al elseif
- Las estructuras de control “If”, “Else” y “ElseIf” son similares a la función “If” que vimos en funciones lógicas
en Excel. Nada impide que dentro de un “If”, “Else” o “ElseIf” haya otra estructura del tipo “If”, “Else” y
“ElseIf”. Tip: piénsenlo como árboles de decisión (igual que lo que vimos con funciones if antes)

CICLOS

LOOPS DE TIPO WHILE:

- Para poder repetir acciones muchas veces siguiendo algún criterio existen estructuras de control
especializadas. Repasemos dos operaciones importantes:
o Reasignación. Podemos modificar el valor de una variable utilizando nuevamente el operador
asignación
o Actualización: es un caso particular de reasignación
§ X=10
§ X=x+1
- Al ser una estructura de control debemos determinar en dónde comienza (en donde dice “While”) y en
dónde termina (en donde dice “Wend”)
 o Se puede tener otras estructuras de control dentro del while.
- Podemos definir una variable que empiece en 0, en cada iteración se vaya actualizando, sumando 1, y una
guarda tal que si la variable es mayor o igual a 10 retorne falso.

- Los loop que usan “While” son muy útiles, pero también son en cierto modo “peligrosos”, si uno pone una
condición que siempre se cumpla el programa quedará corriendo para “siempre”, a este tipo de loops se los
suele llamar “Loops Infinitos”.
o Si entras en un loop infinito se traba Excel y no termina de ejecutar nunca

LOOPS DE TIPO FOR:

- Los loops del tipo “For” tienen una variable de iteración, generalmente un número entero, que se mueve en
un rango de valores [inicio, fin] y podemos ir modificando de a un cierto paso.
- De manera similar a las otras estructuras, los ciclos “For” deben ser terminados con la palabra reservada
“Next”
- El código que se repite es el que está entre la línea que dice “For” y la línea que dice “Next”.
- En el caso de los ciclos for no tenemos que declarar las variables por fuera del ciclo como en while. Acá
derecho poner i=a to b step 1 e interpreta que cada vez que da vuelta al ciclo i vale uno más que antes
- Dentro de un loop uno puede tener cualquier tipo de código, ya sea otras estructuras de control, órdenes
para que solver resuelva algo, llamadas a otras macros o lo que fuera.
- Se pueden “anidar” tantos loops como uno quiera. Sin embargo, muchos loops anidados puede volver a la
macro costosa de correr

Loop for para matrices:

- El objeto “Range” tiene una propiedad que se llama “Item”, que nos permite acceder o escribir en el
elemento que tiene el número que se le pasa como parámetro a ítem.
o Por ejemplo, si seleccionamos Range(“A1:A5”) y ponemos Range(“A1:A5”).Item(2) = 3 esto escribe
en el segundo ítem de ese rango el valor 3 (que sería la celda A2).
- A su vez uno puede obtener el número de filas y columnas de un Rango con las siguientes instrucciones:
Range("A1:B5").Rows.Count Range("A1:B5").Columns.Count
o Esto nos sirve para completar matrices – como condición de guarda del for, para limitar el número
de iteraciones