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).
Sugerencias y ayuda para Excel en español. Ejemplos de funciones y fórmulas, gráficos, automatización con Vba, Power Query y PowerPivot
jueves, agosto 27, 2015
martes, agosto 25, 2015
Crear series de días hábiles en Excel
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.
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
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.
lunes, agosto 24, 2015
Formato numérico de campos de datos en tablas dinámicas
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 As String
Dim oPTable As PivotTable
Dim oPField As PivotField
Dim iPTCount As Integer
iPTCount = ActiveSheet.PivotTables.Count
If iPTCount = 0 Then
MsgBox "No se encontraron tablas dinamicas en la hoja", _
vbInformation, _
"Formato numerico"
Exit Sub
End If
Set oPTable = ActiveSheet.PivotTables(1)
Application.Dialogs(xlDialogFormatNumber).Show
strFormatSelected = ActiveCell.NumberFormat
For Each oPField In oPTable.DataFields
oPField.NumberFormat = strFormatSelected
Next oPField
End Sub
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 As String
Dim oPTable As PivotTable
Dim oPField As PivotField
Dim iPTCount As Integer
Dim iX As Integer
iPTCount = ActiveSheet.PivotTables.Count
If iPTCount = 0 Then
MsgBox "No se encontraron tablas dinamicas en la hoja", _
vbInformation, "Formato numerico"
Exit Sub
End If
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
End With
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.
El complemento se puede descargar sin cargo aquí
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:
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 As String
Dim oPTable As PivotTable
Dim oPField As PivotField
Dim iPTCount As Integer
iPTCount = ActiveSheet.PivotTables.Count
If iPTCount = 0 Then
MsgBox "No se encontraron tablas dinamicas en la hoja", _
vbInformation, _
"Formato numerico"
Exit Sub
End If
Set oPTable = ActiveSheet.PivotTables(1)
Application.Dialogs(xlDialogFormatNumber).Show
strFormatSelected = ActiveCell.NumberFormat
For Each oPField In oPTable.DataFields
oPField.NumberFormat = strFormatSelected
Next oPField
End Sub
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 As String
Dim oPTable As PivotTable
Dim oPField As PivotField
Dim iPTCount As Integer
Dim iX As Integer
iPTCount = ActiveSheet.PivotTables.Count
If iPTCount = 0 Then
MsgBox "No se encontraron tablas dinamicas en la hoja", _
vbInformation, "Formato numerico"
Exit Sub
End If
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
End With
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.
El complemento se puede descargar sin cargo aquí
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
- podemos convertir matrices planas a matrices tabulares usando el Power Query o la opción de consolidación de rangos múltiples en el viejo asistente de tablas dinámicas.
Suscribirse a:
Entradas (Atom)