Excel.tips

[Microsoft]

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

• Excel.tips Excelを使う時のちょっとしたこと
• ショートカットキー
• 集計：集計した結果を入れたい列もしくは行を選んでおいて、AltとShiftと=を押す
• セル結合：Ctrl+Y
• 行・列挿入：Ctrl+「＋」
• 行・列削除：Ctrl+「−」
• カーソル移動　データのある端までジャンプ： Ctrl + カーソルキー
• カーソル移動　左上角のセル（A1）に戻る： Ctrl + Home
• カーソル移動　右下角のセルにジャンプ： Ctrl + End
• Tips
• 数値の変換 =IF(シート!C4>1,1,0)
• 文字の入ったセルの連結 =セル&セル
• 文字の入ったセルの連結 =CONCATENATE(セル, セル)
• 英単語数を数える
• ランダムにする
• 行の高さを一気に全部揃える
• 正規表現検索
• 「このファイルのリソースが信頼できないため、、、」
• 「このファイル形式は、保護ビューでサポートされていません」
• VBAを使えるようにする
• カラムを複数のカラムに分割
• フィルターをかけた部分だけコピペ
• フラッシュ・ファイル： Ctrl+E
• 連続値を入れる
• 入力欄に前に入れたものが勝手に入力される「オートコンプリート」を切る
• カラム幅の自動調整
• 入力してあるセル全体を選択
• マクロの記録
• 連続したセルの中身を消す
• 斜め線
• 行番号
• エラー表示を消す
• ハイパーリンクにしない
• 計算が「手動」になっていることがあるので注意
• 選択肢の中から選んで入力していく場合
• 同じものを何か所かに入力する
• 見出しを固定する
• ファイル名を自動で取得して表示： =CELL("filename")
• 今日の日付： =TODAY()
• CSVファイルを読み込んで文字化けしたら
• 文字化け対策
• エラーの対応
• セルの左上に緑の三角
• マクロが実行できないエラー
• ファイルのプロパティの属性のセキュリティを許可する
• リストからマウスで選んで入力できるように
• 二つの文字列の結合 =CONCATENATE(セル, セル)
• 複数の文字列の統合 &でつなげる
• 二つのセルの値が同じかどうか照合 =
• =exact(A, B)
• ゼロで始まる番号
• 欠損値
• そこだけデータがないときの扱い
• たとえば授業で、平均値を出すときに、その範囲内に欠損値のセルがあるとしたら、
• 空白のセルすべてに0を入れる
• 空白のセル（欠損値）すべてに NA を入れる
• いくつあるか数える
• 単語の数を数える
• 空欄だったら、元の値をコピーする（一覧表のアップデートするときに）
• Excelで特定の値（のセル）に色をつける
• エクセルで文字列を逆にする (2003-08-01 sugiura)
• --- 文字列を後方からソートするには ---
• ポイント
• 手順
• 成績の集計、評価
• 小数点以下の四捨五入に注意
• IF関数を入れ子にする
• LOOKUP関数
• LOOKUP関数を使った成績評価の具体的処理
• VLOOKUP関数
• VLOOKUP関数を使った具体的処理
• VLOOKUP関数のかわりに、indexとmatch
• IFを使って、条件によって、数値を修正
• 二つのファイルの相違を比較　アドイン Inquire
• 差し込み印刷で、Wordのファイルに出力
• Wordでテンプレート作成
• 「差し込み文書」（個別の案内メール送付まで可能）
• グラフ関連
• グラフに数値を表示
• 多読教材、読んだ累積語数のグラフ作成

 ショートカットキー

集計：集計した結果を入れたい列もしくは行を選んでおいて、AltとShiftと=を押す

セル結合：Ctrl+Y

行・列挿入：Ctrl+「＋」

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

行・列削除：Ctrl+「−」

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

カーソル移動　左上角のセル（A1）に戻る： Ctrl + Home

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

 Tips

数値の変換 =IF(シート!C4>1,1,0)

• 別のシートのセルを参照して
• そこが1より大きかったら、1を入れて、そうでなかったら０をいれる

文字の入ったセルの連結 =セル&セル

• 複数も可
• 文字列を加えるときは、 &"文字列"&

文字の入ったセルの連結 =CONCATENATE(セル, セル)

英単語数を数える

https://ja.extendoffice.com/documents/excel/933-excel-count-words.html

ランダムにする

• ランダムにしたいものの右側にその行数分 =rand() と書く
  • １未満の数字がランダムに並ぶので、その大きい順とか小さい順に並べればランダムになる。
    • 並べ替えを実行したら、その時点でまた、ランダム数が表示される（が、それは気にしない）
• =rand() のかわりに、=randbetween(最小値, 最大値)としたら、その間の数字をランダムに出してくれる。例： =randbetween(1, 10)
  • 並べ替えるわけではないので、たまたま同じ数字が出ることがある。
    • それを考えると、単に =rand() した方が簡単。

行の高さを一気に全部揃える

• シート全体を選択　＞　ホーム　＞　書式　＞　行の高さ　＞　数字を入れる　18とか
  • この数字は「ポイント」という単位。

正規表現検索

https://kokodane.com/tec3_37.htm

「このファイルのリソースが信頼できないため、、、」

• ファイル　＞　オプション　＞　トラストセンター　＞　トラストセンターの設定　＞　信頼できる場所　＞　新しい場所の追加　に、自分のファイルを保存してあるフォルダーを指定　＞　サブフォルダーも許可

「このファイル形式は、保護ビューでサポートされていません」

• ファイル　＞　オプション　＞　トラストセンター　＞　トラストセンターの設定　＞　保護ビュー　＞　インターネットから取得したファイルに対して、保護ビューを有効にする のチェックを外す

VBAを使えるようにする

• ファイル　＞　オプション　＞リボンのユーザー設定　＞　メインタブ　＞　開発にチェックを入れる

カラムを複数のカラムに分割

• 被験者情報が混在したIDを、情報ごとに別々のカラムにする。

CNS_DMU_2_JP_001

• カラムを選択して　＞　データ　＞　区切り位置指定
  • 注意: 分けた後、既存の右側のカラムを上書きしてしまうので、事前に列挿入が必要

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

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

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

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

連続値を入れる

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

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

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

カラム幅の自動調整

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

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

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

マクロの記録

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

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

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

斜め線

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

行番号

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

エラー表示を消す

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

ハイパーリンクにしない

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

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

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

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

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

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

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

見出しを固定する

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

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

今日の日付： =TODAY()

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

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

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

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

文字化け対策

• オプション　＞　言語　＞　Officeの編集言語と校正機能
  • 日本語を「優先として設定」

 エラーの対応

セルの左上に緑の三角

• セルを選ぶと左にエラー内容と対応方法が出る
• よくあるエラー
  • 数字が文字として入力されている
    • 数字に変換（複数セル選択で一度に変換可能）

 マクロが実行できないエラー

ファイルのプロパティの属性のセキュリティを許可する

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

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

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

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

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

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

• 間に文字列を入れるときは "文字列" を挿入

=B2&C2&D2&"_"&E2

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

• =でセルを並べるだけ

=C2=F2

• 同じなら 　TRUE
• 違う場合は FALSE
• スペースの有無で違いが出るので注意

=exact(A, B)

 ゼロで始まる番号

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

1. 該当箇所を、エディターなどへコピペ
2. 正規表現で行頭に0を挿入する（行頭^を0で置き換える＝行頭に0を挿入）
   • 検索文字列：^
   • 置換文字列：0

1. エクセルの表の該当箇所の書式を「文字列」にしておく
2. エディターで置き換えたものを、エクセルにコピペ

 欠損値

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

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

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

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

空白のセルすべてに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()
• 単語の数を数える関数はない。
• スペースで単語を区切る、ってことは、スペースの数＋１＝単語の数
• スペース込みの文字数−スペース除いた文字数＋１

=(LEN(文のセル)-LEN(SUBSTITUTE(文のセル," ","")))+1

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

=IF(B2="", A2)

• 一覧表の修正をするときに、
  • 修正が必要なところだけメモをB2に書いておく
  • もし、B2が空欄""だったら、A2の値を入れる

 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 のウインドウが消えて、もとのエクセルの画面になる。

• これで、「reverse」という名前の関数が使えるようになっている。
• 使い方は、他の一般の関数と同じ。

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

 成績の集計、評価

• ６０点未満　　　　　：F
• ６０点以上７０点未満：C
• ７０点以上８０点未満：B
• ８０点以上９０点未満：A
• ９０点以上　　　　　：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関数

• 総合点のセルを指定
• 得点の範囲を集計票の脇に作っておく
  • 低い点から順に高く
• その右に対応する評価を明記

=LOOKUP(総合点,得点範囲,評価)

• 得点範囲と評価のセルを「絶対参照で指定」（記号と数字の両方の前に$を付ける）例：$T$43

=LOOKUP(N42,$T$43:$T$47,$U$43:$U$47)

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

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

---

VLOOKUP関数

• ABCの+-評価を数値に変換する
• 対応表を作っておく

• 低いほうから並べる必要はない

=VLOOKUP(評価対象のセル,対応表の左上から右下の範囲を絶対参照指定,左から何列目を返すか,FALSE)

• 対応表は２列しかないので、返すのは２列目。最後のオプションをFALSEにすることで完全一致の場合のみ値を返す。

=VLOOKUP(G3,$G$23:$H$33,2,FALSE)

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

• 別ファイルを参照する場合は、シート名の前に [ブック名.xlsx]を付ける。

• 参照範囲のエラー
  • 戻り値として取得する値の入っているところまでを参照範囲として指定しないとエラーになる。
    • 単に、照合するカラムだけでなく。
  • 検索対象の値が入っているセルが、参照範囲の左端になるように範囲を選ばないとエラーになる。
  • 返す値は、その参照範囲内で、左から何番目のカラムかを指定

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

• シートを別に作っておいて（例：Scoreという名前に）、そこに基準を貼り付けて（見出しなしで、左上隅から）、そこを絶対参照するようにする。

=VLOOKUP(評価対象セル,Score!$A$1:$B$11,2,FALSE)

Score.xlsx(281)

• これをコピペして「評価対象セル」の箇所を該当のセルに修正
• あとは変換すべき範囲に対応した範囲にそのセルをコピペして広げる
  • マウスで範囲をドラッグして選択して、
    • そのセルをドラッグした範囲（下）にコピペ：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列目）の値（相対位置）を出力する。
  • 出力したい列に0を指定することで、一行全体を出力可能

• 注意 エクセルのバージョンの問題

https://support.microsoft.com/ja-jp/office/index-%E9%96%A2%E6%95%B0-a5dcf0dd-996d-40a4-a822-b56b061328bd

現在のバージョンの Microsoft 365 を使用している場合は、
出力範囲の左上のセルに式を入力し ［Enter］ キーを押して、
式を動的な配列の数式として確定することができます。 

それ以外の場合、最初に出力範囲を選択し、
出力範囲の左上のセルに数式を入力し、
Ctrl + Shift + Enter キーを押して確定し、
従来の配列数式として数式を入力する必要があります。 

IFを使って、条件によって、数値を修正

• 課題を余分に提出したら、ボーナス点をもらえることにした場合、
  • 最終的な集計が100点を超えてしまうケースが出る。
  • あくまでも上限は100点とする場合、
  • 「もし100点以上だったら100点、そうでなければ、そのまま」

=IF(チェックするセルの条件,条件に合う場合のスコア,合わない場合のスコア)
=IF(F2>=100,100,F2)

 二つのファイルの相違を比較　アドイン Inquire

• https://color-variations.com/excel-inquire/

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

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

Wordでテンプレート作成

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

など。

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

「差し込み文書」（個別の案内メール送付まで可能）

1. 「宛先の選択」＞「既存のリストを使用」で、エクセルのファイルを選ぶ
2. Wordのテンプレートで、情報を差し込みたい文字を選ぶ。例えば「学籍番号」
3. 「差し込みフィールドの挿入」から、エクセルの見出しの中で、該当の項目を選ぶ
4. これを必要なだけ繰り返す。
5. 「結果のプレビュー」で、差し込み状況を確認
   1. その右側で、一枚ずつ確認可能
6. 「完了と差し込み」＞
   1. 個々のドキュメントの編集
      1. Wordのファイルで一枚ずつ編集できるようになる
   2. 「文書の印刷」で、そのまま印刷するか、PDFに出力する
   3. 電子メールメッセージの送信
      1. 「宛先」で、エクセルファイル内のメールアドレスを指定可能
      2. 「件名」で、共通のタイトル
      3. 「メール形式」で、書式なしだとThunderbirdが起動する
      4. 「メール形式」で、HTMLだとOutlookが起動する
      5. OK押すと、デフォルトのメールソフトが起動して、自動で送信を開始する。
      6. 「確認」のダイアログボックスが出ることがある。「別のプログラムがメールを送信しようとしているがよいか」というもの。OKする。
      7. 複数のメールソフトを使っていると、どれから送信されるかわからなくなるので、要注意！

グラフ関連

 グラフに数値を表示

• 出来たグラフを選ぶと、「メニューにグラフのデザイン」が出てくる。
• グラフの右上に「＋」マークが出てきて、そこでグラフに入れる要素を選べる。
  • データラベルを選んで、数字を表示

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

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