Formularios

DESCARGAR FORMULARIO LISTADO

Un control muy similar al Cuadro de lista es la Lista Desplegable, el cual básicamente cumple la misma función ya que nos presenta un listado de opciones de la cual podemos seleccionar la deseada. La diferencia entre el Cuadro de lista y la Lista desplegable es que el primero permite ver varias opciones a la vez desplazándonos con una barra vertical mientras que la Lista desplegable solo muestra una opción y para ver las demás debemos desplegar la lista (idéntico a la Validación de Datos).

Para utilizar este control, nos dirigimos a la ficha DESARROLLADOR, herramientas Controles seleccionamos la opción Insertar y luego Lista Desplegable:

Con esto se activa el cursor del mouse en modo edición para poder dibujar en la hoja Excel el Cuadro de lista:

Al igual que el Cuadro de Lista, debemos indicar a este control cuales son los datos que necesitamos nos presente para nuestro ejercicio, vamos a contar con un listado de los vendedores a los cuales se tienen asociado unos montos de ventas en la Hoja2:

Para que nuestro nos presente el listado de los vendedores damos clic derecho sobre el mismo y seleccionamos la última opción Formato de control; con esto se muestra la ventana de formato para configurar las opciones:

En la ficha Control tenemos 3 opciones:

Rango de entrada: En esta opción indicaremos el rango de celdas del libro de Excel donde están los datos que necesitamos se muestren en el control de lista desplegable. Así que damos clic en el botón de selección  y seleccionamos el rango del listado de vendedores que tenemos en la Hoja2:

Vincular con la celda: Con esta segunda opción, es donde debemos indicar en cual celda deseamos obtener el resultado de la interacción que realicemos con el control Lista Desplegable. La interacción en este control es simplemente seleccionar la opción deseada del listado que se presenta en la lista y el resultado de esta interacción es un valor numérico entero que va desde 1 hasta el número de opciones que se tiene en la lista, así, al seleccionar el primer valor de la lista, el resultado será 1, si seleccionamos la quinta opción de la lista, el resultado será 5 y así sucesivamente. Para indicar en cual celda deseamos obtener el resultado damos clic en el botón de selección de esta opción:

Y vamos a seleccionar la celda $A$5.

Líneas de unión verticales: Con esta opción podemos indicar el número de reglones que deseamos se desplieguen una vez demos clic en la flecha del control Lista Desplegable; por defecto el valor es de 8, es decir que se muestran 8 valores al desplegar la lista y para ver las demás opciones nos desplazamos por la barra vertical. Para nuestro ejercicio (y en general) vamos a dejar el valor por defecto, es decir 8

La configuración de estas opciones quedará de la siguiente manera:

Veamos entonces que al dar clic en la flecha desplegable del control, podemos ver que tenemos todo el listado de vendedores:

Ahora, al seleccionar cualquier vendedor, en la celda A5 veremos el número o posición en la cual se encuentra el vendedor seleccionado:

Vamos a darle un poco más de funcionalidad a la interacción con este control, diseñando un esquema en esta Hoja1 donde podamos ver las ventas de cada mes para el vendedor seleccionado en la lista desplegable:

Con el uso de la función INDICE podemos obtener el valor de las ventas de cada mes para el vendedor seleccionado:

Vamos a adicionar otro control de Lista Desplegable que nos ayude a comparar los resultados entre dos vendedores, así que solo copiamos y pegamos la Lista Desplegable que tenemos y la ubicamos debajo, donde solo debemos ajustar en el formato de este segundo control, la celda con la cual está vinculada ya que no puede ser la misma del primer control (A5), debe quedar vinculado esta segunda lista desplegable a la celda A6:

Por último, agregamos un gráfico sencillo de columnas que nos muestre los valores de los vendedores que seleccionamos y damos formato a la hoja:

PLANTILLA DE AYUDA

Buscar objetivo

¿En qué consiste Buscar objetivo en Excel?, pues en encontrar un resultado deseado mediante la modificación del valor de una determinada celda de la que va a depender el valor deseado.

Empezare con un ejemplo sencillo si bien te comento otros ejemplos en los que puedes usar esta herramienta en Excel:

· Imagina que quieres comprar un nuevo mobiliario para tu oficina que vale 1.200 dólares y lo vas a pagar a plazos, el numero de plazo de pago que estableces con el vendedor es de 24 meses, a un tipo de interés del 10% te sale una cuota a pagar de 55 dólares. Pero resulta que no puedes pagar esa cantidad mensual, solo puedes hacer frente al pago de 30 dólares al mes, esto alargara los pagos a realizar, con Buscar objetivo encontraras rápidamente la respuesta acerca de en cuantos plazos terminaras de pagar la compra.

· Otro ejemplo: Recibes un sueldo bruto al año de 20.000 dólares y el descuento para los impuestos es del 15% (obviando otro tipo de descuentos). Imagina que puedes pedir una reducción del impuesto para que aumente la cantidad liquida a percibir, si quisieras recibir 18.500 dólares líquidos al mes con Buscar Objetivo en Excel podrás calcular que tipo de retención se aplicaría.

Uso de Buscar Objetivo.

Ejemplo sencillo: Vendes una determinada mercancía que cuesta 20 dólares, la cantidad vendida en el último año ha sido de 500 unidades, con ello has tenido unas ventas de 10.000 dólares, que es el resultado de multiplicar 20x500=10.000.

Ahora quieres saber cuántas unidades has de vender para obtener unas ventas de 15.000 dólares.

Pasos:

· Abre una hoja de cálculo Excel en blanco.

· Escribe en la columna A lo siguiente:

En A1: Precio

En A2: Cantidad

En A3: Ventas

· Escribe en la columna B:

En B1: 20

En B2: 500

En B3: La formula B1xB2, te tiene que aparecer el resultado que es 10.000

· A continuación has de hacer clic en la pestaña Datos.

· Después en el grupo de trabajo llamado Herramientas de análisis, haz clic en el triangulo negro al lado de Análisis Y si.

· Se despliegan una serie de opciones, pulsa en Buscar objetivo.

· Se abre una ventana en la que vas a colocar los datos de la siguiente manera:

En Definir la celda: haz clic sobre la celda B3 que es la que queremos que se modifique y alcance la cifra de 15.000 dólares de ventas.

En Con el valor: Escribe 15.000.

En Para cambiar la celda: Haz clic en B2, que es donde está el dato sobre el número de unidades a vender.

Aceptar.

La ventana cambia de apariencia al igual que los datos de la hoja de cálculo ahora puedes comprobar que para obtener 15.000 dólares de ventas has de vender 750 unidades, manteniendo constante el precio en 20 dólares la unidad.

Ejercicio

Nos tocan 20 Millones de Euros en la lotería. La alegría fue tan grande que compramos una casa por valor de 1 Millón.

Hicimos una plantilla con Excel destinando el dinero del premio a varias cosas.

Al ir a cobrar el premio, comprobamos que nos han tocado solo 10.345.612 Euros. con lo que tenemos que redistribuir el dinero del premio.

Utilizando la herramienta de Búsqueda de Objetivo, tenemos que determinar que parte del premio de 10 millones podemos destinar ahora a juerga y ocio.

Escenarios

El "Administrador de escenarios" de Excel es una buena herramienta de análisis de datos con multiples aplicaciones en el ámbito de la Dirección Financiera o Comercial. Es aplicable a cuentas de resultados, presupuestos y en general, cualquier contexto en el que haya que operar con varios parámetros.

Para conocer su funcionamiento, vamos a descargar una plantilla sobre la que practicaremos:

DESCARGAR

Si vamos a la hoja "Escenarios", podemos ver una tabla similar a la de la imagen de abajo, con la cuenta de resultados previsional a 5 años (simplificada para el ejercicio) de una empresa. También podemos ver más arriba un cuadro con 3 parámetros supuestos referidos al crecimiento que se espera experimente la empresa y a la evolución del Índice de Precios al Consumo:

Las celdas del año 2 contienen fórmulas que calculan el crecimiento respecto de los valores de las celdas del año 1. Las celdas del año 3 contienen fórmulas que hacen lo propio respecto de los valores de las celdas del año 2..... y así sucesivamente. Por lo tanto las únicas celdas de la tabla de abajo que contienen valores (no fórmulas) son las del año 1.

Es una imagen muy frecuente en el departamento financiero de cualquier pyme o bien en el plan de viabilidad económica de cualquier emprendedor, pero imaginemos que el director financiero o el gerente de la empresa necesita valorar distintas situaciones que se pudieran dar debido a la coyuntura económica y/o a la propia empresa, variando los valores de algunos parámetros... ¿cómo se podría hacer esta simulación?.

Supongamos que las tablas que presentamos anteriormente conforman un "escenario moderado" y el gerente necesita estudiar otros posibles escenarios (uno optimista y otropesimista) para poder prever una toma de decisiones en todos los casos:

1) Hagamos click en la "Ficha Datos", después en el botón "Análisis Y si" (que hay dentro del grupo"Herramientas de datos") y después en "Administrador de escenarios...". Obtendremos el cuadro de diálogo:

... donde tendremos que agregar uno a uno, los "escenarios" que queremos estudiar, empezando por el  moderado (que es el que aparece de partida en las tablas) y terminando con el pesimista y optimista.

2) Hacemos click en "Agregar" e introducimos en el siguiente cuadro de diálogo que emerge, los siguientes datos y referencias:

 

En el "Nombre de escenario" podemos poner libremente un nombre con el que queremos identificar el escenario.

En "Celdas cambiantes", una vez borramos la referencia que aparece por defecto, presionamos CTRL y con el ratón seleccionamos el rango de celdas H3:H5 y, sin haber dejado de hacer presión sobre CTRL, selesccionamos el rango de celdas D9;D10. El resultado que obtenemos son 2 rangos separados por punto y coma (Excel pone automáticamente el signo punto y coma y los signos de $).

3) Aceptamos y obtenemos el siguiente cuadro:

4) Vemos que lo que aparece son los datos que había en las celdas seleccionadas y que deseamos queden memorizadas como "Escenario moderado". Así pues, no tenemos que cambiar los datos y damos a "Agregar", obteniendo de nuevo el cuadro de diálogo donde se definen los escenarios (el nombre y las celdas cambiantes).

5) Repetimos exactamente el paso 2), pero en el "Nombre de escenario" , ponemos "Escenario pesimista" y aceptamos (las celdas cambiantes las pone Excel ya por defecto esta vez. Obteniendo de nuevo el pequeño cuadro de diálogo "Valores de escenario".

6) Esta vez, cambiamos los valores que nos presenta por defecto Excel, por los valores del "Escenario pesimista" de nuestra tabla de supuestos. Es decir:

... y damos a "Agregar".

7) Repetimos los pasos 5) y 6), pero esta vez poniendo como nombre de escenario "Escenario optimista" y en el cuadro de diálogo "Valores del escenario" ponemos los valores del escenario optimista de nuestros supuestos. Es decir:

...y esta vez, puesto que no tenemos que agregar más escenarios,hacemos click en "Aceptar" y obtenemos:

Ahora ya tenemos todos los escenarios configurados, sólo nos queda seleccionar el deseado en el panel "Escenarios" y hacer click en el botón "Mostrar". Podremos ver cómo se introducen automáticamente en las 5 celdas los datos de nuestro supuesto y todo queda recalculado según el escenario elegido.

Debemos apreciar que cuando hacemos clik en "Mostrar" el cuadro de diálogo no desaparece. Esto es para que podamos cambiar entre escenarios más rápidamente y poder apreciar los cambios con más facilidad.

En el cuadro de diálogo anterior ("Administrador de escenarios"), vemos que existe un botón llamado "Resumen..." que al hacer click sobre él obtenemos el siguiente cuadro, donde debemos seleccionar en el campo "Celdas de resultado:", el rango de celdas que consideramos como resultados importantes para nosotros y cuyo resultado para cada escenario, nos gustaría saber (vemos abajo que se han seleccionado arbitrariamente como rango de celdas importantes, las que reflejan el beneficio en porcentaje respecto de las ventas):

Si hacemos click en "Aceptar", obtenemos un cuadro - resumen que es totalmente modificable (y no se actualiza al volver a cambiar los datos en la tabla sobre la que hemos aplicado los escenarios). Una vez cambiamos las celdas en las que obtenemos resultados irrelevantes para nosotros, podemos obtener un interesante resumen como este:

Insertar el control Calendario en una hoja

Para poner una fecha en una celda, podemos escribirla directamente o seleccionarla en un calendario insertado en la hoja y vinculado con la celda. Este calendario es un control ActiveX que se instala extrayéndole de la ficha Programador. En Excel 2010 el "Control de calendario" ha sido eliminado, pero si al instalar Excel 2010 se mantuvo la versión anterior (2003 ó 2007), podrá seguir utilizándose.

Accedemos a Programador + Insertar + Controles ActiveX + Más controles.

En el cuadro de diálogo Más controles, seleccionamos Control de calendario 11.0 y pulsamos Aceptar.

Marcamos un rectángulo en el lugar donde queremos poner el control y el calendario quedará insertado.

Con el calendario seleccionado, accedemos al menú contextual y elegimos Propiedades (también, desde el grupo Controles de la ficha Programador, clic en Propiedades). En la propiedad LinkedCell ponemos la celda a la que queremos vincular la fecha elegida en el calendario. En el ejemplo, la celda C3.

Terminamos cerrando la ventana de Propiedades haciendo clic en el botón Modo Diseño.

Ahora, elegimos una fecha cualquiera en el calendario; por ejemplo, el 6 de junio de 2012. La fecha seleccionada se insertará en la celda C3 con el formato "dd/mm/aaaa".

Hay un detalle extraño. ¿Por qué se ajusta la fecha a la izquierda de la celda cuando, por defecto, las fechas se ajustan a la derecha? La razón es sencilla, el dato se inserta como un texto, no como una fecha. Si ponemos en una celda vacía: =ESTEXTO(C3), Excel nos devolverá VERDADERO, confirmando que el dato es un texto.

Si intentamos poner a C3 un formato de fecha distinto; por ejemplo, dddd, dd "de" mmmm "de" aaaa, veremos que no se puede. Es lógico, a un texto no se le puede asignar un formato de fecha.

Para hacerlo, tendremos que usar otra celda (por ejemplo, la C4) en la que se transforme el texto en el valor numérico de la fecha seleccionada en el control calendario. Esto se puede hacer de dos formas: usando la función VALOR, o sumándo a C3 un cero.

En C4:

=VALOR(C3)   [Resultado: 41066]

En D4:

=C3+0   [Resultado: 41066]

Ahora ya podemos poner a C4 (o a D4) el formato dddd, dd "de" mmmm "de" aaaa. El resultado será:miércoles, 06 de junio de 2012.

Seis métodos de búsqueda

Una operación frecuente en Excel es la búsqueda de datos en una tabla. Por ejemplo, con los datos de la tabla siguiente, queremos saber la cantidad de guantes vendidos en el mes de octubre.

En C15 escribimos el mes: Octubre

En C16 escribimos el nombre del producto: Guantes

Ahora, pondremos 6 fórmulas diferentes para hallar los guantes que se vendieron en octubre.

1ª fórmula: BUSCARV combinada con COINCIDIR

En C18:

=BUSCARV(C15;B2:F13;COINCIDIR(C16;B2:F2;0);FALSO)

2ª fórmula: BUSCARH combinada con COINCIDIR

En C19:

=BUSCARH(C16;B2:F13;COINCIDIR(C15;B2:B13;0))

3ª fórmula: INDICE combinada con COINCIDIR

En C20:

=INDICE(C3:F13;COINCIDIR(C15;B3:B13;0);COINCIDIR(C16;C2:F2;0))

4ª fórmula: SUMAPRODUCTO

En C21:

=SUMAPRODUCTO((B3:B13=C15)*(C2:F2=C16)*(C3:F13))

5ª fórmula (matricial): SUMA

En C22:

=SUMA((B3:B13=C15)*(C2:F2=C16)*(C3:F13))   [Terminar con Ctrl + Mayús + Intro]

6ª fórmula: DIRECCION, INDIRECTO y COINCIDIR

En C23:

=INDIRECTO(DIRECCION(COINCIDIR(C15;B1:B13;0);COINCIDIR(C16;A2:F2;0)))

Consideraciones: 

• BUSCARV sólo se puede utilizar si la columna de los meses es la primera. Del mismo modo,BUSCARH requiere que la lista de productos esté en la primera fila. No se podrán utilizar en la tabla siguiente:



• INDICE se puede utilizar en cualquier circunstancia.
• SUMAPRODUCTO y SUMA sólo se pueden utilizar si la tabla contiene valores numéricos, ya que hemos hecho una operación matemática (que requiere números).
• DIRECCION + INDIRECTO se pueden utilizar en cualquier circunstancia. Conviene recordar queINDIRECTO es una función volátil.

Ejercicio 1

Ejercicio 2

COINCIDIR

Encuesta de satisfacción

Desarrolle la siguiente encuesta AQUI

Pruebas

Acceder a esta prueba AQUI

La función DESREF en Excel

La función DESREF en Excel nos devuelve una referencia a un rango de celdas que ha sido desplazado respecto a otra referencia que hemos especificado ¿Suena complicado? Vaya que es complicado explicar, pero la función DESREF es uno de esos tesoros escondidos de Excel.

La función DESREF y los Boy Scouts

Después de pensar cómo explicar mejor la función DESREF he recordado un juego que teníamos en los “Boy Scouts” cuando estábamos aprendiendo a utilizar la brújula.  El juego consistía en encontrar el tesoro escondido siguiendo las instrucciones dadas:

1. Colocarse en el árbol marcado con la banda amarilla
2. Caminar 50 pasos hacia en Norte
3. Caminar 35 pasos al Oeste
4. Encontrarás el tesoro debajo de una piedra.

Si seguíamos las instrucciones correctamente entonces encontrábamos el tesoro escondido justamente en la ubicación indicada. Pero ¿qué tienen que ver los Boy Scouts con la función DESREF en Excel? Imagina que podemos dar a Excel un conjunto de instrucciones para que “encuentre el tesoro” dentro de una hoja:

1. Colocarse en la celda B5
2. Moverse 50 filas hacia abajo
3. Moverse 35 columnas a la derecha
4. Devolver la referencia de la celda donde te encuentres

Podemos hacer que Excel siga estos pasos utilizando la función DESREF. Los pasos anteriores serán  seguidos al utilizar la siguiente fórmula:

=DESREF(B5, 50, 35)

Con esta fórmula Excel nos devolverá la referencia a la celda AK55 ya que a partir de la celda B5 se moverá 50 filas hacia abajo y 35 columnas a la derecha.

Sintaxis de la función DESREF

Con la analogía previamente hecha será más sencillo explicar el detalle de la función DESREF en Excel y su sintaxis.

• Ref (obligatorio): La referencia a la celda o rango donde se iniciará el desplazamiento.
• Filas (obligatorio): El número de filas a moverse. Si el valor es positivo se moverá hacia abajo y si es negativo se moverá hacia arriba.
• Columnas (obligatorio): El número de columnas a moverse. Si el valor es positivo se moverá hacia la derecha y si es negativo se moverá a la izquierda.
• Alto (opcional): El número de filas que deseamos que nos devuelva como resultado.
• Ancho (opcional): El número de columnas que deseamos que nos devuelva como resultado.

De esta manera, para indicar a la función DESREF que necesitamos desplazarnos 5 filas hacia abajo y 3 columnas a la derecha a partir de la celda A1 utilizaremos la siguiente fórmula:

=DESREF(A1, 5, 3)

Notas importantes sobre la función DESREF

Algo muy importante que debo decir sobre la función DESREF en Excel es que si el primer argumento de la función es una referencia a una sola celda entonces la función nos devolverá de manera la referencia a una sola celda. Observa la siguiente imagen:

Ya que la función DESREF nos devuelve la referencia a la celda C4 es posible mostrar su valor en la celda C7. Ahora considera lo siguiente, si en lugar de especificar una sola celda como el primer argumento de la función proporcionamos un rango, entonces nos daremos cuenta que la función DESREF devuelve una referencia a un rango y no podremos mostrar su resultado sino que obtendremos un error:

El error no está en la función DESREF sino al intentar desplegar su resultado en la celda C7 cuando en realidad nos está devolviendo una referencia a un rango. Para este ejemplo específico la función DESREF devuelve la referencia C3:C4 y lo puedo demostrar utilizando la función SUMA sobre el resultado de la función DESREF:

Observa que no he cambiado la función DESREF sino que solamente apliqué la función SUMA al rango de celdas devuelto por la función. En este ejemplo la función SUMA hace la operación SUMA(C3:C4) que es precisamente el resultado mostrado en la celda C7. Por esta razón es que frecuentemente observarás que lafunción DESREF es utilizada en junto con otras funciones.

Ya te has dado cuenta también que si el primer argumento es una referencia a una sola celda, la función DESREF devolverá también una sola celda. Pero si el primer argumento es un rango de celdas entonces obtendremos también una referencia a un rango de celdas.

Ejemplos de la función DESREF

Ya hemos visto varios ejemplos de la función DESREF en Excel, solo mostraré algunos ejemplos adicionales para dejar más claro el uso de la función. En el siguiente ejemplo coloco en el segundo argumento de la función un número negativo que hará un desplazamiento de filas hacia arriba:

Ahora quiero mostrarte otro ejemplo pero utilizando el cuarto y quinto argumento de la función DESREF. Ya sabemos que al indicar una referencia a una sola celda en el primer argumento de la función DESREF obtendremos como resultado la referencia a una sola celda. Este comportamiento lo puedes modificar si utilizas el cuarto y quinto argumento de la función. En la siguiente fórmula estoy indicando a la función DESREF iniciar en la celda A4 y desplazarse hacia arriba y a la derecha.

=SUMA(DESREF(A4, -2, 2, 3, 2))

Aunque he especificado una sola celda de inicio también estoy indicando a la función que a partir de la celda destino encontrada me devuelva en total 3 filas y dos columnas. En la siguiente imagen puedes observar el rango devuelto con un fondo de color rojo:

Usos prácticos de la función DESREF

A continuación mencionaré algunos usos prácticos de la función DESREF en Excel. Para cada uno de ellos podrás leer un artículo con información adicional sobre el uso de la función.

• Crear un rango dinámico. Excel nos permite asignar un nombre a un rango de celdas para después hacer referencia a ellas. Sin embargo, si necesitamos aumentar o disminuir el tamaño de dicho rango necesitamos editar continuamente el nombre y su rango asociado. Este problema lo podemos solucionar utilizando la función DESREF. Consulta el artículo Nombres de rango dinámicos.
• Actualizar una lista desplegable. Las listas desplegables, que forman parte de la validación de datos en Excel, se basan en un rango de datos. Si creamos un rango dinámico sobre el cual se base la lista desplegable, entonces lograremos que la lista se actualice automáticamente. Lee más al respecto en el artículo Cómo actualizar una lista desplegable en Excel.
• Actualizar listas dependientes. Si tenemos dos listas y deseamos que las opciones de una dependan de la selección de otra entonces podemos utilizar la función DESREF para crear dicha dependencia. Lee más al respecto en el artículo Cambiar valores de una lista basados en la selección de otra lista.

Así como algunas otras funciones de Excel, la función DESREF no es de mucha utilidad si la usamos sola, pero si la combinamos con algunas otras funciones nos ayudará a implementar muy buenas soluciones en Excel.

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