メンバー

2023/06/12

【Excel】図形の既定の書式を好みの書式にする

2023年6月12日2024年10月24日it-yobi

図形を挿入すると、デフォルトの設定では以下のような書式になるかと思います。

ただ、この図形の書式のまま使用することは比較的少ないかと思います。
実は、このデフォルトの書式は自由に変更することができます。

まず初めに、図形に好みの書式を適用します。

設定できましたら、右クリックし、「既定の図形に設定」を選択します。

この設定を１度行うだけで、この対象のExcelファイルに関しては、図形の挿入時の書式が設定した書式になります。
設定した図形以外の種類の図形でも、この書式が初期設定になります。
※図形の色だけではなく、図形内の文字のフォントや色などの書式も規定に設定されます。

それぞれの資料に必要な図形の書式を設定したExcelファイルを、それぞれのテンプレートとして保存して、ファイルをコピーして活用するのも良いかと思います。

2023/06/09

【Excel】表にある日付のみグラフに表示

2023年6月9日2024年10月24日it-yobi

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

今回は、このグラフに関して、表の中にない日付を表示させない方法について解説していきます。

グラフの作成

まずは、グラフを作成します。
こちらでは棒グラフを用いて解説していきます。

作成できましたら、軸をダブルクリックし（もしくは右クリックから）「軸の書式設定」を開きます。

「軸の書式設定」の中の「軸のオプション」-「軸の種類」に関して、「データを基準に自動的に選択する」が選択されているかと思います。
この設定により、表の中の日付のセル情報を元に自動的に「時系列で隙間のない連続的な日付」になってしまっています。

ここを「テキスト軸」にすることで、日付ではなく文字情報として扱うことができます。
文字情報の場合は「時系列」という概念がないため、表に存在している値のみを順番に表示することができます。

補足

〇月〇日という表示を〇/〇などと変更したい場合は、「軸の書式設定」より表示形式を設定することができます。
「表示形式コード」に設定したい形式を入力し、「追加」を選択することで表示形式を設定できます。

2023/06/07

【Excel】勤務日数を瞬時に求める

2023年6月7日2024年10月24日it-yobi

休日を考慮した勤務日数を瞬時に求める方法について解説しています。
※使用したExcelファイルは記事の最後にて配布しています。

以下の表に対して、勤務日数を数式で求めていきます。
年月に関しては各月の１日の日付が入力されています。

今回使用する関数は以下の２つになります。

・NETWORKDAYS関数
・NETWORKDAYS.INTL関数

NETWORKDAYS関数は、定休日が土日で固定です。
それに対し、NETWORKDAYS.INTL関数は、定休日を自由に設定できます。

定休日が土日の場合

まずは定休日が土日の場合で求めていきたいと思います。
また定休日の他に以下の「休日一覧」シートを活用して、不規則な休日にも対応したいと思います。

土日で固定の場合は、NETWORKDAYS関数を活用します。
※NETWORKDAYS.INTL関数でも実現できます。

=NETWORKDAYS(開始日, 終了日, [祭日])
// 指定した開始日と終了日の間の勤務日数を返す

今回は、終了日に関して、該当月の月末日を指定したいと思います。
そのため、月末日を求めるEOMONTH関数を組み合わせます。

=EOMONTH(開始日, 月)
// 開始日から指定した「月」か月後の月の月末日を返す

活用例は以下になります。

=NETWORKDAYS(B4,EOMONTH(B4,0),休日一覧!$B$3:$B$19)

このように求めることができました。

定休日が土日以外の場合

次は、定休日が土日以外の場合で求めていきます。
土日以外の場合は、NETWORKDAYS.INTL関数を活用します。

=NETWORKDAYS.INTL(開始日, 終了日, [週末], [祭日])
// NETWORKDAYS関数とは異なり[週末]が指定できる

週末に関しては、以下の候補から指定します。

候補に希望の曜日構成がない場合は、「1」と「0」を並べて自由に指定することもできます。

月・水→”1010000″
月・水・金→”1010100″
水・日→”0010001″
※「1」と「0」で月から日を順番に指定します。

活用例が以下になります。

=NETWORKDAYS.INTL(B4,EOMONTH(B4,0),"1010000",休日一覧!$B$3:$B$19)

このように求めることができました。

▼サンプルファイル▼

trick-no90.zip ダウンロード

2023/06/05

【ExcelVBA】ダブルクリックで塗りつぶしとリセットを繰り返す

2023年6月5日2023年7月7日it-yobi

シート上の特定のセル範囲をダブルクリックするだけで、セルの背景色を黄色（黄色の際はリセット）する方法について解説しています。
こちらの内容を応用することで、塗りつぶしではなく、文字を変更したりすることも可能です。
※完成例は記事の最後にて配布しております。

では、以下の範囲（B2～B11）に適用していきます。

特定のシート上をダブルクリックするとプログラムが実行するようにするには、該当するシートモジュール上にプログラムを記述する必要があります。
[開発]タブから[Visual Basic]を選択します。

表示されたVBE（プログラムを記述するウィンドウ）のプロジェクトから、該当するシートをダブルクリックし選択します。
※プロジェクトが表示されていない場合は、[表示]タブの[プロジェクトエクスプローラー]を選択すると表示されます。

上記の操作で表示したものはシートモジュールと呼ばれるもので、シート単位で管理されているプログラムを管理する場所になります。
シートモジュール上の[General]から[Worksheet]を選択します。

ここで表示された「Private Sub Worksheet_SelectionChange]というものは、該当するシート上の選択しているセルが変更された場合に実行される特殊な「イベントプロシージャ」と呼ばれるものになります。
今回はダブルクリックした際に実行するようにしたいため、こちらは使用しません。

「Private Sub Worksheet_SelectionChange]を表示させると、右上に「SelectionChange」と表示されるかと思います。
ここから「BeforeDoubleClick」を選択します。

上記の操作で表示された「Private Sub Worksheet_BeforeDoubleClick」が、セルをダブルクリックすると実行される特殊なイベントプロシージャになります。
今回はこちらを使用します。

先ほどの「Private Sub Worksheet_SelectionChange」は使用しないため、削除して問題ございません。
また「Private Sub Worksheet_BeforeDoubleClick」内に以下のように記述します。
※Option Explicitは必要に応じて削除しても問題ございません。

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.Row >= 2 And Target.Row <= 11 And Target.Column = 2 Then
        Cancel = True
        If Target.Interior.ColorIndex = xlNone Then
            Target.Interior.Color = RGB(255, 255, 0)
        Else
            Target.Interior.ColorIndex = xlNone
        End If
    End If
    
End Sub

簡単にプログラムの内容を解説します。
シート上をダブルクリックすると、「Private Sub Worksheet_BeforeDoubleClick」が実行されます。
また実行時に、「Target」にダブルクリックされたセル情報が入ってきます。

そのため、If文を活用し「Target.Row（ダブルクリックされたセルの行番号）」が２以上かつ１１以下、「Target.Column（ダブルクリックされたセルの列番号）」が２（B列）の時に「If～End If」間の内容が実行されます。
実行時に「Cancel = True」で編集モードになるのを中断しています。

その次に、If文を活用し「Target.Interior.ColorIndex（ダブルクリックされたセルの背景色情報） = xlNone（無色：塗りつぶしなし）」でセルの背景色が無色の場合に「If～Else」間の内容が実行されます。
先ほどの条件を満たしていない場合は、「Else～End If」間の内容が実行されます。

そこで無色の場合は「Target.Interior.Color = RGB(255, 255, 0)」で背景色を黄色に、色が既に設定されている場合は「Target.Interior.ColorIndex = xlNone」で無色にしています。

シートモジュールを含む各モジュールについては下記にて解説しています。
＞各モジュールの違いと使い分け

▼サンプルファイル▼

trick-no88.zip ダウンロード

2023/05/31

【Excel】複数の目標値を自動で計算「ソルバー」

2023年5月31日2024年10月22日it-yobi

逆算して値を求める機能に「ゴールシーク」という機能があります。
それを更に進化させた機能が「ソルバー」になります。

ゴールシーク

まずは、ゴールシークの機能から簡単に確認します。

以下のような数式と目標人数が入力された表があります。
例えば、この表を用いて「合計金額を”100,000”にするには、Bプランの目標人数を何人にする必要があるのか？」
それを求めていきます。

合計金額を求める数式の入力されたセル「E6」を選択し、[データ]タブから[What-IF 分析]、[ゴールシーク]を選択します。

選択すると以下のような画面が表示されます。
この[数式入力セル]に選択したセルのアドレスが自動入力されるため、残りの[目標値]と[変化させるセル]を入力します。

入力後、[OK]で確定すると以下のようにBプランの人数を自動で調整してくれます。

※値を変化させながら収束させていく仕組みのため、場合によっては収束できないことがあります。

ソルバー

では次にソルバーです。
ゴールシークでは特定の１つのセルを変化させることしかできなかったのですが、ソルバーでは複数のセルを変化させることができます。

例えば、以下の表を元に、合計金額が”100,000”になるようなA～Cプランの人数を求めていきます。
また、人数を求める際に、「Cプランは10人まで」などの制約を設けることもできます。

まずは[開発]タブの中の[Excelアドイン]から[ソルバーアドイン]を追加します。

[ソルバーアドイン]を追加すると、[データ]タブの中に[ソルバー]が追加されます。
こちらを選択します。

選択後、必要な値を入力し[解決]で実行します。

【設定内容】
目標セル：$E$6 // 目標値の数式のセル
指定値：100000 // 10万円の利益を目標
変数セル：$D$3:$D$5 // A～Cプランの人数
制約：$D$5 <= 10 // Cプランは10人まで

実行すると以下のように求めることができます。
※ソルバーに関しても上手く求められないことがあります。

2023/05/30

※サイト内の限定動画です。

自動で担当を割り当てる機能について解説しています。
それぞれの役割に人数を設定してプログラムを実行するだけで、ランダムに割り当てます。

「この役割はこの人には割り当てたくない」などの設定も可能です。

00:00　挨拶
00:17　完成イメージ
02:03　準備
02:28　実現方法
05:17　作成（担当自動割当）
19:21　作成（割当ボタン）
19:50　完成
21:47　プログラムの全体
29:01　まとめ

▼準備ファイル▼

225準備「担当自動割当」.zip ダウンロード

2023/05/23

【Excel】指定日以降でないと入力できない設定

2023年5月23日2024年10月18日it-yobi

指定日以降でないと入力できない設定方法について、営業日を考慮しない方法と考慮する方法の２パターンについて解説しています。

営業日を考慮しない方法

以下の表の対応予定日に関して、申込日から３日後以降でないと入力ができないような設定を行います。

入力制限する対象のセルを選択し、[データ]タブの中の[データの入力規則]を選択します。

[データの入力規則]の画面にて、[入力値の種類]を「ユーザー設定」にし、以下の数式を入力します。

C3>=B3+3

初めの「=」は「今から数式が始まります」というような意味になります。その後に、入力を許可する条件式を設定します。
こちらでは、「セルC3は、セルB3の３日後以降である」という条件式になります。
日付はシリアル値で管理されているため、このように「+」で計算することができます。

＞シリアル値とは

次に、[エラーメッセージ]に関しても以下のように設定します。
[スタイル]を「停止」にすることで、対象外の日付を入力できなくなります。
※デフォルト値は「停止」です。

上記の設定で確定することによって、申込日から３日後以降でないと入力ができなくなりました。

営業日を考慮する方法

以下の表の対応予定日に関して、申込日から営業日を考慮して３日後以降でないと入力ができないような設定を行います。

同じように[データの入力規則]を開きます。
ここで設定する数式は先ほどとは異なり以下のようになります。

=C3>=WORKDAY.INTL(B3,3,1,E3:E100)

WORKDAY.INTL関数を用いて定休日(土日)と休日を除いた３日後の日付を求めています。
休日一覧表に関しては、データが追加されても反映されるように、「E3:E100」と１００行目まで考慮しています。
※休日の範囲内に文字列が含まれるとエラーになります。そのため、未入力は空白にする必要があります。

＞WORKDAY.INTL関数の使い方について

必要に応じて[エラーメッセージ]を設定し、確定することで適用されます。

2023/05/22

【Excel】特定の項目が重複しているデータを削除

2023年5月22日2024年10月18日it-yobi

以下の画像のように「特定の項目が重複しているデータ」を瞬時に削除する方法について解説しています。
※重複箇所が分かりやすいように黄色で塗りつぶしています。

重複データの削除

まず初めに、表の中のセルを選択し、[データ]タブの中の[重複の削除]を選択します。

選択すると、以下のように表のデータの範囲が自動で選択され、[重複の削除]のウィンドウには項目名が表示されます。

表に空白列や空白行がある場合は、正しい範囲が選択されないことがあります。

その場合は、予め対象の範囲を選択してから、[挿入の削除]を選択します。
この時に、項目名が表示されず列名が表示されることがありますが問題ございません。
項目名を表示したい場合は、項目名を含む全体を選択し、[先頭行をデータの見出しとして使用する]にチェックを入れます。

次に、重複を確認したい項目以外のチェックを外してOKで確定します。

このように、簡単に重複した行を削除することができました。
重複データに関して、最も上にあるデータのみが残り、それ以外のデータが削除されます。

2023/05/18

【ExcelVBA】人数分の招待状を自動で印刷

2023年5月18日2024年10月18日it-yobi

以下の表の内容をもとに招待状を作成して印刷するという作業を自動化する方法について解説していきます。

数式の作成

まず初めに、できる限り数式を使って招待状を作成できるようにします。
今回の場合は、Noがキーになっているため、Noをもとに、他の氏名や性別が表示されるように以下のように作成します。

=OFFSET(招待者一覧!D2,C3,0) // フリガナ
=OFFSET(招待者一覧!C2,C3,0) // 氏名
=OFFSET(招待者一覧!E2,C3,0) // 性別

こちらではOFFSET関数を使っていますが、場合によってはVLOOKUP関数などでも対応できるかと思います。
今回の場合のOFFSET関数の使い方は以下になります。

=OFFSET(参照, 行数, 列数)
// 参照セルから指定の行数・列数移動した位置のセルの情報を返す

=OFFSET(招待者一覧!D2,C3,0) // フリガナ
// 招待者一覧シートのセルD2を基準にセルC3の数分下へ移動したセルの情報を返す

VBAで自動印刷

次に、VBAを使って、Noの値を変更しつつ、招待状シートを印刷します。

[開発]タブの中から[マクロ]を選択し、好みの名前でプログラムを作成します。

こちらでは以下のようなプログラムを作成しています。

Sub 自動印刷()
    
    Dim 開始No As Long: 開始No = 1
    Dim 終了No As Long: 終了No = 20
    
    Dim i As Long
    
    For i = 開始No To 終了No
        Worksheets("招待状").Range("C3").Value = i
        Worksheets("招待状").PrintOut
    Next i
    
End Sub

今回の場合、Noは１からの連番なので、招待状シートのセルC3の値を開始Noから終了Noまで順番に実行しています。
それぞれのNoをセルC3に設定した後に、招待状シートをPrintOutで印刷しています。

このPrintOutは設定されている印刷設定の内容をもとに実行されます。
そのため、こちらのプログラムを実行する前に、印刷設定にて、プリンターや余白などの設定を行う必要があります。

必要に応じてプログラムの一部を変更する必要がありますが、基本はこのようなアルゴリズムで自動化することができます。

▼サンプルファイル▼

trick-no76.zip ダウンロード

2023/05/15