Hassan II University - Mohammedia

Faculty of Legal, Economic and Social Sciences - Ain Sebaâ

Computer science course
MICROSOFT OFFICE EXCEL
Field: Economic Sciences and Management
2007-2008
Pr. Wafae SABBAR
Basic concepts
1. Definition
A spreadsheet is software that allows data manipulation and automatic calculations.
calculations on numbers stored in an array. It is thus possible to automate calculations
complexes involving a large number of parameters by creating tables called sheets of
calculate
.
There are many spreadsheets developed by major software publishers. The main spreadsheets are:
Microsoft Excel, from the Microsoft Office suite
Sun StarOffice Calc, from the StarOffice suite
OpenCalc, from the OpenOffice suite
IBM/Lotus 1-2-3 from the SmartSuite suite
Corel Quattro Pro from the WordPerfect suite
KSpread from the free KOffice suite on Linux
2. Features of a spreadsheet
Direct entry of cell contents.
The user types the data directly into the spreadsheet cells. This implies a set of
commands to navigate the sheet, select cells, enter, modify values or
formulas, or even create template forms.
Data importation from other sources (Databases, word processors...).
Often, the company has a database where information related to is stored
his activity. The spreadsheet allows them to be read directly from the database (without re-entering them) for the
manipulate.
Data storage
The sheets and binders can be saved and thus serve as storage areas.
of information.
Scientific, financial, statistical calculations... on numbers, dates, and times.
2
Pr. Wafae SABBAR
There are many predefined functions that easily allow you to establish statistics, make
of the totals, (for example a profit and loss account or a balance sheet).
Problem solving (maximization of gains, minimization of losses...).
The spreadsheet also allows you to find the data that optimizes or enables you to achieve a result.
For example, 'How many kilometers do I need to drive per year with my car for a tax to be applied'
Are actual expenses advantageous?
Simulations
The spreadsheet allows for automatic calculations on a set of possible data and
present the results obtained in each case. For example, suppose a company wants to launch
a new product line, it builds several sales scenarios and the spreadsheet calculates which
would be the potential benefits or losses in each case.
Syntheses
It is the set of operations that allows several results to be gathered into one.
For example, a company can build a balance of its activity by reviewing each of its sheets.
representatives.
Analyze
The spreadsheet can participate in data analysis, providing tools to interpret the observation.
result sets (survey processing, ...).
3. Presentation of Microsoft Excel
Microsoft Excel is the spreadsheet application of the Microsoft Office suite. To launch it, simply
click on the appropriate icon in your Start menu (under Windows) or click on a
Excel file (with the extension .xls).
An Excel document is called a workbook, it contains one or more spreadsheets, present under
tab forms at the bottom of the page.
3
Dr. Wafae SABBAR
It is composed of various elements:
A title bar indicating the name of the application as well as the name of the open workbook
A menu bar allowing access to the different functions of the spreadsheet
3. A toolbar offering direct access to the main features in the form of icons
features. It is interesting to note that this bar can be customized in order
to allow you to create shortcuts to the features you use
more often.
A formula bar showing the address of the selected cell and indicating its
content. The formula bar allows you to enter the data to be inserted into the
cells.
5. The spreadsheet is the key element of the spreadsheet application, it is the table containing all the
cells. At the bottom of the displayed spreadsheet are tabs allowing to
switch from one spreadsheet to another.
6. A status bar providing information on the actions to be taken. By default the
message displayed in the status bar
4. Presentation of the spreadsheet:
Click on Start, select Programs and Microsoft Excel to access the
next working window:
Header of
Scroll bars
Cell
sélectionnée
Barre de
En-tête de
Sheet tab
From the binder
Locking

Locking
Digital activated
Caps Lock activated
A spreadsheet is a grid made up of rows and columns. Each cell
corresponds to the intersection of a row and a column and has a unique address,
called reference.
Each line of a spreadsheet is marked by a number.
Each column is identified by a letter (from A to Z, then from AA to AZ, then from BA to BZ, ...).
Ceci permet de désigner une cellule par une lettre et un chiffre qui repèrent la colonne et la
line at the intersection of which it is located.
For example, the cell located at the intersection of column Bet and row 5 is cell B5.
The workbook can contain up to 255 sheets. Each Excel sheet can contain up to
256 columns identified by letters and 65,536 rows identified by numbers.
In general, you first select the cell or cells that you want to use,
then you type in data or you choose a command.
5. Management of Bindings
Create a workbook
1) In the file menu, click on new
Insert a new spreadsheet
1) Select a sheet from the corresponding tab
2) In the insert menu, click on spreadsheet and a new sheet will be inserted.
Delete a sheet
1) Select the sheet to delete
2) In the edit menu, click on delete spreadsheet and the sheet disappears.
Copy a spreadsheet
1) Using the mouse, select the sheet to copy or move
2) Click on the tab of the selected sheet and drag it to where you want.
Saving a workbook
The registration procedure remains the same for all application software.
1) In the file menu, click on 'save as'.
5
Pr. Wafae SABBAR
2) In the 'save to' area, select the disk and then the directory where you want to
save your document.
3) In the file name box, enter the document name.
Finally, click on 'save'.
Modification of cell contents
1) Double-click on the cell containing the data to be modified.
2) Make the necessary modifications to the content of the cells.
3) To validate your changes, press enter.
How to clear the contents of the cells
1) Select the cells, rows, or columns you wish to delete.
2) In the edit menu, hover over delete, then click on all, format, content or depending on the case.
comment. If you click on a cell and then press the delete or backspace key,
Microsoft Excel clears the content of the cells but does not delete the comments or formats.
Deletion of cells, rows, or columns.
1) Select the cells, rows, or columns to delete.
2) In the edit menu, click on delete.
Adjacent cells are shifted to occupy the freed space.
Change of font or font size
1) Select the entire cells or the text that you want to format.
2) In the font area, click on the desired font
In the size area, click on the desired size
Printing of active sheets
If a print area has been defined for the worksheets, MS Excel only prints this.
printing zone.
1) In the file menu, click on print
In the print area, click on the desired option
Formatting a table
To apply a type of border, colors, and patterns to a table: Select your table, choose
the Format command, Cells. In the Border tab of the dialog box, choose the type of
border requested and click on ok.
6
Pr. Wafae SABBAR
You can also apply a border style from the AutoFormat option:
These formatting options are intended to offer predefined templates by Excel. In order to
apply a model
1) Select your table
2) Choose the Format command, AutoFormat
In the dialog box, choose the template and click ok.
Change the width of a column
The initial width of a column is 10.71 characters, to modify it:
1) Select your column
2) Choose the command Format, Column, Width
In the dialog box, enter the requested width and click ok.
Change the width of a line
The initial height of a line is set by default to 12.75 points, to change it:
1) Select your line
2) Choose the command Format, Line, Height
In the dialog box, enter the requested height and click ok.
Merge several cells
To merge several adjacent cells into one cell:
1) Select the cells to merge, choose the Format command, Cells
2) In the Alignment tab of the dialog box, enable Merge cells and click OK.
To create a line break, that is to say, to create two lines in the same cell:
1) Select the cell at the level where you want to create a line break
2) Choose the Format, Cells command
3) In the Alignment tab of the dialog box, enable Wrap text automatically and
click on ok.
6. Values, formulas, functions, and formats
Each cell can contain a value. This value is either entered directly by the user or is
the result of a calculation expressed by a formula.
7
Pr. Wafae SABBAR
A value has a type and is displayed in a format.
The types
These are, for example: whole or decimal numerical values, words, or phrases.
(called strings in computer science), logical values (TRUE or FALSE, called
Boolean values in computer science.
The formulas
These are expressions that are evaluated by the spreadsheet and return a result. For example,
(1+cos(5))*2-1 is a formula (mathematical).
The formulas are built with functions.
In this last example, cos is a function, but so are +, *, and -. The ease of use of the spreadsheet
also comes from the fact that we can put cell references or ranges of cells in the
(1+cos(A1))*B2-C3.
The expression will be evaluated with the value contained in cell A1, cell B2, and cell C3.
The formats
These are the attributes of the cells that allow for the values to be presented legibly. One can mention by
for example, date formats, time, monetary, percentage, but also bold, underline, italic, ...
It should be noted that the display of the contents of a cell may differ from the value contained in it.
this cell.
8
Pr. Wafae SABBAR
2. Cell references
Each cell can receive a formula, the evaluation of which provides a
value.
To ensure that the spreadsheet understands that the content of a cell is a formula, we use the convention
Next: a formula always begins with the symbol "="
A formula is a set of calculations performed using operators (usual arithmetic operators
+, *, -, ...; comparison operators <, >, =, ..., etc.) and functions on some
data (statistical, financial, mathematical functions...).
The data can be literally written in the formula or may be found in others.
cells.
The mechanism allowing access from one cell to a value located in another
The cell is called a reference. In this section, we will present the references, then the functions.
and the formulas.
1. Relative references
The relative reference of a cell is the expression of its position relative to another cell. Thus,
A relative reference gives the offset (in terms of number of rows and columns) between the cell
(referred to as reference) and a pointed cell (referred to as referenced cell). By convention, it is noted negatively.
an upward shift for the vertical axis and a left shift for the horizontal axis.
Example
Let's consider the following example: we want to arrange 4 multiplications on the spreadsheet as follows
next, the products being calculated by Excel using appropriate formulas.
A first solution is to write all the formulas to calculate the products yourself, which
give
9
Pr. Wafae SABBAR
•
•
•
•
formula in C6: =C4*C5
formula in F6: =F4*F5
=C8*C9
formula in F10: =F8*F9
In other words, you need to enter four different formulas, while the principle of the calculation remains the same.
same: multiply the value found two rows above by the value of the cell
finding immediately above.
Another way to proceed is, however, possible: to write the first formula in cell C6.
Then we use Copy-Paste. Cell C6 is copied and then pasted into F6, C10, and F10.
What happens during a Paste operation?
A priori, one might expect that the formula would be copied as is: one would then obtain the
Formula "=C4*C5" in cell F6, which is not exactly what we wanted.
However, that's not what happens. The formula obtained in F6 is indeed "=F4*F5". Why?
This comes from the fact that the references used when writing the formula in cell C6 are
relative references. Thus C4 refers more to the cell located two rows above in the
the same column (which happens to be cell C4!) as cell C4 itself.
When this formula is copied to the clipboard, this perspective is retained.
When pasting into cell F6, the reference is modified to continue referring to the cell.
finding two lines higher in the same column. Thus C4 becomes F4 and C5 becomes
transform into F5, etc...
2. Absolute references
The absolute reference represents the way to uniquely identify a cell in a sheet.
calculation.
Now let's consider the following example: we want to write the multiplication table of the number se
found in cell E2.
Again, the formulas that we want to copy into the cells B5 to K5 are all of the same type:
multiply the content of cell E2 by the content of the cell immediately above it. We
So go write this formula once in cell B5, then copy it to the cells.
following with Copy-Paste.
10
Dr. Wafae SABBAR
If we stick to what we currently know, we are tempted to simply write the formula
"=E2*B4" in cell B5.
What happens if we copy this formula into the neighboring cell?
The references used are relative references, so when copying, these references are
modified to always refer to the same relative cell position.
Thus, in B5, B4 actually refers to the cell located in the previous row in the same column.
After Copy-Pasting into the adjacent cell, the reference is modified to refer again to the
cell of the previous row and the same column, which gives C4. That's exactly what we wanted.
obtain.
Let's now examine the case of the reference B2 used in the formula in B5. It actually refers to
the cell located three rows up and three columns to the right. AfterCopy-Paste the
In C5 formula, this reference is modified to refer to the cell in the same position.
relative, namely cell F2!
Catastrophe! This is not at all what we wanted to achieve! How do we get out of this?
In cell B5, an absolute reference to cell E2 must be used.
Such a reference is obtained by preceding the letter of the column and the number of the row with a
$ sign: this gives the notation $E$2. This notation actually refers to the cell E2, and not to the
cell located three rows up and three columns to the right. During a Copy operation
Copying, the reference is not changed.
Ultimately, the correct formula to write in cell B5 is "=$E$2*B4".
After copying in C5, we get the formula "=$E$2*C4" (the absolute reference is not modified, the
relative reference is updated).
3. Mixed references
Absolute reference absolutely designates the row and column of the referenced cell.
is done by preceding the references with the symbol "$" for rows and columns (example: $E$2).
Relative reference, on the other hand, refers relatively to the row and column of the cell.
referenced (example: E2).
The mixed reference is a blend of the two reference modes seen earlier.
The mixed reference allows you to refer to the row absolutely and the column relatively.
(or the reverse): thus writing « =$C4 » in cell D5 references the cell located in column C
(absolute reference for the column using a "$" sign) and in the previous line
relative reference for the line, no "$" sign in front of the line number.
11
Pr. Wafae SABBAR
4. Absolute and relative reference to cell ranges
One may need to reference several adjacent cells in order to perform certain tasks.
calculations: let's think for example about calculating the average grades of a student in different subjects.
The calculation of the average in French is done on the grades contained in the cells B3, B4 and
B5. A first solution would be to write the formula "=AVERAGE(B3;B4;B5)" in B7. We
list all the cells by separating each reference with the sign ';'. However,
Since the referenced cells are adjacent, we can write more simply.
=AVERAGE(B3:B5)
In general, to designate a set of cells adjacent to each other and forming
a rectangular beach, we simply refer to the two cells located at the top ends
left and bottom-right of this area, while separating the reference to each of these cells by the
: sign.
In formula editing mode, the designation of a range can be done with the mouse by selecting the
beach.
If you need to reference cells located in multiple ranges (for example here for
to calculate the overall average), simply designate each range as before, and separate
references to each beach by the sign " ; ".
Thus, the formula "=AVERAGE(B3:B5;D3:D5)" means: calculate the average of the values found
finding in the range defined by cells B3 and B5 (using ':' between B3 and B5) and
(using ';') within the range defined by cells D3 and D5 (using ':' between D3 and
D5) With the mouse, in formula edit mode, just select the different ranges while
pressing the Ctrl key.
5. Cell references: summary
When similar calculations take place in several places in the table, one must ask the question of
the use of absolute, relative or mixed references.
If I copy the formula on the same line, does the referenced cell need to move the same way?
way?
•
•
If yes, the reference in the column must be relative.
If not, the reference in the column must be absolute.
If I copy the formula in the same column, does the referenced cell need to move from there?
same way?
12
Dr. Wafae SABBAR
•
•
If so, the reference on the line must be relative.
If not, the reference on the line must be absolute.
6. References by name
Instead of referring to a cell by its coordinates, one can use a name, provided that one has
previously define which cell this name refers to.
In the second example, we could have referenced cell E2 by the name MULTIPLICATEUR.
The formula to write in cell B5 would have been "=MULTIPLY*B4".
To be able to use a reference by name, two steps are necessary:
First, you need to assign a name to a cell. This is done by typing the name in the window.
of name editing or through the Insert menu/Name/Define.
2. We can then use this name to reference this cell in a formula. The window
Name editing allows for easy introduction of names into formulas.
The use of name reference provides two advantages:
•
The formulas become more readable: a formula like "=AMOUNT_HT*(1+VAT)"
is much more explicit than a formula like =C2*(1+$D$2) (assuming that the name of the
cell C2 is AMOUNT_HT and that of cell D2 is VAT.
•
By using a named reference, the absolute reference of the cell becomes transparent.
If for one reason or another we need to indicate the VAT in cell B2 (at
in place of cell D2), you just need to name this new cell VAT.
Thus, the cells using the named reference will not need to be modified unlike the others.
cells that used the absolute reference $D$2, which would need to be modified manually.
7. Scope of names
Defined names can be used throughout the workbook, which means a name is defined for
the entire binder. This implies that there can only be one cell or range of cells associated with
a name. For example, the name tax_rate will designate a single cell in a sheet of the workbook.
If we take up the example from the previous study, the sheets for the different representatives are
identical. For calculations in a representative's sheet, we want to designate the range B6:E6 as the
Sales name. This name must be local to the sheet.
We can start by naming the representatives' sheets. By default, the sheets are called
Sheet1, ...
To rename a sheet, double click on the tab with its name, you can then rename it.
13
Pr. Wafae SABBAR
To define a local cell name for the sheet, select the cell in the input window of
names, type the name of the sheet followed by a '!' followed by the name of the cell.
For example, the sheet of representative Duponta for sheet name Dupont, I select the range B6:E6, I
la nomme feuildupont! ventes. Pour utiliser cette plage dans la feuille, il suffit alors de la désigner par
the name sales, to use this range in another sheet, you just need to refer to it by name
feuildupont!sales.
A second solution that we recommend is to create the sheet for a
representative: create the template, name the cells and range of cells, enter the formulas, make the
formatting. Then, duplicate this sheet as many times as there are representatives and finally rename
the sheets. During duplication, the names will then be considered as local to each of the
leaves.
8. Organization and choice of the type of reference to use
How to choose one reference mode or another? The choice of a certain type of reference should not be
to do randomly, but must result from a minimum of reflection in order to effectively build one's sheet
of calculation. The following principles should be kept in mind:
•
Isolated cells that contain fixed data must be referenced by name, this
name will be chosen to describe the content of the cell (VAT, exchange rate, discount, ...).
•
To ensure the consistency of the spreadsheet, each data point must not appear
only once. For example, it is out of the question for the amount of VAT to appear
in two cells D2 and F6. Consider for example the day when the VAT changes from 18.6% to
20.6%: what will happen if you modify cell D2 and not cell F6?
Response: the spreadsheet is very likely to contain inconsistent results!
•
Assign names to cell ranges whenever necessary and possible.
Formulas are easier to develop, review, or correct. Reference errors are
less common with the use of names.
9. Names and cell ranges
It is possible to name ranges of cells in the same way as for individual cells. For
explain the behavior of names in formulas, we will clarify the example from the figure
next :
14
Pr. Wafae SABBAR
The names provided in the sheet and the formulas are summarized in the following table:
name
cell(s)
formula
taux_de_tva C2
prix_ht
C5:C11
D5:D11
quantity
amount_excl_tax E5:E11
montant_ttc F5:F11
=quantity * price_excl_tax
amount_excl_tax * (1 + VAT_rate)
=sum(total_amount)
total_ttc
F12
For the column 'amount excluding tax', we find the formula '=quantity*price_ht'.
The operation "*" performs the product of two numbers, or the product of two cells.
Excel interprets this formula as follows: take the contents of two cells
two ranges named montant_ht and quantity located on the same line (because these ranges are
verticals)
and
the
multiplier,
this
who
correspond
in
result
awaited.
For the formula "=sum(amount_ttc)" contained in the cell named total_ttc, the function
sum() calculates the sum of the contents of a range of cells, so the result is the sum of
contents of the cells in the named range total_ttc.
In the first case, the operation involves cells, so Excel performs an implicit intersection.
between the beaches designated by their name and the lines when the beaches are vertical (and the columns
if the beaches are horizontal).
15
Pr. Wafae SABBAR
In the second case, the function applies to a range of cells, and therefore the operation focuses on the
designated range of cells.
A second example is that of the figure below.
In the title column "total", the formula "=note1+note2" has been written, the result is indeed the result.
expected. For the title column "totalbis" the formula "=sum(note1;note2)" was written,
the result is the sum of all the cells contained in the union of the two ranges note1 and note2.
16
Pr. Wafae SABBAR
3. The Formulas
1. Introduction
The main interest of a spreadsheet is to allow for the automation of calculations, that is to say to use
cells to perform operations based on the values of other cells. The spreadsheet recalculates
thus all the values at each change of a cell value.
We thus speak of a formula to designate the expression that, once entered in a cell, allows for
spreadsheet to automatically perform a calculation based on the values of other cells and display a
result.
Formulas can thus contain references to other cells, expressions, or
functions.
2. Enter a formula
Entering a formula is done by selecting a cell and then using the formula bar.
To enter a formula, just start by typing the equals sign (=) and then enter the
cell references to use and possibly operators and/or functions.
3. The expressions in the formulas
Arithmetic expressions
Arithmetic expressions are expressions constructed using arithmetic operators.
usuals and constants, references, functions. The available operators are: " + " (Addition),
« - » (unaire : prendre l'opposé), « - » (binaire : soustraction), « / » (division), « * » (multiplication),
^ (exponentiation)
17
Pr. Wafae SABBAR
Note: For the spreadsheet, a date is a numeric value. Only the display format allows
visualize this value as a date.
Conditional expressions:
They are built using comparison operators. These expressions result in a
logical value TRUE or FALSE.
The comparison operators are: " = " (equal to), " > " (strictly greater than), " >= " (greater than or equal to)
equals to), " < " (strictly less than), " <= " (less than or equal to), " <> " (not equal to).
The text expressions:
They are constructed using the operator "&" which allows you to concatenate (put end to end) two
strings.
4. Functions
Functions are powerful calculation tools. A spreadsheet, particularly Excel, provides you with
offers a wide range of functions. Only a professional user knows all the functions
and its particularities. You will profitably use the function assistant which can be called by a button
in the toolbar, a button in the toolbar when you are in formula editing mode, or
by selecting the Insertion/function menu.
Note that every function is followed by parentheses.
We will only present the usual functions.
You need to know how to use the help (easily accessible from the function assistant) to search for a
function and know its use: its syntax (how to write it), its parameters (what can we
apply it), its effect, its restrictions.
Examples of date and time functions
•
TODAY: This function returns the numerical value corresponding to today's date.
which allows to obtain the current date using the Date format. This function is a function without
parameter (it has no argument).
The syntax is TODAY().
•
MONTH: This function returns the month number that corresponds to the date associated with the value.
numeric passed as an argument. This function is therefore a function with one parameter.
The syntax is MONTH(numeric_value).
Examples of text functions
18
Dr. Wafae SABBAR
•
•
UPPERCASE: This function returns the text passed as an argument in uppercase.
The syntax is UPPER(valueText).
CNUM: This function converts the text passed as an argument into a numerical value.
correspondence. If the text cannot be converted, the value "#value!" is returned.
Examples of logical functions
Logical functions allow you to build logical expressions from expressions.
Conditionals. These functions are important and we provide the complete list here.
•
ET: This function returns the value TRUE if all its arguments are TRUE, and
FALSE otherwise. This function accepts a number of arguments between 1 and 30. The syntax
is AND(logical_value1;logical_value2;...).
•
OR: This function returns TRUE if at least one of the arguments is TRUE.
and FALSE otherwise (that is to say if all the arguments are FALSE). This function admits
a
name
of arguments
understood
between
1
and
[Link]
syntax
is
OR(logical_value1;logical_value2;...)
•
•
NO: This function returns the logical opposite of the logical value passed in
argument.
The syntax is: NOT(booleanvalue).
This function returns one value or another depending on the truth value of an expression.
logic.
The syntax is: IF(logical_test;value_if_TRUE;value_if_FALSE).
The first logical test argument must be a logical expression resulting in TRUE or FALSE, the
the second argument is the value returned by the function if the logical expression has the value
TRUE, the third argument is the value returned by the function if the logical expression is the
value FALSE.
The logical test can be a logical expression.
We can nest the IS.
Examples of mathematical functions
All common mathematical and trigonometric functions are available (SIN, LN,
RACINE,...).
•
ROUND: This function returns the numeric value given as the first argument
rounded to the number of digits passed in the second argument.
•
The syntax is ROUND(number; num_digits).
19
Pr. Wafae SABBAR
•
SUM: This function returns the sum of all the numerical values passed in
argument.
The syntax is: SUM(argument1;argument2;...). The arguments can be values.
numeric, but most often the arguments will be references to ranges of cells.
In this case, only the numeric values are taken into account in the calculation of the sum.
Examples of statistical functions
Most statistical functions are available (median, standard deviation, variance, ...). We do not
Let's detail in this paragraph that the most basic functions. Generally, the arguments will be
references to cell ranges like for the SUM function.
•
MAX: This function returns the largest numeric value from the list of arguments.
The syntax is: MAX(argument1;argument2;...).
•
•
MIN: Like MAX but MIN!
AVERAGE: This function returns the arithmetic mean of the numeric values in the list
without taking into account the other values.
The syntax is: AVERAGE(argument1;argument2;...).
•
•
Note: This function returns the number of numeric values in the arguments list.
The syntax is: NB(argument1;argument2;...).
NBVAL: This function returns the number of non-empty cells (containing values)
numerical or not) in the list of arguments.
The syntax is: COUNT(argument1;argument2;...).
20
Pr. Wafae SABBAR
4. The Lists
1. Definition
The list is the memorization in Excel of organized data, a list consists of a series
of records. All records have the same structure. The structure of a list is defined
by a certain number of fields. Each field has a name that corresponds to the column header.
Fields always have a value taken from a type, that is, the set of its values.
possibilities.
Example of a list
There are two representation modes for lists: table mode and grid mode. We switch from one
representation mode to the other using the Data--Grid menu.
•
•
The table mode is the usual mode as presented in the previous figure. A
record corresponds to a line of the list.
The mode displays a record entry to the user with the list of values of
these fields. For example:
21
Pr. Wafae SABBAR
2. Browse/search in a list
In table mode, we navigate the list using the arrow keys or the scroll bars; to
search, we can use the Edit--Find menu.
In grid mode, we navigate the list using the Previous and Next buttons or the
elevators; to search, specify the search criteria after pressing the button
Criteria.
3. Add/Delete/Edit records
In grid mode, these operations are facilitated using buttons: New to add,
Delete to delete, the changes are made in the grid, we can undo a
modification using the Restore button.
In table mode, these actions are performed by actions on the rows or cells:
•
To add a record, simply insert a row and then fill in the cells.
It is advisable to insert (in the list) rather than add (at the end of the list) a new line.
Indeed, when inserting a line, the size of the list is modified, and if you have named
your list, the name will now be on the list with the new record included.
•
•
Deleting a record involves removing a row.
Modifying a record involves changing the contents of the cells in the row.
22
Pr. Wafae SABBAR
4. Sort the records
Records can be organized in different orders. An order is defined by specifying criteria.
of three.
To apply sorting, simply place yourself anywhere in the list and use the Data menu--
Trier.
Specifications: you must specify the list area by its name or by referencing the range of
cells and specify the sorting criteria as in the following examples:
For example, the list of children can be sorted in alphabetical order of names, the criterion
of triage then:
What will be represented by:
field order number
1
Rising name
2. One may also wish for a display in the order of CSPs. As there are several
students of the same socio-professional category, we want these students to be ranked in alphabetical order.
The sorting criterion is:
What will be represented by:
23
Pr. Wafae SABBAR
field order number
1
2
CSP croissant
Rising name
3. Finally, if we want to visualize the list in order of age, from the youngest to the oldest, the criterion
of sorting is:
What we will represent by:
field number
order
1
Descending DateOfBirth
5. Filter/extract into a list
Filtering consists of only displaying records in the list that meet certain criteria.
Extracting consists of copying the sub-list of records that meet certain criteria to another.
place in the binder.
We can filter using automatic filters or advanced filters, we can extract using
elaborate filters.
Automatic filters
We use the Data menu -- Filter and the submenu auto filter. We can then filter on the
different fields. For example, we want to only see the list of students who were in
CM2 during the year 1994-1995, we then filter on the field An94/95, the filter criterion is:
24
Pr. Wafae SABBAR
What will be represented by:
AN94/95
CM2
The result obtained is presented in the figure below:
When filtering on multiple columns, we filter according to the conjunction of the criteria. For example,
if we filter on the field An94/95 who were in CM2 during the year 1994-1995, and on the
field Gender for male students, we obtain the list of male students who
were in CM2 during the year 1994-1995.
The filter criterion will then be represented by:
An94/95 sex
CM2
M
and we will obtain:
25
Pr. Wafae SABBAR
Elaborate filters.
The advanced filters allow for filters that cannot be created using the Filter tool.
automatic. These are the filters for which the filter criteria contain disjunctions (some
"or") or contain calculated expressions.
They also allow for extractions. For example, one wants to extract a
composed list:
•
•
students whose CSP code is less than 3,
and students from CSP equal to 5 who were in CM1 in 1994-1995.
This example is developed in the following paragraph, the result is presented in the figure
below.
26
Pr. Wafae SABBAR
Specifications of the developed filters.
It is necessary to specify the source area (the name or references of the range containing the list), the area of
criteria (the name or references of the range containing the filter criteria, the destination zone (the
cell from which we will organize the list). In the definition of the criteria, we use the convention
next: the criteria on the same line correspond to a conjunction, the criteria on one line
different from a disjunction. In our example, we would have:
•
•
•
zone source : listeélèves (nom donné à la liste)
criteria area: criteria area (name given to the range containing the criteria)
destination zone: result!A1 (from the first cell of a result sheet of
binder
•
criteria: the filter criterion is:
CSP An94/95
<3
5
CM1
3. The subtotals
The subtotals allow adding to the summary line list, for example, a count, a
average, a sum. The subtotals relate to groups. A group is formed by sequences.
records for which the value of a field is identical.
Warning: in order for the groups to be properly formed, it is necessary to sort beforehand on the
field on which the group must focus.
To create subtotals, use the Data--Subtotals menu.
Subtotal specifications: you must specify the source area, define the groups and thus the
sorting criteria, specify the synthesis function used among the functions Average, Sum, Count,
Nbval, Min, Max and the fields to which these functions apply.
For example, if we want to calculate the average of birth dates for each class of the year
1994-1995, we filter by the column An94/95, then we ask to perform the subtotal operation with the
average function on the DateNaiss field at each class change:
27
Dr. Wafae SABBAR
If we stick to what we currently know, we are tempted to simply write the formula
"=E2*B4" in cell B5.
What happens if we copy this formula into the neighboring cell?
The references used are relative references, so when copying, these references are
modified to always refer to the same relative cell position.
Thus, in B5, B4 actually refers to the cell located in the previous row in the same column.
After Copy-Pasting into the adjacent cell, the reference is modified to refer again to the
cell of the previous row and the same column, which gives C4. That's exactly what we wanted.
obtain.
Let's now examine the case of the reference B2 used in the formula in B5. It actually refers to
the cell located three rows up and three columns to the right. AfterCopy-Paste the
In C5 formula, this reference is modified to refer to the cell in the same position.
relative, namely cell F2!
Catastrophe! This is not at all what we wanted to achieve! How do we get out of this?
In cell B5, an absolute reference to cell E2 must be used.
Such a reference is obtained by preceding the letter of the column and the number of the row with a
$ sign: this gives the notation $E$2. This notation actually refers to the cell E2, and not to the
cell located three rows up and three columns to the right. During a Copy operation
Copying, the reference is not changed.
Ultimately, the correct formula to write in cell B5 is "=$E$2*B4".
After copying in C5, we get the formula "=$E$2*C4" (the absolute reference is not modified, the
relative reference is updated).

Absolute reference absolutely designates the row and column of the referenced cell.
is done by preceding the references with the symbol "$" for rows and columns (example: $E$2).
Relative reference, on the other hand, refers relatively to the row and column of the cell.
referenced (example: E2).
The mixed reference is a blend of the two reference modes seen earlier.
The mixed reference allows you to refer to the row absolutely and the column relatively.
(or the reverse): thus writing « =$C4 » in cell D5 references the cell located in column C
(absolute reference for the column using a "$" sign) and in the previous line
relative reference for the line, no "$" sign in front of the line number.
11
Pr. Wafae SABBAR
The result is presented below.
29
Pr. Wafae SABBAR
4. Resolution of a practical exercise
The table below shows the grades of some subjects of the students in a class.
In this specific case, we are called to calculate all the empty cells.
Note
info
Note from
chemistry
Note of
Physical
total
Average rank
individual
Mention
Coeff.
1
2.5
3
X
X
X
Sara
8
12
15
16
17
12
0
11
8
Mohcine
Bouchra
Meriam
Ahmed
samia
10
12
15
10
14
10
18
0
14
My class
X
Average < 8
→
weak
Average < 14
average < 16
average < 20
→
→
→
good enough
good
Average < 10 →
mediocre
passable
perfect
2) Select the function range and validate surok
Enter F3 which is the number whose rank we want.
4) Click in front of the reference and enter all the numbers that will be arranged while introducing '$':
F$3;F$4;F$5;F$6;F$7;F$8
5) If we need an ascending order (from 1 to 9), in front of 'order', we place '0' and validate.
If we need a descending order (from 9 to 1), in front of 'order', we put '1' and validate.
Place the mouse pointer over the fill handle of cell G3 and click while dragging.
mouse up to G8.
Calculation of the class average.
Select B9 and click surf(x)
2) Select the average function and validate
3) Enter B3: B8 (means from B3 to B8) and confirm
4) Place the mouse pointer over the fill handle of cell B9, then click and drag.
mouse up to F9.
Determination of the mention
1) Select H3 and click surf(X)
2) Select the 'if' function and validate
3) Enter the cell from which you want the mention 'F3'
4) Go to the formula bar, place the cursor between F3 and the closing parenthesis, continue typing
of
tell
luck
that one
ait :
=
yes
(F3 ;
yes
(F3<8 ;
‘Weak’; if(F3<10; ‘Mediocre’; if(F3<12; ‘Passable’; if(F3<14; ‘QuiteGood’; if(F3<16; ‘Good’; if(F3>16
"Very well".
5) Confirm by pressing the Enter key.
6) Placez le pointeur de la souris sur le poignet de recopie de la celluleH3puis cliquez tout en
sliding the mouse to H9.
31
Pr. Wafae SABBAR