La Disyunción exclusiva (el O exclusivo) en Excel.

Hablaré en esta ocasión sobre una función que apareció en la última versión de Excel 2013, la función XO llamada disyunción exclusiva o también O exclusivo.
Ésta es una función lógica, por lo que siempre cuesta bastante comprender su funcionamiento, más aún cuando ya existía otra función O (disyunción lógica) parecida pero NO igual.

A modo de resumen creo interesante definir, a grandes rasgos, las diferentes funciones O, XO e Y:
La función Y (conjunción lógica), que devuelve VERDADERO cuando todos los argumentos son VERDADEROS, será suficiente con que solo uno no se cumpla para que nuestra función sea FALSO.
(puedes leer algo más en wikipedia).

La función O (disyunción lógica), que devuelve VERDADERO cuando al menos uno de los argumentos sea VERDADERO, únicamente será FALSO cuando todos los argumentos sean FALSO.
(puedes leer algo más en wikipedia).

Y la función XO (disyunción exclusiva), que devolverá VERDADERO cuando sólo un operando es VERDADERO pero no todos a la vez.
(puedes leer algo más en wikipedia).

Plantadas las bases teóricas, veámoslo sobre un ejemplo:

haz clic en la imagen

Como observamos en el rango D5:D12 hemos aplicado la siguiente fórmula:
=O(A5="norte";B5="rojo")
la disyunción lógica tradicional, obteniendo por tanto VERDADERO en aquellas combinaciones en las que al menos uno de los valores cumple.
En el rango E5:E12 he aplicado la función de Excel 2013 XO:
=XO(A5="norte";B5="rojo")
como era de esperar devuelve VERDADERO cuando se cumple sólo uno de los criterios y FALSO cuando se verifican ambos o ninguno.
En el rango de celdas F5:F12 hemos replicado con las funciones lógicas Y, O y NO la función XO (inexistente en versiones anteriores a 2013). Nuestra primera posibilidad sería construir algo del estilo:
O(Y(A;NO(B));Y(NO(A);B))
en formato Excel y aplicado sobre nuestro ejemplo:
=O(Y(A5="norte";NO(B5="rojo"));Y(NO(A5="norte");B5="rojo"))
obteniendo el mismo resultado que con XO.
Otras forma diferente de llegar a lo mismo, en el rango G5:G12, sería aplicar el algoritmo lógico:
Y(NO(Y(A;B));O(A;B))
en formato Excel:
=Y(NO(Y(A5="norte";B5="rojo"));O(A5="norte";B5="rojo")).

Sin duda un avance importante de Excel en esta versión 2013...

Seguir leyendo...

Excel avanzado en modalidad elearning.

Cuantas veces has necesitado Excel para algún puesto de trabajo o simplemente has intentado elaborar una fórmula y te faltaba 'algo; si es así, necesitas aprender con los mejores: edición de Cursos de Excel y Macros online con tutor personal de Marzo de 2014.

Los cursos de Excel y Macros abiertos para este mes de marzo son:

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Excel Financiero

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de marzo de 2014; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

VBA: Una macro para autocompletar gradualmente intervalos vacíos.

Retomando la entrada anterior del blog (ver, en la que se explicaba cuál era la regla aritmética que emplea Excel para calcular el factor de crecimiento/disminución al emplear la herramienta Series, veremos como realizar esta operación para un número indeterminado de rangos...
Esta explicación da respuesta a la cuestión planteada por un lector:

Como podría hacer si mis valores no son correlativos, subieran y/o bajaran casi de forma aleatoria y el espacio entre ellos también es variable y tuviera que interpolar cogiendo el último valor (orden descendente) y el siguiente. Ejemplo  1 97 2 97 3  4  5 96 6 96 7  8  9 98 10 98 11  12  13  14 98 15 98 El tipo de interpolación es gradual, es decir, debería calcular la diferencia y dividir por el número de celdas, de tal modo que en el ejmplo anterior, de 97 pase a 96.6777, 96.333 y finalmente 96, que es el valor que ya disponemos. Cuando lo hago manual lo hace automáticamente si cojo la ultima celda con valores y la primera de la siguiente tanda...pero claro estamos hablando de miles de datos para cada archivo.

Veamos en la siguiente imagen el planteamiento:

Podemos ver en el rango C3:C25 una disposición de valores y celdas vacías a completar con valores secuenciales determinados por el valor de la última y siguiente celda con dato.
Al lado, en el rango E3:E25 vemos el resultado de ir ejecutando la herramienta Series, manualmente, rango a rango (rangos C5:C7, C7:C9, C11:C14, C15:C20 y C21:C25.

Así añadiremos nuestro código en un módulo de nuestro proyecto VBA:

Sub CompletaSerieGradual()
Dim celda As Object
Dim paso As Double, Inicial As Double, Final As Double
Dim blancos As Long
'generamos una colección
Set unicos = New Collection
Set Rng = Range("C3:C25")

For Each celda In Rng
    'determinamos las celdas en las que hay una ruptura
    'es decir, cuando hay un salto
    If (celda.Value <> "" And celda.Value <> celda.Offset(-1, 0).Value) _
        Or (celda.Value <> "" And celda.Value <> celda.Offset(1, 0).Value) Then
        'cuando encuentre un item repetido, daría un error
        'que salvamos con la instrucción On Error Resume Next
        On Error Resume Next
        'por tanto, nuestra coleccion solo agrega elementos no repetidos
        'y en concreto en qué celda se encuentra el nuevo elemento
        unicos.Add celda.Address, CStr(celda.Address)
        On Error GoTo 0
    End If
Next celda

'Recorremos las celdas que determinan los rangos a analizar
For j = 1 To unicos.Count - 1
    'damos valor de cálculo a nuestras variables
    blancos = Application.WorksheetFunction.CountBlank(Range(unicos(j), Range(unicos(j + 1))))
    Final = Range(unicos(j + 1)).Value
    Inicial = Range(unicos(j)).Value
    'trabajaremos sólo cuando el rango definido tenga celdas vacías intermedias
    If blancos > 0 Then
        paso = (Final - Inicial) / (blancos + 1)
        'aplicamos la herramienta Series con un Step personalizado
        Range(unicos(j), Range(unicos(j + 1))).DataSeries Rowcol:=xlColumns, _
            Type:=xlLinear, Date:=xlDay, Step:=paso, Trend:=False
    End If
Next j

End Sub

Tras ejecutar nuestra macro podemos ver el resultado...

Seguir leyendo...

Completar Series con principio y fin.

Hoy, como adelanto a una entrada próxima, explicaré cómo podemos emplear la herramienta Series para completar de manera gradual celdas entre dos 'puntos'.
Se trata en definitiva de conocer cuál es la regla que aplica Excel para autorellenar valores entre dos celdas de un rango seleccionado.
Supongamos dos celdas B2 y B8 con valores 95 y 90 respectivamente, y pretendemos completar de manera gradual las celdas vacías entre éstas. Para tal fin nos aprovecharemos de la herramienta Series,a la que podemos acceder desde la ficha Inicio > grupo Modificar > botón Rellenar > opción Series.
Normalmente estamos a emplear esta herramienta para dándole un incremente y un fin, nos complete la secuencia o serie.. sin embargo, una utilidad podría ser un autocálculo del factor de crecimiento/disminución necesario para, gradualmente, pasar de un valor A a un valor B.
Puedes leer algo más al respecto en ejemplo1 y ejemplo2
Veamos nuestro ejemplo en la siguiente imagen:

El siguiente paso es, tras seleccionar el rango B2:B8, abrir la herramienta desde la ficha Inicio > grupo Modificar > botón Rellenar > opción Series, lo que abre la ventana siguiente.. donde prestaremos atención en la opción 'Incremento':

prestemos atención en primer lugar a la sección 'Series en' que se ha completado automáticamente en 'Columnas'. Igualmente se ha completado la sección tipo como 'Lineal' (aunque esta opción podríamos modificarla... nos interesa este tipo de incremento, puesto que buscamos un 'movimiento' gradual/lineal).
Por otra parte, lo importante, es que Excel ha calculado cuál debe ser el incremento/disminución lineal para llegar de 95 a 90 (de la celda B2 a la B8) rellenando las cinco celdas vacías intermedias.
Finalmente, en este caso, no se hace necesario indicar cuál es el límite de la serie, puesto que ya queda determinado en la selección del rango.
Tras Aceptar el resultado será:

Vemos que la secuencia cumple la regla autodefinida....

Sólo me queda para terminar reflejar cuál es la regla aritmética que ha aplicado Excel para conseguir llegar de un valor a otro. El asunto es sencillo:
=(Valor final - valor inicial)/ (celdas vacías intermedias +1)
En el ejemplo:
=(90-95)/(5 + 1) = -0.83333

Seguir leyendo...

Romper el eje de categorías (eje X) de un gráfico de Excel.

Un año atrás expuse una forma de engañar a Excel y conseguir que un gráfico mostrara un eje vertical (eje Y) partido o con dos escalas diferentes (ver)... En la entrada de hoy, respondiendo la consulta de una lectora, haremos un ejercicio similar pero sobre el eje horizontal, de categorías o eje X:

...necesito realizar una gráfica donde se parta el eje horizontal en dos escalas, similar al ejemplo pero invertido. El eje horizontal (X) inicia con valores de concentración muy bajos (7, 15, 30, 60, 120, 240) que se van duplicando y llego a valores muy altos (>3000), pero lo que me interesa que se vea en el gráfico son los valores bajos donde se describe una curva, ya que después de 1000 la curva se hace constante en una linea horizontal. Los valores del eje vertical (Y) son porcentajes que va desde el 0 al 100%....

En definitiva conseguiremos mostrar información sesgada sobre el eje horizontal.

Veamos los datos inventados:

En el ejemplo he dispuesto pocos valores, por lo que realmente el gráfico directo obtenido muestra de una manera correcta y visual la correlación entre Valores de concentración y sus porcentajes de medición... pero si trabajáramos con más datos sería un incordio ver demasiados datos.
Es importante remarcar la situación que el dato porcentual de concentración se repite (como indicaba la lectora) a partir de un valor dado, para nuestro ejemplo por encima de 250 el porcentaje será siempre igual al 49%.

El objetivo es tener un gráfico que muestre los valores más bajos (inferiores a 250) de manera individualizada y los superiores a 250 escalados de 1000 en 1000:

Nuestro trabajo será sencillo, lo primero construiremos una columna auxiliar al lado de nuestros datos que represente la manera en que queremos visualizar nuestras etiquetas del eje horizontal, en nuestro ejemplo será:
=SI(A2<250;A2;SI(A2<2000;1000;SI(A2<3000;2000;">3000")))

Seguir leyendo...

VBA: Repetir una macro cada x tiempo.

Hace algún tiempo publiqué un par de entradas donde aplicabamos el método .OnTime, en una de ellas mostrábamos lo más esencial de este método (ejemplo1) y en el segundo algo más elaborado, donde completábamos un cronómetro (ejemplo2).
En la entrada de hoy, dando respuesta a un lector, mostraremos algo interesante (parecido al ejemplo del cronómetro), y es la forma en cómo poder repetir con una carencia de tiempo definida una macro o rutina.
La idea entonces es clara, queremos automatizar la repetición controlada de una macro cada x segundos, minutos u horas. Para ello crearemos tres procesos Sub:
1- macro que inicie todo el proceso, y que además controle el final de las repeticiones
2- macro que de por finalizado el método .OnTime
3- por supuesto, nuestra macro principal, la que queremos se ejecute cada x tiempo.

Estas son nuestras macros que añadiremos nuestro código asociándolo a un Módulo; para ello accederemos a la ventana de código del explorador del editor de VBA, donde insertaremos el siguiente código VBA:

'declaración de variables para todo el módulo
Dim tiempo As Date
Dim contador As Integer

Sub IniciaOnTime()
'controla la repetición y la cadencia
Dim allea As Integer
'repetición cada 2 segundos
tiempo = Now + TimeSerial(0, 0, 2)
'llama a esta misma macro en el tiempo estipulado
Application.OnTime tiempo, "IniciaOnTime"
'controlamos el final de la repetición
contador = contador + 1
'mientras el contador sea menor a 6 ejecutamos la rutina principal
'cuando lleguemos a 6 interrumpimos la repetición
If contador < 6 Then
    Run "MacroPrincipal"
Else
    Run "CancelaOnTime"
End If
End Sub

Sub CancelaOnTime()
'ejecuta el final de la repetición y reinicia el contador
    Application.OnTime tiempo, "IniciaOnTime", , False
    contador = 0
End Sub

Sub MacroPrincipal()
'el código de la rutina a repetir
'cualquier procedimiento....
With Range("B2")
    .Value = contador & " - " & Now
    Randomize
    allea = Int(20 * Rnd)
    If allea < 5 Then
        .Interior.Color = vbRed
        .Font.Color = vbYellow
    End If
    If allea >= 5 And allea < 10 Then
        .Interior.Color = vbGreen
        .Font.Color = vbBlack
    End If
    If allea >= 10 And allea < 20 Then
        .Interior.Color = vbBlue
        .Font.Color = vbWhite
    End If
End With
End Sub

Algo fundamental es incluir algún control que finalice las reiteraciones, ya que de lo contrario nunca finalizará y tendremos que pararlo manualmente entrnado en el editor. En el ejemplo el control se ha realizado con un 'contador' que ejecuta y para el método .Ontime.. cuando contador llegue a 6 se lanza la macro CancelaOntime. Otra manera, si nos interesa que la repetición esté operativa mientras el libro esté abierto es asociar el inicio y fin a un evento WorkBook_Open y otro WorkBook_Close. Este sería el resultado visible:

Seguir leyendo...

Etiquetas de datos personalizadas en un gráfico Excel.

En un post anterior del blog mostré una forma de personalizar las etiquetas de datos de un gráfico mediante el uso de una sencilla macro (ver).
Hoy explicaré una forma, sin macros, de engañar a Excel y personalizar nuestras etiquetas de datos.
Este desarrollo lo encontré en un grupo de Excel al que pertenezco en Linkedin y que mostró el miembro Mario Mosquera.
Partiremos de los siguientes datos que representan un listado de comerciales con sus ventas.. y con una fórmula anexa que personaliza unos comentarios (que posteriormente añadiremos al gráfico), aunque unos textos manuales servirán igualmente.

La primera parte del trabajo ha sido sencilla, simplemente ha bastado señalar el rango de datos A1:B8 e insertar un gráfico de columnas.

La segunda parte consistirá en añadir una segunda serie de datos. Lo que haremos accediendo a la ventana de Seleccionar datos..:

Tras lo cual indicaremos dónde está el rango con los datos a incluir. Aquí seleccionaremos, para nuestro ejemplo, el rango B2:B8 (que contiene los valores):

El siguiente paso es seleccionar esta nueva serie (sólo ésta) y Cambiarle el tipo de gráfico a un de tipo Dispersión (XY):

Seguir leyendo...

Cambiar el Origen en un Gráfico dinámico.

Como sabemos un Gráfico dinámico está asociado a una Tabla dinámica, y a su vez la tabla dinámica a un Origen de datos. Modificar el Origen de datos del que se alimenta una tabla dinámica es simple, basta acceder a la ventana diálogo de Seleccionar datos y cambiar el rango o tabla origen.
¿Pero qué pasa si lo que queremos es cambiar el vínculo del gráfico dinámico respecto de su tabla dinámica asociada??... si alguna vez lo has intentado habrás comprobado como la posibilidad de modificar el origen está deshabilitada.
En la entrada de hoy veremos un par de soluciones 'alternativas' para poder cambiar la relación de un gráfico dinámico respecto de una u otra tabla dinámica.

Y es que quizá alguna vez hayas necesitado, una vez creada Tabla y Gráfico dinámico, copiar y pegar ambas para luego redefinir el diseño de la Tabla y Gráfico, manteniendo el formato, pero adecuándolo a una nueva situación... comprobando como la copia realizada del gráfico queda vinculada a la tabla dinámica original, y no a la copiada.

Veamos el proceso completo sobre un ejemplo.
Partiremos de un origen de datos llamado 'TblOrigenDatos', sobre el que hemos construido una Tabla dinámica que he llamado 'TDOriginal-1', y sobre este un Gráfico dinámico. Podemos ver todo esto en la imagen siguiente:

haz clic en la imagen

Si con el gráfico seleccionado intentamos modificar el origen presionando Seleccionar datos veremos cómo la acción de cambio está deshabilitada:

Continuemos con nuestra hipótesis. Supongamos que deseamos tener un segundo gráfico dinámico con una estructura diferente (con un diseño de campos distintos). En ese caso podemos seleccionar tabla y gráfico, copiar y pegar en otro lugar, por ejemplo en otra hoja.
Si hacemos esto veremos como la tabla dinámica sigue asociada al origen de datos primero (llamado 'TblOrigenDatos'), que la tabla dinámica recién copiada tiene un nombre distinto (el autonumérico que le corresponda... en nuestro ejemplo 'Tabla dinámica2'), pero el gráfico copiado sigue asociada a la primera tabla dinámica:

Seguir leyendo...