トップ差分一覧ソース検索ヘルプログイン

《Soft》Excel

Excel関数

ひとつ上のセルを参照する表記

	A	B	C
1	A1
2	=A1	A1形式の相対参照	行が消されると#REF!になる
3	=R[-1]C	R1C1形式の相対参照	行が消されると#REF!になる
4	=OFFSET(A4,-1,0)	OFFSET関数で自分の位置からひとつ上を記載し、高さと幅を省略	自分の位置から以外の場合、消去したときずれる可能性あり
5	=INDIRECT(ADDRESS(ROW()-1,COLUMN()))	INDIRECT関数+ADDRESS関数+ROW関数+COLUMN関数	参照セルが不要

計算式で3桁区切りカンマを表記する方法

=TEXT(A1,"#,##0")　TEXT(数値, 表示形式)　数値以外にも書式は自由になる
=FIXED(A1,0)　　FIXED (数値, 桁数, 桁区切り)　桁数省略は2(小数2位)/桁区切り省略でカンマあり　※数値でない場合エラー（#VALUE!）になる

整数の場合は整数表示、小数の場合は小数点表示を行う方法

備忘録：EXCELで、整数の場合は整数表示、小数の場合は小数点表示を行う方法 | 情報サービス／WEB・業務システム企画・開発・運用の株式会社オガコムジャパン
https://www.ogacom.co.jp/?p=546

通常の表示は小数点なし「##0」など
条件付き書式で、=A1-int(A1) > 0 のときに、「##0.#」などとする
※単純に「##0.#」では、小数点以下が0でも「*.」と表示されるため

マイナス時間の表記

時間だけの場合は「[h]:mm;[赤]-[h]」

計算式で絶対値(ABS)を使って書式を整える
=TEXT(ABS(式),IF(式<0,"-mm:ss","mm:ss"))
=TEXT(ABS(式),IF(式<0,"-hh:mm","hh:mm"))

マイナスの時間計算～時間を引き算すると『######』
https://kokodane.com/2013_waza_059.htm

ファイル名・ワークシート名を表示させる

ファイル名・ワークブック名をセルに表示－CELL関数・FIND関数・MID関数:Excel(エクセル)の関数・数式の使い方-文字列
http://www.relief.jp/docs/001313.html

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

ワークシート名をセルに表示－CELL・FIND・LEN・RIGHT関数:Excel(エクセル)の関数・数式の使い方-文字列
http://www.relief.jp/docs/001079.html

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

派生して、[ファイル名]ワークシート名

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("[",CELL("filename",A1))+1)

IFS()

Office 365 サブスクリプションを購入しているユーザーのみが使用できます

IFS 関数 - Office サポート
https://support.office.com/ja-jp/article/ifs-%E9%96%A2%E6%95%B0-36329a26-37b2-467c-972b-4a39bd951d45

IFS 関数は、1 つ以上の条件が満たされているかどうかをチェックして、最初の TRUE 条件に対応する値を返します。

CHOOSE()

CHOOSE 関数 - Office サポート
https://support.microsoft.com/ja-jp/office/choose-%E9%96%A2%E6%95%B0-fc5c184f-cb62-4ec7-a46e-38653b98f5bc

インデックス番号に基づいて最大 254 個の値から 1 つを選択

ExcelのCHOOSE関数の使い方を知っていると得なわけ | ですぎたまにぃー
https://www.desugitamanee.com/excel-choose

CHAR()

CHAR()と文字コード

n	HEX(n)	CHAR(n)	備考
9	09		Tab 水平タブ
10	0A		改行コード LF（Line Feed）
11	0B		垂直タブ
12	0C		改ページ
13	0D		改行コード CR（Carriage Return）
29	1D		繰り返し区切り（FileMaker）
32	20		空白文字 Space
34	22	"	ダブルクォート　★（ファイル名に使えない文字）
38	26	&
39	27	'
42	2A	*	★（ファイル名に使えない文字）
44	2C	,	半角カンマ
45	2D	-
46	2E	.
47	2F	/	★（ファイル名に使えない文字）
48	30	0
57	39	9
58	3A	:	★（ファイル名に使えない文字）
59	3B	;	★（ファイル名に使えない文字）
60	3C	<	★（ファイル名に使えない文字）
61	3D	=
62	3E	>	★（ファイル名に使えない文字）
63	3F	?	★（ファイル名に使えない文字）
64	40	@
65	41	A
90	5A	Z
91	5B	[
92	5C	\	★（ファイル名に使えない文字）
93	5D	]
94	5E	^
95	5F	_
96	60	`
97	61	a
122	7A	z
123	7B	{
124	7C	\|	★（ファイル名に使えない文字）
125	7D	}
126	7E	~
127	7F		Delete

参考：ASCII文字コード： IT用語辞典
http://e-words.jp/p/r-ascii.html

使用例

=SUBSTITUTE(facility_list!O27,"<br/>",CHAR(10))
「facility_list!O27」のタグ「<br/>」を「改行コード LF」に置換

=SUBSTITUTE(SUBSTITUTE(facility_list!AS14,"<br/>"," "),CHAR(34),)
（2回処理）「facility_list!AS14」のタグ「<br/>」を「半角スペース」に変換し、ダブルクウォート「"」を削除

=CHAR(CODE(J4)+1)
文字列を１つ後ろにずらす（「z」は「{」、「Z」は「[」になるので注意）

=TEXT(CODE(P4)-80,"00")
文字列を2桁の数字にする（3桁になる可能性が高いので、調整で80ずらしている）

COUNTIF

COUNTIF 関数 - Microsoft サポート
https://support.microsoft.com/ja-jp/office/countif-%E9%96%A2%E6%95%B0-e0de10c6-f885-4e71-abb4-1f464816df34

IF 関数で「ｘｘという文字を含む場合」の分岐処理｜クリエアナブキのちょこテク
https://www.crie.co.jp/chokotech/detail/383/

たとえば下図の場合は、「=COUNTIF(A2 , "*A*")」という数式を作成しています。
「A」が含まれていれば「1」が、含まれていなければ「0」が返ります。(厳密には、含まれているセルが 1 つ、含まれているセルが 0 ということだけれど)

FIND

FIND 関数、FINDB 関数 - Microsoft サポート
https://support.microsoft.com/ja-jp/office/find-%E9%96%A2%E6%95%B0-findb-%E9%96%A2%E6%95%B0-c7912941-af2a-4bdf-a553-d0d89b0a0628

特定の文字列以前の文字を消す

=IFERROR(RIGHT(A1,LEN(A1)-FIND(".jp",A1)-2),"")
「.jp」以前の文字列を消したものに（ルートパスに変更）

【Excel】特定の文字から文字までの間を削除する | 教えて!goo
https://oshiete.goo.ne.jp/qa/8656370.html#google_vignette

セルの中身を改行位置で別々のセルに分ける

【エクセル時短】セルの中身を改行位置で別々のセルに分ける方法。文字列抽出にCHAR関数を活用する！ | できるネット
https://dekiru.net/article/22371/

=LEFT(A1,FIND(CHAR(10),A1)-1)
=RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1))

セルの先頭から改行まで、一つ目の改行以降の文字列を取得する数式－FIND・CHAR・LEFT・MID関数:Excel(エクセル)の関数・数式の使い方/文字列
https://www.relief.jp/docs/013811.html

参考：さらにセル内末の（）を外して改行で分ける方法

 =IF(COUNTIF
     (D3,"*"&CHAR(10)&"*"),
       MID(
         D3,
         FIND(CHAR(10),D3)+1,
         FIND("（",D3,FIND(CHAR(10),D3))-FIND(CHAR(10),D3)-1
         )
     ,"")

LEN

LEN 関数、LENB 関数 - Microsoft サポート
https://support.microsoft.com/ja-jp/office/len-%E9%96%A2%E6%95%B0-lenb-%E9%96%A2%E6%95%B0-29236f94-cedc-429d-affd-b5e33d2c67cb

右から（後から・末尾から）n文字を削除したい－LEN関数・LEFT関数:Excel(エクセル)の関数・数式の使い方/文字列
https://www.relief.jp/docs/003606.html

=LEFT(A1, LEN(A1)-7)

MOD(数値, 除数)

MOD 関数 - Microsoft サポート
https://support.microsoft.com/ja-jp/office/mod-%E9%96%A2%E6%95%B0-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3

数値を除数で割ったときの剰余を返します。 戻り値は除数と同じ符号になります。

条件付き書式を使って、ｎ行おきに書式を変更できる

エクセルの条件付き書式で行に色を付ける方法｜複数条件の設定方法も！ - リテラアップで仕事効率化
https://litera.app/blog/excel-conditional-format-highlight-row/

「=MOD(ROW(),2)=0」と入力しましょう。ROW関数はそのセルの行数を返す関数で、MOD関数はある数を割った際の余りを返すエクセル関数です。
つまり、ある行の番号を2で割った余りが0（偶数行）に一致する条件を指定しています。

IMAGE(source)

IMAGE 関数
https://support.microsoft.com/ja-jp/office/image-%E9%96%A2%E6%95%B0-7e112975-5e52-4f2a-b9da-1d913d51f5d5?ns=excel&version=90&ui=ja-JP&rs=ja-JP&ad=us

イメージがフォルダー内にある場合は、OneDrive オンラインの場所をソースとして使用できます。 オンラインの場所を見つけるには、ファイルを右クリックし、 OneDrive > [オンラインで表示] を選択します。

エラー
#VALUE：画像ファイルがサポートされていないなど
#CONNECT!：接続またはサーバーに問題がある
#BLOCKED!：セキュリティ設定によってアクセスがブロックされている

#BLOCKEDを修正する方法! エラー - Microsoft サポート
https://support.microsoft.com/ja-jp/office/-blocked%E3%82%92%E4%BF%AE%E6%AD%A3%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-%E3%82%A8%E3%83%A9%E3%83%BC-13be117b-92e4-400a-a215-aa59d37d6e7c

「Excel」に新関数「IMAGE」が追加～セルへインターネットの画像を簡単に挿入できる - 窓の杜
https://forest.watch.impress.co.jp/docs/news/1434913.html

Windows：バージョン 2209（Build 15608.10000）およびそれ以降

WEBSERVICE(URL)

WEBSERVICE 関数
https://support.microsoft.com/ja-jp/office/webservice-%E9%96%A2%E6%95%B0-0546a35a-ecc6-4739-aed7-c0b7ce1562c4

【注意】エクセルWEBSERVICE関数でのアクセス発生件数に注意
https://www.excelspeedup.com/webservice/

英日翻訳 | ExcelAPI
https://excelapi.org/docs/translate/translate_enja/

カスタム関数

Excel でカスタム関数を作成する - Office サポート
https://support.microsoft.com/ja-jp/office/excel-%E3%81%A7%E3%82%AB%E3%82%B9%E3%82%BF%E3%83%A0%E9%96%A2%E6%95%B0%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-2f06c10b-3622-40d6-a1b2-b6748ae8231f

エクセルの行の高さを取得したいと考えています。関数、VBAどち... - Yahoo!知恵袋
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11168661241

画像を読み込む - LoadPicture関数 [ExcelのVBA]
https://www.petitmonte.com/excel/excel_vba_loadpicture.html

写真をサムネイルに変換して取り込む（Shapes.AddPicture）｜VBAサンプル集
https://excel-ubara.com/excelvba5/EXCELVBA232.html

自作関数でExcel作業の幅を「すんごく」効率的にするには | Excel VBAのお勉強
http://www.excel-wing.com/study/tips/functions/1204

自作関数を使うと「ハッピーな業務」になるのか
https://global-wing.com/activity/excel_kansu_jisaku.html

書式設定

Excel のセルの表示形式で [ユーザー定義] に使用できる書式記号について
https://support.microsoft.com/ja-jp/help/883199

【Excel】ユーザー定義をわかりやすく解説｜書式記号の早見表もご紹介 - まいにちdoda - はたらくヒントをお届け
https://mainichi.doda.jp/article/2019/07/23/1677.html

書式設定(日付)

実際の表示	書式のユーザ定義
5月5日	←標準の場合
09/5/5	←「yy/m/d」の場合
09/05/05	←「yy/mm/dd」の場合
09/5/5 火	←「yy/m/d aaa」の場合
09/5/5(火)	←「yy/m/d(aaa)」の場合
09/5/5(火曜日)	←「yy/m/d(aaaa)」の場合
09/5/5 Tue	←「yy/m/d ddd」の場合
09/5/5 Tuesday	←「yy/m/d dddd」の場合
21/5/5	←「ee/m/d」の場合
H21/5/5	←「ge/m/d」の場合
平21/5/5	←「gge/m/d」の場合
平成21/5/5	←「ggge/m/d」の場合

セルのデータを非表示にするには（ユーザー定義で設定） | Excel 2003 | 初心者のためのOffice講座
https://hamachan.info/excel/datahihyouji.html

［;;;］(セミコロンを3つ）を入力

ハイパーリンクの書式を変更したい場合

ホーム＞スタイル＞ハイパーリンクを右クリック＞変更
- セルのスタイルを変更しない場合は、チェックを外す
- セルのスタイルを指定したい場合は、書式設定ボタンから設定を変更する

Excel のショートカットキー - Office サポート
https://support.microsoft.com/ja-jp/office/excel-%e3%81%ae%e3%82%b7%e3%83%a7%e3%83%bc%e3%83%88%e3%82%ab%e3%83%83%e3%83%88-%e3%82%ad%e3%83%bc-1798d9d5-842a-42b8-9c99-9b7213f0040f?ocmsassetid=hp010073848&correlationid=a5206cdb-8ba9-4b9e-a72c-16c1868eb7e3&ui=ja-jp&rs=ja-jp&ad=jp

Excelのよく使うショートカットキーをマスターしよう【Excelファイル付】 - YouTube
https://www.youtube.com/watch?v=wDh3PT4F3gM

【機能】簡単にできる！キー操作でオートフィルターを絞り込む方法 - YouTube
https://youtu.be/mcNAwz65-Co?t=875

フィルターで絞り込みたい値を選択して、App(or Shift+F10)＞E＞V
オートフィルターをAlt+↓で表示し、「C」でフィルターをクリア

オートフィルターをAlt+↓で表示し、「E」で検索ボックスにカーソル

キー	動作
Shift+Space	行全体を選択
Ctrl+Space	列全体を選択
Ctrl+End	データが含まれていてる最後のセルに移動
Alt+PageDown	1画面右にスクロール
Alt+PageUp	1画面左にスクロール
Ctrl+]	アクティブセルを直接参照している数式を含むセルを選択
Ctrl+Shift+]	アクティブセルを直接または間接参照している数式を含むセルを選択
Ctrl+[	選択範囲で数式によって直接参照されている全てのセルを選択
Ctrl+Shift+[	選択範囲で数式によって直接または間接参照されている全てのセルを選択
Ctrl+Shift+2	1つ上のセルの値を、アクティブセルにコピー
Ctrl+7	1つ上のセルの数式を、アクティブセルにコピー
Ctrl+Alt+V	[形式を選択して貼り付け]ダイアログボックスが表示
Ctrl+5	取り消し線の設定と解除を切り替え
Ctrl+9	選択した行を非表示に
Ctrl+0	選択した列を非表示に
Ctrl+Shift+L	オートフィルター
App、E、V	選択したセルの値でフィルター
App、E、C	選択したセルの色でフィルター

列を再表示するショートカットキー Ctrl + Shift + 0 が機能しない場合の対処法
https://excel.nj-clucker.com/ctrl-shift-0-not-working/

セル内の改行（改行コードLF）＝「Alt＋Enter」＝「Ctrl＋J」

「Alt＋Enter」：入力中
「Ctrl＋J」：検索と置換、区切り位置、ユーザー定義（表示形式の設定欄）
Macの場合は、「command + option + return」

マクロ等で「CRLF」が入力されていると、「CR」が抜けてしまう。
「CR」を削除したい場合は、CLEAN関数を使うと削除できる

【機能】おかしいなぁ～なんだかやだなぁ～と思ってよく見たら、そこにね、あるんですよ改行コードが、消したはずの改行コードがぁ - YouTube
https://youtu.be/iCYJSp54X2c?t=845
CLEAN 関数 - Office サポート
https://support.microsoft.com/ja-jp/office/clean-%E9%96%A2%E6%95%B0-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41

CLEAN 関数は、7 ビットの ASCII コードの印刷できない先頭の 32 文字 (値 0 ～ 31) を文字列から削除
Unicode 文字セットでは、印刷できない文字が追加されています (値 127、129、141、143、144、157)

入力モード：未入力のセルにデータを入力するときのモード（中略）アクティブセルが移動します。
参照モード：入力モード中に、数式などでセル参照を入力するときのモード（中略）数式内で参照するセルが移動します。
編集モード：データが入力済みのセルを修正するときのモード（中略）セル内でカーソルが移動します。

セルのモードを切り替えるには、「F2」キー

条件付き書式の数式で矢印キーを押したときにイライラしないために｜クリエアナブキのちょこテク
https://www.crie.co.jp/chokotech/detail/332/

条件付き書式のダイアログ ボックスで数式を作っているときに、「入力」モードだと、方向キーを使ったときに (セルのときと同じように) 「参照」モードになり、求めていないセル参照が追加されます。

タイトルバーの検索ボックス

タイトルバーの検索ボックスを非表示にする: パソコンのツボ　Office　のTIP
http://pcclick.seesaa.net/article/465214838.html

ファイル＞オプション＞全般　既定でMicrosoft Search ボックス を折りたたむ

ピボットテーブルを使ってリストからワークシートを生成する

https://www.youtube.com/watch?v=_h9KHmZIJEY

1.リストを選択
2.ピボットテーブルの作成
3.右ウインドウ＞ピボットテーブルのフィールド リストを作成 フィルターにドラッグ
4.ピボットテーブルツール/分析/ピボットテーブル名/ピボットテーブルのオプション＞レポートフィルターページの表示
5.リストから作成＞OK
6.シートが生成されるので、不要なA1-B1を消去

応答なし

EXCEL2016 フリーズ - マイクロソフトコミュニティ
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_excel-mso_win10-mso_2016/excel2016-%E3%83%95%E3%83%AA%E3%83%BC%E3%82%BA/24db6a1a-cb1e-4447-92f4-4ccab796a80d

私も同様の現象で困っていましたが、偶然にも以下の設定で初回入力時の遅さは解消されました。
　①excelのファイル→オプション→アドイン
　②アドイン画面の下の設定で、’Euro currency tools’をオンにする。
当方のPC環境：windows10（64bit）、excel2016
私の周りの数人も同様の現象で、これで解消されています。
ユーロ通貨に関わるアドインのようですが、自分ではこのような通貨は使用していません。
何故解消したのか理由は分かりません。この現象は根本的にはバグだと思います。
（注）excel立ち上げ時、’EUROTOOL.XLAM’アドインが読み込まれるので少し時間が掛かります。

Excel が応答しない、停止する、フリーズする、動作しなくなる - Excel
https://support.office.com/ja-jp/article/excel-%E3%81%8C%E5%BF%9C%E7%AD%94%E3%81%97%E3%81%AA%E3%81%84%E3%80%81%E5%81%9C%E6%AD%A2%E3%81%99%E3%82%8B%E3%80%81%E3%83%95%E3%83%AA%E3%83%BC%E3%82%BA%E3%81%99%E3%82%8B%E3%80%81%E5%8B%95%E4%BD%9C%E3%81%97%E3%81%AA%E3%81%8F%E3%81%AA%E3%82%8B-37e7d3c9-9e84-40bf-a805-4ca6853a1ff4

Office アプリケーションを修復する - Office サポート
https://support.office.com/ja-jp/article/office-%E3%82%A2%E3%83%97%E3%83%AA%E3%82%B1%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B3%E3%82%92%E4%BF%AE%E5%BE%A9%E3%81%99%E3%82%8B-7821d4b6-7c1d-4205-aa0e-a6b40c5bb88b#ID0EAADAAA=Windows_7%E3%80%81Windows_Vista_%E3%81%AE%E5%A0%B4%E5%90%88

Excel で発生するクラッシュおよび応答が停止する問題をトラブルシューティングする方法
https://support.microsoft.com/ja-jp/help/2758592/how-to-troubleshoot-crashing-and-not-responding-issues-with-excel

保存の時にフリーズ！エクセルが【応答なし】になる原因と対処法は？
http://itojisan.xyz/%E3%83%91%E3%82%BD%E3%82%B3%E3%83%B3%E3%81%AE%E3%83%88%E3%83%A9%E3%83%96%E3%83%AB/%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB%E3%81%8C%E5%BF%9C%E7%AD%94%E3%81%AA%E3%81%97%E3%83%BB%E3%83%95%E3%83%AA%E3%83%BC%E3%82%BA/

Excel でのクラッシュや応答していない問題をトラブルシューティングする方法
https://support.microsoft.com/ja-jp/help/2758592/how-to-troubleshoot-crashing-and-not-responding-issues-with-excel

UTF-8

Excel 2016でCSVがUTF-8でも保存可能: 世の中は不思議なことだらけ
http://snow-white.cocolog-nifty.com/first/2017/03/excel-2016csvut.html

編集後に保存をするわけですが、その時に「上書き保存」を使ってはいけません。
日本語版の Excelの場合、CSV ファイルを上書き保存すると文字コードが UTF-8 から日本語版特有の「ANSI」になってしまいます。

そこで、面倒でも「ファイル」－「名前を付けて保存」で「ファイルの種類」を「CSV （カンマ区切り）(*.csv)」に切り替えてから、
同じファイル名で保存して、上書き保存されるようにしてください。

ExcelでUTF-8のCSVデータを取り込む方法: 小粋空間
http://www.koikikukan.com/archives/2017/03/24-000300.php

BOMつきのUTF-8であれば、ダブルクリックでそのまま開けます。
UTF-8ファイルにBOMを付与するには、Windowsのメモ帳でCSVファイルで開き、そのまま保存すればOKです。
ただし改行コードはCRLFになっている必要があります。

メニュー

更新状況

関連リンク

検索

Google