Excel チートシート

すべてのツール

無料

検索・印刷できる Excel のリファレンス — 必須の数式、検索関数、テキストと日付の関数、ショートカット、ピボットテーブル。無料。

Essential formulas

=SUM(A1:A10)

Add a range of numbers

=AVERAGE(A1:A10)

Mean of a range

=COUNT(A1:A10)

Count cells containing numbers

=COUNTA(A1:A10)

Count non-empty cells

=MIN(A1:A10) =MAX(A1:A10)

Smallest / largest value

=ROUND(A1, 2)

Round to 2 decimal places

=SUMIF(B:B,">100")

Sum values matching a condition

=SUMIFS(C:C,A:A,"x",B:B,">5")

Sum with multiple conditions

=COUNTIF(A:A,"yes")

Count cells matching a condition

=AVERAGEIF(A:A,">0")

Average of cells matching a condition

Lookup & reference

=VLOOKUP(key,A:C,3,FALSE)

Look up a key, return col 3 (exact match)

=HLOOKUP(key,1:3,2,FALSE)

Horizontal lookup across rows

=XLOOKUP(key,A:A,C:C)

Modern lookup (any direction)

=XLOOKUP(key,A:A,C:C,"N/A")

XLOOKUP with a not-found default

=INDEX(C:C,MATCH(key,A:A,0))

Flexible lookup left or right

=MATCH(key,A:A,0)

Position of a value in a range

=INDEX(A1:C10,2,3)

Value at row 2, column 3 of a range

=OFFSET(A1,2,1)

Reference offset from a cell

=INDIRECT("A"&row)

Build a reference from text

Text functions

=LEFT(A1,3) =RIGHT(A1,3)

First / last 3 characters

=MID(A1,2,4)

4 chars starting at position 2

=LEN(A1)

Number of characters

=TRIM(A1)

Remove extra spaces

=UPPER / LOWER / PROPER(A1)

Change case

=CONCAT(A1,B1)

Join text values

=TEXTJOIN(", ",TRUE,A1:A5)

Join a range with a delimiter

=SUBSTITUTE(A1,"-","")

Replace text occurrences

=FIND("x",A1)

Position of text (case-sensitive)

=TEXT(A1,"0.00%")

Format a number as text

=TEXTSPLIT(A1,",")

Split text into columns by a delimiter

Date & time

=TODAY() =NOW()

Current date / date-time

=YEAR(A1) =MONTH(A1) =DAY(A1)

Extract date parts

=DATE(2026,6,26)

Build a date from parts

=DATEDIF(start,end,"d")

Days/months/years between dates

=EDATE(A1,3)

Date 3 months after A1

=EOMONTH(A1,0)

Last day of the month

=NETWORKDAYS(start,end)

Working days between dates

=WEEKDAY(A1,2)

Day of week (Mon=1)

=A1-B1

Difference in days between dates

Logical & error handling

=IF(A1>10,"hi","lo")

Branch on a condition

=IF(A1>10,IF(A1>20,"x","y"),"z")

Nested IF

=IFS(A1>20,"x",A1>10,"y",TRUE,"z")

Cleaner multi-branch logic

=AND(A1>0,B1<10)

True if all conditions hold

=OR(A1>0,B1<10)

True if any condition holds

=NOT(A1)

Invert a boolean

=IFERROR(A1/B1,0)

Return 0 on error

=IFNA(VLOOKUP(...),"")

Handle only #N/A errors

=SWITCH(A1,1,"a",2,"b","?")

Match a value to a result

References & names

A1

Relative reference (shifts on copy)

$A$1

Absolute reference (locked)

A$1 / $A1

Lock row only / column only

F4

Cycle reference lock styles while editing

Sheet2!A1

Reference a cell on another sheet

A1:A10

A range of cells

A:A / 1:1

Whole column / whole row

Name Box: TaxRate

Define a named range for clarity

=Table1[Amount]

Structured reference to a table column

Keyboard shortcuts

Ctrl + C / V / X

Copy / paste / cut

Ctrl + Z / Y

Undo / redo

Ctrl + Shift + V

Paste special

Ctrl + Arrow

Jump to edge of data region

Ctrl + Shift + Arrow

Select to edge of data region

Ctrl + ;

Insert today's date

Alt + =

AutoSum the column/row

Ctrl + T

Convert range to a Table

F2

Edit the active cell

Ctrl + Shift + L

Toggle filters

Ctrl + 1

Open Format Cells dialog

Pivot tables & analysis

Insert > PivotTable

Summarize data interactively

Rows / Columns / Values

Drag fields to build the layout

Value Field Settings

Switch Sum / Count / Average etc.

Show Values As > % of Total

Display values as a share

Right-click > Group

Group dates or numbers into bins

Refresh (Alt + F5)

Reload pivot from source data

Insert > Slicer

Add visual filter buttons

=GETPIVOTDATA(...)

Reference a pivot cell in a formula

Insert > PivotChart

Chart driven by a pivot table

「:q」に一致する項目はありません。