آموزش Google Spreadsheets-En

[A M I R]

[h=2]Working with Functions

[h=3]Basic Functions
A function is a predefined formula that performs calculations using specific values

in a particular order. One of the key benefits of functions is that they can save you time

because you do not have to write the whole formula yourself. Spreadsheets has several

functions readily available and it provides access to hundreds of other functions to assist with your calculations


In order to use functions correctly, you will need to understand the different parts

of a function and how to create arguments in functions to calculate values and cell references




​

 

[A M I R]

[h=2]Working with Functions

[h=4]The Parts of a Function
Similarly to entering a formula, the order in which you enter a function into a

cell is important. Each function has a specific order, called syntax, that must be

followed in order for the function to calculate properly. The basic syntax to create a

formula with a function is to insert an equal sign (=), a function name

(AVERAGE, for example, is the function name for finding an average), and

an argument. Arguments contain the information you want the formula to

calculate, such as a range of cell references

Syntax of a basic function
​

 

[A M I R]

[h=2]Working with Functions

[h=4]Working with Arguments
Arguments must be enclosed in parentheses (). Individual values

or cell references inside the parentheses are separated by either colons or commas


Colons create a reference to a range of cells. For

example, =SUM(F21:F27) would calculate the sum of the cell range F21 through F27


Commas separate individual values, cell references, and cell

ranges. If there is more than one argument, each argument must

be separated by a comma. For example, =COUNT(L5:L8,L12:L20,L25) will

count all the cells in the three arguments that are included in parentheses




​

 

[A M I R]

[h=2]Working with Functions

[h=3]Creating a Function
Google Spreadsheets has a wide variety of functions available. Here

are some of the most common functions you'll use

SUM: The SUM function adds all the values of the selected cells in

the argument. This function is useful for quickly adding values in a range of cells

AVERAGE: The AVERAGE function will find the average of the values included in

the argument. It calculates the sum of the cells and then divides the sum by the

number of cells in the argument

COUNT: Using the COUNT function will display the number of cells that have been

included in the argument. This function is useful for quickly counting items on the sheet


MAX: The MAX function displays the highest cell value included in the argument.


MIN: The MIN function displays the lowest cell value included in the argument.

​

 

[A M I R]

[h=2]Working with Functions

[h=4]To Create a Function:
Select the cell where the answer will appear (for example, cell C15).

Type the equal sign (=) and type the desired function name (for example, AVERAGE).

Enter the cells for the argument inside parentheses (for example, C5:C14).

Press the Enter key on your keyboard and the answer appears.

​

 

[A M I R]

[h=2]Working with Functions

[h=4]To Create a Function using the Function Button:

The Function Button allows you to automatically return the results for a

range of cells. The answer will display in the cell below the range


Select the range of cells you wish to include in the argument (for example, D2127).

Click the Functions button and select the desired function from the drop-down menu (for example, SUM).

In the cell directly below the selected cells, the function appears.

Press the Enter key on your keyboard and the answer appears.

Like formulas, functions can be copied to adjacent cells. Select the cell that contains

the function and the fill handle displays. Click, hold and drag the fill handle over the cells you wish to fill

Copying a function into multiple cells

​

 

[A M I R]

[h=2]Working with Functions

[h=3]Google Spreadsheets Function List

If you have experience using spreadsheets and would like to use Google

Spreadsheets to make more advanced calculations, then you may wish

to explore the Google Spreadsheets function list. It is a handy reference

for hundreds of financial, statistical, and data analysis functions

If you are familiar with functions found in Microsoft Excel's Function Library, you

will find that the Google Spreadsheets function list has many of the same functions




​

 

[A M I R]

[h=2]Working with Functions

[h=4]To Access the Function List:
Click the Functions button and select More functions... from the drop-down

menu. The Google spreadsheets function list will appear in a new browser tab

Selecting More functions...

​

 

[A M I R]

[h=2]Sorting and ******ing Data

[h=3]Introduction

Google Spreadsheets allows you to analyze and work with a significant amount of data. As you

add more content to your spreadsheet, knowing how to locate specific information in it becomes

important. Spreadsheets allows you reorganize your data by sorting and applying ******s to

it. You can sort your data by arranging it alphabetically or numerically, or you can apply a ******

to narrow down the data and hide some of it from view



In this lesson, you will learn how to sort data to better view and organize the

contents of your spreadsheet. You will also learn how to ****** data to display

only the information you need

​

 

[A M I R]

Sorting and F i l t ering Data

Sorting and F i l t ering Data


​

When working with Spreadsheets, sometimes you may wish to reorganize the data so

that you can easily find the information you're looking for. Sorting allows you to change

or customize the order of your spreadsheet data. For example, in preparation for a

charity event, you could organize the volunteer list by name and t-shirt size so you

can determine how many t-shirts you need in each size.
​

 

[A M I R]

[h=2]Sorting and ******ing Data

******s can be applied in many different ways to improve the performance of your

worksheet. They are useful for displaying only the data that interests you. For

example, you could apply a ****** to a party guest list to view only the people who

responded to the invitation, or sort the genres of a song list to display only rock

and roll songs or classical music


​

 

[A M I R]

[h=2]Sorting and ******ing Data

Sort sheet organizes all of the data in your spreadsheet by one column. Related

information across each row is kept together when the sort is applied. In the image

below, the Name column has been sorted to display the client names in alphabetical

order. Each client's address information has been kept with each corresponding name

Sort range sorts the data in a range of cells. When working with a

sheet that contains several tables, you may wish to sort the data of a

single table. Sorting a range will not affect the data on the rest of the

sheet and it will keep related information across the row together

Sorting a range of cells
​

 

[A M I R]

[h=2]Sorting and ******ing Data

[h=4]To Sort a Sheet:

In our example, we will sort a library's books on loan list to easily see which

books have been checked out most often. We will freeze the header row so the header labels will not be included in the sort


Click View and hover the mouse over Freeze rows. Select Freeze 1 row from the menu that appears.

The header row freezes. Decide which column will be sorted and click on a

cell in the column. In our example, we will click on a cell in the Loan Count

column to reorganize the books by the number of times they have been loaned

Click Data and select Sort Sheet by column, A-Z (ascending), or Sort Sheet by

column, Z-A (descending). In our example, we will select Sort Sheet by

column, Z-A to display the most often loaned books at the top of the list

The sheet will be sorted according to your selection.

​

 

[A M I R]

[h=2]Sorting and ******ing Data

[h=4]To Sort a Range:

In our example, we will sort a range of data in an exercise log. We will reorganize

the data to display the jogging distance in ascending order


Highlight the range of cells you wish to sort. In this example, we will select the range C21:E27.

Click Data and select Sort range... from the drop-down menu.

The Sorting dialog box appears. Select the desired column you wish to sort by and then select ascending or descending.


Click Sort.

The range will be sorted according to your selections (in our example, the

data has been sorted in ascending order according to the distance column

​

 

[A M I R]

[h=2]Sorting and ******ing Data

[h=4]To Create a ******:
In our example, we will apply a ****** to narrow down the data in our spreadsheet. We will display only the animal and nature books.


Click any cell that contains data.

Click the ****** button.

A drop-down arrow

appears in each column header.

Decide which column you will apply a ****** to. Click the column's drop-down arrow and a ******ing

options box appears. You will see the values from that column as a checklist inside the

box. In our example, the checklist is showing all of the genres we typed in the column

Click Clear to remove all of the checks.

Select the data you wish to display on the spreadsheet. In this

example, we will select Animals and Nature. Click OK

The sheet will be ******ed according to your data selection. A

****** symbol

appears in the ******ed column's header cell

​

 

[A M I R]

[h=2]Sorting and ******ing Data

[h=4]Applying Multiple ******s
When you create a ****** in your spreadsheet, each column header on your

sheet will have a drop-down arrow for creating new ******s. ******s are cumulative, meaning

you are free to use as many as you need to narrow your data to the desired result. In the

image below, the library's books on loan list has been ******ed by genre and loan count. As a

result of the two ******s, Spreadsheets displays animal and nature books that have been loaned more than four times

Viewing multiple ******s

​

 

[A M I R]

[h=2]Sorting and ******ing Data

[h=4]To Clear All ******s:

Click the ****** button and the spreadsheet will return to its original appearance.

Removing a ******



​