iT邦幫忙

0

EXCEL―跨工作簿/跨工作表參照欄位,並使整列資料可同步排序、調動 (未解決)

範例檔案︰

含兩張工作表,
▾〈物料資料〉為主表,物料的建立、刪除、調動皆在此表;
https://ithelp.ithome.com.tw/upload/images/20190712/201064551sCB1RwctD.png

▾ 另一張工作表為〈物料價格〉,料編及名稱都必須與物料資料一致,
料編以公式 =IF(範例.xlsx!物料編號="","",範例.xlsx!物料編號) 參照主表,
名稱以公式 INDEX搭配MATCH 的方式,由主表取得
https://ithelp.ithome.com.tw/upload/images/20190712/20106455SJXu4YmPZ7.png
範例檔案連結︰[]https://drive.google.com/open?id=1viYUjjNbTIBptbq6rXLrWbHmOuav4KKv

狀況︰
雖然兩表料編及名稱部分,可同步變更、刪除、排序、插入列,
但後方資料是完全不會跟著變動的,
如範例,主表新增一水藍底色,物料編號為A02010的列,再重新以A-Z排序,
價格C-F欄的資料不會隨著其編號調動,

▾ 若主表經過多次的增減排序,勢必會導致價格表資料大量錯位
https://ithelp.ithome.com.tw/upload/images/20190712/20106455vlXLfsEYuH.png

https://ithelp.ithome.com.tw/upload/images/20190712/20106455sG2iQNWRiy.png

為了使物料維持一致性且不重複
許多後續其他的工作表、活頁簿資料,都是根據主表衍伸而來,
基本卻很重要的功能需求,相關的應用討論卻不多
請問須透過何種方式才可解決此問題?
先感謝回覆

2 個回答

0
小魚
iT邦大師 1 級 ‧ 2019-07-12 17:02:59

你排序的範圍選錯了吧,
你是直接操作的嗎?

看更多先前的回應...收起先前的回應...
l410203 iT邦新手 5 級 ‧ 2019-07-12 18:07:07 檢舉

沒有框選範圍,
點擊主表A欄內任一格後,再排序的方式

ccenjor iT邦新手 3 級 ‧ 2019-07-12 19:15:49 檢舉

用VLOOKUP做會不會比較好,主表怎麼編修,其他工作表都可以正常抓取到所要資料。
我的主表是活頁簿1
我的資料表是活頁簿2
A欄是編號,B欄公式我是這樣寫的
=IFERROR(VLOOKUP($A3,[活頁簿1.xlsx]工作表1!$A:$F,COLUMN(),0),"")

l410203 iT邦新手 5 級 ‧ 2019-07-12 23:02:46 檢舉

用物料編號當收尋關鍵字的方式是可以達到整列同步,
比如價格表中的物料編號名稱,就是依據編號再去收尋主表,

價格表 B欄-名稱 公式︰
=IF(A3<>"",INDEX('物料資料-主表'!$A$1:$F$21,MATCH(A3,'物料資料-主表'!A:A,0),MATCH('物料資料-主表'!$B$1,'物料資料-主表'!$A$1:$F$1,0)),"")
所以不管編號怎麼調動,名稱都不會錯亂;

但變成價格資料,就都要建立在主表內,
會讓主表的欄位數量變得很龐大,所有資料建在一張工作表裡不便管理
須一直往右拖曳橫桿尋找

小魚 iT邦大師 1 級 ‧ 2019-07-12 23:19:05 檢舉

如果你是要操作Excel而不是要用VBA,
其實排序很簡單,
就是要先框選出範圍,

1.移到A1
2.End + 右(方向鍵) 移到最右(標題要連續)
3.End + 下(方向鍵) 移到最下(基本上應該是看最左邊欄是否連續)
4.再選擇排序欄即可

l410203 iT邦新手 5 級 ‧ 2019-07-13 01:24:26 檢舉

在價格表中,
不管點擊A列任一格排序,或框選範圍後以A欄排序
都會出現價格對不上物料的情況
https://ithelp.ithome.com.tw/upload/images/20190713/20106455459hH5Um3l.png

似乎EXCEL顧不到那些參照欄位後的單純文字資料?
預期結果是 主表的物料怎麼增減變動,其他參照的表就整列綁著一起變動

不知是否能以函數和公式解決,或是需要靠VBA才可達成?

0
giulian
iT邦新手 4 級 ‧ 2019-07-13 15:40:15

你的物料價格那張表中的A,B欄是用公式所以物料資料表有異動時物料價格的A,B欄會跟者動,但物料價格C~F你不是用公式的方式所以不會跟著動。
設計上,價格資料跟規格資料是什麼原因需要分開?其實你可以用一張表就好,就不需要考慮這個問題
如果一定要在excel做到你說的功能,只有vba可以幫你達成
單純排序的話有其他方法可以做到兩張表同步,但如果要A表新增刪除插入要同步到B表哪就只能靠vba

我要發表回答

立即登入回答