Construir criterios en las funciones de Excel.

Es importante no olvidar nunca los pequeños detalles de nuestras funciones que nos facilitan un poco la vida; es el caso de la contrucción de los criterios en las funciones.
Al hilo de un correo recibido:

...Me gustaría saber cómo puedo hacer para que el criterio de un sumar.si, sea que NO se cumpla algo. Es decir, algo asi: SUMAR.SI(E12:E17;NO"X";D12:D17): que se sumen aquellas celdas que NO tengan una "X"...

Respondiendo en primer lugar a nuestro lector, diré que sí es posible. El criterio de suma que busca nuestro amigo, i.e., el 'NO "X"' equivaldría en lenguaje Excel a '"<>X"'; y por tanto la función de suma condicionada quedaría:
=SUMAR.SI(E12:E17;"<>X";D12:D17)
que indicaría que sumara del rango D12:D17 todos aquellos importes correspondientes a los elementos del rango E12:E17 diferentes de 'X'.
Otra forma hubiera sido:
=SUMA(B2:B6)-SUMAR.SI(A2:A6;"X";B2:B6)
donde, en lugar de calcularlo directamente, lo hacemos por exclusión.
También podríamos haberlo calculado con la función =BDSUMA(base_datos;nombre_campo; criterios) pero necesitaríamos un rango de criterio.

Pero cómo podemos emplear los comodines estándar (* y ?) y los operadores condicionales (<, >, =, etc) de Excel en estos criterios, tanto para valores de texto como para numéricos.
Los más habituales son los operadores condicionales (<, >, =); por ejemplo:
=SUMAR.SI.CONJUNTO($D$2:$D$11;motivo;"Operativa";oper;">=2471346";oper;"<=2471600";recibida;"INSAT*")
visto en el post http://excelforo.blogspot.com/2010/02/sumas-condicionadas-sumarsiconjunto.html; donde veíamos como en la función SUMAR.SI.CONJUNTO acumulabamos importes para cantidades superiores a 2471346 (">=2471346") pero inferiores a 2471600 ("<=2471600").
En el mismo ejemplo, como última condición, vemos el criterio "INSAT*", que habilitaba la suma de aquellas coincidencias de elementos que comenzaban por 'INSAT'.

Seguir leyendo...

Conversión entre INT.EFECTIVO y TASA.NOMINAL.

Con este ejercicio de funciones financieras (INT.EFECTIVO y TASA.NOMINAL hemos visto la forma de relacionar los distintos tipo o tasas de interes existentes; pero vamos a ir un paso más allá.
Sin necesidad de hacer esos cálculos previos, directamente anidando nuestras funciones financieras y combinándolas, obtendremos el equivalente entre distintos tipos efectivos periódicos.
Por comodidad dispondremos de un listado con los periodos más habituales (semestral, cuatrimestral, trimestral y mensual) con el número de períodos de interés compuesto por año.
También añadiremos dos celdas validadas (A3 y A5) con una lista de esos periodos (semestral, cuatrimestral, trimestral y mensual), con una función BUSCARV traeremos el número de periodos a nuestra tabla de conversión de tipos efectivos:

Supongamos que, por ejemplo, queremos conocer cuál es el tipo efectivo mensual equivalente a un 3% efectivo trimestral.
Empleando nuestra plantilla de conversión, añadiremos en la celda C5 la siguiente función anidada:
=TASA.NOMINAL(INT.EFECTIVO(C3*$B$3;$B$3);$B$5)/$B$5
Fijémonos que el valor devuelto corresponde a un tipo nominal dividido por el número de periodos, por tanto es un tipo efectivo periódico, en el ejemplo,mensual; que toma como base de cálculo un interés efectivo [INT.EFECTIVO(C3*$B$3;$B$3)] que obtenemos a partir de nuestro efectivo trimestral convertido en nominal.

haz click en la imagen

Comprobamos como a pesar de la existencia de funciones específicas financieras, seguimos necesitando no perder la perspectivas de las leyes financieras.

Seguir leyendo...

Relación entre INT.EFECTIVO y TASA.NOMINAL.

Hace ya bastante tiempo que no subo ningún post con funciones financieras; aprovecharé la cuestión planteada por una seguidora sobre la forma de obtener tipos de interés efectivo de diferente periodicidad:

...si yo tengo mi TEA mis tasas efectivas anuales y deseo covertirlas mensualmente, bimestralmente o semestralmente (claro puedo usar la formula), pero mas rapido seria con las formulas financieras del excel por ejemplo =INT.EFECTIVO(tasanominal,numperidos); pero si yo kiero pasarlo directo de efectiva a efectiva, no se puede?...

Como siempre es fundamental, y hablando de términos financieros especialmente, tener claro cuáles son las relaciones existentes entre los diferentes conceptos con los que vamos a trabajar. En este caso deberemos saber qué es un interés efectivo anual, o una tasa nominal y cómo se relacionan con los tipos de interés efectivo periódicos (diferente de la anual).
Las fórmulas financieras serían, siendo m el número de periodos en que dividimos el año:

• Para la Tasa nominal:
  j_m = m * i_m


• Para el tipo efectivo:
  (1 + i_m)^m = (1 + i)

También debemos conocer nuestras funciones de Excel a usar:

1. TASA.NOMINAL(tasa efectiva; núm periodos en año)


2. INT.EFECTIVO(tasa nominal; núm periodos en año)

Vemos cómo la una sin la otra no tienen sentido, y que es imprescindible saber la relación entre ambas:
j_m = m * i_m

Veámoslo con un ejemplo. Tenemos un tipo de interés efectivo anual del 10%, y queremos saber cuáles son los diferentes tipos efectivos semestrales, trimestrales, etc; y cómo puedo relacionar directamente entre un bimestral y un cuatrimestral.

Seguir leyendo...

Tabla dinámica: Campos de página personalizados.

Un asiduo del blog me preguntaba la forma de conseguir una tabla, a partir de otras dos con campos iguales, con la peculiaridad que no deseaba obtener un agregado (sumado) de ambas tablas, si no que quería obtener la información de las dos tablas originales en una sólo comparando los valores de ambas:

...necesito una ayuda con una relacion de datos , lo he tratado de hacer con la tablas dinamicas con rangos de consolidacion multiple les explico tengo una base por ejemplo color cantidad ROJO 4 AZUL 5 NEGRO 1 AMARILLO 4 GRIS 3 y otra base de datos color cantidad ROJO 1 AZUL 2 NEGRO 4 AMARILLO 2 GRIS 1 CELESTE 3 NARANJA 4 VERDE 5 y como resultado de las dos bases - que relaciona las cantidades en 2 columnas segun la caracteristica ( color) , la primera la base de datos original y la otra columna de la segunda -base de datos - sin sumar - ROJO 4 1 AZUL 5 2 NEGRO 1 4 AMARILLO 4 2 GRIS 3 1 lo he querido hacer con la consolidacion multiple pero lo suma cuando tienen la misma caracteristica por ejemplo color rojo 5 , y lo que busco es que rojo aparezca 4 y en la columna de a lado 1...

Efectivamente una solución pasa por el empleo de tablas dinámicas, y en concreto para este caso, de los rangos de consolidación múltiples (ver), pero ya que en la cuestión planteada nos encontramos con el problema de tener dos rangos o tablas con iguales cabeceras, esto es, con idénticos nombres de campos, deberemos trabajar con una opción no demasiado conocida como son los Campos de página personalizados.
Veamos nuestras tablas de trabajo:

Nuestro primer paso será llamar al Asistente de tablas dinámicas de la versión Excel 2003, para lo que presionamos al tiempo las teclas Alt+t+b, en cuya primera ventana seleccionaremos la opción Rangos múltiples de consolidación:

En nuestra siguiente etapa, crucial para solucionar nuestro pequeño problema, deberemos marcar, Campos de página personalizados:

Será este Campo de página personalizado quién nos permitirá diferenciar el mismo campo de nuestras tablas origen... como veremos a continuación.
Tras 'Agregar' los distintos rangos
Hoja1!$A$1:$B$6
Hoja1!$D$1:$E$9
deberemos seleccionar ¿Cuántos campos de página deseamos¿, a nosotros nos será suficiente con DOS, campos a los que daremos el nombre 'Q1' y 'Q2' o cualquier otro que queramos:

En el último paso del Asistente indicaremos dónde queremos situar nuestra Tabla dinámica, con lo que nos aparecerá en nuestra Hoja de cálculo la siguiente Tabla con una estructura concreta:

que claro está no es lo que buscamos... aunque estemos muy cerca ya.
Moveremos los campos de página creados al área de rótulos de columnas; por otro lado sacamos fuera de nuestra Tabla dinámica el campo 'Columna':

Por estética lo podemos quitar los 'Subtotales' y los 'Totales generales' y también le cambiaríamos los nombres a los campos... y ya tendríamos lo que buscábamos:

Desde luego esta forma no es la única de lograr nuestro objetivo con los 'Rangos múltiples de consolidación', también seleccionando un único 'Campo de página' sobre el rango de origen de mayor dimensión:

Independientemente que el camino más rápido hubiera sido tan sencillo como cambiar el nombre en la cabecera de rótulos de campo de una de las tablas originales, para luego aplicarles, por ejemplo la herramienta consolidar, o incluso la misma explicada hoy de 'Rangos de consolidación múltiples.

Seguir leyendo...

Comparar valores de tablas con Formato condicional en diferentes hojas.

En un foro en el que suelo acceder a menudo encontré una cuestión sobre formatos condicionales que me pareció curiosa:

...Me gustaría obtener ayuda para solucionar un problema de formatos condicionales. En un mismo documento excel tengo dos tablas en dos hojas diferentes, las celdas son todas de formatos texto. La hoja 1 es la que tiene los datos, es una tabla con cuatro columnas y quiero conseguir que en la tabla de la hoja 2 que es identica per sin datos, cuando yo introduzca un dato en cualquier celda igual a la tabla de la hoja 1 aparezca en verde y cuando no sea correcto aparezca en rojo. He identificado el primer rango de 4 celdas de la hoja 1 con un nombre y la hoja dos con otro nombre, pero aún asi cuando en formato condicional pongo que cuando una sea igual a la otra aparezca en verde, me aparece todo en rojo. He intentado lo mismo con una sola celda pero tampoco me lo identifica. A mi me interesa que cambie de color el rango de por fila o por celda. ¿Debo hacerlo de otra forma?¿Como puedo aplicar luego el formato a toda la tabla sin tener que cambiar de una celda en una el formato condicional? ...

Para responder a esta cuestión usaremos una herramienta ya empleado en repetidas ocasiones en este blog, asignar nombre a rango, así como la función INDICE.

Seguir leyendo...

VBA: Formas de copiar rangos o celdas.

Con asiduidad me llegan preguntas sobre la manera de copiar rangos o celdas de un lugar a otro de nuestros Libros de trabajos empleando macros. Por este motivo expondré con un ejemplo sencillo alguna de las formas más habituales en las que podemos conseguir un copiado de esos rangos de origen a uno de destino.
Para añadir un extra, le incorporaré una condición de pegado que controle que sólo se puede efectuar dicho copiado si algunas celdas no están vacías.

Algunas propiedades que también usaré en este ejemplo son:

• Application.CutCopyMode = False: con la que deshabilitaremos el modo Cortar y Copiar.


• Application.ScreenUpdating = False: desactiva la actualización de pantalla. Realiza todos los procesos sin que tengan un reflejo sobre lo que vemos.


• Application.ScreenUpdating = True: activa dicha actualización de pantalla.

Los códigos VBA de nuestra macro son:

Sub copiados()
Dim HojaOrigen As Worksheet, HojaDestino As Worksheet
  
Set HojaOrigen = Sheets(1)
Set HojaDestino = Sheets(2)
   
'con el If compruebo las condiciones previas a la ejecución del copiado y pegado
If HojaOrigen.Cells(2, 1).Value <> Empty And _
HojaOrigen.Cells(3, 1).Value <> Empty Then
Application.ScreenUpdating = False

'una forma empleando el método .Paste, con dos parámetros incompatibles
'o bien definimos Destination o bien Link
HojaOrigen.Cells(2, 1).Copy
    ActiveSheet.Paste HojaDestino.Range("A2")
'con Link realiza un Pegar vínculo
HojaOrigen.Cells(3, 1).Copy
    HojaDestino.Range("A3").Activate
    ActiveSheet.Paste link:=True
'Realizamos un Pegado especial, en este caso como Pegar valores y Pegar fórmulas
HojaOrigen.Cells(2, 2).Copy
    HojaDestino.Cells(2, 2).PasteSpecial Paste:=xlValues
HojaOrigen.Cells(3, 2).Copy
    HojaDestino.Cells(3, 2).PasteSpecial Paste:=xlFormulas
'Con el método .Copy podemos indicar dónde deseamos nos copie el rango seleccionado
'realiza un copiado exacto (con formatos, fórmulas, etc)
HojaOrigen.Cells(2, 3).Copy Destination:=HojaDestino.Cells(2, 3)
HojaOrigen.Cells(3, 3).Copy Destination:=HojaDestino.Cells(3, 3)
'También podemos vincular valores o fórmulas relacionando diferentes celdas.
HojaDestino.Range("D2").Value = HojaOrigen.Range("D2").Value
HojaDestino.Range("D3").Formula = HojaOrigen.Range("D3").Formula

Application.ScreenUpdating = True
Application.CutCopyMode = False
End If
Set HojaDestino = Nothing
Set HojaOrigen = Nothing
End Sub

Seguir leyendo...

Cómo obtener coincidencias de una tabla cruzada.

Una consulta que me llegó hace algunos días preguntaba sobre la forma en que se podría obtener listados condicionados o filtrados sobre una tabla cruzada:

...DISPONGO DE UNA TABLA CON VARIAS COLUMNAS (45) Y FILAS (266). CADA FILA SE REFIERE A UNA CASA DIFERENTE Y CADA COLUMNA A UN DETERMINADO SERVICIO. LO QUE QUIERO LOGRAR ES DANDO COMO REFERENCIA EL SERVICIO Y SU CONDICION OBTERNER UNA LISTA DE TODAS LAS CASAS QUE CUMPLEN CON ESTAS CONDICIONES. LOS VALORES DENTRO DE TABLA SON REPETIDOS. TE AGREGO UN EJEMPLO RESUMIDO DE LA TABLA . la busqueda la hago en una hoja distinta a la que esta la table con los datos. Por lo que me es importante conseguirlo con formulas...

Se trata entonces, a partir de una tabla cruzada, obtener un listado de elementos que cumplen con dos condiciones. Veamos dicha tabla:

Seguir leyendo...

VBA:Insertar una función con macros.

Un lector enviaba una cuestión sobre la manera de insertar una función en nuestra hoja de cálculo a través de una macro:

...es posible y como puedo cambiar en una formula el valor absoluto de la dirección de una celda por una variable, me explico: Si tengo la formula ActiveCell.Formula = "=SUBTOTAL(9,I3,I500)" , resulta que quiero cambiar en el valor I500 , el 500 por el valor que traiga una variable, (I&variable) si variable tiene el valor de 501, quedaría I501, me funciona en los rangos la siguiente formula Range("A" & variable).Select, pero en la anterior formula no he podido cambiar el 500 por el valor de una variable....

Efectivamente es posible incluir funciones o fórmulas en nuestras hojas de cálculo, a través de una macro, de esta manera tan directa. Lo único que hacemos realmente es determinar que queremos que se escriba en la celda deseada; todo lo que vaya entrecomillado se añadirá simplemente en esa celda elegida.
Si tenemos claro esta clave, será fácil responder a nuestro amigo. La línea de código que soluciona su problema sería:
ActiveCell.Formula = "=SUBTOTAL(9,I3:I" & variable & ")"

Desarrollémoslo con un ejemplo muy sencillo. Disponemos en nuestra hoja de cálculo de un listado de importes en el rango A1:A11, aunque sabemos que éste variará según crezca nuestra base de datos. Queremos conocer en cada momento cuál es la suma del rango vivo con el que trabajamos, en la celda C1.

Seguir leyendo...

La campana de Gauss: función DISTR.NORM

Una lectora planteaba por mail cómo se podía construir una gráfica con la campana de Gauss:

...como hacer la grafica de la campana de gauss o curva normal, ya tengo los datos de las cantidades, creo o tengo la idea q puedo hacer la grafica de la curva normal con las frecuencias o me equivoco?...

Como siempre se hacen necesarios conocimientos probabilísticos para comprender el correcto funcionamiento de la función de Excel que vamos a usar:
=DISTR.NORM(x;media;desv_estándar;acumulado)
Como se nos dice en la Wikipedia, en estadística y probabilidad se llama distribución normal, distribución de Gauss o distribución gaussiana, a una de las distribuciones de probabilidad de variable continua que con más frecuencia aparece en fenómenos reales.
La gráfica de su función de densidad tiene una forma acampanada y es simétrica respecto de un determinado parámetro. Esta curva se conoce como campana de Gauss.
Me centraré en los aspectos prácticos de esta función, basandonos en una serie de datos ya conocidos.
Los argumentos que definen dicha función son:

• x: es el valor cuya distribución desea obtener.


• Media: es la media aritmética de la distribución.


• Desv_estándar: es la desviación estándar de la distribución.


• Acumulado: es un valor lógico que determina la forma de la función. Si el argumento acum es VERDADERO (o 1), la función devuelve la función de distribución acumulada; si es FALSO (ó 0) devuelve la función de masa de probabilidad.

Seguir leyendo...