viernes, 30 de diciembre de 2011

Función INDICE. Forma de referencia

En la entrada anterior hemos utilizado la función INDICE en la "forma matricial". Esta función tiene una segundo modo de utilización: la "forma de referencia".

Las inversiones realizadas por una empresa entre los años 2007 y 2011 en sus sucursales de Europa, América y Asia han sido clasificadas por departamentos. Eligiendo un continente, un año y un departamento, debemos obtener la cifra invertida con una fórmula.

El ejercicio se resuelve facilmente usando la función INDICE en su forma de referencia, cuya sintaxis es:

INDICE(referencias;núm_fila;[núm_columna];[núm_área])

  • referencias son los distintos rangos en los que hay que realizar la búsqueda. Si son más de uno, se escriben separados por punto y coma y, todo ello, encerrado entre paréntesis.
  • núm_fila es la fila en la que hay que buscar.
  • núm_columna es la columna en la que hay que buscar.
  • núm_área es el rango (el área) en el que hay que realizar la búsqueda. La primera área es la 1; la segunda, la 2; la tercera, la 3, etc.

En J2 elegimos un continente; por ejemplo, Asia

En J3 elegimos el departamento: Marketing

En J4, el año: 2010

En J6:
=INDICE((B3:G9;B12:G18;B21:G27);COINCIDIR($J$3;$B$3:$B$9;0);COINCIDIR($J$4;$B$3:$G$3;0);COINCIDIR($J$2;{"Europa";"América";"Asia"};0))

  • (B3:G9;B12:G18;B21:G27), primer argumento, especifica las tres tablas en un orden concreto: Europa (que es el área 1), América (área 2) y Asia (área 3).
  • COINCIDIR($J$3;$B$3:$B$9;0) devuelve el número de la fila (de cualquiera de las tablas) donde se encuentra el departamento buscado.
  • COINCIDIR($J$4;$B$3:$G$3;0) devuelve el número de la columna (de cualquiera de las tablas) donde se encuentra el año buscado.
  • COINCIDIR($J$2;{"Europa";"América";"Asia"};0) devuelve un número del 1 al 3. En el ejemplo, como J2 contiene Asia, devuelve 3. Éste es el área donde se realizará la búsqueda.

 


jueves, 29 de diciembre de 2011

Seis métodos de búsqueda

Una operación frecuente en Excel es la búsqueda de datos en una tabla. Por ejemplo, con los datos de la tabla siguiente, queremos saber la cantidad de guantes vendidos en el mes de octubre.


En C15 escribimos el mes: Octubre

En C16 escribimos el nombre del producto: Guantes

Ahora, pondremos 6 fórmulas diferentes para hallar los guantes que se vendieron en octubre.

1ª fórmula: BUSCARV combinada con COINCIDIR

En C18:
=BUSCARV(C15;B2:F13;COINCIDIR(C16;B2:F2;0);FALSO)

2ª fórmula: BUSCARH combinada con COINCIDIR

En C19:
=BUSCARH(C16;B2:F13;COINCIDIR(C15;B2:B13;0))

3ª fórmula: INDICE combinada con COINCIDIR

En C20:
=INDICE(C3:F13;COINCIDIR(C15;B3:B13;0);COINCIDIR(C16;C2:F2;0))

4ª fórmula: SUMAPRODUCTO

En C21:
=SUMAPRODUCTO((B3:B13=C15)*(C2:F2=C16)*(C3:F13))

5ª fórmula (matricial): SUMA

En C22:
=SUMA((B3:B13=C15)*(C2:F2=C16)*(C3:F13))   [Terminar con Ctrl + Mayús + Intro]

6ª fórmula: DIRECCION, INDIRECTO y COINCIDIR

En C23:
=INDIRECTO(DIRECCION(COINCIDIR(C15;B1:B13;0);COINCIDIR(C16;A2:F2;0)))

Consideraciones: 

  • BUSCARV sólo se puede utilizar si la columna de los meses es la primera. Del mismo modo, BUSCARH requiere que la lista de productos esté en la primera fila. No se podrán utilizar en la tabla siguiente:

  • INDICE se puede utilizar en cualquier circunstancia.
  • SUMAPRODUCTO y SUMA sólo se pueden utilizar si la tabla contiene valores numéricos, ya que hemos hecho una operación matemática (que requiere números).
  • DIRECCION + INDIRECTO se pueden utilizar en cualquier circunstancia. Conviene recordar que INDIRECTO es una función volátil.

 
Descargar archivo (SE2-Seis Búsquedas.xlsx)

miércoles, 28 de diciembre de 2011

Acumular las compras por cliente

Los datos de compras de cinco clientes entre los años 2008 a 2011 han sido puestos en una hoja de cálculo en el rango B2:D24. Deseamos obtener las ventas totales de cada cliente y las acumuladas en cada año (F2:K7).


El primer paso consistirá, con ayuda de Filtro avanzado, en colocar la lista de clientes en la columna F. Para ello, seleccionamos B2:B24 y accedemos a Datos + Avanzadas.

Excel mostrará el cuadro de diálogo Filtro avanzado. Lo rellenamos indicando que queremos copiar, en otro lugar, sólo registros únicos. El lugar elegido será la celda F2.

El resultado será una lista desordenada.

Para ordenarla, seleccionamos F2:K7 y accedemos a Datos + Ordenar. En el cuadro de diálogo Ordenar, dejamos los valores por defecto y pulsamos Aceptar.

La lista habrá quedado ordenada por la columna Comprador.

Las ventas acumuladas de cada cliente (columna G) pueden obtenerse con distintas fórmulas: matriciales y no matriciales. Estudiaremos cuatro soluciones:

1ª solución (no matricial):  Con la función SUMAR.SI(rango;criterios;[rango_suma])

En G3:
=SUMAR.SI($B$3:$B$24;F3;$D$3:$D$24)

La función SUMAR.SI tiene tres argumento: el primero es el rango de datos que deben cumplir un criterio para que puedan ser admitidos; el segundo es el criterio propiamente dicho (los datos del primer argumento que no cumplan este criterio serán desechados); el tercero es el rango que se debe sumar si se cumple el criterio.

Extendemos la fórmula hasta la fila 7.

2ª solución (matricial): Con la función SUMA combinada con SI

En G3:
=SUMA(SI($B$3:$B$24=F3;$D$3:$D$24))     [Terminar con Ctrl + Mayúscula + Intro]

Únicamente se suman los datos de la columna D si en la misma fila de la columna B está el mismo dato que hay en F3.

Extendemos la fórmula hasta la fila 7.

3ª solución (matricial): Con la función SUMA

En G3:
=SUMA(($B$3:$B$24=F3)*($D$3:$D$24))     [Terminar con Ctrl + Mayúscula + Intro]

($B$3:$B$24=F3) devuelve una matriz de valores VERDADERO y FALSO. Lo comprobamos seleccionando M3:M24, escribiendo: =($B$3:$B$24=F3) y terminando con Ctrl + Mayúscula + Intro. A efectos numéricos, VERDADERO es 1 y FALSO es 0.

($B$3:$B$24=F3)*($D$3:$D$24) es el producto de dos matrices: una de VERDADEROS y FALSOS; la otra, es la matriz de la columna D. El resultado será una matriz de ceros (cuando el dato de la primera matriz es FALSO) y valores de la columna D (cuando es VERDADERO). Para comprobarlo, seleccionamos N3:N24, escribimos: =($B$3:$B$24=F3)*($D$3:$D$24) y terminamos con Ctrl + Mayúscula + Intro.

Finalmente, sumamos esta última columna con SUMA. Estamos trabajando con matrices, por lo que debemos terminar con Ctrl + Mayúscula + Intro. Sólo faltará extender la fórmula hasta la fila 7.

4ª solución (no matricial): Con la función SUMAPRODUCTO

En G3:
=SUMAPRODUCTO(($B$3:$B$24=F3)*($D$3:$D$24))

La fórmula es igual que la anterior, salvo que en vez de SUMA hemos puesto SUMAPRODUCTO. Esta función tiene la virtud de convertir fórmulas matriciales en no matriciales, por tanto, se termina con Intro. Como en los casos anteriores, extendemos la fórmula hasta la fila 7 para completar los totales acumulados de todos los compradores.

Las ventas por año de cada cliente (columnas H, I, J y K) también se pueden obtener de distintas maneras. Cualquiera de las fórmulas siguientes es válida:

En H3:

1ª fórmula:
=SUMAPRODUCTO(($B$3:$B$24=$F3)*(AÑO($C$3:$C$24)=H$2)*($D$3:$D$24))      [Pulsar Intro]

2ª fórmula:
=SUMA(SI($B$3:$B$24=$F3;SI(AÑO($C$3:$C$24)=H$2;$D$3:$D$24;0);0))     [Pulsar Ctrl + Mayús + Intro]

3ª fórmula:
=SUMA(($B$3:$B$24=$F3)*(AÑO($C$3:$C$24)=H$2)*($D$3:$D$24))     [Pulsar Ctrl + Mayús + Intro]

En los tres casos el resultado es cero ya que el primer cliente, Aitor, no ha realizado ninguna compra en el año 2008.

Extendiendo la fórmula al rango H3:K7 el ejercicio queda terminado. Se puede comprobar que, para cada comprador, la columna Acumulado es la suma de las compras realizadas durante los años 2008 a 2011.

Descargar archivo (SE1-Ventas acumuladas.xls)

Descargar archivo (SE1-Ventas acumuladas.xlsx)

lunes, 26 de diciembre de 2011

Días laborables

Si un operario tiene que realizar un trabajo durante 84 días laborables, empezando el 26 de diciembre de 2011, ¿qué día acabará, teniendo en cuenta que no trabaja los fines de semana ni los festivos?

El problema es muy sencillo de resolver utilizando la función DIA.LAB, cuya sintaxis es:

DIA.LAB(fecha_inicial;días_laborables;[festivos])

En nuestro ejemplo, la fecha_inicial sería el 26/12/2011; días_laborables, serían 84; festivos (opcional), sería la lista de días no laborables (excluidos los fines de semana) que hay entre diciembre de 2011 y mayo de 2012 (en ese intervalo hay más de 84 días). Poniéndolo todo en una hoja de cálculo quedaría así:

La fórmula de C4 es: =DIA.LAB(C2;C3;C6:C13)

Si no ponemos los datos en la hoja, utilizaremos la fórmula: =DIA.LAB("26/12/2011";84;{"6/12/2011";"8/12/2011";"6/1/2012";"5/4/2012";"6/4/2012";"9/4/2012";"30/4/2012";"1/5/2012"})

La función DIA.LAB considera no laborables los fines de semana y los festivos que explícitamente señalemos. 

Ahora, consideremos otro caso. El operario al que hemos hecho referencia trabaja los sábados pero no trabaja los domingos ni los lunes.

En este caso, no podemos utilizar la función DIA.LAB como lo hemos hecho en el ejemplo anterior. Afortunadamente, Microsoft ha introducido una nueva función en Excel 2010 para resolver este caso. La función se llama DIA.LAB.INTL y su sintaxis es:

DIA.LAB.INTL(fecha_inicial;días_laborables;[fin_de_semana];[festivos])

Como se observa, el tercer argumento permite elegir qué días se consideran fin de semana, según la tabla:

El argumento para nuestro caso es 2. Por tanto, la fórmula de C4 será: =DIA.LAB.INTL(C2;C3;2;C6:C13)

En vez de usar los números de la lista anterior, podemos poner una cifra de 7 ceros y unos. El cero indica que el día es laborable, mientras que el uno indica que es festivo. Se cuenta de lunes a domingo. De este modo, "1000001", indica que el lunes y el domingo son no laborables. Con este convenio, la fórmula de C4 queda así: =DIA.LAB.INTL(C2;C3;"1000001";C6:C13)

Lógicamente, el resultado no cambia.

Si sólo se trabaja los martes, jueves y sábados, la fórmula será: =DIA.LAB.INTL(C2;C3;"1010101";C6:C13)

"1111111" no es una cadena válida, ya que su uso implicaría que no se trabajase ningún día.

Planteemos otra situación. Un trabajo se ha empezado el día 20 de septiembre de 2011 y se ha terminado el 27 de diciembre de 2011. ¿Cuántos días se han trabajado? Naturalmente, no contaremos los fines de semana (que no se trabaja) ni los días festivos.

Como en los ejemplos anteriores hay una función para realizar este cálculo: DIAS.LAB

DIAS.LAB(fecha_inicial; fecha_final; [festivos])

La fecha_inicial es el día que se empezó el trabajo (20/09/2011); la fecha_final, el día que se terminó (27/12/2011); festivos son los días no laborables excluidos los sábados y domingos (que no se trabaja).

La fórmula de C4 debe ser: =DIAS.LAB(C2;C3;C6:C17)

En Excel 2010, también existe la función DIAS.LAB.INTL para el caso en que los fines de semana no sean el sábado y el domingo.

DIAS.LAB.INTL(fecha_inicial; fecha_final; [fin_de_semana]; [festivos])

Por ejemplo, si los días que no se trabaja son el jueves y el viernes, la fórmula será: =DIAS.LAB.INTL(C2;C3;"0001100";C6:C17)

Como jueves y viernes son dos días consecutivos, podemos utilizar el número 6 como tercer argumento de la función DIAS.LAB.INTL. En ese caso, la fórmula será: =DIAS.LAB.INTL(C2;C3;6;C6:C17)

viernes, 9 de diciembre de 2011

Funciones volátiles

En Excel las funciones pueden ser "volátiles" o "no volátiles". Las volátiles se recalculan cuando se abre el libro o cuando se modifica cualquier celda. Las no volátiles se recalculan, únicamente, cuando se modifica alguna celda que interviene en la fórmula donde se ha empleado la función. Veamos un ejemplo.

En A1 ponemos el formato "dd/mm/aaaa hh:mm:ss" y escribimos la fórmula: =AHORA()

Se mostrará la fecha y hora actuales; por ejemplo, 08/12/2011 17:03:41

Esperamos un rato y comprobamos que, a pesar de haber transcurrido un tiempo, la celda muestra el mismo valor. A continuación, escribimos algo en otra celda; la fecha y la hora se actualizan automáticamente. AHORA es una función volátil. Para asegurarnos, estando en A1, hacemos clic en el icono fx de la barra de fórmulas.

Excel muestra la siguiente ventana:

Veamos otro ejemplo basado en esta tabla:

En la celda A12 escribimos: =DESREF(A3;3;4;1;1)

Excel devolverá el valor que hay en la celda E6.

DESREF también es una función volátil y lo comprobaremos colocándonos en A12 y haciendo clic en fx. En este caso, la función tiene 5 argumentos y la ventana que muestra Excel es distinta:

Hagamos otra prueba. Guardamos el libro y cerramos Excel. A continuación, abrimos el mismo libro y volvemos a cerrarlo si hacer ninguna modificación. Excel preguntará si queremos guardar los cambios efectuados. La pregunta sorprenderá a los usuarios que ignoren que AHORA y DESREF son funciones volátiles. Al abrir el libro, Excel recalcula, sin consultar al usuario, las fórmulas en las que aparecen estas funciones, sustituyendo los valores antiguos por otros (que pueden coincidir); esto implica una modificación del libro, y de ahí que, al cerrarlo, Excel nos pregunte si queremos guardar los cambios efectuados.

Son volátiles las siguientes funciones:

ALEATORIO
AHORA
HOY
DESREF
INDIRECTO
INFO
CELDA("nombrearchivo")

Algunas funciones han dejado de ser volátiles al cambiar de versión.Por ejemplo, la función INDICE dejó de ser volátil a partir de Excel 97.

Según cómo se usen, algunas funciones pasan de ser volátiles a no serlo. Esto le ocurre, por ejemplo, a la función SUMAR.SI. En la hoja,

siempre que los valores de la columna A sean mayores o iguales a 15, sumaremos en D2 los correspondientes valores de la columna B. Si utilizamos la fórmula: =SUMAR.SI(A2:A5;">=15";B2:B5), Excel considerará que la función SUMAR.SI no es volátil. Por el contrario, si usamos la fórmula =SUMAR.SI(A2:A5;">=15";B2), que devuelve el mismo resultado, la función será considerada volátil.

En el último caso, Excel nos engaña, porque, al hacer clic en fx, muestra la ventana

en la que no se indica que la función sea volátil. Sin embargo, si guardamos el libro, salimos de Excel y, posteriormente, abrimos el libro y lo cerramos sin hacer cambios, Excel nos preguntará si queremos guardar los cambios efectuados, señal inequívoca de que ha recalculado la fórmula y, por consiguiente, ha considerado que SUMAR.SI es volátil.

jueves, 8 de diciembre de 2011

La función "N"

N es una función con un único argumento que devuelve un número o un error. Si el argumento es un número, devuelve el mismo número; si es una fecha o una hora, devuelve su número de serie; si es un valor lógico, devuelve 1 o 0; si es un error, devuelve el valor de error; en cualquier otro caso, devuelve cero. Probemos la función escribiendo los valores y fórmulas siguientes:

El resultado es:

La función no resulta muy útil que digamos. Microsoft dice que la mantiene por compatibilidad con otras hojas de cálculo. Puestos a buscarle utilidad, podemos emplearla para poner comentarios aclaratorios en algunas fórmulas. Por ejemplo, en la hoja siguiente,

el Coste total se calcula con la siguiente fórmula: =SUMA(B2:B6)*D2   [Resultado: 129.750,00 €]

Con la función N, podemos poner un comentario para indicar que el rango B2:B6 son los kilogramos vendidos en los cinco primeros meses del año. También podemos señalar que D2 contiene el precio por kilo. Lo haremos con esta fórmula: =SUMA(B2:B6;N("Ventas enero-mayo"))*(D2+N("Precio por kilo"))

A la SUMA le hemos añadido un cero y a D2, otro. El resultado no cambia.

miércoles, 7 de diciembre de 2011

Copiar el valor de una fórmula

Si en una celda tenemos una fórmula que puede ir cambiando a lo largo del tiempo y en un determinado momento queremos conservar el valor actual, tendremos que copiar ese valor en otra celda de la hoja. Podemos hacerlo de tres maneras: copiando manualmente el valor, utilizando Pegado especial o usando el truco que se explicará más adelante.

Hagamos una prueba.

Escribimos en A1 una fórmula cualquiera; por ejemplo, =5*ALEATORIO.ENTRE(1;50). Obtendremos un valor comprendido entre 5 y 250. Cada vez que pulsemos F9 o escribamos algo en otra celda, el valor cambiará porque ALEATORIO.ENTRE es una función volátil (en una próxima entrada escribiré sobre las funciones volátiles).

Vamos a copiar el valor actual de A1 en la celda D1. Para ello, nos situamos en A1 y pulsamos Ctrl + C. Hacemos clic con el botón derecho en D1 y, en el menú contextual, seleccionamos Valores (V).

También podemos seleccionar Pegado especial, marcar el botón de Valores y terminar pulsando Aceptar.

El siguiente procedimiento es más rápido. Hacemos clic en A1; colocamos el cursor en el borde de la celda hasta que se transforme en una cruz con cuatro flechas; con el botón derecho, arrastramos la celda hasta D1 y soltamos. En ese momento, se mostrará el menú siguiente:

Terminamos seleccionando Copiar aquí sólo como valores. Eso es todo.

Para sustituir la fórmula de A1 por su valor, arrastramos el borde con el botón derecho a D1 (o a otra celda) y, sin soltar el botón del ratón, volvemos a situarnos en A1. Al liberar el botón, se mostrará el menú de la figura anterior; elegimos Copiar aquí sólo como valores y la fórmula quedará sustituida por el valor actual.


jueves, 24 de noviembre de 2011

Poner el control Calendario en un fomulario

Ya hemos visto cómo poner el control Calendario en una hoja; vamos a estudiar otra variante que consiste en ponerlo en un formulario. Insertaremos un botón para lanzarlo, que necesitará unas cuantas líneas de código en Visual Basic. Una vez terminado el ejercicio, la hoja se verá de la siguiente manera:

El calendario no estará visible hasta que pulsemos el botón. En ese momento se mostrará el formulario, elegiremos la fecha, que se escribirá en la celda C2, y podremos cerrarlo.

Entramos en VBA (Visual Basic para Aplicaciones) pulsando Alt + F11 e insertamos un formulario accediendo a Insertar + UserForm.

El Formulario está identificado con el número 1. El Cuadro de herramientas (número 2) contiene los controles que hay que incorporar al formulario; por defecto, se muestran los controles de la imagen, pero se pueden añadir más. La ventana número 3 es el Explorador de proyectos; si no está visible, hay que pulsar el icono número 5. La Ventana de Propiedades (número 4) permite poner las propiedades que queramos a los controles incorporados en el formulario; si no está visible, tendremos que pulsar el icono número 6.

Hacemos clic en el formulario para asegurarnos de que esté seleccionado y, en la Ventana de Propiedades, ponemos los siguientes valores:
  • (Name): frmCalendario
  • Caption: Calendario
  • Height: 204,75
  • Width: 261,75
  • Left: 50,25
  • Top: 200,25
Volvemos a hacer clic en el Formulario. En el Cuadro de herramientas no está el control Calendario; debemos añadirlo a la lista de controles para poder utilizarlo.

Hacemos clic con el botón derecho en el Cuadro de herramientas y, en el menú contextual, elegimos Controles adicionales. Marcamos Control de calendario 11.0 y pulsamos Aceptar.

El control calendario se ha incorporado a la lista del Cuadro de herramientas.

Arrastramos el nuevo control desde el Cuadro de herramientas hasta el Formulario y le ponemos las propiedades siguientes:
  • (Name): CCalendario
  • Height: 174
  • Width: 246
  • Left: 6
  • Top: 6
  • (Personalizado): Aquí se puede cambiar el formato, fuentes, colores...
Pulsamos F7 para entrar en el Editor de código y escribimos los procedimientos siguientes:

El siguiente paso consistirá en insertar un módulo para escribir el procedimiento que lanzará el formulario. Accedemos a Insertar + Módulo y, en el Editor de código, escribimos:

Salimos de VBA pulsando Alt + Q.

Ya estamos de vuelta en nuestra hoja de cálculo. Necesitamos lanzar el formulario de alguna manera. La forma más sencilla es poniendo un botón en la hoja y asociándolo al formulario. Podemos utilizar el botón del cuadro de Controles de formulario o una autoforma. Vamos a usar la segunda opción.

Accedemos a Insertar + Formas + Rectángulo redondeado y dibujamos un rectángulo en la hoja.

Arrastramos el tirador (rombo amarillo) hacia la derecha para redondear los laterales.

En Herramientas de dibujo + Formato, abrimos la lista de formatos en el grupo Estilos de forma y elegimos el que más nos guste. Arrastramos la autoforma a la posición deseada, escribimos en su interior la palabra Calendario, la centramos horizontal y verticalmente, elegimos el tipo de letra, etc. En el menú contextual elegimos Asignar macro.

En el cuadro de diálogo, seleccionamos Lanzar_Calendario y pulsamos Aceptar.

Sólo falta guardar el ejercicio y probarlo. Cuando un libro de Excel 2010 contiene macros, es necesario guardarlo con extensión .xlsm; para ello, accedemos a Archivo + Guardar como; en Nombre de archivo ponemos el que queramos y en Tipo elegimos Libro de Excel habilitado para macros (*.xlsm).

Descargar archivo (S85-Calendario.xlsm)

Descargar archivo (S85-Calendario.xls)


miércoles, 23 de noviembre de 2011

Insertar el control Calendario en una hoja

Para poner una fecha en una celda, podemos escribirla directamente o seleccionarla en un calendario insertado en la hoja y vinculado con la celda. Este calendario es un control ActiveX que se instala extrayéndolo de la ficha Programador. En Excel 2010 el "Control de calendario" ha sido eliminado, pero si al instalar Excel 2010 se mantuvo la versión anterior (2003 ó 2007), podrá seguir utilizándose.

Accedemos a Programador + Insertar + Controles ActiveX + Más controles.

En el cuadro de diálogo Más controles, seleccionamos Control de calendario 11.0 y pulsamos Aceptar.

Marcamos un rectángulo en el lugar donde queremos poner el control y el calendario quedará insertado.

Con el calendario seleccionado, accedemos al menú contextual y elegimos Propiedades (también, desde el grupo Controles de la ficha Programador, clic en Propiedades). En la propiedad LinkedCell ponemos la celda a la que queremos vincular la fecha elegida en el calendario. En el ejemplo, la celda C3.

Terminamos cerrando la ventana de Propiedades haciendo clic en el botón Modo Diseño.

Ahora, elegimos una fecha cualquiera en el calendario; por ejemplo, el 6 de junio de 2012. La fecha seleccionada se insertará en la celda C3 con el formato "dd/mm/aaaa".

Hay un detalle extraño. ¿Por qué se ajusta la fecha a la izquierda de la celda cuando, por defecto, las fechas se ajustan a la derecha? La razón es sencilla, el dato se inserta como un texto, no como una fecha. Si ponemos en una celda vacía: =ESTEXTO(C3), Excel nos devolverá VERDADERO, confirmando que el dato es un texto.

Si intentamos poner a C3 un formato de fecha distinto; por ejemplo, dddd, dd "de" mmmm "de" aaaa, veremos que no se puede. Es lógico, a un texto no se le puede asignar un formato de fecha.

Para hacerlo, tendremos que usar otra celda (por ejemplo, la C4) en la que se transforme el texto en el valor numérico de la fecha seleccionada en el control calendario. Esto se puede hacer de dos formas: usando la función VALOR, o sumándo a C3 un cero.

En C4:
=VALOR(C3)   [Resultado: 41066]

En D4:
=C3+0   [Resultado: 41066]

Ahora ya podemos poner a C4 (o a D4) el formato dddd, dd "de" mmmm "de" aaaa. El resultado será: miércoles, 06 de junio de 2012.


viernes, 18 de noviembre de 2011

Separadores de decimales y de miles

Aunque en algunos países es al revés, en España el separador de miles es el punto (.) y el separador decimal, la coma (,). No sé en que circunstancias convendría modificar esta convención usando otros separadores, pero bueno es saber que se puede.

Por ejemplo, vamos a mostrar el número 24.215.908,617 con el formato 24 245 908|617, sustituyendo los puntos de los miles por espacios y la coma decimal por una barra vertical.

Seleccionamos toda la hoja (o el rango de celdas donde vayamos a hacer la prueba) y ponemos formato numérico con tres decimales y separador de miles.

Accedemos a Archivo + Opciones + Avanzadas; desmarcamos Usar separadores del sistema; en Separador de decimales, ponemos la barra vertical (|); en Separador de miles, dejamos un espacio con la barra espaciadora y terminamos pulsando Aceptar.

Ahora, para escribir un número con decimales, debemos recordar que la coma hay que sustituirla por la barra vertical. Por tanto, para poner el número anterior en A1, hay que escribir: 24215908|617. Excel separará automáticamente los miles con espacios y nos devolverá 24 215 908|617. Este número es una cifra normal; por tanto, podemos realizar operaciones matemáticas normalmente. Por ejemplo, si queremos dividirlo por 251, escribiremos en otra celda: =A1/251. El resultado será 96 477|724.

miércoles, 16 de noviembre de 2011

Personalizar la barra de herramientas de acceso rápido

Los usuarios de Excel 2003 estamos habituados a las barras de herramientas y al usar las nuevas versiones, en ocasiones, las echamos en falta. En las versiones 2007 y 2010 han desaparecido pero, quizás para los nostálgicos, Microsoft ha dejado, en la parte superior, la Barra de herramientas de acceso rápido. Por defecto, sólo presenta tres iconos: Guardar, Deshacer y Rehacer, pero se pueden quitar o añadir nuevos iconos.

Para añadir iconos hay varios métodos. Si el icono está visible en la Cinta de opciones, basta hacer clic con el botón derecho sobre él y seleccionar Agregar a la barra de herramientas de acceso rápido. Por ejemplo, para añadir el icono Proteger hoja, tendremos que ir a la ficha Revisar  y, en el menú emergente de la opción Proteger hoja, elegiremos Agregar a la barra de herramientas de acceso rápido.

La barra de acceso rápido quedará así:

Si el icono no está visible en la Cinta de opciones, hay que actuar de otra manera. Accedemos a Archivo + Opciones + Barra de herramientas de acceso rápido (también se puede hacer clic en la flecha de la barra de acceso rápido y elegir Más comandos).

Abrimos la lista Comandos disponibles en y seleccionamos Todos los comandos. En el panel de la izquierda elegimos el icono que queremos añadir y pulsamos Agregar. Repetimos el proceso tantas veces como queramos y terminamos pulsando Aceptar. El resultado podría ser:

Para eliminar un icono de la Barra de herramientas de acceso rápido, basta hacer clic con el botón derecho sobre el icono y, en el menú contextual, elegir la opción Eliminar de la barra de herramientas de acceso rápido.

En Excel 2010, la configuración de la Barra de herramientas de acceso rápido se guarda en un fichero XML llamado Excel.officeUI. La localización de este fichero dependerá de cada caso; por ejemplo, en mi ordenador está en C:\Users\Javi\AppData\Local\Microsoft\OFFICE. Para poner la misma configuración en otra máquina, basta copiar el fichero en la carpeta equivalente del nuevo ordenador. En Excel 2007, el fichero de configuración se llama Excel.qat.

Los ficheros de configuración de las Barras de herramientas de acceso rápido de Word 2007 y Word 2010 se llaman, respectivamente, Word.qat y Word.officeUI .

lunes, 14 de noviembre de 2011

Volumen de agua embalsada. Gráfico de termómetro

A no mucha distancia de mi casa hay algunos embalses y pantanos para abastecer de agua a los pueblos y regular el cauce de los ríos. He tomado nota de las capacidades de cinco, las he puesto en una hoja de cálculo junto a unos niveles de llenado imaginarios y he construido un gráfico.

El gráfico muestra, en azul, las capacidades de los pantanos y, en rojo, el volumen de agua embalsada. Se trata de un gráfico de tipo termómetro que vamos a ver cómo se construye.

La columna E es la diferencia entre las columnas C y D.

Seleccionamos B2:E7 y accedemos a Insertar + Columna + Columna agrupada. Eliminando la leyenda el gráfico queda así:

Hacemos doble clic en una columna granate y, en Opciones de serie, marcamos el botón de opción Eje secundario. Repetimos este paso con las columnas de color verde.

Doble clic en una columna azul y, en Opciones de serie, ponemos 45% en Ancho del Intervalo.

Clic con el botón derecho en una columna granate (o en una verde). En el menú emergente, seleccionamos Cambiar tipo de gráfico de series y elegimos Columna apilada.

Doble clic en una columna azul.
  • En Relleno, elegimos Relleno degradado. En Puntos de degradado, dejamos los tres delimitadores que hay por defecto; a los delimitadores de los extremos, les ponemos Azul, Énfasis 1, Oscuro 25%, y al central, Azul, Énfasis 1, Claro 60%. En Dirección, elegimos Lineal izquierda.
  • En Color de borde, ponemos Sin línea.
Doble clic en una columna verde.
  • En Relleno, elegimos Relleno degradado. En Puntos de degradado, eliminamos el delimitador central; al delimitador izquierdo les ponemos Azul, Énfasis 1, Claro 60%, y al de la derecha, Azul, Énfasis 1, Oscuro 25%. En Dirección, elegimos Lineal derecha.
  • En Color de borde, ponemos Sin línea.
Doble clic en una columna granate.
  • En Relleno, elegimos Relleno degradado. En Puntos de degradado sólo debe haber dos delimitadores; al delimitador izquierdo les ponemos Negro, Texto 1, Claro 15%, y al de la derecha, Rojo. En Dirección, elegimos Lineal derecha.
  • En Color de borde, ponemos Sin línea.
Las dos escalas verticales coinciden; en caso de no hacerlo, habría que igualarlas haciendo doble clic en la vertical secundaria e igualándola con la principal.

Eliminemos la escala vertical secundaria. Para ello, hacemos doble clic encima de ella y, en Marca de graduación principal, en Marca de graduación secundaria y en Etiqueta del eje, elegimos Ninguno.

Doble clic en la escala vertical izquierda. En Número y en la opción Posiciones decimales, ponemos 0.

Con el gráfico seleccionado, accedemos a Herramientas de gráficos + Presentación + Título de gráfico + Encima del gráfico. En letra Calibri + 16 + Anaranjado + Énfasis 6 + Oscuro 50%, ponemos el título siguiente: Estado de los embalses

Con el gráfico seleccionado, accedemos a Herramientas de gráficos + Presentación + Rótulos del eje + Título del eje vertical primario + Título girado. En letra Calibri + 12, ponemos el título siguiente: Hectómetros cúbicos

Doble clic en cualquiera de las líneas de división principales.
  • En Color de línea, elegimos Línea sólida. En Color, seleccionamos Blanco, Fondo 1, Oscuro 25%.
  • En Estilo de línea y en el apartado Tipo de guión, elegimos Punto cuadrado.
Hacemos clic en el Área del gráfico y ponemos un Relleno sólido de color Azul, Énfasis 1, Claro 40%.

Hacemos doble clic en el Área del trazado y ponemos un Relleno sólido de color Aguamarina, Énfasis 5, Claro 60%.

Con esta técnica, se pueden crear variaciones sobre este gráfico como los que se muestran a continuación:

En el siguiente enlace se puede descargar un fichero ZIP con los tres gráficos.

Descargar archivo (S49-Embalses.xls)

Descargar archivo (S49-Embalses.xlsx)