Scarica Computer skills: excell e più Appunti in PDF di Fondamenti di informatica solo su Docsity!
COMPUTER SKILLS
>Ribbon = parte rettangolare sopra con pulsanti. Used for shortcuts to excel commands. It is made of: a) app launcher : icona con puntini. Used to access different parts of microsoft b) tabs : used to quickly navigate between options of menus of different groups c) groups : used for related commands. they are separated by a vertical line d) commands : buttons used to make actions. >Sheet = parte grande con colonne e righe. Set of rows and columns. Made of cells identified by unique coordinates:
- column= letter
- row=number >Select Ranges: A1:E (usa i : per il range)
- Name Box : a) enter values in name box
- first top left corner
- then “:”
- then bottom right corner
- Drag to mark : a) select a cell b) left click on the cell and hold c) move mouse over the range to be selected d) let go >Filling:
- select a cell
- click fill icon
- select range by dragging Used for:
a) copying: it puts the same value in the whole range selected b) creating sequences: used to continue the pattern Bisogna avere almeno 2 values diverse (ex. A1=1, A2=2) c) dates: used to fill dates. write at least 2 so it understands what to continue 08/07/ →use this format d) combining words and letters: ex. Hello1, Hello We can also double click to fill: There must be a recognizable pattern: functions We can make excel reuse the same function for the whole column There CANNOT be a blank column or it won’t work There Must be headers(titoli alle colonne) >Moving Cells: a) drag and drop:
- press and hold the left mouse on the border of the selection
- drag and drop it using the hand cursor **b) copy and paste c) cut and paste
Delete Data:**
press “canc” >Add Columns: (same for rows)
- click column
- right click
- click insert column (press delete to delete them, or canc) >Undo: a) press undo button in ribbon(freccia curva a sinistra) b) ctrl+z
Add them with the borders button(finestrella) d) number formats: They can be:
- general= standard
- number
- currency
- time Change format in the number format dropdown(dove è scritto general) We can change decimals : a) increase decimals b) decrease decimals e) grids: We can remove them by pressing on view and then remove grids To access styling we use: a) the ribbon b) formatting menu when clicking on cells >Format Painter: Used to copy formatting from one cell(or range of cells) to others. To use it:
- select cell to copy
- click format painter button(pennello)
- select cell or range It is used for all formattings(color,font…) >Sorting: We can use: a) sort ascending : from smallest to largest b) sort descending : from largest to smallest To do so:
- select range of cells
- use the lens button and decide the sorting. Se ci sono due colonne non ne puoi selezionare solo una sennò fai un casino.
>Filter: Filters are used to sort and hide data. We access the menu with the funnel(imbuto) To use it:
- have a row of headers(prima riga che spiega che sono i dati delle colonne)
- select range of cells(la prima riga coi titoli)
- click the filter command(imbuto) Premi su filter e deseleziona le cose che non vuoi si vedano >Table: We can create tables from ranges. To convert a range into a table we:
- select range of cells
- click “insert” then “table” in the Ribbon
- click OK We can customize tables with the button “table design” There we can:
- resize : increase or decrease the range of the table. To do so: a) select table b) table design button c) resize table button d) drag to resize or type the new range e) add headers
- remove duplicates : a) select table b) click table design c) click remove duplicates command d) click OK
- convert to range
- style options
- conditional formatting : Change the appearance of cells based on some conditions(numeric value or matching text) There are: a) color scale:
A. SUM:
- by adding cells( sommale una alla volta “=A1+A2+A3”)
- sum function: a) E5=SUM b) double click SUM in menu c) mark range (A19:B21) d) hit enter B. AND: Returns TRUE or FALSE based on 2 or more conditions. TRUE or FALSE coming from both conditions being satisfied. Used usually with IF =AND(B2=”Fire”, C2>7)
- logical1,2 are the conditions: a) number > other b) number < other c) number = other C. AVERAGE: calculates the average. =AVERAGE(B2:E2) D. AVERAGEIF( also AVERAGEIFS): calculates average based on TRUE or FALSE condition =AVERAGEIF(B2:B10, E3, C2:C10) E. CONCAT: links content of cells =CONCAT(A2, “”, A3) “” is a delimiter F. COUNT:
counts cells with numbers in a range =COUNT(D2:D21) G. COUNTA: counts all cells in a range having values(numbers and letters) =COUNTA(D2:D21) H. COUNTBLANK: counts blank cells in a range =COUNTBLANK(D2:D21) I. COUNTIF: counts cells as specified(numbers and letters) =COUNTIF(D2:D21, F5) →F5=criteria J. COUNTIFS: counts cells in a range based on one or more TRUE or FALSE conditions. =COUNTIFS(D2:D21, F5, C2:C21, F2) →F5=criteria 1 →F2=criteria 2 →D2:D21= range 1 →C2:C21=range 2 K. IF: Returns values based on TRUE or FALSE conditions. Used with AND, XOR and OR. =IF(B2=”Grass”, “Yes”, “No”) →B2=grass= logical test →Yes=value if true →No=value if false L. IFS: returns values based on one or more TRUE or FALSE conditions =IFS(C2>90, “Fast”, C2<=50, “Slow”) →C2>90=logical test 1
returns TRUE or FALSE based on two or more conditions. It is true if at least one condition is true. It is false if all conditions are false. =OR(B2=”Water”, C2>60) →=condition 1 →=condition 2 U. RAND: generates a random number There are different ways:
- =RAND() →default
- =RAND()*10 →to get a number up to a value(10)
- =INT(RAND()) →to get a whole number V. RIGHT: returns values from right of the cell =RIGHT(B2) or =RIGHT(B2:B21) W. STDEV.P: calculates the standard deviation for the population =STDEV.P(B2:B21) X. STDEV.S: calculates the standard deviation for the sample =STDEV.S(B2:B21) Y. SUMIF (also SUMIFS ) : calculates the sum of values based on one or more TRUE or FALSE conditions. =SUMIF(B2:B10, E3, C2:C10) →B2:B10=range →E3=criteria →C2:C10=sum-range Z. TRIM: removes irregular spacing =TRIM(C1) or =TRIM(C1:C10)
AA. VLOOKUP:
allows vertical searches for values =VLOOKUP(H3, B1:B21, 2, 1) →H3=lookup value →B1:B21=table-erray= table range →2=col-index-num →1=range lookup →True if it’s a numer(1), False if it’s text(0) BB. XOR: returns TRUE or FALSE based on two or more conditions =XOR(B2=”Fire”, C2>60) It is true if ONLY one of the conditions is true. >PIVOT TABLE: Helps organize data by removing values, performing calculations, filtering and sorting data sets. It is made of: a) columns with a header b) rows with related data c) filters to select data to be seen d) values There is the PivotTable Fields panel to change how we see the data Settings are divided into: a) fields: checkboxes that can be selected b) layout: drag fields into:
- filters
- rows
- columns
- values To create one we must have:
- no blank rows
- no blank columns
- headers
