lunes, 26 de noviembre de 2012

Contar el número de palabras que hay en un texto

En un texto, todas las palabras están separadas entre sí por espacios en blanco. Es cierto que, además, pueden existir paréntesis, corchetes, llaves, comillas, guiones, signos de interrogación y exclamación, signos de puntuación, etc. Pero, siempre hay un espacio en blanco antes de la siguiente palabra. Además, no hay espacios entre las palabras y los signos mencionados.

Así pues, para saber el número de palabras que hay en un texto basta contar el número de espacios en blanco y sumarle uno.

En B3 pondremos el texto y en E2 calcularemos el número de palabras que contiene. Usaremos las filas 6 a 10 para hacer cálculos intermedios. Finalizado el ejercicio, podremos borrar estas filas ya que crearemos una fórmula compuesta en E2.

Resolveremos el ejercicio en cinco pasos:
  1. Nos aseguramos de que entre las palabras haya un espacio y sólo uno. Este paso lo damos porque, en ocasiones, pulsamos, sin querer, más de una vez la barra espaciadora para separar las palabras.
  2. Calculamos el número de caracteres (incluidos los espacios) que tiene el texto.
  3. Eliminamos todos los espacios.
  4. Calculamos el número de caracteres del nuevo texto (que ya no tiene espacios).
  5. Restamos ambos valores y le sumamos uno.
La función ESPACIOS elimina todos los espacios sobrantes que hay en una frase. Quita los espacios que hay al principio o al final del texto y deja un único espacio entre las palabras.

En C6:
=ESPACIOS(B3)

La función LARGO devuelve el número de caracteres de una cadena de texto.

En C7:
=LARGO(C6)

Con SUSTITUIR eliminamos todos los espacios.

En C8:
=SUSTITUIR(C6;" ";"")

Calculamos los caracteres de la nueva cadena de texto.

En C9:
=LARGO(C8)

El resultado, en C10, es la diferencia más uno.

En C10:
=C7-C9+1

Ahora, sólo es necesario crear la fórmula compuesta en E2 y borrar las filas 6 a 10.

En E2:
=LARGO(ESPACIOS(B3))-LARGO(SUSTITUIR(B3;" ";""))+1




jueves, 15 de noviembre de 2012

Operar con condiciones

En este ejercicio vamos a realizar cálculos que en sí mismos no tienen demasiado interés, pero que quizás puedan servirnos en un proyecto futuro. Consiste en hallar sumas o contar valores con condiciones poco habituales.

En la columna B tenemos los datos con los que vamos a hacer los cálculos. En D2:E14 pondremos los resultados. Las columnas que van de la G a la O las utilizaremos para obtener valores intermedios. Finalizado el ejercicio, podremos borrarlas.

Sumar los cinco números mayores

Para no complicar el ejercicio consideraremos que no hay números repetidos en la columna B.

Para sumar los cinco números más grandes, primero los aislaremos y luego los sumaremos. Bastarán tres pasos:
  1. Si el número está entre los cinco mayores, lo marcamos como VERDADERO. En caso contrario lo marcamos como FALSO.
  2. Una vez marcados todos los números, al que está marcado con VERDADERO le asignamos su propio valor; al que está marcado con FALSO le asignamos el valor cero.
  3. Ahora, sólo hay que sumar la lista de números
Seleccionamos G3:G20 y escribimos:
=JERARQUIA.EQV(B3:B20;B3:B20;0)<6    [Terminamos con Ctrl + Mayús + Intro]

Ya tenemos la lista de valores VERDADEROS y FALSOS.

La función JERARQUIA.EQV se introdujo en Excel 2010 para sustituir a la función JERARQUIA de Excel 2003 y 2007. El tercer argumento (0 ó 1) de la función permite considerar un orden descendente (0 u omitido) o ascendente (1) de la lista. En el ejemplo, se ha utilizado el orden descendente.

Nota: Por razones de compatibilidad, Excel 2010 conserva la función JERARQUIA.

Seleccionamos H3:H20 y escribimos:
=(B3:B20)*(G3:G20)    [Terminamos con Ctrl + Mayús + Intro]

Hemos multiplicado la columna B por la columna G. Si recordamos que VERDADERO y FALSO equivalen respectivamente a 1 y a 0, se comprende que los cinco mayores conserven su valor y el resto se conviertan en cero.

La suma la pondremos en E3:
=SUMA(H3:H20)    [Resultado 466]

Como una vez acabado el ejercicio queremos borrar las columnas auxiliares, debemos crear una fórmula compuesta en E3. ¿Cómo se hace? Muy sencillo.

Editamos la fórmula de la celda E3 pulsando la tecla F2. Si nos resulta más cómodo, podemos hacer las modificaciones en la barra de fórmulas.

En la fórmula =SUMA(H3:H20) sustituimos H3:H20 por la fórmula que hemos puesto en la columna H. El resultado será: =SUMA((B3:B20)*(G3:G20)). La fórmula se ha convertido en una fórmula matricial y hay que acabar con Ctrl + Mayús + Intro.

Ahora, sustituimos G3:G20 por la fórmula de la columna G. El resultado final será:
=SUMA((B3:B20)*(JERARQUIA.EQV(B3:B20;B3:B20;0)<6))    [Terminamos con Ctrl + Mayús + Intro]

Ya podemos borrar las columnas G y H.

Si no nos gustan las fórmulas matriciales bastará sustituir SUMA por SUMAPRODUCTO.

En E4:
=SUMAPRODUCTO((B3:B20)*(JERARQUIA.EQV(B3:B20;B3:B20;0)<6))    [Terminamos con Intro]

Sumar los cinco números menores

Es lo mismo sumar los cinco mayores o los cinco menores. Basta sustituir por 1 el tercer argumento de la función JERARQUIA.EQV.

En E5:
=SUMA((B3:B20)*(JERARQUIA.EQV(B3:B20;B3:B20;1)<6))    [Terminamos con Ctrl + Mayús + Intro]

En E6:
=SUMAPRODUCTO((B3:B20)*(JERARQUIA.EQV(B3:B20;B3:B20;1)<6))    [Terminamos con Intro]

Sumar los números impares

Tenemos que aislar los impares y sustituir los pares por ceros. Lo primero que se me ocurre es utilizar la función ES.IMPAR para determinar los impares, pero no funciona con matrices. Lo comprobamos:

Seleccionamos J3:J20 y escribimos:
=ES.IMPAR(B3:B20)    [Terminamos con Ctrl + Mayús + Intro]

Excel devuelve un error.

ES.IMPAR funciona correctamente si en J3 escribimos: =ES.IMPAR(B3) y, luego, extendemos la fórmula hasta la fila 20. Mejor aún, si en J3 escribimos: =ES.IMPAR(B3)*B3 y copiamos la fórmula hacia abajo, habremos aislado todos los impares. Sólo habrá que sumarlos y el ejercicio estará resuelto. Pero nuestra intención es prescindir de las celdas auxiliares y escribir una única fórmula que nos devuelva el resultado correcto. Esto sólo se puede hacer si utilizamos fórmulas matriciales. Habrá que ir por otro camino.

Seleccionamos J3:J20 y escribimos:
=(RESIDUO(B3:B20;2)=1)*(B3:B20)     [Terminamos con Ctrl + Mayús + Intro]

Ya están aislados todos los impares.

En E7:
=SUMA(J3:J20)     [Resultado: 340]

Como en el ejemplo anterior, volvemos a crear una fórmula compuesta en E5:
=SUMA((RESIDUO(B3:B20;2)=1)*(B3:B20))     [Terminamos con Ctrl + Mayús + Intro]

En E8 usamos SUMAPRODUCTO:
=SUMAPRODUCTO((RESIDUO(B3:B20;2)=1)*(B3:B20))     [Terminamos con Intro]

Ya podemos borrar la columna J.

Sumar los números pares

La única diferencia con el caso anterior es que el resto de la división por 2 es cero.

En E9:
=SUMA((RESIDUO(B3:B20;2)=0)*(B3:B20))     [Terminamos con Ctrl + Mayús + Intro]

En E10:
=SUMAPRODUCTO((RESIDUO(B3:B20;2)=0)*(B3:B20))     [Terminamos con Intro]

Sumar los múltiplos de tres

No hay ninguna diferencia con los dos casos anteriores. Al dividir un número por 3 dará resto cero si es múltiplo de 3.

En E11:
=SUMA((RESIDUO(B3:B20;3)=0)*(B3:B20))     [Terminamos con Ctrl + Mayús + Intro]

En E12:
=SUMAPRODUCTO((RESIDUO(B3:B20;3)=0)*(B3:B20))     [Terminamos con Intro]

Contar los números que no son múltiplos de tres

Primero aislaremos los números no múltiplos de tres y luego los sustituiremos por unos. Finalmente, sumaremos todos los unos que hayamos obtenido y el ejercicio estará resuelto.

Seleccionamos N3:N20 y escribimos:
=(RESIDUO(B3:B20;3)<>0)*(B3:B20)     [Terminamos con Ctrl + Mayús + Intro]

Seleccionamos O3:O20 y escribimos:
=--NO(N3:N20=-N3:N20)     [Terminamos con Ctrl + Mayús + Intro]

En E13:
=SUMA(O3:O20)     [Resultado: 10]

Ahora, creamos la fórmula compuesta sustituyendo la fórmula de E13 por ésta:
=SUMA(--NO((RESIDUO(B3:B20;3)<>0)*(B3:B20)=-(RESIDUO(B3:B20;3)<>0)*(B3:B20)))     [Terminamos con Ctrl + Mayús + Intro]

En E14:
=SUMAPRODUCTO(--NO((RESIDUO(B3:B20;3)<>0)*(B3:B20)=-(RESIDUO(B3:B20;3)<>0)*(B3:B20)))     [Terminamos con Intro]




lunes, 12 de noviembre de 2012

Mostrar en un gráfico los datos comprendidos entre dos fechas

Si durante un largo período de tiempo se han registrado las ventas semanales de una empresa, tendremos una lista de valores de varios cientos de filas. No tiene sentido representar estos valores en un único gráfico porque no se apreciará nada. Por ejemplo, con los datos de las trescientas semanas registradas del ejercicio que vamos a desarrollar, he hecho un gráfico de líneas y me ha quedado así:
Este gráfico no sirve. Pero, si representamos únicamente las ventas de 10 semanas consecutivas, el resultado es muy representativo:
Veamos cómo podemos extraer los datos de 10 semanas elegidos por el usuario y representarlos en forma gráfica mediante un diagrama de columnas o de líneas.

El usuario elegirá la semana inicial (en H2) y, a partir de esa semana, se acotarán los datos de las 10 semanas siguientes.
Comenzaremos creando dos nombres: Fecha1 y Ventas1

Accedemos a Fórmulas + Asignar nombre y creamos Fecha1 con la siguiente definición: =DESREF(Hoja1!$C$3;Hoja1!$H$2-1;0;10;1)
Aceptamos y pulsamos el botón Nuevo para crear Ventas1: =DESREF(Hoja1!$E$3;Hoja1!$H$2-1;0;10;1)
Pulsamos en una celda vacía y accedemos a Insertar + Columna + Columna agrupada. Como no hemos seleccionados ningún dato, el gráfico será un marco vacío. Con la tecla Alt pulsada, lo ajustamos hasta que ocupe el rango G4:L18.

Con el gráfico seleccionado, vamos a Herramientas de gráficos + Diseño + Seleccionar datos, pulsamos el botón Agregar del panel Entrada de leyenda (Series) y ponemos estos valores:
Hacemos clic en el botón el botón Editar del panel Etiquetas del eje horizontal (categoría) y rellenamos el cuadro de diálogo así:
El gráfico nos ha quedado de esta manera:
Eliminamos la leyenda y hacemos doble clic en uno de los rótulos del eje horizontal para entrar en el cuadro de diálogo Dar formato al eje. En Opciones de eje, marcamos el botón Eje de texto del apartado Tipo de eje y pulsamos Cerrar.

Eliminamos las líneas de división principales haciendo clic en una cualquiera y pulsando Supr.

Para aumentar el grosor de las columnas, hacemos doble clic en una de ellas y, en Opciones de serie, ponemos el ancho del intervalo en 80%. Sin salirnos de este cuadro de diálogo, podemos cambiar el color de las columnas eligiendo Relleno en el panel de la izquierda y Relleno sólido en el panel de la derecha; el color lo elegiremos en Color de relleno.También podemos eliminar el Color del borde.

Para que se muestren los valores de las columna, hacemos clic con el botón derecho en una cualquiera y, en el menú emergente, seleccionamos Agregar etiqueta de datos.
Podemos comprobar que al cambiar el valor de H2 el gráfico se adapta correctamente.

Si queremos que el usuario decida el número de columnas del gráfico, será necesario habilitar una celda para que introduzca ese dato; por ejemplo en H3.
El gráfico se hace igual que el anterior pero, si lo vamos a crear en la Hoja3, los nombres Fecha1 y Ventas1 deben sustituirse por Fecha3 y Ventas3, cuyas definiciones deberán ser:

 Fecha3  =DESREF(Hoja3!$C$3;Hoja3!$H$2-1;0;Hoja3!$H$3;1)
 Ventas3  =DESREF(Hoja3!$E$3;Hoja3!$H$2-1;0;Hoja3!$H$3;1)

También podemos poner una barra de desplazamiento u otro control para modificar los valores de la fila inicial y el número de semanas que deseamos mostrar en el gráfico.
En el ejemplo, los cambios se hacen con barras de desplazamiento (de formulario o ActiveX) y con un control de número (ActiveX). Naturalmente, se puede prescindir de los controles y escribir los datos directamente en las celdas H2 y H4.

Será necesario crear otros dos nuevos nombres: Fecha2 y Ventas2.

 Fecha2  =DESREF(Hoja2!$C$3;Hoja2!$H$2-1;0;10;1)
 Ventas2  =DESREF(Hoja2!$E$3;Hoja2!$H$2-1;0;10;1)

Accedemos a Programador + Insertar + Barra de desplazamiento (control de formulario) y, con la tecla Alt pulsada, insertamos el control en J2:M2. Ahora, en el menú contextual, seleccionamos Formato de control y ponemos los valores siguientes:
De este modo, hemos vinculado el valor asociado a la barra con la celda H2. Además, como hay 300 filas, el desplazamiento de la barra lo hemos restringido al rango 1 a 299.

Debajo, en J4:M4 ponemos otra barra igual con estas propiedades:
La barra estará vinculada con la celda H4 y podrá tomar valores entre 5 y 20; es decir, el número de datos que mostrará el gráfico estará comprendido entre esos valores.

En G6:M20, hacemos un gráfico igual que en los dos casos anteriores pero eligiendo Línea con marcadores en vez de Columna. Lo adornamos como queramos y comprobamos que las barras de desplazamiento funcionan correctamente.

Para ver cómo se usan los controles ActiveX ponemos dos barras de desplazamiento en G22:K22 y en G24:K24. Para hacerlo hay que acceder a Programador + Insertar + Barra de desplazamiento (control ActiveX). Cuando lo hagamos quedará activado el Modo Diseño:
En el menú contextual de la primera barra seleccionamos Propiedades y ponemos los valores que están marcados en rojo:
En la segunda, las propiedades deberán ser:
Terminamos haciendo clic en Modo Diseño para desactivarlo.

Para finalizar el ejercicio, insertamos dos Controles de número (control ActiveX) en M21 y M23. Las propiedades de ambos serán:

Todos los controles deberán funcionar correctamente.