よく使うエクセル関数の使い方

仕事でよく使うエクセルの関数を集めてみました。
簡単な小技なども紹介しています。
 

平均を求める関数

AVERAGE

空白や文字列は対照としません

AVERAGEA

空白や文字列は 0 としてカウントします

 

セルの個数を数える関数

COUNT

数値が入力されたセルの個数

COUNTA

空白以外の数値や文字セルの個数

COUNTIF

条件を指定してマッチしたセルの個数

 

相対的な順位を求める関数

LARGE

1番大きい2番目3番目と大きい順に呼び出します

SMALL

1番小さい2番目3番目と小さい順に呼び出します

 

数値の四捨五入、切捨て、切上げ

ROUND

指定した桁での数値の四捨五入

ROUNDDOWN

指定した桁での数値の切捨て

ROUNDUP

指定した桁での数値の切上げ

 

合計値を求める関数

SUM

範囲に含まれる数値をすべて合計します

SUMIF

条件に合ったものだけを合計します

 

日にちを求める関数

YEAR

年 (2018年)

MONTH

月 (11月)

DAY

日 (21日)

WEEKDAY

曜日(数値 3)(水曜日)

EOMONTH

月末の日付

TODAY

現在の日付に対応するシリアル値

 

時間を求める関数

HOUR

時刻を返します (15時)

MINUTE

時刻の分を返します (30分)

SECOND

時刻の秒を返します (50秒)

TIME

時刻に対応する小数を返します

TIMEVALUE

文字列で表された時刻を小数に変換

 

文字列でよく使う関数

LEN

文字列の文字数を返します

MID

指定された文字数の文字を返します

SEARCH

文字列を検索してその文字の位置を返します

SUBSTITUTE

文字列の中から指定した文字を置き換え(置換)します

 

組み合わせてよく使う関数

名簿などの氏名の分割と結合
(1つのセルにある姓と名を分割)(姓のセルと名のセルを結合)

全角表示やあいまいな表示を半角表示に統一
(半角表示に統一:THOMAS Edison → Thomas Edison)

ランダムパスワード生成
(文字列や大文字、小文字など、指定してパスワードを作ります)

条件に一致したデータを呼び出す
(検索してピックアップする)

姓名から作る4桁数字のアルゴリズムパスワード
(姓名から文字を抜き取り数値化してパスワードにします)

エラーを見えなくするISERRORとIFERROR
(ISERRORより便利な関数IFERRORがEXCEL2007から追加されました)

2進数を10進数に変換するBIN2DEC()関数の上限
(10進数511、2進数111111111←1が9個が上限です。2進数15桁まで変換できる式を作ってみました。おまけは50桁まで)

10進数を2進数に変換するDEC2BIN()関数の上限
(10進数511、2進数111111111←1が9個が上限です。2進数15桁まで変換できる式を作ってみました)

IF関数の使用回数の上限
(IFの入れ子には7回の制限があります。エラー:「入れ子のレベル数が~~」が出たら制限です)

EXCEL2016以降に追加された新関数 IFS()関数
(EXCEL2016からIF()関数の複数に対応したIFS()関数が追加されました)

EXCEL2016以降に追加された新関数 MAXIFSとMINIFS
(複数の条件で最大値、最小値を出すMAXIFSとMINIFS)

byte(バイト)を、KBやMBに換算
(byte(バイト)を、KBやMB、GB、TBに換算します)

VLOOKUP()関数でのエラーや完全一致が複数ある場合の対応
(VLOOKUP()で検索値が無い場合のエラーや複数ある場合わかるようにする)

VLOOKUP()関数の左側を取得したい場合
(OFFSET()関数とMATCH()関数を組み合わせ左側を取得)

統計などで集めた回答を半角小文字に統一する方法
(大文字を半角小文字に、「,」カンマを消します)

光年ってどのくらい距離か計算してみる
(よく300光年先に・・というが1光年ってどのくらいか計算する)

ナンバーズ3の予想をしてみる
(いろんな関数のいろんな使い方を紹介)

正〇〇角形(正多角形)を表にしてみる
(正三角形、正四角形、正五角形・・・正多角形を表にしてみる)

値を連結させる &、CONCATENATE、CONCATの違い
(セルの値や文字列などをつなげる3つの方法)

 

遊:デジタル時計を作ってみる
(セルに1つ1つデータを与えてデジタル時計を作ってみる)

遊:さいころの確立表を作ってみる
(RAND()関数とRANK(数値,参照,順序)関数の使い方です)

遊:不思議なパズルを数式で解いてみる
(8×8=5×13になる不思議なパズルを作る)

遊:エクセルでポーカーを作ってみる
(RAND関数とVLOOKUP、LARGE関数でポーカーを作ります)

遊:ひらがなを数字にする
(「ひらがなを数字に変換」「数字をひらがなに変換」)

 

相違点:「AVERAGE」と「AVERAGEA」の違い
(数学的な平均値を求めるAVERAGE()関数とAVERAGEA()関数の違い)

相違点:「COUNT」「COUNTA」「COUNTIF」の違い
(セルを数える3つの関数COUNT()とCOUNTA()とCOUNTIF())

相違点:「SUM」と「SUMIF」の違い
(選択したセルを合計するSUM()と条件一致したセルを合計するSUMIF())

相違点:「NOW」と「TODAY」の違い
(今を取得するNOW()関数と今日を取得するTODAY()関数)

相違点:「ISERROR」と「IFERROR」の違い
(セルの値がエラー時に使用する関数ISERRORとIFERROR)

相違点:「&」「CONCATENATE」「CONCAT」の違い
(セルの値や文字列などをつなげる3つの方法)

 

なんでも数式で解いてみる「ぐるぐる」
(中心からぐるぐる増える数字の1部を数式で解く)

なんでも数式で解いてみる「時計の針」
(長針と短針の角度をエクセルで解いてみる)

なんでも数式で解いてみる「時計の針2」
(長針と短針が再び針が重なる時間をエクセルで解いてみる)

なんでも数式で解いてみる「三角形」
(ピラミッド型三角形に配置された数字のの規則性を解いてみる)

なんでも数式で解いてみる「三角形2」
(パスカルの三角形の規則性を解いてみる。1000段までいってみました)

なんでも数式で解いてみる「白と黒の比率」
(図形の白と黒の比率を求めてみる。似てるけど勾玉ではありません)

なんでも数式で解いてみる「コラッツの問題」
(コラッツの問題(コラッツの予想)を解いてみる。)

なんでも数式で解いてみる「フェルマーの最終定理」
(なぜ xn + yn = zn は成りたたないかを考えてみた)

なんでも数式で解いてみる「ルジャンドル予想」
(ルジャンドル予想を素数 99991(9592行)まで計算してみる)

なんでも数式で解いてみる「ブロカールの問題」
(n!+1=m2を満たす自然数 n が存在するか計算してみる)

 

エクセル関数を取得するツール検索順位比較ツール

誕生日から今日までの日数と歳月のエクセル関数を生成
(誕生日を入力すると今日までの日数、歳月の数式を表示します)

 

その他(関数以外の設定など)

プルダウンメニュー
(プルダウンメニュー、ドロップダウンリスト、入力規則の設定)

入力規則(入力したいセルをクリックすると自動的に切り替える)

指定したセル以外入力できないようにする(シートの保護)
(指定したセル以外は入力不可にしてエラーメッセージを出す)

セルの左上に出る緑の三角形マークを消す方法
(文字列として認識されたセルを一気に数値化する)

縦の並び替えと横の並び替え
(縦の並べ替えは一般的ですが横の並び替え方を紹介します)

超便利ワザ!画面のスクリーンショット
(指定された範囲内の画面のスクリーンショットの撮り方)

セル内の余白の取り方
(セル内で左にくっつけたくない場合の余白の取り方)

セル内にUPやDOWNなどのアイコンを表示
(条件付き書式設定でUPやDOWNなどのアイコンを表示)

列のアルファベットを数字で表示する「R1C1参照形式」
(列のアルファベットを数字で表示することができます)

謎のコード「jan:」「178956970.625」
(エクセルにのセルに「jan:」と入力すると・・・############)

⦿オプションボタンと☑チェックボックスの使い方
(アンケートなどでよく見かける「⦿」オプションボタンや「☑」チェックボックスを使って簡単な集計ができます)

漢字の上にフリガナを付ける(ルビを振る)方法
漢字(カンジ)(ウエ)フリガナを()ける(ルビを()る)方法(ホウホウ)

 

トラブル別対処方法

・エクセル画面以外がクリック、操作できなくなった
→「alt」+「Ctrl」+「Del」でキャンセルを選択

・保存しているのに開くと数式が消えたりや書式がなくなっている
→拡張子確認「.csv」の場合名前を付けて保存で「EXCELブック」で保存

VLOOKUP関数が日時を検索してくれない

セルの左上に謎の緑の三角形がある

開発タブが見当たらない、消えた

コピーして来たセルの矢印アイコンが消えない

Analyticsなどでダウンロードしたcsvが文字化けする場合

昨日:6485/今日:6269/Total:28071227

(C) 2001-2018 Digital World