jueves, 29 de septiembre de 2011

Marcar la celda si se encuentra en ella una palabra o fragmento

Descargar el fichero: busca_fragmento.xlsm


Al buscar una palabra o un fragmento en Excel podemos utilizar Control+B y así buscaremos a mano, de uno en uno. Pero si deseamos buscar un fragmento que pudiera estar dentro de una celda, y deseamos hacer la búsqueda de forma masiva necesitaremos una pequeña macro que nos ayude. En este caso creamos la macro y la utilizamos junto con Formato Condicional para marcar las celdas que incluyen ese fragmento y que queden visibles con un cierto color. De esta forma localizaremos rápidamente y de forma visual las celdas que contienen el fragmento o palabra que deseamos buscar.


En la celda amarilla indicamos la palabra que deseamos buscar.

La función que hemos creado se llama =POSICION y tiene los siguientes argumentos:

=POSICION(texto;palabra;exacto)

donde:
  • texto, indica la celda donde está el texto que se quiere buscar
  • palabra, indica la palabra o fragmento que se desea buscar
  • exacto, es un argumento opcional. Si no se pone nada o se pone FALSO, o se pone 0, la búsqueda no será exacta, y por lo tanto no será sensible a mayúsculas y minúsculas. Por el contrario, si se pone la palabra VERDADERO, o se pone 1, la búsqueda será exacta y entonces si requiere que las mayúsculas, y minúsculas se cumplan escrupulosamente
La clave de todo está en la función de VBA denominada InStr que nos da el número de carácter que ocupa el primer carácter de la palabra buscada dentro del texto. Si da cero es que la palabra no se encuentra.

En el formato condicional lo que pedimos es que ese número de caracteres sea mayor que cero, lo cual indicará que la palabra buscada si se encuentra en el texto y que por lo tanto se ha de marcar de color verde la celda.

La macro es la siguiente.


viernes, 2 de septiembre de 2011

Último valor de la fila

Descargar el fichero: ultimo.xlsx

Puede llegar a se muy útil determinar cuál es el último valor de una fila o columna. Para lograrlo utilizaremos funciones matriciales. En esta ocasión veremos dos hojas en las que planteamos dos casos en los que se emplean estas fórmulas matriciales que calculan el último valor escrito en una fila.

Hoja1

Veamos cómo se calcula con una fórmula matricial el último valor de una fila.


La celda J16 es:

{=INDICE((B16:H16);;MAX(SI((B16:H16)<>"";COLUMNA(B16:H16)))-1}

Recuerde que las llaves {} que rodean la fórmula indican que se trata de una función matricial y el usuario no ha de escribirlas, ya que de ello se encarga Excel al validar la fórmula pulsando CONTROL+MAYUSCULAS+INTRO.

Puede ampliar información sobre las funciones matriciales en el siguiente enlace: Fórmulas Matriciales.

También puede consultar el penúltimo o anteúltimo valor de la fila.


Para ello en la celda K16 empleamos la fórmula matricial siguiente:

{=INDICE((B16:H16);K.ESIMO.MAYOR(((B16:H16)<>"")*(COLUMNA(B16:H16));2)-1)}

La función K.ESIMO.MAYOR y la función K.ESIMO.MENOR se utilizan mucho en este tipo de cálculos.

Hoja2


Veamos una aplicación práctica de las ideas recogidas en la Hoja1. Disponemos de las ventas mensuales durante los últimos años. Actualmente nos encontramos en el año 2011 y el último mes del que tenemos información es Julio. Esto se puede observar viendo la tabla de datos.


Con el objeto de poder comparar adecuadamente los datos acumulados de los meses que llevamos de 2011 con los de años anteriores, lo que deseamos es crear la columna Q en la que obtengamos el acumulado de las ventas de todos los años pero únicamente hasta el mes de julio, que es el último mes del que tenemos información en el año 2011.

Lo importante es que deseamos que esto se recalcule de forma automática y que cuando sea agosto el último mes del que tengamos información automáticamente sea éste el mes hasta el que se acumulen en la columna Q los valores de ventas de todos los años.

Para conseguir esto hemos tenido que dar una serie de pasos.

Paso 1

En el rango D4:O4 hemos creado la serie de números del 1 al 12. Para que no se vean hemos utilizado un truco que consiste en dar formato a esas celdas del tipo formato número, personalizado y en tipo indicar "". Esto lo que hace es evitar que el valor de esa celda se vea.



Paso 2

La columna C se encuentra oculta. Esta es una columna auxiliar necesaria. En ella lo que calculamos es el último valor escrito en la fila. Así en la celda C8 obtenemos el valor 7 ya que el último valor escrito en el rango D8:O8 es el del mes de julio. La fórmula matricial de la celda C8 es la siguiente.

{=MAX(SI((D8:O8)<>"";COLUMNA(D8:O8)))-3}

Paso 3

En la celda Q4 escribimos la siguiente fórmula matricial.

{=+MIN(SI((C6:C10)>0;(C6:C10)))}

Esta fórmula calcula el último mes escrito que en nuestro caso es el mes de julio. En la celda figura el valor 7, que se corresponde con julio. El número 7 no se ve ya que hemos utilizado en la celda un formato de número personalizado indicando en tipo "


Paso 4

En la celda Q5 deseamos que aparezca una indicación del mes hasta el que se acumula. En este caso vemos que pone "Hasta julio", esto se consigue con la siguiente fórmula.

=FECHA(2011;Q4;1)

Paso 5

En la celda Q6 tenemos la fórmula matricial siguiente.

{=SUMA((D6:O6)*(--(($D$4:$O$4)<=$Q$4)))}

De esta forma, conseguimos que, de forma automática, al ir escribiendo los datos de los sucesivos meses, los acumulados se adapten y muestren la cifra de ventas, justo hasta el mes último del que tenemos datos en el años actual.


Actualización (a 4 sep. 2011)

Para evitar el paso 1, evitando escribir en la fila 4 los números de 1 al 12, y luego ocultarlos, vamos a proponer un cambio en la fórmula de la celda Q6.

{=SUMA((D6:O6)*({1;2;3;4;5;6;7;8;9;10;11;12}<=$Q$4))}