Databases



Data Handling – Reinforcement HandoutWhat is a database?A database (DB), in the most general sense, is an organized collection of data. In other words, a database is used by an organization as a method of storing, managing and retrieving information. Modern databases are managed using a database management system (DBMS).Use relative and absolute referencesIn Ms. Excel all the cells have address, for example first cell is known as A1. This address is taken from rows and columns, A is from column and 1 is from row. A cell reference is the “address” of the cell and identifies its location. When you want to copy the same formula to new cells, you can use the relative and absolute references.Relative ReferenceRelative Reference is the cell reference. When you copy a cell that has a formula, the formula changes automatically. The change depends on the relative position of rows and columns.For example, type the contents of columns A and B below and in cell C2, type =A2*B2. If you copy the formula A2*B2 to cell C3, it will become A3*B3. Absolute ReferenceAbsolute reference is used when we want to keep a cell, a row or a column constant when copying a formula. You have to declare it when you create the formula by using the $ (dollar sign). This way you create an absolute reference which doesn’t change when it’s copied or “filled”. $E$1Cell reference will not update while copying or filling.$E1Only row reference will update and column remains the same.E$1Only column reference will update and row remains the same.Format sheet as table:In Excel, a table is a specially designated range of numbers. This special range of numbers has added functionality that other cell ranges do not have. You can have more than one table in a workbook or worksheet if you want, and tables can be as large or small as the amount of data you want to work with. Normally a table is made from adjacent columns of data, with a unique label or heading for each column. Each row in the table should have entries organized according to the column headings. You should keep your table data adjacent in a block to take advantage of all of Excel’s table features. Some Excel features, like filters and PivotTables, will not work correctly if the data is not blocked together in adjacent columns as a table.To create a table from an existing data in your spread sheet: 458343022987000select a range of data in adjacent columns or click any cell within the required data range On the Home Ribbon, Style group, click on the Format as Table button. This will display a menu of table formatting options. If you click on one of the table menu options, the selected range will be formatted as a table based on the style of your choice. 37687255715000When you choose a table format, you will see a Format as Table dialogue box appear. Make sure the cell range shown is the range that you want for your table; Click the OK button to create your table.Advance filters and Sort features:497268515430500Sorting DataIf you have a lot of records, it’s a good idea to sort them because this way it will be easier to find what you are looking for. You can put the data in alphabetical order for text fields or start from the smallest to the largest (and vice versa) for numbers.Choose a column that you want to sort by 420179539687500Click on the Sort & Filter option on the Home Tab and select the order either Ascending or Descending as per your necessity. If there is any data in your other adjacent columns a warning window will appear before performing the sorting option to expand the selection or continue with existing selection.Advance FiltersAdvanced filters can be constructed to get the more control over your data tables.As an example we want to display the records of kids whose Hobby is photography.Click the arrow button next to a field header Hobby.Click Text Filters and then click Custom Filter. In the hobby list click contains and in the text box next to it, type photography. Click OK then as a result, only those records whose Hobby field contains the word photography is displayed.Working with power and Percentage:PowerIn complex calculations there are more than one part to the formula, the order of the calculations is from left to right, but any part of the formula in parentheses will be calculated first.The basic calculations and their symbols in MS ExcelMultiplicationPowerDivisionAdditionSubtractionPercentage*^/+-%Try simple exponent/power calculation using a ^ symbol in your formula answer will be 4.Type the numbers below.Click any cell and type =((C1^A1)-(D1^B1))+((B1/A1)*A1)^2Press enter and you will see the answer as -309.In MS Excel ^ used as an exponent calculation whereas MS Excel default fx POWER(A2,B2) can also be used for the same purpose. Percentage420560541465500275844042545000Values can be change from a decimal number to a percentage by applying the percentage format. Microsoft Excel multiplies the cell by 100 and displays the result with the percentage sign.Click cell B3 and type =B2/D2. Click cell C3 and type =C2/D2.45807091553930027387554000500Select the cells which contain the numbers you want to format, in this case B3 and C3. On the Home tab, in the Number group, click the Percentage Symbol button %.Percentage can be applied through formula directly putting *100 in the end of formula.4102100-127000 Use of advance functions (Count, today, concatenate, left, right, mid, substitute, AND, OR, IF and AND Together)COUNTThe COUNT function is used to calculate the number of cells that contain numbers, this function ignores the text values between the cells and only calculates numeric values.393446020383500Click the cell where you want to create the function, in this example, cell B6.Enter formula =COUNT (B2:B6).Another way of using the COUNT fx is to enter the formula till =COUNT (and then select the cell ranges manually using the mouse dragging.Press enter and the answer will be 4.COUNT formula ignores the value in B5 because it is a text value.TODAYTo have the current date displayed on your worksheet, use the TODAY function.Enter the formula in any cell where you want to enter today’s date =TODAY() CONCATENATEConcatenate function is use to join text values from multiple cell into one cell.Enter the formula in any cell where you want to concatenate the data in this example we have to join three words together, the 1st part of the “We are” and the 2nd part “Pakistani”. In 1st part “we” and “are” already have space in between whereas to give space between the 1st and 2nd part we have to put two double quote with space like this“ ” in the formula, this will refer the formula as a string value of space in outcome. LEFT, RIGHT, MIDThese formulas are used to extract a part of a string (substring) use the LEFT, RIGHT and MID functions.Click cell B10.Go to Formulas tab from office ribbon, in the Function Library group, click Text, and then click MID.In Function Arguments window, in the Text box type A10.It’s the cell from which you are going to extract characters. In the Start Num box, type 4.It’s the position of the first character you want to extract. In the Num chars box, type 9 and then click okYou are specifying how many characters you want to extract.254381042164000 SUBSTITUTEUsing SUBSTITUTE function we can replace the part of text in a cell.Enter the formula =SUBSTITUTE(A1,"Canada","Pakistan")In first part of the formula cell reference will be entered for example A1.Old text will be enter in 2nd part of the formula which has string value input referring “”.New text will be enter in 2nd part of the formula which has string value input referring “”.256730517653000 ANDYou can use the AND function to check if all the conditions of a particular group are met. In this case, Microsoft Excel shows the value TRUE, otherwise FALSE is displayed. In above mentioned example logical expression evaluate the both logics and if both conditions are correct then the outcome will be TRUE and if any condition is incorrect the outcome will be FALSE. Enter the formula in any cell =AND(D2="Mature",E2="Islamabad")You can change the cell references and logics as per your data.If you want to add more conditions in the formula you can use a coma after the logical test as highlighted in the example: =AND (D2="Mature",E2="Islamabad").String values in double quotes “” are case sensitive. ORYou can use the OR function to check if any out of a group of conditions are met. If there is even one condition which is met, Microsoft Excel shows the value TRUE, otherwise, FALSE is displayed. In above mentioned example logical expression evaluate the both logics and if any one of the condition is correct then the outcome will be TRUE and if all the conditions are incorrect then outcome will be FALSE. Enter the formula in any cell =OR(D2="Mature",E2="Lahore")You can change the cell references and logics as per your data.If you want to add more conditions in the formula you can use a coma after the logical test as highlighted in the example: =OR (D2="Mature",E2="Lahore").G5 cells are FALSE because none of the cells D2 and E2 TRUE conditions.String values in double quotes “” are case sensitive. IFExcel’s IF function can often prove to be very useful. You can use this function to branch to different values or actions depending on a specified condition. The structure of an If function is as follows:=IF (logical test, value if true, value if false)In above mentioned example there are 3 important parts of formulaLogical test: cell reference where we want to assess logic. In this example we have set logic testing parameters to “if C2 is greater than or equals to 18”Value if true: what will be the outcome if the logical test is true. In this example we have input a string value in double quotes “Adult”.Value if false: what will be the outcome if the logical test is false. In this example we have input a string value in double quotes “Under age”.IF & AND togetherAND function can be used in a nested form with IF function, it just needs to put in the formula like an equation in another equation:In this above mentioned example student’s result analysis refer to the condition where they have to obtain more than 50% in Course Work and Exam and if any of the head is less than 50 outcome will be FALSE and due to the IF function the string value “Fail” or “Pass” will appear.Enter the formula in any cell =IF(AND(C2>=50,D2>=50),"Pass","Fail")First part of the formula is a simple IF function =IF(AND(C2>=50,D2>=50),"Pass","Fail").In second part AND function is placed in logical test of IF function =IF(AND(C2>=50,D2>=50),"Pass","Fail").String values in double quotes “” are case sensitive.COUNT IFCOUNT IF often use to find out the count of a specific numbers in a data. For example how many students have gained B grade or scored 70% or more than 70% in their results. Enter the formula in any cell =COUNTIF(E2:E7,">=70")First part of the formula is range of cells in which we want to count =COUNTIF(E2:E7,">=70").2nd part of the formula is the criteria for count =COUNTIF(E2:E7,">=70").String values in double quotes “” are not case sensitive.COUNTIF can be used for both numeric and alphabetic data.Multiple IFMULTIPLE IF is occasionally used when several conditions needs to be checked in a single cell, Multiple IF works as a nested IF. For example student’s automatic grading using their percentages. Enter the formula in E2 =IF(E2>=90,"A+",IF(E2>=80,"A",IF(E2>=70,"B",IF(E2>=60,"C","Fail"))))First part of the formula contains the nested IF which defines if the value is in between 90 then declare A+, if the value is between 80 then declare A, if the value is in between 70 then declare B, if the value is in between 60 then declare C and if all these conditions are not met then declare Fail.Values in “” are string data and can be changes as per need.Another example of Multiple IF using other functions within the formula is =IF(AVERAGE(C2:D2)>=80,"Excellent Student",(IF(AVERAGE(C2:D2)>=70,"Good Student",IF(AVERAGE(C2:D2)>=60,"Average Student","Needs Improvement"))))Common Error Messages – Identify the cause of errorIf we do mistake while typing formulas. Ms. Excel will show you different types of errors relevant to the mistake we have made in formula.The most common error messages are: ######This “message” appears when the column is not wide enough to display all of its content. You can correct it by increasing the width of the column to fit content correctly.#DIV/0! This error message appears when you divide something by 0. You can correct it by changing the divider in the function or formula so it is not zero or blank. #NAME?This error message appears when you have typed a wrong formula and MS Excel cannot recognize it. You can correct it by typing the correct formula’s name. #VALUE! Error This error appears when your formula includes cells that contain text and numbers. You can correct it by correcting data types. Advance ChartsCharts in ExcelChart is graphical representation of data and describes the overall analysis visually. There are several type charts in MS Excel for different sorts of data.The Column/Bar Chart is used to illustrate comparisons between a series of data. In a column chart, categories appear horizontally (x-axis) and numeric values appear vertically (y-axis). The opposite happens in a bar chart which is one of the most commonly used chart types.The Line Chart is used to display trends. It shows the changes in data over a period of time. Numeric values always appear vertically (y-axis) and categories horizontally (x-axis). It is suitable for showing data for a large number of groups.The Pie Chart is used to display only one series of data. It shows the relationship of the parts to the whole. You have to pay attention. It is suitable for showing data for one group.To create a column chart, execute the following steps:Select the range of cells for which we want the chart to be plotted.On the Insert tab, in the Charts group, select the chart type.For Column charts select the range A1:A7, hold down CTRL, and select the range C1:D7For Line charts select the range A1:D7For Pie charts Select the range A1:D2Modification of Charts:To modify the charts select the chart and go to Design/Format tabs:Click on Change Colors to change the color theme of chartClick on Quick Layout to select the predefined layoutsClick Add Chart Element, to add further more elements like Chart Title, Data Labels, Legends etc.Every element of the chart is editable, to do that select that element of the chart and go to Format Tab.Conditional Formatting:Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.Highlight Cells RulesTo highlight cells that are greater than a value, execute the following steps.Select the range A1:A10.On the Home tab, in the Styles group, click Conditional Formatting.Click Highlight Cells Rules, Greater Than.Enter the value 80 and select a formatting style.Click OK and Results will be highlighted for cells that are greater than 80.Try applying other sorts of conditional formatting on your data.Import and export data as CSV file:In Ms Excel we can import and export data from many sources to many types of file. One of the most common source for import and export is CSV file. CSV files are comma separated values and can be incorporated in any software or database.To import comma separated (.csv) follow these steps:From office ribbon go to Data Tab and click From Text button212407564770000101346016319500Select the CSV file from file browser1524953185134200After selecting the file select delimited from the Text Import WizardClick next and at this point data can be separated through Tab, Semicolon, Comma, Space and can be specify in Other: box and click next and finish.To export data in CSV simply save as the file in CSV formatOpen an Excel file, on the File tab click Save As and click Browse.Select the file type as CSV and click save.Glossary ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download