Working with openoffice calc spreadsheets

Working with tables
Below is a table consisting of two rows, three columns:

The table cells contain numbers. In a table, you can add, merge or delete rows and columns, sort text, etc. Let's look at the basic operations in more detail.

Creating a table. There are two ways to create a table.In the first case, use the mode Table →Add table, and a window will appear on the screen where you need to specify h and the village of Stolbtsy and Rows that will be in the table, after which you can enter text or numerical information into the cells.

In the second case, the existing text is selected and the mode is used Table →Convert →Convert text to table, a window will appear on the screen as shown below.

Depending on the type of separator indicated at the bottom of the window, the number of rows and columns is set. For example, the separator can be the character dot. To do this, set the switch Other, in which this character is entered from the keyboard. Depending on the number of points in the selected text and the number of paragraphs, there will be a different number of columns. If you specify a different character, the numeric values ​​will change because the number of delimiters in the text will be different.

After creating the table, you can enter text into it. To do this, place the cursor in the desired cell and left-click, then enter text from the keyboard. After placing the cursor in the table, the ruler will mark the boundaries of vertical and horizontal lines, which can also be moved. The text in each cell is formatted in the same way as regular text. If you need to format a cell, then you need to select it and apply formatting operations; if there are several, then select several cells. Since the key Tab is used to move to the next cell, then to set the tab character you need to press Ctrl+Tab. When you press a key Tab a new row will be added in the last cell of the table.

The table borders can be moved, for which you need to place the cursor on the border of the line, and it will turn into an icon with vertical or horizontal lines with arrows (), then move the border to a new location, and the width of the adjacent columns on the right will change. If you drag the border while holding down the key Shift, then the width of the columns located to the left of the border will change, and if you click on Ctrl+Shift, then the width of only one line will change. If you hold down the key Alt, then only the sizes of the cells on the left and right of the ruler will change. You can also use the icons on the ruler to the left and top of the table. To do this, you can change the table boundaries by moving the icons on the ruler.

Table view. To change the table view, use the command Table →Autoformat…, in which there are Formats: , among which you can select the one you need, and an example of the framing can be seen in the field in the center of the window.

Selecting columns or rows. Selecting lines is done in two ways. The first one places the cursor on the cell that needs to be selected, then press the left mouse button, and without releasing it, move it to the opposite corner, after which we release the button. For example, in a 5x5 table you need to highlight the second and third lines. In this case, the cursor is placed on the cell in the first column in the second cell (1,2) and then moved to the fifth column, in the third cell (5,3), as a result the second and third lines will be selected. You can take other corners of the table, for example, with addresses from (1,3) to (5,2) or (5,2) to (1,3), etc., where the first digit indicates the column number, the second - the number lines. To select a full line, you need to select the cells on the line from the first (1) to the last column (5).

The second method is to place the cursor to the left of the desired line and briefly press the left mouse button (when the mouse cursor turns into an arrow), and the line is highlighted. If you place the cursor to the left of the table and click on the mouse button, the line will be highlighted, and if, without releasing the mouse button, you move the cursor below or above the line, then the adjacent lines around which the cursor moved will also be highlighted. As soon as the desired lines are highlighted, you can release the mouse cursor.

Columns are highlighted in the same way. To select columns in the second way, you need to position the mouse cursor over the columns, the cursor will turn into a vertical arrow, and then move the cursor to the left or right. You can select the entire table using the mode Table →Select →Table.

Next, using the group modes from the top menu Table, rows (columns) are deleted, added, combined using the modes: Table →Insert →Rows, Table →Delete →Rows, Table →Merge Cells etc.

You can paste calculations in a table into the text, and then paste the formula into the text using the Table →Formula…, for example, click on a table cell and use the mode:Table →Formula. Then place the mouse cursor in the first row and column where the selected cell is located and select the cells that are above the selected one where you need to set the formula. As a result, in the cell where the calculations are carried out the following message will appear: "= ", which indicates that this cell will contain the sum of the five cells located above. To complete the operation, press the Enter key. Now, if you change the number, for example, in the first upper cell, the sum will also change.

You can also use the modes that are in the panel that appears if you place the cursor in any cell(s) of the table. Let's create a table of size 3x3. Let's place the cursor in the first cell. If you click on the first button, you can insert a new table into the cell, for example, 2x2.

The article is devoted to creating pivot tables using OpenOffice.org. A PivotTable is a summary of large amounts of data. You can rearrange the PivotTable to view different summaries of your data.

A pivot table is used to combine, compare, and analyze large amounts of data. You can view various summaries of your source data, display detailed information from areas of interest, and create reports.
The table created using the PivotTable function is interactive. The data in it can be organized, redistributed or summarized from different points of view.

First, we need to prepare a table with the initial data. An example of such a table could be the document shown in the following picture.

Select the table and go to the menu Data - Pivot Table.

Now you need to select a data source. We use the previously selected table (check the box next to “Current selection”. Please pay attention to the fact that you can also use external data sources to create a pivot table).

A form for organizing a pivot table appears.

We drag the fields with the names of our columns of the source table (the right column of the form) into the fields of the pivot table on the form (white fields), as shown in the figure:

Pay attention to the “Advanced” button; by clicking on it, you can set additional conditions for generating a pivot table.

You also need to recognize the fact that different fields in a pivot table define different ways of grouping and presenting data. This will be partially demonstrated below.

When you click “OK” we get the first summary table:

Thus, we were able to reduce the total number of fruits accepted over a specified period of time and display their total quantity.

To demonstrate the capabilities of pivot tables, let’s further complicate the task: add the “From” item to our original table, characterizing the country of origin of the cargo. We get the original table shown in the following screenshot:

Let's create a layout for a new pivot table:

And let's admire the result:

The result is an even more visual table for analysis.

However, we may receive several varieties of fruits from different countries per day. We complicate the table by adding new rows to it (highlighted in color in the figure):

Let's create a new pivot table:

After creating such a table, we have a drop-down list that allows us to add even more information and “interactivity” to the analysis process.

It can be noted that the result of presenting the data becomes even more clear (this is illustrated in the next two screenshots):

Having analyzed a simple example of working with pivot tables, it was shown how easy and flexible their use can be in a free office suite.

First way

The font and paragraph format of all table cells will be the same as the corresponding format where the table was inserted. Changing the format of one cell is independent of changes to the format of another. Each cell is completely autonomous.

2. Open the Table menu.

3. In the list of commands, move the cursor to Insert.

4. In the menu that opens, select Table.

5. In the Insert Table window, in the Table Size group, use the Columns and Rows sliders to set the required number of columns and rows. -The number of columns and rows in the table is from 1 to 8192.

6. Close the window with the OK button.

Second way

1. In the open document window, place the text input cursor on the page at the location of the table.

2. Open the Table button menu on the Standard panel.

3. In the table grid, select the required number of rows and columns.

4. Left-click when the desired number of rows and columns are highlighted.

To reduce the number of rows and columns, return the cursor to the previous positions.

How to create a table based on autoformat

Writer provides a large collection of preformatted table templates that you can easily fit into your document and edit as you see fit.

1. In the open document window, place the text input cursor at the location of the table.

2. Open the Table menu and in the list of commands, move the cursor to Insert.

3. In the list that opens, select Table.

4. In the Insert Table window, click the AutoFormat button.

5. In the AutoFormat window, select the desired table style from the list.

6. To edit the selected format, click the Details button and disable the necessary items.

7. Close the window with the OK button. How to convert a table with AutoFormat

1. In the open document window, place the text input cursor in any cell of the desired table.

2. Click the AutoFormat button on the Table panel.

3. In the AutoFormat window, select the desired table style from the list.

4. Close the window with the OK button.

How to convert text to table

If necessary, the already typed document text can be converted into a table.

1. In the open document window, select the desired text that you want to convert into a table.

2. Open the Table menu.

3. In the list of commands, hover over the Convert item.

4. In the drop-down list, select Text to table.

5. In the Convert Text to Table window, in the Text Separator group, select how you want to split the text into table columns.

By selecting Custom, you can specify a non-standard break symbol.

6. Close the window with the OK button.

How to navigate the table

To get to the desired table cell, move the cursor over it and click the left mouse button or move the cursor with one of the four arrow keys (up, down, left, right). To move quickly, use the following keyboard shortcuts:

Tab - to the right cell from the current one;

Shift+Tab - to the left cell from the current one;

Alt+Home - to the first cell of the current line;

Alt+End - to the last cell of the current line;

Alt+Page Up - to the first cell of the current column;

Alt+Page Down - to the last cell of the current column.

How to select a table

First way

In an open document window, left-click over the top-left corner of the desired table when the pointer changes to a diagonal arrow.

Second way

1. In the open document window, place the cursor in any table cell.

2. Use the keyboard shortcut Ctrl+A. How to set the exact table width

3. In the Table Properties window, on the Table tab, in the Alignment group, activate the Manual item.

4. In the Properties group, use the Width slider to set the desired table width in centimeters.

When the Relative width item is activated, the width is specified as a percentage.

5. Close the window with the OK button. How to center a table on the page

1. In the open document window, place the cursor in any cell of the table.

2. Click the Table Properties button on the Table panel.

3. In the Table Properties window, on the Table tab, in the Alignment group, activate the Center item.

4. Close the window with the OK button.

How to set table indent from fields

1. In the open document window, place the cursor in any cell of the table.

2. Click the Table Properties button on the Table panel.

3. In the Table Properties window, on the Table tab, in the Interval group, use the sliders to set the desired indentation value.

4. Close the window with the OK button.

How to combine several tables into one

Several independent tables can be merged together.

First way

If tables are separated only by paragraph marks, remove these markup characters as usual.

Second way

If there is text between the tables, you need to move the tables and place them sequentially one after the other, and then remove the paragraph characters between them.

Laboratory work No. 13

Creating Spreadsheets in OpenOffice.org Calc

Hardware: PC
Software: Windows, OpenOffice.org Calc.
Purpose of the work: acquisition and consolidation of practical skills in OpenOffice.org Calc

Theoretical part

What is Calc?

Calc is the OpenOffice.org (OOo) spreadsheet module. A spreadsheet allows you to enter data, usually numeric, and then manipulate that data to produce specific results.

You can also enter data and use Calc in a What-If mode, changing some of the data and seeing the results without having to completely re-enter the rest of the data.

The main advantage of spreadsheets is that data is easier to change. If the correct functions and formulas are used, the program will automatically apply these changes.

Spreadsheets, Sheets and Cells

Calc works with elements that have a namespreadsheets. Spreadsheets consist of a specific number sheets , each containing a block of cells arranged in rows and columns.

These cells contain individual elements—text, numbers, formulas, etc.—that represent the data that is displayed and manipulated.

Each spreadsheet can have many sheets, and each sheet can consist of a large number of individual cells. Each Calc worksheet 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 like shown in Fig. 1.

Task No. 1. Formatting data in spreadsheets
1. Launch OpenOffice.org Calc.
2. Save the book under the name "Spreadsheet" in the LR_№9 folder on drive H:
3.Create the following table. (The row numbers and column names in Calc are highlighted in yellow in the table. For the execution order, see below the table).

4. Place the cursor in cell A1 and type “Data presentation 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 place in the center.
6. Select columns A-H . (Place the cursor on the column name A (the name is a capital Latin letter at the top of the column), press the left key and, without releasing it, drag it to column F).
7. Execute the command "Format\Column\Width" and set the column width 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 “Next number< 100"
10. Place the cursor in cell C3 and type the text “Currency 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 fill marker of cell A4, press the left mouse button and, without releasing it, drag to cell A14.
Marker is the black dot in the lower right corner of the cell. When you hover your mouse over the fill marker, it will appear as a thin black cross. In this case, the formula written in the cell is copied to the following cells, changing the cell addresses.
16. Place the cursor in cell B4, and type the formula: =A4*100, press key. ENTER (Column names A4, C12, etc. must be typed in Latin letters only).
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: =A4*100, press key. ENTER and copy the formula to cell C14.
19. Place the cursor in cell D4, and type the formula: =A4*100, press . ENTER and copy the formula to cell D14
20. Place the cursor in cell E4, and type the formula: =A4*100, press . 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 the fractional format and display the numbers as simple fractions ("Format \ Cells \ Numbers \ Fractional")
29. Select cells A3-F14 and set external and internal borders ("Format\Cells\Border")
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 formula display mode.
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 formula display mode.
34. Select columns A-F, execute the command "Format\Column\Optimal Width"
35. Click 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 of 10 rows.
37. Place the cursor in cell A1 and type the title: “Representing 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 60˚ (“Format \ Cells \ Alignment”).
46. ​​Place the cursor in cell A4 and type the formula =RAND()*10000
47. Place the cursor on the fill marker of cell A4 and copy the function to cell A14
48. Place the cursor in cell B4 and type the formula =ROUND(A4;0)
49. Place the cursor on the fill marker of cell B4 and copy the function to cell B14
50. Place the cursor in cell C4, and type the formula =ROUND(A4;3), press key. ENTER and copy the formula to cell C14
51. Place the cursor in cell D4, and type the formula =ROUND(A4,-2), press key. ENTER and copy the formula to cell D14
52. Place the cursor in cell E4, and type the formula =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 formula display mode. You should end up with a table that looks like this, consisting of 10 rows:
54 Select columns A-F, execute the command "Format\Column\Optimal Width"
55. Execute the command “Tools\Options\View” and remove the formula display mode.
56. Select columns A-F, execute the command "Format\Column\Optimal Width"
57. Create two lists of five names: the first from students of the first subgroup; the second - from the students of the second subgroup (Tools / Option / Lists / OpenOffice.org Calc / Sorting lists and click on the Create button in “Elements”, type surnames line by line and click the add and OK button).
58. In cell G3, type “1 group”.
59. In cell G4, type the student’s last name from the first list, place the cursor on the fill marker and drag down.
60. In cell H3, type “group 2”.
61. In cell H4, type the student’s last name from the second list, place the cursor on the fill marker and drag down.
62. Select cells A4-H14 and set external and internal borders ("Format\Page\Border", select line type, color, click on the "Internal" and "External" buttons)
63. Save the book under the name “Spreadsheet” in the LR_№9 folder on drive H:

Task No. 2

1.Create a new book (File / New)
2. Save the book under the name “Sales Volume” in the LR_9 folder on drive H:
3.Create the following table. (For the order of execution, see the table below).
Sales volume

4. Place the cursor in 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 type “№№”.
7.Select cells A3 and A4 and execute the command Format / Cells / Alignment, set the alignment both horizontally and vertically to “Center” 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 merge them (see above).
10.Fill out the table header according to the sample above.
11.Fill in the “Name” column. (Choose ten names of different products).
12. We will calculate the sales volume in October as a random integer in the range from 0 to 10. To obtain integer, random numbers in the range from 0 to 10, you need to enter the cell C5 enter the formula:

=INT(RAND()*10)

13. Place the cursor on the fill marker of cell C5 (lower right corner of the cell, a black cross appears), press the left mouse button and, without releasing it, drag the cursor down to line No. 14.
14. Sales volume in November is calculated as a random integer in the range from 10 to 30. To obtain integer, random numbers in the range from 10 to 30, you need to enter the cell D5 enter the formula:

=INT(RAND()*(30 - 10) + 10)

15. Place the cursor on the fill marker of cell D5
16. Sales volume in November is calculated as a random integer in the range from 20 to 50. To obtain integer, random numbers in the range from 20 to 50, enter the formula in cell E5:

=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 number 14.
18. To fill out the “Total Sold” column, you must sum up the Sales Volume for October, November, December. To cell F5 enter the formula:

=C5+D5+E5

19. Place the cursor on the fill marker of cell F5 , press the left key and drag the cursor down to line number 14.
20. To receive random numbers in monetary format in the range from 50 to 150, you need to enter a cell G5 enter the formula:

=RAND()*(150 50) + 50)

21. Place the cursor on the cell fill marker G5 , press the left key and drag the cursor down to line number 14.
22. To present numbers in monetary format, select the column called “Price” (click with the left mouse button on the column name, i.e. on the letter G ), execute the command Format / Cells / Numbers / Currency / Set two decimal places, OK.
23. To find the values ​​of the “Amount” column, you need to multiply the values ​​of the “Total Sold” and “Price” columns. To cell H5 enter the formula:

=F5*G5

24. To find the “TOTAL” amount, place the cursor in cell C15 and enter the formula:

=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 cell F15 .
26. Place the cursor in the cell H15 and enter the formula:

=SUM(H5:H14)

27. Select the table and execute the command Format / Column / Optimal Width.
28. Set the outer and inner borders of the table. Select the table, run the command Format / Cells / Border / click on the External and Internal items, select the line type, OK.
29. Set page parameters (Format\Page\Page)
30. Preview. (File/Preview).
31. Close Preview mode.
32. Save the book under the name “Sales Volume” in the LR_9 folder on drive H:
33. Copy the table from sheet No. 1 to sheet No. 2.
34. Set the formula display mode. (Tools\Options\OpenOffice.org Calc\View OK).
35. Select the table and execute the command Format / Column / Optimal Width.
36. Set page options.
37. Click 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 is a random integer in the range from 0 to 20, - price is a random number in monetary format with two decimal places in the range from 10 to 100 rubles. (Round the result to two decimal places). - plan random integer number in monetary format in the range from 100 to 2000 rubles.
41. To find the amount, you need to multiply the quantity by the price.
42. To find the % of plan completion, the amount must be divided by the plan and presented in percentage format. (Round the result to two decimal places).
43. To find the total, add everything up.

Sales volume


12. Set the outer and inner borders of the table. Select the table, run the command Format / Cells / Framing / click on the External and Internal items, select the line type, OK.
13. Set page options.
14. Save the book (Click on the "Save" button).
15. Create sheet #4.(Insert/Sheet).
16. Copy the table from sheet No. 3 to sheet No. 4.
17. Set the formula display mode. Run Tools\Options\OpenOffice.org Calc\View/Formulas and OK.
18. Select the table and execute the command Format / Column / Optimal Width.
19. Set page options.
20. Rename all sheets in the workbook under namesTable No. 1, Table No. 2, Table No. 3, Table No. 4(Right-click on the sheet name and select "Rename").
21. Save the book (Click on the "Save" button).
22. Quit OpenOffice.org Calc.
23. Show the result to the teacher.

Conclusion about the work:

There are a number of restrictions imposed on the database structure:

• the first row of the database must contain non-repeating field names and be located on one line;
• For field names, you should use a different font, data type, format, frame than those used for data in records;
• the table should be separated from other worksheet data by an empty column and an empty row;
• information on the fields should be homogeneous, i.e. only numbers or only text.

Working with any database involves searching for information based on certain criteria, regrouping and processing information.

Sorting data in a table is done using the command Data > Sorting or buttons on the toolbar Standard Sort Ascending And Sort in descending order(Fig. 44).

For example, sort a table containing last name and gender so that all female last names appear first in alphabetical order, and then male ones.

Let's select the table with the data. Data > Sorting. In the window Sort by choose floor(this is the table header), in the window Then by choose surname(Fig. 45). Let's get rice. 46.

Using the command Data > Filter > AutoFilter the data is selected according to the criterion. At the same time, buttons (arrow) appear in the cells where the headings are located. When you click on them, a menu appears with the autofilter selection conditions (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 > Filter > Standard Filter It will be possible to select rows that satisfy the specified conditions (Fig. 48).

Preparing a document for printing

The created document can be printed either as a sheet or as an entire book, as well as a selected fragment of the sheet and the print area (a fragment of the sheet fixed in a special way). If a print area is specified, only that will be printed. When you save a document, the printable area is also saved. To determine the print area, you need to select a fragment of the sheet (one of the methods for selecting cells), then Format > Print Ranges > Define. Thus, the selected fragment has been converted into a printable area. To change this area, select Format > Print Ranges > Add Area print or Format > Print Ranges > Edit. In the dialog box that appears, change the area as follows:

1. In the field Print range Click on the (Collapse) icon to reduce the dialog box to the size of the input field. After this, it is easier to mark the desired link on the sheet. Then the icon automatically turns into the (Expand) icon, click it to restore the original dimensions of the dialog box. The dialog box will be automatically minimized if you click inside the sheet. When you release the mouse button, the dialog box will be restored and the range of links defined with the mouse will be highlighted in the document with a blue border.
2. Line repeat. Repeating lines are determined using the mouse if the cursor is located in the field Line repeat dialog box. Select one or more lines to print. The list displays the value "user-defined". Select "no" from the list to cancel the set repeat of lines.
3. Column repeat. Select one or more columns to print on each page, and a column reference such as "$A" or "$C:$E" appears in the right text box. The list displays the value "user defined". Select from the list "No" to cancel the set repeat columns.

If the print area is not needed, delete it in a similar way.

To print, you also need to configure the page settings, that is, paper size, sheet orientation, headers and footers, print direction, etc. Let's do this through the menu item Format > Page and on the tabs of the dialog box the page is configured (Fig. 49, 50, 51). Preview allows you to see how your document will look on paper.

To view the page area, use the menu item View > Page Layout(Fig. 52). By moving the borders (blue) of the page, we change the area of ​​the page for printing by changing the scale, which we will see through Format > Page > Sheet > Scale.

Leave your comment!