Excel 300 Example

 

 

아직까지 엑셀을 잘 다루지 못한다. 그저 이런거나 퍼다 저장할 뿐~ㅠ.ㅠ 

배우는 사람들이 격찬한 자료 출처는 Excel Easy 사이트다. 문서 출처를 링크로 연결한다.

 

Introduction

 

1 Range: AutoFill | Fibonacci Sequence | Custom Lists | Hide Columns or Rows | Skip Blanks | AutoFit | Transpose | Split Cells | Flash Fill | Move Columns | ROW function

2 Formulas and Functions: Most Used Functions | Subtract | Multiply | Divide | Square Root | Percentage | Named Range | Dynamic Named Range | Paste Options | Discount

Basics

 

1 Ribbon: Formula Bar | Quick Access Toolbar | Customize the Ribbon | Developer Tab | Status Bar | Checkbox

2 Workbook: Themes | View Multiple Workbooks | AutoRecover | Save in 97-2003 Format

3 Worksheets: Zoom | Split | Freeze Panes | Group Worksheets | Consolidate | View Multiple Worksheets | Get Sheet Name | Comments | Spell Check | Unhide Sheets

4 Format Cells: Decimal Places | Fractions | Currency vs Accounting | Text to Numbers | Numbers to Text | Custom Number Format | Format Painter | Cell Styles | Wrap Text | Merge Cells | Strikethrough | Superscript and Subscript | Check Mark

5 Find & Select: Find Features | Wildcards | Delete Blank Rows | Row Differences | Copy Visible Cells Only | Search Box

6 Templates: Budget | Calendar | Holidays | Meal Planner | Invoice | Automated Invoice | Default Templates | Time Sheet | BMI calculator

7 Data Validation: Reject Invalid Dates | Budget Limit | Prevent Duplicate Entries | Product Codes | Drop-down List | Dependent Drop-down Lists | Cm to inches | Kg to lbs

8 Keyboard Shortcuts: Function Keys | Insert Row | Save As | Delete Row | Formula to Value | Scroll Lock | Bullet Points | Line Break | Show Formulas | Paste Special

9 Print: Workbook Views | Page Breaks | Headers and Footers | Page Numbers | Print Titles | Center on Page | Print Gridlines & Headings | Print Area

10 Share: Embed | PDF | Share Workbooks | OneDrive | Online | Import Access Data | Microsoft Query | Import/Export Text Files | XML

11 Protect: Protect Workbook | Protect Sheet | Lock Cells | Read-only Workbook | Mark as Final

Functions

1 Count and Sum: Countif | Count Blank/Nonblank Cells | Count Characters | Not Equal To | Count Cells with Text | Sum | Running Total | Sumif | Sumproduct

2 Logical: If | Comparison Operators | Or | Roll the Dice | Ifs | Contains Specific Text | Switch | If Cell is Blank | Absolute Value

3 Cell References: Copy a Formula | 3D-reference | External References | Hyperlinks | Union and Intersect | Percent Change | Add a Column | Absolute Reference | Address

4 Date & Time: DateDif | Today's Date | Date and Time Formats | Calculate Age | Time Difference | Weekdays | Days until Birthday | Last Day of the Month | Add or Subtract Time | Quarter | Day of the Year | Days between Dates

5 Text: Separate Strings | Count Words | Text to Columns | Find | Search | Change Case | Remove Spaces | Compare Text | Substitute vs Replace | Text | Concatenate | Substring

6 Lookup & Reference: Vlookup | Tax Rates | Index and Match | Two-way Lookup | Offset | Case-sensitive Lookup | Left Lookup | Locate Maximum Value | Indirect | Two-column Lookup | Closest Match | Compare Two Columns | Xlookup

7 Financial: PMT | Loans with Different Durations | Investment or Annuity | Compound Interest | CAGR | Loan Amortization Schedule | NPV | IRR | Depreciation | Profit Margin

8 Statistical: Average | Negative Numbers to Zero | Random Numbers | Rank | Percentiles and Quartiles | Box and Whisker Plot | AverageIf | Forecast | MaxIfs and MinIfs | Weighted Average | Mode | Standard Deviation | Frequency

9 Round: Chop off Decimals | Nearest Multiple | Even and Odd | Mod | Rounding Times

10 Formula Errors: IfError | IsError | Aggregate | Circular Reference | Formula Auditing | Floating Point Errors | IFNA

11 Array Formulas: Count Errors | Count Unique Values | Count with Or Criteria | Sum Every Nth Row | Sum Largest Numbers | Sum Range with Errors | Sum with Or Criteria | Most Frequently Occurring Word | Dynamic Arrays | LET function

 

Data Analysis

 

1 Sort: Custom Sort Order | Sort by Color | Reverse List | Randomize List | SORT function

2 Filter: Number and Text Filters | Date Filters | Advanced Filter | Data Form | Remove Duplicates | Outlining Data | Subtotal | Unique Values | FILTER function

3 Conditional Formatting: Manage Rules | Data Bars | Color Scales | Icon Sets | Find Duplicates | Shade Alternate Rows | Compare Two Lists | Conflicting Rules | Heat Map

4 Charts: Column Chart | Line Chart | Pie Chart | Bar Chart | Area Chart | Scatter Plot | Data Series | Axes | Chart Sheet | Trendline | Error Bars | Sparklines | Combination Chart | Gauge Chart | Thermometer Chart | Gantt Chart | Pareto Chart

5 Pivot Tables: Group Pivot Table Items | Multi-level Pivot Table | Frequency Distribution | Pivot Chart | Slicers | Update Pivot Table | Calculated Field/Item | GetPivotData

6 Tables: Structured References | Table Styles | Quick Analysis | Merge Tables

7 What-If Analysis: Data Tables | Goal Seek | Quadratic Equation

8 Solver: Transportation Problem | Assignment Problem | Capital Investment | Shortest Path Problem | Maximum Flow Problem | Sensitivity Analysis | System of Linear Equations

9 Analysis ToolPak: Histogram | Descriptive Statistics | Anova | F-Test | t-Test | Moving Average | Exponential Smoothing | Correlation | Regression

VBA

 

1 Create a Macro: Swap Values | Run Code from a Module | Macro Recorder | Use Relative References | FormulaR1C1 | Add a Macro to the Toolbar | Enable Macros | Protect Macro

2 MsgBox: MsgBox Function | InputBox Function

3 Workbook and Worksheet Object: Path and FullName | Close and Open | Loop through Books and Sheets | Sales Calculator | Files in a Directory | Import Sheets | Programming Charts

4 Range Object: CurrentRegion | Dynamic Range | Resize | Entire Rows and Columns | Offset | From Active Cell to Last Entry | Union and Intersect | Test a Selection | Possible Football Matches | Font | Background Colors | Areas Collection | Compare Ranges

5 Variables: Option Explicit | Variable Scope | Life of Variables | Type Mismatch

6 If Then Statement: Logical Operators | Select Case | Tax Rates | Mod Operator | Prime Number Checker | Find Second Highest Value | Sum by Color | Delete Blank Cells

7 Loop: Loop through Defined Range | Loop through Entire Column | Do Until Loop | Step Keyword | Create a Pattern | Sort Numbers | Randomly Sort Data | Remove Duplicates | Complex Calculations | Knapsack Problem

8 Macro Errors: Debugging | Error Handling | Err Object | Interrupt a Macro | Subscript Out of Range | Macro Comments

9 String Manipulation: Separate Strings | Reverse Strings | Convert to Proper Case | Instr | Count Words

10 Date and Time: Compare Dates and Times | DateDiff Function | Weekdays | Delay a Macro | Year Occurrences | Tasks on Schedule | Sort Birthdays

11 Events: BeforeDoubleClick Event | Highlight Active Cell | Create a Footer Before Printing | Bills and Coins | Rolling Average Table

12 Array: Dynamic Array | Array Function | Month Names | Size of an Array

13 Function and Sub: User Defined Function | Custom Average Function | Volatile Functions | ByRef and ByVal

14 Application Object: StatusBar | Read Data from Text File | Write Data to Text File | Vlookup

15 ActiveX Controls: Text Box | List Box | Combo Box | Check Box | Option Buttons | Spin Button | Loan Calculator

16 Userform: Userform and Ranges | Currency Converter | Progress Indicator | Multiple List Box Selections | Multicolumn Combo Box | Dependent Combo Boxes | Loop through Controls | Controls Collection | Userform with Multiple Pages | Interactive Userform

 

엑셀 배우기