Difference between revisions of "LibreOffice Calc basics"
Jump to navigation
Jump to search
Gurumurthy (talk | contribs) |
Gurumurthy (talk | contribs) |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | #A spreadsheet is a software application used for computations and data processing eg LibreOffice Calc, OpenOffice Calc | + | __FORCETOC__ |
| − | # | + | =Introduction= |
| − | #A cell is a combination of a row and a column which is referred to by alphabet+number eg A1, N100 | + | #A spreadsheet is a software application used for computations and data processing eg LibreOffice Calc, OpenOffice Calc. It is a very powerful and versatile software which is a 'number editor' like LibreOffice Writer is a 'text editor'. In addition spreadsheet can also do many text related functions also |
| − | # | + | #A spreadsheet is made of rows and columns which are referred to by numbers and alphabets |
| + | #A cell is a combination of a row and a column which is referred to by alphabet+number eg A1, N100. You can visualise a spreadsheet as a table, with rows and columnns. Data can be entered into a spreadsheet in this tabular manner, with each record entered in one row and each element entered in one column for each row/record. Thus student register can be entered in a spreadsheet, with one student record per row and all data elements relating to the student, entered in different columns, such as name, class, mother tongue, sex, religion etc | ||
| + | =Basic features= | ||
| + | #Edit features | ||
##Insert row/column (INSERT- ROW, INSERT -COLUMN) | ##Insert row/column (INSERT- ROW, INSERT -COLUMN) | ||
##Delete row/column (EDIT- DELETE CELLS-DELETE ROW/COLUMN) | ##Delete row/column (EDIT- DELETE CELLS-DELETE ROW/COLUMN) | ||
| Line 8: | Line 11: | ||
##Show row/column (FORMAT -ROW/COLUMN -SHOW) | ##Show row/column (FORMAT -ROW/COLUMN -SHOW) | ||
##Others - multiple sheets (One_cluster, one_block, Mix), workbook, naming book and sheet, | ##Others - multiple sheets (One_cluster, one_block, Mix), workbook, naming book and sheet, | ||
| + | =Format features= | ||
##Basic column width, row height etc | ##Basic column width, row height etc | ||
##Freeze panes (column headings) WINDOW - FREEZE | ##Freeze panes (column headings) WINDOW - FREEZE | ||
| Line 14: | Line 18: | ||
##If column headings are not seen, use TAB <Options> and tick <Range contains column labels> | ##If column headings are not seen, use TAB <Options> and tick <Range contains column labels> | ||
##Use Edit- select All (or control A) to select sheet before DATA-SORT | ##Use Edit- select All (or control A) to select sheet before DATA-SORT | ||
| − | ##e.g. sort DISE data by DISTRICTNAME-BLOCKNAME-CLUSTERCD or RURAL-GIRLSTOILET | + | ##e.g. sort DISE data by DISTRICTNAME-BLOCKNAME-CLUSTERCD or RURAL-GIRLSTOILET or sort School Admission Register data by Sex, Mother tongue |
#FILTER | #FILTER | ||
##Used to filter some rows based on selection | ##Used to filter some rows based on selection | ||
| Line 20: | Line 24: | ||
##Do DATA-FILTER-AUTO FILTER to create a filter (you can see Arrow sign on each column) | ##Do DATA-FILTER-AUTO FILTER to create a filter (you can see Arrow sign on each column) | ||
##Click on the arrow on any column to select filter | ##Click on the arrow on any column to select filter | ||
| + | ## Filter School Admission Register data on APL/BPL | ||
#FORMULA | #FORMULA | ||
##Addition, (total boys, total girls in HPS =AE2+AG2+AI2+AK2) | ##Addition, (total boys, total girls in HPS =AE2+AG2+AI2+AK2) | ||
| Line 27: | Line 32: | ||
##Use formula for other cases – subtraction, multiplication, division, percentages | ##Use formula for other cases – subtraction, multiplication, division, percentages | ||
##Use formula to connect across work sheets and across work books | ##Use formula to connect across work sheets and across work books | ||
| − | ##If then conditional formula (calculate student grades from marks, [http:// | + | ##If then conditional formula (calculate student grades from marks, [http://karnatakaeducation.org.in/KOER/en/images/b/b7/Using_spreadsheet_to_calculate_grades_June_2015.ods download student grade computation.ods]. |
##Can use statistical functions – average, product, Frequency, Countif etc | ##Can use statistical functions – average, product, Frequency, Countif etc | ||
#Other features | #Other features | ||
| Line 40: | Line 45: | ||
##Header and Footer information | ##Header and Footer information | ||
#Data Pilot | #Data Pilot | ||
| − | ##Open the spreadsheet | + | ##Open the spreadsheet [http://karnatakaeducation.org.in/KOER/en/images/8/8c/Yadgiri_Primary_School_DISE_2010_Information_for_Decision_Support.ods Yadgir Primary School DISE 2010 Information for Decision Support.ods] |
##Select the data – through Control Home (to go to top), then Control Shift Down Arrow and Control Shift Right Arrow | ##Select the data – through Control Home (to go to top), then Control Shift Down Arrow and Control Shift Right Arrow | ||
##Data - Data Pilot - Start - Current Selection | ##Data - Data Pilot - Start - Current Selection | ||
Latest revision as of 20:22, 20 December 2015
Introduction
- A spreadsheet is a software application used for computations and data processing eg LibreOffice Calc, OpenOffice Calc. It is a very powerful and versatile software which is a 'number editor' like LibreOffice Writer is a 'text editor'. In addition spreadsheet can also do many text related functions also
- A spreadsheet is made of rows and columns which are referred to by numbers and alphabets
- A cell is a combination of a row and a column which is referred to by alphabet+number eg A1, N100. You can visualise a spreadsheet as a table, with rows and columnns. Data can be entered into a spreadsheet in this tabular manner, with each record entered in one row and each element entered in one column for each row/record. Thus student register can be entered in a spreadsheet, with one student record per row and all data elements relating to the student, entered in different columns, such as name, class, mother tongue, sex, religion etc
Basic features
- Edit features
- Insert row/column (INSERT- ROW, INSERT -COLUMN)
- Delete row/column (EDIT- DELETE CELLS-DELETE ROW/COLUMN)
- Hide row/column (FORMAT -ROW/COLUMN -HIDE)
- Show row/column (FORMAT -ROW/COLUMN -SHOW)
- Others - multiple sheets (One_cluster, one_block, Mix), workbook, naming book and sheet,
Format features
- Basic column width, row height etc
- Freeze panes (column headings) WINDOW - FREEZE
- Important commands – SORT
- Sorting – is used to sort the data by a particular column or set of columns (DATA-SORT)
- If column headings are not seen, use TAB <Options> and tick <Range contains column labels>
- Use Edit- select All (or control A) to select sheet before DATA-SORT
- e.g. sort DISE data by DISTRICTNAME-BLOCKNAME-CLUSTERCD or RURAL-GIRLSTOILET or sort School Admission Register data by Sex, Mother tongue
- FILTER
- Used to filter some rows based on selection
- Use Edit- select All (or control A) to select sheet before DATA-SORT
- Do DATA-FILTER-AUTO FILTER to create a filter (you can see Arrow sign on each column)
- Click on the arrow on any column to select filter
- Filter School Admission Register data on APL/BPL
- FORMULA
- Addition, (total boys, total girls in HPS =AE2+AG2+AI2+AK2)
- Copy formula across rows by double clicking bottom right part when you get + sign
- Sum of TOTENR for the cluster (use sigmal sign, or formula =sum(AK2:AK12)
- Division ( Teacher pupil ration = Total children / Teachers posted 'P' =AL2/Jj2)
- Use formula for other cases – subtraction, multiplication, division, percentages
- Use formula to connect across work sheets and across work books
- If then conditional formula (calculate student grades from marks, download student grade computation.ods.
- Can use statistical functions – average, product, Frequency, Countif etc
- Other features
- List of values (validation for data entry)
- Algebra – plotting linear equations in single and two variables
- Creating graphs from tabular data
- Copy paste from ODS to ODT (table)
- Paste special – formatted text, transpose cells to convert rows to columns and vice versa
- Formatting spreadsheets
- Print rage definition for spreadsheets to repeat row headings in printouts
- Text alignment (word wrap) to control column width
- Header and Footer information
- Data Pilot
- Open the spreadsheet Yadgir Primary School DISE 2010 Information for Decision Support.ods
- Select the data – through Control Home (to go to top), then Control Shift Down Arrow and Control Shift Right Arrow
- Data - Data Pilot - Start - Current Selection
- You will see the Data Pilot Form. It has four boxes -
- Move BLKNAME to Row Fields
- Move SCHME_DESC to Column Fields
- Move SCHNAME to Data field
- Double click on SCHNAME in the Data field and select Function COUNT (can also do this through Options button)
- Move LIB_RARY to Page Fields
- Click on OK. The Data Pilot is the most advanced feature in spreadsheet software application. It can be used to analyze data in multiple ways
- Practice using spreadsheet
- Use the spreadsheet to create data pilot
- Insert a column “Total Boys” and use formula to add the number of girls class 1,2,3,4,5,6,7,8. Hide the columns containing number of boys so that the ##Total girls is seen after the columns with number of girls
- Calculate the number (count) of schools which are in rural areas and which do not have girls toilet facility
- Number of schools with private management
- Number of children (sum of girls and boys)