lunes, 29 de abril de 2013

Eliminar saltos de línea

Ya he indicado en alguna ocasión que los saltos de línea dentro de una celda se crean pulsando Alt + Intro. Recordémoslo poniendo un ejemplo.

  1. Nos ponemos en B2.
  2. Escribimos: José Luis Martínez     [Pulsamos Alt + Intro]
  3. Escribimos: C/Goya, 3-1º D     [Pulsamos Alt + Intro]
  4. Escribimos: Zaragoza     [Terminamos con Intro ]
Pero, ahora, deseamos realizar la operación contraria; es decir, dada una lista de datos con los valores de las celdas escritas en varias filas, debemos eliminar los saltos de línea y escribir los datos de cada celda en una sola línea con el separador que queramos: espacio, barra vertical, asterisco...

Vamos a resolver el problema de dos maneras:

Manualmente: Buscar y reemplazar

Pulsamos Ctrl + L para entrar en el cuadro de diálogo Buscar y reemplazar. En la ficha Reemplazar y en el apartado Buscar, pulsamos Ctrl + J para insertar el código de salto de línea. En Reemplazar con, escribimos un espacio seguido del carácter | y otro espacio. Terminamos pulsando el botón Reemplazar todos y cerramos el cuadro de diálogo.

Probablemente tengamos que ensanchar la columna manualmente para que nos quepa el texto. Si queremos, podemos hacerlo de otro modo: seleccionamos B2:B4, pulsamos Ctrl + 1 para entrar en la ventana Formato de celdas y quitamos la marca del apartado Ajustar texto.

Con una fórmula: SUSTITUIR y CARACTER

Cuando queramos conservar las celdas originales y copiarlas en otro rango en una sola fila, usaremos esta fórmula:

En D2:
=SUSTITUIR(B2;CARACTER(10);" | ")     [Extendemos la fórmula hasta la fila 4]

El carácter ASCII 10 es el salto de línea que hay que buscar y sustituir por otro carácter (en el ejemplo, la barra vertical). Ensanchamos la columna D para que entre toda la línea y, si queremos, la centramos verticalmente.

lunes, 22 de abril de 2013

La función DELTA

Microsoft incluye DELTA dentro del grupo de funciones de Ingeniería. La función compara dos números y devuelve 1 (si ambos coinciden) o 0 (si no coinciden). La sintaxis es:

DELTA(número1; [número2])

Si se omite número2, se considera que vale 0. Si alguno de los argumentos no es un número, DELTA devuelve el error #¡VALOR!

Nunca he tenido necesidad de utilizar la función DELTA ya que se pueden usar otras funciones para obtener el mismo resultado. Además, en Excel 2003 sólo está disponible si se carga el complemento Herramientas para análisis (Herramientas + Complementos + Herramientas para análisis).

Comparemos dos números usando DELTA, el operador "=" y la función IGUAL:
  • =DELTA(5;5)     [Resultado: 1]
  • =--(5=5)     [Resultado: 1]
  • =--IGUAL(5;5)     [Resultado: 1]
A mi modo de ver, los dos últimos ejemplos son más claros y explícitos que el primero.

Estos ejemplos no son muy adecuados ya que los números suelen estar escritos en las celdas de una hoja de cálculo. Así pues, pongamos otro: en la lista de números (B3:B27), queremos saber cuántas veces se repite el número 103 (celda E2).

Obtendremos la primera solución utilizando la función DELTA. La columna H nos servirá para hacer cálculos auxiliares.

En H3:
=DELTA($E$2;B3)     [Extendemos la fórmula hasta la fila 27]

En E3:
=SUMA(H3:H27)     [Resultado: 8]

Parece ser que DELTA no se puede usar con matrices, de modo que fórmulas como:

=DELTA($E$2;B3:B27)    [Terminado con Ctrl + Mayúscula + Intro]

son erróneas. La conclusión es que no se puede prescindir de la columna auxiliar H.

Pero hay, al menos, tres soluciones muy sencillas que no precisan columnas auxiliares.

En E4:
=CONTAR.SI(B3:B27;E2)     [Resultado: 8]

En E5:
=SUMA(--(E2=B3:B27))     [Terminado con Ctrl + Mayúscula + Intro. Resultado: 8]

En E6:
=SUMAPRODUCTO(--(E2=B3:B27))     [Resultado: 8]

Casos habrá en los que resulte ventajoso utilizar DELTA en lugar de otras funciones, pero no se me ocurre ninguno.

Descargar archivo (S11-Delta.xlsx)

Descargar archivo (S11-Delta.xls)

lunes, 15 de abril de 2013

La función AGREGAR

En un artículo antiguo analizamos cómo se puede usar la función SUBTOTALES en lugar de otras funciones. Además, comprobamos que con SUBTOTALES podemos pasar por alto los valores ocultos. Ésta es, a mi modo de ver, la principal razón de su existencia.

En Excel 2010, Microsoft ha añadido la función AGREGAR, que se usa de un modo parecido a SUBTOTALES. Tiene dos sintaxis:

Primera sintaxis: AGREGAR(núm_función; opciones; ref1; [ref2]; …)

Consideremos una lista de valores numéricos en la que hay intercalados varios errores:

En D1:
=SUMA(A1:A11)     [Resultado: #¡DIV/0!]
 
Excel no puede hallar la suma si en el rango de datos hay algún error.
 
La función AGREGAR permite hallar la suma omitiendo los errores. A medida que vayamos escribiendo la fórmula, Excel mostrará las opciones disponibles.
 
En D2, escribimos: =AGREGAR(
 
Elegimos la opción 9 y ponemos punto y coma. Excel abre otro menú:


Seleccionamos la opción 6 y completamos la fórmula:

En D2:
=AGREGAR(9;6;A1:A11)     [Resultado: 462]

La suma se ha realizado correctamente sin tener en cuenta las celdas con errores. Para operar con rangos no contiguos, bastará separarlos con punto y coma. Por ejemplo, si quisiéramos hallar el promedio de los rangos C4:C10, H5:H20 y M2:N40, la fórmula sería: =AGREGAR(1;6;C4:C10;H5:H20;M2:N40)  

El 1 (primer argumento) selecciona la función PROMEDIO mientras que el 6 (segundo argumento) omite los posibles valores de error que contengan los rangos indicados en el tercer, cuarto y quinto argumento.

Segunda sintaxis: AGREGAR(núm_función, opciones, matriz, [k])

Si núm_función está comprendido entre 14 y 19, es necesario añadir el argumento [k]. Esto ocurre porque las funciones correspondientes requieren este segundo argumento. Como ejemplo, vamos a calcular el tercer número mayor del rango A1:B11

En D3:
=AGREGAR(14; 6; A1:B11; 3)     [Resultado: 95]

Si no hubiéramos omitido los errores (segundo argumento igual a 4), Excel devolvería un error. Habría sido lo mismo que escribir: =K.ESIMO.MAYOR(A1:B11;3)

martes, 9 de abril de 2013

Otra forma de poner los rótulos del eje X en dos filas

Recién publicado el último artículo donde se explica cómo poner los rótulos del eje de abscisas en dos o tres filas, se me ha ocurrido otro método que he probado y funciona.

Consiste en crear una nueva columna en la que los elementos de las filas pares vayan precedidos del carácter 10 de la tabla ASCII. Este carácter equivale a un salto de línea, pero no se abre hueco en la celda encima del texto (ignoro la razón).

Vamos a copiar la lista B3:B11 en L3:L11 de la siguiente manera: los elementos de las filas impares los dejamos intactos; los elementos de las filas pares irán precedidos del carácter ASCII 10.

En L3:
=SI(ES.PAR(FILA(B3));CARACTER(10)&B3;B3)    [Extendemos la fórmula hasta la fila 11]

Aunque no lo parezca, en L3:L11, Avellana, Nuez, Pistacho e Higo seco llevan un salto de línea delante.

Ahora, hacemos un gráfico de columnas con los datos del rango B2:D11 y eliminamos la leyenda.

Los rótulos del eje X han quedado inclinados. Hacemos doble clic en uno de ellos para acceder al cuadro de diálogo Dar formato a eje.
  • En la ficha Opciones del eje, seleccionamos el botón de opción Especificar unidad de intervalo y dejamos el valor 1.
  • En la ficha Alineación, escribimos 1 en Ángulo personalizado.

Volvemos a hacer doble clic en un rótulo cualquiera y, en Ángulo personalizado, ponemos un 0. Los rótulos quedan solapados pero ya están totalmente horizontales.

Con el gráfico seleccionado, accedemos a Herramientas de gráficos + Diseño + Seleccionar datos:

Hacemos clic en el botón Editar del apartado Etiquetas del eje horizontal (categoría). Sustituimos el valor de Rango de rótulos del eje por L3:L11.

Pulsamos dos veces Aceptar y el gráfico quedará perfecto. Ya lo podemos estrechar hasta que nos quede del tamaño deseado.

Como es lógico, para poner los rótulos en tres filas será necesario poner dos veces el carácter 10 en las filas correspondientes. La fórmula podría ser la siguiente:

En L3:
=ELEGIR(RESIDUO(FILA(B3);3)+1;B3;CARACTER(10)&B3;CARACTER(10)&CARACTER(10)&B3)     [Extendemos la fórmula hasta la fila 11]

Para que no moleste, podemos crear la columna auxiliar en una zona que quede cubierta por el gráfico (por ejemplo, en G3:G11) o cortarla y pegarla en otra hoja.




lunes, 8 de abril de 2013

Rótulos del eje X en distintas filas

Cuando los rótulos del eje horizontal de un gráfico tienen una longitud grande, para que quepan y se puedan leer, Excel los pone inclinados.

Si estiramos el gráfico conseguiremos que los rótulos queden en posición horizontal, pero a costa de hacer un gráfico muy ancho.

También podemos conseguirlo reduciendo el tamaño de la fuente, pero esto puede dificultar mucho la lectura.

¿Podemos acercar los rótulos entre sí manteniéndolos en posición horizontal? De este modo, conseguiríamos que cupieran en un espacio menor. Vamos a intentarlo.

Seleccionamos B2:D11 y accedemos a Insertar + Columna + Columna agrupada. Con este paso, y eliminando la leyenda, ya tendremos hecho el gráfico con los rótulos inclinados.

Ahora, hacemos doble clic en uno de los rótulos del eje X para entrar en el cuadro de diálogo Dar formato a eje.
  • En la ficha Opciones del eje, seleccionamos el botón de opción Especificar unidad de intervalo y dejamos el valor 1
  • En la ficha Alineación, escribimos 1 en Ángulo personalizado.
Cerramos la ventana y obtendremos la siguiente imagen:

Los rótulos se solapan unos con otros, pero no están totalmente horizontales (les hemos puesto una inclinación de 1º). Volvemos a entrar en el cuadro de diálogo Dar formato a eje y ponemos una inclinación de 0º. El gráfico quedará con los rótulos totalmente horizontales. La razón de dar este rodeo es que poniendo desde el principio 0º no funciona (al menos, a mí no me ha funcionado).

Estirando el gráfico conseguiremos que se vean los rótulos completos. Esta solución puede que no valga si necesitamos crear un gráfico muy estrecho. Por eso, vamos a ver otras soluciones.

El primer truco consiste en escribir normalmente los rótulos de las filas impares del rango B3:B11, y precedidos de un salto de línea los de las líneas pares. Así, Almendra, Cacahuete, Piñón, Dátil seco y Uva Pasa se escribirán normalmente; Avellana, Nuez, Pistacho e Higo seco, irán precedidos de un salto de línea. Para insertar un salto de línea antes (o después) de una palabra, se coloca el cursor en el lugar preciso y se pulsa Alt + Intro. La tabla quedará así:

Ahora, seleccionando B2:D11, hacemos un gráfico de columnas y repetimos los pasos dados en el ejemplo anterior: unidad de intervalo igual a 1, alineación con un ángulo de 1º y, luego, de 0º. Terminado el gráfico, lo estrechamos hasta el tamaño que queramos y obtendremos una imagen similar a la siguiente:

Lógicamente, podemos dar dos saltos de línea pulsando dos veces Alt + Intro. De este modo, Cacahuete y Dátil seco pasarán a la tercera fila.

Hay otro truco que podemos utilizar para poner rótulos en diferentes filas, aunque es más complicado y requiere usar columnas nuevas.

Para poner los rótulos en dos filas creamos una nueva columna a la izquierda de la tabla y alternamos los rótulos entre las dos columnas de la izquierda. Con la nueva tabla creamos el gráfico.

Los rótulos de la segunda fila no quedan centrados. La solución es sencilla: basta escribir un espacio en blanco en las celdas vacías que hay debajo de Avellana, Nuez, Pistacho e Higo seco. Después, si es necesario, podemos reducir la anchura del gráfico.

Análogamente, necesitaremos una tercera columna para poner los rótulos en tres filas.

Si nos molestan las líneas, las quitamos (o las ponemos de un color tenue) haciendo doble clic en una de ellas y eligiendo las propiedades adecuadas en las pestañas Color de línea y Estilo de línea.

Descargar archivo (S105-Frutos secos.xlsx)

Descargar archivo (S105-Frutos secos.xls)

lunes, 1 de abril de 2013

Permutaciones con repetición

Vamos a calcular de cuántas formas se puede crear un mosaico cuadrado con 96 baldosines verdes, tres granate y uno azul. Todos los baldosines asimétricos deben estar igualmente orientados.

Se trata de un problema de matemática combinatoria. El número total de mosaicos son las permutaciones con repetición de 100 elementos, de los cuales, uno se repite 96 veces, otro, tres veces, y el último aparece una sola vez.

Si ningún baldosín tuviera un identificador que lo hiciera asimétrico, habría que dividir el resultado por cuatro. Se comprende mejor comparando estos cuatro mosaicos:

Los cuatro mosaicos deben contabilizarse como uno, ya que girando cualquiera de ellos 90º, 180º o 270º, coincide con otro. Sin embargo, con baldosines asimétricos no ocurre esto:

Por ejemplo, al girar 90º el primer mosaico, las letras quedarán giradas y ya no coincidirá exactamente con el segundo mosaico; son dos mosaicos distintos.

Pongamos todo en una hoja de cálculo. En O3:O5 ponemos los baldosines que hay de cada color, y debajo, en O6, la suma. En O8 calcularemos el número total de mosaicos posibles.

En O8:
=FACT(O6)/(FACT(O3)*FACT(O4)*FACT(O5))     [Resultado: 15.684.900]

Todo parece ir bien, pero si en vez construir mosaicos de 10 x 10 los hacemos de 15 x 15, tendremos problemas. En este caso, el número total de baldosines por mosaico será de 225 y al calcular el factorial de 225 se producirá un error por desbordamiento. 

Hay que aplicar logaritmos, como se explicó en el artículo Combinatoria: Variaciones. El desarrollo matemático es como sigue:

Tomamos logaritmos:

Calculamos el antilogaritmo de la expresión anterior.

Desarrollando el exponente:

Ahora, tenemos que crear cuatro nombres accediendo a Fórmulas + Administrador de nombres:

 Verde  =FILA(DESREF(Hoja1!$A$1;;;Hoja1!$O$3))
 Granate  =FILA(DESREF(Hoja1!$A$1;;;Hoja1!$O$4))
 Azul  =FILA(DESREF(Hoja1!$A$1;;;Hoja1!$O$5))
 Baldosines  =FILA(DESREF(Hoja1!$A$1;;;Hoja1!$O$6))

Finalmente, sustituimos la fórmula de O8 por la siguiente:

En O8:
=10^(SUMAPRODUCTO(LOG10(Baldosines))-SUMAPRODUCTO(LOG10(Verde))-SUMAPRODUCTO(LOG10(Granate))-SUMAPRODUCTO(LOG10(Azul)))     [Resultado: 415.850.400]

Descargar archivo (S105-Mosaico.xlsx)

Descargar archivo (S105-Mosaico.xls)