IT予備
業務効率化のコツが無料で学べる！Excelなどの解説&配布サイト
新しいデータを追加するときに、「過去の入力内容をコピーして一部を修正」という作業を行っていませんか？
今回は、この入力の手間を減らす工夫を紹介します。
まずは、入力パターンをまとめた表を別シートに用意します。
こちらでは、「_パターン」シートに表を用意しています。
次に、該当する項目全体を選択し、[データ]タブから[データの入力規則]を選択します。
設定画面で、[入力値の種類]を[リスト]にし、[元の値]に用意したパータン一覧表の範囲全体を指定します。
このままですと、リストからパターンを選択できても編集することができません。
そのため、[エラーメッセージ]タブから[無効なデータが入力されたらエラーメッセージを表示する]のチェックを外します。
以上の設定で確定すれば、完成です。
入力パターンをリストから選択でき、選択後に一部を修正することができます。
ただ、リストの候補に引っ張られて修正した内容が反映できないことがあります。
そのような際は、入力後にEscキーを押してリストの候補を非表示にしてから確定することで反映できます。
チェックの数に応じて山を登る目標シートの作成方法について解説していきます。
※こちらで作成したファイルは、記事の最後にて配布しています。
まずは、以下の２枚のシートを用意します。
「記録」シートには、５件分のタスク（目標）一覧と山の画像、「評価用」シートには、タスク（目標）の件数分の項目と、その項目数と同じ数の画像を用意しています。
「記録」シートの各目標の隣の列全体を選択し、[挿入]タブから[チェックボックス]を選択することで、チェックボックスを用意します。
チェックボックスが表示されたセルには、TRUE（チェックされていない状態）もしくはFALSE（チェックされている状態）が入力されます。
この値を活用して、画像を切り替えます。
「評価用」シートの各項目のセル内に画像を配置します。
画像をセル内に配置するには、対象の画像の左上が対象のセルに重なる位置に移動して、「セル内に配置」のアイコンを選択します。
他の画像も同様に、セル内に配置します。
画像をセル内に配置することで、数式で画像を抽出することができるようになります。
次に、B列の数値と「記録」シートのチェックの数が一致する行の画像のみを右隣り（D列）のセルに抽出する数式を入力します。
まずは、先頭の１番の画像から抽出する数式を入力します。
チェックの数はTRUEの数になるため、COUNTIF関数とIF関数を組み合わせた以下の数式になります。
=IF(COUNTIF(記録!$B$13:$B$17,TRUE)=B3,C3,"")
// 記録!$B$13:$B$17：チェックボックスの範囲は絶対参照にする
先頭に数式を入力したら、他の行にもコピーすることで、チェックの数と対応した行のD列に画像を抽出することができます。
チェックの数を１件にし、「評価用」シートの先頭の数式を入力したセルをコピーします。
次に、「記録」シートの適当な位置に、コピーしたセルを[リンクされた図]として貼り付けます。
[リンクされた図]で貼り付けると、対象のセルを参照した図が貼り付けられます。
コピー元のセルの枠までも表示されてしまうので、必要に応じてトリミングします。
用意できたリンクされた図を複製し、山の画像の上にチェックボックスの数分配置します。
リンクされた図を選択すると、数式バーに参照元のセルのアドレスが表示されます。
山の画像の上に配置したリンクされた図の参照元を「評価用」シートのそれぞれの行のセルに変更します。
変更は数式バーから直接行えます。
画像が表示されていないセルを参照することになるので、一部の画像は透明になります。
それぞれを「評価用」シートのセルD3からD7を参照することで、１つの画像のみが表示され、他の画像が透明になります。
以上の手順で完成です。
チェックの数に応じて、数式により抽出される画像が変わるため、同時に、山の画像の上に表示される画像も変わります。
▼サンプルファイル▼
数式による集計結果とピボットテーブルによる集計結果の「0」の表示非表示を切り替える方法について解説していきます。
数式で集計する場合、通常であれば「0」は「0」と表示されます。
「0」を非表示にする場合、集計結果を表示している範囲（数式が入力された範囲）を選択し、選択範囲の上で右クリックして[セルの書式設定]を開きます。
[セルの書式設定]にて[表示形式]タブから[分類]を[ユーザー定義]にし、[種類]に「#」と指定します。
以上の設定で確定することで、以下のように「0」が表示されなくなります。
ただ、「#」のみですと、３桁区切りのカンマが表示されません。
３桁区切りのカンマも表示したいときは「#,###」と指定します。
これで確定することで、以下のように３桁区切りのカンマが表示されるようになります。
ピボットテーブルの場合、通常であれば「0」は表示されません。
「0」を表示にする場合、[ピボットテーブル分析]タブから[オプション]を選択します。
[ピボットテーブル オプション]にて、[空白セルに表示する値]のチェックを外します。
以上の設定で確定することで、以下のように「0」が表示されるようになります。
以下は、問い合わせ管理表のサンプルになります。
この表から、指定した「会員No」の文字列が含まれるデータを数式で抽出する仕組みを作っていきます。
まずは、抽出用のシートを用意します。
こちらでは、「検索」というシートを別で用意しています。
このシートのセルB2に入力した文字が、元の表の「会員No」に含まれているかどうかを判定していきます。
特定の文字が含まれているかどうかは、SEARCH関数で判定できます。
まずは、以下の数式を追加した「検索」シートのセルA2に入力します。
=SEARCH(B1,問い合わせ一覧!B2:B1000)
// B1：検証する文字が入力されたセル
// 問い合わせ一覧!B2:B1000：元の表の「会員No」の項目全体（大きめに指定）
入力して確定すると、各行に数値が表示されます。
こちらの場合は、A列は日付の書式設定が設定されているため「1900/1/1」と表示されています。
次に、検索欄であるセルB1に適当な文字を入力します。
こちらでは「m9」と入力しています。
そうすると、エラーの箇所と数値が表示された箇所が現れました。
この各行の位置は元の表の位置と一致します。
エラーになった行は、元の表の「会員No」に検索値が含まれていない行です。
数値が表示された行は、元の表の「会員No」の検索値が含まれている行で、検索値が含まれている位置が表示されます。
SEARCH関数は大文字と小文字を同じ文字として判定します。
こちらでは、A列に日付の表示形式が設定されているので「1900/1/1」と表示されてしまっていますが、実際は検索値の「m9」が含まれている位置である１番目の「1」が表示されます。
次に、検索値が含まれている行のセルに「TRUE」、含まれていない行のセルに「FALSE」と表示していきます。
このように判定するには、数値かどうかを判定すればよいので、ISNUMBER関数で囲った以下のような数式にします。
=ISNUMBER(SEARCH(B1,問い合わせ一覧!B2:B1000))
入力して確定することで、以下のようにTRUE/FALSEで検索値を含んでいるかどうかを判定することができます。
次は、先ほど数式で求めた結果のTRUEの行のデータのみを抽出していきます。
そのような時は、FILTER関数を使います。
先ほどの数式に、以下のようにFILTER関数を組み合わせます。
=FILTER(問い合わせ一覧!A2:C1000,ISNUMBER(SEARCH(B1,問い合わせ一覧!B2:B1000)),"")
// 問い合わせ一覧!A2:C1000：抽出元の表全体（SEARCH関数に指定した範囲と同じ行数）
// ISNUMBER(…)：抽出条件
// ""：抽出結果が見つからなかったときに表示する値
入力して確定することで、検索値が「会員No」に含まれるデータのみを抽出することができます。
これで完成です。
「フラッシュフィル」とは、自動で法則性を見つけて自動で抽出する機能になります。
一般的な活用例は、以下になります。
① 抽出例を１つ直接入力する
② 先頭の抽出例の１つのセルを選択して、「Ctrl+E」でフラッシュフィルの実行
※ [データ]タブの[フラッシュフィル]を選択することでも実行可能
今回は、上記の抽出以外の活用例を２つ紹介します。
フラッシュフィルは、ただの抽出ではなく指定した形式で抽出することもできます。
例えば、以下のように姓と名の間の半角スペースをなくした抽出例を１つ直接入力して、「Ctrl+E」で実行すると、その例をもとに抽出することができます。
元のセルから値を抽出するだけではなく、指定した形式に当てはめて抽出することもできます。
例えば、以下のように「姓」を単純に抽出するのではなく、「[姓] 様(改行)お世話になっております。」といった形式で１つを直接入力して、「Ctrl+E」で実行すると、その形式をもとに抽出することができます。
ただ、形式によっては正しく抽出できないこともあるため、注意する必要があります。
例えば、以下のように「姓」と「様」の間に半角スペースがない状態で実行したときなどです。
フラッシュフィルにより実行された処理は、ブラックボックスになります。
そのため、使用した後は「想定通りに抽出されたかどうか」を目視で確認する必要があります。
以下のように、ボタンを押すだけで瞬時に絞り込みできる仕組みの作り方について紹介していきます。
今回は、以下の表から作っていきます。
以下のようなボタンを作成するには、元の表をテーブルに変換する必要があります。
まずは、元の表の中にカーソルを移動させ、[挿入]タブから[テーブル]を選択します。
[テーブルの作成]という設定画面に表示されるデータ範囲が、元の表の見出しを含む範囲全体であることを確認します。
もし異なっている場合は、直接修正します。
範囲の確認ができたら、[先頭行をテーブルの見出しとして使用する]にチェックした状態で確定します。
これだけで、元の表をテーブルに変換できます。
テーブル内にカーソルを移動すると、[テーブルデザイン]タブが表示されます。
このタブから、デザインやテーブル名などを変更することができます。
以下では、テーブル名を「タスク管理表」にしています。
テーブルについては、以下で詳しく解説しています。
＞テーブルの活用方法
次は、以下のようなボタンを作成します。
このボタンは「スライサー」と呼ばれ、テーブルの機能に備わっています。
まずは、対象のテーブル内にカーソルを移動させ、[テーブルデザイン]タブから[スライサーの挿入]を選択します。
そうすると、以下のようにテーブルに存在する見出しの一覧が表示されます。
この一覧から、絞り込みしたい項目にチェックし、確定します。
こちらでは、「進捗」のみにチェックしていますが、複数の項目をチェックすることもできます。
確定すると、以下のようにボタン（スライサー）が挿入されます。
後は、好みの位置に移動して、表示方法を少し修正するだけです。
こちらでは、１行目に行を挿入し、スライサーを移動しています。
このままだと、１つのボタンしか表示されないので、列方向に表示する件数を増やします。
スライサー（ボタン以外）を選択すると、[スライサー]タブが表示されます。
そのタブから、ボタンを表示する列数を変更することができます。
こちらでは、列数を「３」にしています。
これでボタン（スライサー）の完成です。
表示したい項目のボタンを押すだけで、以下のように、瞬時に絞り込みされます。
絞り込みを解除するには、左上の「×」のアイコンをクリックします。
表の下の方に移動したときにも、先頭行のボタンと見出しを常に表示したい場合は、表示したい行の下の行全体（３行目）を選択して、[表示]タブから[ウィンドウ枠の固定]の[ウィンドウ枠の固定]を選択します。
以上の手順で、先頭の２行が常に表示されるようになります。
Excelでは、VLOOKUP関数などで空白セルの値を抽出すると、空白ではなく「0」として抽出されてしまいます。
こちらでは、空白セルの値を「0」ではなく空白として抽出する方法について解説していきます。
空白セルの値が空白ではなく「0」として抽出されてしまう原因は、Excelが空白セルを数値の「0」として扱ってしまう仕様のためです。
そのため、「0」ではなく空白のまま抽出するには、「数値」ではなく「文字列」として認識させる必要があります。
空白セルを「数値」ではなく「文字列」として認識させるために最も有効な方法は、「空の文字を加える」という方法です。
例えば、以下の数式で考えてみます。
=VLOOKUP(B18,B3:C15,2,FALSE)この数式の結果を文字列にするためには、以下のように「空の文字」を直接加えます。
=VLOOKUP(B18,B3:C15,2,FALSE)&""たったこれだけで、空白セルの場合は「空白」のまま抽出することができます。
「空の文字を加える」という方法は単純ですが、注意点があります。
それは、数値を抽出する際に「文字列に変換されてしまう」という点です。
そのため、もし数値を抽出する可能性のある数式の場合は、「空の文字を加える」という方法は使えません。
そのような場合は「IF関数」を活用して、以下のように分岐させると良いです。
=IF(VLOOKUP(B18,B3:C15,2,FALSE)="","",VLOOKUP(B18,B3:C15,2,FALSE))こちらの数式では、VLOOKUP関数の結果が「空白」の場合に「空の文字」を意図的に抽出しています。
ただ、VLOOKUP関数の同じ数式が２つ存在しているため、処理に無駄があります。
もし「LET関数」に対応している環境の場合は、以下のように「LET関数」を活用するとよいです。
=LET(x,VLOOKUP(B18,B3:C15,2,FALSE),IF(x="","",x))LET関数については、以下で詳しく解説しています。
＞LET関数の使い方と活用例
以下の表は、商品の価格を管理したものです。
最新の価格を末尾に追加することで、各商品の価格の推移を管理しています。
この表から、数式だけで各商品の最新価格を抽出する方法について解説していきます。
※こちらで作成したファイルは、記事の最後にて配布しています。
今回紹介する数式を使えば、今後の更新も一切不要です。
数式を入力する前に、表のデータの増減に簡単に対応するため、対象の表を「テーブル」に変換します。
まずは、表の中にカーソルを移動させ、[挿入]タブから[テーブル]を選択します。
指定されている範囲が正しいことを確認し、表の先頭行が見出し行のため、[先頭行をテーブルの見出しとして使用する]にチェックした状態で確定します。
テーブルに変換すると、以下のようにデザインが変わってしまいます。
デザインは、[テーブルデザイン]タブの[テーブルスタイル]から簡単に変更することができます。
こちらでは、元の書式を生かすため、[テーブルスタイル]を[なし]にします。
フィルターボタンが不要な場合は、フィルターボタンのチェックを外しておきます。
次に、テーブル名を変更します。
テーブル名は[テーブルデザイン]タブから変更できます。
こちらでは「価格一覧」にしています。
以上の手順で、テーブルの完成です。
次は、数式の入力です。
最新価格を表示する用の表の見出しは、数式ではなく直接用意しておきます。
見出しの用意ができたら、数式を入力していきます。
まずは、テーブルの商品名から重複を除いた一覧を数式で作成します。
指定した範囲から重複を除外するには、UNIQUE関数が便利です。
UNIQUE関数を活用する場合、以下のように入力します。
=UNIQUE(価格一覧[商品名])
数式を入力することで、以下のように商品名一覧を抽出できます。
テーブルの範囲は、構造化参照という特殊な参照方法で指定できます。
構造化参照については、以下で詳しく解説しています。
＞構造化参照とは
次は、各商品の最新価格を抽出します。
まずは、先頭の「消しゴム」の最新価格のみを抽出していきます。
商品名が指定した値と一致する行の末尾の価格のみを抽出する場合は、XLOOKUP関数が便利です。
XLOOKUP関数を活用する場合、以下のように入力します。
=XLOOKUP(F3,価格一覧[商品名],価格一覧[価格],,,-1)
// F3：検索値
// 価格一覧[商品名]：検索範囲
// 価格一覧[価格]：抽出範囲
// （省略）：見つからない場合
// （省略）：一致モード（省略時は完全一致）
// -1：検索モード（「-1」は末尾から先頭へ検索）
数式を入力することで、以下のように最新価格を抽出できます。
他の行の商品も一括で抽出する場合は、XLOOKUP関数の検索値に商品名一覧の全体を指定します。
商品名一覧は１つの数式により展開されています。
そのような数式で展開されている範囲は、数式が入力されているセルに「#」を加えることで、全体を指定できます。
=XLOOKUP(F3#,価格一覧[商品名],価格一覧[価格],,,-1)
数式を入力することで、以下のように各商品の最新価格を一括で抽出できます。
以上の手順で完成です。
テーブルにデータを追加すると、自動でテーブルの範囲が拡張されます。
そのため、数式を更新する必要がなく、継続的に活用することができます。
▼サンプルファイル▼
離れている複数のデータから一部のデータをコピー&ペーストで抽出する場合、よくある方法だと以下の方法があります。
・１件ずつ抽出
・複数選択して一括で抽出
今回はこれらの方法ではなく、１件ずつコピーし、ペーストを一括で行う方法について紹介します。
この方法の場合、コピーしたデータを一覧で確認しながら、必要に応じて、誤ってコピーしたデータを除外し、残ったデータを一括でペーストすることができます。
今回は、Officeソフトに備わっている「クリップボード」を活用します。
実は、コピーした内容はOfficeソフトに備わっているクリップボードに記録されます。
クリップボードは、[ホーム]タブの[クリップボード]グループの右下にある矢印マークをクリックすることで表示できます。
クリップボードに何かしらデータが表示されている場合は、[すべてクリア]を選択します。
次に、クリップボードを表示している状態でセルをコピーすると、以下のように、コピーされたセルの情報がクリップボードに記録されていくのが確認できます。
最新のコピー情報が、クリップボードの一番上に追加されていきます。
必要なデータをクリップボードに記録することができたら、貼り付け先のセルを選択して、[すべて貼り付け]を選択します。
この手順で、記録されたデータを一括で貼り付けることができます。
貼り付けられる順番はコピーした順番になるため、クリップボードに表示されている順番とは逆になります。
Officeのクリップボードには、最大２４件まで記録することができます。
誤ってコピーしてしまった場合は、クリップボードの対象データの上にカーソルを移動すると下向き矢印のアイコンが表示されるため、そのアイコンをクリックしたときに表示されるメニューから削除することができます。
[貼り付け]を選択した場合は、単体のデータが貼り付けられます。
Officeのクリップボードには、セルの情報以外にもコピーした情報が記録されます。
コピー元は、左側に表示されるアイコンで確認することができます。
ただ、文字列以外（画像や図形など）の情報を記録した場合は、Excelでは[すべて貼り付け]が使えなくなるので注意してください。
VLOOKUP関数は有名ですが、「近似一致」を使ったことがないという方は意外と多いです。
ということで、今回は「近似一致」の使い方について解説していきます。
以下は、距離と交通費の関係を表した表から、指定した距離の交通費を抽出する数式になります。
=VLOOKUP(E3,B3:C9,2,FALSE)
上記の数式は、「完全一致」を指定しています。
この指定の場合、一致する距離がないときはエラーになってしまいます。
では、以下の「近似一致」にした数式にしてみます。
=VLOOKUP(E3,B3:C9,2,TRUE)この数式の場合は、エラーではなく値が抽出されます。
「完全一致」では、検索値と同じ値が見つからない場合はエラーになってしまいます。
しかし、「近似一致」ではエラーになりません。
「近似一致」は、検索値以下の値で一番大きい値のものを基準に抽出するという設定になります。
そのため以下の場合は、検索値「７」なので、「７以下で一番大きいものを基準に抽出する」ということになり、７以下で一番大きい値は「５」になるため、その行の値（「２００」）が抽出されました。
検索値を「２３」に変更してみると、それ以下で一番大きい値は「２０」になるため、「３５０」が抽出されます。
これが「近似一致」です。
ただ「近似一致」を使う際は、次のことに気を付ける必要があります。
VLOOKUP関数の「近似一致」を活用する際は、検索項目の値が「必ず昇順（小さい順）」である必要があります。
正しく並べられていない場合は、以下のように正しく抽出できないことがあります。
昇順でない項目を基準に「近似一致」で抽出したい場合は、XLOOKUP関数が便利です。
XLOOKUP関数は、Excel2021以降のバージョンで使用できます。
XLOOKUP関数で指定する場合は、以下の数式になります。
=XLOOKUP(E3,B3:B9,C3:C9,,-1)
XLOOKUP関数については、以下の記事にて詳しく解説しています。
＞VLOOKUP / INDEX・MATCH / XLOOKUPの使い方と違い
以下の表は、ある商品に対してのお問い合わせ一覧になります。
このお問い合わせ内容の中には、「大文字アルファベット３文字 + 「-」 + 数字４文字」の構成の商品コードが含まれるものがあります。
今回は、その決まった構成の文字列（商品コード）を以下のように瞬時に抽出する数式の作成方法について紹介していきます。
文字列の抽出には、REGEXEXTRACT関数が便利です。
この関数では、正規表現（文字列の構成を指定する記述方法）で抽出対象の文字列を指定することができ、その指定した構成の文字列を抽出することができます。
まずは、以下の構成を正規表現で表現していきます。
「大文字アルファベット３文字 + 「-」 + 数字４文字」これを正規表現で表現すると、以下のようになります。
[A-Z]{3}-\d{4}
// [A-Z]：A～Zの大文字アルファベット
// {3}：3文字
// -：-（ハイフン）
// \d：数字
// {4}：4文字正規表現の記述方法については、こちらでは省略します。
正規表現の記述が苦手な場合は、ChatGPTなどのAIにサポートしてもらうのもよいです。
ただし、実際に運用する場合は、使用する正規表現の最低限の理解はした方がよいです。
求めた正規表現を用いて、以下のような数式を入力します。
=REGEXEXTRACT(B2,"[A-Z]{3}-\d{4}",1)
// B2：抽出元の文字列
// "[A-Z]{3}-\d{4}"：抽出対象の正規表現
// 1：該当する文字列をすべて抽出
数式を入力して確定することで、以下のように該当する文字列を抽出することができます。
他の行は、入力した数式をコピーすることで反映できます。
ただし、正規表現に該当する文字列が存在しない場合、以下のようにエラーになってしまいます。
そのため、複数の範囲で展開する場合は、以下のようにIFERROR関数を組み合わせてエラー対策をするとよいです。
=IFERROR(REGEXEXTRACT(B2,"[A-Z]{3}-\d{4}",1),"")
// エラーの場合は空を表示
上記の数式を入力して全体にコピーすることで、以下のように各行に対して該当する文字列を抽出することができます。
以下は、１５日を締め日とした場合の各日付に対する対象月（請求月や支払月など）を求めた表になります。
このように、日付に対応した対象月を数式で求める方法について解説してきます。
締め日を考慮した対象月を求めるには、まずは基準となる日付から「締め日を引いた日付」を求めます。
以下は、１５日を締め日とした場合の数式です。
=B3-15
// B3：基準の日付
この求めた日付の翌月が対象月になります。
そのため次は、EDATE関数を用いて「翌月の日付」を求めます。
=EDATE(B3-15,1)
// 1か月後の日付
// 求めた日付が実在しない場合は月末日を返す（3/31→4/30）
最後に、MONTH関数を用いて求めた日付から「月」のみを抽出します。
=MONTH(EDATE(B3-15,1))
※「月」ではなく日付が表示される場合は、表示形式を「標準」に戻してください。
入力した数式を他の行にもコピーすることで、簡単に「締め日を考慮した対象月」を求めることができます。
