DETECTAR VALOR DE LA PRIMERA Y ÚLTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

Hola a todos!

Hace unos días recibí una consulta en la que me solicitaban el poder capturar el valor de una celda en función de si la celda de la fila inferior tenía datos.

Por ejemplo, imaginad que tenemos dos filas, en la primera unas fechas y en la segunda unos importes, pues bien necesitamos extraer las fechas de la primera y última celda con datos en la fila de los importes:

DETECTAR VALOR DE LA PRIMERA Y ULTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

Para resolver esta necesidad, (que con una macro sería muy sencillo), con una fórmula lo podemos hacer con una función matricial.

Para extraer la primera fecha, el 3 de enero:

{=DESREF(INDICE(B2:O2;COINCIDIR(FALSO;(B2:O2="");0));-1;0)}

Y para extraer la última fecha, el 11 de enero:

{=DESREF(INDICE((B2:O2);;MAX(SI((B2:O2)<>"";COLUMNA(B2:O2)))-1);-1;0)}

En ambos casos, primero detectamos tanto el valor inicial o el final, sin tener en cuenta las celdas vacías, esto nos dará el número de columna, luego simplemente usamos la función desref para indicar que queremos la fila anterior (-1).

El resultado es el siguiente:

DETECTAR VALOR DE LA PRIMERA Y ULTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR1

Como podéis comprobar, las fórmulas funcionan perfectamente.

Y eso ha sido todo, espero que os resulte de utilidad:

Descarga el archivo de ejemplo pulsando en: DETECTAR VALOR DE LA PRIMERA Y ÚLTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

CONTAR PALABRAS EN EXCEL

Hola a todos!

Ayer un lector me pedía un fórmula para poder contar palabras en Excel. Supongo que era para delimitar en un informe la cantidad de palabras que un usuario podía escribir.

Lo cierto es que es muy común delimitar de alguna forma el espacio de escritura en los informes, aunque suele hacerse por caracteres escritos y no tanto por palabras, sin embargo también es posible hacerlo.

Para conseguirlo, y sin necesidad de acudir a la programación, tan solo necesitaremos usar la combinación de varias fórmulas:

=SI(LARGO(A2)=0;0;LARGO(ESPACIOS(A2))-LARGO(SUSTITUIR((A2);" ";""))+1)& " palabras"

Con esta función, donde combinamos las funciones LARGO y SUSTITUIR será muy sencillo. Contamos todos los caracteres en nuestra frase (incluidos los espacios en blanco) y le restamos los mismos caracteres pero sin esos espacios en blanco, la diferencia es el número de palabras. Usaremos un condicional para en caso de no existir palabras nos ponga un “0”.

El resultado de la fórmula es este:

CONTAR PALABRAS EN EXCEL

Descarga el archivo de ejemplo pulsando en: CONTAR PALABRAS EN EXCEL

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

PASAR INFORMACIÓN DE UN RANGO A UNA CELDA USANDO FÓRMULAS

Hola a todos!.

El post de hoy va a tratar sobre cómo podemos pasar el contenido de un rango a una celda usando solo fórmulas y sin recurrir a VBA.

En un post anterior os comentaba la forma de hacerlo con macros: PASAR INFORMACIÓN DE UN RANGO A UNA CELDA

Y también recordar que con las últimas actualizaciones de Excel (pero con una suscripción a Office 365) esto se puede realizar fácilmente con la función UnirCadenas.

Pero este post está pensado para esas situaciones de emergencia, cuando no tenemos tiempo ni para programar ni tampoco acceso a office 365 (por los motivos que sean).

Veamos un ejemplo, en la columna A tenemos un texto que está distribuido en diferentes celdas:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS

La idea es pasar la información a una única celda y además dejarla colocada con la misma estructura en vertical añadiendo saltos de línea.

Necesitaremos una celda auxiliar para realizar nuestro trabajo, en la que vamos a usar la función concatenar y seleccionar el rango con los datos:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS1

A continuación, seleccionamos la fórmula y pulsamos F9 en el teclado. El resultado es que se mostrarán los datos que se han añadido a la matriz de la función:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS2

Ahora lo que tenemos que hacer es eliminar el igual = y los paréntesis { } y nos quedará una cadena de texto con la información lista para tratar con una última fórmula:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS3

Efectivamente, con la última fórmula utilizamos una función anidada con “sustituir”:

=SUSTITUIR(SUSTITUIR(B9;CARACTER(34);" ");";";CARACTER(10))

En la fórmula inicial, sustituimos las comillas dobles CARACTER(34), por un espacio y en la segunda (la anidada), sustituimos el punto y coma por un salto de línea CARACTER(10).

El resultado es el que podéis ver, hemos pasado la información de un rango a una celda e incluso hemos dejado el mismo formato.

Aunque no sea un proceso limpio con una sola función o usando macros, estoy seguro que en algún momento os ayudará en alguna situación apurada (a mí me ha ayudado en más de una ocasión).

No dejaré archivo de prueba porque se trata de un proceso semiautomático.

Y eso es todo!, hasta el próximo post 🙂

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

CONTAR REGISTROS ÚNICOS CON VARIOS CRITERIOS

Hola a todos:

Hoy voy a trabajar un poco con formulación clásica de Excel. En concreto vamos a contar registros únicos en función de varios criterios. Para ello he preparado una sencilla tabla con unos datos que nos van a servir de ejemplo:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS

Imaginad que queremos contar los registros únicos de la columna C que dependan de “Dirección de Area 1 y que el criterio de la columna B sea mayor o igual a 1. Es decir:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS1

Qué los registros únicos serán 3, las Subdirecciones Zona 1.1, 1.2 y 1.3

Para poder realizar esto con una fórmula vamos a utilizar la siguiente función matricial:

{=CONTAR(1/FRECUENCIA(SI(Hoja1!$A$2:$A$18="Dirección Area 1";SI(Hoja1!$B$2:$B$18>=1;COINCIDIR(Hoja1!$C$2:$C$18;Hoja1!$C$2:$C$18;0)));FILA($A$2:$A$18)-FILA(DESREF($A$2:$A$18;;;1;))+1))}

Como podéis ver, estamos usando varios fórmulas, CONTAR, FRECUENCIA, SI, COINCIDIR, FILA y DESREF.

El resultado es el siguiente:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS2

Obviamente, la fórmula se puede modificar variando e incluyendo nuevos criterios, solo debéis realizar algunas pruebas.

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Descarga el archivo de ejemplo pulsando en: CONTAR REGISTROS ÚNICOS CON VARIOS CRITERIOS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

SUMAR EL VALOR DE CADA N NÚMEROS EN UN RANGO

Hola a todos!

Hoy de nuevo voy a escribir sobre funciones matriciales, en el post de hoy veremos cómo podemos sumar el valor de cada “n” números en un rango.

Creo que la mejor forma de explicar esto se puede hacer con una imagen:

SUMAR CADA N VALOR EN UN RANGO

Imaginad que tenemos que hallar el resultado de cada cuatro números en la columna A, (adicionalmente en la columna B he colocado estos números para ofrecer un ejemplo más claro. El resultado sería de 40.

Para realizar esta operación con una sola fórmula o bien recurrimos a macros o bien utilizamos fórmulas matriciales. En el ejemplo de hoy utilizaré una fórmula matricial:

{=SUMA(SI(RESIDUO(FILA(INDIRECTO("1:"&CONTAR(A:A)));4)=0;DESREF(A2;0;0;COINCIDIR(9,99999999999999E+307;A:A)-FILA(A2)+1;1);""))}

Una vez aplicada, el resultado es el de 40:

SUMAR CADA N VALOR EN UN RANGO1

Con esta fórmula somos capaces de obtener los “n” valores que decidamos dentro de un rango variable y proceder a su suma. En rojo está el número que os servirá para indicar el “n” valor, en este ejemplo, el 4.

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Y esto a ha sido todo, espero que os resulte de interés.

Descarga el archivo de ejemplo pulsando en: SUMAR EL VALOR DE CADA N NÚMEROS EN UN RANGO

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

CONTAR NÚMERO DE CARACTERES EN UN RANGO UTILIZANDO FÓRMULAS

Hola a todos!

En el post de hoy voy a trabajar con fórmulas, no siempre vamos a utilizar macros cuando la solución la podemos hallar mediante funciones nativas de Excel.

Un lector me preguntaba ayer si podía ofrecerle una fórmula que contase el número total de caracteres contenidos en un rango de celdas. Aunque podéis pensar que una sencilla solución es usar la función LARGO aplicada a cada celda y luego sumar el total, la respuesta se puede lograr usando una única fórmula (pero matricialmente).

Pero para ilustrar el ejemplo, imaginad que queremos obtener el total de caracteres que contiene el siguiente fragmento de archivo XML obtenido como respuesta a una consulta realizada a Google Maps:

CONTAR NUMERO DE CARACTERES EN UN RANGO UTILIZANDO FORMULAS

Para obtener el total de caracteres de toda la columna A utilizaremos la siguiente fórmula:

{=SUMA(LARGO(A:A))}

El resultado obtenido va a tener en cuenta cualquier caracter, incluidos los espacios en blanco, las comas, los puntos, etc. En concreto, el resultado es de 1720 caracteres.

Con esto el lector ya tiene la respuesta a su consulta, sin embargo, imaginad que lo que necesita es conocer solo el número de caracteres alfanuméricos. Es decir sin tener en cuenta los espacios, puntos, comas …

Para poder realizar el mismo ejercicio, necesitamos incluir la función SUSTITUIR en nuestra función inicial y además anidando los elementos que queremos excluir en la cuenta final:

{=SUMA(LARGO(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(A:A;">";"");"<";"");"/";"");" ";"");"_";"");".";"")))}

Aplicando esta fórmula el resultado ahora es de 1176 caracteres, como podéis observar el número se ha reducido significativamente.

CONTAR NUMERO DE CARACTERES EN UN RANGO UTILIZANDO FORMULAS1

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Espero que os resulte de utilidad!

Descarga el archivo de ejemplo pulsando en: CONTAR NUMERO DE CARACTERES EN UN RANGO UTILIZANDO FORMULAS

 

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

ELIMINAR REGISTROS DUPLICADOS Y CARGAR DATOS ÚNICOS EN COMBOBOX Y LISTBOX

Hola a todos!

El universo de los datos duplicados y su tratamiento es muy variado en Excel, se pueden tratar tanto con fórmulas, con comandos instalados en la hoja y también con vba. El objetivo suele ser siempre trabajar con registros únicos para evitar duplicidades en las informaciones.

En esta web hay varios ejemplos de cómo se pueden obtener registros únicos, hoy os voy a mostrar una nueva variante. Imaginad que tenéis los meses del año en una columna, pero en algunos casos se duplica el nombre de los meses:

ELIMINAR REGISTROS DUPLICADOS Y CARGAR DATOS UNICOS EN COMBOBOX Y LISTBOX

El objetivo será obtener registros únicos y cargar un combobox y un listbox. Para ello vamos a trabajar con la siguiente macro:

Sub CARGAR_UNICOS()
'Declaramos las variables
Dim rango As Range, oDic As Object, celda As Object
Dim ipalabra As String, matriz1 As Variant, matriz2 As Variant
Dim sCadena1 As String, sCadena2 As String, i As Long, j As Long, Fin As Integer
'Trabajamos con la hoja
With Sheets("UNICOS")
'Limpiamos combo y listbox
.ComboBox1.Clear
.ListBox1.Clear
'Definimos el rango de los datos
Fin = .Range("A" & Rows.Count).End(xlUp).Row
'Trabajamos con rango establecido y creamos objeto diccionario
Set rango = .Range("A2:A" & Fin)
Set oDic = CreateObject("scripting.dictionary")
'Por cada celda con datos en el rango rango componemos un string
For Each celda In rango
If celda <> vbNullString Then
ipalabra = ipalabra & "," & celda
End If
Next celda
'Eliminamos posibles espacios en blanco
sCadena1 = Trim(Mid(ipalabra, 2, Len(ipalabra)))
'Comprobamos que cada palabra que incluimos en la matriz no existe en cadena
matriz1 = Split(sCadena1, ",")
For i = 0 To UBound(matriz1)
If Not oDic.Exists(matriz1(i)) Then oDic.Add matriz1(i), matriz1(i)
Next i
'Creamos una nueva cadena ya sin duplicados
sCadena2 = Join(oDic.Keys, ",")
matriz2 = Split(Trim(sCadena2), ",")
'Pasamos los datos al Combo y al listbox
For j = 0 To UBound(matriz2)
.ComboBox1.AddItem (matriz2(j))
.ListBox1.AddItem (matriz2(j))
Next
'Liberamos memoria
Set rango = Nothing
Set oDic = Nothing
End With
End Sub

Lo que estamos haciendo con este código es: pasar los datos del rango a una variable string con todos los meses (incluidos los repetidos) en una cadena de texto. Si en ese rango existen celdas sin información, no las tendremos en cuenta.

Con el objeto “scripting.dictionary” y mediante un loop, comprobamos si cada palabra “no” existe, en caso afirmativo, la incorporamos de nuevo a la “matriz1”. De esta forma controlamos que no aparezcan registros duplicados en nuestros datos.

A continuación, pasamos los datos a una string y de nuevo a una matriz (matriz2), que nos permitirá con un loop cargar los datos en el Combobox1 y en el Listbox1.

En las partes del código donde hago mención al uso de la coma como delimitador “,” es necesario su utilización para delimitar los elementos de la celda. Si usásemos un espacio en blanco pasaría la segunda, tercera o n palabra como si fuesen otra celda (que puede que también os sea de utilidad para obtener únicos en una única celda).

El resultado es el siguiente:

ELIMINAR REGISTROS DUPLICADOS Y CARGAR DATOS UNICOS EN COMBOBOX Y LISTBOX1

Como podéis observar, los datos se han cargado correctamente y sin duplicados. Por cierto, para que realicéis pruebas he adjuntado una sencilla macro para vaciar el combobox y el listbox que está vinculada al botón “Vaciar Combo y Listbox”.

Y eso es todo, espero que este método os resulte de interés : )

Descarga el archivo de ejemplo pulsando en: ELIMINAR REGISTROS DUPLICADOS Y CARGAR DATOS UNICOS EN COMBOBOX Y LISTBOX

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!