【Office2021 シリーズ】Excel実用編：住所録の作成例

完成例　

住所録の入力用シートを作成する例です。

作成した住所データをWord2007で宛名印刷するように加工を加えています。

都道府県名、住所(宛名用)ははがきに印刷する際に利用します。

チェック欄　

印刷するデータにはチェックを入れるように入力規則を利用します

A2:A100を選択します。

[データ]タブの[データ ツール]グループで[データの入力規則]→[データの入力規則]を実行します。

[データの入力規則]ダイアログの[設定]タブで、入力値の種類を「リスト」、元の値に「レ,　」(レ,　スペース)と入力します。

なお、この図ではチェックのマークはユニコードを使用しています。

Microsoft Office IME 2007での変換例です。

「ちぇっく」と入力して変換しますが、変換候補に表示されないときは「記号」から選択しています。

チェック欄で「レ」と「　」(スペース)が選択して入力できるようになりました。

名前、住所(入力)

入力規則でセルを選択時にIMEをONにします。

B2:B100とE2:E100を選択します。

名前ボックスにB2:B100,E2:E100と入力します。




[データ]タブの[データ ツール]グループで[データの入力規則]→[データの入力規則]を実行します。



[データの入力規則]ダイアログの[日本語入力]タブで「オン」に設定します。



B2:B100とE2:E100のセル範囲のセルを選択すると、IMEの日本語入力がオンになります。

郵便番号「８９２－０００２」と入力して変換している状況です。

【Office シリーズ】Excel実用編：住所録の作成例

完成例　

住所録の入力用シートを作成する例です。

作成した住所データをWord2007で宛名印刷するように加工を加えています。

都道府県名、住所(宛名用)ははがきに印刷する際に利用します。


住所録の構成について　

チェック欄：印刷するデータにはチェックを入れるように入力規則を利用します。

名前：入力規則でIMEをONにし、キーボードから入力します。

フリガナ：入力した名前のふりがな情報をPHONETIC関数で取得します。

郵便番号：入力した住所(入力)のふりがな情報をPHONETIC関数で取得します。

(注)住所を郵便番号から変換して入力していることが前提です。

住所(入力)：入力規則でIMEをONにし、IMEの機能を利用し、郵便番号から住所に変換して入力します。

都道府県名：入力した住所(入力)からIF関数、MID関数、LEFT関数で取得します。

住所(宛名用)：入力した住所(入力)と都道府県名からSUBSTITUTE関数で取得します。

電話番号：入力規則でIMEをOFFにします。

電子メールアドレス：入力規則でIMEをOFFにします。

備考：入力規則でIMEをONにします。

excel2021縦棒を長くしたい場合と縦棒のマーカーを消したい場合

縦棒を長くしたい場合はG4セルを　-1.3　、G6セルを 1.3 に変更します。



縦棒のマーカーを消したい場合

グラフツールの[書式]タブで「系列4」を選択し、[選択対象の書式設定]をクリックします。


↓
データ系列の書式設定で「塗りつぶしと線」「マーカー」と順番に選択します。

「マーカーのオプション」で「なし」にチェックを入れます。


知りたい日のデータをグラフに表示するため、Sheet1のF1に
=TEXT(D1,"ge.m.d")&CHAR(10)&

"身体P："&TEXT(SIN((D1-B1)*2*PI()/23),"0%")&CHAR(10)&

"感情S："&TEXT(SIN((D1-B1)*2*PI()/28),"0%")&CHAR(10)&

"知性I："&TEXT(SIN((D1-B1)*2*PI()/33),"0%")

と入力して、データを表示します。



「グラフ1」シートにテキストボックスを描画します。

数式バーを選択し、「=Sheet1!F1」と入力します。

プロットエリアや系列の色などをお好みに変更して完成です。

excel2021 グラフの修正 Y/数値軸の設定

Y/数値軸の設定

Y軸を選択します。

軸のオプションで表示形式を選択します。

「シートとリンクする」のチェックを外します。(シートは小数点表示ですが、グラフではパーセントで表示するためです。)

表示形式コードに　0%　と入力して、[追加]ボタンをクリックします。


凡例を表示します。

グラフツールの[デザイン]タブを選択します。

[グラフ要素を追加]→[凡例]→[上]を選択します。


↓
凡例がグラフエリアの上に表示されました。



知りたい日がどこにあるか見にくいので、縦棒を入れてみます。

Sheet1のF4に「=$D$1」として、F6までコピー します。

Sheet1のG4に「-1」、G5に「0」、G6に「1」と入力します。

"【Office シリーズ】-2021-7-1 443-19"


「グラフ1」シートを選択し、グラフを選択します。


グラフツールの[デザイン]タブの[データの選択]を実行します。


"【Office シリーズ】-2021-7-1 443-20"


データソースの選択で[追加]ボタンをクリックします。


"【Office シリーズ】-2021-7-1 443-21"


系列Xの値のボックス内をクリックしてカーソルを表示します。


Sheet1を選択して、F4:F6 を指定します。(グラフシートがアクティブになったら、Sheet1をアクティブにします)

系列Yの値のボックス内をクリックして、カーソルを表示し、G4:G6 を指定します。

[OK]ボタンをクリックします。

"【Office シリーズ】-2021-7-1 443-22"

縦棒が追加されました。

excel2021 グラフの修正 X/数値軸の設定

グラフの修正　　　　

X/数値軸の設定

グラフのX軸をダブルクリックして「軸の書式設定」を表示します。

または、グラフツールの[書式]タブで「横(値)軸」を選択して、「選択対象の書式設定」をクリックします。



軸のオプションの単位で　主　に　5　、補助に　1　を入力しました。



[表示形式]を選択して、表示形式コードに　m/d　と入力して、[追加]ボタンをクリックします。

すると、種類に　m/d　と表示されます。

グラフの横軸の日付が見やすくなりました。( 年/月/日)では収まらないので、年を省略しています)

Excel実用編：バイオリズムのグラフを作成する グラフの作成 (1)

ここではExcel2016を使って、散布図でバイオリズムのグラフを作成します。

データ表の「A3:A65」を選択し、Ctrlキーを押したまま「C3:E65」を選択します。



上記のシートは説明しやすくするため8～62行目をグループ化しています。

8行目から62行目を選択します。

↓

[データ]タブ[グループ化]を実行します。


↓

アウトライン番号「1」をクリックすると、グループ化した行を折りたたむことができます。

「+」ボタンをクリックすると折りたたんだ行を展開できます。

Excel実用編：バイオリズムのグラフを作成する 元になるデータ表の作成

元になるデータ表の作成　

B1セルに生年月日を入力します。時間が分かる場合は時間を含めて入力します。

D1セルに知りたい日を入力します。 (グラフの中央にする日付になります。この日付の前後30日がグラフに表示されます。)

グラフ化するのは知りたい日の30日前から表示することとします。

A4セル：=D1-30

A5セル：=A4+1　として、A65セルまでフィルハンドルをドラッグして数式をコピーします。

経過日数　B4セル：=A4-$B$1

身体(P)　C4セル：=SIN($B4*2*PI()/23)

感情(S)　D4セル：=SIN($B4*2*PI()/28)

身体(I)　E4セル：=SIN($B4*2*PI()/33)

セル範囲B4:E4を選択して、フィルハンドルをダブルクリックするとデータ表がほぼ完成です。

【Office シリーズ】 Excel実用編：簡単な家計簿の作成(計算式を使う) Step6

【Office シリーズ】  Excel実用編：簡単な家計簿の作成(計算式を使う) 

Step_6　費目ごとの集計　　

Sheet3へ費目の集計をしたいと思います。

A3:A5に費目を入力します。

金額はB3セルに=SUMIF(Sheet1!$C$3:$C$100,Sheet3!A3,Sheet1!$F$3:$F$100)　のように入力し、フィルハンドルを下方向へドラッグしてコピーします。

なお、セル範囲は入力データ数に応じて変更します。ここでは、Sheet1!$C$3:$C$100と100行目までを対象としました。


なお、集計やピボットテーブルなどを利用することもできます。これらは別のページで説明していますので参照してください。

【Office シリーズ】 Excel実用編：簡単な家計簿の作成(計算式を使う) Step2

【Office シリーズ】  Excel実用編：簡単な家計簿の作成(計算式を使う) 

Step_5　詳細をリストから選択したい　　

費目と同様に詳細もリストから選択して入力したい・・・・リストを切り替えれたらできそうだけど?

詳細をSheet2の費目の下にそれぞれ入力します。

B2:S9を選択して、挿入→名前→作成を実行します。

「上端行」にチェックを入れて[OK]ボタンをクリックすると、各詳細の範囲が各費目の名前で定義されます。

D3セルを選択し、[データ]→[入力規則]を実行します。

設定タブで「入力値の種類」で「リスト」、「元の値」に「=INDIRECT(C3)」と入力し[OK]ボタンをクリックします。



D3セルを選択し、フィルハンドルを下方向へドラッグしてコピーします。

費目に応じた詳細をリストから入力できるようになりました。

"【Office シリーズ】-2021-7-1 441-15"

【Office シリーズ】 Excel実用編：簡単な家計簿の作成(計算式を使う) Step4

【Office シリーズ】  Excel実用編：簡単な家計簿の作成(計算式を使う)

Step_4　費目をリストから選択したい　　　

費目は決まっているのでいちいち入力するのが面倒・・・・リストから選択できないの?

入力規則のリストを利用しよう・・・

Sheet2の2行目に費目を横に書き出します。A2には「費目と入力します。



入力した2行目を選択し、挿入→名前→作成を実行します。

「左端列」にチェックを入れて[OK]ボタンをクリックすると、B2:IV2のセル範囲が「費目」という名前で定義されます。


Excel2007では[数式]タブの[定義された名前]グループの[選択範囲から作成]を実行します。



C3セルを選択し、[データ]→[入力規則]を実行します。


設定タブで「入力値の種類」で「リスト」、「元の値」に「=費用」と入力し[OK]ボタンをクリックします。


C3セルを選択し、フィルハンドルを下方向へドラッグしてコピーします。

費目をリストから入力できるようになりました。

【Office シリーズ】 Excel実用編：簡単な家計簿の作成(計算式を使う) Step4

Step_3　自動でNoを表示する　　　

A列のNoを入力するのは面倒・・・自動で表示できないの?

C列の費目が表示されたらNoが表示されるようにしたい・・・

Noは行番号より2少ない値の連番なので、行番号から-2すればよいのに気付きました。

A3セルに=IF(C3="","",ROW()-2)　として、フィルハンドルを下方向へドラッグしてコピーします。

C13セルに「食費」と入力すると、A13セルには「11」と表示されました。これで、Noが自動で表示されるようになりました。

【Office シリーズ】 時刻・時間関連:Excel講座 【30分単位で丸める】

【30分単位で丸める】

時間の計算で「30分単位で丸める」ことが必要な時は

=FLOOR(VALUE(TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"）,"h:mm")),"0:30")

とします。

注意：毎日の残業時間の端数を切り捨てることは、労働基準法違反となり認められないようです。

あくまでも計算の例としてご覧ください。

以上をまとめると、下記のように長～い式になります。



D3セルは

=IF(COUNT(B3:C3)=2,FLOOR(VALUE(TEXT(MAX(0,MIN(C3,"17:00")-MAX(B3,"9:00")),"h:mm")),"0:30"),"")

と書くこともできます。

E3セルは

=IF(COUNT(B3:C3)=2,FLOOR(VALUE(TEXT(MAX(0,MIN(C3,"22:00")-MAX(B3,"17:00")),"h:mm")),"0:30"),"")

と書くこともできます。

テンキーで時刻を簡単に入力する　「:」を「..」で入力する(オートコレクト)

時刻の入力で「:」(コロン)を入力するのが面倒くさい・・・といわれる方がおられます。

テンキーの「..」(ドットを2個）と入力して、「:」に変換する方法にオートコレクトを使用することができます。

オートコレクトはExcelだけではなくWordにも適用されます

[ファイル]タブをクリックして、バックステージビューを表示します。


[オプション]をクリックします。


[文書校正]を選択して、[オートコレクトのオプション]ボタンをクリックします。


【オートコレクト】ダイアログの【オートコレクト】タブを選択します。

「入力中に自動修正する」にチェックを入れます。

『修正文字列』に「..」(ドットを2個)　『修正後の文字列』に「:」(コロン)を入力します。

【追加】ボタンをクリックします。

【Office シリーズ】 時刻・時間関連:Excel講座【計算誤差(小数誤差)の防止】

【計算誤差(小数誤差)の防止】

時刻のシリアル値は1未満の小数値になっています。上記のような計算によって誤差を生じることがあります。

計算誤差を防ぐ方法の一つとして、TEXT関数を使う方法を書いてみます。

=MIN(C3,"22:00")-MAX(B3,"17:00"）

を

=TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm")

とします。これは文字列「2：10」となり計算に使えないので、

=VALUE(TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"）,"h:mm"))

　として、シリアル値に戻します。

=TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm")*1　

=--TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm")

と書くこともできます。

【Office シリーズ】 時刻・時間関連:Excel講座 勤務時間の計算

勤務時間の計算

パートの人の勤務時間計算で、定時が 9:00～17:00　となっている場合の計算方法を考えてみましょう。


手計算ではD列のように各時間帯毎に計算すればいいのですが、一つの計算式にまとめるには

引かれる方の値：終業時刻（17:00）と退社時刻（C列)の値の小さい方が基準になります。

引く方の値　　：始業時刻(9:00)と出社時刻（B列)の値の大きい方が基準となります。

つまり、E列の数式『=MIN(C3,"17:00")-MAX(B3,"9:00")』で求めることができます。

なお、F5セルのような30分未満は切り捨てて計算するには、FLOOR関数と組み合わせます。

=FLOOR(MIN(C5,"17:00")-MAX(B5,"9:00"),"0:30")　　答え=5:00

逆に切り上げたい場合にはCEILING関数を使います。

=CEILING(MIN(C5,"17:00")-MAX(B5,"9:00"),"0:30")　　答え=5:30

次に残業時間の計算を加えてみましょう。

定時は 9:00～17:00

残業は　17:00～22:00　とします。


定時の勤務時間の計算と同様ですが、E5セルはエラーになってしまいます。

IF関数を使って、残業開始時刻前に退社した場合は「空白」とするようにします。

=IF(C5-"17:00"<0,"",MIN(C5,"22:00")-MAX(B5,"17:00"））

エラーとなるのは計算結果が負の値になるときなので、最小値を「0」とするようにします。

=MAX(0,MIN(C5,"22:00")-MAX(B5,"17:00"））

また定時の計算でも、B列やC列が入力されていない時は「空欄」としておく必要があります。

出社時刻が残業開始時刻（17:00）より遅い場合も定時の時間は「0」となります。

=IF(OR(B3="",C3="",B3-"17:00">0),"",MIN(C3,"17:00")-MAX(B3,"9:00"））

=IF(OR(COUNT(B3:C3)<2,B3-"17:00">0),"",MIN(C3,"17:00")-MAX(B3,"9:00"））

=IF(OR(B3="",C3=""),"",MAX(0,MIN(C3,"17:00")-MAX(B3,"9:00"））)

=IF(COUNT(B3:C3)<2,"",MAX(0,MIN(C3,"17:00")-MAX(B3,"9:00"））)

としておけばよいと思います。

【Office シリーズ】 時刻・時間関連:Excel講座 マイナスの時間を表示する

【おまけ：マイナスの時間を表示する】

文字列で表示する例です。

=IF(A1-B1<0,TEXT(ABS(A1-B1),"-"&"h:mm"),TEXT(A1-B1,"h:mm"))

負になる場合は絶対値に「-」を付け加えて文字列にしています。


時刻のシリアル値　　　　　

時刻のシリアル値は、0～0.99999999の範囲にある値で、0:00:00(午前0時)から 23:59:59 (午後11時59分59秒)までの時刻を表します。

セルに「24:00」と入力すると、数式バーには「1900/1/1 0:00:00」と表示されます。

"24:00"= 1　ですので　"1:00"= 1/24　となります。

では、"18:30"-"8:00"="10:30" を 10.5 と表示したい時はどうすればよいでしょうか?

="18:30"-"8:00"=0.4375　とシリアル値が表示されます。(セルの表示形式は標準とします)

=("18:30"-"8:00")*24　=　10.5　とします。

逆に、10.5を、10:30　としたい時は

=10.5/24　=　0.4375　として、セルの表示形式を「時刻」の「13：30」とします。