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

Excelに関する記事です。

[Excel]SUBTOTAL関数で合計に影響を与えない小計項目を作る。

AB
11月2日100
21月3日100
31月4日100
4小計300
51月5日100
61月6日100
7小計200
8合計500

上記のような表を作る場合、小計と合計にSUM関数を使ってB列をまとめて合計しようとすると小計項目まで二重に合計されてしまう。
それぞれにSUBTOTAL関数を使うことで、合計のSUBTOTAL関数では小計が無視されて正しい合計が計算される。

B4の小計に=SUBTOTAL(9,B1:B3)
B7の小計に=SUBTOTAL(9,B5:B6)
B8の合計に=SUBTOTAL(9,B1:B7)

と入力する。9はSUBTOTALの計算方法の指定を合計とする数字で、他にも平均や最大値など様々ある。B1:B3はSUM関数と同様に計算範囲の指定。

[Excel]全角を半角に、半角を全角にする

asc関数とjis関数を使い、英数字やカナの全角と半角を変換できる。

例)B2が「080-1234-5678」と全角で入力されたデータが入っているセル がある。
別のセルに「=asc(B2)」と入力すると、「080-1234-5678」と半角になる。

同様に、半角が入力されたセルを指定して「=jis(B2)」することで半角文字が全角となる。

[Excel]ショートカットで数値の表示を操作する。

・3桁区切りのカンマを入れる。
セルや範囲を選択し、Ctrl+Shift+1を押す。

・¥マークを数字頭につける。
数値の入力されたセルや範囲を選択し、 Ctrl+Shift+4を押す。

・%表示にする。
セルや範囲を選択し、 Ctrl+Shift+5を押す。 0.5と入力されたセルなら50%となる。

・セルに表示される少数の桁数を操作する。
数値の入力されたセルや範囲を選択し、 Alt→H→0を押すと表示される少数が1桁増える。 Alt→H→9を押すと1桁減る。

[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の中央に配置される。