iT邦幫忙

0

求解:EXCEL 的 (FILTER 函數)如何橫向輸出 ,其它函數解也行。

說明:希望能將原字筆的訂單編號移到右圖,求解程式碼與邏輯說明。

目前:使用FILTER 函數只能直向輸出卡住..........資料很多筆需下拉。
https://ithelp.ithome.com.tw/upload/images/20201016/201312209BirY0ktID.jpg

PS:如果找不到資料希望不要有#N/A,謝感各位大神囉。

3
ccenjor
iT邦新手 3 級 ‧ 2020-10-17 19:55:11
最佳解答

F3:
=IFERROR(OFFSET($B$1,SUMPRODUCT(LARGE((($C$3:$C$9=$E3)*(ROW($C$3:$C$9))),COUNTIF($C$3:$C$9,$E3)+6-COLUMN()))-1,,),"")
再按CTRL+SHIFT+ENTER鍵
將公式複製到F3:J5儲存格。
https://ithelp.ithome.com.tw/upload/images/20201017/20109881MTGD7Q1DHW.png

看更多先前的回應...收起先前的回應...

全部成功,無話可說..........一個字「神」

說明一下,公式的邏輯吧!!

ccenjor iT邦新手 3 級 ‧ 2020-10-18 18:50:01 檢舉

=IFERROR(OFFSET($B$1,SUMPRODUCT(LARGE((($C$3:$C$9=$E3)*(ROW($C$3:$C$9))),COUNTIF($C$3:$C$9,$E3)+6-COLUMN()))-1,,),"")
公式1:COUNTIF($C$3:$C$9,$E3)
統計C3:C9儲存格中含有E3儲存格內容的儲存格筆數。

公式2:LARGE((($C$3:$C$9=$E3)*(ROW($C$3:$C$9))),公式1+6-COLUMN())
傳回C3C3:C9儲存格中等於E3儲存格內容的儲存格位置中第N(公式1運算結果值加6減掉欄編號,這是因為從F欄開始,所以需先加6再減掉欄編號,來調整抓取順序)大的值。

公式3:SUMPRODUCT(公式2)
統計公式2運算結果中數值的合計。

公式4:OFFSET($B$1,公式3-1,,)
傳回從B1儲存格開始移動N(公式3-1)列的儲存格內容。
公式5:IFERROR(公式4,"")
如果公式4運算結果傳回錯誤訊息,則不填入資料,否則填入公式4運算結果。

非常詳細,謝謝分享!!

1
海綿寶寶
iT邦大神 1 級 ‧ 2020-10-16 22:18:39

先 FILTER 再欄轉列

你的問題如果是工作上需要的話就好
如果只是你自己找的題目來練習
我真的想再奉勸你一次

把時間花在更美好的事物上
/images/emoticon/emoticon10.gif

大師!!
程式小白的我,手上的武器很有限,用EXCEL(GOOGLE試算表),情非得已......><(目前只能活在,GOOGLE的生態圈裡)串接谷哥給的功能。也希望其它的程式學的快一點,但距離能實際應用還差一大截!

我對你或是 Excel 這個工具沒有任何意見
而是在你的「題目」
很多人來這裡問作業;很多人來這裡問工作上碰到的問題;
解決這些問題都對發問人「有實際而直接的幫助」

如果你這些問題都不是實際應用上碰到的
那我就會祝你好運
Good luck.

不是作業,是工作處理資料時遇到的問題。因為資料也不知道能不能公開,所以「題目」是我想的範例或是引用教學範例(有時沒有教到我需要的)。但基本上這裡的網友解答都能解決我遇到的問題。

1
akissiva
iT邦新手 5 級 ‧ 2020-10-16 23:17:21
F3={INDEX(B$3:B$7,MATCH(1>0,(C$3:C$7=E3),))}
'F3公式可向下填充

G3={IF(ISNA(INDEX($B$3:$B$7,MATCH(1,($C$3:$C$7=$E3)*($B$3:$B$7>F3),))),"",IF(F3="","",INDEX($B$3:$B$7,MATCH(1,($C$3:$C$7=$E3)*($B$3:$B$7>F3),))))}
'G3公式可向下填充及往右拉填充

'兩條打完都要用ctrl+shift+enter產生{},不可直接enter

高手先感謝!!
F3成功 但下拉有#N/A (遇的困難?)
我的想法:
F3=IF(E3="","",XLOOKUP(E3,C:C,B:B,""))
........................................
G3成功 下拉沒有#N/A
.........................................
H3
我的想法:
=IF(E3="","",XLOOKUP(E3,C:C,B:B,"",,-1))

有辨法改成:當H3=G3或F3時變成空格嗎?
以上是我想到應急的方法,不過會遇到很多限制問題就是了。G3的公式邏輯比較實在可以通用不會受限制。

^_^看你寫的公式我感覺我好像有進步(你的公式我還要找資料學習才能看懂,目前還沒有看明白)

akissiva iT邦新手 5 級 ‧ 2020-10-18 11:41:26 檢舉

公式填上時,所有的「列7」(B$7、C$7)有改成資料最後一列的所在嗎? 不然應該沒有問題

改好了
原來G3的公式也可以向右拉....
資料沒有問題 「讚」

1
richardsuma
iT邦大師 1 級 ‧ 2020-10-17 01:47:06

換點新鮮的

註解一下囉,不太了解你的意思!!

power bi可以理解成進階版的excel吧,但多很多功能,我也是前陣子才剛接觸

謝謝你的提供

我要發表回答

立即登入回答