Re: [請益] 事務所的軟體

[noworneverev (小朋友)]

原文恕刪

小弟事務所菜逼巴+Excel新手，
斗膽來分享一下我平常上班時會用的一些功能，歡迎指教

圖文版：https://noworneverev.github.io/2017/11/25/excel-basic/

＊快捷鍵：
─┬─────────┬────────┐
1.│  Ctrl + C        │ 複製           │
─┼─────────┼────────┤
2.│  Ctrl + X        │ 剪下           │
─┼─────────┼────────┤
3.│  Ctrl + V        │ 貼上           │
─┼─────────┼────────┤
4.│  Ctrl + Alt + V  │ 選擇性貼上     │
─┼─────────┼────────┤
5.│  Ctrl + B  或 + 2│ 粗體           │
─┼─────────┼────────┤
6.│  Ctrl + A        │ 全選           │
─┼─────────┼────────┤
7.│  Ctrl + Z        │ 還原           │
─┼─────────┼────────┤
8.│  Ctrl + F        │ 尋找           │
─┼─────────┼────────┤
9.│  Ctrl + H        │ 取代           │
─┼─────────┼────────┤
10│  Ctrl + P        │ 列印           │
─┼─────────┼────────┤
11│  Ctrl + 1        │ 開啟儲存格格式 │
─┼─────────┼────────┤
12│  Ctrl + D        │ 向下填滿       │
─┼─────────┼────────┤
13│  Ctrl + PgDn     │ 下一張工作表   │
─┼─────────┼────────┤
14│  Ctrl + PgUp     │ 上一張工作表   │
─┼─────────┼────────┤
15│  Alt + =         │ 向上加總       │
─┴─────────┴────────┘

＊公式

基本概念：Excel中冒號(:)表連續，錢錢符號($)表絕對位置
          公式(Function)有幾個前括弧就要有幾個後括弧
          單引號(')會將該儲存格轉成文字格式

＊範例檔下載：https://goo.gl/WFdbcG

1. =Sumif(準則範圍, 加總準則, [加總範圍])
   若沒有加總儲存格的範圍，默認加總範圍為準則範圍
   例如用"a"這個tickmark來標示你要加總的地方(A1:A4)，
   把"a"打在如[B1]、[B2]儲存格

  │A │B
  ┼─┼────
  │1 │a
  ┼─┼────
  │2 │a
  ┼─┼────
  │3 │
  ┼─┼────
  │4 │
  ┼─┼────

   =Sumif($B:$B, "a", $A:$A) = 3

   在打公式時按F4將該範圍變成絕對位置，
   如此公式才不會在複製時就跑掉，
   一般Excel的公式都是相對位置，
   所以例如複製[A11]=Sum(A1:A10)到[B11]時公式會自動變成=Sum(B1:B10)。

   上面Sumif是加總，如果Countif就計數，概念差不多


2. =vlookup(你要找的值, 在哪裡找, 回傳第幾欄, 是否完全符合)

   用到爛的vlookup，大部分人找值都是用這個函數，無腦又好用

  │A │B
  ┼─┼───────────────
  │1 │=vlookup(A1, $C:$E, 2, false) = PPT
  ┼─┼───────────────
  │2 │=vlookup(A2, $C:$E, 2, false) = PTT
  ┼─┼───────────────
  │3 │#N/A
  ┼─┼───────────────
  │4 │#N/A
  ┼─┼───────────────

 資料來源：
  │C │D
  ┼─┼───────────────
  │2 │PTT
  ┼─┼───────────────
  │6 │
  ┼─┼───────────────
  │1 │PPT
  ┼─┼───────────────
  │7 │
  ┼─┼───────────────

為了避免因為資料來源沒該筆查找資料而跑出#N/A的狀況
我會將公式改寫成=IFERROR(vlookup(A1, $C:$E, 2, false),"")
多個IFERROR來進行錯誤處理，將#N/A變空白""

VLOOKUP還有一個限制是它只能尋找表格陣列最左邊的欄中的值，
當然可以自己整理一下表格什麼的處理，
不過Excel還有INDEX和MATCH可以來完美取代VLOOKUP，
查找大量資料時經過實測速度也是屌打VLOOKUP

官方文件：
MATCH 函數會搜尋儲存格範圍中的指定項目，並傳回該項目於該範圍中的相對位置。
例如，若範圍 A1:A3 中含有值 5、25 及 38，則公式 =MATCH(25,A1:A3,0) 會傳回數字
 2，因為 25 是範圍中的第二個項目。


INDEX的使用方法為：=INDEX(資料來源, 第幾列, 第幾欄)

所以基本的概念就是，用MATCH先找到你的資料是第幾列，再指定你要回傳的欄位
=INDEX(資料來源, MATCH(~~~), 欄位)
範例檔有一個簡易匯率表用INDEX & MATCH來完成可參考


3. =IF(logic_test, if True, if False)

IF公式也是常用的前幾名，IF...Then...ELSE

常搭配AND()公式使用，=AND(1=1,2=2,3=3) 傳回TRUE
                     =AND(1=1,2=2,3=2) 傳回FALSE 用作邏輯測試判斷TRUE or False

另外可以寫成巢狀來處理更多的情況
IF(logic_test, if True, if(logic_test, if True, if(logic_test, if True,false)))
範例檔有一個隨便寫的巢狀公式可參考

因為Excel公式都要寫成一行，如果真的遇到要寫巢狀公式時，
可以拿紙筆出來實際寫較有感覺
IF...THEN
  xxx
ELSE IF
  xxx
ELSE IF
  xxx
ELSE
  xxx
END IF


樞紐拉一拉的東西好像沒什麼好說的，另外還有篩選也是很重要
不過我現在都用自己寫的篩選，有點脫離Excel內建的功能，有時間再分享


-----------------------------------------
＊ Excel 切換編輯模式
    文章代碼：#1Q3wLTS_
    下載：https://goo.gl/2ziV2A

＊ Excel 自訂選擇性貼上
    文章代碼：#1Q1pEUVg
    下載：https://goo.gl/3SCy2d

＊ Excel 透過公開資訊觀測站查詢財報
    文章代碼：#1P_9YjWs
    下載：https://goo.gl/iUc49o
    Demo檔下載：https://goo.gl/8PUAAy

＊ Excel 一鍵轉底稿
    文章代碼：#1Pz9ZLY0
    下載：https://goo.gl/oBxQug

＊ Excel 千分位&基本Tickmark製作教學
    文章代碼：#1PxCHzh1
    下載：https://goo.gl/gimhNd

＊ Excel 自訂插入儲存格按鈕
    文章代碼：#1PuWvwqq
    下載：https://goo.gl/Q53w7T

＊ Excel 自訂工作表字型按鈕
    文章代碼：#1PqA0Hn2
    下載：https://goo.gl/NCGsnY

--
                Accoding: https://www.facebook.com/AccodingTW/

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 112.104.109.247
※ 文章網址: https://www.ptt.cc/bbs/Accounting/M.1511544733.A.5F1.html
推 czy1992: 太強悍了！必須推一個
推 kenny850317: 推好文
推 thbetray: 推
推 participate: 推 實用
推 myth0422: 明明就EXCEL高手 真是謙虛了
推 amyjoey: 推推
推 Nccjia: 推
推 qazedc97: PUSH!
推 a0970969135: 推，謝謝:)))
推 kh2702: 還不錯
推 blackteashop: 開課囉！推推
推 l8912000: 推
推 Ewighket: 推
推 nochipupu: 推
推 pinkgloomy: 推
推 suhsuan829: 推推
→ sweet79731: 這一篇文章值 986 Ptt幣 最近做內控才有空對吧
這禮拜高鐵通勤，也不算很有空...
推 holykaoo: 得推
推 lyly555: 推
推 beefer6281: 謝謝
推 ilikefalali: 推
推 stcr3011: 大推一個
※ 編輯: noworneverev (112.104.23.68), 11/25/2017 16:42:50
推 ifalmost: 推
推 wowlalow: 推
推 saffron: 推
推 Bien: 推推
網頁版： https://noworneverev.github.io/2017/11/25/excel-basic/
※ 編輯: noworneverev (112.104.23.68), 11/25/2017 21:20:43
推 zoopreme2006: 推
推 anguskenneth: 推
推 asd123159: 推
推 book3433: 推
推 kpckpc: 推
推 ppoo4413: 推
推 jeremyZH: 推
推 w656yl4ej: 強
推 wow777: 推
推 goddioshaha: 推
推 Mochi0506: 推
推 sandy3542200: 推推
推 segaboy: 大推，受益良多
推 Leifox: 實用又明瞭說明太強大了!!!
推 gab7912: 推
推 ZZZZ0: 高手高高手
推 Ankerlooper: 你484下面的staff教的很辛苦 上來救贖新鮮的肝
→ alipixel: 推
推 kscute: 厲害推