Transcript ch091.pptx

目錄
9.1 先備知識
9.2 TQC 208 Booking
9.3 TQC 210 Product
9.1Excel 中不為人所知的秘訣
 9.1.1文字串接(聯結多欄文字)免函數
• 「&」：運算子，可連接或合併多欄文字
B
C
B
C
D
我愛
朝陽
我愛
朝陽
我愛朝陽
=B1&C1
9.1Excel 中不為人所知的秘訣
 9.1.2聯結多欄文字
• 使用 「””」將文字及特殊符號包起來
B
C
B
C
我愛
朝陽
我愛
銀管一A
= B1&”◎“&C1
D
我愛◎銀管一A
9.1Excel 中不為人所知的秘訣
 9.1.3 LEFT函數
• LEFT 會傳回文字字串中的第一個字元或前幾個字
元，視所指定的字元數而定
• 語法：LEFT(text,num_chars)
D
我愛◎銀管一A
E
我愛
= LEFT(D1,2)
9.2 Booking
 「Booking」工作表
Booking id
Hotel
Hotel Izukyu
Hotel Tokinosumika
Katase Hiina
Shimoda Seaside Hotel
Slow House Villa
Sun Hotel
Kurofune Hotel
Kurofune Hotel
Prince Hotels Resorts
Shimoda Jyuraku Hotel
Shimoda Seaside Hotel
Gotemba Kogen
Booking id
Hotel
I1-0106-3-CALLUS Hotel Izukyu
T1-0107-3-JAYME Hotel Tokinosumika
Katase Hiina
K1-0106-4-van ROOIJEN
Shimoda Seaside Hotel
S2-0120-3-van DYKEN
S5-0113-2-HERBST Slow House Villa
S6-0118-4-SEXTON Sun Hotel
K2-0123-3-MOSES Kurofune Hotel
K2-0128-4-POTEC Kurofune Hotel
P1-0123-4-ZWERINGPrince Hotels Resorts
S1-0201-3-WANG Shimoda Jyuraku Hotel
S2-0207-3-DUFOUR Shimoda Seaside Hotel
G1-0201-4-ROMEROGotemba Kogen
Booking date
Check in
Check out
Room no.
No. of roomer
2009/1/3
2009/1/4
2009/1/4
2009/1/10
2009/1/10
2009/1/14
2009/1/16
2009/1/17
2009/1/18
2009/1/22
2009/1/26
2009/1/27
2009/1/6
2009/1/7
2009/1/6
2009/1/20
2009/1/13
2009/1/18
2009/1/23
2009/1/28
2009/1/23
2009/2/1
2009/2/7
2009/2/1
2009/1/8
2009/1/9
2009/1/9
2009/1/22
2009/1/14
2009/1/21
2009/1/25
2009/1/31
2009/1/26
2009/2/3
2009/2/9
2009/2/4
106
103
612
A204
208
219
201
205
802
311
B205
372
1
2
3
2
1
1
2
2
2
4
4
3
Booking date
Check in
Check out
Room no.
No. of roomer
2009/1/3
2009/1/4
2009/1/4
2009/1/10
2009/1/10
2009/1/14
2009/1/16
2009/1/17
2009/1/18
2009/1/22
2009/1/26
2009/1/27
2009/1/6
2009/1/7
2009/1/6
2009/1/20
2009/1/13
2009/1/18
2009/1/23
2009/1/28
2009/1/23
2009/2/1
2009/2/7
2009/2/1
2009/1/8
2009/1/9
2009/1/9
2009/1/22
2009/1/14
2009/1/21
2009/1/25
2009/1/31
2009/1/26
2009/2/3
2009/2/9
2009/2/4
106
103
612
A204
208
219
201
205
802
311
B205
372
1
2
3
2
1
1
2
2
2
4
4
3
First name
CALLUS
JAYME
van ROOIJEN
van DYKEN
HERBST
SEXTON
MOSES
POTEC
ZWERING
WANG
DUFOUR
ROMERO
First name
CALLUS
JAYME
van ROOIJEN
van DYKEN
HERBST
SEXTON
MOSES
POTEC
ZWERING
WANG
DUFOUR
ROMERO
Sur name
Ashley
Carlos
Manon
Amy
Stefan
Katy
Ed
Camelia
Klaas-Erik
Luna
Simon
Rogerio
Sur name
Ashley
Carlos
Manon
Amy
Stefan
Katy
Ed
Camelia
Klaas-Erik
Luna
Simon
Rogerio
9.2 Booking
 「Bill List」工作表
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
G1-0904-4-ADKINS
G1-0910-3-PARRY
G1-1106-3-SPANNEBERG
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
G1-0904-4-ADKINS
G1-0910-3-PARRY
Name
Room no.
Nights
Price
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
ADKINS Amanda
PARRY Stephen
Amount
Room no.
Nights
372
372
270
369
268
369
266
271
269
3
1
3
3
2
2
2
3
2
Price
3,800
3,800
2,300
3,200
3,200
3,200
2,300
2,300
4,000
Amount
$11,400
$3,800
$6,900
$9,600
$6,400
$6,400
$4,600
$6,900
$8,000
9.2 Booking
9.2.1在「Booking」工作表中,利用VLOOKUP、TEXT函
數及Hotel、HotelDetail、Check_in、Check_out
、First_name 範圍名稱,設定「Booking id」欄位
中的資料顯示為：
Hotel id_Check in (mmdd)-Days-First name
以Hotel Izukyu 為例：
Hotel id -> I1
Check in -> 2009/1/15
Check out -> 2009/1/16
Days = Check out – Check in + 1
First name -> ESPOSITO
9.2 Booking
Booking id
Hotel
Hotel Izukyu
Hotel Tokinosumika
Katase Hiina
Shimoda Seaside Hotel
Slow House Villa
Sun Hotel
Booking date
Check in
Check out
Room no.
No. of roomer
2009/1/3
2009/1/4
2009/1/4
2009/1/10
2009/1/10
2009/1/14
2009/1/6
2009/1/7
2009/1/6
2009/1/20
2009/1/13
2009/1/18
2009/1/8
2009/1/9
2009/1/9
2009/1/22
2009/1/14
2009/1/21
106
103
612
A204
208
219
1
2
3
2
1
1
First name
CALLUS
JAYME
van ROOIJEN
van DYKEN
HERBST
SEXTON
Sur name
Ashley
Carlos
Manon
Amy
Stefan
Katy
=VLOOKUP(Hotel,HotelDetail,2,0)&"-"&TEXT(Check_in,"mmdd")&
"-"&Check_out-Check_in+1&"-"&First_name
Booking id
I1-0106-3-CALLUS
T1-0107-3-JAYME
K1-0106-4-van ROOIJEN
S2-0120-3-van DYKEN
S5-0113-2-HERBST
S6-0118-4-SEXTON
Hotel
Hotel Izukyu
Hotel Tokinosumika
Katase Hiina
Shimoda Seaside Hotel
Slow House Villa
Sun Hotel
Booking date
Check in
Check out
Room no.
No. of roomer
2009/1/3
2009/1/4
2009/1/4
2009/1/10
2009/1/10
2009/1/14
2009/1/6
2009/1/7
2009/1/6
2009/1/20
2009/1/13
2009/1/18
2009/1/8
2009/1/9
2009/1/9
2009/1/22
2009/1/14
2009/1/21
106
103
612
A204
208
219
1
2
3
2
1
1
Hotel id_Check in (mmdd)-Days-First name
K1-0106-4-van ROOIJEN
First name
CALLUS
JAYME
van ROOIJEN
van DYKEN
HERBST
SEXTON
Sur name
Ashley
Carlos
Manon
Amy
Stefan
Katy
9.2 Booking
在「Bill List」工作表中：利用VLOOKUP函數與
Booking 範圍名稱
(提示：除Booking 範圍名稱外，不可使用其它範圍
名稱)
2.「Name」欄位：
依據Booking id 搜尋 Booking範圍名稱，傳回
First name 與 Surname 中間間隔一位空白
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
Name
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
9.2 Booking
9.2.2「Name」欄位：
Booking id
Name
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
=VLOOKUP(A2,Booking,8,0)&"
"&VLOOKUP('Bill List'!A2,Booking,9,0)
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
9.2 Booking
9.2.3「Room no.」欄位：
依據Booking id 搜尋 Booking範圍名稱，傳回
Room no.
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
Room no.
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Room no.
372
372
270
369
268
369
9.2 Booking
9.2.3「Room no.」欄位：
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
Room no.
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
=VLOOKUP(A2,Booking,6,0)
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Room no.
372
372
270
369
268
369
9.2 Booking
9.2.4「Nights」欄位：
依據Booking id 搜尋 Booking範圍名稱，傳回
Check_in 及 Check_out,
Night = Check_out – Check_in
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
Room no.
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Nights
372
372
270
369
268
369
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Room no.
Nights
372
372
270
369
268
369
3
1
3
3
2
2
9.2 Booking
9.2.4「Nights」欄位：
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
Room no.
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Nights
372
372
270
369
268
369
=VLOOKUP(A2,Booking,5,0)-VLOOKUP('Bill
List'!A2,Booking,4,0)
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Room no.
Nights
372
372
270
369
268
369
3
1
3
3
2
2
9.2 Booking
9.2.5「Price」欄位：
依據Booking id 前三碼，串接Room no. 後，比對
LookupPrice範圍內的Price
(提示：利用LEFT 函數)
Booking id
Name
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
G1-0904-4-ADKINS
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
ADKINS Amanda
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
Room no.
Nights
372
372
270
369
268
369
266
271
3
1
3
3
2
2
2
3
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
Price
Room no.
Nights
372
372
270
369
268
3
1
3
3
2
Price
3,800
3,800
2,300
3,200
3,200
9.2 Booking
9.2.5「Price」欄位：
Booking id
Name
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
G1-0904-4-ADKINS
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
ADKINS Amanda
Room no.
Nights
372
372
270
369
268
369
266
271
3
1
3
3
2
2
2
3
Price
=VLOOKUP(LEFT(A2,2)&“_"&C2,LookupPrice,2,0)
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
Room no.
Nights
372
372
270
369
268
3
1
3
3
2
Price
3,800
3,800
2,300
3,200
3,200
9.2 Booking
9.2.6「Amount」欄位：
Amount = Nights * Price
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
Room no.
Nights
372
372
270
369
268
369
3
1
3
3
2
2
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
Price
Amount
3,800
3,800
2,300
3,200
3,200
3,200
Room no.
Nights
372
372
270
369
268
369
266
3
1
3
3
2
2
2
Price
3,800
3,800
2,300
3,200
3,200
3,200
2,300
Amount
$11,400
$3,800
$6,900
$9,600
$6,400
$6,400
$4,600
9.2 Booking
9.2.6「Amount」欄位：
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
Room no.
Nights
372
372
270
369
268
369
3
1
3
3
2
2
Price
Amount
3,800
3,800
2,300
3,200
3,200
3,200
=D2 * E2
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
Room no.
Nights
372
372
270
369
268
369
266
3
1
3
3
2
2
2
Price
3,800
3,800
2,300
3,200
3,200
3,200
2,300
Amount
$11,400
$3,800
$6,900
$9,600
$6,400
$6,400
$4,600
9.2 Booking
9.2.7套用「表格樣式中等深淺 9 」，套用後再轉換為範圍
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
G1-0904-4-ADKINS
G1-0910-3-PARRY
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
ADKINS Amanda
PARRY Stephen
Room no.
Nights
372
372
270
369
268
369
266
3
1
3
3
2
2
2
Room no.
Nights
372
372
270
369
268
369
266
271
269
3
1
3
3
2
2
2
3
2
Price
3,800
3,800
2,300
3,200
3,200
3,200
2,300
Price
3,800
3,800
2,300
3,200
3,200
3,200
2,300
2,300
4,000
Amount
$11,400
$3,800
$6,900
$9,600
$6,400
$6,400
$4,600
Amount
$11,400
$3,800
$6,900
$9,600
$6,400
$6,400
$4,600
$6,900
$8,000
9.2 Booking
9.2.7.1套用「表格樣式中等深淺 9 」
常用/樣式/格式化表格/表格樣式中等深淺 9
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
G1-0904-4-ADKINS
G1-0910-3-PARRY
G1-1106-3-SPANNEBERG
G1-1126-4-PEARSON
G1-1206-2-WITTSTOCK
G1-1209-2-CROCKER
I1-0106-3-CALLUS
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
BECCARI Andrea
BECCARI Andrea
ADKINS Amanda
PARRY Stephen
SPANNEBERG Torsten
PEARSON Todd
WITTSTOCK Charlene
CROCKER Ian
CALLUS Ashley
Room no.
Nights
372
372
270
369
268
369
266
271
269
369
368
368
270
106
3
1
3
3
2
2
2
3
2
2
3
1
1
2
Price
3,800
3,800
2,300
3,200
3,200
3,200
2,300
2,300
4,000
3,200
2,300
2,300
2,300
1,000
Amount
$11,400
$3,800
$6,900
$9,600
$6,400
$6,400
$4,600
$6,900
$8,000
$6,400
$6,900
$2,300
$2,300
$2,000
9.2 Booking
9.2.7.2套用後再轉換為範圍
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
G1-0719-3-BECCARI
G1-0806-3-BECCARI
G1-0904-4-ADKINS
G1-0910-3-PARRY
G1-1106-3-SPANNEBERG
G1-1126-4-PEARSON
G1-1206-2-WITTSTOCK
G1-1209-2-CROCKER
I1-0106-3-CALLUS
Name
Room no.
ROMERO Rogerio
ARNARSON Orn
Booking id
OHLIN Mattias
G1-0201-4-ROMERO
VOELKER Sandra
G1-0307-2-ARNARSON
KENKHUIS Johan
G1-0502-4-OHLIN
BECCARI Andrea
G1-0519-4-VOELKER
BECCARI Andrea
ADKINS Amanda
PARRY Stephen
SPANNEBERG Torsten
PEARSON Todd
WITTSTOCK Charlene
CROCKER Ian
CALLUS Ashley
Nights
372
3
372
1 Name
270
3
ROMERO Rogerio
369
3
ARNARSON Orn
268
2
OHLIN Mattias
369
2
VOELKER Sandra
266
2
271
3
269
2
369
2
368
3
368
1
270
1
106
2
作法： 常用/設計/轉換為範圍
Booking id
G1-0201-4-ROMERO
G1-0307-2-ARNARSON
G1-0502-4-OHLIN
G1-0519-4-VOELKER
G1-0702-3-KENKHUIS
Name
ROMERO Rogerio
ARNARSON Orn
OHLIN Mattias
VOELKER Sandra
KENKHUIS Johan
Price
3,800
3,800
2,300
3,200
3,200
3,200
2,300
2,300
4,000
3,200
2,300
2,300
2,300
1,000
Amount
$11,400
$3,800
Room
no.
$6,900
372
$9,600
372
$6,400
270
$6,400
369
$4,600
$6,900
$8,000
$6,400
$6,900
$2,300
$2,300
$2,000
Room no.
Nights
372
372
270
369
268
3
1
3
3
2
Nights
3
1
3
3
Price
3,800
3,800
2,300
3,200
3,200
Price
Amount
3,800
3,800
2,300
3,200
$11,400
$3,800
$6,900
$9,600
Amount
$11,400
$3,800
$6,900
$9,600
$6,400
9.2 Booking
9.2.8列印設定
9.2.8.1縮放比例調整為80%
作法：版面配置/版面設定/頁面
9.2 Booking
9.2.8列印設定
9.2.8.2頁首：中央為工作表名稱
作法：版面配置/版面設定/[頁首/頁尾]
9.2 Booking
9.2.8列印設定
9.2.8.3頁尾：中央選擇「第X頁，共X頁」格式
作法：版面配置/版面設定/[頁首/頁尾]
9.2 Booking
9.2.8列印設定
9.2.8.5列印格線
作法：版面配置/版面設定/工作表/列印格格線[打勾]
9.3 Product
 「Product」工作表
ProductID Region
FD
USA
StockoutDate
2009/2/27
LocalAmount
UnitPrice
0.35
SalesQty
5,000
SalesAmount
0.35
7,000
0.45
1.45
0.7
5,000
5,000
5,000
UnitPrice
0.35
0.35
0.45
1.45
0.7
SalesQty
5,000
7,000
5,000
5,000
5,000
02-2009 Total
FD
USA
2009/1/29
01-2009 Total
FD
FD
FD
USA
USA
USA
2009/2/27
2009/2/27
2009/2/27
02-2009 Total
ProductID
FD
FD
FD
FD
FD
Region
AUS
AUS
AUS
AUS
AUS
StockoutDate
2009/2/27
2009/1/29
2009/2/27
2009/2/27
2009/2/27
LocalAmount
1,724.22
2,413.91
2,216.86
7,143.21
3,448.45
SalesAmount
1,750.00
2,450.00
2,250.00
7,250.00
3,500.00
9.3 Product
9.3.1 「Region」欄位：將欄位內的USA換成AUS
9.3 Product
9.3.2 「Product ID」欄位：刪除欄位內所有的空白
1. 尋找目標：按一個空白鍵
2. 取代成：
3. 全部取代；1191筆資料
9.3 Product
9.3.3 刪除含有「Total」的所有列。
1. 尋找有Total 的欄位
2. 將尋找到有Total的欄位全部選取
3. 常用/刪除/刪除工作表列
ProductID
FD
FD
FD
FD
FD
FD
FD
FD
FD
ISD
ISD
Region
AUS
AUS
AUS
AUS
AUS
AUS
AUS
AUS
AUS
Korea
Korea
StockoutDate
2009/2/27
2009/1/29
2009/2/27
2009/2/27
2009/2/27
2009/3/27
2009/3/22
2009/3/7
2009/2/27
2009/1/31
2009/10/8
LocalAmount
UnitPrice
0.35
0.35
0.45
1.45
0.7
0.7
2.85
2.85
0.7
4.9
4.9
SalesQty
5,000
7,000
5,000
5,000
5,000
9,000
7,000
5,000
5,000
3,046
14,000
SalesAmount
9.3 Product
9.3.4 設定範圍名稱「A1~G151」，以頂端列為範圍名稱
1.選取A1~G151
2.
9.3 Product
9.3.5 「SalesAmount」欄位：
SalesAmount 為 UnitPrice 與 SalesQty 的乘積，
指定為千分位樣式，二位小數位數，利用範圍名稱
建立公式。
9.3 Product
9.3.6 「LocalAmount」欄位：
依據Region 搜尋該地區幣值(Rate 範圍名稱)，將
SalesAmount 換算成當地貨幣，利用範圍名稱建立
公式。
=VLOOKUP(Region,Rate,2,0)*SalesAmount
ProductID
FD
FD
FD
FD
FD
FD
FD
FD
Region
AUS
AUS
AUS
AUS
AUS
AUS
AUS
AUS
StockoutDate
2009/2/27
2009/1/29
2009/2/27
2009/2/27
2009/2/27
2009/3/27
2009/3/22
2009/3/7
LocalAmount
1,724.22
2,413.91
2,216.86
7,143.21
3,448.45
6,207.20
19,656.14
14,040.10
UnitPrice
0.35
0.35
0.45
1.45
0.7
0.7
2.85
2.85
SalesQty
5,000
7,000
5,000
5,000
5,000
9,000
7,000
5,000
SalesAmount
1,750.00
2,450.00
2,250.00
7,250.00
3,500.00
6,300.00
19,950.00
14,250.00
9.3 Product
9.3.7 設定框線：
內框線為最細實線，外框為次粗外框線，第1、2列
之間為雙線。
1.將A1~G151全選，設定框線格式
2. 選取A1~G1，設定底框為雙框線
9.3 Product
9.3.8 列印設定：
1. 設定奇偶頁不同
9.3 Product
9.3.8 列印設定：
2. 頁首：顯示列印日期與工作表名稱之間需間隔一
位空白。奇數頁在右側，偶數頁在左側。
9.3 Product
9.3.8 列印設定：
3. 頁尾：無論奇偶頁，均在中間顯示頁碼。
9.3 Product
9.3.8 列印設定：
4. 列印標題的範圍為第1列。