小技集

以下のように、メールなどで送られてきた文章（B2:D16）から特定の値を表形式で抽出（F3:G7）する方法について紹介していきます。

簡易的な文章から表形式で抽出

元々の文章が以下の内容の場合、抽出対象（商品名：数量）以外にも不要な文章が含まれています。（赤文字）

〇〇様 お世話になっております。 今月の弊社商品在庫状況を以下の通りご報告いたします。 サウンドビー：5 タイムウォッチ：8 タブレットプロ：15 ビートスタジオ：6 ペンマスター：18 ご確認のほど、よろしくお願いいたします。 何かご不明点や追加のご要望がございましたら、お気軽にお知らせください。

そのため、まずは抽出対象の文章のみをシートに貼り付けて、そこから表形式になるように数式で抽出していきます。

文章を表形式で抽出する場合、抽出するルールを決める必要があります。
今回の場合は、以下のようなルールになります。

・「：」で列を区切る ・「（改行）」で行を区切る

このルール通りに抽出するためには、TEXTSPLIT関数を活用します。
実際に活用した数式は、以下になります。

=TEXTSPLIT(B2,"：",CHAR(10))
// B2：抽出元の文章が入力されたセル
// "："：列の区切り文字
// CHAR(10)：行の区切り文字（改行はCHAR関数を用いて表現）

上記の数式を抽出先の表の先頭セル（E3）に入力することで、以下のように表形式で抽出することができます。

とはいえ、メールなどの文書から特定の文章を毎回抽出するのは面倒です。
そのため、文章を全て貼り付けるだけで、対象の値を表形式で抽出する方法についても紹介していきます。

本文全体から対象のみを表形式で抽出

本文全体が以下のようにパターン化している場合（赤文字）は、簡単な数式を加えるだけで、対象の範囲の抽出までを自動で行うことができます。

〇〇様 お世話になっております。 今月の弊社商品在庫状況を以下の通りご報告いたします。 サウンドビー：5 タイムウォッチ：8 タブレットプロ：15 ビートスタジオ：6 ペンマスター：18 ご確認のほど、よろしくお願いいたします。 何かご不明点や追加のご要望がございましたら、お気軽にお知らせください。

まずは、この本文のままシートに貼り付けて、先ほどの数式で抽出してみます。

=TEXTSPLIT(B2,"：",CHAR(10))

そうすると、以下の赤枠の箇所が不要になります。

抽出された表の上から５行、下から３行が不要になるので、この不要な行を数式で除外します。

表から不要な行を除外する場合、DROP関数を活用すると良いです。
実際に活用した数式は、以下になります。

=DROP(DROP(TEXTSPLIT(B2,"：",CHAR(10)),5),-3)
// DROP(…,-3)：「…」の末尾から3行を除外
// DROP(…,5)：「…」の先頭から5行を除外

上記の数式を抽出先の表の先頭セル（E3）に入力することで、以下のように不要な行を除外した表形式で抽出することができます。

以下のように、特定の項目の、要素の順番を回転させる方法について、２通りで解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

方法１

１つ目は、末尾のデータを切り取りして、先頭に挿入するという方法です。

まずは、末尾のデータを選択します。

次に、緑の枠の上にカーソルを移動させ、Shiftを押しながら先頭までドラッグすることで、簡単に切り取りと挿入を行うことができます。

しかし、この方法の場合、行数が多い表や複数項目を回転させる必要がある場合に、若干面倒になります。
そんな時は、次の方法が便利です。

方法２

２つ目は、並べ替え用の数値が入力された項目を用意し、その列を基準に並べ替えるという方法です。
具体的には以下のように、各行の行番号を常に表示し、末尾のみ「１」と表示されるような項目を数式で用意して、対象の項目のみを並べ替えます。

そのような特殊な項目は、以下の数式を入力することで実現できます。

=IF(ROW()=COUNTA($C$3:$C$17)+2,1,ROW())
// ROW()=COUNTA(…)+2：「数式を入力したセルの行番号」と「データの件数+2」が等しい
// 1：先ほどの条件を満たしたときに「1」を表示
// ROW()：先ほどの条件を満たさない時に「数式を入力したセルの行番号」を表示

この数式を表の末尾までコピーすることで、各行の行番号、末尾のみ「１」と表示させることができます。

最後に、並べ替える対象の範囲（見出しを含む）のみを選択し、フィルターを設定します。

以上で、準備完了です。
番号が入力された列を基準に、フィルターボタンから昇順にすることで、データを回転することができます。

番号以外の項目に罫線を設定すると、表が見やすくなります。

▼サンプルファイル▼

方法２の注意点

フィルターを設定せずに、[データ]タブから並べ替えしてしまうと、以下のように、先頭列も並べ替えられてしまいます。

以下のように、予定表から「次回の予定」と「次回の予定までの日数」をXLOOKUP関数で抽出する方法について紹介していきます。

※上記の「2025/11/17」は基準の日付になります。
※こちらで作成したファイルは、記事の最後にて配布しています。

1. 次回の予定の抽出

まずは、次回の予定を抽出します。
そのために、次回の予定の基準となる日付（本日の日付）をTODAY関数で表示しておきます。

=TODAY()

では、表示した日付を基準に、次回の予定を抽出していきます。

抽出対象の予定は、基準の日付以上で最も近い日付の予定になります。
その内容を、以下のようにXLOOKUP関数で指定します。
※以下の数式では、抽出した予定に「まで」という文字列を加えています。

=XLOOKUP(B2,予定表!A:A,予定表!B:B,"",1)&"まで"
// B2：検索値（日付のセル）
// 予定表!A:A：予定表の日付の列
// 予定表!B:B：予定表の予定の列
// ""：対象の予定が見つからない時に表示する値（空）
// 1：一致モード（完全一致または次に大きい項目）
// &"まで"：抽出された予定に「まで」という文字列を加える

このように抽出することができます。
基準の日付をセルに表示したくない場合は、検索値の「B2」に直接TODAY関数を指定してもよいです。

=XLOOKUP(TODAY(),予定表!A:A,予定表!B:B,"",1)&"まで"

2. 次回の予定までの日数

次に、次回の予定までの日数を抽出します。
しかし、予定表の中には、次回の予定までの日数が表示されていないため、少し計算が必要です。

そのため、まずは、以下のように次回の予定の日付を抽出します。

=XLOOKUP(B2,予定表!A:A,予定表!A:A,"",1)
// 予定表!A:A：検索範囲と戻り範囲を同じにする

また、上記のように、日付ではなくシリアル値として表示します。
日付が表示された場合は、表示形式を「標準」に戻すことでシリアル値になります。

次回の予定までの日数は、抽出されたシリアル値から基準の日付のシリアル値を引いた数になります。
そのため、先ほどの抽出された数値からセルB2の日付を引きます。
※以下の数式では、求めた日数の前後に「残り」と「日」という文字列を加えています。

="残り"&XLOOKUP(B2,予定表!A:A,予定表!A:A,"",1)-B2&"日"
// "残り"&：求めた日数の先頭に「まで」という文字列を加える
// XLOOKUP(…)-B2：次回の予定までの日数
// &"日"：求めた日数の末尾に「日」という文字列を加える

このように、求めることができます。
基準の日付をセルに表示しない場合は、「B2」を引くのではなく直接TODAY関数を引いてもよいです。

="残り"&XLOOKUP(B2,予定表!A:A,予定表!A:A,"",1)-TODAY()&"日"

まとめ

以上の手順で、予定表から「次回の予定」と「次回の予定までの日数」を抽出することができます。

▼サンプルファイル▼

以下のような「円グラフを活用したルーレット」を作成する方法について紹介していきます。

※こちらで開発したファイルは、記事の最後にて配布しています。

1. 準備

以下の円グラフは、上の表（「はずれ」と「あたり」の割合）をもとに作成しています。
また、「▼」は別で作成した図形を重ねています。

上の表の「あたり」の割合は、以下のような数式で求めています。

=1-C3
// 1と「はずれ」の割合の差

そのため、「はずれ」の割合を変更するだけで、「あたり」の割合が自動で設定されます。

2. 実現方法

今回は、円グラフの「グラフの基線位置」を活用して、ルーレットを実現していきます。

円グラフは、「データ系列の書式設定」にある「グラフの基線位置」を変更することで回転させることができます。

こちらの値を、マクロで操作することで実現していきます。

3. コードの記述

以下のコードを記述します。

Option Explicit

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Roulette()
    
    Dim ch As Chart
    Set ch = Worksheets("ルーレット").ChartObjects("グラフ 1").Chart
    
    Dim startN As Long, lastN As Long
    startN = ch.ChartGroups(1).FirstSliceAngle
    lastN = Int((360 + 1) * Rnd) + 360 * 6
    
    Dim n As Long
    n = startN
    Do While n < lastN
        DoEvents
        ch.ChartGroups(1).FirstSliceAngle = n Mod 360
        Sleep 5
        If lastN - n > 360 * 3 Then
            n = n + 10
        Else
            n = n + 10 - Int((n - 360 * 3) / (lastN - 360 * 3) * 10)
        End If
    Loop
    
End Sub

では、コードについて解説していきます。
※複雑なコードのため簡単に解説します。

Option Explicit

変数宣言を強制する際に記述するものです。
変数宣言を省略したい場合は、記述する必要はありません。

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Windows APIにある「Sleep」というプロシージャを使えるようにするための宣言です。

    Dim ch As Chart
    Set ch = Worksheets("ルーレット").ChartObjects("グラフ 1").Chart
    
    Dim startN As Long, lastN As Long
    startN = ch.ChartGroups(1).FirstSliceAngle
    lastN = Int((360 + 1) * Rnd) + 360 * 6

対象のグラフを変数「ch」に割り当てて、そのグラフの基線位置を変数「startN」に格納しています。
変数「lastN」には、６周分の角度に、ランダムな角度を加えた角度を格納しています。
変数「lastN」が、回転を止める位置になります。

    Dim n As Long
    n = startN
    Do While n < lastN
        '省略
    Loop

変数「n」に変数「startN」の値を格納し、その変数「n」の値が変数「lastN」の値より小さい間、「Do」から「Loop」の中を繰り返し実行します。

        DoEvents
        ch.ChartGroups(1).FirstSliceAngle = n Mod 360
        Sleep 5
        If lastN - n > 360 * 3 Then
            n = n + 10
        Else
            n = n + 10 - Int((n - 360 * 3) / (lastN - 360 * 3) * 10)
        End If

繰り返しの中では、変数「n」の値を徐々に増やしています。
処理の流れは、以下になります。

１．変数「n」の値（角度）をグラフの基線位置に反映（０～３５９の間になるように３６０で割った余りを活用） ２．５ミリ秒（０．００５秒）停止

この次が少しだけややこしくなります。
ルーレットでは、回転が徐々に減速する演出にするために、以下のような処理にしています。

残りの回転角度が３周より多い場合は、変数「n」に１０を加算 そうでない場合は、徐々に加算する数を減らす

4. ボタンの作成

記述したプロシージャ（Roulette）を割り当てたボタンを作成します。
こちらでは、立体的な見た目の図形（四角形：角度付き）を挿入して、その図形に割り当てています。

5. 完成

以上の手順で完成です。
作成したボタンをクリックすることで、円グラフが回転します。

▼サンプルファイル▼

以下のように、FILTER関数などを用いた数式でデータを抽出し、その抽出された表のサイズに合わせて罫線を設定してしまうと、抽出されるデータの件数が変わった時に罫線が合わなくなってしまいます。

そこで今回は、数式でデータを抽出する際に、その抽出されたデータの件数に合わせて罫線を自動で設定する方法について紹介していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. データの間の罫線を点線にする

こちらでは、以下のように、データの間の罫線を点線に、末尾の罫線を実線にしていきます。

まずは、データの間の罫線を点線していきます。

データの増減に合わせて罫線などの書式を自動で変更するには、「条件付き書式」を活用します。

はじめに、見出し以外の罫線が設定されていない状態にし、今後データが追加されることを考慮したデータの範囲のみを選択します。

次に、[ホーム]タブから[条件付き書式]の[新しいルール]を選択します。

表示された以下の設定画面で、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にします。

次に、表示されたテキストボックスに、点線の罫線を設定する条件式を入力します。

今回の表では、「No」の値の有無で判断していきます。
点線の罫線を設定するときとは、「No」が表示されているときになります。
そのため、以下の条件式を入力します。

=$A5<>""
// A5は選択基準のセル
// 他の選択範囲に反映する際に、A列を固定するため、Aに「$」を指定

次に、[書式]を選択し、[罫線]タブから下側の罫線を点線にします。

上記の内容で確定することで、データが存在する行の下側に点線の罫線を設定することができます。

2. データの末尾の罫線を実線にする

次は、データの末尾の罫線を実線にしていきます。

初めに、先ほどと同じ範囲を選択し、条件付き書式の設定画面を開きます。

次は、罫線を実線にする条件式を入力します。

こちらも先ほどと同様に、「No」の値の有無で判断していきます。
実線の罫線を設定するときとは、「No」が表示されていて、かつ、その次の行の「No」が表示されていないときになります。
そのため、以下の条件式を入力します。

=AND($A5<>"",$A6="")
// A5は選択基準のセル
// A6は選択基準の下のセル
// 他の選択範囲に反映する際に、A列を固定するため、Aに「$」を指定

次に、[書式]を選択し、[罫線]タブから下側の罫線を実線にします。

上記の内容で確定することで、表の末尾に実線の罫線を設定することができます。

3. 完成

以上の内容で完成です。
数式によって抽出されたデータの増減に合わせて、罫線が自動で設定されます。

▼サンプルファイル▼

以下のように、「表示切替」ボタンを押すことで、複数あるシートから今月のシートのみを表示することができ、再度ボタンを押すことで、すべてのシートを表示することができる仕組みの実現方法について紹介します。

※こちらで開発したファイルは、記事の最後にて配布しています。

1. 開発準備

今回は、以下のように「1月」から「12月」という名前のシートが含まれるブックに対して実現していきます。

また、「設定」シートの「表示切替」ボタンを配置する予定のセルに、「TRUE」もしくは「FALSE」と入力します。

この値は、現在のシートの状態を表しています。
「TRUE」ならば、すべてのシートが表示されている状態、「FALSE」ならば、一部のシートが非表示の状態になります。
※仕組みを実装する前は、シートの状態に関係なく「TRUE」もしくは「FALSE」の値を入力しておきます。

次に、[開発]タブを選択し、[マクロ]を選択します。

以下の画面にて、開発する機能の名前を入力し、[作成]を選択します。
こちらでは、「ToggleSheetView」と入力しています。

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

2. コードの記述

以下のコードを記述します。

Sub ToggleSheetView()
    
    Dim ws As Worksheet
    Set ws = Worksheets("設定")
    
    Dim m As Long
    m = Month(Date)
    
    Dim ckRng As Range
    Set ckRng = ws.Range("B3")
    ckRng.Value = Not ckRng.Value
    
    Dim i As Long
    For i = 1 To 12
        On Error Resume Next
        With Worksheets(i & "月")
            If i = m Or ckRng.Value Then
                .Visible = xlSheetVisible
            Else
                .Visible = xlSheetHidden
            End If
        End With
        On Error GoTo 0
    Next i
    
    ws.Activate
    
End Sub

では、コードについて解説していきます。

    Dim ws As Worksheet
    Set ws = Worksheets("設定")
    
    Dim m As Long
    m = Month(Date)

シート情報を割り当てる用の変数（ws）を用意し、ボタンを配置する予定の「設定」シートを割り当てています。
次に、現在の月を格納する用の変数（m）を用意し、現在の月を格納しています。

    Dim ckRng As Range
    Set ckRng = ws.Range("B3")
    ckRng.Value = Not ckRng.Value

シートの表示非表示の状態を表す値が入力されたセルの情報を割り当てる用の変数（ckRng）を用意し、対象のセル（「設定」シート（ws）のセルB3）を割り当てています。
そして、その対象のセルの現在の値を、「TRUEならばFALSE」、「FALSEならばTRUE」と反転させています。

    Dim i As Long
    For i = 1 To 12
        On Error Resume Next
        With Worksheets(i & "月")
            '省略
        End With
        On Error GoTo 0
    Next i

繰り返し処理で使う用の変数（i）を用意し、Forで「1」から「12」まで１つずつ繰り返しています。
「For～Next」の中では、「1月」から「12月」のシートを１つずつ繰り返しています。

「Worksheets(i & “月”)」と直接シート名で指定しているため、対象のシート名のシートが存在しないときにエラーになってしまいます。
その対策として、「On Error Resume Next」と「On Error GoTo 0」を記述しています。
「On Error Resume Next」で、それ以降でエラーになった際に、エラーを飛ばして処理を実行することができます。
「On Error GoTo 0」で、「On Error Resume Next」の設定を解除しています。

            If i = m Or ckRng.Value Then
                .Visible = xlSheetVisible
            Else
                .Visible = xlSheetHidden
            End If

Withの中で、「繰り返された対象の月と実行したときの月が同じ場合」、もしくは、「シートの表示非表示の状態を表すセルの値がTRUEの場合」は、繰り返された対象の月のシートを表示しています。
それ以外の場合は、繰り返された対象の月のシートを非表示にしています。

※「With」から「End With」で囲まれている範囲内は、「With」で指定した情報を省略して「.」から記述することができます。

    ws.Activate

シートの表示非表示を切り替える処理で、選択されているシートが変わってしまうことがあるため、最後に「設定」シートを選択しています。

3. ボタンの作成

コードが完成したら、実行用のボタンを用意します。
ボタンは、[開発]タブの中の[挿入]から作成することができます。

4. 完成

以上の手順で完成です。
「設定」シートに配置した「表示切替」ボタンを押すことで、シートの表示非表示を切り替えることができます。

▼サンプルファイル▼