funciones y form

JLD EXCEL EN CASTELLANO - USAR MICROSOFT EXCEL EFICIENTEMENTESugerencias y ayuda para Excel en español. Ejemplos de funciones y fórmulas, gráficos y automatización de hojas de cálculo con Vba.

Inicio Acerca de JLD Excel Ayuda Consultoría Apoyar JLD Excel

Anuncios Google ► Excel 2007 uso ► Excel ► MS Excel macro

Autofiltro BUSCARV Calendario COINCIDIR Comparar Listas Consolidar Datos CONTAR.SIControles Dashboards DESREF Eventos Excel 2007 Excel 2010 Excel 2013 Fechas y horaFormato Condicional Funciones y Formulas Gantt Graficos Hipervínculos

Importar Datos INDICE Listas Desplegables Macros MS Query Muestra aleatoria Objetos ParetoPDF rangos dinámicos SIFECHA SUBTOTALES SUMAPRODUCTO Tablas DinamicasTablas/Listas Tips Validacion de Datos Varios

Encontrar el primer número positivo o negativo en el rangoDOMINGO, MARZO 03, 2013

En el pasado hemos tratado el tema de encontrar el último valor en un rango y también elúltimo positivo o negativo.

Para encontrar el primer valor negativo en un rango podemos usar esta fórmula:

=INDICE($A$2:$A$15,COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0))

ARCHIVO DELBLOG

Archivo del Blog

TRADUCIR ESTA PÁGINA

BUSCAR EN EL BLOG

Entre su e-mail para suscribirse:

Delivered by FeedBurner

Suscribirse a un lector de feeds

Anuncios Google ► Macros ► Formulas 1 ► VBA I Excel ► Access VBA

Mostrando las entradas con la etiqueta Funciones y Formulas. Mostrartodas las entradas

Página 1 de 38JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Funciones y Formulas

16/03/2013http://jldexcelsp.blogspot.mx/search/label/Funciones%20y%20Formulas

Para hallar el primer número positivo en el rango usamos

=INDICE($A$2:$A$15,COINCIDIR(VERDADERO,INDICE($A$2:$A$15>0,0,1),0))

Sencillamente invertimos el signo "<" a ">"

La fórmula funciona de esta manera:

La expresión INDICE($A$2:$A$15<0,0,1) genera un vector de valores VERDADERO oFALSO

Luego COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0) nos da la ubicación de laprimera aparición de VERDADERO en el vector

El resultado lo usamos como argumento en la función INDICE "externa"

ESTADÍSTICAS



obteniendo así el resultado

Podemos, también, obtener la dirección de la celda que contiene el valor combinando lafunción COINCIDIR con la función DIRECCION

=DIRECCION(COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0)+1,1)



Posted by Jorge L. Dunkelman at 1:25 p.m. , Links to this post

, 1 comments

Labels: Funciones y Formulas

Registrar entradas con fórmulas – una aclaraciónVIERNES, OCTUBRE 26, 2012

En la nota sobre registro de entradas con fórmulas en Excel, sugerí el uso de Tablas paraevitar tener que copiar las fórmulas con cada entrada.

Sin embargo, y como me señalan varios de mis lectores, el uso de tablas en este casopuede dar resultados inesperados. Veamos:

Enviar esto por correo electrónicoBlogThis!Compartir en TwitterCompartir en Facebook Recomendar esto en Google



Al introducir el segundo nombre nos hemos movido con las flechas y el resultado es 0, quepor el formato fecha-hora vemos como 00/01/1900 00:00:00.

Sin embargo, si nos movemos con la tecla TAB veremos que el resultado es el correcto:

En conclusión: podemos usar un rango común de Excel, copiando las fórmulas o convertir elrango en Tabla y movernos dentro de esta con la tecla TAB.


, 0 comments

Labels: Funciones y Formulas, Tablas/Listas

Registrar fecha y hora de una entrada en Excel con fórmulasMARTES, OCTUBRE 23, 2012

Poniéndome al día con los 196 mails acumulados durante mis vacaciones, me encuentro conesta consulta:

quiero capturar la hora y la fecha del momento que ingresar este información, la funciónHOY() y AHORA() no sirven por que estas se actualizan cada vez que ingreso datos en lahoja; es necesario para mi que estas queden estáticas para realizar posteriores cálculos yestadísticas con ellas…

Mi primera respuesta fue que la única forma de hacerlo es programando un evento (macro).




Pero después de investigar un poco el tema descubrí (y muchos otros antes de mi) quepuede hacerse con fórmulas.

Para hacerlo con fórmulas tenemos que crear una referencia circular, tema mencionadotangencialmente en la prehistoria de este blog en esta nota.

En pocas palabras, ¿qué es una referencia circular? Cuando una fórmula incluye unareferencia a la misma celda que la contiene o una celda que se refiere a ésta, Excel generauna advertencia de "referencia circular".

En este ejemplo, queremos calcular la ganancia neta que incluye el pago de comisiones quea su vez son calculadas en base a la ganancia neta, creándose así una referencia circular (lacelda B3 contiene la fórmula =B4*15% y la celda B4 la fórmula =B1-B2-B3 que se refiere ala celda B3)

Al apretar Aceptar veremos



Para que Excel pueda resolver el cálculo tenemos que habilitar el cálculo iterativo enOpciones de Excel-Fórmulas

Después de habilitar el cálculo iterativo Excel muestra el resultado

Una vez definido el cálculo iterativo podemos usar esta fórmula para crear una registro defecha y hora ("timestamp" en inglés)

=SI(A2<>"",SI(B2="",AHORA(),B2),"")



Para evitar tener que copiar la fórmula a lo largo de la columna A, transformamos el rangoen Tabla (en un rango definido como Tabla las fórmulas y formato de las columnas soncopiadas automáticamente)

26/10/2012 - Aclaración importante sobre el uso de Tablas en este modelo.

En la próxima nota veremos como hacerlo programando un evento (macros).

Posted by Jorge L. Dunkelman at 9:30 a.m. , Links to this post

, 14 comments

Labels: Funciones y Formulas, Tablas/Listas

Enviar esto por correo electrónicoBlogThis!Compartir en TwitterCompartir en Facebook +1 Recomendar esto en Google



El extraño caso del espacio inamovible en Excel (o ASCII 160)MIÉRCOLES, OCTUBRE 17, 2012

Cuando importamos datos a una hoja de Excel, en particular de una base de datos o de laWeb, las celdas pueden contener, además del valor visible, un espacio o algún carácter noimprimible.

La presencia de estos caracteres crea varios problemas: los números son interpretados porExcel como texto o los resultados de filtrar u ordenar resultan imprevisibles.Excel nos provee con dos funciones para enfrentarnos con estos problemas: ESPACIOS() yLIMPIAR().

ESPACIOS() remueve todos los espacios excepto los espacios individuales entre palabras.Pero no siempre es así. Veamos este ejemplo

La celda A2 contiene tres caracteres visibles (abc) pero la función LARGO da un valor de 4.Este se debe a que la celda contiene un espacio en blanco después de "c".

En la celda A3 usamos ESPACIOS() para remover el espacio y vemos que ahora el largo esde 3.

Ahora veamos este caso, aparentemente idéntico



A pesar de que ambos casos parecen idénticos, en este segundo caso LIMPIAR no lograremover el espacio en blanco. Esto se debe a que la función LIMPIAR fue diseñada pararemover el carácter de espacio de 7-bit ASCII (valor 32). Pero ciertos programas, y enespecial datos provenientes del Web usan el carácter ASCII 160. Este carácter se utilizacomúnmente en las páginas Web como la entidad HTML. La función LIMPIAR no quita estecarácter de espacio.

Una solución es usar la función SUSTITUIR()

=SUSTITUIR(A2;CARACTER(160);"")

Si se trata de un número (que ha sido convertido en texto por la presencia del espacio),agregamos un doble signo menos (--) al principio de la fórmula para forzar la conversión anúmero

El uso de la función SUSTITUIR puede ser menos conveniente cuando tenemos queocuparnos de un gran número de registros. En esos caso es mejor usar una macro comoésta

Sub limpiar_todo()

Selection.Replace What:=Chr(160),Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows,MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False



End Sub

Esta macro quita todos los espacion generados por el carácter 160 en el rangoseleccionado. Si queremos quitar todos los espacios en la hoja usamos

Sub limpiar_todo()

Cells.Replace What:=Chr(160),Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows,MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

End Sub


, 4 comments

Labels: Funciones y Formulas, Macros

Búsquedas con COINCIDIR en varias columnasMIÉRCOLES, SEPTIEMBRE 26, 2012

A partir de mañana y por los próximos diez estaré ocupado en una investigación sobre eltema “Los mejores platos y vinos en restoranes no turísticos de Toscana” (es decir, metomo vacaciones en esa bellísima zona de Italia).

Mientras tanto veamos un tema sobre el cual he recibido varias consultas últimamente:realizar búsqueda usando COINCIDIR a través de varias columnas.

Para calcular qué posición ocupa un elemento determinado en un rango, Excel nos proveecon la función COINCIDIR. Pero si el rango de búsqueda comprende más de una columna (ofila), la función da un resultado de error.

Para el caso supongamos que tenemos una serie de valores (números o texto) en el rangoD1:E10. Para saber que posición ocupa el valor “14” nos veremos tentados a usar lafórmula

=COINCIDIR(B2,D1:E10,0)

donde B2 contiene el valor de búsqueda. Si bien 14 ocupa el quinto lugar en la segundacolumna, el resultado es #N/A




La función COINCIDIR funciona sólo con rangos de búsqueda (matrices) de una únicacolumna o fila.

Para hacer la búsqueda a través de varias columnas podemos combinar COINCIDIR con lasfunciones SI y ESERROR o, si usamos Excel 2007 o 2010, la nueva función SI.ERROR deesta manera

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),"inexistente"))

Si la búsqueda debe hacerse en tres columnas agregamos otra función SI.ERROR

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),SI.ERROR(COINCIDIR(B2,F1:F10,0),"inexistente")))

En Excel Clásico (97-2003) puede hacerse combinando Si con ESERROR, pero la nuevafunción SI.ERROR nos permite crear una fórmula mucho más compacta.

Uno de los inconvenientes de esta solución es que no nos dice en qué columna se encuentrael valor. Además, nos da la ubicación relativa del elemento en la matriz de búsqueda, peropor lo general queremos saber en qué fila se encuentra el elemento.



Para calcular la dirección de la celda que contiene el elemento buscado podemos usarDIRECCION combinada con la solución anterior

=SI.ERROR(DIRECCION(COINCIDIR(B2,D1:D10,0),4),SI.ERROR(DIRECCION(COINCIDIR(B2,E1:E10,0),5),"inexistente"))

Otra alternativa es crear una UDF (función definida por el usuario) como ésta

Function direccion_celda(Valor_Buscado, Matriz_Busqueda As Range)

Dim rngCell As Range

For Each rngCell In Matriz_Busqueda

If rngCell.Value = Valor_Buscado Then

direccion_celda= rngCell.Address

Exit Function

Else

direccion_celda= "inexistente"

End If

Next rngCell

End Function


, 8 comments

Labels: COINCIDIR, Funciones y Formulas, Macros




EL extraño caso del signo más (+) en Excel.DOMINGO, AGOSTO 26, 2012

Excel tiene ciertas zonas un poco tenebrosas donde no todo funciona, o parece funcionar,como esperamos. Pero el usuario avisado puede evitar entrar en esos oscuros callejones…

Vimos el caso de los resultados incorrectos de CONTAR.SI. Hoy vamos a ver ciertocomportamiento extraño del símbolo “más” (+).

Todos sabemos que si iniciamos una fórmula con el “más” (+) Excel lo transformaautomáticamente en “igual” (=). El símbolo + era el usado en Lotus 1-2-3, el antecesor deExcel.

Por ejemplo, si introducimos en una celda “+10/2.5”, veremos en la barra de fórmulas queExcel lo transforma en “=10/2.5” y el resultado es 4

Ahora veamos que pasa con esta operación cuando la celda tiene un formato distinto alGeneral



Excel graciosamente nos informa que hay un error en la fórmula. No voy a intentar explicareste fenómeno por la sencilla razón que no tengo la menor idea por qué sucede. Pero sipuedo exponer la solución.

En Excel 2010 activamos Archivo-Opciones-Avanzada y al final del formulario señalamos laopción “Introducción de fórmulas de transición” y apretamos Aceptar.



En Excel 2007

En Excel 2003


, 7 comments

Labels: Funciones y Formulas, Varios




Ordenar listas numéricas con fórmulas en ExcelSÁBADO, ENERO 14, 2012

Nada más sencillo que ordenar listas o tablas en Excel. Un clic al icono correspondiente(ascendente o descendente) y Excel ordena la lista. Pero en ciertas situaciones queremosque la lista se ordene automáticamente al ir agregando o quitando datos.

Podemos programar un evento que se ocupe de ordenar nuestra lista con cada cambio,pero también podemos hacerlo con fórmulas. En esta nota veremos cómo hacerlo en el casode listas numéricas (cómo hacerlo con listas de texto he mostrado en esta nota).

Supongamos un rango donde vamos agregando fechas (recordemos que la fechas sonnúmeros en Excel)

Como puede verse estoy usando ALEATORIO.ENTRE para generar fechas en formaaleatoria.

Para ordenar esta lista en orden ascendente usamos esta fórmula

=K.ESIMO.MENOR(lstFechas,FILA()-1)



donde “lstFechas” es un nombre que define el rango de las fechas en forma dinámica, conla fórmula

=fechas!$A$2:INDICE(fechas!$A:$A,CONTARA(fechas!$A:$A))

Para ordenar la lista en orden descendente usamos esa otra fórmula

=K.ESIMO.MENOR(lstFechas,CONTARA(lstFechas)-FILA()+2)



Una desventaja de este método es que cada vez que agregamos un valor a lista debemoscopiar las fórmulas. Podemos superar este inconveniente convirtiendo el rango en “tabla”

Al convertir el rango en tabla, las fórmulas son copiadas automáticamente.


, 5 comments

Labels: Funciones y Formulas, rangos dinámicos

Cálculo de semanas en ExcelMIÉRCOLES, OCTUBRE 19, 2011

Excel no tiene una función que calcule la cantidad de semanas entre dos fechas. Paracalcular la cantidad de años, meses y días podemos usar la “indocumentada” función




SIFECHA.

En esa nota muestro cómo calcular también la cantidad de semanas, pero dado que sigorecibiendo consultas sobre el tema mostraré aquí la técnica en forma sucinta. A quien noesté familiarizado con SIFECHA le recomiendo leer la nota mencionada.

Supongamos que queremos calcular la cantidad de meses, semanas y días entre dos fechas

En la celda B4 usamos la fórmula

=SIFECHA(comienzo,fin,"ym")

donde “comienzo” es un nombre que se refiere a la celda B1 y “final” es un nombre que serefiere a la celda B2.

(aclaración: dependiendo de las definiciones del sistema hay que usar “a” en lugar de “y”para los años)

En B5 ponemos =SIFECHA(comienzo,fin,"md")

Finalmente en B8 usamos =B4&" meses y "&B5&" días"

Excel no incluye el día de comienzo cuando calcula el intervalo, de manera que quienquiera incluirlo en el resultado deberá agregar “+1” a la fórmula.



Nosotros queremos este cálculo

En la celda B5 (semanas) ponemos

=ENTERO(SIFECHA(comienzo,fin,"md")/7)

para calcular la cantidad de semanas de los días no incluidos en la cuenta de los meses.

En la celda B6 ponemos

=SIFECHA(comienzo,fin,"md")-(B5*7)

Es decir, descontamos del total de días calculado por SIFECHA la cantidad de días de lassemanas en la celda B5.

Si queremos una fórmula que no dependa del B5 tendríamos que usar

=SIFECHA(comienzo,fin,"md")-(ENTERO(SIFECHA(comienzo,fin,"md")/7)*7)

La fórmula en B9 es

=B4&" meses, "&B5&" semanas y "&B6&" días"


, 11 commentsEnviar esto por correo electrónicoBlogThis!Compartir en TwitterCompartir en Facebook +1 Recomendar esto en Google



Labels: Funciones y Formulas, SIFECHA

Uso de comodines (wildcards) en funciones de ExcelSÁBADO, AGOSTO 20, 2011

Cuando filtramos tablas o hacemos búsquedas en Excel podemos usar comodines (*, ?).¿Podemos usarlos en las funciones? Bien, sí y no. Es decir, hay funciones que aceptancomodines en los argumentos y otras que no.

Por ejemplo, queremos evaluar si una celda contiene cierto texto. En caso afirmativo elresultado será "A", en caso negativo "B".

Obviamente =SI(A1="*no*";"A";"B") no funciona.

Pero veamos esta alternativa

Como pueden ver, =SI(ESNUMERO(HALLAR("no";A1));"A";"B") no requiere comodines paranuestro ejemplo.

Podemos ver cómo funciona esta fórmula descomponiéndola en sus partes:



La función HALLAR da la primera posición en la cadena de texto del texto buscado

=HALLAR("no";A2)

Si el texto no aparece el resultado el #¡VALOR!

La función ESNUMERO evalúa si el resultado de HALLAR es numérico. Cuando lo es daVERDADERO, cuando no lo es, también si el error, da FALSO. Estos resultados son losargumentos que usamos en SI.

Veamos un ejemplo más elaborado donde buscamos valores que en la segunda posicióncontienen una "n" y en la cuarta posición una "o".

Como ven, en =SI(ESNUMERO(HALLAR("?n?o";A2));"A";"B") estamos usando el comodín"?" para señalar que la primera y la tercera posición puede ser ocupada por cualquier valor,pero la segunda y la cuarta deben ser "n" y "o" respectivamente.

La función COINCIDIR también acepta comodines.



También la función CONTAR.SI. Por ejemplo, en la lista anterior, si queremos contar todoslos nombres que terminan con la letra "o" usamos

=CONTAR.SI($A$2:$A$7;"*o")

De la misma manera SUMAR.SI comodines pero SUMAPRODUCTO no.


, 3 comments


Determinar posición (ranking) por gruposVIERNES, JUNIO 10, 2011

En la nota sobre la función JERARQUIA aparece este comentario




¿Es posible aplicar la función jerarquía a una columna de manera que se reinicie el rankingsi el valor de otra columna cambia? Por ejemplo, tengo 5 vendedores en 4 países y quierosaber el principal vendedor en cada país.

Esta consulta me llevó a reflexionar sobre dos temas:

1 - ¿cómo aplicar la función JERARQUIA en este caso?2 – ¿Por qué aplicar la función JERARQUIA para este caso?

Muy a menudo me encuentro con consultas de este tipo, donde el lector presupone que lasolución pasa por usar una función o un método determinado. En lugar de preguntar"¿cómo determino el mejor vendedor de cada país?" la consulta se transforma en "¿cómouso JERARQUIA para solucionar este problema?

El medio (el uso de la función JERARQUIA) se transforma en el objetivo (encontrar el mejorvendedor de cada país).

Supongo que esta forma de enfrentar la solución de problemas está relacionada con latendencia natural, en mi opinión, de aferrarnos a lo conocido y nuestra aversión o temor alo desconocido. Seguramente hay otros factores, pero no lo trataré en esta nota. Porsupuesto, mis lectores están invitados a opinar sobre el tema.

Y ahora llegó el momento de empezar a trabajar. ¿Cómo solucionamos el problema?Empecemos por mostrar nuestros datos



Solución con JERARQUIA

Para usar la función JERARQUIA por grupos (vendedores de Argentina, vendedores deColombia, etc.) empezamos por definir rangos con nombres

Cada nombre se refiere al rango de valores de ventas correspondiente al país a que serefiere. Nótese que estos rangos no son dinámicos, por lo que se agregamos valores,



tendremos que editar el nombre para redefinir el rango.

Agregamos una columna a la tabla, Ranking, donde ponemos esta fórmula

=JERARQUIA(C2,INDIRECTO(A2))

Hemos agregado además un formato condicional para resaltar el nombre y las ventas delvendedor que recibe la posición 1



Solución con INDICE, COINCIDIR y MAX en forma matricial

La ventaja de esta solución es que nos permite definir rangos dinámicos, librándonos de lanecesidad de editar los rangos definidos cada vez que agregamos o quitamos datos de latabla.

Otra ventaja es que implementamos uno de los principios importantes de buenas prácticasen Excel: la separación de la base de datos de los cálculos y los informes.

Como en el caso anterior, empezamos por definir rangos en nombres (otra buena prácticaen Excel)En este caso definimos rangos dinámicos

pais =indice!$A$2:INDICE(indice!$A:$A,CONTARA(indice!$A:$A))vendedor =indice!$B$2:INDICE(indice!$B:$B,CONTARA(indice!$B:$B))ventas =indice!$C$2:INDICE(indice!$C:$C,CONTARA(indice!$C:$C))

Creamos una tabla separada para mostrar los resultados por país donde ponemos estafórmula matricial (fórmulas que introducimos apretando simultáneamenteCtrl+Mayúsculas+Enter)

=INDICE(vendedor,COINCIDIR(MAX((pais=E2)*(ventas)),ventas,0))



Solución con tablas dinámicas

Empezamos por crear una tabla dinámica con los campos País y Vendedor en el área defilas y Ventas en el área de datos. Luego ordenamos el campo Vendedor según las Ventas

El próximo paso es crear un campo calculado, Ranking (o cualquier otro nombre) con lafórmula "=1"



Seleccionamos el campo que acabamos de agregar y en el menú de configuración delcampo mostramos los valores como "Total en" usando como campo de base "Vendedor"

El resultado es



Como puede apreciarse, no se trata de una verdadera solución ya que nos basta conordenar los valores tal como hicimos en el primer paso. Obviamente, quien aparece en elprimer lugar de cada país es el que más vendió. Esta solución puede ser útil si queremosextraer el vendedor que más vendió (o el segundo o el tercer, etc.) usando funciones quese refieran a la tabla dinámica, como IMPORTARDATOSDINAMICOS.

En este enlace muestro otra forma de aplicar JERARQUIA, es decir señalar el ranking, enuna tabla dinámica.


, 5 comments

Labels: Funciones y Formulas, Tablas Dinamicas

Encontrar el último número positivo o negativo en un rangoLUNES, MAYO 16, 2011

En uno de los proyectos que estoy desarrollando me enfrenté con la necesidad de encontrarel último número negativo de una serie. Específicamente se trataba de calcular el períodode recuperación de una inversión (Payback), pero este problema puede presentarse envarias situaciones.




En el pasado he mostrado cómo encontrar el último elemento en un rango usando lafunción BUSCAR (LOOKUP). Pero en este caso se trata de encontrar el último elemento bajola condición que sea negativo.

Supongamos esta serie de números en el rango B1:B9

Nuestro objetivo es crear una fórmula que de cómo resultado el último número negativo dela serie, en nuestro caso -30

La fórmula que usamos es la siguiente:

=BUSCAR(2;1/(B1:B9<0);B1:B9)



¿Cómo funciona esta fórmula?

=BUSCAR(2;1/(B1:B9>=0);B1:B9)Seguir leyendo...


, 19 comments


Calcular el mínimo con criterios excluyendo cerosSÁBADO, ABRIL 16, 2011

Un lector me consultaba cómo calcular el precio mínimo de los tornillos en esta lista




Para hacerlo tenemos que crear una matriz (array) de valores de la lista que correspondana la categoría tornillos. Esta lista la usamos como argumento en la función MIN paraobtener el valor buscado. La fórmula matricial sería

=MIN((A2:A13="Tornillos")*B2:B13)

Esta es una fórmula matricial que introducimos apretando simultáneamenteCtrl+Mayúscula+Enter.

El problema con esta fórmula es que cuando un valor en la lista no corresponde a lacategoría buscada ("tornillos" en nuestro caso), el valor en la matriz es 0 (cero) y éste pasaa ser el valor mínimo (a excepción de que la lista contenga valores negativos).

La solución es usar la función SI para crear una condición que excluya los ceros. La fórmulamatricial que excluye los ceros del cálculo del mínimo es

=MIN(SI((A2:A13="Tornillos"),B2:B13))

Nótese que excluimos el tercer argumento de la función SI, es decir, ponemos solamente elresultado a calcular cuando la condición se cumple.

Los precios máximos se calculan sin mucho trámite con esta función matricial

=MAX((A2:A13="Tornillos")*(B2:B13))

Ahora vamos a darle un toque un poco más profesional a la solución agregándole la



posibilidad de elegir el tipo de artículo en forma dinámica. Para esto creamos una listadesplegable con validación de datos en la celda E3

Nuestro modelo se ve ahora así

donde hemos modificado las fórmulas de esta manera

=MAX((A2:A13=E3)*(B2:B13))

=MIN(SI((A2:A13=E3),B2:B13))



Ahora, si queremos realmente impresionar al jefe (o mejor aún, a la secretaria) hacemosque también el tipo de valor buscado se establezca dinámicamente.

Agregamos dos columnas a la izquierda de la columna A, donde haremos cálculos auxiliares

En la celda A7 ponemos esta fórmula

=COINCIDIR(G2,A3:A4,0)

En las celdas A9 y A10 ponemos las fórmulas de máximo y mínimo respectivamente.

En G2 ponemos una lista desplegable con validación de datos con dos posibilidades:Máximo y Mínimo.

Finalmente, en la celda G4 ponemos la fórmula

=INDICE(A9:A10,A7)

Ahora, cuando el usuario elige el máximo, la celda A7 recibe el valor 1; si elige el mínimo lacelda muestra el 2. Este valor es usado como argumento en la función INDICE de la celdaG4, que extrae el valor de la celda A9 o A10, según el caso.

El último toque es ocultar la columna A.

El archivo con el ejemplo se puede descargar aquí.


, 15 comments





Se produjo un error en este gadget.SEGUIDORES

Participar en este sitioGoogle Friend Connect

Miembros (391) Más »

¿Ya eres miembro?Iniciar sesión

ESTADÍSTICAS

547 Entradas publicadas a la fecha

Entradas antiguasPágina PrincipalSuscribirse a: Entradas (Atom)

Términos Legales

JLD Excel en Castellano by Jorge Dunkelman is licensed under a Creative CommonsReconocimiento-No comercial-Sin obras derivadas 3.0 España License.

Back to TOP



funciones y form

Documents