viernes, 25 de noviembre de 2011

Sumar los valores en negrita

Descargar el fichero: suma_negritas.xlsm

En Excel es frecuente trabajar introduciendo colores o negritas en las celdas que deseamos resaltar, y luego se presenta la tarea de tener que contar o sumar los valores de las celdas que hemos resaltado. En otra entrada de este blog ya dimos varios métodos para sumar las celdas según su color. Puedes verlo en este enlace:

Contar y Sumar los valores de las celdas según su Color

Ahora vamos a ver algún método para sumar las celdas en negrita.


Método 1

Vamos a crear una función personalizada que nos indique con VERDADERO o FALSO si una celda está en negrita o no.

La función se denomina negrita y es la siguiente.

Código:

Function negrita(ByVal Rng As Range) As Boolean
'Solo para una celda
If Rng.Count <> 1 Then negrita = CVErr(xlErrNum)
'Detecta si es negrita
If Rng.Font.Bold Then negrita = True
End Function

En la celda F5 calculamos la suma por el método 1.

=SUMAPRODUCTO(B4:B12;--(C4:C12))

Utilizamos la función SUMAPRODUCTO. El par de signos menos sirven para convertir a numéricos (ceros y unos) los valores de la columna C.

Este método no es perfecto ya que si se cambia una negrita, añadiédola o quitándola, no se recalcula automáticamente hasta que no se actualicen las fórmulas de la columna C.

Método 2

El método 2 calcula directamente la suma de las celdas que están en negrita, sin necesidad de utilizar una columna auxiliar. Pare ello hemos diseñado una nueva función personalizada que se denomina suma_negritas.

Código:

Function suma_negritas(ByVal Rng As Range) As Double
Dim celda As Range
For Each celda In Rng
  If celda.Font.Bold Then
    suma_negritas = suma_negritas + celda.Value
  End If
Next
End Function
Esta fórmula personalizada calcula la suma de las celdas que se indiquen en el rango, y siempre que estén en negrita.

Si añadimos una negrita, o bien la quitamos, la suma no se actualiza automaticamente y debemos pulsar F9, que es la tecla de recálculo manual, o bien escribir algo en alguna celda cualquiera de la hoja.

Contar las negritas

Proponemos al lector que diseñe una función que cuente los valores que se encuentran en negrita, dentro de un rango dado. Por ejemplo, se podría llamar cuenta_negritas.

Y, ¿qué tal una función que calcule el máximo, pero únicamente de las celdas en negrita?. Se podría llamar max_negritas.

jueves, 17 de noviembre de 2011

Crea una hoja Index en tu Excel

Descargar el fichero: index.xlsm

Hagamos un indice en la primera hoja de un libro de Excel, para listar todas las hoja y establecer un hipervínculo que nos lleve a cada hoja. Cuando en un libro de Excel tenemos muchísimas hojas, en ocasiones es aburrido llegar a ir hasta una concreta. Para facilitar nuestro trabajo vamos a establecer una primera hoja llamada Index en la que vamos a escribir el nombre que queremos dar a cada hoja y luego las vamos a hipervincular.


Inicialmente las hojas del libro se denominan: Hoja1, Hoj2, y así hasta Hoja101.



Disponemos de una macro que renombra las hojas dándolas el nombre que hemos indicado en la columna C, comenzando en la celda C5. La primera hoja será la propia hoja en la que se encuentra el listado y que denominaremos Index. Las 100 hojas restantes irán tomando el nombre que se indica en la lista.


Código:

Sub renombra_hoja()
Dim Hoja As Worksheet
Dim Fila As Long
Fila = 5
For Each Hoja In Worksheets
  Hoja.Name = Cells(Fila, 3)
  Fila = Fila + 1
Next
End Sub



Disponemos de otra macro que crea los hipervínculos.


Código:

Sub Hipervincula()
Dim Nombre As String
Range("C5").Select
Do
  Nombre = Selection.Value
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
      "'" & Nombre & "'!A1", TextToDisplay:=Nombre
  ActiveCell.Offset(1, 0).Select
Loop While Not IsEmpty(ActiveCell)
Range("A1").Select
End Sub


Incluimos dos botones que lanzan las macros.


Debemos tener especial cuidado de que en la lista existan tantos nombres como hojas existen el el libro de Excel en el que estamos.

¿Y si no quiero cambiar los nombres?

En caso de que desees establecer los hipervínculos pero no quieras cambiar los nombres de las hojas, puedes recurrir al ejemplo siguiente.


Disponemos de una macro que lista los nombres de las hojas existentes y luego le pasamos la macro que crea los hipervínculos.

La macro que lista las hojas existentes es la siguiente.

Código:

Sub Lista_nombres()
Dim Hoja As Worksheet
Dim Fila As Long
Fila = 5
For Each Hoja In Worksheets
  Cells(Fila, "G") = Hoja.Name
  Cells(Fila, "F") = Fila - 4
  Fila = Fila + 1
Next
End Sub

La macro que realiza los hipervínculos la hemos llamado Hipervincula2 que es similar a la macro Hipervincula. Lo único que cambia es la celda de inicio que ataca la macro.

martes, 1 de noviembre de 2011

Consolidar tablas de datos

Puede descargar los archivos de Excel siguientes.
Ya hemos tratado el tema del consolidado. Para ello utilizábamos Tablas Dinámicas con rangos de consolidación múltiple. Puede consultar el Post que se publicó:

En esta ocasión realizaremos el trabajo de consolidación con la herramienta Datos, Consolidar.


En la versión de Excel 2007 se encuentra en el menú Datos.



Añadimos los 4 rangos de datos indicados de la Hoja 3.



Agregamos los 4 rangos.


Es importante marcar:

  • Fila superior
  • Columna izquierda
Al aceptar se genera la tabla del consolidado.


Al cambiar algún dato en la tabla de origen no cambia automáticamente en la tabla del consolidado. Para actualizar la tabla tendríamos que lanzar nuevamente la herramienta de consolidación.