10. In the Mail Merge toolbar, click on Next Mail Merge Entry button to see the first merged letter. ¤ Click on Edit Individual Document button to display the merged document. ¤ Click Save Merged Document button to save it. ¤ Click Print Merged Document button to print the merged document. LAB EXERCISE Your school is going to organize annual sports meet. Use mail merge feature as specified here: 1. Create an invitation letter to invite all the schools of your zone/district to participate in this event. 2. Create a list of 10 schools that you want to invite. The fields should be: PRINCIPAL_NAME SCHOOL_NAME SCHOOL_ADDRESS 3. Perform mail merge to generate individual letters for each school. Address Labels Labels contain the addresses which can be printed for mailing purpose. All the labels may contain same address on all of them or different address. Once you have the data source for labels is ready and registered with LibreOffice like you did earlier, you can begin creating a document containing labels out of it. We are going to use a Writer document addresses.odt as data source that contains all the data for labels as shown here. 5100 Digital Documentation (Advanced)
Creating Label Document 1. Go to File menu > New > Labels options. In the Labels dialog box, do all the settings for the labels. We shall create multiple labels with different addresses. 2. In Labels tab, selected your registered data source in Database drop-down and Table 1 as the data table. 3. One-by-one add the fields from Database field drop-down using Insert button. 4. Arrange the fields in the Label Text box as you need. 5. Go to Format tab and define the page dimensions that will contain the labels, number of labels in terms of rows and columns, dimensions of the labels and distance between them. Digital Documentation (Advanced) 6. Go to Options tab and check the Synchronize content checkbox so that if you format one label then other labels are formatted automatically. 7. Click on New Document button. A new document with label placeholders will appear. Each placeholder shows the fields in the label. 5101
At this time if you change the format of any label and click on Synchronize Labels button in the Synchronize popup, the format will be applied to all other labels. 8. Click on Next Mail Merge Entry button to preview the labels. 9. Click on Edit Individual Document button. The document with labels will be displayed. Note: Try making the name of one recipient bold and colourful then click on Synchronize Labels button in the Synchronize popup. Notice that same format is applied to all the other labels. LAB EXERCISE 1. Create labels of dimension 4 X 5 inches to paste on the notebooks. Label should contain your school name and space to write Name, Class, Section and Subject. 2. Create multiple visiting cards on single page for your friend containing a fictitious company name, Address and Contact Number. 5102 Digital Documentation (Advanced)
A. Select the correct option to answer the following questions – 1 mark each. 1. Ravi has drafted his office annual report. He wants to quickly format the contents and give them a professional, formal look. Which of the following should help him? a. Templates b. Styles c. Themes d. None of these 2. Which of the following features is best suited to make a formatted document reusable multiple time? a. Save it as Template b. Apply styles c. Apply themes d. Mail merge 3. Graphics styles can be applied using which type of styles? a. List b. Frame c. Paragraph d. Page 4. Which of the following styles affect the formatting of a block of text in a paragraph? a. Character b. Frame c. Paragraph d. Page 5. Renu wants to apply formatting of a text piece to multiple other text pieces. Which of the following must help her in doing so? a. New style from selection b. Fill format mode c. Update selected style d. None of these 6. Rajat is a computer programmer and he needs to create graphical representations of his program logic. Which shape category is most useful for him? a. Basic shapes b. Block arrows c. Callouts d. Flowchart 7. Anu has an old family picture. She wants it to insert in her document. Which is the best way to do this? a. Insert > Picture from file b. Click picture with cell phone camera, copy it on computer then insert it c. Paste special d. Insert > picture scan 8. An embedded image is a linked image whose link option is removed. a. True b. False 9. A consistent look to the document formatting can be given by the help of which of the following? a. Styles b. Text alignment c. Section breaks d. Shapes Digital Documentation (Advanced) 5103
10. The flow of text around a shape or image is termed as which of the following? a. Text align b. Text arrangement c. Text wrap d. Text box 11. Which of the following actions changes the dimensions of an image inserted in a document? a. Skew b. Rotate c. Crop d. Symbol shapes 12. Grayscale is an example of which of the following? a. Image filter b. Image mode c. Image transparency d. None of these 13. One point place to work with templates is which of the following? a. Manage Templates b. Edit Template c. Template library d. Install template 14. Which of the following components help in installing online templates? a. Extensions b. Online Templates c. Update Templates d. All of these 15. Cut link to a template means which of the following? a. Changes in template will not be applied to the document b. Changes in the template will not be applied to a section of the document c. Document does not remain to be based on any template. d. Deactivated link to the template cannot be undone. 16. Table of Content is based on which of the following? a. Document templates b. Heading styles c. Page styles d. Document outline 17. Which of the following contains the merge field names in the top row? a. Main document b. Data source c. Merged document d. None of these 18. The variable values inserted in the main document from the data source are indicated by ______________. a. Merged fields b. Merged document c. Data d. Main document 19. Rajat has created some labels. He changed the text colour in one of the labels. How can he cascade same changes to all the other labels? a. Styles b. Label templates c. Synchronise content d. Apply to all 20. Contents of the labels are inserted from which of the following? a. Label database b. Label templates c. Data source d. Typed manually 5104 Digital Documentation (Advanced)
B. Answer the following questions – 2 marks each. 1. Briefly discuss the benefits of document styles. 2. How are styles different from templates? 3. List any 4 types of styles. 4. Write the steps to apply a paragraph style to a part of text. 5. How will you apply formatting of some text to other multiple pieces of text quickly and easily? 6. Anita designs school magazine. She has formatted some nice text samples. She wants them to be available as new styles in her document. Help her with the steps. 7. How will you load a style from a template? 8. How do shapes and images help in enhancing the content? 9. List any four distinct categories of shapes in a word processor. 10. List any 4 common ways of inserting images in a document (do not write the steps). 11. List any four common operations which can be done on an image. 12. What is test wrapping? 13. Which common properties of a line can you set? 14. Name various Area style filling options for a shape. 15. How is skew shape different from rotate shape? 16. How will you group and ungroup graphical objects? 17. List the names of four image modes. 18. Wat is an image filter? List the names and functions of any four image filters. 19. What do you mean by a template? What is a default template? 20. What is a ToC? What is its significance? 21. What does Synchronise content option do while working with labels? 22. What do you mean by mail merge? What is the role of a data source in mail merge? 23. What is the advantage of mail merge? C. Answer the following questions – 3 marks each. 1. Discuss briefly any two document style types. 2. How will you do the following? a. Create a new style. b. Load style from a template. c. Scan and insert an image in the document. d. Apply a bitmap fill in a shape. e. Make an image 50% transparent. f. Turn an image into a watermark. g. Sharpen the pixels of an image. h. Create a new template and then set as default template. Digital Documentation (Advanced) 5105
i. Install a template from internet. j. Change a template manually. k. Create a table of content and apply a style on it. 3. Discuss the different ways in which text can be wrapped around an image or a shape. 4. Sumit has created some of his own templates saved on his computer. How can he bring them in his document? 5. What does a Template Changer do? 6. Explain the terms – Main document, data sources, merged fields, merged document. D. Answer the following questions – 5 marks each. 1. What is a style? How is it different from a template? Discuss any 3 types of styles. 2. How will you create a new style and update an existing style with new changes. 3. What are the various ways if inserting image in a document? 4. Discuss various text wrapping options around an image. 5. What do you mean by image modes? How is it different from image filter? Discuss with example. 6. How will you apply basic formatting on a shape (line and fill)? 7. What do you mean by rotating, cropping and resizing an image? 8. What is the significance of a template? How will you create a new template? 9. Describe the process of updating a document with a changed template. 10. How will you set, deactivate, reactivate and cut link from a template with the document? 11. How will you create a table of content with 3 levels of headings with impact font, light blue fill area colour? 12. Taking a small example, explain the standard process of mail merge briefly. 13. You are given the task to create label sized ID cards for all the students in the school with the details – Name, Class, Section, House and Date of Birth. The data is stored in a document table. Briefly discuss the process of creating these labels. 5106 Digital Documentation (Advanced)
Unit Electronic Spreadsheet 2 (Advanced) Session-1 Calculating Subtotals Subtotals feature is a quick way to calculate automatic subtotals, averages and other numeric summary on a group of data. Subtotals works better on the sorted data. For subtotals, you need to have at least one column in the data-set as numeric to calculate the subtotal on. To calculate subtotals, first select the data-set and then click on Subtotals option in Data menu. In the Subtotals dialog box, specify the following: The field at whose change you need the subtotal in Group by drop-down. Select the fields for which calculation needs to be done in Calculate subtotals for list. The function you need to apply (by default, it is SUM) in Use function list. Then, click on OK button. The example shown here is calculating subtotals of QUANTITY at each change of TYPE of the garment. You can add more groups for sub-totals by going other group tabs in the dialog box. Electronic Spreadsheet (Advanced) 5107
LAB EXERCISE Mr. Rajan has a sheet containing the names of the students, their houses (Red, Green, Blue, Orange) at school and their score in sports. He wants to calculate number of students for each house and average score of each house. Help Mr. Rajan. Data Consolidation This feature allows you to bring together data from different spreadsheets and workbooks into a consolidated form where sum or average or other calculations are done on the data values. Let us see a simple example. Sheets named East Sale and West Sale need to be consolidated as their average in sheet Average Sale. Ÿ Go to the blank sheet and desired cell where you need to display the consolidated data. Ÿ Data menu > Consolidate option. Ÿ In the Consolidate dialog box, select the desired function to perform on the values. Click the Shrink/Expand button of Source Data Ranges and select the data range in first sheet and click on Add button. The select data range will be added in the list. Repeat this step for all other sheets to be consolidated. Finally, click on OK button. LAB EXERCISE Mrs. Sengupta has English marks of some students for exams Term1, Term2 and PreBoard in separate sheets by the same names. Help her to consolidate the average marks of the three exams into a fourth sheet in the same workbook. What-if Analysis What-if analysis is a way to do forecast on the basis of assumed but realistic figures. It is the process that allows to see “what” changes would happen “if ” the data values are changed in a particular manner. For example. “What” should be the commission “if ” the sale is increased? There are various ways to perform what-if analysis in electronic spreadsheets such as applying scenarios, goal-seek etc. What-if Analysis with Scenario Scenario is a way to perform what-if analysis. A scenario is defined by a set of values which can be applied on an existing data-set. The original data-set is substituted by the scenario data-set and user 5108 Electronic Spreadsheet (Advanced)
can analyse the changes. Various scenarios can be created and applied on the original data-set to analysis different situations predicted in the scenario. This way, user can analyse different business scenarios by using Scenario feature. Creating Scenarios Let us understand it with the example shown here. In this example, we shall apply different scenarios of prices to see the change in the commissions. The new set of prices are labelled as NEW PRICE 1 and NEW PRICE 2. These will be applied to the origirnal prices in column C. First, select all the original price values in range C2:C10. Go to Tools menu > Scenarios option. In the Create Scenarios dialog box, mention the name of the scenario (ORIGINAL PRICE) and click OK. Repeat this step once more but this time save the scenario with different name (NEW PRICE 1). Now, copy-paste the values of NEW PRICE 1 i.e. A13:A21 on the original range i.e. C2:C10. Now the second scenario has different values. Again, select all the values in range C2:C10 and create yet another scenario (NEW PRICE 2) Now, copy-paste the values of NEW PRICE 2 i.e. B13:B21 on the range C2:C10. Now the third scenario has different values. Applying Scenarios As you select different scenarios form the drop-down, you can observe and analyse the changing values of Commission. Electronic Spreadsheet (Advanced) 5109
LAB EXERCISE Mr. Ben is investing Rs. 12000 per month with a guaranteed return of 13% on the annual amount (i.e. 12 * 12000). Create a scenario to help Mr. Ben see the amount he should get in return after 15 years if he invests Rs. 15000 per month. Also, prepare a scenario to check what will be the return after 10 years on investing Rs. 12000 per month but at a rate of 15%. Assume the layout of the data. What-if Analysis with Goal Seek When we need to assess the impact on any data value due to any change in another data value, we use Goal Seek feature. In simple terms, goal seek allows us to determine the input for a desired output. For example, how many more students should have scored 70% to make class average 75%? Or, how many more five stars were needed for a web site to have its rating above 8? In goal seek, you need to specify 3 items – which cell you want changed, by which value (expected output) and which cell has the input value. To apply goal seek, go to Tools menu > Goal Seek option. Let us understand this with a small example. In the example shown here, the merchant wants to know how many sofas should be sold to achieve total commission of 20 lakh? Commision is calculated as 12% of Price * Sale Qty for both the items. Open Goal Seek tool and then do the following: ¤ In Formula cell, select the cell D4 since it contains the formula to calculate total commission. ¤ In Target value, mention 2000000 since that is the expected total commission. ¤ In Variable cell, select the cell C2 that has sale quantity to be estimated. On clicking OK, Goal Seek tool will confirm whether you want to apply the new changes on the data. You can click Yes or No as per your choice. So, 98 more sofas (total 298) need to be sold to achieve the goal Electronic Spreadsheet (Advanced) of Rs. 2000000 commission. 5110
LAB EXERCISE Ravi wants to apply the “cause and effect” on his studies. His exams are approaching and he has to prepare for 7 subjects. Each subject has 12 chapters to be prepared. He takes 4 hours to prepare one chapter. He studies 6 hours daily. He needs to know, how many hours does he need to study daily to complete the preparation of all the 7 subjects. What-if Analysis with Solver Solver is a more advanced tool than Goal Seek. It is used to find out best optimised results from the data by considering certain constraints. For example, maximizing the profits, minimising the expenses, estimating optimum budget etc. Solver analyses the values and constraints to provide the best possible solution. In order to use the Solver to solve a mathematical programming problem, you must formulate the problem as follows: • Decision variables – one or more non-negative values. • Constraints – conditions to be satisfied depending on the business requirement. • Objective function – the maximised, minimised or a fixed value to achieve after calculation. Let us understand Solver with the following scenario: A publication company publishes books in 4 subjects namely Windows 10, Windows 7, LibreOffice and MS Office. The billing of books is done on credit (no payment at the time of selling the books) and details of billing amount are recorded. Then, an estimate of expected amount to be collected is taken as 60% of the billing amount. The publisher wants to know - How much billing of each book is needed to collect expected amount of Rs. 40000000 but for each subject, the billing amount should be at least Rs. 10000000? Here, · Target cell is D8 which needs to have calculated value 40000000. · Optimisation result is the fixed value of 40000000. · The changing cells are C4:C7. · Limiting conditions for each of the billing cells is 10000000. Let us setup solver with the above values and criteria by going to Tools menu > Solver option. Ÿ Select cell D8 for Target cell field. Ÿ Enter 40000000 in Value of option of the field Optimize result to. Ÿ Select cells C4:C7 for By changing cells field. Electronic Spreadsheet (Advanced) 5111
Ÿ In Limiting Conditions section, one-by- one select cells C4, C5, C6 and C7 and for each of them select the Operator as >= and specify Values as 10000000. Finally, click on Solve button. Solver Status popup will show the calculation being done. You can click on Continue button to recalculate new figures. Once satisfied, click on OK. You can save (Keep Result button) or discard (Restore Previous button) the result in Solving Result popup. Note: To calculate values as integers you can click on Options button on Solver dialog box and select Solver engine as LibreOffice Linear Solver and select Assume variable as integer option. Solver engines Solver engines are based on following algorithms: DEPS – Evolutionary algorithm best suited for calculating closest approximate result. SCO – Evolutionary algorithm based on social cognitive optimisation techniques. In above two, a group of solutions is generated. The best solution is selected by evaluating it against the input values (changing cells) and output values (objective cell). This is used for complex calculations. LibreOffice Linear Solver - Good for optimising linear equations. LibreOffice Swarm Non-Linear Solver - Good for optimising non-linear equations. 5112 Electronic Spreadsheet (Advanced)
LAB EXERCISE Consider the sales sheet given here. The objective is to achieve the profit of 100000. You need to manipulate 3 variables: number of burgers needed to sell (C2), the price per burger (C3), the cost of one burger (C6). The constraints to consider are: 1. The number of burgers sold (C2) should be a whole number (integer). 2. The price of one burger (C3) should not exceed 65. Session-2 Referencing and Linking Sheets You have learned how to refer to the cell addresses while applying various formulae and functions in a worksheet. Calc allows referring to the cells in another worksheet or workbook from your active worksheet. Within the active worksheet, cells are referred to simply by their cell addresses but if you need to refer to a cell which is in another worksheet of the same workbook then the cell address needs to be preceded by worksheet name with an ! sign in between the worksheet name and the cell address. For example, =mysheet2!B5 * mysheet3!B5 Here, cell addresses B5 belong to the worksheets named mysheet2 and mysheet3. Remember that this approach of calculation across the sheets works where consistent formats are used by the team members and every sheet has a fixed format. Let us understand it with the following example. Consider the four sheets in a workbook here: Notice that cell B3 contains the sales figures of North region in all the sheets names JAN, FEB and MAR. To calculate the total of these 3 values, you need to mention the name of the sheet, followed by ! sign and then the cell address B3 in the formula as shown in the sheet QRT1. =JAN!B3+FEB!B3+MAR!B3 Electronic Spreadsheet (Advanced) 5113
Referring to Sheets as a Range If the worksheets are in sequence then they can be referred to as a range of sheets which includes the name of the first sheet in the range and the name of the last sheet in the range, both separated by a : Consider the sheets JAN, FEB and MAR in the previous example. The formula to calculate total sale of Quarter1 can also be calculated by the formula as shown here: =SUM(JAN:MAR!B3) Note: If sequence of first or last sheet is changed then the formula will consider only the sheets in the new range. For instance, if there are 10 sheets named from sheet1 till sheet10 and formula is: =SUM(sheet1:sheet10!B3) and if sheet10 is moved before sheet5 then the sequence of sheets would be sheet1, sheet2, sheet4, sheet10, sheet5, sheet6, sheet7, sheet8, sheet9. In this case, SUM function will refer to only the sheets sheet1, sheet2, sheet4 and sheet10. Creating references to other worksheets with mouse We can refer to the cells in the other worksheets. To do so, first go to the cell in which you need to apply the reference of a cell in another sheet and type = sign (See the example sheet named TOTAL). Then, click on the name of the desired sheet. In that sheet, click on the desired cell and press Enter key. Apply the same steps for other cells. Finally, drag with the fill handle to apply the same references to other cells. To refer to the cells in a sheet in another workbook, follow the same steps. You just need to keep the other workbook opened first. Referring to Sheets in Separate Workbooks The processes of referring to the cells of other sheets in separate workbooks is same as explained earlier except that you need to open the desire workbook, go the desired sheet and select the cell you need to refer to. The path to the workbook will be included in the cell reference. One example is shown here as in a worksheet following reference is created: Here, cell range B2:b12 of Sheet1 in workbook Ex-session 3.ods on the local path H:/ is referred to in SUM(). Here, file: is a protocol that refers to a local storage such as disk drive H. 5114 Electronic Spreadsheet (Advanced)
LAB EXERCISE 1. There are 5 sheets named Class1, Class2, Class3 and Class4. Each sheet contains average marks of English in cell A1, average marks of Hindi in cell B1 and average marks of Math in cell C1. Calculate overall average marks in a sheet Class5 of all three subjects by referring to above cell addresses using + arithmetic operator. 2. Calculate the above using SUM(). 3. Create a new worksheet Sheet1 with the names of some countries and their populations in columns A and B. In column C type the percentage rate at which their population is increasing (e.g. 5, 3, 10 etc). In Sheet 2, in column A, display the populations of those countries after 3 years. The formula for calculating this is: ((Population * Percentage Increase) / 100) * 3. Absolute and relative hyperlinks Hyperlinks connect one document with another. A piece of text or image works as hyperlink, clicking on which fetches the linked document. Imagine that a file summary.ods is on the following path: D:\\myprojects\\project20\\reports\\summaries\\summary.ods summary.ods has a text: Click for details which is hyperlinked to another file details.ods on path: D:\\myprojects\\project20\\reports\\details\\details.ods This is absolute hyperlinking wherein entire path to the linked document is mentioned. If you send reports folder to someone who copies it on his computer in drive E: so, the path to these files on his computer will be - for summary.ods: E:\\reports and for details.ods: E:\\reports\\details Can you see the problem? When he will click on the hyperlink, the application will look for D:\\myprojects\\project20\\reports\\details\\details.ods, instead of E:\\reports\\details. To prevent such situations when you need to distribute linked documents to others, relative hyperlinking is useful. Now, let us understand relative hyperlinking. Notice the common path to both the files: D:\\myprojects\\project20\\reports. This is called Base address. So, if the source file (that contains the hyperlink) and the linked file are sharing a common path, then hyperlink can be created by omitting the base (common) path. This is called relative hyperlinking. Therefore, in above example, relative hyperlink address in summary.ods will be details\\details.ods. When user clicks on a relative hyperlink, application looks for the path from the current folder onwards and not from the very beginning (root) of the path. In this case, folder details is always going to be under folder reports so, no matter where you copy folder reports on any computer the relative path is not going to change. Electronic Spreadsheet (Advanced) 5115
Setting up and inserting hyperlinks ¤ G o t o t h e To o l s m e n u > Options option. In the Options dialog box, go to Load/Save > General and check Save URLs relative to file system and Save URLs relative to internet check boxes. Click OK. ¤ To insert a hyperlink into the spreadsheet do any of these: ¤ Go to the desired cell or select the desired text. Then, Insert menu > Hyperlink option, Or ¤ Click the Insert Hyperlink button on the Standard toolbar. ¤ Drag-drop an item from the Navigator to the desired cell. ¤ Type the target web address or URL or Shortcut path to the target document at the point where you want to insert the hyperlink. To display Insert Hyperlink dialog box : Ctrl + K Hyperlink Dialog Box Ÿ Internet section: It allows to mention the web (http/https) or file (ftp) url and hyperlink text. For ftp links, you need to provide username and password to access the ftp service. Ÿ Document section: It allows to set the path to the target document or part of that document. You can also insert the mail reference to a mail recipient in Mail section or create a new hyperlinked document by going to New Document section. The hyperlink can be inserted as a form button if you select Button option in Form drop-down. Finally click on Apply or OK button. Hyperlink to the target sheet named SCENARIO in the Button Hyperlink to a URL worksheet Unit 2Examples.ods Electronic Spreadsheet (Advanced) 5116
Editing a Hyperlink To edit a hyperlink, go to the cell that contains the hyperlink and do any of the following: Ÿ Right click on the hyperlink text and select Edit Hyperlink option in the popup menu. Ÿ Insert menu > Hyperlink option. Ÿ Press Ctrl + K. Removing a Hyperlink To remove a hyperlink, right click on it and select Remove Hyperlink option in the popup menu. Opening a Hyperlink To open or follow a hyperlink, right click on it and select Open Hyperlink option in the popup menu. Or, left click with Ctrl key down. LAB EXERCISE Ankita has created a sheet for her team to be filled individually. She created the data format in Sheet2 of the workbook and mentioned the instructions in Sheet1. In cell A1 of Sheet1, she needs to create an internal hyperlink to the instructions in Sheet1. Help Ankita. Linking to External Data You can link HTML tables in web pages, csv files and data on other worksheets to another worksheet. To insert the linked data from an external worksheet, place the cursor where you need to insert the linked data and go to Sheet menu > Link to External Data option. l In the External Data dialog box, click on Browse button to locate the file which contains the desired data to be linked. The named ranges in that sheet will be listed. l Select the named ranges you want to link using Ctrl key and click OK. You can also link data range in a sheet by drag- dropping it onto the other sheet from Navigator pane. Do not forget to right click on the range name and select Drag Mode > Insert as Link option to insert the range as a link. To view a list of all external data links in the spreadsheet, select Edit menu > Links to External Files option. In Edit Links dialog box all the links are listed. You can modify, break or update the links. Electronic Spreadsheet (Advanced) 5117
Note: Use Data menu > Define Range option to name a selected data range. Linking to Registered Data Source Registered data source means any file which contains the data required to be accessed through your document or spreadsheet and your application is aware about it. Aware means, your application knows about its file type (spreadsheet, csv file, database, website etc.) and able to establish connection with it (compatibility). l Tools menu > Options. In the Options dialog box, go to LibreOffice Base > Databases. l Registered databases will be listed. l Click on New button to locate the database to be registered and click OK on Create Database Link popup. l Finally click OK in Options dialog box. Linking the registered database: Go to View menu > Data Sources option. Registered data sources will be listed in the Data Source Window. Navigate to the desired database and table in it. Select the whole table by clicking in top-left blank gray cell. Then, drag-drop the table to the sheet or click on Data to Text button in the Table Data toolbar. Update any changes in the data in datasource by selecting the data range in the sheet and using Data menu > Refresh Range option. 5118 Electronic Spreadsheet (Advanced)
LAB EXERCISE A. Open a new worksheet and insert two hyperlinks - one to a document saved on your computer and the other to a web url. B. Get a database StudentDB made by your teacher that contains a table named Student. The table should store First Name, Surname, Date of Birth, Class, Section, House and Percentage Marks. Import this data in a spreadsheet. Session-3 Share and Review Sheets Modern businesses work in a shared environment where information and resources are shared over a network. Concurrency is the main factor while sharing files on the network. It refers to the ability of the software application or operating system to allow multiple users access the same file to facilitate collaborative data sharing. In such scenario, multiple users can view the data of the file and can make edits in the same file simultaneously. The worksheet can be shared over local network as well as across the world over internet. For a local network (LAN), the spreadsheet file can be shared by saving it on a shared drive on any computer with rights given to the concerned users by network administrator to access it. Sharing of file has following major advantages: ¤ Faster data entry. ¤ Real-time data updates. ¤ Decreased data redundancy (multiple copies of same data set). ¤ Collaborative work environment. For any worksheet to be shared, first ensure that it is saved on a shared drive where it can be accessed by multiple users. If you have not saved the file then Calc will prompt to save it while you share it. To share the worksheet, go to Tools menu > Share Spreadsheet option. In the Share Document dialog box, check Share this spreadsheet with other users check box and click OK button. Electronic Spreadsheet (Advanced) 5119
When you change and save a shared worksheet, there are following possible scenarios: 1. If no other user has modified the sheet meanwhile then it will be saved. 2. If another user has modified the sheet then Calc will prompt you to choose other changes (Keep Other/ Keep All Others) or your changes (Keep Mine/Keep All Mine) in Resolve Conflict dialog box. 3. If you have already opened the sheet, the other user will be displayed the message that the file is locked for changes and he/she may save the file later. LAB EXERCISE If your lab has computers networked with each other, ask your teacher to have a worksheet copied on a shared drive which you can access then try editing the sheet at the same time along with some of your classmates. Review changes in spreadsheet Spreadsheets, like other documents, can be reviewed either before sharing them with the intended recipients or after receiving from them to review the changes made by others. We know how to review sheets by the tools such as Spelling and Grammar and Thesaurus. Now, let us see how to review sheets when they are shared among multiple users. 5120 Electronic Spreadsheet (Advanced)
Preparing a sheet before sharing When multiple users are likely to make changes in a sheet, it is a good practice to switch on the tracking of changes. This way, the changes made by anyone in the sheet are visible clearly for review by others. To do so, go to Edit menu > Track Changes > Record option. You can also go to File menu > Properties option > In Properties dialog box, Security tab > Record changes > OK. Shortcut Switch on Recording Changes: Ctrl + Shift + C Once the recording of changes in the sheet is on, any changes made in the sheet are recorded. Changed cells are indicated by red border with a red dot in top-left corner of the cell. Cells B4, B6 and C5 are shown as changed here. As you bring the mouse pointer over any changed cell, the information appears in a yellow pop-up. Reviewing Changes in the Sheet To review the changes, open the sheet. Go to Edit menu > Track Changes > Manage option. Electronic Spreadsheet (Advanced) In the Manage Changes dialog box, all the changes are listed. Select them one by one and click on Accept or Reject button. Or, to accept/ reject all changes at once click on Accept All or Reject Changes buttons respectively. 5121
Adding and Reviewing Comments Instead of making specific changes, users can also suggest changes by adding popup comments to the cells in the sheet. Calc provides two types of comments - comments on changes and general comments. Track changes comments are added to the changed cells and appear in the Manage Changes dialog box while reviewing the sheet but general changes do not. ¤ To add/edit comments on changes in the selected cell, go to Edit menu > Track Changes > Comment. A comment pop-up appears for the user to enter the comment. You can click on Next and Previous buttons to jump to other changed cells. ¤ To add general Comments, right click on the cell > Insert Comment option or go to Insert menu > Comment option. Shortcut Insert general comments: Ctrl + Alt + C General comments are indicated by a red dot in the top-right corner of the cell. Formatting Comments General comments can be formatted like text and graphics. To format the comment: ¤ Right click on the cell that contains the comment and select Show Comment option. ¤ Using buttons in Text Formatting toolbar and Drawing Object Properties toolbar comment text and comment box can be formatted. ¤ After formatting, right click on the cell and select Hide Comment option. 5122 Electronic Spreadsheet (Advanced)
Editing and Deleting Comments General comments can be edited and deleted by right clicking on the cell that contains the comment and selecting Edit Comment and Delete Comment options respectively. LAB EXERCISE Practice adding certain changes and comments in a spreadsheet using Track Changes. Then, exchange spreadsheets with your classmate. Practice accepting/rejecting changes in the sheet. Merge and Compare Sheets If there are multiple copies of same worksheet modified by multiple users then all of them need to be merged into the original sheet. Sometimes users forget to enable tracking of changes and changes made by them are not recorded. In such a case, you can compare the original document and changed document. Merging Sheets To merge the worksheets, first open the original worksheet. Then, go to Edit menu > Track Changes > Merge Document option. In the Merge With dialog box, select the other file whose data needs to be merged and click on Open button. The sheet will be merged. In the Merge With dialog box, 5123 select the other file whose data needs to be merged and click on Open button. Manage Changes dialog box will appear. Accept/Reject changes as you need and click Close. The sheet will be merged. Comparing Sheets Go to Edit menu > Track Changes > Compare Document option > In the Compare to dialog box, select the desired file > Open button. Manage Changes dialog box will appear. Accept/Reject changes as you need and click Close. Electronic Spreadsheet (Advanced)
LAB EXERCISE Create a sheet to store 5 records of your friends names with their marks in 3 subjects. Make a copy of this sheet. Make some changes in the copy. Also, delete a record so that the copy has only 4 records. Now, merge the copy to the original sheet. Discuss your observations with the teacher. Do not forget to switch on recording of changes in the original file before making its copy. Session-4 Working with Macros Macros are a great way to automate routine tasks in an application like spreadsheet. For example, Mr. Sen heads a team of 200 salesmen in various cities who send him sales data by day end daily. That is 200 records which Mr. Sen needs to sort on the basis of city, sales target and salesman name. Since he needs to do it every day, he can have a macro created for the same to execute this task automatically in a few seconds. Macros are the named set of recorded steps which can be run anytime later as and when required. Macro feature allows the user to record the steps he/she does. The steps are saved in the workbook by a unique name given by the user. User also sets a shortcut key to run the macro later. Macros usually work well with a standard, fixed layout of data. In real life, people perform a lot of routine tasks on a fixed layout of data-set. In such scenarios, macros help in saving a lot of time and effort. Macros can be created by recording the steps as you perform a task or they can be created using LibreOffice Basic programming language. Recording a Macro A macro can be recorded by running the macro recorder and performing the desired task to be automated. Macro recorder records all the steps. Once done, macro recorder is stopped and macro is saved. Enabling Macro Recorder: Go to Tools menu > Options option > In Options dialog box, LibreOffice > Advanced and select Enable macro recording (may be limited) checkbox. Then, click OK. Now, go to Tools menu > Macros > Record Macro option. Electronic Spreadsheet (Advanced) The macro will begin recording with a popup containing Stop Recording button to stop macro recording once you are done. 5124
Example: Creating a New Macro to Sort Data First, open a sheet with some data to sort. Begin macro recording by following the steps explained before. Select the data and go to Data menu > Sort option. Select the desired field on sort in the Data Sort dialog box and click OK. After sorting, click on Stop Recording button. Now follow the steps below to save the macro: ¤ When you stop the macro recording, Basic Macros dialog box opens. Macros are organised under modules and modules under libraries. By default macros are saved in Module1. ¤ You can create your own module by clicking on New Module button. ¤ In New Module pop-up, enter the relevant name of the module (e.g. Routine_Tasks) and click OK button. ¤ Mention a relevant name of the macro (e.g. Sort_BallsPlayed) in Macro Name field and click on Save button. Assigning Shortcut Key to the Macro 5125 ¤ To see and organise macros, go to Tools menu > Macros > Organise Macros > Basic option. ¤ In Basic Macros dialog box, select your macro and click on Assign button. Electronic Spreadsheet (Advanced)
¤ Here, go to Keyboard tab and in Shortcut Keys section, scroll and select a combination of shortcut keys e.g., Ctrl+Alt+Shift+B. ¤ In Category tree, expand the tree to locate and select the module name. ¤ In Functions section, select the desired macro. ¤ In Keys section, select the shortcut key displayed ¤ Click OK button. Now, when you get new data to be sorted, copy-paste it in the sheet at exact location of the older data and press the shortcut key for the macro. Data will be sorted. Alternatively, you can run the macro by pressing Run button in Basic Macros dialog box. LAB EXERCISE Assume a blank colourful, formatted layout and record the macro to create it. The fields are Serial No., Name, Address, Street, City, Pincode, State, Landline No., Mobile Phone No., WhatsApp No., Email ID, School Name, School Address, School Phone and School Email ID. After recording the macro, test it by the help of the shortcut key. Using Macros as a Function and Pass Arguments Macros can be implemented as built-in functions also. A function is an independent executable unit of code which is identified by its unique name. A function performs a specific task and returns the result of the task done. For example, SUM() returns the total of the given values. Many functions need input values to process. The values input to a function are called arguments. For example, in =SUM(A1:A10), range A1:A10 is argument. You need to have basic knowledge of Basic programming language to create functions. Here, concept is explained with a simple example. A function body begins with the keyword Function followed by its name and any arguments and ends with End Function. Creating a function: Let us create a function DA under our module created earlier by the name Routine_Macros. In this example, function DA is taking 2 arguments – basic_salary and da_perc and returns the dearness allowance calculated. ¤ Go to Tools menu > Macros > Organize Macros > Basic option. ¤ In the Basic Macros dialog box, click on the Organiser button. ¤ In the Basic Macro Organiser dialog box, select your module and click on Edit button. 5126 Electronic Spreadsheet (Advanced)
This will open up the macro program editor window. If you have recorded any macro earlier in your selected module then the code of that macro will be displayed by the name of the macro preceded by the keyword Sub. Sub denotes a sub-procedure or in simple terms, a macro. A sub block ends with keyword End Sub. Scroll down in the program editor until cursor comes to end sub. Press Enter key to take a new line. Here, we shall insert the code for our function named DA as shown here. After completing the code, close the editor’s window. Using a function: Once a function is created, it can be used as any other 5127 functions you have been using in spreadsheets. Go to any blank cell and type =DA(3500, 180) and press Enter key. You will see that function DA has returned the dearness allowance as 180% of 3500 i.e. 6300. Electronic Spreadsheet (Advanced)
Structure and working of a function: A function must have a unique, relevant, short name in a module such as DA. The function name is preceded by the keyword Function. A function block ends by the keyowords End Function. Any function arguments are mentioned after the function name within parentheses, separated by comma. E.g., Function DA(basic_salary, da_perc). These arguments hold the values passed to the function when it is called later. These values are used in the function for processing. Once all the calculations are done by the function, the result is returned by it. To return the result of the processing, the result value is assigned to the function name. Accessing Worksheets and Cells Using Macros Modern programs deal with objects. The objects have properties and methods. Properties give us some information about the object. For example, the number or text or a formula in a cell is its property. Objects also have functions which help us in manipulating their properties as we need. Functions associated with objects are called methods. For example, getValue() method is associated with cells of the worksheet and returns the number stored in the cell. Similarly, setValue(number) method takes a number and puts it into the corresponding cell. ThisComponent object: ThisComponent object of LibreOffice Basic refers to the currently opened worksheet in which macro is running. This object has method getSheets() which returns a collection of all the sheets in the workbook. Sheets object: This object refers to all the sheets in the workbook. It has method getCount() that returns the number of sheets in the workbook; method getByIndex(number) which returns the reference to the sheet at the given index. Index is zero-based that means getByIndex(0) will return reference to the first sheet. If there are n sheets in the workbook, then you can pass n-1 into the method getByIndex() to refer to the last sheet. Note: Zero-based indexing is a way to locate items in a collection. Here, first item has index 0, second has 1 and so on. So, if there are 10 items in a collection then, the index of the last item will be 9. Hence, by formula it is n - 1 where n is the total count of items in the collection. Sheets object also has another method getByName(string) which takes the name of the sheet and returns its reference. Sheet object: This object refers to the single sheet out of all the sheets in the Sheets object. It has method getCellByPosition(col, row) that returns the reference to the cell at given column number and row number. Remember that here also, the column position and row numbers are zero-based indices. For example, ThisComponent.Sheets(1).getCellByPosition(0,0) This is referring to first column, first row i.e. cell A1 in the second sheet (Sheets(1)) in the current workbook. 5128 Electronic Spreadsheet (Advanced)
You can also access a cell range by using the method getCellRangeByName(“starting cell:ending cell”) which returns the reference to the given cell range. For example, ThisComponent.Sheets(1).getCellRangeByName(“A1:B5”) Cell Range object: This object refers to a cell range. It has method getData() that returns all the rows in a range. Cell object: This object refers to a particular cell in a sheet. It has methods getValue() to retrieve a number in it; getString() to retrieve text stored in it and getFormula() that returns any formula stored in the cell. Functions LBound() and Ubound(): These functions return the index position of the first item and index of last item in a collection respectively. For example, LBound(Sheets) will return the position of the first sheet in a collection of sheets while UBound(Sheets) will return the index position of the last sheet. Example 1 Here, object ThisComponent is storing reference to the current sheet in user defined object workbook. It has the collection Sheets which is taken into user defined object sheets. Its index 0 is mentioned to access the first sheet i.e. Sheet1. It is taken into user defined object sheet. Now, on this sheet object, method getCellByPosition(0,1) is called to access first column i.e. A and second row i.e. 2 (A2). Reference to cell A2 is created in user defined object cell. On this object, method getString() is called because this cell contains text value i.e. APPLE. Method getString() returns string APPLE in user defined variable cell_value. Finally, a function MsgBox(cell_value) is used to display it in a popup message box. Example 2 Here, object ThisComponent.Sheet(0).getCellRangeByName(“B2:B6”) returns cell range B2:B6 (quantities of all fruits) in Sheet1 in the current workbook. Note how reference opererator . (dot) is used to access the objects within an object. Electronic Spreadsheet (Advanced) 5129
The returned cell range is stored in user defined reference range. Its method getData() returns the rows in the range i.e. rows 2, 3, 4, 5 and 6. These rows are stored in user defined collection rows. In collection rows, the rows are arranged on zero-based index i.e. row 2 is at index 0, row 3 at index 1 and so on. This range has only one column so the column index is 0. Thus, first cell in the range can be accessed as rows(0)(0), second cell as rows(1)(0) and so on. Here, first index is row and second index is column. So, all the values are accessed and their sum is stored in user defined variable total. Its value is 1319. Next, ThisComponent.Sheet(0).getCellByPosition(1,6) returns the cell B7 (column B at index 1 and row 7 at index 6) into user defined reference cell. Finally, cell.setValue(total) is storing the value of variable total i.e. 1319 in the cell B7. For Loop Loop is a way to execute program statements muliple times as long as we need. When all the statements within a loop are executed, it runs again and again as long as a condition allows or on the basis of a counter. For loop is one such loop that runs on the basis of available counts. It is denoted by For - Next keywords. With For, a counter variable is associated which is set to count from a lower value to a higher value using the keyword to. Keyword Next increases the value of counter variable by 1 by default (or, mention the numbers to skip. E.g., Next 3). The syntax of For loop is: For counter_variable = start value to end value statement statement statement statement Next 5130 Electronic Spreadsheet (Advanced)
As shown in the example function for_demo(). Here, loop counter is variable x which is set to start value 0 till the end value 10. This means, the loop will run 11 times. This loop has two statements which will execute 11 times. In first iteration of the loop, statement cell = ThisComponent.Sheets(0).getCellByPosition(5,x) will access cell F1 (i.e. 5, 0) since value of variable x is 0. Second statement cell.setValue(x) will store 0 in cell F1. Next, the value of variable x will be 1. So, its value 1 will be stored in cell F2, then 2 in cell F3 and so on. Finally, cell F11 will have value 10 and the loop will terminate. Example 2 revised with For Loop Notice lines 5, 6 and 7. In line 5 , LBound(rows) returns 0 and UBound(rows) returns the index of last cell in the range i.e. 4. So, the loop will run 5 times (0 to 4). In line 6, rows(x)(0) refers to indices 0,0. Then, in next iteration 1,0 and so on. This way, one by one, variable total will accumulate sum of all the 5 values. When the loop is over, finally, the value of variable total is displayed in cell B7. 5 rows in cell range B2:B6 returned by getData() Note: With for loop, Step keyword can be used to specify a number by which the loop counter should skip. E.g. For x = 1 to 5 Step 2 means x will count 1, 3 and 5 (skipping by 2). Sorting the columns using macro Let us now learn how to create a macro code to sort the data on the basis of multiple columns. Consider the dataset shown here of Classes and Sections which is not sorted. Let us create a macro to sort the column Class in ascending order and within it the column Section in descending order. Column SNo will remain unchanged obviously. Electronic Spreadsheet (Advanced) 5131
¤ Go to Tools menu > Macros > Organize Macros > Basic option. ¤ Select the module under which you need to create macro and click on Edit button. ¤ Type the following code in the macro editor. LibreOffice Basic language provides two main objects that help in sorting: SortField object: This object has property Field to specify the field on which the sorting should be done in the selected range. Field 0 means first column in the selected range and so on. For sorting on multiple fields, we need to create an array of this object. This object is defined in a package util which is part of package star under package sun under com. See how it is accessed in line 3. New keyword is used to create an object of the specified type. Dim keyword is used to declare a variable or object. So, here, array of 2 objects of SortField by the name sortfields(1) is created. This array has 2 elements - element 0 and element 1 - one for each of the two fields on which we have to do sorting. Element 0 will be used for column Class and element 1 for column Section. PropertyValue object: This object just needs the reference of SortFields object to apply sorting. See line 4 how a new array sortdesc(0) of type PropertyValue is created. Let us now understand rest of the program. Lines 6 and 7 are familiar. They are used to create an object range_to_sort which refers to the range B2:C13 in the sheet named Data1. In Line 8, method select() is passed with the range object range_to_sort to select the range in the sheet. 5132 Electronic Spreadsheet (Advanced)
In Lines 10 and 11, properties Field and SortAscending of element 0 of array sortfields are being set to 0 and TRUE respectively. Value of property Field is 0 that refers to the first column in the selected range i.e. Class. Property SortAscending is boolean type that can be either set to TRUE or FALSE - TRUE means sort in ascending order otherwise descending. So, lines 10 and 11 means sort on column Class in ascending order. Similarly, in lines 12 and 13, element 1 refers to the second column of the selected range i.e. Section and sorting order is set to descending by setting property SortAscending to FALSE. So, lines 12 and 13 means sort on column Section in descending order. Now, the specified sorting settings need to be loaded into object sortdesc of type PropertyValue. Line 15 has property Name of sortdesc(0) is set to a string “SortFields”. So, lines 10 and 11 means sort on column Class in ascending order. In line 18, finally, the sorting is applied by passing object array sortdesc() into the method Sort of range object range_to_sort as argument. LAB EXERCISE 5133 1. Write a macro function SortNames() to sort the names of 10 cities and their population in descending order of cities and ascending order of population. 2. Consider the given data set. Write macro CalcAvg() to calculate average marks for each subject in correspoinding coloured cells. Electronic Spreadsheet (Advanced)
A. Select the correct option to answer the following questions – 1 mark each. 1. Ravi has Science marks of students of class IX and X. He wants to calculate the average marks class-wise. Which of the following is best suited for this? a. Goal seek b. Solver c. Sub-total d. User defined formula 2. Varun has got marks of 500 students of Term1 and Term 2 of class X in separate sheets. He needs to create a third sheet that contains total of Term1 and Term2 of all the students. Which of the following is best suited for this? a. Goal seek b. Sheet linking c. Data consolidation d. Scenario 3. Which of the following is not a “what-if ” analysis tool in a spreadsheet? a. Goal seek b. Sub-total c. Solver d. Scenario 4. Applying a set of values on a data set to analyse changes in the calculations and result is achieved by which of the following tools? a. Goal seek b. Sub-total c. Solver d. Scenario 5. “How many months would it take to pay the loan is monthly instalment paid is increased by 25%?” – this question can be answered most suitably by which of the following tools? a. Goal seek b. Sub-total c. Solver d. Scenario 6. “What should be the maximum performance of the class if all the students appear for the exam and all of them secure at least 65%?” – this can be answered by which of the following tools? a. Goal seek b. Sub-total c. Solver d. Scenario 7. In the formula: rep1:A3 * rep5*A5 + D5, the cell D5 belongs to which of the following? a. Different workbook b. Current sheet c. Different sheet d. Current workbook 8. To keep the hyperlinks intact while distributing documents to others, which of the following must be used? a. Absolute hyperlinking b. Relative hyperlinking c. Both a) and b) d. None of these 9. An application knows how to establish connection with a ______________ data source a. Registered b. Installed c. Embedded d. All of these 5134 Electronic Spreadsheet (Advanced)
10. To decrease data redundancy and collaborative editing of a document, the best way of the following is? a. Relative hyperlinking b. Sharing document c. Review document d. Comments in the document 11. If multiple users are using the same sheet, then which of the following is a good practice to? a. Protect sheet b. Encrypt sheet c. Apply track changes d. All of these 12. Which of the following can be deleted and edited any time? a. General comments b. Comments on changes c. Both a) and b) d. None of these 13. While editing a shared sheet, if tracking of changes is not on, then which of the following is useful to examine which changes have been done? a. Merge sheets b. Review sheets c. Compare sheets d. Any of these 14. A set of macros is called which of the following? a. Module b. Library c. Program d. Function 15. A set of modules is called which of the following? a. Module b. Library c. Program d. Function 16. An independent executable unit of code having a unique name is called ____? a. Function b. Program c. Module d. Library 17. Macros can be implemented by using which of the following keywords? a. Sub b. Function c. Both a) and b) d. None of these 18. To return value 10 from a function myfunction, which of the following statements is correct? a. myfunction(10) b. myfunction = 10 c. 10 = myfunction() d. myfunction 10 19. If certain values are stored in a collection, then the position of the first value in that collection will be denoted by which of the following? a. 1 b.0 c. total number of values - 1 d. None of hese 20. If certain values are stored in a collection, then the position of the last value in that collection will be denoted by which of the following? a. 1 b. 0 c. total number of values - 1 d. None of these 21. Which of the following is the correct sequence of containership. a. workbook.sheet.cell.range b. workbook.sheet.range.cell c. sheet.workbook.range.cell d. sheet.workbook.cell.range 22. In the statement: for z = 10 to 100, z is which of the following? a. Loop counter b. Variable c. Both a) and b) d. None of these 23. Which of the following keywords creates a new instance of any object? a. Dim b. New c. Var d. None of these Electronic Spreadsheet (Advanced) 5135
B. Answer the following questions – 2 marks each. 1.What is the significance of sub-total feature in a spreadsheet? 2. Briefly describe a scenario/example where data consolidation is useful. 3. Anu has two sets of 10 marks each which she wants to apply on a dataset to analyse the output to improve students' performance in next exam. Write steps to help Anu apply these 2 sets of scenarios one by one. 4. What do you mean by Formula cell, Target value and Variable cell in Goal Seek? 5. How can you say that Solver is an advanced form of Goal Seek tool? 6. Why do we need the data sorted before applying sub-total feature on it? 7. How is Scenario feature different from Goal-seek? Explain with a small example each. 8. Ajit has 3 sets of sales data which he wants to apply on the actual data to see how much earning his company will do in the end of the month. Which “what-if ” analysis feature will be suitable for him and why? 9. Ajit wants to know how much savings are needed to be done in current month to keep his expenses at Rs. 100000 by month end. Which “what-if ” analysis feature will be suitable for him and why? 10. How is relative path different from absolute path? 11. What is the advantage of relative hyperlinking? 12. In a formula, how will you refer to a cell B5 in a sheet named mysheet1? 13. In a formula, how will you refer to a cell B5 in a sheet named mysheet1 in a workbook named myworkbook? 14. What do you mean by registered data source? 15. Why do we need to share documents? 16. What is the advantage of tracking changes in a shared document? 17. What actions can you take on the changes done by other users in a shared document? 18. What is the basic difference between track changes comments and general comments? 19. How is a function different from a sub-procedure? 20. How does a function return a value? Give example. 21. What is function argument? Give example. 22. Write the statement to store value 10 in cell A1. 23. Write a macro to copy value of one cell to another. 24. Implement a loop to display values 1 to 20 in reverse. C. Answer the following questions – 3 marks each. 1. With a small example, explain how Solver is a better tool than Goal Seek in certain scenarios. 2. What is the advantage of data consolidation? 3. Describe the syntax of referring to a cell in another worksheet. 4. What do you mean by base path in hyperlinking? What is the limitation of absolute linking? 5. How is relative linking a better way than absolute linking? 5136 Electronic Spreadsheet (Advanced)
6. Roma has saved Index.doc on path D:\\my stories\\fiction\\2020. Index.doc contains relative hyperlinks, one for each of the chapter in the story. All the chapters are in separate files named as Chapter_x.doc, where x is the number of chapter. All the chapters are on path D:\\my stories\\fiction\\2020\\pdf\\draft. Which folder should she send to the publisher so that all the links work fine on publisher's computer too and why? 7. How will you link a worksheet to a defined name in another sheet in the same workbook? 8. What are the advantages of sharing a file over a cloud location? 9. Atul has 4 worksheets of data from different branches of his company. All have identical layout. He needs to merge them into one sheet, How will he do that? 10. What is the benefit of macros? How will you create and run a macro in an spreadsheet application? D. Answer the following questions – 5 marks each. 1. Briefly describe the different ways of what-if analysis using a spreadsheet. 2. How is data consolidation different from sheet comparison? 3. Describe a scenario where Solver is the best choice to apply. 4. How will you ensure that documents hyperlinked with the sheet are linked relatively? 5. Manoj has details of employees in a table Employee_MasteR in a database named CompanyDB.odb. How will he link the data in his worksheet? 6. Write a brief note on various types of comment in spreadsheet. 7. Anuj has a worksheet in a particular format. A copy of same sheet is with his assistant. They both made certain changes in their copies. How will Anuj keep his changes as well as accommodate assistant's changes also in his sheet? Write detailed steps. 8. Consider this sheet named Employee and write macro to sort the data on the basis of department in ascending order. 9. Referring to the sheet Employee, write macro to sort data on department in ascending order and basic salary in descending order under it. 10. Referring to the sheet Employee, write a function to return average of all basic salary values. 11. A worksheet has some values with font colour blue and cell fill colour yellow. Write steps to create a macro to copy-paste only the values of these cells to another location in the same sheet. (Hint: Use paste-special while pasting the data). Watch & Learn www.eduitspl.com www.youtube.com/edusoftknowledgeverse Electronic Spreadsheet (Advanced) 5137
Unit Database 3 Management System Session-1 Database Concepts Storing data and retrieving information has been a necessity of all businesses. Data can be anything such as, name of a person, a number, images, sound, etc. For example, Ravi, 4, student, etc. When the data is processed and converted into a meaningful and useful form, it is known as information. For example, Ravi is 4 years old and he is a student. is information. Earlier, the data was stored manually in the form of files, books and ledgers. Storing data and retrieving information from them is a time-consuming task. With computers, this problem has been resolved. Computers have replaced paper, files and ledgers as the principal media for storing important information. Computers help manage and organize the data so that useful information can be accessed easily and efficiently. A database is a collection of related data from which users can efficiently retrieve the desired information. A database can be a simple collection of text data in a simple tabular form to a complex collection of images, audio or video. DBMS is a collection of programs that enables users to create, maintain database and control all the access to the database. The primary goal of the DBMS is to provide an environment that is both convenient and efficient for user to retrieve and store information. The basic concepts behind a DBMS are: a) Specification of data types, structures and constraints to be considered. b) Storing the data and manipulating it as required. c) Querying the database to retrieve desired data. d) Updating the content of the database. Need for Database Management System Database approach came into existence due to the drawbacks and limitations of file processing system. In file processing systems; updating, deleting and maintaining the data is difficult. A number of application programs are written by programmers to maintain the data. In the database 5138 Database Management System
approach, the data is stored at a central location and is shared among multiple users. Thus, the main advantage of DBMS is centralization of data. Advantages of Database Management System a) The ability to update and retrieve data: This is the fundamental feature of a DBMS. Without the ability to view or manipulate data, there would be no point to using a database system. Updating data in a database includes adding new records, deleting existing records and changing information within a record. b) Support Concurrent Updates: Concurrent updates occur when multiple users make updates to the database simultaneously. Supporting concurrent updates is also crucial to database management as this ensures that updates are made correctly. Otherwise it would lead to the loss of important data and/or inaccurate data stored. c) Recovery of Data: DBMS provides ways to backup and recover database. There are times computers may crash, a fire or other natural disaster may occur, or a user may enter incorrect information invalidating or making records inconsistent. d) Security: To prevent unauthorized access to the database, DBMS uses features like encryption, authentication, authorization and views to provide security to the database. e) Data Integrity: Data integrity is a set of rules that DBMS provides to see that data incorrect or inconsistent data is not stored. f) Controlled data redundancy: During database design, various files are integrated and each logical data item is stored at central location. This removes replicating the data item in different files, and ensures consistency and saves the storage space. g) Data sharing: The data stored in the database can be shared among multiple users or application programs. Due to shared data, it is possible to integrate new applications without having to create any additional data or with minimal modification. h) Ease of application development: The application programmer needs to develop the application programs according to the users' needs. The other issues like concurrent access, security, data integrity, etc., are handled by the DBMS itself. This makes the application development an easier task. Application Areas of Database System Database systems support businesses of almost every nature now a days. Some of the most common database applications are listed here. Travel Industry: Airlines, railways, hotels and cab industry use online databases for reservation, room bookings and for displaying the schedule information. Banking & Finance: Databases are used for storing information such as sales, purchases of stocks and bonds or data useful for online trading, customers, accounts, loans, and other transactions. Education: Schools and colleges use databases for course registration, result, and other information. E-commerce: Integration of heterogeneous information sources (for example, catalogues) for business activity such as online shopping, booking of holiday package, consulting a doctor, etc. Health Care Industry: Databases are used for maintaining the patient health care details. Digital libraries & publishing: Databases manage bulk text & multimedia data. Human resources: Organizations use databases for storing information about their employees, salaries, benefits, taxes, and for generating salary cheques. Database Management System 5139
Components of Database Management System User: Any person who can access the data by using a DBMS is called database user. Database Administrator: A database administrator is a person who directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing and maintaining the database system, and providing security & training to employees in database management system. Application Program: An application program is any program designed to perform a specific function directly for the user or, in some cases, for another application program or operating system. Relational Data Model (RDM) The relational data model is both flexible and natural. This model depicts the storage of data in symmetrical layouts of tables. tables are the simplest form of organised data. A table stores the data in rows. Details about any object or event constitute one row. Each data value in a row is identified a unique label or name. This way, similar data values are found in a single vertical column called field with a unique name. More than one table together store the data about entire enterprise or business. These tables are called relations hence the term relational data model. To locate complete information from the tables, they are linked with each other through common fields. Properties of relational data model: Types of Database Databases are essential for record keeping. You can set up databases with database software packages, or even spreadsheet applications. Either way, both include options for database tables. Establishing database tables are a big part of database design, and the number of database tables is the main contrast between flat file and relational databases. 1. Flat File Database: Flat file is a database with one database table. One single database table can include all the database's fields. A single table flat file database can be okay if the database is only a small one with a few fields. However, some require much larger and more extensive databases with lots of fields. With lots of multiple fields, some record details can then become duplicated within a single database table. As such, flat file databases are not ideal for larger database models. 2. Relational Database: Relational databases are more widespread than the flat file database alternatives. Relational databases break up the single table of flat file database mode. Relational databases are based on relational data model explained earlier. Links between the tables are established with primary and foreign keys which are unique identifiers for the database tables. These links between the database tables are known as relationships and can be one-to-one, many-to-one or one-to-many. We shall learn about primary and foreign keys in coming sessions. 5140 Database Management System
Database Servers Database servers are dedicated computers that hold the actual databases and run onlythe DBMS and related software. Typically databases available on the database servers are accessed through command line or graphic user interface tools referred to as Frontends; database servers are referred to as Back-ends. Such type of data access is referred to as a client-server model. LAB EXERCISE 1. Your school library runs on the database of books. Try to find out how your librarian uses that database. 2. Design the structure of a table BOOK with fields namely: Book_No, Book_Title, Author. 3. Design another table BOOK_ISSUE with the fields namely: Book_No, Student_Registration_No, Issue_Date, Return_Date. 4. Decide the primary key and foreign key in the above tables and mention the reasons. Why? Session-2 Data Storage Data in a relational database management system (RDBMS) is organized in the form of tables. You will now quickly recap what you learnt in the last session and assimilate more concepts. Table A Table is a collection of data related to a single topic and a database is a collection of tables. A table organizes the information about a single topic into rows and columns. Parts of a Table a. Columns: The columns of a table are also called attributes. The column is the vertical part of a table. For example, table STUDENT given below has 6 columns. b. Rows: This is the horizontal part of the table. One row represents one record of the table. The row of a table is also called tuple. For example, table STUDENT given below has 8 tuples or rows. c. Cell: Cell is a small rectangular box that contains a value in the table. It is an intersection point of row and column. d. Degree: The number of attributes (columns) in a table is called the degree of the table. For example, table STUDENT given below has degree 6 (number of columns) columns. Database Management System 5141
e. Cardinality: The number of rows in the table is called its cardinality. For example, table STUDENT given below has cardinality 8 (number of rows). Query The purpose of a database is not only to store and accumulate information, but also to ensure information retrieval according to specific criteria. A query can be used for selection of information in one or several tables. The result of a query can be displayed in data table, form and report views; it is possible to specify the table fields to be included. Form In databases, it is possible to use forms for the creation, display and modification of records. Data entry in a form is visually easier to understand than in a table, and is simpler for the user. Forms may contain fields for data entry, field descriptions (names), action buttons, menus, pictures, and design elements. Report Reports are formal presentation of data in a particular design and layout. Reports are created using query or table data. Creating database reports is ideal when you want to: ¤ Reports help in analysing data and take decisions for future plans. ¤ Provide hard-copy printouts for review at a meeting. ¤ Include data from your site in a printed publication. ¤ Mark up the information in your database that needs updating. 5142 Database Management System
Data types Data types are used to identify which type of data (value) we are going to store in the database. Fields themselves can be of different types depending on the data they contain. Data types in Open Office base is broadly classified into five categories listed below. • Numeric Types. • Alphanumeric Types. • Binary Types. • Date time. • Other Variable Types. Numeric Types Numeric data types are used for describing numeric values for the field used in the table of a database. Numeric data types in a database can be using for storing information such as mobile number, roll number, door number, year of school admission, true or false statements, statistical values, etc. The different types of numeric data types available are listed here. ¤ BOOLEAN (yes / no): Values as 0 or 1. Example: True or False, Yes or No. ¤ TINYINT (tiny integer): Integer range between 0 and 255 ¤ SMALLINT (small integer): Integer range between -215 to +215-1 ¤ INTEGER: Integer range between -231 and + 231-1 ¤ BIGINT (big integer): Range between -263 to + 263-1 ¤ NUMERIC: Maximum precision of e(+/-)231 ¤ DECIMAL: Maximum precision of e(+/-)231 ¤ REAL: 2-1074 to (2-2-52)* 21023 ¤ FLOAT: 2-1074 to (2-2-52)* 21023 ¤ DOUBLE: 2-1074 to (2-2-52)* 21023 Alphanumeric Types ¤ LONGVARCHAR or memo: Stores up to the max length or number indicated by user. It accepts any UTF 8 character. ¤ CHAR text (fix): Stores exactly the length specified by user. Pads with trailing spaces for shorter strings. Accepts any UTF 8 Character. ¤ VARCHAR (text): Stores up to the specified length. No padding (Same as long var char). ¤ VARCHAR_IGNORECASE (text): Stores up the specified length. Comparisons are not case sensitive but stores capitals as you type them. Binary Types Binary data types are used for storing data in binary formats. Binary data types in a database can be using for storing photos, music files, etc. In general, files of any format can be stored using the binary data type. The different types of binary data types available are listed here. ¤ LONGVARBINARY (image): Stores any array of bytes (images, sounds, etc.). No validation required. ¤ BINARY (fix): Stores any array of bytes. No validation required. ¤ VARBINARY: Stores any array of bytes. No validation required. Database Management System 5143
Date time Date time data types are used for describing date and time values for the field used in the table of a database. Date time data types in a database can be using for storing information such as date of birth, date of admission, date of product sale, etc. The different types of date time data types available are listed here. ¤ Date stores month, day and year information 1/1/99 to 1/1/9999. ¤ Time stores hour, minute and second info Seconds since 1/1/1970. ¤ Timestamp stores date and time information. Database Keys Each table has a set of attributes and values of each attribute are different from each other in the form of data types. Each table has a number of rows that represent to a single record. So how do databases keep all of these records straight? It's through the use of keys. Primary Key Primary key uniquely identifies each record in the table. Primary key consists of a single attribute or multiple attributes in a combination. Properties of Primary Key a) It must have a non-null value for each instance of the entity. b) The value must be unique for each instance of the entity. c) The value must not change or become null during the life of each entity. Foreign Key The foreign key refers to a referencing key present in child table to a matching value in a column in another table (parent). It cannot contain the values other than that column. It can contain the duplicate values also. Candidate Key In some instances, an entity will have more than one attribute that can serve as a primary key. Any key or minimum set of keys that could be a primary key is called a candidate key. Once candidate keys are identified, choose one and only one primary key for each entity. Let's assume that for each employee in an organization there are three candidate keys: Employee's ID, Social Security Number and Insurance Policy No. So you can choose only one column as a primary key. Properties of Candidate Key are same as of Primary Key. 5144 Database Management System
Alternate Key The Candidate Key, which is not chosen as a Primary Key is known as Alternate Key. Let's assume that for each employee in an organization there are three candidate keys: “Employee's ID”, “Social Security Number” and “Insurance Policy No. that serve as unique identifiers in the given table. If for example “Emp_Id” was chosen as Primary Key then “Social_Security_No” and “Insurance_Policy_No” would become the alternate key. Combination of “Emp_Id”, “Social_Security_No” and “Insurance_Policy_No” is known as Candidate Key. Composite primary key There are certain cases where a single field may not qualify to become a primary key then unique combination of more than one key can be considered as a primary key. Such fields which together function as primary key are called composite primary key fields. LAB EXERCISE 1. Go to a grocery shop in your neighbourhood and analyse that if you create a table to store the details of all the items in that shop then what will be the data types of each field in the table. Justify your choice of data type. 2. Which type of data should be suitable for the fields Phone_Number and Pin_Code? Justify your answer. 3. Why does database have a variety of data types? 4. How is a form different from a report? Give example. 5. Why table is the most important object of a database? 6. List 2 basic differences between primary key and foreign key. Session-3 Manipulating Data Database applications provide graphical interface to work with the database and manage the data. Besides this, they provide a common high level language called structured query language to work with the database. Structured Query Language Structured Query Language (SQL) [pro: sequel] is the industry standard to work with databases and usually follows the similar basic syntax for easier operation of all kinds of database applications. SQL allows working with a relational database in two aspects - defining and modify the structure of the database and manipulating and managing the data stored in the database. The part of SQL that lets us define and modify the structure of the database is called Data Definition Language and the part that allows manipulating and managing the data is called Data Manipulation Language. Data Definition Language (DDL) Data definition language or data description language (DDL) includes database commands that define, modify and remove the structure of various objects like tables, indexes, users, etc. in a database. Common DDL statements are CREATE, ALTER, and DROP. Database Management System 5145
Data Manipulation Language (DML) Data manipulation language (DML) includes the commands that allows to access and manipulate data in a database in the following ways: ¤ Retrieval of information from the database- SELECT statement. ¤ Insertion of new information into the database - INSERT statementDeletion of information in the database - DELETE statement. ¤ Modification of information in the database - UPDATE statement. Session-4 Creating a Database Object There are several popular RDBMS applications like Microsoft Access, OpenOffice Base, LibreOffice Base, IBM DB2 etc. Here, we shall use LibreOffice Base open source database application. Opening LibreOffice 1. Start > All Programs > LibreOffice. OR Click on the desktop shortcut of LibreOffice. Creating a Database To open LibreOffice Base, do any of the following: ¤ Start > All Programs > LibreOffice > LibreOffice Base. ¤ Open LibreOffice and select Base Database from the side bar. ¤ Open LibreOffice > File menu > New > Database option. 2. In the pop-up window, click on Database. 3. In the Database Wizard dialog box, select Create a new database option. 5146 Database Management System
4. Click Next. 5. Keep the option to Yes, register the database for me and Open the database for editing selected. 6. Click Finish. 7. In the Save As dialog box, select the desired location for the database and enter the name of the database file. Note that, LibreOffice database file has the extension ODB. 8. Click on Save button. LAB EXERCISE Create a database in LibreOffice Base by the name Library. Session-5 Creating and Working with Tables Table is the primary object of a database, which is used to store data in tabular format. While creating a table you need to decide the field or column names you need and their data types. Master Table What is a master table? A master table contains the main and primary data about any object in any enterprise or any business. Such table is not modified after every transaction. For example, a table containing details like codes, names, description, type and unit prices of items. Master table ideally contains a primary key field. Master table is also referred to as parent table. Transaction Table A transaction table contains the details of every transaction that occurs in any enterprise or business. For example, daily sales details like Sale Date and quantity sold for each item that is sold. Transaction table usually has a field common with the primary key field of Master table. Such field logically relates transaction table to the master table. Such field in transaction table is called Foreign Key. Transaction table is also referred to as child table. Database Management System 5147
Transaction Table : Sales Master Table : Item Working with Tables After creating the database, you will see the database interface which lists the four database object types – Tables, Queries, Forms and Reports in the Database pane. Click on the category Tables. The Tasks pane will list the choices to create a new table. Table Design View Design View provides full control to the user to create or design a database object by defining every small detail. For example, Table Design View allows you to mention each field you need to keep in the table and define the type of data it should store along with various constraints on the fields which ensures valid data storage in the field. You can specify which field should be primary key field. Click on Create Table in Design View… option in the Tasks pane. As you chose to create a table using Design View, the Design View window opens up. Under Field Name, specify the names of the fields that make the table. For each field, define the data type under Field Type. Description is optional and is for documentation or reference purpose. Before designing a table, the basic requirements regarding that table should be determined. Let us create table Item considering following structure requirements: Field Name Field Type Maximum Decimal Other Size or Places Requirements Length ITEM_CODE Text of variable 4 None Primary Key ITEM_NAME number of 100 None ITEM_SIZE characters 5 None ITEM_TYPE 100 None PRICE Text of variable 6 2 number of characters Text of variable number of characters Text of variable number of characters Text of variable number of characters As shown here, one by one, type the name of the field, select the field type. For currently selected field, properties of that field are displayed in the Field Properties pane at the bottom. Some common field properties are as below: 5148 Database Management System
Entry required: If Yes, it means that data entry in this field is mandatory. Such fields cannot be left blank. For example, in the Item table above, all fields are mandatory for data entry. Length: Refers to maximum number of letters allowed in a text field. For numeric field, the length refers to the number of digits entered. E.g., 6 means values from 0 to 999999. Default value: A value can be defined in this property which will be taken if user does not enter any value. For example, in the garments shop, mostly the size is 32. So, a default value of 32 can be set for the field that is going to store the size (E.g., Item_Size). Decimal places: Number of places after decimal for numeric and monitory values. Format example: Specifies formats of dates, time, currencies etc. You will learn about them in a later session. The field property settings for each field are given below: Field Name Property Settings ITEM_CODE ITEM_NAME ITEM_SIZE ITEM_TYPE PRICE 5149 Database Management System
Search
Read the Text Version
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
