メンバー

以下のグラフのように、日付軸のグラフを作成した際に、表には存在していない日付まで表示されることがあります。

日付軸は基本、時系列であり、空白があるとは考えにくいため、自動的に間の日付も表示されてしまいます。
ただ、間に表示された日付が不要に感じることもあるかと思います。

間の日付を非表示にする方法について解説していきます。

間の日付を非表示にする

まず初めに、日付軸をダブルクリック、もしくは、右クリックメニューから「軸の書式設定」を開きます。

「軸の書式設定」より[軸のオプション]の中の[軸の種類]を[テキスト軸]にします。

この設定により、日付として表示されていた軸の値が、文字列として扱われるようになります。
そのため、時系列の情報を持たなくなるので、以下のように表に存在するデータのみが表示されるようになります。

補足

日付かテキストによって表示される順番が異なる場合がございます。

日付として扱われる場合は、時系列があるため、時系列を考慮した順番に表示されます。
それに対し、テキストの場合は、時系列の情報を持たないため、表の順番に表示されます。

こちらでは、最も頻繁に出現する値の抽出方法について、以下の３パターンで解説していきます。

１.数値の場合
２.文字の場合（1文字）
３.文字の場合（2文字以上）

最頻値を求める関数は以下の２つになります。

=MODE.SNGL(数値1, [数値2], …)
// 選択した値の中で最も出現頻度の高い値を抽出
// 複数件見つかった場合は、範囲の先頭にある数値が優先される

=MODE.MULT(数値1, [数値2], …)
// 選択した値の中で最も出現頻度の高い値を抽出
// 複数件見つかった場合は、配列で返す

①数値の場合

数値の場合は、関数そのものの機能になるため、以下のように抽出できます。

=MODE.SNGL(B2:B10)

複数件見つかった場合は、以下のようになります。

=MODE.SNGL(H2:H9)
=MODE.MULT(H2:H9)

MODE.MULT関数に関しては、スピルに対応していない場合は配列数式にする必要があります。
配列数式は、表示される可能性がある件数分の範囲を選択した後に数式を入力し、確定時はEnterではなく「Ctrl+Shift+Enter」で行います。

数式バーを確認すると、入力した数式に「{}」が追加されて表示されます。
これが配列数式になります。

配列数式の場合は、表示させる件数が選択したセルの数より少ない場合、エラー（#N/A）が表示されます。
また、配列数式を削除する場合は、対象の範囲全てを選択してからDeleteキーを押します。
入力した単体のセルのみでは削除できないため、削除する際は注意する必要があります。

こちらの内容以降に関しては、スピルに対応したExcel向けに解説していきます。
対応していない場合は、必要に応じて配列数式を活用することで同じことが実現できます。

①補足

複数列を指定している場合は、以下の順番で抽出対象が優先されます。

②文字の場合（１文字）

MODE.SNGL関数とMODE.MULT関数は数値のみに対応しています。
そのため、文字で求めたい場合は、文字を数値にして行う必要があります。

文字が１文字の場合は、CODE関数とCHAR関数が使えます。

=CODE(文字列)
// 文字列の先頭の文字の文字コードを返す

=CHAR(数値)
// 文字コード(数値)に対応する文字を返す

CODE関数で、対象の範囲を文字コードの配列にし、その配列に対して最頻値を求め、その結果をCHAR関数により文字に戻すことで抽出できます。

=CHAR(MODE.SNGL(CODE(B2:B10)))
=CHAR(MODE.MULT(CODE(B2:B10)))

③文字の場合（２文字以上）

最後に複数の文字の場合です。
先ほどのCODE関数やCHAR関数は１文字の場合のみに対応しています。
複数の文字の場合は、同じ文字を同じ数字に置き換える必要があります。

そんな時は、MATCH関数が使えます。

=MATCH(検査値, 検査範囲, [照合の種類])
// [照合の種類]：(1)以下、(0)完全一致、(-1)以上
// 指定した検査範囲の中から検査値が何番目にあるのか、その数値を返す
// 複数件、検査値が存在する場合は、最初に見つかった時の数値を返す

MTACH関数を活用して、それぞれの文字の番号を求めると以下のようになります。

それぞれの文字に対して、重複のない数値が割り当てられているかと思います。
ちなみに、このB2からB8の検査値に関して配列で指定することで、一括で取得することができます。

=MATCH(B2:B8,B2:B8,0)

このMATCH関数で取得した値に対して、最頻値を求める関数を組み合わせます。
以下のようにすることで、出現頻度の高い文字に割り当てられた数値を取得することができます。

=MODE.SNGL(MATCH(B2:B8,B2:B8,0))
=MODE.MULT(MATCH(B2:B8,B2:B8,0))

この番号のままですと、何の文字なのかが確認しづらいため、文字に戻す必要があります。
MATCH関数で取得された数値は、対象範囲の先頭から最初に見つかった対象の文字の先頭からの位置になります。

その位置を指定することで、その位置にある文字を取得することができる関数があります。
それはINDEX関数です。

=INDEX(配列 , 番号)
※他の引数もありますが、こちらでは今回使用する引数のみを説明します。
// 配列の先頭から指定した番号の位置にある値を返す

INDEX関数の使用例は以下になります。

=INDEX(B2:B8,1)

INDEX関数を組み合わせると、以下のように複数の文字であっても出現頻度の高い値を抽出することができます。

=INDEX(B2:B8,MODE.SNGL(MATCH(B2:B8,B2:B8,0)))
=INDEX(B2:B8,MODE.MULT(MATCH(B2:B8,B2:B8,0)))

セルB2:C2に、以下のような「ON・OFFボタン」を開発してみました。

セルB2:C2のどちらかを選択すると、ONとOFFが切り替わるようになっています。
「ONの時にどうなるのか」といった機能は割り当てていませんが、上記の場合、「セルB2にONという文字が入力されている場合」という条件を使うことで、好みの機能を割り当てることはできます。

では、ON・OFFボタンの開発方法について解説していきます。

特定のシート上の特定のセルを操作した際に自動で処理を実行するには、シートモジュール上にコードを記述する必要があります。

＞５つのモジュールの違いと使い分け

まず初めに、[開発]タブの中の[Visual Basic]を選択します。

開かれたエディター画面にて、[プロジェクト]から実装したいシートモジュールを選択します。
※「Option Explicit」は設定によっては表示されていないかと思いますが、特に問題ございません。
　プロジェクトウィンドウが表示されていない場合は、[表示]タブから表示できます。

次に、特定のセルを選択した際に、自動で処理を実行するためのイベントプロシージャを用意します。
下記のリストから[Worksheet]を選択します。

[Worksheet]を選択すると、以下のようなプロシージャが表示されるかと思います。
※他のプロシージャが表示される場合は、右側のリストより[SelectionChange]を選択してください。

このプロシージャが、該当するシート上のセルの選択が変更された際に自動で処理が実行されるイベントプロシージャになります。
実行時に選択されていたセルの情報は、引数のTargetに渡されます。

その情報を活用して開発していきます。

以下のように、記述します。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    '1つのセルのみが選択されている場合
    If Target.CountLarge = 1 Then
        
        'セルB2もしくはC2が選択された場合
        If Target.Row = 2 And _
            (Target.Column = 2 Or Target.Column = 3) Then
            
            'セルB2の背景色が白色の場合
            If Range("B2").Interior.Color = RGB(255, 255, 255) Then
                Range("B2").Value = "ON"
                Range("C2").ClearContents
                Range("B2").Interior.Color = RGB(0, 255, 0)
                Range("C2").Interior.ColorIndex = 0
            Else
                Range("B2").ClearContents
                Range("C2").Value = "OFF"
                Range("B2").Interior.ColorIndex = 0
                Range("C2").Interior.Color = RGB(255, 0, 0)
            End If
            
        End If
    End If
    
End Sub

上記の内容は、以下のような処理になります。

1.選択されているセルが1つのみの場合に、次の処理を実行
2.選択されているセルの行番号が2で、列番号が2もしくは3の場合に、次の処理を実行
3.セルB2の背景色が無色(白色)の場合に、3-1の処理を実行、異なる場合は、3-2の処理を実行
3-1.セルB2の値を"ON"にし、セルC2の値を削除し、セルB2の背景色を緑色にし、セルC2の背景色を白色にする
3-2.セルB2の値を削除し、セルC2の値を"OFF"にし、セルB2の背景色を白色にし、セルC2の背景色を赤色にする

※無色と白色は異なります。無色にしたい場合は、以下のように記述します。

Range("XX").Interior.ColorIndex = 0

上記のコードを記述することで、ON・OFFを切り替えられるボタンを実装することができます。

▼サンプルファイル▼

以下の表のように、未登録期間に関しても数式を設定している場合があるかと思います。
その場合、以下の画像の場合は、2023年7月以降のデータがないにも関わらず「0」と表示されてしまっています。
※E列の年月は、各月の1日の日付が入力されています。表示形式により年と月のみを表示しています。

今回は、表（B列～C列）に登録されている最新の日付を基準に、その日付よりも後のデータに関しては非表示にする方法について解説していきます。
こちらでは条件付き書式を活用して、見た目のみ非表示にする方法について解説しています。
そのため、集計上は「0」として扱われます。

まずは、期間外を非表示にしたい範囲を上から全体的に選択し、[ホーム]タブの中の[条件付き書式]から[新しいルール]を選択します。

表示された[書式ルールの編集]画面にて、[数式を使用して、…]を選択します。
選択後、以下の数式を入力します。

=$E3>MAX($B:$B)

MAX関数でB列の最大値（最新の日付）を取得しています。
日付はシリアル値という数値で管理されているため、MAX関数により簡単に最新の日付を求めることができます。

＞シリアル値とは

その取得した日付が「該当するE列の値より小さい場合」という条件式になります。

=$E3>MAX($B:$B)

E3の3は、選択した基準の行番号になります。
他の行に関しては、E3を基準に相対参照で式が設定されます。
相対参照されたくない列や行に関しては、$を加え絶対参照にします。
今回の場合は、1列のみしか適用する範囲がないため、列を絶対参照にしなくても問題ございません。

条件式の設定ができましたら、条件を満たしている場合の書式を設定します。
書式は、[表示形式]の[ユーザー定義]にて「;;;」と入力します。
※「;;;」は入力値を非表示にするという設定になります。

＞「;;;」で非表示になる理由

上記の設定をすることで、以下のように期間外を非表示にすることができます。

以下に「会員推移」を表したグラフがあります。
このグラフは、各月の新規会員数と退会会員数、累計会員数を表しています。
会員数として2000人を目指しているとした場合、このグラフからも2000人という目標値が分かるようにしていきます。

※上記の表の累計は以下のような数式で求められています。（直接入力した値でも問題ございません。）

目標値をグラフ上に表示させるには、組み合わせグラフを活用するのが良いです。
まずは、表に目標値の項目を追加します。

目標値は折れ線グラフで表現します。
元々の棒グラフに折れ線グラフを組み合わせたグラフを作成していきます。

[挿入]タブから[組み合わせ]の中の[ユーザー設定の複合グラフを作成する]を選択します。

以下の画面が表示されましたら、[目標]のみ[折れ線]を指定し、他の項目は[集合縦棒]を指定します。

上記の設定をして確定すると、以下のように目標値を表示することができます。

補足

上記の目標のグラフを確認すると、左右の長さが若干足りていません。
折れ線グラフを左右限界まで表示させる方法について解説していきます。

目標のグラフ上で右クリックし、[近似曲線の追加]を選択します。

次に、[近似曲線の書式設定]より[近似曲線のオプション]内の[予測]にある[前方(後方)補外]に関して、[0.5]と指定します。

これで、目標の左右に0.5分の予測値を表示させることができます。
後は、点線を実線に変更し、線の太さを調整します。

上記の設定をすることで、以下のように左右限界まで目標値の横線を表示させることができます。

Excelのマクロ開発に使われるプログラミング言語には、VBAとOfficeスクリプトがあります。
Officeスクリプトに関しては、2021年5月27日に一般公開された比較的新しい言語になります。

デスクトップ版のExcelでは、VBAでマクロ開発ができるのですが、Web版ではVBAを使うことができません。
そこでWeb版のExcelのマクロ開発用として登場したのが、Officeスクリプトになります。

現時点では、デスクトップ版のExcelでもOfficeスクリプトを使用することができるのですが、スクリプトファイルはExcelファイルとは別で、OneDrive上で管理する必要があります。
そのため、ネットに繋がっていないパソコンのExcelでは実行することができません。（2023年6月22日時点）

Officeスクリプトが使用できるライセンスは現時点では以下になります。

・Microsoft 365 Apps for business
・Microsoft 365 Business Standard
・Microsoft 365 Apps for enterprise
・Office 365 ProPlus for Devices
・Office 365 Enterprise E3
・Office 365 Enterprise E5
・Office 365 A3
・Office 365 A5

365でも家族向けのFamilyやPersonalではなく、一般法人向けである必要があるので、新規で登録する場合は注意する必要があります。

OfficeスクリプトはWeb業界では一般的なプログラミング言語「JavaScript」をベースとしています。
もう少し細かく解説すると、「JavaScript」をベースにMicrosoft社によって２０１４年頃に発表された「TypeScript」をベースとした言語になっています。

VBAを理解している方の場合は、比較的、簡単にOfficeスクリプトを理解できるかと思います。

こちらでは、そんなVBAとOfficeスクリプトのコードの違いを簡単に比較していきたいと思います。

今回、VBAとOfficeスクリプトで作成する内容は以下になります。

---

・A列に〇が入力されている行を削除する

---

VBAで開発した内容とOfficeスクリプトで開発した内容は以下になります。
アルゴリズム（仕組み）をVBAに寄せてOfficeスクリプトで開発しています。

▼VBA▼

Sub 対象行の削除()
    
    Dim sh As Worksheet
    Set sh = ActiveWorkbook.ActiveSheet
    
    Dim lastRow As Integer
    lastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
    
    Dim rng As Variant
    rng = sh.Range(sh.Cells(1, 1), sh.Cells(lastRow, 1)).Value
    
    Dim i As Long
    
    For i = lastRow To 3 Step -1
        If rng(i, 1) <> "" Then
            sh.Range(sh.Cells(i, 1), sh.Cells(i, 3)).Delete Shift:=xlShiftUp
        End If
    Next i
    
End Sub

▼Officeスクリプト▼

function main(workbook: ExcelScript.Workbook) {
    // Your code here

    const sh = workbook.getActiveWorksheet();
    var maxRow = sh.getRange('A:A').getRowCount();
    var lastRow = sh.getCell(maxRow - 1,0).getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
    var rng = sh.getRangeByIndexes(0,0,lastRow,1).getValues();
    
    for (var i = lastRow - 1; i >= 2; i--){
        if (String(rng[i]) != ''){
            sh.getRangeByIndexes(i,0,1,3).delete(ExcelScript.DeleteShiftDirection.up);
        }
    }
}

処理の流れは以下になります。

1.アクティブシートの情報を取得(ws)
2.A列に〇が入力されている最終行の行番号を取得(lastRow)
→シート上のA列の最終行を選択し、「Ctrl+↑」で移動して選択した位置にあるセルの行番号を取得
3.対象の表の範囲を取得(rng)
4.繰り返し文(for)で、手順2で取得した最終行から表の先頭行までを繰り返し、A列に値が入力されている場合は、対象行(A列からC列)を削除して上に詰める

※あくまで一例のため、他の方法でも実現は可能になります。

比較すると、書き方は大きく異なってはいるのですが、全体の構造は似ているかと思います。
Officeスクリプトに挑戦してみたい方は、基礎コンテンツを用意しているため、ご確認ください。

＞Officeスクリプトの基礎

Excelには過去のデータから未来のデータを予測したグラフを自動で作成する機能があります。
例えば、以下の表を元に未来を予測してみます。

上記の表は、とあるサービスの各月ごとの登録ユーザー数をまとめたものになります。
このような推移で人数が増えていると考えた時の、３か月先（2024/3/1）までのデータを予測したグラフを作成していきます。

まずは、グラフの中を選択し、[データ]タブの中の[予測]の[予測シート]を選択します。

次の画面で、好みのグラフと、予測したい期間の最終日を指定します。

※オプションを展開すると、細かな設定をすることができます。

設定内容の確認ができましたら、[作成]を選択することで、以下のような予測シート（新しいシート）が自動で追加されます。

ちなみに、この未来予測には、FORECAST.ETS関数とFORECAST.ETS.CONFINT関数が使用されています。

この関数により、最新データに比重をかけて移動平均を求めています。（指数平滑法）

FORECAST.ETS関数：指数平滑法にて予測値を求める
FORECAST.ETS.CONFINT関数：指数平滑法にて求めた予測値の信頼区間を求める
※この関数の使い方に関してはこちらでは省きます。

非表示の行もしくは列を含む範囲を選択してコピーした場合、貼り付け時には、非表示のセルに関しても貼り付けられてしまいます。

実は、ちょっとしたショートカットを使うことで、選択範囲から非表示のセルを除外することができます。

それは「Alt + ;(セミコロン)」です。

選択後に「Alt + ;」を押すと、以下のように非表示のセルを除外して選択することができます。

後は、この状態でコピーして貼り付けるだけです。

補足

フィルター機能により絞り込みをしている時は、コピー時に自動で非表示セルが除外されます。

ただ、絞り込みをしていない時は、非表示のセルも選択されてしまうので、注意する必要があります。

複数の表を縦方向に結合して並べる関数にVSTACK関数があります。
※今回は割愛しますが、横方向に結合するHSTACK関数というものもあります。

VSTACK関数の使い方

使用方法や活用例は以下になります。

=VSTACK(配列1, [配列2], [配列3], …)
// 指定した配列を縦方向に結合した配列を返す

・活用例

=VSTACK(E2:F7,H2:I6)

この関数に関して、上記の活用例のように個々の表を指定すると、個々の表にデータが追加された場合、自動で拡張することができません。

では、どのような数式にすると自動で拡張できるのでしょうか？
自動で拡張させる方法について、２パターンで解説していきます。

①テーブルの活用

１つ目は結合元の表をテーブルにするという方法です。
対象の範囲を選択し[挿入]タブから[テーブル]を選択し、テーブルを作成します。
必要に応じてテーブル名を分かりやすい名前に変更します。

後は、テーブルの項目を含む全ての行を指定することで、自動で範囲を拡張することができます。

=VSTACK(売上1[#すべて],売上2[#すべて])
// 「テーブル名[#すべて]」で項目名を含む全ての行を選択することができます

②INDIRECT関数の活用

２つ目の方法は、INDIRECT関数を活用するという方法です。
INDIRECT関数は文字列のアドレスを直接参照することができます。

=INDIRECT(参照文字列)
// 指定した文字列のアドレス（テーブル名やセルの名前なども含む）を直接参照する

INDIRECT関数を使用して、以下の数式の「7」や「6」が自動で変わるようにしていきます。

=VSTACK(E2:F7,H2:I6)

この「7」や「6」は各表のデータの数になります。
データの数に関してはCOUNTA関数を活用することで求めることができます。

=COUNTA(値1, [値2], [値3], …)
// 指定した範囲の中で値が入力されているセルの数を返す

必ず売り上げの項目が入力されているとした場合、売り上げの列の値が入力されているセルの数を数えることで、自動で範囲を拡張することができます。

=VSTACK(INDIRECT("E2:F"&(COUNTA(F:F)+1)),INDIRECT("H2:I"&(COUNTA(I:I)+1)))

補足

VSTACK関数により結合された表に対し、項目名に関して色を設定したり、データを含む行に罫線を引いたりしたい場合は、条件付き書式が活用できます。

今回の表の場合は、A列は日付になり、項目名のみが文字列になります。
そのため、A列が文字列の場合は、背景色を青色にするという設定で、項目名に色を自動で設定することができます。

=ISTEXT($A2)
// B列に関してもA列を見て判断する必要があるため、A列のみ絶対参照にする

罫線に関しては、A列を基準に考えると、A列に値が入力されている場合は、行全体に罫線を引くという設定ができます。

=$A2<>""
// A列が空白でない場合という条件式

設定イメージは以下のようになります。

※セルA2を基準に選択した場合のルールになります。

以下のようなフォームに対して、入力漏れの項目がある場合に、入力漏れがあると分かりやすくする方法について解説していきます。

手順は大きく分けて３つあります。

手順１：値が入力されていないセルを自動で色付けする

対象項目を全て選択し（離れているセルの場合はCtrlキーを押しながら選択し）、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

[新しい書式ルール]の設定画面にて、空白の場合は背景色を変更する設定を行います。

この設定によって、対象項目が空白の場合は色付けされるため、入力漏れに気づきやすくなります。

手順２：対象項目に入力漏れがある場合は、「入力していない項目があります」と表示させる

セルに値が入力されているかどうかはCOUNTA関数を使うことで確認できます。
COUNTA関数は、指定した範囲に値が入力されているセルの数を返します。
そのため、対象項目をCOUNTA関数で指定し、その返ってきた数が対象項目の数と一致していない場合に「入力していない項目があります」と表示する数式を作成します。

=IF(COUNTA(C3:C4,C6:G7,C9)<>5,"入力していない項目があります","")
// 対象項目が5箇所あるため、IF文の条件式に「COUNTA関数の取得結果が5である場合」という式を設定して比較しています

※書式は赤色の太字にしています。

これで、一目で入力漏れの有無を確認することができます。

手順３：先ほど入力した数式などが誤って消されないように保護をする

手順２までで十分に入力チェックとしては実現できています。
ただ、この状態のままでは、誤って数式が消されてしまったりといったリスクがあります。

その対策として、入力できる項目以外のセルの操作を禁止していきます。
対象項目を全て選択し、右クリックから「セルの書式設定」を開きます（「Ctrl+1」でも開けます）。

「セルの書式設定」の[保護]タブの中の[ロック]のチェックを外します。

次に、[校閲]タブの中の[シートの保護]を設定することで、[ロック]のチェックを外したセル以外の操作を禁止することができます。
※パスワードや細かな保護の設定は必要に応じて変更してください。

以上の３つの手順を行うことで、入力チェックを実現することができます。

セル内で改行して複数行の文字を入力すると、以下のように右端が揃わないことがあります。

全角文字と半角文字は混ざっている文字列の場合は、半角文字が行内に収まらない時に改行されてしまいます。

そのため、最初の画像のように全角文字から半角文字に変わる時に自動で改行されてしまっています。
文字列内に金額などの短い数値などを入力している場合は、右端が微妙に揃っていなかったりと違和感を感じるかと思います。

改行の位置を意図的に変更する場合は、「Alt+Enter」などの手動の改行になりますが、自動で改行を行って右端を揃えるには、設定を変更する必要があります。

対象のセルを選択し、右クリックから「セルの書式設定」を開きます（「Ctrl+1」でも開けます）。
「セルの書式設定」にて、[配置]タブの中にある[横位置]を[両端揃え]にすると、右端を揃えることができます。

全角文字に長文の半角文字が混ざっている場合などは、以下のように違和感を感じるバランスになることがあるため注意して活用する必要があります。