2016-08-31

〔EXCEL〕TEXT-自動編固定長度的流水號:公式/格式設定/自動填入


這篇文發想源自於我一個學長在FB上的分享…

在Excel中,若想產生一個流水序號,最簡單的方式就是寫一個公式(如上圖B欄)
=A2+1
將上一個儲存格的數字加1,即可得到新的流水號

但問題來了…Excel在判斷此值會自動判定為數字…所以會呈現1、2…10、11、12…
如果,希望流水號呈現固定碼位時(例如3碼,001、002…010、011、012…),要如何處理?

只要將儲存格改成文字格式,並設定顯示Format為補零3碼即可,有以下2種方式處理
A.  在公式內加入轉文字的函數…(想習慣使用公式的學員可利用這方式來處理)
      =TEXT( 值 , 顯示格式)
      =TEXT( A2+1, "000")

其中顯示格式"000",代表以數字3碼顯示,前面補0

B. 直接修改A欄的格式設定(其實作用跟A相同,只是一個寫公式,一個改設定)
     如上圖「儲存格格式」設定,類別改為「自訂」,並將類型設定為000即可

當然啦~~如果…你完全不想寫公式…
也可在填入'001(若要打文字,在前方加上 ' 即可)後
將滑鼠移到儲存右下方…當滑鼠變成實心+號時…按著往下拉即可直接複製流水號了…
但如果您實心+號往下拉,結果只是複製原值(例如全是001)…請同時按著Ctrl往下複製即可
(至於什麼時候該按Ctrl,什麼時候不用按…我想您直接試一下就知道了…原理跟儲存格式複製模式有關,這就不特別說明了…因為按按就知哪個行…不用太深入了解,腦袋記憶體很珍貴)



5 章老師的電腦小講堂: 8月 2016 這篇文發想源自於我一個學長在FB上的分享… 在Excel中,若想產生一個流水序號,最簡單的方式就是寫一個公式(如上圖B欄) =A2+1 將上一個儲存格的數字加1,即可得到新的流水號 但問題來了…Excel在判斷此值會自動判定為數字…所以會呈現1、2…10、11...

2016-08-24

〔EXCEL〕Find/Mid-用函數取出指定規則的字串~手動"資料剖析"變形版


當你有一大"篇"一連"串"的資料,想要將其依一定的原則切割成不同的欄位
在Excel中有個很好用的功能--『資料剖析』
可以很輕易的協助您分割資料
(怎麼做不是這篇的重點,在此就不說明了)

但如果你想切割出的資料,取出某個欄位。但它被切割出來的位置可能有些不同(喔喔>"<)
例如:
A , , B , C
A , B , C
A , B , , C

用『,』來做資料剖析會切出

如果你想取的是B或C的資料…卻發現它不在同一欄中…
或許,你可以改用函數去計算取出你要的字串唷~

以最上方那張圖為例…
如果想取出每列g_後的字串
我們可以找到其規則:
1. 每列只會有1個g_
2. 字串用"g_開頭,而結束於另一個" (也就是說只要取畫面中藍色底的字串)

可依此決定做法
A. 先找到每列g_開頭的位置→→結果寫入J欄(開始位置)
B. 找到g_開頭後"的位置→→結果寫入K欄(結束位置)
C. 這樣你就知道要找的字串是從哪段了吧

講完了思考出的作法…再來就是實做了。

●如何找到"完整字串"中的"指定字串"開始的位置?
=FIND(指定字串, 完整字串, [開始尋找的位置])
例如:
=FIND("CD","ABCDEFGFECDBA") →→3  在ABCDEFGFECDBA,找CD第一個出現的位置
=FIND("CD","ABCDEFGFECDBA",4 ) →→10 在ABCDEFGFECDBA,從第4個字元開始找CD第一個出現的位置

課後練習:
=FIND("CD","ABCDEFGFECDBA",3 ) →→??

●如何取得"完整字串"中指定位置的"字串值"?
=MID(完整字串, 開始位置, 取幾碼)
例如:
=MID("ABCDEFGFECDBA", 3, 2) →→CD 從ABCDEFGFECDBA第3碼開始取2碼字串
=MID("ABCDEFGFECDBA", 3, 4) →→CDEF 從ABCDEFGFECDBA第3碼開始取4碼字串

課後練習:
=MID("ABCDEFGFECDBA", 3, 20) →→??


後記:
其實寫函數、寫程式…並不難。重點在於你想寫什麼…想怎麼寫…先搞清楚後…再動手吧!
5 章老師的電腦小講堂: 8月 2016 當你有一大"篇"一連"串"的資料,想要將其依一定的原則切割成不同的欄位 在Excel中有個很好用的功能-- 『資料剖析』 可以很輕易的協助您分割資料 (怎麼做不是這篇的重點,在此就不說明了) 但如果你想切割出的資料,取出某個...

2016-08-21

〔EXCEL〕IF的延伸課程…多個範圍間的判斷~~h002

上一堂課 【Excel】IF,假如你不會用…小心落伍囉~( Lesson 1/1)
我們知道可以用簡單的IF判斷將一件事,分為"成立(真)"或"不成立(假)"
這是一個非A即B、非黑即白的結果…
你若不支持,那就是反對…(耶…扯遠了)

複習一下
IF(條件 , 成立-真 , 不成立-假)

但我不止一次碰到學員問我…那如果是好幾個範圍怎麼寫呢?
例如:
0~10,則為A
11~20,則為B
21~30,則為C

或是:
若<=10,則為A
若>10且<=20,則為B
若>20且<=30,則為C

上面這2種敘述,基本上是同一個問題(你發現了嗎?)

這種狀況…您可以用槽狀的IF判斷式來處理唷~~
什麼叫槽狀的…簡單來說就像下圖…大圈圈包小圈圈…一圈圈包下去…
看你想包幾層,只要函數字串長度不要超過Excel可容許範圍就行~

那您該怎麼將判斷式套用進去呢…化繁為簡,就一句句套~~

確認每句的判斷式都是正確之後…再整個組起來即可(不成立三個字被下面的判斷式取代)
=IF(A1<=10,"A",IF(A1<=20,"B",IFA(A1<=30,"C","??")))

組判斷式並不難…只要先拆分好再組起來就行了唷~~


5 章老師的電腦小講堂: 8月 2016 上一堂課  【Excel】IF,假如你不會用…小心落伍囉~( Lesson 1/1) 我們知道可以用簡單的IF判斷將一件事,分為"成立(真)"或"不成立(假)" 這是一個非A即B、非黑即白的結果… 你若不支持,那就是反對…(耶…扯遠了...

2016-08-19

〔EXCEL〕表格設計說~~利用Excel套表格式建議

Word、Excel是我們經常拿來製作表格的工具…
這兩個應用軟體的強項不同…

對我而言,Word具備很強的樣式/格式設定及邊界觀念,在填入內容時的版面控制相對輕鬆。
而Excel則具備強大的運算,本身就是一個具大的表格任你使用…

所以在製作套表時,應以表格使用功能角度去選擇…

在此分享一下我利用Excel製作表格的其中一個小技巧(使用Word時我也用這樣的概念)…




為做這個套表的目的,是為了依據來源的資料,讓Excel自動產生銀行的匯款單(印出來就跟你去銀行填的一樣)。
這個作法可以讓原本人工一張張填據或修改內容的作業,變成自動化。由Excel自動產生並且切分不同的檔案,同時存檔讓您資料被保留下來。
一個人力修改4個小時的工作,可以變成1分鐘電腦轉製+5分鐘人工列印吧(由於跟錢有關,所以不做直接列印的部份)

好了…好像有些離題了…

一般我們在繪製表格時,因表格內格子可能有大有小…很多人都會將儲存格依據需要而拉大…
今天要分享給大家的剛好相反…

善用"合併"儲存格

合併有什麼好處?

  1. 未來格式有調整時,只要解除某欄列之合併儲存格,再依需求合併即可,不會影響到其它列的大小。
  2. 每個欄位寬度間有個"比例"關係在,整個視覺效果也會比較協調,表格專業度會提升唷~
  3. 在預估內容不會超過整個範圍時,甚至可以連合併儲存格的動作都免了。


因此您看這張匯款單的表格,一開始我就直接拉了45欄,直接縮小欄寬到適合放入一個勾選框□的大小。
接著就開始照需求要的位置,把欄位標題都填進去。
滿意後,再進行欄位合併,調整字的位置並繪製框線。

很快的,一張套表格式就完成了…而且美觀度通常比拉寬產生非比例性的大小來得舒適唷~
這方式我在Word畫表格也如此,開始就分割個10、20欄等…再來合併…絕對賞心悅目~


5 章老師的電腦小講堂: 8月 2016 Word、Excel是我們經常拿來製作表格的工具… 這兩個應用軟體的強項不同… 對我而言,Word具備很強的樣式/格式設定及邊界觀念,在填入內容時的版面控制相對輕鬆。 而Excel則具備強大的運算,本身就是一個具大的表格任你使用… 所以在製作套表時,應以表格使用功能角...
< >
小講堂經過多次搬移,舊文章連結及內容較難整理~
大家可以新文章為主~
每篇文章後有代碼,任何文章問題可至FB小講堂用代碼提問唷~