Transcript Slide 1

DAY 6:
MICROSOFT EXCEL – CHAPTER 2 CONTD.
MICROSOFT EXCEL – CHAPTER 3
Aliya Farheen
[email protected]
January 29, 2015
ANNOUNCEMENT
• MyITLab Lesson B is due on Monday,
February 9, 2015 by 11:59 pm.
• Homework #2 –Excel is posted on the
website.
• Due date for HW #2 is Friday,
February 13, 2015 by 11:59 pm.
MICROSOFT EXCEL –
CHAPTER 2 CONTD.
LOOKUP FUNCTIONS
• Lookup functions are used to look up
values in a table to perform calculations or
display results
• A Lookup table is a range that contains
data for the basis of the lookup and data to
be retrieved.
CREATE LOOK UP TABLE
• Look Up table should at least contain two
rows and two columns, not including the
headings.
VLOOKUP
• Used to gauge an entered value against a
range of corresponding results
• Well suited for large tables of data, such
as tax tables
• Also to convert the number grades into
letter grades
• Has three mandatory arguments
VLOOKUP(CONTD.)
• Syntax
=VLOOKUP(lookup_value ,table_array ,
col_index_number , [range lookup])
• The lookup value ─ value to look up in a
reference look up table.
• The table_array ─ a range of cells containing
the reference table
• The col_index_number ─ the column
number in the lookup table that contains
return values.
EXAMPLE
Converting the
Number grades to
the letter grades.
Grading Scale
Breakpoint Grade
0
F
60
D
70
C
80
B
90
A
VLOOKUP(E3,$A$3:$B$7,2)
Partial Grade Book
Final
Letter
Grade
Names Score
Abbott
85
B
Carter
69
D
Hon
90
A
Jackson
74
C
Miller
80
B
Nelsen
78
C
HLOOKUP FUNCTION
• The HLOOKUP function is used when the
breakpoints and return data are placed in
rows
• The third argument now lists the row index
• Syntax
=HLOOKUP(lookup_value ,table_array ,
row_index_number , [range lookup])
FINANCIAL FUNCTIONS
• Used for decisions involving payments,
investments, interest rates, etc.
• Allows you to consider several alternatives
PMT FUNCTION
• Used to calculate loan payments
• Has three arguments:
– the interest rate per payment period
– the number of periods
– the amount of the loan
• Computes the associated payment on a
loan
PMT(CONTD.)
• Syntax:
Pmt( interest_rate, number_payments, PV,
[FV],[Type] )
• Example:
– Pmt(7%/12, 2*12, 5000, 0, 0)
– returns the monthly payment on a $5,000 loan
at an annual rate of 7%. The loan is paid off in
2 years (ie: 2 x 12).
– E2 - 7% ; E3 - 24 ; E4 - $5,000.00
– PMT ( E2/12 , E3 , - E4 )
RANGE NAMES
• A range name is a word or phrase used to
identify a cell or cell range
• Range names make formulas easier to
read
• Range names use the following rules:
– 1to 255 characters
– Begin with a letter or underscore (_)
– Contain letters, digits, period, underscore.
CREATING AND MAINTAINING A
RANGE NAME
• Type the range name in the Name Box area
• Enter the name using New Name dialog box
• Name Manager dialog box is used to edit or
delete a range name.
RANK FUNCTION
• The RANK function is useful for producing a
ranking by using a formula
• Syntax
=RANK(number, range, order)
number: the number/cell for which you want to find
the ranking within the “range”
range: range of numbers/cells within which the
“number” will be ranked
order: not really required for our purposes. Leave it
blank do not put anything.
RANK FUNCTION EXAMPLE
• Insert a Rank function
• Type =rank(c3,c3:c8)
• Autofill the formula to remaining cells.
Observe the cell references and make the
references absolute
• =rank(c3,$c$3:$c$8)
CONDITIONAL FORMATTING
• Formats the data in excel sheet if and only
if the data satisfies the condition.
• We wish to apply conditional formatting for
the values in column3
• Select all the values in column3
->Click on conditional formatting in styles group
->Select the type of condition which you want to
give
->Give the condition
MICROSOFT EXCEL –
CHAPTER 3
IMPORTING
• Importing –Inserting data from one
application or file into another.
• Text, CSV, XML and Database files are the
commonly imported files.
• Before importing we have to check if we
want to manage the data as a separate
entity in Excel or you want a connection to
the original data source/application.
TEXT FILES
• The text file has .txt extension and
contains only text (alphabets, numbers
and symbols).
• We must choose a delimiter for importing.
Ex: Space, tab or Comma etc
• Columns align according to the delimiters.
CSV FILES
• CSV abbreviated as Comma-Separated
Values
• Text file with comma as delimiter.
• Excel imports the text between the
commas in the text file into individual cells.
IMPORTING A TEXT FILE
• ->Data Ribbon
->Click “from text” which is in External data group
->Browse the location of text file
->Open the text file
• Text import wizard (3 steps)
–
–
–
–
Select the type that describes the data
Select the type of delimiter in your data
Select the format of column data
Select the location (where to import the data in
your sheet)
IMPORTING AN ACCESS
DATABASE TABLE OR QUERY
• ->Data ribbon
->Click “from access” in External data group
->Browse the location of file
->Open the file
• Import Wizard
– Select the table in database which you want
to import into your sheet
– Select the location where you want to place
the imported data
IMPORTS
• Import data from other sources
Source
Definition
SQL Server
Create a connection to an SQL server
table and import data as a table or
PivotTable.
Analysis Services
Create a connection to an SQL server
analysis services cube, and import data as
a table or PivotTable
XML Data Import
Open or map an XML file into Excel
Data Connection Wizard
Import data for an unlisted format by using
the Data Connection Wizard and OLEDB.
Microsoft Query
Import data for an unlisted format by using
the Microsoft Query Wizard and ODBC.
CREATE A WEB QUERY
• A Web query enables Excel to go to a
particular Web site to obtain information
• Web queries let you extract data from tables
on a Web page
• We need this to display live stock prices,
traffic reports, airport delays etc.
• If the Web address changes, you must
change the Web query and URL to ensure
you have the most accurate information and
citation
• If you have to log in to the Web site, the
query generally will not work
CREATE A WEB QUERY
• Select “FROM WEB” in Get External Data
column.
• Specify the ULR in ADDRESS ( u can see
the page by clicking GO )
• Select IMPORT
• Select cells where to put data
26
CREATE A WEB QUERY
MANAGE CONNECTIONS
• When you import data using the options in
the Get External Data group, Excel
creates a link to the original data source
• Refresh connections periodically to ensure
you have the most up-to-date and
accurate information
• View or remove existing connections
through the Workbook Connections dialog
box
• Refresh Connections
• Display Connections
• Set Connection Properties
MANAGE CONNECTIONS
CONVERT TEXT TO COLUMNS
• In the imported data, the data may not be
structured in a way that meets your needs.
• If data is displayed in one column, use the
Text to Columns command to separate the
data into multiple columns
• Convert Text to Columns Wizard guides
you through the process of separating
data and choosing a delimiting character
CONVERT TEXT TO COLUMNS
TEXT MANIPULATION
• Converting text to columns.
– Select the cell or range which contains the delimited
text
– Go to Data ribbon->Data tools->Text to columns
• Manipulating Text with functions.
– Concatenate Function combines the text
=CONCATENATE(text1,text2)
– Proper Function capitalizes the first letter in a text
string =PROPER(text)
– Upper Function converts the text to upper case
=UPPER(text)
– Lower Function converts all upper case letters to
lower case =LOWER(text)
MANIPULATE TEXT WITH
FUNCTIONS
TEXT MANIPULATION
• Other text functions
Function
Description
TRIM(Text)
Removes leading and trailing spaces in
a text string but maintains spaces
between words in a text string
LEFT(Text,Num_chars)
Returns the specific number of
characters from the start of a text string
RIGHT(Text,Num_chars)
Returns the specified number of
characters from the end of a text string
MID(Text,Start_num,Numchars)
Returns the specified number of
characters from the middle of a text
string, based on a starting position and
length
XML FILES
• XML is eXtensible Markup Language
• Files can contain a significant amount of
machine readable data and allow us to easily
import it into Microsoft Office to work with
• Steps to import
– Go to Data tab->From Other Sources->From XML
Data Import
– Choose the XML file and click OK.
– Choose the cell from where you want the data to
be inserted from the XML file.