關於篩選2個條件以上自動產生排序問題?

hairyhead

hairyhead
個人積分：5分
文章編號：58877343

5分

樓主

2016-01-12 14:54

以前有請教過篩選的問題
=SUMIF(A2:A100,"A部*",B2:B100)

最近要弄一個總表卻卡在要篩選2個以上
且想要按照人名排序不知道該怎麼做下一步才好

關於篩選2個條件以上自動產生排序問題?

如果有多個工作表(12個月份共有12分頁)
在最後一個總表時
該怎麼做可以讓公式自動去抓2個以上的絛件產生呢?
例如…
A部門 A君 = 1月的業積
A部門 B君 = 1月的業積
B部門 A君 = 1月的業積 (名字剛好跟A部門的A君重覆，但部門不同)
B部門 C君 = 1月的業積

這部份該怎麼讓表自動按照部門的名字排序加總呢?
(自動產生排序)

謝謝

2016-01-12 14:54 發佈

文章關鍵字條件排序問題

Mystique Hsiao

Mystique Hsiao
個人積分：7623分
文章編號：58880533

7623分

2樓

2016-01-12 19:53

hairyhead wrote:
該怎麼做可以讓公式自動去抓2個以上的絛件產生呢?

這樣的方式不知是否可行？
（第一列的人員部門／姓名仍須手動輸入）

原理：用函數判斷『-』左／右側的條件是否同時成立！

B2：
=SUMPRODUCT(IF('T-1'!$H$3:$H$17=LEFT(B$1,FIND("-",B$1)-1),1,0),IF('T-1'!$G$3:$G$17=RIGHT(B$1,LEN(B$1)-FIND("-",B$1)),1,0),'T-1'!$F$3:$F$17)
（輸入完畢請以 Ctrl+Shift+Enter 結束，否則無法查詢）

說明：
紅字（$H$3:$H$17, $G$3:$G$17, $F$3:$F$17）分別為部門，人員姓名，業績金額的範圍！
（可自行調整）

綠字（T-1）為各月工作表名稱，函數向下複製時需更改，向右複製則不變！
（尚未想到複製時不需自行更改的方式）

註：函數建議先向下複製，並修改綠字部份，再全部直接向右複製！

My Interior Knowledge is Extraordinaire

hairyhead

hairyhead
個人積分：5分
文章編號：58886349

5分

樓主

2016-01-13 10:58

Mystique Hsiao wrote:
這樣的方式不知是否...(恕刪)

剛剛有試過提供的公式
紅字（$H$3:$H$17, $G$3:$G$17, $F$3:$F$17）

原T-1的工作表
紅字（$I$9:$I$100, $H$9:$H$100, $H$9:$F$100）

I = 部門名稱
H = 人名
F = 單筆總金額

CTRL+SHIFT+ENTER 公式輸入結束

但是... 出現結果是 0 (我是用A名做搜尋，他1月共有20筆)

我這邊是不是哪裡寫錯了呢?
(B的部分是否也須修改呢? 應該不用不到修改吧?)

如果想用其它方式來做會不會比較簡單呢?
例如...

直式改為橫式呈列?

以下先用直式呈列說明

A3:A14 = 月份(1至12月)
B2:...H2....Z2... = 篩選2個條件(部門、名字)，然後只呈顯名字往右排列(如果改橫式就是向下排序)
B3:B14 = 篩選2個符合條件呈現單月金額加總

因為考慮到手輸部門、人名
有時候不一定由同一個人輸入資料
如果漏掉沒注意輸入就少算

在想... 如果只篩選1個條件的話是不是會比較簡單?
(重覆名字就乾脆讓改為XXX-85、XXX-75之類做判別)

謝謝

Mystique Hsiao

Mystique Hsiao
個人積分：7623分
文章編號：58889744

7623分

4樓

2016-01-13 15:42

hairyhead wrote:
剛剛有試過提供的公式
紅字（$H$3:$H$17, $G$3:$G$17, $F$3:$F$17）

原T-1的工作表
紅字（$I$9:$I$100, $H$9:$H$100, $H$9:$F$100）

但是... 出現結果是 0 (我是用A名做搜尋，他1月共有20筆)

我這邊是不是哪裡寫錯了呢?

紅字要改為 F

所以公式為：
=SUMPRODUCT(IF('T-1'!$I$9:$I$100=LEFT(B$1,FIND("-",B$1)-1),1,0),IF('T-1'!$H$9:$H$100=RIGHT(B$1,LEN(B$1)-FIND("-",B$1)),1,0),'T-1'!$F$9:$F$100)

hairyhead wrote:
如果想用其它方式來做會不會比較簡單呢?
例如...

直式改為橫式呈列?

以下先用直式呈列說明

A3:A14 = 月份(1至12月)
B2:...H2....Z2... = 篩選2個條件(部門、名字)，然後只呈顯名字往右排列(如果改橫式就是向下排序)
B3:B14 = 篩選2個符合條件呈現單月金額加總

不敢說本人的方式一定最簡單，
但做出來的表格直接就可以看到『某部門-某人』的單月／年度業績！

若用您說的方式，做出來的表格反而容易變得雜亂，
而且使用到不同函數的欄位增加，後續維護／修改時更麻煩，
若是其中某個地方改錯或漏改，除錯更不容易！

hairyhead wrote:
因為考慮到手輸部門、人名
有時候不一定由同一個人輸入資料
如果漏掉沒注意輸入就少算

在想... 如果只篩選1個條件的話是不是會比較簡單?
(重覆名字就乾脆讓改為XXX-85、XXX-75之類做判別)

其實部門／人名只需要於第一次輸入，
並不需要每次重新輸入（除非人員有增減）！

若用代碼區別重覆的姓名，不但函數的設計上可能更複雜，
而且出來的表格容易讓看的人搞不清楚，還要再去詢問／確定誰是誰！
（因為有可能同一個人每次的代碼都不同）

在設計／規劃表格或函數時，不能只顧慮到自己的簡便．
更要從其他使用人員或是看表格者的角度去考量！

My Interior Knowledge is Extraordinaire

hairyhead

hairyhead
個人積分：5分
文章編號：58903188

5分

樓主

2016-01-14 17:55

Mystique Hsiao wrote:
紅字要改為 F所以...(恕刪)

後來想想真的像您說的容易造成混亂...

還是按照原設計方向走比較清楚

瞭解感謝您的指教

Mystique Hsiao

Mystique Hsiao
個人積分：7623分
文章編號：58903356

7623分

6樓

2016-01-14 18:10

hairyhead wrote:
後來想想真的像您說的容易造成混亂...
還是按照原設計方向走比較清楚

有不同的構想其實是好事，畢竟人容易有盲點，
透過各種角度／方式去思考，或許能找到更好的解決方法，
我也是透過參考別人的經驗，學習到不少新方法，新概念！

My Interior Knowledge is Extraordinaire

hairyhead

hairyhead
個人積分：5分
文章編號：58922236

5分

樓主

2016-01-16 12:40

Mystique Hsiao wrote:
有不同的構想其實是好...(恕刪)

再請教一下
=SUMPRODUCT(IF('T-1'!$H$3:$H$17=LEFT(B$1,FIND("-",B$1)-1),1,0),IF('T-1'!$G$3:$G$17=RIGHT(B$1,LEN(B$1)-FIND("-",B$1)),1,0),'T-1'!$F$3:$F$17)

這個矩陣公式
如果要使用在同個工作表
是否還可以使用呢?

剛試了一下
發現它對於有合併儲存格會不能使用此公式

所以合併儲存格要套用這個公式是無解了嗎?

謝謝