Contar condicional distinguiendo entre mayúsculas y minúsculas

La función CONTAR.SI no distingue entre minúsculas y mayúsculas cuando contamos las apariciones de una palabra o cadena de texto en un rango.

Por ejemplo, en el rango A1.A12 aparece la palabra “manzana”, siete veces escrita con mayúsculas (MANZANA) y cinco veces en minúsculas (manzana)

Si queremos contar cuantas veces aparece MANZANA en el rango, estaríamos tentados a usar esta fórmula

=CONTAR.SI(A1:A12,"MANZANA")

El problema es que el resultado es 12, es decir, CONTAR.SI no distingue entre minúsculas y mayúsculas



La solución consiste en usar la función IGUAL(). Esta función compara dos textos y da como resultado VERDADERO o FALSO, tomando en cuenta minúsculas y mayúsculas.

Como ya hemos explicado en el pasado, podemos forzar a Excel a convertir VERDADERO en 1 y FALSO en 0, multiplicando estos valores lógicos por 1 (o usándolos en alguna operación aritmética). Sobre esta base podemos escribir esta fórmula matricial

={SUMA(--IGUAL("MANZANA",A1:A12))}

El doble signo “-“ a la izquierda de IGUAL tiene la función de convertir la matriz de VERDADERO y FALSO generada por la función en un vector de 1 y 0, que son sumados por la función SUMA.

Como toda fórmula matricial, ésta debe ser introducida apretando simultáneamente Ctrl+Mayusculas+Enter (lo que hace que aparezcan los corchetes).

Si reemplazamos “MANZANA” por “manzana”, veremos que el resultado es 5

Technorati Tags: MS Excel

Rangos dinámicos con Listas

Una tarea frecuente en Excel es crear rangos dinámicos. La técnica más difundida es crear un nombre (Insertar-Nombres-Definir) con una fórmula que combine DESREF y CONTARA.

Una técnica alternativa más sencilla es usar Listas (Excel 2003) o Tablas (Excel 2007). Esta funcionalidad es muy útil y permite simplificar nuestros modelos en Excel.

En una nota anterior mostramos como crear con facilidad un gráfico dinámico usando Listas. En esta nota mostraremos cómo crear un modelo dinámico.

Como ejemplo construiremos un modelo para manejar el inventario de un almacén/depósito. En un cuaderno Excel creamos dos hojas: “movimientos” y “saldos”. En la primera anotamos los movimientos de los productos en el almacén (entradas – salidas); en mostramos los saldos actualizados de los productos.





En la hoja “movimientos” tenemos ahora un cuadro de datos en el rango A1:D31. Para transformar este rango en Lista, usamos el menú Datos-Lista (o Ctrl+Q)






Al apretar Aceptar veremos que Excel selecciona todo el rango, activa Autofiltro y en la primer fila libre aparece un asterisco azul. A partir de este momento, cada vez que agreguemos datos a la lista, ésta se expandirá automáticamente.

En la celda A1 de la hoja “saldos” combinamos texto y funciones para crear un título dinámico


="Saldos a la fecha "&TEXTO(MAX(movimientos!C2:C31),"dd/mm/yyyy")

Como pueden ver usamos una referencia estática al rango de las fechas en la hoja “movimientos”.
Para calcular los saldos actualizados usamos la fórmula

=SUMAR.SI(movimientos!$A$2:$A$31,saldos!A4,movimientos!$D$2:$D$31)


También aquí usamos rangos “normales”.

Ahora agregamos los movimientos del día 08/01/2009



Cuando pasamos a la hoja “saldos” vemos que tanto el título como los saldos se han actualizados. Así de simple!



En Excel 2007, el mecanismo es similar, pero la funcionalidad Lista ha pasado a llamarse Tabla. Para convertir un rango en Tabla usamos el icono Tabla en la pestaña Insertar



Tanto en Excel 2003 como en Excel 2007, la forma más cómoda y eficiente de agregar datos en la lista/tabla, es usando Tab.



Technorati Tags: MS Excel

Transferir datos de Excel a Access – segunda nota.

En la nota anterior vimos como transferir datos de Excel a Access manualmente usando los mecanismos nativos de Access.
Otra alternativa es exportar los datos de Excel a Access programáticamente, es decir usando Vba.

Para nuestro ejemplo usaremos nuevamente el archivo de la nota anterior.

En la nota anterior usamos los mecanismos de Access para importar los datos de Excel.
Al realizar la tarea con Vba haremos el camino a la inversa, decir crearemos una conexión a Access desde Excel. Esto lo haremos usando objetos de la ADO library (ADO = ActiveX Data Objects).

Antes de sumergirnos en los vericuetos del código, una observación importante: para poder usar las funciones de ADO en Excel debemos primero crear una referencia con el menú Tools-Reference del editor de Vb





Como en el ejemplo anterior, empezamos por crear una base de datos que contiene una tabla a la cual queremos exportar los datos de Excel. Nuestra base de datos (bd1.mdb) contiene la tabla Ventas que por ahora está vacía



Sin entrar demasiado en detalles técnicos, podemos describir la tarea de la siguiente manera:

# - creamos una conexión a la base de datos

#- definimos en qué tabla de la base de datos queremos poner los datos

# - definimos un objeto que contenga los datos a transferir (recordset)

# - definimos donde se encuentran los datos a transferir (en nuestro ejemplo el cuaderno tiene dos hojas con datos)

# - transferimos los datos

He agregado la hoja Parámetros en la cual podemos definir donde se encuentra la base de datos, cuál es la tabla a la cual queremos copiar los datos y en qué cuaderno se encuentran. De esta manera, podemos controlar los parámetros del código con facilidad.


Como ven he agregado también la línea a partir de la cual hay que transferir los datos de la hoja. En general los datos empiezan en la fila 2 (la 1 está ocupada por los encabezamientos). También podemos elegir de qué hoja pasar los datos, con una lista desplegable en la celda B3.
El código de la macro es el siguiente

Sub ExcelaAccess_ADO()

Dim Conn As ADODB.Connection, RecSet As ADODB.Recordset
Dim fila As Long, primerFila As Integer, ultimaFila As Long, iX As Long
Dim dataSource As String, Tabla As String
Dim wsName As String


'definimos los parametros que seran usados por el codigo

dataSource = Sheets("parametros").[B1]
Tabla = Sheets("parametros").[B2]
wsName = Sheets("parametros").[B3]
primerFila = Sheets("parametros").[B4]


' establecemos la conexion a la base de datos

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & _
dataSource & ";"

' definimos un recordset
Set RecSet = New ADODB.Recordset
RecSet.Open Tabla, Conn, adOpenKeyset, adLockOptimistic, adCmdTable

ultimaFila = WorksheetFunction.CountA(Sheets(wsName).Range("A:A"))

For iX = primerFila To ultimaFila 'mientras que la celda la columna A
'tenga un valor transferimos la fila
With RecSet
.AddNew
.Fields("Factura") = Sheets(wsName).Cells(iX, 1).Value
.Fields("Fecha") = Sheets(wsName).Cells(iX, 2).Value
.Fields("Producto") = Sheets(wsName).Cells(iX, 3).Value
.Fields("Descripcion") = Sheets(wsName).Cells(iX, 4).Value
.Fields("Cantidad") = Sheets(wsName).Cells(iX, 5).Value
.Fields("Precio") = Sheets(wsName).Cells(iX, 6).Value
.Fields("Total") = Sheets(wsName).Cells(iX, 7).Value
.Update
End With
Next iX


RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing
End Sub



Este código debe ponerse en un módulo común de Vba.