帖子: 36
主題: 16
精華: 0
積分: 134
點名: 0
作業系統: Xp
軟體版本: office 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-1-7
最後登錄: 2019-1-6

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2011-3-2 14:08 | 只看該作者

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

本帖最後由 kim595 於 2011-3-3 15:32 編輯

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

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)

樓主熱帖

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

2^#

發表於 2011-3-2 14:58 | 只看該作者

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

TOP

kim595

中學生 Rank: 2

帖子: 36
主題: 16
精華: 0
積分: 134
點名: 0
作業系統: Xp
軟體版本: office 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-1-7
最後登錄: 2019-1-6

3^#

發表於 2011-3-2 15:48 | 只看該作者

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

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

感謝賜教

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 253
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-21

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

4^#

發表於 2011-3-2 16:12 | 只看該作者

本帖最後由 Hsieh 於 2011-3-2 16:21 編輯

回復 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次複製動作
請試著說明你的需求，才能得到正確解答

學海無涯_不恥下問

TOP

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

5^#

發表於 2011-3-2 16:13 | 只看該作者

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

TOP

ANGELA

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 812
主題: 7
精華: 0
積分: 862
點名: 0
作業系統: XP
軟體版本: 2007
閱讀權限: 100
性別: 男
來自: 台中
註冊時間: 2010-5-13
最後登錄: 2019-10-10

論壇貢獻勳章

6^#

發表於 2011-3-2 23:06 | 只看該作者

本帖最後由 ANGELA 於 2011-3-2 23:20 編輯

回復 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,))

TOP

kim595

中學生 Rank: 2

帖子: 36
主題: 16
精華: 0
積分: 134
點名: 0
作業系統: Xp
軟體版本: office 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-1-7
最後登錄: 2019-1-6

7^#

發表於 2011-3-3 15:32 | 只看該作者

本帖最後由 kim595 於 2011-3-3 15:47 編輯

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

目前小妹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)

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 253
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-21

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

8^#

發表於 2011-3-3 18:12 | 只看該作者

回復 7# kim595

對於F38跟G38還是不懂你的說法
試試附件

空白暫放款.rar (20.87 KB)

學海無涯_不恥下問

TOP

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

9^#

發表於 2011-3-3 20:04 | 只看該作者

本帖最後由 GBKEE 於 2011-3-3 20:09 編輯

回復 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

TOP

chhars

小學生 Rank: 1

帖子: 4
主題: 0
精華: 0
積分: 3
點名: 0
作業系統: winxp
軟體版本: sp3
閱讀權限: 10
註冊時間: 2011-1-2
最後登錄: 2014-12-11

10^#

發表於 2014-12-11 12:06 | 只看該作者

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

TOP