麻辣家族討論版版 - Powered by Discuz! Board

標題: 分組排序 [打印本頁]

作者: chin15 時間: 2012-9-7 21:22 標題: 分組排序

本帖最後由 Hsieh 於 2012-9-10 21:51 編輯

這個公式頭痛了好久，現把資料簡化一下，懇請高人指導。[attach]12403[/attach]

作者: Hsieh 時間: 2012-9-8 01:48

回復 1# chin15

若有群組排列試試看

[attach]12409[/attach]

作者: chin15 時間: 2012-9-8 10:54

多謝超版回覆
可能我說得不夠清楚，不是定義名稱分組
我想用陣列公式表現，而且不下拉
同時選取d2:d18，輸入公式後按ctrl+shift+enter
這樣得到的陣列可作其他用途
這個公式對我難度頗高，目前班別欄是假合併，如果是真合併公式又該如何表現？
多謝耐心指導。

作者: ML089 時間: 2012-9-8 17:11

回復 1# chin15

本以為排名次應該是很簡單，仔細研究你的排名方式還滿不同，還沒有研究出來。

其中合併儲存格裡每格都有值，倒讓我很有興趣，以前找過網路沒找到如何設定，希望你能幫我回答此問題，
題目發問如下
http://forum.twbts.com/viewthread.php?tid=7748&extra=

作者: Hsieh 時間: 2012-9-9 00:57

回復 3# chin15
要直接取出分組內不重複分數排名已經讓人傷透腦筋，若再加上合併儲存格問題，相信內建函數是無法達成
純粹好玩寫個自定義函數，取得排名陣列
[attach]12420[/attach]

作者: ML089 時間: 2012-9-9 11:36

回復 3# chin15

重複名次不跳號(1、1、2、3、3、4、5 ...)的排名

班別欄是假合併，可以採用 MATCH(A2:A18,A2:A18,)*10^5+C2:C18 就可以重新分類編號
將重複分數濾除，可以採用 FREQUENCY(分類編號,分類編號)>0 濾除重複分數與貴式合併使用
貴式建議修改為，MMULT((...)*(...)*TRANSPOSE(INDEX(濾除重複分數,ROW(1:17))), ...)

全選式陣列輸入，同時選取d2:d18，輸入公式後按ctrl+shift+enter

班別欄是真合併時，重新分類編號我正在研究下式
LOOKUP("嫺",INDIRECT("A2:A"&ROW(2:18)),ROW(2:18))*10^5+C2:C18
單獨測試時OK，一組合後就產生錯誤，後續還要研究...，先PO出來也讓妳想一想。

作者: ML089 時間: 2012-9-9 17:29

回復 1# chin15

兩種解法提供參考
解1 : 版大原式 + FREQUENCY 濾除重複分數
解2 : 採用 MATCH + SMALL 不重複分數排序解法
[attach]12428[/attach][attach]12428[/attach]

作者: chin15 時間: 2012-9-9 19:23

真是要得！了不起！辛苦了，收下學習。謝謝！
先進若有時間也請一併指導a列真合併時的處理。

作者: ML089 時間: 2012-9-10 13:00

回復 8# chin15

班別欄是真合併時，重新分類編號可用下式
MATCH(ROW(2:19),IF(A2:A19<>"",ROW(2:19)))
程式構架同前

範列檔案如下
[attach]12436[/attach]

PS: 適用於班別欄是真合併時

作者: chin15 時間: 2012-9-10 20:04

本帖最後由 chin15 於 2012-9-10 20:06 編輯

感謝感謝，辛苦了，公式好複雜，我得分解開來慢慢理解學習。
真的不是普通的厲害，佩佩之至。

另外想請教版主，我找不到可以評分的地方，是不是只有版主可以評分？

作者: BigDog 時間: 2012-11-27 20:45

回復 chin15

若有群組排列試試看
Hsieh 發表於 2012-9-8 01:48

請教超版大:
SUMPRODUCT((a>C2)*(COUNTIF(a,a)>1))/2
紅色字體我不太明嘹,我看a的名稱等於總分的數值,"a>c2"這邏輯都是false,"countif(a,a)>1"也都等於false.
所以sumproduct(false*false)/2=0,可以請超版大解釋一下嗎？這個地方我哪裡判斷錯誤？謝謝！

作者: Hsieh 時間: 2012-11-27 21:44

回復 11# BigDog
定義名稱a是取得每班的總分範圍
SUMPRODUCT((a>C2)*(COUNTIF(a,a)>1))
以一年一班為例，a的範圍是C2:C4
a>C2測試每個總分比C2大的數有C3、C4
所以得到一個陣列{TRUE,FALSE,FALSE}
COUNTIF(a,a)>1則是計算a範圍內每個數値出現次數
得到陣列{1,1,1}>1就成了{FALSE,FALSE,FALSE}
SUMPRODUCT({TRUE,FALSE,FALSE}*{FALSE,FALSE,FALSE})
就成了TRUE*FALSE+FALSE*FALSE+FALSE*FALSE
=0+0+0
=0

作者: BigDog 時間: 2012-11-29 17:52

回復 BigDog
定義名稱a是取得每班的總分範圍
SUMPRODUCT((a>C2)*(COUNTIF(a,a)>1))
以一年一班為例，a ...
Hsieh 發表於 2012-11-27 21:44

拍謝,再次請教超版大,
問題一:
以一年一班為例，a的範圍是C2:C4, a>C2測試每個總分比C2大的數有C3、C4.
以一年三班為例，a的範圍是C9:C13, a>C9沒半個,那公式就都成為false,這裡我的觀念哪裡有問題？
問題二:
圖檔藍色字體的公式隨著儲存格而改變,那公式就會一直不成立?
[attach]13334[/attach]
謝謝!!

作者: Hsieh 時間: 2012-11-29 21:50

回復 13# BigDog

沒錯啊!
這是要加進來比C欄大的數値個數，C9是C9:C13中最大值
所以，將內建RANK函數取得的排名減去比該列C欄大的個數就是正確不重複排名

作者: BigDog 時間: 2012-11-30 14:41

回復 14# Hsieh

資質鈍悟,感謝回答. ^^"

作者: BigDog 時間: 2012-12-3 16:46

回復 14# Hsieh

請問超版大
(C2:C18<=TRANSPOSE(C2:C18) => 這公式不是全都TRUE嗎？不是很懂,為什麼有些會變FALSE.
(A2:A18=TRANSPOSE(A2:A18) => 這公式不是全都TRUE嗎？不是很懂,為什麼有些會變FALSE.

[attach]13382[/attach]

謝謝超版大.

作者: BigDog 時間: 2012-12-3 23:19

回復 1# chin15

chin15大大您好:
能否幫我解釋16f的問題嗎？謝謝!!

作者: BigDog 時間: 2012-12-4 16:50

回復 chin15

chin15大大您好:
能否幫我解釋16f的問題嗎？謝謝!!
BigDog 發表於 2012-12-3 23:19

已經看懂了@@"
花了一整天的時間,但要實際運用還真有些困難,要多多練習,謝謝各位的解說....

作者: pigchen 時間: 2013-3-12 09:46

謝謝版主適時解決我的疑問

作者: chhars 時間: 2014-12-11 12:11

成立與不成立可以回傳成文字或數值
這樣得出的資料，之後還能夠套入其他的東西

作者: aoao001 時間: 2015-7-27 08:39

試試看，研究一下

看看如何能解決

作者: hcm19522 時間: 2015-10-24 14:32

http://blog.xuite.net/hcm19522/twblog/351915358

作者: hcm19522 時間: 2015-10-24 19:13

本帖最後由 ML089 於 2015-10-24 19:27 編輯

D2:D18{=SUM(((((MATCH(MATCH(A$2:A$18,A$2:A$18,)*100+C$2:C$18,MATCH(A$2:A$18,A$2:A$18,)*100+C$2:C$18,)=ROW(B$2:B$18)-1)*C$2:C$18)*(MATCH(A$2:A$18,A$2:A$18,)=MATCH(A2,A$2:A$18,)))>C2)*1)+1

作者: hcm19522 時間: 2015-10-25 15:04

=SUMPRODUCT((((MATCH(A$2:A$18&C$2:C$18,A$2:A$18&C$2:C$18,)=ROW(B$2:B$18)-1)*(A$2:A$18=A2)*C$2:C$18)>C2)*1)+1

作者: hcm19522 時間: 2015-10-25 15:12

版大資料 A欄雖跨欄置中但A3,A4與A2同 ,A6,A7,A8與A5同...非空白另 22#有修改增列總和公式
http://blog.xuite.net/hcm19522/twblog/351915358

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)