BuscarV en varias hojas y en varios libros

Ya en alguna ocasión hemos hablado de la utilidad de la función BuscarV de Excel para buscar y extraer datos en una tabla (puedes mirar cómo es aquí, en este post). Casi con toda seguridad, si manejas de manera habitual muchos datos, habrás tenido ocasión de usarla y la conoces. Si no es así, desde luego te animo a que lo hagas, estoy segura de que no lo lamentarás y no es nada complicada.

Como probablemente sepas, la función BuscarV encuentra un valor en la primera columna de una tabla y extrae el dato que le digas de esa misma línea. Hoy vamos ir un poco más lejos y  suponer que por la circunstancia  que sea, tienes los datos de tu tabla distribuidos en varias hojas. A lo mejor no es lo más frecuente, pero se podría dar.

Sigue leyendo

Anuncios

Comprobar si los números de cuentas bancarias son correctos y que Excel te calcule los dígitos de control

En esta ocasión he realizado dos funciones de usuario para calcular los dígitos de control de una cuenta bancaria y para comprobar si el número de ésta es correcto.
¿Por qué una función de usuario?
Realmente los números de las cuentas son muy largos y es muy fácil equivocarse en algún dígito al teclearlos. Usando estas funciones puedes evitarte algún que otro disgusto. Por ejemplo, si envías recibos para cobrar y resulta que algún número de cuenta no es correcto, el banco lo devuelve. Además de devolverlo, el banco te cobra un dinero por cada recibo que no consiguió cobrar, y esta cantidad no es precisamente pequeña. Si puedes hacer lo posible por detectar estos errores, mejor que mejor ¿no?. Y si esa comprobación la realiza de forma automática Excel por ti, todavía más.
Sigue leyendo

Funciones de usuario en Excel

El programa Excel cuenta con muchas, muchísimas funciones. Puede que conozcas solo algunas o que tengas un conocimiento extenso de todas ellas. Pero lo que es seguro es que si usas Excel, en algún momento habrás tenido que usarlas. Seguro que te suenan las funciones suma, contar, etc. Las puedes ver todas pulsando en un pequeño símbolo que aparece en un lado de la barra de fórmulas (también puedes verlas en la cinta de opciones, pero es más rápido así) :

Sigue leyendo

Escribir de forma rápida los argumentos de una función de Excel

Cuando estás escribiendo una función en Excel, justo en el momento en el que escribes el primer paréntesis, verás que aparece una etiqueta con la sintaxis de ésta. Es ella aparecen detallados los argumentos que has de escribir, en qué orden y de qué manera, tal y como se puede ver en la imagen siguiente:

argumentos
argumentos

Si en ese momento pulsas la combinación de teclas Ctrol + May + A, comprobarás que la etiqueta se traslada a la barra de fórmulas, aparece el primer argumento seleccionado y puede ayudarte a recordar la forma en la que tienes que escribir la función. Puedes seleccionar los rangos de la misma forma que lo harías normalmente. Recuerda que los argumentos que aparecen entre corchetes [ ] son opcionales y por lo tanto puedes ponerlos o no según lo que necesites en ese momento.

Formatos condicionales para encontrar un texto en una lista de datos

Lo cierto es que Excel dispone de una gran variedad de formatos condicionales que ayudan a resaltar los datos de forma visual y destacar aquellos que cumplen determinadas condiciones. Son muy fáciles de usar, merece la pena practicar con ellos ya que normalmente el resultado obtenido puede ser muy atractivo.

Pero uno de las posibilidades más interesantes de los formatos condicionales es la de poder aplicarlos a aquellas celdas que cumplen condiciones que especificaremos por medio de fórmulas. Son algo más complejas, sin embargo pueden llegar a tener aplicaciones muy útiles y sernos de gran ayuda.

Vamos a suponer que tenemos una amplia lista de diversos artículos que vendemos, similar a ésta:

Formato condicional
Formato condicional

Ahora deseamos que al introducir un texto en la celda B3, se nos indique de alguna manera las celdas que contienen dicho texto. Por ejemplo, si en B3 escribimos la palabra Flexo, se resaltan las celdas B11 y B12, que son las que la contienen. Una posibilidad es usar un formato condicional. Para ello seleccionamos previamente el rango de la lista (B6:B17 en el ejemplo) y elegimos la pestaña Inicio. En el grupo Estilos, la opción Formato condicional. Aparece un menú a continuación, elegiremos Nueva regla (pues es la primera vez que la establecemos, aunque también en Administrar reglas, se pueden establecer, modificar, etc.). En el cuadro de diálogo siguiente:

Formato condicional2
Formato condicional2

Hemos elegido la última opción de la lista, aquella que pone “Utilice una fórmula…”. Un poco más abajo leemos: “Dar formato a los valores donde esta fórmula sea verdadera”. En nuestro ejemplo queremos que se señalen aquellos valores de la lista de datos que contengan lo que escribamos en la celda B3. Usaremos para ello la función encontrar, que tiene la siguiente sintaxis:
=encontrar(textobuscar,texto)
El primer argumento indica el texto que deseamos buscar y el segundo dónde se buscará, en qué texto. Por ejemplo, supongamos que escribimos en nuestra hoja de cálculo la siguiente fórmula:
=ENCONTRAR("el","Siempre es el mismo")
El resultado es 12. Si contamos, “el” comienza a partir de la letra o posición 12 en el texto “Siempre es el mismo”. Hay que recordar que se cuentan los espacios en blanco y que distingue entre mayúsculas y minúsculas.
Volviendo a nuestro ejemplo escribiríamos la fórmula siguiente en el apartado “Dar formato a los valores…”:
=ENCONTRAR($B$3,B6)
B3 es la celda que contiene el valor a buscar y es fija (por eso le ponemos los símbolos $), pues siempre ocupará la misma posición. B6 es la primera celda del rango en el que buscaremos, no es fija. Una vez introducida la fórmula, no debemos olvidar dar al botón “Formato” a fin de especificar cómo queremos que nos señale las celdas en cuestión, podemos elegir que se aplique un color azul, por ejemplo. Si olvidáramos este paso, fundamental, aunque la fórmula sea correcta, no funcionaría, pues no habría formato que aplicar. Aceptamos entonces en todas las ventanas hasta volver a nuestra hoja de cálculo. Vemos con asombro que se han señalado todas las celdas en azul. Sin embargo, si colocamos el cursor en B3 y escribimos Tornillos, se nos indican las celdas B6 y B14. ¿Por qué si B3 está vacía se señalan todas? porque todas las celdas cumplen la condición impuesta. ¿Cómo podemos evitarlo? Alterando la fórmula y añadiendo además otra condición de la forma siguiente:
=Y($B$3"",ENCONTRAR($B$3,B6))
Es decir, que B3 no esté vacía.
Otro problema que encontramos es que si escribimos en mayúsculas todo o minúsculas no lo encuentra. Ya comentamos anteriormente que la función encontrar distingue mayúsculas y minúsculas. Por ejemplo si ponemos TORNILLOS, no se encontrará, pues en la lista aparece escrito Tornillos. Si queremos evitar este problema podemos hacer la búsqueda convirtiendo todo, tanto el texto buscado, como en el que buscamos, a mayúsculas o a minúsculas de la forma siguiente:
=Y($B$3"",ENCONTRAR(MAYUSC($B$3),MAYUSC(B6)))
Con ésta sí funcionará independientemente de cómo escribamos la palabra a encontrar. En la imagen buscamos CABLE, el resultado es correcto:
condicional3

Fórmula para calcular la letra del NIF

En España se utiliza el NIF. Este número, que es identificativo a efectos fiscales, se calcula operando con el número del DNI de cada persona de la forma siguiente:

  • El número del DNI se divide entre 23
  • Lo importante de la anterior operación es el resto obtenido. Este resto estará entre 0 y 22, como es lógico.
  • Tomamos el resto y sumamos uno.
  • Con el número obtenido, miramos la letra que tiene dicha posición en la siguiente cadena de caracteres: “TRWAGMYFPDXBNJZSQVHLCKE”. Por ejemplo, si de las operaciones anteriores obtuvimos un 6, corresponde la letra M.

Para poner esta fórmula en Excel tenemos las siguientes funciones:

  • Residuo(dividendo;divisor) : nos devuelve precisamente el resto de dividir un número (dividendo) entre otro (divisor). Por ejemplo si ponemos =residuo(10;3) el resultado es uno, ya que al dividir 10 entre 3, el resto es 1. Si ponemos =residuo(25;5), obtenemos 0, ya que 25 es múltiplo de 5 y al hacer la división el resto es cero.
  • Extrae(Texto;inicio;caracteres): nos devuelve el número de caracteres especificado en el tercer argumento, a partir de inicio, del texto indicado como primer argumento. Por ejemplo, si ponemos =extrae(“Este es un ejemplo de Excel”;9;2), nos devuelve “un” porque si contamos las letras contenidas en el texto “Este…” vemos que la novena posición (se incluyen espacios en blanco) corresponde a la u, y si tomamos 2 letras (o caracteres) tenemos “un”.

Hacemos ahora la fórmula en Excel (suponemos que el número del DNI está en A1):
=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKE";RESIDUO(A1;23)+1;1)
Que leeríamos de la forma siguiente:

De la cadena “TRW…” extrae un carácter (el tercer argumento es un uno), comenzando en el número que obtenemos como resto de dividir lo que hay en A1 por 23, y sumarle 1. (Residuo(A1;23)+1.

La función Buscarv

Probablemente sea una de las funciones más útiles de Excel. Nos puede ayudar a extraer datos de una tabla o lista de datos de forma muy rápida. Buscav es no es nada complicada de usar. Y aunque tiene varios argumentos, una vez se comprenden éstos, resulta bastante sencilla.
La sintaxis general de la función es como sigue:
BUSCARV(valor,matriz,columna,ordenado)
Veamos con más detalle a qué corresponde cada uno de los argumentos de la función:

  • Valor: Se refiere al dato que buscamos. Buscarv tiene la peculiaridad, eso sí, que el valor que queremos encontrar deberá estar en la primera columna de la tabla de datos en la que busquemos.
  • Matriz: Es el rango que ocupa la tabla de datos en la cual vamos a consultar.
  • Columna: Aquí pondremos un número que corresponderá a la posición de la columna en la que está el dato que queremos extraer dentro de la matriz anterior. Es tan sencillo como contar las columnas comenzando por la izquierda y poner el número de la que contiene lo que queremos que nos muestre.
  • Ordenado: Este argumento es opcional. Si no lo ponemos tendrá el valor 0/FALSO, ya que es el que se asigna por defecto. Solo puede tener dos valores: 0 ó 1, o sus equivalentes: FALSO/VERDADERO. Si ponemos 0 o FALSO (opción por defecto), se buscará una coincidencia exacta con el valor buscado y en el caso de no encontrarla, mostrará error. Si ponemos 1 o VERDADERO, no tendremos error, ya que en caso de no encontrarse exactamente lo que buscamos, mostrará el valor más cercano. Depende del tipo de búsqueda que estemos haciendo, unas veces interesará una opción u otra. Veremos un ejemplo para cada una para que quede más claro.

Vamos poner un ejemplo práctico, seguro que de esta forma se entenderá mucho mejor.
Supongamos que tenemos la siguiente tabla de datos:

buscarv
buscarv

Se trata de una lista de alumnos, sus datos personales, notas y asistencia a clase. Hemos puesto cada uno de los alumnos en una fila y cada uno de los datos en una columna. Así por ejemplo, el alumno 3 es María González Rubio, su nota es un 4,5 y asistencia 50.
Supongamos que ahora quiero hacer una ficha en la que solo con introducir el número que identifica al alumno, me aparecerán el resto de datos que le corresponda. En esta tabla tenemos muy pocos datos, pero si tuviéramos cientos o miles de registros, entonces sí sería de gran ayuda poder localizarlos con solo ese dato ¿verdad?. Hemos hecho la siguiente entrada (puede situarse en la misma hoja o en otra distinta, según prefieras o necesites):

buscarv
buscarv

Queremos que en la posición del cursor, la celda C19, se introduzca el número de alumno y automáticamente nos rellene los datos correspondientes en las celdas C20 (nombre), C21 (apellidos), C22 (nota media) y C23 (asistencia). Para ello en cada una de ellas pondremos una fórmula buscarv que extraerá el dato correcto. Todas ellas tienen en común que extraerán los datos de la misma tabla de datos, la que habíamos situado en el rango A6:E13 (no es necesario que incluyas en el rango los encabezados de la columna). También tienen en común que lo que buscaremos será el número del alumno que está situado en C19. Estos dos datos son iguales para todas las fórmulas, si quieres que haciendo la primera puedas copiarla en las demás, debes hacer absolutas estas direcciones poniendo el símbolo $ delante, así: $C$19 y $A$6:$E$13. Ya tenemos los dos primeros argumentos que hemos de poner en la función: qué buscamos y dónde lo buscamos, fácil ¿no?.
El nombre del alumno está situado en la segunda columna de la tabla de datos, los apellidos en la tercera columna, la nota y la asistencia en la cuarta y quinta respectivamente. Estos datos serían el tercer argumento de la función.
En cuanto al cuarto argumento, hemos de observar que cuando hagamos una búsqueda de un alumno deben aparecerme los datos de ese alumno en cuestión, no se puede admitir ninguna aproximación. En el caso de que el alumno no se encuentre, es mejor que se avise con un error a que aparezcan los datos de otro. Por lo tanto el último argumento será 0 o FALSO. Como esta opción es la que se considera por defecto, podemos omitir este argumento, ya que es opcional.
Vamos pues con las fórmulas.
Nos situamos en la celda en la que deseamos obtener el primer dato, el nombre del alumno, la celda C20, y escribimos la siguiente fórmula:
=BUSCARV($C$19;$A$6:$E$13;2;0)
Recordemos:

  • $C$19 Celda donde pondremos el número a buscar
  • $A$6:$E$13 Rango en el que se encuentra la tabla de datos a consultar
  • 2 número de columna en la que se encuentra el nombre
  • 0 significa que la coincidencia del dato a buscar debe ser exacta

Una vez completada la fórmula, damos enter. Si te ha aparecido un error, se debe a que en la C19 no tienes ningún dato aún. Escribe un número que se encuentre en la tabla de datos, por ejemplo 7. Te debe aparecer el nombre del alumno.
De la misma manera completaremos el resto de fórmulas:

buscarv
buscarv

Observa cada una de las fórmulas anteriores y las referencias que se usan como argumentos. El resultado es el siguiente:

buscarv
buscarv

Situando el cursor en la celda C19, y cambiando el número de alumno, aparecerán los datos de ese alumno de forma instantánea .