Archivo de la etiqueta: Excel

Reordenando nombres y eliminado comas con LibreOffice usando funciones concatenadas – SPREADSHEETS TIPS

SpreadsheetEl otro dia me encontré con un caso, que seguramente muchos os habéis encontrado alguna vez. Tenia dos hojas de calculo (por no decir excel) que en uno tenia los dos apellidos y el nombre (por lo menos estaban separados por una coma) y en el otro el nombre estaba en el orden de nombre y dos apellidos. Me gusta muy poco, por no decir nada, que me pongan el nombre y los apellidos en el mismo campo, pero que le vamos ha hacer, me vino así. Total que estos listados estaban destinados para crear un email personalizado, y la verdad, queda muy mal mandar el email con “Estimado Fernandez Martinez, Francisco”.  Ante tal escenario, solo hay 3 opciones, la primera que es pedir que la persona que te lo ha mandado te lo ponga bien, algo que todos sabemos no va a ser, o hacerlo uno mismo, algo que sabemos va a ser lo que va a suceder.

En mi terminal de trabajo uso Linux, mas concretamente Linux Mint, y a la hora de abrir hojas de calculo uso Libre Office Calc, pero uno a la hora de trabajar con funciones de hojas de calculo esta acostumbrado a Excel, tengo que decir que la diferencia es minima, y si uno conoce las funciones que tiene que usar, da igual usar uno u otro programa.

Para realizar esta tarea use las funciones CONCATENAR, DERECHA, IZQUIERDA, LARGO , ENCONTRAR y REDUCIR

Vamos a ver un poco por encima estas funciones a ver que hacen.

La función CONCATENAR(texto;texto1;texto2;…): Esta función sirve, como su propio nombre indica, para concatenar cadenas de texto.

La función DERECHA(texto;número): Devuelve el ultimo carácter o los últimos caracteres de un texto.

La función IZQUIERDA(texto;número): Devuelve el primer carácter o los primeros caracteres de un texto.

La función LARGO(texto): Devuelve la longitud de una cadena de texto.

La función ENCONTRAR(texto_buscado;texto;num_inicial): Busca un valor de texto dentro de otro tomando en cuenta las mayusculas y minúsculas.

La función REDUCIR(texto): Elimina los espacios del texto, excepto el espacio normal que se deja entre palabras.

Bien con estas funciones vamos a encontrar la solución a nuestro problema. Supongamos los siguientes datos de ejemplo en nuestra hoja de calculo

Tabla de datos origen
Tabla de datos origen

Primero vamos a buscar la posición de la , y esto lo haremos con la función ENCONTRAR.

Nos situamos en la celda B1 y escribimos en la linea de funciones:

=ENCONTRAR(“,”;A1)

Esta función nos devolvera 19, que es la posición que ocupa la , en el texto de la celda A1

Función encontrar

Una vez encontrada la posición, vamos a extraer los apellidos, para eso usaremos la función IZQUIERDA la cual la usaremos concatenada con la función ENCONTRAR.

=IZQUIERDA(A1;ENCONTRAR(“,”;A1)-1)

Esta función nos devolvera FERNANDEZ MARTINEZ. El -1 del final es por que si le pasamos directamente el encontrar nos incluye la , y eso no nos interesa, nos interesa solo los apellidos.

Ahora vamos a extraer el nombre, y para hacer eso usaremos la función DERECHA, la función LARGO, y otra vez la función encontrar.

Empezaremos usando la función LARGO la cual nos devuelve el total de numero de caracteres que tiene la cadena de texto.

=LARGO(A1)

Esta función nos devuelve 29, ahora vamos a usar de nuevo la función ENCONTRAR(“,”;A1)

=LARGO(A1)-ENCONTRAR(“,”;A1)

Esto nos devolvera 10, que es el numero de caracteres que hay desde la , hasta el final de la cadena. Hay que tener en cuenta que según tengamos escrita la cadena puede haber un espacio en blanco o no incluido en ese numero, eso es algo que solucionaremos mas tarde con otra función.

Ahora que sabemos cuantos caracteres tiene el nombre, podemos extraerlo con la función DERECHA.

=DERECHA(A1;LARGO(A1) – ENCONTRAR(“,”;A1))

Esto nos devolvera FRANCISCO. Ahora fijaros si el texto resultante tiene un espacio en blanco delante o no, esto dependerá de como lo han escrito en origen, para eliminar los espacios que puede haber al principio o al final usaremos la función REDUCIR.

=REDUCIR(DERECHA(A1;LARGO(A1) – ENCONTRAR(“,”;A1)))

Esta función nos devolvera FRANCISCO sin espacios delante.

Ahora nos queda CONCATENAR las partes que tenemos, y esto nos deja la siguiente función. En esta función final uso la función REDUCIR para que envuelva a toda la cadena y elimine los posibles espacios en blanco no deseados.

=REDUCIR(CONCATENAR(DERECHA(A1;LARGO(A1) – ENCONTRAR(“,”;A1));” “;IZQUIERDA(A1;ENCONTRAR(“,”;A1) -1)))

Función finalResultado final

Una vez aplicada la función, en la celda B2 nos aparece FRANCISCO FERNANDEZ MARTINEZ, es decir ordenado, sin comas y sin espacios extra.

Ahora solo nos quedaria aplicar la formula en toda la columna (doble clic en el punto negro de la esquina inferior derecha) teniendo la celda B1 seleccionada.

Aplicando la formula

Finalmente, si queremos eliminar los datos originales y quedarnos solo con los nuevos datos, ya ordenados, seleccionaremos la columna B, la copiaremos, y haremos un pegado especial de solo texto encima de la columna A, aceptaremos la advertencia que nos da, y eliminaremos la columna B, y con esto ya tenemos todos nuestros datos en el formato deseado.

Pegado especial Resultado final

Espero que os sirva.

Anuncios

Contar elementos que cumplan una condición o varias – Spreadsheets Tips

Contar los que cumplan un solo criterio

Supongamos la siguiente situación. Tenemos una tabla con datos y en una de las columnas tenemos 2 posibles valores, si o no, y queremos contar cuantas veces esta el valor si y cuantas esta el valor no. Ademas queremos que este total se actualize según se introduzcan datos en esa columna.

Para realizar este calculo en Excel o Numbers o LibreOffice Calc utilizaremos la función CONTAR.SI

= CONTAR.SI(rango;valor)

Si por ejemplo queremos saber cuantas veces se ha escrito el valor ‘Si’ en la columna A escribiremos:

= CONTAR.SI(A:A; "Si")

En LibreOffice Calc la función es la misma, con una salvedad, la cual puede resultar un poco incomoda, no podemos seleccionar la columna entera y tenemos que definir el rango. Por lo demás la formula es igual.

En Numbers la función es tambien igual pero en el rango si seleccionamos la columna A solo hace falta poner A.

Importante: Hay que tener en cuenta las dobles comillas para encerrar textos literales y el ‘;’ para separar los parámetros.

En el caso que estemos utilizando Google Spreadsheets, hay que tener en cuenta que las formulas no han sido traducidas al castellano, por lo tanto tenemos que usarlo como si estuviéramos usando el Excel en Ingles y usaríamos la función COUNTIF

= COUNTIF(rango, valor)

Para saber cuantas veces se ha escrito el valor ‘Si’ en la columna A

= COUNTIF(A:A, "Si")

Una de las diferencias entre las versiones de los programas de hojas de calculo en Español o en ingles es que en las versiones inglesas usan la coma (,) como separador de parámetros en la función.

 

Contar los que cumplan mas de un criterio

La función anterior solo nos permite buscar en una columna un criterio, pero ¿y si necesitamos contar los que cumplan dos criterios? En ese caso si estamos utilizando Excel, Numbers o Libreoffice Calc utilizaremos la función: CONTAR.SI.CONJUNTO()

= CONTAR.SI.CONJUNTO(rango1;criterio1;rango2;criterio2;...)

Si tenemos la columna A con valores Si o No y la columna B con valores Si o No y queremos saber cuantas veces si el valor de la A es Si el valor de la B es Si

= CONTAR.SI.CONJUNTO(A:A;"Si";B:B;"Si")

En el caso de Google Spreadsheets no existe una función equivalente y para obtener el resultado tenemos que utilizar la función ARRAYFORMULA con el COUNTIF anidado.

= ARRAYFORMULA( COUNTIF(rango1&rango2&..., criterio1&criterio2&...) )

De tal manera que para obtener nuestro resultado tendríamos que hacer lo siguiente

= ARRAYFORMULA( countif(A:A&B:B, "Si"&"Si") )

 

Inauguramos la sección de hojas de calculo – Spreadsheets Tips

Con esta entrada inauguramos una nueva sección en la cual voy a contar trucos y funciones para usar en los programas de hojas de calculo, y digo hojas de calculo y no Excel, ya que hoy en dia no solo se usa el Excel, sino que se pueden usar otras alternativas comerciales como Numbers para Mac, o alternativas gratuitas como LibreOffice o Google Spreadsheet, y no todas tienen las mismas funcionalidades, aunque el trabajo basico es bastante parecido, por no decir igual.

Soy consciente que la categoria principal en la cual voy a ubicar estas entradas (Programación) apriori no seria la categoria mas adecuada para estas, pero si nos paramos a pensar un momento, estos programas tienen funciones propias y tienes que cumplir una estructura para poder realizar los cálculos, por no hablar de la programación con la cual funcionan las macros.

Aunque la mayoría de las entradas estarán relacionadas con Excel, intentare realizar las mismas funciones tanto para LibreOffice, Google Spreadsheet y Numbers.

Espero que sean de utilidad para todos.