EXCEL 公式運算

2015-05-10 6:09 am

E-Card 會員, 總數: 5000

工作表 1
F1 姓別
G1 出生日期
H1 會籍類別
J1 登記日期
J2 16/3/2015
K1 有效日期
K2 16/3/2021
L1 電郵地址

工作表 2 (E-Card 會員電郵記錄)
A1 會員姓名
B1 會籍類別
C1 會員證號碼
D1 有效日期
E1 電郵地址
F1 發出日期
G1 重發日期

提供上述資料，以便下列查詢：

1) 會籍有效期為六年(由申請日期開始計算)，工作表1 怎樣設定公式 M1 續會日期(項目名稱), M2答案公式自動計算,如6年後又再續會怎樣設定框架項目及公式運算？N1 申請次數(項目名稱) ,N2 儲存格答案是K1+M1=2次,所以答案是2,如似類推，6年後再續會就會是答案3.怎樣設定框架項目及公式運算？

2) 工作表 4
要計算累積會員人數
B1及C1 項目名稱: 少年會員14歲或以下
B2 項目名稱：男
C2 項目名稱：女
D1及E1 項目名稱: 青年會員15歲或以上
D2 項目名稱：男
E2 項目名稱：女
F1及G1項目名稱：總數
F2 項目名稱: 男
G2 項目名稱: 女
H2 項目名稱: 合共
F3 是公式自動計算答案
G3 是公式自動計算答案
H3 是公式自動計算答案

A3 項目名稱 : 上月累積(a) B3,C3,D3,E3是公式自動計算答案
A4 項目名稱：今月累積(b) B4,C4,D4,E4是公式自動計算答案
A5 項目名稱：今月續會(c) B5,C5,D5,E5是公式自動計算答案
A6 項目名稱：今月續會(c) B6,C6,D6,E6是公式自動計算答案
A7 項目名稱：今月到期(d) B7,C7,D7,E7是公式自動計算答案
A8 項目名稱：小計(a)+(b)+(c)-(d) B8,C8,D8,E8是公式自動計算答案
A9 項目名稱：總數B9+C9=少年男女總數, D9+E9=青年男女總數

工作表3
是否要先設定今月續會及今月到期項目名稱？對嗎？怎樣設定公式運算？
其實現在我不清楚怎樣設定框架及公式運算，如有不足請指教及提點。謝！
另重發日期次數次數怎樣設定框架及公式運算，請教教，謝！

回答 (1)

Eddie

2015-05-12 1:29 am

✔ 最佳答案

由於會員可以多次續會，而最後續會記錄會影響對該會員會藉有效性和可進行的操作，建議插入一新「工作表 2會員續會記錄」，保留會藉資料歷史記錄，原來工作表2至4則順延至3至5。
新「工作表 2會員續會記錄」欄位如下：
A1 會員姓名
B1 會籍類別
C1 會員證號碼
D1今次會藉屆滿日期
E1 續會日期
F1下次會藉屆滿日期
G1 通知已續會日期
其中F欄儲存格可以用公式，在F2公式是： =DATE(YEAR(E2)+6,MONTH(E2),DAY(E2)) 或=DATE(YEAR(D2)+6,MONTH(D2),DAY(D2))，視乎規則是用上次會藉屆滿日期加6年抑或按續會日期加6年。至於需否G欄視乎有沒有必要步驟通知會員，確認其會藉已更新。

另在工作表 1上，修改欄位如下：
F1 性別(原為「姓別」)
G1 出生日期
H1 會籍類別
J1 首次登記日期
K1 有效日期
L1 電郵地址
M1 最近續會日期
N1會藉屆滿日期
O1 申請續會次數
P1 刪除會藉日期
其中M、N兩欄是運用LOOKUP公式由工作表 2取得每位會員最後一筆續會資料、O欄則加總得出續會次數，假設每名會員續會不多於15次，續會記錄不多於15x5000=75000筆，三道公式是：
M2 =LOOKUP(2,1/(工作表 2!$A$2:$A$75000=$D2),$E$2:$E$75000)
N2 =LOOKUP(2,1/(工作表 2!$A$2:$A$75000=$D2),$F$2:$F$75000)
O2 =COUNTIF(工作表 2!$A$2:$A$75000, $D2)

2015-05-11 17:31:29 補充：
餘下公式不夠字數容納。

👍 0 👎 0