2020.11.11

Excelのセル参照(相対参照・絶対参照・複合参照)

A1参照形式でセルの座標を指定するとき特に指定しないと相対参照で記述されます。
数式を作成中セルを選択して[F4]キーを押すたび、相対参照→絶対参照→複合参照(行のみ)→複合参照(列のみ)→相対参照に切り替わります。
例)A1 → $A$1 → A$1 → $A1 → A1…
相対参照、絶対参照の違いは、数式を他のセルにコピーしたときに影響します。

例として、衆議院HPより所属議員数の表をデータ例として参考にさせていただきました。

例として、左端シート(相対参照シート)上の計算式はすべて相対参照で作られています。
セルD1には=B4+C4、下のD5からD11へコピーした場合=B5+C5…=B11+C11のように、相対的に2つ左+1つ左を参照して計算します。
同様に、E4,F4や、B12,C12,D12も相対参照で計算式が作られています。
数式タブ-ワークシート分析-参照元のトレースボタンを使用することで計算式を可視化することができます。

2020111101
ダウンロード - 2020111101.xlsx

二つ目の例として、中央シート(絶対参照シート)には絶対参照が含まれています。
セルC4には=B4/$B$12、下のC5からC11へコピーした場合には$B$12の部分が絶対参照として固定されています。
これにより分母の$B$12が固定することができます。

2020111102

三つ目の不成功な例として、右端シート(相対参照のままコピーした例)は、相対参照のままでコピーした例です。
セルD5以下のセルの結果が#DIV/0!となり、ゼロ除算エラーと表示されています。

2020111103

衆議院HP
http://www.shugiin.go.jp/internet/index.nsf/html/index.htm

会派名及び会派別所属議員数
http://www.shugiin.go.jp/internet/itdb_annai.nsf/html/statics/shiryo/kaiha_m.htm

2020.11.09

シリアル値(日付・時刻)を表すセルの表示形式の例

ホームメニューから日付時刻の表示形式を指定する場合
セルA1,A2に2020/12/31を入力
セルA1にホーム-数値-下矢印ボタン-日付(上)→R2.12.31
セルA2にホーム-数値-下矢印ボタン-日付(下)→令和2年12月31日、と表示されます。
Excelのバージョンにより異なるようです。
以前は短い日付形式、長い日付形式で西暦の表示形式でした。
お手元のExcelでご確認ください。
セルA3に23:59:59を入力
セルA3にホーム-数値-下矢印ボタン-時刻→23:59:59、と表示

2020110901

ダウンロード - 2020110901.xlsx

セルの書式設定-表示形式によって詳細な設定ができます。
年月日
セルA4,A5に2020/1/1を入力
セルA4の表示形式-ユーザー定義 yyyy/mm/dd→2020/01/01
セルA5の表示形式-ユーザー定義 yy/m/d→20/1/1、と表示されます。
yyyy 西暦4桁
yy 西暦下2桁
m 先頭に0を付けずに月を表示
mm 必要に応じて先頭に0を付けて月を2桁で表示
d 先頭に0を付けずに日を表示
dd 必要に応じて先頭に0を付けて日を2桁で表示

時分秒
セルA6,A7に1:02:03を入力
セルA6の表示形式-ユーザー定義 hh:mm:dd→01:02:03
セルA7の表示形式-ユーザー定義 h:m:d→1:2:3
h 先頭に0を付けずに時間を表示
hh 必要に応じて先頭に0を付けて時間を表示。24時間表示。形式にAMまたはPMが含まれるときは12時間表示。
m 先頭に0を付けずに分を表示
mm 必要に応じて先頭に0を付けて分を表示。
s 先頭に0を付けずに秒を表示
ss 必要に応じて先頭に0を付けて秒を表示。
AM/PM または am/pm 12時間制で時間を表示

経過時間を表す場合の例
セルA8に8:00,A9に=A8*5を入力
セルA8の表示形式-ユーザー定義 [h]:mm→40:00
[h]:mm:ss は、24時間を超えて経過時間を表します。
[mm]:ss は、60分を超えて経過時間を表します。
[ss] は、60秒を超えて経過時間を表します。

日付のシリアル値は曜日も持っています。
セルA10に2020/12/31 23:59:59を入力
セルA10の表示形式-ユーザー定義 yyyy/mm/dd (aaa) hh:mm:dd→2020/12/31 (木) 23:59:59
aaa は、漢字1文字で曜日を表示します。
aaaa は、漢字で曜日を表示します。

上記の表示形式は一部です。また、英語表記の表示形式が用意されています。
詳しくはMicrosoft Supportをご参照ください。

表示形式のカスタマイズに関するガイドラインを確認する
(Microsoft Support ※リンクは変更される場合があります)
https://support.microsoft.com/ja-jp/office/%E8%A1%A8%E7%A4%BA%E5%BD%A2%E5%BC%8F%E3%81%AE%E3%82%AB%E3%82%B9%E3%82%BF%E3%83%9E%E3%82%A4%E3%82%BA%E3%81%AB%E9%96%A2%E3%81%99%E3%82%8B%E3%82%AC%E3%82%A4%E3%83%89%E3%83%A9%E3%82%A4%E3%83%B3%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5

 

2020.11.05

Excelのシリアル値(時刻)

Excelの時刻のシリアル値は1日=1.0を24時間で割ったものです。
1時間は1/24=0.0416666666666667
1分は1/24/60=0.000694444444444444
1秒は1/24/60/60=0.0000115740740740741
になります。
このように、10進数の小数では割り切れない場合が多く出てきます。
(上の3例とも結果が循環小数になっています。)

2020110501

ダウンロード - 2020110501.xlsx

また、コンピューターにおける二進数の小数の計算方法である浮動小数点算術も関連してきます。

このようにExcel内部では時・分・秒を小数で扱っています。
そして小数の端数処理が積み重なることによって、想定した数値と計算結果が異なる場合が出てくる場合があります。
そのためには切上げ、切り捨ての端数処理関数を用いるか、時、分、秒をそれぞれ別々のセルで整数として切上げ、切り捨てして集計する方法などが考えられます。

次の記事で、日付時刻関連のセルの表示形式について書きたいと思います。

循環小数 wikipediaより
循環小数とは、ある桁から先で同じ数字の列が無限に繰り返される小数のことである。
https://ja.wikipedia.org/wiki/%E5%BE%AA%E7%92%B0%E5%B0%8F%E6%95%B0

IEEE754 wikipediaより
IEEE標準のひとつで、浮動小数点算術に関する標準である。
https://ja.wikipedia.org/wiki/IEEE_754

2020.11.04

Excelのシリアル値(日付)

Excelでは、2つの日付システム、1900dateシステム、1904dateシステムがサポートされています。各日付システムでは、他のすべてのブックの日付を計算する一意の開始日が使用されます。Windows用Excelのすべてのバージョンは、1900dateシステムに基づいて日付を計算します。
この記事では1900dateシステムについて書きます。

1900dateシステムでは、日付は、1900年1月1日を出発点として使用して計算されます。日付を入力すると、1900年1月1日以降の経過日数を表すシリアル値に変換されます。たとえば、2011年7月5日を入力すると、日付がシリアル番号40729に変換されます。

2020110401

ダウンロード - 2020110401.xlsx

Excelのシリアル値は曜日も持っています。
セルの表示形式にaaaを指定すると、漢字で曜日の頭文字 (日~土) を表示します。
セルの表示形式にaaaaを指定すると、漢字で曜日 (日曜日~土曜日) を表示します。

※ISO8601において、グレゴリオ暦による年月日は、パリにおけるメートル条約の調印年月日を1875年5月20日とすることで規定されている。曜日は、2000年1月1日を土曜日とすることで規定されている。

Excelにおける1日は1.0です。例としてセルA1に2021/1/1、セルB1に2020/1/1、セルC1に=A1-B1と計算式を入れると366になります。
2020年はうるう年なのでうるう日2/29があるため1年が366日です。

2020110402

ダウンロード - 2020110402.xlsx

次の記事で時間のシリアル値について書きます。

Excelの日付システム(Microsoft support ※リンクは変更される場合があります)
https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE%E6%97%A5%E4%BB%98%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0-e7fe7167-48a9-4b96-bb53-5612a800b487

Excelのセルの表示形式で[ユーザー定義]に使用できる書式記号について
(Microsoft support ※リンクは変更される場合があります
適用対象: Microsoft Office Excel 2003の記事です、今回は曜日部分のみ参照して下さい。 )
https://support.microsoft.com/ja-jp/help/883199

wikipedeiaより
https://ja.wikipedia.org/wiki/ISO_8601

 

2020.11.02

Excelの数式

Excelの数式は=から始まります。
例えばセルに=123+45-67+8-9[Enter]と入力すると計算結果100が表示されます。

2020110201

ダウンロード - 2020110201.xlsx

セルの値を参照する時はA1参照スタイルで座標を指定します。
列はA,B,C...Z,AA,AB,AC...AAA,AAB,AAC...XFDのように1列目から16,384列目を意味します。
行は1行目からはじまり1,048,576行が最終行です。
列の右端の最終列は[Ctrl]+[→]で移動します。左端に戻るには[Ctrl]+[←]です。
行の下端の最終列は[Ctrl]+[↓]で移動します。上橋に戻りには[Ctrl]+[↑]です。
また、[Ctrl]+[Home]で表の左上に移動します。

一番左上のセルはA1で、A1の下はA2で、A1の右はB1となります。

セルA1=123 セルB1=45 セルC1=-67 セルD1=8 セルE1=-9 のように入力されている場合。
セルF1に合計関数SUMで数式=SUM(A1:E1)と入力すると計算結果100が表示されます。

2020110202

ダウンロード - 2020110202.xlsx

演算子の優先度は、()で囲まれた数式>%指定>^べき乗>*乗算/除算>+加算-減算>比較演算子の順に演算されます。

Excelの関数は約500種類近くあります。
様々な目的に合わせて用意されていますが、必要になったときにヘルプ等を利用して使えばよいと思います。

セル参照形式にはもうひとつR1C1参照スタイルがあります。
Excelが出来る前のDOS時代に作られたMultiplanという表計算ソフトでの参照形式です。ちなみにA1参照スタイルはLotus1-2-3で採用していた方式です。
VBA(Visual Basic for Applications)で自動処理を記述する際にはR1C1参照形式で表記するとメリットがある場合があります。


Excel の数式の概要(Microsoft Support リンクは変更される場合があります)
https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE%E6%95%B0%E5%BC%8F%E3%81%AE%E6%A6%82%E8%A6%81-ecfdc708-9162-49e8-b993-c311f47ca173

演算子とExcelでの優先順位(Microsoft Support リンクは変更される場合があります)
https://support.microsoft.com/ja-jp/office/%E6%BC%94%E7%AE%97%E5%AD%90%E3%81%A8-excel-%E3%81%A7%E3%81%AE%E5%84%AA%E5%85%88%E9%A0%86%E4%BD%8D-48be406d-4975-4d31-b2b8-7af9e0e2878a

2020.10.29

Excelと文字列

Excelの一つのセルに入力できる文字数は32,767文字です。
注意が必要なのは入力した値が文字列ではなく、数値や日付時刻(シリアル値)として変換され入力される場合があることです。

例えば携帯番号を連続した11桁の数値として入力したい場合、09099999999と入力すると先頭の0は無視され9099999999と入力されます。
また、半分の意味で1/2と入力すると、今年の1月2日に変換して入力されます。

2020102901

文字列を入力したい場合、あらかじめセルの書式を文字列に設定しておくことが出来ます。
ホーム-数値-▼下三角ボタン-文字列
または
右クリック-セルの書式設定-表示形式-文字列
[Ctrl]+[1]-セルの書式設定-表示形式-文字列
など設定の手順がいくつかありますが設定される結果は同じです。

あるいは、文字列の左端先頭に[Shift]+[7]'(シングルクオーテーション)を付けて入力すると文字列として入力されます。

2020102902 

2020102903

分数で入力したい場合、例えば半分の意味で1/2は0 1/2と入力します。(0と1の間に半角スペースを入れます)
セルの値が0.5と入力され、セルの表示形式が分数に設定されます。

2020102905

どうしても番号などを数値で入力する必要があり、0を省略されたくない場合はセルの書式設定の表示形式をユーザー定義にして設定して表示させることが出来ます。
例えば携帯番号の例だと000-0000-0000のように設定することで090-9999-9999と表示することができます。

2020102904

Excel の仕様と制限(Microsoftサポート ※リンクは変更される場合があります)

https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE%E4%BB%95%E6%A7%98%E3%81%A8%E5%88%B6%E9%99%90-1672b34d-7043-467e-8e27-269d656771c3

 

2020.10.28

Excelでの値の表示

Excelはセルの値を表示するときに、表示形式コードによって結果を整えて表示します。
指定のないとき標準(G/標準)の表示形式が適用されます。
セル幅が値の内容より広ければ値のまま表示されますが、狭い場合は指数表示が適用されます。

2020102801

サンプル画像のセルA1からセルB3には、123456.789が入力されています。
セルA1が指数形式で表示されている例です。

2~3行目は、桁区切りスタイルを指定した場合と、桁区切りスタイルに小数を3桁増やした例です。
B列のようにセル幅が広ければ正しくセルの内容を表示していますが、A列ではセル幅が狭いため ##### と表示されています。

また、セルB2が 123,457 と表示されていることに注目してください。
Excelは表示桁数で四捨五入して表示します。
計算結果を表示桁数で正しく表示するためには、四捨五入、切り上げ、切り捨ての関数を使用して計算する必要があります。
詳しくは段階を追ってブログに書いていきたいと思います。よろしくおねがいします。

表示形式のカスタマイズに関するガイドラインを確認する(Microsoftサポート ※リンクは変更される可能性があります)
https://support.microsoft.com/ja-jp/office/%E8%A1%A8%E7%A4%BA%E5%BD%A2%E5%BC%8F%E3%81%AE%E3%82%AB%E3%82%B9%E3%82%BF%E3%83%9E%E3%82%A4%E3%82%BA%E3%81%AB%E9%96%A2%E3%81%99%E3%82%8B%E3%82%AC%E3%82%A4%E3%83%89%E3%83%A9%E3%82%A4%E3%83%B3%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5

2020.10.26

Excelが扱える桁数

Excelはとても大きな数から小さな数まで扱えますが、その有効桁数は15桁です。

1円まで正確な計算を行うとすると、999兆9999億9999万9999円が最大となります。

小数でも有効桁数は同じ15桁です。円周率は無限に続きますが、Excelでは3.14159265358979までとなり、以降は0となってしまいます。

例えば、クレジットカードの番号を数値で入力すると、16桁目が0になってしまいますので、文字列として入力しないと正確に記録できません。

一般的には有効桁数15桁で問題が起きることは少ないと思いますが、無限の数値を扱えるわけではないと知ってるとよいかもしれません。

2020102601

参考リンク

Excelの仕様と制限(Microsoftサポート ※リンクは変更される可能性があります)

https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE%E4%BB%95%E6%A7%98%E3%81%A8%E5%88%B6%E9%99%90-1672b34d-7043-467e-8e27-269d656771c3

 

 

 

2020.10.25

ブログはじめます。

MOS ExcelとWordの2019に合格したことをきっかけにブログ始めます。

主に、Excel,Word,PowerPointやLibreOfficeなどオフィスソフト関連について書いていきます。

 

次のMOS受験はPowerPoint2019を予定しています。FOM出版のテキストが年内発売のようなので、発売後に学習して受験したいとおもっています。

よろしくお願いします。

世良満久