iT邦幫忙

1

EXCEL 根據值 選取後面不定數量欄位的數值

大家好,
我有一個類似像這樣的表格
https://ithelp.ithome.com.tw/upload/images/20200523/20127366kjFeatP7fk.png

目前需要根據第一欄位的數字,也就是上圖中深色那欄的數字,去選取後面不定欄位數量的值
例如:
如果在空白格輸入編號53,那就希望後面的格子可以依序出現17.4.20.2.21.2
(下圖中紅色部分)
輸入編號59,出現20.1.21.1.22.1.23.4(下圖中藍色部分)
編號67,出現13.5.14.5.15.5.20.2.21.2.22.2.23.5(下圖中黑色部分)
https://ithelp.ithome.com.tw/upload/images/20200523/20127366RvR5fwhZGF.png

請問這是可以根據excel內部的function就解決的問題嗎?
還是一定要另外寫程式才可以辦到呢?
麻煩大家了,謝謝!

看更多先前的討論...收起先前的討論...
選到編號53,那就希望可以出現17.4.20.2.21.2
在哪裡出現?
請問這樣子發問有比較清楚一點嗎QQ 我原本的描述好像不太好
沒有
九號 iT邦新手 5 級 ‧ 2020-05-25 17:22:14 檢舉
這是甚麼解謎嗎 我看不懂WWW
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
2
海綿寶寶
iT邦大神 1 級 ‧ 2020-05-23 20:24:07

https://ithelp.ithome.com.tw/upload/images/20200524/2000178730RMWSy9Dh.png

B8 公式為=VLOOKUP($A$8, $A$1:$S$6, COLUMN())
然後將 B8 公式複製到 C8..S8 即可
1.在 A8 輸入數字
2.在 B8..S8 顯示數字

ckp6250 iT邦好手 1 級 ‧ 2020-05-24 10:54:09 檢舉

回答地乾淨俐落,
怎麼沒有得到最佳解答?

九號 iT邦新手 5 級 ‧ 2020-05-25 17:32:44 檢舉

懂了 問題這樣海綿大還可以看懂 真神..

1
秋米苗
iT邦新手 5 級 ‧ 2020-05-23 20:28:11

使用INDIRECT + ADDRESS 來解決這個問題

說明

  • 首先預設你能夠在資料表中新增一個參數,紀錄「本資料的長度」
  • ADDRESS 欄位讓你能使用數字存取指定儲存格,而非寫死
    如此一來,讓 ADDRESS 存取指定儲存格,就能知道使用特定儲存格指定計算範圍
  • INDIRECT 負責翻譯 ADDRESS 回傳的絕對地址,剩下就把騎回傳資料視為一個地址即可!

範例

|row id|row count|sum of count|data|
|-|-|-|-|-|-|-|-|
|2|2|<公式>|3|5|
|3|3|<公式>|4|6|2|
|4|4|<公式>|5|3|22|1|
|5|5|<公式>|6|7|2|777|20|
|6|5|<公式>|7|5|2|1|3|
|7|5|<公式>|89|9|2|1|6|

  • row id 提供 「這是第幾個 row?」的的資料。excel 應該有可以取代他的方式,就不需要這一欄位了!
  • row count 表示 要往後抓多少欄位。
  • sum of count 簡單用 sum 公式來展現效果。
    最上面那一格<公式>(2C)為「=SUM(INDIRECT(ADDRESS(A2,4)):INDIRECT(ADDRESS(A2,4+B2-1)))」
    剩下公式向下生成
  • data 為資料集

線上範例

https://docs.google.com/spreadsheets/d/1Z4vaE2PS9VmuGLUQe2yC2UskrjXmqCSQ_dCgvITj5jA/edit?usp=sharing

秋米苗 iT邦新手 5 級 ‧ 2020-05-23 20:49:08 檢舉

你可以嘗試在資料集空白處添加值,因為 range count 限制範圍,所以結果不會改變。套用你的需求,必須在每筆資料最前面添加 range count 欄位,標示資料範圍,就能在只用內部韓式狀況下達成

秋米苗 iT邦新手 5 級 ‧ 2020-05-23 21:01:26 檢舉

此範例中只示範了取得,沒有做出塞入。
塞入請使用 COLUMN 函式來塞

秋米苗 iT邦新手 5 級 ‧ 2020-05-23 21:07:13 檢舉

如果需要跨資料表,參考https://support.office.com/en-us/article/address-function-d0c26c0d-3991-446b-8de4-ab46431d4f89

2
ccenjor
iT邦高手 1 級 ‧ 2020-05-23 20:33:16

T2儲存格輸入要找列數:
U2儲存格輸入公式:
=IF(COLUMN()>37,"",OFFSET($A$1,$T2-1,COLUMN()-21))
再將公式複製到V2:AK2(預估資料最多18欄)
https://ithelp.ithome.com.tw/upload/images/20200523/201098819O9hu0htbQ.png

我要發表回答

立即登入回答