Practical spreadsheet course
With Microsoft Excel program, versions 97 – 2000
Advanced Level
EXCEL
Index
–
LEVEL
] ADVANCED
1. Automatic filters
2. Advanced filters
3. Sort the data of a list
4. Subtotals
5. Introduction to dynamic tables
6. Dynamic tables based on lists
7. Dynamic tables based on multiple consolidation ranges
1. Audits
Topic 1: Excel Databases
Topic 2: Spreadsheet Analysis Tools
Topic 3: Solving Mathematical Problems
Topic 4: Paragraphs Format
one. The alignments
2. Apply indentations
3. Tabulations
4. Numbered and bulleted lists
5. Text in newspaper columns
6. Footnotes and end of document
7. Headers and footers
1. Introduction
2. Search Goal
3. Solver
2 – Advanced Level Excel
1
TOPIC
Databases
in Excel
1
Automatic Fitters
Automatic filters allow to display
a subset within a list with
just click on the mou-
button.
When we select the command
Filter in the Data menu, and then Auto-
Excel filter will place arrows
bles directly on the labels of co-
lumins of the list. If we click on
an arrow will appear a list with all
unique elements of the column. If
we select an item from the list
of a specific column, it will hide
automatically all the rows that do not
contain the selected value.
Let's see step by step how to create an
automatic filter.
We will start for our example of a
table like the one in the illustration, in which
several vendors are attributed to
finished products.
If we press any of those
arrows, a small
menu will be displayed whose contents will be the
lords contained in that column
only once.
Just choose a value from that
list so that the table goes to
only the entries that
refer to the selected value
swim (in our example we have
] chosen the name of a vendor-
dor).
We will place the cursor in some cell-
da inside the table so that the
application can identify it.
We will direct the cursor to the menu Da-
cough, and within it, to the option
Filters and AutoFilter.
Done this, next to the head-
two of each of the columns
of data of our table there will be
appeared a small button with
an arrow pointing downwards.
Trick: if you notice, the co-
luminaries that are
filtered have the button with
an arrow inside of
blue. Clicking
on it, displaying the
list, and choosing the op-
tion All we will remove the
filters applied.
We can combine different
filters,
to specify more and more the
object of our search, as well as
undo the screens already made (it is enough
to choose the option all in menu
3 – Excel Advanced Level
drop-down of the applied filter, or Mos-
all from the Data-Filters menu).
To cancel the auto-filtering, it is enough with
to go back to the menu Data, Filters,
and remove the mark of the option AutoFilter
by clicking on it.
Making use of the option Customize
that we find in all filters
we can establish more complex criteria
where they intervene, for example,
comparison criteria, creating enun-
] Show all those
that contain a number greater than 500.
Configure the
custom filter is
simple: in the Perso-
dialog box, select, first, the
comparison criteria in the menu des-
foldable, and then we will write
the desired value.
2
Advanced Fitros
We can resort to the advanced filters
two when the automatic ones are
insufficient to solve the pro
problems that may be posed to us:
example, to apply more than two criteria
to a single column, or to create criteria
resulting from a formula, or simple-
mind, to copy the filtered list into
a place different from the original listing.
When using an advanced filter, the
AutoFilter drop-down arrows do not
appear. Instead, the
criterion is specified in a range of criteria in the
worksheet. To change the form
in which the data is filtered, we will have to change the values of the
rite
and apply the filter to the
data again.
To use the advanced filters you must-
repeat the headers of the
In the two boxes that we see below
we can establish another criterion of
filtered: for example, if in the upper
we choose to show the rows that have
a value greater than 500, in the lower one
we can request that those
have a value less than 1000. Vere-
mos those between 500 and 1000.
Right between the two rows of criteria there are
a pair of option buttons represented by
by the conjunctions O and Y. The
option Y is enabled by default: for
that a row is displayed must
necessarily meet the two filtering criteria
. If only one fails, no
will appear in the list.
On the other hand, if we activate the "O" option,
simply with the fulfillment of one of
the two conditions the row will be shown-
da. For example, we can select
that those with
written in the cell have the word "Barcelo-
na" or those with "Girona".
columns that we will use in another
place in our spreadsheet.
In the rows under the labels of
criteria we must introduce the criteria
that we wish to search.
4 – Advanced Level Excel
filters.
the
B16: C17.
In our example,
Finally, we must indicate where
the filtered table should be inserted. Bas-
ta with which we indicate a single cell:
Excel will copy the data down to
from it. In our example, we will do
from A19.
We can also check the box Solo
unique records. In case of data re-
requested, they would not appear on the screen. In
our example, we will leave it deactivated
vada.
In this illustration we have introduced
a filter for the Tables column and for the
Chairs. To show the corresponding rows
in both columns
there should be a figure greater than 30.
Once we have entered the criteria,
we will place the cursor on any
place in the table original, we will go to the
menu Data, the option Filters and Filters
Advanced. A window will pop up as
this:
Finally, click on OK. In effect
to, as we can see in the image,
only those rows appear that in
Tables and Chairs have a number
higher than 30.
Naturally, the example is very simple.
From here you can elaborate condi-
as complex as you are interested.
As a little trick, I must tell you
that if instead of using the condition
"Y", that we have applied here, you wish
to see those that contain a number greater than
50 in either of the two columns,
you must write the criteria in rows
different, more or less, like this:
First we must determine
if we want the data to be filtered
on the original table , or that the filtered information
remains in another table. In
our case we will choose the option
Copy to another place.
The Range box in the list appears
with references to the
cells that contain the table to be filtered.
In principle they should be correct.
In the Criteria Range box, indicate
the cells in which they are
5 – Excel Advanced Level
indicate the criteria in the same row, such and
as we discussed before. Let's see the
example:
This assumption, applied to the example that
before we studied, would give the following-
you result:
In the on-screen help of the sheet of
calculation Excel you can find a
good collection of examples of advanced filters
. Below we reproduce some of them explained in
detail:
Several conditions in a single co-
lumna If you want to include more than one
condition in a column, we must
write them in different rows. For example
plo:
It will show those rows in which the
province column
the labels
"Barcelona", "Girona" or "Tarragona".
have
And this example will filter that in which
column Sales shows a 50, a 40 or a
30.
In this type of case we work with
a single column, and with the disjunctive
"O". If we wish, for example, to see the
records that have an amount less than
above 50 AND more than 30 we should
Attention: You can also specify
different
columns and present only
rows that meet all
conditions using the
AutoFilter command of the
Data menu.
Let's see another example in the same line.
In the following table
We wish to show those persons
of the "Administration" department that
have a payroll of more than 150,000
pesetas. We will have to write:
6 – Excel Advanced Level
On the other hand, if we want to see all
of Administration (whatever its number-
mine), or those who have a
payroll higher than 150000 (of any
departament
from Nettech Post http://bit.ly/2GMOcjc
No hay comentarios:
Publicar un comentario