excel tips for engineering

Upload: italo-venegas

Post on 08-Apr-2018

221 views

Category:

Documents


0 download

TRANSCRIPT

  • 8/6/2019 Excel Tips for Engineering

    1/17

    Microsoft Office Excel for EngineersExcel Primer

    Created By:

    Darren Henry, Director of Product Marketing

    Dassault Systmes SolidWorks Corporation

    2009 Dassault Systmes, All Rights Reserve

    This document outlines many of the commands reviewed in theSolidWorks screencast Microsoft Office Excel Tips for Engineers

  • 8/6/2019 Excel Tips for Engineering

    2/17

    Data Population

    Quickly populate adjacent cells with series of values

    Saturday Sunday Monday TuesdayJanuary

    Cell Addressing

    To lock down the cell location insert a dollar sign "$" in the cell address.

    quation How Excel interprets the address

    =A5 relative addressing

    =$A$5 equation will always reference cell A5 no matter where it is copied

    =$A5 equation will always reference column A, but will adjust the row number as it is c

    =A$5 equation will always reference row 5, but will adjust the column letter as it is copi

    In formulas, Excel uses relative addressing by default. As you copy formulas through your

    spreadsheet it will adjust the cell locations.

    Darren's Tips: Use the F4 key to quickly cycle through the various address

    combinations

  • 8/6/2019 Excel Tips for Engineering

    3/17

    pied

    d

  • 8/6/2019 Excel Tips for Engineering

    4/17

    Data Validation (List)Location: Data Data Validation

    Possible Uses:Prompting for options and accessories

    Selecting languages, units, or styles

    Example:

    Color hexadecimal

    Blue 16711680

    Color Red Green Blue Hexadecimal

    Black 0 0 0 0

    Red 255 0 0 255

    Orange 255 128 0 33023

    Green 0 255 0 65280

    Blue 0 0 255 ###

    Purple 255 128 255 ###

    Turquoise 0 255 255 ###White 255 255 255 ###

    Use Data Validation to make user friendly pick lists

    Darren's Tips: The list used for validation must reside on the same workshthe cell being validated. The "Source" field of a Data Validation list can utilfunctions to add another level of customization.

  • 8/6/2019 Excel Tips for Engineering

    5/17

    eet asize "IF"

  • 8/6/2019 Excel Tips for Engineering

    6/17

    IFSyntax: IF(logical_test,value_if_true,value_if_false)

    Possible Uses:

    Caster?

    Example: Yes R

    value if a condition you specify evaluates to TRUE and another value if itevaluates to FALSE.

    Millions of uses, from conditional formulas based on dimensions, to partnumber prefixes based on options. When driving CAD models with Excel,use IFfunctions to control whether a component is suppressed or resolved.

    Darren's Tips: You can nest up to six IF functions, but if there is more thanthree, consider storing data in a table and use Vlookup.

    $STATE@Caster

    Darren Henry:Here an IFfunctionsuppresses or resolvesa caster componentbased on a user'schoice. Type in "No" incell B11 to see thechange.

  • 8/6/2019 Excel Tips for Engineering

    7/17

    CONCATENATESyntax: CONCATENATE (text1,text2,...)

    Possible Uses:

    Piecing together part numbers

    Material Length ole spacing

    Example: SS 12 1.5

    Special Character

    - Part Numb SS-12-1.5

    .

    Darren's Tips: You can explicitly type in any symbols that separate prefixesand suffixes, but I find if you list them in individual cells, it aids in edit-ability

  • 8/6/2019 Excel Tips for Engineering

    8/17

    &Syntax: "Text1"&" Text 2"

    Possible Uses:Piecing together part numbers

    Darren's Tips: Remember to add " " Space between variables when desired

    material Length hole spacing

    Example: SS 12 15

    Part Number SS - 12 - 15

    .

    Darren Henry:Here we used a simpleformula with & to piece

    together a partnumber. We explicitlytyped in the dashesand spaces.

  • 8/6/2019 Excel Tips for Engineering

    9/17

    VLOOKUP

    Possible Uses:

    Example: Color hexadecimalBlack 0

    Color Red Green Blue Hexadecimal

    Black 0 0 0 0

    Red 255 0 0 255

    Orange 255 128 0 33023

    Green 0 255 0 65280

    Blue 0 0 255 16711680

    Purple 255 128 255 16744703

    Turquoise 0 255 255 16776960White 255 255 255 16777215

    Syntax: VLOOKUP(lookup_value,table_array,col_index_num,range_look

    Searches for a value in the leftmost column of a table, and then returns avalue in the same row from a column you specify in the table.

    Use instead of writing nested IFstatements, finding part number prefixes andsuffixes, finding correct hexadecimal color code, etc

    ,not find a match if there is and extra space in the text string. You canVlookup from different worksheets, so organize your tables by storing data onother worksheets

    Darren Henry:Vlookup is locatingthe color in the tableand then returningthe correspondingvalue in the 5thcolumn.

  • 8/6/2019 Excel Tips for Engineering

    10/17

    p)

  • 8/6/2019 Excel Tips for Engineering

    11/17

  • 8/6/2019 Excel Tips for Engineering

    12/17

    INTSyntax: INT(number)

    Rounds a number down to the nearest integer.

    Possible Uses:Use when calculating number of instances in patterns

    Example 1: Number Integer

    2.8 2

    Example 2: overall length 18hole spacing 3.5

    number of holes 5

    Darren's Tips: INTdoes not round up, so accurate hole calculations may require multipleformulas

  • 8/6/2019 Excel Tips for Engineering

    13/17

    Custom ViewsFound under Views --> Custom Views

    Quickly toggle betweens showing your control panel, and the entire designtable

    Darren Henry:a few custom viewshave been created. Goto View->Custom Viewto see them.

  • 8/6/2019 Excel Tips for Engineering

    14/17

    CONVERTSyntax: CONVERT(number,from_unit,to_unit)

    Possible Uses:

    Heat Converter2400 J equals 2.275 BTU

    Use Convert to convert a number from one unit of measurement toanother.

    .spreadsheet to specify the type of units they are familiar with, andconvert all data to suite.

    Darren's Tips: Specify the from_unit and to_unit in cells, so they canbe changed quickly.

    Darren Henry:Convert was used inthis cell to calculate thevalue of BTUs

    Darren Henry:Enter any value in thiscell and it will beconverted from Joulesto BTUs

  • 8/6/2019 Excel Tips for Engineering

    15/17

    Paste SpecialLocation: Available from Right Mouse Button, once a cell has been sele

    Possible Uses:

    Update price sheets or quantities in BOMs

    Price New Price Multiplier

    $100.00 0.9

    $150.00

    $200.00

    $250.00

    $300.00

    $350.00

    Enables the user to copy more than a cells value. Includes the ability to copydata validation restrictions as well as perform bulk mathematical tasks.

    Darren Henry:Select cell D20 and chooseCopy, then highlight theprices and use Paste Special,Multiply. All the numberswill be multiplied by 0.9

  • 8/6/2019 Excel Tips for Engineering

    16/17

    cted for copy

  • 8/6/2019 Excel Tips for Engineering

    17/17

    Copying from PDF files

    Many product spec sheets and property tables are available as PDF files. Often youmay copy the data for use with 3D CAD models.

    In Adobe Acrobat, when selecting large amounts of tabular data, use the rightmouse button option: "Copy as Table" to aid in copying the data