EXTRAER ELEMENTOS REPETIDOS

Extraer elementos repetidos

El problema se puede definir de esta manera: dada una lista de nombres, extraer, únicamente, aquéllos que estén repetidos.

La hoja donde vamos a hacer el ejercicio se llama Repetidos.

 

Primera solución: Poner fondo amarillo a los países repetidos

Accedemos a Fórmulas + Asignar nombre + Definir nombre y creamos el nombre Países con la siguiente definición:

Países

=DESREF(Repetidos!$B$2;1;0;CONTARA(Repetidos!$B:$B)-1;1)

Seleccionamos B3:B25 y vamos a Inicio + Formato condicional + Nueva regla. Elegimos Utilice una fórmula que determine las celdas para aplicar formato y ponemos la fórmula siguiente:

=Y(CONTAR.SI(Países;B3)<>1;B3<>"")

... pulsamos el botón Formato y, en la pestaña Relleno, elegimos el color amarillo.

Creamos una segunda regla con esta fórmula:

=NO(ESBLANCO(B3))

... pulsamos el botón Formato y, en la pestaña Bordes, elegimos el color gris y Contorno.

Segunda solución: Copiar los nombres repetidos en otra columna en orden invertido

Seleccionamos H3:H16 y escribimos:

=CONTAR.SI(Países;Países)<>1      [Terminar con Ctrl + Mayús + Intro]

 

Seleccionamos I3:I16 y escribimos:

=(H3:H16)*FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]

 

Seleccionamos J3:J16 y escribimos:

=FILA(Países)-2      [Terminar con Ctrl + Mayús + Intro]

 

Seleccionamos K3:K16 y escribimos:

=K.ESIMO.MAYOR(I3:I16;J3:J16)      [Terminar con Ctrl + Mayús + Intro]

 

Seleccionamos L3:L16 y escribimos:

=INDICE(Países;K3:K16)      [Terminar con Ctrl + Mayús + Intro]

 

Omito la justificación de estos pasos porque ya se han explicado en el artículo Extraer elementos no repetidos.

 

Seleccionamos D3:D25 y escribimos:

=INDICE(Países;K.ESIMO.MAYOR((CONTAR.SI(Países;Países)<>1)*FILA(Países)-2;FILA(Países)-2))    [Terminar con Ctrl + Mayús + Intro]

 

Los formatos condicionales para el rango D3:D25 son: 

=ESERROR(D3)

 

... y, en la pestaña Fuente, color blanco.

=NO(ESERROR(D3))

 

... y, en la pestaña Bordes, color gris y Contorno.

 

Esta solución tiene el inconveniente de que los nombres aparecen repetidos tantas veces como lo están en la lista original. Quizás sería mejor que sólo apareciesen una vez, y esto es lo que vamos a tratar de conseguir con la tercera solución.

 

Tercera solución: Copiar una sola vez los nombres repetidos en orden natural

 

Aislamos los nombres de los países repetidos.

 

En N3:

=SI(B3="";"";SI(CONTAR.SI(Países;B3)<>1;B3;""))    [Copiamos la fórmula hasta la fila 25]

 

Vamos contando las veces que cada nombre va apareciendo a medida que bajamos en la lista de la columnaN. También contaremos las apariciones de las celdas en blanco aunque después tendremos que desestimarlas.

 

En O3:

=CONTAR.SI($N$3:N3;N3)    [Copiamos la fórmula hasta la fila 25]

 

Nos quedamos con los nombres que han aparecido la primera vez.

 

En P3:

=SI(Y(N3<>"";O3<>1);"";N3)    [Copiamos la fórmula hasta la fila 25]

 

Si en la columna P hay un nombre, ponemos su posición en la lista original; en caso contrario, ponemos un número muy grande (10³⁰⁰).

 

En Q3:

=SI((P3)="";10^300;FILA()-2)     [Copiamos la fórmula hasta la fila 25]

 

Generamos una lista de números consecutivos del 1 al 23.

 

En R3:

=FILA()-2     [Copiamos la fórmula hasta la fila 25]

 

Ordenamos los números de la columna Q.

 

En S3:

=K.ESIMO.MENOR($Q$3:$Q$25;R3)      [Copiamos la fórmula hasta la fila 25]

 

Extraemos los nombres de los países.

 

En F3:

=INDICE(Países;S3)      [Copiamos la fórmula hasta la fila 25]

 

Ponemos en la columna F un formato condicional similar el de la columna D y el ejercicio quedará terminado.

 

Si utilizamos la segunda solución podremos eliminar las columnas H a L porque hemos creado una fórmula matricial compuesta en la columna D. En la tercera solución no he encontrado una fórmula que permita eliminar las columnas auxiliares.

EJERCICIO 1

EJERCICIO 2

BASES DE DATOS EN EXCEL

Funciones BD

Excel dispone de un conjunto de funciones que comienzan con las iniciales BD y que sirven para trabajar con bases de datos. Se usan de un modo similar a los criterios de selección en las Consultas de Access. A los lectores que conozcan Access les resultará familiar su manejo.

Éstas son las funciones BD:

	=BDCONTAR		Cuenta las celdas que contienen números en el campo (columna) de registros de la base de datos que cumplen las condiciones especificadas
	=BDCONTARA		Cuenta el número de celdas que no están en blanco en el campo (columna) de los registros de la base de datos que cumplen las condiciones especificadas
	=BDDESVEST		Calcula la desviación estándar basándose en una muestra de las entradas seleccionadas de una base de datos
	=BDDESVESTP		Calcula la desviación estándar basándose en la población total de las entradas seleccionadas de una base de datos
	=BDEXTRAER		Extrae de una base de datos un único registro que coincide con las condiciones especificadas
	=BDMAX		Devuelve el número máximo en el campo (columna) de registros de la base de datos que coinciden con las condiciones especificadas
	=BDMIN		Devuelve el número menor del campo (columna) de registros de la base de datos que coinciden con las condiciones especificadas
	=BDPRODUCTO		Multiplica los valores del campo (columna) de registros en la base de datos que coinciden con las condiciones especificadas
	=BDPROMEDIO		Obtiene el promedio de los valores de una columna, lista o base de datos que cumplen las condiciones especificadas
	=BDSUMA		Suma los números en el campo (columna) de los registros que coinciden con las condiciones especificadas
	=BDVAR		Calcula la varianza basándose en una muestra de las entradas seleccionadas de una base de datos
	=BDVARP		Calcula la varianza basándose en la población total de las entradas seleccionadas de una base de datos

Todas tienen la misma sintaxis:

Sintaxis: FUNCIÓN_BD(base_de_datos;nombre_de_campo;criterios)

• base_de_datos: Es la tabla o base de datos.
• nombre_de_campo: Es el nombre de la columna de la tabla sobre la que se va a realizar el cálculo.
• criterios: Es el rango de celdas que contiene las condiciones que se van a utilizar en el cálculo.

El rango de criterios puede colocarse en cualquier lugar pero, para permitir la adición de nuevos datos, se desaconseja situarlo debajo de la tabla.

Vamos a emplear la función BDCONTAR para ilustrar cómo se usan las funciones BD. Utilizaremos una base de datos ficticia que colocaremos en el rango B9:F41, dejando las filas 1 a 7 para situar los criterios. La fórmula la pondremos en H10.

Primer ejemplo

Contar las veces que la Empresa Ascensores J & C ha hecho Aportaciones comprendidas entre 150 y 300, teniendo, al mismo tiempo, algún valor en la columna Devolución.

Copiamos los encabezamientos de la base de datos en B2:F2. Como el criterio que vamos a emplear requiere hacer dos comprobaciones en el campo Aportación, necesitamos dos celdas con este título. Por tanto, escribimos Aportación en G2.

En la celda B3, escribimos:
="=Ascensores J & C"     [Excel mostrará: =Ascensores J & C]

En C3:
>150

En G3:
<300

Los tres criterios están en la misma fila. Esto significa que están vinculados mediante el operador Y. Dicho de otra forma: (El campo Empresa contiene Ascensores J & C) Y (Aportación es mayor que 150) Y(Aportación es menor que 300).

Como queremos contar el número de celdas no vacías de la columna Devolución que cumplen los tres criterios, la fórmula que pondremos en H10 será:

En H10:
=BDCONTAR(B9:F41;D9;B2:G3)    [Resultado: 2]

El primer argumento es el rango que ocupa la base de datos; el segundo, es el campo sobre el que vamos a aplicar la función (contar registros); el tercero, el el rango que ocupa los criterios. El segundo argumento, D9, podemos sustituirlo por el nombre del campo. Si lo hacemos así, la fórmula sería:=BDCONTAR(B9:F41;"Devolución";B2:G3)

Segundo ejemplo

Contar las veces que Ascensores J & C ha hecho Aportaciones mayores que 300 o menores que 200 y haya algún dato en la columna Devolución.

Los criterios deberán ser:

Cuando se usa el operador O los criterios van en filas distintas.

En este caso, la fórmula será:

En H10:
=BDCONTAR(B9:F41;D9;B2:C4)    [Resultado: 2]

Tercer ejemplo

Contar las veces que cualquier empresa distinta de Ascensores J & C haya hecho Aportaciones mayores que200 y haya algún dato en la columna Devolución.

En H10:
=BDCONTAR(B9:F41;D9;B2:C3)     [Resultado: 8]

Cuarto ejemplo

Contar las veces que cualquier empresa, excluidas Ascensores J & C y Decoraciones Eder, haya hechoAportaciones mayores que 200 y haya algún dato en la columna Devolución.

En H10:
=BDCONTAR(B9:F41;D9;B2:H3)     [Resultado: 8]

Quinto ejemplo

Contar celdas no vacías de Devolución que cumplan:

• Ascensores J & C tenga Aportación entre 150 y 300, O
• Decoraciones Eder tenga Rendimiento=6, O
• Decoraciones Eder tenga Beneficios >3500, O
• Pascual Reina tenga Aportaciones >84

En H10:
=BDCONTAR(B9:F41;"Devolución";B2:G6)     [Resultado: 6]

Sexto ejemplo

Contar celdas no vacías de Devolución que cumplan:

• La Empresa no debe ser Ascensores J & C
• La Empresa no debe ser Metalkarma, S.L.
• Beneficio menor que la media de beneficios de todas las empresas

Estamos ante un caso complejo ya que no conocemos el promedio de la columna Beneficio para poner el criterio. Podemos calcularlo en una celda vacía o poner la fórmula correspondiente en la zona de criterios. El primer método es poco recomendable ya que requiere cambiar la fórmula si se modifica algún valor de la columna Beneficio. Veamos cómo se haría.

En H14 (o cualquier otra celda vacía):
=PROMEDIO(F10:F41)     [Resultado: 2.565,67]

Conocido el promedio, ponemos los criterios:

La fórmula en H10 sería:
=BDCONTAR(B9:F41;"Devolución";B2:G3)     [Resultado: 14]

Es mejor utilizar el segundo método: poner una fórmula en la zona de criterios. Sin embargo, antes hay que conocer una serie de condiciones de obligado cumplimiento (extraídas de la ayuda de Excel):

• La fórmula se debe evaluar como VERDADERO o FALSO.
• Puesto que está utilizando una fórmula, escriba la fórmula como lo haría normalmente, pero no la escriba de la forma siguiente: =''=entrada''
• No utilice rótulos de columnas para los rótulos de los criterios; deje los rótulos de criterios en blanco o utilice uno que no sea un rótulo de columna incluido en el rango.
• Si en la fórmula utiliza un rótulo de columna en lugar de una referencia relativa a celda o un nombre de rango, Excel presenta un valor de error, como por ejemplo #¿NOMBRE? o #¡VALOR!, en la celda que contiene el criterio. Puede pasar por alto este error, ya que no afecta a la manera en que se filtra el rango.
• La fórmula que utilice con el fin de generar los criterios debe utilizar una referencia relativa para hacer referencia a la celda correspondiente de la primera fila.
• Todas las demás referencias usadas en la fórmula deben ser referencias absolutas.

 A la nueva columna de la zona de criterios le llamaremos Auxiliar y la fórmula será:

En H3:
=F10<PROMEDIO($F$10:$F$41)     [Resultado: FALSO]

F10 es la primera celda de la columna con la que vamos a hacer el cálculo (en nuestro caso la media aritmética). Debe ser una referencia relativa (no lleva signo $). Todas las demás referencias deben ser absolutas (llevan signo $).

En H10:
=BDCONTAR(B9:F41;"Devolución";B2:H3)     [Resultado: 14]

Séptimo ejemplo

Contar celdas no vacías de la columna Devolución de las empresas que sean sociedades anónimas (S.A.) o sociedades limitadas (S.L.)

En este caso tendremos que usar caracteres comodín: asterisco (*) e interrogación (?). El asterisco sustituye a un número indeterminado de caracteres; la interrogación, solamente a uno.

Si entre los elementos buscados hay una interrogación o un asterisco, para incluirlo en la búsqueda debe ir precedido de la tilde (~).

En H10:
=BDCONTAR(B9:F41;"Devolución";B2:B3)     [Resultado: 6]

EJERCICIO 1

EJERCICIO 2

ASOCIAR PALABRAS 3

Veamos las dos últimas soluciones al problema de asociar nombres de ciudades con las frases que contienen palabras claves.

Quinta solución

Lo haremos con tres columnas auxiliares.

La primera columna auxiliar será parecida a la de los casos anteriores, pero introduciendo la novedad de incorporar el comodín asterisco (*).

Seleccionamos H3:H10 y escribimos:

=HALLAR("*"&$E$3:$E$10&"*";$B3)     [Terminar con Ctrl + Mayús + Intro]

 

("*"&$E$3:$E$10&"*") implica buscar cualquier palabra clave del rango E3:E10 precedida o seguida de cualquier número de caracteres. En el caso de que haya alguna coincidencia, la fórmula devolverá un 1.

 

Ahora, bastará buscar en qué fila del rango H3:H10 está ese 1.

 

En I3:

=COINCIDIR(1;$H$3:$H$10;0)     [Terminar con Intro]

 

Una vez que hemos determinado que hay coincidencia con el sexto elemento del rango H3:H10, usaremos la función INDICE para determinar la ciudad asociada. También contemplaremos la posibilidad de que no haya coincidencia y se haya producido error.

 

En J3:

=SI.ERROR(INDICE($F$3:$F$10;$I$3);"******")     [Terminar con Ctrl + Mayús + Intro]

 

Como siempre, pondremos la fórmula definitiva en C3.

 

En C3:

=SI.ERROR(INDICE($F$3:$F$10;COINCIDIR(1;HALLAR("*"&$E$3:$E$10&"*";$B3)));"******")     [Terminar con Ctrl + Mayús + Intro]

 

Finalizamos el ejercicio extendiendo la fórmula hasta la fila 17.

 

Sexta solución

 

La última solución será la más corta. Sólo requerirá dos columnas auxiliares.

 

Seleccionamos H3:H10 y escribimos:

=HALLAR($E$3:$E$10;$B3)     [Terminar con Ctrl + Mayús + Intro]

 

Si hay una palabra clave, en H3:H10 habrá un número (como ocurre en nuestro ejemplo). El truco consiste en utilizar la función BUSCAR para buscar no ese número sino uno mayor. La función BUSCAR tiene la particularidad de que si no encuentra el número buscado, se queda con el número más cercano que sea inferior al buscado. Usando el número 10³⁰⁰ nos aseguramos de que en la columna H no haya ninguno mayor.

 

En I3:

=SI.ERROR(BUSCAR(10^300;$H$3:$H$10;$F$3:$F$10);"******")     [Terminar con Intro]

 

Concluimos con la fórmula final.

 

En C3:

=SI.ERROR(BUSCAR(10^300;HALLAR($E$3:$E$10;B3);$F$3:$F$10);"******")     [Terminar con Intro y extender la fórmula hasta la fila 17]

EJERCICIO

ASOCIAR PALABRAS 2

Asociar palabras (2 de 3)

Siguiendo con el problema planteado en la entrada anterior, vamos a asignar a las frases de la columna B el nombre de la ciudad asociada a la palabra clave que contiene el texto.

Segunda solución

 

Comprobamos  si la frase de la celda B3 contiene alguna palabra clave.

 

Seleccionamos H3:H10 y escribimos:

=HALLAR($E$3:$E$10;$B3)     [Terminar con Ctrl + Mayús + Intro]

 

Sustituimos los errores por FALSO y el número por VERDADERO.

 

Seleccionamos I3:I10 y escribimos:

=ESNUMERO($H$3:$H$10)     [Terminar con Ctrl + Mayús + Intro]

 

Determinamos en qué fila de la lista I3:I10 hay VERDADERO.

 

Seleccionamos J3:J10 y escribimos:

=($I$3:$I$10)*(FILA($E$3:$E$10)-FILA($E$3)+1)     [Terminar con Ctrl + Mayús + Intro] 

 

VERDADERO está en la fila 6. Aislamos ese valor en la celda K3.

 

En K3:

=SUMA($J$3:$J$10)      [Terminar con Intro]

 

Podría darse la circunstancia de que la frase no contuviera ninguna palabra clave, en cuyo caso, la fórmula deK3 devolvería cero. Hemos de tener en cuenta este supuesto para determinar el nombre de la ciudad asociada.

 

En L3:

=SI($K$3=0;"******";INDICE($F$3:$F$10;$K$3))      [Terminar con Intro]

 

Una vez desarrolladas todas las fórmulas (cinco columnas auxiliares), creamos en C3 la fórmula compuesta.

 

En C3:

=SI(SUMA((ESNUMERO(HALLAR($E$3:$E$10;$B3)))*(FILA($E$3:$E$10)-FILA($E$3)+1))=0;"******";INDICE($F$3:$F$10;SUMA((ESNUMERO(HALLAR($E$3:$E$10;$B3)))*(FILA($E$3:$E$10)-FILA($E$3)+1))))     [Terminar con Ctrl + Mayús + Intro]

 

Finalizamos extendiendo la fórmula hasta la fila 17.

 

Tercera solución

 

En la primera solución del artículo anterior usamos 7 columnas auxiliares; en la segunda solución, 5; y en ésta lo haremos con 4.

 

El primer paso es el mismo.

 

Seleccionamos H3:H10 y escribimos:

=HALLAR($E$3:$E$10;$B3)     [Terminar con Ctrl + Mayús + Intro]

 

Mantenemos los errores y sustituimos el número (si existe) por un 1.

 

Seleccionamos I3:I10 y escribimos:

=SI($H$3:$H$10>0;1;0)     [Terminar con Ctrl + Mayús + Intro]

 

Comprobamos en qué fila de la lista I3:I30 está el número 1.

 

En J3:

=COINCIDIR(1;$I$3:$I$10;0)     [Terminar con Intro]

 

Usamos la función INDICE para determinar la ciudad asociada. Si J3 contiene un error, lo capturamos conSI.ERROR y devolvemos una lista de asteriscos.

 

En K3:

=SI.ERROR(INDICE($F$3:$F$10;$J$3);"******")     [Terminar con Intro]

 

Ponemos la fórmula definitiva en C3:

=SI.ERROR(INDICE($F$3:$F$10;COINCIDIR(1;SI(HALLAR($E$3:$E$10;$B3)>0;1;0);0));"******")     [Terminar con Ctrl + Mayús + Intro]

 

Extendemos la fórmula hasta la fila 17.

 

Cuarta solución

 

También con 4 columnas auxiliares, podemos resolver el problema modificando ligeramente el razonamiento.

 

Seleccionamos H3:H10 y escribimos:

=HALLAR($E$3:$E$10;$B3)     [Terminar con Ctrl + Mayús + Intro]

 

En el segundo paso, sustituimos los errores por blancos, y el número por su posición en la lista H3:H10.

 

Seleccionamos I3:I10 y escribimos:

=SI(ESERROR($H$3:$H$10);"";FILA($F$3:$F$10)-2)     [Terminar con Ctrl + Mayús + Intro]

 

En J3:

=SUMA($I$3:$I$10)     [Terminar con Intro]

 

En K3:

=SI($J$3=0;"******";INDICE($F$3:$F$10;$J$3))     [Terminar con Intro]

 

Escribimos la fórmula compuesta en C3:

=SI(SUMA(SI(ESERROR(HALLAR($E$3:$E$10;$B3));"";FILA($F$3:$F$10)-2))=0;"******";INDICE($F$3:$F$10;SUMA(SI(ESERROR(HALLAR($E$3:$E$10;$B3));"";FILA($F$3:$F$10)-2))))     [Terminar con Ctrl + Mayús + Intro]

 

Extendemos la fórmula hasta la fila 17.

EJERCICIO

ASOCIAR PALABRAS

Asociar palabras (1 de 3)

Las frases de la columna B contienen palabras clave relacionadas con fenómenos meteorológicos. El objetivo del ejercicio es asignar a cada frase el nombre de la ciudad asociada a cada palabra clave según la tabla del rango E2:F10. El resultado irá en la columna C. Así, la frase "Como una ventisca helada", contiene la palabra clave "ventisca", que lleva asociada la ciudad de "Pisa".

El ejercicio lo resolveremos de varias maneras, empezando con los métodos que utilizan fórmulas más largas y terminando con las más cortas. En esta entrada estudiaremos la fórmula más larga y dejaremos las otras para otro artículo.

 

Primera solución

 

Usaremos las columnas H a N para obtener valores auxiliares. Finalizado el ejercicio podremos prescindir de esos valores ya que la fórmula compuesta la colocaremos en la columna C.

 

Usando la frase de la celda B3 para hacer el razonamiento, primero, comprobaremos, una a una, si hay alguna palabra clave en la frase. En caso de haberla, en qué lugar comienza. La frase que no contenga ninguna palabra clave devolverá un error. Inicialmente, todas las frases tienen una palabra clave. Más adelante consideraremos el caso de que no tengan ninguna.

 

Seleccionamos H3:H10 y escribimos:

=HALLAR($E$3:$E$10;$B3)     [Terminar con Ctrl + Mayús + Intro]

 

Sustituimos los errores por ceros.

 

Seleccionamos I3:I10 y escribimos:

=SI.ERROR($H$3:$H$10;0)     [Terminar con Ctrl + Mayús + Intro]

 

Sumando los números de la columna I obtendremos la posición en la que comienza la palabra clave.

 

En J3:

=SUMA($I$3:$I$10)     [Resultado: 10]

 

Ya sabemos que la palabra clave empieza en el carácter décimo. Ahora, necesitamos saber dónde acaba para determinar su longitud. Como no hemos puesto signos de puntuación, la palabra clave irá seguida de un espacio (si está en medio de la frase) o no habrá nada después de ella (si es la última palabra de la frase).

 

En K3:

=SI.ERROR(HALLAR(" ";$B3;J3);LARGO($B3)+1)     [Resultado: 18]

 

La longitud será la diferencia de las celdas K3 y J3.

 

En L3:

=K3-J3     [Resultado: 8]

 

La palabra clave la obtendremos extrayendo 8 caracteres (L3) de la frase (B3) empezando desde el carácter 10 (J3).

 

En M3:

=EXTRAE($B3;J3;L3)     [Resultado: ventisca]

 

Una vez que tenemos la palabra clave, con BUSCARV, obtenemos la ciudad asociada.

 

En N3:

=BUSCARV(M3;$E$3:$F$10;2;FALSO)     [Resultado: Pisa]

 

Sólo falta crear la fórmula compuesta en C3 y copiar la fórmula hacia abajo.

 

En C3:

=BUSCARV(EXTRAE($B3;SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0));SI.ERROR(HALLAR(" ";$B3;SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0)));LARGO($B3)+1)-SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0)));$E$3:$F$10;2;FALSO)     [Resultado: Pisa]

 

Arrastramos la fórmula hasta la fila 17.

 

¿Qué ocurre si alguna frase no tiene palabra clave? Para comprobarlo, sustituimos la frase de la celda B17 por la siguiente: "Cuando se disipó la bruma". Excel nos devuelve un error.

 

La solución es sencilla. Basta capturar el error con la función SI.ERROR y mostrar en su lugar un espacio en blanco, una línea, un conjunto de asteriscos, o lo que queramos.

 

En C3:

=SI.ERROR(BUSCARV(EXTRAE($B3;SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0));SI.ERROR(HALLAR(" ";$B3;SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0)));LARGO($B3)+1)-SUMA(SI.ERROR(HALLAR($E$3:$E$10;$B3);0)));$E$3:$F$10;2;FALSO);"*******")

 

Si añadimos nuevas frases a la columna B, tendremos que copiar hacia abajo la fórmula de la columna C. Sin embargo, esto no será necesario si transformamos la lista de las columnas B y C en una tabla. Para ello, nos colocamos en cualquier celda del rango B2:C17 y accedemos a Insertar + Tabla. Excel mostrará el cuadro de diálogo correspondiente.

 

Nos aseguramos de que muestre los valores de la figura y pulsamos Aceptar.

 

Añadimos unas cuantas frases:

 

En la columna C, las fórmulas de las nuevas filas se han generado automáticamente.

EJERCICIO

FUNCIÓN AGREGAR

La función AGREGAR

En Excel 2010, Microsoft ha añadido la función AGREGAR, que se usa de un modo parecido aSUBTOTALES. Tiene dos sintaxis:

Primera sintaxis: AGREGAR(núm_función; opciones; ref1; [ref2]; …)

Consideremos una lista de valores numéricos en la que hay intercalados varios errores:

En D1:

=SUMA(A1:A11)     [Resultado: #¡DIV/0!]

 

Excel no puede hallar la suma si en el rango de datos hay algún error.

 

La función AGREGAR permite hallar la suma omitiendo los errores. A medida que vayamos escribiendo la fórmula, Excel mostrará las opciones disponibles.

 

En D2, escribimos: =AGREGAR(

 

Elegimos la opción 9 y ponemos punto y coma. Excel abre otro menú:

Seleccionamos la opción 6 y completamos la fórmula:

En D2:
=AGREGAR(9;6;A1:A11)     [Resultado: 462]

La suma se ha realizado correctamente sin tener en cuenta las celdas con errores. Para operar con rangos no contiguos, bastará separarlos con punto y coma. Por ejemplo, si quisiéramos hallar el promedio de los rangosC4:C10, H5:H20 y M2:N40, la fórmula sería: =AGREGAR(1;6;C4:C10;H5:H20;M2:N40)  

El 1 (primer argumento) selecciona la función PROMEDIO mientras que el 6 (segundo argumento) omite los posibles valores de error que contengan los rangos indicados en el tercer, cuarto y quinto argumento.

Segunda sintaxis: AGREGAR(núm_función, opciones, matriz, [k])

Si núm_función está comprendido entre 14 y 19, es necesario añadir el argumento [k]. Esto ocurre porque las funciones correspondientes requieren este segundo argumento. Como ejemplo, vamos a calcular el tercer número mayor del rango A1:B11

En D3:
=AGREGAR(14; 6; A1:B11; 3)     [Resultado: 95]

Si no hubiéramos omitido los errores (segundo argumento igual a 4), Excel devolvería un error. Habría sido lo mismo que escribir: =K.ESIMO.MAYOR(A1:B11;3)

FUNCIONES CONTAR.SI.CONJUNTO Y SUMAR.SI.CONJUNTO

Las nuevas funciones «___.SI.CONJUNTO»

Las funciones CONTAR.SI, SUMAR.SI y PROMEDIO.SI solamente admiten un criterio. Pero, a veces, es necesario contar, sumar o promediar rangos de celdas que cumplan más de una condición. En Excel 2007 y 2010, hay tres funciones nuevas que sirven para este propósito: CONTAR.SI. CONJUNTO,SUMAR.SI.CONJUNTO y PROMEDIO.SI.CONJUNTO.

Las tres funciones tienen una sintaxis similar. Por ejemplo, para CONTAR.SI. CONJUNTO la ayuda de Excel muestra la siguiente información.

----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONTAR.SI.CONJUNTO(rango_criterio1;criterio1;[rango_criterio2;criterio2]...)

Aplica criterios a las celdas en varios rangos y cuenta cuántas veces se cumplen dichos criterios.

• rango_criterio1: Obligatorio. El primer rango en el que se evalúan los criterios asociados.
• criterio1: Obligatorio. Los criterios en forma de número, expresión, referencia de celda o texto que determinan las celdas que se van a contar. Por ejemplo, los criterios que se pueden expresar como 32, ">32", B4, "manzanas" o "32".
• rango_criterio2;criterio2...: Opcional. Rangos adicionales y criterios asociados. Se permiten hasta 127 pares de rango/criterio.

Importante: Cada rango adicional debe tener la misma cantidad de filas y columnas que el argumentorango_criterio1. No es necesario que los rangos sean adyacentes.

Observaciones:

• Los criterios de cada rango se aplican a una celda cada vez. Si todas las primeras celdas cumplen los criterios asociados, el número aumenta en 1. Si todas las segundas celdas cumplen los criterios asociados, el número aumenta en 1 nuevamente y así sucesivamente hasta evaluar todas las celdas.
• Si el argumento de los criterios hace referencia a una celda vacía, la funciónCONTAR.SI.CONJUNTO trata dicha celda como un valor 0.

En los criterios se pueden usar caracteres comodín, el signo de interrogación (?) y el asterisco (*). El signo de interrogación se corresponde con un solo carácter y el asterisco se corresponde con cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco reales, escriba una tilde (~) delante del carácter que desea buscar.

EJERCICIO