Guía práctica para el uso de Google Sheets y Excel, Apuntes de Cálculo

¡Descarga Guía práctica para el uso de Google Sheets y Excel y más Apuntes en PDF de Cálculo solo en Docsity! 1.INTRODUCTION TO SPREADSHEETS 1.INTRODUCTION AND CONCEPT What is a spreadsheet? A spreadsheet is an interactive computer application for organization, analysis and storage of data in tabular form. They are developed as computerized simulations of paper accounting worksheets. The program operates on data entered in cells of a table. Each cell may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells. A spreadsheet may also refer to one such electronic document. Main function: data and information organization and management Main tools and applications: Proprietary (most used): MsExcel (multiple versions) Open: LibreOffice Calc, OpenOffice Calc Online: Google Sheets, Office 365 Main functionalities Information analysis, information display and summary, information comparison (graphics), simple/complex calculations, data connection and task and time management. Examples: falta diaz 2.ON-LINE SPREADSHEETS AND COLLABORATIVE WORKING How to collaborate (team-working)? 1. Send/Receive files: They have the disadvantages of version mismatch and conflict resolution and no real-time updates (collaboration). 2. On-line shared file (Dropbox, Gdrive, Onedrive): They have the disadvantage of no real-time updates (collaboration) and advantage of versioning and conflict resolution. 3. On-line tools: They have the disadvantage that complex operations are usually slower than in local installations and advantages of versioning and conflict resolution, real-time updates, off-line working Independent of local installations, no installation and upload files (off-line). Example to shared sheets Google sheets (GSheets): A Gmail or Google Apps account is required and UC3M accounts are valid. It is free, has unlimited space (UC3M accounts) and no registration or activation is required. It can be accessed from your Gmail account, google sheets and google drive that lead to the same sheet but from different places. 3.SHARING OPTIONS Who has access (link sharing) 1.Corporate account (@uc3m.es): on UC3M (anyone at UC3M), on anyone at UC3M with the link, off-specific people (shared with specific people). 2.Personal account (@gmail.com): on-public on the web, on-anyone with the link, off-specific people. 3.Private: only you can access. Restrictions and configuration -Prevent editors from changing access and and adding new people -Disable options to download, print, and copy for commenters and viewers Type of permissions Begiratu driven Who is editing what? Google Sheets allows a simultaneous edition from more than one user. The user sharing the sheet is identified with a specific color and, if he/she is editing a cell at the same time, this is marked with the same color. Version history GSheets allows to track all the changes done at a sheet, as well as who was the autor, date and time of the change being made. Other ways to share GSheets 1.Version history 2.Download as: Save a copy into your device (using another format) 3.Email as attachment: using another format 4.Publish to the web: it might be seen as a web page (but it does not allow for mofication) 5.Import: Unload a sheet (from a different spreadsheet format) to be converted into GSheet. 2.BOOK, SHEETS, CELLS 1.DEFINITIONS Book: a spreadsheet file that is composed of information organized in sheets. Sheet: A set of data organized in cells (rows and columns). Sheets are organized by rows that are identified by a unique number-ID and columns that are identified by a unique letter-ID. A sheet has a name visible in the bottom. Cell: The intersection between a row and a column where data is introduced. To name a cell a letter (column) and a number (row) are required. “A3” refers to data in column “A” and row ”3” of the current sheet. Borders of active cells are highlighted (in green by default). Book – sheet 1 – cells – sheet 2 – cells Range: It is a set of cells that can be managed according to the following rules. The operator “;” is used to join different ranges. A1: A30 all cells in column A and rows [1-30]. A1: B30 all cells between columns A and B and rows [1-30] A1: A30;D1:D30 all cells in column A between rows [1-30] and all cells in column D between rows [1-30]. A1: A30;D1;C3 all cells in column A between rows [1-30] and both cells D1 and C3 2.CELL REFERENCES 1. Relative references: By default, all cell references are relative references. When copied, across multiple cells, they change based on the relative position of rows and columns. 2. Absolute references: They always remain constant, no matter where they are copied. They are created with the $ sign before the column letters, row numbers or both of them. =$A$1*3: both column and rows remain fixed =A$1*3: only the row remains fixed =$A1*3: only the column remains fixed To insert a new row or column (two options): Click on the right mouse button where the row must be inserted or Home > Cells group > Insert. To delete a new row or column (two options): Click on the right mouse button where the row must be deleted and Home > Cells group > Delete To hide a row or a column: select first the row or column, click on the RMB in its header and select the option “Hide” To show a hidden row or column: select the surrounding rows and columns, click on the RMB and select the option “Unhide”. Define height/width of rows/columns: If we select a row or a column, we have a contextual dropdown menu in which we can define the “Row height” or “Column width” values. Universidad Carlos III de Madrid 42. If we situate the cursor on the intersection of rows and columns, we can directly change these values. Combine cells: It is possible to combine neighboring cells in just one cell. To do so, select the candidate cells, go to the Home tab and select the option “Merge & Center”. Insert comments: To insert a new comment, situate the mouse on the cell, click on the RMB and select the option “Insert”. Edit/reply/delete the comment: Go to the cell that contains the comment, Double click or RMB and select the option 3. WORKING WITH CELLS AND SHEETS DATA TYPES 1.Numbers: When you enter a number into a cell, Excel automatically recognizes it as such and aligns it to the right side of the cell. A number is any characters from 1 to 9, including separators for thousands or decimals. The comma separates thousands, millions, etc. Numbers with a single dot are processed as a decimal numbers. When you enter a number along with the symbol €, Excel assigns a Currency number format to the cell. If you don’t apply a fraction format to a cell before you enter a fraction into it, Excel Will convert the fraction into a date. 2.Text: Strings are representing alphanumeric data (data that contain alphabetic letters together with numbers and/or special characters). Titles, labels, comments and other descriptions. When you enter text into a cell, Excel automatically recognizes it and aligns it to the left side of the cell. All data entries that mix letters (A-Z) and numbers are consideredtext, even when the entry begins with a number. All data entries beggining with equal sign (=) or plus sign (+) areinterpreted as formulas. If you want to enter a number as text in acell, you must place a quotation mark (“) before its first characterand after its last character. 3.Date and hour: Short dates can be separated by using: a slash (/) or an hyphen (-). 06-08 denotes 6 th August 2018 (current year). Although each cell could have a different date format, they all havethe same value. The numeric value of a cell is obtained by changing the cell format toGeneral format. It is obtained by calculating the days elapsed since 1st January 1900• Column B shows the values of column A but applying a General format. When adding or subtracting two dates, Excel calculates the difference in days. For this reason, dates are often considered as numbers. An hour is entered by numbers separated by the symbol: -hh:mm will be interpreted as hour:minute -hh:mm:ss will be interpreted as hour:minute:seconds Dates or times must be valid, if not they will be interpreted as text -31/04/15 will be considered as text, given that the date 31st April does not exist -12:75 will be also considered as text 4.Formulas: Along with the numerical value and text, a cell can also contain a formula. Formula can be defined as a operation that processes numerical values and/or text and returns a result. FILL DATA AUTOMATICALLY IN WORKSHEET CELLS 1.Repeat an existing value in the column If the first characters that are written match an existing input in the column, Excel automatically completes the characters. Only inputs that contain text or combination of text and numbers. It does not complete inputs that are numbers, dates or hours. After completing the cell automatically… accept proposed characters (press Enter). To replace the characters written automatically, keep writing. To delete proposed characters, press Backspace. To activate/deactivate the automatic completion of cell values... File > Options > Advanced > Edit Options. Activate or deactivate the Enable AutoComplete for cell values. 2.Fill data with the fill handle Select the cells that you want to use as a base. Drag the fill handle vertically or horizontally to fill the cells. After dragging the fill handle, the Auto Fill Options displays. Copy Cells: fills in the contents of the original cells, including their format. Fill Series: fills the values in series, it predicts values of series. Fill Formatting Only: fills only the cell formats. Fill Without Formatting: fills the contents of the cell, but without format. Fast Fill: it starts when a specific data pattern is recognized. To activate/deactivate Auto Fill Options ... File > Options > Advanced > Cut, copy, and paste Activate or deactivate Show Paste Options button when content is pasted It allows filling a selected range or active cell with the content of an adjacent cell or range. To do this, select... -An empty cell located just below, to the right, above or to the left of the cell that contains the data -The cell that contains the content and the adjacent cells in which you want to include it Home > Editing > Fill: Down, Right, Up or Left Fastest option: -Ctrl+J fill with the upper cell contents -Ctrl+D fill with the contents of the cell on the left 3.Fill cells with formula Select the cell containing the formula. Drag the fill handle over the cells you want to fill. Select suitable Auto Fill Options 4.Fill cells with a series of numbers, dates or other integrated series elements Fill cells with series with the fill handle. Select the first cell in the range and write the initial value of the serie• Drag the fill handle to fill the cells you want to fill. Fill cells with a series using the Fill command. Select the first cell in the range and enter the initial value of the series Home > Editing > Fill > Series. Type: -Linear: creates series adding a “Step value” to the next cell. Text direction: It defines reading order and alignment. The default setting is Context, but you can change it to Right-toLeft or Left-to-Right. Applied to languages such as Hebrew, Arabic, etc 2. The standard excel ribbon includes command buttons for quick access to the frequently used alignment commands. -vertical alignment (Top Align, Middle Align, or Bottom Align) -horizontal alignment (Align-left, Center or Align-right) -wrap text (row height is adjusted to show all the wrapped text) -orientation (select one of orientation styles in the drop down list of the Orientation button Merge options: To access a couple more merge options, click drop-down arrow next to the Merge & Center button and select. 3.Borders Add a border around cells to emphasize data, mark summarized values, or separate data in the cell range. 1. Home > Font combo box > Format Cells > Border 2. The standard excel ribbon includes a command button for quick access to border commands. Borders button: Adds internal vertical lines and horizontal lines as well as external border lines. To appy a border style, click the arrow next to Borders and select a border style. To apply a customised border style or a diagonal border, click More Borders … 4.Fill You can highlight data in cells by adding or changing the background color. 1. Home > click the arrow of the Font combo box > Format Cells > Fill 2. The standard excel ribbon includes command buttons for quick access to the frequently used fill commands. Fill Color button: If clicked, a selected color is applied to the cell background. The button shows the last color being applied to the cells (yellow in this example). To apply different color, click the arrow next to Fill Color button and then select a color from a drop-down list. Through Fill Color button, it is not possible to apply a pattern or fill effects. For that, click Home > Format Cells > Fill. 5.Predefined cell styles and table formats Apply multiple formats in one step. This tool enables maintaining consistent cell/table formatting throughout the worksheet/book. A cell style is a defined set of formatting characteristics (i.e., font type, font size, number category, cell borders, and cell shading). On the Home > Styles group. Format as Table: Automatically converts your data range to a table. Under Light, Medium, or Dark, select the style. If data range contain headers in the top row, check “My table has headers”. Click New Table Style to create your own table style. Cell styles: A quick way to includeprofessionalformatting for different parts of theworkbook (titles, headers, etc.).Choose the desired stylefrom thedrop-down menú. Cell Style tool will replace anyexisting cell formatting except textalignment. 6.Copy cell formatting To quickly copy formatting from one cell (cell range) to another cell (cell range). Select the cells that have the formatting you want to copy. Home > Format Painter icon (pincel). Select (click) the cells where you want to use the copied format. This is the appearance of the cursor in the worksheet when copying formatting. 7.Format of numerical values Formatting a numerical value only affects the way the number is displayed in the cell, but it does not affect the actual value. 1.Dialog box: Home > Number group, click the dialog box launcher -General: Numbers are displayed just the way they are typed in (default number format) -Number: You can specify the number of decimal places -Currency: Used for general monetary values and displays the default currency symbol with numbers -Accounting: It displays the currency symbols long with the number and its decimals -Date: It displays date and time serial numbers as date values (according to type and location specified) -Time: It displays date and time serial numbers as time values (according to type and location specified) -Percentage: It multiplies the value by 100 and displays the result with a percentage (%) symbol -Fraction: It displays a number as a fraction, according to the type of fraction that you specify -Scientific: It displays a number in exponential notation -Text: It treats the content of a cell as a text and displays it exactly as it is typed in (even the numbers) -Special: It displays a number of postal code, phone number or Security Social number -Custom: It allows you to create a customized number format 2.The standard excel ribbon includes command buttons for quick access to the frequently used number formats. -Assigns Currency format to the cell. -Assigns Percentage format to the cell. -Displays a thousands separator and 2 decimal places. -Increases decimal places. -Decreases decimal places. 8.Conditional formatting On the tab Home > Styles > Conditional Formatting -Highlight cell rules: Highlights cells that are greater or lower than, between two values, etc -Top/Botttom rules: It allows you to apply formatting to cells that satisfy a statistical condition in relation to other cells in the range (e.g. above average, within top 10%, etc.). -New Rule…: You can create a new/custom rule by using a formula to determine which cells to format. Open New Rule > New Formatting Rule window: A value between two numbers / greater than… / lower than… 9.Themes Themes allow you to change the look of the workbook with a click. Each theme consists of 12 colors, two fonts (Headings and Body) and effects for shapes and SmartArt. On the tab Page Layout > Themes. Browse for Themes: To apply a theme that is not listed (you can find it in your computer or a network location). Save Current Theme: To create, customize, and save a theme. Functions Functions are in-built formulas that enable different operations. For example: – =(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/10 (Formula) / =AVERAGE(A1:A10) (Function) Same as formulas, we can introduce functions manually or using the assistant (Function Arguments). The assistant provides additional information about the function parameters/elements. Function is followed by parenthesis in which the argument is included. There are the following possibilities: No argument, An argument with values, An argument with cell references, Other formulas or functions. Types of functions: 1.Mathematical and trigonometric -Sum, product, quotient : SUM(), PRODUCT(), QUOTIENT() -Square root, power: SQRT(),POWER() -ROUND(): returns a number rounded to a given number of digits (decimals). -FLOOR.MATH(): rounds a number down, to the nearest integer or to the nearest multiple of significance. -CEILING.MATH(: )rounds a number up to the nearest integer or to the nearest multiple of significance. -=MAX(): returns the largest value in a given list of arguments. -=MIN( -=AVERAGE(): returns the average (arithmetic mean) of the arguments. -=MODE(): returns the most frequently occruing, or repetitive, value in an array orin a range of data. -=MEDIAN(): returns the median of the given set of numbers. The median is the number in the middle of a set of numbers. -=SUM(): adds up individual values, cell references, or values in a cell range. -=SUMIF() sums up the values in a range that meet specifed criteria. -=SUMIFS() adds up arguments that meet multiple criteria. 2.Text -=CONCATENATE(): joins two or more text strings into one string. -=RIGHT(),LEFT(): extracts a given number of characters from the right/left side of a text string. -=PROPER(): sets the first character in each word to uppercase. -=REPLACE(): replacing text at a known location in a given string. -=EXACT(): compares two strings and returns TRUE if they are exactly the same,FALSE otherwise. EXACT function is case-sensitive but ignores formatting differences. -=DOLLAR(): converts a number to text using currency format, with the decimals rounded as specify. -=TEXT() returns a number in a given number format, as text. It is useful insituations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. -=LEN() returns the number of characters in a text string. -=SEARCH() returns the location of one text string inside another. -=TEXTJOIN () combines the text from multiple ranges and/or strings, and includesa delimiter you specify between each text string that is combined. 3.Logical -=IF() a logical test returns one value for a TRUE result, and another for a FALSE result. So, an IF statement can have two results, True or False. -=AND( )a logical function used to require more than one condition at the sametime. Returns either TRUE or FALSE. -=OR() a logical function to test multiple conditions at the same time. Returns trueif at least one is true. =NOT() a logical function. If we give TRUE, it will return FALSE and when given FALSE, it will return TRUE. So, basically, it returns a reverse logical value. 4.Reference and look up -=VLOOKUP()stands for 'Vertical Lookup’. Look up data in a table organizedvertically. -=HLOOKUP()stands for 'Horizontal Lookup’. Look up data in a table organizedhorizontally. -=MATCH()locate the position of a lookup value in a row, column, or table. 5.Time -=NOW()returns the day and time of now. -=DAY()returns the day of the month as a number between 1 to 31 from a given date. -=DAYS (END DATE – INITIAL DATE) returns the number of days between ENDDATE and INITIAL DATE -=NETWORKDAYS()returns the working days between 2 dates. The cell has to have date format. Data tools Converting text to columns Data > Text to Columns - Delimited > Next - Delimiters: Comma, Space… Flash fill: automatically fills data when a pattern is recognised. Data > Flash Fill icon