Gráfico sobre una función lineal.

En un comentario un lector pregunta la mejor forma de solucionar un problema de visualización de gráfico:

Cuál es la solución de este ejercicio -se refiere a un ejercicio de Gráfico de superficie sobre una Tabla de datos- si y = x elevado a 2 + 4x + 4; qué resultado me da y cómo se resuelve.

Planteamos en primer lugar los datos de la función:

hemos formado la fórmula
=(B2^2)+(4*B2)+4
para definir la función requerida.
Aplicaremos, por ejemplo, una Tabla de datos sobre la celda resultado B3.
Ver la etiqueta de análisis de sensibilidad:

donde observamos el resultado cambiante, de acuerdo al valor de x, de la función establecida y=x^2 + 4x + 4.
Sólo nos queda aplicar un gráfico, que aunque para mi gusto debería ser mejor alguno de tipo línea, elegiremos uno de tipo superficie. Seleccionamos el rango de datos B6:C26 y desde el Menú Insertar > Grupo Gráficos > otros gráficos > Superficie 3D

donde vemos la parábola descrita por la función.

Seguir leyendo...

Función definida por el usuario de una búsqueda vertical.

Solicitaba ayuda una lectora a este respecto:

Quisiera saber si hay alguna opción para que si por ejemplo pongo un nombre en una celda, pongamos en la celda A1 pongo "Pepe" y en B1 "micro 1", cada vez que ponga en otra celda "Pepe" en la correspondiente de la columna 2 me salga "micro 2" y que además, si, pongamos, en A2 he puesto "Cruz" y en B2 "micro 2", cuando convine "Pepe" y "Cruz" en una celda, en la correspondiente columna me salga "micro 1" y "micro 2", es decir, si en A5 pongo "Pepe" y "Cruz" que en B5 me salga "micro 1" y "micro 2".

La primera parte de la pregunta claramente se podría resolver mediante la función BUSCARV, la segunda es un poco más compleja y la dejaremos para una entrada posterior.
Si tuvieramos, al hilo de la cuestión planteada por la usuario, un listado con dos columnas de datos (Nombre y Micrófonos):

Para determinar y alcanzar el resultado deseado aplicaríamos la función BUSCARV sobre un valor buscado, por ejemplo, 'Pepe' referenciándolo a una celda:
=BUSCARV(nombre buscado; rango de datos; 2; 0)
Nada nuevo hasta ahora, si bien propondremos un punto de vista diferente a este problema.
Nos adentraremos en el mundo del VBA, de una manera muy sencilla ya que mis conocimientos en este punto son bastante limitados.
Desarrollaremos en VBA una Función definida por el usuario o UDF que nos ejecute o resuelva de igual forma que BUSCARV.
En primer lugar por tanto accederemos al Editor de VBA, presionando Alt + F11, o bien desde el Menú programador>Grupo Código>Visual Basic en Excel 2007.

Deberemos Insertar un procedimiento tipo 'Función':

Escribiremos en la ventana de Código la siguiente instrucción:

haz click en la imagen

Ya hemos creado una función que replica el BUSCARV estándar, pero específica para bases de datos de dos columnas. En cualquier momento podremos emplear esta función en nuestra hoja de cálculo:

observamos que al escribir el nombre de la función, Excel la reconoce al igual que las funciones estándar; y por supuesto podremos trabajar de igual forma desde su propia ventana diálogo:

Seguir leyendo...

Otro formato condicional en Excel 2003.

Nos solicitaba ayuda un lector a este respecto:

Tengo una plantilla, en Excel 2003, en una serie de números en orden horizontal, éstos están en formato de celda personalizada ( d ), tengo todos los días del mes;en otra plantilla debajo de la serie de números con formato de celda ( ddd ); quiero que los días que caen en 'dom' en la plantilla ( ddd ) los coloree .

Disponemos de una etiqueta para formatos condicionales en la que hemos desarrollado algunas ideas similares. Para dar respuesta a la cuestión de hoy emplearemos, además del Formato condicional con fórmula, la función DIASEM(fecha; tipo) - ya utilizada en la entrada Series en Excel-.
Disponemos por tanto de los siguientes datos:

En una nueva fila copiaremos la fila de fechas con el formato personalizado 'ddd', y será a ésta a la que daremos el siguiente Formato condicional (Menú Formato>Formato condicional):

Con lo que obtenemos el resultado deseado, es decir, los días que sean 'Domingo', quedan coloreados o remarcados.

Por supuesto, de igual forma se podría haber aplicado a las fechas sin formato o con cualquier otro formato personalizado.

Seguir leyendo...

Fórmula lógica directa en Excel.

Supongamos que queremos llegar a un cálculo por día y porcentaje pero en función de unas fechas definibles por el usuario; es decir, tenemos un importe a aplicar por cada día que se encuentre en un intervalo de fechas definible en cada momento por el usuario.
Nos solicitaba ayuda un lector a este respecto:

Tengo una tabla de datos con una programación de obra por día en la que ya tengo un formato condicional para que me marque los días comprendidos entre el rango de fechas a realizar la actividad, pero además necesito que me de un porcentaje por día de actividad, o sea, que me devuelva un porcentaje en cada día marcado en color, formulado para cuando cambien las fechas.

Nuestros datos son:

En primer lugar remarcamos las fechas a las que son aplicables el cálculo, dándoles un Formato condicional, para todo el rango de fechas:

Para condicionar, de igual forma, el cálculo a realizar a las fechas elegidas, podremos formular lo siguiente:

Detengámonos en la fórmula:
=Y(A2>=$E$2;A2<=$F$2)*$F$4*B2
El primer valor (Y(A2>=$E$2;A2<=$F$2)) nos devuelve una condición lógica verdadero o falso, que al emplearlo en funciones se convierte en 1 (verdadero) ó 0 (falso); si la fecha del registro se encuentra comprendida entre las fechas de estudio el resultado será 1 ó 0. El segundo y tercer producto ($F$4*B2) simplemente son multiplicaciones directas. Por lo que resulta, en cada registro, un valor dependiendo de la fecha del registro.

Otra posibilidad es conocer, el valor de la suma de todos aquellos registros que cumplan la condición de encontrarse entre ambas fechas, para lo que previamente definiremos los siguientes rangos:
Fecha =Hoja1!$A$2:$A$28
Porcentaje =Hoja1!$B$2:$B$28
Importe =Hoja1!$F$4
Una vez asignados los nombres a esos rangos de celdas aplicaremos la siguiente función Matricial:
{=SUMA((Fecha>=$E$2)*(Fecha<=($F$2))*Porcentaje*Importe)}
lo que nos determinará, dentro de los rangos de registros definidos, cuál es el sumatorio de aquellos que cumplan el requisito de encontrarse entre las fechas definidas por el usuario en cada momento.

Seguir leyendo...

Macro para agrupar datos de diferentes hojas en Excel.

En una entrada anterior determinabamos, empleando la herramienta Consolidar el número de elementos, agrupados por un concepto, de diferentes rangos de datos situados en varias hojas de nuestro Libro; esta herramienta -Consolidar- está diseñada para obtener un resultado numérico en base a la función objetivo seleccionada.
Veíamos en nuestro ejemplo de Consolidar, que nuestra empresa se dedicada a gestionar un pequeño puerto con tres muelles de atraque, para la cual en nuestro fichero almacenabamos los datos de cada uno de estos muelles en una hoja del libro diferente; en esta ocasión no nos interesa conocer únicamente el número de embarcaciones atracadas en cada fecha, si no que queremos conocer cuáles son los nombres de estos barcos.
Para ello vamos a generar una macro con la que en primer lugar se nos liste en una hoja resumen, de manera conjunta, los datos de todos los barcos de los tres muelles; para posteriormente generar en base a este listado único una Tabla dinámica donde poder ver la información de los barcos agrupados por fecha.
Disponemos de los siguientes datos en cada Muelle:

Será básico antes de comenzar a desarrollar nuestra Macro definir los siguientes nombres para los rangos dinámicos:
listado =DESREF(Resumen!$A$1;;;CONTARA(Resumen!$A:$A);2)
Muelle1 =DESREF(MuelleA!$A$2;;;CONTARA(MuelleA!$A:$A)-1;2)
Muelle2 =DESREF(MuelleB!$A$2;;;CONTARA(MuelleB!$A:$A)-1;2)
Muelle3 =DESREF(MuelleC!$A$2;;;CONTARA(MuelleC!$A:$A)-1;2)
El desarrollo de la macro tendrá que ser:

haz click en la imagen

Analicemos la Macro antes de dar por finalizado el ejercicio.
Las primeras partes nos permiten seleccionar cada uno de los rangos de datos de las diferentes hojas y pegarlos una a continuación del otro en una hoja Resumen:

de forma análoga para las tres hojas (MuelleA, MuelleB y MuelleC) que tenemos.
Una vez agrupado en un único listado todos los datos, continuamos con la Macro e incluimos una tabla dinámica que nos de la siguiente estructura:

Por último, ya creada la Macro, que hemos llamado 'agrupación_lista', asignamos esta Macro a un Botón para ejecutarla.
Ya podemos visualizar los datos de nuestros tres Muelles, agrupados por Fecha, con los nombres de los barcos amarrados...
Adjunto fichero para análisis:

Listar por fecha-v...

Hosted by eSnips

P.D.: Al no ser especialista en programación VBA se admiten mejoras en la Macro ;-)

Seguir leyendo...

Consolidar: herramienta avanzada de Excel.

Supongamos que disponemos de tres hojas de cálculo en nuestro Libro (las que necesitemos), y en cada hoja, disponemos de unas bases de datos con igual estructura de campos, para nuestro ejemplo, tendremos una campo de Fecha y otro de Número de barcos amarrados; obviamente los registros pueden ser o no diferentes.
Lo que necesitamos obtener es un Resumen consolidado de las distintas hojas de trabajo, en el que veamos resumido por Fechas la suma total de los barcos amarrados.
Disponemos de los siguientes datos, distribuidos en tres hojas (Barco1, Barco1 y Barco1):

Para realizar la consolidación ejecutaremos, en la versión Excel 2007, desde el menú Datos > Grupo Herramientas de datos > Consolidar; y con la versión Excel 2003, menú Datos > Consolidar:

En el cuadro diálogo, lo primero ha sido determinar el tipo de consolidación de datos que deseamos, seleccionando la función 'Suma'; posteriormente procedemos a Agregar los rangos de las distintas hojas a Consolidar; por último, para dar la estructura resumen final, marcamos las opciones de Usar rótulos de las 'Fila superior' y, en este caso, de la 'Columna izquierda' (con estas opciones marcadas conseguimos que se utilicen los campos de títulos de los rangos de cada hoja seleccionados y además que agrupe por los distintos elementos que aparezcan en cualquiera de los rangos de celdas seleccionados).
Vemos el resultado obtenido:

a lo que hemos añadido un subtotal del número de barcos amarrados, mediante un sumatorio, y para realizar la comprobación que ha sumado la totalidad de barcos, hemos incluido también un sumatorio de las distintas hojas.
Con lo que queda resuelto el problema.

Seguir leyendo...

Completar Series en Excel.

Me ha llegado la siguiente consulta sobre la manera de completar Series temporales:

Realizamos la siguiente serie de datos. Comenzamos introduciendo en la celda B3 el día 05/01/09. La serie debe terminar en el día 31/12/09. En esta serie no deben aparecer sábados ni domingos.
En cada una de las celdas de la columna C debe aparecer el día de la semana correspondiente a la celda de la columna B contigua.
Por último, en la columna D debe figurar el trimestre al que pertenece el dia de la celda contigua.

Comenzaremos por dar forma a la plantilla en nuestra hoja de cálculo:

Este ha sido nuestro primer paso, de acuerdo a las instrucciones hemos insertado la fecha 05/01/2009 en la celda A2; también nos pedían que la celda contigua, i.e., en la columna B, nos apareciera el día de la semana; para esto sólo debemos emplear la función
=DIASEM(fecha;2)
el segundo argumento de la función podrá ser:

• 1 u omitido: Números del 1 (domingo) al 7 (sábado),


• 2: Números del 1 (lunes) al 7 (domingo),


• 3: Números del 0 (lunes) al 6 (domingo).

Esta función nos devolverá un valor numérico del 1 al 7, es decir, de lunes a domingo; pero nosotros preferimos que el valor que aparezce sea textual, por lo que anidando la función DIASEM en la función
=ELEGIR(número colocación; valor1; valor2; ...)
el primer argumento selecciona el elemento de la serie definida en los siguientes argumentos de la misma función; en nuestro ejemplo, la función final quedaría:
=ELEGIR(DIASEM(A2;2);"Lunes";"Martes";"Miércoles";"Jueves";"Viernes";"Sábado";"Domingo")
cuando DIASEM(fecha;2) resuelva con un 2, la función ELEGIR mostrará el segundo valor, i.e., 'Martes'.
Una última petición es que en otra celda nos aparezca el trimestre del año al que pertenece; para dar una solución anidaremos a una funcion SI otra función lógica Y:
=SI(Y(MES(A2)>=1;MES(A2)<=3);1;SI(Y(MES(A2)>=4;MES(A2)<=6);2;SI(Y(MES(A2)>=7;MES(A2)<=9);3;SI(Y(MES(A2)>=10;MES(A2)<=12);4))))
como sólo queremos conocer el trimestre, el único dato que necesitamos tratar es el mes de cada fecha, para ello aplico a la celda fecha la función
=MES(fecha)
lo que muestra un valor numérico de 1 a 12; este valor, al anidarlo dentro de una función lógica Y, lo convertimos en la prueba lógica de nuestra función condicional SI.
Hemos delimitado, en función del mes de la fecha, el trimestre al que pertenece:

• si el mes de la fecha está entre 1 y 3, el trimestre será 1


• si el mes de la fecha está entre 4 y 6, el trimestre será 2


• si el mes de la fecha está entre 7 y 9, el trimestre será 3


• si el mes de la fecha está entre 10 y 12, el trimestre será 4

Para darle un 'toque' personalizado, hemos dado formato a la celda para que la máscara que veamos nos aparezca con valor" Trimestre".
Pues una vez definido todos los parámetros que nos piden, nos queda lo más sencillo, completar una serie, en este caso una serie cronológica; desde la celda A2:

hemos tenido cuidado de restringir, el la sección Unidad de tiempo, los valores de la serie cronológica a los días laborales, como exigían.
Adjunto fichero:

Consulta-Series de...

Hosted by eSnips

Seguir leyendo...

SI condicional: solución a un problema.

Pregunta una usuaria la manera de resolver mediante una función SI el siguiente problema:

Desarrolla una hoja de cálculo en la que sólo tengas que introducir los datos necesarios (DÍAS QUE NECESITAS EL COCHE) para determinar el precio final; siendo las condiciones:
67,31€ al día + 19,62€ de seguro.
Además esta empresa te da un
10% de descuento por el 2º día
15% por 3er día
17% por el 4º, 5º días............
el descuento se calcula sobre el alquiler por día.

Entendemos que los descuentos aplicables se harán sobre la totalidad de días contratados, y no sobre el seguro.

Planteamos en una hoja de cálculo las condiciones del alquiler:

El precio definitivo dependerá entonces del número de días contratados, variable que hemos definido en la celda F4; en función al valor de esta celda deberemos asignar un descuento, de entre los tres propuestos.

• Si el valor es 1 día no existe descuento alguno, por tanto el precio final será el resultado de multiplicar un día por su precio-día más el precio de seguro.
  =B4*F4+C4


• Si el valor es 2 días corresponde aplicar un descuento del 10% al precio-día, por tanto el precio final será el resultado de multiplicar dos días por su precio-día descontado ese 10% más el precio de seguro.
  =B4*F4*(1-B7)+C4


• Si el valor es 3 días corresponde aplicar un descuento del 15% al precio-día, por tanto el precio final será el resultado de multiplicar tres días por su precio-día descontado ese 15% más el precio de seguro.
  =B4*F4*(1-C7)+C4


• Si el valor es mayor o igual a 4 días corresponde aplicar un descuento del 17% al precio-día, por tanto el precio final será el resultado de multiplicar este número de días por su precio-día descontado ese 17% más el precio de seguro.
  =B4*F4*(1-D7)+C4

La fórmula final, juntando y construyéndolo en forma de condicional (hemos desarrollado varios ejemplos con el SI en este blog), quedaría entonces:
=SI(F4=1;(B4*F4+C4);SI(F4=2;(B4*F4*(1-B7)+C4);SI(F4=3;(B4*F4*(1-C7)+C4);SI(F4>=4;(B4*F4*(1-D7)+C4)))))

haz click en la imagen

Seguir leyendo...

Tabla de referencias cruzadas - II.

Continuando con distintas alternativas de resolver una Tabla de referencias cruzadas, además de la ya vista en el post anterior , podremos optar por emplear el Asistente para Sumas Condicionales, es decir, empleando fórmulas matriciales (es necesario tener instalado este Complemento).
Llegaremos finalmente a:

Desde el menú Herramientas > Asistente > Suma condicional... ejecutamos dicho asistente, con el que en cuatro pasos creamos una función matricial:
En primer lugar seleccionamos el rango de datos

En el segundo paso identificamos el campo sobre el que queremos calcular, así como agregamos las condiciones iniciales

En el tercer paso del Asistente, indico que tipo de resultado deseo, si sólo una celda con la fórmula final, o un detalle del resultado junto a las condiciones dadas; seleccionaremos sólo una celda con el resultado:

Por último, indicamos dónde queremos situar el resultado matricial obtenido:

Vemos que la fórmula matricial quedaría de la siguiente manera:
{=SUMA(SI($C$2:$C$11="11";SI($B$2:$B$11="F";1;0);0))}
y para poder arrastrar la fórmula al resto de la Tabla creada, vinculando a los diferentes valores que podemos tener tanto para Edad como para Sexo, cambiaremos la fórmula para que quede de esta manera:
{=SUMA(SI($C$2:$C$11=F$3;SI($B$2:$B$11=$E4;1;0);0))}
Con lo que queda resuelto nuestro problema. Recordemos que la manera de ejecutar fórmulas matriciales es pulsando simultaneamente las teclas Ctrl+Mayus+Enter.

Seguir leyendo...

Tablas de referencias cruzadas en Excel.

Un usuario del blog pregunta la manera de completar, en base a su origen de datos, una tabla de referencias cruzadas empleando los campos de su base de datos; emplearemos los datos que él nos ha propuesto:

me gustaria me concedieran su apoyo para resolver el siguiente problema. Nombre Sexo Edad Javier M 11 Pedro M 11 Pablo M 13 Juan M 12 Chana F 14 Petra F 15 PetronilaF 13 DoroteO F 14 Menelea M 12 RAUL M 13 Deseo llenar la siguiente tabla. 11 12 13 14 15 H M

Podríamos optar por varios métodos, pero sin duda el más sencillo es aplicar sobre la base de datos de tres campos (Nombre, Sexo y Edad) un informe de Tabla dinámica.

Ejecutamos, por ejemplo desde la versión 2003, desde el menú Datos > Informe de gráfico y tabla dinámica (ver Varios Tablas dinámicas), seleccionamos en primer lugar (Paso 1 del Asistente) que nuestro rango de datos está en una base de datos de Excel, y que deseamos sólo un Informe de tabla dinámica. en el paso 2 del asistente seleccionamos el área $A$1:$C$11. Por último, marcamos una celda destino, por ejemplo, en la misma hoja, la celda E3.
Ahora desde la lista de campos, generamos la estructura de tabla dinámica, llevando el campo Edad al área de columnas; el campo Sexo al área de filas y por último el campo Nombre, por ejemplo, al área de datos. Probablemente haciéndolo de esta manera ya tengamos construido la tabla que deseábamos:

Lo importante es que el campo incluido en el área de datos este resumido por Cuenta en la configuración de campo.

Seguir leyendo...

Insertar un botón de Macro en la Barra de herramientas en Excel 2007.

Intentaremos replicar el proceso de un lector a la hora de Personalizar la Barra de herramientas de acceso rápido introduciendo un Comando-Icono de una Macro:

Tengo una macro en excel 2007 que la agrego a la barra de herramientas de un libro especifico grabo todo y cierro ... cuando lo vuelvo a abrir la macro ya no esta en la barra de herramientas...

.
Podemos seguir los pasos en el siguiente video:

como podemos observar, hay dos pasos clave, uno es asignar el Comando de la Macro a un Libro en concreto, por lo que sólo nos aparecerá dicho Comando en la Barra de herramientas al abrir este fichero; el otro paso es importante a la hora de Grabar ficheros excel en la versión 2007 con macros, ya que tendremos que darles una extensión .xlsm (Libro de Excel habilitado para macros).

Seguir leyendo...

Macro de un filtro avanzado en Excel.

En una entrada anterior de este blog sobre los filtros avanzados, nos introdujimos en esta herramienta; hoy, a partir de éste crearemos una Macro con la que ejecutaremos este Filtro avanzado, para posteriormente asignarle un botón. Tendrá sentido esta creación de macros cuando el usuario final de nuestras bases de datos no tenga conocimientos suficientes de esta herramienta avanzada.
Realizaremos una configuración sencilla, sin entrar en definir rangos dinámicos que complicarían la explicación y el desarrollo de la programación de la macro; por tanto, emplearemos el Asistente para Macros para luego realizar alguna modificación directamente sobre el código desde el Editor de Visual Basic(Alt+F11).
Desde luego no considero que tenga suficientes conocimientos de programación en Visual Basic ni tan siquiera para darme la categoría de principiante, tan sólo me dedico a observar en qué forma redacta el código el Asistente, para tras analizarlo poder cambiar determinados parámetros o líneas de la programación.
Dicho esto, para nuestro ejemplo partimos de la siguiente base de datos:

Antes de continuar generaremos o asignaremos nombre a los siguientes rangos:
campos = $A$1:$I$1 recoge la cabecera de la base de datos,
Criterios = $K$1:$M$3 el rango donde se encuentran los criterios,
Datos = $A$1:$I$13 el rango de la base de datos.
Crear estos nombres es importante para poder personalizar posteriormente nuestra Macro, puesto que al fijar rangos nombrados, simplemente cambiando directamente en la hoja de cálculo los criterios de filtro y pulsando el botón de ejecución, se filtrarán los registros de la base de datos que cumplan estos criterios.
Supongamos que nuestras condiciones siempre estarán vinculados a tres campos como máximo, y una doble condición al menos sobre una de estas, es decir, que nuestro rango de criterios para el filtro avanzado corresponde a un rango fijo; en nuestro ejemplo a Criterios=$K$1:$M$3:

Con esta limitación, de momento, podemos iniciar la secuencia de movimientos grabados mediante el Asistente para macros (Menú Programador > Grupo Código > Opción Grabar Macro):

Observamos en el video adjunto que hemos añadido dos botones, de los controles de formulario, para poder ejecutar las dos macros creadas de manera sencilla. Uno de ellos nos permite el filtro avanzado y el otro un borrado del resultado del filtrado.
Los códigos de la programación son los siguientes:

Seguir leyendo...

La ordenación avanzada en Excel.

Revisaremos una manera avanzada de ordenar nuestras bases de datos en Excel, lo haremos sobre la versión Excel 2007. aunque para versiones Excel 2003 o anteriores es muy similar.
Comenzamos con la disposición de nuestra Base de datos:

Normalmente estamos acostumbrados a realizar la ordenación empleando los iconos directos de orden de A a Z o bien el contrario de Z a A, es decir en orden ascendente o descendente:

Sin duda es la opción más cómoda y rápida, pero en ocasiones necesitamos realizar una ordenación sobre distintos campos de nuestra base de datos, dando prioridad a unos sobre el resto, para estas ocasiones es cuando empleamos el Ordenar avanzado:

Esta ordenación nos permitirá aplicar una prioridad en nuestra Base de datos. Seleccionamos nuestro origen de datos en Excel, y desde el menú Datos > Ordenar

En este cuadro diálogo configuraremos nuestros criterios de orden. En primer lugar es muy importante que marquemos la opción de Mis datos tienen encabezado, ya que de este modo no se incluirán los títulos de los campos en la ordenación; si no fuera el caso, habría que desmarcarlo. Ahora tan sólo deberemos ir seleccionando la prioridad de campos en la ordenación, seleccionando en la sección columna dicho campo. En la siguiente sección de la ventana de diálogo seleccionaremos la naturaleza de esta ordenación, es decir, si queremos ordenarlo siguiendo el valor de las celdas, el color de la fuente o del fondo de la celda (esta es una opción que sólo existe para la versión Excel 2007). Por último, para cada campo seleccionado, le indicaremos cuál es el criterio, ascendente o descendente o personalizado, por el que será ordenado el campo. Una vez configurados los tres elementos, podremos Agregar un nuevo nivel en la ordenación, esto es, darle una segunda prioridad para la ordenación total de la Base de datos.
En nuestro ejemplo, supongamos que queremos tener ordenada nuestra base de datos, en primer lugar, en orden ascendente (de menor a mayor), siguiendo el valor de las celdas, por el campo 'Fecha operación'; como segundo nivel, ordenaremos dependiendo del valor de cada celda, de mayor a menor, por el campo 'Precio unitario'; y por último, utilizaremos el campo 'descuento' para ordenar los registros de nuestra base de datos de menor a mayor.

Vemos el resultado de la ordenación:

Lo que ha ocurrido en nuestra base de datos es que se han ordenado todos los registros, respetando la cabecera o títulos con los nombres de los campos, dando prioridad en primer lugar a las fechas de cada operación, si hubieram habido coincidencias en alguna fecha de varios registros, hubiera aplicado inmediantamente el segundo nivel de prioridad, es decir, el del campo 'Precio unitario', ordenando esos registros coincidentes por el campo 'Precio unitario' con una ordenación descendente; de igual forma, si nos encontraramos con coincidencias de registros también al aplicar este segundo nivel, aplicaría el tercer nivel definido.

Seguir leyendo...

Ejemplo de doble Validación condicionada.

Pregunta un lector sobre la forma de realizar una validación condicionada

Cómo poder hacer lo siguiente: 1. Si la celda A1 introduzco el valor 1, entonces la celda B1 solo puede permitir los numeros 1, 2 o 3. 2. Pero si en la celda A1 introduzco el valor 2, entonces la celda B1 solo puede permitir los valores 4, 5 y 6

Para dar solución a esta duda tendremos que Asignar nombres a los distintos rangos, para luego mediante la Validación configurar los posible valores. Podremos jugar y experimentar más adelante con los rangos dinámicos.
El problema que surge al tratar de vincular el valor de una celda con el de otra celda, siendo en ambos casos valores numéricos, es que Excel nos devuelve un error, puesto que entiende que pretendemos emplear valores genéricos ya utilizados en otras partes de la aplicación; por lo que para salvar este inconveniente convertiremos en texto algunos de estos valores.
Vamos por partes, en primer lugar supongamos que es esta la distribución de posibles valores:

Pretendemos que cuando en la celda A2 seleccione un valor de los posibles en el campo 'Opción1', como primer nivel, en la celda B2 automáticamente sólo me permita desplegar y seleccionar aquellos existentes en las columnas F, G y H, como segundo nivel.
Antes de continuar, es fundamental dar una forma de texto a las cabeceras de las columnas F, G y H, mediante la función =TEXTO(valor;formato) para cada agrupación de segundo orden; esta función convierte en texto cualquier valor numérico, en nuestro ejemplo
=TEXTO(1;"#") convertimos el valor 1 en un texto con un formato de un caracter,
=TEXTO(2;"#") convertimos el valor 2 en un texto con un formato de un caracter,
=TEXTO(3;"#") convertimos el valor 3 en un texto con un formato de un caracter;

Una vez realizada esta conversión podremos comenzar a Asignar nombres a cada agrupación de valores, ya sean de primer o segundo nivel; recordemos cómo lo hacíamos viendo la entrada Asignar nombres a rangos; en concreto, para nuestro ejemplo, los nombres asignados son:
Opción1=Hoja1!$D$2:$D$4
_1=Hoja1!$F$2:$F$3
_2=Hoja1!$G$2:$G$4
_3=Hoja1!$H$2:$H$4
Nos fijamos en la forma particular en que Excel a dado los nombres a las agrupaciones de segundo nivel, y es que a pesar de la conversión en texto, Excel no puede entender un Nombre como un valor numérico genérico; sin embargo esto podremos salvarlo.
Llegamos al último paso, la Validación. Para la celda A2 configuramos la siguiente validación:

y para la celda B2:

Fijémonos en la función empleada en este paso:
=INDIRECTO("_"&$A$2)
es aquí donde hemos 'corregido' esa particularidad de Excel, el guión bajo; lo que conseguimos con esta función es que la Validación busque en el rango que sea igual a un guión bajo seguido del valor elegido en la celda A2.
Como se puede ver no he configurado los mensajes de error o títulos de esas celdas validadas, pero podríamos hacerlo igual que en cualquier otro caso, entiendo que no es básico para el desarrollo del ejemplo.

Probamos que todo funcione correctamente desplegando y seleccionando un valor en la celda A2, y vemos que efectivamente la celda B2, al desplegar la lista, sólo nos devuelve los valores correspondientes:

Seguir leyendo...

Un ejemplo de Filtro Avanzado en Excel.

Nos adentraremos, un poco al menos, en el mundo de los filtros avanzados. Son útiles, en especial, cuando estamos trabajando con bases de datos en Excel; si bien conviene recordar que Excel es una Hoja de cálculo y no un gestor de Bases de datos, como lo podría ser Access.
Supondremos que tenemos una Base de datos en nuestra hoja de cálculo, y que pretendemos aplicarles, para encontrar o filtrar algunos de sus registros, un filtro; en aquellas ocasiones en que los Autofiltros se quedan 'cortos' para lo que pretendemos obtener, aplicaremos los Filtros Avanzados, donde desaparecerán las restricciones o limitaciones que tenía el Autofiltro.
En primer lugar recordaremos desde donde activamos esta herramienta avanzada; si utilizamos Excel 2003 navegaremos por el Menú Datos > Filtro > Filtro avanzado; si nos decidimos por la versión Excel 2007 iremos al Menú Datos > Ordenar y Filtrar > Avanzadas.
Para poder aplicar esta herramienta avanzada debemos saber que además de nuestra Base de datos, necesitamos un rango de celdas a parte donde indicar cuáles son los criterios de filtro, es decir, dos rangos: Rango de la lista y Rango de criterios.

Otra ventaja que tenemos al aplicar este filtro avanzado es que podemos optar bien por filtrar sobre la misma base de datos, al igual que el Autofiltro, o bien realizar un copiado con los registros filtrados que cumplan las condiciones o criterios dados en el lugar que seleccionemos.

De nuestra base de datos necesitamos conocer qué registros cumplen la condición que el número de productos vendidos, campo 'unidades del producto', sea menor de 56 o que sea mayor de 79 y menor de 96 (trabajaremos en nuestro ejemplo con desigualdades estrictas para facilitar el trabajo).

Seguir leyendo...