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

Excelの “VLOOKUP関数” で検索する時の検索場所の指定は検索範囲の中の左からの列番号で指定します。
そこで、検索場所の列の左側に列を挿入してしまうと本来検索する列が右側にズレるため間違った列を検索してしまうことになってしまいます。
そこで、そのズレを防止するために、検索する列番号を“MATCH関数”を使って求めてやります。


“COLUMN関数”でも同じような効果があるのですが、“MATCH関数”の方がより間違いなく検索できます。

こんな様な3列の表があります。その右側でVLOOKUP関数で得意先コードから都道府県を検索しています。



�@は都道府県の列番号を“3”としたVLOOKUP関数です。


�Aが“COLUMN関数”で見出しの“都道府県”のセル“D3”の列番号を求めてそれを列番号にしています。ただここではD3の列番号は“4”になるため“-1”して“3”にしています。


�Bが“MATCH関数”で表の見出しの範囲（B3:D3）から“都道府県”の列番号を求めています。


いずれも、得意先コード“2”に対し“福岡県”が正しく検索されています。


では、意地悪テストです。
得意先コードの右側に列を挿入します。
すると都道府県の列は右にズレて表の中で4列目に変わります。
すると�@の検索列を“3”と指定したVLOOKUP関数では“小料理なんごく”が表示されてしまいました。


�@では列の挿入によりVLOOKUP関数の検索範囲は$B$3:$E$23に拡大されるため都道府県の検索列は左から“4”番目にならなければいけません。


�Aの“COLUMN関数”では列の挿入により検索セルも相対的に右にズレて“E3”を検索するようになったため、“E3”の列番号“5”“-1”で4になって都道府県が表示されています。


�Bの“MATCH関数”は列の挿入により検索範囲がB3:E3に拡大し“都道府県”と言う文字を検索し“4”を返しVLOOKUP関数は都道府県から検索しています。


次は、表の中ではなく外側（右側）に列が送入された場合です。
ここで、都道府県が正しく表示されないのは�Aの“COLUMN関数”で指定した場合です。


�@では“VLOOUP”の検索範囲が表の右側の列の挿入により“$C$3:$E$23”に右に移動するため“都道府県”の列は表の左から3番目であることに変わりはありません。ので正しく“福岡県”と表示されました。


では問題の“COLUMN関数”を使用した�Aの場合です。
列の挿入により検索するセルは“E3”になり、右にズレました。ので、“5”“-1”で“4”になり、VLOOKUP関数の検索範囲の外を指定してしまっています。


“MATCH関数”を使用した�Bの場合は列の挿入により検索範囲が“C3:E3”と右に移動しているだけなので“都道府県”の検索結果は“3”になり正しく“福岡県”が表示されました。


と言うことで、VLOOKUP関数を使って検索したいのは絶対的な列番号ではなく表の中の“都道府県”の列番号であるわけなので、表の中の“都道府県”の列番号を返してくれる“MATCH関数”を使うのが妥当なのは当たり前と言うことになる。。。



関連ページ
・VLOOKUPで列方向の検索をして表示させてみた
・COLUMN（COLUMNS）関数で列番号を求めてみた〜

Excelの関数を使って0.5単位で四捨五入してみます。

0.5単位で四捨五入というのは、
例えば、
“-0.75”〜“0.25未満”は“0”
“0.25”〜“0.75未満”は“0.5”
“0.75”〜“1.25未満”は“1”
という感じで数値を丸めることです。

つまりこんなこと
15.2→15.0、15.3→15.5、15.6→15.5、15.8→15.5、16.0


で、これを関数を使って計算してみます。

Excelで用意されている、端数処理（数値の丸め）は、切り捨て、切り上げ、四捨五入があります。0.5単位の四捨五入はありません。
なので、どうするかと言うと、元の数を2倍して四捨五入すればいいわけです。
で、それから2で割って元に戻します。
すると、
“-0.75”〜“0.25未満”は“0”⇒“-1.5”〜“0.5未満”は“0”
“0.25”〜“0.75未満”は“0.5”⇒“0.5”〜“1.5未満”は“1”
“0.75”〜“1.25未満”は“1”⇒“1.5”〜“2.5未満”は“2”
となります。


具体的にはこうなります。


因みに、式をみてみましょう。
式を表示させるには、「メニュー」の「数式」から「数式の表示」を選択します。


と、こうなります。
四捨五入は“ROUND関数”を使用します。



次ページ：
前ページ：



関連ページ
ROUND関数で四捨五入してみた
特集! 端数処理（数値の丸め方）について
Excel関数一覧（カテゴリ別）

ExcelのSUMIF関数を使って予算表を見える化してみました。

例えば、年の初めに今年の主な出費の予定を立ててみました。
で、実施する月に“〇”を付けた時に下に金額が表示されるようにしようと思います。
もちろん、7月の様に複数実施する場合は合計金額が表示されるようにしようと思います。


なんで、こんな表を作るかというと。。。
普通、こんな表を「クロス集計表」と言います。縦横から交わった（クロスした）値を集計するみたいな意味なんでしょうきっと、で、それってあくまで集計結果を見やすく表示する手段なわけなんですが、今回作ろうとしている表は、データを作るための表なんです。
どういうことかつーと、
普通まず初めにこんな出費の予定表を作ります。


で、それを元にクロス集計表を作るのですが、こんな場合はと言うか、たいていの場合ピボットテーブルを使うとそれが簡単にできます。
「ピボットテーブル」を挿入します。


で、行ラベルに「項目」、列ラベルに「実施月」、値に「予算」を設定します。


すると、こんなクロス集計表が自動的に出来上がります。


なんですが、最初の出費の予定表を作る時に項目の脇に実施月を入力するのはちょっとやり辛いというか、全体が見え辛いと思いませんか？
できれば、横軸に1月〜12月の1年があって、その中から月の出費金額を確認しながら、出来れば出費が偏らないようにとか、ボーナス月に合わせたりとか、季節商品の場合はその時期を見計らったりとか、、、で、“〇”を付け替えるとリアルタイムに月の出費金額が更新される様な表があると予定を立て易いと思うんです。でしょ？
つまり、見える化です。

というわけで、お待たせ致しました。ようやく本題です。
お急ぎの方は、前段の部分を飛ばしてここから読み始めてください！？

最初の表の月の合計の欄に“SUMIF”関数を挿入します。


範囲は1月の列の範囲


検索条件は“〇”。つまり、“〇”が入力されているセルを探します。
合計範囲は予算の列範囲にします。


で、この1月の合計のセルに入力した式を12月までコピーするの合計範囲（予算の列範囲）は“$”マークを付けて絶対参照範囲にしておきます。


で、1月は何も実施予定がありません（“〇”がない）ので“0”です。


で、これを12月までコピーするとこの様に各月の予算の実施金額の合計が表示されました。
〇を付け替えると自動的に金額が集計しなおされます。


これで、予算立てがかなりやりやすくなりました！

・・・？

あれっ、間違った！
珈琲メーカーが3月と8月にダブって“〇”が付いちゃってます。
んで、車検が抜けっちゃってます。
8月に実施予定の車検の“〇”が間違って珈琲メーカーに付いてしまってました。。。

ありがちな間違いですよねぇ〜。。。人間だから。。。こう言うの“ヒューマンエラー”って言うんでしょ？
こんな家庭の出費予定なら、珈琲メーカーは2台は購入しないのでよく確認すれば気が付きますが、会社の予算なんかだと、同じ物を年に複数回実施する場合もあるので、この様なミスが発見しづらくなします。

予算と実際に“〇”を付けた合計金額が合っているかどうかをチェックするようにしてみましょう。

まず、一番右端の列にチェックさせる式を入力します。
“COUNTA関数”を使って、範囲内の空白でないセルの個数、つまりここでは“〇”の数を数えます。


と、「パソコン買替え」は2月に“〇”が付いているだけなので“1”になります。


次に、それに予算の金額“\100,000”をかけます。
すると、1*100,000 で“\100,000”となりました。


で、今度はその結果と予算の金額が合っていたら“OK”、そうでなかったら“NG”と“IF関数”を使って表示させます。


すると、このように、“珈琲メーカー”と“車検”が“NG”と表示されました！


このままでは分かり辛いので“NG”を目立つようにしたいと思います。
チェックのセル範囲を選択して、「条件付き書式」を選択します。


「セルの強調表示ルール」で「指定の値に等しい」を選択します。


“NG”の書式をこの様に（既定値がこうなってます）します。
注）NGはダブルクォーテーション（“ ”）は要りません。


するとこのように目立つようになりました。


で、修正しました。



めでたし、めでたし！

関連ページ
SUMIF関数で条件に一致したセルの合計を求めてみた
クロス集計表を簡単に作成する

で、問題の珈琲メーカーなんだけど、先日実際にこれを購入しました。
ミル付きの珈琲メーカーはたくさんありますが、それっていうのはミルが外付けされているような構造で、粉にした珈琲を手動でドリッパーに供給しなければなりません。
それに対し、全自動珈琲メーカーと言うのは豆を入れてスイッチを入れるだけで自動で粉がドリッパーに落ちて珈琲がはいるもので、国産だとパナソニックと最近出てきた象印の2機種位ではないでしょか。
購入したのは珈琲メーカーでは古くから作っているパナソニックの NC-A56-K
豆を挽く音と漂う香り、いい。。。

NC-A56-K【税込】 パナソニック 沸騰浄水コーヒーメーカー　ブラック Panasonic [NCA56K]【返品種別A】【送料無料】【RCP】 価格：23440円（税込、送料無料) (2017/7/23時点)

Excelで、数字の頭に0を付けて桁を揃えてみます。

例えば、エクセルで何かの台帳の様なのを作っていて、採番（番号を採る）時、記号と組み合わせた数字が必要だったりする時ってありますよね？
あるでしょ？

その時、皆さんはどうしてますか？
いろいろやり方はあると思いますが、今回はこんな方法を紹介してみます。

最初は数値があって、それの頭に“0”を付けて桁合わせします。
今度はそれと文字を組み合わせた番号（文字）を作ってみます。


こんな数値があります。


これの頭に“0”を付けるにはユーザー定義で書式設定をする方法があります。
“000”とすると頭に“0”を付けた3桁の数字にすることが出来ます。


と、こうなりました。


で、今度はその頭に“A-”を付けたいので、“&”で2つの文字を繋げようと思います。


とっ！あら不思議！？
“0”が消えちゃいました！


なんつって、わざとらしくてスミマセン。
こやって、正解は別にあるのに間違った例から説明し始めるのっていかがなものでしょう。。。
「結論を先に言う！」これが私の常日頃のモットーなんですが。。

“0”が消えたのは、セルの書式で“0”が表示される（見える）様にしてただけなので、セルの中身は（実体には）“0”は付いてないからなんです。
例えば、7月17日の実体はシリアル値“42933”みたいな、、
「実は私・・・」

で、どうすっかというと、
“TEXT関数”を使ってみます。これは数値を文字列（数字）に変換する関数です。
つまりこれを使うと、「もう、数値には戻れなくなってしまうのです」
そう、セルの書式設定で見かけを変えた“001”と“002”は足し算をすれば“003”になりますが、“TEXT関数”で文字列になった“001”はもう計算はできません。
そこんとこ、よくわかっといてくださいね。

関数の挿入で「文字列操作」から「TEXT」を選択します。


値：数値の入力してあるセル
表示形式：ここでダブルクォーテーション（“”）で囲んで000
これで、3桁になります。2桁にしたかったら“00”


で、こうなります。
これは戻れない姿です。見た目はさっきのと一緒なんだけど、


で、今度こそ“&”で“A-”と繋げると、、


出来た！




関連ページ
TEXT関数で日付のシリアル値を文字の日付にしてみた
ユーザー定義の書式に使う記号の意味

関数一覧（カテゴリ別）
関数一覧（名前別）

Excelの関数を組み合わせて複数の検索条件からデータを抽出して表示させてみます。
VLOOKUP関数とAND関数を使用します。

この様なフォトブック（デジカメのデータから写真本を作るサービス）の「価格表」があります。
本の形、大きさ、ページ数で値段が変わってきます。


別のシート「価格抽出」でこの様に“形状”、“サイズ”、“ページ数”の3つの検索条件を入力すると、「価格表」から価格をVLOOKUP関数を使って表示させるようにしてみます。


「価格表」の左端に列（“条件一致”）を追加して論理関数から“AND”を入力します。


これは、「価格表」に入力されているデータが、「価格抽出」で入力された条件と一致するかどうかを調べます。“AND”関数を使うのは、3つの条件が全て一致しないといけないので“AND（且つ）”を使用しています。間違って“OR（又は）”を使ってしまうと、1つでも合っていると条件一致とみなされてしまいます。
「価格表」の一行毎のデータ（レコード）の“形状”、“サイズ”、“ページ数”が「価格抽出」の“形状”、“サイズ”、“ページ数”と等しいか（“＝”か）調べて、それを“AND”で繋げます。
３つ全て一致していると“TRUE”、一つでも違っていると“FALSE”が表示されるというわけです。


実際の引数の設定はこうなります。
ここの入力した式は「価格表」の最後の行までコピーするので「価格抽出」の“Sheet2”の参照セルは“$”を付けて絶対参照にして固定しておきます。


するとこの様に、1行目は“FALSE”になりました。
サイズは一致してますが、形状とページ数が違ってますよね。


で、この式をコピーしていくと1つだ一致したデータが見つかりました。
「条件付き書式」で“TRUE”の時に背景色を付ける様にしておくと目立って分かりやすくなりますね。


で、ここまで出来たらあとは「価格抽出」で“VLOOKUP関数”を使って「価格表」の“TRUE”のデータの“価格”データを表示させてやればいいだけです。
つまり、VLOOKUP関数の設定できる検索条件は１つだけなので、その前にAND関数で複数の条件での検索結果を調べておいてしまうわけです。


引数の設定です。
「検索値」は手入力で“TRUE”を入力します。


「範囲」は「価格表」のデータの範囲を指定します。


「列番号」は“価格”が入力しされている列の位置（5番目）
「検索結果」は“FALSE”を入力します。


と、この様に“\4,500”と表示されました。


「形状」を“正方形”に変えてみます。
このように予めドロップダウンリストを設定しておくと選択しやすくなりますし、入力ミスによる誤検索も防止できます。


と、一瞬で（当たり前か。。）“\3,000”と表示されました。


「価格表」を見てみると。。。“\3,000”が“TRUE”になってました。。




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

時間の集計計算って簡単なようだと思っても、上手くいかなかったりすることありませんか？
今回は、Excelの関数を使って時間（時刻）を設定した単位時間で丸めたり、切上げたり、切り捨てたりしてみます。
MROUND関数、CELING関数、FLOOR関数を使用します。

こんな業務日報があります。
業務を行った日、開始時刻、終了時刻とどんな業務を行ったかの内容が入力されています。
んで、開始時刻、終了時刻は1分単位で入力されてますね。。スゴッ！
あっ、ちなみにこの表は“テーブル”に設定しています。


まず、作業時間を求めてみましょう。
終了時刻から開始時刻を引けばその差が作業時間になりますよね。


んで、計算結果が小数点の数字（シリアル値）になっているので、書式を“時刻”に変更します。


すると、こんな感じに作業時間の集計が出来ました。1分単位で集計されてますね。


時間を丸める
では、これを“MROUND関数”を使って10分単位で丸めてみましょう。
ちなみに、“Round（ラウンド）”は数値を丸めるという意味で“M（Multiple）”（マルチプル）は倍数という意味で、“MROUND関数”は指定した値の倍数になるように数値を切上げ、切り下げを行う関数です。


「数式」「数学/三角」から「MROUND,MATH」を選択します。
(Excel2010以下では“MROUND”になります )


数値：作業時間が入力されているセルを指定します
倍数：ここでは10分単位で丸めるので“0:10”と入力します（ダブルクォーテーションは不要です）


「OK」で10分単位で丸まってるはずなんですが、、、


シリアル値ではわからないので、これまた書式を時刻に変更すると、このように綺麗に10分単位で丸められました。


時間を切上げする
では次は、10分単位で切上げをしてみます。


今度は“CEILING関数”を使用します。“シーリング”というのは“天井”、“上限”という意味がありますが、数値の端数処理（丸め）に関しては“天井関数”ってのがあります。
「数式」「数学/三角」から「CEILING,MATH」を選択します。
(Excel2010以下では“CEILING”になります )


数値：作業時間が入力されているセルを指定します
倍数：“0:10”と入力します。MROUND関数の時は、ダブルクォーテーションは不要でしたが、ここでは半角のダブルクォーテーションを入れないとエラーになります。


と、このように10分単位で作業時間が切上げられました。


時間を切下げする
では次は、10分単位で切下げをしてみます。
今度は“FLOOR関数”を使用します。“フロア”というのは“床”、“下限”という意味がありますが、これも数値の端数処理（丸め）に関しては“床関数”ってのがあります。
「数式」「数学/三角」から「FLOOR,MATH」を選択します。
(Excel2010以下では“FLOOR”になります )


数値：作業時間が入力されているセルを指定します
倍数：“0:10”と入力します。ここも半角のダブルクォーテーションを入れないとエラーになります。


と、このように10分単位で作業時間が切捨てれました。


これらの関数は、数値の端数処理用の関数ですが、このように時間の集計計算にも応用が可能です！


関連ページ
CEILING関数で指定した値の倍数で切上げをしてみた（端数処理）
日付、時刻関数のシリアル値とは
日毎の作業時間（経過時間）を集計してみた /ピボットテーブル
関数一覧（カテゴリ別）
関数一覧（名前別）

Excelの関数を使って1行おきの値を合計してみます。
“ROW関数”、“ISEVEN関数”、“SUMIF関数”を使用します。


例えばこんな受注データがあります。日毎の“数量”と“金額”が縦に配置されています。


Excelでデータ分析をしようとする賢い人は、分析用の元データをこんな形に作ってはいけません。
ですが、何も知らない人の中には見易いという理由だけで、こんなデータを作ってしまう人がいるんです。。。
後で、別の人がこのデータの分析をしようとした時に、、、
「“数量”の合計や平均どうやって集計すんの？」
「日付で並べ替えできないじゃん！！？」
と、キレ気味になっちゃいます。
んで、そんな時にExcelの関数を使って1行おきの値を合計してみます。

やりかたは、“数量”と“金額”の入力されている行番号が奇数か偶数かを求めて、奇数行だけの合計、偶数行だけの合計を求めます。

まず、行番号を求めるために“ROW関数”を使用します。


次に求めた行番号か奇数か偶数かを求めるのは“ISEVEN関数”を使います。
“ISEVEN関数は”偶数だったら“TRUE”、奇数だったら“FALSE”を表示する関数ですが、
“ISODD関数”で奇数だったら“TRUE”、偶数だったら“FALSE”を表示させてもよいですし、“MOD関数”で行番号を“2”で割った時の余り（“0”か“1”）を求めてもいいでしょう。。


と、3行目に入力されている“数量”は“FALSE”と表示されます。


これを、コピーして貼り付けるとこうなって、“数量”は“FALSE”、“金額”は“TRUE”に表示されました。


あとは、“FALSE”の数値の合計、“TRUE”の数値の合計を“SUMIF関数”で求めれば、“数量”と“金額”の合計が求められます。
“SUMIF関数”の引数は、条件検索をする範囲（ここでは、“偶数かどうか”のF列の範囲）、検索条件（“FALSE”か“TRUE”）、合計を集計する範囲（“値”のD列の範囲）を指定します。


と、このように、1行おきに入力されている“数量”と“金額”の合計が集計できました。


“AVERAGEIF関数”を使うと、平均値も集計することができます。


こんな感じになります。


それから、“ROW関数”と“ISEVEN関数”をネスト（入れ子）すれば、シンプルになります。


ちなみに、こんな体裁の集計表はピボットテーブルを使えば簡単にできちゃいますから、見易いからと言ってこんな表を時間をかけて作っては絶対にイケマセンっ！

では、次に同じ関数の活用で一行おきのセルに書式を設定してみましょう。


関連ページ
Excelのデータの作り方
行番号を求める / ROW
奇数か偶数かを求める / ISODD、ISEVEN
条件に一致したセルの合計を求める / SUMIF
割り算の余り（剰余）を求める / MOD
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）