Siguiendo con mi (inintencionada) serie "Funciones Excel poco usadas" hoy veremos la función DELTA().
En realidad no hay mucho que podamos decir sobre esta función. La definición es bien escueta: Prueba si dos números son iguales; si lo son, da como resultado 1, en caso contrario el resultado es 0
Podríamos afirmar que esta función es innecesaria ya que la simplísima fórmula =A1=A2 daría el mismo resultado (en realidad da VERDADERO en lugar de 1 y FALSO en lugar de 0). Sin embargo esta función tiene una ventaja sobre la comparación (la fórmula que mostramos más arriba): permite comparar números con textos que representan números o textos que representan números entre si.
La celda A1 contiene el número 31; la celda A2 contiene el texto 31 (nótese que va precedido de una comilla). La celda C2 contiene la fórmula =DELTA(A1,A2); la celda D2 contiene la fórmula =A1=A2.
Otra situación donde la comparación no nos sirve es cuando los valores a comparar son números como texto pero con espacios antes o después de las cifras (sitaución que suele darse cuando importamos datos de una base de datos a Excel).
En este ejemplo, la celda A2 contiene el texto 31 precedido de tres espacios; la celda A3 contiene 31 seguido por siete espacios. DELTA da 1, es decir, los considera iguales; la fórmula =A2=A3 da FALSO.
Excel tiene dos funciones para extraer la parte entera de un número: TRUNCAR() y ENTERO(). Si bien en principio puede parecer una redundancia, no lo es.
ENTERO() toma como argumento un número y lo redondea al entero inferior más próximo.
TRUNCAR() suprime la parte fraccionaria de un número. A diferencia de la función ENTERO, TRUNCAR tiene un argumento opcional: número de decimales.
Dado que ENTERO redondea hacia abajo y TRUNCAR suprimer la parte decimal, el resultado será el mismo si el argumento es un número positivo, pero distinto si el número es negativo
Como TRUNCAR tiene el argumento opcional "num_decimales" la fórmula =TRUNCAR(64.5894,1) da como resultado 64.5, a diferencia de REDONDEAR(64.5894,1) que dá 64.6.
Otro uso de TRUNCAR y ENTERO es obtener la fecha sin las horas
Las fechas en Excel son números positivos donde la parte entera es la fecha y la parte decimal la hora (como he explicado en esta nota). Por lo tanto ENTERO y TRUNCAR quitan la parte "horaria" (decimal) y dejan la parte "entera" (la fecha).
Podemos crear una serie de días hábiles en Excel usando una funcionalidad poco conocida: Rellenar - Series. Podemos activar este funcionalidad de dos maneras: en la cinta de opciones con Rellenar-Series o con el menú contextual de opciones de autorrelleno.
Supongamos que queremos crear una lista de los días hábiles de agosto del 2015. En la celda A1 ponemos la fecha del primer día hábil del mes de agosto de este año; luego en la cinta de opciones activamos Inicio-Modificar-Rellenar-Series y completamos el formulario de esta manera
Apretamos Aceptar y obtenemos la serie de días hábiles
La fórmula en la columna B es
=ELEGIR(DIASEM(A1;2);"lunes";"martes";"miércoles";"jueves";"viernes";"sábado";"domingo")
Podemos hacer lo mismo con el menú contextual arrastrando con el mouse
La serie de días hábiles resultante depende de las definiciones regionales del sistema.
Excel reconoce si el valor de la celda es una fecha y ofrece crear series de días corridos, hábiles, meses y años.
Podemos considerar dos formas de organizar datos en una hoja de Excel: en forma plana ("flat file") y en forma tabular ("tabular dataset"). Supongamos una de tabla de ventas que muestra las cantidades vendidas de distintos productos por año. Si organizamos la tabla en forma plana tendremos algo así
En cambio si organizamos los datos en forma tabular, tendremos esta tabla
Esta última forma donde todos los valores (datos numéricos) están en una única columna (un solo único campo numérico) es la más eficiente para trabajar con tablas dinámicas.
Pero si tenemos que crear una tabla dinámica a partir de una matriz de datos plana descubriremos que cada campo de valor (las columnas 2010, 2011, etc, en el primer ejemplo) debe ser arrastrado individualmente al área de los datos. Y lo mismo cuenta para el formato de los números. Excel da por defecto formato "General" a los datos numéricos.
Al crear esta tabla dinámica
si queremos cambiar el formato de los valores tendremos que hacerlo campo por campo, cinco veces en nuestro caso. En nuestro auxilio vendrán las macros, como cada vez que tenemos que queremos automatizar una tarea repetitiva.
Si tenemos una única tabla dinámica en la hoja activa podemos usar esta macro
Sub format_NUM_1() Dim strFormatSelected AsString Dim oPTable As PivotTable Dim oPField As PivotField Dim iPTCount AsInteger
iPTCount = ActiveSheet.PivotTables.Count If iPTCount = 0 Then
MsgBox "No se encontraron tablas dinamicas en la hoja", _
vbInformation, _
"Formato numerico" ExitSub EndIf
Set oPTable = ActiveSheet.PivotTables(1)
Application.Dialogs(xlDialogFormatNumber).Show
strFormatSelected = ActiveCell.NumberFormat
For Each oPField In oPTable.DataFields
oPField.NumberFormat = strFormatSelected Next oPField
EndSub
Usamos el método Application.Dialogs(xlDialogFormatNumber).Show para abrir el diálogo de formato de números, capturamos la elección de usuario y con el loop For Each...Next lo aplicamos a todos los campos de datos de la tabla.
Este video muestra el funcionamiento
Si hay más de una tabla dinámica en la hoja activa tendremos que complicar un poco nuestro código
Sub format_NUM_all() Dim strFormatSelected AsString Dim oPTable As PivotTable Dim oPField As PivotField Dim iPTCount AsInteger Dim iX AsInteger
iPTCount = ActiveSheet.PivotTables.Count If iPTCount = 0 Then
MsgBox "No se encontraron tablas dinamicas en la hoja", _
vbInformation, "Formato numerico" ExitSub EndIf
With Application
.ScreenUpdating = False
.Dialogs(xlDialogFormatNumber).Show
strFormatSelected = ActiveCell.NumberFormat
For iX = 1 To iPTCount
For Each oPField In ActiveSheet.PivotTables(iX).DataFields
oPField.NumberFormat = strFormatSelected Next oPField Next iX
.ScreenUpdating = True EndWith
Podemos llevar nuestra macro un paso más adelante y dar al usuario la posibilidad de elegir que tabla dinámica formar de las que se encuentran en la hoja activa.
En este caso tendremos que agregar un Userform con una combobox, que contendrá los nombres de las tablas dinámicas presentes en la hoja activa (un evento crea la lista dinámicamente de acuerdo a la hoja), y una rutina que recibe como variable el nombre de la tabla elegida, abre el diálogo de formato numérico y aplica el formato elegido a la tabla.
Podemos reunir todas las macros en un complemento (Add in) e instalarlo de manera que podamos usarlo en todo cuaderno activo de Excel.
Otra ventaja del complemento es que agregará una pestaña en la cinta de comandos para activar las macros con facilidad.
Después de descargar y guardar el complemento lo instalamos usando el menú Programador-Complementos (en caso de ser necesario usamos el botón Examinar para encontrar la ubicación del complemento)
En caso de recibir una advertencia de seguridad aceptamos la opción "Habilitar contenido".
Este video muestra la instalación y el funcionamiento de la macro
Los códigos pueden verse con el editor de Vba (el complemento no está protegido con contraseña).
Algunas observaciones:
como norma de buena práctica es recomendable reemplazar el nombre por defecto de la tablas dinámica (Tabla dinámica1, Tabla dinámica2, etc.) por algo más significativo
En ciertas ocasiones queremos evitar que el usuario pueda ver todos los datos que alimentan una tabla dinámica. Dado que Excel no tiene un método para "desconectar" la tabla de la base de datos, la forma de hacerlo es con Copiar-Pegar Valores.
Pero este método tiene un inconveniente: Excel sólo copia los datos sin copiar los formatos que hayamos establecido en el reporte.
Por ejemplo, si copiamos con Pegar-Valores-Formato y Origen este informe dinámico al que hemos aplicado uno de los estilos
ek resultado no será el esperado; sólo el formato de los números aparece y ancho de las columnas son copiados; el resto de los formato desaparece.
Empezamos por seleccionar el rango de la tabla (o el rango de las columnas que ocupa), aplicamos Copiar y seleccionamos la primer celda del rango al cual queremos copiar la tabla.
Luego en la cinta Vamos a Inicio y abrimos el Portapapeles usando la flecha que aparece en el angulo inferior derecho
Hacemos un clic en el elemento que deseamos copiar y obtenemos este resultado
Coo podemos ver todos los formatos han sido copiados con la única excepción del ancho de las columnas.
En un post anterior sobre funciones Excel raramente usadas tendría que haber mencionado a la función CELDA(). Por ejemplo, a lo largo y a lo ancho de los 670 posts de esta blog, hay solamente dos menciones a esta función.
Tal como lo insinúa su nombre, esta función da como resultado información sobre una celda y su entorno. La función CELDA() acepta dos argumentos, el tipo de información, que es obligatorio y la celda de referencia, que es opcional.
El uso más obvio de esta función es en fórmulas donde el resultado depende de cierta característica del contenido de la celda o del entorno donde se halla.
Esta función tiene dos características importantes:
es volátil, es decir que es recalculada con cualquier cambio en la hoja. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones.
Si se omite la referencia a una celda la información especificada en el argumento tipo_de_info el resultado muestra la información de la última celda cambiada
Veamos algunos usos posibles.
Determinar si la celda contiene una fecha.
Excel no tiene una función nativa para determinar si el valor de una celda es una fecha (existe la función ESNUMERO pero no la función ESFECHA). Podemos usar la opción "Formato" para evaluar si el contenido es una fecha en lugar de usar la función IsDate de Vba (lo que implica crear una UDF).
Sucede que si la celda contiene una fecha la función CELDA con la opción Formato da como resultado "Dx", donde "x" es un número que identifica el tipo de formato
En este ejemplo, la fórmula =IZQUIERDA(CELDA("formato",B5),1)="D" nos permite determinar que la celda B5 contiene una fecha
SUBTOTALES con columnas ocultas
Podemos hacerlo usando la opción "ancho" que da el ancho de la columna de referencia como número entero (SUBTOTALES realiza cálculos sin tomar en cuenta filas ocultas, pero no funciona con columnas ocultas).
Si la columna esta oculta su ancho es 0, dato que podemor usar en conjunto con SUMAPRODUCTO tal como muestra Ismael Romero en esta nota de su blog Excelforo
El rango C1:H1 contiene la fórmula =CELDA("ancho",C4)<>0; la celda I4 contiene la fórmula =SUMAPRODUCTO(C1:H1*C4:H4).
Al multiplicar las matriz C1:H1 por la matriz C4:H4, SUMAPRODUCTO convierte los valores VERDADERO en 1 y los FALSO en 0.
Hay que tomar en cuenta que ocultar o mostrar filas no dispara el mecanismo de cálculo de Excel y por lo tanto hay que apretar F9 para actualizar el resultado de la fórmula.
Extaer el nombre de la hoja:
Lo hacemos usando la opción "nombrearchivo" que da la ruta de acceso completa de la celda referencia, en forma de texto.
Supongamos que tenemos un cuaderno con una hoja por mes; si el nombre de la hoja es "agosto", esta fórmula dará como resultado "agosto"
En esta fórmula es obligatorio usar la referencia a una celda de la hoja ya que de lo contrario elresultado será el nombre de la hoja donde se produjo el último cambio.
Las tablas dinámicas presentan cuatro áreas a las cuales podemos arrastrar los campos de la base de datos que la alimenta: etiquetas de filas, etiquetas de columna, valores y filtro de informe
Por defecto, los valores de las áreas de etiquetas, columnas y filtro del informe están ordenadas alfabéticamente, de A a Z, y el orden de los valores depende del orden del área de las filas.
Para cambiar el orden por defecto de las eetiquetas de fila y de columna podemos usar el menú de edición de estas áreas
El filtro de informe no cuenta con un menú de edición y por lo tanto no tenemos una forma directa de cambiar el orden de los valores (que por defecto es de A a Z). Pero podemos hacerlo con un pequeño truco.
En nuestro ejemplo tenemos un único campo, País, en el área de filtro de informe
y como puede apreciarse está ordenado de A a Z. Para cambiar este orden reubicamos el campo del filtro de informe en el área de filas
Ahora podemos, por ejemplo, elegir "Ordenar de Z a A", apretar Aceptar y luego volver a ubicar el campo en el área de filtro de informe
con lo cual hemos invertido el orden por defecto del campo.
Ahora supongamos que queremos que los valores del filtro de informe aparezcan ordenados de mayor a menor según el total de ventas de cada país. Para lograrlo empezamos por quitar temporariamente el campo Ciudad y ubicar el campo País en el área de las filas. Abrimos el menú de edicion del campo y en "Más opciones de ordenación" elegimos "Descendente..." y "Por Ventas"
Pero esto no es suficiente. Ahora tenemos que apretar el botón "Más opciones" (en el ángulo inferior izquierdo del formulario, en la imagen está parcialmente oculto) y quitar la marca de la opción "Ordenar automáticamente cada vez que se actualice el informe)
En este post voy a tocar el tema del cálculo con Excel del interés compuesto. Supongo que la mayoría de mis lectores, en particular aquellos que ocupan su tiempo de trabajo con temas financieros, están familiarizados con el tema. Para quien no lo esté digamos, en pocas palabras, que el interés compuesto consiste en tomar en cuenta el interés acumulado en los períodos anteriores al calcular el interés de un período, o citando a Wikipedia
El interés compuesto representa la acumulación de intereses que se han generado en un período determinado por un capital inicial (CI) o principal a una tasa de interés (r) durante (n) periodos de imposición, de modo que los intereses que se obtienen al final de cada período de inversión no se retiran sino que se reinvierten o añaden al capital inicial, es decir, se capitalizan.
Este ejemplo muestra la aplicación de la definición
Por supuesto con Excel podemos ser un poco más sofisticados y hacer el cálculo en una única celda usando la expresión =C2*(1+C3)^C4 o con =C2*POTENCIA(1+C3,C4)
Pero podemos ser aún más sofisticados y usar la función VF (valor futuro) dejando el argumento Pago de la función en blanco
Hasta ahora hemos asumido una tasa de interés constante para todos los períodos. Supongamos que cada año tenemos una tasa de interés distinta. En este caso no podemos usar la función VF. Pero Excel viene provisto con 55 funciones financieras y una de ellas es VF.PLAN que nos permite hacer el cálculo fácilmente, como podemos ver en este ejemplo:
La función VF.PLAN tiene dos argumentos: el capital inicial y un rango que contiene las tasas de interés a tomar en cuenta que Excel usa como matriz para realizar el cálculo
Podemos darle dinamismo a nuestro modelo usando Tablas (como ya saben, soy fanático de las tablas). En nuestro ejemplo creamos la tabla "tblInteres" y la usamos en nuestra fórmula
De esta manera cuando agregamos o quitamos períodos la fórmula se ajusta automáticamente
Ahora surge la pregunta, si tenemos distintas tasas en distintos períodos ¿cómo calculamos la tasa promedio? Esta tasa es conocida como TCAC (Tasa de Crecimiento Anual Compuesto) también conocida como CAGR (en inglés Compound Annual Growth Rate).
Dados:
V(t_0) : valor inicial,
V(t_n) : valor final,
t_n - t_0 : número de años
la expresión para calcular la TCAC (CAGR) es
En nuestro ejemplo calculamos la TCAC con la expresión =(C3/C2)^(1/5)-1
Podemos también usar la función TASA, dejando el argumento Pago en blanco y poniendo el capital inicial como número negativo (o el valor futuro como negativo y el inicial como positivo)
Es una norma de buena práctica evitar el uso de constantes en nuestras fórmulas; por lo tanto en lugar de la constante 5 para el número de períodos usamos la fórmula CONTARA(tblInteres[Período]) quedando =TASA(CONTARA(tblInteres[Período]),,-C2,C3)
Invito a todos mis lectores a visitar dos páginas que acabo de agregar al blog: Recursos Excel de JLD y Tienda JLD Excel. Pueden acceder a las páginas desde los enlaces en el linkbar del blog
En la página Recursos Excel de JLDpodrán ver y descargar algunos de los modelos y manuales que he desarrollado a lo largo de los años de existencia de este blog:
Listas Desplegables
Generador de Facturas
Planificador de Proyectos
La página Tienda JLD Excel les permitirá acceder a herramientas y manuales de otros autores que cuentan con mi recomendación.
Al presente podrán ver y descargar los complementos de Optipe, Data ToolsSuite y Multimail
(estoy adherido al programa de afiliados de Optipe y por lo tanto recibo una comisión por cada venta).
En el futuro seguiré agregando más herramientas y recursos para optimizar el trabajo con Excel.
No me avergüenzo de decir que soy un fanático de las Tablas. Una de las mejores herramientas de Excel, la mejor, tal vez, después de las tablas dinámicas, el Power Query y el PowerPivot.
Una de las mejores características de las tablas es que crean rangos dinámicos en todo objeto que dependa de ellas. Por ejemplo, si creamos un gráfico basado en una tabla cada cambio se reflejará automáticamente en el gráfico
Al crear una tabla Excel le asigna un nombre, por defecto Tabla1, que podemos cambiar para usar algo más significativo. Por ejemplo, rebautizamos a nuestra tabla de ventas con "tblVentas"
También veremos que Excel la incluye en administrador de nombres como un nombre definido que se refiere al rango de la tabla
Esto nos lleva a concluir que podemos crear rangos dinámicos, como aquellos que usamos en listas desplegables, sin necesidad de echar mano a fórmulas con las funciones DESREF o INDICE. Pero para poder usar las tablas o las columnas de una tabla como rangos dinámicos tendremos primero que crear nombres definidos que se refieran a esos rangos.
A los efectos del ejemplo supongamos dos tablas de datos. Una contiene nombres de continentes y la otra contiene una columna por cada continente donde se encuentran los países del continente
A la tabla de los continentes le damos el nombre "Continente"; a la segunda tabla le damos el nombre "Paises". Para poder usar la columna de los continentes en una lista desplegable con validación de datos tenemos que crear un nombre definido que se refiera al rango de la columna
Hemos creado el nombre definido "lstContinente" que se refiere a la tabla Continente usando el lenguaje estructural de las tablas: =Continente[Continente] (en este caso el nombre de la tabla y el de la única columna coinciden).
Ahora para definir la lista desplegable con validación de datos en la celda B2 usamos el nombre definido "lstContinente"
Para crear la lista desplegable dependiente tendremos que referirnos a la columna de la tabla Paises que coincide con el continente elegido en B2. Para eso creamos el nombre definido "PaisSelec" que se refiere a esta fórmula
=INDIRECTO("Paises["&valdat!$B$2&"]")
donde "valdat" es el nombre de la hoja; es decir, creamos una cadena de texto con el operador & que la función INDIRECTO convierte en rango.
Si queremos evitar los espacios en blanco al final de algunas de las listas (el rango se determina según el tamaño de la tabla, no de una columna en particular), tendremos que crear una Tabla para cada continente. En este caso sólo necesitamos crear el nombre definido que se refiere al rango de la tabla de continentes.
La lista desplegable en la celda B2 la creamos como en el caso anterior. Para la validación de datos en la celda B3 usamos la fórmula =INDIRECTO(B2).
También podemos usar esta técnica para poblar comboboxes y listboxes. En este ejemplo creamos un Userform con dos combobox, una para los continentes y el segundo combobox para los países cuyos valores dependerán del continente elegido. Como base vamos a usar el modelo con tablas separadas por continentes.
Creamos el Userform y agregamos dos comboboxes. La lista de valores del primer combobox (el que muestra los continentes) lo definimos directamente en el cuadro de propiedades del objeto
Como puede verse, sencillamente ponemos el nombre definido que se refiere a la tabla de continentes.
La lista de valores del segundo combobox debe depender del valor seleccionado en el combobox de continentes para lo cual debemos definir un evento Change del combo de continentes.
Hacemos un doble clic al combobox de los continentes lo que abre el módulo del userform y agrega, por defecto, el evento Change del objeto donde ponemos este código
Private Sub cbxContinentes_Change()
With Me
.cbxPaises.RowSource = .cbxContinentes.Value
End WithEnd Sub
Ahora podemos probar el funcionamiento del Userform y las cos comboboxes seleccionando el Userform en el editor de VB y apretando F5
Ahora que vemos que nuestro código funciona vamos a mejorarlo agregando una línea para limpiar el valor del combobox de países si el usuario cambia el continente antes de cerrar el Userform
Private Sub cbxContinentes_Change()
With Me.cbxPaises.Value = ""
.cbxPaises.RowSource = .cbxContinentes.Value
End WithEnd Sub
