[發問] 如何使用巨集循環增加欄位?

kim595

各位前輩午安,
目前在進行一個試算表遇到幾個瓶頸,試了幾次都沒辦法突破
請各位多多指教 :)

1. Sheet1的 F38 & G38 欄位,我想要在D38 KEY 入發票號碼就自動抓取金額跟暫放款單號
   但我用VLOOKUP公式好像抓不到,怎麼會這樣?
    F38公式: IF(D18=0,"",VLOOKUP(D18,sheet2!$B:$D,2,FALSE),1)
    G38公式: IF(D18=0,"",VLOOKUP(D18,sheet2!$B:$D,1,FALSE),1)
    我在想抓不到的原因應該是 儲存格裡面是"文字"不是"數字",有這個可能嗎?
    ***另外我想請教一下,如果像我的表格有部分是合併儲存格,那公式還抓的到資料嗎?

2.Sheet2的表格會隨著新資料增加欄位不夠使用,
   所以我做了一個自動增加五個欄位的按鈕 / 巨集 ,每按一次就多加五欄 (檔案中黃色部分)
   我的方法是: 開始錄製>直接複製黃色部分,再貼在B187那一欄,錄製完畢>再指定該按鈕開始巨集
  但它只會新增B187那一次的欄位,並不會累計遞增貼上,
   按鈕宣告失敗 :(


是哪裡弄錯了呢?
還有,增加欄位的時候有什麼方法讓框線不會跑掉嗎?(虛線/實線 /雙實線/粗實線)
(我每次填滿公式的時候,最下面的雙時線都會變成實線或虛線,然後就要再重新調整一次)



空白暫放款.rar (20.31 KB)

下載次數: 12

2011-3-2 14:08

GBKEE

回復 1# kim595
資料庫要正規化 請自行搜尋來了解
有關VBA的問題 請到[Excel程式區] 發問會恰當些

kim595

GBKEE前輩:
sorry,我不知道excel 巨集要歸類在VBA區

對於"資料庫要正規化"我不是很懂,可以給我一點方向嗎?
目前部分儲存需合併,對我來說是必要的;還是有更好的方法呢?

感謝賜教

Hsieh

回復 1# kim595


    1.   F38公式: IF(D18=0,"",VLOOKUP(D18,sheet2!$B:$D,2,FALSE),1)
          G38公式: IF(D18=0,"",VLOOKUP(D18,sheet2!$B:$D,1,FALSE),1)
這樣抓不到資料是正常的，因為VLOOKUP要搜尋的值，必須在資料表的第一欄位
sheet2的B欄是暫放款單號，所以你搜尋不到發票號碼
當然，你要的答案也不應該是對應到的單一儲存格的值
從你的範例中看不懂F38要的是什麼，若是加總sheet2!F3:F10答案也不該是29735
發問問題請將你的需求描述清楚，以一個錯誤的公式別人是猜不透你要的是什麼?
不知道是不是這個意思?
F38=IF(D38="","",ROUND(SUM(OFFSET(Sheet2!$D$1,MATCH(D38,Sheet2!$D:$D,0)-1,2,8,))*1.05,0))
G38=TEXT(OFFSET(Sheet2!$D$1,MATCH(D38,Sheet2!$D:$D,0)-1,-2),"OF000000")

2.錄製巨集得到的程式碼，是以錄製時的動作紀錄所有動作
要達到隨工作變動而隨著變化內容，必須依照表中當時的資料狀態，尋找規則，再依照規則修改程式配合所需
我看你的意思應該是按下按鈕就要新增5個表格，我想到的試算出B3以下第一個非合併儲存格位置後，再以迴圈執行5次複製動作
請試著說明你的需求，才能得到正確解答

GBKEE

回復 3# kim595
10]http://forum.twbts.com/redirect.php?goto=findpost&pid=7360&ptid=1463]10#

ANGELA

回復 3# kim595

如果一定要用合併格可參考阿吉兄及謝版主發表過的作法就不會受合併格的影响了.未合併前先把要合併的格子都給同一值,再用下列方法合併.
      假設要做A1:C3成為合併儲存格但含有各自資料
先選取其範圍外如D1:F3合併儲存格
然後在工具列上找到一個黃色刷
按下該刷
掃過A1:A3
就能得到效果

F38=INDEX(Sheet2!C:C,MATCH($D38,Sheet2!D:D,))
G38=INDEX(Sheet2!B:B,MATCH($D38,Sheet2!D:D,))

kim595

非常感謝各位版主的耐心回復

目前小妹EXCEL程度還在幼幼生階段，可能不夠了解一些基本規則也形容的不是很清楚
尚請各位前輩多包含~  。若有作法待加強處，煩請不吝提點了！多謝,感恩。

TO: GBKEE 版大
我有重新上傳了一個更改後的檔案,
主要是把第二頁的儲存格全部取消合併,
不知道是否符合GBKEE 版主所說的"資料庫正規化"呢?
感謝指教

TO:Hsieh 版大
我第二頁的搜尋值已經放到第一欄,
其實我想的很簡單,第一個頁面是要給客戶看的(資料都由第二頁做KEY IN 與修改)
所以我希望第一頁不用花很多時間在KEY IN資料,
D38我要的是~只要把發票號碼KEY上去該發票的金額跟訂單單號就會秀在F38與G38
(所以F38這個數值不是算出來的,是希望他抓到C3這一格)

關於增加5個欄位按鈕,您提到使用迴圈執行五次,所以這樣的話需要寫VBA才能做到嘍?
**新上傳的更新檔內還有說明,希望能夠明確傳達我想表達的意思

TO:ANGELA 版大
感謝版主熱心回答,
雖然有點看不懂,但我會再研究看看!
是指合併儲存格之前,儲存格要先統一格式嗎?
(例如全部改成通用格式?)

空白暫放款-1.rar (20.34 KB)

下載次數: 4

2011-3-3 15:31

Hsieh

回復 7# kim595

對於F38跟G38還是不懂你的說法
試試附件
空白暫放款.rar (20.87 KB)

下載次數: 13

2011-3-3 18:12

GBKEE

回復 7# kim595
資料庫正規化   資料列不要有空白
發票號碼        暫放款單號        暫放款金額        銷貨單號        金額        剩餘金額
JA26731912        OF22597        29,735         SF22597-2        19,110         10,625
JA26731912        OF22597        29,735         SF22597-3        7,500         3,125
JA26731912        OF22597        29,735         SF22597-4        1,660         1,465
JA26731912        OF22597        29,735         SF22691-3        49         1,416

chhars

其實不一定要先正規化
換個函數也能在不正規的資料表中自動挑出需要的結果
但是正規化的好處是以後維護資料相對容易
如果資料越來越龐大也有利於函數循環速度