其他說明:目前該公司的資訊系統因為某些因素，目前資訊系統尚末有建置相關功能，但該服務專員手頭上有一份 " VIP名單 " 的Excel檔案，資訊系統也能轉每天 " 來店會員 " 的Excel檔案。
臨時解決方案:
使用 Excel 內建 COUNTIF 公式來呈現 VIP客群 是否有到店消費
範例測試環境，OS是XP，使用Office2010的Excel
先把 VIP名單 跟 來店會員 2個excel放到電腦桌面
先把 來店會員 這個Excel打開
再打開 VIP名單的 Excel，把公式貼到E2欄位
公式 : =COUNTIF([來店會員.xls]工作表1!$B:$B,A2)
公式內的欄位對應，請看不同顏色的線，詳細說明最後會有文字加強
點圖2下，圖會放大
把滑鼠移到E2欄位右下角，原滑鼠符號變實心十字時，點滑鼠右鍵2下
就能快速複製公式到最後，1 就表示VIP有出現在 來店會員的Excel裡1次
http://office.microsoft.com/zh-tw/excel-help/HP010342346.aspx
引述微軟網頁說明如下:
COUNTIF 函數
說明
COUNTIF 函數會計算一範圍內，符合您所指定之單一準則的儲存格數目。 例如，您可以計算所有以特定字母開始的儲存格，或是可以計算所有包含大於或小於您所指定之數字的儲存格。 例如，假設您工作表的 A 欄中含有任務清單，而 B 欄中是各項任務之指派人員的名字。 您可以使用 COUNTIF 函數，計算某個人員的名稱出現在 B 欄的次數，如此一來便可以判斷已指派給該人員多少任務。 例如：
=COUNTIF(B2:B25,"Nancy")
語法
COUNTIF(range, criteria)
COUNTIF 函數語法具有下列引數：
range 必要。 這是要列入計算的一個或多個儲存格，包括數字或名稱、陣列，或含有數字的參照。 空白或文字值會被忽略。
criteria 必要。 定義要將哪些儲存格列入計算的數字、運算式、儲存格參照或文字字串。 例如，準則可以用 32、">32"、B4、"apples" 或 "32" 表示。
其他補充說明:
測試過程中，重新開檔案有時公式會變的比較長，如下面這樣 ，是因為範例中有參照到外部檔案的部份，簡單說就是因為用2個excel的關係。
=COUNTIF('C:\Documents and Settings\登入電腦的帳號\桌面\[來店會員.xls]工作表1'!$B$1:$B$65536,A2)
=公式(單引號 包 路徑 [ EXCEL.XLS ]excle的工作表名稱 單引號 警探號 excle裡的範圍 , 比對值)
微軟外部參照說明如下
微軟網址:
http://office.microsoft.com/zh-tw/excel-help/HP010102338.aspx
引述微軟網頁
建立外部參照的方法
如果您使用儲存格參照建立外部參照，也可以套用公式至此資訊。藉由切換各種類型的儲存格參照，也可以控制外部參照移動時要連結哪些儲存格。例如，如果您使用相對參照，在移動外部參照時，所連結的一個或多個儲存格便會變更，以反映工作表的新位置。
當來源活頁簿開啟時，外部參照會在中括號 ([ ]) 中包含活頁簿的名稱，接著是工作表名稱、驚嘆號 (!)，及公式所在的儲存格。例如，以下公式從名為 Budget.xls 的活頁簿中新增儲存格 C10:C25。
外部參照
=SUM([Budget.xlsx]Annual!C10:C25)
當來源關閉時，外部參照將包括完整路徑。
外部參照
=SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)
2015/06/17 新增
比對2個excel，會員編號相符且購物日期跟退出會員日期比較後是否小於7天
一個是會員資料的excel，另一個是購物日期的名單excel
第一個條件比會員編號
第二個條件比購物日期是跟退出會員日期 ，有可能當天買完就退會員
第二個條件比購物日期 - 1是跟退出會員日期，有可能買完後過一天才退會員
以此往後推
公式，這不一定是最好的解法，有空再找比較好的方法，貼在會員名單的excel上
=COUNTIFS([購物明細.xlsx]購物明細!$A:$A,A10,[購物明細.xlsx]購物明細!$K:$K,E10)+COUNTIFS([購物明細..xlsx]購物明細!$A:$A,A10,[購物明細.xlsx]購物明細!$K:$K,E10-1)+COUNTIFS([購物明細.xlsx]購物明細!$A:$A,A10,[購物明細.xlsx]購物明細!$K:$K,E10-2)
要一直用 + 串公式直到 E10-6 ，這樣就能找出小於7天的資料
第二個條件值比較麻煩，可以這樣下條件 ">="&E10 ，要用&才能再放其他函數，但試過好像只能比一個值，有試過用 { } 包起來，前面要加sum才有用，但{"yes","no"}只能放固定值，不能直擒放欄位{E10-1,E10-2}像這樣
用 + 串起2個公式，符合2個條件，會出現2
第一個是比會員編號
第二個是比較小於等於退出會員日
另一組是
第一個是比會員編號
第二個是比較的大於退出會員日 用 -7 在得到購買日期於小於退出會員日7天內
=COUNTIFS([購物明細.xlsx]購物明細!$A:$A,A10,[購物明細.xlsx]購物明細!$K:$K,"<="&E10)+COUNTIFS([購物明細.xlsx]購物明細!$A:$A,A10,[購物明細
.xlsx]購物明細!$K:$K,">"&E10-7)
第1組比較公式
“<=” & E10
，如果E10
= 2015-05-25
第2組比較公式
“>” & E10 –
7 ，這邊用 + 串公式，結果會是2 因為OR，如果用* 就會是AND 就會等於1
比對日期
25、24、23、22、21、20、19
，包含25號共7天
如果第2組改
“> =” & E10 –
7 ，就會多一天到18號這樣
符號 *
表示AND，放在2個公式中間，跟AND是一樣的意思，要符合2個條件
符號 +
表示OR，放在2個公式中間，跟OR是一樣的意思，至少要符合1個條件
符號 &
在COUNTIF這邊有點像引用
函數 ，原本只能用放值 ，但可以用
& 來放
函數，但在 COUNTIF的值這欄，只能放一個值進來比較，不然就是要這樣比COUNITF(範圍,{“值1”,
“值2”})，用{}包多個值，測試過好像不能放欄位E10的值
可以比較到小時的部份
可以比較到小時的部份
會計算到小時，這邊用 * 等於AND，符合2個COUNTIFS條件才會等於1，但重覆時，好像會等於4。
=COUNTIFS([購物明細.xlsx]購物明細!$H:$H,A9,[購物明細.xlsx]購物明細!$B:$B,"<="&F9) * COUNTIFS([購物明細.xlsx]購物明細!$H:$H,A9,[購物明細.xlsx]購物明細!$B:$B,">"&F9-2)
F9
- 2，系統會計算減2天，就是48小時
這樣是2天內
2015-5-23 17:00
|
2015-5-25 16:16
超過2天
|
2015-5-23 10:00
2015-5-25 16:16
前面要加sum才有用，{"yes"，"no"}
=SUM((COUNTIFS(會員.xlsx!$G:$G,A9,會員.xlsx!$B:$B,"<="&F9,會員.xlsx!$H:$H,{"96017","96020"})*COUNTIFS(會員.xlsx!$G:$G,A9,會員.xlsx!$B:$B,">"&F9-7)
)+
(COUNTIFS(會員.xlsx!$G:$G,A9,會員.xlsx!$B:$B,"<="&F9,麻醉.xlsx!$I:$I,{"96017","96020"})*COUNTIFS(會員.xlsx!$G:$G,A9,會員.xlsx!$B:$B,">"&F9-7)
))
沒有留言:
張貼留言