【エクセル】「フィルター」機能と「小計」機能を使えば大量のデータも怖くない!

売上管理の月表から顧客のデータをチェックする際は、「フィルター」機能でデータを抽出するのがオススメす。多くのデータから簡単に絞り込むことができます。また、顧客ごとに売上金額を集計したい場合は、「小計」機能が便利です。SUM関数を使わずに自動で計算できます。あらかじめ顧客名で並べ替えておくのがポイントです。

 

「フィルター」機能でデータを抽出する

「フィルター」機能を使ってデータを抽出するには、フィルターボタンを表示します。

↑表内のいずれかのセルを選択します

 

↑「データ」タブで「フィルター」ボタンをクリックします

 

「フィルター」機能がオンになり、表の項目に「フィルター」ボタンが表示されます。

↑抽出したい項目の「フィルター」ボタンをクリックし、メニューの「(すべて選択)」のチェックを外してから、顧客名にチェックを入れます。最後に「OK」ボタンをクリックします

 

指定した顧客のデータが抽出され、「フィルター」ボタンが抽出中を示すマークに変わります。

↑指定した顧客のデータが絞り込まれます

 

抽出を解除する場合は、「データ」タブで「クリア」ボタンをクリックします。

↑「データ」タブで「クリア」ボタンをクリックすると、抽出が解除されます

 

元の表に戻ります。「フィルター」ボタンを消す場合は、「データ」タブ → 「フィルター」ボタンをクリックして「フィルター」機能をオフにします。

 

顧客ごとに売上金額を集計する

顧客ごとに売上金額を集計する際は、「小計」機能を使います。あらかじめ顧客名で表を並べ替えておきます。また、月表はシートコピーし、コピー後のシートで集計しましょう。

↑シートコピーした月表で、並べ替えたい項目のセルを選択します(ここではB列「顧客コード」のセル)

 

↑「データ」タブで「昇順」ボタンをクリックします

 

データが顧客コード順に並べ替わります。

↑データが並べ替わります

 

次に、「小計」機能を使って売上金額を集計します。

↑「データ」タブで「小計」ボタンをクリックします

 

「集計の設定」画面が表示されます。

↑「グループの基準」で集計の基準にする項目名(ここでは「顧客コード」)、「集計の方法」で「合計」、「集計するフィールド」で「売上金額」にチェックを入れ、「OK」ボタンをクリックします

 

顧客コードごとの売上金額が集計され、アウトラインで表示されます。

↑顧客コードごとに売上金額が集計されます

 

アウトラインを解除する場合は、「データ」タブ → 「グループ解除」の「▼」ボタン → 「アウトラインのクリア」をクリックします。

 

「フィルター」機能や「小計」機能を使うと、手間取るデータの抽出や小計が簡単にできます。大量のデータを使うビジネスで必須のテクニックです。覚えておきましょう。

【エクセル時短術】「重複入力」を回避すれば凡ミスが激減する

リストの入力でよく起きるのが重複入力でしょう。入力したあと重複をチェックすることもできますが、入力する際「入力済み」の警告を表示できれば効率的ですよね。この自動警告は、「データの入力規則」を使い、入力済みのデータと同じものを入力しようとしたら表示します。データの重複チェックは、COUNTIF関数を使います。

 

入力済みデータと同じものを入力する際にエラーを表示する

入力しようとするデータが入力済みかどうかは、COUNTIF関数でチェックします。計算結果が2以上の場合は同じデータがあり、1の場合はデータが1つだけです。「データの入力規則」で1の場合だけ入力を許可し、それ以外では入力を制限します。この際、エラーの原因がわかるメッセージを表示させるのがポイントです。

↑ 入力をチェックする列を選択しします(ここではA列)

 

↑「データ」タブの「データの入力規則」ボタンをクリックします

 

「データの入力規則」画面が表示されます。

↑ 「設定」タブの「入力値の種類」欄で「ユーザー設定」を選択し、「数式」欄に「=COUNTIF(A:A,A1)=1」を入力します。「A:A」はA列を示します

 

↑「エラーメッセージ」タブに切り替え、「タイトル」欄と「エラーメッセージ」欄に入力し、「OK」ボタンをクリックします

 

A列のセルに「データの入力規則」が設定されます。同じデータを入力しようとすると、エラーメッセージが表示されます。

↑ 入力済みのデータと同じものを入力使用とすると、エラーが表示されます

 

重複入力を防ぐワザはいかがでしょうか。リスト入力では欠かせないテクニックです。覚えておきましょう。

エクセルの便利オプション「音声読み上げ」使ってる? データの入力チェックがはかどる!

データの入力にはミスがつきものです。チェックはどうしていますか? 手の空いた人がいれば読み合わせができますが、1人で原本と照合するのは心もとないですね。特に桁数の多い数値は不安です。そんなときは、エクセルの「音声読み上げ」機能を使えば、データが自動で読み上げられ、入力チェックがはかどります。この「音声読み上げ」機能はオプションなので、クイックアクセルツールバーに追加して利用します。

 

「音声読み上げ」機能を追加する

「音声読み上げ」機能を使うには、クイックアクセスツールバーに追加します。

↑「クイックアクセルツールバーのユーザー設定」ボタンをクリックし、メニューから「その他のコマンド」を選択します

 

「Excelのオプション」画面が表示されます。

↑「コマンドの選択」で「すべてのコマンド」を選択し、下の一覧から「セルの読み上げ」を選択したら「追加」ボタンをクリックします。右欄で「セルの読み上げ」を確認し、「OK」ボタンをクリックします

 

画面左上のクイックアクセスツールバーに「音声読み上げ」ボタンが追加されます。

↑「音声読み上げ」ボタンを追加されます

 

データを音声で読み上げる

読み上げしたいデータを選択してから「音声読み上げ」ボタンをクリックします。

↑読み上げしたいデータを選択します

 

↑「音声読み上げ」ボタンをクリックします

 

データが読み上げられます。中断する場合はEscキーを押します。

 

音声読み上げはデータの入力チェックに便利です。特に在宅ワークでは必須のツール。ぜひ利用してみましょう。

【エクセル】同じような表、まだ画面切り替えてみてるの? そんなときは「条件付き書式」を使うべし!

エクセルで表をコピーして使っているとき、どのデータを更新したか忘れてしまった……といったことはないでしょうか。そんなときは「条件付き書式」を使って、新旧の表を比較してみましょう。データが異なるセルを明示することができます。同じ表を別々に更新したときも異なるデータをチェックできます。

 

2つのセルを比較しデータに相違があるかをチェックする

新旧の商品リストのセルを比較してデータに相違があるかをチェックするには、条件付き書式を使います。条件では、セルA=セルB=FALSE(セルAとセルBが同じでない場合)、と指定します。

↑比較したいセルを選択します(ここでは「商品リスト_0801」シートの商品コード)

 

↑「ホーム」タブで「条件付き書式」ボタンをクリックし、メニューから「新しいルール」を選択します

 

「新しい書式ルール」画面が表示されます。

↑「数式を使用して、書式設定するセルを決定」を選択し、条件欄に「=A2=商品リスト_0730!A2=FALSE」を入力し、「書式」ボタンをクリックします。ここでは、比較したいセルを「書品リスト_0730」シートのセルA2とします

 

=A2=商品リスト_0730!A2=FALSE

 

上記に数式により、セルA2と、「商品リスト_0730」シートのセルA2が等しいか調べ、等しくない(FALSE)の場合は、指定した書式で表示します。
「セルの書式設定」画面が表示されます。

↑「塗りつぶし」タブをクリックし、背景色を選択し、「OK」ボタンをクリックします

 

「新しい書式ルール」画面に戻ります。

↑指定した書式のプレビューが表示されます。「OK」ボタンをクリックします

 

データが異なるセルに色が付きます。

↑データが異なるセルに色が付きます

 

2つの表でデータをチェックするワザはいかがでしょうか。条件付き書式の条件で、数式に「=」を使う方法はデータチェックに有効です。ぜひ覚えましょう。

エクセルでシフト表を作るには? 「ガントチャート」作成手順をイチから解説

エクセルで店員の勤務表などのシフト表を作成する際、ガントチャートがあれば、手薄な時間帯を一発で発見できて便利です。このガントチャートは、積み上げ横棒グラフを使えば簡単に作成できます。あるいは、条件付き書式を使って、就業時間帯のセルを塗りつぶしてガントチャートを作成することもできます。今回は、ガントチャートを作成するワザを2つ解説します。

↑始業時の帯を透明にし、就業時間を積み上げた横棒グラフで作成したガントチャート

 

↑条件付き書式で就業時間のセルを塗りつぶして作成したガントチャート

 

「積み上げ横棒グラフ」でガントチャートを作る

積み上げ横棒グラフでガントチャートを作るには、就業時間を計算した表を作ります。縦に並ぶ氏名が表と逆になるので、軸を反転させます。就業時間帯を横軸に設定し、始業時のデータ系列を透明(「塗りつぶしなし」と「線なし」)に指定します。

↑氏名、始業時、就業時間の範囲を選択します。就業時間はCtrlキー+ドラッグで範囲を追加します。就業時間のセルD3は、終業時のセルC3から始業時のセルB3を引いています(=C3-B3)

 

↑「挿入」タブで「縦棒/横棒グラフの挿入」ボタンをクリックし、メニューから「積み上げ横棒」を選択します

 

積み上げ横棒グラフが作成されます。グラフタイトルは、セルA1の日付(曜日付き)のデータを参照します。

↑「グラフタイトル」をクリックしてから、数式バーに「=」を入力し、セルA1をクリックして選択しEnterキーを押します

 

グラフタイトルに日付と曜日が表示されます。次に縦に並んだ氏名を表の並びに揃えます。

↑氏名をダブルクリックします

 

「軸の書式設定」画面が表示されます。

↑「軸を反転する」にチェックを入れます

 

氏名の並びが表と同じになります。次は、就業時間を指定します(ここでは8~20時)。

↑横軸をダブルクリックします

 

「軸の書式設定」画面の「軸のオプション」が表示されます。

↑「最小値」に「8」を、最大値」に「20」、「単位」の「主」に「1」を入力します

 

横軸に時刻が表示されます。

↑凡例を選択しDeleteキーを押します。次に、始業時のデータ系列(青帯)をクリックして選択します

 

凡例が削除されます。最後に、選択した始業時の青帯を透明に変えます。

↑「書式」タブで「図形の塗りつぶし」の「▼」ボタンをクリックし、「塗りつぶしなし」を選択します

 

↑「書式」タブで「図形の枠線」の「▼」ボタンをクリックし、「枠線なし」を選択します

 

始業時の青帯が透明になり、ガントチャートが作成されます。

↑ガントチャートが作成されます

条件付き書式でガントチャートを作る

条件付き書式でガントチャートを作るには、勤務時間帯のセルに色を塗ります。条件には、AND関数を使って、「始業時」以降で「終業時-1」以前のセルを指定します。上下の罫線に白色を指定して時間帯を切り離すのがポイントです。

↑ガントチャートを作成する範囲を選択します

 

↑「ホーム」タブで「条件付き書式」ボタンをクリックし、メニューから「新しいルール」を選択します

 

「新しい書式ルール」画面が表示されます。

↑「数式を使用して、書式設定するセルを決定」を選択し、条件欄に条件「AND(E$2>=$B3,E$2<=$C3-1)=TRUE 」を半角で入力し、「書式」ボタンをクリックします

 

この場合、時刻のセルE2が始業時のセルB3以上で、しかもセルE2が「終業時-1」以下を満たす場合、という条件をAND関数で指定して満たす(TRUE)ときと指定しています。時間帯の2行目と、始業時の列B列、終業時の列C列は絶対参照で指定します。セルG3では、時刻のセルG2(10)が始業時のセルB3(10)以上になり、終業時のセルC3(16)-1が終業時のセルC3(16)以下になるので、指定した書式で表示されます。

 

「書式」ボタンをクリックすると、「セルの書式設定」画面が表示されます。

↑「塗りつぶし」タブをクリックし、背景色を選択します

 

↑「罫線」タブをクリックし、「色」で「白」を選択し、「罫線」で「上」と「下」をクリックします。最後に「OK」ボタンをクリックします

 

セルの書式が設定され、「新しい書式ルール」画面に戻ります。

↑プレビューを確認し、「OK」ボタンをクリックします

 

ガントチャートが表示されます。

↑勤務時間帯のセルに色が付きます

 

ガントチャート作成のワザはいかがでしょうか。ビジネスではよく使うので、いずれかをマスターしましょう。

【エクセル】残念な資料になってない? 円グラフの訴求力を高める3つのポイント

簡単にグラフを作成できるのがエクセルの優れたところですが、作成したまま資料として提案していませんか? 実はひと工夫加えるだけで、グラフが見やすくなり訴求度がアップします。例えば、円グラフ。項目名をグラフの近くに置けば、凡例の色とグラフを見比べる手間が省けます。加えて、割合(パーセント)も同時に表示したいところ。今回は、円グラフの訴求力を高めるポイントを3つご紹介します。

↑作成したばかり円グラフ。ここからひと工夫して、見やすく加工するのが訴求度アップのポイントです

 

【ポイント①】

項目名をグラフの近くに置く

項目名をグラフの近くに置くには、まず凡例を消します。項目名は、データラベルオプションで「分類名」と「パーセンテージ」をオンにします。項目名をグラフの外に置く場合は、「ラベルの位置」で「外部」を選択します。

↑凡例を選択し、Deleteキーを押します

 

凡例が消えます。次に、項目名をグラフに表示します。

↑「デザイン」タブで「グラフ要素を追加」ボタンをクリックし、「データラベル」 → 「その他のデータラベルオプション」をクリックします

 

「データラベルの書式設定」画面が表示されます。

↑「ラベルオプション」で「分類名」と「パーセンテージ」にチェックを入れ、「値」のチェックを外します。「ラベルの位置」で「外部」を選択します

 

グラフの外部に項目名とパーセントが表示されます。

↑グラフの外部に項目名とパーセントが表示されます

 

【ポイント②】

円グラフの項目を値の大きい順に並べる

値の大きい順に項目を並べる場合は、表を降順に並べ替えます。

↑並べ替える項目のセルを選択します(ここでは「売上高」のセルB3)

 

↑「ホーム」タブの「並べ替えとフィルター」ボタンをクリックし、「降順」を選択します

 

表が売上高の大きい順位並べ替わり、円グラフも変わります:。

↑表に合わせて円グラフの項目も並べ替わります

 

【ポイント③】

目立たせたい項目を切り出す

円グラフの項目を切り出すには、その項目だけを選択し、「データ要素の書式設定」画面の「要素の切り出し」で「20%」などと指定します。

↑切り出したい項目をクリックしたあと、もう一度クリックします

 

指定した項目だけが選択されます。

↑「書式」タブの「選択対象の書式設定」ボタンをクリックします

 

「データ要素の書式設定」画面が表示されます。

↑「要素の切り出し」で切り出し割合を指定します

 

指定した項目が切り出されます。

↑項目が切り出されます

 

円グラフのひと工夫ワザはいかがでしょうか。簡単に加工できるので、ぜひお試しください。

【エクセル】使えるとデキる人っぽい!! データ推移を「バタフライチャート」で見せるワザ

人口動態や売上高、会員数などのデータの推移を表すには、折れ線グラフや積み上げ棒グラフを使うことが多いですよね。しかし、「バタフライチャート」ならデータを左右に振り分けて2つの項目を比較し、データの推移を表現することができます。このバタフライチャートは横棒グラフから作成しますが、一方の数値をマイナスにするのがポイントです。

↑バタフライチャート。横棒を左右に振り分けてデータを比較できます

 

バタフライチャートを作成する

バタフライチャートを作成する前に、まずはシートをコピーし、一方の項目の数値をマイナスにします。グラフは、横棒グラフで作成します。左右の横棒を揃えるには、「系列の重なり」を「100%」に設定します。ラベルをグラフの左側に置く場合は「ラベルの位置」で「下端/左端」を指定し、横軸のマイナス数値は表示形式を変えてプラスにします。

↑1つの項目の数値をマイナスに変え、グラフにする範囲を選択します

 

↑「挿入」タブ → 「縦棒/横棒グラフの挿入」ボタンをクリックし、メニューから「集合横棒」を選択します

 

横棒グラフが作成されたら、まず横棒の位置を揃えます。

↑横棒をクリックして選択します

 

↑「書式」タブ → 「選択対象の書式設定」ボタンをクリックします

 

「データ系列の書式設定」画面が表示されます。

↑「系列の重なり」で「100%」を指定します

 

横棒の位置が揃います。次に、ラベルを左に移動します。

↑横棒の位置が揃います。次に、ラベルを選択します

 

↑「書式」タブ → 「選択対象の書式設定」ボタンをクリックします

 

「軸の書式設定」画面が表示されます。

↑「ラベル」をクリックし、「ラベルの位置」で「下端/左端」を選択します

 

ラベルがグラフ左端に移動します。最後に、横軸のマイナス数値をプラスに変えます。

↑ラベルがグラフ左端に移動します。次に、横軸を選択します

 

↑「書式」タブ → 「選択対象の書式設定」ボタンをクリックします

 

「軸の書式設定」画面が表示されます。

↑「表示形式」をクリックし、「表示形式コード」で「#,##0;#,##0」に修正し、「追加」ボタンをクリックします

 

マイナスの数値がプラスに変わります。

↑マイナス数値がプラスに変わります

 

バタフライチャートは男女別年代別人口グラフなどでよく目にします。作成方法をマスターしましょう。