EXCEL TIPS

2017/10/06

うっかり忘れがちなエクセルTIPSをまとめて紹介。

（これ以上増えたら分割するかも）

■操作系

一つ前の作業を繰り返す：

F4キー

絶対参照と相対参照の切り替え：

計算式で切り替えたいところにカーソルを合わせてF4キーで切り替える
「A1」→「$A$1」→「A$1」→「$A1」→「A1」

外部CSVファイル参照：

[データ][テキストファイル]

■文字列

指定した文字列が含まれる：

=IF(COUNTIF(A1,"*Excel*"),"○","")

参考：指定した文字列が含まれるかどうかを判断する：Excel 一般｜即効テクニック｜Excel VBAを学ぶならmoug

先頭や末尾の不要スペースを削除：

=TRIM(A1)

参考：不要なスペースは一度に削除 TRIM関数でスペースを取り除こう！ - FMVサポート : 富士通

改行を削除：

=CLEAN(B1)

参考：Office TANAKA - Excel Tips[セル内の改行を削除する]

単位の追加：

[セルの書式設定][分類]を[ユーザー定義]、[種類]に1"kg"

参考：Excelで氏名のあとに“様”や“さん”を付けたいのですが、氏名を入力しただけで“様”... - Yahoo!知恵袋

 敬称の追加：

[セルの書式設定][分類]を[ユーザー定義]、[種類]に@"様"

参考：Excelで氏名のあとに“様”や“さん”を付けたいのですが、氏名を入力しただけで“様”... - Yahoo!知恵袋

自動行番号追加：

=ROW()

■ファイル

セルにファイル名を表示（フルパス）：

=CELL("FILENAME")

セルにファイル名を表示（パス名を含まない）：

=REPLACE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))-1),1,FIND("[",CELL("filename",$A$1)),)

セルにファイルベース名を表示

=REPLACE(LEFT(CELL("filename",$A$1),FIND(".",CELL("filename",$A$1))-1),1,FIND("[",CELL("filename",$A$1)),)

参考：[Excel] セルにファイル名を表示する: Kenの我楽多館blog館

■リンク

リンクの下線を消す：

[セルの書式設定][フォント]の[下線]で[なし]を選択

参考：ハイパーリンクの下線を消す | Excel2013 | 初心者のためのOffice講座

相対pdfに対するリンク：

=HYPERLINK("Dir\test.pdf","表示文言")

■共有

ブックの共有：

[ファイル][オプション]の[セキュリティ センター][セキュリティ センターの設定]、[プライバシー オプション]で「ファイルを保存するときにファイルのプロパティから個人情報を削除する」のチェックを外す
[校閲][ブックの共有]で「複数のユーザーによる同時編集と、ブックの統合を許可する」をチェック

参考：Excelのブックの共有

■印刷

セルの枠線を印刷しない：

[ページレイアウト][ページ設定][印刷タイトル]、[ページ設定][シート]の[印刷]の枠線のチェックボックスを外す

参考：セルの枠線が印刷されてしまう場合の対処法｜Excel （エクセル） | オフィスソフト活用・業務効率化Ｑ＆Ａブログ

■条件付き書式

条件で隣のセルの色を変更：

色を付ける範囲を選択、[条件付き書式][新しいルール]で「数式を使用して、書式を設定するセルを決定」
=OR($A1="test")
[書式][セルの書式設定]の[塗りつぶし]で好みの色を選択

参考：条件付き書式のやり方。隣のセルも色付け。 -エクセル初心者です。(1)B- その他（Microsoft Office） | 教えて!goo

1行おきに色を変える：

範囲を指定、[ホーム][条件付き書式]の[新しいルール]、[ルールの種類]で「数式を使用して、書式を設定するセルを決定」
[ルールの内容]で=MOD(ROW(A1),2)=1
[書式][セルの書式設定]の[塗りつぶし]で好みの色を選択

参考：Excelで1行おきに色を付ける方法-Excel 2010編- | ミライFAN [ミライファン]

■入力制限

文字数制限：

[データ][データの入力規則]の[データの入力規則][設定]で「文字列（長さ指定）」「次の値以下」、[最大値]に文字数を入れる

参考：121ware.com > サービス＆サポート > Q&A > Q&A番号 012721

照合テーブルを可変長に：

[数式][名前の定義]で[名前]に「照合用データ」、[参照範囲]に「=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)」
[データ][データの入力規制][設定]の[入力値の種類]を[リスト]、[元の値]を「=照合用データ」

参考：「入力規則」の照合用データリストを可変にする方法 | エクセルスキル向上事典

■時間関連

期間を数える：

＝DATEDIF(開始日,終了日,"D")

参考：DATEDIF関数で期間の計算--Excel・エクセル

稼働日を数える：

=NETWORKDAYS(開始日,終了日）

参考：NETWORKDAYS関数の使い方～平日だけを数えるExcel関数

作業時間の表示：

A1に「分データ」、A2に「=A1/(60*24)」
A2セルの[書式設定][ユーザー定義]で「h:mm」

参考：エクセルで分から時間に変換したいのですが -いつもお世話になっていま- Excel（エクセル） | 教えて!goo

■演算

マイナスは0に：

=MAX(A1,)

二値の大きい方をとる：

=MAXI(A1,A2）

小数点切り捨て：

=ROUNDDOWN(A1,0)

■画面制御

見出し行の固定：

固定したいところをクリック、[表示][ウィンドウ枠の固定]

■集計

条件に合致する合計値：

=SUMIF(A1:A5,"日本",B1:B5)

条件に合致する個数：

=COUNTIF(A1:A5,"日本")

N/Aの除外：

=SUMIF(A1:A5,"<>#N/A")

■条件分岐

以上かそれ以外か：

=IF(A1>=80,"合格","不合格")

ゼロ除算を除外：

=IF(A1="","",A2/A1)

複数条件：

=IF(OR(条件1,条件2),真,偽)
=IF(AND(条件1,条件2),真,偽)