CLASS X - Notes - 2 - IT - Term I
CLASS X - Notes - 2 - IT - Term I
CLASS X - Notes - 2 - IT - Term I
>>> Data consolidation process involves combining data from different sources, cleaning and
verifying it, and storing it in one place, such as a data warehouse. There are multiple sources and
formats of data in every business. By consolidating data, it becomes easier to unify it and understand and
utilise the data.
Function:- Select the function that you want to use to consolidate the data.
Consolidation ranges:- Displays the cell ranges that you want to consolidate.
Source data range:- Specifies the cell range that you want to consolidate with the cell ranges listed in
the Consolidation ranges box. Select a cell range in a sheet, and then click Add. You can also select the
name of a predefined cell from the Source data range list.
Shrink / Expand:- Click the Shrink icon to reduce the dialog to the size of the input field. It is then easier
to mark the required reference in the sheet. The icons then automatically convert to the Expand icon.
Click it to restore the dialog to its original size.
The dialog is automatically minimised when you click into a sheet with the mouse. As soon as you
release the mouse button, the dialog is restored and the reference range defined with the mouse is
highlighted in the document by a blue frame.
Shrink Expand
Copy results to :- Displays the first cell in the range where the consolidation results will be displayed.
Delete:- Deletes the selected element or elements without requiring confirmation.
Add:- Adds the cell range specified in the Source data range box to the Consolidation ranges box.
>>> Subtotal Tool :- The Subtotals tool can create subtotals for up to three arrays arranged in
labelled columns. It also groups subtotals by category and sorts them automatically, thereby eliminating
the need to apply AutoFilters and filter categories by hand. To use this tool, select Data > Subtotals from
the Menu bar, which opens the Subtotals dialog.
Group by:- Select the column that you want to control the subtotal calculation process. If the contents of
the selected column change, the subtotals are automatically recalculated.
Calculate subtotals for:- Select the column(s) containing the values that you want to subtotal.
Use function:- Select the mathematical function that you want to use to calculate the subtotals.
Subtotals tool options Click on the Options tab of the Subtotals dialog to access the following:
Groups Defines how subtotal data is organised.
• Page break between groups – inserts page breaks between each subtotal group so that each
group displays on a separate page when you print the data.
• Case sensitive – prevents the tool from grouping entries by data labels that differ by case. In our
sales data example, entries with “Brigitte” and “brigitte'' under the Employee column will not match
if this option is selected.
• Presort area according to groups – sorts entries by group before calculating subtotals.
Disabling this option prevents the tool from grouping matching entries together. As a result,
distinct subtotals will be created for matching entries if they do not appear on consecutive rows.
Sort Defines how subtotal data is sorted.
This section is disabled if the Pre-sort area according to groups is unchecked.
• Ascending or Descending – sorts entries by value from lowest to highest and highest to lowest,
respectively. You can modify these sort rules by using Data > Sort in the Menu bar.
• Include formats – carries over formatting, such as the currency format, from the data to the
corresponding subtotals.
• Custom sort order – sorts your data according to one of the predefined custom sorts defined in
Tools > Options > LibreOffice Calc > Sort Lists in the Menu bar.
>>> Scenarios are saved, named cell ranges that you can use to answer “what-if” questions about
your data. You can create multiple scenarios for the same calculation set, then quickly swap between
them to view the outcomes of each.For example, if you wanted to test different interest rates for an
investment, you could create scenarios for each rate, then switch between them to find out which rates
work the best for you. To use the Scenarios tool, select Tools > Scenarios from the Menu bar to open the
Create. For each scenario you create, use a unique name that clearly identifies
Name of scenario:- Defines the name for the scenario. Use a clear and unique name so you can easily
identify the scenario. You can also modify a scenario name in the Navigator through the Properties
context menu command.
Comment:- Specifies additional information about the scenario. This information will be displayed in the
Navigator when you click the Scenarios icon and select the desired scenario. You can also modify this
information in the Navigator through the Properties context menu command.
Settings:- This section is used to define some of the settings used in the scenario display.
Display border:- Highlights the scenario in your table with a border. The colour for the border is
specified in the field to the right of this option. The border will have a title bar displaying the name of the
last scenario. The button on the right of the scenario border offers you an overview of all the scenarios in
this area, if several have been defined. You can choose any of the scenarios from this list without
restrictions.
Copy back:- Copies the values of cells that you change into the active scenario. If you do not select this
option, the scenario is not changed when you change cell values. The behaviour of the Copy back setting
depends on the cell protection, the sheet protection, and the Prevent changes settings.
Copy entire sheet:- Copies the entire sheet into an additional scenario sheet.
Prevent changes:- Prevents changes to the active scenario. The behaviour of the Copy back setting
depends on the cell protection, the sheet protection, and the Prevent changes settings.
● You can only change the scenario properties if the Prevent changes option is not selected and if
the sheet is not protected.
● You can only edit cell values if the Prevent changes option is selected, if the Copy back option
is not selected, and if the cells are not protected.
● You can only change scenario cell values and write them back into the scenario if the Prevent
changes option is not selected, if the Copy back option is selected, and if the cells are not
protected.
Solver settings
Target Cell
Enter or click the cell reference of the target cell. This field takes the address of the cell whose value is to
be optimised.
Optimise results to
● Maximum: Try to solve the equation for a maximum value of the target cell.
● Minimum: Try to solve the equation for a minimum value of the target cell.
● Value of: Try to solve the equation to approach a given value of the target cell.
Enter the value or a cell reference in the text field.
By Changing Cells
Enter the cell range that can be changed. These are the variables of the equations.
Limiting Conditions
Add the set of constraints for the mathematical problem. Each constraint is represented by a cell
reference (a variable), an operator, and a value.
● Cell reference: Enter a cell reference of the variable.
Click the Shrink button to shrink or restore the dialog. You can click or select cells in the sheet.
You can enter a cell reference manually in the input box.
● Operator: Select an operator from the list. Use the Binary operator to restrict your variable to 0 or
1. Use the Integer operator to restrict your variable to take only integer values (no decimal part).
● Value: Enter a value or a cell reference. This field is ignored when the operator is Binary or
Integer.
● Remove button: Click to remove the row from the list. Any rows from below this row move up.
There are three ways you can rename a worksheet, and the only difference between them is the
way in which you start the renaming process. You can do any of the following:
• Double-click on one of the existing worksheet names.
• Right-click on an existing worksheet name, then choose Rename from the resulting Context menu.
• 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.
Referencing Other Sheets There are two ways to reference cells in other sheets: by entering the
formula directly using the keyboard or by using the mouse.
Referencing Other Worksheets:There are two ways to reference cells in other sheets: by entering the
formula directly using the keyboard or by using the mouse.
Creating The Reference With The Keyboard Typing the reference is simple once you know the format the
reference takes. The reference has three parts to it: Path and file name Sheet name Cell Looking at the
figure above, you can see the general format for the reference is
=’file:///Path &File Name’#$SheetName.CellName.
Working with Hyperlinks 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.
CREATE AND USE MACROS IN SPREADSHEET
A macro is a set of commands or keystrokes that are stored for later use. An example of a simple macro
is one that enters your address into an open document. You can use macros to automate both simple
and complex tasks. Macros are very useful when you have to repeat the same task in the same way.
Recording a macro
Make sure macro recording is enabled by going to Tools > Options > LibreOffice > Advanced and
selecting the option Enable macro recording under Optional Features. By default, this feature is turned
off in LibreOffice.
1. Go to Tools > Macros > Record Macro to start recording a macro. A small dialog with a
Stop Recording button is displayed indicating that LibreOffice is recording a macro.
2. Type the desired text you want to be entered when this macro is run. As an example, type
your name.
3. Click Stop Recording on the small dialog. This will cause Basic Macros dialog to open
(similar to Figure 1, but with different action buttons).
4. Open the library container My Macros.
5. Find the library named Standard in My Macros. Note that every library container has a
library named Standard.
6. Select the Standard library and then choose an existing module in which to save the
macro. Alternatively you can click New Module to create a new module to contain the
newly recorded macro.
7. In the Macro Name text box at the top left section of the dialog, type a name for the macro
you have just recorded, for example EnterMyName.
8. Click Save to save the macro and close the Basic Macros dialog.
If you followed all of the above steps, a macro named EnterMyName will have been created inside the
selected module.
Running a macro
1. Go to Tools > Macros > Run Macro to open the Macro Selector dialog (Figure 6).
2. For example, select your newly created macro EnterMyName and click Run.
3. Alternatively, go to Tools > Macros > Organise Macros > Basic to open the Basic
Macros dialog (Figure 1), select your macro and click Run.
NETWORKING FUNDAMENTALS
A computer network is a collection of computers and other hardware components interconnected
by communication channels (cables or satellites) that allow sharing of resources and information.
Dial-up: Dial-up Internet access is a form of Internet access that uses the facilities of the
public switched telephone network (PSTN) to establish a connection to an Internet service
provider (ISP) via telephone lines using a device called MODEM. Users dial a particular
number provided by the ISP and gain access to the Internet. Dial-up connections are
extremely slow and in most cases, it is replaced by a high speed connection such as DSL or
Cable Modem.
DSL: Digital subscriber line(DSL) provide Internet access by transmitting digital data over
wires of a local telephone network. DSL service is delivered along with wired telephone service
on the same telephone line. On the customer premises, a DSL filter removes the high
frequency interference, enabling simultaneous use of the telephone and data transmission.
For using a DSL connection, you need a DSL modem and a subscription.
Cable Internet Access: Cable Internet Access is a form of broadband Internet access that uses
the cable television infrastructure. Cable Internet Access is provided through existing cable TV
networks; this is similar to DSL that is provided over existing telephone lines.
3G: 3G, short for 3rd Generation is a set of standards used for mobile devices and mobile
telecommunication services and networks. High-Speed Downlink Packet Access (HSDPA) is
3G mobile telephony communications protocol that allows higher data transfer speeds and
capacity. If support for 3G is available on your mobile phone, you can subscribe to the 3G
connectivity with your ISP in order to get high speed Internet connection on your phone.
WiMAX: WiMAX (Worldwide Interoperability for Microwave Access) is a wireless
communications standard designed to provide mobile broadband connectivity across cities and
countries through a variety of devices. WiMAX is a long range system, covering many
kilometres and is typically used where DSL or Cable Internet Access cannot be used; this
could be difficult in laying out cables for home or offices located in remote locations but need
access to the Internet.
WI-Fi: Wi-Fi is a popular technology that allows an electronic device such as computers or
mobile phones to exchange data wirelessly over a network, including high-speed Internet
connections. WiFi devices such as personal computers, smartphones, video game console,
etc. can connect to a network resource such as the Internet through a device called the
Wireless Access Point (WAP). Wi-Fi is used where cables cannot be run (such as old
buildings, outdoor areas) to provide network and Internet access. Wi-Fi can also be used
where users require mobile connectivity. Wi-Fi connectivity is used in home & offices, hotels,
college & school campuses typically for Internet Access. Shopping malls, coffee shops, resorts
mostly offer free or paid Wi-Fi access to the Internet for their customers.
DATA TRANSFER ON THE INTERNET
➔ Each packet is sent from computer to computer until it finds its destination. Each computer
on the way decides where next to send the packet. All packets may not take the same
route.
➔ At the destination, the packets are examined. If any packets are missing or damaged, a
message is sent asking for them to be re-sent. This continues until all packets have been
received intact.
➔ The packets are now reassembled into their original form. All this done in seconds!
● Local Area Network (LAN) A LAN comprises cables, access points, switches,
routers, and other components that enable devices to connect to internal
servers, web servers, and other LANs via wide area networks.
Instant Messaging Services There are two kinds of instant messaging software –
>>>>Application based >>>>> Web based.
● Application based instant messaging software is downloaded and installed on the user's
computer.
● Some of the popular instant messaging software are: Google Talk / Yahoo! Messenger /
Skype / Windows Live Messenger / Rediff Bol / WhatsApp/etc.