「function」タグアーカイブ

EXCEL WORKSHOP TRIAL

EXCEL2：サンプリング ／統計関数で統計学事始め

まず、統計学入門のページを検索すると、2つのキーワードが出てきます。

母集団(population), 標本(sample)

「エクセル関数」から「統計関数」を抽出し、簡単な分類を試してみました。

■　統計関数で知っている。使ったことがある。
データ個数（5）
COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS
平均値 (5)
AVERAGE AVERAGEA　AVERAGEIF AVERAGEIFS　TRIMMEAN
最大値と最小値 (4)
MAX MAXA MIN MINA
順位 (5)
LARGE SMALL　RANK RANK.EQ RANK.AVG

■　情報処理技術者試験で学習。実務でも一部使ったことがある。
分散 (6)
VAR.P VARP VARPA　VAR VAR.S VARA
標準偏差 (6)
STDEV.P STDEVP STDEVPA　STDEV STDEV.S STDEVA

■　実務で使ったことはないが、統計関数で知っている
中央値と最頻値 (4)
MEDIAN　MODE MODE.SNGL MODE.MULT
度数分布 ：区間配列 (1)
FREQUENCY
平均値(相乗平均)(調和平均) (2)
GEOMEAN HARMEAN
平均偏差 (1)
AVEDEV
百分位数 (6) ：率
PERCENTILE PERCENTILE.INC PERCENTILE.EXC
PERCENTRANK PERCENTRANK.INC PERCENTRANK.EXC
四分位数 (3) ：戻り値（0%,25％,50%,75%,100％）
QUARTILE QUARTILE.INC QUARTILE.EXC

■　サンプル例によっては説明を読めば理解はできる。専門的で手ごたえがある。
変動 (1)
DEVSQ
標準化変量 (1)
STANDARDIZE
歪度、尖度 (3)
SKEW SKEW.P KURT
相関係数 (2)
CORREL PEARSON
共分散 (3) ：
COVAR COVARIANCE.P COVARIANCE.S
二項分布 (8) ：成功率
BINOM.DIST BINOMDIST　BINOM.DIST.RANGE BINOM.INV CRITBINOM
NEGBINOM.DIST NEGBINOMDIST
超幾何分布 (2) ：母集団、標本数
HYPGEOM.DIST HYPGEOMDIST
ポアソン分布 (2) ：事象の平均
POISSON POISSON.DIST
正規分布 (4) ：平均、標準偏差、累積確率
NORM.DIST NORMDIST　NORM.INV NORMINV

■　専門的で、関数を学習してもよく解らない。実例も直観で理解できない。
回帰直線による分析 (6) ：予測に使うｘ
FORECAST　TREND SLOPE INTERCEPT LINEST RSQ
指数回帰曲線による予測 (2)
GROWTH LOGEST
正規分布 (10)
NORM.S.DIST NORMSDIST　NORM.S.INV NORMSINV PHI GAUSS
LOGNORM.DIST LOGNORMDIST
LOGINV LOGNORM.INV
母集団に対する信頼区間 (3) ：有意水準、標準偏差
CONFIDENCE CONFIDENCE.NORM CONFIDENCE.T
下限値〜上限値の確率 (1)
PROB
カイ二乗分布 (6) ：自由度
CHISQ.DIST CHIDIST　CHISQ.DIST.RT CHISQ.INV　CHIINV CHISQ.INV.RT
カイ二乗検定 (2) ：実測値、期待値
CHISQ.TEST CHITEST
t分布 (7) ：自由度
T.DIST T.DIST.RT T.DIST.2T　TDIST T.INV T.INV.2T TINV
t検定 (2) ：平均値
T.TEST TTEST
Z検定 (2) ：正規母集団の平均
Z.TEST ZTEST
F分布 (6) ：自由度
F.DIST F.DIST.RT FDIST F.INV F.INV.RT FINV
F検定 (2) ：母分散（帰無仮説）（対立仮説）
F.TEST FTEST
フィッシャー変換 (2) ：
FISHER FISHERINV
指数分布関数 (2) ：
EXPON.DIST EXPONDIST
ガンマ分布 (7) ：
GAMMA　GAMMA.DIST GAMMADIST　GAMMA.INV GAMMAINV
GAMMALN GAMMALN.PRECISE
ベータ分布 (4) ：
BETA.DIST BETADIST BETA.INV BETAINV
ワイブル分布 (2) ：
WEIBULL WEIBULL.DIST

今回のところは、以上になります。

EXCEL WORKSHOP TRIAL

EXCEL2：排他的論理和 XOR関数

EXCEL2013から追加された論理関数、XOR関数について。＜タイトルのみ＞

XOR関数
読み方　eXclusive OR（エクスクルーシブ オア）
英単語　exclusive　排他的な。閉鎖的な。独占的な。専用 の。

引数　＝XOR(論理式1,論理式2,‥論理式254）　254個まで　→？
※ 引数が２つの場合は説明できる。３つ以上の場合？

関連　BITXOR関数（エンジニアリング関数　ビット エクスクルーシブ オア）

—
自分は、WindowsXP、Office2003の頃に受講した地元区立公民館の講習会で「排他的論理和」を勉強しました。言葉は、一度で憶えました。
教室にPCはなく、週1回3ヶ月間、先生は職業訓練校講師、座学でプリント学習、格安の情報処理試験対策講座でしたが、その年の春に情報処理技術者試験に合格しました。

以上

EXCEL WORKSHOP TRIAL

EXCEL2：サムイフ ゆース

機能が同じ関数で同じ名前、単数形の単語にSを付けて複数形を表すような関数名が、Excelのバージョンアップで追加されています。Sを追加して複数の条件に対応できることで、EXCELが相当便利に感じるという印象があります。

SUMIF　 SUMIFS　(数学関数　2007以降)
COUNTIF COUNTIFS　(統計関数　2007以降)
AVRAGEIF AVRAGEIFS　(統計関数　2007以降)
COLUMN COLUMNS (行列関数　2007以降)
ROW ROWS (行列関数　2007以降)
DAY DAYS (日付／時刻関数 2013以降)

SUMIF関数は条件を指定して数値を集計する関数ですが、SUMIFS関数のSは「複数の条件」に当たり、データベースの条件で場合分けしながら集計、引数の[条件範囲］と［条件］は127個まで指定できます。また、すべての［条件］に一致したセル範囲の数値、複数の［条件］がAND条件で抽出される値が合計されることになります。

EXCEL WORKSHOP TRIAL

EXCEL2：十六進数

10進数で0から15までの数値を、16進数では

０１２３４５６７８９ＡＢＣＤＥＦ

と表記します。
16進数の ＡＢＣＤＥＦ は、10進数の 10～15 で、2桁になると、１０(10進数で 16)　からそれ以降は、１１、１２～ １９、１Ａ，１Ｂ～　　９Ｆ(10進数で 159)、Ａ０(10進数で 160)～ ＦＦ(10進数で 255)

カラーコード(#000000～ #FFFFFF)が6桁の16進数で表記されるのを御存知だと思いますが、16進数で表記される値を10進数の普通の数値に置き換えたりして、その訳を知りたくなるものでしょう。

＃００００００　　Black: 黒　(10進数で　0, 0, 0）

# 00 00 FF 　Blue　　(10進数で　0, 0, 255）
# 00 FF 00 　Lime　　(10進数で　0, 255, 0）
# 00 FF FF 　Cyan　　(10進数で　0, 255, 255）
# FF 00 00 　Red　　 (10進数で　255, 0, 0）
# FF 00 FF 　Magenta (10進数で　255, 0, 255）
# FF FF 00 　Yellow　(10進数で　255, 255, 0）

＃ＦＦＦＦＦＦ　　White: 白　(10進数で　255, 255, 255)

組合せで 256×256×256種類、色の名前が付けられてあるわけではないと思いますが、カラーコードは10進数では、ＲＧＢ(Red, Green, Blue) 表記になります。

それからIPアドレス、IPv6 は128ビットを16ビットごとに：(コロン)で区切って、16進数で表記されていて、たとえば

2001：0000：0000：0000：aaaa：bbbb：cccc：1111

2001　　(10進数で　18,193) (2進数で　10 0000 0000 0001)
0000　　(10進数で　10) (2進数で　0 0 0 0)
aaaa　　(10進数で　143,690)(2進数で　1010 1010 1010 1010)
bbbb　　(10進数で　148,059)(2進数で　1011 1011 1011 1011)
cccc　　(10進数で　152,428)(2進数で　1100 1100 1100 1100)
1111　　(10進数で　14,369) (2進数で　0001 0001 0001 0001)

さて、
EXCELでは、16進数を変換したい場合の関数があります。
いずれも、エンジニアリング関数に属しています。

■　HEX2DEC関数　：　16進数を10進数に変換する
ヘキサデシマル（hexadecimal 十六進数）・トゥ・デシマル(decimal 十進数)

= HEX2DEC(数値)

■　DEC2HEX関数　：　10 進数を 16 進数に変換する
デシマル (decimal 十進数)・トゥ・ヘキサデシマル（hexadecimal 十六進数）

= DEC2HEX（数値, 桁数）

この場合、求める16進数の桁数をあらかじめ指定しておかなければなりませんので、進数の変換を試すより、数式のセルの値に指定通りの16進数表記にするための関数と思われます。

EXCEL WORKSHOP TRIAL

EXCEL2：Summation, the SUM of

SUM関数の SUM は「Summation」の略語とどこかで読んだかもしれないのですが、
「SUM」自体で単語だそうです。

※以下の()内は引数ではありません。
Summation　＝the whole sum(総額、総数)

The sum total is 100,000

a big sum(大金)

do a sum(計算する)

a like sum(同額)

某データシートにJOBが来るごとに随時、1行1件ずつデータ情報を追加していくというエクセル表で、毎日退社時は当日データの最終行に当日計の行として、当日分セル範囲を選択してSUM関数で小計、と毎日毎月何行追加になるか分からない、という表を作成していました。

月末には、月末日の当日小計行のさらに１行下、表の最終行を当月計として、

＝SUM(月初日1行目：末日最終行)／２

としていました。

各日各データの合計と小計行がダブっているだけだから、というわけです。

EXCEL WORKSHOP TRIAL

EXCEL2：INDIRECT攻略したい

INDIRECTという風変わりな関数に興味があります。二次的な、という意味を持つINDIRECTは、間接的にセル参照する機能を持つ検索関数で、二次関数ではなく、OFFICE Onlineサポートサイトでは、INDIRECT関数は「指定される文字列への参照を返す」と書かれています。
つまり、INDIRECT関数の引数の括弧には、＝INDIRECT(A1)などとセル番地が入ることになると思います。A1セルに、表のどこか「セル番地」を文字列で入力しているとき、INDIRECT関数は、引数の参照セルに書かれたセル番地のデータを参照しています。

普通セル参照といえば、A1の値を参照するB1セルに ＝A1 と入力しますが、
INDIRECT関数の参照元で ＝A1とすると、数式ですので結果はエラーになります。参照元は文字列で、引数のように「セル番地」を入力するという事です。

また、SUM関数と組み合わせて、セル範囲の合計を計算します。
＝SUM（INDIRECT(A1))
A1に、B1:B10 と引数の様に入力したセル範囲の合計値を表示します。
このセル範囲に名前を定義すると、A1に名前で置き換えた選択範囲を計算します。

あるいは、「指定される文字列への参照を返す」ので　＝INDIRECT(“A1”) と解説している教本の方が多いです。
“A1” と表記するときは、引数が文字列のことですので、A1セルの内容をそのまま参照しています。A1セルが文字列でなくても、数値でも、数値は数値としてそのままA1セルの値が参照されるようです。

参照元表は別シートでも、開いている別ブックでも可能という事です。

＝INDIRECT(“A1”) 　の場合、セルの絶対参照のように、行列にコピーしてもA1を参照しています。

EXCEL WORKSHOP TRIAL

EXCEL◇表計算で 1円足りない？

業務用で1円未満を含むちょっとややこしい資料です。
小数点以下2位

5項目分のポイントを加算

↓

5等分した値を各項目の元データから減算します。

計算結果が請求金額と同額なのを確認して、科目別の仕訳伝票を起こす作業でしたが、EXCEL2010(Win7)で、EXCEL表の合計金額が正しい値(整数値)で見えているので［OK］したのに、業務システムに入力した収支の結果の方を見ると1円不足しています。

1円も違っていました！！

EXCELの数式を信用できない例はわりとよくあります。
ROUND関数で桁上げや四捨五入した値を用いる場合、整数値を計算した後、もう1度データを手入力することがあります。

INT関数などでも同様です。値貼り付けすると、小数点以下の値までセルにちゃんと残されていることが解ります。

計算結果がどうしても合わない時は、数式やコードを見直す仕事より、慌てずに実際のお金の残高に合わせてあるデータを信用することです。

EXCEL WORKSHOP TRIAL

EXCEL◇金種両替表

エクセルで簡単な両替ツールを考えているところです。

金種は以下の9通り、
1万円札
5千円札
1千円札
5百円玉
1百円玉
5十円玉
1十円玉
5円玉
1円玉

まず、A1に金額を入力すると、B列各セルの数式で金種ごとの枚数が計算されるという標準ツールで、実際手元にあるお金の枚数をエクセル両替ツールでチェックします。

次に作成したいのは、以下B列の各金種行に「欲しい枚数」が入力できるものです。
2万5千円分の1千円札が欲しいとか、出金は4千8百円だけど5百円玉が10枚欲しい等の両替の希望条件がある場合です。

1万円札の方から処理すると、金額を各金種で割り算して整数値にする関数を使用すれば計算できます。
金額は0以上の整数ですから、関数の性質はあまり深く考えなくても大丈夫だと思います。
1円玉の方から考えると、10円以上の端数で枚数を計算できますが、5の倍数の金種があるかないかを一度確認します。

EXCEL WORKSHOP TRIAL

EXCEL◇SUM関数で合計する選択範囲について

列の合計、SUM関数で足し算したいデータ列のセルを連続で範囲選択しますが、

あとで行挿入して、選択範囲にデータ行を追加する事があります。

SUM関数が入っている合計セルの、上行に行挿入してデータを追加した時、正しい合計値が出ていたと思います。別のシートで、合計値が少ないことに気付きました。SUM関数の数式で、合計の選択範囲が元の範囲のままです。合計するセル範囲全体が選択されていません。

「集計値は桁違いでなければよい」（提出元が作成しているデータが未完成で、必ずしも信用できるとは限らないから）と教わった気もしますが、

千の位未満の百、十、一の位の金額、小銭も集めればまとまった金額が出ますので、実際あるはずなのに残高不足とか、金額で余るとか、問題になりますので、無視できません。

SUM関数は見慣れていて、数式が入っているセルをそのままにしますが、EXCELが編集できる状態で共有されていると、更新されることがあります。セルで見えている数字が明らかにおかしい時は、数式の内容まで確認すると思いますが、何気なく見過ごさない様に気を付けなければなりません。よく見て、楽観しない事です。

EXCEL WORKSHOP TRIAL

EXCEL◇日付に曜日を表示する

日程表の「日付」列1行目「1日」セルに「今月1日」の日付を入力すると、右隣「曜日」列に曜日が表示されるように数式か何か仕掛けを作成しておきたいと思います。日付に対応する曜日を表示させる方法は幾通りか考えられます。今年1月1日の曜日を入力していればオートフィルで機能で７曜日が繰返しコピーされますので、日付と曜日2列セットで、1シートに1年分365行(366行)の日付と曜日の値が一気に得られます。各月シートを作成する場合は、同様に先頭の1日の曜日をカレンダーで調べ、以降はオートフィルで曜日をコピーします。

曜日列に「曜日」の書式を設定しておく方が比較的簡単かもしれません。セルの書式設定の「日付」に曜日はありませんが、ユーザ定義で日付のシリアル値を aaa あるいは aaaa とすると日本語の曜日名(日月火水..及び曜日)をセルで表わす事が出来ます。書式のユーザ定義を ddd あるいは　dddd とすると、sun,mon,tue.. など曜日を英語表記できます。

また、曜日といえば［WEEKDAY関数］ が挙げられますが、［WEEKDAY関数］は、番号で曜日名を取得します。

EXCELが曜日を下記のように1～7の数字で表示するように、［WEEKDAY関数］の表示形式を１に設定します。

＝WEEKDAY（シリアル値,［種類］)

引数の［シリアル値］は日付を入力したセルを参照します。

引数の［種類］に１を入力します。引数の１は、１から７までの番号で１を日曜に設定する表示の種類です。日曜からはじまるカレンダーが１、月曜からはじまるカレンダーが２.. などと［種類］の番号で決めておきます。（引数の［種類］は省略できます。［種類］を設定しない場合は自動的に１）

下記表の曜日名の 日曜日（SUNDAY）の１とは異なります。

※曜日名表

１　日　日曜日　SUN　SUNDAY

２　月　月曜日　MON　MONDAY

３　火　火曜日　TUS　TUSDAY

４　水　水曜日　WED　WEDNESDAY

５　木　木曜日　THU　THURSDAY

６　金　金曜日　FRI　FRIDAY

７　土　土曜日　SAT　SATURDAY

［WEEKDAY関数］は、数字で曜日名を表すだけですので、数字を曜日名に置き換えなくてはなりません。

［セルの書式設定］でユーザ定義を曜日にします。

WEEKDAY関数で曜日の番号が出ているセルについて、［セルの書式設定］［表示形式タブ］の［ユーザ定義］の［種類］のテキストボックスに　aaa（日本語の曜日）または　ddd(英語表記）を設定します。

aaa( または aaaa) 　と入力すると、日（または日曜日）

ddd ( または dddd)　と入力すると、SUN（またはSUNDAY）

また、他の関数と組合せ（ネスト）して、関数で曜日名が表示されるように、数式を作る方法もあります。

［TEXT関数］は、［セルの書式設定］［表示形式タブ］の［ユーザ定義］で設定する表示形式を、関数式で記述することができます。

＝TEXT（シリアル値，表示形式）　です。

TEXT関数の引数の［シリアル値］は、「日付を入力したセルを参照して曜日の数式を作ったセル」を選択します。

TEXT関数の引数の［表示形式］は、［セルの書式設定］で設定する　aaa など曜日の表示にします。

=TEXT(WEEKDAY(日付セル ),aaa)

さらに別の関数で、7通りの「曜日名」に置き換える方法があります。［CHOOSE関数］は、引数のインデックスを1以上の整数で表し、イデックスの番号に当たる文字列を設定することで、番号と置き換えする曜日名の表示などに使えます。

＝CHOOSE（インデックス、値１，値２，値３，値４，値５，・・)

=CHOOSE(WEEKDAY(日付セル),”日”,”月”,”火”,”水”,”木”,”金”,”土”)

あるいは、別表で「番号と曜日表」を作成、表を範囲選択して参照することで［VLOOKUP関数］が使えます。

VLOOKUPで参照する表は、前述のWEEKDAY関数の※曜日名表 などを参考にしてください。

＝VLOOKUP（検索値，範囲，列番号，［検索方法］）

=VLOOKUP(WEEKDAY(日付セル),表の範囲,2,0)

引数の［列番号］を ２にすると、aaa なので 日

引数の［列番号］３にすると、aaaa で　日曜日

引数の［列番号］４にすると、ddd で　　SUN

引数の［列番号］５にすると、dddd で　SUNDAY

EXCEL WORKSHOP TRIAL

EXCEL◇同じ値なのに FALSE

EXCELの2シートで各々計算した値が、同じ金額(True)であればOK という数式を作成したのですが、今月、その数式ツールでFalse が表示されました。

求める値は、[請求金額]＝[支払金額] でシート上は同じ値なので支払金額は決定で、支払OKなのですが、作成した判定の数式 が間違っているようです。何故、同じ値でFalse が表示されてしまったのか検証しなければなりません。

請求金額シートは、請求書を転記しているだけですが、支払チェックシートの方に長い数式が入っています。合計金額は小数点以下2位の値で、条件が等しい整数値になるように加工しています。ツールバーの「小数点表示桁上げ(小数点以下の桁数を増やす)」ボタンで小数点以下を表示させると1円未満の差がありました。

つまりセル上で整数値が表示されていても、EXCELは値の差を見つけて「値がちがう」と言ってきます。

判定する数式に、小数点以下の値を処理するINT関数、TRUNC関数をネストさせてもFalseが出ます。
=INT(数式)
引数が「数式」で、結果にたまたまTrue値が出てくるのかもしれませんが、判定の数式はいつも正確でなければ意味がありません。

INT関数をWeb検索したところ、
=INT(数値)
INT関数の引数は「値」と書かれていました。

数式の結果を、セルに値貼付けするか、テンキー入力しなおさなければ、判定の数式が正しくTrueを表示できないようです。

EXCEL WORKSHOP TRIAL

EXCEL◇RAND()がはたらく

RAND関数のランダム値は、サンプルデータに利用できます。
EXCELがダイナミックな働きをします。
表の値に =rand() を入力して列にコピー、ワークシートを［F9］キーで再読み込みするたびに乱数、ランダムに値が表示され、サンプル値などに使われます。

=RAND() のみでは 1未満の数値が発生しますので、数式を追加して10倍、100倍、1000倍に見せる、 また関数を組み合わると整数値にすることも可能です。

ところで、このRAND関数 =RAND() を、Microsoft Word で使用してみますと、手頃な日本語サンプル文がWORD文書の画面に出てきます。WORDの空白のどこでも構いませんので、

=rand()

と入力して、［Enter］キーを押します。

EXCEL WORKSHOP TRIAL

EXCEL◇PHONETIC・考察

YouTube(インターネット動画共有サービス) で、自作EXCEL動画講座をグローバル配信している管理者が増えています。キーワード　EXCEL、FUNCTION（関数）で検索すると、EXCELの関数に関する様々な動画レクチャーを見ることが出来ます。

検索キーワードを「PHONETIC 」に絞り込むと、、PHONETIC関数の仕様解説、チュートリアルのサイトが検索されます。日本語(Furigana)、中国語(Pinyin)、Hindi、 URDU 、等々、PHONETIC 機能をインストールする説明が見られることから、PHONETIC関数は、 Microsoft Officeの外国語版ユーザ向けのアドイン機能と思われます。Microsoft Japan の製品ソフトは、既定で日本語ふりがな機能やPHONETIC関数のフリガナがセットされているものということです。

さらに、WEBサーチで、キーワード PHONETIC を検索すると、キッズ向けアルファベット学習絵本のような学習教材の動画が多数ヒットします。動画の画面に単語や短文、文字が表示され、それを読み上げるキャラクターが登場しています。画面に描かれている文字や短文がPHONETIC なのか、読みの音声の方がPHONETIC なのかは解りません。

EXCEL WORKSHOP TRIAL

EXCEL◇PHONETIC関数と日本語フリガナ

PHONETIC関数は、漢字をカナ読み「フリガナ(furigana)」に変換できますが、IMEやATOKなど日本語入力システムを使っていると「フリガナ」変換となる日本専用の関数で、日本語版にしかないのでしょうか。

ちなみに、日本語版WindowsOS、英語版(ベータ版)Officeで、PHONETIC関数は、日本語フリガナです。

他国の言語のEXCELで、日本語のPHONETIC関数はあるのでしょうか。

ほかにも、言語が気になるEXCEL関数がいくつかあります。

AMORLINC関数　フランスの会計システムで減価償却費を求める
AMORDEGRC関数　フランスの会計システムで減価償却費を求める

ARABIC 関数　ローマ数字をアラビア数字に変換する
ROMAN関数　数値データをローマ数字表記に変換する

BAHTTEXT(バーツ）関数　は、タイ国の通貨表記バーツテキストに変換されます。

NUMBERSTRING　という関数は、数値が漢字書式に変換されます。
漢字書式は、三種類の表示方法があるようですが、

元データが　１２３　の場合、

１　 百二十三

２　壱百弐拾参

３ 　一二三

EXCEL WORKSHOP TRIAL

EXCEL◇列範囲を絶対参照SUMIF関数

たとえば、SUMIF関数で月別データの合計を集計するとき、関数の引数の参照列範囲を絶対参照(相対参照)するのを忘れていると、当然合計値はおかしくなります。数式を作成して何らかのデータが現れると、とりあえず信用してしまいがちですが要注意です。

参照列を［F4］キーで固定しておかなければなりません。

行の参照セルの値を計算しますが、絶対参照にすると列・行の参照元の値が変わりません。

リストを固定しないと、列方向に数式をコピーするとき参照元リストが下方向にずれてきますので、データの結果をよく見て、1月よりも12月の方向に合計値があまりに少なくなるのはおかしい、ということに気付かなければなりません。

ちなみに、SUMIF関数の参照範囲は1列ですから、正確にはF4キー2回で行固定の相対参照で充分なのですが、引数の範囲選択をしてF4キーを1度たたいて絶対参照でも結果は同じですので、この場合引数の範囲選択を絶対参照、相対参照どちらでも構わないと思います。

EXCEL WORKSHOP TRIAL

EXCEL◇ネストした数式の列

Excelのデータ元表の右列に、数式をつくって結果のデータを作成します。

データ表の1行目で、計算式を試作します。空白セルに 直接＝ (イコール) を入力して、つづけて数式を入力してみます。シンプルな数式なら、数式バーに関数ボックスから検索して関数を呼び出す必要もありません。

条件分岐がある場合など、数式が複雑になりそうな時は、1列で1度に解決出来ません。1列ごとに数式を区切って、空白セルに、左隣セルを参照した数式をつくっていきます。

データ表の一番右の列に結果が出たところで、フィルのコピー、数式を最下行までコピーします。

数列に渡って参照した数式については、あとで説明が必要になりますので、さらに、1列で長い数式にしてみます。

関数を組み合わせて数式の(括弧)で関数の中に別の関数を二重三重…に入れ子にするやり方を「ネスト」と呼びます。左隣りセルを代入した数式をつくる作業を繰返して、結果が合っていたら成功です。最後につくったひと続きの数式が有効かもしれません。

EXCEL WORKSHOP TRIAL

EXCEL◇レポート仕上げは値貼付け

「レポートは、値貼付けで上書きして出してください」と指示をもらいました。

レポート仕上げは値貼付けでよいということは、要するにシートに作成した数式やマクロは納品に必要ないということです。

EXCELデータで納品する時は、作ったマクロを解放して、削除しておいた方がよいです。時間を掛けて仕掛けを考えて、頑張ってEXCELで数式やマクロを作ったのに、見てもらったり評価してもらうことが出来なくて、等と言うスタッフさん達がいたりします。

お客様が求めているのは、会議資料で使う図表やグラフ、データの結果だけです。「印刷して提出して下さい」とだけの場合もあります。せっかく作ったマクロまでは見てもらえないわけなのです。

EXCEL WORKSHOP TRIAL

EXCEL◇IF関数 (じゃない，ではなく，それ以外)

たとえば、A列参照B列数式で、IF関数の引数が
＝IF (A1=0 , True , False )　、ではなくても
＝IF (A1<>0 , True , False )、でも同じ結果ですが、
TrueとFalseの中身が入れ替わります。

つまりは、
＝IF(A列の値=0，1，0）、で済むところで、
＝IF(A列の値<>0， “A列＝0 のときの0” ， ”A列＝0の1” )　、と書いて、

＝IF(A列の値が”0”じゃないので, こっちがTrueなら, NotTrue)

なので、
＝IF (”A1=0” じゃない ”A1<>0”ならば, Trueは”A1=0”のFalseではなく , それ以外False)

些細な事なので、Trueじゃなかったかもしれないのですが、

IF関数（ じゃない，ではなく，それ以外）　

という記述をしています。
これがFalseでいいのだ！

ですが、この法則が必ずしも通用しないIF関数の例が、またあるのですが..

EXCEL WORKSHOP TRIAL

EXCEL◇会社のエクセル制限

プロジェクト先のデータ処理作業で、EXCEL関数がまったく使えない事があります。その理由は、オフィスのパソコンでEXCELが正常に動作しなかったからです。

オフィス・ネットワークのパソコンは、セキュリティ設定やウィルス対策ソフト、アクセス権の設定等などで様々な制限がかかっています。プライベートで自宅で使うパソコンとは使い勝手がまったく違います。通常オフィスでは、特定の関数やマクロ(VBA)を制限している事が多いです。

SUM関数が使えないオフィスでは、計算式の合計セルの値が動いて値が決まらないので、注意しなければなりません。

社内ヘルプデスクで人気の遠隔操作ツールや社内ポータル、あるいはMicrosoft以外のソフトと競合して、EXCELの動作がとても鈍くなる事があります。EXCEL関数に依存しない正確なデータ作成を推奨しているプロジェクト先では、作業が慎重になります。SUM関数の計算結果が正確かどうか、そのオフィス・ネットワーク環境でEXCEL関数が正常に使えるかどうか、なによりまず、事前に確認しなければならない事があります。

数式が正確に動作しない場合は、電卓や筆算等であらかじめ計算しておいて、数式のない空白のセルに手入力で結果の値を入力すれば、問題ありません。

EXCEL WORKSHOP TRIAL

EXCEL◇関数ばやりのころ

Excel関数、三百数十種、Officeバージョンが新しくなると既定の関数が更新されて、関数の種類が増えていきますが、EXCELマスターするために、たとえばEXCEL全関数を全制覇する事でも良い、有意義なわけです。

Microsoft Office Excelは元々米国の会社で開発された他国向けのアプリケーションですので実際、日本の社会でEXCEL関数は全部使わないと思います。関数のカテゴリ「数学」「財務」「統計・データベース」「エンジニアリング」は、それぞれ業界が異なりますので、全EXCEL関数をマスターするということは、業界を股掛けするような羽目になりかねません。記憶力マスター、一般教養でEXCELマスターする程度で差支えないかもしれませんが、EXCEL関数を全部記憶していても、仕事ができるとは限りません。EXCEL関数を全然知らなくても、仕事で支障はありません。

それでも、インターネットやEXCEL本で学習していて、「この関数はどういう時にどうやって使うの？」とふと思った時に、EXCELを極めて全部知っているという物知りさんがいてくれると、便利だと思います。ふと気になっちゃうので、ヘルプデスクに電話問い合わせしてみると、話し相手になってくれるたりして、連帯感を感じることでしょう。

EXCEL WORKSHOP TRIAL

EXCEL◇合計をROUND関数で四捨五入

行の集計値を千円単位としてROUND関数で千円未満を切捨てた数値にして、この表の結果を合計行のSUM関数で計算すると、実際の残高と合わないということがよくあります。

全体の合計を計算するときは千円未満切捨てした概算とは別の列に、1の位まで正確に表示させた元データが必要で、1円まで正確なデータ列を合計した結果を、ROUND関数で千円未満切捨てしなければなりません。金額にもよりますが、前述した、切捨てして千円未満の各行を合計する、その差額分が結果に影響しているのがお解りになると思います。

EXCEL WORKSHOP TRIAL

EXCEL◇ふりがな五十音昇順

PHONETIC関数・・Excel表の地名や人名のふりがな、どうしよう、ふりがなが全部合っているデータと、一部合っている、全然合ってないデータが出てくるので困ってしまいます。PHONETIC関数の法則で、漢字の読み方が、音読み訓読みが適当に組合せてあります。元データにふりがなが無い時、いちおうPHONETIC関数を使ってみるのですが、地名や人名は固有名詞なので正確なふりがなを求めてしまいます。PHONETIC関数の読みが正確かどうかを調べて、違っているふりがなをキーボードで入力修正しなければなりません。正しい地名や人名のふりがな入力する作業を考えると、あまりに時間がかかり過ぎます。

Vlookup関数でデータ抽出する場合、検索値列の地名や人名を五十音順の昇順に並べ替えしますが、ふりがなは省略できます。
表の左端列の地名や人名リストを昇順で並べ替えする場合に、ふりがなは必要ではありません。元表の市区町村名を漢字表記のまま、並べ替えの[A-Zボタン]で昇順にします。PHONETIC関数の結果と同じことで、EXCELの仕様で、昇順は固定され決定されているはずだからです。Vlookup関数で検索するわけですから、読み方が正確な五十音順でなくても、Excelが認識する昇順であれば、大丈夫です。

EXCEL WORKSHOP TRIAL

EXCEL◇四則計算と SUM関数

Excel関数をマスターして、様々なサンプル例を学ぶことになりますが、実際に職場では、システムツールなどで、それほどレベルの高い関数式やプログラムを使っていません。たとえば、月次決算表にしても、合計セルはSUM関数で集計をするだけです。

もともと決算書類は手書き伝票を転記して、電卓や算盤で集計していたものを、今はシステム入力するパソコンで作業しているわけで、それほど複雑な計算はしていません。足し算ならA＋B＋Cなど、関数も使わずに、四則計算式で充分なのです。

データが十数行以上になると、SUM関数が必須です。プラス記号(＋)で各行を加算入力すると、数式が長くなりすぎるので、四則計算式だけで数式を作ると、限界を感じます。

SUM関数なら、マイナス値のセル・データでも気にせず、セル範囲を加算する計算式になるので、意外と便利です。

EXCEL WORKSHOP TRIAL

EXCEL◇データベースの合計行

EXCEL表の合計は、たいてい一番下の行にあります。

しかしながら、新着データが日々追加されるような表は、EXCELデータの行数が続々と増えていきます。合計行が最下行にあると、合計行の手前に新着データを追加、挿入しなければならない仕組みになってしまいます。また、データが増えて数百、数千行になると、一番下の行が集計結果では、見づらくなってくるし、数式がずれてくるかもしれません。

そこで、ワークシートの上方に、合計やデータ表の集計結果を表示するスペースを作ることを考えました。

データ表の一番上の項目行は、10行目あたりにします。次行からデータ表を下方へ追加していきますが、ワークシートには充分に余裕があります。データベースの項目行にオートフィルタ設定できますので、条件付きデータ抽出を実行することも可能です。

合計の値や、件数など、１～９行目の空白のセルどこかに、関数式を作って、集計結果が見られるように準備しておきます。データ表とは異なるフォントやフォントサイズにしたり、分かりやすくデザインすることもできます。データ管理が比較的、楽になると思います。

EXCEL WORKSHOP TRIAL

EXCEL◇EXCEL帳票シート試作

ここ数年の不景気から、なんとなく正気にかえってきて、バーチャル的マイナス勘定をちゃんと見直そうかと、そうしないと、節約生活を楽しめない、もっと遊びたいのに実現できない、夢を見ることしかできません。

昨日は「収入振替概算シート」をいうツールを作成してみました。私は個人で普通預金通帳を複数の銀行に作っているのですが、10数年来お給料が1社のみでなく、入金日も会社毎に違うので、銀行引落しなど残高不足で引落エラーになったり、その後のすべての支払が滞ってしまわないように気を付けています。

「振替」用の数式がうまく出来ませんでした。結局数式を使わずに、合計金額を手入力をする事で落ち着きました。

EXCEL WORKSHOP TRIAL

EXCEL◇特定被災地は督促を除外する

昨年2011年、情報研究所で省庁から依頼の企業調査、毎年数万社の事業者宛てに調査票を郵送して、回答を返送してもらいます。提出締切り期限に未提出の事業者に督促のレターを郵送するのですが、「東日本震災の被災地へ督促を送らないで下さい」と指示を受けたので、Excelリストを新規作成しなければなりません。
「1　特定被災地方公共団体」及び「2　特定被災地域市区町村」の2種類があって、いずれにも属する市区町村があります。「１」「２」どちらも送付しない、または「１」は送付しない、「２」は送る。それぞれの列で、リストの住所が「１」か「２」に含まれるかを調べて、「１」 OR「２」を抽出してから、その結果を全リストから除外します。「催促しない」住所に連絡しないように、慎重に作業して確認しなければなりません。