Ayer apareció por mi oficina Armando, del departamento de planificación. Me llamó la atención que llegara solo porque, por lo general, se lo ve con Raquel, la del departamento de producción (las malas lenguas dicen que...).
- Como seguramente sabés, los datos de uso de las máquinas están en minutos.
- Si, lo se.
- ¿Cómo hago para mostrarlos en días, horas y minutos?
- Poniendo el formato correspondiente. Digamos "dd hh:mm"
- No sabía que existía ese formato, no aparece en la lista.
- Es un formato personalizado.
- Ah!, gracias.
Como era de esperar, Armando apareció de nuevo a los pocos minutos.
- Mirá, tu método no funciona; en lugar de 1 día, 22 horas y 40 minutos, me da este resultado
- ¿Te acordás lo que te explique sobre cómo Excel calcula fechas y horas? Tenes que dividir los 2800 minutos por 1440.
- Pero, ¿por qué 1440?
- Porque tu dato está en minutos, y en un día hay 1440 minutos (60 x 24). Como explicaba en esa nota, que parece ser no leiste, los días en Excel están representados por una serie de números enteros y las horas, minutos y segundos por la parte decimal. Así 1 es un día, una hora es 1/24 (0.466667), un minuto es 1/1440 y un segundo es 1/86400 (24 x 60 x 60). Fijate en esta tabla
- Entendido, gracias. Pero cómo hago para que se entienda que se trata de días/horas/minutos. Si te fijas en la celda D3 de tu tabla parece ser 1 hora, no un día.
- Agregando texto en el formato personalizado; por ejemplo dd "dias" hh "horas y" mm "minutos". Fijate en esta tabla
- ¿Y si quiero que cuando sea un día aparezca "01 día" y no en plural?
- Usando formato condicional con esta regla
El formato condicional se suma al formato de la celda. Si queremos aplicar lo mismo para las horas y los minutos tenemos que crear un formato condicional para cada caso.
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, enero 29, 2015
miércoles, enero 28, 2015
Funcionalidades de Excel en macros
En la nota anterior vimos un ejemplo de las ventajas de usar funcionalidades nativas de Excel en nuestras macros. En ese caso usamos Texto en Columnas para transformar fechas en formato mes/días/año al formato día/mes/año.
Otra funcionalidad nativa de Excel que conviene considerar en nuestras macros es Quitar Duplicados
Supongamos que para nuestro proyecto de Vba (macro) necesitamos un código que elimine los duplicados de una lista como ésta (la lista completa incluye 10774 registros con sólo 9 registros únicos)
Como no es obligatorio inventar la rueda cada vez que escribimos código, hacemos una búsqueda en Google (recomendablemente en inglés, para obtener más resultados).
Probablemente encontraremos códigos ineficientes como éste de VBA Express
Sub DeleteDups()
'VBA Express - Jacob Hilderbrand
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub
que podemos mejorar en algo de esta manera
Sub DeleteDups_modified()
'VBA Express - Jacob Hilderbrand
'modificada por Jorge Dunkelman
Dim x As Long
Dim LastRow As Long
LastRow = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column)).Rows.Count
Debug.Print LastRow
Application.ScreenUpdating = False
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True
End Sub
Public Sub DeleteDuplicateRows()
o códigos profesionalmente desarrollados como éste de Chip Pearson
Public Sub DeleteDuplicateRows()
'origen:http://www.cpearson.com/excel/deleting.htm
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If
V = Rng.Cells(R, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
End If
Next R
EndMacro:
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)
End Sub
Siguiendo la técnica que mostré en la nota anterior podemos generar código que use la funcionalidad Quitar Duplicados. Activamos la grabadora de macros para grabar las acciones de eliminar los duplicados. El código generado es el siguiente
Sub Macro1()
'
ActiveSheet.Range("$A$1:$A$10775").RemoveDuplicates _
Columns:=1, Header:=xlYes
End Sub
Mejoramos este código de la siguiente manera
Sub remove_dups_1()
Selection.RemoveDuplicates Columns:=1, Header:=xlGuess
End Sub
Como podemos ver, un código muy compacto y claro. La pregunta ahora es: ¿cuál es el código que corre más rápido?. En mi máquina los resultados fueron los siguientes:
Otra funcionalidad nativa de Excel que conviene considerar en nuestras macros es Quitar Duplicados
Supongamos que para nuestro proyecto de Vba (macro) necesitamos un código que elimine los duplicados de una lista como ésta (la lista completa incluye 10774 registros con sólo 9 registros únicos)
Como no es obligatorio inventar la rueda cada vez que escribimos código, hacemos una búsqueda en Google (recomendablemente en inglés, para obtener más resultados).
Probablemente encontraremos códigos ineficientes como éste de VBA Express
Sub DeleteDups()
'VBA Express - Jacob Hilderbrand
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub
que podemos mejorar en algo de esta manera
Sub DeleteDups_modified()
'VBA Express - Jacob Hilderbrand
'modificada por Jorge Dunkelman
Dim x As Long
Dim LastRow As Long
LastRow = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column)).Rows.Count
Debug.Print LastRow
Application.ScreenUpdating = False
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True
End Sub
Public Sub DeleteDuplicateRows()
o códigos profesionalmente desarrollados como éste de Chip Pearson
Public Sub DeleteDuplicateRows()
'origen:http://www.cpearson.com/excel/deleting.htm
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If
V = Rng.Cells(R, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
End If
Next R
EndMacro:
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)
End Sub
Siguiendo la técnica que mostré en la nota anterior podemos generar código que use la funcionalidad Quitar Duplicados. Activamos la grabadora de macros para grabar las acciones de eliminar los duplicados. El código generado es el siguiente
Sub Macro1()
'
ActiveSheet.Range("$A$1:$A$10775").RemoveDuplicates _
Columns:=1, Header:=xlYes
End Sub
Mejoramos este código de la siguiente manera
Sub remove_dups_1()
Selection.RemoveDuplicates Columns:=1, Header:=xlGuess
End Sub
Como podemos ver, un código muy compacto y claro. La pregunta ahora es: ¿cuál es el código que corre más rápido?. En mi máquina los resultados fueron los siguientes:
- DeleteDups: 78 segundos
- DeleteDups_modified: 7.5 (mejora como consecuencia de usar Application.ScreenUpdating = True)
- DeleteDuplicateRows: 7.0 segundos
- remove_dups_1: 0.047 segundos
Resumiendo: DeleteDups_modified y DeleteDups son aproximadamente 11 veces más rápidas que la infeciente DeleteDups; pero remove_dups_1, basada en la funcionalidad Remover Duplicados, es casi 150 veces más rápida que DeleteDups_modified y DeleteDups y 1660 veces más rápida que la ineficiente DeleteDups.
Como en la nota anterior concluimos: siempre conviene considerar el uso de funcionalidades nativas de Excel en nuestros códigos.
Como en la nota anterior concluimos: siempre conviene considerar el uso de funcionalidades nativas de Excel en nuestros códigos.
lunes, enero 26, 2015
Tip para escribir macros eficientes en Excel
Hay muchas normas de buenas prácticas y tips para escribir macros eficientes. Una posibilidad raramente mencionada es usar en nuestros códigos métodos incorporados de Excel que podemos grabar con la grabadora de macros.
Un ejemplo puede ser la macro que propuse para importar fechas de un archivo .csv. La macro usa el loop For Each - Next para convertir fechas en formato mes/día/año (como en los Estados Unidos) al formato día/mes/año en uso en la mayoría de los países de habla hispana.
En lugar del código podemos usar la grabadora de macros para usar el método Texto en Columnas (Datos-Texto en Columnas) en nuestro código en lugar del loop For Each - Next. La ventaja inmediata de la grabadora de macros es que nos exime de tener que conocer la sintaxis del método. Además, como veremos más adelante, esta funcionalidad incorporada de Excel es mucho más eficiente.
No nos limitaremos a grabar la macro sino que eliminaremos las partes innecesarias; luego agregaremos variables para que nuestro código sea lo más flexible posible (al contrario del código que resulta de la grabadora de macros).
Supongamos que hemos importado un archivo .csv que contiene 400000 registros de fechas. Como el archivo fue originado en los Estados Unidos, tenemos que cambiar el formato de los datos, tal como explicamos en la nota mencionada.
Después de importar el archivo, activamos la grabadora de macros y usamos Texto en Columnas para transformar las fechas
El código resultante es el siguiente
Eliminamos la primer línea del código, suponiendo que el usuario activará la macro después de haber elegido el rango a convertir. También podemos eliminar las propiedades definidas por defecto, es decir, aquellas que no hemos cambiado (como norma, aquellas donde el valor de la propiedad es False)
Podemos dar un paso más adelante y permitir al usuario definir donde pegar el resultado (en el código de arriba Destination:=Range("A1")); además queremos verificar que el usuario haya elegido un rango que contengo por lo menos dos celdas
Ahora podemos verificar cuál es el código que corre más rápido: el que usa el loop For each - Next, de la nota mencionada, o éste basado en el método incorporado de Excel.
En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), la macro que usa Texto en Columnas tomó 4.3 segundos en convertir las 400 mil fechas. La misma tarea con el loop For Each - Next tomó 180 segundos.
Conclusión: siempre considerar usar los métodos incorporados en Excel en nuestras macros.
Un ejemplo puede ser la macro que propuse para importar fechas de un archivo .csv. La macro usa el loop For Each - Next para convertir fechas en formato mes/día/año (como en los Estados Unidos) al formato día/mes/año en uso en la mayoría de los países de habla hispana.
En lugar del código podemos usar la grabadora de macros para usar el método Texto en Columnas (Datos-Texto en Columnas) en nuestro código en lugar del loop For Each - Next. La ventaja inmediata de la grabadora de macros es que nos exime de tener que conocer la sintaxis del método. Además, como veremos más adelante, esta funcionalidad incorporada de Excel es mucho más eficiente.
No nos limitaremos a grabar la macro sino que eliminaremos las partes innecesarias; luego agregaremos variables para que nuestro código sea lo más flexible posible (al contrario del código que resulta de la grabadora de macros).
Supongamos que hemos importado un archivo .csv que contiene 400000 registros de fechas. Como el archivo fue originado en los Estados Unidos, tenemos que cambiar el formato de los datos, tal como explicamos en la nota mencionada.
Después de importar el archivo, activamos la grabadora de macros y usamos Texto en Columnas para transformar las fechas
El código resultante es el siguiente
Eliminamos la primer línea del código, suponiendo que el usuario activará la macro después de haber elegido el rango a convertir. También podemos eliminar las propiedades definidas por defecto, es decir, aquellas que no hemos cambiado (como norma, aquellas donde el valor de la propiedad es False)
Podemos dar un paso más adelante y permitir al usuario definir donde pegar el resultado (en el código de arriba Destination:=Range("A1")); además queremos verificar que el usuario haya elegido un rango que contengo por lo menos dos celdas
Ahora podemos verificar cuál es el código que corre más rápido: el que usa el loop For each - Next, de la nota mencionada, o éste basado en el método incorporado de Excel.
En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), la macro que usa Texto en Columnas tomó 4.3 segundos en convertir las 400 mil fechas. La misma tarea con el loop For Each - Next tomó 180 segundos.
Conclusión: siempre considerar usar los métodos incorporados en Excel en nuestras macros.
viernes, enero 16, 2015
Fechas de .csv a Excel - otras técnicas
En relación a la nota sobre los problemas que pueden surgir al importar fechas de un archivo texto .csv a Excel, dos lectores me sugieren técnicas que no fueron tratadas en el post. Javi sugiere usar Texto en Columnas (la opción más obvia que ignoré olímpicamente, mea culpa!) y Miguel (Power User en Español) sugiere usar la propiedad "locale" del Power Query.
Ambas técnicas merecen algo más que un comentario al pie del post, así que vamos a mostrar cómo usarlas.
Recordemos que el formato de fechas en los distintos países genera en potencia un problema al importar fechas de un archivo .csv a Excel. Al abrir directamente un archivo .csv, Excel "decide" que tipo de dato irá en cada celda. Todo lo que se vea como número será transformado en número (también si va precedido por uno o más ceros); todo lo que se vea como fecha será transformado en fecha. Todo lo demás será importado como texto.
Dado que el formato de fecha en los Estados Unidos (y en otras áreas del mundo) es mes/día/año mientras que la mayoría de los países hispanoparlantes y en Europa es día/mes/año, las fechas pueden ser importadas incorrectamente. Por ejemplo la fecha 10/06/2014 en los Estados Unidos es el 6 de octubre, mientras que en la Argentina es el diez de junio.
La técnica sugerida por Javi es usar Texto en Columnas y en el tercer paso elegir el formato MDA lo que transformará las fechas en forma correcta. Este video muestra la técnica
La técnica sugerida por Miguel es más avanzada y requiere que tengamos instalado el complemento Power Query (Excel 2010 en adelante).
Entre otras fuentes el Power Query permite importar datos también de archivos .csv. Una vez importados los datos al editor del Power Query podemos determinar el formato de fecha del archivo de origen (la propiedad "locale") para que estas sean interpretadas correctamente. Al cargar los datos a la hoja de Excel estos serán transformados correctamente.
Los datos en el archivo .csv son los siguientes
El proceso con el Power Query puede verse en este video
Ambas técnicas merecen algo más que un comentario al pie del post, así que vamos a mostrar cómo usarlas.
Recordemos que el formato de fechas en los distintos países genera en potencia un problema al importar fechas de un archivo .csv a Excel. Al abrir directamente un archivo .csv, Excel "decide" que tipo de dato irá en cada celda. Todo lo que se vea como número será transformado en número (también si va precedido por uno o más ceros); todo lo que se vea como fecha será transformado en fecha. Todo lo demás será importado como texto.
Dado que el formato de fecha en los Estados Unidos (y en otras áreas del mundo) es mes/día/año mientras que la mayoría de los países hispanoparlantes y en Europa es día/mes/año, las fechas pueden ser importadas incorrectamente. Por ejemplo la fecha 10/06/2014 en los Estados Unidos es el 6 de octubre, mientras que en la Argentina es el diez de junio.
La técnica sugerida por Javi es usar Texto en Columnas y en el tercer paso elegir el formato MDA lo que transformará las fechas en forma correcta. Este video muestra la técnica
La técnica sugerida por Miguel es más avanzada y requiere que tengamos instalado el complemento Power Query (Excel 2010 en adelante).
Entre otras fuentes el Power Query permite importar datos también de archivos .csv. Una vez importados los datos al editor del Power Query podemos determinar el formato de fecha del archivo de origen (la propiedad "locale") para que estas sean interpretadas correctamente. Al cargar los datos a la hoja de Excel estos serán transformados correctamente.
Los datos en el archivo .csv son los siguientes
El proceso con el Power Query puede verse en este video
miércoles, enero 14, 2015
Importar fechas de un archivo .csv a una hoja de Excel
Ya hemos tocado en el pasado los problemas que pueden surgir cuando abrimos en forma directa archivos texto .csv en hojas de Excel. Por "forma directa" me refiero a archivos .csv abiertos con un doble click o usando Abrir. Excel interpreta los datos de acuerdos a ciertas reglas y pueden producir cambios indeseados. Por ejemplo el texto "012345" que representa, digamos, un número de catálogo será transformado en "12345".
El problema es particularmente grave cuando los datos importados son fechas. Supongamos que recibimos un archivo .csv con una lista de fechas que nos envía una empresa de los Estados Unidos. En los Estados Unidos se usa el formato mes/día/año mientras que en la mayoría de los países hispanoparlantes el formato de fecha es día/mes/año. Al abrir el archivo .csv directamente, los valores que Excel no interpreta como fechas de acuerdo a las definiciones regionales serán transformados en texto.Veamos este ejemplo:
Podemos ver que algunas fechas están alineadas a la izquierda y otras a la derecha. Los valores alineados a la derecha han sido importados como fechas (al ser números Excel los alinea a la derecha), mientras que los valores alineados a la izquierda son texto. Esto se debe que Excel no puede interpretar esos valores como fecha siguiendo el formato regional día/mes/año (por ejemplo en la celda A2, donde el número de mes sería 23).
El valor en la celda A3 nos muestra el problema más grave que se puede generar cuando importamos archivos .csv con fechas. La fecha, siguiendo el formato de los Estados Unidos, es el 6 de Octubre pero Excel la ha transformado en el 10 de Junio.
Señalemos que los datos importados deben ser fechas, de manera que podamos realizar operaciones con ellos.
Podemos transformar los textos en fechas usando una fórmula como
=SI(ESTEXTO(A3),VALOR(EXTRAE(A3,4,2)&"/"&IZQUIERDA(A3,2)&"/"&DERECHA(A3,4)),VALOR(TEXTO(A3,"mm/dd/yyyy")))
Podemos ver que el valor de la celda A3 es transformado correctamente por la fórmula en 06/10/2012.
Otra solución es usar una macro para forzar la transformación. La ventaja de la macro consiste en que no debemos crear las fórmulas para cada hoja; podemos guardar la macro en el cuaderno Personal y usarla en cada hoja que necesitemos sin necesidad de cargarla con fórmulas.
El código básico de la macro es
Sub USDdate_to_EURdate()
Dim rngcell As Range
On Error Resume Next
For Each rngcell In Selection
rngcell = CDate(Format(rngcell, "mm/dd/yyyy"))
Next rngcell
On Error GoTo 0
End Sub
Esta macro reemplaza los valores en el rango seleccionado por fechas con formato (dd/mm/yyyy).
Este código más elaborado nos permite elegir el rango donde copiar los resultados
Sub USDdate_to_EURdate_2()
Dim rngcell As Range
Dim rngOrigin As Range, rngDest As Range
Dim iX As Long
'seleccionar el rango a transformar
Set rngOrigin = Application.InputBox(prompt:="Seleccione el rango a transformar", _
Title:="Rango a transformar", _
Type:=8)
'comprobar si el rango elegido es vertical/columna
If rngOrigin.Columns.Count > 1 Then
MsgBox "El rango seleccionado debe contener solo una columna", vbCritical, "Error en la seleccion"
Exit Sub
End If
'seleccionar la primer celda del destino
Set rngDest = Application.InputBox(prompt:="Seleccione la primer celda del rango del destino", _
Title:="Destino", _
Type:=8)
'comprobar que se haya elegido una sola celda
If rngDest.Count > 1 Then
MsgBox "Debe seleccionar solo una celda", vbCritical, "Error en la seleccion"
Exit Sub
End If
Application.ScreenUpdating = False
On Error Resume Next 'en caso de haber celdas vacias o valores no validos en el rango elegido
For iX = 0 To rngOrigin.Count - 1
rngDest.Offset(iX, 0) = CDate(Format(rngOrigin(iX + 1), "mm/dd/yyyy"))
Next iX
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
El problema es particularmente grave cuando los datos importados son fechas. Supongamos que recibimos un archivo .csv con una lista de fechas que nos envía una empresa de los Estados Unidos. En los Estados Unidos se usa el formato mes/día/año mientras que en la mayoría de los países hispanoparlantes el formato de fecha es día/mes/año. Al abrir el archivo .csv directamente, los valores que Excel no interpreta como fechas de acuerdo a las definiciones regionales serán transformados en texto.Veamos este ejemplo:
Podemos ver que algunas fechas están alineadas a la izquierda y otras a la derecha. Los valores alineados a la derecha han sido importados como fechas (al ser números Excel los alinea a la derecha), mientras que los valores alineados a la izquierda son texto. Esto se debe que Excel no puede interpretar esos valores como fecha siguiendo el formato regional día/mes/año (por ejemplo en la celda A2, donde el número de mes sería 23).
El valor en la celda A3 nos muestra el problema más grave que se puede generar cuando importamos archivos .csv con fechas. La fecha, siguiendo el formato de los Estados Unidos, es el 6 de Octubre pero Excel la ha transformado en el 10 de Junio.
Señalemos que los datos importados deben ser fechas, de manera que podamos realizar operaciones con ellos.
Podemos transformar los textos en fechas usando una fórmula como
=SI(ESTEXTO(A3),VALOR(EXTRAE(A3,4,2)&"/"&IZQUIERDA(A3,2)&"/"&DERECHA(A3,4)),VALOR(TEXTO(A3,"mm/dd/yyyy")))
Podemos ver que el valor de la celda A3 es transformado correctamente por la fórmula en 06/10/2012.
Otra solución es usar una macro para forzar la transformación. La ventaja de la macro consiste en que no debemos crear las fórmulas para cada hoja; podemos guardar la macro en el cuaderno Personal y usarla en cada hoja que necesitemos sin necesidad de cargarla con fórmulas.
El código básico de la macro es
Sub USDdate_to_EURdate()
Dim rngcell As Range
On Error Resume Next
For Each rngcell In Selection
rngcell = CDate(Format(rngcell, "mm/dd/yyyy"))
Next rngcell
On Error GoTo 0
End Sub
Esta macro reemplaza los valores en el rango seleccionado por fechas con formato (dd/mm/yyyy).
Este código más elaborado nos permite elegir el rango donde copiar los resultados
Sub USDdate_to_EURdate_2()
Dim rngcell As Range
Dim rngOrigin As Range, rngDest As Range
Dim iX As Long
'seleccionar el rango a transformar
Set rngOrigin = Application.InputBox(prompt:="Seleccione el rango a transformar", _
Title:="Rango a transformar", _
Type:=8)
'comprobar si el rango elegido es vertical/columna
If rngOrigin.Columns.Count > 1 Then
MsgBox "El rango seleccionado debe contener solo una columna", vbCritical, "Error en la seleccion"
Exit Sub
End If
'seleccionar la primer celda del destino
Set rngDest = Application.InputBox(prompt:="Seleccione la primer celda del rango del destino", _
Title:="Destino", _
Type:=8)
'comprobar que se haya elegido una sola celda
If rngDest.Count > 1 Then
MsgBox "Debe seleccionar solo una celda", vbCritical, "Error en la seleccion"
Exit Sub
End If
Application.ScreenUpdating = False
On Error Resume Next 'en caso de haber celdas vacias o valores no validos en el rango elegido
For iX = 0 To rngOrigin.Count - 1
rngDest.Offset(iX, 0) = CDate(Format(rngOrigin(iX + 1), "mm/dd/yyyy"))
Next iX
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
lunes, enero 12, 2015
Otro uso del Power Query - Dividir columna de ancho variable
Esta vez Eduardo entró a mi oficina sonriendo.
- El jefe me pidió que separe esta lista en dos columnas: una con el nombre del cliente y la otra con el país
- Usá Texto en Columnas, le dije sin mirar la lista ocupado como estaba con mis propios asuntos.
- No se puede. Fijate que el país es la última palabra en la celda pero cada celda contiene un número distinto de palabras.
- Y si no se puede por qué sonreís.
- Porque encontré la solución. Me fijé en el post que publicaste hace unos años sobre cómo extraer el último elemento de una celda y apliqué la que mostrabas allí a mi problema.
- Ah, que bueno. Mostrame lo que hiciste.
Lo que hizo Eduardo es crear una columna auxiliar con esta fórmula
=SUSTITUIR(A2," ","#",LARGO(A2)-LARGO(SUSTITUIR(A2," ","")))
Lo que hace esta fórmula es poner un símbolo # en lugar del último espacio en la celda. De esta manera creamos un criterio para encontrar la última palabra o valor en la celda
La expresión LARGO(A2)-LARGO(SUSTITUIR(A2," ","")) calcula la cantidad de espacios entre palabras que hay en la celda y este resultado lo usamos como argumento en la función SUSTITUIR para poner el # en el último espacio.
Para poder usar Texto en Columnas, Eduardo tuvo que eliminar las fórmulas de la columna auxiliar con Copiar-Pegado Especial-Valores. Y finalmente
La misma tarea puede hacerse con Power Query. El Power Query tiene también un método para dividir columnas pero con la ventaja de ser más flexible ya que nos permite determinar la ubicación del separador (en nuestro ejemplo el espacio) si éste se repite dentro del registro (celda).
Veamos el proceso. Empezamos por convertir la lista de clientes en Tabla (Insertar-Tablas-Tabla) para poder usar el Power Query con facilidad. Activamos la pestaña del Power Query en la cinta y elegimos Excel Data-From Table
Excel exporta los datos de la tabla a la ventana del editor del Power Query. En la ventana del editor de la consulta seleccionamos la columna y apretamos el icono Split Column- By Delimiter
En el diálogo que se abre elegimos las opciones Espacio (Space) y "el último a la derecha" (At the right-most delimiter) y apretamos OK
Todo lo que nos queda por hacer es transferir el resultado a una hoja de Excel, lo que hacemos con el menú Close&Load
Los nombres de las columnas los podemos cambiar en la ventana del editor del Power Query antes de transferir la consulta la hoja de Excel (usando Rename) o directamente en la hoja de Excel.
- El jefe me pidió que separe esta lista en dos columnas: una con el nombre del cliente y la otra con el país
- Usá Texto en Columnas, le dije sin mirar la lista ocupado como estaba con mis propios asuntos.
- No se puede. Fijate que el país es la última palabra en la celda pero cada celda contiene un número distinto de palabras.
- Y si no se puede por qué sonreís.
- Porque encontré la solución. Me fijé en el post que publicaste hace unos años sobre cómo extraer el último elemento de una celda y apliqué la que mostrabas allí a mi problema.
- Ah, que bueno. Mostrame lo que hiciste.
Lo que hizo Eduardo es crear una columna auxiliar con esta fórmula
=SUSTITUIR(A2," ","#",LARGO(A2)-LARGO(SUSTITUIR(A2," ","")))
Lo que hace esta fórmula es poner un símbolo # en lugar del último espacio en la celda. De esta manera creamos un criterio para encontrar la última palabra o valor en la celda
La expresión LARGO(A2)-LARGO(SUSTITUIR(A2," ","")) calcula la cantidad de espacios entre palabras que hay en la celda y este resultado lo usamos como argumento en la función SUSTITUIR para poner el # en el último espacio.
Para poder usar Texto en Columnas, Eduardo tuvo que eliminar las fórmulas de la columna auxiliar con Copiar-Pegado Especial-Valores. Y finalmente
La misma tarea puede hacerse con Power Query. El Power Query tiene también un método para dividir columnas pero con la ventaja de ser más flexible ya que nos permite determinar la ubicación del separador (en nuestro ejemplo el espacio) si éste se repite dentro del registro (celda).
Veamos el proceso. Empezamos por convertir la lista de clientes en Tabla (Insertar-Tablas-Tabla) para poder usar el Power Query con facilidad. Activamos la pestaña del Power Query en la cinta y elegimos Excel Data-From Table
Excel exporta los datos de la tabla a la ventana del editor del Power Query. En la ventana del editor de la consulta seleccionamos la columna y apretamos el icono Split Column- By Delimiter
En el diálogo que se abre elegimos las opciones Espacio (Space) y "el último a la derecha" (At the right-most delimiter) y apretamos OK
Todo lo que nos queda por hacer es transferir el resultado a una hoja de Excel, lo que hacemos con el menú Close&Load
Los nombres de las columnas los podemos cambiar en la ventana del editor del Power Query antes de transferir la consulta la hoja de Excel (usando Rename) o directamente en la hoja de Excel.
jueves, enero 08, 2015
Contar registros únicos en rangos grandes
En la nota anterior sobre registros únicos vimos cómo encarar el recuento usando tablas dinámicas. Esta vez Eduardo, el de la nota anterior, apareció en mi oficina y sin mucha ceremonia se sentó del otro lado del escritorio mirándome con el ceño fruncido.
- ¿Te acordás del asunto de contar registros únicos con tablas dinámicas?
- Si, seguro. Publiqué una nota en el blog.
- La leí, pero tengo otro problema.
- Si...
- Tengo una tabla con ventas a clientes. Cada cliente aparece muchas veces. Quiero poner en una celda cuántos clientes hay la lista, es decir, sin repeticiones.
- Ah! podés leer mi nota sobre contar valores únicos en Excel.
- La leí y apliqué la fórmula, pero lo lleva mucho tiempo calcular.
- ¿Cuántos registros hay en tu tabla?
- Más o menos, cuatrocientos mil.
- ¿¡¡Cua-tro-cientos-mil!!?, respondí pronunciando cada una de las sílabas por separado para enfatizar.
- Si, y cada mes agrego más.
Lo que Eduardo descubrió es que CONTAR.SI no es la más veloz de las funciones de Excel. Aplicar CONTAR.SI a un rango de 400 mil celdas es una de las mejores maneras de explicar el concepto de eternidad.
Como Eduardo insiste en no usar Filtro Avanzado o Quitar Duplicados y, además, está prohibido mencionar Access en su presencia, tuve que buscar alguna otra solución.
La fórmulas que intentaba usar Eduardo son:
=SUMA(1/CONTAR.SI(miRango,miRango)) en forma matricial
=SUMAPRODUCTO((miRango<>"")/CONTAR.SI(miRango,miRango))
Como puede observarse, ambas fórmulas utilizan la función CONTAR.SI, por lo que la solución que le propuse fue esta UDF (función definida por el usuario)
Function contar_unicos(rngSeleccion As Range)
Dim collUnicos As New Collection
Dim rngCell As Range
On Error Resume Next
For Each rngCell In rngSeleccion
collUnicos.Add rngCell, CStr(rngCell)
Next rngCell
On Error GoTo 0
contar_unicos = collUnicos.Count
End Function
Usando una macro que Charles Williams de Decisions Models tuvo la gentileza de colgar en el sitio de artículos técnicos del Office Dev Center, medí el tiempo de cálculo de las distintas fórmulas.
Para investigar el tiempo de cálculo de las distintas opciones usé un ejemplo con un rango de 20 mil celdas conteniendo números aleatorios. El examen del tiempo de cálculo de las fórmulas lo hice usando rangos de 5 mil, 10 mil y 20 mil registros
El valor en la celda G2 permite controlar el tamaño del rango; la celda G3 contiene la fórmula a examinar y la celda G4 recibe el valor del tiempo de cálculo hecho con la macro (apretando el botón "Tiempo de cálculo").
En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), estos fueron los resultados en segundos:
Podemos ver que la UDF Contar_Unicos es mucho más rápida que las que usan CONTAR.SI. Además, la diferencia crece con la cantidad de celdas a procesar. Con 5 mil celdas, Contar_Unicos es casi 33 veces más rápida que las otras; con 10 mil celdas la diferencia llega a 78 veces y con 20 mil celdas 160 veces.
Otro detalle interesante es que el tiempo de cálculo de las fórmulas no es proporcional a la cantidad de registros. Al aumentar la cantidad de registros en un 100% (de 5000 a 10000), el tiempo de cálculo de las fórmulas con CONTAR.SI crece en un 300%; un aumento del 300% en los registros (de 5000 a 20000 celdas) resulta en un aumento del 1500% en el tiempo de cálculo.
El tiempo de cálculo de la UDF con 400 mil registros fue 3 segundos.
- ¿Te acordás del asunto de contar registros únicos con tablas dinámicas?
- Si, seguro. Publiqué una nota en el blog.
- La leí, pero tengo otro problema.
- Si...
- Tengo una tabla con ventas a clientes. Cada cliente aparece muchas veces. Quiero poner en una celda cuántos clientes hay la lista, es decir, sin repeticiones.
- Ah! podés leer mi nota sobre contar valores únicos en Excel.
- La leí y apliqué la fórmula, pero lo lleva mucho tiempo calcular.
- ¿Cuántos registros hay en tu tabla?
- Más o menos, cuatrocientos mil.
- ¿¡¡Cua-tro-cientos-mil!!?, respondí pronunciando cada una de las sílabas por separado para enfatizar.
- Si, y cada mes agrego más.
Lo que Eduardo descubrió es que CONTAR.SI no es la más veloz de las funciones de Excel. Aplicar CONTAR.SI a un rango de 400 mil celdas es una de las mejores maneras de explicar el concepto de eternidad.
Como Eduardo insiste en no usar Filtro Avanzado o Quitar Duplicados y, además, está prohibido mencionar Access en su presencia, tuve que buscar alguna otra solución.
La fórmulas que intentaba usar Eduardo son:
=SUMA(1/CONTAR.SI(miRango,miRango)) en forma matricial
=SUMAPRODUCTO((miRango<>"")/CONTAR.SI(miRango,miRango))
Como puede observarse, ambas fórmulas utilizan la función CONTAR.SI, por lo que la solución que le propuse fue esta UDF (función definida por el usuario)
Function contar_unicos(rngSeleccion As Range)
Dim collUnicos As New Collection
Dim rngCell As Range
On Error Resume Next
For Each rngCell In rngSeleccion
collUnicos.Add rngCell, CStr(rngCell)
Next rngCell
On Error GoTo 0
contar_unicos = collUnicos.Count
End Function
Usando una macro que Charles Williams de Decisions Models tuvo la gentileza de colgar en el sitio de artículos técnicos del Office Dev Center, medí el tiempo de cálculo de las distintas fórmulas.
Para investigar el tiempo de cálculo de las distintas opciones usé un ejemplo con un rango de 20 mil celdas conteniendo números aleatorios. El examen del tiempo de cálculo de las fórmulas lo hice usando rangos de 5 mil, 10 mil y 20 mil registros
El valor en la celda G2 permite controlar el tamaño del rango; la celda G3 contiene la fórmula a examinar y la celda G4 recibe el valor del tiempo de cálculo hecho con la macro (apretando el botón "Tiempo de cálculo").
En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), estos fueron los resultados en segundos:
Podemos ver que la UDF Contar_Unicos es mucho más rápida que las que usan CONTAR.SI. Además, la diferencia crece con la cantidad de celdas a procesar. Con 5 mil celdas, Contar_Unicos es casi 33 veces más rápida que las otras; con 10 mil celdas la diferencia llega a 78 veces y con 20 mil celdas 160 veces.
Otro detalle interesante es que el tiempo de cálculo de las fórmulas no es proporcional a la cantidad de registros. Al aumentar la cantidad de registros en un 100% (de 5000 a 10000), el tiempo de cálculo de las fórmulas con CONTAR.SI crece en un 300%; un aumento del 300% en los registros (de 5000 a 20000 celdas) resulta en un aumento del 1500% en el tiempo de cálculo.
El tiempo de cálculo de la UDF con 400 mil registros fue 3 segundos.
Suscribirse a:
Entradas (Atom)