OBTENER VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ

Hola a todos!

El post de hoy surge como respuesta a una consulta de un lector, dice así:

Tengo el siguiente cuadro de inventario:
Columna A códigos que se repiten en varias oportunidades, podrían estar ordenados o no.
Columna B cantidad de unidades que trae una caja de ese producto.
Columna C cantidad de cajas del producto.
Necesitaría obtener el valor de B, pero del valor mas alto de C.
O sea:
A1 = 11809 ; B1 = 10 ; C1 = 2
A2 = 11809 ; B2 = 5 ; C2 = 5
A3 = 11809 ; B3 = 30 ; C3 = 1
El resultado debería ser igual a 11809 = 5

Este ejercicio se puede resolver con código o con fórmulas, para este caso la solución se la envié en forma de función, en realidad una combinación de varias.

obtener-valor-de-una-celda-segc3ban-el-valor-maximo-de-una-matriz

Pues bien, para obtener la solución vamos a comenzar con una fórmula matricial que nos va a permitir detectar el valor máximo de la columna C en función de un ID concreto, por ejemplo el 1321 y cuyo resultado será: 777. La fórmula utilizada es la siguiente (recordad que es matricial):

{=MAX(SI(A:A=F1;C:C))}

Ahora que ya conocemos el número más alto, debemos obtener la fila en la que se encuentra, que es la 6. Para ello vamos a utilizar la siguiente fórmula, COINCIDIR:

{=COINCIDIR(MAX(SI(A:A=F1;C:C));C:C;0)}

Una vez que tenemos este dado, debemos obtener las coordenadas de la celda a la izquierda (B6 o $B$6), para ello debemos añadir la función dirección en la que especificaremos que queremos la dirección de la celda 6 en la columna 2, es decir B6:

{=DIRECCION(COINCIDIR(MAX(SI(A:A=F1;C:C));C:C;0);2)}

Finalmente, debemos obtener el valor de esta celda (62), para ello solo nos queda una última función, INDIRECTO:

{=INDIRECTO(DIRECCION(COINCIDIR(MAX(SI(A:A=F1;C:C));C:C;0);2))}

 

El resultado final es el siguiente:

obtener-valor-de-una-celda-segc3ban-el-valor-maximo-de-una-matriz_1

Y finalmente lo hemos conseguido, el resultado es el que el lector ha indicado y funciona perfectamente. Hemos combinado 5 funciones para crear una que resuelve nuestra necesidad.

Recordad que para introducir las fórmulas matriciales debemos seleccionar la fórmula y presionar: CTRL + MAYUS + ENTRAR

Y esto es todo, espero que os sea de utilidad : )

Descarga el archivo de ejemplo pulsando en: OBTENER VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ 

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.


Donate Button with Credit Cards
¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

USO COMBINADO DE LAS FUNCIONES SUMAPRODUCTO E IGUAL, DISCRIMINAR MAYÚSCULAS Y MINÚSCULAS.

Hola  a todos:

Hoy seguiré hablando sobre fórmulas, aunque muchas de las consultas que me envían son sobre programación, lo cierto es que también me llegan muchas dudas sobre fórmulas y cómo utilizarlas, por lo que creo que es interesante darle visibilidad para que todos nos podamos beneficiar : )

Esta es la consulta que me enviaban:

“Hola, tengo una consulta. El countif no diferencia entre mayusculas y minusculas. Si tengo el nombre “CASA” y en otra celda “casa” dirá que es duplicado pero yo necesito que lo diferencie. Alguna idea?

muchas gracias”

 

Pues bien, el tema de la discriminación de mayúsculas y minúsculas cuando usamos fórmulas suele ser bastante recurrente y en el caso que nos ocupa, el lector necesita contar y por lo tanto diferenciar las minúsculas y mayúsculas.

Para solucionar esto, tenemos varias posibilidades y aunque algunas de ellas requieren utilizar columnas auxiliares (algo que suelo utilizar solo cuando no queda más remedio), lo más eficiente es trabajar con una única celda.

Vamos con un ejemplo:

USO COMBINADO DE LAS FUNCIONES SUMAPRODUCTO E IGUAL

En la columna C debemos contar las veces que se repite “cultura y ocio” en minúscula. Pues bién para hacerlo utilizaremos la combinación de la función SUMAPRODUCTO e IGUAL:

=SUMAPRODUCTO(IGUAL($C$2:$C$64980;"cultura y ocio")*1)

También se podría omitir la multiplicación del 1 del final (necesario para pasar los datos a número de la función sumaproducto, utilizando el siguiente operador “–“:

=SUMAPRODUCTO(--IGUAL($C$2:$C$64980;"cultura y ocio"))

En ambos casos el resultado es de “3”

Y si necesitamos introducir otra condición, también lo podemos hacer. Por ejemplo las veces que se repite “cultura y ocio” y el sexo es MUJER:

=SUMAPRODUCTO(IGUAL($C$2:$C$64980;"cultura y ocio")*1;(IGUAL($E$2:$E$64980;"MUJER")*1))

O también:

=SUMAPRODUCTO(--IGUAL($C$2:$C$64980; "cultura y ocio");--(IGUAL($E$2:$E$64980;"MUJER")))

El resultado es 1 en los dos casos.

USO COMBINADO DE LAS FUNCIONES SUMAPRODUCTO E IGUAL1

Os dejo el ejercicio con las fórmulas utilizadas. Por cierto, para mostrarlas, he utilizado la función =formulatexto() que sirve para mostrar las fórmulas que contiene una celda.

Descarga el archivo de ejemplo pulsando en: USO COMBINADO DE LAS FUNCIONES SUMAPRODUCTO E IGUAL

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

USO COMBINADO DE LA FUNCIÓN INDICE Y COINCIDIR

Hola a todos!

Hoy toca formulación!, No todo van a ser macros y programación : )

Hace unos días me enviaron una consulta en la que solicitaban una fórmula para mostrar cierta información.

Os mostraré un ejemplo de sencillo de lo que se buscaba y las fórmulas que utilicé. Primero veamos los datos y que se necesita buscar:

USO COMBINADO DE LA FUNCIÓN INDICE Y COINCIDIR

Como podéis observar, necesitamos mostrar en la columna “B” la fecha que aparece en fila 3, pero en función de la coincidencia del importe que aparece en la columna A. Es decir, en la celda B6 debe aparecer la fecha: “2015”, en la B7: “2016” y así en adelante.

Para hacer esto (se puede hacer de varias formas), os propongo utilizar las funciones COINCIDIR e INDICE.

Con la primera podemos extraer el número de columna en la que se encuentra el dato buscado:

=COINCIDIR(A6;C6:J6;0)

El resultado sería “5”

Con la segunda, vamos a ser capaces de localizar la fecha, debemos analizar su sintaxis para comprenderlo mejor:

Indice (matriz, número de fila, número de columna).

Matriz: sería C3:J3
Fila: la 1
Columna: COINCIDIR(A6;C6:J6;0)

Ambas funciones combinadas darían lugar a esta fórmula:

=INDICE($C$3:$J$3;1;COINCIDIR(A6;C6:J6;0))

Y el resultado es el esperado:

USO COMBINADO DE LA FUNCIÓN INDICE Y COINCIDIR1

Como podéis observar es una función bastante sencilla pero muy eficiente utilizando ambas fórmulas.

Os dejo el archivo para lo probéis : )

Descarga el archivo de ejemplo pulsando en: USO COMBINADO DE LA FUNCIÓN INDICE Y COINCIDIR

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

SUMAR LA PARTE ENTERA DEL CONTENIDO DE UN RANGO CON NÚMEROS DECIMALES

Hola a todos!:

Respondiendo a dos consultas, hoy publicaré la respuesta a la más sencilla de las dos y mañana intentaré responder a la segunda.

La pregunta que me trasladan es cómo se pueden sumar todos los enteros de un rango de números decimales. Por ejemplo este rango de números:

SUMAR LA PARTE ENTERA DEL CONTENIDO DE UN RANGO CON NÚMEROS DECIMALES

El resultado de esta suma es: -16,89539, pero a nosotros nos interesa la suma de sus enteros, es decir, 2 + 3 +4 + 6 etc donde el resultado debería ser 18

Para realizar el ejercicio con una fórmula y que contemple todo el rango podemos utilizar o bien una fórmula matricial o bien otra (que no deja de ser otra matricial pero muy especial).

Lo podemos hacer con está fórmula: {=SUMA(TRUNCAR(A2:A10))}  como ya sabéis  las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

SUMAR LA PARTE ENTERA DEL CONTENIDO DE UN RANGO CON NÚMEROS DECIMALES2

Como podéis observar, realiza la suma correctamente y tan solo tenemos que combinar las funciones SUMA y TRUNCAR y activarlas matricialmente.

La segunda forma de hacerlo es utilizando la función sumaproducto combinada con la función TRUNCAR. No será necesario usar matrices ya que la función sumaproducto se expresa matricialmente:

SUMAR LA PARTE ENTERA DEL CONTENIDO DE UN RANGO CON NÚMEROS DECIMALES3

Y efectivamente aquí tenemos el mismo resultado.

Es importante diferenciar las funciones ENTERO y TRUNCAR, que aunque  parece que se obtiene el mismo resultado, la primera redondea con decimales y el resultado podría no ser preciso para esta tarea.

En el próximo post trataré de realizar la inversa, es decir, sumar la parte decimal de un número decimal : )

Descarga el archivo de ejemplo pulsando en: SUMAR LA PARTE ENTERA DEL CONTENIDO DE UN RANGO CON NÚMEROS DECIMALES

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

DETECTAR VALOR DE LA PRIMERA Y ÚLTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

Hola a todos!

Hace unos días recibí una consulta en la que me solicitaban el poder capturar el valor de una celda en función de si la celda de la fila inferior tenía datos.

Por ejemplo, imaginad que tenemos dos filas, en la primera unas fechas y en la segunda unos importes, pues bien necesitamos extraer las fechas de la primera y última celda con datos en la fila de los importes:

DETECTAR VALOR DE LA PRIMERA Y ULTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

Para resolver esta necesidad, (que con una macro sería muy sencillo), con una fórmula lo podemos hacer con una función matricial.

Para extraer la primera fecha, el 3 de enero:

{=DESREF(INDICE(B2:O2;COINCIDIR(FALSO;(B2:O2="");0));-1;0)}

Y para extraer la última fecha, el 11 de enero:

{=DESREF(INDICE((B2:O2);;MAX(SI((B2:O2)<>"";COLUMNA(B2:O2)))-1);-1;0)}

En ambos casos, primero detectamos tanto el valor inicial o el final, sin tener en cuenta las celdas vacías, esto nos dará el número de columna, luego simplemente usamos la función desref para indicar que queremos la fila anterior (-1).

El resultado es el siguiente:

DETECTAR VALOR DE LA PRIMERA Y ULTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR1

Como podéis comprobar, las fórmulas funcionan perfectamente.

Y eso ha sido todo, espero que os resulte de utilidad:

Descarga el archivo de ejemplo pulsando en: DETECTAR VALOR DE LA PRIMERA Y ÚLTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

CONTAR PALABRAS EN EXCEL

Hola a todos!

Ayer un lector me pedía un fórmula para poder contar palabras en Excel. Supongo que era para delimitar en un informe la cantidad de palabras que un usuario podía escribir.

Lo cierto es que es muy común delimitar de alguna forma el espacio de escritura en los informes, aunque suele hacerse por caracteres escritos y no tanto por palabras, sin embargo también es posible hacerlo.

Para conseguirlo, y sin necesidad de acudir a la programación, tan solo necesitaremos usar la combinación de varias fórmulas:

=SI(LARGO(A2)=0;0;LARGO(ESPACIOS(A2))-LARGO(SUSTITUIR((A2);" ";""))+1)& " palabras"

Con esta función, donde combinamos las funciones LARGO y SUSTITUIR será muy sencillo. Contamos todos los caracteres en nuestra frase (incluidos los espacios en blanco) y le restamos los mismos caracteres pero sin esos espacios en blanco, la diferencia es el número de palabras. Usaremos un condicional para en caso de no existir palabras nos ponga un “0”.

El resultado de la fórmula es este:

CONTAR PALABRAS EN EXCEL

Descarga el archivo de ejemplo pulsando en: CONTAR PALABRAS EN EXCEL

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

PASAR INFORMACIÓN DE UN RANGO A UNA CELDA USANDO FÓRMULAS

Hola a todos!.

El post de hoy va a tratar sobre cómo podemos pasar el contenido de un rango a una celda usando solo fórmulas y sin recurrir a VBA.

En un post anterior os comentaba la forma de hacerlo con macros: PASAR INFORMACIÓN DE UN RANGO A UNA CELDA

Y también recordar que con las últimas actualizaciones de Excel (pero con una suscripción a Office 365) esto se puede realizar fácilmente con la función UnirCadenas.

Pero este post está pensado para esas situaciones de emergencia, cuando no tenemos tiempo ni para programar ni tampoco acceso a office 365 (por los motivos que sean).

Veamos un ejemplo, en la columna A tenemos un texto que está distribuido en diferentes celdas:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS

La idea es pasar la información a una única celda y además dejarla colocada con la misma estructura en vertical añadiendo saltos de línea.

Necesitaremos una celda auxiliar para realizar nuestro trabajo, en la que vamos a usar la función concatenar y seleccionar el rango con los datos:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS1

A continuación, seleccionamos la fórmula y pulsamos F9 en el teclado. El resultado es que se mostrarán los datos que se han añadido a la matriz de la función:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS2

Ahora lo que tenemos que hacer es eliminar el igual = y los paréntesis { } y nos quedará una cadena de texto con la información lista para tratar con una última fórmula:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS3

Efectivamente, con la última fórmula utilizamos una función anidada con “sustituir”:

=SUSTITUIR(SUSTITUIR(B9;CARACTER(34);" ");";";CARACTER(10))

En la fórmula inicial, sustituimos las comillas dobles CARACTER(34), por un espacio y en la segunda (la anidada), sustituimos el punto y coma por un salto de línea CARACTER(10).

El resultado es el que podéis ver, hemos pasado la información de un rango a una celda e incluso hemos dejado el mismo formato.

Aunque no sea un proceso limpio con una sola función o usando macros, estoy seguro que en algún momento os ayudará en alguna situación apurada (a mí me ha ayudado en más de una ocasión).

No dejaré archivo de prueba porque se trata de un proceso semiautomático.

Y eso es todo!, hasta el próximo post 🙂

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!