sábado, 29 de octubre de 2016

Estimación.Lineal

Hola Amigos de MBExcel.

Después de una larga temporada sin escribir, me aventuré, más que a escribir, a explicar mediante un video ¿Cómo obtener los estadígrafos básicos de una regresión lineal usando la función Estimación.Lineal?

Debo mencionar que esta intención nace de varias consultas que he recibido acerca del tema:

Buenos días, me pueden explicar como obtener la tabla con los resultados de la estimación lineal, ya que cuando hago este cálculo solo me aparece como resultado el valor de la pendiente en una celda, pero no la tabla completa con todos los resultados como se ve en el ejemplo.
Gracias




martes, 15 de mayo de 2012

NuevoFunción Sumar.Si.Conjunto

Descargar el ejemplo

Siguiendo con la temática acerca de funciones para consolidar información, presento una de las novedades de la versión 2007-2010; esta es:




Donde:
Rango_suma: celdas que se van a sumar.
Rango_Criterio1: primer rango a evaluar segun condición dada.
Criterio1: primera condición a evaluar en el Rango_Criterio1.
Rango_Criterio2: segundo rango a evaluar según condición dada.
Criterio2: segunda condición a evaluar en el Rango_Criterio2.







El plus de usar esta función es la posibilidad de filtrar los datos en función de dos o mas criterios. Aclaro que la función permite 127 parejas de criterios. En nuestro ejemplo se buscará sumar el número de artículos por mes; ello exige filtrar la información tanto por mes como por artículo.

Aquí la información a filtrar:




Ahora, el filtro por artículo se hará usando una lista desplegable tipo formulario de la opción de programador. Instalamos el control y damos click derecho.
                              


Vinculamos el control con la celda H2, es decir, debajo de la misma celda donde está ubicado el control; esto por razones de estética.




Previamente hemos creado el nombre "producto" que hace que podamos ingresar más artículos posteriormente.



Teniendo la lista desplegable, en la celda H3 formulamos:

=SUMAR.SI.CONJUNTO($D$2:$D$39;$C$2:$C$39;G3;$E$2:$E$39;INDICE(productos;$H$2))

El rango $D$2:$D$39 será el rango a sumar si se cumplen las condiciones. Columna de entradas.
El rango $C$2:$C$39 será el primer  rango susceptible de ser evaluado. Columna Meses
G3 será el criterio1, para la celda H3 será enero.
$E$2:$E$39 será el segundo rango evaluado. Columna artículo.
INDICE(productos;$H$2) hará que coincida el artículo escogido desde el control con la referencia de fila en la celda H2.

Al final obtenemos nuestro anhelado consolidado por mes:


La lista desplegable quedaría así:
Seleccione el artículo 





domingo, 6 de mayo de 2012

Función Desref

Descargar el ejemplo

En esta nota buscaremos darle una utilidad diferente a la función Desref, dado que hasta el momento se ha utilizado para crear rangos dinámicos en las notas:


Aquí, los argumentos de la función:



Donde:

ref: es el origen desde donde se se mueve la referencia.
filas: es la cantidad de filas que se desplazará verticalmente.
columnas: es la cantidad de columnas que se desplazará horizontalmente.
alto: es opcional e indica la cantidad de filas que tendrá el rango devuelto.
ancho: es opcional e indica la cantidad de columnas que tendrá el rango devuelto.

Se debe aclarar que esta función es de tipo referencial; por tanto sólo devuelve la longitud de un rango de celdas, no hace alguna operación con este. Entonces, los tres primeros argumentos (ref, filas y columnas) definen el primer punto de referencia, y los dos últimos(alto y ancho) definen el tamaño del rango.


Recordemos que al final del ejercicio Función Sumar.Si.Conjunto , se sumaban las entradas de todo el año.
Pero ¿cómo saber la cantidad de pernos que ingreso de febrero a octubre? por ejemplo. Pues la respuesta a esta necesidad es utilizar la función DESREF, indicando mes inicial y mes final de la consulta a través de listas desplegables.

Buscaremos llegar a este modelo:


Como en notas anteriores, se instalan las listas desplegables de la pestaña Programador, se da click derecho, se asigna un rango de entrada que en este caso corresponde al rango denominado "meses"; es decir, el rango G8:G19 y se vincula con una celda ( mes inicial y mes final con las celdas M8 y M10 respectivamente). 





Un truco para que al insertar el control, este quede justo entre las dimensiones de las celdas es insertarlo mientras se mantiene oprimida la tecla Alt.



Ocupándonos de la consolidación de los datos, formulamos en la celda L21 lo siguiente :


La función opera así:

La referencia inicial será la celda H7, una celda antes de la celda que contiene el primer mes (enero).
M8 corresponde a la fila inicial del rango, el valor cero en el argumento columna indica que no habrá desviación hacia otra columna.

El alto del rango se establecerá al restar M10 (vínculo del control que contiene el mes final)- M8(vínculo del control que contiene el mes inicial)+1. El hecho de sumar una celda permite que se incluya al mes inicial seleccionado desde el control. 

Es necesario aclarar que no es lógico seleccionar un mes final que sea menor en términos de arreglo que el mes inicial; por tanto, la fórmula arrojaría un error.






RecienteFunción Sumar.Si

Descargar el ejemplo


Hoy presento un ejemplo de una función muy interesante de la categoría matemáticas y trigonométricas, esta es:



Donde:

rango: es el rango que se pretende evaluar.
criterio: es la condición a evaluar en cada elemento del rango.
[rango suma]: es el rango que contiene los valores a sumar de acuerdo con lo especificado en el rango criterio. Es opcional. Si se omite, se sumaran las celdas del argumento "rango".

Una de las virtudes de usar esta función es el hecho de poder evaluar un criterio o condición en un rango distinto del rango que se va a sumar.

En este ejemplo se tiene un consolidado de ganancias por mes; la idea es sumar los valores de las facturas por mes, y al final sumar las ganancias de todo el año. Este es el modelo:


Previamente he nombrado las columnas Mes y costo de la factura como "mes_1" y "costo", respectivamente.

A continuación, en la columna Ganancias por mes formulo lo siguiente:

=SUMAR.SI(mes_1;"Enero";costo)

Recordemos que mes_1 es el rango evaluado, es decir, mes a sumar.
"Enero" es el criterio o condición, en este caso sumará todos los costos de factura del mes de enero.
"costo" es el rango sobre el que se realizará la suma.


Aquí, el criterio va cambiado de acuerdo con el mes sumado.


Al final realizamos una suma de las ganancias por año. =SUMA(P5:P16)

martes, 1 de mayo de 2012

¿Cómo extraer el nombre del mes de una fecha?


Cuando tenemos un valor con formato fecha (05/01/2012), y necesitamos obtener el nombre del mes y no el número del mismo, podemos pensar en la función MES() o en un formato personalizado. Pues bien, la función MES() devuelve el número del mes y el formato personalizado podría funcionar así: "mmmm".  Pero en esta nota buscaremos resolver la necesidad mediante la función BuscarV.

Primero haremos uso de la función MES() solo para extraer el número del mes .

= MES(núm de serie)
núm de serie: es un valor, celda o función que devuelve un valor de tipo fecha.


Ahora tendremos que armar una matriz que servirá de consulta para que la función BuscarV de como resultado el nombre del mes. Se recomienda nombrarla para evitar errores al copiar la fórmula; el nombre de la matriz será: "mes".


Finalmente formularemos en una columna contigua a la columna que contiene el número de mes lo siguiente:

=BUSCARV(H5;mes;2;FALSO)

H5 será la celda que contiene el número de mes; es decir, el valor buscado.
mes será la matriz consultada .
El argumento Ordenado será FALSO o cero para obtener una coincidencia exacta.


Nótese que si hubiese un valor por fuera de la matriz mes, el resultado seria NO aplica (#N/A), pero dicho valor no tendría sentido dado que solo hay doce meses. Bueno, la excepción sería si la celda que contiene el número del mes estuviese vacía.












sábado, 28 de abril de 2012

Función SI...Segunda parte


Descargar el ejemplo

En esta segunda entrega de la función SI, se pretende clasificar una calificación dentro de un referente cualitativo que viene dado por rangos. Se usará la función en cuestión acompañada de la función Y, vista en nuestra entrega anterior. Este es el modelo:

Nota: Para que la función trabaje correctamente con el primer criterio de valoración, es decir, <= 3,00 se debe utilizar un formato personalizado, así:

Damos click derecho sobre la celda que contiene el valor (3,00), elegimos la opción formato de celda, número_personalizada, y aparece el siguiente formulario:



En el ítem "Tipo:" ponemos entre comillas la desigualdad "<=" (menor o igual que).

En la celda C18 pondremos la calificación a evaluar, y en la celda E18 formularemos:

=SI($C$18="";"";SI($C$18<=$D$7;$E$7;SI(Y($C$18>=$C$8;$C$18<=$D$8);$E$8;SI(Y($C$18>=$C$9;$C$18<=$D$9);$E$9;SI(Y($C$18>=$C$10;$C$18<=$D$10);$E$10;SI(Y($C$18>=$C$11;$C$18<=$D$11);$E$11;"Valor Fuera del Referente"))))))

Como es de esperarse, no se clarifica como actúa cada parte de la formulación. A continuación se presenta una explicación de cada componente.


Para calificaciones entre 3,01 y 3,50 se formula el rango, haciendo uso de la función "Y".  Hemos usado referencias absolutas con la tecla F4. Y($C$18>=$C$8;$C$18<=$D$8);$E$8. Si el valor está en este rango, La función "SI" devuelve el mensaje "Se Cumple insatisfactoriamente", es decir, el texto que está en la celda E8. Si no se cumple con el criterio, la funcón SI evaluará el siguiente criterio: Calificaciones entre 3,51 y 4,00. De esta manera se evalúa hasta llegar al rango entre 4,51 y 5.00.

Al final de la fórmula he adicionado un mensaje a presentar si el valor ingresado en la celda C18 es superior a 5,00. Este es: "Valor Fuera del Referente".




Imagen que explica cada componente de la formulación


Cuando se ingresa en la celda C18 la calificación 3,45 la valoración cualitativa arrojará el mensaje: se cumple insatisfactoriamente.









lunes, 23 de abril de 2012

Función SI.....Primera Parte

Descargar el ejemplo

Considero que la función SI es una de las más populares en lo que respecta a formulación con excel, dado que se trata de una función versatil y facil de usar, salvo cuando se hacen demasiadas anidaciones. Básicamente, evalua una condición o criterio, si aquel criterio se cumple, ejecutará una acción que se programe, y si aquel criterio no se cumple, ejecutará otra acción..



Los argumentos de la función se presentan a continuación:





prueba lógica: es una comparación entre dos celdas usando operadores lógicos. Recordemos que los operadores lógicos son: =(igual), <(menor), >(mayor), <>(distinto), >=(mayor o igual), <=(menor o igual).

[valor si verdadero]: es el valor, celda o texto especificado a devolver si prueba lógica es verdadera.

[valor si falso]: es el valor, celda o texto especificado a devolver si prueba lógica es falsa.
Los argumentos valor si verdadero y valor si falso son opcionales. Si se omiten la función SI devuelve directamente VERDADERO o FALSO.

Veamos un primer ejemplo:

Se tiene un consolidado de resultados de análisis de Calcio, Magnesio y Dureza total en agua. La idea es confrontar los datos frente a un criterio normativo; en este caso la resolución 2115/2007.

Vamos a nombrar las columnas de manera sencilla. La primer columna la llamaremos "Ca", la segunda "Mg" y la tercera "Dt" ¿Cómo se hace? Primero señalamos la columna, vamos al cuadro de nombres, escribimos Ca y damos enter. De la misma manera hacemos con las otras columnas..


sábado, 24 de marzo de 2012

Cómo hacer consultas en dos direcciones con la función Indice

Descargar el ejemplo

En un momento dado necesitamos hacer una consulta de un dato ubicado en otra hoja del mismo archivo o incluso fuera de él pero en dos direcciones; es decir,  teniendo en cuenta no solo la ubicación de la columna o   fila (asunto que se resuelve con las funciones BuscarV y BuscarH), sino las dos al tiempo. Pues alguna vez me sucedio y la función Indice acompañada de la función Coincidir fue la respuesta.

Pues bien, los argumentos y la explicación del funcionamiento de estas funciones se presentan a continuación:






valor buscado: es el valor buscado dentro del rango de matriz buscada.
matriz buscada: es un rango donde efectúa la búsqueda.
tipo de coincidencia: si es 0 se busca un valor que sea igual al dato indicado. Si es 1 se busca el mayor valor en un rango que sea menor o igual al dato buscado (el rango debe estar ordenado en forma creciente). Si es -1 busca el valor mayor o igual al dato considerando el rango ordenado decrecientemente.








matriz: es la tabla donde  se efectúa la búsqueda.
núm fila: es un número que especifica la fila de la tabla donde se buscará el valor. Si se omite debe estar presente núm columna.
núm columna: es un número que especifica la columna de la tabla donde se buscará el valor. Si se omite debe estar presente núm fila.

La función COINCIDIR devuelve el número de posición de un dato que coincida con un valor especificado dentro de un rango de datos.




La función INDICE devuelve el valor referido a una fila y columna indicadas dentro de una matriz de datos especificada.


Ahora veamos el ejemplo que hoy planteo. A partir de una matriz de datos se pretende extraer un dato teniendo en cuenta valores de fila y de columna especificados en celdas validadas desde una hoja distinta a la que contiene los datos de la matriz.


Aquí la matriz de datos:


Lo primero que debemos hacer es crear los nombres  para las filas donde están los nombres de los parámetros ("Parámetro"), las columnas donde están los puntos de muestreo ("Punto_muestreo") y  la matriz de donde se extraerá el dato ("matriz") , así:




Y por último el nombre de la matriz:


miércoles, 14 de marzo de 2012

Cómo hacer consultas con la función Indirecto

Descargar el ejemplo

Desde hace algún tiempo he sentido la necesidad de escribir una nota acerca de la función Indirecto, confieso  que en un principio no comprendía su funcionamiento, y por ello decidí investigar. Después de leer publicaciones de algunos autores y probar sus bondades; aquí presento mi propia versión del tema. 

La descripción de esta función dice que:

La función INDIRECTO devuelve un valor de una celda referenciada por una cadena de texto como dirección. 



 
 
ref: es una cadena de texto que representa una referencia de Columna y Fila válida para Excel.
a1: es un valor lógico que indica el tipo de referencia que se acepta en ref, si es VERDADERO u omitido la referencia es del tipo A1 (columna A - fila 1); si es FALSO la referencia posee el estilo L1C1 (fila 1 - columna 1).

Para comprender su funcionamiento, veamos un ejemplo:

Si en la celda D1 digitamos = A1 y en la celda D9 digitamos =INDIRECTO(D1), la función devolverá el valor contenido en la celda A1; es decir 23, puesto que le estoy ordenando a la función que me devuelva el valor contenido en la celda a la que hace referencia la celda D1.


El resultado:


Si digitamos =INDIRECTO("A1") en la celda D24, obtendremos el mismo resultado, dado que al adicionar comillas al texto, estoy indicándole al función que quiero el valor contenido en la celda A1 directamente.




Después de ver el funcionamiento básico, ahora haremos algo más complejo. Buscaremos hacer una consulta de valores mínimos, máximos y promedios de series mensuales, dispuestas en columnas. La consulta se hará desde una hoja diferente a la que contiene los datos mensules.

Este es consolidado de datos ubicado en la hoja 2.


Lo primero que debemos hacer es generar un nombre para cada columna así:

1. Accedemos al administrador de nombres con la combinación Ctrl + F3.


lunes, 5 de marzo de 2012

Uniendo cadenas de caracteres contenidas en celdas

En una nota antigua vimos como extraer parte de un texto a una celda específica (combinación de funciones de texto); en esta nota buscaremos unir cadenas de texto en una celda, y cadenas de texto con valores numéricos contenidos en celdas que presentan formatos especiales.

Existe dos formas de hacerlo. Una forma es separando cada cadena por el carácter ampersand "&" así:


En la celda D7 se encuentra el nombre, y en la celda E7 se encuentra el apellido. Estos caracteres serán unidos con la expresión "Mi nombres es", y así buscaremos presentar un mensaje más completo. Esto es lo que aparece en la imagen anterior. Es necesario recordar que el texto debe incluirse entre dos "".

Si pretendemos unir un texto con una fecha contenidos en celdas diferentes, se debe usar la función:

Donde:



valor: es un número al cual se le aplicará el formato numérico.
formato: es el tipo de formato que se aplica a valor.


domingo, 5 de febrero de 2012

Combinación de Funciones de texto

Antes de sumergirnos en esta nota debo aclarar que Excel no es un software diseñado para edición o redacción de texto; de ahí el apelativo de hoja cálculo. No obstante, existen algunas funciones para realizar algunas operaciones con cadenas de caracteres (texto). La información tipo texto, usualmente proviene de aplicaciones administrativas que generan archivos planos susceptibles de ser modificados con Excel.
En esta nota buscaremos extraer parte de una cadena de texto usando las funciones: Encontrar, Largo y Extrae.






Donde:
Texto buscado: es el carácter o cadena de texto que se desea encontrar dentro de del texto indicado, si se especifica dentro de la función, debe ir entre comillas.
Dentro del texto: es la cadena de texto (o caracteres) en la cual se busca al carácter o un sub texto.
Núm. inicial: es un número que indica la posición desde donde parte la búsqueda de texto buscado en dentro del texto.
Esta función devuelve la posición de los caracteres encontrados en una cadena de texto mayor. Esta función diferencia entre mayúsculas y minúsculas.





Donde:
Texto: es una cadena de texto de la cual se desea saber la cantidad de caracteres que la componen.
Esta función devuelve la cantidad de caracteres que posee una cadena de caracteres.





Texto: es una referencia a una celda, una función que devuelva una cadena de caracteres o un texto entre comillas al cual se le extrae una cantidad de caracteres indicados por núm. de caracteres.
Posición inicial: es un número que indica la posición donde comienza la subcadena a extraer.
Núm. de caracteres: es un valor que indica la cantidad de caracteres que se extraen a partir de posición inicial. 
Esta función devuelve una cantidad de caracteres específicos de una cadena de texto.
A continuación se presenta parte de la base datos de la que se pretende obtener la información puntual.


La idea es extraer de cada celda el nombre, apellido y dirección, y ponerlos en celdas independientes.

viernes, 6 de enero de 2012

La función frecuencia para analizar series de datos



Descargar el archivo

A partir de una serie de datos, a veces resulta necesario conocer el número de casos que están incluidos en rangos sucesivos. Para este tipo de necesidad se puede utilizar la función Frecuencia de manera matricial. Esto último indica que las formulaciones se hacen en un rango de celdas, y por tanto, este debe seleccionarse antes de aplicar la formulación.

La formulación se hace efectiva activando el comando Control+shift+enter. Como resultado se tendra la formulación entre corchetes {}
La sintaxis de la función Frecuencia es:



Datos: conjunto de valores de los cuales de desea contar frecuencias.
Grupos: Matriz o referencia a rangos dentro de los cuales se desea agrupar los valores de datos.
Imaginemos que tenemos 1079 datos de eventos de lluvia media diaria de una cuenca hidrográfica, y que los agruparemos en 16 rangos con amplitud de 4mm por rango. A continuación se presenta el modelo:

martes, 27 de diciembre de 2011

BuscarV para buscar entre rangos


Descargar el ejemplo


La función BuscarV o ConsultaV (versión 2010) sirve para realizar búsquedas en grandes bases de datos, sin ordenar, usando una clave o valor buscado.
La sintaxis es:





Valor_buscado: es el valor por el que se identifica a los datos de la base de datos.
Matriz_buscar_en: es el rango de celdas que contiene la base de datos.
Indicador_columnas : indica el orden que ocupa en la base de datos la columna que 
contiene el valor que se busca.
ordenado: es una variable lógica (0 o 1) que permite elegir si la coincidencia del valor buscado es aproximada o exacta. Para que sea exacta 0 (Falso), aproximado 1 (Verdadero).

Después de describir los argumentos de la función vamos a darle una aplicación poco usada. Se trata de buscar un número de habitantes dentro de una serie de rangos y obtener un número mínimo de muestras diarias a analizar para el parámetro Turbiedad, dependiendo del rango en el que se encuentre el número de habitantes digitado. Este ejemplo se construye a partir del capítulo V de la resolución 2115/2007 (Procesos básicos de control de la calidad del agua para consumo humano).

En este caso el valor buscado no tiene que coincidir exactamente con los valores del rango de celdas (matriz) en donde se hace la búsqueda; por lo que nos ofrece un plus y es definitivamente movernos entre los rangos de valores. Lo anterior se logra asignando el valor 1 o verdadero al argumento Ordenado de la función BuscarV (Para acceder rápidamente a los argumentos de la función sitúese en la celda que contiene la fórmula y ejecute el comando Shift + F3)  . Recordemos que usualmente Buscarv  o consultav se usa para buscar coincidencias exactas.



Este es el modelo de datos: