haku1569　Excel でらくらく データ分析！

Excel のTEXT関数を使って数字を書式設定して表示させたような文字に変換してみます。
そうすれば、演算子“＆” を使って文字列の連結ができるようになりますよ。









続きを読む

Excelの関数でデータの回帰直線の傾きを求めてみます。
SLOPE関数を使用します。
“SLOPE”（スロープ）とは、斜面、傾きと言う意味です。





続きを読む

Excelの関数を組み合わせて複数の重複データを抽出して表示させてみます。
VLOOKUP関数とCOUNTIF関数を使用します。

この様なデータがあって例えば受注日が2012/6/14のデータを関数を使って抽出して表示させようと思います。


普通にVLOOKUP関数を使って2012/6/14で検索しても検索対象範囲の上から一番最初に見つかったデータしか表示されません。（VLOOKUPの式を下にコピーしても次のデータは検索してくれません）
(-_-;)


んで、どうしたら良いかというと、COUNTIF関数を使って重複するセルに番号を付けてしまいます。
それから、その番号と日付を組み合わせた重複のないデータ（ユニークなデータ）を新たに作ってそのデータを使ってVLOOKUP関数で検索しようというわけです。

受注日の左隣に列「重複番号」を追加してそこにCOUNTIF関数の式を入力します。
統計関数から“COUNTIF”を選択します。


引数を設定します。
ここでは、「範囲」の設定が肝（キモっ！？）になります。
受注日の先頭のセル“C2”（後で下にコピーするので絶対参照の“$”をf4キーで付けておきます）
で、範囲の最後は今式を入力している行のセル“C2”これは絶対参照にはしません。
それによって、この式を下にコピーしていった時範囲の先頭は固定され範囲の最後はコピーするに従って下に広がって行きます。


「検索条件」は検索する受注日を入力するセルを指定します。
ここでは別のシート「検索シート」のセルになります。


ここも、下にコピーしていくので絶対参照にして固定しておきます。


と、こんな感じになります。
“2012/6/14”は“1”から“6”まで番号が振られました。
でその下の“2012/6/12”とかの“2012/6/14”以外のデータのところはカウントされないので範囲の中の“2012/6/14”の数は増えないので“6”のままになっています。
でちなみに、この下の方にも“2012/6/14”のデータがあるのでもっと番号が振られています。


で、「検索シート」にVLOOKUP関数で検索して表示させる表を作ります。
ここでも、肝い部分があります。
こんな様に上の受注日に検索したい受注日を入力すると下の表にその結果が表示されるようにします。
検索結果は複数あるので�ｂ�振っているのですが、実はこの�ｂｪ重複した受注日に付けた番号に相当するようにするわけです。
で、例えば“6”の様に重複番号自体も重複しているのですが、VLOOKUPは最初に登場したデータしか表示されないので重複していても気にする必要はありません。
つまり、VLOOKUP関数の重複データに対する欠点を克服するためにその欠点を利用しているということです。


早速、在籍支社下のセルC5にVLOOKUP関数を入力していきます。


引数を設定します。
検索値：ここで、表の�ｂﾌ値を指定します。
この式は後で右にコピーするので列だけ固定しておきます。
範囲：検索対象の「重複番号」の列が先頭になるように範囲指定して絶対参照にして固定しておきます。
列番号：“3”で「在籍支社」の値を指定します。
検索方法：完全一致の“FALSE”を指定します。


すると、この様に1番目の2012/6/14のデータの「在籍支社」のデータが表示されました。


そうやって、式をコピーし、VLOOKUP関数の引数の「列番号」を一つずつ手入力で増やしてやるとこんな結果になりました。
“2012/6/14”のデータは12個ありました。



「受注日」を“2012/6/12”に変えるとこの様に検索結果が表示されます。






関連ページ
VLOOKUPで列方向の検索をして表示させてみた
COUNTIFで条件に一致するセルの数を数えてみた
VLOOKUPとAND関数で複数の検索条件からデータを抽出して表示させてみた
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で既知のデータを元に未知の値を予想してみます。
TREND関数を使用します。
“TREND”（トレンド）とは、傾向、動向　と言う意味です。

さて、前回はあるイベントに出店したソフトクリームの屋台の販売量をその日の気温からFORECAST関数を使って予測してみましたが販売量を左右するのはその日の気温だけでしょうか？もっと、要因があるのではないでしょうか？

気温の他に考えられるのは、そのイベント訪れた来場者数。イベントの内容によって、来場者数が変化します。来場者数が 少ないとソフトクリームの販売数も少なく、来場者数が多いと販売数も多くなると予想できますよね？

その日の気温、来場者数とソフトクリームの販売数量のデータがあります。
このデータから気温が35℃で来場者数が1万人、2万人、3万人の時の販売数量を予測してみましょう。


統計関数から“TREND”を選択します。


引数を設定します。
既知のy：販売数量のデータ範囲を選択します。
既知のx：気温と来場者数のデータ範囲を選択します。
新しいx：予想する気温と来場者数のデータ範囲を選択します。
ちなみに、この表はテーブルに設定しているため、このような列のタイトル表示になります。
定数：空白にしておきます


OKしてコピーするとこのように予測値が計算されました。


予想原理について
このTREND関数もFORECAST関数と同じく回帰直線を使って値を予測していますが、重回帰分析による直線近似で近似式は
y=ax1+bx2+c
となり、この近似直線と実際の各データの誤差の二乗が最小になる時のaとbとcを求めています。これを最小二乗法と言います。
ちなみに、yはソフトクリームの販売数量、x1が気温、x2が来場者数になります。



関連ページ
FORECAST関数で値（例えば販売数量とか）を予想してみた
関数一覧（カテゴリ別）
関数一覧（名前別）

Excelの関数で既知のデータを元に未知の値を予想してみます。
FORECAST関数を使用します。
“FORECAST”（フォーキャスト）とは、予想する、予測する　と言う意味です。
“forecast the weather”と言うと「天気予報する」です。

例えばこんな、あるイベントに出店したソフトクリームの屋台のその日の気温とソフトクリームの販売数量のデータがあります。
気温が高いと売り上げが上がるだろうと予測できますよね。


グラフを描いてみるとこんな感じになります。


週間天気予報を見ると更に気温が上がって、35℃になる予報がでています。
さて、気温が35℃になったらソフトクリームは何個売れるでしょうか？
1日分の材料は何個分用意しておけばいいでしょうか？

と言うことで、FORECAST関数で気温が35℃になった時のソフトクリームの販売数量を予想してみます。


関数の挿入で「統計」から「FORECAST」を選択します。


引数を設定します。
X：予想する値Yの時のXの値。ここでは販売数量がYで気温がXなので、このXは気温35を入力しているセルを設定します。
既知のy：既に分かっている販売数量のデータ範囲（D4：D18）を設定します。
この表はテーブルに設定しているため、このような列のタイトル表示になります。
既知のx：既に分かっている気温のデータ範囲（C4：C18）を設定します。


OKするとこのように予測値が“364.45”と計算されました。

予想原理について
このFORECAST関数は回帰直線を使って値を予測しています。
回帰直線とは近似直線のことで近似式は
y=ax+b
となり、この近似直線と実際の各データの誤差の二乗が最小になる時のaとbを求めています。これを最小二乗法と言います。
Excelでの最小二乗法の計算についてはコチラをご覧ください。

んで、Excelではグラフで簡単に近似直線（曲線も）を描くことが出来ます。

プロットされているデータをクリック（選択）して、右クリします。
メニューから「近似曲線の追加」を選択します。


近似曲線の書式設定が開きます。
「近似または回帰の種類」を「線形近似」にします。
「グラフに数式を表示する」のチェックを入れます。
それから、35℃の予測も確認したいので、「前方補外」を“2”にします。
「前方」とは将来のこと。ここではグラフの右側つまり気温が高い方。
「後方」とは過去のことの予測になります。


んで、グラフの体裁を整えて、回帰直線（近似直線）はこうなります。
この直線の式も表示されています。


気温35℃の時の販売数量は360位になっているのが分かります。
ちなみにこの式のxに35を入れて計算するとｙはFORECAST関数で計算した値とほぼ一致します。



関連ページ
ソルバーを使った最小二乗法
関数一覧（カテゴリ別）
関数一覧（名前別）

Excelの関数で検索対象の相対的な位置を数値で表示させてみます。
MATCH関数を使用します。

「赤」〜「茶」までの色の一覧表があります。
この5行の表の中で指定した色が上から何番目の位置か数字で表示してみます。
下の「色」に指定する色を入力し（今青を入れています）「位置」の所に表示してみます。


関数の挿入で「検索/行列」関数から"MATCH"を選択します。


引数を指定します。
検索値：青を入力したセルを指定します。
検索範囲：5色の色を入力した範囲を指定します。
照合の種類：“0”で検査値と等しい値を検索します。


と、"2"と表示されました。
上から2番目に“青”が入力されているためです。


“茶”と入れると“5”が表示されます。


横向きの表にしても左からの位置が表示されます



関連ページ
ユーザー定義の書式に使う記号の意味
関数一覧（カテゴリ別）
関数一覧（名前別）
数式パレットを表示させてみた
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数でセルのいろいろな情報を表示させてみます。
CELL関数を使用します。

この関数を使用すると、セルの位置、ファイル名やシート名、セルに設定されている書式等を表示できます。

C列にB列のセルの情報を表示させてみます。


関数の挿入で「情報」関数から"CELL"を選択します。


引数の選択画面が表示されます。
検査の種類：12種類の情報から表示させる1つの情報（検査の種類）を入力します。
または、検査の種類を文字入力したセルを指定します。
ここでは、検査の種類"address"を入力したセルを指定しています。
参照：検査するセルを指定します。
ここでは、検査の種類を入力したセルを検査してみることにしました。


するとこのような結果になりました。
"address"って言う検査はセルの位置を表示させることができます。


"=CELL"と関数を手入力すると検査の種類が一覧から選択できます。


12種類の検査の種類とは、、
address：セルの位置
col：セルの列番号
color：負の数（マイナスの数字）の場合色で表示させる（赤とか）書式設定をしていると"1"、そうでない場合は"0"
contents：セルの値
filename：パス＋ファイル名＋シート名
format：セルに設定されている書式を記号で表示します
parentheses：正の数、または全ての数をかっこで囲む書式を設定している場合"1"、そうでない場合は"0"
prefix：左詰め表示" '"、右詰め" ”"、中央揃い"^"、量揃え"￥"、それ以外" "（空白）
protect：セルがロックされている時"1"、そうでないとき"0"
row：セルの行番号
type：セルが空白の時"b"、文字列の時"l"、数字の場合"v"
width：セル幅（整数）

で、こんな感じになります。


検査の種類"format"での書式の記号はこうなります。
このユーザー定義の書式の記号についてはコチラをご覧ください。




関連ページ
ユーザー定義の書式に使う記号の意味
関数一覧（カテゴリ別）
関数一覧（名前別）
数式パレットを表示させてみた
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で指定した期間の土日を除いた日数（稼働日）を求めてみます。
NETWORKDAYS関数を指定します。

ネットワークデイズ？
英語では稼働日のことは"working days"とか"effective working days"とか、同様の意味で"business days"（営業日）等があります。
"net work days"とは言わないようです。
"net"と言うのはネットワークの"網"という意味と、"グロス"と"ネット"の"正味の"って意味があります。
なので、"net work days"とは「正味の働く日」（稼働日）と解釈することもできますね。

例えば、この開始日から終了日までの土日を除いた稼働日を求めてみましょう。


関数の挿入で「日付/時刻」関数から"NETWORKDAYS"を選択します。


引数の選択画面が表示されます。
開始日、終了日、祭日を入力します。
祭日というのは土日以外の祝日などが有る場合はその日付を指定します。


すると、稼働日が求められました。



関連ページ
関数一覧（カテゴリ別）
関数一覧（名前別）
数式パレットを表示させてみた
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、

#REF!、#NUM!、#NULL!とか）

Excelの関数で検索対象の左側の列の値を表示させてみます。
LOOKUP関数を指定します。

さて、みなさんは"VLOOKUP関数"って知ってまよね？
Excelでとりあえずこの関数を使いこなせてればOKみたな定番の関数なわけですが、、、
検索範囲を指定して、その範囲の一番左端の列の値を検索してその右側の列の値を列番号を指定して表示させるという関数です。

表（テーブル）の構成って普通というか、データベースのテーブルでは左端にIDと言うかその行のデータ（レコード）を特定できる様にする重複しない値の列（フィールド）を作ります（あるいは自動的に作られます）
なので、「表のデータを検索する」イコール「一番左のIDを探してデータを特定し右側に広がっている列の値を表示させる」と言うのがデータベース的発想の表の検索になるわけです。

ですが、実際の検索はそんな模範的な検索だけで済むわけはありません。
検索対象の左側の列の値を表示させたい！
な、時に使用できるのが"LOOKUP"関数です。
但し、いろいろ制約条件があって、上手く検索できない時もあるようなのでご注意を！

例えば、右側の"部品名"と"部品番号"の表があります。
ここで、部品番号を検索して部品名を表示させてみましょう。
"VLOOKUP"関数ではできないですよね。


関数の挿入で「検索/行列」関数から"LOOKUP"を選択します。


引数の選択画面が表示されます。
上の"検索値,検索範囲,対応範囲"を選択して「OK」します。


引数を指定します。
検索値：B列の部品番号を指定します。
検索範囲：右側の表の部品番号の列を指定します。
　　　　　このセルで入力した式を"C8"までコピーするので行番号には絶対位置（$）にします。
対応範囲：表示させる部品名の列を指定します。
　　　　　これも行番号は絶対位置にしておきます。


で、"C8"までコピーするとこのように部品番号に対応した部品名が表示されました。



関連ページ
VLOOKUP/列方向の検索
IFERROR関数でエラーの時の表示を設定する
関数一覧（カテゴリ別）
関数一覧（名前別）
数式パレットを表示させてみた
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で文字列中の指定された文字列を他の文字列に置き換えます。
SUBSTITUTE関数を使用します。

例えば、"シートA"にA-001と入力した時に、


"シートB"に自動的に"B-001"と表示させ、


"シートC"には自動的に"C-001"と表示させてみましょう！


"シートB"で、関数の挿入で文字列操作関数から"SUBSTITUTE"を選択します。


引数の設定します
文字列：置換する文字列が含まれる文字列を設定します。
　　　　ここでは、"シートA"の"A-001"が入力されているセル"C4"を設定します。
検索文字列：置換対象の文字列を設定します。
　　　　　　ここでは"A"を設定します。
置換文字列：置換する文字列を設定します。
　　　　　　ここでは"B"と設定します。


するとこのように"A-001"の"A"が"B"に置換されて"B-001"と表示されました。


同様に"シートC"では検索文字列を"A"、置換文字列を"C"とします。



おしまい！
あっ、どうせならシート名を取得して表示させたいですよね。



関連ページ
関数一覧（カテゴリ別）
関数一覧（名前別）
数式パレットを表示させてみた
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）