有網友問到：在 Excel 中有一個資料表(如下圖左)，其中的『資料』欄位如果順序被打亂，如何讓『數值』欄位正確的對應(如下圖右)？
觀察下圖中的BBB資料順序雖改變，但是對應的數值，其出現順序仍依原順序呈現。
【公式設計與解析】
本例為說明和觀察時的方便性，特別使用「輔助」欄位。
儲存格F2：
｛=SMALL(IF($A$2:A19=D2,ROW($A$2:A19),FALSE),COUNTIF($D$2:D2,D2))｝
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 自動加上「｛｝」。
(第1式) IF($A$2:A19=D2,ROW($A$2:A19),FALSE)：比對第一個儲存格A2開始的儲存格陣列，如果與儲存格D2相同者，傳回其列號(ROW函數傳回的數值)，否則傳回 FALSE。
(第2式) COUNTIF($D$2:D2,D2)：計算第一個儲存格D2開始的儲存格陣列中和儲存格D2相同者有幾個。
SMALL(第1式,第2式)：在陣列公式中，經由第1式傳回的「列號」陣列，找出第幾個較小的列號(例如：儲存格D3的AAA是第2個)。
儲存格E2：=OFFSET($B$1,F2-1,,,)
透過 OFFSET 函數依相對位置取得儲存格內容。
複製儲存格F2:E2，貼至儲存格E2:E19。
註：完整的E欄公式
儲存格E2：｛=OFFSET($B$1,SMALL(IF($A$2:A19=D2,ROW($A$2:A19),FALSE),
COUNTIF($D$2:D2,D2))-1,,,)｝
文章標籤
全站熱搜
Hi Vincent老師您好 謝謝您那麼熱血熱心的幫大家解答，我也從您的BLOG學了不少東西。 這個題目，我有另外一個解答，貼上來，我想函式應該還有可以調整的地方，再麻煩老師您指教 A、B、D欄位皆不變維持，F欄則不需要，E欄改成以下陣列公式： =SMALL(($A$2:$A$18=D2)*($B$2:$B$18),COUNTIF($D$2:$D$18,D2)-COUNTIF(D2:$D$18,D2)+1+COUNTA($A$2:$A$18)-COUNTIF($A$2:$A$18,D2)) 說明如下： part1 ($A$2:$A$18=D2)*($B$2:$B$18) 表示符合AAA的值的陣列，會顯示{111;112;113;114;115;0;0;0;0;0;0;0;0;0;0;0;0} 所以 small(part1,(part2+part3))指令，可以抓出上述陣列中(part1)的第XX小(part2+part3) part2 COUNTIF($D$2:$D$18,D2)-COUNTIF(D2:$D$18,D2)+1 計算「資料」欄位中的重複次數 part3 COUNTA($A$2:$A$18)-COUNTIF($A$2:$A$18,D2) 屬於part1中為0的部分數量
謝謝你，很好喔。
哈，改成用lagre好像簡單點 E欄變成 =LARGE(($A$2:$A$18=D2)*($B$2:$B$18),COUNTIF($D$2:D2,D2))
能解決問題的答案都是好答案。
Thx. 學習了
留言消失了@@
不知為何，你的留言被Pixnet自動列為垃圾留言了，我已取消。