html5-img
1 / 44

Excel Workshop

Excel Workshop. Presented by: California Actuarial League. Basics, Formats, and Formulas. Eric Chao. Interface. Worksheet and Workbook Rows and Columns Excel 2003: Rows from 1 to 65536, Columns from A to IV Resizing – Auto, Manual, Exact Cells Edit cells – F2 or double-click

aron
Télécharger la présentation

Excel Workshop

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel Workshop Presented by: California Actuarial League

  2. Basics, Formats, and Formulas Eric Chao

  3. Interface • Worksheet and Workbook • Rows and Columns • Excel 2003: Rows from 1 to 65536, Columns from A to IV • Resizing – Auto, Manual, Exact • Cells • Edit cells – F2 or double-click • Cancel changes – ESC • Formula Bar • Multiple ways to do most things • Help! – F1

  4. Formats • Format Cells – Ctrl+1 or right-click • “Number” “Font,” “Border,” “Fill” Categories • Paste Special (Alt + E + S) • Conditional Formatting • Allows cells to be formatted a certain way based on criteria • Home  Styles  Conditional Formatting

  5. Common Formats • General Format (Ctrl + Shift + ~) • 182893 • Accounting Number Format • $ 182,893.00 • Currency Format (Ctrl + Shift + $) • $182,893.00 • Comma Format (Ctrl + Shift + !) • 182,893.00 • Percent Format (Ctrl + Shift + %) • 18289300% • Date Format (Ctrl + Shift + #) • 27-Sep-00 • Text Format

  6. Tables • Example – NBA Player Stats • Auto-Filter • Data  Filter  Auto-Filter • The Filter will hide the entries you do not want to see • Sort • Can sort alphabetically or reverse alphabetically

  7. Two Places at Once • Freeze Panes • 2007: View  Freeze Panes • 2003: Window  Freeze Panes • Allows you to keep header in view • Splitting the Worksheet • Allows you to look at two far away parts of the worksheet at the same time • New Windows • 2007: View  New Window • 2003: Window  New Window • Can look at two different Worksheets in the same Workbook at the same time

  8. Formulas • Always start with an “=“ sign • Operators • Numerical : +, -, /, * • Text: & (concatenate) • Quotes • Using “quotes” will designate something as text, otherwise Excel will think you are referring to a cell

  9. Common Formulas • SUM – sums the referenced cells or values • AVERAGE – averages the referenced cells or values • COUNT – counts the number of cells or values • MAX – returns the maximum value • MIN – returns the minimum value • SUMIF • Sums cells based on criteria • SUMIF(range, criteria, [sum_range]) • Example: SUMIF(B3:D3,"=500",B5:D5)

  10. Using Formulas • Use Help files for more detail on a function’s syntax, i.e. its layout and order of the function and its arguments • Relative references vs Absolute references • Autofill

  11. IF • IF(logical_test, [value_if_true], [value_if_false] • Examples • IF(B8>1, "flag", "") • If the value in cell B8 is greater than 1, the if statement returns the text “flag”; if not, then it returns nothing.

  12. AND, OR • AND(logical1, logical2, logical3, …) • All logical tests must be true for the entire function to be true • Example: IF(AND(B4=C4,C4=D4),"","check dates") • OR(logical1, logical2, logical3, …) • At least one logical test must be true for the entire function to be true

  13. IS… • ISBLANK • Checks if a referenced cell is empty • ISERR • Checks if a value is an error, excluding #N/A • ISERROR • Checks if a value is an error, including #N/A • ISNA • Checks if a value is #N/A • ISNUMBER • Checks if a value is a number • ISTEXT • Checks if a value is text

  14. SUMPRODUCT • SUMPRODUCT(array1, array2, array3, …) or SUMPRODUCT((array1)*(array2)*…) • Multiplies corresponding values in arrays and sums the results • Useful for obtaining sums based on a criteria (when used with logical statements) • Example: • SUMPRODUCT(B5:D5,B8:D8)/SUM(B5:D5) • Finds the weighted average of the values in B8:D8 • SUMPRODUCT((B3:D3=500)*(B5:D5)) • The first array is a logical, so the returned value is the value of B5:D5 for which its corresponding cell in B3:D3 is equal to 500

  15. Vlookup Function • Allows you to retrieve data that exist in a list in another sheet or workbook. • Requirements for the list: • First column of the list must contain the value that matches a value in your worksheet • List should be sorted on the first column in Ascending order

  16. VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) lookup_value: The value to search in the first column of the table array  table_array: Cell Range or Name of cell range that makes up the cell range of your list col_index: Number representing the column in that list that contains the value you want to achieve. range_lookup: Logical value of True or False. True (or left empty) - closest lowest value False - Exact Match

  17. GPA Example – Steps • Create a Full Name column • = FirstNameCell&“ ”&LastNameCell • Create a Grade Point column • =VLOOKUP(Grade, GradePointsTable, 2, False) • Do some Excel magic • Try yourself: use pivot tables

  18. Common (Advanced) Formulas • Vlookup/Hlookup • Match & Index • Sumproduct • Offset

  19. Charts, Pivot Tables, and Pivot Charts Michelle Shieh

  20. Charts Where to make one: • In Excel 2003, Chart Wizard. • In Excel 2007, the ribbon! If you forget, look for: InsertChart

  21. Charts: Types • Bar Charts (Column Charts) • Line Charts • Pie Charts • Area Charts • XY (scatter) Charts , i.e. a scatterplot • Stock Charts • Surface Charts • Donut Charts • Bubble Charts • Radar Charts • Histograms (from Data Analysis Tools Add-in) and more…!

  22. Scatterplot: Graphing • Have two columns with labeled data (a data range) • Open the Chart Wizard • Select both columns (including the header for that column) • click Insert  Chart • Follow the dialog prompts and customize! • Example: 1. Plot Temperature against Time 2. Overlay trend line

  23. Histograms: Loading Add-ins • 2003: Tools>Add-ins >Analysis Toolpak • 2007: Excel Options

  24. Histogram: Graphing • Data Data Analysis  Histogram • Choose data range • Bin width (predefined or automatic) • Chart output • Example: Temperature data • Reduce gaps between bars

  25. Pivot Tables • An easy way to summarize table • Designed for • Subtotaling or aggregating data by groups • Expanding and collapsing levels of data to focus your results • Filter, sort, group, conditionally format most useful subset

  26. Pivot Tables: Intro Filters (eg Country) Count, sum, mean, etc Data you want to summarize Ways to group data • Insert Pivot Table Check Range Ok • 2003: Data Pivot Table

  27. Pivot Tables: Example Using the Sales data, create: • Total Category Sales • Total Category Sales by Quarter • Product sales per Quarter • Top 3 Products in each Category • Using filter to see only US data

  28. Pivot Tables: Calculated Field • We can do calculation on the summarized field as well • Options -> Formulas -> Calculated Field • Name: Formula 1 • Formula: Using Insert Field • Add -> OK

  29. Pivot Tables: Adjustments • Sorting data • Number formatting • Adjust Labels • Note: your custom name cannot be the same as your source name • Multiple data fields • Rearrange them by dragging the gray Data button

  30. Pivot Charts • Used to create stylized charts from a PivotTable report • Differences between PivotChart and standard graphing: • Chart types : PivotChart reports cannot be changed to an xy (scatter), stock, or bubble chart. • Source data: cannot change the data source range • Formatting: some formatting is not preserved when you refresh a PivotChart report, i.e. if any underlying data is modified • Row/Column orientation: Unlike a standard chart, you cannot switch the row/column orientation of a PivotChart report by using the Select Data Source dialog box. However, you can pivot the Row and Column labels to achieve the same effect

  31. Visual Basic for Applicationsfor Excel Koji Hamada

  32. VBA??? • Microsoft based programming language for MS Office and other programs • Write programs within a program so you can program on a program • Write macros (user defined functions) within Excel • Macros are shortcuts!!!

  33. How to write Macros • Record Macro function - User friendly way • Records your inputs onto a Visual Basic module • Storage place for your macros under the current workbook • Coding macros directly under the Project Window • Under the Visual Basic button • Essentially the same as above

  34. Initializing VBA • Office 2007 • First enable it under options (show developer tab in Ribbon) • Office 2003 • Under “Tools” tab and under “Macros” • Or right click on your tool bar and check Visual Basic

  35. Best way to Learn VBA • Play Around with the Record Macro function • Then go into the Project Window or Visual Basic Editor • Debug tab • Step Into funtion • Press F8 to go line by line • Then recreate macro by code with out the Record Macro function

  36. Quick Example with Record Macro • Go crazy and have fun :D • Use Relative References to apply macros on all spaces • Suggestions • Input values and play around with them • Apply functions (discuss later)

  37. Recreating your Recording • Always begin with Sub [name of macro] () • Use . to see what functions are available • Some functions are guided too • Check your code by using debug + F8

  38. Example 1 - Loops • Do loops • While • Example – Do While x <= [integer] • Until • Example – Do Until [variable] = [integer] • End with loop • For loops • Example – For [variable] = [integer] to [integer] • End with Next [looping variable]

  39. Example 2 – Find Function • Use to find an integer, string, or fuctions • Applications • Replace empty observations with values • Delete or modify certain values • Count the number of unique items

  40. Example 3 - Delete Empty Rows • ...or columns or empty observations (but most effective to use find function) • See previous example • Cleans up the data • Useful for large datasets • Makes it easier to apply functions with having to write logical statements if data is not there • Example – if cell(x, y) = Not “” • If missing some data • Uses loops

  41. Example 4 – Creating Arrays • Arrays are storage vectors or matrices • Types of Array Values • Integers => Sub [name] () as • Long Integers => Sub [name] () as Long • Variant (characters and numbers) => Sub [name] () as Variant • Type of Arrays • Static – User defined number of values • Dynamic – Unknown number of values

  42. Example 4 – Creating Arrays • 1 Dimensional Array (vector of data) • A row or column of data • 2 Dimensional Array (matrix of data) • A table of data • To Make larger sets of data, consider using a For Loop • May seem odd to contruct data like this BUT....

  43. Example 5 – Array Applications • Can apply some powerful fuctions to each table simply through one macro • Make quick tables with varying numbers • Example apply math operators (logs, invert matrix, etx) • Create a predefined Pivot tables out of multiple Arrays

  44. Summary • Best way to learn is to use the Record Macro feature and debug your code • Next time when you’re doing analysis in your workbook, turn on the Record Macro button • ...remember to turn it off and reset periodically • If you have a job which requires repetitive Excel analysis, VBA will be your best friend • Applies to functions, transforming data, making pivot tables, etc

More Related