Board logo

標題: [分享] 公式輸入的常用技巧(新手必讀) [打印本頁]
作者: Hsieh    時間: 2010-9-4 23:12     標題: 公式輸入的常用技巧(新手必讀)

本帖最後由 oobird 於 2010-10-31 09:47 編輯

有些常在解題中使用的技巧，但一再被重複問到此類問題，將容易造成資源浪費
故發此帖，希望論壇新手能先預習，俾利日後爬文能易於吸收了解。
此帖希望大家有好技巧都能發表跟帖，但請勿發表其他無意義或感謝文章
有再想到的項目將陸續增加

1.陣列公式輸入方法是Ctrl+Shift+Enter三鍵一起完成輸入
2.公式中--代表意義:--就解讀成負負得正就是將數字型的字串轉換成數值
如:M10234要取得10234成為數值使用，先以MID或RIGHT等字串函數來取得
   =RIGHT("M10234",5)這樣得到10234是字串型態
   =--RIGHT("M10234",5)這樣得到10234是數值型態
3.輸入公式時善用f4鍵切換位址的相對或絕對參照。
作者: lumark1976    時間: 2010-12-21 00:04

本帖最後由 Hsieh 於 2010-12-21 10:52 編輯

版主大大，請問下面寫法內的 -- 是與您所說的數值型態一樣嗎？還有這樣的寫法有更好的嗎？謝謝回覆囉!!

=IF(I14="","",IF(SUMPRODUCT(--(C14='C:\生產日報\[標準工時(新版).xls]標準工時'!$C$2:$C$3000),--(生產日報!I14='C:\生產日報\[標準工時(新版).xls]標準工時'!$E$2:$E$3000))=580,0,INDEX('C:\生產日報\[標準工時(新版).xls]標準工時'!$L$2:$L$3000,SUMPRODUCT(--(C14='C:\生產日報\[標準工時(新版).xls]標準工時'!$C$2:$C$3000),--(生產日報!I14='C:\生產日報\[標準工時(新版).xls]標準工時'!$E$2:$E$3000),ROW('C:\生產日報\[標準工時(新版).xls]標準工時'!$L$2:$L$3000))-1,1)))
作者: Hsieh    時間: 2010-12-21 11:00

回復 2# lumark1976


    --是轉為數值沒錯
只是不知道妳的資料型態
不能給你正確回答
但可肯定的是
你這些條件式應該是要同時成立的情況
加總某欄位
應該可以用
sumproduct((條件1)*(條件2)*(條件3),加總欄位)
作者: lumark1976    時間: 2010-12-21 21:28

本帖最後由 Hsieh 於 2010-12-21 21:44 編輯

版大，我的資料為數值形態，且我改完後寫法如下：(但這樣卻帶不出數值出來，是因為要3種條件才能嗎？)

=SUMPRODUCT((B14=[標準工時新版.xls]標準工時!$B$2:$B$3000)*(F14=[標準工時新版.xls]標準工時!$D$2:$D$3000),[標準工時新版.xls]標準工時!$I$2:$I$3000)
作者: Hsieh    時間: 2010-12-21 21:46

回復 4# lumark1976
加個刮弧看看

    =SUMPRODUCT(((B14=[標準工時新版.xls]標準工時!$B$2:$B$3000)*(F14=[標準工時新版.xls]標準工時!$D$2:$D$3000)),[標準工時新版.xls]標準工時!$I$2:$I$3000)
作者: lumark1976    時間: 2010-12-21 23:33

[attach]4176[/attach]

版主大大，還是出現一樣的現象也( 該欄位出現DIV/0! )，不知您有解嗎？
作者: Hsieh    時間: 2010-12-21 23:47

標準工時新版是否開啟?
作者: lumark1976    時間: 2010-12-21 23:50

本帖最後由 lumark1976 於 2010-12-21 23:54 編輯

恩恩，開啟狀態!!可是原來寫的方式仍然沒問題
[attach]4177[/attach]
作者: Hsieh    時間: 2010-12-21 23:53

這樣看不出你的問題出在哪裡?
可否將檔案上傳以了解資料
作者: lumark1976    時間: 2010-12-22 00:01

版主大大，已將附件上傳，麻煩您了，謝謝!!
[attach]4179[/attach]
作者: Hsieh    時間: 2010-12-22 00:05

列數不同
=SUMPRODUCT(((B3=標準工時!$B$2:$B$3000)*(F3='C:\[標準工時新版.xls]標準工時'!$D$2:$D$3000)),'C:\[標準工時新版.xls]標準工時'!$I$2:$I$3000)
作者: lumark1976    時間: 2010-12-22 00:14

版主大大，感謝您的幫忙，此問題解決了，非常感恩!!
作者: my6671    時間: 2011-2-17 14:51

=--RIGHT("M10234",5)這樣得到10234是數值型態
--是轉數值型態第一次看到了  看來是學到寶了
請問是否還有其他型態可轉呢??
作者: yoyola    時間: 2011-9-30 12:45

真是博大精深呀~完全看沒
作者: 無記小小超    時間: 2012-1-25 02:36

用了這麼久還是頭一次知道可以這樣做
每一次我都是copy then paste as value 再按個感歎號 convert to number
作者: jeng    時間: 2012-1-28 21:45

我也是第一次看到可以用--方式
謝謝
作者: wxwx    時間: 2012-1-31 14:31

回復 16# jeng


    我用了很久excel 現在才慢慢瞭解這些東西
作者: meet0718    時間: 2012-3-2 17:06

excel 真是博大精深啊!用了很己都不知道有這功用!
作者: echern0705    時間: 2012-3-9 10:59

用--就可以取出數值，真是方便！受教了！
作者: miketwbts    時間: 2012-3-14 18:07

很謝謝版主分享知識, 剛加入此社團, 想在此學習關於EXCEL的應用, 透過版主分享真的減少很多自我摸索的時間, 雖然還沒有辦法下載附件一併操作演練, 就先多爬爬文章累積知識, 帶升級後再下載演練
總之, 很高興能夠找到這麼棒的分享空間!
作者: fpoqua    時間: 2012-3-15 14:26

原來--可以轉換為數值型式
之前還在想要用啥函數咧
作者: seanlai    時間: 2012-3-25 08:17

Excel真是博大經深,受教了
作者: missbox    時間: 2012-5-14 11:30

回復 1# Hsieh

原來--的用法可以值接轉成數值，這真的太好用了
又學到一樣!!!:D
作者: htnvt241    時間: 2012-6-3 00:04

負負得正那一招, 值得了, 謝謝版大 !
作者: wsx904605    時間: 2012-6-6 20:52

謝謝大大的分享

我到現在才知道加個--可以轉換成數值格式
作者: gavinhsu    時間: 2012-6-14 23:23

學到新技巧了，感謝分享
作者: taiabc1234    時間: 2012-8-20 23:59

公式中--代表意義:--就解讀成負負得正就是將數字型的字串轉換成數值
這方法以前都没注意到
謝謝
作者: jomeow    時間: 2012-9-10 17:17

回復 15# 無記小小超

我也是喔.. 每次都笨笨的按CONVERT TO NO.  ><  
THXTHX大大..
作者: jomeow    時間: 2012-9-10 17:21

回復 20# miketwbts

對呀對呀... 亂打亂撞來到這個討論區.. 真的很高興.. 因為在這兒有很多很厲害的人... 謝謝各位的教導..
更要多謝分區版主的提點..
真的獲益良多...
感恩感恩..
m(_ _)m
作者: dakiu    時間: 2012-9-12 11:21

以前是用數組,原來sumproduct就可以ㄌ...又學到ㄌ...謝謝!
作者: mark15jill    時間: 2012-12-7 08:38

回復 1# Hsieh


    1.Excel 在 A 和 1 的夾角(左上角那邊) 只要點一下 就可以將整個活頁簿的儲存格選取 或按  Ctrl + A 也可 (但有時候會指選取到有資料的部份 需再按一次..)
    2.選取後，在欄與欄 之間 點兩下(就是手動調整寬度的那個) 就會自動調整。但此方法不支援已經設定 自動分行 的儲存格。
作者: hydraulic    時間: 2013-4-8 11:48

所以使用Right, mid, left 函數所擷取的一串都屬於文字的範圍 , 除非加上 --所擷取才會變成數字 那如果擷取錯誤的話會出現甚麼情形呢
作者: jason20000    時間: 2013-7-22 23:48

不是很嘹解,看來要多學習
作者: chris406    時間: 2013-8-18 17:36

回復 1# Hsieh

受教了,第一次看到可以--就可以要到數值了.
作者: lp787878    時間: 2013-11-10 14:22

EXCEL可以很簡單也可以很複雜，又上了一課，感謝!
作者: alextask    時間: 2014-3-24 07:18

又多學習了一些技巧. 感謝
作者: bqwangs    時間: 2014-4-22 15:44

好多實用的技巧
謝謝各位大大了
作者: tuetr    時間: 2014-7-11 17:29

感謝 來試試看
作者: guesspro    時間: 2014-8-16 21:39

請問  --和value 作用一樣嗎？
作者: jomeow    時間: 2014-10-30 10:57

我就是用了value來把文字轉數字..

現在看了之後真的又學懂了..

原來不用value.. 只要加--在前面就可以轉數字..
謝謝版主大大的教導...
m(_ _)m
作者: kenlin    時間: 2014-12-17 15:17

之前都用 *1
=Right(M12345,5)*1
作者: w571384    時間: 2015-3-5 16:38

怪招有樣,必殺絕技
作者: co2000801    時間: 2015-3-19 00:11

要常用才知道,原來還有--的用法,學到一招..........
作者: Bluemirror    時間: 2015-5-15 17:45

回復 32# hydraulic

擷取錯誤的話，就會出現“#VALUE!"，旁邊驚嘆號的提示是：公式中所使用的某值其資料類型錯誤。
作者: tmwcykixe    時間: 2015-8-6 08:53

負負得正，難的簡單技巧，不過很實用，謝謝分享.
作者: 小笨比    時間: 2015-9-18 17:48

回復 39# guesspro


    一樣啊
作者: ltwinery    時間: 2015-10-10 17:50

很感謝，用"--"可以轉換成數值型態是第一次學到，還有知道有快速鍵可以變換相對位置與絕對位置，但不知是那個鍵，原來是F4，很感謝版主提示
作者: hcm19522    時間: 2015-11-16 11:49

寫程式前 ,製造資料 ,我都用亂數產生 ,按F9就會改變資料 ,用 "--"有時結果不會改變 ,不知何故 ,用 "*1" 就無此問題
作者: Piagetzu    時間: 2016-3-22 17:36

回復 1# Hsieh

有個自己覺得蠻好用快速鍵: ctrl+shift+方向鍵，在整列或整行的資料開頭按這個，就可以快速全選全部的資料
有時候資料4、5百行，有這個快速鍵就不用滑鼠慢慢拉~
作者: ketrddem    時間: 2016-4-25 19:02

定義名稱其實可以大幅減化公式
尤其是excel常以整列或整欄都同類型資料，公式都是往下拉直接複製。
此時如果針對整列或整列定義名稱
公式上就直接打名稱就好了
例如A欄為日期，就定義整個A欄的名稱為日期
對別欄的公式，如果有針對日期部份要做計算的，就不用打A欄，而是直接打「日期」就行了。
通常我都會在第一列為標題列，標題列的名稱直接設定為定義名稱
作者: cict4    時間: 2016-6-28 13:13

我又學到一個了,謝謝大家!!



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