Listar valores unicos de una base de datos y II.

Como continuación del post de ayer, explicaré cómo podemos obtener de una manera diferente un listado de valores únicos extraidos de una base de datos.
Partimos de la misma base de datos:

En esta ocasión trabajaremos con la herramienta de Filtro avanzado; navegaremos por el menú Datos > Ordenar y filtrar > Avanzadas

Es importante que observemos que hemos seleccionado la opción de Copiar a otro lugar, y por supuesto el tick de Sólo registros únicos. El resultado no puede ser otro que obtener un nuevo listado con valores únicos.

Existe otra forma de conseguir nuestro registro únicos, y es mediante una Tabla dinámica...

Seguir leyendo...

Listar valores únicos de una base de datos.

En muchas ocasiones hemos trabajado con bases de datos en Excel en la que tenemos multitud de registros duplicados y nuestro trabajo nos obliga a quedarnos sólo con los valores únicos.
Vamos a ver dos formas de obtener nuestro listado de valores no repetidos.
La primera es bien sencilla empleando Excel 2007, ya que en esta versión se incluye una herramienta que elimina los valores duplicados.
Tiempo atrás mostré una forma de remarcar cuáles eran los elementos duplicados en una base de datos mediante un Formato condcional, pero para nuestra tarea de hoy esto es insuficiente.

Seguir leyendo...

Proteger hoja en Excel.

Un usuario, a través de un comentario, pedía una aclaración de cómo Proteger una hoja en nuestros Libros de Excel. El asunto necesariamente debe pasar, en primer lugar, por el Formato de celdas (Ctrl+1), ya que debemos conocer las opciones de una de las pestañas de este formato de celdas:

Seguir leyendo...

Macro para convertir en número valores de texto.

Días atrás comenté cómo desde los menús de Excel podíamos CONVERTIR EN NÚMERO VALORES ALMACENADOS COMO TEXTO; en esta ocasión generaré una sencilla macro en VBA para realizar esta conversión: multiplicar las celdas por UNO.
El código planteado surge de la cuestión lanzada por un lector:

...tengo la necesidad de crear una macro para convertir valores de texto a numérico, y evitar tener que hacerlo manualmente cada vez que uso la base de datos que me llega; los datos están siempre en la columna F pero la cantidad de filas es variable...

Mi hipótesis de trabajo es que los valores con formato de texto están siempre en la columna F, y que tiene una cabecera o título, es decir, que los importes empiezan siempre en F2, y nos da igual donde acaben (haya o no celdas vacías entre los valores).

El sencillo código VBA generado consiste en identificar las celdas con valor de la columna F y multiplicarlas por 1, de tal forma que se conviertan en celdas con valores numéricos para poder operar con ellas...

'para convertir valores almacenados como texto en números automáticamente
Sub Conv_text_Num()
Dim finAs Range
Dim f,ffAs Integer
Const x = 1
Set fin = Range("F"&Rows.Count).End(xlUp)
f = Cells(fin.Row, 6).Row
    For ff = 2 To f
        If Range("f" & ff).Value <> "" Then _
Range("f" & ff).Value = Range("f" & ff).Value * x
    Next
End Sub

Luego, para ejecutarlo, lo más sencillo es crear un botón y asignádole dicha macro.
Como siempre en estos temas quedo expuesto a los comentarios de verdaderos expertos en programación VBA...

Seguir leyendo...

Discriminar valores máximos y mínimos.

Propondré esta ocasión un ejercicio o pequeño ejemplo de cómo discriminar valores de un listado a efectos de cálculos estadísticos, empleando Excel.
El ejercicio surge de la petición de un usuario:

...como quitar los datos extremos (ej. el 30 % de los valores más altos y el 30 % de los más bajos) de cada grupo a comparar...

El problema viene por la necesidad de trabajar con valores de una población que no distorsionen el estudio.

Seguir leyendo...

Convertir en número valores almacenados como texto.

Recientemente un lector me consultaba la forma de convertir en números aquellos números almacenados como texto. En ocasiones, la mayoría al importar datos a nuestras hojas de cálculo, o simplemente por que nuestros números tienen formato de texto, nos encontramos con impedimentos para operar correctamente con nuestras celdas o produce ordenaciones confusas no deseadas...
En todos los casos sabremos que estos números no son reconocidos como tal por Excel cuando están alineados a la izquierda, por que no les podemos dar formatos personalizados de números y por que suelen tener un indicador de error en la esquina superior izquierda.

Seguir leyendo...

Calcular la edad con Excel.

Hace ya algunos meses expliqué el uso de una función poco conocida y descatalogada (diría yo), la función SIFECHA(fecha inicial, fecha final, criterio de cuenta), con la que podíamos calcular diferencias entre fechas...
Buenos, pues un ejemplo de por qué podríamos necesitar diferencias entre fechas es conocer cuál es nuestra EDAD.
Supongamos dos celdas A1 - fecha nacimiento y A2 - fecha a calcular, ya que queremos conocer nuestra edad en una fecha a determinar.
De acuerdo a lo que leímos en cálculo entre fechas, podíamos definir ese tercer argumento 'Criterio de cuenta'de varias formas:

1. "y" : devuelve la cantidad entera de años en el intervalo de fechas.
2. "m" : devuelve la cantidad entera de meses en el intervalo de fechas.
3. "d" : devuelve la cantidad entera de días entre ambas fechas.
4. "ym" : obtendríamos la cantidad de meses por encima de la cantidad entera de años.
5. "md" : obtendríamos la cantidad de días por encima de la cantidad entera de meses.

En concreto utilizaremos estos dos últimos parámetros. Supongamos en A1 la fecha de hoy 10/05/2010, y en A2 la fecha 08/05/2013; si combinamos los distintos criterios y concatenamos los cálculos de años, meses y días obtendremos:
=SIFECHA(A1;A2;"y")&" años, "&SIFECHA(A1;A2;"ym")&" meses y "&SIFECHA(A1;A2;"md")&" días"

haz click en la imagen

Seguir leyendo...

Ejercicio con LISTAS de Excel.

Un par de meses atrás expliqué brevemente cómo funcionaban las LISTAS o Tablas, y las muchas ventajas que tenía trabajar con ellas.
El ejercicio que planteamos surge de la cuestión planteada por un usuario:

...tengo dos libros en ALUMNO.XLS Y INFORMACION.XLS y necesito que al modificar o crear filas en el libro alumno.xls se actualice el libro informacion.xls sólo con el apellido que esta en la celda NOMBRE (tiene junto el nombre y el apellido) , y la celda CURSO ....

Resolveré el ejercicio empleando la función BUSCARV y la Tablas o Listas.

Seguir leyendo...

La función K.ESIMO.MAYOR.

Mencionaré una función útil a la hora de analizar datos con un orden determinado, la función K.ESIMO.MAYOR y su correspondiente para el orden inverso K.ESIMO.MENOR; funciones que nos devuelven el orden k, por arriba o por abajo, de un listado de valores.
El ejemplo que planteamos surge de la duda de un usuario:

...Según el ejemplo que te envio el problema que tengo es que necesito saber para B5:AE5, los días transcurridos (celdas en blanco) desde la ultima "x" y la anterior "x", con una formula que me lo vaya calculando automáticamente....

Tenemos distribuido por filas los días de un mes, y en otra fila más abajo, determinados días concretos marcados con una 'x'. Lo que necesitamos saber es cuántos días han transcurrido entre la última y penúltima 'x' marcadas.
Antes de aplicar la función objeto de estudio, incorporaremos un sencillo condicional en una nueva fila, que nos convierta en cero los días no marcados (en blanco):
=SI(B5="";0;B3)

Seguir leyendo...

Convertir positivos en negativos dada una condición.

Hace un par de días me llegó al correo un problema curioso, y aunque me considero un mero aprendiz en temas de programación VBA, sin embargo, decidí analizar la consulta del lector. En ella pedía ayuda para facilitar la introducción de datos en una plantilla, distinguiendo valores positivos o negativos, condicionando el signo del número al valor de otra celda.
Describía el usuario:

...Supongamos que en la columna A de una hoja tengo los siguientes titulos de filas: A2: Pagos por intereses, A3: Cobros por dividendos, A4: Pagos a proveedores, A5: Cobros por Vtas Contado, A6: Cobros por Vtas. Cta.Cte., A7:Pagos por prestamos En la fila 1 tengo los siguientes titulos de columnas: B1: Enero, C1: Febrero, D1: Marzo, E1: Abril Luego las celdas del rango B2:E7 se cargaran con datos numericos respectivamente Hasta aca todo bien, pero si ven el cuadro que se armo tenemos 3 celdas que contienen la palabra "pagos" y tres celdas que contienen la palabra "cobros". Lo que quiero hacer es que a medida que se carguen los datos vayan quedando como positivos o negativos segun lo que indiquen las celdas A2, A3, A4, A5, A6 y A7 en su interior. La formula o macro deberá detectar, identificar o buscar la palabra "pagos" dentro de la frase de la celda A2 y si es cierto entonces el numero que se cargó en la celda B2 transformarlo en negativo. Si dentro de la celda no se encuentra la palabra "pagos" entonces dejarlo como positivo...

Para facilitar el trabajo del desarrollo de la macro, incluiremos una nueva columna en la que nos indique si el concepto de las filas corresponde a pagos 'P' o a cobros 'C':

Desarrollaré unas líneas de VBA donde se restrinja, condicionado al valor de las celdas de la columna A, el valor introducido ('P' o 'C').

Para ello abrimos el Editor de Visual Basic(Alt+F11), y nos dirigimos a la ventana del Explorador de proyectos, haciendo click en la Hoja donde queramos incluir esta funcionalidad, en mi caso la Hoja1, y en la ventana de código de esta Hoja escribiremos:

'para convertir números positivos en negativos automáticamente
Private Sub Worksheet_change(ByVal Target As Range)
Static celda_activa As Integer
On Error Resume Next
    If Cells(Target.Row, 1) = "P" And Cells(Target.Row, Target.Column).Value > 0 Then
       Cells(Target.Row, Target.Column).Value = 
Cells(Target.Row,Target.Column).Formula * (-1)
     End If
End Sub

Con lo que he conseguido lo que se pretendía, esto es, cada vez que se introudzca un valor en la Hoja1 y en la primera columna exista un valor igual a 'P', el número se convertirá en negativo.

Seguir leyendo...

Macro para Guardar como texto

Hace algunos días subí el código de una macro con la cual, podíamos Navegar con un Explorador de Windows desde nuestro Excel.
Aprovecharé la petición de un usuario para presentar una utilidad de éste, así como complementarlo, ejecutando una opción especial de Guardar como texto deilimitado por tabulaciones.
Lo que pedía era:

...Tengo una tabla de datos en excel compuesta por 15.000 lineas de registro, cada una de las cuales tiene 7 columnas de datos... Esta tabla de datos debe servir para la creación de listas de distribución en Outlook... La macro que necesito ha de hacer lo siguiente: 1.- Filtar la columna 7 cada vez eligiendo un valor diferente 2.- Copiar el contenido de los resultados del filtrado a otra hoja de este libro 3.- Guardar esa nueva hoja como documento de texto separado por tabuladores (para poderlo importar luego a Outlook), tomando como nombre de archivo el valor correspondiente de la colunma 7 que ha servido para el filtrado.

Para los dos primeros puntos ya teníamos una solución programada, que podemos ver en macro de un filtro avanzado
Recordando el código de ésta:

'ejecutamos un filtro avanzado, copiando el resultado en la Hoja2
Sub Filtro avanzado()
    Range("Datos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "Criterios"), CopyToRange:=Sheets("Hoja2").Range("A1"), Unique:=False
    Sheets("Hoja2").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete
End Sub

También podemos añadir una segunda macro que permita el borrado del Filtro obtenido:

'borra de la Hoja2 los registros filtrados
Sub Borrar()
    Sheets("Hoja2").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlToLeft
End Sub

Seguir leyendo...

Rangos de consolidación múltiples.

He desarrollado algunas utilidades de las tablas dinámicas, hoy, a través del ejemplo planteado por un lector, veremos cómo podemos obtener alguna ventaja adicional empleando los rangos de consolidación múltiples:

...tengo dos hojas de excel, en una tengo informacion de costes para el primer trimestre, y en la otra tengo más gastos (algunos conceptos repetidos) desde marzo a junio, necesito combinar la primera con la informacion de la segunda...

Esta herramienta sólo está disponible desde el asistente de tablas dinámicas de Excel 2003, por lo que si trabajamos con versiones superiores, deberemos llamar al asistente presionando las teclas Alt+t+b o sacando el botón correspondiente a la barra de herramientas de acceso rápido.
Para desarrollar un ejemplo no hará falta trabajar desde dos hojas, será suficiente disponer de dos o más bases de datos diferenciadas:

Seguir leyendo...