.:::MBExcel:::.

Filtros Avanzados

2020-03-23T15:38:00.002-07:00

Después de mucho tiempo sin ver el potencial de los filtros avanzados, de repente una clase inesperada acerca del tema me clarificó las ideas para mejorar ciertas actividades en mi sitio de trabajo; por lo que decidí hacer esta nota con las aplicaciones que a mi juicio resultan más interesantes y útilies de esta opción.

Es preciso aclarar que los más incautos, dentro de los que me incluyo, podrian pensar que con los filtros normales sobre una tabla se podría conseguir el mismo efecto, pues la respuesta es que no con las mismas especificaciones, aunque advierto que es una buena alternativa. En fin, vamos a enfocarnos en un modelo de tabla para analizar el recaudo por diferentes conceptos. Véase la siguiente imagen:

Para empezar, vamos a filtrar los valores > 15000 y disponerlos en otro lugar de la hoja como valores.

Accedemos a la pestaña datos, avanzadas:

Inmediatamente se despliega el formulario con los siguientes criterios:

Rango de la lista: En este caso corresponde a la la tabla que contiene toda la información.
Rango de criterios: En este caso hace referencia al rango de celdas de H2:H3 que debe contener el nombre del encabezado (tal cual) del campo que quiero que se filtre, además del criterio que para este ejemplo es > 15000.
Copiar a: indica el rango donde quiero que se copien los datos filtrados como valores. Para que se active debemos elegir la opción de copiar a otro lugar. Finalmente damos click en aceptar.

Datos filtrados:

Un ejercicio interesante sería filtrar los conceptos vendidos entre dos fechas:

2011-02-24 y 2012-06-06

Para este menester se duplica el criterio fecha acompañado con los operadores relacionales >= (mayor o igual que) y <= (menor o igual que).

El filtro se observa a continuación:

Regresión lineal múltiple

2017-12-29T16:37:00.002-08:00

Hace un par de meses alguien vino a mi con la siguiente consulta:

¿Cómo puedo obtener una correlación lineal múltiple que permita estudiar la relación entre variables microbiológicas (independientes) y las perdidas de sacarosa en algunos materiales fabriles?

Naturalmente, este tema es muy específico de la industria azucarera, por lo que vamos a usar un ejemplo más común.

El ejemplo consiste en explicar la variabilidad de la evaporación del agua mediante la humedad relativa y la radiación solar.

Partiendo del concepto de multiplicidad; podemos afirmar que en este caso la humedad relativa y la radiación solar (variables independientes) influyen sobre la evaporación (variable dependiente).

Para ver fundamentos estadísticos acerca del tema, véase el siguiente enlace:

http://eio.usc.es/eipc1/BASE/BASEMASTER/FORMULARIOS-PHP-DPTO/MATERIALES/Mat_50140128_RegresionMultiple.pdf

A continuación se observan los datos del ejercicio:

Para realizar el ejercicio nos vamos a valer de la opción validación de datos de la pestaña Datos.

Si no se tiene activada, habrá que ir a la pestaña Archivo, Opciones, Complementos, Administrar; de la lista desplegable elegir complementos de Excel; y finalmente dar click en el botón Ir... y activar la casilla Herramientas para análisis.

Entrando en materia, activamos la opción Análisis de Datos, y elegimos la opción Regresión de la que aparece un formulario como el que se muestra en la siguiente imagen:

En el Rango Y de entrada elegimos los datos de la variable dependiente que en nuestro caso corresponde a la evaporación; incluyendo el título. En el Rango X de entrada se eligen las dos columnas que contienen la información de Humedad Relativa y Radiación Solar respectivamente. Dentro de las posibilidades que ofrece el formulario tenemos casillas de opción para generar varios gráficos de ínteres estadístico.

Finalmente podemos generar el análisis en un lugar de la hoja donde se encuentran los datos o en una hoja nueva o hasta incluso en libro nuevo.

A continuación se presenta el análisis estadístico que genera Excel:

De las estadísticas de regresión, se puede concluir que el 75 % de la variación de la evaporación, son explicadas por la variación del % de Humedad Relativa y de la Radiación solar.

Por último, se obtiene la ecuación de regresión que permitiría predecir valores de evaporación a partir de valores de humedad y radiación solar.

Macro para copiar columna

2017-03-29T09:04:00.004-07:00

Descargar el ejemplo

Usualmente en Excel no se trabaja en términos de columnas. La mayoría de las plantilllas se elaboran pensando en ampliar el rango de datos en términos de filas. Comparto decididamente esta premisa, especialmente porque una hoja tiene más filas que columnas, exactamante 1048576 filas * 16384 columnas (Office 2013). Pero cuando es inevitable ampliar nuestro número de columnas, nada mejor que hacerlo automáticamente mediante un código de VBA.

En el presente ejemplo se pretende copiar la última columna con datos y pegarla en una columna subsiguiente para ir ampliando el rango paulatinamente. En el modelo de datos que se presenta, se copió la columna J y se pegó en la columna K.

Quiero agradecer al experto "GREGORI00001" de todoexpertos, quien desinteresadamente me dio una mano para terminar el código que implemente en otra plantilla y que da vida a la presente nota.

Aquí el modelo de datos:

El código es el siguiente:

Sub copiar()

Dim Col As Integer

Col = ActiveSheet.Range("XFD1").End(xlToLeft).Column

ActiveCell.Offset(0, -1).Select

Selection.EntireColumn.Insert

ActiveCell.Offset(0, -1).Select

ActiveCell.EntireColumn.Select

Selection.Copy

ActiveCell.Offset(0, 1).Select

ActiveSheet.Paste

Application.CutCopyMode = False

ActiveCell.Offset(2, 0).Select

End Sub

Explicando paso a paso:

1. Col = ActiveSheet.Range("XFD1").End(xlToLeft).Column, recorre la fila uno desde la última columna hasta la primera columna donde encuentre dato.

2. ActiveCell.Offset(0, -1).Select, se posiciona una columna hacia la izquierda respecto de la primera columna con dato que encuentra.

3. Selection.EntireColumn.Insert, Inserta una columna en columna activa.

4. ActiveCell.Offset(0, -1).Select, se posiciona una columna hacia la izquierda respecto de la columna insertada, que es la columna con datos que requiero copiar.

5. ActiveCell.EntireColumn.Select

Selection.Copy

Selecciona y copia la columna activa.

6. ActiveCell.Offset(0, 1).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Se posiciona una columna hacia la derecha respecto de la posición de la columna copiada y, deshabilita el modo copiar.

7. ActiveCell.Offset(2, 0).Select

Finalmente, se posiciona dos filas debajo de la fila activa.

Buscar en dos direcciones combinando las funciones Coincidir, Dirección e Indirecto

2017-01-14T10:11:00.000-08:00

Descargar el ejemplo xls.

Ya había escrito una nota referente a este tema, usando las funciones Coincidir e Indice. Accidentalmente vuelvo a retomarlo por causa de una necesidad que vi en una plantilla. Se trataba de una referencia de celda que cambiaba al insertar una columna; por lo que la referencia quedaba desactualizada cada vez que se ejecutaba la mencionada acción, solo respecto de la columna, puesto que la referencia de fila era constante.

En la presente nota, se tiene una base datos con las ventas por días en siete sedes, lo que se pretende es consultar las ventas de cualquier día, en cualquiera de las siete sedes.

Para tal fin, se implementaron dos listas validadas (opción validación de datos-pestaña Datos) con las sedes y los días respectivamente.

Lista de sedes:

Lista de días: esto desde la hoja "ventas". También se puede hacer creando un nombre para el rango de datos. Véase la nota: asignar nombres a celdas y rangos.

Pasando a la consulta que quiero ilustrar, debo mencionar que el truco consiste en usar la función dirección para establecer la referencia de celda correspondiente; para finalmente consultar el valor con la función Indirecto. Una de las ventajas que encuentro de esta forma de trabajar, es que no se hace referencia a una matriz de datos; lo que le confiere mayor flexibilidad a la búsqueda, ya que no se requiere actualizar la formulación si se ingresan nuevos datos.

Lo primero que debo hacer es usar la función coincidir para traducir cada valor de las listas sede y día en una referencia de posición. En otras palabras, lo que busco es construir una dirección en términos de fila y columna en donde se encuentra el dato consultado.

He usado las celdas B2 y D2 concatenadas como argumento de búsqueda, dado que en la hoja "ventas" los nombres de las sedes estan así: sede1, sede2, sede3, etcétera.

Ahora hago lo mismo con los días:

Ahora que ya tenemos nuestra referencia de fila y de columna, vamos a usar la función Dirección para que la presente en forma dirección de celda:

El resultado es:

La celda F27 de la hoja "ventas".

Para comprender un poco más el funcionamiento de "Dirección", veamos los argumentos:

=DIRECCION(fila; columna; [abs]; [a1]; [hoja])

fila: número de fila.
Columna: número de columna.
abs: es opcional y puede ser un número entre 1 y 4 que indica el tipo de referencia absoluta que se aplicará (1 = ref. absoluta en fila y col., 2 = ref. absoluta solo en fila, 3 = ref. absoluta solo en col. y 4 ref. relativa en fila y col.). Si no se especifica nada, dejará la referencia absoluta en fila y columna, como en nuestro caso.
a1: especifica el estilo de referencia a presentar, A1 o F1C1.
hoja: es el nombre de la hoja de donde se obtiene la referencia, si se omite, se extraerá de la hoja activa. En nuestro caso especificamos el nombre de la hoja "ventas".

Visto lo anterior, solo resta hacer nuestra consulta mediante la función Indirecto; ya había escrito una nota acerca de esta función: hacer consultas con la función indirecto.

Comprobamos que para la sede 4, el día 26; las ventas fueron de 5521 unidades.

Estimación.Lineal

2016-10-29T10:53:00.001-07:00

Hola Amigos de MBExcel.

Después de una larga temporada sin escribir, me aventuré, más que a escribir, a explicar mediante un video ¿Cómo obtener los estadígrafos básicos de una regresión lineal usando la función Estimación.Lineal?

Debo mencionar que esta intención nace de varias consultas que he recibido acerca del tema:

Anónimo17 de octubre de 2016, 09:17

Buenos días, me pueden explicar como obtener la tabla con los resultados de la estimación lineal, ya que cuando hago este cálculo solo me aparece como resultado el valor de la pendiente en una celda, pero no la tabla completa con todos los resultados como se ve en el ejemplo.
Gracias

Responder

Buenas tardes:
Sigo con el mismo problema al tratar de obtener la estimación lineal. Yo tengo Excel 2010 y cuando entro la matrix de 2 columnas y 21 filas que se corresponde con tres repeticiones de la curva de calibración de siete puntos, en lugar de obtener la tabla completa con todos los estadígrafos obtengo solamente la pendiente del gráfico en una sola celda. Me podrían ayudar por favor,

Gracias

Multiplicar horas y minutos por enteros

2016-04-30T05:29:00.001-07:00

Una mañana cualquiera y cansado del mismo requerimiento, decido formular mediante MS Excel una simple pero útil solución.

Requería saber el tiempo en horas y minutos que estuvo por fuera de funcionamiento un conjunto de equipos; con lo que surgía la necesidad de multiplicar las horas y/o minutos de inactividad por el número de equipos. Para resolver tal necesidad no bastaba con ejecutar una sencilla operación matemática, por lo que hubo que valerse del formato personalizado "[h]:mm"; ya que las horas y minutos en Excel corresponden a un número en el que la parte decimal corresponde a la parte proporcional de un día; así, por ejemplo 0.5 corresponde a las 12:00 PM, 0.8 corresponde a las 19:12 PM y 1 corresponde a las 24:00 horas. De acuerdo con lo mencionado y, acudiendo a mi afición por resolverlo todo mediante Excel, surge lo siguiente:

Podemos insertar una tabla (uno de mis favoritos) y sumar mediante la fila de totales.

La columna D lleva el formato mencionado: "[h]:mm".

Por último, podemos sumar la cantidad de horas y minutos en la tabla, usando la opción totales. Puede verse que existen múltiples opciones; tales como: promedio, cuenta, máximo, mínimo, suma, desviación estándar, varianza, entre otras.

Buscar valor mediante macro

2015-07-18T10:17:00.003-07:00

Descargar el ejemplo

El título de esta nota parece un tanto inusual teniendo en cuenta que existe una función para buscar de manera vertical, en una o dos dimensiones, sin tener que acudir al "tortuoso" pero fascinante escenario de las macros.

Un amigo, ávido de implementar mejoras ofimáticas en el supermercado donde labora, me pregunta lo siguiente:

Tengo un sensor de código de barras que transmite el código de cada producto a una hoja de cálculo ¿cómo puedo hacer que Excel busque ese valor en una columna determinada, y que cuando encuentre ese valor, se situe en una celda contigua al valor encontrado?

La respuesta fue, un código de Visual Basic.

Cómo no soy experto en el tema, tome en cuenta varias referencias; entre ellas el material notable de la experta Elsa Matilde.

El requerimiento supone que el código detectado siempre quedará incluido en la celda G2; este sera el valor buscado en la columna D. Cuando se encuentre el valor , la celda activa será la celda contigua al valor encontrado; es decir, la celda correspondiente a la columna E.

Aquí el código que será incluido en un módulo:

Para terminar, se puede asignar la macro a un botón de formulario para facilitar la ejecución de la macro.

Carta de Control de Rangos

2015-02-01T10:50:00.000-08:00

En esta nota les presento un video acerca de cómo elaborar una carta de control de rangos de manera rápida y sencilla.

Algunas características de esta herramienta de control estadístico en el ámbito del aseguramiento de la calidad analítica en química:

Se trabaja con análisis de muestra por duplicado.

Se alimentan con resultados de análisis de varios días.

Para muestras que no son estables en el tiempo (muestras naturales).

Evalúa la variabilidad del método.

Para mayor información de este tema, puede ver la presentación en Prezi :

https://prezi.com/2ute8cnerfwg/asegur/

Factores que se usan para su construcción:

(n) Corresponde al número de lecturas.

Copiar Hoja Mediante Código VBA

2015-01-29T10:39:00.000-08:00

Después de una larga parada vuelvo a escribir una nota. En esta ocasión quiero exponer una de las formas de resolver una necesidad típica cuando se usa a Excel para generar plantillas de trabajo; y es, copiar una hoja ya diligenciada y borrar la información que no se requiere para digitar nuevos datos.

Tal necesidad se me presentó en mi lugar de trabajo ¿cómo la solucioné?

Pues use un código de BVA advirtiendo que no soy un experto en el tema. Aquí las líneas:

Sub nueva_hoja()

Application.ScreenUpdating = False "Evita el parpadeo de la pantalla cuando se ejecuta la macro"

ActiveSheet.Copy After:=Sheets(Sheets.Count) "duplica la hoja activa y la deja al final"

ActiveSheet.Select "selecciona la hoja activa"

Range("D14") = Sheets.Count "cuenta el número de hojas y pone el valor en la celda D14"

ActiveSheet.Name = Range("D14") "Nombra la nueva hoja con el valor de la celda D14"

Range("D16:H35").ClearContents "limpia el contenido del rango de celdas D16:H35"

Application.ScreenUpdating = True "activa la actualización de pantalla"

End Sub

Estas se deben copiar en un módulo. Atajo para acceder al entorno de Visual Basic (VBA), teclas Alt y F11.

Para la ejecución de la macro se puede instalar un botón de formulario.

Se dibuja el control en la hoja y con clic derecho se asigna la macro referida anteriormente.

Cuando se copia la hoja, esta toma como nombre el número de hojas del libro.

Herramienta Subtotal

2014-08-18T11:11:00.000-07:00

¡Hola amigos de MBExcel!

Hoy presento una aplicación básica de la opción Subtotal, opción que aparece dentro de la pestaña "Datos". Veamos la información que nos provee Excel acerca del tema:

Esta permite agrupar o resumir parte de la información de un rango de datos, definiendo el campo (una columna) en el que Excel debe detectar el cambio y la función a realizar (suma, cuenta, promedio, etc). Por último, se debe elegir el campo al que se le agregará el subtotal. Lo que se ha descrito anteriormente, se puede observar en el siguiente formulario:

Modelo de datos.

A partir del valor elegido en el argumento "Para cada cambio en:", Excel realizará un análisis buscando las filas en las que hay un cambio, en nuestro caso, de los diferentes artículos de la columna que denominamos "Artículo". Cuando encuentre un cambio, automáticamente se insertará una fila en la que se hará el cálculo de acuerdo con la función que hayamos definido en el argumento "Usar función:".

A continuación se explican las opciones de la parte inferior del formulario:

Se puede usar para realizar un segundo resumen a partir de uno ya realizado.

Se utiliza para imprimir cada sub resumen de datos en una hoja.

Si no se selecciona, mostrará el resumen de cada grupo en la parte superior de este.

La opción quitar todos, deja el rango de datos en las condiciones iniciales.

Después de elegir cambio en el campo artículo, función sumar; y agregar subtotal al campo cantidad, este es el resultado:

Buscar Objetivo

2014-06-22T10:11:00.003-07:00

Hola amigos de MBExcel. Hoy tengo el agrado de presentar una nota muy interesante acerca de la opción Buscar Objetivo.

Ruta: pestaña Datos, Opción Análisis Y si?, Buscar objetivo.

¿Qué se puede lograr con esta?

"Busca el objetivo de un modelo, no sólo de una ecuación, porque resuelve la hoja de cálculo variando el valor de una celda, hasta conseguir que otra celda adopte un valor que nosotros fijamos. Entre medias de estas celdas se pueden realizar todas las operaciones que queramos, desde una simple función hasta el modelo de cálculo más sofisticado que puedas imaginar, recordando que resuelve el sistema en función de una única variable". (fuente: Ya sé Excel pero necesito más).

¡Un poco confuso no!

En fin, lo que se pretende es resolver un modelo que contenga una sola incógnita, no sin antes definir una de las variables y el modelo. Como ejemplo de modelo podemos mencionar una operación sencilla (multiplicación, división, etc) o una ecuación.

Para dar mayor claridad a este tema, presento el siguiente ejercicio:

Tenemos la siguiente ecuación:

Para no tener que acudir a encontrar las raíces para despejar el valor de (x), dado un valor de y (Véase artículo acerca de la ecuación de segundo grado), acudiremos a la opción Buscar Objetivo.

Formulamos la ecuación en una celda (J33).

Notemos que la formulación hace alusión a la celda J31, aunque no haya valor definido en ella. Ello tiene mucho sentido, pues es en esta donde la opción presentará el resultado de la iteración.

2. Acudimos a la opción Buscar objetivo.

Es preciso mencionar que el ítem "Con el valor" se debe teclear directamente en el formulario.

Por último, se muestra el resultado.

El resultado de la operación es 35.536624

En otras palabras, la opción hizo muchas iteraciones y encontró que el valor más cercano a 1.0754 (valor objetivo), es decir 1.075307559 (Valor actual) definido como (y), hace que el valor de (x) resultante sea 35.536624

Estimar la cuota de un crédito

2014-03-24T18:59:00.000-07:00

Parece que el tema de las matemáticas financieras me atrapó un tanto. Quizá tenga que ver con la gran utilidad que puede tener en la vida cotidiana, especialmente si afrontamos el reto de endeudarnos por necesidad o por cualquier otra razón. En fin, en la presente nota se pretende estimar la cuota de una anualidad vencida.

Los datos necesarios para realizar el ejercicio:

Como tenemos una tasa efectiva anual, debemos convertirla en una tasa de interés periódica, aquí la fórmula para hacerlo:

Nota: No existe una función en Excel que permita estimar directamente una tasa de interés periódica mensual a partir de una tasa efectiva anual, pero si su equivalente anual.

De acuerdo con lo anterior, podemos ejecutar un pequeño truco, para lo cual usaremos la función Tasa.Nominal.

Como esta función estima la tasa nominal anual dependiendo del número de periódos, lo que haremos es indicar la tasa efectiva anual (9,51 %), el número de periódos (12), es decir un año; y por último dividimos entre 12 periódos para establecer la tasa periódica mensual.

Obtenemos el mismo resultado si usamos la formulación propuesta anteriormente.

Por último usaremos la función Pago() para establecer la cuota vencida.

Como mencionamos en la explicación de la función, omitimos los argumentos Vf y Tipo, dado que el valor futuro esperado es cero, es decir que esperamos que despues del periódo 24 el saldo sea cero. De otro lado, la variable Tipo se omite para indicar el pago al final del periódo, es decir pago vencido.

Aquí el resultado:

Referencia en tres dimensiones

2014-03-08T07:18:00.001-08:00

Descargar el archivo

A continuación presento mi segundo video realizado con el software Camtasia, que de paso aprovecho para destacar. Creo que se trata de una gran opción para explicar un tema tan práctico como el uso de MS Excel.

El presente video (sin relato por ahora) muestra la forma de consolidar en una hoja, la información de tres hojas de un libro, haciendo referencia a un mismo rango. Ello aplica para situaciones en las que se tenga un registros de años o de meses por hoja, que requiera ser resumido en una hoja "resumen" o "consolidado". En fin, no parece ser tan claro con palabras; por lo que espero que el video sea más explícito...

La formulación que se ve en la imagen es la siguiente:

=SUMA('2011:2013'!A$2:A$17)

'2011:2013' indica el rango de hojas del que se hará la consolidación.

!A$2:A$17 indica el rango de celdas que consolidará de cada hoja.

Opción Tabla

2014-01-07T08:35:00.001-08:00

En notas anteriores hemos analizado la bondad de esta opción; como en el caso de la inserción de tablas dinámicas, en el que la opción tabla juega un papel relevante para mantener el rango de dinámico de datos.

En la presente nota se pretente exponer más de los atributos que presenta la mencionada opción. Hacen parte de estos los siguientes:

Facil ampliación del rango mediante la tecla tab.
Posibilidad de incluir fila de totales, que permite usar diferentes funciones (suma, promedio, cuenta, max, min, entre otras).
Permite hacer referencia a cualquier campo de la tabla en formulaciones dentro y fuera de la tabla.

Como se explicó en notas anteriores, para hacer uso de esta opción basta con señalar el rango de datos, ir a la pestaña insertar, y elegir el ícono Tabla.

Modelo de datos:

Para ampliar el rango existen dos posibilidades. La primera es incluir filas o columnas dentro del rango de la tabla, la segunda es ir a la última fila y oprimir la tecla Tab, con lo que automáticamente se añadirá una fila nueva.

Fila de totales al final del rango para totalizar de acuerdo con las funciones: suma, cuenta, máximo, mínimo, desviación estandar, varianza, entre otras.

Para activarla solo se requiere situarse en cualquier celda de la tabla, automáticamente se activará una pestaña adicional denominada "Diseño", de la que se desprende la opción "Herramientas de Tabla"; y haciendo click en esta, se puede activar la opción "Fila de Totales".

Referencias de campos de la tabla dentro y fuera del rango:

Si se desea hacer operaciones directamente con los campos, como por ejemplo: unir texto de dos columnas, es preciso abrir la formulación con corchete, y automáticamente Excel sugerirá los nombres de los campos así:

Para usar una función fuera del rango de la tabla, por ejemplo para establecer el promedio del campo sueldo:

Cuando se crea la tabla, Excel le asigna el nombre "Tabla1". Se puede modificar si así lo deseamos.

También podemos hacer alusión a la tabla cuando se usa por ejemplo la función BuscarV:

Por último, si deseamos replicar los nombres de los encabezados de la tabla en otra posición de la hoja, se requiere utilizar una formulación matricial, así:

Se señala el rango objetivo, se escribe la formulación =Tabla1[#Encabezados], y se termina con la combinación Control+ Shift+Enter para darle la configuración matricial.

Una última consideración es que si queremos crear una lista desplegable a partir de la información de uno de los campos de la tabla, se requiere crear el nombre señalando sólo los datos del campo, es decir, sin el encabezado. Lo que se consigue es crear un rango dinámico de manera sencilla; con lo que al añadir un nuevo registro, este quedará incluido en la lista desplegable.

Estimar La Tasa Interna de Retorno

2013-12-26T14:08:00.002-08:00

Para iniciar con esta nota, debo aclarar que no soy un experto en temas financieros y que mi motivación para escribirla obedece al fruto de haber visto un curso relacionado con estos.

Entonces ¿a qué hace referencia el concepto de Tasa Interna de Retorno (TIR)??

Es una herramienta que facilita la toma de decisiones en torno a factibilidad de varias inversiones.
Es la tasa de interes por la cual se recupera una inversión.
Es la tasa de interes máxima a la que se puede endeudar para no perder. (Manzano Duran, Omaira (2010). Módulo del curso de matemáticas financieras para proyectos).
Es preciso compararla con la Tasa Interna de Oportunidad (TIO); que no es más que la tasa de rendimiento del dinero cuando se guarda en un banco mediante un CDT por ejemplo. Entonces, si la TIR > TIO, el negocio o proyecto es rentable.

Ahora, despues de conceptualizar, vamos a estimar mediante MS Excel, no sin antes mencionar que su estimación a mano requiere de interpolación, lo que lo hace un tanto dispendioso. MS Excel contiene una función predeterminada para tal fin:

Valores: Corresponde a la inversión y las utilidades que deberá presentar el proyecto o inversión (flujo de caja). Se debe disponer primero la inversión o pago con signo negativo, y despues las utilidades anuales, todo referido al valor presente, es decir al momento del análisis.

Estimar: como esta función realiza una iteración, mediante este argumento se le puede indicar que empiece a iterar desde una tasa específica. Si se omite, iniciará desde cero.

¿Cuál sera la TIR para el siguiente flujo de caja de un proyecto?

Según la imagen anterior se requiere de una inversión inicial de $50.000.000, y ello presenta unas utilidades anuales de $20.000.000, hasta el quinto año que es cuando se decide vender o ceder el proyecto por un valor de $35.000.000.

Teniendo en cuenta lo anterior, disponemos los valores de la siguiente manera:

Y, finalmente aplicamos la función TIR.

Nuestra TIR de retorno es del 37.10 %, es decir que el dinero invertido tiene un retorno, y este es del 37,10 % efectivo anual. Si existe una entidad u otro negocio que me ofrezca una TIO mayor que esta, el negocio no sera tan atractivo, por lo que habrá que compararlo con las posibilidades de inversión que se tenga.

BuscarV para buscar en una y dos dimensiones

2013-09-08T10:35:00.001-07:00

Descargar el ejemplo

Como muchos saben, la función BuscarV realiza búsqueda en grandes bases de datos, usando una clave o valor buscado. En una nota anterior se trató una variante de esta función: BuscarV para buscar entre rangos. Ahora buscaremos explicar su funcionamiento básico y otra variante: Buscar en dos dimensiones. Recordemos que esto es posible principalmente con la función Indice, de la que ya se escribió una nota: Consultas en dos dimensiones con la función Indice.

Recordemos la sintaxis de la función:

Donde:

Valor buscado: es el valor buscado o clave de la matriz.

Matriz de comparación: rango de datos en donde se hace la búsqueda.

Indicador columnas: es un número que indica la columna de la matriz de donde se devuelve un valor.

Ordenado: es un valor lógico (VERDADERO o FALSO) que devuelve la coincidencia más cercana en la primera columna. VERDADERO, para buscar el valor más cercano en la primera columna, ordenada de manera ascendente. FALSO, para encontrar la coincidencia exacta.

Ahora, presento la tabla de posiciones de las eliminatorias suramericanas al mundial 2014 actualizada hasta el día tres de septiembre de 2013 (Fuente: Futbolred).

Para hacer una consulta en una dimensión con esta función se debe hacer lo siguiente:

1. Generar una lista validada con los títulos de los paises. Véase la nota celda validada dependiente.

2.Consultar con la función BuscarV cualquier dato estadístico de alguna de las selecciones a partir de la celda antes validada.

Para realizar la consulta se debe formular en la celda D5 lo siguiente:

De lo anterior se infiere que B5 corresponde con la celda validada que contiene los nombres de los paises, es decir, el valor a consultar en la matriz de datos.

El rango $F$3:$P$11 es la matriz en donde la función consultará el valor seleccionado en la celda B5 .

El valor 2 (indicador_columnas) hace referencia a la segunda columna de la matriz $F$3:$P$11, es decir la columna cuyo título es Puntos. por lo que al consultar por Colombia, el resultado será 23.

Nótese que si quisiera consultar otra estadística tendría que modificar la fórmula para incluir el número de columna correspondiente; por lo que es aquí cuando aparece una variante para hacer posible consultas en dos dimensiones con la función en cuestión.

El truco consiste en adicionarle a la formulación anterior, en lugar del número de columna, la función COINCIDIR() que hace el trabajo de cambiar la dirección de columna a partir del valor seleccionado en una lista validada que contenga los rótulos de las columnas.

Teniendo la segunda lista validada, sólo basta con modificar la formulación de la celda D5, así:

La formulación COINCIDIR(B3;$F$2:$P$2;0) busca el valor en la celda B3 (Celda validada con los rótulos de los datos estadísticos) en la matriz $F$2:$P$2, y el cero indica que se buscará la coincidencia exacta.

Entonces, si consultamos a Colombia y el número de goles a favor, deberíamos obtener 21. Descarga el ejemplo en archivo xls. al inicio de esta nota.

Importar tablas en formato html desde Excel

2013-03-28T11:24:00.003-07:00

Como verán, esta es la primera publicación que incluye un video. Debo ser sincero y reconocer que no soy un avezado en el tema pero me embarque en la aventura de presentar la información de esta forma un tanto más diciente.

En fin, en este video (sin audio) presento una forma sencilla de importar desde Excel, tablas provenientes de páginas web (formato HTML). En este caso, la agenda de un curso de universidad que tomé.

El plus de este truco radica en la versatilidad de Excel para hacer que el producto final encaje relativamente bien en las celdas. Por lo que terminar el trabajo será mucho más sencillo y rápido que si se digitara cada dato de la tabla en la hoja de cálculo.

La referencia principal de esta nota aparece en el buen blog: JLD Excel.

Protección de un archivo con clave de acceso

2013-03-28T09:37:00.000-07:00

Una pregunta inusual resultó de una conversación desprevenida. ¿Cómo asignar clave de acceso a un archivo de Excel?

Voy a exponer la manera de hacerlo en la versión 2013; es decir la nueva y en la versión 2010, que en ultimas se trata de un proceso similar.

Existen dos posibilidades:

1.Generar una contraseña de apertura.

Versión 2013

Ruta: Archivo/Información/Proteger libro/Cifrar con contraseña

Esta es mi opción favorita, ya que restringe el archivo de manera absoluta. En otras palabras, si no se tiene la clave de acceso, no se dejará ni siquiera ver la información allí contenida.

Finalmente se ingresa la contraseña con la que se protegerá el archivo.

En la versión 2010, como mencioné anteriormente es algo semejante.

2. Generar Contraseña de escritura.

Versión 2013

Ruta: Archivo/Guardar como/Examinar/Herramientas/Opciones generales/Contraseña de escritura

Esta opción restringe el archivo a sólo lectura; es decir que lo deja abrir pero no deja que se modifique la información. En caso de querer hacerlo, se tendría que guardar como un archivo nuevo.

Usando esta ruta es posible agregar la clave de acceso antes explicada.

Versión 2010.....

Frente a este tema debo opinar que es la forma más segura que conozco de proteger la información gestionada a través de Excel, dado que sólo es posible desencriptar el archivo mediante un software; lo que dificulta un tanto las cosas para aquel sagaz de la informática.

Tips para optimizar el uso de Excel_Parte II

2013-01-28T10:21:00.000-08:00

Después de revisar cómo trabajar con dos libros de excel al tiempo (tips para optimizar el uso de excel). En esta nota veremos como ver varias hojas simultáneamente, incluso veremos como revisar dos zonas o partes de la misma hoja a la vez.

En primera instancia resolveremos el interrogante ¿cómo trabajar con dos hojas al tiempo?, por ejemplo cuando tenemos una hoja en la que alimentamos una base de datos y otra con un gráfico que se surte de la primera. Para hacerlo debemos:

1. Seleccionamos la hoja que queremos ver en una segunda ventana.

2. Seleccionamos la opción ventana de la pestaña vista.

3. Inmediatamente se genera una ventana o vista del mismo libro pero con el nombre del libro acompañado de un número que denomina el total de vistas creadas. En este caso aparece así:

"Libro_nota Tips parte II: 1"

4. Seleccionamos la hoja que deseamos ver en la parte izquierda de la pantalla y elegimos la opción Organizar todo (organizar vertical) de la pestaña vista..

El resultado es:

Ahora resolveremos el segundo interrogante, ¿cómo trabajar con dos zonas de la misma hoja a la vez?

El proceso es similar al anterior, sólo que al seleccionar la opción nueva ventana, dejamos que opere en la misma hoja. Entonces bastaría con seleccionar zonas de la hoja diferentes para cada ventana.

Decidí elegir la opción organizar horizontal para este ejemplo. Al menos en mi opinión, resulta adecuado.

Sumar entre fechas

2013-01-02T10:31:00.001-08:00

Descargar el ejemplo

Para iniciar con esta nota, debo mencionar que soy un admirador de la función SUMAR.SI.CONJUNTO(rango_suma,rango_criterios1,criterio1,rango_criterios2,criterio2…), por su utilidad y facilidad de implementación. Les recuerdo que esta función permite sumar en un rango, teniendo en cuenta hasta 127 criterios....

En una nota anterior se uso esta función para sumar cantidades de productos de acuerdo con dos criterios: El mes y el artículo.

En algún momento tuve la necesidad de sumar de acuerdo con tres criterios: rubro, fecha inicial y fecha final..
Entonces, apareció de nuevo la función en cuestión; la aparición tuvo lugar en el sitio: EXCELLENTIAS. Con esto quise darle los créditos correspondientes al autor. Mi humilde aporte a esta excelente nota fue usar en lugar de listas desplegables de la opción validación de datos, las listas desplegables de la opción controles de formulario, esto le confiere un aspecto más profesional a nuestras aplicaciones; al menos en mi opinión.

Pues bien, iniciemos...

Se pretende consultar el ingreso por rubro y por fecha desde un formato que es alimentado por una tabla.

Formato de consulta:

Tabla consultada:

Instalamos los controles de formulario.

Para darle un aspecto 3D, damos click derecho sobre el objeto, pestaña Control, y elegimos la opción_Sombreado 3D.

Ahora, vincularemos los datos de fecha de la hoja Tabla_Consulta con las listas desplegables. Recordemos que como se trata de la herramienta tabla, no requerimos generar rangos dinámicos para que cuando se actualice la información en la tabla, el control también haga lo propio.

El control de fecha inicial se vincula con la celda G2.

El control de fecha final se vincula con la celda G4.

Ahora, nos ocuparemos de la formulación con la función SUMAR.SI.CONJUNTO, de tal manera que podamos sumar rubros de acuerdo con un rango de fechas; aquí representado por dos controles. Uno de inicio y otro de final.

=SUMAR.SI.CONJUNTO(Tabla1[VALOR];Tabla1[[CONCEPTO ]];B6;Tabla1[FECHA];">="&INDICE(Tabla1[FECHA];$G$2);Tabla1[FECHA];"<="&INDICE(Tabla1[FECHA];$G$4))

¡Si!, entiendo que al principio se ve compleja pero veremos que no lo es tanto....

Tabla1[VALOR], corresponde al Rango suma; es decir el rango de la tabla que se pretende sumar. Recordaran que la información se consulta a partir de una tabla, por lo que aparece el nombre de la tabla acompañado por corchetes y el nombre del campo (VALOR).

Tabla1[CONCEPTO ], hace referencia al rango criterio # 1.

B6, es la celda que contiene el concepto a consultar desde la hoja "Sumar_Entre_Fechas", es decir, el criterio #1

Tabla1[FECHA], corresponde al rango criterio # 2, es decir el rango de fechas de la hoja "Tabla_Consulta".

">="&INDICE(Tabla1[FECHA];$G$2), este es el criterio # 2. aquí está la clave para usar el control de formulario. Se usa la desigualdad ">=" para hacer referencia a la fecha que muestra el control de fecha inicial. Recordemos que G2 es la celda con la que se vinculó el control.

"<="&INDICE(Tabla1[FECHA];$G$4), este es el criterio # 3. Se usa la desigualdad "<=" para hacer referencia a la fecha que muestra el control de fecha final. Ahora, la celda G4 es la celda con la que se vínculó el control.

El resultado.....

Alerta por vencimiento de fechas

2012-12-23T10:25:00.001-08:00

Descargar el ejemplo

Una de las herramientas más útiles para visualizar eventos dentro una plantilla, aplicación o como quiera que se le denomine, es el formato condicional. Innumerables posibilidades gráficas ofrecen la versión 2007 y 2010; incluso para realizar controles a partir de cambios en formatos (Color de fondo de celda, bordes,tipo de fuente, entre otros). En esta nota haremos uso de la opción "utilice una fórmula que determine las celdas para aplicar formato", para controlar el Stock de reactivos de un laboratorio...

Para acceder a la herramienta antes mencionada, vamos a la pestaña Inicio-Formato condicional.

Se establecerán tres formulaciones con la función Y() para generar tres alertas. cuando se cumpla la fecha de vencimiento, la celda contigua a la celda que contiene la fecha se verá de color rojo; cuando falten 30 días para caducar, la celda mencionada se verá de color amarillo, y por último, cuando falten seis meses para caducar, la celda se verá de color azul.

Recordemos que la función Y() evalúa una serie de condiciones; si todas las condiciones son verdaderas, la función devuelve Verdadero, si alguna no se cumple, la función devuelve Falso.

Para este caso sólo nos interesa saber si se cumple la condición. Celda que contiene la fecha de vencimiento(C8) menos la fecha de hoy (HOY()), es igual a 180, 30 o cero días. Si alguna de las condiciones se cumple, la celda ostentará el color que le corresponda..

El resultado se verá así:

Comparto el link del archivo modificado de esta nota que contiene algunas modificaciones que surgen después de la consulta realizada por un lector.

Alerta por fechas de vencimiento

Asignar nombres a celdas y rangos

2012-11-12T10:56:00.000-08:00

Descargar el ejemplo

Confieso que esta es una nota que debí escribir hace tiempo, dada la importancia y la versatilidad que ofrece este tipo de formulación. Básicamente, podríamos definir el título de esta nota como, usar una palabra para identificar una celda o un rango de datos; para después hacer referencia a lo creado en una formulación.

Beneficios al usar esta técnica:

Formulaciones fáciles de entender.
Formulaciones fáciles de revisar y modificar.
Fácil administración de los nombres cuando se tiene cierta práctica.
Útil cuando se requiere generar rangos dinámicos.
Útil cuando se hace referencia a celdas o rangos ubicados en otras hojas del mismo libro.

Para asignar un nombre a una celda o rango, seleccionamos la celda o rango, hacemos click en cuadro de nombres; escribimos el nombre y confirmamos este con la tecla Enter. Es preciso mencionar que el cuadro de nombres no permite espacios en blanco (al igual que los caracteres, . ; : + - / () = > < $ % @ entre otros). Para incluir un nombre compuesto por dos palabras se puede utilizar el caracter underscore "_".

Aquí un ejemplo sencillo.

El nombre creado se puede utilizar en cualquier formulación, en lugar de las referencias de filas y columnas. Para hacerlo de manera sencilla, pulsamos la tecla F3, elegimos el nombre con doble click sobre este, y finalmente aceptamos.

Otra forma de hacerlo es desde la opción "crear desde la selección" de la pestaña Fórmulas. Muy útil cuando se cuenta con un rango que tiene un encabezado..

Para utilizar esta alternativa se requiere seleccionar tanto el rango como la celda que contiene el nombre que identificará el rango.

Por último, es posible usar una tercera alternativa, y en mi opinión la más completa. Se ejecuta mediante el formulario "administrador de nombres" de la pestaña Fórmulas, o mediante el comando Control+F3.

Esta opción es útil cuando se requiere generar nombres que hacen referencia a rangos dinámicos...

Buscar de Izquierda a derecha con la función Indice

2012-10-30T11:16:00.002-07:00

Descargar el ejemplo

La mayoría que utiliza la función BUSCARV, se habrá percatado que esta realiza la búsqueda de izquierda a derecha usando como referencia el argumento "indicador de columnas"; de tal manera que si se tiene una base de datos en donde la columna A corresponde a los números de identificación, y la columna D corresponde al primer nombre, es posible hacer una consulta del nombre a partir de un número de identificación mediante la función antes referida.

La celda I3 contiene los números de identificación ( celda validada).

Pero ¿qué sucede si en la columna C se encuentran los nombres, y en la columna G se encuentran los números de identificación? pues la respuesta es usar la función INDICE, acompañada de la función COINCIDIR.

La formulación es la siguiente:

=INDICE(C2:C1084;COINCIDIR($I$4;G2:G1084;0)

Recordemos los argumentos de estas funciones.

=INDICE(matriz; núm fila; núm columna)

Matríz: rango de datos en el que se realizará la búsqueda
Núm fila: referencia numérica que indica la posición de un dato en términos de fila dentro de la Matríz En este caso se usó la función COINCIDIR para "traducir" los números de identificación que están en la celda I4, de tal forma que la función lo reconozca como una dirección de fila.
Columna: referencia numérica que indica la posición de una columna dentro de la Matríz.

=COINCIDIR(valor buscado; matriz buscada; tipo de coincidencia)

Valor buscado: valor a buscar dentro de la Matríz En nuestro caso corresponde a la celda I4, es decir, la celda validada que contiene los números de identificación.
Matríz buscada: rango de datos en donde se busca el dato.
Tipo de coincidencia: si es 0 se busca un valor que sea igual al dato indicado. Si es 1 se busca el mayor valor en un rango que sea menor o igual al dato buscado (el rango debe estar ordenado en forma creciente). Si es -1 busca el valor mayor o igual al dato considerando el rango ordenado decrecientemente. En nuestro caso hemos elegido coincidencia exacta.

Gráficos Interactivos parte dos

2012-10-15T10:37:00.001-07:00

Descargar el ejemplo

En la primera entrega acerca de gráficos interactivos, se elaboró un gráfico que enseñaba un número de datos por serie, mediante un SpinButton y un TextBox (del grupo de controles Activex). Ahora, se pretende adicionar a este gráfico un combobox que nos permita decidir cual de las cuatro series mostrar ¡claro! manteniendo las bondades de actualizar automáticamente la serie de datos, y de limitar el número de datos por serie.

Primero, instalamos el combobox en el gráfico:

Configuraremos algunas propiedades del control.

1.LinkedCell: básicamente permite vincular el control con una celda, en nuestro caso, lo vincularemos con la celda H12.

2.ListfillRange: le asigna un rango de datos al control, se puede digitar el rango o un nombre previamente creado. Para este caso he decidido digitar directamente el rango (I12:I15) ya que este será fijo. El rango anterior corresponde a los números 1,2,3,4 que serán las convenciones de las series.

En segundo lugar, requerimos generar los siguientes nombres (acceda rápidamente al administrador de nombres con el comando Ctrl+F3):

Serie1

=SI(Hoja1!$H$12="1";Datos1;Datos5)

Serie2

=SI(Hoja1!$H$12="2";Datos2;Datos5)

Serie3

=SI(Hoja1!$H$12="3";Datos3;Datos5)

Serie4

=SI(Hoja1!$H$12="4";Datos4;Datos5)

Datos5

=Hoja1!$V$4:$V$33

Recordemos que en la nota anterior, los nombres Datos1, Dato2, Datos3 y Datos4, correspondían a las series dinámicas del gráfico. Aquí, se usará la función SI() para evaluar el valor que arroja el control en la celda H12 cuando se manipule. Entonces, si elegimos el valor uno del control, debería mostrar la serie Datos1, que a su vez corresponde a =DESREF(Hoja1!$B$2;0;0;Hoja1!$G$8;1); es decir la serie1. De lo contrario mostrara la serie Datos5, que como se puede ver, corresponde a la serie =Hoja1!$V$4:$V$33 y que debe estar vacía (para que el gráfico no muestre datos). De la misma forma sucederá con los otros valores elegidos desde el control.

Pues bien, lo que resta es asignar estos nuevos nombres (Serie1, Serie2, Serie3 y Serie4) a las series del gráfico. Señalamos el gráfico, como se indico en la nota anterior, de inmediato se activa una especie de pestaña adicional llamada Herramientas de gráficos. De esta elegimos la opción Diseño, luego el botón Seleccionar datos

Como se ve en el formulario Seleccionar origen de datos, podemos seleccionar cada serie para editarla.

La serie se vera así:

=Hoja1!$B$2:$B$55

Debemos reemplazar $B$2:$B$55 por el nombre creado anteriormente para esta serie; es decir, Serie1.

¡cuidado con borrar el signo de admiración(!)

Cuando ejecutemos la acción anterior, el formulario "Modificar serie" se vera asi:

Tendremos que ejecutar la misma acción para las restantes tres series. Al final se obtendrá un gráfico como este:

Un gráfico que muestra sólo la serie que deseo ver, además, se puede limitar el número de datos a presentar por serie.

Gráficos Interactivos parte uno

2012-09-03T18:31:00.001-07:00

Descargar el ejemplo

¿Exsite alguna forma de limitar el número de datos que se puede ver en una gráfica? además ¿Puedo hacerlo con todas las series de datos que tenga, de manera que pueda seguir ingresando datos a las celdas que alimentan las series y que el gráfico se actualice automáticamente?.

La respuesta es ¡si!

El primer interrogante se resuelve así:

En primer lugar, se debe tener varias series susceptibles de graficar.

Insertamos el gráfico, en este caso he utilizado un gráfico de línea. Para hacerlo nos situamos en cualquiera de las celdas de las cinco columnas, damos click en la pestaña insertar y elegimos el tipo de gráfico que requerimos.

Posteriormente hay que generar nombres volàtiles para las series, así:

Accedemos al cuadro administrador de nombres de manera rápida con el comando Ctrl+F3

Generamos el primer nombre "Datos1".

La formulación descrita en el campo "Hace referencia a:"

=DESREF(Hoja1!$B$2;0;0;Hoja1!$G$8;1)

Define el alto de fila de la columna B (Alcalinidad). Es decir, inicia en la celda B2, no se desvia en términos de fila ni de columna. En el argumento fila hay una variación, pues usaremos dos botones de Activex controlados desde la celda G8. el argumento columna será uno, puesto que solo vamos a trabajar con una columna por serie de datos.

Lo mismo hacemos con las tres siguientes columnas.

Por último insertamos dos controles en el gráfico; SpinButton y TextBox de la categoría Controles Activex_pestaña programador_botón Insertar Controles.

Tanto el SpinButton como el TextBox se vinculan con una celda (G8 en nuestro caso). Esto se hace desde la pestaña programador, accionando el modo diseño. Damos click en el control y oprimimos el boton de propiedades. Ya en el cuadro de propiedades, buscamos la propiedad LinkedCell, y en frente de esta digitamos la celda que servira de referencia.

Para relacionar los controles con las series de datos, señalamos el gráfico, nótese que al hacerlo, aparece en color verde una barra denominada "Herramientas de gráficos". De esta, seleccionamos la pestaña Diseño, luego el boton "Seleccionar datos".

En el formulario que aparece como "Seleccionar origen de datos", podemos dar click en las series del gráfico y editar. En el nuevo formulario denominado "Modificar serie", modificamos los valores de la serie.

La serie aparece asi:

=Hoja1!$B$2:$B$55

Debemos reemplazar $B$2:$B$55 por el nombre creado anteriormente para esta serie; es decir, Datos1.

¡cuidado con borrar el signo de admiración(!)

Cuando ejecutemos la acción anterior, el formulario "Modificar serie" se vera asi:

Debemos hacer lo mismo para las otras tres series. El resultado será:

Un gráfico que muestra solo un número determinado de datos por serie; esto claro, controlado por un boton de número. Espero que esta nota sea de ayuda.