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

Excel.tips

*disclaimer
160530

[Microsoft]

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

 Tips

入力欄に前に入れたものが勝手に入力される「オートコンプリート」を切る

  • 「ファイル」>「オプション」>「詳細設定」>「編集オプション」>「オートコンプリートを使用する」のチェックを外す

カラム幅の自動調整

  • すべてのカラムを選んで、カラムとカラムの間をダブルクリックすると、カラムの幅を自動調節してくれる。

入力してあるセル全体を選択

  • 選択範囲を、ずずっと下まで指定: Ctrl+Shift+↓

マクロの記録

  • パターンのある操作を繰り返すには:マクロの記録、あとは、Ctrl+指定の文字

連続したセルの中身を消す

  • 複数の連続したセルの中身を消す:選んで「Del」キー(Bkspキーではダメな点に注意)

斜め線

  • セルに斜め線を入れる:セルを選んで、右ボタン「セルの書式設定」で「罫線」

行番号

  • 行番号を入れる =row()
    • 上から下までこれを入れておけば、連続番号になる
      • 番号をずらしたいときは、これに足したり引いたりすればよい(=row()+5 とか)

エラー表示を消す

  •  #N/A を表示させないようにする:iferror(該当部分,"")

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


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

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

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

ハイパーリンクにしない

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

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

  • 「手動」に設定してあるファイルをひらくと、それ以降、手動になってしまう。
    • 知らないでいると大変なことになる。
  • エクセルのファイルをいくつも開いていて、そのうちの一つが、手動に設定してあると、それを見た後、設定が手動になってしまう。
  • 「ファイル」>「オプション」>「数式」>「計算方法の設定」で、「自動」を選びなおす。

選択肢の中から選んで入力していく場合

  • いくつか選択肢を入れた後、Alt + ↓ で、選択肢が選べるようになる

同じものを何か所かに入力する

  • 入れたいセルを、Ctrlを押しながら選んでいく
  • 最後のところで、入れたい内容を入れて、Ctrl 押しながら、Enterで確定する


見出しを固定する

  • 見出しにする行の下の行を選んで、
  • 「表示」>「ウィンドウ枠の固定」

ファイル名を自動で取得して表示: =CELL("filename")


今日の日付: =TODAY()


CSVファイルを読み込んで文字化けしたら

  • ファイルをダブルクリックで開けるのではなくて、

  1. 新規ファイルを開いてから、
  2. メニューの「データ」で「テキストまたはCSVから」を選んで「インポート」

 リストからマウスで選んで入力できるように

  1. 別のシートに選択肢のリストを作っておく
  2. 「データ」>「データの入力規則」>「リスト」を選び
  3. 項目を記入する欄が出たら、右の矢印を押して
  4. 選択肢のシートの該当箇所の範囲を選んで
  5. 入力欄の右の矢印を押して、「OK」

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

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

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

 複数の文字列の統合 &でつなげる

  • 間に文字列を入れるときは "文字列" を挿入
=B2&C2&D2&"_"&E2

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

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

 ゼロで始まる番号

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

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


 欠損値

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

  • エクセルでは欠損値を表す「記号」はない。(「#N/A」は値がないことを示すエラー表示)
  • そこの値をデータに含めるかどうかは、目的次第。
    • 含めない場合は、空欄にしておく(0という数字を入れないように)(セルの背景をグレーなどにしておくとわかりやすい)
    • 含める場合、つまり、値がないことはそこが0という値であることを示す必要がある場合、数値 0 を入れておく。

たとえば授業で、平均値を出すときに、その範囲内に欠損値のセルがあるとしたら、

  • 空欄なら、計算の対象からはずして平均を出す。
  • 0 になっていると、その分、平均値が下がることになる。
  • 授業の小テストの記録ではこの違いが大違いになるので注意
    • 小テストの平均点を出そうとして、=average(範囲) としたとき
    • 15回中5回しか受けてなくても5回とも10点だったら、=average()では10点になる
    • 平常点に加味する場合は、受けなかった小テストは0点を明示的に入力しておくこと

空白のセルすべてに0を入れる

  1. 範囲を選んでおく
  2. [CTRL]+[G]
  3. 「セル選択」>「空白セル」で、空白のセルがすべて選ばれる
  4. 0を入力し、[CTRL]+{Enter}

 いくつあるか数える

countif(範囲, 数える対象)

countif(B4:M4, "〇")
  • B4からM4の範囲内に 〇 がいくつあるか数える


 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)
  • 参照先が別のシートの場合は範囲の前に「シート名!」をつける
  • 3列目の値を返したかったら、後ろから二つ目の引数を 3 にする

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に出力する

 多読教材、読んだ累積語数のグラフ作成

  1. 範囲選択
  2. 「挿入」>「おすすめグラフ」>「積み上げ横棒」>「OK」
  3. グラフエリアで、マウス右ボタン>「データの選択」
    1. >「凡例項目(系列)」で、受講者IDのチェックを外す>「OK」
    2. >「横(項目)軸ラベル」で、「編集」>「軸ラベルの範囲」>受講生IDの範囲を指定>OK
  4. 縦軸のところで、マウス右ボタン>「軸の書式設定」>「軸を反転する」にチェック☑
  5. 横軸のところで、マウス右ボタン>「軸の書式設定」>「表示形式」>「カテゴリ」で「数値」
  6. 横軸のところで、マウス右ボタン>「補助目盛線の追加」
  7. グラフのウィンドウの大きさに注意(高さが足りないと途中が省略される)