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

Excelに関する記事です。

[Excel]行や列を簡単に入れ替える

入れ替えたい行や列を範囲選択する。
選択範囲の外枠にポインタを合わせて(上下左右の矢印表示)Shiftを押しながらドラッグ→入れ替えたい位置でShiftを押したままドロップ
これで「切り取り→挿入」のショートカットとなり、指定の位置に入れ替わる。

行全体、列全体でなく範囲選択でも可能なので、表の一部分のみを入れ替えることもできる。

[Excel]複数セルをまたぐ斜線を引く

罫線としての斜線は1セル内にしか引けないため、複数セルをまたいで斜線を引きたい場合は図形機能を使う
[操作]
挿入タブ→図→図形→直線を選択。
Altを押しながらマウスで斜線を引く。
※Altを押すことでセルの枠ぴったりに合わせて線を引くことができる。

[Excel]オートフィルを使いこなす

・セルの右下にカーソルを当て+マークの状態(フィルハンドル)でドラッグすると
セルの情報に応じて自動で連続したデータが入力される
例、日にち、曜日、数字、干支など

・10、20と連続した2セルを選択して、(フィルハンドル)でドラッグすると
30、40、50と10ずつ増える連続したデータが入力される。

・ドラッグからドロップした際に右下に表示されるオプションメニューから、様々な連続データのルールを選択できる。日付を月単位、日単位にするなど。

・オートフィルで書式のみのコピーも可能
フィルハンドルを右ボタンでドラッグ&ドロップで書式のみコピーのオプションを選べる。これで、値はコピーせず書式だけをオートフィル可能。

これを利用して、1行おきにセルの背景色が交互に変わる表を簡単に作れる。
操作は背景色灰色、背景色黒の2行を選択し、オートフィル→書式のみコピー。

・大量の行・列にわたるオートフィルは長々とドラッグするのではなく、数値指定で行う
ホームタブ→右の方にある「フィルボタン」→連続データの作成
からダイアログにオートフィルの条件を入力する。

[Excel]複数セルにまたがる表の見出しを中央に配置したい。セルの結合は使わず、選択範囲内で中央を使う。

「BMIを算出したい人の名前」を中央に揃えたい場合
C2~G2に対して「セル結合して中央揃えというボタン」から揃える方法もあるが、セル結合はトラブルの原因になりやすいため適切でない。
[操作]
C2~G2を選択し、ホームタブ→配置の設定をクリック。
配置タブ→横位置から「選択範囲内で中央」を選択しOK。
これで、セルを結合することなく、見た目だけC2~G2の中央に配置される。

[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*消費税」と入力すれば、消費税分の数値が表示される。
数値に名前を付けるのは、それ専用の一つのシートにまとめて分かりやすくする。別シートからも参照可能。

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