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

Excel.tips

*disclaimer
247122

[Microsoft]

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

 Tips

フィルターをかけた部分だけコピペ

  1. コピーしたい部分を選択
  2. Alt+;
  3. あとは普通にコピペ

フラッシュ・ファイル: Ctrl+E

  • 特定のパタンに従って、一連の値を入れたい場合
    • 苗字と名前がある隣に、苗字だけ入れたいとか
    • 学生番号と名前の隣に、学生番号だけ入れたいとか

連続値を入れる

  • 先頭のセルに「1」> 「ホーム」> 右の方「フィル」>「連続データの作成」

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

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

カラム幅の自動調整

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

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

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

マクロの記録

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

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

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

斜め線

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

行番号

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

エラー表示を消す

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

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


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

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

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


カーソル移動 データのある端までジャンプ: Ctrl + カーソルキー

カーソル移動 左上角のセル(A1)に戻る: Ctrl + Home

カーソル移動 右下角のセルにジャンプ: Ctrl + End


ハイパーリンクにしない

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

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

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

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

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

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

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


見出しを固定する

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

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


今日の日付: =TODAY()

  • キーボード・ショートカット:Ctrl+;

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]

空白のセル(欠損値)すべてに NA を入れる

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

 いくつあるか数える

countif(範囲, 数える対象)

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


 単語の数を数える

  • 文字数を数える関数はある。LEN()
  • 単語の数を数える関数はない。
  • スペースで単語を区切る、ってことは、スペースの数+1=単語の数
  • スペース込みの文字数−スペース除いた文字数+1
=(LEN(文のセル)-LEN(SUBSTITUTE(文のセル," ","")))+1

 空欄だったら、元の値をコピーする(一覧表のアップデートするときに)

=IF(B2="", A2)
  • 一覧表の修正をするときに、
    • 修正が必要なところだけメモをB2に書いておく
    • もし、B2が空欄""だったら、A2の値を入れる
元の値(A2) メモ(B2) 修正後
メールアドレス ここに書く
メールアドレス 削除
メールアドレス
メールアドレス 変更
メールアドレス 削除
メールアドレス
メールアドレス 変更

 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)

Score.xlsx(74)

  • これをコピペして「評価対象セル」の箇所を該当のセルに修正
  • あとは返還すべき範囲にそれを広げる
    • マウスで範囲をドラッグして選択して、Ctrl+DとCtrl+R

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

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

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

  • 説明
=index(参照する配列, match(検索語,参照する配列中で検索語が含まれる列, 0), 出力したい列)
=INDEX(Sheet2!$A$1:$B$80, MATCH(D2,Sheet2!$A$1:$A$80, 0),2)
    • 参照する配列は、絶対参照
    • 二枚目のシートの左上A1から2列で80行目B80までの配列を参照
    • 配列の左側A1からA80の中にキーとなる語が含まれている。
    • その語に対応する右側(2列目)の値を出力する。

 差し込み印刷で、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. グラフのウィンドウの大きさに注意(高さが足りないと途中が省略される)