La herramienta Seleccionar celdas visibles en Excel.

Algunas entradas atrás expliqué como añadir un Suma y sigue o un Suma anterior al inicio y fin de las páginas impresas (ver). A partir de éstas, surgió la duda, a una lectora, de cómo podíamos seleccionar sólo las filas añadidas de subtotales, sin ir de una en una, para cambiarles el formato:

...con un filtro en el subtotal por hoja, en el suma y sigue y en el total anterior, pude darle formato a la celda en cuanto a dimensiones y de lineas ...solo en dichas lineas, pero cuando intento colocarles color de la misma forma...toda la planilla asume dicho color...que dolor de cabeza...(te cuento que en el primer subtotal elegido si asumieron únicamente las lineas elegidas como quería, pero para la segunda selección ya no hubo caso)quiero que las filas de los distintos subtotales queden bien diferenciadas ..hay alguna forma que solo las lineas elegidas asuman los parámetros que les doy..o solo podré darles color linea x linea?...

Veamos como trabajar, entonces, con rangos de celdas discontinuos de una manera rápida y sencilla.
Comenzaremos con un listado de valores, a los que hemos añadido un columna auxiliar con valores tomados de cuatro en cuatro, al estilo de lo que hicimos en este post, para luego aplicarle la herramienta Subtotal.
Tenemos entonces:

La idea es seleccionar de una vez, sólo las celdas de la columna B ('Datos'), que correspondan a las filas de Subtotales añadidos.
Para eso, el primer paso será sacar a la Barra de herramientas de acceso rápido el comando Seleccionar celdas visibles:

Seguir leyendo...

Sudoku 4x4 con Solver.

Constantemente veo a personas pasar su tiempo resolviendo esos puzzles numéricos que conocemos como Sudoku, y pensando sobre el tema, me vino a la cabeza que la solución a éstos no es más que el encuentro de las raices de un sistema de ecuaciones; desde este planteamiento, decidí intentar resolver un Sudoku con la herramienta de Excel habilitada para este asunto: Solver.
Para no complicar demasiado el sistema de ecuaciones a plantear, me decido por un mini-Sudoku de 4x4, eso sí, le daremos la máxima dificultad, con un mínimo de valores de partida:

Los primeros pasos consistirán en construir el mayor número de ecuaciones sobre nuestro Sudoku. Empezaremos con sumas de valores, ya que sabemos que la suma de cada fila y cada columna del cuadro debe ser igual a 10 (=1+2+3+4), de igual modo que la suma de cada uno de los minicuadros que contiene el Sudoku también debe sumar 10; además como criterio adicional añadiremos una última ecuación, que determine a qué debe ser igual el producto de los cuatro elementos por minicuadro(=24 = 1x2x3x4).
Un último condicionante para las celdas a completar es que sus valores se encuentren entre 1 y 4.
Como celda objetivo elegiremos la celda $G$7, como suma de las filas del sudoku ( =SUMA(G2:G5) ), que deberá ser 40.

Seguir leyendo...

Añadir un Suma anterior al inicio de cada página impresa.

Algunos días atrás escribí una explicación de cómo añadir Subtotales al final de cada página impresa, la cuestión que explicaré hoy no podía hacerse esperar.
¿Cómo añado una Suma anterior al principio de cada página impresa:

...¿se podría con un criterio similar obtener una fila al final (tendría que ser la penúltima fila) de cada página con el subtotal de los valores de la página y otra fila bajo la misma que indique el total acumulado de cada una de las páginas anteriores? (la fila del acumulado podría ser la primera fila de la siguiente pagina tal como un transporte en un libro diario contable)...

La respuesta la encontraremos igualmente con la herramienta Subtotal.
Veamos los pasos a seguir, si partimos de esta tabla de valores:

En primer lugar, dividimos nuestro listado en cuatro partes, para que cada una de ellas se imprima en una página diferente. Para ello, añadiremos una columna auxiliar con la función:
=ENTERO((FILA()+2)/4)
inmediatamente después, los copiaremos y pegaremos como valores. Si no realizamos este punto, en pasos siguientes tendremos ciertas incongruencias.
Seleccionamos nuestro nuevo rango, y ejecutamos la herramienta Subtotal:

Como vemos en la imagen, hemos decidido añadir Subtotales a los campos 'Rosas', 'Margaritas' y 'Violetas'. También es muy importante que para este primer Subtotal agregado no marquemos la opción de Salto de página entre grupos.
Sin desmarcar el rango activo, volvemos a ejecutar la herramienta Subtotal, pero teniendo cuidado de seleccionar la opción Salto de página entre grupos, ahora sí:

Seguir leyendo...

Añadir un Suma y sigue al final de cada página impresa.

Recientemente leía un correo de una lectora que me preguntaba sobre la manera de añadir Subtotales al final de cada página impresa:

...Tengo una base de datos que al momento de imprimir tiene alrededor de 120 paginas…Necesito que al momento de imprimirlas aparezcan en ellas los subtotales al pie de cada pagina y al final de la ultima pagina el total general…o quizas como se pueden insertar los subtotales en la parte inferior de cada pagina rapida o automaticamente en la parte inferior de cada pagina en las hojas de trabajo antes de imprimirlo...

La solución que daré hoy no necesita de macros.
Supongamos que en nuestra Hoja de cálculo tenemos un campo con 1000 registros, en nuestro ejemplo valores del 1 al 1000, saldrían immpresas unas 17 páginas:

El siguiente paso es clave, ya que añadiremos una columna auxiliar con la siguiente función:
=ENTERO(FILA()/58)
con esto conseguiremos dividir nuestros 1.000 registros en grupos de 58 (he elegido 58 por que más o menos son los registros que caben en cada página).

Para que al final quede más vistosa nuestra impresión, desde Diseño página > Configurar página > Imprimir títulos > Repetir filas en extremo superior y marcaremos que se repita al imprimir la fila 1:

Seguir leyendo...

Proteger y desproteger proyectos VBA en Excel.

En ocasiones, al desarrollar nuestro trabajo, hemos generado alguna macro o código VBA en nuestras hojas de cálculo; y estos ficheros los compartimos con compañeros 'peligrosos' para nuestras programaciones. ¿Qué podemos hacer para proteger o desproteger nuestros proyectos en VBA de Excel?.
El proceso es muy sencillo.
Accederemos al Editor de VBA (Alt+F11) y buscaremos en el Explorador de proyectos el VBAProject a proteger (no es posible proteger/desproteger módulos independientes dentro del proyecto).
Con el botón derecho del ratón o bien desde el Menú Herramientas haremos click sobre las Propiedades de VBAProject...

Aparecerá la siguiente ventana, donde marcaremos Bloquear proyecto para visualización, para luego introducir y confirmar la contraseña, todo esto dentro de la pestaña Protección:

Seguir leyendo...

VBA: Cómo insertar filas. El método Insert.

Aprenderemos hoy a emplear la instrucción de VBA que nos permite insertar filas en nuestra hoja de cálculo.
La entrada da solución al problema planteado por un usuario, como continuación de un post anterior (ver):

...como hago si no tengo las filas creadas en blanco, ¿hay alguna función que cree esas filas?. [...]en este caso yo quiero que se me auto complete con el codigo 000025, 000026, 000027 y asi con los restantes codigos...

La idea es que nuestra macro verifique, en primer lugar, donde hay un salto de valores en el rango de trabajo; una vez haya encontrado dicho salto insertaremos tantas filas como se requieran para completar la continuidad; por último completaremos las nuevas filas con los valores correspondientes.
Veamos nuestro listado de valores, fijándonos en los saltos de valor marcados en amarillo:

Por ejemplo, la macro evaluará la fila 5 y 6, donde ha encontrado un salto entre los valores de las celdas (pasa de 3 a 10); lo que implicará que nuestra macro insertará 6 filas nuevas, y cada nueva fila la completará con el valor adecuado, esto es, valores de 4 a 9...
Veamos nuestro código VBA, que se reparte entre dos macros (CompletarFilas() y ultimafila().
Nuestra programación se incluirá el código en un módulo del Editor de VBA (Alt+F11) será:

'declaramos variables públicas que sirvan para
'todo los procesos de este proyecto.
Public UltFila As Long
Public rng As Variant

Sub CompletarFilas()
'www.excelforo.blogspot.com
Dim hoja As Worksheet
Dim celda As Object
Dim destino As Integer
Dim fila1 As Integer, fila2 As Integer

ScreenUpdating = False
Set hoja = Sheets("Hoja1")
'llamamos la macro 'ultimafila para que calcule
'de manera actualizada cuál es dicha fila
ultimafila

For Each celda In hoja.Range(rng)
'con destino calculamos el salto de valores
destino = celda.Offset(1, 0).Value - celda.Value - 1
fila1 = Range(celda.Address).Row + 1
fila2 = Range(celda.Address).Row + destino
'comprobamos si hay salto de valores en el rango
If celda.Offset(1, 0).Value - celda.Value > 1 Then
'si existe, insertamos tantas filas como valores falten por completar
Rows(fila1 & ":" & fila2).Insert Shift:=xlDown
'rellenamos dichas celdas con los valores correspondientes
For i = fila1 To fila2
Cells(i, 1).Value = i - 2
Next
End If
If celda.Offset(1, 0).Row > UltFila Then Exit Sub Else ultimafila

Next celda

ScreenUpdating = True
'liberamos las variables de objeto
Set hoja = Nothing
Set rng = Nothing

End Sub

Sub ultimafila()
Dim hoja As Worksheet

'Encontramos la última fila con valores CADA VEZ
Set hoja = Sheets("Hoja1")
UltFila = hoja.Range("A" & hoja.Rows.Count).End(xlUp).Row
rng = hoja.Range("A2:A" & UltFila).Address

End Sub

Si ejecutamos la macro CompletarFilas comprobaremos cómo responde según lo esperado, esto es, insertando filas y dándoles los valores correctos.

Seguir leyendo...

Rellenar celdas en blanco en Excel.

Para responder a la cuestión planteada por un lector del blog, emplearemos una herramienta poco conocida, Ir a especial > celdas en blanco:

...tengo una lista de más de 5000 productos y hemos realizado un inventario, resulta que al pasar el inventario en el excel hay algunos productos que no figuran, es decir, yo tengo el prod 001, 002, 003 y luego me salta al prod 007, que función tengo que usar para me vaya auto complentando los productos que faltan en este caso el 004, 005 y 006...

El objetivo es claro, que se autorrellenen exclusivamente las celdas vacías o en blanco con el valor deseado.
Para ello deberemos selccionar, en primer lugar, todo el rango de datos (vacíos o con valor).
En segundo lugar activaremos la herramienta Ir a, presionando la tecla de función F5, bien Ctrl+i o bien navegando desde la Ficha Inicio > Grupo Modificar > Buscar y seleccionar > Ir a (o Ir a Especial)

Seguir leyendo...

VBA: Macro para rellenar celdas en blanco en Excel.

En una entrada anterior vimos como trabajar con la herramienta Ir a Especial > Celdas en blanco para rellenarlas con un valor o fórmula concreta(ver).
El problema de esta herramienta es que para un número elevado de valores o celdas a rellenar puede fallar. Será en estos casos cuando debamos emplear las macros.
Partimos de un ejemplo similar a la del post anterior:

Sabiendo que nuestros datos se encuentran en la 'Hoja1' y que el rango de celdas a evaluar comienza en A1, el código a incluir en un módulo del Editor de VBA (Alt+F11) será:

Sub RellenaCeldasenBlanco()
Dim hoja As Worksheet
Dim UltFila As Long
Dim Rng As Range

Set hoja = Sheets("Hoja1")
With hoja

'Encontramos la última fila con valores
UltFila = .Range("A" & .Rows.Count).End(xlUp).Row
'creamos el rango
Set Rng = .Range("A1:A" & UltFila)
End With

'formulamos las Celdas en Blanco para que tomen el valor de la celda anterior +1
Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"

'liberamos las variables de objeto
Set hoja = Nothing
Set Rng = Nothing

End Sub

El resultado será la introducción en cada una de las celdas en blanco del rango de la fórmula =R[-1]C+1, o lo que es lo mismo celda anterior más uno.

Seguir leyendo...

Un ejemplo de la función FILAS(rango) en Excel.

Usaremos en esta ocasión la función FILAS(rango), anidándola en otras funciones y combinando su uso con la herramienta Tablas o Listas.
Como ejemplo desarrollaré un problema planteado por un usuario:

...Se trata de un fichero con datos de posibles clientes que tengo que repartir entre varios comerciales, conforme a sus objetivos, y una vez repartido el fichero copiar los datos de cada uno en una hoja aparte para enviárselo. Por ejemplo: Fichero Datos: 5000 registros de clientes con campos: nombre, domicilio, localidad, provincia. 5 comerciales con 3 campos cada uno: número, nombre, código Como cada comercial tiene un objetivo, le asigno un número de clientes (varía cada mes): Ejem: El 1 1500 2 1000 3 750 4 1250 5 500 Lo que hago y necesito automatizar, ya que se van a ampliar tanto los registros (pueden ser 35000 registros) y los comerciales (entre 25 y 30): En el fichero Datos inserto al principio 3 columnas con los tres campos que corresponden a cada comercial y pongo los 1500 primeros con los datos del 1, del 1501 al 2500 con los del 2…. Cuando ya los tengo todos repartidos, filtro por comercial y copio y pego las filas con datos de cada uno de ellos en una hoja separada para cada uno de ellos; tanto en el fichero Datos como en los de cada comercial la fila 1 es la cabecera (igual para ambos), es decir copio desde la 2 hasta el último registro que tiene asignado....

Nuestros orígenes de datos serán entonces:

haz click en la imagen

Cada origen de datos se encuentra en una hoja diferente de nuestro Libro de trabajo, de izquierda a derecha, Hoja 'objetivos', Hoja 'comerciales' y Hoja 'clientes'.
Nuestra primera labor consiste en crear, para cada una de ellas, una Tabla o Lista; para lo que seleccionamos los diferentes rangos y presionando Ctrl+q los convertiremos en Tablas... con todas las funcionalidades de éstas (ver). Nuestras tres tablas son entonces:
Tabla 'Clientes': Hoja 'clientes' y rango A1:D31
Tabla 'Comerciales': Hoja 'comerciales' y rango A1:C8
Tabla 'Objetivos': Hoja 'objetivos' y rango A1:B8
¿Por qué hemos creado estas tablas?, bueno, el fin es que cualquier variación en alguna de estas Tablas se adapte y autorrellene en los campos nuevos que vamos a incluir, así como que las interrelaciones entre las tres Tablas tengan un componente 'dinámico'.

Siguiendo las indicaciones de nuestro lector, el siguiente paso a desarrollar consistirá en añadir una nueva columna o campo a la Tabla Objetivos con la siguiente fórmula:
=SI(FILA()=2;0;C1+A1)
esta fórmula aportará sencillamente un acumulado del número de clientes para cada Comercial de la empresa. Se trata de repartir el número total de clientes existente en la primera de las Tablas 'Clientes' entre los diferentes comerciales. Con el campo generado lo desplazaremos y colocaremos a la izquierda, o como primera columna.
Quedará así:

Seguir leyendo...

VBA: La función MsgBox en una macro.

Es frecuente encontrarnos en algunas de nuestra macros, especialmente cuando queremos interrelacionarnos con la ejecución de ésta, esta función MsgBox, mediante la cual se muestra un mensaje en un cuadro de diálogo, a la espera de que el usuario pulse algunos de los botones habilitados.
¿Cuáles son los parámetros de esta función y qué podemos esperar de ella?.
Veamos en primer lugar la Sintáxis:
MsgBox(Prompt[, Buttons][, Title][, Helpfile, Context])
donde el Prompt se mostrará en nuestro Cuadro diálogo como parte del texto,
el siguiente parametro Buttons corresponde a alguno o varios de las siguientes posibilidades (mostraré los más habituales):

• VbOKOnly: Muestra un botón 'Aceptar'
• VbOKCancel:Muestra los botones 'Aceptar' y 'Cancelar'
• VbAbortRetryIgnore: Muestra los botones 'Anular', 'Reintentar' e 'Ignorar'
• VbYesNoCancel: Muestra los botones 'Sí', 'No' y 'Cancelar'
• VbYesNo: Muestra los botones 'Sí' y 'No'
• VbRetryCancel: Muestra los botones 'Reintentar' y 'Cancelar'.
• VbCritical: Muestra el icono de mensaje crítico.
• VbQuestion: Muestra el icono de pregunta de advertencia.
• VbExclamation: Muestra el icono de mensaje de advertencia.
• VbInformation: Muestra el icono de mensaje de información.

el parametro de Title añade en la cabecera del cuadro diálogo el texto deseado; y por último Helpfile que identifica cuál es el archivo de 'Ayuda' asociado a nuestro Cuadro diálogo (si especificamos Helpfile, también se debe especificar Context).

La interacción con este cuadro diálogo se realizará a través de las respuestas obtenidas tras pulsar los botones, ya que cada respuesta pulsada (cada botón pulsado) devuelve alguno de estos valores:

1. Si pulsamos el botón 'Aceptar', vbOK
2. Si pulsamos el botón 'Cancelar', vbCancel
3. Si pulsamos el botón 'Anular', vbAbort
4. Si pulsamos el botón 'Reintentar', vbRetry
5. Si pulsamos el botón 'Ignorar', vbIgnore
6. Si pulsamos el botón 'Sí', vbYes
7. Si pulsamos el botón 'No', vbNo

Pero veámoslo con un sencillo ejemplo.
Supongamos que deseamos generar un proceso que nos pregunte al inicio de éste, si deseamos Continuar con el resto de nuestra macro; por hacerlo sencillo, sólo queremos que se nos cambie el color de fondo de la celda A1 de la 'Hoja1' a amarillo, y que además escriba en dicha celda, en rojo, el texto 'macro 'amarillo' completada'.

Sub amarillo()
'fondo de la celda amarillo
Sheets("Hoja1").Range("A1").Interior.Color = 65535
'color de la fuente rojo
Sheets("Hoja1").Range("A1").Font.Color = -16776961
Sheets("Hoja1").Range("A1").Value = "macro 'amarillo' completada"    
End Sub

El trabajo consistirá en asignar, con la función MsgBox, los botones 'Sí' y 'No'.
Nuestro código a incluir en un módulo del Editor de VBA será:

Sub CuadroDialogo()
'www.excelforo.blogspot.com
Dim respuesta As Variant
'asignamos una variable a la función MsgBox
respuesta = MsgBox("Texto del Prompt... ¿continuamos con la macro?", _
vbYesNo, "Título del Cuadro diálogo")
'Evaluamos la respuesta dada sobre el cuadro diálogo
If respuesta = vbYes Then amarillo Else Exit Sub
'si pulsamos el botón 'Sí' entonces ejecutamos la macro 'amarillo',
'si pulsamos 'No' entonces terminamos y salimos de la macro.
End Sub

Al ejecutar la macro CuadroDialogo nos aparecerá:

Sabiendo que si pulsamos 'Sí' nuestra macro 'CuadroDialogo' llamará o ejecutará la macro 'amarillo', mientras que si pulsamos el 'No' directamente saldremos del procedimiento Sub, acabando el proceso.

Seguir leyendo...