SQL : PostgreSQL

  Aggregate Functions Like most other relational database products,  PostgreSQL  supports  aggregate functions . An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the  count ,  sum ,  avg  (average),  max  (maximum) and  min  (minimum) over a set of rows. As an example, we can find the highest low-temperature reading anywhere with: SELECT max(temp_lo) FROM weather; max ----- 46 (1 row) If we wanted to know what city (or cities) that reading occurred in, we might try: SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG but this will not work since the aggregate  max  cannot be used in the  WHERE  clause. (This restriction exists because the  WHERE  clause determines which rows will be included in the aggregate calculation; so obviously it has to be evaluated before aggregate functions are computed.) However, as is o...
Post a Comment

Function in spreadsheets

 



Quick reference: Functions in spreadsheets

As a quick refresher, a function is a preset command that automatically performs a specific process or task using the data in a spreadsheet. Functions give data analysts the ability to do calculations, which can be anything from simple arithmetic to complex equations. Use this reading to help you keep track of some of the most useful options.

Functions

The basics

  • Just like formulas, start all of your functions with an equal sign; for example =SUM. The equal sign tells the spreadsheet that what follows is part of a function, not just a word or number in a cell. 

  • After you type the equal sign, most spreadsheet applications will display an autocomplete menu that lists valid functions, names, and text strings. This is a great way to create and edit functions while avoiding typing and syntax errors.

  • A fun way to learn new functions is by simply typing an equal sign and a single letter of the alphabet. Choose one of the options that pops up and learn what that function does.

Difference between formulas and functions

  • A formula is a set of instructions used to perform a calculation using the data in a spreadsheet.

  • A function is a preset command that automatically performs a specific process or task using the data in a spreadsheet.

Popular functions

A lot of people don’t realize that keyboard shortcuts like cut, save, and find are actually functions. These functions are built into an application and are amazing time-savers. Using shortcuts lets you do more with less effort. They can make you more efficient and productive because you are not constantly reaching for the mouse and navigating menus. The following table shows some of the most popular shortcuts, for Chromebook, PC, and Mac:

Command

Chromebook

PC

Mac

Create new workbook

Control+N

Control+N

Command+N

Open workbook

Control+O

Control+O

Command+O

Save workbook

Control+S

Control+S

Command+S

Close workbook

Control+W

Control+W

Command+W

Undo

Control+Z

Control+Z

Command+Z

Redo

Control+Y

Control+Y

Command+Y

Copy

Control+C

Control+C

Command+C

Cut

Control+X

Control+X

Command+X

Paste

Control+V

Control+V

Command+V

Paste values only

Control+Shift+V

Control+Shift+V

Command+Shift+V

Find

Control+Shift+F

Control+F

Command+F

Find and replace

Control+H

Control+H

Command+Shift+F

Insert link

Control+K

Control+K

Command+K

Bold

Control+B

Control+B

Command+B

Italicize

Control+I

Control+I

Command+I

Underline

Control+U

Control+U

Command+U

Zoom in

Control+Plus (+)

Control+Plus (+)

Option+Command+Plus (+)

Zoom out

Control+Minus (-)

Control+Minus (-)

Option+Command+Minus (-)

Select column

Control+Spacebar

Control+Spacebar

Command+Spacebar

Select row

Shift+Spacebar

Shift+Spacebar

Up Arrow+Spacebar

Select all cells

Control+A

Control+A

Command+A

Edit the current cell

Enter

F2

F2

Comment on a cell

Ctrl + Alt + M

Alt+I+M

Option+Command+M

Insert column to the left

Ctrl + Alt + = (with existing column selected)

Alt+Shift+I, then C

⌘ + Option + = (with existing column selected)

Insert column to the right

Alt + I, then O

Alt+Shift+I, then O

Ctrl + Option + I, then O

Insert row above

Ctrl + Alt + = (with existing row selected)

Alt+Shift+I, then R

⌘ + Option + = (with existing row selected)

Insert row below

Alt + I, then R, then B

Alt+Shift+I, then B

Ctrl + Option + I, then B

Auto-filling

The lower-right corner of each cell has a fill handle. It is a small green square in Microsoft Excel and a small blue square in Google Sheets.

  • Click the fill handle for a cell and drag it down a column to auto-fill other cells in the column with the same formula or function used in that cell. 

  • Click the fill handle for a cell and drag it across a row to auto-fill other cells in the row with the same formula or function used in that cell. 

Relative, absolute, and mixed references

  • Relative references (cells referenced without a dollar sign, like A2) will change when you copy and paste the function into a different cell. With relative references, the location of the cell that contains the function determines the cells used by the function.

  • Absolute references (cells fully referenced with a dollar sign, like $A$2) will not change when you copy and paste the function into a different cell. With absolute references, the cells referenced always remain the same.

  • Mixed references (cells partially referenced with a dollar sign, like $A2 or A$2) will change when you copy and paste the function into a different cell. With mixed references, the location of the cell that contains the function determines the cells used by the function, but only the row or column is relative (not both).

  • In spreadsheets, you can press the F4 key to toggle between relative, absolute, and mixed references in a function. Click the cell containing the function, highlight the referenced cells in the formula bar, and then press F4 to toggle between and select relative, absolute, or mixed referencing.

Data ranges

  • When you click a cell that contains a function, colored data ranges in the formula bar indicate which cells are being used in the spreadsheet. There are different colors for each unique range in a function.

  • Colored data ranges help prevent you from getting lost in complex functions.

  • In spreadsheets, you can press the F2 key to highlight the range of data used by a function. Click the cell containing the function, highlight the range of data used by the function in the formula bar, and then press F2. The spreadsheet will go to and highlight the cells specified by the range.

Data ranges evaluated for a condition

COUNTIF is an example of a function that returns a value based on a condition that the data range is evaluated for. The function counts the number of cells that meet the criteria. For example, in an expense spreadsheet, use COUNTIF to count the number of cells that contain a reimbursement for "airfare."

For more information, refer to:

Conclusion

There are a lot more functions that can help you make the most of your data. This is just the start. You can keep learning how to use functions to help you solve complex problems efficiently and accurately throughout your entire career.

Comments

Post a Comment

Popular posts from this blog

SQL : PostgreSQL

  Aggregate Functions Like most other relational database products,  PostgreSQL  supports  aggregate functions . An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the  count ,  sum ,  avg  (average),  max  (maximum) and  min  (minimum) over a set of rows. As an example, we can find the highest low-temperature reading anywhere with: SELECT max(temp_lo) FROM weather; max ----- 46 (1 row) If we wanted to know what city (or cities) that reading occurred in, we might try: SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG but this will not work since the aggregate  max  cannot be used in the  WHERE  clause. (This restriction exists because the  WHERE  clause determines which rows will be included in the aggregate calculation; so obviously it has to be evaluated before aggregate functions are computed.) However, as is o...
Read more

Using BigQuery / MySQL / other SQL

Image
  https://azure.microsoft.com/en-us/products/azure-sql/#product-overview https://cloud.google.com/bigquery/docs Using BigQuery BigQuery is a data warehouse on Google Cloud that data analysts can use to query, filter large datasets, aggregate results, and perform complex operations. An upcoming activity is performed in BigQuery. This reading provides instructions to create your own BigQuery account, select public datasets, and upload CSV files. At the end of this reading, you can confirm your access to the BigQuery console before you move on to the activity, Note: Additional getting started resources for a few other SQL database platforms are also provided at the end of this reading if you choose to work with them instead of BigQuery. Types of BigQuery accounts There are two different types of accounts: sandbox and free trial. A sandbox account allows you to practice queries and explore public datasets for free, but has additional restrictions on top of the standard quotas and lim...
Read more

The R-versus-Python debate

Image
  The R-versus-Python debate People often wonder which programming language they should learn first. You might be wondering about this, too. This certificate teaches the open-source programming language, R. R is a great starting point for foundational data analysis, and it has helpful packages that beginners can apply to projects. Python isn’t covered in the curriculum, but we encourage you to explore Python after completing the certificate. If you are curious about other programming languages, make every effort to continue learning. Any language a beginner starts to learn will have some advantages and challenges. Let’s put this into context by looking at R and Python. The following table is a high-level overview based on a sampling of articles and opinions of those in the field. You can review the information without necessarily picking a side in the R vs. Python debate. In fact, if you check out RStudio’s blog article in the Additional resources section, it’s actually more about ...
Read more