10th IT 402 Main Book/ Final Revision Notes
UNIT-2: ELECTRONIC SPREADSHEET (ADVANCED)
SESSION 1: ANALYZE DATA USING SCENARIOS AND GOAL SEEK
1) Data Consolidation allows you to gather together your data from separate
worksheets into a master worksheet.
2) Data menu à Consolidate
3) the Data
Consolidation function takes data from a series of
worksheets or workbooks and summaries it into a single
worksheet that you can update easily.
4) SUBTOTAL is a function listed under the Mathematical category SUBTOTAL,
totals/adds data arranged in an array when you use the Function
Wizard (Insert > Function).
5) Because of SUBTOTAL usefulness, the function
has a graphical interface.
6) Data menu à SUBTOTAL — that is, a group of cells with labels for columns and/or rows.
Using the Subtotals dialog, you can select arrays,
and then choose a statistical function to apply to them. For efficiency, you can choose up to three groups of arrays
to which to apply a function. When you click OK, Calc adds subtotals and grand
totals to the selected arrays, using the Result and
Result2 cell styles for them.
7) Tools-> Options-> OpenOffice.org Calc-> Sort Lists.
8) Using “What If” Scenarios, and
can be edited and formatted separately.
9) When you print the spreadsheet, only the content of the
currently active scenario is printed.
10) A scenario is essentially a saved set
of cell values for your calculations.
11) Scenarios
are a tool to test “what-if” questions.
12) You can easily switch between these sets using
the Navigator or a drop-down list which can be shown
beside the changing cells.
13) Tools > Scenarios.
14) Using Goal Seek: Usually, you run a formula to calculate a
result based upon existing values. By contrast, using Goal Seek option
under Tools menu,
you can discover what values will produce the result that you want.
15) Only one argument can be altered in a single
goal seek.
16) Solver option under Tools
menu amounts to a more elaborate form of Goal Seek.
17) The difference is that the Solver deals with equations with multiple
unknown variables.
18) Tools -> Solver.
19) Solver option is specifically designed to minimize or maximize the result
according to a set of rules that you define.
20) Once you have finished setting up the rules in solver
option, you can adjust the argument and the results by clicking the
Solve button.
SESSION 2: LINK DATA AND SPREADSHEETS USING MULTIPLE
WORKBOOKS AND LINKING CELLS
21) Spreadsheet also allows you to link the cells
from various worksheets and from various other spreadsheets to summarize
data from several sources.
22) you can create formulas that span different
sources and make calculations using a combination of local and linked
information.
23) Multiple sheets help keep information organized.
24) When you open a new
spreadsheet, by default, it has a sheet named Sheet1 which
is managed using tabs at the bottom of the spreadsheet,
25) There are several ways to insert a new sheet:
a. · Select Insert > Sheet
from the menu bar, or
b. · Right-click on the tab
and select Insert Sheet, or
c. · Click in an empty space at
the end of the line of sheet tabs.
26) On the Insert Sheet dialog, you can also add a sheet from a
different spreadsheet file (for example, another Calc or Excel spreadsheet),
by choosing the From file option.
27) : For a shortcut to inserting a sheet from another file,
choose Insert > Sheet. The Insert Sheet dialog opens
with the From file option preselected, and then the Insert
dialog opens on top of it.
28) Renaming Worksheets: At the bottom
of each worksheet window is a small tab that indicates the name of the
worksheets in the workbook. These names (Sheet1, Sheet2, Sheet3,
and so on) are not very descriptive; you might want to rename
your worksheets to reflect what they contain.
29) There are three ways you can rename a worksheet:
a. • Double-click on one of the existing worksheet
names.
b. • Right-click on an existing worksheet name,
then choose Rename from the resulting Context menu.
c. • Select the worksheet you want to rename (click on the worksheet
tab) and Format menu à Sheet. This displays a submenu from which you should select the Rename
option.
30) A cell reference refers to a cell or a range
of cells on a worksheet and can be used to find the values or data
that you want formula
to calculate.
31) There are two ways to reference cells in other sheets:
by entering the formula directly using the keyboard or by using
the mouse.
32) The reference has three parts to it: (Referencing with other workbook)
· Path and file name · Sheet name · Cell
you can see the general format for the reference is =’file:///Path &File
Name’#$SheetName.CellName
33) Hyperlinks can be used in Calc to jump to a different location
from within a spreadsheet and can lead to other parts of the current file, to
different files or even to web sites.
34) Hyperlinks can be stored within your file as either relative or
absolute.
35) An absolute
link will stop working only if the target
is moved. A relative link will stop
working only if the start and target locations change
relative to each other.
36) if you have two spreadsheets in the same folder linked to each other
and you move the entire folder to a new location, a relative hyperlink
will not break.
37) To change the way that OOo saves the hyperlinks in your file, select
Tools > Options > Load/Save > General
38)
You can insert and modify links
using the Hyperlink dialog. Insert > Hyperlink
a. Internet: the hyperlink points
to a web address, normally starting with http://
b. · Mail & News: the hyperlink opens an email message that is pre-addressed to a
particular recipient.
c. · Document: current worksheet or another existing worksheet.
d. · New document: the hyperlink creates a new worksheet
39) For an Internet hyperlink, choose the type of
hyperlink (choose between Web, FTP or Telnet), and enter the required web
address (URL).
40) For a Mail and News hyperlink, specify whether
it is a mail or news link, the receiver address and for
email, also the subject.
41) For a Document hyperlink, specify the worksheet
path (the Open File button opens a file browser); leave
this blank if you want to link to a target in the same spreadsheet.
42) Linking To External
Data: You can
insert tables from HTML documents, and data located within named ranges from an
OpenOffice.org Calc or Microsoft Excel spreadsheet, into a Calc spreadsheet You
can do this in two ways: using the External Data dialog or
using the Navigator.
43) Insert -> Link to External Data.
44) Linking To Registered Data Sources; You can access a variety of databases and other
data sources and link them into Calc worksheets. Tools -> Options
-> OpenOffice.org Base -> Databases
45) At the bottom of each worksheet window is a
small tab that indicates the name of the worksheets in the
workbook.
46) A cell reference refers to a cell or a
range of cells on a worksheet and can be used to find the values or
data that you want formula to calculate.
SESSION 3: SHARING WORKSHEET DATA
47) Spreadsheet software allows the user to share the workbook
and place it in the network location where several users can access it
simultaneously.
48) At any time, you can set up a spreadsheet for sharing with others.
49) choose Tools > Share Document to activate
the collaboration features for this worksheet. A dialog opens where you can
choose to enable or disable sharing.
50) The Tools > Share Document command can be
used to switch the mode for a worksheet from unshared to shared.
51) When you open a spreadsheet that is in shared mode,
you see a message that the worksheet is in shared mode and
that some features are not available in this mode. You can
choose to disable this message for the future.
52) When you successfully save a shared spreadsheet, the worksheet shows
the latest version of all changes that got saved by all
users.
53) Most spreadsheets software automatically turns off some
features in shared workbooks.
54) shared workbooks don‘t allow merging cells,
conditional formatting, or inserting pictures/graphs/etc.
55) Record Changes: Calc has the
feature to track what data was changed, when the
change was made, who made the change and in which
cell the change has occurred. Edit > Changes > Record
56) A colored border, with a dot in the upper
left-hand corner, appears around a cell where changes were made.
57) Other reviewers then quickly know which cells were edited
by colored border.
58) A deleted column or row is marked by a heavy colored bar.
59) Some changes, for example cell formatting, are not
recorded and marked.
60) To change the color that indicates changes, select Tools
> Options > OpenOffice.org Calc > Changes
61) Viewing Changes: Edit > Changes > Show
62) Adding Comments to Changes: Calc
automatically adds to any recorded change a comment
describing what was changed (for example, Cell B4 changed from ‘9’ to ‘4’).
63) Reviewers and authors can add their comments to
explain their changes. Edit > Changes > Comments.
64) After you have added a comment to a changed cell, you can see
comment by hovering the mouse pointer over the cell.
65) The comment also appears in the dialog when you
are accepting and rejecting changes.
66) Editing change comments: Edit > Changes > Comments
67) Accepting or Rejecting Changes: Edit > Changes > Accept or
Reject
68) When you receive a worksheet back with changes, the beauty of the recording
changes system becomes evident. Now, as the original author, you
can step through each change and decide how to
proceed.
69) The Comment column by default contains an explanation
of the change that was made.
70) If the reviewer added a comment to the change,
it is displayed, followed by the description of the change.
71) If more than one person has reviewed the worksheet, one
reviewer may have modified another reviewer’s change.
72) If so, the changes are hierarchically arranged with a plus
sign for opening up the hierarchy.
73) On the Filter tab of this dialog, you can choose
how to filter the list of changes: by date, author, cell range, or
comments containing specific terms.
74) Merging Worksheets: Edit > Changes > Merge Document
75) Sometimes, multiple reviewers return edited
versions of a worksheet at the same time. In this case, it may be quicker to review
all of these changes at once, rather than one review at a time. For
this purpose, Calc provides the feature of merging worksheets.
76) Comparing Documents: Edit > Compare Document
77) When sharing worksheets reviewers may forget to record the changes
they make. This is not a problem with Calc because Calc can find the changes by
comparing worksheets. In order to compare
worksheets you need to have the original worksheet
and the one that is edited.
78) Spreadsheet software allows the user to share the workbook and place
it in the one location where several users can access.
79) Spreadsheet software can find the changes by comparing
Sheets.
SESSION 4: CREATE AND USE MACROS IN SPREADSHEET
80) A macro
is a saved sequence of commands or keystrokes
that are stored for later use.
81) An example
of a simple macro is one that “types” your address.
82) The OpenOffice.org (OOo) macro language is very
flexible, allowing automation of both
simple and complex tasks.
83) Macros are especially useful to repeat a task the same
way over and over again.
84) Use Tools
> Macros > Record Macro to start the macro recorder.
85) Click Stop Recording to stop the macro
recorder.
86) Existing worksheets/saved worksheet show a library named Standard.
87) Standard library is not created until
the worksheet is saved.
88) If no libraries exist, then the Standard
library is automatically created and used.
89) Function names are not case sensitive. You can
enter =NumberFive() and Calc clearly shows =NUMBERFIVE()
90) Depending on your settings in Tools > Options >
OpenOffice.org > Security > Macro Security, Calc will display
one of the warnings shown below. You will need to click Enable
Macros, or Calc will not allow any macros to be run inside the
document.
91) If you choose to disable macros, then when the
document loads, Calc can no longer find the function.
92) When a document is created and saved, it automatically contains a
library named Standard.
93) The Standard library is automatically
loaded when the document is opened.
94) No other library
is automatically loaded.
95) Use Tools > Macros > Organize Macros >
OpenOffice.org Basic to open the OpenOffice.org Basic Macros dialog
shown further down the page.
96) Calc does not recalculate cells in error unless
you edit them or somehow change them.
97) The usual solution is to store macros used as
functions in the Standard library.
98) If the macro is large or if there are many macros, a stub
with the desired name is stored in the Standard library.
99) The stub macro loads the library containing the
implementation and then calls the implementation.
100) Tools > Macros > Organize Macros > OpenOffice.org Basic to open the OpenOffice.org Basic Macros dialog.
101) Arguments passed to a macro from Calc are always values.
102) Although Calc finds and calls macros as normal functions,
they do not really behave as built-in functions.
103) You can access the OOo internal objects
directly to manipulate a Calc document.
104) Sorting data can be automated
in Open Office by creating a Macro in Calc.
105) Macros are useful to repeat a task the same way over
and over again.
10th IT 402 Main Book/ Final Revision Notes
UNIT-2: ELECTRONIC SPREADSHEET (ADVANCED)/
PYQ
|
S No |
Question (UNIT-2: ELECTRONIC
SPREADSHEET (ADVANCED)/ PYQ) |
Marks |
Year |
|
1 |
When you open a new spreadsheet, by default, it has a sheet named
________ which is managed using tabs at the bottom of the spreadsheet. a.
Sheet1 c. Worksheet1 b. Untitled1 d. New Sheet 1 |
1 |
23 SQP |
|
2 |
Reviewers and authors can add their ______ to explain their changes in
the cell of Spreadsheet. a. Comments c. Worksheet b. Hyperlink d. Macros 1 |
1 |
23 SQP |
|
3 |
In Calc, Arguments passed to a macro from Calc are always ___________.
a. Cell Reference c. Both a and b b. Value d. Sheet Reference 1 |
1 |
23 SQP |
|
4 |
In a spreadsheet using to create a hyperlink to a web FTP or Telnet,
click on the __________ icon available in Hyperlink dialog box. a. browser c.
Internet b. hyperlink d. mail & news 1 |
1 |
23 SQP |
|
5 |
A _________ refers to a cell or a range of cells on a worksheet and
can be used to find the values or data that you want formula to calculate. a.
Cell reference c. Sheet Reference b. Block d. Autofill 1 |
1 |
23 SQP |
|
6 |
________ function takes data from a series of worksheets or workbooks
and summaries it into a single worksheet that you can update easily. a .Data
Combination c. Data Consolidation b. Data Merging d. Data Concatenation 1 |
1 |
23 SQP |
|
10 |
Which function cannot be performed through Subtotal in a Spreadsheet?
a. Sum b. Product c. Average d. Percentage 1 |
1 |
22 T1 SQP |
|
11 |
In a document, __________ refers to the vertical or horizontal
placement of a graphic in relation to the chosen anchor point. a. Arrangement
b. Anchoring c. Alignment d. Text Wrapping 1 |
1 |
22 T1 SQP |
|
12 |
Scenarios are a tool to test questions. a. Auto b. Goal Seek c.
What-if d. Drop Down 1 |
1 |
22 T1 SQP |
|
13 |
It refers to a cell or a range of cells on a worksheet and can be used
to find the values or data that you want formula to calculate. a. Row b.
Column c. Autosum d. Cell Reference 1 |
1 |
22 T1 SQP |
|
14 |
__________, totals/adds data arranged in an array—that is, a group of
cells with labels for columns and/or rows Which step one must should follow
before using the Subtotal option? a. Consolidate b. Rename Data c. Filter
Data d. Subtotal 1 |
1 |
22 T1 SQP |
|
15 |
A ______ hyperlink contains the full address of the destination file
or web page. a. Relative b. Absolute c. Mixed d. Address 1 |
1 |
22 T1 SQP |
|
16 |
Which of the following is more elaborate form of Goal Seek? a.
Subtotal b. Scenario c. Solver d. Consolidate 1 |
1 |
22 T1 SQP |
|
17 |
_________ means combining data in a spreadsheet from different
worksheets into master worksheet a. Hyperlinks b. Consolidating c. Linking d.
Filter 1 |
1 |
22 T1 SQP |
|
18 |
An __________ link will stop working only if the target is moved while
a__________ link will stop working only if the start and target locations
change relative to each other. a. absolute, fixed b. Relative, Absolute c.
absolute, relative d. permanent, absolute 1 |
1 |
22 T1 SQP |
|
19 |
_______________ include fonts, alignment, borders, background, number
formats (for example, currency, date, number), and cell protection in
document. a. Cell Style b. Numbering Style c. Paragraph Style d. Character
Style 1 |
1 |
22 T1 SQP |
|
20 |
By default, sheets tab are present at the __________ of the
spreadsheet. a. Top b. Bottom c. Center d. Right 1 |
1 |
22 T1 SQP |
|
21 |
The cell reference in a spreadsheet for cell range B2 to F15 is
_____________. a. B2.F15 b. B2;F15 c. B2:F15 d. B2-F15 1 |
1 |
22 T1 SQP |
|
22 |
Hema is a chartered accountant. She used to maintain the accounts in a
spreadsheet on everyday basis. There are number of steps which she needs to
follow every time. Can you suggest her a feature of spreadsheet through which
she can perform these tasks quickly without repeating the steps every time?
a. Record Changes b. Track Changes c. Goal Seek d. Using Macros 1 |
1 |
22 T1 SQP |
|
23 |
Raj has created a worksheet where he has added all the information of
his employees. He wants every employee to go through the worksheet and update
their address and phone number, if required. He also would like to know the
changes done by his employees. Which feature of spreadsheet he should enable
to see the changes made by his employees? a. Macro b. Link Workbook c. Change
Worksheet d. Track Changes 1 |
1 |
22 T1 SQP |
|
24 |
Sunita is making a project in spreadsheets. Her friend has told her
how to rename a spreadsheet as it will help her identify the worksheet data
easily. She was very excited and told the same to her brother. Her brother
told her that in spreadsheets(OpenOffice0, a worksheet can be renamed using __________
ways. a. 2 b. 3 c. 4 d. 1 |
1 |
22 T1 SQP |
|
25 |
Explain the term Sorting. |
1 |
22 T2 PYQ |
|
26 |
__________is specifically designed to minimize or maximize the result
according to a set of rules that you define in a spreadsheet. a) Goal Seek b)
Scenario c) Solver d) Subtotal 1 |
1 |
21 SQP |
|
27 |
The intersection point between a row and column is called _______. a)
Row b) Column c) Table d) Cell 1 |
1 |
21 SQP |
|
28 |
Formulas in a Spreadsheet must begin with a __________ sign. a) $ b) @
c) # d) = 1 |
1 |
21 SQP |
|
29 |
___________ is a tool to test “what-if” questions. a) Scenario b)
Solver c) Macro d) Average 1 |
1 |
21 SQP |
|
30 |
Rohit scored 25 out of 30 in English, 22 out of 30 in Maths. He wants
to calculate the score in IT he needs to achieve 85 percent in aggregate.
Suggest him the suitable option out of the following to do so. a)Macro b)
Solver c) Goal Seek d) Sub Total 1 |
1 |
21 SQP |
|
31 |
_________can be used in a spreadsheet software to jump to a different
location from within a spreadsheet and can lead to other parts of the current
file, to different files or even to web sites. a) Illustrations b) Hyperlinks
c) Links d) Filter 1 |
1 |
21 SQP |
|
32 |
Krish and Kritika have done a survey of age wise literacy rates of
their locality as a school project, which they have created in a Spreadsheet.
They both want to work simultaneously to complete it on time. Which option
they should use to access the same Spreadsheet to speed up their work. a)
Consolidate Worksheet b) Shared Worksheet c) Link Worksheet d) Lock Worksheet
1 |
1 |
21 SQP |
|
36 |
A/An ____________ link will stop working only if the target is moved.
1 |
1 |
21 CPYQ |
|
37 |
You can access a variety of ______________ and other data sources and
link them into Calc worksheets. 1 |
1 |
21 CPYQ |
|
38 |
A macro is a saved sequence of commands or keystrokes that are stored
for later use. (True/False) 1 |
1 |
21 CPYQ |
|
7 |
Mention any two operations that can be performed using Macros in a
spreadsheet. 2 |
2 |
23 SQP |
|
8 |
What do you mean by Hyperlinks in Spreadsheets? Give the two different
types of Hyperlinks that can be used in Spreadsheets. 2 |
2 |
23 SQP |
|
33 |
Differentiate between Relative and Absolute Hyperlinks with the help
of an example. 2 |
2 |
21 SQP |
|
34 |
What is the use of Macros in a Spreadsheet? 2 |
2 |
21 SQP |
|
39 |
Define cell reference. 2 |
2 |
21 CPYQ |
|
40 |
Differentiate between relative and absolute hyperlink. 2 |
2 |
21 CPYQ |
|
9 |
Anshita is preparing spreadsheet notes for her Term Exam. Help her to
write short note on the following: i. Scenarios ii. Goal Seek iii. Solver iv.
Subtotal 4 |
4 |
23 SQP |
|
35 |
Define the following: a) Subtotals b) Consolidating Data 4 |
4 |
21 SQP |
|
41 |
How can an original and edited worksheet be compared? 4 |
4 |
21 CPYQ |
Electronic
Spreadsheet (Advanced) IT Code 402 Class 10 Book Solutions
Question
and Answers (Electronic Spreadsheet (Advanced)
Q1. How can we rename a worksheet?
Ans. There are three ways you can rename a worksheet
a)
Double-click on one of the
existing worksheet names.
b)
Right-click on an existing
worksheet name, then choose Rename from the resulting Context menu.
c)
Select the worksheet you
want to rename (click on the worksheet tab) and then select the Sheet option
from the Format menu. This displays a submenu from which you should select the
Rename option.
Q2. What are the two ways of referencing cells in
other worksheets?
Ans. Two ways to reference cells in other sheets: by
entering the formula directly using the keyboard or by using the mouse.
Q3. Differentiate between Relative and absolute
hyperlinks.
Ans. Hyperlinks can be
used in Calc to jump to a different location from within a spreadsheet. An
absolute link will stop working only if the target is moved. A relative link
will stop working only if the start and target locations change relative to
each other. For instance, if you have two spreadsheets in the same folder
linked to each other and you move the entire folder to a new location, a
relative hyperlink will not break.
Q4. List the procedure involved in Linking HTML
Tables to Calc Worksheet.
Ans. You can insert tables
from HTML documents, and data located within named ranges from an
OpenOffice.org Calc or Microsoft Excel spreadsheet, into a Calc spreadsheet.
We can do this in two
ways: using the External Data dialog or using the Navigator.
Using the External Data dialog
a)
Open the Calc worksheet
where the external data is to be inserted. This is the target worksheet.
b)
Select the cell where the
external data is to be inserted.
c)
Choose Insert -> Link
to External Data.
d)
On the External Data
dialog, type the URL of the source worksheet or click the […] button to open a
file selection dialog. Press Enter to get Calc to load the list of available
tables.
e)
In the Available
tables/range list, select the named ranges or tables you want to insert. You
can also specify that the ranges or tables are updated every (number of)
seconds.
f)
Click OK to close this dialog and insert the linked data.
Q5. What is the purpose of adding comments?
Ans. Comments are mostly used in shared Calc sheet
which is used to explain the changes made in the sheet to the author of the
sheet.
Q6. How can we add comments to the changes made?
Ans.
Comments can be added as follows:
1) Make the change to the spreadsheet.
2) Select the cell with the change.
3) Choose Edit > Changes > Comments. The
automatically-added comment provided by Calc appears in the title bar of this
dialog and cannot be edited.
4) Type your own comment and click OK.
After you
have added a comment to a changed cell, you can see it by hovering the mouse
pointer over the cell.
Q7. What are Macros?
Ans. A macro is a saved sequence of commands or
keystrokes that are stored for later use. Macros are especially useful to
repeat a task the same way over and over again.
Q8. How can we record a Macro?
Ans. Steps to record macro are as follows
a)
Use Tools > Macros >
Record Macro to start the macro recorder. The Record Macro dialog is displayed
with a stop recording button.
b)
Perform the actions you
want to be recorded in the document.
c)
Click Stop Recording.
d)
The Macro dialog
appears, in which you can save and run the macro.
Fill in the blanks (Electronic Spreadsheet
(Advanced))
1) At the bottom of each worksheet
window is a small tab that indicates the name of the worksheets in the workbook.
2) A cell reference refers to a cell or a range of
cells on a worksheet and can be used to find the values or data that you want
formula to calculate.
3) Spreadsheet software allows the user
to share the workbook and place it in the Network location where several users can access.
4) Spreadsheet software can find the
changes by Comparing Sheets.
5) Macros are useful to repeat a task the same way over and over again.
Disclaimer : I tried to give the correct answers
of Electronic Spreadsheet (Advanced) IT Code 402 Class 10, but if you feel
that there is some mistake in any of the above answers of Electronic
Spreadsheet, please share your valuable feedback on sa7588164gmail.com
0 Comments