miércoles, 29 de marzo de 2017

Macro para copiar columna

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. 


sábado, 14 de enero de 2017

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


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.


 


sábado, 29 de octubre de 2016

Estimación.Lineal

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:

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




sábado, 30 de abril de 2016

Multiplicar horas y minutos por enteros


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.










sábado, 18 de julio de 2015

Buscar valor mediante macro


 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.












domingo, 1 de febrero de 2015

Carta de Control de Rangos

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 : 



Factores que se usan para su construcción:



(n) Corresponde al número de lecturas.
















jueves, 29 de enero de 2015

Copiar Hoja Mediante Código VBA

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.



























lunes, 18 de agosto de 2014

Herramienta Subtotal

¡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:














domingo, 22 de junio de 2014

Buscar Objetivo


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. 




  1. 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












lunes, 24 de marzo de 2014

Estimar la cuota de un crédito

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:











sábado, 8 de marzo de 2014

Referencia en tres dimensiones


 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.