How to reduce a table in an open office. Change column width and row height
There are a number of restrictions on the database structure:
- the first row of the database must contain non-duplicate field names and be located on one line;
- for field names, use a font, data type, format, frame, different from those used for data in records;
- the table should be separated from other worksheet data by an empty column and an empty row;
- the information on the fields must be homogeneous, i.e. only numbers or only text.
Working with any database consists in searching for information according to certain criteria, regrouping and processing information.
Sorting data in the table is carried out through the command Data\u003e Sort or buttons on the toolbar Standard Sort Ascending and Sort descending (fig. 44).
For example, sort the table containing the last name and gender so that all female names appear alphabetically first, and then male ones.
Let's select a table with data. Data\u003e Sort... In the window Sort by choose floor(this is the table header), in the window Then by choose surname(fig. 45). We get fig. 46.
Using the command Data\u003e Filter\u003e AutoFilter selection of data by criterion is carried out. At the same time, buttons (arrow) appear in the cells where the headers are located. When you click on them, a menu appears with the conditions for selecting an autofilter (fig. 47).
After working with the selected list, you need to return the entire data column for further work with it.
When running the command Data\u003e Filter\u003e Standard Filter it will be possible to select lines that meet the specified conditions (Fig. 48).
Preparing a document for printing
The created document can be printed as a sheet, as well as the entire book, as well as a selected fragment of a sheet and a print area (fixed, in a special way, a fragment of a sheet). If a printable area is specified, only that area will be printed. When you save the document, the printable area is also saved. To determine the printable area, select a fragment of the sheet (using one of the methods for selecting cells), then Format\u003e Print Ranges\u003e Define... Thus, the selection was transformed into a printable area. To change this area, select Format\u003e Print Ranges\u003e Add Region print either Format\u003e Print Ranges\u003e Change... In the dialog box that appears, change the area as follows:
- In field Print rangeclick on the (Minimize) icon to reduce the dialog box to the size of the input field. After that, it is easier to mark the desired link on the sheet. Then the icon automatically turns into an icon (Expand), click it to restore the original dimensions of the dialog box. The dialog box will automatically collapse when you click with the mouse inside the sheet. When you release the mouse button, the dialog box will be restored and the reference range defined with the mouse will be highlighted in a blue frame in the document.
- Line repeat... Duplicate lines are defined with the mouse, if the cursor is in the field Line repeat dialog box. Select one or more lines for printing. The list then displays the value "user-defined". Select "no" in the list to cancel the set repeat lines.
- Column repeat... Select one or more columns to print on each page, and a column link, such as "$ A" or "$ C: $ E", appears in the right text box. The list displays the value user-defined... Choose from the list "no"to cancel the set column repeat.
If the print area is not needed, we delete it in the same way.
To print, you must also adjust the page settings, that is, paper size, paper orientation, headers and footers, print direction, etc. Let's do it through the menu item Format\u003e Page and on the tabs of the dialog box, the page is configured (Fig. 49, 50, 51). The preview lets you see how your document will look on paper.
To view the page area, use the menu item View\u003e Page Layout (fig. 52). By moving the borders (blue) of the page, we change the area of \u200b\u200bthe page for printing by changing the scale, which we will see through Format\u003e Page\u003e Sheet\u003e Scale.
Leave your comment!
Create, edit and design a table
1. Purpose of work
Learn to create, edit and design tables. A table is data arranged in rows and columns (columns). In a table, you can use a border, fill, change the width of columns and height of rows, sort data and other actions.
Working with text within a cell is no different from working with a paragraph.
2. Creating a table
The table can be created using the commands of the main menu: Insert → Table or Table → Paste → Table or using the button Table on the toolbar (Attention! You need to click on the icon itself, and not next to the drop-down list (small green arrow)). In the dialog box that appears "Insert Table" you need to set the following table parameters: number of rows, number of columns.
Fig. 1: Inserting a table
After inserting the table, an additional toolbar will appear "Tables":
Fig. 2: Toolbar "Tables"
If in the process of work you accidentally close this panel, then you can return it through the main menu View → Toolbars → "Table" (left-clicking on the toolbar name will open / close the selected panel).
3. Moving around the table
You can move around the table using the mouse, the Tab key, or the cursor keys.
|
Task number 2: Make sure you can go to any cell in the table and move through the cells using the Tab key and the arrow keys. Fill in the appropriate cells:
|
4. Selection of table elements
Methods for selecting table elements using the mouse are shown in the table:
| What stands out |
How |
|---|---|
|
Table row |
Move the cursor to the beginning of the line you are interested in, the pointer should take the form and help information “select a table row” appears, left-clicking will lead to the selection of this line. |
|
Table column |
In the same way, you need to move the mouse cursor to the upper border of the table (in the column selection area), where the pointer takes the form, after which clicking with the mouse will lead to the selection of this column |
Selection is also possible using the command: Main menu: Table → Highlight - the row or column is highlighted where the text input cursor, cell or the entire table is located.
5. Inserting rows
A row is inserted using the "Insert row" button on the toolbar Table : a new line is inserted below the current line (the current line is the one in the cell that the cursor is currently on).
If you need to insert several lines, then you need to select as many lines as you want to insert and then click the "Insert line" button. The required number of lines will be inserted below the selected lines.
An arbitrary number of lines can be inserted through the main menu: Table → Paste → Strings ... In the dialog box that appears, you need to specify how many lines should be added and their position - before or after the current line.
Figure 3: Insert Rows Dialog Box
To insert to the end of the table of one row it is necessary to place the insertion cursor in the last cell of the last line and press the Tab key;
|
Task number 3: 1. Add a line to the end of the table using the Tab key.
|
6. Inserting columns
Columns can be inserted in the same way or by using the corresponding button "Insert column" on the Table toolbar or through the main menu Table → Insert → Columns ...
|
Task number 4: 1. Add a column using the "Insert Column" button on the Table toolbar
|
7. Inserting text before the table
Place the cursor in the upper left cell of the first row of the table and press the Enter key.
8. Deleting rows, columns
To delete rows or columns, you must:
- select the required elements of the table (rows or columns);
- execute the "Delete ..." command of the "Table" menu item or using the corresponding buttons on the Table toolbar
Attention! The "Delete" and "Backspace" keys only delete the contents of the cells!
9. Table decoration
You can design the table using the dialog box "Table decoration" ... This window opens either using the main menu: Table → Table properties ... either through the context menu, the "Table" command, or using the "table properties" button on the "Table" toolbar
Dialog window "Table decoration" contains several tabs:
- tab "Framing" serves to define the properties of table lines;
- tab "Background" defines the fill, background color of rows, columns, cells or the entire table;
Let's see how to change the type and color of table lines using the tab "Framing":
Figure 4: Dialog Box "Table Decoration: Border"
To change the line style you need: in the "line" group in the list "style" set the required line thickness and select the desired color (clicking on the triangle will open the list of colors in which you can select the color you are interested in). The set line style can be seen immediately on the small layout in the section "User-defined" .
By clicking on the corresponding icons in the section "Predetermined" you can choose which lines will be drawn with the set style.
The predefined section contains the following icons:
In the dialog box "Table decoration" on the layout "User-defined" you can add or remove a line with a mouse click. Please note that one mouse click can result in:
- Line color gray - will keep the previous line style
- Line color white - the line will be deleted.
You can also change the style of table lines (cells) using the corresponding buttons on the "Tables" toolbar
Procedure:
- Select cells of interest
- Click on the "frame" button and select the frame layout
- Use the "line style" button to set the line style
- Button "frame line color" - line color
- The "background color" button sets the background color of the selected cells
10. Changing the column width and row height
For width changes column it is necessary:
- set the mouse pointer to the dividing line of the columns to be changed, the cursor will take the form of a horizontal double arrow and a tooltip "set up a table column" will appear
- holding the left mouse button, drag the dividing line in the required direction until the required column width is obtained.
For altitude changes strings it is necessary:
- set the mouse pointer on the dividing line of the rows to be changed; the cursor will take the form of a vertical double arrow and a tooltip will appear "adjust table row"
- holding the left mouse button, drag the dividing line to the desired side until the required line height is obtained.
This operation changes the width of two adjacent columns or the height of two adjacent rows.
11. Merging cells
To combine cells you need:
- or select all the cells to be merged and execute the menu command "Table" / "Merge cells";
- or select all the cells to be merged and use the button of the same name on the “tables” toolbar;
12. Splitting cells
To separate cells you need:
- select shared cells;
- execute the "Table" / "Split cells" menu command or use the button of the same name on the "tables" toolbar. In the dialog box that appears, set the number of cells into which each of the selected cells is supposed to be split and indicate the direction of splitting vertically or horizontally. This operation can be performed using the "Split Cells" button in the "Tables" panel.
Spreadsheets is an interactive application that stores and processes data in rectangular tables.
Spreadsheets allow you to process large amounts of numeric data. Unlike spreadsheets on paper, spreadsheets provide dynamic calculations; recalculation by formulas when new numbers are introduced. In mathematics, using spreadsheets, you can represent a function in numerical form and plot its graph, in physics you can process the results of laboratory work, in geography or history, you can present statistical data in the form of a diagram.
Spreadsheets are also called table processors. One of the most popular spreadsheet processors is the OpenOffice.org Calc program (hereinafter simply Calc), which is part of the OpenOffice suite.
Relative, absolute and mixed links
References to cell addresses can be used in formulas. There are two main types of links: relative and absolute. Differences between relative and absolute references appear when you copy a formula from an active cell to other cells.
When you move or copy a formula from the active cell, relative references automatically change based on the position of the cell where you copied the formula. Moving a cell position one row in the formula changes the row numbers by one, and moving one column moves the column names by one letter.
Absolute references in formulas are used to specify a fixed cell address. Absolute references do not change when you move or copy a formula. In absolute references, the unchangeable value of the cell address is preceded by a $ sign (for example, $ A $ 1).
Calc also allows you to reference other sheets and other books (external links) in formulas, so in general terms, the cell name looks like this:
‘book_name '# sheet_name.cell_name.
Built-in Calc functions
Cal c provides users with many special functions that can be used in calculations. The function performs certain operations. The original data is passed to it through arguments. A function is called by specifying its name, followed by parentheses. If the function has arguments, they are listed in parentheses and separated from each other by semicolons. For instance:
SUM (A6: A16; A21: A24).
The argument can be a numeric or text value, a cell address (absolute or relative), an address, or a range name. Functions from formulas are called, and in one formula there can be several functions, united by different signs of operations. If other functions are used as arguments to a function, then such functions are called nested functions.
You can enter a formula using the keyboard, either directly in a cell or in the formula bar. In this case, the addresses of the cells, the contents of which should be used as arguments to the function, are convenient to determine by selecting cells with the mouse. The function arguments can be the addresses of both contiguous and non-contiguous ranges of cells. A contiguous range address consists of the first and last cell addresses, separated by colons, and a non-contiguous range address consists of non-contiguous cell addresses, separated by semicolons. Both delimiters are inserted into the formula automatically when you specify a range of cells with the mouse.
To avoid syntax errors when entering formulas, it is advisable to use Function wizard - step-by-step instructions that facilitate this task. In this case, the function is entered in two stages. By command Insert - Function ... (or by clicking the button next to the formula bar) a dialog box appears Function Wizards , in which you need to select the function required to perform the calculations. In this case, the window displays the name of the function, its description and arguments.
After pressing the button OK of this dialog box, the following window appears Function Wizards , where you can see a description of each apr-statement, the current result of the function and the entire formula. Help for the function also remains available during this step. Function entry ends by pressing the button OK.
When using references as arguments, you must take into account the possibility of further copying the created formula. To do this, write them down in the fields of the dialog box in absolute or relative form.
Let's take a look at the most commonly used functions. Knowing these features and using the Calc help will help you solve practical problems.
Math functions
Trigonometric functions occupy a significant place in this category. These include direct and inverse trigonometric and hyperbolic functions. To evaluate these functions, you only need to enter one argument, a number. For functions SIN (number), COS (number) and TAN (number) the number argument is the angle, in radians, at which the function value is determined. If the angle is in degrees, it should be converted to radians by multiplying it by PI ()/180 or using the function RADIANS .
One of the most popular features is SUM ... The syntax for this function is as follows:
SUM (number1; number2;...)
The SUM function requires at least one argument, which is a set of numbers from a range of cells or an array of constants that you want to sum. For example, the formula
=SUM (OT: B10; Sheet2.VZ: B10; SheetZ.VZ: B10)
sums up the values \u200b\u200bthat are in the range of cells OT: B10 worksheets Sheet1, Sheet2 and Sheet3 of one workbook, returning the total to the cell of sheet1, where the formula is located.
About a quarter of the functions included in this category handle arrays of data - vectors and matrices. Let's consider some of them.
SUMIF (range; criterion; sum_range).
Range - a range of cells containing a specific feature.
Criterion - a condition written in the form of a number, expression or text that defines the requirements for the value of a feature.
Sum_range - a range of cells, the data values \u200b\u200bin which are summed up, if the attribute of these cells meets the condition.
Using this function, you can calculate the sum of the values \u200b\u200bwritten in cells from " sum_range"if the values \u200b\u200bin their corresponding cells" range"satisfy" criterion". If a " sum_range"omitted, then the values \u200b\u200bof the cells in" range".
To solve this problem, the formula is entered in cell B 6 = SUMIF (A 2: A 5;">160000"; B 2: B 5)
In some cases, the functions may be useful MOD and INT .
The result of applying the first of them is the remainder of the division of the argument number on argument divider... The syntax for this function is:
MOD (number; divisor),
where number - the number, the remainder of which is determined,
divider - the number to be divided by (divisor).
Function INT rounds a number to the nearest integer. Its syntax:
INT (number),
where number is a real number rounded to the nearest integer.
To solve this problem, the formula is entered in cell A 3 = INT (A1 / 60),and in cell C 3 = MOD (A 1;60).
Statistical functions
This category includes a large number of statistical functions from the most simple ( AVERAGE , MAX andMIN ) to functions that are mainly used only by specialists in the field ( CHITEST , POISSON , PERSENTILE and etc.). In addition to special statistical functions, Cal c provides a set of counting functions that allow you to calculate the number of cells that contain any values, nonblank cells (containing any type of information), or only those cells that contain values \u200b\u200bthat meet specified criteria.
Functions AVERAGE (to find the average), MAX (to find the largest value) and MIN (for finding the smallest value) have syntax similar to that of a function SUM ... For example, the function AVERAGE uses the following arguments:
= AVERAGE (number1; number2; ...)
Example.It is necessary to calculate the minimum, maximum and average cost of goods.
To solve this problem in cells C 5, C 6, and C 7, respectively, the formulas are introduced = MIN (B2: B4), \u003dMAX (B2: B4)and = AVERAGE (B2: B4).
Logic functions
Of all logical functions, functions are most often used AND , OR and IF ... This is explained by the fact that they make it possible to organize branching in the process of solving problems, i.e., to implement a choice of several computation options, and the functions AND and OR serve to combine conditions.
The result of the function AND will be the value TRUE (true) if all arguments are valid TRUE . If at least one of the arguments is valid FALSE (false), the result is the value FALSE ... The syntax for this function is as follows:
AND (boolean1; boolean2; ...),
where boolean1, boolean2, ... - these are from one to thirty checked conditions, each of which can have a value either TRUE , or FALSE .
Function OR has a similar syntax and will result in the value TRUE if at least one argument has a value TRUE . If all arguments are valid FALSE , the result is the value FALSE .
Function syntax IF :
IF (boolean_test; value_if_true; value_if_false),
where log_expression is any value or expression that can be TRUE or FALSE,
value_if_true is the value that will be written to the calculated cell if log_expression true. This value can be a formula;
value_if_false is the value that will be written to the calculated cell if boolean_expression is false. This value can be a formula.
Example.A request has been made to the employment office, which maintains lists of those wishing to get a job. Employer's requirements - higher education, age not more than 35 years.
It is necessary to determine who can be a candidate.
To solve this problem, the formula is introduced into cell E 2 = IF (AND (C2 \u003d "in"; D2<=35);"Да";"Нет")
test questions
1. In which case should you use relative and in which absolute links?
4. Name the math functions Calc.
5. How are the values \u200b\u200bof a range of cells summed?
6.What is the syntax of the function IF ?
The task
1.Create a payroll sheet for laboratory employees taking into account monthly indexation of 5%, district coefficient of 20% and income tax of 13%.
The allowance "For the regional coefficient" is calculated by the formula:
"accrued" * "regional coefficient".
Income tax is calculated using the formula:
("accrued" + "for the regional coefficient") * "income tax".
The amount of wages in each subsequent month is determined by the formula:
"accrued in January" + "accrued in January" * "indexation coefficient" * ("month number" - 1).
2. Fill in the columns "For the regional coefficient", "Tax", "Total to issue" using the copy operation. Similarly, fill in the "Employee surname" column for each month.
3.Place the indexation and income tax coefficients in separate cells of the table and use their absolute addresses when entering formulas.
4. Change the values \u200b\u200bof the indexation coefficient (10%) and income tax (10%) and analyze the results.
5. Calculate the average salary for February.
6.Using function IF in the additional column print the word “Highly paid” for employees with a salary above average.
Preparing for the exam
When working with a spreadsheet, the formula is written in cell B1: \u003d $C $4+ E 2.
How will the formula look after cell B1 is copied to cell D4?
1) =$ C$4+ G5
2) =$ C$4+ E4
3) =$ C$7+ G5
4) =$ E$4+ E4
Decision
When we copy the formula to some other cell, Calc changes the addressing in the formula towards copying. In our case, copying occurs to the right by two positions (from column B to column D) and down by three positions (from row 1 to row 4), therefore the numbers of rows and columns should change, and without absolute addressing it should have turned out \u003d E 7 + G 5. But the first address in the formula ($ C $ 4) is absolute, therefore, it will not change when copying, which means that the formula after copying will take the form: \u003d $ C $ 4 + G 5. Thus, the correct answer is # 1.
Data visualization
Program Calc allows you to visualize the data placed on the worksheet in the form of a chart. Charts visualize relationships between data, making it easier to read and helping you analyze and compare data. Charts can be of various types and present data in different forms. It's important to choose the right type of chart for each dataset.
Chart types
Bar charts and histogramsare used for visual comparison of values. In bar charts, the width of the bar (and in bar charts, its height) is proportional to the value of the quantity. Bar charts and bar charts can be flat or 3-D.
Example.Suppose we have sales data from four regional sales organizations.
To compare the indicators of departments with each other, you can build a histogram.
Type diagrams lineare used to build graphs of functions and display changes in values \u200b\u200bover time. Markers display the values \u200b\u200bof a numerical value, which are connected by smooth lines.
Example.To compare the dynamics of changes in sales volumes using the table from the previous example, we will draw a line.
Pie chart is used to display the values \u200b\u200bof parts of a whole. The size of the pie sector of the chart is proportional to the value of the part. Pie charts can be flat or 3-D, and the sectors can be moved apart (sliced \u200b\u200bpie chart).
Example... To clearly see the ratio of sales in the regions in January, let's build a pie chart.
Area charts actually represent the same information as the lines. However, they show the sum of the entered values \u200b\u200band display the contribution of individual rows to the total. The main advantage of these diagrams is that they allow you to see the ratio of individual components to the total volume.
Example... Let's build an area-type chart based on sales data in regions.
The top line represents the total sales by month. Each bar in this chart is a fraction of the total that is per region. This chart allows you to trace, for example, the trend of increasing sales in general for all four regions.
Areas are more descriptive than lines because they largely represent the whole picture rather than individual changes. These charts accomplish two goals: They visually represent the dependencies between data series and indicate their exact values. These diagrams are useful in cases where you need to draw the attention of the recipient of information to consumption or volume of sales, production, etc.
There are other types of charts.
Building diagrams
When creating a chart, first of all, you need to select the range of cells containing the source data for its construction. Charts are linked to the original data in the worksheet and are refreshed when the data in the worksheet is refreshed. The highlighted range of source data includes data series and categories.
Data series is a set of values \u200b\u200bthat need to be displayed on a chart. On a bar chart, the values \u200b\u200bof a data series are displayed using columns, On a pie chart, using sectors, and on a line, points with specified Y coordinates.
Categories set the position of the data series values \u200b\u200bin the chart. In a bar chart, categories are the “labels” under the columns, in a pie chart, they are the names of the sectors, and on the line, categories are used to indicate tick marks on the X-axis. If the chart displays the change in value over time, then the categories are always time intervals: days, months, years etc.
Charts can be located both on separate sheets and on a sheet with data (embedded charts). The chart area, in addition to the required chart construction area, can contain the titles of the category axis and the value axis, the chart title and the legend.
Chart plot area is the main object in the chart area, since it is there that the graphical display of data is performed. In bar charts, you can change the color of bars, in pie charts, the color of the sectors, and in lines, the shape, size, and color of markers and their connecting lines.
Bar charts and lines containcategory axis (axisX ) andvalue axis (axisHave ), the format of which can be changed (thickness, type and color of lines). The most important parameter of the axes isscale , which determines the minimum and maximum values \u200b\u200bof the axis, as well as the price of major and intermediate divisions. The names of categories are placed next to the scale divisions on the category axis, and the values \u200b\u200bof the data series are placed next to the scale divisions along the value axis. In pie charts, category names and data series values \u200b\u200bappear next to the slices of the chart.
You can use horizontal and vertical gridlines to more accurately define the size of the bars in a bar chart and the positions of the line markers. Major grid lines continue with major scale divisions, and intermediate lines continue with intermediate scale divisions.
Chart title and axis names you can move and resize them, and you can also change the font type, size and color.
Legend contains the names of categories and shows the color of the columns used to display them in bar charts, the color of sectors - in pie charts, the shape and color of markers and lines - on lines. You can move and resize the legend, and change the type of font used, its size, and color.
To build diagrams, it is advisable to use it Chart Wizard ... To do this, select the command Insert - Diagram ...and a dialog box will appear.
At this stage, you can choose the type of chart that best suits the purposes of your analysis.
The next step is to select or refine the range of data used to build the chart.
In the third step, you can add or remove data series and set up data ranges for each series and categories.
At the final, fourth stage, you can select additional objects to include in the diagram and set some of their characteristics and the content of the labels.
If the properties of the objects included in the diagram, which is built in this way, does not suit the user, then it should be changed. To do this, by double-clicking the left mouse button on the diagram, switch to the edit mode. Then, right-clicking on the desired object (legend, plot area, chart title, etc.), select the command from the context menu Object properties ....
test questions
1. What types of charts do you know?
2. When is it better to use a bar chart, which is a pie chart, and which is a line?
3. Describe the chart objects - categories, data series, legend.
The task
1. Create a new book.
2. Save it in a personal folder under the name Diagrams.
3. Review the Chart Reference.
4. On Sheet 1, create histograms according to the table data, using as data series: a) columns; b) strings.
7. Save your changes to the file.
8. Using the same data, draw a line using columns as data series.
9. Using the data in the table, create a pie chart showing the share of each cycle of disciplines in the total amount of time for theoretical studies.
10. Give Sheet 1 a Chart title.
11. Save your changes to the file.
Preparing for the exam
From the demo version of the Unified State Exam-2008.
A fragment of a spreadsheet is given
After performing the calculations, a diagram was built using the values \u200b\u200bof the range of cells A 2: D 2. Indicate the resulting diagram.
Decision
First, you need to calculate the values \u200b\u200bof the cells from the interval A 2: D 2 using the formulas given:
A2 \u003d C1 - B1 \u003d 4 - 3 \u003d 1
B2 \u003d B1 - A2 * 2 \u003d 3 - 1 * 2 \u003d 1
C2 \u003d C1 / 2 \u003d 4/2 \u003d 2
D2 \u003d B1 + B2 \u003d 3 + 1 \u003d 4
Now we check the diagrams for compliance.
In the first diagram, the values \u200b\u200bare: 1, 1, 2, 3 - does not fit.
In the second: 1, 1, 3, 3 - does not fit.
In the third: 2, 2, 1, 1 - does not fit.
In the fourth: 1, 1, 2, 4 - fits.
Thus, the correct answer is # 4.
Laboratory work No. 13
Create spreadsheetsin OpenOffice.org Calc
Hardware: PC
Software:Windows, OpenOffice.org Calc.
Objective: acquisition and consolidation of practical skills in OpenOffice.org Calc
Theoretical part
What is Calc?
Calc is the OpenOffice.org (OOo) spreadsheet module. You can enter data, usually numeric, into a spreadsheet, and then manipulate that data to produce specific results.
You can also enter data and use Calc in 'What-If' mode by changing some of the data and observing the results without having to re-enter the rest of the data completely.
The main advantage of spreadsheets is that the data is easier to modify. If the correct functions and formulas are used, the program will automatically apply these changes.
Spreadsheets, sheets and cells
Calc works with elements namedspreadsheets... Spreadsheets are composed of a specific numbersheets , each of which contains a block of cells arranged in rows and columns.
These cells contain individual elements - text, numbers, formulas, and so on - that represent data that is displayed and manipulated.
Each spreadsheet can have many sheets, and each sheet can have many individual cells. Each Calc sheet can have a maximum of 65,536 rows and a maximum of 245 columns (A through IV). This amounts to 16,056,320 individual cells per sheet.
Components of the main Calc window
When loading Calc for execution, the main window looks as shown in Fig. 1.
Task number 1. Formatting data in spreadsheets
1. Start OpenOffice.org Calc.
2. Save the book under the name "Spreadsheet" in the LR_№9 folder on the H drive:
3. Create the following table. (The row numbers and column names in Calc are highlighted in yellow in the table. See the table below for the order of execution).
4. Place the cursor in cell A1 and type "Data representation in OpenOffice.org Calc", font size 14, bold italic.
5. Select cells A1-H1. (Place the cursor in the center of cell A1, press the left key and, without releasing it, drag to cell H1). On the Formatting toolbar, click the Merge Cells button and center.
6. Select columnsA-H ... (Set the cursor to the column nameA (name is a capital Latin letter in the upper part of the column), press the left key and, without releasing it, stretch to column F).
7. Run the command "Format \\ Column \\ Width" and set the width of the columns to 4 cm.
8. Place the cursor in cell A3 and type the text "Random number"
9. Place the cursor in cell B3 and type the text "Word.< 100"
10. Place the cursor in cell C3 and type the text "Monetary format"
11. Place the cursor in cell D3 and type the text "Percentage format"
12. Place the cursor in cell E3 and type the text "Fractional format"
13. Select columns A-F, execute the command "Format \\ Column \\ Optimal Width". Leave everything unchanged and click OK.
14. Place the cursor in cell A4, Insert → Function, function type - mathematical, select the "RAND ()" function. (The "RAND ()" function returns a random number between 0 and 1).
15. Place the mouse pointer on the filling marker of cell A4, press the left mouse button and, without releasing it, drag it to cell A14.
Marker Is the black dot in the lower right corner of the cell. When you position the mouse pointer over a fill handle, it changes to a thin black cross. In this case, the formula written in the cell is copied to the next cells with a change in the cell addresses.
16. Place the cursor in cell B4, and type the formula: \u003d A4 * 100, press key. ENTER (The names of columns A4, C12, etc. should be typed only in Latin letters).
17. Place the cursor on the fill marker of cell B4 and copy the function to cell B14
18. Place the cursor in cell C4, and type the formula: \u003d A4 * 100, press cl. ENTER and copy the formula to cell C14.
19. Place the cursor in cell D4, and type the formula: \u003d A4 * 100, press key. ENTER and copy the formula to cell D14
20. Place the cursor in cell E4, and type the formula: \u003d A4 * 100, press key. ENTER and copy the formula to cell E14
21. Select cells A4-A14 and set the number format ("Format \\ Cells \\ Numbers \\ Numeric")
22. Select cells B4-B14 and set the number format with three decimal places ("Format \\ Cells \\ Numbers \\ Special") and write # ###, 000 in the format code
23. Select cells C4-C14 and set the currency format with two decimal places ("Format \\ Cells \\ Numbers \\ Currency")
27. Select cells D4-D14 and set the percentage format with one decimal place ("Format \\ Cells \\ Numbers \\ Special") and write 0.0% in the format code
28. Select cells E4-E14, set fractional format and display numbers as simple fractions ("Format \\ Cells \\ Numbers \\ Fractional")
29. Select cells A3-F14 and set outer and inner borders ("Format \\ Cells \\ Borders")
30. Set page parameters. (Format \\ Page \\ Page, top margin -1 cm, bottom - 4 cm, left - 3 cm, right - 1 cm)
31. Run the command "Tools \\ Options \\ OpenOffice.org Calc \\ View" and set the display mode for formulas.
32. Select columns A-F, execute the command "Format \\ Column \\ Optimal width"
33. Run the command "Tools \\ Options \\ OpenOffice.org Calc \\ View" and remove the formulas display mode.
34. Select columns A-F, execute the command "Format \\ Column \\ Optimal width"
35. Click on the "Save" button on the toolbar or execute the File / Save command.
36.
Go to sheet 2. (Sheet numbers are displayed at the bottom of the screen on a horizontal scroll bar). Create a table with 10 rows.
37. Place the cursor in cell A1 and type the title: "Representation of numbers in OpenOffice.org Calc", font size 14, bold italic.
38. Select cells A1-F1 and on the "Formatting" toolbar, click the "Merge Cells" button and align to the center.
39. Place the cursor in cell A3 and type the text "Random number<10000"
40. Place the cursor in cell B3 and type the text "0 characters"
41. Place the cursor in cell C3 and type the text "3 decimal places"
42. Place the cursor in cell D3 and type the text "2 decimal places"
43. Place the cursor in cell E3 and type the text "Integer"
44. Select columns A-E, execute the command "Format \\ Column \\ Optimal width"
45. Select cells A3-E3, rotate the text by 60˚ ("Format \\ Cells \\ Alignment").
46. \u200b\u200bPlace the cursor in cell A4, and type the formula \u003d RAND () * 10000
47. Place the cursor on the filling marker of cell A4 and copy the function to cell A14
48. Place the cursor in cell B4, and type the formula \u003d ROUND (A4; 0)
49. Place the cursor on the filling marker of cell B4 and copy the function to cell B14
50. Place the cursor in cell C4, and type the formula \u003d ROUND (A4; 3), press key. ENTER and copy the formula to cell C14
51. Place the cursor in cell D4, and type the formula \u003d ROUND (A4; -2), press key. ENTER and copy the formula to cell D14
52. Place the cursor in cell E4, and type the formula \u003d INT (A4), press key. ENTER and copy the formula to cell E14
53. Run the command "Tools \\ Options \\ OpenOffice.org Calc \\ View" and set the display mode for formulas. You should get a table of the following form, consisting of 10 rows:
54 Select columns A-F, execute the command "Format \\ Column \\ Optimal width"
55. Run the command "Service \\ Options \\ View" and remove the formulas display mode.
56. Select columns A-F, execute the command "Format \\ Column \\ Optimal width"
57. Create two lists of five names: the first - from the students of the first subgroup; the second - from the students of the second subgroup (Service / Parameter / Lists / OpenOffice.org Calc / Sorting lists and click on the Create button in the "Elements" line by line type the names and click the add button and OK).
58. In cell G3, dial "1 group".
59. In cell G4, type the surname of the student from the first list, place the cursor on the filling marker and drag it down.
60. In cell H3, dial "Group 2".
61. In cell H4, type the name of the student from the second list, place the cursor on the fill marker and drag it down.
62. Select cells A4-H14 and set the outer and inner borders ("Format \\ Page \\ Border", select the line type, color, click on the buttons "Inner" and "Outer")
63. Save the book under the name "Spreadsheet" in the folder ЛР_№9 on the drive H:
Task number 2
1.Create a new workbook (File / New)
2. Save the book under the name "Sales volume" in the LR_9 folder on the H drive:
3. Create the following table. (For the order of execution, see the table below).
Volume of sales 
4. Set the cursor to cell A1 and type "Sales volume", font size 14, bold italic.
5.Select cells A1-H1 and on the "Formatting" toolbar click the "Merge" button and align to the center.
6. Place the cursor in cell A3 and dial "№№".
7.Select cells A3 and A4 and execute the Format / Cells / Alignment command, set the alignment both horizontally and vertically "Centered" and on the "Formatting" toolbar, click the "Merge" button.
8. Place the cursor in cell B3 and type "Name".
9. Select cells B3 and B4 and combine them (see above).
10. Fill in the head of the table according to the above example.
11. Fill in the "Name" column. (Dial ten different product names).
12. The sales volume in October will be calculated as an integer random number in the range from 0 to 10. To obtain integers, random numbers in the range from 0 to 10, you need to enter a cellC5 enter the formula:
\u003d INT (RAND () * 10)
13. Place the cursor on the fill marker of cell C5 (bottom right corner of the cell, a black cross appears), press the left mouse button and, without releasing it, drag the cursor down to line number 14.
14. The volume of sales in November is calculated as an integer random number in the range from 10 to 30. To obtain integers, random numbers in the range from 10 to 30, you need to enter a cell D5 enter the formula:
\u003d INT (RAND () * (30 - 10) + 10)
15. Place the cursor on the fill marker of cell D5
16. The volume of sales in November is calculated as an integer random number in the range from 20 to 50. To obtain integers, random numbers in the range from 20 to 50, enter the formula in cell E5:
\u003d INT (RAND () * (50 - 20) + 20)
17. Place the cursor on the fill marker of cell E5, press the left key and drag the cursor down to line 14.
18. To fill in the "Total sold" column, you need to sum up the Sales volume for October, November, December. Into the cellF5 enter the formula:
\u003d C5 + D5 + E5
19. Place the cursor on the cell fill marker F5 , press the left key and drag the cursor down to line 14.
20. To get random numbers in monetary format in the range from 50 to 150, you need to enter a cellG5 enter the formula:
\u003d RAND () * (150 - 50) + 50)
21. Place the cursor on the cell filling markerG5 , press the left key and drag the cursor down to line 14.
22. To represent numbers in monetary format, select the column with the name "Price" (left-click on the column name, ie on the letterG ), execute the command Format / Cells / Numbers / Currency / Set two decimal places, OK.
23. To find the values \u200b\u200bof the "Amount" column, multiply the values \u200b\u200bof the "Total sold" and "Price" columns. Into the cellH5 enter the formula:
\u003d F5 * G5
24. To find the "TOTAL" amount, place the cursor in cell C15 and enter the formula:
\u003d SUM (C5: C14)
25. Place the cursor on the fill marker of cell C15 , press the left key and drag the cursor to the right to the cellF15 .
26. Place the cursor in the cellH15 and enter the formula:
\u003d SUM (H5: H14)
27. Select the table and run the Format / Column / Optimal Width command.
28. Set the outer and inner borders of the table. Select the table, execute the Format / Cells / Border command / click on the Outer and Inner items, select the line type, OK.
29. Set page parameters (Format \\ Page \\ Page)
30. Preview. (File / Preview).
31. Close the preview mode.
32. Save the book under the name "Sales volume" in the LR_9 folder on the H drive:
33. Copy the table from sheet # 1 to sheet # 2.
34. Set the display mode for formulas. (Tools \\ Options \\ OpenOffice.org Calc \\ View OK).
35. Select the table and execute the Format / Column / Optimal Width command.
36. Set page parameters.
37. Click on the "Save" button on the toolbar or execute the File / Save command.
38.
Go to sheet 3.
39. Create the following table(see below) .
40. The number of goods sold per month - a random integer in the range from 0 to 20, - price - a random number in monetary format with two decimal places in the range from 10 to 100 rubles. (Round off the result to two decimal places). - plan - a random integer in monetary format in the range from 100 to 2000 rubles.
41. To find the amount, the quantity must be multiplied by the price.
42. To find the% of the plan, the amount must be divided by the plan and presented in percentage format. (Round off the result to two decimal places).
43. Add everything to find the grand total.
Volume of sales 
12. Set the outer and inner borders of the table. Select the table, execute the Format / Cells / Border command / click on the Outer and Inner items, select the line type, OK.
13. Set the page parameters.
14. Save the book (Click on the "Save" button).
15.
Create sheet # 4. (Insert / Sheet).
16. Copy the table from sheet 3 to sheet 4.
17. Set the display mode for formulas. Go to Tools \\ Options \\ OpenOffice.org Calc \\ View / Formulas and OK.
18. Select the table and execute the Format / Column / Optimal Width command.
19. Set the page parameters.
20. Rename all sheets of the book under the namesTable # 1, Table # 2, Table # 3, Table # 4 (Right-click on the sheet name, select "Rename").
21. Save the book (Click on the "Save" button).
22. Quit OpenOffice.org Calc.
23. Present the result to the teacher.
Conclusion on work:
The table is a variant of content presentation, information. It is easier for users to perceive, especially when it comes to some comparative characteristics of equipment, cars or programs. Tables dilute boring monotonous text and add value. Let's figure out how to make a table in OpenOffice.
How to create a new table in Open Office
First, you need to decide and clearly think over the structure - the number of rows and columns. It is better to do this right away so that later you do not have to add data or delete it. Let's say it is decided that the table will consist of 10 rows and 3 columns. Let's create it and create it on the OpenOffice page.
Go to the "Insert-Table" menu (you can use the keyboard shortcut Ctrl + F12), a window for specifying the parameters will open.
In the window that appears, you need to specify:
- table name,
- number of columns and rows,
- you can specify a line (or lines) that will be headings; if the table is large and will be split into several pages of an OpenOffice document, then enable the Repeat Title option so that each sheet has the required header lines. This is important so that on each page it is clear what each row or column is responsible for.
After clicking OK, the table will be inserted into the OpenOffice document and you can fill it out. If you suddenly missed something, for example, you indicated fewer rows or columns than necessary, you can easily add them. To do this, click on any table place with the mouse and a special editing menu will be available.
You can add or remove rows and columns, make a union, fill the entire table or individual cells with a background, change the thickness of borders (lines) and other parameters to taste and color.
Creating a table from typed text
It is not always convenient to fill a table in OpenOffice, especially since it has a lot of cells in it. Sometimes it's easier to type the text first and then convert it to a table. OpenOffice makes this very easy. The principle is this:
The result will be immediately displayed in the Open Office table.
How to delete a table in Open Office
To delete a table completely, and not its individual parts, place the mouse cursor on any table area, then click on the "Table-Delete-Table" menu items.
Of course, creating a table in OpenOffice is very simple and there is no point in using any third-party programs or services. But do not forget that Open Office is a full-fledged office suite, which, when installed on your computer, contains a specialized tool for working with OpenOffice Calc spreadsheets, this is an analogue of MS Excel.
