INFORMATION TECHNOLOGY APPLICATION TOOLS IN BUSINESS PDF

Preview

Summary

TRIVIA QUESTION 1 Each Excel file is called a workbook because Ø It can contain many sheets including worksheets and chart sheets. FORMATIVE 1 1) ___________is an expression which calculates the value of the cell < FORMULA> 2) ___________is a predefined formula that is made available for use i...

Description

TRIVIA QUESTION 1 1) Each Excel file is called a workbook because Ø It can contain many sheets including worksheets and chart sheets. FORMATIVE 1 1) ___________is an expression which calculates the value of the cell < FORMULA> 2) ___________is a predefined formula that is made available for use in Excel.

3) =SUM (A2, A4, C3, C5) is an example of a ____________.

4) =5*(100-8) is an example of a _____________.

5) Order tracking is for _____________and Sales is for ____________. < ACCESS, EXCEL> 6) _________is better for managing data while ________is better for analyzing data.

LESSON 1: Basic Things About Functions 1) FUNCTION Ø Formula you can use in MS EXCEL to get an automatic result based on the values you enter in that formula. 2) FORMULA VS FUNCTION (starts with =) FORMULA Ø Expression which calculates the value of the cell. Ø A function with an argument ARGUMENT Ø Reference behind the function FUNCTION Ø Predefined formula or the built-in mathematical calculation by excel. Ø Each function has its own name. Ø “Functions are sometimes called formulas” Argument Types 1) Numbers – 1,2,3 2) Text – January 3) Logical Values - True or False 4) Cell References B7 on B7:B20 How can you use Function in Excel 1) Open the Excel Spreadsheet and find Formulas Section and then you will have 2 options. Option A: Insert Function Fx Option B: Click the Category you want Ø Recently Used Ø Financial Ø Logical Ø Text Ø Date/Time Formulas Tab in Excel Ø Name Box Ø Formula Bar Ø Create from Selection Ø Define Name Ø Show Formulas Ø View and Edit Named Ranges

1) ARITHMETIC OPERATORS + PLUS SIGN à ADDITION - MINUS SIGNà SUBTRACTION/ NEGATION * ASTERISK à MULTIPLICATION / FORWARD SLASHà DIVISION % PERCENT SIGNà PERCENT ^ CARETà EXPONENTIATION 2) COMPARISON OPERATORS 1) EQUAL TO = 2) GREATER THAN > 3) LESS THAN < 4) GREATER THAN OR EQUAL TO >= 5) LESS THAN OR EQUAL TO ,>= COMPARISONS

DATABASE VS SPREADSHEET

FEATURES 1) FUNDAMENTALS Ø The basic content in a spreadsheet or a database is a set of data values. 2) TECHNOLOGIES: PROGRAM

DATABASE Stores data values in tables.

3) VOLUME OF DATA

Stores large amounts of data.

4) PROCESSING

Offers a greater range of complexity in terms of data manipulation but must be expressed in Programming or SQL Code. Several people can access the same data set but 2 people cannot alter the same data at the same time. charts and graphs are normally a matter for the application providing access to the data. Databases generally require a higher level of technological expertise.

5) ACCESSING

6) OUTPUT AND PRESENTATION

• • • •

Microsoft Access Oracle MySQL SQL Server

SPREADSHEET Stores data values in cells.

Excel which is part of Ø Microsoft Office Ø Open Office Ø Google Suite Designed to analyze data and sort list items, not for long term storage of raw data. Provides users with a range of automated functions and are accessible to people who do don’t have much technical experience. Access to a spreadsheet is sometimes limited to one person at a time. •

can generate charts and graphsusing automated software tools

ACCESS VS EXCEL BOTH STORES LARGE AMOUNTS OF DATA RUN POWERFUL QUERIES PERFORM SOPHISTICATED CALCULATIONS ACCESS EXCEL 1) Maintain Data Integrity in a format 1) For complex numerical data that you that can be accessed by multiple users. want to analyze in depth. 2) Managing and Storing Data 2) Analyzing Data 3) Helping you keep data organized 3) Performing Complex calculations 4) Easy to Search 4) Exploring Possible Outcomes 5) Available to multiple simultaneous 5) Producing High Quality Charts users COMMON SCENARIOS USING COMMON SCENARIOS USING EXCEL 1. Accounting ACCESS 1. Managing Contacts 2. Budgeting 3. Billing and Sales 2. Inventory and Asset Tracking 4. Reporting 3. Order Tracking 5. Planning 4. Task Tracking 6. Sales 5. Organizing lending libraries 7. Tracking 6. Event Planning 8. Using Calendars 7. Nutrition tracking • • •

USING ACCESS OR EXCEL TO MANAGE YOUR DATA Ø You can always transfer the data from one program to the other, where you can continue to work with it. Ø With or without a data connection, you can bring data into Access from Excel (and vice versa) by copying, importing, or exporting it. SUMMARY 1) Function is a predefined formula that is made available for you to use in Excel. 2) There are 2 options in using Function in Excel: Click “Insert Function” or “Category” 3) Different parts that are involved in using the Formulas Tab in Excel: Name box, Formula bar, View and edit named ranges, Define Name, Show Formulas, and Create from Selection 4) The symbols of the Excel Arithmetic Operators, Comparison Operators, Text and Reference Operators. 5) Operator order

LESSON 2: ARITHMETIC FORMULAS (SUM FUNCTION) SUM FUNCTION • Main purpose to ADD (to sum) all the values you want. • You do that by manually inserting the cells in the formula. WHICH IS THE MAIN BENEFIT OF THIS FORMULA? • You can use Excel as a calculator AND you “drag” the formula to automatically perform more sums in another part of the spreadsheet.

7 ways to use SUM Function 1) Quick Grand Total for a range of cells Ø 1. Select the range of cells, and the blank row below the range, and the blank cells in the column to the right (cells A1:D5 in the example below)

Ø 2. Click the AutoSum button on the Ribbon's Home tab. A SUM formula will be automatically entered for each Total. 2) Sum specific cells 3) Running Total for a Range of Cells • To see a running total in each row of an Excel table, you can use the SUM function, with the starting row locked as an absolute reference. • Instead of calculating a total at the bottom of a range of cells, you can use a formula to show the running total in each row. • In the screen shot below, amounts are entered in column C, and a running total is calculated in column D. • Enter this formula in cell D2, and copy down to cell D6: • =SUM(C$2:C2)

•

• The formula uses an absolute reference to row 2 as the starting point -- C$2 -- and a relative reference to the ending point -- C2

• •

This ensures that the starting point will not change when you copy the formula down to the rows below. Here is the formula in cell D6 -- the starting point has stayed the same and the ending point is in the current row -- C6 =SUM(C$2:C6)

Sum cells that match criteria – SUMIF 4) Sum Specific Items (Match criterion exactly) • You can calculate a total for rows that meet a specific criterion. In this example only the rows with Pen orders will be included in the total. • 1) Select the cell in which you want to see the total • 2) Type an equal sign (=) to start the formula • 3) Type: SUMIF( • 4) Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked • 5) Type a comma, to separate the arguments

• • • • • •

6) Type the criterion. In this example, you're checking for text, so type the word in double quotes: "Pen" • Note: upper and lower case are treated equally 7) Type a comma, to separate the arguments 8) Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed 9) The completed formula is: =SUMIF(A2:A10,"Active",B2:B10) 10) Press the Enter key to complete the entry Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to: =SUMIF(A2:A10, B12, B2:B10) if cell B12 contained the text — pen.

5) Sum Partial Match (Match criterion in a string) • You can add cells that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be summed, because they contain the string "pen". • 1) Select the cell in which you want to see the total (cell A12 in this example) • 2) Type an equal sign (=) to start the formula • 3) Type: SUMIF( • 4) Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked • 5) Type a comma, to separate the arguments • 6) Type the criterion. In this example, you're checking for text, so type the word in double quotes, with one or more asterisk (*) wildcard characters: "*Pen*" • Note: upper and lower case are treated equally

• • • • •

7) Type a comma, to separate the arguments 8) Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed 9) Type a closing bracket. The completed formula is: =SUMIF(A2:A10,"*Pen*",B2:B10) 10) Press the Enter key to complete the entry 11) The result will be 53, the total of rows that contain the string, "Pen“

•

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to: =SUMIF(A2:A10,"*" & B12 & "*",B2:B10) if cell B12 contained the text — pen.

6) Sum Cells that Match Multiple Criteria 7) Subtotal Sum a filtered list

OUTLINE EXERCISE 1: 1) GrandTotal 2) SUM Cells 3) RunTotal 4) SUMIFExact 5) SUMIFWildcard 6) SUMIFS 7)Filtered EXERCISE2: AVERAGE

COUNT1 COUNT

COUNTA

COUNTBLANK

COUNTIF

COUNTIFS

SUMIF,AVERAGEIF,COUNTIF

Gives you the average of the group of values

AVERAGE(number1,number2....

Counts the number of cells that contain numbers Counts the number of cells that are non-blank including empty text. Counts the number of cells that are non blank Counts the number of cells that matches your specified condition Counts the number of cells that matches your specified conditions

COUNT(value1,[value2]..)

COUNTA(value1,[value2]..)

COUNTBLANK(range)

COUNTIF(range,criteria)

COUNTIFS(range1,criteria1,[range2],[criteria2]

EXERCISE 3: MIN AND MAX MIN

MAX

MAXIFS

SUBTOTAL PRODUCT SUMPRODUCT Formula 1

Gets the smallest value from a range of values Gets the largest value from a range of values Gets the max value based on the cells that matches the criteria Returns a Subtotal in a list or database

MIN(number1,[number2],...)

Returns the sum of the products of corresponding ranges or arrays

SUMPRODUCT(array1,[array2],[array3]..)

MAX(number1,[number2],...)

MAXIFS(max_range,criteria_range1,criteria1,...)

SUBTOTAL(function_num,ref1)

SUM PRODUCT Formula 2 EXERCISE 4: ABSOLUTE REFERENCE RAND

RANBETWEEN

MEDIAN

ROUND

Gives you a random number between 0 and 1 Returns a random integer number between the numbers you specify Gets the middle number in the set of the numbers Rounds a number to the nearest

RAND()

RANBETWEEN(bottomnumberltopnumber)

MEDIAN(number1,[number2],...)

ROUND(number;num_digits)

decimal based on your specified number of digits Round a number matching the number of digits you establish but always up, not down. Round a number matching the number of digits you establish but always down, not up.

ROUNDUP

ROUNDDOWN

ROUNDUP(number;digits)

ROUNDDOWN(number;digits)

AVERAGE,MODE,MEDIAN EXERCISE 5: LOOKUP

VLOOKUP (EXACT)2

Looks up a value from a table array or one-row /onecolumn range Searches for a value in the first column of a table array and returns a value in

LOOKUP(lookup_value,lookup_vector,[result_vector])

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

the same row from another column (to the right) in the table array. VLOOKUP (APPROXIMATE)2 Search VLOOKUP for a (NAMED RANGES) value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. VLOOKUP (DROP Search for a DOWN LIST) value in the first column of a table array and returns a value in the same row from another

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

VLOOKUP (MULTIPLE COLUMNS)

column (to the right) in the table array. Search for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.

{SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, [range_lookup]))}

VLOOKUP (MULTIPLE CRITERIA) EXERCISE: 6 HLOOKUP

HLOOKUP2 XLOOKUP1 XLOOKUP2 MATCH

MATCH1 MATCH2 INDEX

Searches for a value in the first row of a table array and returns a value in the same column from another row (downwards) in the table array.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

It returns the position of an item in a range

MATCH(lookup_value, lookup_array, [match_type])

It returns a cell´s value from within a table/range

FILTER

Filters a table array based on the filtering condition given

FILTER(array, include, [if_empty])

Checks if all of the conditions are satisfied or not. Checks if any one of the conditions is satisfied or not. It returns a value that you set if a condition is met, and a value if it is not met It returns a value that you set if a formula has an error

AND(logical1, [logical2], ...)

Checks multiple conditions and returns the value of the first TRUE condition

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

EXERCISE 7: NPV & IRR XNPV & XIRR PMT IMPT NPER RECEIVED EXERCISE 8: PDURATION RATE PV FV CUMPRINC CUMIPMT FVSCHEDULE EXERCISE 9: SLN SYD DB DDB VDB EXERCISE 10: AND OR IF

IFERROR IFERROR WITH VLOOKUP EXERCISE 11: IFNA

OR(logical1, [logical2], ...) IF(Logical Test,Value if True,Value if False) IFERROR(Value,Value if Error)

IFS

SWITCH XOR NOT EXERCISE 12: CLEAN

TEXT SUBSTITUTE

Matches multiple values and returns the first value that has a match

SWITCH(expression, value1, result1, [value2 / default, result2], ...)

Removes all nonprintable characters from text

CLEAN(text)

Substitutes new_text for old_text in a text string

SUBSTITUTE(text, old_text, new_text, [instance num]) REPLACE(old_text, start_num, num_chars, new_text)

REPLACE

Replaces part of a text string, based on the number of characters you specify, with a different text string

FIND

Gets the position of a specific text within another text Gets the position of a specific text within another text and allows wildcards *

FIND(find_text, within_text, [start_num]) SEARCH(find_text, within_text, [start_num])

Extracts a specific number of characters from the middle of a text It returns the first character or characters in a text string, based on the number of characters you specify It returns the last character or characters in a text string, based on the number of characters you specify Capitalizes the first letter in a text string and any other letters in the text that follow a space. Converts all characters in the text into lowercase Converts all characters in the text into uppercase

MID(text, start_num, num_chars)

SEARCH

EXERCISE 13: MID

LEFT

RIGHT

PROPER

LOWER UPPER EXERCISE 14:

LEFT(text, [num_chars])

RIGHT(text, [num_chars])

PROPER(text)

LOWER(text) UPPER(text)

CONCATENATE

TEXTJOIN TRIM

EXACT

LEN VALUE

Joins two or more text strings into one string. The item can be a text value, number, or cell reference.

CONCATENATE(text1, [text2], [text3], ...)

Removes unneeded spaces in your text, except single spaces in between words

TRIM(text)

Compares two texts in a casesensitive manner if they are the same Gives you the number of characters of the text Converts text into a numeric value

EXACT(text1, text2)

LEN(text) VALUE(text)...