今天是中華民國100年1月1日, 農曆春節也快到了, 假設你正在揪團購買年貨, 為了收集團員的訂單, 你建立一張表格如下
姓名
|
項目
|
數量
|
單價
|
總價
|
王五
|
仙楂丸
|
6
|
10
|
60
|
李四
|
木耳
|
5
|
100
|
500
|
趙六
|
木耳
|
4
|
100
|
400
|
梁二
|
香菇
|
10
|
300
|
3000
|
趙六
|
蒟蒻
|
1
|
65
|
65
|
王五
|
仙楂丸
|
3
|
10
|
30
|
梁二
|
香菇
|
5
|
300
|
1500
|
梁二
|
開心果
|
2
|
70
|
140
|
王五
|
蒟蒻
|
5
|
65
|
325
|
梁二
|
龍眼乾
|
4
|
220
|
880
|
丁一
|
香菇
|
8
|
300
|
2400
|
梁二
|
開心果
|
9
|
70
|
630
|
梁二
|
仙楂丸
|
4
|
10
|
40
|
梁二
|
花生
|
1
|
50
|
50
|
丁一
|
香菇
|
9
|
300
|
2700
|
李四
|
蒟蒻
|
2
|
65
|
130
|
趙六
|
蒟蒻
|
10
|
65
|
650
|
現在你想知道有多少人訂購, 訂購了多少種東西, 你會如何做?
方法很多, 淺一點的, 先排序, 手工算一下; 深一點的用個樞紐分析表, 還是要手工算.
姓名
|
項目
|
數量
|
總價
|
丁一
|
香菇
|
17
|
5100
|
丁一 合計
|
17
|
5100
| |
王五
|
仙楂丸
|
9
|
90
|
蒟蒻
|
5
|
325
| |
王五 合計
|
14
|
415
| |
李四
|
木耳
|
5
|
500
|
蒟蒻
|
2
|
130
| |
李四 合計
|
7
|
630
| |
梁二
|
仙楂丸
|
4
|
40
|
花生
|
1
|
50
| |
香菇
|
15
|
4500
| |
開心果
|
11
|
770
| |
龍眼乾
|
4
|
880
| |
梁二 合計
|
35
|
6240
| |
趙六
|
木耳
|
4
|
400
|
蒟蒻
|
11
|
715
| |
趙六 合計
|
15
|
1115
| |
總計
|
88
|
13500
|
那有自動的方法可以直接顯示有5個人, 購買了7種東西嗎? 有的. 用CountIf.
假設上面表1從A1格開始, 那CountIf(A2:A18,A2)=3, 也就是說王五有3筆訂單. 沒錯, 我們是要算有幾個團員下單, 不是要算那個團員有幾筆訂單. 所以要另想方法.
假若把每一筆都算一次呢? 也就是下表, 用自動填滿, 記得公式為CountIf($A$2:$A$18,A2).
姓名
|
次數
|
王五
|
3
|
李四
|
2
|
趙六
|
3
|
梁二
|
7
|
趙六
|
3
|
王五
|
3
|
梁二
|
7
|
梁二
|
7
|
王五
|
3
|
梁二
|
7
|
丁一
|
2
|
梁二
|
7
|
梁二
|
7
|
梁二
|
7
|
丁一
|
2
|
李四
|
2
|
趙六
|
3
|
我們發現王五被算了3次, 每次有3筆訂單, 李四被算了2次, 每次有2筆訂單. 也就是說有N筆訂單的人會被算N次. 這樣利用 N*1/N=1特性, 我們可以算出有幾個人. 也就是把公式倒數, 1/CountIf($A$2:$A$18,A2), 然後加總就可以得到想要的結果了.
姓名
|
次數
|
王五
|
0.3333333
|
李四
|
0.5
|
趙六
|
0.3333333
|
梁二
|
0.1428571
|
趙六
|
0.3333333
|
王五
|
0.3333333
|
梁二
|
0.1428571
|
梁二
|
0.1428571
|
王五
|
0.3333333
|
梁二
|
0.1428571
|
丁一
|
0.5
|
梁二
|
0.1428571
|
梁二
|
0.1428571
|
梁二
|
0.1428571
|
丁一
|
0.5
|
李四
|
0.5
|
趙六
|
0.3333333
|
總計
|
5
|
排序一下, 就會比較清楚了
姓名
|
次數
|
合計
|
丁一
|
0.5
|
1
|
丁一
|
0.5
| |
王五
|
0.3333333
|
1
|
王五
|
0.3333333
| |
王五
|
0.3333333
| |
李四
|
0.5
|
1
|
李四
|
0.5
| |
梁二
|
0.1428571
|
1
|
梁二
|
0.1428571
| |
梁二
|
0.1428571
| |
梁二
|
0.1428571
| |
梁二
|
0.1428571
| |
梁二
|
0.1428571
| |
梁二
|
0.1428571
| |
趙六
|
0.3333333
|
1
|
趙六
|
0.3333333
| |
趙六
|
0.3333333
| |
總計
|
5
|
5
|
為了便於擴充訂單, 可以把總計移到最頂端, 加總的範圍記得放大, 例如到B100.
總計
|
5
|
姓名
|
次數
|
王五
|
0.333333
|
李四
|
0.5
|
趙六
|
0.333333
|
梁二
|
0.142857
|
趙六
|
0.333333
|
王五
|
0.333333
|
梁二
|
0.142857
|
梁二
|
0.142857
|
王五
|
0.333333
|
梁二
|
0.142857
|
丁一
|
0.5
|
梁二
|
0.142857
|
梁二
|
0.142857
|
梁二
|
0.142857
|
丁一
|
0.5
|
李四
|
0.5
|
趙六
|
0.333333
|
到此, 大致完成了. 最後, 要把這些公式合併, 這樣就可以更有彈性的運用. 現在在總計那格寫上
=sumproduct(1/CountIf(A3:A100,A3:A100 & "")) -1
你會發現可以得到相同的結果. 其中CountIf 用了陣列模式, 同時為了計算空白, 加上了 & "", 加總的部份為了陣列, 改為 sumproduct, 最後把多算的空白減回來. 完成.
5
|
7
|
88
|
13500
| |
姓名
|
項目
|
數量
|
單價
|
總價
|
王五
|
仙楂丸
|
6
|
10
|
60
|
李四
|
木耳
|
5
|
100
|
500
|
趙六
|
木耳
|
4
|
100
|
400
|
梁二
|
香菇
|
10
|
300
|
3000
|
趙六
|
蒟蒻
|
1
|
65
|
65
|
王五
|
仙楂丸
|
3
|
10
|
30
|
梁二
|
香菇
|
5
|
300
|
1500
|
梁二
|
開心果
|
2
|
70
|
140
|
王五
|
蒟蒻
|
5
|
65
|
325
|
梁二
|
龍眼乾
|
4
|
220
|
880
|
丁一
|
香菇
|
8
|
300
|
2400
|
梁二
|
開心果
|
9
|
70
|
630
|
梁二
|
仙楂丸
|
4
|
10
|
40
|
梁二
|
花生
|
1
|
50
|
50
|
丁一
|
香菇
|
9
|
300
|
2700
|
李四
|
蒟蒻
|
2
|
65
|
130
|
趙六
|
蒟蒻
|
10
|
65
|
650
|
沒有留言:
張貼留言