尊敬的 微信汇率:1円 ≈ 0.046166 元 支付宝汇率:1円 ≈ 0.046257元 [退出登录]
SlideShare a Scribd company logo
SHARED COMPUTING SERVICES
                                          Microsoft Excel XP/2003, Level 300
Become a Microsoft Excel Power-User
Updated 12/14/05


Logical Functions
Use the IF Function
=IF(Statement,IFtrue,IFfalse)
     Statement - comparison statement to which you try to match.                               Nest IF functions in the IFfalse
     IFtrue - result if the comparison matches the statement.                                  area of the parent function.
     IFfalse - result if the comparison does not match the statement.

Combine Logical Functions
=IF(AND(Statement1,Statement2),IFtrue,IFfalse) — Ensures that both statements must be true.
=IF(OR(Statement1,Statement2),IFtrue,IFfalse) — Tests both statements, either of which can be true.
=IF(NOT(Statement),IFtrue,IFfalse) — Returns the reverse value fur the condition.

Concatenate & Parse Cells (Merge & Split)
Concatenate (Merge) Data in Separate Cells
                 A              B                C
 1        John            Smith            Smith, John     =CONCATENATE(B1,“, ”,A1)
 2        Jane            Jones            Jones, Jane     =B2&“, ”&A2
Parse (Split) Data into Separate Cells
     1.     Select the cells to parse (split)
     2.     Go to Data Text to Columns… to open the wizard.
     3.     Step 1 of 3:Be sure Delimited is selected, then click Next >
     4.     Step 2 of 3:Select the delimiter type, then click Next >
     5.     Step 3 of 3: Choose the data type for the column if necessary, then click Finish

                                                                                                              When passing the interval code
Use the DATEDIF Function                                                                                  to the DATEDIF function, enclose it
Syntax: =DATEDIF(Date1,Date2,Interval)                                                                    in quotes if you are passing a literal
         Date1 — first date, in standard Excel serial-date format.                                        value to the function.
         Date2 — second date, in standard Excel serial-date format.
         Interval — unit of time for the result.
Date1 must be ≤ Date2 or a #NUM! error will be returned. If either Date1 or Date2 is not a date, a #VALUE! error will be returned.
Interval must be one of the following codes:
 Code Meaning                          Description
"m"       Months                          Number of complete months between Date1 and Date2.
"d        Days                            Number of days between Date1 and Date2.
"y"       Years                           Number of complete years between Date1 and Date2.
"ym"      Months Excluding Year           Number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.
"yd"      Days Excluding Years            Number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.
"md"      Days Excluding Months & Years   Number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and year.
When passing the interval code to the DATEDIF function, enclose it in quotes if you are passing a literal value to the function.
Microsoft Excel XP/2003, Level 300
Become a Microsoft Excel Power-User
Use HLOOKUP
You must have a data table for which to lookup information indexed in horizontal rows.
=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
     lookup_value is the reference or address of the comparison cell.
     table_array is the data table range that is searched (including row labels).
     row_index_num indicates how many rows to move down.
     range_lookup is a logical argument that returns a true or false value (optional)

Use VLOOKUP
You must have a data table for which to lookup information indexed in vertical columns.
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
     lookup_value is the reference or address of the comparison cell.
     table_array is the data table range that is searched (including column labels).
     col_index_num indicates how many columns to move the right.
     range_lookup is a logical argument that returns a true or false value (optional)

Use Data Validation
       1.  Select the cell(s) for which you will define the data validation criteria.
       2.  Go to Data Validation… to open the Data Validation dialog box. The Settings tab is active.
       3.  Click the Allow: arrow to display a list of options, and then select the option you desire.
       4.  Click the Data: arrow to display a list of conditional operators, and then select the option you desire. Complete the remaining
           fields that are pertinent to your choices.
       5. Click the Input Message tab and click in the Title: field. Type the title of the dialog box to appear.
       6. Click the Input Message: field and type the message you’d like the dialog box to display.
       7. Click the Error Alert tab, click the Style: arrow to choose an icon that will display.
       8. Click the Title: field and type the title to appear.
       9. Click the Error Message: field and type the message you’d like the dialog box to display.
       10. Click OK .

       •      Click Circle Invalid Data     on the Auditing toolbar to locate cells that don’t meet the validation criteria.
       •      Click Clear Validation Circles     on the Auditing toolbar to clear the circle.

Use Data Validation with Lookup Tables
When creating the data validation, use a formula to reference the lookup table’s column in the Source: field.


Visual Basic
Record a Macro                                                        Edit a Macro
  1.        Go to Tools Macro Record New Macro… to                       •   Open the Visual Basic window by going to
            record your actions for the macro.                               Tools Macro Macros… OR press ALT+F8. You can edit
  2.        Give the macro a name and description and click Close            any macro using Visual Basic for Applications (VBA) code.
  3.        Perform the actions you would like recorded.
  4.        Click Stop      on the Macros toolbar when finished       Debug a Macro
            OR go to Tools Macro Stop Recording.
                                                                         •   Open the Visual Basic window by going to
  •         To run a macro, press the shortcut key if you provided
                                                                             Tools Macro Macros…
            one or go to Tools Macro Macros…, select the
                                                                         •   Select the macro under Macro Name: and click Step Into .
            macro and click Run . You may also assign the macro to
                                                                         •   Go to Debug Step Into OR press F8 to move to execute the
            a button as described below.
                                                                             next line of code.




page 2                                                                                                            Shared Computing Services
Microsoft Excel XP/2003, Level 300
                                                                                              Become a Microsoft Excel Power-User
Create a Macro Button
    1.   Open the Forms toolbar and select the Button         button.
    2.   Draw the button anywhere on the worksheet.
    3.   Select the macro to assign and click OK .
    4.   Select the button’s text and type your own button name. You may format the text any way you wish.
    5.   Click off of the button to deselect.
    •    To run the macro, click the button with your mouse.
    •    To edit the button, right-click on the button and select your options.


User-Defined Functions
Create a Function Procedure
    1.   Open Visual Basic Editor by going to Tools Macros Visual Basic Editor OR press ALT+F11.
    2.   Go to Insert Module to open the Code window.
    3.   Go to Insert Procedure… to open the Add Procedure dialog box.
    4.   Name the procedure and provide the arguments between the parentheses.
    5.   Define what the name of the function equals (what calculation to perform).
Public Function Commission(SubTotal)
Commission = SubTotal * 0.05
End Function

Use an Application Object
    Name=Application.FunctionName(arguments)
Public Function SalesTax(State)
SalesTax = Application.VLookup(State, Range("Tax_Rates"), 3)
End Function

Control Procedure Flow
    If Name = “n” Then
         Name = Application.FunctionName(arguments)
    ElseIf Name = “n” Then
         Name = Application.FunctionName(arguments)
    End If
         Name = formula
Public Function Discount(Sales, Customer)
      If Customer = "R" Then
         Discount = Application.VLookup(Sales, Range("Discounts"), 2)
      ElseIf Customer = "W" Then
         Discount = Application.VLookup(Sales, Range("Discounts"), 3)
      End If
         Discount = (-1) * Discount * Sales
End Function




Shared Computing Services                                                                                                 page 3
Microsoft Excel XP/2003, Level 300
Become a Microsoft Excel Power-User
Write a Subroutine
Public Function Discount(Sales, Customer)
   If Customer = "R" Then
      Discount = Application.VLookup(Sales, Range("Discounts"), 2)
      Discount = (-1) * Discount * Sales
   ElseIf Customer = "W" Then
      Discount = Application.VLookup(Sales, Range("Discounts"), 3)
      Discount = (-1) * Discount * Sales
   ElseIf Customer = "" Then
      Discount = "Please Enter Customer Code"
   Else
      Discount = "Invalid Customer Code"
   End If
End Function


Customize Excel
Share a Workbook
    1.   With an Excel workbook open, go to Tools Share Workbook.
    2.   On the Editing tab, select Allow changes by more than one user at the same time. This also allows workbook merging.
    3.   On the Advanced tab, select the Automatically every option, and then click OK .

Create a Cell Comment
    •    Select a cell and go to Insert Comment and type your message. A red dot will be placed in the upper-right corner of the cell.
    •    When you move your mouse cursor on a cell with a comment, the comment will appear.
    •    Right-click a commented cell and select Edit Comment to edit the message.
    •    Go to Edit Clear Comments to remove comments.

Create Custom Cell Formats
When creating a format for a cell, there are four types of numbers or text that you need to specify how you want them to appear.
Positive Number; Negative Number; Zero; Text
Code                    Display
         d              Day (d=1, dd=01, ddd=Mon, dddd=Monday)
         m              Month (m=1, mm=01, mmm=Jan, mmmm=January)
         y              Year (yy=02, yyyy=2001)
                        Insert space in a number format [when you follow an underscore with a closing parenthesis _),
  _ (underscore)
                        positive numbers line up correctly with negative numbers that are enclosed in parentheses]
   * (asterisk)         Repeat the next character in the format to fill the column width
         0              Displays a digit, if one exists in the position. A zero displays if no digit exists.
         #              Displays a digit, if one exists in the position.
         $              Displays a dollar sign in the position
         .              Displays a decimal point in the position
         ,              Add commas as thousands separators
         @              Indicates that an alpha character is required
         &              Indicates that an alpha character is an option
      “ABC”             Displays anything inside quotation marks as literal characters
                       Display for following character as literal
     [color]            Displays the result in the color specified. (red, magenta, yellow, blue, cyan, green, black, white)
         ;              Separates sections in the syntax of the cell



page 4                                                                                                     Shared Computing Services
Microsoft Excel XP/2003, Level 300
                                                                                                 Become a Microsoft Excel Power-User
Add a Background Image to a Worksheet
    1.   To add a background pattern, go to Format Sheet Background.
    2.   Browse to the image you want and click OK.
    3.   To remove the pattern, select Format Sheet Delete Background.

Create a Custom List
     1. Select an existing list and then go to Tools Options Custom Lists.
     2. Click Import . You’ll see the cell references to the range of cells containing your list. OK .
Edit the list by returning to Tools Options Custom Lists and select your list on the left. Edit the list to the right by pressing
ENTER after each entry.

Create a Custom Menu
    1.   Go to Tools Customize… Commands tab.
    2.   Scroll to and select New Menu under Categories:
    3.   Click and hold New Menu under Commands: and drag it up to the menu bar in any location you desire.
    4.   Select a category and drag the command of your choice to the new menu.
    •    Rename the menu by right-clicking New Menu and edit the Name: field. Use the ampersand (&) in front of any character
         you’d like underlined for shortcuts. (i.e. F&ormat will be Format)

Customize a Toolbar
    1.   Go to Tools Customize… Toolbars tab.
    2.   Click the Commands tab and select a category and drag the command of your choice to the new toolbar.
    1.   Edit any button while the Customize dialog box is still open by right-clicking any button and select Edit Button Image.
    2.   Move any button by holding ALT while dragging the button to a new location.
    3.   Remove the button by dragging it off the toolbar.
    4.   Reset any toolbar to the default by going to Tools Customize… Toolbars tab and click Reset .

Customize How You Move
    1.   Tools Options Edit tab.
    2.   In the Move Selection after Enter drop-down box, choose the direction you want the selection to move to.




Shared Computing Services                                                                                                       page 5

More Related Content

What's hot

E-Book 25 Tips and Tricks MS Excel Functions & Formulaes
E-Book 25 Tips and Tricks MS Excel Functions & FormulaesE-Book 25 Tips and Tricks MS Excel Functions & Formulaes
E-Book 25 Tips and Tricks MS Excel Functions & Formulaes
BurCom Consulting Ltd.
 
Using Microsoft Excel7 Advanced
Using Microsoft Excel7 AdvancedUsing Microsoft Excel7 Advanced
Using Microsoft Excel7 Advanced
Jack Frost
 
Microsoft Office Package: Practical Questions
Microsoft Office Package: Practical QuestionsMicrosoft Office Package: Practical Questions
Microsoft Office Package: Practical Questions
Makaha Rutendo
 
Vba
Vba Vba
History of microsoft excel
History of microsoft excelHistory of microsoft excel
History of microsoft excel
Ofun Emma
 
Microsoft excel
Microsoft excelMicrosoft excel
Microsoft excel
14312
 
Libre Office Calc Lesson 4: Understanding Functions
Libre Office Calc Lesson 4: Understanding FunctionsLibre Office Calc Lesson 4: Understanding Functions
Libre Office Calc Lesson 4: Understanding Functions
Smart Chicago Collaborative
 
Visual basic
Visual basicVisual basic
Visual basic
Alisha Korpal
 
Libre Office Calc Lesson 2: Formatting and Charts
Libre Office Calc Lesson 2: Formatting and ChartsLibre Office Calc Lesson 2: Formatting and Charts
Libre Office Calc Lesson 2: Formatting and Charts
Smart Chicago Collaborative
 
Excel ppt
Excel pptExcel ppt
Excel 2007- Enter Formulas
Excel 2007- Enter FormulasExcel 2007- Enter Formulas
Excel 2007- Enter Formulas
Oklahoma Dept. Mental Health
 
Creating a quiz using visual basic 6
Creating a quiz using visual basic 6Creating a quiz using visual basic 6
Creating a quiz using visual basic 6
Ella Marie Wico
 
Chapter 2 — Program and Graphical User Interface Design
Chapter 2 — Program and Graphical User Interface DesignChapter 2 — Program and Graphical User Interface Design
Chapter 2 — Program and Graphical User Interface Design
francopw
 
Excel help 01
Excel help 01Excel help 01
Excel help 01
Keashan Jayaweera
 
Project1 VB
Project1 VBProject1 VB
Project1 VB
sunmitraeducation
 
Ms Access
Ms AccessMs Access
Easy Pivot Tutorial June 2020
Easy Pivot Tutorial June 2020Easy Pivot Tutorial June 2020
Easy Pivot Tutorial June 2020
Adhi Wikantyoso
 
2 front panel
2  front panel2  front panel
2 front panel
Ankush Jamthikar
 
Splitter Pro X version Tutorial June 2020 English
Splitter Pro X version Tutorial June 2020 EnglishSplitter Pro X version Tutorial June 2020 English
Splitter Pro X version Tutorial June 2020 English
Adhi Wikantyoso
 
Ms excel 2007 tutorial
Ms excel 2007 tutorialMs excel 2007 tutorial
Ms excel 2007 tutorial
jks2010
 

What's hot (20)

E-Book 25 Tips and Tricks MS Excel Functions & Formulaes
E-Book 25 Tips and Tricks MS Excel Functions & FormulaesE-Book 25 Tips and Tricks MS Excel Functions & Formulaes
E-Book 25 Tips and Tricks MS Excel Functions & Formulaes
 
Using Microsoft Excel7 Advanced
Using Microsoft Excel7 AdvancedUsing Microsoft Excel7 Advanced
Using Microsoft Excel7 Advanced
 
Microsoft Office Package: Practical Questions
Microsoft Office Package: Practical QuestionsMicrosoft Office Package: Practical Questions
Microsoft Office Package: Practical Questions
 
Vba
Vba Vba
Vba
 
History of microsoft excel
History of microsoft excelHistory of microsoft excel
History of microsoft excel
 
Microsoft excel
Microsoft excelMicrosoft excel
Microsoft excel
 
Libre Office Calc Lesson 4: Understanding Functions
Libre Office Calc Lesson 4: Understanding FunctionsLibre Office Calc Lesson 4: Understanding Functions
Libre Office Calc Lesson 4: Understanding Functions
 
Visual basic
Visual basicVisual basic
Visual basic
 
Libre Office Calc Lesson 2: Formatting and Charts
Libre Office Calc Lesson 2: Formatting and ChartsLibre Office Calc Lesson 2: Formatting and Charts
Libre Office Calc Lesson 2: Formatting and Charts
 
Excel ppt
Excel pptExcel ppt
Excel ppt
 
Excel 2007- Enter Formulas
Excel 2007- Enter FormulasExcel 2007- Enter Formulas
Excel 2007- Enter Formulas
 
Creating a quiz using visual basic 6
Creating a quiz using visual basic 6Creating a quiz using visual basic 6
Creating a quiz using visual basic 6
 
Chapter 2 — Program and Graphical User Interface Design
Chapter 2 — Program and Graphical User Interface DesignChapter 2 — Program and Graphical User Interface Design
Chapter 2 — Program and Graphical User Interface Design
 
Excel help 01
Excel help 01Excel help 01
Excel help 01
 
Project1 VB
Project1 VBProject1 VB
Project1 VB
 
Ms Access
Ms AccessMs Access
Ms Access
 
Easy Pivot Tutorial June 2020
Easy Pivot Tutorial June 2020Easy Pivot Tutorial June 2020
Easy Pivot Tutorial June 2020
 
2 front panel
2  front panel2  front panel
2 front panel
 
Splitter Pro X version Tutorial June 2020 English
Splitter Pro X version Tutorial June 2020 EnglishSplitter Pro X version Tutorial June 2020 English
Splitter Pro X version Tutorial June 2020 English
 
Ms excel 2007 tutorial
Ms excel 2007 tutorialMs excel 2007 tutorial
Ms excel 2007 tutorial
 

Viewers also liked

Ftp server
Ftp serverFtp server
Ftp server
pawnbeeta
 
The linux file system structure
The linux file system structureThe linux file system structure
The linux file system structure
Teja Bheemanapally
 
File Sever
File SeverFile Sever
Linux11 Proxy Server
Linux11 Proxy ServerLinux11 Proxy Server
Linux11 Proxy Server
Jainul Musani
 
Samba server
Samba serverSamba server
Samba server
Santosh Khadsare
 
Web Servers (ppt)
Web Servers (ppt)Web Servers (ppt)
Web Servers (ppt)
webhostingguy
 
Linux.ppt
Linux.ppt Linux.ppt
Linux.ppt
onu9
 

Viewers also liked (7)

Ftp server
Ftp serverFtp server
Ftp server
 
The linux file system structure
The linux file system structureThe linux file system structure
The linux file system structure
 
File Sever
File SeverFile Sever
File Sever
 
Linux11 Proxy Server
Linux11 Proxy ServerLinux11 Proxy Server
Linux11 Proxy Server
 
Samba server
Samba serverSamba server
Samba server
 
Web Servers (ppt)
Web Servers (ppt)Web Servers (ppt)
Web Servers (ppt)
 
Linux.ppt
Linux.ppt Linux.ppt
Linux.ppt
 

Similar to Excel300

Form4 cd4
Form4 cd4Form4 cd4
Form4 cd4
smktsj2
 
Vb6.0 intro
Vb6.0 introVb6.0 intro
Vb6.0 intro
JOSEPHINEA6
 
Autocad excel vba
Autocad excel vbaAutocad excel vba
Autocad excel vba
rjg_vijay
 
Spreadsheet Analytical Tools
Spreadsheet Analytical ToolsSpreadsheet Analytical Tools
Spreadsheet Analytical Tools
Joselito Perez
 
Tugas testing
Tugas testingTugas testing
Tugas testing
Astrid yolanda
 
Print9
Print9Print9
Notacd04
Notacd04Notacd04
Notacd04
Azmiah Mahmud
 
Notacd04
Notacd04Notacd04
Notacd04
cikgushaharizan
 
Getting started with the visual basic editor
Getting started with the visual basic editorGetting started with the visual basic editor
Getting started with the visual basic editor
putiadetiara
 
50 MS Excel Tips and Tricks
50 MS Excel Tips and Tricks 50 MS Excel Tips and Tricks
50 MS Excel Tips and Tricks
BurCom Consulting Ltd.
 
QESUTGPfbmYH3WQF257.pptx
QESUTGPfbmYH3WQF257.pptxQESUTGPfbmYH3WQF257.pptx
QESUTGPfbmYH3WQF257.pptx
RAMCHANDRASHARMA7
 
Basics of excel for medical profession
Basics of excel for medical professionBasics of excel for medical profession
Basics of excel for medical profession
Digital Shende
 
003.query
003.query003.query
Lesson9 working with basic functions
Lesson9 working with basic functionsLesson9 working with basic functions
Lesson9 working with basic functions
ricsanmae
 
Getting started with Microsoft Excel Macros
Getting started with Microsoft Excel MacrosGetting started with Microsoft Excel Macros
Getting started with Microsoft Excel Macros
Nick Weisenberger
 
Microsoft Excel- basics
Microsoft Excel-  basicsMicrosoft Excel-  basics
Microsoft Excel- basics
jeshin jose
 
Office excel tips and tricks 201101
Office excel tips and tricks 201101Office excel tips and tricks 201101
Office excel tips and tricks 201101
Vishwanath Ramdas
 
Useful macros and functions for excel
Useful macros and functions for excelUseful macros and functions for excel
Useful macros and functions for excel
Nihar Ranjan Paital
 
AVB201.1 MS Access VBA Module 1
AVB201.1 MS Access VBA Module 1AVB201.1 MS Access VBA Module 1
AVB201.1 MS Access VBA Module 1
guest38bf
 
Advanced Filter Concepts in MS-Excel
Advanced Filter Concepts in MS-ExcelAdvanced Filter Concepts in MS-Excel
Advanced Filter Concepts in MS-Excel
P. SUNDARI ARUN
 

Similar to Excel300 (20)

Form4 cd4
Form4 cd4Form4 cd4
Form4 cd4
 
Vb6.0 intro
Vb6.0 introVb6.0 intro
Vb6.0 intro
 
Autocad excel vba
Autocad excel vbaAutocad excel vba
Autocad excel vba
 
Spreadsheet Analytical Tools
Spreadsheet Analytical ToolsSpreadsheet Analytical Tools
Spreadsheet Analytical Tools
 
Tugas testing
Tugas testingTugas testing
Tugas testing
 
Print9
Print9Print9
Print9
 
Notacd04
Notacd04Notacd04
Notacd04
 
Notacd04
Notacd04Notacd04
Notacd04
 
Getting started with the visual basic editor
Getting started with the visual basic editorGetting started with the visual basic editor
Getting started with the visual basic editor
 
50 MS Excel Tips and Tricks
50 MS Excel Tips and Tricks 50 MS Excel Tips and Tricks
50 MS Excel Tips and Tricks
 
QESUTGPfbmYH3WQF257.pptx
QESUTGPfbmYH3WQF257.pptxQESUTGPfbmYH3WQF257.pptx
QESUTGPfbmYH3WQF257.pptx
 
Basics of excel for medical profession
Basics of excel for medical professionBasics of excel for medical profession
Basics of excel for medical profession
 
003.query
003.query003.query
003.query
 
Lesson9 working with basic functions
Lesson9 working with basic functionsLesson9 working with basic functions
Lesson9 working with basic functions
 
Getting started with Microsoft Excel Macros
Getting started with Microsoft Excel MacrosGetting started with Microsoft Excel Macros
Getting started with Microsoft Excel Macros
 
Microsoft Excel- basics
Microsoft Excel-  basicsMicrosoft Excel-  basics
Microsoft Excel- basics
 
Office excel tips and tricks 201101
Office excel tips and tricks 201101Office excel tips and tricks 201101
Office excel tips and tricks 201101
 
Useful macros and functions for excel
Useful macros and functions for excelUseful macros and functions for excel
Useful macros and functions for excel
 
AVB201.1 MS Access VBA Module 1
AVB201.1 MS Access VBA Module 1AVB201.1 MS Access VBA Module 1
AVB201.1 MS Access VBA Module 1
 
Advanced Filter Concepts in MS-Excel
Advanced Filter Concepts in MS-ExcelAdvanced Filter Concepts in MS-Excel
Advanced Filter Concepts in MS-Excel
 

Recently uploaded

Call Girls Kochi 💯Call Us 🔝 7426014248 🔝 Independent Kochi Escorts Service Av...
Call Girls Kochi 💯Call Us 🔝 7426014248 🔝 Independent Kochi Escorts Service Av...Call Girls Kochi 💯Call Us 🔝 7426014248 🔝 Independent Kochi Escorts Service Av...
Call Girls Kochi 💯Call Us 🔝 7426014248 🔝 Independent Kochi Escorts Service Av...
dipikamodels1
 
Real-Time Persisted Events at Supercell
Real-Time Persisted Events at  SupercellReal-Time Persisted Events at  Supercell
Real-Time Persisted Events at Supercell
ScyllaDB
 
CNSCon 2024 Lightning Talk: Don’t Make Me Impersonate My Identity
CNSCon 2024 Lightning Talk: Don’t Make Me Impersonate My IdentityCNSCon 2024 Lightning Talk: Don’t Make Me Impersonate My Identity
CNSCon 2024 Lightning Talk: Don’t Make Me Impersonate My Identity
Cynthia Thomas
 
Guidelines for Effective Data Visualization
Guidelines for Effective Data VisualizationGuidelines for Effective Data Visualization
Guidelines for Effective Data Visualization
UmmeSalmaM1
 
TrustArc Webinar - Your Guide for Smooth Cross-Border Data Transfers and Glob...
TrustArc Webinar - Your Guide for Smooth Cross-Border Data Transfers and Glob...TrustArc Webinar - Your Guide for Smooth Cross-Border Data Transfers and Glob...
TrustArc Webinar - Your Guide for Smooth Cross-Border Data Transfers and Glob...
TrustArc
 
Chapter 5 - Managing Test Activities V4.0
Chapter 5 - Managing Test Activities V4.0Chapter 5 - Managing Test Activities V4.0
Chapter 5 - Managing Test Activities V4.0
Neeraj Kumar Singh
 
So You've Lost Quorum: Lessons From Accidental Downtime
So You've Lost Quorum: Lessons From Accidental DowntimeSo You've Lost Quorum: Lessons From Accidental Downtime
So You've Lost Quorum: Lessons From Accidental Downtime
ScyllaDB
 
Elasticity vs. State? Exploring Kafka Streams Cassandra State Store
Elasticity vs. State? Exploring Kafka Streams Cassandra State StoreElasticity vs. State? Exploring Kafka Streams Cassandra State Store
Elasticity vs. State? Exploring Kafka Streams Cassandra State Store
ScyllaDB
 
Automation Student Developers Session 3: Introduction to UI Automation
Automation Student Developers Session 3: Introduction to UI AutomationAutomation Student Developers Session 3: Introduction to UI Automation
Automation Student Developers Session 3: Introduction to UI Automation
UiPathCommunity
 
ScyllaDB Real-Time Event Processing with CDC
ScyllaDB Real-Time Event Processing with CDCScyllaDB Real-Time Event Processing with CDC
ScyllaDB Real-Time Event Processing with CDC
ScyllaDB
 
intra-mart Accel series 2024 Spring updates_En
intra-mart Accel series 2024 Spring updates_Enintra-mart Accel series 2024 Spring updates_En
intra-mart Accel series 2024 Spring updates_En
NTTDATA INTRAMART
 
Call Girls Chennai ☎️ +91-7426014248 😍 Chennai Call Girl Beauty Girls Chennai...
Call Girls Chennai ☎️ +91-7426014248 😍 Chennai Call Girl Beauty Girls Chennai...Call Girls Chennai ☎️ +91-7426014248 😍 Chennai Call Girl Beauty Girls Chennai...
Call Girls Chennai ☎️ +91-7426014248 😍 Chennai Call Girl Beauty Girls Chennai...
anilsa9823
 
Call Girls Chandigarh🔥7023059433🔥Agency Profile Escorts in Chandigarh Availab...
Call Girls Chandigarh🔥7023059433🔥Agency Profile Escorts in Chandigarh Availab...Call Girls Chandigarh🔥7023059433🔥Agency Profile Escorts in Chandigarh Availab...
Call Girls Chandigarh🔥7023059433🔥Agency Profile Escorts in Chandigarh Availab...
manji sharman06
 
Communications Mining Series - Zero to Hero - Session 2
Communications Mining Series - Zero to Hero - Session 2Communications Mining Series - Zero to Hero - Session 2
Communications Mining Series - Zero to Hero - Session 2
DianaGray10
 
LF Energy Webinar: Carbon Data Specifications: Mechanisms to Improve Data Acc...
LF Energy Webinar: Carbon Data Specifications: Mechanisms to Improve Data Acc...LF Energy Webinar: Carbon Data Specifications: Mechanisms to Improve Data Acc...
LF Energy Webinar: Carbon Data Specifications: Mechanisms to Improve Data Acc...
DanBrown980551
 
Fuxnet [EN] .pdf
Fuxnet [EN]                                   .pdfFuxnet [EN]                                   .pdf
Fuxnet [EN] .pdf
Overkill Security
 
QR Secure: A Hybrid Approach Using Machine Learning and Security Validation F...
QR Secure: A Hybrid Approach Using Machine Learning and Security Validation F...QR Secure: A Hybrid Approach Using Machine Learning and Security Validation F...
QR Secure: A Hybrid Approach Using Machine Learning and Security Validation F...
AlexanderRichford
 
Containers & AI - Beauty and the Beast!?!
Containers & AI - Beauty and the Beast!?!Containers & AI - Beauty and the Beast!?!
Containers & AI - Beauty and the Beast!?!
Tobias Schneck
 
Day 4 - Excel Automation and Data Manipulation
Day 4 - Excel Automation and Data ManipulationDay 4 - Excel Automation and Data Manipulation
Day 4 - Excel Automation and Data Manipulation
UiPathCommunity
 
ScyllaDB Leaps Forward with Dor Laor, CEO of ScyllaDB
ScyllaDB Leaps Forward with Dor Laor, CEO of ScyllaDBScyllaDB Leaps Forward with Dor Laor, CEO of ScyllaDB
ScyllaDB Leaps Forward with Dor Laor, CEO of ScyllaDB
ScyllaDB
 

Recently uploaded (20)

Call Girls Kochi 💯Call Us 🔝 7426014248 🔝 Independent Kochi Escorts Service Av...
Call Girls Kochi 💯Call Us 🔝 7426014248 🔝 Independent Kochi Escorts Service Av...Call Girls Kochi 💯Call Us 🔝 7426014248 🔝 Independent Kochi Escorts Service Av...
Call Girls Kochi 💯Call Us 🔝 7426014248 🔝 Independent Kochi Escorts Service Av...
 
Real-Time Persisted Events at Supercell
Real-Time Persisted Events at  SupercellReal-Time Persisted Events at  Supercell
Real-Time Persisted Events at Supercell
 
CNSCon 2024 Lightning Talk: Don’t Make Me Impersonate My Identity
CNSCon 2024 Lightning Talk: Don’t Make Me Impersonate My IdentityCNSCon 2024 Lightning Talk: Don’t Make Me Impersonate My Identity
CNSCon 2024 Lightning Talk: Don’t Make Me Impersonate My Identity
 
Guidelines for Effective Data Visualization
Guidelines for Effective Data VisualizationGuidelines for Effective Data Visualization
Guidelines for Effective Data Visualization
 
TrustArc Webinar - Your Guide for Smooth Cross-Border Data Transfers and Glob...
TrustArc Webinar - Your Guide for Smooth Cross-Border Data Transfers and Glob...TrustArc Webinar - Your Guide for Smooth Cross-Border Data Transfers and Glob...
TrustArc Webinar - Your Guide for Smooth Cross-Border Data Transfers and Glob...
 
Chapter 5 - Managing Test Activities V4.0
Chapter 5 - Managing Test Activities V4.0Chapter 5 - Managing Test Activities V4.0
Chapter 5 - Managing Test Activities V4.0
 
So You've Lost Quorum: Lessons From Accidental Downtime
So You've Lost Quorum: Lessons From Accidental DowntimeSo You've Lost Quorum: Lessons From Accidental Downtime
So You've Lost Quorum: Lessons From Accidental Downtime
 
Elasticity vs. State? Exploring Kafka Streams Cassandra State Store
Elasticity vs. State? Exploring Kafka Streams Cassandra State StoreElasticity vs. State? Exploring Kafka Streams Cassandra State Store
Elasticity vs. State? Exploring Kafka Streams Cassandra State Store
 
Automation Student Developers Session 3: Introduction to UI Automation
Automation Student Developers Session 3: Introduction to UI AutomationAutomation Student Developers Session 3: Introduction to UI Automation
Automation Student Developers Session 3: Introduction to UI Automation
 
ScyllaDB Real-Time Event Processing with CDC
ScyllaDB Real-Time Event Processing with CDCScyllaDB Real-Time Event Processing with CDC
ScyllaDB Real-Time Event Processing with CDC
 
intra-mart Accel series 2024 Spring updates_En
intra-mart Accel series 2024 Spring updates_Enintra-mart Accel series 2024 Spring updates_En
intra-mart Accel series 2024 Spring updates_En
 
Call Girls Chennai ☎️ +91-7426014248 😍 Chennai Call Girl Beauty Girls Chennai...
Call Girls Chennai ☎️ +91-7426014248 😍 Chennai Call Girl Beauty Girls Chennai...Call Girls Chennai ☎️ +91-7426014248 😍 Chennai Call Girl Beauty Girls Chennai...
Call Girls Chennai ☎️ +91-7426014248 😍 Chennai Call Girl Beauty Girls Chennai...
 
Call Girls Chandigarh🔥7023059433🔥Agency Profile Escorts in Chandigarh Availab...
Call Girls Chandigarh🔥7023059433🔥Agency Profile Escorts in Chandigarh Availab...Call Girls Chandigarh🔥7023059433🔥Agency Profile Escorts in Chandigarh Availab...
Call Girls Chandigarh🔥7023059433🔥Agency Profile Escorts in Chandigarh Availab...
 
Communications Mining Series - Zero to Hero - Session 2
Communications Mining Series - Zero to Hero - Session 2Communications Mining Series - Zero to Hero - Session 2
Communications Mining Series - Zero to Hero - Session 2
 
LF Energy Webinar: Carbon Data Specifications: Mechanisms to Improve Data Acc...
LF Energy Webinar: Carbon Data Specifications: Mechanisms to Improve Data Acc...LF Energy Webinar: Carbon Data Specifications: Mechanisms to Improve Data Acc...
LF Energy Webinar: Carbon Data Specifications: Mechanisms to Improve Data Acc...
 
Fuxnet [EN] .pdf
Fuxnet [EN]                                   .pdfFuxnet [EN]                                   .pdf
Fuxnet [EN] .pdf
 
QR Secure: A Hybrid Approach Using Machine Learning and Security Validation F...
QR Secure: A Hybrid Approach Using Machine Learning and Security Validation F...QR Secure: A Hybrid Approach Using Machine Learning and Security Validation F...
QR Secure: A Hybrid Approach Using Machine Learning and Security Validation F...
 
Containers & AI - Beauty and the Beast!?!
Containers & AI - Beauty and the Beast!?!Containers & AI - Beauty and the Beast!?!
Containers & AI - Beauty and the Beast!?!
 
Day 4 - Excel Automation and Data Manipulation
Day 4 - Excel Automation and Data ManipulationDay 4 - Excel Automation and Data Manipulation
Day 4 - Excel Automation and Data Manipulation
 
ScyllaDB Leaps Forward with Dor Laor, CEO of ScyllaDB
ScyllaDB Leaps Forward with Dor Laor, CEO of ScyllaDBScyllaDB Leaps Forward with Dor Laor, CEO of ScyllaDB
ScyllaDB Leaps Forward with Dor Laor, CEO of ScyllaDB
 

Excel300

  • 1. SHARED COMPUTING SERVICES Microsoft Excel XP/2003, Level 300 Become a Microsoft Excel Power-User Updated 12/14/05 Logical Functions Use the IF Function =IF(Statement,IFtrue,IFfalse) Statement - comparison statement to which you try to match. Nest IF functions in the IFfalse IFtrue - result if the comparison matches the statement. area of the parent function. IFfalse - result if the comparison does not match the statement. Combine Logical Functions =IF(AND(Statement1,Statement2),IFtrue,IFfalse) — Ensures that both statements must be true. =IF(OR(Statement1,Statement2),IFtrue,IFfalse) — Tests both statements, either of which can be true. =IF(NOT(Statement),IFtrue,IFfalse) — Returns the reverse value fur the condition. Concatenate & Parse Cells (Merge & Split) Concatenate (Merge) Data in Separate Cells A B C 1 John Smith Smith, John =CONCATENATE(B1,“, ”,A1) 2 Jane Jones Jones, Jane =B2&“, ”&A2 Parse (Split) Data into Separate Cells 1. Select the cells to parse (split) 2. Go to Data Text to Columns… to open the wizard. 3. Step 1 of 3:Be sure Delimited is selected, then click Next > 4. Step 2 of 3:Select the delimiter type, then click Next > 5. Step 3 of 3: Choose the data type for the column if necessary, then click Finish When passing the interval code Use the DATEDIF Function to the DATEDIF function, enclose it Syntax: =DATEDIF(Date1,Date2,Interval) in quotes if you are passing a literal Date1 — first date, in standard Excel serial-date format. value to the function. Date2 — second date, in standard Excel serial-date format. Interval — unit of time for the result. Date1 must be ≤ Date2 or a #NUM! error will be returned. If either Date1 or Date2 is not a date, a #VALUE! error will be returned. Interval must be one of the following codes: Code Meaning Description "m" Months Number of complete months between Date1 and Date2. "d Days Number of days between Date1 and Date2. "y" Years Number of complete years between Date1 and Date2. "ym" Months Excluding Year Number of months between Date1 and Date2, as if Date1 and Date2 were in the same year. "yd" Days Excluding Years Number of days between Date1 and Date2, as if Date1 and Date2 were in the same year. "md" Days Excluding Months & Years Number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and year. When passing the interval code to the DATEDIF function, enclose it in quotes if you are passing a literal value to the function.
  • 2. Microsoft Excel XP/2003, Level 300 Become a Microsoft Excel Power-User Use HLOOKUP You must have a data table for which to lookup information indexed in horizontal rows. =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) lookup_value is the reference or address of the comparison cell. table_array is the data table range that is searched (including row labels). row_index_num indicates how many rows to move down. range_lookup is a logical argument that returns a true or false value (optional) Use VLOOKUP You must have a data table for which to lookup information indexed in vertical columns. =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) lookup_value is the reference or address of the comparison cell. table_array is the data table range that is searched (including column labels). col_index_num indicates how many columns to move the right. range_lookup is a logical argument that returns a true or false value (optional) Use Data Validation 1. Select the cell(s) for which you will define the data validation criteria. 2. Go to Data Validation… to open the Data Validation dialog box. The Settings tab is active. 3. Click the Allow: arrow to display a list of options, and then select the option you desire. 4. Click the Data: arrow to display a list of conditional operators, and then select the option you desire. Complete the remaining fields that are pertinent to your choices. 5. Click the Input Message tab and click in the Title: field. Type the title of the dialog box to appear. 6. Click the Input Message: field and type the message you’d like the dialog box to display. 7. Click the Error Alert tab, click the Style: arrow to choose an icon that will display. 8. Click the Title: field and type the title to appear. 9. Click the Error Message: field and type the message you’d like the dialog box to display. 10. Click OK . • Click Circle Invalid Data on the Auditing toolbar to locate cells that don’t meet the validation criteria. • Click Clear Validation Circles on the Auditing toolbar to clear the circle. Use Data Validation with Lookup Tables When creating the data validation, use a formula to reference the lookup table’s column in the Source: field. Visual Basic Record a Macro Edit a Macro 1. Go to Tools Macro Record New Macro… to • Open the Visual Basic window by going to record your actions for the macro. Tools Macro Macros… OR press ALT+F8. You can edit 2. Give the macro a name and description and click Close any macro using Visual Basic for Applications (VBA) code. 3. Perform the actions you would like recorded. 4. Click Stop on the Macros toolbar when finished Debug a Macro OR go to Tools Macro Stop Recording. • Open the Visual Basic window by going to • To run a macro, press the shortcut key if you provided Tools Macro Macros… one or go to Tools Macro Macros…, select the • Select the macro under Macro Name: and click Step Into . macro and click Run . You may also assign the macro to • Go to Debug Step Into OR press F8 to move to execute the a button as described below. next line of code. page 2 Shared Computing Services
  • 3. Microsoft Excel XP/2003, Level 300 Become a Microsoft Excel Power-User Create a Macro Button 1. Open the Forms toolbar and select the Button button. 2. Draw the button anywhere on the worksheet. 3. Select the macro to assign and click OK . 4. Select the button’s text and type your own button name. You may format the text any way you wish. 5. Click off of the button to deselect. • To run the macro, click the button with your mouse. • To edit the button, right-click on the button and select your options. User-Defined Functions Create a Function Procedure 1. Open Visual Basic Editor by going to Tools Macros Visual Basic Editor OR press ALT+F11. 2. Go to Insert Module to open the Code window. 3. Go to Insert Procedure… to open the Add Procedure dialog box. 4. Name the procedure and provide the arguments between the parentheses. 5. Define what the name of the function equals (what calculation to perform). Public Function Commission(SubTotal) Commission = SubTotal * 0.05 End Function Use an Application Object Name=Application.FunctionName(arguments) Public Function SalesTax(State) SalesTax = Application.VLookup(State, Range("Tax_Rates"), 3) End Function Control Procedure Flow If Name = “n” Then Name = Application.FunctionName(arguments) ElseIf Name = “n” Then Name = Application.FunctionName(arguments) End If Name = formula Public Function Discount(Sales, Customer) If Customer = "R" Then Discount = Application.VLookup(Sales, Range("Discounts"), 2) ElseIf Customer = "W" Then Discount = Application.VLookup(Sales, Range("Discounts"), 3) End If Discount = (-1) * Discount * Sales End Function Shared Computing Services page 3
  • 4. Microsoft Excel XP/2003, Level 300 Become a Microsoft Excel Power-User Write a Subroutine Public Function Discount(Sales, Customer) If Customer = "R" Then Discount = Application.VLookup(Sales, Range("Discounts"), 2) Discount = (-1) * Discount * Sales ElseIf Customer = "W" Then Discount = Application.VLookup(Sales, Range("Discounts"), 3) Discount = (-1) * Discount * Sales ElseIf Customer = "" Then Discount = "Please Enter Customer Code" Else Discount = "Invalid Customer Code" End If End Function Customize Excel Share a Workbook 1. With an Excel workbook open, go to Tools Share Workbook. 2. On the Editing tab, select Allow changes by more than one user at the same time. This also allows workbook merging. 3. On the Advanced tab, select the Automatically every option, and then click OK . Create a Cell Comment • Select a cell and go to Insert Comment and type your message. A red dot will be placed in the upper-right corner of the cell. • When you move your mouse cursor on a cell with a comment, the comment will appear. • Right-click a commented cell and select Edit Comment to edit the message. • Go to Edit Clear Comments to remove comments. Create Custom Cell Formats When creating a format for a cell, there are four types of numbers or text that you need to specify how you want them to appear. Positive Number; Negative Number; Zero; Text Code Display d Day (d=1, dd=01, ddd=Mon, dddd=Monday) m Month (m=1, mm=01, mmm=Jan, mmmm=January) y Year (yy=02, yyyy=2001) Insert space in a number format [when you follow an underscore with a closing parenthesis _), _ (underscore) positive numbers line up correctly with negative numbers that are enclosed in parentheses] * (asterisk) Repeat the next character in the format to fill the column width 0 Displays a digit, if one exists in the position. A zero displays if no digit exists. # Displays a digit, if one exists in the position. $ Displays a dollar sign in the position . Displays a decimal point in the position , Add commas as thousands separators @ Indicates that an alpha character is required & Indicates that an alpha character is an option “ABC” Displays anything inside quotation marks as literal characters Display for following character as literal [color] Displays the result in the color specified. (red, magenta, yellow, blue, cyan, green, black, white) ; Separates sections in the syntax of the cell page 4 Shared Computing Services
  • 5. Microsoft Excel XP/2003, Level 300 Become a Microsoft Excel Power-User Add a Background Image to a Worksheet 1. To add a background pattern, go to Format Sheet Background. 2. Browse to the image you want and click OK. 3. To remove the pattern, select Format Sheet Delete Background. Create a Custom List 1. Select an existing list and then go to Tools Options Custom Lists. 2. Click Import . You’ll see the cell references to the range of cells containing your list. OK . Edit the list by returning to Tools Options Custom Lists and select your list on the left. Edit the list to the right by pressing ENTER after each entry. Create a Custom Menu 1. Go to Tools Customize… Commands tab. 2. Scroll to and select New Menu under Categories: 3. Click and hold New Menu under Commands: and drag it up to the menu bar in any location you desire. 4. Select a category and drag the command of your choice to the new menu. • Rename the menu by right-clicking New Menu and edit the Name: field. Use the ampersand (&) in front of any character you’d like underlined for shortcuts. (i.e. F&ormat will be Format) Customize a Toolbar 1. Go to Tools Customize… Toolbars tab. 2. Click the Commands tab and select a category and drag the command of your choice to the new toolbar. 1. Edit any button while the Customize dialog box is still open by right-clicking any button and select Edit Button Image. 2. Move any button by holding ALT while dragging the button to a new location. 3. Remove the button by dragging it off the toolbar. 4. Reset any toolbar to the default by going to Tools Customize… Toolbars tab and click Reset . Customize How You Move 1. Tools Options Edit tab. 2. In the Move Selection after Enter drop-down box, choose the direction you want the selection to move to. Shared Computing Services page 5
  翻译: