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

Excel.tips

*disclaimer
35929

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

 Tips

  • すべてのカラムを選んで、カラムとカラムの間をダブルクリックすると、カラムの幅を自動調節してくれる。
  • 選択範囲を、ずずっと下まで指定: Ctrl+Shift+↓
  • パターンのある操作を繰り返すには:マクロの記録、あとは、Ctrl+指定の文字
  • 複数の連続したセルの中身を消す:選んで「Del」キー(Bkspキーではダメな点に注意)
  • セルに斜め線を入れる:セルを選んで、右ボタン「セルの書式設定」で「罫線」

 ゼロで始まる番号

  • 学生番号など、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

LOOKUP関数

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

VLOOKUP関数

  • ABCの+-評価を数値に変換する
  • 対応表を作っておく
評価 得点
A+ 10
A 9
A- 8
B+ 7
B 6
B- 5
C+ 4
C 3
C- 2
D 1
NA 0
  • 低いほうから並べる必要はない
=VLOOKUP(評価対象のセル,対応表の左上から右下の範囲を絶対参照指定,左から何列目を返すか,FALSE)
  • 対応表は2列しかないので、返すのは2列目。最後のオプションをFALSEにすることで完全一致の場合のみ値を返す。
=VLOOKUP(G3,$G$23:$H$33,2,FALSE)
  • 参照先が別のシートの場合は範囲の前に「シート名!」をつける
    • LibreOfficeCalcの場合は「$シート名.」
      • また、最後のところ、FALSEではなく0