• Published on
26-Dec-2014

• View
6.149

11

Embed Size (px)

DESCRIPTION

Excel Basic, function & formula, Chart, Excel Report, PivotTable and Macro

Transcript

• 1. Advanced Excel Training Advanced Excel Training tharapon.m@stpi.co.th | tharapon@gmail.com Senior ERP Development Analyst STP&I Public Company Limited Microsoft Office Specialist MasterTopics1. Excel Basic2. Function & Formula3. Chart4. Excel Report5. PivotTable6. Macro7. Workshop1. Excel Basic 1.1 (Cell Reference) - \$A\$1 Fix ( Copy \$A\$1) - \$A1, A\$1 Fix - F4 Excel \$ 1.2 (Name) - - Name Box - F3 - Formulas Name Manager STP&I Public Company Limited 1 Information Technology Section Administrative Department
• 2. Advanced Excel Training - Dynamic Range - =OFFSET(\$A\$1, 0, 0, COUNTA(\$A:\$A), 2) 2 - =OFFSET(\$A\$1, 0, 0, COUNTA(\$A:\$A), COUNTA(\$1\$1))1.3 Tips - Range Name () - Spacebar Excel 1 ( -) 1 - Merge Cell Paste Values PivotTable - > Format Cells > Alignment > Text Alignment: Horizontal Center Across Selection - - A+B > 100 ( A+B > 105.29) - ( ) 1.4 Shortcut Key Shortcut Key Ctrl + C Copy - Ctrl + X Cut - Ctrl + V Paste - Ctrl + Z Undo - Ctrl + ; Ctrl + Shift + : Ctrl + Shift + * Ctrl + . () Alt + Enter Ctrl + Enter Ctrl + Enter Ctrl + Ctrl + Shift + Value Shift + End + F3 Paste Name ( ) F4 Reference (A1 > \$A\$1 > A\$1 > \$A1) STP&I Public Company Limited 2 Information Technology Section Administrative Department
• 3. Advanced Excel Training2. Formula & Function Function IF (True) (False) VLOOKUP ( ) SUMIF / SUMIFS / COUNTIF / COUNTIFS / IFERROR Error ROUND LEFT, RIGHT, MID (, , ) TRIM 1 UPPER, LOWER, PROPER LEN TEXT SUBSTITUTE () REPLACE () TODAY, NOW DAY, MONTH, YEAR Date () DATE EOMONTH CONVERT Formula Errors Function ######## / #DIV/0 Divided by 0 - 0 #N/A Not Available (VLOOKUP) #NAME? (Range Name) #REF! #VALUE! STP&I Public Company Limited 3 Information Technology Section Administrative Department
• 4. Advanced Excel Training3. Chart3.1 2 (Secondary Axis) - Series > Format Data Series > Secondary Axis - > Series > 3.2 - Series STP&I Public Company Limited 4 Information Technology Section Administrative Department
• 5. Advanced Excel Training - 3.3 Trend STP&I Public Company Limited 5 Information Technology Section Administrative Department
• 6. Advanced Excel Training4. Excel Report4.1 (Tables) - Text - - - 4.2 Format Table / Cell Styles - Format Home > (Style) Format as Table - Format Home > (Style) Format as Table > New Table Style4.3 Conditional Formatting - Highlight Cells Rules - Top/Bottom Rules - Data Bars - Color Scales - Icon Sets / Showing only one icon STP&I Public Company Limited 6 Information Technology Section Administrative Department
• 7. Advanced Excel Training4.4 Sort by Color4.5 Data Validation - List - - Data Validation Formula Validation Formula A1 Text =ISTEXT(A1) A1:A25 =COUNTIF(\$A\$1:\$A\$25, A1) = 1 A1 B1 C1 =B1>C1 A1 B1 5 C1 5 =AND(B1=5, C1B1+C1, TRUE, FALSE) A1 BT =LEFT(A1, 2) = BT A1 BT 10 =AND(LEFT(A1, 2) = BT, LEN(A1) = 10)4.6 Report - Input Output Calculation - Input () () ( ) - Calculation () - Output () Report STP&I Public Company Limited 7 Information Technology Section Administrative Department
• 8. Advanced Excel Training5. PivotTable - > Insert > PivotTable - PivotTable () PivotTable > Options STP&I Public Company Limited 8 Information Technology Section Administrative Department
• 9. Advanced Excel Training5.1 PivotChart - Insert > PivotTable PivotChart - PivotChart PivotTable Excel PivotTable PivotChart5.2 % - (Value) > Value Field Setting - Show values as % STP&I Public Company Limited 9 Information Technology Section Administrative Department
• 10. Advanced Excel Training5.3 (Running Total) - Show values as Running Total in 5.4 (% Difference From)5.5 Group Year, Quarter & Year - > PivotTable Tools > Options > Group Selection STP&I Public Company Limited 10 Information Technology Section Administrative Department
• 11. Advanced Excel Training5.6 (Calculated Field) - 20% Amount 120% - PivotTable Tools > Options > Formulas > Calculated Field - Forecast (Formula) Fields PivotTable Calculated Field STP&I Public Company Limited 11 Information Technology Section Administrative Department
• 12. Advanced Excel Training5.7 (Calculated Item) - Product 5 Computer Notebook Calculated Item Item Computer Notebook - PivotTable Tools > Options > Formulas > Calculated Item - Name Computer & Notebook Formula Items Insert Item - Item Item Filter - Item STP&I Public Company Limited 12 Information Technology Section Administrative Department
• 13. Advanced Excel Training6. Macro6.1 Developer - Excel Options (Popular) Show Developer tab in the Ribbon6.2 Record New Macro - Developer Record Macro Shortcut Key - Use Relative Reference Macro Fixed A1 E10 E10 6.3 Macro - Shortcut Record Macro - Developer Macros Macro STP&I Public Company Limited 13 Information Technology Section Administrative Department
• 14. Advanced Excel Training 1. BAHTTEXTBAHTTEXT BAHTTEXT Excel BAHTTEXT() =BAHTTEXT(200)...