it-yobi | IT予備~ITで効率化する予備知識~

2025/02/18

【Excel】自由入力もできるドロップダウンリスト

2025年2月18日2025年2月11日it-yobi

Excelで作成した表の特定の項目に関して、基本はリストから選択させたいけれど、稀に例外的な入力も必要な場合があります。
しかし、通常は例外的な入力をするとエラーになってしまいます。

こちらでは、リストの例外的な入力にも対応する方法について解説しています。

記述方法

１. リストを設定する範囲を選択し、[データ]タブから[データの入力規則]を選択する

２. 設定画面の[入力値の種類]を[リスト]にし、[元の値]にリストの設定値を入力する

３. [エラーメッセージ]タブを開き、[無効なデータが入力されたらエラーメッセージを表示する]のチェックを外す

以上の設定で完了です。
これだけで、例外的な入力も可能になります。

入力時に警告を表示する方法

「例外的な入力は可能にしたいけど警告は表示したい」
そのような場合は、先ほどの手順３の画面でチェックは外さずに[スタイル]を[注意]もしくは[情報]に変更します。

[タイトル]と[エラーメッセージ]のテキストボックスにて、警告画面に表示するテキストを指定することができます。

・注意：[いいえ]が選択されている状態で警告が表示される

・情報：[OK]が選択されている状態で警告が表示される

まとめ

データの入力規則を活用することで、

・基本はリストから選択
・例外的な入力の許可
・警告メッセージの表示

といった柔軟な設定ができます。
ぜひ試してみてください。

2025/02/15

【番外-049】条件付き書式などで「カーソル移動ができない問題」

2025年2月15日2025年7月20日it-yobi

旧タイトル：【ex49】条件付き書式などで「カーソル移動ができない問題」

#条件付き書式 #データの入力規則

YouTubeで開く

「条件付き書式」や「データの入力規則」などの設定画面内のテキストボックスで、カーソルを移動しようとカーソル（矢印/方向）キーを押したところ、「+$A$1」などの参照が入力されてしまった経験はないでしょうか。
こちらでは、参照が入力されてしまう原因と解決方法について解説しています。

00:00　挨拶
00:20　本題に入る前に
00:40　原因
01:40　本題
03:22　プレゼントについて

2025/02/14

【Excel】カテゴリー単位の連番を自動表示

2025年2月14日2025年2月4日it-yobi

以下の表では、性別ごとで異なる連番をIDとして設定しています。
男性は「M」から始まる連番で、女性は「F」から始まる連番になっています。

このようなカテゴリー（性別）単位の連番を入力する場合、毎回手入力するのは大変ですよね。
実は、数式を使うことで自動で入力することができます。

こちらでは、上記の性別ごとの連番を例に解説していきます。

性別ごとに連番を付ける数式の作成方法

まずは、IDの項目に、男なら「M」、女なら「F」と表示する数式を入力します。
その数式は以下になります。

=IF(D3="男","M","F")
// セルD3が"男"なら"M"、それ以外は"F"と表示する

この数式をセルB3に入力し、表の最終行までコピーして正しく表示されることを確認します。

次に、「M」や「F」の後に連番を表示する数式を加えます。
その数式は以下になります。

=IF(D3="男","M","F")&COUNTIF($D$3:D3,D3)
// セルD3からD3の中でセルD3の値の数を加える
// 範囲の開始セルD3のみ「$」で固定し、コピーした際に範囲が拡張するように指定する

この数式をセルB3に入力し、表の最終行までコピーすることで、性別ごとの連番を表示することができます。

2025/02/11

【Excel】数式などでシートを切り替えずに別シートを参照！？

2025年2月11日2025年2月4日it-yobi

数式を入力する際などに、別シートのセルを参照することはよくあります。
その際に、毎回シートを切り替えるのは面倒ですよね。

そんな時におすすめの機能を紹介していきます。

複数のウィンドウを開く

Excelには、同じファイルを複数のウィンドウで開く機能が用意されています。
その機能を活用することで、別シートを別ウィンドウとして常に表示することができるので、シートを切り替えることなく別シートを参照した数式を入力することができます。

手順と使い方

[表示]タブの中の[新しいウィンドウを開く]を選択することで、ウィンドウを分割して表示することができます。

同じウィンドウが表示されましたら、ウィンドウの位置を調整し、別々のシートを開きます。

このようにウィンドウを分割することで、数式を入力する際などに、シートを切り替えることなく別ウィンドウのシートを選択することで参照することができます。

補足

複数のウィンドウを開いている状態で、Excelファイルを保存すると、複数のウィンドウが開かれている状態で保存されます。
そのため、再度開き直した際にも複数のウィンドウが表示されてしまいます。

他のウィンドウが不要になった際は、どのウィンドウでも良いので１つのウィンドウを残した状態で閉じられると良いです。

また、新しいウィンドウに関しては、Excelのシートに設定した表示倍率がリセットされてしまうので注意してください。

2025/02/10

【業務】表の項目の「表示・非表示」切り替え

2025年2月10日2025年6月28日it-yobi

#表示 #非表示 #Hidden #標準モジュール #ユーザーフォーム #UserForm_Initialize

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

※動画内では、項目名の更新時に「変更後の名前の要素追加 → 元々の要素の削除」としていますが、以下のように直接更新することもできます。
.List(.ListIndex) = Cells(ROW_NO, colNo).Value
→ 「ListIndex」でダブルクリックされた要素の位置情報を取得して、その要素を直接更新する

表の項目名の一覧を専用フォーム（リストボックス）に表示させて、フォーム内の項目名をダブルクリックすることで、該当する項目の表示と非表示を切り替えることができる機能になります。

例で解説している表は、セルA1から始まる表ですが、どの位置から始まる表でも、一部を修正することで活用することができます。

00:00　挨拶
00:55　完成イメージ
01:59　準備
02:34　作成（ウィンドウ枠の固定）
03:18　作成（項目名リストボックス：外部）
07:11　作成（項目名リストボックス：内部）
29:28　作成（リストボックス呼び出し）
30:15　作成（実行ボタン）
30:56　完成
31:50　プログラムの全体
40:48　まとめ

▼準備ファイル▼

290準備「項目の表示非表示」.zip ダウンロード

2025/02/08

【業務】ボタン1つで空白を削除して値を上に詰める

2025年2月8日2025年1月27日it-yobi

#UBound #For #If #ReDim

YouTubeで開く

ボタンを押すだけで、表の中の空白セルを除外し、値を上に詰める機能について解説しています。
並べ替え機能や空白セルの削除などでも対応はできますが、こちらの機能の場合は、セルの書式を崩さずにセル内の値のみを上に詰めることができます。

00:00　挨拶
00:34　完成イメージ
02:28　準備
02:56　作成（空白上詰め機能）
14:43　作成（実行ボタン）
15:14　完成
16:02　プログラムの全体
17:57　プレゼントについて

▼準備ファイル▼

327準備「ボタン1つで値を上に詰める」.zip ダウンロード

2025/02/07

【ExcelVBA】表の絞り込みと並べ替えを一瞬でリセット

2025年2月7日2025年6月17日it-yobi

以下のように、並べ替えたり絞り込みしたりした表を毎回元の形に戻すのは手間になります。

そこで、こちらの記事では、並べ替えと絞り込みを一瞬でリセットする「リセット機能」の開発方法について解説します。

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

1. 開発準備

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

表示された以下の画面にて、開発する機能の名前を入力し、[作成]を選択します。
こちらでは、「リセット」と入力しています。

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。

2. コードの記述

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

Sub リセット()
    
    With ActiveSheet.ListObjects("従業員管理表")
        .AutoFilter.ShowAllData
        Dim keyR As Range
        Set keyR = .ListColumns("社員ID").DataBodyRange
        With .Sort
            .SortFields.Clear
            .SortFields.Add key:=keyR
            .Apply
        End With
    End With
    
End Sub

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

    With ActiveSheet.ListObjects("従業員管理表")
        '省略
    End With

対象のテーブルを指定します。
今回の場合、操作を行う表を予めテーブルにしています。
そのテーブル名（従業員管理表）を指定します。

        .AutoFilter.ShowAllData

次に、対象のテーブルの絞り込みを解除しています。

        Dim keyR As Range
        Set keyR = .ListColumns("社員ID").DataBodyRange

次に、対象のテーブルの[社員ID]の範囲を変数（keyR）に割り当てています。

        With .Sort
            .SortFields.Clear
            .SortFields.Add key:=keyR
            .Apply
        End With

最後に、テーブルの既存の並べ替えの設定を解除し、テーブルの[社員ID]の範囲（keyR）を基準に並べ替える設定を適用しています。

3. ボタンの作成

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

4. 完成

以上の手順で、機能が完成します。
リセット機能を実行することで、以下のように表の絞り込みが解除され、[社員ID]を基準に並べ替えられます。

▼サンプルファイル▼

trick-no384.zip ダウンロード

2025/02/05

【Excel】ガントチャートの対象期間を自動色付け

2025年2月5日2025年1月28日it-yobi

以下の表のように、開始日と終了日を指定するだけで、その期間を自動で色付けする方法について解説します。

条件付き書式の設定

指定した条件を満たしているセルを自動で色付けする場合、『条件付き書式』を活用します。

ステップ1: 色付け対象の範囲を選択

1. Excelシートで、色付けしたい項目の範囲を左上から右下に向かってドラッグして選択します。
こちらではセルE5を基準に全体を選択しています。

ステップ2: 条件付き書式を設定

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

2. 設定画面で [数式を使用して、書式設定するセルを決定] を選択します。

ステップ3: 条件式を入力

1. 選択された範囲の基準となるセルが色付けされる条件式を入力します。
こちらではセルE5を基準とした条件式を入力します。

セルE5が色付けされる条件は以下になります。

「セルE5の日付が開始日以上かつ終了日以下」

セルE5の日付はDATE関数を用いて、以下のように表現することができます。

=DATE(D2,E2,E3)
// D2：年、E2：月、E3：日

その求めた日付が、開始日以上かつ終了日以下であることを確認する必要があるため、以下のような条件式になります。

=AND(DATE($D$2,E$2,E$3)>=$C5,DATE($D$2,E$2,E$3)<=$D5)
// 条件式に設定した数式は、他の選択範囲にも相対的に反映されるため、一部を「$」で固定する
// $D$2：年は固定のセル
// E$2,E$3：月と日は行のみ固定
// $C5, $D5：開始日と終了日は列のみ固定

この条件式を条件付き書式の設定画面に入力します。

ステップ4: 色を設定

1. [書式]を選択して、希望の色を指定します。
こちらでは、[塗りつぶし]タブから黄色を指定します。

2. 条件付き書式の設定画面を確定して閉じます。

以上の手順で、以下のように対象の期間を自動で色付けすることができます。

2025/02/03

【Excel】表の全選択を瞬時に行う

2025年2月3日2025年1月28日it-yobi

Excelの以下のような大きな表全体を選択する際に、ドラッグ操作で選択している方いませんか？
少しのコツを知るだけで、簡単に、しかも瞬時に表全体を選択できるようになります。

この記事では、空白行や空白列が含まれている表の場合でも一瞬で表全体を選択できる方法を解説します。

空白行（列）がない場合

表の中に空白行（列）がない場合は、以下の方法が最も効率的です。

1. 表の中にカーソルを移動

2. Ctrlキー押しながらAを押す

※「1」で選択したセルを基準に表全体が選択されます。

Ctrl+Aというショートカットだけで、上記のように一瞬で表全体を選択することができます。

空白行（列）が含まれている場合

表の中に空白行（列）が含まれていると、Ctrl+Aでは表の一部しか選択されないことがあります。

このような場合は、以下の方法が効率的です。

1. 表の開始のセル（一番左上）を選択

2. Shiftキーを押しながら、表の最後のセル（一番右下）を選択

この方法であれば、空白行（列）が含まれていたとしても、上記のように一瞬で表全体を選択することができます。

2025/02/01

【番外-048】今後主流の「XLOOKUP関数」の魅力とは

2025年2月1日2025年7月20日it-yobi

旧タイトル：【ex48】今後主流の「XLOOKUP関数」の魅力とは

#XLOOKUP #VLOOKUP

YouTubeで開く

間違いなく今後主流になる『XLOOKUP関数』の魅力と、今まで主流だった「VLOOKUP関数」との違いや、「VLOOKUP関数」が今後も必要になる場面について解説しています。

00:00　挨拶
00:57　VLOOKUP関数のよく知られている欠点
03:31　XLOOKUP関数ではどのように改善されたのか
06:32　VLOOKUP関数が必要とされる場面
07:29　プレゼントについて

2025/01/31

【ExcelVBA】削除したデータを別シートに記録

2025年1月31日2025年1月21日it-yobi

以下の表の中の不要なデータ行のいずれかのセルを選択し、[選択行削除]ボタンを押すだけで、該当するデータを別シート（「削除済み」シート）の表の末尾に移動する機能の開発方法について解説してきます。

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

手順1: 開発準備

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

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

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。

手順2: コードの記述

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

Sub deleteData()
    
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("商品一覧")
    Set ws2 = Worksheets("削除済み")
    
    Dim lastRow As Long
    lastRow = ws2.Cells(ws2.Rows.Count, "B").End(xlUp).Row + 1
    
    Dim rng As Range
    Set rng = ActiveCell
    
    If ActiveSheet.Name = ws1.Name And rng.Row >= 3 Then
        ws2.Rows(lastRow).Value = ws1.Rows(rng.Row).Value
        ws1.Rows(rng.Row).Delete
    End If
    
End Sub

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

    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("商品一覧")
    Set ws2 = Worksheets("削除済み")

移動元、移動先のシート情報を変数「ws1、ws2」に割り当てています。

    Dim lastRow As Long
    lastRow = ws2.Cells(ws2.Rows.Count, "B").End(xlUp).Row + 1

移動先のシート（「削除済み」シート）のデータを追加する行の行番号を取得し、変数「lastRow」に割り当てています。
移動先のシート（「削除済み」シート）のB列の最終行のセルを基準に、「Ctrl+↑」で移動した先のセルの行番号に１を加えた番号を取得しています。

    Dim rng As Range
    Set rng = ActiveCell

実行時に選択されているセル（複数選択の場合は選択基準のセル）の情報を、変数「rng」に割り当てています。

    If ActiveSheet.Name = ws1.Name And rng.Row >= 3 Then
        ws2.Rows(lastRow).Value = ws1.Rows(rng.Row).Value
        ws1.Rows(rng.Row).Delete
    End If

実行時に選択されているシートが移動元のシート（「商品一覧」シート）であり、尚且つ、実行時に選択されているセルの行番号が３以上の場合にIfの中の処理を実行します。
Ifの中の処理では、移動元のシート（「商品一覧」シート）の選択されている行全体の値を、移動先のシート（「削除済み」シート）の表の末尾（lastRow）に入力しています。
入力後、移動元のシート（「商品一覧」シート）の選択されている行全体を削除しています。

以上の内容で、実行時に選択されているセルのデータを「削除済み」シートに移動することができます。