「Excel」カテゴリーアーカイブ

Excelに関する記事です。

[Excel]シナリオ登録機能で、複数の条件のセットを登録・呼び出しできるようにする。

表の中に代入する値をいくつかまとめてセットにして登録しておくことで、簡単に呼び出して、それぞれの場合を想定して検討できるようになる。

[例]
店舗の1日の会計を計算する表があるとして
「標準の日:単価3000円、客数50人」「高単価低回転な日:単価6000円、客数30人」「低単価低回転な日:単価1500円、客数30人」
といった場面を登録しておくことで、それぞれの場面を会計表に簡単に代入できる。
これらの数値のセットを切り替えての場面ごとの検討が容易になる。

[操作]
データタブ→What-If分析→シナリオの登録と管理→追加
から、
シナリオ名:標準の日と入力
変化させるセル:単価と客数を入れるセルをそれぞれ選択し、数値を入れる。
ことで登録ができる。

シナリオの登録と管理→代入したいシナリオ名を選択し表示をクリックで、数値がシナリオの内容に変化する。

[Excel]ゴールシークで自動で必要な値を逆算。

[概要]
求めたい計算結果を得るための値を逆算して出す際に、自分で逆算の式を考えなくても自動で算出してくれる機能。
複雑に数値同士が関係する状態で逆算の式を立てるのは大変という時に便利。

身長1.7
体重100
BMI34.6

[例]
身長170センチの人が100kg、BMI値34.6の肥満の人がいる。
健康的なBMI値23になるには体重を何kgにすればいいか逆算したい。

このとき、データタブ→What-If分析→ゴールシークから、
数式入力セル:BMIを算出する式の入ったセル
目標値:23
変化させるセル:体重のセル
と指定することで、体重の値を色々と変化させて目標値に最適な体重を算出してくれる。
ゴールシーク使用後は下の表のように数値が変化し、66.47kgを目指すべきということがわかる。

身長1.7
体重66.47
BMI23.0

[注意点]
変化させるセルには数式が入ったセルは設定できない。ゴールシーク用に表をコピーしておいて、数字で入れなおしてからゴールシークを使う事。

[Excel]データテーブルで試算・分析を行う

[概要]
様々な条件同士の計算結果の一覧を、簡単な操作で作れるようになる。

データベース機能を使って、複数の条件を変更して、試算や分析を行い、最適な選択肢を比較検討する。
感度分析、What-If分析を行うためのツール。

[例]
・借入額と返済期間の条件を様々に変えた際の月々の返済額を調べる。

・様々な条件のうちの1つを使った、試算結果例の表
・様々な条件を行列に配置した表
の二つを用意する。

・ 試算結果例の表
B2とB4に条件の一例を入れる。
B1は=-PMT(B3/12,B4*12,B2)とローンの利率計算の関数を入れて月々の返済額を出している。

AB
1月々の返済額¥29,524
2借入額1,000,000
3金利4%
4返済期間3

・ 様々な条件を行列に配置した表

ABCDE
5借入額
6=B11,000,000 1,200,000 1,400,000
7返済期間(年)2
83
94

上記のような借入額と返済期間を並べた表を用意する。
B6にはそれぞれから求めたい計算結果となるB1を入力する。=B1と入れるか、数式そのものをコピーする。
A5からE9の表全体を選択し、データタブ→What-If分析→データテーブルをクリック。
行の代入セルにB2、列の代入セルにB4を絶対参照で入力、セルをクリックして選択の場合は自動でなる。
以下のように自動的にそれぞれの場合の返済額が表示される。

ABCDE
5借入金額
6¥29,5241,000,000 1,200,000 1,400,000
7返済期間243,425 52,110 60,795
8329,524 35,429 41,334
9422,579 27,095 31,611

これを見て、月々4万円以内の返済で、出来るだけ短い期間で返済できるように借りる場合は、120万円まで借りられる。
月々4万円以内の返済で 、借入額を最大にしたい場合は、4年で140万円が最適
といった検討ができる。

B6には 試算結果例の数値が表示されてしまうため、見た目が気になるのであれば文字色を変えて見えなくする。

[条件が1つの場合]
データタブ→What-If分析→データテーブルをクリックの際に条件が複数になる片方だけを代入セル指定することで、条件1つの変化だけをデータテーブルにすることも可能。

[注意点]
データテーブルの条件が多い場合、計算元の表の数値を一つ買える度に大量の計算が行われて重くなる。
その場合、数式タブ→計算方法の設定からデータテーブル以外自動にチェックを入れて、自動計算を切る。
手動で再計算するには、計算方法の設定の右隣にある再計算実行ボタンをクリック。

参考
https://www.crie.co.jp/chokotech/detail/346/
Excel最強の教科書

[Excel]除算貼り付けや乗算貼り付けで数値の単位変更を簡単に行う

1,000,000(円)、2,000,000(円)…と円単位で入力されている表を千円単位に変換したい場合
適当なセルに1000と入力し、それをコピー→1,000,000、2,000,000…を範囲選択→形式を選択→除算貼り付けを選択する。
コピーした1000で割り算されて、1,000 、2,000となる

乗算貼り付けで同様にして0を増やすことも可能。

[Excel]セルに名前を付けて、何度も使う数字を参照できるようにする。

画面左上、デフォルトでB7などと表示されている箇所に任意の名前を入力する。
数式内でその名前を入力することで、名前の付いたセルに入っている数値を参照できる。
これによって同じ数字を何度も入力する際に簡略化できる、一括で変更を簡単にできる。

[例]
数値:0.10、名前:消費税 と設定したセルを用意して、別のセルの数式として「=100*消費税」と入力すれば、消費税分の数値が表示される。
数値に名前を付けるのは、それ専用の一つのシートにまとめて分かりやすくする。別シートからも参照可能。

付けた名前の管理編集は、数式タブ→名前の管理から行う。

[Excel]トレース機能でセルの参照を視覚的に確認

数式が入力されているセルを選択し、数式タブ→参照元のトレース

これによって、そのセルの数式がどこのセルの数値を参照して持ってきているのかが矢印で視覚化されて、数式とセルの関係性が分かりやすくなったりミスに気づける。
矢印は同タブのトレース矢印の削除ボタンで削除可能。

同様に、参照先のトレースボタンで、参照されている側のセル起点の→として使える。

ショートカット
参照元トレース:Alt→M→P
参照先トレース:Alt→M→D

[Excel]SUMIF関数 SUMIFS関数で条件を指定して合計する

=SUMIF(検索する範囲,検索する条件,合計する範囲)
と入力する。
=SUMIF(D3:D8,F4,E3:E8)
範囲はセル:セル 検索範囲と合計範囲は同じ形で指定する。合計は省略する。

=SUMIFS (検索する範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, )
で複数の条件を満たす場合になる

=SUMIF()+SUMIF()
とすることで、orの条件設定が可能


検索条件の指定

※「」の中身を入力する。
・指定の値と等しい場合は直接入力 セルと等しい場合も直接入力「B12」
 検索する条件に文字列を入れる際は「”文字列”」と””で囲う。
 日付の場合は 「”2019/11/19″」 と入力すればOK


・指定した値以上、以下等

記号内容
<未満
> 超過(指定した値より大きい)
<= 以下
>=以上
<>指定した値以外 指定した値と等しくない

[Excel]非表示機能は非推奨。非表示ではなくグループ化で列や行を隠す。

非表示機能は表示されていないデータに気づきにくく、ミスの見落としやデータの不備に気づかない、相手に見せてはいけないデータが実は存在していた。再表示の操作が面倒といった問題が起こりやすい。

行や列を隠して表を見やすくしたい場合は、グループ化を使う。

[操作]
隠したい行・列をまとめて選択しデータタブ→グループ化をクリック。
シート外側に表示された-ボタンをクリックすることで、グループ化された範囲が非表示になるが、+ボタンの存在で省略されたことは明確、再表示もワンクリックで出来る状態になる。
グループ化した中をさらに入れ子状にグループ化も可能
解除は同じくデータタブの「グループ化解除」から

[Excel]0から始まる文字列を入力する

[問題]
001、080xxxxxといった数字は頭のゼロが削られてしまう。
それを文字列として0も含めて入力したい。

[解決1]
入力の際に先頭に「’」を付ける。「’080」と入力する。表示は’を除いて表示される。

[解決2]
入力前にセルの入力形式を右クリック→セルの書式設定から「文字列」を指定しておく。

[Excel]相対参照と絶対参照と複合参照

[概要]
数式をコピーした際にコピー先に応じて数式内の参照先をどう変化させるかの設定。固定したい行列の値の前に$をつける。

[操作]
直接入力。またはセル参照をクリックしてをF4を押すことで相対、絶対、複合(行固定、列固定)の順に変化

[例]

相対参照:コピー先に応じて参照先が変化する
      例「B2」

絶対参照:コピー先と関係なく参照先は固定される
      例「$B$2」

複合参照:行・列の片方は固定、片方がコピー先に応じて変化する
      例「B$2」

相対参照  D2に =B2+C2と入力する。 三行目にコピーするとD3に =B3+C3となる

絶対参照  D2に =$B$2+C2 と入力する。 三行目にコピーするとD3に =B2+C3となる

複合参照  D2に =B$2+C2 と入力する。 三行目にコピーするとD3に =B2+C3となる
E列にコピーするとE3に=C2+D3となる B2のBは変化、2は固定。C3はどちらも変化。