La función BUSCARV con virgulilla o tilde.

Hace un par de días respondí a una cuestión que me pareció de gran utilidad para este blog, por lo curioso y práctico. La pregunta que se hacía era en qué forma se podía realiza un BUSCARV textual, donde el vlaor buscado incluía un asterísco como caracter, empleado normalmente como comodín:

...MI duda es como refinar o anidar funciones que me permitan evitar error de búsqueda al usar Buscarv con valor Falso sobre valores que contienen el símbolo " * ", es decir, cuando manejo datos que contienen * especialmente que terminen con dicho símbolo, al realizar la búsqueda me encuentra un valor diferente lo cual es muy problemático especialmente cuando manejo matrices que contienen una gran cantidad de regístros. ejemplo: mi deseo es encontrar el registro ad* y manejo la matriz "Prueba" que contiene los valores: ad* ad*o adpo Al momento de usar la función Buscarv("ad*",Prueba,1,FALSO) me devuelve el valor ad* lo que parecería que la funcion funciona correctamente [...] Yo entiendo que cuando se coloca el símbolo * se traduce como multiplicación si solo existen numeros y que cuando existen letras (ad*) el sistema los traduce como "lo que sea", es decir, que puede ser ado,adp,adi,adu,ad8,ad9,... bueno, ya se hizo obvia la idea en general Ahora bien lo que deseo saber es como puedo hacer, para que el sistema me interprete el * como texto, es decir, que si yo busco al palabra "ad*" busque la palabra "ad*" y no se detenga cuando encuentre "adp" por ejemplo, en especial cuando uso el valor FALSO en la función. ...

Efectivamente, este usuario de Excel ha 'sufrido en sus carnes' la desventaja de los comodines estándar de Excel (asterisco y cierre interrogación), ya que además la función BUSCARV es una de las funciones de Excel que admite estos comodines en sus argumentos. Tal cual formulaba su búsqueda:
=BUSCARV("ad*";Prueba;1;FALSO)
La función devolvía el primer valor por defecto de orden que encontraba más parecido, a pesar de indicarle o exigirle una búsqueda exacta con el argumento FALSO.
Sin embargo Excel nos da la solución para salvar este inconveniente, podemos forzarle a que encuentre el texto exacto buscado, tomando el asterisco como un caracter de texto y no como comodín. Esto lo conseguiremos añadiendo antes del comodín una virgulilla o tilde ~, para lo cual deberemos presionar AltGr+4 o bien Alt+Ctrl+4 (recuerda a continuación pulsar otra tecla), hasta conseguir que nuestra función quede:
=BUSCARV("ad~*";Prueba;1;FALSO)
consiguiendo entonces que busque exactamente el texto literal 'ad*'.

Seguir leyendo...

Una variante de suma acumulada en Excel.

Recientemente he contestado a una cuestión de sumas acumuladas en nuestras hojas de Excel, que en sí no tiene nada de complicado, salvo por lo particular en la forma de contruir un rango dinámico asociado a una celda validada. La cuestión que se planteó fue:

...Tengo una tabla de 5 proveedores con sus compras por cada mes del año, quiero que en una columna me sume para el mes que le establezca el importe acumulado de lo comprado hasta ese mes y que se actualice de forma automática para todos ellos. ¿Cómo podría hacerlo? Por ejemplo: enero febrero marzo abril mayo ... diciembre Total acumulado Proveedor 1 12 15 10 5 10 23 Proveedor 2 5 10 3 0 2 14 Si quiero que me dé el acumulado de lo comprado en NOVIEMBRE o en FEBRERO ...

Veamos la tabla en nuestra hoja de cálculo:

haz click en la imagen

Como vemos el trabajo es sencillo, debemos acumular las cantidades para cada proveedor desde enero hasta el mes desplegado en la celda A1. La fórmula final será entonces:
=SUMA($B4:INDIRECTO(DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1)))
La explicación o la clave de esta fórmula es la construcción de un rango dinámico en función al valor de una celda.
Fijémosnos que con COINCIDIR($A$1;$B$3:$M$3;0)+1 obtendríamos el número de la columna donde se ha encontrado el mes buscado, por eso sumanos 1 a COINCIDIR, para salvar que en la primera columna A se encuentra el nombre del proveedor.
Con este número de columna, y la función FILA() que identifica en qué número de fila nos encontramos, obtenemos la referencia de la celda hasta donde queremos acumular:
DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1)
devolvería, para el caso del ejemplo la referencia: $F$4.
Finalmente, sólo nos queda que Exccel reconozca ese valor como una referencia, asi que le aplicamos INDIRECTO, que unido a la celda $B4, tenemos el rango buscado $B4:$F$4.
Sumamos dicho rango, y resolvemos nuestro problema:
=SUMA($B4:INDIRECTO(DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1)))

Seguir leyendo...

El criterio O (Or) en SUMA de Excel.

Estamos muy acostumbrados a trabajar con varias condiciones que deben verificarse al mismo tiempo, es decir, a aplicar criterios tipo Y (And); hoy veremos una forma de trabajar con sumas en caso de querer obtener unresultado condicionado al criterio O (Or).
Como solicitaba un lector:

...se usa un anidamiento de funciones SI() para producir el mismo efecto que si encadenáramos condiciones con Y() que posiblemente no funcionen en un esquema matricial. ¿Cuál sería la sintaxix de SUMA.SI() matricial para producir el efecto de O() en la condición, o sea, que la condición se considere verdadera cuando cualquiera de dos o tres términos sea verdadero? Gracias....

Partiremos de una tabla de datos con campos Edad, Peso y Kcal necesarias, y queremos obtener el total de Kcal necesarias para personas con una Edad inferior a 27 o un Peso mayor de 65 kilos:

La fórmula matricial que buscamos es:
{=SUMA(SI(A2:A7>27;C2:C7))+SUMA(SI(B2:B7<65;C2:C7))}
o también
{=SUMA(SI(A2:A7>27;C2:C7);SI(B2:B7<65;C2:C7))}
que en la práctica es como si se calcularan las sumas por separado, como se desglosa en la imagen:

Seguir leyendo...

La función DESVEST.P sujeta a una condición en Excel.

Abordaremos hoy una cuestión estadística, que en más de una ocasión me han planteado; en concreto hablaré sobre la opción de trabajar sobre muestras (entendiendo muestra como parte de la población o rango de datos en Excel) con funciones estadísticas de Excel, esto es, trabajar con rangos condicionados con ciertas funciones estadísticas, en este caso sobre la función DESVEST.P.
Veamos la cuestión planteada:

...preguntaba si existe alguna forma para calcular, aparte del promedio, la desviación estándar "condicionada". Es decir, de un rango de datos que cumplan ciertas condiciones...

Supongamos un rango de celdas con un registro o lectura de datos de toda una población, pero deseamos obtener cierta información exclusivamente para la muestra de datos 'sesgada' que cumpla cierta condición. Partimos de un ejemplo de datos:

Trataremos de obtener la desviación estándar unicamente para los valores del rango A1:A6 que cumplan la condición de ser mayores a 10.
Si aplicamos la función DESVEST.P al rango A1:A6
=DESVEST.P(A1:A6)
obtendríamos la desviación estándar de todo el rango, entendiendo el rango como la población total a estudio. Esta función omite los valores lógicos y de texto. Igual proceder si los datos correspondieran ya de inicio a una muestra de datos, pero trabajando con la función DESVEST.M.
Recordemos que la desviación estándar es la medida de la dispersión de unos valores con respecto al promedio (= media).

Seguir leyendo...

Validacíon de datos vinculada por aproximación en Excel.

Se me planteó una cuestión hace algunos días en la que se necesitaba una adaptación de una Validación de datos, vinculada a otra anterior en nuestro hoja de Excel. En esta ocasión existía la peculiaridad que diferentes elementos de la primera validación nos debían llevar en la segunda a los mismos listados.
Leamos el planteamiento del lector:

...Quiero usar dos listas de validación de datos a efectos de contabilidad. La primera incluye los posibles movimientos, por ejemplo: gasto de efectivo, gasto banco, ingreso de efectivo e ingreso banco. En la segunda columna quiero que la lista desplegable sea condicional en función de si la primera columna se refiere a ingresos o gastos con independencia de que sea de efectivo o bancario (tipos de ingresos o tipos de gastos). Hasta ahora empleaba una fórmula de la siguiente forma: =SI(O(K179=$C$6;K179=$C$7);$A$109:$A$116;SI(O(K179=$C$1;K179=$C$2);$A$123:$A$166;0)) El problema es que al ir incluyendo categorías para la primera columna la fórmula se hace enorme y no entra en la casilla de validación de datos. Necesitaría alguna formula que verificara que K179 es igual a algún valor de un rango para ahorrarme los fórmulas O de muchos parámetros...

El objetivo parece claro, debemos minimizar y abarcar al tiempo el mayor número de tipos de ingresos/gastos. Trabajaremos bajo la hipótesis que los elementos de la primera validación siempre contendrán un texto tipo 'ingreso' o 'gasto' según el tipo de movimiento que se trate. En el ejemplo planteado: 'gasto de efectivo', 'gasto banco', 'ingreso de efectivo' e 'ingreso banco'

Observemos el planteamiento, disponemos en el rango A1:A5 los tipos de movimientos, que irán a la primera celda de Validación; y tenemos dos rangos más C1:C6 y E1:E4 para la segunda celda validada condicionada al resultado de la primera. Para trabajar de manera más cómoda, y que pueda funcionar correctamente, hemos asignado nombre a esos rangos:
gasto =Hoja1!$E$2:$E$4
ingreso =Hoja1!$C$2:$C$6
tipo =Hoja1!$A$2:$A$5
En la celda G2 configuramos la primera validación de datos, como tantas veces hemos hecho ya, sobre el rango llamado 'tipo'. Desde la Ficha Datos > Herramientas de datos > Validación de datos:

Seguir leyendo...

La función de Excel PROMEDIO.SI.

Veremos hoy un par de formas de calcular el PROMEDIO de un rango de celda en Excel obviando alguno de los elementos que lo componen. En concreto esta cuestión me la han planteado ya varias veces,por lo que he decidio subir una explicación de como obtener un resultado válido.
La petición de ayuda dice:

...podrian ayudar con alguna formula para hallar el promedio de 4 notas pero no sumar la nota menor, ejemplo 14 20 21 16 Por favor alguna formula para hallar lo que les menciono lineas arriba...

El fin está claro, obtener el PROMEDIO de un rango sin tener en cuenta el menor de los valores.
Supongamos un rango A1:A4 con los valores del ejemplo propuesto:

Como vemos una de las posibilidades más sencillas para obtener la media aritmética, esto es el PROMEDIO, sólo de los valores más altos, obviando el menor de ellos; para nuestro ejemplo la media de 20, 21 y 16, sería aplicar la siguiente función:
=PROMEDIO.SI(A1:A4;"<>"&K.ESIMO.MENOR(A1:A4;1))
con la que conseguimos que sólo tenga en consideración aquellos valores del rango A1:A4 diferentes del menor valor de ese mismo rango.

Podemos observar que esta función es la manera más sencilla para alcanzar nuestra meta, pero existen otras formas, por ejemplo, empleando una función matricial (Ctrl+Mayusc+Enter):
{=PROMEDIO(SI.ERROR($A$1:$A$4*SI($A$1:$A$4=K.ESIMO.MENOR($A$1:$A$4;1);"";1);""))}

Seguir leyendo...

La función de Excel BUSCAR.

Responderé hoy a la cuetión planteada por un lector a través de los comentarios del blog. Me parece interesante por aprender a usar una función de Excel algo olvidada, pero muy práctica; hablo de la función BUSCAR en su forma vectorial.
La cuestión dice:

...Tengo las columnas A (con porcentajes: 1.00, 1.20, 1.40, 1.60, 1.80, 2.00), B (con valores: 500, 1000, 2000, 3000, 4000, 5000) y C (con valores 999, 1999, 2999, 3999, 4999, 5999). Los datos empiezan desde la fila 5. Cuando en la celda D1 tenga un valor entre 501 y 998, que ese valor se multiplique por 1% y el resultado lo coloque en la celda F1 Cuando el valor de D1 esté en el Segundo rango, o sea entre 1000 y 1999, que lo multiplique por 1.20% y el resultado igualmente lo coloque en F1 Y así sucesivamente cuando el valor de D1 vaya cambiando, lo multiplique por los porcentajes de la columna A y los resultados se vayan actualizando en la celda F1...

Empezaremos viendo cuál es la estructura de nuestra función BUSCAR vectorial:
=BUSCAR(valor_buscado; vector_comparación; [vector_resultado])
Esta función BUSCAR en su formato vectorial realiza la búsqueda un valor en un rango de una columna o una fila (denominado vector) y devuelve un valor desde la misma posición en un segundo rango de una columna o una fila. El uso de esta función está pensado para cuando deseemos especificar un rango que incluya los valores que desea buscar.
Como precaución debemos saber que los valores del argumento vector_comparación se deben colocar en orden ascendente, ya que de lo contrario, la función BUSCAR podría devolver un valor incorrecto. Además, si la función BUSCAR no puede encontrar el valor_buscado, la función muestra el valor más grande en vector_comparación que es menor o igual al valor_buscado.

Trabajemos sobre el ejemplo planteado por el lector:

Seguir leyendo...