Fórmulas y Funciones Matriciales en Excel.

Funciones más útiles para crear fórmulas matriciales en Excel. Parte 3.

 

Fórmulas matriciales: Curso de Matrices en Excel para aprender a trabajar con rangos en vez de con celdas con la finalidad de trabajar con datos organizados en rangos más rápidamente.

Temario: trabajar con rangos matriciales: operaciones básicas y función SI(). Fórmulas de resumen de rangos matriciales: SUMA(), CONTAR(), PROMEDIO(). Uso de SUMA(SI()) para contar. CONTAR.SI() matricial. Funciones matriciales específicas: FRECUENCIA(), TENDENCIA() y SUMAPRODUCTO(). Funciones SI CONJUNTO. Lógica Matricial.

Descarga los ejemplos en: https://www.dropbox.com/s/p3ots7xugziesd6/Excel-Matrices.zip?dl=0

 

Puedes ir al índice de contenidos para ver todos los Minicursos o a la primera entrega de este minicurso de Matrices en Excel.

 

Funciones más útiles en el mundillo de las fórmulas matriciales de Excel

 

Funciones matriciales de Tipo 3 que deben ser introducidas con CONTROL+MAYUSCULAS+ENTER

Hemos visto que las funciones matriciales de Tipo 3 se introducen con un ENTER y ya actúan como una función matricial, lo que es mucho más cómodo de usar. Estas funciones son unas cuantas, pero nosotros vimos SUMAPRODUCTO(), SUMAR.SI(), CONTAR.SI(), PROMEDIO.SI() y las funciones SI CONJUNTO. Si bien no todas son estrictamente matriciales, nosotros las consideramos matriciales al menos dentro de este Minicurso.

En su momento ya dijimos que estas funciones tienen una limitación importante. Vamos a explicarla ahora.

La limitación consiste en que, en ocasiones, según cómo se usen estas funciones, darán un resultado incorrecto si se introducen con ENTER. Solamente darán un resultado correcto si se introducen con CONTROL+MAYUSCULAS+ENTER.

Vamos a ver solamente dos de estas funciones: CONTAR.SI() que es la más usada con CONTROL+MAYUSCULAS+ENTER y SUMAPRODUCTO() que solamente hay que introducirla con CONTROL+MAYUSCULAS+ENTER en pocas ocasiones.

 

Función CONTAR.SI() introducida con CONTROL+MAYUSCULAS+ENTER

Ya hemos visto la función CONTAR.SI() como función matricial de Tipo 3, y hemos dicho que es una función que trabaja ordenadamente con rangos de por sí, o sea, que podemos introducirla con un ENTER normal. Pero ahora vamos a ver su uso como función matricial que debe introducirse con CONTROL+MAYUSCULAS+ENTER y que tiene una utilidad diferente a la que hemos visto, pudiéndose usar como una función especial de conteo.

Para verlo claramente: hasta ahora hemos usado la función CONTAR.SI() como una función matricial de Tipo 3, así:

=CONTAR.SI(rango;valor) y ENTER

Esta función nos retorna cuántos valores del rango del primer parámetro son iguales al valor del segundo parámetro.

Pero esta misma función, usada como una función matricial de Tipo 1, es así:

=CONTAR.SI(rango1;rango2) y CONTROL+MAYUSCULAS+ENTER

Observa que ahora el segundo parámetro es un rango, no es un valor. Tanto el primer parámetro como el segundo son rangos. ¿Cómo se usa? Vamos a verlo.

 

Uso de CONTAR.SI() con ENTER para obtener información

Vamos a empezar, primero, viendo rápidamente que CONTAR.SI(), como función de Tipo 3 introducida con ENTER, puede usarse para saber si un valor está en un rango. Es decir, no nos interesa contar cuántas veces aparece un valor en un rango, sino solamente saber si ese valor está en el rango.

Si ya has descargado los ejemplos, abre el archivo "ContarSiMatricial.xls" y haz clic en la hoja "Compras Meses". Tiene que aparecer la hoja como sigue:

 

 

Es una lista de compras por departamentos de una supuesta empresa. Supón que te piden que en la celda G3 aparezca un "SI" en el caso de que el departamento que hay en la celda F3 esté en la lista de compras (en la columna B) y que aparezca un "NO" en otro caso. Haz lo siguiente:

• Haz clic en G3
• Teclea la fórmula

=SI(CONTAR.SI(B3:B102;F3)>0;"SI";"NO")

• Y acaba con un ENTER

El resultado:

 

 

Lo que significa que el departamento de Calidad está en la lista de compras, es decir, como mínimo ha hecho 1 compra (no te interesa el conteo exacto).

Ahora haz clic en F3 e introduce el texto "Administración". El resultado:

 

 

Lo que significa que el departamento de Administración no está en la lista de compras, es decir, no ha hecho compra alguna (obviamente el conteo es 0 pero seguimos sin estar interesados en el conteo en sí).

 

Una vez visto esto, vamos a ver algo similar. Se trata de saber si un departamento está repetido en la lista de compras, no interesa el conteo exacto, solamente te piden saber si está repetido. Sigue estos pasos:

• Haz clic en G2
• Introduce el texto "¿Repetido?"
• Haz clic en F3
• Introduce el texto "I+D"
• Haz clic en G3
• Cambia la fórmula por

=SI(CONTAR.SI(B3:B102;F3)>1;"SI";"NO")

• Y acaba con un ENTER

El resultado:

 

 

Lo que significa que el departamento de I+D está repetido en la lista de compras, es decir, como mínimo ha hecho 2 compras (no te interesa el conteo exacto).

Ahora haz clic en F3 e introduce el texto "Jardines". El resultado:

 

 

Lo que significa que el departamento de Jardines no está repetido en la lista de compras o simplemente no está en la lista  (obviamente el conteo es 0 ó 1 pero seguimos sin estar interesados en el conteo en sí).

Fíjate que, en los dos ejemplos anteriores, no te han pedido hacer un conteo exacto de las veces que aparece un departamento en la lista de compras, solamente te han pedido saber si un departamento estaba o no en la lista y si estaba o no repetido.

Hasta aquí, hemos visto una utilidad más de la fórmula CONTAR.SI() introducida con ENTER.

 

Pasemos a ver su utilidad introducida con CONTROL+MAYUSCULAS+ENTER, en concreto como fórmula matricial de Tipo 1.

 

Uso de CONTAR.SI() con CONTROL+MAYUSCULAS+ENTER para obtener contar ocurrencias de valores

Continuamos en el archivo "ContarSiMatricial.xls" en la hoja "Compras Meses".

Una vez visto que podemos ver si un valor está dentro de un rango usando CONTAR.SI() con ENTER, vamos a ver cómo hacer algo similar usando la función como matricial de Tipo 1.

Usada matricialmente como fórmula de Tipo 1 y con CONTROL+MAYUSCULAS+ENTER, la función CONTAR.SI() puede contar cuántas veces aparece una serie de valores dentro de un rango. Por ejemplo, en la hoja "Compras Meses", el rango C3:C102 indica el número de compras de un departamento. Supón que te piden una fórmula que muestre cuántas veces ha habido 1 compra, 2 compras, 3 compras,... hasta 10 compras. Sigue estos pasos:

• Haz clic en F2
• Introduce el texto "Compras"
• Haz clic en G2
• Introduce el texto "Conteo"
• Haz clic en G3
• Pulsa la tecla SUPR para borrar su contenido
• Rellena desde F3 hasta F12 con los números 1 a 10:

 

 

Vamos a poner en la columna G la fórmula matricial de Tipo 1 necesaria para calcular cuántos 1’s aparecen en la columna C, cuántos 2’s aparecen en la columna C, cuántos 3’s aparecen en la columna C, etc. hasta contar cuántos 10’s aparecen en la columna C. Continua con estos pasos:

• Selecciona el rango G3:G12
• Teclea la siguiente fórmula matricial como si fuese de Tipo 1:

=CONTAR.SI(C3:C102;F3:F12)

• Y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado:

 

 

Es decir, esto me está diciendo que en la columna C el número 1 aparece 2 veces, el 2 aparece 3 veces, el 3 aparece 0 veces, ... hasta el 10, que aparece 2 veces en la columna C. Cada fórmula de la columna G nos indica cuántas veces aparece el número que hay a su izquierda, en la columna F. Este es el resultado de sustituir en la función CONTAR.SI() el segundo parámetro por un rango, en concreto el rango D3:D12 que contiene 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Observa que simplemente hemos contado ocurrencias de valores únicos, no hemos hecho ninguna segmentación por intervalos como hicimos en el tema anterior.

 

Contar repeticiones dentro de un mismo rango con CONTAR.SI() como fórmula matricial de Tipo 1

Una de las utilidades de la función CONTAR.SI() acabada con CONTROL+MAYUSCULAS+ENTER es la  siguiente:

=CONTAR.SI(rangoX;rangoX)

... es decir, observa que los dos rangos son el mismo rango. En tal caso, CONTAR.SI(), como función matricial de Tipo 1, calcula cuántas veces aparece cada valor del rangoX. El resultado es que la fórmula cuenta las repeticiones de cada valor del rangoX. Esta función se usa como si fuese de Tipo 1 y se coloca a la derecha de donde está el rango en el que queremos contar sus valores repetidos.

Por ejemplo, continuando en el archivo "ContarSiMatricial.xls" en la hoja "Repetidos", hay una lista de códigos en la columna B. Imaginemos que son códigos postales:

 

 

Supón que te piden que calcules, en la celda F2, cuántos códigos hay repetidos, pero que no haya ningún rango con cálculos intermedios.

Si has pensado que esto podría hacerse con una fórmula matricial de Tipo 2, es que vas bien encaminado.

Pero lo vamos a hacer por pasos. Primero, calcularemos cuántas veces está repetido cada código postal usando CONTAR.SI() como función matricial de Tipo 1 acabada con CONTROL+MAYUSCULAS+ENTER. Sigue estos pasos:

• Selecciona desde C2 hasta C120
• Sin hacer clic en ningún sitio para que no se deshaga la selección, teclea la siguiente fórmula:

=CONTAR.SI(B2:B120;B2:B120)

• y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

 

 

 

Nota: a partir de la versión Excel 2007, puedes usar como rangos columnas enteras =CONTAR.SI(B:B;B:B) pero seguramente notarás que Excel irá bastante más lento para hacer el cálculo, ya que usa una columna entera, que es muy grande en comparación con un rango pequeño. No obstante, solamente se puede usar columnas enteras como rangos cuando dichas columnas no tienen celdas vacías en sus primeras filas, es decir, solamente se puede usar columnas enteras como rango cuando la columna contiene datos válidos desde la primera fila. Por tanto, aconsejamos nunca usar CONTAR.SI() ni SUMAR.SI() con columnas enteras como rangos. Además, al usar columnas enteras, el archivo no será compatible con Excel 2003.

 

Lo que hemos hecho ha sido obtener justo a la derecha de cada código, cuántas veces aparece repetido dicho código.

Fíjate que la mayoría están sin repetir porque aparece un 1 a su derecha. Los códigos que tienen un valor mayor que 1 a su derecha son los que están repetidos. Por ejemplo, el código 615266 (en B4) tiene un 2 a su derecha, es decir, 615266 está 2 veces, esto indica que está repetido.

Sin embargo, recuerda que lo que te han pedido es contar los repetidos. Una solución sería indicar en la columna C con una "X" los códigos repetidos y luego contar cuántas "X" hay. Vamos a probar. Sigue estos pasos:

• Haz clic en C2
• Teclea o modifica la fórmula así:

=SI(CONTAR.SI(B2:B120;B2:B120)>1;"X";"")

• y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

 

 

Al igual que comentábamos en el apartado anterior, cada código que tiene una X a su derecha significa que está repetido en la lista de códigos, no te interesa el conteo exacto. Obviamente, los códigos que no tienen una "X" a su derecha no están repetidos, solamente aparecen 1 vez en la columna B.
 
Ahora, solamente falta contar cuántas "X" hay en la columna C:

• Haz clic en F2
• Teclea la fórmula =CONTAR.SI(C2:C120;"X")
• Y acaba con ENTER

El resultado es:

 

 

Sin embargo, una manera más fácil, es usar la función SUMA() estándar como función de conteo, usando el truco que hemos usado hasta ahora que es poner 1's y 0's en vez de "X" y luego sumar los 1's. Haz lo siguiente:

• Haz clic en C2
• Teclea o cambia la fórmula así:

=SI(CONTAR.SI(B2:B120;B2:B120)>1;1;"")

• Introduce la fórmula con CONTROL+MAYUSCULAS+ENTER

La columna C muestra ahora un 1 a la derecha de cada código repetido y un nulo "" si no está repetido:

 

 

Dado que los nulos no afectan al resultado de la mayoría de las fórmulas, ahora bastará con sumar todos los 1's de la columna C para tener el conteo de códigos repetidos:

• Haz clic en F2
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula =SUMA(C2:C120)
• Acaba con ENTER.

El resultado será igualmente:

16

Sin embargo, te han pedido contar en la celda F2 los códigos repetidos, pero sin usar un rango visible con cálculos intermedios. El proceso será el de siempre: usar la fórmula matricial multicelda de Tipo 1 que has tecleado en la columna C y ponerla dentro de una función de resumen unicelda SUMA() acabada con CONTROL+MAYUSCULAS+ENTER. Sigue estos pasos:

• Haz clic en F2
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula:

=SUMA(SI(CONTAR.SI(B2:B120;B2:B120)>1;1;""))

• y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser el mismo:

16

Para comprobar que la columna C ya no es necesaria, selecciona toda la columna C y pulsa la tecla SUPR para borrar su contenido. El resultado debería seguir sin cambiar:

 

 

Como dijimos en las matrices de Tipo 2, es lo mismo:

• poner en C2:C120 la fórmula matricial =SI(CONTAR.SI(B2:B120;B2:B120)>1;1;"") y sumar este rango con =SUMA(C2:C120), es lo mismo que...
• usar directamente la función en una sola celda =SUMA(SI(CONTAR.SI(B2:B120;B2:B120)>1;1;"") y acabarla con CONTROL+MAYUSCULAS+ENTER.

 

Observaciones finales:

• En realidad este resultado de 16 no indica exactamente cuántos códigos hay repetidos sino el número de repeticiones que Excel ha detectado, es decir, podría ser un código repetido 16 veces pero también 8 códigos repetidos 2 veces.

• En Excel 2007+ se pueden usar las fórmulas:

• Nunca debe usarse la función =CONTAR.SI(rango;valor) en ninguna fórmula matricial acabada con CONTROL+MAYUSCULAS+ENTER, ya que dará un resultado no válido. En cualquier fórmula que acabe con CONTROL+MAYUSCULAS+ENTER sólo se puede usar =CONTAR.SI(rango;rango). La manera correcta de usar una fórmula matricial de Tipo 2 para hacer un conteo de las veces que aparece un solo valor dentro de un rango es =SUMA(SI(rango=valor;1)) introducida con CONTROL+MAYUSCULAS+ENTER.

 

Uso de SUMAPRODUCTO() introducida con CONTROL+MAYUSCULAS+ENTER

Hemos dicho varias veces que la función SUMAPRODUCTO() puede usarse para sustituir a la función SUMA() con la ventaja de que puede introducirse acabándola con ENTER, lo que es más cómodo.

 

Pero ello no es posible si ponemos como parámetro de SUMAPRODUCTO() una función SI() o, en general, cualquier función que no sea intrínsecamente matricial.

 

Esto significa que, siguiendo en la hoja "Repetidos", en la celda F2, no podemos sustituir SUMA() por SUMAPRODUCTO() e introducirla con ENTER para ahorrarnos trabajo. Es una fórmula incorrecta. Para comprobarlo haz lo siguiente:

• Haz clic en F2
• Teclea la fórmula incorrecta:

  =SUMAPRODUCTO(SI(CONTAR.SI(B2:B120;B2:B120)>1;1;"")) y ENTER

El resultado es un error #¡VALOR!.

 

 

¿Por qué da un resultado erróneo?

 

El resultado es erróneo porque debemos forzar que la función SI(), que está puesta dentro de SUMAPRODUCTO(), sea tratada como matricial por Excel introduciendo la fórmula con CONTROL+MAYUSCULAS+ENTER.

 

Para comprobarlo, haz lo siguiente:

• Haz doble clic en la celda F2, esto hará que entres en modo edición de fórmula
• Pulsa la combinación de teclas CONTROL+MAYUSCULAS+ENTER

 

El resultado ha de ser nuevamente el correcto:

 

 

Observa en la barra de fórmulas que la fórmula es la misma pero ahora está rodeada de { y } indicando una fórmula matricial.

 

¿Vale la pena en estos casos sustituir el SUMA() por un SUMAPRODUCTO()? La respuesta es que no, porque teclear SUMAPRODUCTO() es más largo que teclear SUMA() y como no nos ahorramos de pulsar CONTROL+MAYUSCULAS+ENTER, no ganamos nada. Lo mejor es usar la fórmula original:

 

=SUMA(SI(CONTAR.SI(B2:B120;B2:B120)>1;1;"")) y CONTROL+MAYUSCULAS+ENTER

 

En general, cuando SUMAPRODUCTO() tenga dentro una función SI() como parámetro, habrá que acabar con CONTROL+MAYUSCULAS+ENTER para forzar que el SI() que hay dentro de SUMAPRODUCTO() actúe como matricial.

 

De todos modos, es importante que continúes teniendo en cuenta que la función SUMAPRODUCTO() sigue siendo una función matricial muy importante. En el próximo tema de la lógica matricial, verás que podemos usar SUMAPRODUCTO() y acabar con ENTER sin que Excel muestre error. Para ello, usaremos un truco para evitar tener que usar la función SI() como parámetro dentro de SUMAPRODUCTO(). Este truco se explicará.

 

Ahora hazlo tú:

Seguimos en el archivo "ContarSiMatricial.xls". Haz clic en la hoja "Vuelos":

 

 

Esta hoja contiene en la columna B una lista de 3 letras que son los códigos internacionales de los aeropuertos.

Te piden que uses la columna C para que aparezca cuántas veces se repite cada código de aeropuerto. El resultado debería ser:

 

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Contar valores únicos con CONTAR.SI()

Como hemos visto, la función CONTAR.SI() sirve para contar valores repetidos en un rango. Pero alguna vez te pueden pedir todo lo contrario, es decir, que cuentes cuántos valores hay en un rango sin tener en cuenta si están repetidos o no. Es lo que se denomina "contar valores únicos". Microsoft tiene pensado añadir a Excel una función, que probablemente se llame [¿] CONTAR.UNICOS() [?], que permitirá hacer este cálculo de manera sencilla. Pero, en el momento de crear este Minicurso, la única manera de hacerlo es usar una fórmula matricial.

Por ejemplo, en la lista de aeropuertos de la hoja "Vuelos", supón que te piden contar cuántos códigos de aeropuerto hay, pero contando como 1 cada código, incluso aunque esté repetido varias veces. Te adelantamos que hay 18 códigos de aeropuerto en total, independientemente de si están repetidos o no. Partimos de la base de que la columna C está ya rellenada con el número de veces que se repite cada código. Para ver cómo se calcula, usaremos provisionalmente la columna D. Teclea la siguiente fórmula matricial de Tipo 1:

• Selecciona el rango D4:D27
• Teclea la fórmula =1/C4:C27
• y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

 

 

Ahora supón que quieres obtener en la celda B29 cuántos códigos de aeropuerto sin contar las repeticiones. Usa la fórmula estándar SUMA():

• Haz clic en B29
• Teclea la fórmula =SUMA(D4:D27)
• Acaba con ENTER

El resultado es el correcto:

 

 

El funcionamiento de esta fórmula es como sigue:

• Si un aeropuerto sólo aparece una vez se sumará 1/1 que es 1.
• Si aparece 2 veces se sumará 1/2 la primera vez que aparezca y 1/2 la segunda vez que aparezca y 1/2+1/2 es 1.
• Si aparece 3 veces se sumará 1/3+1/3+1/3 que es 1.
• Si aparece 4 veces se sumará 1/4+1/4+1/4+1/4 que es 1.
• Etc…

En Internet encontrarás fórmulas ingeniosas como esta.

Sin embargo, nos interesa, como siempre, que la fórmula no dependa de rangos con datos provisionales. Por tanto, para hacer que esta fórmula sea independiente de los valores que hay en las columnas C y D, haz lo siguiente:

• Haz clic en B29
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula: =SUMA(1/CONTAR.SI(B4:B27;B4:B27))
• y acaba con CONTROL+MAYUSCULAS+ENTER. El resultado sigue siendo el correcto.

Selecciona el rango D4:D27 y pulsa la tecla SUPR para borrar su contenido. Observa que el resultado en B29 sigue siendo el correcto. La fórmula en B29 ya no depende de ningún dato de la columna C o D.

Nota: Si hay celdas en blanco la función dará error porque los blancos se consideran 0 y daría error de división por cero. La solución es usar en la celda B30 la fórmula (en Excel 2007+):

 

=SUMA(SI.ERROR(1/CONTAR.SI(B4:B27;B4:B27);"")) y CONTROL+MAYUSCULAS+ENTER

 

Comprueba que funciona bien borrando el código de aeropuerto en la celda B26. La fórmula en B29 dará error y la fórmula en B30 seguirá bien:

 

 

Esta fórmula la podemos usar también para contar los códigos postales únicos en el ejemplo anterior de los códigos postales. Por ejemplo, en la hoja "Repetidos" teclea la palabra "Únicos" en la celda F4 y en la celda F5 teclea la fórmula:

 

=SUMA(1/CONTAR.SI(B2:B120;B2:B120)) y CONTROL+MAYUSCULAS+ENTER

 

El resultado es 111 códigos postales únicos.

 

Indicar la primera repetición de un valor en una columna

Continuamos de nuevo en la hoja "Vuelos". Como ya hemos hecho varias veces, en vez de indicar cuántas veces se repite cada valor de un rango, puedo simplemente indicar con una "X" si un valor se repite o no, es decir, si no nos interesa saber cuántas veces se repite un valor sino que simplemente queremos saber si se repite o no. Por ejemplo, podemos usar la columna D para indicar con una "X" los aeropuertos que se repiten y dejar la celda vacía para los que no se repiten. Utilizaremos fórmulas matriciales de Tipo 4. Haz lo siguiente:

• Haz clic en B26
• Teclea el texto "TFS" que habíamos borrado antes
• Haz clic en D4
• Teclea la primera fórmula con las referencias absolutas correctamente indicadas:

=SI(CONTAR.SI($B$4:$B$27;B4)>1;"X";"")

• Introduce la fórmula con ENTER
• Vuelve a hacer clic en D4
• Usa el cuadro de relleno (parte inferior derecha de la celda) para rellenar con la fórmula desde D4 hasta D27.

El resultado debería ser:

 

 

Como vemos, solamente aparece una "X" en los códigos repetidos (no nos interesa el número exacto de repeticiones).

Sin embargo, esto no indica claramente en qué momento aparece la primera repetición de cada aeropuerto. Supón que te piden que, en la columna E, aparezca el texto "Repet.", no en todos los aeropuertos repetidos, sino solamente cuando aparezca la primera repetición de un aeropuerto. Una fórmula así tendría la utilidad de saber en qué celda de un rango se ha tecleado un valor repetido. Para conseguirlo, hay que usar una fórmula que no hemos visto hasta ahora, que sería de Tipo 4 pero con referencias mixtas. Una referencia mixta es un rango que no tiene los 4 símbolos $, sino que solamente tiene 2 símbolos $ en la celda inicial del rango. Veamos cómo se usa:

• Haz clic en la celda E4
• Teclea la siguiente fórmula, pero, atención, los $ no están equivocados, tecléalos tal cual:

=SI(CONTAR.SI($B$4:B4;B4)>1;"Repet.";"")

• Introduce la fórmula con ENTER
• Vuelve a hacer clic en E4
• Usa el cuadro de relleno (parte inferior derecha de la celda) para rellenar con la fórmula desde E4 hasta E27.

El resultado debería ser:

 

 

Aparece el texto "Repet." a partir de la celda en que Excel encuentra la primera repetición de un aeropuerto.

Esta fórmula usa un rango mixto o semi-relativo, que es un rango en la forma:

$Columna$Fila:ColumnaFila

Por ejemplo:

$H$6:H18

De manera que, al copiar con el cuadro de relleno una fórmula que tenga ese rango, la parte que tiene los $ se mantiene fija, H6, pero la parte que no tiene los $ va variando: H18, H19, H20, H21... Así, en nuestro caso, usamos este rango mixto o semi-relativo en la fórmula:

 

 

y al arrastrar por el cuadro de relleno hacia abajo, este rango irá cambiando B4:B4, B4:B5, B4:B6, B4:B7, B4:B8, ...

De esta manera, la función CONTAR.SI() que usa este rango semi-relativo, siempre hace el conteo desde donde está la fórmula hacia arriba, hacia la primera celda del rango. No cuenta las repeticiones en todo el rango, como hemos hecho hasta ahora. Con ello, el resultado que obtengo es que Excel me mostrará el texto "Repet." en la celda en que aparece la primera repetición de un aeropuerto y, si bien también aparece en la segunda, tercera y siguientes repeticiones, con este resultado la fórmula ya nos es útil.

 

Estas fórmulas que hemos visto pueden servirte para saber si un valor está repetido en una lista, tanto si te interesa saber cuántas veces aparece, como si te interesa precisamente lo contrario, controlar que los datos no estén repetidos.

 

 

_{Continua en la siguiente entrega. Puedes también ir al Indice de los Minicursos o Volver arriba.}