オーロラさんの勉強帳

SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

【Excel】INDEX関数の使い方 ~超入門:より分かりやすくINDEX関数を勉強する~

INDEX関数とは

INDEX関数では、指定したセル範囲内で行番号、列番号を指定して該当のセルの値を取得することができます。

INDEX関数の書式は以下の通りです。

書式
INDEX(対象範囲,行番号,列番号)

INDEX関数の使い方

簡単な例を使って、INDEX関数の使い方を説明します。
以下のようなセル範囲「A1:C8」の表を使って、「田中」をINDEX関数を使って取得する方法を考えてみましょう。

f:id:auroralights:20210409233838p:plain

最初にINDEX関数の引数である、対象範囲、行番号、列番号を一つ一つ考えます。
f:id:auroralights:20210409235028p:plain
行番号、列番号は対象範囲に対して決めるものなので、まずは対象範囲を決めます。

対象範囲はA1:C8とします。
f:id:auroralights:20210409235243p:plain
f:id:auroralights:20210409234744p:plain


次に行番号と列番号を決めます。
「田中」は対象範囲「A1:C8」の中で5行目、2列目のデータです。
f:id:auroralights:20210409235515p:plain
f:id:auroralights:20210409235421p:plain


任意のセルに「=INDEX(A1:C8,5,2)」と数式を入力すると、セル「A1:C8」内の
5行目・2列目のデータである「田中」を取得することができました。
f:id:auroralights:20210409235701p:plain


以前書いたINDEX関数の記事はこちら
auroralights.jp

【サクラエディタ】改行コードを確認する方法

各OSで使われている改行コードと改行コードの正規表現

各OSで使われている改行コードおよび改行コードの正規表現は以下の通りです。

OS 改行コード 改行コード 正規表現
Unix Line Feed LF \n
Mac(OSX) Line Feed LF \n
Mac(OS9) Carriage Return CR \r
Windows Carriage Return+Line Feed CR+LF \r\n


サクラエディタでの改行コードの表示(見え方)

サクラエディタの行末尾に表示される表示は、改行コードによって異なります。
LFなら「↓」、CRなら「←」、CR+LFなら「↵」と表示されます。

改行コード サクラエディタでの表示
LF
LF
CR
CR+LF

サクラエディタの改行コードの表示
f:id:auroralights:20210403230620p:plain

改行コードの正規表現で改行コードを確認する方法

「Ctrl」+「F」のショートカット、もしくは「検索」メニュー>「検索」もしくは「検索」アイコンから検索画面を表示します。

<検索>
f:id:auroralights:20210403232023p:plain

検索画面の「条件」に正規表現の改行コードを入力、「正規表現」のチェックボックスにチェック、「該当行マーク」を選択します。

f:id:auroralights:20210403233134p:plain
このファイルの最下行までスクロールして、このファイルの改行コードが「LF」であることがわかります。

【Excel】特定の文字列が含まれているかどうかを調べる方法(COUNTIF関数・SEARCH関数・FIND関数)

特定の文字列が含まれているかどうかを調べる4つの方法を紹介します。
今回は以下のようなデータ中でコメント列に特定の文字列「品質不良」が含まれるデータを調べます。
f:id:auroralights:20210328013917p:plain

目次

この記事の目的

  • 特定の文字列が含まれているデータを調べる・抽出する方法を学ぶ
  • Excelのフィルター機能でデータ抽出する方法を学ぶ
  • ExcelのCOUNTIF関数・SEARCH関数・FIND関数で特定の文字列が含まれているデータを調べる方法を学ぶ
  • 複数の方法を学ぶことでデータ分析・抽出や関数の組み合わせなど応用力を付ける

1.フィルターを使って特定の文字列が含まれているデータを抽出する方法

[データ]タブの「フィルター」を選択して、フィルターを設定します。
※「Ctrl」+「Shift」+「L」のショートカットでもフィルターを設定できます。
f:id:auroralights:20210328015227p:plain

「F1」セルの「▼」を選択し、テキストボックスに検索したい文字列「品質不良」を入力し、「OK」を選択します。
f:id:auroralights:20210328015449p:plain

文字列「品質不良」が含まれているデータを抽出することが出来ました。左下のメッセージで12件のレコード中5件のデータが見つかったことも分かります。

1-a.「品質不良」の他に「品質・・・不良・・・」のようなデータをフィルターで抽出する方法

フィルターの「テキストフィルター(F)」>「ユーザー設定フィルター(F)」を順次選択する。
オートフィルターオプションにて、コメント欄に「品質」「不良」を入力、「AND」のラジオボックスを選択、「を含む」をプルダウンからそれぞれ選び、「OK」を選択します。
f:id:auroralights:20210328020545p:plain

「品質」および「不良」の文字列を含むデータを抽出できます。
f:id:auroralights:20210328020810p:plain

2.COUNTIF関数を使って特定の文字列が含まれているデータを抽出する方法

COUNTIF関数で1行ずつ特定の文字列が含まれるかどうかを確認する方法です。

セルG2に「=IF(COUNTIF(F2,"*品質不良*")=1,"〇","")」を数式入力し、セルG2の数式をセルG13までコピーします。
文字列「品質不良」を含むデータは「〇」を返します。
文字列「品質不良」を含むデータの件数をセルG14に「=COUNTIF(G2:G13,"〇")」で表示しています。
f:id:auroralights:20210328022657p:plain

COUNTIF関数とIF関数を以下のように分解して考えると分かりやすいです。
f:id:auroralights:20210328043111p:plain

【参考情報:COUNTIF関数】

COUNTIF関数は指定された範囲に含まれるセルの内、検索条件に一致するセルの個数を返します。

書式
COUNTIF(範囲,検索条件)

2-a.「品質不良」の他に「品質・・・不良・・・」のようなデータをCOUNTIF関数で抽出する方法

セルG2に「=IF(COUNTIF(F2,"*品質*不良*")=1,"〇","")」を数式入力し、セルG2の数式をセルG13までコピーします。
「品質」と「不良」の間にワイルドカード「*」を追加することで、「品質」と「不良」を含むデータに「〇」を返します。
f:id:auroralights:20210328024306p:plain

3.SEARCH関数を使って特定の文字列が含まれているデータを抽出する方法

SEARCH関数で特定の文字列が対象セルの左から何文字目に始まるかを調べることで、対象データ有無を判断する方法です。※特定文字が無ければSEARCH関数ではエラーが返ってきます。

セルG2に「=IF(SEARCH("品質不良",F2,1)>=1,"〇","")」を入力し、セルG13まで数式をコピーする。文字列「品質不良」を含むデータには〇を返します。
f:id:auroralights:20210328030453p:plain

SEARCH関数とIF文を分解して考えると分かりやすいです。
f:id:auroralights:20210328043744p:plain

3-a.「品質不良」の他に「品質・・・不良・・・」のようなデータをSEARCH関数で抽出する方法

「=IF(SEARCH("品質*不良",F2,1)>=1,"〇","")」で「品質」と「不良」の間にワイルドカード「*」を追加し、「品質」と「不良」を含むデータに「〇」を返しています。
f:id:auroralights:20210328030627p:plain

4.FIND関数を使って特定の文字列が含まれているデータを抽出する方法

セルG2に「=IF(FIND("品質不良",F2,1)>=1,"〇","")」を入力し、セルG13まで数式をコピーする。文字列「品質不良」を含むデータには〇を返します。
※数式は「=IF(FIND("品質不良",F2,1)>=1,"〇")」でも良いです。
f:id:auroralights:20210328024910p:plain

「品質不良」を含まないデータに「#VALUE!」のエラーが返を返さないために、IFERRORを追加すると以下のようになります。
f:id:auroralights:20210328025418p:plain

※FIND関数はワイルドカードが使えません。

auroralights.jp

以上です。