Ecuaciones, comentarios y cuadros de texto

En Excel 2003 el Editor de ecuaciones se encuentra un tanto oculto. Se accede seleccionando Insertar + Objeto y, en la pestaña Crear nuevo, se elige Microsoft Editor de ecuaciones 3.0. En la versión 2010 está más accesible: Insertar + Ecuación.

En este ejercicio, vamos a manejar el Editor de ecuaciones, los Cuadros de texto y los Comentarios. El objetivo será construir la hoja que se muestra a continuación y que tiene algunas particularidades que se desvelarán más adelante.

Partiremos de una hoja con este formato:

Comencemos con los cuadros de texto del rango B2:D8.

Accedemos a Insertar + Formas + Formas básicas + Cuadro de texto y, manteniendo pulsada la tecla Alt, trazamos un rectángulo que ocupe las celdas B1:D1. Dentro del cuadro de texto, en negrita, centrado vertical y horizontalmente, escribimos: Poliedros regulares

Ahora, con el cuadro de texto seleccionado, vamos a Herramientas de dibujo + Formato y abrimos la lista de Estilos de forma. Elegimos el primero de la segunda fila.

Repetimos estos mismos pasos para poner los cuadros de texto de las celdas B3:D3 (segundo de la última fila) y B4:B8 (tercero de la última fila). Ajustando a la izquierda los textos de la columna B, obtendremos la imagen siguiente:

Vamos a poner las ecuaciones de las celdas C4:D8. Para ilustrar el procedimiento pondremos la ecuación de la celda C7.

Seleccionamos Insertar + Ecuación. En medio de la pantalla aparecerá un rectángulo punteado y la Cinta de opciones presentará dos nuevas pestañas en la parte superior: Herramientas de dibujo y Herramientas de ecuación. Si arrastramos el rectángulo punteado a un lugar vacío y lo agrandamos para que dentro quepa la ecuación, el aspecto de la pantalla será similar a la figura siguiente:

Pulsamos en Índices y elegimos la primera opción de la primera línea (Superíndice). Se nos mostrará dos cuadraditos dentro del rectángulo punteado:

En el primer cuadradito escribimos: 3a

En el segundo cuadradito escribimos: 2

Pulsamos flecha derecha para salirnos del superíndice y hacemos clic en Radical. Elegimos la primera opción y, dentro del radicando, escribimos: 25+10

Volvemos a elegir la primera opción de Radical y escribimos: 5

La ecuación está creada.

En Herramientas de dibujo + Formato, abrimos los Estilos de forma y seleccionamos el último de la última fila. Cambiamos el color de la fuente y, con la tecla Alt pulsada, arrastramos la ecuación haciendo que ocupe completamente la celda C7.

Con las demás ecuaciones tendremos que actuar de la misma manera.

En las celdas G4:G8 pondremos comentarios que informen sobre las características más importantes de cada poliedro. Para ello, hacemos clic con el botón derecho en la celda G4 y, en el menú emergente, seleccionamos Insertar comentario. Se mostrará un rectángulo acoplado al vértice superior derecho de la celda mediante una flecha. En su interior escribimos el comentario y pulsamos fuera para terminar.

En el vértice superior derecho de las celda que contienen comentarios, Excel muestra un pequeño triángulo rojo. Colocando el cursor encima de la celda, emerge el comentario como por arte de magia. Al sacar el cursor de la celda, se oculta.

Si queremos modificar el texto u otras características del comentario, basta hacer clic con el botón derecho en la celda y elegir la opción correspondiente.

Las columnas F y G contienen fórmulas y éstas no funcionan si se escriben en los cuadros de texto. Por tanto, las escribiremos en las propias celdas (como siempre). Luego, pondremos encima cuadros de texto similares a los que hemos colocado en la fila 3 y en la columna B creando un enlace con las fórmulas de las celdas que están cubriendo. Pero, vayamos despacito para no perdernos.

En F3 y G3 ponemos dos cuadros de texto similares a los que hay en C3 y D3.

En G2: 5

En F4: =G2^2*RAIZ(3)    [Resultado: 43,30]

En F5: =6*G2^2    [Resultado: 150,00]

En F6: =2*G2^2*RAIZ(3)    [Resultado: 86,60]

En F7: =3*G2^2*RAIZ(25+10*RAIZ(5))    [Resultado: 516,14]

En F8: =5*G2^2*RAIZ(3)    [Resultado: 216,51]

En G4: =G2^3*RAIZ(2)/12    [Resultado: 14,73]

En G5: =G2^3    [Resultado: 125,00]

En G6: =G2^3*RAIZ(2)/3    [Resultado: 58,93]

En G7: =(G2^3/4)*(15+7*RAIZ(5))    [Resultado: 957,89]

En G8: =(5*G2^3/12)*(3+RAIZ(5))    [Resultado: 272,71]

En la celda F4 ponemos un cuadro de texto vacío del mismo color que el de la celda C4. Con el cuadro de texto seleccionado, en la barra de fórmulas escribimos: =F4

Con este truco, conseguimos que en el cuadro de texto aparezca el valor de la celda a la que está cubriendo. Poniendo el texto de color negro y repitiéndolo con las otras celdas, el ejercicio queda terminado.

Podremos comprobar que cambiando el valor de la arista de la celda G2, los cuadros de texto muestran correctamente las áreas y volúmenes de todos los poliedros.

Descargar archivo (S54-Poliedros.xlsx)

La función IGUAL

Para Excel no hay diferencia entre un texto escrito en mayúscula o en minúscula. Por ejemplo, si en A1 escribimos Gabriel García Márquez y en A2 escribimos GABRIEL GARCÍA MÁRQUEZ, la fórmula =A1=A2 devolverá VERDADERO. El operador igual (=) no distingue entre mayúsculas y minúsculas. Ocurre igual con otras funciones.

Si necesitamos diferenciar mayúsculas de minúsculas debemos usar la función IGUAL. Por ejemplo, si sustituimos la fórmula anterior por la siguiente: =IGUAL(A1;A2), el resultado es FALSO.

Veremos cómo usar la función IGUAL con un ejemplo.

En la columna B hay repetidos los nombres de dos ciudades pero, en algunos casos, la primera letra está escrita en minúscula. En la columna C hemos puesto unos números arbitrarios.

En F2 pondremos el nombre de una ciudad. En F5 y en las celdas inferiores calcularemos, por varios métodos, cuántas veces aparece esa ciudad (coincidencia exacta).

En F2:

Burgos

La primera reacción para saber cuántas veces está escrita la palabra Burgos en la columna B es utilizar la función CONTAR.SI, pero es una elección incorrecta. Comprobémoslo.

En F5:

=CONTAR.SI(B3:B14;F2)    [Resultado incorrecto: 7]

La función CONTAR.SI es una de tantas funciones que no distinguen entre mayúsculas y minúsculas; no nos sirve.

Probemos otra solución.

En F6:

=CONTAR(SI(IGUAL(B3:B14;F2);C3:C14;""))    [Terminar con Ctrl + Mayúscula + Intro]

En este caso la respuesta es correcta. Veamos lo que hace la fórmula.

• IGUAL(B3:B14;F2) compara cada elemento del rango B3:B14 con el valor que hemos puesto en la celda F2 y devuelve una matriz de valores VERDADERO y FALSO. VERDADERO cuando la coincidencia es exacta; FALSO cuando no hay coincidencia exacta.
• La función SI toma esta matriz y, si el valor es VERDADERO, devuelve el número correspondiente del rango C3:C14; en caso contrario, devuelve un blanco (""). Por tanto, devuelve otra matriz.
• la función CONTAR cuenta los números que hay en esta última matriz, devolviendo las veces que la palabra Burgos está escrita exactamente igual en la columna B.

Si no existiera la columna C o no quisiéramos usarla, podríamos usar esta fórmula:

En F7:

=CONTAR(SI(IGUAL(B3:B14;F2);1;""))    [Terminar con Ctrl + Mayúscula + Intro]

En este caso, la función SI no devuelve el valor de la columna C, sino un uno (podemos poner cualquier otro número) o un blanco (""). De este modo, devolverá tantos unos como veces esté escrita la palabra de la celda F2. Después, CONTAR contará los unos que hay y devolverá el resultado correcto. Como es lógico, podemos sustituir CONTAR por SUMA.

En F8:

=SUMA(SI(IGUAL(B3:B14;F2);1;""))    [Terminar con Ctrl + Mayúscula + Intro]

En realidad, ni siquiera necesitamos usar la función SI.

En F9:

=SUMA(--IGUAL(B3:B14;F2))    [Terminar con Ctrl + Mayúscula + Intro]

Puesto que la función IGUAL nos ha dado una matriz de valores VERDADEROS y FALSOS, bastará sumarlos (recordamos que VERDADERO equivale a 1 y FALSO equivale a 0) y directamente nos dará el resultado. La transformación de valores lógicos a numéricos se hace, como se ha explicado en otro artículo, multiplicando por uno o poniendo dos signos menos delante.

Todas las fórmulas correctas empleadas en el ejercicio son fórmulas matriciales. Si no queremos utilizar una fórmula matricial, bastará sustituir SUMA por SUMAPRODUCTO.

En F10:

=SUMAPRODUCTO(--IGUAL(B3:B14;F2))    [Terminar con Intro]

Descargar archivo (S52-F_Igual.xlsx)

Descargar archivo (S52-F_Igual.xls)

La función FRECUENCIA

Si una lista de valores numéricos la dividimos en intervalos, el cálculo de los números que hay en cada intervalo se obtiene fácilmente usando la la función FRECUENCIA.

Se trata de una función matricial (se termina con Ctrl + Mayús + Intro) y se utiliza de una forma que puede resultar extraña. Veamos un ejemplo.

Hemos registrado en B4:C105 las precipitaciones anuales habidas en Sevilla entre los años 1900 y 2000. Queremos saber cuántos años las precipitaciones estuvieron comprendidas entre 0 y 300 litros/m², entre 300 y 400, entre 400 y 500... y cuántos las precipitaciones fueron superiores a 1.000 litros/m².

Para facilitar la lectura, en la columna F hemos puesto el valor inferior del intervalo y en la columna H, el superior. La función FRECUENCIA no utiliza el valor inferior, sólo el superior.

Seleccionamos I5:I13. Parece un poco extraño que seleccionemos la fila 13 cuando en la columna H —que es la que utiliza la función FRECUENCIA, como veremos enseguida— sólo hay datos hasta la fila 12, pero es así como debemos obrar si queremos calcular cuántos años las precipitaciones superaron los 1.000 litros/m².

Escribimos la fórmula: =FRECUENCIA(C5:C105;H5:H12)      [Terminamos con Ctrl + Mayús + Intro]

En caso de coincidencia exacta, la cifra se contabiliza en el rango que contiene el número; por ejemplo, si un año cayeron, exactamente, 600 litros/m², ese año se contará dentro del intervalo: más de 500 litros/m² y menos o igual a 600 litros/m². En la fila 13, como no hay ningún número en H13, el intervalo será: más de 1000 litros/m² y —al no haber límite superior— hasta el infinito.

Naturalmente, se puede resolver este ejercicio usando otros métodos. Por ejemplo, con la función CONTAR.SI.CONJUNTO las fórmulas serían:

En I5:

=CONTAR.SI.CONJUNTO(C5:C105;">"&F5;C5:C105;"<="&H5)    [Resultado 1]

Ahora, hay que copiar la fórmula hata la fila 12.

La fórmula de la fila 13 puede simplificarse usando CONTAR.SI

En I13:

=CONTAR.SI(C13:C113;">"&F13)    [Resultado 3]

Descargar archivo (SE51-Frecuencia.xlsx)

Descargar archivo (SE51-Frecuencia.xls)

Gráfico de tipo velocímetro

Si Excel tuviera en su arsenal de gráficos uno de tipo velocímetro no estaría escribiendo este artículo. Pero no lo tiene y esto me brinda la posibilidad de mostrar cómo se crea el siguiente:

En C2 pondremos el valor que debe mostrar la aguja del gráfico. En el rango B4:C9 están los intervalos de velocidad que nos servirán para colorear el gráfico. Los datos que están escritos en gris son valores auxiliares.

Vamos a realizar el gráfico superponiendo unas formas sobre otras, como las transparencias que se emplean en los libros de anatomía —¿se siguen usando?— para ir montando las diferentes partes del cuerpo humano.

Las guías para montar este puzzle serán las cuadrículas de la hoja, pero necesitamos que estén más juntas. Para ello, seleccionamos las columnas D:X. Con el botón derecho del ratón mostramos el menú contextual y elegimos Ancho de columna. El ejemplo está hecho con un ancho de 2,71.

El fondo del gráfico será un rectángulo coloreado. Para dibujarlo, accedemos a Insertar + Formas + Rectángulo y, manteniendo pulsada la tecla Alt, trazamos un rectángulo que ocupe el rango E2:W17. Ahora, hacemos clic con el botón derecho en el interior del rectángulo y elegimos Formato de forma. Ponemos un relleno degradado de color verde y eliminamos el borde.

El siguiente paso consiste en crear la tabla de datos con la que construiremos el cuerpo del gráfico. Lo haremos en B11:C17.

En C12:

=C5    [Resultado: 600]

En C13:

=C6-C5    [Resultado: 300]

Extendemos la fórmula hasta la fila 16. En la fila 17 pondremos la suma:

En C17:

=SUMA(C12:C16)    [Resultado: 3.000]

Seleccionamos B12:C17 y, en el grupo Gráficos del menú Insertar, accedemos a Otros + Anillo. Borramos las leyendas y, con la tecla Alt pulsada, ajustamos el gráfico hasta colocarlo en F2:V21.

Debemos girar el gráfico 270º para que el sector marrón, que corresponde a la serie Auxiliar, quede en la parte inferior. Para ello, hacemos doble clic en cualquier sector del gráfico y, en Opciones de serie, ponemos un giro de 270º.

La mencionada serie Auxiliar ocupa la mitad del gráfico. Esto es debido a que en la celda C17 hemos sumado todos los valores de las celdas superiores; es decir, esta celda suma tanto como todas las demás celdas juntas. Lo hemos hecho así porque queremos crear un gráfico semicircular y, al tener medio gráfico por debajo, bastará con que lo ocultemos eliminando el relleno y el borde.

Hacemos dos veces clic (¡cuidado!, no hay que hacer doble clic) en la zona marrón (serie Auxiliar). Una vez seleccionada la serie, hacemos doble clic sobre la misma. Entraremos en el cuadro de diálogo anterior y todo lo que hagamos afectará únicamente a la serie seleccionada. Quitando el relleno y el color del borde, el gráfico quedará así:

Repetimos estos pasos con el resto de las series pero teniendo la precaución de quitar el borde y poner el color adecuado a cada caso.

Debemos hacer que el Área del gráfico y el Área de trazado sean transparentes. Para ello, hacemos doble clic en el Área del gráfico y quitamos el relleno y el borde.

El anillo es demasiado grueso y vamos a adelgazarlo. Doble clic en cualquier serie del gráfico para entrar en la ventana Formato de serie de datos. En Opciones de serie, ponemos 65% en el apartado Tamaño del agujero del anillo.

Una vez creado el cuerpo principal del gráfico vamos a poner los rótulos que identifiquen dónde empieza y dónde acaba cada serie. Podríamos hacerlo poniendo manualmente unos cuadros de textos con sus correspondientes rótulos, pero este método tiene el inconveniente de que quedarían descolocados cuando cambiáramos los intervalos de las series. Debemos hacerlo de forma que se coloquen automáticamente en sus correspondientes posiciones según los valores de la tabla B4:C9. Para ello, crearemos una nueva tabla de datos y, con ella, un gráfico circular. Pero, vayamos paso a paso.

Para evitar molestias, arrastramos el gráfico que acabamos de crear a una posición que no nos moleste; por ejemplo, a la derecha de la columna AA.

Escribimos el número 50 en las celdas siguientes: Z3, Z5, Z7, Z9, Z11 y Z13. En el resto de las celdas de la columna Z debemos poner los mismos valores que los del rango C12:C16. En Z14 sumaremos toda la columna.

En Z4:

=C12

En Z6:

=C13

En Z8:

=C14

En Z10:

=C15

En Z12:

=C16

En Z14:

=SUMA(Z3:Z13)    [Resultado: 3.300]

Posteriormente, sustituiremos todos los valores que hemos puesto en Z3, Z5, Z7, Z9, Z11 y Z13 por ceros. Pero este paso lo explicaremos en su momento.

Seleccionamos Y3:Z14 y, en el grupo Gráficos de la pestaña Insertar, elegimos Circular + Circular. Quitamos la leyenda y, manteniendo pulsada la tecla Alt, arrastramos y redimensionamos el gráfico hasta que ocupe el rango E1:W22.

Hacemos doble clic en cualquier sector para girar el gráfico y eliminar el relleno. En Opciones de serie ponemos un giro de 270º. En Relleno, elegimos Sin relleno y en Color de borde, seleccionamos Sin línea.

Los sectores más estrechos (los de valor 50) nos servirán para poner los rótulos de los intervalos de cada color. Para ello, estos sectores deben mostrar la Etiqueta de datos en el exterior. Se hace así: Seleccionamos un sector haciendo dos veces clic (no doble clic) sobre él y elegimos Herramientas de gráficos + Presentación + Etiquetas de datos + Extremo externo.

Esto debemos hacerlo con los seis sectores de tamaño 50. El resultado será:

Ahora, debemos sustituir el número 50 de cada sector por el correspondiente a la zona en la que está. El primer sector (inferior izquierdo) debe ser cero; por tanto, borramos manualmente el 50 y ponemos en su lugar un 0.

Los otros 5 sectores restantes deben mostrar los valores que hay en C5:C9. Lo haremos así: seleccionamos el rótulo que contiene el número 50 del segundo sector de la izquierda y, en la barra de fórmulas, escribimos: =Hoja1!$C$5. En el tercer sector la fórmula será: =Hoja1!$C$6. Y así sucesivamente.

Una vez puestas todas las fórmulas, debemos sustituir los 50 de Z3, Z5, Z7, Z9, Z11 y Z13 por 0. Si desde el primer momento los sectores hubieran tenido valor 0, habría sido más difícil seleccionarlos y poner las etiquetas de datos. Esta es la razón del extraño rodeo que hemos dado. Pulsando fuera del gráfico, la hoja se verá así:

Hacemos doble clic en el Área del gráfico y quitamos el relleno y el borde. A continuación, arrastramos el gráfico a una zona vacía para seguir trabajando.

Es el momento de ponerle una aguja al gráfico. La crearemos con otro gráfico circular, pero antes necesitamos la tabla de datos para construirlo. Estará en Y16:Z19.

En Z17:

=C2    [Resultado: 1.600]

En Z18:

50     [Más adelante este valor lo sustituiremos por un cero]

En Z19:

=2*C9-Z18-Z17    [Resultado: 4.350]

Seleccionamos Y17:Z19 y, en el grupo Gráficos de la pestaña Insertar, elegimos Circular + Circular. Quitamos la leyenda y, manteniendo pulsada la tecla Alt, arrastramos y redimensionamos el gráfico hasta que ocupe el rango G3:U20.

Del mismo modo que hemos hecho en el caso anterior, giramos el gráfico 270º, eliminamos el relleno y el borde de todos los sectores y le ponemos la Etiqueta de datos en el Extremos externo al sector de tamaño 50. Hacemos clic en el 50 de la Etiqueta de datos y, en la barra de fórmulas, ponemos la fórmula siguiente: =Hoja1!$C$2

Hacemos dos veces clic (no doble clic) en el sector de tamaño 50 y, una vez seleccionado, hacemos doble clic. En la ventana Formato de puntos de datos, ponemos un Relleno sólido de color negro, un Color de borde con Línea sólida de color negro y un Estilo de borde con Ancho de 2 puntos. Volvemos a hacer doble clic en el Área del gráfico y eliminamos el relleno y el borde.

El último paso consiste en sustituir el 50 de la celda Z18 por un cero. El resultado será:

Ya sólo queda montarlo todo y poner los últimos detalles para adornar el gráfico.

Con la tecla Alt pulsada, arrastramos el gráfico de sectores (el primero que hemos hecho) a la posición F2:V21. Del mismo modo, arrastramos el gráfico de los números a E1:W22.

Ponemos dos cuadros de texto con las leyendas que correspondan a nuestro caso. Manteniendo pulsada la tecla Mayúscula, con Insertar + Formas + Elipse, dibujamos un círculo que simule el eje donde gira la aguja. Le ponemos el tamaño y los efectos de color que queramos y el ejercicio quedará terminado.

Podemos comprobar que cambiando el valor de C2 la aguja se mueve a la posición correcta. También conviene comprobar que cambiando los intervalos C5:C9 las diferentes zonas coloreadas se amplían o reducen adecuadamente y las etiquetas de datos se colocan en su sitio.

Descargar archivo (SES15-Grafico_velocimetro.xlsx)

Dígitos de control de una cuenta bancaria

Hace unos días recibí una llamada telefónica, supuestamente, de mi compañía telefónica. La empleada me ofrecía el cambio de router y una serie de mejoras gratuitas. Al final de una larga y convincente explicación, me aseguró que para llevar a cabo la operación era imprescindible que le diera el número de mi cuenta corriente, pero, por motivos de seguridad, no debía proporcionarle los dos dígitos de control. Naturalmente, me despedí cortésmente y colgué.

Este intento de... ¿cómo llamarlo? me ha proporcionado el tema del artículo de hoy: los Dígitos de Control de las cuentas bancarias.

Todas las cuentas bancarias tienen un número de identificación. Este número, llamado Código Cuenta Cliente (CCC), está compuesto por veinte dígitos que corresponden a:

• Código de la Entidad (los 4 primeros dígitos) donde radica la cuenta. Lo proporciona el Banco de España.
• Código de la Oficina (los 4 dígitos siguientes) que identifica la oficina donde el cliente tiene abierta la cuenta.
• Dígitos de Control (2 dígitos: el noveno y el décimo). El primero sirve para verificar los Códigos de Entidad y Oficina; el segundo para verificar el Número de Cuenta.
• Número de Cuenta (los 10 últimos dígitos) que incluye todos los identificadores de índole interna que la Entidad desee utilizar para individualizar cada cuenta.

La determinación de los dos Dígitos de Control se realiza de acuerdo a las especificaciones de la Norma bancaria 34 de la AEB (Asociación Española de Banca). Para el cálculo de los dígitos hay que realizar una serie de operaciones, que se mostrarán en el ejemplo que sigue, basándose en la siguiente tabla ponderada:

Consideremos el caso hipotético de un cliente que ha abierto una cuenta bancaria en la Entidad 0210 y en la Oficina 0345. La entidad le asigna el Número de Cuenta 0000067892 y dos Dígitos de control que vamos a determinar.

El CCC de la cuenta será 0210 0345 xz 0000067892. Debemos calcular los valores de los dos Dígitos de control representados por xz.

• El dígito x se obtiene a partir de los 8 dígitos de la Entidad y la Oficina (02102345) multiplicándolos ordenadamente por los 8 primeros valores de la tabla y sumando los resultados.
  
  

• A continuación, se obtiene el resto de la división entre la suma (84) y 11. El resultado es 7.
• El siguiente paso consiste en restar 11 menos el resto anterior: 11-7=4
• El número obtenido es el primer valor (x) de los Dígitos de Control, salvo que la resta anterior sea 10 u 11. Si es 10, x toma el valor 1; si es 11, toma el valor 0. En nuestro caso, no se da ninguna de estas excepciones, por lo que x valdrá 4.

Ya hemos determinado el valor del primer dígito. Vamos a por el segundo.

• El dígito z se obtiene a partir de los 10 dígitos del Número de Cuenta (0000067892) multiplicándolos ordenadamente por los 10 valores de la tabla y sumando los resultados.

• Volvemos a repetir el mismo proceso que en el caso anterior. Hallamos el resto de la división entre la suma (218) y 11. El resultado es 9.
• Restamos 11 menos el resto: 11-9=2
• Si el valor obtenido es 10, z valdrá 1; si es 11, 0; si es cualquier otro número, z será ese número. Por tanto, en el ejemplo que estamos manejando, z es 2.

En conclusión, los Dígitos de Control son x=4 y z=2 y el Código Cuenta Cliente (CCC) es 0210 0345 42 0000067892.

Una vez conocida la mecánica del cálculo de los Dígitos de Control, vamos a hacer un ejercicio en el que dado un Código Cuenta Cliente (CCC) determinemos si es un código correcto o incorrecto. Para ello, calcularemos los Dígitos de Control y los compararemos con los del CCC.

El Código Cuenta Cliente lo escribiremos en la celda C5, los cálculos los haremos en las columnas F:M y el resultado lo pondremos en C7.

El primer paso consiste en asignar formato de texto a la celda C5. Si no lo hacemos, Excel le asignará el formato general y el número quedará expresado en modo exponencial (2,10035E+18). Para hacerlo, en el menú contextual, accedemos a Formato de celdas y, en la pestaña Número, elegimos la categoría Texto.

En C5 escribimos los 20 dígitos de la cuenta: 02100345420000067892

Para obtener el primer Dígito de Control, extraemos en F3 los 8 primeros números de la cuenta y, debajo, ponemos cada dígito en una fila distinta.

En F3:

=IZQUIERDA(C5;8)    [Resultado: 02100345]

En F4:

=EXTRAE($F$3;9-FILA(A1);1)    [Resultado: 5]

Extendemos la fórmula de la celda F4 hasta la fila 11.

En la columna G obtendremos el producto de cada dígito con los valores correspondientes de la tabla ponderada. Esta tabla la tenemos en el rango L3:M13.

En G4:

=F4*M4    [Resultado: 30]

Extendemos la fórmula de la celda G4 hasta la fila 11.

Sumamos los números de la columna G, dividimos la suma por 11 para quedarnos con el resto y hallamos la diferencia entre 11 y este valor. Finalmente, comprobamos si el resultado es distinto de 10 u 11 y obramos en consecuencia. Todo esto lo hacemos en G15:G18.

En G15:

=SUMA(G4:G11)    [Resultado: 84]

En G16:

=RESIDUO(G15;11)    [Resultado: 7]

En G17:

=11-G16    [Resultado: 4]

En G18:

=SI(Y(G17<>10;G17<>11);G17;SI(G17=10;1;0))    [Resultado: 4]

Ya hemos descubierto que el primer Dígito de Control es el 4. El segundo dígito se obtiene de forma similar. Primero, extraeremos los 10 últimos números de la cuenta y los pondremos en filas diferentes.

En I3:

=DERECHA(C5;10)

En I4:

=EXTRAE($I$3;11-FILA(A1);1)    [Resultado: 2]

Extendemos la fórmula de la celda I4 hasta la fila 13.

En J4:

=I4*M4    [Resultado: 12]

Extendemos la fórmula de la celda J4 hasta la fila 13.

En J15:

=SUMA(J4:J13)    [Resultado: 218]

En J16:

=RESIDUO(J15;11)    [Resultado: 9]

En J17:

=11-J16    [Resultado: 2]

En J18:

=SI(Y(J17<>10;J17<>11);J17;SI(J17=10;1;0))    [Resultado: 2]

El segundo Dígito de Control es el 2.

Sólo nos falta comprobar si los dígitos noveno y décimo del CCC que nos han proporcionado forman 42. Si coincide, la cuenta será correcta; en caso contrario, será incorrecta.

En C7:

=SI(ESBLANCO(C5);"";SI(G18&J18=EXTRAE(C5;9;2);"Correcto";"Incorrecto"))    [Resultado: Correcto]

Cuando os pidan el número de vuestra cuenta bancaria y os digan que, por motivos de seguridad, no les proporcionéis los dígitos de control... ¡cuidado! Hay trampa. Como acabamos de comprobar en este ejercicio, los números ocultos se pueden obtener fácilmente.

 Descargar archivo (SE73-DC.xlsx)

 Descargar archivo (SE73-DC.xls)

Rastrear una fórmula

Cuando una fórmula compleja devuelve un resultado incorrecto podemos rastrearla para encontrar el fallo y corregirlo. También tendremos que rastrear la fórmula si ha sido escrita por otra persona y no la entendemos. Esta operación puede hacerse mediante un procedimiento manual (tecla F9) o usando una herramienta de Excel (Evaluar fórmula).

Comencemos por el procedimiento manual. Necesitamos una fórmula compleja; por ejemplo, la que se muestra en la celda Q8.

La barra de fórmulas muestra la fórmula que hay en la celda. Es una fórmula complicada y difícil de entender. Cada función hará algo..., ¿pero qué? Por ejemplo, ¿qué hace la expresión: CONTAR($G8:P8)<$E8?

Descubrirlo es facilísimo. En la barra de fórmulas, seleccionamos con el ratón la expresión a evaluar; en nuestro caso: CONTAR($G8:P8)<$E8.

Una vez hecha la selección, pulsamos F9 y Excel sustituirá la fórmula por su valor. En el caso que nos ocupa, el valor devuelto será VERDADERO.

Para recuperar la fórmula original tenemos que pulsar Esc. Pero no vamos a hacerlo para seguir realizando nuevas comprobaciones. Por ejemplo, seleccionamos: Q$3=$C8

Pulsando F9 obtenemos:

De este modo, rastreamos la fórmula para descubrir por qué se obtiene un determinado resultado o para buscar los errores que hayamos podido cometer. Terminaremos pulsando Esc para recuperar la fórmula.

El otro procedimiento es Evaluar fórmula. Se trata de una herramienta que hace lo mismo que hemos hecho con F9 pero la selección de lo que se quiere evaluar no la hace el usuario sino que la decide Excel, siguiendo el orden que utiliza internamente para hacer los cálculos.

Volveremos a usar la fórmula de la celda Q8 para ilustrar el procedimiento a seguir. Con la celda Q8 seleccionada, accedemos al grupo Auditoría de fórmulas de la pestaña Fórmulas y elegimos Evaluar fórmula.

Excel muestra la ventana correspondiente.

En esta ventana, Excel subraya la operación que va a hacer en primer lugar. En el ejemplo, va a obtener el dato que hay en la celda P8. Para que se realice este cálculo debemos hacer clic en el botón Evaluar. Como en P8 hay un 6, Excel sustituirá P8 por 6 y subrayará la operación siguiente:

La próxima operación será: ESNUMERO(6). Volviendo a pulsar el botón Evaluar, Excel comprueba si el 6 es un número. Como, efectivamente, lo es, devuelve VERDADERO y subraya la nueva operación.

Repitiendo el proceso podremos rastrear, paso a paso, toda la secuencia de cálculos que hace el ordenador. Cuando hayamos terminado pulsaremos en botón Cerrar.

Cuándo usar SUBTOTALES en lugar de otras funciones

Se puede usar SUBTOTALES en sustitución de las siguientes funciones: PROMEDIO, CONTAR, CONTARA, MAX, MIN, PRODUCTO, DESVEST, DESVESTP, SUMA, VAR y VARP. La sintaxis es:

SUBTOTALES(núm_función;ref1;[ref2];...)

• núm_función: es un número del 1 al 11 ó del 101 al 111.

• Ref1: es el rango al que se aplica la función. Obligatorio.
• Ref2, Ref3...: son los otros rangos a los que se les aplica la función. Opcional.

Por ejemplo, si queremos sumar las celdas del rango A1:A5 lo normal es poner: =SUMA(A1:A5), pero obtendremos el mismo resultado usando la función SUBTOTALES, con los números 9 ó 109 como primer argumento, de esta manera: =SUBTOTALES(9;A1:A5) o =SUBTOTALES(109;A1:A5). De la misma forma, podremos sustituir por SUBTOTALES cualquiera de las funciones mostradas en la tercera columna del cuadro anterior.

¿Por qué usar una fórmula más compleja si podemos obtener el mismo resultado con otra más simple? Esta es la pregunta que vamos a tratar de responder en este artículo. Para ello, partiremos de la tabla de valores C2:F12:

En las filas 14 a 28 hemos hecho unos cuantos cálculos con funciones convencionales y con SUBTOTALES. Por ejemplo, en D14 la fórmula es: =SUMA(D3:D12); es decir, una suma sencilla. En D15 hemos usado SUBTOTALES con el argumento 9: =SUBTOTALES(9;D3:D12). En D16 hemos usado el argumento 109: =SUBTOTALES(109;D3:D12).

En el resto de las celdas de la columna D, las fórmulas son:

En D18: =PROMEDIO(D3:D12)

En D19: =SUBTOTALES(1;D3:D12)
En D20: =SUBTOTALES(101;D3:D12)

En D22: =MAX(D3:D12)
En D23: =SUBTOTALES(4;D3:D12)
En D24: =SUBTOTALES(104;D3:D12)

En D26: =CONTAR(D3:D12)
En D27: =SUBTOTALES(2;D3:D12)
En D28: =SUBTOTALES(102;D3:D12)

Por el momento, no se aprecia ninguna diferencia al usar una función convencional  o SUBTOTALES. Pero veamos lo que ocurre al ocultar algunas filas del rango C2:F12.

Seleccionamos las filas 5, 6, 7, 8 y 9; hacemos clic con el botón derecho en la selección y, en el menú emergente, elegimos la opción Ocultar. El resultado es el siguiente:

Las funciones convencionales que hemos empleado (SUMA, PROMEDIO, MAX y CONTAR) devuelven los mismos resultados; las celdas donde hemos usado la función SUBTOTALES con el primer argumento comprendido entre 1 y 11 también muestran el mismo resultado; sin embargo, los SUBTOTALES obtenidos con argumentos comprendidos entre 101 y 111 han devuelto valores distintos. Comprobamos que esta función hace exactamente lo que está señalado en el encabezado de la tabla que hemos mostrado en la sintaxis de la función: pasa por alto valores ocultos.

Ahora que ya sabemos que los argumentos del 101 al 111 se deben emplear para operar únicamente con los datos visibles excluyendo los ocultos, nos preguntamos en qué casos son útiles los argumentos que van del 1 al 11.

Para responder a esta pregunta, comenzamos borrando todas la fórmulas de las filas 14 a 28. También debemos mostrar las filas ocultas. Para ello, seleccionamos las filas 4 y 10, hacemos clic con el botón derecho en la zona seleccionada y elegimos Mostrar.

Necesitamos transformar el rango de datos con el que vamos a trabajar en una tabla. Lo haremos seleccionando C2:F12 y eligiendo Insertar + Tabla. Excel mostrará la ventana Crear tabla.

Nos aseguramos de que estén seleccionadas las opciones de la figura y pulsamos Aceptar.

En D14 escribimos =SUMA(, seleccionamos con el ratón el rango D3:D12 y pulsamos Entrar. Excel escribirá la siguiente fórmula: =SUMA(Tabla1[Poducto1]) 

Por defecto, la tabla que hemos creado tiene de nombre Tabla1. Por ese motivo, la expresión Tabla1[Poducto1] hace referencia al rango D3:D12.

Nota: Si queremos, podemos cambiar Tabla1 por otro nombre accediendo a Fórmulas + Administrador de nombres. En la ventana Administrador de nombres, seleccionamos Tabla1, pulsamos el botón Editar y escribimos el nuevo nombre.

Siguiendo este procedimiento, ponemos las siguientes fórmulas:

En D14: =SUMA(Tabla1[Poducto1])

En D15: =SUBTOTALES(9;Tabla1[Poducto1])
En D16: =SUBTOTALES(109;Tabla1[Poducto1])

En D18: =PROMEDIO(Tabla1[Poducto1])
En D19: =SUBTOTALES(1;Tabla1[Poducto1])
En D20: =SUBTOTALES(101;Tabla1[Poducto1])

En D22: =MAX(Tabla1[Poducto1])
En D23: =SUBTOTALES(4;Tabla1[Poducto1])
En D24: =SUBTOTALES(104;Tabla1[Poducto1])

En D26: =CONTAR(Tabla1[Poducto1])
En D27: =SUBTOTALES(2;Tabla1[Poducto1])
En D28: =SUBTOTALES(102;Tabla1[Poducto1])

Las fórmulas devuelven los mismos resultados que en el ejemplo anterior.

Al transformar el rango en una tabla conseguimos varias cosas:

• Poder añadir nuevas filas a la tabla sin que sea necesario cambiar las fórmulas de las filas 14 a 28. Las fórmulas se recalcularán automáticamente teniendo en cuenta los nuevos valores incorporados a la tabla.
• Filtrar los datos abriendo la lista asociada a las flechas de los encabezados

Esta última particularidad es la que vamos a usar. Necesitamos filtrar los datos de manera que los cálculos se realicen excluyendo las filas correspondientes al periodo comprendido entre el 04/03/2012 y el 07/03/2012. Para ello, hacemos clic en la flecha del primer encabezado de la tabla y desmarcamos los cuadros de verificación de las fechas señaladas a continuación (tendremos un control mayor si elegimos Filtros de fecha):

Pulsamos Aceptar y comprobaremos, igual que en el caso anterior, que las fórmulas convencionales siguen operando con toda la tabla, mientras que las celdas que usan SUBTOTALES eliminan de los cálculos las filas filtradas. Esto ocurre tanto si utilizamos los parámetros de 1 al 11 como si usamos los comprendidos entre 101 y 111.

Sin quitar el filtro añadimos dos nuevas filas y comprobamos que los nuevos valores modifican las fórmulas sin que intervengan los valores filtrados (en las fórmulas convencionales los valores filtrados sí son tenidos en cuenta).

Descargar archivo (S308-Subtotales.xlsx)