Sumar cada enésimo valor de una serie con Excel

Recibí esta consulta: dado un rango de valores, ¿cómo hacer para sumar los valores de cada quinta fila con Excel?

Supongamos estos valores en el rango A1:A20

Esta fórmula suma cada quinto valor en el rango

=SUMAPRODUCTO((RESIDUO(FILA(A1:A20),5)=0)*A1:A20)

Podemos generalizar la fórmula reemplazando la constante 5 de la función RESIDUO por una referencia a una celda

=SUMAPRODUCTO((RESIDUO(FILA(A1:A20),D1)=0)*A1:A20)

Podemos cambiar el valor de D1 para ver el resultado de sumar cada n filas



Esta fórmula funciona de la siguiente manera:

RESIDUO(FILA(A1:A20),D1)=0 da como resultado VERDADERO sólo si el número de fila es múltiplo de 5 (o el número que hayamos ingresado en D1)

Luego al multiplicar los valores de la serie por los correspondientes valores VERDADERO o FALSO obtenemos

Autofiltro de campos en tablas dinámicas

Supongamos esta tabla dinámica (basada en la tabla de facturas de la base de datos Northwind que viene con Office)

Si queremos mostrar sólo los datos de un determinado intervalo (digamos entre el 15/08/2008 y el 15/09/2008) podemos desplegar la lista de fechas y elegir las fechas una por una



Pero como estamos usando Excel 2007, la tarea es mucho más fácil. Sencillamente usamos la opción Filtros de fecha-Entre…



Como podemos ver, las posibilidades de filtrar por criterios de fechas son múltiples

Lo mismo es válido si los valores del campo de filas son texto

En Excel 97-2003 no existen, aparentemente, estas posibilidades.

Digo aparentemente porque con un pequeño truco podemos replicar la funcionalidad de autofiltro en tablas dinámicas que existe en Excel 2007.

Seleccionamos la celda adyacente al borde derecho de la tabla dinámica en la fila de los encabezamientos (en nuestro ejemplo C4) y aplicamos Autofiltro con el menú Datos-Filtro-Autofiltro. El resultado es que el Autofiltro es aplicado a las columnas de la tabla dinámica

Ahora disponemos de la funcionalidad "Personalizar", con la cual podemos aplicar el filtro con criterios personalizados

Extraer las iniciales de un nombre con Excel

Uno de mis lectores me consulta cómo extraer las iniciales de un nombre. En la columna A tenemos una lista de nombres (empleados de una empresa, invitados a un acontecimiento, etc.) y en la columna B queremos obtener las iniciales

En esta nota presentaré dos soluciones, una basada en funciones y la segunda con una UDF (función definida por el usuario).

Solución con funciones

Empecemos por el final: la fórmulas es (respirar hondo) ésta

=ELEGIR(LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))+1,IZQUIERDA(A2,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",3))+1,1))

¿Cómo funciona esta fórmula?

La expresión

LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))+1

calcula de cuantas palabras está compuesto el nombre. La primer función LARGO da la cantidad de caracteres en la celda, incluidos los espacios. La segunda función LARGO calcula la cantidad de caracteres sin los espacios. La diferencia entre ambas más 1, es el número de palabras.

De acuerdo a este valor, la función ELEGIR activa la expresión adecuada para extraer las iniciales.

La primer inicial será siempre la primer letra del nombre, lo que logramos con la fórmula IZQUIERDA(A2,1) . Este es el primer caso de la función ELEGIR.

Para encontrar la segunda inicial tenemos que encontrar donde comienza la segunda palabra. El truco aquí es reemplazar el primer espacio con un carácter especial (por ejemplo #). Para sustituir el primer espacio con el carácter # usamos

SUSTITUIR(A2," ","#",1)

Para hallar donde se encuentra este carácter en el nombre usamos

HALLAR("#",SUSTITUIR(A2," ","#",1))+1

Y finalmente, para extraer la inicial usamos

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)

Ahora tenemos que concatenarlo con la primer inicial lo que hacemos con el operador &

IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)

Para el caso que el nombre tenga tres palabras usamos la misma fórmula concatenando a la anterior la expresión

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)

Quedando

IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)

Para el caso de cuatro palabras en el nombre concatenamos a la expresión anterior

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",3))+1,1)

Esta fórmula tiene algunas serias limitaciones:

• Puede extraer iniciales para nombres de hasta cuatro palabras (si bien podemos agregar otras expresiones y adaptarla para cinco o más palabras en el nombre)

• En caso de nombres que incluyen "de", la función falla ya que no distingue entre minúsculas y mayúsculas

• Otro problema surge si entre las palabras del nombre hay más de un espacio.

Una función definida por el usuario nos proporciona una solución más flexible (y elegante, en mi opinión).

Solución con una UDF

En un módulo común del editor de Vba (preferentemente en el Personal.xls) ponemos este código



Option Explicit

Function Iniciales(strText As String)
    Dim strLen As Long, iChr As Long
    Dim strTemp As String

    strLen = Len(strText)

    For iChr = 1 To strLen
        If Asc(Mid(strText, iChr, 1)) >= 65 And Asc(Mid(strText, iChr, 1))<= 90 Then
            strTemp = strTemp & Mid(strText, iChr, 1)
        End If
    Next iChr

    Iniciales = strTemp
    
End Function

Este código empieza por contar cuantos caracteres hay en la celda que contiene el nombre. Luego evalúa cada carácter y determina si es una letra mayúscula (Asc>=65 o Asc<=90). Si es mayúscula agrega el carácter a la variable strTemp. Al terminar la rutina, la función recibe el valor de strTemp.

Activar una macro desde un cuadro combinado (ComboBox)

En la nota anterior vimos como activar una macro a partir de la elección en una lista desplegable. Esta lista fue creada con validación de datos.

Podemos también crear listas desplegables usando el control cuadro combinado (ComboBox). Pero en ese caso tendremos que usar técnicas un poco distintas de las que mostramos ayer.

Empecemos por señalar que hay dos tipos de controles: los de la barra de formularios y los ActiveX (tema que ya fue tratado en este blog). Los controles de la barra de formularios son fáciles de usar pero no pueden ser programados. Los ActiveX al poder programarlos son más flexibles, pero requieren un poco más de conocimientos.

Siguiendo con el ejemplo de la nota anterior, veremos cómo implementarlo con ambos tipos de controles.


Cuadro combinado de la barra de formularios.

Empezamos por crear las tres macros en un módulo común del editor de Vba

Sub dia()
    MsgBox "Buenos dias"
End Sub
Sub tarde()
    MsgBox "Buenas tardes"
End Sub

Sub noche()
    MsgBox "Buenas noches"
End Sub

Luego creamos una lista con los nombres de las macros y le asignamos un nombre (en este caso usamos el cuadro de nombres para crearlo)

El próximo paso es pegar el cuadro combinado y definir sus propiedades. En la pestaña Programador

Una vez creado abrimos el menú de propiedades del control apretando el botón derecho del mouse.

En el rango de entrada ponemos el nombre que se refiere al rango con la lista de macros y en la casilla de vincular con celda ponemos alguna celda, no necesariamente en la misma hoja y no necesariamente visible

Ahora podemos ver que al elegir la opción "dia", la celda vinculada recibe el valor 1 (ya que "dia" es el primer elemento de la lista). Si elegimos "tarde" la celda vinculada recibe el valor 2.

El paso siguiente es crear una macro lo que haremos abriendo el menú contextual del control (clic con el botón derecho) y eligiendo la opción Asignar macro y apretando el botón Nuevo

En el editor de Vba veremos que Excel ha creado una macro, sin código en esta etapa

En esa macro ponemos este código

Sub Listadesplegable1_AlCambiar()
    Dim intCaseSel As Integer

    intCaseSel = Range("C8")

    Select Case intCaseSel
        Case 1: Call dia
        Case 2: Call tarde
        Case 3: Call noche
    End Select
      
    
End Sub


Cada vez que un valor del cuadro combinado es elegido, la instrucción Select activa la macro correspondiente según el valor de la celda vinculada al control.


Cuadro combinado ActiveX

Empezamos seleccionando el cuadro combinado de los controles ActiveX

y pegándolo en algún lugar de la hoja. En el momento en que terminamos de pegar el control, la hoja pasa al modo "Diseño", lo que nos permite definir las propiedades del control. Abrimos el menú de propiedades (no el de formato!) y definimos el rango de los valores de la lista (ListFillRange)

En el próximo paso abrimos el menú Ver código. Excel crea un evento Change para el control,

donde ponemos este código

Private Sub ComboBox1_Change()
    Application.Run ComboBox1.Value
End Sub

Nótese que el código está en el módulo de la hoja que contiene el control. El evento usa el método Run con el valor elegido en el cuadro combinado (ComboBox1.Value) para activar la macro adecuada.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Activar una macro desde una lista desplegable

En este blog hemos tratado extensivamente el tema de las listas desplegables. Pero siempre queda un detalle más a tratar, como la consulta de un lector sobre cómo accionar una macro a partir de la elección efectuada en una lista desplegable.

La idea es tener una lista desplegable en una celda creada con validación de datos de la cual podemos elegir tres valores. Según la elección, la macro correspondiente es accionada.

Vamos a ejemplificarlo con un modelo absolutamente inocuo, pero útil para los propósitos de la explicación. Programamos tres macros: "dia", "tarde" y "noche". Cuando accionamos la macro "dia" aparece en pantalla el mensaje "Buenos días"; cuando accionamos la macro "tarde", aparece en pantalla el mensaje "Buenas tardes". La tercer opción la dejo librada a la imaginación de mis perspicaces lectores.



En este modelo usamos los siguientes elementos:

1. una lista desplegable con los nombres de las macros; la lista la creamos con validación de datos-lista
2. tres macros: "dia", "tarde" y "noche"
3. un evento de tipo Worksheet_Change que ponemos en el módulo de la hoja correspondiente (en nuestro caso la hoja donde está la lista deslegable)-

Los pasos:

• Creamos la lista desplegable

• Creamos las macros

Nótese que las macros van en un módulo común del editor de Vba.

• Creamos el evento

El código del evento

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strToCall As String
 
    strToCall = Range("C2").Value
 
    On Error Resume Next
    If Target.Address = "$C$2" Then Application.Run strToCall
    On Error GoTo 0
 
End Sub

debe ir en el módulo de la hoja. La función del evento es accionar la macro elegida cuando cambia el valor de la celda C2 que contiene la lista desplegable.
La instrucción On Error evita que la macro se detenga con un error cuando el usuario borra el valor de la celda C2 y ésta queda vacía.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Definir el rango a imprimir según valor en celda

El lector Boris me consulta:

En una celda con datos tipo lista tengo 2 opciones: Factura, Boleta. ¿Es posible hacer una formula con la función SI, para definir el área de impresión, dependiendo de esas dos opciones?

Una vez más aclaremos que las funciones sólo saben hacer cálculos; no podemos hacer cambios estructurales en la hoja o el cuaderno con funciones.

Sin embargo sí podemos definir el rango del área de impresión basándonos en el valor de una celda. Lo haremos usando la técnica que mostré en la nota sobre área de impresión dinámica en Excel sin macros.

El truco consiste en usar nombres. Supongamos que definimos dos rango a imprimir: Area_1 y Area_2

Lo que hemos llamado Area_1 o Area_2 en el ejemplo puede ser factura y recibo o escenario 1 y escenario 2, etc.

Definimos cada uno de los rangos con nombres

El próximo paso es definir una lista desplegable sencilla con validación de datos y ponerla en una celda donde el usuario pueda elegir el rango a imprimir

El último paso es modificar la definición del nombre Area_de_impresion . Esto lo haremos con el asistente de nombres. En la definición del nombre predefinido "Area_de_impresión" ponemos la fórmula

=INDIRECTO(Hoja1!$C$2)

Si al abrir el Administrador de nombres "Área _de_impresión" no aparece, podemos crearlo definiendo un rango arbitrario en "Área de impresión" del menú Configurar página.

La fórmula =INDIRECTO(Hoja1!$C$2) traduce el texto que aparece en la celda C2, donde hemos puesto la lista desplegable, por el rango definido por el nombre que coincide con el valor de la celda.

Este video muestra el funcionamiento

Impedir ingreso de fórmulas en celdas de Excel

Excel viene provisto con un mecanismo para controlar el tipo de datos permitidos en un rango: Validación de datos. La herramienta controla el tipo de datos ingresado, es decir, constantes (números, fechas, texto), pero no evitará que el usuario ingrese una fórmula en el rango validado.

Este problema se me presentó cuando estaba desarrollando un formulario para ingresar datos de gastos

Este tipo de formularios basados en hojas de Excel requieren poco esfuerzo para construirlos. Los valores permitidos para cada uno de los campos están controlados con Validación de datos; los botones en la parte inferior permiten pasar los datos a otra hoja que hace las veces de base de datos; las filas y columnas innecesarias están simplemente ocultas.

El problema es que validación de datos no impide que una fórmula sea ingresada en las celdas de la columna Monto

Para evitar esto tenemos que usar un evento. En el módulo de la hoja correspondiente ponemos este código

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Union(Target, Range("rngMonto")).Address = Range("rngMonto").Address Then
        If Target.HasFormula Then
            Target.ClearContents
            MsgBox "Solo valores, no formulas!"
        End If
    End If
   
End Sub

Al introducir un valor en el rango Monto, se dispara el código que evalúa si se trata de una fórmula con la propiedad HasFormula. En caso positivo el contenido de la celda (target) es borrado y aparece el mensaje informando que no está permitido introducir fórmulas.

Este video muestra el funcionamiento de la validación

Mostrar los criterios de Autofiltro en una celda

Una de las funcionalidades más populares de Excel es el Autofiltro. Con esta herramienta podemos filtrar una tabla de acuerdo criterios lógicos aplicados a los valores de una o más columnas de la tabla.
Un lector me consulta cómo se puede hacer para que el criterio aplicado aparezca en una celda por encima del encabezamiento de la columna. Por ejemplo, partiendo de esta lista

ver en las celdas correspondientes de fila 2 los criterios aplicados para filtrar la lista

Como puede verse, estamos usando una función definida por el usuario (UDF).

Esta función fue desarrollada por el guru de Excel Stephen Bullen.

Para poder usar esta función tenemos que poner este código en un módulo común del editor de Vba, preferentemente en el Personal.xlsb (Personal.xls en Excel 97-2003)

Function FilterCriteria(Rng As Range) As String
    'By Stephen Bullen
    Dim Filter As String
   
    Application.Volatile True
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " y " & .Criteria2
                Case xlOr
                    Filter = Filter & " o " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCriteria = Filter
End Function

Esta función utiliza un único argumento, "Rng", que es cualquier celda de la columna sobre se aplica el Autofiltro.

Hay que tener en cuenta que esta función fue desarrollada antes de la aparición de Excel 2007. Por ejemplo, si elegimos más de dos criterios en una misma columna, por ejemplo ver las zonas Norte, Sur y Oeste, la función no podrá mostrar el criterio aplicado

Tip: en Excel 2007, al apuntar con el mouse al icono del autofiltro podemos ver una ventanilla que nos muestra los criterios aplicados (como en la imagen de arriba).

Mostrar y ocultar series en gráficos de Excel dinámicamente

¿Cómo podemos hacer para mostrar u ocultar series de datos en un gráfico de Excel dinámicamente? Con controles de la barra de formularios, que son sencillos de usar y no requieren programación.
Por ejemplo, supongamos este gráfico

Nuestro objetivo es añadir un elemento que nos permita con un clic mostrar u ocultar cada una de las series.

Veamos los pasos a dar:

Agregamos tres casillas de verificación de la barra de formularios, una para cada serie

Reemplazamos el texto de cada casilla por el nombre de la serie.

Definimos la celda vinculada al control

En este caso he elegido una celda visible, para mostrar el funcionamiento del control. En una implementación real, usaremos una celda oculta.

Hacemos lo mismo con los restantes controles.

Cuando la casilla está activada, el valor de la celda vinculada pasa a ser VERDADERO; cuando no lo está el valor será FALSO. Vamos a aprovechar estos valores en nuestro mecanismo.

El próximo paso consiste en crear cuatro nombres definidos, uno para cada serie y uno para los valores de las categorías (el eje de las X, los meses en nuestro ejemplo).

Para los valores del eje de las X

Mes=dinamico!$B$3:$B$14

Para las series

Ventas =SI(dinamico!$G$20,dinamico!$C$3:$C$14,dinamico!$F$3:$F$14)
Costos=SI(dinamico!$H$20,dinamico!$D$3:$D$14,dinamico!$F$3:$F$14)
Ganancia=SI(dinamico!$I$20,dinamico!$E$3:$E$14,dinamico!$F$2:$F$14)

Como puede verse, estos nombres contienen fórmulas condicionales. Si el valor de la casilla de verificación es VERDADERO, la condición se cumple y el rango es el definido en la tabla de datos; si el valor es FALSO, el rango elegido es F3:F14 que no contiene ningún valor (y por lo tanto no tiene representación en el gráfico).

Ahora tenemos que reemplazar los rangos relevantes en la función SERIES de cada una de las series con los nombres definidos. Esto puede hacerse de varias maneras, por ejemplo:

Seleccionamos la serie VENTAS para que en la barra de las fórmulas aparezca la fórmula SERIES

Reemplazamos los rangos relevantes por los nombres tomando en cuenta no eliminar el signo de admiración (!) que aparece a la izquierda del rango

Apretamos Enter. En lugar del nombre de la hoja aparecerá ahora el nombre del cuaderno

También podemos usar el formulario Modificar Serie de la opción Seleccionar datos

La ventaja de esta técnica es que podemos usar Pegar Nombres (F3) en lugar de tener que escribirlos manualmente. La otra ventaja es que podemos reemplazar el rango de la categorías en el formulario de Editar Rótulos de Eje, sin necesidad de tener que reemplazarlo en la fórmula SERIES de cada serie.

Finalmente, ocultamos las celdas vinculadas, agregamos un cuadro de grupo para agrupar los controles (sólo por cuestiones estéticas) y ya hemos completado nuestro modelo



Descargar el ejemplo.

Calcular con Excel horas trabajadas con intervalos

Una consulta frecuente es cómo calcular con Excel las horas trabajadas en un lapso determinado, tomando en cuenta sólo los días hábiles y las horas de trabajo.

Por ejemplo, cuantas horas de trabajo hay desde el 29/09/2010 hasta el 01/10/2010, tomando en cuenta que la jornada de trabajo es de 08:00 a 17:00.

Excel nos permite hacer el cálculo con relativa facilidad si tomamos en cuenta ciertos detalles.
En nuestro ejemplo, el total trabajado es 90 horas que calculamos con esta fórmula

=DIAS.LAB(B1,B2)*(B4-B3)

En nuestra fórmula usamos DIAS.LAB para calcular los días hábiles entre la fecha inicial y la final. DIAS.LAB no toma en cuenta los sábados y domingos. Las horas trabajadas cada día las calculamos fácilmente como la diferencia entre la hora de cierre y la de comienzo.

Un detalle importante es que la celda que muestra el total de horas (B5) debe tener el formato [hh]:mm

Una situación distinta es cuando el turno de trabajo concluye al día siguiente. Por ejemplo, un turno que empieza a las 22:00 y termina a las 06:00

Recordemos que Excel no puede calcular diferencias de horas negativas.

Modificamos nuestra fórmula para que también pueda calcular las horas en casos como ese

=DIAS.LAB(B1,B2)*RESIDUO(B4-B3,1)

En lugar de la fórmula con RESIDUO, podemos usar también

=DIAS.LAB(B1,B2)*(B4-B3+(B4<B3))

 En caso de querer hacer el cálculo complementario, horas trabajadas descontando las horas de descanso, modificamos levemente nuestra fórmula =DIAS.LAB(B1,B2)*(1-RESIDUO(B4-B3,1))

En este caso, el trabajo se interrumpe de 22:00 a las 06:00 del día siguiente, lo que nos deja un día de trabajo de 16 horas que multiplicado por 10 días laborales nos da 160 horas.

Diseño clásico de tablas dinámicas en Excel 2007

En relación a la nota anterior sobre el tema, un lector me consultaba si era posible cambiar el diseño por defecto de las tablas dinámicas en Excel 2007 de manera que se abrieran con el diseño "clásico" (Excel 2003) y que también se pudieran arrastrar los campos, como era posible en el Excel Clásico.

En la nota anterior pasé por alto un detalle importante que da respuesta a las dos inquietudes de mi lector.

Al crear una tabla dinámica en Excel 2007, el diseño por defecto es el siguiente

Para cambiar el diseño al del Excel Clásico, en lugar de usar los estilos de tablas dinámicas como mostramos en la nota anterior, podemos usar el formulario Opciones de Tablas Dinámicas

En la pestaña "Mostrar" marcamos la opción "Diseño de tabla dinámica clásica". Esta opción no sólo cambia el diseño de la tabla sino que también permite arrastrar los campos, incluidos los campos de página, tal como lo hacíamos en las versiones anteriores de Excel. Una vez activada esta opción la tabla se verá así

Nótese que ahora los rótulos de los campos de columnas son visibles y es posible arrastrarlos. Lo mismo con los campos de página.

Este video muestra el proceso



Otro "tip" para los nostálgicos del Excel Clásico. En Excel 2007 se puede activar el "viejo" asistente de tablas dinámicas apretando el atajo de teclado Alt+T+B.

Tablas dinámicas – cambiar el diseño de Excel 2007 a Excel Clásico

Quienes emigran al nuevo Excel (2007/10) del Excel Clásico (97-2003) deben adaptarse a muchos cambios. Uno de ellos es en el diseño por defecto de las tablas dinámicas.

Partiendo de la misma base de datos obtenemos estos resultados

en Excel Clásico

en Excel 2007

Además de algunas diferencias estéticas, saltan a la vista dos detalles:

• los botones a la izquierda de los rótulos de fila en la versión 2007 que no están presentes en las versiones anteriores
• en Excel 2007 los totales de de cada rótulo de fila están en la fila inmediata superior al detalle del los valores, mientras que en Excel Clásico están al final.

A muchos usuarios les cuesta, o no quieren, acostumbrarse a este nuevo diseño. Si queremos obtener las tablas dinámicas en el nuevo Excel con el diseño del Excel Clásico podemos hacer lo siguiente

1 – cambiar el diseño de la tabla al formato tabular



2 – Cambiar el estilo

3 – Quitar los botones +/-

El resultado final