1 / 53

Using Microsoft Excel/VBA to Speed-up Your Data Processing

MS EXCEL/VBA TRAINING COURSES - 1. Using Microsoft Excel/VBA to Speed-up Your Data Processing. XLDataSoft. Some f acts about Microsoft Excel. 16,777,216 = # of Cells in A Worksheet (65,536 rows x 256 columns)

ike
Download Presentation

Using Microsoft Excel/VBA to Speed-up Your Data Processing

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. MS EXCEL/VBA TRAINING COURSES - 1 Using Microsoft Excel/VBA to Speed-up Your Data Processing XLDataSoft

  2. Some facts about Microsoft Excel • 16,777,216 = # of Cells in A Worksheet (65,536 rows x 256 columns) • 32,767 = Length of cell contents (text) in characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. • 255 = Column width, in characters • 16 = Undo Levels • 7 = Nested levels of functions • 329 = Number of available worksheet functions • Number of Sheets in a Workbook= Limited by available memory

  3. Can thesebe done in Excel? • Can we view more than one (1) sheets of same workbook in one window (screen)? • Can we view/print al the formula in a worksheet ? • Can we conduct query using SQL statements with CSV files? • Can we export Excel data into Word document for reporting? • Can we use Excel to build a sales database? • Can we…? • Please contact XLDataSoft for the answers!

  4. What will be covered in course I? • BASICS • Shortcut Keys , Symbols Input, Instant Results, Name Definition • EXCEL FUNCTIONS • Some Useful Functions, Create and Use Mega Formulas, Error Handling, Use of Array Functions • EXCEL ADVANCED FEATURES • Protect File/Cell using Password, Data Entry Validation , Conditional Format, Custom Formatting, Filter, Goal Seek, Solver, Pivot Table, Subtotal, Statistic Analysis, Controls • BRIEF INTRODUCTION OF VBA • Macros, Work with Cells, Create User’s Own Function, Work with External Files • Q & A

  5. BASICS-Shortcut Keys -1

  6. By Yongjun Chen BASICS-Shortcut Keys -2

  7. BASICS-Symbols Input - Use Different Font

  8. BASICS-Symbols Input -Use ASCII Code (Char() function)

  9. BASICS-Instant Results • Sometimes, we want to know some statistic results for a range of data, but we do not want to keep the results. • Using Instant Results, we can achieve this without any key press. • Step 1: Right click the Status Bar, if the status bar is hidden, go to View-Status Bar, make it checked. • Step 2: Select the function you want from the list: Average, Count, Count Num, Max, Min, and Sum. • Step 3: Select the data range. • Step 4: The results is shown in the right side of Status Bar.

  10. BASICS-Instant Results

  11. BASICS-Naming • Naming a Range • Naming a Single Cell • Use Name in Formulas • Find the Address of an Existing Name

  12. Excel Functions Some Useful Functions Vlookup(Value,Range,Col_index, Type) Searches for a value in the leftmost column of a range, return a value in the same row from a column you specify in the range. Index(Range, Row, Column) Return the value in a specified location of an array/range Match(Value, Range, Type) Return the location of a value in a range Column() and Row() Return current cell's column and row number, without argument Column(CellName), Row(CellName) Return the column/Row number for the specified cell Address(Row, Column,Type1, Type2, Sheet) Return the address of a specified cell in a specified sheet Indirect(Address) Return the value in the specified address

  13. Excel Functions- IF, AND, OR Use of IF, AND, OR Functions • IF(logical_test, True_Value, False_Value) • AND(Logical1, Logical2), OR(Logical1, Logical2) • AND(A>0, B>0) returns TRUE of both A and B are positive, otherwise, it returns FALSE • OR (A=0, B=0) returns TRUE if A = 0 or B =0; if neither of them is 0, then it returns FALSE. • =IF(OR(AND(A>0,B>0),AND(A<0,B<0)),"Positive",IF(OR(A=0,B=0), "Zero","Negative")) - This formula is used to tell the sign of A*B.

  14. Excel Functions- TEXT Use of Text Function for Formatting • TEXT function is used to converts a value to text in a specific number format. • Syntax: TEXT(value,format_text) • SSN: • = TEXT(123456789, "###-##-####") => 123-45-6789 • Phone: • = TEXT(1234567890, "(###) ###-####") => (123) 456-7890 • Date: • = TEXT(“08/19/1998”, "mmmm dd, yyyy") => August 19,1998 • Integer: • = TEXT(25, "0000") => 0025 • Conditional Formatting: • format_text = positive_fmt; negative_fmt;zero_fmt; text_fmt • =TEXT(B9,"#,###;(#,##0.00);00000;@")

  15. Excel Functions- Use Mega Formula Introduce Mega Formula – More than one function in a formula Q1: What is the number of hours for David? A1: Use VLookup Function =vlookup(“David”,A2:B5,2,false) Q2: Who has the maximum number of hours??? A2: Need 3 functions >> max = Max(B2:B5) to find the maximum number of hours. >> pos = Match(max,B2:B5) to find the relative position of the maximum value in the range >> result =Index(A2:A5,pos) to find the name for a given relative position in a range. >> One Mega Formula =INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0))

  16. Excel Functions- Use Mega Formula Summarize Data from Different Worksheets Suppose you have some results for different cases in the same workbook but different sheets. The results are at the same location (row and column). You want to use a table to summarize the results for different cases. Use One Mega Formula to Fill the Summary Table! =INDIRECT(ADDRESS(COLUMN()-2,2,,,$D3)) Row Number (Calculated) Column Number (Fixed) Sheet Name (Change)

  17. Excel Functions- Use Mega Formula Transpose Data / Extract Data every N Rows One Mega Formula does it all for you- =INDIRECT(ADDRESS(1+(ROW()-3)*4+COLUMN()-6,3)) Row # from Calculation Column #, Fixed

  18. Excel Functions- Use Mega Formula Text Operation - Manipulate Name Suppose you have a string “George Bush”, You want to extract its first name, last name, initial, etc. First Name =LEFT(B18,FIND(" ",B18)) Last Name =RIGHT(B18,LEN(B18)-FIND(" ",B18)) Initial =LEFT(B18,1) & MID(B18,FIND(" ",B18)+1,1) For concatenate

  19. Excel Functions- Use Mega Formula Data Operation – Reverse the Order The following formula used to Reverse the order of an array Of data =INDEX($B$27:$B$31, ROW($B$31)-ROW($B27)+1) Array, Fix Last Row, Fix Current Row, Change with rows

  20. Excel Functions - Array Functions An Introduction An array function is a function which return an array of data, some times it has only one data value like matrix operation. After you finish the input the array function, end it with CSE (Ctrl+Shift+Enter), then the program automatically add “{” and “}” to the function. Array function input End w/ CSE

  21. Excel Functions –UseArray Functions Linear Fit Suppose you have a table of data for X and Y, and you want to use a linear equation to fit it. For a linear equation, you need a slope (k) and an intercept (b) : Y=kX+b Since you need a function returns two values, then you need an array function – linest Select two cells in the same row, input =LINEST(y_range,x_range) And end with CSE, then you get k in the first cell and b in the second cell. If you only need k value, then use =INDEX( LINEST(C14:F14,C13:F13),1 )

  22. Excel Functions –Use Array Functions Achieve MaxIf, AverageIf In Excel, we have countif and sumif functions, but we do not have maxif, averageif functions. Use array function, we can achieve this easily. =AVERAGE(IF(B21:B27="A",C21:C27)) Criteria Range Range for Average ! The two ranges must have the same size!, the function must ended with CSE =MAX(IF(B21:B27="A",C21:C27))

  23. Excel Functions –UseArray Functions AverageIf under multiple conditions Multiple conditions, use “*” for AND Array function

  24. Advanced Features – File and Cell Protection How To... • Protected from Change Formatting Cell->Protection->Locked and Protect Sheet • Protected from View the Formula Formatting Cell->Protection->Locked and Hidden and Protect Sheet • Protect the Whole File from Viewing/Editing Save/Save As->Tools->General Options-> Set Password

  25. Advanced Features – Data Entry Validation An Introduction Data Entry Validation is an advanced feature of Excel, it is used to prevent invalid input which may cause error. This feature is very useful especially when others will use you spreadsheet.

  26. Advanced Features – Data Entry Validation An Introduction Menu Pop-up Window for setting up entry criteria/hint/alert

  27. Advanced Features – Data Entry Validation An Introduction When you select the cell which has validation, then the input message/hint, if any, will be displayed When invalid input is entered, then the alert message, if any, will be displayed

  28. Advanced Features – Data Entry Validation Examples 1. If you have a formula to calculate the log value of user’s input, then you do not want user input any non-positive values - use data entry validation can achieve this. Sorry, Examples are not included in this file. 2. Sometimes, you do not want user input same values in a range - use customized data validation criteria can achieve this - prevent duplicate input

  29. Advanced Features – Conditional Format Introduction Conditional Format is an advanced feature of Excel. A cell/range has conditional format will change its format if its value changes. Up to 3 criteria you can set for different format

  30. Advanced Features – Conditional Format Examples • Based on its own value - if it is positive, then display it as shaded, if it is negative, then display it in red, if it is zero, then display in white (hide). • Highlight the maximum value and the minimum value in a range. • Highlight the difference between two columns.

  31. Advanced Features – Custom Format Introduction Normally when we do formatting, we use what Excel pre-defined formatting types for Numbers, Currency, Dates, etc. Excel also gives us some free space to do our own formatting using formatting string - custom format. User must know the code/syntax of the formatting string

  32. Advanced Features – Custom Format Example • Formatting String: • #,##0.00 " ft” • “5” Display as “5 ft” • Formatting String: • $000 " Increase";($000) " Decrease";"No Change” • “5” Display as “$005 Increase” • “-5” Display as “($005) Decrease” • “0” Display as “No Change” • !! Although the display includes text, but the real value in the cell is still the number your entered/calculated, So you still can use the formatted cell for other calculations - they are numbers NOT text !!

  33. Advanced Features – Filter Introduction • Filtering is a quick and easy way to find and work with a subset of data in a list. • A filtered list displays only the rows that meet the criteria you specify. • Microsoft Excel provides two commands for filtering lists: • AutoFilter, which includes filter by selection, for simple criteria • Advanced Filter, for more complex criteria • Unlike sorting, filtering DOES NOT rearrange a list. Filtering temporarily hides rows you do not want displayed.

  34. Advanced Features – Filter Introduction Use these to set up criteria for filer Have Criteria Here (in different color) All the rows do not meet the criteria are hidden

  35. Advanced Features – Filter Examples 1. Example for Auto Filter 2. Example for Advanced Filter Sorry, Examples are not included in this file.

  36. Advanced Features – Goal Seek Introduction and Example Goal Seek - Adjust the value of one cell to get a specific value for another cell - solve equation Y = f(x) Set Cell: The Target Cell which has a formula - Y To Value: The Target Value for the Target Cell By Changing Cell - The Cell to be adjusted - X. This cell can not have a formula

  37. Advanced Features – Solver Introduction and Example Solver is more complicated than goal seek. It can find an optimal value (max, min, 0) value for a Target Cell (has formula) by changing other cells (more than one cells it depends on. Meanwhile, you can set criteria for the cells you want to change.

  38. Advanced Features – Solver Introduction and Example Target Cells to be adjusted Criteria to be met

  39. Advanced Features – Pivot Table Introduction and Example A PivotTable report is an interactive table that you can use to quickly summarize large amounts of data. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest. Sorry, Examples are not included in this file.

  40. Advanced Features – Subtotal Introduction and Example Microsoft Excel can automatically summarize data by calculating subtotal and grand total values in a list. To use automatic subtotals, your list must contain labeled columns and the list must be sorted on the columns for which you want subtotals. Sorry, Examples are not included in this file.

  41. Advanced Features – Statistic Analysis Introduction and Example • Use Analysis ToolPak Add-InPerform Various Statistical Analysis -ANOVA, Correlation, Covariance, Descriptive Statistics, Fourier Analysis, Moving Average, Random Number Generation, Regression, t-Test, z-Test, etc Sorry, Examples are not included in this file.

  42. Advanced Features – Controls Introduction and Example Command Button - for assigning macro to perform different tasks; List Box and Combo Box - for selection with many choices Option Button and Check Box - for selection with several choices … Normally, a control need to be linked to a cell’s value, when the value in the control changes, the value in the linked cell will automatically change. Users use the linked cell to do other calculations.

  43. Advanced Features – Controls Introduction and Example Assign a Macro Link to a Cell

  44. Advanced Features – Controls Introduction and Example Example – Quick Mortgage Calculation

  45. Brief Introduction of VBA Introduction • VBA is a script language which has most of Visual Basic’s features and function, and also integrated with the Microsoft Office products, like Excel, Word, Access, Powerpoint, etc. • Use VBA in Excel you can create your own function, procedures, forms, menus, I/O with external files, etc. • Use VBA in Excel you can create an user-friendly interface utility program or spreadsheet application • Press Alt+F11 to activate VBA window

  46. Brief Introduction of VBA Macro • The easiest way to learn VBA is through recording macros and modify the macro. • The procedure for recording the a macro is: • Start • Do something, which you want to repeat later, within Excel • Stop • Using macros can save your time on something you need to do again and again.

  47. Brief Introduction of VBA Macro If macros are disabled at when you start Excel, change the security level to medium

  48. Brief Introduction of VBA Introduction- work with the VBA window • Press Alt+F11 to activate VBA window • View/Modify Code - go to modules • VBA is an object-based (NOT object-oriented) programming language • Containers – Sheets, Workbooks, etc • Object - Cell, Range, Chart, Sheet, etc • Method - ClearContent, Delete, Add, Select • Properties - Value, Font, Name, etc • Events - Workbook_Open, Sheet_Activate, CommandButton_Click • ActiveCell.Font.Bold = True • Cells(1,1).Select

  49. Brief Introduction of VBA Introduction -work with cells • Write A Value to A Cell • Cells(row, column) =5 • Cells(rr,cc) = Variable_Name • Cells(rr,cc)=Cells(rr2,cc2) • Extract Value from A Cell • Variable_Name = Cells(rr,cc)

  50. Brief Introduction of VBA Introduction - Create User’s Own Function Private Function myFun(x As Integer, y As Integer) As Integer myFun = x * y + x / y End Function Arguments Must start with Keyword “Function” and end with “End Function” Return Value Type The return value must be assigned to the function name

More Related