Kitabı oku: «Excel y SQL de la mano», sayfa 2
1.4 MICROSOFT EXCEL COMO BASE DE DATOS
Una vez vistas las características principales de las bases de datos, ¿se puede decir que Microsoft Excel es un SGBD?
No, Microsoft Excel no es un sistema de gestión de bases de datos. Aunque hay muchas personas que utilizan Excel para almacenar información, no lo convierte en un SGBD, o sistema de gestión de bases de datos.
Excel no tiene un servicio que controle la inserción o eliminación de los datos, sino que es el mismo usuario quien puede o debe hacerlo directamente en la aplicación.
Otras desventajas de utilizar Excel para almacenar nuestros datos son:
• Solo un usuario puede acceder a la información al mismo tiempo.
• Excel irá más lento a medida que la base de datos crezca.
• No es posible establecer un nivel de seguridad avanzado como para proteger ciertos datos de determinados usuarios.
Pero a pesar de ello, si se diseña y organiza adecuadamente la información dentro de un libro de Excel, se podrán tratar los datos de una manera fácil y eficiente.
A partir de aquí veremos las mejores técnicas para crear y trabajar con bases de datos en Excel que permitan organizar y estructurar adecuadamente la información, dentro de las técnicas del Self Service Business Intelligence.
1.4.1 Requisitos importantes que tener en cuenta
En el momento en el que se quiera construir una base de datos en Excel, habrá que tener en cuenta una serie de puntos para que dicha base de datos funcione:
• No puede haber columnas vacías entre un campo y otro.
• Todas las columnas deben tener un nombre, que será el que se asignará como nombre de campo. Este nombre estará en la primera fila de la base de datos.
• Si no se asignase nombre, habrá que decírselo a Excel ya que, de lo contrario, tomaría como nombre la primera fila de datos o lo asignaría por defecto.
• Tampoco puede haber filas vacías entre un registro y el siguiente, ya que Excel puede tomar la fila vacía como un registro vacío o finalizar la base de datos en el registro anterior a la fila vacía.
Todos estos puntos hay que tenerlos muy en cuenta tanto si se trabaja con las instrucciones propias de bases de datos como cuando se trabaje con SQL.
1.4.2 Las funciones de Excel para bases de datos
Las funciones que se pueden utilizar en Excel para la manipulación de bases de datos son básicamente las siguientes:
| BDCONTAR | Cuenta el número de celdas que contienen números en la base de datos |
| BDCONTARA | Cuenta el número de celdas no vacías de la base de datos |
| BDDESVEST | Calcula la desviación estándar a partir de una muestra de entradas seleccionadas en la base de datos |
| BDDESVESTP | Calcula la desviación estándar en función de la población total de las entradas seleccionadas de la base de datos |
| BDEXTRAER | Extrae de la base de datos un único registro que cumple los criterios especificados |
| BDMAX | Devuelve el valor máximo de las entradas seleccionadas de la base de datos |
| BDMIN | Devuelve el valor mínimo de las entradas seleccionadas de la base de datos |
| BDPRODUCTO | Multiplica los valores de un campo concreto de registros de la base de datos que cumplen los criterios especificados |
| BDPROMEDIO | Devuelve el promedio de las entradas seleccionadas en la base de datos |
| BDSUMA | Agrega los números de la columna de campo de los registros de la base de datos que cumplen los criterios |
| BDVAR | Calcula la varianza a partir de una muestra de entradas seleccionadas de la base de datos |
| BDVARP | Calcula la varianza a partir de la población total de entradas seleccionadas de la base de datos |
Independientemente de estas funciones propias del tratamiento de bases de datos, se podrán utilizar también las funciones de la hoja como:
| BUSCARH | Busca en la fila superior de una matriz y devuelve el valor de la celda indicada |
| BUSCARV | Busca en la primera columna de una matriz y se mueve en horizontal por la fila para devolver el valor de una celda |
| COINCIDIR | Busca valores de una referencia o matriz |
| COLUMNA | Devuelve el número de columna de una referencia |
| FILA | Devuelve el número de fila de una referencia |
| COLUMNAS | Devuelve el número de columnas de una referencia |
| FILAS | Devuelve el número de filas de una referencia |
| DIRECCION | Devuelve una referencia como texto a una sola celda de una hoja de cálculo |
| ELEGIR | Elige un valor de una lista de valores |
| INDICE | Utiliza un índice para elegir un valor de una referencia o matriz |
| INDIRECTO | Devuelve una referencia indicada por un valor de texto |
| TRANSPONER | Devuelve la trasposición de una matriz |
1.4.3 Ejemplos de las funciones de Excel para bases de datos
La mejor manera de entender el funcionamiento de las funciones de bases de datos vistas en el punto anterior es con ejemplos. A continuación, partiendo de una tabla de Excel se va a ver la construcción y funcionamiento de estas funciones sobre una hoja.
La tabla con la que se va a trabajar es la siguiente, situada en las celdas A1 a D7, que consta de cuatro campos y seis filas, más la que sirve de cabecera.
Las condiciones se pueden ver en la columna F y la utilización de las distintas funciones en la columna G. Las fórmulas que están en la columna H son solo la transcripción de las fórmulas de la columna G.
Las funciones indicadas en las celdas G2, G3 y G4 son equivalentes, para poder demostrar las maneras que se pueden implementar. Así, si BDCONTAR tiene tres argumentos, siendo el primero el correspondiente a la constitución de la base de datos, el segundo al nombre del campo y el tercero a la condición, la forma de indicar la base de datos es válida de estas dos maneras:
funciones!$A$1:$D$7 A1:D7
Incluso con un nombre de la tabla, de tenerlo como, por ejemplo, Tabla4[#Todo]
El propio Excel, al realizar la selección de una base de datos en forma de tabla, señala que se ha seleccionado toda con la indicación entre corchetes de [#Todo] después del nombre de la misma.
Se indica así, sin necesidad de ninguna otra intervención del usuario, al señalar la tabla entera.
Todas las otras funciones de base de datos que comienzan con BD se pueden ver en el siguiente gráfico:
Todas estas funciones se han mostrado con una condición única. Pero pueden construirse también con varias condiciones.
Y estas condiciones tanto pueden ser con la condición Y como con la condición O. Bastará con colocarlas en la misma fila o en filas distintas.
Gráficamente sería:
Las condiciones son las que utiliza normalmente Excel:
Todas las funciones de bases de datos tienen la misma estructura en cuanto a los argumentos:
- Dirección de la base de datos: mediante rango directo o mediante nombre de rango o tabla.
- Nombre del campo sobre el que se va a hacer la consulta, filtro, etc. Si hay varios, habrá que indicar solamente el primero. Se puede tomar de donde se quiera o indicarlo directamente.
- Dirección de la condición.
Las condiciones pueden estar establecidas de varias maneras siendo todas ellas válidas, tal como puede verse en el gráfico anterior. No hace falta que en las condiciones estén todos los campos, sino solamente los estrictamente necesarios.
CAPÍTULO 2.
SQL: el lenguaje de las bases de datos
Debido a la diversidad de lenguajes y de bases de datos existentes, la manera de comunicarse entre unos y otras sería realmente complicada de gestionar si no fuese por la existencia de estándares que permiten realizar las operaciones básicas de una forma universal.
De eso es lo que trata el SQL, Structured Query Language, que no es más que un lenguaje estándar de comunicación con bases de datos.
Hablamos, por tanto, de un lenguaje normalizado que permite trabajar con cualquier tipo de lenguaje, sea ASP.NET o PHP para entornos web, en combinación con cualquier tipo de base de datos: MS Access, Excel, SQL Server o MySQL.
En el caso que nos ocupa, acceder a bases de datos desde Excel para recuperar información de manera muy rápida y eficiente de las bases de datos indicadas en el punto anterior se hará con Visual Basic, así como también exportar a ficheros de texto, csv o txt, o dBASE. El lector lo encontrará en los próximos capítulos.
El hecho de que sea estándar no quiere decir que sea idéntico para cada base de datos. Determinadas bases de datos implementan funciones específicas que no tienen necesariamente que funcionar en otras.
Aparte de esta universalidad, el SQL posee otras dos características muy apreciadas. Por una parte, presenta una gran potencia y notable versatilidad que ciertamente contrasta, por otra, con su facilidad de aprendizaje.
2.1. CATEGORÍAS DE DATOS
Como ya se ha dicho en el capítulo anterior, una base de datos está compuesta de tablas donde se almacenan registros con información homogénea, compuestos por distintos campos.
Por tanto, previamente habrá que considerar la naturaleza de los valores que se introducen en estos campos. Dado que una base de datos trabaja con todo tipo de información, es importante especificar qué tipo de valor se introducirá, de manera que, por un parte, se facilite su búsqueda en cualquier momento y, por otra, se optimicen los recursos de memoria.
Cada base de datos trabaja con tipos de valores de campo que no necesariamente están presentes en otras. Sin embargo, existen unas categorías o tipos que están representados en la totalidad de estas bases.
Los más comunes son los siguientes:
| Alfanuméricos | Contienen cifras y letras Tienen una longitud limitada: 255 caracteres |
| Numéricos | Existen varios tipos, aunque los más comunes son los enteros, sin decimales y los reales, con decimales |
| Booleanos | Poseen dos formas: Verdadero y Falso, Sí o No |
| Fechas | Almacenan fechas facilitando su explotación, ya que almacenar fechas posibilita la ordenación de registros cronológicamente o el cálculo de días entre una y otra |
| Memos | Son campos alfanuméricos de longitud ilimitada. Presentan el inconveniente de no poder ser indexados |
| Autoincrementables | Son campos numéricos enteros que incrementan en una unidad su valor para cada registro incorporado. Por tanto, sirven de identificador, ya que resultan exclusivos de un registro |
2.2. TIPOS DE DATOS
Si en el punto anterior se ha visto el tipo de campos que puede haber en una base de datos, aquí se van a enumerar los tipos de datos en SQL Server, en el bien entendido que son los más comunes, pero no por ello compatibles al cien por cien y con una correspondencia exacta en Excel o en otras bases de datos.
| Grupo | Tipo | Intervalo |
| Numéricos exactos | bigint | De -263 a 263 |
| int | De -231 a 231 | |
| smallint | De -215 a 215 | |
| tinyint | De 0 a 255 | |
| bit | Tipo de datos entero que puede aceptar los valores 1, 0 o NULL | |
| decimal, numeric, decimal (p, s) | p (precisión): el número total máximo de dígitos decimales que se puede almacenar, tanto a la izquierda como a la derecha del separador decimal. La precisión debe ser un valor comprendido entre 1 y la precisión máxima de 38. La precisión predeterminada es 18 s (escala): el número máximo de dígitos decimales que se puede almacenar a la derecha del separador decimal. La escala debe ser un valor comprendido entre 0 y p. Solo es posible especificar la escala si se ha especificado la precisión. La escala predeterminada es 0. Con precisión máxima de -1038 y 1038 | |
| money | Tipos de datos que representan valores monetarios o de moneda: de -922 337 203 685.4775808 a 922 337 203 685.4775807 | |
| smallmoney | De -214.7483648 a 214.7483647 | |
| Numéricos aproximados | float | De -2.23E-308, 0 a de 2.23E-308 |
| real | De -3.40E a 3.40E + 38 | |
| Fecha y hora | datetime | Del 1 de enero de 1753 hasta el 31 de diciembre de 9999 |
| smalldatetime | Del 1 de enero de 1900 hasta el 6 de junio de 2079 | |
| Cadenas de caracteres | char (n) | Caracteres no Unicode de longitud fija, con una longitud de n bytes, y n debe ser un valor entre 1 y 8000 |
| varchar (n) | Caracteres no Unicode de longitud variable. n indica que el tamaño de almacenamiento máximo es de 231 bytes | |
| text | En desuso y sustituido por varchar | |
| Cadenas de caracteres unicode | nchar (n) | Datos de carácter Unicode de longitud fija, con n caracteres. n debe estar comprendido entre 1 y 4000 |
| nvarchar (n) | Datos de carácter Unicode de longitud variable. n indica que el tamaño máximo de almacenamiento es 231 bytes | |
| ntext (n) | En desuso, sustituido por nvarchar | |
| Cadenas binarias | binary (n) | Datos binarios de longitud fija con una longitud de n bytes, donde n es un valor que oscila entre 1 y 8000 |
| varbinary (n) | Datos binarios de longitud variable. n indica que el tamaño de almacenamiento máximo es de 231 bytes | |
| image | En desuso, sustituido por varbinary | |
| Otros tipos de datos | cursor | Tipo de datos para las variables o para los parámetros de resultado de los procedimientos almacenados que contiene una referencia a un cursor. Las variables creadas con el tipo de datos cursor aceptan NULL |
| timestamp | Tipo de datos que expone números binarios únicos generados automáticamente en una base de datos El tipo de datos timestamp es simplemente un número que se incrementa y no conserva una fecha o una hora | |
| sql_variant | Tipo de datos que almacena valores de varios tipos de datos aceptados en SQL Server, excepto text, ntext, image, timestamp y sql_variant | |
| uniqueidentifier | Es un GUID (Globally Unique Identifier, Identificador Único Global) | |
| table | Es un tipo de datos especial que se puede utilizar para almacenar un conjunto de resultados para su procesamiento posterior: table se utiliza principalmente para el almacenamiento temporal de un conjunto de filas devuelto como el conjunto de resultados de una función con valores de tabla | |
| xml | Almacena datos de XML. Puede almacenar instancias de xml en una columna o una variable de tipo xml |
Valga la pena repetir que, si se importa una tabla o una consulta de SQL Server a Excel, en la hoja de cálculo habrá la precisión propia de esta. Recordemos que en Excel los valores numéricos tienen una precisión de hasta 15 dígitos. Por tanto, si en una celda se introduce el valor 123456789012345, Excel lo guardará con la precisión indicada, ya que el número consta exactamente de 15 dígitos, pero si se agrega un 6 al final del número anterior para tener el valor numérico 1234567890123456, Excel guardará dicho número con el valor 1234567890123450.
Aunque este tema podría parecer preocupante, la realidad es que muy pocos usuarios de Excel tienen problemas con esta limitación porque nunca se llegan a manejar valores numéricos que requieran más de 15 dígitos.
Ücretsiz ön izlemeyi tamamladınız.
