トップ 差分 一覧 Farm ソース 検索 ヘルプ PDF RSS ログイン

Excel.tips

*disclaimer
93483

Excel.tips Excelを使う時のちょっとしたこと

 Tips

  • すべてのカラムを選んで、カラムとカラムの間をダブルクリックすると、カラムの幅を自動調節してくれる。
  • 選択範囲を、ずずっと下まで指定: Ctrl+Shift+↓
  • パターンのある操作を繰り返すには:マクロの記録、あとは、Ctrl+指定の文字
  • 複数の連続したセルの中身を消す:選んで「Del」キー(Bkspキーではダメな点に注意)
  • セルに斜め線を入れる:セルを選んで、右ボタン「セルの書式設定」で「罫線」
  • 行番号を入れる =row()
    • 上から下までこれを入れておけば、連続番号になる
      • 番号をずらしたいときは、これに足したり引いたりすればよい(=row()+5 とか)
  •  #N/A を表示させないようにする:iferror(該当部分,"")

セル結合ショートカットキー:Ctrl+Y


行・列挿入のショートカットキー:Ctrl+「+」

  • 具体的には、CtrlとShiftと+の三つのキーを押す

行・列削除のショートカットキー:Ctrl+「−」

ハイパーリンクにしない

  • オプション > 文章校正 > オートコレクトのオプション > 入力オードフォーマット > □インターネットとネットワークのアドレスをハイパーリンクに変更する

計算が「手動」になっていることがあるので注意

  • 「手動」に設定してあるファイルをひらくと、それ以降、手動になってしまう。
    • 知らないでいると大変なことになる。

 二つの文字列の結合 =CONCATENATE(セル, セル)

  • 間にスペースを入れるときは

=CONCATENATE(セル, " ", セル)

 二つのセルの値が同じかどうか照合 =

  • =でセルを並べるだけ
=C2=F2
    • 同じなら  TRUE
    • 違う場合は FALSE
  • スペースの有無で違いが出るので注意

 ゼロで始まる番号

  • 学生番号など、0で始まる数字の場合、そのままだと、先頭の0は表示されない。ないものとして、扱われてしまう。
  • 最初から、セルの書式を「文字列」にしてから、入力し始めるように。
  • でも、既存のデータをファイルでもらって、開けたら、0が消えていたという場合、どうやって復活するか。

  1. 該当箇所を、エディターなどへコピペ
  2. 正規表現で行頭に0を挿入する(行頭^を0で置き換える=行頭に0を挿入)
    • 検索文字列:^
    • 置換文字列:0
  3. エクセルの表の該当箇所の書式を「文字列」にしておく
  4. エディターで置き換えたものを、エクセルにコピペ


 欠損値

そこだけデータがないときの扱い

  • エクセルでは欠損値を表す「記号」はない。(「#N/A」は値がないことを示すエラー表示)
  • そこの値をデータに含めるかどうかは、目的次第。
    • 含めない場合は、空欄にしておく(0という数字を入れないように)(セルの背景をグレーなどにしておくとわかりやすい)
    • 含める場合、つまり、値がないことはそこが0という値であることを示す必要がある場合、数値 0 を入れておく。
  • たとえば、平均値を出すときに、その範囲内に欠損値のセルがあるとしたら、
    • 空欄なら、計算の対象からはずして平均を出す。
    • 0 になっていると、その分、平均値が下がることになる。

 Excelで特定の値(のセル)に色をつける

  1. 色をつけたい範囲を選んでおく。
  2. 「ホーム」から、右の方、「条件付き書式」を選ぶ。
  3. 一番上の「セルの強調表示ルール」から、
  4. 「指定の値に等しい」を選ぶ。(文字列の場合は「文字列」を選ぶ)
  5. 左に数字を入れて、右で書式を選ぶ。(一番下、「ユーザ設定の書式」で選ぶ。)
  6. 「OK」を押す

 エクセルで文字列を逆にする (2003-08-01 sugiura)

--- 文字列を後方からソートするには ---

ポイント

  • ユーザー関数をつくる
  • VBAにそなわっている文字列を逆にする関数「StrReverse」を使う

手順

  1. エクセルでワークシートを開く。
  2. メニューの「ツール」から「マクロ」を選び、そこから「Visual Basic Editor」を選ぶ。
  3. 新しく見慣れないウインドウが開く。それが Visual Basic というプログラムを書くところ。
  4. Visual Basic のウインドウのメニューの「挿入」から「標準モジュール」を選ぶ。
  5. 「Module (コード)」という新しいウインドウが開く。そこにプログラムを書いていく。
  6. そのなかに以下のように書く:
Function reverse(v)
reverse = StrReverse(v)
End Function
  1. Visual Basic のウインドウのメニューの「ファイル」から「終了して Microsoft Excel へ戻る」を選ぶ。
  2. Visual Basic のウインドウが消えて、もとのエクセルの画面になる。
  3. これで、「reverse」という名前の関数が使えるようになっている。
  4. 使い方は、他の一般の関数と同じ。

  • 例えば、A1のセルに「あいうえお」と入っているとして、その右側のB1 のセルに「=reverse(A1)」と書き入れ、「Enter」キーを押せば、B1の セルには「おえういあ」と表示される。
  • 単語リストが縦に並んでいるとして、その右側にこの関数を使って、 単語の文字列を逆順にして並べて、その逆順の文字列で並べ替えをすれ ば、もとの単語の後方から並べ替えたことになる。

 成績の集計、評価

  • 60点未満     :F
  • 60点以上70点未満:C
  • 70点以上80点未満:B
  • 80点以上90点未満:A
  • 90点以上     :S

小数点以下の四捨五入に注意

  • 最終的な得点を小数点以下一桁を四捨五入する場合、
    • エクセルの「ホーム」の小数点以下の数字の桁表示調整で「四捨五入」したつもりになっていても、実際の数値は四捨五入されていないので注意。
    • 見た目「80」なのに、実際の数値は、「79.5」で80に満たないことになっている。
  • ROUND(セル,0)で、小数点以下一桁を四捨五入して、小数点以下ゼロにしておく必要がある。

IF関数を入れ子にする

=IF(Q4>=90,"S",IF(Q4>=80,"A",IF( Q4>=70,"B",IF( Q4>=60,"C","D"))))
  • 最後は、それ以外の場合は"D"ということ
  • EXCEL 2019よりIFS関数が加わった
    • 入れ子にしなくても、並記することで、左から順にあてはめていってくれる

LOOKUP関数

  • 総合点のセルを指定
  • 得点の範囲を集計票の脇に作っておく
    • 低い点から順に高く
  • その右に対応する評価を明記
得点 評価
0 F
60 C
70 B
80 A
90 S
=LOOKUP(総合点,得点範囲,評価)
  • 得点範囲と評価のセルを「絶対参照で指定」(記号と数字の両方の前に$を付ける)例:$T$43
=LOOKUP(N42,$T$43:$T$47,$U$43:$U$47)

LOOKUP関数を使った成績評価の具体的処理

0 F
60 C-
65 C
70 B
80 A
95 A+

=LOOKUP(得点,Grade!$A$1:$A$6,Grade!$B$1:$B$6)


VLOOKUP関数

  • ABCの+-評価を数値に変換する
  • 対応表を作っておく
評価 得点
A+ 10
A 9
A- 8
B+ 7
B 6
B- 5
C+ 4
C 3
C- 2
D 1
NA 0

評価 得点
A+ 100
A 95
A- 90
B+ 85
B 80
B- 75
C+ 70
C 65
C- 60
D 50
NA 0
  • 低いほうから並べる必要はない
=VLOOKUP(評価対象のセル,対応表の左上から右下の範囲を絶対参照指定,左から何列目を返すか,FALSE)
  • 対応表は2列しかないので、返すのは2列目。最後のオプションをFALSEにすることで完全一致の場合のみ値を返す。
=VLOOKUP(G3,$G$23:$H$33,2,FALSE)
  • 参照先が別のシートの場合は範囲の前に「シート名!」をつける

VLOOKUP関数を使った具体的処理

  • シートを別に作っておいて(例:Scoreという名前に)、そこに基準を貼り付けて(見出しなしで、左上隅から)、そこを絶対参照するようにする。
=VLOOKUP(評価対象セル,Score!$A$1:$B$11,2,FALSE)
  • これをコピペして「評価対象セル」の箇所を該当のセルに修正
  • あとは返還すべき範囲にそれを広げる
    • マウスで範囲をドラッグして選択して、Ctrl+DとCtrl+R

  • LibreOfficeCalcの場合は「$シート名.」
      • また、最後のところ、FALSEではなく0

VLOOKUP関数のかわりに、indexとmatch

=index(列を選択, match(対応する列, 0))


 差し込み印刷で、Wordのファイルに出力

  • データがエクセルの形で手元にある場合、一件一件を見やすくレイアウトして、出力する。
  • 例:NUCTの「小テスト」で論述問題の解答をエクスポートしたもの。それを一人ずつの答案に出力する。

Wordでテンプレート作成

  • 出力したい項目を並べる。
    • 学籍番号
    • 名前
    • エッセイ

など。

  • この時に指定したフォントで出力されるので、この時点で好きなフォントを選んでおく

「差し込み文書」

  1. 「宛先の選択」>「既存のリストを使用」で、エクセルのファイルを選ぶ
  2. Wordのテンプレートで、情報を差し込みたい文字を選ぶ。例えば「学籍番号」
  3. 「差し込みフィールドの挿入」から、エクセルの見出しの中で、該当の項目を選ぶ
  4. これを必要なだけ繰り返す。
  5. 「結果のプレビュー」で、差し込み状況を確認
  6. 「完了と差し込み」>「文書の印刷」で、そのまま印刷するか、PDFに出力する