麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 如何在設有公式的儲存格填入數字 [打印本頁]

作者: kai6929 時間: 2010-5-6 17:20 標題: 如何在設有公式的儲存格填入數字

請教各位大師,如何在設有公式的儲存格填入數字,

如附件
[attach]139[/attach]

作者: Hsieh 時間: 2010-5-6 18:04

請教各位大師,如何在設有公式的儲存格填入數字,

如附件
kai6929 發表於 2010-5-6 17:20

誰看的懂需求的麻煩開示一下

作者: sujane0701 時間: 2010-5-6 20:53

誰看的懂需求的麻煩開示一下
Hsieh 發表於 2010-5-6 18:04

樓主的需求在B4欄的驗証訊息內,內容如下:
當產品類別是A時 B7,C7,D7,E7,F7,都是手動填入的
當產品類別是B,C,D,E時 B7,C7,D7,E7,F7,都是預先設定好的數字自動填入的
----
不過小弟(即本人,在下,我..)不會解^^

作者: Hsieh 時間: 2010-5-6 22:43

這種問題是觀念問題
試想公式是原先建好的內容
你要能允許自行輸入數值後再恢復公式
這只有一個可能
把公式輸入在某個位置
等要用時再複製回來
如果真是如此
除了用VBA來做這動作外
恐怕再也想不到其他方法了

作者: kai6929 時間: 2010-5-7 08:09

感謝各位大師,的指教我,但如果是用VBA該如何著手

作者: Hsieh 時間: 2010-5-7 08:46

回復 7# kai6929

vba常是量身訂做
你要填入的公式是如何?
你可先用錄製巨集的方法開始瞭解vba
這問題錄製好後再去程式區發問
尋求如何自動化更改儲存格內容
就會有答案的

作者: kai6929 時間: 2010-5-7 11:28

了解,感謝各大師的開示.

作者: Hsieh 時間: 2010-5-8 15:17

樓主的意思不知是否如此
假設把規格表放在M1:R8
B7=VLOOKUP($A$7,$M$1:$R$10,COLUMN(),0)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$B$4" Then Exit Sub
If Target = "A" Then [B7:F7] = "" Else [B7:F7].FormulaR1C1 = "=VLOOKUP(R7C1,R1C13:R10C18,COLUMN(),0)"
End Sub

複製代碼

[attach]136[/attach]

作者: PD961A 時間: 2010-5-8 20:46

回復 1# kai6929

請問
我沒辦法打開這個程式?
它不是RAR檔嗎?
下載下來變成"Kai Q&amp"
請問是什麼文件?要用哪種程式開啟?
謝謝!!

作者: Hsieh 時間: 2010-5-8 20:51

檔名不能有特殊符號&
改成kai.rar即可

作者: PD961A 時間: 2010-5-8 22:12

[attach]138[/attach]
回復 13# Hsieh
版主哪裡改??
改完還是一樣阿?
不管是在RAR解壓縮前或後
都不行阿?可以教一下嗎?
謝謝!

作者: Hsieh 時間: 2010-5-8 22:30

下載時的對話要用儲存[attach]140[/attach]
檔案名稱直接把RAR附檔名一併寫入[attach]141[/attach]

作者: 小誌 時間: 2010-5-8 22:33

回復 Hsieh
版主哪裡改??
改完還是一樣阿?
不管是在RAR解壓縮前或後
都不行阿?可以教一下嗎?
...
PD961A 發表於 2010-5-8 22:12

看了痛苦
我已將樓主的附檔重新放上,你重新回第一帖#1下載吧,不用回帖感謝
試驗後報告研究結果就好

作者: PD961A 時間: 2010-5-9 14:25

回復 17# 老夏

夏板主:
我知道(11樓)HS版主的可以用
它已經寫好了
所以才想要看原檔
因此才會有小誌版主的那段話
"看了痛苦"
小誌版主的意思是
請我不用照HS版主解釋步驟去解
他已經修正好原檔放回1樓...所以看完寫心得報告!!!不用寫感謝函(還是謝謝小誌版主)

另外還是謝謝HS版主回覆
因為本來就想知道怎麼解..常遇到這個問題!!謝謝!

作者: PD961A 時間: 2010-5-9 15:35

本帖最後由 Hsieh 於 2010-5-9 22:07 編輯

只能二選一的結構性問題

公式寫在儲存格裡,329種函數再怎麼樣寫,產生的都是[值]
[值]儲存在那裡

儲存 ...
老夏發表於 2010-5-7 11:20

只能二選一的結構性問題

夏版主說的是EXCEL基本觀念公式寫在儲存格裡,329種函數再怎麼樣寫,產生的都是[值]
[值]儲存在那裡儲存格直接鍵入[值],原有公式則被刪除硬要違反存取規則神仙難解

1樓主用驗證清單主要要使用關聯表做VLOKUP()查詢,關聯表在那裡?主索引在那裡?完全沒有
這點HS版主已經在他寫好的程式裡幫這位仁兄..在右邊寫好關聯性..所以希望請這位仁兄您下次提問題時可否先像夏版主說的
關聯表在那裡?主索引在那裡?完全沒有先稍微想好..還好論壇有HS版主能了解你的意思.花時間幫您想好(他會說白頭髮又多1根..所以希望您讓這些版主能很快進入狀況..這樣解題會很快喔!)
否則真的大家都不太懂您的意思!

另外將HS版主寫好的程式看完後..不好意思我把VB的部分刪掉..它還是能選擇關聯性
所以不懂他只要將需要的手動部份用VLOKUP()查詢去運用就可以..
這裡1樓仁兄的需求並沒有關聯到產品類別ABCDE阿?需要手動的用VLOKUP()查詢就夠用了....
謝謝!
[attach]151[/attach]

作者: PD961A 時間: 2010-5-9 15:45

本帖最後由 PD961A 於 2010-5-26 12:44 編輯

小誌版主
不好意思
我常常上傳檔案都會重複傳到
刪又刪不掉..
請問在哪裡刪?
發帖後
才發現跑出3個檔案
但是KAI-2才是正確
另外2個要如何刪掉?我連它怎麼跑出來的
都有點莫名奇妙?因為在上傳時--畫面是當掉的
所以又重寫一次回復(心得報告)
結果剛剛上傳的程式...竟然一起秀出..
謝謝!

作者: PD961A 時間: 2010-5-9 23:20

回復 20# PD961A

小誌版主
您刪錯檔案了
kai-2才是正確的檔案
kai-1那是沒有刪掉hs版主vba的檔案
在上傳時當掉的檔案
不好意思!
我重傳好了
謝謝![attach]165[/attach]

作者: kai6929 時間: 2010-5-10 09:45

樓主的意思不知是否如此
假設把規格表放在M1:R8
B7=VLOOKUP($A$7,$M$1R$10,COLUMN(),0)
Hsieh 發表於 2010-5-8 15:17

感謝各位大師的參與
以及 Hsieh 大師,你真了內行,這就是我要的意思,但我想把vlookup的函數改為INDEX函數可以嗎

作者: Hsieh 時間: 2010-5-10 09:51

回復 22# kai6929

我只是揣摩查詢方法
你要改公式就照我8#的方法錄製公式後
再把我程式碼寫的公式取代掉就好

作者: kai6929 時間: 2010-5-10 11:11

If Target = "A" Then [B7:F7] = "" Else [B7:F7].FormulaR1C1 =
"=VLOOKUP(R7C1,R1C13:R10C18,COLUMN(),0)"

請教一下大師 (R7C1,R1C13:R10C18 這裡是什麼意思

作者: Hsieh 時間: 2010-5-10 11:14

儲存格表示以R1C1格式表示

作者: asmileboy 時間: 2010-5-10 18:50

個人理解不知是否正確？樓主是否想達到以下功能？

以[產品類別]為條件（分兩層分支），

當產品類別是A時 →B7,C7,D7,E7,F7,都是手動填入的（輸入值便是所需之值）
當產品類別是B,C,D,E時→ B7,C7,D7,E7,F7,都是預先設定好的數字自動填入的（意即預設對照表之相關對應值）

若是如此，個人解題如下，不知是否可以？

以if做判別，（+用vlookup 查詢對照表方式解決→當[產品類別]≠A之狀況）
當[產品類別]=A →則B7,C7,D7,E7,F7各儲存格手動輸入值便是所需。
當[產品類別]≠A（因已事前設定值為：A,B,C,D,E，意即當產品類別是B,C,D,E時）→則用vlookup 查詢對照表方式，自動對應相關對應值。

但個人目前卡在：「當[產品類別]=A →則B7,C7,D7,E7,F7各儲存格手動輸入值便是所需。」
「手動輸入值便是所需」→要如何（用公式）表之？

尚請高手指點？

作者: Hsieh 時間: 2010-5-10 18:53

回復 26# asmileboy

把值填到資料表內

作者: asmileboy 時間: 2010-5-10 19:06

回復 27# Hsieh

若此值「沒有限定/特定範圍」（意即：各種數值都可以），那如何輸入到「資料表」？
（請問：此「資料表」意思是：vookup所查詢之「查詢對照表」之意嗎？）

作者: Hsieh 時間: 2010-5-10 19:22

回復 28# asmileboy

假如只是讓B7,C7,D7,E7,F7得到A的對照值
那就再VLOOKUP的對照表填入A的對照值
只是下次再選A就會再次顯示上回填入的值
所以公式只能傳回值不能讓你任意輸入值
我的作法只不過是把公式記在程式碼中
然後靠事件程序自動填入公式或清除
僅此而已

作者: HUNGCHILIN 時間: 2010-5-10 19:56

本帖最後由 HUNGCHILIN 於 2010-5-10 20:55 編輯

kai6929 提問的這個問題
在成本試算表中屬於重要問題

這個問題業界稱為"自訂配方/自訂規格/自定..."
發生在制式配方規格之外的訂單或研發研發新產品計算成本時
系統資料庫無資料會無資料產生錯誤
故我們要用手工填入資料 (系統無資料可自動代入)

解法與固定做法(這個請不用要VBA來解會較好):
你至少必須有兩個欄位來存放這些資料
第一個欄位用來存放固定且制式的電腦自帶的資料(且為主要的顯示資料區)
第二個欄位用來手工自訂如配方(當你輸入使用此區時第一欄的資料就會以此為主並直接使用此資料,當第一欄呈現ERROR時你可以使用第二欄直接自訂避免計算錯誤也不用改函數與程式)
以下為我六七年前製作的其中一個範例機密處已刪除
你可以看PVC粉已經有價格欄位那為何還要加一欄自訂欄位
就是要解決此問題

[attach]237[/attach]

觀念上開通
自然也就沒有公式與值的問題而且成本表使用與靈活度上也方便

這是成本表作法的經驗希望對你有幫助

成本試算表核算與製作
是我的專長之一
有機會可以拿來此多討論

作者: PD961A 時間: 2010-5-10 20:03

回復 28# asmileboy

asmileboy素還真兄:
HS版主的11樓的檔案是版主他已經寫好了
所以你下載後去看他寫的程式碼
再把文章從頭看一遍
你就會懂HS版主要闡述的意思了....
謝謝!

作者: asmileboy 時間: 2010-5-10 21:16

本帖最後由 asmileboy 於 2010-5-10 21:20 編輯

復 31# PD961A

Hsieh版主解得相當好！
我的意思也是顯現如此效果。
只是，我不會寫程式且也看不懂！不過，這段 If Target = "A" Then [B7:F7] = "" 寫的真好！
哈！就是我想要表達之效果！（直指我心！但我不會用公式表達）

請問，此題若不用程式，
只用Excel預設之函數、功能....等等，可以解的出來嗎？
（因為一般人不見得會程式、不見得看得懂程式...）
而且我看書上作者講；若能用Excel預設之函數、功能.....則用此！
非萬不得已才用VBA！（似乎是效能問題...）

PS：
To PD961A 君
我是一頁書，而非素還真！

作者: kai6929 時間: 2010-5-11 10:30

感謝各位大師,
這是我做好的成本分析跟大家分享一下
[attach]252[/attach]

作者: luhpro 時間: 2010-5-12 23:21

本帖最後由 luhpro 於 2010-5-12 23:29 編輯

回復 32# asmileboy

若只想用 Excel 的儲存格公式達到你要的效果,比較可行的方式是如阿吉所說的設置手動資料輸入區,
然後當此區有資料時公式就抓這的資料,而若此區無資料時就去抓預設資料.

因為Excel的儲存格有一個特性,
就是 "儲存格的實際內容"(不論是文數字亦或是公式) 都是非 "經使用者操作" 就不能變更的.(以不使用外部程式或 VBA 為前提)
而若使用者在儲存格上輸入資料就必然會 "蓋掉" 該儲存格上原先的東西,
所以 VBA 還是有 Excel 一般操作方式做不到的功能.

另外我有用過另一種的方式來達到類似的效果,
那就是用 HYPERLINK 函數,
點該儲存格時會跳到手動輸入資料區內對應的儲存格, (此時可手動輸入資料)
而其右方儲存格則放跳回原位置的 HYPERLINK, (亦即按 Enter 兩次會回到原儲存格, 當然按下 Enter 鍵後, 移動選取範圍的方向要先設成 "右")
若顯示的值則設為當手動輸入資料區內對應的儲存格內有資料則秀此資料, 否則秀預設資料.

此方式不友善的地方的是作業中操作區會跳來跳去,
使用者可能容易眼花花...

另外建議貴方善用 =IF(ISERROR(原公式),"",原公式) 的方式來避免文件上出現 #DIV/0! 或其他影響文件美觀的文字出現.

作者: asmileboy 時間: 2010-5-13 13:19

回復 34# luhpro

謝謝luhpro說明：

其中
「若只想用 Excel 的儲存格公式達到你要的效果,比較可行的方式是如阿吉所說的設置手動資料輸入區,
然後當此區有資料時公式就抓這的資料,而若此區無資料時就去抓預設資料.」

這正是我所想要之需求！平常碰到之頻率很高！
但要如何設定？（該用何函數？...）
謝謝！^_^

例如：
我有一個「學生」參加象棋比賽對照表（歷史紀錄；「學生參加名單；即參加學生之個人基本資料」）
一個月後將舉辦「全省」象棋比賽，
參加之學生有可能是「以前參加過」（歷史名單中有）
也有可能是「不曾參加過者」（無歷史紀錄）

我希望：若曾參加過，則其相關資料便自動填入（以減少輸入且增加資料正確性、提高效率。）
若未曾參加過，則手動填入資料。
請問要如何設計、處理？（該用何函數？...）

作者: HUNGCHILIN 時間: 2010-5-13 19:45

本帖最後由 HUNGCHILIN 於 2010-5-13 19:51 編輯

回復 35# asmileboy

大概是這樣驗證等沒多做修飾架構大概這樣
裡面沒計算題比較可昔
輸入區:請按TAB 鍵就知道
保護沒設密碼
[attach]379[/attach]

作者: sujane0701 時間: 2010-5-13 20:47

回復 36# HUNGCHILIN

請教阿吉大師
1.您的附件檔 "首頁"表單無論滑鼠或是鍵盤,最右下只能移到 N8 ,請教大師相關設定是放在哪????小弟找了好久都找不到,請開示~~

2.小弟對定義不太熟悉,想請教定義名稱Print_Area的設定 =首頁!$A$1

G$8 ,請問是否只要定義完成,列印版面就會受到控制嗎??或是寫好定義後,還有哪裏需要設定
?
小弟問題可能十分淺薄可笑,還請多多包涵指導,感謝感謝~~~

作者: HUNGCHILIN 時間: 2010-5-13 21:11

這個問題跟此版比較沒關係
此技巧是簡易的欄列隱藏而已

動作就是
把N欄以後的欄位全選後隱藏欄
把8列以後的欄位全選後隱藏列
其餘較深的欄列隱藏技巧請看
http://forum.twbts.com/viewthrea ... =1&extra=#pid61

作者: luhpro 時間: 2010-5-13 22:42

回覆 35# asmileboy

我剛剛想到另外一種方式應該也是可以達到你想要的結果, 流程如下:

1. 使用者輸入 IndexKey - 身分證字號
2. 按下 "尋找資料" 按鈕
3. 若有找到資料就把相關資料帶入各個欄位內
4. 若沒有找到資料就清空各欄位內容等待使用者輸入相關資料
5. 使用者輸入資料後按下 "新增資料" 後自動新增該筆資料到基本資料索引區內
6. 等待下一筆資料的查詢或輸入

這個方式沒有用到 Excel 儲存格公式, 完全以 VBA 程式來達到想要的效果.
當然也可以再做進去 "刪除資料" 亦或是其他想要的功能.

[attach]386[/attach]

作者: baa168 時間: 2010-11-6 00:35

我也了解了
感謝分享

作者: mistery 時間: 2010-11-10 19:16

回復 11# Hsieh

又要再謝謝一次超級版主了~
最近在弄一個試算表也有遇到類似的問題
版大的範例剛好可以讓我應用上....

作者: mistery 時間: 2010-12-31 10:09

本帖最後由 mistery 於 2010-12-31 13:58 編輯

回復 8# Hsieh

請教版主  在這個案例中
如何可以在點選其他儲存格時  也可以有相同的結果?
例  點選儲存格 A1 會依照A1的值判定後 1.清空儲存格A2  或  2.帶入原儲存格A2的公式
      點選儲存格 B1 會依照B1的值 1.清空儲存格B2 或  2.帶入原儲存格B2的公式
      以此類推....

PS.我有改寫您原來的程式碼
      If Target.Count > 1 Then Exit Sub
      If targe.address <> "A1" or "B1"  Then Exit sub    <-----似乎不能直接這樣加 OR
      If Target = "某值" Then [A2:A2] = "" Else [A2:A2].FormulaR1C1 = "某公式"
      If Target = "某值" Then [B2:B2] = "" Else [B2:B2].FormulaR1C1 = "某公式"

作者: mistery 時間: 2010-12-31 13:57

本帖最後由 Hsieh 於 2010-12-31 16:48 編輯

回復 8# Hsieh

Dear 版主

我已補上附件說明再請您解惑!感激~
[attach]4278[/attach]

作者: Hsieh 時間: 2010-12-31 16:49

回復 39# mistery

做法已將您樓上的檔案更新
你看看是否有會錯意

作者: mistery 時間: 2011-1-3 14:36

回復 40# Hsieh

謝謝版主您真是善解人意啊!!!
這正是我要的感謝!!

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)