domingo, 29 de agosto de 2010

Nuevas formas de escribir con macro

Nuevas formas de escribir en las celdas de la hoja de cálculo con Macro. Usaremos Range y Cells combinadas. También veremos cómo se hace un comentario del programador escribiendo una comilla simple ('), o con Rem.

Para elevar a potencias usamos el acento circunflejo francés (^).



Código:

Sub escribe2()
Rem para elevar usamos el acento circunflejo francés ^
Range("C1:D6") = 2 ^ 3 + 10
Range("B8,B10,B12:C12") = "Madrid"
End Sub
Sub escribe3()
Range(Cells(1, 5), Cells(4, 8)).Value = "Hola"
Range("A5").Cells(3, 1).Value = 700
Range("A5:B10").Cells(2, 1).Value = "Bye"
End Sub


Escribir en un rango con un bucle For...Next

Si deseamos escribir en una serie de celdas, por ejemplo en el rango A1: A10 disponemos de varios métodos, que usan una variable, por ejemplo i, que hacemos variar entre 1 y 10.

  • Cells(i,"A") = "Pedido" & i
  • Range("B"&i) = "Envío " & i


viernes, 27 de agosto de 2010

Escribir en una hoja desde una macro

Podemos escribir en una hoja de Excel desde una macro. Para ello utilizaremos entre otros Range y Cells. También veremos cómo se procede cuando se ha producido un error al ejecutar una macro. Con Range podemos usar Value o no, aunque esta es una de las pocas veces que podremos saltarnos la estricta sintaxis de un lenguaje de programación.




Las macros comienzan por Sub seguido del nobre de la macro y un paréntesis abierto y otro cerrado. Dentro de los paréntesis normalmente no se escribe nada, aunque ya veremos más adelante que en ocasiones se escriben variables. La macro finaliza con End Sub. Entre ese comienzo y ese final se encuentran las líneas de comandos o sentencias. Al ejecutar una macro ésta se ejecuta secuencialmente de arriba hacia abajo.

Sub nombre_macro()
   Sentencia 1
   Sentencia 2
   Sentencia 3
   Sentencia 4
End Sub

El nombre de la macro no acepta espacios en blanco. Esto supone que, si son dos palabras, o bien se escriben juntas, o bien se separan con barra baja, como en nombre_macro.

Código:

Sub escribe()
Range("B1").Value = 100
Range("B2").Value = "Felicidades"
Range("B3") = 200
Range("B4") = "Bienvenido"
Cells(5, 2) = "Córdoba"
Cells(6, "B") = "Cádiz"
[B7] = 700
End Sub

lunes, 23 de agosto de 2010

Mi primera macro

Mi primera macro en Excel 2007. Lo primero que necesitamos en la versión 2007 de Excel es disponer de la ficha PORGRAMADOR dentro de la cinta de opciones. La ficha Programador no viene por defecto al instalar Excel, hemos de activarla a posteriori.

Para activar la ficha programador puede visitar un post anterior donde se explica esto:

Ficha Programador

Seguidamente se muestra en vídeo el procedimiento para crear nuestra primera macro.



Supongamos que se nos olvidó cerrar el paréntesis. En este caso el Editor de Visual Basic nos ayuda marcando en rojo la línea y mostrando un mensaje de error.


Nosotros hemos saludado diciendo "Buenos días" pero es muy habitual que el primer programa que se hace de un lenguaje de programación nos salude diciendo "Hola mundo" (en inglés Hello World).

Actualización para Excel 2010

En Excel 2010 seguiríamos los mismos pasos que se muestran en el vídeo para la versión de Excel 2007. En este aspecto no hay diferencias.

Actualización para Excel 2016

En la versiones nuevas de Excel la pestaña ya no se llama 'Programador', ahora la pestaña se denomina 'Desarrollador'.

jueves, 19 de agosto de 2010

Valoración de Rentas a tipo variable

Descargar el fichero: Rentas.xlsm
Calcular el Valor Actual y el Valor Final de una renta de cuantía constante es sencillo con las funciones de Excel VA y VF. Calcular el valor actual o final de una renta en la que el tipo de interés varía ya no es tan obvio. Para calcular el Valor Actual tendremos que capitalizar cada cuantía hasta el final de la renta, aplicando el tipo de interés vigente en cada momento a medida que la cuantía atraviesa los diferentes periodos hasta llegar al final (t=n).

Hoja E11

Nos vamos a la Hoja E11 del fichero Rentas.xls.Vamos a calcular el VA y el VF de una renta de 60 términos mensuales pospagables valorada a diferentes tipos de interés cada uno de los 5 años de duración. La cuantía de los términos de la renta también son variables siendo las mensualidades del primer año de 1.000 €, las del segundo año 1.200 €, y así con incrementos de 200 € cada año, hasta llegar al quinto año donde la cuantía mensual es de 1.800 €.

Utilizaremos dos métodos. El método 1 trabaja en meses, por lo que necesitamos una tabla de 60 meses. El método 2 trabaja en años, debido a que durante cada año la cuantía mensual es constante y el tipo dentro del año no varía. Esto nos permite valorar la renta de cada año al final de ese año, y así con los valores anualizados podemos trabajar como si se tratara de una renta anual.

Método 1 (trabajando en meses)


Columna B: Mes

Rellenamos los meses de 0 a 60. Comenzamos en cero, pero al tratarse de una renta pospagable la primera cuantía vence al final del primer mes, que corresponde al instante 1.

Columna C: Año

Se calcula con fórmula el año. Así la celda C8 contiene la siguiente fórmula:

=SI(RESIDUO(B8;12)=1;C7+1;C7)

Observar que en Excel siempre que deseamos tratar valores periódicos es muy frecuente utilizar la función RESIDUO que calcula el resto. Si estuviéramos programando en VBA usaríamos MOD (módulo) que en español se denomina resto.

Otra forma de conseguir la columna de años sin necesidad de fórmulas muy elaboradas es la siguiente. Durante los primeros 12 meses escribimos un 1. En la celda que se corresponde con el primer mes del segundo año (celda C20) pondríamos esta fórmula: =C8+1. Finalmente copiaríamos esta fórmula hacia abajo.

Columna D: Tipo anual

En nuestro caso el tipo de interés varía cada año comenzando en el 5%, e incrementándose un punto cada año, hasta llegar al 9% en el quinto año. Estos datos se encuentran en una tabla a la derecha. Tabla que posteriormente completaremos para obtener el método 2 de resolución. Creamos un nombre de rango denominado tabla que es: K7:S11.

La fórmula utilizada en la celda D8, que luego copiaremos hacia abajo, es la siguiente.

=BUSCARV(C8;tabla;2;0)

Esto nos proporciona el tipo de interés efectivo anual.

Columna E: Tipo mensual

Tomamos el tipo mensual de la tabla de la derecha como se ve en la fórmula de la celda E8:

=BUSCARV(C8;tabla;3;0)

Columna F: Renta

La celda F8 nos proporciona el importe de los términos de la renta, y para ello consulta la tabla de la derecha  con la siguiente fórmula.

=BUSCARV(C8;tabla;5;0)

Columna G: Factor

El factor en finanzas es igual a uno más el tanto. En este caso lo que hacemos es sumar 1 al tipo mensual de la columna anterior. En finanzas es muy típico trabajar con el famoso (1+i) que es el factor que elevado a exponente negativo y multiplicado por la cuantía, la capitaliza. Por el contrario, elevado a exponente negativo y multiplicado por la cuantía, lo que hace es descontarla, tantos periodos como indique el exponente.

La celda G8 es simplemente: =1+E8.

Columna H: VA


Calculamos el Valor Actual de cada término de la renta. Lo que hacemos es llevar financieramente hasta el origen de la renta (t=0) cada una de las cuantía que la componen. La peculiaridad en este caso es que el tanto es variable por lo que se ha de dividir (o multiplicar con exponente negativo) entre el producto de todos los factores que son necesarios para llegar hasta su valor actual.

En Excel existe la función PRODUCTO que multiplica todos los elementos que se indiquen en su rango. Así la celda H8 tiene la siguiente expresión:

=+F8/PRODUCTO($G$8:G8)

Esta fórmula se copia hacia abajo. Para analizar mejor la fórmula tomemos como ejemplo la de la celda H12:

=+F12/PRODUCTO($G$8:G12)

En esta fórmula se toma la cuantía de la celda F12 que son los 1.000 € que vencen en t=5 y se han de descontar 5 meses. Este es el motivo de que el argumento de la función PRODUCTO sea G8:G12.

Observar que en el argumento de la función PRODUCTO la celda G8 se fija con dólares. Esto es así para que al copiar la fórmula hacia abajo siempre se descuenten las cuantías de la renta hasta el momento inicial (t=0).

El Valor Actual de la renta se calcula en la celda P14 sumando todos los valores actuales individuales de todos los términos de la renta. Así P14 es:

=SUMA(H8:H67)

Columna I: VF

Para calcular el Valor Final de la renta capitalizamos cada cuantía hasta el final de la renta (t=60). Comencemos el razonamiento por el final. La celda I67 es simplemente una fórmula que se vincula con la cuantía que vence en ese momento (=+F67) sin necesidad de capitalizar, ya que la cuantía vence justo al final, en t=60.

La celda I66 tiene la siguiente fórmula.

=+F66*PRODUCTO(G67:$G$67)

En esta expresión lo que hacemos es capitalizar la cuantía hasta su valor final. Para ello utilizamos la función PRODUCTO y en su argumento debemos incluir el rango de los factores necesarios. Para la cuantía que vence en t=59, que está en la celda F66, se ha de multiplicar únicamente por un factor que es el que está en la celda G67. Pero como esta fórmula la copiaremos hacia arriba, hemos de poner el rango de factores como G67:$G$67 dentro de la función PRODUCTO. Esto hace que al fijar con dólares la celda G67 los factores implicados vayan siempre hasta el último.

Observar el desfase de filas en la fórmula. Cuando estoy capitalizando la celda F66 llego hasta el factor G67. Para entender esto, siempre es conveniente recordar que una cosa es un instante, por ejemplo el instante en el que vence una cuantía, y otra cosa es un intervalo de tiempo, por ejemplo el que va asociado a un factor mensual es todo un mes.


El Valor Final de la renta se calcula en la celda Q14 sumando todos los valores finales individuales de todos los términos de la renta. Así Q14 es:

=SUMA(I8:I67)



Método 2 (trabajando en años)

En este método podemos trabajar en años gracias a que la cuantía únicamente varía una ver al año, al igual que sucede con la variación del tipo de interés. Vamos a calcular el valor a final de cada año de los términos de la renta que vencen dentro de ese año. Así obtendremos unas cuantías anualizadas equivalentes financieramente a las 12 mensualidades de cada año. Con las anualidades calculadas podremos trabajar como si de una renta anual se tratara.


Las columnas K y L son datos.


Columna M: Tipo mensual

La celda M7 es:

=+(1+L7)^(1/12)-1

Lo que hacemos es considerar que el tipo anual es un efectivo anual y lo que nosotros pretendemos es calcular el efectivo mensual equivalente. Para ello se aplica la expresión: im=(1+i)^(1/m)-1.


Columna N: Factor Anual

Puesto que trabajaremos con la renta anualizada necesitaremos el factor anual que es (1+i).

Columna O: Renta

Los términos de la renta son datos.

Columna P: Renta Anual

La celda P7 tiene la siguiente expresión:

=+VF(M7;12;-O7)

Lo que hacemos es calcular el valor final con VF, ya que se trata de una renta de 12 términos mensuales constantes. El valor obtenido es el de la renta anualizada para el primer año. Luego lo que hacemos es copiar la fórmula hacia abajo y así obtenemos los 5 términos de la renta anualizada.

Columna Q: Renta Anual

La celda Q7 es:

=+P7/PRODUCTO($N$7:N7)

Aquí calculamos el valor actual del primer término de la renta anualizada. Ese término vence al final del primer año y para descontarlo utilizamos el factor anual, esto es, descontamos con el tanto efectivo anual. Esto fórmula se copia hacia abajo y así obtenemos el valor actual de cada una de las cuantías previamente anualizadas.

Columna R: Factor Capitaliz

En esta columna calculamos el Factor de Capitalización. La celda R7 es:

=+PRODUCTO($N8:N$11)

Esta fórmula se copia hacia abajo, con la excepción de la última celda. En la celda R11 debemos poner un 1 de forma manual, ya que si se copia la fórmula anterior hasta esta celda el proceso no funcionará bien. De hecho el motivo de la existencia de esta columna es precisamente este pequeño e importante detalle. Observar que en el caso del cálculo del VA no fue necesaria una columna calculando previamente el Factor de Descuento.


El Valor Actual de la renta se calcula en la celda P15 sumando todos los valores actuales individuales de todos los términos de la renta. Así P15 es:

=SUMA(Q7:Q11)


Columna S: VF

La celda S7 es:

=+R7*P7

Esta fórmula se copia hacia abajo y nos da el valor final de cada término de la renta anualizada.



El Valor Final de la renta se calcula en la celda Q15 sumando todos los valores finales individuales de todos los términos de la renta. Así Q15 es:

=SUMA(S7:S11)


Comprobación




La celda R14 es:

=+P14*PRODUCTO($N$7:$N$11)-Q14

Esta fórmula se copia a la R15 y si obtenemos cero indica que ambos método coinciden.

En la fórmula R14 lo que hacemos es tomar el valor actual calculado por el método 1 y capitalizarlo con el PRODUCTO de los factores anuales. Esto nos debería dar el valor final de la renta, por lo que al restar éste el resultado debe ser cero.

Puede realizar esta comprobación pero utilizando el producto de los factores mensuales. ¿Cuál es el resultado que debiera obtener con la siguiente expresión?

=+P14*PRODUCTO($G$8:$G$67)-Q14

Ejercicio propuesto

Proponemos al lector que realice este mismo caso pero suponiendo la renta prepagable, esto es, con 60 mensualidades pero venciendo la primera al inicio del primer mes (en t=0). Podrá comprobar que las ideas son las mismas pero hay que tener especial cuidado con los factores que se han de utilizar para descontar o capitalizar cada cuantía.

Texto en Columnas

Convertir texto en columnas. Es frecuente disponer de tablas que provienen de otras aplicaciones (contabilidad, almacén, RRHH, ...) y nos las dan en formato de texto, código ASCII, o también llamdao texto plano. En estos casos es necesario una conversión para poder trabajar en Excel. Lo que debemos hacer es convertir ese texto plano en columnas de la hoja de cálculo con las que podamos luego trabajar.

Existen tres métodos:

  1. Trabajar con funciones de cadena. Son las que se encuentran dentro de la categoría texto: EXTRAE, IZQUIERDA, DERECHA, LARGO, VALOR, ....
  2. Utilizar el asistente para convertir texto en columnas. Esta es la opción que veremos en este artículo.
  3. Crear una macro. Esta opción es adecuada para casos repetitivos en los que la macro ha de adaptarse a las peculiaridades del texto que nos llega de otra aplicación.

En el siguiente video se comenta el uso del Asistente para convertir texto en columnas.

domingo, 15 de agosto de 2010

Un test de Excel

Presentamos un test de 10 preguntas de Excel con un nivel inicial-medio. Se trata de un formulario creado con la hoja de cálculo de Google.



viernes, 13 de agosto de 2010

VAN y TIR, evitando errores

Descargar el fichero: v_vantir1.xlsx

Un error frecuente que se comete al calcular el VAN y la TIR es dejar vacia la celda en la que el flujo de caja que vence en ese momento es cero. Dicho de otro forma, si en un momento determinado el flujo de caja es cero, se ha de poner necesariamente ese cero, ya que si se deja vacia la celda el cálculo del VAN y de la TIR será erróneo.


martes, 10 de agosto de 2010

Tablas Dinámicas con Rangos de Consolidación Múltiples

Descargar el fichero: xPoblamun_TD.xlsx

Habíamos llegado a pensar que la posibilidad de crear Táblas Dinámicas con Rangos de Consolidación Múltiple había desaparecido con Excel 2007, pero no es así. En Excel 2007 y en Excel 2010 podemos crear Tablas Dinámicas con información que proviene de varias tablas que pueden estar en la misma hoja, en distintas hojas, o incluso en distintos libros. Esto es lo que se conoce como Rangos de Consolidación Múltiples.

Primero: obtengamos los datos

Vamos a visitar la página del periódico El Pais. Concretamente su Anuario que contiene información estadística y nos la proporcionan en formato de hoja de cálculo Excel. Su dirección es:



Vamos a tomar como datos de partida los correspondientes al anuario de 2007. Luego seleccionamos Economía dentro del apartado Internacional. Llegamos a una página en la que descargamos la hoja de cálculo correspondiente al apartado:

Países y territorios según población



Si todo va bien habremos descargado el siguiente fichero:

xPoblamun.xlsx

Nos centraremos en la Hoja3 que contiene información sobre:

Los países más poblados del mundo 1995-2030

Disponemos de cuatro tablas con los 30 países más poblados en 1995, 2005, 2015 y 2030. Algunos datos son reales y otros previstos.

Segundo: preparemos los datos

Preparamos un poco las tablas. Lo principal es poner en la fila 6 las cabeceras de las tablas. Indicamos la cabecera 'Pais' y para la población utilizamos como cabecera el año: 1995, 2005, 2015, 2030. La República Democrática del Congo la hemos abreviado por Congo. Al final la idea es obtener la información con el siguiente aspecto.


Tercero: veamos el proceso en Excel 2003

Vamos a lanzar el Asistente de Tablas Dinámicas. En Excel 2003 es sencillo, simplemente se ha de ir al menú Datos y allí veremos la opción que lanza el Asistente para Tablas y Gráficos Dinámicos.

El primer paso del asistente nos pregunta: ¿Dónde están los datos que desea analizar?. Elegimos 'Rangos de consolidación múltiple'.



En el segundo paso indicamos que deseamos un solo campo de página.



En la siguiente ventana nos preguntan ¿Dónde están los rangos de hoja de cálculo que desea consolidar?.




Vamos marcando cada rango y pulsando el botón Agregar, hasta tener incluidos los cuatro rangos correspondientes a las cuatro tablas.



En el tercer paso indicamos que deseamos el informe de tabla dinámica en una hoja de cálculo nueva.



Ya tenemos generada la tabla dinámica, aunque será necesario mejorar un poco. Por ejemplo la columna F de Total general aquí no tienen ningún sentido y tendremos que eliminarla. Pero antes de comenzar a mejorar la tabla veremos cómo se realiza este proceso en Excel 2007 y 2010.



Cuarto: veamos el proceso en Excel 2007 y Excel 2010

En Excel 2007 y en Excel 2010 en la Cinta de Opciones se encuentra la pestaña Insertar y  a su izquierda podemos ver la opción Tabla dinámica




Al pulsar sobre 'Tabla dinámica' obtendremos la siguiente ventana



En esta ventana no encontramos la opción de 'Rangos de Consolidación Múltiple'. Por tanto, abandonaremos esta vía.

Para obtener la opción de 'Rangos de Consolidación Múltiple' debemos llegar hasta el Asistente para Tablas y Gráficos dinámicos. Esto se consigue incluyendo en la Barra de Herramientas de Acceso Rápido el icono correspondiente a este asistente.

Inicialmente la Barra de Herramientas de Acceso Rápido contiene muy pocos icono, pero podemos personalizarla. Su imagen en Excel 2010 es la siguiente.




Pulsamos sobre el desplegable que pone 'Personalizar barra de herramientas de acceso rápido'.




Seleccionamos la opción 'Más comandos ...'



Así llegamos a obtener la siguiente ventana.



En 'Comandos disponibles en:' elegimos del desplegable 'Todos los comandos'. Encontraremos muchísimos iconos ordenados alfabéticamente. De entre todos ellos elegiremos 'Asistente para tablas y gráficos dinámicos' y con el botón Agregar le pasaremos al recuadro de la derecha, y aceptaremos.




De esta forma habremos conseguido en la barra de herramientas de acceso rápido el icono correspondiente al Asistente para tablas y gráficos dinámicos.





Quinto: usemos el Asistente

Ya podemos utilizar el icono con el Asistente para tablas y gráficos dinámicos. Al pulsarlo se obtiene esta ventana en Excel 2010.


Donde podemos ver la tan ansiada opción de 'Rangos de consolidación múltiple' que nos permitirá trabajar con tablas de datos que se encuentren en varias hojas, o incluso en varios libros.


En el segundo paso elegimos un solo campo de página.


Agregamos los cuatro rangos correspondientes a las cuatro tablas.


En el paso 3 pedimos crear la tabla dinámica en hoja de cálculo nueva.



En Excel 2010 obtenemos al siguiente tabla dinámica.


Eliminamos la columna de Total general, y añadimos una columna que nos de el porcentaje de variación de la población entre 1995 y 2030. Así obtenemos la siguiente tabla.


Los colores de las celdas se han añadido de forma manual y nos permiten efectuar un análisis de la evolución de la población para los próximos años. Es sorprendente ver la caída de la población en Rusia, y que India iguala o incluso supera a China.

Nota

También se puede obtener el Asisten para tablas y gráficos dinámicos en versiones posteriores a Excel 2003 pulsando la combinación de teclas ALT+T+B.

miércoles, 4 de agosto de 2010

Concatenar para comparar

Descargar el fichero: concatenaycompara.xlsx

Comparar un valor con otro es algo sencillo (Hoja1). Comparar un valor con otros, es algo más complicado (Hoja2). Y comparar un conjunto de valores con otros conjuntos requiere algún truco o idea más elaborada (Hoja3). En este caso lo que realizaremos es la comparación de un conjunto de 5 valores de una fila, con una matriz compuesta por varias filas. Lo que queremos es que Excel nos diga si nuestra fila está en la matriz, y en caso afirmativo que nos indique en qué fila se encuentra.

Comparar dos valores (Hoja1)

Si queremos comparar el valor que está en la celda A1 con el valor que está en la celda B1, podemos escribir en la celda C1 la siguiente expresión:

=A1=B1

Excel responderá con la palabra VERDADERO si la igualdad es cierta, y con la palabra FALSO en caso contrario.

En informática los verdaderos se representan por el número 1, y los falsos por el número 0. Esto se ve muy bien al convertir los verdaderos y falsos en unos y ceros. Para realizar esta conversión a números disponemos de dos trucos:

Método 1

Multiplicamos la igualdad por 1. Así forzamos a que el valor se convierta en numérico. En la celda D1 utilizamos la expresión:

=+(A1=B1)*1

Método 2

A la expresión que compara ambas celdas (=A1= B1) la precedemos de dos signos menos. Así la celda E1 es:

=--(A1=B1)

El primer singo menos equivale a multiplicar por -1. Con esto ya hemos convertido la expresión en numérica. Pero para que no queden números negativos volvemos a multiplicar por -1, de ahí el segundo signo menos.



En la imagen observamos un caso en el que comparamos los elementos de la columna A con los elementos de la columna B. Con los métodos comentados anteriormente obtenemos en las columnas D o E una serie de ceros y unos.

En ciertas ocasiones necesitaremos conocer cuantos elementos de la columna A no coinciden con su pareja correspondiente de la columna B. Este dato viene dado por el número de ceros de la columna E, que en la imagen son 2. Como contar ceros es algo más complicado que contar unos lo que hacemos en la columna F es introducir el operador lógico NO que convierte lo VERDADERO en FALSO, y lo FALSO en VERDADERO. Así la celda F1 será:

=--NO(A1=B1)

Al copiar hacia abajo nos proporciona la columna F. Pulsando con el ratón sobre el indicador de columna F, queda seleccionada toda la columna F, y Excel nos proporciona, abajo a la derecha, la suma de todos los valores de esta columna. Como podemos comprobar en la imagen la suma es 2, lo que indica que son dos los elementos de la columna A que no coinciden con los elementos de la columna B.

En el fichero (Hoja1) los valores pueden cambiar ya que se obtienen con números aleatorios. De hecho al pulsar la tecla F9 de recálculo manual los valores cambiarán.


Comparar con los anteriores (Hoja2)

Supongamos que en la columna A estamos introduciendo datos y no deseamos que al introducir un nuevo dato ya esté introducido con anterioridad en la lista. Vamos a introducir la referencia de una serie de canciones dando el grupo y el título de la canción. Al lado introducimos una fórmula que no indicará con un 0 si la canción no esta repetida y con un 1 si ya está incluida con anterioridad y por tanto está repetida.


La fórmula de la celda B44 es:

=CONTAR.SI($A$1:A43;A44)

Observar dónde van los dólares. Van en A1 para que quede fija al copiar hacia abajo la fórmula. Esto se hace para que se pueda comparar con todos los anteriores.

En el ejemplo la canción introducida en la fila 44 está repetida, lo que se indica con un 1 en la celda B44.


Comparar varias celdas con las filas de una matriz (Hoja3)

Si comparamos varias celdas podemos obtener lo siguiente:


En la tabla de la izquierda tenemos un matriz con 25 filas (de A1 a E25). Cada fila esta compuesta de 5 valores numéricos. En la tabla de la derecha (H1:L17) disponemos de varias filas compuestas también por 5 valores numéricos. Deseamos saber si para cada una de las 17 filas de la derecha esa combinación de números se encuentra alguna fíla de la matriz de la izquierda.

Para ello vamos a crear la columna F (color purpura) y la columna M (color gris). Las columnas F y M se obtienen concatenando los 5 valores de cada fila. En este caso los hemos separado por comas, pero en la mayoría de los casos no sería necesario. Para concatenar utilizamos el signo & que aplicado a celdas, o a texto entrecomillado nos permite concatenar caracteres alfanuméricos. Así la fórmula de la celda F1 es:

=+A1&","&B1&","&C1&","&D1&","&E1

Observar que por tratarse de una fórmula de Excel comienza por el signo "=". Si no ubiéramos separado con comas la fórmula sería:

=+A1&B1&C1&D1&E1

Para comparar si una fila de la segunda tabla está en la matriz de la izquierda empleamos la función:

=CONTAR.SI(rango;criterios)

Ejemplo

=CONTAR.SI(B2:B25,"Nancy")

En el ejemplo se cuenta el número de veces que aparece el texto "Nancy" en el rango B2:B25.

En nuestro caso la fórmula de la celda N15 es:

=CONTAR.SI(datos;M15)

siendo datos el rango F1:F25 (de color purpura).

Lo que hace la función es contar cuantas veces aparece el contenido de la celda M15 dentro del rango datos.

En la columna O deseamos que en caso de coincidencia nos diga en que fila de la matriz se encuentran los números que se repiten. Para ello introducimos el condicional SI. La fórmula de la celda O15 es:

=SI(CONTAR.SI(datos;M15);COINCIDIR(M15;datos;0);0)
Cuando la función CONTAR.SI proporciona el valor cero la condición se entiende que no se cumple (equivale a un FALSO), y cuando la función CONTAR.SI proporciona un valor 1 o superior se entiende que la condición si se cumple (equivale a un VERDADERO).

Cuando la condición se cumple introducimos la función COINCIDIR que nos dice en que lugar del rango datos se encuentra el valor de la celda M15.

Si en la columna N encontramos un valor superior a 1, indica que en la matriz de la izquierda existe una o varias filas repetidas. En caso de existir una fila repetida dentro de la matriz, la columna O nos daría la primera fila que se repite.