Parece que hay dos enfoques diferentes para responder a esta pregunta: algunos escritores ofrecen consejos de “mejores prácticas” para programadores experimentados, mientras que otros intentan ofrecer algunas lecciones simples que inspirarían a los principiantes. Voy a seguir este último enfoque, al demostrar cómo podemos usar Microsoft Excel (o una aplicación similar de hoja de cálculo) para enseñar algunos principios básicos en la administración de datos. Las herramientas de productividad como MS Access y MS Excel proporcionan una excelente manera para que los nuevos programadores aprovechen las plataformas que son poderosas (es decir, ofrecen soporte para secuencias de comandos, enlace a fuentes de datos externas, etc.) y están diseñadas para que los principiantes las comprendan (es decir, llenas de asistentes, herramientas de diseño gráfico, plantillas integradas y otras características destinadas a ayudarlo a comenzar a trabajar rápidamente).
En este “tutorial de diez minutos”, presentaré siete conceptos básicos que se pueden aprender usando Excel. Cada una de las siete secciones a continuación debe requerir solo uno o dos minutos para revisar y completar.
Concepto uno: Células, valores y referencias
Pasos:
- Crea una nueva hoja de cálculo en blanco.
- Ingrese un valor en la celda superior izquierda (A1 – la intersección de la columna “A” y la fila “1”. En el ejemplo, ingresé ” 36 “.
- En la celda B1 (una celda a la derecha de “A1″), ingrese esta fórmula: ” = A1 ” y presione la tecla de retorno. El valor que ingresó en la celda A1 ahora también debería mostrarse en la celda B2.
Ideas :
- En Excel, un archivo ( libro de trabajo ) se compone de una o más hojas de cálculo . A su vez, cada hoja de cálculo es una cuadrícula de celdas, donde cada celda tiene su propia identidad única (” dirección “).
- Dentro de cada celda, podemos ingresar un valor codificado (como 36 en la celda A1) o una fórmula, que generalmente hace referencia a los valores de otras celdas. Por ejemplo, en la celda B1 creamos una fórmula básica (con el signo igual) que recupera el valor almacenado en la dirección A1.
- Mientras que la celda en la cuadrícula siempre mostrará el valor final, la barra de fórmulas (arriba a la derecha en la foto de arriba) nos muestra si la celda contiene un valor o una fórmula que hace referencia a los valores de otras celdas.
Concepto Dos: Definiendo Un Rango
Pasos:
- Cree una pequeña “tabla” dentro de una hoja de cálculo ingresando un conjunto de nombres de columnas en una fila consecutiva de celdas. Es posible que desee resaltar estas celdas en negrita (es decir, para que sean fáciles de reconocer como encabezados de columna).
- Debajo de esa fila de encabezado, ingrese una fila de datos que define un “registro” (es decir, un conjunto completo de entradas para cada una de las columnas que anotó anteriormente).
- Continúe ingresando filas / ‘registros’ hasta que haya descrito al menos algunos elementos separados en su tabla.
- Cuando la tabla esté completa, resalte todas las celdas de la tabla que creó. Luego, en el cuadro de referencia de la celda (directamente arriba de la celda A1), ingrese un nombre que describa los datos que ingresó (ejemplo: ” MYRANGE “), y presione la tecla de retorno. Acaba de crear un rango: un conjunto de celdas contiguas a las que se puede hacer referencia usando el nombre que acaba de proporcionar.
Ideas:
- En Excel, todos los datos están esencialmente estructurados en términos de celdas y hojas. En otras palabras, la aplicación no tiene un mecanismo para “saber” qué tipo de datos está ingresando (por ejemplo, si una fila de celdas representa un “registro” que usa un conjunto de valores diferentes para describir una sola entidad, o un conjunto / lista de valores similares donde cada celda representa una medida tomada de una entidad diferente).
- Sin embargo, podemos imponer nuestro propio significado a un grupo de celdas utilizando rangos. Un rango es esencialmente un nombre asignado a un grupo de celdas de dos dimensiones, que indica que esas celdas pertenecen juntas. Se puede usar un rango para indicar una “tabla” (un conjunto de descripciones de entidades discretas, donde cada fila es un “registro” que describe una entidad), o una “matriz” (un conjunto bidimensional de medidas similares; más allá de la columna y encabezados de fila, cada celda en una matriz básicamente representa el mismo tipo de valor).
ALGUNAS CLAVES DE LA TERMINOLOGÍA DE EXCEL
Partes explícitas del modelo de Excel :
- una celda : el ‘bloque de construcción’ básico en Excel, que contiene un valor, una referencia o una función
- una hoja de cálculo : un conjunto de celdas organizadas en dos dimensiones, siguiendo una convención de nomenclatura estándar para columnas y filas integradas (A1, B2, etc.)
- un rango – una sub-cuadrícula (rectángulo) de celdas, que se identifica con un solo nombre (y se trata como un solo valor en algunas funciones)
Conceptos de datos modelados implícitamente en Excel :
- una ” tabla “: un rango utilizado de una manera en la que cada fila de celdas (” registro “) describe una entidad única (y cada columna se refiere a un atributo de esa entidad)
- una ‘ matriz ‘: cada celda de una matriz (además de las de la columna exterior / izquierda y la fila superior) contiene una única medida o valor
- un ‘ vector ‘ – un rango de una sola columna (normalmente anidado dentro de otro rango), que contiene ID u otra información de referencia (útil para hacer coincidir, clasificar, etc.)
Concepto tres: uso de dos rangos para almacenar datos relacionados
Pasos:
- Cree un segundo rango en la hoja de cálculo ingresando una fila de nombres de columna seguidos por varias filas de registros. En este ejemplo, una columna en la nueva tabla (“ID”) contiene valores que hacen referencia a una columna en el rango original (“OfficeID”). Asegúrese de que la columna ID sea la columna más a la izquierda en este nuevo rango.
- Tal como lo hizo antes, asigne un nombre a este rango ingresando ese nombre (” KEYRANGE “) en el cuadro de referencia de la celda, en la parte superior izquierda de la imagen anterior (y presionando Enter ).
Ideas:
Ahora hemos creado dos conjuntos separados de entidades (una lista de “personas” y una lista de “oficinas”), donde las entidades en una tabla hacen referencia a las entidades en la otra tabla usando una referencia de clave externa . En el ejemplo de la imagen de arriba, se asigna un conjunto de números de ID de oficina a cada una de las personas / empleados en el primer rango / tabla. El segundo rango / tabla describe cada asignación de oficina con mayor detalle; en otras palabras, la columna de ID de oficina en el lado izquierdo identifica cada opción de oficina, mientras que los valores en esas otras columnas proporcionan los detalles ( atributos ) con respecto a ese elemento.
Concepto cuatro: asociar los dos rangos usando la búsqueda
Pasos:
- Agregue una columna al primer rango, titulada “Construcción”.
- En esa columna, en la primera fila de datos (celda D2), inserte una fórmula VLOOKUP (” = VLOOKUP (C2, KEYRANGE, 2, FALSE) “).
- “Arrastre y suelte” esa fórmula en la parte inferior del rango de celdas (D2 a D6), colocando primero el cursor en la esquina inferior derecha de la celda D2, hasta que el icono cambie a una cruz; luego haga clic con el botón izquierdo y arrastre el cursor hacia abajo, a través de todas las celdas que desea rellenar con la fórmula. Observe cómo cualquier referencia de celda dentro de la fórmula se actualiza automáticamente , según la ubicación de la nueva fórmula.
Ideas:
- Esta es la primera vez, en este tutorial, en el que hemos utilizado una de las muchas funciones integradas de Excel para definir una fórmula de celda. Como podemos ver, las fórmulas pueden ser más que ecuaciones matemáticas (como ” = A2 + B2 “). En Excel, podemos usar fórmulas para asociar datos en diferentes rangos, de manera similar a escribir consultas en el lenguaje SQL para crear asociaciones entre tablas de bases de datos. (Si no está familiarizado con SQL, es un lenguaje estándar de la industria para el análisis de datos y la administración de bases de datos relacionales).
- Específicamente, en este ejemplo, hemos utilizado la función VLOOKUP para “extraer” información de una “tabla de bucle” / rango de referencia, basado en el valor en una columna de “clave externa” (en este caso, la “ID de Office” columna) en el rango principal. Como puede ver, esta es una forma poderosa de almacenar y relacionar información (especialmente si tiene que mantener varios conjuntos de hojas de cálculo y rangos dentro de un libro de Excel).
Concepto cinco: rangos múltiples que describen el mismo tipo (entidad)
Pasos:
- Copie y pegue los encabezados de columna del primer rango a otro conjunto de celdas en la fila 1 (es decir, la fila que contiene todos los encabezados de columna)
- Comience a ingresar un nuevo conjunto de valores de registro en las celdas / filas debajo del nuevo conjunto de encabezados de columna, como se muestra arriba
- Cuando haya terminado, defina un nuevo rango (” RANGE2 “) de la misma manera que el primer rango
Ideas:
- Si bien estos dos rangos contienen conjuntos de datos separados, describen el mismo tipo de entidad subyacente (ya que los encabezados de columna son en su mayoría o completamente idénticos). Podríamos imaginar que ambos conjuntos de datos podrían combinarse en una sola “tabla de unión”.
- Dado que ambos conjuntos de datos contienen tipos de información idénticos, nuestra principal preocupación sería si hay alguna superposición entre las entradas de la tabla (por ejemplo, si los nombres son únicos o si se asigna más de una persona a la misma persona). persona oficina).
Concepto seis: búsqueda de duplicados (miembros idénticos) dentro de un rango
Pasos:
- Cree una nueva columna en el nuevo rango (“¿Duplicar?”), Y use la fórmula COUNTIF para buscar valores duplicados dentro del rango. La expresión exacta en la celda H2 sería: ” = COUNTIF (RANGE2, G2) “.
- Como hicimos anteriormente: use arrastrar y soltar para copiar esta fórmula en todo el rango en la columna H, para ubicar todas las instancias duplicadas dentro del rango. (Un valor de 1 indica que el valor de la celda correspondiente en la columna G es único; solo se encontró una vez dentro del rango completo de celdas).
- Intente cambiar uno de los valores en la columna G anterior, para que coincida con un valor existente; por ejemplo, intente poner un 5 en la celda G3; el valor en la celda H3 debería cambiar. El valor de dos celdas en la columna H debería cambiar, de 1 a 2 .
Ideas:
Acabamos de realizar una forma muy simple (si es defectuosa) de buscar valores de clave duplicados dentro de un rango. Si encontramos valores clave duplicados (y si nuestras reglas comerciales establecen que “cada ID de oficina debe asignarse a un empleado de la organización, y solo a uno, en cualquier momento”, simplemente habríamos identificado una violación de la empresa Este es un problema de “calidad de los datos” (o “integridad de los datos”). Siempre debemos asegurarnos de que nuestros datos reflejen los procesos subyacentes que describen y no violan ninguna de las reglas que guían esos procesos.
Concepto siete: búsqueda de duplicados en múltiples rangos
Pasos:
- Volveremos a utilizar la función COUNTIF , pero esta vez haremos referencia a un rango ‘externo’, en lugar del mismo rango; queremos descubrir si algún valor clave en un rango se está reutilizando en el otro rango. Vamos a crear un nuevo encabezado de columna en la columna I (“¿ Otra tabla? “).
- En la celda I2, ingresaremos esta fórmula: ” = COUNTIF (MYRANGE, G2) “. Observe que el único cambio de la fórmula en el Concepto Seis anterior es que el nombre del rango (que se está verificando) ahora apunta al otro rango.
- Como hemos hecho anteriormente, arrastre y suelte para copiar la fórmula a las otras celdas de la columna I en las mismas filas que el resto del rango.
Hay otra manera de obtener el mismo resultado: use la función MATCH , en lugar de la función COUNTIF , de la siguiente manera:
Pasos:
- Vamos a crear un nuevo conjunto de valores en la columna J. En la celda J1, ingrese el encabezado de la columna ” Verificar con coincidencia “.
- En la celda J2, ingresaremos la siguiente fórmula: ” = MATCH (G2, C1: C6,0) “. La función MATCH , como se usa aquí, puede devolver dos tipos de valores: un valor entero (que indica la posición en el rango buscado – en este caso, el rango C1: C6 ) o un valor que indica que no se encontró una coincidencia (” NA ” ).
- Como no se encontró ninguna coincidencia (usando los valores del ejemplo anterior), queremos devolver un valor más “legible” que ” NA “; S0 “envolveremos” la llamada de la función MATCH dentro de otra función: ” = ISNA (MATCH (G2, C1: C6,0)) “. La función ISNA devolverá VERDADERO cuando no se encuentre una coincidencia, y FALSO cuando se encuentre una coincidencia (y la función EMPAREJAR devuelve un número entero en lugar de “NA”).
- Si este resultado parece demasiado confuso, si queremos que VERDADERO indique cuando se encuentra un registro coincidente, en lugar de cuando NO se encontró, podemos simplemente ajustar esa llamada de función ISNA con una llamada de función NO (que devuelve el valor booleano opuesto – VERDADERO se vuelve FALSO y viceversa). Así terminamos con: ” = NO (ISNA (PARTIDO (PARTIDO (G2, C1: C6,0))) “.
- Como hicimos anteriormente, simplemente arrastre y suelte para copiar la fórmula a través de todas las celdas de la columna J que están en las mismas filas que el resto del rango.
Ideas:
- Los ejemplos en esta sección muestran que se pueden usar las mismas funciones para comparar valores en el mismo rango o en un rango diferente; la única diferencia es qué rango se especifica (como parámetro) dentro de la fórmula.
- El ejemplo anterior introdujo el concepto de ajuste de funciones : podemos anidar funciones dentro de otras funciones, a fin de construir procesos de varios pasos (algoritmos simples) dentro de una definición de celda de Excel.
- Tenga en cuenta que la función MATCH se refiere a un cierto tipo de rango (como parámetro) – un rango de una sola columna (o de una sola fila), al que nos referimos como un “vector”. Tenga en cuenta que podemos definir rangos dentro de otros rangos ; de hecho, podría ser una buena idea definir cualquier columna (que contenga un conjunto de claves externas o claves primarias [de registro]) que se buscará como su propio rango vectorial.
- También vimos otra sintaxis para especificar un rango. En lugar de primero seleccionar manualmente un conjunto de celdas y luego asignarle un nombre de rango único y reconocido, podemos especificar rangos “sobre la marcha” simplemente identificando sus celdas miembro superior izquierda y derecha (es decir, ” A1: E5 ” define una celda con cinco columnas y cinco filas).
INFORMACIÓN BONUS: Otra forma de encontrar y crear rangos
Como ha visto, los rangos son un concepto importante que debe comprender y usar si planea almacenar datos basados en tablas en sus libros de Excel. Si bien ya hemos demostrado un par de formas diferentes para crear y administrar rangos, también hay una ventana especial, el ” Administrador de nombres “, que facilita la búsqueda, creación, modificación o eliminación de los rangos con nombre:
Busque el ícono del Administrador de nombres dentro de la barra de menú Fórmulas , en la sección del menú llamada ” Nombres definidos “.
Revisión y resumen
Si usted es un principiante en programación, entonces espero que haya podido obtener una comprensión básica de algunos principios básicos de la administración de datos; y si usted es un desarrollador veterano, espero haberle mostrado nuevas formas de hacer que los datos de su empresa sean más accesibles para los gerentes y otros consumidores no técnicos de inteligencia empresarial. Si bien los programadores expertos tienen muchas herramientas de administración de datos a su disposición (que van desde lenguajes de consulta como SQL; a las herramientas ORM y lenguajes como LINQ en .NET Framework; hasta una amplia gama de tecnologías ETL), Excel aún debería ser una herramienta. de elección siempre que necesite analizar cantidades relativamente pequeñas de datos, extraídas de varias fuentes separadas. Siempre debe tener su propio lugar en el kit de herramientas de cada desarrollador corporativo.