iT邦幫忙

0

想請問SUMPRODUCT函數如何改寫成vba表示出來??

  • 分享至 

  • xImage

因為公司電腦仍是舊版2003所以只有SUMPRODUCT沒有新版的函數https://ithelp.ithome.com.tw/upload/images/20210116/20132376inj52sCxJ3.png雖然能用函數處裡,但是每次系統代出xls檔,手動輸入一次函數求解很慢,而且筆數一多就死當快十分鐘才跑完結果,所以產生用vba處裡的想法
圖片是範例檔,其中三者都第一次出現的資料只要代出一筆就好,後續如果三者都相符第一次出現的資料就代入累加,三者其中之一不相符就代入為新的一筆資料繼續判定累加

看其他參考資料可能要設三個變數 客戶 分店 卡別 三個都相符才去加總消費金額
最後在產出在指定的欄位

1.代入資料 https://ithelp.ithome.com.tw/upload/images/20210116/20132376izGvo1XwKB.png這步沒問題

2.設定變數並判定 這步有點卡住

3.加總 https://ithelp.ithome.com.tw/upload/images/20210116/20132376W45EhhuX1L.png這步應該是類似作法

4.指定產出位置

想請問有沒有高手可以從頭寫一個範例檔讓我學習看看??非常感謝!!!

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
0
rogeryao
iT邦超人 7 級 ‧ 2021-01-16 23:26:20
最佳解答

在 VBA 內下 SQL 處裡即可.
Excel 內的標題要變更為 : [卡別_A卡],[消費金額_A卡],[卡別_副卡],[消費金額_副卡]

CREATE TABLE [工作表1] (
[客戶姓名] Nvarchar(40) NULL ,
[分店名] Nvarchar(40) NULL ,
[卡別_A卡] Nvarchar(40) NULL,
[消費金額_A卡] float NULL ,
[卡別_副卡] Nvarchar(40) NULL ,
[消費金額_副卡] float NULL);
--
INSERT INTO [工作表1] ([客戶姓名], [分店名], [卡別_A卡],[消費金額_A卡],[卡別_副卡],[消費金額_副卡]) 
VALUES 
(N'王小明',N'鳳山店',N'A卡',1000,N'副卡',0),
(N'王小明',N'鳳山店',N'A卡',0,N'副卡',2000),
(N'王小明',N'前鎮店',N'A卡',4000,N'副卡',0),
(N'王小明',N'前鎮店',N'A卡',0,N'副卡',3000),
(N'王小明',N'前鎮店',N'A卡',5000,N'副卡',0),
(N'王小明',N'前鎮店',N'A卡',0,N'副卡',7000),
(N'王小明',N'小港店',N'A卡',6000,N'副卡',0),
(N'王小明',N'小港店',N'A卡',0,N'副卡',8000);
-- A卡
SELECT 客戶姓名,分店名,卡別_A卡 AS 卡別,SUM(消費金額_A卡) as 消費金額
FROM [工作表1] 
WHERE 客戶姓名 is not null
GROUP BY 客戶姓名,分店名,卡別_A卡
ORDER BY 客戶姓名,分店名,卡別_A卡
-- 副卡
SELECT 客戶姓名,分店名,卡別_副卡 AS 卡別,SUM(消費金額_副卡) as 消費金額
FROM [工作表1] 
WHERE 客戶姓名 is not null
GROUP BY 客戶姓名,分店名,卡別_副卡
ORDER BY 客戶姓名,分店名,卡別_副卡

Demo

下載 ACE : Microsoft Access Database Engine 2010 可轉散發套件
請參閱 : Excel 97-2003 Xls files with ACE OLEDB 12.0 using Microsoft.ACE.OLEDB.12.0
請參閱 : Excel 中正确使用SQL的姿势

這解決方法對於內建的2003版本有用嗎?公司內部使用的是沒有更新不聯網的,我去了解看看,感謝

rogeryao iT邦超人 7 級 ‧ 2021-01-18 08:49:59 檢舉

Excel 2003版本有用

0
paicheng0111
iT邦大師 5 級 ‧ 2021-01-16 11:28:13

VBA不會比較快。
可以考慮用陣列公式樞紐分析表

感謝,這些方式我都試過了,還是要手動拉取資料作業

0
richardsuma
iT邦大師 1 級 ‧ 2021-01-16 20:42:39

POWER BI

2003版有嗎?

0
海綿寶寶
iT邦大神 1 級 ‧ 2021-01-17 11:42:17

在你的使用情境下
1.SUMPRODUCT 簡單易用彈性大,缺點是速度慢
2.VBA 速度快,缺點是撰寫/修改困難

有沒有「簡單易用,速度又像 VBA 一樣快」的方式?
有,答案就是「樞紐分析」

https://ithelp.ithome.com.tw/upload/images/20210117/20001787UukBJsEeNM.png
如果上圖的結果你可以接受

再來就是要付出的「代價」
就是把原始資料調整成如下格式
https://ithelp.ithome.com.tw/upload/images/20210117/20001787eo849Lpjyf.png

如果我的判斷沒錯
樞紐分析絕對會是你現在及未來最佳的選擇

感謝回答,樞紐確實是最平易近人的數據分析工具,只是數據源我無法掌控處理,才會退而求其次用函數去解決

froce iT邦大師 1 級 ‧ 2021-01-18 11:30:52 檢舉

你可以寫VBA去獲取資料然後調用樞紐分析...

我要發表回答

立即登入回答