How to maintain a database using Excel 2007
Excel allows you to manage lists of all sorts, to retrieve data and to extract statistics. All without programming and in a few clicks.
You have to manage a file of members of an association, a collection of stamps, DVDs or good bottles? For this you have two options. Either you install a software of database management (DBMS) such as Access. But beware: such a program is not cheap (more than Rs. 13000 for Access 2007), or easy to use. The alternative is to use an Excel spreadsheet as widespread.
The latter, in fact, has many functions that allow you to enter or modify data in a collection, to extract information using a variety of criteria, to obtain data on all or part of the basis . Thus, on the basis of films such as the one we have chosen to illustrate this issue, you will see that in a few clicks, you will easily find things in less than 90 min, while this very intuitively and without learning programming language.
But what should you do to convert an Excel spreadsheet into a database? The answer is simple: nothing! Indeed, any list of data is automatically considered as a basis provided that a few simple rules of common sense that we detail in the following pages.
Leaving on good... bases
After reviewing the techniques to capture, edit and monitor data, we will see how to create simple queries, then using the complex functions of the spreadsheet. We detail the PivotTables, this magical summarizing statistical calculations the more complex a few mouse movements. Finally, we will finish this guide by sharing a database on a remote computer. To illustrate these pages, we will use Excel 2007, which brings several new features compared to the previous version. To track and rebuild our manipulation, no need to be an expert in the use of spreadsheet Microsoft at most should know the basics.
Limitations of a database using Excel 2007
The limits of a database with Excel
Excel is not a "real" database manager. While it is ideal for medium-size collections, there are some gaps that prohibit the management of large databases professional
- Number of records limited.
Although it is theoretically possible to enter data in all rows of a spreadsheet (over a million with Excel 2007!), It would be unwise to exceed a few tens of thousands of sheets (one sheet corresponds to a line). Indeed, Excel is working memory, unlike the real DBMS. The smallest crash or any power outage, all your changes are lost.
- Control input rudimentary.
Excel allows you to check the data entered by users. But it continues to be fairly brief. DBMS goes much further.
- One table.
Excel is not done to manage the foundation made up of several tables (eg Customers, Invoices, Stock ...). In fact, you might be achieved in part with cumbersome and complex, but your database would become too heavy.
- No referential integrity.
A DBMS provides referential integrity if it prohibits the removal of a record which still has links with records in other tables. For example: you can not delete a customer if there are still bills to his name. Deleting a file in Excel, not of consistency checks.
Step 1: Enter and verify data
1 - Make sure your list is an Excel
Every table in Excel, is automatically considered as a database if it meets a few simple rules and logic (it is probably already the case in your list):
A header row
The first line of your database (not necessarily line 1 of your Excel sheet) should contain the names of fields, all different. There is a field-by-column and one column per field. A field name must not contain space. We advise you to color the header line to make it more visible.
Only one type of data field
In a field (column), do not type the same data type: text, numbers or dates.
One line per card and a sheet-by-line
One form is the name under which we will designate part of your base (a member of the club, a movie ...)
No row or column blank in the base.
Consistent data
It is simple common sense: Always type in the same way the same data. Thus, Bombay and Mumbai will be considered as two different cities. Finally, when you enter titles (movies, books ...), always put this to the end. The sorting will be facilitated.
2 - Adjust the display
The first step on a large is to "freeze" the first line on the screen. In this way, the field names remain displayed when the leaf scroll. To do this, place the pointer on the first line under the header row in the left column. Activate the View menu. Click the Freeze Panes button, then on the menu with the same name. Take this opportunity to give each column width just need to ensure that all data are visible. To do this, place the insertion point between the letter head of the column and placed on the right. When the pointer becomes a black cross, double click. Repeat the same for the other columns of the base.
3 - Add and edit records
To create new files or modify existing files, you can take the course as you would any table. But it is more pleasant to do so in a form. Paradoxically, the order form, which appeared on the menus of previous versions of Excel, was hidden in the maze of Excel 2007! To make it available, click the small arrow to the right of the Quick Access Toolbar (top left) and select other commands. Pull down the list to the item form, click Add>> and then OK.
4 - Display the form
Place the pointer in any cell of the database and click the form that you just added to the Quick Access Toolbar. The form is displayed. You will not see a sheet at a time, but all fields are visible. To scroll through the database, use the scroll bar or the Back and Forward buttons. You can change values in any field. To validate this modification, press enter, or use one of the Previous or Next buttons.
5 - Add entry controls
In a basic entry, errors are a problem because they make it impossible or inconsistent calculations, selection and sorting. Excel allows you to control the data entered. We want, for example, that in the Price field (movies), can be entered as values between 2 and 30. Click on the header field of Price to select the entire column.
Activate the Data menu and click Data Validation, and then click the Options tab. In the Allow list, select Decimal. In the Data list, select Between ... Finally, type 2 in the Minimum field and 30 in the field possible. Enable the Error Alert tab. In the Title field, type error rate, then in the Error Message field, type the prices must be between 2 and 30. Click OK. Now, typing prohibits a prize will result in displaying an error message. You can, in this way, attribute checks all your fields, including those to contain numbers (prices, dates ...).
Attention, control input only applies to data that you enter: if off-limits numbers (eg prices above 30) are already in the database when you move the control, they will not change.
6 - Impose typing in a list
In the Type field (film), do not enter anything because it would be impossible to find a genre in the base. So if you are looking for films marked "Drama", you will not find those for which you have entered "Dramatic." Here is how to impose on the user input "closed." Create a new worksheet in your workbook by clicking on the button to the right of the tabs at the bottom left. In this new sheet, enter in a column contents allowed one per cell, leaving no empty cell. Check this list and, in the Name box in the upper left, type TheGenres and press OK (you can choose another name, but avoid using the names of the fields in your database).
Return to the worksheet titles, select the entire column D (Styles), open the Data Validation window (see above). In the field Allow, select List. Click in the Source field, press F3 and select TheGenres in the field list. Valid OK twice. Now, when you click in a cell type of the field, a list appears on the right and you can select an item.
7 - Create custom views
If the database must be accessed or modified by several people, all are not necessarily the same data. Thus, the film maker does not wish to display only the title, year, director and distribution, while the accounting wants to see the title, the number in stock (the field) and price. Finally, the seller wants the whole database to be visible (as is the case now). Activate the Display field, click Custom Views and then click Add. Enter Seller and click OK.
To create the view by accounting, hide columns Year, Genre, Director, Duration and Distribution (to hide a column, right-click on the letter to his head and choose Hide). Click Custom Views and then click Add. Enter Accounts and confirm with OK. Create the view of Film Maker on the same principle. Now, to activate a view, click Custom Views, and double-click the desired. Of course you can modify the database when a view is activated.
8 - Make the numbers
A significant novelty of Excel 2007 is its function as conditional formatting, more powerful and easier to use than previous versions. Here's how to bring out in the Price column, the most expensive films. Select column (Price), select the Home menu and click Conditional Formatting, then Bars data. Choose a color and click OK. Excel inserts in each cell colored bar width proportional to its value. You can also opt for a gradient of colors (red for the more expensive green for cheaper).
9 - Sort the contents of the database
Sorting is an essential function for any list of data: it allows, among other things, identify duplicates and to detect input errors. To make a simple sort, for example by years of declining output of films, select any cell in column C, select the Data menu and click on Z.. A for a descending sort (alphabetical, numerical or chronological by data type). Button A.. Z give him an ascending sort. Note: Do not select a field especially of several cells before sorting, because in this case, only the scope will be sorted ... and not other cells. Your base is totally mixed!
10 - Add criteria
By default, Excel does one sort key, which is often inadequate. Thus, in the previous example, if you want all films of the same year are ordered alphabetically by title, click Sort. The first sorting criterion (down by year) is already in place. Click Add and edit a new list until then by securities from A to Z. Then press OK.
In Excel 2007, finally sort accented characters in the "right" place. Thus, the word "variety" will be classified at the sorting, between "center" and "this". With older versions of Excel, the accented characters whose ASCII code is greater than all other letters of the alphabet, were relegated at the end.
11 - Choose a sort order arbitrary
If you sort your database by genre (Comedy, Drama ...), Excel will sort those categories alphabetically (or reverse). Thus, action will be placed before Comedy. You can impose an arbitrary sort order by creating a custom list, for example to place at the top of all categories of films recommended for such young Comedy, Action / Adventure, Musical ...
To do this in a new sheet (or the step 6), enter all kinds of films, in the order you want to impose the sort. Select the list and click the Office button in the upper left and choose Excel Options. In the Standard tab, click Edit custom lists, and then in the new window, click Import. Genres appear. Click OK twice. Finally, repeat the above steps by selecting Sort by Gender, Values, Custom list, select your new list and click OK twice.
12 - Protect data
It is possible to inadvertently overwrite important data in a column by typing a different content to the site. To avoid this inconvenience, you can protect the contents of the base. To do this, select all the cells you do not want the change (note: with previous versions of Excel, it should do the opposite!). Activate the Home menu, click Format and choose Lock the cell.
Once you have checked out all the fields, click the Format button and select Protect Sheet. Check the actions that you allow on the sheet locked (you can, for example, prohibit the insertion of rows or columns). Optionally, type a password and confirm by OK. If necessary, confirm your password. Henceforth, any attempt to write to a protected cell will result in an error message.
Step 2: Find information
1 - Use the Find / Replace
Is the basis of research. Looking, for example, films whose name contains diamond. Select column A (Titles). Activate the Home menu and click on Browse and select, then click Search. Diamond Type and click Next. The first title containing that word is selected. To scan all the others, click Next. The search is not case-sensitive: if you are looking for Diamond, you will also find DIAMOND. In contrast, the accented letters are considered (looking forest, you will not find forest).
2 - Use the form
The form you have activated and used in the previous chapter is not to add or modify files: it allows to find. Consider the following example: looking for movies for more than two hours (120 minutes) and produced after 1998. Click the Form button in the Quick Access Toolbar. When the form is displayed, click Criteria. In the Duration field, type> 120 and, in the Year field, type> 1998. Press Enter. Excel displays (if any) the first movie that meets your criteria. To "scan" the answers, use the Back and Forward buttons, but not the scroll bar, because in this case all records from the basis that scrolled.
3 - Use the filters
Queries made with a form are fairly rudimentary. Basically, they are effective only with numerical comparisons. No way, for example, to search for films with a specific actor, especially if it is in the middle of the distribution list. To do this, you use the automatic filters. Activate the Data menu and click the Filter button. A small black triangle appears in each header field (another click the same button makes them all disappear). For the example treated in the preceding paragraph (movies over 120 minutes produced after 1998), click on the triangle located in the Duration field and select Digital Filters, Above. In the window that appears, enter 120 on the right of more than ... "and click OK.
You notice that the small triangle in the button Time has changed: it now represents a small filter. This lets you know that the information in this column are filtered. In other words, that the base is no longer displayed in full. For the same reason, the line numbers to the left of the screen is blue. Repeat this for only those films whose Year field contains a value greater than 1998. Each time you put a filter on a field, this filter is in addition to those already in place. These filters play a cumulative role, allowing you to gradually refine your search by adding criteria to gradually reduce the number of responses.
4 - Combine the criteria
How to find films with Aishwarya Rai. Click on the triangle located in the Distribution and choose Text Filters (Excel has recognized that this field contains the text and not numbers) and choose Contains. Enter Rai and confirm with OK. You can, in the entry criteria, add other words, for example Rai AND Bachchan or Rai OR Bachchan. In the fields of criteria, such as forms, capital letters are ignored, but not accented.
5 - Find Duplicates
Can you say that you never have before in double (even triple!) Certain records of your database? Probably not. To find out, use the Search for duplicates, another new feature of Excel 2007. Select any cell in your database, select the Data menu and click on Search for duplicates. Then select the fields on which your research. Warning: if you select all, Excel does indicate, as duplicates, the files are strictly identical to the last comma. Better tick only significant fields (eg Title, Director). Finally, click OK. The double sheets are automatically erased.
6 - Use a simple field criteria
For more sophisticated needs (eg looking for three players), we advise you to use criteria fields (we use in the next chapter, devoted to calculations). For example, a customer looking for films which have played one or several of his favorite actors: Akshay kumar, Ajay Devgan and Kajol. Open a sheet in your workbook (or use one you have added to enter the lists ). In a blank, key distribution (it should be the exact name of the field on which your research). Below, type successively *Akshay*, *Ajay* and *Kajol* in three cells. Note that each name is surrounded by stars (we will return). Give these four cells MyCriteria name (no spaces). Return to the worksheet titles, select any cell in the distribution field and click the Advanced button (right of the Sort button). Do not change the contents of the field Beaches, click in the area of criteria and press F3. In the list of field names, MyCriteria then click OK twice.
With our example - if you have not changed - you should find 31 films (the number of results is shown below). The area criterion works as follows: all elements placed in a column are connected by an OR. Excel research therefore films played by Akshay OR Kajol OR Ajay. (Here, OR is to take effect from and/or). As the stars they represent, in the search criteria, any number of characters. Thus, *Akshay* means any number of letters, then Akshay, then any number of letters. Logically: the word Akshay can be anywhere in the list of players. If you enter Akshay instead of *Akshay*, Excel will seek the cards whose field distribution contains only the word Akshay ... and do not find any!
7 - Add a condition
Complicated matters somewhat. The lover of films mentioned in the preceding paragraph is putting less than Rs. 780 per film. We must therefore add a second criteria. Go back to your field of criteria and complete it as discussed previously. Set this field of 2 columns x 4 rows NotTooExpensive name. Back titles on the sheet and repeat the manipulations of the above step in choosing this time the criteria NotTooExpensive. You should find more than 9 movies. The explanation is simple: all elements of the same line in an area of criteria are linked by AND. Excel seeks films that meet the following criteria (containing Actor Akshay AND Price<780)>
Attention to a classic mistake: if you write<780>
8 - Extract a part of the basis
In all the examples you have dealt so far, you get a partial view of the base. If you want to get these results, for example to copy them to another workbook, you must do otherwise. Define your criteria field (see step 6). In the window of defining criteria, select Copy to another location. In the Copy To field, enter the destination address (specifically, the address of the cell placed in the top left of the field to copy the results), then click OK. Note that the scope of results obtained is independent of the initial base. If it is changed, the field results will not be updated.
Reply With Quote
Step 3: Make calculations on the basis
1 - Use the aggregation functions
The calculation as simple as could be done with a collection of data is an aggregation by criteria. You want to, for example, determining the number of films by genre (ie a combination of all the contents of the column Nb). By number of films, we mean the total number of discs and not many different titles. For this you must first sort your base on this criterion. Select a cell in column D, activate the Data menu and click A.. Z. Click Sub-Total.
In the window that appears, scroll down the list each change and choose Genre.... In the Add subtotal to, check Nb (number of films) and uncheck all other boxes. Validate by OK. Look at the left of your screen you can see two vertical lines and, above, three small buttons 1, 2 and 3. This means that Excel has activated the Outline view. Click 2: the spreadsheet hides all the files just to show the totals, the result you want. Now click on 1: only the grand total is displayed. Finally, if you click on 3, all the cards are visible: the display of your database.
2 - Create a calculated field
You now want to obtain the monetary value of your stock, thus increasing the total for each title, its price by the number (Nb) discs. However, there is no Value field in the list. It would indeed be to redundancy in a field whose content is derived from other fields. You will use the column I, hitherto idle (remember: no empty column in a database). In I1, key value, the name of the calculated field. In I2, enter the formula = B2 * E2. Place the cell pointer in the bottom right of I2 (the pointer turns into a black cross) and double-click: the formula is repeated on all the cells in column I. All you have to repeat step 01 but by choosing this time a total of the Value field (which was automatically added to your base). If necessary, hide columns E, F and G for easier reading of results.
3 - Add an arbitrary criterion
Sometimes it is necessary to perform tabulations on a criterion that can not be inferred directly. For example, you want to count the number of securities by the length of film. Unable to use the Subtotal on the duration field because the latter, expressed in minutes, has a different value for almost all films. You would get a total too fragmented and unmanageable. Create a calculated field, you call CatDuration (Class Period), and where you will be a code, eg 1 for films of less than one hour, 2 for films between 61 and 90 minutes, 3 for films between 91 and 120 minutes and 4 for longer films. These thresholds are arbitrary, you can edit at leisure. To obtain this code directly in terms of duration, place the formula = IF (G2 <60,>
4 - Make statistics on your database
The total raised in the previous steps apply to all of the base. If you want to perform calculations on a portion of your list, you must use the statistical functions of a database. These functions, whose name begins with NB (NBSUM, NBAVERAGE ...) are different from their conventional counterparts (SUM, AVERAGE ...) with a capital: they apply only to cells filtered through a zone criteria like the one we saw earlier (see step 2, paragraph "Use a simple field criteria).
For example, you want to know the average price of comedies in your database. Start by giving a name to your database. It is not essential, but it makes the formulas easier to read. In the main sheet (Parts), click in any cell of the base, press Ctrl + * (Ctrl-star) to select all and give him the name MyFilms. Then insert a sheet in your workbook (or use that you added to enter the lists of entry). Type in a cell Genre Comedy and just below. Give these two cells AveragePriceByGenre name (no spaces). Eventually, enter below a label explains, for example, average price for this. Finally, in the cell intended to receive the results, it will enter the formula = BDAVERAGE (MyFilms; "Price"; AveragePriceByGenre). The first argument (MyFilms ) is the extent of the field on which the calculation. The second ( "Price", do not forget the quotation marks) is the field to calculate. The last argument (AveragePriceByGenre) denotes the area of criteria.
Now, for the average of the genre films, type the word in place of Comedy: the result is immediately updated. If a price change in the base, the formula takes into account immediately. We advise you to check the entry in the cell where you type the names of its kind (for that, see the paragraph imposes typing in a list, in step 1). By using areas of complex criteria, such as we have seen above, you can perform any calculation. Excel offers many other functions of this type, for example BDMAX (maximum), BDMIN (minimum), BDNB (number of sheets), BDECARTYPE (standard deviation for statistical calculations).
5 - Create a PivotTable
With PivotTables (PT), a feature already present in previous versions of the spreadsheet, but greatly improved in Excel 2007, you have an extraordinarily effective tool to talk about your numbers. To create such a table, select the worksheet containing your database, select the Insert menu and click PivotTable. Do not change the fields in the dialog box and click OK. You get the blank form to create a PT. The upper zone contains all the field names of your database. We will make a number of films per year of output. Drag the button to the Year Labels lines.
Finally, drag the field to the Number Values. Your PT shows a total field of Nb (thus the total number of disks) for each year.
6 - Add a second dimension
You now want to obtain a state per year, but also by types of films. In the form of PT, to the right of the screen, drag the button to the Genre column labels. Your PT is immediately recalculated.
7 - Reorganize Table
On the same principle, you can add fields in the row labels or column labels or move from field to field to change the criteria for consolidation. On the screen below, we switched fields inserted in the preceding paragraph. You can place multiple fields in one area: you get the sort at several levels, for example years then, in each year by directors.
8 - Ask a filter
You will not want to see that films released after 1990? In the table of values to the left of the screen, click the small triangle to the left of line labels and applying filters to select labels, Greater than or equal to and confirm with OK. In choosing to apply filters values, you could, for example, only the years for which there are more than 60 films in your stock.
9 - Add graphics
With the new Excel 2007, you can make your data more explicit. Return to the main sheet (Parts), activate the Insert menu. Do not click PivotTable, but on the small triangle adjacent to this button, then choose PivotChart. Click OK and choose your data fields as with PT in the previous steps.
Step 4: Sharing a remote
1 - Prepare the base
Sharing a database between multiple remote users has many advantages. Indeed, only one person modifies this base by adding, changing or deleting a file, so that all other immediately have a list up to date. To build such a structure with Excel, you do not need other software. You simply have an Internet connection and a storage space, as proposed in almost all providers. Suppose, for example, that our selection of films is the content of a lending library. By putting the data online, it becomes possible for everyone, both the operator of the library, or a member, whether a particular movie is available.
However, these are not the same for different users. The operator, for example, wish to know the price of the DVD. The member, in contrast, will display the length of the film. Excel allows you to give everyone a display adapted to their needs while using common data. You just need to separate the binder into two parts. A data part, stored in an Excel workbook and put on an online site and a second workbook, linked to the first, but deals only with the 'view' of data.
In the second workbook, you can, for example, for sorting, extracting information, or draw a graph from the data. Each user therefore has a display of its own, customized to their needs and stored locally on the hard disk of a PC. This file must be able to automatically connect to the Internet to obtain the data. Conversely, the workbook data itself is present on your website.
2 - Create the new workbook
To provide such data Excel, just export only leaf binder Securities Films.xlsx in a new workbook. Make sure that the sheet is currently displayed in full, ie without filters, hidden columns or rows, custom displays, or other options ... Right-click the mouse on the Titles tab. Pull down the menu Move or Copy. Select the workbook: (New file), check Create a copy and confirm by OK. Excel creates a new folder named Parts.xlsx.
3 - Place on the Internet
You need a hosting site (and FTP). Most ISPs offer you a personal space than sufficient for this purpose. Alternatively, you can easily create (and free) such a space, eg, Free or on 0fees.net. In Excel and with the workbook Titles.xlsx open, click the Office button and pull down the menu Save As, Other formats. In the File Name box, enter the address of your FTP server in the form ftp://mylogin@serverftp.supplier.com (eg ftp://foo@ftppersonal.free.com). You are then prompted for the password. Check the box Save password and confirm by Sign. The contents of the FTP server is displayed in the window.
Warning: some hosts, the data will not be placed in the root folder, but in a subfolder called web or www. If your base does not appear, So to explore the contents of these files. In the Type box, select Web Page in single file and confirm with Save and then Yes. Excel may ask you to enter the username and password. In this case, make sure that the Anonymous option is not selected and press OK. If this is your first FTP connection, Excel will ask you also to unblock the firewall.
4 - Check which transfer has taken place
Make sure now that the file is available. Launch Internet Explorer and enter the address of your site followed by /titles.mht (eg http://foo.free.com/titles.mht): your browser should give you a list of films. You notice that the data are now in a MHT file extension. It is simply a special HTML file containing text and images from any workbook. In our example, there is no picture, but the principle remains valid.
5 - Create the display file
In Excel, close the workbook and open titles, if not, the workbook Films. Activate the Titles tab and select cell A1. Now, select the Data tab, then click From website. Enter, in the navigation bar, the address you type into your browser (http://foo.free.com/titles.mht). The list appears with yellow arrow next to certain items. Click the second from the top to select all the data. The arrow turns into a green check mark.
The attributes of the original cell, such as background color or the police, are not displayed by default. You can choose to maintain these attributes by clicking the Options button, the option Formatting complete HTML and then clicking OK. Validate by clicking on the Import button.
Excel will ask you to where it needs to insert the data from the Web. Simply click OK to replace the existing title. The data are then fully inserted. It only remains for you to use the sheet "normally", sorting, extraction, graphics or other options on this new piece performed exactly as we described in the previous pages.
6 - Choose the options for updating
By default Excel 2007 does not automatically update the database when a change takes place. Indeed, this update can be applied to a cell in which another person is working, causing some confusion. In addition, Excel strictly control the use of external data in order to prevent copies of unwanted information.
To avoid these messages systematically opening the workbook Films.xlsx, pull down menu, click the Options button and then open the Excel Center Management confidentiality. Click Management Center of confidentiality and Content externally. Check the "Enable all data connections and Enable automatic update for all the routes of this workbook. Validate OK twice. In the workbook titles, open the ribbon and then click Data Connections. Check Update data when opening the file. Confirm with OK. Save the workbook.
Now, when loading a file, based on the local PC will be automatically updated with the read data in the database remotely. If you have set up filters in your workbook, think of them again by clicking in the Data ribbon, on Reapply button.
7 - Update the data file
Files with extension mht are directly readable by Excel 2007. In these conditions, update the data file is simple. Quit and restart Excel to close all current connections, you avoid a few bugs related to the cache. Click the Office button. If the file titles.mht is present in the list of recent documents, simply click it to open. Otherwise, click Open, type the file name in the address of your FTP server (ftp://foo@ftppersonal.free.com/titles.mht) and click OK.
The file appears after a few seconds in Excel. It only remains for you to make corrections, add or delete rows... Save the document normally (by clicking on the disk in the upper left), click Yes. The file is saved directly to your FTP server. For the same reasons as above, close Excel before opening the file and display films.xlsx in the Titles tab changes.
Source : techarena
Excel allows you to manage lists of all sorts, to retrieve data and to extract statistics. All without programming and in a few clicks.
You have to manage a file of members of an association, a collection of stamps, DVDs or good bottles? For this you have two options. Either you install a software of database management (DBMS) such as Access. But beware: such a program is not cheap (more than Rs. 13000 for Access 2007), or easy to use. The alternative is to use an Excel spreadsheet as widespread.
The latter, in fact, has many functions that allow you to enter or modify data in a collection, to extract information using a variety of criteria, to obtain data on all or part of the basis . Thus, on the basis of films such as the one we have chosen to illustrate this issue, you will see that in a few clicks, you will easily find things in less than 90 min, while this very intuitively and without learning programming language.
But what should you do to convert an Excel spreadsheet into a database? The answer is simple: nothing! Indeed, any list of data is automatically considered as a basis provided that a few simple rules of common sense that we detail in the following pages.
Leaving on good... bases
After reviewing the techniques to capture, edit and monitor data, we will see how to create simple queries, then using the complex functions of the spreadsheet. We detail the PivotTables, this magical summarizing statistical calculations the more complex a few mouse movements. Finally, we will finish this guide by sharing a database on a remote computer. To illustrate these pages, we will use Excel 2007, which brings several new features compared to the previous version. To track and rebuild our manipulation, no need to be an expert in the use of spreadsheet Microsoft at most should know the basics.
Limitations of a database using Excel 2007
The limits of a database with Excel
Excel is not a "real" database manager. While it is ideal for medium-size collections, there are some gaps that prohibit the management of large databases professional
- Number of records limited.
Although it is theoretically possible to enter data in all rows of a spreadsheet (over a million with Excel 2007!), It would be unwise to exceed a few tens of thousands of sheets (one sheet corresponds to a line). Indeed, Excel is working memory, unlike the real DBMS. The smallest crash or any power outage, all your changes are lost.
- Control input rudimentary.
Excel allows you to check the data entered by users. But it continues to be fairly brief. DBMS goes much further.
- One table.
Excel is not done to manage the foundation made up of several tables (eg Customers, Invoices, Stock ...). In fact, you might be achieved in part with cumbersome and complex, but your database would become too heavy.
- No referential integrity.
A DBMS provides referential integrity if it prohibits the removal of a record which still has links with records in other tables. For example: you can not delete a customer if there are still bills to his name. Deleting a file in Excel, not of consistency checks.
Step 1: Enter and verify data
1 - Make sure your list is an Excel
Every table in Excel, is automatically considered as a database if it meets a few simple rules and logic (it is probably already the case in your list):
A header row
The first line of your database (not necessarily line 1 of your Excel sheet) should contain the names of fields, all different. There is a field-by-column and one column per field. A field name must not contain space. We advise you to color the header line to make it more visible.
Only one type of data field
In a field (column), do not type the same data type: text, numbers or dates.
One line per card and a sheet-by-line
One form is the name under which we will designate part of your base (a member of the club, a movie ...)
No row or column blank in the base.
Consistent data
It is simple common sense: Always type in the same way the same data. Thus, Bombay and Mumbai will be considered as two different cities. Finally, when you enter titles (movies, books ...), always put this to the end. The sorting will be facilitated.
2 - Adjust the display
The first step on a large is to "freeze" the first line on the screen. In this way, the field names remain displayed when the leaf scroll. To do this, place the pointer on the first line under the header row in the left column. Activate the View menu. Click the Freeze Panes button, then on the menu with the same name. Take this opportunity to give each column width just need to ensure that all data are visible. To do this, place the insertion point between the letter head of the column and placed on the right. When the pointer becomes a black cross, double click. Repeat the same for the other columns of the base.
3 - Add and edit records
To create new files or modify existing files, you can take the course as you would any table. But it is more pleasant to do so in a form. Paradoxically, the order form, which appeared on the menus of previous versions of Excel, was hidden in the maze of Excel 2007! To make it available, click the small arrow to the right of the Quick Access Toolbar (top left) and select other commands. Pull down the list to the item form, click Add>> and then OK.
4 - Display the form
Place the pointer in any cell of the database and click the form that you just added to the Quick Access Toolbar. The form is displayed. You will not see a sheet at a time, but all fields are visible. To scroll through the database, use the scroll bar or the Back and Forward buttons. You can change values in any field. To validate this modification, press enter, or use one of the Previous or Next buttons.
5 - Add entry controls
In a basic entry, errors are a problem because they make it impossible or inconsistent calculations, selection and sorting. Excel allows you to control the data entered. We want, for example, that in the Price field (movies), can be entered as values between 2 and 30. Click on the header field of Price to select the entire column.
Activate the Data menu and click Data Validation, and then click the Options tab. In the Allow list, select Decimal. In the Data list, select Between ... Finally, type 2 in the Minimum field and 30 in the field possible. Enable the Error Alert tab. In the Title field, type error rate, then in the Error Message field, type the prices must be between 2 and 30. Click OK. Now, typing prohibits a prize will result in displaying an error message. You can, in this way, attribute checks all your fields, including those to contain numbers (prices, dates ...).
Attention, control input only applies to data that you enter: if off-limits numbers (eg prices above 30) are already in the database when you move the control, they will not change.
6 - Impose typing in a list
In the Type field (film), do not enter anything because it would be impossible to find a genre in the base. So if you are looking for films marked "Drama", you will not find those for which you have entered "Dramatic." Here is how to impose on the user input "closed." Create a new worksheet in your workbook by clicking on the button to the right of the tabs at the bottom left. In this new sheet, enter in a column contents allowed one per cell, leaving no empty cell. Check this list and, in the Name box in the upper left, type TheGenres and press OK (you can choose another name, but avoid using the names of the fields in your database).
Return to the worksheet titles, select the entire column D (Styles), open the Data Validation window (see above). In the field Allow, select List. Click in the Source field, press F3 and select TheGenres in the field list. Valid OK twice. Now, when you click in a cell type of the field, a list appears on the right and you can select an item.
7 - Create custom views
If the database must be accessed or modified by several people, all are not necessarily the same data. Thus, the film maker does not wish to display only the title, year, director and distribution, while the accounting wants to see the title, the number in stock (the field) and price. Finally, the seller wants the whole database to be visible (as is the case now). Activate the Display field, click Custom Views and then click Add. Enter Seller and click OK.
To create the view by accounting, hide columns Year, Genre, Director, Duration and Distribution (to hide a column, right-click on the letter to his head and choose Hide). Click Custom Views and then click Add. Enter Accounts and confirm with OK. Create the view of Film Maker on the same principle. Now, to activate a view, click Custom Views, and double-click the desired. Of course you can modify the database when a view is activated.
8 - Make the numbers
A significant novelty of Excel 2007 is its function as conditional formatting, more powerful and easier to use than previous versions. Here's how to bring out in the Price column, the most expensive films. Select column (Price), select the Home menu and click Conditional Formatting, then Bars data. Choose a color and click OK. Excel inserts in each cell colored bar width proportional to its value. You can also opt for a gradient of colors (red for the more expensive green for cheaper).
9 - Sort the contents of the database
Sorting is an essential function for any list of data: it allows, among other things, identify duplicates and to detect input errors. To make a simple sort, for example by years of declining output of films, select any cell in column C, select the Data menu and click on Z.. A for a descending sort (alphabetical, numerical or chronological by data type). Button A.. Z give him an ascending sort. Note: Do not select a field especially of several cells before sorting, because in this case, only the scope will be sorted ... and not other cells. Your base is totally mixed!
10 - Add criteria
By default, Excel does one sort key, which is often inadequate. Thus, in the previous example, if you want all films of the same year are ordered alphabetically by title, click Sort. The first sorting criterion (down by year) is already in place. Click Add and edit a new list until then by securities from A to Z. Then press OK.
In Excel 2007, finally sort accented characters in the "right" place. Thus, the word "variety" will be classified at the sorting, between "center" and "this". With older versions of Excel, the accented characters whose ASCII code is greater than all other letters of the alphabet, were relegated at the end.
11 - Choose a sort order arbitrary
If you sort your database by genre (Comedy, Drama ...), Excel will sort those categories alphabetically (or reverse). Thus, action will be placed before Comedy. You can impose an arbitrary sort order by creating a custom list, for example to place at the top of all categories of films recommended for such young Comedy, Action / Adventure, Musical ...
To do this in a new sheet (or the step 6), enter all kinds of films, in the order you want to impose the sort. Select the list and click the Office button in the upper left and choose Excel Options. In the Standard tab, click Edit custom lists, and then in the new window, click Import. Genres appear. Click OK twice. Finally, repeat the above steps by selecting Sort by Gender, Values, Custom list, select your new list and click OK twice.
12 - Protect data
It is possible to inadvertently overwrite important data in a column by typing a different content to the site. To avoid this inconvenience, you can protect the contents of the base. To do this, select all the cells you do not want the change (note: with previous versions of Excel, it should do the opposite!). Activate the Home menu, click Format and choose Lock the cell.
Once you have checked out all the fields, click the Format button and select Protect Sheet. Check the actions that you allow on the sheet locked (you can, for example, prohibit the insertion of rows or columns). Optionally, type a password and confirm by OK. If necessary, confirm your password. Henceforth, any attempt to write to a protected cell will result in an error message.
Step 2: Find information
1 - Use the Find / Replace
Is the basis of research. Looking, for example, films whose name contains diamond. Select column A (Titles). Activate the Home menu and click on Browse and select, then click Search. Diamond Type and click Next. The first title containing that word is selected. To scan all the others, click Next. The search is not case-sensitive: if you are looking for Diamond, you will also find DIAMOND. In contrast, the accented letters are considered (looking forest, you will not find forest).
2 - Use the form
The form you have activated and used in the previous chapter is not to add or modify files: it allows to find. Consider the following example: looking for movies for more than two hours (120 minutes) and produced after 1998. Click the Form button in the Quick Access Toolbar. When the form is displayed, click Criteria. In the Duration field, type> 120 and, in the Year field, type> 1998. Press Enter. Excel displays (if any) the first movie that meets your criteria. To "scan" the answers, use the Back and Forward buttons, but not the scroll bar, because in this case all records from the basis that scrolled.
3 - Use the filters
Queries made with a form are fairly rudimentary. Basically, they are effective only with numerical comparisons. No way, for example, to search for films with a specific actor, especially if it is in the middle of the distribution list. To do this, you use the automatic filters. Activate the Data menu and click the Filter button. A small black triangle appears in each header field (another click the same button makes them all disappear). For the example treated in the preceding paragraph (movies over 120 minutes produced after 1998), click on the triangle located in the Duration field and select Digital Filters, Above. In the window that appears, enter 120 on the right of more than ... "and click OK.
You notice that the small triangle in the button Time has changed: it now represents a small filter. This lets you know that the information in this column are filtered. In other words, that the base is no longer displayed in full. For the same reason, the line numbers to the left of the screen is blue. Repeat this for only those films whose Year field contains a value greater than 1998. Each time you put a filter on a field, this filter is in addition to those already in place. These filters play a cumulative role, allowing you to gradually refine your search by adding criteria to gradually reduce the number of responses.
4 - Combine the criteria
How to find films with Aishwarya Rai. Click on the triangle located in the Distribution and choose Text Filters (Excel has recognized that this field contains the text and not numbers) and choose Contains. Enter Rai and confirm with OK. You can, in the entry criteria, add other words, for example Rai AND Bachchan or Rai OR Bachchan. In the fields of criteria, such as forms, capital letters are ignored, but not accented.
5 - Find Duplicates
Can you say that you never have before in double (even triple!) Certain records of your database? Probably not. To find out, use the Search for duplicates, another new feature of Excel 2007. Select any cell in your database, select the Data menu and click on Search for duplicates. Then select the fields on which your research. Warning: if you select all, Excel does indicate, as duplicates, the files are strictly identical to the last comma. Better tick only significant fields (eg Title, Director). Finally, click OK. The double sheets are automatically erased.
6 - Use a simple field criteria
For more sophisticated needs (eg looking for three players), we advise you to use criteria fields (we use in the next chapter, devoted to calculations). For example, a customer looking for films which have played one or several of his favorite actors: Akshay kumar, Ajay Devgan and Kajol. Open a sheet in your workbook (or use one you have added to enter the lists ). In a blank, key distribution (it should be the exact name of the field on which your research). Below, type successively *Akshay*, *Ajay* and *Kajol* in three cells. Note that each name is surrounded by stars (we will return). Give these four cells MyCriteria name (no spaces). Return to the worksheet titles, select any cell in the distribution field and click the Advanced button (right of the Sort button). Do not change the contents of the field Beaches, click in the area of criteria and press F3. In the list of field names, MyCriteria then click OK twice.
With our example - if you have not changed - you should find 31 films (the number of results is shown below). The area criterion works as follows: all elements placed in a column are connected by an OR. Excel research therefore films played by Akshay OR Kajol OR Ajay. (Here, OR is to take effect from and/or). As the stars they represent, in the search criteria, any number of characters. Thus, *Akshay* means any number of letters, then Akshay, then any number of letters. Logically: the word Akshay can be anywhere in the list of players. If you enter Akshay instead of *Akshay*, Excel will seek the cards whose field distribution contains only the word Akshay ... and do not find any!
7 - Add a condition
Complicated matters somewhat. The lover of films mentioned in the preceding paragraph is putting less than Rs. 780 per film. We must therefore add a second criteria. Go back to your field of criteria and complete it as discussed previously. Set this field of 2 columns x 4 rows NotTooExpensive name. Back titles on the sheet and repeat the manipulations of the above step in choosing this time the criteria NotTooExpensive. You should find more than 9 movies. The explanation is simple: all elements of the same line in an area of criteria are linked by AND. Excel seeks films that meet the following criteria (containing Actor Akshay AND Price<780)>
Attention to a classic mistake: if you write<780>
8 - Extract a part of the basis
In all the examples you have dealt so far, you get a partial view of the base. If you want to get these results, for example to copy them to another workbook, you must do otherwise. Define your criteria field (see step 6). In the window of defining criteria, select Copy to another location. In the Copy To field, enter the destination address (specifically, the address of the cell placed in the top left of the field to copy the results), then click OK. Note that the scope of results obtained is independent of the initial base. If it is changed, the field results will not be updated.
Reply With Quote
Step 3: Make calculations on the basis
1 - Use the aggregation functions
The calculation as simple as could be done with a collection of data is an aggregation by criteria. You want to, for example, determining the number of films by genre (ie a combination of all the contents of the column Nb). By number of films, we mean the total number of discs and not many different titles. For this you must first sort your base on this criterion. Select a cell in column D, activate the Data menu and click A.. Z. Click Sub-Total.
In the window that appears, scroll down the list each change and choose Genre.... In the Add subtotal to, check Nb (number of films) and uncheck all other boxes. Validate by OK. Look at the left of your screen you can see two vertical lines and, above, three small buttons 1, 2 and 3. This means that Excel has activated the Outline view. Click 2: the spreadsheet hides all the files just to show the totals, the result you want. Now click on 1: only the grand total is displayed. Finally, if you click on 3, all the cards are visible: the display of your database.
2 - Create a calculated field
You now want to obtain the monetary value of your stock, thus increasing the total for each title, its price by the number (Nb) discs. However, there is no Value field in the list. It would indeed be to redundancy in a field whose content is derived from other fields. You will use the column I, hitherto idle (remember: no empty column in a database). In I1, key value, the name of the calculated field. In I2, enter the formula = B2 * E2. Place the cell pointer in the bottom right of I2 (the pointer turns into a black cross) and double-click: the formula is repeated on all the cells in column I. All you have to repeat step 01 but by choosing this time a total of the Value field (which was automatically added to your base). If necessary, hide columns E, F and G for easier reading of results.
3 - Add an arbitrary criterion
Sometimes it is necessary to perform tabulations on a criterion that can not be inferred directly. For example, you want to count the number of securities by the length of film. Unable to use the Subtotal on the duration field because the latter, expressed in minutes, has a different value for almost all films. You would get a total too fragmented and unmanageable. Create a calculated field, you call CatDuration (Class Period), and where you will be a code, eg 1 for films of less than one hour, 2 for films between 61 and 90 minutes, 3 for films between 91 and 120 minutes and 4 for longer films. These thresholds are arbitrary, you can edit at leisure. To obtain this code directly in terms of duration, place the formula = IF (G2 <60,>
4 - Make statistics on your database
The total raised in the previous steps apply to all of the base. If you want to perform calculations on a portion of your list, you must use the statistical functions of a database. These functions, whose name begins with NB (NBSUM, NBAVERAGE ...) are different from their conventional counterparts (SUM, AVERAGE ...) with a capital: they apply only to cells filtered through a zone criteria like the one we saw earlier (see step 2, paragraph "Use a simple field criteria).
For example, you want to know the average price of comedies in your database. Start by giving a name to your database. It is not essential, but it makes the formulas easier to read. In the main sheet (Parts), click in any cell of the base, press Ctrl + * (Ctrl-star) to select all and give him the name MyFilms. Then insert a sheet in your workbook (or use that you added to enter the lists of entry). Type in a cell Genre Comedy and just below. Give these two cells AveragePriceByGenre name (no spaces). Eventually, enter below a label explains, for example, average price for this. Finally, in the cell intended to receive the results, it will enter the formula = BDAVERAGE (MyFilms; "Price"; AveragePriceByGenre). The first argument (MyFilms ) is the extent of the field on which the calculation. The second ( "Price", do not forget the quotation marks) is the field to calculate. The last argument (AveragePriceByGenre) denotes the area of criteria.
Now, for the average of the genre films, type the word in place of Comedy: the result is immediately updated. If a price change in the base, the formula takes into account immediately. We advise you to check the entry in the cell where you type the names of its kind (for that, see the paragraph imposes typing in a list, in step 1). By using areas of complex criteria, such as we have seen above, you can perform any calculation. Excel offers many other functions of this type, for example BDMAX (maximum), BDMIN (minimum), BDNB (number of sheets), BDECARTYPE (standard deviation for statistical calculations).
5 - Create a PivotTable
With PivotTables (PT), a feature already present in previous versions of the spreadsheet, but greatly improved in Excel 2007, you have an extraordinarily effective tool to talk about your numbers. To create such a table, select the worksheet containing your database, select the Insert menu and click PivotTable. Do not change the fields in the dialog box and click OK. You get the blank form to create a PT. The upper zone contains all the field names of your database. We will make a number of films per year of output. Drag the button to the Year Labels lines.
Finally, drag the field to the Number Values. Your PT shows a total field of Nb (thus the total number of disks) for each year.
6 - Add a second dimension
You now want to obtain a state per year, but also by types of films. In the form of PT, to the right of the screen, drag the button to the Genre column labels. Your PT is immediately recalculated.
7 - Reorganize Table
On the same principle, you can add fields in the row labels or column labels or move from field to field to change the criteria for consolidation. On the screen below, we switched fields inserted in the preceding paragraph. You can place multiple fields in one area: you get the sort at several levels, for example years then, in each year by directors.
8 - Ask a filter
You will not want to see that films released after 1990? In the table of values to the left of the screen, click the small triangle to the left of line labels and applying filters to select labels, Greater than or equal to and confirm with OK. In choosing to apply filters values, you could, for example, only the years for which there are more than 60 films in your stock.
9 - Add graphics
With the new Excel 2007, you can make your data more explicit. Return to the main sheet (Parts), activate the Insert menu. Do not click PivotTable, but on the small triangle adjacent to this button, then choose PivotChart. Click OK and choose your data fields as with PT in the previous steps.
Step 4: Sharing a remote
1 - Prepare the base
Sharing a database between multiple remote users has many advantages. Indeed, only one person modifies this base by adding, changing or deleting a file, so that all other immediately have a list up to date. To build such a structure with Excel, you do not need other software. You simply have an Internet connection and a storage space, as proposed in almost all providers. Suppose, for example, that our selection of films is the content of a lending library. By putting the data online, it becomes possible for everyone, both the operator of the library, or a member, whether a particular movie is available.
However, these are not the same for different users. The operator, for example, wish to know the price of the DVD. The member, in contrast, will display the length of the film. Excel allows you to give everyone a display adapted to their needs while using common data. You just need to separate the binder into two parts. A data part, stored in an Excel workbook and put on an online site and a second workbook, linked to the first, but deals only with the 'view' of data.
In the second workbook, you can, for example, for sorting, extracting information, or draw a graph from the data. Each user therefore has a display of its own, customized to their needs and stored locally on the hard disk of a PC. This file must be able to automatically connect to the Internet to obtain the data. Conversely, the workbook data itself is present on your website.
2 - Create the new workbook
To provide such data Excel, just export only leaf binder Securities Films.xlsx in a new workbook. Make sure that the sheet is currently displayed in full, ie without filters, hidden columns or rows, custom displays, or other options ... Right-click the mouse on the Titles tab. Pull down the menu Move or Copy. Select the workbook: (New file), check Create a copy and confirm by OK. Excel creates a new folder named Parts.xlsx.
3 - Place on the Internet
You need a hosting site (and FTP). Most ISPs offer you a personal space than sufficient for this purpose. Alternatively, you can easily create (and free) such a space, eg, Free or on 0fees.net. In Excel and with the workbook Titles.xlsx open, click the Office button and pull down the menu Save As, Other formats. In the File Name box, enter the address of your FTP server in the form ftp://mylogin@serverftp.supplier.com (eg ftp://foo@ftppersonal.free.com). You are then prompted for the password. Check the box Save password and confirm by Sign. The contents of the FTP server is displayed in the window.
Warning: some hosts, the data will not be placed in the root folder, but in a subfolder called web or www. If your base does not appear, So to explore the contents of these files. In the Type box, select Web Page in single file and confirm with Save and then Yes. Excel may ask you to enter the username and password. In this case, make sure that the Anonymous option is not selected and press OK. If this is your first FTP connection, Excel will ask you also to unblock the firewall.
4 - Check which transfer has taken place
Make sure now that the file is available. Launch Internet Explorer and enter the address of your site followed by /titles.mht (eg http://foo.free.com/titles.mht): your browser should give you a list of films. You notice that the data are now in a MHT file extension. It is simply a special HTML file containing text and images from any workbook. In our example, there is no picture, but the principle remains valid.
5 - Create the display file
In Excel, close the workbook and open titles, if not, the workbook Films. Activate the Titles tab and select cell A1. Now, select the Data tab, then click From website. Enter, in the navigation bar, the address you type into your browser (http://foo.free.com/titles.mht). The list appears with yellow arrow next to certain items. Click the second from the top to select all the data. The arrow turns into a green check mark.
The attributes of the original cell, such as background color or the police, are not displayed by default. You can choose to maintain these attributes by clicking the Options button, the option Formatting complete HTML and then clicking OK. Validate by clicking on the Import button.
Excel will ask you to where it needs to insert the data from the Web. Simply click OK to replace the existing title. The data are then fully inserted. It only remains for you to use the sheet "normally", sorting, extraction, graphics or other options on this new piece performed exactly as we described in the previous pages.
6 - Choose the options for updating
By default Excel 2007 does not automatically update the database when a change takes place. Indeed, this update can be applied to a cell in which another person is working, causing some confusion. In addition, Excel strictly control the use of external data in order to prevent copies of unwanted information.
To avoid these messages systematically opening the workbook Films.xlsx, pull down menu, click the Options button and then open the Excel Center Management confidentiality. Click Management Center of confidentiality and Content externally. Check the "Enable all data connections and Enable automatic update for all the routes of this workbook. Validate OK twice. In the workbook titles, open the ribbon and then click Data Connections. Check Update data when opening the file. Confirm with OK. Save the workbook.
Now, when loading a file, based on the local PC will be automatically updated with the read data in the database remotely. If you have set up filters in your workbook, think of them again by clicking in the Data ribbon, on Reapply button.
7 - Update the data file
Files with extension mht are directly readable by Excel 2007. In these conditions, update the data file is simple. Quit and restart Excel to close all current connections, you avoid a few bugs related to the cache. Click the Office button. If the file titles.mht is present in the list of recent documents, simply click it to open. Otherwise, click Open, type the file name in the address of your FTP server (ftp://foo@ftppersonal.free.com/titles.mht) and click OK.
The file appears after a few seconds in Excel. It only remains for you to make corrections, add or delete rows... Save the document normally (by clicking on the disk in the upper left), click Yes. The file is saved directly to your FTP server. For the same reasons as above, close Excel before opening the file and display films.xlsx in the Titles tab changes.
Source : techarena
No comments:
Post a Comment