Short Courses in Computer Software Excel No



Yüklə 24,93 Kb.
tarix12.01.2019
ölçüsü24,93 Kb.
#95763

Short Courses in Computer Software - Excel No. 4



Names 1

Defining Names 1

Assigning Names 1

Lists 2


Vlookup 2

Pivot Tables 4

Selecting data for the PivotTable 5



Names


Instead of referring to a cell or range of cells by the cell reference you can define the cell reference to be a name. This is often much easier to remember.

For example, cells B3 to B9 might contain information about employees’ wages. You could define B3:B9 to be a named range called Wage.

After you have defined a named range, you can insert cells into that range and they will be automatically included in the name.

Defining Names

To create a name;


  • Select the cell/s or range of cells.

  • Use one of the four following methods;

    1. From the Formulas ribbon, in the Defined Names
      group, choose the Create from Selection command.


    2. From the Formulas ribbon, in the Defined Names
      group, choose the Define Names command.


    3. In the Name Box on the Formula Bar.

    4. Right mouse click the selection and choose Define Name

Assigning Names


You can use another method that allows you to reference directly into the cell you wish the code entered. This way you don’t necessarily need the code on the sheet but it is still handy as a reference. You can give individual cells names all at once using Define Assign Names Using the example shown;

To define Assign Names


  • Select the range A1:B7

  • From the Formulas ribbon, in the Define Names group,

  • Choose the Create from Selection command.
    The Create Names from Selection window appears.
    It should have the Left Column ticked
    (if it hasn’t select it and de-select the others.)
    This means it will take the left column as the
    name for the contents of the right cell.
    I.e. B1 will now have the name ”A


  • Click OK



To use Assign Names


  • On another sheet select the cell you want to
    use as a reference. (e.g. A1)


  • Type; =J (don’t forget the =)

  • Press Enter and the amount is now entered

Lists

You use a drop down list in a cell to lookup values in a table.


For example, to use the Vlookup you have to enter the code using the same entries as the first column in the table. If you don’t know what these codes are you can create a list in the cell and choose from a drop down list.

This is handy but has to be done in conjunction with another formula to get a result e.g.vlookup.

There are two stages for this,


  • Give the codes a name and then,

  • Create the cell list.



To give Table a name


If your information is on another Sheet you have to give it a name to use as a reference.

  • Select the codes from the list of the Data sheet
    (i.e. in our example A1 to A7)


  • Click in the Name box (on the Formula bar).

  • Type a name; (e.g. ListCode) and press Enter.
    This is now the name for the range.



To create a list


Next you must create the cell list.

  • Select the cell you want to use as a reference. (e.g. A1)

  • From the Data ribbon, in the Data Tools group, choose the Data Validation command.

  • Click the Settings tab.

    1. In the Allow box, select List from the drop down list.

    2. In the Source box, type the “Name” e.g; =ListCode (don’t forget the =)

  • Click OK



1


To create a list


Select the cell and an arrow  appears next to it.

Click the arrow to view the list.

Vlookup


Vlookup is a formula that finds a reference in the left column of a table and then returns the value in the same row from a column you specify in that table.

Syntax;


VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value    is the value to be found in the first column of the table

Table_array    is the table of information in which data is looked up.

Col_index_num  is the column number in table where the matching value is stored.

Range_lookup    (Optional) is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. Approximate match is default.

Using Vlookup


For this example (shown below), we use a Data sheet (Sheet 1) to hold the table.

The lookup value is in the range A1:B7. The second column B1: B7 is the value required.



  • On another sheet, in the first cell A1 you could enter a letter which would match the letters in the Data sheet Column A (i.e. A to G)

  • In B1 you would enter the Vlookup formula that will compare the contents of A1 and give us a result from the table. B1 is also be formatted for currency.
    The formula would be.
    =VLOOKUP(A1,Sheet2!A1:B7,2)


    1. If A1 contained the letter A then B1 would read $12.37

    2. If A1 contained the letter G then B1 would read $30.61



Pivot Tables


A PivotTable is an interactive worksheet table that can summarise large amounts of existing data using the format and calculating methods you choose. When you create a PivotTable, you specify the data to use as row fields, column fields and Report fields, then the data you want summarised in the data field of the table. Excel automatically uses the Sum function for numeric data and the Count function for non-numeric data.

To set up the PivotTable


  • Click somewhere inside the table of data

  • From the Insert ribbon, in the Tables group, choose PivotTable
    Note the following.


    1. The Create PivotTable dialog box, will open.

    2. The range nominated is all of your data (if it isn’t you need to adjust it)

    3. It has nominated a New Worksheet for the Table.

  • Click OK and note the following.

    1. Two more ribbons are added to the tabs,
      Pivot Tools - Options and Design.


    2. Both the PivotTable field list and instructions
      are displayed. (See diagram. This is a fuill page but narrowed to include all contents)





Selecting data for the PivotTable


You can now use the outline to select the data for the table. You do this by dragging the Heading names at the top of the PivotTable toolbar to the areas below.

To select data for the PivotTable


  • From the PivotTable field list, drag the field that you want to base your PivotTable on (in this example SalesPerson) to the Report Filter area. (See example following).
    Note how it “docks” to the spreadsheet.


1

2

3



Drag three more fields to the Row Labels area Column Labels area, and Values area and watch it build on the spreadsheet;


  • In the PivotTable Field List;

    • The “used” fields have a tick beside them. 

    • The fields appear in their area in the pane below. 

    Your PivotTable (on the spreadsheet)  shows;

    • The totals for each particular heading i.e. Date and Style, for a SalesPerson (in this case All sales people).



To change values


  • Ensure you have clicked anywhere in the table.

  • In the PivotTable Field List, in one of the areas,
    (e.g. Values), click the contents (e.g. Sum of Costs)
    to produce a list.


  • Choose Value Field Settings and the dialog box opens

  • Under Summarize Value Field by, select another type (e.g. Average)

  • Click OK. The PivotTable changes.

  • You can change any field settings.


To change a field


  • Ensure you have clicked anywhere in the table.

  • In the PivotTable Field List, in an area (e.g. Column Labels)

  • Click the field (e.g. Style ) to produce a list.

  • Choose Remove Field and the field is removed

  • Drag another field from the list to the labels area.


Format table Design You can apply a style to the table.

  • From the PivotTable Tools – Design ribbon, in the
    PivotTable Styles group, click the More button to
    show a range of styles.


  • Place your cursor over the styles and watch your
    PivotTable change design.


  • When you find one you like simply click the style.

Yüklə 24,93 Kb.

Dostları ilə paylaş:




Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©muhaz.org 2024
rəhbərliyinə müraciət

gir | qeydiyyatdan keç
    Ana səhifə


yükləyin