excel - 2010 - advanced, Notas de estudo de Engenharia Civil

Baixe excel - 2010 - advanced e outras Notas de estudo em PDF para Engenharia Civil, somente na Docsity!

2 Download free ebooks at bookboon.com

Stephen Moffat, The Mouse Training Company

Excel 2010 Advanced

Download free ebooks at bookboon.com

Excel 2010 Advanced Contents

Contents

Introduction 7 1 Advanced worksheet functions Conditional & Logical Functions (^) 10 9 Counting And Totalling Cells Conditionally And, Or, Not 1425 Lookup Functions 31 2 Views, scenarios, goal seek, solver Goal Seeking And Solving (^39) 40 Advanced Solver Features Scenarios 4751 Views 59 3 Using excel to manage lists Excel Lists,List Terminology (^) (^64) 64 Sorting Data Subtotals 6573 Filtering A List Advanced Filtering (^7987)

Stand out from the crowd Designed for graduates with less than one year of full-time postgraduate workexperience, London Business School’s Masters in Management will expand your thinking and provide you with the foundations for a successful career in business.The programme is developed in consultation with recruiters to provide you withthe key skills that top employers demand. Through 11 months of full-time study, you will gain the business knowledge and capabilities to increase your careerchoices and stand out from the crowd. Applications are now open for entry in September 2011. For more information visit www.london.edu/mim/email [email protected] or call +44 (0)20 7000 7573

Masters in Management

London Regent’s ParkLondon NW1 4SA Business School United KingdomTel +44 (0)20 7000 7573Email [email protected]/mim/

Fast-track

your career

Please click the advert

Download free ebooks at bookboon.com

Excel 2010 Advanced Contents Criteria Tips Multiple Criteria (^9193) Calculated Criteria Data Consolidation 10294 Pivottables Modifying A Pivottable (^106117) Managing Pivottables Formatting A Pivottable (^123130) Banding Slicers 131135 4 Charts Introduction To Charting (^) (^143) 143 Creating Charts Formatting Charts (^145157) Changing The Chart Layout Sparklines 163177 5 Templates Introduction To Templates (^) (^184) 185 Create Custom Templates 187

© UBS 2010. All rights reserved.

www.ubs.com/graduates

Looking for a career where your ideas could really make a difference? UBS’sGraduate Programme and internships are a chance for you to experience for yourself what it’s like to be part of a global team that rewards your inputand believes in succeeding together. Wherever you are in your academic career, make your future a part of oursby visiting www.ubs.com/graduates.

You’re full of energy and ideas. And that’s just what we are looking for.

Please click the advert

Download free ebooks at bookboon.com

Excel 2010 Advanced Introduction

Introduction Excel 2010 is a powerful spreadsheet application that allows users to produce tables containing calculations and graphs. These can range from simple formulae through to complex functions and mathematical models.

How To Use This Guide

This manual should be used as a point of reference after following attendance of the advanced level Excel 2010 training course. It covers all the topics taught and aims to act as a support aid for any tasks carried out by the user after the course.

The manual is divided into sections, each section covering an aspect of the advanced course. The table of contents lists the page numbers of each section and the table of figures indicates the pages containing tables and diagrams.

Objectives

Sections begin with a list of objectives each with its own check box so that you can mark off those topics that you are familiar with following the training.

Instructions

Those who have already used a spreadsheet before may not need to read explanations on what each command does, but would rather skip straight to the instructions to find out how to do it. Look out for the arrow icon which precedes a list of instructions.

Appendices

The Appendices list the Ribbons mentioned within the manual with a breakdown of their functions and tables of shortcut keys.

Keyboard Keys are referred to throughout the manual in the following way: [ ENTER ] – Denotes the return or enter key, [ DELETE ] – denotes the Delete key and so on. Where a command requires two keys to be pressed, the manual displays this as follows: [ CTRL ] + [ P ] – this means press the letter “p” while holding down the Control key. Commands When a command is referred to in the manual, the following distinctions have been made:

Download free ebooks at bookboon.com

Excel 2010 Advanced Introduction When Ribbon commands are the group name – FONT group and then referred to, the manual will refer you to the Ribbon – E.g. “Choose B for bold ”. home from the Ribbons

When dialog box options are referred to, the following style has been used for the text – “In the of the PRINT dialog, click the CURRENT PAGE option” PAGE RANGE section

Dialog box buttons are shaded and boxed – “Click OK to close the PRINT dialog and launch the print.” Notes Within each section, any items that need further explanation or extra attention devoted to them are denoted by shading. For example:

“Excel will not let you close a file that you have not already saved changes to without prompting you to save.” Tips At the end of each section there is a page for you to make notes on and a “Useful Information” heading where you will find tips and tricks relating to the topics described within the section.

Download free ebooks at bookboon.com

Excel 2010 Advanced Advanced worksheet functions Conditional & Logical Functions Excel has a number of logical functions which allow you to set various “conditions” and have data respond to them. For example, you may only want a certain calculation performed or piece of text displayed if certain conditions are met. The functions used to produce this type of analysis are found in the Insert, Function menu, under the heading LOGICAL.

If Statements

The IF function is used to analyse data, test whether or not it meets certain conditions and then act upon its decision. The formula can be entered either by typing it or by using the Function Library on the formula’s ribbon, the section that deals with logical functions Typically, the IF statement is accompanied by three arguments enclosed in one set of parentheses; the condition to be met (logical_test); the action to be performed if that condition is true (value_if_true); the action to be performed if false (value_if_false). Each of these is separated by a comma, as shown; =IF ( logical_test, value_if_true, value_if_false) To view IF function syntax: Mouse

1. Click the drop down arrow next to the FORMULAS Ribbon; LOGICAL button in the FUNCTION LIBARY Groupon the
2. 3. A dialog box will appearThe three arguments can be seen within the box

Download free ebooks at bookboon.com

Excel 2010 Advanced Advanced worksheet functions

Logical Test

This part of the IF statement is the “condition”, or test. You may want to test to see if a cell is a certain value, or to compare two cells. In these cases, symbols called LOGICAL OPERATORS are useful;

**> < Greater thanLess than

= < = Greater than or equal toLess than or equal to <>**^ =^ Not equal toEqual to Therefore, a typical logical test might be spreadsheet is greater than the value in cell B2. Names can also be included in the logical test, so if cells B1 > B2, testing whether or not the value contained in cell B1 and B1 B2 of the were respectively named include text strings. If you want to check a cell to see if it contains text, that text string must be included in quotation SALES and TARGET , the logical test would read SALES > TARGET. Another type of logical test could marks. For example, cell C5 could be tested for the word YES as follows; C5 =” YES ”. It should be noted that Excel’s logic is, at times, brutally precise. In the above example, the logical test is that sales should be greater than target. If sales are equal to target, the IF statement will return the false value. To make the logical test more flexible, it would be advisable to use the operator >= to indicate “meeting or exceeding”.

Value If True / False

Provided that you remember that If desired, a simple number could be returned, a calculation performed, or even a piece of text entered. Also, the type TRUE value always precedes FALSE value, these two values can be almost anything. of data entered can vary depending on whether it is a true or false result. You may want a calculation if the logical test is true, but a message displayed if false. (Remember that text to be included in functions should be enclosed in quotes).

Download free ebooks at bookboon.com

Excel 2010 Advanced Advanced worksheet functions A particularly common use of both the true and false values are text strings. For example, if a sales figure exceeds a certain amount, a rating of “ IF statements is to produce “ratings” or “comments” on figures in a spreadsheet. For this, GOOD ” is returned, otherwise the rating is “ POOR ”; =IF(B2>1000,”GOOD”,”POOR”)

Nested If

When you need to have more than one condition and more than two possible outcomes, a is based on the same principle as a normal IF statement, but involves “nesting” a secondary formula inside the main one. NESTED IF is required. This The secondary IF forms the FALSE part of the main statement, as follows; =IF(1st logic test , 1st true value , IF(2nd logic test , 2nd true value , false value)) Only if both logic tests are found to be false will the false value be returned. Notice that there are two sets of parentheses, as there are two separate IF statements. This process can be enlarged to include more conditions and more eventualities - up to seven of parentheses are added. IF ’s can be nested within the main statement. However, care must be taken to ensure that the correct number

In the example, sales staff could now receive one of three possible ratings; =IF(B2>1000,”GOOD”,IF(B2<600,”POOR”,”AVERAGE”)) To make the above instead of figures. In the example, column E has been used to hold the upper and lower sales thresholds. IF statement more flexible, the logical tests could be amended to measure sales against cell references

=IF(B2>$E$2,”GOOD”,IF(B2<$E$3,”POOR”,”AVERAGE”)) (If the IF statement is to be copied later, this cell reference should be absolute).

N.B. The depth of nested IF functions has been increased to 64 as previous versions of excel only nested 7 deep

Download free ebooks at bookboon.com

Excel 2010 Advanced Advanced worksheet functions Counting And Totalling Cells Conditionally Occasionally you may need to create a total that only includes certain cells, or count only certain cells in a column or row.

The example above shows a list of orders. There are two headings in bold at the bottom where you need to generate a) the total amount of money spent by Viking Supplies and b) the total number of orders placed by Bloggs & Co.

The only way you could do this is by using functions that have conditions built into them. A condition is simply a test that you can ask Excel to carry out the result of which will determine the result of the function.

Statistical If Statements

A very useful technique is to display text or perform calculations only if a cell is the maximum or minimum of a range. In this case the logical test will contain a nested statistical function (such as MAX or MIN ). If, for example, a person’s sales cell is the maximum in the sales column, a message stating “Top Performer” could appear next to his or her name. If the logical test is false, a blank message could appear by simply including an empty set of quotation marks. When typing the logical test, it should be understood that there are two types of cell referencing going on. The first is a reference to one person’s figure, and is therefore relative. The second reference represents the RANGE of everyone’s figures, and should therefore be absolute. =IF(relative cell = MAX(absolute range) , “Top Performer” , “”)

Download free ebooks at bookboon.com

Excel 2010 Advanced Advanced worksheet functions

Sumif

You can use this function to say to Excel, “Only total the numbers in the column is Viking Supplies”. The syntax of the SUMIF () function is detailed below: Total column where the entry in the Customer

=SUMIF(range,criteria,sum_range) RANGE is the range of cells you want to test. CRITERIA. example, criteria can be expressed as 32, “32”, “>32”, “apples”. It is the criteria in the form of a number, expression, or text that defines which cells will be added. For

SUM RANGE. range match the criteria. If sum range is omitted, the cells in range are summed. These are the actual cells to sum. The cells in sum range are summed only if their corresponding cells in

=SUMIF(B2:B11, “Viking Supplies”, F2:F11) With the example above, the look as above. SUMIF function that you would use to generate the VIKING SUPPLIES TOTAL would

Using the INSERT FUNCTION tool the dialog would look like this and show any errors in entering the values or ranges

Download free ebooks at bookboon.com

Excel 2010 Advanced Advanced worksheet functions

Countif

countif counts the number of cells in a range based on agiven criteria. COUNTIF(range,criteria) RANGE and text values are ignored.is one or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank

CRITERIA counted. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, orIS the criteria in the form of a number, expression, cell reference, or text that defines which cells will be B.

To use COUNTIF function Mouse

1. 2. Click on theClick on STATISTICAL MORE FUNCTIONS. button in the FORMULAS group on the FORMULAS ribbon
2. Select COUNTIF from the displayed functions. A dialog will be displayed

Download free ebooks at bookboon.com

Excel 2010 Advanced AVERAGEIF(Range, Criteria, [Average Range])^ Advanced worksheet functions RANGE is one or more cells to average, including numbers or names, arrays, or references that contain numbers. CRITERIA For example, criteria can be expressed as 32, “32”, “>32”, “apples”, orIS the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. B.

AVERAGe_range is the actual set of cells to average. If omitted, RANGE is used. Here is an example that returns the average of B2:B5 where the corresponding value in column A is greater than 250,000 : =AVERAGEIF(A2:A5, “>250000”, B2:B5) To use AVERAGEIF function Mouse

1. Click on the on STATISTICAL MORE FUNCTIONS. button in the FORMULAS group on the FORMULAS ribbon and Click
2. Select AVERAGEIF from the displayed functions. A dialog will be displayed
3. 4. Click inSelect the range of cells containing the .values you wish checked against the criteria. RANGE text box.
4. Click in the you wish to check the range against. CRITERIA box, either, type criteria directly in the box or select a cell that contains the value
5. 7. Click in theClick ok. AVERAGE_RANGE text box and select the range you wish to average..

Averageifs

Average ifs is a new function to excel and does much the same as the multiple criteria. Averageif function but it will average a range using

Download free ebooks at bookboon.com

Excel 2010 Advanced Advanced worksheet functions

To use AVERAGEIFS function Mouse

1. Click on the on STATISTICAL MORE FUNCTIONS. button in the FORMULAS group on the FORMULAS ribbon and Click
2. Select AVERAGEIFS from the displayed functions. A dialog will be displayed.
3. 4. Click inSelect the range of cells containing the .values you wish checked against the criteria. AVERAGE_RANGE text box.
4. Click in the criteria against. CRITERIA_RANGE1 box select a range of cells that contains the values you wish to check the
5. 7. Click in theRepeat steps 5 and 6 to enter multiple criteria, range2, range3 etc, use the scroll bar on the right to scroll CRITERIA1 Text box and type in the criteria to measure against your CRITERIA_RANGE1. down and locate more range and criteria text boxes.Click entered. OK when all ranges and criterias have been

Some important points about AVERAGEIFS function