Excel｜データ入力効率化

Excel作業時間を大幅短縮！プルダウンリストの自動更新、条件付き書式による視覚化、テーブル機能を活用したデータ管理など、知っておくべきデータ入力効率化テクニックを実例付きで解説。初心者から中級者まで必見の内容です。

完了したタスクに自動で色を付ける方法

日々の仕事の進捗管理の中でExcelを使用している方は多いと思います。期限が迫ってきたらセルの色が変わる、TO DOリストを作成する、条件付き書式等を使用して、様々なテクニックをご紹介します。

上記は完成図です。納期管理表ですが、状況の所にプルダウンリストがあって、下から引き出される形となっています。プルダウンリストの【発送完了】を選択します。すると【発送完了】したものは自動でグレーになります。

K7〜K12のセルをドラックします。
【データ】タブをクリックします。
【データの入力規則】の▼をクリックします。
【データの入力規則】をクリックします。

すると【データの入力規則】ダイアルボックスが表示されます。
【入力値の種類】は、【全ての値▼】から【リスト】を選びます。

【元の値】は【発送完了,未確認】とドラックして選択します。
【OK】ボタンを押します。

すると、プルダウンリストが作成され、【状況】の欄をクリックすると、【発送完了】か【未確認】かを選ぶ事が可能になります。

発送完了したら、表の中の列の背景をグレーアウト

上記の様に表をドラックし、【ホーム】タブをクリックします。
【条件付き書式の▼】から【新しいルール】をクリックします。

すると【新しい書式ルール】ダイアルログが出てきます。
【数式を使用して書式設定するセルを決定】をクリックします。

数式を使用して、【書式設定するセルを決定】を選んで数式を「=$K=”発送完了”」と入力します。
【書式】をクリックします。

【書式】をクリックして選択します。

すると、【セルの書式設定】ダイアルボックスが表示されま
す。【塗りつぶし】タブをクリックします。色をグレーにします。
【OK】を押します。

すると発送完了したものは自動でグレーになります。※タスク管理や、生産管理にお役立てください。

入力済みデータを非表示

作った資料を会議などで他の人に見せる時に、入力している一部のデータを見せたくないという場合など有りませんか？今回は、Excelで作った売上管理や等、セルに入力した、一部のデータだけを非表示にする方法をご紹介します。

上記の支店別売上表があって、6月分の売上は確定じゃないのでまだ見せたくないという場合。6月分の売上データだけを非表示にすることができます。削除では有りません。

範囲選択して、キーボードの【Ctrl】+【1】を押します。

すると、【セルの書式設定】ダイアルボックスが表示されます。
1.【表示形式】タブをクリックします。
2.【ユーザー定義】をクリックします。
3.【種類】に【;;】セミコロンを二つ入力します。
【OK】ボタンを押します。

選択されていたセルの入力データが表示されません。
データは非表示になっているだけで、非表示になっている所をアクティブセルにして数式バーで見てみると、データが入力されている事がわかります。
データをこの状態で非表示にしたら印刷する時も非表示になります。

表示形式　電話番号　郵便番号

Excel には、郵便番号、電話番号の特別な番号書式が用意されています。目的に合わない場合は、独自のユーザー定義の表示形式を作成して適用することができます。

電話番号ユーザー定義

Excel には、郵便番号、電話番号の特別な番号書式が用意されています。目的に合わない場合は、独自のユーザー定義の表示形式を作成して適用することができます。

この様な電話番号表があります。
ユーザー定義で表示形式を変えます。

範囲選択して、キーボードの【Ctrl】+【1】を押します。

【セルの書式設定】ダイアルログが出ます。
1.【表示形式】タブをクリックします。
2.【その他】をクリックします。
3.【種類】を電話番号にします。
【OK】ボタンを押します。

この様に、電話番号の表示形式に変更されます。

郵便番号ユーザー定義

範囲選択して、キーボードの【Ctrl】+【1】を押します。

【セルの書式設定】ダイアルログが出ます。
1.【表示形式】タブをクリックします。
2.【その他】をクリックします。
3.【種類】を郵便番号にします。
【OK】ボタンを押します。

この様に、郵便番号の表示形式に変更されます。

プルダウンリストを修正不要にする

Excelでは、いくつかの決まった項目の中から選んで表に入力する事が有ります。。そんな時、プルダウンリストを作成する方も多いと思います。ここではテーブル機能を使いドロップダウンリストの修正を不要にします。

表をテーブル化する

【氏名】の欄を選択し
キーボードの【Ctrl】+【T】をクリックします。

すると、【テーブルの作成】ダイアルボックスが表示されます。
【先頭行をテーブルの見出しとして使用する】にチェックを入れ,【OK】ボタンを押します。

すると、【氏名】の欄がテーブル化します。

テーブルに名前を定義

①氏名の名前の部分を選択します。
②【テーブルデザイン】タブの【テーブル名】に【リスト】と入力します。
③.キーボードの【Enter】キーを押します。

データの入力規則を定義

【氏名】の範囲を選択し【データ】タブをクリックして、【データの入力規則】を選択してクリックします。

元の値は【氏名】の表内を選択して【OK】ボタンを押します。

すると、プルダウンリストから【氏名の人員】が表示されます。

次に新しい人員【山本係長】を追加します。

すると、【プルダウンリスト】も連携して、【山本係長】が追加されます。

Excelでプルダウンリストが参照する表に項目が追加された時に、プルダウンリスト上での項目も自動で追加される方法をご紹介しました。

プルダウンリストを作成する際、テーブル化して名前をつけておくと、後々の管理がとても楽になります。

フォーマットの異なるシートのデータを集計するテク

支店別、部署別といったカテゴリーに、別シートに売上表などの集計表を作成があります。作成した後に、すべてのシートのデータを1つにまとめて集計したいと思ったことはありませんか？

実際の業務では、別々のシートにあるデータを1つに集計したい場合があります。今回は、「統合」機能を使います。

異なるデータの集計

【東京支店】【福岡支店】【宮城支店】の3店舗の売上を、れ別々のシートに記録している業務があります。この3店舗の売上を集計して「集計」シートまとめます。それぞれのシートは、商品の項目数が異なるなど、フォーマットが異なっています。

同じフォーマットの表を使っている時には、「3-D集計」という機能を使って、同じ位置にあるセルの値を集計することができます。

実際の業務では、フォーマットや形式にかかわらず、別々のシートにあるデータを1つに集計したい場合が発生します。紹介する「統合」機能を使うと、データの位置や項目数などが異なっていても、複数シートのデータを1つのシートに統合することができます。

【東京支店】【福岡支店】【宮城支店】の3店舗の売上日報を、それぞれ別々のシートに記録している業務があるとします。例として、この3店舗の売上データを集計して「集計」シートにまとめます。

【H3】セルをクリックして、【データ】タブの【統合】をクリックして、選択します。

するとこの様なボックスが出てきます。
集計方法は【合計】
統合の基準は上端行・【左端業】にもチェックしてください。

統合元参照範囲は【東京支店】の範囲を選択してから【追加】ボタンを押します。

次に【福岡支店】を追加して、【宮城支店】を追加してから【OK】ボタンを押します。

すると、【A3】～【K11】に複数のシートのデータを簡単に集計できました。

今回は、「統合」機能を使って、データの位置や項目数などが異なる複数シートのデータを1つのシートに統合する方法について解説しました。

シート数の多いブックで集計を行う際には、たいへん役立つ機能です。ぜひ試してみてください。

修正箇所を把握するのに便利

データが修正されたセルに背景色が付くようにしておくと、管理者は色のついたセルのみを確認すると良いです。
ここでは、その手順をご紹介します。

このような、【社員名簿】があります。

【Ctrl】+マウスでドラックで、新しいシートの作成をして、【編集中】と名前の変更をします。

【編集中】のシートで、表内を選択して、【ホーム】タブをクリックして【条件付き書式▼】から【新しいルール】をクリックして選択します。

【新しいルール】ダイアルボックスが表示されます。
【指定の値を含むセルだけを書式設定】から【次の値▼】から【次の値に等しくない】を選択します。

範囲は、【社員名簿】シートのデータの【A1】セル選択します。すると、AIセルが破線になります。

選択したA1が【絶対参照】になっているので【F4】キーを3回押して、押して、相対参照にし、【=社員名簿!A2】にして【書式】をクリックします。

すると、【セルの書式設定】ダイアルボックスが表示されます。
【塗りつぶし】タブから、背景色を【黄色】にして
【OK】ボタンを押します。

すると、この様に、【編集中】シートで修正されたセルに背景色が付きます。

スライサー

テーブルでも［スライサー］が使用できるのはご存じですか？スライサーを使った絞り込みを行うことができます。ここでは、スライサーを挿入して、表を視覚的に表す方法をご紹介します。

表のテーブル化

このような備品管理表があります。

表の何処かをアクティブセルにします。
①【キーボードの【Ctrl】+【T】を押します。

テーブルに作成しておくと、シート内のデータの並べ替え、フィルター、書式設定を簡単に行えるようになります。

すると、【テーブルの作成】ダイアルボックスが出てきます。
【先頭行をテーブルの見出しとして使用する】にチェックを入れた状態でOKボタンを押します。

すると、このように、表がテーブル化します。表の中の何処かをクリックすると【テーブルデザイン】タブが
表示されます。

【テーブルデザイン】タブから、【スライサーの挿入】をクリックします。

すると【スライサーの挿入】ダイアルボックスが挿入されるので、今回は、【日付】・【部署】・【商品】にチェックを入れ【OK】ボタンを押します。

すると、【スライサー】がこのように表示されます。

【スライサー】の位置をわかりやすく調整しておきましょう。

このように、日付の【9/1】をクリックすると、【備品管理表】の【9/1】の日付が抽出されます。

キーボードの【Ctrl】キーを押しながらクリックすると、複数の項目を選択する事が出来ます。

上下隣り合っている項目であるなら、マウスでドラックすると複数選択する事が出来ます。

このように、スライサーを使った絞り込みを行うことができます。ここでは、スライサーを挿入して、表を視覚的に表すことができます。

スライサーの削除

このように、【フィルターのクリア】をクリックすると、スライサーの削除ができます。

画像からデータにする

Excelには画像の設置だけでなく、その画像から文字を読み込んでデータに変換する機能が搭載されてます。
データの加工において、画像から文字化して、表にすることも出来るのでとても便利です。

Excelデータ表のテキストとして変換

このような、日経平均株価の画像があります。画像から文字化して、表にする方法をご紹介します。

画像データ選択

【データ】タブをクリックします。
【画像から▼】をクリックして【ファイルから画像】をクリックします。

すると、右側の作業ウィンドウで、画像からのデータを分析し始めます。

【データを挿入】をクリックします。

実際にExcelに入れ込んだのはこちらのデータ内容になります。※、完全に読み取れない事もありますので確認してカスタマイズしてください。

XLOOKUPで二段階プルダウンリスト

詳細は下記の記事をご覧ください。

複数シート一括コピペで作業効率爆上げ！

まとめ