IT予備
業務効率化のコツが無料で学べる！Excelなどの解説&配布サイト
パターンの決まっているデータ（定型）を、瞬時にデータベースの項目に入力する機能になります。
定型シートに予めパターンを登録することで、必要に応じて定型シートから自動で反映することができます。
数式を使用していないため、反映された定型のデータの一部を修正して登録することもできます。
00:00 挨拶
00:35 完成イメージ
04:26 準備
05:03 作成（定型リスト）
07:29 作成（定型文自動入力）
23:44 完成
25:54 プログラムの全体
29:51 まとめ
▼準備ファイル▼
Excelには、「初学者向けの無料教材」が用意されていることはご存じでしょうか？
その教材の中には、実践形式で取り組める、以下のような教材も含まれます。
この教材のダウンロード方法について解説していきます。
まずは、空のExcelファイルを開き、[ヘルプ]タブの中の[トレーニングを表示]を選択します。
そのようにすると、Excelの画面上に、以下のようなヘルプの画面が表示されます。
このメニューの下の方を確認すると、[テンプレートをダウンロードする]と書かれている箇所が見つかります。
こちらから学習用のファイルを無料で取得することができます。
試しに、[数式のチュートリアル]の[テンプレートをダウンロードする]を選択してみると、以下のような、テンプレートファイルを取得することができます。
拡張子が「xltx」というテンプレート形式のファイルでダウンロードされます。
この形式のファイルに関しては、ダブルクリックして開くと、常に新しいファイルとして開かれます。
そのため、このテンプレート形式のファイルをそのまま研修用のフォルダ内に配置していたとしても、上書きされる心配が少なくなります。
こちらのファイルを開いて、シートの内容に沿って学習することができます。
基本は、シートを直接選択して切り替える必要がなく、シート上のリンク（上記の画像ですと、[さらに詳しく]や[次の手順へ]と書かれた画像がリンクになります）を選択するだけで、次の解説に遷移することができます。
表の一部の項目を隠す際に、対象の列を選択し、右クリックメニューから非表示にするという方法があります。
ただ、この方法の場合ですと、項目を再度確認する際に、対象の項目を選択してからの再表示、そして確認後に、再度非表示にする必要があります。
このようになると、確認するだけでも大変になってしまうため、項目を隠す場合に関しては、グループ化がおすすめになります。
グループ化する場合は、最初に、グループ化したい対象の列（行でも可能）全体を選択します。
以下の例では、１列のみを選択していますが、E列からG列などと複数列の範囲選択でも問題ございません。
次に、[データ]タブの中の[グループ化]を選択します。
もしくは、ShiftキーとAltキーを押しながら矢印の「→」キーを押します。
以上の操作で、以下のように、対象の列をグループ化し、アウトラインを表示することができます。
[-]を選択すると、対象範囲の列名の上に「・」と書かれた列が非表示になります。
[+]を選択すると、非表示になった列が表示されます。
[１]を選択すると、列全体のグループを非表示に、[２]を選択すると、列全体のグループを表示することができます。
また、グループに関しては、複数範囲やグループの中にグループなどと、いくつも作成することができます。
グループ化を解除する時は、解除したい対象の列（列名の上に「・」と書かれた列）を選択して、[データ]タブの中の[グループ解除]を選択します。
もしくは、ShiftキーとAltキーを押しながら矢印の「←」キーを押します。
全てのグループを一括で解除したい場合は、[グループの解除]のメニューを展開し、[アウトラインのクリア]を選択することで、一括で解除できます。
旧タイトル：【ex35】マトリックス表をリスト形式に変換
マトリックス表を１行１データのリスト形式の表に変換する方法について解説しています。
また、動画内では、マトリックス表からピボットテーブルを作成する方法についても解説しています。
00:00 挨拶
01:26 準備
01:53 マトリックス表からピボットテーブルを作成
05:12 ピボットテーブルからリスト形式の表を作成
07:30 まとめ
▼準備ファイル▼
以下の「未完了の表」の中にあるタスク名をダブルクリックすることで、瞬時に、「完了の表」に該当するタスクを移動させる仕組みの実現方法について、解説していきます。
今回の仕組みを実現したサンプルファイルは、記事の最後にて配布しています。
特定のシート内の特定のセルをダブルクリックした時に、何かしらの処理を実行させる場合は、「シートモジュール」を活用します。
シートモジュールは、[開発]タブの中の[Visual Basic]を選択し、表示されたVBEの画面内の[プロジェクト]から、該当するシートをダブルクリックすることで、開くことができます。
他の方法ですと、該当するシートタブ上で右クリックし、表示されたメニューから[コードの表示]を選択することでも、シートモジュールを開くことができます。
次に、「セルをダブルクリックした時に実行されるイベントプロシージャ」を用意する必要があります。
そのため、シートモジュール上の[General]と書かれたリストから[Worksheet]を選択します。
恐らく、「Worksheet_SelectionChange」というイベントプロシージャが表示されるかと思います。
次に、隣のリストから[BeforeDoubleClick]を選択します。
そのようにすると、「Worksheet_BeforeDoubleClick」というイベントプロシージャが表示されるため、このプロシージャのみを残し、他は削除しても問題ございません。
この「Worksheet_BeforeDoubleClick」というイベントプロシージャが、シートモジュールが属するシート上のセルをダブルクリックした時に実行されるイベントプロシージャになります。
次に、SubからEnd Subの間に、以下のコードを記述します。
コードの内容について解説します。
「Worksheet_BeforeDoubleClick」では、引数である「Target」にダブルクリックされた対象のセルの情報が渡されます。
そのため、「If Target.Row >= 4 And Target.Column = 2 And Target.Value <> “” Then」にて、ダブルクリックされた対象が、４行目以上であり、２列目（B列）であり、空白でない場合に「If」から「End If」の間の処理が実行されるようにしています。
※１行のコードを改行する場合は、「(半角スペース)_」を入力します。
「４行目以上、２列目」というのは、該当するシート上の「未完了の表」の中を指します。
次に、「Worksheet_BeforeDoubleClick」の引数の「Cancel」を更新します。
[Cancel]の値を「True」にすることで、ダブルクリック後の動作（セル内にカーソルが表示され、編集モードに切り替わる）を中断することができます。
今回の目的は、タスク名が入力されたセルを移動させることになるため、「Cancel = True」と記述し、ダブルクリック後の動作を中断しています。
最後に以下の内容です。
「Target.Copy」にて、ダブルクリックされたセル自体をコピーし、「Range(“D4”).Insert xlDown」にて、セルD4の位置に、コピーしたセルを挿入しています。
「xlDown」では、セルの挿入後に、セル全体が下方向に移動するように指定しています。
そして、「Target.Delete」にて、元のセルを削除することで、未完了タスクを上に詰めています。
「Target.Delete」に関しては、「Target.Delete xlShiftUp」のように「xlShiftUp」と記述し、「上方向にシフト」と指定することもできますが、省略時は「xlShiftUp」が適用されるため、こちらでは省略しています。
以上で完成になります。
実装したサンプルファイルは、以下になります。
▼サンプルファイル▼
パターンの決まっているデータ（定型）を、瞬時にデータベースの項目に入力する機能になります。
定型シートに予めパターンを登録することで、必要に応じて定型シートから自動で反映することができます。
数式を使用していないため、反映された定型のデータの一部を修正して登録することもできます。
00:00 挨拶
00:32 完成イメージ
04:10 準備
04:55 作成（定型シートの表をテーブルに変換）
06:27 作成（定型のリスト）
08:27 プログラムの概要
08:59 作成（定型文の自動入力）
22:01 完成
24:05 プログラムの全体
28:02 まとめ
▼準備ファイル▼
条件付き書式などの設定画面のテキストボックス内で、矢印（カーソル）キーを使ってカーソルを移動しようとした結果、以下のように、セルの参照が入力されてしまった経験はないでしょうか。
こちらでは、セルの参照が入力されてしまう原因と、カーソルを移動できるようにする方法について解説していきます。
まずは、設定画面ではなく、セルに値を入力します。
そのようにすると、ステータスバーに「入力」と表示されるかと思います。
次に、入力を確定した後に、セルをダブルクリックして編集してみます。
そのようにすると、ステータスバーに「編集」と表示されます。
ステータスバーに「入力」と表示されている時は、矢印キーを使うと、入力が確定されてしまい、セル内のカーソルの移動ができません。
それに対し、「編集」と表示されている時は、矢印キーを使うことで、セル内のカーソルを移動することができます。
これは、設定画面のテキストボックスでも同様になります。
条件付き書式などの設定画面を開いて、テキストボックスを選択している時は、ステータスバーに「入力」と表示されているかと思います。
この時に、矢印キーを使ってしまうと、選択範囲が移動してしまい、「入力」が「参照」に変わってしまいます。
通常、矢印キーでカーソルを移動させるには、「編集」にする必要があります。
ただ、設定画面のテキストボックス内をダブルクリックしても、「編集」にはなりません。
そんな時に活用できるのが、「F2」キーになります。
このキーは、「編集」に切り替えることができるキーです。
そのため、テキストボックス内を選択した後に、「F2」キーを押すことで、以下のように「編集」に切り替えることができ、矢印キーでのカーソルの移動ができるようになります。
「A,B,C,…」や「①,②,③,…」などの連続した文字を、オートフィル機能を活用して入力する方法について解説していきます。
通常、「A」などのアルファベットや「①」などの記号は、オートフィル機能を活用して連続した値を入力することができません。
しかし、文字コードを活用することで、入力することができるようになります。
文字コードとは、入力できる全ての文字に割り当てられている数値のことになります。
文字から文字コードを取得するにはCODE関数、文字コードから文字を取得するにはCHAR関数を活用します。
これらの関数の使い方は、以下の通りです。
=CODE(文字列)
// 文字列の先頭文字を表すコードを返す=CHAR(数値)
// コードに対応する文字を返すCODE関数とCHAR関数の活用例は、以下になります。
=CODE(B2)=CHAR(E2)
上記の画像のように、基本は連続した文字に対して、連続した文字コードが割り当てられています。
ただ、「あ、い、う、え、お」となどの文字や、一部の文字に関しては、連続でないことや認識されないこともあります。
認識されない場合は、「?」の文字コードである「63」が、その認識されない文字の文字コードとして返されます。
文字コードでは、例外もありますが、基本は連続の文字に対し、連続の数値が割り当てられています。
今回は、この仕組みを活用して連続した文字を入力していきます。
まずは、オートフィル機能を活用して連続した文字コードを入力する方法から解説していきます。
連続した文字コードを入力するためには、ROW関数、もしくは、COLUMN関数を活用します。
これらの関数の使い方は、以下の通りです。
=ROW(参照)
// 参照したセルの行番号を返す=COLUMN(参照)
// 参照したセルの列番号を返す例えば、列方向に「A,B,C,…」といったアルファベットの文字コードを入力する場合、『「A」の文字コードにセルA1の行番号を加え、１を引く』という数式を作成することで実現できます。
実際に作成したものが、以下になります。
=CODE("A")+ROW(A1)-1
行方向に入力する場合、『「A」の文字コードにセルA1の列番号を加え、１を引く』という数式を作成することで実現できます。
実際に作成したものが、以下になります。
=CODE("A")+COLUMN(A1)-1
このようにして、文字コードを入力することができます。
最後に、先ほど入力した文字コードに対応した文字を表示させる方法についての解説になります。 文字を入力するには、以下のように、先ほどの数式をCHAR関数で囲むことで実現できます。
=CHAR(CODE("A")+ROW(A1)-1)
=CHAR(CODE("A")+COLUMN(A1)-1)
同じ理屈で、「①,②,③,…」を入力する場合は、CODE関数の引数を「A」から「①」に変更するだけで実現できます。
=CHAR(CODE("①")+ROW(A1)-1)
旧タイトル：【2-06】テーブルの「構造化参照」とは
テーブルを参照する際に活用できる「構造化参照」を用いた数式の書き方について解説しています。
00:00 挨拶
00:41 準備
01:15 テーブルの作成
02:21 構造化参照の活用
07:35 構造化参照の活用2
11:31 まとめ
テーブルについての解説は以下になります。
＞【1-07】テーブルの活用方法
▼準備ファイル▼
以下のように、予め、[目次]シートに追加する予定のシート名一覧表を作成し、存在しているシートのみに、そのシートへ遷移するリンクを表示する方法について解説していきます。
初めに、以下のようなシート名一覧表を作成し、今後追加する予定のシート名を入力します。
また、シート名一覧表には、[リンク]という項目を用意します。
まずは、それぞれのシートのセルA1に遷移するリンクを、HYPERLINK関数を用いて作成していきます。
この関数の使い方は、以下になります。
=HYPERLINK(リンク先, [別名])
// 指定したリンク先に遷移するリンクを作成する
// リンク先：リンク先の情報を指定
// [別名]：セルに表示させる文字を指定（省略時は引数[リンク先]に指定した値を表示する）HYPERLINK関数を用いて、特定のシートのセルに遷移するリンクを作成する場合は、引数[リンク先]に、「#セルのアドレス」と指定する必要があります。
例えば、「ABC」というシートのセルA1に遷移する場合は、「#ABC!A1」になります。
「!」などの特殊な文字が含まれるシート名「!ABC」のシートに遷移する場合は、「#’!ABC’!A1」などと、シート名を「’」で囲む必要があります。
そのため、予め「’」で囲んだシート名を指定しておくと良いです。
今回の場合、シート名は、表の中のセルの値を参照する必要があるため、「&」を活用し、以下のような数式を入力します。
=HYPERLINK("#'"&B4&"'!A1","●")
//「#’」とセルB4の値、「’!A1」を「&」で文字結合
後は、こちらの数式を表の最後までコピーすることで、リンクを作成することができます。
ただ、このままですと、存在していないシートに関してもリンクが表示されてしまいます。
存在していないシートを選択すると、以下のように、エラーが表示されてしまいます。
ということで、次は、存在しないシートに関しては、リンクを表示させない仕組みを実現していきます。
HYPERLINK関数のみですと、常にリンクが表示されてしまうため、他の関数を組み合わせる必要があります。
まずは、シートの存在の有無を数式で求めていきます。
シートの存在の有無を求めるには、INDIRECT関数が便利です。
この関数の使い方は、以下になります。
=INDIRECT(参照文字列)
// 指定した参照文字列を参照した値を返えす
// 参照文字列：参照先の情報INDIRECT関数を活用して、引数にリンク先のアドレスを指定することによって、そのリンク先が存在しない場合にエラーを返すことができます。
試しに、表の隣にINDIECT関数を入力して、シートの存在の有無を確認していきます。
=INDIRECT("'"&B4&"'!A1")
数式を入力して表の最後までコピーすると、存在しているシートの場合は、そのシートの指定したセルの値が表示され、存在していない場合は、「#REF!」というエラーが表示されます。
次に、エラーの場合はTRUE、エラー出ない場合はFALSEを返すISERROR関数で囲みます。（ISERROR関数で囲むだけなので、関数の解説は省略します）
=ISERROR(INDIRECT("'"&B4&"'!A1"))
このようにTRUEとFALSEを表示することができました。
後は、TRUEの場合のみに、HYPERLINK関数の内容を表示すればよいので、IF関数を活用します。
この関数の使い方は、以下になります。
=IF(論理式, 値が真の場合, 値が偽の場合)
// 論理式の値に応じて返す値を変更する
// 論理式：TRUEもしくはFALSEを返す式
// 値が真の場合：論理式がTRUEの時に返す値
// 値が偽の場合：論理式がFALSEの時に返す値実際に、INDIRECT関数、ISERROR関数、IF関数をHYPERLINK関数の数式に組み合わせると、以下のようになります。
=IF(ISERROR(INDIRECT("'"&B4&"'!A1")),"",HYPERLINK("#'"&B4&"'!A1","●"))
このようにして、シートが存在している行のみにリンクを表示させることができます。
リンクの色が青色にならない場合は、手動で書式を設定すると良いです。
この作成方法の場合、リンクが表示されていない空白のセル上にカーソルを移動した時に関しても、カーソルが指マークになってしまいます。
ただ、リンクとしては機能せず、クリックしてもセルが選択されるだけなので、そこまで問題ではないです。
該当するシートが追加された時に、自動でリンクが表示されます。
最新の関数を用いて、以下の社員ごとの売上表を参照し、赤枠に上位３件を抽出する方法について解説していきます。
まずは、売上表の「売上」を基準に降順にした表を、SORT関数を用いて抽出していきます。
この関数の使い方は、以下になります。
=SORT(配列, [並べ替えインデックス], [並べ替え順序], [並べ替え基準])
// 指定した配列を指定した順序に並べ替えた表を返す
// 配列：対象の表
// [並べ替えインデックス]：並べ替えの基準となる項目の位置
// [並べ替え順序]：「1」昇順、「-1」降順
// [並べ替え基準]：「TRUE」列で並べ替え、「FALSE」行で並べ替え
// ※引数「配列」以外は省略可能では、SORT関数を用いて、セルG3を基準に、表を抽出していきます。
=SORT(B3:D12,3,-1)
// B3:B12：表の範囲
// 3：売り上げの項目の位置（先頭から3列目）
// -1：降順
表を抽出することができましたら、抽出した表の先頭から３件のみを抽出します。
その際に活用する関数は、TAKE関数になります。
この関数の使い方は、以下になります。
=TAKE(配列, 行数, [列数])
// 指定した配列から指定した行数（列数）を抽出する
// 配列：対象の表
// 行数：抽出する行数（正の数：上から抽出、負の数：下から抽出）
// [列数]：抽出する列数（正の数：上から抽出、負の数：下から抽出）
// ※[列数]は省略可能、省略時は全ての列を抽出では、TAKE関数を用いて、先頭から３件のみを抽出していきます。
=TAKE(SORT(B3:D12,3,-1),3)
// SORT(B3:D12,3,-1)：抽出する対象の表
// 3：先頭から抽出する行数
このようにして、売上の上位３件を抽出することができます。
以下の表に関して、瞬時に曜日単位の集計表を作成する方法について解説していきます。
まずは、曜日単位で集計するために、曜日の列を追加します。
こちらでは、日付の列の隣に列を挿入し、曜日という項目を追加しています。
次に、日付から曜日を求めていきます。
そのような際は、TEXT関数が便利です。
=TEXT(値, 表示形式)
// [値]に対し[表示形式]を適用した文字列を返す日付から曜日を表示する表示形式は「aaa」になります。
そのため、TEXT関数を用いて、表示形式「aaa」を適用した文字列を取得します。
=TEXT(B3,"aaa")
数式を入力後、最終行までコピーして、曜日を表示させます。
次に、表の中を選択し、[挿入]タブより[ピボットテーブル]を選択して、ピボットテーブルを作成します。
ピボットテーブルを作成すると、[ピボットテーブルのフィールド]という画面が表示されます。
表示された画面にて、[曜日]を[行]に、[売上]を[値]に移動させます。
以上の作業を行うだけで、以下のような曜日単位の集計表を作成することができます。
曜日の順番に関しては、該当する曜日を選択して、緑の枠上（カーソルが十字矢印になる位置）でドラッグすることで移動することができます。
必要に応じて、作成したピボットテーブルを選択した時に表示される[デザイン]タブなどを活用し、デザインに関しても変更すると良いです。