, Simulaties
SORT
Indeling &
Symbol : One
Data
Column
Sort & Filter Click AZ
group
Az Ascending Multiple columns
A
Descending Data
to sort
Sort Choose first column
Use + toaddmorecolums
you
want
FILTER
Symbol :
1
1 Click
Data
inacellinyour
sort & Filter
dataset .
Filter
group
Filteryourtxceldataif 2 Use the arrow to
filteryour data
ou only want to
records that
displaycertain criteria
measure
CONDITIONALFORMATTING
-
Symbol :
1 Horne
group Condition at form
Styles .
2 Several
formatting options based on the ,
absolute / relative values of the cell
PNOT TABLES
Symbol :
1
2
lnsert
Select at
Piuottable
New work sheet
A- Piuottableisa Fields between
3
Droog areas
tool tocakulate , 4 Press • to
charge setting like for calculators
summerize and
data
analyse
FUNCTIOAS
Symbol -1 : Sum : Calculaties the values of alt cells in a certain
range .
Formula : =
Sum ( number 1 ; number 2 ; . . .
)
:(akulates the
Average
Values of cells
average
certain in a
value of the
range
Formula : =
AVERAGE ( number 1 ; number 2; . . .
)
Gunt : function returns counter cells that contain
number Values
Formula : = COUNT ( value 1 .
'
value 2 .
.
.
. .
)
ij
, Counter : function returns the Court of non -
blank
cells ,
Numbers text
and form at a return
containing text
1 , ,
logica ,
Values
(
" "
)
,
Formula : =
COUNTA ( value 1 ;
ing value 2 )
empty .
.
. .
String
.
IF : The if functies test and return some for TRUE result and
runs a
Logic at value a ,
another for a
FALSE result .
For example ,
to
"
pas
"
scores above 70 : =
IF ( AL > 70
.
"
pass
"
,
"
Fail
"
)
Formula IF ( if if false )
:
logica1 test ; value true ; value
=
- - _ - _
average )
"
( A1
" "
VB : IF < 5 ;
"
smaller than Above checks wether the
average
=
;
smaller than
"
value of A1 is 5 IF yes then value "
Smaller than is
.
,
average
returned is return ed
" "
otherWise Above
, Average
AND : The excel AND function function used to require condition at the
is a
logicat more than one same
time .
AND returns either TRUE or FALSE
OR : The or function is a
logic at function to test multiple conditions at the same time .
OR returns
either TRUE or FALSE
SUMIF COUNTIF and AVERAGE IF
,
are a combi nation of the IF -
function and respect
ively the SUM .
(OUNT and AVERAGE function
For muta SUMIF ( range ; criteria ; [sum
: =
])
range -
SUMIF ( A1 : -15 ; < & 5 A1 : -15 ) soms up ah cells with a value smaller than
" "
VB 5
'
: =
, .
In this cells At and AH smaller than 5 The result value wilt be 3+2=5
cases are .
ing
The first reference is the of the condition the second reference is the want
range ,
range you
to summarize When those the same the second reference be dropped
.
ranges are ,
can
, ✓ & XLOOKUP
The VLOOKUP and XLOOKUP format as used to look up values
are in
ranges or
arrays
Formula VLOOKUP ( IOOKUP value ; 1- able ; (Ol index [ ])
array num ; range Iookup
: =
- - - - -
tfyou want to know the
age of Someone ,
you look op the information in the 2nd column of the
1- able in column A &B
LOOKUP value Inge
•
=
-
•
Table A2 : 135 ( or A :B which re Presents the
array
=
-
entire columns )
•
(01 index ( start at the
- _
nam = 2
you Counting
column of the look UP value -
which
Range lookup FALSE 100 King for
'
means are
you
=
_
,
an exact match .
TRUE wilt Search for the closed match .
default vlookup wilt per form exact match
By an
Forma la : ✗ Lookup ( IOOKUP value ; lookup array ;
- -
return -
array ;
[ If not found] ; [ match mode ] ;
-
[ Search mode ] ) -
lfyou want to know the of Someone / ook op the information in the 2nd column of the
age .
you
1- able in column A and B
LOOKUP value Hans
•
=
-
•
LOOKUP array -
= A2 : -15 (or A:A ,
which represents the
entire column
'
Return B. 2 :B5 ( or B :B which re Presents the entire
array
=
-
,
column
If not found ( option at ) be Fill ed with term which
•
- -
= Can a
would like to show it the look UP value cannot be found
you -
Match mode (option at ) how to match IOOKUP value
specify
•
:
- -
the Values in look up
against -
array .
Search mode (optioneel) the Search mode
•
to use
specify
=
-
SORT
Indeling &
Symbol : One
Data
Column
Sort & Filter Click AZ
group
Az Ascending Multiple columns
A
Descending Data
to sort
Sort Choose first column
Use + toaddmorecolums
you
want
FILTER
Symbol :
1
1 Click
Data
inacellinyour
sort & Filter
dataset .
Filter
group
Filteryourtxceldataif 2 Use the arrow to
filteryour data
ou only want to
records that
displaycertain criteria
measure
CONDITIONALFORMATTING
-
Symbol :
1 Horne
group Condition at form
Styles .
2 Several
formatting options based on the ,
absolute / relative values of the cell
PNOT TABLES
Symbol :
1
2
lnsert
Select at
Piuottable
New work sheet
A- Piuottableisa Fields between
3
Droog areas
tool tocakulate , 4 Press • to
charge setting like for calculators
summerize and
data
analyse
FUNCTIOAS
Symbol -1 : Sum : Calculaties the values of alt cells in a certain
range .
Formula : =
Sum ( number 1 ; number 2 ; . . .
)
:(akulates the
Average
Values of cells
average
certain in a
value of the
range
Formula : =
AVERAGE ( number 1 ; number 2; . . .
)
Gunt : function returns counter cells that contain
number Values
Formula : = COUNT ( value 1 .
'
value 2 .
.
.
. .
)
ij
, Counter : function returns the Court of non -
blank
cells ,
Numbers text
and form at a return
containing text
1 , ,
logica ,
Values
(
" "
)
,
Formula : =
COUNTA ( value 1 ;
ing value 2 )
empty .
.
. .
String
.
IF : The if functies test and return some for TRUE result and
runs a
Logic at value a ,
another for a
FALSE result .
For example ,
to
"
pas
"
scores above 70 : =
IF ( AL > 70
.
"
pass
"
,
"
Fail
"
)
Formula IF ( if if false )
:
logica1 test ; value true ; value
=
- - _ - _
average )
"
( A1
" "
VB : IF < 5 ;
"
smaller than Above checks wether the
average
=
;
smaller than
"
value of A1 is 5 IF yes then value "
Smaller than is
.
,
average
returned is return ed
" "
otherWise Above
, Average
AND : The excel AND function function used to require condition at the
is a
logicat more than one same
time .
AND returns either TRUE or FALSE
OR : The or function is a
logic at function to test multiple conditions at the same time .
OR returns
either TRUE or FALSE
SUMIF COUNTIF and AVERAGE IF
,
are a combi nation of the IF -
function and respect
ively the SUM .
(OUNT and AVERAGE function
For muta SUMIF ( range ; criteria ; [sum
: =
])
range -
SUMIF ( A1 : -15 ; < & 5 A1 : -15 ) soms up ah cells with a value smaller than
" "
VB 5
'
: =
, .
In this cells At and AH smaller than 5 The result value wilt be 3+2=5
cases are .
ing
The first reference is the of the condition the second reference is the want
range ,
range you
to summarize When those the same the second reference be dropped
.
ranges are ,
can
, ✓ & XLOOKUP
The VLOOKUP and XLOOKUP format as used to look up values
are in
ranges or
arrays
Formula VLOOKUP ( IOOKUP value ; 1- able ; (Ol index [ ])
array num ; range Iookup
: =
- - - - -
tfyou want to know the
age of Someone ,
you look op the information in the 2nd column of the
1- able in column A &B
LOOKUP value Inge
•
=
-
•
Table A2 : 135 ( or A :B which re Presents the
array
=
-
entire columns )
•
(01 index ( start at the
- _
nam = 2
you Counting
column of the look UP value -
which
Range lookup FALSE 100 King for
'
means are
you
=
_
,
an exact match .
TRUE wilt Search for the closed match .
default vlookup wilt per form exact match
By an
Forma la : ✗ Lookup ( IOOKUP value ; lookup array ;
- -
return -
array ;
[ If not found] ; [ match mode ] ;
-
[ Search mode ] ) -
lfyou want to know the of Someone / ook op the information in the 2nd column of the
age .
you
1- able in column A and B
LOOKUP value Hans
•
=
-
•
LOOKUP array -
= A2 : -15 (or A:A ,
which represents the
entire column
'
Return B. 2 :B5 ( or B :B which re Presents the entire
array
=
-
,
column
If not found ( option at ) be Fill ed with term which
•
- -
= Can a
would like to show it the look UP value cannot be found
you -
Match mode (option at ) how to match IOOKUP value
specify
•
:
- -
the Values in look up
against -
array .
Search mode (optioneel) the Search mode
•
to use
specify
=
-
