lunes, 25 de febrero de 2013

¿Se solapan las fechas?

En una hoja de cálculo se han puesto los programas de utilización de tres máquinas (B2:D18). Cada fila señala las fechas de comienzo y fin previstas para la realización de una tarea. Sin embargo, existe el riesgo de que se hayan cometido errores y algunas máquinas se hayan programado de modo que inicien sus actividades antes de terminar el trabajo de la tarea anterior.

Debemos determinar si se producen solapamientos en las programaciones de las máquinas. Los resultados irán en la columna E. Las columnas G a M se usarán para obtener resultados intermedios.
 
El razonamiento para resolver el problema lo haremos con la Máquina A de la celda B4. Constará de 8 pasos:
  1. Asignar FALSO a la máquina que estamos analizando (en nuestro caso la de la celda B4) y VERDADERO al resto (esto dará la primera matriz de valores VERDADERO y FALSO).
  2. Comprobar si el valor de B4 coincide con los otros valores de la columna B (segunda matriz, con valores VERDADERO Y FALSO).
  3. Comprobar si la fecha final de D4 es anterior a todas las fechas de comienzo (matriz con valores VERDADERO Y FALSO). Comprobar si la fecha inicial de C4 es posterior a todas las fechas finales (matriz con valores VERDADERO Y FALSO). Sumar ambas matrices (obtendremos la tercera matriz, con ceros y unos).
  4. Sustituir los ceros por unos y viceversa (se creará la cuarta matriz, con ceros y unos).
  5. Multiplicar las matrices de los apartados 1), 2) y 4).
  6. Sumar los elementos de la esta última matriz.
  7. Si el resultado es cero, no hay solapamiento. En caso contrario, hay solapamiento.
  8. Combinar todas las fórmulas en E4.
Seleccionamos G4:G18 y escribimos:
=FILA($B4)<>FILA($B$4:$B$18)     [Terminar con Ctrl + Mayús + Intro]

La primera referencia ($B4) debe ser mixta ya que una vez obtenida la fórmula definitiva la pondremos en E4 y la copiaremos hacia abajo. De ese modo, $B4 pasará a ser $B5, $B6...

Seleccionamos H4:H18 y escribimos:
=($B4=$B$4:$B$18)     [Terminar con Ctrl + Mayús + Intro]

Seleccionamos I4:I18 y escribimos:
=($D4<$C$4:$C$18)+($C4>$D$4:$D$18)     [Terminar con Ctrl + Mayús + Intro
 
Seleccionamos J4:J18 y escribimos:
=--NO($I$4:$I$18)     [Terminar con Ctrl + Mayús + Intro]
 
Seleccionamos K4:K18 y escribimos:
=($G$4:$G$18)*($H$4:$H$18)*($J$4:$J$18)     [Terminar con Ctrl + Mayús + Intro]
 
En L4:
=SUMA($K$4:$K$18)     [Terminar con Intro]
 
En M4:
=SI(L4=0;"No";"Sí")     [Terminar con Intro]
 
Sólo falta poner la fórmula compuesta en E4.
 
En E4:
=SI(SUMA((FILA($B4)<>FILA($B$4:$B$18))*(($B4=$B$4:$B$18))*(--NO(($D4<$C$4:$C$18)+($C4>$D$4:$D$18))))=0;"No";"Sí")     [Terminar con Ctrl + Mayús + Intro]
 
Extendemos la fórmula hasta la fila 18 y eliminamos las columnas G:M.
 
 
 
 

viernes, 22 de febrero de 2013

Sumar caracteres según su posición en el abecedario

En este ejercicio vamos a resolver un problema que, probablemente, no tiene una utilidad práctica inmediata pero que va a poner a prueba nuestro ingenio.

Asignaremos a cada letra un valor numérico que coincida con su posición en el abecedario, independientemente de que sea mayúscula, minúscula, esté acentuada  o lleve diéresis. Así,  "a", "á", "A" y "Á" tendrán valor 1; "b" y "B" tendrán valor 2; "u", "ú", "ü", "U", "Ú" y "Ü" tendrán valor 22; y así sucesivamente.

El problema va a consistir en escribir el nombre de una persona (o un texto cualquiera) y calcular, con arreglo al criterio anterior, la suma de las cifras asignadas a sus caracteres. El texto irá en la celda C2 y la suma en C3. Las celdas de las filas 6 a 26 las usaremos para hacer cálculos intermedios. Al final, crearemos una fórmula compleja y podremos eliminar los valores de las celdas auxiliares.

Resolveremos el ejercicio en 7 pasos:
  1. Quitar los espacios en blanco y poner el texto en mayúscula (MARTÍNCORTÉS).
  2. Contar los caracteres que han quedado (12).
  3. Generar una lista de números del 1 al total de caracteres (del 1 al 12).
  4. Extraer uno a uno todos los caracteres creando una matriz.
  5. Eliminar la tilde de las vocales que lo lleven.
  6. Asignar a cada letra un valor numérico según su posición en el abecedario.
  7. Sumar los números.
Para quitar los espacios usaremos la función SUSTITUIR, y para poner el texto en mayúscula, la función MAYUSC.

En C6:
=MAYUSC(SUSTITUIR($C$2;" ";))    [Resultado: MARTÍNCORTÉS]

El número de caracteres del texto se obtiene mediante la función LARGO.

En B9:
=LARGO(C6)     [Resultado: 12]

La lista de números del 1 al 12 la generamos mediante las funciones FILA y DESREF.

Seleccionamos C9:C26 y escribimos:
=FILA(DESREF($A$1;;;$B$9))     [Terminar con Ctrl + Mayús + Intro]

La matriz de caracteres individuales del texto se obtiene con la función EXTRAE.

Seleccionamos D9:D26 y escribimos:
=EXTRAE($C$6;$C$9:$C$26;1)     [Terminar con Ctrl + Mayús + Intro]

La eliminación de las tildes de las vocales acentuadas se realiza con las funciones CODIGO, ELEGIR y COINCIDIR.

Seleccionamos E9:E26 y escribimos:
=SI(CODIGO($D$9:$D$26)>90;ELEGIR(COINCIDIR($D$9:$D$26;{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");$D$9:$D$26)     [Terminar con Ctrl + Mayús + Intro]

Usamos la función HALLAR para obtener la posición de cada letra en el abecedario.

Seleccionamos F9:F26 y escribimos:
=HALLAR($E$9:$E$26;"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1)     [Terminar con Ctrl + Mayús + Intro]

Sólo falta sumar los números.

En G9:
=SUMA(F9:F26)     [Resultado: #N/A]

Parece que no hemos obtenido el resultado correcto, pero cuando compongamos la fórmula veremos que todo se arregla. La razón es que en la suma hemos incluido las filas 21 a 26 que contienen errores. En la fórmula definitiva, Excel eliminará estas filas, como se comprobará más adelante.

Compondremos la fórmula paso a paso. Para ello, comenzamos poniendo en C3 la fórmula anterior pero sustituyendo F9:F26 por la fórmula de la matriz incluida en ese rango. El resultado será:

En C3:
=SUMA(HALLAR($E$9:$E$26;"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

El error continúa.
 
En la fórmula que nos ha quedado, sustituimos $E$9:$E$26 por la fórmula de esa columna. Queda otra fórmula más larga:
 
En C3:
=SUMA(HALLAR(SI(CODIGO($D$9:$D$26)>90;ELEGIR(COINCIDIR($D$9:$D$26;{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");$D$9:$D$26);"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]
 
Persiste el error. No desesperemos. Tenemos que seguir sustituyendo valores. En este momento, el valor a sustituir, tres veces, es $D$9:$D$26.
 
En C3:
=SUMA(HALLAR(SI(CODIGO(EXTRAE($C$6;$C$9:$C$26;1))>90;ELEGIR(COINCIDIR(EXTRAE($C$6;$C$9:$C$26;1);{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");EXTRAE($C$6;$C$9:$C$26;1));"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]
 
Aún no hemos obtenido el valor correcto. $C$9:$C$26 aparece otras tres veces. Repetimos el proceso.
 
=SUMA(HALLAR(SI(CODIGO(EXTRAE($C$6;FILA(DESREF($A$1;;;$B$9));1))>90;ELEGIR(COINCIDIR(EXTRAE($C$6;FILA(DESREF($A$1;;;$B$9));1);{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");EXTRAE($C$6;FILA(DESREF($A$1;;;$B$9));1));"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

¡Por fin! Resultado correcto. ¿Por qué ahora Excel nos devuelve un resultado correcto y antes no? La razón es que las fórmulas anteriores incluían las filas 21 a 26, que contienen errores. Sin embargo, ahora, hemos introducido en la fórmula la función DESREF, que devuelve una matriz de únicamente 12 elementos, que no incluyen las filas mencionadas.

Aún no hemos acabado. Debemos quitar $B$9.

En C3:
=SUMA(HALLAR(SI(CODIGO(EXTRAE($C$6;FILA(DESREF($A$1;;;LARGO(C6)));1))>90;ELEGIR(COINCIDIR(EXTRAE($C$6;FILA(DESREF($A$1;;;LARGO(C6)));1);{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");EXTRAE($C$6;FILA(DESREF($A$1;;;LARGO(C6)));1));"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

Finalmente, quitamos $C$6.

En C3:
=SUMA(HALLAR(SI(CODIGO(EXTRAE(MAYUSC(SUSTITUIR($C$2;" ";));FILA(DESREF($A$1;;;LARGO(MAYUSC(SUSTITUIR($C$2;" ";)))));1))>90;ELEGIR(COINCIDIR(EXTRAE(MAYUSC(SUSTITUIR($C$2;" ";));FILA(DESREF($A$1;;;LARGO(MAYUSC(SUSTITUIR($C$2;" ";)))));1);{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");EXTRAE(MAYUSC(SUSTITUIR($C$2;" ";));FILA(DESREF($A$1;;;LARGO(MAYUSC(SUSTITUIR($C$2;" ";)))));1));"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

Podemos borrar las celdas auxiliares; ya no las necesitamos.
 
 
 

viernes, 8 de febrero de 2013

Saltar a una celda con el "Cuadro de nombres"

Normalmente, para seleccionar una celda o un rango nos movemos a la zona deseada por medio de las barras de desplazamiento y hacemos clic con el ratón. Sin embargo, este método puede ser demasiado lento si tenemos que desplazarnos a una celda que esté muy alejada. Por ejemplo, si estamos en A1 y debemos desplazarnos a UEG25000, la cosa es complicada usando el ratón, pero no lo es si usamos el "Cuadro de nombres".

En los ejemplos que se muestran a continuación se utilizan dos libros. El libro "E29-Cuadro de Nombres.xlsx" tiene tres hojas: Ciudad, Rutas y Verduras. El otro libro, "E4-Inspección.xlsx", tiene dos: Precio y Rebaja.

Abrimos el libro E29-Cuadro de Nombres.xlsx y nos ponemos en la celda A1 de la hoja Ciudad. Estando en esa celda (o en otra cualquiera), escribimos B15 en el Cuadro de nombres y terminamos pulsando Intro. Excel salta y selecciona la celda B15.

De nuevo, en el Cuadro de nombres, escribimos UEG25000 y pulsamos Intro. Hemos conseguido ir a esa celda, que, por cierto, está vacía. 
 
También podemos saltar a una celda de otra hoja; por ejemplo, para ir a la celda B20 de la hoja Rutas, hay que escribir lo siguiente: Rutas!B20

Del mismo modo, estando en cualquier hoja, para seleccionar el rango B6:D8 de la hoja Verduras, tendremos que escribir: Verduras!B6:D8

Otra forma de saltar a cualquier lugar es usando nombres. En el libro se han creado cinco:
 
Pulsando en cualquiera de ellos se selecciona el rango definido por el nombre elegido.
 
El uso del Cuadro de nombres aún es más completo: permite saltar de un libro a otro. La única condición es que ambos estén abiertos.
 
Sin cerrar el libro actual, abrimos E4-Inspección.xlsx. No es necesario abrirlo desde el libro activo; puede abrirse desde otra copia de Excel.
 
Para seleccionar el rango B4:C8 de la hoja Rebajas del libro E4-Inspección.xlsx, estando en cualquier celda del libro E29-Cuadro de Nombres.xlsx, escribimos en el Cuadro de nombres: '[E4-Inspección.xlsx]Rebaja'!B4:C8