Excel_18 Excel macOSX版での複合グラフ作成

今回は連載6回目の内容に関してmacOSX版への補足です。ちなみにWindows版でも同様の処理が可能です。

Excel_06　複合グラフ作成は焦ったら負け 2020/6/25

商品名と横浜店〜船橋店そして合計値のデータを選択したら、［挿入］＞［組合せグラフ］をクリックしても最終的に複合グラフは作成出来ません。

2021/09/09

※M1のmacOS Big Sur上のExcel 16.52ではWindowsと同様に［挿入］＞［組合せグラフ］で成出来ました。

商品名と横浜店〜船橋店そして合計値のデータを選択したら、［挿入］＞［おすすめグラフ］をクリックして複合グラフ（集合縦棒）を選択します。

2021/09/09

※M1のmacOS Big Sur上のExcel 16.52では［挿入］＞［おすすめグラフ］をクリックしても複合グラフ（集合縦棒）は選択できませんでした。

続いてグラフの合計値を示す折れ線グラフの任意の位置を右クリックすると表示されるコンテキストメニューから［データ系列の書式設定］を選びます。

ここで［第2軸］を選択すると右端に折れ線グラフの値として第2軸が表示されます。

次に、［グラフデザイン］の左端にある［グラフ要素を追加］にて［軸ラベル］＞［第2縦軸］を選択します。

続いて［グラフデザイン］の［グラフ要素を追加］にて［データテーブル］＞［凡例マーカーあり］を選択します。

最後に［グラフデザイン］の［グラフ要素を追加］にて［凡例］＞［なし］を選択します。

これは完成です。タイトルや軸ラベルの傾き修正は以下を参照して下さい。

Excel_16　Excel macOSX版での棒グラフ作成 2020/11/20

Office 356 Pro Plus

Excel_17 Excel macOSX版での円グラフ作成

今回は連載5回目の内容に関してmacOSX版への補足です。

Excel_05　円グラフ作成は項目設定に注意 2020/06/23

商品名と横浜店のデータを選択したら・・・

［挿入］＞［円］をクリックて一番左上の円を選択して円グラフを作成します。

グラフが表示されたら、［グラフのデザイン］＞［グラフ要素を追加］より［凡例］＞［なし］をクリックします。

続けて、［グラフのテザイン］＞［グラフ要素を追加］より［データラベル］＞［内部外側］をクリックします。

これで円グラフに、とりあえずの値が表示されました。

任意の値をControlキーを押しながらクリックすると出てくるメニューから［データラベルの書式設定］を選択して出て来たメニューの［ラベルオプション］から［分類名］［パーセント］［引き出し線を表示する］にチェックを入れてから［値］のチェックを外します。

あとはそれぞれのテキストブロックのサイズや位置を調整すれば完成です。

目立たせたいパイがアル場合は、そのパイに対してクリックを2度（ダブルクリックではありません）行うと個別に選択されるので、そのまま移動させれば完成です。

Windows版の場合は以下を参照

Excel_05　円グラフ作成は項目設定に注意 2020/06/23

Office 365 Pro Plus

Excel_16 Excel macOSX版での棒グラフ作成

今回は連載4回目の内容に関してmacOSX版への補足です。

Excel_04　棒グラフ作成の手順がすべてのグラフの基本 2020/06/22

商品名と横浜店〜船橋店のデータを選択したら、［挿入］＞［縦棒］をクリックて一番左上の集合縦棒を選択します。

グラフが表示されたら、［グラフのテザイン］＞［グラフ要素を追加］をクリックします。

［軸ラベル］＞［第1縦軸］を選択すると変な向きの軸ラベルが生成されますのでそのまま軸ラペルという文字をControlキーを押しながらクリックします。

Controlキーを押しながらクリックすると出てくるメニューから［軸ラベルの書式設定］を選択して出て来たメニューから［テキストボックス］を選択し［文字列の方向］から［垂直］を選びます。

続けて［グラフのデザイン］＞［グラフ要素を追加］をクリックし［データテーブル］＞［凡例マーカーなし］を選択すると・・・

完成です。

Windows版の場合は以下を参照

Excel_04　棒グラフ作成の手順がすべてのグラフの基本 2020/06/22

Office 365 Pro Plus

Excel_15 乗算よりも小数が発生する除算に注意

Excelには便利な関数が480以上も用意されていますが、用途に合わせてその都度使い分ければ良いだけです。例えば除算処理だけをとっても色々な処理結果があります。今回は気分転換として色々な除算について整理してみました。下はA列の値をB列で乗算、除算する方法と結果の違いを整理した表です。

03行目

=A3*B3

一般的な乗算の書式。

04行目

=SUM(A4*B4)

乗算を合計するという流れでの数式。ただし、単純な計算なら問題ありませんが、関数の組合せを行うとオカシナ結果になるのでお薦めしません。

05行目

=PRODUCT(A5,B5)

乗算用の関数PRODUCTを使って数式。

06行目

=A6/B6

一般的な除算の書式。

07行目

=SUM(A7/B7)

除算を合計するという流れでの数式。ただし、単純な計算なら問題ありませんが、関数の組合せを行うとオカシナ結果になるのでお薦めしません。

08行目

=QUOTIENT(A8,B8)

除算用の関数QUOTIENTを使って数式。ただし、小数点以下は切り捨てられます。

09行目

=ROUNDDOWN(A9/B9,2)

除算結果の小数値を指定桁数て切り捨てて表します。

10行目

=ROUNDUP(A10/B10,0)

除算結果の小数値を指定桁数て切り上げて表します。

11行目

=ROUND(A11/B11,2)

除算結果の小数値を指定桁数て四捨五入して表します。

12行目

=ROUNDDOWN(QUOTIENT(A12,B12),2)

除算用の関数QUOTIENTを用いた結果の小数値を指定桁数て切り捨てて表します。ただし、QUOTIENT関数が小数点以下は切り捨てるので意味がない処理になります。

13行目

=ROUNDUP(QUOTIENT(A13,B13),2)

除算用の関数QUOTIENTを用いた結果の小数値を指定桁数て切り上げて表します。ただし、QUOTIENT関数が小数点以下は切り捨てるので意味がない処理になります。

14行目

=ROUND(QUOTIENT(A14,B14),2)

除算用の関数QUOTIENTを用いた結果の小数値を指定桁数て四捨五入して表します。ただし、QUOTIENT関数が小数点以下は切り捨てるので意味がない処理になります。

結果数図のように赤で塗りつぶした部分は一般的な処理では意味が無いことが解ります。ちなみに、計算結果の桁数を指定した場合、ホームタグの［数値］

にある桁数調整は正しく機能しなくなることも覚えておきましょう。

Office 365 2020

Excel_14 表示形式とシリアル値（2）

3回目で触れた表示形式とシリアル値について実際のデータで再度整理してみました。

まず全員の勤務時間を計算します。これは退勤時間から出勤時間を引けばよいので、リストの最初にいる上杉謙信さんの勤務時間は =C4-B4 となります。処理結果をそのままフィルすれば全員の勤務時間が出ます。

次に全員の勤務私感の合計をオートSUMで出します。すると、5時間という値が出て来ました。明らかに変ですが、これは表示形式に問題が在るからです。通常24時間で時間はリセットされてしまいます。ですからデフォルトの設定で加算を行っても24時間を越える値は出て来ません。

そこで計算結果である D16 を選択し、ホームタグの［数値＞その他の番号書式］からヨーザー定義で h:mm を [h]:mm に変更します。これで正しい 53:00 が表示されます。

  [h]:mm 

次に給与を計算します。給与は時間に時給を掛けます =E1*D4 しかし、そのままだととんでもなく低い金額になってしまいます。これはExcelの時刻がシリアル値で管理されており、1日を1（1900年1月1日を1としてスタート）とカウントしているからです。そのため、時刻に時刻以外の値を掛ける場合は時刻を24倍する必要があります。よって=E1*D4*24 となります。

ところがフィルをするとまたまた飛んでない値になってしまいます。これはうっかり絶対参照の設定をしていなかったことが原因です。時給は全ての人が同じ位置（D1）を参照するからです。よって正確には、

=E$1*D4*24 
となります。これで全ての計算が完了しました。Ecelで忘れてはいけないのはシリアル値とデータの表示形式です。これさえ混乱しなければ意外とスムースに理解が深まると思います。

Office365 2020

Excel_13 SUMIF関数で同一条件の合計が簡単

条件を指定して数値を合計するSUMIF（サムイフ）関数は複雑なデータの中の欲しい情報だけをセレクトするのにとっても便利です。

サンプルデータは、前回のDATEDIFの続きです。K4を選択し、数式タグの［数学/三角］からSUMIFを選択します。

表示されるパレットは、

範囲　→　 検索対象とするセル範囲（絶対参照）

検索条件　→　セルを検索するための条件を数値や文字列

合計範囲　→　合計したい値が入力されているセル範囲を指定（絶対参照）

別表（B4:B15）を活用するので、B4:B15の中にH4が有れば、その行のF列の値を加算していきます。

=SUMIF(B$4:B$15,H4,F$4:F$15)

範囲と合計範囲は固定位置で計算するので絶対参照となります。

ちなみにデータ的には、

=SUMIF(C$4:C$15,J4,F$4:F$15)

としても同じですが、コード番号などで処理した方が現実的でしょう。

基準のデータが計算できたら、いつものようにフィルをして完成です。

Office365 2020

Excel_12 DATEDIF関数で日付計算が簡単です

日付の計算ならDATEDIF（デイトディフ）関数ですが、実はこの関数は何故か関数一覧に入って居ません。タイピングするしかない謎の関数です。ということでサンプルデータは前回のVLOOKUPの続きになります。

答えを先に書くと

=DATEDIF(D4,D$1,"D")

となります。

=DATEDIF（開始日,終了日,単位）となります。詳しくは以下を参照して下さい。なお、終了日は今日の日付になるので絶対参照です。

実際問題としてほとんどの場合はY、M、Dだけで事が足りしてしまうと思います。

yearのY、monthのM、dayのDです。

具体的にはこんな感じです。

最初のデータが完成したらいつものようにフィルをして完成です。

Office365 2020

Excel_11 TODAYとVLOOKUP関数でタイピングを楽に

今回は常にファイルを開いた時点の日付を返すTODAY（トゥディ）関数と、一覧から自動的に目的項目を書き出してくれるVLOOKUP（ヴイルックアップ）関数について整理しました。

まず D1 に現在の日付を入れます。これは本当に現在の日付なので、ファイルを開いた時点での現在の日付になります。ですから、ダイレクトに日付を入れても意味がありません。

まず D1 セルを選択し数式タグの日付時刻から TODAY を選びます。

選んだ直後に上の様なバレットが表示されますが、そのままOKをクリックします。これで完了です。常にファイルを開いた直後の日付に自動的に変更されます。

ただし、設定直後は長い表記となっていますので調整したい場合はホームタグの［数値＞その他の表示形式］から［日付］を選んで好みの表記に変更します。

ここでは［月日］としました。

次にこの表のC列には商品名が記載されていません。複雑な諸品名をタイプミスせずに入力するのは困難です。そこでタイピングしやすいコード番号だけを入力し、予め用意した商品コードと商品名の対応表（H3:K15）を元にデータを自動的に書き出すためにVLOOKUP関数を使います。まず C4 のセルを選択し数式タグの［検索/業列＞VLOOKUP］を選びます。

表示されたパレットは

検索値　→　基準となる自身の値

範囲　→　別表全体を指定

列番号　→　別表の何列目の情報なのか

検索方法　→　完全一致か曖昧一致か

ということで、

検索値　→　B4　C4セルはABCカメラのデータなので、B4の商品コードを指定します。

範囲　→　H$4:K$15　別表のデータ範囲を指定します。他の店にフィルで対応できるので絶対参照とします。

列番号　→　3　別表のどの列のデータを拾うのかを指定します。ここでは商品名なので 3 を指定。

検索方法　→　0　検索方法は完全一致でないと類似名を拾ってしまうかのうせいがあるので 0 とします。

=VLOOKUP(B4,H$4:K$15,3,0)

Excelのデフォルトは相対参照なので、必要に応じて絶対参照とすることを忘れないようにします。

これで C4 へ自動的に別表のデータが書き出されました。あとはフィルをするだけです。

これで完成です。ところで、VLOOKUPと一字違いのHLOOKUP関数があります。V は vertical（垂直）、H は horizontal （水平）ということで参照データ構成が水平の場合に利用します。今回の例でもし参照する表が以下の様になっていたらHLOOKUP関数となります。

=VLOOKUP(B4,H$4:K$15,3,0)

ではなく、

=HLOOKUP(B4,I$3:T$6,3,0)

となります。Excelのデータ的にはVLOOKUPの方が用途は多いかもしれません。

ちなみに、VLOOKUP関数は処理するデータ量が多いと動作が遅くなります。毎回すべてを探すことになるからです。　そこでビジネスシーンではINDEX関数とMATCH関数の組合せを使うのが一般的ですが、通常のデータ量（500程度？）であればVLOOKUPでも問題は無いでしょう。

Office365 2020