Excel y SQL Filtrar registros SELECT

Como conectar Excel con Access, es el post que dio origen a escribir sobre algunas de las sentencias SQL que se pueden utilizar en Excel al trabajar con Access, en las siguientes entregas expuse sobre como filtrar datos trabajando con el comando SELECT y como insertar o agregar datos, modificar y eliminar datos.

Guardar datos en Access es recomendable ya que es algo que realiza muy bien Access, porque es una base de datos específica, si bien Excel se puede usar como base de datos no contiene todas las funcionalidades de una base de datos propiamente dicha como Access; pero es más conveniente a los fines del análisis y manipuleo de datos trabajar con Excel; Como conectar Excel con Access se expuso en forma detallada, pero una vez lograda la conexión se debe escribir comandos para poder recuperar los datos y es aquí donde es preciso saber de SQL y sobre todo saber que comandos escribir para poder filtrar los datos necesarios.

En esta parte del tutorial se expondrá en detalle acerca del comando SELECT que nos permite seleccionar o filtrar los datos necesarios, claro está conjuntamente con otras cláusulas, predicados y demás códigos que se usan en SQL.

Si estás usando macros, o necesitas modificar alguna de ellas para automatizar tu libro de Excel seguramente te es necesario saber seleccionar o referenciar celdas, en FORMAS DE SELECCIONAR O REFERENCIAR CELDAS CON VBA (Spanish Edition) encontrarás una excelente ayuda; si trabajas con fechas en Excel, fíjate en el siguiente link que te puede ser de utilidad FORMATOS Y FUNCIONES DE FECHA Y HORA DE MICROSOFT EXCEL (Spanish Edition)

CONSULTAS
DE SELECCIÓN COMANDO SELECT

Existen varios
tipos de consultas, las que más se desarrollaran por ser las más usada es la
consulta de Selección, no obstante los diferentes tipos de consultas son:

CONSULTAS DE SELECCIÓN COMANDO SELECT

Consultas de
selección (SELECT); este tipo de consultas es la que vamos a desarrollar en los
siguientes párrafos; se usan para indicar al motor de datos que información
debe mostrar de las base de datos, se devuelve como un conjunto de
registros que se pueden almacenar en un objeto recordset, los cuales son
modificables.

La
sintaxis de una consulta de selección es la siguiente:

SELECT Campos
FROM Tabla

Campos: es la
lista de campos que se desean recuperar.

Tabla: es la
tabla de origen de los datos que se desean recuperar.

Consulta simple

La siguiente
consulta devuelve un recordset
que es un conjunto de datos, con el nombre del cliente, apellido de cliente,
teléfono y dirección datos que se encuentran en la tabla Clientes.

SELECT
NombreCliente, ApellidoCliente, Telefono, Dirección FROM Clientes

CONSULTA
CON PREDICADO

El predicado,
sirve para se debe incluir o debe ir entre la cláusula y el primer nombre del
campo del cual se desean recuperar los datos, a final del tutorial  en el título
Subconsultas, se usan, los predicados ANY y SOME, entre los predicados aparte
de los mencionados, tenemos:

Predicado	Descripción
ALL	Devuelve todos los campos de la tabla
TOP	Devuelve un determinado número de registros de la tabla
ANY	Hace referencia a alguno de los registros de la tabla
SOME	Hace referencia a alguno de los registros de la tabla
DISTINCT	Omite los registros cuyos campos seleccionados coincidan totalmente
DISTINCTROW	Omite los registros duplicados basándose en la totalidad del registro y no sólo en los campos seleccionados.

Predicado
ALL

El predicado ALL
devuelve todos los campos de la tabla, si no se incluye el predicado se asume
por defecto ALL, se debe usar con cuidado porque se obliga al motor de base de
datos a analizar toda la tabla para determinar los campos que contiene, es más
apropiado detallar todos los campos que se requieren, se puede utilizar *
(asterisco) para hacer referencia a ALL; las cláusulas siguientes seleccionan
todos los campos de la tabla Clientes.

SELECT ALL FROM Clientes

SELECT * FROM Clientes 

Predicado
TOP

El predicado TOP
devuelve un determinado número de registros, especificado por la cláusula ORDER
BY, si se omite el orden se devolverá la cantidad de
registros especificados, pero en forma aleatoria, siguiendo el
ejemplo supongamos que se requiere devolver los 10 primeros clientes en base al
importe de ventas, este predicado no selecciona entre registros iguales, es
decir si el registro 10 y 11 son iguales se devolverán 11
registros. 

SELECT TOP 10 NombreCliente FROM Clientes ORDER BY Venta ASC 

En el caso que quisiéramos devolver un porcentaje de clientes con mayores
ventas se podrá usar una palabra reservada que es PERCENT, lo cual permite
devolver un porcentaje el ejemplo quedaría así:

 SELECT TOP 10 PERCENT NombreCliente FROM Clientes ORDER BY Venta
ASC 

Predicado
DISTINCT

Este predicado
omite los registros que tienen datos duplicados en el campo o campos
seleccionados, en otras palabras solamente selecciona datos únicos del campo
seleccionado con el comando SELECT, supongamos que existe tres clientes cuyo
nombre sea «Tomy» al incluir esta cláusula solamente se listará uno,
la sentencia del ejemplo sería:

SELECT DISTINCT NombreCliente FROM Clientes

Predicado DISTINCTROW

El predicado
DISTINCTROW devuelve los registros únicos en la tabla, difiere con el anterior
ya que DISTINCT solo se devolvían datos únicos teniendo en cuenta el campo
indicado, en cambio DISTINCTROW analiza todos los campos del registro, para
determinar si es único o no; por ejemplo si en una tabla tenemos dos registros
uno cuyo nombre y apellido de Cliente es «Dayra Sol» y Otro
«Tomy Sol», si utilizamos la
cláusula DISTINCT y seleccionamos el Apellido, nos devolvería un
solo registro ya que el apellido «Sol» se encuentra dos veces; en
cambio con la cláusula DISTINCTROW  devolvería dos registros ya que
el campo Nombre, en este caso no es igual y esta cláusula verifica en todos los
campos.

SELECT
DISTINCTROW NombreCliente FROM Clientes

AS (Alias)

Existen casos
donde se necesita asignar un nombre a determinada columna de un conjunto de
datos devuelto para ello existe la palabra reservada AS que sirve para asignar
un nombre a una columna, un ejemplo de la usando esta palabra reservada sería:

SELECT
DISTINCTROW NombreCliente AS Socio FROM Clientes

IN

Cuando se desea
recuperar datos de una base de datos externa, es decir en una base de datos que
no se encuentra en la cual se ejecuta la consulta se utiliza la palabra
reservada IN para hacer referencia a ella, por ejemplo:

SELECT DISTINCTROW NombreCliente
AS Socio FROM Clientes IN ‘c:dbasepadron.mdb’

CRITERIOS
DE SELECCIÓN

Los criterios de
selección se refieren a las condiciones que deben cumplir los registros para
que sean filtrados, dicho de otra forma es filtrar solo los registros que
cumplan con condiciones preestablecidas, se deben tener presente los siguientes
detalles:

1. Cuando se requiera establecer una condición referida a un campo de tipo
texto, la condición debe ir  entre comillas simples.

2. Las fechas se deben escribir siempre en formato mm-dd-yy, es decir mes, día,
año; la separación habitual de la barra (/) no sirve, se debe usar guion (-);
además que debe ir encerrada entre almohadillas  (#); por ejemplo si se
requiere escribir 12 de Julio de 2015, se debe colocar #06-12-15# o #6-12-15#.

3. No es posible establecer condiciones de búsqueda en campos tipo memo.  

Un ejemplo
aclarará el panorama, la sentencia siguiente expresa que se deben filtrar todos
los campos de la tabla Ventas cuando el campo Venta  se encuentre entre
450000 y 600000; el que le sigue recupera  las ventas entre la fecha
12/01/2012 y el 12/06/2015:

SELECT *FROM Ventas WHERE Venta BETWEEN 45000 AND 60000

SELECT *FROM Ventas WHERE Fecha BETWEEN #01-12-2012#
AND #06-12-2015″

OPERADORES LÓGICOS

El uso de
operadores lógicos sirven para unir dos o más criterios, posee la siguiente sintaxis,
excepto para los operadores IS y NOT:

expresión1 OPERADOR LÓGICO expresión2

Expresión1: es la primera condición a evaluar.

Operador
lógico:
los operadores que se pueden usar son: AND, OR, XOR, EQV, IMP, IS y NOT

Expresión2: es la segunda condición
a evaluar.

Los resultados varían en función del operador lógico a usar, los posibles
resultados se observan en el cuadro que se expone a continuación:

Si a cualquier operador lógico o de comparación le anteponemos el operador NOT
el resultado será el contrario al obtenido sin dicho operador.

IS se emplea para comparar dos variables de tipo objeto, como habíamos dicho anteriormente
su sintaxis difiere del resto de los operadores lógicos, el resultado es
verdadero si los dos objetos son iguales; siendo su sintaxis la siguiente:

objeto1 IS objeto2

Objeto1: es el primer objeto a evaluar.

Operador
lógico (IS):
operador compara dos objetos

Objeto2: es el segundo
objeto a evaluar.

Algunos ejemplos usando los operadores indicados anteriormente, aclararán la
forma de uso; por ejemplo si quisiéramos seleccionar el cliente Dayra
Sol cuyas ventas sean mayores a U$S 8000, podríamos usar la
siguiente sentencia:

SELECT * FROM Ventas WHERE Cliente = ‘Dayra Sol’ AND Venta > 8000

Selecciona el Cliente Dayra Col cuyas ventas sean mayores a 8000 y menores a
10000.

SELECT * FROM Ventas WHERE Cliente = ‘Dayra Sol’ AND Venta > 8000 AND Venta
< 10000

Selecciona
 ventas menores de 2000 y que no sean del cliente Dayra Col o cliente Tomy
Lee.

SELECT * FROM Ventas WHERE Venta < 2000 AND NOT Cliente = ‘Dayra
Sol’ OR NOT Cliente = ‘Tomy Lee’

OPERADORES
DE COMPARACIÓN

Los operadores de
comparación permite comparar dos o más expresiones; ellos son:

OPERADORES
MENOR QUE (<)

Este operador
permite comparar si una expresión es menor a otra, por ejemplo la siguiente
sentencia seleccionas las ventas que cumplen con el criterio de ser menores a
2000.

SELECT * FROM Ventas WHERE Venta < 2000 

OPERADORES
MAYOR QUE (>)

Este operador
permite comparar si una expresión es mayor a otra, por ejemplo la siguiente
sentencia seleccionas las ventas que cumplen con el criterio de ser mayores a
2000.

SELECT * FROM Ventas WHERE Venta > 2000 

OPERADORES
DISTINTO DE (<>)

Este operador
permite comparar si una expresión es distinta a otra, por ejemplo la siguiente
sentencia seleccionas las ventas que sean distintas a 2000.

SELECT * FROM Ventas WHERE Venta <> 2000 

OPERADORES MENOR O IGUAL QUE (<=)

Este operador
permite comparar si una expresión es menor o igual a otra, por ejemplo la
siguiente sentencia seleccionas las ventas que cumplen con el criterio de ser
menores o iguales a 2000.

SELECT * FROM Ventas WHERE Venta <= 2000 

OPERADORES
MAYOR O IGUAL QUE (>=)

Este operador
permite comparar si una expresión es mayor o igual a otra, por ejemplo la
siguiente sentencia seleccionas las ventas que cumplen con el criterio de ser
mayores o iguales a 2000.

SELECT * FROM Ventas WHERE Venta >= 2000 

OPERADORES
IGUAL QUE (=)

Este operador
permite comparar si una expresión es igual a otra, por ejemplo la siguiente
sentencia seleccionas las ventas que cumplen con el criterio de ser iguales a
2000.

SELECT * FROM Ventas WHERE Venta = 2000 

OPERADOR
BETWEEN

Si se precisa
filtrar los datos que se encuentran entre valores o un rango de valores se
puede usar el operador BETWEEN la sintaxis es la siguiente:

campo BETWEEN valor1 AND valor2

Campo: es el campo donde se encuentran los valores a evaluar.

Operador de
comparación (BETWEEN): permite filtrar valores en un rango de datos.

Valor1: es el
primer valor del rango.

Operador lógico
(AND): se debe utilizar este operador para especificar el último valor del
rango

Valor2: es el último
valor del rango. 

La siguiente sentencia extrae o filtra las ventas entre la fecha
12/01/2012 y el 12/06/2015.

SELECT *FROM Ventas WHERE Fecha BETWEEN #01-12-2012# AND #06-12-2015″

También es válido usar: 

SELECT *FROM
Ventas WHERE Fecha > #01-12-2012# AND
 Fecha < #06-12-2015″

Si anteponemos NOT al operador obtendríamos el resultado contrario es decir
todas las ventas que no se encuentran en el rango de fechas 12/01/2012 y
el 12/06/2015.

SELECT *FROM Ventas WHERE Fecha NOT BETWEEN #01-12-2012# AND #06-12-2015″

La siguiente sentencia extrae o filtra las ventas correspondientes al cliente
Dayra Col o cliente Tomy Lee que se encuentren entre la fecha 12/01/2012 y
el 12/06/2015 y a su vez se encuentren en el rango de 450000 y 600000.

SELECT *FROM Ventas WHERE Cliente = ‘Dayra Sol’ OR Cliente = ‘Tomy Lee’
AND Fecha BETWEEN #01-12-2012# AND #06-12-2015″  AND Venta
BETWEEN 450000 AND 600000

SELECT IIF

La función IFF
devuelve uno de dos valores dependiendo de la evaluación de la expresión, la
sintaxis es la siguiente:

SELECT IIF (expresión, valor verdadero, valor falso)

Expresión: es la expresión que se desea evaluar.

Valor verdadero:
es el valor que se devuelve si el análisis de la expresión resulta
verdadera.

Valor falso: es
el valor que se devuelve si el análisis de la expresión resulta
falsa.

El siguiente ejemplo devuelve Excelente si la venta es superior  450000 en
caso contrario devuelve Deficiente.

SELECT IIF (Venta > 450000, ‘Excelente’, ‘Deficientes’ FROM Ventas

OPERADOR LIKE

Este operador se
usa para comparar una expresión de cadena con un modelo en una expresión SQL,
la sintaxis es la siguiente:

Expresión: es la cadena que se desea comparar.

Operador (LIKE):
operador que se usa para comparar la cadena y expresión SQL.

Modelo: es una
cadena o modelo contra lo cual se compara la expresión.

Se pueden usar comodines como % (porcentaje) para especificar una cadena de
caracteres o ? (signo cierre pregunta) para especificar un solo caracter, se
puede usar el operador LIKE para filtrar todos los datos que comienzan con T de
la siguiente forma: LIKE ‘T*’; o por ejemplo los que comienzan con T seguidos
de cualquier letra entre  B y D y de tres dígitos: LIKE ‘T [B-D]###’.
También se puede usar en consultas de parámetros donde el usuario debería
ingresar el modelo, la grilla siguiente muestra como utilizar el operador con
diferentes modelos.  

Por ejemplo en la
sentencia siguiente establece que se deben filtrar todos los registros que cuyo
cliente comience con «Day» y además que la venta se encuentre
entre 12/01/2012 y el 12/06/2015.

SELECT *FROM Ventas WHERE LIKE ‘Day*’ AND Fecha BETWEEN
#01-12-2012# AND #06-12-2015″  

OPERADOR IN

Este operador
devuelve los registros cuyo campo indicado coincide con alguno de la lista
requerida, la sintaxis que requiere el operador es la siguiente:

expresión operador (valor1, valor2, valorN)

Expresión: es lo que se debe analizar

Operador (IN): es
el operador que devuelve los registros que coinciden con los campos indicados.

Valor1: es el campo1

Valor2: es el
campo2

ValorN:
representa el último campo que se requiere.

SELECT * FROM Ventas WHERE Cliente IN (‘Dayra Col’,
‘Tomy Lee’)

FUNCIONES
DE AGREGADO

Son funciones que
vienen incorporadas o agregadas en SQL y permiten realizar operaciones con dos
o más datos de la base de datos, entre ellas tenemos:

FUNCIÓN AVG

Esta función
calcula la media aritmética de un conjunto de datos contenidos en un
campo especificado, es decir la suma de los valores dividido en la cantidad de
valores, los campos nulos no se incluyen; su sintaxis es:

AVG (expresión)

AVG: función de agregado que calcula la media aritmética.

Expresión: campo
del cual se desea calcular la media aritmética.

El siguiente ejemplo calcula la media aritmética del campo Venta denominándolo
Promedio cuando el cliente sea igual a Dayra Col o Tomy Lee.

SELECT Fecha, AVG(Venta) AS Promedio FROM Ventas WHERE Cliente = ‘Dayra
Sol’ OR Cliente = ‘Tomy Lee’ 

FUNCIÓN COUNT

Esta función
cuenta un conjunto de datos contenidos en un campo especificado, los campos
nulos no se incluyen salvo que contengan * (asterisco); puede contar cualquier
tipo de datos incluyendo datos tipo string o texto; la sintaxis es la
siguiente:

COUNT(expresión)

COUNT: función de agregado que cuenta los datos de un campo o tabla.

Expresión: campo
del cual se deben contar los registros, puede ser una tabla, una constante, una
función, excepto las funciones agregadas SQL; se pueden especificar varios
campos, devolviendo un solo valor si al menos alguno de los campos no es nulo,
cada campo se debe separar con ampersand (&). 

El siguiente ejemplo cuenta el total de registros y denomina el campo Total de
la tabla Ventas, sean nulos o no.

SELECT COUNT (*) AS Total FROM Ventas

La siguiente
sentencia SQL cuenta el total de registros de los campos Fecha, Cliente y
Venta denominándolo Total cuando el Cliente es igual
a  Dayra Col o Tomy Lee.

  

SELECT  COUNT(Fecha
& Cliente & Venta) AS Total FROM Ventas WHERE Cliente =
‘Dayra Sol’ OR Cliente = ‘Tomy Lee’ 

FUNCIÓN SUM

Esta función calcula
la suma de un conjunto de valores contenidos en un campo, su sintaxis es:

SUM(expresión)

SUM: función de agregado que devuelve la suma del total de valores de un campo.

Expresión: campo
del cual se debe determinar la suma o expresión que realiza un cálculo
utilizando los datos de los campos, puede ser una tabla, una constante, una
función, excepto las funciones agregadas SQL.

La siguiente sentencia SQL determina la suma de la comisiones del campo
denominado comisión que se forma por el cálculo del campo ventas por 0,10;
cuando el cliente es Dayra Col o Tomy Lee.

SELECT
 SUM(Venta * 0,10) AS Comision FROM Ventas WHERE Cliente = ‘Dayra
Sol’ OR Cliente = ‘Tomy Lee’ 

FUNCIÓN MAX

Esta función
devuelve el valor máximo de un conjunto de valores de un campo especificado, la
sintaxis es la siguiente:

MAX(expresión)

MAX: función de agregado que devuelve el máximo valor.

Expresión: campo
del cual se debe establecer el valor máximo, puede ser una tabla, una
constante, una función, excepto las funciones agregadas SQL.

La siguiente sentencia SQL determina el valor máximo del campo venta cuando el
cliente es Dayra Col o Tomy Lee.

SELECT  MAX
(Venta) AS MaxVentas FROM Ventas WHERE Cliente = ‘Dayra Sol’ OR
Cliente = ‘Tomy Lee’ 

FUNCIÓN MIN

Esta función
devuelve el valor mínimo de un conjunto de valores de un campo especificado, la
sintaxis es la siguiente:

MIN(expresión)

MIN: función de agregado que devuelve el mínimo valor.

Expresión: campo
del cual se debe establecer el valor mínimo, puede ser una tabla, una
constante, una función, excepto las funciones agregadas SQL.

La siguiente sentencia SQL determina el valor mínimo del campo venta cuando el
cliente es Dayra Col o Tomy Lee.

SELECT
 MIN(Venta) AS MinVentas FROM Ventas WHERE Cliente =
‘Dayra Sol’ OR Cliente = ‘Tomy Lee’ 

FUNCIÓN STDEV

Esta función devuelve la
desviación estándar para la población, es decir el total de registros de la
tabla, devuelve nulo si la consulta tiene menos de dos registros; su sintaxis
es:

STDEV(expresión)

STDEV: devuelve la desviación
estándar de una población

Expresión: campo del cual
se debe determinar la desviación estándar o expresión que realiza un cálculo
utilizando los datos de los campos, puede ser una tabla, una constante, una
función, excepto las funciones agregadas SQL.

La siguiente
sentencia SQL determina la desviación estándar del campo venta cuando el
cliente es Dayra Col o Tomy Lee.

SELECT STDEV(Venta)
AS DesvVentas  FROM Ventas WHERE Cliente = ‘Dayra Sol’
OR Cliente = ‘Tomy Lee’ 

FUNCIÓN STDEVP

Esta función devuelve la
desviación estándar para una muestra aleatoria de la población, devuelve
nulo si la consulta tiene menos de dos registros; su sintaxis es:

STDEVP(expresión)

STDEVP: devuelve la
desviación estándar de una muestra de la población.

Expresión: campo del cual
se debe determinar la desviación estándar o expresión que realiza un cálculo
utilizando los datos de los campos, puede ser una tabla, una constante, una
función, excepto las funciones agregadas SQL.

La siguiente
sentencia SQL determina la desviación estándar del campo venta cuando el
cliente es Dayra Col o Tomy Lee.

SELECT STDEVP(Venta)
AS DesvPVentas  FROM Ventas WHERE Cliente = ‘Dayra
Sol’ OR Cliente = ‘Tomy Lee’ 

FUNCIÓN VAR

Esta función devuelve la
varianza de la población, es decir el total de registros de la tabla, devuelve
nulo si la consulta tiene menos de dos registros; su sintaxis es:

VAR(expresión)

VAR: devuelve la
desviación estándar de una población

Expresión: campo del cual
se debe determinar la desviación estándar o expresión que realiza un cálculo
utilizando los datos de los campos, puede ser una tabla, una constante, una
función, excepto las funciones agregadas SQL.

La siguiente
sentencia SQL determina la varianza del campo venta cuando el cliente
es Dayra Col o Tomy Lee.

SELECT VAR(Venta)
AS VarVentas FROM Ventas WHERE Cliente
= ‘Dayra Sol’ OR Cliente = ‘Tomy Lee’ 

FUNCIÓN VARP

Esta función devuelve la
varianza de una muestra aleatoria de la población, devuelve nulo si la
consulta tiene menos de dos registros; su sintaxis es:

VARP(expresión)

VARP: devuelve la
desviación estándar de una muestra de la población

Expresión: campo del cual
se debe determinar la desviación estándar o expresión que realiza un cálculo
utilizando los datos de los campos, puede ser una tabla, una constante, una
función, excepto las funciones agregadas SQL.

La siguiente
sentencia SQL determina la varianza del campo venta cuando el cliente
es Dayra Col o Tomy Lee.

SELECT VARP(Venta)
AS VarPVentas FROM Ventas WHERE Cliente = ‘Dayra Sol’ OR
Cliente = ‘Tomy Lee’ 

Continua en parte I Principales sentencias SQL

Continua en parte II Seleccionar Datos Comando SELECT

Continua en parte III Insertar, actualizar y eliminar Datos Comando INSERT, UPDATE DELETE