Excel

多個儲存格輸入

• 選取多個儲存格
• 輸入內容（可以是公式，會以參照位址填滿）
• Ctrl + Enter

Enable "Developer" tab

Personal Macro / Global Macro

Reference

• Developer
  • Record Macro
• Store macro in
  • Personal Macro Workbook
• Developer
  • Stop Recording

Edit Personal Macro

• Developer
  • Visual Basic
    • VBA Project (PERSONAL.xlsb)

Paste value

Sub PasteText()
    ' Paste:=xlPasteValues will cause 1004 error
    ' xlPasteAll only paste value, Bug?
    Selection.PasteSpecial Paste:=xlPasteAll
End Sub

Keyboard Shortcut

Reference

Description	Key
Edit cell	F2
New line in the same cell	Alt + Enter
Paste value	Ctrl + Alt + V V
Copy the the topmost cell of a selected range into the cells below	Ctrl + D
Apply the General number format	Ctrl + Shift + Tilde(~)
Apply the Currency format	Ctrl + Shift + Dollar($)
Apply the Percentage format	Ctrl + Shift + Percent(%)
Apply the Date format	Ctrl + Shift + Number(#)
Apply the Time format	Ctrl + Shift + At(@)
Apply the Number format	Ctrl + Shift + Exclamation point(!)
Repeat the last action	Ctrl + Y
Select row	Shift + Space
Select column	Ctrl + Space
Insert row / column	Ctrl + Shift + Plus(+)
Delete row / column	Ctrl + Minus(-)
Enter the current date	Ctrl + Semi-colon(;)
Enter the current time	Ctrl + Shift + Colon(:)
Move to edge of data range	Ctrl + Arrow
Move to edge of data range	End Arrow
Open the list of validation / auto-complete	Alt + Down

Note

• Global object is Application (like window in JavaScript)

Highlight duplicates

Reference

• Select the cells you want to check for duplicates
• Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Formula / Function

Operator

Operator	Description
+	Addition
–	Subtraction
*	Multiplication
/	Division
%	Percent
^	Exponentiation
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to
&	Concatenate
Space	Intersection range

Reference

Reference

Reference to cell

<column><row>

Reference to range

<column><row>:<column><row>

Reference to column

<from column>:<to column>

Reference to row

<from row>:<to row>

Reference to sheet

[<sheet name>[:<sheet name>]!]<cell>

Absolute reference

$<column>$<row>

When editing, press F4 to change between relative and absolute

VLOOKUP

找尋 <range> 第 1 欄中等於 <value> 的列，然後傳回 <column> 所指的欄的值。如果找不到會傳回 #N/A。

VLOOKUP(
  <value | cell to lookup>,
  <range>,
  <column of return value>,
  { TRUE | FALSE }
)

TRUE	Approximate match
FALSE	Exact match

Demo

	A	B
1	Apple	10
2	Banana	20
3	Cat	30

=VLOOKUP("Apple", A1:B3, 2, FALSE)

# 10

Equal to

VLOOKUP search from begin, but MATCH will searh from the end

=INDEX(B1:B3, MATCH("Apple", A1:A3, 0))

INDEX

Return the value of <row> in <range>

INDEX(<range>, <row>[, <column>])

MATCH

Retrun the index of <value> in <range>

MATCH(<value>, <range>, 0)

SUM

SUM(<value | range>[, ...])

IF

IF(
  <expression>,
  <the return value if TRUE>,
  <the return value if FALSE>
)

IFS

For Excel 2016 and above

IFS(
  <expression>, <the return value if TRUE>
  [, ...]
)

IFS(
  <expression 1>, <value 1>,
  <expression 2>, <value 2>,
  TRUE, <value 3>
)

same as

IF(
  <expression 1>, <value 1>, 
  IF(<expression 2>, <value 2>, <value 3>)
)

SUMIF

SUMIF(
  <range>,
  "<comparison operator><value>"
  <range of value to sum>
)

Demo

	A	B
1	Apple	10
2	Banana	20
3	Apple	30
4	Cat	40

=SUMIF(A1:A4, "=Apple", B1:B4)

# 40

SUMIFS

SUMIF(
  <range of value to sum>,
  <range>, "<comparison operator><value>"
  [, ...]
)

Demo

	A	B	C
1	Apple	Red	10
2	Banana	Yellow	20
3	Apple	Green	30
4	Cat	White	40
5	Apple	Red	50

=SUMIFS(C1:C5, A1:A5, "=Apple", B1:B5, "=Red")

# 60

=SUMIFS(C1:C5, A1:A5, "=Apple", B1:B5, "=Green")

# 30

CHOOSE

CHOOSE(<index>, <value | range>[, ...])

Equal to JavaScript

[<value>, ...][<index> - 1]